-- ============================================================================
-- The Leakage Report — Federal Contracts Awarded During Active Exclusion Periods
-- Procurement W4 · reproducible SQL sidecar · buildplan §6
--
-- This is the EXACT join the pure TypeScript engine (src/lib/procurement/leakage.ts
-- + reconcile.ts::exclusionStatusAsOf) computes, expressed against the committed
-- procurement_* tables so any third party can re-derive every published figure
-- directly in Postgres. Run against the Supabase project `fonteum-platform`.
--
-- DEFINITION (the only definition — exact regulatory facts, no inference):
--   An award is LEAKAGE iff its signed (action) date falls inside an exclusion's
--   ACTIVE window for the SAME confirmed entity:
--       activation_date <= action_date
--       AND (termination_date IS NULL OR termination_date > action_date)
--   joined on a CONFIRMED EXACT KEY only (UEI or CAGE). A held / name-only /
--   unmatched exclusion, or one with a NULL activation_date, never qualifies.
--
-- Each row is the TWO DATED FACTS the regulator published (award signed-date +
-- exclusion active window). It is NOT an accusation of fraud and carries no score.
-- "Confirm current status at SAM.gov" for every entity.
--
-- BITEMPORAL: only the CURRENT version of each row participates (valid_to IS NULL).
-- To reproduce the report AS-OF a past date D, replace each `valid_to IS NULL`
-- with `(valid_from <= D AND (valid_to IS NULL OR valid_to > D))`.
-- ============================================================================

-- ── 0. CONFIRMED exclusion links only (the spine; hold-ambiguous) ───────────
-- match_status IN ('confirmed_uei','confirmed_cage') with a non-null activation.
-- (held_name_only / unmatched are excluded by construction.)

-- ── 1. Leakage records — confirmed_uei join (award.recipient_uei = excl.uei) ─
--    + confirmed_cage join (award.recipient_cage = excl.cage_code), UNION'd.
WITH leakage AS (
  SELECT
    a.award_unique_key,
    a.action_date::date            AS award_action_date,
    a.recipient_uei,
    a.recipient_cage,
    a.recipient_name,
    a.obligation_amount,
    a.awarding_agency,
    e.exclusion_record_key,
    e.match_status                 AS exclusion_match_status,
    e.exclusion_type,
    e.excluding_agency,
    e.activation_date::date        AS exclusion_activation_date,
    e.termination_date::date       AS exclusion_termination_date,
    COALESCE(a.recipient_uei, a.recipient_cage) AS recipient_key
  FROM public.procurement_awards a
  JOIN public.procurement_exclusion_links e
    ON e.match_status = 'confirmed_uei'
   AND e.uei = a.recipient_uei
  WHERE a.valid_to IS NULL
    AND e.valid_to IS NULL
    AND a.action_date IS NOT NULL
    AND e.activation_date IS NOT NULL
    AND e.activation_date <= a.action_date
    AND (e.termination_date IS NULL OR e.termination_date > a.action_date)

  UNION

  SELECT
    a.award_unique_key,
    a.action_date::date,
    a.recipient_uei,
    a.recipient_cage,
    a.recipient_name,
    a.obligation_amount,
    a.awarding_agency,
    e.exclusion_record_key,
    e.match_status,
    e.exclusion_type,
    e.excluding_agency,
    e.activation_date::date,
    e.termination_date::date,
    COALESCE(a.recipient_uei, a.recipient_cage)
  FROM public.procurement_awards a
  JOIN public.procurement_exclusion_links e
    ON e.match_status = 'confirmed_cage'
   AND e.cage_code = a.recipient_cage
  WHERE a.valid_to IS NULL
    AND e.valid_to IS NULL
    AND a.action_date IS NOT NULL
    AND e.activation_date IS NOT NULL
    AND e.activation_date <= a.action_date
    AND (e.termination_date IS NULL OR e.termination_date > a.action_date)
)

-- ── 2. Headline aggregates (distinct-award $ — an award counted once) ────────
SELECT
  COUNT(*)                                   AS total_findings,
  COUNT(DISTINCT award_unique_key)           AS total_leakage_awards,
  COUNT(DISTINCT recipient_key)              AS total_entities,
  COALESCE(SUM(DISTINCT_obligation), 0)      AS total_obligated
FROM (
  SELECT
    award_unique_key,
    recipient_key,
    -- one obligation value per distinct award (avoids double-count across exclusions)
    MAX(obligation_amount) AS DISTINCT_obligation
  FROM leakage
  GROUP BY award_unique_key, recipient_key
) d, leakage
-- NOTE: the headline numbers above are also recomputed deterministically by
-- src/lib/procurement/leakage.ts::aggregateLeakage; the two MUST agree.
;

-- ── EXPECTED RESULTS (committed snapshot src/lib/procurement/leakage-snapshot.json) ──
-- As of the W4 commit (2026-06-19), the procurement_* public tables are NOT yet
-- populated on the reachable project (the W1 schema migration is operator-pending
-- and the targeted awards-for-excluded backfill has not run), so:
--
--   total_findings        = 0
--   total_leakage_awards  = 0
--   total_entities        = 0
--   total_obligated       = 0
--
-- This zero is HONEST coverage, not a null result about the world: it reflects an
-- empty ingested set. The study page is shipped as a non-indexed draft until the
-- ingested set yields a defensible headline. After the operator applies the W1
-- migration and runs:
--     npm run seed:procurement:exclusion-links
--     npm run seed:procurement:awards-for-excluded -- --pages 2 --enrich
-- re-run this query and regenerate the snapshot:
--     npm run research:procurement-leakage -- --write
-- and the expected results will reflect the real, reproducible counts.

-- ── 3. Breakdown by awarding agency (distinct awards + obligated) ────────────
--   SELECT awarding_agency,
--          COUNT(*)                         AS findings,
--          COUNT(DISTINCT award_unique_key) AS awards,
--          SUM(obligation_amount)           AS obligated  -- (per-finding; use the
--                                                          -- distinct-award CTE for $)
--   FROM leakage GROUP BY awarding_agency ORDER BY awards DESC;

-- ── 4. Breakdown by exclusion type ──────────────────────────────────────────
--   SELECT exclusion_type, COUNT(*) AS findings,
--          COUNT(DISTINCT award_unique_key) AS awards
--   FROM leakage GROUP BY exclusion_type ORDER BY awards DESC;

-- ── 5. Breakdown by award-signed year ───────────────────────────────────────
--   SELECT EXTRACT(YEAR FROM award_action_date)::int AS year,
--          COUNT(DISTINCT award_unique_key) AS awards,
--          SUM(obligation_amount)           AS obligated
--   FROM leakage GROUP BY 1 ORDER BY 1;

-- ── 6. Per-entity timeline (the two dated facts, award-date ordered) ─────────
--   SELECT recipient_key, recipient_name, award_unique_key, award_action_date,
--          exclusion_record_key, exclusion_activation_date, exclusion_termination_date,
--          exclusion_type, awarding_agency, obligation_amount
--   FROM leakage
--   ORDER BY recipient_key, award_action_date;
