/**
 * W3b — api_keys RLS-blind lookup proof (AS/PLATFORM/2026/007).
 *
 * Substantiates the W3b headline finding: storage.getApiKeyByHash()
 * (server/storage.ts:2260-2263) uses the plain request-scoped RLS `db`, and
 * apiKeyAuth is mounted (server/routes.ts:679 `app.use("/api", apiKeyAuth)`)
 * BEFORE tenantMiddleware (server/routes.ts:682). At lookup time there is no
 * tenant DB phase, so app.current_tenant_id is unset -> current_tenant_id()
 * returns NULL (rls-init.ts:216) -> the api_keys policy
 * `USING (tenant_id = current_tenant_id())` (rls-init.ts:519-522) is UNKNOWN
 * -> fail-closed -> 0 rows. Therefore EVERY valid aegis_-prefixed key returns
 * undefined and the middleware 401s it: the api-key auth path is RLS-blind
 * (functionally non-functional, but fail-CLOSED — no key authenticates).
 *
 * This proves the mechanism at the DB layer, as the real RLS-subject role
 * `aegis_app`, paired POS/NEG (Rule 18):
 *   - never-set GUC  -> 0 rows   (NEG — the EXACT middleware state: hollow 401)
 *   - empty-string GUC -> 0 rows (NEG — §13.0 fail-closed)
 *   - GUC = tenant A -> 1 row    (POS — row IS findable, but ONLY with a GUC
 *                                  the middleware never sets at lookup time)
 *   - GUC = tenant B -> 0 rows   (NEG — cross-tenant isolation)
 *
 * HOLLOW-GREEN GUARDS:
 *   - Owner connection (DATABASE_URL) is used ONLY to seed the fixture and to
 *     clean up + verify-0-residual. NEVER an enforcement proof (owner BYPASSes
 *     RLS — an owner read proves nothing about isolation).
 *   - Fixture gate: physical tenant_id of the seeded row is verified == TENANT_A
 *     before any conclusion (defeats false-RED from a mis-seeded row and
 *     false-GREEN from a row that silently landed elsewhere).
 *   - Every denial (NEG) is paired with its positive (POS) in the same run.
 *
 * Requires: DATABASE_URL + AEGIS_APP_DB_PASSWORD.
 * Run: cd /home/runner/workspace && timeout 90 npx tsx scripts/vf-w3b-apikey-rls-blind-proof.ts
 */
import pg from "pg";
import { randomUUID, randomBytes, createHash } from "crypto";

const { Pool } = pg;

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

const TENANT_A = "aegis-sovereign";
const TENANT_B = "stanbic-ug-001";
const PREFIX = `vf-w3b-apikey-${Date.now()}`;
const idA = randomUUID();
// Synthetic key material — never a real credential. Hash mirrors how a real
// key would be stored (sha256 hex), so the lookup-by-hash path is exercised
// exactly as getApiKeyByHash runs it.
const rawKey = `aegis_${randomBytes(24).toString("hex")}`;
const keyHash = createHash("sha256").update(rawKey).digest("hex");
const keyPrefix = rawKey.slice(0, 12);

// Owner pool — FIXTURE SETUP/CLEANUP ONLY. Never an enforcement proof.
const ownerPool = new Pool({ connectionString: process.env.DATABASE_URL });

// App pool — aegis_app restricted RLS-subject role. ALL enforcement assertions.
const appUrl = new URL(process.env.DATABASE_URL);
appUrl.username = "aegis_app";
appUrl.password = process.env.AEGIS_APP_DB_PASSWORD!;
const appPool = new Pool({ connectionString: appUrl.toString() });

type Leg = { name: string; pass: boolean; detail: string };
const legs: Leg[] = [];
function record(name: string, pass: boolean, detail: string) {
  legs.push({ name, pass, detail });
  console.log(`  [${pass ? "PASS" : "FAIL"}] ${name} — ${detail}`);
}

/**
 * Count probe key rows visible to aegis_app under a given GUC state, looking up
 * by key_hash exactly as storage.getApiKeyByHash does.
 */
