<?php

namespace App\Http\Controllers;

use App\Models\District;
use App\Models\IdType;
use App\Models\NationalStatusCategory;
use App\Models\Parish;
use App\Models\Status;
use App\Models\SubCounty;
use App\Models\Victim;
use App\Models\TimeofDay;
use App\Models\Relation;
use App\Models\Location;
use App\Models\Religion;
use App\Models\AgeGroup;
use App\Models\MaritalStatus;
use App\Models\NationalStatus;
use App\Models\Nationality;
use App\Models\Incident;
use App\Models\IncidentData;
use App\Models\Perpetrator;
use App\Models\Witness;
use App\Models\Service;
use App\Models\Authority;
use App\Models\Action;
use App\Models\CaseStatus;
use App\Models\IncidentStatus;
use App\Models\EducationLevel;
use App\Models\Ocupation;
use App\Models\Place;

use Maatwebsite\Excel\Facades\Excel;
use App\Http\Requests\DataEntry\OfflineImportRequest;
use App\Services\DataEntry\OfflineCaseImportService;
use Symfony\Component\HttpFoundation\StreamedResponse;
use App\Http\Controllers\Controller;
use App\Jobs\PushNsrToRegistry;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

use App\Services\Core\ColumnAwareLocationChainValidator;
use App\Services\Audit\AuditLogService;
use App\Services\Integrations\NsrRegistryService;
use Illuminate\Support\Str;

class DataEntryController extends Controller
{


    function index(){

        /* $time = TimeofDay::all();
        $relation = Relation::all();
        $district = Location::where('locationtype', 2)->get();
        $subcounty = Location::where('locationtype', 4)->get();
        $parish = Location::where('locationtype', 5)->get(); */
        $user = Auth::user();
        $usrditrct = Auth::user()->district;
        $districtarray = explode (",", $usrditrct);


        $district = District::select('id', 'name')->whereIn('id', $districtarray)->get();
        $subcounty = SubCounty::select('name', 'id')->whereIn('districts_id', $districtarray)->get();
        $parish = Parish::where('name', 'id')->whereIn('districts_id', $districtarray)->get();
        if(Empty($usrditrct) || $usrditrct == "*"){
            $district = District::select('id', 'name')->get();
            $subcounty = SubCounty::select('name', 'id')->get();
            $parish = Parish::where('name', 'id')->get();
        }
        $time = TimeofDay::where('status', '1')->get();
        $relation = Relation::where('status', '1')->get();
        $religion = Religion::where('status', '1')->get();
        $agegroup = AgeGroup::where('status', '1')->get();
        $mariage = MaritalStatus::where('status', '1')->orderby('name', 'asc')->get();
        $nationality = Nationality::select('id', 'name')->orderBy('id', 'asc')->get();
        $status = NationalStatus::where('status', '=', '1')->get();
        $incidents = Incident::all();
        $age = AgeGroup::where('status', '1')->get();
        $services = Service::where('status', '1')->get();
        $authorities = Authority::all();
        $statuses = CaseStatus::orderBy('name')->get();
        $pendingPoints = Status::select('id','name')->orderBy('name')->get();
        $pendingCaseStatusIds = CaseStatus::where('name', 'like', '%Pending%')->pluck('id')->map(function ($id) { return (string) $id; })->toArray();
        $education = EducationLevel::where('status', '1')->get();;
        $occupation = Ocupation::where('status', '1')->get();;
        $places = Place::all();
        $idtypes = IdType::where('status', '1')->get();
        $nstatuscategories = NationalStatusCategory::where('status', '1')->get();

    	return view('dataentry.index')->with(['times'=>$time, 'relations'=>$relation, 'districts'=>$district, 'subcounties'=> $subcounty,
            'parishes'=>$parish, 'religions'=>$religion, 'agegroups'=>$agegroup, 'mariage'=>$mariage, 'nationality'=>$nationality,
            'status'=>$status, 'incidents'=>$incidents, 'agegroup'=>$age, 'services'=>$services, 'authorities'=>$authorities,
            'statuses'=>$statuses, 'pendingPoints'=>$pendingPoints, 'pendingCaseStatusIds'=>$pendingCaseStatusIds, 'education'=>$education, 'occupation'=>$occupation, 'places'=>$places, 'idtypes' => $idtypes,
            'nstatuscategories' => $nstatuscategories]);
    }

    public function update(Request $request)
    {
        $survivor = $this->resolveVictimFromReference($request->id);
        $id = $survivor->id;
        $time = TimeofDay::where('status', '1')->get();
        $relation = Relation::where('status', '1')->get();
        $religion = Religion::where('status', '1')->get();
        $agegroup = AgeGroup::where('status', '1')->get();
        $mariage = MaritalStatus::where('status', '1')->orderby('name', 'asc')->get();
        $nationality = Nationality::select('id', 'name')->orderBy('id', 'asc')->get();
        $status = NationalStatus::where('status', '=', '1')->get();
        $incidents = Incident::all();
        $age = AgeGroup::where('status', '1')->get();
        $services = Service::where('status', '1')->get();
        $authorities = Authority::all();
        $statuses = CaseStatus::orderBy('name')->get();
        $pendingPoints = Status::select('id','name')->orderBy('name')->get();
        $pendingCaseStatusIds = CaseStatus::where('name', 'like', '%Pending%')->pluck('id')->map(function ($id) { return (string) $id; })->toArray();
        $education = EducationLevel::where('status', '1')->get();;
        $occupation = Ocupation::where('status', '1')->get();;
        $places = Place::all();
        $idtypes = IdType::where('status', '1')->get();
        $nstatuscategories = NationalStatusCategory::where('status', '1')->get();
        // Fetch existing section records for edit tabs. These are used by the repeatable-row views
        // so users can see and edit previously captured Perpetrator, Witness, Action and Status rows.
        $perpetrators = Perpetrator::where('victimid', $id)->orderBy('id')->get();
        $witnesses = Witness::where('victimid', $id)->orderBy('id')->get();
        $actions = Action::where('victim_id', $id)->orderBy('id')->get();
        $incidentStatuses = IncidentStatus::where('victimid', $id)->orderBy('id')->get();

        // Get logged-in user
        $user = Auth::user();
        $usrditrct = $user->district;
        $districtarray = explode(",", $usrditrct);

        $districts = District::select('id', 'name')->whereIn('id', $districtarray)->get();
        $subcounties = SubCounty::select('name', 'id')->whereIn('districts_id', $districtarray)->get();
        $parishes = Parish::where('name', 'id')->whereIn('districts_id', $districtarray)->get();
        if(Empty($usrditrct) || $usrditrct == "*"){
            $districts = District::select('id', 'name')->get();
            $subcounties = SubCounty::select('name', 'id')->get();
            $parishes = Parish::where('name', 'id')->get();
        }

        return view('dataentry.update', compact( 'districts', 'subcounties', 'parishes', 'pendingPoints', 'pendingCaseStatusIds', 'survivor', 'perpetrators', 'witnesses', 'actions', 'incidentStatuses'))
            ->with(['times'=>$time, 'relations'=>$relation, 'religions'=>$religion, 'agegroups'=>$agegroup, 'mariage'=>$mariage, 'nationality'=>$nationality,
            'status'=>$status, 'incidents'=>$incidents, 'agegroup'=>$age, 'services'=>$services, 'authorities'=>$authorities,
            'statuses'=>$statuses, 'pendingPoints'=>$pendingPoints, 'pendingCaseStatusIds'=>$pendingCaseStatusIds, 'education'=>$education, 'occupation'=>$occupation, 'places'=>$places, 'idtypes' => $idtypes,
            'nstatuscategories' => $nstatuscategories]);
    }

