-- FDA Enforcement Trends 2026 — re-derivation SQL
-- Run against: fonteum-platform (public schema)
-- Snapshot as-of: 2026-06-14
-- Methodology: fda-enforcement-trends/v1
-- All figures are aggregate counts only; no individual is named.

-- 1. Totals
SELECT
  (SELECT COUNT(*) FROM public.fda_enforcement_reports) AS total_recall_reports,
  (SELECT COUNT(*) FROM public.fda_enforcement_actions) AS total_enforcement_actions,
  (SELECT COUNT(*) FROM public.fda_enforcement_actions WHERE action_type = 'recall') AS recall_actions,
  (SELECT COUNT(*) FROM public.fda_enforcement_actions WHERE action_type = 'warning_letter') AS warning_letter_actions;
-- expected: 56777 | 89553 | 85979 | 3574

-- 2. Recalls by year (2015–2025)
SELECT
  EXTRACT(YEAR FROM recall_initiation_date)::int AS year,
  COUNT(*) AS recalls,
  COUNT(*) FILTER (WHERE classification = 'Class I')   AS class_i,
  COUNT(*) FILTER (WHERE classification = 'Class II')  AS class_ii,
  COUNT(*) FILTER (WHERE classification = 'Class III') AS class_iii
FROM public.fda_enforcement_reports
WHERE recall_initiation_date IS NOT NULL
  AND EXTRACT(YEAR FROM recall_initiation_date) BETWEEN 2015 AND 2025
GROUP BY 1
ORDER BY 1;

-- 3. By classification
SELECT
  COALESCE(classification, 'Unknown') AS classification,
  COUNT(*) AS recalls,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM public.fda_enforcement_reports
GROUP BY 1
ORDER BY 2 DESC;
-- expected: Class II 48820 (86.0%) | Class I 5237 (9.2%) | Class III 2720 (4.8%)

-- 4. By product kind
SELECT
  COALESCE(product_kind, 'Unknown') AS product_kind,
  COUNT(*) AS recalls,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM public.fda_enforcement_reports
GROUP BY 1
ORDER BY 2 DESC;
-- expected: device 39095 (68.9%) | drug 17682 (31.1%)

-- 5. Voluntary vs mandated
SELECT
  COALESCE(voluntary_mandated, 'Unknown') AS voluntary_mandated,
  COUNT(*) AS recalls,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM public.fda_enforcement_reports
GROUP BY 1
ORDER BY 2 DESC;
-- expected: Voluntary firm-initiated 56430 (99.4%) | FDA Mandated 302 (0.5%)

-- 6. Drug recall reason categories
SELECT
  CASE
    WHEN reason_for_recall ILIKE '%steril%' THEN 'Sterility / contamination concerns'
    WHEN reason_for_recall ILIKE '%contamina%' OR reason_for_recall ILIKE '%particulate%' THEN 'Contamination (non-sterility)'
    WHEN reason_for_recall ILIKE '%CGMP%' OR reason_for_recall ILIKE '%cGMP%' OR reason_for_recall ILIKE '%GMP%' THEN 'cGMP / manufacturing deviations'
    WHEN reason_for_recall ILIKE '%mislabel%' OR reason_for_recall ILIKE '%label%' THEN 'Labeling errors'
    WHEN reason_for_recall ILIKE '%potent%' OR reason_for_recall ILIKE '%subpotent%' OR reason_for_recall ILIKE '%superpotent%' THEN 'Potency / out-of-specification'
    WHEN reason_for_recall ILIKE '%temperatur%' THEN 'Temperature / storage excursion'
    ELSE 'Other'
  END AS reason_category,
  COUNT(*) AS recalls,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM public.fda_enforcement_reports
WHERE product_kind = 'drug' AND reason_for_recall IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC;

-- 7. Device recall reason categories
SELECT
  CASE
    WHEN reason_for_recall ILIKE '%steril%' THEN 'Sterility / packaging integrity'
    WHEN reason_for_recall ILIKE '%software%' OR reason_for_recall ILIKE '%firmware%' THEN 'Software / firmware defect'
    WHEN reason_for_recall ILIKE '%label%' OR reason_for_recall ILIKE '%IFU%' OR reason_for_recall ILIKE '%instruction%' THEN 'Labeling / IFU errors'
    WHEN reason_for_recall ILIKE '%leak%' OR reason_for_recall ILIKE '%seal%' THEN 'Leak / seal failure'
    WHEN reason_for_recall ILIKE '%quality system%' OR reason_for_recall ILIKE '%QSR%' OR reason_for_recall ILIKE '%validation%' THEN 'Quality system / validation failure'
    WHEN reason_for_recall ILIKE '%fracture%' OR reason_for_recall ILIKE '%break%' OR reason_for_recall ILIKE '%crack%' THEN 'Component fracture / breakage'
    WHEN reason_for_recall ILIKE '%contamina%' OR reason_for_recall ILIKE '%particulate%' THEN 'Contamination'
    ELSE 'Other / design / component defect'
  END AS reason_category,
  COUNT(*) AS recalls,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM public.fda_enforcement_reports
WHERE product_kind = 'device' AND reason_for_recall IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC;

-- 8. Top 10 firms by recall count
SELECT
  firm_name_normalized AS firm,
  COUNT(*) AS recalls,
  COUNT(*) FILTER (WHERE classification = 'Class I') AS class_i,
  ROUND(COUNT(*) FILTER (WHERE classification = 'Class I') * 100.0 / NULLIF(COUNT(*),0), 1) AS class_i_pct,
  MIN(EXTRACT(YEAR FROM recall_initiation_date))::int AS first_year,
  MAX(EXTRACT(YEAR FROM recall_initiation_date))::int AS latest_year,
  COUNT(DISTINCT EXTRACT(YEAR FROM recall_initiation_date)) AS distinct_years
FROM public.fda_enforcement_reports
WHERE firm_name_normalized IS NOT NULL AND firm_name_normalized != ''
  AND recall_initiation_date IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

-- 9. Firm concentration
WITH firm_counts AS (
  SELECT firm_name_normalized, COUNT(*) AS recalls
  FROM public.fda_enforcement_reports
  WHERE firm_name_normalized IS NOT NULL AND firm_name_normalized != ''
  GROUP BY 1
)
SELECT
  COUNT(*) AS total_firms,
  SUM(recalls) AS total_recalls,
  COUNT(*) FILTER (WHERE recalls = 1)   AS single_recall_firms,
  COUNT(*) FILTER (WHERE recalls >= 2)  AS repeat_firms,
  COUNT(*) FILTER (WHERE recalls >= 10) AS firms_10_plus,
  COUNT(*) FILTER (WHERE recalls >= 50) AS firms_50_plus,
  COUNT(*) FILTER (WHERE recalls >= 100) AS firms_100_plus,
  SUM(recalls) FILTER (WHERE recalls >= 10)  AS recalls_from_10_plus,
  SUM(recalls) FILTER (WHERE recalls >= 50)  AS recalls_from_50_plus,
  SUM(recalls) FILTER (WHERE recalls >= 100) AS recalls_from_100_plus
FROM firm_counts;
-- expected: 4204 firms | 109 with 100+ recalls | 26,593 recalls from those 109 firms (46.8%)
