<?php

namespace App\Services;

use App\Models\Branch;
use App\Models\Collateral;
use App\Models\Loan;
use App\Models\LoanEarlySettlement;
use App\Models\LoanInstallment;
use App\Models\LoanPayment;
use App\Models\LoanRestructuring;
use App\Models\LoanTopUp;
use App\Models\LoanWriteOff;
use App\Models\LoanWriteOffRecovery;
use Carbon\CarbonImmutable;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Collection;
use Illuminate\Validation\ValidationException;

class LoanBranchReportService
{
    public const REPORTS = [
        'active-loan-portfolio' => 'Active Loan Portfolio',
        'outstanding-principal' => 'Outstanding Principal',
        'outstanding-interest' => 'Outstanding Interest',
        'due-today' => 'Due Today',
        'overdue-loans' => 'Overdue Loans',
        'completed-loans' => 'Completed Loans',
        'written-off-loans' => 'Written-Off Loans',
        'loan-collection-rate' => 'Loan Collection Rate',
        'branch-performance' => 'Branch Performance',
        'company-performance' => 'Consolidated Company Performance',
        'loan-officer-performance' => 'Loan Officer Performance',
        'collateral-report' => 'Collateral Report',
        'top-up-report' => 'Top-Up Report',
        'restructuring-report' => 'Restructuring Report',
        'write-off-recovery-report' => 'Write-Off Recovery Report',
    ];

    public function __construct(private MoneyMath $money) {}

    public function generate(string $report, array $branchIds, string $from, string $to, array $filters = []): array
    {
        if (! array_key_exists($report, self::REPORTS)) {
            throw ValidationException::withMessages(['report' => 'Unknown loan or branch report.']);
        }
        if ($branchIds === []) {
            throw ValidationException::withMessages(['branch_ids' => 'At least one accessible branch is required.']);
        }

        $fromDate = CarbonImmutable::parse($from)->startOfDay();
        $toDate = CarbonImmutable::parse($to)->endOfDay();
        if ($toDate->lt($fromDate)) {
            throw ValidationException::withMessages(['to' => 'The report end date cannot be before the start date.']);
        }

        $scope = [
            'branch_ids' => array_values(array_unique(array_map('intval', $branchIds))),
            'from' => $fromDate,
            'to' => $toDate,
            'loan_officer_id' => isset($filters['loan_officer_id']) && $filters['loan_officer_id'] !== '' ? (int) $filters['loan_officer_id'] : null,
            'loan_product_id' => isset($filters['loan_product_id']) && $filters['loan_product_id'] !== '' ? (int) $filters['loan_product_id'] : null,
            'search' => trim((string) ($filters['search'] ?? '')),
            'arrears_bucket' => trim((string) ($filters['arrears_bucket'] ?? '')),
            'record_status' => trim((string) ($filters['record_status'] ?? '')),
        ];

        return match ($report) {
            'active-loan-portfolio' => $this->activeLoanPortfolio($scope),
            'outstanding-principal' => $this->outstandingPrincipal($scope),
            'outstanding-interest' => $this->outstandingInterest($scope),
            'due-today' => $this->dueToday($scope),
            'overdue-loans' => $this->overdueLoans($scope),
            'completed-loans' => $this->completedLoans($scope),
            'written-off-loans' => $this->writtenOffLoans($scope),
            'loan-collection-rate' => $this->loanCollectionRate($scope),
            'branch-performance' => $this->branchPerformance($scope),
            'company-performance' => $this->companyPerformance($scope),
            'loan-officer-performance' => $this->loanOfficerPerformance($scope),
            'collateral-report' => $this->collateralReport($scope),
            'top-up-report' => $this->topUpReport($scope),
            'restructuring-report' => $this->restructuringReport($scope),
            'write-off-recovery-report' => $this->writeOffRecoveryReport($scope),
        };
    }

    private function activeLoanPortfolio(array $scope): array
    {
        $loans = $this->loanQuery($scope)->where('status', 'active')->get()->sortByDesc(fn (Loan $loan) => $this->minor($loan->total_outstanding))->values();
        $rows = $loans->map(fn (Loan $loan) => $this->portfolioLoanRow($loan))->all();

        return $this->payload(
            'active-loan-portfolio',
            [
                $this->summary('Active loans', (string) $loans->count(), 'number'),
                $this->summary('Original principal', $this->sumModelMoney($loans, 'principal'), 'money'),
                $this->summary('Outstanding principal', $this->sumModelMoney($loans, 'principal_outstanding'), 'money'),
                $this->summary('Total outstanding', $this->sumModelMoney($loans, 'total_outstanding'), 'money'),
                $this->summary('Current overdue', $this->sumModelMoney($loans, 'total_overdue'), 'money'),
            ],
            [
                'loan_number'=>'Loan','customer'=>'Customer','phone'=>'Phone','branch'=>'Branch','officer'=>'Loan officer','product'=>'Product',
                'principal'=>'Principal','total_paid'=>'Paid','principal_outstanding'=>'Principal O/S','interest_outstanding'=>'Interest O/S',
                'fees_outstanding'=>'Fees O/S','penalties_outstanding'=>'Penalties O/S','total_outstanding'=>'Total O/S','days_overdue'=>'Days overdue',
            ],
            $rows,
            ['principal','total_paid','principal_outstanding','interest_outstanding','fees_outstanding','penalties_outstanding','total_outstanding'],
            [],
            'Current portfolio snapshot. Date range does not rewrite current outstanding balances.'
        );
    }

