Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
91.43%
128 / 140
7.69%
1 / 13
 CRAP
0.00%
0 / 1
InvestorRepository
91.43%
128 / 140
7.69%
1 / 13
 36.82
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 createInvestor
95.45%
21 / 22
0.00%
0 / 1
 2
 findInvestorById
87.50%
7 / 8
0.00%
0 / 1
 3.02
 findInvestorByEmail
87.50%
7 / 8
0.00%
0 / 1
 3.02
 findInvestorByUserId
87.50%
7 / 8
0.00%
0 / 1
 3.02
 emailExists
85.71%
6 / 7
0.00%
0 / 1
 2.01
 isEmailTakenByAnotherInvestor
85.71%
6 / 7
0.00%
0 / 1
 2.01
 updateEmailById
83.33%
5 / 6
0.00%
0 / 1
 2.02
 updateInvestor
96.55%
28 / 29
0.00%
0 / 1
 5
 updateKycStatus
91.67%
11 / 12
0.00%
0 / 1
 3.01
 updateInvestorStatus
91.67%
11 / 12
0.00%
0 / 1
 3.01
 getStripeCustomerId
90.00%
9 / 10
0.00%
0 / 1
 5.03
 setStripeCustomerId
90.00%
9 / 10
0.00%
0 / 1
 2.00
