<?php

namespace App\Libraries\TPAClaimsImportServices;

use CodeIgniter\Database\BaseConnection;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use App\Models\TicketMasterModel;
use App\Models\EmployeeModel;
use App\Models\ClaimDumpFileModel;
use App\Models\ClaimsDumpFhplModel;
use App\Models\ClientPolicyModel;

use RuntimeException;

abstract class BaseTpaClaimImportService
{
    protected BaseConnection $db;
    protected $claimDumpFileModel;
    protected $clientPolicyModel;
    protected $policyNumberMapping;
    protected $tpaTableMapping;

    public function __construct()
    {
        $this->db = db_connect();
        $this->claimDumpFileModel = new ClaimDumpFileModel();
        $this->clientPolicyModel = new ClientPolicyModel();
        $this->policyNumberMapping = [
            (int) env('VIDAL_PRIMARY_KEY_CONSTANT') => 'Insurer Policy Number',
            (int) env('ABHI_PRIMARY_KEY_CONSTANT') => 'Policy Number',
            (int) env('MEDI_ASSIST_PRIMARY_KEY_CONSTANT') => 'policy_no',
            (int) env('FHPL_PRIMARY_KEY_CONSTANT') => 'Policy No',
            (int) env('R_CARE_PRIMARY_KEY_CONSTANT') => 'Policy Number',
            (int) env('ICICI_PRIMARY_KEY_CONSTANT') => 'POLICY_NO', 
        ];
        $this->tpaTableMapping = [
            (int) env('VIDAL_PRIMARY_KEY_CONSTANT') => 'claims_dump_vidal',
            (int) env('ABHI_PRIMARY_KEY_CONSTANT') => 'claims_dump_abhi',
            (int) env('MEDI_ASSIST_PRIMARY_KEY_CONSTANT') => 'claims_dump_medi_assist',
            (int) env('FHPL_PRIMARY_KEY_CONSTANT') => 'claims_dump_fhpl',
            (int) env('R_CARE_PRIMARY_KEY_CONSTANT') => 'claims_dump_reliance',
            (int) env('ICICI_PRIMARY_KEY_CONSTANT') => 'claims_dump_icici',
        ];
    }

    /**
     * Structured claim-dump process logging. Search logs with: [CLAIM_DUMP]
     * Uses error level so entries appear under production logger threshold.
     */
    protected function logClaimDump(string $level, string $step, array $context = []): void
    {
        $parts = [];
        foreach ($context as $key => $value) {
            if (is_bool($value)) {
                $parts[] = $key . '=' . ($value ? 'true' : 'false');
            } elseif (is_scalar($value) || $value === null) {
                $parts[] = $key . '=' . ($value === null ? 'null' : $value);
            } else {
                $parts[] = $key . '=' . json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            }
        }

        $suffix = $parts === [] ? '' : ' ' . implode(' ', $parts);
        // Keep severity in the message; always write as error for production visibility.
        log_message('error', '[CLAIM_DUMP][' . strtoupper($level) . '][' . $step . ']' . $suffix);
    }