    private function outstandingPrincipal(array $scope): array
    {
        $loans = $this->loanQuery($scope)->where('status','active')->where('principal_outstanding','>',0)->get()
            ->sortByDesc(fn (Loan $loan) => $this->minor($loan->principal_outstanding))->values();
        $rows = $loans->map(fn (Loan $loan) => [
            'loan_number'=>$loan->loan_number,'customer'=>$loan->customer?->full_name,'branch'=>$loan->branch?->name,
            'officer'=>$loan->creator?->name ?: 'Unassigned','product'=>$loan->product?->name,
            'principal'=>$this->moneyString($loan->principal),'principal_outstanding'=>$this->moneyString($loan->principal_outstanding),
            'total_outstanding'=>$this->moneyString($loan->total_outstanding),'days_overdue'=>(string) ((int)$loan->days_overdue),
        ])->all();

        return $this->payload('outstanding-principal',[
            $this->summary('Loans with principal outstanding',(string)$loans->count(),'number'),
            $this->summary('Outstanding principal',$this->sumModelMoney($loans,'principal_outstanding'),'money'),
            $this->summary('Total outstanding',$this->sumModelMoney($loans,'total_outstanding'),'money'),
        ],[
            'loan_number'=>'Loan','customer'=>'Customer','branch'=>'Branch','officer'=>'Loan officer','product'=>'Product','principal'=>'Original principal',
            'principal_outstanding'=>'Principal O/S','total_outstanding'=>'Total O/S','days_overdue'=>'Days overdue',
        ],$rows,['principal','principal_outstanding','total_outstanding'],[],'Current active-loan principal snapshot.');
    }

    private function outstandingInterest(array $scope): array
    {
        $loans = $this->loanQuery($scope)->where('status','active')->where('interest_outstanding','>',0)->get()
            ->sortByDesc(fn (Loan $loan) => $this->minor($loan->interest_outstanding))->values();
        $rows = $loans->map(fn (Loan $loan) => [
            'loan_number'=>$loan->loan_number,'customer'=>$loan->customer?->full_name,'branch'=>$loan->branch?->name,
            'officer'=>$loan->creator?->name ?: 'Unassigned','product'=>$loan->product?->name,
            'original_interest'=>$this->moneyString($loan->interest),'interest_outstanding'=>$this->moneyString($loan->interest_outstanding),
            'total_outstanding'=>$this->moneyString($loan->total_outstanding),'days_overdue'=>(string)((int)$loan->days_overdue),
        ])->all();

        return $this->payload('outstanding-interest',[
            $this->summary('Loans with interest outstanding',(string)$loans->count(),'number'),
            $this->summary('Original interest',$this->sumModelMoney($loans,'interest'),'money'),
            $this->summary('Outstanding interest',$this->sumModelMoney($loans,'interest_outstanding'),'money'),
        ],[
            'loan_number'=>'Loan','customer'=>'Customer','branch'=>'Branch','officer'=>'Loan officer','product'=>'Product',
            'original_interest'=>'Original interest','interest_outstanding'=>'Interest O/S','total_outstanding'=>'Total O/S','days_overdue'=>'Days overdue',
        ],$rows,['original_interest','interest_outstanding','total_outstanding'],[],'Current active-loan interest snapshot.');
    }

    private function dueToday(array $scope): array
    {
        $loanIds = $this->loanQuery($scope)->where('status','active')->pluck('id');
        $dueDate = $scope['to']->toDateString();
        $installments = LoanInstallment::query()
            ->with(['loan.customer','loan.branch','loan.creator'])
            ->whereIn('loan_id',$loanIds)
            ->where('is_active',true)
            ->whereDate('due_date',$dueDate)
            ->where('remaining','>',0)
            ->orderBy('loan_id')->orderBy('installment_number')->get();

        $rows = $installments->map(fn (LoanInstallment $installment) => [
            'due_date'=>$installment->due_date?->format('d M Y'),'loan_number'=>$installment->loan?->loan_number,
            'installment'=>(string)$installment->installment_number,'customer'=>$installment->loan?->customer?->full_name,
            'phone'=>$installment->loan?->customer?->phone,'branch'=>$installment->loan?->branch?->name,
            'officer'=>$installment->loan?->creator?->name ?: 'Unassigned','scheduled_due'=>$this->moneyString($installment->amount_due),
            'penalty_due'=>$this->moneyString($installment->penalty_due),'paid'=>$this->moneyString($installment->amount_paid),
            'remaining'=>$this->moneyString($installment->remaining),
        ])->all();

        return $this->payload('due-today',[
            $this->summary('Installments due',(string)$installments->count(),'number'),
            $this->summary('Scheduled due',$this->sumModelMoney($installments,'amount_due'),'money'),
            $this->summary('Remaining due',$this->sumModelMoney($installments,'remaining'),'money'),
        ],[
            'due_date'=>'Due date','loan_number'=>'Loan','installment'=>'Inst.','customer'=>'Customer','phone'=>'Phone','branch'=>'Branch','officer'=>'Loan officer',
            'scheduled_due'=>'Scheduled','penalty_due'=>'Penalty','paid'=>'Paid','remaining'=>'Remaining',
        ],$rows,['scheduled_due','penalty_due','paid','remaining'],[],'Uses the selected report end date as the due date.');
    }

