/**
 * AS/PLATFORM/2026/008 — Tenant-Sep Chunk C behavioral verification (Phase 1).
 * (AS/CYBER/TENANTSEP/CHUNK-C — operator EXPLICIT GO 2026-06-19; HOLD at G0.)
 *
 * Two tables tenant-isolated this chunk (register §W5d-4 / §W5e-3 sub-findings).
 * Phase 1 = bind writers + RLS; the SET NOT NULL belt is a SEPARATE later publish,
 * so BOTH tables keep a NULLABLE tenant_id here (the NULL-write belt is RLS WITH
 * CHECK only, NOT a NOT NULL constraint, this phase).
 *
 *   T1 — threat_matches (writer: threat-intelligence-db.recordThreatMatch;
 *        schema-persistence.ts). ALREADY RLS-enrolled (TENANT_TABLES) with the
 *        STRICT/SYMMETRIC policy USING/WITH CHECK (tenant_id = current_tenant_id()).
 *        Chunk C only closes the writer-gap: recordThreatMatch is now fail-closed
 *        (no tenant ctx → throw) and stamps tenant_id = current_tenant_id() inside
 *        a dedicated withTenantRls tx (no reliance on the caller param).
 *
 *   T2 — dlp_policies (writers: dlp-scanner.createPolicy [per-tenant] +
 *        dlp-scanner.seedDefaults [GLOBAL]; schema.ts). Newly RLS-enrolled this
 *        chunk via a DEDICATED rls-init block with FOUR SPLIT per-command policies
 *        (NOT a single FOR ALL — that asymmetric FOR ALL let any tenant DELETE/UPDATE
 *        a shared global, since DELETE/UPDATE are gated by USING only):
 *          SELECT  USING       (tenant_id = current_tenant_id() OR tenant_id IS NULL)
 *          INSERT  WITH CHECK  (tenant_id = current_tenant_id())
 *          UPDATE  USING + WITH CHECK (tenant_id = current_tenant_id())
 *          DELETE  USING       (tenant_id = current_tenant_id())
 *        i.e. a tenant READS its own rows + the global built-ins (tenant_id NULL),
 *        but can only WRITE/UPDATE/DELETE its own — it can never mint a global rule,
 *        nor mutate/delete a shared global (globals are invisible to write). Globals
 *        are authored only by the system via BYPASSRLS (seedDefaults).
 *
 * Per table, paired POS/NEG (Rule 18), as the RLS-subject role aegis_app:
 *   L1 POS : write own-tenant row via the REAL writer → readable by the owner.
 *   L2 NEG : RLS USING — tenant B does NOT see tenant A's own-tenant row.
 *   L3     : (dlp only) ASYMMETRIC USING — a GLOBAL (tenant_id NULL) row IS visible
 *            to BOTH tenants (the OR tenant_id IS NULL half).
 *   L4 NEG : RLS WITH CHECK — a direct insert tenant_id=B under GUC=A → REJECTED.
 *            Both writers are GUC-self-consistent (they stamp current_tenant_id()),
 *            so a foreign-tenant write cannot reach the writer; L4 is the DB belt.
 *   L5 NEG : RLS WITH CHECK — a direct insert tenant_id=NULL under GUC=A → REJECTED.
 *            (Phase 1: rejected by RLS WITH CHECK, NOT NOT-NULL — column still
 *            nullable.) For dlp this is the key asymmetry: USING permits READING a
 *            NULL/global row, WITH CHECK forbids a tenant WRITING one.
 *   L6     : (dlp only) writer binds the GUC, NOT the caller — createPolicy invoked
 *            with a caller-supplied tenantId=B under GUC=A still persists tenant_id=A.
 *
 * dlp_policies WRITE protection (paired POS/NEG, the split-policy belt — closes the
 * old FOR-ALL global-mutation hole the architect flagged):
 *   L7 NEG : tenant A CANNOT DELETE a global (tenant_id NULL) row — DELETE USING is
 *            own-only, so the global is invisible-for-delete → 0 rows, row survives.
 *   L8 NEG : tenant A CANNOT UPDATE a global row — UPDATE USING own-only → 0 rows.
 *   L9 POS : tenant A CAN DELETE its OWN row (own-row DML still works).
 *   L10 POS: tenant A CAN UPDATE its OWN row (WITH CHECK own passes — tenant_id stays A).
 *
 * Role discipline (Rule 17): every DB-touching leg runs as aegis_app. Writers on the
 * module `db` Proxy (dlp createPolicy) run inside runAsTenant() — pins an appPool
 * client, sets the tenant GUC, activates tenantContext so the Proxy resolves to the
 * GUC-set client (mirrors the request middleware). recordThreatMatch manages its own
 * tenant tx (withTenantRls over appPool = aegis_app). A0 asserts current_user=
 * 'aegis_app' so the isolation proof is not hollow-green (the owner bypasses RLS).
 * Synthetic, self-cleaning rows (MARK-prefixed). NO secret values are printed.
 *
 * Run: npx tsx scripts/vf-chunkC-tenant-sep-proof.ts
 */
