Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
96.61%
171 / 177
94.44%
17 / 18
 CRAP
0.00%
0 / 1
SqlCommentRepository
96.59%
170 / 176
94.44%
17 / 18
 45
0.00%
0 / 1
 __construct
100.00%
4 / 4
100.00%
1 / 1
 1
 getSelectCommentsBase
100.00%
7 / 7
100.00%
1 / 1
 1
 getSelectAttachmentsBase
100.00%
3 / 3
100.00%
1 / 1
 1
 findStream
100.00%
20 / 20
100.00%
1 / 1
 5
 countStream
100.00%
13 / 13
100.00%
1 / 1
 4
 getStreamCounts
100.00%
18 / 18
100.00%
1 / 1
 2
 findById
100.00%
9 / 9
100.00%
1 / 1
 2
 save
100.00%
27 / 27
100.00%
1 / 1
 3
 togglePin
100.00%
9 / 9
100.00%
1 / 1
 1
 toggleVerify
100.00%
9 / 9
100.00%
1 / 1
 1
 delete
100.00%
4 / 4
100.00%
1 / 1
 1
 saveAttachment
100.00%
15 / 15
100.00%
1 / 1
 1
 findAttachmentById
100.00%
6 / 6
100.00%
1 / 1
 2
 findAttachmentByToken
100.00%
6 / 6
100.00%
1 / 1
 2
 deleteAttachment
100.00%
4 / 4
100.00%
1 / 1
 1
 buildHierarchyWhereClause
57.14%
8 / 14
0.00%
0 / 1
 20.52
 hasHierarchyRollup
100.00%
4 / 4
100.00%
1 / 1
 1
 buildScopeCondition
100.00%
4 / 4
100.00%
1 / 1
 5
