-- OIG Exclusion Check: The 2026 Excluded-Provider Landscape
-- Fonteum reproducible query — https://fonteum.com/research/excluded-providers-landscape-2026
-- Data as of: 2026-07-14  ·  methodology: exclusion-landscape/v1
-- Source datasets: OIG LEIE
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- The excluded-provider landscape — fully reproducible query.
--
-- Question: across every exclusion source Fonteum holds, how many providers
-- are barred somewhere, how does the population split by source, how much do
-- the lists overlap, and how much does any single list miss?
--
-- EXCLUSION layer (the matchable universe — joined on NPI):
--   public.oig_leie_exclusions — OIG List of Excluded Individuals/Entities,
--                                federal monthly bulk download, release
--                                2026-05-08, 68,055 records. Public, read-only.
--   public.sam_exclusions      — SAM.gov / GSA government-wide debarment list,
--                                323,930 active, in-force rows at the audit cutoff.
--   public.state_exclusions    — State Medicaid Exclusion Ring (13 states).
--                                Public, read-only.
--
-- Join key: NPI only (10-digit, btrim). A name match is not a defensible
-- identity assertion, so rows with no NPI are excluded from the matchable
-- universe and reported separately (see no-NPI query below).
--
-- "In force" mirrors the production exclusion lookup (src/lib/exclusions): a row
-- is in force when reinstatement_date IS NULL OR reinstatement_date > today
-- (for SAM.gov, the equivalent lapse signal is termination_date; "Indefinite"
-- stores as NULL and stays in force). Audit cutoff: DATE '2026-07-14'.
--
-- COMPROMISED-BUT-OPERATING layer (reported separately, NEVER NPI-joined —
-- neither source carries an NPI):
--   public.oig_cia_agreements        — OIG Corporate Integrity Agreements
--                                       (entity-name keyed).
--   public.cms_civil_money_penalties — CMS Civil Money Penalties (CCN keyed).
--
-- Every headline figure in the study resolves to one of the rows below.

-- audit-claim: exclusion-union-overlap
WITH leie AS (
  SELECT DISTINCT btrim(npi) AS npi FROM public.oig_leie_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')
),
sam AS (
  SELECT DISTINCT btrim(npi) AS npi FROM public.sam_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND status = 'Active'
    AND (termination_date IS NULL OR termination_date > DATE '2026-07-14')
),
st AS (
  SELECT DISTINCT btrim(npi) AS npi FROM public.state_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')
),
all_npi AS (
  SELECT npi FROM leie UNION SELECT npi FROM sam UNION SELECT npi FROM st
),
flags AS (
  SELECT a.npi,
         (l.npi IS NOT NULL)::int AS in_leie,
         (s.npi IS NOT NULL)::int AS in_sam,
         (t.npi IS NOT NULL)::int AS in_state
  FROM all_npi a
  LEFT JOIN leie  l USING (npi)
  LEFT JOIN sam   s USING (npi)
  LEFT JOIN st    t USING (npi)
),
scored AS (
  SELECT *, (in_leie + in_sam + in_state) AS src_count FROM flags
)
SELECT
  (SELECT count(*) FROM all_npi)                                  AS excluded_anywhere,   -- 10,824
  (SELECT count(*) FROM scored WHERE src_count = 1)               AS on_exactly_one,       --  6,031 (55.7%)
  (SELECT count(*) FROM scored WHERE src_count >= 2)              AS on_two_or_more,       --  4,793 (44.3%)
  (SELECT count(*) FROM scored WHERE src_count = 3)               AS on_all_three,         --    906 ( 8.4%)
  (SELECT count(*) FROM scored WHERE in_leie  = 1)                AS leie_npi,             --  6,880 (63.6%)
  (SELECT count(*) FROM scored WHERE in_sam   = 1)                AS sam_npi,              --  4,694 (43.4%)
  (SELECT count(*) FROM scored WHERE in_state = 1)                AS state_npi,            --  4,949 (45.7%)
  (SELECT count(*) FROM scored WHERE src_count = 1 AND in_leie  = 1) AS leie_only,         --  2,278
  (SELECT count(*) FROM scored WHERE src_count = 1 AND in_sam   = 1) AS sam_only,          --    756
  (SELECT count(*) FROM scored WHERE src_count = 1 AND in_state = 1) AS state_only,        --  2,997
  (SELECT count(*) FROM scored WHERE in_leie = 1 AND in_sam   = 1) AS leie_and_sam,        --  3,747
  (SELECT count(*) FROM scored WHERE in_leie = 1 AND in_state = 1) AS leie_and_state,      --  1,761
  (SELECT count(*) FROM scored WHERE in_sam  = 1 AND in_state = 1) AS sam_and_state;       --  1,097
