<?php
declare(strict_types=1);

namespace App\Models;

use PDO;

class Category extends BaseModel
{
    protected string $table = 'categories';

    public function findBySlug(string $slug): ?array
    {
        $statement = $this->db()->prepare(
            'SELECT c.*,
                parent.name AS parent_name,
                parent.slug AS parent_slug,
                (
                    SELECT COUNT(*)
                    FROM mods m
                    WHERE m.category_id = c.id AND m.status = "published"
                ) AS mod_count
             FROM categories c
             LEFT JOIN categories parent ON parent.id = c.parent_id
             WHERE c.slug = :slug AND c.type = "mod"
             LIMIT 1'
        );
        $statement->execute(['slug' => $slug]);
        $category = $statement->fetch();

        return $category ? $this->normalize($category) : null;
    }

    public function modRoots(int $limit = 12): array
    {
        $statement = $this->db()->prepare(
            'SELECT c.*,
                (
                    SELECT COUNT(*)
                    FROM mods m
                    LEFT JOIN categories child_count ON child_count.id = m.category_id
                    WHERE m.status = "published" AND (m.category_id = c.id OR child_count.parent_id = c.id)
                ) AS mod_count
             FROM categories c
             WHERE c.type = "mod" AND c.parent_id IS NULL AND c.is_active = 1
             ORDER BY c.sort_order ASC, c.name ASC
             LIMIT :limit'
        );
        $statement->bindValue('limit', $limit, PDO::PARAM_INT);
        $statement->execute();

        return array_map(fn (array $category): array => $this->normalize($category), $statement->fetchAll());
    }

    public function children(int $parentId): array
    {
        $statement = $this->db()->prepare(
            'SELECT c.*,
                (
                    SELECT COUNT(*)
                    FROM mods m
                    WHERE m.category_id = c.id AND m.status = "published"
                ) AS mod_count
             FROM categories c
             WHERE c.type = "mod" AND c.parent_id = :parent_id AND c.is_active = 1
             ORDER BY c.sort_order ASC, c.name ASC'
        );
        $statement->execute(['parent_id' => $parentId]);

        return array_map(fn (array $category): array => $this->normalize($category), $statement->fetchAll());
    }

    public function treeIds(int $rootId): array
    {
        $statement = $this->db()->query('SELECT id, parent_id FROM categories WHERE type = "mod"');
        $rows = $statement->fetchAll();
        $ids = [$rootId];
        $changed = true;

        while ($changed) {
            $changed = false;
            foreach ($rows as $row) {
                $id = (int) $row['id'];
                $parentId = $row['parent_id'] === null ? null : (int) $row['parent_id'];

                if ($parentId !== null && in_array($parentId, $ids, true) && !in_array($id, $ids, true)) {
                    $ids[] = $id;
                    $changed = true;
                }
            }
        }

        return $ids;
    }

