<?php

namespace App\Controllers\View;

use App\Controllers\BaseController;
use App\Libraries\FinancialAttachmentService;
use App\Libraries\InvoiceLedgerService;
use App\Models\RefundModel;
use App\Models\UserModel;
use App\Models\PaymentModel;
use App\Models\ConfigurationModel;
use App\Models\InvoiceModel;
use App\Models\EnrollmentModel;
use CodeIgniter\Exceptions\PageNotFoundException;

class RefundController extends BaseController
{
    protected RefundModel $refundModel;
    protected UserModel $userModel;
    protected PaymentModel $paymentModel;
    protected ConfigurationModel $configModel;
    protected InvoiceModel $invoiceModel;
    protected EnrollmentModel $enrollmentModel;
    protected InvoiceLedgerService $invoiceLedgerService;
    protected FinancialAttachmentService $financialAttachmentService;
    protected $db;

    // Allowed request types (mapped to your `refunds.request` column)
    private const REQUEST_TYPES = ['tuition','overpayment','duplicate','extra'];
    // Allowed finalization decisions
    private const DECISIONS = ['Approved','Rejected'];

    public function __construct()
    {
        $this->refundModel     = new RefundModel();
        $this->userModel       = new UserModel();
        $this->paymentModel    = new PaymentModel();
        $this->configModel     = new ConfigurationModel();
        $this->invoiceModel    = new InvoiceModel();
        $this->enrollmentModel = new EnrollmentModel();
        $this->invoiceLedgerService = new InvoiceLedgerService();
        $this->financialAttachmentService = new FinancialAttachmentService();
        $this->db              = \Config\Database::connect();
    }

    /** Get current term (school_year, semester) from configuration */
    private function getCurrentTerm(): array
    {
        $rows = $this->configModel
            ->select('config_key, config_value')
            ->whereIn('config_key', ['school_year','semester'])
            ->findAll();

        $map = [];
        foreach ($rows as $r) {
            $map[$r['config_key']] = $r['config_value'];
        }
        return [
            'school_year' => $map['school_year'] ?? date('Y') . '-' . (date('Y') + 1),
            'semester'    => $map['semester']    ?? 'Fall',
        ];
    }

