<?php

namespace App\Http\Controllers\Tenant;

use App\Http\Controllers\Controller;
use App\Models\AccountingAccount;
use App\Models\BillPayment;
use App\Models\Customer;
use App\Models\Expense;
use App\Models\Income;
use App\Models\LedgerEntry;
use App\Models\Loan;
use App\Models\LoanPayment;
use App\Models\LoanRepaymentSchedule;
use App\Models\SavingsAccount;
use App\Models\SavingsTransaction;
use Barryvdh\DomPDF\Facade\Pdf;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\View\View;

class ReportController extends Controller
{
    private const REPORT_TYPES = [
        'overview' => ['label' => 'Overview', 'icon' => 'bi-speedometer2'],
        'savings' => ['label' => 'Savings', 'icon' => 'bi-wallet2'],
        'loans' => ['label' => 'Loans', 'icon' => 'bi-cash-coin'],
        'investments' => ['label' => 'Investments', 'icon' => 'bi-piggy-bank'],
        'withdrawals' => ['label' => 'Withdrawals', 'icon' => 'bi-cash'],
        'repayments' => ['label' => 'Repayments', 'icon' => 'bi-arrow-repeat'],
        'expenses' => ['label' => 'Expenses', 'icon' => 'bi-receipt-cutoff'],
        'bill_payments' => ['label' => 'Bill Payments', 'icon' => 'bi-phone'],
        'risk' => ['label' => 'Arrears & Risk', 'icon' => 'bi-exclamation-triangle'],
        'customers' => ['label' => 'Members', 'icon' => 'bi-people'],
        'financials' => ['label' => 'Financials', 'icon' => 'bi-journal-richtext'],
    ];

    private const UNPAID_SCHEDULE_STATUSES = ['pending', 'partial', 'overdue'];
    private const LIVE_LOAN_STATUSES = ['active', 'disbursed'];

    public function index(Request $request): View
    {
        return view('tenant.reports.index', $this->buildReportData($request));
    }

    public function pdf(Request $request): Response
    {
        $data = $this->buildReportData($request);
        $filename = str('microfinance-'.$data['report'].'-report-'.now()->format('Y-m-d'))->slug().'.pdf';

        $pdf = Pdf::loadView('tenant.reports.pdf', $data);

        return $pdf->download($filename);
    }

    private function buildReportData(Request $request): array
    {
        [$from, $to] = $this->reportDates($request);
        $fromDay = $from->copy()->startOfDay();
        $toDay = $to->copy()->endOfDay();
        $report = $this->activeReport($request);
        $nplDays = max(1, min((int) $request->integer('npl_days', 30), 365));

        $data = [
            'reportTypes' => self::REPORT_TYPES,
            'report' => $report,
            'reportTitle' => self::REPORT_TYPES[$report]['label'],
            'from' => $from,
            'to' => $to,
            'fromDay' => $fromDay,
            'toDay' => $toDay,
            'nplDays' => $nplDays,
            'summary' => $this->overviewSummary($fromDay, $toDay, $nplDays),
            'financialSnapshot' => $this->financialSnapshot($fromDay, $toDay),
        ];

        return $data + match ($report) {
            'savings' => $this->savingsReport($fromDay, $toDay),
            'loans' => $this->loanReport($fromDay, $toDay, $request),
            'withdrawals' => $this->withdrawalReport($fromDay, $toDay),
            'repayments' => $this->repaymentReport($fromDay, $toDay),
            'risk' => $this->riskReport($fromDay, $toDay, $nplDays),
            'investments' => $this->investmentReport($fromDay, $toDay),
            'expenses' => $this->expenseReport($fromDay, $toDay),
            'bill_payments' => $this->billPaymentReport($fromDay, $toDay),
            'customers' => $this->customerReport(),
            'financials' => $this->financialReport($fromDay, $toDay),
            default => $this->overviewReport($fromDay, $toDay, $nplDays),
        };
    }

    private function reportDates(Request $request): array
    {
        $from = $request->filled('from') ? Carbon::parse($request->input('from')) : now()->startOfMonth();
        $to = $request->filled('to') ? Carbon::parse($request->input('to')) : now()->endOfMonth();

        if ($from->gt($to)) {
            [$from, $to] = [$to, $from];
        }

        return [$from->startOfDay(), $to->endOfDay()];
    }

    private function activeReport(Request $request): string
    {
        $report = (string) $request->input('report', 'overview');

        return array_key_exists($report, self::REPORT_TYPES) ? $report : 'overview';
    }

