/**
 * Runs one or more .sql files against the DB from .env.local, executing each
 * statement autocommitted (no wrapping transaction) so statements that cannot run
 * inside a transaction block — VACUUM, CREATE INDEX CONCURRENTLY — work. For
 * environments without the psql CLI.
 *
 *   tsx src/app/db/run-sql.ts <file.sql> [more.sql ...]
 *
 * Statement splitting is deliberately simple: it strips full-line `--` comments
 * and splits on `;`. That's correct for the hand-written files in
 * src/app/db/sql, but it is NOT a general SQL parser — don't point it at files
 * with `;` inside string literals or dollar-quoted function bodies.
 */
import { readFileSync } from "fs";
import dotenv from "dotenv";
import { Pool } from "pg";

dotenv.config({ path: ".env.local" });

const files = process.argv.slice(2);
if (files.length === 0) {
  console.error("Usage: tsx src/app/db/run-sql.ts <file.sql> [more.sql ...]");
  process.exit(1);
}

const pool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  user: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

function statementsOf(path: string): string[] {
  const raw = readFileSync(path, "utf8");
  return raw
    .split("\n")
    .filter((line) => !line.trim().startsWith("--"))
    .join("\n")
    .split(";")
    .map((s) => s.trim())
    .filter((s) => s.length > 0);
}

async function main(): Promise<void> {
  const client = await pool.connect();
  try {
    for (const file of files) {
      console.log(`\n=== ${file} ===`);
      for (const sql of statementsOf(file)) {
        const preview = sql.replace(/\s+/g, " ").slice(0, 70);
        const startedAt = Date.now();
        console.log(`> ${preview}${sql.length > 70 ? "…" : ""}`);
        await client.query(sql);
        console.log(`  ok (${Math.round((Date.now() - startedAt) / 1000)}s)`);
      }
    }
    console.log("\nAll statements complete.");
  } finally {
    client.release();
    await pool.end();
  }
}

main().catch((err) => {
  console.error("SQL run failed:", err);
  process.exit(1);
});
