<?php

namespace App\Commands;

use App\Libraries\TpaClaimsImportFactory;
use CodeIgniter\CLI\BaseCommand;
use CodeIgniter\CLI\CLI;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use ReflectionClass;

/**
 * End-to-end TPA claim dump test: generate Excel → Job 1 → Job 2.
 *
 * Usage:
 *   php spark tpa:e2e-pipeline --tpa=all
 *   php spark tpa:e2e-pipeline --tpa=icici --apply
 *   php spark tpa:e2e-pipeline --tpa-id=6 --apply
 *   php spark tpa:e2e-pipeline --tpa=all --apply --truncate
 *   php spark tpa:e2e-pipeline --tpa=icici --status-cases --apply
 */
class TestTpaE2ePipeline extends BaseCommand
{
    protected $group       = 'TPA';
    protected $name        = 'tpa:e2e-pipeline';
    protected $description = 'Generate TPA Excel and run Job1 + Job2 end-to-end';
    protected $usage       = 'tpa:e2e-pipeline [--tpa=NAME|all] [--tpa-id=ID] [--apply] [--truncate] [--new-status=STATUS] [--status-cases]';
    protected $options     = [
        '--tpa'          => 'TPA key: icici|abhi|mediassist|fhpl|rcare|vidal|all (default: all)',
        '--tpa-id'       => 'Numeric TPA primary key (overrides --tpa)',
        '--apply'        => 'Run Job 1 + Job 2 (default is generate Excel + file row only)',
        '--truncate'     => 'Soft-truncate the test file after a successful apply',
        '--new-status'   => 'Override dump status string written into Excel',
        '--status-cases' => 'Write 3 rows: empty status, unmapped status, mapped status (expect 61/61/mapped id)',
    ];

