<?php

namespace App\Http\Controllers\Art;


use App\Http\Controllers;
use App\Http\Controllers\Controller;
use App\Exports\Export_age;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Support\Facades\Validator;

use Illuminate\Validation\ValidationException;
use Illuminate\Validation\Rule;
use Carbon\Carbon;

use App\Models\Art\ArtFollowup;
use App\Models\Art\PtConfig;
use App\Exports\Art\ArtFollowupExport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Arr;

use App\Models\Patients;
use App\Models\Lab;
use App\Models\LabHbcTest;
use App\Models\Urine;
use App\Models\Art\Applog;

use Illuminate\Support\Facades\Log;

class ServicesController extends Controller
{
    public function index(Request $request)
    {
        $pid = $request->get('pid');
        $configId = $request->get('config_id');
        $data = null;
        $lastVisit = null;
        $latestViralLoadVisit = null;
        $visits = collect();
        $config = null;

        if ($configId) {
            $config = PtConfig::find($configId);
        }

        if (!$config && $pid) {
            $config = PtConfig::where('Pid', $pid)->first();
        }

        if ($config) {
            $configId = $config->id;
            $pid = $config->Pid ?? $pid;
            $configPayload = $this->decryptConfigRow($config->toArray());
            $configPayload['DB_Location'] = 'pt_config';
            $data = $this->normalizeDemographicPayload($configPayload);
        }

        $effectivePid = $pid;
        $allowExternalSearch = !$request->boolean('force_offline');

        if (!$data && $pid && $allowExternalSearch) {
            $fallback = $this->searchID($pid, 0, 'pid');
            if ($fallback) {
                $payload = $this->prepareFallbackPatientData($fallback, [
                    'identifier' => $pid,
                    'identifier_type' => 'pid',
                ]);
                $data = $this->normalizeDemographicPayload($payload);
                $effectivePid = $data['Pid'] ?? $pid;
            }
        }

        $followupQuery = null;
        $targetNapId = $config?->NAP_ID ?? $request->input('nap_id');

        if ($targetNapId || $effectivePid) {
            $followupQuery = ArtFollowup::query()
                ->where(function ($query) use ($targetNapId, $effectivePid) {
                    if ($targetNapId) {
                        $query->where('nap_id', $targetNapId)
                            ->orWhere('NAP_ID', $targetNapId);
                    }
                    if ($effectivePid) {
                        $query->orWhere('pid', $effectivePid);
                    }
                });
        }

        if ($followupQuery) {
            $lastVisit = (clone $followupQuery)
                ->orderByDesc('visit_date')
                ->first();
            $latestViralLoadVisit = $this->findLatestPositiveViralLoadVisit(clone $followupQuery);
            $visits = $followupQuery
                ->orderByDesc('visit_date')
                ->paginate(10)
                ->withQueryString();
            // If we landed here via NAP/fuchia search and pid is still empty, hydrate it from follow-ups
            if (!$pid && $lastVisit?->pid) {
                $pid = $lastVisit->pid;
            }
        }

        if ($lastVisit) {
            $lastClinic = $lastVisit->clinic_id ?? $lastVisit->Clinic_ID ?? null;
            $lastVisitGender = $this->formatGenderValue($lastVisit->sex);
            if (is_array($data)) {
                if (!array_key_exists('Agey', $data) && $lastVisit->age !== null) {
                    $data['Agey'] = $lastVisit->age;
                }
                if (!array_key_exists('Clinic_ID', $data) && $lastClinic) {
                    $data['Clinic_ID'] = $lastClinic;
                }
                if (empty($data['Gender']) && $lastVisitGender) {
                    $data['Gender'] = $lastVisitGender;
                }
            } elseif ($lastVisit->age !== null || $lastClinic || $lastVisit->nap_id || $lastVisit->pid) {
                $data = [
                    'Agey' => $lastVisit->age,
                    'Gender' => $lastVisitGender,
                    'Clinic_ID' => $lastClinic,
                    'NAP_ID' => $lastVisit->nap_id ?? $lastVisit->NAP_ID ?? null,
                    'Pid' => $lastVisit->pid ?? null,
                ];
            }
        }

        if (is_array($data) && empty($data['FuchiaID'])) {
            $visitFuchia = $lastVisit?->fuchia_id ?? $lastVisit?->FuchiaID ?? null;
            if ($visitFuchia) {
                $data['FuchiaID'] = $visitFuchia;
            }
        }

        $napId = is_array($data) ? ($data['NAP_ID'] ?? null) : null;
        if (!$napId) {
            $napId = $request->input('nap_id');
        }
        if (is_array($data) && empty($data['FuchiaID'])) {
            $lookupNapId = $napId ?? ($targetNapId ?? null);
            $lookupPid = $effectivePid ?? ($data['Pid'] ?? null);
            if ($lookupNapId || $lookupPid) {
                $configLookup = PtConfig::query()
                    ->when($lookupNapId, fn ($query) => $query->where('NAP_ID', $lookupNapId))
                    ->when(!$lookupNapId && $lookupPid, fn ($query) => $query->where('Pid', $lookupPid))
                    ->first();
                if ($configLookup?->{'FuchiaID'}) {
                    $data['FuchiaID'] = $configLookup->{'FuchiaID'};
                }
            }
        }
        $labSummary = $this->buildLabSummary($pid, $napId);

        $hasPtConfigFlag = (bool) $config || (bool) $lastVisit;

        $clinicOptions = $this->getClinicOptionsForUser();
        $selectedClinicId = null;
        if (is_array($data) && array_key_exists('Clinic_ID', $data)) {
            $selectedClinicId = $data['Clinic_ID'];
        }
        if (!$selectedClinicId && $config?->{'Clinic_ID'}) {
            $selectedClinicId = $config->{'Clinic_ID'};
        }
        if (!$selectedClinicId && $lastVisit?->clinic_id) {
            $selectedClinicId = $lastVisit->clinic_id;
        }
        if (!$selectedClinicId && count($clinicOptions) === 1) {
            $selectedClinicId = (string) array_key_first($clinicOptions);
        }

        $payload = [
            'pid'       => $pid,
            'data'      => $data,
            'lastVisit' => $lastVisit,
            'latestViralLoadVisit' => $latestViralLoadVisit,
            'visits'    => $visits,
            'configId'  => $configId,
            'labSummary'=> $labSummary,
            'hasPtConfig' => $hasPtConfigFlag,
            'napId'     => $targetNapId,
            'clinicOptions' => $clinicOptions,
            'selectedClinicId' => $selectedClinicId,
        ];

        if ($request->expectsJson() || $request->is('api/*')) {
            return response()->json($payload);
        }

        return view('art.Services', $payload);


    }

    public function searchPatient(Request $request)
    {
        $isApiRequest = $request->expectsJson() || $request->is('api/*');
        $baseRules = [
            'identifier_type' => ['required', 'in:id,pid,nap_id,fuchia_id'],
            'identifier'      => ['required', 'string'],
        ];

        $type = $request->input('identifier_type');
        $identifierRules = ['required', 'string'];
        if ($type === 'pid') {
            $identifierRules[] = 'regex:/^[0-9]{10,12}$/';
        } elseif ($type === 'nap_id') {
            $identifierRules[] = 'regex:/^[A-Za-z0-9\\/]{1,15}$/';
        } elseif ($type === 'fuchia_id') {
            $identifierRules[] = 'regex:/^[A-Za-z0-9]{8,9}$/';
        } elseif ($type === 'id') {
            $identifierRules[] = 'regex:/^[0-9]+$/';
        }

        $rules = $baseRules;
        $rules['identifier'] = $identifierRules;

        $messages = [
            'identifier.regex' => match ($type) {
                'pid'      => 'General ID must be 10–12 digits.',
                'nap_id'   => 'NAP ID must be 1–15 characters (letters/numbers/"/").',
                'fuchia_id'=> 'Fuchia ID must be 8–9 letters or numbers.',
                'id'       => 'Inner ID must be numeric.',
                default    => 'Invalid identifier format.',
            },
        ];

        if ($isApiRequest) {
            $request->validate($rules, $messages);
        } else {
            try {
                $request->validate($rules, $messages);
            } catch (ValidationException $e) {
                return redirect()
                    ->route('services.index')
                    ->withInput($request->only('identifier', 'identifier_type'))
                    ->withErrors($e->errors())
                    ->with('suppress_errors', true);
            }
        }

        $identifier = trim($request->input('identifier'));
        $type = $request->input('identifier_type');
        $clinicId = $this->getArtClinicScope();
        $allowExternalSearch = !$request->boolean('force_offline');

        Log::info("Search requested for {$type}: " . $identifier);

        $inputPersist = $request->only('identifier', 'identifier_type');
        if ($request->boolean('force_offline')) {
            $inputPersist['force_offline'] = '1';
        }
        if ($type === 'nap_id') {
            $inputPersist['nap_id'] = $identifier;
        }
        if ($type === 'fuchia_id') {
            $inputPersist['fuchia_id'] = $identifier;
        }
        if ($type === 'pid') {
            $inputPersist['pid'] = $identifier;
        }

        if (in_array($type, ['pid', 'fuchia_id'], true)) {
            $duplicates = $this->findDuplicatePtConfigs($type, $identifier, $clinicId);
            if ($duplicates->count() > 1) {
                $matches = $duplicates
                    ->map(fn (PtConfig $config) => $this->formatDuplicateMatch($config))
                    ->values()
                    ->all();

                if ($isApiRequest) {
                    return response()->json([
                        'found' => false,
                        'duplicate' => true,
                        'identifier_type' => $type,
                        'identifier' => $identifier,
                        'matches' => $matches,
                        'message' => 'Multiple patient records found. Please resolve using Inner ID or NAP ID.',
                    ], 409);
                }

                return redirect()
                    ->route('services.index')
                    ->withInput($inputPersist)
                    ->with('duplicate_matches', $matches)
                    ->with('duplicate_identifier', [
                        'type' => $type,
                        'value' => $identifier,
                    ])
                    ->with('info', 'Multiple patient records found. Please choose the correct row below.');
            }
        }

        [$pid, $configId, $napId] = $this->resolveIdentifiers($identifier, $type, $clinicId);

        if (!$pid && !$napId && $type === 'fuchia_id' && $allowExternalSearch) {
            $patient = $this->searchID($identifier, 0, 'fuchia_id');
            if ($patient) {
                $pid = $patient['Pid'] ?? null;
            }
        }

        if (!$pid && !$napId) {
            $fallbackFollowup = null;
            if ($type === 'pid') {
                $query = ArtFollowup::query();
                $this->applyClinicFilter($query, $clinicId);
                $fallbackFollowup = $query->where('pid', $identifier)->latest('visit_date')->first();
            } elseif ($type === 'nap_id') {
                $query = ArtFollowup::query();
                $this->applyClinicFilter($query, $clinicId);
                $fallbackFollowup = $query->where(function ($q) use ($identifier) {
                    $q->where('nap_id', $identifier)
                        ->orWhere('NAP_ID', $identifier);
                })->latest('visit_date')->first();
            } elseif ($type === 'fuchia_id') {
                $query = ArtFollowup::query();
                $this->applyClinicFilter($query, $clinicId);
                $fallbackFollowup = $query->where('FuchiaID', $identifier)->latest('visit_date')->first();
            }

            if ($fallbackFollowup) {
                $pid = $fallbackFollowup->pid ?: $pid;
                $napId = $fallbackFollowup->nap_id ?: $napId;
            }
        }

        $inputPersist['nap_id'] = $type === 'nap_id' ? $identifier : ($napId ?? null);
        $inputPersist['pid'] = $type === 'pid' ? $identifier : ($pid ?? null);
        if ($type === 'fuchia_id') {
            $inputPersist['fuchia_id'] = $identifier;
        } else {
            unset($inputPersist['fuchia_id']);
        }

        if (!$pid && !$configId) {
            if ($isApiRequest) {
                return response()->json([
                    'found' => false,
                    'identifier_type' => $type,
                    'identifier' => $identifier,
                    'message' => "No records found for {$type}: {$identifier}.",
                ], 404);
            }

            return redirect()
                ->route('services.index')
                ->withInput($inputPersist)
                ->with('info_new_patient', "No records found for {$type}: {$identifier}. Please register this patient.");
        }

        if ($isApiRequest) {
            return response()->json([
                'found' => true,
                'pid' => $pid,
                'config_id' => $configId,
                'nap_id' => $napId,
                'identifier_type' => $type,
                'identifier' => $identifier,
                'services_url' => route('services.index', [
                    'pid' => $pid,
                    'config_id' => $configId,
                    'nap_id' => $napId,
                    'identifier_type' => $type,
                    'identifier' => $identifier,
                    'force_offline' => $request->boolean('force_offline') ? '1' : null,
                ]),
            ]);
        }

        return redirect()
            ->route('services.index', [
                'pid' => $pid,
                'config_id' => $configId,
                'nap_id' => $napId,
                'identifier_type' => $type,
                'identifier' => $identifier,
                'force_offline' => $request->boolean('force_offline') ? '1' : null,
            ])
            ->withInput($inputPersist);
    }

    public function store(Request $request)
    {
        try {
            $result = $this->persistArtForm($request);
        } catch (ValidationException $e) {
            throw $e;
        } catch (\Throwable $e) {
            return redirect()
                ->back()
                ->withInput()
                ->withErrors(['general' => 'Unable to save ART follow-up. Please try again.']);
        }

        $routeParams = $result['route_params'] ?? [];
        $response = redirect()->route('services.index', $routeParams);
        $flashKey = $result['flash_key'] ?? 'success';

        if (!empty($result['message'])) {
            $response = $response->with($flashKey, $result['message']);
        }

        foreach (['highlight_pid', 'highlight_name', 'highlight_inner_id'] as $key) {
            if (array_key_exists($key, $result)) {
                $response = $response->with($key, $result[$key]);
            }
        }

        return $response;
    }

    public function updateIdentifiers(Request $request)
    {
        $ptConfigModel = new PtConfig();
        $payload = $request->validate([
            'inner_id'  => [
                'required',
                'integer',
                Rule::exists($ptConfigModel->getConnectionName() . '.' . $ptConfigModel->getTable(), 'id'),
            ],
            'new_pid'       => ['nullable', 'regex:/^[0-9]{6,15}$/'],
            'new_nap_id'    => ['nullable', 'regex:/^[A-Za-z0-9\\/]{1,15}$/'],
            'new_fuchia_id' => ['nullable', 'string', 'max:191'],
        ], [], [
            'inner_id' => 'Inner ID',
        ]);

        $config = PtConfig::find($payload['inner_id']);
        if (!$config) {
            return redirect()
                ->back()
                ->withInput()
                ->withErrors(['inner_id' => 'Inner ID not found.']);
        }

        $newPid = $payload['new_pid'] ?? null;
        $newNap = $payload['new_nap_id'] ?? null;
        $newFuchia = $payload['new_fuchia_id'] ?? null;

        if (is_string($newPid)) {
            $newPid = trim($newPid);
        }
        if (is_string($newNap)) {
            $newNap = trim($newNap);
        }
        if (is_string($newFuchia)) {
            $newFuchia = trim($newFuchia);
        }

        $currentPid = $config->{'Pid'};
        $currentNap = $config->{'NAP_ID'};
        $currentFuchia = $config->{'FuchiaID'};

        if (!$newPid && !$newNap && !$newFuchia) {
            throw ValidationException::withMessages([
                'new_pid' => 'Provide at least one identifier (PID, NAP ID, or Fuchia ID) to update.',
            ]);
        }

        $duplicateRules = [
            'NAP_ID' => $newNap,
        ];

        foreach ($duplicateRules as $column => $value) {
            if (!$value) {
                continue;
            }
            $exists = PtConfig::where($column, $value)
                ->where('id', '!=', $config->id)
                ->exists();
            if ($exists) {
                $label = match ($column) {
                    'Pid' => 'PID',
                    'NAP_ID' => 'NAP ID',
                    'FuchiaID' => 'Fuchia ID',
                    default => ucfirst(strtolower($column)),
                };
                $errorField = match ($column) {
                    'Pid' => 'new_pid',
                    'NAP_ID' => 'new_nap_id',
                    'FuchiaID' => 'new_fuchia_id',
                    default => 'new_pid',
                };
                throw ValidationException::withMessages([
                    $errorField => "{$label} already exists on another record.",
                ]);
            }
        }

        $conn = DB::connection((new PtConfig())->getConnectionName());
        $conn->beginTransaction();

        try {
            $originalConfig = $config->toArray();

            if ($newPid !== null) {
                $config->{'Pid'} = $newPid;
            }
            if ($newNap !== null) {
                $config->{'NAP_ID'} = $newNap;
            }
            if ($newFuchia !== null) {
                $config->{'FuchiaID'} = $newFuchia;
            }

            $config->save();

            $followupUpdateCount = 0;
            $updatePayload = [];
            if ($newPid !== null) {
                $updatePayload['pid'] = is_numeric($newPid) ? (int) $newPid : $newPid;
            }
            if ($newNap !== null) {
                $updatePayload['NAP_ID'] = $newNap;
            }
            if ($newFuchia !== null) {
                $updatePayload['FuchiaID'] = $newFuchia;
            }

            if (!empty($updatePayload) && ($currentPid || $currentNap || $currentFuchia)) {
                $followupUpdateCount = ArtFollowup::where(function ($query) use ($currentPid, $currentNap, $currentFuchia) {
                    if ($currentPid) {
                        $query->orWhere('pid', $currentPid);
                    }
                    if ($currentNap) {
                        $query->orWhere('NAP_ID', $currentNap);
                    }
                    if ($currentFuchia) {
                        $query->orWhere('FuchiaID', $currentFuchia);
                    }
                })->update($updatePayload);
            }

            $this->logAuditTrail(
                'pt_configs',
                $config->{'Pid'} ?? ($config->{'NAP_ID'} ?? null),
                $originalConfig,
                $config->toArray()
            );

            if (!empty($updatePayload)) {
                $this->logAuditTrail(
                    'art_followups',
                    $config->{'Pid'} ?? ($config->{'NAP_ID'} ?? null),
                    [
                        'rows_affected' => $followupUpdateCount,
                        'pid' => $currentPid,
                        'nap_id' => $currentNap,
                        'fuchia_id' => $currentFuchia,
                    ],
                    [
                        'rows_affected' => $followupUpdateCount,
                        'pid' => $newPid ?? $currentPid,
                        'nap_id' => $newNap ?? $currentNap,
                        'fuchia_id' => $newFuchia ?? $currentFuchia,
                    ]
                );
            }

            $conn->commit();

            $messageParts = ['Identifiers updated.'];
            if ($followupUpdateCount) {
                $messageParts[] = "{$followupUpdateCount} follow-up rows updated.";
            }

            return redirect()
                ->route('services.index', [
                    'pid' => $config->{'Pid'} ?? null,
                    'config_id' => $config->id,
                    'nap_id' => $config->{'NAP_ID'} ?? null,
                ])
                ->with('success', implode(' ', $messageParts))
                ->with('highlight_inner_id', $config->id);
        } catch (ValidationException $e) {
            $conn->rollBack();
            throw $e;
        } catch (\Throwable $e) {
            $conn->rollBack();
            Log::error('Identifier update failed', ['error' => $e->getMessage()]);
            return redirect()
                ->back()
                ->withInput()
                ->withErrors(['general' => 'Unable to update identifiers. Please try again.']);
        }
    }

