Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
89.83%
53 / 59
14.29%
1 / 7
 CRAP
0.00%
0 / 1
DisbursementRepository
89.83%
53 / 59
14.29%
1 / 7
 16.27
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 enqueue
90.00%
9 / 10
0.00%
0 / 1
 2.00
 findById
88.89%
8 / 9
0.00%
0 / 1
 3.01
 listPending
88.89%
8 / 9
0.00%
0 / 1
 3.01
 listHistory
92.31%
12 / 13
0.00%
0 / 1
 3.00
 countByStatus
80.00%
4 / 5
0.00%
0 / 1
 2.03
 markDisbursed
91.67%
11 / 12
0.00%
0 / 1
 2.00
1<?php
2
3declare(strict_types=1);
4
5namespace App\Domain\Disbursement\Repository;
6
7use App\Domain\Disbursement\Data\DisbursementData;
8use App\Support\Row;
9use PDO;
10use RuntimeException;
11
12final readonly class DisbursementRepository
13{
14    private const string SELECT_COLUMNS = <<<SQL
15            disbursement_id    AS "disbursementId",
16            loan_id            AS "loanId",
17            requested_amount   AS "requestedAmount",
18            confirmed_amount   AS "confirmedAmount",
19            origination_fee    AS "originationFee",
20            net_disbursement_amount AS "netDisbursementAmount",
21            bank_reference     AS "bankReference",
22            status             AS "status",
23            disbursed_date     AS "disbursedDate",
24            disbursed_by       AS "disbursedBy",
25            created_at         AS "createdAt",
26            updated_at         AS "updatedAt"
27        SQL;
28
29    public function __construct(
30        private PDO $pdo,
31    ) {}
32
33    /**
34     * Create a new disbursement queue row in `pending_disbursement` state.
35     *
36     * The origination fee (FSC-51) is withheld from the disbursement: the loan
37     * balance stays the full requested amount, but the borrower receives
38     * requested − fee. We snapshot both the fee and the net wire amount here,
39     * using the currently configured origination_fee_pct, so a later rate
40     * change doesn't retro-alter queued rows.
41     *
42     * @param int $loanId
43     * @param string $requestedAmount
44     * @return int The new disbursement_id.
45     */
46    public function enqueue(int $loanId, string $requestedAmount): int
47    {
48        $sql = <<<SQL
49                WITH calc AS (
50                    SELECT
51                        :requested_amount::numeric AS req,
52                        (
53                            SELECT config_value::numeric
54                            FROM lending.loan_config
55                            WHERE config_key = 'origination_fee_pct'
56                        ) AS pct
57                )
58                INSERT INTO lending.disbursements
59                    (loan_id, requested_amount, origination_fee, net_disbursement_amount, status)
60                SELECT
61                    :loan_id,
62                    calc.req,
63                    ROUND(calc.req * COALESCE(calc.pct, 0) / 100, 2),
64                    calc.req - ROUND(calc.req * COALESCE(calc.pct, 0) / 100, 2),
65                    'pending_disbursement'
66                FROM calc
67                RETURNING disbursement_id
68            SQL;
69
70        $stmt = $this->pdo->prepare($sql);
71        if ($stmt === false) {
72            throw new RuntimeException('Failed to prepare statement');
73        }
74        $stmt->execute([
75            'loan_id' => $loanId,
76            'requested_amount' => $requestedAmount,
77        ]);
78
79        return (int)$stmt->fetchColumn();
80    }
81
82    public function findById(int $disbursementId): ?DisbursementData
83    {
84        $sql = 'SELECT ' . self::SELECT_COLUMNS . ' FROM lending.disbursements WHERE disbursement_id = :id';
85
86        $stmt = $this->pdo->prepare($sql);
87        if ($stmt === false) {
88            throw new RuntimeException('Failed to prepare statement');
89        }
90        $stmt->execute(['id' => $disbursementId]);
91        $row = $stmt->fetch(PDO::FETCH_ASSOC);
92
93        if (!is_array($row)) {
94            return null;
95        }
96
97        return DisbursementData::fromRow($row);
98    }
99
100    /**
101     * @return list<DisbursementData>
102     */
103    public function listPending(): array
104    {
105        $sql = 'SELECT ' . self::SELECT_COLUMNS . " FROM lending.disbursements WHERE status = 'pending_disbursement' ORDER BY created_at ASC";
106
107        $stmt = $this->pdo->query($sql);
108        if ($stmt === false) {
109            throw new RuntimeException('Failed to execute query');
110        }
111
112        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
113        $out = [];
114        foreach ($rows as $row) {
115            $out[] = DisbursementData::fromRow(Row::from($row));
116        }
117
118        return $out;
119    }
120
121    /**
122     * @param string $status
123     * @param int $limit
124     * @param int $offset
125     * @return list<DisbursementData>
126     */
127    public function listHistory(string $status, int $limit, int $offset): array
128    {
129        $sql = 'SELECT ' . self::SELECT_COLUMNS . ' FROM lending.disbursements WHERE status = :status ORDER BY updated_at DESC LIMIT :limit OFFSET :offset';
130
131        $stmt = $this->pdo->prepare($sql);
132        if ($stmt === false) {
133            throw new RuntimeException('Failed to prepare statement');
134        }
135        $stmt->bindValue('status', $status, PDO::PARAM_STR);
136        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
137        $stmt->bindValue('offset', $offset, PDO::PARAM_INT);
138        $stmt->execute();
139
140        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
141        $out = [];
142        foreach ($rows as $row) {
143            $out[] = DisbursementData::fromRow(Row::from($row));
144        }
145
146        return $out;
147    }
148
149    public function countByStatus(string $status): int
150    {
151        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM lending.disbursements WHERE status = :status');
152        if ($stmt === false) {
153            throw new RuntimeException('Failed to prepare statement');
154        }
155        $stmt->execute(['status' => $status]);
156
157        return (int)$stmt->fetchColumn();
158    }
159
160    /**
161     * Transition a row to `disbursed` with confirmation details.
162     *
163     * Caller is responsible for state-machine validation (must be in
164     * `pending_disbursement`). The disbursements_disbursed_consistency_check
165     * constraint enforces that all three confirmation fields are populated.
166     * @param int $disbursementId
167     * @param string $bankReference
168     * @param string $disbursedDate
169     * @param string $confirmedAmount
170     * @param int $disbursedBy
171     */
172    public function markDisbursed(
173        int $disbursementId,
174        string $bankReference,
175        string $disbursedDate,
176        string $confirmedAmount,
177        int $disbursedBy,
178    ): void {
179        $sql = <<<SQL
180                UPDATE lending.disbursements
181                SET status = 'disbursed',
182                    bank_reference = :bank_reference,
183                    disbursed_date = :disbursed_date,
184                    confirmed_amount = :confirmed_amount,
185                    disbursed_by = :disbursed_by,
186                    updated_at = CURRENT_TIMESTAMP
187                WHERE disbursement_id = :id
188            SQL;
189
190        $stmt = $this->pdo->prepare($sql);
191        if ($stmt === false) {
192            throw new RuntimeException('Failed to prepare statement');
193        }
194        $stmt->execute([
195            'id' => $disbursementId,
196            'bank_reference' => $bankReference,
197            'disbursed_date' => $disbursedDate,
198            'confirmed_amount' => $confirmedAmount,
199            'disbursed_by' => $disbursedBy,
200        ]);
201    }
202}