    private function overdueLoans(array $scope): array
    {
        $query = $this->loanQuery($scope)->where('status','active')->where(function (Builder $query): void {
            $query->where('days_overdue','>',0)->orWhere('total_overdue','>',0);
        });
        if ($scope['arrears_bucket'] !== '') $query->where('arrears_bucket',$scope['arrears_bucket']);
        $loans = $query->get()->sortByDesc(fn (Loan $loan) => (int)$loan->days_overdue)->values();
        $rows = $loans->map(fn (Loan $loan) => [
            'loan_number'=>$loan->loan_number,'customer'=>$loan->customer?->full_name,'phone'=>$loan->customer?->phone,'branch'=>$loan->branch?->name,
            'officer'=>$loan->creator?->name ?: 'Unassigned','bucket'=>$loan->arrears_bucket ?: '—','days_overdue'=>(string)((int)$loan->days_overdue),
            'overdue_principal'=>$this->moneyString($loan->overdue_principal),'overdue_interest'=>$this->moneyString($loan->overdue_interest),
            'overdue_fees'=>$this->moneyString($loan->overdue_fees),'overdue_penalties'=>$this->moneyString($loan->overdue_penalties),
            'total_overdue'=>$this->moneyString($loan->total_overdue),'total_outstanding'=>$this->moneyString($loan->total_outstanding),
        ])->all();

        return $this->payload('overdue-loans',[
            $this->summary('Overdue loans',(string)$loans->count(),'number'),
            $this->summary('Overdue principal',$this->sumModelMoney($loans,'overdue_principal'),'money'),
            $this->summary('Overdue interest',$this->sumModelMoney($loans,'overdue_interest'),'money'),
            $this->summary('Total overdue',$this->sumModelMoney($loans,'total_overdue'),'money'),
        ],[
            'loan_number'=>'Loan','customer'=>'Customer','phone'=>'Phone','branch'=>'Branch','officer'=>'Loan officer','bucket'=>'Bucket','days_overdue'=>'Days overdue',
            'overdue_principal'=>'Principal','overdue_interest'=>'Interest','overdue_fees'=>'Fees','overdue_penalties'=>'Penalties','total_overdue'=>'Total overdue','total_outstanding'=>'Total O/S',
        ],$rows,['overdue_principal','overdue_interest','overdue_fees','overdue_penalties','total_overdue','total_outstanding'],[],'Current arrears snapshot. Optional arrears-bucket filter is applied to the stored overdue classification.');
    }

    private function completedLoans(array $scope): array
    {
        $loans = $this->loanQuery($scope)->whereIn('status',['completed','fully_paid'])
            ->whereNotNull('completed_at')->whereBetween('completed_at',[$scope['from'],$scope['to']])->get()
            ->sortByDesc('completed_at')->values();
        $rows = $loans->map(fn (Loan $loan) => [
            'completed_at'=>$this->dateTime($loan->completed_at),'loan_number'=>$loan->loan_number,'customer'=>$loan->customer?->full_name,
            'branch'=>$loan->branch?->name,'officer'=>$loan->creator?->name ?: 'Unassigned','product'=>$loan->product?->name,
            'principal'=>$this->moneyString($loan->principal),'total_repayment'=>$this->moneyString($loan->total_repayment),
            'total_paid'=>$this->moneyString($loan->total_paid),'status'=>$this->label($loan->status),
        ])->all();

        return $this->payload('completed-loans',[
            $this->summary('Completed loans',(string)$loans->count(),'number'),
            $this->summary('Original principal',$this->sumModelMoney($loans,'principal'),'money'),
            $this->summary('Total paid',$this->sumModelMoney($loans,'total_paid'),'money'),
        ],[
            'completed_at'=>'Completed','loan_number'=>'Loan','customer'=>'Customer','branch'=>'Branch','officer'=>'Loan officer','product'=>'Product',
            'principal'=>'Principal','total_repayment'=>'Contract total','total_paid'=>'Paid','status'=>'Status',
        ],$rows,['principal','total_repayment','total_paid'],[],'Date range is applied to the loan completion date.');
    }

    private function writtenOffLoans(array $scope): array
    {
        $loans = $this->loanQuery($scope)->where('status','written_off')->whereNotNull('written_off_at')
            ->whereBetween('written_off_at',[$scope['from'],$scope['to']])->get()->sortByDesc('written_off_at')->values();
        $rows = $loans->map(function (Loan $loan): array {
            $writeOff = $loan->writeOff;
            return [
                'written_off_at'=>$this->dateTime($loan->written_off_at),'write_off_number'=>$writeOff?->write_off_number ?: '—','loan_number'=>$loan->loan_number,
                'customer'=>$loan->customer?->full_name,'branch'=>$loan->branch?->name,'officer'=>$loan->creator?->name ?: 'Unassigned',
                'original_principal'=>$this->moneyString($writeOff?->original_principal ?? $loan->principal),
                'write_off_amount'=>$this->moneyString($writeOff?->write_off_amount ?? $loan->total_outstanding),
                'recovered_amount'=>$this->moneyString($writeOff?->recovered_amount ?? '0.00'),
                'recoverable_balance'=>$this->moneyString($writeOff?->recoverable_balance ?? '0.00'),'reason'=>$writeOff?->reason ?: '—',
            ];
        })->all();
        $writeOffs = $loans->pluck('writeOff')->filter();

        return $this->payload('written-off-loans',[
            $this->summary('Written-off loans',(string)$loans->count(),'number'),
            $this->summary('Write-off amount',$this->sumModelMoney($writeOffs,'write_off_amount'),'money'),
            $this->summary('Recovered',$this->sumModelMoney($writeOffs,'recovered_amount'),'money'),
            $this->summary('Recoverable balance',$this->sumModelMoney($writeOffs,'recoverable_balance'),'money'),
        ],[
            'written_off_at'=>'Written off','write_off_number'=>'Write-off','loan_number'=>'Loan','customer'=>'Customer','branch'=>'Branch','officer'=>'Loan officer',
            'original_principal'=>'Original principal','write_off_amount'=>'Written off','recovered_amount'=>'Recovered','recoverable_balance'=>'Recoverable','reason'=>'Reason',
        ],$rows,['original_principal','write_off_amount','recovered_amount','recoverable_balance'],[],'Date range is applied to the approved write-off date stored on the loan.');
    }