    protected function persistArtForm(Request $request): array
    {
        $this->normalizeIdentifierInputs($request);
        $pidInput = $request->input('pid');
        $currentConfig = $this->findPtConfig(
            $request->input('pid'),
            $request->input('nap_id'),
            $request->input('fuchia_id'),
            $request->input('config_id')
        );
        $hasExistingConfig = (bool) $currentConfig || $request->filled('config_id');
        $hasPid = filled($pidInput) || $hasExistingConfig;
        $formAction = $request->input('form_action', $hasPid ? 'update' : 'create');
        $actions = $this->resolveSectionActions($request, $hasPid);
        $hasConfig = (bool) $currentConfig;

        Log::info('ART follow-up request received', [
            'pid'            => $request->input('pid'),
            'form_action'    => $formAction,
            'patient_action' => $actions['patient'],
            'visit_action'   => $actions['visit'],
            'services_action'=> $actions['services'],
            'vl_action'      => $actions['vl'],
        ]);

        if (!$hasConfig && $actions['patient'] === 'skip' && !$this->shouldHandleFollowup($actions)) {
            throw ValidationException::withMessages([
                'patient_name' => 'Please update patient details before saving visits.',
            ]);
        }

        $this->validateStoreRequest($request, $actions, $formAction, $hasPid);

        DB::beginTransaction();

        try {
            if ($actions['patient'] === 'delete') {
                $pidForDeletion = $request->input('pid');
                $this->deletePatientCascade($pidForDeletion);
                DB::commit();

                return [
                    'route_params' => [],
                    'message' => "Patient {$pidForDeletion} deleted successfully.",
                    'highlight_pid' => null,
                    'highlight_name' => null,
                    'highlight_inner_id' => null,
                    'visit_id' => null,
                ];
            }

            $pid = $this->persistPatientData(
                $request,
                $request->input('pid'),
                $actions['patient']
            );

            $configId = $currentConfig?->id ?? $request->input('config_id');
            $config = null;
            $originalConfig = $currentConfig ? $currentConfig->toArray() : null;

            if (in_array($actions['patient'], ['create', 'update'], true)) {
                $config = $this->syncPtConfig(
                    $request,
                    $pid,
                    $configId
                );
                $configId = $config->id ?? $configId;
                if ($config) {
                    $this->logAuditTrail(
                        'pt_configs',
                        $config->Pid ?? ($config->{'NAP_ID'} ?? $pid),
                        $originalConfig ?? [],
                        $config->toArray()
                    );
                }
            } else {
                $config = $currentConfig;
            }

            $shouldHandleFollowup = $this->shouldHandleFollowup($actions);
            $fallbackNapId = $config->{'NAP_ID'} ?? $request->input('nap_id');
            $effectivePid = $pid
                ?: ($config->Pid ?? null)
                ?: ($fallbackNapId ?: null);

            if (!$effectivePid && !$fallbackNapId && $shouldHandleFollowup) {
                throw ValidationException::withMessages([
                    'nap_id' => 'Unable to determine patient reference. Please provide NAP ID or ensure config exists.',
                ]);
            }

            if ($effectivePid === null && $shouldHandleFollowup) {
                throw ValidationException::withMessages([
                    'pid' => 'Patient identifier missing; please provide PID or NAP ID.',
                ]);
            }

            $innerId = $config?->id ?? $configId;

            $scope = $request->input('action_scope');
            $visitNapId = $config?->{'NAP_ID'} ?? $request->input('nap_id');
            $visitDeletion = $actions['visit'] === 'delete'
                || ($formAction === 'delete' && $scope !== 'patient');

            if ($visitDeletion) {
                $this->deleteFollowup($effectivePid, $request->input('visit_id'), $visitNapId);
                DB::commit();

                return [
                    'route_params' => ['pid' => $pid, 'config_id' => $configId, 'nap_id' => $visitNapId],
                    'message' => 'Visit deleted successfully.',
                    'highlight_pid' => null,
                    'highlight_name' => null,
                    'highlight_inner_id' => $innerId,
                    'visit_id' => $request->input('visit_id'),
                ];
            }

            $visitNapId = $visitNapId ?? $request->input('nap_id');
            $visitClinicId = $this->resolveVisitClinicId($request->input('clinic_id'), $config);

            if ($shouldHandleFollowup) {
                if (!$visitNapId) {
                    throw ValidationException::withMessages([
                        'nap_id' => 'NAP ID is required to save visits.',
                    ]);
                }

            }

            if (!$shouldHandleFollowup) {
                DB::commit();

                $message = $actions['patient'] === 'create'
                    ? 'New patient created.'
                    : 'Patient information updated successfully.';

                $highlightPid = $pid;
                $highlightName = $highlightPid ? $request->input('patient_name') : null;

                return [
                    'route_params' => ['pid' => $pid, 'config_id' => $configId],
                    'message' => $message,
                    'highlight_pid' => $highlightPid,
                    'highlight_name' => $highlightName,
                    'highlight_inner_id' => $innerId,
                    'visit_id' => null,
                ];
            }

            $numericPid = is_numeric($effectivePid) ? (int) $effectivePid : null;
            $visitId = $request->input('visit_id')
                ?: $this->generateVisitId($numericPid ?: ($visitNapId ?: null));
            $payload = $this->composeFollowupPayload($request, $actions);
            $payload['clinic_id'] = $visitClinicId;
            $payload['nap_id'] = $visitNapId;
            $payload['pid'] = $numericPid;
            $payload['visit_id'] = $visitId;

            Log::debug('Saving ART follow-up row', [
                'pid'        => $effectivePid,
                'visit_id'   => $visitId,
                'clinic_id'  => $visitClinicId,
                'nap_id'     => $visitNapId,
                'form_scope' => $request->input('action_scope'),
                'payload'    => array_keys(array_filter($payload, fn ($value) => !is_null($value) && $value !== '')),
            ]);

            $existingVisit = null;
            if ($shouldHandleFollowup && $actions['visit'] !== 'delete') {
                $existingVisit = ArtFollowup::where('visit_id', $visitId)->first();
                $this->assertUniqueVisit(
                    $numericPid,
                    $visitNapId,
                    $payload['visit_date'] ?? null,
                    $actions['visit'] === 'update' ? $visitId : null
                );
            }

            $match = $numericPid !== null
                ? ['pid' => $numericPid, 'visit_id' => $visitId]
                : ['nap_id' => $visitNapId, 'visit_id' => $visitId];

            ArtFollowup::updateOrCreate($match, $payload);
            if ($shouldHandleFollowup && $actions['visit'] !== 'delete') {
                $savedVisit = ArtFollowup::where('visit_id', $visitId)->first();
                $this->logAuditTrail(
                    'art_followups',
                    $savedVisit?->pid ?: ($savedVisit?->nap_id ?? null),
                    $existingVisit?->toArray() ?? [],
                    $savedVisit?->toArray() ?? []
                );
            }

            Log::info('ART follow-up persisted', [
                'pid'         => $pid,
                'visit_id'    => $visitId,
                'form_action' => $formAction,
                'patient_action' => $actions['patient'],
            ]);

            DB::commit();

            $message = 'ART follow-up saved successfully.';
            $highlightPid = null;
            $highlightName = null;
            if ($formAction === 'update') {
                $message = 'ART follow-up updated successfully.';
            }
            if ($actions['patient'] === 'create' && $pid) {
                $message = "New patient created. PID: {$pid}.";
                $highlightPid = $pid;
                $highlightName = $request->input('patient_name');
            }

            return [
                'route_params' => ['pid' => $pid, 'config_id' => $configId, 'nap_id' => $visitNapId],
                'message' => $message,
                'highlight_pid' => $highlightPid,
                'highlight_name' => $highlightName,
                'highlight_inner_id' => $innerId,
                'visit_id' => $visitId,
            ];
        } catch (ValidationException $e) {
            DB::rollBack();
            Log::warning('ART follow-up validation failed', $e->errors());
            throw $e;
        } catch (\Throwable $e) {
            DB::rollBack();
            Log::error('ART follow-up save failed', ['error' => $e->getMessage()]);
            throw $e;
        }
    }

    protected function resolveSectionActions(Request $request, bool $hasPid): array
    {
        $default = $hasPid ? 'update' : 'create';
        $allowed = ['create', 'update', 'delete', 'skip'];

        $actions = [
            'patient'  => 'skip',
            'visit'    => $default,
            'services' => $default,
            'vl'       => $default,
        ];

        foreach ($actions as $section => $value) {
            $inputValue = $request->input("{$section}_action");
            if ($inputValue && in_array($inputValue, $allowed, true)) {
                $actions[$section] = $inputValue;
            }
        }

        $scope = $request->input('action_scope');

        if ($scope === 'patient') {
            $actions['patient'] = $request->input('form_action', $actions['patient']);
            $actions['visit'] = 'skip';
            $actions['services'] = 'skip';
            $actions['vl'] = 'skip';
        } elseif ($scope === 'visit') {
            $selected = $request->input('form_action', $default);
            $actions['visit'] = $selected;
            $actions['services'] = $selected;
            $actions['vl'] = $selected;
        }

        return $actions;
    }

    protected function shouldHandleFollowup(array $actions): bool
    {
        foreach (['visit', 'services', 'vl'] as $section) {
            if (($actions[$section] ?? 'skip') !== 'skip') {
                return true;
            }
        }

        return false;
    }

    protected function validateStoreRequest(Request $request, array $actions, string $formAction, bool $hasPid): void
    {
        $rules = [
            'form_action'     => ['required', 'in:create,update,delete'],
            'action_scope'    => ['nullable', 'in:patient,visit'],
            'patient_action'  => ['nullable', 'in:create,update,delete,skip'],
            'visit_action'    => ['nullable', 'in:create,update,delete,skip'],
            'services_action' => ['nullable', 'in:create,update,delete,skip'],
            'vl_action'       => ['nullable', 'in:create,update,delete,skip'],
        ];

        $requirePid = $formAction === 'delete' && $request->input('action_scope') === 'patient';

        $rules['pid'] = $requirePid
            ? ['required', 'numeric']
            : ['nullable', 'numeric'];
        $scope = $request->input('action_scope');

        $rules['patient_name'] = ['nullable', 'string', 'max:255'];
        $rules['nap_id'] = ['nullable', 'string', 'max:191'];
        $rules['fuchia_id'] = ['nullable', 'string', 'max:191'];

        $rules['patient_phone'] = ['nullable', 'string', 'max:50'];
        $rules['age'] = ['nullable', 'integer', 'min:0', 'max:120'];
        $rules['age_months'] = ['nullable', 'integer', 'min:0'];
        $rules['patient_dob'] = $this->dateRule();
        $rules['patient_gender'] = ['nullable', Rule::in(['M', 'F', 'O', 'Male', 'Female', 'Other'])];
        $rules['patient_address'] = ['nullable', 'string', 'max:255'];
        $rules['patient_township'] = ['nullable', 'string', 'max:150'];
        $rules['patient_main_risk'] = ['nullable', 'string', 'max:150'];
        $rules['patient_notes'] = ['nullable', 'string', 'max:500'];
        $rules['reg_date'] = $this->dateRule();

        $requiresPatientData = $actions['patient'] !== 'skip'
            || $request->input('action_scope') === 'patient';

        if ($requiresPatientData) {
            $rules['patient_name'] = ['nullable', 'string', 'max:255'];
            $rules['nap_id'] = ['required', 'string', 'max:191'];
            $rules['age'] = ['required', 'integer', 'min:0', 'max:120'];
            $rules['patient_gender'] = ['required', Rule::in(['M', 'F', 'O', 'Male', 'Female', 'Other'])];
            $rules['patient_township'] = ['nullable', 'string', 'max:150'];
            $rules['patient_main_risk'] = ['nullable', 'string', 'max:150'];
            $rules['patient_dob'] = $this->dateRule();
            $rules['reg_date'] = $this->dateRule();
        }

        $requiresVisitData = $this->shouldHandleFollowup($actions);

        $isVisitDeletion = $actions['visit'] === 'delete'
            || ($formAction === 'delete' && $scope !== 'patient');

        if ($isVisitDeletion) {
            $rules['visit_id'] = ['required', 'string'];
            if (!$requirePid) {
                $rules['nap_id'] = ['required', 'string', 'max:191'];
            }
        } elseif ($requiresVisitData) {
            $rules['visit_date'] = $this->dateRule(true);
            // Require a NAP ID for visit saves when PID is absent
            if (!$requirePid) {
                $rules['nap_id'] = ['required', 'string', 'max:191'];
            }
            $rules['age'] = ['required', 'integer', 'min:0', 'max:120'];
            $rules['patient_gender'] = ['required', Rule::in(['M', 'F', 'O', 'Male', 'Female', 'Other'])];
            $rules['sex'] = ['nullable', Rule::in(['M', 'F', 'O', 'Male', 'Female', 'Other'])];
            $rules['main_risk'] = ['nullable', 'string', 'max:150'];
            $rules['art_regime'] = ['nullable', 'string', 'max:150'];
            $rules['patient_status'] = ['nullable', 'string', 'max:100'];
            $rules['art_started_date'] = $this->dateRule();
            $rules['next_appointment_date'] = $this->dateRule(false, true);
        } else {
            $rules['next_appointment_date'] = $this->dateRule(false, true);
        }
        $rules['vl_test_date'] = $this->dateRule();
        $rules['vl_copies_ml'] = ['nullable', 'string', 'max:100'];
        $rules['sti_patient_type'] = ['nullable', Rule::in(['non-KP', 'MSM', 'TGW', 'FSW', 'PWID'])];
        $rules['sti_visit_type'] = ['nullable', Rule::in(['3months', '6months', '12months'])];
        $rules['sti_complaint'] = ['nullable', Rule::in(['Yes', 'No'])];
        $rules['sti_syphilis_rdt_result'] = ['nullable', Rule::in(['Positive', 'Negative'])];
        $rules['sti_syphilis_rpr_result'] = ['nullable', 'string', 'max:50'];
        $rules['sti_syphilis_rpr_qual'] = ['nullable', Rule::in(['Reactive', 'Non-Reactive'])];
        $rules['sti_presumptive_gc_ct_rx'] = ['nullable', Rule::in(['Yes', 'No'])];
        $rules['sti_syphilis_rx'] = ['nullable', Rule::in(['Yes', 'No'])];

        $clinicOptions = $this->getClinicOptionsForUser();
        $clinicIds = array_keys($clinicOptions);
        $clinicRules = ['nullable', 'integer'];
        if ($clinicIds) {
            $clinicRules[] = Rule::in($clinicIds);
        }
        $rules['clinic_id'] = $clinicRules;

        $request->validate($rules, [], [
            'pid'            => 'Patient ID',
            'visit_id'       => 'Visit ID',
            'visit_date'     => 'Visit Date',
            'age'            => 'Agey',
            'patient_name'   => 'Patient Name',
            'patient_gender' => 'Sex',
        ]);
    }