    /**
     * Detect overpayments per parent for current school_year and create/update Pending refunds.
     * When $triggerEvents is true, emits refundPending for newly created rows only.
     *
     * @return array [created_ids => int[], updated_ids => int[]]
     */
    private function recalcOverpayments(bool $triggerEvents = false): array
    {
        $created = [];
        $updated = [];
        $term = $this->getCurrentTerm();
        $year = (string)$term['school_year'];
        $sem  = (string)$term['semester'];

        // Sum payments by parent for the term
        $pm        = $this->paymentModel;
        $payTable  = $pm->table;
        $hasStatus = $this->paymentModel->db->fieldExists('status', $payTable);
        $hasVoid   = $this->paymentModel->db->fieldExists('is_void', $payTable);

        $qbPaid = $pm->select('parent_id, COALESCE(SUM(paid_amount),0) AS total_paid')
            ->where('school_year', $year)
            ->groupBy('parent_id');
        if ($hasStatus) {
            $qbPaid->groupStart()
                ->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
                ->orWhere('status IS NULL', null, false)
            ->groupEnd();
        }
        if ($hasVoid) {
            $qbPaid->groupStart()
                ->where('is_void', 0)
                ->orWhere('is_void IS NULL', null, false)
            ->groupEnd();
        }
        $paidRows = $qbPaid->findAll();

        // Sum invoices (charges) by parent for the term
        $invRows = $this->invoiceModel
            ->select('parent_id, COALESCE(SUM(total_amount),0) AS total_invoiced')
            ->where('school_year', $year)
            ->groupBy('parent_id')
            ->findAll();

        // Sum refunds PAID (cash out) by parent for the term
        $refRows = $this->refundModel
            ->select('parent_id, COALESCE(SUM(refund_paid_amount),0) AS total_refunded')
            ->where('school_year', $year)
            ->whereIn('status', ['Partial','Paid'])
            ->groupBy('parent_id')
            ->findAll();

        $paidMap = [];
        foreach ($paidRows as $r) $paidMap[(int)$r['parent_id']] = (float)($r['total_paid'] ?? 0);
        $invMap  = [];
        foreach ($invRows  as $r) $invMap[(int)$r['parent_id']]  = (float)($r['total_invoiced'] ?? 0);
        $refMap  = [];
        foreach ($refRows  as $r) $refMap[(int)$r['parent_id']]  = (float)($r['total_refunded'] ?? 0);

        $parentIds = array_unique(array_merge(array_keys($paidMap), array_keys($invMap)));

        foreach ($parentIds as $pid) {
            $paid = (float)($paidMap[$pid] ?? 0);
            $invd = (float)($invMap[$pid]  ?? 0);
            $rfnd = (float)($refMap[$pid]  ?? 0);
            $over = round($paid - $invd - $rfnd, 2);
            if ($over > 0.00 + 0.0001) {
                // Parent-level policy: DO NOT create new overpayment rows; only keep existing parent-level
                // overpayment rows in sync if they are still open (Pending/Partial). Prefer per-invoice rows.
                $existing = $this->refundModel
                    ->where('parent_id', $pid)
                    ->where('school_year', $year)
                    ->where('request', 'overpayment')
                    ->orderBy('id', 'DESC')
                    ->first();

                if ($existing && in_array($existing['status'], ['Pending','Partial'], true)) {
                    $this->refundModel->update((int)$existing['id'], [
                        'refund_amount' => $over,
                        'updated_at'    => utc_now(),
                        'updated_by'    => session()->get('user_id'),
                    ]);
                    $updated[] = (int)$existing['id'];
                }
                // else: no parent-level row created
            }
        }

        // ===== Stage 2: Per-invoice overpayment detection (current school year) =====
        try {
            $invRows = $this->invoiceModel
                ->select('id, parent_id, total_amount, school_year')
                ->where('school_year', $year)
                ->findAll();
            if ($invRows) {
                $invoiceIds = array_map(static fn($r) => (int)$r['id'], $invRows);

                // payments by invoice
                $qbInvPaid = $this->paymentModel
                    ->select('invoice_id, COALESCE(SUM(paid_amount),0) AS total_paid')
                    ->whereIn('invoice_id', $invoiceIds)
                    ->groupBy('invoice_id');
                $payTable2  = $this->paymentModel->table;
                $hasStatus2 = $this->paymentModel->db->fieldExists('status', $payTable2);
                $hasVoid2   = $this->paymentModel->db->fieldExists('is_void', $payTable2);
                if ($hasStatus2) {
                    $qbInvPaid->groupStart()
                        ->whereNotIn('status', ['void','voided','refunded','failed','chargeback','declined','reversed','canceled','cancelled'])
                        ->orWhere('status IS NULL', null, false)
                    ->groupEnd();
                }
                if ($hasVoid2) {
                    $qbInvPaid->groupStart()
                        ->where('is_void', 0)
                        ->orWhere('is_void IS NULL', null, false)
                    ->groupEnd();
                }
                $invPaidRows = $qbInvPaid->findAll();

                // discounts by invoice
                $invDiscRows = $this->db->table('discount_usages')
                    ->select('invoice_id, COALESCE(SUM(discount_amount),0) AS total_disc')
                    ->whereIn('invoice_id', $invoiceIds)
                    ->groupBy('invoice_id')
                    ->get()->getResultArray();

                // refunds paid by invoice
                $invRefRows = $this->refundModel
                    ->select('invoice_id, COALESCE(SUM(refund_paid_amount),0) AS total_ref')
                    ->whereIn('invoice_id', $invoiceIds)
                    ->whereIn('status', ['Partial','Paid'])
                    ->groupBy('invoice_id')
                    ->findAll();

                $paidByInv = [];
                foreach ($invPaidRows as $r) { $paidByInv[(int)$r['invoice_id']] = (float)($r['total_paid'] ?? 0); }
                $discByInv = [];
                foreach ($invDiscRows as $r) { $discByInv[(int)$r['invoice_id']] = (float)($r['total_disc'] ?? 0); }
                $refByInv  = [];
                foreach ($invRefRows  as $r) { $refByInv[(int)$r['invoice_id']]  = (float)($r['total_ref']  ?? 0); }

                foreach ($invRows as $inv) {
                    $iid   = (int)$inv['id'];
                    $pid   = (int)$inv['parent_id'];
                    $total = (float)($inv['total_amount'] ?? 0);
                    $paid  = (float)($paidByInv[$iid] ?? 0);
                    $disc  = (float)($discByInv[$iid] ?? 0);
                    $rfnd  = (float)($refByInv[$iid]  ?? 0);

                    $rawBalance = round($total - $disc - $paid - $rfnd, 2);
                    if ($rawBalance < -0.0001) {
                        $over = abs($rawBalance);

                        // If ANY open refund exists for this invoice, update it instead of creating another line
                        $openRow = $this->refundModel
                            ->where('invoice_id', $iid)
                            ->whereIn('status', ['Pending','Approved','Partial'])
                            ->orderBy('id', 'DESC')
                            ->first();
                        if ($openRow) {
                            // Prefer merging into 'overpayment' if present, else update the open row
                            $this->refundModel->update((int)$openRow['id'], [
                                'refund_amount' => $over,
                                'updated_at'    => utc_now(),
                                'updated_by'    => session()->get('user_id'),
                            ]);
                            $updated[] = (int)$openRow['id'];
                            continue;
                        }

                        // If there is an open PARENT-LEVEL overpayment (invoice_id IS NULL), migrate it to this invoice
                        $parentLevelOpen = $this->refundModel
                            ->where('parent_id', $pid)
                            ->where('school_year', $year)
                            ->where('invoice_id', null)
                            ->where('request', 'overpayment')
                            ->whereIn('status', ['Pending','Approved','Partial'])
                            ->orderBy('id', 'DESC')
                            ->first();
                        if ($parentLevelOpen) {
                            $this->refundModel->update((int)$parentLevelOpen['id'], [
                                'invoice_id'    => $iid,
                                'refund_amount' => $over,
                                'updated_at'    => utc_now(),
                                'updated_by'    => session()->get('user_id'),
                            ]);
                            $updated[] = (int)$parentLevelOpen['id'];
                            continue; // merged instead of creating new
                        }

                        // Existing overpayment refund for this invoice?
                        $existing = $this->refundModel
                            ->where('invoice_id', $iid)
                            ->where('request', 'overpayment')
                            ->whereIn('status', ['Pending','Approved','Partial','Paid'])
                            ->orderBy('id', 'DESC')
                            ->first();

                        if (!$existing || $existing['status'] === 'Paid') {
                            $ok = $this->refundModel->insert([
                                'parent_id'          => $pid,
                                'school_year'        => $year,
                                'semester'           => $sem,
                                'invoice_id'         => $iid,
                                'refund_amount'      => $over,
                                'refund_paid_amount' => 0.00,
                                'status'             => 'Pending',
                                'request'            => 'overpayment',
                                'reason'             => 'Auto-detected per-invoice overpayment',
                                'updated_by'         => session()->get('user_id'),
                                'created_at'         => utc_now(),
                                'updated_at'         => utc_now(),
                            ]);
                            if ($ok) {
                                $created[] = (int)$this->refundModel->getInsertID();
                                if ($triggerEvents) {
                                    $user = (new UserModel())->select('id, email, firstname, lastname')->find($pid) ?: [];
                                    $eventData = [
                                        'user_id'   => (int)($user['id'] ?? $pid),
                                        'email'     => $user['email'] ?? null,
                                        'firstname' => $user['firstname'] ?? null,
                                        'lastname'  => $user['lastname'] ?? null,
                                        'amount'    => $over,
                                        'portalLink'=> base_url('/login'),
                                    ];
                                    \CodeIgniter\Events\Events::trigger('refundPending', $eventData, []);
                                }
                            }
                        } else {
                            // Keep Pending/Partial in sync with current overpayment
                            if (in_array($existing['status'], ['Pending','Partial'], true)) {
                                $this->refundModel->update((int)$existing['id'], [
                                    'refund_amount' => $over,
                                    'updated_at'    => utc_now(),
                                    'updated_by'    => session()->get('user_id'),
                                ]);
                                $updated[] = (int)$existing['id'];
                            }
                        }
                    }
                }
            }
        } catch (\Throwable $e) {
            log_message('error', 'recalcOverpayments per-invoice failed: ' . $e->getMessage());
        }

        return ['created_ids' => $created, 'updated_ids' => $updated];
    }

