Source: config/migrations/runMigrations.js

import { query, transaction, UUID2hex, HEX2uuid } from '@commtool/sql-query';
import { readdir } from 'fs/promises';
import path from 'path';
import { fileURLToPath, pathToFileURL } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));

/**
 * API passed into each migration's migrate() function.
 * Migrations can use SQL queries, transactions, or pure JS logic.
 */
const MIGRATION_API = { query, transaction, UUID2hex, HEX2uuid };

/**
 * Ensures the dbVersion tracking table exists.
 * Safe to call on both fresh and existing databases.
 */
const ensureVersionTable = async () => {
    await query(`
        CREATE TABLE IF NOT EXISTS \`dbVersion\` (
            \`migrationId\` varchar(128) NOT NULL,
            \`name\` varchar(256) DEFAULT NULL,
            \`appliedAt\` timestamp(6) NOT NULL DEFAULT current_timestamp(6),
            PRIMARY KEY (\`migrationId\`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    `, []);
};


/**
 * Discovers and runs all pending migration modules from this directory.
 *
 * Each migration file must export:
 *   - `id`      {string}   — stable unique identifier used to track applied state
 *   - `name`    {string}   — human-readable description shown in progress
 *   - `migrate` {async fn} — receives the migration API and performs the change
 *
 * Migrations support any logic: SQL schema changes, JSON data transformations,
 * backfills, or any combination. Files are executed in alphabetical filename order.
 *
 * @param {{ onProgress?: (status: object) => void }} [options]
 * @returns {Promise<{ applied: number, skipped: number, total: number }>}
 */
export const runMigrations = async ({ onProgress } = {}) => {
    await ensureVersionTable();

    const applied = await query('SELECT migrationId FROM dbVersion', []);
    const appliedSet = new Set(applied.map(r => r.migrationId));

    const files = (await readdir(__dirname))
        .filter(f => f.endsWith('.js') && f !== 'runMigrations.js')
        .sort();

    const pending = [];
    for (const file of files) {
        const mod = await import(pathToFileURL(path.join(__dirname, file)).href);
        if (!appliedSet.has(mod.id) && !mod.disabled) {
            pending.push({ file, ...mod });
        }
    }

    
    let completed = 0;
    for (const migration of pending) {
        onProgress?.({
            action: 'progress',
            text: `Running migration: ${migration.name}`,
            current: completed,
            total: pending.length,
            progress: pending.length > 0 ? Math.round((completed / pending.length) * 100) : 100
        });

        await migration.migrate(MIGRATION_API);
        if(!migration.DRY_RUN) { 
            await query('INSERT INTO dbVersion (migrationId, name) VALUES (?, ?)', [migration.id, migration.name]);
            completed++;
        }
    }

    return { applied: completed, skipped: appliedSet.size, total: files.length };
};