<?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 LoadsNcdAnalysisData
{
    private function loadNcdAnalytics(string $clinicConnection, array $filters = [], array $config = []): array
    {
        $clinicKey = $clinicConnection === 'ALL' ? 'overall' : $clinicConnection;
        $basePath = base_path('outputs');
        $clinicPath = $basePath . DIRECTORY_SEPARATOR . $clinicKey;

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

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

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

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

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

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

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

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

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

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

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

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

        return $rows;
    }

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

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

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

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

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

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

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

        $top = 20;

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

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

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

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

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

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

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

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

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

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

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

        return $filtered;
    }

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

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

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

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

            $filtered[] = $row;
        }

        return $filtered;
    }

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

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

        return '65-200';
    }

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

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

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

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

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

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

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

        return $text;
    }

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

    private function parseFloat($value): ?float
    {
        if ($value === null) {
            return null;
        }
        $text = trim((string) $value);
        if ($this->isUnknownValue($text)) {
            return null;
        }
        if (!is_numeric($text)) {
            return null;
        }
        return (float) $text;
    }

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

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

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

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

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

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

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

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

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

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

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

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

        return $map;
    }

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

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

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

        return $map;
    }

    private function applyPatientTableGenderToFollowups(array $followups, string $clinicConnection): array
    {
        if (empty($followups)) {
            return $followups;
        }

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

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

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

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

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

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

        return $followups;
    }
}