<?php

namespace App\Libraries;

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

/**
 * Builds ABHI MIS report payload from claim_report → claims_dump_abhi linkage + KPI header data.
 */
class AbhiMisReportService
{
    public const DUMP_TABLE = 'claims_dump_abhi';

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

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

        $premiumRaw = $this->parseFormattedNumber($premiumRow['premium_as_on_date'] ?? null);
        $earnedRaw  = $this->parseFormattedNumber($experience['earned_premium'] ?? null);

        $statusSummary   = $this->aggregateByStatus($dumpRows);
        $claimTypeSummary = $this->aggregateByClaimType($dumpRows);
        $incurredTotal   = $statusSummary['incurred_amount'];
        $icrPct          = $earnedRaw > 0 ? round(($incurredTotal / $earnedRaw) * 100, 2) : 0.0;

        $settledCount      = $statusSummary['buckets']['Settled']['count'] ?? 0;
        $acsIpd            = $settledCount > 0
            ? round(($statusSummary['buckets']['Settled']['paid_amount'] ?? 0) / $settledCount, 0)
            : 0;

        $lives = (int) ($livesRow['current_lives'] ?? 0);

        $data = [
            'meta' => [
                'policy_id'     => $policyId,
                'last_refresh'  => $this->dumpFileModel->getGeneratedAtForPolicy($policyId, 'd-M-Y') ?: date('d-M-Y'),
                'generated_at'  => $this->dumpFileModel->getGeneratedAtForPolicy($policyId, 'd-M-Y') ?: date('d-M-Y'),
                'quote_type'    => 'IPD + OPD',
                'client_name'   => trim((string) ($policy['client_name'] ?? '')),
                'tpa_name'      => trim((string) ($policy['tpa_name'] ?? 'Aditya Birla Health Insurance Co. Limited')),
            ],
            'header' => [
                'insurer_name'       => trim((string) ($policy['client_name'] ?? '')),
                'claim_processor'    => trim((string) ($policy['tpa_name'] ?? 'Aditya Birla Health Insurance Co. Limited')),
                'policy_number'      => trim((string) ($policy['policy_no'] ?? '')),
                'policy_start_date'  => $this->formatDate($policy['policy_start_date'] ?? $exposure['policy_start_date'] ?? null),
                'policy_end_date'    => $this->formatDate($policy['policy_end_date'] ?? $exposure['policy_end_date'] ?? null),
                'lives'              => $lives,
                'premium_paid'       => $premiumRaw,
                'earned_premium'     => $earnedRaw,
                'insurer_legal_name' => trim((string) ($exposure['insurer_name'] ?? $policy['insurer_name'] ?? '')),
            ],
            'summary' => [
                'claim_paid_outstanding'   => $incurredTotal,
                'claims_paid_amount'       => $statusSummary['buckets']['Settled']['paid_amount'] ?? 0,
                'claim_outstanding_amount' => $statusSummary['buckets']['Outstanding']['incurred_amount'] ?? 0,
                'acs_ipd_claim'            => $acsIpd,
                'claims_paid_count'        => $settledCount,
                'claims_outstanding_count' => $statusSummary['buckets']['Outstanding']['count'] ?? 0,
                'icr_pct'                  => $icrPct,
            ],
            'status_breakdown' => $statusSummary,
            'claim_type'       => $claimTypeSummary,
            'gender'           => $this->aggregateByGender($dumpRows),
            'relation'         => $this->aggregateByRelation($dumpRows),
            'member_type'      => $this->aggregateByMemberType($dumpRows),
            'age_band'         => $this->aggregateByAgeBand($dumpRows),
            'diagnosis'        => $this->aggregateByDiagnosis($dumpRows, 10),
            'hospital'         => $this->aggregateByHospital($dumpRows),
            'city'             => $this->aggregateByCity($dumpRows),
            'monthly'          => $this->aggregateByMonth($dumpRows),
            'utilization'      => [
                'employees'  => $this->aggregateUtilization($dumpRows, 'Self'),
                'dependents' => $this->aggregateUtilization($dumpRows, 'Dependent'),
            ],
            'dump_row_count' => count($dumpRows),
        ];

        $data['view_model'] = $this->buildViewModel($data, $dumpRows);

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