    function list(Request $request){
        $validated = $request->validate([
            'start_date' => ['nullable', 'date'],
            'end_date' => ['nullable', 'date', 'after_or_equal:start_date'],
            'perPage' => ['nullable', 'integer', 'min:1', 'max:5000'],
            'page' => ['nullable', 'integer', 'min:1', 'max:100000'],
        ]);

        $start_date = !empty($validated['start_date']) ? date("Y-m-d", strtotime($validated['start_date'])) : null;
        $end_date = !empty($validated['end_date']) ? date("Y-m-d", strtotime($validated['end_date'])) : null;
        $perPage = (int) ($validated['perPage'] ?? 10);
        $user = Auth::user();
        $scopeService = app(\App\Services\AccessControl\DataScopeService::class);

        $baseQuery = DB::table('victim')
            ->select('victim.*', 'gender.name as genderValue', 'nationality.name as nationality', 'national_status.name as national_status',
                'district.name as district', 'subbcounty.name as subbcounty','parish.name as parish', 'users.name as created_by', 'users.email as email', 'user_roles.name as urole')
            ->leftJoin('gender', 'victim.gender', '=', 'gender.id')
            ->leftJoin('nationality as nationality', 'victim.nationality', '=', 'nationality.id')
            ->leftJoin('national_status as national_status', 'victim.nationality_status', '=', 'national_status.id')
            ->leftJoin('districts as district', 'victim.districtid', '=', 'district.id')
            ->leftJoin('sub_counties as subbcounty', 'victim.subcountyid', '=', 'subbcounty.id')
            ->leftJoin('parishes as parish', 'victim.parishid', '=', 'parish.id')
            ->leftJoin('users', 'victim.created_by', '=', 'users.id')
            ->leftJoin('user_roles', 'users.userrole', '=', 'user_roles.id')
            ->when($start_date, function ($query) use ($start_date) {
                return $query->where('interview_date', '>=', $start_date);
            })
            ->when($end_date, function ($query) use ($end_date) {
                return $query->where('interview_date', '<=', $end_date);
            });

        $baseQuery = $scopeService->applyToVictimTableQuery($baseQuery, $user, 'victim');

        $victims = $baseQuery->orderByDesc('victim.id')->paginate($perPage)->onEachSide(5);

        $countQuery = DB::table('victim');
        $countQuery = $scopeService->applyToVictimTableQuery($countQuery, $user, 'victim');
        $totalCount = $countQuery->count();

        $availablePerPageOptions = [10, 25, 50];
        if ($totalCount > 50) { $availablePerPageOptions[] = 100; }
        if ($totalCount > 100) { $availablePerPageOptions[] = 200; }
        if ($totalCount > 200) { $availablePerPageOptions[] = 500; }
        if ($totalCount > 500) { $availablePerPageOptions[] = 1000; }
        if ($totalCount > 1000) { $availablePerPageOptions[] = 5000; }

        return view('dataentry.list', compact('victims','perPage', 'availablePerPageOptions'));
    }

    function profiledcases(Request $request){
        return $this->list($request);
    }

    function listdetails(){
        $request = request();
        $victim = Victim::all();
        $resultdetails = array(); $key =1;
        foreach ($victim as $victim) {
            $resultdetails [$key] = $victim['crb_number'];;
            $key++;
        }
        echo json_encode(array_values($resultdetails));
    }

    function import(){
        return view('dataentry.import');
    }

    public function processOfflineImport(OfflineImportRequest $request, OfflineCaseImportService $service)
    {
        \Log::info('Offline import POST received.', [
            'user_id' => optional(auth()->user())->id,
            'has_file' => $request->hasFile('offline_file'),
            'mode' => $request->input('mode'),
            'file_name' => $request->hasFile('offline_file') ? $request->file('offline_file')->getClientOriginalName() : null,
            'file_size' => $request->hasFile('offline_file') ? $request->file('offline_file')->getSize() : null,
        ]);

        if (!$request->hasFile('offline_file')) {
            return view('dataentry.import', [
                'message' => null,
                'importSummaryJson' => null,
            ])->withErrors(['offline_file' => 'No offline import file was received. Please select the completed template and try again.']);
        }

        $uploadedFile = $request->file('offline_file');

        if (!$uploadedFile || !$uploadedFile->isValid()) {
            return view('dataentry.import', [
                'message' => null,
                'importSummaryJson' => null,
            ])->withErrors(['offline_file' => 'The offline import file upload failed before processing. Please check file size and retry.']);
        }

        try {
            $summary = $service->process($uploadedFile, $request->input('mode'));

            $message = $request->input('mode') === 'validate_only'
                ? 'Validation completed. Review the summary before final import.'
                : 'Offline import completed. Review the summary below.';

            $summaryJson = json_encode($summary, JSON_PRETTY_PRINT);

            \Log::info('Offline import completed.', [
                'user_id' => optional(auth()->user())->id,
                'mode' => $request->input('mode'),
                'summary_size' => strlen((string) $summaryJson),
            ]);

            // Return the view directly instead of redirecting with a large flash payload.
            // Large import summaries can exceed cookie/session limits and make the page look like it only refreshed.
            return view('dataentry.import', [
                'message' => $message,
                'importSummaryJson' => $summaryJson,
                'importSummary' => $summary,
                'selectedMode' => $request->input('mode'),
            ]);
        } catch (\Throwable $exception) {
            \Log::error('Offline import processing failed.', [
                'user_id' => optional(auth()->user())->id,
                'message' => $exception->getMessage(),
                'file' => $exception->getFile(),
                'line' => $exception->getLine(),
            ]);

            return view('dataentry.import', [
                'message' => null,
                'importSummaryJson' => null,
                'selectedMode' => $request->input('mode'),
            ])->withErrors(['offline_file' => 'The offline import file could not be processed: '.$exception->getMessage()]);
        }
    }

    public function downloadOfflineImportTemplate(OfflineCaseImportService $service)
    {
        $headers = $service->headings();
        $sample = [
            'GBV/00/00/001', date('Y-m-d'), 'Morning', 'KAMPALA', 'CENTRAL DIVISION', 'KAMPALA CENTRAL',
            'Village Name', 'GBV/00/00/001', 'Female', 'Uganda', '1995-01-01', 'Resident',
            '0', 'Christianity', 'Single', '0', '0', 'Primary', 'Business', '0', 'Rape,Physical Assault', date('Y-m-d'),
            'Morning', 'Home', 'Survivor', '0700000000', 'Brief incident notes'
        ];

        $variables = [
            ['Variable', 'Accepted Values / Guidance'],
            ['district', DB::table('districts')->orderBy('name')->pluck('name')->implode(', ')],
            ['subcounty', DB::table('sub_counties')->orderBy('name')->limit(250)->pluck('name')->implode(', ')],
            ['parish', DB::table('parishes')->orderBy('name')->limit(250)->pluck('name')->implode(', ')],
            ['gender', DB::table('gender')->orderBy('name')->pluck('name')->implode(', ')],
            ['nationality', DB::table('nationality')->orderBy('name')->pluck('name')->implode(', ')],
            ['nationality_status', DB::table('national_status')->orderBy('name')->pluck('name')->implode(', ')],
            ['religion', DB::table('religion')->orderBy('name')->pluck('name')->implode(', ')],
            ['marital_status', DB::table('marital_status')->orderBy('name')->pluck('name')->implode(', ')],
            ['education', DB::table('education_levels')->orderBy('name')->pluck('name')->implode(', ')],
            ['occupation', DB::table('ocupations')->orderBy('name')->limit(250)->pluck('name')->implode(', ')],
            ['incident_ids / incident_names', DB::table('incidents')->orderBy('name')->pluck('name')->implode(', ')],
            ['date format', 'YYYY-MM-DD'],
            ['multiple incidents', 'Use comma-separated incident names e.g. Rape, Physical Assault'],
        ];

        return new StreamedResponse(function () use ($headers, $sample, $variables) {
            echo "<?xml version=\"1.0\"?>
";
            echo "<?mso-application progid=\"Excel.Sheet\"?>
";
            echo "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">";
            echo "<Worksheet ss:Name=\"Data Entry Template\"><Table>";
            echo "<Row>"; foreach ($headers as $header) { echo "<Cell><Data ss:Type=\"String\">".htmlspecialchars($header, ENT_XML1)."</Data></Cell>"; } echo "</Row>";
            echo "<Row>"; foreach ($sample as $value) { echo "<Cell><Data ss:Type=\"String\">".htmlspecialchars($value, ENT_XML1)."</Data></Cell>"; } echo "</Row>";
            echo "</Table></Worksheet>";
            echo "<Worksheet ss:Name=\"Variables\"><Table>";
            foreach ($variables as $row) { echo "<Row>"; foreach ($row as $cell) { echo "<Cell><Data ss:Type=\"String\">".htmlspecialchars($cell, ENT_XML1)."</Data></Cell>"; } echo "</Row>"; }
            echo "</Table></Worksheet></Workbook>";
        }, 200, [
            'Content-Type' => 'application/vnd.ms-excel; charset=UTF-8',
            'Content-Disposition' => 'attachment; filename=ngbvd_offline_import_template.xls',
        ]);
    }

