<?php

declare(strict_types=1);

class Tag
{
    public static function find(int $id): ?array
    {
        return Database::fetchOne('SELECT * FROM tags WHERE id = ?', [$id]);
    }

    public static function findBySlug(string $slug): ?array
    {
        $vt = Video::activeType();
        $locale = Translator::locale();
        $defaultLang = Translator::getDefaultCode();

         
        if ($locale !== $defaultLang) {
            $q = 'SELECT tt.tag_id FROM tag_translations tt
                  JOIN tags t ON tt.tag_id = t.id
                  WHERE tt.slug = ? AND tt.lang_code = ?';
            $p = [$slug, $locale];
            if ($vt !== '') { $q .= ' AND t.video_type = ?'; $p[] = $vt; }
            $trans = Database::fetchOne($q, $p);
            if ($trans) {
                $tag = self::find((int) $trans['tag_id']);
                return $tag ? self::localize($tag) : null;
            }
        }

         
        $q = 'SELECT * FROM tags WHERE slug = ?';
        $p = [$slug];
        if ($vt !== '') { $q .= ' AND video_type = ?'; $p[] = $vt; }
        $tag = Database::fetchOne($q, $p);

         
        if (!$tag && $vt !== '') {
            $tag = Database::fetchOne('SELECT * FROM tags WHERE slug = ?', [$slug]);
        }

        return $tag ? self::localize($tag) : null;
    }

    public static function all(string $search = '', int $limit = 0, int $offset = 0): array
    {
        $limitClause = $limit > 0 ? ' LIMIT ? OFFSET ?' : '';

        if ($search !== '') {
             
            if (mb_strlen($search) >= 3) {
                $params = [$search];
                if ($limit > 0) { $params[] = $limit; $params[] = $offset; }
                return Database::fetchAll(
                    'SELECT * FROM tags WHERE MATCH(name) AGAINST(? IN BOOLEAN MODE) ORDER BY name ASC' . $limitClause,
                    $params
                );
            }
            $params = [$search . '%'];
            if ($limit > 0) { $params[] = $limit; $params[] = $offset; }
            return Database::fetchAll(
                'SELECT * FROM tags WHERE name LIKE ? ORDER BY name ASC' . $limitClause,
                $params
            );
        }

        $params = [];
        if ($limit > 0) { $params[] = $limit; $params[] = $offset; }
        return Database::fetchAll(
            'SELECT * FROM tags ORDER BY name ASC' . $limitClause,
            $params
        );
    }

    public static function count(string $search = '', string $videoType = ''): int
    {
        $where = [];
        $params = [];
        if ($search !== '') {
            if (mb_strlen($search) >= 3) {
                $where[] = 'MATCH(name) AGAINST(? IN BOOLEAN MODE)';
            } else {
                $where[] = 'name LIKE ?';
                $search .= '%';
            }
            $params[] = $search;
        }
        if ($videoType !== '') {
            $where[] = 'video_type = ?';
            $params[] = $videoType;
        }
        if (!empty($where)) {
            return Database::count('tags', implode(' AND ', $where), $params);
        }
        return Database::count('tags');
    }

    

    