    /**
     * Shape report data exactly like ABH_MIS_Report.html JS constants.
     *
     * @param array<string,mixed> $data
     * @param list<array<string,mixed>> $dumpRows
     * @return array<string,mixed>
     */
    public function buildViewModel(array $data, array $dumpRows): array
    {
        $header  = $data['header'] ?? [];
        $summary = $data['summary'] ?? [];
        $buckets = $data['status_breakdown']['buckets'] ?? [];

        $claimStatus = [];
        foreach (['Reported', 'Settled', 'Outstanding', 'Rejected'] as $status) {
            $b = $buckets[$status] ?? [];
            $incurred = (float) ($b['incurred_amount'] ?? 0);
            if ($status === 'Reported') {
                $incurred = (float) ($buckets['Settled']['incurred_amount'] ?? 0)
                    + (float) ($buckets['Outstanding']['incurred_amount'] ?? 0)
                    + (float) ($buckets['Rejected']['reported_amount'] ?? 0);
            }
            if ($status === 'Rejected') {
                $incurred = (float) ($b['reported_amount'] ?? 0);
            }
            $claimStatus[] = [
                'status'   => $status,
                'number'   => (int) ($b['count'] ?? 0),
                'reported' => (float) ($b['reported_amount'] ?? 0),
                'incurred' => $incurred,
            ];
        }

        $premiumPaid = (float) ($header['premium_paid'] ?? 0);
        $policyPremium = [
            ['sr' => 1, 'label' => 'At Inception', 'amount' => $premiumPaid],
            ['sr' => 2, 'label' => 'Addition', 'amount' => 0],
            ['sr' => 3, 'label' => 'Deletion', 'amount' => 0],
        ];

        $reimbTAT = $this->aggregateReimbursementTat($dumpRows);
        $claimTypeRows = $this->buildClaimTypeRows($dumpRows);
        $claimTypeTotal = $claimTypeRows['_total'] ?? ['count' => 0, 'amount' => 0, 'incurred' => 0];

        $gender = $data['gender'] ?? [];
        $genderRows = [];
        foreach ($gender as $label => $row) {
            if ($label === '_total') {
                continue;
            }
            $genderRows[] = [
                'label'  => $label,
                'count'  => (int) ($row['count'] ?? 0),
                'amount' => (float) ($row['incurred_amount'] ?? 0),
            ];
        }
        $genderTotal = $gender['_total'] ?? ['count' => 0, 'incurred_amount' => 0];

        $relationRows = $this->dimensionRows($data['relation'] ?? []);
        $memberRows   = $this->dimensionRows($data['member_type'] ?? []);

        $ageRows = [];
        $ageBands = ['0-10', '11-20', '21-30', '31-40', '41-50', '51-60', '61-70', '>70'];
        $ageData  = $data['age_band'] ?? [];
        foreach ($ageBands as $band) {
            $row = $ageData[$band] ?? ['count' => 0, 'incurred_amount' => 0, 'count_pct' => '0.00', 'amount_pct' => '0.00'];
            $ageRows[] = [
                'band'  => $band,
                'count' => (int) ($row['count'] ?? 0),
                'cpct'  => (float) ($row['count_pct'] ?? 0),
                'amt'   => (float) ($row['incurred_amount'] ?? 0),
                'apct'  => (float) ($row['amount_pct'] ?? 0),
            ];
        }
        $ageTotal = $ageData['_total'] ?? ['count' => 0, 'incurred_amount' => 0];

        $diagnosisRows = [];
        $diagData = $data['diagnosis'] ?? [];
        $diagTotalAmount = (float) ($diagData['_total']['incurred_amount'] ?? 0);
        foreach ($diagData as $label => $row) {
            if ($label === '_total') {
                continue;
            }
            $count = (int) ($row['count'] ?? 0);
            $amt   = (float) ($row['incurred_amount'] ?? 0);
            $diagnosisRows[] = [
                'name'  => $label,
                'count' => $count,
                'cpct'  => (float) ($row['count_pct'] ?? 0),
                'amt'   => $amt,
                'apct'  => (float) ($row['amount_pct'] ?? 0),
                'acs'   => $count > 0 ? round($amt / $count, 0) : null,
                'lr'    => $diagTotalAmount > 0 ? round(($amt / $diagTotalAmount) * 100, 2) : 0,
            ];
        }
        $diagAcsTotal = ($diagTotalAmount > 0 && ($diagData['_total']['count'] ?? 0) > 0)
            ? round($diagTotalAmount / (int) $diagData['_total']['count'], 0)
            : 0;

        $hospitalRows = [];
        foreach (($data['hospital'] ?? []) as $label => $row) {
            if ($label === '_total') {
                continue;
            }
            $hospitalRows[] = [
                'name'  => $label,
                'count' => (int) ($row['count'] ?? 0),
                'cpct'  => (float) ($row['count_pct'] ?? 0),
                'amt'   => (float) ($row['incurred_amount'] ?? 0),
                'apct'  => (float) ($row['amount_pct'] ?? 0),
            ];
        }
        $hospitalTotal = ($data['hospital']['_total'] ?? ['count' => 0, 'incurred_amount' => 0]);

        $cityRows = [];
        $cityTotals = [
            'count' => 0, 'amt' => 0.0, 'paidCount' => 0, 'paidAmt' => 0.0,
            'outCount' => 0.0, 'outAmt' => 0.0, 'acs' => 0,
        ];
        foreach (($data['city'] ?? []) as $city => $row) {
            $paidCount = (int) ($row['paid_count'] ?? 0);
            $paidAmt   = (float) ($row['paid_amount'] ?? 0);
            $acs       = $paidCount > 0 ? round($paidAmt / $paidCount, 0) : '';
            $cityRows[] = [
                'city'      => $city,
                'count'     => (int) ($row['claim_count'] ?? 0),
                'amt'       => (float) ($row['claim_amount'] ?? 0),
                'paidCount' => $paidCount ?: '',
                'paidAmt'   => $paidAmt ?: '',
                'outCount'  => ($row['outstanding_count'] ?? 0) ?: '',
                'outAmt'    => ($row['outstanding_amount'] ?? 0) ?: '',
                'acs'       => $acs,
            ];
            $cityTotals['count']     += (int) ($row['claim_count'] ?? 0);
            $cityTotals['amt']       += (float) ($row['claim_amount'] ?? 0);
            $cityTotals['paidCount'] += $paidCount;
            $cityTotals['paidAmt']   += $paidAmt;
            $cityTotals['outCount']  += (float) ($row['outstanding_count'] ?? 0);
            $cityTotals['outAmt']    += (float) ($row['outstanding_amount'] ?? 0);
        }
        $cityTotals['acs'] = $cityTotals['paidCount'] > 0
            ? round($cityTotals['paidAmt'] / $cityTotals['paidCount'], 0)
            : 0;

        $monthly     = $data['monthly'] ?? [];
        $monthKeys   = array_keys($monthly);
        usort($monthKeys, static fn ($a, $b) => self::monthKeyToSort($b) <=> self::monthKeyToSort($a));
        $paidCountByMonth = [];
        $outCountByMonth  = [];
        $paidAmtByMonth   = [];
        $outAmtByMonth    = [];
        foreach ($monthKeys as $mk) {
            $paidCountByMonth[] = (int) ($monthly[$mk]['paid_count'] ?? 0);
            $outCountByMonth[]  = (int) ($monthly[$mk]['outstanding_count'] ?? 0);
            $paidAmtByMonth[]   = (float) ($monthly[$mk]['paid_amount'] ?? 0);
            $outAmtByMonth[]    = (float) ($monthly[$mk]['outstanding_amount'] ?? 0);
        }

        $paidCharts = $this->buildPaidChartData($dumpRows);

        return [
            'claimStatus'      => $claimStatus,
            'policyPremium'    => $policyPremium,
            'policyPremiumTotal' => $premiumPaid,
            'reimbTAT'         => $reimbTAT['rows'],
            'reimbTATTotal'    => $reimbTAT['total'],
            'preAuthTAT'       => ['within' => 100, 'above' => 0],
            'premiumBars'      => [
                (float) ($header['premium_paid'] ?? 0),
                (float) ($header['earned_premium'] ?? 0),
                (float) ($summary['claim_paid_outstanding'] ?? 0),
            ],
            'lives'            => (int) ($header['lives'] ?? 0),
            'icr'              => (float) ($summary['icr_pct'] ?? 0),
            'gender'           => $genderRows,
            'genderTotal'      => $genderTotal,
            'claimType'        => $claimTypeRows['rows'],
            'claimTypeTotal'   => $claimTypeTotal,
            'relation'         => $relationRows['rows'],
            'relationTotal'    => $relationRows['total'],
            'memberType'       => $memberRows['rows'],
            'memberTypeTotal'  => $memberRows['total'],
            'age'              => $ageRows,
            'ageTotal'         => $ageTotal,
            'diagnosis'        => $diagnosisRows,
            'diagnosisTotal'   => [
                'count'  => (int) ($diagData['_total']['count'] ?? 0),
                'amt'    => $diagTotalAmount,
                'acs'    => $diagAcsTotal,
                'lr'     => (float) ($summary['icr_pct'] ?? 0),
            ],
            'utilizationEmployees'  => $this->utilizationRows($data['utilization']['employees'] ?? []),
            'utilizationDependents' => $this->utilizationRows($data['utilization']['dependents'] ?? []),
            'hospitals'        => $hospitalRows,
            'hospitalTotal'    => $hospitalTotal,
            'paidCharts'       => $paidCharts,
            'cities'           => $cityRows,
            'cityTotals'       => $cityTotals,
            'months'           => $monthKeys,
            'paidCountByMonth' => $paidCountByMonth,
            'outCountByMonth'  => $outCountByMonth,
            'paidAmtByMonth'   => $paidAmtByMonth,
            'outAmtByMonth'    => $outAmtByMonth,
        ];
    }