    function fgm(){
        $district = District::select('name', 'id')->get();
        $subcounty = SubCounty::select('name', 'id')->get();
        $parish = Parish::select('name', 'id')->get();
        $services = Service::all();
        return view('dataentry.fgm')->with(['districts'=>$district, 'subcounties'=> $subcounty, 'parishes'=>$parish, 'services'=>$services]);
    }
    /**
     * Return to the current data-entry workflow with a safe user-facing message.
     */
    private function redirectToCaseTab($victimId, string $tab, string $message, bool $isUpdate = false)
    {
        $route = $isUpdate ? 'dataentry/update' : 'dataentry/index';

        $isFailure = $this->isFailureMessage($message);
        $flashKey = $isFailure ? 'error' : 'message';

        return redirect(url($route . '?id=' . $this->publicVictimReference($victimId) . '&tab=' . $tab))
            ->withInput()
            ->with($flashKey, $message);
    }

    /**
     * Determine whether a flash message should be rendered as an error/warning instead of success.
     */
    private function isFailureMessage(string $message): bool
    {
        $message = strtolower($message);

        foreach ([
            'could not',
            'duplicate',
            'failed',
            'failure',
            'invalid',
            'not allowed',
            'not authorised',
            'not authorized',
            'no partial',
            'rejected',
            'error',
        ] as $needle) {
            if (strpos($message, $needle) !== false) {
                return true;
            }
        }

        return false;
    }

    /**
     * Assign a value only to a column that exists on the model table.
     * This prevents legacy schema differences from causing SQL "unknown column" errors.
     */
    private function setFirstExistingModelAttribute($model, array $possibleColumns, $value): void
    {
        if ($value === null || $value === '') {
            return;
        }

        $table = $model->getTable();

        foreach ($possibleColumns as $column) {
            if (Schema::hasColumn($table, $column)) {
                $model->{$column} = $value;
                return;
            }
        }

        Log::notice('NGBVD skipped optional model attribute because no compatible column exists', [
            'table' => $table,
            'possible_columns' => $possibleColumns,
        ]);
    }

    /**
     * Log write failures without exposing database/server details to the user.
     */
    private function logWriteFailure(string $operation, \Throwable $exception, Request $request): void
    {
        Log::error('NGBVD data-entry write failed', [
            'operation' => $operation,
            'user_id' => optional(Auth::user())->id,
            'victim_id' => $request->input('id'),
            'message' => $exception->getMessage(),
            'exception_class' => get_class($exception),
        ]);
    }



    /**
     * Keep legacy form names stable while ensuring the backend receives consistent values.
     * This does not change the UI; it only normalizes submitted data before validation/save.
     */
    private function normalizeVictimOverviewRequest(Request $request): void
    {
        $safeFields = [
            'crb_number' => 100,
            'survivor_code' => 100,
            'other_names' => 180,
            'first_name' => 120,
            'surname' => 120,
            'address' => 500,
            'phone' => 50,
            'reporter_name' => 180,
            'reported_by' => 180,
            'reportedby' => 180,
        ];

        $clean = [];
        foreach ($safeFields as $field => $limit) {
            if ($request->has($field)) {
                $clean[$field] = mb_substr(strip_tags(trim((string) $request->input($field))), 0, $limit);
            }
        }

        if (!empty($clean)) {
            $request->merge($clean);
        }

        $reference = trim((string) $request->input('crb_number', ''));
        if ($reference !== '') {
            $request->merge(['survivor_code' => mb_substr($reference, 0, 100)]);
        }

        if ((string) $request->input('reporter_type') === '1') {
            $request->merge(['reporter_relation' => null]);
        }

        if (!$request->filled('ns_categornsy')) {
            $request->merge(['ns_categornsy' => null]);
        }
    }

    private function getReportedBy(Request $request)
    {
        return mb_substr(strip_tags(trim((string) $request->input('reported_by', $request->input('reportedby')))), 0, 180);
    }

    protected function validateVictimLocationChain(Request $request): void
    {
        $request->validate([
            'districtid' => ['required', 'integer', 'exists:districts,id'],
            'subcountyid' => ['required', 'integer'],
            'parishid' => ['nullable', 'integer'],
        ]);

        app(ColumnAwareLocationChainValidator::class)->assertValid(
            (int) $request->input('districtid'),
            (int) $request->input('subcountyid'),
            (int) $request->input('parishid')
        );
    }



    /**
     * Resolve a case from a public UUID, an older base64 encoded numeric ID, or a raw numeric ID.
     * New URLs and redirects use UUIDs; numeric IDs are accepted only for backward compatibility.
     */
    private function resolveVictimFromReference($reference): Victim
    {
        $reference = (string) $reference;

        if (Schema::hasColumn((new Victim())->getTable(), 'uuid') && Str::isUuid($reference)) {
            $victim = Victim::where('uuid', $reference)->firstOrFail();
        } else {
            $decoded = base64_decode($reference, true);
            $numericId = ($decoded !== false && is_numeric($decoded)) ? $decoded : (is_numeric($reference) ? $reference : null);

            if (!$numericId) {
                abort(404, 'Case record not found.');
            }

            $victim = Victim::where('id', $numericId)->firstOrFail();
        }

        app(\App\Services\AccessControl\DataScopeService::class)->assertCanAccessVictim(Auth::user(), $victim);

        return $victim;
    }

    private function publicVictimReference($victimOrId): string
    {
        if ($victimOrId instanceof Victim) {
            return !empty($victimOrId->uuid) ? (string) $victimOrId->uuid : (string) $victimOrId->id;
        }

        $victim = Victim::find($victimOrId);
        if ($victim && Schema::hasColumn($victim->getTable(), 'uuid') && !empty($victim->uuid)) {
            return (string) $victim->uuid;
        }

        return (string) $victimOrId;
    }

    private function auditCaseEvent(string $action, $victimId, array $context = []): void
    {
        $victim = $victimId instanceof Victim ? $victimId : Victim::find($victimId);

        app(AuditLogService::class)->recordBusinessEvent('dataentry', $action, array_merge([
            'victim_id' => $victim ? $victim->id : $victimId,
            'victim_uuid' => $victim && Schema::hasColumn($victim->getTable(), 'uuid') ? $victim->uuid : null,
            'case_reference' => $victim ? ($victim->crb_number ?: $victim->survivor_code) : null,
        ], $context));
    }

    private function pushNsrSafely($victimId, string $recordType = 'case'): void
    {
        try {
            if (!config('ngbvd_integrations.nsr.enabled')) {
                return;
            }

            $victim = $victimId instanceof Victim ? $victimId : Victim::find($victimId);
            if (!$victim) {
                return;
            }

            // External NSR calls must not run inside the browser request. Queue them so one
            // slow NSR/API response does not block other open pages/tabs for the same user.
            if (config('queue.default') === 'sync') {
                Log::warning('NGBVD NSR push not queued because QUEUE_CONNECTION=sync. Configure QUEUE_CONNECTION=database or redis and run a queue worker.', [
                    'victim_id' => $victim->id,
                    'record_type' => $recordType,
                ]);
                return;
            }

            PushNsrToRegistry::dispatch($victim->id, $recordType, Auth::id())->afterCommit();
        } catch (\Throwable $e) {
            Log::warning('NGBVD NSR queue dispatch skipped after data-entry save.', [
                'victim_id' => $victimId instanceof Victim ? $victimId->id : $victimId,
                'record_type' => $recordType,
                'message' => $e->getMessage(),
            ]);
        }
    }

    /**
     * Read a repeated-row value. Supports both indexed names such as field_0 and the legacy
     * scalar field name used by the original first row. This keeps UI markup stable while
     * allowing multiple rows to save independently.
     */
    private function repeatedValue(Request $request, string $field, int $index, $default = null)
    {
        $indexed = $field . '_' . $index;

        if ($request->has($indexed)) {
            return $this->sanitizeRepeatedValue($request->input($indexed), $default);
        }

        if ($index === 0 && $request->has($field)) {
            return $this->sanitizeRepeatedValue($request->input($field), $default);
        }

        return $default;
    }

    private function repeatedArrayValue(Request $request, string $field, int $index, array $default = []): array
    {
        $indexed = $field . '_' . $index;

        $value = $request->has($indexed) ? $request->input($indexed) : ($index === 0 ? $request->input($field, $default) : $default);

        if ($value === null || $value === '') {
            return [];
        }

        $items = is_array($value) ? $value : [$value];

        return array_values(array_filter(array_map(function ($item) {
            return $this->sanitizeRepeatedValue($item, null);
        }, $items), function ($item) {
            return $item !== null && $item !== '';
        }));
    }

