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(a.interest_rate, get_loan_config('account_yield_rate'))::TEXT as \"interestRate\",
222                    a.loan_to_value_ratio::TEXT as \"loanToValueRatio\",
223                    COALESCE((
224                        SELECT config_value::NUMERIC
225                        FROM loan_config
226                        WHERE config_key = 'default_interest_rate'
227                    ), 8.00)::TEXT as \"loanInterestRate\",
228                    a.status,
229                    (a.available_for_loan > 0) as \"hasAvailableCredit\",
230                    (a.balance * a.loan_to_value_ratio > a.available_for_loan) as \"hasActiveLoans\"
231                FROM accounts a
232                WHERE a.account_id = :account_id";
233
234        $stmt = $this->pdo->prepare($sql);
235        $stmt->execute(['account_id' => $accountId]);
236
237        $row = $stmt->fetch(PDO::FETCH_ASSOC);
238
239        return is_array($row) ? $row : null;
240    }
241
242    /**
243     * Updates the status of an account in the database.
244     *
245     * @param int $accountId the ID of the account to update
246     * @param string $status The new status to assign to the account. Valid values are: pending, active, frozen, closed.
247     *
248     * @return bool returns true if the update was successful, false otherwise
249     */
250    public function updateAccountStatus(int $accountId, string $status): bool
251    {
252        // Database uses snake_case
253        $sql = 'UPDATE accounts
254                SET status = :status, updated_at = CURRENT_TIMESTAMP
255                WHERE account_id = :account_id';
256
257        $stmt = $this->pdo->prepare($sql);
258
259        return $stmt->execute([
260            'status' => $status,
261            'account_id' => $accountId,
262        ]);
263    }
264
265    /**
266     * Updates the bank account status for the given account ID.
267     *
268     * @param int $accountId the unique identifier of the bank account to update
269     * @param string $status the new status to set for the bank account
270     *
271     * @return bool returns true if the update was successful, false otherwise
272     */
273    public function updateBankAccountStatus(int $accountId, string $status): bool
274    {
275        $sql = 'UPDATE accounts
276                SET bank_account_status = :status, updated_at = CURRENT_TIMESTAMP
277                WHERE account_id = :account_id';
278
279        $stmt = $this->pdo->prepare($sql);
280
281        return $stmt->execute([
282            'status' => $status,
283            'account_id' => $accountId,
284        ]);
285    }
286
287    public function setBankAccountId(int $accountId, string $bankAccountId): bool
288    {
289        $sql = 'UPDATE accounts
290                SET bank_account_id = :bank_account_id,
291                    bank_account_status = \'bank_created\',
292                    updated_at = CURRENT_TIMESTAMP
293                WHERE account_id = :account_id';
294
295        $stmt = $this->pdo->prepare($sql);
296
297        return $stmt->execute([
298            'bank_account_id' => $bankAccountId,
299            'account_id' => $accountId,
300        ]);
301    }
302
303    public function updateInterestRate(int $accountId, string $rate): bool
304    {
305        $sql = 'UPDATE accounts
306                SET interest_rate = :rate, updated_at = CURRENT_TIMESTAMP
307                WHERE account_id = :account_id';
308
309        $stmt = $this->pdo->prepare($sql);
310
311        return $stmt->execute([
312            'rate' => $rate,
313            'account_id' => $accountId,
314        ]);
315    }
316
317    public function updateLoanToValueRatio(int $accountId, string $ratio): bool
318    {
319        // Trigger will automatically recalculate available_for_loan
320        $sql = 'UPDATE accounts
321                SET loan_to_value_ratio = :ratio, updated_at = CURRENT_TIMESTAMP
322                WHERE account_id = :account_id';
323
324        $stmt = $this->pdo->prepare($sql);
325
326        return $stmt->execute([
327            'ratio' => $ratio,
328            'account_id' => $accountId,
329        ]);
330    }
331
332    /**
333     * Get the available loan amount for an account.
334     *
335     * Note: This is now stored in the available_for_loan column and maintained
336     * by triggers. This method is kept for backward compatibility but simply
337     * reads the column value.
338     *
339     * @param int $accountId
340     *
341     * @return string Available loan amount as a string
342     */
343    public function getAvailableForLoan(int $accountId): string
344    {
345        $sql = 'SELECT available_for_loan::TEXT FROM accounts WHERE account_id = :account_id';
346        $stmt = $this->pdo->prepare($sql);
347        $stmt->execute(['account_id' => $accountId]);
348
349        return (string)$stmt->fetchColumn();
350    }
351
352    /**
353     * Check if an account has active loans.
354     *
355     * Simplified: if available_for_loan < max_loan_amount, there are active loans.
356     *
357     * @param int $accountId
358     *
359     * @return bool
360     */
361    public function hasActiveLoans(int $accountId): bool
362    {
363        $sql = 'SELECT (balance * loan_to_value_ratio) > available_for_loan
364                FROM accounts
365                WHERE account_id = :account_id';
366
367        $stmt = $this->pdo->prepare($sql);
368        $stmt->execute(['account_id' => $accountId]);
369
370        return (bool)$stmt->fetchColumn();
371    }
372
373    /**
374     * Get balance history for an account (for charting).
375     *
376     * @param int $accountId The account ID
377     *
378     * @return BalanceHistoryPointData[]
379     */
380    public function getBalanceHistory(int $accountId): array
381    {
382        $sql = "
383        SELECT DISTINCT ON (DATE(created_at))
384            DATE(created_at)::TEXT as date,
385            balance_after as value
386        FROM transactions
387        WHERE account_id = :account_id
388          AND status = 'completed'
389        ORDER BY DATE(created_at), created_at DESC
390    ";
391
392        $stmt = $this->pdo->prepare($sql);
393        $stmt->execute(['account_id' => $accountId]);
394
395        $history = [];
396        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
397            $history[] = new BalanceHistoryPointData(Row::from($row));
398        }
399
400        return $history;
401    }
402
403    /**
404     * Check if an account can be closed.
405     *
406     * Simplified: balance must be 0, and available_for_loan must equal max_loan_amount
407     * (meaning no outstanding loans).
408     *
409     * @param int $accountId
410     *
411     * @return bool
412     */
413    public function canCloseAccount(int $accountId): bool
414    {
415        $sql = 'SELECT balance = 0
416                       AND available_for_loan = (balance * loan_to_value_ratio)
417                FROM accounts
418                WHERE account_id = :account_id';
419
420        $stmt = $this->pdo->prepare($sql);
421        $stmt->execute(['account_id' => $accountId]);
422
423        return (bool)$stmt->fetchColumn();
424    }
425
426    public function getTotalAccounts(): int
427    {
428        $sql = 'SELECT COUNT(*) FROM accounts';
429        $stmt = $this->pdo->query($sql);
430
431        if ($stmt === false) {
432            return 0;
433        }
434
435        $result = $stmt->fetchColumn();
436
437        return $result !== false ? (int)$result : 0;
438    }
439
440    public function getTotalBalance(): string
441    {
442        $sql = 'SELECT COALESCE(SUM(balance), 0)::TEXT FROM accounts';
443        $stmt = $this->pdo->query($sql);
444
445        if ($stmt === false) {
446            return '0.00';
447        }
448
449        $result = $stmt->fetchColumn();
450
451        return $result !== false ? (string)$result : '0.00';
452    }
453
454    /**
455     * Generate a unique account number with retry on collision.
456     *
457     * Format: INV-XXXXX (where X is 0-9)
458     * Range: INV-00001 to INV-99999 (99,999 possible accounts)
459     *
460     * @param int $maxAttempts Maximum retry attempts before failing
461     *
462     * @throws RandomException If no source of randomness is available
463     * @throws RuntimeException If unable to generate unique number after max attempts
464     *
465     * @return string Unique account number
466     */
467    private function generateUniqueAccountNumber(int $maxAttempts = 10): string
468    {
469        for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
470            // Generate random 5-digit number (1-99999)
471            $number = random_int(1, 99999);
472            $accountNumber = sprintf('INV-%05d', $number);
473
474            // Check if it exists
475            if (!$this->accountNumberExists($accountNumber)) {
476                return $accountNumber;
477            }
478
479            // Log collision for monitoring (optional)
480            // error_log("Account number collision on attempt {$attempt}: {$accountNumber}");
481        }
482
483        // If we get here, we couldn't find a unique number
484        throw new RuntimeException(
485            "Failed to generate unique account number after {$maxAttempts} attempts. "
486            . 'Consider expanding the account number range.',
487        );
488    }
489
490}