    /**
     * @param list<array<string,mixed>> $dumpRows
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function aggregateReimbursementTat(array $dumpRows): array
    {
        $buckets = [
            'A) 0-7 Days'  => ['count' => 0, 'pct' => 0],
            'B) 7-15 Days' => ['count' => 0, 'pct' => 0],
        ];
        $total = 0;

        foreach ($dumpRows as $row) {
            if ($this->normalizeClaimType((string) ($row['claim_type'] ?? '')) !== 'Reimbursement') {
                continue;
            }
            if ($this->normalizeStatus((string) ($row['claim_status'] ?? '')) !== 'Settled') {
                continue;
            }

            $from = strtotime((string) ($row['intimation_date'] ?? ''));
            $to   = strtotime((string) ($row['settled_date'] ?? ''));
            if ($from === false || $to === false || $to < $from) {
                continue;
            }

            $days = (int) floor(($to - $from) / 86400);
            $key  = $days <= 7 ? 'A) 0-7 Days' : 'B) 7-15 Days';
            $buckets[$key]['count']++;
            $total++;
        }

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

        $rows = [];
        foreach ($buckets as $range => $data) {
            $rows[] = ['range' => $range, 'count' => $data['count'], 'pct' => $data['pct']];
        }

        return [
            'rows'  => $rows,
            'total' => ['count' => $total, 'pct' => $total > 0 ? 100.0 : 0],
        ];
    }

    /**
     * @param list<array<string,mixed>> $dumpRows
     * @return array{rows:list<array<string,mixed>>,_total:array<string,mixed>}
     */
    private function buildClaimTypeRows(array $dumpRows): array
    {
        $types = ['Cashless', 'Reimbursement'];
        $statusMap = ['Settled' => 'Paid', 'Outstanding' => 'In Process', 'Rejected' => 'Denied'];
        $rows = [];
        $grandCount = 0;
        $grandAmount = 0.0;
        $grandIncurred = 0.0;

        foreach ($types as $type) {
            $typeCount = 0;
            $typeAmount = 0.0;
            $typeIncurred = 0.0;
            $subRows = [];

            foreach ($statusMap as $internal => $display) {
                $count = 0;
                $amount = 0.0;
                $incurred = 0.0;

                foreach ($dumpRows as $row) {
                    if ($this->normalizeClaimType((string) ($row['claim_type'] ?? '')) !== $type) {
                        continue;
                    }
                    if ($this->normalizeStatus((string) ($row['claim_status'] ?? '')) !== $internal) {
                        continue;
                    }
                    $claimed = $this->toFloat($row['claimed_amount'] ?? 0);
                    $count++;
                    $amount += $claimed;
                    $incurred += $this->claimTypeIncurred($row, $internal);
                }

                $subRows[] = compact('display', 'count', 'amount', 'incurred');
                $typeCount += $count;
                $typeAmount += $amount;
                $typeIncurred += $incurred;
            }

            $rows[] = [
                'type'     => $type,
                'header'   => true,
                'count'    => $typeCount,
                'countPct' => 0,
                'amount'   => $typeAmount,
                'amtPct'   => 0,
                'incurred' => $typeIncurred,
                'incPct'   => 0,
            ];

            foreach ($subRows as $sub) {
                $rows[] = [
                    'type'     => $sub['display'],
                    'header'   => false,
                    'count'    => $sub['count'],
                    'countPct' => 0,
                    'amount'   => $sub['amount'],
                    'amtPct'   => 0,
                    'incurred' => $sub['incurred'],
                    'incPct'   => 0,
                ];
            }

            $grandCount += $typeCount;
            $grandAmount += $typeAmount;
            $grandIncurred += $typeIncurred;
        }

        foreach ($rows as &$row) {
            $row['countPct'] = $grandCount > 0 ? round(($row['count'] / $grandCount) * 100, 2) : 0;
            $row['amtPct']   = $grandAmount > 0 ? round(($row['amount'] / $grandAmount) * 100, 2) : 0;
            $row['incPct']   = $grandIncurred > 0 ? round(($row['incurred'] / $grandIncurred) * 100, 2) : 0;
        }
        unset($row);

        return [
            'rows'   => $rows,
            '_total' => ['count' => $grandCount, 'amount' => $grandAmount, 'incurred' => $grandIncurred],
        ];
    }

