Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
71.89%
133 / 185
4.76%
1 / 21
 CRAP
0.00%
0 / 1
AuthRepository
71.89%
133 / 185
4.76%
1 / 21
 105.52
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 findUserByEmail
88.89%
8 / 9
0.00%
0 / 1
 3.01
 findUserByUsername
88.89%
8 / 9
0.00%
0 / 1
 3.01
 findUserById
88.89%
8 / 9
0.00%
0 / 1
 3.01
 getPasswordHash
87.50%
7 / 8
0.00%
0 / 1
 3.02
 createUser
92.86%
13 / 14
0.00%
0 / 1
 2.00
 emailExists
85.71%
6 / 7
0.00%
0 / 1
 2.01
 usernameExists
85.71%
6 / 7
0.00%
0 / 1
 2.01
 storeRefreshToken
92.86%
13 / 14
0.00%
0 / 1
 2.00
 findSessionByRefreshToken
88.89%
8 / 9
0.00%
0 / 1
 3.01
 revokeRefreshToken
85.71%
6 / 7
0.00%
0 / 1
 2.01
 revokeAllUserTokens
85.71%
6 / 7
0.00%
0 / 1
 2.01
 cleanupExpiredTokens
85.71%
6 / 7
0.00%
0 / 1
 2.01
 updateLastLogin
85.71%
6 / 7
0.00%
0 / 1
 2.01
 incrementFailedLoginAttempts
85.71%
6 / 7
0.00%
0 / 1
 2.01
 lockAccount
90.00%
9 / 10
0.00%
0 / 1
 2.00
 isAccountLocked
94.12%
16 / 17
0.00%
0 / 1
 5.01
 storePasswordResetToken
0.00%
0 / 10
0.00%
0 / 1
 6
 findValidResetToken
0.00%
0 / 14
0.00%
0 / 1
 12
 markResetTokenUsed
0.00%
0 / 6
0.00%
0 / 1
 6
 updatePassword
0.00%
0 / 6
0.00%
0 / 1
 6