    /**
     * First JOB for insert TPA wise Bulk Upload
     */
    public function runTpaClaimDumpInsert(string $filePath, int $fileId): array
    {
        $this->logClaimDump('info', 'JOB1_START', [
            'file_id'   => $fileId,
            'file_path' => $filePath,
        ]);

        // 1. Start Transaction
        $this->db->transBegin(); 

        try {
            $fileData = $this->claimDumpFileModel->where('id', $fileId)->first();
            if (empty($fileData)) {
                $this->logClaimDump('error', 'JOB1_FILE_NOT_FOUND', ['file_id' => $fileId]);
                return $this->failTpaClaimDumpInsert($fileId, 'Claim dump file record not found');
            }

            $client_policy_data = $this->clientPolicyModel->where('id', $fileData['client_policy_id'])->first();
            if (empty($client_policy_data)) {
                $this->logClaimDump('error', 'JOB1_POLICY_NOT_FOUND', [
                    'file_id' => $fileId,
                    'client_policy_id' => $fileData['client_policy_id'] ?? null,
                ]);
                return $this->failTpaClaimDumpInsert($fileId, 'Client policy not found');
            }

            $this->logClaimDump('info', 'JOB1_FILE_LOADED', [
                'file_id' => $fileId,
                'tpa_id' => $fileData['tpa_id'] ?? null,
                'client_id' => $fileData['client_id'] ?? null,
                'client_policy_id' => $fileData['client_policy_id'] ?? null,
                'policy_no' => $client_policy_data['policy_no'] ?? null,
            ]);

            // Determine sheet name logic...
            if (env('FHPL_PRIMARY_KEY_CONSTANT') == $fileData['tpa_id']) {
                $rows = $this->readExcelBySheetName($filePath, 'Claims&Preauth');
                $this->logClaimDump('info', 'JOB1_EXCEL_READ', ['file_id' => $fileId, 'sheet' => 'Claims&Preauth', 'rows' => count($rows)]);
            } else if (env('R_CARE_PRIMARY_KEY_CONSTANT') == $fileData['tpa_id']) {
                $rows = $this->readExcelBySheetName($filePath, 'CL');
                $this->logClaimDump('info', 'JOB1_EXCEL_READ', ['file_id' => $fileId, 'sheet' => 'CL', 'rows' => count($rows)]);
            } else {
                $rows = $this->readExcel($filePath);
                $this->logClaimDump('info', 'JOB1_EXCEL_READ', ['file_id' => $fileId, 'sheet' => 'active', 'rows' => count($rows)]);
            }

            if (empty($rows)) {
                $this->logClaimDump('error', 'JOB1_EMPTY_EXCEL', ['file_id' => $fileId]);
                return $this->failTpaClaimDumpInsert($fileId, 'Excel file contains no data or wrong file upload');
            }

            if ($this->fileHasStagingRows($fileId)) {
                $this->db->transRollback();
                $this->logClaimDump('info', 'JOB1_STAGING_EXISTS_SKIP', ['file_id' => $fileId]);
                return [
                    'status'  => true,
                    'message' => 'Claim dump staging already exists for this file; skipped re-insert.',
                ];
            }

            if(isset($this->policyNumberMapping[$fileData['tpa_id']]) && !empty($this->policyNumberMapping[$fileData['tpa_id']])){
                $policy_number_column = $this->policyNumberMapping[$fileData['tpa_id']];
            }else{
                $policy_number_column = 'policy_no';
            }

            $expectedPolicy = trim((string) ($client_policy_data['policy_no'] ?? ''));
            foreach ($rows as $index => $row) {
                $filePolicy = trim((string) ($row[$policy_number_column] ?? ''));
                if ($filePolicy === '' || $filePolicy !== $expectedPolicy) {
                    $rowNumber = $index + 2; // header is row 1
                    $this->logClaimDump('error', 'JOB1_POLICY_MISMATCH', [
                        'file_id' => $fileId,
                        'excel_row' => $rowNumber,
                        'expected' => $expectedPolicy,
                        'found' => $filePolicy,
                        'column' => $policy_number_column,
                    ]);
                    return $this->failTpaClaimDumpInsert(
                        $fileId,
                        "Policy number mismatch at Excel row {$rowNumber}. Expected '{$expectedPolicy}', found '{$filePolicy}'."
                    );
                }
            }
            $this->logClaimDump('info', 'JOB1_POLICY_VALIDATED', [
                'file_id' => $fileId,
                'policy_no' => $expectedPolicy,
                'rows_checked' => count($rows),
            ]);

            $tpaInsertData = $this->mapTPAData($rows, $fileId);
            $this->logClaimDump('info', 'JOB1_MAPPED', [
                'file_id' => $fileId,
                'mapped_count' => count($tpaInsertData),
            ]);

            if (empty($tpaInsertData)) {
                $this->logClaimDump('error', 'JOB1_NO_MAPPED_ROWS', ['file_id' => $fileId]);
                return $this->failTpaClaimDumpInsert($fileId, 'These records already exist in the system.');
            }

            $return_res = $this->bulkInsertTPATable($tpaInsertData);

            if ($return_res !== true) {
                $this->logClaimDump('error', 'JOB1_BULK_INSERT_FAILED', ['file_id' => $fileId]);
                return $this->failTpaClaimDumpInsert($fileId, 'TPA Import bulk insert failed');
            }

            // 2. Commit if everything is fine
            $this->db->transCommit();
            $this->logClaimDump('info', 'JOB1_SUCCESS', [
                'file_id' => $fileId,
                'record_count' => count($tpaInsertData),
            ]);
            return ['status' => true, 'message' => 'File uploaded successfully', 'record_count' => count($tpaInsertData)];

        } catch (\Throwable $e) {
            $this->logClaimDump('error', 'JOB1_EXCEPTION', [
                'file_id' => $fileId,
                'error' => $e->getMessage(),
                'line' => $e->getLine(),
                'file' => $e->getFile(),
            ]);
            return $this->failTpaClaimDumpInsert($fileId, 'System error : ' . $e->getMessage());
        }
    }

