<?php

namespace App\Http\Controllers;

use App\Models\Followup_general;
use App\Models\PreTbRecord;
use App\Models\PtConfig;
use App\Services\PreTbRecordPayloadService;
use Barryvdh\DomPDF\Facade\Pdf;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Arr;
use Symfony\Component\HttpFoundation\StreamedResponse;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class PreTbRecordController extends Controller
{
    private PreTbRecordPayloadService $payloadService;

    public function __construct(PreTbRecordPayloadService $payloadService)
    {
        $this->payloadService = $payloadService;
    }

    public function create()
    {
        return view('TB.pre_tb_record', [
            'mdInitials' => $this->mdInitialOptions(),
        ]);
    }

    public function manage(Request $request)
    {
        $statusCutoff = Carbon::today()->subMonth()->format('Y-m-d');
        $recentCutoff = Carbon::today()->subDays(60)->format('Y-m-d');

        $query = PreTbRecord::query()
            ->select('pre_tb_records.*')
            ->selectRaw("
                CASE
                    WHEN (chest_xray = 1 OR chest_xray = '1')
                        AND (md_diagnosis IS NULL OR md_diagnosis = '')
                        AND (cad_score IS NULL OR cad_score = '')
                        AND date_of_screening >= ?
                    THEN 1
                    ELSE 0
                END AS needs_xray_result
            ", [$statusCutoff])
            ->selectRaw("
                CASE
                    WHEN (genexpert = 1 OR genexpert = '1')
                        AND (genexpert_res IS NULL OR genexpert_res = '')
                        AND date_of_screening >= ?
                    THEN 1
                    ELSE 0
                END AS needs_genexpert_result
            ", [$statusCutoff])
            ->selectRaw("
                CASE
                    WHEN (radio_request = 1 OR radio_request = '1')
                        AND (radiologist_result = 4 OR radiologist_result = '4')
                        AND LOWER(COALESCE(comment, '')) LIKE '%recheck%'
                        AND date_of_screening >= ?
                    THEN 1
                    ELSE 0
                END AS needs_radio_recheck
            ", [$statusCutoff])
            ->selectRaw("
                CASE
                    WHEN (
                        ((chest_xray = 1 OR chest_xray = '1')
                            AND (md_diagnosis IS NULL OR md_diagnosis = '')
                            AND (cad_score IS NULL OR cad_score = '')
                            AND date_of_screening >= ?)
                        OR
                        ((genexpert = 1 OR genexpert = '1')
                            AND (genexpert_res IS NULL OR genexpert_res = '')
                            AND date_of_screening >= ?)
                        OR
                        ((radio_request = 1 OR radio_request = '1')
                            AND (radiologist_result = 4 OR radiologist_result = '4')
                            AND LOWER(COALESCE(comment, '')) LIKE '%recheck%'
                            AND date_of_screening >= ?)
                    )
                    THEN 1
                    ELSE 0
                END AS needs_result_attention
            ", [$statusCutoff, $statusCutoff, $statusCutoff])
            ->orderByDesc('needs_result_attention')
            ->orderByDesc('date_of_screening')
            ->orderByDesc('id');

        $cid = trim((string)$request->input('cid', ''));
        if ($cid !== '') {
            $query->where('cid', 'like', '%' . $cid . '%');
        }

        $name = trim((string)$request->input('name', ''));
        if ($name !== '') {
            $query->where('name', 'like', '%' . $name . '%');
        }

        $md = trim((string)$request->input('md', ''));
        if ($md !== '') {
            $query->where('md', $md);
        }

        $hasSearchFilters = ($cid !== '' || $name !== '' || $md !== '');
        $recentOnly = !$hasSearchFilters;

        if (!$hasSearchFilters) {
            $isBlankSql = static function (string $column): string {
                return "({$column} IS NULL OR TRIM(CAST({$column} AS CHAR)) = '')";
            };
            $hasMdOtherSql = "(LOWER(COALESCE(comment, '')) LIKE '%md other:%')";
            $hasRadiologistOtherSql = "(LOWER(COALESCE(comment, '')) LIKE '%radiologist other:%')";

            // Default view highlights only recently-screened records that still need linked results.
            $query->where(function ($q) use ($isBlankSql, $hasMdOtherSql, $hasRadiologistOtherSql) {
                $q->where(function ($s) use ($isBlankSql, $hasMdOtherSql) {
                    $s->whereRaw("(chest_xray = 1 OR chest_xray = '1')")
                        ->whereRaw("("
                            . $isBlankSql('chest_xray_date') . " OR "
                            . $isBlankSql('chest_xray_fac') . " OR "
                            . "(" . $isBlankSql('md_diagnosis') . " AND NOT " . $hasMdOtherSql . ")"
                            . ")");
                })->orWhere(function ($s) use ($isBlankSql, $hasRadiologistOtherSql) {
                    $s->whereRaw("(radio_request = 1 OR radio_request = '1')")
                        ->whereRaw("("
                            . $isBlankSql('radio_request_date') . " OR "
                            . "(" . $isBlankSql('radiologist_result') . " AND NOT " . $hasRadiologistOtherSql . ")"
                            . ")");
                })->orWhere(function ($s) use ($isBlankSql) {
                    $s->whereRaw("(sputum_afb = 1 OR sputum_afb = '1')")
                        ->whereRaw("("
                            . $isBlankSql('sputum_afb_date') . " OR "
                            . $isBlankSql('sputum_afb_res')
                            . ")");
                })->orWhere(function ($s) use ($isBlankSql) {
                    $s->whereRaw("(genexpert = 1 OR genexpert = '1')")
                        ->whereRaw("("
                            . $isBlankSql('genexpert_date') . " OR "
                            . $isBlankSql('genexpert_res')
                            . ")");
                })->orWhere(function ($s) use ($isBlankSql) {
                    $s->whereRaw("(truenat = 1 OR truenat = '1')")
                        ->whereRaw("("
                            . $isBlankSql('truenat_date') . " OR "
                            . $isBlankSql('truenat_res')
                            . ")");
                })->orWhere(function ($s) use ($isBlankSql) {
                    $s->whereRaw("(hiv_det = 1 OR hiv_det = '1')")
                        ->whereRaw("("
                            . $isBlankSql('hiv_det_date') . " OR "
                            . $isBlankSql('hiv_det_res')
                            . ")");
                })->orWhere(function ($s) use ($isBlankSql) {
                    $s->whereRaw("(antibiotics = 1 OR antibiotics = '1')")
                        ->whereRaw("("
                            . $isBlankSql('antibiotic_date') . " OR "
                            . $isBlankSql('drug')
                            . ")");
                })->orWhere(function ($s) use ($isBlankSql) {
                    $s->whereRaw("(tb_treat = 1 OR tb_treat = '1')")
                        ->whereRaw("("
                            . $isBlankSql('tb_treat_date') . " OR "
                            . $isBlankSql('tb_treat_regimen')
                            . ")");
                });
            });
        }

        if ($recentOnly) {
            $query->whereDate('date_of_screening', '>=', $recentCutoff);
        }

        $records = $query->paginate(25)->withQueryString();
        $mdInitials = $this->mdInitialOptions();
        if (empty($mdInitials)) {
            $mdInitials = PreTbRecord::query()
                ->whereNotNull('md')
                ->where('md', '!=', '')
                ->orderBy('md')
                ->pluck('md')
                ->map(fn($v) => trim((string)$v))
                ->filter(fn($v) => $v !== '')
                ->unique()
                ->values()
                ->all();
        }
        if ($md !== '' && !in_array($md, $mdInitials, true)) {
            $mdInitials[] = $md;
        }

        return view('TB.pre_tb_record_manage', [
            'records' => $records,
            'mdInitials' => $mdInitials,
            'filters' => [
                'cid' => $cid,
                'name' => $name,
                'md' => $md,
            ],
            'recentOnly' => $recentOnly,
            'recentCutoff' => $recentCutoff,
        ]);
    }

    public function exportVisitPdf(PreTbRecord $preTbRecord)
    {
        if (!class_exists(Pdf::class)) {
            return redirect()
                ->back()
                ->withErrors(['pdf' => 'PDF export package is missing. Install barryvdh/laravel-dompdf.']);
        }

        $commentParts = $this->parseCommentExtras($preTbRecord->comment);
        $mdDiagnosisOther = $commentParts['md_diagnosis_oth'];
        $radiologistOther = $commentParts['radiologist_result_oth'];
        $cleanComment = $commentParts['comment'];

        $sexLabel = (string) $preTbRecord->sex === '1'
            ? 'Male'
            : ((string) $preTbRecord->sex === '2' ? 'Female' : '-');

        $symptoms = [
            ['label' => 'Fever', 'present' => $this->labelForExportField('fever', $preTbRecord->fever_present), 'days' => $preTbRecord->fever_days],
            ['label' => 'Cough', 'present' => $this->labelForExportField('cough', $preTbRecord->cough_present), 'days' => $preTbRecord->cough_days],
            ['label' => 'Hemoptysis', 'present' => $this->labelForExportField('hemoptysis', $preTbRecord->hemoptysis_present), 'days' => $preTbRecord->hemoptysis_days],
            ['label' => 'Weight loss', 'present' => $this->labelForExportField('weight_loss', $preTbRecord->weight_loss_present), 'days' => $preTbRecord->weight_loss_days],
            ['label' => 'Loss of appetite', 'present' => $this->labelForExportField('loss_of_appetite', $preTbRecord->appetite_loss_present), 'days' => $preTbRecord->appetite_loss_days],
            ['label' => 'Chest pain', 'present' => $this->labelForExportField('chest_pain', $preTbRecord->chest_pain_present), 'days' => $preTbRecord->chest_pain_days],
            ['label' => 'Night sweats', 'present' => $this->labelForExportField('night_sweats', $preTbRecord->night_sweats_present), 'days' => $preTbRecord->night_sweats_days],
            ['label' => 'Neck glands', 'present' => $this->labelForExportField('neck_glands', $preTbRecord->neck_glands_present), 'days' => $preTbRecord->neck_glands_days],
            ['label' => 'Fatigue', 'present' => $this->labelForExportField('fatigue', $preTbRecord->fatigue_present), 'days' => $preTbRecord->fatigue_days],
        ];

        $riskFactors = [
            'Alcohol' => $this->labelForExportField('alcohol', $preTbRecord->alcohol) ?? '-',
            'Smoking' => $this->labelForExportField('smoking', $preTbRecord->smoking) ?? '-',
            'Malnutrition' => $this->labelForExportField('malnutrition', $preTbRecord->malnutrition) ?? '-',
            'PWID' => $this->labelForExportField('PWID', $preTbRecord->PWID) ?? '-',
            'PWUD' => $this->labelForExportField('PWUD', $preTbRecord->PWUD) ?? '-',
            'DM' => $this->labelForExportField('DM', $preTbRecord->DM) ?? '-',
            'DM treatment status' => $this->labelForExportField('dm_tx_status', $preTbRecord->dm_tx_status) ?? '-',
            'HIV' => $this->labelForExportField('hiv_status', $preTbRecord->hiv_status) ?? '-',
            'HIV treatment status' => $this->labelForExportField('hiv_tx', $preTbRecord->hiv_tx) ?? '-',
            'History of TB (self)' => $this->labelForExportField('his_tb_self', $preTbRecord->his_tb_self) ?? '-',
            'History of TB (family)' => $this->labelForExportField('his_tb_family', $preTbRecord->his_tb_family) ?? '-',
        ];

        $radiology = [
            'Chest X-ray requested' => $this->labelForExportField('chest_x-ray', $preTbRecord->chest_xray) ?? '-',
            'Chest X-ray date' => $this->formatDateForReport($preTbRecord->chest_xray_date),
            'Facility' => $this->labelForExportField('chest_x-ray_fac', $preTbRecord->chest_xray_fac) ?? '-',
            'MD diagnosis' => $this->labelForExportField('md_diagnosis', $preTbRecord->md_diagnosis) ?? '-',
            'MD diagnosis other' => $mdDiagnosisOther ?: '-',
            'CAD score' => $preTbRecord->cad_score ?? '-',
            'Radiologist requested' => $this->labelForExportField('radio_request', $preTbRecord->radio_request) ?? '-',
            'Radiologist request date' => $this->formatDateForReport($preTbRecord->radio_request_date),
            'Radiologist result' => $this->labelForExportField('radiologist_result', $preTbRecord->radiologist_result) ?? '-',
            'Radiologist result other' => $radiologistOther ?: '-',
            'Radiologist opinion / comment' => $cleanComment ?: '-',
        ];

        $labTests = [
            'Sputum AFB' => $this->labelForExportField('sputum_afb', $preTbRecord->sputum_afb) ?? '-',
            'Sputum AFB date' => $this->formatDateForReport($preTbRecord->sputum_afb_date),
            'Sputum AFB result' => $this->labelForExportField('sputum_afb_res', $preTbRecord->sputum_afb_res) ?? '-',
            'GeneXpert' => $this->labelForExportField('genexpert', $preTbRecord->genexpert) ?? '-',
            'GeneXpert date' => $this->formatDateForReport($preTbRecord->genexpert_date),
            'GeneXpert specimen' => $preTbRecord->type_of_specimen_geneXpert ?: '-',
            'GeneXpert result' => $this->labelForExportField('genexpert_res', $preTbRecord->genexpert_res) ?? '-',
            'Truenat' => $this->labelForExportField('truenat', $preTbRecord->truenat) ?? '-',
            'Truenat date' => $this->formatDateForReport($preTbRecord->truenat_date),
            'Truenat specimen' => $preTbRecord->type_of_specimen_truenat ?: '-',
            'Truenat result' => $this->labelForExportField('truenat_res', $preTbRecord->truenat_res) ?? '-',
            'HIV (determine) tested' => $this->labelForExportField('hiv_det', $preTbRecord->hiv_det) ?? '-',
            'HIV (determine) date' => $this->formatDateForReport($preTbRecord->hiv_det_date),
            'HIV (determine) result' => $this->labelForExportField('hiv_det_res', $preTbRecord->hiv_det_res) ?? '-',
            'CRP tested' => $this->labelForExportField('crp', $preTbRecord->crp) ?? '-',
            'CRP date' => $this->formatDateForReport($preTbRecord->crp_date),
            'CRP result' => $preTbRecord->crp_result ?? '-',
        ];

        $management = [
            'Antibiotics' => $this->labelForExportField('antibiotics', $preTbRecord->antibiotics) ?? '-',
            'Antibiotic date' => $this->formatDateForReport($preTbRecord->antibiotic_date),
            'Drug' => $preTbRecord->drug ?: '-',
            'TB treatment' => $this->labelForExportField('tb_treat', $preTbRecord->tb_treat) ?? '-',
            'TB treatment date' => $this->formatDateForReport($preTbRecord->tb_treat_date),
            'Regimen' => $this->labelForExportField('tb_treat_regimen', $preTbRecord->tb_treat_regimen) ?? '-',
            'Other management' => $preTbRecord->manage_oth ?: '-',
            'TB Diagnosis' => $preTbRecord->tb_diagnosis ?: '-',
            'Treatment status' => $preTbRecord->treatment_status ?: '-',
            'Treatment status other' => $preTbRecord->treatment_status_other ?: '-',
            'Remark' => $preTbRecord->remark ?: '-',
            'MD' => $preTbRecord->md ?: '-',
        ];

        $xrayImageDataUri = $this->buildImageDataUri($preTbRecord->xray_image_path);

        $viewName = null;
        foreach (['TB.export.pretb_visit_pdf', 'TB.export.pretb.pretb_visit_pdf'] as $candidateView) {
            if (view()->exists($candidateView)) {
                $viewName = $candidateView;
                break;
            }
        }

        if ($viewName === null) {
            return redirect()
                ->route('pretb_record.manage')
                ->withErrors(['pdf' => 'PDF template is missing on server. Please deploy latest views and clear view cache.']);
        }

        $pdf = Pdf::loadView($viewName, [
            'record' => $preTbRecord,
            'sexLabel' => $sexLabel,
            'modeOfEntryLabel' => $this->labelForExportField('mode_of_entry', $preTbRecord->mode_of_entry) ?? '-',
            'typeOfScreeningLabel' => $this->labelForExportField('type_of_screening', $preTbRecord->type_of_screening) ?? '-',
            'dateOfScreening' => $this->formatDateForReport($preTbRecord->date_of_screening),
            'dateOfNextVisit' => $this->formatDateForReport($preTbRecord->dofnv),
            'symptoms' => $symptoms,
            'riskFactors' => $riskFactors,
            'radiology' => $radiology,
            'labTests' => $labTests,
            'management' => $management,
            'xrayImageDataUri' => $xrayImageDataUri,
            'xrayImagePath' => $preTbRecord->xray_image_path,
            'generatedAt' => Carbon::now()->format('d-m-Y H:i'),
        ])->setPaper('a4', 'portrait');

        $filename = sprintf(
            'pretb_visit_%s_%s.pdf',
            $preTbRecord->cid ?: $preTbRecord->id,
            Carbon::now()->format('Ymd_His')
        );

        // Render inline in browser to avoid repeated forced-download/open loops.
        return $pdf->stream($filename);
    }

    public function edit(PreTbRecord $preTbRecord)
    {
        $input = $preTbRecord->toArray();
        $commentParts = $this->parseCommentExtras($preTbRecord->comment);
        $input['md_diagnosis_oth'] = $commentParts['md_diagnosis_oth'];
        $input['radiologist_result_oth'] = $commentParts['radiologist_result_oth'];
        $input['comment'] = $commentParts['comment'];
        foreach ($input as $key => $value) {
            if (is_int($value) || is_float($value)) {
                $input[$key] = (string)$value;
            }
        }
        if (array_key_exists('height', $input)) {
            $heightCm = $this->formatHeightForForm($preTbRecord->height);
            $input['height'] = $heightCm === null ? null : (string)$heightCm;
        }
        if (!empty($input['mode_of_entry']) && is_string($input['mode_of_entry'])) {
            $input['mode_of_entry'] = array_values(array_filter(array_map('trim', explode(',', $input['mode_of_entry']))));
        }
        $symptomKeys = [
            'fever' => 'fever_days',
            'cough' => 'cough_days',
            'hemoptysis' => 'hemoptysis_days',
            'weight_loss' => 'weight_loss_days',
            'appetite_loss' => 'appetite_loss_days',
            'chest_pain' => 'chest_pain_days',
            'night_sweats' => 'night_sweats_days',
            'neck_glands' => 'neck_glands_days',
            'fatigue' => 'fatigue_days',
        ];

        $input['symptoms'] = [];
        $input['symptoms_reviewed'] = [];
        foreach ($symptomKeys as $key => $column) {
            $days = $preTbRecord->{$column};
            $presentColumn = $key . '_present';
            $present = null;
            if (array_key_exists($presentColumn, $input)) {
                $rawPresent = $preTbRecord->{$presentColumn};
                if ((string)$rawPresent === '1') {
                    $present = '1';
                } elseif (($rawPresent === null || $rawPresent === '') && $days !== null && $days !== '') {
                    $present = '1';
                }
            } elseif ($days !== null && $days !== '') {
                $present = '1';
            }
            $input['symptoms'][$key] = [
                'present' => $present,
                'days' => $days,
            ];
            if ($present !== '1') {
                $input['symptoms_reviewed'][$key] = '1';
            }
        }

        $screeningDate = $this->formatDateForFormFromRaw($preTbRecord, 'date_of_screening');
        if ($screeningDate !== null) {
            $input['dofscrrening'] = $screeningDate;
        }

        $nextVisitDate = $this->formatDateForFormFromRaw($preTbRecord, 'dofnv');
        if ($nextVisitDate !== null) {
            $input['dofnv'] = $nextVisitDate;
        }

        foreach (['chest_xray_date', 'genexpert_date', 'truenat_date', 'hiv_det_date', 'crp_date', 'radio_request_date', 'sputum_afb_date', 'antibiotic_date', 'tb_treat_date'] as $df) {
            $formatted = $this->formatDateForFormFromRaw($preTbRecord, $df);
            if ($formatted !== null) {
                $input[$df] = $formatted;
            }
        }

        return redirect()
            ->route('pretb_record.create', ['edit' => $preTbRecord->id])
            ->withInput($input);
    }

    public function newVisit(PreTbRecord $preTbRecord)
    {
        $input = [
            'cid' => $preTbRecord->cid,
            'name' => $preTbRecord->name,
            'age' => $preTbRecord->age,
            'sex' => $preTbRecord->sex,
            'height' => $this->formatHeightForForm($preTbRecord->height),
            'weight' => $preTbRecord->weight,
            'mode_of_entry' => $preTbRecord->mode_of_entry,
            'dofscrrening' => Carbon::today()->format('d-m-Y'),
        ];

        return redirect()
            ->route('pretb_record.create')
            ->withInput($input);
    }

    public function lookup(Request $request)
    {
        $cid = $request->input('cid');
        if (!$cid) {
            return response()->json(['message' => 'CID required'], 400);
        }

        $record = PreTbRecord::query()
            ->where('cid', $cid)
            ->when(Schema::hasColumn('pre_tb_records', 'clinic_name') && session('tb.clinic'), function ($query) {
                $query->where('clinic_name', session('tb.clinic'));
            })
            ->latest('date_of_screening')
            ->latest('id')
            ->first();

        if (!$record) {
            $record = PreTbRecord::query()
                ->where('cid', $cid)
                ->latest('date_of_screening')
                ->latest('id')
                ->first();
        }

        if (!$record) {
            return response()->json(['message' => 'Not found'], 404);
        }

        return response()->json([
            'name' => $record->name,
            'age' => $record->age,
            'sex' => $record->sex,
            'height' => $this->formatHeightForForm($record->height) ?? $record->height,
            'weight' => $record->weight,
            'phone' => $record->phone,
        ]);
    }

    public function downloadSync(Request $request)
    {
        $request->validate([
            'since' => ['nullable', 'date'],
        ]);

        $query = PreTbRecord::query()->latest('updated_at');
        if ($request->filled('since')) {
            try {
                $query->where('updated_at', '>=', Carbon::parse($request->input('since')));
            } catch (\Throwable $e) {
                // validation already catches bad dates; keep this defensive for older clients
            }
        }

        return response()->json([
            'records' => $query->get()->map(fn (PreTbRecord $record) => $this->transformForSync($record)),
            'synced_at' => now()->toISOString(),
        ]);
    }

    public function uploadSync(Request $request)
    {
        $payload = $request->validate([
            'records' => ['array'],
            'records.*' => ['array'],
        ]);

        $records = $payload['records'] ?? [];
        $results = [
            'synced' => [],
            'errors' => [],
        ];

        foreach ($records as $index => $row) {
            $localUuid = $row['_local_uuid'] ?? $row['local_uuid'] ?? null;
            $serverId = $row['server_id'] ?? $row['id'] ?? null;

            try {
                unset($row['xray_image'], $row['xray_image_path']);
                $row['export_pdf_after_save'] = '0';

                $formRequest = new Request($row);
                $formRequest->setUserResolver(function () use ($request) {
                    return $request->user();
                });

                $validated = $this->validateAndNormalize($formRequest, false);
                $futureErrors = $this->validateNotFuture([
                    'date_of_screening' => $validated['date_of_screening'] ?? null,
                    'dofnv' => $validated['dofnv'] ?? null,
                    'chest_xray_date' => $validated['chest_xray_date'] ?? null,
                    'genexpert_date' => $validated['genexpert_date'] ?? null,
                    'truenat_date' => $validated['truenat_date'] ?? null,
                    'hiv_det_date' => $validated['hiv_det_date'] ?? null,
                    'crp_date' => $validated['crp_date'] ?? null,
                    'radio_request_date' => $validated['radio_request_date'] ?? null,
                    'sputum_afb_date' => $validated['sputum_afb_date'] ?? null,
                    'antibiotic_date' => $validated['antibiotic_date'] ?? null,
                    'tb_treat_date' => $validated['tb_treat_date'] ?? null,
                ], ['dofnv']);

                if (!empty($futureErrors)) {
                    $results['errors'][] = [
                        'index' => $index,
                        'local_uuid' => $localUuid,
                        'message' => collect($futureErrors)->flatten()->first(),
                    ];
                    continue;
                }

                $validated = $this->filterToTableColumns($validated);
                $record = null;

                if ($serverId) {
                    $record = PreTbRecord::find($serverId);
                }

                if (!$record && !empty($validated['cid']) && !empty($validated['date_of_screening'])) {
                    $record = PreTbRecord::query()
                        ->where('cid', (string) $validated['cid'])
                        ->whereDate('date_of_screening', $validated['date_of_screening'])
                        ->first();
                }

                if ($record) {
                    $record->fill($validated);
                    $record->save();
                } else {
                    $record = PreTbRecord::create($validated);
                }

                $results['synced'][] = [
                    'index' => $index,
                    'local_uuid' => $localUuid,
                    'server_id' => $record->id,
                    'record' => $this->transformForSync($record->fresh()),
                ];
            } catch (\Illuminate\Validation\ValidationException $e) {
                $results['errors'][] = [
                    'index' => $index,
                    'local_uuid' => $localUuid,
                    'message' => collect($e->errors())->flatten()->first() ?? 'Validation failed.',
                ];
            } catch (\Throwable $e) {
                Log::error('Pre-TB sync upload failed', [
                    'index' => $index,
                    'local_uuid' => $localUuid,
                    'error' => $e->getMessage(),
                ]);
                $results['errors'][] = [
                    'index' => $index,
                    'local_uuid' => $localUuid,
                    'message' => $e->getMessage(),
                ];
            }
        }

        return response()->json($results);
    }

    private function transformForSync(PreTbRecord $record): array
    {
        $payload = Arr::except($record->toArray(), ['xray_image_path']);
        $payload['server_id'] = $record->id;
        foreach ([
            'date_of_screening',
            'dofnv',
            'chest_xray_date',
            'genexpert_date',
            'truenat_date',
            'hiv_det_date',
            'crp_date',
            'radio_request_date',
            'sputum_afb_date',
            'antibiotic_date',
            'tb_treat_date',
            'created_at',
            'updated_at',
        ] as $field) {
            if ($record->{$field}) {
                $payload[$field] = $record->{$field} instanceof \DateTimeInterface
                    ? $record->{$field}->format(str_ends_with($field, '_at') ? 'c' : 'Y-m-d')
                    : (string) $record->{$field};
            }
        }

        return $payload;
    }

    public function export(Request $request): StreamedResponse
    {
        $records = PreTbRecord::orderBy('created_at')->get();
        $format = $request->input('format', 'codes');
        $format = in_array($format, ['codes', 'labels', 'analysis', 'counts'], true) ? $format : 'codes';

        if ($format === 'analysis') {
            return $this->exportAnalysis($records);
        }
        if ($format === 'counts') {
            return $this->exportCounts($records);
        }

        $tb03Lookup = $this->buildTb03Lookup($records);
        $exportSchema = $this->exportSchema($tb03Lookup);
        $exportCols = array_keys($exportSchema);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray($exportCols, null, 'A1');

        $isDateCol = static function (string $col): bool {
            return $col === 'dofscrrening'
                || $col === 'dofnv'
                || str_ends_with($col, '_date');
        };

        $rowIndex = 2;
        foreach ($records as $record) {
            $row = [];
            foreach ($exportCols as $col) {
                $value = $exportSchema[$col]($record);
                if ($isDateCol($col)) {
                    if ($value instanceof \DateTimeInterface) {
                        $value = ExcelDate::PHPToExcel(Carbon::instance($value)->startOfDay());
                    } elseif (is_string($value) && $value !== '') {
                        // Backward-compat if any schema still returns strings.
                        try {
                            $value = ExcelDate::PHPToExcel(Carbon::parse($value)->startOfDay());
                        } catch (\Throwable $e) {
                            // keep original
                        }
                    }
                } elseif ($value instanceof Carbon) {
                    $value = $value->format('d-m-Y');
                }
                if ($format === 'labels') {
                    $value = $this->labelForExportField($col, $value);
                }
                $row[] = $value;
            }
            $sheet->fromArray($row, null, 'A' . $rowIndex);
            $rowIndex++;
        }

        // Apply Excel date format so filtering/sorting works as dates, not text.
        $lastRow = $rowIndex - 1;
        if ($lastRow >= 2) {
            foreach ($exportCols as $idx => $colName) {
                if (!$isDateCol($colName)) {
                    continue;
                }
                $colLetter = Coordinate::stringFromColumnIndex($idx + 1);
                $sheet->getStyle($colLetter . '2:' . $colLetter . $lastRow)
                    ->getNumberFormat()
                    ->setFormatCode('dd-mm-yyyy');
            }
        }

        $writer = new Xlsx($spreadsheet);

        $filename = $format === 'labels'
            ? 'pretb_records_labels_' . date('Ymd_His') . '.xlsx'
            : 'pretb_records_codes_' . date('Ymd_His') . '.xlsx';

        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    private function exportAnalysis($records): StreamedResponse
    {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getProperties()->setTitle('Pre-TB analysis export');

        $dataSheet = $spreadsheet->getActiveSheet();
        $dataSheet->setTitle('analysis_data');

        $schema = $this->analysisExportSchema();
        $cols = array_keys($schema);
        $dataSheet->fromArray($cols, null, 'A1');

        $isDateCol = static function (string $col): bool {
            // analysis schema uses *_date and date_of_* names
            return $col === 'created_at'
                || str_starts_with($col, 'date_of_')
                || str_ends_with($col, '_date');
        };

        $rowIndex = 2;
        foreach ($records as $record) {
            $row = [];
            foreach ($cols as $col) {
                $value = $schema[$col]($record);
                if ($isDateCol($col)) {
                    if ($value instanceof \DateTimeInterface) {
                        $value = $col === 'created_at'
                            ? ExcelDate::PHPToExcel(Carbon::instance($value))
                            : ExcelDate::PHPToExcel(Carbon::instance($value)->startOfDay());
                    } elseif (is_string($value) && $value !== '') {
                        try {
                            $parsed = Carbon::parse($value);
                            $value = $col === 'created_at'
                                ? ExcelDate::PHPToExcel($parsed)
                                : ExcelDate::PHPToExcel($parsed->startOfDay());
                        } catch (\Throwable $e) {
                            // keep original
                        }
                    }
                }
                $row[] = $value;
            }
            $dataSheet->fromArray($row, null, 'A' . $rowIndex);
            $rowIndex++;
        }

        $lastRow = $rowIndex - 1;
        if ($lastRow >= 2) {
            foreach ($cols as $idx => $colName) {
                if (!$isDateCol($colName)) {
                    continue;
                }
                $colLetter = Coordinate::stringFromColumnIndex($idx + 1);
                $formatCode = $colName === 'created_at' ? 'yyyy-mm-dd hh:mm' : 'yyyy-mm-dd';
                $dataSheet->getStyle($colLetter . '2:' . $colLetter . $lastRow)
                    ->getNumberFormat()
                    ->setFormatCode($formatCode);
            }
        }

        $summarySheet = $spreadsheet->createSheet();
        $summarySheet->setTitle('summary');
        $this->fillAnalysisSummarySheet($summarySheet, $records);

        $writer = new Xlsx($spreadsheet);
        $filename = 'pretb_records_analysis_' . date('Ymd_His') . '.xlsx';

        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    private function exportCounts($records): StreamedResponse
    {
        $spreadsheet = new Spreadsheet();
        $spreadsheet->getProperties()->setTitle('Pre-TB counted summary');

        $countsSheet = $spreadsheet->getActiveSheet();
        $countsSheet->setTitle('counts');
        $this->fillCountsSheet($countsSheet, $records);

        $symptomsSheet = $spreadsheet->createSheet();
        $symptomsSheet->setTitle('symptoms');
        $this->fillCountsSymptomsSheet($symptomsSheet, $records);

        $trendsSheet = $spreadsheet->createSheet();
        $trendsSheet->setTitle('trends');
        $this->fillCountsTrendsSheet($trendsSheet, $records);

        $writer = new Xlsx($spreadsheet);
        $filename = 'pretb_records_counts_' . date('Ymd_His') . '.xlsx';

        return response()->streamDownload(function () use ($writer) {
            $writer->save('php://output');
        }, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    private function fillCountsSheet($sheet, $records): void
    {
        $sheet->fromArray(['section', 'category', 'n', 'pct_total', 'pct_nonmissing'], null, 'A1');
        $row = 2;
        $total = count($records);

        $write = function (string $section, array $counts, int $nonMissing) use (&$row, $sheet, $total) {
            arsort($counts);
            foreach ($counts as $category => $n) {
                $pctTotal = $total > 0 ? round(($n / $total) * 100, 1) : 0;
                $pctNon = $nonMissing > 0 ? round(($n / $nonMissing) * 100, 1) : 0;
                $sheet->fromArray([$section, $category, $n, $pctTotal, $pctNon], null, 'A' . $row);
                $row++;
            }
            $row++;
        };

        $writeFrequency = function (string $section, callable $valueFn) use ($records, $write) {
            $counts = [];
            $nonMissing = 0;
            foreach ($records as $r) {
                $v = $valueFn($r);
                if ($v === null || $v === '') {
                    $counts['Missing'] = ($counts['Missing'] ?? 0) + 1;
                    continue;
                }
                $nonMissing++;
                $k = (string)$v;
                $counts[$k] = ($counts[$k] ?? 0) + 1;
            }
            $write($section, $counts, $nonMissing);
        };

        $writeFrequency('sex', fn(PreTbRecord $r) => (string)$r->sex === '1' ? 'Male' : ((string)$r->sex === '2' ? 'Female' : null));
        $writeFrequency('age_group', fn(PreTbRecord $r) => $this->ageGroup($r->age));
        $writeFrequency('mode_of_entry', fn(PreTbRecord $r) => $this->labelForExportField('mode_of_entry', $r->mode_of_entry));
        $writeFrequency('tb_symptoms', fn(PreTbRecord $r) => (string)$r->tb_symptoms === '1' ? 'Yes' : ((string)$r->tb_symptoms === '2' ? 'No' : null));

        $riskMap = [
            'smoking' => 'smoking',
            'alcohol' => 'alcohol',
            'PWID' => 'PWID',
            'PWUD' => 'PWUD',
            'DM' => 'DM',
            'hiv_status' => 'hiv_status',
        ];
        foreach ($riskMap as $field => $exportField) {
            $writeFrequency($field, fn(PreTbRecord $r) => $this->labelForExportField($exportField, $r->{$field}));
        }

        $writeFrequency('chest_xray', fn(PreTbRecord $r) => $this->labelForExportField('chest_x-ray', $r->chest_xray));
        $writeFrequency('chest_xray_fac', fn(PreTbRecord $r) => $this->labelForExportField('chest_x-ray_fac', $r->chest_xray_fac));
        $writeFrequency('sputum_afb', fn(PreTbRecord $r) => $this->labelForExportField('sputum_afb', $r->sputum_afb));
        $writeFrequency('sputum_afb_res', fn(PreTbRecord $r) => $this->labelForExportField('sputum_afb_res', $r->sputum_afb_res));

        $writeFrequency('genexpert', fn(PreTbRecord $r) => $this->labelForExportField('genexpert', $r->genexpert));
        $writeFrequency('genexpert_res', fn(PreTbRecord $r) => $this->labelForExportField('genexpert_res', $r->genexpert_res));
        $writeFrequency('truenat', fn(PreTbRecord $r) => $this->labelForExportField('truenat', $r->truenat));
        $writeFrequency('truenat_res', fn(PreTbRecord $r) => $this->labelForExportField('truenat_res', $r->truenat_res));

        $writeFrequency('hiv_det_tested', fn(PreTbRecord $r) => $this->labelForExportField('hiv_det', $r->hiv_det));
        $writeFrequency('hiv_det_res', fn(PreTbRecord $r) => $this->labelForExportField('hiv_det_res', $r->hiv_det_res));
        $writeFrequency('crp_tested', fn(PreTbRecord $r) => $this->labelForExportField('crp', $r->crp));
        $writeFrequency('tb_treat', fn(PreTbRecord $r) => $this->labelForExportField('tb_treat', $r->tb_treat));
        $writeFrequency('tb_treat_regimen', fn(PreTbRecord $r) => $this->labelForExportField('tb_treat_regimen', $r->tb_treat_regimen));
    }

    private function fillCountsSymptomsSheet($sheet, $records): void
    {
        $sheet->fromArray(['symptom', 'yes_n', 'no_n', 'missing_n', 'mean_days_yes', 'median_days_yes'], null, 'A1');
        $row = 2;

        $symptoms = [
            'fever' => 'fever_days',
            'cough' => 'cough_days',
            'hemoptysis' => 'hemoptysis_days',
            'weight_loss' => 'weight_loss_days',
            'appetite_loss' => 'appetite_loss_days',
            'chest_pain' => 'chest_pain_days',
            'night_sweats' => 'night_sweats_days',
            'neck_glands' => 'neck_glands_days',
            'fatigue' => 'fatigue_days',
        ];

        foreach ($symptoms as $slug => $daysCol) {
            $yes = 0;
            $no = 0;
            $missing = 0;
            $days = [];
            foreach ($records as $r) {
                $present = $r->{$slug . '_present'};
                if ($present === null || $present === '') {
                    $missing++;
                    continue;
                }
                if ((string)$present === '1') {
                    $yes++;
                    $d = $r->{$daysCol};
                    if (is_numeric($d)) {
                        $days[] = (float)$d;
                    }
                } elseif ((string)$present === '2') {
                    $no++;
                } else {
                    $missing++;
                }
            }
            $mean = !empty($days) ? round(array_sum($days) / count($days), 1) : null;
            $median = $this->median($days);
            $sheet->fromArray([$slug, $yes, $no, $missing, $mean, $median], null, 'A' . $row);
            $row++;
        }
    }

    private function fillCountsTrendsSheet($sheet, $records): void
    {
        $sheet->fromArray(['month', 'n'], null, 'A1');
        $counts = [];
        foreach ($records as $r) {
            $d = $r->date_of_screening;
            if (!$d) {
                $counts['Missing'] = ($counts['Missing'] ?? 0) + 1;
                continue;
            }
            try {
                $m = Carbon::parse($d)->format('Y-m');
            } catch (\Exception $e) {
                $m = 'Invalid';
            }
            $counts[$m] = ($counts[$m] ?? 0) + 1;
        }
        ksort($counts);
        $row = 2;
        foreach ($counts as $m => $n) {
            $sheet->fromArray([$m, $n], null, 'A' . $row);
            $row++;
        }
    }

    private function median(array $values): ?float
    {
        $vals = array_values(array_filter($values, fn($v) => is_numeric($v)));
        if (empty($vals)) {
            return null;
        }
        sort($vals);
        $count = count($vals);
        $mid = (int)floor(($count - 1) / 2);
        if ($count % 2) {
            return (float)$vals[$mid];
        }
        return round(((float)$vals[$mid] + (float)$vals[$mid + 1]) / 2, 1);
    }

    private function analysisExportSchema(): array
    {
        $heightCm = fn($h) => $this->formatHeightForForm($h);
        $sexLabel = fn($v) => (string)$v === '1' ? 'Male' : ((string)$v === '2' ? 'Female' : null);

        $symptoms = [
            'fever' => 'fever_days',
            'cough' => 'cough_days',
            'hemoptysis' => 'hemoptysis_days',
            'weight_loss' => 'weight_loss_days',
            'appetite_loss' => 'appetite_loss_days',
            'chest_pain' => 'chest_pain_days',
            'night_sweats' => 'night_sweats_days',
            'neck_glands' => 'neck_glands_days',
            'fatigue' => 'fatigue_days',
        ];

        return array_merge([
            'record_id' => fn(PreTbRecord $r) => $r->id,
            'patient_id' => fn(PreTbRecord $r) => $this->hashPatientId($r->cid),
            'date_of_screening' => fn(PreTbRecord $r) => $r->date_of_screening,
            'date_of_next_visit' => fn(PreTbRecord $r) => $r->dofnv,
            'phone' => fn(PreTbRecord $r) => $r->phone,
            'age_years' => fn(PreTbRecord $r) => $r->age,
            'age_group' => fn(PreTbRecord $r) => $this->ageGroup($r->age),
            'sex' => fn(PreTbRecord $r) => $r->sex,
            'sex_label' => fn(PreTbRecord $r) => $sexLabel($r->sex),
            'mode_of_entry' => fn(PreTbRecord $r) => $r->mode_of_entry,
            'mode_of_entry_label' => fn(PreTbRecord $r) => $this->labelForExportField('mode_of_entry', $r->mode_of_entry),
            'type_of_screening' => fn(PreTbRecord $r) => $r->type_of_screening,
            'height_cm' => fn(PreTbRecord $r) => $heightCm($r->height),
            'weight_kg' => fn(PreTbRecord $r) => $r->weight,
            'bmi' => fn(PreTbRecord $r) => $r->bmi ?? $this->calculateBmi($r->weight, $r->height),
            'bmi_category' => fn(PreTbRecord $r) => $this->bmiCategory($r->bmi ?? $this->calculateBmi($r->weight, $r->height), $r->age),
            'main_presenting_complaint' => fn(PreTbRecord $r) => $r->main_presenting_complaint,
            'tb_symptoms' => fn(PreTbRecord $r) => $r->tb_symptoms,
            'tb_symptoms_label' => fn(PreTbRecord $r) => $this->labelForExportField('fever', $r->tb_symptoms),
            'symptom_count' => function (PreTbRecord $r) use ($symptoms) {
                $count = 0;
                foreach (array_keys($symptoms) as $slug) {
                    $present = $r->{$slug . '_present'};
                    if ((string)$present === '1') {
                        $count++;
                    }
                }
                return $count;
            },
            'symptom_any' => function (PreTbRecord $r) use ($symptoms) {
                foreach (array_keys($symptoms) as $slug) {
                    $present = $r->{$slug . '_present'};
                    if ((string)$present === '1') {
                        return 1;
                    }
                }
                return 0;
            },
        ], $this->analysisSymptomColumns($symptoms), [
            'smoking' => fn(PreTbRecord $r) => $r->smoking,
            'smoking_label' => fn(PreTbRecord $r) => $this->labelForExportField('smoking', $r->smoking),
            'alcohol' => fn(PreTbRecord $r) => $r->alcohol,
            'alcohol_label' => fn(PreTbRecord $r) => $this->labelForExportField('alcohol', $r->alcohol),
            'PWID' => fn(PreTbRecord $r) => $r->PWID,
            'PWID_label' => fn(PreTbRecord $r) => $this->labelForExportField('PWID', $r->PWID),
            'PWUD' => fn(PreTbRecord $r) => $r->PWUD,
            'PWUD_label' => fn(PreTbRecord $r) => $this->labelForExportField('PWUD', $r->PWUD),
            'DM' => fn(PreTbRecord $r) => $r->DM,
            'DM_label' => fn(PreTbRecord $r) => $this->labelForExportField('DM', $r->DM),
            'hiv_status' => fn(PreTbRecord $r) => $r->hiv_status,
            'hiv_status_label' => fn(PreTbRecord $r) => $this->labelForExportField('hiv_status', $r->hiv_status),
            'chest_xray' => fn(PreTbRecord $r) => $r->chest_xray,
            'chest_xray_label' => fn(PreTbRecord $r) => $this->labelForExportField('chest_x-ray', $r->chest_xray),
            'chest_xray_fac' => fn(PreTbRecord $r) => $r->chest_xray_fac,
            'chest_xray_fac_label' => fn(PreTbRecord $r) => $this->labelForExportField('chest_x-ray_fac', $r->chest_xray_fac),
            'chest_xray_date' => fn(PreTbRecord $r) => $r->chest_xray_date,
            'sputum_afb' => fn(PreTbRecord $r) => $r->sputum_afb,
            'sputum_afb_label' => fn(PreTbRecord $r) => $this->labelForExportField('sputum_afb', $r->sputum_afb),
            'sputum_afb_date' => fn(PreTbRecord $r) => $r->sputum_afb_date,
            'sputum_afb_res' => fn(PreTbRecord $r) => $r->sputum_afb_res,
            'sputum_afb_res_label' => fn(PreTbRecord $r) => $this->labelForExportField('sputum_afb_res', $r->sputum_afb_res),
            'genexpert' => fn(PreTbRecord $r) => $r->genexpert,
            'genexpert_label' => fn(PreTbRecord $r) => $this->labelForExportField('genexpert', $r->genexpert),
            'genexpert_date' => fn(PreTbRecord $r) => $r->genexpert_date,
            'type_of_specimen_geneXpert' => fn(PreTbRecord $r) => $r->type_of_specimen_geneXpert,
            'genexpert_res' => fn(PreTbRecord $r) => $r->genexpert_res,
            'genexpert_res_label' => fn(PreTbRecord $r) => $this->labelForExportField('genexpert_res', $r->genexpert_res),
            'truenat' => fn(PreTbRecord $r) => $r->truenat,
            'truenat_label' => fn(PreTbRecord $r) => $this->labelForExportField('truenat', $r->truenat),
            'truenat_date' => fn(PreTbRecord $r) => $r->truenat_date,
            'type_of_specimen_truenat' => fn(PreTbRecord $r) => $r->type_of_specimen_truenat,
            'truenat_res' => fn(PreTbRecord $r) => $r->truenat_res,
            'truenat_res_label' => fn(PreTbRecord $r) => $this->labelForExportField('truenat_res', $r->truenat_res),
            'hiv_det' => fn(PreTbRecord $r) => $r->hiv_det,
            'hiv_det_label' => fn(PreTbRecord $r) => $this->labelForExportField('hiv_det', $r->hiv_det),
            'hiv_det_date' => fn(PreTbRecord $r) => $r->hiv_det_date,
            'hiv_det_res' => fn(PreTbRecord $r) => $r->hiv_det_res,
            'hiv_det_res_label' => fn(PreTbRecord $r) => $this->labelForExportField('hiv_det_res', $r->hiv_det_res),
            'crp' => fn(PreTbRecord $r) => $r->crp,
            'crp_label' => fn(PreTbRecord $r) => $this->labelForExportField('crp', $r->crp),
            'crp_date' => fn(PreTbRecord $r) => $r->crp_date,
            'crp_result' => fn(PreTbRecord $r) => $r->crp_result,
            'tb_treat' => fn(PreTbRecord $r) => $r->tb_treat,
            'tb_treat_label' => fn(PreTbRecord $r) => $this->labelForExportField('tb_treat', $r->tb_treat),
            'tb_treat_date' => fn(PreTbRecord $r) => $r->tb_treat_date,
            'tb_diagnosis' => fn(PreTbRecord $r) => $r->tb_diagnosis,
            'antibiotics' => fn(PreTbRecord $r) => $r->antibiotics,
            'antibiotics_label' => fn(PreTbRecord $r) => $this->labelForExportField('antibiotics', $r->antibiotics),
            'antibiotic_date' => fn(PreTbRecord $r) => $r->antibiotic_date,
            'drug' => fn(PreTbRecord $r) => $r->drug,
            'created_at' => fn(PreTbRecord $r) => $r->created_at,
        ]);
    }

    private function analysisSymptomColumns(array $symptoms): array
    {
        $cols = [];
        foreach ($symptoms as $slug => $daysColumn) {
            $presentColumn = $slug . '_present';
            $cols["{$slug}_present"] = fn(PreTbRecord $r) => $r->{$presentColumn};
            $cols["{$slug}_present_label"] = fn(PreTbRecord $r) => $this->labelForExportField('fever', $r->{$presentColumn});
            $cols["{$slug}_days"] = fn(PreTbRecord $r) => $r->{$daysColumn};
        }
        return $cols;
    }

    private function fillAnalysisSummarySheet($sheet, $records): void
    {
        $total = count($records);
        $sheet->fromArray(['metric', 'value'], null, 'A1');
        $sheet->fromArray(['total_records', $total], null, 'A2');

        $row = 4;
        $sections = [
            'sex' => fn(PreTbRecord $r) => (string)$r->sex === '1' ? 'Male' : ((string)$r->sex === '2' ? 'Female' : 'Unknown'),
            'mode_of_entry' => fn(PreTbRecord $r) => $this->labelForExportField('mode_of_entry', $r->mode_of_entry) ?? 'Unknown',
            'tb_symptoms' => fn(PreTbRecord $r) => ((string)$r->tb_symptoms === '1' ? 'Yes' : ((string)$r->tb_symptoms === '2' ? 'No' : 'Unknown')),
            'tb_treat' => fn(PreTbRecord $r) => $this->labelForExportField('tb_treat', $r->tb_treat) ?? 'Unknown',
        ];

        foreach ($sections as $title => $groupFn) {
            $sheet->fromArray([strtoupper($title), 'count'], null, 'A' . $row);
            $row++;

            $counts = [];
            foreach ($records as $r) {
                $k = (string)$groupFn($r);
                $counts[$k] = ($counts[$k] ?? 0) + 1;
            }
            arsort($counts);
            foreach ($counts as $k => $c) {
                $sheet->fromArray([$k, $c], null, 'A' . $row);
                $row++;
            }
            $row++;
        }
    }

    private function ageGroup($age): ?string
    {
        if ($age === null || $age === '') {
            return null;
        }
        if (!is_numeric($age)) {
            return null;
        }
        $a = (float)$age;
        if ($a < 5) return '0-4';
        if ($a < 15) return '5-14';
        if ($a < 25) return '15-24';
        if ($a < 35) return '25-34';
        if ($a < 45) return '35-44';
        if ($a < 55) return '45-54';
        return '55+';
    }

    private function bmiCategory($bmi, $age): ?string
    {
        if ($bmi === null || $bmi === '') {
            return null;
        }
        if (!is_numeric($bmi)) {
            return null;
        }
        if ($age !== null && is_numeric($age) && (float)$age < 15) {
            return 'child';
        }
        $v = (float)$bmi;
        if ($v < 18.5) return 'underweight';
        if ($v < 25) return 'normal';
        if ($v < 30) return 'overweight';
        return 'obese';
    }

    private function hashPatientId(?string $cid): ?string
    {
        if (!$cid) {
            return null;
        }
        $key = config('app.key') ?: 'pretb';
        return substr(hash('sha256', $cid . '|' . $key), 0, 16);
    }

    public function import(Request $request)
    {
        $request->validate([
            'import_file' => ['required', 'file', 'mimes:csv,txt,xlsx'],
        ]);

        $file = $request->file('import_file');
        $extension = strtolower($file->getClientOriginalExtension());
        return $this->importFromPath($file->getRealPath(), $extension);
    }

    public function importLocal()
    {
        $path = storage_path('app/preTB.xlsx');
        if (!file_exists($path)) {
            return redirect()->back()->withErrors(['import_file' => "File not found: {$path}"]);
        }

        return $this->importFromPath($path, 'xlsx');
    }

    private function importFromPath(string $path, string $extension)
    {
        $rows = [];

        if ($extension === 'xlsx') {
            $spreadsheet = IOFactory::load($path);
            $rows = $spreadsheet->getActiveSheet()->toArray();
        } else {
            $rows = array_map('str_getcsv', file($path));
        }

        if (count($rows) < 2) {
            return redirect()->back()->withErrors(['import_file' => 'File must include a header and at least one row.']);
        }

        $headerRaw = array_map('trim', array_shift($rows));
        $header = array_map([$this, 'normalizeHeader'], $headerRaw);

        $importMap = $this->importMap();
        $preparedRows = [];
        $dupKeyRows = [];

        foreach ($rows as $rowIndex => $row) {
            $rowNumber = $rowIndex + 2; // +2 to account for header row
            if (count(array_filter($row)) === 0) {
                Log::info('Pre-TB import skipped empty row', ['row' => $rowNumber]);
                continue;
            }

            $rowData = [];
            $extras = [];

            foreach ($header as $i => $col) {
                $raw = $row[$i] ?? null;
                if ($col && array_key_exists($col, $importMap)) {
                    $mapped = $importMap[$col];
                    if ($mapped) {
                        $rowData[$mapped] = $raw;
                    }
                }
                if (in_array($col, ['md_diagnosis_oth', 'radiologist_result_oth'])) {
                    $extras[] = ($col === 'md_diagnosis_oth' ? 'MD other: ' : 'Radiologist other: ') . $raw;
                }
            }

            if (!isset($rowData['tb_symptoms']) && ($tbSymptoms = $this->getRawValue($header, $row, 'tb_symptoms'))) {
                $rowData['tb_symptoms'] = $tbSymptoms;
            }

            $symptomSlugs = [
                'fever',
                'cough',
                'hemoptysis',
                'weight_loss',
                'appetite_loss',
                'chest_pain',
                'night_sweats',
                'neck_glands',
                'fatigue',
            ];

            foreach ($symptomSlugs as $slug) {
                $presentKey = $slug . '_present';
                $daysKey = $slug . '_days';
                if (!array_key_exists($presentKey, $rowData) && array_key_exists($daysKey, $rowData)) {
                    $val = $rowData[$daysKey];
                    $rowData[$presentKey] = (is_numeric($val) && (int)$val > 0) ? 1 : 2;
                }
            }

            foreach ($symptomSlugs as $slug) {
                $presentKey = $slug . '_present';
                if (array_key_exists($presentKey, $rowData)) {
                    $rowData[$presentKey] = $this->normalizeYesNoToOneTwo($rowData[$presentKey]);
                }
            }

            if (!isset($rowData['tb_symptoms'])) {
                $symptomDayCols = [
                    'fever_days',
                    'cough_days',
                    'hemoptysis_days',
                    'weight_loss_days',
                    'appetite_loss_days',
                    'chest_pain_days',
                    'night_sweats_days',
                    'neck_glands_days',
                    'fatigue_days',
                ];
                $hasAny = false;
                foreach ($symptomSlugs as $slug) {
                    $presentKey = $slug . '_present';
                    if (isset($rowData[$presentKey]) && (string)$rowData[$presentKey] === '1') {
                        $hasAny = true;
                        break;
                    }
                }
                foreach ($symptomDayCols as $col) {
                    $val = $rowData[$col] ?? null;
                    if ($val === '' || $val === null) {
                        continue;
                    }
                    if (is_numeric($val) && (int)$val > 0) {
                        $hasAny = true;
                        break;
                    }
                    $hasAny = true;
                    break;
                }
                $rowData['tb_symptoms'] = $hasAny ? '1' : '2';
            }

            $dateFields = [
                'date_of_screening',
                'dofnv',
                'chest_xray_date',
                'genexpert_date',
                'truenat_date',
                'hiv_det_date',
                'crp_date',
                'radio_request_date',
                'sputum_afb_date',
                'antibiotic_date',
                'tb_treat_date',
            ];
            foreach ($dateFields as $field) {
                $rowData[$field] = $this->normalizeDate($rowData[$field] ?? null);
            }

            [$normHeight, $normWeight] = $this->normalizeAnthro($rowData['height'] ?? null, $rowData['weight'] ?? null);
            $rowData['height'] = $normHeight;
            $rowData['weight'] = $normWeight;

            if (!empty($extras)) {
                $rowData['comment'] = trim(($rowData['comment'] ?? '') . ' ' . implode(' | ', array_filter($extras)));
            }

            $rowData['bmi'] = $this->calculateBmi($rowData['weight'] ?? null, $rowData['height'] ?? null) ?? ($rowData['bmi'] ?? null);

            if (!empty($rowData['cid']) && !empty($rowData['date_of_screening'])) {
                $dupKey = (string)$rowData['cid'] . '|' . $rowData['date_of_screening'];
                if (!isset($dupKeyRows[$dupKey])) {
                    $dupKeyRows[$dupKey] = [];
                }
                $dupKeyRows[$dupKey][] = $rowNumber;
            }

            $preparedRows[] = [
                'row' => $rowNumber,
                'data' => $rowData,
            ];
        }

        $duplicateInFile = array_filter($dupKeyRows, function (array $rows) {
            return count($rows) > 1;
        });

        if (!empty($duplicateInFile)) {
            $examples = [];
            foreach (array_slice($duplicateInFile, 0, 5, true) as $key => $rows) {
                $examples[] = $key . ' (rows ' . implode(', ', $rows) . ')';
            }
            $message = 'Import stopped. Duplicate CID + date_of_screening found in file: ' . implode('; ', $examples);
            if (count($duplicateInFile) > 5) {
                $message .= ' ...';
            }
            Log::warning('Pre-TB import stopped: duplicate CID + date_of_screening in file', [
                'examples' => $examples,
            ]);
            return redirect()->back()->withErrors(['import_file' => $message]);
        }

        if (!empty($dupKeyRows)) {
            $cids = [];
            $dates = [];
            foreach (array_keys($dupKeyRows) as $key) {
                [$cid, $date] = explode('|', $key, 2);
                $cids[$cid] = true;
                $dates[$date] = true;
            }

            $existing = PreTbRecord::whereIn('cid', array_keys($cids))
                ->whereIn('date_of_screening', array_keys($dates))
                ->get(['cid', 'date_of_screening']);

            $existingKeys = [];
            foreach ($existing as $record) {
                $existingKeys[$record->cid . '|' . $record->date_of_screening] = true;
            }

            $duplicateExisting = array_intersect_key($existingKeys, $dupKeyRows);
            if (!empty($duplicateExisting)) {
                $examples = array_slice(array_keys($duplicateExisting), 0, 5);
                $message = 'Import stopped. Duplicate CID + date_of_screening already exists: ' . implode('; ', $examples);
                if (count($duplicateExisting) > 5) {
                    $message .= ' ...';
                }
                Log::warning('Pre-TB import stopped: duplicate CID + date_of_screening in database', [
                    'examples' => $examples,
                ]);
                return redirect()->back()->withErrors(['import_file' => $message]);
            }
        }

        $created = 0;
        foreach ($preparedRows as $prepared) {
            $rowData = $prepared['data'];
            $rowNumber = $prepared['row'];

            $validator = Validator::make($rowData, [
                'cid' => ['required', 'regex:/^[0-9]{10,12}$/'],
                'name' => ['required', 'string', 'max:255'],
                'age' => ['required', 'numeric', 'min:0', 'max:111'],
                'sex' => ['required', 'in:1,2'],
                'mode_of_entry' => ['required', 'regex:/^[1-7](,[1-7])*$/'],
                'date_of_screening' => ['required', 'date'],
            ]);

            if ($validator->fails()) {
                Log::warning('Pre-TB import skipped row: validation failed', [
                    'row' => $rowNumber,
                    'cid' => $rowData['cid'] ?? null,
                    'date_of_screening' => $rowData['date_of_screening'] ?? null,
                    'errors' => $validator->errors()->all(),
                ]);
                continue;
            }

            $futureErrors = $this->validateNotFuture([
                'date_of_screening' => $rowData['date_of_screening'],
                'dofnv' => $rowData['dofnv'] ?? null,
                'chest_xray_date' => $rowData['chest_xray_date'] ?? null,
                'genexpert_date' => $rowData['genexpert_date'] ?? null,
                'truenat_date' => $rowData['truenat_date'] ?? null,
                'hiv_det_date' => $rowData['hiv_det_date'] ?? null,
                'crp_date' => $rowData['crp_date'] ?? null,
                'radio_request_date' => $rowData['radio_request_date'] ?? null,
                'sputum_afb_date' => $rowData['sputum_afb_date'] ?? null,
                'antibiotic_date' => $rowData['antibiotic_date'] ?? null,
                'tb_treat_date' => $rowData['tb_treat_date'] ?? null,
            ], ['dofnv']);

            if (!empty($futureErrors)) {
                Log::warning('Pre-TB import skipped row: date in future', [
                    'row' => $rowNumber,
                    'cid' => $rowData['cid'] ?? null,
                    'date_of_screening' => $rowData['date_of_screening'] ?? null,
                    'errors' => $futureErrors,
                ]);
                continue;
            }

            $rowData = $this->filterToTableColumns($rowData);
            if (empty($rowData)) {
                Log::warning('Pre-TB import skipped row: no importable columns', [
                    'row' => $rowNumber,
                    'cid' => $rowData['cid'] ?? null,
                    'date_of_screening' => $rowData['date_of_screening'] ?? null,
                ]);
                continue;
            }
            PreTbRecord::create($rowData);
            $created++;
        }

        return redirect()->back()->with('status', "Import completed. Added {$created} records.");
    }

    public function store(Request $request)
    {
        $validated = $this->validateAndNormalize($request, false);

        $futureErrors = $this->validateNotFuture([
            'date_of_screening' => $validated['date_of_screening'],
            'dofnv' => $validated['dofnv'],
            'chest_xray_date' => $validated['chest_xray_date'],
            'genexpert_date' => $validated['genexpert_date'],
            'truenat_date' => $validated['truenat_date'],
            'hiv_det_date' => $validated['hiv_det_date'],
            'crp_date' => $validated['crp_date'] ?? null,
            'radio_request_date' => $validated['radio_request_date'],
            'sputum_afb_date' => $validated['sputum_afb_date'],
            'antibiotic_date' => $validated['antibiotic_date'],
            'tb_treat_date' => $validated['tb_treat_date'] ?? null,
        ], ['dofnv']);

        if (!empty($futureErrors)) {
            return redirect()->back()->withErrors($futureErrors)->withInput();
        }

        if ($this->hasDuplicateScreeningKey($validated['cid'] ?? null, $validated['date_of_screening'] ?? null)) {
            return redirect()
                ->back()
                ->withErrors(['dofscrrening' => 'A Pre-TB record with this CID and Date of screening already exists.'])
                ->withInput();
        }

        $validated['xray_image_path'] = $this->storeXrayImage($request, null);
        $validated = $this->filterToTableColumns($validated);
        $preTbRecord = PreTbRecord::create($validated);

        if ($request->boolean('export_pdf_after_save')) {
            return $this->exportVisitPdf($preTbRecord);
        }

        return redirect()
            ->back()
            ->with('status', 'Pre-TB record saved successfully.');
    }

    public function update(Request $request, PreTbRecord $preTbRecord)
    {
        $validated = $this->validateAndNormalize($request, true);

        $futureErrors = $this->validateNotFuture([
            'date_of_screening' => $validated['date_of_screening'],
            'dofnv' => $validated['dofnv'],
            'chest_xray_date' => $validated['chest_xray_date'],
            'genexpert_date' => $validated['genexpert_date'],
            'truenat_date' => $validated['truenat_date'],
            'hiv_det_date' => $validated['hiv_det_date'],
            'crp_date' => $validated['crp_date'] ?? null,
            'radio_request_date' => $validated['radio_request_date'],
            'sputum_afb_date' => $validated['sputum_afb_date'],
            'antibiotic_date' => $validated['antibiotic_date'],
            'tb_treat_date' => $validated['tb_treat_date'] ?? null,
        ], ['dofnv']);

        if (!empty($futureErrors)) {
            return redirect()
                ->route('pretb_record.create', ['edit' => $preTbRecord->id])
                ->withErrors($futureErrors)
                ->withInput();
        }

        if ($this->hasDuplicateScreeningKey(
            $validated['cid'] ?? null,
            $validated['date_of_screening'] ?? null,
            (int) $preTbRecord->id
        )) {
            return redirect()
                ->route('pretb_record.create', ['edit' => $preTbRecord->id])
                ->withErrors(['dofscrrening' => 'Another Pre-TB record with this CID and Date of screening already exists.'])
                ->withInput();
        }

        $validated['xray_image_path'] = $this->storeXrayImage($request, $preTbRecord->xray_image_path);
        $validated = $this->filterToTableColumns($validated);
        $preTbRecord->fill($validated);
        $preTbRecord->save();

        if ($request->boolean('export_pdf_after_save')) {
            return $this->exportVisitPdf($preTbRecord->fresh());
        }

        return $this->edit($preTbRecord)
            ->with('status', 'Pre-TB record updated successfully.');
    }

    public function destroy(PreTbRecord $preTbRecord)
    {
        if (!empty($preTbRecord->xray_image_path) && Storage::disk('public')->exists($preTbRecord->xray_image_path)) {
            Storage::disk('public')->delete($preTbRecord->xray_image_path);
        }

        $preTbRecord->delete();

        return redirect()
            ->route('pretb_record.manage')
            ->with('status', 'Pre-TB record deleted successfully.');
    }

    private function validateAndNormalize(Request $request, bool $enforceDateDependencies = false): array
    {
        return $this->payloadService->validateAndNormalize($request, $enforceDateDependencies);
    }

    private function hasDuplicateScreeningKey($cid, $dateOfScreening, ?int $ignoreId = null): bool
    {
        if (!$cid || !$dateOfScreening) {
            return false;
        }

        $query = PreTbRecord::query()
            ->where('cid', (string) $cid)
            ->whereDate('date_of_screening', $dateOfScreening);

        if ($ignoreId !== null) {
            $query->where('id', '!=', $ignoreId);
        }

        return $query->exists();
    }

    private function filterToTableColumns(array $data): array
    {
        static $columns = null;
        if ($columns === null) {
            try {
                $columns = Schema::getColumnListing('pre_tb_records');
            } catch (\Throwable $e) {
                return $data;
            }
        }
        if (!$columns) {
            return $data;
        }
        return array_intersect_key($data, array_flip($columns));
    }

    private function mdInitialOptions(): array
    {
        $clinicCode = (string) (optional(auth()->user())->clinic ?? '');
        $clinicMap = [
            '71' => 'A',
            '72' => 'B',
            '73' => 'SPT',
            '74' => 'TL',
            '75' => 'Winka',
            '76' => 'TBZY',
            '77' => 'PTO-DT',
            '78' => 'PTO-MCB',
            '80' => 'Hpakant',
            '81' => 'HTY-C2',
            '82' => 'Taze',
            '83' => 'HTY-C1',
            '84' => 'SDG',
        ];

        $candidates = [];
        if ($clinicCode !== '') {
            $candidates[] = $clinicCode;
        }
        if (array_key_exists($clinicCode, $clinicMap)) {
            $label = $clinicMap[$clinicCode];
            $candidates[] = $label;
            if (str_starts_with($label, 'HTY-')) {
                $candidates[] = substr($label, 4);
            }
        }
        $candidates = array_values(array_unique(array_filter($candidates)));

        $fetch = function (?string $connection, bool $useClinicFilter) use ($candidates) {
            try {
                $hasTable = $connection
                    ? Schema::connection($connection)->hasTable('md_initials')
                    : Schema::hasTable('md_initials');
                $hasInitialColumn = $connection
                    ? Schema::connection($connection)->hasColumn('md_initials', 'initial')
                    : Schema::hasColumn('md_initials', 'initial');
                if (!$hasTable || !$hasInitialColumn) {
                    return [];
                }

                $query = $connection
                    ? DB::connection($connection)->table('md_initials')->select('initial')
                    : DB::table('md_initials')->select('initial');

                $hasIsActive = $connection
                    ? Schema::connection($connection)->hasColumn('md_initials', 'is_active')
                    : Schema::hasColumn('md_initials', 'is_active');
                if ($hasIsActive) {
                    $query->where('is_active', true);
                }

                $hasClinicCode = $connection
                    ? Schema::connection($connection)->hasColumn('md_initials', 'clinic_code')
                    : Schema::hasColumn('md_initials', 'clinic_code');
                if ($useClinicFilter && !empty($candidates) && $hasClinicCode) {
                    $query->whereIn('clinic_code', $candidates);
                }

                return $query
                    ->whereNotNull('initial')
                    ->orderBy('initial')
                    ->pluck('initial')
                    ->map(fn($v) => trim((string) $v))
                    ->filter(fn($v) => $v !== '')
                    ->unique()
                    ->values()
                    ->all();
            } catch (\Throwable $e) {
                return [];
            }
        };

        $initials = $fetch(null, true);
        if (empty($initials)) {
            $initials = $fetch(null, false);
        }

        $connections = array_keys((array) config('database.connections', []));
        if (empty($initials) && in_array('OfficeLab', $connections, true)) {
            $initials = $fetch('OfficeLab', true);
            if (empty($initials)) {
                $initials = $fetch('OfficeLab', false);
            }
        }

        return $initials;
    }

    private function buildTb03Lookup($records): array
    {
        if (!Schema::hasTable('tb_register_o3_s')) {
            return [];
        }

        $cids = $records->pluck('cid')->filter()->map(fn($cid) => (string)$cid)->unique()->values();
        if ($cids->isEmpty()) {
            return [];
        }

        $matches = DB::table('tb_register_o3_s')
            ->whereIn('Pid_TB03', $cids)
            ->pluck('Pid_TB03');

        $lookup = [];
        foreach ($matches as $pid) {
            $lookup[(string)$pid] = true;
        }
        return $lookup;
    }

    private function exportSchema(array $tb03Lookup = []): array
    {
        $heightToCm = fn($h) => $this->formatHeightForForm($h);

        return [
            'clinic' => fn(PreTbRecord $r) => null,
            'cid' => fn(PreTbRecord $r) => $r->cid,
            'TB-03' => fn(PreTbRecord $r) => isset($tb03Lookup[(string)$r->cid]) ? 'yes' : 'no',
            'name' => fn(PreTbRecord $r) => $r->name,
            'age' => fn(PreTbRecord $r) => $r->age,
            'sex' => fn(PreTbRecord $r) => $r->sex,
            'typ_of_scr' => fn(PreTbRecord $r) => $r->type_of_screening,
            'height' => fn(PreTbRecord $r) => $heightToCm($r->height),
            'weight' => fn(PreTbRecord $r) => $r->weight,
            'bmi' => fn(PreTbRecord $r) => $r->bmi,
            'mode_of_entry' => fn(PreTbRecord $r) => $r->mode_of_entry,
            'dofscrrening' => fn(PreTbRecord $r) => $r->date_of_screening,
            'dofnv' => fn(PreTbRecord $r) => $r->dofnv,
            'phone' => fn(PreTbRecord $r) => $r->phone,
            'main_presenting_complaint' => fn(PreTbRecord $r) => $r->main_presenting_complaint,
            'fever' => fn(PreTbRecord $r) => $r->fever_present,
            'fever_dur' => fn(PreTbRecord $r) => $r->fever_days,
            'cough' => fn(PreTbRecord $r) => $r->cough_present,
            'cough_dur' => fn(PreTbRecord $r) => $r->cough_days,
            'hemoptysis' => fn(PreTbRecord $r) => $r->hemoptysis_present,
            'hemoptysis_dur' => fn(PreTbRecord $r) => $r->hemoptysis_days,
            'weight_loss' => fn(PreTbRecord $r) => $r->weight_loss_present,
            'weight_loss_dur' => fn(PreTbRecord $r) => $r->weight_loss_days,
            'loss_of_appetite' => fn(PreTbRecord $r) => $r->appetite_loss_present,
            'loss_of_appetite_dur' => fn(PreTbRecord $r) => $r->appetite_loss_days,
            'chest_pain' => fn(PreTbRecord $r) => $r->chest_pain_present,
            'chest_pain_dur' => fn(PreTbRecord $r) => $r->chest_pain_days,
            'night_sweats' => fn(PreTbRecord $r) => $r->night_sweats_present,
            'night_sweats_dur' => fn(PreTbRecord $r) => $r->night_sweats_days,
            'neck_glands' => fn(PreTbRecord $r) => $r->neck_glands_present,
            'neck_glands_dur' => fn(PreTbRecord $r) => $r->neck_glands_days,
            'fatigue' => fn(PreTbRecord $r) => $r->fatigue_present,
            'fatigue_dur' => fn(PreTbRecord $r) => $r->fatigue_days,
            'alcohol' => fn(PreTbRecord $r) => $r->alcohol,
            'smoking' => fn(PreTbRecord $r) => $r->smoking,
            'malnutrition' => fn(PreTbRecord $r) => $r->malnutrition,
            'PWID' => fn(PreTbRecord $r) => $r->PWID,
            'PWUD' => fn(PreTbRecord $r) => $r->PWUD,
            'DM' => fn(PreTbRecord $r) => $r->DM,
            'dm_tx_status' => fn(PreTbRecord $r) => $r->dm_tx_status,
            'hiv_status' => fn(PreTbRecord $r) => $r->hiv_status,
            'hiv_tx' => fn(PreTbRecord $r) => $r->hiv_tx,
            'his_tb_self' => fn(PreTbRecord $r) => $r->his_tb_self,
            'his_tb_family' => fn(PreTbRecord $r) => $r->his_tb_family,
            'chest_x-ray' => fn(PreTbRecord $r) => $r->chest_xray,
            'chest_x-ray_date' => fn(PreTbRecord $r) => $r->chest_xray_date,
            'chest_x-ray_fac' => fn(PreTbRecord $r) => $r->chest_xray_fac,
            'md_diagnosis' => fn(PreTbRecord $r) => $r->md_diagnosis,
            'md_diagnosis_oth' => fn(PreTbRecord $r) => $this->parseCommentExtras($r->comment)['md_diagnosis_oth'],
            'cad_score' => fn(PreTbRecord $r) => $r->cad_score,
            'comment' => fn(PreTbRecord $r) => $this->parseCommentExtras($r->comment)['comment'],
            'radio_request' => fn(PreTbRecord $r) => $r->radio_request,
            'radio_request_date' => fn(PreTbRecord $r) => $r->radio_request_date,
            'radiologist_result' => fn(PreTbRecord $r) => $r->radiologist_result,
            'radiologist_result_oth' => fn(PreTbRecord $r) => $this->parseCommentExtras($r->comment)['radiologist_result_oth'],
            'sputum_afb' => fn(PreTbRecord $r) => $r->sputum_afb,
            'sputum_afb_date' => fn(PreTbRecord $r) => $r->sputum_afb_date,
            'sputum_afb_res' => fn(PreTbRecord $r) => $r->sputum_afb_res,
            'genexpert' => fn(PreTbRecord $r) => $r->genexpert,
            'genexpert_date' => fn(PreTbRecord $r) => $r->genexpert_date,
            'type_of_specimen_geneXpert' => fn(PreTbRecord $r) => $r->type_of_specimen_geneXpert,
            'genexpert_res' => fn(PreTbRecord $r) => $r->genexpert_res,
            'truenat' => fn(PreTbRecord $r) => $r->truenat,
            'truenat_date' => fn(PreTbRecord $r) => $r->truenat_date,
            'type_of_specimen_truenat' => fn(PreTbRecord $r) => $r->type_of_specimen_truenat,
            'truenat_res' => fn(PreTbRecord $r) => $r->truenat_res,
            'hiv_det' => fn(PreTbRecord $r) => $r->hiv_det,
            'hiv_det_date' => fn(PreTbRecord $r) => $r->hiv_det_date,
            'hiv_det_res' => fn(PreTbRecord $r) => $r->hiv_det_res,
            'crp' => fn(PreTbRecord $r) => $r->crp,
            'crp_date' => fn(PreTbRecord $r) => $r->crp_date,
            'crp_result' => fn(PreTbRecord $r) => $r->crp_result,
            'antibiotics' => fn(PreTbRecord $r) => $r->antibiotics,
            'antibiotic_date' => fn(PreTbRecord $r) => $r->antibiotic_date,
            'drug' => fn(PreTbRecord $r) => $r->drug,
            'tb_treat' => fn(PreTbRecord $r) => $r->tb_treat,
            'tb_treat_date' => fn(PreTbRecord $r) => $r->tb_treat_date,
            'tb_treat_regimen' => fn(PreTbRecord $r) => $r->tb_treat_regimen,
            'manage_oth' => fn(PreTbRecord $r) => $r->manage_oth,
            'tb_diagnosis' => fn(PreTbRecord $r) => $r->tb_diagnosis,
            'treatment_status' => fn(PreTbRecord $r) => $r->treatment_status,
            'treatment_status_other' => fn(PreTbRecord $r) => $r->treatment_status_other,
            'xray_image_path' => fn(PreTbRecord $r) => $r->xray_image_path,
            'remark' => fn(PreTbRecord $r) => $r->remark,
            'md' => fn(PreTbRecord $r) => $r->md,
        ];
    }

    private function labelForExportField(string $field, $value)
    {
        if ($value === null || $value === '') {
            return $value;
        }

        $v = (string)$value;
        $yesNo = ['1' => 'Yes', '2' => 'No'];

        $maps = [
            'mode_of_entry' => [
                '1' => 'General',
                '2' => 'PLHIV',
                '3' => 'NCD',
                '4' => 'Remote (ICMV)',
                '5' => 'Remote (TB mobile)',
                '6' => 'Contact tracing',
                '7' => 'Diabetes',
            ],
            'alcohol' => ['1' => 'Current', '2' => 'Ex', '3' => 'No', '4' => 'Unknown'],
            'smoking' => ['1' => 'Current', '2' => 'Ex', '3' => 'No', '4' => 'Unknown'],
            'PWID' => ['1' => 'Current', '2' => 'Ex', '3' => 'No', '4' => 'Unknown'],
            'PWUD' => ['1' => 'Current', '2' => 'Ex', '3' => 'No', '4' => 'Unknown'],
            'DM' => ['1' => 'Yes', '2' => 'No', '3' => 'Unknown'],
            'dm_tx_status' => ['1' => 'On Tx', '2' => 'Not Tx'],
            'hiv_status' => ['1' => 'Yes', '2' => 'No', '3' => 'Unknown'],
            'hiv_tx' => ['1' => 'On Tx', '2' => 'Not Tx'],
            'his_tb_self' => ['1' => 'Yes', '2' => 'No', '3' => 'Unknown'],
            'his_tb_family' => ['1' => 'Yes', '2' => 'No', '3' => 'Unknown'],
            'malnutrition' => $yesNo,
            'fever' => $yesNo,
            'cough' => $yesNo,
            'hemoptysis' => $yesNo,
            'weight_loss' => $yesNo,
            'loss_of_appetite' => $yesNo,
            'chest_pain' => $yesNo,
            'night_sweats' => $yesNo,
            'neck_glands' => $yesNo,
            'fatigue' => $yesNo,
            'chest_x-ray' => $yesNo,
            'chest_x-ray_fac' => ['1' => 'MAM Xray', '2' => 'Outside facility'],
            'type_of_screening' => ['New' => 'New', 'Follow-up' => 'Follow up', 'Recheck' => 'Recheck'],
            'md_diagnosis' => ['1' => 'Normal', '2' => 'Active TB', '3' => 'Other', '4' => 'Old TB (scar)'],
            'radio_request' => $yesNo,
            'radiologist_result' => ['1' => 'Normal', '2' => 'Active TB', '3' => 'Old TB (scar)', '4' => 'Other'],
            'sputum_afb' => $yesNo,
            'sputum_afb_res' => ['1' => 'Positive', '2' => 'Negative'],
            'genexpert' => $yesNo,
            'genexpert_res' => ['1' => 'N', '2' => 'T', '3' => 'TT', '4' => 'RR', '5' => 'TI', '6' => 'Invalid'],
            'truenat' => $yesNo,
            'truenat_res' => ['1' => 'N', '2' => 'T', '3' => 'RR', '4' => 'TI', '5' => 'Invalid'],
            'hiv_det' => $yesNo,
            'hiv_det_res' => ['1' => 'Reactive', '2' => 'Non-Reactive'],
            'crp' => $yesNo,
            'antibiotics' => $yesNo,
            'tb_treat' => $yesNo,
            'tb_treat_regimen' => [
                '1' => 'Initial Regimen',
                '2' => 'Retreatment Regimen',
                '3' => 'Childhood Regimen',
                '4' => 'MDR Regimen',
            ],
        ];

        if (isset($maps[$field][$v])) {
            return $maps[$field][$v];
        }

        if ($field === 'mode_of_entry' && isset($maps[$field])) {
            $labels = [];
            foreach (array_filter(array_map('trim', explode(',', $v))) as $modeValue) {
                $labels[] = $maps[$field][$modeValue] ?? $modeValue;
            }
            return empty($labels) ? $value : implode(', ', $labels);
        }

        return $value;
    }

    private function calculateBmi(?float $weight, ?float $height): ?float
    {
        if (!$weight || !$height || $height <= 0) {
            return null;
        }

        return round($weight / ($height * $height), 2);
    }

    private function storeXrayImage(Request $request, ?string $existingPath = null): ?string
    {
        if (!Schema::hasColumn('pre_tb_records', 'xray_image_path')) {
            return $existingPath;
        }

        if (!$request->hasFile('xray_image')) {
            return $existingPath;
        }

        $file = $request->file('xray_image');
        if (!$file || !$file->isValid()) {
            return $existingPath;
        }

        $newPath = $file->store('pretb/xray', 'public');

        if ($existingPath && $existingPath !== $newPath && Storage::disk('public')->exists($existingPath)) {
            Storage::disk('public')->delete($existingPath);
        }

        return $newPath;
    }

    private function buildImageDataUri(?string $storagePath): ?string
    {
        if (!$storagePath) {
            return null;
        }

        try {
            if (!Storage::disk('public')->exists($storagePath)) {
                return null;
            }

            $absolutePath = Storage::disk('public')->path($storagePath);
            $raw = @file_get_contents($absolutePath);
            if ($raw === false) {
                return null;
            }

            $mime = @mime_content_type($absolutePath) ?: 'image/jpeg';
            return 'data:' . $mime . ';base64,' . base64_encode($raw);
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function formatDateForReport($value): string
    {
        if (!$value) {
            return '-';
        }

        try {
            return Carbon::parse($value)->format('d-m-Y');
        } catch (\Throwable $e) {
            return '-';
        }
    }

    private function formatDateForFormFromRaw(PreTbRecord $record, string $column): ?string
    {
        $raw = $record->getRawOriginal($column);
        if ($raw === null || $raw === '') {
            return null;
        }

        try {
            if (preg_match('/^\d{4}-\d{2}-\d{2}$/', (string)$raw)) {
                return Carbon::createFromFormat('Y-m-d', (string)$raw)->format('d-m-Y');
            }
            if (preg_match('/^\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2}$/', (string)$raw)) {
                return Carbon::createFromFormat('Y-m-d H:i:s', (string)$raw)->format('d-m-Y');
            }
            return Carbon::parse((string)$raw)->format('d-m-Y');
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function parseCommentExtras(?string $comment): array
    {
        [$mdDiagnosisOther, $withoutMdOther] = $this->extractCommentSegment($comment, 'MD other:');
        [$radiologistOther, $cleanComment] = $this->extractCommentSegment($withoutMdOther, 'Radiologist other:');

        return [
            'md_diagnosis_oth' => $mdDiagnosisOther,
            'radiologist_result_oth' => $radiologistOther,
            'comment' => $cleanComment,
        ];
    }

    private function extractCommentSegment(?string $comment, string $prefix): array
    {
        if (!$comment) {
            return [null, null];
        }

        $source = trim((string) $comment);
        if ($source === '') {
            return [null, null];
        }

        // Extract prefixed segment up to next "|" marker (if any), then remove it.
        $pattern = '/' . preg_quote($prefix, '/') . '\\s*(.*?)(?:\\s*\\|\\s*|$)/i';
        if (!preg_match($pattern, $source, $m)) {
            return [null, $source];
        }

        $value = trim((string) ($m[1] ?? ''));
        $clean = trim((string) preg_replace($pattern, '', $source));
        $clean = trim((string) preg_replace('/\\s*\\|\\s*/', ' | ', $clean), " |\t\n\r\0\x0B");

        return [
            $value !== '' ? $value : null,
            $clean !== '' ? $clean : null,
        ];
    }

    private function normalizeDate(?string $raw): ?string
    {
        if (!$raw) {
            return null;
        }

        // Excel serial number
        if (is_numeric($raw)) {
            try {
                return Carbon::instance(ExcelDate::excelToDateTimeObject($raw))->format('Y-m-d');
            } catch (\Exception $e) {
                // fall through
            }
        }

        $candidates = [
            'd-m-y',
            'd-m-Y',
            'Y-m-d',
            'd/m/Y',
            'd/m/y',
            'd-M-y',
            'd-M-Y',
            'd M Y',
        ];

        foreach ($candidates as $format) {
            try {
                return Carbon::createFromFormat($format, $raw)->format('Y-m-d');
            } catch (\Exception $e) {
                continue;
            }
        }

        try {
            return Carbon::parse($raw)->format('Y-m-d');
        } catch (\Exception $e) {
            return null;
        }
    }

    private function validateNotFuture(array $dates, array $allowFuture = []): array
    {
        $errors = [];
        $tz = config('app.timezone') ?: 'Asia/Yangon';
        $today = Carbon::now($tz)->startOfDay();
        $labels = [
            'date_of_screening' => 'Date of screening',
            'dofnv' => 'Date of next visit',
            'chest_xray_date' => 'CXR date',
            'genexpert_date' => 'GeneXpert date',
            'truenat_date' => 'Truenat date',
            'hiv_det_date' => 'HIV DET date',
            'crp_date' => 'CRP date',
            'radio_request_date' => 'Radiology request date',
            'sputum_afb_date' => 'AFB date',
            'antibiotic_date' => 'Antibiotic start date',
            'tb_treat_date' => 'TB treatment start date',
        ];

        foreach ($dates as $field => $value) {
            if (!$value) {
                continue;
            }
            try {
                $dt = Carbon::createFromFormat('Y-m-d', $value, $tz)->startOfDay();
                if (!in_array($field, $allowFuture, true) && $dt->gt($today)) {
                    $label = $labels[$field] ?? $field;
                    $errors[$field] = "{$label} cannot be in the future.";
                }
            } catch (\Exception $e) {
                $label = $labels[$field] ?? $field;
                $errors[$field] = "{$label} has invalid date format.";
            }
        }

        return $errors;
    }

    private function normalizeAnthro($height, $weight): array
    {
        $h = is_numeric($height) ? (float)$height : null;
        $w = is_numeric($weight) ? (float)$weight : null;

        if ($h && $h > 3) {
            $h = round($h / 100, 3); // convert cm to m if clearly in cm
        }

        return [$h, $w];
    }

    private function formatHeightForForm($height): ?float
    {
        if ($height === null || $height === '') {
            return null;
        }
        if (!is_numeric($height)) {
            return null;
        }

        $h = (float)$height;
        if ($h <= 0) {
            return null;
        }

        // DB stores meters; form/export expect cm. If already cm, keep.
        if ($h <= 3) {
            return round($h * 100, 1);
        }

        return round($h, 1);
    }

    private function normalizeYesNoToOneTwo($value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }

        $v = trim(mb_strtolower((string)$value));
        if ($v === '1' || $v === 'yes' || $v === 'y' || $v === 'true') {
            return 1;
        }
        if ($v === '2' || $v === 'no' || $v === 'n' || $v === 'false' || $v === '0') {
            return 2;
        }

        if (is_numeric($value)) {
            $num = (int)$value;
            if ($num === 0) {
                return 2;
            }
            return $num > 0 ? 1 : 2;
        }

        return null;
    }

    private function normalizeHeader(string $header): string
    {
        $h = trim(mb_strtolower($header));
        $h = str_replace([' ', '/', '\\', '.', '__'], '_', $h);
        return trim($h, '_');
    }

    private function importMap(): array
    {
        return [
            'clinic' => null,
            'cid' => 'cid',
            'name' => 'name',
            'age' => 'age',
            'sex' => 'sex',
            'typ_of_scr' => 'type_of_screening',
            'height' => 'height',
            'weight' => 'weight',
            'bmi' => 'bmi',
            'mode_of_entry' => 'mode_of_entry',
            'dofscrrening' => 'date_of_screening',
            'dofnv' => 'dofnv',
            'phone' => 'phone',
            'phone_no' => 'phone',
            'main_presenting_complaint' => 'main_presenting_complaint',
            'alcohol' => 'alcohol',
            'smoking' => 'smoking',
            'malnutrition' => 'malnutrition',
            'pwid' => 'PWID',
            'pwud' => 'PWUD',
            'dm' => 'DM',
            'dm_tx_status' => 'dm_tx_status',
            'hiv_status' => 'hiv_status',
            'hiv_tx' => 'hiv_tx',
            'his_tb_self' => 'his_tb_self',
            'his_tb_family' => 'his_tb_family',
            'chest_x-ray' => 'chest_xray',
            'chest_x_ray' => 'chest_xray',
            'chest_x-ray_date' => 'chest_xray_date',
            'chest_x_ray_date' => 'chest_xray_date',
            'chest_x-ray_fac' => 'chest_xray_fac',
            'md_diagnosis' => 'md_diagnosis',
            'md_diagnosis_oth' => null,
            'cad_score' => 'cad_score',
            'comment' => 'comment',
            'radio_request' => 'radio_request',
            'radio_request_date' => 'radio_request_date',
            'radiologist_result' => 'radiologist_result',
            'radiologist_result_oth' => null,
            'sputum_afb' => 'sputum_afb',
            'sputum_afb_date' => 'sputum_afb_date',
            'sputum_afb_res' => 'sputum_afb_res',
            'genexpert' => 'genexpert',
            'genexpert_date' => 'genexpert_date',
            'type_of_specimen_geneXpert' => 'type_of_specimen_geneXpert',
            'type_of_specimen_genexpert' => 'type_of_specimen_geneXpert',
            'genexpert_res' => 'genexpert_res',
            'truenat' => 'truenat',
            'truenat_date' => 'truenat_date',
            'type_of_specimen_truenat' => 'type_of_specimen_truenat',
            'truenat_res' => 'truenat_res',
            'hiv_det' => 'hiv_det',
            'hiv_det_date' => 'hiv_det_date',
            'hiv_det_res' => 'hiv_det_res',
            'crp' => 'crp',
            'crp_date' => 'crp_date',
            'crp_result' => 'crp_result',
            'antibiotics' => 'antibiotics',
            'antibiotic_date' => 'antibiotic_date',
            'drug' => 'drug',
            'tb_treat' => 'tb_treat',
            'tb_treat_date' => 'tb_treat_date',
            'tb_treat_regimen' => 'tb_treat_regimen',
            'manage_oth' => 'manage_oth',
            'tb_diagnosis' => 'tb_diagnosis',
            'treatment_status' => 'treatment_status',
            'treatment_status_other' => 'treatment_status_other',
            'xray_image_path' => 'xray_image_path',
            'xray_image' => 'xray_image_path',
            'remark' => 'remark',
            'remarks' => 'remark',
            'md' => 'md',
            // symptom day fields (new schema)
            'fever_days' => 'fever_days',
            'fever_day' => 'fever_days',
            'fever_duration' => 'fever_days',
            'cough_days' => 'cough_days',
            'cough_day' => 'cough_days',
            'cough_duration' => 'cough_days',
            'hemoptysis_days' => 'hemoptysis_days',
            'hemoptysis_day' => 'hemoptysis_days',
            'hemoptysis_duration' => 'hemoptysis_days',
            'weight_loss_days' => 'weight_loss_days',
            'weight_loss_day' => 'weight_loss_days',
            'weight_loss_duration' => 'weight_loss_days',
            'appetite_loss_days' => 'appetite_loss_days',
            'appetite_loss_day' => 'appetite_loss_days',
            'appetite_loss_duration' => 'appetite_loss_days',
            'loss_of_appetite_days' => 'appetite_loss_days',
            'chest_pain_days' => 'chest_pain_days',
            'chest_pain_day' => 'chest_pain_days',
            'chest_pain_duration' => 'chest_pain_days',
            'night_sweats_days' => 'night_sweats_days',
            'night_sweats_day' => 'night_sweats_days',
            'night_sweats_duration' => 'night_sweats_days',
            'neck_glands_days' => 'neck_glands_days',
            'neck_glands_day' => 'neck_glands_days',
            'neck_glands_duration' => 'neck_glands_days',
            'fatigue_days' => 'fatigue_days',
            'fatigue_day' => 'fatigue_days',
            'fatigue_duration' => 'fatigue_days',
            // symptom presence flags (new schema)
            'fever_present' => 'fever_present',
            'cough_present' => 'cough_present',
            'hemoptysis_present' => 'hemoptysis_present',
            'weight_loss_present' => 'weight_loss_present',
            'appetite_loss_present' => 'appetite_loss_present',
            'chest_pain_present' => 'chest_pain_present',
            'night_sweats_present' => 'night_sweats_present',
            'neck_glands_present' => 'neck_glands_present',
            'fatigue_present' => 'fatigue_present',
            // template headers (Yes/No + duration)
            'tb_symptoms' => 'tb_symptoms',
            'fever' => 'fever_present',
            'fever_dur' => 'fever_days',
            'cough' => 'cough_present',
            'cough_dur' => 'cough_days',
            'hemoptysis' => 'hemoptysis_present',
            'hemoptysis_dur' => 'hemoptysis_days',
            'weight_loss' => 'weight_loss_present',
            'weight_loss_dur' => 'weight_loss_days',
            'loss_of_appetite' => 'appetite_loss_present',
            'loss_of_appetite_dur' => 'appetite_loss_days',
            'chest_pain' => 'chest_pain_present',
            'chest_pain_dur' => 'chest_pain_days',
            'night_sweats' => 'night_sweats_present',
            'night_sweats_dur' => 'night_sweats_days',
            'neck_glands' => 'neck_glands_present',
            'neck_glands_dur' => 'neck_glands_days',
            'fatigue' => 'fatigue_present',
            'fatigue_dur' => 'fatigue_days',
        ];
    }

    private function getRawValue(array $header, array $row, string $key)
    {
        foreach ($header as $i => $col) {
            if ($col === $key) {
                return $row[$i] ?? null;
            }
        }
        return null;
    }
}