#!/usr/bin/env npx tsx
/**
 * T2b — v1 → v2 PII backfill: re-encrypt existing ENC:v1: (direct-key) values into ENC:v2: (envelope).
 *
 * Mirrors t004b-migrate.ts discipline:
 *   - Phase-0 ENCRYPTED snapshot to disk before any write (the sole record of prior values).
 *   - Per row: Gate-A in-memory pre-write verify (decrypt(new v2) === plaintext), a CAS UPDATE
 *     (WHERE id + each col IS NOT DISTINCT FROM the snapshot value → stale-overwrite aborts), then
 *     Gate-B post-commit readback verify (decrypt the DB's new v2 === plaintext).
 *   - DRY_RUN default (Phase-0 only: snapshot + counts, NO DB writes).
 *   - NO plaintext PII in ANY log line (abort messages reference the snapshot path only).
 *
 * FAIL-SAFE: the per-row context must match how the row was encrypted. If it does not, decrypting the
 * v1 value throws (wrong scrypt key → GCM auth failure) BEFORE any write — the row aborts, nothing is
 * corrupted. HMAC columns (national_id_hmac, owner_national_id_hmac) are NEVER touched (they key off
 * plaintext, unchanged by re-encryption).
 *
 * Usage:
 *   DRY_RUN=true  npx tsx scripts/t2b-v1-to-v2-backfill.ts   (default — snapshot + counts, no writes)
 *   DRY_RUN=false npx tsx scripts/t2b-v1-to-v2-backfill.ts   (live; requires operator authorisation)
 * Requires DATABASE_URL + AEGIS_APP_DB_PASSWORD (envelope DEK store) + PII_AES_MASTER_KEY.
 */

import pg from "pg";
import * as fs from "fs";
import { scryptSync, randomBytes, createCipheriv, createDecipheriv } from "crypto";
import { decryptPii, encryptPii, isEncrypted, type PiiContext } from "../server/lib/pii-encryption";

const DRY_RUN = process.env.DRY_RUN !== "false";
const TS = process.env.T2B_TS || String(Date.now());
const SNAPSHOT_PATH = `./t2b-snapshot-${TS}.enc`;
const PLATFORM: PiiContext = { type: "platform" };

// ────────────────────────────────────────────────────────────────────────────
// DEK-INDEPENDENT recovery-snapshot crypto.
// The recovery snapshot must NOT be encrypted under the envelope DEK it exists to
// protect against losing (the 2026-07-08 incident: the snapshot was ENC:v2 under
// the same DEK that got dropped, so the safety net died with the key). This uses a
// direct AES-256-GCM key derived ONLY from PII_AES_MASTER_KEY (an env secret that
// survives restarts) + a fixed salt — no data_keys, no DEK, independent of the
// PII_ENVELOPE_WRITE flag. Format: "T2BSNAP:v1:" + base64(iv[12] | tag[16] | ct).
// Its decrypt companion (snapshotDecrypt) is PROVEN against the file before any
// write, so the net is verified real, not hoped-for.
// ────────────────────────────────────────────────────────────────────────────
const SNAP_MARKER = "T2BSNAP:v1:";
const SNAP_SALT = "aegis-t2b-snapshot-v1";
function snapshotKey(): Buffer {
  const master = process.env.PII_AES_MASTER_KEY;
  if (!master || master.length < 32) throw new Error("[t2b] snapshot: PII_AES_MASTER_KEY missing/short.");
  return scryptSync(master, SNAP_SALT, 32);
}
function snapshotEncrypt(plaintext: string): string {
  const key = snapshotKey();
  const iv = randomBytes(12);
  const c = createCipheriv("aes-256-gcm", key, iv);
  const enc = Buffer.concat([c.update(plaintext, "utf8"), c.final()]);
  const tag = c.getAuthTag();
  return SNAP_MARKER + Buffer.concat([iv, tag, enc]).toString("base64");
}
function snapshotDecrypt(blob: string): string {
  if (!blob.startsWith(SNAP_MARKER)) throw new Error("[t2b] snapshot: bad marker — not a T2BSNAP:v1 file.");
  const raw = Buffer.from(blob.slice(SNAP_MARKER.length), "base64");
  const iv = raw.subarray(0, 12), tag = raw.subarray(12, 28), enc = raw.subarray(28);
  const d = createDecipheriv("aes-256-gcm", snapshotKey(), iv);
  d.setAuthTag(tag);
  return Buffer.concat([d.update(enc), d.final()]).toString("utf8");
}

