<?php

namespace App\Http\Controllers\NcdAnalysis\Concerns;

use App\Models\Patients;
use App\Models\PtConfig;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\Process\Process;

trait BuildsNcdClinicalAnalytics
{
    private function buildMissedAppointmentVisitStats(array $followups, array $activePatientIds): array
    {
        if (empty($followups) || empty($activePatientIds)) {
            return [
                'missed_visits' => 0,
                'total_visits' => 0,
                'rate' => 0,
            ];
        }

        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $activeSet = array_flip($activePatientIds);
        $visitsByPatient = [];
        $totalVisits = 0;

        foreach ($followups as $row) {
            $pid = $row[$idField] ?? null;
            if ($pid === null || $pid === '' || !isset($activeSet[$pid])) {
                continue;
            }
            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            if ($visitTs === null) {
                continue;
            }
            $totalVisits++;
            $visitsByPatient[$pid][] = [
                'visit_ts' => $visitTs,
                'next_appt_ts' => $this->parseDateValue($row['next_appointment'] ?? null),
            ];
        }

        $missed = 0;
        foreach ($visitsByPatient as $visits) {
            usort($visits, function ($a, $b) {
                return $a['visit_ts'] <=> $b['visit_ts'];
            });
            $count = count($visits);
            for ($i = 0; $i < $count; $i++) {
                $nextApptTs = $visits[$i]['next_appt_ts'];
                if ($nextApptTs === null) {
                    continue;
                }
                $nextVisitTs = $i + 1 < $count ? $visits[$i + 1]['visit_ts'] : null;
                if ($nextVisitTs === null) {
                    $missed++;
                    continue;
                }
                if (date('Y-m-d', $nextVisitTs) !== date('Y-m-d', $nextApptTs)) {
                    $missed++;
                }
            }
        }

        $rate = $totalVisits ? round(($missed / $totalVisits) * 100, 1) : 0;

        return [
            'missed_visits' => $missed,
            'total_visits' => $totalVisits,
            'rate' => $rate,
        ];
    }