1<?php
2
3declare(strict_types=1);
4
5namespace App\Domain\Auth\Repository;
6
7use App\Domain\Auth\Data\UserAuthData;
8use App\Support\Row;
9use DateTimeImmutable;
10use PDO;
11use RuntimeException;
12
13final class AuthRepository
14{
15    public function __construct(
16        private readonly PDO $pdo,
17    ) {}
18
19    public function findUserByEmail(string $email): ?UserAuthData
20    {
21        $stmt = $this->pdo->prepare(
22            'SELECT user_id as "userId",
23                    investor_id as "investorId",
24                    username,
25                    email,
26                    password_hash as "passwordHash",
27                    role,
28                    is_active as "isActive",
29                    last_login as "lastLogin",
30                    failed_login_attempts as "failedLoginAttempts",
31                    locked_until as "lockedUntil"
32             FROM users
33             WHERE email = :email',
34        );
35
36        if ($stmt === false) {
37            throw new RuntimeException('Failed to prepare statement');
38        }
39        $stmt->execute(['email' => $email]);
40        $row = $stmt->fetch(PDO::FETCH_ASSOC);
41
42        return is_array($row) ? UserAuthData::fromRow($row) : null;
43    }
44
45    public function findUserByUsername(string $username): ?UserAuthData
46    {
47        $stmt = $this->pdo->prepare(
48            'SELECT user_id as "userId",
49                    investor_id as "investorId",
50                    username,
51                    email,
52                    password_hash as "passwordHash",
53                    role,
54                    is_active as "isActive",
55                    last_login as "lastLogin",
56                    failed_login_attempts as "failedLoginAttempts",
57                    locked_until as "lockedUntil"
58             FROM users
59             WHERE username = :username',
60        );
61
62        if ($stmt === false) {
63            throw new RuntimeException('Failed to prepare statement');
64        }
65        $stmt->execute(['username' => $username]);
66        $row = $stmt->fetch(PDO::FETCH_ASSOC);
67
68        return is_array($row) ? UserAuthData::fromRow($row) : null;
69    }
70
71    public function findUserById(int $userId): ?UserAuthData
72    {
73        $stmt = $this->pdo->prepare(
74            'SELECT user_id as "userId",
75                    investor_id as "investorId",
76                    username,
77                    email,
78                    password_hash as "passwordHash",
79                    role,
80                    is_active as "isActive",
81                    last_login as "lastLogin",
82                    failed_login_attempts as "failedLoginAttempts",
83                    locked_until as "lockedUntil"
84             FROM users
85             WHERE user_id = :user_id',
86        );
87
88        if ($stmt === false) {
89            throw new RuntimeException('Failed to prepare statement');
90        }
91        $stmt->execute(['user_id' => $userId]);
92        $row = $stmt->fetch(PDO::FETCH_ASSOC);
93
94        return is_array($row) ? UserAuthData::fromRow($row) : null;
95    }
96
97    public function getPasswordHash(int $userId): ?string
98    {
99        $stmt = $this->pdo->prepare(
100            'SELECT password_hash FROM users WHERE user_id = :user_id',
101        );
102
103        if ($stmt === false) {
104            throw new RuntimeException('Failed to prepare statement');
105        }
106        $stmt->execute(['user_id' => $userId]);
107        $result = $stmt->fetchColumn();
108
109        return $result !== false ? (string)$result : null;
110    }
111
112    public function createUser(
113        ?int $investorId,
114        string $username,
115        string $email,
116        string $passwordHash,
117        string $role = 'investor',
118    ): int {
119        $stmt = $this->pdo->prepare(
120            'INSERT INTO users (investor_id, username, email, password_hash, role, is_active, created_at, updated_at)
121             VALUES (:investor_id, :username, :email, :password_hash, :role, true, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
122             RETURNING user_id',
123        );
124
125        if ($stmt === false) {
126            throw new RuntimeException('Failed to prepare statement');
127        }
128        $stmt->execute([
129            'investor_id' => $investorId,
130            'username' => $username,
131            'email' => $email,
132            'password_hash' => $passwordHash,
133            'role' => $role,
134        ]);
135
136        return (int)$stmt->fetchColumn();
137    }
138
139    public function emailExists(string $email): bool
140    {
141        $stmt = $this->pdo->prepare(
142            'SELECT COUNT(*) FROM users WHERE email = :email',
143        );
144
145        if ($stmt === false) {
146            throw new RuntimeException('Failed to prepare statement');
147        }
148        $stmt->execute(['email' => $email]);
149
150        return (int)$stmt->fetchColumn() > 0;
151    }
152
153    public function usernameExists(string $username): bool
154    {
155        $stmt = $this->pdo->prepare(
156            'SELECT COUNT(*) FROM users WHERE username = :username',
157        );
158
159        if ($stmt === false) {
160            throw new RuntimeException('Failed to prepare statement');
161        }
162        $stmt->execute(['username' => $username]);
163
164        return (int)$stmt->fetchColumn() > 0;
165    }
166
167    public function storeRefreshToken(
168        int $userId,
169        string $refreshToken,
170        DateTimeImmutable $expiresAt,
171        ?string $ipAddress = null,
172        ?string $userAgent = null,
173    ): int {
174        $stmt = $this->pdo->prepare(
175            'INSERT INTO user_sessions (user_id, refresh_token, expires_at, ip_address, user_agent, created_at)
176             VALUES (:user_id, :refresh_token, :expires_at, :ip_address, :user_agent, CURRENT_TIMESTAMP)
177             RETURNING session_id',
178        );
179
180        if ($stmt === false) {
181            throw new RuntimeException('Failed to prepare statement');
182        }
183        $stmt->execute([
184            'user_id' => $userId,
185            'refresh_token' => $refreshToken,
186            'expires_at' => $expiresAt->format('Y-m-d H:i:sP'),
187            'ip_address' => $ipAddress,
188            'user_agent' => $userAgent,
189        ]);
190
191        return (int)$stmt->fetchColumn();
192    }
193
194    /**
195     * @param string $refreshToken
196     * @return array<mixed>|null
197     */
198    public function findSessionByRefreshToken(string $refreshToken): ?array
199    {
200        $stmt = $this->pdo->prepare(
201            'SELECT session_id as "sessionId",
202                    user_id as "userId",
203                    refresh_token as "refreshToken",
204                    expires_at as "expiresAt",
205                    ip_address as "ipAddress",
206                    user_agent as "userAgent",
207                    created_at as "createdAt"
208             FROM user_sessions
209             WHERE refresh_token = :refresh_token
210             AND expires_at > CURRENT_TIMESTAMP',
211        );
212        if ($stmt === false) {
213            throw new RuntimeException('Failed to prepare statement');
214        }
215
216        $stmt->execute(['refresh_token' => $refreshToken]);
217        $row = $stmt->fetch(PDO::FETCH_ASSOC);
218
219        return is_array($row) ? $row : null;
220    }
221
222    public function revokeRefreshToken(string $refreshToken): bool
223    {
224        $stmt = $this->pdo->prepare(
225            'DELETE FROM user_sessions WHERE refresh_token = :refresh_token',
226        );
227
228        if ($stmt === false) {
229            throw new RuntimeException('Failed to prepare statement');
230        }
231        $stmt->execute(['refresh_token' => $refreshToken]);
232
233        return $stmt->rowCount() > 0;
234    }
235
236    public function revokeAllUserTokens(int $userId): int
237    {
238        $stmt = $this->pdo->prepare(
239            'DELETE FROM user_sessions WHERE user_id = :user_id',
240        );
241
242        if ($stmt === false) {
243            throw new RuntimeException('Failed to prepare statement');
244        }
245        $stmt->execute(['user_id' => $userId]);
246
247        return $stmt->rowCount();
248    }
249
250    public function cleanupExpiredTokens(): int
251    {
252        $stmt = $this->pdo->prepare(
253            'DELETE FROM user_sessions WHERE expires_at < CURRENT_TIMESTAMP',
254        );
255
256        if ($stmt === false) {
257            throw new RuntimeException('Failed to prepare statement');
258        }
259        $stmt->execute();
260
261        return $stmt->rowCount();
262    }
263
264    public function updateLastLogin(int $userId): void
265    {
266        $stmt = $this->pdo->prepare(
267            'UPDATE users
268             SET last_login = CURRENT_TIMESTAMP,
269                 failed_login_attempts = 0,
270                 locked_until = NULL,
271                 updated_at = CURRENT_TIMESTAMP
272             WHERE user_id = :user_id',
273        );
274
275        if ($stmt === false) {
276            throw new RuntimeException('Failed to prepare statement');
277        }
278        $stmt->execute(['user_id' => $userId]);
279    }
280
281    public function incrementFailedLoginAttempts(int $userId): void
282    {
283        $stmt = $this->pdo->prepare(
284            'UPDATE users
285             SET failed_login_attempts = failed_login_attempts + 1,
286                 updated_at = CURRENT_TIMESTAMP
287             WHERE user_id = :user_id',
288        );
289
290        if ($stmt === false) {
291            throw new RuntimeException('Failed to prepare statement');
292        }
293        $stmt->execute(['user_id' => $userId]);
294    }
295
296    public function lockAccount(int $userId, DateTimeImmutable $lockedUntil): void
297    {
298        $stmt = $this->pdo->prepare(
299            'UPDATE users
300             SET locked_until = :locked_until,
301                 updated_at = CURRENT_TIMESTAMP
302             WHERE user_id = :user_id',
303        );
304
305        if ($stmt === false) {
306            throw new RuntimeException('Failed to prepare statement');
307        }
308        $stmt->execute([
309            'user_id' => $userId,
310            'locked_until' => $lockedUntil->format('Y-m-d H:i:sP'),
311        ]);
312    }
313
314    public function isAccountLocked(int $userId): bool
315    {
316        $stmt = $this->pdo->prepare(
317            'SELECT locked_until FROM users WHERE user_id = :user_id AND locked_until IS NOT NULL',
318        );
319
320        if ($stmt === false) {
321            throw new RuntimeException('Failed to prepare statement');
322        }
323        $stmt->execute(['user_id' => $userId]);
324
325        $lockedUntil = $stmt->fetchColumn();
326
327        if ($lockedUntil === false) {
328            return false;
329        }
330
331        if (new DateTimeImmutable((string)$lockedUntil) <= new DateTimeImmutable()) {
332            // Lock expired — reset attempts so user isn't immediately re-locked
333            $reset = $this->pdo->prepare(
334                'UPDATE users SET failed_login_attempts = 0, locked_until = NULL WHERE user_id = :user_id',
335            );
336
337            if ($reset !== false) {
338                $reset->execute(['user_id' => $userId]);
339            }
340
341            return false;
342        }
343
344        return true;
345    }
346
347    public function storePasswordResetToken(int $userId, string $token, DateTimeImmutable $expiresAt): void
348    {
349        $stmt = $this->pdo->prepare(
350            'INSERT INTO password_reset_tokens (user_id, token, expires_at) VALUES (:user_id, :token, :expires_at)',
351        );
352
353        if ($stmt === false) {
354            throw new RuntimeException('Failed to prepare statement');
355        }
356        $stmt->execute([
357            'user_id' => $userId,
358            'token' => $token,
359            'expires_at' => $expiresAt->format('Y-m-d H:i:sP'),
360        ]);
361    }
362
363    /**
364     * @param string $token
365     * @return array{userId: int, tokenId: int}|null
366     */
367    public function findValidResetToken(string $token): ?array
368    {
369        $stmt = $this->pdo->prepare(
370            'SELECT token_id AS "tokenId", user_id AS "userId" FROM password_reset_tokens
371             WHERE token = :token AND expires_at > CURRENT_TIMESTAMP AND used_at IS NULL',
372        );
373
374        if ($stmt === false) {
375            throw new RuntimeException('Failed to prepare statement');
376        }
377        $stmt->execute(['token' => $token]);
378        $row = $stmt->fetch(PDO::FETCH_ASSOC);
379
380        if (!is_array($row)) {
381            return null;
382        }
383
384        return [
385            'userId' => Row::int($row, 'userId'),
386            'tokenId' => Row::int($row, 'tokenId'),
387        ];
388    }
389
390    public function markResetTokenUsed(int $tokenId): void
391    {
392        $stmt = $this->pdo->prepare(
393            'UPDATE password_reset_tokens SET used_at = CURRENT_TIMESTAMP WHERE token_id = :token_id',
394        );
395
396        if ($stmt === false) {
397            throw new RuntimeException('Failed to prepare statement');
398        }
399        $stmt->execute(['token_id' => $tokenId]);
400    }
401
402    public function updatePassword(int $userId, string $passwordHash): void
403    {
404        $stmt = $this->pdo->prepare(
405            'UPDATE users SET password_hash = :hash, failed_login_attempts = 0, locked_until = NULL, updated_at = CURRENT_TIMESTAMP WHERE user_id = :user_id',
406        );
407
408        if ($stmt === false) {
409            throw new RuntimeException('Failed to prepare statement');
410        }
411        $stmt->execute(['hash' => $passwordHash, 'user_id' => $userId]);
412    }
413}