    private function loanCollectionRate(array $scope): array
    {
        $branches = Branch::whereIn('id',$scope['branch_ids'])->orderBy('name')->get();
        $allLoans = $this->loanQuery($scope)->whereNotNull('disbursed_at')->get();
        $rows = [];
        $companyExpected = 0; $companyCollected = 0;
        foreach ($branches as $branch) {
            $loanIds = $allLoans->where('branch_id',$branch->id)->pluck('id')->all();
            [$expected,$collected] = $this->collectionRateMinor($loanIds,$scope);
            $companyExpected += $expected; $companyCollected += $collected;
            $rows[] = [
                'branch'=>$branch->name,'scheduled_due'=>$this->money->fromMinor($expected),'collected'=>$this->money->fromMinor($collected),
                'variance'=>$this->money->fromSignedMinor($collected-$expected),'collection_rate'=>$this->percent($collected,$expected),
            ];
        }

        return $this->payload('loan-collection-rate',[
            $this->summary('Scheduled due',$this->money->fromMinor($companyExpected),'money'),
            $this->summary('Collected',$this->money->fromMinor($companyCollected),'money'),
            $this->summary('Collection rate',$this->percent($companyCollected,$companyExpected),'percent'),
        ],[
            'branch'=>'Branch','scheduled_due'=>'Scheduled due','collected'=>'Collected','variance'=>'Variance','collection_rate'=>'Collection rate',
        ],$rows,['scheduled_due','collected','variance'],['collection_rate'],'Period rate = normal loan payments plus early settlements collected during the selected period ÷ currently active scheduled installments due in the selected period. The rate can exceed 100% when arrears or advance settlements are collected. Written-off recoveries are excluded from this rate and reported separately.');
    }

    private function branchPerformance(array $scope): array
    {
        $branches = Branch::whereIn('id',$scope['branch_ids'])->orderBy('name')->get();
        $allLoans = $this->loanQuery($scope)->get();
        $rows = $branches->map(function (Branch $branch) use ($allLoans,$scope): array {
            $metrics = $this->performanceMetrics($allLoans->where('branch_id',$branch->id)->values(),$scope);
            return array_merge(['branch'=>$branch->name],$metrics);
        })->all();

        $company = $this->performanceMetrics($allLoans,$scope);
        return $this->payload('branch-performance',[
            $this->summary('Selected branches',(string)$branches->count(),'number'),
            $this->summary('Period disbursed',$company['disbursed_amount'],'money'),
            $this->summary('Period collections',$company['collections'],'money'),
            $this->summary('Current portfolio',$company['total_outstanding'],'money'),
            $this->summary('Collection rate',$company['collection_rate'],'percent'),
        ],$this->performanceColumns(true),$rows,$this->performanceMoneyColumns(),['collection_rate'],'Period activity is combined with clearly current-labelled portfolio and arrears values.');
    }

    private function companyPerformance(array $scope): array
    {
        $loans = $this->loanQuery($scope)->get();
        $metrics = $this->performanceMetrics($loans,$scope);
        $rows = [array_merge(['scope'=>'Selected company scope'],$metrics)];

        return $this->payload('company-performance',[
            $this->summary('Active loans',$metrics['active_loans'],'number'),
            $this->summary('Current portfolio',$metrics['total_outstanding'],'money'),
            $this->summary('Period collections',$metrics['collections'],'money'),
            $this->summary('Period disbursed',$metrics['disbursed_amount'],'money'),
            $this->summary('Collection rate',$metrics['collection_rate'],'percent'),
        ],array_merge(['scope'=>'Scope'],$this->performanceColumns(false)),$rows,$this->performanceMoneyColumns(),['collection_rate'],'Consolidates only the selected accessible branches. Period flows and current portfolio values are kept distinct.');
    }

    private function loanOfficerPerformance(array $scope): array
    {
        $loans = $this->loanQuery($scope)->get();
        $groups = $loans->groupBy(fn (Loan $loan) => (string)($loan->created_by ?? 0));
        $rows = $groups->map(function (Collection $officerLoans, string $key) use ($scope): array {
            $first = $officerLoans->first();
            $metrics = $this->performanceMetrics($officerLoans->values(),$scope);
            $originated = $officerLoans->filter(fn (Loan $loan) => $this->between($loan->created_at,$scope['from'],$scope['to']));
            return array_merge([
                'officer'=>$key === '0' ? 'Unassigned' : ($first?->creator?->name ?: 'Unassigned'),
                'employee_number'=>$key === '0' ? '—' : ($first?->creator?->employee_number ?: '—'),
                'originated_loans'=>(string)$originated->count(),
                'originated_principal'=>$this->sumModelMoney($originated,'principal'),
            ],$metrics);
        })->sortByDesc(fn (array $row) => $this->minor($row['collections']))->values()->all();

        $allMetrics = $this->performanceMetrics($loans,$scope);
        $originatedAll = $loans->filter(fn (Loan $loan) => $this->between($loan->created_at,$scope['from'],$scope['to']));
        return $this->payload('loan-officer-performance',[
            $this->summary('Officers represented',(string)count($rows),'number'),
            $this->summary('Loans originated',(string)$originatedAll->count(),'number'),
            $this->summary('Originated principal',$this->sumModelMoney($originatedAll,'principal'),'money'),
            $this->summary('Period collections',$allMetrics['collections'],'money'),
        ],array_merge([
            'officer'=>'Loan officer','employee_number'=>'Employee #','originated_loans'=>'Originated','originated_principal'=>'Originated principal',
        ],$this->performanceColumns(false)),$rows,array_merge(['originated_principal'],$this->performanceMoneyColumns()),['collection_rate'],'Loan officer is the employee stored as the loan creator/originator. Period originations and collections are shown beside current portfolio quality.');
    }