    /**
     * Per-TPA Excel seed config.
     * excel_fields: excel header => source key from ticket/policy context
     */
    private function tpaConfigs(): array
    {
        return [
            'icici' => [
                'env'              => 'ICICI_PRIMARY_KEY_CONSTANT',
                'table'            => 'claims_dump_icici',
                'sheet'            => null,
                'default_status'   => 'REJECTED',
                'status_header'    => 'Updated_status',
                'status_db_column' => 'updated_status',
                'mapped_status'    => 'PAID',
                'mapped_status_id' => 11,
                'excel_fields'     => [
                    'POLICY_NO'          => 'policy_no',
                    'UHID'               => 'tpa_no',
                    'EMPLOYEE_MEMBER_ID' => 'emp_code',
                    'RELATION'           => 'relation',
                    'CLAIMED_AMOUNT'     => 'claim_amount',
                    'DOA'                => 'doa',
                    'Updated_status'     => 'status_value',
                ],
            ],
            'abhi' => [
                'env'              => 'ABHI_PRIMARY_KEY_CONSTANT',
                'table'            => 'claims_dump_abhi',
                'sheet'            => null,
                'default_status'   => 'Rejected',
                'status_header'    => 'Claim Status',
                'status_db_column' => 'claim_status',
                'mapped_status'    => 'Paid',
                'mapped_status_id' => 11,
                'excel_fields'     => [
                    'Policy Number' => 'policy_no',
                    'HEALTHCARD_ID' => 'tpa_no',
                    'Member Code'   => 'emp_code',
                    'Relation'      => 'relation',
                    'Claimed Amount'=> 'claim_amount',
                    'DOA'           => 'doa',
                    'Claim Status'  => 'status_value',
                ],
            ],
            'mediassist' => [
                'env'              => 'MEDI_ASSIST_PRIMARY_KEY_CONSTANT',
                'table'            => 'claims_dump_medi_assist',
                'sheet'            => null,
                'default_status'   => 'Rejected',
                'status_header'    => 'claim_status',
                'status_db_column' => 'claim_status',
                'mapped_status'    => 'Paid',
                'mapped_status_id' => 11,
                'excel_fields'     => [
                    'policy_no'              => 'policy_no',
                    'event_id'               => 'tpa_no',
                    'pribenef_employee_code' => 'emp_code',
                    'benef_relation'         => 'relation',
                    'claim_amount'           => 'claim_amount',
                    'date_of_admission'      => 'doa',
                    'claim_status'           => 'status_value',
                ],
            ],
            'fhpl' => [
                'env'              => 'FHPL_PRIMARY_KEY_CONSTANT',
                'table'            => 'claims_dump_fhpl',
                'sheet'            => 'Claims&Preauth',
                'default_status'   => 'Rejected',
                'status_header'    => 'Current Claim Status',
                'status_db_column' => 'current_claim_status',
                'mapped_status'    => 'Paid',
                'mapped_status_id' => 11,
                'excel_fields'     => [
                    'Policy No'            => 'policy_no',
                    'UHIDNO'               => 'tpa_no',
                    'employeeid'           => 'emp_code',
                    'relationship'         => 'relation',
                    'claimamount'          => 'claim_amount',
                    'Admdate'              => 'doa',
                    'Current Claim Status' => 'status_value',
                ],
            ],
            'rcare' => [
                'env'              => 'R_CARE_PRIMARY_KEY_CONSTANT',
                'table'            => 'claims_dump_reliance',
                'sheet'            => 'CL',
                'default_status'   => 'Rejected',
                'status_header'    => 'Final Status',
                'status_db_column' => 'final_status',
                'mapped_status'    => 'Paid',
                'mapped_status_id' => 11,
                'excel_fields'     => [
                    'Policy Number'          => 'policy_no',
                    'UHID'                   => 'tpa_no',
                    'Employee/Member Id'     => 'emp_code',
                    'Relation'               => 'relation',
                    'Claimed Amount'         => 'claim_amount',
                    'DOA/OPD Treatment From' => 'doa',
                    'Final Status'           => 'status_value',
                ],
            ],
            'vidal' => [
                'env'              => 'VIDAL_PRIMARY_KEY_CONSTANT',
                'table'            => 'claims_dump_vidal',
                'sheet'            => null,
                'default_status'   => 'Rejected',
                'status_header'    => 'Claim Status',
                'status_db_column' => 'claim_status',
                'mapped_status'    => 'Paid',
                'mapped_status_id' => 11,
                'excel_fields'     => [
                    'Insurer Policy Number'         => 'policy_no',
                    'Primary Policy Holder Card ID' => 'tpa_no',
                    'Employee Number'               => 'emp_code',
                    'Relation'                      => 'relation',
                    'Claim Amount'                  => 'claim_amount',
                    'Date of Admission'             => 'doa',
                    'Claim Status'                  => 'status_value',
                ],
            ],
        ];
    }

