<?php

declare(strict_types=1);

final class MyronPublicComponentDataResolver
{
    /** @var list<array<string, mixed>>|null */
    private ?array $publishedPages = null;

    /** @var array<string, list<array<string, mixed>>> */
    private array $pagesByClass = [];

    /** @var list<array<string, mixed>>|null */
    private ?array $publishedPostCategories = null;

    /** @var array<string, list<array{id:string,title:string,slug:string,description:string}>>|null */
    private ?array $activeCategoriesByPageId = null;

    public function __construct(
        private readonly PDO $pdo,
    ) {
    }

    public function resetCache(): void
    {
        $this->publishedPages = null;
        $this->pagesByClass = [];
        $this->publishedPostCategories = null;
        $this->activeCategoriesByPageId = null;
    }

    /**
     * @param list<string> $templateClasses
     * @return list<array<string, mixed>>
     */
    public function listPublishedPagesByTemplateClasses(array $templateClasses, ?string $sourceBundleId = null, int $limit = 6): array
    {
        $classes = array_values(array_unique(array_filter(
            array_map(static fn (string $class): string => trim($class), $templateClasses),
            static fn (string $class): bool => $class !== ''
        )));

        if ($classes === []) {
            return [];
        }

        $bundle = trim((string) $sourceBundleId);
        $cacheKey = implode('|', $classes) . '::' . $bundle;
        if (!array_key_exists($cacheKey, $this->pagesByClass)) {
            $this->pagesByClass[$cacheKey] = array_values(array_filter(
                $this->listPublishedPagesForComponents(),
                static function (array $page) use ($classes, $bundle): bool {
                    $matchesClass = in_array((string) ($page['template_class'] ?? ''), $classes, true);
                    if (!$matchesClass) {
                        return false;
                    }

                    if ($bundle === '') {
                        return true;
                    }

                    return (string) ($page['source_bundle_id'] ?? '') === $bundle;
                }
            ));
        }

        return $this->limitRows($this->pagesByClass[$cacheKey], $limit);
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listPublishedPosts(?string $sourceBundleId = null, int $limit = 6): array
    {
        return $this->listPublishedPagesByTemplateClasses(['post-single'], $sourceBundleId, $limit);
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listPublishedPostsBySourcePath(string $sourcePath, ?string $sourceBundleId = null, int $limit = 6): array
    {
        try {
            $path = myronNormalizePath($sourcePath);
        } catch (InvalidArgumentException) {
            return [];
        }
        if ($path === '/') {
            return $this->listPublishedPosts($sourceBundleId, $limit);
        }

        $prefix = rtrim($path, '/') . '/';
        $posts = array_values(array_filter(
            $this->listPublishedPosts($sourceBundleId, 0),
            static function (array $page) use ($path, $prefix): bool {
                $pagePath = myronNormalizePath((string) ($page['path'] ?? $page['href'] ?? ''));
                return $pagePath !== $path && str_starts_with($pagePath, $prefix);
            }
        ));

        return $this->limitRows($posts, $limit);
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listPublishedProjects(?string $sourceBundleId = null, int $limit = 6): array
    {
        return $this->listPublishedPagesByTemplateClasses(['project-single'], $sourceBundleId, $limit);
    }

    /**
     * @param list<array<string, mixed>> $rows
     * @return list<array<string, mixed>>
     */
    private function limitRows(array $rows, int $limit): array
    {
        if ($limit <= 0) {
            return array_values($rows);
        }

        return array_slice($rows, 0, $limit);
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function listActiveCategoriesWithPublishedPostUsage(?string $sourceBundleId = null): array
    {
        if ($this->publishedPostCategories === null) {
            $this->publishedPostCategories = $this->loadActiveCategoriesWithPublishedPostUsage();
        }

        $bundle = trim((string) $sourceBundleId);
        if ($bundle === '') {
            return $this->publishedPostCategories;
        }

        return array_values(array_filter(
            $this->publishedPostCategories,
            static fn (array $category): bool => ($category['source_bundle_ids'][$bundle] ?? false) === true
        ));
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function listPublishedPagesForComponents(): array
    {
        if ($this->publishedPages !== null) {
            return $this->publishedPages;
        }

        $disabledFilter = $this->tableHasColumn('pages', 'disabled_by_bundle_removal_at')
            ? ' AND pages.disabled_by_bundle_removal_at IS NULL'
            : '';
        $statement = $this->pdo->query(
            'SELECT
                pages.id,
                pages.path,
                pages.slug,
                pages.title AS page_title,
                pages.source_template_id AS page_source_template_id,
                pages.source_bundle_id AS page_source_bundle_id,
                page_templates.id AS template_id,
                page_templates.template_key,
                page_templates.name AS template_name,
                page_templates.source_template_id AS template_source_template_id,
                page_templates.source_bundle_id AS template_source_bundle_id,
                page_versions.title AS version_title,
                page_versions.seo_title,
                page_versions.meta_description,
                page_versions.canonical_override,
                page_versions.robots_override,
                page_versions.og_title,
                page_versions.og_description,
                page_versions.og_image,
                page_versions.twitter_title,
                page_versions.twitter_description,
                page_versions.twitter_image,
                page_versions.jsonld_mode,
                page_versions.jsonld_type,
                page_versions.jsonld_custom,
                page_versions.content_json,
                page_versions.published_at
             FROM pages
             INNER JOIN page_versions ON page_versions.id = pages.current_published_version_id
             INNER JOIN page_templates ON page_templates.id = pages.template_id
             WHERE pages.publication_state = \'published\''
            . $disabledFilter .
            ' ORDER BY page_versions.published_at DESC, pages.updated_at DESC, pages.path ASC'
        );

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

            $roleSourceId = $this->roleSourceId($row);
            $content = $this->decodeContent((string) ($row['content_json'] ?? '{}'));
            $title = trim((string) (($row['version_title'] ?? '') ?: ($row['page_title'] ?? '') ?: ($row['template_name'] ?? '')));
            $path = myronNormalizePath((string) ($row['path'] ?? '/'));
            $pages[] = [
                'id' => (string) ($row['id'] ?? ''),
                'title' => $title !== '' ? $title : 'Untitled',
                'path' => $path,
                'href' => $path,
                'slug' => (string) ($row['slug'] ?? ''),
                'summary' => $this->summaryForPage($row, $content),
                'source_template_id' => $roleSourceId,
                'source_bundle_id' => (string) (($row['page_source_bundle_id'] ?? '') ?: ($row['template_source_bundle_id'] ?? '')),
                'template_class' => MyronTemplateDefinitionRepository::templateClassForTemplateId($roleSourceId),
                'published_at' => (string) ($row['published_at'] ?? ''),
                'content' => $content,
                'categories' => $categoriesByPageId[(string) ($row['id'] ?? '')] ?? [],
                'author' => $this->postAuthorForPage($content),
                'featured_media' => $this->featuredMediaForPage($content),
                'approved_comment_count' => MyronTemplateDefinitionRepository::templateClassForTemplateId($roleSourceId) === 'post-single'
                    ? myronBlogCommentService()->countApprovedForPage((string) ($row['id'] ?? ''))
                    : 0,
            ];
        }

        $this->publishedPages = $pages;
        return $this->publishedPages;
    }

    /**
     * @return array<string, list<array{id:string,title:string,slug:string,description:string}>>
     */
    private function loadActiveCategoriesByPublishedPageId(): array
    {
        if ($this->activeCategoriesByPageId !== null) {
            return $this->activeCategoriesByPageId;
        }

        $disabledFilter = $this->tableHasColumn('pages', 'disabled_by_bundle_removal_at')
            ? ' AND pages.disabled_by_bundle_removal_at IS NULL'
            : '';
        $statement = $this->pdo->query(
            'SELECT
                page_category_assignments.page_id,
                content_categories.id,
                content_categories.title,
                content_categories.slug,
                content_categories.description
             FROM page_category_assignments
             INNER JOIN content_categories ON content_categories.id = page_category_assignments.category_id
             INNER JOIN pages ON pages.id = page_category_assignments.page_id
             WHERE content_categories.is_active = 1
               AND pages.current_published_version_id IS NOT NULL
             ORDER BY content_categories.title ASC'
        );

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

            $pageId = trim((string) ($row['page_id'] ?? ''));
            $categoryId = trim((string) ($row['id'] ?? ''));
            $slug = trim((string) ($row['slug'] ?? ''));
            $title = trim((string) ($row['title'] ?? ''));
            if ($pageId === '' || $categoryId === '' || $slug === '' || $title === '') {
                continue;
            }

            $byPageId[$pageId][] = [
                'id' => $categoryId,
                'title' => $title,
                'slug' => $slug,
                'description' => (string) ($row['description'] ?? ''),
            ];
        }

        $this->activeCategoriesByPageId = $byPageId;
        return $this->activeCategoriesByPageId;
    }

    /**
     * @param array<string, mixed> $content
     * @return array<string, string>
     */
    private function postAuthorForPage(array $content): array
    {
        $meta = is_array($content['_postMeta'] ?? null) ? (array) $content['_postMeta'] : [];
        $author = is_array($meta['author'] ?? null) ? (array) $meta['author'] : [];

        return [
            'authorName' => trim((string) ($author['authorName'] ?? '')),
            'authorUserId' => trim((string) ($author['authorUserId'] ?? '')),
            'avatarFileId' => trim((string) ($author['avatarFileId'] ?? '')),
            'bio' => trim((string) ($author['bio'] ?? '')),
        ];
    }

    /**
     * @param array<string, mixed> $content
     * @return array{mediaFileId:string,mediaAlt:string}
     */
    private function featuredMediaForPage(array $content): array
    {
        foreach (is_array($content['sections'] ?? null) ? $content['sections'] : [] as $section) {
            if (!is_array($section)) {
                continue;
            }
            $fileId = trim((string) ($section['mediaFileId'] ?? ''));
            if ($fileId === '') {
                continue;
            }

            return [
                'mediaFileId' => $fileId,
                'mediaAlt' => trim((string) ($section['mediaAlt'] ?? '')),
            ];
        }

        return [
            'mediaFileId' => trim((string) ($content['mediaFileId'] ?? '')),
            'mediaAlt' => trim((string) ($content['mediaAlt'] ?? '')),
        ];
    }

    /**
     * @param array<string, mixed> $row
     */
    private function roleSourceId(array $row): string
    {
        foreach (['page_source_template_id', 'template_source_template_id', 'template_key', 'template_id'] as $field) {
            $candidate = trim((string) ($row[$field] ?? ''));
            if ($candidate !== '') {
                return $candidate;
            }
        }

        return '';
    }

    /**
     * @return array<string, mixed>
     */
    private function decodeContent(string $json): array
    {
        try {
            $decoded = json_decode($json !== '' ? $json : '{}', true, 512, JSON_THROW_ON_ERROR);
        } catch (Throwable) {
            return [];
        }

        return is_array($decoded) ? $decoded : [];
    }

    /**
     * @param array<string, mixed> $row
     * @param array<string, mixed> $content
     */
    private function summaryForPage(array $row, array $content): string
    {
        foreach ([
            $row['meta_description'] ?? '',
            $content['intro'] ?? '',
            $content['summary'] ?? '',
            $content['excerpt'] ?? '',
            $content['body'] ?? '',
        ] as $candidate) {
            $summary = trim(strip_tags((string) $candidate));
            if ($summary !== '') {
                return strlen($summary) > 180 ? rtrim(substr($summary, 0, 177)) . '...' : $summary;
            }
        }

        return '';
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function loadActiveCategoriesWithPublishedPostUsage(): array
    {
        $disabledFilter = $this->tableHasColumn('pages', 'disabled_by_bundle_removal_at')
            ? ' AND pages.disabled_by_bundle_removal_at IS NULL'
            : '';
        $statement = $this->pdo->query(
            'SELECT
                content_categories.id,
                content_categories.title,
                content_categories.slug,
                content_categories.description,
                pages.id AS page_id,
                pages.source_bundle_id AS page_source_bundle_id,
                pages.source_template_id AS page_source_template_id,
                page_templates.source_bundle_id AS template_source_bundle_id,
                page_templates.source_template_id AS template_source_template_id,
                page_templates.template_key,
                page_templates.id AS template_id
             FROM content_categories
             INNER JOIN page_category_assignments ON page_category_assignments.category_id = content_categories.id
             INNER JOIN pages ON pages.id = page_category_assignments.page_id
             INNER JOIN page_templates ON page_templates.id = pages.template_id
             INNER JOIN page_versions ON page_versions.id = pages.current_published_version_id
             WHERE content_categories.is_active = 1
               AND pages.publication_state = \'published\''
            . $disabledFilter .
            ' ORDER BY content_categories.title ASC, pages.path ASC'
        );

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

            $roleSourceId = $this->roleSourceId([
                'page_source_template_id' => $row['page_source_template_id'] ?? '',
                'template_source_template_id' => $row['template_source_template_id'] ?? '',
                'template_key' => $row['template_key'] ?? '',
                'template_id' => $row['template_id'] ?? '',
            ]);
            if (MyronTemplateDefinitionRepository::templateClassForTemplateId($roleSourceId) !== 'post-single') {
                continue;
            }

            $id = (string) ($row['id'] ?? '');
            if ($id === '') {
                continue;
            }

            if (!isset($categories[$id])) {
                $categories[$id] = [
                    'id' => $id,
                    'title' => (string) ($row['title'] ?? ''),
                    'slug' => (string) ($row['slug'] ?? ''),
                    'description' => (string) ($row['description'] ?? ''),
                    'page_count' => 0,
                    'source_bundle_ids' => [],
                ];
            }

            $categories[$id]['page_count']++;
            $bundle = trim((string) (($row['page_source_bundle_id'] ?? '') ?: ($row['template_source_bundle_id'] ?? '')));
            if ($bundle !== '') {
                $categories[$id]['source_bundle_ids'][$bundle] = true;
            }
        }

        return array_values($categories);
    }

    private function tableHasColumn(string $table, string $column): bool
    {
        $driver = (string) $this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        if ($driver === 'sqlite') {
            $statement = $this->pdo->query('PRAGMA table_info(' . $table . ')');
            foreach (($statement ? $statement->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                if ((string) ($row['name'] ?? '') === $column) {
                    return true;
                }
            }
            return false;
        }

        $statement = $this->pdo->prepare(
            'SELECT COUNT(*)
             FROM information_schema.columns
             WHERE table_schema = DATABASE()
               AND table_name = :table
               AND column_name = :column'
        );
        $statement->execute([':table' => $table, ':column' => $column]);
        return (int) $statement->fetchColumn() > 0;
    }
}
