<?php

namespace App\Libraries;

use App\Models\ClaimDumpFileModel;
use App\Models\ClaimReportDashboardModel;

/**
 * Builds ICICI Lombard Portfolio Analysis MIS from claim_report → claims_dump_icici.
 */
class IciciMisReportService
{
    public const DUMP_TABLE = 'claims_dump_icici';

    private const AGE_BANDS = [
        '00-05', '06-18', '19-25', '26-35', '36-45', '46-55', '56-65', '66-75', 'Above 75',
    ];

    private const RELATIONS = [
        'Self', 'Parents', 'Spouse', 'Children', 'Siblings', 'In Laws', 'Others',
    ];

    private const AMOUNT_BANDS = [
        '0-25000', '25001-50000', '50001-100000', '100001-150000',
        '150001-200000', '200001-300000', '300001-500000', 'Above 500000',
    ];

    private const SI_BANDS = [
        '0-200000', '200000-400000', '400000-600000', 'Above 600000',
    ];

    private const STATUS_ROWS = [
        'Outstanding — AL Approved',
        'Outstanding — Claim WIP',
        'Outstanding — Query',
        'Outstanding — Sent for Payment',
        'Settled — Paid',
        'Settled — Reject',
    ];

    private ClaimReportDashboardModel $kpiModel;
    private ClaimDumpFileModel $dumpFileModel;

    public function __construct(
        ?ClaimReportDashboardModel $kpiModel = null,
        ?ClaimDumpFileModel $dumpFileModel = null
    ) {
        $this->kpiModel      = $kpiModel ?? new ClaimReportDashboardModel();
        $this->dumpFileModel = $dumpFileModel ?? new ClaimDumpFileModel();
    }

    /**
     * @return array{status:bool,message?:string,data?:array}
     */
    public function build(int $policyId): array
    {
        if ($policyId <= 0) {
            return ['status' => false, 'message' => 'client_policy_id is required.'];
        }

        $db = db_connect();
        if (!$db->tableExists('claim_report') || !$db->tableExists(self::DUMP_TABLE)) {
            return ['status' => false, 'message' => 'Required tables claim_report or claims_dump_icici do not exist.'];
        }

        $dumpRows = $this->getLinkedDumpRows($policyId);
        if ($dumpRows === []) {
            return [
                'status'  => false,
                'message' => 'No ICICI dump rows linked via claim_report. Run sync: /util/claims-collection-report/sync',
            ];
        }

        $policy = $db->table('client_policy cp')
            ->select('cp.*, c.client_name, i.name AS insurer_name, t.name AS tpa_name')
            ->join('clients c', 'c.id = cp.client_id', 'left')
            ->join('insurers i', 'i.id = cp.insurer_id', 'left')
            ->join('tpa t', 't.id = cp.tpa_id', 'left')
            ->where('cp.id', $policyId)
            ->get()
            ->getRowArray();

        if (!$policy) {
            return ['status' => false, 'message' => 'Policy not found.'];
        }

        $exposure   = $this->firstRow($this->kpiModel->policy_exposure_summary($policyId));
        $premiumRow = $this->firstRow($this->kpiModel->premium_as_on_date($policyId));
        $livesRow   = $this->firstRow($this->kpiModel->current_emp_lives($policyId));
        $experience = $this->firstRow($this->kpiModel->claims_experience_summary($policyId));

        $premium = $this->parseFormattedNumber($premiumRow['premium_as_on_date'] ?? null);
        $earned  = $this->parseFormattedNumber($experience['earned_premium'] ?? null);
        $lives   = (int) ($livesRow['current_lives'] ?? 0);

        $paidAmt = 0.0;
        $osAmt   = 0.0;
        foreach ($dumpRows as $row) {
            $bucket = $this->statusBucket((string) ($row['updated_status'] ?? ''));
            if ($bucket === 'Settled — Paid') {
                $paidAmt += $this->paidAmount($row);
            } elseif (str_starts_with($bucket, 'Outstanding')) {
                $osAmt += $this->claimedOsAmount($row);
            }
        }
        $hitLoss    = $paidAmt + $osAmt;
        $claimCost  = 0.0;
        $grossInc   = $hitLoss + $claimCost;
        $lossRatio  = $earned > 0 ? (int) round(($grossInc / $earned) * 100) : 0;

        $claimsSummary = $this->buildClaimsSummary($dumpRows);
        $iltcSummary   = $this->buildIltcSummary($dumpRows);
        $enrollment    = $this->buildEnrollmentSummary($dumpRows, $lives);
        $ageAnalysis   = $this->buildTypeGridByKey($dumpRows, fn ($r) => $this->ageBand($r), self::AGE_BANDS);
        $relationAcs   = $this->buildTypeGridByKey($dumpRows, fn ($r) => $this->relationLabel($r), self::RELATIONS);
        $amountBands   = $this->buildAmountBands($dumpRows);
        $siBands       = $this->buildSiBands($dumpRows);
        $utilEmp       = $this->buildUtilization($dumpRows, true);
        $utilDep       = $this->buildUtilization($dumpRows, false);
        $disease       = $this->buildTypeGridByKey($dumpRows, fn ($r) => $this->diseaseLabel($r), null, true);
        $hospitals     = $this->buildTopHospitals($dumpRows, 10);
        $cities        = $this->buildTopCities($dumpRows, 10);
        $tat           = $this->buildTat($dumpRows);
        $takeaways     = $this->buildTakeaways($dumpRows, $claimsSummary, $enrollment, $disease, $utilDep);

        $startDate = $this->formatDate($policy['policy_start_date'] ?? $exposure['policy_start_date'] ?? null);
        $endDate   = $this->formatDate($policy['policy_end_date'] ?? $exposure['policy_end_date'] ?? null);
        $dumpTs    = $this->dumpFileModel->getLatestClaimDumpTimestamp($policyId) ?: time();
        $uploadAt  = date('d/m/Y g:i:s A', $dumpTs);
        $reportMonth = date('F-Y', $dumpTs);

        $data = [
            'meta' => [
                'policy_id'     => $policyId,
                'client_name'   => trim((string) ($policy['client_name'] ?? '')),
                'report_month'  => $reportMonth,
                'data_upload'   => $uploadAt,
                'generated_at'  => date('d-M-Y', $dumpTs),
                'tpa_name'      => trim((string) ($policy['tpa_name'] ?? 'ICICI Lombard')),
            ],
            'header' => [
                'policy_number'     => trim((string) ($policy['policy_no'] ?? '')),
                'policy_start_date' => $startDate,
                'policy_end_date'   => $endDate,
                'policy_period'     => trim($startDate . ' To ' . $endDate),
                'insurer_name'      => trim((string) ($policy['insurer_name'] ?? 'ICICI Lombard General Insurance Company')),
                'lives'             => $lives,
            ],
            'premium_lr' => [
                'premium'             => $premium,
                'earned_premium'      => $earned,
                'loss_amount_paid'    => $paidAmt,
                'loss_amount_os'      => $osAmt,
                'hit_loss'            => $hitLoss,
                'claim_cost'          => $claimCost,
                'gross_incurred_cost' => $grossInc,
                'loss_ratio'          => $lossRatio,
                'as_on'               => date('m/d/Y', $dumpTs),
            ],
            'claims_summary' => $claimsSummary,
            'iltc_summary'   => $iltcSummary,
            'enrollment'     => $enrollment,
            'age_analysis'   => $ageAnalysis,
            'relation_acs'   => $relationAcs,
            'amount_bands'   => $amountBands,
            'si_bands'       => $siBands,
            'iltc_app'       => [
                'month' => date('M-y'),
                'engagement' => ['self_lives' => '', 'active_users' => '', 'downloads' => '', 'ratio' => '0%'],
                'features'   => ['ecard' => '', 'face_scan' => '0%', 'blog' => '0%', 'cross_sell' => ''],
                'services'   => ['hat_req' => '', 'hat_ful' => '', 'ilhd' => '', 'expertise' => '0%', 'health_claim' => ''],
            ],
            'utilization_employees'  => $utilEmp,
            'utilization_dependents' => $utilDep,
            'disease'    => $disease,
            'hospitals'  => $hospitals,
            'cities'     => $cities,
            'tat'        => $tat,
            'takeaways'  => $takeaways,
            'dump_row_count' => count($dumpRows),
        ];

        $data['view_model'] = $data;

        return ['status' => true, 'data' => $data];
    }