    public function run(array $params)
    {
        helper('utility_helper');

        $db = db_connect();
        $apply = $this->hasFlag('apply');
        $truncate = $this->hasFlag('truncate');
        $statusCases = $this->hasFlag('status-cases');
        $statusOverride = $this->resolveOptionValue('new-status', '');
        $configs = $this->tpaConfigs();

        $selected = $this->resolveSelectedTpas($configs);
        if ($selected === []) {
            return;
        }

        $summary = [];
        foreach ($selected as $tpaKey) {
            CLI::newLine();
            CLI::write(str_repeat('=', 64), 'yellow');
            CLI::write('E2E TPA: ' . strtoupper($tpaKey) . ($statusCases ? ' [status-cases]' : ''), 'yellow');
            CLI::write(str_repeat('=', 64), 'yellow');

            try {
                $summary[$tpaKey] = $this->runForTpa(
                    $db,
                    $tpaKey,
                    $configs[$tpaKey],
                    $apply,
                    $truncate,
                    $statusOverride,
                    $statusCases
                );
            } catch (\Throwable $e) {
                CLI::error("[{$tpaKey}] " . $e->getMessage());
                $summary[$tpaKey] = [
                    'ok' => false,
                    'error' => $e->getMessage(),
                ];
            }
        }

        CLI::newLine();
        CLI::write(str_repeat('=', 64), 'cyan');
        CLI::write('E2E SUMMARY', 'cyan');
        CLI::write(str_repeat('=', 64), 'cyan');
        foreach ($summary as $tpaKey => $row) {
            $ok = !empty($row['ok']);
            $line = sprintf(
                '%-12s %s file_id=%s job1=%s job2=%s%s',
                strtoupper($tpaKey),
                $ok ? 'OK' : 'FAIL',
                $row['file_id'] ?? '-',
                $row['job1'] ?? '-',
                $row['job2'] ?? '-',
                !empty($row['error']) ? ' | ' . $row['error'] : ''
            );
            CLI::write($line, $ok ? 'green' : 'red');
            if (!empty($row['status_cases'])) {
                foreach ($row['status_cases'] as $case) {
                    $caseOk = !empty($case['ok']);
                    CLI::write(sprintf(
                        '  %-10s dump=%s expected=%s got=%s %s',
                        $case['label'],
                        $case['dump_status'] === '' ? '(empty)' : $case['dump_status'],
                        $case['expected_id'],
                        $case['actual_id'] ?? 'null',
                        $caseOk ? 'PASS' : 'FAIL'
                    ), $caseOk ? 'green' : 'red');
                }
            }
        }

        if (!$apply) {
            CLI::newLine();
            CLI::write('Dry-run only (Excel + claim_dump_files created). Re-run with --apply to execute Job1+Job2.', 'yellow');
        }
    }

    private function resolveSelectedTpas(array $configs): array
    {
        $tpaIdOpt = (int) $this->resolveOptionValue('tpa-id', '0');
        if ($tpaIdOpt > 0) {
            foreach ($configs as $key => $cfg) {
                if ((int) env($cfg['env']) === $tpaIdOpt) {
                    return [$key];
                }
            }
            CLI::error("No TPA config matches --tpa-id={$tpaIdOpt}");
            return [];
        }

        $tpaOpt = strtolower((string) ($this->resolveOptionValue('tpa', '') ?: 'all'));
        if ($tpaOpt === '' || $tpaOpt === 'all') {
            return array_keys($configs);
        }
        if (!isset($configs[$tpaOpt])) {
            CLI::error("Unknown --tpa={$tpaOpt}. Use: " . implode('|', array_keys($configs)) . '|all');
            return [];
        }

        return [$tpaOpt];
    }