    private function repeatedCount(Request $request, string $countField, int $minimum = 1): int
    {
        $count = (int) $request->input($countField, $minimum);

        return min(100, max($minimum, $count));
    }

    private function sanitizeRepeatedValue($value, $default = null)
    {
        if ($value === null) {
            return $default;
        }

        if (is_bool($value) || is_numeric($value)) {
            return $value;
        }

        if (is_string($value)) {
            return mb_substr(strip_tags(trim($value)), 0, 2000);
        }

        return $value;
    }

    private function repeatedRowHasAnyValue(array $row, array $keys): bool
    {
        foreach ($keys as $key) {
            if (array_key_exists($key, $row) && $row[$key] !== null && $row[$key] !== '' && $row[$key] !== []) {
                return true;
            }
        }

        return false;
    }

    private function assertNoDuplicateRows(string $section, array $rows, array $identityKeys): void
    {
        $seen = [];

        foreach ($rows as $row) {
            $fingerprintValues = [];

            foreach ($identityKeys as $key) {
                $value = $row[$key] ?? '';
                if (is_array($value)) {
                    sort($value);
                    $value = implode(',', $value);
                }
                $fingerprintValues[] = trim(strtolower((string) $value));
            }

            $fingerprint = implode('|', $fingerprintValues);

            if (trim(str_replace('|', '', $fingerprint)) === '') {
                continue;
            }

            if (isset($seen[$fingerprint])) {
                throw new \App\Exceptions\DataQualityException("Duplicate {$section} entry detected. Please remove exact duplicate rows before saving.");
            }

            $seen[$fingerprint] = true;
        }
    }


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

        if (is_string($value)) {
            $value = trim($value);
        }

        if ($value === '') {
            return null;
        }

