/**
 * VF — Transaction-anomaly detection, P2 (persistence + sweep + RLS isolation).
 * #50. Run against a DB where boot has created the M66 tables + applied RLS.
 *
 * Setup seeds synthetic kyt_results via the owner pool (ddlPool, RLS-bypass — test
 * data only). The SWEEP and every isolation read run as aegis_app (the RLS-subject
 * role; the owner bypasses RLS, so a leg run as owner would be hollow-green).
 *
 *   Leg a  sweep-reads-real       — persisted detection score == engine recompute
 *   Leg b  durable                — detection survives a fresh read
 *   Leg c  RLS isolates (aegis_app)— tenant A's detection invisible under tenant B
 *   Leg d  null-partition empty   — 0 null-tenant kyt_results (RLS-construction invariant)
 *   Leg e  three-state empty-state — NOT_YET_SWEPT (pre-sweep) / DETECTIONS / CLEAN
 *   Leg f  determinism            — re-sweep → identical detection (idempotent, same score)
 *
 * Exit 0 iff all pass.
 */

import { sql } from "drizzle-orm";
import { randomUUID } from "crypto";
import { appPool, ddlPool, withTenantRls } from "../server/db";
import { runSweep } from "../server/lib/transaction-anomaly/sweep";
import { getDetections, getAnomalyView, SqlRunner } from "../server/lib/transaction-anomaly/store";
import { computeBaseline } from "../server/lib/transaction-anomaly/baseline";
import { scoreTransaction } from "../server/lib/transaction-anomaly/detector";
import { TxnRow } from "../server/lib/transaction-anomaly/types";

const NOW = new Date("2026-07-14T12:00:00.000Z");
const DAY = 86400000;
const SFX = String(Date.now());

type Leg = { name: string; pass: boolean; detail: string };
const legs: Leg[] = [];
const rec = (name: string, pass: boolean, detail: string) => legs.push({ name, pass, detail });

async function createTenant(tag: string): Promise<string> {
  const r = await ddlPool.query(
    `INSERT INTO tenants (name, slug, industry, tier) VALUES ($1, $2, 'banking', 'pilot') RETURNING id`,
    [`P2VF ${tag}`, `p2vf-${tag}-${SFX}`],
  );
  return r.rows[0].id as string;
}

/** Seed 30 normal daily txns; optionally a most-recent extreme outlier (returns its id). */
async function seedKyt(tenantId: string, userId: string, withOutlier: boolean): Promise<string | null> {
  for (let i = 1; i <= 30; i++) {
    const amount = 100000 * (0.7 + (i % 7) * 0.1);
    await ddlPool.query(
      `INSERT INTO kyt_results (id, user_id, amount, currency, recipient_type, channel, risk_level, risk_score, recommendation, tenant_id, scored_at)
       VALUES ($1,$2,$3,'UGX','KNOWN','MOBILE','LOW',0.100,'APPROVE',$4,$5)`,
      [randomUUID(), userId, amount, tenantId, new Date(NOW.getTime() - i * DAY).toISOString()],
    );
  }
  if (!withOutlier) return null;
  const outlierId = randomUUID();
  await ddlPool.query(
    `INSERT INTO kyt_results (id, user_id, amount, currency, recipient_type, channel, risk_level, risk_score, recommendation, tenant_id, scored_at)
     VALUES ($1,$2,$3,'UGX','FLAGGED','API','LOW',0.100,'APPROVE',$4,$5)`,
    [outlierId, userId, 10000000, tenantId, NOW.toISOString()],
  );
  return outlierId;
}

/** Rebuild the TxnRow view the sweep sees for a user (to recompute the engine score independently). */
async function readUserTxns(tenantId: string, userId: string): Promise<TxnRow[]> {
  const r = await ddlPool.query(
    `SELECT id, user_id, amount, channel, recipient_type, tenant_id, scored_at
     FROM kyt_results WHERE tenant_id=$1 AND user_id=$2 ORDER BY scored_at DESC`,
    [tenantId, userId],
  );
  return r.rows.map((x: Record<string, unknown>) => ({
    id: x.id as string,
    userId: x.user_id as string,
    amount: Number(x.amount),
    channel: x.channel as string,
    recipientType: x.recipient_type as string,
    tenantId: x.tenant_id as string | null,
    scoredAt: new Date(x.scored_at as string),
  }));
}

