Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
84.52%
202 / 239
3.85%
1 / 26
 CRAP
0.00%
0 / 1
LoanRepository
84.52%
202 / 239
3.85%
1 / 26
 80.68
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 checkEligibility
80.00%
8 / 10
0.00%
0 / 1
 3.07
 createLoanRequest
92.86%
13 / 14
0.00%
0 / 1
 2.00
 generatePaymentSchedule
75.00%
3 / 4
0.00%
0 / 1
 2.06
 findById
87.50%
7 / 8
0.00%
0 / 1
 3.02
 findByInvestorId
90.00%
9 / 10
0.00%
0 / 1
 3.01
 findActive
88.89%
8 / 9
0.00%
0 / 1
 3.01
 getConfig
88.89%
8 / 9
0.00%
0 / 1
 3.01
 getConfigBool
80.00%
8 / 10
0.00%
0 / 1
 3.07
 getLoanConfig
93.33%
14 / 15
0.00%
0 / 1
 3.00
 updateLoanConfig
83.33%
5 / 6
0.00%
0 / 1
 2.02
 getPaymentSchedule
90.00%
9 / 10
0.00%
0 / 1
 3.01
 findPending
88.89%
8 / 9
0.00%
0 / 1
 3.01
 findApproved
0.00%
0 / 9
0.00%
0 / 1
 12
 approveLoan
92.31%
12 / 13
0.00%
0 / 1
 2.00
 acceptLoan
80.00%
4 / 5
0.00%
0 / 1
 2.03
 declineLoan
80.00%
4 / 5
0.00%
0 / 1
 2.03
 markActive
83.33%
5 / 6
0.00%
0 / 1
 2.02
 investorHasBlockingLoan
85.71%
6 / 7
0.00%
0 / 1
 2.01
 denyLoan
90.00%
9 / 10
0.00%
0 / 1
 2.00
 getInvestorAccountId
87.50%
7 / 8
0.00%
0 / 1
 3.02
 getNextUnpaidScheduleEntry
87.50%
7 / 8
0.00%
0 / 1
 3.02
 recordPayment
90.32%
28 / 31
0.00%
0 / 1
 4.01
 recordAdditionalPayment
91.67%
11 / 12
0.00%
0 / 1
 2.00
 reamortizeLoan
75.00%
3 / 4
0.00%
0 / 1
 2.06
 markRemainingScheduleEntriesPaid
83.33%
5 / 6
0.00%
0 / 1
 2.02