    /**
     * Parent financial summary for *current term* (adjust if you want all-time).
     * Assumes invoices.total_amount and payments.amount (positive=in, negative=out).
     */
    private function getParentFinancialSummary(int $parentId, string $schoolYear, string $semester): array
    {
        // invoices total for term
        $inv = $this->invoiceModel
            ->select('COALESCE(SUM(total_amount),0) AS total_invoiced')
            ->where('parent_id', $parentId)
            ->where('school_year', $schoolYear)
            ->where('semester', $semester)
            ->first();

        // payments in (amount > 0) for term
        $payIn = $this->paymentModel
            ->select('COALESCE(SUM(amount),0) AS paid_in')
            ->where('parent_id', $parentId)
            ->where('school_year', $schoolYear)
            ->where('semester', $semester)
            ->where('amount >', 0)
            ->first();

        // refunds already paid out (your refunds table)
        $refPaid = $this->refundModel
            ->select('COALESCE(SUM(refund_paid_amount),0) AS refunded_cash')
            ->where('parent_id', $parentId)
            ->where('school_year', $schoolYear)
            ->where('semester', $semester)
            ->whereIn('status', ['Partial','Paid'])
            ->first();

        $totalInvoiced   = (float)($inv['total_invoiced'] ?? 0);
        $paidIn          = (float)($payIn['paid_in'] ?? 0);
        $refundedCash    = (float)($refPaid['refunded_cash'] ?? 0);

        // Unapplied balance = money in − invoices − cash refunds out
        $unapplied = $paidIn - $totalInvoiced - $refundedCash;

        return [
            'total_invoiced'    => $totalInvoiced,
            'total_paid_in'     => $paidIn,
            'total_refunded'    => $refundedCash,
            'unapplied_balance' => $unapplied,
        ];
    }

