<?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 ExportsNcdAnalysisData
{
    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']
        );
        $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 = $ncdConfig['thresholds'] ?? [];
        $reportEndOverride = $this->resolveReportEndDate($selectedFilters['end_date'], $ncdConfig, $filteredFollowups, $filteredPatients);
        $observeTs = $this->parseDateValue($reportEndOverride) ?? time();
        $filteredPatients = $this->applyReportEndFlags($filteredPatients, $filteredFollowups, $thresholds, $reportEndOverride);

        $files = [];
        $generatedAt = now()->toDateTimeString();
        $files['README.txt'] = $this->buildNcdDataPackReadme($generatedAt, $clinicConnection);

        $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/bp_stage_comparison_logic.txt'] = $this->buildBpStageComparisonLogicText();
        $files['references/glucose_status_comparison_logic.txt'] = $this->buildGlucoseStatusComparisonLogicText();

        $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' => 'evidence/bp_stage_comparison_evidence.csv', 'description' => 'Patient-level BP stage comparison evidence for the total hypertension cohort, including baseline, latest record, comparison status, reasons, and care status'],
            ['dataset' => 'evidence/glucose_status_comparison_evidence.csv', 'description' => 'Patient-level glucose status comparison evidence for the total glucose cohort, including baseline, latest record, transition label, reasons, and care status'],
            ['dataset' => 'evidence/comparison_evidence.xlsx', 'description' => 'Workbook containing BP and glucose comparison evidence sheets'],
            ['dataset' => 'references/bp_stage_comparison_logic.txt', 'description' => 'Current BP stage comparison cohort rules, stage logic, invalid handling, and transition counting'],
            ['dataset' => 'references/glucose_status_comparison_logic.txt', 'description' => 'Current glucose status comparison cohort rules, thresholds, truth table, and transition mapping'],
            ['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));

        $bpEvidenceRows = $this->buildBpStageEvidenceRows($bpComparisonRegisters, $bpComparisonFollowups, $observeTs);
        $glucoseEvidenceRows = $this->buildGlucoseStatusEvidenceRows($glucoseComparisonRegisters, $glucoseComparisonFollowups, $observeTs);
        $bpEvidenceHeaders = [
            'patient_id',
            'care_status',
            'baseline_date',
            'baseline_source',
            'baseline_raw',
            'baseline_stage',
            'baseline_status',
            'baseline_reason',
            'last_record_date',
            'last_record_source',
            'last_record_raw',
            'last_record_stage',
            'last_record_status',
            'last_record_reason',
            'comparison_status',
            'comparison_reason',
        ];
        $glucoseEvidenceHeaders = [
            'patient_id',
            'care_status',
            'baseline_date',
            'baseline_source',
            'baseline_age',
            'baseline_data',
            'baseline_status',
            'baseline_control_state',
            'baseline_reason',
            'last_record_date',
            'last_record_window_start',
            'last_record_source',
            'last_record_age',
            'last_record_data',
            'last_record_status',
            'last_record_control_state',
            'last_record_reason',
            'comparison_status',
            'comparison_reason',
        ];
        $files['evidence/bp_stage_comparison_evidence.csv'] = $this->rowsToCsvString($bpEvidenceRows, $bpEvidenceHeaders);
        $files['evidence/glucose_status_comparison_evidence.csv'] = $this->rowsToCsvString($glucoseEvidenceRows, $glucoseEvidenceHeaders);
        $comparisonEvidenceWorkbook = $this->buildXlsxWorkbook([
            ['title' => 'BpStageEvidence', 'rows' => $bpEvidenceRows, 'headers' => $bpEvidenceHeaders],
            ['title' => 'GlucoseEvidence', 'rows' => $glucoseEvidenceRows, 'headers' => $glucoseEvidenceHeaders],
        ]);
        if ($comparisonEvidenceWorkbook !== '') {
            $files['evidence/comparison_evidence.xlsx'] = $comparisonEvidenceWorkbook;
        }

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

    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 buildNcdDataPackReadme(string $generatedAt, string $clinicConnection): string
    {
        return 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.',
            '- evidence/*.csv and evidence/comparison_evidence.xlsx: patient-level evidence rows for BP stage and glucose comparison outputs.',
            '- data_quality/*: used columns, column-level quality summary, and top invalid values.',
            '- references/bp_stage_comparison_logic.txt: current BP stage comparison flow, stage rules, invalid handling, and transition counting.',
            '- references/glucose_status_comparison_logic.txt: current glucose comparison flow, thresholds, truth table, and transition mapping.',
            '',
            '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/followups_clean_filtered.csv',
            '   - Logic reference: references/bp_stage_comparison_logic.txt.',
            '   - 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 uses the latest valid follow-up bp_raw on or before observe date.',
            '   - 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.',
            '',
            '10) Glucose status comparison',
            '   - Sources: source/registers_clean_filtered.csv + source/followups_clean_filtered.csv.',
            '   - Logic reference: references/glucose_status_comparison_logic.txt.',
            '   - Labels: Improved / Maintaining controlled / Worsen / Remain uncontrolled / Unavailable for comparison.',
            '',
            '11) Evidence exports',
            '   - evidence/bp_stage_comparison_evidence.csv: one row per hypertension cohort patient with baseline BP, latest valid follow-up BP, care status, comparison status, and reasons.',
            '   - evidence/glucose_status_comparison_evidence.csv: one row per glucose cohort patient with baseline glucose, latest 1-year follow-up window summary, care status, transition label, and reasons.',
            '   - evidence/comparison_evidence.xlsx: same evidence tables in workbook format.',
            '',
            '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.',
            '',
        ]);
    }

    private function buildBpStageComparisonLogicText(): string
    {
        return implode(PHP_EOL, [
            'BP stage comparison',
            '',
            'Current Flow',
            '- Cohort = one row per patient from registers_clean.csv after filters, but only if register 1stHypertension is New, Known, or Know.',
            '- Baseline = the earliest register row per patient.',
            '- Baseline stage uses register priority 3rdBP -> 2ndBP -> 1stBP.',
            '- Baseline fallback = if no valid BP string is found in those register BP fields, use staging_Hypertension text when it maps to a known stage.',
            '- Latest = scan followups_clean.csv on or before the observe date and use the latest valid bp_raw for that patient, even if that latest valid BP is from an earlier year.',
            '- If no valid latest bp_raw exists, the comparison keeps the latest invalid bp_raw for data-quality counting; if no bp_raw exists, latest is missing.',
            '- Active/LTFU split uses the same latest appointment +84 days logic as the LTFU chart.',
            '',
            'Per-Side Classification',
            'Baseline and latest stages use the same BP stage rules once a valid SBP/DBP pair is parsed:',
            '- Stage 3 = SBP >= 180 or DBP >= 110',
            '- Stage 2 = SBP >= 160 or DBP >= 100',
            '- Stage 1 = SBP >= 140 or DBP >= 90',
            '- Normal = SBP < 140 and DBP < 90',
            '',
            'BP parsing and validation',
            '- Raw BP must match the pattern NNN/NNN with 2-3 digits on each side.',
            '- Valid numeric range:',
            '  - SBP 50-300',
            '  - DBP 30-200',
            '- Values outside that range are treated as invalid and are not staged.',
            '',
            'Baseline text fallback',
            '- staging_Hypertension maps to Normal if it contains normal or <140/90.',
            '- staging_Hypertension maps to Stage 1 if it contains stage 1.',
            '- staging_Hypertension maps to Stage 2 if it contains stage 2.',
            '- staging_Hypertension maps to Stage 3 if it contains stage 3 or >=180/110.',
            '',
            'Truth table for each side',
            '- Valid BP string or mapped stage text => valid stage',
            '- Invalid BP string or impossible BP values => invalid',
            '- No usable BP field or stage text => missing',
            '',
            'Comparison Table',
            '- baseline stage rank > latest stage rank => Improved',
            '- baseline stage rank = latest stage rank => Unchanged',
            '- baseline stage rank < latest stage rank => Worsened',
            '- if either side is missing or invalid => patient is counted under Unavailable in the baseline/last-record distribution and excluded from the transition counts',
            '',
            'Important current behavior',
            '- The BP comparison cohort is restricted to hypertension register diagnosis only.',
            '- The latest side is not limited to the same calendar year as the observe date; it uses the latest valid follow-up bp_raw on or before the observe date.',
            '- The chart counts the whole cohort by adding Unavailable to the baseline/last-record distribution when a comparable pair is not available.',
            '',
        ]);
    }

    private function buildGlucoseStatusComparisonLogicText(): string
    {
        return implode(PHP_EOL, [
            'Glucose status comparison',
            '',
            'Current Flow',
            '- Cohort = one row per patient from registers_clean.csv after filters, but only if:',
            '  - register 2nd_Hypertension is New, Known, or Know, and',
            '  - the latest 4 follow-up visits on or before the observe date have NCD_Diagnosis = Diabetes or Both.',
            '- Baseline = the earliest register row per patient, using first_dm_test_type/value/date and second_dm_test_type/value/date.',
            '- Baseline fallback = if baseline glucose is missing and register diagnosis is New/Known/Know, use the earliest follow-up FBS with valid FBS_test_date where NCD_Diagnosis = Diabetes or Both.',
            '- Latest = the latest follow-up on or before the observe date, then scan that patient\'s prior 366-day follow-up window.',
            '- Latest window reads numeric follow-up fields: fbs, rbs_result, t2hpp, hba1c.',
            '- Age = visit_age at baseline, then recalculated forward to the latest follow-up date.',
            '- Active/LTFU split uses the same latest appointment +84 days logic as the LTFU chart.',
            '',
            'Per-Side Classification',
            'Thresholds used now:',
            '',
            'Age < 65',
            '- FBS controlled: 80 <= FBS <= 130',
            '- RBS/2HPP controlled: RBS/2HPP < 180',
            '- HbA1c controlled: HbA1c < 7.5 within 1 year',
            '',
            'Age >= 65',
            '- FBS controlled: 100 <= FBS <= 180',
            '- RBS/2HPP controlled: RBS/2HPP < 200',
            '- HbA1c controlled: HbA1c < 7.5 within 1 year',
            '',
            'Truth table for each side, baseline and latest:',
            '- Age missing and no invalid raw values => missing',
            '- Age missing and invalid raw values exist => invalid',
            '- Any FBS outside target => uncontrolled',
            '- Any RBS/2HPP at or above target => uncontrolled',
            '- Any HbA1c within 1 year at or above 7.5 => uncontrolled',
            '- No exceedance, and at least one FBS/RBS/2HPP is in range => controlled',
            '- No main measure at all, but invalid raw values exist => invalid',
            '- No main measure at all, and no invalid raw values => missing',
            '',
            'Important current behavior',
            '- HbA1c >= 7.5 alone is enough to make the side uncontrolled.',
            '- HbA1c < 7.5 alone does not make the side controlled.',
            '- 2HPP is currently treated with the same threshold bucket as RBS.',
            '- Invalid numeric ranges are rejected before comparison:',
            '  - FBS outside 20-600',
            '  - RBS/2HPP outside 20-1000',
            '  - HbA1c outside 2-25',
            '',
            'Comparison Table',
            '- baseline uncontrolled + latest controlled => Improved',
            '- baseline controlled + latest controlled => Maintaining controlled',
            '- baseline controlled + latest uncontrolled => Worsen',
            '- baseline uncontrolled + latest uncontrolled => Remain uncontrolled',
            '- if either side is missing or invalid => Unavailable for comparison',
            '',
        ]);
    }

    private function buildBpStageEvidenceRows(array $registers, array $followups, ?int $observeTs = null): array
    {
        $observeTs = $observeTs ?? time();
        $statusByPatient = $this->buildLatestFollowupStatusByPatient($followups, 84, $observeTs);
        $stageRank = ['Normal' => 0, 'Stage 1' => 1, 'Stage 2' => 2, 'Stage 3' => 3];

        $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;
            $baseline = $this->extractBaselineStageEvidenceFromRegisterRow($row, $regTs);
            $current = $baselineByPatient[$pid] ?? null;

            if ($current === null || $regTs < ($current['ts'] ?? PHP_INT_MAX) || ($regTs === ($current['ts'] ?? null) && ($current['status'] ?? null) !== 'valid' && ($baseline['status'] ?? null) === 'valid')) {
                $baselineByPatient[$pid] = $baseline;
                continue;
            }

            if ($regTs === ($current['ts'] ?? null) && ($baseline['status'] ?? null) === 'invalid') {
                $baselineByPatient[$pid]['invalid_values'] = array_values(array_unique(array_merge(
                    $baselineByPatient[$pid]['invalid_values'] ?? [],
                    $baseline['invalid_values'] ?? []
                )));
                $baselineByPatient[$pid]['baseline_reason'] = 'Invalid register BP values: ' . implode('; ', $baselineByPatient[$pid]['invalid_values']);
            }
        }

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

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

            $latest = $this->extractLatestStageEvidenceFromFollowupRow($row, $visitTs);
            if (($latest['status'] ?? null) === 'valid') {
                $current = $latestValidByPatient[$pid] ?? null;
                if ($current === null || $visitTs > ($current['ts'] ?? 0)) {
                    $latestValidByPatient[$pid] = $latest;
                }
                continue;
            }

            if (($latest['status'] ?? null) !== 'invalid') {
                continue;
            }

            $current = $latestInvalidByPatient[$pid] ?? null;
            if ($current === null || $visitTs > ($current['ts'] ?? 0)) {
                $latestInvalidByPatient[$pid] = $latest;
                continue;
            }

            if ($visitTs === ($current['ts'] ?? null)) {
                $latestInvalidByPatient[$pid]['invalid_values'] = array_values(array_unique(array_merge(
                    $latestInvalidByPatient[$pid]['invalid_values'] ?? [],
                    $latest['invalid_values'] ?? []
                )));
                $latestInvalidByPatient[$pid]['last_record_reason'] = 'Invalid latest follow-up bp_raw: ' . implode('; ', $latestInvalidByPatient[$pid]['invalid_values']);
            }
        }

        $rows = [];
        $patientIds = array_keys($baselineByPatient);
        sort($patientIds, SORT_NATURAL);

        foreach ($patientIds as $pid) {
            $baseline = $baselineByPatient[$pid];
            $latest = $latestValidByPatient[$pid]
                ?? $latestInvalidByPatient[$pid]
                ?? [
                    'ts' => null,
                    'status' => 'missing',
                    'stage' => null,
                    'last_record_source' => 'follow-up bp_raw',
                    'last_record_raw' => '',
                    'last_record_reason' => 'No valid follow-up bp_raw on or before the observe date.',
                    'invalid_values' => [],
                ];

            $baselineValid = ($baseline['status'] ?? null) === 'valid' && isset($stageRank[$baseline['stage'] ?? '']);
            $latestValid = ($latest['status'] ?? null) === 'valid' && isset($stageRank[$latest['stage'] ?? '']);

            $comparisonStatus = 'Unavailable';
            $comparisonReason = $this->buildUnavailableComparisonReason(
                'BP stage comparison',
                $baselineValid,
                $baseline['baseline_reason'] ?? 'Baseline unavailable.',
                $latestValid,
                $latest['last_record_reason'] ?? 'Latest record unavailable.'
            );

            if ($baselineValid && $latestValid) {
                $fromRank = $stageRank[$baseline['stage']];
                $toRank = $stageRank[$latest['stage']];
                if ($toRank < $fromRank) {
                    $comparisonStatus = 'Improved';
                    $comparisonReason = 'Baseline ' . $baseline['stage'] . ' -> latest ' . $latest['stage'] . ' (lower BP stage).';
                } elseif ($toRank === $fromRank) {
                    $comparisonStatus = 'Unchanged';
                    $comparisonReason = 'Baseline and latest BP stage are both ' . $baseline['stage'] . '.';
                } else {
                    $comparisonStatus = 'Worsened';
                    $comparisonReason = 'Baseline ' . $baseline['stage'] . ' -> latest ' . $latest['stage'] . ' (higher BP stage).';
                }
            }

            $rows[] = [
                'patient_id' => $pid,
                'care_status' => $this->formatNcdCareStatusLabel($statusByPatient[$pid]['status'] ?? null),
                'baseline_date' => $this->formatExportDate($baseline['ts'] ?? null),
                'baseline_source' => $baseline['baseline_source'] ?? '',
                'baseline_raw' => $baseline['baseline_raw'] ?? '',
                'baseline_stage' => $baseline['stage'] ?? '',
                'baseline_status' => $baseline['status'] ?? 'missing',
                'baseline_reason' => $baseline['baseline_reason'] ?? '',
                'last_record_date' => $this->formatExportDate($latest['ts'] ?? null),
                'last_record_source' => $latest['last_record_source'] ?? '',
                'last_record_raw' => $latest['last_record_raw'] ?? '',
                'last_record_stage' => $latest['stage'] ?? '',
                'last_record_status' => $latest['status'] ?? 'missing',
                'last_record_reason' => $latest['last_record_reason'] ?? '',
                'comparison_status' => $comparisonStatus,
                'comparison_reason' => $comparisonReason,
            ];
        }

        return $rows;
    }

    private function extractBaselineStageEvidenceFromRegisterRow(array $row, int $regTs): array
    {
        $invalid = [];
        $fieldLabels = [
            'third_bp' => 'register 3rdBP',
            'second_bp' => 'register 2ndBP',
            'first_bp' => 'register 1stBP',
        ];

        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 [
                        'ts' => $regTs,
                        'status' => 'valid',
                        'stage' => $stage,
                        'baseline_source' => $fieldLabels[$field] ?? $field,
                        'baseline_raw' => $raw,
                        'baseline_reason' => 'Earliest register row used; stage derived from ' . ($fieldLabels[$field] ?? $field) . '.',
                        'invalid_values' => [],
                    ];
                }
            }

            $invalid[] = ($fieldLabels[$field] ?? $field) . ': ' . $raw;
        }

        $stageText = $this->normalizeCategory($row['staging_hypertension'] ?? null);
        $stageFromText = $this->parseBpStageText($stageText);
        if ($stageFromText !== null) {
            return [
                'ts' => $regTs,
                'status' => 'valid',
                'stage' => $stageFromText,
                'baseline_source' => 'register staging_Hypertension',
                'baseline_raw' => $stageText,
                'baseline_reason' => 'Earliest register row used; stage mapped from staging_Hypertension text.',
                'invalid_values' => [],
            ];
        }

        if ($stageText !== '' && !$this->isUnknownValue($stageText)) {
            $invalid[] = 'register staging_Hypertension: ' . $stageText;
        }

        if (!empty($invalid)) {
            return [
                'ts' => $regTs,
                'status' => 'invalid',
                'stage' => null,
                'baseline_source' => 'register BP fields',
                'baseline_raw' => implode(' | ', $invalid),
                'baseline_reason' => 'Invalid register BP values: ' . implode('; ', $invalid),
                'invalid_values' => $invalid,
            ];
        }

        return [
            'ts' => $regTs,
            'status' => 'missing',
            'stage' => null,
            'baseline_source' => 'register BP fields',
            'baseline_raw' => '',
            'baseline_reason' => 'No usable BP found in register 3rdBP, 2ndBP, 1stBP, or staging_Hypertension.',
            'invalid_values' => [],
        ];
    }

    private function extractLatestStageEvidenceFromFollowupRow(array $row, int $visitTs): array
    {
        $raw = trim((string) ($row['bp_raw'] ?? ($row['own_clinic_bp'] ?? '')));
        if ($raw === '' || $this->isUnknownValue($raw)) {
            return [
                'ts' => $visitTs,
                'status' => 'missing',
                'stage' => null,
                'last_record_source' => 'follow-up bp_raw',
                'last_record_raw' => '',
                'last_record_reason' => 'No usable bp_raw on this follow-up row.',
                'invalid_values' => [],
            ];
        }

        $bp = $this->parseBpString($raw);
        if ($bp !== null) {
            $stage = $this->classifyBpStage((float) $bp['sbp'], (float) $bp['dbp']);
            if ($stage !== null) {
                return [
                    'ts' => $visitTs,
                    'status' => 'valid',
                    'stage' => $stage,
                    'last_record_source' => 'latest valid follow-up bp_raw',
                    'last_record_raw' => $raw,
                    'last_record_reason' => 'Latest valid follow-up bp_raw on or before the observe date.',
                    'invalid_values' => [],
                ];
            }
        }

        return [
            'ts' => $visitTs,
            'status' => 'invalid',
            'stage' => null,
            'last_record_source' => 'follow-up bp_raw',
            'last_record_raw' => $raw,
            'last_record_reason' => 'Invalid latest follow-up bp_raw: ' . $raw,
            'invalid_values' => [$raw],
        ];
    }

    private function buildGlucoseStatusEvidenceRows(array $registers, array $followups, ?int $observeTs = null): array
    {
        $observeTs = $observeTs ?? time();
        $statusByPatient = $this->buildLatestFollowupStatusByPatient($followups, 84, $observeTs);
        $eligiblePatientIds = $this->buildGlucoseEligiblePatientIdsByLatestDiagnosisVisits($followups, 4);

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

            $regTs = $this->parseDateValue($row['reg_date'] ?? null) ?? 0;
            $baseline = $this->extractBaselineGlucoseEvidenceFromRegisterRow($row, $regTs);
            $baseline['diagnosis_label'] = $diagnosisLabel;

            $current = $baselineByPatient[$pid] ?? null;
            if ($current === null || $regTs < ($current['ts'] ?? PHP_INT_MAX) || ($regTs === ($current['ts'] ?? null) && ($current['status'] ?? null) !== 'valid' && ($baseline['status'] ?? null) === 'valid')) {
                $baselineByPatient[$pid] = $baseline;
                continue;
            }

            if ($regTs === ($current['ts'] ?? null) && ($baseline['status'] ?? null) === 'invalid') {
                $baselineByPatient[$pid]['invalid_values'] = array_values(array_unique(array_merge(
                    $baselineByPatient[$pid]['invalid_values'] ?? [],
                    $baseline['invalid_values'] ?? []
                )));
                $baselineByPatient[$pid]['baseline_reason'] = 'Invalid baseline glucose values: ' . implode('; ', $baselineByPatient[$pid]['invalid_values']);
            }
        }

        $baselineByPatient = $this->applyDiabetesFollowupBaselineFallbackEvidence($baselineByPatient, $followups);
        $latestByPatient = $this->buildLatestGlucoseEvidenceByPatientFromFollowups($followups, $baselineByPatient, $observeTs);

        $rows = [];
        $patientIds = array_keys($baselineByPatient);
        sort($patientIds, SORT_NATURAL);

        foreach ($patientIds as $pid) {
            $baseline = $baselineByPatient[$pid];
            $latest = $latestByPatient[$pid] ?? [
                'ts' => null,
                'window_start_ts' => null,
                'status' => 'missing',
                'controlled' => null,
                'age' => null,
                'last_record_source' => 'latest 1-year follow-up window',
                'last_record_data' => '',
                'last_record_reason' => 'No follow-up on or before the observe date.',
                'invalid_values' => [],
            ];

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

            $comparisonStatus = 'Unavailable for comparison';
            $comparisonReason = $this->buildUnavailableComparisonReason(
                'Glucose status comparison',
                $baselineValid,
                $baseline['baseline_reason'] ?? 'Baseline unavailable.',
                $latestValid,
                $latest['last_record_reason'] ?? 'Latest record unavailable.'
            );
            if ($baselineValid && $latestValid) {
                $comparisonStatus = $this->resolveGlucoseTransitionLabel((bool) $baseline['controlled'], (bool) $latest['controlled']);
                $comparisonReason = 'Baseline ' . $this->formatControlState($baseline['controlled']) . ' -> latest ' . $this->formatControlState($latest['controlled']) . '.';
            }

            $rows[] = [
                'patient_id' => $pid,
                'care_status' => $this->formatNcdCareStatusLabel($statusByPatient[$pid]['status'] ?? null),
                'baseline_date' => $this->formatExportDate($baseline['ts'] ?? null),
                'baseline_source' => $baseline['baseline_source'] ?? '',
                'baseline_age' => $this->formatNumericValue($baseline['age'] ?? null),
                'baseline_data' => $baseline['baseline_data'] ?? '',
                'baseline_status' => $baseline['status'] ?? 'missing',
                'baseline_control_state' => $this->formatControlState($baseline['controlled'] ?? null),
                'baseline_reason' => $baseline['baseline_reason'] ?? '',
                'last_record_date' => $this->formatExportDate($latest['ts'] ?? null),
                'last_record_window_start' => $this->formatExportDate($latest['window_start_ts'] ?? null),
                'last_record_source' => $latest['last_record_source'] ?? '',
                'last_record_age' => $this->formatNumericValue($latest['age'] ?? null),
                'last_record_data' => $latest['last_record_data'] ?? '',
                'last_record_status' => $latest['status'] ?? 'missing',
                'last_record_control_state' => $this->formatControlState($latest['controlled'] ?? null),
                'last_record_reason' => $latest['last_record_reason'] ?? '',
                'comparison_status' => $comparisonStatus,
                'comparison_reason' => $comparisonReason,
            ];
        }

        return $rows;
    }

    private function extractBaselineGlucoseEvidenceFromRegisterRow(array $row, int $regTs): array
    {
        $age = $this->parseFloat($row['visit_age'] ?? ($row['age_at_reg'] ?? ($row['current_age'] ?? null)));
        $referenceTs = $this->parseDateValue($row['reg_date'] ?? null) ?? $regTs;
        $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'
        );

        $evaluation = $this->evaluateGlucoseControlStatus($age, $referenceTs, $measures, $invalid);
        return array_merge($evaluation, [
            'ts' => $referenceTs,
            'baseline_source' => 'earliest register DM tests',
            'baseline_data' => $this->summarizeGlucoseMeasures($measures),
            'baseline_reason' => $this->describeGlucoseEvaluation($evaluation, $measures, $referenceTs, 'baseline'),
        ]);
    }

    private function applyDiabetesFollowupBaselineFallbackEvidence(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 || $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] ?? [];
            $fallback = $this->extractBaselineGlucoseEvidenceFromFollowupFallbackRow(
                $entry['row'] ?? [],
                $baseline['age'] ?? null,
                $baseline['ts'] ?? null,
                $entry['test_ts'] ?? null
            );
            if (($fallback['status'] ?? 'missing') === 'missing' && empty($fallback['invalid_values'] ?? [])) {
                continue;
            }
            $baselineByPatient[$pid] = array_merge($fallback, [
                'diagnosis_label' => $baseline['diagnosis_label'] ?? null,
            ]);
        }

        return $baselineByPatient;
    }

    private function extractBaselineGlucoseEvidenceFromFollowupFallbackRow(
        array $row,
        ?float $baselineAge,
        ?int $baselineTs,
        ?int $fallbackTs
    ): array {
        $fallbackTs = $fallbackTs ?? ($this->parseDateValue($row['fbs_test_date'] ?? null) ?? $this->parseDateValue($row['visit_date'] ?? null));
        $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'
        );

        $evaluation = $this->evaluateGlucoseControlStatus($age, $fallbackTs, $measures, $invalid);
        $reason = 'Register baseline glucose missing; fallback used earliest follow-up FBS with valid FBS_test_date. '
            . $this->describeGlucoseEvaluation($evaluation, $measures, $fallbackTs, 'baseline');

        return array_merge($evaluation, [
            'ts' => $fallbackTs,
            'baseline_source' => 'earliest follow-up FBS fallback',
            'baseline_data' => $this->summarizeGlucoseMeasures($measures),
            'baseline_reason' => trim($reason),
        ]);
    }

    private function buildLatestGlucoseEvidenceByPatientFromFollowups(array $followups, array $baselineByPatient, int $observeTs): array
    {
        if (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 (array_keys($baselineByPatient) as $pid) {
            $baseline = $baselineByPatient[$pid] ?? [];
            $anchorTs = $latestVisitByPatient[$pid] ?? null;
            if ($anchorTs === null) {
                $latestByPatient[$pid] = [
                    'ts' => null,
                    'window_start_ts' => null,
                    'status' => 'missing',
                    'controlled' => null,
                    'age' => null,
                    'last_record_source' => 'latest 1-year follow-up window',
                    'last_record_data' => '',
                    'last_record_reason' => 'No follow-up on or before the observe date.',
                    'invalid_values' => [],
                ];
                continue;
            }

            $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
            );
            $evaluation = $this->evaluateGlucoseControlStatus($age, $anchorTs, $measures, $invalid);
            $latestByPatient[$pid] = array_merge($evaluation, [
                'ts' => $anchorTs,
                'window_start_ts' => $windowStartTs,
                'last_record_source' => 'latest 1-year follow-up window',
                'last_record_data' => $this->summarizeGlucoseMeasures($measures),
                'last_record_reason' => $this->describeGlucoseEvaluation($evaluation, $measures, $anchorTs, 'latest'),
            ]);
        }

        return $latestByPatient;
    }

    private function summarizeGlucoseMeasures(array $measures): string
    {
        $items = [];
        foreach (['fbs', 'rbs', 'hba1c'] as $bucket) {
            foreach (($measures[$bucket] ?? []) as $measure) {
                $type = strtoupper((string) ($measure['type'] ?? $bucket));
                $items[] = [
                    'ts' => $measure['ts'] ?? null,
                    'text' => $type . '=' . $this->formatNumericValue($measure['value'] ?? null) . ($measure['ts'] ? '@' . $this->formatExportDate($measure['ts']) : ''),
                ];
            }
        }

        usort($items, static function (array $left, array $right): int {
            return (($left['ts'] ?? 0) <=> ($right['ts'] ?? 0));
        });

        return implode(' | ', array_map(static function (array $item): string {
            return $item['text'];
        }, $items));
    }

    private function describeGlucoseEvaluation(array $evaluation, array $measures, ?int $referenceTs, string $sideLabel): string
    {
        $status = $evaluation['status'] ?? 'missing';
        $age = $evaluation['age'] ?? null;
        $invalidValues = $evaluation['invalid_values'] ?? [];

        if ($age === null) {
            if (!empty($invalidValues)) {
                return ucfirst($sideLabel) . ' age missing; invalid glucose values: ' . implode('; ', $invalidValues);
            }
            return ucfirst($sideLabel) . ' age missing for glucose control classification.';
        }

        $fbsLower = $age < 65 ? 80.0 : 100.0;
        $fbsUpper = $age < 65 ? 130.0 : 180.0;
        $rbsUpper = $age < 65 ? 180.0 : 200.0;
        $hasMainMeasure = false;
        $inRange = [];
        $exceeded = [];
        $annualHba1cControlled = [];
        $annualHba1cExceeded = [];
        $hasAnyHba1c = false;

        foreach (($measures['fbs'] ?? []) as $measure) {
            $hasMainMeasure = true;
            $value = (float) ($measure['value'] ?? 0);
            $label = 'FBS ' . $this->formatNumericValue($value) . ' mg/dL';
            if ($value < $fbsLower || $value > $fbsUpper) {
                $exceeded[] = $label . ' outside target ' . $fbsLower . '-' . $fbsUpper;
            } else {
                $inRange[] = $label . ' within target ' . $fbsLower . '-' . $fbsUpper;
            }
        }

        foreach (($measures['rbs'] ?? []) as $measure) {
            $hasMainMeasure = true;
            $value = (float) ($measure['value'] ?? 0);
            $type = strtoupper((string) ($measure['type'] ?? 'RBS'));
            $label = $type . ' ' . $this->formatNumericValue($value) . ' mg/dL';
            if ($value >= $rbsUpper) {
                $exceeded[] = $label . ' at or above target limit ' . $rbsUpper;
            } else {
                $inRange[] = $label . ' below target limit ' . $rbsUpper;
            }
        }

        foreach (($measures['hba1c'] ?? []) as $measure) {
            $hasAnyHba1c = true;
            $measureTs = $measure['ts'] ?? $referenceTs;
            if ($referenceTs !== null && $measureTs !== null && abs($referenceTs - $measureTs) > 366 * 86400) {
                continue;
            }
            $value = (float) ($measure['value'] ?? 0);
            $label = 'HbA1c ' . $this->formatNumericValue($value) . '%';
            if ($value >= 7.5) {
                $annualHba1cExceeded[] = $label . ' at or above target 7.5';
            } else {
                $annualHba1cControlled[] = $label . ' below target 7.5';
            }
        }

        if ($status === 'valid' && ($evaluation['controlled'] ?? null) === false) {
            return ucfirst($sideLabel) . ' uncontrolled: ' . implode('; ', array_merge($exceeded, $annualHba1cExceeded));
        }

        if ($status === 'valid' && ($evaluation['controlled'] ?? null) === true) {
            $details = [];
            if (!empty($inRange)) {
                $details[] = implode('; ', $inRange);
            }
            if (!empty($annualHba1cControlled)) {
                $details[] = implode('; ', $annualHba1cControlled);
            } elseif ($hasAnyHba1c === false) {
                $details[] = 'HbA1c not available, so control is based on in-range FBS/RBS/2HPP only.';
            }
            return ucfirst($sideLabel) . ' controlled: ' . implode(' ', $details);
        }

        if ($status === 'invalid') {
            return ucfirst($sideLabel) . ' invalid glucose values: ' . implode('; ', $invalidValues);
        }

        if ($hasAnyHba1c && !$hasMainMeasure) {
            return ucfirst($sideLabel) . ' missing comparison data: only HbA1c available, with no FBS/RBS/2HPP main measure.';
        }

        if (!$hasMainMeasure) {
            return ucfirst($sideLabel) . ' missing comparison data: no usable FBS, RBS, or 2HPP values.';
        }

        return ucfirst($sideLabel) . ' unavailable for comparison.';
    }

    private function buildUnavailableComparisonReason(
        string $label,
        bool $baselineValid,
        string $baselineReason,
        bool $latestValid,
        string $latestReason
    ): string {
        $parts = [];
        if (!$baselineValid) {
            $parts[] = 'baseline: ' . $baselineReason;
        }
        if (!$latestValid) {
            $parts[] = 'latest: ' . $latestReason;
        }
        if (empty($parts)) {
            return $label . ' unavailable for comparison.';
        }
        return 'Unavailable because ' . implode(' | ', $parts);
    }

    private function formatNcdCareStatusLabel(?string $status): string
    {
        return match ($status) {
            'active' => 'Active',
            'ltfu' => 'LTFU',
            'exited' => 'Exited',
            'missing_next_appointment' => 'Missing next appointment',
            default => 'No follow-up status',
        };
    }

    private function formatControlState(?bool $controlled): string
    {
        if ($controlled === true) {
            return 'Controlled';
        }
        if ($controlled === false) {
            return 'Uncontrolled';
        }
        return '';
    }

    private function formatExportDate(?int $ts): string
    {
        if ($ts === null || $ts <= 0) {
            return '';
        }
        return date('Y-m-d', $ts);
    }

    private function formatNumericValue($value): string
    {
        if ($value === null || $value === '') {
            return '';
        }
        if (!is_numeric($value)) {
            return (string) $value;
        }
        $numeric = (float) $value;
        if (abs($numeric - round($numeric)) < 0.00001) {
            return (string) (int) round($numeric);
        }
        return rtrim(rtrim(number_format($numeric, 2, '.', ''), '0'), '.');
    }
}