    /**
     * @return list<array<string,mixed>>
     */
    public function getLinkedDumpRows(int $policyId): array
    {
        $db = db_connect();
        $reportRows = $db->table('claim_report')
            ->select('source_row_id')
            ->where('client_policy_id', $policyId)
            ->where('is_active', 1)
            ->where('source_table', self::DUMP_TABLE)
            ->where('source_row_id IS NOT NULL', null, false)
            ->get()
            ->getResultArray();

        if ($reportRows === []) {
            return [];
        }

        $dumpIds = array_values(array_unique(array_filter(array_map(
            static fn ($r) => (int) ($r['source_row_id'] ?? 0),
            $reportRows
        ))));

        if ($dumpIds === []) {
            return [];
        }

        return $db->table(self::DUMP_TABLE)
            ->whereIn('id', $dumpIds)
            ->where('is_active', 1)
            ->orderBy('id', 'ASC')
            ->get()
            ->getResultArray();
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>,cashless_pct:float}
     */
    private function buildClaimsSummary(array $rows): array
    {
        $empty = ['count' => 0, 'claimed' => 0.0, 'paid' => 0.0];
        $grid  = [];
        foreach (self::STATUS_ROWS as $label) {
            $grid[$label] = [
                'Cashless'      => $empty,
                'Reimbursement' => $empty,
            ];
        }

        foreach ($rows as $row) {
            $bucket = $this->statusBucket((string) ($row['updated_status'] ?? ''));
            if (!isset($grid[$bucket])) {
                continue;
            }
            $type = $this->claimType($row);
            $claimed = $this->claimedOsAmount($row);
            $paid    = str_contains($bucket, 'Paid')
                ? $this->paidAmount($row)
                : (str_starts_with($bucket, 'Outstanding') ? $claimed : 0.0);

            $grid[$bucket][$type]['count']++;
            $grid[$bucket][$type]['claimed'] += $claimed;
            if (!str_contains($bucket, 'Reject')) {
                $grid[$bucket][$type]['paid'] += $paid;
            }
        }

        $outRows = [];
        $totals  = [
            'Cashless' => $empty,
            'Reimbursement' => $empty,
        ];

        foreach (self::STATUS_ROWS as $label) {
            $c = $grid[$label]['Cashless'];
            $r = $grid[$label]['Reimbursement'];
            $o = [
                'count'   => $c['count'] + $r['count'],
                'claimed' => $c['claimed'] + $r['claimed'],
                'paid'    => $c['paid'] + $r['paid'],
            ];
            $outRows[] = [
                'label' => $label,
                'cashless' => $c,
                'reimb'    => $r,
                'overall'  => $o,
            ];
            foreach (['Cashless', 'Reimbursement'] as $t) {
                $totals[$t]['count']   += $grid[$label][$t]['count'];
                $totals[$t]['claimed'] += $grid[$label][$t]['claimed'];
                $totals[$t]['paid']    += $grid[$label][$t]['paid'];
            }
        }

        $totalOverall = [
            'count'   => $totals['Cashless']['count'] + $totals['Reimbursement']['count'],
            'claimed' => $totals['Cashless']['claimed'] + $totals['Reimbursement']['claimed'],
            'paid'    => $totals['Cashless']['paid'] + $totals['Reimbursement']['paid'],
        ];
        $den = $totals['Cashless']['count'] + $totals['Reimbursement']['count'];
        $cashlessPct = $den > 0 ? round(($totals['Cashless']['count'] / $den) * 100) : 0;

        return [
            'rows'         => $outRows,
            'total'        => [
                'cashless' => $totals['Cashless'],
                'reimb'    => $totals['Reimbursement'],
                'overall'  => $totalOverall,
            ],
            'cashless_pct' => $cashlessPct,
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{ri:int,iltc:int,pct:int}
     */
    private function buildIltcSummary(array $rows): array
    {
        $ri = 0;
        $iltc = 0;
        foreach ($rows as $row) {
            if ($this->claimType($row) !== 'Reimbursement') {
                continue;
            }
            $ri++;
            $tag = strtoupper(trim((string) ($row['iltc_tag'] ?? '')));
            if ($tag !== '' && $tag !== '0' && $tag !== 'N' && $tag !== 'NO') {
                $iltc++;
            }
        }

        return [
            'ri'   => $ri,
            'iltc' => $iltc,
            'pct'  => $ri > 0 ? (int) round(($iltc / $ri) * 100) : 0,
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array<string,mixed>
     */
    private function buildEnrollmentSummary(array $rows, int $lives): array
    {
        $claimCount = count($rows);
        $freq = $lives > 0 ? round(($claimCount / $lives) * 100, 2) : 0.0;

        // Age × relation from dump demographics (unique UHID preferred)
        $matrix = [];
        foreach (self::AGE_BANDS as $band) {
            foreach (self::RELATIONS as $rel) {
                $matrix[$band][$rel] = 0;
            }
            $matrix[$band]['_total'] = 0;
        }
        $seen = [];
        foreach ($rows as $row) {
            $uhid = trim((string) ($row['uhid'] ?? ''));
            $key  = $uhid !== '' ? $uhid : ('id:' . ($row['id'] ?? uniqid('', true)));
            if (isset($seen[$key])) {
                continue;
            }
            $seen[$key] = true;
            $band = $this->ageBand($row);
            $rel  = $this->relationLabel($row);
            if (!isset($matrix[$band])) {
                continue;
            }
            $matrix[$band][$rel] = ($matrix[$band][$rel] ?? 0) + 1;
            $matrix[$band]['_total']++;
        }

        $colTotals = array_fill_keys(self::RELATIONS, 0);
        $grand = 0;
        foreach (self::AGE_BANDS as $band) {
            foreach (self::RELATIONS as $rel) {
                $colTotals[$rel] += $matrix[$band][$rel];
            }
            $grand += $matrix[$band]['_total'];
        }
        if ($lives > 0 && $grand === 0) {
            $grand = $lives;
        }

        $topBand = '';
        $topPct  = 0;
        foreach (self::AGE_BANDS as $band) {
            $pct = $grand > 0 ? (int) round(($matrix[$band]['_total'] / $grand) * 100) : 0;
            if ($pct > $topPct) {
                $topPct  = $pct;
                $topBand = $band;
            }
        }

        return [
            'lives'       => $lives > 0 ? $lives : $grand,
            'claims'      => $claimCount,
            'frequency'   => $freq,
            'matrix'      => $matrix,
            'col_totals'  => $colTotals,
            'grand_total' => $grand > 0 ? $grand : $lives,
            'top_band'    => $topBand,
            'top_band_pct'=> $topPct,
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @param callable(array):string $keyFn
     * @param list<string>|null $order
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function buildTypeGridByKey(array $rows, callable $keyFn, ?array $order = null, bool $dynamic = false): array
    {
        $buckets = [];
        if ($order !== null) {
            foreach ($order as $k) {
                $buckets[$k] = $this->emptyTypeBucket();
            }
        }

        foreach ($rows as $row) {
            // Age/Relation ACS tables in PDF use settled+OS paid amounts (incidence with amount)
            $bucketStatus = $this->statusBucket((string) ($row['updated_status'] ?? ''));
            if ($bucketStatus === 'Settled — Reject') {
                continue;
            }
            // Include Paid + Outstanding for analysis grids (match sample counts)
            if (!str_contains($bucketStatus, 'Paid') && !str_starts_with($bucketStatus, 'Outstanding')) {
                // still include paid only for ACS amount grids — sample includes outstanding in claims summary
                // For age/relation ACS sample uses paid amounts mostly; include Paid + OS with amounts
            }

            $key = $keyFn($row);
            if ($key === '') {
                $key = 'Others';
            }
            if (!isset($buckets[$key])) {
                if (!$dynamic && $order !== null) {
                    continue;
                }
                $buckets[$key] = $this->emptyTypeBucket();
            }
            $type = $this->claimType($row);
            $amt  = str_contains($bucketStatus, 'Paid')
                ? $this->paidAmount($row)
                : $this->claimedOsAmount($row);
            if ($bucketStatus === 'Settled — Reject') {
                continue;
            }
            // Count Paid + Outstanding for analysis (PDF age/relation uses paid-focused rows)
            if (!str_contains($bucketStatus, 'Paid') && !str_starts_with($bucketStatus, 'Outstanding')) {
                continue;
            }
            // Prefer Paid amounts for ACS tables like sample (Outstanding also counted in summary)
            if (str_starts_with($bucketStatus, 'Outstanding')) {
                // Sample age analysis counts appear closer to paid-only; skip OS for ACS grids
                continue;
            }

            $buckets[$key][$type]['count']++;
            $buckets[$key][$type]['amount'] += $amt;
        }

        $keys = $order ?? array_keys($buckets);
        if ($dynamic) {
            // sort by total amount desc, keep Overall separate
            uasort($buckets, static function ($a, $b) {
                $ta = $a['Cashless']['amount'] + $a['Reimbursement']['amount'];
                $tb = $b['Cashless']['amount'] + $b['Reimbursement']['amount'];

                return $tb <=> $ta;
            });
            $keys = array_keys($buckets);
        }

        $rowsOut = [];
        $total   = $this->emptyTypeBucket();
        foreach ($keys as $key) {
            $b = $buckets[$key] ?? $this->emptyTypeBucket();
            $row = $this->finalizeTypeRow($key, $b);
            $rowsOut[] = $row;
            foreach (['Cashless', 'Reimbursement'] as $t) {
                $total[$t]['count']  += $b[$t]['count'];
                $total[$t]['amount'] += $b[$t]['amount'];
            }
        }

        return [
            'rows'  => $rowsOut,
            'total' => $this->finalizeTypeRow('Overall', $total),
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array>,total:array}
     */
    private function buildAmountBands(array $rows): array
    {
        $buckets = [];
        foreach (self::AMOUNT_BANDS as $b) {
            $buckets[$b] = $this->emptyTypeBucket();
        }

        $totalCount = 0;
        $totalAmt   = 0.0;
        foreach ($rows as $row) {
            if ($this->statusBucket((string) ($row['updated_status'] ?? '')) !== 'Settled — Paid') {
                continue;
            }
            $amt  = $this->paidAmount($row);
            $band = $this->amountBand($amt);
            $type = $this->claimType($row);
            $buckets[$band][$type]['count']++;
            $buckets[$band][$type]['amount'] += $amt;
            $totalCount++;
            $totalAmt += $amt;
        }

        $out = [];
        $tot = $this->emptyTypeBucket();
        foreach (self::AMOUNT_BANDS as $band) {
            $b = $buckets[$band];
            $c = $b['Cashless']['count'] + $b['Reimbursement']['count'];
            $a = $b['Cashless']['amount'] + $b['Reimbursement']['amount'];
            $out[] = [
                'label'        => $band,
                'cashless'     => $b['Cashless'],
                'reimb'        => $b['Reimbursement'],
                'total_count'  => $c,
                'total_amount' => $a,
                'pct_claims'   => $totalCount > 0 ? (int) round(($c / $totalCount) * 100) : 0,
                'pct_value'    => $totalAmt > 0 ? (int) round(($a / $totalAmt) * 100) : 0,
            ];
            foreach (['Cashless', 'Reimbursement'] as $t) {
                $tot[$t]['count']  += $b[$t]['count'];
                $tot[$t]['amount'] += $b[$t]['amount'];
            }
        }

        return [
            'rows'  => $out,
            'total' => [
                'cashless'     => $tot['Cashless'],
                'reimb'        => $tot['Reimbursement'],
                'total_count'  => $tot['Cashless']['count'] + $tot['Reimbursement']['count'],
                'total_amount' => $tot['Cashless']['amount'] + $tot['Reimbursement']['amount'],
                'pct_claims'   => 100,
                'pct_value'    => 100,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array>,total:array}
     */
    private function buildSiBands(array $rows): array
    {
        $buckets = [];
        foreach (self::SI_BANDS as $b) {
            $buckets[$b] = $this->emptyTypeBucket();
        }

        $totalCount = 0;
        $totalAmt   = 0.0;
        foreach ($rows as $row) {
            if ($this->statusBucket((string) ($row['updated_status'] ?? '')) !== 'Settled — Paid') {
                continue;
            }
            $si   = $this->toFloat($row['sum_insured'] ?? 0);
            $band = $this->siBand($si);
            $type = $this->claimType($row);
            $amt  = $this->paidAmount($row);
            $buckets[$band][$type]['count']++;
            $buckets[$band][$type]['amount'] += $amt;
            $totalCount++;
            $totalAmt += $amt;
        }

        $out = [];
        $tot = $this->emptyTypeBucket();
        foreach (self::SI_BANDS as $band) {
            $b = $buckets[$band];
            $row = $this->finalizeTypeRow($band, $b);
            $c = $row['total']['count'];
            $a = $row['total']['amount'];
            $row['pct_claims'] = $totalCount > 0 ? (int) round(($c / $totalCount) * 100) : 0;
            $row['pct_value']  = $totalAmt > 0 ? (int) round(($a / $totalAmt) * 100) : 0;
            $out[] = $row;
            foreach (['Cashless', 'Reimbursement'] as $t) {
                $tot[$t]['count']  += $b[$t]['count'];
                $tot[$t]['amount'] += $b[$t]['amount'];
            }
        }
        $totalRow = $this->finalizeTypeRow('Overall', $tot);
        $totalRow['pct_claims'] = 100;
        $totalRow['pct_value']  = 100;

        return ['rows' => $out, 'total' => $totalRow];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array>,total:array}
     */
    private function buildUtilization(array $rows, bool $employees): array
    {
        $byMember = [];
        foreach ($rows as $row) {
            if ($this->statusBucket((string) ($row['updated_status'] ?? '')) !== 'Settled — Paid') {
                continue;
            }
            $isSelf = $this->isSelf($row);
            if ($employees && !$isSelf) {
                continue;
            }
            if (!$employees && $isSelf) {
                continue;
            }
            $key = trim((string) ($row['uhid'] ?? ''));
            if ($key === '') {
                $key = trim((string) ($row['employee_member_id'] ?? '')) . '|' . trim((string) ($row['insured_name'] ?? ''));
            }
            if ($key === '|') {
                $key = 'row:' . ($row['id'] ?? uniqid('', true));
            }
            if (!isset($byMember[$key])) {
                $byMember[$key] = ['count' => 0, 'amount' => 0.0];
            }
            $byMember[$key]['count']++;
            $byMember[$key]['amount'] += $this->paidAmount($row);
        }

        $bands = [
            '1'   => ['members' => 0, 'amount' => 0.0, 'claims' => 0],
            '2-3' => ['members' => 0, 'amount' => 0.0, 'claims' => 0],
            '>3'  => ['members' => 0, 'amount' => 0.0, 'claims' => 0],
        ];
        foreach ($byMember as $m) {
            $c = $m['count'];
            $key = $c === 1 ? '1' : ($c <= 3 ? '2-3' : '>3');
            $bands[$key]['members']++;
            $bands[$key]['amount'] += $m['amount'];
            $bands[$key]['claims'] += $c;
        }

        $totMembers = array_sum(array_column($bands, 'members'));
        $totAmount  = array_sum(array_column($bands, 'amount'));
        $totClaims  = array_sum(array_column($bands, 'claims'));

        $out = [];
        foreach ($bands as $label => $b) {
            $out[] = [
                'label'      => $label,
                'members'    => $b['members'],
                'amount'     => $b['amount'],
                'pct_claims' => $totClaims > 0 ? (int) round(($b['claims'] / $totClaims) * 100) : 0,
                'pct_value'  => $totAmount > 0 ? (int) round(($b['amount'] / $totAmount) * 100) : 0,
            ];
        }

        return [
            'rows'  => $out,
            'total' => [
                'members'    => $totMembers,
                'amount'     => $totAmount,
                'pct_claims' => 100,
                'pct_value'  => 100,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array>,total:array}
     */
    private function buildTopHospitals(array $rows, int $limit): array
    {
        $map = [];
        foreach ($rows as $row) {
            if ($this->statusBucket((string) ($row['updated_status'] ?? '')) !== 'Settled — Paid') {
                continue;
            }
            $name = trim((string) ($row['hospital_name'] ?? ''));
            if ($name === '') {
                $name = 'Unknown';
            }
            $city = trim((string) ($row['hospital_city'] ?? '')) ?: '--';
            $key  = strtoupper($name);
            if (!isset($map[$key])) {
                $map[$key] = [
                    'name' => $name,
                    'city' => $city,
                    'Cashless' => ['count' => 0, 'amount' => 0.0],
                    'Reimbursement' => ['count' => 0, 'amount' => 0.0],
                ];
            }
            $type = $this->claimType($row);
            $map[$key][$type]['count']++;
            $map[$key][$type]['amount'] += $this->paidAmount($row);
        }

        uasort($map, static function ($a, $b) {
            $ta = $a['Cashless']['amount'] + $a['Reimbursement']['amount'];
            $tb = $b['Cashless']['amount'] + $b['Reimbursement']['amount'];

            return $tb <=> $ta;
        });

        $top = array_slice(array_values($map), 0, $limit);
        $out = [];
        $tot = $this->emptyTypeBucket();
        foreach ($top as $h) {
            $bucket = [
                'Cashless'      => $h['Cashless'],
                'Reimbursement' => $h['Reimbursement'],
            ];
            $row = $this->finalizeTypeRow($h['name'], $bucket);
            $row['city'] = $h['city'];
            $out[] = $row;
            foreach (['Cashless', 'Reimbursement'] as $t) {
                $tot[$t]['count']  += $h[$t]['count'];
                $tot[$t]['amount'] += $h[$t]['amount'];
            }
        }

        return ['rows' => $out, 'total' => $this->finalizeTypeRow('Overall', $tot)];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array>,total:array}
     */
    private function buildTopCities(array $rows, int $limit): array
    {
        $map = [];
        foreach ($rows as $row) {
            if ($this->statusBucket((string) ($row['updated_status'] ?? '')) !== 'Settled — Paid') {
                continue;
            }
            $city = trim((string) ($row['hospital_city'] ?? ''));
            if ($city === '') {
                $city = 'Unknown';
            }
            $key = strtoupper($city);
            if (!isset($map[$key])) {
                $map[$key] = $this->emptyTypeBucket();
                $map[$key]['_label'] = $city;
            }
            $type = $this->claimType($row);
            $map[$key][$type]['count']++;
            $map[$key][$type]['amount'] += $this->paidAmount($row);
        }

        uasort($map, static function ($a, $b) {
            $ta = $a['Cashless']['amount'] + $a['Reimbursement']['amount'];
            $tb = $b['Cashless']['amount'] + $b['Reimbursement']['amount'];

            return $tb <=> $ta;
        });

        $top = array_slice($map, 0, $limit, true);
        $out = [];
        $tot = $this->emptyTypeBucket();
        foreach ($top as $item) {
            $label = $item['_label'];
            unset($item['_label']);
            $out[] = $this->finalizeTypeRow($label, $item);
            foreach (['Cashless', 'Reimbursement'] as $t) {
                $tot[$t]['count']  += $item[$t]['count'];
                $tot[$t]['amount'] += $item[$t]['amount'];
            }
        }

        return ['rows' => $out, 'total' => $this->finalizeTypeRow('Overall', $tot)];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return list<array{month:string,reimb_days:string,fresh_al:string,enhancement:string}>
     */
    private function buildTat(array $rows): array
    {
        $byMonth = [];
        foreach ($rows as $row) {
            $bucket = $this->statusBucket((string) ($row['updated_status'] ?? ''));
            $type   = $this->claimType($row);
            $inward = strtotime((string) ($row['inward_date'] ?? ''));
            $paid   = strtotime((string) ($row['payment_date'] ?? ''));
            $doa    = strtotime((string) ($row['doa'] ?? ''));

            $monthKey = '';
            if ($paid !== false) {
                $monthKey = date('M-y', $paid);
            } elseif ($inward !== false) {
                $monthKey = date('M-y', $inward);
            } elseif ($doa !== false) {
                $monthKey = date('M-y', $doa);
            }
            if ($monthKey === '') {
                continue;
            }
            if (!isset($byMonth[$monthKey])) {
                $byMonth[$monthKey] = [
                    'reimb_days' => [],
                    'fresh_secs' => [],
                    'sort'       => $paid ?: ($inward ?: ($doa ?: 0)),
                ];
            }

            if ($type === 'Reimbursement' && $bucket === 'Settled — Paid' && $inward !== false && $paid !== false && $paid >= $inward) {
                $byMonth[$monthKey]['reimb_days'][] = (int) floor(($paid - $inward) / 86400);
            }
            if ($type === 'Cashless' && $doa !== false && $inward !== false && $inward >= $doa) {
                $byMonth[$monthKey]['fresh_secs'][] = $inward - $doa;
            }
        }

        uasort($byMonth, static fn ($a, $b) => $a['sort'] <=> $b['sort']);

        $out = [];
        foreach ($byMonth as $month => $data) {
            $reimb = $data['reimb_days'] !== []
                ? (string) (int) round(array_sum($data['reimb_days']) / count($data['reimb_days']))
                : '';
            $fresh = '';
            if ($data['fresh_secs'] !== []) {
                $avg = (int) round(array_sum($data['fresh_secs']) / count($data['fresh_secs']));
                $h = intdiv($avg, 3600);
                $m = intdiv($avg % 3600, 60);
                $s = $avg % 60;
                $fresh = $h . ':' . $m . ':' . $s;
            }
            $out[] = [
                'month'       => $month,
                'reimb_days'  => $reimb,
                'fresh_al'    => $fresh,
                'enhancement' => '',
            ];
        }

        return $out;
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @param array<string,mixed> $claimsSummary
     * @param array<string,mixed> $enrollment
     * @param array<string,mixed> $disease
     * @param array<string,mixed> $utilDep
     * @return array<string,list<string>>
     */
    private function buildTakeaways(array $rows, array $claimsSummary, array $enrollment, array $disease, array $utilDep): array
    {
        $portfolio = [];
        $portfolio[] = 'Cashless % = ' . ($claimsSummary['cashless_pct'] ?? 0) . '% (Cashless/Cashless+Reimbursement)';
        $iltc = $this->buildIltcSummary($rows);
        $portfolio[] = 'ILTC % = ' . $iltc['pct'] . '% (ILTC(Incidence)/Overall RI(Incidence))';

        $enrollmentNotes = [];
        if (($enrollment['top_band'] ?? '') !== '') {
            $enrollmentNotes[] = 'Highest enrolment is done in age band of "' . $enrollment['top_band'] . '" which is ' . $enrollment['top_band_pct'] . '%';
        }

        $diseaseNotes = [];
        $dRows = $disease['rows'] ?? [];
        if ($dRows !== []) {
            $top = $dRows[0];
            $totCount = (int) ($disease['total']['total']['count'] ?? 0);
            $totAmt   = (float) ($disease['total']['total']['amount'] ?? 0);
            $cPct = $totCount > 0 ? (int) round(($top['total']['count'] / $totCount) * 100) : 0;
            $aPct = $totAmt > 0 ? (int) round(($top['total']['amount'] / $totAmt) * 100) : 0;
            $diseaseNotes[] = ($top['label'] ?? '') . ' is a disease category which is causing maximum number of claims which is ' . $cPct . '%';
            $diseaseNotes[] = 'Also ' . ($top['label'] ?? '') . ' is a disease category where the claim amount is maximum which is ' . $aPct . '%';
        }

        $utilNotes = [];
        foreach ($utilDep['rows'] ?? [] as $r) {
            if (($r['label'] ?? '') === '1' && (int) ($r['members'] ?? 0) > 0) {
                $utilNotes[] = 'Most dependents raise an average of 1 claims with a value ' . number_format((float) $r['amount']);
                break;
            }
        }

        $tatNotes = [];
        $tat = $this->buildTat($rows);
        $reimbVals = array_values(array_filter(array_map(static fn ($r) => $r['reimb_days'] !== '' ? (int) $r['reimb_days'] : null, $tat)));
        if ($reimbVals !== []) {
            $tatNotes[] = 'Reimbursement TAT is ' . (int) round(array_sum($reimbVals) / count($reimbVals));
        }

        return [
            'portfolio'  => $portfolio,
            'enrollment' => $enrollmentNotes,
            'disease'    => $diseaseNotes,
            'utilization'=> $utilNotes,
            'tat'        => $tatNotes,
        ];
    }

    // ----------------- helpers -----------------

    /** @return array{Cashless:array{count:int,amount:float},Reimbursement:array{count:int,amount:float}} */
    private function emptyTypeBucket(): array
    {
        return [
            'Cashless'      => ['count' => 0, 'amount' => 0.0],
            'Reimbursement' => ['count' => 0, 'amount' => 0.0],
        ];
    }

    /**
     * @param array{Cashless:array{count:int,amount:float},Reimbursement:array{count:int,amount:float}} $b
     * @return array<string,mixed>
     */
    private function finalizeTypeRow(string $label, array $b): array
    {
        $c = $b['Cashless'];
        $r = $b['Reimbursement'];
        $tCount = $c['count'] + $r['count'];
        $tAmt   = $c['amount'] + $r['amount'];

        return [
            'label'    => $label,
            'cashless' => [
                'count'  => $c['count'],
                'amount' => $c['amount'],
                'acs'    => $c['count'] > 0 ? round($c['amount'] / $c['count']) : 0,
            ],
            'reimb' => [
                'count'  => $r['count'],
                'amount' => $r['amount'],
                'acs'    => $r['count'] > 0 ? round($r['amount'] / $r['count']) : 0,
            ],
            'total' => [
                'count'  => $tCount,
                'amount' => $tAmt,
                'acs'    => $tCount > 0 ? round($tAmt / $tCount) : 0,
            ],
        ];
    }

    private function statusBucket(string $status): string
    {
        $s = strtoupper(trim(str_replace(['_', '-'], ' ', $status)));
        $s = preg_replace('/\s+/', ' ', $s) ?? $s;

        if (str_contains($s, 'REJECT')) {
            return 'Settled — Reject';
        }
        if (str_contains($s, 'PAID') || $s === 'SETTLED' || $s === 'CLOSED') {
            return 'Settled — Paid';
        }
        if (str_contains($s, 'AL APPROVED') || $s === 'APPROVED' || str_contains($s, 'AUTH')) {
            return 'Outstanding — AL Approved';
        }
        if (str_contains($s, 'QUERY') || str_contains($s, 'DEFICIEN')) {
            return 'Outstanding — Query';
        }
        if (str_contains($s, 'SENT FOR PAYMENT') || str_contains($s, 'PAYMENT PENDING') || str_contains($s, 'FOR PAYMENT')) {
            return 'Outstanding — Sent for Payment';
        }
        if (str_contains($s, 'WIP') || str_contains($s, 'IN PROCESS') || str_contains($s, 'UNDER PROCESS') || str_contains($s, 'OUTSTANDING') || $s === '') {
            return 'Outstanding — Claim WIP';
        }

        return 'Outstanding — Claim WIP';
    }

    /** @param array<string,mixed> $row */
    private function claimType(array $row): string
    {
        $t = strtoupper(trim((string) ($row['type_of_claim'] ?? '')));
        if (str_contains($t, 'CASH')) {
            return 'Cashless';
        }

        return 'Reimbursement';
    }

    /** @param array<string,mixed> $row */
    private function paidAmount(array $row): float
    {
        $net = $this->toFloat($row['net_sanct_amt'] ?? 0);
        if ($net > 0) {
            return $net;
        }

        return $this->toFloat($row['payment_amount'] ?? 0);
    }

    /** @param array<string,mixed> $row */
    private function claimedOsAmount(array $row): float
    {
        $os = $this->toFloat($row['claim_r_os_amt'] ?? 0);
        if ($os > 0) {
            return $os;
        }

        return $this->toFloat($row['claimed_amount'] ?? 0);
    }

    /** @param array<string,mixed> $row */
    private function ageBand(array $row): string
    {
        $band = trim((string) ($row['age_band'] ?? ''));
        if ($band !== '') {
            $band = str_replace(['–', '—'], '-', $band);
            foreach (self::AGE_BANDS as $known) {
                if (strcasecmp($band, $known) === 0) {
                    return $known;
                }
            }
            // normalize "Above 75" variants
            if (preg_match('/above\s*75|>\s*75/i', $band)) {
                return 'Above 75';
            }

            return $band;
        }

        $age = (int) $this->toFloat($row['age'] ?? 0);
        if ($age <= 5) {
            return '00-05';
        }
        if ($age <= 18) {
            return '06-18';
        }
        if ($age <= 25) {
            return '19-25';
        }
        if ($age <= 35) {
            return '26-35';
        }
        if ($age <= 45) {
            return '36-45';
        }
        if ($age <= 55) {
            return '46-55';
        }
        if ($age <= 65) {
            return '56-65';
        }
        if ($age <= 75) {
            return '66-75';
        }

        return 'Above 75';
    }

    /** @param array<string,mixed> $row */
    private function relationLabel(array $row): string
    {
        $g = strtoupper(trim((string) ($row['relation_group'] ?? '')));
        $r = strtoupper(trim((string) ($row['relation'] ?? '')));
        $src = $g !== '' ? $g : $r;

        if ($src === '' || str_contains($src, 'SELF') || str_contains($src, 'EMPLOYEE') || $src === 'PRIMARY') {
            if ($src === '' && $r === '') {
                return 'Others';
            }
            if (str_contains($src, 'SELF') || str_contains($src, 'EMPLOYEE') || $src === 'PRIMARY') {
                return 'Self';
            }
        }
        if (str_contains($src, 'IN LAW') || str_contains($src, 'INLAW') || str_contains($src, 'FATHER IN') || str_contains($src, 'MOTHER IN')) {
            return 'In Laws';
        }
        if (str_contains($src, 'PARENT') || str_contains($src, 'FATHER') || str_contains($src, 'MOTHER')) {
            return 'Parents';
        }
        if (str_contains($src, 'SPOUSE') || str_contains($src, 'WIFE') || str_contains($src, 'HUSBAND')) {
            return 'Spouse';
        }
        if (str_contains($src, 'CHILD') || str_contains($src, 'SON') || str_contains($src, 'DAUGHTER')) {
            return 'Children';
        }
        if (str_contains($src, 'SIBLING') || str_contains($src, 'BROTHER') || str_contains($src, 'SISTER')) {
            return 'Siblings';
        }

        return 'Others';
    }

    /** @param array<string,mixed> $row */
    private function isSelf(array $row): bool
    {
        return $this->relationLabel($row) === 'Self';
    }

    /** @param array<string,mixed> $row */
    private function diseaseLabel(array $row): string
    {
        $d = trim((string) ($row['disease_category'] ?? ''));
        if ($d === '') {
            $d = trim((string) ($row['diagnosis'] ?? ''));
        }

        return $d !== '' ? strtoupper($d) : 'OTHERS';
    }

    private function amountBand(float $amt): string
    {
        if ($amt <= 25000) {
            return '0-25000';
        }
        if ($amt <= 50000) {
            return '25001-50000';
        }
        if ($amt <= 100000) {
            return '50001-100000';
        }
        if ($amt <= 150000) {
            return '100001-150000';
        }
        if ($amt <= 200000) {
            return '150001-200000';
        }
        if ($amt <= 300000) {
            return '200001-300000';
        }
        if ($amt <= 500000) {
            return '300001-500000';
        }

        return 'Above 500000';
    }

    private function siBand(float $si): string
    {
        if ($si <= 200000) {
            return '0-200000';
        }
        if ($si <= 400000) {
            return '200000-400000';
        }
        if ($si <= 600000) {
            return '400000-600000';
        }

        return 'Above 600000';
    }

    private function toFloat(mixed $value): float
    {
        if (is_numeric($value)) {
            return (float) $value;
        }
        $s = preg_replace('/[^0-9.\-]/', '', (string) $value) ?? '';

        return $s === '' || $s === '-' || $s === '.' ? 0.0 : (float) $s;
    }

    private function parseFormattedNumber(mixed $value): float
    {
        return $this->toFloat($value);
    }

    /** @param list<array<string,mixed>> $rows */
    private function firstRow(array $rows): array
    {
        return $rows[0] ?? [];
    }

    private function formatDate(mixed $value): string
    {
        if ($value === null || $value === '' || $value === '0000-00-00') {
            return '';
        }
        $ts = strtotime((string) $value);

        return $ts ? date('d-M-Y', $ts) : (string) $value;
    }
}