/**
 * T004a Batch 3 verification — UBO Registrations + KYC Customers
 *
 * Checks per table:
 *   (a) round-trip: encrypt via module path → decrypt → original value recovered
 *   (b) ciphertext-on-disk: raw DB value begins with ENC:v1:; IV randomness across two rows
 *   (c) plaintext passthrough: isEncrypted dual-mode reader leaves non-ENC values unchanged
 *   (d) nullable columns: null stays null through encrypt path
 *   (e) query-reference: no DB-level filter on any (i) encrypted column
 *   (f) UBO-only: erasure sentinel encryption — encrypted sentinels decrypt back to marker strings
 *
 * Run: npx tsx scripts/t004a-batch3-verify.ts
 */

import { withBypassRls } from "../server/db";
import { uboRegistrations } from "../shared/schema-pilot-extras-7";
import { kycCustomers } from "../shared/schema-persistence";
import { encryptPii, decryptPii, isEncrypted } from "../server/lib/pii-encryption";
import { eq, and } from "drizzle-orm";
import { randomBytes } from "crypto";

const UBO_TENANT  = "t004a-batch3-ubo-verify";
const KYC_TENANT  = "t004a-batch3-kyc-verify";
const uboCtx = { type: "tenant" as const, tenantId: UBO_TENANT };
const kycCtx = { type: "tenant" as const, tenantId: KYC_TENANT };

let passCount = 0;
let failCount = 0;

function pass(label: string, detail = "") {
  console.log(`  ✅ PASS: ${label}${detail ? " — " + detail : ""}`);
  passCount++;
}
function fail(label: string, detail = "") {
  console.error(`  ❌ FAIL: ${label}${detail ? " — " + detail : ""}`);
  failCount++;
}

function newId(pfx: string) {
  return `${pfx}-${randomBytes(4).toString("hex")}`;
}

async function cleanupUbo() {
  await withBypassRls(async (bdb) => {
    await bdb.delete(uboRegistrations).where(eq(uboRegistrations.tenantId, UBO_TENANT));
  });
}
async function cleanupKyc() {
  await withBypassRls(async (bdb) => {
    await bdb.delete(kycCustomers).where(eq(kycCustomers.tenantId, KYC_TENANT));
  });
}

