-- Sanctioned Across Borders — cross-list overlap on EXACT shared identifiers only.
-- Warehouse: fonteum-platform. Source tables (current bitemporal slice each):
--   public.oig_leie_exclusions    -- OIG LEIE        (release 2026-05-08)
--   public.sam_exclusions         -- SAM.gov         (snapshot 2026-06-18)
--   public.state_exclusions       -- state Medicaid  (snapshot 2026-06-15, 13 states)
--   public.eu_sanctions_listings  -- EU FSF          (snapshot 2026-06-05)
--   public.un_sanctions_listings  -- UN Consolidated (snapshot 2026-06-20)
--
-- The U.S. overlap is keyed on the 10-digit National Provider Identifier (NPI),
-- the only machine key the three U.S. exclusion registries share. NPIs are
-- normalized to a valid 10-digit form (leading 1-9). No fuzzy name matching is
-- used, and no party is named — these are GROUP BY counts only.

-- The valid-NPI set on each registry (distinct).
with leie as (
  select distinct npi::text n from public.oig_leie_exclusions
  where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'
),
sam as (
  select distinct npi::text n from public.sam_exclusions
  where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'
),
st as (
  select distinct npi::text n from public.state_exclusions
  where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'
)

-- (1) Per-registry distinct NPIs, the three pairwise intersections, and the
--     all-three intersection (the pairwise overlap matrix).
select
  (select count(*) from leie)                                   as leie_npi,    -- 6,880
  (select count(*) from sam)                                    as sam_npi,     -- 4,692
  (select count(*) from st)                                     as state_npi,   -- 5,224
  (select count(*) from leie l join sam s on l.n = s.n)         as leie_sam,    -- 3,747
  (select count(*) from leie l join st  t on l.n = t.n)         as leie_state,  -- 1,763
  (select count(*) from sam  s join st  t on s.n = t.n)         as sam_state,   -- 1,101
  (select count(*) from leie l join sam s on l.n = s.n
                        join st  t on l.n = t.n)                as all_three;   --   908

-- (2) Membership distribution — how many distinct NPIs sit on exactly one, two,
--     or three registries (and the 2+ share of the union). Re-declare the CTEs.
with leie as (select distinct npi::text n from public.oig_leie_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
sam  as (select distinct npi::text n from public.sam_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
st   as (select distinct npi::text n from public.state_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
allnpi as (
  select n,
    (case when n in (select n from leie) then 1 else 0 end)
  + (case when n in (select n from sam)  then 1 else 0 end)
  + (case when n in (select n from st)   then 1 else 0 end) as lists
  from (select n from leie union select n from sam union select n from st) u
)
select
  count(*)                                          as total_distinct_npi, -- 11,093
  count(*) filter (where lists = 1)                 as on_one,             --  6,298
  count(*) filter (where lists = 2)                 as on_two,             --  3,887
  count(*) filter (where lists = 3)                 as on_three,           --    908
  count(*) filter (where lists >= 2)                as on_two_plus,        --  4,795
  round(100.0 * count(*) filter (where lists >= 2)
        / count(*), 1)                              as on_two_plus_pct     --   43.2
from allnpi;

-- (3) Top overlap categories — the OIG LEIE statutory exclusion type carried by
--     the overlapping (2+) providers that appear on the LEIE registry.
with leie as (select distinct npi::text n from public.oig_leie_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
sam  as (select distinct npi::text n from public.sam_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
st   as (select distinct npi::text n from public.state_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
two_plus as (
  select n from (select n from leie union all select n from sam union all select n from st) u
  group by n having count(*) >= 2
)
select coalesce(nullif(l.exclusion_type,''),'(unspecified)') as exclusion_type,
       count(distinct l.npi::text)                           as providers
from public.oig_leie_exclusions l
join two_plus tp on tp.n = l.npi::text
where l.npi is not null and l.npi::text ~ '^[1-9][0-9]{9}$'
group by 1 order by providers desc;
--  1128a1 = 1,788 (program-related conviction) / 1128b4 = 1,453 (license action) ...

-- (4) Overlap geography — the LEIE-recorded state of the overlapping providers.
with leie as (select distinct npi::text n from public.oig_leie_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
sam  as (select distinct npi::text n from public.sam_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
st   as (select distinct npi::text n from public.state_exclusions
              where npi is not null and npi::text ~ '^[1-9][0-9]{9}$'),
two_plus as (
  select n from (select n from leie union all select n from sam union all select n from st) u
  group by n having count(*) >= 2
)
select coalesce(nullif(l.state,''),'(none)') as state,
       count(distinct l.npi::text)           as providers
from public.oig_leie_exclusions l
join two_plus tp on tp.n = l.npi::text
where l.npi is not null and l.npi::text ~ '^[1-9][0-9]{9}$'
group by 1 order by providers desc;
--  CA 574 / NY 503 / OH 412 / FL 352 / PA 299 ...

-- (5) International cross-reference — EU designations carrying the UN's OWN
--     published reference number, and how many resolve to an active UN listing.
select
  count(*) filter (where valid_to is null)                        as eu_active,      -- 5,994
  count(*) filter (where valid_to is null
                     and un_id is not null and un_id <> '')        as eu_with_un_ref -- 87
from public.eu_sanctions_listings;

select count(distinct e.id) filter
        (where u.un_data_id is not null)                          as eu_resolves_to_un -- 84
from public.eu_sanctions_listings e
left join public.un_sanctions_listings u
  on u.valid_to is null and (u.un_data_id = e.un_id or u.reference_number = e.un_id)
where e.valid_to is null and e.un_id is not null and e.un_id <> '';
