-- ============================================================================
-- The State of Federal Contractor Integrity 2026 — reproducible SQL
-- Flagship annual roundup · SYNTHESIS study · /gov procurement silo
--
-- This roundup does NOT introduce a new query. It re-states the figures of the
-- two component studies it bundles, each of which ships its own reproducible SQL
-- sidecar. Run these against the Supabase project `fonteum-platform`; every
-- headline figure on the roundup page is re-derivable here, with no inference and
-- no entity named (aggregate counts + the two dated-fact join only).
--
--   • Federal Suspension & Debarment Scorecard  → src/lib/procurement/debarment-scorecard.sql
--       served at /gov/research/federal-debarment-scorecard/scorecard.sql
--   • The Leakage Report                        → src/lib/procurement/leakage.sql
--       served at /gov/research/contracts-awarded-during-active-exclusion/leakage.sql
--
-- The roundup page reads the COMMITTED snapshots of both studies at build time
-- (src/lib/procurement/debarment-scorecard.json + leakage-snapshot.json), so its
-- numbers always match the pages it links to.
-- ============================================================================

-- ── A. Registry scale + agency concentration (Debarment Scorecard) ──────────
-- Headline: 324,126 active exclusions · 56 excluding agencies; HHS+OPM+OFAC =
-- 90.15% (292,197 records). Aggregate counts only — no party named.
SELECT
  COUNT(*)                                              AS active_exclusion_records,
  COUNT(DISTINCT NULLIF(excluding_agency, ''))          AS excluding_agencies
FROM public.sam_exclusions;

SELECT
  COALESCE(NULLIF(excluding_agency, ''), '(unattributed)') AS agency_code,
  COUNT(*)                                                  AS active_exclusions,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2)        AS share_pct
FROM public.sam_exclusions
GROUP BY 1
ORDER BY active_exclusions DESC;

-- By classification (individuals 79.76% vs firms 5.01%) and exclusion type:
SELECT classification, COUNT(*) AS records,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS share_pct
FROM public.sam_exclusions GROUP BY 1 ORDER BY 2 DESC;

SELECT exclusion_type, COUNT(*) AS records,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS share_pct
FROM public.sam_exclusions GROUP BY 1 ORDER BY 2 DESC;

-- Date-coverage gap: only 48.3% (156,544 / 324,126) carry a parseable activation date.
SELECT
  COUNT(*)                                          AS active_exclusion_records,
  COUNT(*) FILTER (WHERE exclusion_date IS NOT NULL) AS records_with_activation_date
FROM public.sam_exclusions;

-- ── B. Awards signed during an active exclusion window (Leakage Report) ─────
-- Headline (committed snapshot): 112 distinct awards · $1,601,819.41 · 21
-- entities. The full join (confirmed UEI / CAGE key, activation_date <=
-- action_date < termination_date) is in src/lib/procurement/leakage.sql. The
-- DISTINCT-award headline:
WITH leakage AS (
  SELECT a.award_unique_key, a.obligation_amount,
         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.obligation_amount,
         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)
)
SELECT
  COUNT(DISTINCT award_unique_key) AS total_leakage_awards,
  COUNT(DISTINCT recipient_key)    AS total_entities,
  (SELECT COALESCE(SUM(d.o), 0) FROM (
      SELECT award_unique_key, MAX(obligation_amount) AS o
      FROM leakage GROUP BY award_unique_key, recipient_key) d) AS total_obligated
FROM leakage;

-- ── EXPECTED RESULTS (committed snapshots, generated 2026-06-20) ────────────
--   Scorecard: active_exclusion_records = 324126 · excluding_agencies = 56
--              HHS 137396 (42.39%) · OPM 80948 (24.97%) · OFAC 73853 (22.79%)
--              top-3 = 292197 (90.15%) · Individual 79.76% · Firm 5.01%
--              records_with_activation_date = 156544 (48.3%)
--   Leakage:   total_leakage_awards = 112 · total_entities = 21
--              total_obligated = 1601819.41
-- The leakage counts grow as the targeted USASpending award backfill for the
-- confirmed-excluded recipient set expands; the roundup reflects the committed
-- snapshot so it stays consistent with the linked study page.