import { eq, sql } from "drizzle-orm";
import { appPool, ddlPool, tenantContext, makeClientRunner, db, withBypassRls } from "../server/db";
import { threatMatches, threatIndicators } from "../shared/schema-persistence";
import { dlpPolicies } from "../shared/schema";
import { recordThreatMatch } from "../server/lib/threat-intelligence-db";
import { dlpScanner } from "../server/lib/dlp-scanner";

let pass = 0;
let fail = 0;
const lines: string[] = [];
function check(name: string, cond: boolean, detail = "") {
  if (cond) {
    pass++;
    lines.push(`  PASS  ${name}${detail ? ` — ${detail}` : ""}`);
  } else {
    fail++;
    lines.push(`  FAIL  ${name}${detail ? ` — ${detail}` : ""}`);
  }
}

// Replicate the request path so module-`db`-proxy code (dlp createPolicy) runs as
// aegis_app under the tenant GUC (mirrors tenant-middleware). withTenantRls does
// NOT activate tenantContext; this does.
async function runAsTenant<T>(tenantId: string, fn: () => Promise<T>): Promise<T> {
  const client = await appPool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);
    const runner = makeClientRunner(client);
    const out = await tenantContext.run({ runner, tenantId }, fn);
    await client.query("COMMIT");
    return out;
  } catch (e) {
    try { await client.query("ROLLBACK"); } catch { /* noop */ }
    throw e;
  } finally {
    client.release();
  }
}

const MARK = "vf-chunkc";
const RLS_ERR = /42501|policy|row-level/i;

