<?php

declare(strict_types=1);

trait MyronContentPageCategoriesTrait
{
    /**
     * @return list<array<string, mixed>>
     */
    public function listPageCategories(bool $activeOnly = true): array
    {
        $sql = 'SELECT id, title, slug, description, is_active, created_at, updated_at FROM content_categories';
        if ($activeOnly) {
            $sql .= ' WHERE is_active = 1';
        }
        $sql .= ' ORDER BY title ASC';

        $statement = $this->pdo->query($sql);
        $categories = [];
        foreach ($statement->fetchAll() as $row) {
            if (!is_array($row)) {
                continue;
            }

            $categories[] = [
                'id' => (string) ($row['id'] ?? ''),
                'title' => (string) ($row['title'] ?? ''),
                'slug' => (string) ($row['slug'] ?? ''),
                'description' => (string) ($row['description'] ?? ''),
                'is_active' => (int) ($row['is_active'] ?? 0) === 1,
                'created_at' => (string) ($row['created_at'] ?? ''),
                'updated_at' => (string) ($row['updated_at'] ?? ''),
            ];
        }

        return $categories;
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listPageCategoriesWithUsage(bool $activeOnly = false): array
    {
        $sql = 'SELECT
                    content_categories.id,
                    content_categories.title,
                    content_categories.slug,
                    content_categories.description,
                    content_categories.is_active,
                    content_categories.created_at,
                    content_categories.updated_at,
                    COUNT(page_category_assignments.page_id) AS page_count
                FROM content_categories
                LEFT JOIN page_category_assignments
                  ON page_category_assignments.category_id = content_categories.id';
        if ($activeOnly) {
            $sql .= ' WHERE content_categories.is_active = 1';
        }
        $sql .= ' GROUP BY
                    content_categories.id,
                    content_categories.title,
                    content_categories.slug,
                    content_categories.description,
                    content_categories.is_active,
                    content_categories.created_at,
                    content_categories.updated_at
                  ORDER BY content_categories.title ASC';

        $statement = $this->pdo->query($sql);
        $categories = [];
        foreach ($statement->fetchAll() as $row) {
            if (!is_array($row)) {
                continue;
            }

            $categories[] = $this->hydratePageCategoryRow($row);
        }

        return $categories;
    }

    public function getPageCategory(string $categoryId): ?array
    {
        $statement = $this->pdo->prepare(
            'SELECT
                content_categories.id,
                content_categories.title,
                content_categories.slug,
                content_categories.description,
                content_categories.is_active,
                content_categories.created_at,
                content_categories.updated_at,
                COUNT(page_category_assignments.page_id) AS page_count
             FROM content_categories
             LEFT JOIN page_category_assignments
               ON page_category_assignments.category_id = content_categories.id
             WHERE content_categories.id = :id
             GROUP BY
                content_categories.id,
                content_categories.title,
                content_categories.slug,
                content_categories.description,
                content_categories.is_active,
                content_categories.created_at,
                content_categories.updated_at
             LIMIT 1'
        );
        $statement->execute([':id' => trim($categoryId)]);
        $row = $statement->fetch();

        return is_array($row) ? $this->hydratePageCategoryRow($row) : null;
    }

    public function createPageCategory(string $title, string $slug = '', string $description = ''): string
    {
        $category = $this->normalizePageCategoryInput($title, $slug, $description);
        $this->assertUniquePageCategorySlug($category['slug']);
        $now = myronNow();
        $categoryId = myronGenerateId('cat');

        $statement = $this->pdo->prepare(
            'INSERT INTO content_categories (
                id, title, slug, description, is_active, created_at, updated_at
             ) VALUES (
                :id, :title, :slug, :description, 1, :created_at, :updated_at
             )'
        );
        $statement->execute([
            ':id' => $categoryId,
            ':title' => $category['title'],
            ':slug' => $category['slug'],
            ':description' => $category['description'],
            ':created_at' => $now,
            ':updated_at' => $now,
        ]);

        myronInvalidatePublicPageCache('page category created');

        return $categoryId;
    }

