<?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']
        );
        $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'] = $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/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' => '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));

        $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.',
            '- data_quality/*: used columns, column-level quality summary, and top invalid values.',
            '- 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',
            '   - 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.',
            '',
            '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 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',
            '',
        ]);
    }
}