-- ============================================================================
-- Federal Suspension & Debarment Scorecard — reproducible SQL sidecar
-- Procurement /gov silo · count-based scorecard
--
-- Every figure on /gov/research/federal-debarment-scorecard is re-derivable
-- directly in Postgres from these queries against the SAM.gov Exclusions extract
-- held in the `fonteum-platform` Supabase project (public.sam_exclusions).
--
-- SCOPE: SAM.gov publishes the CURRENTLY-ACTIVE federal exclusion registry —
-- terminated exclusions age off the public file, so every row here has
-- status = 'Active'. This scorecard counts that active registry, aggregated by
-- excluding agency, exclusion type, classification, and activation year. It is a
-- COUNT-based scorecard only: no dollar value is attributed to any agency or
-- record (per-dollar-obligated is a future enhancement once the federal award
-- backfill completes).
--
-- DEFAMATION-SAFE: every query below is an aggregate GROUP BY. No individual,
-- business, UEI, or excluded party is selected or named. Confirm any specific
-- entity's current status at https://sam.gov/content/exclusions before acting.
--
-- Expected results are the committed snapshot
-- (src/lib/procurement/debarment-scorecard.json), queried 2026-06-20.
-- ============================================================================

-- ── 1. Totals (the denominators) ────────────────────────────────────────────
SELECT
  COUNT(*)                                                              AS active_exclusion_records,
  COUNT(DISTINCT excluding_agency)                                     AS excluding_agencies,
  COUNT(DISTINCT COALESCE(excluding_agency, '(unattributed)'))         AS agency_buckets,
  COUNT(*) FILTER (WHERE exclusion_date IS NOT NULL)                   AS records_with_activation_date,
  COUNT(*) FILTER (WHERE exclusion_date IS NULL)                       AS records_without_activation_date,
  COUNT(*) FILTER (WHERE termination_date IS NULL)                     AS indefinite_no_scheduled_termination,
  COUNT(*) FILTER (WHERE termination_date > CURRENT_DATE)              AS scheduled_termination_future_dated
FROM public.sam_exclusions;
-- EXPECTED: active_exclusion_records = 324126; excluding_agencies = 56;
--           agency_buckets = 57 (56 named + 1 '(unattributed)' null bucket);
--           records_with_activation_date = 156544;
--           records_without_activation_date = 167582;
--           indefinite_no_scheduled_termination = 314916;
--           scheduled_termination_future_dated = 9193

-- ── 2. THE SCORECARD — active exclusions by excluding agency (count + share) ──
WITH t AS (SELECT COUNT(*)::numeric AS tot FROM public.sam_exclusions)
SELECT
  COALESCE(excluding_agency, '(unattributed)')          AS agency_code,
  COUNT(*)                                              AS records,
  ROUND(100.0 * COUNT(*) / (SELECT tot FROM t), 2)      AS share_pct
FROM public.sam_exclusions
GROUP BY excluding_agency
ORDER BY records DESC;
-- EXPECTED (top of the ranked table):
--   HHS  137396  42.39
--   OPM   80948  24.97
--   OFAC  73853  22.79
--   DOJ    6490   2.00
--   HUD    4464   1.38
--   EPA    4375   1.35
-- Three agencies (HHS + OPM + OFAC) = 292,197 records = 90.15% of the registry.

-- ── 3. By exclusion type / program ──────────────────────────────────────────
WITH t AS (SELECT COUNT(*)::numeric AS tot FROM public.sam_exclusions)
SELECT
  COALESCE(NULLIF(TRIM(exclusion_type), ''), '(unspecified)') AS exclusion_type,
  COUNT(*)                                                    AS records,
  ROUND(100.0 * COUNT(*) / (SELECT tot FROM t), 2)            AS share_pct
FROM public.sam_exclusions
GROUP BY 1
ORDER BY records DESC;
-- EXPECTED:
--   Prohibition/Restriction              258252  79.68
--   Ineligible (Proceedings Completed)    58978  18.20
--   Ineligible (Proceedings Pending)       5304   1.64
--   Ineligible (Proceedings Complete)      1181   0.36
--   Voluntary Exclusion                     411   0.13

-- ── 4. By classification ────────────────────────────────────────────────────
WITH t AS (SELECT COUNT(*)::numeric AS tot FROM public.sam_exclusions)
SELECT
  classification,
  COUNT(*)                                              AS records,
  ROUND(100.0 * COUNT(*) / (SELECT tot FROM t), 2)      AS share_pct
FROM public.sam_exclusions
GROUP BY classification
ORDER BY records DESC;
-- EXPECTED:
--   Individual                  258536  79.76
--   Special Entity Designation   46866  14.46
--   Firm                         16243   5.01
--   Vessel                        2481   0.77

-- ── 5. By activation year (trend among dated records) ───────────────────────
-- COVERAGE NOTE: only 156,544 of 324,126 records (48.3%) carry a parseable
-- activation (exclusion) date in this extract; the remaining 167,582 — including
-- much of the sanctions-derived OFAC set — have no published activation date and
-- are EXCLUDED from this trend. 237 dated rows predate 1989 and 2 are future-dated;
-- both are out of the displayed 1989–present window.
SELECT
  EXTRACT(YEAR FROM exclusion_date)::int  AS year,
  COUNT(*)                                AS records
FROM public.sam_exclusions
WHERE exclusion_date IS NOT NULL
  AND exclusion_date BETWEEN '1989-01-01' AND CURRENT_DATE
GROUP BY 1
ORDER BY year;
-- EXPECTED (recent surge, dated records only):
--   2021  4026
--   2022  6948
--   2023  9470
--   2024 10125
--   2025  6770
--   2026  1799  (partial year — additions ongoing)