// Per-row context resolvers — MUST match how the app encrypts/decrypts each table.
type CtxFn = (row: Record<string, any>) => PiiContext;
const PLAT: CtxFn = () => PLATFORM;
const TENANT: CtxFn = (r) => ({ type: "tenant", tenantId: r.tenant_id ?? "" });
const PAM: CtxFn = (r) => (r.tenant_id ? { type: "tenant", tenantId: r.tenant_id } : PLATFORM);
const USER: CtxFn = (r) => ({ type: "user", userId: String(r.user_id) });

interface TableCfg { table: string; idCol: string; cols: string[]; ctx: CtxFn }
const TABLES: TableCfg[] = [
  { table: "users", idCol: "id", cols: ["full_name"], ctx: PLAT },
  { table: "suspicious_activity_reports", idCol: "id", cols: ["subject_name", "subject_id", "description"], ctx: PLAT },
  { table: "explanation_dossiers", idCol: "id", cols: ["subject_name", "subject_id", "contact_information"], ctx: PLAT },
  { table: "vendor_risk_register", idCol: "id", cols: ["contact_email"], ctx: PLAT },
  { table: "key_inventory", idCol: "id", cols: ["owner_email"], ctx: PLAT },
  { table: "model_cards_v2", idCol: "id", cols: ["owner_email"], ctx: PLAT },
  { table: "auditor_credentials", idCol: "id", cols: ["auditor_email"], ctx: PLAT },
  { table: "examiner_tokens", idCol: "id", cols: ["examiner_name", "examiner_email"], ctx: PLAT },
  { table: "regulator_onboarding_sessions", idCol: "id", cols: ["examiner_name", "examiner_email"], ctx: PLAT },
  { table: "ombudsman_complaints", idCol: "id", cols: ["customer_name", "customer_contact", "description"], ctx: PLAT },
  { table: "momo_sandbox_txns", idCol: "id", cols: ["msisdn"], ctx: PLAT },
  { table: "decision_scoring_outputs", idCol: "id", cols: ["reasoning_text", "factors_json"], ctx: TENANT },
  { table: "kyc_customers", idCol: "id", cols: ["national_id", "full_name", "date_of_birth", "phone_number", "email", "address", "photo_url"], ctx: TENANT },
  { table: "ubo_registrations", idCol: "id", cols: ["owner_name", "owner_national_id", "owner_nationality"], ctx: TENANT },
  { table: "scim_provisions", idCol: "id", cols: ["user_name", "display_name", "email"], ctx: TENANT },
  { table: "dsar_requests", idCol: "id", cols: ["subject_name", "subject_email", "subject_phone", "subject_national_id"], ctx: TENANT },
  { table: "pam_sessions", idCol: "id", cols: ["account_name", "user_name"], ctx: PAM },
  { table: "fincrime_str_reports", idCol: "id", cols: ["encrypted_narrative"], ctx: TENANT },
  { table: "fincrime_case_evidence", idCol: "id", cols: ["encrypted_note"], ctx: TENANT },
  { table: "biometric_profiles", idCol: "user_id", cols: ["keystroke_baseline_json", "mouse_baseline_json"], ctx: USER },
];

class AbortErr extends Error { constructor(m: string) { super(m); this.name = "AbortErr"; } }

const stats = { migrated: 0, skipped: 0, tables: 0 };
// Per-table snapshot: table → rowId → { col: v1-ciphertext | null }
type Snap = Record<string, Record<string, Record<string, string | null>>>;

async function buildSnapshot(pool: pg.Pool): Promise<Snap> {
  const snap: Snap = {};
  for (const cfg of TABLES) {
    snap[cfg.table] = {};
    let rows: any[];
    try {
      const res = await pool.query(`SELECT * FROM ${cfg.table} WHERE ${cfg.cols[0]} LIKE 'ENC:v1:%'`);
      rows = res.rows;
    } catch (e) {
      console.log(`[t2b] [phase-0] ${cfg.table}: SKIPPED (query failed: ${(e as Error).message.slice(0, 60)})`);
      continue;
    }
    for (const row of rows) {
      const s: Record<string, string | null> = { __ctx_tenant_id: row.tenant_id ?? null, __ctx_user_id: row.user_id ?? null };
      for (const c of cfg.cols) s[c] = (row[c] as string | null) ?? null;
      snap[cfg.table][String(row[cfg.idCol])] = s;
    }
    console.log(`[t2b] [phase-0] ${cfg.table}: ${rows.length} v1 row(s) to migrate`);
  }
  return snap;
}

