-- Medicaid Exclusion List Blind Spots: State Bars the Feds Miss
-- Fonteum reproducible query — https://fonteum.com/research/state-exclusion-federal-blind-spot-2026
-- Data as of: 2026-07-14  ·  methodology: state-blind-spot/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).

-- Excluded by a state, invisible to a federal check — fully reproducible query.
--
-- Question: of the providers (by NPI) carrying an ACTIVE state Medicaid
-- exclusion across the 13 state programs Fonteum currently ingests, how many
-- are NOT present on the federal OIG LEIE — i.e. how many a federal-only
-- screen would miss? And, layering the second federal list, how many are on
-- neither the OIG LEIE nor the SAM.gov / GSA debarment registry? Plus the
-- cross-state overlap: how many excluded NPIs appear on 2+ state lists.
--
-- This study extends the prior LEIE-only cut (federal-state-exclusion-gap-2026,
-- 10 states) two ways: (1) it tests the FULL federal screening stack — OIG LEIE
-- AND SAM.gov debarment — not a single list; (2) it adds the cross-state
-- dimension. Coverage is now 13 states (NY OH GA PA NC MD WA IA MS MT NH ND TN).
--
-- Sources (all public, read-only):
--   public.state_exclusions       — State Exclusion Ring, 13 state Medicaid
--                                    programs, 22,917 rows (5,922 carry an NPI;
--                                    16,995 — 74.2% — carry none). Data as of
--                                    observed 2026-07-14.
--   public.oig_leie_exclusions    — OIG List of Excluded Individuals and
--                                    Entities, federal monthly bulk download,
--                                    release 2026-05-08, 68,055 active records
--                                    (7,025 carry an NPI).
--   public.sam_exclusions         — SAM.gov / GSA federal exclusion & debarment
--                                    registry, 323,930 active and unterminated
--                                    rows (37,443 carry an NPI; 4,694 distinct
--                                    NPIs) as observed 2026-07-14.
--
-- Join key: NPI only (10-digit, btrim). We NEVER match on name — a name match
-- is not a defensible identity assertion, so rows with no NPI are excluded from
-- the matchable denominator and reported separately (see the no-NPI query).
--
-- "In force" mirrors the production exclusion lookup (src/lib/exclusions):
--   state / LEIE : reinstatement_date IS NULL OR reinstatement_date > today
--   SAM          : termination_date  IS NULL OR termination_date  > today
-- (isCurrentlyExcluded in src/lib/exclusions/types.ts). The OIG/state files drop
-- reinstated parties, so the in-force set is "any row by NPI" net of future-dated
-- reinstatements. Audit date basis: DATE '2026-07-14'.
--
-- Every headline figure in the study resolves to one of the rows below.

