Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
95.20%
119 / 125
85.00%
17 / 20
 CRAP
0.00%
0 / 1
AccountRepository
95.20%
119 / 125
85.00%
17 / 20
 31
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 accountNumberExists
100.00%
4 / 4
100.00%
1 / 1
 1
 createAccount
100.00%
14 / 14
100.00%
1 / 1
 1
 findAccountById
100.00%
6 / 6
100.00%
1 / 1
 2
 findAccountByInvestorId
100.00%
6 / 6
100.00%
1 / 1
 2
 findAccountByAccountNumber
100.00%
6 / 6
100.00%
1 / 1
 2
 investorHasAccount
100.00%
4 / 4
100.00%
1 / 1
 1
 getAccountSummary
100.00%
6 / 6
100.00%
1 / 1
 2
 updateAccountStatus
100.00%
7 / 7
100.00%
1 / 1
 1
 updateBankAccountStatus
100.00%
7 / 7
100.00%
1 / 1
 1
 setBankAccountId
100.00%
7 / 7
100.00%
1 / 1
 1
 updateInterestRate
100.00%
7 / 7
100.00%
1 / 1
 1
 updateLoanToValueRatio
100.00%
7 / 7
100.00%
1 / 1
 1
 getAvailableForLoan
100.00%
4 / 4
100.00%
1 / 1
 1
 hasActiveLoans
100.00%
5 / 5
100.00%
1 / 1
 1
 getBalanceHistory
100.00%
8 / 8
100.00%
1 / 1
 2
 canCloseAccount
100.00%
5 / 5
100.00%
1 / 1
 1
 getTotalAccounts
83.33%
5 / 6
0.00%
0 / 1
 3.04
 getTotalBalance
83.33%
5 / 6
0.00%
0 / 1
 3.04
 generateUniqueAccountNumber
55.56%
5 / 9
0.00%
0 / 1
 3.79
