import { readdirSync, readFileSync } from "node:fs";
import { dirname, join } from "node:path";
import { fileURLToPath } from "node:url";
import pg from "pg";

/** Plain-SQL forward migrations tracked in `schema_migrations`. Idempotent. */
export async function migrate(databaseUrl = process.env.DATABASE_URL ?? "postgres://localhost:5432/websensor"): Promise<string[]> {
  const here = dirname(fileURLToPath(import.meta.url));
  const dir = join(here, "..", "migrations");
  const files = readdirSync(dir)
    .filter((f) => f.endsWith(".sql"))
    .sort();
  const client = new pg.Client({ connectionString: databaseUrl });
  await client.connect();
  const applied: string[] = [];
  try {
    await client.query("create table if not exists schema_migrations (name text primary key, applied_at timestamptz not null default now())");
    await client.query("select pg_advisory_lock(7245)");
    const done = new Set((await client.query<{ name: string }>("select name from schema_migrations")).rows.map((r) => r.name));
    for (const f of files) {
      if (done.has(f)) continue;
      const sql = readFileSync(join(dir, f), "utf8");
      await client.query("begin");
      try {
        await client.query(sql);
        await client.query("insert into schema_migrations (name) values ($1)", [f]);
        await client.query("commit");
        applied.push(f);
      } catch (e) {
        await client.query("rollback");
        throw e;
      }
    }
    await client.query("select pg_advisory_unlock(7245)");
  } finally {
    await client.end();
  }
  return applied;
}

if (process.argv[1] && fileURLToPath(import.meta.url) === process.argv[1]) {
  migrate()
    .then((a) => {
      console.log(a.length ? `applied: ${a.join(", ")}` : "schema up to date");
    })
    .catch((e) => {
      console.error(e);
      process.exit(1);
    });
}