-- Diagnosis burden by pin code (Trino / Iceberg)
SELECT
  p.pin_code,
  COUNT(DISTINCT v.patient_id) AS patients,
  COUNT(*) AS total_visits,
  ROUND(
    COUNT(*)* 100.0 /
    SUM(COUNT(*)) OVER(),2
  ) AS pct_of_total
FROM iceberg.gold.fct_opd_visits v
JOIN iceberg.gold.dim_patients p
  ON v.patient_id = p.patient_id
WHERE (
  v.icd10_code LIKE 'E11%' -- T2 Diabetes
  OR v.icd10_code LIKE 'I10%' -- Hypertension
)
AND v.visit_date >= current_date - INTERVAL '12' MONTH
GROUP BY p.pin_code
ORDER BY patients DESC;

-- ANC-registered women: missed visits in last 4 weeks
SELECT
  p.patient_name,
  p.mobile_number,
  p.pin_code,
  a.edd AS expected_delivery,
  MAX(v.visit_date) AS last_anc_visit,
  current_date - MAX(v.visit_date) AS days_overdue
FROM iceberg.gold.dim_patients p
JOIN iceberg.gold.fct_anc_registrations a
  ON p.patient_id = a.patient_id
JOIN iceberg.gold.fct_opd_visits v
  ON p.patient_id = v.patient_id
  AND v.visit_type = 'ANC'
WHERE a.status = 'active'
GROUP BY p.patient_name, p.mobile_number,
         p.pin_code, a.edd
HAVING MAX(v.visit_date) < current_date
        - INTERVAL '28' DAY
ORDER BY days_overdue DESC;

-- Weekly respiratory admissions + AQI correlation
SELECT
  date_trunc('week', a.admission_date) AS week,
  COUNT(*) AS respiratory_admissions,
  AVG(q.aqi_value) AS avg_aqi,
  MAX(q.aqi_value) AS peak_aqi
FROM iceberg.gold.fct_admissions a
JOIN iceberg.gold.dim_diagnoses d
  ON a.primary_diagnosis_id = d.diagnosis_id
LEFT JOIN iceberg.silver.ext_aqi_daily q
  ON CAST(a.admission_date AS DATE) = q.aqi_date
WHERE d.icd10_chapter = 'J'
  -- Chapter J: Respiratory system
AND a.admission_date >= current_date
    - INTERVAL '24' MONTH
GROUP BY 1
ORDER BY week;

-- Dengue early warning: fever + thrombocytopenia clusters
WITH weekly_signals AS (
  SELECT
    p.pin_code,
    date_trunc('week', v.visit_date) AS week,
    COUNT(*) AS fever_cases,
    COUNT(CASE WHEN
      l.platelet_count < 100000
    THEN 1 END) AS thrombocytopenia_cases
  FROM iceberg.gold.fct_opd_visits v
  JOIN iceberg.gold.dim_patients p ON v.patient_id = p.patient_id
  LEFT JOIN iceberg.gold.fct_lab_results l ON v.visit_id = l.visit_id
  WHERE v.presenting_complaint ILIKE '%fever%'
  GROUP BY 1, 2
)
SELECT *, CASE
  WHEN thrombocytopenia_cases >= 5 THEN '🔴 ALERT'
  WHEN thrombocytopenia_cases >= 2 THEN '🟡 WATCH'
  ELSE '🟢 NORMAL'
END AS alert_level
FROM weekly_signals
ORDER BY week DESC, fever_cases DESC;

-- Today's high-risk discharge cohort
SELECT
  p.patient_name,
  p.patient_id,
  d.discharge_date,
  d.primary_diagnosis_display,
  r.risk_score,
  r.risk_tier,
  r.top_risk_factor
FROM iceberg.gold.fct_discharges d
JOIN iceberg.gold.dim_patients p
  ON d.patient_id = p.patient_id
JOIN iceberg.gold.fct_readmission_scores r
  ON d.discharge_id = r.discharge_id
WHERE d.discharge_date = current_date
  AND r.risk_tier = 'HIGH'
ORDER BY r.risk_score DESC;