<?php

declare(strict_types=1);

require_once __DIR__ . '/SiteSnapshotEnvelope.php';
require_once dirname(__DIR__, 3) . '/system/release/ReleaseMetadata.php';
// DesignSystemRegistry loaded by the app-local JSONM runtime.

final class MyronSiteSnapshotExportService
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    /**
     * @return array<string, mixed>
     */
    public function export(): array
    {
        $data = [
            'siteSettings' => [
                'rows' => $this->rows('site_settings', 'id ASC'),
            ],
            'designDefinition' => [
                'current' => $this->currentDesignDefinition(),
                'tokenPatches' => $this->rows('token_patches', 'token_name ASC'),
                'customCssSettings' => $this->rows('custom_css_settings', 'scope_type ASC, scope_key ASC'),
            ],
            'customSectionTemplates' => [
                'rows' => $this->rows('custom_section_templates', 'section_key ASC'),
            ],
            'pageTemplates' => [
                'templates' => $this->rows('page_templates', 'id ASC'),
                'versions' => $this->rows('page_template_versions', 'template_id ASC, version_number ASC, id ASC'),
            ],
            'pages' => $this->publishedPagesSection(),
            'navigation' => [
                'menus' => $this->rows('menus', 'menu_key ASC, id ASC'),
                'menuItems' => $this->rows('menu_items', 'menu_id ASC, sort_order ASC, id ASC'),
                'navigationDesignSettings' => $this->rows('navigation_design_settings', 'id ASC'),
                'navigationDesigns' => $this->rows('navigation_designs', 'name ASC, id ASC'),
            ],
        ];

        $envelope = [
            'snapshotVersion' => MyronSiteSnapshotEnvelope::SNAPSHOT_VERSION,
            'appVersion' => MyronReleaseMetadata::VERSION,
            'sourceUrl' => function_exists('myronAppUrl') ? myronAppUrl() : (string) (getenv('MYRON_APP_URL') ?: ''),
            'exportedAt' => myronNow(),
            'entityCounts' => $this->entityCounts($data),
            'data' => $data,
        ];

        return MyronSiteSnapshotEnvelope::withHash($envelope);
    }

    public function exportJson(): string
    {
        return MyronSiteSnapshotEnvelope::prettyJson($this->export());
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function rows(string $table, string $orderBy): array
    {
        if (!$this->tableExists($table)) {
            return [];
        }

        $statement = $this->pdo->query('SELECT * FROM ' . $table . ' ORDER BY ' . $orderBy);
        $rows = $statement ? $statement->fetchAll(PDO::FETCH_ASSOC) : [];
        return is_array($rows) ? array_map([$this, 'normaliseRow'], $rows) : [];
    }

    /**
     * @return array<string, mixed>
     */
    private function publishedPagesSection(): array
    {
        if (!$this->tableExists('pages')) {
            return ['pages' => [], 'versions' => []];
        }

        $rolePageIds = [];
        if ($this->tableExists('site_page_roles')) {
            $rolePageIds = array_values(array_map('strval', $this->pdo->query('SELECT DISTINCT page_id FROM site_page_roles')?->fetchAll(PDO::FETCH_COLUMN) ?: []));
        }
        $roleClause = $rolePageIds !== []
            ? ' OR id IN (' . implode(',', array_map([$this->pdo, 'quote'], $rolePageIds)) . ')'
            : '';
        $pages = $this->pdo->query(
            "SELECT * FROM pages
             WHERE publication_state = 'published'
                OR (current_published_version_id IS NOT NULL AND TRIM(current_published_version_id) <> '')"
                . $roleClause .
            ' ORDER BY path ASC, id ASC'
        )?->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $pages = array_map([$this, 'normaliseRow'], $pages);

        $versionIds = [];
        foreach ($pages as $page) {
            $versionId = trim((string) ($page['current_published_version_id'] ?? ''));
            if ($versionId !== '') {
                $versionIds[] = $versionId;
            }
            if (in_array((string) ($page['id'] ?? ''), $rolePageIds, true)) {
                $draftVersionId = trim((string) ($page['current_draft_version_id'] ?? ''));
                if ($draftVersionId !== '') {
                    $versionIds[] = $draftVersionId;
                }
            }
        }
        $versionIds = array_values(array_unique($versionIds));

        $versions = [];
        if ($versionIds !== [] && $this->tableExists('page_versions')) {
            $placeholders = implode(',', array_fill(0, count($versionIds), '?'));
            $statement = $this->pdo->prepare('SELECT * FROM page_versions WHERE id IN (' . $placeholders . ') ORDER BY page_id ASC, version_number ASC, id ASC');
            $statement->execute($versionIds);
            $versions = $statement->fetchAll(PDO::FETCH_ASSOC) ?: [];
            $versions = array_map([$this, 'normaliseRow'], $versions);
        }

        return ['pages' => $pages, 'versions' => $versions, 'roles' => $this->rows('site_page_roles', 'role_key ASC')];
    }

    /**
     * @return array<string, mixed>
     */
    private function currentDesignDefinition(): array
    {
        $registry = new DesignSystemRegistry();
        if (method_exists($registry, 'ensureStorage')) {
            $registry->ensureStorage();
        }

        $current = $registry->currentDefinition();
        return [
            'record' => $current,
            'definition' => $registry->exportCurrentDefinition(
                (string) ($current['id'] ?? ''),
                (string) ($current['name'] ?? '')
            ),
        ];
    }

    /**
     * @param array<string, mixed> $data
     * @return array<string, int>
     */
    private function entityCounts(array $data): array
    {
        return [
            'siteSettings' => count((array) ($data['siteSettings']['rows'] ?? [])),
            'tokenPatches' => count((array) ($data['designDefinition']['tokenPatches'] ?? [])),
            'customCssSettings' => count((array) ($data['designDefinition']['customCssSettings'] ?? [])),
            'customSectionTemplates' => count((array) ($data['customSectionTemplates']['rows'] ?? [])),
            'pageTemplates' => count((array) ($data['pageTemplates']['templates'] ?? [])),
            'pageTemplateVersions' => count((array) ($data['pageTemplates']['versions'] ?? [])),
            'pages' => count((array) ($data['pages']['pages'] ?? [])),
            'pageVersions' => count((array) ($data['pages']['versions'] ?? [])),
            'pageRoles' => count((array) ($data['pages']['roles'] ?? [])),
            'menus' => count((array) ($data['navigation']['menus'] ?? [])),
            'menuItems' => count((array) ($data['navigation']['menuItems'] ?? [])),
            'navigationDesignSettings' => count((array) ($data['navigation']['navigationDesignSettings'] ?? [])),
            'navigationDesigns' => count((array) ($data['navigation']['navigationDesigns'] ?? [])),
        ];
    }

    /**
     * @param array<string, mixed> $row
     * @return array<string, mixed>
     */
    private function normaliseRow(array $row): array
    {
        foreach ($row as $key => $value) {
            if (is_int($key)) {
                unset($row[$key]);
            }
        }
        return $row;
    }

    private function tableExists(string $table): bool
    {
        try {
            $this->pdo->query('SELECT 1 FROM ' . $table . ' LIMIT 1');
            return true;
        } catch (Throwable) {
            return false;
        }
    }
}
