/**
 * AS/PLATFORM/2026/003 T004 — RLS-BEHAVIORAL diagnostic battery (DEV-only).
 *
 * A manifest-driven, looping RLS-behavioral diagnostic. For each MANIFESTED real
 * TENANT_TABLES table it seeds an owner-tenant row (owner pool — ground truth),
 * asserts the row's PHYSICAL tenant_id == ownerTenant (the fixture-identity gate
 * that stops a heterogeneous-table false-RED), then drives the generic per-table
 * driver rlsPairedProbe as the aegis_app RLS-subject role via withTenantRls — the
 * least-privilege role real requests use, NOT the owner (which BYPASSRLS →
 * hollow-green). Each table leg is Rule-18 paired inside rlsPairedProbe: the
 * POSITIVE leg (owner tenant sees its own row) is asserted alongside the NEGATIVE
 * leg (a foreign tenant is denied), so a "denial" can never pass for the wrong
 * reason (a table that denies everyone would fail the positive).
 *
 * Acceptance gate (the T004 modest acceptance), proven NON-tautologically:
 *   • SEEDED GAP   — a self-created RLS-DISABLED scratch table: a foreign tenant
 *                    CAN read the owner's row → the loop CATCHES the gap
 *                    (foreignDenied=false, pass=false).
 *   • KNOWN-GOOD   — the SAME scratch table after ENABLE RLS + tenant_isolation
 *                    policy, PLUS four real TENANT_TABLES rows: owner sees its
 *                    row, foreign tenant denied (pass=true).
 *   • aegis_app    — every behavioral leg runs through rlsPairedProbe →
 *                    withTenantRls (appPool = aegis_app, non-BYPASSRLS).
 *
 * SCOPE (architect plan, bounded — Scope A): a fixture manifest for the cleanly-
 * seedable, cleanly-deletable, varchar-id tables harvested from the production
 * /api/internal/rls-diagnostic recipes. Every TENANT_TABLES entry WITHOUT a
 * manifest entry is reported SKIPPED_NOT_MANIFESTED and is NOT counted as a pass
 * (honest coverage, no hollow-green). Generic introspection-fill for all 77 was
 * rejected: FK/CHECK/semantic NOT NULL values would manufacture false-RED noise.
 *
 * Rule 11 — only self-created scratch state is touched; a CLEANUP leg re-queries
 * to confirm 0 residual seeded rows and that the scratch table is dropped.
 *
 * Runs against the live dev DB (DATABASE_URL owner pool + appPool aegis_app via
 * the harness). Exit 0 iff ALL legs PASS.
 */

import {
  ProofRun,
  verdict,
  rlsPairedProbe,
  ownerPool,
  endOwnerPool,
  randomUUID,
  assertIdent,
} from "./lib/proof-harness";
import { TENANT_TABLES } from "../server/lib/rls-init";

const RUN = Date.now();
const OWNER_TENANT = `t004-owner-${RUN}`;
const FOREIGN_TENANT = `t004-foreign-${RUN}`;
const SCRATCH = `_t004_rls_gap_${RUN}`;

type OwnerPool = ReturnType<typeof ownerPool>;

/** A per-table fixture: how to seed one owner-tenant row and how to remove it.
 *  idColumn/tenantColumn are validated with assertIdent before any interpolation;
 *  rlsPairedProbe itself keys on the `id` column, so every fixture uses id. */
interface Fixture {
  table: string;
  tenantColumn: string;
  idColumn: string;
  /** harvested from the proven production rls-diagnostic INSERT recipes. */
  seed(o: OwnerPool, ownerTenant: string, runId: number): Promise<string>;
}

