<?php

class ArticleCategory
{
    private PDO $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function all(): array
    {
        return $this->db->query('SELECT * FROM article_categories ORDER BY sort_order ASC, name ASC')->fetchAll();
    }

    public function findBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM article_categories WHERE slug = :s LIMIT 1');
        $stmt->execute(['s' => $slug]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM article_categories WHERE id = :id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    /**
     * Returns all categories with the latest published-article updated_at as `last_mod`.
     * Categories with no published articles get last_mod = null.
     */
    public function allWithLastMod(): array
    {
        $sql = 'SELECT c.*, MAX(a.updated_at) AS last_mod
                FROM article_categories c
                LEFT JOIN articles a ON a.category_id = c.id AND a.status = "published"
                GROUP BY c.id
                ORDER BY c.sort_order ASC, c.name ASC';
        return $this->db->query($sql)->fetchAll();
    }

    public function withCounts(?string $locale = null): array
    {
        $sql = 'SELECT c.*, COUNT(a.id) AS article_count
                FROM article_categories c
                LEFT JOIN articles a ON a.category_id = c.id AND a.status = "published"'
                . ($locale ? ' AND a.locale = :loc' : '') .
                ' GROUP BY c.id ORDER BY c.sort_order ASC, c.name ASC';
        $stmt = $this->db->prepare($sql);
        if ($locale) {
            $stmt->bindValue(':loc', $locale);
        }
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function create(array $data): int
    {
        $stmt = $this->db->prepare('INSERT INTO article_categories (slug, name, description, sort_order) VALUES (:slug, :name, :description, :sort_order)');
        $stmt->execute([
            'slug' => $data['slug'],
            'name' => $data['name'],
            'description' => $data['description'] ?? null,
            'sort_order' => (int)($data['sort_order'] ?? 0),
        ]);
        return (int)$this->db->lastInsertId();
    }
}