/**
 * T004a Batch B3 — PII encryption verification
 * Tables: auditor_credentials (1 col), examiner_tokens (2 cols),
 *         vendor_risk_register (1 col), key_inventory (1 col), users (1 col)
 * Total: 6 columns across 5 tables
 *
 * Per-column checks:
 *   (a) DB row stores ciphertext (isEncrypted true)
 *   (b) Function return carries plaintext
 *   (c) Wrong-context decrypt THROWS (cross-isolation)
 *   (d) Read-path list function returns plaintext
 *   (e) Consuming-read path (where applicable) returns plaintext
 *
 * Platform-key salt: "aegis-pii-platform-aes"
 * Advisor carry-forward: vendor contactEmail varchar(256) and key_inventory
 * ownerEmail varchar(192) — long fixture used to test margin.
 */

import { db } from "../server/db";
import { encryptPii, decryptPii, isEncrypted } from "../server/lib/pii-encryption";
import {
  auditorCredentials, examinerTokens, vendorRiskRegister, keyInventory, users,
} from "../shared/schema";
import { eq } from "drizzle-orm";
import crypto from "crypto";

const PLATFORM_CTX = { type: "platform" as const };
// Tenant ctx — cross-context decrypt must throw
const TENANT_CTX = { type: "tenant" as const, tenantId: "aegis-sovereign" };

const sha256 = (s: string) => crypto.createHash("sha256").update(s).digest("hex");
const EXAMINER_TOKEN_SALT = process.env.EXAMINER_TOKEN_SALT ?? "";

interface Result { check: string; pass: boolean; detail?: string }
const results: Result[] = [];

function pass(check: string, detail?: string) {
  results.push({ check, pass: true, detail });
  console.log(`  ✓ ${check}${detail ? " — " + detail : ""}`);
}
function fail(check: string, detail: string) {
  results.push({ check, pass: false, detail });
  console.error(`  ✗ FAIL: ${check} — ${detail}`);
}

// ── helpers ───────────────────────────────────────────────────────────────────

function assertEncrypted(val: string | null | undefined, label: string) {
  if (!val) { fail(label, "value is null/undefined"); return; }
  if (!isEncrypted(val)) { fail(label, `stored value is plaintext: ${val.slice(0, 40)}`); return; }
  pass(label, "ciphertext confirmed");
}

function assertPlaintext(val: string | null | undefined, expected: string, label: string) {
  if (val === expected) { pass(label, `"${expected.slice(0, 40)}"`); return; }
  fail(label, `expected "${expected.slice(0, 40)}" got "${String(val).slice(0, 40)}"`);
}

function assertCrossContextThrows(ciphertext: string, label: string) {
  try {
    decryptPii(ciphertext, TENANT_CTX);
    fail(label, "cross-context decrypt did NOT throw — isolation broken");
  } catch {
    pass(label, "cross-context decrypt throws as required");
  }
}

// ── cleanup registry ──────────────────────────────────────────────────────────

const cleanupIds: { table: string; id: string }[] = [];

// ═══════════════════════════════════════════════════════════════════════════════
// TABLE 1: auditor_credentials.auditorEmail
// ═══════════════════════════════════════════════════════════════════════════════

async function testAuditorCredentials() {
  console.log("\n[TABLE 1] auditor_credentials.auditorEmail");
  const plainEmail = "senior.auditor.b3-verify@deloitte-kampala.ug";

  // Write
  const token = crypto.randomBytes(32).toString("hex");
  const tokenHash = sha256(token);
  const expiresAt = new Date(Date.now() + 86400000);
  const [row] = await db.insert(auditorCredentials).values({
    firmName: "Deloitte Kampala B3",
    auditorEmail: encryptPii(plainEmail, PLATFORM_CTX),
    scopes: ["audit"],
    tokenHash,
    expiresAt,
    createdBy: "b3-verify-script",
  }).returning();
  cleanupIds.push({ table: "auditor_credentials", id: row.id });

  // (a) DB stores ciphertext
  const [dbRow] = await db.select().from(auditorCredentials).where(eq(auditorCredentials.id, row.id));
  assertEncrypted(dbRow.auditorEmail, "[A1a] auditorEmail stored as ciphertext");

  // (b) write-return carries plaintext (we override in return: { ...row, auditorEmail: input.auditorEmail })
  // Here we re-decrypt to simulate the function return
  const decryptedFromDb = decryptPii(dbRow.auditorEmail, PLATFORM_CTX);
  assertPlaintext(decryptedFromDb, plainEmail, "[A1b] decryptPii(dbRow) == plaintext");

  // (c) cross-context throws
  assertCrossContextThrows(dbRow.auditorEmail, "[A1c] cross-context decrypt throws");

  // (d) listAuditorCredentials decrypts
  const { listAuditorCredentials } = await import("../server/lib/pilot-readiness-extras-7");
  const list = await listAuditorCredentials();
  const found = list.find(r => r.id === row.id);
  if (!found) { fail("[A1d] list find", "row not found in listAuditorCredentials()"); }
  else {
    assertPlaintext(found.auditorEmail, plainEmail, "[A1d] listAuditorCredentials() returns plaintext");
    if (isEncrypted(found.auditorEmail)) {
      fail("[A1d-guard] list returned ciphertext, not plaintext", found.auditorEmail.slice(0, 40));
    }
  }

  // (e) verifyAuditorToken returns plaintext (consuming-read path for recordAuditorAccess)
  const { verifyAuditorToken } = await import("../server/lib/pilot-readiness-extras-7");
  const cred = await verifyAuditorToken(token);
  if (!cred) { fail("[A1e] verifyAuditorToken", "returned null for valid token"); }
  else {
    assertPlaintext(cred.auditorEmail, plainEmail, "[A1e] verifyAuditorToken() returns plaintext auditorEmail");
  }
}

