/**
 * AS/PLATFORM/2026/008 — Tenant-Sep Chunk A behavioral verification (TS-A4).
 *
 * Tables under test (newly tenant-isolated this chunk — register §W9-SYNTHESIS
 * P-B no-tenant-column SECURITY class):
 *   ai_threat_scores      (writer: storage.createAiThreatScore — schema.ts)
 *   model_drift_history   (writer: wave5-services.recordModelDrift — schema-wave5.ts)
 *   geo_violations        (writer: geoFencing.recordViolation — schema.ts)
 *
 * Each table now (a) has a DB NOT NULL tenant_id text column, (b) is RLS-enrolled
 * (ENABLE RLS + policy USING/WITH CHECK tenant_id = current_tenant_id()), and
 * (c) has a fail-closed writer that refuses an unbound write.
 *
 * Per table, paired POS/NEG (Rule 18), as the RLS-subject role aegis_app:
 *   L1 POS  : write own-tenant row (tenant A) via the real writer → readable by A.
 *   L2 NEG  : RLS USING — tenant B does NOT see tenant A's row.
 *   L3 NEG  : RLS WITH CHECK — writer with tenant_id=B under GUC=A → REJECTED (42501).
 *   L4 NEG  : writer fail-closed — no tenant_id → throws the writer's own guard
 *             BEFORE any DB call (asserted by the exact guard message).
 *   L5 NEG  : DB belt — a direct insert with tenant_id=NULL is REJECTED by the DB
 *             (NOT NULL + WITH CHECK), so even a future unguarded writer cannot
 *             persist a null-tenant row.
 *
 * Role discipline (Rule 17): every DB-touching leg runs as aegis_app via
 * runAsTenant() — pins an appPool client, sets the tenant GUC, and activates
 * tenantContext so the module-level `db` Proxy resolves to the GUC-set client
 * (mirrors the request middleware). A0 asserts current_user='aegis_app' so the
 * isolation proof is not hollow-green (the owner role bypasses RLS). Synthetic,
 * self-cleaning rows. NO secret values are printed.
 *
 * Run: npx tsx scripts/vf-tsa4-tenant-sep-chunkA-proof.ts
 */
