<?php

namespace App\Libraries;

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

/**
 * Builds Medi Assist Portfolio Analysis MIS from claim_report → claims_dump_medi_assist.
 */
class MediAssistMisReportService
{
    public const DUMP_TABLE = 'claims_dump_medi_assist';

    private const VERSION = '2.3';

    private const STATUS_KEYS = [
        'paid',
        'doc_shortfall',
        'policy_exclusion',
        'processed',
        'in_process',
    ];

    private const STATUS_LABELS = [
        'paid'              => 'Paid',
        'doc_shortfall'     => 'Denied due to document shortfall',
        'policy_exclusion'  => 'Denied due to Policy Exclusion',
        'processed'         => 'Processed',
        'in_process'        => 'In Process',
    ];

    private const LANES = [
        'ip_cashless',
        'ip_reimb',
        'op_cashless',
        'op_reimb',
    ];

    private const RELATIONS = [
        'Self', 'Spouse', 'Child', 'Parent', 'Others',
    ];

    private const AGE_BANDS = [
        '0-5', '6-10', '11-15', '16-20', '21-25', '26-30', '31-35', '36-40',
        '41-45', '46-50', '51-55', '56-60', '61-65', '66-70', '71-more', 'Not classified',
    ];

    private const UTIL_BUCKETS = [
        '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', 'Above 10',
    ];