// ═══════════════════════════════════════════════════════════════════════════════
// TABLE 2: examiner_tokens.examinerName + examinerEmail
// ═══════════════════════════════════════════════════════════════════════════════

async function testExaminerTokens() {
  console.log("\n[TABLE 2] examiner_tokens.examinerName + examinerEmail");
  const plainName  = "Dr. Amara Nakato Ssebuliba B3";
  const plainEmail = "amara.nakato.ssebuliba.examiner.b3@bou.go.ug"; // 45 chars — within varchar

  const rawToken = crypto.randomBytes(32).toString("hex");
  const tokenHash = sha256(EXAMINER_TOKEN_SALT + rawToken);
  const expiresAt = new Date(Date.now() + 3600 * 1000);
  const [row] = await db.insert(examinerTokens).values({
    tokenHash,
    examinerName: encryptPii(plainName, PLATFORM_CTX),
    examinerEmail: encryptPii(plainEmail, PLATFORM_CTX),
    scopesJson: ["read"],
    issuedBy: "b3-verify-script",
    expiresAt,
  }).returning();
  cleanupIds.push({ table: "examiner_tokens", id: row.id });

  // (a) DB stores ciphertext for both columns
  const [dbRow] = await db.select().from(examinerTokens).where(eq(examinerTokens.id, row.id));
  assertEncrypted(dbRow.examinerName,  "[A2a-name]  examinerName stored as ciphertext");
  assertEncrypted(dbRow.examinerEmail, "[A2a-email] examinerEmail stored as ciphertext");

  // (b) decrypt roundtrips
  assertPlaintext(decryptPii(dbRow.examinerName!, PLATFORM_CTX),  plainName,  "[A2b-name]  decryptPii(dbRow.examinerName)");
  assertPlaintext(decryptPii(dbRow.examinerEmail!, PLATFORM_CTX), plainEmail, "[A2b-email] decryptPii(dbRow.examinerEmail)");

  // (c) cross-context throws
  assertCrossContextThrows(dbRow.examinerName!,  "[A2c-name]  examinerName cross-context throws");
  assertCrossContextThrows(dbRow.examinerEmail!, "[A2c-email] examinerEmail cross-context throws");

  // (d) listExaminerTokens decrypts both
  const { listExaminerTokens } = await import("../server/lib/pilot-readiness-extras-9");
  const list = await listExaminerTokens();
  const found = list.find(r => r.id === row.id);
  if (!found) { fail("[A2d] list find", "row not found in listExaminerTokens()"); }
  else {
    assertPlaintext(found.examinerName,  plainName,  "[A2d-name]  listExaminerTokens() returns plaintext examinerName");
    assertPlaintext(found.examinerEmail, plainEmail, "[A2d-email] listExaminerTokens() returns plaintext examinerEmail");
  }

  // validateExaminerToken does NOT surface name/email — no (e) check needed for that path
}

// ═══════════════════════════════════════════════════════════════════════════════
// TABLE 3: vendor_risk_register.contactEmail  [varchar(256) margin test]
// ═══════════════════════════════════════════════════════════════════════════════