    private function collateralReport(array $scope): array
    {
        $query = Collateral::query()->with(['customer','branch','category','loans'])->whereIn('branch_id',$scope['branch_ids']);
        if ($scope['record_status'] !== '' && in_array($scope['record_status'],Collateral::STATUSES,true)) $query->where('status',$scope['record_status']);
        if ($scope['search'] !== '') {
            $search = $scope['search'];
            $query->where(function (Builder $query) use ($search): void {
                $query->where('code','like','%'.$search.'%')->orWhere('item','like','%'.$search.'%')->orWhere('serial_number','like','%'.$search.'%')
                    ->orWhereHas('customer',fn (Builder $customer) => $customer->where('first_name','like','%'.$search.'%')->orWhere('last_name','like','%'.$search.'%')->orWhere('phone','like','%'.$search.'%'));
            });
        }
        if ($scope['loan_officer_id'] || $scope['loan_product_id']) {
            $query->whereHas('loans',function (Builder $loans) use ($scope): void {
                if ($scope['loan_officer_id']) $loans->where('created_by',$scope['loan_officer_id']);
                if ($scope['loan_product_id']) $loans->where('loan_product_id',$scope['loan_product_id']);
            });
        }
        $collaterals = $query->orderByDesc('id')->get();
        $rows = $collaterals->map(fn (Collateral $collateral) => [
            'code'=>$collateral->code,'customer'=>$collateral->customer?->full_name,'branch'=>$collateral->branch?->name,
            'category'=>$collateral->category?->name,'item'=>$collateral->item,'serial_number'=>$collateral->serial_number ?: '—',
            'estimated_value'=>$this->moneyString($collateral->estimated_value),'accepted_value'=>$this->moneyString($collateral->accepted_value),
            'status'=>$this->label($collateral->status),'location'=>$collateral->storage_location ?: '—',
            'loans'=>$collateral->loans->pluck('loan_number')->join(', ') ?: 'Unlinked',
        ])->all();

        return $this->payload('collateral-report',[
            $this->summary('Collateral items',(string)$collaterals->count(),'number'),
            $this->summary('Accepted value',$this->sumModelMoney($collaterals,'accepted_value'),'money'),
            $this->summary('In custody',(string)$collaterals->where('status','in_custody')->count(),'number'),
            $this->summary('Ready for release',(string)$collaterals->where('status','ready_for_release')->count(),'number'),
        ],[
            'code'=>'Collateral','customer'=>'Customer','branch'=>'Branch','category'=>'Category','item'=>'Item','serial_number'=>'Serial #',
            'estimated_value'=>'Estimated','accepted_value'=>'Accepted','status'=>'Status','location'=>'Storage','loans'=>'Linked loans',
        ],$rows,['estimated_value','accepted_value'],[],'Current collateral and custody snapshot. Use the status filter for custody/release/recovery views.');
    }

    private function topUpReport(array $scope): array
    {
        $query = LoanTopUp::query()->with(['branch','previousLoan.customer','newLoan.customer','newLoan.creator','financialAccount','executor'])
            ->whereIn('branch_id',$scope['branch_ids'])->whereBetween('top_up_date',[$scope['from']->toDateString(),$scope['to']->toDateString()]);
        $this->applyRelatedLoanFilters($query,'newLoan',$scope);
        if ($scope['search'] !== '') {
            $search=$scope['search'];
            $query->where(function (Builder $query) use ($search): void {
                $query->where('top_up_number','like','%'.$search.'%')->orWhere('reference','like','%'.$search.'%')
                    ->orWhereHas('newLoan',fn (Builder $loan) => $loan->where('loan_number','like','%'.$search.'%')->orWhereHas('customer',fn (Builder $customer) => $customer->where('first_name','like','%'.$search.'%')->orWhere('last_name','like','%'.$search.'%')->orWhere('phone','like','%'.$search.'%')));
            });
        }
        $rowsData = $query->orderByDesc('top_up_date')->orderByDesc('id')->get();
        $rows = $rowsData->map(fn (LoanTopUp $topUp) => [
            'date'=>$topUp->top_up_date?->format('d M Y'),'top_up_number'=>$topUp->top_up_number,'branch'=>$topUp->branch?->name,
            'customer'=>$topUp->newLoan?->customer?->full_name,'previous_loan'=>$topUp->previousLoan?->loan_number,'new_loan'=>$topUp->newLoan?->loan_number,
            'previous_outstanding'=>$this->moneyString($topUp->previous_outstanding),'settled_internally'=>$this->moneyString($topUp->amount_settled_internally),
            'new_principal'=>$this->moneyString($topUp->new_principal),'new_total'=>$this->moneyString($topUp->new_total_repayment),
            'cash_given'=>$this->moneyString($topUp->cash_given_to_customer),'account'=>$topUp->financialAccount?->name,'executed_by'=>$topUp->executor?->name,
        ])->all();

        return $this->payload('top-up-report',[
            $this->summary('Top-ups',(string)$rowsData->count(),'number'),
            $this->summary('Previous balances settled',$this->sumModelMoney($rowsData,'amount_settled_internally'),'money'),
            $this->summary('New principal',$this->sumModelMoney($rowsData,'new_principal'),'money'),
            $this->summary('Cash given',$this->sumModelMoney($rowsData,'cash_given_to_customer'),'money'),
        ],[
            'date'=>'Date','top_up_number'=>'Top-up','branch'=>'Branch','customer'=>'Customer','previous_loan'=>'Previous loan','new_loan'=>'New loan',
            'previous_outstanding'=>'Previous O/S','settled_internally'=>'Settled internally','new_principal'=>'New principal','new_total'=>'New total',
            'cash_given'=>'Cash given','account'=>'Account','executed_by'=>'Executed by',
        ],$rows,['previous_outstanding','settled_internally','new_principal','new_total','cash_given'],[],'Date range is applied to top-up execution date.');
    }

