Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
87.93%
102 / 116
7.14%
1 / 14
 CRAP
0.00%
0 / 1
LoanRepository
87.93%
102 / 116
7.14%
1 / 14
 36.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
85.71%
6 / 7
0.00%
0 / 1
 3.03
 getPaymentSchedule
85.71%
6 / 7
0.00%
0 / 1
 2.01
 findPending
88.89%
8 / 9
0.00%
0 / 1
 3.01
 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
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 PDO;
10use RuntimeException;
11
12final readonly class LoanRepository
13{
14    public function __construct(
15        private PDO $pdo,
16    ) {}
17
18    public function checkEligibility(int $investorId): LoanEligibilityData
19    {
20        $sql = <<<SQL
21                SELECT
22                    eligible,
23                    reason,
24                    max_loan_amount AS "maxLoanAmount",
25                    current_balance AS "currentBalance",
26                    ltv_percentage AS "ltvPercentage",
27                    min_required_balance AS "minRequiredBalance"
28                FROM check_loan_eligibility(:investor_id)
29            SQL;
30
31        $stmt = $this->pdo->prepare($sql);
32        if ($stmt === false) {
33            throw new RuntimeException('Failed to prepare statement');
34        }
35        $stmt->execute(['investor_id' => $investorId]);
36        $row = $stmt->fetch(PDO::FETCH_ASSOC);
37
38        if ($row === false) {
39            return new LoanEligibilityData(false, true, 'Unable to check eligibility', '0', '0', '0', '0');
40        }
41
42        $row['allowMultipleLoans'] = true;
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    ): int {
54        $sql = <<<SQL
55                INSERT INTO loans (
56                    investor_id, account_id, loan_type, status,
57                    requested_amount, requested_term_months,
58                    principle_amount, interest_rate, term_months,
59                    outstanding_balance, monthly_payment,
60                    start_date, maturity_date,
61                    requested_at
62                ) VALUES (
63                    :investor_id, :account_id, 'secured', 'requested',
64                    :amount, :term_months,
65                    :amount, :interest_rate, :term_months,
66                    0, 0,
67                    CURRENT_DATE, CURRENT_DATE,
68                    CURRENT_TIMESTAMP
69                )
70                RETURNING loan_id
71            SQL;
72
73        $stmt = $this->pdo->prepare($sql);
74        if ($stmt === false) {
75            throw new RuntimeException('Failed to prepare statement');
76        }
77        $stmt->execute([
78            'investor_id' => $investorId,
79            'account_id' => $accountId,
80            'amount' => $amount,
81            'interest_rate' => $interestRate,
82            'term_months' => $termMonths,
83        ]);
84
85        return (int)$stmt->fetchColumn();
86    }
87
88    public function generatePaymentSchedule(int $loanId): void
89    {
90        $stmt = $this->pdo->prepare('SELECT generate_payment_schedule(:loanId)');
91        if ($stmt === false) {
92            throw new RuntimeException('Failed to prepare statement');
93        }
94        $stmt->execute(['loanId' => $loanId]);
95    }
96
97    public function findById(int $loanId): ?LoanData
98    {
99        $sql = <<<SQL
100                SELECT
101                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
102                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
103                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
104                    l.loan_type AS "loanType",
105                    l.requested_amount AS "requestedAmount",
106                    l.requested_term_months AS "requestedTermMonths",
107                    l.principle_amount AS "principleAmount",
108                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
109                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
110                    l.total_interest AS "totalInterest", l.total_repayment AS "totalRepayment",
111                    l.start_date AS "startDate", l.maturity_date AS "maturityDate",
112                    l.next_payment_due AS "nextPaymentDue", l.status,
113                    l.collateral_description AS "collateralDescription",
114                    l.requested_at AS "requestedAt", l.reviewed_at AS "reviewedAt",
115                    l.reviewed_by AS "reviewedBy", l.activated_at AS "activatedAt",
116                    l.denial_reason AS "denialReason", l.approval_notes AS "approvalNotes",
117                    l.created_at AS "createdAt", l.updated_at AS "updatedAt"
118                FROM loans l
119                 JOIN investors i ON i.investor_id = l.investor_id
120                 JOIN accounts a ON a.account_id = l.account_id
121                 WHERE l.loan_id = :loan_id
122            SQL;
123
124        $stmt = $this->pdo->prepare($sql);
125        if ($stmt === false) {
126            throw new RuntimeException('Failed to prepare statement');
127        }
128        $stmt->execute(['loan_id' => $loanId]);
129        $row = $stmt->fetch(PDO::FETCH_ASSOC);
130
131        return $row !== false ? LoanData::fromRow($row) : null;
132    }
133
134    /**
135     * @param int $investorId
136     * @return array<int, LoanData>
137     */
138    public function findByInvestorId(int $investorId): array
139    {
140        $sql = <<<SQL
141                SELECT
142                    loan_id AS "loanId", account_id AS "accountId", investor_id AS "investorId",
143                    loan_type AS "loanType",
144                    requested_amount AS "requestedAmount",
145                    requested_term_months AS "requestedTermMonths",
146                    principle_amount AS "principleAmount",
147                    outstanding_balance AS "outstandingBalance", interest_rate AS "interestRate",
148                    term_months AS "termMonths", monthly_payment AS "monthlyPayment",
149                    total_interest AS "totalInterest", total_repayment AS "totalRepayment",
150                    start_date AS "startDate", maturity_date AS "maturityDate",
151                    next_payment_due AS "nextPaymentDue", status,
152                    requested_at AS "requestedAt", reviewed_at AS "reviewedAt",
153                    activated_at AS "activatedAt",
154                    denial_reason AS "denialReason", approval_notes AS "approvalNotes"
155                 FROM loans WHERE investor_id = :investor_id ORDER BY created_at DESC
156            SQL;
157
158        $stmt = $this->pdo->prepare($sql);
159        if ($stmt === false) {
160            throw new RuntimeException('Failed to prepare statement');
161        }
162        $stmt->execute(['investor_id' => $investorId]);
163
164        $loans = [];
165        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
166            $loans[] = LoanData::fromRow($row);
167        }
168
169        return $loans;
170    }
171
172    /**
173     * @return array<int, LoanData>
174     */
175    public function findActive(): array
176    {
177        $sql = <<<SQL
178                SELECT
179                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
180                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
181                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
182                    l.loan_type AS "loanType",
183                    l.requested_amount AS "requestedAmount",
184                    l.principle_amount AS "principleAmount",
185                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
186                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
187                    l.total_interest AS "totalInterest", l.total_repayment AS "totalRepayment",
188                    l.start_date AS "startDate", l.maturity_date AS "maturityDate",
189                    l.next_payment_due AS "nextPaymentDue", l.status,
190                    l.requested_at AS "requestedAt", l.activated_at AS "activatedAt"
191                FROM loans l
192                JOIN investors i ON i.investor_id = l.investor_id
193                JOIN accounts a ON a.account_id = l.account_id
194                WHERE l.status = 'active'
195                ORDER BY l.created_at DESC
196            SQL;
197
198        $stmt = $this->pdo->query($sql);
199        if ($stmt === false) {
200            throw new RuntimeException('Failed to execute query');
201        }
202
203        $loans = [];
204        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
205            $loans[] = LoanData::fromRow($row);
206        }
207        return $loans;
208    }
209
210    /**
211     * @return array<string, string>
212     */
213    public function getConfig(): array
214    {
215        $stmt = $this->pdo->query('SELECT config_key, config_value FROM loan_config');
216        if ($stmt === false) {
217            throw new RuntimeException('Failed to execute query');
218        }
219        $config = [];
220        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
221            $config[$row['config_key']] = $row['config_value'];
222        }
223        return $config;
224    }
225
226    /**
227     * @param int $loanId
228     * @return array<int, array<string, mixed>>
229     */
230    public function getPaymentSchedule(int $loanId): array
231    {
232        $sql = <<<SQL
233                SELECT schedule_id AS "scheduleId", loan_id AS "loanId", payment_number AS "paymentNumber",
234                    due_date AS "dueDate", expected_amount AS "expectedAmount",
235                    principal_portion AS "principalPortion", interest_portion AS "interestPortion",
236                    is_paid AS "isPaid", actual_payment_id AS "actualPaymentId", paid_date AS "paidDate"
237                FROM loan_payment_schedule WHERE loan_id = :loanId ORDER BY payment_number
238            SQL;
239
240        $stmt = $this->pdo->prepare($sql);
241        if ($stmt === false) {
242            throw new RuntimeException('Failed to prepare statement');
243        }
244        $stmt->execute(['loanId' => $loanId]);
245        return $stmt->fetchAll(PDO::FETCH_ASSOC);
246    }
247
248    /**
249     * @return array<int, LoanData>
250     */
251    public function findPending(): array
252    {
253        $sql = <<<SQL
254                SELECT
255                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
256                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
257                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
258                    l.loan_type AS "loanType",
259                    l.requested_amount AS "requestedAmount",
260                    l.requested_term_months AS "requestedTermMonths",
261                    l.principle_amount AS "principleAmount",
262                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
263                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
264                    l.status,
265                    l.requested_at AS "requestedAt",
266                    l.denial_reason AS "denialReason", l.approval_notes AS "approvalNotes"
267                FROM loans l
268                JOIN investors i ON i.investor_id = l.investor_id
269                JOIN accounts a ON a.account_id = l.account_id
270                WHERE l.status IN ('requested', 'under_review')
271                ORDER BY l.requested_at ASC
272            SQL;
273
274        $stmt = $this->pdo->query($sql);
275        if ($stmt === false) {
276            throw new RuntimeException('Failed to execute query');
277        }
278
279        $loans = [];
280        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
281            $loans[] = LoanData::fromRow($row);
282        }
283        return $loans;
284    }
285
286    public function approveLoan(
287        int $loanId,
288        int $adminUserId,
289        ?string $amount,
290        ?int $termMonths,
291        ?string $interestRate,
292        ?string $notes,
293    ): void {
294        $sql = <<<SQL
295                UPDATE loans SET
296                    status = 'approved',
297                    principle_amount = COALESCE(:amount, principle_amount),
298                    term_months = COALESCE(:term_months, term_months),
299                    interest_rate = COALESCE(:interest_rate, interest_rate),
300                    approval_notes = :notes,
301                    reviewed_at = CURRENT_TIMESTAMP,
302                    reviewed_by = :admin_user_id,
303                    updated_at = CURRENT_TIMESTAMP
304                WHERE loan_id = :loan_id
305            SQL;
306
307        $stmt = $this->pdo->prepare($sql);
308        if ($stmt === false) {
309            throw new RuntimeException('Failed to prepare statement');
310        }
311        $stmt->execute([
312            'loan_id' => $loanId,
313            'admin_user_id' => $adminUserId,
314            'amount' => $amount,
315            'term_months' => $termMonths,
316            'interest_rate' => $interestRate,
317            'notes' => $notes,
318        ]);
319    }
320
321    public function activateLoan(int $loanId): void
322    {
323        $stmt = $this->pdo->prepare(
324            "UPDATE loans SET status = 'active', start_date = CURRENT_DATE WHERE loan_id = :loan_id",
325        );
326        if ($stmt === false) {
327            throw new RuntimeException('Failed to prepare statement');
328        }
329        $stmt->execute(['loan_id' => $loanId]);
330    }
331
332    public function denyLoan(int $loanId, int $adminUserId, string $reason): void
333    {
334        $sql = <<<SQL
335                UPDATE loans SET
336                    status = 'denied',
337                    denial_reason = :reason,
338                    reviewed_at = CURRENT_TIMESTAMP,
339                    reviewed_by = :admin_user_id,
340                    updated_at = CURRENT_TIMESTAMP
341                WHERE loan_id = :loan_id
342            SQL;
343
344        $stmt = $this->pdo->prepare($sql);
345        if ($stmt === false) {
346            throw new RuntimeException('Failed to prepare statement');
347        }
348        $stmt->execute([
349            'loan_id' => $loanId,
350            'admin_user_id' => $adminUserId,
351            'reason' => $reason,
352        ]);
353    }
354
355    public function getInvestorAccountId(int $investorId): ?int
356    {
357        $stmt = $this->pdo->prepare(
358            'SELECT account_id FROM accounts WHERE investor_id = :investorId LIMIT 1',
359        );
360
361        if ($stmt === false) {
362            throw new RuntimeException('Failed to prepare statement');
363        }
364        $stmt->execute(['investorId' => $investorId]);
365        $result = $stmt->fetchColumn();
366        return $result !== false ? (int)$result : null;
367    }
368}