const FIXTURES: Fixture[] = [
  {
    table: "incident_slas",
    tenantColumn: "tenant_id",
    idColumn: "id",
    seed: async (o, tenant, run) => {
      const r = await o.query<{ id: string }>(
        `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', 'T004 RLS probe', 'open', 240, 0, false, NOW())
         RETURNING id`,
        [tenant, `${run}-inc`],
      );
      return String(r.rows[0].id);
    },
  },
  {
    table: "dsar_requests",
    tenantColumn: "tenant_id",
    idColumn: "id",
    seed: async (o, tenant, run) => {
      const deadline = new Date(Date.now() + 7 * 86_400_000);
      const r = await o.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, 'T004 Probe', 't004@probe.local', 'access',
                 'portal', $3, 'received', NOW(), NOW(), NOW())
         RETURNING id`,
        [tenant, `${run}-DSAR`, deadline],
      );
      return String(r.rows[0].id);
    },
  },
  {
    table: "kyt_results",
    tenantColumn: "tenant_id",
    idColumn: "id",
    seed: async (o, tenant) => {
      const id = randomUUID();
      await o.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())`,
        [id, tenant],
      );
      return id;
    },
  },
  {
    table: "billing_invoices",
    tenantColumn: "tenant_id",
    idColumn: "id",
    seed: async (o, tenant) => {
      const id = randomUUID();
      await o.query(
        `INSERT INTO billing_invoices
           (id, tenant_id, period, status, line_items_json,
            subtotal_usd, tax_usd, total_usd)
         VALUES ($1, $2, '2026-t004-probe', 'DRAFT', '[]', 0, 0, 0)`,
        [id, tenant],
      );
      return id;
    },
  },
];

interface Seeded {
  table: string;
  idColumn: string;
  id: string;
}

