<?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 HandlesNcdAnalysis
{
    public function runNcdAnalysis(Request $request)
    {
        $phpBinary = $this->resolvePhpCliBinary();
        $existingStatus = $this->refreshNcdAnalysisStatus();
        if (($existingStatus['state'] ?? null) === 'running') {
            $startedAt = (string) ($existingStatus['started_at'] ?? '');
            $scope = (string) ($existingStatus['scope'] ?? 'selected scope');
            return redirect()
                ->back()
                ->with('message', 'NCD analysis is already running in background for ' . $scope . ($startedAt !== '' ? ' (started ' . $startedAt . ').' : '.'));
        }

        $endDateInput = trim((string) $request->input('end_date', $request->query('end_date', '')));
        $endDateTs = $this->parseDateValue($endDateInput);
        if ($endDateTs === null) {
            return redirect()
                ->back()
                ->withInput()
                ->with('message', 'Please select LTFU cut date before running NCD analysis.');
        }
        $observeDate = date('Y-m-d', $endDateTs);
        $runtimeConfigPath = $this->createNcdRuntimeConfig($observeDate);
        if ($runtimeConfigPath === null) {
            return redirect()
                ->back()
                ->withInput()
                ->with('message', 'Unable to prepare runtime NCD config for the selected LTFU cut date. Please check storage permissions.');
        }

        $clinicOptions = $this->buildNcdClinicOptions();
        $defaultClinic = $this->defaultNcdDatabaseName();
        $requestedClinic = (string) $request->input('clinic', $request->query('clinic', $defaultClinic));
        if (strtoupper($requestedClinic) === 'ALL') {
            $requestedClinic = 'overall';
        }
        $clinicConnection = array_key_exists($requestedClinic, $clinicOptions) ? $requestedClinic : $defaultClinic;

        $runDatabases = [];
        $scopeLabel = '';
        if ($clinicConnection === 'overall') {
            $ncdConfig = $this->loadNcdConfig();
            $runDatabases = array_values(array_filter(array_map(static function ($db) {
                return trim((string) $db);
            }, (array) ($ncdConfig['databases'] ?? []))));
            if (empty($runDatabases)) {
                $runDatabases = array_values(array_filter($this->allowedNcdDatabases(), static function ($db) {
                    $label = strtoupper(trim((string) $db));
                    return $label !== '' && $label !== 'ALL' && $label !== 'OVERALL';
                }));
            }
            $scopeLabel = 'ALL clinics';
        } else {
            $runDatabases = [$clinicConnection];
            $scopeLabel = 'clinic ' . $clinicConnection;
        }
        $runDatabases = array_values(array_unique($runDatabases));

        $commandParts = [$phpBinary, 'artisan', 'ncd:analyze', '--config=' . $runtimeConfigPath, '--timeout=0'];
        if (!empty($runDatabases)) {
            $commandParts[] = '--databases=' . implode(',', $runDatabases);
        }
        $analysisCommand = implode(' ', array_map('escapeshellarg', $commandParts));

        $logPath = $this->ncdAnalysisLogPath();
        $logDir = dirname($logPath);
        if (!is_dir($logDir)) {
            @mkdir($logDir, 0775, true);
        }

        try {
            $runSuffix = bin2hex(random_bytes(4));
        } catch (\Throwable $exception) {
            $runSuffix = substr(md5((string) microtime(true)), 0, 8);
        }
        $runId = date('Ymd_His') . '_' . $runSuffix;
        $inner = 'echo "[NCD_RUN:' . $runId . '] START" >> ' . escapeshellarg($logPath)
            . '; ' . $analysisCommand . ' >> ' . escapeshellarg($logPath) . ' 2>&1'
            . '; rc=$?; echo "[NCD_RUN:' . $runId . '] EXIT:$rc" >> ' . escapeshellarg($logPath);
        $shellCommand = 'cd ' . escapeshellarg(base_path())
            . ' && nohup bash -lc ' . escapeshellarg($inner)
            . ' >/dev/null 2>&1 & echo $!';

        $launch = new Process(['bash', '-lc', $shellCommand], base_path());
        $launch->setTimeout(15);
        $launch->setIdleTimeout(15);
        $launch->run();

        if (!$launch->isSuccessful()) {
            Log::error('Failed to start background NCD analysis from dashboard', [
                'user_id' => Auth::id(),
                'scope' => $scopeLabel,
                'command' => $analysisCommand,
                'stderr' => trim($launch->getErrorOutput()),
                'stdout' => trim($launch->getOutput()),
            ]);
            return redirect()
                ->back()
                ->with('message', 'Failed to start background NCD analysis. Please check server logs.');
        }

        $pidText = trim($launch->getOutput());
        $pid = ctype_digit($pidText) ? (int) $pidText : 0;
        if ($pid <= 0) {
            Log::error('Background NCD analysis started without valid PID', [
                'user_id' => Auth::id(),
                'scope' => $scopeLabel,
                'stdout' => $pidText,
            ]);
            return redirect()
                ->back()
                ->with('message', 'Background NCD analysis started but PID could not be verified.');
        }

        $status = [
            'state' => 'running',
            'running' => true,
            'pid' => $pid,
            'run_id' => $runId,
            'scope' => $scopeLabel,
            'requested_clinic' => $clinicConnection,
            'databases' => $runDatabases,
            'started_at' => now()->toDateTimeString(),
            'finished_at' => null,
            'exit_code' => null,
            'log_file' => $logPath,
            'command' => $analysisCommand,
            'requested_by' => Auth::id(),
        ];
        $this->writeNcdAnalysisStatus($status);

        Log::info('Background NCD analysis started from dashboard', [
            'user_id' => Auth::id(),
            'scope' => $scopeLabel,
            'pid' => $pid,
            'run_id' => $runId,
            'databases' => $runDatabases,
        ]);

        $redirectQuery = array_filter(array_merge($request->query(), [
            'clinic' => $clinicConnection,
            'end_date' => $observeDate,
        ]), static function ($value) {
            return $value !== null && $value !== '';
        });

        return redirect()
            ->route('ncd_analysis.dashboard', $redirectQuery)
            ->with('message', 'NCD analysis started in background for ' . $scopeLabel . '. You can continue using the page; refresh in a few minutes to see updated data.');
    }

    private function resolvePhpCliBinary(): string
    {
        $candidates = [
            '/usr/bin/php',
            '/usr/local/bin/php',
            PHP_BINARY,
            'php',
        ];
        foreach ($candidates as $candidate) {
            if ($candidate === null || $candidate === '') {
                continue;
            }
            if ($candidate === 'php') {
                return $candidate;
            }
            if (is_executable($candidate)) {
                return $candidate;
            }
        }
        return 'php';
    }

    public function ncdDashboard(Request $request)
    {
        $clinicLeader = Auth::user()->name ?? 'Clinic Leader Dr.';
        $clinicOptions = $this->buildNcdClinicOptions();
        $defaultClinic = $this->defaultNcdDatabaseName();
        $requestedClinic = (string) $request->input('clinic', $defaultClinic);
        if (strtoupper($requestedClinic) === 'ALL') {
            $requestedClinic = 'overall';
        }
        $clinicConnection = array_key_exists($requestedClinic, $clinicOptions) ? $requestedClinic : $defaultClinic;
        $clinicWarning = null;

        $ncdConfig = $this->loadNcdConfig();
        $filters = [
            'start_date' => $request->input('start_date'),
            'end_date' => $request->input('end_date'),
            'gender' => $request->input('gender'),
            'age_band' => $request->input('age_band'),
            'timeframe' => $request->input('timeframe'),
            'trend_granularity' => $request->input('trend_granularity'),
            'trend_year' => $request->input('trend_year'),
        ];
        $ncdAnalytics = $this->loadNcdAnalytics($clinicConnection, $filters, $ncdConfig);
        $ncdAnalysisStatus = $this->refreshNcdAnalysisStatus();

        return view('ncd_dashboard', compact('clinicLeader', 'clinicOptions', 'clinicConnection', 'clinicWarning', 'ncdAnalytics', 'ncdConfig', 'ncdAnalysisStatus'));
    }

    private function ncdAnalysisStatusPath(): string
    {
        return storage_path('app/ncd_analysis_status.json');
    }

    private function ncdAnalysisLogPath(): string
    {
        return storage_path('logs/ncd_analysis_background.log');
    }

    private function readNcdAnalysisStatus(): array
    {
        $path = $this->ncdAnalysisStatusPath();
        if (!file_exists($path)) {
            return [];
        }
        $raw = @file_get_contents($path);
        if ($raw === false || trim($raw) === '') {
            return [];
        }
        $decoded = json_decode($raw, true);
        return is_array($decoded) ? $decoded : [];
    }

    private function writeNcdAnalysisStatus(array $status): void
    {
        $path = $this->ncdAnalysisStatusPath();
        $dir = dirname($path);
        if (!is_dir($dir)) {
            @mkdir($dir, 0775, true);
        }
        @file_put_contents($path, json_encode($status, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
    }

    private function resolveNcdRunExitCode(string $runId, string $logPath): ?int
    {
        if ($runId === '' || !file_exists($logPath)) {
            return null;
        }
        $content = @file_get_contents($logPath);
        if ($content === false || $content === '') {
            return null;
        }
        $pattern = '/\\[NCD_RUN:' . preg_quote($runId, '/') . '\\] EXIT:(\\d+)/';
        if (!preg_match_all($pattern, $content, $matches) || empty($matches[1])) {
            return null;
        }
        $last = end($matches[1]);
        return is_string($last) && ctype_digit($last) ? (int) $last : null;
    }

    private function isProcessRunning(?int $pid): bool
    {
        if ($pid === null || $pid <= 0) {
            return false;
        }
        $check = new Process(['bash', '-lc', 'ps -p ' . (int) $pid . ' -o pid=']);
        $check->setTimeout(5);
        $check->run();
        return trim($check->getOutput()) !== '';
    }

    private function refreshNcdAnalysisStatus(): array
    {
        $status = $this->readNcdAnalysisStatus();
        if (empty($status)) {
            return ['state' => 'idle'];
        }

        $state = (string) ($status['state'] ?? '');
        if ($state !== 'running') {
            return $status;
        }

        $pid = isset($status['pid']) ? (int) $status['pid'] : 0;
        if ($this->isProcessRunning($pid)) {
            return $status;
        }

        $runId = (string) ($status['run_id'] ?? '');
        $logFile = (string) ($status['log_file'] ?? $this->ncdAnalysisLogPath());
        $exitCode = $this->resolveNcdRunExitCode($runId, $logFile);

        $status['running'] = false;
        $status['exit_code'] = $exitCode;
        $status['finished_at'] = now()->toDateTimeString();
        $status['state'] = $exitCode === 0 ? 'completed' : 'failed';
        $this->writeNcdAnalysisStatus($status);

        return $status;
    }

    public function exportNcdDataPack(Request $request)
    {
        $clinicOptions = $this->buildNcdClinicOptions();
        $defaultClinic = $this->defaultNcdDatabaseName();
        $requestedClinic = (string) $request->input('clinic', $defaultClinic);
        if (strtoupper($requestedClinic) === 'ALL') {
            $requestedClinic = 'overall';
        }
        $clinicConnection = array_key_exists($requestedClinic, $clinicOptions) ? $requestedClinic : $defaultClinic;

        $ncdConfig = $this->loadNcdConfig();
        $filters = [
            'start_date' => $request->input('start_date'),
            'end_date' => $request->input('end_date'),
            'gender' => $request->input('gender'),
            'age_band' => $request->input('age_band'),
            'timeframe' => $request->input('timeframe'),
            'trend_granularity' => $request->input('trend_granularity'),
            'trend_year' => $request->input('trend_year'),
        ];

        $ncdAnalytics = $this->loadNcdAnalytics($clinicConnection, $filters, $ncdConfig);
        if (empty($ncdAnalytics['available'])) {
            abort(404, 'No NCD analytics outputs found. Run analysis first.');
        }

        $clinicKey = $clinicConnection === 'ALL' ? 'overall' : $clinicConnection;
        $clinicPath = base_path('outputs') . DIRECTORY_SEPARATOR . $clinicKey;
        if (!is_dir($clinicPath)) {
            abort(404, 'Clinic output folder not found.');
        }

        $patientLatest = $this->readCsv($clinicPath . DIRECTORY_SEPARATOR . 'patient_latest.csv');
        $followups = $this->readCsv($clinicPath . DIRECTORY_SEPARATOR . 'followups_clean.csv');
        $registers = $this->readCsv($clinicPath . DIRECTORY_SEPARATOR . 'registers_clean.csv');
        $filterOptions = $this->buildFilterOptions($patientLatest);
        $selectedFilters = $this->buildSelectedFilters($filters, $filterOptions, $followups, $ncdConfig);

        $filteredPatients = $this->filterPatients($patientLatest, $selectedFilters);
        $filteredPatients = $this->filterRowsByDateRange(
            $filteredPatients,
            'visit_date',
            $selectedFilters['start_date'],
            $selectedFilters['end_date']
        );
        $filteredFollowups = $this->filterEventRows($followups, $selectedFilters);
        $filteredFollowups = $this->filterRowsByDateRange(
            $filteredFollowups,
            'visit_date',
            $selectedFilters['start_date'],
            $selectedFilters['end_date']
        );
        $filteredFollowups = $this->applyPatientTableGenderToFollowups($filteredFollowups, $clinicConnection);
        $filteredRegisters = $this->filterEventRows($registers, $selectedFilters);
        $filteredRegisters = $this->filterRowsByDateRange(
            $filteredRegisters,
            'reg_date',
            $selectedFilters['start_date'],
            $selectedFilters['end_date']
        );
        $thresholds = $ncdConfig['thresholds'] ?? [];
        $reportEndOverride = $this->resolveReportEndDate($selectedFilters['end_date'], $ncdConfig, $filteredFollowups, $filteredPatients);
        $filteredPatients = $this->applyReportEndFlags($filteredPatients, $filteredFollowups, $thresholds, $reportEndOverride);

        $files = [];
        $generatedAt = now()->toDateTimeString();
        $files['README.txt'] = implode(PHP_EOL, [
            'NCD analytics data pack',
            'Generated: ' . $generatedAt,
            'Clinic: ' . $clinicConnection,
            '',
            'How to use this pack',
            '- charts/charts_all_in_one.xlsx: one worksheet per chart dataset (for pivot and chart reproduction).',
            '- source/*.csv: filtered record-level rows used in the calculations.',
            '- data_quality/*: used columns, column-level quality summary, and top invalid values.',
            '',
            'Calculation summary by chart/worksheet',
            '1) DiagnosisTrend (charts/charts_all_in_one.xlsx::DiagnosisTrend)',
            '   - Source: source/registers_clean_filtered.csv',
            '   - Date field: reg_date',
            '   - Total cohort: count of register rows per period (yearly or monthly).',
            '   - HTN diagnosis: 1stHypertension contains New/Known/Know.',
            '   - DM diagnosis: Diabetes_Diagnose (column: 2nd_Hypertension) contains New/Known/Know.',
            '   - Both: HTN and DM flags true in the same row.',
            '',
            '2) DiagnosisGender (..::DiagnosisGender)',
            '   - Same as DiagnosisTrend, then split by gender (male/female).',
            '',
            '3) FollowupCumulative (..::FollowupCumulative)',
            '   - Source: source/followups_clean_filtered.csv',
            '   - Date field: visit_date (year bucket).',
            '   - Diagnosis source: ncd_diagnosis (NCD_Diagnosis).',
            '   - Categories: HTN / DM / Both / Other.',
            '   - Output values are cumulative counts by year.',
            '',
            '4) FollowupByGender (..::FollowupByGender)',
            '   - Same follow-up cumulative calculation, split by male/female.',
            '',
            '5) AgeDistribution (..::AgeDistribution)',
            '   - Source: source/registers_clean_filtered.csv',
            '   - Age field: visit_age (visit_Age).',
            '   - Rule: ages 1-100 only, grouped into 5-year bins.',
            '',
            '6) VisitPlanTrend (..::VisitPlanTrend)',
            '   - Source: source/followups_clean_filtered.csv',
            '   - For each patient, sort visits by visit_date.',
            '   - Compare next visit date with prior row next_appointment.',
            '   - Unplan: next visit earlier than next_appointment (delta < 0).',
            '   - Ontime: delta 0-7 days.',
            '   - Late: delta 8-83 days.',
            '   - LTFU: delta >= 84 days after next_appointment.',
            '   - Return to care: later visit after >= 84-day gap between consecutive visits.',
            '   - Excluded: missing next_appointment, no following visit.',
            '',
            '7) ReturnToCare (..::ReturnToCare)',
            '   - Source: source/followups_clean_filtered.csv',
            '   - For consecutive visits in same patient, if gap >= 84 days, count return-to-care event.',
            '   - Group by year of the later visit.',
            '',
            '8) LTFUStatus / LTFUByGender (..::LTFUStatus, ..::LTFUByGender)',
            '   - Source: source/followups_clean_filtered.csv',
            '   - Use latest visit per patient.',
            '   - If outcome indicates Died/Tout => Exited.',
            '   - Else if next_appointment is missing => Missing next appointment.',
            '   - Else if next_appointment + 84 days < observe date => LTFU, otherwise Active.',
            '',
            '9) ControlMatrix / ControlDirection / ControlDistribution',
            '   - Sources: source/registers_clean_filtered.csv + source/patient_latest_filtered.csv',
            '   - Hypertension cohort from ncd_pt_registers 1stHypertension = New/Known.',
            '   - Baseline BP stage from register priority: 3rdBP -> 2ndBP -> 1stBP; fallback staging_Hypertension.',
            '   - Last-record BP stage from patient_latest SBP/DBP; fallback bp_raw / bp_stage.',
            '   - Stage rules: Normal, Stage1 (140/90-159/99), Stage2 (160/100-179/109), Stage3 (>=180/110).',
            '   - ControlMatrix: overall hypertension cohort baseline stage -> last-record stage transition counts.',
            '   - ControlDirection: overall cohort Improved / Unchanged / Worsened from stage rank comparison.',
            '   - ControlDistribution: overall hypertension cohort baseline vs last-record stage distribution.',
            '',
            'Data quality tables',
            '- data_quality/used_columns.csv: columns used by this export and validation rule.',
            '- data_quality/column_quality_summary.csv: missing/valid/invalid counts and rates per column.',
            '- data_quality/top_invalid_values.csv: most frequent invalid raw values with reason.',
            '- data_quality/data_quality_tables.xlsx: same quality tables in workbook format.',
            '',
            'Tip',
            '- If needed, map aliases back to original field names using source CSV headers.',
            '',
        ]);

        $files['references/export_info.csv'] = $this->rowsToCsvString([
            ['field' => 'generated_at', 'value' => $generatedAt],
            ['field' => 'clinic', 'value' => $clinicConnection],
            ['field' => 'start_date', 'value' => $selectedFilters['start_date'] ?? ''],
            ['field' => 'end_date', 'value' => $selectedFilters['end_date'] ?? ''],
            ['field' => 'timeframe', 'value' => $selectedFilters['timeframe'] ?? 'all'],
            ['field' => 'trend_granularity', 'value' => $selectedFilters['trend_granularity'] ?? 'yearly'],
            ['field' => 'trend_year', 'value' => $selectedFilters['trend_year'] ?? ''],
        ], ['field', 'value']);

        $files['references/stage_definitions.csv'] = $this->rowsToCsvString([
            ['stage' => 'Normal', 'rule' => 'SBP < 140 and DBP < 90'],
            ['stage' => 'Stage 1', 'rule' => 'SBP 140-159 or DBP 90-99'],
            ['stage' => 'Stage 2', 'rule' => 'SBP 160-179 or DBP 100-109'],
            ['stage' => 'Stage 3', 'rule' => 'SBP >= 180 or DBP >= 110'],
        ], ['stage', 'rule']);

        $files['references/metric_dictionary.csv'] = $this->rowsToCsvString([
            ['dataset' => 'charts/charts_all_in_one.xlsx', 'description' => 'All chart datasets in one workbook; one sheet per chart'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::DiagnosisTrend', 'description' => 'Yearly/monthly cohort and diagnosis segments'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::FollowupCumulative', 'description' => 'Cumulative follow-up visits by diagnosis'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::VisitPlanTrend', 'description' => 'Visit plan status: ontime/unplan/late/ltfu plus return-to-care'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::ReturnToCare', 'description' => 'Return to care events by year (gap >= 84 days)'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::LTFUStatus', 'description' => 'Active/LTFU/Exited/Missing next appointment'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::ControlMatrix', 'description' => 'Overall hypertension cohort BP stage transition matrix baseline -> last record'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::ControlDirection', 'description' => 'Improved/Unchanged/Worsened counts'],
            ['dataset' => 'charts/charts_all_in_one.xlsx::ControlDistribution', 'description' => 'Overall hypertension cohort baseline vs last-record stage distribution'],
            ['dataset' => 'data_quality/column_quality_summary.csv', 'description' => 'Column-level data quality summary for used columns'],
            ['dataset' => 'data_quality/top_invalid_values.csv', 'description' => 'Top invalid values by table/column with reason and counts'],
            ['dataset' => 'data_quality/used_columns.csv', 'description' => 'List of used columns and validation rules'],
            ['dataset' => 'data_quality/data_quality_tables.xlsx', 'description' => 'Data quality workbook (one sheet per quality table)'],
            ['dataset' => 'source/*.csv', 'description' => 'Filtered record-level data sources used by charts'],
        ], ['dataset', 'description']);

        $files['source/patient_latest_filtered.csv'] = $this->rowsToCsvString($filteredPatients, $this->detectCsvHeaders($patientLatest));
        $files['source/registers_clean_filtered.csv'] = $this->rowsToCsvString($filteredRegisters, $this->detectCsvHeaders($registers));
        $files['source/followups_clean_filtered.csv'] = $this->rowsToCsvString($filteredFollowups, $this->detectCsvHeaders($followups));

        $diagnosisTrendRows = $this->flattenSeriesChartRows($ncdAnalytics['diagnosisTrend'] ?? [], 'period', [
            'granularity' => $ncdAnalytics['diagnosisTrend']['granularity'] ?? '',
            'trend_year' => $ncdAnalytics['diagnosisTrend']['trend_year'] ?? '',
        ]);
        $diagnosisGenderRows = $this->flattenSeriesChartRows([
            'labels' => $ncdAnalytics['diagnosisTrend']['labels'] ?? [],
            'series' => $ncdAnalytics['diagnosisTrend']['gender_series'] ?? [],
        ], 'period', [
            'granularity' => $ncdAnalytics['diagnosisTrend']['granularity'] ?? '',
            'trend_year' => $ncdAnalytics['diagnosisTrend']['trend_year'] ?? '',
        ]);
        $followupCumulativeRows = $this->flattenSeriesChartRows($ncdAnalytics['followupCumulativeTrend'] ?? [], 'year');
        $followupCumulativeGenderRows = $this->flattenSeriesChartRows([
            'labels' => $ncdAnalytics['followupCumulativeTrend']['labels'] ?? [],
            'series' => $ncdAnalytics['followupCumulativeTrend']['gender_series'] ?? [],
        ], 'year');
        $ageDistributionRows = $this->flattenTitleValueRows($ncdAnalytics['ageDistribution'] ?? [], 'age_band');
        $visitPlanRows = $this->flattenSeriesChartRows($ncdAnalytics['visitPlanTrend'] ?? [], 'year');
        $returnToCareRows = $this->flattenSeriesChartRows($ncdAnalytics['returnToCareTrend'] ?? [], 'year');
        $ltfuStatusRows = $this->flattenTitleValueRows($ncdAnalytics['ltfuByAppointment']['chart'] ?? [], 'status');
        $ltfuByGenderRows = $this->flattenGenderChartRows($ncdAnalytics['ltfuByAppointment']['gender_chart'] ?? []);

        $control = $ncdAnalytics['controlStatus'] ?? [];
        $controlDirectionRows = $this->flattenTitleValueRows($control['direction_chart'] ?? [], 'direction');
        $controlDistributionRows = $this->flattenSeriesChartRows($control['distribution_chart'] ?? [], 'phase');
        $controlDataQualityRows = $this->flattenMetricValueRows($control['data_quality'] ?? []);
        $controlInvalidBaselineRows = $control['invalid_examples']['baseline'] ?? [];
        $controlInvalidLatestRows = $control['invalid_examples']['latest'] ?? [];
        $controlMatrixRows = $this->flattenControlMatrixRows($control);

        $chartsWorkbook = $this->buildXlsxWorkbook([
            ['title' => 'DiagnosisTrend', 'rows' => $diagnosisTrendRows, 'headers' => ['period', 'series', 'value', 'granularity', 'trend_year']],
            ['title' => 'DiagnosisGender', 'rows' => $diagnosisGenderRows, 'headers' => ['period', 'series', 'value', 'granularity', 'trend_year']],
            ['title' => 'FollowupCumulative', 'rows' => $followupCumulativeRows, 'headers' => ['year', 'series', 'value']],
            ['title' => 'FollowupByGender', 'rows' => $followupCumulativeGenderRows, 'headers' => ['year', 'series', 'value']],
            ['title' => 'AgeDistribution', 'rows' => $ageDistributionRows, 'headers' => ['age_band', 'value']],
            ['title' => 'VisitPlanTrend', 'rows' => $visitPlanRows, 'headers' => ['year', 'series', 'value']],
            ['title' => 'ReturnToCare', 'rows' => $returnToCareRows, 'headers' => ['year', 'series', 'value']],
            ['title' => 'LTFUStatus', 'rows' => $ltfuStatusRows, 'headers' => ['status', 'value']],
            ['title' => 'LTFUByGender', 'rows' => $ltfuByGenderRows, 'headers' => ['status', 'series', 'value']],
            ['title' => 'ControlDirection', 'rows' => $controlDirectionRows, 'headers' => ['direction', 'value']],
            ['title' => 'ControlDistribution', 'rows' => $controlDistributionRows, 'headers' => ['phase', 'series', 'value']],
            ['title' => 'ControlDataQuality', 'rows' => $controlDataQualityRows, 'headers' => ['metric', 'value']],
            ['title' => 'ControlInvalidBaseline', 'rows' => $controlInvalidBaselineRows, 'headers' => ['value', 'count']],
            ['title' => 'ControlInvalidLatest', 'rows' => $controlInvalidLatestRows, 'headers' => ['value', 'count']],
            ['title' => 'ControlMatrix', 'rows' => $controlMatrixRows, 'headers' => ['baseline_stage', 'latest_stage', 'value']],
        ]);
        if ($chartsWorkbook === '') {
            abort(500, 'Unable to build charts workbook.');
        }
        $files['charts/charts_all_in_one.xlsx'] = $chartsWorkbook;

        $dataQuality = $this->buildNcdSourceDataQuality($filteredRegisters, $filteredFollowups, $ncdConfig);
        $files['data_quality/used_columns.csv'] = $this->rowsToCsvString(
            $dataQuality['used_columns'] ?? [],
            ['source_table', 'column_name', 'field_type', 'used_in', 'validation_rule']
        );
        $files['data_quality/column_quality_summary.csv'] = $this->rowsToCsvString(
            $dataQuality['summary'] ?? [],
            [
                'source_table',
                'column_name',
                'field_type',
                'column_present',
                'total_rows',
                'non_missing',
                'missing',
                'missing_pct',
                'valid',
                'invalid',
                'invalid_pct',
            ]
        );
        $files['data_quality/top_invalid_values.csv'] = $this->rowsToCsvString(
            $dataQuality['top_invalid'] ?? [],
            ['source_table', 'column_name', 'field_type', 'invalid_reason', 'invalid_value', 'count']
        );
        $qualityWorkbook = $this->buildXlsxWorkbook([
            [
                'title' => 'UsedColumns',
                'rows' => $dataQuality['used_columns'] ?? [],
                'headers' => ['source_table', 'column_name', 'field_type', 'used_in', 'validation_rule'],
            ],
            [
                'title' => 'ColumnSummary',
                'rows' => $dataQuality['summary'] ?? [],
                'headers' => [
                    'source_table',
                    'column_name',
                    'field_type',
                    'column_present',
                    'total_rows',
                    'non_missing',
                    'missing',
                    'missing_pct',
                    'valid',
                    'invalid',
                    'invalid_pct',
                ],
            ],
            [
                'title' => 'TopInvalid',
                'rows' => $dataQuality['top_invalid'] ?? [],
                'headers' => ['source_table', 'column_name', 'field_type', 'invalid_reason', 'invalid_value', 'count'],
            ],
        ]);
        if ($qualityWorkbook !== '') {
            $files['data_quality/data_quality_tables.xlsx'] = $qualityWorkbook;
        }

        $zipDir = storage_path('app/tmp');
        if (!is_dir($zipDir)) {
            mkdir($zipDir, 0775, true);
        }
        $safeClinic = preg_replace('/[^A-Za-z0-9_\\-]+/', '_', $clinicConnection);
        $timestamp = now()->format('Ymd_His');
        $zipName = "ncd-data-pack-{$safeClinic}-{$timestamp}.zip";
        $zipPath = $zipDir . DIRECTORY_SEPARATOR . $zipName;

        $zip = new \ZipArchive();
        $open = $zip->open($zipPath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE);
        if ($open !== true) {
            abort(500, 'Unable to create ZIP file.');
        }
        foreach ($files as $path => $content) {
            $zip->addFromString($path, $content);
        }
        $zip->close();

        return response()->download($zipPath, $zipName, [
            'Content-Type' => 'application/zip',
        ])->deleteFileAfterSend(true);
    }

    public function updateNcdConfig(Request $request)
    {
        $configPath = base_path('config.yaml');
        if (!file_exists($configPath)) {
            return redirect()
                ->back()
                ->with('message', 'Unable to update NCD settings because config.yaml was not found.');
        }

        $config = $this->loadNcdConfig();
        $config['date_range'] = $config['date_range'] ?? [];
        $config['thresholds'] = $config['thresholds'] ?? [];
        $config['privacy'] = $config['privacy'] ?? [];

        $allowedDbs = $this->allowedNcdDatabases();
        $rawDbs = $request->input('databases', []);
        $rawDbs = is_array($rawDbs) ? $rawDbs : [$rawDbs];
        $selectedDbs = array_values(array_intersect($rawDbs, $allowedDbs));
        if (!empty($selectedDbs)) {
            $config['databases'] = $selectedDbs;
        }

        $startDate = $request->input('start_date');
        if (!empty($startDate)) {
            $config['date_range']['start_date'] = $startDate;
        }
        $endDate = $request->input('end_date');
        if (!empty($endDate)) {
            $config['date_range']['end_date'] = $endDate;
        }

        $activeDays = filter_var($request->input('active_days'), FILTER_VALIDATE_INT);
        if ($activeDays !== false && $activeDays > 0) {
            $config['thresholds']['active_days'] = $activeDays;
        }
        $ltfuDays = filter_var($request->input('ltfu_days'), FILTER_VALIDATE_INT);
        if ($ltfuDays !== false && $ltfuDays > 0) {
            $config['thresholds']['ltfu_days'] = $ltfuDays;
        }
        $hba1cLookback = filter_var($request->input('hba1c_lookback_months'), FILTER_VALIDATE_INT);
        if ($hba1cLookback !== false && $hba1cLookback > 0) {
            $config['thresholds']['hba1c_lookback_months'] = $hba1cLookback;
        }
        $kidneyLookback = filter_var($request->input('kidney_lookback_months'), FILTER_VALIDATE_INT);
        if ($kidneyLookback !== false && $kidneyLookback > 0) {
            $config['thresholds']['kidney_lookback_months'] = $kidneyLookback;
        }
        $cvdLookback = filter_var($request->input('cvd_risk_lookback_months'), FILTER_VALIDATE_INT);
        if ($cvdLookback !== false && $cvdLookback > 0) {
            $config['thresholds']['cvd_risk_lookback_months'] = $cvdLookback;
        }

        $config['privacy']['mask_ids'] = $request->boolean('mask_ids');

        $yamlAvailable = class_exists('\\Symfony\\Component\\Yaml\\Yaml');
        $yamlExtAvailable = function_exists('yaml_parse_file') && function_exists('yaml_emit');

        if ($yamlAvailable) {
            $config = \Symfony\Component\Yaml\Yaml::parseFile($configPath);
            if (!is_array($config)) {
                $config = [];
            }
            $config['date_range'] = $config['date_range'] ?? [];
            $config['thresholds'] = $config['thresholds'] ?? [];
            $config['privacy'] = $config['privacy'] ?? [];
            if (!empty($selectedDbs)) {
                $config['databases'] = $selectedDbs;
            }
            if (!empty($startDate)) {
                $config['date_range']['start_date'] = $startDate;
            }
            if (!empty($endDate)) {
                $config['date_range']['end_date'] = $endDate;
            }
            if ($activeDays !== false && $activeDays > 0) {
                $config['thresholds']['active_days'] = $activeDays;
            }
            if ($ltfuDays !== false && $ltfuDays > 0) {
                $config['thresholds']['ltfu_days'] = $ltfuDays;
            }
            if ($hba1cLookback !== false && $hba1cLookback > 0) {
                $config['thresholds']['hba1c_lookback_months'] = $hba1cLookback;
            }
            if ($kidneyLookback !== false && $kidneyLookback > 0) {
                $config['thresholds']['kidney_lookback_months'] = $kidneyLookback;
            }
            if ($cvdLookback !== false && $cvdLookback > 0) {
                $config['thresholds']['cvd_risk_lookback_months'] = $cvdLookback;
            }
            $config['privacy']['mask_ids'] = $request->boolean('mask_ids');
            file_put_contents($configPath, \Symfony\Component\Yaml\Yaml::dump($config, 4, 2));
        } elseif ($yamlExtAvailable) {
            $config = yaml_parse_file($configPath);
            if (!is_array($config)) {
                $config = [];
            }
            $config['date_range'] = $config['date_range'] ?? [];
            $config['thresholds'] = $config['thresholds'] ?? [];
            $config['privacy'] = $config['privacy'] ?? [];
            if (!empty($selectedDbs)) {
                $config['databases'] = $selectedDbs;
            }
            if (!empty($startDate)) {
                $config['date_range']['start_date'] = $startDate;
            }
            if (!empty($endDate)) {
                $config['date_range']['end_date'] = $endDate;
            }
            if ($activeDays !== false && $activeDays > 0) {
                $config['thresholds']['active_days'] = $activeDays;
            }
            if ($ltfuDays !== false && $ltfuDays > 0) {
                $config['thresholds']['ltfu_days'] = $ltfuDays;
            }
            if ($hba1cLookback !== false && $hba1cLookback > 0) {
                $config['thresholds']['hba1c_lookback_months'] = $hba1cLookback;
            }
            if ($kidneyLookback !== false && $kidneyLookback > 0) {
                $config['thresholds']['kidney_lookback_months'] = $kidneyLookback;
            }
            if ($cvdLookback !== false && $cvdLookback > 0) {
                $config['thresholds']['cvd_risk_lookback_months'] = $cvdLookback;
            }
            $config['privacy']['mask_ids'] = $request->boolean('mask_ids');
            file_put_contents($configPath, yaml_emit($config));
        } else {
            $updated = $this->updateNcdConfigFile($configPath, [
                'databases' => $selectedDbs,
                'date_range' => [
                    'start_date' => $startDate,
                    'end_date' => $endDate,
                ],
                'thresholds' => [
                    'active_days' => $activeDays,
                    'ltfu_days' => $ltfuDays,
                    'hba1c_lookback_months' => $hba1cLookback,
                    'kidney_lookback_months' => $kidneyLookback,
                    'cvd_risk_lookback_months' => $cvdLookback,
                ],
                'privacy' => [
                    'mask_ids' => $request->boolean('mask_ids'),
                ],
            ]);

            if (!$updated) {
                return redirect()
                    ->back()
                    ->with('message', 'Unable to update NCD settings. Please check file permissions.');
            }
        }

        $clinic = $request->input('clinic', 'ALL');
        return redirect()
            ->route('ncd_analysis.dashboard', ['clinic' => $clinic])
            ->with('message', 'NCD settings saved. Run analysis to refresh outputs.');
    }

    private function createNcdRuntimeConfig(string $endDate): ?string
    {
        $sourcePath = base_path('config.yaml');
        if (!file_exists($sourcePath)) {
            return null;
        }

        $runtimeDir = storage_path('app/ncd_runtime');
        if (!is_dir($runtimeDir) && !@mkdir($runtimeDir, 0775, true) && !is_dir($runtimeDir)) {
            return null;
        }
        $runtimeConfigPath = $runtimeDir . DIRECTORY_SEPARATOR . 'config_runtime.yaml';
        $sourceContent = @file_get_contents($sourcePath);
        if ($sourceContent === false) {
            return null;
        }
        if (@file_put_contents($runtimeConfigPath, $sourceContent) === false) {
            return null;
        }

        $currentConfig = $this->loadNcdConfig();
        $maskIds = (bool) ($currentConfig['privacy']['mask_ids'] ?? false);

        $updated = $this->updateNcdConfigFile($runtimeConfigPath, [
            'databases' => [],
            'date_range' => [
                'end_date' => $endDate,
            ],
            'thresholds' => [],
            'privacy' => [
                'mask_ids' => $maskIds,
            ],
        ]);

        return $updated ? $runtimeConfigPath : null;
    }

    private function defaultNcdDatabaseName(): string
    {
        $ncdConfig = $this->loadNcdConfig();
        $configuredDatabases = array_values(array_filter(array_map(static function ($db) {
            return trim((string) $db);
        }, (array) ($ncdConfig['databases'] ?? []))));
        if (!empty($configuredDatabases)) {
            return $configuredDatabases[0];
        }

        $defaultConnection = (string) config('database.default', 'mysql');
        $defaultDatabase = config("database.connections.{$defaultConnection}.database");
        $value = trim((string) ($defaultDatabase ?? $defaultConnection));
        return $value !== '' ? $value : 'mam';
    }

    private function allowedNcdDatabases(): array
    {
        $databases = [
            $this->defaultNcdDatabaseName(),
            'MAM_A',
            'MAM_B',
            'MAM_C1',
            'MAM_SPT',
            'MAM_SDG',
            'MAM_TL',
            'MAM_TBZY',
        ];
        return array_values(array_unique(array_filter($databases)));
    }

    private function buildNcdClinicOptions(): array
    {
        $ncdConfig = $this->loadNcdConfig();
        $configuredDatabases = array_values(array_filter(array_map(static function ($db) {
            return trim((string) $db);
        }, (array) ($ncdConfig['databases'] ?? []))));
        $defaultDatabase = $this->defaultNcdDatabaseName();
        $candidates = array_values(array_unique(array_merge(
            [$defaultDatabase],
            $configuredDatabases,
            $this->allowedNcdDatabases()
        )));
        $options = [];
        foreach ($candidates as $database) {
            $database = trim((string) $database);
            if ($database === '' || strtolower($database) === 'overall' || strtoupper($database) === 'ALL') {
                continue;
            }
            $options[$database] = strtoupper($database);
        }

        if (is_dir(base_path('outputs' . DIRECTORY_SEPARATOR . 'overall'))) {
            $options['overall'] = 'ALL';
        }

        return $options;
    }

    private function loadNcdConfig(): array
    {
        $configPath = base_path('config.yaml');
        if (!file_exists($configPath)) {
            return [];
        }

        if (class_exists('\\Symfony\\Component\\Yaml\\Yaml')) {
            try {
                $config = \Symfony\Component\Yaml\Yaml::parseFile($configPath);
            } catch (\Symfony\Component\Yaml\Exception\ParseException $exception) {
                Log::warning('Unable to read config.yaml for NCD settings: ' . $exception->getMessage());
                return [];
            }
            return is_array($config) ? $config : [];
        }

        if (function_exists('yaml_parse_file')) {
            $config = yaml_parse_file($configPath);
            return is_array($config) ? $config : [];
        }

        return $this->parseNcdConfigFallback($configPath);
    }

    private function parseNcdConfigFallback(string $path): array
    {
        $config = [
            'databases' => [],
            'date_range' => [],
            'thresholds' => [],
            'privacy' => [],
        ];

        $lines = file($path, FILE_IGNORE_NEW_LINES);
        if ($lines === false) {
            return $config;
        }

        $section = null;
        foreach ($lines as $line) {
            $trimmed = trim($line);
            if ($trimmed === '' || strpos($trimmed, '#') === 0) {
                continue;
            }

            if (preg_match('/^([A-Za-z0-9_]+):\s*$/', $line, $match)) {
                $section = $match[1];
                continue;
            }

            if ($section === 'databases' && preg_match('/^\s*-\s*(.+)$/', $line, $match)) {
                $config['databases'][] = $this->stripYamlValue($match[1]);
                continue;
            }

            if (in_array($section, ['date_range', 'thresholds', 'privacy'], true)
                && preg_match('/^\s*([A-Za-z0-9_]+):\s*(.+)$/', $line, $match)) {
                $key = $match[1];
                $value = $this->stripYamlValue($match[2]);
                if ($section === 'privacy' && $key === 'mask_ids') {
                    $value = in_array(strtolower($value), ['true', 'yes', '1'], true);
                }
                $config[$section][$key] = $value;
            }
        }

        return $config;
    }

    private function stripYamlValue(string $value): string
    {
        $value = preg_replace('/\s+#.*$/', '', $value ?? '');
        $value = trim($value);
        return trim($value, " \t\n\r\0\x0B'\"");
    }

    private function updateNcdConfigFile(string $path, array $updates): bool
    {
        $lines = file($path, FILE_IGNORE_NEW_LINES);
        if ($lines === false) {
            return false;
        }

        if (!empty($updates['databases'])) {
            $lines = $this->replaceSectionBlock($lines, 'databases', $this->renderDatabasesBlock($updates['databases']));
        }

        if (!empty($updates['date_range']['start_date'])) {
            $lines = $this->replaceSectionScalar(
                $lines,
                'date_range',
                'start_date',
                $this->formatYamlString($updates['date_range']['start_date'])
            );
        }
        if (!empty($updates['date_range']['end_date'])) {
            $lines = $this->replaceSectionScalar(
                $lines,
                'date_range',
                'end_date',
                $this->formatYamlString($updates['date_range']['end_date'])
            );
        }

        $thresholdKeys = ['active_days', 'ltfu_days', 'hba1c_lookback_months', 'kidney_lookback_months', 'cvd_risk_lookback_months'];
        foreach ($thresholdKeys as $key) {
            $value = $updates['thresholds'][$key] ?? null;
            if ($value !== null && $value !== false && $value > 0) {
                $lines = $this->replaceSectionScalar($lines, 'thresholds', $key, (string) $value);
            }
        }

        $maskIds = $updates['privacy']['mask_ids'] ?? false;
        $lines = $this->replaceSectionScalar($lines, 'privacy', 'mask_ids', $maskIds ? 'true' : 'false');

        $content = implode(PHP_EOL, $lines);
        if ($content !== '' && substr($content, -strlen(PHP_EOL)) !== PHP_EOL) {
            $content .= PHP_EOL;
        }

        return file_put_contents($path, $content) !== false;
    }

    private function renderDatabasesBlock(array $databases): array
    {
        $block = ['databases:'];
        foreach ($databases as $database) {
            $block[] = '- ' . $database;
        }
        return $block;
    }

    private function replaceSectionBlock(array $lines, string $section, array $block): array
    {
        $bounds = $this->findSectionBounds($lines, $section);
        if ($bounds === null) {
            return array_merge($lines, [''], $block);
        }

        [$start, $end] = $bounds;
        return array_merge(array_slice($lines, 0, $start), $block, array_slice($lines, $end));
    }

    private function replaceSectionScalar(array $lines, string $section, string $key, string $value): array
    {
        $bounds = $this->findSectionBounds($lines, $section);
        if ($bounds === null) {
            return array_merge($lines, [''], [$section . ':', '  ' . $key . ': ' . $value]);
        }

        [$start, $end] = $bounds;
        for ($idx = $start + 1; $idx < $end; $idx++) {
            if (preg_match('/^\s*' . preg_quote($key, '/') . '\s*:/', $lines[$idx])) {
                $lines[$idx] = '  ' . $key . ': ' . $value;
                return $lines;
            }
        }

        array_splice($lines, $start + 1, 0, '  ' . $key . ': ' . $value);
        return $lines;
    }

    private function findSectionBounds(array $lines, string $section): ?array
    {
        $total = count($lines);
        $start = null;
        for ($idx = 0; $idx < $total; $idx++) {
            if (preg_match('/^' . preg_quote($section, '/') . ':\s*$/', $lines[$idx])) {
                $start = $idx;
                break;
            }
        }

        if ($start === null) {
            return null;
        }

        $end = $total;
        for ($idx = $start + 1; $idx < $total; $idx++) {
            if (preg_match('/^[A-Za-z0-9_]+:\s*/', $lines[$idx])) {
                $end = $idx;
                break;
            }
        }

        return [$start, $end];
    }

    private function formatYamlString(string $value): string
    {
        $value = trim($value);
        if ($value === '') {
            return "''";
        }
        if (preg_match('/^["\'].*["\']$/', $value)) {
            return $value;
        }
        return "'" . str_replace("'", "''", $value) . "'";
    }

    private function loadNcdAnalytics(string $clinicConnection, array $filters = [], array $config = []): array
    {
        $clinicKey = $clinicConnection === 'ALL' ? 'overall' : $clinicConnection;
        $basePath = base_path('outputs');
        $clinicPath = $basePath . DIRECTORY_SEPARATOR . $clinicKey;

        if (!is_dir($clinicPath)) {
            return [
                'available' => false,
                'message' => 'No NCD analytics outputs found. Run the analysis first.',
            ];
        }

        $patientLatest = $this->readCsv($clinicPath . DIRECTORY_SEPARATOR . 'patient_latest.csv');
        $followups = $this->readCsv($clinicPath . DIRECTORY_SEPARATOR . 'followups_clean.csv');
        $registers = $this->readCsv($clinicPath . DIRECTORY_SEPARATOR . 'registers_clean.csv');
        $otherMeds = $this->readCsv($clinicPath . DIRECTORY_SEPARATOR . 'other_medications.csv');

        $filterOptions = $this->buildFilterOptions($patientLatest);
        $selectedFilters = $this->buildSelectedFilters($filters, $filterOptions, $followups, $config);

        $filteredPatients = $this->filterPatients($patientLatest, $selectedFilters);
        $filteredPatients = $this->filterRowsByDateRange(
            $filteredPatients,
            'visit_date',
            $selectedFilters['start_date'],
            $selectedFilters['end_date']
        );
        $filteredFollowups = $this->filterEventRows($followups, $selectedFilters);
        $filteredFollowups = $this->filterRowsByDateRange(
            $filteredFollowups,
            'visit_date',
            $selectedFilters['start_date'],
            $selectedFilters['end_date']
        );
        $filteredFollowups = $this->applyPatientTableGenderToFollowups($filteredFollowups, $clinicConnection);
        $filteredRegisters = $this->filterEventRows($registers, $selectedFilters);
        $filteredRegisters = $this->filterRowsByDateRange(
            $filteredRegisters,
            'reg_date',
            $selectedFilters['start_date'],
            $selectedFilters['end_date']
        );
        $bpComparisonFollowups = $this->filterEventRows($followups, $selectedFilters);
        $bpComparisonFollowups = $this->filterRowsByDateRange(
            $bpComparisonFollowups,
            'visit_date',
            null,
            $selectedFilters['end_date']
        );
        $bpComparisonFollowups = $this->applyPatientTableGenderToFollowups($bpComparisonFollowups, $clinicConnection);
        $bpComparisonRegisters = $this->filterEventRows($registers, $selectedFilters);
        $bpComparisonRegisters = $this->filterRowsByDateRange(
            $bpComparisonRegisters,
            'reg_date',
            null,
            $selectedFilters['end_date']
        );
        $glucoseComparisonFollowups = $this->filterEventRows($followups, $selectedFilters);
        $glucoseComparisonFollowups = $this->filterRowsByDateRange(
            $glucoseComparisonFollowups,
            'visit_date',
            null,
            $selectedFilters['end_date']
        );
        $glucoseComparisonFollowups = $this->applyPatientTableGenderToFollowups($glucoseComparisonFollowups, $clinicConnection);
        $glucoseComparisonRegisters = $this->filterEventRows($registers, $selectedFilters);
        $glucoseComparisonRegisters = $this->filterRowsByDateRange(
            $glucoseComparisonRegisters,
            'reg_date',
            null,
            $selectedFilters['end_date']
        );

        $thresholds = $config['thresholds'] ?? [];
        $activeDays = (int) ($thresholds['active_days'] ?? 90);
        $reportEndOverride = $this->resolveReportEndDate($selectedFilters['end_date'], $config, $filteredFollowups, $filteredPatients);
        $statusObserveTs = $this->parseDateValue($reportEndOverride) ?? time();
        $filteredPatients = $this->applyReportEndFlags($filteredPatients, $filteredFollowups, $thresholds, $reportEndOverride);
        $activePatientIds = $this->extractActivePatientIds($filteredPatients);

        $kpiCards = $this->buildKpiCardsFromPatients($filteredPatients, $activeDays);
        $controlRateDetails = $this->buildControlRateDetails($filteredPatients, $thresholds);
        $summaryRows = $this->buildSummaryRowsFromPatients($filteredPatients, $activeDays);
        $ageDistribution = $this->buildAgeDistribution($filteredRegisters);
        $diagnosisTrend = $this->buildDiagnosisTrendFromRegisters(
            $filteredRegisters,
            $selectedFilters['trend_granularity'] ?? 'yearly',
            $selectedFilters['trend_year'] ?? null
        );
        $yearlyDiagnosisTrend = $this->buildDiagnosisTrendFromRegisters($filteredRegisters, 'yearly');
        $followupCumulativeTrend = $this->buildFollowupCumulativeTrendByYear($yearlyDiagnosisTrend);
        $monthlySeries = $this->buildMonthlySeriesFromRows($filteredRegisters, $filteredFollowups);
        $bpTrendSeries = $this->buildBpTrendFromFollowups($filteredFollowups, $thresholds);
        $dmTrendSeries = $this->buildDmTrendFromFollowups($filteredFollowups, $thresholds);
        $sbpDistribution = $this->buildSbpDistribution($filteredFollowups);
        $visitPlanTrend = $this->buildClinicVisitPlanTrend($filteredFollowups, 7, 84, 2018);
        $returnToCareTrend = $this->buildReturnToCareTrend($filteredFollowups, 84);
        $controlStatus = $this->buildBpStageControlStatus($bpComparisonRegisters, $bpComparisonFollowups, $statusObserveTs);
        $glucoseStatusComparison = $this->buildGlucoseStatusComparison($glucoseComparisonRegisters, $glucoseComparisonFollowups, $statusObserveTs);
        $ltfuByAppointment = $this->buildLtfuByAppointmentChart($filteredFollowups, 84, $statusObserveTs);
        $qualitySummary = $this->buildQualitySummaryFromFollowups(
            $filteredFollowups,
            $reportEndOverride,
            $thresholds
        );
        $missedAppointmentStats = $this->buildMissedAppointmentVisitStats($filteredFollowups, $activePatientIds);
        $continuitySummary = $this->buildContinuityRateSummary($filteredPatients, $missedAppointmentStats);
        $dmTestBreakdown = $this->buildTestUsedSummary($filteredPatients);
        $medPatterns = $this->buildMedicationPatterns($filteredPatients);
        $visitIntervals = $this->buildVisitIntervals($filteredFollowups);
        $riskOutcome = $this->buildRiskOutcomeSummary($filteredPatients);
        $medChange = $this->buildMedicationChangeSummary($filteredPatients);
        $equity = $this->buildEquitySummary($filteredPatients);
        $referrals = $this->buildReferralSummary($filteredPatients);
        $controlImprovement = $this->buildControlImprovementSummary($filteredPatients, $filteredRegisters, $thresholds);
        $sustainedControl = $this->buildSustainedControlSummary($filteredFollowups, $thresholds);

        $otherMedsTop = $this->normalizeOtherMeds($this->limitRows($otherMeds, $selectedFilters['other_meds_top']));
        $lastUpdated = $this->latestCsvTimestamp($clinicPath);

        return [
            'available' => true,
            'lastUpdated' => $lastUpdated,
            'filterOptions' => $filterOptions,
            'filters' => $selectedFilters,
            'kpiCards' => $kpiCards,
            'summaryRows' => $summaryRows,
            'controlRateDetails' => $controlRateDetails,
            'ageDistribution' => $ageDistribution,
            'diagnosisTrend' => $diagnosisTrend,
            'followupCumulativeTrend' => $followupCumulativeTrend,
            'monthlySeries' => $monthlySeries,
            'bpTrendSeries' => $bpTrendSeries,
            'dmTrendSeries' => $dmTrendSeries,
            'sbpDistribution' => $sbpDistribution,
            'visitPlanTrend' => $visitPlanTrend,
            'returnToCareTrend' => $returnToCareTrend,
            'controlStatus' => $controlStatus,
            'glucoseStatusComparison' => $glucoseStatusComparison,
            'ltfuByAppointment' => $ltfuByAppointment,
            'qualitySummary' => $qualitySummary,
            'dmTestBreakdown' => $dmTestBreakdown,
            'continuitySummary' => $continuitySummary,
            'missedAppointmentStats' => $missedAppointmentStats,
            'medPatterns' => $medPatterns,
            'visitIntervals' => $visitIntervals,
            'riskOutcome' => $riskOutcome,
            'medChange' => $medChange,
            'equity' => $equity,
            'referrals' => $referrals,
            'otherMeds' => $otherMedsTop,
            'controlImprovement' => $controlImprovement,
            'sustainedControl' => $sustainedControl,
        ];
    }

    private function readCsv(string $path): array
    {
        if (!file_exists($path)) {
            return [];
        }

        $handle = fopen($path, 'r');
        if (!$handle) {
            return [];
        }

        $rows = [];
        $headers = fgetcsv($handle);
        if (!$headers) {
            fclose($handle);
            return [];
        }

        while (($data = fgetcsv($handle)) !== false) {
            $row = [];
            foreach ($headers as $idx => $header) {
                $row[$header] = $data[$idx] ?? null;
            }
            $rows[] = $row;
        }
        fclose($handle);

        return $rows;
    }

    private function buildFilterOptions(array $rows): array
    {
        return [
            'genders' => $this->uniqueValues($rows, 'gender'),
            'age_bands' => $this->uniqueValues($rows, 'age_band'),
        ];
    }

    private function buildSelectedFilters(array $filters, array $options, array $followups, array $config): array
    {
        $genderInput = $filters['gender'] ?? null;
        $ageBandInput = $filters['age_band'] ?? null;
        $selectedGenders = ($genderInput === null || $genderInput === '' || $genderInput === [])
            ? []
            : $this->coerceFilterValues($genderInput, $options['genders'] ?? []);
        $selectedAges = ($ageBandInput === null || $ageBandInput === '' || $ageBandInput === [])
            ? []
            : $this->coerceFilterValues($ageBandInput, $options['age_bands'] ?? []);
        $trendGranularity = strtolower(trim((string) ($filters['trend_granularity'] ?? 'yearly')));
        if (!in_array($trendGranularity, ['monthly', 'yearly'], true)) {
            $trendGranularity = 'yearly';
        }

        $bounds = $this->findDateBounds($followups, 'visit_date');
        $configStart = $this->parseDateValue($config['date_range']['start_date'] ?? null);
        $configEnd = $this->parseDateValue($config['date_range']['end_date'] ?? null);
        $defaultStart = $bounds['min'];
        $defaultEnd = $bounds['max'];
        if ($configStart !== null) {
            $defaultStart = $defaultStart !== null ? max($defaultStart, $configStart) : $configStart;
        }
        if ($configEnd !== null) {
            $defaultEnd = $defaultEnd !== null ? min($defaultEnd, $configEnd) : $configEnd;
        }

        $trendYearRaw = $filters['trend_year'] ?? null;
        $trendYear = filter_var($trendYearRaw, FILTER_VALIDATE_INT);
        if ($trendYear === false || $trendYear < 1900 || $trendYear > 2100) {
            $trendYear = null;
        }

        $timeframe = $filters['timeframe'] ?? 'all';
        $startTs = $this->parseDateValue($filters['start_date'] ?? null);
        $endTs = $this->parseDateValue($filters['end_date'] ?? null);

        if ($timeframe === 'before_2025') {
            $selectedStart = null;
            $selectedEnd = '2024-12-31';
        } elseif ($timeframe === 'year_2025') {
            $selectedStart = '2025-01-01';
            $selectedEnd = '2025-12-31';
        } elseif ($timeframe === 'all') {
            if ($startTs !== null || $endTs !== null) {
                $selectedStart = $startTs !== null ? date('Y-m-d', $startTs) : ($defaultStart !== null ? date('Y-m-d', $defaultStart) : null);
                $selectedEnd = $endTs !== null ? date('Y-m-d', $endTs) : ($defaultEnd !== null ? date('Y-m-d', $defaultEnd) : null);
                $timeframe = 'custom';
            } else {
                $selectedStart = null;
                $selectedEnd = null;
            }
        } else {
            $selectedStart = $startTs !== null ? date('Y-m-d', $startTs) : ($defaultStart !== null ? date('Y-m-d', $defaultStart) : null);
            $selectedEnd = $endTs !== null ? date('Y-m-d', $endTs) : ($defaultEnd !== null ? date('Y-m-d', $defaultEnd) : null);
            $timeframe = 'custom';
        }

        if ($trendYear === null) {
            $trendYearSourceTs = $this->parseDateValue($selectedEnd) ?? $defaultEnd;
            $trendYear = $trendYearSourceTs !== null ? (int) date('Y', $trendYearSourceTs) : (int) date('Y');
        }

        $top = 20;

        return [
            'genders' => $selectedGenders,
            'age_bands' => $selectedAges,
            'start_date' => $selectedStart,
            'end_date' => $selectedEnd,
            'other_meds_top' => $top,
            'timeframe' => $timeframe,
            'trend_granularity' => $trendGranularity,
            'trend_year' => $trendYear,
        ];
    }

    private function uniqueValues(array $rows, string $field): array
    {
        $values = [];
        foreach ($rows as $row) {
            $label = $this->normalizeCategory($row[$field] ?? null);
            if ($label === '') {
                continue;
            }
            $values[$label] = true;
        }
        $list = array_keys($values);
        sort($list, SORT_NATURAL | SORT_FLAG_CASE);
        return $list;
    }

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

    private function isUnknownValue($value): bool
    {
        $text = strtolower(trim((string) ($value ?? '')));
        if ($text === '') {
            return true;
        }
        return in_array($text, ['unknown', 'na', 'n/a', 'null', 'none', 'nan', '-'], true);
    }

    private function coerceFilterValues($input, array $options): array
    {
        if (empty($options)) {
            return [];
        }
        if ($input === null) {
            return $options;
        }
        $values = is_array($input) ? $input : [$input];
        $selected = [];
        $optionSet = array_flip($options);
        foreach ($values as $value) {
            $label = $this->normalizeCategory($value);
            if (isset($optionSet[$label])) {
                $selected[] = $label;
            }
        }
        $selected = array_values(array_unique($selected));
        return empty($selected) ? $options : $selected;
    }

    private function findDateBounds(array $rows, string $field): array
    {
        $min = null;
        $max = null;
        foreach ($rows as $row) {
            $ts = $this->parseDateValue($row[$field] ?? null);
            if ($ts === null) {
                continue;
            }
            $min = $min === null ? $ts : min($min, $ts);
            $max = $max === null ? $ts : max($max, $ts);
        }
        return ['min' => $min, 'max' => $max];
    }

    private function parseDateValue($value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }
        $ts = strtotime((string) $value);
        return $ts === false ? null : $ts;
    }

    private function filterPatients(array $rows, array $filters): array
    {
        $genders = $filters['genders'] ?? [];
        $ageBands = $filters['age_bands'] ?? [];

        if (empty($genders) && empty($ageBands)) {
            return $rows;
        }

        $filtered = [];
        foreach ($rows as $row) {
            $gender = $this->normalizeCategory($row['gender'] ?? null);
            $ageBand = $this->normalizeCategory($row['age_band'] ?? null);

            if (!empty($genders) && !in_array($gender, $genders, true)) {
                continue;
            }
            if (!empty($ageBands) && !in_array($ageBand, $ageBands, true)) {
                continue;
            }
            $filtered[] = $row;
        }

        return $filtered;
    }

    private function filterEventRows(array $rows, array $filters): array
    {
        $genders = $filters['genders'] ?? [];
        $ageBands = $filters['age_bands'] ?? [];

        if (empty($genders) && empty($ageBands)) {
            return $rows;
        }

        $filtered = [];
        foreach ($rows as $row) {
            if (!empty($genders)) {
                $gender = $this->normalizeGender($row['gender'] ?? null);
                if (!in_array($gender, $genders, true)) {
                    continue;
                }
            }

            if (!empty($ageBands)) {
                $age = $this->parseFloat($row['visit_age'] ?? ($row['visit_Age'] ?? ($row['Agey'] ?? null)));
                $ageBand = $this->buildAgeBandFromAge($age);
                if ($ageBand === null || !in_array($ageBand, $ageBands, true)) {
                    continue;
                }
            }

            $filtered[] = $row;
        }

        return $filtered;
    }

    private function buildAgeBandFromAge(?float $age): ?string
    {
        if ($age === null || $age < 0) {
            return null;
        }

        if ($age < 15) {
            return '0-14';
        }
        if ($age < 25) {
            return '15-24';
        }
        if ($age < 35) {
            return '25-34';
        }
        if ($age < 45) {
            return '35-44';
        }
        if ($age < 55) {
            return '45-54';
        }
        if ($age < 65) {
            return '55-64';
        }

        return '65-200';
    }

    private function extractPatientIds(array $rows): array
    {
        if (empty($rows)) {
            return [];
        }
        $idField = $this->detectIdField($rows, ['patient_id', 'pid']);
        $ids = [];
        foreach ($rows as $row) {
            $id = $this->normalizePatientId($row[$idField] ?? null);
            if ($id === null) {
                continue;
            }
            $ids[$id] = true;
        }
        return array_keys($ids);
    }

    private function extractActivePatientIds(array $rows): array
    {
        if (empty($rows)) {
            return [];
        }
        $idField = $this->detectIdField($rows, ['patient_id', 'pid']);
        $ids = [];
        foreach ($rows as $row) {
            if ($this->parseBool($row['active_patient'] ?? null)) {
                $id = $this->normalizePatientId($row[$idField] ?? null);
                if ($id === null) {
                    continue;
                }
                $ids[$id] = true;
            }
        }
        return array_keys($ids);
    }

    private function detectIdField(array $rows, array $candidates): string
    {
        $first = $rows[0] ?? [];
        foreach ($candidates as $field) {
            if (array_key_exists($field, $first)) {
                return $field;
            }
        }
        return $candidates[0];
    }

    private function filterRowsByPatientIds(array $rows, array $patientIds): array
    {
        if (empty($rows) || empty($patientIds)) {
            return $rows;
        }
        $idField = $this->detectIdField($rows, ['patient_id', 'pid']);
        $idSet = array_flip($patientIds);
        $filtered = [];
        foreach ($rows as $row) {
            $id = $this->normalizePatientId($row[$idField] ?? null);
            if ($id !== null && isset($idSet[$id])) {
                $filtered[] = $row;
            }
        }
        return $filtered;
    }

    private function normalizePatientId($value): ?string
    {
        if ($value === null) {
            return null;
        }
        $text = trim((string) $value);
        if ($text === '') {
            return null;
        }

        if (preg_match('/^\d+\.0+$/', $text)) {
            return preg_replace('/\.0+$/', '', $text);
        }

        if (is_numeric($text) && (str_contains($text, '.') || stripos($text, 'e') !== false)) {
            $numeric = (float) $text;
            if (is_finite($numeric) && floor($numeric) === $numeric) {
                return sprintf('%.0f', $numeric);
            }
        }

        return $text;
    }

    private function filterRowsByDateRange(array $rows, string $field, ?string $start, ?string $end): array
    {
        if (empty($rows)) {
            return $rows;
        }
        $startTs = $this->parseDateValue($start);
        $endTs = $this->parseDateValue($end);
        if ($startTs === null && $endTs === null) {
            return $rows;
        }
        if ($startTs === null) {
            $startTs = PHP_INT_MIN;
        }
        if ($endTs === null) {
            $endTs = PHP_INT_MAX;
        }
        $filtered = [];
        foreach ($rows as $row) {
            $ts = $this->parseDateValue($row[$field] ?? null);
            if ($ts === null) {
                continue;
            }
            if ($ts >= $startTs && $ts <= $endTs) {
                $filtered[] = $row;
            }
        }
        return $filtered;
    }

    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 applyPatientTableGenderToFollowups(array $followups, string $clinicConnection): array
    {
        if (empty($followups)) {
            return $followups;
        }

        $clinicKey = trim($clinicConnection);
        $isOverallClinic = strtolower($clinicKey) === 'overall' || strtoupper($clinicKey) === 'ALL';
        $idField = $this->detectIdField($followups, ['patient_id', 'pid']);
        $idsByDb = [];
        foreach ($followups as $row) {
            $pid = $this->normalizePatientId($row[$idField] ?? null);
            if ($pid === null) {
                continue;
            }
            $dbKey = $isOverallClinic
                ? trim((string) ($row['source_db'] ?? ''))
                : $clinicKey;
            if ($dbKey === '' || strtolower($dbKey) === 'overall' || strtoupper($dbKey) === 'ALL') {
                continue;
            }
            $idsByDb[$dbKey][$pid] = true;
        }

        if (empty($idsByDb)) {
            return $followups;
        }

        $genderMapByDb = [];
        foreach ($idsByDb as $dbKey => $idSet) {
            $genderMapByDb[$dbKey] = $this->fetchPatientGenderMapForNcd($dbKey, array_keys($idSet));
        }

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

            $dbKey = $isOverallClinic
                ? trim((string) ($row['source_db'] ?? ''))
                : $clinicKey;
            if ($dbKey === '' || strtolower($dbKey) === 'overall' || strtoupper($dbKey) === 'ALL') {
                $row['gender'] = $this->normalizeGender($row['gender'] ?? null);
                continue;
            }

            $patientGender = $genderMapByDb[$dbKey][$pid] ?? null;
            if (in_array($patientGender, ['Male', 'Female'], true)) {
                $row['gender'] = $patientGender;
            } else {
                // Fallback keeps chart resilient when patient row is missing for a follow-up PID.
                $row['gender'] = $this->normalizeGender($row['gender'] ?? null);
            }
        }
        unset($row);

        return $followups;
    }

    private function fetchPatientGenderMapForNcd(string $connection, array $patientIds): array
    {
        $connection = trim($connection);
        if ($connection === '' || empty($patientIds)) {
            return [];
        }

        $normalizedIds = [];
        foreach ($patientIds as $id) {
            $normalized = $this->normalizePatientId($id);
            if ($normalized !== null) {
                $normalizedIds[$normalized] = true;
            }
        }
        if (empty($normalizedIds)) {
            return [];
        }
        $targetIds = array_keys($normalizedIds);

        $hashedCount = 0;
        foreach ($targetIds as $id) {
            if ($this->looksLikeSha256Id($id)) {
                $hashedCount++;
            }
        }
        if ($hashedCount > 0 && $hashedCount === count($targetIds)) {
            return $this->fetchPatientGenderMapForHashedIds($connection, $targetIds);
        }

        $map = [];
        foreach (array_chunk($targetIds, 1000) as $chunk) {
            try {
                $rows = DB::connection($connection)
                    ->table('patients')
                    ->select('Pid', 'Gender')
                    ->whereIn('Pid', $chunk)
                    ->get();
            } catch (\Throwable $exception) {
                Log::warning('Unable to load patient gender map for NCD cumulative follow-up trend', [
                    'connection' => $connection,
                    'error' => $exception->getMessage(),
                ]);
                break;
            }

            foreach ($rows as $row) {
                $pid = $this->normalizePatientId($row->Pid ?? null);
                if ($pid === null) {
                    continue;
                }
                $rawGender = $row->Gender ?? null;
                $decryptedGender = $this->decryptGeneralValue($rawGender);
                $gender = $this->normalizeGender($decryptedGender ?? $rawGender);
                if (in_array($gender, ['Male', 'Female'], true)) {
                    $map[$pid] = $gender;
                }
            }
        }

        return $map;
    }

    private function fetchPatientGenderMapForHashedIds(string $connection, array $hashedIds): array
    {
        $salt = $this->ncdPrivacyIdSalt();
        if ($salt === '') {
            Log::warning('NCD gender map: privacy.id_salt is empty, cannot map hashed patient IDs', [
                'connection' => $connection,
            ]);
            return [];
        }

        $targetSet = array_fill_keys($hashedIds, true);
        $map = [];
        try {
            DB::connection($connection)
                ->table('patients')
                ->select('Pid', 'Gender')
                ->orderBy('Pid')
                ->chunk(2000, function ($rows) use (&$map, $targetSet, $salt) {
                    foreach ($rows as $row) {
                        $pid = $this->normalizePatientId($row->Pid ?? null);
                        if ($pid === null) {
                            continue;
                        }
                        $hashedPid = $this->hashNcdIdForPrivacy($pid, $salt);
                        if ($hashedPid === null || !isset($targetSet[$hashedPid])) {
                            continue;
                        }

                        $rawGender = $row->Gender ?? null;
                        $decryptedGender = $this->decryptGeneralValue($rawGender);
                        $gender = $this->normalizeGender($decryptedGender ?? $rawGender);
                        if (in_array($gender, ['Male', 'Female'], true)) {
                            $map[$hashedPid] = $gender;
                        }
                    }
                });
        } catch (\Throwable $exception) {
            Log::warning('Unable to load hashed patient gender map for NCD cumulative follow-up trend', [
                'connection' => $connection,
                'error' => $exception->getMessage(),
            ]);
        }

        return $map;
    }

    private function ncdPrivacyIdSalt(): string
    {
        $config = $this->loadNcdConfig();
        return trim((string) ($config['privacy']['id_salt'] ?? ''));
    }

    private function hashNcdIdForPrivacy($value, string $salt): ?string
    {
        $canonical = $this->normalizePatientId($value);
        if ($canonical === null || $salt === '') {
            return null;
        }
        return hash('sha256', $salt . ':' . $canonical);
    }

    private function looksLikeSha256Id($value): bool
    {
        $text = trim((string) ($value ?? ''));
        if ($text === '') {
            return false;
        }
        return (bool) preg_match('/^[a-f0-9]{64}$/i', $text);
    }

    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 parseFloat($value): ?float
    {
        if ($value === null) {
            return null;
        }
        $text = trim((string) $value);
        if ($this->isUnknownValue($text)) {
            return null;
        }
        if (!is_numeric($text)) {
            return null;
        }
        return (float) $text;
    }

    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;
    }

    private function parseBool($value): bool
    {
        if ($value === null) {
            return false;
        }
        $text = strtolower(trim((string) $value));
        return in_array($text, ['1', 'true', 'yes', 'y'], true);
    }

    private function hasValue($value): bool
    {
        if ($value === null) {
            return false;
        }
        $text = trim((string) $value);
        return !$this->isUnknownValue($text);
    }

    private function latestCsvTimestamp(string $clinicPath): ?string
    {
        $latest = null;
        foreach (glob($clinicPath . DIRECTORY_SEPARATOR . '*.csv') as $file) {
            $ts = filemtime($file);
            if ($ts && ($latest === null || $ts > $latest)) {
                $latest = $ts;
            }
        }
        return $latest ? date('Y-m-d H:i', $latest) : null;
    }

    private function detectCsvHeaders(array $rows): array
    {
        $headers = [];
        foreach ($rows as $row) {
            foreach (array_keys((array) $row) as $key) {
                if (!in_array($key, $headers, true)) {
                    $headers[] = $key;
                }
            }
        }
        return $headers;
    }

    private function rowsToCsvString(array $rows, array $headers = []): string
    {
        if (empty($headers)) {
            $headers = $this->detectCsvHeaders($rows);
        }

        $handle = fopen('php://temp', 'w+');
        if (!empty($headers)) {
            fputcsv($handle, $headers);
        }
        foreach ($rows as $row) {
            $line = [];
            foreach ($headers as $header) {
                $line[] = $row[$header] ?? '';
            }
            fputcsv($handle, $line);
        }
        rewind($handle);
        $content = stream_get_contents($handle);
        fclose($handle);
        return $content !== false ? $content : '';
    }

    private function buildXlsxWorkbook(array $sheets): string
    {
        $spreadsheet = new Spreadsheet();
        $usedTitles = [];
        $sheetIndex = 0;

        foreach ($sheets as $sheetSpec) {
            $title = (string) ($sheetSpec['title'] ?? ('Sheet' . ($sheetIndex + 1)));
            $rows = is_array($sheetSpec['rows'] ?? null) ? $sheetSpec['rows'] : [];
            $headers = is_array($sheetSpec['headers'] ?? null) ? $sheetSpec['headers'] : [];
            if (empty($headers)) {
                $headers = $this->detectCsvHeaders($rows);
            }

            $sheet = $sheetIndex === 0 ? $spreadsheet->getActiveSheet() : $spreadsheet->createSheet();
            $sheet->setTitle($this->uniqueSheetTitle($title, $usedTitles));

            if (!empty($headers)) {
                $sheet->fromArray($headers, null, 'A1');
                $dataRows = [];
                foreach ($rows as $row) {
                    $line = [];
                    foreach ($headers as $header) {
                        $line[] = $row[$header] ?? '';
                    }
                    $dataRows[] = $line;
                }
                if (!empty($dataRows)) {
                    $sheet->fromArray($dataRows, null, 'A2');
                }
                $sheet->freezePane('A2');
                $sheet->setAutoFilter($sheet->calculateWorksheetDimension());
            }

            $sheetIndex++;
        }

        if ($sheetIndex === 0) {
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Sheet1');
            $sheet->fromArray(['empty'], null, 'A1');
            $sheet->fromArray([['no data']], null, 'A2');
        }

        $spreadsheet->setActiveSheetIndex(0);
        $tmpFile = tempnam(sys_get_temp_dir(), 'ncd_charts_');
        if ($tmpFile === false) {
            return '';
        }

        $xlsxFile = $tmpFile . '.xlsx';
        @rename($tmpFile, $xlsxFile);

        try {
            $writer = new Xlsx($spreadsheet);
            $writer->save($xlsxFile);
            $content = file_get_contents($xlsxFile);
            return $content !== false ? $content : '';
        } catch (\Throwable $e) {
            return '';
        } finally {
            $spreadsheet->disconnectWorksheets();
            if (file_exists($xlsxFile)) {
                @unlink($xlsxFile);
            }
            if (file_exists($tmpFile)) {
                @unlink($tmpFile);
            }
        }
    }

    private function uniqueSheetTitle(string $title, array &$usedTitles): string
    {
        $base = $this->sanitizeSheetTitle($title);
        if (!in_array($base, $usedTitles, true)) {
            $usedTitles[] = $base;
            return $base;
        }

        $counter = 1;
        do {
            $suffix = '_' . $counter;
            $trimLength = max(1, 31 - strlen($suffix));
            $candidate = substr($base, 0, $trimLength) . $suffix;
            $counter++;
        } while (in_array($candidate, $usedTitles, true));

        $usedTitles[] = $candidate;
        return $candidate;
    }

    private function sanitizeSheetTitle(string $title): string
    {
        $clean = preg_replace('/[\\\\\\/\\?\\*\\:\\[\\]]/', '_', trim($title));
        if (!is_string($clean) || $clean === '') {
            $clean = 'Sheet';
        }
        if (strlen($clean) > 31) {
            $clean = substr($clean, 0, 31);
        }
        return $clean;
    }

    private function buildNcdSourceDataQuality(array $registers, array $followups, array $config): array
    {
        $qualityConfig = $config['data_quality'] ?? [];
        $minValidTs = $this->parseDateValue($qualityConfig['min_valid_date'] ?? null);
        $maxValidTs = $this->parseDateValue($qualityConfig['max_valid_date'] ?? null);

        $specsByTable = [
            'ncd_pt_registers' => [
                [
                    'column' => 'patient_id',
                    'type' => 'id',
                    'used_in' => 'patient linkage',
                    'validation_rule' => 'Non-empty normalized patient ID',
                ],
                [
                    'column' => 'reg_date',
                    'type' => 'date',
                    'used_in' => 'cohort trends, baseline timing',
                    'validation_rule' => 'Parseable date within configured min/max valid date',
                ],
                [
                    'column' => 'visit_age',
                    'type' => 'numeric_range',
                    'min' => 1,
                    'max' => 120,
                    'used_in' => 'age distribution',
                    'validation_rule' => 'Numeric age in range 1-120',
                ],
                [
                    'column' => 'gender',
                    'type' => 'gender',
                    'used_in' => 'equity and gender trends',
                    'validation_rule' => 'Recognized male/female value',
                ],
                [
                    'column' => 'first_bp',
                    'type' => 'bp',
                    'used_in' => 'baseline BP/control improvement',
                    'validation_rule' => 'BP format X/Y with SBP 50-300 and DBP 30-200',
                ],
                [
                    'column' => 'date_of_birth',
                    'type' => 'date',
                    'used_in' => 'age derivation quality checks',
                    'validation_rule' => 'Parseable date within configured min/max valid date',
                ],
                [
                    'column' => 'age_at_reg',
                    'type' => 'numeric_range',
                    'min' => 0,
                    'max' => 120,
                    'used_in' => 'age quality checks',
                    'validation_rule' => 'Numeric age in range 0-120',
                ],
            ],
            'ncd_followups' => [
                [
                    'column' => 'patient_id',
                    'type' => 'id',
                    'used_in' => 'patient linkage',
                    'validation_rule' => 'Non-empty normalized patient ID',
                ],
                [
                    'column' => 'visit_date',
                    'type' => 'date',
                    'used_in' => 'all visit trends and continuity metrics',
                    'validation_rule' => 'Parseable date within configured min/max valid date',
                ],
                [
                    'column' => 'next_appointment',
                    'type' => 'date',
                    'used_in' => 'visit plan, LTFU, missed appointment logic',
                    'validation_rule' => 'Parseable date within configured min/max valid date',
                ],
                [
                    'column' => 'bp_raw',
                    'type' => 'bp',
                    'used_in' => 'BP control and stage transitions',
                    'validation_rule' => 'BP format X/Y with SBP 50-300 and DBP 30-200',
                ],
                [
                    'column' => 'sbp',
                    'type' => 'numeric_range',
                    'min' => 50,
                    'max' => 300,
                    'used_in' => 'BP control and stage transitions',
                    'validation_rule' => 'Numeric SBP in range 50-300',
                ],
                [
                    'column' => 'dbp',
                    'type' => 'numeric_range',
                    'min' => 30,
                    'max' => 200,
                    'used_in' => 'BP control and stage transitions',
                    'validation_rule' => 'Numeric DBP in range 30-200',
                ],
                [
                    'column' => 'hba1c',
                    'type' => 'numeric_range',
                    'min' => 2,
                    'max' => 25,
                    'used_in' => 'DM control hierarchy, quality-of-care',
                    'validation_rule' => 'Numeric HbA1c in range 2-25',
                ],
                [
                    'column' => 't2hpp',
                    'type' => 'numeric_range',
                    'min' => 20,
                    'max' => 600,
                    'used_in' => 'DM control hierarchy',
                    'validation_rule' => 'Numeric 2HPP in range 20-600',
                ],
                [
                    'column' => 'fbs',
                    'type' => 'numeric_range',
                    'min' => 20,
                    'max' => 600,
                    'used_in' => 'DM control hierarchy',
                    'validation_rule' => 'Numeric FBS in range 20-600',
                ],
                [
                    'column' => 'rbs_result',
                    'type' => 'numeric_range',
                    'min' => 20,
                    'max' => 1000,
                    'used_in' => 'legacy DM control (RBS)',
                    'validation_rule' => 'Numeric RBS in range 20-1000',
                ],
                [
                    'column' => 'creatinine',
                    'type' => 'numeric_range',
                    'min' => 0,
                    'max' => 30,
                    'used_in' => 'kidney monitoring coverage',
                    'validation_rule' => 'Numeric creatinine in range 0-30',
                ],
                [
                    'column' => 'crcl',
                    'type' => 'numeric_range',
                    'min' => 0,
                    'max' => 300,
                    'used_in' => 'kidney monitoring coverage',
                    'validation_rule' => 'Numeric CRCL in range 0-300',
                ],
                [
                    'column' => 'uring_ac_ratio',
                    'type' => 'numeric_range',
                    'min' => 0,
                    'max' => 10000,
                    'used_in' => 'kidney monitoring coverage',
                    'validation_rule' => 'Numeric urine A/C ratio in range 0-10000',
                ],
                [
                    'column' => 'cvd_risk',
                    'type' => 'numeric_range',
                    'min' => 0,
                    'max' => 100,
                    'used_in' => 'risk stratification',
                    'validation_rule' => 'Numeric CVD risk in range 0-100',
                ],
                [
                    'column' => 'ncd_diagnosis',
                    'type' => 'diagnosis',
                    'used_in' => 'follow-up diagnosis trends',
                    'validation_rule' => 'Contains HTN/DM/Both category text',
                ],
                [
                    'column' => 'medication_changed',
                    'type' => 'bool_like',
                    'used_in' => 'operations indicators',
                    'validation_rule' => 'Boolean-like value (yes/no/true/false/1/0)',
                ],
                [
                    'column' => 'gender',
                    'type' => 'gender',
                    'used_in' => 'equity and gender trends',
                    'validation_rule' => 'Recognized male/female value',
                ],
                [
                    'column' => 'visit_age',
                    'type' => 'numeric_range',
                    'min' => 1,
                    'max' => 120,
                    'used_in' => 'age-linked visit analytics',
                    'validation_rule' => 'Numeric age in range 1-120',
                ],
                [
                    'column' => 'outcome',
                    'type' => 'free_text',
                    'used_in' => 'exit/LTFU classification',
                    'validation_rule' => 'Free text; missing tracked only',
                ],
            ],
        ];

        $allSummary = [];
        $allTopInvalid = [];
        $usedColumns = [];
        $tables = [
            'ncd_pt_registers' => $registers,
            'ncd_followups' => $followups,
        ];

        foreach ($tables as $sourceTable => $rows) {
            $specs = $specsByTable[$sourceTable] ?? [];
            $tableQuality = $this->buildColumnQualityForTable($sourceTable, $rows, $specs, $minValidTs, $maxValidTs);
            $allSummary = array_merge($allSummary, $tableQuality['summary']);
            $allTopInvalid = array_merge($allTopInvalid, $tableQuality['top_invalid']);
            $usedColumns = array_merge($usedColumns, $tableQuality['used_columns']);
        }

        usort($allSummary, static function ($a, $b) {
            return [$a['source_table'], $a['column_name']] <=> [$b['source_table'], $b['column_name']];
        });
        usort($allTopInvalid, static function ($a, $b) {
            $countDiff = ((int) ($b['count'] ?? 0)) <=> ((int) ($a['count'] ?? 0));
            if ($countDiff !== 0) {
                return $countDiff;
            }
            return [$a['source_table'], $a['column_name'], $a['invalid_reason']] <=> [$b['source_table'], $b['column_name'], $b['invalid_reason']];
        });

        return [
            'used_columns' => $usedColumns,
            'summary' => $allSummary,
            'top_invalid' => $allTopInvalid,
        ];
    }

    private function buildColumnQualityForTable(
        string $sourceTable,
        array $rows,
        array $specs,
        ?int $minValidTs,
        ?int $maxValidTs
    ): array {
        $totalRows = count($rows);
        $summary = [];
        $topInvalid = [];
        $usedColumns = [];
        $firstRow = $rows[0] ?? [];

        foreach ($specs as $spec) {
            $column = (string) ($spec['column'] ?? '');
            if ($column === '') {
                continue;
            }
            $type = (string) ($spec['type'] ?? 'free_text');
            $usedColumns[] = [
                'source_table' => $sourceTable,
                'column_name' => $column,
                'field_type' => $type,
                'used_in' => (string) ($spec['used_in'] ?? ''),
                'validation_rule' => (string) ($spec['validation_rule'] ?? ''),
            ];

            $columnPresent = empty($rows) ? true : array_key_exists($column, $firstRow);
            $missing = 0;
            $nonMissing = 0;
            $valid = 0;
            $invalid = 0;
            $invalidValueCounts = [];

            if (!$columnPresent) {
                $summary[] = [
                    'source_table' => $sourceTable,
                    'column_name' => $column,
                    'field_type' => $type,
                    'column_present' => 0,
                    'total_rows' => $totalRows,
                    'non_missing' => 0,
                    'missing' => $totalRows,
                    'missing_pct' => $totalRows > 0 ? 100.0 : 0.0,
                    'valid' => 0,
                    'invalid' => 0,
                    'invalid_pct' => 0.0,
                ];
                continue;
            }

            foreach ($rows as $row) {
                $raw = $row[$column] ?? null;
                if ($this->isUnknownValue($raw)) {
                    $missing++;
                    continue;
                }
                $nonMissing++;

                $validation = $this->validateQualityValue($raw, $spec, $minValidTs, $maxValidTs);
                if ($validation['valid']) {
                    $valid++;
                    continue;
                }

                $invalid++;
                $reason = (string) ($validation['reason'] ?? 'invalid_value');
                $label = trim((string) $raw);
                if ($label === '') {
                    $label = '[blank]';
                }
                if (strlen($label) > 160) {
                    $label = substr($label, 0, 157) . '...';
                }
                $key = $reason . '||' . $label;
                $invalidValueCounts[$key] = ($invalidValueCounts[$key] ?? 0) + 1;
            }

            $summary[] = [
                'source_table' => $sourceTable,
                'column_name' => $column,
                'field_type' => $type,
                'column_present' => 1,
                'total_rows' => $totalRows,
                'non_missing' => $nonMissing,
                'missing' => $missing,
                'missing_pct' => $totalRows > 0 ? round(($missing / $totalRows) * 100, 2) : 0.0,
                'valid' => $valid,
                'invalid' => $invalid,
                'invalid_pct' => $nonMissing > 0 ? round(($invalid / $nonMissing) * 100, 2) : 0.0,
            ];

            if (!empty($invalidValueCounts)) {
                arsort($invalidValueCounts);
                foreach (array_slice($invalidValueCounts, 0, 10, true) as $key => $count) {
                    [$reason, $label] = explode('||', $key, 2);
                    $topInvalid[] = [
                        'source_table' => $sourceTable,
                        'column_name' => $column,
                        'field_type' => $type,
                        'invalid_reason' => $reason,
                        'invalid_value' => $label,
                        'count' => $count,
                    ];
                }
            }
        }

        return [
            'used_columns' => $usedColumns,
            'summary' => $summary,
            'top_invalid' => $topInvalid,
        ];
    }

    private function validateQualityValue($raw, array $spec, ?int $minValidTs, ?int $maxValidTs): array
    {
        $type = strtolower(trim((string) ($spec['type'] ?? 'free_text')));
        if ($type === 'free_text') {
            return ['valid' => true, 'reason' => null];
        }

        if ($type === 'id') {
            return $this->normalizePatientId($raw) !== null
                ? ['valid' => true, 'reason' => null]
                : ['valid' => false, 'reason' => 'invalid_id'];
        }

        if ($type === 'date') {
            $ts = $this->parseDateValue($raw);
            if ($ts === null) {
                return ['valid' => false, 'reason' => 'invalid_date_format'];
            }
            if ($minValidTs !== null && $ts < $minValidTs) {
                return ['valid' => false, 'reason' => 'before_min_valid_date'];
            }
            if ($maxValidTs !== null && $ts > $maxValidTs) {
                return ['valid' => false, 'reason' => 'after_max_valid_date'];
            }
            return ['valid' => true, 'reason' => null];
        }

        if ($type === 'bp') {
            $text = trim((string) $raw);
            if (!preg_match('/^\s*\d{2,3}\s*\/\s*\d{2,3}\s*$/', $text)) {
                return ['valid' => false, 'reason' => 'invalid_bp_format'];
            }
            $parts = preg_split('/\s*\/\s*/', $text);
            if (!$parts || count($parts) < 2) {
                return ['valid' => false, 'reason' => 'invalid_bp_format'];
            }
            $sbp = (int) $parts[0];
            $dbp = (int) $parts[1];
            if ($sbp < 50 || $sbp > 300 || $dbp < 30 || $dbp > 200) {
                return ['valid' => false, 'reason' => 'bp_out_of_range'];
            }
            return ['valid' => true, 'reason' => null];
        }

        if ($type === 'numeric' || $type === 'numeric_range') {
            $num = $this->parseFloat($raw);
            if ($num === null) {
                return ['valid' => false, 'reason' => 'invalid_numeric'];
            }
            if (isset($spec['min']) && $num < (float) $spec['min']) {
                return ['valid' => false, 'reason' => 'numeric_below_min'];
            }
            if (isset($spec['max']) && $num > (float) $spec['max']) {
                return ['valid' => false, 'reason' => 'numeric_above_max'];
            }
            return ['valid' => true, 'reason' => null];
        }

        if ($type === 'gender') {
            return in_array($this->normalizeGender($raw), ['Male', 'Female'], true)
                ? ['valid' => true, 'reason' => null]
                : ['valid' => false, 'reason' => 'invalid_gender'];
        }

        if ($type === 'diagnosis') {
            $category = $this->categorizeFollowupDiagnosis($raw);
            return in_array($category, ['hypertension', 'diabetes', 'both'], true)
                ? ['valid' => true, 'reason' => null]
                : ['valid' => false, 'reason' => 'non_target_diagnosis'];
        }

        if ($type === 'bool_like') {
            $text = strtolower(trim((string) $raw));
            if (in_array($text, ['1', '0', 'true', 'false', 'yes', 'no', 'y', 'n'], true)) {
                return ['valid' => true, 'reason' => null];
            }
            return ['valid' => false, 'reason' => 'invalid_boolean_like'];
        }

        return ['valid' => true, 'reason' => null];
    }

    private function flattenSeriesChartRows(array $dataset, string $dimensionName = 'period', array $extra = []): array
    {
        $labels = $dataset['labels'] ?? [];
        $series = $dataset['series'] ?? [];
        if (!is_array($labels) || !is_array($series)) {
            return [];
        }
        $rows = [];
        foreach ($labels as $idx => $label) {
            foreach ($series as $item) {
                $name = $item['label'] ?? $item['key'] ?? 'Series';
                $value = $item['data'][$idx] ?? null;
                if ($value === null) {
                    continue;
                }
                $row = array_merge([
                    $dimensionName => $label,
                    'series' => $name,
                    'value' => is_numeric($value) ? (float) $value : $value,
                ], $extra);
                $rows[] = $row;
            }
        }
        return $rows;
    }

    private function flattenTitleValueRows(array $rows, string $dimensionName = 'label'): array
    {
        $output = [];
        foreach ($rows as $row) {
            $label = $row['title'] ?? $row['label'] ?? null;
            if ($label === null || $label === '') {
                continue;
            }
            $output[] = [
                $dimensionName => $label,
                'value' => is_numeric($row['value'] ?? null) ? (float) $row['value'] : ($row['value'] ?? ''),
            ];
        }
        return $output;
    }

    private function flattenMetricValueRows(array $rows): array
    {
        $output = [];
        foreach ($rows as $row) {
            $metric = $row['metric'] ?? $row['title'] ?? null;
            if ($metric === null || $metric === '') {
                continue;
            }
            $output[] = [
                'metric' => $metric,
                'value' => is_numeric($row['value'] ?? null) ? (float) $row['value'] : ($row['value'] ?? ''),
            ];
        }
        return $output;
    }

    private function flattenGenderChartRows(array $chart): array
    {
        $labels = $chart['labels'] ?? [];
        $series = $chart['series'] ?? [];
        if (!is_array($labels) || !is_array($series)) {
            return [];
        }
        $rows = [];
        foreach ($labels as $idx => $label) {
            foreach ($series as $item) {
                $name = $item['label'] ?? $item['key'] ?? 'Series';
                $value = $item['data'][$idx] ?? null;
                if ($value === null) {
                    continue;
                }
                $rows[] = [
                    'status' => $label,
                    'series' => $name,
                    'value' => is_numeric($value) ? (float) $value : $value,
                ];
            }
        }
        return $rows;
    }

    private function flattenControlMatrixRows(array $control): array
    {
        $stages = $control['stages'] ?? [];
        $matrix = $control['matrix'] ?? [];
        if (!is_array($stages) || !is_array($matrix)) {
            return [];
        }
        $rows = [];
        foreach ($stages as $fromStage) {
            foreach ($stages as $toStage) {
                $rows[] = [
                    'baseline_stage' => $fromStage,
                    'latest_stage' => $toStage,
                    'value' => (int) (($matrix[$fromStage][$toStage] ?? 0)),
                ];
            }
        }
        return $rows;
    }

    private function limitRows(array $rows, int $limit): array
    {
        if (count($rows) <= $limit) {
            return $rows;
        }
        return array_slice($rows, 0, $limit);
    }

    private function decryptGeneralValue($raw): ?string
    {
        $value = is_string($raw) ? trim($raw) : null;
        if ($value === null || $value === '') {
            return null;
        }
        try {
            return Crypt::decrypt_light($value, 'General');
        } catch (\Throwable $e) {
            return $value;
        }
    }

    private function normalizeGender($raw): string
    {
        $value = strtolower(trim((string) ($raw ?? '')));
        if ($value === '') {
            return 'Unknown';
        }
        if (in_array($value, ['f', 'female', 'woman', 'girl'], true) || str_contains($value, 'female')) {
            return 'Female';
        }
        if (in_array($value, ['m', 'male', 'man', 'boy'], true) || preg_match('/\\bmale\\b/', $value)) {
            return 'Male';
        }
        return 'Unknown';
    }
}