    /**
     * Second JOB for insert Ticket Master table after insert the TPA bulk upload success
     */
    public function runTicketMasterInsert(array $params): array
    {
        $file_id = (int) ($params['file_id'] ?? 0);
        $this->logClaimDump('info', 'JOB2_START', ['file_id' => $file_id]);

        // Outside the Job 2 transaction so a later rollback cannot restore orphan links.
        $orphansCleared = $this->clearOrphanDumpTicketLinks($file_id);
        if ($orphansCleared > 0) {
            $this->logClaimDump('info', 'JOB2_ORPHANS_CLEARED', [
                'file_id' => $file_id,
                'count' => $orphansCleared,
            ]);
        }

        // 1. Start manual transaction
        $this->db->transBegin();

        try {
            $ticketMasterData = $this->mapClaimMasterData($file_id);
            $this->logClaimDump('info', 'JOB2_MAPPED', [
                'file_id' => $file_id,
                'mapping_status' => !empty($ticketMasterData['status']),
                'inserts' => count($ticketMasterData['mapped_array'] ?? []),
                'status_updates' => count($ticketMasterData['status_update_array'] ?? []),
                'rejects_or_links' => count($ticketMasterData['rejected_reason_array'] ?? []),
                'already_processed' => !empty($ticketMasterData['already_processed']),
                'message' => $ticketMasterData['message'] ?? null,
            ]);

            // Check if mapping failed
            if (!$ticketMasterData['status']) {
                // Already-processed dump rows (ticket_id linked) must not be deleted as a "failure".
                if (!empty($ticketMasterData['already_processed'])) {
                    $this->db->transCommit();
                    $this->logClaimDump('info', 'JOB2_ALREADY_PROCESSED', ['file_id' => $file_id]);
                    return [
                        'status'  => true,
                        'message' => $ticketMasterData['message'] ?? 'Claim dump already processed for this file.',
                    ];
                }

                // Job 1 staging is already committed — roll back Job 2 only; keep dump for retry/export.
                $this->db->transRollback();
                $this->logClaimDump('error', 'JOB2_MAPPING_FAILED', [
                    'file_id' => $file_id,
                    'message' => $ticketMasterData['message'] ?? 'mapping failed',
                ]);
                return $ticketMasterData;
            }

            $message = $orphansCleared > 0
                ? "Cleared {$orphansCleared} orphan dump ticket_id link(s). "
                : '';
            $hasExecutedTask = false;
            $hasInserts = !empty($ticketMasterData['mapped_array']);
            $hasExistingTicketUpdates = !empty($ticketMasterData['status_update_array']);
            $hasRejectedReasons = !empty($ticketMasterData['rejected_reason_array']);
            $hasExistingTicketLinks = $this->hasExistingTicketLinkUpdates($ticketMasterData['rejected_reason_array'] ?? []);

            // Process Mapped Data
            if ($hasInserts) {
                $this->logClaimDump('info', 'JOB2_INSERT_TICKETS_START', [
                    'file_id' => $file_id,
                    'count' => count($ticketMasterData['mapped_array']),
                ]);
                $insert_res = $this->importClaimMaster($ticketMasterData['mapped_array']);
                if (!$insert_res) {
                    return $this->failTicketMasterInsert($file_id, 'Ticket Master Claim bulk insert failed');
                }
                // Map newly created ticket IDs back to the TPA staging table
                if (!$this->updateTicketIdInTPATable($file_id)) {
                    return $this->failTicketMasterInsert($file_id, 'Updating ticket_id in TPA table failed');
                }
                if (!$this->recordHistoryForNewDumpTickets($ticketMasterData['mapped_array'], $file_id)) {
                    return $this->failTicketMasterInsert($file_id, 'Recording ticket history for dump claims failed');
                }

                $message .= 'Ticket Master Claim bulk insert success. ';
                $hasExecutedTask = true;
                $this->logClaimDump('info', 'JOB2_INSERT_TICKETS_DONE', ['file_id' => $file_id]);
            }

            // Update existing tickets: status and/or missing claim_dump_ref_id
            if ($hasExistingTicketUpdates) {
                $this->logClaimDump('info', 'JOB2_UPDATE_EXISTING_START', [
                    'file_id' => $file_id,
                    'count' => count($ticketMasterData['status_update_array']),
                ]);
                $update_status_res = $this->updateExistingTicketStatuses($ticketMasterData['status_update_array']);
                if (!$update_status_res) {
                    return $this->failTicketMasterInsert($file_id, 'Updating existing tickets failed');
                }
                if (!$this->recordHistoryForExistingTicketUpdates($ticketMasterData['status_update_array'])) {
                    return $this->failTicketMasterInsert($file_id, 'Recording ticket history for status updates failed');
                }

                $message .= 'Existing ticket updated successfully. ';
                $hasExecutedTask = true;
                $this->logClaimDump('info', 'JOB2_UPDATE_EXISTING_DONE', ['file_id' => $file_id]);
            }

            // Process dump link / reject updates (ticket_id and/or master_reject_reason)
            if ($hasRejectedReasons) {
                $this->logClaimDump('info', 'JOB2_DUMP_LINK_REJECT_START', [
                    'file_id' => $file_id,
                    'count' => count($ticketMasterData['rejected_reason_array']),
                    'has_links' => $hasExistingTicketLinks,
                ]);
                $update_res = $this->updateTicketMasterRejectedReasonInTPATable(
                    $this->normalizeDumpLinkOrRejectUpdates($ticketMasterData['rejected_reason_array'])
                );
                if ($update_res === false) {
                    return $this->failTicketMasterInsert($file_id, 'Updating rejected reasons failed');
                }

                if ($hasExistingTicketLinks) {
                    $message .= 'Existing claim dump ticket_id linked successfully. ';
                } elseif (!$hasInserts && !$hasExistingTicketUpdates) {
                    $message .= 'Those employee or dependent not in our system. ';
                } else {
                    $message .= 'Ticket Master Claim rejected reason updated successfully. ';
                }
                $hasExecutedTask = true;
                $this->logClaimDump('info', 'JOB2_DUMP_LINK_REJECT_DONE', ['file_id' => $file_id]);
            }

            // If nothing was processed but no error occurred
            if (!$hasExecutedTask) {
                return $this->failTicketMasterInsert($file_id, 'No data found to process.');
            }

            // 2. Commit the transaction
            $this->db->transCommit();

            // Rejection-only runs are still a completed Job 2 — keep dump rows for error export.
            $status = $hasInserts || $hasExistingTicketUpdates || $hasExistingTicketLinks || $hasRejectedReasons;

            $this->logClaimDump('info', 'JOB2_SUCCESS', [
                'file_id' => $file_id,
                'status' => $status,
                'has_inserts' => $hasInserts,
                'has_existing_updates' => $hasExistingTicketUpdates,
                'has_links' => $hasExistingTicketLinks,
                'has_rejects' => $hasRejectedReasons,
                'message' => trim($message),
            ]);

            return ['status' => $status, 'message' => trim($message)];

        } catch (\Throwable $th) {
            $fileId = (int) ($params['file_id'] ?? 0);
            $this->logClaimDump('error', 'JOB2_EXCEPTION', [
                'file_id' => $fileId,
                'error' => $th->getMessage(),
                'line' => $th->getLine(),
                'file' => $th->getFile(),
            ]);
            return $this->failTicketMasterInsert(
                $fileId,
                'System error during Ticket Master Insert: ' . $th->getMessage()
            );
        }
    }