    /** Optional helper if you want a quick API for balances in UI */
    public function parentBalances(int $parentId)
    {
        $t = $this->getCurrentTerm();
        return $this->response->setJSON($this->getParentFinancialSummary($parentId, $t['school_year'], $t['semester']));
    }

    /**
     * Create refund request.
     * Stores source/type in `refunds.request` as: tuition|overpayment|duplicate|extra
     *
     * @param int         $parentId
     * @param float       $amount
     * @param string|null $requestType tuition|overpayment|duplicate|extra
     * @param int|null    $invoiceId   required for tuition; useful for duplicate
     * @param int|null    $paymentId   useful for duplicate
     * @param string|null $reason
     */
    public function requestRefund(
        int $parentId,
        float $amount,
        ?string $requestType = 'tuition',
        ?int $invoiceId = null,
        ?int $paymentId = null,
        ?string $reason = null
    ) {
        $requestType = strtolower((string)$requestType);
        if (!in_array($requestType, self::REQUEST_TYPES, true)) {
            return $this->response->setJSON(['error' => 'Invalid refund request type.']);
        }

        $parent = $this->userModel->find($parentId);
        if (!$parent) {
            return $this->response->setJSON(['error' => 'Parent not found.']);
        }
        if ($amount <= 0) {
            return $this->response->setJSON(['error' => 'Refund amount must be > 0.']);
        }

        $term = $this->getCurrentTerm();
        $schoolYear = $term['school_year'];
        $semester   = $term['semester'];

        // Tuition requires an invoice check
        if ($requestType === 'tuition') {
            if (empty($invoiceId)) {
                return $this->response->setJSON(['error' => 'invoice_id is required for tuition refunds.']);
            }
            $inv = $this->invoiceModel->find($invoiceId);
            if (!$inv || (int)$inv['parent_id'] !== $parentId) {
                return $this->response->setJSON(['error' => 'Invoice not found for parent.']);
            }
            // Optionally cap to eligible amount per your policy.
        }

        // Overpayment/extra must not exceed unapplied balance
        if (in_array($requestType, ['overpayment','extra'], true)) {
            $sum = $this->getParentFinancialSummary($parentId, $schoolYear, $semester);
            if ($sum['unapplied_balance'] <= 0) {
                return $this->response->setJSON(['error' => 'No unapplied balance available.']);
            }
            if ($amount > $sum['unapplied_balance'] + 0.0001) {
                return $this->response->setJSON([
                    'error'     => 'Amount exceeds available unapplied balance.',
                    'available' => number_format($sum['unapplied_balance'], 2)
                ]);
            }
        }

        // Duplicate: optionally tie to payment; otherwise your staff can fill details in reason/note
        if ($requestType === 'duplicate' && empty($paymentId) && empty($invoiceId)) {
            // Not hard-failing; but better to guide:
            // return $this->response->setJSON(['error' => 'Provide payment_id or invoice_id for duplicate refunds.']);
        }

        $payload = [
            'parent_id'          => $parentId,
            'school_year'        => $schoolYear,
            'semester'           => $semester,
            'invoice_id'         => $invoiceId,
            'refund_amount'      => $amount,
            'refund_paid_amount' => 0.00,          // IMPORTANT: your column is NOT NULL
            'status'             => 'Pending',
            'reason'             => $reason,
            'request'            => $requestType,  // <- store the source/type here
            'note'               => $paymentId ? ('duplicate-of-payment#' . $paymentId) : null,
            'created_at'         => utc_now(),
            'updated_at'         => utc_now(),
        ];
        $ok = $this->refundModel->insert($payload);

        if (!$ok) {
            return $this->response->setJSON(['error' => 'Failed to create refund request.']);
        }

        if (!empty($invoiceId)) {
            try {
                $this->invoiceLedgerService->recalculateInvoice((int) $invoiceId);
            } catch (\Throwable $e) {
                log_message('error', 'requestRefund recalc failed: ' . $e->getMessage());
            }
        }

        // Fire refundPending notification/event for newly created refunds
        try {
            $user = $this->userModel->select('id, email, firstname, lastname')->find($parentId) ?: [];
            $eventData = [
                'user_id'   => (int)($user['id'] ?? $parentId),
                'email'     => $user['email'] ?? null,
                'firstname' => $user['firstname'] ?? null,
                'lastname'  => $user['lastname'] ?? null,
                'amount'    => (float)$amount,
                'portalLink'=> base_url('/login'),
            ];
            \CodeIgniter\Events\Events::trigger('refundPending', $eventData, []);
        } catch (\Throwable $e) {
            log_message('error', 'requestRefund: failed to trigger refundPending: ' . $e->getMessage());
        }

        return $this->response->setJSON(['success' => 'Refund requested']);
    }