-- Coverage = source_npi / excluded_anywhere. Single most complete list (LEIE)
-- covers 6,880 / 10,824 = 63.6% → federal-LEIE-only screening misses 3,944 (36.4%).

-- audit-claim: in-force-source-vector
-- The no-NPI rows are excluded from the universe above and reported separately.
SELECT 'oig-leie' AS source,
       count(DISTINCT btrim(npi)) FILTER (WHERE nullif(btrim(npi),'') IS NOT NULL
         AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')) AS inforce_distinct_npi,
       count(*) FILTER (WHERE nullif(btrim(npi),'') IS NULL
         AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')) AS inforce_no_npi
FROM public.oig_leie_exclusions
UNION ALL
SELECT 'sam-gov',
       count(DISTINCT btrim(npi)) FILTER (WHERE nullif(btrim(npi),'') IS NOT NULL
         AND status = 'Active'
         AND (termination_date IS NULL OR termination_date > DATE '2026-07-14')),
       count(*) FILTER (WHERE nullif(btrim(npi),'') IS NULL
         AND status = 'Active'
         AND (termination_date IS NULL OR termination_date > DATE '2026-07-14'))
FROM public.sam_exclusions
UNION ALL
SELECT 'state-medicaid',
       count(DISTINCT btrim(npi)) FILTER (WHERE nullif(btrim(npi),'') IS NOT NULL
         AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')),
       count(*) FILTER (WHERE nullif(btrim(npi),'') IS NULL
         AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14'))
FROM public.state_exclusions;
--  source          inforce_distinct_npi  inforce_no_npi
--  oig-leie               6,880              61,030
--  sam-gov                4,694             286,487
--  state-medicaid         4,949              15,487   (no-NPI records total: 363,004)

-- audit-claim: in-force-state-vector
SELECT source_key, state,
  count(DISTINCT btrim(npi)) FILTER (WHERE nullif(btrim(npi),'') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')) AS inforce_distinct_npi
FROM public.state_exclusions
GROUP BY source_key, state
ORDER BY inforce_distinct_npi DESC;
--  ny-omig NY 2,259 | pa-dhs PA 973 | oh-odm OH 673 | ia-medicaid IA 313 | md-mdh MD 264
--  wa-hca WA 210 | nc-dhhs NC 149 | ms-dom MS 138 | nh-dhhs NH 51 | nd-hhs ND 47
--  mt-dphhs MT 42 | ga-dch GA 35 | tn-tenncare TN 9
--  Sum 5,163 vs distinct 4,949 → 214 providers barred by more than one state.

-- audit-claim: in-force-state-summary
WITH in_force AS (
  SELECT source_key, state, btrim(npi) AS npi
  FROM public.state_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')
), source_state AS (
  SELECT source_key, state, count(DISTINCT npi) AS distinct_npi
  FROM in_force
  GROUP BY source_key, state
), totals AS (
  SELECT
    (SELECT count(DISTINCT npi) FROM in_force) AS distinct_npi,
    sum(distinct_npi) AS source_state_row_sum
  FROM source_state
)
SELECT
  distinct_npi,
  source_state_row_sum,
  source_state_row_sum - distinct_npi AS cross_state_excess
FROM totals;

-- audit-claim: compromised-layer-vector
-- Both sources carry zero NPI (CIA = entity-name keyed; CMP = CCN keyed).
SELECT 'oig-cia' AS source,
       count(*) FILTER (WHERE closed_date IS NULL OR closed_date > DATE '2026-07-14') AS active,
       count(DISTINCT entity_name) AS distinct_entities,
       count(*) FILTER (WHERE nullif(btrim(npi),'') IS NOT NULL) AS with_npi
FROM public.oig_cia_agreements
UNION ALL
SELECT 'cms-cmp',
       count(*) AS active,
       count(DISTINCT ccn) AS distinct_entities,
       count(*) FILTER (WHERE nullif(btrim(npi),'') IS NOT NULL) AS with_npi
FROM public.cms_civil_money_penalties;
--  oig-cia   127 active agreements |   333 entities | 0 with NPI
--  cms-cmp  16,277 penalties       | 6,884 facilities | 0 with NPI

-- Source-universe totals are separate from the NPI-matchable universe. SAM is
-- counted with the same Active + termination-window semantics used above;
-- 167,582 is only the missing-exclusion-date subset, not the SAM universe.
-- audit-claim: raw-source-totals
SELECT 'oig-leie' AS source, count(*) AS records
FROM public.oig_leie_exclusions
UNION ALL
SELECT 'sam-gov-active', count(*) FILTER (
  WHERE status = 'Active'
    AND (termination_date IS NULL OR termination_date > DATE '2026-07-14')
)
FROM public.sam_exclusions
UNION ALL
SELECT 'state-medicaid', count(*)
FROM public.state_exclusions
ORDER BY source;
