import * as fs from 'fs';
import * as path from 'path';
import { pgQuery } from './client';

const migrationsDir = path.join(process.cwd(), 'db', 'migrations');

function listMigrationFiles(): string[] {
  if (!fs.existsSync(migrationsDir)) {
    return [];
  }

  return fs
    .readdirSync(migrationsDir)
    .filter((file) => file.endsWith('.sql'))
    .sort((a, b) => a.localeCompare(b));
}

async function ensureMigrationsTable(): Promise<void> {
  await pgQuery(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      id TEXT PRIMARY KEY,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    );
  `);
}

export async function runMigrations(): Promise<{ applied: string[]; skipped: string[] }> {
  await ensureMigrationsTable();

  const files = listMigrationFiles();
  const applied: string[] = [];
  const skipped: string[] = [];

  for (const file of files) {
    const migrationId = file;
    const exists = await pgQuery<{ id: string }>('SELECT id FROM schema_migrations WHERE id = $1 LIMIT 1', [migrationId]);
    if (exists.rowCount && exists.rowCount > 0) {
      skipped.push(file);
      continue;
    }

    const sql = fs.readFileSync(path.join(migrationsDir, file), 'utf-8');
    await pgQuery('BEGIN');
    try {
      await pgQuery(sql);
      await pgQuery('INSERT INTO schema_migrations(id) VALUES($1)', [migrationId]);
      await pgQuery('COMMIT');
      applied.push(file);
    } catch (error) {
      await pgQuery('ROLLBACK');
      throw error;
    }
  }

  return { applied, skipped };
}