    /**
     * @param list<array<string,mixed>> $dumpRows
     */
    private function buildPaidChartData(array $dumpRows): array
    {
        $types = ['Cashless', 'Reimbursement'];
        $paidCount = [];
        $paidAmt   = [];
        $acs       = [];
        $byStatus  = [
            'Settled'     => ['Cashless' => 0, 'Reimbursement' => 0],
            'Outstanding' => ['Cashless' => 0, 'Reimbursement' => 0],
            'Rejected'    => ['Cashless' => 0, 'Reimbursement' => 0],
        ];

        foreach ($types as $type) {
            $settled = 0;
            $paid    = 0.0;
            foreach ($dumpRows as $row) {
                if ($this->normalizeClaimType((string) ($row['claim_type'] ?? '')) !== $type) {
                    continue;
                }
                $status = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
                $byStatus[$status][$type]++;
                if ($status === 'Settled') {
                    $settled++;
                    $approved = $this->toFloat($row['abhi_amount_less_coins_current_month'] ?? 0);
                    $claimed  = $this->toFloat($row['claimed_amount'] ?? 0);
                    $paid += $approved > 0 ? $approved : $claimed;
                }
            }
            $paidCount[] = $settled;
            $paidAmt[]   = $paid;
            $acs[]       = $settled > 0 ? round($paid / $settled, 0) : 0;
        }

        $statusSummary = $this->aggregateByStatus($dumpRows);
        $buckets = $statusSummary['buckets'] ?? [];

        return [
            'paidCount'    => $paidCount,
            'paidAmt'      => $paidAmt,
            'acs'          => $acs,
            'countByType'  => $byStatus,
            'amtByStatus'  => [
                (float) ($buckets['Settled']['reported_amount'] ?? 0),
                (float) ($buckets['Outstanding']['reported_amount'] ?? 0),
                (float) ($buckets['Rejected']['reported_amount'] ?? 0),
            ],
        ];
    }

