-- EPA's Top Violators 2026 — re-derivation SQL
-- Run against: fonteum-platform (public schema)
-- Source: EPA ECHO Exporter facility file (echo.epa.gov) — snapshot 2026-06-25
-- Methodology: epa-top-violators/v1
-- All figures are aggregate counts/distributions only; no individual is named.
-- Facility names in query 8 are public EPA ECHO regulatory records.
--
-- NOTE: public.epa_echo_exporter_facilities holds ~3.14M rows (≈7 GB). The
-- universe aggregate (query 1) is a full-table scan; raise statement_timeout
-- (SET statement_timeout = '120s') or aggregate over primary-key id ranges and
-- sum the parts. Every other query below filters to an indexed subset
-- (formal_action_count_5yr > 0) and returns in well under a second.

-- 1. Universe (full-table aggregate)
SELECT
  COUNT(*)                                                              AS total_facilities,
  COUNT(*) FILTER (WHERE facility_active)                              AS active_facilities,
  COUNT(*) FILTER (WHERE inspection_count_5yr > 0)                     AS inspected_5yr,
  COUNT(*) FILTER (WHERE formal_action_count_5yr > 0)                  AS with_formal_action_5yr,
  COUNT(*) FILTER (WHERE informal_action_count_5yr > 0)               AS with_informal_action_5yr,
  COUNT(*) FILTER (WHERE total_penalties_5yr > 0)                      AS with_penalty_5yr,
  ROUND(COALESCE(SUM(total_penalties_5yr), 0))                         AS total_penalties_5yr,
  COUNT(*) FILTER (WHERE compliance_status = 'Significant Violation')  AS significant_violation,
  COUNT(*) FILTER (WHERE compliance_status IN ('Violation Identified','Violation')) AS violation_identified
FROM public.epa_echo_exporter_facilities;
-- expected: 3,135,553 facilities | 44,112 with a formal action | $10,907,284,601 total penalties

-- 2. Distribution by current compliance status (full-table aggregate)
SELECT
  COALESCE(compliance_status, 'Not classified / blank') AS status,
  COUNT(*) AS facilities,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct
FROM public.epa_echo_exporter_facilities
GROUP BY 1
ORDER BY 2 DESC;

-- 3. Top states by 5-year assessed penalties (US states/territories only;
--    served by the formal_action_count_5yr > 0 partial index)
SELECT
  state,
  COUNT(*)                                            AS facilities_formal,
  COUNT(*) FILTER (WHERE total_penalties_5yr > 0)     AS facilities_penalty,
  ROUND(COALESCE(SUM(total_penalties_5yr), 0))        AS penalties_5yr
FROM public.epa_echo_exporter_facilities
WHERE formal_action_count_5yr > 0
  AND state IN ('AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL',
                'IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT',
                'NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI',
                'SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY','DC','PR',
                'VI','GU','AS','MP')
GROUP BY state
ORDER BY penalties_5yr DESC
LIMIT 15;

-- 4. Facilities + attributed penalties by EPA media program (the "sector" axis;
--    NAICS/SIC is not carried in the ECHO Exporter file). program_metrics is the
--    per-program JSON rollup; penalties summed where the program attributes one.
SELECT
  'CAA' AS code,
  COUNT(*) FILTER (WHERE (program_metrics->'caa'->>'formal_action_count_5yr')::int > 0)  AS facilities_formal,
  ROUND(COALESCE(SUM((program_metrics->'caa'->>'total_penalties_5yr')::numeric), 0))     AS penalties_5yr
FROM public.epa_echo_exporter_facilities WHERE formal_action_count_5yr > 0
UNION ALL SELECT 'CWA',
  COUNT(*) FILTER (WHERE (program_metrics->'cwa'->>'formal_action_count_5yr')::int > 0),
  ROUND(COALESCE(SUM((program_metrics->'cwa'->>'total_penalties_5yr')::numeric), 0))
FROM public.epa_echo_exporter_facilities WHERE formal_action_count_5yr > 0
UNION ALL SELECT 'RCRA',
  COUNT(*) FILTER (WHERE (program_metrics->'rcra'->>'formal_action_count_5yr')::int > 0),
  ROUND(COALESCE(SUM((program_metrics->'rcra'->>'total_penalties_5yr')::numeric), 0))
FROM public.epa_echo_exporter_facilities WHERE formal_action_count_5yr > 0
UNION ALL SELECT 'SDWA',
  COUNT(*) FILTER (WHERE (program_metrics->'sdwa'->>'formal_action_count_5yr')::int > 0),
  ROUND(COALESCE(SUM((program_metrics->'sdwa'->>'total_penalties_5yr')::numeric), 0))
FROM public.epa_echo_exporter_facilities WHERE formal_action_count_5yr > 0;
-- expected: CAA 7,266 / $2.456B · CWA 12,738 / $487M · RCRA 4,918 / $179M · SDWA 10,273 / $0

-- 5. Facilities by year of most recent formal enforcement action
SELECT
  EXTRACT(YEAR FROM last_formal_action_date)::int AS year,
  COUNT(*) AS facilities,
  ROUND(COALESCE(SUM(total_penalties_5yr), 0)) AS penalties_5yr
FROM public.epa_echo_exporter_facilities
WHERE formal_action_count_5yr > 0 AND last_formal_action_date IS NOT NULL
GROUP BY 1
ORDER BY 1 DESC
LIMIT 6;

-- 6. Penalty concentration among the highest-penalty facilities
WITH ranked AS (
  SELECT total_penalties_5yr,
         ROW_NUMBER() OVER (ORDER BY total_penalties_5yr DESC NULLS LAST) AS rn
  FROM public.epa_echo_exporter_facilities
  WHERE formal_action_count_5yr > 0 AND total_penalties_5yr > 0
)
SELECT
  COUNT(*)                                                    AS penalized_facilities,
  ROUND(SUM(total_penalties_5yr))                             AS total_penalties_5yr,
  ROUND(MAX(total_penalties_5yr))                             AS largest_single,
  ROUND(SUM(total_penalties_5yr) FILTER (WHERE rn <= 10))     AS top10,
  ROUND(SUM(total_penalties_5yr) FILTER (WHERE rn <= 50))     AS top50,
  ROUND(SUM(total_penalties_5yr) FILTER (WHERE rn <= 100))    AS top100,
  ROUND(SUM(total_penalties_5yr) FILTER (WHERE rn <= 500))    AS top500
FROM ranked;
-- expected: 21,125 penalized facilities | top 100 = $4.293B (39.4% of $10.907B)

-- 7. Top facilities by 5-year assessed penalties (public EPA ECHO records).
--    Names are the published facility/company records; no Fonteum score applied.
SELECT facility_name, city, state, ROUND(total_penalties_5yr) AS penalties_5yr,
       formal_action_count_5yr
FROM public.epa_echo_exporter_facilities
WHERE formal_action_count_5yr > 0 AND total_penalties_5yr > 0
ORDER BY total_penalties_5yr DESC NULLS LAST
LIMIT 20;