    protected function persistPatientData(Request $request, ?string $pid, string $action): ?string
    {
        if (in_array($action, ['skip', 'delete'], true)) {
            return $pid;
        }

        $resolvedPid = $pid ?: $request->input('pid');

        if ($resolvedPid && !$request->filled('pid')) {
            $request->merge(['pid' => $resolvedPid]);
        }

        return $resolvedPid ? (string) $resolvedPid : null;
    }

    protected function deletePatientCascade(?string $pid): void
    {
        if (!$pid) {
            throw ValidationException::withMessages([
                'pid' => 'Patient ID is required for deletion.',
            ]);
        }

        PtConfig::where('Pid', $pid)->delete();

        ArtFollowup::where('pid', $pid)->delete();
    }

    protected function deleteFollowup(?string $pid, ?string $visitId, ?string $napId = null): void
    {
        if (!$visitId) {
            throw ValidationException::withMessages([
                'visit_id' => 'Visit ID is required to delete records.',
            ]);
        }

        $query = ArtFollowup::where('visit_id', $visitId);
        if ($pid) {
            $query->where('pid', $pid);
        } elseif ($napId) {
            $query->where('nap_id', $napId);
        }

        $toDelete = $query->first();
        $deleted = $query->delete();

        if (!$deleted) {
            throw ValidationException::withMessages([
                'visit_id' => 'No visit found for the given ID.',
            ]);
        }

        if ($toDelete) {
            $this->logAuditTrail(
                'art_followups',
                $toDelete->pid ?: ($toDelete->nap_id ?? null),
                $toDelete->toArray(),
                []
            );
        }
    }

    protected function composeFollowupPayload(Request $request, array $actions): array
    {
        if ($actions['visit'] === 'skip' && $actions['services'] === 'skip' && $actions['vl'] === 'skip') {
            return [];
        }

        $payload = [];

        if ($actions['visit'] !== 'delete') {
            $payload = array_merge($payload, [
                'visit_date'        => $this->parseDateInput($request->input('visit_date')),
                'age'               => $request->input('age'),
                'sex'               => $request->input('sex') ?: $request->input('patient_gender'),
                'main_risk'         => $request->input('main_risk'),
                'art_started_date'  => $this->parseDateInput($request->input('art_started_date')),
                'art_regime'        => $request->input('art_regime'),
                'art_regime_changed'=> $request->boolean('art_regime_changed'),
                'patient_status'    => $request->input('patient_status'),
            ]);
        }

        if ($actions['services'] === 'delete') {
            $payload = array_merge($payload, $this->resetServicesPayload());
        } else {
            $payload = array_merge($payload, [
                'fp_service'           => $request->boolean('fp_service'),
                'fp_condom'            => $request->boolean('fp_condom'),
                'fp_oc_pills'          => $request->boolean('fp_oc_pills'),
                'fp_depo'              => $request->boolean('fp_depo'),
                'prevention_commodities_provided' => $request->boolean('prevention_commodities_provided'),
                'prevention_condom'    => $request->boolean('prevention_condom'),
                'prevention_ns'        => $request->boolean('prevention_ns'),
                'sti_patient_type'     => $request->input('sti_patient_type'),
                'sti_visit_type'       => $request->input('sti_visit_type'),
                'sti_complaint'        => $request->input('sti_complaint'),
                'sti_syphilis_rdt_result' => $request->input('sti_syphilis_rdt_result'),
                'sti_syphilis_rpr_result' => $request->input('sti_syphilis_rpr_result'),
                'sti_syphilis_rpr_qual' => $request->input('sti_syphilis_rpr_qual'),
                'sti_presumptive_gc_ct_rx' => $request->input('sti_presumptive_gc_ct_rx'),
                'sti_syphilis_rx'      => $request->input('sti_syphilis_rx'),
                'sti_service'          => $request->boolean('sti_service'),
                'counseling_service'   => $request->boolean('counseling_service'),
                'hepc_tx'              => $request->boolean('hepc_tx'),
                'oi_tb'                => $request->boolean('oi_tb'),
                'oi_mac'               => $request->boolean('oi_mac'),
                'oi_crypto'            => $request->boolean('oi_crypto'),
                'oi_cmv'               => $request->boolean('oi_cmv'),
                'oi_pcp'               => $request->boolean('oi_pcp'),
                'oi_toxo'              => $request->boolean('oi_toxo'),
                'oi_penic'             => $request->boolean('oi_penic'),
                'oi_pml'               => $request->boolean('oi_pml'),
                'oi_other'             => $request->boolean('oi_other'),
                'oi_other_specify'     => $request->input('oi_other_specify'),
                'next_appointment_date'=> $this->parseDateInput($request->input('next_appointment_date')),
                'refer_to_mam'         => $request->boolean('refer_to_mam'),
                'reason_of_ref'        => $request->input('reason_of_ref'),
                'transfer_out_center'  => $request->input('transfer_out_center'),
            ]);
        }

        if ($actions['vl'] === 'delete') {
            $payload = array_merge($payload, [
                'vl_test_date' => null,
                'vl_copies_ml' => null,
                'vl_result'    => null,
                'remarks'      => null,
            ]);
        } else {
        $payload = array_merge($payload, [
            'vl_test_date' => $this->parseDateInput($request->input('vl_test_date')),
            'vl_copies_ml' => $request->input('vl_copies_ml'),
            'vl_result'    => $request->input('vl_result'),
            'remarks'      => $request->input('remarks'),
        ]);
        }

        return $payload;
    }

    protected function resetServicesPayload(): array
    {
        return [
            'fp_service'            => false,
            'fp_condom'             => false,
            'fp_oc_pills'           => false,
            'fp_depo'               => false,
            'prevention_commodities_provided' => false,
            'prevention_condom'     => false,
            'prevention_ns'         => false,
            'sti_patient_type'      => null,
            'sti_visit_type'        => null,
            'sti_complaint'         => null,
            'sti_syphilis_rdt_result' => null,
            'sti_syphilis_rpr_result' => null,
            'sti_syphilis_rpr_qual'  => null,
            'sti_presumptive_gc_ct_rx' => null,
            'sti_syphilis_rx'       => null,
            'sti_service'           => false,
            'counseling_service'    => false,
            'hepc_tx'               => false,
            'oi_tb'                 => false,
            'oi_mac'                => false,
            'oi_crypto'             => false,
            'oi_cmv'                => false,
            'oi_pcp'                => false,
            'oi_toxo'               => false,
            'oi_penic'              => false,
            'oi_pml'                => false,
            'oi_other'              => false,
            'oi_other_specify'      => null,
            'next_appointment_date' => null,
            'refer_to_mam'          => false,
            'reason_of_ref'         => null,
            'transfer_out_center'   => null,
        ];
    }

    protected function parseClinicList($value): array
    {
        if (is_array($value)) {
            $parts = $value;
        } else {
            $raw = trim((string) $value);
            if ($raw === '') {
                return [];
            }
            $decoded = null;
            if (str_starts_with($raw, '[')) {
                $decoded = json_decode($raw, true);
            }
            if (is_array($decoded)) {
                $parts = $decoded;
            } else {
                $parts = preg_split('/[,\s|;]+/', $raw) ?: [];
            }
        }

        $filtered = array_filter(array_map('trim', $parts), fn ($item) => $item !== '');
        return array_values(array_unique($filtered));
    }

    protected function resolveSingleClinicId($value): ?string
    {
        $ids = $this->parseClinicList($value);
        if (count($ids) !== 1) {
            return null;
        }

        return (string) $ids[0];
    }

    protected function resolveVisitClinicId(?string $requestedClinicId, ?PtConfig $config): ?string
    {
        $userClinicId = $this->resolveSingleClinicId(Auth::user()?->clinic);
        if ($userClinicId) {
            return $userClinicId;
        }

        if ($requestedClinicId !== null && $requestedClinicId !== '') {
            return (string) $requestedClinicId;
        }

        if ($config?->{'Clinic_ID'}) {
            return (string) $config->{'Clinic_ID'};
        }

        return null;
    }

    protected function getClinicOptionsForUser($user = null): array
    {
        $clinics = (array) config('art.clinics', []);
        if (!$clinics) {
            return [];
        }

        $user = $user ?: Auth::user();
        if (!$user) {
            return $clinics;
        }

        if (method_exists($user, 'isAdmin') && $user->isAdmin()) {
            return $clinics;
        }

        $allowed = $this->parseClinicList($user->clinic ?? '');
        if (!$allowed) {
            return [];
        }

        $allowedLookup = array_flip(array_map('strval', $allowed));
        $options = [];
        foreach ($clinics as $id => $name) {
            if (isset($allowedLookup[(string) $id])) {
                $options[$id] = $name;
            }
        }

        return $options;
    }

    protected function getArtClinicScope(): ?array
    {
        $user = Auth::user();
        if (!$user) {
            return null;
        }
        if (method_exists($user, 'isAdmin') && $user->isAdmin()) {
            return null;
        }
        $clinics = $this->parseClinicList($user->clinic ?? '');
        return $clinics ?: null;
    }

    protected function applyClinicFilter($query, $clinicScope): void
    {
        $ids = $this->parseClinicList($clinicScope);
        if (!$ids) {
            return;
        }

        if (count($ids) === 1) {
            $query->where('Clinic_ID', $ids[0]);
            return;
        }
        $query->whereIn('Clinic_ID', $ids);
    }

    protected function resolvePidFromIdentifier(?string $value, string $type): ?string
    {
        if (blank($value)) {
            return null;
        }

        $type = strtolower($type);
        $columnMap = [
            'id'        => 'id',
            'pid'       => 'Pid',
            'nap_id'    => 'NAP_ID',
            'fuchia_id' => 'FuchiaID',
        ];

        if ($type === 'pid') {
            return $value;
        }

        $column = $columnMap[$type] ?? 'Pid';

        $config = PtConfig::where($column, $value)->first();

        return $config?->Pid;
    }

    protected function resolveIdentifiers(string $value, string $type, ?array $clinicId = null): array
    {
        $value = trim($value);
        $type = strtolower($type);
        $config = null;
        $pid = null;
        $napId = null;

        if ($type === 'id') {
            $config = PtConfig::query()->whereKey($value)->first();
            $pid = $config?->Pid;
            $napId = $config?->{'NAP_ID'};
        } elseif ($type === 'pid') {
            $pid = $value;
            $config = PtConfig::query()->where('Pid', $pid)->first();
            $napId = $config?->{'NAP_ID'};
        } else {
            $columnMap = [
                'nap_id'    => 'NAP_ID',
                'fuchia_id' => 'FuchiaID',
            ];
            $column = $columnMap[$type] ?? null;

            if ($column) {
                $config = PtConfig::query()->where($column, $value)->first();
                $pid = $config?->Pid;
                $napId = $config?->{'NAP_ID'};
            }
        }

        if (!$config && $pid) {
            $config = PtConfig::query()->where('Pid', $pid)->first();
            $napId = $napId ?: $config?->{'NAP_ID'};
        }

        // Fallback: look at followups table if not found in PtConfig
        if (!$config) {
            if ($type === 'pid' || $pid) {
                $query = ArtFollowup::query();
                $this->applyClinicFilter($query, $clinicId);
                $fallback = $query->where('pid', $pid ?: $value)->latest('visit_date')->first();
                $pid = $pid ?: $fallback?->pid;
                $napId = $napId ?: $fallback?->nap_id;
            } elseif ($type === 'nap_id' || $napId) {
                $query = ArtFollowup::query();
                $this->applyClinicFilter($query, $clinicId);
                $fallback = $query->where(function ($q) use ($napId, $value) {
                    $needle = $napId ?: $value;
                    $q->where('nap_id', $needle)
                        ->orWhere('NAP_ID', $needle);
                })->latest('visit_date')->first();
                $pid = $pid ?: $fallback?->pid;
                $napId = $napId ?: $fallback?->nap_id;
            } elseif ($type === 'fuchia_id') {
                $query = ArtFollowup::query();
                $this->applyClinicFilter($query, $clinicId);
                $fallback = $query->where('FuchiaID', $value)->latest('visit_date')->first();
                $pid = $pid ?: $fallback?->pid;
                $napId = $napId ?: $fallback?->nap_id;
            }
        }

        if (!$napId && $type === 'nap_id') {
            $napId = $value;
        }

        return [$pid, $config?->id, $napId];
    }

    protected function syncPtConfig(Request $request, ?string $pid, $configId = null): PtConfig
    {
        if ($request->filled('nap_id')) {
            $config = PtConfig::where('NAP_ID', $request->input('nap_id'))->first() ?? new PtConfig();
        } elseif ($configId) {
            $config = PtConfig::find($configId) ?? new PtConfig();
        } elseif ($pid) {
            $config = PtConfig::firstOrNew(['Pid' => $pid]);
        } else {
            $config = new PtConfig();
        }

        if ($request->filled('nap_id')) {
            $config->{'NAP_ID'} = $request->input('nap_id');
        }
        if ($request->filled('fuchia_id')) {
            $config->{'FuchiaID'} = $request->input('fuchia_id');
        }
        if ($request->filled('patient_name')) {
            $config->{'Name'} = Crypt::encryptString($request->input('patient_name'));
        }
        if ($request->filled('patient_phone')) {
            $config->{'Phone'} = Crypt::encryptString($request->input('patient_phone'));
        }
        if ($request->filled('patient_gender')) {
            $config->{'Gender'} = Crypt::encrypt_light($request->input('patient_gender'), 'General');
        }
        if ($request->filled('patient_dob')) {
            $config->{'Date of Birth'} = Crypt::encryptString(
                $this->parseDateInput($request->input('patient_dob')) ?: ''
            );
        }
        if ($request->filled('patient_township')) {
            $config->{'Township'} = Crypt::encryptString($request->input('patient_township'));
        }
        if ($request->filled('patient_main_risk')) {
            $config->{'Main Risk'} = Crypt::encrypt_light($request->input('patient_main_risk'), 'General');
        }
        if ($request->filled('patient_notes')) {
            $config->{'Notes'} = Crypt::encryptString($request->input('patient_notes'));
        }
        if ($request->filled('patient_address')) {
            $config->{'Address'} = Crypt::encryptString($request->input('patient_address'));
        }
        if ($request->filled('reg_date')) {
            $config->{'Reg Date'} = $this->parseDateInput($request->input('reg_date'));
        }

        if ($request->has('age')) {
            $config->{'Agey'} = $request->filled('age') ? (int) $request->input('age') : null;
        }

        if ($request->has('age_months')) {
            $config->{'Agem'} = $request->filled('age_months')
                ? max(0, (int) $request->input('age_months'))
                : null;
        }

        $clinicId = $request->input('clinic_id');
        if (!$clinicId) {
            $clinicId = $config->{'Clinic_ID'} ?? $this->resolveSingleClinicId(Auth::user()?->clinic);
        }
        if ($clinicId) {
            $config->{'Clinic_ID'} = $clinicId;
        }

        if ($pid) {
            $config->{'Pid'} = $pid;
        }

        $config->save();

        return $config;
    }

    protected function dateRule(bool $required = false, bool $allowFuture = false): array
    {
        $rules = $required ? ['required', 'string'] : ['nullable', 'string'];

        $rules[] = function ($attribute, $value, $fail) use ($allowFuture) {
            if (blank($value)) {
                return;
            }

            if (!$this->isValidDateInput($value)) {
                $fail("The {$attribute} does not match the required date format (d-m-Y).");
                return;
            }

            try {
                $parsed = Carbon::parse($value)->startOfDay();
            } catch (\Throwable $e) {
                $fail("The {$attribute} is not a valid date.");
                return;
            }

            $year = (int) $parsed->format('Y');
            if ($year < 1900 || $year > 2100) {
                $fail("The {$attribute} year must be between 1900 and 2100.");
                return;
            }

            if (!$allowFuture && $parsed->greaterThan(Carbon::today())) {
                $fail("The {$attribute} cannot be greater than today.");
            }
        };

        return $rules;
    }