    private function overviewSummary(Carbon $fromDay, Carbon $toDay, int $nplDays): array
    {
        $periodDue = $this->scheduleQuery()->whereBetween('due_date', [$fromDay->toDateString(), $toDay->toDateString()]);
        $overdue = $this->scheduleQuery()->whereDate('due_date', '<', today())
            ->whereIn('status', self::UNPAID_SCHEDULE_STATUSES);
        $nplCutoff = today()->subDays($nplDays);

        return [
            'customers' => $this->customerQuery()->count(),
            'activeCustomers' => $this->customerQuery()->where('status', 'active')->count(),
            'savingsBalance' => (float) $this->savingsAccountQuery()->sum('balance'),
            'periodDeposits' => (float) $this->savingsTransactionQuery()->where('type', 'deposit')->whereBetween('transacted_at', [$fromDay, $toDay])->sum('amount'),
            'periodWithdrawals' => (float) $this->savingsTransactionQuery()->where('type', 'withdrawal')->whereBetween('transacted_at', [$fromDay, $toDay])->sum('amount'),
            'loanPortfolio' => (float) $this->loanQuery()->whereIn('status', self::LIVE_LOAN_STATUSES)->sum('balance'),
            'activeLoans' => $this->loanQuery()->whereIn('status', self::LIVE_LOAN_STATUSES)->count(),
            'periodLoanDisbursement' => (float) $this->loanQuery()->whereBetween('disbursed_at', [$fromDay, $toDay])->sum('amount_disbursed'),
            'periodCollections' => (float) $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay])->sum('amount'),
            'repaymentPerformance' => $this->repaymentPerformance($fromDay, $toDay),
            'periodDue' => $this->scheduleDueAmount($periodDue),
            'overdueAmount' => $this->scheduleOutstandingAmount($overdue),
            'overdueCount' => (clone $overdue)->count(),
            'nplCount' => $this->loanQuery()->whereIn('status', self::LIVE_LOAN_STATUSES)
                ->where('balance', '>', 0)
                ->whereHas('schedules', function ($query) use ($nplCutoff): void {
                    $query->whereDate('due_date', '<=', $nplCutoff)
                        ->whereIn('status', self::UNPAID_SCHEDULE_STATUSES);
                })
                ->count(),
        ];
    }

    private function overviewReport(Carbon $fromDay, Carbon $toDay, int $nplDays): array
    {
        return [
            'dailySavings' => $this->savingsTransactionQuery()->whereBetween('transacted_at', [$fromDay, $toDay])
                ->selectRaw('DATE(transacted_at) as day, type, SUM(amount) as total')
                ->groupBy('day', 'type')
                ->orderBy('day')
                ->get(),
            'loanStatusBreakdown' => $this->loanStatusBreakdown(),
            'recentPayments' => $this->loanPaymentQuery()->with(['loan.customer', 'receiver'])
                ->whereBetween('paid_at', [$fromDay, $toDay])
                ->latest('paid_at')
                ->limit(10)
                ->get(),
            'topOverdueSchedules' => $this->overdueSchedulesQuery()
                ->with('loan.customer')
                ->orderBy('due_date')
                ->limit(10)
                ->get(),
            'nplLoans' => $this->nplLoans($nplDays)->take(10),
        ];
    }

    private function savingsReport(Carbon $fromDay, Carbon $toDay): array
    {
        return [
            'savingsMetrics' => [
                'accounts' => $this->savingsAccountQuery()->count(),
                'activeAccounts' => $this->savingsAccountQuery()->where('status', 'active')->count(),
                'balance' => (float) $this->savingsAccountQuery()->sum('balance'),
                'deposits' => (float) $this->savingsTransactionQuery()->where('type', 'deposit')->whereBetween('transacted_at', [$fromDay, $toDay])->sum('amount'),
                'withdrawals' => (float) $this->savingsTransactionQuery()->where('type', 'withdrawal')->whereBetween('transacted_at', [$fromDay, $toDay])->sum('amount'),
            ],
            'savingsByProduct' => $this->savingsAccountQuery()->select('product_name')
                ->selectRaw('COUNT(*) as accounts_count, SUM(balance) as total_balance')
                ->groupBy('product_name')
                ->orderByDesc('total_balance')
                ->get(),
            'savingsTransactions' => $this->savingsTransactionQuery()->with(['customer', 'account', 'creator', 'approver'])
                ->whereBetween('transacted_at', [$fromDay, $toDay])
                ->latest('transacted_at')
                ->limit(100)
                ->get(),
            'topSavingsAccounts' => $this->savingsAccountQuery()->with('customer')
                ->orderByDesc('balance')
                ->limit(50)
                ->get(),
        ];
    }

    private function loanReport(Carbon $fromDay, Carbon $toDay, Request $request): array
    {
        $status = $request->input('loan_status');
        $loans = $this->loanQuery()->with(['customer', 'product', 'creator', 'approver'])
            ->when($status, fn ($query) => $query->where('status', $status))
            ->where(function ($query) use ($fromDay, $toDay): void {
                $query->whereBetween('application_date', [$fromDay->toDateString(), $toDay->toDateString()])
                    ->orWhereBetween('disbursed_at', [$fromDay, $toDay])
                    ->orWhereIn('status', self::LIVE_LOAN_STATUSES);
            })
            ->latest()
            ->limit(150)
            ->get();

        return [
            'loanStatus' => $status,
            'loanMetrics' => [
                'applications' => $this->loanQuery()->whereBetween('application_date', [$fromDay->toDateString(), $toDay->toDateString()])->count(),
                'approved' => $this->loanQuery()->where('status', 'approved')->whereBetween('approved_at', [$fromDay, $toDay])->count(),
                'disbursedAmount' => (float) $this->loanQuery()->whereBetween('disbursed_at', [$fromDay, $toDay])->sum('amount_disbursed'),
                'principal' => (float) $this->loanQuery()->whereBetween('application_date', [$fromDay->toDateString(), $toDay->toDateString()])->sum('principal'),
                'interestExpected' => (float) $this->loanQuery()->whereIn('status', self::LIVE_LOAN_STATUSES)->sum('interest_amount'),
                'portfolioBalance' => (float) $this->loanQuery()->whereIn('status', self::LIVE_LOAN_STATUSES)->sum('balance'),
            ],
            'loanStatusBreakdown' => $this->loanStatusBreakdown(),
            'loanProductBreakdown' => $this->loanQuery()
                ->leftJoin('loan_products', 'loans.loan_product_id', '=', 'loan_products.id')
                ->selectRaw("COALESCE(loan_products.name, 'No product') as product_name, COUNT(loans.id) as loans_count, SUM(loans.principal) as principal_total, SUM(loans.balance) as balance_total")
                ->groupBy('product_name')
                ->orderByDesc('balance_total')
                ->get(),
            'loans' => $loans,
        ];
    }

    private function repaymentReport(Carbon $fromDay, Carbon $toDay): array
    {
        $dueQuery = $this->scheduleQuery()->whereBetween('due_date', [$fromDay->toDateString(), $toDay->toDateString()]);

        return [
            'repaymentMetrics' => [
                'due' => $this->scheduleDueAmount($dueQuery),
                'paidOnSchedules' => $this->schedulePaidAmount($dueQuery),
                'collections' => (float) $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay])->sum('amount'),
                'principal' => (float) $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay])->sum('principal_component'),
                'interest' => (float) $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay])->sum('interest_component'),
                'fees' => (float) $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay])->sum('fee_component'),
                'penalty' => (float) $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay])->sum('penalty_component'),
                'collectionRate' => $this->repaymentPerformance($fromDay, $toDay),
            ],
            'repaymentsByMethod' => $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay])
                ->select('method')
                ->selectRaw('COUNT(*) as payments_count, SUM(amount) as total_amount')
                ->groupBy('method')
                ->orderByDesc('total_amount')
                ->get(),
            'payments' => $this->loanPaymentQuery()->with(['loan.customer', 'customer', 'receiver'])
                ->whereBetween('paid_at', [$fromDay, $toDay])
                ->latest('paid_at')
                ->limit(150)
                ->get(),
            'dueSchedules' => $this->scheduleQuery()->with('loan.customer')
                ->whereBetween('due_date', [$fromDay->toDateString(), $toDay->toDateString()])
                ->orderBy('due_date')
                ->limit(150)
                ->get(),
        ];
    }

    private function withdrawalReport(Carbon $fromDay, Carbon $toDay): array
    {
        $baseQuery = $this->withdrawalTransactionQuery();
        $periodQuery = (clone $baseQuery)->whereBetween('savings_transactions.transacted_at', [$fromDay, $toDay]);
        $postedPeriodQuery = (clone $periodQuery);

        if (Schema::hasColumn('savings_transactions', 'status')) {
            $postedPeriodQuery->where('savings_transactions.status', 'posted');
        }

        $pendingQuery = (clone $baseQuery);
        if (Schema::hasColumn('savings_transactions', 'status')) {
            $pendingQuery->where('savings_transactions.status', 'pending');
        } else {
            $pendingQuery->whereRaw('1 = 0');
        }

        return [
            'withdrawalMetrics' => [
                'requests' => (clone $periodQuery)->count(),
                'withdrawn' => (float) (clone $postedPeriodQuery)->sum('amount'),
                'pendingCount' => (clone $pendingQuery)->count(),
                'pendingAmount' => (float) (clone $pendingQuery)->sum('amount'),
                'average' => (clone $postedPeriodQuery)->count() > 0
                    ? round((float) (clone $postedPeriodQuery)->avg('amount'), 2)
                    : 0,
            ],
            'withdrawals' => (clone $periodQuery)->with(['customer', 'account', 'creator', 'approver'])
                ->latest('transacted_at')
                ->limit(150)
                ->get(),
            'withdrawalsByMethod' => (clone $postedPeriodQuery)->select('method')
                ->selectRaw('COUNT(*) as withdrawals_count, SUM(amount) as total_amount')
                ->groupBy('method')
                ->orderByDesc('total_amount')
                ->get(),
            'withdrawalsByProduct' => (clone $postedPeriodQuery)
                ->join('savings_accounts', 'savings_transactions.savings_account_id', '=', 'savings_accounts.id')
                ->selectRaw("COALESCE(savings_accounts.product_name, 'No product') as product_name, COUNT(savings_transactions.id) as withdrawals_count, SUM(savings_transactions.amount) as total_amount")
                ->groupBy('product_name')
                ->orderByDesc('total_amount')
                ->get(),
            'withdrawalsByStatus' => Schema::hasColumn('savings_transactions', 'status')
                ? (clone $periodQuery)->select('savings_transactions.status')
                    ->selectRaw('COUNT(*) as withdrawals_count, SUM(savings_transactions.amount) as total_amount')
                    ->groupBy('savings_transactions.status')
                    ->orderBy('savings_transactions.status')
                    ->get()
                : collect(),
        ];
    }

    private function riskReport(Carbon $fromDay, Carbon $toDay, int $nplDays): array
    {
        $dueSoon = $this->scheduleQuery()->with('loan.customer')
            ->whereBetween('due_date', [today()->toDateString(), today()->addDays(7)->toDateString()])
            ->whereIn('status', self::UNPAID_SCHEDULE_STATUSES)
            ->orderBy('due_date')
            ->limit(100)
            ->get();

        return [
            'riskMetrics' => [
                'outstandingLoans' => $this->loanQuery()->whereIn('status', self::LIVE_LOAN_STATUSES)->where('balance', '>', 0)->count(),
                'outstandingBalance' => (float) $this->loanQuery()->whereIn('status', self::LIVE_LOAN_STATUSES)->where('balance', '>', 0)->sum('balance'),
                'periodDue' => $this->scheduleDueAmount($this->scheduleQuery()->whereBetween('due_date', [$fromDay->toDateString(), $toDay->toDateString()])),
                'periodUnpaid' => $this->scheduleOutstandingAmount($this->scheduleQuery()->whereBetween('due_date', [$fromDay->toDateString(), $toDay->toDateString()])->whereIn('status', self::UNPAID_SCHEDULE_STATUSES)),
                'overdueAmount' => $this->scheduleOutstandingAmount($this->overdueSchedulesQuery()),
                'nplCount' => $this->nplLoans($nplDays)->count(),
            ],
            'outstandingLoans' => $this->loanQuery()->with(['customer', 'product'])
                ->whereIn('status', self::LIVE_LOAN_STATUSES)
                ->where('balance', '>', 0)
                ->orderByDesc('balance')
                ->limit(100)
                ->get(),
            'dueSoonSchedules' => $dueSoon,
            'overdueSchedules' => $this->overdueSchedulesQuery()
                ->with('loan.customer')
                ->orderBy('due_date')
                ->limit(150)
                ->get(),
            'nplLoans' => $this->nplLoans($nplDays),
        ];
    }

    private function investmentReport(Carbon $fromDay, Carbon $toDay): array
    {
        $tenantId = auth()->user()->tenant_id;

        if (! Schema::hasTable('investment_accounts')) {
            return [
                'investmentReady' => false,
                'investmentMetrics' => [
                    'products' => 0,
                    'accounts' => 0,
                    'activeAccounts' => 0,
                    'principal' => 0,
                    'balance' => 0,
                    'returns' => 0,
                ],
                'investmentAccounts' => collect(),
                'investmentTransactions' => collect(),
            ];
        }

        $accounts = DB::table('investment_accounts')->where('investment_accounts.tenant_id', $tenantId);

        if (! auth()->user()->canViewAllTenantCustomers() && Schema::hasColumn('investment_accounts', 'customer_id')) {
            $accounts->whereIn('customer_id', $this->customerQuery()->select('id'));
        }
        $hasTransactions = Schema::hasTable('investment_transactions');
        $transactions = $hasTransactions ? DB::table('investment_transactions')->where('investment_transactions.tenant_id', $tenantId) : null;
        $postedTransactions = $transactions && Schema::hasColumn('investment_transactions', 'status')
            ? (clone $transactions)->where('investment_transactions.status', 'posted')
            : $transactions;

        return [
            'investmentReady' => true,
            'investmentMetrics' => [
                'products' => Schema::hasTable('investment_products') ? DB::table('investment_products')->where('tenant_id', $tenantId)->count() : 0,
                'accounts' => (clone $accounts)->count(),
                'activeAccounts' => (clone $accounts)->where('status', 'active')->count(),
                'principal' => (float) (clone $accounts)->sum('amount'),
                'balance' => (float) (clone $accounts)->sum('balance'),
                'returns' => $hasTransactions ? (float) (clone $postedTransactions)->where('type', 'return')->whereBetween('transacted_at', [$fromDay, $toDay])->sum('amount') : 0,
            ],
            'investmentAccounts' => (clone $accounts)
                ->leftJoin('customers', 'investment_accounts.customer_id', '=', 'customers.id')
                ->select('investment_accounts.*', 'customers.first_name', 'customers.last_name', 'customers.account_number as customer_account')
                ->orderByDesc('investment_accounts.created_at')
                ->limit(100)
                ->get(),
            'investmentTransactions' => $hasTransactions
                ? (clone $postedTransactions)
                    ->leftJoin('users as creators', 'investment_transactions.created_by', '=', 'creators.id')
                    ->leftJoin('users as approvers', 'investment_transactions.approved_by', '=', 'approvers.id')
                    ->select('investment_transactions.*', 'creators.name as initiated_by_name', 'approvers.name as approved_by_name')
                    ->whereBetween('investment_transactions.transacted_at', [$fromDay, $toDay])
                    ->orderByDesc('investment_transactions.transacted_at')
                    ->limit(100)
                    ->get()
                : collect(),
        ];
    }

    private function expenseReport(Carbon $fromDay, Carbon $toDay): array
    {
        $periodQuery = Expense::with(['recordedBy', 'approvedBy', 'branch', 'categoryModel'])
            ->whereBetween('spent_at', [$fromDay->toDateString(), $toDay->toDateString()]);
        $approvedPeriodQuery = Expense::where('status', Expense::STATUS_APPROVED)
            ->whereBetween('spent_at', [$fromDay->toDateString(), $toDay->toDateString()]);
        $pendingQuery = Expense::where('status', Expense::STATUS_PENDING);

        return [
            'expenseMetrics' => [
                'approvedTotal' => (float) (clone $approvedPeriodQuery)->sum('amount'),
                'todayTotal' => (float) Expense::where('status', Expense::STATUS_APPROVED)->whereDate('spent_at', today())->sum('amount'),
                'pendingCount' => (clone $pendingQuery)->count(),
                'pendingAmount' => (float) (clone $pendingQuery)->sum('amount'),
                'recurringTotal' => (float) Expense::where('status', Expense::STATUS_APPROVED)
                    ->where('is_recurring', true)
                    ->whereBetween('spent_at', [$fromDay->toDateString(), $toDay->toDateString()])
                    ->sum('amount'),
            ],
            'expenses' => (clone $periodQuery)->latest('spent_at')->limit(150)->get(),
            'expenseByCategory' => (clone $approvedPeriodQuery)->select('category')
                ->selectRaw('COUNT(*) as entries_count, SUM(amount) as total_amount')
                ->groupBy('category')
                ->orderByDesc('total_amount')
                ->get(),
            'expenseByStatus' => Expense::whereBetween('spent_at', [$fromDay->toDateString(), $toDay->toDateString()])
                ->select('status')
                ->selectRaw('COUNT(*) as entries_count, SUM(amount) as total_amount')
                ->groupBy('status')
                ->orderBy('status')
                ->get(),
            'expenseByBranch' => Schema::hasColumn('expenses', 'branch_id')
                ? (clone $approvedPeriodQuery)
                    ->leftJoin('branches', 'expenses.branch_id', '=', 'branches.id')
                    ->selectRaw("COALESCE(branches.name, 'Head Office') as branch_name, COUNT(expenses.id) as entries_count, SUM(expenses.amount) as total_amount")
                    ->groupBy('branch_name')
                    ->orderByDesc('total_amount')
                    ->get()
                : collect(),
            'expenseByMethod' => (clone $approvedPeriodQuery)->select('method')
                ->selectRaw('COUNT(*) as entries_count, SUM(amount) as total_amount')
                ->groupBy('method')
                ->orderByDesc('total_amount')
                ->get(),
            'pendingExpenses' => Expense::with(['recordedBy', 'branch', 'categoryModel'])
                ->where('status', Expense::STATUS_PENDING)
                ->latest('submitted_at')
                ->limit(100)
                ->get(),
        ];
    }

    private function billPaymentReport(Carbon $fromDay, Carbon $toDay): array
    {
        $periodQuery = $this->billPaymentQuery()->whereBetween('created_at', [$fromDay, $toDay]);
        $successfulQuery = (clone $periodQuery)->where('status', BillPayment::STATUS_SUCCESSFUL);

        return [
            'billMetrics' => [
                'volume' => (float) (clone $successfulQuery)->sum('total_amount'),
                'transactions' => (clone $periodQuery)->count(),
                'successful' => (clone $successfulQuery)->count(),
                'earnings' => (float) (clone $successfulQuery)->sum('fee') + (float) (clone $successfulQuery)->sum('commission'),
                'pending' => (clone $periodQuery)->where('status', BillPayment::STATUS_PENDING)->count(),
                'failed' => (clone $periodQuery)->where('status', BillPayment::STATUS_FAILED)->count(),
            ],
            'billPayments' => (clone $periodQuery)->with(['customer', 'recordedBy'])
                ->latest()
                ->limit(150)
                ->get(),
            'billPaymentsByStatus' => (clone $periodQuery)->select('status')
                ->selectRaw('COUNT(*) as payments_count, SUM(total_amount) as total_amount, SUM(fee + commission) as earnings_total')
                ->groupBy('status')
                ->orderBy('status')
                ->get(),
            'billPaymentsByService' => (clone $periodQuery)->select('service_type')
                ->selectRaw('COUNT(*) as payments_count, SUM(total_amount) as total_amount, SUM(fee + commission) as earnings_total')
                ->groupBy('service_type')
                ->orderByDesc('total_amount')
                ->get(),
            'billPaymentsByProvider' => (clone $periodQuery)->select('provider')
                ->selectRaw('COUNT(*) as payments_count, SUM(total_amount) as total_amount, SUM(fee + commission) as earnings_total')
                ->groupBy('provider')
                ->orderByDesc('total_amount')
                ->get(),
            'billPaymentsByMethod' => (clone $periodQuery)->select('payment_method')
                ->selectRaw('COUNT(*) as payments_count, SUM(total_amount) as total_amount')
                ->groupBy('payment_method')
                ->orderByDesc('total_amount')
                ->get(),
        ];
    }

    private function customerReport(): array
    {
        return [
            'customerStatusBreakdown' => $this->customerQuery()->select('status')
                ->selectRaw('COUNT(*) as customers_count')
                ->groupBy('status')
                ->orderBy('status')
                ->get(),
            'customerKycBreakdown' => $this->customerQuery()->select('kyc_status')
                ->selectRaw('COUNT(*) as customers_count')
                ->groupBy('kyc_status')
                ->orderBy('kyc_status')
                ->get(),
            'customers' => $this->customerQuery()->withSum('savingsAccounts as savings_balance', 'balance')
                ->withSum(['loans as loan_balance' => fn ($query) => $query->whereIn('status', self::LIVE_LOAN_STATUSES)], 'balance')
                ->latest()
                ->limit(150)
                ->get(),
        ];
    }

    private function financialReport(Carbon $fromDay, Carbon $toDay): array
    {
        $snapshot = $this->financialSnapshot($fromDay, $toDay);

        return [
            'financialMetrics' => [
                'income' => $snapshot['income'],
                'expenses' => $snapshot['expenses'],
                'revenue' => $snapshot['revenue'],
                'profit' => $snapshot['profit'],
                'losses' => $snapshot['losses'],
                'net' => $snapshot['net'],
                'billPaymentVolume' => (float) $this->billPaymentQuery()->whereBetween('created_at', [$fromDay, $toDay])->sum('total_amount'),
                'billPaymentEarnings' => $snapshot['bill_earnings'],
                'loanRevenue' => $snapshot['loan_revenue'],
                'legacyIncome' => $snapshot['legacy_income'],
                'legacyExpenses' => $snapshot['legacy_expenses'],
            ],
            'incomeAccountBreakdown' => $snapshot['income_breakdown'],
            'expenseAccountBreakdown' => $snapshot['expense_breakdown'],
            'financialRevenueBreakdown' => $snapshot['revenue_breakdown'],
            'incomeByCategory' => Income::posted()->whereBetween('received_at', [$fromDay->toDateString(), $toDay->toDateString()])
                ->select('category')
                ->selectRaw('COUNT(*) as entries_count, SUM(amount) as total_amount')
                ->groupBy('category')
                ->orderByDesc('total_amount')
                ->get(),
            'expenseByCategory' => Expense::where('status', Expense::STATUS_APPROVED)->whereBetween('spent_at', [$fromDay->toDateString(), $toDay->toDateString()])
                ->select('category')
                ->selectRaw('COUNT(*) as entries_count, SUM(amount) as total_amount')
                ->groupBy('category')
                ->orderByDesc('total_amount')
                ->get(),
            'billPaymentsByStatus' => $this->billPaymentQuery()->whereBetween('created_at', [$fromDay, $toDay])
                ->select('status')
                ->selectRaw('COUNT(*) as payments_count, SUM(total_amount) as total_amount, SUM(fee + commission) as earnings_total')
                ->groupBy('status')
                ->orderBy('status')
                ->get(),
            'recentIncome' => Income::posted()->whereBetween('received_at', [$fromDay->toDateString(), $toDay->toDateString()])->latest('received_at')->limit(50)->get(),
            'recentExpenses' => Expense::where('status', Expense::STATUS_APPROVED)->whereBetween('spent_at', [$fromDay->toDateString(), $toDay->toDateString()])->latest('spent_at')->limit(50)->get(),
        ];
    }

    private function financialSnapshot(Carbon $fromDay, Carbon $toDay): array
    {
        $incomeBreakdown = $this->ledgerAccountBreakdown('income', $fromDay, $toDay);
        $expenseBreakdown = $this->ledgerAccountBreakdown('expense', $fromDay, $toDay);
        $legacyExpenseRows = $this->legacyExpenseRows($fromDay, $toDay);
        if ($legacyExpenseRows->isNotEmpty()) {
            $expenseBreakdown->push([
                'account_id' => null,
                'code' => 'OLD',
                'name' => 'Older expense records',
                'type' => 'expense',
                'type_label' => 'Expense',
                'entries_count' => $legacyExpenseRows->count(),
                'debit_total' => round($legacyExpenseRows->sum('debit_amount'), 2),
                'credit_total' => round($legacyExpenseRows->sum('credit_amount'), 2),
                'net_amount' => round($legacyExpenseRows->sum('net_amount'), 2),
                'entries' => $legacyExpenseRows->values()->all(),
            ]);
        }

        $legacyIncomeRows = $this->legacyIncomeRows($fromDay, $toDay);
        $legacyLoanRevenueRows = $this->legacyLoanRevenueRows($fromDay, $toDay);
        $billCommissionRows = $this->billCommissionRows($fromDay, $toDay);
        $ledgerIncome = (float) $incomeBreakdown->sum('net_amount');
        $legacyIncome = (float) $legacyIncomeRows->sum('net_amount');
        $legacyExpenses = (float) $legacyExpenseRows->sum('net_amount');
        $legacyLoanRevenue = (float) $legacyLoanRevenueRows->sum('net_amount');
        $billCommission = (float) $billCommissionRows->sum('net_amount');
        $income = $ledgerIncome + $legacyIncome + $legacyLoanRevenue;
        $expenses = (float) $expenseBreakdown->sum('net_amount');
        $revenue = $income + $billCommission;
        $net = $revenue - $expenses;

        return [
            'income' => round($income, 2),
            'expenses' => round($expenses, 2),
            'loan_revenue' => round($legacyLoanRevenue, 2),
            'bill_earnings' => round($billCommission, 2),
            'legacy_income' => round($legacyIncome, 2),
            'legacy_expenses' => round($legacyExpenses, 2),
            'revenue' => round($revenue, 2),
            'profit' => round(max(0, $net), 2),
            'losses' => round(max(0, -$net), 2),
            'net' => round($net, 2),
            'income_breakdown' => $incomeBreakdown,
            'expense_breakdown' => $expenseBreakdown,
            'revenue_breakdown' => $this->revenueBreakdown($incomeBreakdown, $legacyIncomeRows, $legacyLoanRevenueRows, $billCommissionRows),
        ];
    }

    private function legacyIncomeRows(Carbon $fromDay, Carbon $toDay): Collection
    {
        $query = Income::posted()
            ->with('recordedBy')
            ->whereBetween('received_at', [$fromDay->toDateString(), $toDay->toDateString()]);
        $query = $this->withoutLedgerEntries($query, 'incomes', Income::class);

        return $query->latest('received_at')
            ->get()
            ->map(fn (Income $income): array => $this->financialDetailRow(
                app_date($income->received_at),
                $income->reference ?? '-',
                $income->category,
                'Income record',
                0,
                (float) $income->amount,
                (float) $income->amount,
                $income->recordedBy?->name ?? 'System',
            ));
    }

    private function legacyExpenseRows(Carbon $fromDay, Carbon $toDay): Collection
    {
        $query = Expense::where('status', Expense::STATUS_APPROVED)
            ->with('recordedBy')
            ->whereBetween('spent_at', [$fromDay->toDateString(), $toDay->toDateString()]);
        $query = $this->withoutLedgerEntries($query, 'expenses', Expense::class);

        return $query->latest('spent_at')
            ->get()
            ->map(fn (Expense $expense): array => $this->financialDetailRow(
                app_date($expense->spent_at),
                $expense->reference ?? '-',
                $expense->category,
                'Expense record',
                (float) $expense->amount,
                0,
                (float) $expense->amount,
                $expense->recordedBy?->name ?? 'System',
            ));
    }

    private function billCommissionRows(Carbon $fromDay, Carbon $toDay): Collection
    {
        $query = $this->billPaymentQuery()
            ->with('recordedBy')
            ->whereBetween('created_at', [$fromDay, $toDay])
            ->where('status', BillPayment::STATUS_SUCCESSFUL)
            ->whereRaw('(fee + commission) > 0');
        $query = $this->withoutLedgerEntries($query, 'bill_payments', BillPayment::class);

        return $query->latest()
            ->get()
            ->map(function (BillPayment $payment): array {
                $earnings = (float) $payment->fee + (float) $payment->commission;

                return $this->financialDetailRow(
                    app_date($payment->created_at),
                    $payment->reference ?? '-',
                    trim(ucfirst((string) $payment->service_type).' '.$payment->provider),
                    'Bill payment VTU earnings',
                    0,
                    $earnings,
                    $earnings,
                    $payment->recordedBy?->name ?? 'System',
                );
            });
    }

    private function legacyLoanRevenueRows(Carbon $fromDay, Carbon $toDay): Collection
    {
        $query = $this->loanPaymentQuery()
            ->with('receiver')
            ->whereBetween('paid_at', [$fromDay, $toDay]);
        $query = $this->withoutLedgerEntries($query, 'loan_payments', LoanPayment::class);

        return $query->latest('paid_at')
            ->get()
            ->map(function (LoanPayment $payment): array {
                $amount = (float) $payment->interest_component + (float) $payment->fee_component + (float) $payment->penalty_component;

                return $this->financialDetailRow(
                    app_date($payment->paid_at),
                    $payment->reference ?? '-',
                    'Interest: '.number_format((float) $payment->interest_component, 2).', Fee: '.number_format((float) $payment->fee_component, 2).', Penalty: '.number_format((float) $payment->penalty_component, 2),
                    'Loan repayment revenue',
                    0,
                    $amount,
                    $amount,
                    $payment->receiver?->name ?? 'System',
                );
            })
            ->filter(fn (array $row): bool => (float) $row['net_amount'] > 0)
            ->values();
    }

    private function loanPaymentRevenue(Carbon $fromDay, Carbon $toDay, bool $onlyWithoutLedger = false): float
    {
        $query = $this->loanPaymentQuery()->whereBetween('paid_at', [$fromDay, $toDay]);
        if ($onlyWithoutLedger) {
            $query = $this->withoutLedgerEntries($query, 'loan_payments', LoanPayment::class);
        }

        $columns = collect(['interest_component', 'fee_component', 'penalty_component'])
            ->filter(fn (string $column): bool => Schema::hasColumn('loan_payments', $column))
            ->values();

        if ($columns->isEmpty()) {
            return 0.0;
        }

        return (float) (clone $query)
            ->selectRaw($columns->map(fn (string $column): string => 'COALESCE(SUM('.$column.'), 0)')->implode(' + ').' as revenue_total')
            ->value('revenue_total');
    }

    private function ledgerAccountBreakdown(string $accountType, Carbon $fromDay, Carbon $toDay): Collection
    {
        return LedgerEntry::with(['account', 'creator'])
            ->whereBetween('entry_date', [$fromDay->toDateString(), $toDay->toDateString()])
            ->whereHas('account', fn ($query) => $query->where('type', $accountType))
            ->get()
            ->filter(fn (LedgerEntry $entry): bool => $entry->account !== null)
            ->groupBy('accounting_account_id')
            ->map(function (Collection $entries): array {
                $account = $entries->first()->account;
                $debits = (float) $entries->where('type', 'debit')->sum('amount');
                $credits = (float) $entries->where('type', 'credit')->sum('amount');
                $net = (float) $entries->sum(fn (LedgerEntry $entry): float => $this->signedLedgerAmount($account->type, $entry->type, (float) $entry->amount));

                return [
                    'account_id' => $account->id,
                    'code' => $account->code,
                    'name' => $account->name,
                    'type' => $account->type,
                    'type_label' => AccountingAccount::TYPES[$account->type] ?? str($account->type)->headline()->toString(),
                    'entries_count' => $entries->count(),
                    'debit_total' => round($debits, 2),
                    'credit_total' => round($credits, 2),
                    'net_amount' => round($net, 2),
                    'entries' => $this->ledgerDetailRows($entries, $account->type)->values()->all(),
                ];
            })
            ->sortByDesc('net_amount')
            ->values();
    }

    private function revenueBreakdown(Collection $incomeBreakdown, Collection $legacyIncomeRows, Collection $legacyLoanRevenueRows, Collection $billCommissionRows): Collection
    {
        $rows = $incomeBreakdown->map(fn (array $row): array => [
            'source' => $row['code'].' - '.$row['name'],
            'entries_count' => $row['entries_count'],
            'amount' => $row['net_amount'],
            'note' => 'Posted ledger income',
            'entries' => $row['entries'] ?? [],
        ])->values();

        if ($legacyIncomeRows->isNotEmpty()) {
            $rows->push([
                'source' => 'Older income records',
                'entries_count' => $legacyIncomeRows->count(),
                'amount' => round($legacyIncomeRows->sum('net_amount'), 2),
                'note' => 'Posted income not yet linked to ledger entries',
                'entries' => $legacyIncomeRows->values()->all(),
            ]);
        }

        if ($legacyLoanRevenueRows->isNotEmpty()) {
            $rows->push([
                'source' => 'Older loan charges',
                'entries_count' => $legacyLoanRevenueRows->count(),
                'amount' => round($legacyLoanRevenueRows->sum('net_amount'), 2),
                'note' => 'Interest, fees, and penalties not yet linked to ledger entries',
                'entries' => $legacyLoanRevenueRows->values()->all(),
            ]);
        }

        if ($billCommissionRows->isNotEmpty()) {
            $rows->push([
                'source' => 'Bill payment VTU earnings',
                'entries_count' => $billCommissionRows->count(),
                'amount' => round($billCommissionRows->sum('net_amount'), 2),
                'note' => 'Successful bill payment fees and markup',
                'entries' => $billCommissionRows->values()->all(),
            ]);
        }

        return $rows;
    }

    private function ledgerDetailRows(Collection $entries, string $accountType): Collection
    {
        return $entries
            ->sortByDesc(fn (LedgerEntry $entry): string => $entry->entry_date?->format('Y-m-d') ?? '')
            ->map(fn (LedgerEntry $entry): array => $this->financialDetailRow(
                app_date($entry->entry_date),
                $entry->reference,
                $entry->description ?: '-',
                $this->ledgerSourceLabel($entry->source_type),
                $entry->type === 'debit' ? (float) $entry->amount : 0,
                $entry->type === 'credit' ? (float) $entry->amount : 0,
                $this->signedLedgerAmount($accountType, $entry->type, (float) $entry->amount),
                $entry->creator?->name ?? 'System',
            ));
    }

    private function financialDetailRow(string $date, ?string $reference, ?string $description, string $source, float $debit, float $credit, float $net, string $postedBy): array
    {
        return [
            'date' => $date,
            'reference' => $reference ?: '-',
            'description' => $description ?: '-',
            'source' => $source,
            'debit_amount' => round($debit, 2),
            'credit_amount' => round($credit, 2),
            'net_amount' => round($net, 2),
            'posted_by' => $postedBy,
        ];
    }

    private function ledgerSourceLabel(?string $sourceType): string
    {
        if (blank($sourceType)) {
            return 'Manual journal';
        }

        return str(class_basename($sourceType))->headline()->toString();
    }

    private function signedLedgerAmount(string $accountType, string $entryType, float $amount): float
    {
        $normalDebit = in_array($accountType, ['asset', 'expense'], true);

        return ($normalDebit && $entryType === 'debit') || (! $normalDebit && $entryType === 'credit')
            ? $amount
            : -$amount;
    }

    private function withoutLedgerEntries($query, string $table, string $sourceClass)
    {
        return $query->whereNotExists(function ($subQuery) use ($table, $sourceClass): void {
            $subQuery->selectRaw('1')
                ->from('ledger_entries')
                ->whereColumn('ledger_entries.source_id', $table.'.id')
                ->whereColumn('ledger_entries.tenant_id', $table.'.tenant_id')
                ->where('ledger_entries.source_type', $sourceClass);
        });
    }

    private function loanStatusBreakdown(): Collection
    {
        return $this->loanQuery()->select('status')
            ->selectRaw('COUNT(*) as loans_count, SUM(principal) as principal_total, SUM(balance) as balance_total')
            ->groupBy('status')
            ->orderBy('status')
            ->get();
    }

    private function overdueSchedulesQuery()
    {
        return $this->scheduleQuery()->whereDate('due_date', '<', today())
            ->whereIn('status', self::UNPAID_SCHEDULE_STATUSES);
    }

    private function nplLoans(int $nplDays): Collection
    {
        $cutoff = today()->subDays($nplDays);

        return $this->scheduleQuery()->with(['loan.customer', 'loan.product'])
            ->whereDate('due_date', '<=', $cutoff)
            ->whereIn('status', self::UNPAID_SCHEDULE_STATUSES)
            ->orderBy('due_date')
            ->limit(500)
            ->get()
            ->groupBy('loan_id')
            ->map(function (Collection $schedules): array {
                $first = $schedules->first();
                $loan = $first?->loan;
                $oldestDue = $schedules->min('due_date');
                $overdueAmount = $schedules->sum(fn ($schedule) => max(0, (float) $schedule->total_due - (float) $schedule->paid_amount));

                return [
                    'loan' => $loan,
                    'oldest_due' => $oldestDue,
                    'days_overdue' => $oldestDue ? Carbon::parse($oldestDue)->diffInDays(today()) : 0,
                    'overdue_amount' => $overdueAmount,
                    'balance' => (float) ($loan?->balance ?? 0),
                ];
            })
            ->sortByDesc('balance')
            ->values();
    }

    private function repaymentPerformance(Carbon $fromDay, Carbon $toDay): float
    {
        $due = $this->scheduleDueAmount($this->scheduleQuery()->whereBetween('due_date', [$fromDay->toDateString(), $toDay->toDateString()]));
        $paid = $this->schedulePaidAmount($this->scheduleQuery()->whereBetween('due_date', [$fromDay->toDateString(), $toDay->toDateString()]));

        return $due > 0 ? round(($paid / $due) * 100, 2) : 100.0;
    }

    private function scheduleDueAmount($query): float
    {
        return (float) (clone $query)->sum('total_due');
    }

    private function schedulePaidAmount($query): float
    {
        return (float) (clone $query)->sum('paid_amount');
    }

    private function scheduleOutstandingAmount($query): float
    {
        return (float) (clone $query)
            ->selectRaw('COALESCE(SUM(total_due - paid_amount), 0) as outstanding_total')
            ->value('outstanding_total');
    }

    private function customerQuery()
    {
        return Customer::visibleTo(auth()->user());
    }

    private function savingsAccountQuery()
    {
        return SavingsAccount::whereHas('customer', fn ($query) => $query->visibleTo(auth()->user()));
    }

    private function savingsTransactionQuery()
    {
        $query = SavingsTransaction::whereHas('customer', fn ($query) => $query->visibleTo(auth()->user()));

        if (Schema::hasColumn('savings_transactions', 'status')) {
            $query->where('status', 'posted');
        }

        return $query;
    }

    private function withdrawalTransactionQuery()
    {
        return SavingsTransaction::where('savings_transactions.type', 'withdrawal')
            ->whereHas('customer', fn ($query) => $query->visibleTo(auth()->user()));
    }

    private function loanQuery()
    {
        return Loan::whereHas('customer', fn ($query) => $query->visibleTo(auth()->user()));
    }

    private function loanPaymentQuery()
    {
        return LoanPayment::posted()->whereHas('customer', fn ($query) => $query->visibleTo(auth()->user()));
    }

    private function scheduleQuery()
    {
        return LoanRepaymentSchedule::whereHas('loan.customer', fn ($query) => $query->visibleTo(auth()->user()));
    }

    private function billPaymentQuery()
    {
        return BillPayment::where(function ($query): void {
            $query->whereNull('customer_id')
                ->orWhereHas('customer', fn ($customerQuery) => $customerQuery->visibleTo(auth()->user()));
        });
    }
}
