Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
68.68%
239 / 348
12.00%
3 / 25
 CRAP
0.00%
0 / 1
SuperAdminRepository
68.68%
239 / 348
12.00%
3 / 25
 220.57
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%
19 / 19
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
0.00%
0 / 59
0.00%
0 / 1
 132
 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
 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 loans WHERE account_id IN (
368                SELECT account_id FROM accounts WHERE investor_id IN (
369                    SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
370                )
371            )
372        ");
373
374        $this->pdo->exec("
375            DELETE FROM transactions WHERE account_id IN (
376                SELECT account_id FROM accounts WHERE investor_id IN (
377                    SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
378                )
379            )
380        ");
381
382        $this->pdo->exec("
383            DELETE FROM accounts WHERE investor_id IN (
384                SELECT investor_id FROM investors WHERE email LIKE '{$emailPattern}'
385            )
386        ");
387
388        $this->pdo->exec("
389            DELETE FROM user_sessions WHERE user_id IN (
390                SELECT user_id FROM users WHERE email LIKE '{$emailPattern}'
391            )
392        ");
393
394        $this->pdo->exec("DELETE FROM users WHERE email LIKE '{$emailPattern}'");
395        $this->pdo->exec("DELETE FROM investors WHERE email LIKE '{$emailPattern}'");
396
397        return $counts;
398    }
399
400    /**
401     * @return list<array<mixed>>
402     */
403    public function getAllUsers(): array
404    {
405        $stmt = $this->pdo->query(
406            'SELECT
407                    u.user_id AS "userId",
408                    u.username,
409                    u.email,
410                    u.role,
411                    u.is_active AS "isActive",
412                    u.last_login AS "lastLogin",
413                    u.created_at AS "createdAt"
414                FROM users u
415            ORDER BY u.created_at DESC',
416        );
417
418        if ($stmt === false) {
419            throw new RuntimeException('Failed to execute query');
420        }
421
422        $rows = [];
423        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
424            $rows[] = Row::from($row);
425        }
426
427        return $rows;
428    }
429
430    /**
431     * @param int $userId
432     * @return array<mixed>|null camelCase user row, or null if not found
433     */
434    public function getUserById(int $userId): ?array
435    {
436        $stmt = $this->pdo->prepare(
437            'SELECT
438                    user_id AS "userId",
439                    username,
440                    email,
441                    role
442                FROM users
443            WHERE user_id = :user_id',
444        );
445
446        if ($stmt === false) {
447            throw new RuntimeException('Failed to prepare statement');
448        }
449        $stmt->execute(['user_id' => $userId]);
450
451        $row = $stmt->fetch(PDO::FETCH_ASSOC);
452
453        return is_array($row) ? $row : null;
454    }
455
456    public function updateUserRole(int $userId, string $role): bool
457    {
458        $stmt = $this->pdo->prepare(
459            'UPDATE users SET role = :role, updated_at = CURRENT_TIMESTAMP WHERE user_id = :user_id',
460        );
461
462        if ($stmt === false) {
463            throw new RuntimeException('Failed to prepare statement');
464        }
465        $stmt->execute([
466            'user_id' => $userId,
467            'role' => $role,
468        ]);
469
470        return $stmt->rowCount() > 0;
471    }
472
473    /**
474     * @param int $userId
475     * @return array{user: int, investor: int, account: int, loans: int, transactions: int, sessions: int}
476     */
477    public function deleteUser(int $userId): array
478    {
479        // Look up investor and account IDs
480        $stmt = $this->pdo->prepare(
481            'SELECT u.user_id, i.investor_id, a.account_id
482             FROM users u
483             LEFT JOIN investors i ON i.investor_id = u.investor_id
484             LEFT JOIN accounts a ON a.investor_id = i.investor_id
485             WHERE u.user_id = :user_id',
486        );
487        if ($stmt === false) {
488            throw new RuntimeException('Failed to prepare statement');
489        }
490        $stmt->execute(['user_id' => $userId]);
491        $row = $stmt->fetch(PDO::FETCH_ASSOC);
492
493        if (!is_array($row)) {
494            throw new RuntimeException("User {$userId} not found");
495        }
496
497        $investorId = Row::nullableInt($row, 'investor_id');
498        $accountId = Row::nullableInt($row, 'account_id');
499
500        $counts = ['user' => 0, 'investor' => 0, 'account' => 0, 'loans' => 0, 'transactions' => 0, 'sessions' => 0];
501
502        $this->pdo->beginTransaction();
503
504        try {
505            if ($accountId !== null) {
506                // Loan payment schedule
507                $stmt = $this->pdo->prepare(
508                    'DELETE FROM loan_payment_schedule WHERE loan_id IN (SELECT loan_id FROM loans WHERE account_id = :account_id)',
509                );
510                $stmt->execute(['account_id' => $accountId]);
511
512                // Loan payments
513                $stmt = $this->pdo->prepare(
514                    'DELETE FROM loan_payments WHERE loan_id IN (SELECT loan_id FROM loans WHERE account_id = :account_id)',
515                );
516                $stmt->execute(['account_id' => $accountId]);
517
518                // Loans
519                $stmt = $this->pdo->prepare('DELETE FROM loans WHERE account_id = :account_id');
520                $stmt->execute(['account_id' => $accountId]);
521                $counts['loans'] = $stmt->rowCount();
522
523                // Transactions
524                $stmt = $this->pdo->prepare('DELETE FROM transactions WHERE account_id = :account_id');
525                $stmt->execute(['account_id' => $accountId]);
526                $counts['transactions'] = $stmt->rowCount();
527
528                // Account
529                $stmt = $this->pdo->prepare('DELETE FROM accounts WHERE account_id = :account_id');
530                $stmt->execute(['account_id' => $accountId]);
531                $counts['account'] = $stmt->rowCount();
532            }
533
534            if ($investorId !== null) {
535                // Documents
536                $stmt = $this->pdo->prepare('DELETE FROM investor_documents WHERE investor_id = :investor_id');
537                if ($stmt !== false) {
538                    $stmt->execute(['investor_id' => $investorId]);
539                }
540            }
541
542            // Sessions
543            $stmt = $this->pdo->prepare('DELETE FROM user_sessions WHERE user_id = :user_id');
544            if ($stmt === false) {
545                throw new RuntimeException('Failed to prepare statement');
546            }
547            $stmt->execute(['user_id' => $userId]);
548            $counts['sessions'] = $stmt->rowCount();
549
550            // Audit log
551            $stmt = $this->pdo->prepare('DELETE FROM audit_log WHERE user_id = :user_id');
552            if ($stmt !== false) {
553                $stmt->execute(['user_id' => $userId]);
554            }
555
556            // Error logs
557            $stmt = $this->pdo->prepare('UPDATE error_logs SET user_id = NULL WHERE user_id = :user_id');
558            if ($stmt !== false) {
559                $stmt->execute(['user_id' => $userId]);
560            }
561
562            // User
563            $stmt = $this->pdo->prepare('DELETE FROM users WHERE user_id = :user_id');
564            $stmt->execute(['user_id' => $userId]);
565            $counts['user'] = $stmt->rowCount();
566
567            // Investor
568            if ($investorId !== null) {
569                $stmt = $this->pdo->prepare('DELETE FROM investors WHERE investor_id = :investor_id');
570                $stmt->execute(['investor_id' => $investorId]);
571                $counts['investor'] = $stmt->rowCount();
572            }
573
574            $this->pdo->commit();
575        } catch (Throwable $e) {
576            $this->pdo->rollBack();
577            throw $e;
578        }
579
580        return $counts;
581    }
582
583    private function generateUniqueAccountNumber(int $maxAttempts = 10): string
584    {
585        for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
586            $accountNumber = sprintf('INV-%05d', random_int(1, 99999));
587
588            $stmt = $this->pdo->prepare(
589                'SELECT EXISTS(SELECT 1 FROM accounts WHERE account_number = :account_number)',
590            );
591
592            if ($stmt === false) {
593                throw new RuntimeException('Failed to prepare statement');
594            }
595            $stmt->execute(['account_number' => $accountNumber]);
596
597            if (!$stmt->fetchColumn()) {
598                return $accountNumber;
599            }
600        }
601
602        throw new RuntimeException(
603            "Failed to generate unique account number after {$maxAttempts} attempts",
604        );
605    }
606
607    /**
608     * Run daily interest accrual for a single date.
609     *
610     * @param string $date
611     * @return int Number of accounts accrued
612     */
613    public function accrueDailyInterest(string $date): int
614    {
615        $stmt = $this->pdo->prepare('SELECT accrue_daily_interest(:date::DATE)');
616        if ($stmt === false) {
617            throw new RuntimeException('Failed to prepare statement');
618        }
619        $stmt->execute(['date' => $date]);
620
621        return (int)$stmt->fetchColumn();
622    }
623
624    /**
625     * Snapshot all account balances for a single date.
626     *
627     * @param string $date
628     * @return int Number of accounts snapshotted
629     */
630    public function snapshotDailyAccountBalance(string $date): int
631    {
632        $stmt = $this->pdo->prepare('SELECT snapshot_daily_account_balance(:date::DATE)');
633        if ($stmt === false) {
634            throw new RuntimeException('Failed to prepare statement');
635        }
636        $stmt->execute(['date' => $date]);
637
638        return (int)$stmt->fetchColumn();
639    }
640
641    /**
642     * Last-run summary for the three scheduled console commands. Source
643     * data lives in daily_interest_accrual / daily_account_balance /
644     * transactions; we don't keep a dedicated cron_run table because
645     * the work itself is the audit trail.
646     *
647     * Returned shape (one row per job, three rows total):
648     *   jobName, lastRunAt, lastRunFor, accountsAffected, totalAmount
649     *
650     * Caller (service) applies the staleness thresholds and assembles
651     * the DTO with status + display name.
652     *
653     * @return array<string, array{lastRunAt: ?string, lastRunFor: ?string, accountsAffected: int, totalAmount: ?string}>
654     */
655    public function getCronJobLastRunSummaries(): array
656    {
657        $result = [];
658
659        // interest:accrue — group by accrual_date (one run per date)
660        $stmt = $this->pdo->prepare('
661            SELECT
662                accrual_date::TEXT                            AS "lastRunFor",
663                MAX(created_at)::TEXT                         AS "lastRunAt",
664                COUNT(*)::INTEGER                             AS "accountsAffected"
665            FROM daily_interest_accrual
666            WHERE accrual_date = (SELECT MAX(accrual_date) FROM daily_interest_accrual)
667            GROUP BY accrual_date
668        ');
669        $stmt->execute();
670        $row = $stmt->fetch(PDO::FETCH_ASSOC);
671        $result['interest:accrue'] = is_array($row)
672            ? [
673                'lastRunAt' => Row::nullableString($row, 'lastRunAt'),
674                'lastRunFor' => Row::nullableString($row, 'lastRunFor'),
675                'accountsAffected' => Row::int($row, 'accountsAffected'),
676                'totalAmount' => null,
677            ]
678            : ['lastRunAt' => null, 'lastRunFor' => null, 'accountsAffected' => 0, 'totalAmount' => null];
679
680        // balance:snapshot — group by snapshot_date
681        $stmt = $this->pdo->prepare('
682            SELECT
683                snapshot_date::TEXT                           AS "lastRunFor",
684                MAX(created_at)::TEXT                         AS "lastRunAt",
685                COUNT(*)::INTEGER                             AS "accountsAffected"
686            FROM daily_account_balance
687            WHERE snapshot_date = (SELECT MAX(snapshot_date) FROM daily_account_balance)
688            GROUP BY snapshot_date
689        ');
690        $stmt->execute();
691        $row = $stmt->fetch(PDO::FETCH_ASSOC);
692        $result['balance:snapshot'] = is_array($row)
693            ? [
694                'lastRunAt' => Row::nullableString($row, 'lastRunAt'),
695                'lastRunFor' => Row::nullableString($row, 'lastRunFor'),
696                'accountsAffected' => Row::int($row, 'accountsAffected'),
697                'totalAmount' => null,
698            ]
699            : ['lastRunAt' => null, 'lastRunFor' => null, 'accountsAffected' => 0, 'totalAmount' => null];
700
701        // interest:post — group by description (one description per posted month)
702        $stmt = $this->pdo->prepare("
703            SELECT
704                description                                 AS \"lastRunFor\",
705                MAX(created_at)::TEXT                       AS \"lastRunAt\",
706                COUNT(*)::INTEGER                           AS \"accountsAffected\",
707                SUM(amount)::TEXT                           AS \"totalAmount\"
708            FROM transactions
709            WHERE transaction_type = 'interest'
710              AND description LIKE 'Monthly interest for%'
711            GROUP BY description
712            ORDER BY MAX(created_at) DESC
713            LIMIT 1
714        ");
715        $stmt->execute();
716        $row = $stmt->fetch(PDO::FETCH_ASSOC);
717        $result['interest:post'] = is_array($row)
718            ? [
719                'lastRunAt' => Row::nullableString($row, 'lastRunAt'),
720                'lastRunFor' => Row::nullableString($row, 'lastRunFor'),
721                'accountsAffected' => Row::int($row, 'accountsAffected'),
722                'totalAmount' => Row::nullableString($row, 'totalAmount'),
723            ]
724            : ['lastRunAt' => null, 'lastRunFor' => null, 'accountsAffected' => 0, 'totalAmount' => null];
725
726        return $result;
727    }
728
729    /**
730     * Post monthly interest as transactions for a given month.
731     *
732     * @param string $monthStart
733     * @return array{accountsPosted: int, totalInterest: string}
734     */
735    public function postMonthlyInterest(string $monthStart): array
736    {
737        $stmt = $this->pdo->prepare('SELECT * FROM post_monthly_interest(:month::DATE)');
738        if ($stmt === false) {
739            throw new RuntimeException('Failed to prepare statement');
740        }
741        $stmt->execute(['month' => $monthStart]);
742        $result = $stmt->fetch(PDO::FETCH_ASSOC);
743
744        if (!is_array($result)) {
745            return ['accountsPosted' => 0, 'totalInterest' => '0.00'];
746        }
747
748        return [
749            'accountsPosted' => Row::int($result, 'accounts_posted'),
750            'totalInterest' => Row::string($result, 'total_interest'),
751        ];
752    }
753
754    /**
755     * Get all active accounts with positive balances and their investor names.
756     *
757     * @return list<array{accountId: int, accountNumber: string, investorName: string, balance: string, interestRate: string}>
758     */
759    public function getActiveAccountsWithBalances(): array
760    {
761        $stmt = $this->pdo->prepare(
762            "SELECT
763                a.account_id AS \"accountId\",
764                a.account_number AS \"accountNumber\",
765                i.first_name || ' ' || i.last_name AS \"investorName\",
766                a.balance AS \"balance\",
767                COALESCE(a.interest_rate, get_loan_config('account_yield_rate')) AS \"interestRate\"
768            FROM accounts a
769            JOIN investors i ON i.investor_id = a.investor_id
770            WHERE a.status = 'active'
771              AND a.balance > 0
772            ORDER BY a.account_number",
773        );
774        if ($stmt === false) {
775            throw new RuntimeException('Failed to prepare statement');
776        }
777        $stmt->execute();
778
779        /** @var list<array{accountId: int, accountNumber: string, investorName: string, balance: string, interestRate: string}> */
780        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
781
782        return $rows;
783    }
784
785    /**
786     * Get the global account yield rate from loan_config.
787     */
788    public function getAccountYieldRate(): string
789    {
790        $stmt = $this->pdo->prepare(
791            "SELECT config_value FROM loan_config WHERE config_key = 'account_yield_rate'",
792        );
793        if ($stmt === false) {
794            throw new RuntimeException('Failed to prepare statement');
795        }
796        $stmt->execute();
797        $value = $stmt->fetchColumn();
798
799        return is_string($value) ? $value : '0.00';
800    }
801}