    private function restructuringReport(array $scope): array
    {
        $query = LoanRestructuring::query()->with(['loan.customer','loan.creator','branch','requester','approver','rejecter'])
            ->whereIn('branch_id',$scope['branch_ids'])->whereBetween('requested_at',[$scope['from'],$scope['to']]);
        if ($scope['record_status'] !== '' && in_array($scope['record_status'],LoanRestructuring::STATUSES,true)) $query->where('status',$scope['record_status']);
        $this->applyRelatedLoanFilters($query,'loan',$scope);
        if ($scope['search'] !== '') {
            $search=$scope['search'];
            $query->where(function (Builder $query) use ($search): void {
                $query->where('restructuring_number','like','%'.$search.'%')->orWhere('reason','like','%'.$search.'%')
                    ->orWhereHas('loan',fn (Builder $loan) => $loan->where('loan_number','like','%'.$search.'%')->orWhereHas('customer',fn (Builder $customer) => $customer->where('first_name','like','%'.$search.'%')->orWhere('last_name','like','%'.$search.'%')->orWhere('phone','like','%'.$search.'%')));
            });
        }
        $records = $query->orderByDesc('requested_at')->orderByDesc('id')->get();
        $rows = $records->map(fn (LoanRestructuring $record) => [
            'requested_at'=>$this->dateTime($record->requested_at),'restructuring_number'=>$record->restructuring_number,'loan_number'=>$record->loan?->loan_number,
            'customer'=>$record->loan?->customer?->full_name,'branch'=>$record->branch?->name,'officer'=>$record->loan?->creator?->name ?: 'Unassigned',
            'previous_version'=>(string)$record->previous_schedule_version,'new_version'=>(string)$record->new_schedule_version,
            'total_outstanding'=>$this->moneyString($record->total_outstanding),'status'=>$this->label($record->status),'reason'=>$record->reason,
            'decision_by'=>$record->approver?->name ?? $record->rejecter?->name ?? 'Pending','decided_at'=>$this->dateTime($record->approved_at ?? $record->rejected_at),
        ])->all();

        return $this->payload('restructuring-report',[
            $this->summary('Requests',(string)$records->count(),'number'),
            $this->summary('Approved',(string)$records->where('status','approved')->count(),'number'),
            $this->summary('Pending',(string)$records->where('status','pending')->count(),'number'),
            $this->summary('Outstanding represented',$this->sumModelMoney($records,'total_outstanding'),'money'),
        ],[
            'requested_at'=>'Requested','restructuring_number'=>'Restructuring','loan_number'=>'Loan','customer'=>'Customer','branch'=>'Branch','officer'=>'Loan officer',
            'previous_version'=>'Old schedule','new_version'=>'New schedule','total_outstanding'=>'O/S at request','status'=>'Status','reason'=>'Reason','decision_by'=>'Decision by','decided_at'=>'Decision date',
        ],$rows,['total_outstanding'],[],'Date range is applied to restructuring request date. Status filter supports pending, approved and rejected.');
    }

