-- OFAC SDN Composition 2026 (ofac-sdn-composition/v1)
-- ---------------------------------------------------------------------------
-- Reproducible aggregation over the signed OFAC SDN + Consolidated source table
-- in the fonteum-platform Supabase project:
--   public.ofac_sdn_listings - snapshot as of 2026-06-28
--
-- Every query filters to the CURRENT bitemporal slice (valid_to IS NULL), so the
-- counts reflect the current published list, not superseded rows.
--
-- AGGREGATE-ONLY: counts of LISTINGS, never names. Program counts are
-- listing-program pairs: a listing can carry more than one program code, so
-- those counts can exceed the list universe.

WITH current_sdn AS (
  SELECT *
  FROM public.ofac_sdn_listings
  WHERE valid_to IS NULL
)
SELECT count(*) AS current_sdn_listings
FROM current_sdn;
-- 20,051

WITH current_sdn AS (
  SELECT *
  FROM public.ofac_sdn_listings
  WHERE valid_to IS NULL
)
SELECT initcap(coalesce(nullif(party_type, ''), 'other')) AS party_type,
       count(*) AS listings
FROM current_sdn
GROUP BY 1
ORDER BY listings DESC;
-- Entity 10,533; Individual 7,665; Vessel 1,499; Aircraft 344.

WITH current_sdn AS (
  SELECT *
  FROM public.ofac_sdn_listings
  WHERE valid_to IS NULL
)
SELECT prog AS program_code,
       count(*) AS listing_program_pairs
FROM current_sdn
CROSS JOIN LATERAL unnest(programs) AS prog
GROUP BY prog
ORDER BY listing_program_pairs DESC
LIMIT 10;
-- RUSSIA-EO14024 6,567; SDGT 3,187; IFSR 1,532; SDNTK 1,400;
-- NPWMD 1,167; UKRAINE-EO13662 1,108; IRAN-EO13902 805; GLOMAG 740;
-- IRAN 674; ILLICIT-DRUGS-EO14059 651.

WITH current_sdn AS (
  SELECT *
  FROM public.ofac_sdn_listings
  WHERE valid_to IS NULL
)
SELECT extract(year FROM listing_date)::int AS year_added,
       count(*) AS listings
FROM current_sdn
WHERE listing_date IS NOT NULL
GROUP BY 1
ORDER BY listings DESC;
-- 2024 3,292; 2023 2,613; 2022 2,468; 2025 2,189; 2021 846;
-- 2020 721; 2019 726; 2018 1,197; 2017 585; 2016 663; 2026 775.

WITH current_sdn AS (
  SELECT *
  FROM public.ofac_sdn_listings
  WHERE valid_to IS NULL
)
SELECT count(*) AS iran_related_listing_program_pairs
FROM current_sdn
CROSS JOIN LATERAL unnest(programs) AS prog
WHERE prog = ANY (ARRAY['IFSR','IRAN','IRAN-EO13902','IRAN-EO13846','IRGC','IRAN-HR']);
-- Iran-related cluster query. The committed page reports the cluster direction
-- from the supplied aggregate and itemizes only top-ten counts.