    /**
     * Clear dump ticket_id (and reject reason) when the linked ticket is missing or inactive.
     * Makes orphan rows pending again so Job 2 can recreate tickets.
     *
     * @return int Number of dump rows reset
     */
    protected function clearOrphanDumpTicketLinks(int $fileId): int
    {
        if ($fileId <= 0) {
            return 0;
        }

        $fileData = $this->claimDumpFileModel->where('id', $fileId)->first();
        if (empty($fileData)) {
            return 0;
        }

        $tpaId = (int) ($fileData['tpa_id'] ?? 0);
        $tpaTable = $this->tpaTableMapping[$tpaId] ?? null;

        if ($tpaTable === null || !in_array($tpaTable, $this->tpaTableMapping, true)) {
            return 0;
        }

        $sql = "UPDATE `{$tpaTable}` d
                LEFT JOIN ticket_master tm ON tm.id = d.ticket_id AND tm.is_active = 1
                SET d.ticket_id = NULL,
                    d.master_reject_reason = NULL
                WHERE d.file_id = ?
                  AND d.is_active = 1
                  AND d.ticket_id IS NOT NULL
                  AND tm.id IS NULL";

        $this->db->query($sql, [$fileId]);

        return $this->db->affectedRows();
    }

    /**
     * Clear orphan dump ticket_id links (ticket missing/inactive), then run Job 2
     * so those dump rows are inserted into ticket_master again.
     */
    public function recreateOrphanDumpTickets(int $fileId): array
    {
        $this->logClaimDump('info', 'RECREATE_ORPHANS_START', ['file_id' => $fileId]);

        if ($fileId <= 0) {
            return ['status' => false, 'message' => 'Invalid file id'];
        }

        $fileData = $this->claimDumpFileModel->where('id', $fileId)->first();
        if (empty($fileData)) {
            return ['status' => false, 'message' => 'claim_dump_files row not found'];
        }

        $cleared = $this->clearOrphanDumpTicketLinks($fileId);
        $this->logClaimDump('info', 'RECREATE_ORPHANS_CLEARED', [
            'file_id' => $fileId,
            'cleared' => $cleared,
        ]);

        // Job 2 also clears orphans; running insert/link for now-pending dump rows.
        $result = $this->runTicketMasterInsert(['file_id' => $fileId]);
        $result['orphans_cleared'] = $cleared;

        $this->logClaimDump(
            !empty($result['status']) ? 'info' : 'error',
            'RECREATE_ORPHANS_DONE',
            [
                'file_id' => $fileId,
                'cleared' => $cleared,
                'status' => !empty($result['status']),
                'message' => $result['message'] ?? null,
            ]
        );

        return $result;
    }