    private function writeOffRecoveryReport(array $scope): array
    {
        $query = LoanWriteOffRecovery::query()->with(['writeOff','loan.customer','loan.creator','branch','financialAccount','receiver'])
            ->whereIn('branch_id',$scope['branch_ids'])->whereBetween('recovery_date',[$scope['from'],$scope['to']]);
        $this->applyRelatedLoanFilters($query,'loan',$scope);
        if ($scope['search'] !== '') {
            $search=$scope['search'];
            $query->where(function (Builder $query) use ($search): void {
                $query->where('recovery_number','like','%'.$search.'%')->orWhere('reference','like','%'.$search.'%')
                    ->orWhereHas('loan',fn (Builder $loan) => $loan->where('loan_number','like','%'.$search.'%')->orWhereHas('customer',fn (Builder $customer) => $customer->where('first_name','like','%'.$search.'%')->orWhere('last_name','like','%'.$search.'%')->orWhere('phone','like','%'.$search.'%')));
            });
        }
        $recoveries = $query->orderByDesc('recovery_date')->orderByDesc('id')->get();
        $rows = $recoveries->map(fn (LoanWriteOffRecovery $recovery) => [
            'recovery_date'=>$this->dateTime($recovery->recovery_date),'recovery_number'=>$recovery->recovery_number,'write_off_number'=>$recovery->writeOff?->write_off_number,
            'loan_number'=>$recovery->loan?->loan_number,'customer'=>$recovery->loan?->customer?->full_name,'branch'=>$recovery->branch?->name,
            'officer'=>$recovery->loan?->creator?->name ?: 'Unassigned','amount'=>$this->moneyString($recovery->amount),'method'=>$this->label($recovery->method),
            'account'=>$recovery->financialAccount?->name,'reference'=>$recovery->reference ?: '—','received_by'=>$recovery->receiver?->name,
        ])->all();
        $writeOffs = $recoveries->pluck('writeOff')->filter()->unique('id');

        return $this->payload('write-off-recovery-report',[
            $this->summary('Recovery transactions',(string)$recoveries->count(),'number'),
            $this->summary('Recovered in period',$this->sumModelMoney($recoveries,'amount'),'money'),
            $this->summary('Current recoverable balance',$this->sumModelMoney($writeOffs,'recoverable_balance'),'money'),
        ],[
            'recovery_date'=>'Recovery date','recovery_number'=>'Recovery','write_off_number'=>'Write-off','loan_number'=>'Loan','customer'=>'Customer','branch'=>'Branch',
            'officer'=>'Loan officer','amount'=>'Amount','method'=>'Method','account'=>'Account','reference'=>'Reference','received_by'=>'Received by',
        ],$rows,['amount'],[],'Date range is applied to written-off loan recovery posting date.');
    }

    private function performanceMetrics(Collection $loans, array $scope): array
    {
        $active = $loans->where('status','active');
        $overdue = $active->filter(fn (Loan $loan) => (int)$loan->days_overdue > 0 || $this->minor($loan->total_overdue) > 0);
        $periodDisbursed = $loans->filter(fn (Loan $loan) => $this->between($loan->disbursed_at,$scope['from'],$scope['to']));
        $periodCompleted = $loans->filter(fn (Loan $loan) => in_array($loan->status,['completed','fully_paid'],true) && $this->between($loan->completed_at,$scope['from'],$scope['to']));
        $periodWrittenOff = $loans->filter(fn (Loan $loan) => $loan->status === 'written_off' && $this->between($loan->written_off_at,$scope['from'],$scope['to']));
        $loanIds = $loans->pluck('id')->all();
        [$expected,$normalCollected] = $this->collectionRateMinor($loanIds,$scope);
        $recoveries = $this->writeOffRecoveriesMinor($loanIds,$scope);
        $writeOffAmount = $this->writeOffAmountMinor($loanIds,$scope);

        return [
            'active_loans'=>(string)$active->count(),
            'principal_outstanding'=>$this->sumModelMoney($active,'principal_outstanding'),
            'total_outstanding'=>$this->sumModelMoney($active,'total_outstanding'),
            'overdue_loans'=>(string)$overdue->count(),
            'overdue_amount'=>$this->sumModelMoney($overdue,'total_overdue'),
            'disbursed_loans'=>(string)$periodDisbursed->count(),
            'disbursed_amount'=>$this->sumModelMoney($periodDisbursed,'principal'),
            'collections'=>$this->money->fromMinor($normalCollected+$recoveries),
            'scheduled_due'=>$this->money->fromMinor($expected),
            'collection_rate'=>$this->percent($normalCollected,$expected),
            'completed_loans'=>(string)$periodCompleted->count(),
            'written_off_loans'=>(string)$periodWrittenOff->count(),
            'write_off_amount'=>$this->money->fromMinor($writeOffAmount),
            'write_off_recoveries'=>$this->money->fromMinor($recoveries),
        ];
    }

    private function performanceColumns(bool $withBranch): array
    {
        $columns = [
            'active_loans'=>'Active loans','principal_outstanding'=>'Principal O/S','total_outstanding'=>'Total O/S','overdue_loans'=>'Overdue loans',
            'overdue_amount'=>'Overdue amount','disbursed_loans'=>'Disbursed loans','disbursed_amount'=>'Disbursed amount','collections'=>'Collections',
            'scheduled_due'=>'Scheduled due','collection_rate'=>'Collection rate','completed_loans'=>'Completed','written_off_loans'=>'Written off',
            'write_off_amount'=>'Write-off amount','write_off_recoveries'=>'W/O recoveries',
        ];
        return $withBranch ? array_merge(['branch'=>'Branch'],$columns) : $columns;
    }

    private function performanceMoneyColumns(): array
    {
        return ['principal_outstanding','total_outstanding','overdue_amount','disbursed_amount','collections','scheduled_due','write_off_amount','write_off_recoveries'];
    }

    private function collectionRateMinor(array $loanIds, array $scope): array
    {
        if ($loanIds === []) return [0,0];
        $installments = LoanInstallment::query()->whereIn('loan_id',$loanIds)->where('is_active',true)
            ->whereDate('due_date','>=',$scope['from']->toDateString())
            ->whereDate('due_date','<=',$scope['to']->toDateString())
            ->get(['amount_due','penalty_due']);
        $expected = 0;
        foreach ($installments as $installment) $expected += $this->minor($installment->amount_due) + $this->minor($installment->penalty_due);

        $payments = LoanPayment::query()->whereIn('loan_id',$loanIds)->whereBetween('payment_date',[$scope['from'],$scope['to']])->get(['amount']);
        $settlements = LoanEarlySettlement::query()->whereIn('loan_id',$loanIds)
            ->whereDate('settlement_date','>=',$scope['from']->toDateString())
            ->whereDate('settlement_date','<=',$scope['to']->toDateString())
            ->get(['final_amount']);
        return [$expected,$this->sumMinor($payments,'amount')+$this->sumMinor($settlements,'final_amount')];
    }