    protected function isValidDateInput(?string $value): bool
    {
        if (blank($value)) {
            return true;
        }

        $value = trim($value);
        $formats = ['d-m-Y', 'd/m/Y', 'Y-m-d'];

        foreach ($formats as $format) {
            try {
                Carbon::createFromFormat($format, $value)->format('Y-m-d');
                return true;
            } catch (\Throwable $e) {
                // keep checking
            }
        }

        try {
            Carbon::parse($value);
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    protected function parseDateInput($value): ?string
    {
        if ($value instanceof \DateTimeInterface) {
            $year = (int) $value->format('Y');
            return ($year >= 1900 && $year <= 2100) ? $value->format('Y-m-d') : null;
        }

        if (blank($value)) {
            return null;
        }

        if (is_numeric($value)) {
            $excelSerial = (float) $value;
            if ($excelSerial > 1000 && $excelSerial < 100000) {
                try {
                    $parsed = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($excelSerial);
                    $year = (int) $parsed->format('Y');
                    if ($year >= 1900 && $year <= 2100) {
                        return $parsed->format('Y-m-d');
                    }
                } catch (\Throwable $e) {
                    // Fall through to string parsing.
                }
            }
        }

        $value = trim((string) $value);

        if (preg_match('/^(\d{1,2})[\/-](\d{1,2})[\/-](\d{2})$/', $value, $matches)) {
            $day = (int) $matches[1];
            $month = (int) $matches[2];
            $year = (int) $matches[3];
            $year += $year >= 70 ? 1900 : 2000;

            if (checkdate($month, $day, $year) && $year >= 1900 && $year <= 2100) {
                return sprintf('%04d-%02d-%02d', $year, $month, $day);
            }
        }

        if (preg_match('/^(\d{1,2})[\/-](\d{1,2})[\/-](\d{4})$/', $value, $matches)) {
            $day = (int) $matches[1];
            $month = (int) $matches[2];
            $year = (int) $matches[3];

            if (checkdate($month, $day, $year) && $year >= 1900 && $year <= 2100) {
                return sprintf('%04d-%02d-%02d', $year, $month, $day);
            }
        }

        if (preg_match('/^(\d{4})[\/-](\d{1,2})[\/-](\d{1,2})$/', $value, $matches)) {
            $year = (int) $matches[1];
            $month = (int) $matches[2];
            $day = (int) $matches[3];

            if (checkdate($month, $day, $year) && $year >= 1900 && $year <= 2100) {
                return sprintf('%04d-%02d-%02d', $year, $month, $day);
            }
        }

        $formats = ['!d-m-Y', '!d/m/Y', '!Y-m-d', '!d-m-y', '!d/m/y', '!j-n-Y', '!j/n/Y', '!j-n-y', '!j/n/y'];

        foreach ($formats as $format) {
            try {
                $parsed = Carbon::createFromFormat($format, $value);
                $year = (int) $parsed->format('Y');
                if ($year < 1900 || $year > 2100) {
                    continue;
                }
                return $parsed->format('Y-m-d');
            } catch (\Throwable $e) {
                // continue
            }
        }

        try {
            $parsed = Carbon::parse($value);
            $year = (int) $parsed->format('Y');
            if ($year < 1900 || $year > 2100) {
                return null;
            }
            return $parsed->format('Y-m-d');
        } catch (\Throwable $e) {
            Log::warning('Unable to parse date input', ['value' => $value]);
            return null;
        }
    }

    protected function normalizeHeaders(array $row): array
    {
        $headers = [];

        foreach ($row as $index => $value) {
            $headers[$index] = $this->normalizeHeaderKey($value);
        }

        return $headers;
    }

    protected function normalizeHeaderKey($value): string
    {
        $value = (string) $value;
        $value = str_replace(['–', '—'], '-', $value);
        $value = preg_replace('/[^A-Za-z0-9]+/', '_', trim($value));

        return trim(Str::snake($value), '_');
    }

    protected function mapRow(array $headers, array $row): array
    {
        $assoc = [];

        foreach ($headers as $index => $key) {
            $assoc[$key] = $row[$index] ?? null;
        }

        return $assoc;
    }

    protected function interpretBoolean($value): bool
    {
        if ($value === null) {
            return false;
        }

        $normalized = strtolower(trim((string) $value));
        if ($normalized === '') {
            return false;
        }

        if (in_array($normalized, ['1', 'yes', 'y', 'true', 't', 'on'], true)) {
            return true;
        }
        if (in_array($normalized, ['0', 'no', 'n', 'false', 'f', 'off'], true)) {
            return false;
        }

        if (is_numeric($normalized)) {
            return (float) $normalized !== 0.0;
        }

        return false;
    }

    protected function firstFilled(array $assoc, array $keys)
    {
        foreach ($keys as $key) {
            if ($key === null) {
                continue;
            }
            if (array_key_exists($key, $assoc) && filled($assoc[$key])) {
                return $assoc[$key];
            }
        }

        return null;
    }

    protected function importPatients(Collection $rows, array $headers): array
    {
        $imported = 0;
        $skipped = 0;
        $defaultClinicId = $this->resolveSingleClinicId(Auth::user()?->clinic);

        foreach ($rows as $row) {
            $assoc = $this->mapRow($headers, $row->toArray());
            if ($this->importPatientAssoc($assoc, $defaultClinicId)) {
                $imported++;
            } else {
                $skipped++;
            }
        }

        return [$imported, $skipped];
    }

    protected function importFollowups(Collection $rows, array $headers): array
    {
        $imported = 0;
        $skipped = 0;
        $clinicId = $this->resolveSingleClinicId(Auth::user()?->clinic);

        foreach ($rows as $row) {
            $assoc = $this->mapRow($headers, $row->toArray());
            if ($this->importFollowupAssoc($assoc, $clinicId)) {
                $imported++;
            } else {
                $skipped++;
            }
        }

        return [$imported, $skipped];
    }

    protected function importPatientAssoc(
        array $assoc,
        ?string $defaultClinicId = null,
        ?string $defaultRegDate = null,
        ?string &$reason = null,
        ?string &$status = null,
        ?int &$recordId = null
    ): bool
    {
        $this->applyDefaultRegDate($assoc, $defaultRegDate);
        $pid = trim((string) $this->firstFilled($assoc, ['pid', 'general_id', 'generalid']));
        $napId = trim((string) $this->firstFilled($assoc, ['nap_id', 'napid']));
        $fuchia = trim((string) $this->firstFilled($assoc, ['fuchia_id', 'fuchiaid']));
        $innerId = trim((string) ($assoc['inner_id'] ?? ''));
        $rowClinicId = $this->firstFilled($assoc, ['clinic_id', 'clinicid']);
        $clinicId = filled($rowClinicId) ? trim((string) $rowClinicId) : $defaultClinicId;

        $config = $this->findPtConfig($pid, $napId, $fuchia, $innerId);

        if (!$config && $napId) {
            $config = PtConfig::where('NAP_ID', $napId)->first();
        }

        if (!$config && $fuchia) {
            $config = PtConfig::where('FuchiaID', $fuchia)->first();
        }

        if (!$config && $pid) {
            $config = PtConfig::where('Pid', $pid)->first();
        }

        if (!$config) {
            $config = new PtConfig();
        } else {
            $pid = $pid ?: ($config->{'Pid'} ?: $napId);
        }

        $isNew = !$config->exists;
        $this->fillPtConfigFromAssoc($config, $assoc, $pid, $napId, $fuchia, $clinicId);
        if (!$config->{'Reg Date'}) {
            $reason = 'Missing Reg Date';
            $status = 'skipped';
            return false;
        }
        $config->save();
        $status = $isNew ? 'created' : 'updated';
        $recordId = $config->id;

        return true;
    }

    protected function importFollowupAssoc(
        array $assoc,
        ?string $defaultClinicId = null,
        ?string $defaultRegDate = null,
        ?string &$reason = null,
        ?string &$status = null,
        ?int &$recordId = null
    ): bool
    {
        $this->applyDefaultRegDate($assoc, $defaultRegDate);
        $pid = trim((string) $this->firstFilled($assoc, ['pid', 'general_id', 'generalid']));
        $napId = trim((string) $this->firstFilled($assoc, ['nap_id', 'napid']));
        $fuchia = trim((string) $this->firstFilled($assoc, ['fuchia_id', 'fuchiaid']));
        $innerId = trim((string) ($assoc['inner_id'] ?? ''));
        $visitDate = $this->parseDateInput($assoc['visit_date'] ?? null);
        $rowClinicId = $this->firstFilled($assoc, ['clinic_id', 'clinicid']);
        $clinicId = filled($rowClinicId) ? trim((string) $rowClinicId) : $defaultClinicId;

        if (!$visitDate) {
            $reason = 'Missing Visit Date';
            $status = 'skipped';
            return false;
        }

        if (!$napId) {
            $reason = 'Missing NAP ID';
            $status = 'skipped';
            return false;
        }

        $age = $this->firstFilled($assoc, ['age', 'agey', 'age_years']);
        if ($age === null || $age === '') {
            $reason = 'Missing Agey';
            $status = 'skipped';
            return false;
        }

        $sex = $this->firstFilled($assoc, ['sex', 'gender']);
        if ($sex === null || $sex === '') {
            $reason = 'Missing Sex';
            $status = 'skipped';
            return false;
        }

        $config = $this->findPtConfig(null, $napId, null, $innerId) ?? new PtConfig();

        $this->fillPtConfigFromAssoc($config, $assoc, $pid, $napId, $fuchia, $clinicId);
        if (!$config->{'Reg Date'}) {
            $reason = 'Missing Reg Date';
            $status = 'skipped';
            return false;
        }
        $config->save();

        $resolvedPid = $config->{'Pid'} ?: null;
        $resolvedPidNumeric = is_numeric($resolvedPid) ? (int) $resolvedPid : null;

        $visitClinicId = $config->{'Clinic_ID'} ?? $clinicId;
        $visitNapId = $config->{'NAP_ID'} ?? $napId;

        if (!$visitClinicId || !$visitNapId) {
            $reason = 'Missing clinic or NAP ID';
            $status = 'skipped';
            return false;
        }

        $duplicateQuery = ArtFollowup::whereDate('visit_date', $visitDate)
            ->where('nap_id', $visitNapId);
        if ($duplicateQuery->exists()) {
            $reason = 'Duplicate visit on date';
            $status = 'skipped';
            return false;
        }

        $visitId = trim((string) ($this->firstFilled($assoc, ['visit_id', 'visitid']) ?? ''));
        if (!$visitId) {
            $visitId = sprintf(
                '%s-IMP-%s',
                $visitNapId,
                md5($visitNapId . $visitDate . microtime())
            );
        }

        $payload = [
            'clinic_id'            => $visitClinicId,
            'nap_id'               => $visitNapId,
            'pid'                  => $resolvedPidNumeric,
            'visit_date'           => $visitDate,
            'age'                  => $age,
            'sex'                  => $sex,
            'main_risk'            => $this->firstFilled($assoc, ['main_risk']),
            'art_started_date'     => $this->parseDateInput($this->firstFilled($assoc, ['art_started_date'])),
            'art_regime'           => $this->firstFilled($assoc, ['art_regime']),
            'art_regime_changed'   => $this->interpretBoolean($this->firstFilled($assoc, ['art_regime_changed'])),
            'patient_status'       => $this->firstFilled($assoc, ['patient_status']),
            'next_appointment_date'=> $this->parseDateInput($this->firstFilled($assoc, ['next_appointment_date'])),
            'reason_of_ref'        => $this->firstFilled($assoc, ['reason_of_ref']),
            'transfer_out_center'  => $this->firstFilled($assoc, ['transfer_out_other_nap_center', 'tout_other_nap_center']),
            'vl_test_date'         => $this->parseDateInput($this->firstFilled($assoc, ['viral_load_test_date', 'viral_load_tstdate'])),
            'vl_copies_ml'         => $this->firstFilled($assoc, ['viral_load_copies_ml']),
            'vl_result'            => $this->firstFilled($assoc, ['viral_load_result']),
            'oi_other_specify'     => $this->firstFilled($assoc, ['other_specify']),
            'remarks'              => $this->firstFilled($assoc, ['remarks']),
        ];

        $boolMap = [
            'family_planning_serv_provided_res' => 'fp_service',
            'fp_condom'                         => 'fp_condom',
            'fp_oc_pills'                       => 'fp_oc_pills',
            'fp_depo'                           => 'fp_depo',
            'prevention_commodities_provided'   => 'prevention_commodities_provided',
            'prevention_condom'                 => 'prevention_condom',
            'prevention_ns'                     => 'prevention_ns',
            'sti_serv_provided_res'             => 'sti_service',
            'counseling_service_res'            => 'counseling_service',
            'hepc_tx_res'                       => 'hepc_tx',
            'refer_to_mam_res'                  => 'refer_to_mam',
            'tb_res'                            => 'oi_tb',
            'pcp_res'                           => 'oi_pcp',
            'mac_res'                           => 'oi_mac',
            'toxo_res'                          => 'oi_toxo',
            'crypto_res'                        => 'oi_crypto',
            'penicilliosis_res'                 => 'oi_penic',
            'cmv_res'                           => 'oi_cmv',
            'pml_res'                           => 'oi_pml',
            'other_res'                         => 'oi_other',
        ];

        foreach ($boolMap as $column => $field) {
            if (array_key_exists($column, $assoc)) {
                $payload[$field] = $this->interpretBoolean($assoc[$column]);
            }
        }

        Log::debug('Importing ART follow-up row', [
            'pid' => $resolvedPid,
            'visit_id' => $visitId,
            'clinic_id' => $payload['clinic_id'] ?? null,
            'nap_id' => $payload['nap_id'] ?? null,
        ]);

        $match = ['nap_id' => $visitNapId, 'visit_id' => $visitId];

        $followup = ArtFollowup::updateOrCreate($match, $payload);
        $status = $followup->wasRecentlyCreated ? 'created' : 'updated';
        $recordId = $followup->id;

        return true;
    }

    protected function applyDefaultRegDate(array &$assoc, ?string $defaultRegDate = null): void
    {
        if (!$defaultRegDate) {
            return;
        }
        $existing = $this->firstFilled($assoc, ['reg_date', 'regdate', 'registration_date']);
        if (filled($existing)) {
            return;
        }
        $assoc['reg_date'] = $defaultRegDate;
    }

    protected function formatVisitOrdinal(int $order): string
    {
        return match ($order) {
            1 => 'First Time',
            2 => 'Second Time',
            3 => 'Third Time',
            default => $this->formatOrdinal($order) . ' Time',
        };
    }

    protected function formatOrdinal(int $number): string
    {
        $suffix = 'th';
        if (($number % 100) < 11 || ($number % 100) > 13) {
            switch ($number % 10) {
                case 1:
                    $suffix = 'st';
                    break;
                case 2:
                    $suffix = 'nd';
                    break;
                case 3:
                    $suffix = 'rd';
                    break;
            }
        }

        return $number . $suffix;
    }

    public function template(string $type)
    {
        $type = strtolower($type);

            switch ($type) {
            case 'followups':
                $headings = [
                    'Inner ID',
                    'Pid',
                    'NAP_ID',
                    'Fuchia_ID',
                    'Visit_ID',
                    'Visit_Date',
                    'Agey',
                    'Sex',
                    'Main_Risk',
                    'ART_Started_Date',
                    'ART_Regime',
                    'ART_Regime_Changed',
                    'Patient_Status',
                    'Next_Appointment_Date',
                    'Reason_of_ref',
                    'Transfer_Out_Other_NAP_Center',
                    'Viral_Load_Test_Date',
                    'Viral_Load_Copies_ml',
                    'Viral_Load_Result',
                    'TB_res',
                    'PCP_res',
                    'MAC_res',
                    'Toxo_res',
                    'Crypto_res',
                    'Penicilliosis_res',
                    'CMV_res',
                    'PML_res',
                    'Other_res',
                    'Other_Specify',
                    'Family_Planning_Serv_Provided_res',
                    'STI_Serv_Provided_res',
                    'Counseling_Service_res',
                    'HepC_Tx_res',
                    'Refer_to_MAM_res',
                    'Remarks',
                ];
                $rows = collect();
                $filename = 'art_followups_template.xlsx';
                break;
            case 'patients':
                $headings = [
                    'Clinic_ID',
                    'Inner ID',
                    'Pid',
                    'NAP_ID',
                    'Fuchia_ID',
                    'Name',
                    'Gender',
                    'Date_of_Birth',
                    'Phone',
                    'Township',
                    'Address',
                    'Main_Risk',
                    'Notes',
                    'Reg_Date',
                ];
                $rows = collect();
                $filename = 'art_patients_template.xlsx';
                break;
            default:
                abort(404);
        }

        return Excel::download(new ArtFollowupExport($rows, $headings), $filename);
    }

    protected function generatePatientId(): string
    {
        $maxPatients = (int) (Patients::max('Pid') ?? 0);
        $maxFollowups = (int) (ArtFollowup::max('pid') ?? 0);

        $base = max($maxPatients, $maxFollowups, 100000);

        return (string) ($base + 1);
    }

    protected function generateVisitId($pid): string
    {
        if (!$pid) {
            return (string) Str::uuid();
        }

        $count = ArtFollowup::where('pid', $pid)->count() + 1;
        return sprintf('%s-%02d', $pid, $count);
    }

    // Example lab endpoint – adjust to your real lab table
    public function labs($pid)
    {
        $labs = DB::table('labs')
            ->where('CID', $pid)
            ->orderByDesc('vdate')
            ->limit(10)
            ->get();

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

    protected function findPtConfig(?string $pid, ?string $napId, ?string $fuchiaId, ?string $innerId = null): ?PtConfig
    {
        if ($innerId && $config = PtConfig::find($innerId)) {
            return $config;
        }

        if ($napId && $config = PtConfig::where('NAP_ID', $napId)->first()) {
            return $config;
        }

        if ($pid && $config = PtConfig::where('Pid', $pid)->first()) {
            return $config;
        }

        if ($fuchiaId && $config = PtConfig::where('FuchiaID', $fuchiaId)->first()) {
            return $config;
        }

        return null;
    }

    protected function findDuplicatePtConfigs(string $type, string $value, ?array $clinicScope): Collection
    {
        $column = $type === 'fuchia_id' ? 'FuchiaID' : 'Pid';
        return PtConfig::query()->where($column, $value)->orderBy('id')->get();
    }

    protected function formatDuplicateMatch(PtConfig $config): array
    {
        $data = $this->decryptConfigRow($config->toArray());

        return [
            'id' => $config->id,
            'nap_id' => $config->{'NAP_ID'},
            'pid' => $config->{'Pid'},
            'fuchia_id' => $config->{'FuchiaID'},
            'clinic_id' => $config->{'Clinic_ID'},
            'name' => $data['Name'] ?? null,
            'gender' => $data['Gender'] ?? null,
            'township' => $data['Township'] ?? null,
            'phone' => $data['Phone'] ?? null,
            'reg_date' => $data['Reg Date'] ?? null,
        ];
    }

    protected function normalizeIdentifierInputs(Request $request): void
    {
        $fields = ['pid', 'nap_id', 'fuchia_id', 'config_id', 'visit_id'];
        $updates = [];
        foreach ($fields as $field) {
            if (!$request->has($field)) {
                continue;
            }
            $value = $request->input($field);
            if (is_string($value)) {
                $trimmed = trim($value);
                if ($trimmed !== $value) {
                    $updates[$field] = $trimmed;
                }
            }
        }
        if ($updates) {
            $request->merge($updates);
        }
    }

    protected function fillPtConfigFromAssoc(
        PtConfig $config,
        array $assoc,
        ?string $pid,
        ?string $napId,
        ?string $fuchiaId,
        ?string $clinicId = null
    ): void
    {
        if ($pid && (!$config->{'Pid'} || $config->{'Pid'} !== $pid)) {
            $config->{'Pid'} = $pid;
        }

        if ($napId) {
            $config->{'NAP_ID'} = $napId;
        }

        if ($fuchiaId) {
            $config->{'FuchiaID'} = $fuchiaId;
        }

            if ($name = trim((string) $this->firstFilled($assoc, ['name']))) {
            $config->{'Name'} = Crypt::encryptString($name);
        }

            if ($phone = trim((string) $this->firstFilled($assoc, ['phone']))) {
            $config->{'Phone'} = Crypt::encryptString($phone);
        }

            if ($township = trim((string) $this->firstFilled($assoc, ['township']))) {
            $config->{'Township'} = Crypt::encryptString($township);
        }

            if ($address = trim((string) $this->firstFilled($assoc, ['address']))) {
            $config->{'Address'} = Crypt::encryptString($address);
        }

        if ($notes = trim((string) $this->firstFilled($assoc, ['notes']))) {
            $config->{'Notes'} = Crypt::encryptString($notes);
        }

        if ($gender = trim((string) $this->firstFilled($assoc, ['gender', 'sex']))) {
            $config->{'Gender'} = Crypt::encrypt_light($gender, 'General');
        }

        if ($risk = trim((string) $this->firstFilled($assoc, ['main_risk']))) {
            $config->{'Main Risk'} = Crypt::encrypt_light($risk, 'General');
        }

        if ($dob = $this->parseDateInput($this->firstFilled($assoc, ['date_of_birth', 'dob']))) {
            $config->{'Date of Birth'} = $dob;
        }

        if ($regDate = $this->parseDateInput($this->firstFilled($assoc, ['reg_date', 'regdate', 'registration_date']))) {
            $config->{'Reg Date'} = $regDate;
        }

        if (($ageYears = $this->firstFilled($assoc, ['age', 'agey', 'age_years'])) !== null) {
            $config->{'Agey'} = (int) $ageYears;
        }

        if (($ageMonths = $this->firstFilled($assoc, ['age_months', 'agem', 'age_month'])) !== null) {
            $config->{'Agem'} = max(0, (int) $ageMonths);
        }

        if ($clinicId) {
            $config->{'Clinic_ID'} = $clinicId;
        }
    }

    protected function transformPtConfigForSync(PtConfig $config): array
    {
        $data = $this->decryptConfigRow($config->toArray());

        $napId = $config->{'NAP_ID'} ?: (string) $config->id;

        return [
            'id'        => $config->id,
            'clinic_id' => $config->{'Clinic_ID'},
            'pid'       => $config->{'Pid'},
            'nap_id'    => $napId,
            'fuchia_id' => $config->{'FuchiaID'},
            'name'      => $data['Name'] ?? null,
            'phone'     => $data['Phone'] ?? null,
            'gender'    => $data['Gender'] ?? null,
            'dob'       => $data['Date of Birth'] ?? null,
            'township'  => $data['Township'] ?? null,
            'main_risk' => $data['Main Risk'] ?? null,
            'address'   => $data['Address'] ?? null,
            'notes'     => $data['Notes'] ?? null,
            'reg_date'  => $data['Reg Date'] ?? null,
            'age_years' => $config->{'Agey'},
            'age_months'=> $config->{'Agem'},
            'updated_at'=> $config->updated_at,
        ];
    }

    protected function transformFollowupForSync(ArtFollowup $followup): array
    {
        return [
            'id'                    => $followup->id,
            'clinic_id'             => $followup->clinic_id,
            'nap_id'                => $followup->nap_id,
            'pid'                   => $followup->pid,
            'visit_id'              => $followup->visit_id,
            'visit_date'            => optional($followup->visit_date)->toDateString(),
            'age'                   => $followup->age,
            'sex'                   => $followup->sex,
            'main_risk'             => $followup->main_risk,
            'art_started_date'      => optional($followup->art_started_date)->toDateString(),
            'art_regime'            => $followup->art_regime,
            'art_regime_changed'    => (bool) $followup->art_regime_changed,
            'patient_status'        => $followup->patient_status,
            'fp_service'            => $followup->fp_service,
            'fp_condom'             => $followup->fp_condom,
            'fp_oc_pills'           => $followup->fp_oc_pills,
            'fp_depo'               => $followup->fp_depo,
            'prevention_commodities_provided' => $followup->prevention_commodities_provided,
            'prevention_condom'     => $followup->prevention_condom,
            'prevention_ns'         => $followup->prevention_ns,
            'sti_patient_type'      => $followup->sti_patient_type,
            'sti_visit_type'        => $followup->sti_visit_type,
            'sti_complaint'         => $followup->sti_complaint,
            'sti_syphilis_rdt_result' => $followup->sti_syphilis_rdt_result,
            'sti_syphilis_rpr_result' => $followup->sti_syphilis_rpr_result,
            'sti_syphilis_rpr_qual' => $followup->sti_syphilis_rpr_qual,
            'sti_presumptive_gc_ct_rx' => $followup->sti_presumptive_gc_ct_rx,
            'sti_syphilis_rx'       => $followup->sti_syphilis_rx,
            'sti_service'           => $followup->sti_service,
            'counseling_service'    => $followup->counseling_service,
            'hepc_tx'               => $followup->hepc_tx,
            'oi_tb'                 => $followup->oi_tb,
            'oi_mac'                => $followup->oi_mac,
            'oi_crypto'             => $followup->oi_crypto,
            'oi_cmv'                => $followup->oi_cmv,
            'oi_pcp'                => $followup->oi_pcp,
            'oi_toxo'               => $followup->oi_toxo,
            'oi_penic'              => $followup->oi_penic,
            'oi_pml'                => $followup->oi_pml,
            'oi_other'              => $followup->oi_other,
            'oi_other_specify'      => $followup->oi_other_specify,
            'next_appointment_date' => optional($followup->next_appointment_date)->toDateString(),
            'refer_to_mam'          => $followup->refer_to_mam,
            'reason_of_ref'         => $followup->reason_of_ref,
            'transfer_out_center'   => $followup->transfer_out_center,
            'vl_test_date'          => optional($followup->vl_test_date)->toDateString(),
            'vl_copies_ml'          => $followup->vl_copies_ml,
            'vl_result'             => $followup->vl_result,
            'remarks'               => $followup->remarks,
            'updated_at'            => $followup->updated_at,
        ];
    }

    public function import(Request $request)
    {
        $redirectRoute = $request->input('redirect_route');
        $redirectTo = function () use ($redirectRoute) {
            if (is_string($redirectRoute) && $redirectRoute !== '' && \Illuminate\Support\Facades\Route::has($redirectRoute)) {
                return redirect()->route($redirectRoute);
            }
            return redirect()->route('services.index');
        };

        $request->validate([
            'import_type' => ['required', Rule::in(['patients', 'followups'])],
            'import_file' => ['required', 'file', 'mimes:xls,xlsx,csv'],
        ]);

        try {
            $sheets = Excel::toCollection(null, $request->file('import_file'));
        } catch (\Throwable $e) {
            Log::error('ART import failed (excel parse)', ['error' => $e->getMessage()]);
            return $redirectTo()
                ->withInput()
                ->with('info', 'Unable to read the uploaded file. Please ensure it is a valid Excel document.');
        }

        $rows = $sheets->first();

        if (!$rows || $rows->isEmpty()) {
            return $redirectTo()
                ->with('info', 'Uploaded file is empty.');
        }

        $headerRow = $rows->shift();
        $headers = $this->normalizeHeaders($headerRow?->toArray() ?? []);

        if (empty(array_filter($headers))) {
            return $redirectTo()
                ->with('info', 'Unable to detect column headings. Please include a header row in the spreadsheet.');
        }

        if ($request->input('import_type') === 'patients') {
            [$imported, $skipped] = $this->importPatients($rows, $headers);
            $message = "{$imported} patient rows imported.";
        } else {
            [$imported, $skipped] = $this->importFollowups($rows, $headers);
            $message = "{$imported} follow-up rows imported.";
        }

        if ($skipped) {
            $message .= " {$skipped} rows skipped (missing required fields).";
        }

        return $redirectTo()
            ->with('success', $message);
    }

    public function downloadSync(Request $request)
    {
        $request->validate(['since' => ['nullable', 'date']]);
        $since = $request->filled('since') ? Carbon::parse($request->input('since')) : null;
        $clinicId = $this->getArtClinicScope();

        $ptConfigs = PtConfig::query();
        $followups = ArtFollowup::query();

        $this->applyClinicFilter($followups, $clinicId);

        if ($since) {
            $ptConfigs->where('updated_at', '>=', $since);
            $followups->where('updated_at', '>=', $since);
        }

        $clinicByNap = [];
        $clinicByPid = [];
        $clinicMapQuery = ArtFollowup::query();
        $this->applyClinicFilter($clinicMapQuery, $clinicId);
        $clinicMapQuery->orderByDesc('updated_at')->chunk(1000, function ($rows) use (&$clinicByNap, &$clinicByPid) {
            foreach ($rows as $row) {
                $clinic = $row->clinic_id ?? $row->Clinic_ID ?? null;
                if (!$clinic) {
                    continue;
                }
                $napId = $row->nap_id ?? $row->NAP_ID ?? null;
                if ($napId && !array_key_exists($napId, $clinicByNap)) {
                    $clinicByNap[$napId] = $clinic;
                }
                $pid = $row->pid ?? null;
                if ($pid && !array_key_exists($pid, $clinicByPid)) {
                    $clinicByPid[$pid] = $clinic;
                }
            }
        });

        return response()->json([
            'pt_configs' => $ptConfigs->latest('updated_at')->get()->map(function ($item) use ($clinicByNap, $clinicByPid) {
                $payload = $this->transformPtConfigForSync($item);
                $napId = $payload['nap_id'] ?? ($item->{'NAP_ID'} ?? null);
                $pid = $payload['pid'] ?? ($item->{'Pid'} ?? null);
                $followupClinic = null;
                if ($napId && array_key_exists($napId, $clinicByNap)) {
                    $followupClinic = $clinicByNap[$napId];
                } elseif ($pid && array_key_exists($pid, $clinicByPid)) {
                    $followupClinic = $clinicByPid[$pid];
                }
                if ($followupClinic) {
                    $payload['clinic_id'] = $followupClinic;
                }
                return $payload;
            }),
            'followups'  => $followups->latest('updated_at')->get()->map(fn ($item) => $this->transformFollowupForSync($item)),
            'synced_at'  => now()->toISOString(),
        ]);
    }

    public function uploadSync(Request $request)
    {
        $followupsPayload = $request->input('followups', []);
        if (is_array($followupsPayload)) {
            foreach ($followupsPayload as &$row) {
                if (!is_array($row)) {
                    continue;
                }
                foreach (['nap_id', 'visit_id', 'pid', 'fuchia_id'] as $key) {
                    if (isset($row[$key]) && is_string($row[$key])) {
                        $row[$key] = trim($row[$key]);
                    }
                }
            }
            unset($row);
            $request->merge(['followups' => $followupsPayload]);
        }
        $payload = $request->validate([
            'followups'   => ['array'],
            'followups.*.nap_id' => ['required', 'string'],
            'followups.*.visit_date' => ['required', 'date'],
            'followups.*.clinic_id' => ['nullable', 'integer'],
            'followups.*.visit_id' => ['nullable', 'string'],
            'followups.*.pid' => ['nullable'],
            'forms'      => ['array'],
            'forms.*'    => ['array'],
        ]);

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

        foreach ($followups as $index => $row) {
            $napId = $row['nap_id'];
            $visitDate = $this->parseDateInput($row['visit_date'] ?? null);
            $row['age'] = $row['age'] ?? $row['agey'] ?? $row['Agey'] ?? null;
            $row['sex'] = $row['sex'] ?? $row['patient_gender'] ?? $row['gender'] ?? null;

            if (!$visitDate) {
                $results['errors'][] = [
                    'index' => $index,
                    'message' => 'Invalid visit date provided.',
                ];
                continue;
            }

            if (!filled($row['age'] ?? null)) {
                $results['errors'][] = [
                    'index' => $index,
                    'message' => 'Agey is required for follow-up record.',
                ];
                continue;
            }

            if (!filled($row['sex'] ?? null)) {
                $results['errors'][] = [
                    'index' => $index,
                    'message' => 'Sex is required for follow-up record.',
                ];
                continue;
            }

            $config = PtConfig::where('NAP_ID', $napId)->first();
            if (!$config) {
                $results['errors'][] = [
                    'index' => $index,
                    'message' => "NAP ID {$napId} not found on server.",
                ];
                continue;
            }

            $clinicId = $this->resolveVisitClinicId($row['clinic_id'] ?? null, $config);
            if (!$clinicId) {
                $results['errors'][] = [
                    'index' => $index,
                    'message' => 'Clinic ID missing for follow-up record.',
                ];
                continue;
            }

            $pid = $row['pid'] ?? $config->Pid;
            $visitId = $row['visit_id'] ?? ($pid ? $this->generateVisitId($pid) : (string) Str::uuid());

            $allowed = (new ArtFollowup())->getFillable();
            $fields = Arr::only($row, $allowed);
            $fields['visit_date'] = $visitDate;
            $fields['clinic_id'] = $clinicId;
            $fields['nap_id'] = $napId;
            $fields['pid'] = $pid;
            $fields['visit_id'] = $visitId;

            ArtFollowup::updateOrCreate(
                ['visit_id' => $visitId, 'NAP_ID' => $napId],
                $fields
            );

            $results['synced'][] = [
                'index' => $index,
                'visit_id' => $visitId,
            ];
        }

        $forms = $payload['forms'] ?? [];

        foreach ($forms as $index => $formRow) {
            $localId = $formRow['_local_id'] ?? null;
            $formRequest = new Request($formRow);
            $formRequest->setUserResolver(function () use ($request) {
                return $request->user();
            });

            try {
                $result = $this->persistArtForm($formRequest);
                $results['forms'][] = [
                    'index' => $index,
                    'local_id' => $localId,
                    'visit_id' => $result['visit_id'] ?? null,
                    'pid' => $result['route_params']['pid'] ?? null,
                ];
            } catch (ValidationException $e) {
                $results['errors'][] = [
                    'index' => $index,
                    'local_id' => $localId,
                    'message' => collect($e->errors())->flatten()->first() ?? 'Validation failed.',
                ];
            } catch (\Throwable $e) {
                $results['errors'][] = [
                    'index' => $index,
                    'local_id' => $localId,
                    'message' => $e->getMessage(),
                ];
            }
        }

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

    public function export(Request $request)
    {
        $request->validate([
            'export_type' => ['required', Rule::in(['followup', 'appointment', 'missed', 'ltfu'])],
            'start_date'  => ['required_unless:export_type,ltfu', 'nullable', 'string'],
            'end_date'    => ['required', 'string'],
        ]);

        $start = $this->parseDateInput($request->input('start_date'));
        $end = $this->parseDateInput($request->input('end_date'));

        if ($request->input('export_type') !== 'ltfu' && (!$start || !$end)) {
            throw ValidationException::withMessages([
                'start_date' => 'Please provide valid dates (dd-mm-yyyy).',
                'end_date'   => 'Please provide valid dates (dd-mm-yyyy).',
            ]);
        }

        $startDate = $start ? Carbon::parse($start)->startOfDay() : null;
        $endDate = Carbon::parse($end)->endOfDay();

        if ($startDate && $endDate->lessThan($startDate)) {
            throw ValidationException::withMessages([
                'end_date' => 'End Date must be greater than or equal to Start Date.',
            ]);
        }
        // Keep appointment exports to a 7-day window to fit the grid layout
        if ($request->input('export_type') === 'appointment' && $startDate && $startDate->diffInDays($endDate) > 6) {
            throw ValidationException::withMessages([
                'end_date' => 'Appointment export supports up to 7 days. Please narrow the date range.',
            ]);
        }

        $type = $request->input('export_type');
        $clinicScope = $this->getArtClinicScope();
        $query = ArtFollowup::query();
        $this->applyClinicFilter($query, $clinicScope);

        switch ($type) {
            case 'followup':
                $query->whereBetween('visit_date', [$startDate, $endDate]);
                break;
            case 'appointment':
                $query->whereBetween('next_appointment_date', [$startDate, $endDate]);
                break;
            case 'missed':
                $query->whereBetween('next_appointment_date', [$startDate, $endDate])
                    ->where('next_appointment_date', '<', Carbon::today());
                break;
            case 'ltfu':
                // handled below after query building
                break;
        }

        if ($type === 'ltfu') {
            $cutDate = $endDate;
            $recordsQuery = ArtFollowup::query();
            $this->applyClinicFilter($recordsQuery, $clinicScope);
            $records = $recordsQuery->orderBy('pid')
                ->orderByDesc('visit_date')
                ->get();

            if ($records->isEmpty()) {
                return redirect()
                    ->back()
                    ->withInput($request->only('export_type', 'start_date', 'end_date'))
                    ->with('info', 'No records found for the selected filters.');
            }

            $latestByKey = [];
            foreach ($records as $row) {
                $key = $row->pid ?: ($row->nap_id ?? $row->NAP_ID ?? null);
                if ($key === null) {
                    continue;
                }
                if (!array_key_exists($key, $latestByKey)) {
                    $latestByKey[$key] = $row;
                }
            }

            $ltfuRows = [];
            $latestRows = collect($latestByKey);
            $pidKeys = $latestRows->pluck('pid')->filter()->unique();
            $napKeys = $latestRows->pluck('nap_id')->filter()->unique();
            $fuchiaKeys = $latestRows->pluck('fuchia_id')->filter()->unique();

            $ptConfigsQuery = PtConfig::query();
            $this->applyClinicFilter($ptConfigsQuery, $clinicScope);
            if ($pidKeys->isNotEmpty() || $napKeys->isNotEmpty() || $fuchiaKeys->isNotEmpty()) {
                $ptConfigsQuery->where(function ($query) use ($pidKeys, $napKeys, $fuchiaKeys) {
                    if ($pidKeys->isNotEmpty()) {
                        $query->orWhereIn('Pid', $pidKeys);
                    }
                    if ($napKeys->isNotEmpty()) {
                        $query->orWhereIn('NAP_ID', $napKeys);
                    }
                    if ($fuchiaKeys->isNotEmpty()) {
                        $query->orWhereIn('FuchiaID', $fuchiaKeys);
                    }
                });
            }
            $ptConfigs = $ptConfigsQuery->get();
            $ptConfigsByPid = $ptConfigs->keyBy('Pid');
            $ptConfigsByNap = $ptConfigs->keyBy('NAP_ID');
            $ptConfigsByFuchia = $ptConfigs->keyBy('FuchiaID');
            $resolveConfig = function ($row) use ($ptConfigsByPid, $ptConfigsByNap, $ptConfigsByFuchia) {
                if ($row->pid && $ptConfigsByPid->has($row->pid)) {
                    return $ptConfigsByPid->get($row->pid);
                }
                if ($row->nap_id && $ptConfigsByNap->has($row->nap_id)) {
                    return $ptConfigsByNap->get($row->nap_id);
                }
                if ($row->fuchia_id && $ptConfigsByFuchia->has($row->fuchia_id)) {
                    return $ptConfigsByFuchia->get($row->fuchia_id);
                }
                return null;
            };

            foreach ($latestByKey as $row) {
                $nextAppt = $row->next_appointment_date ? Carbon::parse($row->next_appointment_date) : null;
                if (!$nextAppt) {
                    continue;
                }
                $daysDiff = $nextAppt->diffInDays($cutDate, false);
                if ($daysDiff <= 84) {
                    continue;
                }
                $config = $resolveConfig($row);
                $configData = $config ? $this->decryptConfigRow($config->toArray()) : [];
                $ltfuRows[] = [
                    $config?->id,
                    $row->pid,
                    $row->nap_id ?? $row->NAP_ID ?? '',
                    $row->fuchia_id ?? $row->FuchiaID ?? '',
                    $configData['Name'] ?? '',
                    optional($row->visit_date)->format('d-m-Y'),
                    optional($row->next_appointment_date)->format('d-m-Y'),
                    $daysDiff,
                    $row->patient_status,
                ];
            }

            if (empty($ltfuRows)) {
                return redirect()
                    ->back()
                    ->withInput($request->only('export_type', 'start_date', 'end_date'))
                    ->with('info', 'No records found for the selected filters.');
            }

            $columns = [
                'Inner ID',
                'Pid',
                'NAP ID',
                'Fuchia ID',
                'Name',
                'Last Visit Date',
                'Last Next Appointment',
                'Days Overdue',
                'Patient Status',
            ];

            $fileName = sprintf(
                'art_ltfu_%s.xlsx',
                $cutDate->format('Ymd')
            );

            return Excel::download(new ArtFollowupExport(collect($ltfuRows), $columns), $fileName);
        }

        $records = $query->orderBy('pid')->orderBy('visit_date')->get();
        $pidKeys = $records->pluck('pid')->filter()->unique();
        $napKeys = $records->pluck('nap_id')->filter()->unique();
        $fuchiaKeys = $records->pluck('fuchia_id')->filter()->unique();

        $ptConfigsQuery = PtConfig::query();
        $this->applyClinicFilter($ptConfigsQuery, $clinicScope);
        if ($pidKeys->isNotEmpty() || $napKeys->isNotEmpty() || $fuchiaKeys->isNotEmpty()) {
            $ptConfigsQuery->where(function ($query) use ($pidKeys, $napKeys, $fuchiaKeys) {
                if ($pidKeys->isNotEmpty()) {
                    $query->orWhereIn('Pid', $pidKeys);
                }
                if ($napKeys->isNotEmpty()) {
                    $query->orWhereIn('NAP_ID', $napKeys);
                }
                if ($fuchiaKeys->isNotEmpty()) {
                    $query->orWhereIn('FuchiaID', $fuchiaKeys);
                }
            });
        }
        $ptConfigs = $ptConfigsQuery->get();
        $ptConfigsByPid = $ptConfigs->keyBy('Pid');
        $ptConfigsByNap = $ptConfigs->keyBy('NAP_ID');
        $ptConfigsByFuchia = $ptConfigs->keyBy('FuchiaID');
        $resolveConfig = function ($row) use ($ptConfigsByPid, $ptConfigsByNap, $ptConfigsByFuchia) {
            if ($row->pid && $ptConfigsByPid->has($row->pid)) {
                return $ptConfigsByPid->get($row->pid);
            }
            if ($row->nap_id && $ptConfigsByNap->has($row->nap_id)) {
                return $ptConfigsByNap->get($row->nap_id);
            }
            if ($row->fuchia_id && $ptConfigsByFuchia->has($row->fuchia_id)) {
                return $ptConfigsByFuchia->get($row->fuchia_id);
            }
            return null;
        };

        if ($records->isEmpty() && $type !== 'missed') {
            return redirect()
                ->back()
                ->withInput($request->only('export_type', 'start_date', 'end_date'))
                ->with('info', 'No records found for the selected filters.');
        }

        $visitSequence = [];
        $records->groupBy('pid')->each(function ($items) use (&$visitSequence) {
            $sorted = $items->sortBy('visit_date')->values();
            foreach ($sorted as $index => $visit) {
                $visitSequence[$visit->getKey()] = $index + 1;
            }
        });

        if ($type !== 'followup') {
            if ($type === 'missed') {
                $missedColumns = [
                    'NAP ID',
                    'Fuchia ID',
                    'General ID',
                    'Appointment Date',
                    'Status',
                    'Unplan Visited',
                    'Unplan Visited Date',
                ];

                $todayEnd = Carbon::today()->endOfDay();
                $effectiveEnd = $endDate->lessThan($todayEnd) ? $endDate : $todayEnd;
                if ($effectiveEnd->lessThan($startDate)) {
                    return redirect()
                        ->back()
                        ->withInput($request->only('export_type', 'start_date', 'end_date'))
                        ->with('info', 'No records found for the selected filters.');
                }

                $appointmentsQuery = ArtFollowup::query()
                    ->whereBetween('next_appointment_date', [$startDate, $effectiveEnd]);
                $this->applyClinicFilter($appointmentsQuery, $clinicScope);
                $appointments = $appointmentsQuery
                    ->orderBy('nap_id')
                    ->orderBy('next_appointment_date')
                    ->get();

                if ($appointments->isEmpty()) {
                    return redirect()
                        ->back()
                        ->withInput($request->only('export_type', 'start_date', 'end_date'))
                        ->with('info', 'No records found for the selected filters.');
                }

                $napIds = $appointments->pluck('nap_id')->filter()->unique()->values();
                $visitsQuery = ArtFollowup::query()
                    ->whereIn('nap_id', $napIds)
                    ->whereBetween('visit_date', [$startDate->copy()->subDays(30), $effectiveEnd->copy()->addDays(30)])
                    ->orderBy('visit_date');
                $this->applyClinicFilter($visitsQuery, $clinicScope);
                $visitsByNap = $visitsQuery->get()->groupBy('nap_id');

                $pidKeys = $appointments->pluck('pid')->filter()->unique()->values();
                $fuchiaKeys = $appointments->pluck('fuchia_id')->filter()->unique()->values();
                $ptConfigsQuery = PtConfig::query();
                $this->applyClinicFilter($ptConfigsQuery, $clinicScope);
                if ($pidKeys->isNotEmpty() || $napIds->isNotEmpty() || $fuchiaKeys->isNotEmpty()) {
                    $ptConfigsQuery->where(function ($query) use ($pidKeys, $napIds, $fuchiaKeys) {
                        if ($pidKeys->isNotEmpty()) {
                            $query->orWhereIn('Pid', $pidKeys);
                        }
                        if ($napIds->isNotEmpty()) {
                            $query->orWhereIn('NAP_ID', $napIds);
                        }
                        if ($fuchiaKeys->isNotEmpty()) {
                            $query->orWhereIn('FuchiaID', $fuchiaKeys);
                        }
                    });
                }
                $ptConfigs = $ptConfigsQuery->get();
                $ptConfigsByPid = $ptConfigs->keyBy('Pid');
                $ptConfigsByNap = $ptConfigs->keyBy('NAP_ID');
                $ptConfigsByFuchia = $ptConfigs->keyBy('FuchiaID');
                $resolveMissedConfig = function ($row) use ($ptConfigsByPid, $ptConfigsByNap, $ptConfigsByFuchia) {
                    if ($row->pid && $ptConfigsByPid->has($row->pid)) {
                        return $ptConfigsByPid->get($row->pid);
                    }
                    if ($row->nap_id && $ptConfigsByNap->has($row->nap_id)) {
                        return $ptConfigsByNap->get($row->nap_id);
                    }
                    if ($row->fuchia_id && $ptConfigsByFuchia->has($row->fuchia_id)) {
                        return $ptConfigsByFuchia->get($row->fuchia_id);
                    }
                    return null;
                };

                $missedRows = [];
                foreach ($appointments as $row) {
                    $apptDate = optional($row->next_appointment_date)->toDateString();
                    if (!$apptDate) {
                        continue;
                    }
                    $napId = $row->nap_id ?? $row->NAP_ID;
                    $visits = $visitsByNap->get($napId, collect());
                    $sameDayVisit = $visits->first(fn ($v) => optional($v->visit_date)->toDateString() === $apptDate);
                    if ($sameDayVisit) {
                        continue;
                    }

                    $differentVisit = $visits
                        ->filter(fn ($v) => optional($v->visit_date)->toDateString() !== $apptDate)
                        ->filter(function ($v) use ($apptDate) {
                            return abs(Carbon::parse($apptDate)->diffInDays($v->visit_date)) <= 7;
                        })
                        ->sortBy(fn ($v) => abs(Carbon::parse($apptDate)->diffInDays($v->visit_date)))
                        ->first();

                    $config = $resolveMissedConfig($row);
                    $configData = $config ? $this->decryptConfigRow($config->toArray()) : [];
                    $fuchia = $configData['FuchiaID'] ?? ($config?->{'FuchiaID'} ?? ($row->fuchia_id ?? $row->FuchiaID ?? ''));
                    $generalId = $config?->Pid ?? $row->pid;

                    $missedRows[] = [
                        $napId,
                        $fuchia,
                        $generalId,
                        $this->formatDateOutput($row->next_appointment_date),
                        'missing',
                        $differentVisit ? 'TRUE' : '',
                        $differentVisit ? $this->formatDateOutput($differentVisit->visit_date) : '',
                    ];
                }

                $fileName = sprintf(
                    'art_%s_%s_%s.xlsx',
                    $type,
                    $startDate->format('Ymd'),
                    $endDate->format('Ymd')
                );

                return Excel::download(new ArtFollowupExport(collect($missedRows), $missedColumns), $fileName);
            }

            $columns = [
                'Inner ID',
                'Pid',
                'NAP ID',
                'Fuchia ID',
                'Name',
                'Agey',
                'Sex',
                'Date',
                'Category',
                'Patient Status',
            ];

            $simpleRows = $records->map(function ($row) use ($type, $resolveConfig) {
                $config = $resolveConfig($row);
                $configData = $config ? $this->decryptConfigRow($config->toArray()) : [];

                $name = $configData['Name'] ?? '';
                $napId = $configData['NAP_ID'] ?? ($config?->{'NAP_ID'} ?? ($row->nap_id ?? $row->NAP_ID ?? ''));
                $fuchia = $configData['FuchiaID'] ?? ($config?->{'FuchiaID'} ?? ($row->fuchia_id ?? $row->FuchiaID ?? ''));
                $gender = $row->sex ?: ($configData['Gender'] ?? null);

                $dateValue = match ($type) {
                    'appointment', 'missed' => optional($row->next_appointment_date)->format('d-m-Y'),
                    'ltfu' => optional($row->visit_date)->format('d-m-Y'),
                    default => null,
                };

                $category = match ($type) {
                    'appointment' => 'Appointment',
                    'missed'      => 'Missed Appointment',
                    default       => '',
                };

                return [
                    $config?->id,
                    $row->pid,
                    $napId,
                    $fuchia,
                    $name,
                    $row->age,
                    $gender,
                    $dateValue,
                    $category,
                    $row->patient_status,
                ];
            });

            if ($type === 'appointment') {
                ['headings' => $headings, 'rows' => $gridRows] = $this->buildAppointmentGrid($records, $startDate, $endDate, $clinicScope);

                $fileName = sprintf(
                    'art_%s_grid_%s_%s.xlsx',
                    $type,
                    $startDate->format('Ymd'),
                    $endDate->format('Ymd')
                );

                return Excel::download(new ArtFollowupExport(collect($gridRows), $headings), $fileName);
            }

            $fileName = sprintf(
                'art_%s_%s_%s.xlsx',
                $type,
                $startDate->format('Ymd'),
                $endDate->format('Ymd')
            );

            return Excel::download(new ArtFollowupExport($simpleRows, $columns), $fileName);
        }

        $exportRows = $records->map(function ($row) use (&$visitSequence, $resolveConfig) {
            $config = $resolveConfig($row);
            $configData = $config ? $this->decryptConfigRow($config->toArray()) : [];
            $formatBool = fn($value) => $value ? 'Yes' : 'No';
            $visitOrder = $visitSequence[$row->getKey()] ?? 1;

            $name = $configData['Name'] ?? '';
            $phone = $configData['Phone'] ?? '';
            $napId = $configData['NAP_ID'] ?? ($config?->{'NAP_ID'} ?? ($row->nap_id ?? $row->NAP_ID ?? ''));
            $fuchia = $configData['FuchiaID'] ?? ($config?->{'FuchiaID'} ?? ($row->fuchia_id ?? $row->FuchiaID ?? ''));
            $generalId = $config?->Pid ?? $row->pid;
            $clinicId = $row->clinic_id ?? $row->Clinic_ID ?? ($config?->{'Clinic_ID'} ?? null);
            $mainRisk = $row->main_risk ?: ($configData['Main Risk'] ?? null);
            $gender = $row->sex ?: ($configData['Gender'] ?? null);

            return [
                $config?->id,
                $clinicId,
                $napId,
                $fuchia,
                $generalId,
                $name,
                $phone,
                optional($row->visit_date)->format('d-m-Y'),
                $row->age,
                $gender,
                $mainRisk,
                optional($row->art_started_date)->format('d-m-Y'),
                $row->art_regime,
                $row->patient_status,
                $formatBool($row->fp_service),
                $formatBool($row->sti_service),
                $formatBool($row->counseling_service),
                $formatBool($row->hepc_tx),
                optional($row->next_appointment_date)->format('d-m-Y'),
                $formatBool($row->refer_to_mam),
                $row->reason_of_ref,
                $row->transfer_out_center,
                optional($row->vl_test_date)->format('d-m-Y'),
                $row->vl_copies_ml,
                $row->vl_result,
                $formatBool($row->oi_tb),
                $formatBool($row->oi_pcp),
                $formatBool($row->oi_mac),
                $formatBool($row->oi_toxo),
                $formatBool($row->oi_crypto),
                $formatBool($row->oi_penic),
                $formatBool($row->oi_cmv),
                $formatBool($row->oi_pml),
                $formatBool($row->oi_other),
                $row->oi_other_specify,
                '', // Prev NAPP placeholder
                $this->formatVisitOrdinal($visitOrder),
                max(0, $visitOrder - 1),
                $row->remarks,
            ];
        });

        $fileName = sprintf(
            'art_%s_%s_%s.xlsx',
            $type,
            $startDate->format('Ymd'),
            $endDate->format('Ymd')
        );

        return Excel::download(new ArtFollowupExport($exportRows), $fileName);
    }

    public function previewAppointments(Request $request)
    {
        $payload = $request->validate([
            'start_date' => ['required', 'string'],
            'end_date'   => ['required', 'string'],
        ]);

        $start = $this->parseDateInput($payload['start_date']);
        $end = $this->parseDateInput($payload['end_date']);

        if (!$start || !$end) {
            return response()->json(['error' => 'Please provide valid dates (dd-mm-yyyy).'], 422);
        }

        $startDate = Carbon::parse($start)->startOfDay();
        $endDate = Carbon::parse($end)->endOfDay();

        if ($endDate->lessThan($startDate)) {
            return response()->json(['error' => 'End Date must be greater than or equal to Start Date.'], 422);
        }

        if ($startDate->diffInDays($endDate) > 6) {
            return response()->json(['error' => 'Appointment preview supports up to 7 days. Please narrow the date range.'], 422);
        }

        $clinicScope = $this->getArtClinicScope();
        $recordsQuery = ArtFollowup::whereBetween('next_appointment_date', [$startDate, $endDate]);
        $this->applyClinicFilter($recordsQuery, $clinicScope);
        $records = $recordsQuery
            ->orderBy('nap_id')
            ->orderBy('next_appointment_date')
            ->get();

        if ($records->isEmpty()) {
            return response()->json(['message' => 'No appointments found for the selected range.', 'headings' => [], 'rows' => []]);
        }

        ['headings' => $headings, 'rows' => $rows] = $this->buildAppointmentGrid($records, $startDate, $endDate, $clinicScope);

        return response()->json([
            'headings' => $headings,
            'rows' => $rows,
            'count' => count($rows),
        ]);
    }

    protected function buildAppointmentGrid($records, Carbon $startDate, Carbon $endDate, $clinicScope = null): array
    {
        $appointments = [];
        $napIds = $records->map(fn ($r) => $r->nap_id ?? $r->NAP_ID)->filter()->unique()->values();
        $pidKeys = $records->pluck('pid')->filter()->unique()->values();
        $fuchiaKeys = $records->pluck('fuchia_id')->filter()->unique()->values();
        $nearbyMin = $startDate->copy()->subDays(7);
        $nearbyMax = $endDate->copy()->addDays(7);
        $nearbyVisitsQuery = ArtFollowup::query()
            ->whereIn('nap_id', $napIds)
            ->whereBetween('visit_date', [$nearbyMin, $nearbyMax])
            ->orderBy('visit_date');
        $this->applyClinicFilter($nearbyVisitsQuery, $clinicScope);
        $nearbyVisits = $nearbyVisitsQuery->get()
            ->groupBy('nap_id');

        $ptConfigsQuery = PtConfig::query();
        $this->applyClinicFilter($ptConfigsQuery, $clinicScope);
        if ($pidKeys->isNotEmpty() || $napIds->isNotEmpty() || $fuchiaKeys->isNotEmpty()) {
            $ptConfigsQuery->where(function ($query) use ($pidKeys, $napIds, $fuchiaKeys) {
                if ($pidKeys->isNotEmpty()) {
                    $query->orWhereIn('Pid', $pidKeys);
                }
                if ($napIds->isNotEmpty()) {
                    $query->orWhereIn('NAP_ID', $napIds);
                }
                if ($fuchiaKeys->isNotEmpty()) {
                    $query->orWhereIn('FuchiaID', $fuchiaKeys);
                }
            });
        }
        $ptConfigs = $ptConfigsQuery->get();
        $ptConfigsByPid = $ptConfigs->keyBy('Pid');
        $ptConfigsByNap = $ptConfigs->keyBy('NAP_ID');
        $ptConfigsByFuchia = $ptConfigs->keyBy('FuchiaID');
        $resolveConfig = function ($row) use ($ptConfigsByPid, $ptConfigsByNap, $ptConfigsByFuchia) {
            if ($row->pid && $ptConfigsByPid->has($row->pid)) {
                return $ptConfigsByPid->get($row->pid);
            }
            if ($row->nap_id && $ptConfigsByNap->has($row->nap_id)) {
                return $ptConfigsByNap->get($row->nap_id);
            }
            if ($row->fuchia_id && $ptConfigsByFuchia->has($row->fuchia_id)) {
                return $ptConfigsByFuchia->get($row->fuchia_id);
            }
            return null;
        };

        foreach ($records as $row) {
            $napId = $row->nap_id ?? $row->NAP_ID;
            $apptDate = optional($row->next_appointment_date)->toDateString();
            if (!$apptDate) {
                continue;
            }

            $entry = [
                'nap' => $napId,
                'fuchia' => $row->fuchia_id ?? $row->FuchiaID ?? null,
                'general' => $row->pid,
                'visit_date' => $this->formatDateOutput($row->visit_date),
                'next_appt' => $this->formatDateOutput($row->next_appointment_date ?? $apptDate),
                'unplan_flag' => '',
                'unplan_date' => '',
            ];

            $config = $resolveConfig($row);
            if ($config) {
                $entry['general'] = $config->Pid ?? $entry['general'];
                $entry['fuchia'] = $config->{'FuchiaID'} ?? $entry['fuchia'];
            }

            $relatedVisits = $nearbyVisits->get($napId, collect());
            if ($relatedVisits->isNotEmpty()) {
                $closest = $relatedVisits
                    ->filter(fn ($v) => optional($v->visit_date)->toDateString() !== $apptDate)
                    ->sortBy(function ($v) use ($apptDate) {
                        return abs(Carbon::parse($apptDate)->diffInDays($v->visit_date));
                    })
                    ->first();
                if ($closest) {
                    $entry['unplan_flag'] = 'TRUE';
                    $entry['unplan_date'] = $this->formatDateOutput($closest->visit_date);
                }
            }

            $appointments[] = $entry;
        }

        usort($appointments, function ($a, $b) {
            return strcmp($a['next_appt'] ?? '', $b['next_appt'] ?? '');
        });

        $headings = [
            'NAP ID',
            'Fuchia ID',
            'General ID',
            'Visit Date',
            'Next Appointment Date',
            'Unplan Visited',
            'Unplan Visited Date',
        ];

        $rows = array_map(fn ($entry) => [
            $entry['nap'] ?? '',
            $entry['fuchia'] ?? '',
            $entry['general'] ?? '',
            $entry['visit_date'] ?? '',
            $entry['next_appt'] ?? '',
            $entry['unplan_flag'] ?? '',
            $entry['unplan_date'] ?? '',
        ], $appointments);

        return ['headings' => $headings, 'rows' => $rows];
    }

    protected function formatDateOutput($value): string
    {
        if (!$value) {
            return '';
        }
        try {
            return Carbon::parse($value)->format('d-m-Y');
        } catch (\Throwable $e) {
            return '';
        }
    }

    protected function searchID(string $cid, int $targetClinic = 0, string $identifierType = 'pid'): ?array
    {
        $priorityDBs = [
            'MAM_SDG',
            'MAM_A',
            'MAM_B',
            'MAM_C1',
            'MAM_SPT',
            'MAM_TL',
            'MAM_TBZY',
            'MAM_TAZE',
            'MAM_PTO',
            'MAM_PHAKANT',
            'MAM_WINKA',
            'MAM_C2',
        ];

        $model = app()->make(Patients::class);
        $identifierType = strtolower($identifierType);
        $column = $identifierType === 'fuchia_id' ? 'FuchiaID' : 'Pid';

        foreach ($priorityDBs as $conn) {
            try {
                $model->setConnection($conn);
                if (!Schema::connection($conn)->hasTable($model->getTable())) continue;

                $row = $model->where($column, $cid)->first();
                if ($row) {
                    $this->decryptPatient($row);
                    $arr = $row->toArray();

                    // ✅ Age Calculation
                    try {
                        $arr = Export_age::Export_general(
                            $arr,
                            request('vDate') ?? now()->toDateString(),
                            $arr['Dob'] ?? ($arr['Date of Birth'] ?? null),
                            $arr
                        );
                    } catch (\Throwable $e) {
                        Log::warning("Export_age failed: " . $e->getMessage());
                    }

                    // ✅ ATTACH Lab_Rpr for clinic DB
                    try {
                        $rpr = Lab_Rpr::on($conn)
                            ->where('CID', $cid)
                            ->orderBy('vdate', 'desc')
                            ->first();

                        $arr['Lab_RPR'] = $rpr ? $rpr->toArray() : null;
                    } catch (\Throwable $e) {
                        Log::warning("Lab_Rpr fetch failed ($conn): " . $e->getMessage());
                        $arr['Lab_RPR'] = null;
                    }

                    $arr['DB_Location'] = 'Clinics';
                    return $arr;
                }
            } catch (\Throwable $e) {
                Log::warning("searchID {$conn} failed: " . $e->getMessage());
            }
        }

        // ✅ OFFICE LAB SECTION
        try {
            $model->setConnection('OfficeLab');
            if (Schema::connection('OfficeLab')->hasTable($model->getTable())) {
                $row = $model->where($column, $cid)->first();
                if ($row) {
                    $this->decryptPatient($row);
                    $arr = $row->toArray();

                    // ✅ Age Calculation
                    try {
                        $arr = Export_age::Export_general(
                            $arr,
                            request('vDate') ?? now()->toDateString(),
                            $arr['Dob'] ?? ($arr['Date of Birth'] ?? null),
                            $arr
                        );
                    } catch (\Throwable $e) {
                        Log::warning("Export_age failed: " . $e->getMessage());
                    }

                    // ✅ ATTACH Lab_Rpr for OfficeLab DB
                    try {
                        $rpr = Lab_Rpr::on('OfficeLab')
                            ->where('CID', $cid)
                            ->orderBy('vdate', 'desc')
                            ->first();

                        $arr['Lab_RPR'] = $rpr ? $rpr->toArray() : null;
                    } catch (\Throwable $e) {
                        Log::warning("Lab_Rpr fetch failed (OfficeLab): " . $e->getMessage());
                        $arr['Lab_RPR'] = null;
                    }

                    $arr['DB_Location'] = 'Office';
                    $arr['Clinic_Target'] = $row['Clinic Code'] ?? null;

                    return $arr;
                }
            }
        } catch (\Throwable $e) {
            Log::warning("searchID OfficeLab failed: " . $e->getMessage());
        }

        return null;
    }

    protected function prepareFallbackPatientData(array $row, array $context = []): array
    {
        $pid = $this->extractRowValue(
            $row,
            ['Pid', 'PID', 'General ID', 'General_ID', 'GeneralID', 'Patient ID', 'Patient_ID', 'PatientID', 'CID', 'ID', 'Id'],
            ['pid', 'generalid']
        );
        $napId = $this->extractRowValue(
            $row,
            ['NAP_ID', 'NAP ID', 'NAPID', 'NapID', 'Nap Id', 'NAP-ID'],
            ['napid']
        );
        $clinicId = $this->extractRowValue(
            $row,
            ['Clinic_ID', 'Clinic ID', 'ClinicID', 'Clinic Id', 'Clinic Code', 'Clinic_Code', 'ClinicCode'],
            ['clinicid', 'cliniccode']
        );
        $nameRaw = $this->extractRowValue(
            $row,
            ['Name', 'Patient Name', 'Patient_Name', 'PatientName', 'Full Name', 'FullName', 'PName'],
            ['name']
        );
        $name = $this->decryptValue($nameRaw);
        $fuchiaRaw = $this->extractRowValue(
            $row,
            ['FuchiaID', 'Fuchia_ID', 'Fuchia ID', 'FuchsiaID', 'Fuchsia_ID', 'Fuchsia ID'],
            ['fuchia', 'fuchsia']
        );
        $fuchiaId = $this->decryptValue($fuchiaRaw);
        $phoneRaw = $this->extractRowValue(
            $row,
            ['Phone', 'Phone1', 'Phone2', 'Phone3', 'Phone No', 'PhoneNo', 'Mobile', 'Telephone'],
            ['phone', 'mobile', 'tel']
        );
        $phone = $this->decryptValue($phoneRaw);
        $dobRaw = $this->extractRowValue(
            $row,
            ['Date of Birth', 'Date Of Birth', 'Date_of_Birth', 'DateOfBirth', 'DOB', 'Dob', 'Birth Date', 'Birth_Date', 'BirthDate'],
            ['dob', 'birthdate', 'birth']
        );
        $dob = $this->decryptValue($dobRaw);
        $age = $this->extractRowValue($row, ['Age', 'Agey', 'AgeY', 'Register Agey', 'RegisterAgey']);
        $regDate = $this->extractRowValue(
            $row,
            ['Reg Date', 'Reg_Date', 'RegDate', 'Register Date', 'Register_Date', 'Registration Date', 'Registration_Date'],
            ['regdate', 'registerdate', 'registrationdate']
        );
        $rawRisk = $this->extractRowValue(
            $row,
            ['Main Risk', 'Main_Risk', 'MainRisk', 'Main Risk Type', 'Main_Risk_Type', 'MainRiskType'],
            ['mainrisk']
        );
        $mainRisk = $this->decryptValue($rawRisk, 'light');
        if ($mainRisk !== null && is_numeric($mainRisk) && $rawRisk && !is_numeric($rawRisk)) {
            $mainRisk = $rawRisk;
        }
        $genderRaw = $this->extractRowValue($row, ['Gender', 'Sex', 'gender', 'sex'], ['gender', 'sex']);
        $gender = $this->decryptValue($genderRaw, 'light');

        $payload = array_filter([
            'Pid'            => $pid,
            'NAP_ID'         => $napId,
            'Clinic_ID'      => $clinicId,
            'Name'           => $name,
            'FuchiaID'       => $fuchiaId,
            'Phone'          => $phone,
            'ID'             => $this->extractRowValue($row, ['ID', 'Id']),
            'Age'            => $age,
            'Agey'           => $this->extractRowValue($row, ['Agey', 'AgeY']),
            'Date of Birth'  => $dob,
            'Date Of Birth'  => $dob,
            'Date_of_Birth'  => $dob,
            'Gender'         => $gender,
            'Main Risk'      => $mainRisk,
            'Reg Date'       => $regDate,
            'DB_Location'    => $row['DB_Location'] ?? 'patients',
        ], fn ($value) => $value !== null);

        if (empty($payload)) {
            Log::warning('External patient lookup returned empty payload', [
                'identifier' => $context['identifier'] ?? null,
                'identifier_type' => $context['identifier_type'] ?? null,
                'db_location' => $row['DB_Location'] ?? 'patients',
                'row_keys' => array_keys($row),
            ]);
        }

        return $payload;
    }

    protected function normalizeRowKey(string $key): string
    {
        return strtolower(preg_replace('/[^a-z0-9]/i', '', $key));
    }

    protected function extractRowValue(array $row, array $aliases, array $contains = [])
    {
        foreach ($aliases as $alias) {
            if (array_key_exists($alias, $row)) {
                return $row[$alias];
            }
        }
        $normalized = [];
        foreach ($row as $key => $value) {
            $normalized[$this->normalizeRowKey($key)] = $value;
        }
        foreach ($aliases as $alias) {
            $needle = $this->normalizeRowKey($alias);
            if (array_key_exists($needle, $normalized)) {
                return $normalized[$needle];
            }
        }
        foreach ($contains as $fragment) {
            $needle = $this->normalizeRowKey($fragment);
            if ($needle === '') {
                continue;
            }
            foreach ($normalized as $key => $value) {
                if (Str::contains($key, $needle)) {
                    return $value;
                }
            }
        }
        return null;
    }

    protected function normalizeDemographicPayload(?array $data): ?array
    {
        if (!$data) {
            return $data;
        }

        if (isset($data['Gender'])) {
            $data['Gender'] = $this->formatGenderValue($data['Gender']);
        }
        if (isset($data['Main Risk'])) {
            $source = $data['DB_Location'] ?? 'pt_config';
            $alreadyDecrypted = $source === 'pt_config';
            Log::debug('Normalizing main risk', [
                'raw' => $data['Main Risk'],
                'source' => $data['DB_Location'] ?? 'pt_config',
            ]);
            $data['Main Risk'] = $this->formatRiskValue($data['Main Risk'], $alreadyDecrypted);
            Log::debug('Normalized main risk', ['value' => $data['Main Risk']]);
        }

        return $data;
    }

    protected function formatGenderValue($value): ?string
    {
        if ($value === null || $value === '') {
            return $value;
        }

        $normalize = static function ($candidate): ?string {
            if ($candidate === null || $candidate === '') {
                return null;
            }

            $trimmed = trim((string) $candidate);
            if ($trimmed === '') {
                return null;
            }

            $upper = strtoupper($trimmed);

            if (is_numeric($trimmed)) {
                return match ((int) $trimmed) {
                    0       => 'Male',
                    1       => 'Female',
                    2       => 'Other',
                    default => null,
                };
            }

            return match ($upper) {
                'M', 'MALE'   => 'Male',
                'F', 'FEMALE' => 'Female',
                'O', 'OTHER'  => 'Other',
                default       => null,
            };
        };

        $raw = trim((string) $value);
        $mapped = $normalize($raw);
        if ($mapped) {
            return $mapped;
        }

        try {
            $decoded = $this->decryptLightValue($raw);
            $mapped = $normalize($decoded);
            if ($mapped) {
                return $mapped;
            }
        } catch (\Throwable $e) {
            // Plain, non-encrypted gender strings are handled by the raw mapping above.
        }

        return preg_match('/^\d+$/', $raw) ? null : ucfirst(strtolower($raw));
    }

    protected function findLatestPositiveViralLoadVisit($query): ?ArtFollowup
    {
        return $query
            ->whereNotNull('vl_copies_ml')
            ->orderByDesc('visit_date')
            ->orderByDesc('updated_at')
            ->get()
            ->first(fn ($visit) => $this->isPositiveViralLoadResult($visit->vl_copies_ml));
    }

    protected function isPositiveViralLoadResult($value): bool
    {
        if ($value === null) {
            return false;
        }

        $normalized = str_replace(',', '', trim((string) $value));
        if ($normalized === '' || !preg_match('/^(?:[<>]=?)?\s*(\d+(?:\.\d+)?)$/', $normalized, $matches)) {
            return false;
        }

        return (float) $matches[1] > 0;
    }

    protected function decryptLightValue($value): string
    {
        if ($value === null || $value === '') {
            return '';
        }

        $raw = trim((string) $value);
        $decoded = $this->decryptLightValueLocally($raw);
        if ($decoded !== '') {
            return $decoded;
        }

        try {
            $encrypter = Crypt::getFacadeRoot();
            if ($encrypter && method_exists($encrypter, 'decrypt_light')) {
                return trim((string) $encrypter->decrypt_light($raw, 'General'));
            }
        } catch (\Throwable $e) {
            // Caller falls back to the raw value when this legacy cipher is unavailable.
        }

        return $raw;
    }

    protected function decryptLightValueLocally(string $value): string
    {
        if ($value === '' || !ctype_digit($value)) {
            return '';
        }

        $chars = str_split($value);
        $length = count($chars);
        if ($length < 3) {
            return '';
        }

        $pairCount = $length % 2 === 0
            ? (int) (($chars[$length - 2] ?? '0') . ($chars[$length - 1] ?? '0'))
            : (int) ($chars[$length - 1] ?? '0');

        $expectedLength = ($pairCount * 2) + ($length % 2 === 0 ? 2 : 1);
        if ($pairCount <= 0 || $expectedLength !== $length) {
            return '';
        }

        $map = [
            '11' => '0', '21' => '1', '41' => '2', '12' => '3', '42' => '4',
            '19' => 'M', '25' => 'F', '16' => 'O',
            '59' => 'a', '32' => 'e', '84' => 'h', '97' => 'l', '92' => 'm', '98' => 'r', '70' => 't',
        ];

        $decoded = '';
        for ($i = 0; $i < $pairCount * 2; $i += 2) {
            $pair = ($chars[$i] ?? '') . ($chars[$i + 1] ?? '');
            if (!array_key_exists($pair, $map)) {
                return '';
            }
            $decoded .= $map[$pair];
        }

        return $decoded;
    }

    protected function formatRiskValue($value, bool $alreadyDecrypted = false): ?string
    {
        if ($value === null) {
            return null;
        }

        $decoded = $alreadyDecrypted ? $value : $this->decryptValue($value, 'light');
        $result = trim((string) ($decoded ?? ''));

        if ($result === '' || is_numeric($result)) {
            $original = trim((string) $value);
            if ($original !== '' && !is_numeric($original)) {
                $result = $original;
            }
        }

        return $result === '' ? null : $result;
    }

    protected function buildLabSummary(?string $pid, ?string $napId): ?string
    {
        $searchValues = array_values(array_filter([$pid, $napId]));
        if (empty($searchValues)) {
            return null;
        }

        try {
            $row = DB::connection('OfficeLab')
                ->table('bio_tests')
                ->select([
                    'vdate',
                    'alat',
                    'ast',
                    'alp',
                    'urea',
                    'creatinine',
                    'hbA1C',
                    'triglycerides',
                    'total_clolesterol',
                    'hdl_clolesterol',
                    'ldl_clolesterol',
                    'complete_picture',
                    'complete_picture_comment',
                ])
                ->whereIn('Pid', $searchValues)
                ->orderByDesc('vdate')
                ->first();
        } catch (\Throwable $e) {
            Log::warning('Unable to fetch lab summary', ['error' => $e->getMessage()]);
            return null;
        }

        $segments = [];
        $dateLabel = null;

        if ($row) {
            $metricsMap = [
                'alat'         => 'ALT',
                'ast'          => 'AST',
                'alp'          => 'ALP',
                'urea'         => 'Urea',
                'creatinine'   => 'Creatinine',
                'hbA1C'        => 'HbA1C',
                'triglycerides'=> 'Triglycerides',
                'total_clolesterol' => 'Total Chol',
                'hdl_clolesterol'   => 'HDL',
                'ldl_clolesterol'   => 'LDL',
            ];

            foreach ($metricsMap as $column => $label) {
                $value = $this->decryptLabValue($row->{$column} ?? null);
                if ($value !== '') {
                    $segments[] = "{$label}: {$value}";
                }
            }

            $cbc = $this->decryptLabValue($row->complete_picture ?? null);
            if ($cbc !== '') {
                $segments[] = 'CBC: ' . $cbc;
            }

            $cbcNote = $this->decryptLabValue($row->complete_picture_comment ?? null);
            if ($cbcNote !== '') {
                $segments[] = 'Note: ' . $cbcNote;
            }

            $dateLabel = $row->vdate
                ? Carbon::parse($row->vdate)->format('d-m-Y')
                : 'Unknown date';
        }

        $rpr = $this->fetchLatestRprResult($searchValues);
        if ($rpr) {
            $parts = [];
            if ($rpr['rdt_result'] !== '') $parts[] = 'RDT ' . $rpr['rdt_result'];
            if ($rpr['rpr_qual'] !== '') $parts[] = 'RPR ' . $rpr['rpr_qual'];
            $titreBits = [];
            if ($rpr['titre_current'] !== '') $titreBits[] = 'Current ' . $rpr['titre_current'];
            if ($rpr['titre_last'] !== '') $titreBits[] = 'Last ' . $rpr['titre_last'];
            if (!empty($titreBits)) {
                $parts[] = 'Titre ' . implode(' / ', $titreBits);
            }
            if (!empty($parts)) {
                $label = $rpr['date'] ? "RPR {$rpr['date']}" : 'RPR';
                $segments[] = "{$label}: " . implode(', ', $parts);
            }
        }

        if (empty($segments)) {
            return null;
        }

        $prefix = $dateLabel ? "Latest lab {$dateLabel}" : 'Lab summary';

        return "{$prefix} — " . implode(', ', $segments);
    }

    protected function decryptLabValue($value): string
    {
        if ($value === null || $value === '') {
            return '';
        }

        $value = (string) $value;

        try {
            return trim((string) Crypt::decrypt_light($value, 'General'));
        } catch (\Throwable $e) {
            return trim($value);
        }
    }

    protected function fetchLatestRprResult(array $searchValues): ?array
    {
        $connections = [
            'MAM_A',
            'MAM_B',
            'MAM_C1',
            'MAM_SDG',
            'MAM_SPT',
            'MAM_TL',
        ];

        foreach ($connections as $conn) {
            try {
                $row = DB::connection($conn)
                    ->table('rprtests')
                    ->selectRaw('vdate, `RDT Result` as rdt_result, `RPR Qualitative` as rpr_qual, `Titre(current)` as titre_current, `Titre(Last)` as titre_last')
                    ->whereIn('Pid', $searchValues)
                    ->orderByDesc('vdate')
                    ->first();
                if ($row) {
                    return [
                        'date'          => $row->vdate ? Carbon::parse($row->vdate)->format('d-m-Y') : null,
                        'rdt_result'    => $this->decryptLabValue($row->rdt_result ?? null),
                        'rpr_qual'      => $this->decryptLabValue($row->rpr_qual ?? null),
                        'titre_current' => $this->decryptLabValue($row->titre_current ?? null),
                        'titre_last'    => $this->decryptLabValue($row->titre_last ?? null),
                        'source'        => $conn,
                    ];
                }
            } catch (\Throwable $e) {
                Log::warning('RPR fetch failed', ['connection' => $conn, 'error' => $e->getMessage()]);
            }
        }

        return null;
    }

    protected function assertUniqueVisit(?int $pid, ?string $napId, ?string $visitDate, ?string $currentVisitId = null): void
    {
        if (!$visitDate) {
            return;
        }

        $query = ArtFollowup::query()->whereDate('visit_date', $visitDate);

        if ($pid !== null) {
            $query->where('pid', $pid);
        } elseif ($napId) {
            $query->where(function ($q) use ($napId) {
                $q->where('NAP_ID', $napId)
                    ->orWhere('nap_id', $napId);
            });
        }

        if ($currentVisitId) {
            $query->where('visit_id', '!=', $currentVisitId);
        }

        if ($query->exists()) {
            $displayDate = Carbon::parse($visitDate)->format('d-m-Y');
            throw ValidationException::withMessages([
                'visit_date' => "A visit already exists for {$displayDate}.",
            ]);
        }
    }

    protected function decryptPatient($row): void
    {
        try {
            if (isset($row['Gender'])) {
                $row['Gender'] = $this->decryptValue($row['Gender'], 'light');
            }
            if (isset($row['Main Risk'])) {
                $row['Main Risk'] = $this->decryptValue($row['Main Risk'], 'light');
            }
        } catch (\Throwable $e) {
            Log::warning("decryptPatient failed: ".$e->getMessage());
        }
    }

    protected function decryptConfigRow(array $config): array
    {
        foreach (['Name','Phone','Township','Address','Quarter','Region','Notes'] as $field) {
            if (isset($config[$field])) {
                $config[$field] = $this->decryptValue($config[$field]);
            }
        }

        foreach (['Name','Phone','Township','Address','Notes'] as $field) {
            if (isset($config[$field])) {
                $config[$field] = $this->decryptValue($config[$field]);
            }
        }

        foreach (['Date Of Birth','Date of Birth','Date_of_Birth','Dob'] as $field) {
            if (isset($config[$field])) {
                $config[$field] = $this->decryptValue($config[$field]);
            }
        }

        if (isset($config['Gender'])) {
            $config['Gender'] = $this->decryptValue($config['Gender'], 'light');
        }
        if (isset($config['Main Risk'])) {
            $config['Main Risk'] = $this->decryptValue($config['Main Risk'], 'light');
        }

        return $config;
    }

    protected function decryptValue($value, string $mode = 'string')
    {
        if ($value === null || $value === '') {
            return $value;
        }

        if ($mode === 'light') {
            try {
                $decoded = Crypt::decrypt_light($value, 'General');
                $raw = is_string($value) ? trim($value) : (string) $value;
                if (is_numeric($decoded) && $raw !== '' && !is_numeric($raw)) {
                    return $value;
                }
                return $decoded;
            } catch (\Throwable $e) {
                return $value;
            }
        }

        try {
            return Crypt::decryptString($value);
        } catch (\Throwable $e) {
            return $value;
        }
    }

    protected function logAuditTrail(string $table, ?string $pid, array $original, array $updated): void
    {
        try {
            $user = Auth::user();
            $summaryOriginal = Str::limit(json_encode($original), 250, '...');
            $summaryUpdated = Str::limit(json_encode($updated), 250, '...');

            Applog::create([
                'User' => (string) ($user?->id ?? 'guest'),
                'Pid' => $pid ?: 0,
                'tableName' => $table,
                'Org_info' => $summaryOriginal,
                'Updated_info' => $summaryUpdated,
            ]);
        } catch (\Throwable $e) {
            Log::warning('Audit trail write failed', ['error' => $e->getMessage()]);
        }
    }

    public function previewMissed(Request $request)
    {
        $payload = $request->validate([
            'start_date' => ['required', 'string'],
            'end_date'   => ['required', 'string'],
        ]);

        $start = $this->parseDateInput($payload['start_date']);
        $end = $this->parseDateInput($payload['end_date']);

        if (!$start || !$end) {
            return response()->json(['error' => 'Please provide valid dates (dd-mm-yyyy).'], 422);
        }

        $startDate = Carbon::parse($start)->startOfDay();
        $endDate = Carbon::parse($end)->endOfDay();
        $todayEnd = Carbon::today()->endOfDay();
        $effectiveEnd = $endDate->lessThan($todayEnd) ? $endDate : $todayEnd;

        if ($effectiveEnd->lessThan($startDate)) {
            return response()->json(['error' => 'End Date must be greater than or equal to Start Date.'], 422);
        }

        // Missed appointments within window: appointment date in range AND no visit on/after appointment date within range
        $clinicScope = $this->getArtClinicScope();
        $appointmentsQuery = ArtFollowup::whereBetween('next_appointment_date', [$startDate, $effectiveEnd]);
        $this->applyClinicFilter($appointmentsQuery, $clinicScope);
        $appointments = $appointmentsQuery
            ->orderBy('nap_id')
            ->orderBy('next_appointment_date')
            ->get();

        if ($appointments->isEmpty()) {
            return response()->json(['message' => 'No appointments found for the selected range.', 'rows' => []]);
        }

        // Preload visits by NAP within window
        $napIds = $appointments->pluck('nap_id')->filter()->unique()->values();
        $visitsQuery = ArtFollowup::whereIn('nap_id', $napIds)
            ->whereBetween('visit_date', [$startDate->copy()->subDays(30), $effectiveEnd->copy()->addDays(30)])
            ->orderBy('visit_date');
        $this->applyClinicFilter($visitsQuery, $clinicScope);
        $visitsByNap = $visitsQuery->get()
            ->groupBy('nap_id');

        $pidKeys = $appointments->pluck('pid')->filter()->unique()->values();
        $fuchiaKeys = $appointments->pluck('fuchia_id')->filter()->unique()->values();
        $ptConfigsQuery = PtConfig::query();
        $this->applyClinicFilter($ptConfigsQuery, $clinicScope);
        if ($pidKeys->isNotEmpty() || $napIds->isNotEmpty() || $fuchiaKeys->isNotEmpty()) {
            $ptConfigsQuery->where(function ($query) use ($pidKeys, $napIds, $fuchiaKeys) {
                if ($pidKeys->isNotEmpty()) {
                    $query->orWhereIn('Pid', $pidKeys);
                }
                if ($napIds->isNotEmpty()) {
                    $query->orWhereIn('NAP_ID', $napIds);
                }
                if ($fuchiaKeys->isNotEmpty()) {
                    $query->orWhereIn('FuchiaID', $fuchiaKeys);
                }
            });
        }
        $ptConfigs = $ptConfigsQuery->get();
        $ptConfigsByPid = $ptConfigs->keyBy('Pid');
        $ptConfigsByNap = $ptConfigs->keyBy('NAP_ID');
        $ptConfigsByFuchia = $ptConfigs->keyBy('FuchiaID');
        $resolveConfig = function ($row) use ($ptConfigsByPid, $ptConfigsByNap, $ptConfigsByFuchia) {
            if ($row->pid && $ptConfigsByPid->has($row->pid)) {
                return $ptConfigsByPid->get($row->pid);
            }
            if ($row->nap_id && $ptConfigsByNap->has($row->nap_id)) {
                return $ptConfigsByNap->get($row->nap_id);
            }
            if ($row->fuchia_id && $ptConfigsByFuchia->has($row->fuchia_id)) {
                return $ptConfigsByFuchia->get($row->fuchia_id);
            }
            return null;
        };

        $rows = [];
        foreach ($appointments as $appt) {
            $apptDate = optional($appt->next_appointment_date)->toDateString();
            $napId = $appt->nap_id ?? $appt->NAP_ID;
            $visits = $visitsByNap->get($napId, collect());

            $sameDayVisit = $visits->first(fn ($v) => optional($v->visit_date)->toDateString() === $apptDate);
            if ($sameDayVisit) {
                continue; // they attended on appointment date, not missed
            }

            $differentVisit = $visits
                ->filter(fn ($v) => optional($v->visit_date)->toDateString() !== $apptDate)
                ->filter(function ($v) use ($apptDate) {
                    return abs(Carbon::parse($apptDate)->diffInDays($v->visit_date)) <= 7;
                })
                ->sortBy(fn ($v) => abs(Carbon::parse($apptDate)->diffInDays($v->visit_date)))
                ->first();

            $config = $resolveConfig($appt);
            $configData = $config ? $this->decryptConfigRow($config->toArray()) : [];
            $fuchia = $configData['FuchiaID'] ?? ($config?->{'FuchiaID'} ?? ($appt->fuchia_id ?? $appt->FuchiaID ?? ''));
            $generalId = $config?->Pid ?? $appt->pid;

            $rows[] = [
                'nap_id' => $napId,
                'fuchia_id' => $fuchia,
                'general_id' => $generalId,
                'appointment_date' => $this->formatDateOutput($appt->next_appointment_date),
                'status' => 'missing',
                'unplan_flag' => $differentVisit ? 'TRUE' : '',
                'unplan_date' => $differentVisit ? $this->formatDateOutput($differentVisit->visit_date) : '',
            ];
        }

        return response()->json([
            'rows' => $rows,
            'count' => count($rows),
        ]);
    }

}