    // Approve refund (no money movement)
    public function approveRefund(int $refundId)
    {
        $refund = $this->refundModel->find($refundId);
        if (!$refund) {
            return $this->response->setJSON(['error' => 'Refund not found']);
        }

        $this->db->transBegin();

        try {
            if (!empty($refund['invoice_id'])) {
                $this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [(int) $refund['invoice_id']]);
            }

            $ok = $this->refundModel->update($refundId, [
                'status'      => 'Approved',
                'approved_at' => utc_now(),
                'approved_by' => session()->get('user_id'),
                'updated_at'  => utc_now(),
                'updated_by'  => session()->get('user_id'),
            ]);

            if (!empty($refund['invoice_id'])) {
                $this->invoiceLedgerService->recalculateInvoice((int) $refund['invoice_id']);
            }

            $this->db->transCommit();
        } catch (\Throwable $e) {
            $this->db->transRollback();
            log_message('error', 'approveRefund failed: ' . $e->getMessage());
            $ok = false;
        }

        return $this->response->setJSON($ok ? ['success' => 'Refund approved'] : ['error' => 'Approve failed']);
    }

    // Reject refund
    public function rejectRefund(int $refundId)
    {
        $refund = $this->refundModel->find($refundId);
        if (!$refund) {
            return $this->response->setJSON(['error' => 'Refund not found']);
        }

        $this->db->transBegin();

        try {
            if (!empty($refund['invoice_id'])) {
                $this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [(int) $refund['invoice_id']]);
            }

            $ok = $this->refundModel->update($refundId, [
                'status'      => 'Rejected',
                'approved_at' => utc_now(),
                'approved_by' => session()->get('user_id'),
                'updated_at'  => utc_now(),
                'updated_by'  => session()->get('user_id'),
            ]);

            if (!empty($refund['invoice_id'])) {
                $this->invoiceLedgerService->recalculateInvoice((int) $refund['invoice_id']);
            }

            $this->db->transCommit();
        } catch (\Throwable $e) {
            $this->db->transRollback();
            log_message('error', 'rejectRefund failed: ' . $e->getMessage());
            $ok = false;
        }

        return $this->response->setJSON($ok ? ['success' => 'Refund rejected'] : ['error' => 'Reject failed']);
    }

    /**
     * POST: refund_id, paid_amount, payment_method (Check|Online|Cash), check_number, check_file (optional)
     * Marks payout (can be partial) and inserts a negative Payment row to keep balances correct.
     */
    public function updatePayment()
    {
        log_message('debug', 'POST data: ' . print_r($_POST, true));

        $refundId      = (int)$this->request->getPost('refund_id');
        $newPaidAmount = (float)$this->request->getPost('paid_amount');
        $refundMethod  = $this->request->getPost('payment_method'); // Check|Online|Cash
        $checkNbr      = $this->request->getPost('check_number');

        if ($refundId <= 0)    return $this->response->setJSON(['error' => 'Missing refund ID.']);
        if ($newPaidAmount <= 0) return $this->response->setJSON(['error' => 'Valid paid amount is required.']);

        $refund = $this->refundModel->find($refundId);
        if (!$refund) return $this->response->setJSON(['error' => 'Refund not found.']);

        if (!in_array($refund['status'], ['Approved','Partial'], true)) {
            return $this->response->setJSON(['error' => 'Refund must be Approved/Partial to pay.']);
        }

        $oldPaid = (float)$refund['refund_paid_amount'];
        $target  = (float)$refund['refund_amount'];
        $total   = $oldPaid + $newPaidAmount;

        if ($total > $target + 0.0001) {
            return $this->response->setJSON(['error' => 'Total paid cannot exceed refund amount.']);
        }

        // Optional file upload for Check
        $checkFileName = $refund['check_file'] ?? null;
        if ($refundMethod === 'Check') {
            try {
                $checkFile = $this->request->getFile('check_file');
                $uploaded = $this->financialAttachmentService->saveUploadedFile($checkFile, 'checks');
                if ($uploaded !== null) {
                    $checkFileName = $uploaded;
                }
            } catch (\RuntimeException $e) {
                return $this->response->setJSON(['error' => $e->getMessage()]);
            }
        }

        $newStatus = ($total < $target) ? 'Partial' : 'Paid';

        $db = db_connect();
        $db->transBegin();

        // 0) If this refund is not tied to an invoice yet, assign it to the most overpaid invoice
        $assignInvoiceId = null;
        if (empty($refund['invoice_id'])) {
            try {
                // Gather invoices for this parent/year
                $invRows = $this->invoiceModel
                    ->select('id, total_amount')
                    ->where('parent_id', (int)$refund['parent_id'])
                    ->where('school_year', $refund['school_year'])
                    ->findAll();

                if ($invRows) {
                    $invoiceIds = array_map(static fn($r) => (int)$r['id'], $invRows);

                    // Payments per invoice
                    $payRows = $this->paymentModel
                        ->select('invoice_id, COALESCE(SUM(paid_amount),0) AS total_paid')
                        ->whereIn('invoice_id', $invoiceIds)
                        ->groupBy('invoice_id')
                        ->findAll();
                    $paidBy = [];
                    foreach ($payRows as $r) { $paidBy[(int)$r['invoice_id']] = (float)($r['total_paid'] ?? 0); }

                    // Discounts per invoice
                    $discRows = $this->db->table('discount_usages')
                        ->select('invoice_id, COALESCE(SUM(discount_amount),0) AS total_disc')
                        ->whereIn('invoice_id', $invoiceIds)
                        ->groupBy('invoice_id')
                        ->get()->getResultArray();
                    $discBy = [];
                    foreach ($discRows as $r) { $discBy[(int)$r['invoice_id']] = (float)($r['total_disc'] ?? 0); }

                    // Choose invoice with most negative raw (total - disc - paid)
                    $minRaw = 0.0; $minId = null;
                    foreach ($invRows as $ir) {
                        $iid  = (int)$ir['id'];
                        $raw  = (float)($ir['total_amount'] ?? 0) - (float)($discBy[$iid] ?? 0) - (float)($paidBy[$iid] ?? 0);
                        if ($raw < $minRaw) { $minRaw = $raw; $minId = $iid; }
                    }
                    if ($minId !== null) {
                        $assignInvoiceId = $minId;
                    } else {
                        // fallback: latest invoice in this year
                        $row = $this->invoiceModel
                            ->select('id')
                            ->where('parent_id', (int)$refund['parent_id'])
                            ->where('school_year', $refund['school_year'])
                            ->orderBy('created_at', 'DESC')
                            ->first();
                        if ($row && !empty($row['id'])) $assignInvoiceId = (int)$row['id'];
                    }
                }
            } catch (\Throwable $e) {
                log_message('error', 'updatePayment: invoice assignment failed: ' . $e->getMessage());
            }
        }

        try {
            $affectedInvoiceId = (int) ($assignInvoiceId ?? $refund['invoice_id'] ?? 0);
            if ($affectedInvoiceId > 0) {
                $db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [$affectedInvoiceId]);
            }

            $this->refundModel->update($refundId, [
                'refund_paid_amount' => $total,
                'status'             => $newStatus,
                'refunded_at'        => utc_now(),
                'updated_at'         => utc_now(),
                'updated_by'         => session()->get('user_id'),
                'refund_method'      => $refundMethod,
                'check_nbr'          => $checkNbr,
                'check_file'         => $checkFileName,
                'invoice_id'         => $affectedInvoiceId ?: null,
            ]);

            if ($affectedInvoiceId > 0) {
                $this->invoiceLedgerService->recalculateInvoice($affectedInvoiceId);
            }

            $db->transCommit();
        } catch (\Throwable $e) {
            $db->transRollback();
            log_message('error', 'updatePayment failed: ' . $e->getMessage());
            return $this->response->setJSON(['error' => 'Failed to update payment.']);
        }

        return $this->response->setJSON(['success' => 'Payment updated successfully.']);
    }

    /** Keep your listing; added extra fields for clarity */
    public function listRefunds()
    {
        // NOTE: We no longer auto-create/adjust refunds on page load to avoid duplicate lines
        // when staff are recording payouts. Use the "Recalculate" buttons to run detection on demand.

        // 2) List refunds with joins
        $refunds = $this->refundModel
            ->select('refunds.*,
                      u.firstname, u.lastname, u.school_id,
                      a.firstname AS approved_by_firstname,
                      a.lastname  AS approved_by_lastname')
            ->join('users u', 'refunds.parent_id = u.id')
            ->join('users a', 'refunds.approved_by = a.id', 'left')
            ->orderBy('refunds.created_at', 'DESC')
            ->findAll();

        foreach ($refunds as &$r) {
            $r['approved_by_name'] = trim(($r['approved_by_firstname'] ?? '') . ' ' . ($r['approved_by_lastname'] ?? '')) ?: '-';
        }

        $parentId = !empty($refunds) ? $refunds[0]['parent_id'] : null;

        return view('refunds/list', [
            'refunds'  => $refunds,
            'parentId' => $parentId
        ]);
    }

    /** Manual endpoint to recalculate/sync overpayments and optionally notify newly created entries. */
    public function recalculateOverpayments()
    {
        // Optional targeted invoice_number to handle cross-year cases
        $invoiceNumber = (string)($this->request->getPost('invoice_number') ?? '');
        if ($invoiceNumber !== '') {
            try {
                $inv = $this->invoiceModel->where('invoice_number', $invoiceNumber)->first();
                if ($inv) {
                    $pid   = (int)$inv['parent_id'];
                    $iid   = (int)$inv['id'];
                    $year  = (string)$inv['school_year'];

                    // Compute per-invoice overpayment now (independent of current term)
                    $paid = (float)($this->paymentModel
                        ->select('COALESCE(SUM(paid_amount),0) AS tot')
                        ->where('invoice_id', $iid)
                        ->first()['tot'] ?? 0);
                    $disc = (float)($this->db->table('discount_usages')
                        ->select('COALESCE(SUM(discount_amount),0) AS tot')
                        ->where('invoice_id', $iid)
                        ->get()->getRowArray()['tot'] ?? 0);
                    $rfnd = (float)($this->refundModel
                        ->select('COALESCE(SUM(refund_paid_amount),0) AS tot')
                        ->where('invoice_id', $iid)
                        ->whereIn('status', ['Partial','Paid'])
                        ->first()['tot'] ?? 0);
                    $total = (float)($inv['total_amount'] ?? 0);
                    $raw   = round($total - $disc - $paid - $rfnd, 2);
                    if ($raw < -0.0001) {
                        $over = abs($raw);
                        // If there is ANY open refund for this invoice (any request), update it rather than create
                        $openRow = $this->refundModel
                            ->where('invoice_id', $iid)
                            ->whereIn('status', ['Pending','Approved','Partial'])
                            ->orderBy('id', 'DESC')
                            ->first();
                        if ($openRow) {
                            $this->refundModel->update((int)$openRow['id'], [
                                'refund_amount' => $over,
                                'request'       => 'overpayment',
                                'updated_at'    => utc_now(),
                                'updated_by'    => session()->get('user_id'),
                            ]);
                            return redirect()->to(site_url('refunds/list'))->with('success', 'Overpayment updated for invoice ' . esc($invoiceNumber));
                        }

                        // Else, if no open rows exist, either update existing overpayment (Paid) — skip creating new
                        $existing = $this->refundModel
                            ->where('invoice_id', $iid)
                            ->where('request', 'overpayment')
                            ->orderBy('id', 'DESC')
                            ->first();
                        if ($existing) {
                            // If it's Paid, do not create a new duplicate line
                            return redirect()->to(site_url('refunds/list'))->with('info', 'Overpayment was already resolved for this invoice.');
                        }
                        // As a last resort, create a single overpayment row
                        $this->refundModel->insert([
                            'parent_id'          => $pid,
                            'school_year'        => $year,
                            'semester'           => $inv['semester'] ?? null,
                            'invoice_id'         => $iid,
                            'refund_amount'      => $over,
                            'refund_paid_amount' => 0.00,
                            'status'             => 'Pending',
                            'request'            => 'overpayment',
                            'reason'             => 'Auto-detected per-invoice overpayment (manual)',
                            'updated_by'         => session()->get('user_id'),
                            'created_at'         => utc_now(),
                            'updated_at'         => utc_now(),
                        ]);
                        $user = $this->userModel->select('id, email, firstname, lastname')->find($pid) ?: [];
                        $eventData = [
                            'user_id'   => (int)($user['id'] ?? $pid),
                            'email'     => $user['email'] ?? null,
                            'firstname' => $user['firstname'] ?? null,
                            'lastname'  => $user['lastname'] ?? null,
                            'amount'    => $over,
                            'portalLink'=> base_url('/login'),
                        ];
                        \CodeIgniter\Events\Events::trigger('refundPending', $eventData, []);
                        return redirect()->to(site_url('refunds/list'))->with('success', 'Overpayment detected and refund created for invoice ' . esc($invoiceNumber));
                    }
                    return redirect()->to(site_url('refunds/list'))->with('info', 'No overpayment detected for invoice ' . esc($invoiceNumber));
                }
                return redirect()->to(site_url('refunds/list'))->with('error', 'Invoice not found: ' . esc($invoiceNumber));
            } catch (\Throwable $e) {
                return redirect()->to(site_url('refunds/list'))->with('error', 'Failed to recalc for invoice: ' . $e->getMessage());
            }
        }

        $res = $this->recalcOverpayments(true);
        $nNew = count($res['created_ids'] ?? []);
        $nUpd = count($res['updated_ids'] ?? []);
        return redirect()->to(site_url('refunds/list'))
            ->with('success', "Recalculated overpayments: created {$nNew}, updated {$nUpd}.");
    }

    /** Decision endpoint (Approved/Rejected) with reason */
    public function updateStatus()
    {
        log_message('debug', 'POST data: ' . print_r($_POST, true));

        $refundId = (int)$this->request->getPost('refund_id');
        $status   = $this->request->getPost('status');
        $reason   = $this->request->getPost('reason');

        if ($refundId <= 0 || !in_array($status, self::DECISIONS, true)) {
            return $this->response->setJSON(['error' => 'Invalid status update request.']);
        }
        if (empty($reason)) {
            return $this->response->setJSON(['error' => 'Reason is required for approval or rejection.']);
        }

        $refund = $this->refundModel->find($refundId);
        if (!$refund) {
            return $this->response->setJSON(['error' => 'Refund not found.']);
        }

        $this->db->transBegin();

        try {
            if (!empty($refund['invoice_id'])) {
                $this->db->query('SELECT id FROM invoices WHERE id = ? FOR UPDATE', [(int) $refund['invoice_id']]);
            }

            $ok = $this->refundModel->update($refundId, [
                'status'      => $status,
                'reason'      => $reason,
                'approved_at' => utc_now(),
                'approved_by' => session()->get('user_id'),
                'updated_at'  => utc_now(),
                'updated_by'  => session()->get('user_id'),
            ]);

            if (!empty($refund['invoice_id'])) {
                $this->invoiceLedgerService->recalculateInvoice((int) $refund['invoice_id']);
            }

            $this->db->transCommit();
        } catch (\Throwable $e) {
            $this->db->transRollback();
            log_message('error', 'updateStatus failed: ' . $e->getMessage());
            $ok = false;
        }

        return $this->response->setJSON($ok ? ['success' => 'Refund status updated successfully.']
                                            : ['error'   => 'Failed to update refund status.']);
    }

    public function serveRefundFile(int $refundId, string $mode = 'download')
    {
        $refund = $this->refundModel->find($refundId);
        if (!$refund || empty($refund['check_file'])) {
            throw PageNotFoundException::forPageNotFound('Refund file not found.');
        }

        if (!$this->canViewRefund($refund)) {
            return $this->response->setStatusCode(403);
        }

        $path = $this->financialAttachmentService->resolvePath('checks', (string) $refund['check_file']);
        if ($path === null) {
            throw PageNotFoundException::forPageNotFound('Refund file not found.');
        }

        if ($mode === 'inline') {
            return $this->response
                ->setHeader('Content-Type', $this->financialAttachmentService->detectMime($path))
                ->setHeader('Content-Disposition', 'inline; filename="' . basename($path) . '"')
                ->setBody(file_get_contents($path));
        }

        return $this->response->download($path, null);
    }

    private function canViewRefund(array $refund): bool
    {
        $roles = array_map('strtolower', (array) (session()->get('roles') ?? []));
        $activeRole = strtolower((string) (session()->get('role') ?? ''));
        if ($activeRole !== '' && !in_array($activeRole, $roles, true)) {
            $roles[] = $activeRole;
        }

        foreach (['administrator', 'administrative staff', 'principal', 'teacher', 'teacher_assistant'] as $role) {
            if (in_array($role, $roles, true)) {
                return true;
            }
        }

        return in_array('parent', $roles, true) && (int) ($refund['parent_id'] ?? 0) === (int) session()->get('user_id');
    }
}