    private function runForTpa(
        $db,
        string $tpaKey,
        array $cfg,
        bool $apply,
        bool $truncate,
        string $statusOverride,
        bool $statusCases = false
    ): array {
        $tpaId = (int) env($cfg['env']);
        CLI::write("{$cfg['env']} = {$tpaId}", 'cyan');
        CLI::write("table={$cfg['table']}, sheet=" . ($cfg['sheet'] ?? 'active'), 'cyan');

        if (!$this->tableExists($db, $cfg['table'])) {
            throw new \RuntimeException("Table {$cfg['table']} does not exist");
        }

        $context = $this->resolveSeedContext($db, $tpaId, $tpaKey, $cfg, $statusOverride);
        CLI::write('Seed ticket: ' . json_encode([
            'id' => $context['ticket']['id'] ?? null,
            'client_id' => $context['client_id'],
            'client_policy_id' => $context['client_policy_id'],
            'policy_no' => $context['policy_no'],
            'emp_code' => $context['emp_code'],
            'tpa_no' => $context['tpa_no'],
            'claim_amount' => $context['claim_amount'],
            'doa' => $context['doa'],
            'status_value' => $context['status_value'],
        ]));

        $service = TpaClaimsImportFactory::make($tpaId);
        $headers = $this->extractExcelHeaders($service);
        if ($headers === []) {
            throw new \RuntimeException('Could not read Excel headers from service mapping');
        }

        $scenarios = $statusCases
            ? $this->buildStatusCaseScenarios($cfg)
            : [['label' => 'default', 'status_value' => $context['status_value'], 'claim_amount' => $context['claim_amount'], 'expected_id' => null]];

        $excelRows = [];
        foreach ($scenarios as $i => $scenario) {
            $rowContext = $context;
            $rowContext['status_value'] = $scenario['status_value'];
            $rowContext['claim_amount'] = $scenario['claim_amount'];
            $excelRows[] = $this->buildExcelRow($headers, $cfg, $rowContext);
            CLI::write(sprintf(
                'Row %d [%s]: status=%s amount=%s expected_id=%s',
                $i + 1,
                $scenario['label'],
                $scenario['status_value'] === '' ? '(empty)' : $scenario['status_value'],
                $scenario['claim_amount'],
                $scenario['expected_id'] ?? 'n/a'
            ), 'cyan');
        }

        $fileName = $statusCases
            ? "{$tpaKey}_status_cases_" . date('Ymd_His') . '.xlsx'
            : "{$tpaKey}_e2e_" . date('Ymd_His') . '.xlsx';
        $uploadDir = WRITEPATH . 'uploads' . DIRECTORY_SEPARATOR . 'claim_dump_excel' . DIRECTORY_SEPARATOR;
        if (!is_dir($uploadDir)) {
            mkdir($uploadDir, 0775, true);
        }
        $filePath = $uploadDir . $fileName;
        $this->writeExcel($filePath, $headers, $excelRows, $cfg['sheet'] ?? null);
        CLI::write("Excel written: {$filePath} (" . count($excelRows) . ' data rows)', 'green');

        $fileId = $this->createClaimDumpFile($db, [
            'tpa_id' => $tpaId,
            'client_id' => $context['client_id'],
            'client_policy_id' => $context['client_policy_id'],
            'file_name' => $fileName,
            'status' => 'inprogress',
            'created_by' => 1,
            'created_at' => date('Y-m-d H:i:s'),
            'is_active' => 1,
        ]);
        CLI::write("claim_dump_files.id = {$fileId}", 'green');

        $result = [
            'ok' => true,
            'file_id' => $fileId,
            'file_name' => $fileName,
            'job1' => 'skipped',
            'job2' => 'skipped',
        ];

        if (!$apply) {
            $applyHint = $statusCases
                ? "php spark tpa:e2e-pipeline --tpa={$tpaKey} --status-cases --apply"
                : "php spark tpa:e2e-pipeline --tpa={$tpaKey} --apply";
            CLI::write("Dry-run ready. Apply with: {$applyHint}", 'yellow');
            return $result;
        }

        // Clear claim_dump_ref_id so link/update path can re-bind cleanly.
        if (!$statusCases && !empty($context['ticket']['id']) && !empty($context['ticket']['claim_dump_ref_id'])) {
            $db->table('ticket_master')
                ->where('id', $context['ticket']['id'])
                ->update(['claim_dump_ref_id' => null]);
            CLI::write('Cleared seed ticket.claim_dump_ref_id for retest.', 'yellow');
        }

        CLI::write('Running Job 1 (Excel → dump)...', 'light_red');
        $job1 = $service->runTpaClaimDumpInsert($filePath, $fileId);
        CLI::write('Job1 result: ' . json_encode($job1));
        $result['job1'] = !empty($job1['status']) ? 'ok' : 'fail';
        if (empty($job1['status'])) {
            $db->table('claim_dump_files')->where('id', $fileId)->update([
                'status' => 'failed',
                'reason' => json_encode(['error_data' => $job1['message'] ?? 'Job1 failed']),
            ]);
            $result['ok'] = false;
            $result['error'] = $job1['message'] ?? 'Job1 failed';
            return $result;
        }

        $dumpCount = $db->table($cfg['table'])->where('file_id', $fileId)->where('is_active', 1)->countAllResults();
        CLI::write("Dump rows after Job1: {$dumpCount}", 'cyan');

        CLI::write('Running Job 2 (dump → ticket_master)...', 'light_red');
        $job2 = $service->runTicketMasterInsert(['file_id' => $fileId]);
        CLI::write('Job2 result: ' . json_encode($job2));
        $result['job2'] = !empty($job2['status']) ? 'ok' : 'fail';

        if (!empty($job2['status'])) {
            $db->table('claim_dump_files')->where('id', $fileId)->update([
                'status' => 'success',
                'reason' => null,
            ]);
        } else {
            $db->table('claim_dump_files')->where('id', $fileId)->update([
                'status' => 'failed',
                'reason' => json_encode(['error_data' => $job2['message'] ?? 'Job2 failed']),
            ]);
            $result['ok'] = false;
            $result['error'] = $job2['message'] ?? 'Job2 failed';
            return $result;
        }

        $statusCol = $cfg['status_db_column'] ?? null;
        $selectCols = 'id, ticket_id, master_reject_reason, is_active';
        if ($statusCol) {
            $selectCols .= ', ' . $statusCol;
        }
        $afterDump = $db->table($cfg['table'])
            ->select($selectCols)
            ->where('file_id', $fileId)
            ->orderBy('id', 'ASC')
            ->get()->getResultArray();
        CLI::write('Dump AFTER Job2: ' . json_encode($afterDump, JSON_PRETTY_PRINT));

        $linked = 0;
        foreach ($afterDump as $drow) {
            if (!empty($drow['ticket_id'])) {
                $linked++;
            }
        }
        $result['linked'] = $linked;
        if ($linked === 0 && empty($afterDump[0]['master_reject_reason'])) {
            $result['ok'] = false;
            $result['error'] = 'Job2 reported success but dump ticket_id is still null';
            CLI::error($result['error']);
            return $result;
        }

        if ($statusCases) {
            $caseResults = $this->assertStatusCases($db, $cfg, $afterDump, $scenarios);
            $result['status_cases'] = $caseResults;
            foreach ($caseResults as $case) {
                if (empty($case['ok'])) {
                    $result['ok'] = false;
                    $result['error'] = 'Status case assertion failed for: ' . $case['label'];
                }
            }
        }

        if ($truncate) {
            CLI::write('Running soft truncate...', 'light_red');
            $trunc = $service->softTruncateClaimDump($fileId);
            CLI::write('Truncate: ' . json_encode($trunc));
            $result['truncated'] = !empty($trunc['status']);
            if (empty($trunc['status'])) {
                $result['ok'] = false;
                $result['error'] = $trunc['message'] ?? 'Truncate failed';
            }
        }

        return $result;
    }

