/**
 * VERIFY BAR — full-database export gate (Stage 1).
 *
 * Runs the count-diff + plaintext decrypt-match that decide whether the export ships.
 * Self-contained: no live swap, no auth, no HTTP route. Run in a throwaway container on
 * the ops Docker network with the ops .env (DB creds + encryption keys).
 *
 * Proves, for Moses's ruling:
 *   1. COUNT-DIFF — every non-excluded base table's export row-count matches the OWNER count
 *      taken on a SEPARATE superuser connection (bracketed [before,after] for tables that grow
 *      during the run, e.g. api_usage_metrics). The RLS-truncation proof: api_usage_metrics
 *      (264,939 behind RLS) must come back whole under withBypassRls, not ~0 as aegis_app.
 *   2. PRESENCE — every non-excluded base table appears in the manifest (a missing table is a
 *      truncation the count-where-present check can't see).
 *   3. DECRYPT-MATCH — a synthetic kyc_customers row, seeded through the app's real write path
 *      (saveKycCustomer -> encryptPii under {tenant,tenantId}), decrypts from the bundle to the
 *      exact plaintext seeded == what the UI shows (both decrypt the same ciphertext/context).
 *   4. ENC-RESIDUAL GUARD EXERCISED — with a real encrypted row present and decrypted, the guard
 *      had something to check; the export completing (no abort) + no "ENC:" in the decrypted row
 *      proves it ran clean rather than never firing.
 *
 * Cleanup: removes the seed by id and confirms kyc_customers returns to baseline.
 */
import crypto from "crypto";
import { Client } from "pg";
import { runFullDatabaseExport } from "../server/lib/pilot-readiness-extras";
import { saveKycCustomer } from "../server/lib/kyc-agent-engine-db";
import { withTenantRls, tenantContext } from "../server/db";

const TENANT = "aegis-sovereign"; // existing kyc_customers tenant; matches the decrypt map's tenant ctx
const MARKER = "VERIFY-EXPORT-SEED";

function ownerUrl(): string {
  const u = process.env.POSTGRES_USER, p = process.env.POSTGRES_PASSWORD, d = process.env.POSTGRES_DB;
  if (!u || !p || !d) throw new Error("owner conn requires POSTGRES_USER/PASSWORD/DB in env");
  return `postgres://${u}:${encodeURIComponent(p)}@postgres:5432/${d}`;
}

async function counts(c: Client, tables: string[]): Promise<Record<string, number>> {
  const out: Record<string, number> = {};
  for (const t of tables) {
    const r = await c.query(`SELECT count(*)::int AS c FROM public."${t}"`);
    out[t] = r.rows[0].c;
  }
  return out;
}