1<?php
2
3declare(strict_types=1);
4
5namespace App\Domain\Account\Repository;
6
7use App\Domain\Account\Data\AccountData;
8use App\Domain\Account\Data\BalanceHistoryPointData;
9use App\Support\Row;
10use PDO;
11use Random\RandomException;
12use RuntimeException;
13
14use function sprintf;
15
16/**
17 * Repository for account data access operations.
18 *
19 * Returns monetary values as TEXT strings to preserve exact decimal precision
20 * from Postgres NUMERIC types - critical for financial applications.
21 */
22final class AccountRepository
23{
24    private PDO $pdo;
25
26    public function __construct(PDO $pdo)
27    {
28        $this->pdo = $pdo;
29    }
30
31    /**
32     * Check if an account number already exists.
33     *
34     * @param string $accountNumber
35     *
36     * @return bool
37     */
38    public function accountNumberExists(string $accountNumber): bool
39    {
40        $sql = 'SELECT EXISTS(SELECT 1 FROM accounts WHERE account_number = :account_number)';
41        $stmt = $this->pdo->prepare($sql);
42        $stmt->execute(['account_number' => $accountNumber]);
43
44        return (bool)$stmt->fetchColumn();
45    }
46
47    /**
48     * Create an account for an investor.
49     *
50     * @param array<string, mixed> $data
51     *
52     * @throws RandomException
53     * @throws RuntimeException If account number generation fails
54     *
55     * @return int Account ID
56     */
57    public function createAccount(array $data): int
58    {
59        // Generate unique account number
60        $accountNumber = $this->generateUniqueAccountNumber();
61
62        $sql = '
63        INSERT INTO accounts (
64            investor_id,
65            account_number,
66            balance,
67            available_balance,
68            interest_rate,
69            loan_to_value_ratio,
70            status
71        ) VALUES (
72            :investor_id,
73            :account_number,
74            :balance,
75            :available_balance,
76            :interest_rate,
77            :loan_to_value_ratio,
78            :status
79        )
80        RETURNING account_id
81    ';
82
83        $stmt = $this->pdo->prepare($sql);
84        $stmt->execute([
85            'investor_id' => $data['investorId'],
86            'account_number' => $accountNumber,
87            'balance' => $data['balance'] ?? 0.00,
88            'available_balance' => $data['availableBalance'] ?? $data['balance'] ?? 0.00,
89            'interest_rate' => $data['interestRate'] ?? null,
90            'loan_to_value_ratio' => $data['loanToValueRatio'] ?? 0.80,
91            'status' => $data['status'] ?? 'pending',
92        ]);
93
94        return (int)$stmt->fetchColumn();
95    }
96
97    public function findAccountById(int $accountId): ?AccountData
98    {
99        // Query database with snake_case, convert to camelCase in SELECT
100        $sql = 'SELECT
101                    account_id as "accountId",
102                    investor_id as "investorId",
103                    account_number as "accountNumber",
104                    balance::TEXT as balance,
105                    available_balance::TEXT as "availableBalance",
106                    available_for_loan::TEXT as "availableForLoan",
107                    COALESCE(interest_rate, get_loan_config(\'account_yield_rate\'))::TEXT as "interestRate",
108                    loan_to_value_ratio::TEXT as "loanToValueRatio",
109                    currency,
110                    opened_date as "openedDate",
111                    status,
112                    bank_account_id as "bankAccountId",
113                    bank_account_status as "bankAccountStatus",
114                    created_at as "createdAt",
115                    updated_at as "updatedAt"
116                FROM accounts
117                WHERE account_id = :account_id';
118
119        $stmt = $this->pdo->prepare($sql);
120        $stmt->execute(['account_id' => $accountId]);
121
122        $row = $stmt->fetch(PDO::FETCH_ASSOC);
123
124        return is_array($row) ? new AccountData($row) : null;
125    }
126
127    public function findAccountByInvestorId(int $investorId): ?AccountData
128    {
129        // Cast numeric fields to TEXT for exact decimal precision
130        $sql = 'SELECT
131                    account_id as "accountId",
132                    investor_id as "investorId",
133                    account_number as "accountNumber",
134                    balance::TEXT as balance,
135                    available_balance::TEXT as "availableBalance",
136                    available_for_loan::TEXT as "availableForLoan",
137                    COALESCE(interest_rate, get_loan_config(\'account_yield_rate\'))::TEXT as "interestRate",
138                    loan_to_value_ratio::TEXT as "loanToValueRatio",
139                    currency,
140                    opened_date as "openedDate",
141                    status,
142                    bank_account_id as "bankAccountId",
143                    bank_account_status as "bankAccountStatus",
144                    created_at as "createdAt",
145                    updated_at as "updatedAt"
146                FROM accounts
147                WHERE investor_id = :investor_id';
148
149        $stmt = $this->pdo->prepare($sql);
150        $stmt->execute(['investor_id' => $investorId]);
151
152        $row = $stmt->fetch(PDO::FETCH_ASSOC);
153
154        return is_array($row) ? new AccountData($row) : null;
155    }
156
157    public function findAccountByAccountNumber(string $accountNumber): ?AccountData
158    {
159        $sql = 'SELECT
160                    account_id as "accountId",
161                    investor_id as "investorId",
162                    account_number as "accountNumber",
163                    balance::TEXT as balance,
164                    available_balance::TEXT as "availableBalance",
165                    available_for_loan::TEXT as "availableForLoan",
166                    COALESCE(interest_rate, get_loan_config(\'account_yield_rate\'))::TEXT as "interestRate",
167                    loan_to_value_ratio::TEXT as "loanToValueRatio",
168                    currency,
169                    opened_date as "openedDate",
170                    status,
171                    bank_account_id as "bankAccountId",
172                    bank_account_status as "bankAccountStatus",
173                    created_at as "createdAt",
174                    updated_at as "updatedAt"
175                FROM accounts
176                WHERE account_number = :account_number';
177
178        $stmt = $this->pdo->prepare($sql);
179        $stmt->execute(['account_number' => $accountNumber]);
180
181        $row = $stmt->fetch(PDO::FETCH_ASSOC);
182
183        return is_array($row) ? new AccountData($row) : null;
184    }
185
186    public function investorHasAccount(int $investorId): bool
187    {
188        $sql = 'SELECT EXISTS(SELECT 1 FROM accounts WHERE investor_id = :investor_id)';
189        $stmt = $this->pdo->prepare($sql);
190        $stmt->execute(['investor_id' => $investorId]);
191
192        return (bool)$stmt->fetchColumn();
193    }
194
195    /**
196     * Get a comprehensive account summary with calculated loan metrics.
197     *
198     * @param int $accountId
199     * @return array<mixed>|null camelCase summary data, or null if not found
200     */
201    public function getAccountSummary(int $accountId): ?array
202    {
203        $sql = "SELECT
204                    a.account_id as \"accountId\",
205                    a.investor_id as \"investorId\",
206                    a.account_number as \"accountNumber\",
207                    a.balance::TEXT as balance,
208                    a.available_balance::TEXT as \"availableBalance\",
209                    a.available_for_loan::TEXT as \"availableForLoan\",
210                    (a.balance * a.loan_to_value_ratio)::NUMERIC(15,2)::TEXT as \"maxLoanAmount\",
211                    (a.balance * a.loan_to_value_ratio - a.available_for_loan)::NUMERIC(15,2)::TEXT
212                        as \"totalOutstandingLoans\",
213                    COALESCE((
214                        SELECT SUM(amount)
215                        FROM transactions t
216                        WHERE t.account_id = a.account_id
217                          AND t.transaction_type = 'interest'
218                          AND t.status = 'completed'
219                          AND t.created_at >= DATE_TRUNC('year', CURRENT_DATE)
220                    ), 0)::NUMERIC(15,2)::TEXT as \"earningsYtd\",
221                    COALESCE((
222                        SELECT SUM(lp.interest_paid)
223                        FROM lending.loan_payments lp
224                        JOIN lending.loans l ON l.loan_id = lp.loan_id
225                        WHERE l.investor_id = a.investor_id
226                          AND lp.status = 'completed'
227                          AND lp.payment_date >= DATE_TRUNC('year', CURRENT_DATE)
228                    ), 0)::NUMERIC(15,2)::TEXT as \"interestPaidYtd\",
229                    COALESCE(a.interest_rate, get_loan_config('account_yield_rate'))::TEXT as \"interestRate\",
230                    a.loan_to_value_ratio::TEXT as \"loanToValueRatio\",
231                    get_loan_config('default_interest_rate')::TEXT as \"loanInterestRate\",
232                    a.status,
233                    (a.available_for_loan > 0) as \"hasAvailableCredit\",
234                    (a.balance * a.loan_to_value_ratio > a.available_for_loan) as \"hasActiveLoans\",
235                    COALESCE((
236                        SELECT SUM(l.outstanding_balance)
237                        FROM lending.loans l
238                        WHERE l.investor_id = a.investor_id
239                          AND l.status IN ('active', 'disbursed')
240                    ), 0)::NUMERIC(15,2)::TEXT as \"lendingBalance\",
241                    EXISTS (
242                        SELECT 1
243                        FROM lending.disbursements d
244                        JOIN lending.loans l ON l.loan_id = d.loan_id
245                        WHERE l.investor_id = a.investor_id
246                          AND d.status = 'pending_disbursement'
247                    ) as \"lendingPendingDisbursement\",
248                    (
249                        SELECT MIN(ps.due_date)::TEXT
250                        FROM lending.loan_payment_schedule ps
251                        JOIN lending.loans l ON l.loan_id = ps.loan_id
252                        WHERE l.investor_id = a.investor_id
253                          AND ps.is_paid = false
254                    ) as \"nextLoanPaymentDue\"
255                FROM accounts a
256                WHERE a.account_id = :account_id";
257
258        $stmt = $this->pdo->prepare($sql);
259        $stmt->execute(['account_id' => $accountId]);
260
261        $row = $stmt->fetch(PDO::FETCH_ASSOC);
262
263        return is_array($row) ? $row : null;
264    }
265
266    /**
267     * Updates the status of an account in the database.
268     *
269     * @param int $accountId the ID of the account to update
270     * @param string $status The new status to assign to the account. Valid values are: pending, active, frozen, closed.
271     *
272     * @return bool returns true if the update was successful, false otherwise
273     */
274    public function updateAccountStatus(int $accountId, string $status): bool
275    {
276        // Database uses snake_case
277        $sql = 'UPDATE accounts
278                SET status = :status, updated_at = CURRENT_TIMESTAMP
279                WHERE account_id = :account_id';
280
281        $stmt = $this->pdo->prepare($sql);
282
283        return $stmt->execute([
284            'status' => $status,
285            'account_id' => $accountId,
286        ]);
287    }
288
289    /**
290     * Updates the bank account status for the given account ID.
291     *
292     * @param int $accountId the unique identifier of the bank account to update
293     * @param string $status the new status to set for the bank account
294     *
295     * @return bool returns true if the update was successful, false otherwise
296     */
297    public function updateBankAccountStatus(int $accountId, string $status): bool
298    {
299        $sql = 'UPDATE accounts
300                SET bank_account_status = :status, updated_at = CURRENT_TIMESTAMP
301                WHERE account_id = :account_id';
302
303        $stmt = $this->pdo->prepare($sql);
304
305        return $stmt->execute([
306            'status' => $status,
307            'account_id' => $accountId,
308        ]);
309    }
310
311    public function setBankAccountId(int $accountId, string $bankAccountId): bool
312    {
313        $sql = 'UPDATE accounts
314                SET bank_account_id = :bank_account_id,
315                    bank_account_status = \'bank_created\',
316                    updated_at = CURRENT_TIMESTAMP
317                WHERE account_id = :account_id';
318
319        $stmt = $this->pdo->prepare($sql);
320
321        return $stmt->execute([
322            'bank_account_id' => $bankAccountId,
323            'account_id' => $accountId,
324        ]);
325    }
326
327    public function updateInterestRate(int $accountId, string $rate): bool
328    {
329        $sql = 'UPDATE accounts
330                SET interest_rate = :rate, updated_at = CURRENT_TIMESTAMP
331                WHERE account_id = :account_id';
332
333        $stmt = $this->pdo->prepare($sql);
334
335        return $stmt->execute([
336            'rate' => $rate,
337            'account_id' => $accountId,
338        ]);
339    }
340
341    public function updateLoanToValueRatio(int $accountId, string $ratio): bool
342    {
343        // Trigger will automatically recalculate available_for_loan
344        $sql = 'UPDATE accounts
345                SET loan_to_value_ratio = :ratio, updated_at = CURRENT_TIMESTAMP
346                WHERE account_id = :account_id';
347
348        $stmt = $this->pdo->prepare($sql);
349
350        return $stmt->execute([
351            'ratio' => $ratio,
352            'account_id' => $accountId,
353        ]);
354    }
355
356    /**
357     * Get the available loan amount for an account.
358     *
359     * Note: This is now stored in the available_for_loan column and maintained
360     * by triggers. This method is kept for backward compatibility but simply
361     * reads the column value.
362     *
363     * @param int $accountId
364     *
365     * @return string Available loan amount as a string
366     */
367    public function getAvailableForLoan(int $accountId): string
368    {
369        $sql = 'SELECT available_for_loan::TEXT FROM accounts WHERE account_id = :account_id';
370        $stmt = $this->pdo->prepare($sql);
371        $stmt->execute(['account_id' => $accountId]);
372
373        return (string)$stmt->fetchColumn();
374    }
375
376    /**
377     * Check if an account has active loans.
378     *
379     * Simplified: if available_for_loan < max_loan_amount, there are active loans.
380     *
381     * @param int $accountId
382     *
383     * @return bool
384     */
385    public function hasActiveLoans(int $accountId): bool
386    {
387        $sql = 'SELECT (balance * loan_to_value_ratio) > available_for_loan
388                FROM accounts
389                WHERE account_id = :account_id';
390
391        $stmt = $this->pdo->prepare($sql);
392        $stmt->execute(['account_id' => $accountId]);
393
394        return (bool)$stmt->fetchColumn();
395    }
396
397    /**
398     * Get balance history for an account (for charting).
399     *
400     * @param int $accountId The account ID
401     *
402     * @return BalanceHistoryPointData[]
403     */
404    public function getBalanceHistory(int $accountId): array
405    {
406        $sql = "
407        SELECT DISTINCT ON (DATE(created_at))
408            DATE(created_at)::TEXT as date,
409            balance_after as value
410        FROM transactions
411        WHERE account_id = :account_id
412          AND status = 'completed'
413        ORDER BY DATE(created_at), created_at DESC
414    ";
415
416        $stmt = $this->pdo->prepare($sql);
417        $stmt->execute(['account_id' => $accountId]);
418
419        $history = [];
420        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
421            $history[] = new BalanceHistoryPointData(Row::from($row));
422        }
423
424        return $history;
425    }
426
427    /**
428     * Check if an account can be closed.
429     *
430     * Simplified: balance must be 0, and available_for_loan must equal max_loan_amount
431     * (meaning no outstanding loans).
432     *
433     * @param int $accountId
434     *
435     * @return bool
436     */
437    public function canCloseAccount(int $accountId): bool
438    {
439        $sql = 'SELECT balance = 0
440                       AND available_for_loan = (balance * loan_to_value_ratio)
441                FROM accounts
442                WHERE account_id = :account_id';
443
444        $stmt = $this->pdo->prepare($sql);
445        $stmt->execute(['account_id' => $accountId]);
446
447        return (bool)$stmt->fetchColumn();
448    }
449
450    public function getTotalAccounts(): int
451    {
452        $sql = 'SELECT COUNT(*) FROM accounts';
453        $stmt = $this->pdo->query($sql);
454
455        if ($stmt === false) {
456            return 0;
457        }
458
459        $result = $stmt->fetchColumn();
460
461        return $result !== false ? (int)$result : 0;
462    }
463
464    public function getTotalBalance(): string
465    {
466        $sql = 'SELECT COALESCE(SUM(balance), 0)::TEXT FROM accounts';
467        $stmt = $this->pdo->query($sql);
468
469        if ($stmt === false) {
470            return '0.00';
471        }
472
473        $result = $stmt->fetchColumn();
474
475        return $result !== false ? (string)$result : '0.00';
476    }
477
478    /**
479     * Generate a unique account number with retry on collision.
480     *
481     * Format: INV-XXXXX (where X is 0-9)
482     * Range: INV-00001 to INV-99999 (99,999 possible accounts)
483     *
484     * @param int $maxAttempts Maximum retry attempts before failing
485     *
486     * @throws RandomException If no source of randomness is available
487     * @throws RuntimeException If unable to generate unique number after max attempts
488     *
489     * @return string Unique account number
490     */
491    private function generateUniqueAccountNumber(int $maxAttempts = 10): string
492    {
493        for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
494            // Generate random 5-digit number (1-99999)
495            $number = random_int(1, 99999);
496            $accountNumber = sprintf('INV-%05d', $number);
497
498            // Check if it exists
499            if (!$this->accountNumberExists($accountNumber)) {
500                return $accountNumber;
501            }
502
503            // Log collision for monitoring (optional)
504            // error_log("Account number collision on attempt {$attempt}: {$accountNumber}");
505        }
506
507        // If we get here, we couldn't find a unique number
508        throw new RuntimeException(
509            "Failed to generate unique account number after {$maxAttempts} attempts. "
510            . 'Consider expanding the account number range.',
511        );
512    }
513
514}