async function aegisLookupByHash(opts: {
  setGuc: boolean;
  guc?: string;
}): Promise<number> {
  const c = await appPool.connect();
  try {
    await c.query("BEGIN");
    if (opts.setGuc) {
      await c.query("SELECT set_config('app.current_tenant_id', $1, true)", [
        opts.guc ?? "",
      ]);
    }
    const r = await c.query(
      `SELECT id FROM api_keys WHERE key_hash = $1`,
      [keyHash],
    );
    await c.query("ROLLBACK");
    return r.rowCount ?? 0;
  } finally {
    c.release();
  }
}

async function main() {
  console.log("=== W3b api_keys RLS-blind lookup proof ===");
  console.log(`  probe key id=${idA} tenant=${TENANT_A} hash=${keyHash.slice(0, 12)}…`);

  // ── Fixture seed (owner) ────────────────────────────────────────────────
  await ownerPool.query(
    `INSERT INTO api_keys (id, name, key_hash, key_prefix, tenant_id, is_active)
     VALUES ($1, $2, $3, $4, $5, true)`,
    [idA, `${PREFIX}-name`, keyHash, keyPrefix, TENANT_A],
  );

  // ── Fixture gate: physical tenant_id == expected (owner read, setup only) ─
  const phys = await ownerPool.query<{ tenant_id: string }>(
    `SELECT tenant_id FROM api_keys WHERE id = $1`,
    [idA],
  );
  const gateOk = phys.rows[0]?.tenant_id === TENANT_A;
  record(
    "FIXTURE-GATE physical tenant_id == expected",
    gateOk,
    `tenant_id=${phys.rows[0]?.tenant_id} (exp ${TENANT_A})`,
  );
  if (!gateOk) {
    console.error("  ABORT: fixture gate failed — would be hollow. Cleaning up.");
    await cleanup();
    process.exit(1);
  }

  // ── Enforcement assertions (ALL as aegis_app) ───────────────────────────
  // NEG (THE BUG): never-set GUC is the EXACT apiKeyAuth middleware state
  // (mounts before tenantMiddleware). A valid key returns 0 rows -> 401 hollow.
  const neverSet = await aegisLookupByHash({ setGuc: false });
  record(
    "never-set GUC (= apiKeyAuth middleware state): 0 rows -> valid key 401s (RLS-BLIND)",
    neverSet === 0,
    `rows=${neverSet} (expected 0 — confirms hollow 401)`,
  );

  // NEG: empty-string GUC -> fail-closed.
  const emptyGuc = await aegisLookupByHash({ setGuc: true, guc: "" });
  record(
    "empty-string GUC: 0 rows (§13.0 fail-closed)",
    emptyGuc === 0,
    `rows=${emptyGuc}`,
  );

  // POS: WITH the correct tenant GUC the row IS findable — proving the row
  // exists and the ONLY reason the middleware fails is the missing GUC.
  const tenantA = await aegisLookupByHash({ setGuc: true, guc: TENANT_A });
  record(
    "tenant-A GUC: 1 row (POS — row IS findable, but ONLY with a GUC the middleware never sets)",
    tenantA === 1,
    `rows=${tenantA} (expected 1)`,
  );

  // NEG: cross-tenant — tenant B context cannot see tenant A's key.
  const tenantB = await aegisLookupByHash({ setGuc: true, guc: TENANT_B });
  record(
    "tenant-B GUC: 0 rows (NEG — cross-tenant isolation)",
    tenantB === 0,
    `rows=${tenantB}`,
  );

  // ── Cleanup (owner) + 0-residual verify ─────────────────────────────────
  await cleanup();

  const overall = legs.every((l) => l.pass);
  console.log("");
  console.log(
    `ACCEPTANCE: ${legs.filter((l) => l.pass).length}/${legs.length} legs PASS — ${overall ? "ALL PASS" : "FAIL"}`,
  );
  await ownerPool.end();
  await appPool.end();
  process.exit(overall ? 0 : 1);
}

async function cleanup() {
  await ownerPool.query(`DELETE FROM api_keys WHERE id = $1`, [idA]);
  const residual = await ownerPool.query(
    `SELECT id FROM api_keys WHERE id = $1`,
    [idA],
  );
  record(
    "CLEANUP Rule 11 — 0 residual probe rows",
    residual.rowCount === 0,
    `residual=${residual.rowCount}`,
  );
}

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