1<?php
2
3declare(strict_types=1);
4
5namespace App\Domain\Investor\Repository;
6
7use App\Domain\Investor\Data\InvestorData;
8use PDO;
9use RuntimeException;
10
11use function in_array;
12
13final class InvestorRepository
14{
15    public function __construct(
16        private readonly PDO $pdo,
17    ) {}
18
19    /**
20     * @param array<mixed> $data
21     */
22    public function createInvestor(array $data): int
23    {
24        $sql = 'INSERT INTO investors (
25                    first_name, last_name, email, phone, date_of_birth,
26                    ssn_encrypted, address_line1, address_line2, city, state,
27                    zip_code, country, kyc_status, status
28                ) VALUES (
29                    :first_name, :last_name, :email, :phone, :date_of_birth,
30                    :ssn_encrypted, :address_line1, :address_line2, :city, :state,
31                    :zip_code, :country, :kyc_status, :status
32                ) RETURNING investor_id';
33
34        $stmt = $this->pdo->prepare($sql);
35
36        if ($stmt === false) {
37            throw new RuntimeException('Failed to prepare statement');
38        }
39        $stmt->execute([
40            'first_name' => $data['firstName'],
41            'last_name' => $data['lastName'],
42            'email' => $data['email'],
43            'phone' => $data['phone'] ?? null,
44            'date_of_birth' => $data['dateOfBirth'],
45            'ssn_encrypted' => $data['ssnEncrypted'] ?? null,
46            'address_line1' => $data['addressLine1'] ?? null,
47            'address_line2' => $data['addressLine2'] ?? null,
48            'city' => $data['city'] ?? null,
49            'state' => $data['state'] ?? null,
50            'zip_code' => $data['zipCode'] ?? null,
51            'country' => $data['country'] ?? 'USA',
52            'kyc_status' => $data['kycStatus'] ?? 'pending',
53            'status' => $data['status'] ?? 'active',
54        ]);
55
56        return (int)$stmt->fetchColumn();
57    }
58
59    public function findInvestorById(int $investorId): ?InvestorData
60    {
61        $sql = 'SELECT
62                    investor_id as "investorId",
63                    first_name as "firstName",
64                    last_name as "lastName",
65                    email,
66                    phone,
67                    date_of_birth as "dateOfBirth",
68                    ssn_encrypted as "ssnEncrypted",
69                    address_line1 as "addressLine1",
70                    address_line2 as "addressLine2",
71                    city,
72                    state,
73                    zip_code as "zipCode",
74                    country,
75                    kyc_status as "kycStatus",
76                    status,
77                    created_at as "createdAt",
78                    updated_at as "updatedAt"
79                FROM investors
80                WHERE investor_id = :investor_id';
81        $stmt = $this->pdo->prepare($sql);
82        if ($stmt === false) {
83            throw new RuntimeException('Failed to prepare statement');
84        }
85        $stmt->execute(['investor_id' => $investorId]);
86
87        $row = $stmt->fetch(PDO::FETCH_ASSOC);
88
89        return is_array($row) ? InvestorData::fromRow($row) : null;
90    }
91
92    public function findInvestorByEmail(string $email): ?InvestorData
93    {
94        $sql = 'SELECT
95                    investor_id as "investorId",
96                    first_name as "firstName",
97                    last_name as "lastName",
98                    email,
99                    phone,
100                    date_of_birth as "dateOfBirth",
101                    ssn_encrypted as "ssnEncrypted",
102                    address_line1 as "addressLine1",
103                    address_line2 as "addressLine2",
104                    city,
105                    state,
106                    zip_code as "zipCode",
107                    country,
108                    kyc_status as "kycStatus",
109                    status,
110                    created_at as "createdAt",
111                    updated_at as "updatedAt"
112                FROM investors
113                WHERE email = :email';
114        $stmt = $this->pdo->prepare($sql);
115        if ($stmt === false) {
116            throw new RuntimeException('Failed to prepare statement');
117        }
118        $stmt->execute(['email' => $email]);
119
120        $row = $stmt->fetch(PDO::FETCH_ASSOC);
121
122        return is_array($row) ? InvestorData::fromRow($row) : null;
123    }
124
125    public function findInvestorByUserId(int $userId): ?InvestorData
126    {
127        $sql = 'SELECT i.investor_id AS "investorId",
128                       i.first_name AS "firstName",
129                       i.last_name AS "lastName",
130                       i.email,
131                       i.phone,
132                       i.date_of_birth AS "dateOfBirth",
133                       i.address_line1 AS "addressLine1",
134                       i.address_line2 AS "addressLine2",
135                       i.city,
136                       i.state,
137                       i.zip_code AS "zipCode",
138                       i.country,
139                       i.kyc_status AS "kycStatus",
140                       i.status,
141                       i.created_at AS "createdAt",
142                       i.updated_at AS "updatedAt"
143                FROM investors i
144                INNER JOIN users u ON u.investor_id = i.investor_id
145                WHERE u.user_id = :user_id';
146
147        $stmt = $this->pdo->prepare($sql);
148
149        if ($stmt === false) {
150            throw new RuntimeException('Failed to prepare statement');
151        }
152
153        $stmt->execute(['user_id' => $userId]);
154        $row = $stmt->fetch(PDO::FETCH_ASSOC);
155
156        return is_array($row) ? InvestorData::fromRow($row) : null;
157    }
158
159    public function emailExists(string $email): bool
160    {
161        $stmt = $this->pdo->prepare(
162            'SELECT EXISTS(SELECT 1 FROM investors WHERE email = :email)',
163        );
164
165        if ($stmt === false) {
166            throw new RuntimeException('Failed to prepare statement');
167        }
168        $stmt->execute(['email' => $email]);
169
170        return (bool)$stmt->fetchColumn();
171    }
172
173    /**
174     * True if the email belongs to a *different* investor profile. Pairs with
175     * the verified self-service email change so a customer re-confirming their
176     * own address doesn't trip the uniqueness guard (FSC-86).
177     * @param string $email
178     * @param int $excludeInvestorId
179     */
180    public function isEmailTakenByAnotherInvestor(string $email, int $excludeInvestorId): bool
181    {
182        $stmt = $this->pdo->prepare(
183            'SELECT EXISTS(SELECT 1 FROM investors WHERE email = :email AND investor_id <> :investor_id)',
184        );
185
186        if ($stmt === false) {
187            throw new RuntimeException('Failed to prepare statement');
188        }
189        $stmt->execute(['email' => $email, 'investor_id' => $excludeInvestorId]);
190
191        return (bool)$stmt->fetchColumn();
192    }
193
194    /**
195     * Update only the contact email. Deliberately separate from updateInvestor's
196     * field whitelist — email changes must go through the verified flow
197     * (EmailChangeService) so investors.email and users.email never drift (FSC-86).
198     * @param int $investorId
199     * @param string $newEmail
200     */
201    public function updateEmailById(int $investorId, string $newEmail): bool
202    {
203        $stmt = $this->pdo->prepare(
204            'UPDATE investors SET email = :email, updated_at = CURRENT_TIMESTAMP WHERE investor_id = :investor_id',
205        );
206
207        if ($stmt === false) {
208            throw new RuntimeException('Failed to prepare statement');
209        }
210
211        return $stmt->execute(['email' => $newEmail, 'investor_id' => $investorId]);
212    }
213
214    /**
215     * @param array<mixed> $data
216     * @param int $investorId
217     */
218    public function updateInvestor(int $investorId, array $data): bool
219    {
220        $fieldMapping = [
221            'firstName' => 'first_name',
222            'lastName' => 'last_name',
223            'phone' => 'phone',
224            'addressLine1' => 'address_line1',
225            'addressLine2' => 'address_line2',
226            'city' => 'city',
227            'state' => 'state',
228            'zipCode' => 'zip_code',
229            'country' => 'country',
230            'kycStatus' => 'kyc_status',
231            'status' => 'status',
232        ];
233
234        $updates = [];
235        $params = ['investor_id' => $investorId];
236
237        foreach ($data as $camelKey => $value) {
238            if (isset($fieldMapping[$camelKey])) {
239                $snakeKey = $fieldMapping[$camelKey];
240                $updates[] = "{$snakeKey} = :{$snakeKey}";
241                $params[$snakeKey] = $value;
242            }
243        }
244
245        if (empty($updates)) {
246            return false;
247        }
248
249        $updates[] = 'updated_at = CURRENT_TIMESTAMP';
250
251        $stmt = $this->pdo->prepare(
252            'UPDATE investors SET ' . implode(', ', $updates) . ' WHERE investor_id = :investor_id',
253        );
254
255        if ($stmt === false) {
256            throw new RuntimeException('Failed to prepare statement');
257        }
258
259        return $stmt->execute($params);
260    }
261
262    public function updateKycStatus(int $investorId, string $status): bool
263    {
264        if (!in_array($status, ['pending', 'verified', 'rejected'], true)) {
265            return false;
266        }
267
268        $stmt = $this->pdo->prepare(
269            'UPDATE investors SET kyc_status = :status, updated_at = CURRENT_TIMESTAMP
270             WHERE investor_id = :investor_id',
271        );
272
273        if ($stmt === false) {
274            throw new RuntimeException('Failed to prepare statement');
275        }
276
277        return $stmt->execute([
278            'status' => $status,
279            'investor_id' => $investorId,
280        ]);
281    }
282
283    public function updateInvestorStatus(int $investorId, string $status): bool
284    {
285        if (!in_array($status, ['active', 'inactive', 'suspended'], true)) {
286            return false;
287        }
288
289        $stmt = $this->pdo->prepare(
290            'UPDATE investors SET status = :status, updated_at = CURRENT_TIMESTAMP
291             WHERE investor_id = :investor_id',
292        );
293
294        if ($stmt === false) {
295            throw new RuntimeException('Failed to prepare statement');
296        }
297
298        return $stmt->execute([
299            'status' => $status,
300            'investor_id' => $investorId,
301        ]);
302    }
303
304    /**
305     * Returns the Stripe Customer ID linked to this investor, or null if
306     * none has been provisioned yet.
307     * @param int $investorId
308     */
309    public function getStripeCustomerId(int $investorId): ?string
310    {
311        $stmt = $this->pdo->prepare(
312            'SELECT stripe_customer_id FROM investors WHERE investor_id = :investor_id',
313        );
314        if ($stmt === false) {
315            throw new RuntimeException('Failed to prepare statement');
316        }
317        $stmt->execute(['investor_id' => $investorId]);
318
319        $value = $stmt->fetchColumn();
320        if ($value === false || $value === null) {
321            return null;
322        }
323
324        return is_string($value) ? $value : null;
325    }
326
327    /**
328     * Persist the Stripe Customer ID on an investor row. Called once after
329     * the Customer is created in Stripe; the column is UNIQUE so a second
330     * call with a different ID for the same investor will fail loudly.
331     * @param int $investorId
332     * @param string $stripeCustomerId
333     */
334    public function setStripeCustomerId(int $investorId, string $stripeCustomerId): void
335    {
336        $stmt = $this->pdo->prepare(
337            'UPDATE investors
338                SET stripe_customer_id = :stripe_customer_id, updated_at = CURRENT_TIMESTAMP
339                WHERE investor_id = :investor_id',
340        );
341        if ($stmt === false) {
342            throw new RuntimeException('Failed to prepare statement');
343        }
344        $stmt->execute([
345            'stripe_customer_id' => $stripeCustomerId,
346            'investor_id' => $investorId,
347        ]);
348    }
349}