<?php
/**
 * PaymentRequests — Slice 8Z-s · Slice D2
 *
 * Supplier LPO Payment Request ladder (uses next_check_status, NOT fin_chains — mirrors
 * Works PR1 muscle memory):
 *
 *   DRAFT → SUBMITTED → PC_CONFIRMED → PM_REVIEWED → SCHEDULED → PAID
 *                    ↘ REJECTED  (from any rung; bounce back to DRAFT for edit)
 *                    ↘ CANCELLED (raiser or admin only)
 *
 * Each PR claims against 1+ `lpo_payment_terms` rows (payment_request_lines junction). On raise,
 * increments `lpo_payment_terms.claimed_amount` per line; on cancel/reject, decrements.
 *
 * NET_DAYS-triggered lines require supplier_invoice_no + supplier_invoice_date on the PR — the
 * raise gate validates (invoice_date + net_days) <= today for each net-days line.
 */
class PaymentRequests extends Model
{
    /* Status vocabulary — kept in sync with next_check_status for who's-next-who dispatch. */
    public const STATUS_LIST = ['DRAFT','SUBMITTED','PC_CONFIRMED','PM_REVIEWED','SCHEDULED','PAID','REJECTED','CANCELLED'];

    /* ── Reads ─────────────────────────────────────────────────────────── */

