/* oxlint-disable executor/no-try-catch-or-throw -- boundary: out-of-band migration script over a raw postgres connection */
// ---------------------------------------------------------------------------
// One-off data backfill: mint `subject` rows for principals that predate the
// subject table (added by migration 0012, written only on sightings from then
// on).
//
//   bun run db:backfill-subjects:prod   # op run --env-file=.env.production
//   bun run db:backfill-subjects:dev    # against the local PGlite dev db
//
// Source of truth is the `connection` table: before 0012, "which users exist
// under this tenant" was answerable only through connection rows, so a distinct
// (tenant, subject) there — excluding the org sentinel '' — is exactly the set
// of users the admin console under-reports until their next request.
//
//   created_at   ← the tenant+subject's oldest connection, the closest honest
//                  answer to "when did this user first appear".
//   last_seen_at ← NULL. The column means "seen on a request", no request has
//                  been recorded, and the console renders NULL as "Never" —
//                  which is true since the sighting code deployed.
//
// Idempotent — ON CONFLICT on the (tenant, external_id) unique index leaves
// rows the runtime has since minted (or a prior run inserted) untouched, so
// re-running is safe and never rewinds a live row's fields.
// Pass --dry-run to print the plan without writing.
// ---------------------------------------------------------------------------

import postgres from "postgres";
import { createId } from "@executor-js/fumadb/cuid";

const dryRun = process.argv.includes("--dry-run");

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  console.error("DATABASE_URL is not set");
  process.exit(1);
}

const sql = postgres(connectionString, { max: 1, prepare: false, ssl: "require" });

const rows = await sql<{ tenant: string; subject: string; first_connection_at: Date }[]>`
  SELECT tenant, subject, MIN(created_at) AS first_connection_at
  FROM connection
  WHERE subject <> ''
  GROUP BY tenant, subject
  ORDER BY tenant, subject
`;
console.log(`${rows.length} (tenant, subject) pair(s) with connections`);

let inserted = 0;
for (const row of rows) {
  console.log(
    `${row.tenant}  ${row.subject}  first connection ${row.first_connection_at.toISOString()}`,
  );
  if (dryRun) continue;
  const result = await sql`
    INSERT INTO subject (row_id, tenant, external_id, created_at, last_seen_at, status)
    VALUES (${createId()}, ${row.tenant}, ${row.subject}, ${row.first_connection_at}, NULL, NULL)
    ON CONFLICT (tenant, external_id) DO NOTHING
  `;
  inserted += result.count;
}

console.log(
  dryRun ? `dry run — would insert up to ${rows.length} row(s)` : `inserted ${inserted} row(s)`,
);
await sql.end();