Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 81
0.00%
0 / 10
 CRAP
0.00%
0 / 1
LoanRepository
0.00%
0 / 81
0.00%
0 / 10
 650
0.00%
0 / 1
 __construct
0.00%
0 / 1
0.00%
0 / 1
 2
 checkEligibility
0.00%
0 / 10
0.00%
0 / 1
 12
 createActiveLoan
0.00%
0 / 17
0.00%
0 / 1
 6
 generatePaymentSchedule
0.00%
0 / 4
0.00%
0 / 1
 6
 findById
0.00%
0 / 8
0.00%
0 / 1
 12
 findByInvestorId
0.00%
0 / 10
0.00%
0 / 1
 12
 findActive
0.00%
0 / 9
0.00%
0 / 1
 12
 getConfig
0.00%
0 / 7
0.00%
0 / 1
 12
 getPaymentSchedule
0.00%
0 / 7
0.00%
0 / 1
 6
 getInvestorAccountId
0.00%
0 / 8
0.00%
0 / 1
 12
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                    allow_multiple_loans AS "allowMultipleLoans",
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 ($row === false) {
40            return new LoanEligibilityData(false, false, 'Unable to check eligibility', '0', '0', '0', '0');
41        }
42
43        return LoanEligibilityData::fromRow($row);
44    }
45
46    public function createActiveLoan(
47        int $investorId,
48        int $accountId,
49        string $principleAmount,
50        int $termMonths,
51        string $interestRate,
52        string $monthlyPayment,
53        string $totalInterest,
54        string $totalRepayment,
55        string $maturityDate,
56    ): int {
57        $sql = <<<SQL
58                INSERT INTO loans (
59                    investor_id, account_id, loan_type, status,
60                    principle_amount, outstanding_balance, interest_rate,
61                    term_months, monthly_payment, total_interest, total_repayment,
62                    start_date, maturity_date, next_payment_due,
63                    requested_at, activated_at
64                ) VALUES (
65                    :investor_id, :account_id, 'secured', 'active',
66                    :principle_amount, :total_repayment, :interest_rate,
67                    :term_months, :monthly_payment, :total_interest, :total_repayment,
68                    CURRENT_DATE, :maturity_date, CURRENT_DATE + INTERVAL '1 month',
69                    CURRENT_TIMESTAMP, 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            'principle_amount' => $principleAmount,
82            'interest_rate' => $interestRate,
83            'term_months' => $termMonths,
84            'monthly_payment' => $monthlyPayment,
85            'total_interest' => $totalInterest,
86            'total_repayment' => $totalRepayment,
87            'maturity_date' => $maturityDate,
88        ]);
89
90        return (int)$stmt->fetchColumn();
91    }
92
93    public function generatePaymentSchedule(int $loanId): void
94    {
95        $stmt = $this->pdo->prepare('SELECT generate_payment_schedule(:loanId)');
96        if ($stmt === false) {
97            throw new RuntimeException('Failed to prepare statement');
98        }
99        $stmt->execute(['loanId' => $loanId]);
100    }
101
102    public function findById(int $loanId): ?LoanData
103    {
104        $sql = <<<SQL
105                SELECT
106                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
107                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
108                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
109                    l.loan_type AS "loanType",
110                    l.principle_amount AS "principleAmount",
111                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
112                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
113                    l.total_interest AS "totalInterest", 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", l.requested_at AS "requestedAt",
117                    l.activated_at AS "activatedAt",
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 $row !== false ? 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                    principle_amount AS "principleAmount",
146                    outstanding_balance AS "outstandingBalance", interest_rate AS "interestRate",
147                    term_months AS "termMonths", monthly_payment AS "monthlyPayment",
148                    total_interest AS "totalInterest", total_repayment AS "totalRepayment",
149                    start_date AS "startDate", maturity_date AS "maturityDate",
150                    next_payment_due AS "nextPaymentDue", status,
151                    requested_at AS "requestedAt",
152                    activated_at AS "activatedAt"
153                 FROM loans WHERE investor_id = :investor_id ORDER BY created_at DESC
154            SQL;
155
156        $stmt = $this->pdo->prepare($sql);
157        if ($stmt === false) {
158            throw new RuntimeException('Failed to prepare statement');
159        }
160        $stmt->execute(['investor_id' => $investorId]);
161
162        $loans = [];
163        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
164            $loans[] = LoanData::fromRow($row);
165        }
166
167        return $loans;
168    }
169
170    /**
171     * @return array<int, LoanData>
172     */
173    public function findActive(): array
174    {
175        $sql = <<<SQL
176                SELECT
177                    l.loan_id AS "loanId", l.account_id AS "accountId", l.investor_id AS "investorId",
178                    i.first_name || ' ' || i.last_name AS "investorName", i.email AS "investorEmail",
179                    a.account_number AS "accountNumber", a.balance AS "accountBalance",
180                    l.loan_type AS "loanType",
181                    l.principle_amount AS "principleAmount",
182                    l.outstanding_balance AS "outstandingBalance", l.interest_rate AS "interestRate",
183                    l.term_months AS "termMonths", l.monthly_payment AS "monthlyPayment",
184                    l.next_payment_due AS "nextPaymentDue", l.status,
185                    l.requested_at AS "requestedAt", l.activated_at AS "activatedAt"
186                FROM loans l
187                JOIN investors i ON i.investor_id = l.investor_id
188                JOIN accounts a ON a.account_id = l.account_id
189                WHERE l.status = 'active'
190                ORDER BY l.created_at DESC
191            SQL;
192
193        $stmt = $this->pdo->query($sql);
194        if ($stmt === false) {
195            throw new RuntimeException('Failed to execute query');
196        }
197
198        $loans = [];
199        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
200            $loans[] = LoanData::fromRow($row);
201        }
202        return $loans;
203    }
204
205    /**
206     * @return array<string, string>
207     */
208    public function getConfig(): array
209    {
210        $stmt = $this->pdo->query('SELECT config_key, config_value FROM loan_config');
211        if ($stmt === false) {
212            throw new RuntimeException('Failed to execute query');
213        }
214        $config = [];
215        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
216            $config[$row['config_key']] = $row['config_value'];
217        }
218        return $config;
219    }
220
221    /**
222     * @param int $loanId
223     * @return array<int, array<string, mixed>>
224     */
225    public function getPaymentSchedule(int $loanId): array
226    {
227        $sql = <<<SQL
228                SELECT schedule_id AS "scheduleId", loan_id AS "loanId", payment_number AS "paymentNumber",
229                    due_date AS "dueDate", expected_amount AS "expectedAmount",
230                    principal_portion AS "principalPortion", interest_portion AS "interestPortion",
231                    is_paid AS "isPaid", actual_payment_id AS "actualPaymentId", paid_date AS "paidDate"
232                FROM loan_payment_schedule WHERE loan_id = :loanId ORDER BY payment_number
233            SQL;
234
235        $stmt = $this->pdo->prepare($sql);
236        if ($stmt === false) {
237            throw new RuntimeException('Failed to prepare statement');
238        }
239        $stmt->execute(['loanId' => $loanId]);
240        return $stmt->fetchAll(PDO::FETCH_ASSOC);
241    }
242
243    public function getInvestorAccountId(int $investorId): ?int
244    {
245        $stmt = $this->pdo->prepare(
246            'SELECT account_id FROM accounts WHERE investor_id = :investorId LIMIT 1',
247        );
248
249        if ($stmt === false) {
250            throw new RuntimeException('Failed to prepare statement');
251        }
252        $stmt->execute(['investorId' => $investorId]);
253        $result = $stmt->fetchColumn();
254        return $result !== false ? (int)$result : null;
255    }
256}