<?php

require_once APP_ROOT . '/helpers/Slug.php';

class ArticleTag
{
    private PDO $db;

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

    public function findOrCreateByName(string $name): array
    {
        $name = trim($name);
        if ($name === '') {
            return [];
        }
        $slug = Slug::make($name, 80);
        $stmt = $this->db->prepare('SELECT * FROM article_tags WHERE slug = :s LIMIT 1');
        $stmt->execute(['s' => $slug]);
        $row = $stmt->fetch();
        if ($row) {
            return $row;
        }
        $ins = $this->db->prepare('INSERT INTO article_tags (slug, name) VALUES (:s, :n)');
        $ins->execute(['s' => $slug, 'n' => $name]);
        $id = (int)$this->db->lastInsertId();
        return ['id' => $id, 'slug' => $slug, 'name' => $name];
    }

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

    public function forArticle(int $articleId): array
    {
        $stmt = $this->db->prepare('SELECT t.* FROM article_tags t
            INNER JOIN article_tag_map m ON m.tag_id = t.id
            WHERE m.article_id = :aid ORDER BY t.name ASC');
        $stmt->execute(['aid' => $articleId]);
        return $stmt->fetchAll();
    }

    /**
     * Returns all tags that have at least one published article, with `last_mod`
     * set to MAX(article.updated_at) for that tag. Used for sitemap generation.
     */
    public function allWithLastMod(int $limit = 1000): array
    {
        $sql = 'SELECT t.*, MAX(a.updated_at) AS last_mod
                FROM article_tags t
                INNER JOIN article_tag_map m ON m.tag_id = t.id
                INNER JOIN articles a ON a.id = m.article_id AND a.status = "published"
                GROUP BY t.id
                ORDER BY t.name ASC
                LIMIT ' . (int)$limit;
        return $this->db->query($sql)->fetchAll();
    }

    public function topTags(int $limit = 20, ?string $locale = null): array
    {
        $sql = 'SELECT t.*, COUNT(m.article_id) AS cnt
                FROM article_tags t
                INNER JOIN article_tag_map m ON m.tag_id = t.id
                INNER JOIN articles a ON a.id = m.article_id AND a.status = "published"'
                . ($locale ? ' AND a.locale = :loc' : '') .
                ' GROUP BY t.id
                ORDER BY cnt DESC, t.name ASC
                LIMIT ' . (int)$limit;
        $stmt = $this->db->prepare($sql);
        if ($locale) {
            $stmt->bindValue(':loc', $locale);
        }
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function syncForArticle(int $articleId, array $tagNames): void
    {
        $this->db->prepare('DELETE FROM article_tag_map WHERE article_id = :a')->execute(['a' => $articleId]);
        $seen = [];
        foreach ($tagNames as $name) {
            $name = trim($name);
            if ($name === '' || isset($seen[mb_strtolower($name)])) {
                continue;
            }
            $seen[mb_strtolower($name)] = true;
            $tag = $this->findOrCreateByName($name);
            if (!empty($tag['id'])) {
                $this->db->prepare('INSERT IGNORE INTO article_tag_map (article_id, tag_id) VALUES (:a, :t)')
                    ->execute(['a' => $articleId, 't' => $tag['id']]);
            }
        }
    }
}