// ─────────────────────────────────────────────────────────────────────────────
// UBO REGISTRATIONS — 2 (i) columns: ownerName, ownerNationality
// ─────────────────────────────────────────────────────────────────────────────
async function runUboChecks() {
  console.log("\n════════════════════════════════════════════════════════════");
  console.log("  UBO REGISTRATIONS — ownerName, ownerNationality (treat i)");
  console.log("════════════════════════════════════════════════════════════\n");

  await cleanupUbo();

  const OWNER_NAME        = "Kampala Joseph Ssekandi";
  const OWNER_NATIONALITY = "Ugandan";
  const OWNER_NAT_ID      = "CM900123456UG";  // (ii) column — plaintext in this batch
  const uboRef1 = newId("UBO");
  const uboRef2 = newId("UBO");

  // ── Check (a) + (b): Round-trip + ciphertext-on-disk ──────────────────────
  console.log("--- Check (a)+(b): Round-trip + ciphertext-on-disk ---");

  const [rowA] = await withBypassRls(async (bdb) =>
    bdb.insert(uboRegistrations).values({
      uboRef: uboRef1,
      tenantId: UBO_TENANT,
      corporateCustomerRef: "CORP-VERIFY-001",
      corporateName: "Verify Corp Ltd",
      ownerName:        encryptPii(OWNER_NAME,        uboCtx),
      ownerNationalId:  OWNER_NAT_ID,                           // (ii) — not encrypted here
      ownerNationality: encryptPii(OWNER_NATIONALITY, uboCtx),
      ownershipPct: 51,
      controlType: "DIRECT",
      pepStatus: 0, sanctionsStatus: 0,
      verifiedAt: new Date(), registeredBy: "verify-script",
    }).returning()
  );

  if (!rowA) { fail("UBO row-A inserted"); return; }

  // (a) round-trip: decrypt what came back from INSERT .returning()
  const decOwnerName        = decryptPii(rowA.ownerName,        uboCtx);
  const decOwnerNationality = decryptPii(rowA.ownerNationality, uboCtx);

  decOwnerName === OWNER_NAME
    ? pass("(a) ownerName round-trip", `"${decOwnerName}"`)
    : fail("(a) ownerName round-trip", `got "${decOwnerName}"`);

  decOwnerNationality === OWNER_NATIONALITY
    ? pass("(a) ownerNationality round-trip", `"${decOwnerNationality}"`)
    : fail("(a) ownerNationality round-trip", `got "${decOwnerNationality}"`);

  // (b) ciphertext format on disk
  isEncrypted(rowA.ownerName)
    ? pass("(b) ownerName stored as ENC:v1:", rowA.ownerName.slice(0, 20) + "…")
    : fail("(b) ownerName stored as ENC:v1:", `raw="${rowA.ownerName.slice(0, 30)}"`);

  isEncrypted(rowA.ownerNationality)
    ? pass("(b) ownerNationality stored as ENC:v1:", rowA.ownerNationality.slice(0, 20) + "…")
    : fail("(b) ownerNationality stored as ENC:v1:", `raw="${rowA.ownerNationality.slice(0, 30)}"`);

  // (b) ownerNationalId stored as plaintext (ii column — not encrypted)
  rowA.ownerNationalId === OWNER_NAT_ID
    ? pass("(b) ownerNationalId stays plaintext (ii — not encrypted in this batch)")
    : fail("(b) ownerNationalId should be plaintext", `got "${rowA.ownerNationalId}"`);

  // (b) IV randomness: two rows same value → different ciphertexts
  const [rowA2] = await withBypassRls(async (bdb) =>
    bdb.insert(uboRegistrations).values({
      uboRef: uboRef2,
      tenantId: UBO_TENANT,
      corporateCustomerRef: "CORP-VERIFY-002",
      corporateName: "Verify Corp 2 Ltd",
      ownerName:        encryptPii(OWNER_NAME,        uboCtx),
      ownerNationalId:  OWNER_NAT_ID,
      ownerNationality: encryptPii(OWNER_NATIONALITY, uboCtx),
      ownershipPct: 30,
      controlType: "INDIRECT",
      pepStatus: 0, sanctionsStatus: 0,
      verifiedAt: new Date(), registeredBy: "verify-script",
    }).returning()
  );

  if (rowA2) {
    rowA.ownerName !== rowA2.ownerName
      ? pass("(b) IV randomness: ownerName ciphertexts differ across rows")
      : fail("(b) IV randomness: ownerName ciphertexts identical — deterministic encryption");
    rowA.ownerNationality !== rowA2.ownerNationality
      ? pass("(b) IV randomness: ownerNationality ciphertexts differ across rows")
      : fail("(b) IV randomness: ownerNationality ciphertexts identical — deterministic encryption");
  } else {
    fail("(b) row-A2 second UBO insert failed");
  }

  // ── Check (c): Plaintext passthrough (isEncrypted dual-mode reader) ────────
  console.log("\n--- Check (c): Plaintext passthrough ---");

  const PLAIN_VALUE = "plaintext-sentinel-value";
  const passThroughName = decryptPii(PLAIN_VALUE, uboCtx);
  passThroughName === PLAIN_VALUE
    ? pass("(c) non-ENC: plaintext passed through unchanged — ownerName path")
    : fail("(c) plaintext passthrough failed", `got "${passThroughName}"`);

  const passThroughNat = decryptPii("Uganda", uboCtx);
  passThroughNat === "Uganda"
    ? pass("(c) non-ENC: plaintext 'Uganda' passed through unchanged — ownerNationality path")
    : fail("(c) plaintext passthrough — 'Uganda' corrupted", `got "${passThroughNat}"`);

  // ── Check (e): Query-reference — no DB filter on (i) encrypted columns ─────
  console.log("\n--- Check (e): Query-reference ---");

  // Fetch by corporateCustomerRef (structural, unencrypted) — should return decrypt rows
  const byCorpRows = await withBypassRls(async (bdb) =>
    bdb.select().from(uboRegistrations)
      .where(and(
        eq(uboRegistrations.corporateCustomerRef, "CORP-VERIFY-001"),
        eq(uboRegistrations.tenantId, UBO_TENANT)
      ))
  );
  const byCorpDecrypted = byCorpRows.map(r => ({
    ...r,
    ownerName:        decryptPii(r.ownerName,        uboCtx),
    ownerNationality: decryptPii(r.ownerNationality, uboCtx),
  }));
  byCorpDecrypted.length === 1 && byCorpDecrypted[0].ownerName === OWNER_NAME
    ? pass("(e) SELECT by structural column (corporateCustomerRef) → decrypted ownerName correct")
    : fail("(e) SELECT by corporateCustomerRef row-count or ownerName mismatch", `count=${byCorpDecrypted.length}`);

  // Fetch by ownerNationalId (ii column, plaintext) — clean reference
  const byNatIdRows = await withBypassRls(async (bdb) =>
    bdb.select().from(uboRegistrations)
      .where(and(
        eq(uboRegistrations.ownerNationalId, OWNER_NAT_ID),
        eq(uboRegistrations.tenantId, UBO_TENANT)
      ))
  );
  byNatIdRows.length >= 1
    ? pass("(e) filter on ownerNationalId (ii column, plaintext) returns rows — clean reference")
    : fail("(e) filter on ownerNationalId returned no rows");

  // ── Check (f): Erasure sentinel encryption ────────────────────────────────
  console.log("\n--- Check (f): Erasure sentinel encryption ---");

  const ERASED_SENTINEL     = "[erased]";
  const REDACTED_NATIONALITY = "[redacted]";
  const encErasedName   = encryptPii(ERASED_SENTINEL,     uboCtx);
  const encErasedNat    = encryptPii(REDACTED_NATIONALITY, uboCtx);

  isEncrypted(encErasedName)
    ? pass("(f) erasure sentinel '[erased]' encrypted as ENC:v1:")
    : fail("(f) erasure sentinel '[erased]' not encrypted");
  isEncrypted(encErasedNat)
    ? pass("(f) erasure sentinel '[redacted]' encrypted as ENC:v1:")
    : fail("(f) erasure sentinel '[redacted]' not encrypted");

  decryptPii(encErasedName, uboCtx) === ERASED_SENTINEL
    ? pass("(f) decrypt of encrypted '[erased]' → '[erased]'")
    : fail("(f) decrypt of encrypted erasure sentinel failed", `got "${decryptPii(encErasedName, uboCtx)}"`);
  decryptPii(encErasedNat, uboCtx) === REDACTED_NATIONALITY
    ? pass("(f) decrypt of encrypted '[redacted]' → '[redacted]'")
    : fail("(f) decrypt of encrypted nationality sentinel failed", `got "${decryptPii(encErasedNat, uboCtx)}"`);

  await cleanupUbo();
}