    /**
     * Three rows: empty status → 61, unmapped → 61, mapped → mapped_status_id.
     * Unique claim amounts so Job 2 creates/links distinct tickets.
     */
    private function buildStatusCaseScenarios(array $cfg): array
    {
        $suffix = (string) (time() % 100000);
        $mappedStatus = (string) ($cfg['mapped_status'] ?? 'Paid');
        $mappedId = (int) ($cfg['mapped_status_id'] ?? 11);

        return [
            [
                'label' => 'empty',
                'status_value' => '',
                'claim_amount' => '9' . $suffix . '.11',
                'expected_id' => 61,
            ],
            [
                'label' => 'unmapped',
                'status_value' => 'ZZZ_UNMAPPED_STATUS',
                'claim_amount' => '9' . $suffix . '.22',
                'expected_id' => 61,
            ],
            [
                'label' => 'mapped',
                'status_value' => $mappedStatus,
                'claim_amount' => '9' . $suffix . '.33',
                'expected_id' => $mappedId,
            ],
        ];
    }

    /**
     * Match dump rows to scenarios by amount (and status text) and assert ticket claim_status_id.
     */
    private function assertStatusCases($db, array $cfg, array $afterDump, array $scenarios): array
    {
        $statusCol = $cfg['status_db_column'] ?? null;
        $results = [];

        // Resolve amount column from excel_fields source key claim_amount → need dump amount column.
        // Prefer joining via ticket_id and comparing expected_id on ticket_master.
        foreach ($scenarios as $scenario) {
            $match = null;
            foreach ($afterDump as $drow) {
                $dumpStatus = $statusCol !== null ? trim((string) ($drow[$statusCol] ?? '')) : '';
                $expectedStatus = trim((string) $scenario['status_value']);
                if ($dumpStatus === $expectedStatus) {
                    $match = $drow;
                    break;
                }
            }

            $actualId = null;
            $ticketId = $match['ticket_id'] ?? null;
            if (!empty($ticketId)) {
                $ticket = $db->table('ticket_master')
                    ->select('id, claim_status_id')
                    ->where('id', $ticketId)
                    ->get()->getRowArray();
                $actualId = isset($ticket['claim_status_id']) ? (int) $ticket['claim_status_id'] : null;
            }

            $ok = $match !== null
                && !empty($ticketId)
                && $actualId !== null
                && $actualId === (int) $scenario['expected_id'];

            $results[] = [
                'label' => $scenario['label'],
                'dump_status' => $scenario['status_value'],
                'expected_id' => (int) $scenario['expected_id'],
                'actual_id' => $actualId,
                'ticket_id' => $ticketId,
                'dump_id' => $match['id'] ?? null,
                'reject' => $match['master_reject_reason'] ?? null,
                'ok' => $ok,
            ];

            CLI::write(sprintf(
                'ASSERT [%s] status=%s expected=%d actual=%s ticket_id=%s dump_id=%s %s%s',
                $scenario['label'],
                $scenario['status_value'] === '' ? '(empty)' : $scenario['status_value'],
                (int) $scenario['expected_id'],
                $actualId === null ? 'null' : (string) $actualId,
                $ticketId ?? 'null',
                $match['id'] ?? 'null',
                $ok ? 'PASS' : 'FAIL',
                !empty($match['master_reject_reason']) ? ' reject=' . $match['master_reject_reason'] : ''
            ), $ok ? 'green' : 'red');
        }

        return $results;
    }