async function main() {
  const bundleKey = crypto.randomBytes(32);
  const seedId = `${MARKER}-${crypto.randomBytes(6).toString("hex")}`;
  const seed = {
    id: seedId,
    nationalId: `${MARKER}-NID-${crypto.randomBytes(4).toString("hex")}`,
    fullName: `${MARKER} Synthetic Person`,
    dateOfBirth: "1990-01-01",
    phoneNumber: "+256700000000",
    email: `${MARKER.toLowerCase()}@example.test`,
    address: "1 Verify Lane, Kampala",
    status: "PENDING",
    riskTier: "LOW",
    tenantId: TENANT,
  };

  // OWNER connection — separate superuser session (RLS-bypassing by superuser), a different
  // path from the export's in-process withBypassRls reads.
  const owner = new Client({ connectionString: ownerUrl() });
  await owner.connect();

  const baseRes = await owner.query(
    `SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' ORDER BY table_name`,
  );
  const baseTables: string[] = baseRes.rows.map((r: any) => r.table_name);

  const kcBaseline = (await owner.query(`SELECT count(*)::int c FROM kyc_customers`)).rows[0].c;

  // 1) SEED via the app's real write path, inside a real tenant context (GUC set) so RLS
  //    passes exactly as a live KYC creation does — faithful to the write path AND its context.
  await withTenantRls(TENANT, async (tenantDb) => {
    await tenantContext.run({ tenantId: TENANT, runner: tenantDb } as any, async () => {
      await saveKycCustomer(seed as any);
    });
  });
  console.log(`[seed] kyc_customers ${seedId} written via saveKycCustomer under tenant=${TENANT} (baseline ${kcBaseline} -> ${kcBaseline + 1})`);

  // 2) owner counts BEFORE export (post-seed) — bracket start.
  const before = await counts(owner, baseTables);

  // 3) run the export (withBypassRls reads — different role/connection than `owner`).
  const result = await runFullDatabaseExport("verify-bar", bundleKey);

  // 4) owner counts AFTER export — bracket end (growing tables).
  const after = await counts(owner, baseTables);

  // 5) manifest.
  const fs = await import("fs");
  const path = await import("path");
  const manifest = JSON.parse(fs.readFileSync(path.join(result.exportDir, "MANIFEST.json"), "utf8"));
  const exportCounts: Record<string, number> = {};
  for (const t of manifest.tables) exportCounts[t.table] = t.rows;
  const excluded = new Set<string>(manifest.excludedTables.map((e: any) => e.table));

  // 6) COUNT-DIFF + PRESENCE.
  const presenceFails: string[] = [];
  const countFails: string[] = [];
  for (const t of baseTables) {
    if (excluded.has(t)) continue;
    if (!(t in exportCounts)) { presenceFails.push(t); continue; }
    const ec = exportCounts[t];
    const lo = Math.min(before[t], after[t]);
    const hi = Math.max(before[t], after[t]);
    if (ec < lo || ec > hi) countFails.push(`${t}: export=${ec} owner=[${lo},${hi}]`);
  }

  // 7) DECRYPT-MATCH on the seeded row.
  const encPath = path.join(result.exportDir, "kyc_customers.ndjson.enc");
  const blob = fs.readFileSync(encPath);
  const km = manifest.tables.find((x: any) => x.table === "kyc_customers");
  const iv = Buffer.from(km.iv, "hex");
  const tag = Buffer.from(km.authTag, "hex");
  const ctBytes = blob.subarray(12 + 16); // IV(12) || TAG(16) || ciphertext
  const dec = crypto.createDecipheriv("aes-256-gcm", bundleKey, iv);
  dec.setAuthTag(tag);
  const ndjson = Buffer.concat([dec.update(ctBytes), dec.final()]).toString("utf8");
  const rows = ndjson.split("\n").filter(Boolean).map((l) => JSON.parse(l));
  const seedRow = rows.find((r: any) => r.id === seedId);
  const nameMatch = !!seedRow && seedRow.full_name === seed.fullName;
  const nidMatch = !!seedRow && seedRow.national_id === seed.nationalId;
  const hmacPresent = !!seedRow && typeof seedRow.national_id_hmac === "string" && !seedRow.national_id_hmac.startsWith("ENC:");
  const noCiphertext = !!seedRow && !JSON.stringify(seedRow).includes("ENC:");

  // 8) REPORT.
  const aum = "api_usage_metrics";
  console.log("\n=== VERIFY BAR RESULTS ===");
  console.log(`RLS truncation test — ${aum}: export=${exportCounts[aum]} owner=[${Math.min(before[aum], after[aum])},${Math.max(before[aum], after[aum])}]  (must be ~264,939, not ~0)`);
  console.log(`manifest tables=${manifest.tables.length}  excluded=${excluded.size}  base=${baseTables.length}  (tables+excluded should equal base)`);
  console.log(`PRESENCE fails (non-excluded base table missing from manifest): ${presenceFails.length}${presenceFails.length ? " -> " + presenceFails.join(", ") : ""}`);
  console.log(`COUNT fails (export outside owner bracket): ${countFails.length}${countFails.length ? "\n  " + countFails.join("\n  ") : ""}`);
  console.log(`DECRYPT match — seeded row found=${!!seedRow} full_name=${nameMatch} national_id=${nidMatch} hmac-passthrough-present=${hmacPresent} no-ciphertext-in-row=${noCiphertext}`);
  console.log(`ENC-residual guard: export completed without abort AND decrypted seed row carries no "ENC:" prefix = ${noCiphertext} (guard was exercised on a real encrypted row)`);

  // 9) CLEANUP.
  await owner.query(`DELETE FROM kyc_customers WHERE id = $1`, [seedId]);
  const kcAfter = (await owner.query(`SELECT count(*)::int c FROM kyc_customers`)).rows[0].c;
  const cleanOk = kcAfter === kcBaseline;
  console.log(`[cleanup] deleted seed ${seedId}; kyc_customers now=${kcAfter} baseline=${kcBaseline} restored=${cleanOk}`);

  await owner.end();

  const pass = presenceFails.length === 0 && countFails.length === 0 && nameMatch && nidMatch && noCiphertext && cleanOk;
  console.log(`\n=== GATE ${pass ? "PASS" : "FAIL"} ===`);
  process.exit(pass ? 0 : 1);
}

main().catch((e) => {
  console.error("VERIFY BAR ERROR:", e);
  process.exit(2);
});