    public function updatePageCategory(
        string $categoryId,
        string $title,
        string $slug = '',
        string $description = '',
        ?bool $isActive = null
    ): void {
        $category = $this->requirePageCategory($categoryId);
        $normalized = $this->normalizePageCategoryInput($title, $slug, $description);
        $this->assertUniquePageCategorySlug($normalized['slug'], (string) $category['id']);

        $statement = $this->pdo->prepare(
            'UPDATE content_categories
             SET title = :title,
                 slug = :slug,
                 description = :description,
                 is_active = :is_active,
                 updated_at = :updated_at
             WHERE id = :id'
        );
        $statement->execute([
            ':title' => $normalized['title'],
            ':slug' => $normalized['slug'],
            ':description' => $normalized['description'],
            ':is_active' => $isActive === null ? ((bool) ($category['is_active'] ?? false) ? 1 : 0) : ($isActive ? 1 : 0),
            ':updated_at' => myronNow(),
            ':id' => (string) $category['id'],
        ]);
        myronInvalidatePublicPageCache('page category updated');
    }

    public function setPageCategoryActive(string $categoryId, bool $isActive): void
    {
        $category = $this->requirePageCategory($categoryId);
        $statement = $this->pdo->prepare(
            'UPDATE content_categories
             SET is_active = :is_active,
                 updated_at = :updated_at
             WHERE id = :id'
        );
        $statement->execute([
            ':is_active' => $isActive ? 1 : 0,
            ':updated_at' => myronNow(),
            ':id' => (string) $category['id'],
        ]);
        myronInvalidatePublicPageCache('page category visibility changed');
    }

    /**
     * @return list<string>
     */
    public function listCategoryIdsForPage(string $pageId): array
    {
        $statement = $this->pdo->prepare(
            'SELECT category_id
             FROM page_category_assignments
             WHERE page_id = :page_id
             ORDER BY category_id ASC'
        );
        $statement->execute([':page_id' => trim($pageId)]);

        return array_values(array_map('strval', $statement->fetchAll(PDO::FETCH_COLUMN)));
    }

    /**
     * @return list<array{id:string,title:string,slug:string,description:string,is_active:bool,created_at:string,updated_at:string}>
     */
    public function listCategoriesForPage(string $pageId, bool $activeOnly = true): array
    {
        $sql = 'SELECT
                    content_categories.id,
                    content_categories.title,
                    content_categories.slug,
                    content_categories.description,
                    content_categories.is_active,
                    content_categories.created_at,
                    content_categories.updated_at
                FROM page_category_assignments
                INNER JOIN content_categories ON content_categories.id = page_category_assignments.category_id
                WHERE page_category_assignments.page_id = :page_id';
        if ($activeOnly) {
            $sql .= ' AND content_categories.is_active = 1';
        }
        $sql .= ' ORDER BY content_categories.title ASC';

        $statement = $this->pdo->prepare($sql);
        $statement->execute([':page_id' => trim($pageId)]);

        $categories = [];
        foreach ($statement->fetchAll() as $row) {
            if (!is_array($row)) {
                continue;
            }

            $categories[] = [
                'id' => (string) ($row['id'] ?? ''),
                'title' => (string) ($row['title'] ?? ''),
                'slug' => (string) ($row['slug'] ?? ''),
                'description' => (string) ($row['description'] ?? ''),
                'is_active' => (int) ($row['is_active'] ?? 0) === 1,
                'created_at' => (string) ($row['created_at'] ?? ''),
                'updated_at' => (string) ($row['updated_at'] ?? ''),
            ];
        }

        return $categories;
    }

