<?php

namespace App\Controllers\View;
use App\Controllers\BaseController;
use App\Models\DiscountUsageModel;
use App\Models\InvoiceModel;
use App\Models\PaymentModel;
use App\Models\RefundModel;
use App\Models\ExpenseModel;
use App\Models\ReimbursementModel;
use App\Models\UserModel;

require_once APPPATH . 'ThirdParty/fpdf/fpdf.php';
use FPDF;

class FinancialController extends BaseController
{   
    private function wantsJson(): bool
    {
        $accept = strtolower((string)($this->request->getHeaderLine('Accept') ?? ''));
        return $this->request->isAJAX() || str_contains($accept, 'application/json');
    }

    private function selectedSchoolYear(): string
    {
        $requested = trim((string) ($this->request->getGet('school_year') ?? ''));
        if ($requested !== '') {
            return $requested;
        }

        return $this->currentSchoolYearName();
    }

    private function financialSchoolYearOptions(?string $selectedYear = null): array
    {
        $schoolYears = [];
        $addYear = static function (mixed $value) use (&$schoolYears): void {
            $year = trim((string) $value);
            if ($year !== '' && ! in_array($year, $schoolYears, true)) {
                $schoolYears[] = $year;
            }
        };

        $addYear($selectedYear);
        $addYear($this->currentSchoolYearName());

        try {
            $db = \Config\Database::connect();

            if ($db->tableExists('school_years')) {
                $rows = $db->table('school_years')
                    ->select('name')
                    ->orderBy('name', 'DESC')
                    ->get()
                    ->getResultArray();
                foreach ($rows as $row) {
                    $addYear($row['name'] ?? '');
                }
            }

            if ($db->tableExists('invoices') && $db->fieldExists('school_year', 'invoices')) {
                $rows = $db->table('invoices')
                    ->select('DISTINCT school_year', false)
                    ->where('school_year IS NOT NULL', null, false)
                    ->orderBy('school_year', 'DESC')
                    ->get()
                    ->getResultArray();
                foreach ($rows as $row) {
                    $addYear($row['school_year'] ?? '');
                }
            }
        } catch (\Throwable $e) {
        }

        return $schoolYears;
    }
    
public function financialReport()
{
    $invoiceModel        = new InvoiceModel();
    $paymentModel        = new PaymentModel();
    $refundModel         = new RefundModel();
    $expenseModel        = new ExpenseModel();
    $reimbursementModel  = new ReimbursementModel();
    $discountModel       = new DiscountUsageModel();

    $dateFrom   = $this->request->getGet('date_from');
    $dateTo     = $this->request->getGet('date_to');
    $schoolYear = $this->selectedSchoolYear();

    // === Apply filters to models (invoice/refund/discount/expense/reimb) ===
    if ($schoolYear) {
        $invoiceModel->where('invoices.school_year', $schoolYear);
        $refundModel->where('school_year', $schoolYear);
        $discountModel->where('school_year', $schoolYear);
        // If these tables have school_year columns:
        $expenseModel->where('school_year', $schoolYear);
        $reimbursementModel->where('school_year', $schoolYear);
        // Payments are filtered separately below to ensure reuse across queries
    }

    // Apply date filters (support from-only and to-only) using domain-appropriate fields
    $hasFrom = !empty($dateFrom);
    $hasTo   = !empty($dateTo);
    if ($hasFrom) {
        // Invoices: prefer issue_date, fallback to created_at
        $invoiceModel->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $dateFrom);
        // Refunds: prefer refunded_at, fallback to created_at
        $refundModel->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
        // Discounts: prefer used_at, fallback to created_at
        $discountModel->where('DATE(COALESCE(used_at, created_at)) >=', $dateFrom);
        // Expenses & Reimbursements: created_at
        $expenseModel->where('DATE(created_at) >=', $dateFrom);
        $reimbursementModel->where('DATE(created_at) >=', $dateFrom);
    }
    if ($hasTo) {
        $invoiceModel->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $dateTo);
        $refundModel->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
        $discountModel->where('DATE(COALESCE(used_at, created_at)) <=', $dateTo);
        $expenseModel->where('DATE(created_at) <=', $dateTo);
        $reimbursementModel->where('DATE(created_at) <=', $dateTo);
    }

    // === Invoices ===
    // Business rule: one active invoice per parent per school year.
    // If legacy data has multiple invoices, show only the latest one to avoid reporting older invoices as "Unpaid"
    // after new charges/payments update the current invoice.
    $db = \Config\Database::connect();
    if (!empty($schoolYear)) {
        $latestSub = $db->table('invoices')
            ->select('parent_id, MAX(id) AS max_id')
            ->where('school_year', $schoolYear)
            ->groupBy('parent_id')
            ->getCompiledSelect();

        $invBuilder = $db->table('invoices')
            ->select("invoices.*, CONCAT(users.firstname, ' ', users.lastname) AS parent_name", false)
            ->join("($latestSub) latest", 'latest.max_id = invoices.id', 'inner', false)
            ->join('users', 'users.id = invoices.parent_id', 'left');

        if ($hasFrom) {
            $invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $dateFrom);
        }
        if ($hasTo) {
            $invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $dateTo);
        }

        $invoices = $invBuilder->get()->getResultArray();
    } else {
        $invoices = $invoiceModel
            ->select("invoices.*, CONCAT(users.firstname, ' ', users.lastname) AS parent_name")
            ->join('users', 'users.id = invoices.parent_id')
            ->findAll();
    }

    $invoiceIds = array_values(array_unique(array_filter(array_map(static function ($inv) {
        $id = (int)($inv['id'] ?? 0);
        return $id > 0 ? $id : null;
    }, $invoices))));

    // Helper to build a fresh, filtered PaymentModel each time (so filters don't get lost between queries)
    $buildPaymentModel = function () use ($schoolYear, $dateFrom, $dateTo) {
        $pm = new PaymentModel();
        if (!empty($dateFrom)) {
            $pm->where('DATE(payment_date) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $pm->where('DATE(payment_date) <=', $dateTo);
        }
        return $pm;
    };
    // Filter out void/failed/chargeback/etc. payments (align with balance logic elsewhere)
    $applyPaymentFilters = function ($qb) {
        try {
            $db = \Config\Database::connect();
            $hasStatus = $db->fieldExists('status', 'payments');
            $hasVoid   = $db->fieldExists('is_void', 'payments');
            $exclude   = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];

            if ($hasStatus) {
                $qb->groupStart()
                    ->whereNotIn('status', $exclude)
                    ->orWhere('status IS NULL', null, false)
                    ->groupEnd();
            }
            if ($hasVoid) {
                $qb->groupStart()
                    ->where('is_void', 0)
                    ->orWhere('is_void IS NULL', null, false)
                    ->groupEnd();
            }
        } catch (\Throwable $e) {
            // If schema checks fail, fall back to unfiltered results.
        }
        return $qb;
    };

    // === Payments aggregated by invoice_id (for the "Paid" column) ===
    $paymentsQuery = $applyPaymentFilters($buildPaymentModel());
    if (!empty($invoiceIds)) {
        $paymentsQuery->whereIn('invoice_id', $invoiceIds);
    } else {
        $paymentsQuery->where('invoice_id', -1);
    }
    $payments = $paymentsQuery
        ->select('invoice_id, SUM(paid_amount) AS paid_amount')
        ->where('invoice_id IS NOT NULL')
        ->groupBy('invoice_id')
        ->findAll();

    // === Per-invoice breakdown by normalized method (cash/credit/check) ===
    // Normalization rules:
    //  - 'cash' => 'cash'
    //  - 'check','cheque' => 'check'
    //  - 'credit','card','credit card','debit','debit card','visa','mastercard' => 'credit'
    //  - everything else => 'other' (not shown per-invoice, still included in grand totals)
    $paymentBreakdownRows = $applyPaymentFilters($buildPaymentModel())
        ->select("
            invoice_id,
            CASE
                WHEN LOWER(TRIM(payment_method)) = 'cash' THEN 'cash'
                WHEN LOWER(TRIM(payment_method)) IN ('check','cheque') THEN 'check'
                WHEN LOWER(TRIM(payment_method)) IN ('credit','card','credit card','debit','debit card','visa','mastercard') THEN 'credit'
                ELSE 'other'
            END AS method,
            SUM(paid_amount) AS amount
        ")
        ->where('invoice_id IS NOT NULL')
        ->whereIn('invoice_id', $invoiceIds ?: [-1])
        ->groupBy('invoice_id, method')
        ->findAll();

    // Map to [invoice_id => ['cash' => x, 'credit' => y, 'check' => z, 'other' => o]]
    $paymentBreakdown = [];
    foreach ($paymentBreakdownRows as $row) {
        $iid = (int)($row['invoice_id'] ?? 0);
        if ($iid <= 0) continue;
        $method = (string)$row['method'];
        $amount = (float)$row['amount'];
        if (!isset($paymentBreakdown[$iid])) $paymentBreakdown[$iid] = [];
        $paymentBreakdown[$iid][$method] = $amount;
    }

    // === Overall totals by method + grand total ===
    $paymentTotalsRow = $applyPaymentFilters($buildPaymentModel())
        ->select("
            SUM(paid_amount) AS total_all,
            SUM(CASE WHEN LOWER(TRIM(payment_method)) = 'cash' THEN paid_amount ELSE 0 END) AS total_cash,
            SUM(CASE WHEN LOWER(TRIM(payment_method)) IN ('check','cheque') THEN paid_amount ELSE 0 END) AS total_check,
            SUM(CASE WHEN LOWER(TRIM(payment_method)) IN ('credit','card','credit card','debit','debit card','visa','mastercard') THEN paid_amount ELSE 0 END) AS total_credit
        ")
        ->where('invoice_id IS NOT NULL')
        ->whereIn('invoice_id', $invoiceIds ?: [-1])
        ->first() ?? [];

    $paymentTotals = [
        'total_all'    => (float)($paymentTotalsRow['total_all']    ?? 0),
        'total_cash'   => (float)($paymentTotalsRow['total_cash']   ?? 0),
        'total_check'  => (float)($paymentTotalsRow['total_check']  ?? 0),
        'total_credit' => (float)($paymentTotalsRow['total_credit'] ?? 0),
    ];

    // === Refunds (grouped) ===
    $refunds = $refundModel
        ->select('invoice_id, school_year, SUM(refund_paid_amount) AS total_refunded')
        ->where('invoice_id IS NOT NULL')
        ->whereIn('status', ['Partial', 'Paid'])
        ->groupBy(['invoice_id', 'school_year'])
        ->findAll();

    // === Discounts (grouped) ===
    $discounts = $discountModel
        ->select('invoice_id, school_year, SUM(discount_amount) AS discount_amount')
        ->where('invoice_id IS NOT NULL')
        ->groupBy(['invoice_id', 'school_year'])
        ->get()
        ->getResultArray();

    // === Expenses ===
    $expenses = $expenseModel
        ->select('category, SUM(amount) AS total_amount')
        ->groupBy('category')
        ->findAll();

    // === Reimbursements ===
    $reimbursements = $reimbursementModel
        ->select('status, SUM(amount) AS total_amount')
        ->groupBy('status')
        ->findAll();

    // --- School year options (for detailed view & JSON) ---
    $schoolYears = $this->financialSchoolYearOptions($schoolYear);
    
    $eventFeesTotal = $this->getEventFeesTotal($schoolYear, $dateFrom, $dateTo);

    // JSON API support
    if ($this->wantsJson() || strtolower((string)($this->request->getGet('format') ?? '')) === 'json') {
      return $this->response->setJSON([
        'ok'               => true,
        'selectedYear'     => $schoolYear,
        'dateFrom'         => $dateFrom,
        'dateTo'           => $dateTo,
            'schoolYears'      => $schoolYears,
            'invoices'         => $invoices,
            'payments'         => $payments,
            'paymentBreakdown' => $paymentBreakdown,
            'paymentTotals'    => $paymentTotals,
        'refunds'          => $refunds,
        'expenses'         => $expenses,
        'reimbursements'   => $reimbursements,
        'discounts'        => $discounts,
        'eventFeesTotal'   => $eventFeesTotal,
        'csrf_token'       => csrf_token(),
        'csrf_hash'        => csrf_hash(),
      ]);
    }

    return view('payment/financial_report', [
        'invoices'         => $invoices,
        'payments'         => $payments,           // for Paid column
        'paymentBreakdown' => $paymentBreakdown,   // per-invoice Cash/Credit/Check
        'paymentTotals'    => $paymentTotals,      // summary totals + grand total
        'refunds'          => $refunds,
        'expenses'         => $expenses,
        'reimbursements'   => $reimbursements,
        'discounts'        => $discounts,
        'eventFeesTotal'   => $eventFeesTotal,
        'selectedYear'     => $schoolYear,
        'schoolYears'      => $schoolYears,
        'dateFrom'         => $dateFrom,
        'dateTo'           => $dateTo,
    ]);
}


    public function financialReportSummary()
    {
        $dateFrom   = $this->request->getGet('date_from');
        $dateTo     = $this->request->getGet('date_to');
        $schoolYear = $this->selectedSchoolYear();

        $data = $this->getFinancialSummary($dateFrom, $dateTo, $schoolYear);

        $data['schoolYears'] = $this->financialSchoolYearOptions((string)($data['schoolYear'] ?? $schoolYear));
        if ($this->wantsJson() || strtolower((string)($this->request->getGet('format') ?? '')) === 'json') {
            return $this->response->setJSON(['ok' => true] + $data + [
                'csrf_token' => csrf_token(),
                'csrf_hash'  => csrf_hash(),
            ]);
        }
        return view('payment/financial_report_summary', $data);
    }

    public function financialReportData()
    {
        $this->response->setHeader('Accept', 'application/json');
        return $this->financialReport();
    }

    public function financialReportSummaryData()
    {
        $this->response->setHeader('Accept', 'application/json');
        return $this->financialReportSummary();
    }

    public function downloadSummaryCsv()
    {
        $dateFrom   = $this->request->getGet('date_from');
        $dateTo     = $this->request->getGet('date_to');
        $schoolYear = $this->request->getGet('school_year');
        $data = $this->getFinancialSummary($dateFrom, $dateTo, $schoolYear);

        $safeYear = preg_replace('/[^A-Za-z0-9_-]+/', '_', (string)($data['schoolYear'] ?? 'summary'));
        $filename = 'financial_report_summary_' . trim($safeYear, '_') . '_' . date('Ymd_His') . '.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $out = fopen('php://output', 'w');

        fputcsv($out, ['Financial Report Summary']);
        fputcsv($out, ['School Year', (string)($data['schoolYear'] ?? '')]);
        fputcsv($out, ['Date From', (string)($data['dateFrom'] ?? '')]);
        fputcsv($out, ['Date To', (string)($data['dateTo'] ?? '')]);
        fputcsv($out, []);
        fputcsv($out, ['Description', 'Amount']);

        $rows = [
            ['Tuition Charges', (float)($data['tuitionCharges'] ?? 0)],
            ['Event Fees', (float)($data['totalEventFees'] ?? 0)],
            ['Prior-Year Tuition Carryover', (float)($data['totalExtraCharges'] ?? 0)],
            ['Gross Charges', (float)($data['grossCharges'] ?? $data['totalCharges'] ?? 0)],
            ['Total Discounts', (float)($data['totalDiscounts'] ?? 0)],
            ['Total Refunds', (float)($data['totalRefunds'] ?? 0)],
            ['Total Expenses', (float)($data['totalExpenses'] ?? 0)],
            ['Total Reimbursements', (float)($data['totalReimbursements'] ?? 0)],
            ['Donation to School (included in expenses)', (float)($data['donationToSchool'] ?? 0)],
            ['Net Charges After Discounts/Refunds', (float)($data['netAmount'] ?? 0)],
            ['Amount Collected (Paid)', (float)($data['amountCollected'] ?? 0)],
            ['Overpayment Credits', (float)($data['totalOverpaid'] ?? 0)],
            ['Gross Outstanding Before Credits', (float)($data['totalUnpaid'] ?? 0)],
            ['Amount Unpaid (Net of Credits)', (float)($data['netReceivable'] ?? 0)],
        ];

        foreach ($rows as $row) {
            fputcsv($out, $row);
        }

        fputcsv($out, []);
        fputcsv($out, ['Reconciliation']);
        fputcsv($out, [
            'Collected + Outstanding - Overpayment Credits = Net Charges After Discounts/Refunds',
            round(
                (float)($data['amountCollected'] ?? 0)
                + (float)($data['totalUnpaid'] ?? 0)
                - (float)($data['totalOverpaid'] ?? 0),
                2
            ),
        ]);

        if (!empty($data['overpaymentDetails']) && is_array($data['overpaymentDetails'])) {
            fputcsv($out, []);
            fputcsv($out, ['Overpayment Credit Details']);
            fputcsv($out, ['Parent', 'Invoice #', 'Gross Charges', 'Discounts', 'Refunds', 'Paid', 'Credit Amount', 'Note']);
            foreach ($data['overpaymentDetails'] as $detail) {
                fputcsv($out, [
                    (string)($detail['parent_name'] ?? ''),
                    (string)($detail['invoice_number'] ?? ''),
                    (float)($detail['gross_amount'] ?? 0),
                    (float)($detail['discount_amount'] ?? 0),
                    (float)($detail['refund_amount'] ?? 0),
                    (float)($detail['paid_amount'] ?? 0),
                    (float)($detail['credit_amount'] ?? 0),
                    (string)($detail['note'] ?? ''),
                ]);
            }
        }

        fclose($out);
        exit;
    }

    public function downloadSummaryAllDetailsCsv()
    {
        $dateFrom   = $this->request->getGet('date_from');
        $dateTo     = $this->request->getGet('date_to');
        $schoolYear = $this->request->getGet('school_year');

        $summary = $this->getFinancialSummary($dateFrom, $dateTo, $schoolYear);
        $sections = $this->getFinancialSummaryDetailSections($dateFrom, $dateTo, $schoolYear);

        $safeYear = preg_replace('/[^A-Za-z0-9_-]+/', '_', (string)($summary['schoolYear'] ?? 'summary'));
        $filename = 'financial_report_all_details_' . trim($safeYear, '_') . '_' . date('Ymd_His') . '.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $out = fopen('php://output', 'w');

        fputcsv($out, ['Financial Report All Details']);
        fputcsv($out, ['School Year', (string)($summary['schoolYear'] ?? '')]);
        fputcsv($out, ['Date From', (string)($summary['dateFrom'] ?? '')]);
        fputcsv($out, ['Date To', (string)($summary['dateTo'] ?? '')]);

        $overview = [
            ['Tuition Charges' => (float)($summary['tuitionCharges'] ?? 0)],
            ['Event Fee Charges' => (float)($summary['totalEventFees'] ?? 0)],
            ['Prior-Year Tuition Carryover' => (float)($summary['totalExtraCharges'] ?? 0)],
            ['Gross Charges' => (float)($summary['grossCharges'] ?? $summary['totalCharges'] ?? 0)],
            ['Total Discounts' => (float)($summary['totalDiscounts'] ?? 0)],
            ['Total Refunds' => (float)($summary['totalRefunds'] ?? 0)],
            ['Total Expenses' => (float)($summary['totalExpenses'] ?? 0)],
            ['Total Reimbursements' => (float)($summary['totalReimbursements'] ?? 0)],
            ['Donation to School (included in expenses)' => (float)($summary['donationToSchool'] ?? 0)],
            ['Net Charges After Discounts/Refunds' => (float)($summary['netAmount'] ?? 0)],
            ['Amount Collected (Paid)' => (float)($summary['amountCollected'] ?? 0)],
            ['Overpayment Credits' => (float)($summary['totalOverpaid'] ?? 0)],
            ['Gross Outstanding Before Credits' => (float)($summary['totalUnpaid'] ?? 0)],
            ['Amount Unpaid (Net of Credits)' => (float)($summary['netReceivable'] ?? 0)],
        ];

        fputcsv($out, []);
        fputcsv($out, ['Summary Overview']);
        fputcsv($out, ['Description', 'Amount']);
        foreach ($overview as $row) {
            $label = array_key_first($row);
            fputcsv($out, [$label, $row[$label]]);
        }

        fputcsv($out, []);
        fputcsv($out, ['Reconciliation']);
        fputcsv($out, [
            'Collected + Gross Outstanding - Overpayment Credits = Net Charges After Discounts/Refunds',
            round(
                (float)($summary['amountCollected'] ?? 0)
                + (float)($summary['totalUnpaid'] ?? 0)
                - (float)($summary['totalOverpaid'] ?? 0),
                2
            ),
        ]);

        $orderedSections = [
            'Invoices' => $sections['invoices'] ?? [],
            'Discount Details' => $sections['discounts'] ?? [],
            'Payment Details' => $sections['payments'] ?? [],
            'Refund Details' => $sections['refunds'] ?? [],
            'Event Fee Charge Details' => $sections['eventCharges'] ?? [],
            'Prior-Year Tuition Carryover Details' => $sections['carryovers'] ?? [],
            'Parents With Outstanding Balance Details' => $sections['unpaidParents'] ?? [],
            'Expense Details' => $sections['expenses'] ?? [],
            'Reimbursement Details' => $sections['reimbursements'] ?? [],
            'Donation Expense Details' => $sections['donationExpenses'] ?? [],
            'Donation Reimbursement Details' => $sections['donationReimbursements'] ?? [],
            'Reimbursement Batch Fallback Details' => $sections['reimbursementBatchFallbacks'] ?? [],
            'Overpayment Credit Details' => $summary['overpaymentDetails'] ?? [],
        ];

        foreach ($orderedSections as $title => $rows) {
            $this->writeCsvSection($out, $title, $rows);
        }

        fclose($out);
        exit;
    }

    private function writeCsvSection($out, string $title, array $rows): void
    {
        fputcsv($out, []);
        fputcsv($out, [$title]);
        if (empty($rows)) {
            fputcsv($out, ['No rows found']);
            return;
        }

        $headers = array_keys((array)reset($rows));
        fputcsv($out, $headers);
        foreach ($rows as $row) {
            $line = [];
            foreach ($headers as $header) {
                $line[] = $row[$header] ?? '';
            }
            fputcsv($out, $line);
        }
    }

    private function getFinancialSummaryDetailSections(?string $dateFrom = null, ?string $dateTo = null, ?string $schoolYear = null): array
    {
        $configModel = new \App\Models\ConfigurationModel();
        $schoolYear = $schoolYear ?: $this->currentSchoolYearName();

        $derivedDateFrom = null;
        $derivedDateTo   = null;
        if (!empty($schoolYear) && empty($dateFrom) && empty($dateTo)) {
            if (preg_match('/^(\\d{4})[^\\d]?(\\d{4})$/', $schoolYear, $m)) {
                $derivedDateFrom = $m[1] . '-01-01';
                $derivedDateTo   = $m[2] . '-12-31';
            }
        }

        $invoiceDateFrom = $dateFrom ?: $derivedDateFrom;
        $invoiceDateTo   = $dateTo   ?: $derivedDateTo;
        $db = \Config\Database::connect();

        $normalizeYear = static function (?string $year): string {
            return preg_replace('/[^0-9]/', '', (string)$year);
        };
        $normalizedYear = $normalizeYear($schoolYear);
        $paymentExclude = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];
        $hasEventPaid = $db->fieldExists('event_paid', 'event_charges');
        $hasEventPaymentId = $db->fieldExists('event_payment_id', 'event_charges');
        $specialRecipientIds = array_map('intval', array_keys(ReimbursementController::SPECIAL_RECIPIENTS));

        $invoiceRows = $db->table('invoices')
            ->select("invoices.id, invoices.invoice_number, invoices.parent_id, CONCAT(COALESCE(users.firstname, ''), ' ', COALESCE(users.lastname, '')) AS parent_name, invoices.issue_date, invoices.due_date, invoices.total_amount, invoices.status")
            ->join('users', 'users.id = invoices.parent_id', 'left')
            ->where('invoices.school_year', $schoolYear);
        if (!empty($invoiceDateFrom)) {
            $invoiceRows->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $invoiceRows->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $invoiceDateTo);
        }
        $invoiceRows = $invoiceRows->orderBy('invoices.id', 'ASC')->get()->getResultArray();

        $invoiceIds = array_values(array_filter(array_map(static fn($row) => (int)($row['id'] ?? 0), $invoiceRows)));

        $paidByInvoice = [];
        $discountByInvoice = [];
        $refundByInvoice = [];
        if (!empty($invoiceIds)) {
            $paidRows = $db->table('payments')
                ->select('invoice_id, COALESCE(SUM(paid_amount),0) AS total_paid')
                ->whereIn('invoice_id', $invoiceIds)
                ->groupStart()
                    ->whereNotIn('status', $paymentExclude)
                    ->orWhere('status IS NULL', null, false)
                ->groupEnd();
            if (!empty($schoolYear)) {
                $paidRows->where('school_year', $schoolYear);
            }
            if (!empty($invoiceDateFrom)) {
                $paidRows->where('DATE(payment_date) >=', $invoiceDateFrom);
            }
            if (!empty($invoiceDateTo)) {
                $paidRows->where('DATE(payment_date) <=', $invoiceDateTo);
            }
            foreach ($paidRows->groupBy('invoice_id')->get()->getResultArray() as $row) {
                $paidByInvoice[(int)($row['invoice_id'] ?? 0)] = (float)($row['total_paid'] ?? 0);
            }

            $discountRows = $db->table('discount_usages')
                ->select('invoice_id, COALESCE(SUM(discount_amount),0) AS total_discount')
                ->whereIn('invoice_id', $invoiceIds);
            if (!empty($dateFrom)) {
                $discountRows->where('DATE(COALESCE(used_at, created_at)) >=', $dateFrom);
            }
            if (!empty($dateTo)) {
                $discountRows->where('DATE(COALESCE(used_at, created_at)) <=', $dateTo);
            }
            foreach ($discountRows->groupBy('invoice_id')->get()->getResultArray() as $row) {
                $discountByInvoice[(int)($row['invoice_id'] ?? 0)] = (float)($row['total_discount'] ?? 0);
            }

            $refundRows = $db->table('refunds')
                ->select('invoice_id, COALESCE(SUM(refund_paid_amount),0) AS total_refund')
                ->whereIn('invoice_id', $invoiceIds)
                ->whereIn('status', ['Partial', 'Paid']);
            if (!empty($dateFrom)) {
                $refundRows->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
            }
            if (!empty($dateTo)) {
                $refundRows->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
            }
            foreach ($refundRows->groupBy('invoice_id')->get()->getResultArray() as $row) {
                $refundByInvoice[(int)($row['invoice_id'] ?? 0)] = (float)($row['total_refund'] ?? 0);
            }
        }

        $invoices = [];
        foreach ($invoiceRows as $row) {
            $iid = (int)($row['id'] ?? 0);
            $paid = (float)($paidByInvoice[$iid] ?? 0);
            $disc = (float)($discountByInvoice[$iid] ?? 0);
            $refund = (float)($refundByInvoice[$iid] ?? 0);
            $balance = round((float)($row['total_amount'] ?? 0) - $disc - $refund - $paid, 2);
            $invoices[] = [
                'Invoice #' => (string)($row['invoice_number'] ?? ''),
                'Parent' => trim((string)($row['parent_name'] ?? '')),
                'Issue Date' => (string)($row['issue_date'] ?? ''),
                'Due Date' => (string)($row['due_date'] ?? ''),
                'Gross Charges' => (float)($row['total_amount'] ?? 0),
                'Discounts' => $disc,
                'Refunds' => $refund,
                'Paid' => $paid,
                'Balance' => $balance,
                'Status' => (string)($row['status'] ?? ''),
            ];
        }

        $discountDetailsBuilder = $db->table('discount_usages du')
            ->select("du.id, du.invoice_id, du.voucher_id, du.discount_amount, du.used_at, du.created_at, i.invoice_number, CONCAT(COALESCE(u.firstname, ''), ' ', COALESCE(u.lastname, '')) AS parent_name")
            ->join('invoices i', 'i.id = du.invoice_id')
            ->join('users u', 'u.id = i.parent_id', 'left')
            ->where('i.school_year', $schoolYear);
        if (!empty($dateFrom)) {
            $discountDetailsBuilder->where('DATE(COALESCE(du.used_at, du.created_at)) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $discountDetailsBuilder->where('DATE(COALESCE(du.used_at, du.created_at)) <=', $dateTo);
        }
        $discounts = array_map(static function ($row) {
            return [
                'Parent' => trim((string)($row['parent_name'] ?? '')),
                'Invoice #' => (string)($row['invoice_number'] ?? ''),
                'Voucher ID' => (string)($row['voucher_id'] ?? ''),
                'Discount Amount' => (float)($row['discount_amount'] ?? 0),
                'Used At' => (string)($row['used_at'] ?? ''),
                'Created At' => (string)($row['created_at'] ?? ''),
            ];
        }, $discountDetailsBuilder->orderBy('du.id', 'ASC')->get()->getResultArray());

        $paymentDetailsBuilder = $db->table('payments p')
            ->select("p.id, p.invoice_id, p.parent_id, p.paid_amount, p.payment_method, p.payment_date, p.status AS payment_status, p.transaction_id, p.check_number, i.invoice_number, i.balance AS invoice_current_balance, i.status AS invoice_status, i.school_year, CONCAT(COALESCE(u.firstname, ''), ' ', COALESCE(u.lastname, '')) AS parent_name")
            ->join('invoices i', 'i.id = p.invoice_id', 'inner')
            ->join('users u', 'u.id = p.parent_id', 'left')
            ->where('i.school_year', $schoolYear)
            ->groupStart()
                ->whereNotIn('p.status', $paymentExclude)
                ->orWhere('p.status IS NULL', null, false)
            ->groupEnd();
        if (!empty($invoiceDateFrom)) {
            $paymentDetailsBuilder->where('DATE(p.payment_date) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $paymentDetailsBuilder->where('DATE(p.payment_date) <=', $invoiceDateTo);
        }
        $payments = array_map(static function ($row) {
            return [
                'Parent' => trim((string)($row['parent_name'] ?? '')),
                'Invoice #' => (string)($row['invoice_number'] ?? ''),
                'Paid Amount' => (float)($row['paid_amount'] ?? 0),
                'Invoice Balance' => (float)($row['invoice_current_balance'] ?? 0),
                'Payment Method' => (string)($row['payment_method'] ?? ''),
                'Payment Date' => (string)($row['payment_date'] ?? ''),
                'Payment Status' => (string)($row['payment_status'] ?? ''),
                'Invoice Status' => (string)($row['invoice_status'] ?? ''),
                'School Year' => (string)($row['school_year'] ?? ''),
                'Transaction ID' => (string)($row['transaction_id'] ?? ''),
                'Check Number' => (string)($row['check_number'] ?? ''),
            ];
        }, $paymentDetailsBuilder->orderBy('p.id', 'ASC')->get()->getResultArray());

        $refundDetailsBuilder = $db->table('refunds r')
            ->select("r.id, r.invoice_id, r.parent_id, r.refund_paid_amount, r.status, r.request, r.reason, r.refunded_at, r.created_at, i.invoice_number, CONCAT(COALESCE(u.firstname, ''), ' ', COALESCE(u.lastname, '')) AS parent_name")
            ->join('invoices i', 'i.id = r.invoice_id', 'left')
            ->join('users u', 'u.id = r.parent_id', 'left')
            ->where('r.school_year', $schoolYear)
            ->whereIn('r.status', ['Partial', 'Paid']);
        if (!empty($dateFrom)) {
            $refundDetailsBuilder->where('DATE(COALESCE(r.refunded_at, r.created_at)) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $refundDetailsBuilder->where('DATE(COALESCE(r.refunded_at, r.created_at)) <=', $dateTo);
        }
        $refunds = array_map(static function ($row) {
            return [
                'Parent' => trim((string)($row['parent_name'] ?? '')),
                'Invoice #' => (string)($row['invoice_number'] ?? ''),
                'Refund Amount' => (float)($row['refund_paid_amount'] ?? 0),
                'Status' => (string)($row['status'] ?? ''),
                'Request Type' => (string)($row['request'] ?? ''),
                'Reason' => (string)($row['reason'] ?? ''),
                'Refunded At' => (string)($row['refunded_at'] ?? ''),
                'Created At' => (string)($row['created_at'] ?? ''),
            ];
        }, $refundDetailsBuilder->orderBy('r.id', 'ASC')->get()->getResultArray());

        $eventSelect = "ec.id, ec.parent_id, ec.student_id, ec.charged, ec.participation, ec.created_at, e.event_name, CONCAT(COALESCE(u.firstname, ''), ' ', COALESCE(u.lastname, '')) AS parent_name, CONCAT(COALESCE(s.firstname, ''), ' ', COALESCE(s.lastname, '')) AS student_name";
        if ($hasEventPaid) {
            $eventSelect .= ", ec.event_paid";
        }
        if ($hasEventPaymentId) {
            $eventSelect .= ", ec.event_payment_id";
        }
        $eventBuilder = $db->table('event_charges ec')
            ->select($eventSelect, false)
            ->join('events e', 'e.id = ec.event_id', 'left')
            ->join('users u', 'u.id = ec.parent_id', 'left')
            ->join('students s', 's.id = ec.student_id', 'left')
            ->where('ec.school_year', $schoolYear);
        if (!empty($invoiceDateFrom)) {
            $eventBuilder->where('DATE(ec.created_at) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $eventBuilder->where('DATE(ec.created_at) <=', $invoiceDateTo);
        }
        $eventCharges = array_map(static function ($row) use ($hasEventPaid, $hasEventPaymentId) {
            return [
                'Parent' => trim((string)($row['parent_name'] ?? '')),
                'Student' => trim((string)($row['student_name'] ?? '')),
                'Event' => (string)($row['event_name'] ?? ''),
                'Charged Amount' => (float)($row['charged'] ?? 0),
                'Participation' => (string)($row['participation'] ?? ''),
                'Event Paid' => $hasEventPaid ? (string)($row['event_paid'] ?? '') : '',
                'Event Payment ID' => $hasEventPaymentId ? (string)($row['event_payment_id'] ?? '') : '',
                'Created At' => (string)($row['created_at'] ?? ''),
            ];
        }, $eventBuilder->orderBy('ec.id', 'ASC')->get()->getResultArray());

        $carryoverBuilder = $db->table('additional_charges ac')
            ->select("ac.id, ac.parent_id, ac.invoice_id, ac.title, ac.description, ac.amount, ac.status, ac.charge_type, ac.due_date, ac.created_at, i.invoice_number, CONCAT(COALESCE(u.firstname, ''), ' ', COALESCE(u.lastname, '')) AS parent_name")
            ->join('users u', 'u.id = ac.parent_id', 'left')
            ->join('invoices i', 'i.id = ac.invoice_id', 'left')
            ->where('ac.school_year', $schoolYear)
            ->whereNotIn('ac.status', ['void', 'voided']);
        if (!empty($dateFrom)) {
            $carryoverBuilder->where('DATE(ac.due_date) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $carryoverBuilder->where('DATE(ac.due_date) <=', $dateTo);
        }
        $carryovers = array_map(static function ($row) {
            return [
                'Parent' => trim((string)($row['parent_name'] ?? '')),
                'Invoice #' => (string)($row['invoice_number'] ?? ''),
                'Title' => (string)($row['title'] ?? ''),
                'Description' => (string)($row['description'] ?? ''),
                'Amount' => (float)($row['amount'] ?? 0),
                'Charge Type' => (string)($row['charge_type'] ?? ''),
                'Status' => (string)($row['status'] ?? ''),
                'Due Date' => (string)($row['due_date'] ?? ''),
                'Created At' => (string)($row['created_at'] ?? ''),
            ];
        }, $carryoverBuilder->orderBy('ac.id', 'ASC')->get()->getResultArray());

        $expenseBuilder = $db->table('expenses e')
            ->select('e.id, e.category, e.amount, e.description, e.retailor, e.date_of_purchase, e.status, e.school_year, e.semester, e.created_at')
            ->where('e.school_year', $schoolYear);
        if (!empty($invoiceDateFrom)) {
            $expenseBuilder->where('DATE(e.created_at) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $expenseBuilder->where('DATE(e.created_at) <=', $invoiceDateTo);
        }
        $expenseRows = $expenseBuilder->orderBy('e.id', 'ASC')->get()->getResultArray();
        $expenses = array_map(static function ($row) {
            return [
                'Expense ID' => (string)($row['id'] ?? ''),
                'Category' => (string)($row['category'] ?? ''),
                'Amount' => (float)($row['amount'] ?? 0),
                'Description' => (string)($row['description'] ?? ''),
                'Vendor' => (string)($row['retailor'] ?? ''),
                'Date of Purchase' => (string)($row['date_of_purchase'] ?? ''),
                'Status' => (string)($row['status'] ?? ''),
                'Created At' => (string)($row['created_at'] ?? ''),
            ];
        }, $expenseRows);

        $reimbursementBuilder = $db->table('reimbursements r')
            ->select("r.id, r.expense_id, r.amount, r.status, r.description, r.reimbursement_method, r.check_number, r.batch_number, r.created_at, e.category AS expense_category, e.amount AS expense_amount, e.description AS expense_description, CONCAT(COALESCE(rec.firstname, ''), ' ', COALESCE(rec.lastname, '')) AS reimbursed_to_name")
            ->join('expenses e', 'e.id = r.expense_id', 'left')
            ->join('users rec', 'rec.id = r.reimbursed_to', 'left');
        if (!empty($schoolYear)) {
            $reimbursementBuilder
                ->groupStart()
                    ->groupStart()
                        ->where('r.school_year', $schoolYear)
                        ->orWhere(
                            "REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                            $normalizedYear
                        )
                    ->groupEnd()
                    ->orGroupStart()
                        ->where('r.school_year IS NULL', null, false)
                        ->groupStart()
                            ->where('e.school_year', $schoolYear)
                            ->orWhere(
                                "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                                $normalizedYear
                            )
                        ->groupEnd()
                    ->groupEnd()
                ->groupEnd();
        }
        if (!empty($invoiceDateFrom)) {
            $reimbursementBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $reimbursementBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) <=', $invoiceDateTo);
        }
        $reimbursementRows = $reimbursementBuilder->orderBy('r.id', 'ASC')->get()->getResultArray();
        $reimbursements = array_map(static function ($row) {
            return [
                'Reimbursement ID' => (string)($row['id'] ?? ''),
                'Expense ID' => (string)($row['expense_id'] ?? ''),
                'Expense Category' => (string)($row['expense_category'] ?? ''),
                'Expense Amount' => (float)($row['expense_amount'] ?? 0),
                'Reimbursed Amount' => (float)($row['amount'] ?? 0),
                'Reimbursed To' => trim((string)($row['reimbursed_to_name'] ?? '')),
                'Status' => (string)($row['status'] ?? ''),
                'Method' => (string)($row['reimbursement_method'] ?? ''),
                'Check Number' => (string)($row['check_number'] ?? ''),
                'Batch Number' => (string)($row['batch_number'] ?? ''),
                'Description' => (string)($row['description'] ?? ''),
                'Expense Description' => (string)($row['expense_description'] ?? ''),
                'Created At' => (string)($row['created_at'] ?? ''),
            ];
        }, $reimbursementRows);

        $donationExpenses = array_values(array_filter($expenses, static function ($row) {
            return strtolower(trim((string)($row['Category'] ?? ''))) === 'donation';
        }));

        $donationReimbursements = [];
        if (!empty($specialRecipientIds)) {
            $donationBuilder = $db->table('reimbursements r')
                ->select("r.id, r.expense_id, r.amount, r.created_at, r.description, CONCAT(COALESCE(rec.firstname, ''), ' ', COALESCE(rec.lastname, '')) AS reimbursed_to_name, e.category AS expense_category, e.description AS expense_description")
                ->join('expenses e', 'e.id = r.expense_id', 'left')
                ->join('users rec', 'rec.id = r.reimbursed_to', 'left')
                ->whereIn('r.reimbursed_to', $specialRecipientIds);
            if (!empty($schoolYear)) {
                $donationBuilder
                    ->groupStart()
                        ->groupStart()
                            ->where('r.school_year', $schoolYear)
                            ->orWhere(
                                "REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                                $normalizedYear
                            )
                        ->groupEnd()
                        ->orGroupStart()
                            ->where('r.school_year IS NULL', null, false)
                            ->groupStart()
                                ->where('e.school_year', $schoolYear)
                                ->orWhere(
                                    "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                                    $normalizedYear
                                )
                            ->groupEnd()
                        ->groupEnd()
                    ->groupEnd();
            }
            if (!empty($invoiceDateFrom)) {
                $donationBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) >=', $invoiceDateFrom);
            }
            if (!empty($invoiceDateTo)) {
                $donationBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) <=', $invoiceDateTo);
            }
            $donationReimbursements = array_map(static function ($row) {
                return [
                    'Reimbursement ID' => (string)($row['id'] ?? ''),
                    'Expense ID' => (string)($row['expense_id'] ?? ''),
                    'Reimbursed To' => trim((string)($row['reimbursed_to_name'] ?? '')),
                    'Amount' => (float)($row['amount'] ?? 0),
                    'Expense Category' => (string)($row['expense_category'] ?? ''),
                    'Expense Description' => (string)($row['expense_description'] ?? ''),
                    'Description' => (string)($row['description'] ?? ''),
                    'Created At' => (string)($row['created_at'] ?? ''),
                ];
            }, $donationBuilder->orderBy('r.id', 'ASC')->get()->getResultArray());
        }

        $batchFallbackBuilder = $db->table('reimbursement_batch_items bi')
            ->select("bi.batch_id, b.title AS batch_title, b.yearly_batch_number, e.id AS expense_id, e.category, e.amount, e.description, b.closed_at")
            ->join('reimbursement_batches b', 'b.id = bi.batch_id', 'inner')
            ->join('expenses e', 'e.id = bi.expense_id', 'inner')
            ->join('reimbursements r', 'r.expense_id = e.id', 'left')
            ->where('b.status', 'closed')
            ->where('bi.unassigned_at IS NULL', null, false)
            ->where('r.id IS NULL');
        if (!empty($schoolYear)) {
            $batchFallbackBuilder
                ->groupStart()
                    ->where('e.school_year', $schoolYear)
                    ->orWhere(
                        "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                        $normalizedYear
                    )
                ->groupEnd();
        }
        if (!empty($invoiceDateFrom)) {
            $batchFallbackBuilder->where('DATE(COALESCE(e.created_at, b.closed_at)) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $batchFallbackBuilder->where('DATE(COALESCE(e.created_at, b.closed_at)) <=', $invoiceDateTo);
        }
        $reimbursementBatchFallbacks = array_map(static function ($row) {
            $batchNumber = (string)($row['yearly_batch_number'] ?? '');
            if ($batchNumber === '') {
                $batchNumber = (string)($row['batch_title'] ?? '');
            }
            return [
                'Batch ID' => (string)($row['batch_id'] ?? ''),
                'Batch Number' => $batchNumber,
                'Expense ID' => (string)($row['expense_id'] ?? ''),
                'Category' => (string)($row['category'] ?? ''),
                'Amount' => (float)($row['amount'] ?? 0),
                'Description' => (string)($row['description'] ?? ''),
                'Closed At' => (string)($row['closed_at'] ?? ''),
            ];
        }, $batchFallbackBuilder->orderBy('bi.batch_id', 'ASC')->get()->getResultArray());

        return [
            'invoices' => $invoices,
            'discounts' => $discounts,
            'payments' => $payments,
            'refunds' => $refunds,
            'eventCharges' => $eventCharges,
            'carryovers' => $carryovers,
            'unpaidParents' => $this->getUnpaidParentsDetails($schoolYear),
            'expenses' => $expenses,
            'reimbursements' => $reimbursements,
            'donationExpenses' => $donationExpenses,
            'donationReimbursements' => $donationReimbursements,
            'reimbursementBatchFallbacks' => $reimbursementBatchFallbacks,
        ];
    }

    private function getUnpaidParentsDetails(string $schoolYear): array
    {
        if ($schoolYear === '') {
            return [];
        }

        $db = \Config\Database::connect();
        $invRows = $db->table('invoices i')
            ->select('i.id, i.parent_id, i.total_amount, u.firstname, u.lastname, u.email')
            ->join('users u', 'u.id = i.parent_id', 'inner')
            ->where('i.school_year', $schoolYear)
            ->orderBy('i.parent_id', 'ASC')
            ->orderBy('i.id', 'ASC')
            ->get()->getResultArray();

        $byParent = [];
        $paymentsTbl = 'payments';
        $hasStatus   = $db->fieldExists('status', $paymentsTbl);
        $hasVoid     = $db->fieldExists('is_void', $paymentsTbl);

        foreach ($invRows as $r) {
            $iid = (int)($r['id'] ?? 0);
            $pid = (int)($r['parent_id'] ?? 0);
            if ($iid <= 0 || $pid <= 0) {
                continue;
            }

            $qb = $db->table('payments')->select('COALESCE(SUM(paid_amount),0) AS tot')->where('invoice_id', $iid);
            if ($hasStatus) {
                $qb->groupStart()
                    ->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
                    ->orWhere('status IS NULL', null, false)
                   ->groupEnd();
            }
            if ($hasVoid) {
                $qb->groupStart()
                    ->where('is_void', 0)
                    ->orWhere('is_void IS NULL', null, false)
                   ->groupEnd();
            }
            $paidRow = $qb->get()->getRowArray();
            $paidSum = (float)($paidRow['tot'] ?? 0);

            $discRow = $db->table('discount_usages')
                ->select('COALESCE(SUM(discount_amount),0) AS tot')
                ->where('invoice_id', $iid)
                ->get()->getRowArray();
            $discSum = (float)($discRow['tot'] ?? 0);

            $refRow = $db->table('refunds')
                ->select('COALESCE(SUM(refund_paid_amount),0) AS tot')
                ->where('invoice_id', $iid)
                ->whereIn('status', ['Partial','Paid'])
                ->get()->getRowArray();
            $refSum = (float)($refRow['tot'] ?? 0);

            $total   = (float)($r['total_amount'] ?? 0);
            $balance = max(0.0, round($total - $discSum - $paidSum - $refSum, 2));

            if (!isset($byParent[$pid])) {
                $byParent[$pid] = [
                    'parent_id'      => $pid,
                    'firstname'      => (string)($r['firstname'] ?? ''),
                    'lastname'       => (string)($r['lastname'] ?? ''),
                    'email'          => (string)($r['email'] ?? ''),
                    'total_invoice'  => 0.0,
                    'total_balance'  => 0.0,
                    'total_paid'     => 0.0,
                    'total_discount' => 0.0,
                ];
            }
            $byParent[$pid]['total_invoice'] += $total;
            $byParent[$pid]['total_balance'] += $balance;
            $byParent[$pid]['total_paid']    += $paidSum;
            $byParent[$pid]['total_discount']+= $discSum;
        }

        $extraRows = $db->table('additional_charges ac')
            ->select(
                "ac.parent_id,
                 u.firstname,
                 u.lastname,
                 u.email,
                 COALESCE(SUM(ac.amount), 0) AS total_extra",
                false
            )
            ->join('users u', 'u.id = ac.parent_id', 'left')
            ->where('ac.school_year', $schoolYear)
            ->where('ac.status !=', 'void')
            ->groupStart()
                ->where('ac.invoice_id IS NULL', null, false)
                ->orWhere('ac.invoice_id', 0)
                ->orWhere('ac.status !=', 'applied')
            ->groupEnd()
            ->groupBy('ac.parent_id')
            ->get()
            ->getResultArray();

        foreach ($extraRows as $er) {
            $pid = (int)($er['parent_id'] ?? 0);
            if ($pid <= 0) {
                continue;
            }
            $extra = (float)($er['total_extra'] ?? 0);
            if (abs($extra) < 0.00001) {
                continue;
            }
            if (!isset($byParent[$pid])) {
                $byParent[$pid] = [
                    'parent_id'      => $pid,
                    'firstname'      => (string)($er['firstname'] ?? ''),
                    'lastname'       => (string)($er['lastname'] ?? ''),
                    'email'          => (string)($er['email'] ?? ''),
                    'total_invoice'  => 0.0,
                    'total_balance'  => 0.0,
                    'total_paid'     => 0.0,
                    'total_discount' => 0.0,
                ];
            }
            $byParent[$pid]['total_invoice'] += $extra;
            $byParent[$pid]['total_balance'] += $extra;
        }

        $eventFeesPerParent = [];
        try {
            $eventFeesRows = $db->table('event_charges ec')
                ->select('ec.parent_id, COALESCE(SUM(ec.charged),0) AS event_fees', false)
                ->where('ec.school_year', $schoolYear)
                ->groupBy('ec.parent_id')
                ->get()
                ->getResultArray();
            foreach ($eventFeesRows as $row) {
                $pid = (int)($row['parent_id'] ?? 0);
                if ($pid <= 0) {
                    continue;
                }
                $eventFeesPerParent[$pid] = (float)($row['event_fees'] ?? 0);
            }
        } catch (\Throwable $e) {
        }

        $installmentEndRaw = (string) (new \App\Models\ConfigurationModel())->getConfig('installment_date');
        $tzName = (string) (config('School')->attendance['timezone'] ?? user_timezone());
        $tzNY = new \DateTimeZone($tzName);
        $todayNY = new \DateTimeImmutable('today', $tzNY);
        $endDate = null;
        if ($installmentEndRaw) {
            try {
                $endDate = new \DateTimeImmutable($installmentEndRaw, $tzNY);
            } catch (\Throwable $e) {
                $endDate = null;
            }
        }
        $monthsUntil = function (?\DateTimeImmutable $end) use ($todayNY): int {
            if (!$end) {
                return 0;
            }
            $y = (int)$end->format('Y') - (int)$todayNY->format('Y');
            $m = (int)$end->format('n') - (int)$todayNY->format('n');
            $months = $y * 12 + $m;
            if ((int)$end->format('j') > (int)$todayNY->format('j')) {
                $months += 1;
            }
            return max(0, $months);
        };

        $rows = [];
        foreach ($byParent as $pid => $agg) {
            $balance = (float)($agg['total_balance'] ?? 0);
            if ($balance > 0.00001) {
                $remMonths = $monthsUntil($endDate);
                $remainingInstallments = max(1, $remMonths);
                $instAmount = round($balance / $remainingInstallments, 2);

                $rows[] = [
                    'parent_id'               => $pid,
                    'firstname'               => $agg['firstname'],
                    'lastname'                => $agg['lastname'],
                    'email'                   => $agg['email'],
                    'total_invoice'           => (float)$agg['total_invoice'],
                    'total_balance'           => $balance,
                    'total_discount'          => (float)($agg['total_discount'] ?? 0),
                    'remaining_installments'  => $remainingInstallments,
                    'installment_amount'      => $instAmount,
                ];
            }
        }

        usort($rows, static function ($a, $b) {
            return ($b['total_balance'] <=> $a['total_balance']);
        });

        $tz   = new \DateTimeZone($tzName);
        $now  = new \DateTime('now', $tz);
        $nextInstallmentDt = (clone $now)->modify('first day of next month');
        $nextInstallmentYmd = $nextInstallmentDt->format('Y-m-d');

        $parentIds = array_values(array_unique(array_map(static fn($r) => (int)($r['parent_id'] ?? 0), $rows)));
        $hasPayments = [];
        $paidTotals  = [];
        $paymentCounts = [];
        if (!empty($parentIds)) {
            try {
                $pRows = $db->table('payments')
                    ->select('parent_id, SUM(paid_amount) AS total_paid, COUNT(*) AS payment_count')
                    ->whereIn('parent_id', $parentIds)
                    ->where('school_year', $schoolYear)
                    ->groupBy('parent_id')
                    ->get()->getResultArray();
                foreach ($pRows as $pr) {
                    $pid = (int)($pr['parent_id'] ?? 0);
                    if ($pid > 0) {
                        $hasPayments[$pid] = true;
                        $paidTotals[$pid] = (float)($pr['total_paid'] ?? 0);
                        $paymentCounts[$pid] = (int)($pr['payment_count'] ?? 0);
                    }
                }
            } catch (\Throwable $e) {
            }
        }

        return array_map(function(array $r) use ($hasPayments, $paidTotals, $paymentCounts, $nextInstallmentYmd, $eventFeesPerParent) {
            $pid = (int)($r['parent_id'] ?? 0);
            $name = trim((string)($r['firstname'] ?? '') . ' ' . (string)($r['lastname'] ?? ''));
            return [
                'Parent' => $name,
                'Parent ID' => $pid,
                'Email' => (string)($r['email'] ?? ''),
                'Type' => isset($hasPayments[$pid]) ? 'installment' : 'no_payment',
                'Event Fees' => (float)($eventFeesPerParent[$pid] ?? 0),
                'Invoice Amount' => (float)($r['total_invoice'] ?? 0),
                'Applied Discount' => (float)($r['total_discount'] ?? 0),
                'Paid Amount' => isset($paidTotals[$pid]) ? (float)$paidTotals[$pid] : 0.0,
                'Nbr of Installments' => isset($paymentCounts[$pid]) ? (int)$paymentCounts[$pid] : 0,
                'Remaining Installments' => (int)($r['remaining_installments'] ?? 0),
                'Installment Amount' => (float)($r['installment_amount'] ?? 0),
                'Has Installment' => isset($hasPayments[$pid]) ? 1 : 0,
                'Next Installment' => $nextInstallmentYmd,
                'Outstanding Balance' => (float)($r['total_balance'] ?? 0),
            ];
        }, $rows);
    }

        public function downloadCsv()
        {

            $invoiceModel       = new InvoiceModel();
            $refundModel        = new RefundModel();
            $discountModel      = new DiscountUsageModel();
            $expenseModel       = new ExpenseModel();
            $reimbursementModel = new ReimbursementModel();

        $dateFrom   = $this->request->getGet('date_from');
        $dateTo     = $this->request->getGet('date_to');
        $schoolYear = $this->request->getGet('school_year');

        // Invoices with filters (school year + date range on issue_date/created_at)
        $invBuilder = $invoiceModel
            ->select("invoices.*, CONCAT(users.firstname, ' ', users.lastname) AS parent_name")
            ->join('users', 'users.id = invoices.parent_id');
        if (!empty($schoolYear)) {
            $invBuilder->where('invoices.school_year', $schoolYear);
        }
        if (!empty($dateFrom)) {
            $invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $invBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $dateTo);
        }
        $invoices = $invBuilder->findAll();

        $hasFrom = !empty($dateFrom);
        $hasTo   = !empty($dateTo);

        $buildPaymentModel = function () use ($schoolYear, $dateFrom, $dateTo) {
            $pm = new PaymentModel();
            if ($schoolYear) {
                $pm->where('school_year', $schoolYear);
            }
            if (!empty($dateFrom)) {
                $pm->where('DATE(payment_date) >=', $dateFrom);
            }
            if (!empty($dateTo)) {
                $pm->where('DATE(payment_date) <=', $dateTo);
            }
            return $pm;
        };
        $applyPaymentFilters = function ($qb) {
            try {
                $db = \Config\Database::connect();
                $hasStatus = $db->fieldExists('status', 'payments');
                $hasVoid   = $db->fieldExists('is_void', 'payments');
                $exclude   = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];
                if ($hasStatus) {
                    $qb->groupStart()
                        ->whereNotIn('status', $exclude)
                        ->orWhere('status IS NULL', null, false)
                        ->groupEnd();
                }
                if ($hasVoid) {
                    $qb->groupStart()
                        ->where('is_void', 0)
                        ->orWhere('is_void IS NULL', null, false)
                        ->groupEnd();
                }
            } catch (\Throwable $e) {
            }
            return $qb;
        };

        $payments = $applyPaymentFilters($buildPaymentModel())
            ->select('invoice_id, SUM(paid_amount) AS paid_amount')
            ->where('invoice_id IS NOT NULL')
            ->groupBy('invoice_id')
            ->findAll();

        $paymentsMap = [];
        foreach ($payments as $payment) {
            $paymentsMap[(int)($payment['invoice_id'] ?? 0)] = (float)($payment['paid_amount'] ?? 0);
        }

        $paymentBreakdownRows = $applyPaymentFilters($buildPaymentModel())
            ->select("
                invoice_id,
                CASE
                    WHEN LOWER(TRIM(payment_method)) = 'cash' THEN 'cash'
                    WHEN LOWER(TRIM(payment_method)) IN ('check','cheque') THEN 'check'
                    WHEN LOWER(TRIM(payment_method)) IN ('credit','card','credit card','debit','debit card','visa','mastercard') THEN 'credit'
                    ELSE 'other'
                END AS method,
                SUM(paid_amount) AS amount
            ")
            ->where('invoice_id IS NOT NULL')
            ->groupBy('invoice_id, method')
            ->findAll();

        $paymentBreakdown = [];
        foreach ($paymentBreakdownRows as $row) {
            $iid = (int)($row['invoice_id'] ?? 0);
            if ($iid <= 0) continue;
            $method = (string)$row['method'];
            $amount = (float)($row['amount'] ?? 0);
            if (!isset($paymentBreakdown[$iid])) $paymentBreakdown[$iid] = [];
            $paymentBreakdown[$iid][$method] = $amount;
        }

        // Expenses summary with filters
        $expBuilder = $expenseModel
            ->select('category, SUM(amount) AS total_amount');
        if (!empty($schoolYear)) {
            $expBuilder->where('school_year', $schoolYear);
        }
        if (!empty($dateFrom)) {
            $expBuilder->where('DATE(created_at) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $expBuilder->where('DATE(created_at) <=', $dateTo);
        }
        $expenses = $expBuilder->groupBy('category')->findAll();

        // Reimbursements summary with filters
        $reimbBuilder = $reimbursementModel
            ->select('status, SUM(amount) AS total_amount');
        if (!empty($schoolYear)) {
            $reimbBuilder->where('school_year', $schoolYear);
        }
        if (!empty($dateFrom)) {
            $reimbBuilder->where('DATE(created_at) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $reimbBuilder->where('DATE(created_at) <=', $dateTo);
        }
        $reimbursements = $reimbBuilder->groupBy('status')->findAll();

        // Refunds (grouped)
        if (!empty($schoolYear)) {
            $refundModel->where('school_year', $schoolYear);
        }
        if ($hasFrom) {
            $refundModel->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
        }
        if ($hasTo) {
            $refundModel->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
        }
        $refundsData = $refundModel
            ->select('invoice_id, SUM(refund_paid_amount) AS total_refunded')
            ->where('invoice_id IS NOT NULL')
            ->whereIn('status', ['Partial', 'Paid'])
            ->groupBy('invoice_id')
            ->findAll();
        $refunds = [];
        foreach ($refundsData as $refund) {
            $refunds[$refund['invoice_id']] = $refund['total_refunded'];
        }

        $discountBuilder = $discountModel;
        if (!empty($schoolYear)) {
            $discountBuilder->where('school_year', $schoolYear);
        }
        if ($hasFrom) {
            $discountBuilder->where('DATE(COALESCE(used_at, created_at)) >=', $dateFrom);
        }
        if ($hasTo) {
            $discountBuilder->where('DATE(COALESCE(used_at, created_at)) <=', $dateTo);
        }
        $discountsData = $discountBuilder
            ->select('invoice_id, SUM(discount_amount) AS discount_amount')
            ->where('invoice_id IS NOT NULL')
            ->groupBy('invoice_id')
            ->get()
            ->getResultArray();

        $discounts = [];
        foreach ($discountsData as $disc) {
            $discounts[$disc['invoice_id']] = $disc['discount_amount'];
        }

        $filename = 'financial_report_' . date('Ymd_His') . '.csv';

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');

        $out = fopen('php://output', 'w');

        // Invoices section
        fputcsv($out, ['Invoice #', 'Parent', 'School Year', 'Total', 'Paid', 'Cash', 'Credit', 'Check', 'Balance', 'Refund', 'Discount', 'Status']);
        $sumTotal = 0.0;
        $sumPaid = 0.0;
        $sumCash = 0.0;
        $sumCredit = 0.0;
        $sumCheck = 0.0;
        $sumBalance = 0.0;
        $sumRefund = 0.0;
        $sumDiscount = 0.0;
        foreach ($invoices as $inv) {
            $invoiceId = (int)($inv['id'] ?? 0);
            $paid      = (float)($paymentsMap[$invoiceId] ?? 0);
            $bd        = $paymentBreakdown[$invoiceId] ?? [];
            $cash      = (float)($bd['cash']   ?? 0);
            $credit    = (float)($bd['credit'] ?? 0);
            $check     = (float)($bd['check']  ?? 0);
            $refunded  = (float)($refunds[$invoiceId] ?? 0);
            $discount  = (float)($discounts[$invoiceId] ?? 0);
            $total     = (float)($inv['total_amount'] ?? 0);
            $balance   = $total - $paid - $discount - $refunded;
            if ($balance < 0) $balance = 0.0;
            $status    = ($balance === 0.0) ? 'Paid' : 'Unpaid';

            fputcsv($out, [
                $inv['invoice_number'],
                $inv['parent_name'] ?? '',
                $inv['school_year'],
                $total,
                $paid,
                $cash,
                $credit,
                $check,
                $balance,
                $refunded,
                $discount,
                $status,
            ]);

            $sumTotal += $total;
            $sumPaid += $paid;
            $sumCash += $cash;
            $sumCredit += $credit;
            $sumCheck += $check;
            $sumBalance += $balance;
            $sumRefund += $refunded;
            $sumDiscount += $discount;
        }
        fputcsv($out, [
            'TOTALS',
            '',
            '',
            $sumTotal,
            $sumPaid,
            $sumCash,
            $sumCredit,
            $sumCheck,
            $sumBalance,
            $sumRefund,
            $sumDiscount,
            '',
        ]);

        // Expenses section
        fputcsv($out, []); // blank line
        fputcsv($out, ['Expenses Summary']);
        fputcsv($out, ['Category', 'Total Amount']);
        foreach ($expenses as $exp) {
            fputcsv($out, [
                $exp['category'],
                $exp['total_amount']
            ]);
        }

        // Reimbursements section
        fputcsv($out, []); // blank line
        fputcsv($out, ['Reimbursements Summary']);
        fputcsv($out, ['Status', 'Total Amount']);
        foreach ($reimbursements as $r) {
            fputcsv($out, [
                $r['status'],
                $r['total_amount']
            ]);
        }

        fclose($out);
        exit;
    }

    public function downloadFinancialReport()
    {
        helper('filesystem');

        $dateFrom   = $this->request->getGet('date_from');
        $dateTo     = $this->request->getGet('date_to');
        $schoolYear = $this->request->getGet('school_year');
        $data = $this->getFinancialSummary($dateFrom, $dateTo, $schoolYear);

        // === Generate charts ===
        $this->generateBarChart($data);
        $this->generatePieChart($data);

        $pdf = new \FPDF();
        $pdf->AddPage();
        $pdf->SetFont('Arial', 'B', 16);
        $pdf->Cell(0, 10, 'Financial Report for School Year: ' . $data['schoolYear'], 0, 1, 'C');

        $pdf->Ln(10);
        $pdf->SetFont('Arial', 'B', 12);
        $pdf->Cell(130, 10, 'Description', 1);
        $pdf->Cell(60, 10, 'Amount (USD)', 1);
        $pdf->Ln();

        $rows = [
            'Total Charges' => $data['totalCharges'],
            'Prior-Year Tuition Carryover' => $data['totalExtraCharges'] ?? 0,
            'Total Discounts' => $data['totalDiscounts'],
            'Total Refunds' => $data['totalRefunds'],
            'Total Expenses' => $data['totalExpenses'],
            'Total Reimbursements' => $data['totalReimbursements'],
            'Donation to School (Masjid/Donation reimbursements)' => $data['donationToSchool'] ?? 0,
            'Net Amount (Earned Income)' => $data['netAmount'],
            'Amount Collected (Paid)' => $data['amountCollected'],
            'Amount Unpaid (Outstanding)' => $data['totalUnpaid'],
        ];

        $pdf->SetFont('Arial', '', 11);
        foreach ($rows as $label => $amount) {
            $pdf->Cell(130, 10, $label, 1);
            $pdf->Cell(60, 10, '$' . number_format($amount, 2), 1);
            $pdf->Ln();
        }

        // === Add Bar Chart ===
        $barChart = WRITEPATH . 'reports/bar_chart.png';
        if (file_exists($barChart)) {
            $pdf->Ln(10);
            $pdf->SetFont('Arial', 'B', 12);
            $pdf->Cell(0, 10, 'Summary Graph', 0, 1);
            $pdf->Image($barChart, null, null, 180);
        }

        // === Add Pie Chart ===
        $pieChart = WRITEPATH . 'reports/pie_chart.png';
        if (file_exists($pieChart)) {
            $pdf->Ln(10);
            $pdf->SetFont('Arial', 'B', 12);
            $pdf->Cell(0, 10, 'Expense Breakdown', 0, 1);
            $pdf->Image($pieChart, null, null, 120);
        }

        // Output file
        $pdf->Output('D', 'Financial_Report_' . $data['schoolYear'] . '.pdf');
        exit;
    }

    private function generateBarChart(array $data)
    {
        $chartData = [
            'type' => 'bar',
            'data' => [
                'labels' => ['Charges', 'Paid', 'Unpaid', 'Discounts', 'Refunds', 'Expenses', 'Reimbursements', 'Net'],
                'datasets' => [[
                    'label' => 'Amount (USD)',
                    'data' => [
                        $data['totalCharges'],
                        $data['amountCollected'],
                        $data['totalUnpaid'],
                        $data['totalDiscounts'],
                        $data['totalRefunds'],
                        $data['totalExpenses'],
                        $data['totalReimbursements'],
                        $data['netAmount'],
                    ],
                    'backgroundColor' => 'rgba(54, 162, 235, 0.6)',
                ]]
            ]
        ];

        $this->writeChartImage('bar_chart.png', $chartData);
    }

    private function generatePieChart(array $data)
    {
        $chartData = [
            'type' => 'pie',
            'data' => [
                'labels' => ['Expenses', 'Reimbursements'],
                'datasets' => [[
                    'data' => [
                        $data['totalExpenses'],
                        $data['totalReimbursements'],
                    ],
                    'backgroundColor' => ['#e74c3c', '#8e44ad']
                ]]
            ]
        ];

        $this->writeChartImage('pie_chart.png', $chartData);
    }

    private function writeChartImage(string $filename, array $chartData): void
    {
        $reportDir = WRITEPATH . 'reports';
        if (!is_dir($reportDir) && !mkdir($reportDir, 0775, true) && !is_dir($reportDir)) {
            log_message('error', 'Unable to create financial report chart directory: {dir}', ['dir' => $reportDir]);
            return;
        }

        $url = 'https://quickchart.io/chart?c=' . urlencode(json_encode($chartData));
        $image = @file_get_contents($url);
        if ($image === false || $image === '') {
            log_message('warning', 'Unable to download financial report chart from QuickChart.');
            return;
        }

        $path = $reportDir . DIRECTORY_SEPARATOR . $filename;
        if (@file_put_contents($path, $image) === false) {
            log_message('error', 'Unable to write financial report chart image: {path}', ['path' => $path]);
        }
    }

    private function getFinancialSummary(?string $dateFrom = null, ?string $dateTo = null, ?string $schoolYear = null): array
    {
        $invoiceModel = new \App\Models\InvoiceModel();
        $paymentModel = new \App\Models\PaymentModel();
        $refundModel = new \App\Models\RefundModel();
        $expenseModel = new \App\Models\ExpenseModel();
        $reimbursementModel = new \App\Models\ReimbursementModel();
        $discountModel = new \App\Models\DiscountUsageModel();
        $configModel = new \App\Models\ConfigurationModel();
        $additionalModel = new \App\Models\AdditionalChargeModel();

        // Allow override via parameter; fallback to configured year
        $schoolYear = $schoolYear ?: $this->currentSchoolYearName();
        $derivedDateFrom = null;
        $derivedDateTo   = null;
        if (!empty($schoolYear) && empty($dateFrom) && empty($dateTo)) {
            if (preg_match('/^(\\d{4})[^\\d]?(\\d{4})$/', $schoolYear, $m)) {
                $derivedDateFrom = $m[1] . '-01-01';
                $derivedDateTo   = $m[2] . '-12-31';
            }
        }

        $db = \Config\Database::connect();

        // === Invoices: Total Charges ===
        $invoiceBuilder = $invoiceModel
            ->select("invoices.*, CONCAT(COALESCE(users.firstname, ''), ' ', COALESCE(users.lastname, '')) AS parent_name")
            ->join('users', 'users.id = invoices.parent_id', 'left')
            ->where('invoices.school_year', $schoolYear);
        $invoiceDateFrom = $dateFrom ?: $derivedDateFrom;
        $invoiceDateTo   = $dateTo   ?: $derivedDateTo;
        if (!empty($invoiceDateFrom)) {
            $invoiceBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $invoiceBuilder->where('DATE(COALESCE(invoices.issue_date, invoices.created_at)) <=', $invoiceDateTo);
        }
        $invoices = $invoiceBuilder->findAll();
        $totalCharges = array_sum(array_column($invoices, 'total_amount'));
        $invoiceIds = array_values(array_unique(array_filter(array_map(static function ($inv) {
            $id = (int)($inv['id'] ?? 0);
            return $id > 0 ? $id : null;
        }, $invoices))));

        // === Additional Charges ===
        $hasExplicitDates = !empty($dateFrom) || !empty($dateTo);
        $signedAdditionalChargeExpr = "CASE
                WHEN LOWER(COALESCE(ac.charge_type, '')) = 'deduct' THEN -ABS(ac.amount)
                ELSE ABS(ac.amount)
            END";
        $sumExpr = "COALESCE(SUM({$signedAdditionalChargeExpr}), 0) AS amount";

        $acAllBuilder = $db->table('additional_charges ac')
            ->select($sumExpr, false)
            ->where('ac.school_year', $schoolYear)
            ->whereNotIn('ac.status', ['void', 'voided']);
        if ($hasExplicitDates) {
            if (!empty($dateFrom)) $acAllBuilder->where('DATE(ac.due_date) >=', $dateFrom);
            if (!empty($dateTo)) $acAllBuilder->where('DATE(ac.due_date) <=', $dateTo);
        }
        $acAllRow = $acAllBuilder->get()->getRowArray();
        $totalExtraCharges = isset($acAllRow['amount']) ? (float) $acAllRow['amount'] : 0.00;

        // Extra charges not reflected in invoice totals (unapplied or missing invoice_id)
        $extraByParent = [];
        $extraChargesUnapplied = 0.0;
        $acExtraBuilder = $db->table('additional_charges ac')
            ->select('ac.parent_id, ' . $sumExpr, false)
            ->where('ac.school_year', $schoolYear)
            ->whereNotIn('ac.status', ['void', 'voided'])
            ->groupStart()
                ->where('ac.invoice_id IS NULL', null, false)
                ->orWhere('ac.invoice_id', 0)
                ->orWhere('ac.status !=', 'applied')
            ->groupEnd()
            ->groupBy('ac.parent_id');
        if ($hasExplicitDates) {
            if (!empty($dateFrom)) $acExtraBuilder->where('DATE(ac.due_date) >=', $dateFrom);
            if (!empty($dateTo)) $acExtraBuilder->where('DATE(ac.due_date) <=', $dateTo);
        }
        $acExtraRows = $acExtraBuilder->get()->getResultArray();
        foreach ($acExtraRows as $row) {
            $pid = (int)($row['parent_id'] ?? 0);
            if ($pid <= 0) continue;
            $amt = (float)($row['amount'] ?? 0);
            if (abs($amt) < 0.00001) continue;
            $extraByParent[$pid] = $amt;
            $extraChargesUnapplied += $amt;
        }

        $totalCharges += $extraChargesUnapplied;

        // === Payments: Total Paid ===
        $paymentBuilder = $paymentModel->where('school_year', $schoolYear);
        if (!empty($invoiceDateFrom)) {
            $paymentBuilder->where('DATE(payment_date) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $paymentBuilder->where('DATE(payment_date) <=', $invoiceDateTo);
        }
        $payHasStatus = $db->fieldExists('status', 'payments');
        $payHasVoid   = $db->fieldExists('is_void', 'payments');
        if ($payHasStatus) {
            $paymentBuilder->groupStart()
                ->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
                ->orWhere('status IS NULL', null, false)
                ->groupEnd();
        }
        if ($payHasVoid) {
            $paymentBuilder->groupStart()
                ->where('is_void', 0)
                ->orWhere('is_void IS NULL', null, false)
                ->groupEnd();
        }
        $paymentResult = $paymentBuilder->selectSum('paid_amount')->get()->getRowArray();
        $totalPaid = isset($paymentResult['paid_amount']) ? (float) $paymentResult['paid_amount'] : 0.00;

        // === Per-invoice paid/discount/refund totals for outstanding balance ===
        $paidByInvoice = [];
        $discountByInvoice = [];
        $refundByInvoice = [];
        if (!empty($invoiceIds)) {
            $paidRows = $db->table('payments')
                ->select('invoice_id, COALESCE(SUM(paid_amount),0) AS total_paid')
                ->whereIn('invoice_id', $invoiceIds);
            if (!empty($schoolYear)) {
                $paidRows->where('school_year', $schoolYear);
            }
            if (!empty($invoiceDateFrom)) {
                $paidRows->where('DATE(payment_date) >=', $invoiceDateFrom);
            }
            if (!empty($invoiceDateTo)) {
                $paidRows->where('DATE(payment_date) <=', $invoiceDateTo);
            }
            if ($payHasStatus) {
                $paidRows->groupStart()
                    ->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
                    ->orWhere('status IS NULL', null, false)
                    ->groupEnd();
            }
            if ($payHasVoid) {
                $paidRows->groupStart()
                    ->where('is_void', 0)
                    ->orWhere('is_void IS NULL', null, false)
                    ->groupEnd();
            }
            $paidRows = $paidRows->groupBy('invoice_id')->get()->getResultArray();
            foreach ($paidRows as $r) {
                $iid = (int)($r['invoice_id'] ?? 0);
                if ($iid > 0) $paidByInvoice[$iid] = (float)($r['total_paid'] ?? 0);
            }

            $discRows = $db->table('discount_usages')
                ->select('invoice_id, COALESCE(SUM(discount_amount),0) AS total_disc')
                ->whereIn('invoice_id', $invoiceIds);
            if (!empty($dateFrom)) {
                $discRows->where('DATE(COALESCE(used_at, created_at)) >=', $dateFrom);
            }
            if (!empty($dateTo)) {
                $discRows->where('DATE(COALESCE(used_at, created_at)) <=', $dateTo);
            }
            $discRows = $discRows->groupBy('invoice_id')->get()->getResultArray();
            foreach ($discRows as $r) {
                $iid = (int)($r['invoice_id'] ?? 0);
                if ($iid > 0) $discountByInvoice[$iid] = (float)($r['total_disc'] ?? 0);
            }

            $refRows = $db->table('refunds')
                ->select('invoice_id, COALESCE(SUM(refund_paid_amount),0) AS total_refund')
                ->whereIn('invoice_id', $invoiceIds)
                ->whereIn('status', ['Partial', 'Paid']);
            if (!empty($dateFrom)) {
                $refRows->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
            }
            if (!empty($dateTo)) {
                $refRows->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
            }
            $refRows = $refRows->groupBy('invoice_id')->get()->getResultArray();
            foreach ($refRows as $r) {
                $iid = (int)($r['invoice_id'] ?? 0);
                if ($iid > 0) $refundByInvoice[$iid] = (float)($r['total_refund'] ?? 0);
            }
        }

        // === Expenses ===
        $expenseBuilder = $expenseModel->where('school_year', $schoolYear);
        if (!empty($invoiceDateFrom)) {
            $expenseBuilder->where('DATE(created_at) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $expenseBuilder->where('DATE(created_at) <=', $invoiceDateTo);
        }
        $expenseResult = $expenseBuilder->selectSum('amount')->get()->getRowArray();
        $totalExpenses = isset($expenseResult['amount']) ? (float) $expenseResult['amount'] : 0.00;

        // === Reimbursements ===
        // Reimbursements: include rows missing school_year by falling back to expense.school_year
        $normalizeYear = static function (?string $year): string {
            return preg_replace('/[^0-9]/', '', (string) $year);
        };
        $normalizedYear = $normalizeYear($schoolYear);

        $reimbBuilder = $db->table('reimbursements r')
            ->select('SUM(r.amount) AS amount')
            ->join('expenses e', 'e.id = r.expense_id', 'left');
        if (!empty($schoolYear)) {
            $reimbBuilder
                ->groupStart()
                    ->groupStart()
                        ->where('r.school_year', $schoolYear)
                        ->orWhere(
                            "REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                            $normalizedYear
                        )
                    ->groupEnd()
                    ->orGroupStart()
                        ->where('r.school_year IS NULL', null, false)
                        ->groupStart()
                            ->where('e.school_year', $schoolYear)
                            ->orWhere(
                                "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                                $normalizedYear
                            )
                        ->groupEnd()
                    ->groupEnd()
                ->groupEnd();
        }
        if (!empty($invoiceDateFrom)) {
            $reimbBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $reimbBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) <=', $invoiceDateTo);
        }
        $reimbursementResult = $reimbBuilder->get()->getRowArray();
        $totalReimbursements = isset($reimbursementResult['amount']) ? (float) $reimbursementResult['amount'] : 0.00;

        // Fallback: include closed batch items that never created a reimbursement record
        $batchFallback = $db->table('reimbursement_batch_items bi')
            ->select('SUM(e.amount) AS amount')
            ->join('reimbursement_batches b', 'b.id = bi.batch_id', 'inner')
            ->join('expenses e', 'e.id = bi.expense_id', 'inner')
            ->join('reimbursements r', 'r.expense_id = e.id', 'left')
            ->where('b.status', 'closed')
            ->where('bi.unassigned_at IS NULL', null, false)
            ->where('r.id IS NULL');
        if (!empty($schoolYear)) {
            $batchFallback
                ->groupStart()
                    ->where('e.school_year', $schoolYear)
                    ->orWhere(
                        "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                        $normalizedYear
                    )
                ->groupEnd();
        }
        if (!empty($invoiceDateFrom)) {
            $batchFallback->where('DATE(COALESCE(e.created_at, b.closed_at)) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $batchFallback->where('DATE(COALESCE(e.created_at, b.closed_at)) <=', $invoiceDateTo);
        }
        $batchFallbackRow = $batchFallback->get()->getRowArray();
        $fallbackAmount = isset($batchFallbackRow['amount']) ? (float) $batchFallbackRow['amount'] : 0.00;
        $totalReimbursements += $fallbackAmount;

        // Donations line: donations captured as expenses (category=Donation) plus legacy Masjid/Donation reimbursements.
        $donationExpense = 0.0;
        $donationExpenseBuilder = (new ExpenseModel())
            ->where('school_year', $schoolYear)
            ->where('category', 'Donation');
        if (!empty($invoiceDateFrom)) {
            $donationExpenseBuilder->where('DATE(created_at) >=', $invoiceDateFrom);
        }
        if (!empty($invoiceDateTo)) {
            $donationExpenseBuilder->where('DATE(created_at) <=', $invoiceDateTo);
        }
        $donationExpenseRow = $donationExpenseBuilder->selectSum('amount')->get()->getRowArray();
        $donationExpense = isset($donationExpenseRow['amount']) ? (float) $donationExpenseRow['amount'] : 0.00;

        $donationReimb = 0.0;
        $specialRecipientIds = array_map('intval', array_keys(ReimbursementController::SPECIAL_RECIPIENTS));
        if (!empty($specialRecipientIds)) {
            $donationBuilder = $db->table('reimbursements r')
                ->select('SUM(r.amount) AS amount')
                ->join('expenses e', 'e.id = r.expense_id', 'left')
                ->whereIn('r.reimbursed_to', $specialRecipientIds);
            if (!empty($schoolYear)) {
                $donationBuilder
                    ->groupStart()
                        ->groupStart()
                            ->where('r.school_year', $schoolYear)
                            ->orWhere(
                                "REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                                $normalizedYear
                            )
                        ->groupEnd()
                        ->orGroupStart()
                            ->where('r.school_year IS NULL', null, false)
                            ->groupStart()
                                ->where('e.school_year', $schoolYear)
                                ->orWhere(
                                    "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '')",
                                    $normalizedYear
                                )
                            ->groupEnd()
                        ->groupEnd()
                    ->groupEnd();
            }
            if (!empty($invoiceDateFrom)) {
                $donationBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) >=', $invoiceDateFrom);
            }
            if (!empty($invoiceDateTo)) {
                $donationBuilder->where('DATE(COALESCE(r.created_at, e.created_at)) <=', $invoiceDateTo);
            }
            $donationRow = $donationBuilder->get()->getRowArray();
            $donationReimb = isset($donationRow['amount']) ? (float) $donationRow['amount'] : 0.00;
        }

        $donationToSchool = $donationExpense + $donationReimb;
        $totalReimbursements = max(0.0, $totalReimbursements - $donationReimb);

        // === Refunds ===
        $refundBuilder = $refundModel
            ->where('school_year', $schoolYear)
            ->whereIn('status', ['Partial', 'Paid'])
            ->where('refund_paid_amount IS NOT NULL');

        if (!empty($dateFrom)) {
            $refundBuilder->where('DATE(COALESCE(refunded_at, created_at)) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $refundBuilder->where('DATE(COALESCE(refunded_at, created_at)) <=', $dateTo);
        }

        $refundResult = $refundBuilder
            ->selectSum('refund_paid_amount')
            ->get()
            ->getRowArray();

        $totalRefunds = isset($refundResult['refund_paid_amount']) ? (float) $refundResult['refund_paid_amount'] : 0.00;

        // === Discounts ===
        $discountBuilder = $discountModel
            ->join('invoices', 'invoices.id = discount_usages.invoice_id')
            ->where('invoices.school_year', $schoolYear);
        if (!empty($dateFrom)) {
            $discountBuilder->where('DATE(COALESCE(discount_usages.used_at, discount_usages.created_at)) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $discountBuilder->where('DATE(COALESCE(discount_usages.used_at, discount_usages.created_at)) <=', $dateTo);
        }
        $discountResult = $discountBuilder->selectSum('discount_amount')->get()->getRowArray();
        $totalDiscounts = isset($discountResult['discount_amount']) ? (float) $discountResult['discount_amount'] : 0.00;

        // === Net, Outstanding & Overpayments ===
        $overpaymentDetails = [];
        $totalUnpaid = 0.0;
        $totalOverpaid = 0.0;
        foreach ($invoices as $inv) {
            $iid = (int)($inv['id'] ?? 0);
            $pid = (int)($inv['parent_id'] ?? 0);
            if ($iid <= 0 || $pid <= 0) continue;
            $total = (float)($inv['total_amount'] ?? 0);
            $paid  = (float)($paidByInvoice[$iid] ?? 0);
            $disc  = (float)($discountByInvoice[$iid] ?? 0);
            $ref   = (float)($refundByInvoice[$iid] ?? 0);
            $rawBal = round($total - $disc - $paid - $ref, 2);
            if ($rawBal > 0.00001) {
                $totalUnpaid += $rawBal;
            } elseif ($rawBal < -0.00001) {
                $credit = abs($rawBal);
                $totalOverpaid += $credit;
                $overpaymentDetails[] = [
                    'type' => 'invoice',
                    'parent_id' => $pid,
                    'parent_name' => trim((string)($inv['parent_name'] ?? '')),
                    'invoice_id' => $iid,
                    'invoice_number' => (string)($inv['invoice_number'] ?? ''),
                    'credit_amount' => $credit,
                    'gross_amount' => $total,
                    'discount_amount' => $disc,
                    'refund_amount' => $ref,
                    'paid_amount' => $paid,
                    'note' => 'Invoice payments/discounts exceed net invoice charges.',
                ];
            }
        }
        foreach ($extraByParent as $pid => $extra) {
            $extra = round((float)$extra, 2);
            if ($extra > 0.00001) {
                $totalUnpaid += $extra;
            } elseif ($extra < -0.00001) {
                $credit = abs($extra);
                $totalOverpaid += $credit;
                $overpaymentDetails[] = [
                    'type' => 'adjustment',
                    'parent_id' => (int)$pid,
                    'parent_name' => '',
                    'invoice_id' => null,
                    'invoice_number' => '',
                    'credit_amount' => $credit,
                    'gross_amount' => 0.0,
                    'discount_amount' => 0.0,
                    'refund_amount' => 0.0,
                    'paid_amount' => 0.0,
                    'note' => 'Negative unapplied carryover adjustment.',
                ];
            }
        }
        $amountCollected = $totalPaid;
        $grossCharges = round((float)$totalCharges, 2);
        $netAmount = round($grossCharges - (float)$totalDiscounts - (float)$totalRefunds, 2);

        $totalEventFees = $this->getEventFeesTotal($schoolYear, $invoiceDateFrom, $invoiceDateTo);
        $tuitionCharges = round($grossCharges - (float)$totalEventFees - (float)$totalExtraCharges, 2);
        $netReceivable = max(0.0, round((float)$totalUnpaid - (float)$totalOverpaid, 2));
        return [
            'schoolYear' => $schoolYear,
            'dateFrom' => $dateFrom,
            'dateTo' => $dateTo,
            'grossCharges' => $grossCharges,
            'tuitionCharges' => $tuitionCharges,
            'totalCharges' => $totalCharges,
            'totalExtraCharges' => $totalExtraCharges,
            'totalDiscounts' => $totalDiscounts,
            'totalRefunds' => $totalRefunds,
            'totalExpenses' => $totalExpenses,
            'totalReimbursements' => $totalReimbursements,
            'donationToSchool' => $donationToSchool,
            'totalPaid' => $totalPaid,
            'amountCollected' => $amountCollected,
            'totalUnpaid' => $totalUnpaid,
            'totalOverpaid' => $totalOverpaid,
            'overpaymentDetails' => $overpaymentDetails,
            'netReceivable' => $netReceivable,
            'netAmount' => $netAmount,
            'totalEventFees' => $totalEventFees,
        ];
    }

    private function getEventFeesTotal(?string $schoolYear, ?string $dateFrom, ?string $dateTo): float
    {
        $db = \Config\Database::connect();
        $builder = $db->table('event_charges ec')
            ->select('COALESCE(SUM(ec.charged),0) AS amount', false);
        if (!empty($schoolYear)) {
            $builder->where('ec.school_year', $schoolYear);
        }
        if (!empty($dateFrom)) {
            $builder->where('DATE(ec.created_at) >=', $dateFrom);
        }
        if (!empty($dateTo)) {
            $builder->where('DATE(ec.created_at) <=', $dateTo);
        }
        $row = $builder->get()->getRowArray();
        return $row ? (float)($row['amount'] ?? 0) : 0.0;
    }


    /**
     * Management page: list parents with outstanding balances (> 0) for a school year.
     * Supports HTML and JSON (Accept: application/json or format=json).
     */
    public function unpaidParents()
    {
        $configModel = new \App\Models\ConfigurationModel();
        $schoolYear = trim((string)($this->request->getGet('school_year') ?? ''));
        if ($schoolYear === '') {
            $schoolYear = $this->currentSchoolYearName((string) ($configModel->getConfig('school_year') ?? date('Y')));
        }

        // Build school year options from invoices
        $schoolYears = [];
        try {
            $db = \Config\Database::connect();
            $rows = $db->table('invoices')
                ->select('DISTINCT school_year', false)
                ->where('school_year IS NOT NULL', null, false)
                ->orderBy('school_year', 'DESC')
                ->get()->getResultArray();
            foreach ($rows as $r) {
                $val = (string)($r['school_year'] ?? '');
                if ($val !== '' && !in_array($val, $schoolYears, true)) $schoolYears[] = $val;
            }
        } catch (\Throwable $e) {}
        if (empty($schoolYears) && !empty($schoolYear)) {
            $schoolYears[] = (string)$schoolYear;
        }

        // Aggregate balances by parent for selected school year
        // IMPORTANT: Compute current balance = total - payments - discounts - refundsPaid
        // rather than trusting invoices.balance which may become stale.
        $db = \Config\Database::connect();
        $invRows = $db->table('invoices i')
            ->select('i.id, i.parent_id, i.total_amount, u.firstname, u.lastname, u.email')
            ->join('users u', 'u.id = i.parent_id', 'inner')
            ->where('i.school_year', $schoolYear)
            ->orderBy('i.parent_id', 'ASC')
            ->orderBy('i.id', 'ASC')
            ->get()->getResultArray();

        // Group by parent and compute balances dynamically
        $byParent = [];
        $paymentsTbl = 'payments';
        $hasStatus   = $db->fieldExists('status', $paymentsTbl);
        $hasVoid     = $db->fieldExists('is_void', $paymentsTbl);

        foreach ($invRows as $r) {
            $iid = (int)($r['id'] ?? 0);
            $pid = (int)($r['parent_id'] ?? 0);
            if ($iid <= 0 || $pid <= 0) continue;

            // Sum payments for this invoice (exclude void/failed if such columns exist)
            $qb = $db->table('payments')->select('COALESCE(SUM(paid_amount),0) AS tot')->where('invoice_id', $iid);
            if ($hasStatus) {
                $qb->groupStart()
                    ->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
                    ->orWhere('status IS NULL', null, false)
                   ->groupEnd();
            }
            if ($hasVoid) {
                $qb->groupStart()
                    ->where('is_void', 0)
                    ->orWhere('is_void IS NULL', null, false)
                   ->groupEnd();
            }
            $paidRow   = $qb->get()->getRowArray();
            $paidSum   = (float)($paidRow['tot'] ?? 0);

            // Sum discounts for this invoice
            $discRow = $db->table('discount_usages')
                ->select('COALESCE(SUM(discount_amount),0) AS tot')
                ->where('invoice_id', $iid)
                ->get()->getRowArray();
            $discSum = (float)($discRow['tot'] ?? 0);

            // Sum refunds PAID for this invoice (Partial/Paid only)
            $refRow = $db->table('refunds')
                ->select('COALESCE(SUM(refund_paid_amount),0) AS tot')
                ->where('invoice_id', $iid)
                ->whereIn('status', ['Partial','Paid'])
                ->get()->getRowArray();
            $refSum = (float)($refRow['tot'] ?? 0);

            $total   = (float)($r['total_amount'] ?? 0);
            $balance = max(0.0, round($total - $discSum - $paidSum - $refSum, 2));

            if (!isset($byParent[$pid])) {
                $byParent[$pid] = [
                    'parent_id'     => $pid,
                    'firstname'     => (string)($r['firstname'] ?? ''),
                    'lastname'      => (string)($r['lastname'] ?? ''),
                    'email'         => (string)($r['email'] ?? ''),
                    'total_invoice' => 0.0,
                    'total_balance' => 0.0,
                    'total_paid'    => 0.0,
                    'total_discount'=> 0.0,
                ];
            }
            $byParent[$pid]['total_invoice'] += $total;
            $byParent[$pid]['total_balance'] += $balance;
            $byParent[$pid]['total_paid']    += $paidSum; // aggregated payments (for type classification)
            $byParent[$pid]['total_discount']+= $discSum;
        }

        // Add extra charges not yet applied to invoices (or missing invoice_id)
        $extraRows = $db->table('additional_charges ac')
            ->select(
                "ac.parent_id,
                 u.firstname,
                 u.lastname,
                 u.email,
                 COALESCE(SUM(ac.amount), 0) AS total_extra",
                false
            )
            ->join('users u', 'u.id = ac.parent_id', 'left')
            ->where('ac.school_year', $schoolYear)
            ->where('ac.status !=', 'void')
            ->groupStart()
                ->where('ac.invoice_id IS NULL', null, false)
                ->orWhere('ac.invoice_id', 0)
                ->orWhere('ac.status !=', 'applied')
            ->groupEnd()
            ->groupBy('ac.parent_id')
            ->get()
            ->getResultArray();

        foreach ($extraRows as $er) {
            $pid = (int)($er['parent_id'] ?? 0);
            if ($pid <= 0) continue;
            $extra = (float)($er['total_extra'] ?? 0);
            if (abs($extra) < 0.00001) continue;
            if (!isset($byParent[$pid])) {
                $byParent[$pid] = [
                    'parent_id'     => $pid,
                    'firstname'     => (string)($er['firstname'] ?? ''),
                    'lastname'      => (string)($er['lastname'] ?? ''),
                    'email'         => (string)($er['email'] ?? ''),
                    'total_invoice' => 0.0,
                    'total_balance' => 0.0,
                    'total_paid'    => 0.0,
                    'total_discount'=> 0.0,
                ];
            }
            $byParent[$pid]['total_invoice'] += $extra;
            $byParent[$pid]['total_balance'] += $extra;
        }

        $eventFeesPerParent = [];
        try {
            $eventFeesRows = $db->table('event_charges ec')
                ->select('ec.parent_id, COALESCE(SUM(ec.charged),0) AS event_fees', false)
                ->where('ec.school_year', $schoolYear)
                ->groupBy('ec.parent_id')
                ->get()
                ->getResultArray();
            foreach ($eventFeesRows as $row) {
                $pid = (int)($row['parent_id'] ?? 0);
                if ($pid <= 0) continue;
                $eventFeesPerParent[$pid] = (float)($row['event_fees'] ?? 0);
            }
        } catch (\Throwable $e) {
            // ignore, fallback to no event fees data
        }

        // Reduce into rows list; only parents with positive balance
        // Also compute remaining installments and suggested monthly amount
        $rows = [];
        // Installment end date from config (same source as manual pay UI)
        $installmentEndRaw = (string) (new \App\Models\ConfigurationModel())->getConfig('installment_date');
        $tzName = (string) (config('School')->attendance['timezone'] ?? user_timezone());
        $tzNY = new \DateTimeZone($tzName);
        $todayNY = new \DateTimeImmutable('today', $tzNY);
        $endDate = null;
        if ($installmentEndRaw) {
            try { $endDate = new \DateTimeImmutable($installmentEndRaw, $tzNY); } catch (\Throwable $e) { $endDate = null; }
        }
        $monthsUntil = function (?\DateTimeImmutable $end) use ($todayNY): int {
            if (!$end) return 0;
            $y = (int)$end->format('Y') - (int)$todayNY->format('Y');
            $m = (int)$end->format('n') - (int)$todayNY->format('n');
            $months = $y * 12 + $m;
            if ((int)$end->format('j') > (int)$todayNY->format('j')) $months += 1;
            return max(0, $months);
        };

        foreach ($byParent as $pid => $agg) {
            $balance = (float)($agg['total_balance'] ?? 0);
            if ($balance > 0.00001) {
                $remMonths = $monthsUntil($endDate);
                $remainingInstallments = max(1, $remMonths);
                $instAmount = round($balance / $remainingInstallments, 2);

                $rows[] = [
                    'parent_id'     => $pid,
                    'firstname'     => $agg['firstname'],
                    'lastname'      => $agg['lastname'],
                    'email'         => $agg['email'],
                    'total_invoice' => (float)$agg['total_invoice'],
                    'total_balance' => $balance,
                    'total_discount'=> (float)($agg['total_discount'] ?? 0),
                    'remaining_installments' => $remainingInstallments,
                    'installment_amount'     => $instAmount,
                ];
            }
        }

        // Order by total_balance desc (to mimic previous builder ordering)
        usort($rows, static function ($a, $b) {
            return ($b['total_balance'] <=> $a['total_balance']);
        });

        // Compute a global next installment date (first day of next month, local time)
        $tz   = new \DateTimeZone($tzName);
        $now  = new \DateTime('now', $tz);
        $nextInstallmentDt = (clone $now)->modify('first day of next month');
        $nextInstallmentYmd = $nextInstallmentDt->format('Y-m-d');

        // Determine whether each parent has any payments for this year (installment vs no_payment)
        $parentIds = array_values(array_unique(array_map(static fn($r) => (int)($r['parent_id'] ?? 0), $rows)));
        $hasPayments = [];
        $paidTotals  = [];
        $paymentCounts = [];
        if (!empty($parentIds)) {
            try {
                $pRows = $db->table('payments')
                    ->select('parent_id, SUM(paid_amount) AS total_paid, COUNT(*) AS payment_count')
                    ->whereIn('parent_id', $parentIds)
                    ->where('school_year', $schoolYear)
                    ->groupBy('parent_id')
                    ->get()->getResultArray();
                foreach ($pRows as $pr) {
                    $pid = (int)($pr['parent_id'] ?? 0);
                    if ($pid > 0) {
                        $hasPayments[$pid] = true;
                        $paidTotals[$pid]  = (float)($pr['total_paid'] ?? 0);
                        $paymentCounts[$pid] = (int)($pr['payment_count'] ?? 0);
                    }
                }
            } catch (\Throwable $e) {
                // ignore; default type will be 'no_payment'
            }
        }

        $dataRows = array_map(function(array $r) use ($hasPayments, $paidTotals, $paymentCounts, $nextInstallmentYmd, $eventFeesPerParent) {
            $pid = (int)($r['parent_id'] ?? 0);
            $name = trim((string)($r['firstname'] ?? '') . ' ' . (string)($r['lastname'] ?? ''));
            return [
                'parent_id'     => $pid,
                'parent_name'   => $name,
                'email'         => (string)($r['email'] ?? ''),
                'total_invoice' => (float)($r['total_invoice'] ?? 0),
                'total_balance' => (float)($r['total_balance'] ?? 0),
                'total_discount'=> (float)($r['total_discount'] ?? 0),
                'remaining_installments' => (int)($r['remaining_installments'] ?? 0),
                'installment_amount'     => (float)($r['installment_amount'] ?? 0),
                'type'          => isset($hasPayments[$pid]) ? 'installment' : 'no_payment',
                'total_paid'    => isset($paidTotals[$pid]) ? (float)$paidTotals[$pid] : 0.0,
                'payment_count' => isset($paymentCounts[$pid]) ? (int)$paymentCounts[$pid] : 0,
                'has_installment'=> isset($hasPayments[$pid]) ? 1 : 0,
                'next_installment' => $nextInstallmentYmd,
                'event_fees'     => (float)($eventFeesPerParent[$pid] ?? 0),
            ];
        }, $rows);

        if ($this->wantsJson() || strtolower((string)($this->request->getGet('format') ?? '')) === 'json') {
            return $this->response->setJSON([
                'ok'          => true,
                'school_year' => $schoolYear,
                'schoolYears' => $schoolYears,
                'results'     => $dataRows,
                'csrf_token'  => csrf_token(),
                'csrf_hash'   => csrf_hash(),
            ]);
        }

        return view('payment/unpaid_parents', [
            'school_year' => $schoolYear,
            'schoolYears' => $schoolYears,
            'rows'        => $dataRows,
        ]);
    }




}
