/**
 * T003 RLS enforcement probe suite.
 *
 * Substantiates database-layer Row Level Security enforcement (architect Rule 9
 * PASS 2026-06-03, T003 design brief v1.1-RATIFIED). Run with:
 *
 *   npx tsx scripts/t003-rls-probe-suite.ts
 *
 * Requires: DATABASE_URL + AEGIS_APP_DB_PASSWORD environment variables.
 * Safe to run against dev or prod (seeds and cleans up its own probe rows).
 * Superuser pool is used ONLY for seed/cleanup; all assertion queries run as
 * aegis_app through appPool.
 *
 * Coverage (16 probes across 7 architect-required classes):
 *
 *   CLASS 1 — Enforcement under aegis_app (probes 1–3):
 *     1. Correct GUC → own-tenant row visible (RLS permits)
 *     2. Wrong tenant GUC → 0 rows (RLS denies)
 *     3. Empty-string GUC → 0 rows (§13.0 fail-closed, empty path)
 *
 *   CLASS 2 — Cross-tenant isolation (probes 4, 11):
 *     4. Two rows, two tenants; tenant-A context sees only idA, not idB
 *    11. withTenantRls (DSAR worker Shape 2a): own-tenant DSAR visible;
 *        cross-tenant DSAR → 0 rows (PDPO-critical isolation)
 *
 *   CLASS 3 — §13.0 fail-closed (probes 3, 5, 9):
 *     5. Empty-string GUC → 0 rows on kyt_results (second table)
 *     9. NEVER-SET GUC → 0 rows (no set_config called at all — the
 *        actual pre-auth / background-worker / raw-db-call state)
 *
 *   CLASS 4 — audit_chain_entries DML denial, Option B + Option Y (probes 6–8, 14):
 *     6. aegis_app INSERT denied (42501 insufficient_privilege)
 *     7. aegis_app DELETE denied (42501 insufficient_privilege)
 *     8. aegis_app SELECT allowed (read path intact)
 *    14. aegis_rls_bypass INSERT denied (42501) — Option Y structural boundary
 *        (bypass role cannot write the chain despite BYPASSRLS + broad grants)
 *
 *   CLASS 5 — Background-worker bypass scope (probes 10, 12, 13):
 *    10. backup_records INSERT via aegis_rls_bypass succeeds (BACKUP-SYS)
 *    12. sbom_snapshots INSERT via aegis_rls_bypass succeeds (SBOM-SYS)
 *    13. pilot-count isolation: bare aegis_app → 0; bypass → ≥1 (PILOT-COUNT)
 *
 *   CLASS 6 — Application critical-path bypass correctness (probe 15):
 *    15. LOGIN-UTR bypass: bare aegis_app under pre-auth GUC → 0 UTR rows
 *        (the regression state); bypass role → 1 UTR row (the fix).
 *        Mandatory regression probe for any change to the login UTR lookup
 *        or the withBypassRls bypass register (LOGIN-UTR call site).
 *
 *   CLASS 7 — VARCHAR predicate enforcement (probe 16):
 *    16. billing_invoices (varchar tenant_id — produces ((tenant_id)::text =
 *        current_tenant_id()) normalized form): own-tenant row visible (1),
 *        wrong-tenant → 0 rows. Functionally proves the ::text cast form
 *        enforces isolation, not just exists in pg_policy.
 *        Added 2026-06-04: Probes 1/4/11 all target TEXT-predicate tables;
 *        this probe closes the gap by empirically verifying a varchar-predicate
 *        table enforces correctly under aegis_app.
 *
 * Design record: docs/regulatory/T003_DESIGN_BRIEF.md
 * Implementation: server/lib/rls-init.ts, server/db.ts
 * FU record: docs/regulatory/FOLLOW-UPS.md (FU-053)
 */

import pg from "pg";

const { Pool } = pg;

// ── Pool construction ─────────────────────────────────────────────────────────

if (!process.env.DATABASE_URL) {
  console.error("FATAL: DATABASE_URL is not set.");
  process.exit(1);
}
if (!process.env.AEGIS_APP_DB_PASSWORD) {
  console.error("FATAL: AEGIS_APP_DB_PASSWORD is not set.");
  process.exit(1);
}

// Superuser pool — used ONLY for probe-row seeding and cleanup.
// Never used for assertion queries (that would defeat the probe).
const suPool = new Pool({ connectionString: process.env.DATABASE_URL });

// appPool — aegis_app restricted role. All assertion queries run here.
// Mirrors the construction in server/db.ts (Q1 guardrail).
function buildAppConnectionString(): string {
  const url = new URL(process.env.DATABASE_URL!);
  url.username = "aegis_app";
  url.password = process.env.AEGIS_APP_DB_PASSWORD!;
  return url.toString();
}
const appPool = new Pool({ connectionString: buildAppConnectionString() });

// ── Result tracking ───────────────────────────────────────────────────────────

let passed = 0;
let failed = 0;
const results: Array<{ probe: string; result: "PASS" | "FAIL"; detail?: string }> = [];

function pass(probe: string): void {
  passed++;
  results.push({ probe, result: "PASS" });
}

function fail(probe: string, detail: string): void {
  failed++;
  results.push({ probe, result: "FAIL", detail });
}

// ── Unique run prefix — avoids collisions with concurrent dev work ─────────────

const RUN_TS = Date.now();
const PROBE_PREFIX = `t003-rls-probe-${RUN_TS}`;

// ── Main probe sequence ───────────────────────────────────────────────────────