1<?php
2
3declare(strict_types=1);
4
5namespace App\Domain\Loan\Repository;
6
7use App\Domain\Loan\Data\LoanData;
8use App\Domain\Loan\Data\LoanEligibilityData;
9use App\Support\Row;
10use PDO;
11use RuntimeException;
12
13final readonly class LoanRepository
14{
15    public function __construct(
16        private PDO $pdo,
17    ) {}
18
19    public function checkEligibility(int $investorId): LoanEligibilityData
20    {
21        $sql = <<<SQL
22                SELECT
23                    eligible,
24                    allow_multiple_loans AS "allowMultipleLoans",
25                    reason,
26                    max_loan_amount AS "maxLoanAmount",
27                    current_balance AS "currentBalance",
28                    ltv_percentage AS "ltvPercentage",
29                    min_required_balance AS "minRequiredBalance"
30                FROM check_loan_eligibility(:investor_id)
31            SQL;
32
33        $stmt = $this->pdo->prepare($sql);
34        if ($stmt === false) {
35            throw new RuntimeException('Failed to prepare statement');
36        }
37        $stmt->execute(['investor_id' => $investorId]);
38        $row = $stmt->fetch(PDO::FETCH_ASSOC);
39
40        if (!is_array($row)) {
41            return new LoanEligibilityData(false, false, 'Unable to check eligibility', '0', '0', '0', '0');
42        }
43
44        return LoanEligibilityData::fromRow($row);
45    }
46
47    public function createLoanRequest(
48        int $investorId,
49        int $accountId,
50        string $amount,
51        int $termMonths,
52        string $interestRate,
53        string $servicingFeeRate,
54    ): int {
55        $sql = <<<SQL
56                INSERT INTO loans (
57                    investor_id, account_id, loan_type, status,
58                    requested_amount, requested_term_months,
59                    principle_amount, interest_rate, servicing_fee_rate, term_months,
60                    outstanding_balance, monthly_payment,
61                    start_date, maturity_date,
62                    requested_at
63                ) VALUES (
64                    :investor_id, :account_id, 'secured', 'requested',
65                    :amount, :term_months,
66                    :amount, :interest_rate, :servicing_fee_rate, :term_months,
67                    0, 0,
68                    NULL, NULL,
69                    CURRENT_TIMESTAMP
70                )
71                RETURNING loan_id
72            SQL;
73
74        $stmt = $this->pdo->prepare($sql);
75        if ($stmt === false) {
76            throw new RuntimeException('Failed to prepare statement');
77        }
78        $stmt->execute([
79            'investor_id' => $investorId,
80            'account_id' => $accountId,
81            'amount' => $amount,
82            'interest_rate' => $interestRate,
83            'servicing_fee_rate' => $servicingFeeRate,
84            'term_months' => $termMonths,
85        ]);
86
87        return (int)$stmt->fetchColumn();
88    }
89
90    public function generatePaymentSchedule(int $loanId): void
91    {
92        $stmt = $this->pdo->prepare('SELECT generate_payment_schedule(:loanId)');
93        if ($stmt === false) {
94            throw new RuntimeException('Failed to prepare statement');
95        }
96        $stmt->execute(['loanId' => $loanId]);
97    }
98
99    public function findById(int $loanId): ?LoanData
100    {
101        $sql = <<<SQL
102                SELECT
103                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
104                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
105                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
106                    l.loan_type AS "loanType",
107                    l.requested_amount AS "requestedAmount",
108                    l.requested_term_months AS "requestedTermMonths",
109                    l.principle_amount AS "principleAmount",
110                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
111                    l.servicing_fee_rate AS "servicingFeeRate",
112                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
113                    l.total_interest AS "totalInterest", l.total_servicing AS "totalServicing", l.total_repayment AS "totalRepayment",
114                    l.start_date AS "startDate", l.maturity_date AS "maturityDate",
115                    l.next_payment_due AS "nextPaymentDue", l.status,
116                    l.collateral_description AS "collateralDescription",
117                    l.requested_at AS "requestedAt", l.reviewed_at AS "reviewedAt",
118                    l.reviewed_by AS "reviewedBy", l.activated_at AS "activatedAt",
119                    l.denial_reason AS "denialReason", l.approval_notes AS "approvalNotes",
120                    l.accepted_at AS "acceptedAt", l.declined_at AS "declinedAt",
121                    l.decline_reason AS "declineReason",
122                    l.created_at AS "createdAt", l.updated_at AS "updatedAt"
123                FROM loans l
124                 JOIN investors i ON i.investor_id = l.investor_id
125                 JOIN accounts a ON a.account_id = l.account_id
126                 WHERE l.loan_id = :loan_id
127            SQL;
128
129        $stmt = $this->pdo->prepare($sql);
130        if ($stmt === false) {
131            throw new RuntimeException('Failed to prepare statement');
132        }
133        $stmt->execute(['loan_id' => $loanId]);
134        $row = $stmt->fetch(PDO::FETCH_ASSOC);
135
136        return is_array($row) ? LoanData::fromRow($row) : null;
137    }
138
139    /**
140     * @param int $investorId
141     * @return array<int, LoanData>
142     */
143    public function findByInvestorId(int $investorId): array
144    {
145        $sql = <<<SQL
146                SELECT
147                    loan_id AS "loanId", account_id AS "accountId", investor_id AS "investorId",
148                    loan_type AS "loanType",
149                    requested_amount AS "requestedAmount",
150                    requested_term_months AS "requestedTermMonths",
151                    principle_amount AS "principleAmount",
152                    outstanding_balance AS "outstandingBalance", interest_rate AS "interestRate",
153                    servicing_fee_rate AS "servicingFeeRate",
154                    term_months AS "termMonths", monthly_payment AS "monthlyPayment",
155                    total_interest AS "totalInterest", total_servicing AS "totalServicing", total_repayment AS "totalRepayment",
156                    start_date AS "startDate", maturity_date AS "maturityDate",
157                    next_payment_due AS "nextPaymentDue", status,
158                    requested_at AS "requestedAt", reviewed_at AS "reviewedAt",
159                    activated_at AS "activatedAt",
160                    denial_reason AS "denialReason", approval_notes AS "approvalNotes"
161                 FROM loans WHERE investor_id = :investor_id ORDER BY created_at DESC
162            SQL;
163
164        $stmt = $this->pdo->prepare($sql);
165        if ($stmt === false) {
166            throw new RuntimeException('Failed to prepare statement');
167        }
168        $stmt->execute(['investor_id' => $investorId]);
169
170        $loans = [];
171        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
172            $loans[] = LoanData::fromRow(Row::from($row));
173        }
174
175        return $loans;
176    }
177
178    /**
179     * @return array<int, LoanData>
180     */
181    public function findActive(): array
182    {
183        $sql = <<<SQL
184                SELECT
185                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
186                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
187                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
188                    l.loan_type AS "loanType",
189                    l.requested_amount AS "requestedAmount",
190                    l.principle_amount AS "principleAmount",
191                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
192                    l.servicing_fee_rate AS "servicingFeeRate",
193                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
194                    l.total_interest AS "totalInterest", l.total_servicing AS "totalServicing", l.total_repayment AS "totalRepayment",
195                    l.start_date AS "startDate", l.maturity_date AS "maturityDate",
196                    l.next_payment_due AS "nextPaymentDue", l.status,
197                    l.requested_at AS "requestedAt", l.activated_at AS "activatedAt"
198                FROM loans l
199                JOIN investors i ON i.investor_id = l.investor_id
200                JOIN accounts a ON a.account_id = l.account_id
201                WHERE l.status IN ('active', 'disbursed')
202                ORDER BY l.created_at DESC
203            SQL;
204
205        $stmt = $this->pdo->query($sql);
206        if ($stmt === false) {
207            throw new RuntimeException('Failed to execute query');
208        }
209
210        $loans = [];
211        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
212            $loans[] = LoanData::fromRow(Row::from($row));
213        }
214        return $loans;
215    }
216
217    /**
218     * @return array<string, string>
219     */
220    public function getConfig(): array
221    {
222        $stmt = $this->pdo->query('SELECT config_key, config_value FROM loan_config');
223        if ($stmt === false) {
224            throw new RuntimeException('Failed to execute query');
225        }
226        $config = [];
227        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
228            $rowArray = Row::from($row);
229            $key = Row::string($rowArray, 'config_key');
230            $config[$key] = Row::string($rowArray, 'config_value');
231        }
232        return $config;
233    }
234
235    /**
236     * Read a boolean loan_config flag. String 'true' becomes true; everything
237     * else (including missing rows) falls back to $default.
238     * @param string $key
239     * @param bool $default
240     */
241    public function getConfigBool(string $key, bool $default = false): bool
242    {
243        $stmt = $this->pdo->prepare(
244            'SELECT config_value FROM loan_config WHERE config_key = :key',
245        );
246        if ($stmt === false) {
247            throw new RuntimeException('Failed to prepare statement');
248        }
249        $stmt->execute(['key' => $key]);
250        $value = $stmt->fetchColumn();
251
252        if ($value === false) {
253            return $default;
254        }
255
256        return strtolower((string)$value) === 'true';
257    }
258
259    /**
260     * @return list<array{key: string, value: string, description: string|null, updatedAt: string|null}>
261     */
262    public function getLoanConfig(): array
263    {
264        $sql = <<<SQL
265                SELECT config_key AS "key",
266                       config_value AS "value",
267                       description,
268                       updated_at AS "updatedAt"
269                FROM loan_config
270                ORDER BY config_id
271            SQL;
272
273        $stmt = $this->pdo->query($sql);
274        if ($stmt === false) {
275            throw new RuntimeException('Failed to execute query');
276        }
277
278        $config = [];
279        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
280            $r = Row::from($row);
281            $config[] = [
282                'key' => Row::string($r, 'key'),
283                'value' => Row::string($r, 'value'),
284                'description' => Row::nullableString($r, 'description'),
285                'updatedAt' => Row::nullableString($r, 'updatedAt'),
286            ];
287        }
288        return $config;
289    }
290
291    public function updateLoanConfig(string $key, string $value): void
292    {
293        $stmt = $this->pdo->prepare(
294            'UPDATE loan_config SET config_value = :value, updated_at = CURRENT_TIMESTAMP WHERE config_key = :key',
295        );
296        if ($stmt === false) {
297            throw new RuntimeException('Failed to prepare statement');
298        }
299        $stmt->execute(['key' => $key, 'value' => $value]);
300    }
301
302    /**
303     * @param int $loanId
304     * @return list<array<mixed>>
305     */
306    public function getPaymentSchedule(int $loanId): array
307    {
308        $sql = <<<SQL
309                SELECT schedule_id AS "scheduleId", loan_id AS "loanId", payment_number AS "paymentNumber",
310                    due_date AS "dueDate", expected_amount AS "expectedAmount",
311                    principal_portion AS "principalPortion", interest_portion AS "interestPortion",
312                    servicing_portion AS "servicingPortion",
313                    is_paid AS "isPaid", actual_payment_id AS "actualPaymentId", paid_date AS "paidDate"
314                FROM loan_payment_schedule WHERE loan_id = :loanId ORDER BY payment_number
315            SQL;
316
317        $stmt = $this->pdo->prepare($sql);
318        if ($stmt === false) {
319            throw new RuntimeException('Failed to prepare statement');
320        }
321        $stmt->execute(['loanId' => $loanId]);
322
323        $rows = [];
324        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
325            $rows[] = Row::from($row);
326        }
327
328        return $rows;
329    }
330
331    /**
332     * @return array<int, LoanData>
333     */
334    public function findPending(): array
335    {
336        $sql = <<<SQL
337                SELECT
338                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
339                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
340                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
341                    l.loan_type AS "loanType",
342                    l.requested_amount AS "requestedAmount",
343                    l.requested_term_months AS "requestedTermMonths",
344                    l.principle_amount AS "principleAmount",
345                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
346                    l.servicing_fee_rate AS "servicingFeeRate",
347                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
348                    l.status,
349                    l.requested_at AS "requestedAt",
350                    l.denial_reason AS "denialReason", l.approval_notes AS "approvalNotes",
351                    l.declined_at AS "declinedAt", l.decline_reason AS "declineReason"
352                FROM loans l
353                JOIN investors i ON i.investor_id = l.investor_id
354                JOIN accounts a ON a.account_id = l.account_id
355                WHERE l.status IN ('requested', 'under_review')
356                ORDER BY l.requested_at ASC
357            SQL;
358
359        $stmt = $this->pdo->query($sql);
360        if ($stmt === false) {
361            throw new RuntimeException('Failed to execute query');
362        }
363
364        $loans = [];
365        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
366            $loans[] = LoanData::fromRow(Row::from($row));
367        }
368        return $loans;
369    }
370
371    /**
372     * @return array<int, LoanData>
373     */
374    public function findApproved(): array
375    {
376        $sql = <<<SQL
377                SELECT
378                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
379                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
380                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
381                    l.loan_type AS "loanType",
382                    l.requested_amount AS "requestedAmount",
383                    l.requested_term_months AS "requestedTermMonths",
384                    l.principle_amount AS "principleAmount",
385                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
386                    l.servicing_fee_rate AS "servicingFeeRate",
387                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
388                    l.status,
389                    l.requested_at AS "requestedAt",
390                    l.reviewed_at AS "reviewedAt",
391                    l.approval_notes AS "approvalNotes"
392                FROM loans l
393                JOIN investors i ON i.investor_id = l.investor_id
394                JOIN accounts a ON a.account_id = l.account_id
395                WHERE l.status = 'approved'
396                ORDER BY l.reviewed_at ASC
397            SQL;
398
399        $stmt = $this->pdo->query($sql);
400        if ($stmt === false) {
401            throw new RuntimeException('Failed to execute query');
402        }
403
404        $loans = [];
405        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
406            $loans[] = LoanData::fromRow(Row::from($row));
407        }
408        return $loans;
409    }
410
411    public function approveLoan(
412        int $loanId,
413        int $adminUserId,
414        ?string $amount,
415        ?int $termMonths,
416        ?string $interestRate,
417        ?string $notes,
418    ): void {
419        // FSC-94: admin approval no longer takes the loan straight to
420        // `approved`. The investor must accept the (possibly modified)
421        // terms first. Accept transitions to `approved`; decline → `denied`.
422        $sql = <<<SQL
423                UPDATE loans SET
424                    status = 'pending_acceptance',
425                    principle_amount = COALESCE(:amount, principle_amount),
426                    term_months = COALESCE(:term_months, term_months),
427                    interest_rate = COALESCE(:interest_rate, interest_rate),
428                    approval_notes = :notes,
429                    reviewed_at = CURRENT_TIMESTAMP,
430                    reviewed_by = :admin_user_id,
431                    updated_at = CURRENT_TIMESTAMP
432                WHERE loan_id = :loan_id
433            SQL;
434
435        $stmt = $this->pdo->prepare($sql);
436        if ($stmt === false) {
437            throw new RuntimeException('Failed to prepare statement');
438        }
439        $stmt->execute([
440            'loan_id' => $loanId,
441            'admin_user_id' => $adminUserId,
442            'amount' => $amount,
443            'term_months' => $termMonths,
444            'interest_rate' => $interestRate,
445            'notes' => $notes,
446        ]);
447    }
448
449    /**
450     * Investor accepts a loan that admin set to `pending_acceptance`.
451     * Moves to `approved` so the FSC-96 disbursement queue can claim it.
452     * @param int $loanId
453     */
454    public function acceptLoan(int $loanId): void
455    {
456        $stmt = $this->pdo->prepare(<<<'SQL'
457                UPDATE loans SET
458                    status = 'approved',
459                    accepted_at = CURRENT_TIMESTAMP,
460                    updated_at = CURRENT_TIMESTAMP
461                WHERE loan_id = :loan_id
462            SQL);
463        if ($stmt === false) {
464            throw new RuntimeException('Failed to prepare statement');
465        }
466        $stmt->execute(['loan_id' => $loanId]);
467    }
468
469    /**
470     * Investor declines the terms set by admin. FSC-130: instead of ending
471     * the loan at `denied`, the request returns to the admin queue as
472     * `under_review` carrying the investor's reason, so the admin can revise
473     * the terms and re-approve (or hard-deny). The prior `principle_amount`,
474     * `interest_rate`, and `approval_notes` are left intact as context for the
475     * admin's re-review.
476     * @param int $loanId
477     * @param string $reason
478     */
479    public function declineLoan(int $loanId, string $reason): void
480    {
481        $stmt = $this->pdo->prepare(<<<'SQL'
482                UPDATE loans SET
483                    status = 'under_review',
484                    declined_at = CURRENT_TIMESTAMP,
485                    decline_reason = :reason,
486                    updated_at = CURRENT_TIMESTAMP
487                WHERE loan_id = :loan_id
488            SQL);
489        if ($stmt === false) {
490            throw new RuntimeException('Failed to prepare statement');
491        }
492        $stmt->execute(['loan_id' => $loanId, 'reason' => $reason]);
493    }
494
495    /**
496     * Transition a loan into the running `active` state. Called by
497     * DisbursementService::markDisbursed once admin has confirmed the wire.
498     * The on_loan_activated trigger fires on this status change and fills
499     * activated_at, schedule fields, and outstanding_balance.
500     * @param int $loanId
501     */
502    public function markActive(int $loanId): void
503    {
504        $stmt = $this->pdo->prepare(
505            "UPDATE loans SET status = 'active', start_date = CURRENT_DATE WHERE loan_id = :loan_id",
506        );
507        if ($stmt === false) {
508            throw new RuntimeException('Failed to prepare statement');
509        }
510        $stmt->execute(['loan_id' => $loanId]);
511    }
512
513    /**
514     * FSC-98: returns true when the investor backing this account has a loan in
515     * an in-progress status (`requested`, `under_review`, `pending_acceptance`,
516     * `approved`, `active`, `disbursed`). Investor-declined loans (FSC-130 leaves
517     * them `under_review` with `declined_at` set) are excluded — a loan the
518     * investor declined shouldn't block their deposits. Used by TransactionService
519     * to block new investment deposits while a loan is genuinely in progress.
520     * @param int $accountId
521     */
522    public function investorHasBlockingLoan(int $accountId): bool
523    {
524        $sql = <<<SQL
525                SELECT EXISTS (
526                    SELECT 1
527                    FROM lending.loans l
528                    JOIN treasury.accounts a ON a.investor_id = l.investor_id
529                    WHERE a.account_id = :account_id
530                      AND l.status IN ('requested', 'under_review', 'pending_acceptance', 'approved', 'active', 'disbursed')
531                      AND l.declined_at IS NULL
532                )
533            SQL;
534
535        $stmt = $this->pdo->prepare($sql);
536        if ($stmt === false) {
537            throw new RuntimeException('Failed to prepare statement');
538        }
539        $stmt->execute(['account_id' => $accountId]);
540
541        return (bool)$stmt->fetchColumn();
542    }
543
544    public function denyLoan(int $loanId, int $adminUserId, string $reason): void
545    {
546        $sql = <<<SQL
547                UPDATE loans SET
548                    status = 'denied',
549                    denial_reason = :reason,
550                    reviewed_at = CURRENT_TIMESTAMP,
551                    reviewed_by = :admin_user_id,
552                    updated_at = CURRENT_TIMESTAMP
553                WHERE loan_id = :loan_id
554            SQL;
555
556        $stmt = $this->pdo->prepare($sql);
557        if ($stmt === false) {
558            throw new RuntimeException('Failed to prepare statement');
559        }
560        $stmt->execute([
561            'loan_id' => $loanId,
562            'admin_user_id' => $adminUserId,
563            'reason' => $reason,
564        ]);
565    }
566
567    public function getInvestorAccountId(int $investorId): ?int
568    {
569        $stmt = $this->pdo->prepare(
570            'SELECT account_id FROM accounts WHERE investor_id = :investorId LIMIT 1',
571        );
572
573        if ($stmt === false) {
574            throw new RuntimeException('Failed to prepare statement');
575        }
576        $stmt->execute(['investorId' => $investorId]);
577        $result = $stmt->fetchColumn();
578        return $result !== false ? (int)$result : null;
579    }
580
581    /**
582     * @param int $loanId
583     * @return array<mixed>|null camelCase row from loan_payment_schedule, or null if all paid
584     */
585    public function getNextUnpaidScheduleEntry(int $loanId): ?array
586    {
587        $sql = <<<SQL
588                SELECT
589                    schedule_id AS "scheduleId",
590                    payment_number AS "paymentNumber",
591                    due_date AS "dueDate",
592                    expected_amount AS "expectedAmount",
593                    principal_portion AS "principalPortion",
594                    interest_portion AS "interestPortion",
595                    servicing_portion AS "servicingPortion"
596                FROM loan_payment_schedule
597                WHERE loan_id = :loan_id AND is_paid = false
598                ORDER BY payment_number ASC
599                LIMIT 1
600            SQL;
601
602        $stmt = $this->pdo->prepare($sql);
603        if ($stmt === false) {
604            throw new RuntimeException('Failed to prepare statement');
605        }
606        $stmt->execute(['loan_id' => $loanId]);
607        $row = $stmt->fetch(PDO::FETCH_ASSOC);
608
609        return is_array($row) ? $row : null;
610    }
611
612    /**
613     * Record a loan payment and mark the schedule entry as paid.
614     * @param int $loanId
615     * @param int $scheduleId
616     * @param string $principalPaid
617     * @param string $interestPaid
618     * @param string $amountPaid
619     * @param int $transactionId
620     * @param string $paymentMethod
621     * @param ?string $stripePaymentIntentId
622     * @param string $servicingPaid
623     */
624    public function recordPayment(
625        int $loanId,
626        int $scheduleId,
627        string $principalPaid,
628        string $interestPaid,
629        string $amountPaid,
630        ?int $transactionId = null,
631        string $paymentMethod = 'auto_debit',
632        ?string $stripePaymentIntentId = null,
633        string $servicingPaid = '0',
634    ): int {
635        $sql = <<<SQL
636                INSERT INTO loan_payments (
637                    loan_id, payment_date, due_date, amount_paid,
638                    principal_paid, interest_paid, servicing_paid, payment_method,
639                    transaction_id, stripe_payment_intent_id, status
640                )
641                SELECT
642                    :loan_id, CURRENT_DATE, due_date, :amount_paid,
643                    :principal_paid, :interest_paid, :servicing_paid, :payment_method,
644                    :transaction_id, :stripe_payment_intent_id, 'completed'
645                FROM loan_payment_schedule
646                WHERE schedule_id = :schedule_id
647                RETURNING payment_id
648            SQL;
649
650        $stmt = $this->pdo->prepare($sql);
651        if ($stmt === false) {
652            throw new RuntimeException('Failed to prepare statement');
653        }
654        $stmt->execute([
655            'loan_id' => $loanId,
656            'amount_paid' => $amountPaid,
657            'principal_paid' => $principalPaid,
658            'interest_paid' => $interestPaid,
659            'servicing_paid' => $servicingPaid,
660            'payment_method' => $paymentMethod,
661            'transaction_id' => $transactionId,
662            'stripe_payment_intent_id' => $stripePaymentIntentId,
663            'schedule_id' => $scheduleId,
664        ]);
665
666        $paymentId = (int)$stmt->fetchColumn();
667
668        // Mark schedule entry as paid
669        $stmt = $this->pdo->prepare(
670            'UPDATE loan_payment_schedule SET is_paid = true, actual_payment_id = :payment_id, paid_date = CURRENT_DATE WHERE schedule_id = :schedule_id',
671        );
672        if ($stmt === false) {
673            throw new RuntimeException('Failed to prepare statement');
674        }
675        $stmt->execute(['payment_id' => $paymentId, 'schedule_id' => $scheduleId]);
676
677        // Update next_payment_due on the loan
678        $stmt = $this->pdo->prepare(
679            "UPDATE loans SET next_payment_due = (
680                SELECT MIN(due_date) FROM loan_payment_schedule WHERE loan_id = :loan_id AND is_paid = false
681            ) WHERE loan_id = :loan_id2",
682        );
683        if ($stmt === false) {
684            throw new RuntimeException('Failed to prepare statement');
685        }
686        $stmt->execute(['loan_id' => $loanId, 'loan_id2' => $loanId]);
687
688        return $paymentId;
689    }
690
691    /**
692     * Record an off-schedule (additional / payoff) payment — not tied to a
693     * scheduled installment (FSC-102). Under amortization an extra payment is a
694     * principal prepayment: the whole amount reduces the remaining principal.
695     * The caller re-amortizes the remaining schedule (partial) or settles it
696     * (full payoff).
697     *
698     * @param int $loanId
699     * @param string $amount
700     * @param string $stripePaymentIntentId
701     */
702    public function recordAdditionalPayment(int $loanId, string $amount, string $stripePaymentIntentId): int
703    {
704        // An additional payment is a principal prepayment: the whole amount goes
705        // to principal (no interest/servicing portion). The update_loan_balance
706        // trigger draws it off the remaining principal and flips paid_off at zero.
707        $sql = <<<SQL
708                INSERT INTO loan_payments (
709                    loan_id, payment_date, due_date, amount_paid,
710                    principal_paid, interest_paid, servicing_paid,
711                    payment_method, stripe_payment_intent_id, status
712                )
713                VALUES (
714                    :loan_id, CURRENT_DATE, CURRENT_DATE, :amount,
715                    :principal, 0, 0,
716                    'card', :stripe_id, 'completed'
717                )
718                RETURNING payment_id
719            SQL;
720
721        $stmt = $this->pdo->prepare($sql);
722        if ($stmt === false) {
723            throw new RuntimeException('Failed to prepare statement');
724        }
725        $stmt->execute([
726            'loan_id' => $loanId,
727            'amount' => $amount,
728            'principal' => $amount,
729            'stripe_id' => $stripePaymentIntentId,
730        ]);
731
732        return (int)$stmt->fetchColumn();
733    }
734
735    /**
736     * Recast a loan after a principal prepayment: recompute the payment over the
737     * remaining term and regenerate the unpaid schedule rows (FSC-134).
738     * @param int $loanId
739     */
740    public function reamortizeLoan(int $loanId): void
741    {
742        $stmt = $this->pdo->prepare('SELECT reamortize_loan(:id)');
743        if ($stmt === false) {
744            throw new RuntimeException('Failed to prepare statement');
745        }
746        $stmt->execute(['id' => $loanId]);
747    }
748
749    /**
750     * Settle any still-unpaid scheduled installments — used after a payoff so
751     * the schedule reflects the closed loan rather than leaving orphan rows.
752     * @param int $loanId
753     */
754    public function markRemainingScheduleEntriesPaid(int $loanId): void
755    {
756        $stmt = $this->pdo->prepare(
757            'UPDATE loan_payment_schedule SET is_paid = true, paid_date = CURRENT_DATE WHERE loan_id = :loan_id AND is_paid = false',
758        );
759        if ($stmt === false) {
760            throw new RuntimeException('Failed to prepare statement');
761        }
762        $stmt->execute(['loan_id' => $loanId]);
763    }
764}