<?php

declare(strict_types=1);

require_once __DIR__ . '/seed/ContentLifecycleTrait.php';
require_once __DIR__ . '/definitions/TemplateDefinitionRepository.php';
require_once __DIR__ . '/templates/TemplatesTrait.php';
require_once __DIR__ . '/pages/PagesTrait.php';
require_once __DIR__ . '/pages/PageCategoriesTrait.php';
require_once __DIR__ . '/public/PublicReadModelTrait.php';
require_once __DIR__ . '/navigation/NavigationTrait.php';
require_once dirname(__DIR__) . '/quality/QualityRuleRegistry.php';
require_once dirname(__DIR__) . '/quality/QualityReportService.php';

final class MyronContentRepository
{
    use MyronContentLifecycleTrait;
    use MyronContentTemplatesTrait;
    use MyronContentPagesTrait;
    use MyronContentPageCategoriesTrait;
    use MyronContentPublicReadModelTrait;
    use MyronContentNavigationTrait;

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

    /**
     * @return list<array<string, mixed>>
     */
    public function listBundleSeedRecords(?string $bundleId = null): array
    {
        $sql = 'SELECT * FROM bundle_seed_records';
        $params = [];

        if ($bundleId !== null && trim($bundleId) !== '') {
            $sql .= ' WHERE bundle_id = :bundle_id';
            $params[':bundle_id'] = trim($bundleId);
        }

        $sql .= ' ORDER BY seed_type ASC, seed_key ASC';
        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);
        $rows = $statement->fetchAll();