async function run(): Promise<void> {
  // ── CLASS 1 + 3 (probes 1–3, 9): incident_slas ─────────────────────────────
  //
  // Seed one row as superuser. Probes 1–3 use BEGIN + set_config variants.
  // Probe 9 (never-set) uses a fresh connection with NO set_config at all.

  let incidentSlaId: number | null = null;
  // Rule 18 interlock flag: set true when Probe 1 positive-enforcement passes.
  // Probes 2, 3, 9 are denial probes that would pass in a deny-all broken state
  // (deny-all yields 0 rows, which is the expected denial result — hollow green).
  // They are only meaningful evidence of correct isolation when the paired positive
  // probe also passed in the same run.
  let probe1PositiveConfirmed = false;
  const suC1 = await suPool.connect();
  const appC1 = await appPool.connect();
  try {
    // Seed as superuser.
    const seedResult = await suC1.query<{ id: number }>(
      `INSERT INTO incident_slas
         (tenant_id, incident_id, severity, title, status, sla_target_minutes, escalation_level, is_sla_breached, created_at)
       VALUES ($1, $2, 'critical', 'T003 Probe', 'open', 240, 0, false, NOW())
       RETURNING id`,
      [`${PROBE_PREFIX}-A`, `${PROBE_PREFIX}-inc-001`],
    );
    incidentSlaId = seedResult.rows[0].id;

    // Probe 1 — CLASS 1 enforcement: correct GUC → own-tenant row visible.
    await appC1.query("BEGIN");
    await appC1.query(
      "SELECT set_config('app.current_tenant_id', $1, true)",
      [`${PROBE_PREFIX}-A`],
    );
    const r1 = await appC1.query<{ id: number }>(
      "SELECT id FROM incident_slas WHERE id = $1",
      [incidentSlaId],
    );
    await appC1.query("COMMIT");
    if (r1.rows.length === 1) {
      probe1PositiveConfirmed = true;
      pass("Probe 1 — CLASS1 enforcement: own-tenant row visible (GUC matches)");
    } else {
      fail("Probe 1 — CLASS1 enforcement", `Expected 1 row, got ${r1.rows.length}`);
    }

    // Probe 2 — CLASS 1 isolation: wrong GUC → 0 rows.
    await appC1.query("BEGIN");
    await appC1.query(
      "SELECT set_config('app.current_tenant_id', $1, true)",
      [`${PROBE_PREFIX}-B`],
    );
    const r2 = await appC1.query<{ id: number }>(
      "SELECT id FROM incident_slas WHERE id = $1",
      [incidentSlaId],
    );
    await appC1.query("COMMIT");
    if (r2.rows.length === 0) {
      if (!probe1PositiveConfirmed) {
        fail(
          "Probe 2 — CLASS1 isolation",
          "HOLLOW-GREEN: denial passes (0 rows) but Probe 1 positive-enforcement failed — " +
            "deny-all or absent policy produces this result; denial-only is not evidence of " +
            "correct isolation. Rule 18: every denial probe must be interlocked with a positive control.",
        );
      } else {
        pass("Probe 2 — CLASS1 isolation: 0 rows (wrong tenant GUC) [Rule 18: interlocked with Probe 1]");
      }
    } else {
      fail("Probe 2 — CLASS1 isolation", `Expected 0 rows, got ${r2.rows.length}`);
    }

    // Probe 3 — CLASS 3 §13.0 fail-closed (empty-string path): GUC='' → 0 rows.
    // current_tenant_id() = NULLIF(current_setting(..., true), '') → NULL for ''
    // tenant_id = NULL → UNKNOWN → excluded (fail-closed).
    await appC1.query("BEGIN");
    await appC1.query("SELECT set_config('app.current_tenant_id', '', true)");
    const r3 = await appC1.query<{ id: number }>(
      "SELECT id FROM incident_slas WHERE id = $1",
      [incidentSlaId],
    );
    await appC1.query("COMMIT");
    if (r3.rows.length === 0) {
      if (!probe1PositiveConfirmed) {
        fail(
          "Probe 3 — CLASS3 §13.0 fail-closed (empty-string GUC)",
          "HOLLOW-GREEN: denial passes (0 rows) but Probe 1 positive-enforcement failed — " +
            "deny-all or absent policy produces this result; denial-only is not evidence of " +
            "correct isolation. Rule 18: every denial probe must be interlocked with a positive control.",
        );
      } else {
        pass("Probe 3 — CLASS3 §13.0 fail-closed (empty-string GUC → 0 rows) [Rule 18: interlocked with Probe 1]");
      }
    } else {
      fail("Probe 3 — CLASS3 §13.0 fail-closed (empty-string)", `Expected 0 rows, got ${r3.rows.length}`);
    }
  } finally {
    appC1.release();
    suC1.release();
  }

  // Probe 9 — CLASS 3 §13.0 fail-closed (NEVER-SET path):
  //
  // Fresh appPool connection, NO set_config called at all. This is the actual
  // pre-auth / background-worker / raw-db-call state. current_tenant_id() reads
  // current_setting('app.current_tenant_id', true) — the missing-ok flag returns
  // NULL for a GUC that has never been assigned; NULLIF(NULL, '') is still NULL;
  // tenant_id = NULL → UNKNOWN → fail-closed. Proved empirically here rather than
  // accepted by construction (per advisor instruction 2026-06-03 — this session
  // taught us a construction-claim about PostgreSQL can be wrong at runtime).
  //
  // Uses a separate fresh connection to guarantee never-set state.
  const appC9 = await appPool.connect();
  try {
    // Deliberately NO set_config, NO BEGIN (autocommit — GUC is session-level,
    // never written). Query the same probe row from probe 1 (still exists).
    const r9 = await appC9.query<{ id: number }>(
      "SELECT id FROM incident_slas WHERE id = $1",
      [incidentSlaId],
    );
    if (r9.rows.length === 0) {
      if (!probe1PositiveConfirmed) {
        fail(
          "Probe 9 — CLASS3 §13.0 fail-closed (NEVER-SET GUC)",
          "HOLLOW-GREEN: denial passes (0 rows) but Probe 1 positive-enforcement failed — " +
            "deny-all or absent policy produces this result; denial-only is not evidence of " +
            "correct isolation. Rule 18: every denial probe must be interlocked with a positive control.",
        );
      } else {
        pass(
          "Probe 9 — CLASS3 §13.0 fail-closed (NEVER-SET GUC: no set_config called → 0 rows) [Rule 18: interlocked with Probe 1]",
        );
      }
    } else {
      fail(
        "Probe 9 — CLASS3 §13.0 fail-closed (NEVER-SET)",
        `Expected 0 rows, got ${r9.rows.length} — never-set GUC does NOT fail-closed`,
      );
    }
  } finally {
    appC9.release();
  }

  // Cleanup probe 1/2/3/9 seed row.
  if (incidentSlaId !== null) {
    const suClean1 = await suPool.connect();
    try {
      await suClean1.query("DELETE FROM incident_slas WHERE id = $1", [incidentSlaId]);
    } finally {
      suClean1.release();
    }
  }

  // ── CLASS 2 (probe 4): dsar_requests ─────────────────────────────────────────
  //
  // Two rows, two tenants. tenant-A context queries both IDs. Must see only idA.

  const suC2 = await suPool.connect();
  const appC2 = await appPool.connect();
  let dsarIdA: string | null = null;
  let dsarIdB: string | null = null;
  try {
    const deadline = new Date(Date.now() + 30 * 24 * 3600 * 1000).toISOString();
    const r2a = await suC2.query<{ id: string }>(
      `INSERT INTO dsar_requests
         (tenant_id, reference, subject_name, subject_email, request_type,
          request_channel, sla_deadline, status, received_at, created_at, updated_at)
       VALUES ($1, $2, 'Alice Probe', 'alice@probe.t3st', 'access',
               'portal', $3, 'received', NOW(), NOW(), NOW())
       RETURNING id`,
      [`${PROBE_PREFIX}-A`, `${PROBE_PREFIX}-DSAR-A`, deadline],
    );
    const r2b = await suC2.query<{ id: string }>(
      `INSERT INTO dsar_requests
         (tenant_id, reference, subject_name, subject_email, request_type,
          request_channel, sla_deadline, status, received_at, created_at, updated_at)
       VALUES ($1, $2, 'Bob Probe', 'bob@probe.t3st', 'access',
               'portal', $3, 'received', NOW(), NOW(), NOW())
       RETURNING id`,
      [`${PROBE_PREFIX}-B`, `${PROBE_PREFIX}-DSAR-B`, deadline],
    );
    dsarIdA = r2a.rows[0].id;
    dsarIdB = r2b.rows[0].id;

    // Probe 4 — CLASS 2 cross-tenant isolation: tenant-A context, both IDs queried.
    await appC2.query("BEGIN");
    await appC2.query(
      "SELECT set_config('app.current_tenant_id', $1, true)",
      [`${PROBE_PREFIX}-A`],
    );
    const seenByA = await appC2.query<{ id: string }>(
      "SELECT id FROM dsar_requests WHERE id = ANY($1)",
      [[dsarIdA, dsarIdB]],
    );
    await appC2.query("COMMIT");

    if (seenByA.rows.length === 1 && seenByA.rows[0].id === dsarIdA) {
      pass("Probe 4 — CLASS2 cross-tenant isolation: tenant-A sees only idA, not idB");
    } else if (seenByA.rows.length === 2) {
      fail(
        "Probe 4 — CLASS2 cross-tenant isolation",
        `tenant-A context returned BOTH rows — cross-tenant leak`,
      );
    } else if (seenByA.rows.length === 0) {
      fail(
        "Probe 4 — CLASS2 cross-tenant isolation",
        `tenant-A context returned 0 rows — RLS is over-blocking own-tenant rows`,
      );
    } else {
      fail(
        "Probe 4 — CLASS2 cross-tenant isolation",
        `Unexpected: ${seenByA.rows.length} rows, ids: ${seenByA.rows.map((r) => r.id).join(", ")}`,
      );
    }
  } finally {
    appC2.release();
    if (dsarIdA || dsarIdB) {
      await suC2.query(
        "DELETE FROM dsar_requests WHERE id = ANY($1)",
        [[dsarIdA, dsarIdB].filter(Boolean)],
      );
    }
    suC2.release();
  }

  // ── CLASS 3 (probe 5): kyt_results — second table for §13.0 fail-closed ──────
  //
  // kyt_results.id is varchar NOT NULL with no default — explicit probe id required.

  const suC3 = await suPool.connect();
  const appC3 = await appPool.connect();
  const kytProbeId = `${PROBE_PREFIX}-kyt`;
  try {
    await suC3.query(
      `INSERT INTO kyt_results
         (id, tenant_id, user_id, amount, currency, recipient_type, channel,
          risk_level, risk_score, risk_factors, recommendation, required_verification, scored_at)
       VALUES ($1, $2, 'probe-user', 1000, 'UGX', 'individual', 'mobile',
               'critical', 0.95, '[]', 'block', '[]', NOW())`,
      [kytProbeId, `${PROBE_PREFIX}-FC`],
    );

    // Probe 5 — CLASS 3 §13.0 fail-closed (kyt_results) — Rule 18 interlocked.
    //
    // Step A (positive): own-tenant GUC → row must be visible. This is the paired
    // positive control for the denial check below. Without it, a deny-all or absent
    // policy would produce 0 rows for the denial check too — a hollow green pass.
    // The positive check distinguishes predicate-based isolation from absent isolation.
    await appC3.query("BEGIN");
    await appC3.query(
      "SELECT set_config('app.current_tenant_id', $1, true)",
      [`${PROBE_PREFIX}-FC`],
    );
    const r5pos = await appC3.query<{ id: string }>(
      "SELECT id FROM kyt_results WHERE id = $1",
      [kytProbeId],
    );
    await appC3.query("COMMIT");
    const kytPositiveConfirmed = r5pos.rows.length === 1;

    // Step B (denial): empty-string GUC → row must NOT be visible (§13.0 fail-closed).
    await appC3.query("BEGIN");
    await appC3.query("SELECT set_config('app.current_tenant_id', '', true)");
    const r5 = await appC3.query<{ id: string }>(
      "SELECT id FROM kyt_results WHERE id = $1",
      [kytProbeId],
    );
    await appC3.query("COMMIT");

    if (kytPositiveConfirmed && r5.rows.length === 0) {
      pass(
        "Probe 5 — CLASS3 §13.0 fail-closed (kyt_results, positive=1 denial=0) [Rule 18: interlocked]",
      );
    } else if (!kytPositiveConfirmed && r5.rows.length === 0) {
      fail(
        "Probe 5 — CLASS3 §13.0 fail-closed (kyt_results)",
        `HOLLOW-GREEN: denial passes (0 rows) but positive enforcement failed ` +
          `(got ${r5pos.rows.length} rows for own-tenant GUC, want 1) — ` +
          `deny-all or absent policy produces this result. Rule 18.`,
      );
    } else {
      fail(
        "Probe 5 — CLASS3 §13.0 fail-closed (kyt_results)",
        `positive=${r5pos.rows.length} (want 1), denial=${r5.rows.length} (want 0)`,
      );
    }
  } finally {
    appC3.release();
    await suC3.query("DELETE FROM kyt_results WHERE id = $1", [kytProbeId]);
    suC3.release();
  }

  // ── CLASS 4 (probes 6–8): audit_chain_entries DML denial — Option B ───────────
  //
  // Option B structural enforcement: aegis_app has SELECT on audit_chain_entries
  // but INSERT/UPDATE/DELETE are REVOKEd in initRlsPolicies() Step 2.
  // audit_chain_entries has NO RLS policy (system-level cross-tenant table);
  // enforcement is entirely via REVOKE, not via policy.

  const suC4 = await suPool.connect();
  const appC4 = await appPool.connect();
  let auditChainSeedId: number | null = null;
  try {
    // Probe 6 — CLASS 4: aegis_app INSERT denied (42501).
    let insertDenied = false;
    try {
      await appC4.query(
        `INSERT INTO audit_chain_entries
           (hash, previous_hash, action, user_id, resource, details, created_at, source_audit_id, entry_timestamp)
         VALUES ('probe-h', 'probe-ph', 'T003_PROBE', 'probe-u', 'probe-r', '{}', NOW(), 999, NOW())`,
      );
    } catch (e: any) {
      if (e?.code === "42501") {
        insertDenied = true;
      } else {
        throw e;
      }
    }
    if (insertDenied) {
      pass("Probe 6 — CLASS4 audit-chain INSERT denied (42501 insufficient_privilege)");
    } else {
      fail(
        "Probe 6 — CLASS4 audit-chain INSERT",
        "INSERT SUCCEEDED — Option B REVOKE not in force; aegis_app can write audit chain",
      );
    }

    // Seed a target row as superuser so probe 7 (DELETE) and probe 8 (SELECT)
    // have a real row to operate on.
    const seedAudit = await suC4.query<{ id: number }>(
      `INSERT INTO audit_chain_entries
         (hash, previous_hash, action, user_id, resource, details, created_at, source_audit_id, entry_timestamp)
       VALUES ('probe-del-h', 'probe-del-ph', 'T003_PROBE_DEL', 'probe-u', 'probe-r', '{}', NOW(), 999, NOW())
       RETURNING id`,
    );
    auditChainSeedId = seedAudit.rows[0].id;

    // Probe 7 — CLASS 4: aegis_app DELETE denied (42501).
    let deleteDenied = false;
    try {
      await appC4.query(
        "DELETE FROM audit_chain_entries WHERE id = $1",
        [auditChainSeedId],
      );
    } catch (e: any) {
      if (e?.code === "42501") {
        deleteDenied = true;
      } else {
        throw e;
      }
    }
    if (deleteDenied) {
      pass("Probe 7 — CLASS4 audit-chain DELETE denied (42501 insufficient_privilege)");
    } else {
      fail(
        "Probe 7 — CLASS4 audit-chain DELETE",
        "DELETE SUCCEEDED — Option B REVOKE not in force; aegis_app can delete audit chain rows",
      );
    }

    // Probe 8 — CLASS 4: aegis_app SELECT on audit_chain_entries ALLOWED.
    // Option B = SELECT-only carve-out, not total denial. Read path must remain intact.
    const r8 = await appC4.query<{ id: number }>(
      "SELECT id FROM audit_chain_entries WHERE id = $1",
      [auditChainSeedId],
    );
    if (r8.rows.length === 1) {
      pass("Probe 8 — CLASS4 audit-chain SELECT allowed (Option B read path intact)");
    } else {
      fail(
        "Probe 8 — CLASS4 audit-chain SELECT",
        `Expected 1 row, got ${r8.rows.length} — SELECT is unexpectedly blocked`,
      );
    }
  } finally {
    appC4.release();
    if (auditChainSeedId !== null) {
      await suC4.query("DELETE FROM audit_chain_entries WHERE id = $1", [auditChainSeedId]);
    }
    suC4.release();
  }

  // ── CLASS 5 (probes 10–13): background-worker bypass scope ───────────────────
  //
  // Validates the four new bypass/tenant-context sites authorised in the
  // DR-T003 bypass register (T003 background-worker remediation arc):
  //   BACKUP-SYS   — backup_records INSERT+SELECT without request GUC
  //   SBOM-SYS     — sbom_snapshots INSERT without request GUC
  //   PILOT-COUNT  — cross-tenant aggregate count (bare=0; bypass=actual)
  //   DSAR-TENANT  — dsar_requests cross-tenant isolation via withTenantRls
  //                  (tested in probe 11 alongside CLASS 2)

  // Probe 10 — CLASS 5: backup_records INSERT via bypass (BACKUP-SYS).
  // withBypassRls uses SET LOCAL ROLE aegis_rls_bypass — mirrored inline here.
  {
    const appN = await appPool.connect();
    const suN = await suPool.connect();
    let probeBackupId: string | null = null;
    try {
      await appN.query("BEGIN");
      await appN.query("SET LOCAL ROLE aegis_rls_bypass");
      const rN = await appN.query<{ id: string }>(
        `INSERT INTO backup_records (backup_type, status, retention_days, created_at)
         VALUES ('full', 'in_progress', 90, NOW())
         RETURNING id`,
      );
      await appN.query("COMMIT");
      if (rN.rows.length === 1) {
        probeBackupId = rN.rows[0].id;
        pass("Probe 10 — CLASS5 backup_records INSERT via bypass succeeded (BACKUP-SYS)");
      } else {
        fail("Probe 10 — CLASS5 backup_records bypass INSERT", "Expected 1 row returned, got 0");
      }
    } catch (e: any) {
      await appN.query("ROLLBACK").catch(() => {});
      fail("Probe 10 — CLASS5 backup_records bypass INSERT", `Threw: ${e?.message ?? e}`);
    } finally {
      appN.release();
      if (probeBackupId !== null) {
        await suN.query("DELETE FROM backup_records WHERE id = $1", [probeBackupId]);
      }
      suN.release();
    }
  }

  // Probe 11 — CLASS 2: DSAR cross-tenant isolation via withTenantRls (Shape 2a).
  // PDPO-critical: a worker claiming dsar_id from tenant-A must not see tenant-B's
  // DSAR row even when querying by id. withTenantRls sets the GUC to tenant-A;
  // RLS policy must then block the tenant-B row (0 rows returned).
  {
    const suN = await suPool.connect();
    const appNa = await appPool.connect();
    const appNb = await appPool.connect();
    let probeIdA: string | null = null;
    let probeIdB: string | null = null;
    try {
      // try body — errors caught locally to avoid top-level short-circuit.
      const seedA = await suN.query<{ id: string }>(
        `INSERT INTO dsar_requests
           (reference, tenant_id, subject_name, subject_email, request_type,
            request_channel, received_at, sla_deadline, status, created_at, updated_at)
         VALUES ($1, $2, 'Probe A', 'probe-a@test.invalid', 'erasure',
                 'portal', NOW(), NOW() + INTERVAL '30 days', 'received', NOW(), NOW())
         RETURNING id`,
        [`${PROBE_PREFIX}-dsar-a`, `${PROBE_PREFIX}-dsar-tenant-a`],
      );
      probeIdA = seedA.rows[0].id;

      const seedB = await suN.query<{ id: string }>(
        `INSERT INTO dsar_requests
           (reference, tenant_id, subject_name, subject_email, request_type,
            request_channel, received_at, sla_deadline, status, created_at, updated_at)
         VALUES ($1, $2, 'Probe B', 'probe-b@test.invalid', 'erasure',
                 'portal', NOW(), NOW() + INTERVAL '30 days', 'received', NOW(), NOW())
         RETURNING id`,
        [`${PROBE_PREFIX}-dsar-b`, `${PROBE_PREFIX}-dsar-tenant-b`],
      );
      probeIdB = seedB.rows[0].id;

      // 11a: tenant-A context → own row visible (1 row expected).
      await appNa.query("BEGIN");
      await appNa.query("SELECT set_config('app.current_tenant_id', $1, true)", [`${PROBE_PREFIX}-dsar-tenant-a`]);
      const r11a = await appNa.query<{ id: string }>("SELECT id FROM dsar_requests WHERE id = $1", [probeIdA]);
      await appNa.query("COMMIT");

      // 11b: tenant-A context → cross-tenant row invisible (0 rows expected).
      await appNb.query("BEGIN");
      await appNb.query("SELECT set_config('app.current_tenant_id', $1, true)", [`${PROBE_PREFIX}-dsar-tenant-a`]);
      const r11b = await appNb.query<{ id: string }>("SELECT id FROM dsar_requests WHERE id = $1", [probeIdB]);
      await appNb.query("COMMIT");

      if (r11a.rows.length === 1 && r11b.rows.length === 0) {
        pass("Probe 11 — CLASS2 DSAR cross-tenant isolation (withTenantRls own=1 cross=0, PDPO-critical)");
      } else {
        fail(
          "Probe 11 — CLASS2 DSAR cross-tenant isolation",
          `own-tenant rows=${r11a.rows.length} (want 1), cross-tenant rows=${r11b.rows.length} (want 0)`,
        );
      }
    } catch (e: any) {
      fail("Probe 11 — CLASS2 DSAR cross-tenant isolation", `Threw: ${e?.message ?? e}`);
    } finally {
      appNa.release();
      appNb.release();
      await suN.query("DELETE FROM dsar_requests WHERE id = ANY($1::uuid[])", [[probeIdA, probeIdB].filter(Boolean)]).catch(() => {});
      suN.release();
    }
  }

  // Probe 12 — CLASS 5: sbom_snapshots INSERT via bypass (SBOM-SYS).
  // generateSbom is a system-level operation with no per-tenant owner.
  {
    const appN = await appPool.connect();
    const suN = await suPool.connect();
    let probeSbomId: string | null = null;
    try {
      await appN.query("BEGIN");
      await appN.query("SET LOCAL ROLE aegis_rls_bypass");
      const rN = await appN.query<{ id: string }>(
        `INSERT INTO sbom_snapshots
           (snapshot_name, component_count, critical_vulns, high_vulns,
            outdated_components, license_issues, trigger, components_json, snapshot_at)
         VALUES ($1, 0, 0, 0, 0, 0, 'probe', '[]', NOW())
         RETURNING id`,
        [`${PROBE_PREFIX}-sbom`],
      );
      await appN.query("COMMIT");
      if (rN.rows.length === 1) {
        probeSbomId = rN.rows[0].id;
        pass("Probe 12 — CLASS5 sbom_snapshots INSERT via bypass succeeded (SBOM-SYS)");
      } else {
        fail("Probe 12 — CLASS5 sbom_snapshots bypass INSERT", "Expected 1 row returned, got 0");
      }
    } catch (e: any) {
      await appN.query("ROLLBACK").catch(() => {});
      fail("Probe 12 — CLASS5 sbom_snapshots bypass INSERT", `Threw: ${e?.message ?? e}`);
    } finally {
      appN.release();
      if (probeSbomId !== null) {
        await suN.query("DELETE FROM sbom_snapshots WHERE id = $1", [probeSbomId]);
      }
      suN.release();
    }
  }

  // Probe 13 — CLASS 5: PILOT-COUNT bypass isolation.
  // Bare aegis_app (no GUC) cannot count tenant-scoped rows (RLS → 0).
  // Bypass (aegis_rls_bypass) sees the actual row count (≥1 after seeding).
  // This validates that the pilot-pack snapshot worker's bypass is necessary
  // and that the bypass correctly lifts the RLS barrier for cross-tenant counts.
  {
    const suN = await suPool.connect();
    const appNBare = await appPool.connect();
    const appNBypass = await appPool.connect();
    let probePilotId: string | null = null;
    try {
      // Seed a sbom_snapshots row via superuser with a unique probe tenant_id.
      const seedR = await suN.query<{ id: string }>(
        `INSERT INTO sbom_snapshots
           (snapshot_name, component_count, critical_vulns, high_vulns,
            outdated_components, license_issues, trigger, components_json, tenant_id, snapshot_at)
         VALUES ($1, 0, 0, 0, 0, 0, 'probe', '[]', $2, NOW())
         RETURNING id`,
        [`${PROBE_PREFIX}-pilot-count`, `${PROBE_PREFIX}-pilot-tenant`],
      );
      probePilotId = seedR.rows[0].id;

      // 13a: bare aegis_app — no GUC set — RLS must return 0 rows.
      const r13bare = await appNBare.query<{ c: string }>(
        "SELECT count(*)::int AS c FROM sbom_snapshots WHERE tenant_id = $1",
        [`${PROBE_PREFIX}-pilot-tenant`],
      );
      const bareCount = Number(r13bare.rows[0]?.c ?? 0);

      // 13b: bypass role — must see the seeded row (≥1).
      await appNBypass.query("BEGIN");
      await appNBypass.query("SET LOCAL ROLE aegis_rls_bypass");
      const r13bypass = await appNBypass.query<{ c: string }>(
        "SELECT count(*)::int AS c FROM sbom_snapshots WHERE tenant_id = $1",
        [`${PROBE_PREFIX}-pilot-tenant`],
      );
      await appNBypass.query("COMMIT");
      const bypassCount = Number(r13bypass.rows[0]?.c ?? 0);

      if (bareCount === 0 && bypassCount >= 1) {
        pass("Probe 13 — CLASS5 PILOT-COUNT bypass isolation (bare=0 bypass≥1, cross-tenant aggregate works)");
      } else {
        fail(
          "Probe 13 — CLASS5 PILOT-COUNT bypass isolation",
          `bare=${bareCount} (want 0), bypass=${bypassCount} (want ≥1)`,
        );
      }
    } catch (e: any) {
      await appNBypass.query("ROLLBACK").catch(() => {});
      fail("Probe 13 — CLASS5 PILOT-COUNT bypass isolation", `Threw: ${e?.message ?? e}`);
    } finally {
      appNBare.release();
      appNBypass.release();
      if (probePilotId !== null) {
        await suN.query("DELETE FROM sbom_snapshots WHERE id = $1", [probePilotId]).catch(() => {});
      }
      suN.release();
    }
  }

  // ── Probe 14 — CLASS4 Option Y: aegis_rls_bypass INSERT on audit_chain_entries denied ──

  {
    const appN = await appPool.connect();
    try {
      await appN.query("BEGIN");
      await appN.query("SET LOCAL ROLE aegis_rls_bypass");
      let caught42501 = false;
      try {
        await appN.query(
          `INSERT INTO audit_chain_entries
             (hash, previous_hash, action, user_id, resource, details, created_at, source_audit_id, entry_timestamp)
           VALUES ('probe14h', 'probe14ph', 'T003_PROBE14_BYPASS', 'probe-u', 'probe-r', '{}', NOW(), 999, NOW())`,
        );
      } catch (e: any) {
        if (e?.code === "42501") {
          caught42501 = true;
        } else {
          throw e;
        }
      }
      await appN.query("ROLLBACK");

      if (caught42501) {
        pass(
          "Probe 14 — CLASS4 Option Y aegis_rls_bypass INSERT on audit_chain_entries denied (42501)",
        );
      } else {
        fail(
          "Probe 14 — CLASS4 Option Y aegis_rls_bypass INSERT on audit_chain_entries denied",
          "INSERT succeeded — Option Y REVOKE did NOT take effect; bypass role can write the chain",
        );
      }
    } catch (e: any) {
      await appN.query("ROLLBACK").catch(() => {});
      fail(
        "Probe 14 — CLASS4 Option Y aegis_rls_bypass INSERT on audit_chain_entries denied",
        `Threw: ${e?.message ?? e}`,
      );
    } finally {
      appN.release();
    }
  }

  // ── Probe 15 — CLASS 6: Login-path UTR lookup (LOGIN-UTR bypass correctness) ──
  //
  // Directly validates the LOGIN-UTR bypass fix that restored prod authentication
  // after T003 activated RLS on user_tenant_roles (regression discovered 2026-06-03).
  //
  // Root cause of the T003 LOGIN-UTR regression:
  //   The login route used bare db (appPool, aegis_app, RLS-enforced) to look up
  //   user_tenant_roles at login time. At login time GUC = "default" (pre-auth,
  //   no session). current_tenant_id() returns "default". All UTR rows have
  //   tenant_id = 'aegis-sovereign'. The lookup matched 0 rows →
  //   primaryTenant.length === 0 → LOGIN_REJECTED_NO_TENANT (403).
  //
  // The fix (architect Rule 9 PASS 2026-06-03): wrap the UTR lookup in
  //   withBypassRls (aegis_rls_bypass, BYPASSRLS) so the pre-session lookup
  //   reads through RLS. Bypass register entry: LOGIN-UTR (db.ts).
  //
  // Probe 15a — bare aegis_app, NO set_config (pre-auth GUC state) → must
  //   return 0 rows. Proves the regression path exists (without the fix, login
  //   would fail here).
  //
  // Probe 15b — aegis_rls_bypass role → must return 1 UTR row. Proves the
  //   bypass path correctly reads the user's primary tenant assignment.
  //
  // Reads existing data (admin user's UTR row). No probe row seeding required.
  // MANDATORY REGRESSION PROBE: must re-run if login UTR lookup or LOGIN-UTR
  // bypass register entry is ever modified.
  {
    const suN = await suPool.connect();
    const appNBare = await appPool.connect();
    const appNBypass = await appPool.connect();
    try {
      // Get the admin user's ID via superuser (stable across environments).
      const adminRow = await suN.query<{ id: string }>(
        "SELECT id FROM users WHERE username = 'admin' LIMIT 1",
      );
      if (adminRow.rows.length === 0) {
        fail(
          "Probe 15 — CLASS6 LOGIN-UTR bypass correctness",
          "Admin user not found — cannot run login-path probe",
        );
      } else {
        const adminId = adminRow.rows[0].id;

        // 15a: bare aegis_app, NO set_config — the pre-auth state.
        // Mirrors the BROKEN prod login state before the fix.
        // appNBare is a fresh connection from appPool; no GUC has been set.
        const r15a = await appNBare.query<{ tenant_id: string }>(
          `SELECT tenant_id FROM user_tenant_roles
           WHERE user_id = $1 AND is_primary = true AND is_active = true
           LIMIT 1`,
          [adminId],
        );

        // 15b: bypass role (aegis_rls_bypass, SET LOCAL within transaction).
        // Mirrors the FIXED login route's withBypassRls call site (LOGIN-UTR).
        await appNBypass.query("BEGIN");
        await appNBypass.query("SET LOCAL ROLE aegis_rls_bypass");
        const r15b = await appNBypass.query<{ tenant_id: string }>(
          `SELECT tenant_id FROM user_tenant_roles
           WHERE user_id = $1 AND is_primary = true AND is_active = true
           LIMIT 1`,
          [adminId],
        );
        await appNBypass.query("COMMIT");

        if (r15a.rows.length === 0 && r15b.rows.length === 1) {
          pass(
            `Probe 15 — CLASS6 LOGIN-UTR bypass correctness: bare=0 (pre-auth GUC → RLS blocks) bypass=1 tenant=${r15b.rows[0].tenant_id}`,
          );
        } else if (r15a.rows.length > 0) {
          fail(
            "Probe 15 — CLASS6 LOGIN-UTR bypass correctness",
            `bare-path returned ${r15a.rows.length} rows — RLS not enforcing on user_tenant_roles under pre-auth GUC; login-path probe is moot`,
          );
        } else if (r15b.rows.length === 0) {
          fail(
            "Probe 15 — CLASS6 LOGIN-UTR bypass correctness",
            "bypass-path returned 0 rows — admin has no primary active UTR; login would fail even with fix applied",
          );
        } else {
          fail(
            "Probe 15 — CLASS6 LOGIN-UTR bypass correctness",
            `bare=${r15a.rows.length}, bypass=${r15b.rows.length} — unexpected state`,
          );
        }
      }
    } catch (e: any) {
      await appNBypass.query("ROLLBACK").catch(() => {});
      fail(
        "Probe 15 — CLASS6 LOGIN-UTR bypass correctness",
        `Threw: ${e?.message ?? e}`,
      );
    } finally {
      appNBare.release();
      appNBypass.release();
      suN.release();
    }
  }

  // ── Probe 16 — CLASS 7: VARCHAR predicate enforcement (billing_invoices) ──────
  //
  // Functionally proves the ((tenant_id)::text = current_tenant_id()) predicate
  // form (produced by PostgreSQL for varchar("tenant_id") columns) enforces
  // tenant isolation correctly — it does NOT just exist in pg_policy.
  //
  // WHY THIS PROBE EXISTS (added 2026-06-04, same-session discovery):
  //   Six tables use varchar("tenant_id") in their Drizzle schema. PostgreSQL
  //   normalizes their RLS policy USING expression to ((tenant_id)::text =
  //   current_tenant_id()) instead of the bare (tenant_id = current_tenant_id())
  //   form used by TEXT-column tables. The Step 3a boot-time assertion ACCEPTED
  //   both forms as semantically equivalent, widening from a single EXPECTED_PREDICATE.
  //
  //   Probes 1, 4, 11 (the enforcement/isolation probes) all target TEXT-column
  //   tables (incident_slas and dsar_requests). They functionally prove the TEXT
  //   form isolates, but leave the VARCHAR form verified only by reasoning.
  //
  //   This probe closes that gap: billing_invoices has varchar("tenant_id")
  //   (confirmed in shared/schema-wave5.ts). Seeding two rows under different
  //   tenant IDs and querying each from within the other's GUC context proves
  //   empirically that the ::text cast form enforces isolation, not just that
  //   PostgreSQL stored the predicate with a cast.
  //
  // Pattern: seed row-A under tenant-VAR-A + row-B under tenant-VAR-B; with
  //   GUC=VAR-A → must see row-A (1 row); GUC=VAR-B → must NOT see row-A (0 rows).
  //   Both halves must pass for the probe to pass (Rule 18 paired positive+denial).
  {
    const suN = await suPool.connect();
    const appNa = await appPool.connect();
    const appNb = await appPool.connect();
    const invProbeId = `${PROBE_PREFIX}-INV16`;
    const invTenantA = `${PROBE_PREFIX}-VAR-A`;
    const invTenantB = `${PROBE_PREFIX}-VAR-B`;
    try {
      // Seed one billing_invoices row under tenant-VAR-A.
      await suN.query(
        `INSERT INTO billing_invoices
           (id, tenant_id, period, status, line_items_json,
            subtotal_usd, tax_usd, total_usd)
         VALUES ($1, $2, '2026-probe', 'DRAFT', '[]', 0, 0, 0)`,
        [invProbeId, invTenantA],
      );

      // 16a — POSITIVE: GUC=VAR-A → own-tenant row must be visible (1 row).
      // This half proves the ::text cast form allows reads when the GUC matches.
      await appNa.query("BEGIN");
      await appNa.query(
        "SELECT set_config('app.current_tenant_id', $1, true)",
        [invTenantA],
      );
      const r16a = await appNa.query<{ id: string }>(
        "SELECT id FROM billing_invoices WHERE id = $1",
        [invProbeId],
      );
      await appNa.query("COMMIT");

      // 16b — DENIAL: GUC=VAR-B → cross-tenant row must be invisible (0 rows).
      // This half proves the ::text cast form blocks reads when the GUC doesn't match.
      await appNb.query("BEGIN");
      await appNb.query(
        "SELECT set_config('app.current_tenant_id', $1, true)",
        [invTenantB],
      );
      const r16b = await appNb.query<{ id: string }>(
        "SELECT id FROM billing_invoices WHERE id = $1",
        [invProbeId],
      );
      await appNb.query("COMMIT");

      if (r16a.rows.length === 1 && r16b.rows.length === 0) {
        pass(
          "Probe 16 — CLASS7 VARCHAR predicate enforcement: billing_invoices own=1 cross=0 " +
          "(::text cast form functionally enforces isolation, not just present)",
        );
      } else if (r16a.rows.length === 0) {
        fail(
          "Probe 16 — CLASS7 VARCHAR predicate enforcement",
          `own-tenant returned 0 rows — ::text cast form is BLOCKING own-tenant reads ` +
          `(broken isolation); GUC=${invTenantA} should see id=${invProbeId}`,
        );
      } else if (r16b.rows.length > 0) {
        fail(
          "Probe 16 — CLASS7 VARCHAR predicate enforcement",
          `cross-tenant returned ${r16b.rows.length} rows — ::text cast form NOT isolating ` +
          `(cross-tenant leak); GUC=${invTenantB} should NOT see id=${invProbeId}`,
        );
      } else {
        fail(
          "Probe 16 — CLASS7 VARCHAR predicate enforcement",
          `Unexpected: own=${r16a.rows.length} cross=${r16b.rows.length}`,
        );
      }
    } catch (e: any) {
      await appNa.query("ROLLBACK").catch(() => {});
      await appNb.query("ROLLBACK").catch(() => {});
      fail(
        "Probe 16 — CLASS7 VARCHAR predicate enforcement",
        `Threw: ${e?.message ?? e}`,
      );
    } finally {
      appNa.release();
      appNb.release();
      await suN.query(
        "DELETE FROM billing_invoices WHERE id = $1",
        [invProbeId],
      ).catch(() => {});
      suN.release();
    }
  }

  // ── Probe 17 — CLASS 1+2: decision_scoring_outputs (#70) isolation ──────────
  //
  // WHY THIS PROBE EXISTS (added 2026-06-08, post-count-sweep):
  // decision_scoring_outputs is TENANT_TABLES[#70] — added for P5 Item 2 on
  // 2026-06-07, AFTER the T003 diagnostic last ran at 15/15. This is the FIRST
  // functional diagnostic confirmation that #70's tenant-isolation holds.
  // Step 3a boot assertion verified the USING+WITH CHECK predicate structurally;
  // this probe verifies it functionally.
  // Rule 18: both halves required (positive enforcement + cross-tenant denial).
  {
    const su17 = await suPool.connect();
    const app17a = await appPool.connect();
    const app17b = await appPool.connect();
    const dso17TenantA = `${PROBE_PREFIX}-DSO-A`;
    const dso17TenantB = `${PROBE_PREFIX}-DSO-B`;
    const dso17DecisionId = `${PROBE_PREFIX}-DECISION-17`;
    let dso17Id: number | null = null;
    try {
      // Seed one decision_scoring_outputs row under tenant-DSO-A.
      // id is SERIAL — capture via RETURNING id.
      const ins17 = await su17.query<{ id: number }>(
        `INSERT INTO decision_scoring_outputs
           (tenant_id, decision_id, scoring_path, model_identifier,
            risk_score, reasoning_text, factors_json)
         VALUES ($1, $2, 'PROBE', 'probe-model', 0, 'probe', '{}')
         RETURNING id`,
        [dso17TenantA, dso17DecisionId],
      );
      dso17Id = ins17.rows[0]?.id ?? null;
      if (dso17Id === null) throw new Error("INSERT RETURNING id returned no row");

      // 17a — POSITIVE: GUC=DSO-A → own-tenant row must be visible (1 row).
      await app17a.query("BEGIN");
      await app17a.query(
        "SELECT set_config('app.current_tenant_id', $1, true)",
        [dso17TenantA],
      );
      const r17a = await app17a.query<{ id: number }>(
        "SELECT id FROM decision_scoring_outputs WHERE id = $1",
        [dso17Id],
      );
      await app17a.query("COMMIT");

      // 17b — DENIAL: GUC=DSO-B → cross-tenant row must be invisible (0 rows).
      await app17b.query("BEGIN");
      await app17b.query(
        "SELECT set_config('app.current_tenant_id', $1, true)",
        [dso17TenantB],
      );
      const r17b = await app17b.query<{ id: number }>(
        "SELECT id FROM decision_scoring_outputs WHERE id = $1",
        [dso17Id],
      );
      await app17b.query("COMMIT");

      if (r17a.rows.length === 1 && r17b.rows.length === 0) {
        pass(
          "Probe 17 — CLASS1+2 decision_scoring_outputs (#70) isolation: own=1 cross=0 " +
          "(first functional diagnostic confirmation of table #70 tenant-isolation)",
        );
      } else if (r17a.rows.length === 0) {
        fail(
          "Probe 17 — CLASS1+2 decision_scoring_outputs (#70) isolation",
          `own-tenant returned 0 rows — RLS blocking own-tenant reads; ` +
          `GUC=${dso17TenantA} should see id=${dso17Id}`,
        );
      } else if (r17b.rows.length > 0) {
        fail(
          "Probe 17 — CLASS1+2 decision_scoring_outputs (#70) isolation",
          `cross-tenant returned ${r17b.rows.length} rows — tenant-isolation NOT enforced on #70; ` +
          `GUC=${dso17TenantB} should NOT see id=${dso17Id}`,
        );
      } else {
        fail(
          "Probe 17 — CLASS1+2 decision_scoring_outputs (#70) isolation",
          `Unexpected: own=${r17a.rows.length} cross=${r17b.rows.length}`,
        );
      }
    } catch (e: any) {
      await app17a.query("ROLLBACK").catch(() => {});
      await app17b.query("ROLLBACK").catch(() => {});
      fail(
        "Probe 17 — CLASS1+2 decision_scoring_outputs (#70) isolation",
        `Threw: ${e?.message ?? e}`,
      );
    } finally {
      app17a.release();
      app17b.release();
      if (dso17Id !== null) {
        await su17.query(
          "DELETE FROM decision_scoring_outputs WHERE id = $1",
          [dso17Id],
        ).catch(() => {});
      }
      su17.release();
    }
  }

  // ── Summary ───────────────────────────────────────────────────────────────────

  console.log("\n=== T003 RLS PROBE SUITE RESULTS ===\n");
  for (const r of results) {
    const marker = r.result === "PASS" ? "PASS" : "FAIL";
    console.log(`  [${marker}] ${r.probe}${r.detail ? `\n         → ${r.detail}` : ""}`);
  }
  console.log(`\nTotal: ${passed} PASS / ${failed} FAIL`);

  if (failed > 0) {
    console.log(
      "\nFAILURE: One or more RLS enforcement probes failed. Do NOT proceed to prod.",
    );
    process.exit(1);
  } else {
    console.log(
      "\nAll probes PASS. Dev RLS enforcement verified across all 7 architect-required classes.",
    );
    console.log(
      "To re-run against prod after publish, set DATABASE_URL + AEGIS_APP_DB_PASSWORD to prod values.",
    );
  }
}

run()
  .catch((e: any) => {
    console.error("\nPROBE SUITE ERROR:", e?.message ?? e);
    if (e?.code) console.error("SQLSTATE:", e.code);
    process.exit(1);
  })
  .finally(async () => {
    await Promise.all([suPool.end(), appPool.end()]);
  });