// Isolation-gap closure (2026-06-24) — RLS re-probe for the three enrolled child tables.
//
// retention_executions, pen_test_findings, sbom_diffs shipped without tenant_id (no RLS backstop).
// They are now enrolled (tenant_id + TENANT_TABLES + boot-DDL M53). This proves the isolation the
// enrollment is supposed to deliver, the same way the other isolation work was proven:
//   1. Read isolation — tenant A's rows are INVISIBLE to tenant B (B reads zero).
//   2. Cross-tenant write rejection — tenant B cannot INSERT a row tagged for tenant A (WITH CHECK).
// Both run through withTenantRls (the aegis_app NOSUPERUSER NOBYPASSRLS runner with the GUC set) —
// so this exercises the live RLS policy, not application-level filtering.
//
// Requires DATABASE_URL + boot secrets (the RLS policies must be initialised on the target DB).

import { test, describe, before, after } from "node:test";
import assert from "node:assert/strict";
import { sql } from "drizzle-orm";
import { withTenantRls, auditDb } from "../server/db.js";

const RUN = `child-iso-${Date.now()}`;
const A = `${RUN}-A`;
const B = `${RUN}-B`;

// Each table + a builder that inserts a child row with an explicit tenant_id (no FK on these cols).
const TABLES: ReadonlyArray<{ name: string; insert: (id: string, tenantId: string) => ReturnType<typeof sql> }> = [
  { name: "retention_executions", insert: (id, t) => sql`INSERT INTO retention_executions (id, policy_id, status, tenant_id) VALUES (${id}, ${"pol-" + id}, 'completed', ${t})` },
  { name: "pen_test_findings",   insert: (id, t) => sql`INSERT INTO pen_test_findings (id, pen_test_id, title, severity, description, tenant_id) VALUES (${id}, ${"pt-" + id}, 'finding', 'high', 'desc', ${t})` },
  { name: "sbom_diffs",          insert: (id, t) => sql`INSERT INTO sbom_diffs (id, from_snapshot_id, to_snapshot_id, tenant_id) VALUES (${id}, 'from', 'to', ${t})` },
  { name: "servicenow_sync_log", insert: (id, t) => sql`INSERT INTO servicenow_sync_log (id, config_id, direction, record_type, aegis_record_id, status, tenant_id) VALUES (${id}, 'cfg', 'outbound', 'incident', 'rec', 'success', ${t})` },
  { name: "pagerduty_incidents", insert: (id, t) => sql`INSERT INTO pagerduty_incidents (id, config_id, dedupe_key, title, severity, status, tenant_id) VALUES (${id}, 'cfg', ${"dk-" + id}, 'title', 'P1', 'triggered', ${t})` },
];

const countMine = (tenantDb: any, table: string) =>
  tenantDb.execute(sql`SELECT count(*)::int AS c FROM ${sql.raw(table)} WHERE id LIKE ${RUN + "%"}`)
    .then((r: any) => Number((r.rows ?? r)[0].c));

describe("Child-table isolation re-probe (retention_executions / pen_test_findings / sbom_diffs)", () => {
  // The throwaway-DB recipe runs drizzle push + GRANT but not initRlsPolicies, so the RLS policies
  // are not on the test DB. Boot-test already proved ENROLLMENT (initRlsPolicies -> 110/110 includes
  // these three). Here we apply the SAME policy predicate to exactly these three tables and prove the
  // SEMANTICS isolate — scoped to our tables so the rest of the suite is untouched. (auditDb = the
  // superuser owner; it provisions the function + policies and bypasses RLS for cleanup.)
  before(async () => {
    await auditDb.execute(sql`CREATE OR REPLACE FUNCTION public.current_tenant_id() RETURNS text LANGUAGE sql STABLE AS $func$ SELECT NULLIF(current_setting('app.current_tenant_id', true), '') $func$`);
    for (const t of TABLES) {
      await auditDb.execute(sql`ALTER TABLE ${sql.raw(t.name)} ENABLE ROW LEVEL SECURITY`);
      await auditDb.execute(sql`DROP POLICY IF EXISTS tenant_isolation ON ${sql.raw(t.name)}`);
      await auditDb.execute(sql`CREATE POLICY tenant_isolation ON ${sql.raw(t.name)} USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id())`);
    }
  });
  after(async () => {
    for (const t of TABLES) {
      try { await auditDb.execute(sql`DELETE FROM ${sql.raw(t.name)} WHERE id LIKE ${RUN + "%"}`); } catch { /* throwaway DB */ }
      try { await auditDb.execute(sql`DROP POLICY IF EXISTS tenant_isolation ON ${sql.raw(t.name)}`); } catch { /* noop */ }
      try { await auditDb.execute(sql`ALTER TABLE ${sql.raw(t.name)} DISABLE ROW LEVEL SECURITY`); } catch { /* noop */ }
    }
  });

  for (const t of TABLES) {
    test(`${t.name}: tenant A's row is invisible to tenant B (read isolation)`, async () => {
      const id = `${A}-${t.name}-row`;
      await withTenantRls(A, async (dbA) => { await dbA.execute(t.insert(id, A)); });
      const aSees = await withTenantRls(A, (dbA) => countMine(dbA, t.name));
      const bSees = await withTenantRls(B, (dbB) => countMine(dbB, t.name));
      assert.ok(aSees >= 1, `tenant A must see its own ${t.name} row`);
      assert.equal(bSees, 0, `tenant B must see ZERO of tenant A's ${t.name} rows (RLS read isolation)`);
    });

    test(`${t.name}: tenant B cannot write a row tagged for tenant A (WITH CHECK)`, async () => {
      await assert.rejects(
        withTenantRls(B, async (dbB) => { await dbB.execute(t.insert(`${B}-${t.name}-foreign`, A)); }),
        `RLS WITH CHECK must reject a cross-tenant insert into ${t.name}`,
      );
    });
  }
});