<?php

declare(strict_types=1);

trait MyronContentPublicReadModelTrait
{
    public function getPublishedPageByRole(string $role): ?array
    {
        $role = strtolower(trim($role));
        if (!in_array($role, ['home', 'search', 'not-found'], true)) {
            throw new InvalidArgumentException('Unknown page role: ' . $role);
        }
        try {
            $statement = $this->pdo->prepare('SELECT page_id FROM site_page_roles WHERE role_key = :role_key LIMIT 1');
            $statement->execute([':role_key' => $role]);
            $pageId = trim((string) ($statement->fetchColumn() ?: ''));
        } catch (Throwable) {
            return null;
        }
        return $pageId !== '' ? $this->getPublishedPageById($pageId) : null;
    }

    public function getPublishedPageByPath(string $path): ?array
    {
        $normalizedPath = myronNormalizePath($path);
        $disabledFilter = $this->tableHasColumn('pages', 'disabled_by_bundle_removal_at')
            ? ' AND pages.disabled_by_bundle_removal_at IS NULL'
            : '';

        $statement = $this->pdo->prepare(
            'SELECT
                pages.id,
                pages.path,
                pages.slug,
                pages.title,
                pages.source_template_id,
                pages.source_bundle_id,
                pages.indexable_override,
                page_templates.name AS template_name,
                page_templates.template_key,
                page_templates.source_template_id AS template_source_template_id,
                page_templates.source_bundle_id AS template_source_bundle_id,
                page_template_versions.view_name,
                page_template_versions.schema_json,
                page_versions.version_number,
                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
             INNER JOIN page_template_versions ON page_template_versions.id = page_versions.template_version_id
             WHERE pages.path = :path
               AND pages.publication_state = \'published\'
               AND pages.current_published_version_id IS NOT NULL'
            . $disabledFilter .
            ' LIMIT 1'
        );
        $statement->execute([':path' => $normalizedPath]);
        $row = $statement->fetch();

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

    public function getPublishedPageById(string $pageId): ?array
    {
        $disabledFilter = $this->tableHasColumn('pages', 'disabled_by_bundle_removal_at')
            ? ' AND pages.disabled_by_bundle_removal_at IS NULL'
            : '';
        $statement = $this->pdo->prepare(
            'SELECT
                pages.id,
                pages.path,
                pages.slug,
                pages.title,
                pages.source_template_id,
                pages.source_bundle_id,
                pages.indexable_override,
                page_templates.name AS template_name,
                page_templates.template_key,
                page_templates.source_template_id AS template_source_template_id,
                page_templates.source_bundle_id AS template_source_bundle_id,
                page_template_versions.view_name,
                page_template_versions.schema_json,
                page_versions.version_number,
                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
             INNER JOIN page_template_versions ON page_template_versions.id = page_versions.template_version_id
             WHERE pages.id = :page_id
               AND pages.publication_state = \'published\'
               AND pages.current_published_version_id IS NOT NULL'
            . $disabledFilter .
            ' LIMIT 1'
        );
        $statement->execute([':page_id' => trim($pageId)]);
        $row = $statement->fetch();

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

    /**
     * @return list<array<string, mixed>>
     */
    public function listPublishedPages(): array
    {
        $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,
                page_versions.title
             FROM pages
             INNER JOIN page_versions ON page_versions.id = pages.current_published_version_id
             WHERE pages.publication_state = \'published\''
            . $disabledFilter .
            ' ORDER BY CASE WHEN pages.path = \'/\' THEN 0 ELSE 1 END, pages.path ASC'
        );

        $pages = $statement->fetchAll();
        foreach ($pages as &$page) {
            $page['url'] = myronBuildPublicPageUrl((string) $page['path']);
        }

        return $pages;
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function findPublishedIndexablePages(): array
    {
        $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,
                pages.parent_page_id,
                pages.source_bundle_id,
                pages.indexable_override,
                page_templates.source_bundle_id AS template_source_bundle_id,
                page_versions.title AS version_title,
                page_versions.meta_description,
                page_versions.robots_override,
                page_versions.published_at,
                COALESCE(pages.indexable_override, site_type_registry.indexable, 1) AS effective_indexable
             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
             LEFT JOIN site_type_registry ON site_type_registry.bundle_id = COALESCE(NULLIF(pages.source_bundle_id, \'\'), NULLIF(page_templates.source_bundle_id, \'\'))
             WHERE pages.publication_state = \'published\'
               AND COALESCE(pages.indexable_override, site_type_registry.indexable, 1) = 1
               AND (page_versions.robots_override IS NULL OR page_versions.robots_override NOT LIKE \'%noindex%\')'
            . $disabledFilter .
            ' ORDER BY CASE WHEN pages.path = \'/\' THEN 0 ELSE 1 END, pages.path ASC'
        );

        $rows = $statement ? $statement->fetchAll() : [];
        return is_array($rows) ? array_values(array_filter($rows, 'is_array')) : [];
    }

    public function isPageMarkupDownloadable(array $page): bool
    {
        $robots = strtolower((string) ($page['robots_override'] ?? ''));
        if (str_contains($robots, 'noindex')) {
            return false;
        }

        $override = $page['indexable_override'] ?? null;
        if ($override !== null && $override !== '') {
            return (int) $override === 1;
        }

        $bundleId = trim((string) (($page['source_bundle_id'] ?? '') ?: ($page['template_source_bundle_id'] ?? '')));
        return myronSiteTypeRegistryRepository()->getIndexableByBundleId($bundleId);
    }

    /**
     * @param array<string, mixed> $row
     * @return array<string, mixed>
     */
    private function hydratePublishedPageRow(array $row): array
    {
        $row['content'] = json_decode((string) $row['content_json'], true, 512, JSON_THROW_ON_ERROR);
        $schema = json_decode((string) $row['schema_json'], true, 512, JSON_THROW_ON_ERROR);
        if (is_array($schema)) {
            unset($schema['defaultContent']);
        }
        $row['template_schema'] = is_array($schema) ? $schema : [];
        $row['categories'] = $this->loadActiveCategoriesForPublishedPage((string) ($row['id'] ?? ''));
        return $row;
    }

    /**
     * @return list<array{id:string,title:string,slug:string,description:string}>
     */
    private function loadActiveCategoriesForPublishedPage(string $pageId): array
    {
        $statement = $this->pdo->prepare(
            'SELECT
                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
             WHERE page_category_assignments.page_id = :page_id
               AND content_categories.is_active = 1
             ORDER BY content_categories.title ASC'
        );
        $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'] ?? ''),
            ];
        }

        return $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;
    }
}