import { eq } from "drizzle-orm";
import { appPool, ddlPool, tenantContext, makeClientRunner, db } from "../server/db";
import { aiThreatScores, geoViolations } from "../shared/schema";
import { modelDriftHistory } from "../shared/schema-wave5";
import { storage } from "../server/storage";
import { recordModelDrift, getDriftHistory } from "../server/lib/wave5-services";
import { geoFencing } from "../server/lib/geo-fencing";

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 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-tsa4";

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)}`);

  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}`,
  );

  // ── ai_threat_scores ─────────────────────────────────────────────────────
  lines.push("== AI. ai_threat_scores — createAiThreatScore (storage) ==");

  // L1 POS: own-tenant write via the real writer, readable by A.
  const aiRow = await runAsTenant(tenantA, async () =>
    storage.createAiThreatScore({
      modelVersion: `${MARK}-ai`, score: "0.5000", confidence: "0.9000",
      explanation: MARK, tenantId: tenantA,
    } as any)
  );
  const aiSeenByA = await runAsTenant(tenantA, async () =>
    db.select().from(aiThreatScores).where(eq(aiThreatScores.id, aiRow.id))
  );
  check(
    "AI-L1 createAiThreatScore(own tenant) persists + readable by owner [RLS POS]",
    aiSeenByA.length === 1 && aiSeenByA[0].tenantId === tenantA,
    `rows=${aiSeenByA.length} tenant_id matches A=${aiSeenByA[0]?.tenantId === tenantA}`,
  );

  // L2 NEG: RLS USING — tenant B cannot see tenant A's row.
  const aiSeenByB = await runAsTenant(tenantB, async () =>
    db.select().from(aiThreatScores).where(eq(aiThreatScores.id, aiRow.id))
  );
  check(
    "AI-L2 tenant B does NOT see tenant A's score [RLS USING NEG]",
    aiSeenByB.length === 0,
    `tenantB rows=${aiSeenByB.length}`,
  );

  // L3 NEG: RLS WITH CHECK — writer with foreign tenant under GUC=A → rejected.
  let aiXtReject = false; let aiXtErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      storage.createAiThreatScore({
        modelVersion: `${MARK}-ai-xt`, score: "0.1", confidence: "0.1", tenantId: tenantB,
      } as any)
    );
  } catch (e: any) { aiXtReject = true; aiXtErr = String(e?.code ?? e?.message ?? e); }
  check(
    "AI-L3 write with tenant_id=B under GUC=A rejected [RLS WITH CHECK NEG]",
    aiXtReject && /42501|policy|row-level/i.test(aiXtErr),
    `rejected=${aiXtReject} err=${aiXtErr}`,
  );

  // L4 NEG: writer fail-closed — no tenant_id → guard throws before DB.
  let aiFcThrew = false; let aiFcMsg = "";
  try {
    await storage.createAiThreatScore({
      modelVersion: `${MARK}-ai-fc`, score: "0.1", confidence: "0.1",
    } as any);
  } catch (e: any) { aiFcThrew = true; aiFcMsg = String(e?.message ?? e); }
  check(
    "AI-L4 createAiThreatScore without tenantId throws fail-closed guard [writer-binding NEG]",
    aiFcThrew && /tenantId is required/i.test(aiFcMsg),
    `threw=${aiFcThrew} msg=${aiFcMsg}`,
  );

  // L5 NEG: DB belt — direct NULL-tenant insert rejected (NOT NULL + WITH CHECK).
  let aiNullReject = false; let aiNullErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      db.insert(aiThreatScores).values({
        modelVersion: `${MARK}-ai-null`, score: "0.1", confidence: "0.1", tenantId: null as any,
      })
    );
  } catch (e: any) { aiNullReject = true; aiNullErr = String(e?.code ?? e?.message ?? e); }
  check(
    "AI-L5 direct NULL-tenant insert rejected by DB belt [NOT NULL + WITH CHECK NEG]",
    aiNullReject && /23502|42501|null|policy|row-level/i.test(aiNullErr),
    `rejected=${aiNullReject} err=${aiNullErr}`,
  );

  // ── model_drift_history ──────────────────────────────────────────────────
  lines.push("== MD. model_drift_history — recordModelDrift (wave5-services) ==");

  // L1 POS: own-tenant write, readable by A (recordModelDrift returns void).
  await runAsTenant(tenantA, async () =>
    recordModelDrift({
      modelId: `${MARK}-md`, modelVersion: "9.9.9", psiScore: 0.11, ksScore: 0.13,
      featureDrifts: [], status: "TEST", tenantId: tenantA,
    })
  );
  const mdSeenByA = await runAsTenant(tenantA, async () => getDriftHistory(`${MARK}-md`));
  check(
    "MD-L1 recordModelDrift(own tenant) persists + readable by owner [RLS POS]",
    mdSeenByA.length >= 1 && mdSeenByA[0].tenantId === tenantA,
    `rows=${mdSeenByA.length} tenant_id matches A=${mdSeenByA[0]?.tenantId === tenantA}`,
  );

  // L2 NEG: RLS USING — tenant B cannot read it (INVERSE of the old W5b D2c gap).
  const mdSeenByB = await runAsTenant(tenantB, async () => getDriftHistory(`${MARK}-md`));
  check(
    "MD-L2 tenant B does NOT see tenant A's drift row [RLS USING NEG — closes W5b F-W5b-1]",
    mdSeenByB.length === 0,
    `tenantB rows=${mdSeenByB.length}`,
  );

  // L3 NEG: WITH CHECK — foreign tenant under GUC=A → rejected.
  let mdXtReject = false; let mdXtErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      recordModelDrift({
        modelId: `${MARK}-md-xt`, modelVersion: "9", psiScore: 0.1, ksScore: 0.1,
        featureDrifts: [], status: "TEST", tenantId: tenantB,
      })
    );
  } catch (e: any) { mdXtReject = true; mdXtErr = String(e?.code ?? e?.message ?? e); }
  check(
    "MD-L3 write with tenant_id=B under GUC=A rejected [RLS WITH CHECK NEG]",
    mdXtReject && /42501|policy|row-level/i.test(mdXtErr),
    `rejected=${mdXtReject} err=${mdXtErr}`,
  );

  // L4 NEG: writer fail-closed — no tenant_id → guard throws before DB.
  let mdFcThrew = false; let mdFcMsg = "";
  try {
    await recordModelDrift({
      modelId: `${MARK}-md-fc`, modelVersion: "9", psiScore: 0.1, ksScore: 0.1,
      featureDrifts: [], status: "TEST",
    } as any);
  } catch (e: any) { mdFcThrew = true; mdFcMsg = String(e?.message ?? e); }
  check(
    "MD-L4 recordModelDrift without tenantId throws fail-closed guard [writer-binding NEG]",
    mdFcThrew && /tenantId is required/i.test(mdFcMsg),
    `threw=${mdFcThrew} msg=${mdFcMsg}`,
  );

  // L5 NEG: DB belt — direct NULL-tenant insert rejected.
  let mdNullReject = false; let mdNullErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      db.insert(modelDriftHistory).values({
        modelId: `${MARK}-md-null`, modelVersion: "9", psiScore: "0.1", ksScore: "0.1",
        status: "TEST", tenantId: null as any,
      })
    );
  } catch (e: any) { mdNullReject = true; mdNullErr = String(e?.code ?? e?.message ?? e); }
  check(
    "MD-L5 direct NULL-tenant insert rejected by DB belt [NOT NULL + WITH CHECK NEG]",
    mdNullReject && /23502|42501|null|policy|row-level/i.test(mdNullErr),
    `rejected=${mdNullReject} err=${mdNullErr}`,
  );

  // ── geo_violations ───────────────────────────────────────────────────────
  lines.push("== GV. geo_violations — geoFencing.recordViolation ==");

  // L1 POS: own-tenant write, readable by A.
  const gvRow = await runAsTenant(tenantA, async () =>
    geoFencing.recordViolation({
      violationType: `${MARK}-gv`, sourceRegion: "RU", action: "BLOCK", tenantId: tenantA,
    })
  );
  const gvSeenByA = await runAsTenant(tenantA, async () =>
    db.select().from(geoViolations).where(eq(geoViolations.id, gvRow.id))
  );
  check(
    "GV-L1 recordViolation(own tenant) persists + readable by owner [RLS POS]",
    gvSeenByA.length === 1 && gvSeenByA[0].tenantId === tenantA,
    `rows=${gvSeenByA.length} tenant_id matches A=${gvSeenByA[0]?.tenantId === tenantA}`,
  );

  // L2 NEG: RLS USING — tenant B cannot see it.
  const gvSeenByB = await runAsTenant(tenantB, async () =>
    db.select().from(geoViolations).where(eq(geoViolations.id, gvRow.id))
  );
  check(
    "GV-L2 tenant B does NOT see tenant A's violation [RLS USING NEG]",
    gvSeenByB.length === 0,
    `tenantB rows=${gvSeenByB.length}`,
  );

  // L3 NEG: WITH CHECK — foreign tenant under GUC=A → rejected.
  let gvXtReject = false; let gvXtErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      geoFencing.recordViolation({
        violationType: `${MARK}-gv-xt`, sourceRegion: "RU", action: "BLOCK", tenantId: tenantB,
      })
    );
  } catch (e: any) { gvXtReject = true; gvXtErr = String(e?.code ?? e?.message ?? e); }
  check(
    "GV-L3 write with tenant_id=B under GUC=A rejected [RLS WITH CHECK NEG]",
    gvXtReject && /42501|policy|row-level/i.test(gvXtErr),
    `rejected=${gvXtReject} err=${gvXtErr}`,
  );

  // L4 NEG: writer fail-closed — no tenant_id → guard throws before DB.
  let gvFcThrew = false; let gvFcMsg = "";
  try {
    await geoFencing.recordViolation({
      violationType: `${MARK}-gv-fc`, sourceRegion: "RU", action: "BLOCK",
    } as any);
  } catch (e: any) { gvFcThrew = true; gvFcMsg = String(e?.message ?? e); }
  check(
    "GV-L4 recordViolation without tenantId throws fail-closed guard [writer-binding NEG]",
    gvFcThrew && /tenantId is required/i.test(gvFcMsg),
    `threw=${gvFcThrew} msg=${gvFcMsg}`,
  );

  // L5 NEG: DB belt — direct NULL-tenant insert rejected.
  let gvNullReject = false; let gvNullErr = "";
  try {
    await runAsTenant(tenantA, async () =>
      db.insert(geoViolations).values({
        violationType: `${MARK}-gv-null`, sourceRegion: "RU", action: "BLOCK", tenantId: null as any,
      })
    );
  } catch (e: any) { gvNullReject = true; gvNullErr = String(e?.code ?? e?.message ?? e); }
  check(
    "GV-L5 direct NULL-tenant insert rejected by DB belt [NOT NULL + WITH CHECK NEG]",
    gvNullReject && /23502|42501|null|policy|row-level/i.test(gvNullErr),
    `rejected=${gvNullReject} err=${gvNullErr}`,
  );

  // ── cleanup (as owner tenant A; RLS permits A to delete its own rows) ──────
  await runAsTenant(tenantA, async () => {
    await db.delete(aiThreatScores).where(eq(aiThreatScores.id, aiRow.id));
    await db.delete(geoViolations).where(eq(geoViolations.id, gvRow.id));
    await db.delete(modelDriftHistory).where(eq(modelDriftHistory.modelId, `${MARK}-md`));
  });
  // Verify cleanup left no marked rows behind (best-effort; owner-tenant scope).
  const leftAi = await runAsTenant(tenantA, async () =>
    db.select().from(aiThreatScores).where(eq(aiThreatScores.id, aiRow.id))
  );
  const leftGv = await runAsTenant(tenantA, async () =>
    db.select().from(geoViolations).where(eq(geoViolations.id, gvRow.id))
  );
  const leftMd = await runAsTenant(tenantA, async () => getDriftHistory(`${MARK}-md`));
  check(
    "ZZ cleanup removed all synthetic rows [hygiene]",
    leftAi.length === 0 && leftGv.length === 0 && leftMd.length === 0,
    `ai=${leftAi.length} gv=${leftGv.length} md=${leftMd.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);
});