// --------------------------------------------------
// Clinical Metrics
// --------------------------------------------------

// 1. Occupancy Rate (last 6 months)
$occRows = fetchAll($conn, <<<SQL
SELECT FORMAT(RecordDate,'yyyy-MM') AS month,
       SUM(OccupiedBeds) * 1.0 / SUM(TotalBeds) AS occupancy_rate
FROM view_ods_daily_census
WHERE RecordDate >= DATEADD(month, -5, GETDATE())
GROUP BY FORMAT(RecordDate,'yyyy-MM')
ORDER BY month
SQL
);
$occLabels = array_column($occRows, 'month');
$occValues = array_column($occRows, 'occupancy_rate');

// 2. Census Trend (last 30 days)
$censusRows   = fetchAll($conn, <<<SQL
SELECT FORMAT(RecordDate,'MM-dd') AS date, OccupiedBeds
FROM view_ods_daily_census
WHERE RecordDate >= DATEADD(day, -29, GETDATE())
ORDER BY RecordDate
SQL
);
$censusLabels = array_column($censusRows, 'date');
$censusValues = array_column($censusRows, 'OccupiedBeds');

// 3. Admissions vs Discharges (last 6 months)
$admDiscRows = fetchAll($conn, <<<SQL
WITH a AS (
  SELECT FORMAT(AdmissionDate,'yyyy-MM') AS month, COUNT(*) AS admissions
  FROM view_ods_patient_stay
  WHERE AdmissionDate >= DATEADD(month, -5, GETDATE())
  GROUP BY FORMAT(AdmissionDate,'yyyy-MM')
),
 d AS (
  SELECT FORMAT(DischargeDate,'yyyy-MM') AS month, COUNT(*) AS discharges
  FROM view_ods_patient_stay
  WHERE DischargeDate >= DATEADD(month, -5, GETDATE())
  GROUP BY FORMAT(DischargeDate,'yyyy-MM')
)
SELECT COALESCE(a.month, d.month) AS month,
       ISNULL(a.admissions,0) AS admissions,
       ISNULL(d.discharges,0) AS discharges
FROM a FULL OUTER JOIN d ON a.month = d.month
ORDER BY month
SQL
);
$admDiscLabels = [];
$admDiscAdms   = [];
$admDiscDscs   = [];
foreach ($admDiscRows as $r) {
    $admDiscLabels[] = $r['month'];
    $admDiscAdms[]   = (int)$r['admissions'];
    $admDiscDscs[]   = (int)$r['discharges'];
}

// 4. Average LOS (last 6 months)
$losRows = fetchAll($conn, <<<SQL
SELECT FORMAT(AdmissionDate,'yyyy-MM') AS month,
       AVG(DATEDIFF(day, AdmissionDate, DischargeDate)) AS avg_los
FROM view_ods_patient_stay
WHERE DischargeDate IS NOT NULL
  AND AdmissionDate >= DATEADD(month, -5, GETDATE())
GROUP BY FORMAT(AdmissionDate,'yyyy-MM')
ORDER BY month
SQL
);
$losLabels = array_column($losRows, 'month');
$losValues = array_column($losRows, 'avg_los');

// 5. Readmission Rate (30-day)
$readRows = fetchAll($conn, <<<SQL
WITH stays AS (
  SELECT PatientID,
         AdmissionDate,
         DischargeDate,
         LEAD(AdmissionDate) OVER (PARTITION BY PatientID ORDER BY AdmissionDate) AS next_adm
  FROM view_ods_patient_stay
)
SELECT FORMAT(DischargeDate,'yyyy-MM') AS month,
       SUM(CASE WHEN DATEDIFF(day,DischargeDate,next_adm) BETWEEN 1 AND 30 THEN 1 ELSE 0 END)*1.0
         /NULLIF(COUNT(*),0) AS readmit_rate
FROM stays
WHERE DischargeDate >= DATEADD(month,-5,GETDATE())
GROUP BY FORMAT(DischargeDate,'yyyy-MM')
ORDER BY month
SQL
);
$readLabels = array_column($readRows,'month');
$readValues = array_column($readRows,'readmit_rate');

