Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
98.31%
116 / 118
80.00%
8 / 10
 CRAP
0.00%
0 / 1
CommentsRepository
98.31%
116 / 118
80.00%
8 / 10
 26
0.00%
0 / 1
 __construct
100.00%
1 / 1
100.00%
1 / 1
 1
 fetchByReferenceIdAndType
100.00%
11 / 11
100.00%
1 / 1
 2
 fetchPaginated
100.00%
18 / 18
100.00%
1 / 1
 4
 countByReferenceIdAndType
92.31%
12 / 13
0.00%
0 / 1
 4.01
 insert
100.00%
17 / 17
100.00%
1 / 1
 3
 deleteByTypeAndId
100.00%
9 / 9
100.00%
1 / 1
 1
 countByTypeGroupedByRecordId
100.00%
6 / 6
100.00%
1 / 1
 2
 countByCategoryForFaq
100.00%
6 / 6
100.00%
1 / 1
 2
 fetchAllWithCategories
100.00%
22 / 22
100.00%
1 / 1
 4
 isCommentAllowed
93.33%
14 / 15
0.00%
0 / 1
 3.00
1<?php
2
3/**
4 * Repository for comments-related database operations
5 *
6 * This Source Code Form is subject to the terms of the Mozilla Public License,
7 * v. 2.0. If a copy of the MPL was not distributed with this file, You can
8 * obtain one at https://mozilla.org/MPL/2.0/.
9 *
10 * @package   phpMyFAQ
11 * @author    Thorsten Rinne <thorsten@phpmyfaq.de>
12 * @copyright 2025-2026 phpMyFAQ Team
13 * @license   https://www.mozilla.org/MPL/2.0/ Mozilla Public License Version 2.0
14 * @link      https://www.phpmyfaq.de
15 * @since     2025-11-04
16 */
17
18declare(strict_types=1);
19
20namespace phpMyFAQ\Comment;
21
22use phpMyFAQ\Configuration as CoreConfiguration;
23use phpMyFAQ\Database;
24use phpMyFAQ\Entity\Comment;
25use phpMyFAQ\Entity\CommentType;
26
27readonly class CommentsRepository implements CommentsRepositoryInterface
28{
29    public function __construct(
30        private CoreConfiguration $coreConfiguration,
31    ) {
32    }
33
34    /**
35     * @return array<int, \stdClass>
36     */
37    public function fetchByReferenceIdAndType(int $referenceId, string $type): array
38    {
39        $sql = <<<SQL
40                SELECT
41                    id_comment, id, usr, email, comment, datum
42                FROM
43                    %sfaqcomments
44                WHERE
45                    type = '%s'
46                AND
47                    id = %d
48            SQL;
49
50        $query = sprintf(
51            $sql,
52            Database::getTablePrefix(),
53            $this->coreConfiguration->getDb()->escape($type),
54            $referenceId,
55        );
56
57        $result = $this->coreConfiguration->getDb()->query($query);
58        $rows = $this->coreConfiguration->getDb()->fetchAll($result);
59        return is_array($rows) ? $rows : [];
60    }
61
62    /**
63     * Fetch comments with pagination and sorting
64     *
65     * @param int $referenceId Record ID
66     * @param string $type Type (faq or news)
67     * @param int $limit Items per page
68     * @param int $offset Offset for pagination
69     * @param string $sortField Field to sort by
70     * @param string $sortOrder Sort order (ASC or DESC)
71     * @return array<int, \stdClass>
72     */
73    public function fetchPaginated(
74        int $referenceId,
75        string $type,
76        int $limit,
77        int $offset,
78        string $sortField = 'id_comment',
79        string $sortOrder = 'ASC',
80    ): array {
81        $allowedSortFields = ['id_comment', 'id', 'usr', 'email', 'datum'];
82        $sortField = in_array($sortField, $allowedSortFields, strict: true) ? $sortField : 'id_comment';
83        $sortOrder = strtoupper($sortOrder) === 'DESC' ? 'DESC' : 'ASC';
84
85        $sql = <<<SQL
86                SELECT
87                    id_comment, id, usr, email, comment, datum
88                FROM
89                    %sfaqcomments
90                WHERE
91                    type = '%s'
92                AND
93                    id = %d
94                ORDER BY
95                    %s %s
96                LIMIT %d OFFSET %d
97            SQL;
98
99        $query = sprintf(
100            $sql,
101            Database::getTablePrefix(),
102            $this->coreConfiguration->getDb()->escape($type),
103            $referenceId,
104            $sortField,
105            $sortOrder,
106            $limit,
107            $offset,
108        );
109
110        $result = $this->coreConfiguration->getDb()->query($query);
111        $rows = $this->coreConfiguration->getDb()->fetchAll($result);
112        return is_array($rows) ? $rows : [];
113    }
114
115    /**
116     * Count total comments for a reference ID and type
117     *
118     * @param int $referenceId Record ID
119     * @param string $type Type (faq or news)
120     * @return int
121     */
122    public function countByReferenceIdAndType(int $referenceId, string $type): int
123    {
124        $sql = <<<SQL
125                SELECT
126                    COUNT(*) AS total
127                FROM
128                    %sfaqcomments
129                WHERE
130                    type = '%s'
131                AND
132                    id = %d
133            SQL;
134
135        $query = sprintf(
136            $sql,
137            Database::getTablePrefix(),
138            $this->coreConfiguration->getDb()->escape($type),
139            $referenceId,
140        );
141
142        $result = $this->coreConfiguration->getDb()->query($query);
143        $row = $this->coreConfiguration->getDb()->fetchObject($result);
144        if ($row !== false && $row !== null && $row !== []) {
145            return (int) $row->total;
146        }
147
148        return 0;
149    }
150
151    public function insert(Comment $comment): bool
152    {
153        $helpedValue = $comment->hasHelped();
154        $helpedSql = $helpedValue === null ? 'NULL' : "'" . ($helpedValue ? 'y' : 'n') . "'";
155
156        $sql = <<<SQL
157                INSERT INTO
158                    %sfaqcomments (id_comment, id, type, usr, email, comment, datum, helped)
159                VALUES
160                    (%d, %d, '%s', '%s', '%s', '%s', '%s', %s)
161            SQL;
162
163        $query = sprintf(
164            $sql,
165            Database::getTablePrefix(),
166            $this->coreConfiguration->getDb()->nextId(Database::getTablePrefix() . 'faqcomments', 'id_comment'),
167            $comment->getRecordId(),
168            $this->coreConfiguration->getDb()->escape($comment->getType()),
169            $this->coreConfiguration->getDb()->escape($comment->getUsername()),
170            $this->coreConfiguration->getDb()->escape($comment->getEmail()),
171            $this->coreConfiguration->getDb()->escape($comment->getComment()),
172            $this->coreConfiguration->getDb()->escape($comment->getDate()),
173            $helpedSql,
174        );
175
176        return (bool) $this->coreConfiguration->getDb()->query($query);
177    }
178
179    public function deleteByTypeAndId(string $type, int $commentId): bool
180    {
181        $sql = <<<SQL
182                DELETE FROM
183                    %sfaqcomments
184                WHERE
185                    type = '%s'
186                AND
187                    id_comment = %d
188            SQL;
189
190        $query = sprintf(
191            $sql,
192            Database::getTablePrefix(),
193            $this->coreConfiguration->getDb()->escape($type),
194            $commentId,
195        );
196
197        return (bool) $this->coreConfiguration->getDb()->query($query);
198    }
199
200    /**
201     * @return array<int, \stdClass>
202     */
203    public function countByTypeGroupedByRecordId(string $type = CommentType::FAQ): array
204    {
205        $sql = <<<SQL
206                SELECT
207                    COUNT(id) AS anz,
208                    id
209                FROM
210                    %sfaqcomments
211                WHERE
212                    type = '%s'
213                GROUP BY id
214                ORDER BY id
215            SQL;
216
217        $query = sprintf($sql, Database::getTablePrefix(), $this->coreConfiguration->getDb()->escape($type));
218
219        $result = $this->coreConfiguration->getDb()->query($query);
220        $rows = $this->coreConfiguration->getDb()->fetchAll($result);
221        return is_array($rows) ? $rows : [];
222    }
223
224    /**
225     * @return array<int, \stdClass>
226     */
227    public function countByCategoryForFaq(): array
228    {
229        $sql = <<<SQL
230                SELECT
231                    COUNT(fc.id) AS number,
232                    fcg.category_id AS category_id
233                FROM
234                    %sfaqcomments fc
235                LEFT JOIN
236                    %sfaqcategoryrelations fcg
237                ON
238                    fc.id = fcg.record_id
239                WHERE
240                    fc.type = '%s'
241                GROUP BY fcg.category_id
242                ORDER BY fcg.category_id
243            SQL;
244
245        $query = sprintf($sql, Database::getTablePrefix(), Database::getTablePrefix(), CommentType::FAQ);
246
247        $result = $this->coreConfiguration->getDb()->query($query);
248        $rows = $this->coreConfiguration->getDb()->fetchAll($result);
249        return is_array($rows) ? $rows : [];
250    }
251
252    /**
253     * @return array<int, \stdClass>
254     */
255    public function fetchAllWithCategories(string $type = CommentType::FAQ): array
256    {
257        $prefix = Database::getTablePrefix();
258        $escapedType = $this->coreConfiguration->getDb()->escape($type);
259
260        $query = '';
261        if ($type === CommentType::FAQ) {
262            $query = sprintf(
263                'SELECT fc.id_comment AS comment_id, fc.id AS record_id, fcg.category_id, fc.usr AS username, '
264                . 'fc.email AS email, fc.comment AS comment, fc.datum AS comment_date FROM %sfaqcomments fc '
265                . "LEFT JOIN %sfaqcategoryrelations fcg ON fc.id = fcg.record_id WHERE type = '%s'",
266                $prefix,
267                $prefix,
268                $escapedType,
269            );
270        }
271        if ($type !== CommentType::FAQ) {
272            $query = sprintf(
273                'SELECT fc.id_comment AS comment_id, fc.id AS record_id, fc.usr AS username, fc.email AS email, '
274                . "fc.comment AS comment, fc.datum AS comment_date FROM %sfaqcomments fc WHERE type = '%s'",
275                $prefix,
276                $escapedType,
277            );
278        }
279
280        $result = $this->coreConfiguration->getDb()->query($query);
281        $rows = $this->coreConfiguration->getDb()->fetchAll($result);
282        return is_array($rows) ? $rows : [];
283    }
284
285    public function isCommentAllowed(int $recordId, string $recordLang, string $commentType = 'faq'): bool
286    {
287        $table = 'news' === $commentType ? 'faqnews' : 'faqdata';
288
289        $sql = <<<SQL
290                SELECT
291                    comment
292                FROM
293                    %s%s
294                WHERE
295                    id = %d
296                AND
297                    lang = '%s'
298            SQL;
299
300        $query = sprintf(
301            $sql,
302            Database::getTablePrefix(),
303            $table,
304            $recordId,
305            $this->coreConfiguration->getDb()->escape($recordLang),
306        );
307
308        $result = $this->coreConfiguration->getDb()->query($query);
309        $row = $this->coreConfiguration->getDb()->fetchObject($result);
310        if ($row instanceof \stdClass) {
311            return $row->comment === 'y';
312        }
313
314        return false;
315    }
316}