async function main() {
  const tA = await createTenant("a");
  const tB = await createTenant("b");
  const tC = await createTenant("c");
  const outlierA = await seedKyt(tA, "u1", true);
  const outlierB = await seedKyt(tB, "u2", true);
  await seedKyt(tC, "u3", false); // clean tenant

  // current_user must be aegis_app on the isolation path (not the owner) — else hollow-green.
  const who = await withTenantRls(tA, async (tdb) => {
    const r = await tdb.execute(sql`SELECT current_user AS u`);
    return ((r as { rows?: { u: string }[] }).rows ?? [])[0]?.u;
  });

  // Leg e (pre-sweep): a tenant never swept → NOT_YET_SWEPT (not CLEAN).
  const preView = await withTenantRls(tA, (tdb) => getAnomalyView(tdb as unknown as SqlRunner));

  // Run the real sweep (aegis_app under withTenantRls internally).
  await runSweep(NOW);

  // Leg a — persisted score == independent engine recompute.
  const txnsA = await readUserTxns(tA, "u1");
  const baseA = computeBaseline(txnsA, "u1", tA, NOW);
  const recompute = scoreTransaction(txnsA[0], baseA, txnsA); // txnsA[0] = most-recent = outlier
  const detA = await withTenantRls(tA, (tdb) => getDetections(tdb as unknown as SqlRunner));
  const persisted = detA.find((d) => d.kytResultId === outlierA);
  rec("Leg a sweep-reads-real", !!persisted && persisted.score === recompute.score,
    `persisted score=${persisted?.score} == recompute=${recompute.score}`);

  // Leg b — durable across a fresh read.
  const detA2 = await withTenantRls(tA, (tdb) => getDetections(tdb as unknown as SqlRunner));
  rec("Leg b durable", detA2.some((d) => d.kytResultId === outlierA), `re-read found ${detA2.length} detection(s) for tenant A`);

  // Leg c — RLS isolates as aegis_app: A sees only A's, B sees only B's, cross-reads see nothing.
  const detB = await withTenantRls(tB, (tdb) => getDetections(tdb as unknown as SqlRunner));
  const aSeesOnlyA = detA2.every((d) => d.tenantId === tA) && detA2.some((d) => d.kytResultId === outlierA);
  const bSeesOnlyB = detB.every((d) => d.tenantId === tB) && detB.some((d) => d.kytResultId === outlierB);
  const aCannotSeeB = !detA2.some((d) => d.kytResultId === outlierB);
  const bCannotSeeA = !detB.some((d) => d.kytResultId === outlierA);
  rec("Leg c RLS isolates (aegis_app)", who === "aegis_app" && aSeesOnlyA && bSeesOnlyB && aCannotSeeB && bCannotSeeA,
    `current_user=${who}; A sees only A (${detA2.length}), B sees only B (${detB.length}), no cross-visibility`);

  // Leg d — null-partition empty (RLS-construction invariant), both tables.
  const nullKyt = await ddlPool.query(`SELECT COUNT(*)::int AS n FROM kyt_results WHERE tenant_id IS NULL`);
  const nullDet = await ddlPool.query(`SELECT COUNT(*)::int AS n FROM anomaly_detections WHERE tenant_id IS NULL`);
  rec("Leg d null-partition empty", nullKyt.rows[0].n === 0 && nullDet.rows[0].n === 0,
    `kyt_results null-tenant=${nullKyt.rows[0].n}, anomaly_detections null-tenant=${nullDet.rows[0].n}`);

  // Leg e — three-state: pre-sweep NOT_YET_SWEPT; post-sweep DETECTIONS (A) and CLEAN (C).
  const viewA = await withTenantRls(tA, (tdb) => getAnomalyView(tdb as unknown as SqlRunner));
  const viewC = await withTenantRls(tC, (tdb) => getAnomalyView(tdb as unknown as SqlRunner));
  rec("Leg e three-state empty-state",
    preView.status === "NOT_YET_SWEPT" && viewA.status === "DETECTIONS" && viewC.status === "CLEAN",
    `pre=${preView.status}, A=${viewA.status}, C=${viewC.status}`);

  // Leg f — determinism: re-sweep → same detection, same score (idempotent).
  await runSweep(NOW);
  const detAafter = await withTenantRls(tA, (tdb) => getDetections(tdb as unknown as SqlRunner));
  const same = detAafter.filter((d) => d.kytResultId === outlierA);
  rec("Leg f determinism", same.length === 1 && same[0].score === (persisted?.score ?? -1),
    `after re-sweep: ${same.length} detection for the outlier, score=${same[0]?.score} (idempotent)`);

  // Cleanup (harmless on a throwaway DB; safe if run elsewhere).
  for (const t of [tA, tB, tC]) {
    await ddlPool.query(`DELETE FROM anomaly_detections WHERE tenant_id=$1`, [t]);
    await ddlPool.query(`DELETE FROM anomaly_sweep_state WHERE tenant_id=$1`, [t]);
    await ddlPool.query(`DELETE FROM kyt_results WHERE tenant_id=$1`, [t]);
    await ddlPool.query(`DELETE FROM tenants WHERE id=$1`, [t]);
  }

  console.log("\n=== VF transaction-anomaly P2 ===\n");
  let failed = 0;
  for (const l of legs) {
    console.log(`${l.pass ? "PASS" : "FAIL"}  ${l.name} — ${l.detail}`);
    if (!l.pass) failed++;
  }
  console.log(`\n${legs.length - failed}/${legs.length} legs passed.`);
  await appPool.end().catch(() => {});
  await ddlPool.end().catch(() => {});
  if (failed > 0) {
    console.log(`\nP2 GATE: FAIL (${failed} leg(s))`);
    process.exit(1);
  }
  console.log("\nP2 GATE: GREEN — persisted, durable, RLS-isolated (aegis_app), null-empty, honest three-state, deterministic.");
}

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