    /**
     * @param array<string,mixed> $dim
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function dimensionRows(array $dim): array
    {
        $rows = [];
        foreach ($dim as $label => $row) {
            if ($label === '_total') {
                continue;
            }
            $rows[] = [
                'label' => $label,
                'count' => (int) ($row['count'] ?? 0),
                'cpct'  => (float) ($row['count_pct'] ?? 0),
                'amt'   => (float) ($row['incurred_amount'] ?? 0),
                'apct'  => (float) ($row['amount_pct'] ?? 0),
            ];
        }

        return [
            'rows'  => $rows,
            'total' => $dim['_total'] ?? ['count' => 0, 'incurred_amount' => 0],
        ];
    }

    /**
     * @param array<string,mixed> $util
     * @return array{rows:list<array<string,mixed>>,total:array<string,mixed>}
     */
    private function utilizationRows(array $util): array
    {
        $order = ['1', '2', '3', '4', 'Above 5'];
        $rows  = [];
        foreach ($order as $key) {
            $row = $util[$key] ?? ['beneficiaries' => 0, 'incurred_count' => 0, 'incurred_amount' => 0, 'count_pct' => '0.00', 'amount_pct' => '0.00'];
            $rows[] = [
                'claims'        => $key,
                'beneficiaries' => (int) ($row['beneficiaries'] ?? 0),
                'incurredCount' => (int) ($row['incurred_count'] ?? 0),
                'countPct'      => (float) ($row['count_pct'] ?? 0),
                'amount'        => (float) ($row['incurred_amount'] ?? 0),
                'amountPct'     => (float) ($row['amount_pct'] ?? 0),
            ];
        }

        $totalBenef = array_sum(array_column($rows, 'beneficiaries'));
        $totalCount = array_sum(array_column($rows, 'incurredCount'));
        $totalAmt   = array_sum(array_column($rows, 'amount'));

        return [
            'rows'  => $rows,
            'total' => [
                'beneficiaries' => $totalBenef,
                'incurredCount' => $totalCount,
                'amount'        => $totalAmt,
            ],
        ];
    }

    /**
     * @param array<string,mixed> $row
     */
    private function claimTypeIncurred(array $row, string $status): float
    {
        $claimed  = $this->toFloat($row['claimed_amount'] ?? 0);
        $approved = $this->toFloat($row['abhi_amount_less_coins_current_month'] ?? 0);

        if ($status === 'Settled') {
            return $approved > 0 ? $approved : $claimed;
        }

        return $claimed;
    }

    /**
     * @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<string,mixed>
     */
    public function aggregateByStatus(array $rows): array
    {
        $buckets = [
            'Reported'     => ['count' => 0, 'reported_amount' => 0.0, 'incurred_amount' => 0.0, 'paid_amount' => 0.0],
            'Settled'      => ['count' => 0, 'reported_amount' => 0.0, 'incurred_amount' => 0.0, 'paid_amount' => 0.0],
            'Outstanding'  => ['count' => 0, 'reported_amount' => 0.0, 'incurred_amount' => 0.0, 'paid_amount' => 0.0],
            'Rejected'     => ['count' => 0, 'reported_amount' => 0.0, 'incurred_amount' => 0.0, 'paid_amount' => 0.0],
        ];

        foreach ($rows as $row) {
            $claimed  = $this->toFloat($row['claimed_amount'] ?? 0);
            $approved = $this->toFloat($row['abhi_amount_less_coins_current_month'] ?? 0);
            $bucket   = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));

            $buckets['Reported']['count']++;
            $buckets['Reported']['reported_amount'] += $claimed;
            $buckets['Reported']['incurred_amount'] += $this->incurredForRow($row);

            if (!isset($buckets[$bucket])) {
                continue;
            }

