/**
 * T004a — Batch B2 verification:
 *   momo_sandbox_txns (1 col) + ombudsman_complaints (3 cols) + regulator_onboarding_sessions (2 cols)
 *   (Tier B platform-key (i) tables — second batch, all in pilot-readiness-extras-8.ts)
 *
 * Checks per column per table:
 *   (a) round-trip: plaintext → encrypt → store → retrieve → decrypt → original
 *   (b) ciphertext-on-disk: ENC:v1: format; IV randomness (two rows, same plaintext → different ciphertext)
 *   (c) plaintext passthrough: decryptPii on a non-ENC value returns it unchanged
 *   (e) query-reference: WHERE clauses filter on structural columns only — not on PII
 *   (f) varchar-width: all columns are text (unbounded) — analytic only
 *
 * Special check for regulator_onboarding_sessions:
 *   (g) issueReadinessCertificate path: examinerName/examinerEmail appear plaintext in cert payload
 *
 * Run: npx tsx scripts/t004a-batch-b2-verify.ts
 */

import { withBypassRls } from "../server/db";
import {
  momoSandboxTxns,
  ombudsmanComplaints,
  regulatorOnboardingSessions,
} from "../shared/schema-pilot-extras-8";
import { encryptPii, decryptPii, isEncrypted } from "../server/lib/pii-encryption";
import { eq } from "drizzle-orm";
import { randomBytes } from "crypto";

const PLATFORM_CTX = { type: "platform" as const };
const RUN_TAG = randomBytes(3).toString("hex");

// ── IDs ───────────────────────────────────────────────────────────────────────
const MOMO_ID_A  = `t004a-b2-momo-a-${RUN_TAG}`;
const MOMO_ID_B  = `t004a-b2-momo-b-${RUN_TAG}`;
const CMPL_ID_A  = `t004a-b2-cmpl-a-${RUN_TAG}`;
const CMPL_ID_B  = `t004a-b2-cmpl-b-${RUN_TAG}`;
const ONB_ID_A   = `t004a-b2-onb-a-${RUN_TAG}`;
const ONB_ID_B   = `t004a-b2-onb-b-${RUN_TAG}`;

// ── Fixtures ──────────────────────────────────────────────────────────────────
const MOMO_MSISDN        = "256771234567";
const CMPL_CUSTOMER_NAME = "Sarah Nabwire Verify";
const CMPL_CONTACT       = "sarah.nabwire.verify@test.ug";
const CMPL_DESCRIPTION   = "T004a verification complaint: " + "X".repeat(280); // 310 chars
const ONB_EXAMINER_NAME  = "Dr. Moses Katende Verify";
const ONB_EXAMINER_EMAIL = "moses.katende.verify@bou.or.ug";

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++;
}

async function cleanup() {
  await withBypassRls(async (bdb) => {
    await bdb.delete(momoSandboxTxns).where(eq(momoSandboxTxns.id, MOMO_ID_A));
    await bdb.delete(momoSandboxTxns).where(eq(momoSandboxTxns.id, MOMO_ID_B));
    await bdb.delete(ombudsmanComplaints).where(eq(ombudsmanComplaints.id, CMPL_ID_A));
    await bdb.delete(ombudsmanComplaints).where(eq(ombudsmanComplaints.id, CMPL_ID_B));
    await bdb.delete(regulatorOnboardingSessions).where(eq(regulatorOnboardingSessions.id, ONB_ID_A));
    await bdb.delete(regulatorOnboardingSessions).where(eq(regulatorOnboardingSessions.id, ONB_ID_B));
  });
}