    public static function allGroupedByLetter(string $videoType = '', int $maxPerLetter = 200): array
    {
        $where = '';
        $params = [];
        if ($videoType !== '') {
            $where = ' WHERE video_type = ?';
            $params[] = $videoType;
        }

         
        $letterRows = Database::fetchAll(
            "SELECT UPPER(LEFT(name, 1)) as letter, COUNT(*) as cnt FROM tags{$where} GROUP BY letter ORDER BY letter",
            $params
        );

         
        $letterCounts = [];
        foreach ($letterRows as $lr) {
            $l = $lr['letter'];
            $k = ctype_alpha($l) ? $l : '#';
            $letterCounts[$k] = ($letterCounts[$k] ?? 0) + (int) $lr['cnt'];
        }

         
        $grouped = [];
        foreach ($letterCounts as $letter => $count) {
            if ($letter === '#') {
                $letterWhere = $where !== '' ? $where . " AND name NOT REGEXP '^[A-Za-z]'" : " WHERE name NOT REGEXP '^[A-Za-z]'";
            } else {
                $letterWhere = $where !== '' ? $where . " AND name LIKE ?" : " WHERE name LIKE ?";
                $params2 = $videoType !== '' ? [$videoType, $letter . '%'] : [$letter . '%'];
            }

            $q = "SELECT id, name, slug, video_count FROM tags" . ($letter === '#' ? $letterWhere : $letterWhere) . " ORDER BY name ASC LIMIT ?";
            $p = $letter === '#' ? array_merge($params, [$maxPerLetter]) : array_merge($params2, [$maxPerLetter]);

            $rows = Database::fetchAll($q, $p);
            $rows = self::localizeResults($rows);

            if (!empty($rows)) {
                $grouped[$letter] = $rows;
            }
        }

         
        uksort($grouped, function ($a, $b) {
            if ($a === '#') return -1;
            if ($b === '#') return 1;
            return $a <=> $b;
        });

        return $grouped;
    }

    public static function popular(int $limit = 50): array
    {
        return Database::fetchAll(
            'SELECT * FROM tags ORDER BY video_count DESC, name ASC LIMIT ?',
            [$limit]
        );
    }

    

    public static function letterCounts(): array
    {
        $rows = Database::fetchAll(
            "SELECT UPPER(LEFT(name, 1)) as letter, COUNT(*) as cnt FROM tags GROUP BY letter"
        );
        $counts = [];
        foreach ($rows as $row) {
            $l = $row['letter'];
            if (!ctype_alpha($l)) {
                $counts['#'] = ($counts['#'] ?? 0) + (int) $row['cnt'];
            } else {
                $counts[$l] = (int) $row['cnt'];
            }
        }
        return $counts;
    }

    

    public static function allFiltered(array $filters, int $limit, int $offset): array
    {
        $where = [];
        $params = [];

        if (!empty($filters['letter'])) {
            if ($filters['letter'] === '#') {
                $where[] = "name NOT REGEXP '^[A-Za-z]'";
            } else {
                $where[] = "name LIKE ?";
                $params[] = $filters['letter'] . '%';
            }
        }

        if (!empty($filters['q'])) {
            if (mb_strlen($filters['q']) >= 3) {
                $where[] = "MATCH(name) AGAINST(? IN BOOLEAN MODE)";
                $params[] = $filters['q'];
            } else {
                $where[] = "name LIKE ?";
                $params[] = $filters['q'] . '%';
            }
        }

        $whereClause = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        $sort = $filters['sort'] ?? 'popular';
        $orderBy = match ($sort) {
            'alpha'  => 'name ASC',
            'newest' => 'id DESC',
            default  => 'video_count DESC, name ASC',
        };

        $params[] = $limit;
        $params[] = $offset;

        return Database::fetchAll(
            "SELECT * FROM tags {$whereClause} ORDER BY {$orderBy} LIMIT ? OFFSET ?",
            $params
        );
    }

    

    public static function countFiltered(array $filters): int
    {
        $where = [];
        $params = [];

        if (!empty($filters['letter'])) {
            if ($filters['letter'] === '#') {
                $where[] = "name NOT REGEXP '^[A-Za-z]'";
            } else {
                $where[] = "name LIKE ?";
                $params[] = $filters['letter'] . '%';
            }
        }

        if (!empty($filters['q'])) {
            if (mb_strlen($filters['q']) >= 3) {
                $where[] = "MATCH(name) AGAINST(? IN BOOLEAN MODE)";
                $params[] = $filters['q'];
            } else {
                $where[] = "name LIKE ?";
                $params[] = $filters['q'] . '%';
            }
        }

        $whereClause = !empty($where) ? implode(' AND ', $where) : '1';

        return Database::count('tags', $whereClause, $params);
    }

    public static function create(array $data): int
    {
        return Database::insert('tags', $data);
    }