async function testVendorRiskRegister() {
  console.log("\n[TABLE 3] vendor_risk_register.contactEmail (varchar 256 margin test)");
  // 72-char email to test varchar(256) margin (ciphertext ~165 chars, safely within 256)
  const longEmail = "procurement.vendor-risk-b3-verify.2026+aegis@kyagulanyi-technologies-uganda.co.ug";
  console.log(`    fixture length: ${longEmail.length} chars`);

  const vendorKey = `b3-verify-${Date.now()}`;
  const { upsertVendor, listVendors } = await import("../server/lib/pilot-readiness-extras-10");

  // (a)+(b) via upsertVendor INSERT path
  const result = await upsertVendor({
    vendorKey,
    vendorName: "B3 Kyagulanyi Technologies",
    category: "Testing",
    contactEmail: longEmail,
    riskScore: 50,
    riskTier: "MEDIUM",
  });

  // function return must be plaintext
  assertPlaintext(result.contactEmail, longEmail, "[A3b] upsertVendor() return is plaintext");

  // DB row must be ciphertext
  const [dbRow] = await db.select().from(vendorRiskRegister)
    .where(eq(vendorRiskRegister.vendorKey, vendorKey));
  cleanupIds.push({ table: "vendor_risk_register", id: dbRow.id });
  assertEncrypted(dbRow.contactEmail, "[A3a] contactEmail stored as ciphertext");

  // varchar(256) length check — ciphertext must fit
  const ctLen = dbRow.contactEmail!.length;
  if (ctLen <= 256) { pass("[A3a-len] ciphertext fits varchar(256)", `${ctLen} chars`); }
  else { fail("[A3a-len] ciphertext exceeds varchar(256)", `${ctLen} chars`); }

  // (c) cross-context throws
  assertCrossContextThrows(dbRow.contactEmail!, "[A3c] cross-context decrypt throws");

  // (d) listVendors decrypts
  const list = await listVendors();
  const found = list.find(r => r.vendorKey === vendorKey);
  if (!found) { fail("[A3d] list find", "row not found in listVendors()"); }
  else {
    assertPlaintext(found.contactEmail, longEmail, "[A3d] listVendors() returns plaintext contactEmail");
  }

  // (e) upsertVendor UPDATE path also encrypts and decrypts
  const updatedEmail = "updated.b3-verify@kyagulanyi-technologies-uganda.co.ug";
  const updateResult = await upsertVendor({ vendorKey, contactEmail: updatedEmail, riskScore: 55 });
  assertPlaintext(updateResult.contactEmail, updatedEmail, "[A3e] upsertVendor() UPDATE return is plaintext");
  const [dbRow2] = await db.select().from(vendorRiskRegister)
    .where(eq(vendorRiskRegister.vendorKey, vendorKey));
  assertEncrypted(dbRow2.contactEmail, "[A3e-db] contactEmail ciphertext after UPDATE");
}

// ═══════════════════════════════════════════════════════════════════════════════
// TABLE 4: key_inventory.ownerEmail  [varchar(192) margin test — tightest constraint]
// ═══════════════════════════════════════════════════════════════════════════════

async function testKeyInventory() {
  console.log("\n[TABLE 4] key_inventory.ownerEmail (varchar 192 margin test — tightest)");
  // 73-char email — ciphertext ~166 chars, within varchar(192) (~26 headroom)
  const longOwnerEmail = "security-officer.key-inventory.b3-verify+2026@aegis-sovereign-operations.local";
  console.log(`    fixture length: ${longOwnerEmail.length} chars`);

  const keyId = `B3-VERIFY-KEY-${Date.now()}`;
  const encEmail = encryptPii(longOwnerEmail, PLATFORM_CTX);
  const ctLen = encEmail.length;
  console.log(`    ciphertext length: ${ctLen} chars (varchar(192) limit)`);

  if (ctLen > 192) {
    fail("[A4-precheck] ciphertext exceeds varchar(192) — fixture too long, adjust", `${ctLen} chars`);
    return;
  }
  pass("[A4-precheck] ciphertext fits varchar(192)", `${ctLen}/${192} chars`);

  await db.insert(keyInventory).values({
    keyId,
    keyType: "signing",
    algorithm: "HMAC-SHA256",
    bitLength: 256,
    ownerEmail: encEmail,
    status: "active",
    storageLocation: "env",
    rotationIntervalDays: 90,
    postQuantum: 0,
    createdBy: "b3-verify-script",
  });
  cleanupIds.push({ table: "key_inventory", id: keyId });

  // (a) DB stores ciphertext
  const [dbRow] = await db.select().from(keyInventory).where(eq(keyInventory.keyId, keyId));
  assertEncrypted(dbRow.ownerEmail, "[A4a] ownerEmail stored as ciphertext");

  // (b) decrypt roundtrip
  assertPlaintext(decryptPii(dbRow.ownerEmail!, PLATFORM_CTX), longOwnerEmail, "[A4b] decryptPii roundtrip");

  // (c) cross-context throws
  assertCrossContextThrows(dbRow.ownerEmail!, "[A4c] cross-context decrypt throws");

  // (d) listKeyInventory decrypts
  const { listKeyInventory } = await import("../server/lib/pilot-readiness-extras-11");
  const list = await listKeyInventory();
  const found = list.find(r => r.keyId === keyId);
  if (!found) { fail("[A4d] list find", "row not found in listKeyInventory()"); }
  else {
    assertPlaintext(found.ownerEmail, longOwnerEmail, "[A4d] listKeyInventory() returns plaintext ownerEmail");
  }

  // (e) getKeyInventorySummary still works (calls listKeyInventory internally)
  const { getKeyInventorySummary } = await import("../server/lib/pilot-readiness-extras-11");
  const summary = await getKeyInventorySummary();
  if (typeof summary.total === "number" && summary.total > 0) {
    pass("[A4e] getKeyInventorySummary() returns count without decrypt error", `total=${summary.total}`);
  } else {
    fail("[A4e] getKeyInventorySummary()", `unexpected result: ${JSON.stringify(summary)}`);
  }
}