    private function writeOffRecoveriesMinor(array $loanIds, array $scope): int
    {
        if ($loanIds === []) return 0;
        $rows = LoanWriteOffRecovery::query()->whereIn('loan_id',$loanIds)->whereBetween('recovery_date',[$scope['from'],$scope['to']])->get(['amount']);
        return $this->sumMinor($rows,'amount');
    }

    private function writeOffAmountMinor(array $loanIds, array $scope): int
    {
        if ($loanIds === []) return 0;
        $rows = LoanWriteOff::query()->whereIn('loan_id',$loanIds)->where('status','approved')->whereBetween('approved_at',[$scope['from'],$scope['to']])->get(['write_off_amount']);
        return $this->sumMinor($rows,'write_off_amount');
    }

    private function loanQuery(array $scope): Builder
    {
        $query = Loan::query()->with(['branch','customer','product','creator','writeOff'])->whereIn('branch_id',$scope['branch_ids']);
        if ($scope['loan_officer_id']) $query->where('created_by',$scope['loan_officer_id']);
        if ($scope['loan_product_id']) $query->where('loan_product_id',$scope['loan_product_id']);
        if ($scope['search'] !== '') {
            $search = $scope['search'];
            $query->where(function (Builder $query) use ($search): void {
                $query->where('loan_number','like','%'.$search.'%')->orWhere('purpose','like','%'.$search.'%')
                    ->orWhereHas('customer',function (Builder $customer) use ($search): void {
                        $customer->where('customer_number','like','%'.$search.'%')->orWhere('phone','like','%'.$search.'%')
                            ->orWhere('first_name','like','%'.$search.'%')->orWhere('middle_name','like','%'.$search.'%')->orWhere('last_name','like','%'.$search.'%');
                    });
            });
        }
        return $query;
    }

    private function applyRelatedLoanFilters(Builder $query, string $relation, array $scope): void
    {
        if (! $scope['loan_officer_id'] && ! $scope['loan_product_id']) return;
        $query->whereHas($relation,function (Builder $loan) use ($scope): void {
            if ($scope['loan_officer_id']) $loan->where('created_by',$scope['loan_officer_id']);
            if ($scope['loan_product_id']) $loan->where('loan_product_id',$scope['loan_product_id']);
        });
    }

    private function portfolioLoanRow(Loan $loan): array
    {
        return [
            'loan_number'=>$loan->loan_number,'customer'=>$loan->customer?->full_name,'phone'=>$loan->customer?->phone,'branch'=>$loan->branch?->name,
            'officer'=>$loan->creator?->name ?: 'Unassigned','product'=>$loan->product?->name,'principal'=>$this->moneyString($loan->principal),
            'total_paid'=>$this->moneyString($loan->total_paid),'principal_outstanding'=>$this->moneyString($loan->principal_outstanding),
            'interest_outstanding'=>$this->moneyString($loan->interest_outstanding),'fees_outstanding'=>$this->moneyString($loan->fees_outstanding),
            'penalties_outstanding'=>$this->moneyString($loan->penalties_outstanding),'total_outstanding'=>$this->moneyString($loan->total_outstanding),
            'days_overdue'=>(string)((int)$loan->days_overdue),
        ];
    }

    private function payload(string $slug, array $summary, array $columns, array $rows, array $moneyColumns = [], array $percentColumns = [], string $note = ''): array
    {
        return [
            'slug'=>$slug,'title'=>self::REPORTS[$slug],'summary'=>$summary,'columns'=>$columns,'rows'=>$rows,
            'money_columns'=>$moneyColumns,'percent_columns'=>$percentColumns,'row_count'=>count($rows),'note'=>$note,
        ];
    }

    private function summary(string $label, string $value, string $type): array
    {
        return ['label'=>$label,'value'=>$value,'type'=>$type];
    }

    private function sumModelMoney(Collection $rows, string $field): string
    {
        return $this->money->fromMinor($this->sumMinor($rows,$field));
    }

    private function sumMinor(Collection $rows, string $field): int
    {
        $sum = 0;
        foreach ($rows as $row) $sum += $this->minor(data_get($row,$field,'0'));
        return $sum;
    }

    private function minor(mixed $amount): int
    {
        $value = $amount === null || $amount === '' ? '0' : (string)$amount;
        return $this->money->toMinor($value);
    }

    private function moneyString(mixed $amount): string
    {
        return $this->money->fromMinor($this->minor($amount));
    }

    private function percent(int $numerator, int $denominator): string
    {
        if ($denominator <= 0) return '0.00';
        $basisPoints = $this->money->mulDivRound($numerator,10000,$denominator);
        return intdiv($basisPoints,100).'.'.str_pad((string)($basisPoints%100),2,'0',STR_PAD_LEFT);
    }

    private function between(mixed $value, CarbonImmutable $from, CarbonImmutable $to): bool
    {
        if (! $value) return false;
        $date = CarbonImmutable::parse($value);
        return $date->betweenIncluded($from,$to);
    }

    private function dateTime(mixed $value): string
    {
        return $value ? CarbonImmutable::parse($value)->format('d M Y H:i') : '—';
    }

    private function label(?string $value): string
    {
        if (! $value) return '—';
        return str($value)->replace(['_','-'],' ')->headline()->toString();
    }
}