    private function buildClinicVisitPlanTrend(array $followups, int $onTimeGraceDays = 7, int $lateUpperDays = 84, int $minYear = 2018): array
    {
        if (empty($followups)) {
            return [
                'labels' => [],
                'series' => [],
                'summary' => [
                    'min_year' => $minYear,
                    'max_year' => $minYear,
                    'on_time_grace_days' => $onTimeGraceDays,
                    'late_upper_days' => $lateUpperDays,
                    'assessed_pairs' => 0,
                    'excluded_before_min_year' => 0,
                    'excluded_after_max_year' => 0,
                    'excluded_over_84_days' => 0,
                    'ltfu_pairs' => 0,
                    'return_to_care_events' => 0,
                    'return_to_care_unique_patients' => 0,
                    'return_to_care_under_gap' => 0,
                    'missing_next_appointment' => 0,
                    'no_following_visit' => 0,
                ],
            ];
        }

        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $visitsByPatient = [];
        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row[$idField] ?? null);
            if ($pid === null) {
                continue;
            }
            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            if ($visitTs === null) {
                continue;
            }
            $visitsByPatient[$pid][] = [
                'visit_ts' => $visitTs,
                'next_appt_ts' => $this->parseDateValue($row['next_appointment'] ?? null),
            ];
        }

        $yearCounts = [];
        $assessedPairs = 0;
        $excludedBeforeMinYear = 0;
        $ltfuPairs = 0;
        $missingNextAppointment = 0;
        $noFollowingVisit = 0;
        $returnToCareEvents = 0;
        $returnToCareUnderGap = 0;
        $patientsWithReturn = [];

        $initYearCounts = static function (array &$counts, int $year): void {
            if (!isset($counts[$year])) {
                $counts[$year] = [
                    'on_time' => 0,
                    'unplan' => 0,
                    'late' => 0,
                    'ltfu' => 0,
                    'return_to_care' => 0,
                ];
            }
        };

        foreach ($visitsByPatient as $pid => $visits) {
            usort($visits, function ($a, $b) {
                return $a['visit_ts'] <=> $b['visit_ts'];
            });

            $count = count($visits);
            for ($i = 0; $i < $count; $i++) {
                $current = $visits[$i];
                $nextApptTs = $current['next_appt_ts'] ?? null;
                if ($nextApptTs === null) {
                    $missingNextAppointment++;
                    continue;
                }

                $nextVisit = $i + 1 < $count ? $visits[$i + 1] : null;
                if ($nextVisit === null) {
                    $noFollowingVisit++;
                    continue;
                }

                $gapDays = (int) floor(($nextVisit['visit_ts'] - $current['visit_ts']) / 86400);
                if ($gapDays >= $lateUpperDays) {
                    $returnYear = (int) date('Y', $nextVisit['visit_ts']);
                    if ($returnYear >= $minYear) {
                        $initYearCounts($yearCounts, $returnYear);
                        $yearCounts[$returnYear]['return_to_care']++;
                    }
                    $returnToCareEvents++;
                    $patientsWithReturn[$pid] = true;
                } else {
                    $returnToCareUnderGap++;
                }

                $year = (int) date('Y', $nextApptTs);
                if ($year < $minYear) {
                    $excludedBeforeMinYear++;
                    continue;
                }
                $initYearCounts($yearCounts, $year);

                $deltaDays = (int) floor(($nextVisit['visit_ts'] - $nextApptTs) / 86400);
                if ($deltaDays < 0) {
                    $yearCounts[$year]['unplan']++;
                    $assessedPairs++;
                    continue;
                }
                if ($deltaDays <= $onTimeGraceDays) {
                    $yearCounts[$year]['on_time']++;
                    $assessedPairs++;
                    continue;
                }
                if ($deltaDays < $lateUpperDays) {
                    $yearCounts[$year]['late']++;
                    $assessedPairs++;
                    continue;
                }

                $yearCounts[$year]['ltfu']++;
                $ltfuPairs++;
                $assessedPairs++;
            }
        }

        if (empty($yearCounts)) {
            return [
                'labels' => [],
                'series' => [],
                'summary' => [
                    'min_year' => $minYear,
                    'max_year' => $minYear,
                    'on_time_grace_days' => $onTimeGraceDays,
                    'late_upper_days' => $lateUpperDays,
                    'assessed_pairs' => 0,
                    'excluded_before_min_year' => $excludedBeforeMinYear,
                    'excluded_after_max_year' => 0,
                    'excluded_over_84_days' => 0,
                    'ltfu_pairs' => $ltfuPairs,
                    'return_to_care_events' => $returnToCareEvents,
                    'return_to_care_unique_patients' => count($patientsWithReturn),
                    'return_to_care_under_gap' => $returnToCareUnderGap,
                    'missing_next_appointment' => $missingNextAppointment,
                    'no_following_visit' => $noFollowingVisit,
                ],
            ];
        }

        ksort($yearCounts);
        $years = array_keys($yearCounts);
        $startYear = min($years);
        $endYear = max($years);
        for ($year = $startYear; $year <= $endYear; $year++) {
            if (!isset($yearCounts[$year])) {
                $yearCounts[$year] = [
                    'on_time' => 0,
                    'unplan' => 0,
                    'late' => 0,
                    'ltfu' => 0,
                    'return_to_care' => 0,
                ];
            }
        }
        ksort($yearCounts);

        $labels = [];
        $onTime = [];
        $unplan = [];
        $late = [];
        $ltfu = [];
        $returnToCare = [];
        foreach ($yearCounts as $year => $counts) {
            $labels[] = (string) $year;
            $onTime[] = (int) ($counts['on_time'] ?? 0);
            $unplan[] = (int) ($counts['unplan'] ?? 0);
            $late[] = (int) ($counts['late'] ?? 0);
            $ltfu[] = (int) ($counts['ltfu'] ?? 0);
            $returnToCare[] = (int) ($counts['return_to_care'] ?? 0);
        }

        return [
            'labels' => $labels,
            'series' => [
                ['key' => 'on_time', 'label' => 'Ontime (0-7 days)', 'data' => $onTime, 'color' => '#22c55e'],
                ['key' => 'unplan', 'label' => 'Unplan (early)', 'data' => $unplan, 'color' => '#3b82f6'],
                ['key' => 'late', 'label' => 'Late (8-84 days)', 'data' => $late, 'color' => '#ef4444'],
                ['key' => 'ltfu', 'label' => 'LTFU (>=84 days)', 'data' => $ltfu, 'color' => '#f97316'],
                ['key' => 'return_to_care', 'label' => 'Return to care', 'data' => $returnToCare, 'color' => '#8b5cf6'],
            ],
            'summary' => [
                'min_year' => $minYear,
                'max_year' => $endYear,
                'on_time_grace_days' => $onTimeGraceDays,
                'late_upper_days' => $lateUpperDays,
                'assessed_pairs' => $assessedPairs,
                'excluded_before_min_year' => $excludedBeforeMinYear,
                'excluded_after_max_year' => 0,
                'excluded_over_84_days' => 0,
                'ltfu_pairs' => $ltfuPairs,
                'return_to_care_events' => $returnToCareEvents,
                'return_to_care_unique_patients' => count($patientsWithReturn),
                'return_to_care_under_gap' => $returnToCareUnderGap,
                'missing_next_appointment' => $missingNextAppointment,
                'no_following_visit' => $noFollowingVisit,
            ],
        ];
    }

    private function buildReturnToCareTrend(array $followups, int $gapDays = 84): array
    {
        if (empty($followups)) {
            return [
                'labels' => [],
                'series' => [],
                'summary' => [
                    'gap_days' => $gapDays,
                    'events' => 0,
                    'unique_patients' => 0,
                    'excluded_pairs_under_gap' => 0,
                ],
            ];
        }

        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $visitsByPatient = [];
        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row[$idField] ?? null);
            if ($pid === null) {
                continue;
            }
            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            if ($visitTs === null) {
                continue;
            }
            $visitsByPatient[$pid][] = $visitTs;
        }

        $yearCounts = [];
        $events = 0;
        $excludedPairsUnderGap = 0;
        $patientsWithReturn = [];

        foreach ($visitsByPatient as $pid => $visits) {
            if (count($visits) < 2) {
                continue;
            }

            sort($visits, SORT_NUMERIC);
            $count = count($visits);
            for ($i = 1; $i < $count; $i++) {
                $deltaDays = (int) floor(($visits[$i] - $visits[$i - 1]) / 86400);
                if ($deltaDays >= $gapDays) {
                    $year = (int) date('Y', $visits[$i]);
                    $yearCounts[$year] = ($yearCounts[$year] ?? 0) + 1;
                    $events++;
                    $patientsWithReturn[$pid] = true;
                } else {
                    $excludedPairsUnderGap++;
                }
            }
        }

        if (empty($yearCounts)) {
            return [
                'labels' => [],
                'series' => [],
                'summary' => [
                    'gap_days' => $gapDays,
                    'events' => 0,
                    'unique_patients' => 0,
                    'excluded_pairs_under_gap' => $excludedPairsUnderGap,
                ],
            ];
        }

        ksort($yearCounts);
        $years = array_keys($yearCounts);
        $startYear = min($years);
        $endYear = max($years);
        for ($year = $startYear; $year <= $endYear; $year++) {
            if (!isset($yearCounts[$year])) {
                $yearCounts[$year] = 0;
            }
        }
        ksort($yearCounts);

        $labels = [];
        $values = [];
        foreach ($yearCounts as $year => $count) {
            $labels[] = (string) $year;
            $values[] = (int) $count;
        }

        return [
            'labels' => $labels,
            'series' => [
                ['key' => 'return_to_care', 'label' => 'Return to care (>=84 day gap)', 'data' => $values, 'color' => '#8b5cf6'],
            ],
            'summary' => [
                'gap_days' => $gapDays,
                'events' => $events,
                'unique_patients' => count($patientsWithReturn),
                'excluded_pairs_under_gap' => $excludedPairsUnderGap,
            ],
        ];
    }

    private function buildBpStageControlStatus(array $registers, array $followups, ?int $observeTs = null): array
    {
        $stages = ['Normal', 'Stage 1', 'Stage 2', 'Stage 3'];
        $distributionStages = ['Normal', 'Stage 1', 'Stage 2', 'Stage 3', 'Unavailable'];
        $stageRank = ['Normal' => 0, 'Stage 1' => 1, 'Stage 2' => 2, 'Stage 3' => 3];
        $distributionColors = [
            'Normal' => '#22c55e',
            'Stage 1' => '#f59e0b',
            'Stage 2' => '#ef4444',
            'Stage 3' => '#7c3aed',
            'Unavailable' => '#cbd5e1',
        ];
        $emptyStageCounts = array_fill_keys($distributionStages, 0);
        $buildDistributionChart = static function (array $baselineDist, array $latestDist) use ($distributionStages, $distributionColors): array {
            $series = [];
            foreach ($distributionStages as $label) {
                $series[] = [
                    'label' => $label,
                    'data' => [$baselineDist[$label] ?? 0, $latestDist[$label] ?? 0],
                    'color' => $distributionColors[$label] ?? '#0ea5e9',
                ];
            }
            return [
                'labels' => ['Baseline', 'Last record'],
                'series' => $series,
            ];
        };
        $statusByPatient = $this->buildLatestFollowupStatusByPatient($followups, 84, $observeTs ?? time());

        $baselineByPatient = [];
        foreach ($registers as $row) {
            $pid = $this->normalizePatientId($row['patient_id'] ?? $row['pid'] ?? null);
            if ($pid === null) {
                continue;
            }
            if (!$this->hasNewKnownDiagnosis($row['first_hypertension'] ?? ($row['1stHypertension'] ?? null))) {
                continue;
            }
            $regTs = $this->parseDateValue($row['reg_date'] ?? null) ?? 0;
            $bpInfo = $this->extractBaselineStageFromRegister($row);

            $current = $baselineByPatient[$pid] ?? null;
            if ($current === null || $regTs < $current['ts'] || ($regTs === $current['ts'] && $current['status'] !== 'valid' && $bpInfo['status'] === 'valid')) {
                $baselineByPatient[$pid] = [
                    'ts' => $regTs,
                    'status' => $bpInfo['status'],
                    'stage' => $bpInfo['stage'] ?? null,
                    'invalid_values' => $bpInfo['invalid_values'] ?? [],
                ];
                continue;
            }

            if ($regTs === $current['ts'] && $bpInfo['status'] === 'invalid') {
                $baselineByPatient[$pid]['invalid_values'] = array_values(array_unique(array_merge(
                    $baselineByPatient[$pid]['invalid_values'] ?? [],
                    $bpInfo['invalid_values'] ?? []
                )));
            }
        }

        $latestValidByPatient = [];
        $latestInvalidByPatient = [];
        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row['patient_id'] ?? $row['pid'] ?? null);
            if ($pid === null) {
                continue;
            }
            $visitTs = $this->parseDateValue($row['visit_date'] ?? null) ?? 0;
            if ($visitTs <= 0 || $visitTs > ($observeTs ?? time())) {
                continue;
            }
            $bpInfo = $this->extractLatestStageFromFollowup($row);

            if ($bpInfo['status'] === 'valid') {
                $current = $latestValidByPatient[$pid] ?? null;
                if ($current === null || $visitTs > $current['ts']) {
                    $latestValidByPatient[$pid] = [
                        'ts' => $visitTs,
                        'status' => 'valid',
                        'stage' => $bpInfo['stage'] ?? null,
                        'invalid_values' => [],
                    ];
                }
                continue;
            }

            if ($bpInfo['status'] !== 'invalid') {
                continue;
            }

            $current = $latestInvalidByPatient[$pid] ?? null;
            if ($current === null || $visitTs > $current['ts']) {
                $latestInvalidByPatient[$pid] = [
                    'ts' => $visitTs,
                    'status' => 'invalid',
                    'stage' => null,
                    'invalid_values' => $bpInfo['invalid_values'] ?? [],
                ];
                continue;
            }

            if ($visitTs === $current['ts']) {
                $latestInvalidByPatient[$pid]['invalid_values'] = array_values(array_unique(array_merge(
                    $latestInvalidByPatient[$pid]['invalid_values'] ?? [],
                    $bpInfo['invalid_values'] ?? []
                )));
            }
        }

        $allPatientIds = array_values(array_keys($baselineByPatient));
        $matrix = [];
        foreach ($stages as $from) {
            $matrix[$from] = [];
            foreach ($stages as $to) {
                $matrix[$from][$to] = 0;
            }
        }

        $baselineDist = $emptyStageCounts;
        $latestDist = $emptyStageCounts;
        $activeBaselineDist = $emptyStageCounts;
        $activeLatestDist = $emptyStageCounts;
        $ltfuBaselineDist = $emptyStageCounts;
        $ltfuLatestDist = $emptyStageCounts;
        $direction = ['Improved' => 0, 'Unchanged' => 0, 'Worsened' => 0];

        $excludedBaselineMissing = 0;
        $excludedBaselineInvalid = 0;
        $excludedLatestMissing = 0;
        $excludedLatestInvalid = 0;
        $baselineInvalidExamples = [];
        $latestInvalidExamples = [];
        $cohortPatients = count($allPatientIds);
        $activeCohortPatients = 0;
        $ltfuCohortPatients = 0;
        $pairedPatients = 0;
        $activePairedPatients = 0;
        $ltfuPairedPatients = 0;

        foreach ($allPatientIds as $pid) {
            $baseline = $baselineByPatient[$pid] ?? ['status' => 'missing', 'stage' => null, 'invalid_values' => []];
            $latest = $latestValidByPatient[$pid]
                ?? $latestInvalidByPatient[$pid]
                ?? ['status' => 'missing', 'stage' => null, 'invalid_values' => []];

            if (($baseline['status'] ?? 'missing') === 'invalid') {
                foreach (($baseline['invalid_values'] ?? []) as $value) {
                    $baselineInvalidExamples[$value] = ($baselineInvalidExamples[$value] ?? 0) + 1;
                }
            }
            if (($latest['status'] ?? 'missing') === 'invalid') {
                foreach (($latest['invalid_values'] ?? []) as $value) {
                    $latestInvalidExamples[$value] = ($latestInvalidExamples[$value] ?? 0) + 1;
                }
            }

            $baselineValid = ($baseline['status'] ?? null) === 'valid' && in_array($baseline['stage'] ?? '', $stages, true);
            $latestValid = ($latest['status'] ?? null) === 'valid' && in_array($latest['stage'] ?? '', $stages, true);
            $baselineBucket = $baselineValid ? $baseline['stage'] : 'Unavailable';
            $latestBucket = $latestValid ? $latest['stage'] : 'Unavailable';
            $latestStatus = $statusByPatient[$pid]['status'] ?? null;

            $baselineDist[$baselineBucket]++;
            $latestDist[$latestBucket]++;
            if ($latestStatus === 'active') {
                $activeCohortPatients++;
                $activeBaselineDist[$baselineBucket]++;
                $activeLatestDist[$latestBucket]++;
            }
            if ($latestStatus === 'ltfu') {
                $ltfuCohortPatients++;
                $ltfuBaselineDist[$baselineBucket]++;
                $ltfuLatestDist[$latestBucket]++;
            }

            if (!$baselineValid) {
                if (($baseline['status'] ?? 'missing') === 'invalid') {
                    $excludedBaselineInvalid++;
                } else {
                    $excludedBaselineMissing++;
                }
            }
            if (!$latestValid) {
                if (($latest['status'] ?? 'missing') === 'invalid') {
                    $excludedLatestInvalid++;
                } else {
                    $excludedLatestMissing++;
                }
            }

            if (!$baselineValid || !$latestValid) {
                continue;
            }

            $pairedPatients++;
            $fromStage = $baseline['stage'];
            $toStage = $latest['stage'];
            $matrix[$fromStage][$toStage]++;

            $fromRank = $stageRank[$fromStage];
            $toRank = $stageRank[$toStage];
            if ($toRank < $fromRank) {
                $direction['Improved']++;
            } elseif ($toRank === $fromRank) {
                $direction['Unchanged']++;
            } else {
                $direction['Worsened']++;
            }

            if ($latestStatus === 'active') {
                $activePairedPatients++;
            }
            if ($latestStatus === 'ltfu') {
                $ltfuPairedPatients++;
            }
        }

        arsort($baselineInvalidExamples);
        arsort($latestInvalidExamples);
        $baselineInvalidTop = [];
        $latestInvalidTop = [];
        foreach (array_slice($baselineInvalidExamples, 0, 10, true) as $value => $count) {
            $baselineInvalidTop[] = ['value' => $value, 'count' => $count];
        }
        foreach (array_slice($latestInvalidExamples, 0, 10, true) as $value => $count) {
            $latestInvalidTop[] = ['value' => $value, 'count' => $count];
        }

        $rowTotals = [];
        $colTotals = array_fill_keys($stages, 0);
        foreach ($stages as $from) {
            $rowTotal = 0;
            foreach ($stages as $to) {
                $value = (int) ($matrix[$from][$to] ?? 0);
                $rowTotal += $value;
                $colTotals[$to] += $value;
            }
            $rowTotals[$from] = $rowTotal;
        }

        $directionChart = [
            ['title' => 'Improved', 'value' => $direction['Improved'], 'color' => '#22c55e'],
            ['title' => 'Unchanged', 'value' => $direction['Unchanged'], 'color' => '#94a3b8'],
            ['title' => 'Worsened', 'value' => $direction['Worsened'], 'color' => '#ef4444'],
        ];

        $distributionChart = $buildDistributionChart($baselineDist, $latestDist);
        $activeDistributionChart = $buildDistributionChart($activeBaselineDist, $activeLatestDist);
        $ltfuDistributionChart = $buildDistributionChart($ltfuBaselineDist, $ltfuLatestDist);
        $hypertensiveWithLastRecord = count(array_intersect(
            array_keys($baselineByPatient),
            array_unique(array_merge(array_keys($latestValidByPatient), array_keys($latestInvalidByPatient)))
        ));

        return [
            'stages' => $stages,
            'matrix' => $matrix,
            'row_totals' => $rowTotals,
            'col_totals' => $colTotals,
            'cohort_patients' => $cohortPatients,
            'active_cohort_patients' => $activeCohortPatients,
            'ltfu_cohort_patients' => $ltfuCohortPatients,
            'paired_patients' => $pairedPatients,
            'active_paired_patients' => $activePairedPatients,
            'ltfu_paired_patients' => $ltfuPairedPatients,
            'direction_chart' => $directionChart,
            'distribution_chart' => $distributionChart,
            'comparison_charts' => [
                'overall' => [
                    'title' => 'Overall patients stage comparison',
                    'cohort_patients' => $cohortPatients,
                    'paired_patients' => $pairedPatients,
                    'distribution_chart' => $distributionChart,
                ],
                'active' => [
                    'title' => 'Active patients stage comparison',
                    'cohort_patients' => $activeCohortPatients,
                    'paired_patients' => $activePairedPatients,
                    'distribution_chart' => $activeDistributionChart,
                ],
                'ltfu' => [
                    'title' => 'LTFU patients stage comparison',
                    'cohort_patients' => $ltfuCohortPatients,
                    'paired_patients' => $ltfuPairedPatients,
                    'distribution_chart' => $ltfuDistributionChart,
                ],
            ],
            'data_quality' => [
                ['metric' => 'Hypertension register cohort', 'value' => count($baselineByPatient)],
                ['metric' => 'Hypertension cohort with last record', 'value' => $hypertensiveWithLastRecord],
                ['metric' => 'Unavailable overall', 'value' => max(0, $cohortPatients - $pairedPatients)],
                ['metric' => 'Unavailable active', 'value' => max(0, $activeCohortPatients - $activePairedPatients)],
                ['metric' => 'Unavailable LTFU', 'value' => max(0, $ltfuCohortPatients - $ltfuPairedPatients)],
                ['metric' => 'Compared overall', 'value' => $pairedPatients],
                ['metric' => 'Compared active', 'value' => $activePairedPatients],
                ['metric' => 'Compared LTFU', 'value' => $ltfuPairedPatients],
                ['metric' => 'Excluded baseline missing', 'value' => $excludedBaselineMissing],
                ['metric' => 'Excluded baseline invalid', 'value' => $excludedBaselineInvalid],
                ['metric' => 'Excluded last record missing', 'value' => $excludedLatestMissing],
                ['metric' => 'Excluded last record invalid', 'value' => $excludedLatestInvalid],
            ],
            'invalid_examples' => [
                'baseline' => $baselineInvalidTop,
                'latest' => $latestInvalidTop,
                'last_record' => $latestInvalidTop,
            ],
        ];
    }

    private function extractBaselineStageFromRegister(array $row): array
    {
        $invalid = [];
        foreach (['third_bp', 'second_bp', 'first_bp'] as $field) {
            $raw = trim((string) ($row[$field] ?? ''));
            if ($raw === '' || $this->isUnknownValue($raw)) {
                continue;
            }
            $bp = $this->parseBpString($raw);
            if ($bp !== null) {
                $stage = $this->classifyBpStage((float) $bp['sbp'], (float) $bp['dbp']);
                if ($stage !== null) {
                    return ['status' => 'valid', 'stage' => $stage, 'invalid_values' => []];
                }
            }
            $invalid[$raw] = true;
        }

        $stageText = $this->normalizeCategory($row['staging_hypertension'] ?? null);
        $stageFromText = $this->parseBpStageText($stageText);
        if ($stageFromText !== null) {
            return ['status' => 'valid', 'stage' => $stageFromText, 'invalid_values' => []];
        }
        if ($stageText !== '' && !$this->isUnknownValue($stageText)) {
            $invalid[$stageText] = true;
        }

        if (!empty($invalid)) {
            return ['status' => 'invalid', 'stage' => null, 'invalid_values' => array_keys($invalid)];
        }
        return ['status' => 'missing', 'stage' => null, 'invalid_values' => []];
    }

    private function extractLatestStageFromFollowup(array $row): array
    {
        $raw = trim((string) ($row['bp_raw'] ?? ($row['own_clinic_bp'] ?? '')));
        if ($raw === '' || $this->isUnknownValue($raw)) {
            return ['status' => 'missing', 'stage' => null, 'invalid_values' => []];
        }

        $bp = $this->parseBpString($raw);
        if ($bp !== null) {
            $stage = $this->classifyBpStage((float) $bp['sbp'], (float) $bp['dbp']);
            if ($stage !== null) {
                return ['status' => 'valid', 'stage' => $stage, 'invalid_values' => []];
            }
        }

        return ['status' => 'invalid', 'stage' => null, 'invalid_values' => [$raw]];
    }

    private function buildLatestFollowupStatusByPatient(array $followups, int $graceDays = 84, ?int $observeTs = null): array
    {
        if (empty($followups)) {
            return [];
        }

        $observeTs = $observeTs ?? time();
        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $latestByPatient = [];

        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row[$idField] ?? null);
            if ($pid === null) {
                continue;
            }

            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            if ($visitTs === null) {
                continue;
            }

            $nextApptTs = $this->parseDateValue($row['next_appointment'] ?? null);
            $outcomeRaw = $this->extractFollowupOutcomeValue($row);
            $current = $latestByPatient[$pid] ?? null;
            if ($current === null || $visitTs > $current['visit_ts']) {
                $latestByPatient[$pid] = [
                    'visit_ts' => $visitTs,
                    'next_appt_ts' => $nextApptTs,
                    'outcome' => $outcomeRaw,
                    'gender' => $this->normalizeGender($row['gender'] ?? null),
                ];
                continue;
            }

            if ($visitTs === $current['visit_ts'] && $current['next_appt_ts'] === null && $nextApptTs !== null) {
                $latestByPatient[$pid]['next_appt_ts'] = $nextApptTs;
            }
            if ($visitTs === $current['visit_ts'] && $this->isUnknownValue($current['outcome'] ?? null) && !$this->isUnknownValue($outcomeRaw)) {
                $latestByPatient[$pid]['outcome'] = $outcomeRaw;
            }
            if ($visitTs === $current['visit_ts']) {
                $currentGender = $latestByPatient[$pid]['gender'] ?? 'Unknown';
                if ($currentGender === 'Unknown') {
                    $candidateGender = $this->normalizeGender($row['gender'] ?? null);
                    if ($candidateGender !== 'Unknown') {
                        $latestByPatient[$pid]['gender'] = $candidateGender;
                    }
                }
            }
        }

        $statusByPatient = [];
        foreach ($latestByPatient as $pid => $row) {
            $gender = $row['gender'] ?? 'Unknown';
            if (!in_array($gender, ['Male', 'Female'], true)) {
                $gender = 'Unknown';
            }

            $status = 'missing_next_appointment';
            if ($this->isExitedOutcome($row['outcome'] ?? null)) {
                $status = 'exited';
            } elseif (($row['next_appt_ts'] ?? null) === null) {
                $status = 'missing_next_appointment';
            } elseif (($row['next_appt_ts'] + ($graceDays * 86400)) < $observeTs) {
                $status = 'ltfu';
            } else {
                $status = 'active';
            }

            $statusByPatient[$pid] = [
                'status' => $status,
                'gender' => $gender,
                'visit_ts' => $row['visit_ts'] ?? null,
                'next_appt_ts' => $row['next_appt_ts'] ?? null,
                'outcome' => $row['outcome'] ?? null,
            ];
        }

        return $statusByPatient;
    }

    private function buildLtfuByAppointmentChart(array $followups, int $graceDays = 84, ?int $observeTs = null): array
    {
        if (empty($followups)) {
            return [
                'chart' => [],
                'gender_chart' => [],
                'summary' => [
                    'patients' => 0,
                    'observe_date' => null,
                    'grace_days' => $graceDays,
                ],
            ];
        }

        $observeTs = $observeTs ?? time();
        $statusByPatient = $this->buildLatestFollowupStatusByPatient($followups, $graceDays, $observeTs);

        $ltfu = 0;
        $active = 0;
        $exited = 0;
        $missingNextAppt = 0;
        $statusGenderCounts = [
            'active' => ['Male' => 0, 'Female' => 0, 'Unknown' => 0],
            'ltfu' => ['Male' => 0, 'Female' => 0, 'Unknown' => 0],
            'exited' => ['Male' => 0, 'Female' => 0, 'Unknown' => 0],
            'missing_next_appointment' => ['Male' => 0, 'Female' => 0, 'Unknown' => 0],
        ];

        foreach ($statusByPatient as $row) {
            $gender = $row['gender'] ?? 'Unknown';
            if (!in_array($gender, ['Male', 'Female'], true)) {
                $gender = 'Unknown';
            }

            if (($row['status'] ?? null) === 'exited') {
                $exited++;
                $statusGenderCounts['exited'][$gender]++;
                continue;
            }
            if (($row['status'] ?? null) === 'missing_next_appointment') {
                $missingNextAppt++;
                $statusGenderCounts['missing_next_appointment'][$gender]++;
                continue;
            }
            if (($row['status'] ?? null) === 'ltfu') {
                $ltfu++;
                $statusGenderCounts['ltfu'][$gender]++;
            } else {
                $active++;
                $statusGenderCounts['active'][$gender]++;
            }
        }

        return [
            'chart' => [
                ['title' => 'Active', 'value' => $active, 'color' => '#22c55e'],
                ['title' => 'LTFU', 'value' => $ltfu, 'color' => '#ef4444'],
                ['title' => 'Exited (Died/Tout)', 'value' => $exited, 'color' => '#6366f1'],
                ['title' => 'Missing next appointment', 'value' => $missingNextAppt, 'color' => '#f59e0b'],
            ],
            'gender_chart' => [
                'labels' => ['Active', 'LTFU', 'Exited (Died/Tout)', 'Missing next appointment'],
                'series' => [
                    [
                        'label' => 'Male',
                        'data' => [
                            $statusGenderCounts['active']['Male'],
                            $statusGenderCounts['ltfu']['Male'],
                            $statusGenderCounts['exited']['Male'],
                            $statusGenderCounts['missing_next_appointment']['Male'],
                        ],
                        'color' => '#3b82f6',
                    ],
                    [
                        'label' => 'Female',
                        'data' => [
                            $statusGenderCounts['active']['Female'],
                            $statusGenderCounts['ltfu']['Female'],
                            $statusGenderCounts['exited']['Female'],
                            $statusGenderCounts['missing_next_appointment']['Female'],
                        ],
                        'color' => '#ec4899',
                    ],
                    [
                        'label' => 'Unknown',
                        'data' => [
                            $statusGenderCounts['active']['Unknown'],
                            $statusGenderCounts['ltfu']['Unknown'],
                            $statusGenderCounts['exited']['Unknown'],
                            $statusGenderCounts['missing_next_appointment']['Unknown'],
                        ],
                        'color' => '#94a3b8',
                    ],
                ],
            ],
            'summary' => [
                'patients' => count($statusByPatient),
                'observe_date' => date('Y-m-d', $observeTs),
                'grace_days' => $graceDays,
                'active' => $active,
                'ltfu' => $ltfu,
                'exited' => $exited,
                'missing_next_appointment' => $missingNextAppt,
                'active_male' => $statusGenderCounts['active']['Male'],
                'active_female' => $statusGenderCounts['active']['Female'],
                'ltfu_male' => $statusGenderCounts['ltfu']['Male'],
                'ltfu_female' => $statusGenderCounts['ltfu']['Female'],
            ],
        ];
    }

    private function extractFollowupOutcomeValue(array $row)
    {
        foreach (['outcome', 'out_come', 'Out_come'] as $key) {
            if (array_key_exists($key, $row)) {
                return $row[$key];
            }
        }

        foreach ($row as $key => $value) {
            $normalized = strtolower((string) preg_replace('/[^a-z0-9]+/', '', (string) $key));
            if ($normalized === 'outcome') {
                return $value;
            }
        }

        return null;
    }

    private function isExitedOutcome($value): bool
    {
        $text = strtolower(trim((string) ($value ?? '')));
        if ($this->isUnknownValue($text)) {
            return false;
        }

        $normalized = preg_replace('/[^a-z0-9]+/', ' ', $text);
        $normalized = trim((string) $normalized);
        if ($normalized === '') {
            return false;
        }

        if ((bool) preg_match('/\b(died|dead|death)\b/', $normalized)) {
            return true;
        }
        if (str_contains($normalized, 'tout') || str_contains($normalized, 't out') || str_contains($normalized, 'transfer out')) {
            return true;
        }

        return false;
    }

    private function applyReportEndFlags(array $patients, array $followups, array $thresholds, ?string $endDate): array
    {
        if (empty($patients)) {
            return $patients;
        }

        $reportEndTs = $this->parseDateValue($endDate);
        if ($reportEndTs === null) {
            $bounds = $this->findDateBounds($followups, 'visit_date');
            $reportEndTs = $bounds['max'];
        }
        if ($reportEndTs === null) {
            $bounds = $this->findDateBounds($patients, 'visit_date');
            $reportEndTs = $bounds['max'];
        }
        if ($reportEndTs === null) {
            return $patients;
        }

        $activeDays = (int) ($thresholds['active_days'] ?? 90);
        $ltfuDays = (int) ($thresholds['ltfu_days'] ?? 90);
        $activeCutoff = $reportEndTs - ($activeDays * 86400);
        $ltfuCutoff = $reportEndTs - ($ltfuDays * 86400);

        $missedByPatient = [];
        if (!empty($followups)) {
            $followupIdField = $this->detectIdField($followups, ['patient_id', 'pid']);
            $appointments = [];
            foreach ($followups as $row) {
                $pid = $row[$followupIdField] ?? null;
                if ($pid === null || $pid === '') {
                    continue;
                }
                $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
                if ($visitTs === null) {
                    continue;
                }
                $appointments[$pid][] = [
                    'visit_ts' => $visitTs,
                    'next_appt_ts' => $this->parseDateValue($row['next_appointment'] ?? null),
                ];
            }
            foreach ($appointments as $pid => $visits) {
                usort($visits, function ($a, $b) {
                    return $a['visit_ts'] <=> $b['visit_ts'];
                });
                $missed = false;
                $count = count($visits);
                for ($i = 0; $i < $count; $i++) {
                    $nextApptTs = $visits[$i]['next_appt_ts'];
                    if ($nextApptTs === null) {
                        continue;
                    }
                    $nextVisitTs = $i + 1 < $count ? $visits[$i + 1]['visit_ts'] : null;
                    if ($nextVisitTs === null) {
                        $missed = true;
                        break;
                    }
                    if (date('Y-m-d', $nextVisitTs) !== date('Y-m-d', $nextApptTs)) {
                        $missed = true;
                        break;
                    }
                }
                $missedByPatient[$pid] = $missed;
            }
        }

        $patientIdField = $this->detectIdField($patients, ['patient_id', 'pid']);
        foreach ($patients as &$row) {
            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            $row['active_patient'] = $visitTs !== null && $visitTs >= $activeCutoff;
            $row['ltfu'] = $visitTs === null || $visitTs < $ltfuCutoff;

            $pid = $row[$patientIdField] ?? null;
            $row['missed_appointment'] = $row['active_patient'] && $pid !== null && isset($missedByPatient[$pid])
                ? $missedByPatient[$pid]
                : false;
        }
        unset($row);

        return $patients;
    }

    private function resolveReportEndDate(?string $selectedEnd, array $config, array $followups, array $patients): ?string
    {
        if (!empty($selectedEnd)) {
            return $selectedEnd;
        }
        $configEnd = $config['date_range']['end_date'] ?? null;
        if (!empty($configEnd)) {
            return $configEnd;
        }
        $bounds = $this->findDateBounds($followups, 'visit_date');
        if ($bounds['max'] !== null) {
            return date('Y-m-d', $bounds['max']);
        }
        $bounds = $this->findDateBounds($patients, 'visit_date');
        if ($bounds['max'] !== null) {
            return date('Y-m-d', $bounds['max']);
        }
        return null;
    }

    private function buildKpiCardsFromPatients(array $rows, int $activeDays): array
    {
        $total = count($rows);
        $active = 0;
        $bpDen = 0;
        $bpNum = 0;
        $dmDen = 0;
        $dmNum = 0;
        $ltfu = 0;

        foreach ($rows as $row) {
            if ($this->parseBool($row['active_patient'] ?? null)) {
                $active++;
            }
            if ($this->parseBool($row['bp_with_values'] ?? null)) {
                $bpDen++;
                if ($this->parseBool($row['bp_controlled'] ?? null)) {
                    $bpNum++;
                }
            }
            if ($this->parseBool($row['dm_with_values'] ?? null)) {
                $dmDen++;
                if ($this->parseBool($row['dm_controlled'] ?? null)) {
                    $dmNum++;
                }
            }
            if ($this->parseBool($row['ltfu'] ?? null)) {
                $ltfu++;
            }
        }

        $bpRate = $bpDen ? $bpNum / $bpDen : null;
        $dmRate = $dmDen ? $dmNum / $dmDen : null;
        $ltfuRate = $total ? $ltfu / $total : null;

        return [
            ['key' => 'patients', 'title' => 'Patients', 'value' => number_format($total)],
            ['key' => 'active_caseload', 'title' => 'Active caseload (last ' . $activeDays . 'd)', 'value' => number_format($active)],
            ['key' => 'bp_control', 'title' => 'BP control', 'value' => $bpRate !== null ? sprintf('%.1f%%', $bpRate * 100) : '-'],
            ['key' => 'dm_control', 'title' => 'DM control', 'value' => $dmRate !== null ? sprintf('%.1f%%', $dmRate * 100) : '-'],
            ['key' => 'ltfu', 'title' => 'LTFU', 'value' => $ltfuRate !== null ? sprintf('%.1f%%', $ltfuRate * 100) : '-'],
        ];
    }

    private function buildControlRateDetails(array $rows, array $thresholds): array
    {
        $bpTotal = count($rows);
        $bpDen = 0;
        $bpNum = 0;
        $dmDen = 0;
        $dmNum = 0;
        $dmTotal = $bpTotal;
        $bpExclusions = [
            'missing' => 0,
            'invalid_format' => 0,
            'out_of_range' => 0,
            'other' => 0,
        ];
        $invalidFormatValues = [];
        $dmExclusions = [
            'missing_test' => 0,
            'invalid_value' => 0,
            'other' => 0,
        ];
        $dmInvalidValues = [];

        foreach ($rows as $row) {
            if ($this->parseBool($row['bp_with_values'] ?? null)) {
                $bpDen++;
                if ($this->parseBool($row['bp_controlled'] ?? null)) {
                    $bpNum++;
                }
            } else {
                $bpRawText = trim((string) ($row['bp_raw'] ?? ''));
                $hasBpRaw = $bpRawText !== '' && !$this->isUnknownValue($bpRawText);
                $sbpRaw = $this->parseFloat($row['sbp_raw'] ?? null);
                $dbpRaw = $this->parseFloat($row['dbp_raw'] ?? null);
                $sbp = $this->parseFloat($row['sbp'] ?? null);
                $dbp = $this->parseFloat($row['dbp'] ?? null);

                if (!$hasBpRaw) {
                    $bpExclusions['missing']++;
                } elseif ($sbpRaw === null || $dbpRaw === null) {
                    $bpExclusions['invalid_format']++;
                    if ($hasBpRaw) {
                        if (!isset($invalidFormatValues[$bpRawText])) {
                            $invalidFormatValues[$bpRawText] = 0;
                        }
                        $invalidFormatValues[$bpRawText]++;
                    }
                } elseif ($sbp === null || $dbp === null) {
                    $bpExclusions['out_of_range']++;
                } else {
                    $bpExclusions['other']++;
                }
            }
            if ($this->parseBool($row['dm_with_values'] ?? null)) {
                $dmDen++;
                if ($this->parseBool($row['dm_controlled'] ?? null)) {
                    $dmNum++;
                }
            } else {
                $rawFields = [
                    'hba1c' => $row['hba1c'] ?? null,
                    '2hpp' => $row['t2hpp'] ?? null,
                    'fbs' => $row['fbs'] ?? null,
                    'rbs' => $row['rbs_result'] ?? null,
                ];
                $hasAnyRaw = false;
                $hasInvalid = false;
                foreach ($rawFields as $label => $raw) {
                    $text = trim((string) ($raw ?? ''));
                    if ($text === '' || $this->isUnknownValue($text)) {
                        continue;
                    }
                    $hasAnyRaw = true;
                    if ($this->parseFloat($raw) === null) {
                        $hasInvalid = true;
                        $key = $label . ': ' . $text;
                        if (!isset($dmInvalidValues[$key])) {
                            $dmInvalidValues[$key] = 0;
                        }
                        $dmInvalidValues[$key]++;
                    }
                }

                if (!$hasAnyRaw) {
                    $dmExclusions['missing_test']++;
                } elseif ($hasInvalid) {
                    $dmExclusions['invalid_value']++;
                } else {
                    $dmExclusions['other']++;
                }
            }
        }

        arsort($invalidFormatValues);
        $invalidFormatExamples = [];
        foreach (array_slice($invalidFormatValues, 0, 5, true) as $label => $count) {
            $invalidFormatExamples[] = ['label' => $label, 'count' => $count];
        }

        arsort($dmInvalidValues);
        $dmInvalidExamples = [];
        foreach (array_slice($dmInvalidValues, 0, 5, true) as $label => $count) {
            $dmInvalidExamples[] = ['label' => $label, 'count' => $count];
        }

        return [
            'bp_control' => [
                'numerator' => $bpNum,
                'denominator' => $bpDen,
                'total_patients' => $bpTotal,
                'excluded' => $bpTotal - $bpDen,
                'excluded_groups' => $bpExclusions,
                'invalid_format_examples' => $invalidFormatExamples,
                'sbp_threshold' => (float) ($thresholds['bp_control_sbp'] ?? 140),
                'dbp_threshold' => (float) ($thresholds['bp_control_dbp'] ?? 90),
            ],
            'dm_control' => [
                'numerator' => $dmNum,
                'denominator' => $dmDen,
                'total_patients' => $dmTotal,
                'excluded' => $dmTotal - $dmDen,
                'excluded_groups' => $dmExclusions,
                'invalid_value_examples' => $dmInvalidExamples,
                'hba1c_threshold' => (float) ($thresholds['hba1c'] ?? 7.0),
                't2hpp_threshold' => (float) ($thresholds['twopp'] ?? 180),
                'fbs_threshold' => (float) ($thresholds['fbs'] ?? 126),
                'rbs_threshold' => (float) ($thresholds['rbs'] ?? 200),
            ],
        ];
    }

    private function buildSummaryRowsFromPatients(array $rows, int $activeDays): array
    {
        $total = count($rows);
        $active = 0;
        $bpDen = 0;
        $bpNum = 0;
        $dmDen = 0;
        $dmNum = 0;
        $ltfu = 0;

        foreach ($rows as $row) {
            if ($this->parseBool($row['active_patient'] ?? null)) {
                $active++;
            }
            if ($this->parseBool($row['bp_with_values'] ?? null)) {
                $bpDen++;
                if ($this->parseBool($row['bp_controlled'] ?? null)) {
                    $bpNum++;
                }
            }
            if ($this->parseBool($row['dm_with_values'] ?? null)) {
                $dmDen++;
                if ($this->parseBool($row['dm_controlled'] ?? null)) {
                    $dmNum++;
                }
            }
            if ($this->parseBool($row['ltfu'] ?? null)) {
                $ltfu++;
            }
        }

        $bpRate = $bpDen ? $bpNum / $bpDen : null;
        $dmRate = $dmDen ? $dmNum / $dmDen : null;
        $ltfuRate = $total ? $ltfu / $total : null;
        $activeRate = $total ? $active / $total : null;

        return [
            ['metric' => 'BP control rate', 'value' => $bpRate !== null ? sprintf('%.1f%%', $bpRate * 100) : '-'],
            ['metric' => 'DM control rate', 'value' => $dmRate !== null ? sprintf('%.1f%%', $dmRate * 100) : '-'],
            ['metric' => 'LTFU rate', 'value' => $ltfuRate !== null ? sprintf('%.1f%%', $ltfuRate * 100) : '-'],
            ['metric' => 'Active caseload (last ' . $activeDays . 'd)', 'value' => $activeRate !== null ? sprintf('%.1f%%', $activeRate * 100) : '-'],
        ];
    }

    private function buildAgeDistribution(array $rows): array
    {
        $minAge = 1;
        $maxAge = 100;
        $binSize = 5;

        $bins = [];
        for ($start = $minAge; $start <= $maxAge; $start += $binSize) {
            $end = min($maxAge, $start + $binSize - 1);
            $label = $start . '-' . $end;
            $bins[$label] = 0;
        }

        foreach ($rows as $row) {
            $value = $this->parseFloat($row['visit_age'] ?? ($row['visit_Age'] ?? null));
            if ($value === null) {
                continue;
            }

            $age = (int) floor($value);
            if ($age < $minAge || $age > $maxAge) {
                continue;
            }

            $offset = $age - $minAge;
            $bucketStart = $minAge + (int) floor($offset / $binSize) * $binSize;
            $bucketEnd = min($maxAge, $bucketStart + $binSize - 1);
            $label = $bucketStart . '-' . $bucketEnd;
            if (isset($bins[$label])) {
                $bins[$label]++;
            }
        }

        $result = [];
        foreach ($bins as $label => $count) {
            $result[] = ['title' => $label, 'value' => $count];
        }

        return $result;
    }

    private function buildDiagnosisTrendFromRegisters(array $rows, string $granularity = 'yearly', ?int $trendYear = null): array
    {
        $granularity = strtolower(trim($granularity));
        if (!in_array($granularity, ['monthly', 'yearly'], true)) {
            $granularity = 'yearly';
        }

        $selectedYear = null;
        if ($trendYear !== null && $trendYear >= 1900 && $trendYear <= 2100) {
            $selectedYear = $trendYear;
        }
        if ($selectedYear === null) {
            $selectedYear = (int) date('Y');
        }

        $segmentKeys = [
            'htn_only_male',
            'htn_only_female',
            'dm_only_male',
            'dm_only_female',
            'both_male',
            'both_female',
            'no_diag_male',
            'no_diag_female',
        ];

        if (empty($rows)) {
            return [
                'labels' => [],
                'series' => [],
                'gender_series' => [],
                'granularity' => $granularity,
                'trend_year' => $selectedYear,
            ];
        }

        $byPeriod = [];
        foreach ($rows as $row) {
            $regTs = $this->parseDateValue($row['reg_date'] ?? null);
            if ($regTs === null) {
                continue;
            }

            if ($granularity === 'monthly' && (int) date('Y', $regTs) !== $selectedYear) {
                continue;
            }

            $periodKey = $granularity === 'yearly'
                ? date('Y-01-01', $regTs)
                : date('Y-m-01', $regTs);

            if (!isset($byPeriod[$periodKey])) {
                $byPeriod[$periodKey] = [
                    'cohort' => 0,
                    'hypertension' => 0,
                    'diabetes' => 0,
                    'both' => 0,
                    'htn_only_male' => 0,
                    'htn_only_female' => 0,
                    'dm_only_male' => 0,
                    'dm_only_female' => 0,
                    'both_male' => 0,
                    'both_female' => 0,
                    'no_diag_male' => 0,
                    'no_diag_female' => 0,
                ];
            }

            $byPeriod[$periodKey]['cohort']++;
            $hasHtn = $this->hasNewKnownDiagnosis(
                $row['first_hypertension'] ?? ($row['1stHypertension'] ?? null)
            );
            $hasDm = $this->hasNewKnownDiagnosis(
                $row['second_hypertension'] ?? ($row['2nd_Hypertension'] ?? ($row['2nd_hypertension'] ?? null))
            );

            if ($hasHtn) {
                $byPeriod[$periodKey]['hypertension']++;
            }
            if ($hasDm) {
                $byPeriod[$periodKey]['diabetes']++;
            }
            if ($hasHtn && $hasDm) {
                $byPeriod[$periodKey]['both']++;
            }

            $diagKey = 'no_diag';
            if ($hasHtn && $hasDm) {
                $diagKey = 'both';
            } elseif ($hasHtn) {
                $diagKey = 'htn_only';
            } elseif ($hasDm) {
                $diagKey = 'dm_only';
            }

            $genderLabel = $this->normalizeGender($row['gender'] ?? null);
            $genderKey = null;
            if ($genderLabel === 'Male') {
                $genderKey = 'male';
            } elseif ($genderLabel === 'Female') {
                $genderKey = 'female';
            }

            if ($genderKey !== null) {
                $segmentKey = $diagKey . '_' . $genderKey;
                if (isset($byPeriod[$periodKey][$segmentKey])) {
                    $byPeriod[$periodKey][$segmentKey]++;
                }
            }
        }

        if ($granularity === 'monthly') {
            $start = Carbon::create($selectedYear, 1, 1);
            $end = Carbon::create($selectedYear, 12, 1);
        } else {
            if (empty($byPeriod)) {
                return [
                    'labels' => [],
                    'series' => [],
                    'gender_series' => [],
                    'granularity' => $granularity,
                    'trend_year' => $selectedYear,
                ];
            }
            ksort($byPeriod);
            $periodKeys = array_keys($byPeriod);
            $start = Carbon::parse($periodKeys[0]);
            $end = Carbon::parse($periodKeys[count($periodKeys) - 1]);
        }

        $cursor = $start->copy();
        while ($cursor->lte($end)) {
            $key = $granularity === 'yearly'
                ? $cursor->format('Y-01-01')
                : $cursor->format('Y-m-01');
            if (!isset($byPeriod[$key])) {
                $byPeriod[$key] = [
                    'cohort' => 0,
                    'hypertension' => 0,
                    'diabetes' => 0,
                    'both' => 0,
                    'htn_only_male' => 0,
                    'htn_only_female' => 0,
                    'dm_only_male' => 0,
                    'dm_only_female' => 0,
                    'both_male' => 0,
                    'both_female' => 0,
                    'no_diag_male' => 0,
                    'no_diag_female' => 0,
                ];
            }
            if ($granularity === 'yearly') {
                $cursor->addYear();
            } else {
                $cursor->addMonth();
            }
        }
        ksort($byPeriod);

        $labels = [];
        foreach (array_keys($byPeriod) as $periodKey) {
            $labels[] = $granularity === 'yearly'
                ? Carbon::parse($periodKey)->format('Y')
                : Carbon::parse($periodKey)->format('M');
        }

        $cohort = [];
        $hypertension = [];
        $diabetes = [];
        $both = [];
        $segmentSeriesData = [];
        foreach ($segmentKeys as $segmentKey) {
            $segmentSeriesData[$segmentKey] = [];
        }
        foreach (array_keys($byPeriod) as $periodKey) {
            $cohort[] = $byPeriod[$periodKey]['cohort'];
            $hypertension[] = $byPeriod[$periodKey]['hypertension'];
            $diabetes[] = $byPeriod[$periodKey]['diabetes'];
            $both[] = $byPeriod[$periodKey]['both'];
            foreach ($segmentKeys as $segmentKey) {
                $segmentSeriesData[$segmentKey][] = (int) ($byPeriod[$periodKey][$segmentKey] ?? 0);
            }
        }

        $genderSeries = [
            ['key' => 'htn_only_male', 'label' => 'HTN only - Male', 'data' => $segmentSeriesData['htn_only_male'], 'color' => '#ef4444'],
            ['key' => 'htn_only_female', 'label' => 'HTN only - Female', 'data' => $segmentSeriesData['htn_only_female'], 'color' => '#fca5a5'],
            ['key' => 'dm_only_male', 'label' => 'DM only - Male', 'data' => $segmentSeriesData['dm_only_male'], 'color' => '#22c55e'],
            ['key' => 'dm_only_female', 'label' => 'DM only - Female', 'data' => $segmentSeriesData['dm_only_female'], 'color' => '#86efac'],
            ['key' => 'both_male', 'label' => 'Both - Male', 'data' => $segmentSeriesData['both_male'], 'color' => '#f59e0b'],
            ['key' => 'both_female', 'label' => 'Both - Female', 'data' => $segmentSeriesData['both_female'], 'color' => '#fcd34d'],
            ['key' => 'no_diag_male', 'label' => 'No diagnosis - Male', 'data' => $segmentSeriesData['no_diag_male'], 'color' => '#334155'],
            ['key' => 'no_diag_female', 'label' => 'No diagnosis - Female', 'data' => $segmentSeriesData['no_diag_female'], 'color' => '#94a3b8'],
        ];

        return [
            'labels' => $labels,
            'granularity' => $granularity,
            'trend_year' => $selectedYear,
            'series' => [
                ['label' => 'Total cohort', 'data' => $cohort, 'color' => '#0ea5e9'],
                ['label' => 'Hypertension (new+known)', 'data' => $hypertension, 'color' => '#ef4444'],
                ['label' => 'Diabetes (new+known)', 'data' => $diabetes, 'color' => '#22c55e'],
                ['label' => 'Both diagnoses', 'data' => $both, 'color' => '#f59e0b'],
            ],
            'gender_series' => $genderSeries,
        ];
    }

    private function buildFollowupCumulativeTrendByYear(array $diagnosisTrend): array
    {
        $labels = array_values(array_map(static function ($label) {
            return (string) $label;
        }, (array) ($diagnosisTrend['labels'] ?? [])));

        $series = array_values(array_filter((array) ($diagnosisTrend['series'] ?? []), static function ($row) {
            return is_array($row);
        }));

        if (empty($labels) || empty($series)) {
            return ['labels' => [], 'series' => [], 'gender_series' => [], 'yearly' => [], 'yearly_gender' => []];
        }

        $diagBuckets = ['hypertension', 'diabetes', 'both', 'other'];
        $genderSegmentKeys = [
            'htn_only_male',
            'htn_only_female',
            'dm_only_male',
            'dm_only_female',
            'both_male',
            'both_female',
            'no_diag_male',
            'no_diag_female',
        ];

        $findSeriesData = static function (array $seriesRows, string $needle): array {
            $needle = strtolower($needle);
            foreach ($seriesRows as $seriesRow) {
                $label = strtolower(trim((string) ($seriesRow['label'] ?? '')));
                if ($label !== '' && strpos($label, $needle) !== false) {
                    return array_map(static function ($value) {
                        return (int) round((float) $value);
                    }, (array) ($seriesRow['data'] ?? []));
                }
            }

            return [];
        };

        $cohortSeries = $findSeriesData($series, 'total cohort');
        $hypertensionSeries = $findSeriesData($series, 'hypertension');
        $diabetesSeries = $findSeriesData($series, 'diabetes');
        $bothSeries = $findSeriesData($series, 'both');

        $yearly = [
            'hypertension' => [],
            'diabetes' => [],
            'both' => [],
            'other' => [],
            'total' => [],
        ];
        $cumulative = [
            'hypertension' => [],
            'diabetes' => [],
            'both' => [],
            'other' => [],
        ];
        $running = [
            'hypertension' => 0,
            'diabetes' => 0,
            'both' => 0,
            'other' => 0,
        ];

        $labelCount = count($labels);
        for ($idx = 0; $idx < $labelCount; $idx++) {
            $cohort = (int) ($cohortSeries[$idx] ?? 0);
            $both = (int) ($bothSeries[$idx] ?? 0);
            $htnOnly = max(0, ((int) ($hypertensionSeries[$idx] ?? 0)) - $both);
            $dmOnly = max(0, ((int) ($diabetesSeries[$idx] ?? 0)) - $both);
            $noDiagnosis = max(0, $cohort - $htnOnly - $dmOnly - $both);

            $yearly['hypertension'][] = $htnOnly;
            $yearly['diabetes'][] = $dmOnly;
            $yearly['both'][] = $both;
            $yearly['other'][] = $noDiagnosis;
            $yearly['total'][] = $cohort;

            foreach ($diagBuckets as $bucket) {
                $running[$bucket] += (int) end($yearly[$bucket]);
                $cumulative[$bucket][] = $running[$bucket];
            }
        }

        $genderSeries = array_values(array_filter((array) ($diagnosisTrend['gender_series'] ?? []), static function ($row) {
            return is_array($row);
        }));
        $findGenderSeriesData = static function (array $seriesRows, string $key): array {
            foreach ($seriesRows as $seriesRow) {
                if ((string) ($seriesRow['key'] ?? '') === $key) {
                    return array_map(static function ($value) {
                        return (int) round((float) $value);
                    }, (array) ($seriesRow['data'] ?? []));
                }
            }

            return [];
        };

        $yearlyGender = [
            'htn_only_male' => [],
            'htn_only_female' => [],
            'dm_only_male' => [],
            'dm_only_female' => [],
            'both_male' => [],
            'both_female' => [],
            'no_diag_male' => [],
            'no_diag_female' => [],
            'male_total' => [],
            'female_total' => [],
            'total' => [],
        ];
        $cumulativeGender = [];
        foreach ($genderSegmentKeys as $segmentKey) {
            $cumulativeGender[$segmentKey] = [];
        }
        $runningGender = [];
        foreach ($genderSegmentKeys as $segmentKey) {
            $runningGender[$segmentKey] = 0;
        }

        for ($idx = 0; $idx < $labelCount; $idx++) {
            $maleTotal = 0;
            $femaleTotal = 0;
            foreach ($genderSegmentKeys as $segmentKey) {
                $segmentData = $findGenderSeriesData($genderSeries, $segmentKey);
                $value = (int) ($segmentData[$idx] ?? 0);
                $yearlyGender[$segmentKey][] = $value;
                $runningGender[$segmentKey] += $value;
                $cumulativeGender[$segmentKey][] = $runningGender[$segmentKey];
                if (str_ends_with($segmentKey, '_male')) {
                    $maleTotal += $value;
                }
                if (str_ends_with($segmentKey, '_female')) {
                    $femaleTotal += $value;
                }
            }
            $yearlyGender['male_total'][] = $maleTotal;
            $yearlyGender['female_total'][] = $femaleTotal;
            $yearlyGender['total'][] = $maleTotal + $femaleTotal;
        }

        return [
            'labels' => $labels,
            'granularity' => 'yearly',
            'series' => [
                ['key' => 'hypertension', 'label' => 'Cumulative HTN only cohort', 'data' => $cumulative['hypertension'], 'color' => '#ef4444'],
                ['key' => 'diabetes', 'label' => 'Cumulative DM only cohort', 'data' => $cumulative['diabetes'], 'color' => '#22c55e'],
                ['key' => 'both', 'label' => 'Cumulative both diagnoses cohort', 'data' => $cumulative['both'], 'color' => '#f59e0b'],
                ['key' => 'other', 'label' => 'Cumulative no diagnosis cohort', 'data' => $cumulative['other'], 'color' => '#ffffff'],
            ],
            'gender_series' => [
                ['key' => 'htn_only_male', 'label' => 'HTN only - Male', 'data' => $cumulativeGender['htn_only_male'], 'color' => '#ef4444'],
                ['key' => 'htn_only_female', 'label' => 'HTN only - Female', 'data' => $cumulativeGender['htn_only_female'], 'color' => '#fca5a5'],
                ['key' => 'dm_only_male', 'label' => 'DM only - Male', 'data' => $cumulativeGender['dm_only_male'], 'color' => '#22c55e'],
                ['key' => 'dm_only_female', 'label' => 'DM only - Female', 'data' => $cumulativeGender['dm_only_female'], 'color' => '#86efac'],
                ['key' => 'both_male', 'label' => 'Both - Male', 'data' => $cumulativeGender['both_male'], 'color' => '#f59e0b'],
                ['key' => 'both_female', 'label' => 'Both - Female', 'data' => $cumulativeGender['both_female'], 'color' => '#fcd34d'],
                ['key' => 'no_diag_male', 'label' => 'No diagnosis - Male', 'data' => $cumulativeGender['no_diag_male'], 'color' => '#334155'],
                ['key' => 'no_diag_female', 'label' => 'No diagnosis - Female', 'data' => $cumulativeGender['no_diag_female'], 'color' => '#94a3b8'],
            ],
            'yearly' => $yearly,
            'yearly_gender' => $yearlyGender,
        ];
    }

    private function extractFollowupDiagnosisValue(array $row)
    {
        foreach (['ncd_diagnosis', 'NCD_Diagnosis'] as $key) {
            if (array_key_exists($key, $row)) {
                return $row[$key];
            }
        }

        foreach ($row as $key => $value) {
            $normalized = strtolower((string) preg_replace('/[^a-z0-9]+/', '', (string) $key));
            if ($normalized === 'ncddiagnosis') {
                return $value;
            }
        }

        return null;
    }

    private function categorizeFollowupDiagnosis($value): string
    {
        $text = strtolower(trim((string) ($value ?? '')));
        if ($this->isUnknownValue($text)) {
            return 'other';
        }
        $normalized = preg_replace('/[^a-z0-9]+/', ' ', $text);
        $normalized = trim((string) $normalized);

        $hasHtn = (bool) preg_match('/\b(hypertension|htn)\b/', $normalized);
        $hasDm = (bool) preg_match('/\b(diabetes|dm)\b/', $normalized);

        if ($hasHtn && $hasDm) {
            return 'both';
        }
        if ($hasHtn) {
            return 'hypertension';
        }
        if ($hasDm) {
            return 'diabetes';
        }
        if (str_contains($normalized, 'both')) {
            return 'both';
        }
        return 'other';
    }

    private function hasNewKnownDiagnosis($value): bool
    {
        $text = strtolower(trim((string) ($value ?? '')));
        if ($this->isUnknownValue($text)) {
            return false;
        }
        $normalized = preg_replace('/[^a-z]+/', ' ', $text);
        $normalized = trim((string) $normalized);
        return (bool) preg_match('/\b(new|known|know)\b/', $normalized);
    }

    private function isNewDiagnosisLabel($value): bool
    {
        $text = strtolower(trim((string) ($value ?? '')));
        if ($this->isUnknownValue($text)) {
            return false;
        }
        $normalized = preg_replace('/[^a-z]+/', ' ', $text);
        $normalized = trim((string) $normalized);
        return (bool) preg_match('/\bnew\b/', $normalized);
    }

    private function buildMonthlySeriesFromRows(array $registers, array $followups): array
    {
        $regCounts = [];
        foreach ($registers as $row) {
            $ts = $this->parseDateValue($row['reg_date'] ?? null);
            if ($ts === null) {
                continue;
            }
            $month = date('Y-m-01', $ts);
            $regCounts[$month] = ($regCounts[$month] ?? 0) + 1;
        }

        $followupCounts = [];
        foreach ($followups as $row) {
            $ts = $this->parseDateValue($row['visit_date'] ?? null);
            if ($ts === null) {
                continue;
            }
            $month = date('Y-m-01', $ts);
            $followupCounts[$month] = ($followupCounts[$month] ?? 0) + 1;
        }

        $labels = array_unique(array_merge(array_keys($regCounts), array_keys($followupCounts)));
        sort($labels);
        $newRegs = [];
        $followupsSeries = [];
        foreach ($labels as $label) {
            $newRegs[] = $regCounts[$label] ?? 0;
            $followupsSeries[] = $followupCounts[$label] ?? 0;
        }

        return [
            'labels' => $labels,
            'series' => [
                ['label' => 'New registrations', 'data' => $newRegs, 'color' => '#0ea5e9'],
                ['label' => 'Follow-up visits', 'data' => $followupsSeries, 'color' => '#22c55e'],
            ],
        ];
    }

    private function buildBpTrendFromFollowups(array $rows, array $thresholds): array
    {
        if (empty($rows)) {
            return ['labels' => [], 'series' => []];
        }
        $sbpThreshold = (float) ($thresholds['bp_control_sbp'] ?? 140);
        $dbpThreshold = (float) ($thresholds['bp_control_dbp'] ?? 90);

        $latestByMonth = [];
        foreach ($rows as $row) {
            $pid = $row['patient_id'] ?? $row['pid'] ?? null;
            $ts = $this->parseDateValue($row['visit_date'] ?? null);
            if (!$pid || $ts === null) {
                continue;
            }
            $month = date('Y-m-01', $ts);
            $key = $pid . '|' . $month;
            if (!isset($latestByMonth[$key]) || $ts > $latestByMonth[$key]['ts']) {
                $latestByMonth[$key] = ['ts' => $ts, 'month' => $month, 'row' => $row];
            }
        }

        $stats = [];
        foreach ($latestByMonth as $entry) {
            $month = $entry['month'];
            if (!isset($stats[$month])) {
                $stats[$month] = ['with' => 0, 'controlled' => 0];
            }
            $sbp = $this->parseFloat($entry['row']['sbp'] ?? null);
            $dbp = $this->parseFloat($entry['row']['dbp'] ?? null);
            if ($sbp !== null && $dbp !== null) {
                $stats[$month]['with']++;
                if ($sbp < $sbpThreshold && $dbp < $dbpThreshold) {
                    $stats[$month]['controlled']++;
                }
            }
        }

        ksort($stats);
        $labels = array_keys($stats);
        $values = [];
        foreach ($stats as $vals) {
            $rate = $vals['with'] ? ($vals['controlled'] / $vals['with']) * 100 : 0;
            $values[] = round($rate, 1);
        }

        return [
            'labels' => $labels,
            'series' => [
                ['label' => 'BP control rate (%)', 'data' => $values, 'color' => '#f59e0b'],
            ],
        ];
    }

    private function buildDmTrendFromFollowups(array $rows, array $thresholds): array
    {
        if (empty($rows)) {
            return ['labels' => [], 'series' => []];
        }
        $hba1cThresh = (float) ($thresholds['hba1c'] ?? 7.0);
        $twoppThresh = (float) ($thresholds['twopp'] ?? 180);
        $fbsThresh = (float) ($thresholds['fbs'] ?? 126);
        $rbsThresh = (float) ($thresholds['rbs'] ?? 200);

        $latestByMonth = [];
        foreach ($rows as $row) {
            $pid = $row['patient_id'] ?? $row['pid'] ?? null;
            $ts = $this->parseDateValue($row['visit_date'] ?? null);
            if (!$pid || $ts === null) {
                continue;
            }
            $month = date('Y-m-01', $ts);
            $key = $pid . '|' . $month;
            if (!isset($latestByMonth[$key]) || $ts > $latestByMonth[$key]['ts']) {
                $latestByMonth[$key] = ['ts' => $ts, 'month' => $month, 'row' => $row];
            }
        }

        $stats = [];
        foreach ($latestByMonth as $entry) {
            $month = $entry['month'];
            if (!isset($stats[$month])) {
                $stats[$month] = ['with' => 0, 'controlled' => 0];
            }
            $row = $entry['row'];
            $control = $this->computeDmControlFromRow($row, $hba1cThresh, $twoppThresh, $fbsThresh, $rbsThresh);
            if ($control !== null) {
                $stats[$month]['with']++;
                if ($control) {
                    $stats[$month]['controlled']++;
                }
            }
        }

        ksort($stats);
        $labels = array_keys($stats);
        $values = [];
        foreach ($stats as $vals) {
            $rate = $vals['with'] ? ($vals['controlled'] / $vals['with']) * 100 : 0;
            $values[] = round($rate, 1);
        }

        return [
            'labels' => $labels,
            'series' => [
                ['label' => 'Diabetes control rate (%)', 'data' => $values, 'color' => '#8b5cf6'],
            ],
        ];
    }

    private function computeDmControlFromRow(array $row, float $hba1cThresh, float $twoppThresh, float $fbsThresh, float $rbsThresh): ?bool
    {
        $hba1c = $this->parseFloat($row['hba1c'] ?? null);
        if ($hba1c !== null) {
            return $hba1c < $hba1cThresh;
        }
        $t2hpp = $this->parseFloat($row['t2hpp'] ?? null);
        if ($t2hpp !== null) {
            return $t2hpp < $twoppThresh;
        }
        $fbs = $this->parseFloat($row['fbs'] ?? null);
        if ($fbs !== null) {
            return $fbs < $fbsThresh;
        }
        $rbs = $this->parseFloat($row['rbs_result'] ?? null);
        if ($rbs !== null) {
            return $rbs < $rbsThresh;
        }
        return null;
    }

    private function buildSbpDistribution(array $rows): array
    {
        $labels = [
            'Normal (<140/<90)',
            'Stage 1 (140/90-159/99)',
            'Stage 2 (160/100-179/109)',
            'Stage 3 (>=180/110)',
        ];
        $counts = array_fill_keys($labels, 0);

        foreach ($rows as $row) {
            $sbp = $this->parseFloat($row['sbp'] ?? null);
            $dbp = $this->parseFloat($row['dbp'] ?? null);
            if ($sbp === null || $dbp === null) {
                continue;
            }
            if ($sbp >= 180 || $dbp >= 110) {
                $counts['Stage 3 (>=180/110)']++;
            } elseif ($sbp >= 160 || $dbp >= 100) {
                $counts['Stage 2 (160/100-179/109)']++;
            } elseif ($sbp >= 140 || $dbp >= 90) {
                $counts['Stage 1 (140/90-159/99)']++;
            } else {
                $counts['Normal (<140/<90)']++;
            }
        }

        $result = [];
        foreach ($labels as $label) {
            $result[] = ['title' => $label, 'value' => $counts[$label] ?? 0];
        }
        return $result;
    }

    private function buildQualitySummaryFromFollowups(array $rows, ?string $endDate, array $thresholds): array
    {
        if (empty($rows)) {
            return [];
        }
        $reportEndTs = $this->parseDateValue($endDate);
        if ($reportEndTs === null) {
            $bounds = $this->findDateBounds($rows, 'visit_date');
            $reportEndTs = $bounds['max'];
        }
        if ($reportEndTs === null) {
            return [];
        }

        $reportEnd = new \DateTimeImmutable(date('Y-m-d', $reportEndTs));
        $hba1cMonths = (int) ($thresholds['hba1c_lookback_months'] ?? 6);
        $kidneyMonths = (int) ($thresholds['kidney_lookback_months'] ?? 12);
        $cvdMonths = (int) ($thresholds['cvd_risk_lookback_months'] ?? 12);

        $hbCutoff = $reportEnd->modify('-' . $hba1cMonths . ' months')->getTimestamp();
        $kidneyCutoff = $reportEnd->modify('-' . $kidneyMonths . ' months')->getTimestamp();
        $cvdCutoff = $reportEnd->modify('-' . $cvdMonths . ' months')->getTimestamp();

        $patientFlags = [];
        foreach ($rows as $row) {
            $pid = $row['patient_id'] ?? $row['pid'] ?? null;
            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            if (!$pid || $visitTs === null) {
                continue;
            }
            if (!isset($patientFlags[$pid])) {
                $patientFlags[$pid] = [
                    'hba1c_recent' => false,
                    'creatinine_recent' => false,
                    'crcl_recent' => false,
                    'uring_ac_ratio_recent' => false,
                    'cvd_risk_recent' => false,
                ];
            }
            if ($visitTs >= $hbCutoff && $this->hasValue($row['hba1c'] ?? null)) {
                $patientFlags[$pid]['hba1c_recent'] = true;
            }
            if ($visitTs >= $kidneyCutoff && $this->hasValue($row['creatinine'] ?? null)) {
                $patientFlags[$pid]['creatinine_recent'] = true;
            }
            if ($visitTs >= $kidneyCutoff && $this->hasValue($row['crcl'] ?? null)) {
                $patientFlags[$pid]['crcl_recent'] = true;
            }
            if ($visitTs >= $kidneyCutoff && $this->hasValue($row['uring_ac_ratio'] ?? null)) {
                $patientFlags[$pid]['uring_ac_ratio_recent'] = true;
            }
            if ($visitTs >= $cvdCutoff && $this->hasValue($row['cvd_risk'] ?? null)) {
                $patientFlags[$pid]['cvd_risk_recent'] = true;
            }
        }

        $totalPatients = count($patientFlags);
        if ($totalPatients === 0) {
            return [];
        }

        $counts = [
            'hba1c_recent' => 0,
            'creatinine_recent' => 0,
            'crcl_recent' => 0,
            'uring_ac_ratio_recent' => 0,
            'cvd_risk_recent' => 0,
        ];
        foreach ($patientFlags as $flags) {
            foreach ($counts as $key => $value) {
                if (!empty($flags[$key])) {
                    $counts[$key]++;
                }
            }
        }

        $labels = [
            'hba1c_recent' => 'HbA1c last 6m',
            'creatinine_recent' => 'Creatinine last 12m',
            'crcl_recent' => 'CRCL last 12m',
            'uring_ac_ratio_recent' => 'Urine A/C ratio last 12m',
            'cvd_risk_recent' => 'CVD risk last 12m',
        ];

        $summary = [];
        foreach ($counts as $key => $count) {
            $summary[] = [
                'title' => $labels[$key] ?? $key,
                'value' => round(($count / $totalPatients) * 100, 1),
            ];
        }
        return $summary;
    }

    private function buildContinuityCounts(array $rows): array
    {
        $late = 0;
        $missed = 0;
        $ltfu = 0;
        foreach ($rows as $row) {
            if ($this->parseBool($row['late_visit_flag'] ?? null)) {
                $late++;
            }
            if ($this->parseBool($row['missed_appointment'] ?? null)) {
                $missed++;
            }
            if ($this->parseBool($row['ltfu'] ?? null)) {
                $ltfu++;
            }
        }
        return [
            ['title' => 'Late visits', 'value' => $late],
            ['title' => 'Missed appointments', 'value' => $missed],
            ['title' => 'LTFU', 'value' => $ltfu],
        ];
    }

    private function buildContinuityRateSummary(array $rows, array $missedStats): array
    {
        $totalPatients = count($rows);
        $late = 0;
        $ltfu = 0;
        foreach ($rows as $row) {
            if ($this->parseBool($row['late_visit_flag'] ?? null)) {
                $late++;
            }
            if ($this->parseBool($row['ltfu'] ?? null)) {
                $ltfu++;
            }
        }

        $lateRate = $totalPatients ? round(($late / $totalPatients) * 100, 1) : 0;
        $ltfuRate = $totalPatients ? round(($ltfu / $totalPatients) * 100, 1) : 0;
        $missedRate = isset($missedStats['rate']) ? (float) $missedStats['rate'] : 0;

        return [
            ['title' => 'Missed appointments (%)', 'value' => $missedRate],
            ['title' => 'Late visits (%)', 'value' => $lateRate],
            ['title' => 'LTFU (%)', 'value' => $ltfuRate],
        ];
    }

    private function normalizeOtherMeds(array $rows): array
    {
        $normalized = [];
        foreach ($rows as $row) {
            $medication = $this->normalizeCategory($row['medication'] ?? $row['title'] ?? null);
            if ($medication === '') {
                continue;
            }
            $count = (int) ($row['count'] ?? $row['value'] ?? 0);
            $row['title'] = $medication;
            $row['value'] = $count;
            $normalized[] = $row;
        }
        return $normalized;
    }

    private function parseBpString($value): ?array
    {
        if ($value === null) {
            return null;
        }
        $text = trim((string) $value);
        if ($this->isUnknownValue($text)) {
            return null;
        }
        if (!preg_match('/^\s*\d{2,3}\s*\/\s*\d{2,3}\s*$/', $text)) {
            return null;
        }
        $parts = preg_split('/\s*\/\s*/', $text);
        if (!$parts || count($parts) < 2) {
            return null;
        }
        $sbp = (int) $parts[0];
        $dbp = (int) $parts[1];
        if ($sbp < 50 || $sbp > 300 || $dbp < 30 || $dbp > 200) {
            return null;
        }
        return ['sbp' => $sbp, 'dbp' => $dbp];
    }

    private function classifyBpStage(float $sbp, float $dbp): ?string
    {
        if ($sbp < 50 || $sbp > 300 || $dbp < 30 || $dbp > 200) {
            return null;
        }
        if ($sbp >= 180 || $dbp >= 110) {
            return 'Stage 3';
        }
        if ($sbp >= 160 || $dbp >= 100) {
            return 'Stage 2';
        }
        if ($sbp >= 140 || $dbp >= 90) {
            return 'Stage 1';
        }
        return 'Normal';
    }

    private function parseBpStageText($value): ?string
    {
        $text = strtolower(trim((string) ($value ?? '')));
        if ($this->isUnknownValue($text)) {
            return null;
        }
        $normalized = preg_replace('/[^a-z0-9<>=]+/', ' ', $text);
        $normalized = trim((string) $normalized);
        if ($normalized === '') {
            return null;
        }

        if (str_contains($normalized, 'normal') || str_contains($normalized, '<140/90') || str_contains($normalized, '<140 90')) {
            return 'Normal';
        }
        if (preg_match('/\bstage\s*1\b/', $normalized) || str_contains($normalized, 'stage1')) {
            return 'Stage 1';
        }
        if (preg_match('/\bstage\s*2\b/', $normalized) || str_contains($normalized, 'stage2')) {
            return 'Stage 2';
        }
        if (preg_match('/\bstage\s*3\b/', $normalized) || str_contains($normalized, 'stage3') || str_contains($normalized, '>=180/110') || str_contains($normalized, '>=180 110')) {
            return 'Stage 3';
        }

        return null;
    }

    private function normalizeDmTestType($value): ?string
    {
        $text = strtolower(trim((string) ($value ?? '')));
        if ($this->isUnknownValue($text)) {
            return null;
        }
        if (strpos($text, 'hba1c') !== false || strpos($text, 'hb a1c') !== false) {
            return 'HBA1C';
        }
        if (strpos($text, '2hpp') !== false || strpos($text, '2h') !== false && strpos($text, 'pp') !== false) {
            return '2HPP';
        }
        if (strpos($text, 'fbs') !== false || strpos($text, 'fast') !== false) {
            return 'FBS';
        }
        if (strpos($text, 'rbs') !== false || strpos($text, 'random') !== false) {
            return 'RBS';
        }
        return null;
    }

    private function computeBaselineDmControl(?float $value, ?string $testType, array $thresholds): ?bool
    {
        if ($value === null) {
            return null;
        }
        $type = $testType ?: 'RBS';
        if ($type === 'HBA1C') {
            return $value < (float) ($thresholds['hba1c'] ?? 7.0);
        }
        if ($type === '2HPP') {
            return $value < (float) ($thresholds['twopp'] ?? 180);
        }
        if ($type === 'FBS') {
            return $value < (float) ($thresholds['fbs'] ?? 126);
        }
        if ($type === 'RBS') {
            return $value < (float) ($thresholds['rbs'] ?? 200);
        }
        return null;
    }

    private function buildGlucoseStatusComparison(array $registers, array $followups, ?int $observeTs = null): array
    {
        $observeTs = $observeTs ?? time();
        $statusByPatient = $this->buildLatestFollowupStatusByPatient($followups, 84, $observeTs);
        $eligiblePatientIds = $this->buildGlucoseEligiblePatientIdsByLatestDiagnosisVisits($followups, 4);
        $baselineByPatient = [];
        $transitionKeys = ['Improved', 'Maintaining controlled', 'Worsen', 'Remain uncontrolled', 'Unavailable for comparison'];
        $transitionColors = [
            'Improved' => '#22c55e',
            'Maintaining controlled' => '#0ea5e9',
            'Worsen' => '#ef4444',
            'Remain uncontrolled' => '#94a3b8',
            'Unavailable for comparison' => '#cbd5e1',
        ];
        $emptyTransitions = array_fill_keys($transitionKeys, 0);

        foreach ($registers as $row) {
            $pid = $this->normalizePatientId($row['patient_id'] ?? $row['pid'] ?? null);
            if ($pid === null) {
                continue;
            }
            if (!isset($eligiblePatientIds[$pid])) {
                continue;
            }
            if (!$this->hasNewKnownDiagnosis(
                $row['second_hypertension'] ?? ($row['2nd_Hypertension'] ?? ($row['2nd_hypertension'] ?? null))
            )) {
                continue;
            }

            $regTs = $this->parseDateValue($row['reg_date'] ?? null) ?? 0;
            $glucose = $this->extractBaselineGlucoseStatusFromRegister($row);
            $current = $baselineByPatient[$pid] ?? null;

            if ($current === null || $regTs < $current['ts'] || ($regTs === $current['ts'] && $current['status'] !== 'valid' && $glucose['status'] === 'valid')) {
                $baselineByPatient[$pid] = [
                    'ts' => $regTs,
                    'status' => $glucose['status'],
                    'controlled' => $glucose['controlled'] ?? null,
                    'age' => $glucose['age'] ?? null,
                    'diagnosis_label' => $row['second_hypertension'] ?? ($row['2nd_Hypertension'] ?? ($row['2nd_hypertension'] ?? null)),
                    'invalid_values' => $glucose['invalid_values'] ?? [],
                ];
                continue;
            }

            if ($regTs === $current['ts'] && $glucose['status'] === 'invalid') {
                $baselineByPatient[$pid]['invalid_values'] = array_values(array_unique(array_merge(
                    $baselineByPatient[$pid]['invalid_values'] ?? [],
                    $glucose['invalid_values'] ?? []
                )));
            }
        }

        $baselineByPatient = $this->applyDiabetesFollowupBaselineFallback($baselineByPatient, $followups);
        $latestByPatient = $this->buildLatestGlucoseStatusByPatientFromFollowups($followups, $baselineByPatient, $observeTs);

        $baselineInvalidExamples = [];
        $latestInvalidExamples = [];
        $overallTransitions = $emptyTransitions;
        $activeTransitions = $emptyTransitions;
        $ltfuTransitions = $emptyTransitions;
        $cohortPatients = count($baselineByPatient);
        $activeCohortPatients = 0;
        $ltfuCohortPatients = 0;
        $pairedPatients = 0;
        $activePairedPatients = 0;
        $ltfuPairedPatients = 0;
        $excludedBaselineMissing = 0;
        $excludedBaselineInvalid = 0;
        $excludedLatestMissing = 0;
        $excludedLatestInvalid = 0;
        $excludedBaselineOther = 0;
        $excludedLatestOther = 0;

        foreach (array_keys($baselineByPatient) as $pid) {
            $baseline = $baselineByPatient[$pid] ?? ['status' => 'missing', 'invalid_values' => []];
            $latest = $latestByPatient[$pid] ?? ['status' => 'missing', 'invalid_values' => []];
            $latestStatus = $statusByPatient[$pid]['status'] ?? null;

            if (($baseline['status'] ?? 'missing') === 'invalid') {
                foreach (($baseline['invalid_values'] ?? []) as $value) {
                    $baselineInvalidExamples[$value] = ($baselineInvalidExamples[$value] ?? 0) + 1;
                }
            }
            if (($latest['status'] ?? 'missing') === 'invalid') {
                foreach (($latest['invalid_values'] ?? []) as $value) {
                    $latestInvalidExamples[$value] = ($latestInvalidExamples[$value] ?? 0) + 1;
                }
            }

            $baselineValid = ($baseline['status'] ?? null) === 'valid' && array_key_exists('controlled', $baseline) && $baseline['controlled'] !== null;
            $latestValid = ($latest['status'] ?? null) === 'valid' && array_key_exists('controlled', $latest) && $latest['controlled'] !== null;

            if (!$baselineValid) {
                if (($baseline['status'] ?? 'missing') === 'invalid') {
                    $excludedBaselineInvalid++;
                } elseif (($baseline['status'] ?? 'missing') === 'missing') {
                    $excludedBaselineMissing++;
                } else {
                    $excludedBaselineOther++;
                }
            }
            if (!$latestValid) {
                if (($latest['status'] ?? 'missing') === 'invalid') {
                    $excludedLatestInvalid++;
                } elseif (($latest['status'] ?? 'missing') === 'missing') {
                    $excludedLatestMissing++;
                } else {
                    $excludedLatestOther++;
                }
            }

            $transition = 'Unavailable for comparison';
            if ($baselineValid && $latestValid) {
                $pairedPatients++;
                $transition = $this->resolveGlucoseTransitionLabel((bool) $baseline['controlled'], (bool) $latest['controlled']);
            }
            $overallTransitions[$transition]++;

            if ($latestStatus === 'active') {
                $activeCohortPatients++;
                if ($baselineValid && $latestValid) {
                    $activePairedPatients++;
                }
                $activeTransitions[$transition]++;
            }
            if ($latestStatus === 'ltfu') {
                $ltfuCohortPatients++;
                if ($baselineValid && $latestValid) {
                    $ltfuPairedPatients++;
                }
                $ltfuTransitions[$transition]++;
            }
        }

        arsort($baselineInvalidExamples);
        arsort($latestInvalidExamples);
        $baselineInvalidTop = [];
        $latestInvalidTop = [];
        foreach (array_slice($baselineInvalidExamples, 0, 10, true) as $value => $count) {
            $baselineInvalidTop[] = ['value' => $value, 'count' => $count];
        }
        foreach (array_slice($latestInvalidExamples, 0, 10, true) as $value => $count) {
            $latestInvalidTop[] = ['value' => $value, 'count' => $count];
        }

        $buildChartRows = static function (array $counts) use ($transitionKeys, $transitionColors): array {
            $rows = [];
            foreach ($transitionKeys as $label) {
                $rows[] = [
                    'title' => $label,
                    'value' => (int) ($counts[$label] ?? 0),
                    'color' => $transitionColors[$label],
                ];
            }
            return $rows;
        };

        return [
            'cohort_patients' => $cohortPatients,
            'active_cohort_patients' => $activeCohortPatients,
            'ltfu_cohort_patients' => $ltfuCohortPatients,
            'paired_patients' => $pairedPatients,
            'active_paired_patients' => $activePairedPatients,
            'ltfu_paired_patients' => $ltfuPairedPatients,
            'comparison_charts' => [
                'overall' => [
                    'title' => 'Overall glucose status comparison',
                    'cohort_patients' => $cohortPatients,
                    'paired_patients' => $pairedPatients,
                    'chart' => $buildChartRows($overallTransitions),
                ],
                'active' => [
                    'title' => 'Active glucose status comparison',
                    'cohort_patients' => $activeCohortPatients,
                    'paired_patients' => $activePairedPatients,
                    'chart' => $buildChartRows($activeTransitions),
                ],
                'ltfu' => [
                    'title' => 'LTFU glucose status comparison',
                    'cohort_patients' => $ltfuCohortPatients,
                    'paired_patients' => $ltfuPairedPatients,
                    'chart' => $buildChartRows($ltfuTransitions),
                ],
            ],
            'data_quality' => [
                ['metric' => 'Baseline glucose cohort', 'value' => count($baselineByPatient)],
                ['metric' => 'Baseline cohort with latest 1y follow-up glucose window', 'value' => count(array_intersect(array_keys($baselineByPatient), array_keys($latestByPatient)))],
                ['metric' => 'Unavailable overall', 'value' => max(0, $cohortPatients - $pairedPatients)],
                ['metric' => 'Unavailable active', 'value' => max(0, $activeCohortPatients - $activePairedPatients)],
                ['metric' => 'Unavailable LTFU', 'value' => max(0, $ltfuCohortPatients - $ltfuPairedPatients)],
                ['metric' => 'Compared overall', 'value' => $pairedPatients],
                ['metric' => 'Compared active', 'value' => $activePairedPatients],
                ['metric' => 'Compared LTFU', 'value' => $ltfuPairedPatients],
                ['metric' => 'Excluded baseline missing', 'value' => $excludedBaselineMissing],
                ['metric' => 'Excluded baseline invalid', 'value' => $excludedBaselineInvalid],
                ['metric' => 'Excluded baseline other', 'value' => $excludedBaselineOther],
                ['metric' => 'Excluded latest 1y follow-up missing', 'value' => $excludedLatestMissing],
                ['metric' => 'Excluded latest 1y follow-up invalid', 'value' => $excludedLatestInvalid],
                ['metric' => 'Excluded latest 1y follow-up other', 'value' => $excludedLatestOther],
            ],
            'invalid_examples' => [
                'baseline' => $baselineInvalidTop,
                'last_record' => $latestInvalidTop,
            ],
        ];
    }

    private function resolveGlucoseTransitionLabel(bool $baselineControlled, bool $latestControlled): string
    {
        if (!$baselineControlled && $latestControlled) {
            return 'Improved';
        }
        if ($baselineControlled && $latestControlled) {
            return 'Maintaining controlled';
        }
        if ($baselineControlled && !$latestControlled) {
            return 'Worsen';
        }
        return 'Remain uncontrolled';
    }

    private function extractBaselineGlucoseStatusFromRegister(array $row): array
    {
        $age = $this->parseFloat($row['visit_age'] ?? ($row['age_at_reg'] ?? ($row['current_age'] ?? null)));
        $referenceTs = $this->parseDateValue($row['reg_date'] ?? null);
        $invalid = [];
        $measures = ['fbs' => [], 'rbs' => [], 'hba1c' => []];

        $this->appendGlucoseMeasure(
            $measures,
            $this->normalizeDmTestType($row['first_dm_test_type'] ?? null),
            $row['first_dm_value'] ?? null,
            $this->parseDateValue($row['first_dm_date'] ?? null) ?? $referenceTs,
            $invalid,
            'baseline first'
        );
        $this->appendGlucoseMeasure(
            $measures,
            $this->normalizeDmTestType($row['second_dm_test_type'] ?? null),
            $row['second_dm_value'] ?? null,
            $this->parseDateValue($row['second_dm_date'] ?? null) ?? $referenceTs,
            $invalid,
            'baseline second'
        );

        return $this->evaluateGlucoseControlStatus($age, $referenceTs, $measures, $invalid);
    }

    private function buildGlucoseEligiblePatientIdsByLatestDiagnosisVisits(array $followups, int $requiredVisits = 4): array
    {
        if (empty($followups) || $requiredVisits <= 0) {
            return [];
        }

        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $visitsByPatient = [];

        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row[$idField] ?? null);
            if ($pid === null) {
                continue;
            }

            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            if ($visitTs === null) {
                continue;
            }

            $visitKey = date('Y-m-d', $visitTs);
            $category = $this->categorizeFollowupDiagnosis($this->extractFollowupDiagnosisValue($row));
            $isEligibleCategory = in_array($category, ['diabetes', 'both'], true);

            if (!isset($visitsByPatient[$pid][$visitKey])) {
                $visitsByPatient[$pid][$visitKey] = [
                    'ts' => $visitTs,
                    'eligible' => $isEligibleCategory,
                ];
                continue;
            }

            if ($visitTs > ($visitsByPatient[$pid][$visitKey]['ts'] ?? 0)) {
                $visitsByPatient[$pid][$visitKey]['ts'] = $visitTs;
            }
            if ($isEligibleCategory) {
                $visitsByPatient[$pid][$visitKey]['eligible'] = true;
            }
        }

        $eligible = [];
        foreach ($visitsByPatient as $pid => $visits) {
            uasort($visits, static function (array $left, array $right): int {
                return ($right['ts'] ?? 0) <=> ($left['ts'] ?? 0);
            });
            $latestVisits = array_slice(array_values($visits), 0, $requiredVisits);
            if (count($latestVisits) < $requiredVisits) {
                continue;
            }

            $allEligible = true;
            foreach ($latestVisits as $visit) {
                if (empty($visit['eligible'])) {
                    $allEligible = false;
                    break;
                }
            }
            if ($allEligible) {
                $eligible[$pid] = true;
            }
        }

        return $eligible;
    }

    private function applyDiabetesFollowupBaselineFallback(array $baselineByPatient, array $followups): array
    {
        if (empty($baselineByPatient) || empty($followups)) {
            return $baselineByPatient;
        }

        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $fallbackByPatient = [];

        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row[$idField] ?? null);
            if ($pid === null || !isset($baselineByPatient[$pid])) {
                continue;
            }

            $baseline = $baselineByPatient[$pid];
            if (($baseline['status'] ?? null) !== 'missing') {
                continue;
            }
            if (!$this->hasNewKnownDiagnosis($baseline['diagnosis_label'] ?? null)) {
                continue;
            }

            $diagnosis = $this->categorizeFollowupDiagnosis(
                $this->extractFollowupDiagnosisValue($row)
            );
            if (!in_array($diagnosis, ['diabetes', 'both'], true)) {
                continue;
            }

            $fbsTestTs = $this->parseDateValue($row['fbs_test_date'] ?? null);
            if ($fbsTestTs === null) {
                continue;
            }

            if ($this->isUnknownValue($row['fbs'] ?? null)) {
                continue;
            }

            $baselineTs = $baseline['ts'] ?? null;
            if ($baselineTs !== null && $baselineTs > 0 && $fbsTestTs < $baselineTs) {
                continue;
            }

            $visitTs = $this->parseDateValue($row['visit_date'] ?? null) ?? $fbsTestTs;
            $current = $fallbackByPatient[$pid] ?? null;
            if ($current !== null) {
                if ($fbsTestTs > ($current['test_ts'] ?? PHP_INT_MAX)) {
                    continue;
                }
                if ($fbsTestTs === ($current['test_ts'] ?? null) && $visitTs >= ($current['visit_ts'] ?? PHP_INT_MAX)) {
                    continue;
                }
            }

            $fallbackByPatient[$pid] = [
                'test_ts' => $fbsTestTs,
                'visit_ts' => $visitTs,
                'row' => $row,
            ];
        }

        foreach ($fallbackByPatient as $pid => $entry) {
            $baseline = $baselineByPatient[$pid] ?? [];
            $fallbackTs = $entry['test_ts'] ?? null;
            if ($fallbackTs === null) {
                continue;
            }
            $glucose = $this->extractBaselineGlucoseStatusFromFollowupFallback(
                $entry['row'] ?? [],
                $baseline['age'] ?? null,
                $baseline['ts'] ?? null,
                $fallbackTs
            );

            if (($glucose['status'] ?? 'missing') === 'missing' && empty($glucose['invalid_values'] ?? [])) {
                continue;
            }

            $baselineByPatient[$pid] = [
                'ts' => $fallbackTs,
                'status' => $glucose['status'],
                'controlled' => $glucose['controlled'] ?? null,
                'age' => $glucose['age'] ?? ($baseline['age'] ?? null),
                'invalid_values' => $glucose['invalid_values'] ?? [],
            ];
        }

        return $baselineByPatient;
    }

    private function extractBaselineGlucoseStatusFromFollowupFallback(
        array $row,
        ?float $baselineAge,
        ?int $baselineTs,
        int $fallbackTs
    ): array {
        $fallbackAge = $this->parseFloat($row['visit_age'] ?? ($row['age_at_visit'] ?? null));

        $age = $this->computeAgeFromBaselineAtVisit($baselineAge, $baselineTs, $fallbackTs, $fallbackAge);
        $invalid = [];
        $measures = ['fbs' => [], 'rbs' => [], 'hba1c' => []];

        $this->appendGlucoseMeasure(
            $measures,
            'FBS',
            $row['fbs'] ?? null,
            $this->parseDateValue($row['fbs_test_date'] ?? null) ?? $fallbackTs,
            $invalid,
            'baseline fallback FBS'
        );

        return $this->evaluateGlucoseControlStatus($age, $fallbackTs, $measures, $invalid);
    }

    private function buildLatestGlucoseStatusByPatientFromFollowups(array $followups, array $baselineByPatient, int $observeTs): array
    {
        if (empty($followups) || empty($baselineByPatient)) {
            return [];
        }

        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $rowsByPatient = [];
        $latestVisitByPatient = [];
        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row[$idField] ?? null);
            if ($pid === null || !isset($baselineByPatient[$pid])) {
                continue;
            }

            $visitTs = $this->parseDateValue($row['visit_date'] ?? null);
            if ($visitTs === null || $visitTs > $observeTs) {
                continue;
            }

            $rowsByPatient[$pid][] = [
                'ts' => $visitTs,
                'row' => $row,
            ];
            if (!isset($latestVisitByPatient[$pid]) || $visitTs > $latestVisitByPatient[$pid]) {
                $latestVisitByPatient[$pid] = $visitTs;
            }
        }

        $latestByPatient = [];
        $windowSeconds = 366 * 86400;
        foreach ($latestVisitByPatient as $pid => $anchorTs) {
            $baseline = $baselineByPatient[$pid] ?? [];
            $windowStartTs = $anchorTs - $windowSeconds;
            $measures = ['fbs' => [], 'rbs' => [], 'hba1c' => []];
            $invalid = [];

            foreach (($rowsByPatient[$pid] ?? []) as $entry) {
                $visitTs = $entry['ts'] ?? null;
                if ($visitTs === null || $visitTs < $windowStartTs || $visitTs > $anchorTs) {
                    continue;
                }
                $this->appendGlucoseMeasuresFromFollowupWindow($entry['row'], $visitTs, $measures, $invalid);
            }

            $age = $this->computeAgeFromBaselineAtVisit(
                $baseline['age'] ?? null,
                $baseline['ts'] ?? null,
                $anchorTs,
                null
            );
            $latestByPatient[$pid] = array_merge(
                $this->evaluateGlucoseControlStatus($age, $anchorTs, $measures, $invalid),
                ['ts' => $anchorTs]
            );
        }

        return $latestByPatient;
    }

    private function appendGlucoseMeasuresFromFollowupWindow(array $row, ?int $visitTs, array &$measures, array &$invalid): void
    {
        $this->appendGlucoseMeasure(
            $measures,
            'FBS',
            $row['fbs'] ?? null,
            $this->parseDateValue($row['fbs_test_date'] ?? null) ?? $visitTs,
            $invalid,
            'follow-up FBS'
        );
        $this->appendGlucoseMeasure(
            $measures,
            'RBS',
            $row['rbs_result'] ?? null,
            $visitTs,
            $invalid,
            'follow-up RBS'
        );
        $this->appendGlucoseMeasure(
            $measures,
            '2HPP',
            $row['t2hpp'] ?? null,
            $this->parseDateValue($row['t2hpp_test_date'] ?? null) ?? $visitTs,
            $invalid,
            'follow-up 2HPP'
        );
        $this->appendGlucoseMeasure(
            $measures,
            'HBA1C',
            $row['hba1c'] ?? null,
            $visitTs,
            $invalid,
            'follow-up HbA1c'
        );
    }

    private function appendGlucoseMeasure(array &$measures, ?string $type, $rawValue, ?int $dateTs, array &$invalid, string $label): void
    {
        if ($type === null) {
            return;
        }

        $normalizedRaw = trim((string) ($rawValue ?? ''));
        if ($normalizedRaw === '' || $this->isUnknownValue($normalizedRaw)) {
            return;
        }

        $value = $this->parseFloat($rawValue);
        if ($value === null) {
            $invalid[] = $label . ': ' . $type . ' = ' . $normalizedRaw;
            return;
        }

        $bucket = null;
        $min = null;
        $max = null;
        if ($type === 'FBS') {
            $bucket = 'fbs';
            $min = 20.0;
            $max = 600.0;
        } elseif ($type === 'RBS' || $type === '2HPP') {
            $bucket = 'rbs';
            $min = 20.0;
            $max = 1000.0;
        } elseif ($type === 'HBA1C') {
            $bucket = 'hba1c';
            $min = 2.0;
            $max = 25.0;
        }

        if ($bucket === null) {
            return;
        }
        if ($value < $min || $value > $max) {
            $invalid[] = $label . ': ' . $type . ' = ' . $normalizedRaw;
            return;
        }

        $measures[$bucket][] = [
            'type' => $type,
            'value' => $value,
            'ts' => $dateTs,
        ];
    }

    private function evaluateGlucoseControlStatus(?float $age, ?int $referenceTs, array $measures, array $invalid): array
    {
        if ($age === null) {
            return [
                'status' => !empty($invalid) ? 'invalid' : 'missing',
                'controlled' => null,
                'age' => null,
                'invalid_values' => array_values(array_unique($invalid)),
            ];
        }

        $fbsLower = $age < 65 ? 80.0 : 100.0;
        $fbsUpper = $age < 65 ? 130.0 : 180.0;
        $rbsUpper = $age < 65 ? 180.0 : 200.0;
        $hasMainMeasure = false;
        $mainExceeded = false;
        $mainControlled = false;

        foreach (($measures['fbs'] ?? []) as $measure) {
            $hasMainMeasure = true;
            $value = (float) ($measure['value'] ?? 0);
            if ($value < $fbsLower || $value > $fbsUpper) {
                $mainExceeded = true;
            } else {
                $mainControlled = true;
            }
        }

        foreach (($measures['rbs'] ?? []) as $measure) {
            $hasMainMeasure = true;
            $value = (float) ($measure['value'] ?? 0);
            if ($value >= $rbsUpper) {
                $mainExceeded = true;
            } else {
                $mainControlled = true;
            }
        }

        $annualHba1cExceeded = false;
        foreach (($measures['hba1c'] ?? []) as $measure) {
            $measureTs = $measure['ts'] ?? $referenceTs;
            if ($referenceTs !== null && $measureTs !== null && abs($referenceTs - $measureTs) > 366 * 86400) {
                continue;
            }
            if ((float) ($measure['value'] ?? 0) >= 7.5) {
                $annualHba1cExceeded = true;
                break;
            }
        }

        if ($mainExceeded || $annualHba1cExceeded) {
            return [
                'status' => 'valid',
                'controlled' => false,
                'age' => $age,
                'invalid_values' => array_values(array_unique($invalid)),
            ];
        }

        if ($hasMainMeasure && $mainControlled) {
            return [
                'status' => 'valid',
                'controlled' => true,
                'age' => $age,
                'invalid_values' => array_values(array_unique($invalid)),
            ];
        }

        return [
            'status' => !empty($invalid) ? 'invalid' : 'missing',
            'controlled' => null,
            'age' => $age,
            'invalid_values' => array_values(array_unique($invalid)),
        ];
    }

    private function computeAgeFromBaselineAtVisit(?float $baselineAge, ?int $baselineTs, ?int $visitTs, ?float $fallbackAge = null): ?float
    {
        if ($baselineAge !== null && $baselineTs !== null && $visitTs !== null && $visitTs >= $baselineTs) {
            return $baselineAge + (($visitTs - $baselineTs) / 31557600);
        }
        return $fallbackAge;
    }

    private function buildControlImprovementSummary(array $patients, array $registers, array $thresholds): array
    {
        $baselineMap = [];
        foreach ($registers as $row) {
            $pid = $row['patient_id'] ?? $row['pid'] ?? null;
            if (!$pid) {
                continue;
            }
            $regTs = $this->parseDateValue($row['reg_date'] ?? null) ?? 0;
            if (isset($baselineMap[$pid]) && $regTs >= $baselineMap[$pid]['reg_ts']) {
                continue;
            }

            $bp = $this->parseBpString($row['first_bp'] ?? null)
                ?? $this->parseBpString($row['second_bp'] ?? null)
                ?? $this->parseBpString($row['third_bp'] ?? null);

            $bpControlled = null;
            if ($bp !== null) {
                $bpControlled = $bp['sbp'] < (float) ($thresholds['bp_control_sbp'] ?? 140)
                    && $bp['dbp'] < (float) ($thresholds['bp_control_dbp'] ?? 90);
            }

            $dmType = $this->normalizeDmTestType($row['first_dm_test_type'] ?? null);
            $dmValue = $this->parseFloat($row['first_dm_value'] ?? null);
            if ($dmValue === null) {
                $dmType = $this->normalizeDmTestType($row['second_dm_test_type'] ?? null);
                $dmValue = $this->parseFloat($row['second_dm_value'] ?? null);
            }

            $dmControlled = $this->computeBaselineDmControl($dmValue, $dmType, $thresholds);

            $baselineMap[$pid] = [
                'reg_ts' => $regTs,
                'bp_controlled' => $bpControlled,
                'dm_controlled' => $dmControlled,
            ];
        }

        $bpEligible = 0;
        $bpImproved = 0;
        $dmEligible = 0;
        $dmImproved = 0;

        foreach ($patients as $patient) {
            $pid = $patient['patient_id'] ?? $patient['pid'] ?? null;
            if (!$pid || !isset($baselineMap[$pid])) {
                continue;
            }
            $baseline = $baselineMap[$pid];
            $bpWith = $this->parseBool($patient['bp_with_values'] ?? null);
            $dmWith = $this->parseBool($patient['dm_with_values'] ?? null);

            if ($baseline['bp_controlled'] === false && $bpWith) {
                $bpEligible++;
                if ($this->parseBool($patient['bp_controlled'] ?? null)) {
                    $bpImproved++;
                }
            }
            if ($baseline['dm_controlled'] === false && $dmWith) {
                $dmEligible++;
                if ($this->parseBool($patient['dm_controlled'] ?? null)) {
                    $dmImproved++;
                }
            }
        }

        $bpRate = $bpEligible ? round(($bpImproved / $bpEligible) * 100, 1) : 0;
        $dmRate = $dmEligible ? round(($dmImproved / $dmEligible) * 100, 1) : 0;

        return [
            'rows' => [
                ['metric' => 'BP', 'eligible' => $bpEligible, 'improved' => $bpImproved, 'rate' => $bpRate],
                ['metric' => 'Diabetes', 'eligible' => $dmEligible, 'improved' => $dmImproved, 'rate' => $dmRate],
            ],
            'chart' => [
                ['title' => 'BP', 'value' => $bpRate],
                ['title' => 'Diabetes', 'value' => $dmRate],
            ],
        ];
    }

    private function buildSustainedControlSummary(array $followups, array $thresholds): array
    {
        $bpVisits = [];
        $dmVisits = [];
        $sbpThresh = (float) ($thresholds['bp_control_sbp'] ?? 140);
        $dbpThresh = (float) ($thresholds['bp_control_dbp'] ?? 90);
        $hba1cThresh = (float) ($thresholds['hba1c'] ?? 7.0);
        $twoppThresh = (float) ($thresholds['twopp'] ?? 180);
        $fbsThresh = (float) ($thresholds['fbs'] ?? 126);
        $rbsThresh = (float) ($thresholds['rbs'] ?? 200);

        foreach ($followups as $row) {
            $pid = $row['patient_id'] ?? $row['pid'] ?? null;
            $ts = $this->parseDateValue($row['visit_date'] ?? null);
            if (!$pid || $ts === null) {
                continue;
            }
            $sbp = $this->parseFloat($row['sbp'] ?? null);
            $dbp = $this->parseFloat($row['dbp'] ?? null);
            if ($sbp !== null && $dbp !== null) {
                $bpVisits[$pid][] = [
                    'ts' => $ts,
                    'controlled' => $sbp < $sbpThresh && $dbp < $dbpThresh,
                ];
            }

            $dmControl = $this->computeDmControlFromRow($row, $hba1cThresh, $twoppThresh, $fbsThresh, $rbsThresh);
            if ($dmControl !== null) {
                $dmVisits[$pid][] = [
                    'ts' => $ts,
                    'controlled' => $dmControl,
                ];
            }
        }

        $bpEligible = 0;
        $bpSustained = 0;
        foreach ($bpVisits as $visits) {
            usort($visits, fn ($a, $b) => $a['ts'] <=> $b['ts']);
            $count = count($visits);
            if ($count < 2) {
                continue;
            }
            $bpEligible++;
            $lastTwo = array_slice($visits, -2);
            if ($lastTwo[0]['controlled'] && $lastTwo[1]['controlled']) {
                $bpSustained++;
            }
        }

        $dmEligible = 0;
        $dmSustained = 0;
        foreach ($dmVisits as $visits) {
            usort($visits, fn ($a, $b) => $a['ts'] <=> $b['ts']);
            $count = count($visits);
            if ($count < 2) {
                continue;
            }
            $dmEligible++;
            $lastTwo = array_slice($visits, -2);
            if ($lastTwo[0]['controlled'] && $lastTwo[1]['controlled']) {
                $dmSustained++;
            }
        }

        $bpRate = $bpEligible ? round(($bpSustained / $bpEligible) * 100, 1) : 0;
        $dmRate = $dmEligible ? round(($dmSustained / $dmEligible) * 100, 1) : 0;

        return [
            'rows' => [
                ['metric' => 'BP', 'eligible' => $bpEligible, 'sustained' => $bpSustained, 'rate' => $bpRate],
                ['metric' => 'Diabetes', 'eligible' => $dmEligible, 'sustained' => $dmSustained, 'rate' => $dmRate],
            ],
            'chart' => [
                ['title' => 'BP', 'value' => $bpRate],
                ['title' => 'Diabetes', 'value' => $dmRate],
            ],
        ];
    }

    private function buildMonthlySeries(array $rows): array
    {
        if (empty($rows)) {
            return ['labels' => [], 'series' => []];
        }

        $labels = [];
        $newRegs = [];
        $followups = [];
        foreach ($rows as $row) {
            $labels[] = $row['month_start'] ?? '';
            $newRegs[] = (int) ($row['new_regs'] ?? 0);
            $followups[] = (int) ($row['followups'] ?? 0);
        }

        return [
            'labels' => $labels,
            'series' => [
                ['label' => 'New registrations', 'data' => $newRegs, 'color' => '#0ea5e9'],
                ['label' => 'Follow-up visits', 'data' => $followups, 'color' => '#22c55e'],
            ],
        ];
    }

    private function buildRateSeries(array $rows, string $field, string $label): array
    {
        if (empty($rows)) {
            return ['labels' => [], 'series' => []];
        }

        $labels = [];
        $values = [];
        foreach ($rows as $row) {
            $labels[] = $row['visit_month'] ?? '';
            $rate = isset($row[$field]) ? (float) $row[$field] * 100 : 0;
            $values[] = round($rate, 1);
        }

        return [
            'labels' => $labels,
            'series' => [
                ['label' => $label . ' (%)', 'data' => $values, 'color' => '#f59e0b'],
            ],
        ];
    }

    private function buildQualitySummary(array $rows): array
    {
        if (empty($rows)) {
            return [];
        }

        $labels = [
            'hba1c_recent' => 'HbA1c last 6m',
            'creatinine_recent' => 'Creatinine last 12m',
            'crcl_recent' => 'CRCL last 12m',
            'uring_ac_ratio_recent' => 'Urine A/C ratio last 12m',
            'cvd_risk_recent' => 'CVD risk last 12m',
        ];

        $summary = [];
        foreach ($rows as $row) {
            $metric = $row['metric'] ?? '';
            $summary[] = [
                'title' => $labels[$metric] ?? $metric,
                'value' => round(((float) ($row['rate'] ?? 0)) * 100, 1),
            ];
        }

        return $summary;
    }

    private function buildKpiSummary(array $rows): array
    {
        $labels = [
            'patients' => 'Patients',
            'active_caseload' => 'Active caseload',
            'bp_control_rate' => 'BP control rate (%)',
            'dm_control_rate' => 'DM control rate (%)',
            'ltfu_rate' => 'LTFU rate (%)',
        ];

        $cards = [];
        foreach ($rows as $row) {
            $metric = $row['metric'] ?? '';
            $value = $row['value'] ?? null;
            if ($value === null) {
                continue;
            }
            $label = $labels[$metric] ?? $metric;
            $display = $value;
            if (str_contains($metric, 'rate')) {
                $display = round(((float) $value) * 100, 1);
            }
            $cards[] = ['title' => $label, 'value' => $display];
        }
        return $cards;
    }

    private function buildContinuitySummary(array $rows): array
    {
        $labels = [
            'active_caseload' => 'Active caseload',
            'ltfu' => 'LTFU',
            'missed_appointments' => 'Missed appointments',
            'late_visit' => 'Late visits',
        ];

        $summary = [];
        foreach ($rows as $row) {
            $metric = $row['metric'] ?? '';
            $summary[] = [
                'title' => $labels[$metric] ?? $metric,
                'value' => (int) ($row['count'] ?? 0),
                'rate' => round(((float) ($row['rate'] ?? 0)) * 100, 1),
            ];
        }
        return $summary;
    }

    private function buildTestUsedSummary(array $rows): array
    {
        $counts = [];
        foreach ($rows as $row) {
            $test = $this->normalizeCategory($row['dm_test_used'] ?? null);
            if ($test === '') {
                continue;
            }
            $counts[$test] = ($counts[$test] ?? 0) + 1;
        }
        $summary = [];
        foreach ($counts as $label => $count) {
            $summary[] = ['title' => $label, 'value' => $count];
        }
        return $summary;
    }

    private function splitOperationsDistributions(array $rows): array
    {
        $adherence = [];
        $supply = [];
        foreach ($rows as $row) {
            $metric = $row['metric'] ?? '';
            $category = $this->normalizeCategory($row['category'] ?? null);
            if ($category === '') {
                continue;
            }
            $item = ['title' => $category, 'value' => (int) ($row['count'] ?? 0)];
            if ($metric === 'patient_adherence') {
                $adherence[] = $item;
            } elseif ($metric === 'drug_supply') {
                $supply[] = $item;
            }
        }
        return ['adherence' => $adherence, 'supply' => $supply];
    }

    private function buildMedicationPatterns(array $rows): array
    {
        if (empty($rows)) {
            return ['meds' => [], 'regimens' => []];
        }

        $fields = [
            'f_amlodipine_dose' => 'Amlodipine',
            'f_enalapril_dose' => 'Enalapril',
            'f_atorvastain_dose' => 'Atorvastatin',
            'f_hydrochlorothiazide_dose' => 'Hydrochlorothiazide',
            'f_aspirin_dose' => 'Aspirin',
            'f_metformin_500_dose' => 'Metformin 500',
            'f_metformin_1000_dose' => 'Metformin 1000',
            'f_gliclazide_500_dose' => 'Gliclazide 500',
            'f_gliclazide_1000_dose' => 'Gliclazide 1000',
        ];

        $counts = array_fill_keys(array_values($fields), 0);
        $regimenCounts = [];

        foreach ($rows as $row) {
            $regimenSize = 0;
            foreach ($fields as $field => $label) {
                if ($this->hasValue($row[$field] ?? null)) {
                    $counts[$label]++;
                    $regimenSize++;
                }
            }
            $regimenCounts[$regimenSize] = ($regimenCounts[$regimenSize] ?? 0) + 1;
        }

        $meds = [];
        foreach ($counts as $label => $count) {
            $meds[] = ['title' => $label, 'value' => $count];
        }

        $regimens = [];
        ksort($regimenCounts);
        foreach ($regimenCounts as $size => $count) {
            $regimens[] = ['title' => $size . ' meds', 'value' => $count];
        }

        return ['meds' => $meds, 'regimens' => $regimens];
    }

    private function buildVisitIntervals(array $rows): array
    {
        if (empty($rows)) {
            return ['bins' => [], 'median' => null, 'mean' => null];
        }

        $datesByPatient = [];
        foreach ($rows as $row) {
            $pid = $row['patient_id'] ?? $row['pid'] ?? null;
            $visitDate = $row['visit_date'] ?? null;
            if (!$pid || !$visitDate) {
                continue;
            }
            $timestamp = strtotime($visitDate);
            if ($timestamp === false) {
                continue;
            }
            $datesByPatient[$pid][] = $timestamp;
        }

        $intervals = [];
        foreach ($datesByPatient as $dates) {
            sort($dates);
            $prev = null;
            foreach ($dates as $ts) {
                if ($prev !== null) {
                    $days = (int) floor(($ts - $prev) / 86400);
                    if ($days >= 0) {
                        $intervals[] = $days;
                    }
                }
                $prev = $ts;
            }
        }

        if (empty($intervals)) {
            return ['bins' => [], 'median' => null, 'mean' => null];
        }

        $bins = [
            ['label' => '0-30', 'min' => 0, 'max' => 30],
            ['label' => '31-60', 'min' => 31, 'max' => 60],
            ['label' => '61-90', 'min' => 61, 'max' => 90],
            ['label' => '91-180', 'min' => 91, 'max' => 180],
            ['label' => '181-365', 'min' => 181, 'max' => 365],
            ['label' => '366-730', 'min' => 366, 'max' => 730],
            ['label' => '731+', 'min' => 731, 'max' => null],
        ];

        $binCounts = [];
        foreach ($bins as $bin) {
            $binCounts[$bin['label']] = 0;
        }
        foreach ($intervals as $days) {
            foreach ($bins as $bin) {
                if ($days >= $bin['min'] && ($bin['max'] === null || $days <= $bin['max'])) {
                    $binCounts[$bin['label']]++;
                    break;
                }
            }
        }

        sort($intervals);
        $mid = (int) floor(count($intervals) / 2);
        $median = count($intervals) % 2 === 0
            ? ($intervals[$mid - 1] + $intervals[$mid]) / 2
            : $intervals[$mid];
        $mean = array_sum($intervals) / count($intervals);

        $binSeries = [];
        foreach ($binCounts as $label => $count) {
            $binSeries[] = ['title' => $label, 'value' => $count];
        }

        return [
            'bins' => $binSeries,
            'median' => round($median, 1),
            'mean' => round($mean, 1),
        ];
    }

    private function buildRiskOutcomeSummary(array $rows): array
    {
        return [
            'cvd' => $this->summarizeOutcomeByFlag($rows, 'cvd_risk_high', 'High', 'Not high'),
            'ckd' => $this->summarizeOutcomeByFlag($rows, 'ckd_marker', 'CKD marker', 'No marker'),
        ];
    }

    private function buildMedicationChangeSummary(array $rows): array
    {
        return $this->summarizeOutcomeByFlag($rows, 'med_changed_flag', 'Changed', 'Not changed');
    }

    private function summarizeOutcomeByFlag(array $rows, string $field, string $trueLabel, string $falseLabel): array
    {
        $groups = [
            $trueLabel => ['patients' => 0, 'bp_num' => 0, 'bp_den' => 0, 'dm_num' => 0, 'dm_den' => 0],
            $falseLabel => ['patients' => 0, 'bp_num' => 0, 'bp_den' => 0, 'dm_num' => 0, 'dm_den' => 0],
        ];

        foreach ($rows as $row) {
            $flag = $this->parseBool($row[$field] ?? null);
            $label = $flag ? $trueLabel : $falseLabel;
            $groups[$label]['patients']++;

            if ($this->parseBool($row['bp_with_values'] ?? null)) {
                $groups[$label]['bp_den']++;
                if ($this->parseBool($row['bp_controlled'] ?? null)) {
                    $groups[$label]['bp_num']++;
                }
            }

            if ($this->parseBool($row['dm_with_values'] ?? null)) {
                $groups[$label]['dm_den']++;
                if ($this->parseBool($row['dm_controlled'] ?? null)) {
                    $groups[$label]['dm_num']++;
                }
            }
        }

        $summary = [];
        foreach ($groups as $label => $vals) {
            $summary[] = [
                'label' => $label,
                'patients' => $vals['patients'],
                'bp_control_rate' => $vals['bp_den'] ? round(($vals['bp_num'] / $vals['bp_den']) * 100, 1) : 0,
                'dm_control_rate' => $vals['dm_den'] ? round(($vals['dm_num'] / $vals['dm_den']) * 100, 1) : 0,
            ];
        }

        return $summary;
    }

    private function buildEquitySummary(array $rows): array
    {
        $bySex = [];
        $byAge = [];
        foreach ($rows as $row) {
            $gender = $this->normalizeCategory($row['gender'] ?? null);
            if ($gender !== '') {
                $this->accumulateEquity($bySex, $gender, $row);
            }
            $ageBand = $this->normalizeCategory($row['age_band'] ?? null);
            if ($ageBand !== '') {
                $this->accumulateEquity($byAge, $ageBand, $row);
            }
        }

        return [
            'sex' => $this->finalizeEquity($bySex),
            'age' => $this->finalizeEquity($byAge),
        ];
    }

    private function accumulateEquity(array &$group, string $label, array $row): void
    {
        if (!isset($group[$label])) {
            $group[$label] = ['patients' => 0, 'bp_num' => 0, 'bp_den' => 0, 'dm_num' => 0, 'dm_den' => 0, 'ltfu_num' => 0];
        }
        $group[$label]['patients']++;
        if ($this->parseBool($row['bp_with_values'] ?? null)) {
            $group[$label]['bp_den']++;
            if ($this->parseBool($row['bp_controlled'] ?? null)) {
                $group[$label]['bp_num']++;
            }
        }
        if ($this->parseBool($row['dm_with_values'] ?? null)) {
            $group[$label]['dm_den']++;
            if ($this->parseBool($row['dm_controlled'] ?? null)) {
                $group[$label]['dm_num']++;
            }
        }
        if ($this->parseBool($row['ltfu'] ?? null)) {
            $group[$label]['ltfu_num']++;
        }
    }

    private function finalizeEquity(array $group): array
    {
        $rows = [];
        foreach ($group as $label => $vals) {
            $rows[] = [
                'label' => $label,
                'patients' => $vals['patients'],
                'bp_control_rate' => $vals['bp_den'] ? round(($vals['bp_num'] / $vals['bp_den']) * 100, 1) : 0,
                'dm_control_rate' => $vals['dm_den'] ? round(($vals['dm_num'] / $vals['dm_den']) * 100, 1) : 0,
                'ltfu_rate' => $vals['patients'] ? round(($vals['ltfu_num'] / $vals['patients']) * 100, 1) : 0,
            ];
        }
        return $rows;
    }

    private function buildReferralSummary(array $rows): array
    {
        return [
            'outcomes' => $this->countByField($rows, 'outcome'),
            'referrals' => $this->countByField($rows, 'ncd_tout_icmv_location'),
            'transfers' => $this->countByField($rows, 'tout_mam_clinic'),
        ];
    }

    private function countByField(array $rows, string $field): array
    {
        $counts = [];
        foreach ($rows as $row) {
            $value = $this->normalizeCategory($row[$field] ?? null);
            if ($value === '') {
                continue;
            }
            $counts[$value] = ($counts[$value] ?? 0) + 1;
        }
        $result = [];
        foreach ($counts as $label => $count) {
            $result[] = ['title' => $label, 'value' => $count];
        }
        return $result;
    }
}