1<?php
2
3declare(strict_types=1);
4
5/** @license For full copyright and license information, please see the LICENSE.md file. */
6
7namespace App\Modules\Comments\Infrastructure\Repository;
8
9defined('AMMONLY_APP') || exit('Direct script access is forbidden.');
10
11use App\Modules\Comments\Domain\Model\Comment;
12use App\Modules\Comments\Domain\Model\CommentAttachment;
13use App\Modules\Comments\Domain\Model\CommentFilterScope;
14use App\Modules\Comments\Domain\Repository\CommentRepositoryInterface;
15use App\Modules\Comments\Infrastructure\Hydrator\CommentRelationHydrator;
16use PDO;
17
18/**
19 * SQL database implementation of CommentRepositoryInterface using prepared statements.
20 *
21 * @package App\Modules\Comments\Infrastructure\Repository
22 */
23final readonly class SqlCommentRepository implements CommentRepositoryInterface
24{
25    private string $tableComments;
26    private string $tableAttachments;
27    private string $tableUsers;
28
29    private const string PARAM_REC_ID = ':rec_id';
30    private const string PARAM_ROLLUP_REC_ID = ':rollup_rec_id';
31    private const string PARAM_USER_ID = ':user_id';
32    private const string SQL_WHERE_ID = ' WHERE id = :id';
33    private const string SQL_UPDATE_PREFIX = 'UPDATE ';
34
35    private CommentRelationHydrator $hydrator;
36
37    /**
38     * SqlCommentRepository constructor.
39     *
40     * @param PDO                          $pdo         Active database connection.
41     * @param CommentRelationHydrator|null $hydrator    Eager loading and hydrator engine.
42     * @param string                       $tablePrefix Database table prefix (default 'c_').
43     */
44    public function __construct(
45        private PDO $pdo,
46        ?CommentRelationHydrator $hydrator = null,
47        private string $tablePrefix = 'c_'
48    ) {
49        $this->tableComments = $this->tablePrefix . 'mod_comments_records';
50        $this->tableAttachments = $this->tablePrefix . 'mod_comment_attachments_records';
51        $this->tableUsers = $this->tablePrefix . 'mod_users_records';
52        $this->hydrator = $hydrator ?? new CommentRelationHydrator($this->pdo, $this->tablePrefix);
53    }
54
55    private function getSelectCommentsBase(): string
56    {
57        return 'SELECT c.id, c.parent_id, c.content, c.is_pinned, c.is_verified, ' .
58            'c.target_module, c.target_record_id, c.related_party_ref, c.company_id, ' .
59            'c.partner_id, c.contact_id, c.process_ref, c.project_id, c.contract_id, ' .
60            'c.subprocess_ref, c.ticket_id, c.task_id, c.stage_id, c.owner, ' .
61            'c.created_at, c.updated_at, u.username, u.first_name, u.last_name ' .
62            "FROM `{$this->tableComments}` c " .
63            "LEFT JOIN `{$this->tableUsers}` u ON u.id = c.owner";
64    }
65
66    private function getSelectAttachmentsBase(): string
67    {
68        return 'SELECT id, comment_id, file_name, file_path, file_size, ' .
69            'file_extension, mime_type, token, created_by, created_at ' .
70            "FROM `{$this->tableAttachments}`";
71    }
72
73    /**
74     * @inheritDoc
75     */
76    public function findStream(
77        string $module,
78        int $recordId,
79        CommentFilterScope $scope,
80        ?int $currentUserId,
81        int $limit = 100,
82        int $offset = 0
83    ): array {
84        $whereSql = $this->buildHierarchyWhereClause($module);
85        $scopeSql = $this->buildScopeCondition($scope);
86
87        $sql = $this->getSelectCommentsBase() . "
88                WHERE  c.special_access = 1
89                  AND  ({$whereSql})
90                  {$scopeSql}
91                ORDER BY c.is_pinned DESC, c.created_at DESC, c.id DESC
92                LIMIT :limit OFFSET :offset";
93
94        $stmt = $this->pdo->prepare($sql);
95        $stmt->bindValue(':mod', $module, PDO::PARAM_STR);
96        $stmt->bindValue(self::PARAM_REC_ID, $recordId, PDO::PARAM_INT);
97        if ($this->hasHierarchyRollup($module)) {
98            $stmt->bindValue(self::PARAM_ROLLUP_REC_ID, $recordId, PDO::PARAM_INT);
99        }
100        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
101        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
102
103        if ($scope === CommentFilterScope::MINE && $currentUserId !== null) {
104            $stmt->bindValue(self::PARAM_USER_ID, $currentUserId, PDO::PARAM_INT);
105        }
106
107        $stmt->execute();
108        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
109        if ($rows === []) {
110            return [];
111        }
112
113        return $this->hydrator->hydrateCommentsWithRelations($rows);
114    }
115
116    /**
117     * @inheritDoc
118     */
119    public function countStream(
120        string $module,
121        int $recordId,
122        CommentFilterScope $scope,
123        ?int $currentUserId
124    ): int {
125        $whereSql = $this->buildHierarchyWhereClause($module);
126        $scopeSql = $this->buildScopeCondition($scope);
127
128        $sql = "SELECT COUNT(*) FROM " . $this->tableComments . " c
129                WHERE c.special_access = 1 AND ({$whereSql}) {$scopeSql}";
130
131        $stmt = $this->pdo->prepare($sql);
132        $stmt->bindValue(':mod', $module, PDO::PARAM_STR);
133        $stmt->bindValue(self::PARAM_REC_ID, $recordId, PDO::PARAM_INT);
134        if ($this->hasHierarchyRollup($module)) {
135            $stmt->bindValue(self::PARAM_ROLLUP_REC_ID, $recordId, PDO::PARAM_INT);
136        }
137
138        if ($scope === CommentFilterScope::MINE && $currentUserId !== null) {
139            $stmt->bindValue(self::PARAM_USER_ID, $currentUserId, PDO::PARAM_INT);
140        }
141
142        $stmt->execute();
143
144        return (int) $stmt->fetchColumn();
145    }
146
147    /**
148     * @inheritDoc
149     */
150    public function getStreamCounts(
151        string $module,
152        int $recordId,
153        ?int $currentUserId
154    ): array {
155        $whereSql = $this->buildHierarchyWhereClause($module);
156
157        $sql = "SELECT
158                    COUNT(*) AS total_all,
159                    COALESCE(SUM(CASE WHEN c.owner = :user_id THEN 1 ELSE 0 END), 0) AS total_mine,
160                    COALESCE(SUM(CASE WHEN c.is_pinned = 1 THEN 1 ELSE 0 END), 0) AS total_pinned,
161                    COALESCE(SUM(CASE WHEN c.is_verified = 1 THEN 1 ELSE 0 END), 0) AS total_verified
162                FROM " . $this->tableComments . " c
163                WHERE c.special_access = 1 AND ({$whereSql})";
164
165        $stmt = $this->pdo->prepare($sql);
166        $stmt->bindValue(':mod', $module, PDO::PARAM_STR);
167        $stmt->bindValue(self::PARAM_REC_ID, $recordId, PDO::PARAM_INT);
168        if ($this->hasHierarchyRollup($module)) {
169            $stmt->bindValue(self::PARAM_ROLLUP_REC_ID, $recordId, PDO::PARAM_INT);
170        }
171        $stmt->bindValue(self::PARAM_USER_ID, $currentUserId ?? 0, PDO::PARAM_INT);
172        $stmt->execute();
173
174        $row = $stmt->fetch(PDO::FETCH_ASSOC);
175
176        return [
177            'all'      => (int) ($row['total_all'] ?? 0),
178            'mine'     => (int) ($row['total_mine'] ?? 0),
179            'pinned'   => (int) ($row['total_pinned'] ?? 0),
180            'verified' => (int) ($row['total_verified'] ?? 0),
181        ];
182    }
183
184    /**
185     * @inheritDoc
186     */
187    public function findById(int $commentId): ?Comment
188    {
189        $sql = $this->getSelectCommentsBase() . "
190                WHERE  c.id = :id AND c.special_access = 1";
191
192        $stmt = $this->pdo->prepare($sql);
193        $stmt->execute([':id' => $commentId]);
194        $row = $stmt->fetch(PDO::FETCH_ASSOC);
195        if ($row === false) {
196            return null;
197        }
198
199        $comments = $this->hydrator->hydrateCommentsWithRelations([$row]);
200
201        return $comments[0] ?? null;
202    }
203
204    /**
205     * @inheritDoc
206     */
207    public function save(Comment $comment): int
208    {
209        $sql = "INSERT INTO " . $this->tableComments . " (
210                    parent_id, content, is_pinned, is_verified, target_module,
211                    target_record_id, related_party_ref, company_id, partner_id,
212                    contact_id, process_ref, project_id, contract_id, subprocess_ref,
213                    ticket_id, task_id, stage_id, created_by, owner,
214                    created_at, updated_at, special_access
215                ) VALUES (
216                    :parent_id, :content, :is_pinned, :is_verified, :target_module,
217                    :target_record_id, :related_party_ref, :company_id, :partner_id,
218                    :contact_id, :process_ref, :project_id, :contract_id, :subprocess_ref,
219                    :ticket_id, :task_id, :stage_id, :created_by, :owner,
220                    :created_at, :updated_at, 1
221                )";
222
223        $stmt = $this->pdo->prepare($sql);
224        $stmt->execute([
225            ':parent_id'         => $comment->parentId,
226            ':content'           => $comment->content,
227            ':is_pinned'         => $comment->isPinned ? 1 : 0,
228            ':is_verified'       => $comment->isVerified ? 1 : 0,
229            ':target_module'     => $comment->targetModule,
230            ':target_record_id'  => $comment->targetRecordId,
231            ':related_party_ref' => $comment->relatedPartyRef,
232            ':company_id'        => $comment->companyId,
233            ':partner_id'        => $comment->partnerId,
234            ':contact_id'        => $comment->contactId,
235            ':process_ref'       => $comment->processRef,
236            ':project_id'        => $comment->projectId,
237            ':contract_id'       => $comment->contractId,
238            ':subprocess_ref'    => $comment->subprocessRef,
239            ':ticket_id'         => $comment->ticketId,
240            ':task_id'           => $comment->taskId,
241            ':stage_id'          => $comment->stageId,
242            ':created_by'        => $comment->owner,
243            ':owner'             => $comment->owner,
244            ':created_at'        => $comment->createdAt,
245            ':updated_at'        => $comment->updatedAt,
246        ]);
247
248        return (int) $this->pdo->lastInsertId();
249    }
250
251    /**
252     * @inheritDoc
253     */
254    public function togglePin(int $commentId): bool
255    {
256        $stmt = $this->pdo->prepare(
257            self::SQL_UPDATE_PREFIX . $this->tableComments . " SET is_pinned = 1 - is_pinned" . self::SQL_WHERE_ID
258        );
259        $stmt->execute([':id' => $commentId]);
260
261        $check = $this->pdo->prepare(
262            "SELECT is_pinned FROM " . $this->tableComments . self::SQL_WHERE_ID
263        );
264        $check->execute([':id' => $commentId]);
265
266        return (bool) $check->fetchColumn();
267    }
268
269    /**
270     * @inheritDoc
271     */
272    public function toggleVerify(int $commentId): bool
273    {
274        $stmt = $this->pdo->prepare(
275            self::SQL_UPDATE_PREFIX . $this->tableComments . " SET is_verified = 1 - is_verified" . self::SQL_WHERE_ID
276        );
277        $stmt->execute([':id' => $commentId]);
278
279        $check = $this->pdo->prepare(
280            "SELECT is_verified FROM " . $this->tableComments . self::SQL_WHERE_ID
281        );
282        $check->execute([':id' => $commentId]);
283
284        return (bool) $check->fetchColumn();
285    }
286
287    /**
288     * @inheritDoc
289     */
290    public function delete(int $commentId): bool
291    {
292        $stmt = $this->pdo->prepare(
293            self::SQL_UPDATE_PREFIX . $this->tableComments . " SET special_access = 3" . self::SQL_WHERE_ID
294        );
295        return $stmt->execute([':id' => $commentId]);
296    }
297
298    /**
299     * @inheritDoc
300     */
301    public function saveAttachment(CommentAttachment $attachment): int
302    {
303        $sql = "INSERT INTO " . $this->tableAttachments . " (
304                    comment_id, file_name, file_path, file_size,
305                    file_extension, mime_type, token, created_by, created_at
306                ) VALUES (
307                    :comment_id, :file_name, :file_path, :file_size,
308                    :file_extension, :mime_type, :token, :created_by, :created_at
309                )";
310
311        $stmt = $this->pdo->prepare($sql);
312        $stmt->execute([
313            ':comment_id'     => $attachment->commentId,
314            ':file_name'      => $attachment->fileName,
315            ':file_path'      => $attachment->filePath,
316            ':file_size'      => $attachment->fileSize,
317            ':file_extension' => $attachment->fileExtension,
318            ':mime_type'      => $attachment->mimeType,
319            ':token'          => $attachment->token,
320            ':created_by'     => $attachment->createdBy,
321            ':created_at'     => $attachment->createdAt,
322        ]);
323
324        return (int) $this->pdo->lastInsertId();
325    }
326
327    /**
328     * @inheritDoc
329     */
330    public function findAttachmentById(int $attachmentId): ?CommentAttachment
331    {
332        $stmt = $this->pdo->prepare(
333            $this->getSelectAttachmentsBase() . self::SQL_WHERE_ID
334        );
335        $stmt->execute([':id' => $attachmentId]);
336        $row = $stmt->fetch(PDO::FETCH_ASSOC);
337
338        return $row !== false ? $this->hydrator->hydrateAttachment($row) : null;
339    }
340
341    /**
342     * @inheritDoc
343     */
344    public function findAttachmentByToken(string $token): ?CommentAttachment
345    {
346        $stmt = $this->pdo->prepare(
347            $this->getSelectAttachmentsBase() . " WHERE token = :token"
348        );
349        $stmt->execute([':token' => $token]);
350        $row = $stmt->fetch(PDO::FETCH_ASSOC);
351
352        return $row !== false ? $this->hydrator->hydrateAttachment($row) : null;
353    }
354
355    /**
356     * @inheritDoc
357     */
358    public function deleteAttachment(int $attachmentId): bool
359    {
360        $stmt = $this->pdo->prepare(
361            "DELETE FROM " . $this->tableAttachments . self::SQL_WHERE_ID
362        );
363        return $stmt->execute([':id' => $attachmentId]);
364    }
365
366    private function buildHierarchyWhereClause(string $module): string
367    {
368        $directMatch = "(c.target_module = :mod AND c.target_record_id = " . self::PARAM_REC_ID . ")";
369
370        $rollupCol = match ($module) {
371            'companies'      => 'c.company_id',
372            'partners'       => 'c.partner_id',
373            'contacts'       => 'c.contact_id',
374            'projects'       => 'c.project_id',
375            'contracts'      => 'c.contract_id',
376            'tickets'        => 'c.ticket_id',
377            'project_tasks'  => 'c.task_id',
378            'project_stages' => 'c.stage_id',
379            default          => null,
380        };
381
382        return $rollupCol !== null
383            ? "{$directMatch} OR {$rollupCol} = " . self::PARAM_ROLLUP_REC_ID
384            : $directMatch;
385    }
386
387    private function hasHierarchyRollup(string $module): bool
388    {
389        return in_array($module, [
390            'companies', 'partners', 'contacts', 'projects',
391            'contracts', 'tickets', 'project_tasks', 'project_stages',
392        ], true);
393    }
394
395    private function buildScopeCondition(CommentFilterScope $scope): string
396    {
397        return match ($scope) {
398            CommentFilterScope::MINE     => "AND c.owner = :user_id",
399            CommentFilterScope::PINNED   => "AND c.is_pinned = 1",
400            CommentFilterScope::VERIFIED => "AND c.is_verified = 1",
401            CommentFilterScope::ALL      => "",
402        };
403    }
404}