    private function resolveSeedContext($db, int $tpaId, string $tpaKey, array $cfg, string $statusOverride): array
    {
        $ticket = $db->table('ticket_master')
            ->select('id, client_id, client_policy_id, emp_code, tpa_no, claim_amount, doa, claim_status_id, claim_dump_ref_id, tpa_id')
            ->where('tpa_id', $tpaId)
            ->where('is_active', 1)
            ->where('emp_code IS NOT NULL', null, false)
            ->where('tpa_no IS NOT NULL', null, false)
            ->where('claim_amount IS NOT NULL', null, false)
            ->where('doa IS NOT NULL', null, false)
            ->orderBy('id', 'DESC')
            ->get()->getRowArray();

        if (empty($ticket)) {
            CLI::write("[{$tpaKey}] No ticket for this tpa_id; falling back to any ticket with identity keys.", 'yellow');
            $ticket = $db->table('ticket_master')
                ->select('id, client_id, client_policy_id, emp_code, tpa_no, claim_amount, doa, claim_status_id, claim_dump_ref_id, tpa_id')
                ->where('is_active', 1)
                ->where('client_policy_id IS NOT NULL', null, false)
                ->where('emp_code IS NOT NULL', null, false)
                ->where('tpa_no IS NOT NULL', null, false)
                ->where('claim_amount IS NOT NULL', null, false)
                ->where('doa IS NOT NULL', null, false)
                ->orderBy('id', 'DESC')
                ->get()->getRowArray();
        }

        if (empty($ticket)) {
            throw new \RuntimeException('No suitable ticket_master row found to seed Excel');
        }

        $policy = $db->table('client_policy')
            ->select('id, client_id, policy_no')
            ->where('id', $ticket['client_policy_id'])
            ->get()->getRowArray();
        if (empty($policy) || trim((string) ($policy['policy_no'] ?? '')) === '') {
            throw new \RuntimeException('client_policy / policy_no missing for seed ticket');
        }

        $statusValue = $statusOverride !== '' ? $statusOverride : $cfg['default_status'];

        return [
            'ticket' => $ticket,
            'client_id' => (int) ($ticket['client_id'] ?? $policy['client_id']),
            'client_policy_id' => (int) $ticket['client_policy_id'],
            'policy_no' => trim((string) $policy['policy_no']),
            'emp_code' => (string) $ticket['emp_code'],
            'tpa_no' => (string) $ticket['tpa_no'],
            'claim_amount' => (string) $ticket['claim_amount'],
            'doa' => (string) $ticket['doa'],
            'relation' => 'SELF',
            'status_value' => $statusValue,
        ];
    }