    public function find(int $id): array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT pr.*, l.lpo_no, l.currency, l.amount AS lpo_amount, l.payee_name, l.project_id,
                    p.project_no, p.project_name
               FROM payment_requests pr
               JOIN lpos l           ON l.c0d = pr.lpo_id
          LEFT JOIN c_projects p     ON p.c0d = l.project_id
              WHERE pr.c0d = :id AND pr.deleted = 0",
            [':id' => $id]
        );
        return $r[0] ?? [];
    }

    public function linesFor(int $prId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT prl.*, t.label, t.trigger_type, t.percent, t.computed_amount, t.claimed_amount
               FROM payment_request_lines prl
               JOIN lpo_payment_terms t ON t.c0d = prl.term_id
              WHERE prl.payment_request_id = :p
              ORDER BY prl.c0d",
            [':p' => $prId]
        ) ?: [];
    }

    public function forLpo(int $lpoId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, pr_no, status, requested_amount, currency, raised_by_name, raised_on,
                    pc_confirmed_at, pm_reviewed_at, fm_scheduled_at, acct_recorded_at,
                    rejected_at, rejected_reason, next_check_status, supplier_invoice_no
               FROM payment_requests
              WHERE lpo_id = :l AND deleted = 0
              ORDER BY c0d DESC",
            [':l' => $lpoId]
        ) ?: [];
    }

    /** Slice 8Z-u · Procurement's roll-up: all PRs the current user raised, any status. */
    public function forRaiser(int $userId, string $statusFilter = ''): array
    {
        $params = [':u' => $userId];
        $where = "WHERE pr.deleted = 0 AND pr.raised_by = :u";
        if ($statusFilter !== '') { $where .= " AND pr.status = :s"; $params[':s'] = $statusFilter; }
        $db = new DB();
        return $db->mySelect(
            "SELECT pr.c0d, pr.pr_no, pr.status, pr.next_check_status, pr.requested_amount, pr.currency,
                    pr.raised_on, pr.supplier_invoice_no, pr.acct_disburse_ref, pr.acct_recorded_at,
                    pr.rejected_at, pr.rejected_reason,
                    l.lpo_no, l.payee_name, l.cash_out_status,
                    p.project_no
               FROM payment_requests pr
               JOIN lpos l       ON l.c0d = pr.lpo_id
          LEFT JOIN c_projects p ON p.c0d = l.project_id
               $where
              ORDER BY pr.c0d DESC",
            $params
        ) ?: [];
    }

    /** Ladder queue rows for a role: return PRs where next_check_status matches. */
    public function queueForRung(string $rung): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT pr.c0d, pr.pr_no, pr.status, pr.next_check_status, pr.requested_amount, pr.currency,
                    pr.raised_by_name, pr.raised_on, pr.supplier_invoice_no,
                    pr.pc_confirmed_at, pr.pm_reviewed_at, pr.fm_scheduled_at, pr.acct_recorded_at,
                    l.lpo_no, l.payee_name, l.amount AS lpo_amount, p.project_no
               FROM payment_requests pr
               JOIN lpos l       ON l.c0d = pr.lpo_id
          LEFT JOIN c_projects p ON p.c0d = l.project_id
              WHERE pr.deleted = 0 AND pr.next_check_status = :r
              ORDER BY pr.raised_on ASC, pr.c0d ASC",
            [':r' => $rung]
        ) ?: [];
    }

    /* ── Raise ─────────────────────────────────────────────────────────── */

    /**
     * Raise a new PR. $d fields:
     *   lpo_id, notes, supplier_invoice_no, supplier_invoice_date,
     *   lines: array of ['term_id' => int, 'amount' => float, 'note' => string]
     * Increments claimed_amount on each term.
     */
    public function raise(array $d, int $userId, string $userName): int
    {
        $lpoId = (int)($d['lpo_id'] ?? 0);
        if ($lpoId <= 0)                          throw new InvalidArgumentException('lpo_id required.');
        $lines = $d['lines'] ?? [];
        if (empty($lines) || !is_array($lines))   throw new InvalidArgumentException('At least one term line is required.');

        $db  = new DB();
        $lpo = $db->mySelect(
            "SELECT c0d, lpo_no, lpo_type, cash_out_status, currency, amount, issued_at, project_id, payee_id
               FROM lpos WHERE c0d = :id AND deleted = 0",
            [':id' => $lpoId]
        );
        if (empty($lpo))                                   throw new RuntimeException('LPO not found.');
        $lpo = $lpo[0];
        if ((string)$lpo['lpo_type'] !== 'SUPPLIER')       throw new RuntimeException('Payment Requests are only for SUPPLIER LPOs.');
        if (!in_array($lpo['cash_out_status'], ['APPROVED','ISSUED','PAID'], true)) {
            throw new RuntimeException('LPO must be APPROVED or ISSUED to raise a Payment Request (currently ' . $lpo['cash_out_status'] . ').');
        }

        // Fetch + index term availability once so validation matches the UI's view of the world.
        $terms = (new LpoPaymentTerms())->availabilityForLpo($lpoId);
        $termById = []; foreach ($terms as $t) $termById[(int)$t['c0d']] = $t;

        $invNo   = trim((string)($d['supplier_invoice_no'] ?? ''));
        $invDate = trim((string)($d['supplier_invoice_date'] ?? ''));
        $today   = strtotime(date('Y-m-d'));

        $total = 0.0;
        $prepared = [];
        foreach ($lines as $ln) {
            $tid = (int)($ln['term_id'] ?? 0);
            if ($tid <= 0 || !isset($termById[$tid])) throw new InvalidArgumentException('Unknown term line ' . $tid . '.');
            $term = $termById[$tid];
            $amt  = round((float)($ln['amount'] ?? 0), 2);
            if ($amt <= 0)                                 throw new InvalidArgumentException('Amount on term "' . $term['label'] . '" must be > 0.');
            if ($amt > (float)$term['remaining'] + 0.005)  throw new InvalidArgumentException('Amount on "' . $term['label'] . '" exceeds remaining (' . number_format((float)$term['remaining']) . ').');
            if ((string)$term['trigger_type'] === 'NET_DAYS') {
                if ($invNo === '' || $invDate === '') throw new InvalidArgumentException('Supplier invoice # + date required — one of the terms is Net-days ("' . $term['label'] . '").');
                $eligible = strtotime($invDate) + ((int)($term['net_days'] ?? 30) * 86400);
                if ($eligible > $today) throw new InvalidArgumentException('Net-days not yet elapsed on "' . $term['label'] . '" — eligible on ' . date('Y-m-d', $eligible) . '.');
            } elseif ($term['blocked_reason'] !== null) {
                throw new InvalidArgumentException('Cannot claim "' . $term['label'] . '" — ' . $term['blocked_reason']);
            }
            $prepared[] = ['term_id' => $tid, 'amount' => $amt, 'note' => trim((string)($ln['note'] ?? ''))];
            $total += $amt;
        }
        $total = round($total, 2);

        $prNo = $this->allocateSerial();

        $fb = $db->insertUpdate(
            "INSERT INTO payment_requests
                (pr_no, lpo_id, pr_stage, requested_amount, currency, status, next_check_status,
                 supplier_invoice_no, supplier_invoice_date,
                 raised_by, raised_by_name, raised_on,
                 submitted_at, submitted_by, submitted_by_name,
                 notes, created_by)
             VALUES
                (:no, :l, 'PR1', :amt, :cur, 'SUBMITTED', 'PC_CONFIRM',
                 :in, :id,
                 :u, :un, NOW(),
                 NOW(), :u, :un,
                 :nt, :u)",
            [
                ':no'=>$prNo, ':l'=>$lpoId, ':amt'=>$total, ':cur'=>(string)$lpo['currency'],
                ':in'=>$invNo ?: null, ':id'=>$invDate ?: null,
                ':u'=>$userId, ':un'=>$userName,
                ':nt'=>trim((string)($d['notes'] ?? '')) ?: null,
            ]
        );
        $prId = (int)($fb['last_id'] ?? 0);

        $termsModel = new LpoPaymentTerms();
        foreach ($prepared as $p) {
            $db->insertUpdate(
                "INSERT INTO payment_request_lines (payment_request_id, term_id, amount, note)
                 VALUES (:p, :t, :a, :n)",
                [':p'=>$prId, ':t'=>$p['term_id'], ':a'=>$p['amount'], ':n'=>$p['note'] ?: null]
            );
            $termsModel->bumpClaimed($p['term_id'], $p['amount']);
        }

        // Notify PC group (queue is next_check_status = PC_CONFIRM)
        $this->notifyRung($prId, 'PC_CONFIRM', 'Projects Coordinator',
            'PR to confirm: ' . $prNo,
            'Supplier LPO ' . $lpo['lpo_no'] . ' · ' . $lpo['currency'] . ' ' . number_format($total) . ' claimed.',
            $userId, $userName);

        return $prId;
    }

    /* ── Ladder rungs ──────────────────────────────────────────────────── */

    public function pcConfirm(int $prId, string $note, int $userId, string $userName): void
    {
        $this->advance($prId, 'PC_CONFIRM', 'PC_CONFIRMED', 'PM_REVIEW', [
            'pc_confirmed_at' => 'NOW()', 'pc_confirmed_by' => $userId, 'pc_confirmed_by_name' => $userName, 'pc_confirm_note' => $note,
        ]);
        $this->notifyGroupFor($prId, 'PR_PENDING_PM', 'Project Manager', 'PR to review');
    }

    public function pmReview(int $prId, string $note, int $userId, string $userName): void
    {
        $this->advance($prId, 'PM_REVIEW', 'PM_REVIEWED', 'FM_SCHEDULE', [
            'pm_reviewed_at' => 'NOW()', 'pm_reviewed_by' => $userId, 'pm_reviewed_by_name' => $userName, 'pm_review_note' => $note,
        ]);
        $this->notifyGroupFor($prId, 'PR_PENDING_FM', 'Finance Manager', 'PR to schedule');
    }

    public function fmSchedule(int $prId, ?int $scheduleId, string $note, int $userId, string $userName): void
    {
        $this->advance($prId, 'FM_SCHEDULE', 'SCHEDULED', 'ACCT_RECORD', [
            'fm_scheduled_at' => 'NOW()', 'fm_scheduled_by' => $userId, 'fm_scheduled_by_name' => $userName,
            'fm_schedule_note' => $note, 'payment_schedule_id' => $scheduleId,
        ]);
        $this->notifyGroupFor($prId, 'PR_PENDING_ACCT', 'Accountant', 'PR to record disbursement');
    }

    /**
     * Slice 8Z-w · Slice D4 — Attach a PR at FM_SCHEDULE to a batch payment schedule.
     * Advances PR to SCHEDULED / next=ACCT_RECORD; stamps payment_schedule_id.
     * Accountant will now execute via the schedule, not per-PR.
     */
    public function attachToSchedule(int $prId, int $scheduleId, int $userId, string $userName): void
    {
        $this->advance($prId, 'FM_SCHEDULE', 'SCHEDULED', 'ACCT_RECORD', [
            'fm_scheduled_at' => 'NOW()', 'fm_scheduled_by' => $userId, 'fm_scheduled_by_name' => $userName,
            'fm_schedule_note' => 'Batched into schedule #' . $scheduleId,
            'payment_schedule_id' => $scheduleId,
        ]);
    }

    public function acctRecord(int $prId, string $disburseRef, ?int $journalEntryId, string $note, int $userId, string $userName): void
    {
        $this->advance($prId, 'ACCT_RECORD', 'PAID', '', [
            'acct_recorded_at' => 'NOW()', 'acct_recorded_by' => $userId, 'acct_recorded_by_name' => $userName,
            'acct_disburse_ref' => $disburseRef, 'acct_journal_entry_id' => $journalEntryId,
            'acct_record_note' => $note,
        ]);
        $pr = $this->find($prId);
        if (empty($pr)) return;
        $db = new DB();
        // Roll up amount_paid on the underlying LPO
        $db->insertUpdate(
            "UPDATE lpos SET amount_paid = COALESCE(amount_paid,0) + :a, edited_on = NOW() WHERE c0d = :id",
            [':a' => (float)$pr['requested_amount'], ':id' => (int)$pr['lpo_id']]
        );
        // Slice 8Z-t · Best-effort journal post if caller didn't supply one:
        // Dr Trade Payables 2110 · Cr Cash on Hand 1101 (falls back to Trade Receivables 1121 as suspense if 1101 missing).
        if (!$journalEntryId) {
            $autoJeId = $this->postJournalForPr($prId, $userId, $userName);
            if ($autoJeId > 0) {
                $db->insertUpdate("UPDATE payment_requests SET acct_journal_entry_id = :j WHERE c0d = :id", [':j' => $autoJeId, ':id' => $prId]);
            }
        }
        // Slice 8Z-t · Auto-close LPO if fully paid AND fully received
        $this->closeLpoIfDone((int)$pr['lpo_id']);

        // Notify raiser
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notify(
                (int)$pr['raised_by'], 'PR_PAID',
                'PR paid: ' . $pr['pr_no'],
                $pr['currency'] . ' ' . number_format((float)$pr['requested_amount']) . ' disbursed against ' . $pr['lpo_no'] . ' · ref ' . $disburseRef,
                'PaymentRequests/view/' . $prId, 'payment_request', $prId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /**
     * Slice 8Z-t · Post journal for a paid PR — Dr Trade Payables · Cr Cash on Hand.
     * Falls back gracefully if COA setup incomplete. Returns journal_entry_id (0 on skip).
     */
    private function postJournalForPr(int $prId, int $userId, string $userName): int
    {
        try {
            if (!class_exists('JournalPosting')) require_once ROOT . '/app/models/JournalPosting.php';
            $pr = $this->find($prId);
            if (empty($pr)) return 0;
            $payable = $this->resolveAccountCode('2110');            // Trade Payables (Dr)
            $cash    = $this->resolveAccountCode('1101')
                    ?: $this->resolveAccountCode('1121');            // Cash on Hand, or suspense (Cr)
            if (!$payable || !$cash) return 0;
            $amt = (float)$pr['requested_amount'];
            $post = (new JournalPosting())->post([
                'entry_date'      => date('Y-m-d'),
                'source_type'     => 'PAYMENT_REQUEST',
                'source_id'       => $prId,
                'source_ref'      => (string)$pr['pr_no'],
                'narrative'       => 'Supplier payment · ' . $pr['pr_no'] . ' · ' . $pr['lpo_no'] . ' · ref ' . (string)$pr['acct_disburse_ref'],
                'currency'        => (string)$pr['currency'],
                'posted_by'       => $userId,
                'posted_by_name'  => $userName,
                'lines' => [
                    ['account_id' => $payable, 'debit' => $amt, 'credit' => 0, 'narrative' => 'Trade payables settlement · ' . $pr['lpo_no'], 'lpo_id' => (int)$pr['lpo_id']],
                    ['account_id' => $cash,    'debit' => 0,    'credit' => $amt, 'narrative' => 'Cash disbursed · ref ' . (string)$pr['acct_disburse_ref'], 'lpo_id' => (int)$pr['lpo_id']],
                ],
            ]);
            return !empty($post['ok']) ? (int)($post['id'] ?? 0) : 0;
        } catch (\Throwable $e) { return 0; }
    }

    /**
     * Slice 8Z-t · LPO auto-close: cash_out_status = CLOSED when
     *   - receipt_status = FULLY_RECEIVED
     *   - every term is fully claimed (claimed_amount >= computed_amount)
     *   - amount_paid >= amount (every PR against those terms is PAID)
     * Guarded so it never touches an already-CLOSED or DECLINED LPO.
     */
    public function closeLpoIfDone(int $lpoId): bool
    {
        $db  = new DB();
        $r   = $db->mySelect(
            "SELECT c0d, amount, amount_paid, receipt_status, cash_out_status, lpo_no
               FROM lpos WHERE c0d = :id AND deleted = 0",
            [':id' => $lpoId]
        );
        if (empty($r)) return false;
        $row = $r[0];
        if (in_array($row['cash_out_status'], ['CLOSED','DECLINED','DRAFT','SUBMITTED'], true)) return false;
        if ((string)$row['receipt_status'] !== 'FULLY_RECEIVED')                                return false;
        if ((float)$row['amount_paid'] + 0.005 < (float)$row['amount'])                          return false;

        // Every term must be fully claimed (matches remaining=0)
        $t = $db->mySelect(
            "SELECT COALESCE(SUM(GREATEST(computed_amount - claimed_amount, 0)),0) unclaimed,
                    COUNT(*) n
               FROM lpo_payment_terms WHERE lpo_id = :l",
            [':l' => $lpoId]
        );
        if ((int)($t[0]['n'] ?? 0) === 0)                    return false;
        if ((float)($t[0]['unclaimed'] ?? 0) > 0.005)        return false;

        // Every PR must be PAID or CANCELLED (nothing in flight)
        $p = $db->mySelect(
            "SELECT COUNT(*) n FROM payment_requests
              WHERE lpo_id = :l AND deleted = 0 AND status NOT IN ('PAID','CANCELLED','REJECTED')",
            [':l' => $lpoId]
        );
        if ((int)($p[0]['n'] ?? 0) > 0) return false;

        $db->insertUpdate(
            "UPDATE lpos SET cash_out_status = 'CLOSED', edited_on = NOW() WHERE c0d = :id",
            [':id' => $lpoId]
        );
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notifyGroup(
                'Procurement', 'LPO_CLOSED',
                'LPO closed: ' . $row['lpo_no'],
                'Fully paid + fully received. No further activity expected.',
                'Lpos/view/' . $lpoId, 'lpo', $lpoId, 0, 'system'
            );
        } catch (\Throwable $e) { /* best-effort */ }
        return true;
    }

    private function resolveAccountCode(string $code): int
    {
        $db = new DB();
        $r  = $db->mySelect("SELECT c0d FROM chart_of_accounts WHERE account_code = :c AND is_active = 1 LIMIT 1", [':c' => $code]);
        return (int)($r[0]['c0d'] ?? 0);
    }

    /**
     * Reject at any rung. Bounces PR back to DRAFT for the raiser to edit + resubmit; restores
     * claimed_amount so terms availability recomputes.
     */
    public function reject(int $prId, string $stage, string $reason, int $userId, string $userName): void
    {
        if (trim($reason) === '') throw new InvalidArgumentException('A reason is required to reject.');
        $pr = $this->find($prId);
        if (!$pr) throw new RuntimeException('PR not found.');
        if (in_array($pr['status'], ['PAID','REJECTED','CANCELLED'], true)) throw new RuntimeException('Cannot reject a PR that is ' . $pr['status'] . '.');

        $db = new DB();
        $db->insertUpdate(
            "UPDATE payment_requests SET
                status = 'REJECTED', next_check_status = '',
                rejected_at = NOW(), rejected_by = :u, rejected_by_name = :un,
                rejected_at_stage = :st, rejected_reason = :r, edited_on = NOW()
              WHERE c0d = :id",
            [':u'=>$userId, ':un'=>$userName, ':st'=>$stage, ':r'=>trim($reason), ':id'=>$prId]
        );
        // Roll back claimed on each line so terms become claimable again.
        foreach ($this->linesFor($prId) as $ln) {
            (new LpoPaymentTerms())->bumpClaimed((int)$ln['term_id'], -(float)$ln['amount']);
        }
        // Notify raiser
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notify(
                (int)$pr['raised_by'], 'PR_REJECTED',
                'PR bounced back: ' . $pr['pr_no'],
                'Rejected at ' . $stage . ' — ' . mb_strimwidth($reason, 0, 200, '…'),
                'PaymentRequests/view/' . $prId, 'payment_request', $prId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    /** Raiser or admin cancels a not-yet-paid PR. Same claim-rollback as reject. */
    public function cancel(int $prId, int $userId): void
    {
        $pr = $this->find($prId);
        if (!$pr) throw new RuntimeException('PR not found.');
        if (in_array($pr['status'], ['PAID','CANCELLED','REJECTED'], true)) throw new RuntimeException('Cannot cancel a PR that is ' . $pr['status'] . '.');
        $db = new DB();
        $db->insertUpdate(
            "UPDATE payment_requests SET status = 'CANCELLED', next_check_status = '', edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [':u'=>$userId, ':id'=>$prId]
        );
        foreach ($this->linesFor($prId) as $ln) {
            (new LpoPaymentTerms())->bumpClaimed((int)$ln['term_id'], -(float)$ln['amount']);
        }
    }

    /* ── Internals ─────────────────────────────────────────────────────── */

    /** Common rung transition — validates current next_check_status matches expectation. */
    private function advance(int $prId, string $expectedRung, string $newStatus, string $newRung, array $stamps): void
    {
        $db = new DB();
        $pr = $db->mySelect("SELECT status, next_check_status FROM payment_requests WHERE c0d = :id AND deleted = 0", [':id' => $prId]);
        if (empty($pr)) throw new RuntimeException('PR not found.');
        if ((string)$pr[0]['next_check_status'] !== $expectedRung) {
            throw new RuntimeException('PR is at ' . ($pr[0]['next_check_status'] ?: '—') . ', not ' . $expectedRung . ' — cannot advance.');
        }
        $sets = ['status = :ns', 'next_check_status = :nr', 'edited_on = NOW()'];
        $params = [':ns' => $newStatus, ':nr' => $newRung, ':id' => $prId];
        foreach ($stamps as $col => $val) {
            if ($val === 'NOW()') { $sets[] = $col . ' = NOW()'; continue; }
            $p = ':' . $col;
            $sets[] = $col . ' = ' . $p;
            $params[$p] = ($val === '' ? null : $val);
        }
        $db->insertUpdate('UPDATE payment_requests SET ' . implode(', ', $sets) . ' WHERE c0d = :id', $params);
    }

    private function notifyRung(int $prId, string $rung, string $groupName, string $subject, string $body, int $userId, string $userName): void
    {
        try {
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notifyGroup(
                $groupName, 'PR_PENDING_' . $rung, $subject, $body,
                'PaymentRequests/view/' . $prId, 'payment_request', $prId, $userId, $userName
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    private function notifyGroupFor(int $prId, string $kind, string $groupName, string $subjectVerb): void
    {
        try {
            $pr = $this->find($prId);
            if (empty($pr)) return;
            if (!class_exists('Notifications')) require_once ROOT . '/app/models/Notifications.php';
            (new Notifications())->notifyGroup(
                $groupName, $kind,
                $subjectVerb . ': ' . $pr['pr_no'],
                'Supplier LPO ' . $pr['lpo_no'] . ' · ' . $pr['currency'] . ' ' . number_format((float)$pr['requested_amount']),
                'PaymentRequests/view/' . $prId, 'payment_request', $prId, 0, ''
            );
        } catch (\Throwable $e) { /* best-effort */ }
    }

    private function allocateSerial(): string
    {
        $db  = new DB();
        $yr  = date('Y');
        $r   = $db->mySelect(
            "SELECT pr_no FROM payment_requests WHERE pr_no LIKE :p ORDER BY c0d DESC LIMIT 1",
            [':p' => 'PR-' . $yr . '-%']
        );
        $next = 1;
        if (!empty($r) && preg_match('/PR-\d{4}-(\d+)/', (string)$r[0]['pr_no'], $m)) $next = ((int)$m[1]) + 1;
        return sprintf('PR-%s-%05d', $yr, $next);
    }
}