    /**
     * @param list<string> $categoryIds
     */
    public function replacePageCategoryAssignments(string $pageId, array $categoryIds): void
    {
        $page = $this->requirePage($pageId);
        $normalizedPageId = (string) ($page['id'] ?? $pageId);
        $ids = array_values(array_unique(array_filter(
            array_map(static fn (string $id): string => trim($id), $categoryIds),
            static fn (string $id): bool => $id !== ''
        )));

        if ($ids !== []) {
            $placeholders = implode(', ', array_fill(0, count($ids), '?'));
            $statement = $this->pdo->prepare(
                'SELECT id FROM content_categories WHERE is_active = 1 AND id IN (' . $placeholders . ')'
            );
            $statement->execute($ids);
            $validIds = array_fill_keys(array_map('strval', $statement->fetchAll(PDO::FETCH_COLUMN)), true);

            $invalidIds = array_values(array_filter($ids, static fn (string $id): bool => !isset($validIds[$id])));
            if ($invalidIds !== []) {
                throw new InvalidArgumentException('Selected blog category is unavailable.');
            }
        }

        $this->pdo->beginTransaction();

        try {
            $delete = $this->pdo->prepare('DELETE FROM page_category_assignments WHERE page_id = :page_id');
            $delete->execute([':page_id' => $normalizedPageId]);

            if ($ids !== []) {
                $insert = $this->pdo->prepare(
                    'INSERT INTO page_category_assignments (
                        id, page_id, category_id, created_at
                     ) VALUES (
                        :id, :page_id, :category_id, :created_at
                     )'
                );
                $now = myronNow();
                foreach ($ids as $categoryId) {
                    $insert->execute([
                        ':id' => myronGenerateId('pcat'),
                        ':page_id' => $normalizedPageId,
                        ':category_id' => $categoryId,
                        ':created_at' => $now,
                    ]);
                }
            }

            $this->pdo->commit();
        } catch (Throwable $exception) {
            $this->pdo->rollBack();
            throw $exception;
        }
        myronInvalidatePublicPageCache('page category assignments changed');
    }

    /**
     * @param array<string, mixed> $row
     * @return array<string, mixed>
     */
    private function hydratePageCategoryRow(array $row): array
    {
        return [
            'id' => (string) ($row['id'] ?? ''),
            'title' => (string) ($row['title'] ?? ''),
            'slug' => (string) ($row['slug'] ?? ''),
            'description' => (string) ($row['description'] ?? ''),
            'is_active' => (int) ($row['is_active'] ?? 0) === 1,
            'page_count' => (int) ($row['page_count'] ?? 0),
            'created_at' => (string) ($row['created_at'] ?? ''),
            'updated_at' => (string) ($row['updated_at'] ?? ''),
        ];
    }

    private function requirePageCategory(string $categoryId): array
    {
        $category = $this->getPageCategory($categoryId);
        if (!is_array($category)) {
            throw new InvalidArgumentException('Selected category does not exist.');
        }

        return $category;
    }

    /**
     * @return array{title:string,slug:string,description:string}
     */
    private function normalizePageCategoryInput(string $title, string $slug, string $description): array
    {
        $normalizedTitle = trim($title);
        if ($normalizedTitle === '') {
            throw new InvalidArgumentException('Category title is required.');
        }

        $normalizedSlug = myronNormalizeSlug(trim($slug) !== '' ? $slug : $normalizedTitle);
        if ($normalizedSlug === '') {
            throw new InvalidArgumentException('Category slug is required.');
        }

        return [
            'title' => $normalizedTitle,
            'slug' => $normalizedSlug,
            'description' => trim($description),
        ];
    }

    private function assertUniquePageCategorySlug(string $slug, ?string $excludeCategoryId = null): void
    {
        $sql = 'SELECT id FROM content_categories WHERE slug = :slug';
        $params = [':slug' => $slug];
        if ($excludeCategoryId !== null && $excludeCategoryId !== '') {
            $sql .= ' AND id <> :id';
            $params[':id'] = $excludeCategoryId;
        }

        $statement = $this->pdo->prepare($sql . ' LIMIT 1');
        $statement->execute($params);
        if (is_array($statement->fetch())) {
            throw new InvalidArgumentException('Another category already uses that slug.');
        }
    }
}