    /**
     * Soft-delete dump staging + tickets created by this upload (admin truncate / undo).
     * Does not deactivate existing tickets that were only linked from this dump (different file_id).
     */
    public function softTruncateClaimDump(int $fileId): array
    {
        $this->logClaimDump('info', 'TRUNCATE_START', ['file_id' => $fileId]);

        if ($fileId <= 0) {
            $this->logClaimDump('error', 'TRUNCATE_INVALID_FILE_ID', ['file_id' => $fileId]);
            return ['status' => false, 'message' => 'Invalid file id'];
        }

        $fileData = $this->claimDumpFileModel->where('id', $fileId)->where('is_active', 1)->first();
        if (empty($fileData)) {
            $this->logClaimDump('error', 'TRUNCATE_FILE_NOT_FOUND', ['file_id' => $fileId]);
            return ['status' => false, 'message' => 'File not found or already truncated'];
        }

        if (($fileData['status'] ?? '') === 'processing') {
            $this->logClaimDump('error', 'TRUNCATE_BLOCKED_PROCESSING', ['file_id' => $fileId]);
            return ['status' => false, 'message' => 'Cannot truncate while import is processing'];
        }

        $tpaId = (int) ($fileData['tpa_id'] ?? 0);
        $tpaTable = $this->tpaTableMapping[$tpaId] ?? null;
        if ($tpaTable === null || !in_array($tpaTable, $this->tpaTableMapping, true)) {
            $this->logClaimDump('error', 'TRUNCATE_UNSUPPORTED_TPA', [
                'file_id' => $fileId,
                'tpa_id' => $tpaId,
            ]);
            return ['status' => false, 'message' => 'Unsupported TPA for truncate'];
        }

        $this->db->transBegin();

        try {
            $ticketIds = $this->db->table('ticket_master')
                ->select('id, claim_status_id, created_by')
                ->where('file_id', $fileId)
                ->where('is_active', 1)
                ->get()
                ->getResultArray();

            $this->logClaimDump('info', 'TRUNCATE_TICKETS_FOUND', [
                'file_id' => $fileId,
                'tpa_table' => $tpaTable,
                'ticket_count' => count($ticketIds),
            ]);

            // Soft-deactivate dump rows only — keep ticket_id for audit/traceability.
            $this->db->table($tpaTable)
                ->where('file_id', $fileId)
                ->where('is_active', 1)
                ->set(['is_active' => 0])
                ->update();
            $dumpAffected = $this->db->affectedRows();

            $this->db->table('ticket_master')
                ->where('file_id', $fileId)
                ->where('is_active', 1)
                ->set(['is_active' => 0])
                ->update();
            $ticketAffected = $this->db->affectedRows();

            // Avoid Model callbacks that call get_session_userid() (breaks CLI / after output).
            $actorId = null;
            try {
                if (function_exists('get_session_userid')) {
                    $actorId = get_session_userid();
                }
            } catch (\Throwable $e) {
                $actorId = null;
            }

            if ($ticketIds !== []) {
                $historyRows = [];
                $now = date('Y-m-d H:i:s');
                foreach ($ticketIds as $ticket) {
                    $historyRows[] = [
                        'ticket_id'    => $ticket['id'],
                        'field_name'   => 'claim_status_id',
                        'display_name' => 'Claim Removed (TPA Dump Truncate)',
                        'old_value'    => $ticket['claim_status_id'],
                        'new_value'    => null,
                        'created_by'   => $ticket['created_by'] ?? $actorId,
                        'created_at'   => $now,
                        'is_active'    => 1,
                    ];
                }
                if ($this->db->table('ticket_history')->insertBatch($historyRows) === false) {
                    throw new RuntimeException('Failed to write truncate ticket history');
                }
            }

            $this->db->table('claim_dump_files')
                ->where('id', $fileId)
                ->update([
                    'is_active'  => 0,
                    'status'     => 'truncated',
                    'updated_by' => $actorId,
                    'updated_at' => date('Y-m-d H:i:s'),
                ]);

            if ($this->db->transStatus() === false) {
                $this->db->transRollback();
                $this->logClaimDump('error', 'TRUNCATE_DB_FAILED', ['file_id' => $fileId]);
                return ['status' => false, 'message' => 'Truncate failed'];
            }

            $this->db->transCommit();

            $this->logClaimDump('info', 'TRUNCATE_SUCCESS', [
                'file_id' => $fileId,
                'dump_rows_deactivated' => $dumpAffected,
                'tickets_deactivated' => $ticketAffected,
            ]);

            return [
                'status'  => true,
                'message' => 'Claim dump truncated successfully',
                'tickets_deactivated' => count($ticketIds),
            ];
        } catch (\Throwable $e) {
            $this->db->transRollback();
            $this->logClaimDump('error', 'TRUNCATE_EXCEPTION', [
                'file_id' => $fileId,
                'error' => $e->getMessage(),
            ]);
            return ['status' => false, 'message' => 'Truncate failed: ' . $e->getMessage()];
        }
    }

    /**
     * Soft-delete TPA staging rows and ticket_master rows for a failed Job 1 dump upload.
     * Never touches tickets unless the TPA dump table is resolved (avoids orphan ticket_id links).
     */
    protected function cleanupClaimDumpData(int $fileId): void
    {
        if ($fileId <= 0) {
            return;
        }

        $fileData = $this->claimDumpFileModel->where('id', $fileId)->first();
        if (empty($fileData)) {
            $this->logClaimDump('warning', 'CLEANUP_FILE_NOT_FOUND', ['file_id' => $fileId]);
            return;
        }

        $tpaId = (int) ($fileData['tpa_id'] ?? 0);
        $tpaTable = $this->tpaTableMapping[$tpaId] ?? null;

        if ($tpaTable === null) {
            $this->logClaimDump('error', 'CLEANUP_UNKNOWN_TPA', [
                'file_id' => $fileId,
                'tpa_id' => $tpaId,
            ]);
            return;
        }

        $this->db->table($tpaTable)
            ->where('file_id', $fileId)
            ->where('is_active', 1)
            ->set([
                'is_active' => 0,
                'ticket_id' => null,
            ])
            ->update();
        $dumpAffected = $this->db->affectedRows();

        $this->db->table('ticket_master')
            ->where('file_id', $fileId)
            ->where('is_active', 1)
            ->set(['is_active' => 0])
            ->update();
        $ticketAffected = $this->db->affectedRows();

        $this->logClaimDump('info', 'CLEANUP_DONE', [
            'file_id' => $fileId,
            'tpa_table' => $tpaTable,
            'dump_rows_deactivated' => $dumpAffected,
            'tickets_deactivated' => $ticketAffected,
        ]);
    }

    protected function rollbackAndCleanupClaimDumpData(int $fileId): void
    {
        $this->logClaimDump('warning', 'JOB1_ROLLBACK_AND_CLEANUP', ['file_id' => $fileId]);
        $this->db->transRollback();
        $this->cleanupClaimDumpData($fileId);
    }