    public function findBySlugs(array $slugs): array
    {
        if ($slugs === []) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($slugs), '?'));
        $statement = $this->db()->prepare(
            "SELECT c.*,
                (
                    SELECT COUNT(*)
                    FROM mods m
                    LEFT JOIN categories child_count ON child_count.id = m.category_id
                    WHERE m.status = \"published\" AND (m.category_id = c.id OR child_count.parent_id = c.id)
                ) AS mod_count
             FROM categories c
             WHERE c.slug IN ({$placeholders}) AND c.type = \"mod\"
             ORDER BY c.sort_order ASC, c.name ASC"
        );
        $statement->execute(array_values($slugs));

        return array_map(fn (array $category): array => $this->normalize($category), $statement->fetchAll());
    }

    public function adminGroups(): array
    {
        $categories = $this->db()->query(
            'SELECT c.*,
                    parent.name AS parent_name,
                    (SELECT COUNT(*) FROM mods m WHERE m.category_id = c.id) AS mod_count,
                    0 AS track_count
             FROM categories c
             LEFT JOIN categories parent ON parent.id = c.parent_id
             ORDER BY c.type ASC, c.sort_order ASC, c.name ASC'
        )->fetchAll();

        $genres = [];
        try {
            $genres = $this->db()->query(
                'SELECT mg.*,
                        NULL AS parent_id,
                        NULL AS parent_name,
                        "music_genre" AS type,
                        0 AS mod_count,
                        COUNT(DISTINCT mtg.track_id) AS track_count,
                        1 AS is_active
                 FROM music_genres mg
                 LEFT JOIN music_track_genres mtg ON mtg.genre_id = mg.id
                 GROUP BY mg.id, mg.name, mg.slug, mg.description, mg.icon, mg.banner, mg.sort_order, mg.created_at, mg.updated_at
                 ORDER BY mg.sort_order ASC, mg.name ASC'
            )->fetchAll();
        } catch (\Throwable) {
            $genres = [];
        }

        return [
            'mod' => array_values(array_filter($categories, static fn (array $row): bool => ($row['type'] ?? '') === 'mod')),
            'music' => array_values(array_filter($categories, static fn (array $row): bool => ($row['type'] ?? '') === 'music')),
            'music_genres' => $genres,
        ];
    }

    public function createFromAdmin(array $input): int
    {
        $group = (string) ($input['group'] ?? $input['type'] ?? 'mod');
        $data = $this->normalizeAdminInput($input, $group);

        if ($group === 'music_genre') {
            $statement = $this->db()->prepare(
                'INSERT INTO music_genres (name, slug, description, icon, banner, sort_order, created_at, updated_at)
                 VALUES (:name, :slug, :description, :icon, :banner, :sort_order, NOW(), NOW())'
            );
            $statement->execute([
                'name' => $data['name'],
                'slug' => $data['slug'],
                'description' => $data['description'],
                'icon' => $data['icon'],
                'banner' => $data['banner'],
                'sort_order' => $data['sort_order'],
            ]);

            return (int) $this->db()->lastInsertId();
        }

        $statement = $this->db()->prepare(
            'INSERT INTO categories
                (parent_id, type, name, slug, description, icon, banner, sort_order, is_active, created_at, updated_at)
             VALUES
                (:parent_id, :type, :name, :slug, :description, :icon, :banner, :sort_order, :is_active, NOW(), NOW())'
        );
        $statement->execute($data);

        return (int) $this->db()->lastInsertId();
    }

    public function updateFromAdmin(int $id, array $input): void
    {
        $group = (string) ($input['group'] ?? $input['type'] ?? 'mod');
        $data = $this->normalizeAdminInput($input, $group);

        if ($group === 'music_genre') {
            $statement = $this->db()->prepare(
                'UPDATE music_genres
                 SET name = :name,
                     slug = :slug,
                     description = :description,
                     icon = :icon,
                     banner = :banner,
                     sort_order = :sort_order,
                     updated_at = NOW()
                 WHERE id = :id'
            );
            $statement->execute([
                'name' => $data['name'],
                'slug' => $data['slug'],
                'description' => $data['description'],
                'icon' => $data['icon'],
                'banner' => $data['banner'],
                'sort_order' => $data['sort_order'],
                'id' => $id,
            ]);

            return;
        }

        $statement = $this->db()->prepare(
            'UPDATE categories
             SET parent_id = :parent_id,
                 type = :type,
                 name = :name,
                 slug = :slug,
                 description = :description,
                 icon = :icon,
                 banner = :banner,
                 sort_order = :sort_order,
                 is_active = :is_active,
                 updated_at = NOW()
             WHERE id = :id'
        );
        $statement->execute($data + ['id' => $id]);
    }

    public function deleteFromAdmin(int $id, string $group): void
    {
        if ($group === 'music_genre') {
            $statement = $this->db()->prepare('DELETE FROM music_genres WHERE id = :id LIMIT 1');
            $statement->execute(['id' => $id]);

            return;
        }

        $statement = $this->db()->prepare('UPDATE categories SET is_active = 0, updated_at = NOW() WHERE id = :id');
        $statement->execute(['id' => $id]);
    }

    private function normalizeAdminInput(array $input, string $group): array
    {
        $name = trim((string) ($input['name'] ?? ''));
        $slug = $this->slug((string) ($input['slug'] ?? $name));
        if ($name === '' || $slug === '') {
            throw new \InvalidArgumentException('Kategori adi ve slug zorunludur.');
        }

        $type = in_array($group, ['music', 'music_genre'], true) ? 'music' : 'mod';

        return [
            'parent_id' => $type === 'mod' && (int) ($input['parent_id'] ?? 0) > 0 ? (int) $input['parent_id'] : null,
            'type' => $type,
            'name' => substr($name, 0, 120),
            'slug' => substr($slug, 0, 160),
            'description' => trim((string) ($input['description'] ?? '')) ?: null,
            'icon' => trim((string) ($input['icon'] ?? '')) ?: null,
            'banner' => trim((string) ($input['banner'] ?? '')) ?: null,
            'sort_order' => (int) ($input['sort_order'] ?? 0),
            'is_active' => in_array(strtolower((string) ($input['is_active'] ?? '1')), ['1', 'true', 'on', 'yes'], true) ? 1 : 0,
        ];
    }

    private function slug(string $value): string
    {
        $value = strtolower(trim($value));
        $value = strtr($value, ['ı' => 'i', 'ğ' => 'g', 'ü' => 'u', 'ş' => 's', 'ö' => 'o', 'ç' => 'c']);
        $value = preg_replace('/[^a-z0-9]+/', '-', $value) ?: '';

        return trim($value, '-');
    }

    private function normalize(array $category): array
    {
        $category['id'] = (int) $category['id'];
        $category['parent_id'] = $category['parent_id'] === null ? null : (int) $category['parent_id'];
        $category['sort_order'] = (int) ($category['sort_order'] ?? 0);
        $category['mod_count'] = (int) ($category['mod_count'] ?? 0);
        $category['description'] = $category['description'] ?? '';
        $category['icon'] = $category['icon'] ?? '';
        $category['banner'] = $category['banner'] ?? '';

        return $category;
    }
}