// ═══════════════════════════════════════════════════════════════════════════════
// TABLE 5: users.full_name  (via storage.createUser / getUser / getUserByUsername)
// ═══════════════════════════════════════════════════════════════════════════════

async function testUsersFullName() {
  console.log("\n[TABLE 5] users.full_name");
  const plainFullName = "Beatrice Nakawuki Opio-Musoke B3 Verify";
  const username = `b3-verify-${Date.now()}`;

  // createUser via storage (the canonical write path)
  const { DatabaseStorage } = await import("../server/storage");
  const storage = new DatabaseStorage();

  const createdUser = await storage.createUser({
    username,
    password: "hashed-b3-test",
    role: "auditor" as any,
    fullName: plainFullName,
    isActive: true,
  });
  cleanupIds.push({ table: "users", id: createdUser.id });

  // (b) createUser return carries plaintext
  assertPlaintext(createdUser.fullName, plainFullName, "[A5b] createUser() return is plaintext");

  // (a) DB stores ciphertext
  const [dbRow] = await db.select().from(users).where(eq(users.id, createdUser.id));
  assertEncrypted(dbRow.fullName, "[A5a] users.full_name stored as ciphertext");

  // (c) cross-context throws
  assertCrossContextThrows(dbRow.fullName!, "[A5c] cross-context decrypt throws");

  // (d) getUser decrypts
  const fetchedById = await storage.getUser(createdUser.id);
  if (!fetchedById) { fail("[A5d-id] getUser()", "returned undefined"); }
  else {
    assertPlaintext(fetchedById.fullName, plainFullName, "[A5d-id] getUser() returns plaintext fullName");
  }

  // (d2) getUserByUsername decrypts
  const fetchedByUsername = await storage.getUserByUsername(username);
  if (!fetchedByUsername) { fail("[A5d-uname] getUserByUsername()", "returned undefined"); }
  else {
    assertPlaintext(fetchedByUsername.fullName, plainFullName, "[A5d-uname] getUserByUsername() returns plaintext fullName");
  }

  // (e) Falsy fullName safe-path — schema note: users.full_name is NOT NULL (text("full_name").notNull())
  // The null-safe guard `insertUser.fullName ? encryptPii(...) : insertUser` treats any falsy value
  // (empty string, undefined) as "skip encryption." The meaningful DB-valid falsy case is fullName: "".
  // A null/undefined insert would throw a NOT NULL constraint violation at the DB level — correct behaviour.
  // Test: "" (falsy) → guard skips encryptPii → stores "" → reads back "" without crash.
  console.log('  [A5e] users.full_name is NOT NULL (schema confirmed: text("full_name").notNull())');
  console.log("  [A5e] Testing falsy path with fullName:\"\" — guard must skip encryptPii, store empty string");
  const userEmptyName = await storage.createUser({
    username: `b3-verify-emptyname-${Date.now()}`,
    password: "hashed-b3-emptyname",
    role: "auditor" as any,
    fullName: "",
    isActive: true,
  });
  cleanupIds.push({ table: "users", id: userEmptyName.id });

  // A5e-create: createUser return must be "" (not ciphertext)
  if (userEmptyName.fullName === "") {
    pass("[A5e-create] createUser(fullName:\"\") return is \"\" — guard skipped encryptPii as required");
  } else if (isEncrypted(userEmptyName.fullName ?? "")) {
    fail("[A5e-create] createUser(fullName:\"\") returned ciphertext — guard called encryptPii on empty string", String(userEmptyName.fullName).slice(0, 40));
  } else {
    fail("[A5e-create] createUser(fullName:\"\") unexpected return", JSON.stringify(userEmptyName.fullName));
  }

  // A5e-db: DB must store "" (not ciphertext)
  const [dbRowEmpty] = await db.select().from(users).where(eq(users.id, userEmptyName.id));
  if (dbRowEmpty.fullName === "") {
    pass("[A5e-db] DB stores \"\" (not ciphertext) — NOT NULL constraint + falsy guard working together");
  } else if (isEncrypted(dbRowEmpty.fullName ?? "")) {
    fail("[A5e-db] DB stores ciphertext for empty-string fullName — guard failed to skip encryptPii", String(dbRowEmpty.fullName).slice(0, 40));
  } else {
    fail("[A5e-db] DB fullName unexpected value", JSON.stringify(dbRowEmpty.fullName));
  }

  // A5e-read: getUser must return "" without crash
  const fetchedEmpty = await storage.getUser(userEmptyName.id);
  if (!fetchedEmpty) {
    fail("[A5e-read] getUser(emptyName)", "returned undefined");
  } else if (fetchedEmpty.fullName === "") {
    pass("[A5e-read] getUser() returns \"\" — decrypt guard skips falsy value without crash");
  } else if (isEncrypted(fetchedEmpty.fullName ?? "")) {
    fail("[A5e-read] getUser() returned ciphertext — decrypt guard called decryptPii on \"\"", String(fetchedEmpty.fullName).slice(0, 40));
  } else {
    fail("[A5e-read] getUser() unexpected value", JSON.stringify(fetchedEmpty.fullName));
  }
}

