migrate.js

/**
 * @module migrate
 * @description Generic SQL migration runner for PostgreSQL.
 * Reads .sql files from a directory, sorts them, and executes them in order.
 * Skips migrations that have already been applied (handles duplicate relation errors).
 * 
 * Usage:
 *   import { runMigrations } from '@simplex/crm-core/migrate';
 *   await runMigrations('./migrations');
 *   // or: await runMigrations(['./crm-core/migrations', './app/migrations']);
 */

import fs from 'fs';
import path from 'path';
import { exec } from './core/db.js';

/**
 * Run SQL migrations from one or more directories.
 * 
 * @param {string|string[]} migrationPaths - Directory path(s) containing .sql files
 * @param {Object} [options]
 * @param {string[]} [options.skipPrefixes=[]] - Filename prefixes to skip (e.g., ['sqlite'])
 * @param {Function} [options.onApplied] - Callback(filename) after applying a migration
 * @param {Function} [options.onSkipped] - Callback(filename, reason) when skipping
 * @param {Function} [options.onError] - Callback(filename, error) on failure. If not provided, errors throw.
 * @returns {Promise<{applied: string[], skipped: string[], failed: string[]}>}
 */
export async function runMigrations(migrationPaths, options = {}) {
  const {
    skipPrefixes = [],
    onApplied,
    onSkipped,
    onError,
  } = options;

  const dirs = Array.isArray(migrationPaths) ? migrationPaths : [migrationPaths];
  const applied = [];
  const skipped = [];
  const failed = [];

  for (const dir of dirs) {
    const resolvedDir = path.resolve(dir);
    if (!fs.existsSync(resolvedDir)) {
      throw new Error(`Migration directory not found: ${resolvedDir}`);
    }

    const files = fs.readdirSync(resolvedDir)
      .filter(f => f.endsWith('.sql') && !skipPrefixes.some(p => f.startsWith(p)))
      .sort();

    for (const file of files) {
      const sqlFile = path.join(resolvedDir, file);
      const sql = fs.readFileSync(sqlFile, 'utf8');

      try {
        await exec(sql);
        applied.push(file);
        onApplied?.(file);
      } catch (err) {
        // Duplicate relation/column errors mean migration was already applied
        if (
          err.code === '42701' || err.code === '42P07' ||
          (err.message && (err.message.includes('already exists') || err.message.includes('duplicate column')))
        ) {
          skipped.push(file);
          onSkipped?.(file, 'already applied');
        } else if (onError) {
          failed.push(file);
          onError(file, err);
        } else {
          throw err;
        }
      }
    }
  }

  return { applied, skipped, failed };
}