import { readdir, readFile } from 'node:fs/promises';
import { resolve } from 'node:path';
import type { Database } from './types.js';

export async function migrate(database: Database, migrationsDir = resolve('database/migrations')): Promise<string[]> {
  await database.query(`CREATE TABLE IF NOT EXISTS schema_migrations (
    version text PRIMARY KEY,
    applied_at timestamptz NOT NULL DEFAULT now()
  )`);
  const files = (await readdir(migrationsDir)).filter((name) => name.endsWith('.sql')).sort();
  const applied: string[] = [];
  for (const file of files) {
    const found = await database.query<{ version: string }>('SELECT version FROM schema_migrations WHERE version = $1', [file]);
    if (found.rowCount) continue;
    const sql = await readFile(resolve(migrationsDir, file), 'utf8');
    await database.transaction(async (tx) => {
      await tx.query(sql);
      await tx.query('INSERT INTO schema_migrations(version) VALUES ($1)', [file]);
    });
    applied.push(file);
  }
  return applied;
}