-- ── HEADLINE: state-excluded NPIs invisible to a federal check ──────────────
-- audit-claim: federal-blind-spot
WITH state_inforce AS (             -- distinct in-force, NPI-identified state Medicaid exclusions
  SELECT DISTINCT nullif(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')
),
leie_inforce AS (                   -- distinct in-force, NPI-identified federal OIG exclusions
  SELECT DISTINCT nullif(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_inforce AS (                    -- distinct in-force, NPI-identified SAM.gov / GSA debarments
  SELECT DISTINCT nullif(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')
), counts AS (
  SELECT
    (SELECT count(*) FROM state_inforce)                                         AS state_inforce_npi,
    (SELECT count(*) FROM state_inforce s
       LEFT JOIN leie_inforce l USING (npi) WHERE l.npi IS NULL)                 AS not_on_leie,
    (SELECT count(*) FROM state_inforce s
       JOIN leie_inforce l USING (npi))                                         AS also_on_leie,
    (SELECT count(*) FROM state_inforce s
       LEFT JOIN leie_inforce l USING (npi)
       LEFT JOIN sam_inforce sa USING (npi)
       WHERE l.npi IS NULL AND sa.npi IS NULL)                                  AS not_on_leie_nor_sam,
    (SELECT count(*) FROM leie_inforce)                                         AS leie_inforce_npi,
    (SELECT count(*) FROM sam_inforce)                                          AS sam_inforce_npi
)
SELECT
  counts.*,
  round(100.0 * not_on_leie / state_inforce_npi, 1)                              AS not_on_leie_pct,
  not_on_leie - not_on_leie_nor_sam                                             AS recovered_by_sam,
  round(100.0 * not_on_leie_nor_sam / state_inforce_npi, 1)                     AS not_on_both_pct,
  round(100.0 * not_on_leie / state_inforce_npi, 1)
    - round(100.0 * not_on_leie_nor_sam / state_inforce_npi, 1)                  AS recovered_percentage_points,
  round(
    100.0 * (not_on_leie - not_on_leie_nor_sam) / not_on_leie,
    1
  )                                                                              AS blind_spot_closed_pct
FROM counts;
--  state_inforce_npi  not_on_leie  also_on_leie  not_on_leie_nor_sam  leie_inforce_npi  sam_inforce_npi
--       4,949            3,188        1,761            2,997               6,880            4,694
--  => 3,188 / 4,949 = 64.4% of state-excluded NPIs carry NO in-force OIG LEIE record (federal-LEIE-only blind spot).
--  => Adding the SAM.gov debarment list recovers only 3,188 - 2,997 = 191 of them; 2,997 (60.6%) remain
--     invisible to BOTH federal lists. Difference 3.8 percentage points; 6.0% of the gap closed.

-- ── Cross-state overlap: NPIs barred across 2+ of the 13 state lists ─────────
-- audit-claim: cross-state-summary
WITH npi_states AS (
  SELECT nullif(btrim(npi), '') AS npi, count(DISTINCT state) AS n_states
  FROM public.state_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')
  GROUP BY 1
)
SELECT
  count(*)                              AS distinct_npi,      -- 4,949
  count(*) FILTER (WHERE n_states >= 2) AS on_2plus_states,   -- 198
  count(*) FILTER (WHERE n_states = 1)  AS on_1_state,        -- 4,751
  max(n_states)                         AS max_states         -- 7
FROM npi_states;

-- Distribution of states-per-NPI (how many state lists a single NPI sits on):
-- audit-claim: cross-state-distribution
WITH npi_states AS (
  SELECT nullif(btrim(npi), '') AS npi, count(DISTINCT state) AS n_states
  FROM public.state_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')
  GROUP BY 1
)
SELECT n_states, count(*) AS npis FROM npi_states GROUP BY n_states ORDER BY n_states;
--  1 state: 4,751 · 2 states: 186 · 3 states: 11 · 7 states: 1   (198 NPIs on 2+ states)

-- Of the multi-state (2+) NPIs, how many are STILL invisible to the federal LEIE:
-- audit-claim: multi-state-federal-gap
WITH npi_states AS (
  SELECT nullif(btrim(npi), '') AS npi, count(DISTINCT state) AS n_states
  FROM public.state_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14')
  GROUP BY 1
),
leie_inforce AS (
  SELECT DISTINCT nullif(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')
),
multi AS (SELECT npi FROM npi_states WHERE n_states >= 2)
SELECT
  (SELECT count(*) FROM multi)                                              AS multi_state_npi,         -- 198
  (SELECT count(*) FROM multi m LEFT JOIN leie_inforce l USING (npi)
     WHERE l.npi IS NULL)                                                   AS multi_state_not_on_leie; -- 82
--  82 of the 198 providers barred in 2+ states carry no OIG LEIE record — barred coast-to-coast,
--  yet a federal-only screen returns them clean.

-- ── Per-state contribution (in-force distinct NPI, and how many not on LEIE) ─
-- audit-claim: state-vector
WITH state_inforce AS (
  SELECT DISTINCT state, nullif(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')
),
leie_inforce AS (
  SELECT DISTINCT nullif(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')
),
flagged AS (
  SELECT s.state, s.npi, (l.npi IS NULL) AS not_on_leie
  FROM state_inforce s LEFT JOIN leie_inforce l USING (npi)
)
SELECT state,
       count(DISTINCT npi)                            AS excluded_npi,
       count(DISTINCT npi) FILTER (WHERE not_on_leie) AS not_on_leie
FROM flagged GROUP BY state ORDER BY excluded_npi DESC;
--  NY 2,259 (1,352 not on LEIE) · PA 973 (568) · OH 673 (363) · IA 313 (263) · MD 264 (222)
--  · WA 210 (178) · NC 149 (128) · MS 138 (106) · NH 51 (37) · ND 47 (28) · MT 42 (16)
--  · GA 35 (8) · TN 9 (8).  Sum of per-state distinct NPI = 5,163 state-NPI pairs; distinct
--  across states = 4,949 (the 198 multi-state NPIs are counted once per state here).

-- ── Coverage + the unmatchable majority (rows with no NPI) ──────────────────
-- audit-claim: raw-state-universe
SELECT
  count(*)                                                                    AS total_rows,       -- 22,917
  count(*) FILTER (WHERE reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14') AS inforce_rows, -- 21,003
  count(*) FILTER (WHERE nullif(btrim(npi), '') IS NOT NULL)                   AS rows_with_npi,    -- 5,922
  count(*) FILTER (WHERE nullif(btrim(npi), '') IS NULL)                       AS rows_no_npi,      -- 16,995
  round(100.0 * count(*) FILTER (WHERE nullif(btrim(npi), '') IS NULL) / count(*), 1) AS pct_no_npi, -- 74.2
  count(DISTINCT nullif(btrim(npi), ''))                                       AS distinct_npi,     -- 5,224 (incl. reinstated)
  count(DISTINCT state)                                                        AS states           -- 13
FROM public.state_exclusions;
--  74.2% of state exclusion rows carry no NPI at all. They cannot be matched to a federal
--  list by identifier and are excluded from the matchable denominator, never name-matched.

-- Full federal source universes, separate from the distinct-NPI join sets.
-- audit-claim: federal-source-universes
SELECT
  (SELECT count(*) FROM public.oig_leie_exclusions) AS leie_rows,
  (SELECT count(*) FROM public.oig_leie_exclusions
    WHERE nullif(btrim(npi), '') IS NOT NULL) AS leie_rows_with_npi,
  (SELECT count(*) FROM public.sam_exclusions
    WHERE status = 'Active'
      AND (termination_date IS NULL OR termination_date > DATE '2026-07-14')) AS sam_active_rows,
  (SELECT count(*) FROM public.sam_exclusions
    WHERE status = 'Active'
      AND (termination_date IS NULL OR termination_date > DATE '2026-07-14')
      AND nullif(btrim(npi), '') IS NOT NULL) AS sam_active_rows_with_npi,
  (SELECT count(DISTINCT btrim(npi)) FROM public.sam_exclusions
    WHERE status = 'Active'
      AND (termination_date IS NULL OR termination_date > DATE '2026-07-14')
      AND nullif(btrim(npi), '') IS NOT NULL) AS sam_active_distinct_npi;
-- LEIE 68,055 rows / 7,025 with NPI
-- SAM 323,930 active rows / 37,443 with NPI / 4,694 distinct NPI