async function migrateTable(pool: pg.Pool, cfg: TableCfg, tableSnap: Record<string, Record<string, string | null>>): Promise<void> {
  const setClauses = cfg.cols.map((c, i) => `${c} = $${cfg.cols.length + i + 2}`).join(", ");
  const whereClauses = cfg.cols.map((c, i) => `${c} IS NOT DISTINCT FROM $${i + 2}`).join(" AND ");
  const updSQL = `UPDATE ${cfg.table} SET ${setClauses} WHERE ${cfg.idCol} = $1 AND ${whereClauses}`;
  let migrated = 0;

  for (const rowId of Object.keys(tableSnap)) {
    const rowSnap = tableSnap[rowId];
    const ctx = cfg.ctx({ tenant_id: rowSnap.__ctx_tenant_id, user_id: rowSnap.__ctx_user_id });

    // Decrypt each v1 col → plaintext (FAIL-SAFE: wrong ctx throws here, before any write), then v2-encrypt.
    const plain: Record<string, string | null> = {};
    const cipher: Record<string, string | null> = {};
    for (const c of cfg.cols) {
      const v1 = rowSnap[c];
      if (v1 === null) { plain[c] = null; cipher[c] = null; continue; }
      if (!isEncrypted(v1) || v1.startsWith("ENC:v2:")) { plain[c] = null; cipher[c] = null; continue; } // not v1 → skip col
      const pt = await decryptPii(v1, ctx);           // v1 direct-key path
      const v2 = await encryptPii(pt, ctx);           // envelope (flag on OR envelopeEncrypt) — but see note
      plain[c] = pt; cipher[c] = v2;
      // Gate A: the new v2 must decrypt back to the same plaintext.
      if ((await decryptPii(v2, ctx)) !== pt) throw new AbortErr(`[t2b] Gate A fail — ${cfg.table} id=${rowId} col=${c}. Recovery: ${SNAPSHOT_PATH}`);
      if (!v2.startsWith("ENC:v2:")) throw new AbortErr(`[t2b] encryptPii did not emit v2 for ${cfg.table} id=${rowId} col=${c} — is PII_ENVELOPE_WRITE=on? Recovery: ${SNAPSHOT_PATH}`);
    }
    // If no column actually needed migration, skip the row.
    if (cfg.cols.every((c) => cipher[c] === null)) { stats.skipped++; continue; }

    const snapVals = cfg.cols.map((c) => rowSnap[c]);
    const cipherVals = cfg.cols.map((c) => (cipher[c] === null ? rowSnap[c] : cipher[c])); // keep non-v1 cols as-is
    const client = await pool.connect();
    try {
      await client.query("BEGIN");
      const upd = await client.query(updSQL, [rowId, ...snapVals, ...cipherVals]);
      if ((upd.rowCount ?? 0) === 0) { await client.query("ROLLBACK"); throw new AbortErr(`[t2b] Stale-overwrite prevented — ${cfg.table} id=${rowId} changed since snapshot. Recovery: ${SNAPSHOT_PATH}`); }
      await client.query("COMMIT");
    } catch (e) { try { await client.query("ROLLBACK"); } catch {} finally { client.release(); } if (e instanceof AbortErr) throw e; else { client.release(); throw e; } }
    client.release();

    // Gate B: read back and verify each migrated col decrypts to the snapshot plaintext.
    const rb = await pool.query(`SELECT ${cfg.cols.join(", ")} FROM ${cfg.table} WHERE ${cfg.idCol} = $1`, [rowId]);
    const dbRow = rb.rows[0];
    for (const c of cfg.cols) {
      if (cipher[c] === null) continue;
      const dbVal = dbRow[c] as string;
      if (!dbVal || !dbVal.startsWith("ENC:v2:") || (await decryptPii(dbVal, ctx)) !== plain[c]) {
        throw new AbortErr(`[t2b] Gate B fail — ${cfg.table} id=${rowId} col=${c}: readback mismatch. Recovery: ${SNAPSHOT_PATH}`);
      }
    }
    console.log(`[t2b] confirmed ${cfg.table} id=${rowId}`);
    migrated++; stats.migrated++;
  }
  if (migrated > 0) console.log(`[t2b] ${cfg.table}: ${migrated} row(s) migrated+confirmed`);
}