            $buckets[$bucket]['count']++;
            $buckets[$bucket]['reported_amount'] += $claimed;
            if ($bucket === 'Settled') {
                $paid = $approved > 0 ? $approved : $claimed;
                $buckets[$bucket]['incurred_amount'] += $paid;
                $buckets[$bucket]['paid_amount'] += $paid;
            } elseif ($bucket === 'Rejected') {
                $buckets[$bucket]['incurred_amount'] += $claimed;
            } else {
                $buckets[$bucket]['incurred_amount'] += $this->incurredForRow($row);
            }
        }

        $incurredTotal = $buckets['Reported']['incurred_amount'];

        return [
            'buckets'         => $buckets,
            'incurred_amount' => $incurredTotal,
            'reported_count'  => count($rows),
        ];
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByClaimType(array $rows): array
    {
        $types = ['Cashless' => [], 'Reimbursement' => []];
        $totals = ['count' => 0, 'claim_amount' => 0.0, 'incurred_amount' => 0.0];

        foreach ($rows as $row) {
            $typeKey = $this->normalizeClaimType((string) ($row['claim_type'] ?? ''));
            $status  = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
            $claimed = $this->toFloat($row['claimed_amount'] ?? 0);
            $incurred = $this->incurredForRow($row);

            if (!isset($types[$typeKey][$status])) {
                $types[$typeKey][$status] = ['count' => 0, 'claim_amount' => 0.0, 'incurred_amount' => 0.0];
            }

            $types[$typeKey][$status]['count']++;
            $types[$typeKey][$status]['claim_amount'] += $claimed;
            $types[$typeKey][$status]['incurred_amount'] += $incurred;

            if (!isset($types[$typeKey]['_subtotal'])) {
                $types[$typeKey]['_subtotal'] = ['count' => 0, 'claim_amount' => 0.0, 'incurred_amount' => 0.0];
            }
            $types[$typeKey]['_subtotal']['count']++;
            $types[$typeKey]['_subtotal']['claim_amount'] += $claimed;
            $types[$typeKey]['_subtotal']['incurred_amount'] += $incurred;

            $totals['count']++;
            $totals['claim_amount'] += $claimed;
            $totals['incurred_amount'] += $incurred;
        }

        return ['types' => $types, 'total' => $totals];
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByGender(array $rows): array
    {
        return $this->aggregateDimension($rows, 'gender', static function ($row) {
            $g = strtolower(trim((string) ($row['gender'] ?? '')));
            if ($g === 'f' || $g === 'female') {
                return 'Female';
            }
            if ($g === 'm' || $g === 'male') {
                return 'Male';
            }
            return $g !== '' ? ucfirst($g) : 'Unknown';
        });
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByRelation(array $rows): array
    {
        return $this->aggregateDimension($rows, 'relation', static function ($row) {
            $rel = trim((string) ($row['relation'] ?? ''));
            if ($rel === '') {
                return 'Unknown';
            }
            $lower = strtolower($rel);
            if ($lower === 'self' || $lower === 'employee') {
                return 'Self';
            }
            if (str_contains($lower, 'spouse') || $lower === 'wife' || $lower === 'husband') {
                return 'Spouse';
            }
            if (str_contains($lower, 'parent') || str_contains($lower, 'father') || str_contains($lower, 'mother')) {
                return 'Parent';
            }
            if (str_contains($lower, 'child') || str_contains($lower, 'son') || str_contains($lower, 'daughter')) {
                return 'Child';
            }
            return ucfirst($rel);
        });
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByMemberType(array $rows): array
    {
        return $this->aggregateDimension($rows, 'member_type', static function ($row) {
            $rel = strtolower(trim((string) ($row['relation'] ?? '')));
            return ($rel === 'self' || $rel === 'employee') ? 'Self' : 'Dependent';
        });
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByAgeBand(array $rows): array
    {
        $bands = [
            '0-10'   => ['count' => 0, 'incurred_amount' => 0.0],
            '11-20'  => ['count' => 0, 'incurred_amount' => 0.0],
            '21-30'  => ['count' => 0, 'incurred_amount' => 0.0],
            '31-40'  => ['count' => 0, 'incurred_amount' => 0.0],
            '41-50'  => ['count' => 0, 'incurred_amount' => 0.0],
            '51-60'  => ['count' => 0, 'incurred_amount' => 0.0],
            '61-70'  => ['count' => 0, 'incurred_amount' => 0.0],
            '>70'    => ['count' => 0, 'incurred_amount' => 0.0],
        ];

        foreach ($rows as $row) {
            $status = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
            if (!in_array($status, ['Settled', 'Outstanding'], true)) {
                continue;
            }

            $age = (int) preg_replace('/\D/', '', (string) ($row['patient_age'] ?? '0'));
            $band = $this->ageToBand($age);
            $bands[$band]['count']++;
            $bands[$band]['incurred_amount'] += $this->incurredForRow($row);
        }

        return $this->withPercentages($bands);
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByDiagnosis(array $rows, int $limit = 10): array
    {
        $groups = [];

        foreach ($rows as $row) {
            $status = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
            if (!in_array($status, ['Settled', 'Outstanding'], true)) {
                continue;
            }

            $label = trim((string) ($row['diagnosis'] ?? ''));
            if ($label === '') {
                $label = 'OTHERS';
            }

            if (!isset($groups[$label])) {
                $groups[$label] = ['count' => 0, 'incurred_amount' => 0.0];
            }
            $groups[$label]['count']++;
            $groups[$label]['incurred_amount'] += $this->incurredForRow($row);
        }

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

        return $this->withPercentages(array_slice($groups, 0, $limit, true));
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByHospital(array $rows): array
    {
        $groups = [];

        foreach ($rows as $row) {
            $status = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
            if (!in_array($status, ['Settled', 'Outstanding'], true)) {
                continue;
            }

            $name = trim((string) ($row['hospital_name'] ?? ''));
            if ($name === '') {
                $name = trim((string) ($row['abhi_network_non_network'] ?? 'ABH non-network'));
                if ($name === '') {
                    $name = 'ABH non-network';
                }
            }

            if (!isset($groups[$name])) {
                $groups[$name] = ['count' => 0, 'incurred_amount' => 0.0];
            }
            $groups[$name]['count']++;
            $groups[$name]['incurred_amount'] += $this->incurredForRow($row);
        }

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

        return $this->withPercentages($groups);
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByCity(array $rows): array
    {
        $groups = [];

        foreach ($rows as $row) {
            $city = trim((string) ($row['hospital_city'] ?? ''));
            if ($city === '') {
                $city = 'Unknown';
            }

            $claimed  = $this->toFloat($row['claimed_amount'] ?? 0);
            $approved = $this->toFloat($row['abhi_amount_less_coins_current_month'] ?? 0);
            $status   = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));

            if (!isset($groups[$city])) {
                $groups[$city] = [
                    'claim_count'            => 0,
                    'claim_amount'           => 0.0,
                    'paid_count'             => 0,
                    'paid_amount'            => 0.0,
                    'outstanding_count'      => 0,
                    'outstanding_amount'     => 0.0,
                ];
            }

            $groups[$city]['claim_count']++;
            $groups[$city]['claim_amount'] += $claimed;

            if ($status === 'Settled') {
                $groups[$city]['paid_count']++;
                $groups[$city]['paid_amount'] += $approved > 0 ? $approved : $claimed;
            } elseif ($status === 'Outstanding') {
                $groups[$city]['outstanding_count']++;
                $groups[$city]['outstanding_amount'] += $claimed;
            }
        }

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

        return $groups;
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateByMonth(array $rows): array
    {
        $groups = [];

        foreach ($rows as $row) {
            $monthKey = $this->resolveMonthKey($row);
            if ($monthKey === null) {
                continue;
            }

            $status   = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
            $claimed  = $this->toFloat($row['claimed_amount'] ?? 0);
            $approved = $this->toFloat($row['abhi_amount_less_coins_current_month'] ?? 0);

            if (!isset($groups[$monthKey])) {
                $groups[$monthKey] = [
                    'paid_count'         => 0,
                    'outstanding_count'  => 0,
                    'paid_amount'        => 0.0,
                    'outstanding_amount' => 0.0,
                ];
            }

            if ($status === 'Settled') {
                $groups[$monthKey]['paid_count']++;
                $groups[$monthKey]['paid_amount'] += $approved > 0 ? $approved : $claimed;
            } elseif ($status === 'Outstanding') {
                $groups[$monthKey]['outstanding_count']++;
                $groups[$monthKey]['outstanding_amount'] += $claimed;
            }
        }

        uksort($groups, static function ($a, $b) {
            return self::monthKeyToSort($b) <=> self::monthKeyToSort($a);
        });

        return $groups;
    }

    /**
     * @param list<array<string,mixed>> $rows
     */
    public function aggregateUtilization(array $rows, string $memberType): array
    {
        $filtered = array_filter($rows, function ($row) use ($memberType) {
            $rel = strtolower(trim((string) ($row['relation'] ?? '')));
            $isSelf = ($rel === 'self' || $rel === 'employee');
            return $memberType === 'Self' ? $isSelf : !$isSelf;
        });

        $beneficiaryClaims = [];

        foreach ($filtered as $row) {
            $status = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
            if (!in_array($status, ['Settled', 'Outstanding'], true)) {
                continue;
            }

            $memberCode = trim((string) ($row['member_code'] ?? $row['corporate_employee_code'] ?? ''));
            if ($memberCode === '') {
                $memberCode = 'unknown_' . ($row['id'] ?? uniqid());
            }

            if (!isset($beneficiaryClaims[$memberCode])) {
                $beneficiaryClaims[$memberCode] = ['count' => 0, 'incurred_amount' => 0.0];
            }
            $beneficiaryClaims[$memberCode]['count']++;
            $beneficiaryClaims[$memberCode]['incurred_amount'] += $this->incurredForRow($row);
        }

        $claimCountDistribution = [];
        foreach ($beneficiaryClaims as $data) {
            $n = min($data['count'], 5);
            $bucket = $n >= 5 ? 'Above 5' : (string) $n;
            if (!isset($claimCountDistribution[$bucket])) {
                $claimCountDistribution[$bucket] = [
                    'beneficiaries'   => 0,
                    'incurred_count'  => 0,
                    'incurred_amount' => 0.0,
                ];
            }
            $claimCountDistribution[$bucket]['beneficiaries']++;
            $claimCountDistribution[$bucket]['incurred_count'] += $data['count'];
            $claimCountDistribution[$bucket]['incurred_amount'] += $data['incurred_amount'];
        }

        $order = ['1', '2', '3', '4', 'Above 5'];
        $ordered = [];
        foreach ($order as $key) {
            if (isset($claimCountDistribution[$key])) {
                $ordered[$key] = $claimCountDistribution[$key];
            }
        }

        return $this->withPercentages($ordered, 'incurred_amount');
    }

    public function normalizeStatus(string $status): string
    {
        $s = strtolower(trim($status));

        if (in_array($s, ['settled', 'paid', 'approved', 'closed'], true)) {
            return 'Settled';
        }
        if (in_array($s, ['rejected', 'denied', 'repudiated', 'cancelled', 'canceled'], true)) {
            return 'Rejected';
        }
        if (
            str_contains($s, 'progress')
            || str_contains($s, 'query')
            || str_contains($s, 'await')
            || str_contains($s, 'open')
            || str_contains($s, 'process')
            || str_contains($s, 'pending')
        ) {
            return 'Outstanding';
        }

        if ($s !== '' && !in_array($s, ['settled', 'paid'], true)) {
            return 'Outstanding';
        }

        return 'Outstanding';
    }

    public static function formatMoney(float $amount, int $decimals = 0): string
    {
        return number_format($amount, $decimals);
    }

    public static function formatPct(float $part, float $whole, int $decimals = 2): string
    {
        if ($whole <= 0) {
            return number_format(0, $decimals);
        }

        return number_format(($part / $whole) * 100, $decimals);
    }

    /**
     * @param array<string,mixed> $row
     */
    private function incurredForRow(array $row): float
    {
        $status   = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
        $claimed  = $this->toFloat($row['claimed_amount'] ?? 0);
        $approved = $this->toFloat($row['abhi_amount_less_coins_current_month'] ?? 0);

        if ($status === 'Settled') {
            return $approved > 0 ? $approved : $claimed;
        }
        if ($status === 'Outstanding') {
            return $claimed;
        }

        return 0.0;
    }

    /**
     * @param list<array<string,mixed>> $rows
     * @param callable(array):string $labelFn
     */
    private function aggregateDimension(array $rows, string $dimension, callable $labelFn): array
    {
        $groups = [];

        foreach ($rows as $row) {
            $status = $this->normalizeStatus((string) ($row['claim_status'] ?? ''));
            if (!in_array($status, ['Settled', 'Outstanding'], true)) {
                continue;
            }

            $label = $labelFn($row);
            if (!isset($groups[$label])) {
                $groups[$label] = ['count' => 0, 'incurred_amount' => 0.0];
            }
            $groups[$label]['count']++;
            $groups[$label]['incurred_amount'] += $this->incurredForRow($row);
        }

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

        return $this->withPercentages($groups);
    }

    /**
     * @param array<string,array{count:int,incurred_amount:float}> $groups
     */
    private function withPercentages(array $groups, string $amountKey = 'incurred_amount'): array
    {
        $totalCount  = array_sum(array_column($groups, 'count'));
        $totalAmount = array_sum(array_map(static fn ($g) => (float) ($g[$amountKey] ?? 0), $groups));

        $out = [];
        foreach ($groups as $label => $data) {
            $count  = (int) ($data['count'] ?? $data['beneficiaries'] ?? 0);
            $amount = (float) ($data[$amountKey] ?? $data['incurred_amount'] ?? 0);

            $out[$label] = array_merge($data, [
                'count_pct'  => self::formatPct($count, (float) $totalCount),
                'amount_pct' => self::formatPct($amount, $totalAmount),
            ]);
        }

        $out['_total'] = [
            'count'           => $totalCount,
            'incurred_amount' => $totalAmount,
        ];

        return $out;
    }

    private function normalizeClaimType(string $type): string
    {
        $t = strtolower(trim($type));
        if (str_contains($t, 'cashless') || str_contains($t, 'network')) {
            return 'Cashless';
        }

        return 'Reimbursement';
    }

    private function ageToBand(int $age): string
    {
        if ($age <= 10) {
            return '0-10';
        }
        if ($age <= 20) {
            return '11-20';
        }
        if ($age <= 30) {
            return '21-30';
        }
        if ($age <= 40) {
            return '31-40';
        }
        if ($age <= 50) {
            return '41-50';
        }
        if ($age <= 60) {
            return '51-60';
        }
        if ($age <= 70) {
            return '61-70';
        }

        return '>70';
    }

    /**
     * @param array<string,mixed> $row
     */
    private function resolveMonthKey(array $row): ?string
    {
        $raw = trim((string) ($row['intimation_final_month'] ?? ''));
        if ($raw !== '') {
            $ts = strtotime($raw);
            if ($ts !== false) {
                return date('M-y', $ts);
            }
        }

        $dateRaw = trim((string) ($row['intimation_date'] ?? ''));
        if ($dateRaw !== '') {
            $ts = strtotime($dateRaw);
            if ($ts !== false) {
                return date('M-y', $ts);
            }
        }

        return null;
    }

    private static function monthKeyToSort(string $key): string
    {
        $ts = strtotime('01-' . str_replace('-', '-20', $key));
        return $ts !== false ? date('Y-m', $ts) : '0000-00';
    }

    private function toFloat(mixed $value): float
    {
        if ($value === null || $value === '') {
            return 0.0;
        }

        $clean = preg_replace('/[^\d.\-]/', '', (string) $value);

        return (float) ($clean !== '' ? $clean : 0);
    }

    private function parseFormattedNumber(mixed $value): float
    {
        if ($value === null || $value === '') {
            return 0.0;
        }

        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 === '') {
            return '';
        }

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

        return $ts !== false ? date('Y-m-d', $ts) : (string) $value;
    }
}