// 6. Assessment Completion On Time
$assRows = fetchAll($conn, <<<SQL
SELECT FORMAT(ScheduledDate,'yyyy-MM') AS month,
       SUM(CASE WHEN CompletedDate <= DueDate THEN 1 ELSE 0 END)*1.0/NULLIF(COUNT(*),0) AS completion_rate
FROM view_ods_assessment_schedule sch
LEFT JOIN view_ods_assessment_responses resp ON sch.ScheduleID=resp.AssessmentScheduleID
WHERE ScheduledDate >= DATEADD(month,-5,GETDATE())
GROUP BY FORMAT(ScheduledDate,'yyyy-MM')
ORDER BY month
SQL
);
$assLabels = array_column($assRows,'month');
$assValues = array_column($assRows,'completion_rate');

// 7. PDPM Case-Mix Index
$pdpmRows = fetchAll($conn, <<<SQL
SELECT FORMAT(AssessmentDate,'yyyy-MM') AS month, AVG(CaseMixIndex) AS avg_cmi
FROM view_ods_assessment_pdpm
WHERE AssessmentDate >= DATEADD(month,-5,GETDATE())
GROUP BY FORMAT(AssessmentDate,'yyyy-MM')
ORDER BY month
SQL
);
$pdpmLabels = array_column($pdpmRows,'month');
$pdpmValues = array_column($pdpmRows,'avg_cmi');

// 8. Incident Rate per 1000 Resident-Days
$incRows = fetchAll($conn, <<<SQL
SELECT dc.month, COUNT(*)*1000.0/NULLIF(dc.res_days,0) AS rate
FROM view_ods_inc_incident inc
JOIN (
  SELECT FORMAT(RecordDate,'yyyy-MM') AS month, SUM(OccupiedBeds) AS res_days
  FROM view_ods_daily_census
  WHERE RecordDate >= DATEADD(month,-5,GETDATE())
  GROUP BY FORMAT(RecordDate,'yyyy-MM')
) dc ON FORMAT(inc.IncidentDate,'yyyy-MM')=dc.month
WHERE inc.IncidentDate >= DATEADD(month,-5,GETDATE())
GROUP BY dc.month,dc.res_days
ORDER BY dc.month
SQL
);
$incLabels = array_column($incRows,'month');
$incValues = array_column($incRows,'rate');

// 9. Incident Follow-up Lag
$fupRows = fetchAll($conn, <<<SQL
SELECT FORMAT(inc.IncidentDate,'yyyy-MM') AS month,
       AVG(DATEDIFF(day,inc.IncidentDate,cp.CompletedDate)) AS avg_lag
FROM view_ods_inc_incident inc
JOIN view_ods_inc_cp_task cp ON inc.IncidentID=cp.IncidentID
WHERE inc.IncidentDate >= DATEADD(month,-5,GETDATE())
GROUP BY FORMAT(inc.IncidentDate,'yyyy-MM')
ORDER BY month
SQL
);
$fupLabels = array_column($fupRows,'month');
$fupValues = array_column($fupRows,'avg_lag');

// 10. Therapy Minutes per Week (last 12 weeks)
$thRows = fetchAll($conn, <<<SQL
SELECT FORMAT(ServiceDate,'yyyy-ww') AS week, SUM(Minutes) AS total_minutes
FROM view_ods_th_minutes
WHERE ServiceDate >= DATEADD(week,-12,GETDATE())
GROUP BY FORMAT(ServiceDate,'yyyy-ww')
ORDER BY week
SQL
);
$thLabels = array_column($thRows,'week');
$thValues = array_column($thRows,'total_minutes');