async function main(): Promise<void> {
  const run = new ProofRun(
    "AS/PLATFORM/2026/003 T004 — RLS-behavioral diagnostic battery",
    "DEV",
  );
  const o = ownerPool();
  const seeded: Seeded[] = [];
  let scratchCreated = false;

  // Fail-closed global precondition: a same-tenant probe cannot prove isolation.
  if (OWNER_TENANT === FOREIGN_TENANT) {
    throw new Error("ownerTenant and foreignTenant must differ");
  }

  try {
    // ════════════════════════════════════════════════════════════════════════
    // ACCEPTANCE LEGS 1+2 — SEEDED GAP / KNOWN-GOOD on a self-created scratch
    // table. Proves the loop both CATCHES a real RLS gap and PASSES a known-good
    // config, without weakening any production table's RLS.
    // ════════════════════════════════════════════════════════════════════════
    assertIdent(SCRATCH);
    await o.query(
      `CREATE TABLE "${SCRATCH}" (id varchar PRIMARY KEY, tenant_id varchar NOT NULL)`,
    );
    scratchCreated = true;
    await o.query(`GRANT SELECT ON "${SCRATCH}" TO aegis_app`);
    const scratchRow = randomUUID();
    await o.query(`INSERT INTO "${SCRATCH}" (id, tenant_id) VALUES ($1, $2)`, [
      scratchRow,
      OWNER_TENANT,
    ]);

    // LEG 1 — RLS DISABLED → foreign tenant reads the owner's row (gap caught).
    const gap = await rlsPairedProbe(SCRATCH, scratchRow, OWNER_TENANT, FOREIGN_TENANT);
    const gapCaught = gap.ownerExists && gap.ownerVisible && !gap.foreignDenied && !gap.pass;
    run.leg({
      id: "T004-GAP",
      scenario: "loop probes an RLS-DISABLED scratch table as aegis_app (seeded gap)",
      expectation:
        "gap surfaced: ownerExists & ownerVisible true, foreignDenied FALSE, pass FALSE",
      observed: `ownerExists=${gap.ownerExists} ownerVisible=${gap.ownerVisible}(n=${gap.ownerVisibleCount}) foreignDenied=${gap.foreignDenied}(n=${gap.foreignVisibleCount}) pass=${gap.pass}`,
      verdict: gapCaught
        ? verdict.verified("foreign tenant read the owner's row as aegis_app → gap detected")
        : verdict.fail(),
      pass: gapCaught,
      signals: { ...gap },
    });

    // enable RLS + the tenant_isolation policy (the fix) — same scratch table.
    await o.query(`ALTER TABLE "${SCRATCH}" ENABLE ROW LEVEL SECURITY`);
    await o.query(
      `CREATE POLICY tenant_isolation ON "${SCRATCH}" USING (tenant_id = current_tenant_id()) WITH CHECK (tenant_id = current_tenant_id())`,
    );

    // LEG 2 — RLS ENFORCED → owner sees its row, foreign tenant denied (good).
    const good = await rlsPairedProbe(SCRATCH, scratchRow, OWNER_TENANT, FOREIGN_TENANT);
    const isolated = good.ownerExists && good.ownerVisible && good.foreignDenied && good.pass;
    run.leg({
      id: "T004-GOOD",
      scenario: "loop probes the SAME table after ENABLE RLS + tenant_isolation (known-good)",
      expectation: "isolation: owner sees row (n>=1), foreign denied (n=0), pass TRUE",
      observed: `ownerVisible=${good.ownerVisible}(n=${good.ownerVisibleCount}) foreignDenied=${good.foreignDenied}(n=${good.foreignVisibleCount}) pass=${good.pass}`,
      verdict: isolated
        ? verdict.verified("owner sees its row, foreign tenant denied — both as aegis_app under withTenantRls")
        : verdict.fail(),
      pass: isolated,
      signals: { ...good },
    });

    // ════════════════════════════════════════════════════════════════════════
    // LEGS 3..N — MANIFESTED real TENANT_TABLES tables (the looping diagnostic).
    // ════════════════════════════════════════════════════════════════════════
    for (const fx of FIXTURES) {
      assertIdent(fx.table);
      assertIdent(fx.tenantColumn);
      assertIdent(fx.idColumn);

      // precondition: the table must actually be an RLS-governed tenant table.
      if (!TENANT_TABLES.includes(fx.table)) {
        run.leg({
          id: `T004-TBL-${fx.table}`,
          scenario: `manifest entry ${fx.table} must be a real TENANT_TABLES table`,
          expectation: "table ∈ TENANT_TABLES",
          observed: "INVALID_FIXTURE — table not in TENANT_TABLES",
          verdict: verdict.fail(),
          pass: false,
          signals: { inTenantTables: false },
        });
        continue;
      }

      try {
        const id = await fx.seed(o, OWNER_TENANT, RUN);
        seeded.push({ table: fx.table, idColumn: fx.idColumn, id });

        // FIXTURE-IDENTITY GATE — the row must physically exist AND its tenant
        // column must equal ownerTenant, else rlsPairedProbe would false-RED on
        // a heterogeneous table. A failure here is INVALID_FIXTURE, not an RLS
        // result, and fails the run rather than counting a hollow probe.
        const phys = await o.query<{ t: string }>(
          `SELECT ${fx.tenantColumn} AS t FROM "${fx.table}" WHERE ${fx.idColumn} = $1`,
          [id],
        );
        const exists = phys.rows.length === 1;
        const tenantMatches = exists && phys.rows[0].t === OWNER_TENANT;
        if (!exists || !tenantMatches) {
          run.leg({
            id: `T004-TBL-${fx.table}`,
            scenario: `fixture-identity gate for ${fx.table}`,
            expectation: `seeded row exists AND physical ${fx.tenantColumn} == ownerTenant`,
            observed: `INVALID_FIXTURE — exists=${exists} tenantMatches=${tenantMatches} observed=${phys.rows[0]?.t ?? "<none>"}`,
            verdict: verdict.fail(),
            pass: false,
            signals: { exists, tenantMatches },
          });
          continue;
        }

        // behavioral probe — aegis_app, Rule-18 paired (owner sees / foreign denied).
        const p = await rlsPairedProbe(fx.table, id, OWNER_TENANT, FOREIGN_TENANT);
        const ok = p.ownerExists && p.ownerVisible && p.foreignDenied && p.pass;
        run.leg({
          id: `T004-TBL-${fx.table}`,
          scenario: `real TENANT_TABLES row (${fx.table}) probed as aegis_app — owner sees own row, foreign tenant denied`,
          expectation: "ownerVisible n>=1 AND foreignDenied n=0 (paired), pass TRUE",
          observed: `ownerVisible=${p.ownerVisible}(n=${p.ownerVisibleCount}) foreignDenied=${p.foreignDenied}(n=${p.foreignVisibleCount}) pass=${p.pass}`,
          verdict: ok
            ? verdict.verified(`RLS enforced on ${fx.table}: owner sees its row, foreign tenant denied (aegis_app, physical tenant_id verified == ownerTenant)`)
            : verdict.fail(),
          pass: ok,
          signals: { ...p, physicalTenantMatch: true },
        });
      } catch (e) {
        run.leg({
          id: `T004-TBL-${fx.table}`,
          scenario: `seed + probe ${fx.table}`,
          expectation: "seed and behavioral probe complete without throwing",
          observed: `threw: ${(e as Error)?.message ?? String(e)}`,
          verdict: verdict.fail(),
          pass: false,
          signals: { threw: true },
        });
      }
    }

    // ════════════════════════════════════════════════════════════════════════
    // SKIP-ACCOUNTING — every TENANT_TABLES entry is either probed above or
    // explicitly SKIPPED_NOT_MANIFESTED. Skipped tables are NOT passes.
    // ════════════════════════════════════════════════════════════════════════
    const manifested = FIXTURES.map((f) => f.table);
    const manifestedSet = new Set(manifested);
    const allInTenantTables = manifested.every((t) => TENANT_TABLES.includes(t));
    const noDupes = manifested.length === manifestedSet.size;
    const skipped = TENANT_TABLES.filter((t) => !manifestedSet.has(t));
    const accountingComplete =
      allInTenantTables &&
      noDupes &&
      manifestedSet.size + skipped.length === TENANT_TABLES.length;
    run.leg({
      id: "T004-ACCOUNTING",
      scenario: "skip-accounting: every TENANT_TABLES entry is probed or explicitly skipped",
      expectation: `manifested + skipped == TENANT_TABLES.length (${TENANT_TABLES.length}); all manifested ∈ TENANT_TABLES; no dupes`,
      observed: `manifested=${manifestedSet.size} skipped=${skipped.length} total=${TENANT_TABLES.length} allInTT=${allInTenantTables} noDupes=${noDupes}`,
      verdict: accountingComplete
        ? verdict.verified(`every table accounted for; ${skipped.length} SKIPPED_NOT_MANIFESTED are NOT counted as passes (honest coverage)`)
        : verdict.fail(),
      pass: accountingComplete,
      signals: { manifestedCount: manifestedSet.size, skippedCount: skipped.length, total: TENANT_TABLES.length },
    });
    console.log(`\nSKIPPED_NOT_MANIFESTED (${skipped.length}/${TENANT_TABLES.length}):`);
    console.log(`  ${skipped.join(", ")}`);

    // ════════════════════════════════════════════════════════════════════════
    // CLEANUP (Rule 11) — remove ONLY self-created state, then re-query to prove
    // 0 residual seeded rows and that the scratch table is gone.
    // ════════════════════════════════════════════════════════════════════════
    for (const s of seeded) {
      await o
        .query(`DELETE FROM "${s.table}" WHERE ${s.idColumn} = $1`, [s.id])
        .catch(() => {});
    }
    await o.query(`DROP TABLE IF EXISTS "${SCRATCH}" CASCADE`).catch(() => {});

    let residual = 0;
    const residualDetail: string[] = [];
    for (const s of seeded) {
      const c = await o.query(`SELECT 1 FROM "${s.table}" WHERE ${s.idColumn} = $1`, [s.id]);
      if (c.rows.length) {
        residual++;
        residualDetail.push(`${s.table}:${s.id}`);
      }
    }
    const scratchGone =
      (await o.query<{ t: string | null }>(`SELECT to_regclass($1) AS t`, [SCRATCH])).rows[0].t ===
      null;
    const clean = residual === 0 && scratchGone;
    run.leg({
      id: "T004-CLEANUP",
      scenario: "Rule 11 — self-created seed rows + scratch table removed",
      expectation: "0 residual seeded rows AND scratch table dropped (re-queried)",
      observed: `residual=${residual}${residualDetail.length ? ` [${residualDetail.join(", ")}]` : ""} scratchGone=${scratchGone}`,
      verdict: clean
        ? verdict.verified("re-query confirms all self-created state removed (0 residual, scratch dropped)")
        : verdict.fail(),
      pass: clean,
      signals: { residual, scratchGone },
    });
  } finally {
    // safety net (idempotent) — covers a mid-run throw before the cleanup leg.
    for (const s of seeded) {
      await o
        .query(`DELETE FROM "${s.table}" WHERE ${s.idColumn} = $1`, [s.id])
        .catch(() => {});
    }
    if (scratchCreated) {
      await o.query(`DROP TABLE IF EXISTS "${SCRATCH}" CASCADE`).catch(() => {});
    }
  }

  run.report();
  const { allPass } = run.acceptance();
  await endOwnerPool();
  process.exit(allPass ? 0 : 1);
}

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