-- Composition & Growth of the US + EU Sanctions Universe (sanctions-composition/v1)
-- ---------------------------------------------------------------------------------
-- Reproducible aggregation over two signed, daily source tables in the
-- fonteum-platform Supabase project:
--   public.ofac_sdn_listings     — OFAC SDN + Consolidated lists (snapshot 2026-06-18)
--   public.eu_sanctions_listings — EU Consolidated Financial Sanctions / FSF (snapshot 2026-06-05)
--
-- Every query filters to the CURRENT bitemporal slice (valid_to IS NULL) so the
-- counts reflect the live published lists, not superseded rows. Each figure below
-- matches the committed snapshot at src/lib/sanctions/sanctions-composition.json.
--
-- AGGREGATE-ONLY: counts of LISTINGS, never deduplicated identities. No party is
-- named. No cross-list identity match is asserted (composite-key discipline): a
-- party may appear on both lists and on more than one OFAC sub-list.

-- 1. Headline totals -------------------------------------------------------------
WITH o AS (SELECT * FROM public.ofac_sdn_listings   WHERE valid_to IS NULL),
     e AS (SELECT * FROM public.eu_sanctions_listings WHERE valid_to IS NULL)
SELECT
  (SELECT count(*) FROM o)                                                          AS ofac_listings,             -- 19960
  (SELECT count(DISTINCT fixed_ref) FROM o)                                         AS ofac_distinct_primary,     -- 19427
  (SELECT count(*) FROM o WHERE listing_date >= DATE '2022-01-01')                  AS ofac_since_2022,           -- 11256
  (SELECT count(*) FROM e)                                                          AS eu_listings,               -- 5994
  (SELECT count(*) FROM e WHERE designation_date >= DATE '2022-01-01')              AS eu_since_2022,             -- 3916
  (SELECT count(*) FROM o) + (SELECT count(*) FROM e)                               AS combined_listings,         -- 25954
  (SELECT count(*) FROM o WHERE listing_date >= DATE '2022-01-01')
    + (SELECT count(*) FROM e WHERE designation_date >= DATE '2022-01-01')          AS combined_since_2022;       -- 15172
-- combined_since_2022 / combined_listings = 58.46%
-- ofac_since_2022 / ofac_listings         = 56.39%
-- eu_since_2022 / eu_listings             = 65.33%

-- 2. Party-type mix --------------------------------------------------------------
SELECT 'OFAC' AS list, party_type, count(*) AS records
FROM public.ofac_sdn_listings WHERE valid_to IS NULL
GROUP BY party_type ORDER BY records DESC;
--  entity 10475 · individual 7642 · vessel 1499 · aircraft 344
SELECT 'EU' AS list, party_type, count(*) AS records
FROM public.eu_sanctions_listings WHERE valid_to IS NULL
GROUP BY party_type ORDER BY records DESC;
--  person 4405 · entity 1589
-- Physical assets named only by OFAC: vessels 1499 + aircraft 344 = 1843.

-- 3. Designations by year (the post-2022 surge) ----------------------------------
SELECT extract(year FROM listing_date)::int AS year, count(*) AS ofac
FROM public.ofac_sdn_listings WHERE valid_to IS NULL AND listing_date IS NOT NULL
GROUP BY 1 ORDER BY 1;
-- 2022 = 2468 · 2023 = 2613 · 2024 = 3292 (busiest) · 2025 = 2189 · 2026 = 694 (YTD)
SELECT extract(year FROM designation_date)::int AS year, count(*) AS eu
FROM public.eu_sanctions_listings WHERE valid_to IS NULL AND designation_date IS NOT NULL
GROUP BY 1 ORDER BY 1;
-- 2022 = 1508 (busiest) · 2023 = 764 · 2024 = 697 · 2025 = 720 · 2026 = 227 (YTD)

-- 4. Top OFAC programs (a listing may carry more than one program) ----------------
SELECT prog AS program, count(*) AS records
FROM public.ofac_sdn_listings, unnest(programs) AS prog
WHERE valid_to IS NULL
GROUP BY prog ORDER BY records DESC LIMIT 10;
-- RUSSIA-EO14024 6567 · SDGT 3160 · IFSR 1532 · SDNTK 1400 · NPWMD 1167 · ...

-- 5. Top EU regimes --------------------------------------------------------------
SELECT programme, count(*) AS records
FROM public.eu_sanctions_listings WHERE valid_to IS NULL
GROUP BY programme ORDER BY records DESC LIMIT 10;
-- UKR 2722 · IRN 711 · SYR 380 · BLR 372 · TAQA 337 · PRK 255 · ...