        $records = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $payload = json_decode((string) ($row['payload_json'] ?? '{}'), true, 512, JSON_THROW_ON_ERROR);
            $records[] = [
                'seed_key' => (string) ($row['seed_key'] ?? ''),
                'bundle_id' => (string) ($row['bundle_id'] ?? ''),
                'seed_type' => (string) ($row['seed_type'] ?? ''),
                'payload' => is_array($payload) ? $payload : [],
                'created_at' => (string) ($row['created_at'] ?? ''),
                'updated_at' => (string) ($row['updated_at'] ?? ''),
            ];
        }

        return $records;
    }

    private function ensureSeedMenus(): void
    {
        $menuCount = (int) $this->pdo->query('SELECT COUNT(*) FROM menus')->fetchColumn();
        if ($menuCount === 0) {
            $now = myronNow();
            $insert = $this->pdo->prepare(
                'INSERT INTO menus (id, menu_key, name, placement, created_at, updated_at)
                 VALUES (:id, :menu_key, :name, :placement, :created_at, :updated_at)'
            );

            foreach ([
                ['menu_key' => 'primary-navigation', 'name' => 'Primary Navigation', 'placement' => 'primary'],
                ['menu_key' => 'footer-navigation', 'name' => 'Footer Navigation', 'placement' => 'footer'],
            ] as $menu) {
                $insert->execute([
                    ':id' => myronGenerateId('menu'),
                    ':menu_key' => $menu['menu_key'],
                    ':name' => $menu['name'],
                    ':placement' => $menu['placement'],
                    ':created_at' => $now,
                    ':updated_at' => $now,
                ]);
            }
        }

        $placements = $this->listMenuPlacements();
        $menusByKey = [];
        foreach ($this->listMenusWithItems() as $menu) {
            $menusByKey[(string) $menu['menu_key']] = $menu;
        }

        $assignPlacement = $this->pdo->prepare(
            'UPDATE menu_placements
             SET menu_id = :menu_id,
                 updated_at = :updated_at
             WHERE placement_key = :placement_key
               AND (menu_id IS NULL OR menu_id = \'\')'
        );

        foreach ($placements as $placement) {
            $placementKey = (string) ($placement['placement_key'] ?? '');
            $currentMenuId = trim((string) ($placement['menu_id'] ?? ''));
            if ($placementKey === '' || $currentMenuId !== '') {
                continue;
            }

            $defaultMenuKey = $placementKey === 'primary' ? 'primary-navigation' : ($placementKey === 'footer' ? 'footer-navigation' : '');
            if ($defaultMenuKey === '' || !isset($menusByKey[$defaultMenuKey])) {
                continue;
            }

            $assignPlacement->execute([
                ':menu_id' => (string) $menusByKey[$defaultMenuKey]['id'],
                ':updated_at' => myronNow(),
                ':placement_key' => $placementKey,
            ]);
        }

        $menus = $this->listMenusWithItems();
        $menusById = [];
        foreach ($menus as $menu) {
            $menusById[(string) $menu['id']] = $menu;
        }

        $publishedPages = $this->listPublishedPages();
        $seedTargets = [];
        foreach ($publishedPages as $page) {
            $path = (string) $page['path'];
            if ($path === '/' || $path === '/about') {
                $seedTargets[] = $page;
            }
        }

        foreach ($this->listMenuPlacements() as $placement) {
            $menuId = trim((string) ($placement['menu_id'] ?? ''));
            if ($menuId === '' || !isset($menusById[$menuId])) {
                continue;
            }

            $menu = $menusById[$menuId];
            if (($menu['items'] ?? []) !== []) {
                continue;
            }

            foreach ($seedTargets as $page) {
                $this->addPageToMenu((string) $menu['id'], (string) $page['id'], (string) $page['title']);
            }
        }
    }

    private function seedDefaultTemplate(): void
    {
        $templateId = myronGenerateId('tpl');
        $versionId = myronGenerateId('tplver');
        $now = myronNow();

        $this->pdo->beginTransaction();

        try {
            $insertTemplate = $this->pdo->prepare(
                'INSERT INTO page_templates (id, template_key, name, description, current_version_id, created_at, updated_at)
                 VALUES (:id, :template_key, :name, :description, :current_version_id, :created_at, :updated_at)'
            );
            $insertTemplate->execute([
                ':id' => $templateId,
                ':template_key' => 'basic-page',
                ':name' => 'Basic Page',
                ':description' => 'A governed marketing/content template for the first DB-backed page slice.',
                ':current_version_id' => $versionId,
                ':created_at' => $now,
                ':updated_at' => $now,
            ]);

            $insertTemplateVersion = $this->pdo->prepare(
                'INSERT INTO page_template_versions (id, template_id, version_number, view_name, schema_json, created_at)
                 VALUES (:id, :template_id, :version_number, :view_name, :schema_json, :created_at)'
            );
            $insertTemplateVersion->execute([
                ':id' => $versionId,
                ':template_id' => $templateId,
                ':version_number' => 1,
                ':view_name' => 'basic-page',
                ':schema_json' => json_encode([
                    'regions' => [
                        ['key' => 'eyebrow', 'label' => 'Eyebrow', 'type' => 'text', 'richtext' => true],
                        ['key' => 'headline', 'label' => 'Headline', 'type' => 'text', 'richtext' => true],
                        ['key' => 'intro', 'label' => 'Intro', 'type' => 'textarea', 'richtext' => true],
                        ['key' => 'body', 'label' => 'Body', 'type' => 'textarea', 'richtext' => true],
                        ['key' => 'ctaLabel', 'label' => 'CTA label', 'type' => 'text', 'richtext' => true],
                        ['key' => 'ctaHref', 'label' => 'CTA href', 'type' => 'text'],
                    ],
                ], JSON_THROW_ON_ERROR | JSON_PRETTY_PRINT),
                ':created_at' => $now,
            ]);

            $this->pdo->commit();
        } catch (Throwable $exception) {
            $this->pdo->rollBack();
            throw $exception;
        }
    }

    private function insertPageVersion(
        string $versionId,
        string $pageId,
        string $templateVersionId,
        int $versionNumber,
        string $title,
        string $seoTitle,
        string $metaDescription,
        array $content,
        ?string $publishedAt,
        array $seoOverrides = []
    ): void {
        $seo = $this->normalizeSeoOverrides($seoOverrides);
        $statement = $this->pdo->prepare(
            'INSERT INTO page_versions (
                id, page_id, template_version_id, version_number,
                title, seo_title, meta_description,
                canonical_override, robots_override,
                og_title, og_description, og_image,
                twitter_title, twitter_description, twitter_image,
                jsonld_mode, jsonld_type, jsonld_custom,
                content_json, created_at, published_at
             ) VALUES (
                :id, :page_id, :template_version_id, :version_number,
                :title, :seo_title, :meta_description,
                :canonical_override, :robots_override,
                :og_title, :og_description, :og_image,
                :twitter_title, :twitter_description, :twitter_image,
                :jsonld_mode, :jsonld_type, :jsonld_custom,
                :content_json, :created_at, :published_at
             )'
        );
        $statement->execute([
            ':id' => $versionId,
            ':page_id' => $pageId,
            ':template_version_id' => $templateVersionId,
            ':version_number' => $versionNumber,
            ':title' => $title,
            ':seo_title' => $seoTitle !== '' ? $seoTitle : $title,
            ':meta_description' => $metaDescription,
            ':canonical_override' => $seo['canonical_override'],
            ':robots_override' => $seo['robots_override'],
            ':og_title' => $seo['og_title'],
            ':og_description' => $seo['og_description'],
            ':og_image' => $seo['og_image'],
            ':twitter_title' => $seo['twitter_title'],
            ':twitter_description' => $seo['twitter_description'],
            ':twitter_image' => $seo['twitter_image'],
            ':jsonld_mode' => $seo['jsonld_mode'],
            ':jsonld_type' => $seo['jsonld_type'],
            ':jsonld_custom' => $seo['jsonld_custom'],
            ':content_json' => json_encode($content, JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES),
            ':created_at' => myronNow(),
            ':published_at' => $publishedAt,
        ]);
    }

    /**
     * @param array<string, mixed> $input
     * @return array<string, string>
     */
    private function normalizeSeoOverrides(array $input): array
    {
        $mode = trim((string) ($input['jsonld_mode'] ?? 'auto'));
        if (!in_array($mode, ['auto', 'custom', 'none'], true)) {
            $mode = 'auto';
        }
        $type = trim((string) ($input['jsonld_type'] ?? 'Article'));
        if (!in_array($type, ['Article', 'BlogPosting', 'WebPage'], true)) {
            $type = 'Article';
        }

        $fields = [
            'canonical_override',
            'robots_override',
            'og_title',
            'og_description',
            'og_image',
            'twitter_title',
            'twitter_description',
            'twitter_image',
            'jsonld_custom',
        ];
        $normalized = [];
        foreach ($fields as $field) {
            $normalized[$field] = trim((string) ($input[$field] ?? ''));
        }
        $normalized['jsonld_mode'] = $mode;
        $normalized['jsonld_type'] = $type;
        return $normalized;
    }

    private function nextVersionNumber(string $pageId): int
    {
        $statement = $this->pdo->prepare('SELECT COALESCE(MAX(version_number), 0) FROM page_versions WHERE page_id = :page_id');
        $statement->execute([':page_id' => $pageId]);

        return ((int) $statement->fetchColumn()) + 1;
    }

    private function resolveSlug(string $pageName, string $slugInput): string
    {
        $candidate = trim($slugInput) !== '' ? $slugInput : $pageName;
        return myronNormalizeSlug($candidate);
    }

    private function normalizeParentPageId(?string $parentPageId): ?string
    {
        $normalized = is_string($parentPageId) ? trim($parentPageId) : '';
        return $normalized === '' ? null : $normalized;
    }

    private function buildPath(string $slug, ?string $parentPageId, ?string $excludePageId): string
    {
        $normalizedSlug = myronNormalizeSlug($slug);
        $this->assertUniqueSlug($normalizedSlug, $excludePageId);

        if ($parentPageId === null) {
            $path = $this->rootPathForSlug($normalizedSlug, $excludePageId);
            $this->assertUniquePath($path, $excludePageId);
            return $path;
        }

        $this->assertParentIsValid($parentPageId, $excludePageId);
        $parent = $this->requirePage($parentPageId);
        $parentPath = (string) $parent['path'];
        $path = ($parentPath === '/' ? '' : $parentPath) . '/' . $normalizedSlug;
        $path = myronNormalizePath($path);
        $this->assertUniquePath($path, $excludePageId);

        return $path;
    }

    private function rootPathForSlug(string $slug, ?string $excludePageId): string
    {
        if ($slug !== 'home') {
            return myronNormalizePath('/' . $slug);
        }

        $statement = $this->pdo->prepare('SELECT id FROM pages WHERE path = \'/\' LIMIT 1');
        $statement->execute();
        $existingRoot = $statement->fetch();

        if (!is_array($existingRoot) || ($excludePageId !== null && (string) $existingRoot['id'] === $excludePageId)) {
            return '/';
        }

        return myronNormalizePath('/' . $slug);
    }

    private function assertParentIsValid(?string $parentPageId, ?string $pageId): void
    {
        if ($parentPageId === null) {
            return;
        }

        if ($pageId !== null && $parentPageId === $pageId) {
            throw new InvalidArgumentException('A page cannot be its own parent.');
        }

        $cursor = $parentPageId;
        while ($cursor !== null) {
            $parent = $this->requirePage($cursor);
            if ($pageId !== null && (string) $parent['id'] === $pageId) {
                throw new InvalidArgumentException('Page hierarchy cannot contain a cycle.');
            }

            $cursor = isset($parent['parent_page_id']) && is_string($parent['parent_page_id']) && trim((string) $parent['parent_page_id']) !== ''
                ? (string) $parent['parent_page_id']
                : null;
        }
    }

    private function assertUniquePath(string $path, ?string $excludePageId = null): void
    {
        $sql = 'SELECT id FROM pages WHERE path = :path';
        $params = [':path' => $path];

        if ($excludePageId !== null) {
            $sql .= ' AND id <> :page_id';
            $params[':page_id'] = $excludePageId;
        }

        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);
        $existing = $statement->fetch();

        if (is_array($existing)) {
            throw new InvalidArgumentException('Another page already owns that route.');
        }
    }

    private function assertUniqueSlug(string $slug, ?string $excludePageId = null): void
    {
        $sql = 'SELECT id FROM pages WHERE slug = :slug';
        $params = [':slug' => $slug];

        if ($excludePageId !== null) {
            $sql .= ' AND id <> :page_id';
            $params[':page_id'] = $excludePageId;
        }

        $statement = $this->pdo->prepare($sql);
        $statement->execute($params);
        $existing = $statement->fetch();

        if (is_array($existing)) {
            throw new InvalidArgumentException('Another page already uses that slug.');
        }
    }

    private function findTemplateByKey(string $templateKey): ?array
    {
        $statement = $this->pdo->prepare('SELECT * FROM page_templates WHERE template_key = :template_key LIMIT 1');
        $statement->execute([':template_key' => $templateKey]);
        $row = $statement->fetch();

        return is_array($row) ? $row : null;
    }

    private function requireTemplate(string $templateId): array
    {
        $statement = $this->pdo->prepare('SELECT * FROM page_templates WHERE id = :template_id LIMIT 1');
        $statement->execute([':template_id' => $templateId]);
        $template = $statement->fetch();

        if (!is_array($template)) {
            throw new InvalidArgumentException('Selected template does not exist.');
        }

        return $template;
    }

    private function requirePage(string $pageId): array
    {
        $statement = $this->pdo->prepare('SELECT * FROM pages WHERE id = :page_id LIMIT 1');
        $statement->execute([':page_id' => $pageId]);
        $page = $statement->fetch();

        if (!is_array($page)) {
            throw new InvalidArgumentException('Selected page does not exist.');
        }

        return $page;
    }

    private function findMenuById(string $menuId): ?array
    {
        foreach ($this->listMenusWithItems() as $menu) {
            if ((string) ($menu['id'] ?? '') === $menuId) {
                return $menu;
            }
        }

        return null;
    }

    private function requireMenu(string $menuId): array
    {
        $statement = $this->pdo->prepare('SELECT * FROM menus WHERE id = :menu_id LIMIT 1');
        $statement->execute([':menu_id' => $menuId]);
        $menu = $statement->fetch();

        if (!is_array($menu)) {
            throw new InvalidArgumentException('Selected menu does not exist.');
        }

        return $menu;
    }

    private function requireMenuItem(string $menuItemId): array
    {
        $statement = $this->pdo->prepare('SELECT * FROM menu_items WHERE id = :menu_item_id LIMIT 1');
        $statement->execute([':menu_item_id' => $menuItemId]);
        $item = $statement->fetch();

        if (!is_array($item)) {
            throw new InvalidArgumentException('Selected menu item does not exist.');
        }

        return $item;
    }

    /**
     * @param list<array<string, mixed>> $items
     * @return list<array<string, mixed>>
     */
    private function buildMenuTree(array $items, ?string $parentId = null): array
    {
        $tree = [];

        foreach ($items as $item) {
            $itemParentId = $this->normalizeMenuItemId((string) ($item['parent_menu_item_id'] ?? ''));
            if ($itemParentId !== $parentId) {
                continue;
            }

            $item['children'] = $this->buildMenuTree($items, (string) $item['id']);
            $tree[] = $item;
        }

        usort(
            $tree,
            static fn (array $left, array $right): int => ((int) ($left['sort_order'] ?? 0)) <=> ((int) ($right['sort_order'] ?? 0))
        );

        return $tree;
    }

    /**
     * @param list<array<string, mixed>> $items
     * @return list<array<string, mixed>>
     */
    private function applyDefaultMenuParentsFromPageHierarchy(array $items): array
    {
        $menuItemIdByPageId = [];
        foreach ($items as $item) {
            $pageId = trim((string) ($item['page_id'] ?? ''));
            $itemId = trim((string) ($item['id'] ?? ''));
            if ($pageId !== '' && $itemId !== '') {
                $menuItemIdByPageId[$pageId] = $itemId;
            }
        }

        foreach ($items as &$item) {
            if ($this->normalizeMenuItemId((string) ($item['parent_menu_item_id'] ?? '')) !== null) {
                continue;
            }

            $pageParentId = trim((string) ($item['page_parent_id'] ?? ''));
            if ($pageParentId === '') {
                continue;
            }

            $defaultParentMenuItemId = $menuItemIdByPageId[$pageParentId] ?? '';
            if ($defaultParentMenuItemId !== '' && $defaultParentMenuItemId !== (string) ($item['id'] ?? '')) {
                $item['parent_menu_item_id'] = $defaultParentMenuItemId;
            }
        }
        unset($item);

        return $items;
    }

    /**
     * @param list<array<string, mixed>> $items
     * @return list<array<string, mixed>>
     */
    private function appendPublishedPageDescendantsToMenuItems(array $items): array
    {
        if ($items === []) {
            return [];
        }

        $menuItemIdByPageId = [];
        foreach ($items as $item) {
            $pageId = trim((string) ($item['page_id'] ?? ''));
            $itemId = trim((string) ($item['id'] ?? ''));
            if ($pageId !== '' && $itemId !== '') {
                $menuItemIdByPageId[$pageId] = $itemId;
            }
        }

        if ($menuItemIdByPageId === []) {
            return $items;
        }

        $disabledFilter = $this->tableHasColumn('pages', 'disabled_by_bundle_removal_at')
            ? ' AND pages.disabled_by_bundle_removal_at IS NULL'
            : '';
        $includeInNavFilter = $this->tableHasColumn('pages', 'include_in_nav')
            ? ' AND pages.include_in_nav = 1'
            : '';
        $statement = $this->pdo->query(
            'SELECT
                pages.id,
                pages.title,
                pages.path,
                pages.parent_page_id,
                pages.publication_state,
                pages.created_at,
                page_templates.name AS template_name,
                published.version_number AS published_version_number
             FROM pages
             LEFT JOIN page_templates ON page_templates.id = pages.template_id
             LEFT JOIN page_versions AS published ON published.id = pages.current_published_version_id
             WHERE pages.publication_state = \'published\'
               AND pages.current_published_version_id IS NOT NULL
               AND pages.parent_page_id IS NOT NULL
            ' . $disabledFilter .
            $includeInNavFilter .
            ' ORDER BY pages.parent_page_id ASC, pages.title ASC, pages.created_at ASC'
        );

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

            $parentPageId = trim((string) ($row['parent_page_id'] ?? ''));
            $pageId = trim((string) ($row['id'] ?? ''));
            if ($parentPageId === '' || $pageId === '') {
                continue;
            }

            $childrenByParentPageId[$parentPageId][] = $row;
        }

        if ($childrenByParentPageId === []) {
            return $items;
        }

        $queue = array_keys($menuItemIdByPageId);
        $visitedParentPageIds = [];
        while ($queue !== []) {
            $parentPageId = array_shift($queue);
            if (!is_string($parentPageId) || isset($visitedParentPageIds[$parentPageId])) {
                continue;
            }
            $visitedParentPageIds[$parentPageId] = true;

            $parentMenuItemId = $menuItemIdByPageId[$parentPageId] ?? '';
            if ($parentMenuItemId === '') {
                continue;
            }

            foreach (($childrenByParentPageId[$parentPageId] ?? []) as $index => $childPage) {
                $childPageId = trim((string) ($childPage['id'] ?? ''));
                if ($childPageId === '' || isset($menuItemIdByPageId[$childPageId])) {
                    continue;
                }

                $pagePath = (string) ($childPage['path'] ?? '/');
                $virtualMenuItemId = 'derived-page-' . $childPageId;
                $items[] = [
                    'id' => $virtualMenuItemId,
                    'label' => (string) ($childPage['title'] ?? ''),
                    'page_id' => $childPageId,
                    'page_title' => (string) ($childPage['title'] ?? ''),
                    'page_parent_id' => $parentPageId,
                    'page_path' => $pagePath,
                    'path' => $pagePath,
                    'url' => myronBuildPublicPageUrl($pagePath),
                    'publication_state' => (string) ($childPage['publication_state'] ?? 'published'),
                    'template_name' => (string) ($childPage['template_name'] ?? ''),
                    'manager_version_number' => isset($childPage['published_version_number']) ? (int) $childPage['published_version_number'] : null,
                    'preview_url' => myronBuildPublicPageUrl($pagePath),
                    'parent_menu_item_id' => $parentMenuItemId,
                    'sort_order' => 10000 + $index,
                    'is_derived_page_descendant' => true,
                    'children' => [],
                ];

                $menuItemIdByPageId[$childPageId] = $virtualMenuItemId;
                $queue[] = $childPageId;
            }
        }

        return $items;
    }

    /**
     * @param list<array<string, mixed>> $items
     * @return list<array<string, mixed>>
     */
    private function flattenMenuTree(array $items): array
    {
        $flattened = [];

        foreach ($items as $item) {
            $flattened[] = $item;
            $children = $item['children'] ?? [];
            if (is_array($children) && $children !== []) {
                $flattened = array_merge($flattened, $this->flattenMenuTree($children));
            }
        }

        return $flattened;
    }

    /**
     * @param list<array{key:string,label:string}> $placements
     */
    private function describeMenuPlacements(array $placements): string
    {
        if ($placements === []) {
            return 'Unassigned';
        }

        return implode(', ', array_map(static fn (array $placement): string => (string) $placement['label'], $placements));
    }

    private function nextMenuKey(string $name): string
    {
        $base = myronNormalizeSlug($name);
        if (!str_ends_with($base, 'navigation')) {
            $base .= '-navigation';
        }

        $candidate = $base;
        $suffix = 2;
        $statement = $this->pdo->prepare('SELECT COUNT(*) FROM menus WHERE menu_key = :menu_key');

        while (true) {
            $statement->execute([':menu_key' => $candidate]);
            if ((int) $statement->fetchColumn() === 0) {
                return $candidate;
            }

            $candidate = $base . '-' . $suffix;
            $suffix++;
        }
    }

    private function normalizeMenuItemId(?string $menuItemId): ?string
    {
        $normalized = is_string($menuItemId) ? trim($menuItemId) : '';
        return $normalized === '' ? null : $normalized;
    }

    /**
     * @param array<string, mixed> $page
     */
    private function defaultMenuParentItemIdForPage(string $menuId, array $page): ?string
    {
        $parentPageId = trim((string) ($page['parent_page_id'] ?? ''));
        if ($parentPageId === '') {
            return null;
        }

        $statement = $this->pdo->prepare(
            'SELECT id
             FROM menu_items
             WHERE menu_id = :menu_id
               AND page_id = :page_id
             LIMIT 1'
        );
        $statement->execute([
            ':menu_id' => $menuId,
            ':page_id' => $parentPageId,
        ]);
        $menuItemId = trim((string) $statement->fetchColumn());

        return $menuItemId !== '' ? $menuItemId : null;
    }

    private function assertMenuParentIsValid(string $menuItemId, string $parentMenuItemId): void
    {
        if ($menuItemId === $parentMenuItemId) {
            throw new InvalidArgumentException('A menu item cannot become its own parent.');
        }

        $cursor = $parentMenuItemId;
        while ($cursor !== null) {
            $item = $this->requireMenuItem($cursor);
            if ((string) $item['id'] === $menuItemId) {
                throw new InvalidArgumentException('Menu nesting cannot contain a cycle.');
            }

            $cursor = $this->normalizeMenuItemId((string) ($item['parent_menu_item_id'] ?? ''));
        }
    }

    /**
     * @return list<string>
     */
    private function listSiblingMenuItemIds(string $menuId, ?string $parentMenuItemId, ?string $excludeMenuItemId = null): array
    {
        if ($parentMenuItemId === null) {
            $statement = $this->pdo->prepare(
                'SELECT id
                 FROM menu_items
                 WHERE menu_id = :menu_id
                   AND parent_menu_item_id IS NULL
                 ORDER BY sort_order ASC, created_at ASC'
            );
            $statement->execute([':menu_id' => $menuId]);
        } else {
            $statement = $this->pdo->prepare(
                'SELECT id
                 FROM menu_items
                 WHERE menu_id = :menu_id
                   AND parent_menu_item_id = :parent_menu_item_id
                 ORDER BY sort_order ASC, created_at ASC'
            );
            $statement->execute([
                ':menu_id' => $menuId,
                ':parent_menu_item_id' => $parentMenuItemId,
            ]);
        }

        $ids = array_map('strval', $statement->fetchAll(PDO::FETCH_COLUMN));
        if ($excludeMenuItemId !== null) {
            $ids = array_values(array_filter($ids, static fn (string $id): bool => $id !== $excludeMenuItemId));
        }

        return $ids;
    }

    /**
     * @param list<string> $siblingIds
     */
    private function writeSiblingOrder(array $siblingIds, ?string $parentMenuItemId): void
    {
        $statement = $this->pdo->prepare(
            'UPDATE menu_items
             SET parent_menu_item_id = :parent_menu_item_id,
                 sort_order = :sort_order,
                 updated_at = :updated_at
             WHERE id = :id'
        );

        foreach (array_values($siblingIds) as $index => $id) {
            $statement->execute([
                ':parent_menu_item_id' => $parentMenuItemId,
                ':sort_order' => $index + 1,
                ':updated_at' => myronNow(),
                ':id' => $id,
            ]);
        }
    }
}
