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