Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
84.27%
316 / 375
14.81%
4 / 27
 CRAP
0.00%
0 / 1
SuperAdminRepository
84.27%
316 / 375
14.81%
4 / 27
 96.91
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 createUser
92.31%
12 / 13
0.00%
0 / 1
 2.00
 createInvestor
95.45%
21 / 22
0.00%
0 / 1
 2
 createAccount
95.24%
20 / 21
0.00%
0 / 1
 2
 createTransaction
94.12%
16 / 17
0.00%
0 / 1
 2.00
 getAccountById
88.89%
8 / 9
0.00%
0 / 1
 3.01
 getAccountBalance
85.71%
6 / 7
0.00%
0 / 1
 2.01
 updateAccountBalance
90.00%
9 / 10
0.00%
0 / 1
 2.00
 updateAccountStatus
90.00%
9 / 10
0.00%
0 / 1
 2.00
 getAllAccountsWithInvestors
90.00%
9 / 10
0.00%
0 / 1
 3.01
 emailExistsInUsers
85.71%
6 / 7
0.00%
0 / 1
 2.01
 emailExistsInInvestors
85.71%
6 / 7
0.00%
0 / 1
 2.01
 countTestData
94.44%
17 / 18
0.00%
0 / 1
 5.00
 deleteTestData
100.00%
25 / 25
100.00%
1 / 1
 1
 getAllUsers
90.00%
9 / 10
0.00%
0 / 1
 3.01
 getUserById
88.89%
8 / 9
0.00%
0 / 1
 3.01
 updateUserRole
90.00%
9 / 10
0.00%
0 / 1
 2.00
 deleteUser
90.48%
57 / 63
0.00%
0 / 1
 11.10
 generateUniqueAccountNumber
69.23%
9 / 13
0.00%
0 / 1
 4.47
 accrueDailyInterest
0.00%
0 / 5
0.00%
0 / 1
 6
 snapshotDailyAccountBalance
0.00%
0 / 5
0.00%
0 / 1
 6
 getCronJobLastRunSummaries
100.00%
38 / 38
100.00%
1 / 1
 4
 postMonthlyInterest
0.00%
0 / 11
0.00%
0 / 1
 12
 replayAccountHistory
81.25%
13 / 16
0.00%
0 / 1
 4.11
 toIntArrayLiteral
100.00%
1 / 1
100.00%
1 / 1
 1
 getActiveAccountsWithBalances
0.00%
0 / 9
0.00%
0 / 1
 6
 getAccountYieldRate
87.50%
7 / 8
0.00%
0 / 1
 3.02