    private function extractExcelHeaders(object $service): array
    {
        $ref = new ReflectionClass($service);
        if (!$ref->hasProperty('mapping')) {
            return [];
        }
        $prop = $ref->getProperty('mapping');
        $prop->setAccessible(true);
        $mapping = $prop->getValue($service);
        if (!is_array($mapping)) {
            return [];
        }

        $headers = [];
        foreach ($mapping as $map) {
            $excel = $map['excel_column'] ?? null;
            if (is_array($excel)) {
                $name = trim((string) ($excel['col_name'] ?? ''));
            } else {
                $name = trim((string) $excel);
            }
            if ($name !== '') {
                $headers[] = $name;
            }
        }

        return $headers;
    }

    private function buildExcelRow(array $headers, array $cfg, array $context): array
    {
        $row = array_fill(0, count($headers), '');
        $headerIndex = array_flip($headers);

        foreach ($cfg['excel_fields'] as $excelHeader => $sourceKey) {
            if (!isset($headerIndex[$excelHeader])) {
                CLI::write("Warning: Excel header '{$excelHeader}' not in mapping; skipped.", 'yellow');
                continue;
            }
            $row[$headerIndex[$excelHeader]] = $context[$sourceKey] ?? '';
        }

        // Ensure status header is filled even if excel_fields key differs.
        if (isset($headerIndex[$cfg['status_header']])) {
            $row[$headerIndex[$cfg['status_header']]] = $context['status_value'];
        }

        return $row;
    }

    private function writeExcel(string $filePath, array $headers, array $rows, ?string $sheetName): void
    {
        // Backward compatible: single row flat array becomes one data row.
        if ($rows !== [] && !is_array($rows[0] ?? null)) {
            $rows = [$rows];
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        if ($sheetName) {
            $sheet->setTitle($sheetName);
        }

        foreach ($headers as $i => $header) {
            $sheet->setCellValue([$i + 1, 1], $header);
        }

        foreach ($rows as $r => $rowValues) {
            foreach ($headers as $i => $header) {
                $sheet->setCellValue([$i + 1, $r + 2], $rowValues[$i] ?? '');
            }
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }

    private function createClaimDumpFile($db, array $data): int
    {
        $cols = array_column($db->query('SHOW COLUMNS FROM claim_dump_files')->getResultArray(), 'Field');
        $data = array_intersect_key($data, array_flip($cols));
        $db->table('claim_dump_files')->insert($data);
        $id = (int) $db->insertID();
        if ($id <= 0) {
            throw new \RuntimeException('Failed to insert claim_dump_files');
        }
        return $id;
    }

    private function tableExists($db, string $table): bool
    {
        return !empty($db->query('SHOW TABLES LIKE ' . $db->escape($table))->getResultArray());
    }

    private function hasFlag(string $name): bool
    {
        if (CLI::getOption($name) !== null) {
            return true;
        }
        return in_array('--' . $name, $_SERVER['argv'] ?? [], true);
    }

    private function resolveOptionValue(string $name, string $default): string
    {
        $opt = CLI::getOption($name);
        if (is_string($opt) && $opt !== '') {
            return $opt;
        }

        $argv = $_SERVER['argv'] ?? [];
        foreach ($argv as $i => $arg) {
            if (preg_match('/^--' . preg_quote($name, '/') . '=(.+)$/', (string) $arg, $m)) {
                return trim($m[1]);
            }
            if ($arg === '--' . $name && isset($argv[$i + 1]) && strpos((string) $argv[$i + 1], '--') !== 0) {
                return (string) $argv[$i + 1];
            }
        }

        return $default;
    }
}