Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
84.66%
160 / 189
5.26%
1 / 19
 CRAP
0.00%
0 / 1
LoanRepository
84.66%
160 / 189
5.26%
1 / 19
 59.03
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 checkEligibility
81.82%
9 / 11
0.00%
0 / 1
 3.05
 createLoanRequest
92.31%
12 / 13
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
 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
 activateLoan
83.33%
5 / 6
0.00%
0 / 1
 2.02
 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.00%
27 / 30
0.00%
0 / 1
 4.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                    reason,
25                    max_loan_amount AS "maxLoanAmount",
26                    current_balance AS "currentBalance",
27                    ltv_percentage AS "ltvPercentage",
28                    min_required_balance AS "minRequiredBalance"
29                FROM check_loan_eligibility(:investor_id)
30            SQL;
31
32        $stmt = $this->pdo->prepare($sql);
33        if ($stmt === false) {
34            throw new RuntimeException('Failed to prepare statement');
35        }
36        $stmt->execute(['investor_id' => $investorId]);
37        $row = $stmt->fetch(PDO::FETCH_ASSOC);
38
39        if (!is_array($row)) {
40            return new LoanEligibilityData(false, true, 'Unable to check eligibility', '0', '0', '0', '0');
41        }
42
43        $row['allowMultipleLoans'] = true;
44
45        return LoanEligibilityData::fromRow($row);
46    }
47
48    public function createLoanRequest(
49        int $investorId,
50        int $accountId,
51        string $amount,
52        int $termMonths,
53        string $interestRate,
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, 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, :term_months,
67                    0, 0,
68                    CURRENT_DATE, CURRENT_DATE,
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            'term_months' => $termMonths,
84        ]);
85
86        return (int)$stmt->fetchColumn();
87    }
88
89    public function generatePaymentSchedule(int $loanId): void
90    {
91        $stmt = $this->pdo->prepare('SELECT generate_payment_schedule(:loanId)');
92        if ($stmt === false) {
93            throw new RuntimeException('Failed to prepare statement');
94        }
95        $stmt->execute(['loanId' => $loanId]);
96    }
97
98    public function findById(int $loanId): ?LoanData
99    {
100        $sql = <<<SQL
101                SELECT
102                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
103                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
104                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
105                    l.loan_type AS "loanType",
106                    l.requested_amount AS "requestedAmount",
107                    l.requested_term_months AS "requestedTermMonths",
108                    l.principle_amount AS "principleAmount",
109                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
110                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
111                    l.total_interest AS "totalInterest", l.total_repayment AS "totalRepayment",
112                    l.start_date AS "startDate", l.maturity_date AS "maturityDate",
113                    l.next_payment_due AS "nextPaymentDue", l.status,
114                    l.collateral_description AS "collateralDescription",
115                    l.requested_at AS "requestedAt", l.reviewed_at AS "reviewedAt",
116                    l.reviewed_by AS "reviewedBy", l.activated_at AS "activatedAt",
117                    l.denial_reason AS "denialReason", l.approval_notes AS "approvalNotes",
118                    l.created_at AS "createdAt", l.updated_at AS "updatedAt"
119                FROM loans l
120                 JOIN investors i ON i.investor_id = l.investor_id
121                 JOIN accounts a ON a.account_id = l.account_id
122                 WHERE l.loan_id = :loan_id
123            SQL;
124
125        $stmt = $this->pdo->prepare($sql);
126        if ($stmt === false) {
127            throw new RuntimeException('Failed to prepare statement');
128        }
129        $stmt->execute(['loan_id' => $loanId]);
130        $row = $stmt->fetch(PDO::FETCH_ASSOC);
131
132        return is_array($row) ? LoanData::fromRow($row) : null;
133    }
134
135    /**
136     * @param int $investorId
137     * @return array<int, LoanData>
138     */
139    public function findByInvestorId(int $investorId): array
140    {
141        $sql = <<<SQL
142                SELECT
143                    loan_id AS "loanId", account_id AS "accountId", investor_id AS "investorId",
144                    loan_type AS "loanType",
145                    requested_amount AS "requestedAmount",
146                    requested_term_months AS "requestedTermMonths",
147                    principle_amount AS "principleAmount",
148                    outstanding_balance AS "outstandingBalance", interest_rate AS "interestRate",
149                    term_months AS "termMonths", monthly_payment AS "monthlyPayment",
150                    total_interest AS "totalInterest", total_repayment AS "totalRepayment",
151                    start_date AS "startDate", maturity_date AS "maturityDate",
152                    next_payment_due AS "nextPaymentDue", status,
153                    requested_at AS "requestedAt", reviewed_at AS "reviewedAt",
154                    activated_at AS "activatedAt",
155                    denial_reason AS "denialReason", approval_notes AS "approvalNotes"
156                 FROM loans WHERE investor_id = :investor_id ORDER BY created_at DESC
157            SQL;
158
159        $stmt = $this->pdo->prepare($sql);
160        if ($stmt === false) {
161            throw new RuntimeException('Failed to prepare statement');
162        }
163        $stmt->execute(['investor_id' => $investorId]);
164
165        $loans = [];
166        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
167            $loans[] = LoanData::fromRow(Row::from($row));
168        }
169
170        return $loans;
171    }
172
173    /**
174     * @return array<int, LoanData>
175     */
176    public function findActive(): array
177    {
178        $sql = <<<SQL
179                SELECT
180                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
181                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
182                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
183                    l.loan_type AS "loanType",
184                    l.requested_amount AS "requestedAmount",
185                    l.principle_amount AS "principleAmount",
186                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
187                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
188                    l.total_interest AS "totalInterest", l.total_repayment AS "totalRepayment",
189                    l.start_date AS "startDate", l.maturity_date AS "maturityDate",
190                    l.next_payment_due AS "nextPaymentDue", l.status,
191                    l.requested_at AS "requestedAt", l.activated_at AS "activatedAt"
192                FROM loans l
193                JOIN investors i ON i.investor_id = l.investor_id
194                JOIN accounts a ON a.account_id = l.account_id
195                WHERE l.status IN ('active', 'disbursed')
196                ORDER BY l.created_at DESC
197            SQL;
198
199        $stmt = $this->pdo->query($sql);
200        if ($stmt === false) {
201            throw new RuntimeException('Failed to execute query');
202        }
203
204        $loans = [];
205        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
206            $loans[] = LoanData::fromRow(Row::from($row));
207        }
208        return $loans;
209    }
210
211    /**
212     * @return array<string, string>
213     */
214    public function getConfig(): array
215    {
216        $stmt = $this->pdo->query('SELECT config_key, config_value FROM loan_config');
217        if ($stmt === false) {
218            throw new RuntimeException('Failed to execute query');
219        }
220        $config = [];
221        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
222            $rowArray = Row::from($row);
223            $key = Row::string($rowArray, 'config_key');
224            $config[$key] = Row::string($rowArray, 'config_value');
225        }
226        return $config;
227    }
228
229    /**
230     * @return list<array{key: string, value: string, description: string|null, updatedAt: string|null}>
231     */
232    public function getLoanConfig(): array
233    {
234        $sql = <<<SQL
235                SELECT config_key AS "key",
236                       config_value AS "value",
237                       description,
238                       updated_at AS "updatedAt"
239                FROM loan_config
240                ORDER BY config_id
241            SQL;
242
243        $stmt = $this->pdo->query($sql);
244        if ($stmt === false) {
245            throw new RuntimeException('Failed to execute query');
246        }
247
248        $config = [];
249        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
250            $r = Row::from($row);
251            $config[] = [
252                'key' => Row::string($r, 'key'),
253                'value' => Row::string($r, 'value'),
254                'description' => Row::nullableString($r, 'description'),
255                'updatedAt' => Row::nullableString($r, 'updatedAt'),
256            ];
257        }
258        return $config;
259    }
260
261    public function updateLoanConfig(string $key, string $value): void
262    {
263        $stmt = $this->pdo->prepare(
264            'UPDATE loan_config SET config_value = :value, updated_at = CURRENT_TIMESTAMP WHERE config_key = :key',
265        );
266        if ($stmt === false) {
267            throw new RuntimeException('Failed to prepare statement');
268        }
269        $stmt->execute(['key' => $key, 'value' => $value]);
270    }
271
272    /**
273     * @param int $loanId
274     * @return list<array<mixed>>
275     */
276    public function getPaymentSchedule(int $loanId): array
277    {
278        $sql = <<<SQL
279                SELECT schedule_id AS "scheduleId", loan_id AS "loanId", payment_number AS "paymentNumber",
280                    due_date AS "dueDate", expected_amount AS "expectedAmount",
281                    principal_portion AS "principalPortion", interest_portion AS "interestPortion",
282                    is_paid AS "isPaid", actual_payment_id AS "actualPaymentId", paid_date AS "paidDate"
283                FROM loan_payment_schedule WHERE loan_id = :loanId ORDER BY payment_number
284            SQL;
285
286        $stmt = $this->pdo->prepare($sql);
287        if ($stmt === false) {
288            throw new RuntimeException('Failed to prepare statement');
289        }
290        $stmt->execute(['loanId' => $loanId]);
291
292        $rows = [];
293        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
294            $rows[] = Row::from($row);
295        }
296
297        return $rows;
298    }
299
300    /**
301     * @return array<int, LoanData>
302     */
303    public function findPending(): array
304    {
305        $sql = <<<SQL
306                SELECT
307                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
308                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
309                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
310                    l.loan_type AS "loanType",
311                    l.requested_amount AS "requestedAmount",
312                    l.requested_term_months AS "requestedTermMonths",
313                    l.principle_amount AS "principleAmount",
314                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
315                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
316                    l.status,
317                    l.requested_at AS "requestedAt",
318                    l.denial_reason AS "denialReason", l.approval_notes AS "approvalNotes"
319                FROM loans l
320                JOIN investors i ON i.investor_id = l.investor_id
321                JOIN accounts a ON a.account_id = l.account_id
322                WHERE l.status IN ('requested', 'under_review')
323                ORDER BY l.requested_at ASC
324            SQL;
325
326        $stmt = $this->pdo->query($sql);
327        if ($stmt === false) {
328            throw new RuntimeException('Failed to execute query');
329        }
330
331        $loans = [];
332        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
333            $loans[] = LoanData::fromRow(Row::from($row));
334        }
335        return $loans;
336    }
337
338    /**
339     * @return array<int, LoanData>
340     */
341    public function findApproved(): array
342    {
343        $sql = <<<SQL
344                SELECT
345                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
346                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
347                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
348                    l.loan_type AS "loanType",
349                    l.requested_amount AS "requestedAmount",
350                    l.requested_term_months AS "requestedTermMonths",
351                    l.principle_amount AS "principleAmount",
352                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
353                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
354                    l.status,
355                    l.requested_at AS "requestedAt",
356                    l.reviewed_at AS "reviewedAt",
357                    l.approval_notes AS "approvalNotes"
358                FROM loans l
359                JOIN investors i ON i.investor_id = l.investor_id
360                JOIN accounts a ON a.account_id = l.account_id
361                WHERE l.status = 'approved'
362                ORDER BY l.reviewed_at ASC
363            SQL;
364
365        $stmt = $this->pdo->query($sql);
366        if ($stmt === false) {
367            throw new RuntimeException('Failed to execute query');
368        }
369
370        $loans = [];
371        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
372            $loans[] = LoanData::fromRow(Row::from($row));
373        }
374        return $loans;
375    }
376
377    public function approveLoan(
378        int $loanId,
379        int $adminUserId,
380        ?string $amount,
381        ?int $termMonths,
382        ?string $interestRate,
383        ?string $notes,
384    ): void {
385        $sql = <<<SQL
386                UPDATE loans SET
387                    status = 'approved',
388                    principle_amount = COALESCE(:amount, principle_amount),
389                    term_months = COALESCE(:term_months, term_months),
390                    interest_rate = COALESCE(:interest_rate, interest_rate),
391                    approval_notes = :notes,
392                    reviewed_at = CURRENT_TIMESTAMP,
393                    reviewed_by = :admin_user_id,
394                    updated_at = CURRENT_TIMESTAMP
395                WHERE loan_id = :loan_id
396            SQL;
397
398        $stmt = $this->pdo->prepare($sql);
399        if ($stmt === false) {
400            throw new RuntimeException('Failed to prepare statement');
401        }
402        $stmt->execute([
403            'loan_id' => $loanId,
404            'admin_user_id' => $adminUserId,
405            'amount' => $amount,
406            'term_months' => $termMonths,
407            'interest_rate' => $interestRate,
408            'notes' => $notes,
409        ]);
410    }
411
412    public function activateLoan(int $loanId): void
413    {
414        $stmt = $this->pdo->prepare(
415            "UPDATE loans SET status = 'disbursed', start_date = CURRENT_DATE WHERE loan_id = :loan_id",
416        );
417        if ($stmt === false) {
418            throw new RuntimeException('Failed to prepare statement');
419        }
420        $stmt->execute(['loan_id' => $loanId]);
421    }
422
423    public function denyLoan(int $loanId, int $adminUserId, string $reason): void
424    {
425        $sql = <<<SQL
426                UPDATE loans SET
427                    status = 'denied',
428                    denial_reason = :reason,
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            'reason' => $reason,
443        ]);
444    }
445
446    public function getInvestorAccountId(int $investorId): ?int
447    {
448        $stmt = $this->pdo->prepare(
449            'SELECT account_id FROM accounts WHERE investor_id = :investorId LIMIT 1',
450        );
451
452        if ($stmt === false) {
453            throw new RuntimeException('Failed to prepare statement');
454        }
455        $stmt->execute(['investorId' => $investorId]);
456        $result = $stmt->fetchColumn();
457        return $result !== false ? (int)$result : null;
458    }
459
460    /**
461     * @param int $loanId
462     * @return array<mixed>|null camelCase row from loan_payment_schedule, or null if all paid
463     */
464    public function getNextUnpaidScheduleEntry(int $loanId): ?array
465    {
466        $sql = <<<SQL
467                SELECT
468                    schedule_id AS "scheduleId",
469                    payment_number AS "paymentNumber",
470                    due_date AS "dueDate",
471                    expected_amount AS "expectedAmount",
472                    principal_portion AS "principalPortion",
473                    interest_portion AS "interestPortion"
474                FROM loan_payment_schedule
475                WHERE loan_id = :loan_id AND is_paid = false
476                ORDER BY payment_number ASC
477                LIMIT 1
478            SQL;
479
480        $stmt = $this->pdo->prepare($sql);
481        if ($stmt === false) {
482            throw new RuntimeException('Failed to prepare statement');
483        }
484        $stmt->execute(['loan_id' => $loanId]);
485        $row = $stmt->fetch(PDO::FETCH_ASSOC);
486
487        return is_array($row) ? $row : null;
488    }
489
490    /**
491     * Record a loan payment and mark the schedule entry as paid.
492     * @param int $loanId
493     * @param int $scheduleId
494     * @param string $principalPaid
495     * @param string $interestPaid
496     * @param string $amountPaid
497     * @param int $transactionId
498     * @param string $paymentMethod
499     * @param ?string $stripePaymentIntentId
500     */
501    public function recordPayment(
502        int $loanId,
503        int $scheduleId,
504        string $principalPaid,
505        string $interestPaid,
506        string $amountPaid,
507        ?int $transactionId = null,
508        string $paymentMethod = 'auto_debit',
509        ?string $stripePaymentIntentId = null,
510    ): int {
511        $sql = <<<SQL
512                INSERT INTO loan_payments (
513                    loan_id, payment_date, due_date, amount_paid,
514                    principal_paid, interest_paid, payment_method,
515                    transaction_id, stripe_payment_intent_id, status
516                )
517                SELECT
518                    :loan_id, CURRENT_DATE, due_date, :amount_paid,
519                    :principal_paid, :interest_paid, :payment_method,
520                    :transaction_id, :stripe_payment_intent_id, 'completed'
521                FROM loan_payment_schedule
522                WHERE schedule_id = :schedule_id
523                RETURNING payment_id
524            SQL;
525
526        $stmt = $this->pdo->prepare($sql);
527        if ($stmt === false) {
528            throw new RuntimeException('Failed to prepare statement');
529        }
530        $stmt->execute([
531            'loan_id' => $loanId,
532            'amount_paid' => $amountPaid,
533            'principal_paid' => $principalPaid,
534            'interest_paid' => $interestPaid,
535            'payment_method' => $paymentMethod,
536            'transaction_id' => $transactionId,
537            'stripe_payment_intent_id' => $stripePaymentIntentId,
538            'schedule_id' => $scheduleId,
539        ]);
540
541        $paymentId = (int)$stmt->fetchColumn();
542
543        // Mark schedule entry as paid
544        $stmt = $this->pdo->prepare(
545            'UPDATE loan_payment_schedule SET is_paid = true, actual_payment_id = :payment_id, paid_date = CURRENT_DATE WHERE schedule_id = :schedule_id',
546        );
547        if ($stmt === false) {
548            throw new RuntimeException('Failed to prepare statement');
549        }
550        $stmt->execute(['payment_id' => $paymentId, 'schedule_id' => $scheduleId]);
551
552        // Update next_payment_due on the loan
553        $stmt = $this->pdo->prepare(
554            "UPDATE loans SET next_payment_due = (
555                SELECT MIN(due_date) FROM loan_payment_schedule WHERE loan_id = :loan_id AND is_paid = false
556            ) WHERE loan_id = :loan_id2",
557        );
558        if ($stmt === false) {
559            throw new RuntimeException('Failed to prepare statement');
560        }
561        $stmt->execute(['loan_id' => $loanId, 'loan_id2' => $loanId]);
562
563        return $paymentId;
564    }
565}