// 11. Therapy Efficiency
$etRows = fetchAll($conn, <<<SQL
SELECT week, SUM(billed_minutes)*1.0/NULLIF(SUM(delivered_minutes),0) AS efficiency
FROM (
  SELECT FORMAT(ServiceDate,'yyyy-ww') AS week,
         DeliveredMinutes AS delivered_minutes,
         BilledMinutes    AS billed_minutes
  FROM view_ods_th_minutes
  WHERE ServiceDate >= DATEADD(week,-12,GETDATE())
) sub
GROUP BY week
ORDER BY week
SQL
);
$etLabels = array_column($etRows,'week');
$etValues = array_column($etRows,'efficiency');

// 12. Pathway Adherence
$paRows = fetchAll($conn, <<<SQL
SELECT FORMAT(TriggerDate,'yyyy-MM') AS month,
       SUM(CASE WHEN p.CompletedDate IS NOT NULL THEN 1 ELSE 0 END)*1.0/NULLIF(COUNT(*),0) AS adherence
FROM view_ods_cpg_care_pathway_trigger t
LEFT JOIN view_ods_cpg_care_program_pathway p ON t.TriggerID=p.TriggerID
WHERE TriggerDate >= DATEADD(month,-5,GETDATE())
GROUP BY FORMAT(TriggerDate,'yyyy-MM')
ORDER BY month
SQL
);
$paLabels = array_column($paRows,'month');
$paValues = array_column($paRows,'adherence');

// 13. Program Initiation Lag
$pilRows = fetchAll($conn, <<<SQL
SELECT FORMAT(EventDate,'yyyy-MM') AS month, AVG(DATEDIFF(day,EligibilityDate,StartDate)) AS avg_lag
FROM view_ods_cpg_care_program_audit
WHERE EventDate >= DATEADD(month,-5,GETDATE())
GROUP BY FORMAT(EventDate,'yyyy-MM')
ORDER BY month
SQL
);
$pilLabels = array_column($pilRows,'month');
$pilValues = array_column($pilRows,'avg_lag');

// 14. Daily Census by Building
$buildRows = fetchAll($conn, <<<SQL
SELECT FacilityID, SUM(OccupiedBeds) AS beds
FROM view_ods_daily_census
WHERE CAST(RecordDate AS date)=CAST(GETDATE() AS date)
GROUP BY FacilityID
ORDER BY FacilityID
SQL
);
$buildLabels = array_column($buildRows,'FacilityID');
$buildValues = array_column($buildRows,'beds');

// --------------------------------------------------
// Financial Metrics
// --------------------------------------------------

// AR Aging Buckets
$arRows = fetchAll($conn, "SELECT AgingBucket, SUM(Amount) AS total_amount FROM view_ods_ar_aging GROUP BY AgingBucket ORDER BY AgingBucket");
$arLabels = array_column($arRows,'AgingBucket');
$arValues = array_column($arRows,'total_amount');

// Days Sales Outstanding
$dsoRows = fetchAll($conn, <<<SQL
SELECT FORMAT(InvoiceDate,'yyyy-MM') AS month, AVG(DATEDIFF(day,InvoiceDate,PaymentDate)) AS avg_dso
FROM view_ods_ar_invoice_claim i
JOIN view_ods_ar_applied_payment p ON i.ClaimID=p.ClaimID
WHERE PaymentDate>=DATEADD(month,-5,GETDATE())
GROUP BY FORMAT(InvoiceDate,'yyyy-MM')
ORDER BY month
SQL
);
$dsoLabels = array_column($dsoRows,'month');
$dsoValues = array_column($dsoRows,'avg_dso');

// Payer Mix
$pmRows = fetchAll($conn, "SELECT PayerType, SUM(Amount) AS total FROM view_ods_ar_payertype_budget GROUP BY PayerType");
$pmLabels = array_column($pmRows,'PayerType');
$pmValues = array_column($pmRows,'total');

// Denial Rates
$drRows = fetchAll($conn, <<<SQL
SELECT Category, SUM(CASE WHEN Status='Denied' THEN 1 ELSE 0 END)*1.0/NULLIF(COUNT(*),0) AS denial_rate
FROM view_ods_ar_transaction_detail
GROUP BY Category
SQL
);
$drLabels = array_column($drRows,'Category');
$drValues = array_column($drRows,'denial_rate');