    protected function failTpaClaimDumpInsert(int $fileId, string $message): array
    {
        $this->logClaimDump('error', 'JOB1_FAIL', [
            'file_id' => $fileId,
            'message' => $message,
        ]);
        $this->rollbackAndCleanupClaimDumpData($fileId);
        return ['status' => false, 'message' => $message];
    }

    /**
     * Job 2 failure: roll back this transaction only.
     * Do not delete committed Job 1 dump rows so the file can be retried.
     */
    protected function failTicketMasterInsert(int $fileId, string $message): array
    {
        $this->logClaimDump('error', 'JOB2_FAIL', [
            'file_id' => $fileId,
            'message' => $message,
        ]);
        $this->db->transRollback();
        return ['status' => false, 'message' => $message];
    }

    /**
     * Read Excel and return associative rows (header based)
     */
    protected function readExcel(string $filePath): array
    {   
        helper('excel_util_helper');

        if (!file_exists($filePath)) {
            throw new RuntimeException("File not found: {$filePath}");
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();

        $rows = $sheet->toArray(null, true, true, true);

        // dd($rows);

        if (count($rows) < 2) {
            return [];
        }

        // First row is header
        $headers = array_shift($rows);
        $headers = array_map('trim', $headers);

        $data = [];

        foreach ($rows as $row) {

            if (check_row_is_empty_or_null($row)) {
                continue;
            }

            $item = [];
            foreach ($headers as $key => $headerName) {
                if ($headerName !== '') {
                    $item[$headerName] = $row[$key] ?? null;
                }
            }
            $data[] = $item;
        }

        return $data;
    }

    /**
     * Read Excel By Sheet name and return associative rows (header based)
     */

    protected function readExcelBySheetName(string $filePath, string $sheetName): array
    {
        if (!file_exists($filePath)) {
            throw new RuntimeException("File not found: {$filePath}");
        }

        $spreadsheet = IOFactory::load($filePath);

        // Get sheet by name
        $sheet = $spreadsheet->getSheetByName($sheetName);

        if ($sheet === null) {
            throw new RuntimeException("Sheet '{$sheetName}' not found in Excel file");
        }

        $rows = $sheet->toArray(null, true, true, true);

        // Need at least header + one row
        if (count($rows) < 2) {
            return [];
        }

        // First row = headers
        $headers = array_shift($rows);
        $headers = array_map('trim', $headers);

        $data = [];

        foreach ($rows as $row) {
            // Skip completely empty rows
            if (!array_filter($row)) {
                continue;
            }

            $item = [];

            foreach ($headers as $key => $headerName) {
                if ($headerName !== '') {
                    $item[$headerName] = $row[$key] ?? null;
                }
            }

            $data[] = $item;
        }

        return $data;
    }


    /**
     * Dublicate check in the ticket_master table records
     */
    protected function checkDublicateTicketMasterClaim(array $param): bool
    {   
        return $this->getExistingTicketMasterClaim($param) !== null;
    }

    /**
     * Fetch existing ticket_master record matching claim identity keys.
     * Scoped by client_id / client_policy_id when provided to avoid cross-tenant matches.
     */
    protected function getExistingTicketMasterClaim(array $param): ?array
    {
        $ticketMaster = new TicketMasterModel();
        $builder = $ticketMaster
            ->where('doa', $param['doa'])
            ->where('tpa_no', $param['tpa_no'])
            ->where('claim_amount', $param['claim_amount'])
            ->where('emp_code', $param['emp_code'])
            ->where('is_active', 1);

        if (!empty($param['client_id'])) {
            $builder->where('client_id', $param['client_id']);
        }
        if (!empty($param['client_policy_id'])) {
            $builder->where('client_policy_id', $param['client_policy_id']);
        }

        $ticket = $builder->orderBy('id', 'DESC')->first();

        return $ticket ?: null;
    }

    /**
     * Update existing ticket_master rows (status and/or claim_dump_ref_id).
     */
    protected function updateExistingTicketStatuses(array $statusUpdates): bool
    {
        if (empty($statusUpdates)) {
            return true;
        }

        $payload = [];
        foreach ($statusUpdates as $update) {
            $row = $update;
            unset($row['old_claim_status_id']);
            $payload[] = $row;
        }

        $ticketMasterModel = new TicketMasterModel();
        $ok = $ticketMasterModel->updateBatch($payload, 'id') !== false;
        if ($ok) {
            $ids = [];
            foreach ($payload as $row) {
                if (!empty($row['id'])) {
                    $ids[] = (int) $row['id'];
                }
            }
            // updateBatch does not fire model afterUpdate callbacks
            (new \App\Models\ClaimReportModel())->syncFromTicketIds($ids);
        }

        return $ok;
    }

    /**
     * Write ticket_history for newly created dump tickets (by claim_dump_ref_id + file_id).
     */
    protected function recordHistoryForNewDumpTickets(array $mappedTickets, int $fileId): bool
    {
        if (empty($mappedTickets) || $fileId <= 0) {
            return true;
        }

        $refIds = [];
        foreach ($mappedTickets as $row) {
            if (!empty($row['claim_dump_ref_id'])) {
                $refIds[] = (int) $row['claim_dump_ref_id'];
            }
        }
        $refIds = array_values(array_unique($refIds));
        if ($refIds === []) {
            return true;
        }

        $tickets = $this->db->table('ticket_master')
            ->select('id, claim_status_id, created_by')
            ->where('file_id', $fileId)
            ->whereIn('claim_dump_ref_id', $refIds)
            ->where('is_active', 1)
            ->get()
            ->getResultArray();

        if ($tickets === []) {
            return true;
        }

        $historyRows = [];
        foreach ($tickets as $ticket) {
            $historyRows[] = [
                'ticket_id'    => $ticket['id'],
                'field_name'   => 'claim_status_id',
                'display_name' => 'Claim Created (TPA Dump)',
                'old_value'    => null,
                'new_value'    => $ticket['claim_status_id'],
                'created_by'   => $ticket['created_by'] ?? null,
                'is_active'    => 1,
            ];
        }

        $historyModel = new \App\Models\TicketHistoryModel();
        return $historyModel->insertBatch($historyRows) !== false;
    }

    /**
     * Write ticket_history when dump import updates an existing ticket's claim_status_id.
     */
    protected function recordHistoryForExistingTicketUpdates(array $statusUpdates): bool
    {
        if (empty($statusUpdates)) {
            return true;
        }

        $historyRows = [];
        foreach ($statusUpdates as $update) {
            if (empty($update['id']) || !array_key_exists('claim_status_id', $update)) {
                continue;
            }

            $historyRows[] = [
                'ticket_id'    => $update['id'],
                'field_name'   => 'claim_status_id',
                'display_name' => 'Claim Status Updated (TPA Dump)',
                'old_value'    => $update['old_claim_status_id'] ?? null,
                'new_value'    => $update['claim_status_id'],
                'created_by'   => $update['updated_by'] ?? null,
                'is_active'    => 1,
            ];
        }

        if ($historyRows === []) {
            return true;
        }

        $historyModel = new \App\Models\TicketHistoryModel();
        return $historyModel->insertBatch($historyRows) !== false;
    }

    /**
     * True when rejected_reason rows include ticket_id links for existing tickets.
     */
    protected function hasExistingTicketLinkUpdates(array $rejectedReasonArray): bool
    {
        foreach ($rejectedReasonArray as $row) {
            if (!empty($row['ticket_id'])) {
                return true;
            }
        }

        return false;
    }

    /**
     * Ensure link and reject dump updates share the same columns for updateBatch.
     */
    protected function normalizeDumpLinkOrRejectUpdates(array $rows): array
    {
        $normalized = [];

        foreach ($rows as $row) {
            if (empty($row['id'])) {
                continue;
            }

            $normalized[] = [
                'id'                   => $row['id'],
                'ticket_id'            => $row['ticket_id'] ?? null,
                'master_reject_reason' => array_key_exists('master_reject_reason', $row)
                    ? $row['master_reject_reason']
                    : null,
            ];
        }

        return $normalized;
    }

    /**
     * If ticket already exists:
     * - update claim_status_id when changed
     * - set claim_dump_ref_id on ticket_master when null
     * - set ticket_id on TPA dump row when null (via rejected_reason_array)
     *
     * Link updates do not write master_reject_reason (that field is for true rejects only).
     *
     * Returns true when the row was handled as an existing ticket (caller should continue).
     */
    protected function handleExistingTicketStatusUpdate(
        ?array $existingTicket,
        int $newStatusId,
        int $dumpRowId,
        array &$statusUpdateArray,
        array &$rejectedReasonArray
    ): bool {
        if (empty($existingTicket)) {
            return false;
        }

        $ticketId = $existingTicket['id'];
        $currentStatusId = (int) ($existingTicket['claim_status_id'] ?? 0);
        $statusChanged = $currentStatusId !== (int) $newStatusId;
        $claimDumpRefIdMissing = empty($existingTicket['claim_dump_ref_id']);

        $ticketUpdate = ['id' => $ticketId];

        if ($statusChanged) {
            $ticketUpdate['claim_status_id'] = $newStatusId;
            $ticketUpdate['old_claim_status_id'] = $currentStatusId;
        }

        if ($claimDumpRefIdMissing) {
            $ticketUpdate['claim_dump_ref_id'] = $dumpRowId;
        }

        if (count($ticketUpdate) > 1) {
            $statusUpdateArray[] = $ticketUpdate;
        }

        // Dump rows reaching here already have ticket_id NULL; link them to the existing ticket.
        $rejectedReasonArray[] = [
            'id'        => $dumpRowId,
            'ticket_id' => $ticketId,
        ];

        return true;
    }

    /**
     * Dublicate check in the TPA specific table records
     */
    protected function checkDuplicateTpaClaim(string $table, array $params): bool
    {   
        return $this->db->table($table)
            ->where($params)
            ->where('is_active', 1)
            ->countAllResults() > 0;
    }

    /**
     * Dublicate check in the TPA specific table records
     */
    protected function getTpaClaimDumpData(string $table, array $params): array
    {   
        return $this->db
            ->table($table)
            ->where('is_active', 1)
            ->where('file_id', $params['file_id'])
            ->where('ticket_id IS NULL')
            ->where('master_reject_reason IS NULL')
            ->get()
            ->getResultArray();
    }

    /**
     * Response when no pending dump rows are left for ticket_master mapping.
     * Orphan ticket_id links are cleared before mapping (see clearOrphanDumpTicketLinks).
     * Remaining linked/rejected rows are treated as already processed (not a wipe failure).
     */
    protected function emptyClaimMasterMappingResponse(string $table, int $fileId): array
    {
        $alreadyProcessed = $this->db->table($table)
            ->where('file_id', $fileId)
            ->where('is_active', 1)
            ->groupStart()
                ->where('ticket_id IS NOT NULL', null, false)
                ->orWhere('master_reject_reason IS NOT NULL', null, false)
            ->groupEnd()
            ->countAllResults() > 0;

        if ($alreadyProcessed) {
            return [
                'status' => false,
                'already_processed' => true,
                'message' => 'Claim dump already processed for this file.',
            ];
        }

        return ['status' => false, 'message' => 'No data to insert in TICKET MASTER'];
    }

    /**
     * Resolve employee (self) and insured member for a claim dump row.
     */
    public function getEmployeeDetails(int $client_id, int $client_policy_id, ?string $emp_code, ?string $relation = null): array
    {
        if ($emp_code === null || trim($emp_code) === '') {
            return [];
        }

        $emp_code = trim($emp_code);
        $relation = $relation !== null ? strtolower(trim($relation)) : '';

        $client_policy_data = $this->clientPolicyModel->where('id', $client_policy_id)->first();
        if (!empty($client_policy_data)) {
            if (!empty($client_policy_data['base_policy'])) {
               $client_policy_id = $client_policy_data['base_policy'];
            }
        }

        $EmployeeModel = new EmployeeModel();

        // When relation is unknown, still resolve the employee (self); insured join is skipped.
        $insuredJoin = "insured.emp_code = employees.emp_code
                AND insured.client_id = employees.client_id";
        if ($relation !== '') {
            $insuredJoin .= " AND LOWER(insured.relationship) = " . $EmployeeModel->db->escape($relation);
        } else {
            // No usable relation — do not match any insured row.
            $insuredJoin .= " AND 1 = 0";
        }

        $employeeData = $EmployeeModel
            ->select([
                'employees.id AS id',
                'employees.email_corporate AS emp_mail',
                'employees.mobile AS emp_mobile',
                'employees.name AS name',
                'employees.emp_code AS emp_code',

                // insured employee
                'insured.id AS insured_emp_id',
                'insured.name AS insured_name'
            ])
            ->join(
                'employee_polices',
                'employees.id = employee_polices.employee_id'
            )
            ->join(
                'employees AS insured',
                $insuredJoin,
                'left'
            )
            ->where('employees.is_active', 1)
            ->where('employee_polices.is_active', 1)
            ->where('employee_polices.client_policy_id', $client_policy_id)
            ->where('employees.client_id', $client_id)
            ->where('employees.emp_code', $emp_code)
            ->where('LOWER(employees.relationship)', 'self')
            ->first();


        return $employeeData ?? [];
    }

    public function checkStatusMapping($statusArray, $statusString)
    {
        $statusString = trim((string) $statusString);
        if ($statusString === '') {
            return null;
        }

        foreach ($statusArray as $key => $value) {
            if (strtolower($statusString) === strtolower(trim((string) $key))) {
                return $value;
            }
        }

        return null;
    }

    /**
     * Map dump status text to claim_status_id.
     * Falls back to 61 when status is empty or not found in the mapping.
     */
    protected function resolveClaimStatusId(
        array $statusMapping,
        ?string $statusString,
        int $dumpRowId,
        array &$rejectedReasonArray
    ): ?int {
        $statusString = trim((string) ($statusString ?? ''));

        if ($statusString === '') {
            return 61;
        }

        $statusId = $this->checkStatusMapping($statusMapping, $statusString);
        if ($statusId === null) {
            return 61;
        }

        return (int) $statusId;
    }

    /**
     * True when this file already has staging rows in its TPA dump table.
     */
    protected function fileHasStagingRows(int $fileId): bool
    {
        if ($fileId <= 0) {
            return false;
        }

        $fileData = $this->claimDumpFileModel->where('id', $fileId)->first();
        if (empty($fileData)) {
            return false;
        }

        $tpaId = (int) ($fileData['tpa_id'] ?? 0);
        $tpaTable = $this->tpaTableMapping[$tpaId] ?? null;
        if ($tpaTable === null || !in_array($tpaTable, $this->tpaTableMapping, true)) {
            return false;
        }

        return $this->db->table($tpaTable)
            ->where('file_id', $fileId)
            ->where('is_active', 1)
            ->countAllResults() > 0;
    }

    /**
     * Map Excel rows to TPA table structure
     */
    abstract protected function mapTPAData(array $rows, $fileId): array;

    /**
     * Map DB rows to Ticket Master table structure
     */
    abstract protected function mapClaimMasterData($fileId): array;

    /**
     * Insert into TPA-specific table (bulk)
     */
    abstract protected function bulkInsertTPATable(array $data): bool;

    /**
     * Insert into Ticket-Master-specific table (bulk)
     */
    abstract protected function importClaimMaster(array $data): bool;

    /**
     * Update TPA table with ticket_master primary key for a given file.
     */
    abstract protected function updateTicketIdInTPATable(int $fileId): bool;

    /**
     * Update TPA table with ticket_master insert rejected reason
     */
    abstract protected function updateTicketMasterRejectedReasonInTPATable(array $data): bool;
}