/**
 * Minimal, reversible SQL migration runner.
 * Each migration file uses `-- +up` / `-- +down` markers; both sections are required.
 * Applied versions are tracked in schema_migrations.
 *
 * Usage: tsx src/db/migrate.ts up|down|status [N]
 *   up       apply all pending (or only the next N)
 *   down     rollback the last batch (or the last N)
 *   status   list applied/pending migrations
 */
import { readdir, readFile } from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { pool, query } from "./client.js";
import { config } from "../config.js";

const migrationsDir = path.join(path.dirname(fileURLToPath(import.meta.url)), "../../migrations");

interface ParsedMigration {
  version: string;
  name: string;
  up: string;
  down: string;
}

async function ensureTable(): Promise<void> {
  await query(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version VARCHAR(32) PRIMARY KEY,
      name TEXT NOT NULL,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  `);
}

async function loadMigrations(): Promise<ParsedMigration[]> {
  const files = (await readdir(migrationsDir)).filter((f) => f.endsWith(".sql")).sort();
  const migrations: ParsedMigration[] = [];
  for (const file of files) {
    const match = /^(\d+)_([a-z0-9_]+)\.sql$/.exec(file);
    if (!match) throw new Error(`Migration file name must match NNN_description.sql: ${file}`);
    const raw = await readFile(path.join(migrationsDir, file), "utf8");
    const upMatch = /--\s*\+up([\s\S]*?)(?=--\s*\+down|$)/.exec(raw);
    const downMatch = /--\s*\+down([\s\S]*)$/.exec(raw);
    if (!upMatch || !downMatch) {
      throw new Error(`Migration ${file} must contain both '-- +up' and '-- +down' sections`);
    }
    migrations.push({
      version: match[1] as string,
      name: match[2] as string,
      up: upMatch[1] as string,
      down: downMatch[1] as string,
    });
  }
  return migrations;
}

async function main(): Promise<void> {
  const [command, countArg] = process.argv.slice(2);
  const count = countArg ? Number.parseInt(countArg, 10) : undefined;
  if (!["up", "down", "status"].includes(command ?? "")) {
    console.error("Usage: tsx src/db/migrate.ts up|down|status [N]");
    process.exit(1);
  }

  await ensureTable();
  const migrations = await loadMigrations();
  const appliedRows = await query<{ version: string }>("SELECT version FROM schema_migrations ORDER BY version");
  const applied = new Set(appliedRows.rows.map((r) => r.version));

  try {
    if (command === "status") {
      for (const m of migrations) {
        console.log(`${applied.has(m.version) ? "[applied]" : "[pending]"} ${m.version}_${m.name}`);
      }
      return;
    }

    if (command === "up") {
      const pending = migrations.filter((m) => !applied.has(m.version));
      const batch = count === undefined ? pending : pending.slice(0, count);
      if (batch.length === 0) {
        console.log("Nothing to migrate.");
        return;
      }
      for (const m of batch) {
        const client = await pool.connect();
        try {
          await client.query("BEGIN");
          await client.query(m.up);
          await client.query("INSERT INTO schema_migrations (version, name) VALUES ($1, $2)", [m.version, m.name]);
          await client.query("COMMIT");
          console.log(`Applied ${m.version}_${m.name}`);
        } catch (error) {
          await client.query("ROLLBACK");
          throw new Error(`Failed to apply ${m.version}_${m.name}: ${String(error)}`);
        } finally {
          client.release();
        }
      }
      console.log(`Done: ${batch.length} migration(s) applied on ${config.db.database}.`);
      return;
    }

    // down
    const appliedSorted = migrations.filter((m) => applied.has(m.version));
    const batch = count === undefined ? appliedSorted.slice(-1) : appliedSorted.slice(-count);
    if (batch.length === 0) {
      console.log("Nothing to roll back.");
      return;
    }
    for (const m of batch.reverse()) {
      const client = await pool.connect();
      try {
        await client.query("BEGIN");
        await client.query(m.down);
        await client.query("DELETE FROM schema_migrations WHERE version = $1", [m.version]);
        await client.query("COMMIT");
        console.log(`Rolled back ${m.version}_${m.name}`);
      } catch (error) {
        await client.query("ROLLBACK");
        throw new Error(`Failed to roll back ${m.version}_${m.name}: ${String(error)}`);
      } finally {
        client.release();
      }
    }
  } finally {
    await pool.end();
  }
}

main().catch((error) => {
  console.error(error instanceof Error ? error.message : error);
  process.exit(1);
});