// Re-read the on-disk snapshot, decrypt it DEK-independently, and confirm every
// captured v1 value decrypts back to plaintext. Proves the recovery net is real
// (the incident's snapshot was never proven decryptable — it was dead when needed).
// No plaintext is logged — only the recoverable/failed counts.
async function proveSnapshotRecoverable(): Promise<{ total: number; recovered: number; failed: number }> {
  const snap: Snap = JSON.parse(snapshotDecrypt(fs.readFileSync(SNAPSHOT_PATH, "utf8")));
  let total = 0, recovered = 0, failed = 0;
  for (const cfg of TABLES) {
    const tbl = snap[cfg.table] ?? {};
    for (const rowId of Object.keys(tbl)) {
      total++;
      const rowSnap = tbl[rowId];
      const ctx = cfg.ctx({ tenant_id: rowSnap.__ctx_tenant_id, user_id: rowSnap.__ctx_user_id });
      let ok = true;
      for (const c of cfg.cols) {
        const v1 = rowSnap[c];
        if (v1 === null || !isEncrypted(v1) || v1.startsWith("ENC:v2:")) continue;
        try { if (typeof (await decryptPii(v1, ctx)) !== "string") ok = false; } catch { ok = false; }
      }
      if (ok) recovered++; else failed++;
    }
  }
  return { total, recovered, failed };
}

async function main(): Promise<void> {
  console.log(`[t2b] ═══ v1→v2 PII backfill ═══ DRY_RUN=${DRY_RUN}${DRY_RUN ? " (snapshot+counts, NO writes)" : " — LIVE WRITE MODE"}`);
  const connectionString = process.env.PROD_DATABASE_URL ?? process.env.DATABASE_URL;
  if (!connectionString) throw new Error("[t2b] FATAL: DATABASE_URL not set.");
  if (!process.env.PII_AES_MASTER_KEY || process.env.PII_AES_MASTER_KEY.length < 32) throw new Error("[t2b] FATAL: PII_AES_MASTER_KEY missing.");
  const pool = new pg.Pool({ connectionString });
  try {
    const snap = await buildSnapshot(pool);
    const total = Object.values(snap).reduce((s, t) => s + Object.keys(t).length, 0);
    fs.writeFileSync(SNAPSHOT_PATH, snapshotEncrypt(JSON.stringify(snap)), "utf8");
    console.log(`[t2b] [phase-0] DEK-independent snapshot written: ${SNAPSHOT_PATH} — ${total} v1 row(s) total`);

    // Prove the safety net is REAL before any write: re-read the snapshot from disk,
    // decrypt it (DEK-independent), and confirm every captured v1 value decrypts back
    // to plaintext. A snapshot you cannot prove you can decrypt is a hope, not a net.
    const rec = await proveSnapshotRecoverable();
    console.log(`[t2b] [phase-0] snapshotDecrypt PROOF: ${rec.recovered}/${rec.total} row(s) recoverable to plaintext${rec.failed ? ` — ${rec.failed} FAILED` : ""}.`);
    if (rec.total !== total || rec.recovered !== total || rec.failed > 0) {
      throw new Error(`[t2b] FATAL: snapshot NOT proven fully recoverable (${rec.recovered}/${total}, failed=${rec.failed}) — the safety net is not real. Refusing to proceed.`);
    }

    if (DRY_RUN) {
      console.log(`[t2b] [dry-run] ${total} v1 row(s) would be migrated. No DB writes. Snapshot proven ${rec.recovered}/${total} recoverable. Set DRY_RUN=false + PII_ENVELOPE_WRITE=on to run live.`);
      return;
    }
    if (process.env.PII_ENVELOPE_WRITE?.toLowerCase() !== "on") {
      throw new Error("[t2b] FATAL: live run requires PII_ENVELOPE_WRITE=on (so encryptPii emits v2). Aborting to avoid re-writing v1.");
    }
    for (const cfg of TABLES) { stats.tables++; await migrateTable(pool, cfg, snap[cfg.table] ?? {}); }

    // Final proof: 0 v1 rows remain across all configured columns.
    let remaining = 0;
    for (const cfg of TABLES) {
      try { const r = await pool.query(`SELECT count(*)::int AS c FROM ${cfg.table} WHERE ${cfg.cols[0]} LIKE 'ENC:v1:%'`); remaining += r.rows[0].c; } catch {}
    }
    console.log(`[t2b] ─── SUMMARY: migrated=${stats.migrated} skipped=${stats.skipped} — v1 rows remaining (want 0)=${remaining} — snapshot=${SNAPSHOT_PATH}`);
    if (remaining > 0) console.log(`[t2b] WARN: ${remaining} v1 row(s) remain — re-run or investigate.`);
  } finally { await pool.end(); }
}

main().catch((e: Error) => { console.error(`[t2b] FATAL: ${e.message}`); process.exit(1); });