<?php

declare(strict_types=1);

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

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

         
        if ($locale !== $defaultLang) {
            $q = 'SELECT ct.category_id FROM album_category_translations ct
                  JOIN album_categories c ON ct.category_id = c.id
                  WHERE ct.slug = ? AND ct.lang_code = ?';
            $p = [$slug, $locale];
            if ($vt !== '') { $q .= ' AND c.video_type = ?'; $p[] = $vt; }
            $trans = Database::fetchOne($q, $p);
            if ($trans) {
                $cat = self::find((int) $trans['category_id']);
                return $cat ? self::localize($cat) : null;
            }
        }

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

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

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

    public static function all(int $limit = 0, int $offset = 0, string $sort = 'alphabetical', string $videoType = '', bool $onlyPopulated = false): array
    {
        $orderBy = match ($sort) {
            'popularity' => 'c.total_views DESC, c.name ASC',
            'alphabetical' => 'c.name ASC',
            default => 'c.sort_order ASC, c.name ASC',
        };

        $conds = [];
        $params = [];
        if ($videoType !== '') { $conds[] = 'c.video_type = ?'; $params[] = $videoType; }
         
        if ($onlyPopulated) { $conds[] = 'c.album_count > 0'; }
        $where = !empty($conds) ? ' WHERE ' . implode(' AND ', $conds) : '';

        $limitClause = $limit > 0 ? ' LIMIT ? OFFSET ?' : '';
        if ($limit > 0) {
            $params[] = $limit;
            $params[] = $offset;
        }

        $results = Database::fetchAll(
            "SELECT c.* FROM album_categories c{$where} ORDER BY {$orderBy}" . $limitClause,
            $params
        );
        return self::localizeResults($results);
    }

    public static function count(string $videoType = '', bool $onlyPopulated = false): int
    {
        $conds = []; $params = [];
        if ($videoType !== '') { $conds[] = 'video_type = ?'; $params[] = $videoType; }
        if ($onlyPopulated) { $conds[] = 'album_count > 0'; }
        if (!empty($conds)) {
            return Database::count('album_categories', implode(' AND ', $conds), $params);
        }
        return Database::count('album_categories');
    }

    public static function tree(): array
    {
        $all = self::all();
        $tree = [];
        $children = [];

        foreach ($all as $cat) {
            if ($cat['parent_id'] === null) {
                $tree[$cat['id']] = $cat;
                $tree[$cat['id']]['children'] = [];
            } else {
                $children[] = $cat;
            }
        }

        foreach ($children as $child) {
            $pid = (int) $child['parent_id'];
            if (isset($tree[$pid])) {
                $tree[$pid]['children'][] = $child;
            }
        }

        return array_values($tree);
    }

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

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

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

    

    public static function updateCounts(int $id): void
    {
        $row = Database::fetchOne(
            "SELECT COUNT(*) as cnt, COALESCE(SUM(a.views), 0) as tv
             FROM album_category_map m
             JOIN albums a ON m.album_id = a.id
             WHERE m.category_id = ? AND a.status = 'published'",
            [$id]
        );
        Database::update('album_categories', [
            'album_count' => (int) ($row['cnt'] ?? 0),
            'total_views' => (int) ($row['tv'] ?? 0),
        ], 'id = ?', [$id]);
    }

    

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

    

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

        $row = Database::fetchOne(
            'SELECT name, slug, description FROM album_category_translations WHERE category_id = ? AND lang_code = ?',
            [(int) $category['id'], Translator::locale()]
        );

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

        return $category;
    }

    

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

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

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

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

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

        foreach ($categories as &$cat) {
            $cid = (int) $cat['id'];
            if (isset($map[$cid])) {
                $tr = $map[$cid];
                if ($tr['name'] !== null && $tr['name'] !== '') {
                    $cat['name'] = $tr['name'];
                }
                if ($tr['slug'] !== null && $tr['slug'] !== '') {
                    $cat['slug'] = $tr['slug'];
                }
                if (isset($tr['description']) && $tr['description'] !== null && $tr['description'] !== '') {
                    $cat['description'] = $tr['description'];
                }
            }
        }
        unset($cat);

        return $categories;
    }
}