async function main() {
  const tRows = (await appPool.query("SELECT id, slug FROM tenants ORDER BY slug")).rows as Array<{ id: string; slug: string }>;
  const tenantA = tRows.find((r) => r.slug === "aegis-sovereign")?.id;
  const tenantB = tRows.find((r) => r.slug === "stanbic-ug")?.id;
  if (!tenantA || !tenantB) throw new Error(`could not resolve tenants A/B from ${JSON.stringify(tRows)}`);

  // Pre-clean any residue from a prior aborted run (system/BYPASSRLS, by MARK).
  await withBypassRls(async (bdb) => {
    await bdb.delete(threatMatches).where(sql`${threatMatches.indicatorId} LIKE ${MARK + "-%"}`);
    await bdb.delete(dlpPolicies).where(sql`${dlpPolicies.name} LIKE ${MARK + "-%"}`);
  });

  lines.push("== A0. Role discipline — proofs run as the RLS-subject role, not the owner ==");
  const who = await runAsTenant(tenantA, async () =>
    (await db.execute(`SELECT current_user AS u`)) as any
  );
  const currentUser = (who.rows?.[0]?.u ?? who[0]?.u) as string;
  check(
    "A0 runAsTenant connects as aegis_app (non-bypass RLS-subject role) [role discipline]",
    currentUser === "aegis_app",
    `current_user=${currentUser}`,
  );

  // ── T1: threat_matches — recordThreatMatch (own withTenantRls tx) ─────────
  lines.push("== TM. threat_matches — threat-intelligence-db.recordThreatMatch (strict/symmetric RLS) ==");

  // L1 POS — fail-closed writer, own tenant, tenant stamped from current_tenant_id().
  await recordThreatMatch({ indicatorId: `${MARK}-tm`, matchedValue: "203.0.113.7", matchedContext: MARK, riskScore: 0.5, tenantId: tenantA });
  const tmSeenByA = await runAsTenant(tenantA, async () =>
    db.select().from(threatMatches).where(eq(threatMatches.indicatorId, `${MARK}-tm`))
  );
  check(
    "TM-L1 recordThreatMatch(own tenant) persists + readable by owner, tenant stamped from GUC [RLS POS]",
    tmSeenByA.length === 1 && tmSeenByA[0].tenantId === tenantA,
    `rows=${tmSeenByA.length} tenant matches A=${tmSeenByA[0]?.tenantId === tenantA}`,
  );

  // L2 NEG — RLS USING (symmetric): tenant B does NOT see tenant A's match.
  const tmSeenByB = await runAsTenant(tenantB, async () =>
    db.select().from(threatMatches).where(eq(threatMatches.indicatorId, `${MARK}-tm`))
  );
  check("TM-L2 tenant B does NOT see tenant A's match [RLS USING NEG]", tmSeenByB.length === 0, `tenantB rows=${tmSeenByB.length}`);

  // L4 NEG — DB belt: foreign tenant under GUC=A (writer is GUC-self-consistent).
  let tmXt = false, tmXtErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      db.insert(threatMatches).values({ indicatorId: `${MARK}-tm-xt`, matchedValue: "203.0.113.8", matchedContext: MARK, riskScore: "0.5", tenantId: tenantB })
    );
  } catch (e: any) { tmXt = true; tmXtErr = String(e?.code ?? e?.message ?? e); }
  check("TM-L4 direct insert tenant_id=B under GUC=A rejected [RLS WITH CHECK NEG — DB belt]", tmXt && RLS_ERR.test(tmXtErr), `rejected=${tmXt} err=${tmXtErr}`);

  // L5 NEG — DB belt: NULL tenant under GUC=A rejected by RLS WITH CHECK (Phase 1: column still nullable).
  let tmNull = false, tmNullErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      db.insert(threatMatches).values({ indicatorId: `${MARK}-tm-null`, matchedValue: "203.0.113.9", matchedContext: MARK, riskScore: "0.5", tenantId: null as any })
    );
  } catch (e: any) { tmNull = true; tmNullErr = String(e?.code ?? e?.message ?? e); }
  check("TM-L5 direct NULL-tenant insert rejected by RLS WITH CHECK [NEG — Phase-1 nullable, RLS-only belt]", tmNull && RLS_ERR.test(tmNullErr), `rejected=${tmNull} err=${tmNullErr}`);

  // L6 NEG — fail-closed writer: no tenant ctx → throw BEFORE any DB call.
  let tmFc = false, tmFcMsg = "";
  try {
    await recordThreatMatch({ indicatorId: `${MARK}-tm-fc`, matchedValue: "203.0.113.10", matchedContext: MARK, riskScore: 0.5 });
  } catch (e: any) { tmFc = true; tmFcMsg = String(e?.message ?? e); }
  check("TM-L6 recordThreatMatch without tenantId throws fail-closed guard [writer-binding NEG]", tmFc && /tenantId is required/i.test(tmFcMsg), `threw=${tmFc} msg=${tmFcMsg}`);

  // ── T2: dlp_policies — createPolicy (per-tenant) + seedDefaults (global) ──
  lines.push("== DP. dlp_policies — dlp-scanner.createPolicy / seedDefaults (ASYMMETRIC RLS: own + globals) ==");
  const dpData = (tag: string, extra: Record<string, unknown> = {}) => ({
    name: `${MARK}-${tag}`, patternType: "custom", regex: "x", ...extra,
  });

  // L1 POS — createPolicy(own tenant) stamps current_tenant_id() → readable by owner.
  const dpRow = await runAsTenant(tenantA, async () => dlpScanner.createPolicy(dpData("dp")));
  const dpSeenByA = await runAsTenant(tenantA, async () =>
    db.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpRow.id))
  );
  check(
    "DP-L1 createPolicy(own tenant) persists + readable by owner, tenant stamped from GUC [RLS POS]",
    dpSeenByA.length === 1 && dpSeenByA[0].tenantId === tenantA,
    `rows=${dpSeenByA.length} tenant matches A=${dpSeenByA[0]?.tenantId === tenantA}`,
  );

  // L2 NEG — RLS USING own-tenant half: tenant B does NOT see tenant A's per-tenant policy.
  const dpSeenByB = await runAsTenant(tenantB, async () =>
    db.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpRow.id))
  );
  check("DP-L2 tenant B does NOT see tenant A's per-tenant policy [RLS USING own-half NEG]", dpSeenByB.length === 0, `tenantB rows=${dpSeenByB.length}`);

  // L3 — ASYMMETRIC USING: a GLOBAL (tenant_id NULL) row is visible to BOTH tenants.
  // The system authors the global via BYPASSRLS (the seedDefaults path); a tenant
  // never can (proven by L5). The successful bypass insert is itself the proof that
  // the legitimate global-author path works.
  const [dpGlobal] = await withBypassRls(async (bdb) =>
    bdb.insert(dlpPolicies).values({ name: `${MARK}-dp-global`, patternType: "custom", regex: "x", tenantId: null }).returning()
  );
  const dpGlobalSeenByA = await runAsTenant(tenantA, async () =>
    db.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpGlobal.id))
  );
  const dpGlobalSeenByB = await runAsTenant(tenantB, async () =>
    db.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpGlobal.id))
  );
  check("DP-L3a tenant A sees the GLOBAL (tenant_id NULL) policy [ASYMMETRIC USING OR-NULL POS]", dpGlobalSeenByA.length === 1 && dpGlobalSeenByA[0].tenantId === null, `rows=${dpGlobalSeenByA.length} tenant=${dpGlobalSeenByA[0]?.tenantId}`);
  check("DP-L3b tenant B sees the SAME GLOBAL policy [ASYMMETRIC USING OR-NULL POS — universal inheritance]", dpGlobalSeenByB.length === 1 && dpGlobalSeenByB[0].tenantId === null, `rows=${dpGlobalSeenByB.length} tenant=${dpGlobalSeenByB[0]?.tenantId}`);

  // L4 NEG — DB belt: foreign tenant under GUC=A rejected.
  let dpXt = false, dpXtErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      db.insert(dlpPolicies).values({ name: `${MARK}-dp-xt`, patternType: "custom", regex: "x", tenantId: tenantB })
    );
  } catch (e: any) { dpXt = true; dpXtErr = String(e?.code ?? e?.message ?? e); }
  check("DP-L4 direct insert tenant_id=B under GUC=A rejected [RLS WITH CHECK NEG — DB belt]", dpXt && RLS_ERR.test(dpXtErr), `rejected=${dpXt} err=${dpXtErr}`);

  // L5 NEG — ASYMMETRIC WITH CHECK: a tenant CANNOT mint a global (NULL) rule on the
  // request path, even though it can READ globals (L3). NULL readable, NULL not writable.
  let dpNull = false, dpNullErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      db.insert(dlpPolicies).values({ name: `${MARK}-dp-null`, patternType: "custom", regex: "x", tenantId: null as any })
    );
  } catch (e: any) { dpNull = true; dpNullErr = String(e?.code ?? e?.message ?? e); }
  check("DP-L5 tenant CANNOT mint a global: NULL-tenant insert under GUC=A rejected [ASYMMETRIC WITH CHECK NEG]", dpNull && RLS_ERR.test(dpNullErr), `rejected=${dpNull} err=${dpNullErr}`);

  // L6 POS — writer binds the GUC, NOT the caller: createPolicy with a caller-supplied
  // tenantId=B under GUC=A still persists tenant_id=A (the .values last-wins override).
  const dpCaller = await runAsTenant(tenantA, async () => dlpScanner.createPolicy(dpData("dp-caller", { tenantId: tenantB }) as any));
  const dpCallerSeen = await runAsTenant(tenantA, async () =>
    db.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpCaller.id))
  );
  check(
    "DP-L6 createPolicy ignores caller-supplied tenantId=B, binds GUC=A [writer bind-from-GUC POS]",
    dpCaller.tenantId === tenantA && dpCallerSeen.length === 1 && dpCallerSeen[0].tenantId === tenantA,
    `returned tenant=${dpCaller.tenantId} persisted tenant=${dpCallerSeen[0]?.tenantId}`,
  );

  // ── T2b: dlp_policies global-row WRITE protection (architect Rule-9 split-policy fix) ──
  // The split per-command policies make GLOBAL (tenant_id NULL) rows INVISIBLE to a
  // tenant's UPDATE/DELETE (USING own-only), while OWN-row DML still works. A single
  // FOR ALL USING (own OR global) policy would wrongly authorize global DELETE/UPDATE
  // (DELETE is gated by USING only) — this battery proves that hole is closed.
  lines.push("== DP-W. dlp_policies WRITE protection — globals immutable to tenants; own-row DML works ==");

  // L7 NEG — tenant A CANNOT DELETE a global (NULL-tenant) row (DELETE USING own-only →
  // global invisible-for-delete → 0 rows affected, row survives).
  const [dpGlobDel] = await withBypassRls(async (bdb) =>
    bdb.insert(dlpPolicies).values({ name: `${MARK}-dp-globdel`, patternType: "custom", regex: "x", tenantId: null }).returning()
  );
  const dpGlobDelRes = await runAsTenant(tenantA, async () =>
    (await db.execute(sql`DELETE FROM dlp_policies WHERE id = ${dpGlobDel.id}`)) as any
  );
  const dpGlobDelCnt = (dpGlobDelRes.rowCount ?? dpGlobDelRes?.rows?.length ?? 0) as number;
  const dpGlobDelStill = await withBypassRls(async (bdb) =>
    bdb.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpGlobDel.id))
  );
  check(
    "DP-L7 tenant A CANNOT DELETE a global (NULL-tenant) row [split DELETE USING own-only NEG]",
    dpGlobDelCnt === 0 && dpGlobDelStill.length === 1,
    `deleted=${dpGlobDelCnt} stillExists=${dpGlobDelStill.length === 1}`,
  );

  // L8 NEG — tenant A CANNOT UPDATE a global row (UPDATE USING own-only → 0 rows; regex
  // unchanged).
  const [dpGlobUpd] = await withBypassRls(async (bdb) =>
    bdb.insert(dlpPolicies).values({ name: `${MARK}-dp-globupd`, patternType: "custom", regex: "x", tenantId: null }).returning()
  );
  const dpGlobUpdRes = await runAsTenant(tenantA, async () =>
    (await db.execute(sql`UPDATE dlp_policies SET regex = 'y' WHERE id = ${dpGlobUpd.id}`)) as any
  );
  const dpGlobUpdCnt = (dpGlobUpdRes.rowCount ?? dpGlobUpdRes?.rows?.length ?? 0) as number;
  const dpGlobUpdRow = await withBypassRls(async (bdb) =>
    bdb.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpGlobUpd.id))
  );
  check(
    "DP-L8 tenant A CANNOT UPDATE a global (NULL-tenant) row [split UPDATE USING own-only NEG]",
    dpGlobUpdCnt === 0 && dpGlobUpdRow[0]?.regex === "x",
    `updated=${dpGlobUpdCnt} regexUnchanged=${dpGlobUpdRow[0]?.regex === "x"}`,
  );

  // L9 POS — tenant A CAN DELETE its OWN row (own-row DML still works under the split).
  const dpOwnDel = await runAsTenant(tenantA, async () => dlpScanner.createPolicy(dpData("dp-owndel")));
  const dpOwnDelRes = await runAsTenant(tenantA, async () =>
    (await db.execute(sql`DELETE FROM dlp_policies WHERE id = ${dpOwnDel.id}`)) as any
  );
  const dpOwnDelCnt = (dpOwnDelRes.rowCount ?? dpOwnDelRes?.rows?.length ?? 0) as number;
  check(
    "DP-L9 tenant A CAN DELETE its OWN row [split DELETE own-row POS]",
    dpOwnDelCnt === 1,
    `deleted=${dpOwnDelCnt}`,
  );

  // L10 POS — tenant A CAN UPDATE its OWN row (own-row DML still works; WITH CHECK own
  // passes because tenant_id stays A).
  const dpOwnUpd = await runAsTenant(tenantA, async () => dlpScanner.createPolicy(dpData("dp-ownupd")));
  const dpOwnUpdRes = await runAsTenant(tenantA, async () =>
    (await db.execute(sql`UPDATE dlp_policies SET regex = 'y' WHERE id = ${dpOwnUpd.id}`)) as any
  );
  const dpOwnUpdCnt = (dpOwnUpdRes.rowCount ?? dpOwnUpdRes?.rows?.length ?? 0) as number;
  const dpOwnUpdRow = await runAsTenant(tenantA, async () =>
    db.select().from(dlpPolicies).where(eq(dlpPolicies.id, dpOwnUpd.id))
  );
  check(
    "DP-L10 tenant A CAN UPDATE its OWN row [split UPDATE own-row POS]",
    dpOwnUpdCnt === 1 && dpOwnUpdRow[0]?.regex === "y",
    `updated=${dpOwnUpdCnt} regexNow=${dpOwnUpdRow[0]?.regex}`,
  );

  // ── cleanup (system/BYPASSRLS, by MARK — covers mixed per-tenant + global rows) ──
  await withBypassRls(async (bdb) => {
    await bdb.delete(threatMatches).where(sql`${threatMatches.indicatorId} LIKE ${MARK + "-%"}`);
    await bdb.delete(dlpPolicies).where(sql`${dlpPolicies.name} LIKE ${MARK + "-%"}`);
  });
  const leftTm = await withBypassRls(async (bdb) =>
    bdb.select().from(threatMatches).where(sql`${threatMatches.indicatorId} LIKE ${MARK + "-%"}`)
  );
  const leftDp = await withBypassRls(async (bdb) =>
    bdb.select().from(dlpPolicies).where(sql`${dlpPolicies.name} LIKE ${MARK + "-%"}`)
  );
  // Defensive: confirm we never created a stray threat_indicators row (recordThreatMatch
  // only bumps an EXISTING indicator; our synthetic indicatorId does not exist).
  const leftTi = await withBypassRls(async (bdb) =>
    bdb.select().from(threatIndicators).where(eq(threatIndicators.id, `${MARK}-tm`))
  );
  check(
    "ZZ cleanup removed all synthetic rows [hygiene]",
    leftTm.length === 0 && leftDp.length === 0 && leftTi.length === 0,
    `tm=${leftTm.length} dp=${leftDp.length} ti=${leftTi.length}`,
  );

  lines.push("");
  lines.push(`RESULT: ${pass} PASS / ${fail} FAIL  (total ${pass + fail})`);
  console.log(lines.join("\n"));

  await appPool.end().catch(() => {});
  await ddlPool.end().catch(() => {});
  process.exit(fail === 0 ? 0 : 1);
}

main().catch((e) => {
  console.error(lines.join("\n"));
  console.error("HARNESS ERROR:", e);
  process.exit(2);
});