<?php
declare(strict_types=1);

namespace App\Topics;

use PDO;

final class TopicRepository
{
    public function __construct(private readonly PDO $pdo) {}

    /** @return array{items:list<array<string,mixed>>,total:int,page:int,per_page:int,total_pages:int} */
    public function listing(array $filters, int $page, int $perPage): array
    {
        $page = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        $where = [];
        $params = [];
        $status = trim((string) ($filters['status'] ?? 'published'));
        if ($status !== '' && $status !== 'all') { $where[] = 't.status=:status'; $params['status'] = $status; }
        $categoryId = (int) ($filters['category_id'] ?? 0);
        if ($categoryId <= 0 && trim((string) ($filters['category'] ?? '')) !== '') {
            $categoryValue = trim((string) $filters['category']);
            if (ctype_digit($categoryValue)) {
                $categoryId = (int) $categoryValue;
            } else {
                $categoryLookup = $this->pdo->prepare('SELECT id FROM categories WHERE slug=:slug LIMIT 1');
                $categoryLookup->execute(['slug' => $categoryValue]);
                $categoryId = (int) ($categoryLookup->fetchColumn() ?: 0);
            }
        }
        if ($categoryId > 0) { $where[] = 't.category_id=:category_id'; $params['category_id'] = $categoryId; }
        $locale = strtolower(str_replace('_', '-', trim((string) ($filters['locale'] ?? ''))));
        if ($locale !== '') { $where[] = 't.content_locale=:content_locale'; $params['content_locale'] = $locale; }
        $kind = strtolower(trim((string) ($filters['kind'] ?? '')));
        if (in_array($kind, ['discussion', 'announcement'], true)) { $where[] = 't.kind=:kind'; $params['kind'] = $kind; }
        $search = trim((string) ($filters['search'] ?? ''));
        if ($search !== '') {
            $escaped = str_replace(['|', '%', '_'], ['||', '|%', '|_'], $search);
            $where[] = "(t.title LIKE :search ESCAPE '|' OR t.body LIKE :search_body ESCAPE '|')";
            $params['search'] = '%' . $escaped . '%';
            $params['search_body'] = '%' . $escaped . '%';
        }
        $condition = $where === [] ? '1=1' : implode(' AND ', $where);
        $count = $this->pdo->prepare("SELECT COUNT(*) FROM topics t WHERE {$condition}");
        $count->execute($params);
        $total = (int) $count->fetchColumn();
        $totalPages = max(1, (int) ceil($total / $perPage));
        $page = min($page, $totalPages);
        $offset = ($page - 1) * $perPage;
        $query = $this->pdo->prepare(
            "SELECT t.*,u.name AS author_name,u.username AS author_username,u.avatar_url AS author_avatar_url,u.account_type AS author_account_type,c.name AS category_name,c.slug AS category_slug
             FROM topics t LEFT JOIN users u ON u.id=t.user_id LEFT JOIN categories c ON c.id=t.category_id
             WHERE {$condition}
             ORDER BY t.is_pinned DESC,(t.kind='announcement') DESC,t.last_activity_at DESC,t.id DESC
             LIMIT {$perPage} OFFSET {$offset}"
        );
        $query->execute($params);
        return ['items' => $query->fetchAll(PDO::FETCH_ASSOC), 'total' => $total, 'page' => $page, 'per_page' => $perPage, 'total_pages' => $totalPages];
    }

    public function findBySlug(string $slug): ?array
    {
        $statement = $this->pdo->prepare(
            "SELECT t.*,u.name AS author_name,u.username AS author_username,u.avatar_url AS author_avatar_url,u.account_type AS author_account_type,c.name AS category_name,c.slug AS category_slug
             FROM topics t LEFT JOIN users u ON u.id=t.user_id LEFT JOIN categories c ON c.id=t.category_id
             WHERE t.slug=:slug AND t.status='published' LIMIT 1"
        );
        $statement->execute(['slug' => trim($slug)]);
        $topic = $statement->fetch(PDO::FETCH_ASSOC);
        return is_array($topic) ? $topic : null;
    }
}