// ─────────────────────────────────────────────────────────────────────────────
// KYC CUSTOMERS — 6 (i) columns: fullName, dateOfBirth, phoneNumber, email, address, photoUrl
// ─────────────────────────────────────────────────────────────────────────────
async function runKycChecks() {
  console.log("\n════════════════════════════════════════════════════════════");
  console.log("  KYC CUSTOMERS — 6 treat (i) columns");
  console.log("════════════════════════════════════════════════════════════\n");

  await cleanupKyc();

  const NATIONAL_ID = `CM-VERIFY-${randomBytes(3).toString("hex").toUpperCase()}`;  // (ii)
  const FULL_NAME   = "Grace Nakato Ssekandi";
  const DOB         = "1988-05-15";
  const PHONE       = "+256700987654";
  const EMAIL       = "grace.nakato@verify.ug";
  const ADDRESS     = "Plot 45, Kampala Road, Kampala";
  const PHOTO_URL   = "https://cdn.verify.ug/photos/grace.jpg";

  const NATIONAL_ID_2 = `CM-VERIFY2-${randomBytes(3).toString("hex").toUpperCase()}`;

  // ── Check (a) + (b): Round-trip + ciphertext-on-disk ──────────────────────
  console.log("--- Check (a)+(b): Round-trip + ciphertext-on-disk ---");

  const [rowK] = await withBypassRls(async (bdb) =>
    bdb.insert(kycCustomers).values({
      id: newId("KYC"),
      tenantId: KYC_TENANT,
      nationalId: NATIONAL_ID,                                  // (ii) — plaintext
      fullName:    encryptPii(FULL_NAME,   kycCtx),             // (i)
      dateOfBirth: encryptPii(DOB,         kycCtx),             // (i)
      phoneNumber: encryptPii(PHONE,       kycCtx),             // (i)
      email:       encryptPii(EMAIL,       kycCtx),             // (i)
      address:     encryptPii(ADDRESS,     kycCtx),             // (i)
      photoUrl:    encryptPii(PHOTO_URL,   kycCtx),             // (i)
      status: "verified", riskTier: "LOW",
    }).returning()
  );

  if (!rowK) { fail("KYC row inserted"); return; }

  // (a) round-trip decrypt
  const checks: Array<[string, string, string]> = [
    ["fullName",    decryptPii(rowK.fullName,    kycCtx), FULL_NAME],
    ["dateOfBirth", decryptPii(rowK.dateOfBirth, kycCtx), DOB],
    ["phoneNumber", decryptPii(rowK.phoneNumber, kycCtx), PHONE],
    ["email",       decryptPii(rowK.email!,       kycCtx), EMAIL],
    ["address",     decryptPii(rowK.address!,     kycCtx), ADDRESS],
    ["photoUrl",    decryptPii(rowK.photoUrl!,    kycCtx), PHOTO_URL],
  ];
  for (const [col, got, want] of checks) {
    got === want
      ? pass(`(a) ${col} round-trip`, `"${got}"`)
      : fail(`(a) ${col} round-trip`, `want="${want}" got="${got}"`);
  }

  // (b) ciphertext format
  const encCols = ["fullName", "dateOfBirth", "phoneNumber", "email", "address", "photoUrl"] as const;
  for (const col of encCols) {
    const val = rowK[col];
    isEncrypted(val!)
      ? pass(`(b) ${col} stored as ENC:v1:`, (val as string).slice(0, 20) + "…")
      : fail(`(b) ${col} stored as ENC:v1:`, `raw="${(val as string).slice(0, 30)}"`);
  }

  // (b) nationalId stays plaintext ((ii) column)
  rowK.nationalId === NATIONAL_ID
    ? pass("(b) nationalId stays plaintext (ii — not encrypted in this batch)")
    : fail("(b) nationalId should be plaintext", `got "${rowK.nationalId}"`);

  // (b) IV randomness: insert second row with same plaintext values → different ciphertexts
  const [rowK2] = await withBypassRls(async (bdb) =>
    bdb.insert(kycCustomers).values({
      id: newId("KYC"),
      tenantId: KYC_TENANT,
      nationalId: NATIONAL_ID_2,
      fullName:    encryptPii(FULL_NAME,   kycCtx),
      dateOfBirth: encryptPii(DOB,         kycCtx),
      phoneNumber: encryptPii(PHONE,       kycCtx),
      email:       encryptPii(EMAIL,       kycCtx),
      address:     encryptPii(ADDRESS,     kycCtx),
      photoUrl:    encryptPii(PHOTO_URL,   kycCtx),
      status: "verified", riskTier: "LOW",
    }).returning()
  );
  if (rowK2) {
    for (const col of encCols) {
      const c1 = rowK[col] as string;
      const c2 = rowK2[col] as string;
      c1 !== c2
        ? pass(`(b) IV randomness: ${col} ciphertexts differ across rows`)
        : fail(`(b) IV randomness: ${col} ciphertexts identical — deterministic encryption`);
    }
  } else {
    fail("(b) KYC row-2 insert failed — IV randomness check skipped");
  }

  // ── Check (c): Plaintext passthrough ────────────────────────────────────
  console.log("\n--- Check (c): Plaintext passthrough ---");

  const ptCols: Array<[string, string]> = [
    ["fullName",    "Plaintext Full Name"],
    ["dateOfBirth", "1990-01-01"],
    ["phoneNumber", "+256700000001"],
    ["email",       "plain@test.ug"],
  ];
  for (const [col, plain] of ptCols) {
    const result = decryptPii(plain, kycCtx);
    result === plain
      ? pass(`(c) ${col} plaintext passthrough`)
      : fail(`(c) ${col} plaintext passthrough failed`, `got "${result}"`);
  }

  // ── Check (d): Nullable columns — null stays null ─────────────────────────
  console.log("\n--- Check (d): Nullable columns ---");

  const [rowNull] = await withBypassRls(async (bdb) =>
    bdb.insert(kycCustomers).values({
      id: newId("KYC"),
      tenantId: KYC_TENANT,
      nationalId: `CM-NULL-${randomBytes(3).toString("hex")}`,
      fullName:    encryptPii("Null Test User", kycCtx),
      dateOfBirth: encryptPii("2000-01-01",     kycCtx),
      phoneNumber: encryptPii("+256700000099",  kycCtx),
      email:    undefined,  // nullable — NOT passed
      address:  undefined,
      photoUrl: undefined,
      status: "pending", riskTier: "MEDIUM",
    }).returning()
  );
  if (rowNull) {
    rowNull.email    == null ? pass("(d) email null preserved")   : fail("(d) email not null",   `got "${rowNull.email}"`);
    rowNull.address  == null ? pass("(d) address null preserved") : fail("(d) address not null", `got "${rowNull.address}"`);
    rowNull.photoUrl == null ? pass("(d) photoUrl null preserved"): fail("(d) photoUrl not null",`got "${rowNull.photoUrl}"`);
  } else {
    fail("(d) null-column test row insert failed");
  }

  // ── Check (e): Query-reference — no DB-level filter on (i) encrypted cols ─
  console.log("\n--- Check (e): Query-reference ---");

  // Filter on nationalId (ii column, plaintext) — clean reference
  const byNatId = await withBypassRls(async (bdb) =>
    bdb.select().from(kycCustomers)
      .where(and(
        eq(kycCustomers.nationalId, NATIONAL_ID),
        eq(kycCustomers.tenantId, KYC_TENANT)
      ))
  );
  if (byNatId.length === 1) {
    const dec = {
      ...byNatId[0],
      fullName: decryptPii(byNatId[0].fullName, kycCtx),
    };
    dec.fullName === FULL_NAME
      ? pass("(e) filter on nationalId (ii, plaintext) → decrypted fullName correct")
      : fail("(e) filter on nationalId → decrypted fullName wrong", `got "${dec.fullName}"`);
  } else {
    fail("(e) filter on nationalId returned wrong row count", `count=${byNatId.length}`);
  }

  // Filter on tenantId (structural) — all tenant rows accessible
  const allTenant = await withBypassRls(async (bdb) =>
    bdb.select().from(kycCustomers)
      .where(eq(kycCustomers.tenantId, KYC_TENANT))
  );
  allTenant.length >= 2
    ? pass(`(e) SELECT by tenantId returns ${allTenant.length} rows — all (i) columns remain encrypted on disk`)
    : fail("(e) SELECT by tenantId returned fewer rows than expected", `count=${allTenant.length}`);

  // Spot-check: all returned rows have ENC: values in fullName
  const allEncrypted = allTenant.every(r => isEncrypted(r.fullName));
  allEncrypted
    ? pass("(e) all returned rows have ENC:v1: in fullName (no plaintext leakage)")
    : fail("(e) some rows have plaintext fullName in DB — encryption not applied on write");

  await cleanupKyc();
}

// ─────────────────────────────────────────────────────────────────────────────
// Main
// ─────────────────────────────────────────────────────────────────────────────
async function main() {
  console.log("\n╔══════════════════════════════════════════════════════════════╗");
  console.log("║  T004a Batch 3 — UBO Registrations + KYC Customers verify   ║");
  console.log("╚══════════════════════════════════════════════════════════════╝");

  try {
    await runUboChecks();
    await runKycChecks();
  } catch (err) {
    console.error("\n💥 Unhandled error:", err);
    failCount++;
  } finally {
    // Belt-and-suspenders cleanup
    await cleanupUbo().catch(() => {});
    await cleanupKyc().catch(() => {});
  }

  const total = passCount + failCount;
  console.log(`\n${"─".repeat(60)}`);
  console.log(`  Result: ${passCount}/${total} PASS  |  ${failCount} FAIL`);
  if (failCount === 0) {
    console.log("  ✅ ALL PASS — Batch 3 (UBO + KYC) encryption wiring verified");
  } else {
    console.log("  ❌ FAILURES PRESENT — review output above");
  }
  console.log(`${"─".repeat(60)}\n`);

  process.exit(failCount === 0 ? 0 : 1);
}

main();