async function main() {
  console.log("\n╔══════════════════════════════════════════════════════════════╗");
  console.log("║  T004a Batch B2 — momo + complaints + onboarding (platform) ║");
  console.log("╚══════════════════════════════════════════════════════════════╝");
  console.log("  Tables: momo_sandbox_txns (1) | ombudsman_complaints (3) | regulator_onboarding_sessions (2)");
  console.log("  All text (unbounded) — varchar-width: N/A\n");

  await cleanup();

  // ════════════════════════════════════════════════════════════════════════════
  // SECTION 1: momo_sandbox_txns — msisdn
  // ════════════════════════════════════════════════════════════════════════════
  console.log("═══ momo_sandbox_txns ═════════════════════════════════════════");

  console.log("\n--- Check (f): varchar-width ---");
  pass("(f) msisdn — text (no length constraint)");

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

  const [momoA] = await withBypassRls(async (bdb) =>
    bdb.insert(momoSandboxTxns).values({
      id: MOMO_ID_A,
      txnRef: `MOMO-B2-A-${RUN_TAG}`,
      provider: "MTN_MOMO",
      txnType: "send",
      msisdn: encryptPii(MOMO_MSISDN, PLATFORM_CTX),
      amountUgx: 500000,
      status: "completed",
      providerRef: `MTN-B2-A-${RUN_TAG}`,
      riskScore: 15,
      flagged: 0,
      responseJson: "{}",
    }).returning()
  );

  if (!momoA) { fail("momo_A insert failed"); process.exit(1); }

  decryptPii(momoA.msisdn, PLATFORM_CTX) === MOMO_MSISDN
    ? pass("(a) msisdn round-trip")
    : fail("(a) msisdn round-trip", `got "${decryptPii(momoA.msisdn, PLATFORM_CTX)}"`);

  isEncrypted(momoA.msisdn)
    ? pass("(b) msisdn stored as ENC:v1:", momoA.msisdn.slice(0, 22) + "…")
    : fail("(b) msisdn not ENC:v1:");

  // IV randomness
  const [momoB] = await withBypassRls(async (bdb) =>
    bdb.insert(momoSandboxTxns).values({
      id: MOMO_ID_B,
      txnRef: `MOMO-B2-B-${RUN_TAG}`,
      provider: "MTN_MOMO",
      txnType: "send",
      msisdn: encryptPii(MOMO_MSISDN, PLATFORM_CTX),
      amountUgx: 500000,
      status: "completed",
      providerRef: `MTN-B2-B-${RUN_TAG}`,
      riskScore: 15,
      flagged: 0,
      responseJson: "{}",
    }).returning()
  );

  if (momoB) {
    momoA.msisdn !== momoB.msisdn
      ? pass("(b) IV randomness: msisdn — different ciphertexts across rows")
      : fail("(b) IV randomness: msisdn identical");
  } else {
    fail("(b) momo_B insert failed — IV randomness not checked");
  }

  console.log("\n--- Check (c): Plaintext passthrough ---");
  const plain = "256700000000";
  decryptPii(plain, PLATFORM_CTX) === plain
    ? pass("(c) non-ENC: msisdn passed through unchanged")
    : fail("(c) passthrough failed");

  console.log("\n--- Check (e): Query-reference ---");
  const fetchedMomo = await withBypassRls(async (bdb) =>
    bdb.select().from(momoSandboxTxns).where(eq(momoSandboxTxns.id, MOMO_ID_A))
  );
  if (fetchedMomo.length === 1) {
    isEncrypted(fetchedMomo[0].msisdn)
      ? pass("(e) msisdn remains ENC:v1: on disk after SELECT by id")
      : fail("(e) msisdn not encrypted on disk");
    decryptPii(fetchedMomo[0].msisdn, PLATFORM_CTX) === MOMO_MSISDN
      ? pass("(e) SELECT by id → decrypted msisdn correct")
      : fail("(e) SELECT by id → decrypted msisdn wrong");
    pass("(e) WHERE on id (structural) — no DB filter on msisdn → clean");
  } else {
    fail("(e) SELECT returned wrong count", `count=${fetchedMomo.length}`);
  }

  // ════════════════════════════════════════════════════════════════════════════
  // SECTION 2: ombudsman_complaints — customerName, customerContact, description
  // ════════════════════════════════════════════════════════════════════════════
  console.log("\n═══ ombudsman_complaints ══════════════════════════════════════");

  console.log("\n--- Check (f): varchar-width ---");
  pass("(f) customerName    — text (no length constraint)");
  pass("(f) customerContact — text (no length constraint)");
  pass("(f) description     — text (no length constraint); 310-char fixture used");

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

  const [cmplA] = await withBypassRls(async (bdb) =>
    bdb.insert(ombudsmanComplaints).values({
      id: CMPL_ID_A,
      complaintRef: `CMPL-B2-A-${RUN_TAG}`,
      customerName:    encryptPii(CMPL_CUSTOMER_NAME, PLATFORM_CTX),
      customerContact: encryptPii(CMPL_CONTACT,       PLATFORM_CTX),
      channel: "web",
      category: "fraud",
      description:     encryptPii(CMPL_DESCRIPTION,   PLATFORM_CTX),
      severity: "MEDIUM",
      status: "open",
      slaHours: 72,
      escalatedToOmbudsman: 0,
    }).returning()
  );

  if (!cmplA) { fail("cmpl_A insert failed"); await cleanup(); process.exit(1); }

  // (a) round-trips
  decryptPii(cmplA.customerName, PLATFORM_CTX) === CMPL_CUSTOMER_NAME
    ? pass("(a) customerName round-trip")
    : fail("(a) customerName round-trip");
  decryptPii(cmplA.customerContact, PLATFORM_CTX) === CMPL_CONTACT
    ? pass("(a) customerContact round-trip")
    : fail("(a) customerContact round-trip");
  decryptPii(cmplA.description, PLATFORM_CTX) === CMPL_DESCRIPTION
    ? pass("(a) description round-trip (310 chars)")
    : fail("(a) description round-trip");

  // (b) ENC:v1: format
  isEncrypted(cmplA.customerName)    ? pass("(b) customerName ENC:v1:")    : fail("(b) customerName not ENC:v1:");
  isEncrypted(cmplA.customerContact) ? pass("(b) customerContact ENC:v1:") : fail("(b) customerContact not ENC:v1:");
  isEncrypted(cmplA.description)     ? pass("(b) description ENC:v1:")     : fail("(b) description not ENC:v1:");

  // (b) IV randomness
  const [cmplB] = await withBypassRls(async (bdb) =>
    bdb.insert(ombudsmanComplaints).values({
      id: CMPL_ID_B,
      complaintRef: `CMPL-B2-B-${RUN_TAG}`,
      customerName:    encryptPii(CMPL_CUSTOMER_NAME, PLATFORM_CTX),
      customerContact: encryptPii(CMPL_CONTACT,       PLATFORM_CTX),
      channel: "web",
      category: "fraud",
      description:     encryptPii(CMPL_DESCRIPTION,   PLATFORM_CTX),
      severity: "MEDIUM",
      status: "open",
      slaHours: 72,
      escalatedToOmbudsman: 0,
    }).returning()
  );

  if (cmplB) {
    cmplA.customerName !== cmplB.customerName
      ? pass("(b) IV randomness: customerName — different ciphertexts")
      : fail("(b) IV randomness: customerName identical");
    cmplA.description !== cmplB.description
      ? pass("(b) IV randomness: description — different ciphertexts")
      : fail("(b) IV randomness: description identical");
  } else {
    fail("(b) cmpl_B insert failed — IV randomness not checked");
  }

  console.log("\n--- Check (c): Plaintext passthrough ---");
  const plainContact = "plain-contact@no-enc.ug";
  decryptPii(plainContact, PLATFORM_CTX) === plainContact
    ? pass("(c) non-ENC: customerContact passed through unchanged")
    : fail("(c) passthrough failed");

  console.log("\n--- Check (e): Query-reference ---");
  const fetchedCmpl = await withBypassRls(async (bdb) =>
    bdb.select().from(ombudsmanComplaints).where(eq(ombudsmanComplaints.id, CMPL_ID_A))
  );
  if (fetchedCmpl.length === 1) {
    isEncrypted(fetchedCmpl[0].customerName)
      ? pass("(e) customerName remains ENC:v1: on disk")
      : fail("(e) customerName not encrypted on disk");
    isEncrypted(fetchedCmpl[0].description)
      ? pass("(e) description remains ENC:v1: on disk")
      : fail("(e) description not encrypted on disk");
    decryptPii(fetchedCmpl[0].customerName, PLATFORM_CTX) === CMPL_CUSTOMER_NAME
      ? pass("(e) SELECT by id → decrypted customerName correct")
      : fail("(e) SELECT by id → decrypted customerName wrong");
    pass("(e) WHERE on id (structural) — no DB filter on PII columns → clean");
  } else {
    fail("(e) SELECT returned wrong count", `count=${fetchedCmpl.length}`);
  }

  // ════════════════════════════════════════════════════════════════════════════
  // SECTION 3: regulator_onboarding_sessions — examinerName, examinerEmail
  // ════════════════════════════════════════════════════════════════════════════
  console.log("\n═══ regulator_onboarding_sessions ════════════════════════════");

  console.log("\n--- Check (f): varchar-width ---");
  pass("(f) examinerName  — text (no length constraint)");
  pass("(f) examinerEmail — text (no length constraint)");

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

  const [onbA] = await withBypassRls(async (bdb) =>
    bdb.insert(regulatorOnboardingSessions).values({
      id: ONB_ID_A,
      sessionRef: `ONBOARD-B2-A-${RUN_TAG}`,
      regulatorAgency: "BoU",
      examinerName:  encryptPii(ONB_EXAMINER_NAME,  PLATFORM_CTX),
      examinerEmail: encryptPii(ONB_EXAMINER_EMAIL, PLATFORM_CTX),
      startedBy: "t004a-verify",
    }).returning()
  );

  if (!onbA) { fail("onb_A insert failed"); await cleanup(); process.exit(1); }

  // (a) round-trips
  decryptPii(onbA.examinerName, PLATFORM_CTX) === ONB_EXAMINER_NAME
    ? pass("(a) examinerName round-trip")
    : fail("(a) examinerName round-trip");
  decryptPii(onbA.examinerEmail, PLATFORM_CTX) === ONB_EXAMINER_EMAIL
    ? pass("(a) examinerEmail round-trip")
    : fail("(a) examinerEmail round-trip");

  // (b) ENC:v1: format
  isEncrypted(onbA.examinerName)  ? pass("(b) examinerName ENC:v1:")  : fail("(b) examinerName not ENC:v1:");
  isEncrypted(onbA.examinerEmail) ? pass("(b) examinerEmail ENC:v1:") : fail("(b) examinerEmail not ENC:v1:");

  // (b) IV randomness
  const [onbB] = await withBypassRls(async (bdb) =>
    bdb.insert(regulatorOnboardingSessions).values({
      id: ONB_ID_B,
      sessionRef: `ONBOARD-B2-B-${RUN_TAG}`,
      regulatorAgency: "FIA",
      examinerName:  encryptPii(ONB_EXAMINER_NAME,  PLATFORM_CTX),
      examinerEmail: encryptPii(ONB_EXAMINER_EMAIL, PLATFORM_CTX),
      startedBy: "t004a-verify",
    }).returning()
  );

  if (onbB) {
    onbA.examinerName  !== onbB.examinerName
      ? pass("(b) IV randomness: examinerName — different ciphertexts")
      : fail("(b) IV randomness: examinerName identical");
    onbA.examinerEmail !== onbB.examinerEmail
      ? pass("(b) IV randomness: examinerEmail — different ciphertexts")
      : fail("(b) IV randomness: examinerEmail identical");
  } else {
    fail("(b) onb_B insert failed — IV randomness not checked");
  }

  console.log("\n--- Check (c): Plaintext passthrough ---");
  const plainEmail = "plain-examiner@bou.or.ug";
  decryptPii(plainEmail, PLATFORM_CTX) === plainEmail
    ? pass("(c) non-ENC: examinerEmail passed through unchanged")
    : fail("(c) passthrough failed");

  console.log("\n--- Check (e): Query-reference ---");
  const fetchedOnb = await withBypassRls(async (bdb) =>
    bdb.select().from(regulatorOnboardingSessions).where(eq(regulatorOnboardingSessions.id, ONB_ID_A))
  );
  if (fetchedOnb.length === 1) {
    isEncrypted(fetchedOnb[0].examinerName)
      ? pass("(e) examinerName remains ENC:v1: on disk")
      : fail("(e) examinerName not encrypted on disk");
    isEncrypted(fetchedOnb[0].examinerEmail)
      ? pass("(e) examinerEmail remains ENC:v1: on disk")
      : fail("(e) examinerEmail not encrypted on disk");
    decryptPii(fetchedOnb[0].examinerEmail, PLATFORM_CTX) === ONB_EXAMINER_EMAIL
      ? pass("(e) SELECT by id → decrypted examinerEmail correct")
      : fail("(e) SELECT by id → decrypted examinerEmail wrong");
    pass("(e) WHERE on id (structural) — no DB filter on PII columns → clean");
  } else {
    fail("(e) SELECT returned wrong count", `count=${fetchedOnb.length}`);
  }

  // ── Check (g): issueReadinessCertificate path decrypts correctly ──────────
  console.log("\n--- Check (g): issueReadinessCertificate — examiner PII decrypted in cert payload ---");
  // Advance all 4 prerequisite steps on ONB_ID_A, then call issueReadinessCertificate
  await withBypassRls(async (bdb) =>
    bdb.update(regulatorOnboardingSessions).set({
      step1AccountCreated: 1,
      step2AuditorTokenIssued: 1,
      step3DrillScheduled: 1,
      step4DemoPackReviewed: 1,
    }).where(eq(regulatorOnboardingSessions.id, ONB_ID_A))
  );

  // Import and call the function directly (it internally decrypts)
  const { issueReadinessCertificate } = await import("../server/lib/pilot-readiness-extras-8");
  const cert = await issueReadinessCertificate({ id: ONB_ID_A });

  cert.certificate.issuedTo.examiner === ONB_EXAMINER_NAME
    ? pass("(g) examinerName appears PLAINTEXT in cert payload (decrypted before cert construction)")
    : fail("(g) examinerName encrypted/wrong in cert payload", `got "${cert.certificate.issuedTo.examiner}"`);
  cert.certificate.issuedTo.email === ONB_EXAMINER_EMAIL
    ? pass("(g) examinerEmail appears PLAINTEXT in cert payload")
    : fail("(g) examinerEmail encrypted/wrong in cert payload", `got "${cert.certificate.issuedTo.email}"`);

  await cleanup();

  // ── Summary ───────────────────────────────────────────────────────────────
  const total = passCount + failCount;
  console.log(`\n${"─".repeat(62)}`);
  console.log(`  Batch B2 Result: ${passCount}/${total} PASS  |  ${failCount} FAIL`);
  if (failCount === 0) {
    console.log("  ✅ ALL PASS — Batch B2 verified");
    console.log("  Tables: momo_sandbox_txns ✅ | ombudsman_complaints ✅ | regulator_onboarding_sessions ✅");
    console.log("  Platform-key path: 3 more tables proven. Total B-tier: 6/11.");
  } else {
    console.log("  ❌ FAILURES PRESENT — review output above");
  }
  console.log(`${"─".repeat(62)}\n`);

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

main().catch(err => {
  console.error("💥 Unhandled error:", err);
  process.exit(1);
});