    public static function update(int $id, array $data): void
    {
        Database::update('tags', $data, 'id = ?', [$id]);
    }

    public static function delete(int $id): void
    {
        Database::delete('tags', 'id = ?', [$id]);
    }

    public static function findOrCreate(string $name, string $videoType = Video::DEFAULT_TYPE): int
    {
        $slug = Slug::generate($name);
        $existing = Database::fetchOne('SELECT id FROM tags WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
        if ($existing) {
            return (int) $existing['id'];
        }
        try {
            return Database::insert('tags', ['name' => trim($name), 'slug' => $slug, 'video_type' => $videoType]);
        } catch (\PDOException $e) {
            if ($e->getCode() === '23000') {
                $existing = Database::fetchOne('SELECT id FROM tags WHERE slug = ? AND video_type = ?', [$slug, $videoType]);
                if ($existing) {
                    return (int) $existing['id'];
                }
            }
            throw $e;
        }
    }

    

    public static function updateCounts(int $id): void
    {
        $row = Database::fetchOne(
            "SELECT COUNT(*) as cnt FROM video_tags vt
             JOIN videos v ON vt.video_id = v.id
             WHERE vt.tag_id = ? AND v.status = 'published'",
            [$id]
        );
        Database::update('tags', [
            'video_count' => (int) ($row['cnt'] ?? 0),
        ], 'id = ?', [$id]);
    }

    

    public static function recalcAllCounts(): void
    {
        Database::query(
            "UPDATE tags t LEFT JOIN (
                SELECT vt.tag_id, COUNT(*) as cnt
                FROM video_tags vt
                JOIN videos v ON vt.video_id = v.id AND v.status = 'published'
                GROUP BY vt.tag_id
            ) agg ON t.id = agg.tag_id
            SET t.video_count = COALESCE(agg.cnt, 0)"
        );
    }

    

    public static function translations(int $tagId): array
    {
        $rows = Database::fetchAll(
            'SELECT lang_code, name, slug FROM tag_translations WHERE tag_id = ? ORDER BY lang_code',
            [$tagId]
        );
        $result = [];
        foreach ($rows as $row) {
            $result[$row['lang_code']] = $row;
        }
        return $result;
    }

    

    public static function localize(?array $tag): ?array
    {
        if ($tag === null || Translator::locale() === Translator::getDefaultCode()) {
            return $tag;
        }

        $row = Database::fetchOne(
            'SELECT name, slug FROM tag_translations WHERE tag_id = ? AND lang_code = ?',
            [(int) $tag['id'], Translator::locale()]
        );

        if ($row) {
            if ($row['name'] !== null && $row['name'] !== '') {
                $tag['name'] = $row['name'];
            }
            if ($row['slug'] !== null && $row['slug'] !== '') {
                $tag['slug'] = $row['slug'];
            }
        }

        return $tag;
    }

    

    public static function localizeResults(array $tags): array
    {
        if (empty($tags) || Translator::locale() === Translator::getDefaultCode()) {
            return $tags;
        }

        $ids = array_column($tags, 'id');
        if (empty($ids)) {
            return $tags;
        }

        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $params = array_merge([Translator::locale()], $ids);

        $rows = Database::fetchAll(
            "SELECT tag_id, name, slug FROM tag_translations WHERE lang_code = ? AND tag_id IN ({$placeholders})",
            $params
        );

        $map = [];
        foreach ($rows as $row) {
            $map[(int) $row['tag_id']] = $row;
        }

        foreach ($tags as &$tag) {
            $tid = (int) $tag['id'];
            if (isset($map[$tid])) {
                $tr = $map[$tid];
                if ($tr['name'] !== null && $tr['name'] !== '') {
                    $tag['name'] = $tr['name'];
                }
                if ($tr['slug'] !== null && $tr['slug'] !== '') {
                    $tag['slug'] = $tr['slug'];
                }
            }
        }
        unset($tag);

        return $tags;
    }
}