// ═══════════════════════════════════════════════════════════════════════════════
// Cleanup
// ═══════════════════════════════════════════════════════════════════════════════

async function cleanup() {
  console.log("\n[Cleanup]");
  let cleaned = 0;
  for (const { table, id } of cleanupIds) {
    try {
      if (table === "auditor_credentials") {
        await db.delete(auditorCredentials).where(eq(auditorCredentials.id, id));
      } else if (table === "examiner_tokens") {
        await db.delete(examinerTokens).where(eq(examinerTokens.id, id));
      } else if (table === "vendor_risk_register") {
        await db.delete(vendorRiskRegister).where(eq(vendorRiskRegister.id, id));
      } else if (table === "key_inventory") {
        await db.delete(keyInventory).where(eq(keyInventory.keyId, id));
      } else if (table === "users") {
        await db.delete(users).where(eq(users.id, id));
      }
      cleaned++;
    } catch (e: any) {
      console.warn(`  cleanup failed ${table}/${id}: ${e?.message}`);
    }
  }
  console.log(`  cleaned ${cleaned}/${cleanupIds.length} test rows`);
  return cleaned;
}

// ═══════════════════════════════════════════════════════════════════════════════
// Main
// ═══════════════════════════════════════════════════════════════════════════════

async function main() {
  console.log("=== T004a Batch B3 Verification ===");
  console.log("Tables: auditor_credentials, examiner_tokens, vendor_risk_register, key_inventory, users");
  console.log("Columns: auditorEmail, examinerName, examinerEmail, contactEmail, ownerEmail, full_name (6 cols)");
  console.log("Platform-key context — salt: aegis-pii-platform-aes");

  try {
    await testAuditorCredentials();
    await testExaminerTokens();
    await testVendorRiskRegister();
    await testKeyInventory();
    await testUsersFullName();
  } finally {
    const cleaned = await cleanup();

    const total = results.length;
    const passed = results.filter(r => r.pass).length;
    const failed = results.filter(r => !r.pass);

    console.log(`\n=== RESULT: ${passed}/${total} PASS ===`);
    if (failed.length > 0) {
      console.log("FAILURES:");
      for (const f of failed) console.error(`  ✗ ${f.check}: ${f.detail}`);
      console.log(`cleanupRows: ${cleaned}/${cleanupIds.length}`);
      process.exit(1);
    } else {
      console.log(`ALL ${total} CHECKS PASS — Batch B3 wiring verified`);
      console.log(`cleanupRows: ${cleaned}/${cleanupIds.length}`);
      process.exit(0);
    }
  }
}

main().catch(e => { console.error("Fatal:", e); process.exit(1); });