1<?php
2
3declare(strict_types=1);
4
5namespace App\Domain\SuperAdmin\Repository;
6
7use App\Support\Row;
8use PDO;
9use RuntimeException;
10
11use Throwable;
12
13use function sprintf;
14
15/**
16 * Repository for Super Admin test data generation.
17 */
18final class SuperAdminRepository
19{
20    public function __construct(
21        private readonly PDO $pdo,
22    ) {}
23
24    public function createUser(
25        string $email,
26        string $username,
27        string $passwordHash,
28        string $role = 'investor',
29    ): int {
30        $stmt = $this->pdo->prepare(
31            'INSERT INTO users (email, username, password_hash, role, is_active)
32                VALUES (:email, :username, :password_hash, :role, true)
33             RETURNING user_id',
34        );
35
36        if ($stmt === false) {
37            throw new RuntimeException('Failed to prepare statement');
38        }
39        $stmt->execute([
40            'email' => $email,
41            'username' => $username,
42            'password_hash' => $passwordHash,
43            'role' => $role,
44        ]);
45
46        return (int)$stmt->fetchColumn();
47    }
48
49    /**
50     * @param array<string, mixed> $data
51     */
52    public function createInvestor(array $data): int
53    {
54        $stmt = $this->pdo->prepare(
55            'INSERT INTO investors (
56                    first_name, last_name, email, phone, date_of_birth,
57                    address_line1, city, state, zip_code, country,
58                    kyc_status, status, created_at
59                ) VALUES (
60                    :first_name, :last_name, :email, :phone, :date_of_birth,
61                    :address_line1, :city, :state, :zip_code, :country,
62                    :kyc_status, :status, :created_at
63                )
64            RETURNING investor_id',
65        );
66
67        if ($stmt === false) {
68            throw new RuntimeException('Failed to prepare statement');
69        }
70        $stmt->execute([
71            'first_name' => $data['firstName'],
72            'last_name' => $data['lastName'],
73            'email' => $data['email'],
74            'phone' => $data['phone'] ?? null,
75            'date_of_birth' => $data['dateOfBirth'],
76            'address_line1' => $data['addressLine1'] ?? null,
77            'city' => $data['city'] ?? null,
78            'state' => $data['state'] ?? null,
79            'zip_code' => $data['zipCode'] ?? null,
80            'country' => $data['country'] ?? 'USA',
81            'kyc_status' => $data['kycStatus'] ?? 'verified',
82            'status' => $data['status'] ?? 'active',
83            'created_at' => $data['createdAt'] ?? date('Y-m-d H:i:s'),
84        ]);
85
86        return (int)$stmt->fetchColumn();
87    }
88
89    /**
90     * @param int $investorId
91     * @param string $status
92     * @param ?string $createdAt
93     * @return array{accountId: int, accountNumber: string}
94     */
95    public function createAccount(
96        int $investorId,
97        string $status = 'pending',
98        ?string $createdAt = null,
99    ): array {
100        $accountNumber = $this->generateUniqueAccountNumber();
101        $createdDate = $createdAt ?? date('Y-m-d H:i:s');
102
103        $stmt = $this->pdo->prepare(
104            'INSERT INTO accounts (
105                    investor_id, account_number,
106                    interest_rate, loan_to_value_ratio, status, opened_date, created_at
107                ) VALUES (
108                    :investor_id, :account_number,
109                    :interest_rate, :loan_to_value_ratio, :status, :opened_date, :created_at
110                )
111            RETURNING account_id',
112        );
113
114        if ($stmt === false) {
115            throw new RuntimeException('Failed to prepare statement');
116        }
117
118        $stmt->execute([
119            'investor_id' => $investorId,
120            'account_number' => $accountNumber,
121            'interest_rate' => null,
122            'loan_to_value_ratio' => 0.80,
123            'status' => $status,
124            'opened_date' => substr($createdDate, 0, 10),
125            'created_at' => $createdDate,
126        ]);
127
128        return [
129            'accountId' => (int)$stmt->fetchColumn(),
130            'accountNumber' => $accountNumber,
131        ];
132    }
133
134    public function createTransaction(
135        int $accountId,
136        string $type,
137        float $amount,
138        float $balanceAfter,
139        string $description,
140        ?string $referenceNumber,
141        string $createdAt,
142    ): int {
143        $stmt = $this->pdo->prepare(
144            'INSERT INTO transactions (
145                    account_id, transaction_type, amount, balance_after,
146                    description, reference_number, status, created_at
147                ) VALUES (
148                    :account_id, :transaction_type, :amount, :balance_after,
149                    :description, :reference_number, :status, :created_at
150                )
151            RETURNING transaction_id',
152        );
153
154        if ($stmt === false) {
155            throw new RuntimeException('Failed to prepare statement');
156        }
157        $stmt->execute([
158            'account_id' => $accountId,
159            'transaction_type' => $type,
160            'amount' => $amount,
161            'balance_after' => $balanceAfter,
162            'description' => $description,
163            'reference_number' => $referenceNumber,
164            'status' => 'completed',
165            'created_at' => $createdAt,
166        ]);
167
168        return (int)$stmt->fetchColumn();
169    }
170
171    /**
172     * @param int $accountId
173     * @return array<mixed>|null
174     */
175    public function getAccountById(int $accountId): ?array
176    {
177        $stmt = $this->pdo->prepare(
178            'SELECT
179                    account_id AS "accountId",
180                    investor_id AS "investorId",
181                    account_number AS "accountNumber",
182                    balance::TEXT AS balance,
183                    status,
184                    created_at AS "createdAt"
185                FROM accounts
186            WHERE account_id = :account_id',
187        );
188
189        if ($stmt === false) {
190            throw new RuntimeException('Failed to prepare statement');
191        }
192        $stmt->execute(['account_id' => $accountId]);
193
194        $row = $stmt->fetch(PDO::FETCH_ASSOC);
195
196        return is_array($row) ? $row : null;
197    }
198
199    public function getAccountBalance(int $accountId): string
200    {
201        $stmt = $this->pdo->prepare(
202            'SELECT balance::TEXT FROM accounts WHERE account_id = :account_id',
203        );
204
205        if ($stmt === false) {
206            throw new RuntimeException('Failed to prepare statement');
207        }
208        $stmt->execute(['account_id' => $accountId]);
209
210        return (string)$stmt->fetchColumn();
211    }
212
213    public function updateAccountBalance(int $accountId, float $newBalance): bool
214    {
215        $stmt = $this->pdo->prepare(
216            'UPDATE accounts
217                SET balance = :balance,
218                    available_balance = :balance,
219                    updated_at = CURRENT_TIMESTAMP
220             WHERE account_id = :account_id',
221        );
222
223        if ($stmt === false) {
224            throw new RuntimeException('Failed to prepare statement');
225        }
226
227        return $stmt->execute([
228            'balance' => $newBalance,
229            'account_id' => $accountId,
230        ]);
231    }
232
233    public function updateAccountStatus(int $accountId, string $status): bool
234    {
235        $stmt = $this->pdo->prepare(
236            'UPDATE accounts SET status = :status WHERE account_id = :account_id',
237        );
238
239        if ($stmt === false) {
240            throw new RuntimeException('Failed to prepare statement');
241        }
242        $stmt->execute([
243            'account_id' => $accountId,
244            'status' => $status,
245        ]);
246
247        return $stmt->rowCount() > 0;
248    }
249
250    /**
251     * @return list<array<mixed>>
252     */
253    public function getAllAccountsWithInvestors(): array
254    {
255        $stmt = $this->pdo->query(
256            'SELECT
257                    a.account_id AS "accountId",
258                    a.account_number AS "accountNumber",
259                    a.balance::TEXT AS balance,
260                    a.status,
261                    i.first_name || \' \' || i.last_name AS "investorName",
262                    i.email
263                FROM accounts a
264                INNER JOIN investors i ON i.investor_id = a.investor_id
265            ORDER BY a.created_at DESC',
266        );
267
268        if ($stmt === false) {
269            throw new RuntimeException('Failed to execute query');
270        }
271
272        $rows = [];
273        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
274            $rows[] = Row::from($row);
275        }
276
277        return $rows;
278    }
279
280    public function emailExistsInUsers(string $email): bool
281    {
282        $stmt = $this->pdo->prepare(
283            'SELECT EXISTS(SELECT 1 FROM users WHERE email = :email)',
284        );
285
286        if ($stmt === false) {
287            throw new RuntimeException('Failed to prepare statement');
288        }
289        $stmt->execute(['email' => $email]);
290
291        return (bool)$stmt->fetchColumn();
292    }
293
294    public function emailExistsInInvestors(string $email): bool
295    {
296        $stmt = $this->pdo->prepare(
297            'SELECT EXISTS(SELECT 1 FROM investors WHERE email = :email)',
298        );
299
300        if ($stmt === false) {
301            throw new RuntimeException('Failed to prepare statement');
302        }
303        $stmt->execute(['email' => $email]);
304
305        return (bool)$stmt->fetchColumn();
306    }
307
308    /**
309     * @param string $emailPattern
310     * @return array{users: int, investors: int, accounts: int, transactions: int}
311     */
312    public function countTestData(string $emailPattern = '%@testdata.local'): array
313    {
314        $users = $this->pdo->prepare('SELECT COUNT(*) FROM users WHERE email LIKE :pattern');
315
316        $investors = $this->pdo->prepare('SELECT COUNT(*) FROM investors WHERE email LIKE :pattern');
317
318        $accounts = $this->pdo->prepare('
319            SELECT COUNT(*) FROM accounts WHERE investor_id IN (
320                SELECT investor_id FROM investors WHERE email LIKE :pattern
321            )
322        ');
323
324        $transactions = $this->pdo->prepare('
325            SELECT COUNT(*) FROM transactions WHERE account_id IN (
326                SELECT account_id FROM accounts WHERE investor_id IN (
327                    SELECT investor_id FROM investors WHERE email LIKE :pattern
328                )
329            )
330        ');
331        if ($users === false || $investors === false || $accounts === false || $transactions === false) {
332            throw new RuntimeException('Failed to prepare statement');
333        }
334
335        $users->execute(['pattern' => $emailPattern]);
336        $investors->execute(['pattern' => $emailPattern]);
337        $accounts->execute(['pattern' => $emailPattern]);
338        $transactions->execute(['pattern' => $emailPattern]);
339
340        return [
341            'users' => (int)$users->fetchColumn(),
342            'investors' => (int)$investors->fetchColumn(),
343            'accounts' => (int)$accounts->fetchColumn(),
344            'transactions' => (int)$transactions->fetchColumn(),
345        ];
346    }
347
348    /**
349     * @param string $emailPattern
350     * @return array{users: int, investors: int, accounts: int, transactions: int}
351     */
352    public function deleteTestData(string $emailPattern = '%@testdata.local'): array
353    {
354        $counts = $this->countTestData($emailPattern);
355
356        $this->pdo->exec("
357            DELETE FROM loan_payment_schedule WHERE loan_id IN (
358                SELECT loan_id FROM loans WHERE account_id IN (
359                    SELECT account_id FROM accounts WHERE investor_id IN (
360                        SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
361                    )
362                )
363            )
364        ");
365
366        $this->pdo->exec("
367            DELETE FROM loan_payments WHERE loan_id IN (
368                SELECT loan_id FROM loans WHERE account_id IN (
369                    SELECT account_id FROM accounts WHERE investor_id IN (
370                        SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
371                    )
372                )
373            )
374        ");
375
376        $this->pdo->exec("
377            DELETE FROM disbursements WHERE loan_id IN (
378                SELECT loan_id FROM loans WHERE account_id IN (
379                    SELECT account_id FROM accounts WHERE investor_id IN (
380                        SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
381                    )
382                )
383            )
384        ");
385
386        $this->pdo->exec("
387            DELETE FROM loans WHERE account_id IN (
388                SELECT account_id FROM accounts WHERE investor_id IN (
389                    SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
390                )
391            )
392        ");
393
394        $this->pdo->exec("
395            DELETE FROM transactions WHERE account_id IN (
396                SELECT account_id FROM accounts WHERE investor_id IN (
397                    SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
398                )
399            )
400        ");
401
402        $this->pdo->exec("
403            DELETE FROM accounts WHERE investor_id IN (
404                SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
405            )
406        ");
407
408        $this->pdo->exec("
409            DELETE FROM user_sessions WHERE user_id IN (
410                SELECT user_id FROM users WHERE email LIKE '{$emailPattern}'
411            )
412        ");
413
414        $this->pdo->exec("DELETE FROM users WHERE email LIKE '{$emailPattern}'");
415        $this->pdo->exec("DELETE FROM investors WHERE email LIKE '{$emailPattern}'");
416
417        return $counts;
418    }
419
420    /**
421     * @return list<array<mixed>>
422     */
423    public function getAllUsers(): array
424    {
425        $stmt = $this->pdo->query(
426            'SELECT
427                    u.user_id AS "userId",
428                    u.username,
429                    u.email,
430                    u.role,
431                    u.is_active AS "isActive",
432                    u.last_login AS "lastLogin",
433                    u.created_at AS "createdAt"
434                FROM users u
435            ORDER BY u.created_at DESC',
436        );
437
438        if ($stmt === false) {
439            throw new RuntimeException('Failed to execute query');
440        }
441
442        $rows = [];
443        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
444            $rows[] = Row::from($row);
445        }
446
447        return $rows;
448    }
449
450    /**
451     * @param int $userId
452     * @return array<mixed>|null camelCase user row, or null if not found
453     */
454    public function getUserById(int $userId): ?array
455    {
456        $stmt = $this->pdo->prepare(
457            'SELECT
458                    user_id AS "userId",
459                    username,
460                    email,
461                    role
462                FROM users
463            WHERE user_id = :user_id',
464        );
465
466        if ($stmt === false) {
467            throw new RuntimeException('Failed to prepare statement');
468        }
469        $stmt->execute(['user_id' => $userId]);
470
471        $row = $stmt->fetch(PDO::FETCH_ASSOC);
472
473        return is_array($row) ? $row : null;
474    }
475
476    public function updateUserRole(int $userId, string $role): bool
477    {
478        $stmt = $this->pdo->prepare(
479            'UPDATE users SET role = :role, updated_at = CURRENT_TIMESTAMP WHERE user_id = :user_id',
480        );
481
482        if ($stmt === false) {
483            throw new RuntimeException('Failed to prepare statement');
484        }
485        $stmt->execute([
486            'user_id' => $userId,
487            'role' => $role,
488        ]);
489
490        return $stmt->rowCount() > 0;
491    }
492
493    /**
494     * @param int $userId
495     * @return array{user: int, investor: int, account: int, loans: int, transactions: int, sessions: int}
496     */
497    public function deleteUser(int $userId): array
498    {
499        // Look up investor and account IDs
500        $stmt = $this->pdo->prepare(
501            'SELECT u.user_id, i.investor_id, a.account_id
502             FROM users u
503             LEFT JOIN investors i ON i.investor_id = u.investor_id
504             LEFT JOIN accounts a ON a.investor_id = i.investor_id
505             WHERE u.user_id = :user_id',
506        );
507        if ($stmt === false) {
508            throw new RuntimeException('Failed to prepare statement');
509        }
510        $stmt->execute(['user_id' => $userId]);
511        $row = $stmt->fetch(PDO::FETCH_ASSOC);
512
513        if (!is_array($row)) {
514            throw new RuntimeException("User {$userId} not found");
515        }
516
517        $investorId = Row::nullableInt($row, 'investor_id');
518        $accountId = Row::nullableInt($row, 'account_id');
519
520        $counts = ['user' => 0, 'investor' => 0, 'account' => 0, 'loans' => 0, 'transactions' => 0, 'sessions' => 0];
521
522        $this->pdo->beginTransaction();
523
524        try {
525            if ($accountId !== null) {
526                // Loan payment schedule
527                $stmt = $this->pdo->prepare(
528                    'DELETE FROM loan_payment_schedule WHERE loan_id IN (SELECT loan_id FROM loans WHERE account_id = :account_id)',
529                );
530                $stmt->execute(['account_id' => $accountId]);
531
532                // Loan payments
533                $stmt = $this->pdo->prepare(
534                    'DELETE FROM loan_payments WHERE loan_id IN (SELECT loan_id FROM loans WHERE account_id = :account_id)',
535                );
536                $stmt->execute(['account_id' => $accountId]);
537
538                // Disbursements (ON DELETE RESTRICT against loans — must clear before loans)
539                $stmt = $this->pdo->prepare(
540                    'DELETE FROM disbursements WHERE loan_id IN (SELECT loan_id FROM loans WHERE account_id = :account_id)',
541                );
542                $stmt->execute(['account_id' => $accountId]);
543
544                // Loans
545                $stmt = $this->pdo->prepare('DELETE FROM loans WHERE account_id = :account_id');
546                $stmt->execute(['account_id' => $accountId]);
547                $counts['loans'] = $stmt->rowCount();
548
549                // Transactions
550                $stmt = $this->pdo->prepare('DELETE FROM transactions WHERE account_id = :account_id');
551                $stmt->execute(['account_id' => $accountId]);
552                $counts['transactions'] = $stmt->rowCount();
553
554                // Account
555                $stmt = $this->pdo->prepare('DELETE FROM accounts WHERE account_id = :account_id');
556                $stmt->execute(['account_id' => $accountId]);
557                $counts['account'] = $stmt->rowCount();
558            }
559
560            if ($investorId !== null) {
561                // Documents
562                $stmt = $this->pdo->prepare('DELETE FROM investor_documents WHERE investor_id = :investor_id');
563                if ($stmt !== false) {
564                    $stmt->execute(['investor_id' => $investorId]);
565                }
566            }
567
568            // Sessions
569            $stmt = $this->pdo->prepare('DELETE FROM user_sessions WHERE user_id = :user_id');
570            if ($stmt === false) {
571                throw new RuntimeException('Failed to prepare statement');
572            }
573            $stmt->execute(['user_id' => $userId]);
574            $counts['sessions'] = $stmt->rowCount();
575
576            // Audit log
577            $stmt = $this->pdo->prepare('DELETE FROM audit_log WHERE user_id = :user_id');
578            if ($stmt !== false) {
579                $stmt->execute(['user_id' => $userId]);
580            }
581
582            // Error logs
583            $stmt = $this->pdo->prepare('UPDATE error_logs SET user_id = NULL WHERE user_id = :user_id');
584            if ($stmt !== false) {
585                $stmt->execute(['user_id' => $userId]);
586            }
587
588            // User
589            $stmt = $this->pdo->prepare('DELETE FROM users WHERE user_id = :user_id');
590            $stmt->execute(['user_id' => $userId]);
591            $counts['user'] = $stmt->rowCount();
592
593            // Investor
594            if ($investorId !== null) {
595                $stmt = $this->pdo->prepare('DELETE FROM investors WHERE investor_id = :investor_id');
596                $stmt->execute(['investor_id' => $investorId]);
597                $counts['investor'] = $stmt->rowCount();
598            }
599
600            $this->pdo->commit();
601        } catch (Throwable $e) {
602            $this->pdo->rollBack();
603            throw $e;
604        }
605
606        return $counts;
607    }
608
609    private function generateUniqueAccountNumber(int $maxAttempts = 10): string
610    {
611        for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
612            $accountNumber = sprintf('INV-%05d', random_int(1, 99999));
613
614            $stmt = $this->pdo->prepare(
615                'SELECT EXISTS(SELECT 1 FROM accounts WHERE account_number = :account_number)',
616            );
617
618            if ($stmt === false) {
619                throw new RuntimeException('Failed to prepare statement');
620            }
621            $stmt->execute(['account_number' => $accountNumber]);
622
623            if (!$stmt->fetchColumn()) {
624                return $accountNumber;
625            }
626        }
627
628        throw new RuntimeException(
629            "Failed to generate unique account number after {$maxAttempts} attempts",
630        );
631    }
632
633    /**
634     * Run daily interest accrual for a single date.
635     *
636     * @param string $date
637     * @return int Number of accounts accrued
638     */
639    public function accrueDailyInterest(string $date): int
640    {
641        $stmt = $this->pdo->prepare('SELECT accrue_daily_interest(:date::DATE)');
642        if ($stmt === false) {
643            throw new RuntimeException('Failed to prepare statement');
644        }
645        $stmt->execute(['date' => $date]);
646
647        return (int)$stmt->fetchColumn();
648    }
649
650    /**
651     * Snapshot all account balances for a single date.
652     *
653     * @param string $date
654     * @return int Number of accounts snapshotted
655     */
656    public function snapshotDailyAccountBalance(string $date): int
657    {
658        $stmt = $this->pdo->prepare('SELECT snapshot_daily_account_balance(:date::DATE)');
659        if ($stmt === false) {
660            throw new RuntimeException('Failed to prepare statement');
661        }
662        $stmt->execute(['date' => $date]);
663
664        return (int)$stmt->fetchColumn();
665    }
666
667    /**
668     * Last-run summary for the three scheduled console commands. Source
669     * data lives in daily_interest_accrual / daily_account_balance /
670     * transactions; we don't keep a dedicated cron_run table because
671     * the work itself is the audit trail.
672     *
673     * Returned shape (one row per job, three rows total):
674     *   jobName, lastRunAt, lastRunFor, accountsAffected, totalAmount
675     *
676     * Caller (service) applies the staleness thresholds and assembles
677     * the DTO with status + display name.
678     *
679     * @return array<string, array{lastRunAt: ?string, lastRunFor: ?string, accountsAffected: int, totalAmount: ?string}>
680     */
681    public function getCronJobLastRunSummaries(): array
682    {
683        $result = [];
684
685        // interest:accrue — the most recently *executed* run, i.e. the
686        // accrual_date whose rows were inserted last. We deliberately do NOT
687        // anchor on MAX(accrual_date): seeded or projected future-dated rows
688        // would otherwise win and report their (often old) insert time as the
689        // "last run", flagging a healthy job as stale. Two guards:
690        //   - accrual_date <= CURRENT_DATE drops future-dated rows entirely.
691        //   - ORDER BY MAX(created_at) DESC reflects when the job genuinely
692        //     last ran, even when a seed pre-populated today with an old time.
693        $stmt = $this->pdo->prepare('
694            SELECT
695                accrual_date::TEXT          AS "lastRunFor",
696                run_at::TEXT                AS "lastRunAt",
697                accounts_affected           AS "accountsAffected"
698            FROM (
699                SELECT
700                    accrual_date,
701                    MAX(created_at)         AS run_at,
702                    COUNT(*)::INTEGER       AS accounts_affected
703                FROM daily_interest_accrual
704                WHERE accrual_date <= CURRENT_DATE
705                GROUP BY accrual_date
706            ) runs
707            ORDER BY run_at DESC
708            LIMIT 1
709        ');
710        $stmt->execute();
711        $row = $stmt->fetch(PDO::FETCH_ASSOC);
712        $result['interest:accrue'] = is_array($row)
713            ? [
714                'lastRunAt' => Row::nullableString($row, 'lastRunAt'),
715                'lastRunFor' => Row::nullableString($row, 'lastRunFor'),
716                'accountsAffected' => Row::int($row, 'accountsAffected'),
717                'totalAmount' => null,
718            ]
719            : ['lastRunAt' => null, 'lastRunFor' => null, 'accountsAffected' => 0, 'totalAmount' => null];
720
721        // balance:snapshot — same shape as interest:accrue. Future-dated
722        // snapshot rows (e.g. from the interest projection) must not be read
723        // as the last run; cap to <= CURRENT_DATE and order by latest insert.
724        $stmt = $this->pdo->prepare('
725            SELECT
726                snapshot_date::TEXT         AS "lastRunFor",
727                run_at::TEXT                AS "lastRunAt",
728                accounts_affected           AS "accountsAffected"
729            FROM (
730                SELECT
731                    snapshot_date,
732                    MAX(created_at)         AS run_at,
733                    COUNT(*)::INTEGER       AS accounts_affected
734                FROM daily_account_balance
735                WHERE snapshot_date <= CURRENT_DATE
736                GROUP BY snapshot_date
737            ) runs
738            ORDER BY run_at DESC
739            LIMIT 1
740        ');
741        $stmt->execute();
742        $row = $stmt->fetch(PDO::FETCH_ASSOC);
743        $result['balance:snapshot'] = is_array($row)
744            ? [
745                'lastRunAt' => Row::nullableString($row, 'lastRunAt'),
746                'lastRunFor' => Row::nullableString($row, 'lastRunFor'),
747                'accountsAffected' => Row::int($row, 'accountsAffected'),
748                'totalAmount' => null,
749            ]
750            : ['lastRunAt' => null, 'lastRunFor' => null, 'accountsAffected' => 0, 'totalAmount' => null];
751
752        // interest:post — group by description (one description per posted month)
753        $stmt = $this->pdo->prepare("
754            SELECT
755                description                                 AS \"lastRunFor\",
756                MAX(created_at)::TEXT                       AS \"lastRunAt\",
757                COUNT(*)::INTEGER                           AS \"accountsAffected\",
758                SUM(amount)::TEXT                           AS \"totalAmount\"
759            FROM transactions
760            WHERE transaction_type = 'interest'
761              AND description LIKE 'Monthly interest for%'
762              AND created_at <= CURRENT_TIMESTAMP
763            GROUP BY description
764            ORDER BY MAX(created_at) DESC
765            LIMIT 1
766        ");
767        $stmt->execute();
768        $row = $stmt->fetch(PDO::FETCH_ASSOC);
769        $result['interest:post'] = is_array($row)
770            ? [
771                'lastRunAt' => Row::nullableString($row, 'lastRunAt'),
772                'lastRunFor' => Row::nullableString($row, 'lastRunFor'),
773                'accountsAffected' => Row::int($row, 'accountsAffected'),
774                'totalAmount' => Row::nullableString($row, 'totalAmount'),
775            ]
776            : ['lastRunAt' => null, 'lastRunFor' => null, 'accountsAffected' => 0, 'totalAmount' => null];
777
778        return $result;
779    }
780
781    /**
782     * Post monthly interest as transactions for a given month.
783     *
784     * @param string $monthStart
785     * @return array{accountsPosted: int, totalInterest: string}
786     */
787    public function postMonthlyInterest(string $monthStart): array
788    {
789        $stmt = $this->pdo->prepare('SELECT * FROM post_monthly_interest(:month::DATE)');
790        if ($stmt === false) {
791            throw new RuntimeException('Failed to prepare statement');
792        }
793        $stmt->execute(['month' => $monthStart]);
794        $result = $stmt->fetch(PDO::FETCH_ASSOC);
795
796        if (!is_array($result)) {
797            return ['accountsPosted' => 0, 'totalInterest' => '0.00'];
798        }
799
800        return [
801            'accountsPosted' => Row::int($result, 'accounts_posted'),
802            'totalInterest' => Row::string($result, 'total_interest'),
803        ];
804    }
805
806    /**
807     * Replay the real interest engine for a specific set of accounts from
808     * $fromDate to today, in a single server-side pass (see the
809     * replay_account_history plpgsql function). Same math as the cron, scoped to
810     * the seeded batch, with zero side effects on any other account.
811     *
812     * @param list<int> $accountIds
813     * @param string $fromDate Y-m-d (inclusive)
814     * @return array{daysProcessed: int, monthsPosted: int, interestTransactions: int, totalInterest: string}
815     */
816    public function replayAccountHistory(array $accountIds, string $fromDate): array
817    {
818        $empty = ['daysProcessed' => 0, 'monthsPosted' => 0, 'interestTransactions' => 0, 'totalInterest' => '0.00'];
819        if ($accountIds === []) {
820            return $empty;
821        }
822        $stmt = $this->pdo->prepare('SELECT * FROM replay_account_history(:ids::int[], :from::DATE)');
823        if ($stmt === false) {
824            throw new RuntimeException('Failed to prepare statement');
825        }
826        $stmt->execute(['ids' => $this->toIntArrayLiteral($accountIds), 'from' => $fromDate]);
827        $result = $stmt->fetch(PDO::FETCH_ASSOC);
828
829        if (!is_array($result)) {
830            return $empty;
831        }
832
833        return [
834            'daysProcessed' => Row::int($result, 'days_processed'),
835            'monthsPosted' => Row::int($result, 'months_posted'),
836            'interestTransactions' => Row::int($result, 'interest_transactions'),
837            'totalInterest' => Row::string($result, 'total_interest'),
838        ];
839    }
840
841    /**
842     * Build a Postgres int[] array literal ('{1,2,3}') from a list of account
843     * ids. Values are cast through (int) so the string is never user-influenced.
844     *
845     * @param list<int> $accountIds
846     */
847    private function toIntArrayLiteral(array $accountIds): string
848    {
849        return '{' . implode(',', array_map(static fn($id): int => (int)$id, $accountIds)) . '}';
850    }
851
852    /**
853     * Get all active accounts with positive balances and their investor names.
854     *
855     * @return list<array{accountId: int, accountNumber: string, investorName: string, balance: string, interestRate: string}>
856     */
857    public function getActiveAccountsWithBalances(): array
858    {
859        $stmt = $this->pdo->prepare(
860            "SELECT
861                a.account_id AS \"accountId\",
862                a.account_number AS \"accountNumber\",
863                i.first_name || ' ' || i.last_name AS \"investorName\",
864                a.balance AS \"balance\",
865                COALESCE(a.interest_rate, get_loan_config('account_yield_rate')) AS \"interestRate\"
866            FROM accounts a
867            JOIN investors i ON i.investor_id = a.investor_id
868            WHERE a.status = 'active'
869              AND a.balance > 0
870            ORDER BY a.account_number",
871        );
872        if ($stmt === false) {
873            throw new RuntimeException('Failed to prepare statement');
874        }
875        $stmt->execute();
876
877        /** @var list<array{accountId: int, accountNumber: string, investorName: string, balance: string, interestRate: string}> */
878        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
879
880        return $rows;
881    }
882
883    /**
884     * Get the global account yield rate from loan_config.
885     */
886    public function getAccountYieldRate(): string
887    {
888        $stmt = $this->pdo->prepare(
889            "SELECT config_value FROM loan_config WHERE config_key = 'account_yield_rate'",
890        );
891        if ($stmt === false) {
892            throw new RuntimeException('Failed to prepare statement');
893        }
894        $stmt->execute();
895        $value = $stmt->fetchColumn();
896
897        return is_string($value) ? $value : '0.00';
898    }
899}