    private const AMOUNT_BANDS = [
        'Upto 10000',
        '10001 - 25000',
        '25001 – 50000',
        '50001 – 75000',
        '75001 – 100000',
        '100001 – 200000',
        '200001 – 300000',
        '300001 – 400000',
        '400001 - 500000',
        '500001 - 750000',
        '750001 - 1000000',
        'Above 1000000',
    ];

    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_medi_assist do not exist.',
            ];
        }

        $dumpRows = $this->getLinkedDumpRows($policyId);
        if ($dumpRows === []) {
            return [
                'status'  => false,
                'message' => 'No Medi Assist 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);

        $policyStartRaw = $policy['policy_start_date'] ?? $exposure['policy_start_date'] ?? null;
        $policyEndRaw   = $policy['policy_end_date'] ?? $exposure['policy_end_date'] ?? null;
        $startDate      = $this->formatDateLong($policyStartRaw);
        $endDate        = $this->formatDateLong($policyEndRaw);
        $dumpTs         = $this->dumpFileModel->getLatestClaimDumpTimestamp($policyId) ?: time();
        $reportAsOn     = date('d M Y', $dumpTs);
        $uploadAt       = date('d/m/Y g:i:s A', $dumpTs);
        $runDays        = $this->policyRunDays($policyStartRaw, $policyEndRaw);

        $statusAgg       = $this->aggregateStatus($dumpRows);
        $claimTypeAgg    = $this->aggregateClaimTypes($dumpRows);
        $pendingAgg      = $this->aggregatePendingWith($dumpRows);
        $ipFrequency     = $this->buildIpClaimFrequency($dumpRows, $lives);
        $errorsCount     = $this->countClaimsInError($dumpRows);
        $pendingSummary  = $this->buildPendingSummary($dumpRows);
        $savings         = $this->buildSavings($dumpRows);
        $topProviders    = $this->buildTopProviders($dumpRows, 10);
        $topAilments     = $this->buildTopAilments($dumpRows, 10);
        $beneficiary     = $this->buildBeneficiary($dumpRows);
        $ageBands        = $this->buildAgeBands($dumpRows);
        $utilEmp         = $this->buildUtilization($dumpRows, true);
        $utilDep         = $this->buildUtilization($dumpRows, false);
        $amountCashless  = $this->buildAmountBands($dumpRows, 'ip_cashless');
        $amountReimb     = $this->buildAmountBands($dumpRows, 'ip_reimb');
        $pendingDetail   = $this->buildPendingDetail($dumpRows);

        $totalClaims     = count($dumpRows);
        $totalIncurred   = $statusAgg['total']['incurred'];
        $icrPremium      = $premium > 0 ? round(($totalIncurred / $premium) * 100, 2) : 0.0;
        $icrEarned       = $earned > 0 ? round(($totalIncurred / $earned) * 100, 2) : 0.0;
        $ipClaimCount    = ($claimTypeAgg['ip']['total']['count'] ?? 0);
        $frequency       = $lives > 0 ? round(($ipClaimCount / $lives) * 100, 2) : 0.0;

        $data = [
            'meta' => [
                'policy_id'    => $policyId,
                'report_as_on' => $reportAsOn,
                'units'        => 'Actuals',
                'version'      => self::VERSION,
                'generated_at' => date('d-M-Y', $dumpTs),
                'generated_by' => '',
                'upload_at'    => $uploadAt,
            ],
            'header' => [
                'insurer_name'    => trim((string) ($policy['insurer_name'] ?? $exposure['insurer_name'] ?? '')),
                'corporate_name'  => trim((string) ($policy['client_name'] ?? '')),
                'policy_number'   => trim((string) ($policy['policy_no'] ?? '')),
                'policy_holder'   => trim((string) ($dumpRows[0]['policy_holder_name'] ?? $policy['client_name'] ?? '')),
                'policy_start'    => $startDate,
                'policy_end'      => $endDate,
                'policy_period'   => trim($startDate . ' To ' . $endDate),
                'lives'           => $lives,
                'total_premium'   => $premium,
                'earned_premium'  => $earned,
                'policy_run_days' => $runDays,
            ],
            'index_policies' => [[
                'policy_number'   => trim((string) ($policy['policy_no'] ?? '')),
                'policy_holder'   => trim((string) ($dumpRows[0]['policy_holder_name'] ?? $policy['client_name'] ?? '')),
                'policy_start'    => $startDate,
                'policy_end'      => $endDate,
                'lives'           => $lives,
                'total_premium'   => $premium,
                'earned_premium'  => $earned,
                'policy_run_days' => $runDays,
            ]],
            'portfolio_summary' => [
                'lives'         => $lives,
                'premium'       => $premium,
                'premium_lakh'  => $premium / 100000,
                'claims_count'  => $totalClaims,
                'incurred'      => $totalIncurred,
                'incurred_lakh' => $totalIncurred / 100000,
                'icr_premium'   => $icrPremium,
                'icr_earned'    => $icrEarned,
                'frequency'     => $frequency,
                'report_date'   => $reportAsOn,
            ],
            'policy_lives' => [
                'inception'  => max(0, $lives),
                'addition'   => 0,
                'deletion'   => 0,
                'current'    => $lives,
            ],
            'policy_premium' => [
                'first_time' => $premium,
                'addition'   => 0.0,
                'deletion'   => 0.0,
                'total'      => $premium,
                'earned'     => $earned,
            ],
            'claim_status_rows' => $statusAgg['rows'],
            'claim_type_sections' => $claimTypeAgg,
            'ip_claim_frequency'  => $ipFrequency,
            'claims_in_error'     => ['count' => $errorsCount],
            'pending_intro'       => $pendingSummary['intro'],
            'claims_pending_with' => $pendingAgg['rows'],
            'savings'             => $savings,
            'top_providers'       => $topProviders,
            'top_ailments'        => $topAilments,
            'beneficiary'         => $beneficiary,
            'age_bands'           => $ageBands,
            'utilization_employees'  => $utilEmp,
            'utilization_dependents' => $utilDep,
            'amount_bands_cashless'      => $amountCashless,
            'amount_bands_reimbursement' => $amountReimb,
            'pending_detail'             => $pendingDetail,
            'lives_movement_summary'     => $this->buildLivesMovementSummary($lives),
            'premium_movement_summary'   => $this->buildPremiumMovementSummary($premium),
            'lives_movement_monthly'     => $this->buildEmptyMonthlyLives(),
            'premium_movement_monthly'   => $this->buildEmptyMonthlyPremium($premium),
            'glossary'                   => $this->buildGlossary(),
            'dump_row_count'             => count($dumpRows),
            'fmtNum'                     => static fn ($n, int $d = 0): string => self::fmtNum($n, $d),
            'fmtRs'                      => static fn ($n): string => self::fmtRs($n),
            'fmtPct'                     => static fn ($n, int $d = 2): string => self::fmtPct($n, $d),
        ];

        $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();
    }

    public static function fmtNum(mixed $value, int $decimals = 0): string
    {
        $n = is_numeric($value) ? (float) $value : 0.0;
        if (class_exists(\NumberFormatter::class)) {
            $fmt = new \NumberFormatter('en_IN', \NumberFormatter::DECIMAL);
            $fmt->setAttribute(\NumberFormatter::MIN_FRACTION_DIGITS, $decimals);
            $fmt->setAttribute(\NumberFormatter::MAX_FRACTION_DIGITS, $decimals);

            return $fmt->format($n) ?: number_format($n, $decimals);
        }

        return number_format($n, $decimals);
    }

    public static function fmtRs(mixed $value): string
    {
        return '₹' . self::fmtNum($value, 0);
    }

    public static function fmtPct(mixed $value, int $decimals = 2): string
    {
        $n = is_numeric($value) ? (float) $value : 0.0;

        return number_format($n, $decimals) . '%';
    }

    // ----------------- aggregations -----------------

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

        foreach ($rows as $row) {
            $key = $this->statusBucket((string) ($row['claim_status'] ?? ''), $row);
            if (!isset($buckets[$key])) {
                $key = 'in_process';
            }
            $buckets[$key]['count']++;
            $buckets[$key]['claim_amount'] += $this->claimAmount($row);
            $buckets[$key]['incurred'] += $this->incurredAmount($row, $key);
        }

        $total = $this->emptyMetric();
        foreach ($buckets as $b) {
            $total['count'] += $b['count'];
            $total['claim_amount'] += $b['claim_amount'];
            $total['incurred'] += $b['incurred'];
        }

        $out = [];
        foreach (self::STATUS_KEYS as $key) {
            $b = $buckets[$key];
            $out[$key] = $this->finalizeMetric($key, $b, $total, self::STATUS_LABELS[$key] ?? $key);
        }
        $out['total'] = $this->finalizeMetric('total', $total, $total, 'Total');

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

    /**
     * @param list<array<string,mixed>> $rows
     * @return array<string,mixed>
     */
    private function aggregateClaimTypes(array $rows): array
    {
        $grid = [];
        foreach (['ip', 'op'] as $ipOp) {
            $grid[$ipOp] = [
                'cashless' => ['rows' => [], 'subtotal' => $this->emptyMetric()],
                'reimb'    => ['rows' => [], 'subtotal' => $this->emptyMetric()],
                'total'    => $this->emptyMetric(),
            ];
            foreach (['cashless', 'reimb'] as $mode) {
                foreach (self::STATUS_KEYS as $status) {
                    $grid[$ipOp][$mode]['rows'][$status] = $this->emptyMetric();
                }
            }
        }
        $grand = $this->emptyMetric();

        foreach ($rows as $row) {
            $lane   = $this->claimLane($row);
            $ipOp   = str_starts_with($lane, 'op_') ? 'op' : 'ip';
            $mode   = str_contains($lane, 'cashless') ? 'cashless' : 'reimb';
            $status = $this->statusBucket((string) ($row['claim_status'] ?? ''), $row);
            if (!in_array($status, self::STATUS_KEYS, true)) {
                $status = 'in_process';
            }

            $claimAmt = $this->claimAmount($row);
            $incurred = $this->incurredAmount($row, $status);

            $grid[$ipOp][$mode]['rows'][$status]['count']++;
            $grid[$ipOp][$mode]['rows'][$status]['claim_amount'] += $claimAmt;
            $grid[$ipOp][$mode]['rows'][$status]['incurred'] += $incurred;

            $grid[$ipOp][$mode]['subtotal']['count']++;
            $grid[$ipOp][$mode]['subtotal']['claim_amount'] += $claimAmt;
            $grid[$ipOp][$mode]['subtotal']['incurred'] += $incurred;

            $grid[$ipOp]['total']['count']++;
            $grid[$ipOp]['total']['claim_amount'] += $claimAmt;
            $grid[$ipOp]['total']['incurred'] += $incurred;

            $grand['count']++;
            $grand['claim_amount'] += $claimAmt;
            $grand['incurred'] += $incurred;
        }

        $result = ['ip' => [], 'op' => [], 'grand_total' => $this->finalizeMetric('grand_total', $grand, $grand, 'Grand Total')];

        foreach (['ip', 'op'] as $ipOp) {
            foreach (['cashless', 'reimb'] as $mode) {
                $sub = $grid[$ipOp][$mode]['subtotal'];
                $statusRows = [];
                foreach (self::STATUS_KEYS as $status) {
                    $statusRows[$status] = $this->finalizeMetric(
                        $status,
                        $grid[$ipOp][$mode]['rows'][$status],
                        $sub,
                        self::STATUS_LABELS[$status] ?? $status
                    );
                }
                $result[$ipOp][$mode] = [
                    'status_rows' => $statusRows,
                    'subtotal'    => $this->finalizeMetric('subtotal', $sub, $sub, 'Subtotal'),
                ];
            }
            $result[$ipOp]['total'] = $this->finalizeMetric(
                'total',
                $grid[$ipOp]['total'],
                $grid[$ipOp]['total'],
                'Total (' . strtoupper($ipOp) . ')'
            );
        }

        return $result;
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>}
     */
    private function aggregatePendingWith(array $rows): array
    {
        $keys = [
            '9.1 IP'              => ['lanes' => ['ip_cashless', 'ip_reimb']],
            '9.1.1 Cashless'      => ['lanes' => ['ip_cashless']],
            '9.1.2 Reimbursement' => ['lanes' => ['ip_reimb']],
            '9.2 OP'              => ['lanes' => ['op_cashless', 'op_reimb']],
            '9.2.1 Cashless'      => ['lanes' => ['op_cashless']],
            '9.2.2 Reimbursement' => ['lanes' => ['op_reimb']],
        ];

        $emptyParty = static fn () => ['count' => 0, 'amount' => 0.0];
        $grid = [];
        foreach ($keys as $label => $_) {
            $grid[$label] = [
                'label'       => $label,
                'medi_assist' => $emptyParty(),
                'insurer'     => $emptyParty(),
                'member'      => $emptyParty(),
                'provider'    => $emptyParty(),
                'total'       => $emptyParty(),
            ];
        }

        foreach ($rows as $row) {
            $status = $this->statusBucket((string) ($row['claim_status'] ?? ''), $row);
            if ($status !== 'in_process') {
                continue;
            }
            $lane    = $this->claimLane($row);
            $amount  = $this->incurredAmount($row, $status);
            $matched = [];
            foreach ($keys as $label => $cfg) {
                if (in_array($lane, $cfg['lanes'], true)) {
                    $matched[] = $label;
                }
            }
            foreach ($matched as $label) {
                $grid[$label]['medi_assist']['count']++;
                $grid[$label]['medi_assist']['amount'] += $amount;
                $grid[$label]['total']['count']++;
                $grid[$label]['total']['amount'] += $amount;
            }
        }

        $out = array_values($grid);
        $out[] = $this->sumPendingRows('Total', $out);

        return ['rows' => $out];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array<string,mixed>
     */
    private function buildIpClaimFrequency(array $rows, int $lives): array
    {
        $cashless = 0;
        $reimb    = 0;
        foreach ($rows as $row) {
            $lane = $this->claimLane($row);
            if (!str_starts_with($lane, 'ip_')) {
                continue;
            }
            if ($lane === 'ip_cashless') {
                $cashless++;
            } else {
                $reimb++;
            }
        }
        $total = $cashless + $reimb;
        $rate  = static fn (int $c): float => $lives > 0 ? round(($c / $lives) * 100, 2) : 0.0;

        return [
            'cashless_count' => $cashless,
            'reimb_count'    => $reimb,
            'total'          => $total,
            'cashless_rate'  => $rate($cashless),
            'reimb_rate'     => $rate($reimb),
            'total_rate'     => $rate($total),
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    private function countClaimsInError(array $rows): int
    {
        $count = 0;
        foreach ($rows as $row) {
            if (trim((string) ($row['error_group'] ?? '')) !== '') {
                $count++;
            }
        }

        return $count;
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{intro:string,pending_count:int,pending_amount:float,error_count:int}
     */
    private function buildPendingSummary(array $rows): array
    {
        $pendingCount  = 0;
        $pendingAmount = 0.0;
        $errorCount    = 0;
        foreach ($rows as $row) {
            $status = $this->statusBucket((string) ($row['claim_status'] ?? ''), $row);
            if ($status === 'in_process') {
                $pendingCount++;
                $pendingAmount += $this->incurredAmount($row, $status);
            }
            if (trim((string) ($row['error_group'] ?? '')) !== '') {
                $errorCount++;
            }
        }

        $intro = sprintf(
            'As of %s, %d Claims with an Incurred amount of %s are pending. Out of them %d claims are under error log category',
            date('d M Y'),
            $pendingCount,
            self::fmtRs($pendingAmount),
            $errorCount
        );

        return [
            'intro'          => $intro,
            'pending_count'  => $pendingCount,
            'pending_amount' => $pendingAmount,
            'error_count'    => $errorCount,
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return list<array<string,mixed>>
     */
    private function buildSavings(array $rows): array
    {
        $map = [
            ['group' => 'Policy Driven', 'label' => 'Proportionate Deduction', 'field' => 'deduction_amount_prorata'],
            ['group' => 'Policy Driven', 'label' => 'Defined Benefit', 'field' => 'deduction_amount_excess_ailment'],
            ['group' => 'Policy Driven', 'label' => 'Copay', 'field' => 'deduction_amount_copay'],
            ['group' => 'Others', 'label' => 'Hospital Discount', 'field' => 'deduction_amount_hospital_discount'],
            ['group' => 'Others', 'label' => 'Case Management', 'field' => null],
            ['group' => 'Others', 'label' => '*Fraud Waste and Abuse', 'field' => 'deduction_amount_intimation_penalty'],
        ];

        $out = [];
        foreach ($map as $item) {
            $count  = 0;
            $amount = 0.0;
            if ($item['field'] !== null) {
                foreach ($rows as $row) {
                    $val = $this->toFloat($row[$item['field']] ?? 0);
                    if ($val > 0) {
                        $count++;
                        $amount += $val;
                    }
                }
            }
            $out[] = [
                'group'  => $item['group'],
                'label'  => $item['label'],
                'count'  => $count,
                'amount' => $amount,
            ];
        }

        return $out;
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function buildTopProviders(array $rows, int $limit): array
    {
        $map = [];
        foreach ($rows as $row) {
            if (!$this->isIpAnalyticsRow($row)) {
                continue;
            }
            $name = trim((string) ($row['hospital_name'] ?? ''));
            if ($name === '') {
                $name = 'Unknown';
            }
            $key = strtoupper($name);
            if (!isset($map[$key])) {
                $map[$key] = ['name' => $name, 'count' => 0, 'amount' => 0.0];
            }
            $map[$key]['count']++;
            $map[$key]['amount'] += $this->approvedAmount($row);
        }

        uasort($map, static fn ($a, $b) => $b['amount'] <=> $a['amount']);
        $top = array_slice(array_values($map), 0, $limit);

        $totalCount = array_sum(array_column($top, 'count'));
        $totalAmt   = array_sum(array_column($top, 'amount'));
        $out        = [];
        foreach ($top as $item) {
            $out[] = [
                'name'            => $item['name'],
                'count'           => $item['count'],
                'count_pct'       => $totalCount > 0 ? round(($item['count'] / $totalCount) * 100, 2) : 0.0,
                'approved_amount' => $item['amount'],
                'amount_pct'      => $totalAmt > 0 ? round(($item['amount'] / $totalAmt) * 100, 2) : 0.0,
            ];
        }

        return [
            'rows'  => $out,
            'total' => [
                'count'           => $totalCount,
                'count_pct'       => 100.0,
                'approved_amount' => $totalAmt,
                'amount_pct'      => 100.0,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function buildTopAilments(array $rows, int $limit): array
    {
        $map = [];
        foreach ($rows as $row) {
            if (!$this->isIpAnalyticsRow($row)) {
                continue;
            }
            $name = trim((string) ($row['primary_icd_group'] ?? ''));
            if ($name === '') {
                $name = trim((string) ($row['primary_ailment_name'] ?? 'Others'));
            }
            if ($name === '') {
                $name = 'Others';
            }
            $key = strtoupper($name);
            if (!isset($map[$key])) {
                $map[$key] = ['name' => $name, 'count' => 0, 'amount' => 0.0];
            }
            $map[$key]['count']++;
            $map[$key]['amount'] += $this->approvedAmount($row);
        }

        uasort($map, static fn ($a, $b) => $b['amount'] <=> $a['amount']);
        $top = array_slice(array_values($map), 0, $limit);

        $totalCount = array_sum(array_column($top, 'count'));
        $totalAmt   = array_sum(array_column($top, 'amount'));
        $out        = [];
        foreach ($top as $item) {
            $out[] = [
                'name'            => $item['name'],
                'count'           => $item['count'],
                'count_pct'       => $totalCount > 0 ? round(($item['count'] / $totalCount) * 100, 2) : 0.0,
                'approved_amount' => $item['amount'],
                'amount_pct'      => $totalAmt > 0 ? round(($item['amount'] / $totalAmt) * 100, 2) : 0.0,
            ];
        }

        return [
            'rows'  => $out,
            'total' => [
                'count'           => $totalCount,
                'count_pct'       => 100.0,
                'approved_amount' => $totalAmt,
                'amount_pct'      => 100.0,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function buildBeneficiary(array $rows): array
    {
        $buckets = array_fill_keys(self::RELATIONS, ['count' => 0, 'amount' => 0.0]);
        foreach ($rows as $row) {
            if (!$this->isIpAnalyticsRow($row)) {
                continue;
            }
            $rel = $this->relationLabel($row);
            $buckets[$rel]['count']++;
            $buckets[$rel]['amount'] += $this->approvedAmount($row);
        }

        $totalCount = array_sum(array_column($buckets, 'count'));
        $totalAmt   = array_sum(array_column($buckets, 'amount'));
        $out        = [];
        foreach (self::RELATIONS as $rel) {
            $b = $buckets[$rel];
            $out[] = [
                'relation'        => $rel,
                'count'           => $b['count'],
                'count_pct'       => $totalCount > 0 ? round(($b['count'] / $totalCount) * 100, 2) : 0.0,
                'approved_amount' => $b['amount'],
                'amount_pct'      => $totalAmt > 0 ? round(($b['amount'] / $totalAmt) * 100, 2) : 0.0,
            ];
        }

        return [
            'rows'  => $out,
            'total' => [
                'count'           => $totalCount,
                'count_pct'       => 100.0,
                'approved_amount' => $totalAmt,
                'amount_pct'      => 100.0,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function buildAgeBands(array $rows): array
    {
        $buckets = [];
        foreach (self::AGE_BANDS as $band) {
            $buckets[$band] = ['lives' => 0, 'count' => 0, 'amount' => 0.0];
        }

        $seenLives = [];
        foreach ($rows as $row) {
            if (!$this->isIpAnalyticsRow($row)) {
                continue;
            }
            $band = $this->ageBand($row);
            if (!isset($buckets[$band])) {
                $band = 'Not classified';
            }
            $buckets[$band]['count']++;
            $buckets[$band]['amount'] += $this->approvedAmount($row);

            $memberKey = trim((string) ($row['benef_maid'] ?? ''));
            if ($memberKey === '') {
                $memberKey = trim((string) ($row['benef_insurer_id'] ?? '')) . '|' . trim((string) ($row['benef_name'] ?? ''));
            }
            if ($memberKey !== '' && !isset($seenLives[$band][$memberKey])) {
                $seenLives[$band][$memberKey] = true;
                $buckets[$band]['lives']++;
            }
        }

        $totalCount = 0;
        $totalLives = 0;
        $totalAmt   = 0.0;
        $out        = [];
        foreach (self::AGE_BANDS as $band) {
            $b = $buckets[$band];
            $totalCount += $b['count'];
            $totalLives += $b['lives'];
            $totalAmt   += $b['amount'];
            $out[] = [
                'band'            => $band,
                'lives'           => $b['lives'],
                'count'           => $b['count'],
                'count_pct'       => 0.0,
                'approved_amount' => $b['amount'],
                'amount_pct'      => 0.0,
            ];
        }
        foreach ($out as &$row) {
            $row['count_pct']  = $totalCount > 0 ? round(($row['count'] / $totalCount) * 100, 2) : 0.0;
            $row['amount_pct'] = $totalAmt > 0 ? round(($row['approved_amount'] / $totalAmt) * 100, 2) : 0.0;
        }
        unset($row);

        return [
            'rows'  => $out,
            'total' => [
                'lives'           => $totalLives,
                'count'           => $totalCount,
                'count_pct'       => 100.0,
                'approved_amount' => $totalAmt,
                'amount_pct'      => 100.0,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function buildUtilization(array $rows, bool $employees): array
    {
        $byMember = [];
        foreach ($rows as $row) {
            if (!$this->isIpAnalyticsRow($row)) {
                continue;
            }
            $isSelf = $this->relationLabel($row) === 'Self';
            if ($employees && !$isSelf) {
                continue;
            }
            if (!$employees && $isSelf) {
                continue;
            }
            $key = trim((string) ($row['pribenef_employee_code'] ?? ''));
            if ($key === '') {
                $key = trim((string) ($row['benef_maid'] ?? ''));
            }
            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->approvedAmount($row);
        }

        $bands = [];
        foreach (self::UTIL_BUCKETS as $label) {
            $bands[$label] = ['count' => 0, 'amount' => 0.0];
        }
        foreach ($byMember as $member) {
            $c = $member['count'];
            $label = $c >= 11 ? 'Above 10' : (string) $c;
            if (!isset($bands[$label])) {
                $label = 'Above 10';
            }
            $bands[$label]['count']++;
            $bands[$label]['amount'] += $member['amount'];
        }

        $totalMembers = array_sum(array_column($bands, 'count'));
        $totalAmt     = array_sum(array_column($bands, 'amount'));
        $out          = [];
        foreach (self::UTIL_BUCKETS as $label) {
            $b = $bands[$label];
            $out[] = [
                'label'           => $label,
                'member_count'    => $b['count'],
                'count_pct'       => $totalMembers > 0 ? round(($b['count'] / $totalMembers) * 100, 2) : 0.0,
                'approved_amount' => $b['amount'],
                'amount_pct'      => $totalAmt > 0 ? round(($b['amount'] / $totalAmt) * 100, 2) : 0.0,
            ];
        }

        return [
            'rows'  => $out,
            'total' => [
                'member_count'    => $totalMembers,
                'count_pct'       => 100.0,
                'approved_amount' => $totalAmt,
                'amount_pct'      => 100.0,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function buildAmountBands(array $rows, string $lane): array
    {
        $rels = self::RELATIONS;
        $grid = [];
        foreach (self::AMOUNT_BANDS as $band) {
            $grid[$band] = [
                'relations' => array_fill_keys($rels, ['count' => 0, 'amount' => 0.0]),
                'total'     => ['count' => 0, 'amount' => 0.0],
            ];
        }

        foreach ($rows as $row) {
            if ($this->claimLane($row) !== $lane) {
                continue;
            }
            if (!$this->isIpAnalyticsRow($row)) {
                continue;
            }
            $amt  = $this->approvedAmount($row);
            $band = $this->amountBand($amt);
            $rel  = $this->relationLabel($row);
            if (!isset($grid[$band])) {
                continue;
            }
            $grid[$band]['relations'][$rel]['count']++;
            $grid[$band]['relations'][$rel]['amount'] += $amt;
            $grid[$band]['total']['count']++;
            $grid[$band]['total']['amount'] += $amt;
        }

        $grandCount = 0;
        $grandAmt   = 0.0;
        $relTotals  = array_fill_keys($rels, ['count' => 0, 'amount' => 0.0]);
        $out        = [];

        foreach (self::AMOUNT_BANDS as $band) {
            $g = $grid[$band];
            $grandCount += $g['total']['count'];
            $grandAmt   += $g['total']['amount'];
            foreach ($rels as $rel) {
                $relTotals[$rel]['count']  += $g['relations'][$rel]['count'];
                $relTotals[$rel]['amount'] += $g['relations'][$rel]['amount'];
            }
            $out[] = [
                'band'      => $band,
                'relations' => $g['relations'],
                'total'     => $g['total'] + ['pct' => 0.0],
            ];
        }

        foreach ($out as &$row) {
            $row['total']['pct'] = $grandCount > 0
                ? round(($row['total']['count'] / $grandCount) * 100, 2)
                : 0.0;
        }
        unset($row);

        return [
            'rows'  => $out,
            'total' => [
                'relations' => $relTotals,
                'count'     => $grandCount,
                'amount'    => $grandAmt,
                'pct'       => 100.0,
            ],
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array{rows:list<array<string,mixed>>,totals:array<string,int>}
     */
    private function buildPendingDetail(array $rows): array
    {
        $labels = [
            'IP' => [
                'Cashless' => [
                    'A.1.1 Documents awaited — Intimated to Insurer',
                    'A.1.2 In process - Decision pending — Intimated to Insurer',
                    'A.1.3 In process - Denial pending',
                    'A.1.4 In process - Payment pending — Intimated to Insurer',
                    'A.1.4 In process - Payment pending — Member Addition',
                ],
                'Reimbursement' => [
                    'A.1.1 Documents awaited — Intimated to Insurer',
                    'A.1.2 In process - Decision pending — Intimated to Insurer',
                    'A.1.3 In process - Denial pending — Intimated to Insurer',
                    'A.1.4 In process - Payment pending — Intimated to Insurer',
                ],
            ],
        ];

        $emptyCounts = static fn (): array => [
            'medi_assist' => 0,
            'insurer'     => 0,
            'member'      => 0,
            'provider'    => 0,
            'total'       => 0,
        ];

        $rowsOut = [];
        $grand   = $emptyCounts();

        foreach ($labels['IP'] as $mode => $items) {
            $rowsOut[] = ['label' => $mode, 'is_group' => true, 'counts' => $emptyCounts()];
            $subtotal  = $emptyCounts();
            foreach ($items as $itemLabel) {
                $counts = $emptyCounts();
                $rowsOut[] = ['label' => $itemLabel, 'is_group' => false, 'counts' => $counts];
            }
            $rowsOut[] = ['label' => 'Subtotal', 'is_group' => true, 'counts' => $subtotal];
        }

        $pendingCount = 0;
        foreach ($rows as $row) {
            if ($this->statusBucket((string) ($row['claim_status'] ?? ''), $row) === 'in_process') {
                $pendingCount++;
            }
        }
        if ($pendingCount > 0 && isset($rowsOut[1])) {
            $rowsOut[1]['counts']['medi_assist'] = $pendingCount;
            $rowsOut[1]['counts']['total']       = $pendingCount;
            $grand['medi_assist']                = $pendingCount;
            $grand['total']                      = $pendingCount;
        }

        return ['rows' => $rowsOut, 'totals' => $grand];
    }

    /**
     * @return array<string,mixed>
     */
    private function buildLivesMovementSummary(int $lives): array
    {
        return [
            'rows' => [
                ['particular' => 'Inception Lives', 'inception_addition' => 0, 'deletion' => null, 'current' => 0],
                ['particular' => 'Addition - Inception Lives', 'inception_addition' => $lives, 'deletion' => null, 'current' => $lives],
                ['particular' => 'Addition - New', 'inception_addition' => 0, 'deletion' => null, 'current' => 0],
                ['particular' => 'Deletion', 'inception_addition' => null, 'deletion' => 0, 'current' => 0],
            ],
            'total' => [
                'inception_addition' => $lives,
                'deletion'           => 0,
                'current'            => $lives,
            ],
        ];
    }

    /**
     * @return array<string,mixed>
     */
    private function buildPremiumMovementSummary(float $premium): array
    {
        return [
            'rows' => [
                ['particular' => 'Opening Premium - Inception', 'premium' => $premium, 'refund' => null, 'total' => $premium],
                ['particular' => 'Addition', 'premium' => 0.0, 'refund' => null, 'total' => 0.0],
                ['particular' => 'Deletion', 'premium' => null, 'refund' => 0.0, 'total' => 0.0],
            ],
            'total' => [
                'premium' => $premium,
                'refund'  => 0.0,
                'total'   => $premium,
            ],
        ];
    }

    /**
     * @return array<string,mixed>
     */
    private function buildEmptyMonthlyLives(): array
    {
        $particulars = [
            'Inception Lives',
            'Addition - Inception Lives',
            'Addition - New',
            'Deletion',
        ];
        $relations = [
            'employee', 'spouse', 'children',
            'father', 'mother', 'siblings', 'others',
        ];
        $rows = [];
        for ($m = 1; $m <= 12; $m++) {
            foreach ($particulars as $particular) {
                $row = ['month' => 'M' . $m, 'particular' => $particular];
                foreach ($relations as $rel) {
                    $row[$rel . '_add'] = 0;
                    $row[$rel . '_del'] = $particular === 'Deletion' ? 0 : null;
                }
                $rows[] = $row;
            }
        }

        $total = ['month' => '', 'particular' => 'Total'];
        foreach ($relations as $rel) {
            $total[$rel . '_add'] = 0;
            $total[$rel . '_del'] = 0;
        }

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

    /**
     * @return array<string,mixed>
     */
    private function buildEmptyMonthlyPremium(float $closingPremium): array
    {
        $rows = [];
        for ($m = 1; $m <= 12; $m++) {
            if ($m === 1) {
                $rows[] = ['month' => 'Month-1', 'particular' => 'Opening Premium - Inception', 'premium' => $closingPremium, 'refund' => null, 'total' => $closingPremium];
            }
            $rows[] = ['month' => 'Month-' . $m, 'particular' => 'Addition', 'premium' => 0.0, 'refund' => null, 'total' => 0.0];
            $rows[] = ['month' => 'Month-' . $m, 'particular' => 'Deletion', 'premium' => null, 'refund' => 0.0, 'total' => 0.0];
        }

        return [
            'rows'  => $rows,
            'total' => [
                'month'      => '',
                'particular' => 'Closing Premium',
                'premium'    => $closingPremium,
                'refund'     => 0.0,
                'total'      => $closingPremium,
            ],
        ];
    }

    /**
     * @return list<array{label:string,description:string}>
     */
    private function buildGlossary(): array
    {
        return [
            ['label' => '1.0 Policy Lives', 'description' => 'Details of insured members.'],
            ['label' => '1.1 At Inception & Addition', 'description' => 'Total lives at start and added during policy.'],
            ['label' => '1.1.1 At Inception', 'description' => 'Lives covered from policy inception date.'],
            ['label' => '1.1.2 Addition', 'description' => 'Lives added via endorsement.'],
            ['label' => '1.2 Deletion', 'description' => 'Lives removed via endorsement.'],
            ['label' => '1.3 Current Lives', 'description' => 'At inception + additions - deletions.'],
            ['label' => '2.0 Policy Premium', 'description' => 'Breakdown of premium values.'],
            ['label' => '2.1 First Time', 'description' => 'Initial premium.'],
            ['label' => '2.2 Addition', 'description' => 'Premium added via endorsement.'],
            ['label' => '2.3 Deletion', 'description' => 'Premium reduced via endorsement.'],
            ['label' => '2.4 Total Premium', 'description' => 'First + Additions - Deletions.'],
            ['label' => '2.5 Earned Premium', 'description' => 'Premium applicable to elapsed policy period.'],
            ['label' => '3.0 Claim Status', 'description' => 'Indicates processing stage of claims.'],
            ['label' => '4 IPD Cashless', 'description' => 'In-patient claims where treatment cost is directly settled with the provider as per policy terms.'],
            ['label' => '4.1 Paid', 'description' => 'Payment completed and details available.'],
            ['label' => '4.2 Denied – Document Shortfall', 'description' => 'Denied due to missing documents.'],
            ['label' => '4.3 Denied – Inadmissibility', 'description' => 'Denied as per policy exclusions or inadmissible conditions.'],
            ['label' => '4.4 Processed', 'description' => 'Claims where processing is complete and ready for payment upload.'],
            ['label' => '4.5 In Process', 'description' => 'Under review, investigation, or awaiting inputs.'],
            ['label' => '5 IPD Reimbursement', 'description' => 'In-patient claims where the insured pays first and is reimbursed later as per policy terms.'],
            ['label' => '6 OPD Cashless', 'description' => 'Out-patient claims where the provider is paid directly.'],
            ['label' => '7 OPD Reimbursement', 'description' => 'Out-patient claims where the insured pays and is reimbursed.'],
            ['label' => '9 Pending With', 'description' => 'Claims pending with Medi Assist, Insurer, Member, or Provider.'],
            ['label' => '8.1 IPD Claim Count', 'description' => 'Total number of IPD claims.'],
            ['label' => '8.2 Claims per 100 Lives (%)', 'description' => 'Claim frequency per 100 insured lives.'],
            ['label' => '10 Claims that are in Error', 'description' => 'Claims that are in Error'],
        ];
    }

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

    /** @return array{count:int,claim_amount:float,incurred:float} */
    private function emptyMetric(): array
    {
        return ['count' => 0, 'claim_amount' => 0.0, 'incurred' => 0.0];
    }

    /**
     * @param array{count:int,claim_amount:float,incurred:float} $metric
     * @param array{count:int,claim_amount:float,incurred:float} $total
     * @return array<string,mixed>
     */
    private function finalizeMetric(string $key, array $metric, array $total, string $label): array
    {
        return [
            'key'               => $key,
            'label'             => $label,
            'count'             => $metric['count'],
            'count_pct'         => $total['count'] > 0 ? round(($metric['count'] / $total['count']) * 100, 2) : 0.0,
            'claim_amount'      => $metric['claim_amount'],
            'claim_amount_pct'  => $total['claim_amount'] > 0
                ? round(($metric['claim_amount'] / $total['claim_amount']) * 100, 2)
                : 0.0,
            'incurred'          => $metric['incurred'],
            'incurred_pct'      => $total['incurred'] > 0
                ? round(($metric['incurred'] / $total['incurred']) * 100, 2)
                : 0.0,
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @return array<string,mixed>
     */
    private function sumPendingRows(string $label, array $rows): array
    {
        $emptyParty = static fn () => ['count' => 0, 'amount' => 0.0];
        $sum = [
            'label'       => $label,
            'medi_assist' => $emptyParty(),
            'insurer'     => $emptyParty(),
            'member'      => $emptyParty(),
            'provider'    => $emptyParty(),
            'total'       => $emptyParty(),
        ];
        foreach ($rows as $row) {
            if (($row['label'] ?? '') === $label) {
                continue;
            }
            foreach (['medi_assist', 'insurer', 'member', 'provider', 'total'] as $party) {
                $sum[$party]['count']  += (int) ($row[$party]['count'] ?? 0);
                $sum[$party]['amount'] += (float) ($row[$party]['amount'] ?? 0);
            }
        }

        return $sum;
    }

    /** @param array<string,mixed> $row */
    private function statusBucket(string $status, array $row): string
    {
        $s = strtoupper(trim(str_replace(['_', '-'], ' ', $status)));
        $s = preg_replace('/\s+/', ' ', $s) ?? $s;
        $denial = strtoupper(trim(
            (string) ($row['denial_short_description'] ?? '') . ' '
            . (string) ($row['denial_description'] ?? '')
        ));

        if (
            str_contains($s, 'DENIED')
            || str_contains($s, 'REJECT')
            || str_contains($s, 'DENIAL')
            || str_contains($s, 'CANCEL')
        ) {
            if (
                str_contains($denial, 'DOCUMENT')
                || str_contains($denial, 'SHORTFALL')
                || str_contains($s, 'DOCUMENT')
                || str_contains($s, 'SHORTFALL')
                || str_contains($s, 'INFORMATION AWAITED')
                || str_contains($s, 'DEFICIEN')
            ) {
                return 'doc_shortfall';
            }

            return 'policy_exclusion';
        }

        if (str_contains($s, 'PAID') || str_contains($s, 'SETTLED') || $s === 'CLOSED') {
            return 'paid';
        }

        if (str_contains($s, 'PROCESSED') || str_contains($s, 'DEBIT NOTE')) {
            return 'processed';
        }

        return 'in_process';
    }

    /** @param array<string,mixed> $row */
    private function claimLane(array $row): string
    {
        $type = strtolower(trim((string) ($row['claim_type'] ?? '')));
        $sub  = strtolower(trim((string) ($row['claim_sub_type'] ?? '')));
        $src  = trim($type . ' ' . $sub);
        if ($src === '' && trim((string) ($row['claim_mode_of_rcpt'] ?? '')) !== '') {
            $src = strtolower((string) $row['claim_mode_of_rcpt']);
        }

        $isOp = (bool) preg_match('/\b(opd|op)\b/i', $src);
        $isCashless = str_contains($src, 'cashless')
            || str_contains($src, 'cash')
            || strtoupper(trim((string) ($row['is_cashlessanywhere'] ?? ''))) === 'Y';

        if ($isOp) {
            return $isCashless ? 'op_cashless' : 'op_reimb';
        }

        return $isCashless ? 'ip_cashless' : 'ip_reimb';
    }

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

    /** @param array<string,mixed> $row */
    private function incurredAmount(array $row, string $statusBucket): float
    {
        if (in_array($statusBucket, ['doc_shortfall', 'policy_exclusion'], true)) {
            return 0.0;
        }

        $incurred = $this->toFloat($row['incurred_amount'] ?? null);
        if ($incurred > 0) {
            return $incurred;
        }

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

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

        return $this->incurredAmount($row, $this->statusBucket((string) ($row['claim_status'] ?? ''), $row));
    }

    /** @param array<string,mixed> $row */
    private function isIpAnalyticsRow(array $row): bool
    {
        if (!str_starts_with($this->claimLane($row), 'ip_')) {
            return false;
        }
        $status = $this->statusBucket((string) ($row['claim_status'] ?? ''), $row);

        return in_array($status, ['paid', 'processed'], true);
    }

    /** @param array<string,mixed> $row */
    private function relationLabel(array $row): string
    {
        $r = strtoupper(trim((string) ($row['benef_relation'] ?? '')));
        if ($r === '' || str_contains($r, 'SELF') || str_contains($r, 'EMPLOYEE')) {
            return 'Self';
        }
        if (str_contains($r, 'SPOUSE') || str_contains($r, 'WIFE') || str_contains($r, 'HUSBAND')) {
            return 'Spouse';
        }
        if (str_contains($r, 'CHILD') || str_contains($r, 'SON') || str_contains($r, 'DAUGHTER')) {
            return 'Child';
        }
        if (str_contains($r, 'PARENT') || str_contains($r, 'FATHER') || str_contains($r, 'MOTHER') || str_contains($r, 'IN LAW') || str_contains($r, 'IN-LAW')) {
            return 'Parent';
        }

        return 'Others';
    }

    /** @param array<string,mixed> $row */
    private function ageBand(array $row): string
    {
        $age = (int) $this->toFloat($row['benef_age'] ?? 0);
        if ($age <= 0) {
            return 'Not classified';
        }
        if ($age <= 5) {
            return '0-5';
        }
        if ($age <= 10) {
            return '6-10';
        }
        if ($age <= 15) {
            return '11-15';
        }
        if ($age <= 20) {
            return '16-20';
        }
        if ($age <= 25) {
            return '21-25';
        }
        if ($age <= 30) {
            return '26-30';
        }
        if ($age <= 35) {
            return '31-35';
        }
        if ($age <= 40) {
            return '36-40';
        }
        if ($age <= 45) {
            return '41-45';
        }
        if ($age <= 50) {
            return '46-50';
        }
        if ($age <= 55) {
            return '51-55';
        }
        if ($age <= 60) {
            return '56-60';
        }
        if ($age <= 65) {
            return '61-65';
        }
        if ($age <= 70) {
            return '66-70';
        }

        return '71-more';
    }

    private function amountBand(float $amt): string
    {
        if ($amt <= 10000) {
            return 'Upto 10000';
        }
        if ($amt <= 25000) {
            return '10001 - 25000';
        }
        if ($amt <= 50000) {
            return '25001 – 50000';
        }
        if ($amt <= 75000) {
            return '50001 – 75000';
        }
        if ($amt <= 100000) {
            return '75001 – 100000';
        }
        if ($amt <= 200000) {
            return '100001 – 200000';
        }
        if ($amt <= 300000) {
            return '200001 – 300000';
        }
        if ($amt <= 400000) {
            return '300001 – 400000';
        }
        if ($amt <= 500000) {
            return '400001 - 500000';
        }
        if ($amt <= 750000) {
            return '500001 - 750000';
        }
        if ($amt <= 1000000) {
            return '750001 - 1000000';
        }

        return 'Above 1000000';
    }

    private function policyRunDays(mixed $start, mixed $end): int
    {
        $startTs = strtotime((string) $start);
        $endTs   = strtotime((string) $end);
        if ($startTs === false || $endTs === false || $endTs < $startTs) {
            return 0;
        }

        return (int) floor(($endTs - $startTs) / 86400) + 1;
    }

    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 formatDateLong(mixed $value): string
    {
        if ($value === null || $value === '' || $value === '0000-00-00') {
            return '';
        }
        $ts = strtotime((string) $value);

        return $ts ? date('F j, Y', $ts) : (string) $value;
    }

    private function formatDateShort(mixed $value): string
    {
        if ($value === null || $value === '' || $value === '0000-00-00') {
            return date('d M Y');
        }
        $ts = strtotime((string) $value);

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