Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
0.00%
0 / 236
0.00%
0 / 11
 CRAP
0.00%
0 / 1
ArticleManager
0.00%
0 / 236
0.00%
0 / 11
 2256
0.00%
0 / 1
 __construct
0.00%
0 / 1
0.00%
0 / 1
 2
 getPublishedArticles
0.00%
0 / 30
0.00%
0 / 1
 42
 getArticleBySlug
0.00%
0 / 22
0.00%
0 / 1
 30
 getArticleById
0.00%
0 / 19
0.00%
0 / 1
 20
 getFeaturedArticles
0.00%
0 / 20
0.00%
0 / 1
 20
 searchArticles
0.00%
0 / 21
0.00%
0 / 1
 20
 getAll
0.00%
0 / 46
0.00%
0 / 1
 90
 getById
0.00%
0 / 1
0.00%
0 / 1
 2
 slugExists
0.00%
0 / 12
0.00%
0 / 1
 12
 getRecentArticles
0.00%
0 / 20
0.00%
0 / 1
 20
 getCategoryArticles
0.00%
0 / 44
0.00%
0 / 1
 42
1<?php
2
3namespace BuyerKiosk\Support;
4
5use \PDO;
6use \PDOException;
7
8/**
9 * ArticleManager - Manager class for support articles
10 *
11 * Handles article retrieval with various filtering options, search,
12 * featured articles, and category-based queries for the knowledge base.
13 */
14class ArticleManager
15{
16    /**
17     * @var PDO Database connection
18     */
19    private $db;
20
21    /**
22     * Constructor
23     *
24     * @param PDO $db Database connection (kiosk_buykiosk)
25     */
26    public function __construct(PDO $db)
27    {
28        $this->db = $db;
29    }
30
31    /**
32     * Get published articles
33     *
34     * Returns articles with status='published', optionally filtered by category
35     * with pagination support. Includes category data and aggregated counts.
36     *
37     * @param int|null $categoryId Optional category ID filter
38     * @param int $limit Number of articles to return
39     * @param int $offset Starting offset for pagination
40     * @return array Array of Article objects
41     */
42    public function getPublishedArticles(?int $categoryId = null, int $limit = 20, int $offset = 0): array
43    {
44        try {
45            $sql = "
46                SELECT
47                    a.*,
48                    c.name as categoryName,
49                    c.slug as categorySlug,
50                    c.icon as categoryIcon
51                FROM support_articles a
52                LEFT JOIN support_categories c ON a.category_id = c.id
53                WHERE a.status = 'published'
54            ";
55
56            $params = [];
57
58            if ($categoryId !== null) {
59                $sql .= " AND a.category_id = :categoryId";
60                $params[':categoryId'] = $categoryId;
61            }
62
63            $sql .= " ORDER BY a.published_at DESC, a.created_at DESC";
64            $sql .= " LIMIT :limit OFFSET :offset";
65
66            $stmt = $this->db->prepare($sql);
67
68            foreach ($params as $key => $value) {
69                $stmt->bindValue($key, $value);
70            }
71            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
72            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
73
74            $stmt->execute();
75
76            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
77            $articles = [];
78
79            foreach ($rows as $row) {
80                $article = Article::fromArray($row);
81
82                // Add category object if available
83                if ($row['categoryName']) {
84                    $category = new Category();
85                    $category->id = $article->categoryId;
86                    $category->name = $row['categoryName'];
87                    $category->slug = $row['categorySlug'];
88                    $category->icon = $row['categoryIcon'];
89                    $article->category = $category;
90                }
91
92                $articles[] = $article;
93            }
94
95            return $articles;
96        } catch (PDOException $e) {
97            error_log("ArticleManager::getPublishedArticles error: " . $e->getMessage());
98            return [];
99        }
100    }
101
102    /**
103     * Get article by slug
104     *
105     * Returns article matching the given slug with full category data
106     *
107     * @param string $slug Article slug
108     * @param bool $publishedOnly If true, only return published articles (default: true)
109     * @return Article|null Article object or null if not found
110     */
111    public function getArticleBySlug(string $slug, bool $publishedOnly = true): ?Article
112    {
113        try {
114            $sql = "
115                SELECT
116                    a.*,
117                    c.name as categoryName,
118                    c.slug as categorySlug,
119                    c.icon as categoryIcon,
120                    c.description as categoryDescription
121                FROM support_articles a
122                LEFT JOIN support_categories c ON a.category_id = c.id
123                WHERE a.slug = :slug
124            ";
125
126            if ($publishedOnly) {
127                $sql .= " AND a.status = 'published'";
128            }
129
130            $stmt = $this->db->prepare($sql);
131
132            $stmt->execute([':slug' => $slug]);
133
134            $row = $stmt->fetch(PDO::FETCH_ASSOC);
135            if (!$row) {
136                return null;
137            }
138
139            $article = Article::fromArray($row);
140
141            // Add category object if available
142            if ($row['categoryName']) {
143                $category = new Category();
144                $category->id = $article->categoryId;
145                $category->name = $row['categoryName'];
146                $category->slug = $row['categorySlug'];
147                $category->icon = $row['categoryIcon'];
148                $category->description = $row['categoryDescription'];
149                $article->category = $category;
150            }
151
152            return $article;
153        } catch (PDOException $e) {
154            error_log("ArticleManager::getArticleBySlug error: " . $e->getMessage());
155            return null;
156        }
157    }
158
159    /**
160     * Get article by ID
161     *
162     * Returns article (any status) by ID with full category data
163     *
164     * @param int $id Article ID
165     * @return Article|null Article object or null if not found
166     */
167    public function getArticleById(int $id): ?Article
168    {
169        try {
170            $stmt = $this->db->prepare("
171                SELECT
172                    a.*,
173                    c.name as categoryName,
174                    c.slug as categorySlug,
175                    c.icon as categoryIcon,
176                    c.description as categoryDescription
177                FROM support_articles a
178                LEFT JOIN support_categories c ON a.category_id = c.id
179                WHERE a.id = :id
180            ");
181
182            $stmt->execute([':id' => $id]);
183
184            $row = $stmt->fetch(PDO::FETCH_ASSOC);
185            if (!$row) {
186                return null;
187            }
188
189            $article = Article::fromArray($row);
190
191            // Add category object if available
192            if ($row['categoryName']) {
193                $category = new Category();
194                $category->id = $article->categoryId;
195                $category->name = $row['categoryName'];
196                $category->slug = $row['categorySlug'];
197                $category->icon = $row['categoryIcon'];
198                $category->description = $row['categoryDescription'];
199                $article->category = $category;
200            }
201
202            return $article;
203        } catch (PDOException $e) {
204            error_log("ArticleManager::getArticleById error: " . $e->getMessage());
205            return null;
206        }
207    }
208
209    /**
210     * Get featured articles
211     *
212     * Returns published articles marked as featured, ordered by published date
213     *
214     * @param int $limit Maximum number of featured articles to return
215     * @return array Array of Article objects
216     */
217    public function getFeaturedArticles(int $limit = 5): array
218    {
219        try {
220            $stmt = $this->db->prepare("
221                SELECT
222                    a.*,
223                    c.name as categoryName,
224                    c.slug as categorySlug,
225                    c.icon as categoryIcon
226                FROM support_articles a
227                LEFT JOIN support_categories c ON a.category_id = c.id
228                WHERE a.status = 'published' AND a.is_featured = 1
229                ORDER BY a.published_at DESC, a.created_at DESC
230                LIMIT :limit
231            ");
232
233            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
234            $stmt->execute();
235
236            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
237            $articles = [];
238
239            foreach ($rows as $row) {
240                $article = Article::fromArray($row);
241
242                // Add category object if available
243                if ($row['categoryName']) {
244                    $category = new Category();
245                    $category->id = $article->categoryId;
246                    $category->name = $row['categoryName'];
247                    $category->slug = $row['categorySlug'];
248                    $category->icon = $row['categoryIcon'];
249                    $article->category = $category;
250                }
251
252                $articles[] = $article;
253            }
254
255            return $articles;
256        } catch (PDOException $e) {
257            error_log("ArticleManager::getFeaturedArticles error: " . $e->getMessage());
258            return [];
259        }
260    }
261
262    /**
263     * Search articles using FULLTEXT search
264     *
265     * Searches article titles and content using MySQL FULLTEXT index
266     *
267     * @param string $query Search query
268     * @param int $limit Maximum number of results to return
269     * @return array Array of Article objects with relevance score
270     */
271    public function searchArticles(string $query, int $limit = 20): array
272    {
273        try {
274            // Use FULLTEXT search with MATCH...AGAINST
275            $stmt = $this->db->prepare("
276                SELECT
277                    a.*,
278                    c.name as categoryName,
279                    c.slug as categorySlug,
280                    c.icon as categoryIcon,
281                    MATCH(a.title, a.content_md) AGAINST(:query IN NATURAL LANGUAGE MODE) as relevance
282                FROM support_articles a
283                LEFT JOIN support_categories c ON a.category_id = c.id
284                WHERE a.status = 'published'
285                  AND MATCH(a.title, a.content_md) AGAINST(:query IN NATURAL LANGUAGE MODE)
286                ORDER BY relevance DESC, a.published_at DESC
287                LIMIT :limit
288            ");
289
290            $stmt->bindValue(':query', $query);
291            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
292            $stmt->execute();
293
294            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
295            $articles = [];
296
297            foreach ($rows as $row) {
298                $article = Article::fromArray($row);
299
300                // Add category object if available
301                if ($row['categoryName']) {
302                    $category = new Category();
303                    $category->id = $article->categoryId;
304                    $category->name = $row['categoryName'];
305                    $category->slug = $row['categorySlug'];
306                    $category->icon = $row['categoryIcon'];
307                    $article->category = $category;
308                }
309
310                $articles[] = $article;
311            }
312
313            return $articles;
314        } catch (PDOException $e) {
315            error_log("ArticleManager::searchArticles error: " . $e->getMessage());
316            return [];
317        }
318    }
319
320    /**
321     * Get all articles with optional filters (admin)
322     *
323     * Returns articles with any status, filtered by optional status and category.
324     * Used by admin interface.
325     *
326     * @param string|null $status Optional status filter (draft, published, archived)
327     * @param int|null $categoryId Optional category ID filter
328     * @param int $limit Number of articles to return
329     * @param int $offset Starting offset for pagination
330     * @return array Array with 'articles' and 'total' keys
331     */
332    public function getAll(?string $status = null, ?int $categoryId = null, int $limit = 20, int $offset = 0): array
333    {
334        try {
335            $where = [];
336            $params = [];
337
338            if ($status !== null) {
339                $where[] = "a.status = :status";
340                $params[':status'] = $status;
341            }
342
343            if ($categoryId !== null) {
344                $where[] = "a.category_id = :categoryId";
345                $params[':categoryId'] = $categoryId;
346            }
347
348            $whereClause = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';
349
350            // Get total count
351            $countSql = "SELECT COUNT(*) as total FROM support_articles a {$whereClause}";
352            $countStmt = $this->db->prepare($countSql);
353            foreach ($params as $key => $value) {
354                $countStmt->bindValue($key, $value);
355            }
356            $countStmt->execute();
357            $total = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];
358
359            // Get articles
360            $sql = "
361                SELECT
362                    a.*,
363                    c.name as categoryName,
364                    c.slug as categorySlug,
365                    c.icon as categoryIcon
366                FROM support_articles a
367                LEFT JOIN support_categories c ON a.category_id = c.id
368                {$whereClause}
369                ORDER BY a.updated_at DESC, a.created_at DESC
370                LIMIT :limit OFFSET :offset
371            ";
372
373            $stmt = $this->db->prepare($sql);
374            foreach ($params as $key => $value) {
375                $stmt->bindValue($key, $value);
376            }
377            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
378            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
379            $stmt->execute();
380
381            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
382            $articles = [];
383
384            foreach ($rows as $row) {
385                $article = Article::fromArray($row);
386
387                // Add category object if available
388                if ($row['categoryName']) {
389                    $category = new Category();
390                    $category->id = $article->categoryId;
391                    $category->name = $row['categoryName'];
392                    $category->slug = $row['categorySlug'];
393                    $category->icon = $row['categoryIcon'];
394                    $article->category = $category;
395                }
396
397                $articles[] = $article;
398            }
399
400            return [
401                'articles' => $articles,
402                'total' => (int) $total
403            ];
404        } catch (PDOException $e) {
405            error_log("ArticleManager::getAll error: " . $e->getMessage());
406            return [
407                'articles' => [],
408                'total' => 0
409            ];
410        }
411    }
412
413    /**
414     * Get article by ID (any status, for admin)
415     *
416     * @param int $id Article ID
417     * @return Article|null Article object or null if not found
418     */
419    public function getById(int $id): ?Article
420    {
421        return $this->getArticleById($id);
422    }
423
424    /**
425     * Check if a slug exists
426     *
427     * @param string $slug Article slug to check
428     * @param int|null $excludeId Optional article ID to exclude from check
429     * @return bool True if slug exists
430     */
431    public function slugExists(string $slug, ?int $excludeId = null): bool
432    {
433        try {
434            $sql = "SELECT COUNT(*) as cnt FROM support_articles WHERE slug = :slug";
435            $params = [':slug' => $slug];
436
437            if ($excludeId !== null) {
438                $sql .= " AND id != :excludeId";
439                $params[':excludeId'] = $excludeId;
440            }
441
442            $stmt = $this->db->prepare($sql);
443            $stmt->execute($params);
444            $result = $stmt->fetch(PDO::FETCH_ASSOC);
445
446            return $result['cnt'] > 0;
447        } catch (PDOException $e) {
448            error_log("ArticleManager::slugExists error: " . $e->getMessage());
449            return false;
450        }
451    }
452
453    /**
454     * Get recent articles
455     *
456     * Returns most recently published articles across all categories
457     *
458     * @param int $limit Maximum number of articles to return
459     * @return array Array of Article objects
460     */
461    public function getRecentArticles(int $limit = 10): array
462    {
463        try {
464            $stmt = $this->db->prepare("
465                SELECT
466                    a.*,
467                    c.name as categoryName,
468                    c.slug as categorySlug,
469                    c.icon as categoryIcon
470                FROM support_articles a
471                LEFT JOIN support_categories c ON a.category_id = c.id
472                WHERE a.status = 'published'
473                ORDER BY a.published_at DESC, a.created_at DESC
474                LIMIT :limit
475            ");
476
477            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
478            $stmt->execute();
479
480            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
481            $articles = [];
482
483            foreach ($rows as $row) {
484                $article = Article::fromArray($row);
485
486                // Add category object if available
487                if ($row['categoryName']) {
488                    $category = new Category();
489                    $category->id = $article->categoryId;
490                    $category->name = $row['categoryName'];
491                    $category->slug = $row['categorySlug'];
492                    $category->icon = $row['categoryIcon'];
493                    $article->category = $category;
494                }
495
496                $articles[] = $article;
497            }
498
499            return $articles;
500        } catch (PDOException $e) {
501            error_log("ArticleManager::getRecentArticles error: " . $e->getMessage());
502            return [];
503        }
504    }
505
506    /**
507     * Get category articles by category slug
508     *
509     * Returns published articles for a specific category identified by slug
510     *
511     * @param string $categorySlug Category slug
512     * @param int $limit Number of articles to return
513     * @param int $offset Starting offset for pagination
514     * @return array Array with 'articles', 'category', and 'hasMore' keys
515     */
516    public function getCategoryArticles(string $categorySlug, int $limit = 20, int $offset = 0): array
517    {
518        try {
519            // First get the category
520            $categoryStmt = $this->db->prepare("
521                SELECT * FROM support_categories
522                WHERE slug = :slug AND is_active = 1
523            ");
524            $categoryStmt->execute([':slug' => $categorySlug]);
525            $categoryRow = $categoryStmt->fetch(PDO::FETCH_ASSOC);
526
527            if (!$categoryRow) {
528                return [
529                    'articles' => [],
530                    'category' => null,
531                    'hasMore' => false
532                ];
533            }
534
535            $category = Category::fromArray($categoryRow);
536
537            // Get articles with limit + 1 to check if there are more
538            $stmt = $this->db->prepare("
539                SELECT
540                    a.*,
541                    c.name as categoryName,
542                    c.slug as categorySlug,
543                    c.icon as categoryIcon
544                FROM support_articles a
545                LEFT JOIN support_categories c ON a.category_id = c.id
546                WHERE a.status = 'published' AND a.category_id = :categoryId
547                ORDER BY a.published_at DESC, a.created_at DESC
548                LIMIT :limit OFFSET :offset
549            ");
550
551            $stmt->bindValue(':categoryId', $category->id, PDO::PARAM_INT);
552            $stmt->bindValue(':limit', $limit + 1, PDO::PARAM_INT);
553            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
554            $stmt->execute();
555
556            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
557
558            // Check if there are more results
559            $hasMore = count($rows) > $limit;
560            if ($hasMore) {
561                array_pop($rows); // Remove the extra row
562            }
563
564            $articles = [];
565            foreach ($rows as $row) {
566                $article = Article::fromArray($row);
567
568                // Add category object
569                if ($row['categoryName']) {
570                    $articleCategory = new Category();
571                    $articleCategory->id = $article->categoryId;
572                    $articleCategory->name = $row['categoryName'];
573                    $articleCategory->slug = $row['categorySlug'];
574                    $articleCategory->icon = $row['categoryIcon'];
575                    $article->category = $articleCategory;
576                }
577
578                $articles[] = $article;
579            }
580
581            return [
582                'articles' => $articles,
583                'category' => $category,
584                'hasMore' => $hasMore
585            ];
586        } catch (PDOException $e) {
587            error_log("ArticleManager::getCategoryArticles error: " . $e->getMessage());
588            return [
589                'articles' => [],
590                'category' => null,
591                'hasMore' => false
592            ];
593        }
594    }
595}