-- OPIM 5272 hospital / small ACO reporting queries
-- These SELECTs correspond to the displayed portfolio query outputs.

-- 1. High-risk attributed patient panel
SELECT
  p.patient_id,
  p.patient_name,
  p.payer_name,
  p.risk_score,
  p.primary_care_doctor,
  p.encounters,
  p.open_quality_gaps,
  COALESCE(rx.controlled_prescriptions, 0) AS controlled_prescriptions
FROM vw_aco_patient_panel p
LEFT JOIN (
  SELECT
    pp.patient_id,
    COUNT(*) AS controlled_prescriptions
  FROM patient_prescription pp
  JOIN medication m ON m.medication_code = pp.medication_code
  WHERE m.controlled_substance = TRUE
  GROUP BY pp.patient_id
) rx ON rx.patient_id = p.patient_id
WHERE p.risk_score >= 2.00 OR p.open_quality_gaps >= 3
ORDER BY p.risk_score DESC, p.open_quality_gaps DESC
LIMIT 12;

-- 2. ACO quality gaps by measure
SELECT
  measure_code,
  measure_name,
  measure_domain,
  total_gaps,
  open_gaps,
  scheduled_gaps,
  closed_gaps,
  ROUND(closure_rate, 3) AS closure_rate
FROM vw_quality_gap_summary
ORDER BY open_gaps DESC, total_gaps DESC;

-- 3. Controlled prescription history
SELECT
  patient_id,
  patient_name,
  medication_name,
  controlled_prescription_count,
  first_prescribed_at,
  last_prescribed_at,
  prescribing_doctors
FROM vw_controlled_prescription_history
ORDER BY controlled_prescription_count DESC, last_prescribed_at DESC
LIMIT 12;

-- 4. Department resource profile
SELECT
  hospital_name,
  department_name,
  doctor_count,
  visit_count,
  unique_patient_count,
  procedure_count,
  ROUND(total_procedure_cost, 2) AS total_procedure_cost
FROM vw_department_resource_profile
ORDER BY visit_count DESC, total_procedure_cost DESC
LIMIT 12;
