// One-shot migration runner. CRDB DDL is async; apply each statement
// on a pinned connection and retry transient "column does not exist"
// errors that fire when an index references a column whose ADD hasn't
// fully propagated.

import { readFileSync } from "node:fs"
import { Pool } from "pg"

const path = process.argv[2]
if (!path) {
  console.error("usage: apply-migration <path>")
  process.exit(1)
}

const url = process.env.COCKROACH_URL
if (!url) {
  console.error("COCKROACH_URL is required")
  process.exit(1)
}

const sql = readFileSync(path, "utf8")
const stmts = sql
  .split(/;\s*\n/)
  .map((seg) =>
    // Strip full-line SQL comments (-- …) so a statement that starts
    // with a comment block still gets kept.
    seg
      .split("\n")
      .filter((line) => !line.trim().startsWith("--"))
      .join("\n")
      .trim()
  )
  .filter((s) => s.length > 0)

const pool = new Pool({ connectionString: url })
const client = await pool.connect()
try {
  for (const s of stmts) {
    const head = s.split("\n")[0]!.slice(0, 80)
    let attempt = 0
    while (true) {
      try {
        await client.query(s)
        console.log("✓", head)
        break
      } catch (err) {
        const msg = err instanceof Error ? err.message : String(err)
        if (attempt < 5 && /column .* does not exist/.test(msg)) {
          attempt++
          await new Promise((r) => setTimeout(r, 500 * attempt))
          continue
        }
        console.error("✗", head, msg)
        throw err
      }
    }
  }
} finally {
  client.release()
  await pool.end()
}