        return is_numeric($value) ? (int) $value : null;
    }

    private function cleanIntegerDefault($value, int $default = 0): int
    {
        $clean = $this->cleanNullableInteger($value);

        return $clean === null ? $default : $clean;
    }

    private function cleanBooleanInteger($value, int $default = 0): int
    {
        if ($value === null || $value === '') {
            return $default;
        }

        if (is_bool($value)) {
            return $value ? 1 : 0;
        }

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

        if (in_array($value, ['1', 'yes', 'true', 'on'], true)) {
            return 1;
        }

        if (in_array($value, ['0', 'no', 'false', 'off'], true)) {
            return 0;
        }

        return $default;
    }

    private function cleanTextOrNull($value, int $max = 500): ?string
    {
        if ($value === null) {
            return null;
        }

        $value = mb_substr(strip_tags(trim((string) $value)), 0, $max);

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

    private function modelFillExistingColumns($model, array $attributes): void
    {
        $table = $model->getTable();

        foreach ($attributes as $column => $value) {
            if (Schema::hasColumn($table, $column)) {
                $model->{$column} = $value;
            }
        }
    }

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

        $timestamp = strtotime((string) $value);

        return $timestamp ? date('Y-m-d', $timestamp) : null;
    }

    private function findExistingSectionRecord(string $modelClass, $id, string $victimColumn, $victimId)
    {
        if (!$id) {
            return null;
        }

        return $modelClass::where('id', $id)->where($victimColumn, $victimId)->first();
    }

    //Data entry for the victim data.
    function processvictimdata(Request $request){
        $this->normalizeVictimOverviewRequest($request);
        $this->validateVictimLocationChain($request);

        $validated = $request->validate([
            'crb_number' => 'required|string|max:100',
            'interview_date' => 'required|date',
            'interview_time' => 'required',
            'districtid' => 'required',
            'subcountyid' => 'required',
            'parishid' => 'required',
            'survivor_code' => 'required|string|max:100',
            'sex' => 'required',
            'nationality' => 'required',
            'age' => 'required|numeric|min:0',
            'religion' => 'required',
            'marital_status' => 'required',
            'education' => 'required',
            'reporter_sex' => 'required',
            'reporter_type' => 'required',
            'idtype' => ['nullable', 'integer', 'exists:id_types,id'],
            'id_number' => ['nullable', 'string', 'max:120'],
            'national_status' => ['nullable', 'integer', 'exists:national_status,id'],
            'ns_categornsy' => ['nullable', 'integer', 'exists:national_status_categories,id'],
            'number_children' => ['nullable', 'integer', 'min:0', 'max:50'],
            'separeted_children' => ['nullable', 'integer', 'in:0,1'],
            'pwd' => ['nullable', 'integer', 'in:0,1'],
            'disability' => ['nullable', 'integer'],
            'occupation' => ['nullable', 'integer'],
            'emp_status' => ['nullable', 'integer'],
            'previos_reporting' => ['nullable', 'integer', 'in:0,1'],
            'reffered' => ['nullable', 'integer', 'in:0,1'],
            'reporter_phone' => ['nullable', 'string', 'max:50'],
            'reporter_email' => ['nullable', 'email', 'max:180'],
            'reporter_relation' => ['nullable', 'integer'],
        ]);

        app(\App\Services\AccessControl\DataScopeService::class)->assertDistrictAllowed(Auth::user(), $request->input('districtid'));
        app(\App\Services\AccessControl\DataScopeService::class)->assertPartnerAllowed(Auth::user(), $request->input('partnerid'));

        $duplicate = Victim::query()
            ->where('interview_date', date('Y-m-d', strtotime($request->input('interview_date'))))
            ->where('districtid', $request->input('districtid'))
            ->where('subcountyid', $request->input('subcountyid'))
            ->where('parishid', $request->input('parishid'))
            ->where(function ($query) use ($request) {
                $query->where('crb_number', $request->input('crb_number'))
                    ->orWhere('survivor_code', $request->input('survivor_code'));
            })
            ->exists();

        if ($duplicate) {
            return back()->withInput()->with('error_message', 'Possible duplicate case detected. A case with the same CRB/survivor code, interview date and location already exists.');
        }

        try {
            $victim = DB::transaction(function () use ($request) {
                $formvalues = $request;
                $victim = new Victim();

                $victim->crb_number = $formvalues->input('crb_number');
                $victim->interview_date = date('Y-m-d', strtotime($formvalues->input('interview_date')));
                $victim->interview_time  = $formvalues->input('interview_time');
                $victim->refered = $this->cleanBooleanInteger($formvalues->input('reffered'), 0);
                $victim->districtid = $formvalues->input('districtid');
                $victim->subcountyid = $formvalues->input('subcountyid');
                $victim->parishid = $formvalues->input('parishid');
                $victim->village = $this->cleanTextOrNull($formvalues->input('village'), 180);
                $victim->survivor_code = $formvalues->input('crb_number');
                $victim->gender = $formvalues->input('sex');
                $victim->nationality = $formvalues->input('nationality');
                $victim->idtype = $this->cleanNullableInteger($formvalues->input('idtype'));
                $victim->id_number = $this->cleanTextOrNull($formvalues->input('id_number'), 120);
                $victim->dob = $this->cleanIntegerDefault($formvalues->input('age'), 0);
                $victim->nationality_status = $this->cleanNullableInteger($formvalues->input('national_status'));
                $victim->nationality_status_category_id = $this->cleanNullableInteger($formvalues->input('ns_categornsy'));
                $victim->number_of_children = $this->cleanIntegerDefault($formvalues->input('number_children'), 0);
                $victim->religion = $formvalues->input('religion');
                $victim->marital_status = $formvalues->input('marital_status');
                $victim->separeted_children = $this->cleanBooleanInteger($formvalues->input('separeted_children'), 0);
                $victim->pwd = $this->cleanBooleanInteger($formvalues->input('pwd'), 0);
                $victim->disability = $this->cleanNullableInteger($formvalues->input('disability'));
                $victim->education = $formvalues->input('education');
                $victim->occupation = $this->cleanNullableInteger($formvalues->input('occupation'));
                $victim->employment_status_id = $this->cleanNullableInteger($formvalues->input('emp_status'));
                $victim->previos_reporting = $this->cleanBooleanInteger($formvalues->input('previos_reporting'), 0);
                $victim->created_by = Auth::id();
                $victim->reporter_sex = $formvalues->input('reporter_sex');
                $victim->reporter_phone = $this->cleanTextOrNull($formvalues->input('reporter_phone'), 50);
                $victim->reporter_email = $this->cleanTextOrNull($formvalues->input('reporter_email'), 180);
                $victim->reporter_relation = $this->cleanNullableInteger($formvalues->input('reporter_relation'));
                $victim->reporter_type = $formvalues->input('reporter_type');

                $victim->saveOrFail();

                return $victim;
            });

            $this->auditCaseEvent('create_survivor_profile', $victim, ['section' => 'overview']);
            $this->pushNsrSafely($victim, 'survivor');

            return redirect(url('dataentry/index?id=' . $this->publicVictimReference($victim) . '&tab=incidents'))
                ->with('message', 'Victim successfully saved, use ' . $this->publicVictimReference($victim) . ' as the reference ID to identify the case');
        } catch (\Throwable $e) {
            $this->logWriteFailure('processvictimdata', $e, $request);

            return back()
                ->withInput($request->except(['password', 'password_confirmation', 'password_confirmatio']))
                ->withErrors(['general_information' => 'Victim data could not be saved. Please review the highlighted fields and try again.'])
                ->with('error_message', 'Victim data could not be saved. Please review the highlighted fields and try again.');
        }
    }

    function updatevictimdata(Request $request){
        $this->normalizeVictimOverviewRequest($request);
        $this->validateVictimLocationChain($request);

        $request->validate([
            'id' => 'required',
            'crb_number' => 'required|string|max:100',
            'interview_date' => 'required|date',
            'interview_time' => 'required',
            'districtid' => 'required',
            'subcountyid' => 'required',
            'parishid' => 'required',
            'survivor_code' => 'required|string|max:100',
            'sex' => 'required',
            'nationality' => 'required',
            'age' => 'required|numeric|min:0',
            'religion' => 'required',
            'marital_status' => 'required',
            'education' => 'required',
            'reporter_sex' => 'required',
            'reporter_type' => 'required',
            'idtype' => ['nullable', 'integer', 'exists:id_types,id'],
            'id_number' => ['nullable', 'string', 'max:120'],
            'national_status' => ['nullable', 'integer', 'exists:national_status,id'],
            'ns_categornsy' => ['nullable', 'integer', 'exists:national_status_categories,id'],
            'number_children' => ['nullable', 'integer', 'min:0', 'max:50'],
            'separeted_children' => ['nullable', 'integer', 'in:0,1'],
            'pwd' => ['nullable', 'integer', 'in:0,1'],
            'disability' => ['nullable', 'integer'],
            'occupation' => ['nullable', 'integer'],
            'emp_status' => ['nullable', 'integer'],
            'previos_reporting' => ['nullable', 'integer', 'in:0,1'],
            'reffered' => ['nullable', 'integer', 'in:0,1'],
            'reporter_phone' => ['nullable', 'string', 'max:50'],
            'reporter_email' => ['nullable', 'email', 'max:180'],
            'reporter_relation' => ['nullable', 'integer'],
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimId = $victim->id;

        app(\App\Services\AccessControl\DataScopeService::class)->assertCanAccessVictim(Auth::user(), $victim);
        app(\App\Services\AccessControl\DataScopeService::class)->assertDistrictAllowed(Auth::user(), $request->input('districtid'));
        app(\App\Services\AccessControl\DataScopeService::class)->assertPartnerAllowed(Auth::user(), $request->input('partnerid'));

        $duplicate = Victim::query()
            ->where('id', '<>', $victimId)
            ->where(function ($query) use ($request) {
                $query->where('crb_number', $request->input('crb_number'))
                    ->orWhere('survivor_code', $request->input('crb_number'));
            })
            ->exists();

        if ($duplicate) {
            return $this->redirectToCaseTab($victimId, 'overview', 'Possible duplicate case detected. A case with the same reference number already exists.', true);
        }

        try {
            DB::transaction(function () use ($request, $victim) {
                $victim->crb_number = $request->input('crb_number');
                $victim->interview_date = date('Y-m-d', strtotime($request->input('interview_date')));
                $victim->interview_time  = $request->input('interview_time');
                $victim->refered = $this->cleanBooleanInteger($request->input('reffered'), 0);
                $victim->districtid = $request->input('districtid');
                $victim->subcountyid = $request->input('subcountyid');
                $victim->parishid = $request->input('parishid');
                $victim->village = $this->cleanTextOrNull($request->input('village'), 180);
                $victim->survivor_code = $request->input('crb_number');
                $victim->gender = $request->input('sex');
                $victim->nationality = $request->input('nationality');
                $victim->idtype = $this->cleanNullableInteger($request->input('idtype'));
                $victim->id_number = $this->cleanTextOrNull($request->input('id_number'), 120);
                $victim->dob = $this->cleanIntegerDefault($request->input('age'), 0);
                $victim->nationality_status = $this->cleanNullableInteger($request->input('national_status'));
                $victim->nationality_status_category_id = $this->cleanNullableInteger($request->input('ns_categornsy'));
                $victim->number_of_children = $this->cleanIntegerDefault($request->input('number_children'), 0);
                $victim->religion = $request->input('religion');
                $victim->marital_status = $request->input('marital_status');
                $victim->separeted_children = $this->cleanBooleanInteger($request->input('separeted_children'), 0);
                $victim->pwd = $this->cleanBooleanInteger($request->input('pwd'), 0);
                $victim->disability = $this->cleanNullableInteger($request->input('disability'));
                $victim->education = $request->input('education');
                $victim->occupation = $this->cleanNullableInteger($request->input('occupation'));
                $victim->employment_status_id = $this->cleanNullableInteger($request->input('emp_status'));
                $victim->previos_reporting = $this->cleanBooleanInteger($request->input('previos_reporting'), 0);
                $victim->reporter_sex = $request->input('reporter_sex');
                $victim->reporter_phone = $this->cleanTextOrNull($request->input('reporter_phone'), 50);
                $victim->reporter_email = $this->cleanTextOrNull($request->input('reporter_email'), 180);
                $victim->reporter_relation = $this->cleanNullableInteger($request->input('reporter_relation'));
                $victim->reporter_type = $request->input('reporter_type');
                $victim->updated_by = Auth::id();
                $victim->saveOrFail();
            });

            $this->auditCaseEvent('update_survivor_profile', $victimId, ['section' => 'overview']);
            $this->pushNsrSafely($victimId, 'survivor');

            return $this->redirectToCaseTab($victimId, 'incidents', 'Survivor profile successfully updated.', true);
        } catch (\Throwable $e) {
            $this->logWriteFailure('updatevictimdata', $e, $request);
            return $this->redirectToCaseTab($victimId, 'overview', 'Survivor profile could not be updated. Please review the form and try again.', true);
        }
    }

    function updateincidentdata(Request $request){
        $request->validate([
            'id' => 'required',
            'districtid' => 'required',
            'subcountyid' => 'required',
            'parishid' => 'required',
            'incident_date' => 'required|date',
            'incident_time' => 'required',
            'reportedby' => 'required',
            'incidentid' => 'required|array|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $scopeService = app(\App\Services\AccessControl\DataScopeService::class);
        $scopeService->assertCanWriteCaseSection(Auth::user(), $victim, $request->input('districtid'));
        $this->validateVictimLocationChain($request);

        try {
            DB::transaction(function () use ($request, $victimid) {
                IncidentData::where('victimid', $victimid)->delete();

                $regionid = optional(DB::table('districts')
                    ->select('regions_id')
                    ->where('id', $request->input('districtid'))
                    ->first())->regions_id;

                foreach ($request->input('incidentid', []) as $incidentid) {
                    $incident = new IncidentData();
                    $incident->victimid = $victimid;
                    $incident->incidentid = $incidentid;
                    $incident->incident_date  = date('Y-m-d', strtotime($request->input('incident_date')));
                    $incident->incident_time = $request->input('incident_time');
                    $incident->reported_by = $this->getReportedBy($request);
                    $this->setFirstExistingModelAttribute($incident, ['reportedbycontact', 'reported_by_contact', 'reportedby_contact', 'reported_contact'], $request->input('reportedbycontact'));
                    $incident->regions_id = $regionid;
                    $incident->districtid = $request->input('districtid');
                    $incident->subcountyid = $request->input('subcountyid');
                    $incident->parishid = $request->input('parishid');
                    $incident->village = $request->input('village');
                    $incident->notes = $request->input('notes');
                    $incident->updated_by = Auth::id();
                    $incident->saveOrFail();
                }
            });

            $this->auditCaseEvent('update_incident', $victimid, ['section' => 'incidents']);
            $this->pushNsrSafely($victimid, 'incident');

            return $this->redirectToCaseTab($victimid, 'perpetrator', 'Incident data successfully updated.', true);
        } catch (\Throwable $e) {
            $this->logWriteFailure('updateincidentdata', $e, $request);
            return $this->redirectToCaseTab($victimid, 'incidents', 'Incident data could not be updated. No partial records were committed.', true);
        }
    }

    function processincidentdata(Request $request){
        $request->validate([
            'id' => 'required',
            'districtid' => 'required',
            'subcountyid' => 'required',
            'parishid' => 'required',
            'incident_date' => 'required|date',
            'incident_time' => 'required',
            'reportedby' => 'required',
            'incidentid' => 'required|array|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $scopeService = app(\App\Services\AccessControl\DataScopeService::class);
        $scopeService->assertCanWriteCaseSection(Auth::user(), $victim, $request->input('districtid'));
        $this->validateVictimLocationChain($request);
        $incidentDate = date('Y-m-d', strtotime($request->input('incident_date')));
        $duplicates = IncidentData::query()
            ->where('victimid', $victimid)
            ->where('incident_date', $incidentDate)
            ->whereIn('incidentid', $request->input('incidentid', []))
            ->pluck('incidentid')
            ->all();

        if (!empty($duplicates)) {
            return $this->redirectToCaseTab($victimid, 'incidents', 'Possible duplicate incident detected. One or more selected incident categories already exist for this case and incident date.');
        }

        try {
            DB::transaction(function () use ($request, $victimid) {
                $regionid = optional(DB::table('districts')
                    ->select('regions_id')
                    ->where('id', $request->input('districtid'))
                    ->first())->regions_id;

                foreach ($request->input('incidentid', []) as $incidentid) {
                    $incident = new IncidentData();
                    $incident->victimid = $victimid;
                    $incident->incidentid = $incidentid;
                    $incident->incident_date  = date('Y-m-d', strtotime($request->input('incident_date')));
                    $incident->incident_time = $request->input('incident_time');
                    $incident->reported_by = $this->getReportedBy($request);
                    $this->setFirstExistingModelAttribute($incident, ['reportedbycontact', 'reported_by_contact', 'reportedby_contact', 'reported_contact'], $request->input('reportedbycontact'));
                    $incident->regions_id = $regionid;
                    $incident->districtid = $request->input('districtid');
                    $incident->subcountyid = $request->input('subcountyid');
                    $incident->parishid = $request->input('parishid');
                    $incident->village = $request->input('village');
                    $incident->notes = $request->input('notes');
                    $incident->created_by = Auth::id();
                    $incident->saveOrFail();
                }
            });

            $this->auditCaseEvent('create_incident', $victimid, ['section' => 'incidents']);
            $this->pushNsrSafely($victimid, 'incident');

            return $this->redirectToCaseTab($victimid, 'perpetrator', 'Incident data successfully saved, use ' . $this->publicVictimReference($victimid) . ' as the Reference ID to identify the case.');
        } catch (\Throwable $e) {
            $this->logWriteFailure('processincidentdata', $e, $request);

            return $this->redirectToCaseTab($victimid, 'incidents', 'Incident data could not be saved. No partial incident records were committed.');
        }
    }

    function processperpetrator(Request $request){
        $request->validate([
            'id' => 'required',
            'criminals' => 'required|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'criminals');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $row = [
                        'victimid' => $victimid,
                        'nationality' => $this->repeatedValue($request, 'nationality', $i),
                        'agegroup' => $this->repeatedValue($request, 'agegroup', $i),
                        'education' => $this->repeatedValue($request, 'education', $i),
                        'national_status_category_id' => $this->repeatedValue($request, 'national_status_category', $i, $this->repeatedValue($request, 'national_status', $i)),
                        'idtype' => $this->repeatedValue($request, 'idtype', $i),
                        'id_number' => $this->repeatedValue($request, 'id_number', $i),
                        'religion' => $this->repeatedValue($request, 'religion', $i),
                        'employment_status_id' => $this->repeatedValue($request, 'ep_status', $i),
                        'occupation' => $this->repeatedValue($request, 'occupation', $i),
                        'relation' => $this->repeatedValue($request, 'relation', $i),
                        'national_status' => $this->repeatedValue($request, 'national_status', $i),
                        'threat' => $this->repeatedValue($request, 'threat', $i),
                        'gender' => $this->repeatedValue($request, 'sex', $i),
                        'phone' => $this->repeatedValue($request, 'phone', $i),
                        'notes' => $request->input('notes'),
                        'created_by' => Auth::id(),
                    ];

                    if (!$this->repeatedRowHasAnyValue($row, ['nationality','agegroup','education','relation','national_status','gender','phone'])) {
                        continue;
                    }

                    $rows[] = $row;
                }

                $this->assertNoDuplicateRows('perpetrator', $rows, ['gender','agegroup','relation','nationality','national_status','phone']);

                foreach ($rows as $row) {
                    $perpetrator = new Perpetrator();
                    $this->modelFillExistingColumns($perpetrator, $row);
                    $perpetrator->saveOrFail();
                }
            });

            $this->auditCaseEvent('create_perpetrator', $victimid, ['section' => 'perpetrator']);
            $this->pushNsrSafely($victimid, 'perpetrator');

            return $this->redirectToCaseTab($victimid, 'witness', 'Perpetrator Data successfully saved');
        } catch (\Throwable $e) {
            $this->logWriteFailure('processperpetrator', $e, $request);

            return $this->redirectToCaseTab($victimid, 'perpetrator', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Perpetrator data could not be saved. No partial perpetrator records were committed.');
        }
    }

    function updateperpetrator(Request $request){
        $request->validate([
            'id' => 'required',
            'criminals' => 'required|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'criminals');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $rows[] = [
                        '_id' => $this->repeatedValue($request, 'perpetrator_id', $i),
                        'victimid' => $victimid,
                        'nationality' => $this->repeatedValue($request, 'nationality', $i),
                        'agegroup' => $this->repeatedValue($request, 'agegroup', $i),
                        'education' => $this->repeatedValue($request, 'education', $i),
                        'national_status_category_id' => $this->repeatedValue($request, 'national_status_category', $i, $this->repeatedValue($request, 'national_status', $i)),
                        'idtype' => $this->repeatedValue($request, 'idtype', $i),
                        'id_number' => $this->repeatedValue($request, 'id_number', $i),
                        'religion' => $this->repeatedValue($request, 'religion', $i),
                        'employment_status_id' => $this->repeatedValue($request, 'ep_status', $i),
                        'occupation' => $this->repeatedValue($request, 'occupation', $i),
                        'relation' => $this->repeatedValue($request, 'relation', $i),
                        'national_status' => $this->repeatedValue($request, 'national_status', $i),
                        'threat' => $this->repeatedValue($request, 'threat', $i),
                        'gender' => $this->repeatedValue($request, 'sex', $i),
                        'phone' => $this->repeatedValue($request, 'phone', $i),
                        'notes' => $request->input('notes'),
                        'updated_by' => Auth::id(),
                    ];
                }

                $rows = array_values(array_filter($rows, function ($row) {
                    return $this->repeatedRowHasAnyValue($row, ['nationality','agegroup','education','relation','national_status','gender','phone']);
                }));

                $this->assertNoDuplicateRows('perpetrator', $rows, ['gender','agegroup','relation','nationality','national_status','phone']);

                foreach ($rows as $row) {
                    $perpetrator = $this->findExistingSectionRecord(Perpetrator::class, $row['_id'] ?? null, 'victimid', $victimid) ?: new Perpetrator();
                    unset($row['_id']);
                    $this->modelFillExistingColumns($perpetrator, $row);
                    if (!$perpetrator->exists && Schema::hasColumn($perpetrator->getTable(), 'created_by')) {
                        $perpetrator->created_by = Auth::id();
                    }
                    $perpetrator->saveOrFail();
                }
            });

            $this->auditCaseEvent('update_perpetrator', $victimid, ['section' => 'perpetrator']);
            $this->pushNsrSafely($victimid, 'perpetrator');

            return $this->redirectToCaseTab($victimid, 'witness', 'Perpetrator Data successfully saved', true);
        } catch (\Throwable $e) {
            $this->logWriteFailure('updateperpetrator', $e, $request);

            return $this->redirectToCaseTab($victimid, 'perpetrator', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Perpetrator data could not be saved. No partial perpetrator records were committed.', true);
        }
    }

    function processwitness(Request $request){
        $request->validate([
            'id' => 'required',
            'witnessess' => 'required|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'witnessess');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $row = [
                        'victimid' => $victimid,
                        'agegroup' => $this->repeatedValue($request, 'agegroup', $i),
                        'name' => $this->repeatedValue($request, 'name', $i),
                        'relation' => $this->repeatedValue($request, 'relation', $i),
                        'gender' => $this->repeatedValue($request, 'sex', $i),
                        'created_by' => Auth::id(),
                    ];

                    if (!$this->repeatedRowHasAnyValue($row, ['name','agegroup','relation','gender'])) {
                        continue;
                    }

                    $rows[] = $row;
                }

                $this->assertNoDuplicateRows('witness', $rows, ['name','agegroup','relation','gender']);

                foreach ($rows as $row) {
                    $witness = new Witness();
                    $this->modelFillExistingColumns($witness, $row);
                    $witness->saveOrFail();
                }
            });

            $this->auditCaseEvent('create_witness', $victimid, ['section' => 'witness']);
            $this->pushNsrSafely($victimid, 'witness');

            return $this->redirectToCaseTab($victimid, 'action', 'Witness Data successfully saved');
        } catch (\Throwable $e) {
            $this->logWriteFailure('processwitness', $e, $request);

            return $this->redirectToCaseTab($victimid, 'witness', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Witness data could not be saved. No partial witness records were committed.');
        }
    }

    function updatewitness(Request $request){
        $request->validate([
            'id' => 'required',
            'witnessess' => 'nullable|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'witnessess');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $rows[] = [
                        '_id' => $this->repeatedValue($request, 'witness_id', $i),
                        'victimid' => $victimid,
                        'agegroup' => $this->repeatedValue($request, 'agegroup', $i),
                        'name' => $this->repeatedValue($request, 'name', $i),
                        'relation' => $this->repeatedValue($request, 'relation', $i),
                        'gender' => $this->repeatedValue($request, 'sex', $i),
                        'updated_by' => Auth::id(),
                    ];
                }

                $rows = array_values(array_filter($rows, function ($row) {
                    return $this->repeatedRowHasAnyValue($row, ['name','agegroup','relation','gender']);
                }));

                $this->assertNoDuplicateRows('witness', $rows, ['name','agegroup','relation','gender']);

                foreach ($rows as $row) {
                    $witness = $this->findExistingSectionRecord(Witness::class, $row['_id'] ?? null, 'victimid', $victimid) ?: new Witness();
                    unset($row['_id']);
                    $this->modelFillExistingColumns($witness, $row);
                    if (!$witness->exists && Schema::hasColumn($witness->getTable(), 'created_by')) {
                        $witness->created_by = Auth::id();
                    }
                    $witness->saveOrFail();
                }
            });

            $this->auditCaseEvent('update_witness', $victimid, ['section' => 'witness']);
            $this->pushNsrSafely($victimid, 'witness');

            return $this->redirectToCaseTab($victimid, 'action', 'Witness Data successfully saved', true);
        } catch (\Throwable $e) {
            $this->logWriteFailure('updatewitness', $e, $request);

            return $this->redirectToCaseTab($victimid, 'witness', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Witness data could not be saved.', true);
        }
    }

    function processaction(Request $request){
        $request->validate([
            'id' => 'required',
            'actions_count' => 'nullable|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'actions_count');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $services = $this->repeatedArrayValue($request, 'serviceid', $i);

                    $row = [
                        'victim_id' => $victimid,
                        'reported_to' => $this->repeatedValue($request, 'reportedto', $i),
                        'refferedto' => $this->repeatedValue($request, 'refferedto', $i),
                        'report_date' => $this->formatNullableDate($this->repeatedValue($request, 'date_reported', $i)),
                        'date_reffered' => $this->formatNullableDate($this->repeatedValue($request, 'date_reffered', $i)),
                        'notes' => $this->repeatedValue($request, 'notes', $i),
                        'services' => implode(',', $services),
                        'created_by' => Auth::id(),
                    ];

                    if (!$this->repeatedRowHasAnyValue($row, ['reported_to','refferedto','report_date','date_reffered','notes','services'])) {
                        continue;
                    }

                    if (!$row['reported_to'] || !$row['report_date']) {
                        throw new \App\Exceptions\DataQualityException('Each action row must have Reported To and Date Reported.');
                    }

                    $rows[] = $row;
                }

                $this->assertNoDuplicateRows('action', $rows, ['reported_to','refferedto','report_date','date_reffered','services','notes']);

                foreach ($rows as $row) {
                    $action = new Action();
                    $this->modelFillExistingColumns($action, $row);
                    $action->saveOrFail();
                }
            });

            $this->auditCaseEvent('create_survivor_service_action', $victimid, ['section' => 'action']);
            $this->pushNsrSafely($victimid, 'service_action');

            return $this->redirectToCaseTab($victimid, 'status', 'Victim Services captured successfully');
        } catch (\Throwable $e) {
            $this->logWriteFailure('processaction', $e, $request);

            return $this->redirectToCaseTab($victimid, 'action', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Victim services could not be saved. Please try again.');
        }
    }

    function updateaction(Request $request){
        $request->validate([
            'id' => 'required',
            'actions_count' => 'nullable|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'actions_count');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $services = $this->repeatedArrayValue($request, 'serviceid', $i);

                    $rows[] = [
                        '_id' => $this->repeatedValue($request, 'action_id', $i),
                        'victim_id' => $victimid,
                        'reported_to' => $this->repeatedValue($request, 'reportedto', $i),
                        'refferedto' => $this->repeatedValue($request, 'refferedto', $i),
                        'report_date' => $this->formatNullableDate($this->repeatedValue($request, 'date_reported', $i)),
                        'date_reffered' => $this->formatNullableDate($this->repeatedValue($request, 'date_reffered', $i)),
                        'notes' => $this->repeatedValue($request, 'notes', $i),
                        'services' => implode(',', $services),
                        'updated_by' => Auth::id(),
                    ];
                }

                $rows = array_values(array_filter($rows, function ($row) {
                    return $this->repeatedRowHasAnyValue($row, ['reported_to','refferedto','report_date','date_reffered','notes','services']);
                }));

                foreach ($rows as $row) {
                    if (!$row['reported_to'] || !$row['report_date']) {
                        throw new \App\Exceptions\DataQualityException('Each action row must have Reported To and Date Reported.');
                    }
                }

                $this->assertNoDuplicateRows('action', $rows, ['reported_to','refferedto','report_date','date_reffered','services','notes']);

                foreach ($rows as $row) {
                    $action = $this->findExistingSectionRecord(Action::class, $row['_id'] ?? null, 'victim_id', $victimid) ?: new Action();
                    unset($row['_id']);
                    $this->modelFillExistingColumns($action, $row);
                    if (!$action->exists && Schema::hasColumn($action->getTable(), 'created_by')) {
                        $action->created_by = Auth::id();
                    }
                    $action->saveOrFail();
                }
            });

            $this->auditCaseEvent('update_survivor_service_action', $victimid, ['section' => 'action']);
            $this->pushNsrSafely($victimid, 'service_action');

            return $this->redirectToCaseTab($victimid, 'status', 'Victim Services captured successfully', true);
        } catch (\Throwable $e) {
            $this->logWriteFailure('updateaction', $e, $request);

            return $this->redirectToCaseTab($victimid, 'action', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Victim services could not be saved. Please try again.', true);
        }
    }

    function processstatus(Request $request){
        $request->validate([
            'id' => 'required',
            'statuses_count' => 'nullable|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'statuses_count');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $row = [
                        'victimid' => $victimid,
                        'incident_status' => $this->repeatedValue($request, 'reportedto', $i),
                        'date_logged' => $this->formatNullableDate($this->repeatedValue($request, 'date_reported', $i)),
                        'details' => $this->repeatedValue($request, 'notes', $i),
                        'pendingpoint' => $this->repeatedValue($request, 'pendingpoint', $i),
                        'created_by' => Auth::id(),
                        'updated_by' => Auth::id(),
                    ];

                    if (!$this->repeatedRowHasAnyValue($row, ['incident_status','date_logged','details','pendingpoint'])) {
                        continue;
                    }

                    if (!$row['incident_status'] || !$row['date_logged']) {
                        throw new \App\Exceptions\DataQualityException('Each case status row must have Date and Case Status.');
                    }

                    $rows[] = $row;
                }

                $this->assertNoDuplicateRows('case status', $rows, ['incident_status','date_logged','details','pendingpoint']);

                foreach ($rows as $row) {
                    $status = new IncidentStatus();
                    $this->modelFillExistingColumns($status, $row);
                    $status->saveOrFail();
                }
            });

            $this->auditCaseEvent('save_case_status', $victimid, ['section' => 'status']);
            $this->pushNsrSafely($victimid, 'case_status');

            return redirect(url('dataentry/report?id=' . $this->publicVictimReference($victimid)))->with('message', 'Full Details of Incident have been Captured');
        } catch (\Throwable $e) {
            $this->logWriteFailure('processstatus', $e, $request);

            return $this->redirectToCaseTab($victimid, 'status', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Case status could not be recorded. Please try again.');
        }
    }

    function updatestatus(Request $request){
        $request->validate([
            'id' => 'required',
            'statuses_count' => 'nullable|integer|min:1',
        ]);

        $victim = $this->resolveVictimFromReference($request->input('id'));
        $victimid = $victim->id;
        $count = $this->repeatedCount($request, 'statuses_count');

        try {
            DB::transaction(function () use ($request, $victimid, $count) {
                $rows = [];

                for ($i = 0; $i < $count; $i++) {
                    $rows[] = [
                        '_id' => $this->repeatedValue($request, 'status_id', $i),
                        'victimid' => $victimid,
                        'incident_status' => $this->repeatedValue($request, 'reportedto', $i),
                        'date_logged' => $this->formatNullableDate($this->repeatedValue($request, 'date_reported', $i)),
                        'details' => $this->repeatedValue($request, 'notes', $i),
                        'pendingpoint' => $this->repeatedValue($request, 'pendingpoint', $i),
                        'updated_by' => Auth::id(),
                    ];
                }

                $rows = array_values(array_filter($rows, function ($row) {
                    return $this->repeatedRowHasAnyValue($row, ['incident_status','date_logged','details','pendingpoint']);
                }));

                foreach ($rows as $row) {
                    if (!$row['incident_status'] || !$row['date_logged']) {
                        throw new \App\Exceptions\DataQualityException('Each case status row must have Date and Case Status.');
                    }
                }

                $this->assertNoDuplicateRows('case status', $rows, ['incident_status','date_logged','details','pendingpoint']);

                foreach ($rows as $row) {
                    $status = $this->findExistingSectionRecord(IncidentStatus::class, $row['_id'] ?? null, 'victimid', $victimid) ?: new IncidentStatus();
                    unset($row['_id']);
                    $this->modelFillExistingColumns($status, $row);
                    if (!$status->exists && Schema::hasColumn($status->getTable(), 'created_by')) {
                        $status->created_by = Auth::id();
                    }
                    $status->saveOrFail();
                }
            });

            $this->auditCaseEvent('update_case_status', $victimid, ['section' => 'status']);
            $this->pushNsrSafely($victimid, 'case_status');

            return redirect(url('dataentry/report?id=' . $this->publicVictimReference($victimid)))->with('message', 'Full Details of Incident have been Captured');
        } catch (\Throwable $e) {
            $this->logWriteFailure('updatestatus', $e, $request);

            return $this->redirectToCaseTab($victimid, 'status', $e instanceof \App\Exceptions\DataQualityException ? $e->getMessage() : 'Case status could not be recorded. Please try again.', true);
        }
    }




    public function caseReport(Request $request)
    {
        $validated = $request->validate([
            'id' => ['required', 'string', 'max:255'],
        ]);

        $case = $this->resolveVictimFromReference($validated['id']);
        $victimId = $case->id;

        $this->auditCaseEvent('view_case_report', $victimId, ['section' => 'show']);

        $victim = DB::table('victim as v')
            ->leftJoin('gender as g', 'v.gender', '=', 'g.id')
            ->leftJoin('nationality as n', 'v.nationality', '=', 'n.id')
            ->leftJoin('national_status as ns', 'v.nationality_status', '=', 'ns.id')
            ->leftJoin('districts as d', 'v.districtid', '=', 'd.id')
            ->leftJoin('sub_counties as sc', 'v.subcountyid', '=', 'sc.id')
            ->leftJoin('parishes as p', 'v.parishid', '=', 'p.id')
            ->leftJoin('users as u', 'v.created_by', '=', 'u.id')
            ->select('v.*', 'g.name as gender_name', 'n.name as nationality_name', 'ns.name as national_status_name',
                'd.name as district_name', 'sc.name as subcounty_name', 'p.name as parish_name', 'u.name as created_by_name')
            ->where('v.id', $victimId)
            ->first();

        if (!$victim) {
            abort(404, 'Case record not found.');
        }

        $incidentOrderColumn = Schema::hasColumn('incident_data', 'created_at') ? 'id.created_at' : 'id.id';
        $incidents = DB::table('incident_data as id')
            ->leftJoin('incidents as i', 'id.incidentid', '=', 'i.id')
            ->leftJoin('timeofday as tod', 'id.incident_time', '=', 'tod.id')
            ->leftJoin('districts as d', 'id.districtid', '=', 'd.id')
            ->leftJoin('sub_counties as sc', 'id.subcountyid', '=', 'sc.id')
            ->leftJoin('parishes as p', 'id.parishid', '=', 'p.id')
            ->select('id.*', 'i.name as incident_name', 'tod.name as incident_time_name', 'd.name as district_name', 'sc.name as subcounty_name', 'p.name as parish_name')
            ->where('id.victimid', $victimId)
            ->orderBy($incidentOrderColumn, 'asc')
            ->get();

        $perpetratorOrderColumn = Schema::hasColumn('perpetrators', 'created_at') ? 'p.created_at' : 'p.id';
        $perpetrators = DB::table('perpetrators as p')
            ->leftJoin('gender as g', 'p.gender', '=', 'g.id')
            ->leftJoin('relations as r', 'p.relation', '=', 'r.id')
            ->select('p.*', 'g.name as gender_name', 'r.name as relation_name')
            ->where('p.victimid', $victimId)
            ->orderBy($perpetratorOrderColumn, 'asc')
            ->get();

        $witnessOrderColumn = Schema::hasColumn('witnesses', 'created_at') ? 'w.created_at' : 'w.id';
        $witnesses = DB::table('witnesses as w')
            ->leftJoin('gender as g', 'w.gender', '=', 'g.id')
            ->leftJoin('relations as r', 'w.relation', '=', 'r.id')
            ->select('w.*', 'g.name as gender_name', 'r.name as relation_name')
            ->where('w.victimid', $victimId)
            ->orderBy($witnessOrderColumn, 'asc')
            ->get();

        $actionOrderColumn = Schema::hasColumn('actions', 'created_at') ? 'a.created_at' : 'a.id';
        $actions = DB::table('actions as a')
            ->leftJoin('authorities as au', 'a.reported_to', '=', 'au.id')
            ->leftJoin('authorities as ar', 'a.refferedto', '=', 'ar.id')
            ->select('a.*', 'au.name as reported_to_name', 'ar.name as referred_to_name')
            ->where('a.victim_id', $victimId)
            ->orderBy($actionOrderColumn, 'asc')
            ->get()
            ->map(function ($row) {
                $ids = collect(explode(',', (string) $row->services))->map(function ($id) {
                    return trim($id);
                })->filter()->unique()->values()->all();

                $row->service_names = empty($ids) ? '-' : DB::table('services')
                    ->whereIn('id', $ids)
                    ->orderByRaw('FIELD(id, '.implode(',', array_map('intval', $ids)).')')
                    ->pluck('name')
                    ->implode(', ');

                return $row;
            });

        $statusOrderColumn = Schema::hasColumn('incident_status', 'created_at') ? 'ist.created_at' : 'ist.id';
        $statusQuery = DB::table('incident_status as ist')
            ->leftJoin('case_statuses as s', 'ist.incident_status', '=', 's.id')
            ->where('ist.victimid', $victimId)
            ->orderBy($statusOrderColumn, 'asc');

        if (Schema::hasColumn('incident_status', 'pendingpoint')) {
            $statusQuery->leftJoin('statuses as pp', 'ist.pendingpoint', '=', 'pp.id')
                ->select('ist.*', 's.name as status_name', 'pp.name as pending_point_name');
        } else {
            // Backward compatibility for databases that have not yet run the pending-point reconciliation.
            $statusQuery->select('ist.*', 's.name as status_name', DB::raw('NULL as pending_point_name'));
        }

        $statuses = $statusQuery->get();

        return view('dataentry.report', compact('victim', 'incidents', 'perpetrators', 'witnesses', 'actions', 'statuses'));
    }

}
