<?php

declare(strict_types=1);

final class MyronMigrationRunner
{
    public static function migrate(PDO $pdo): void
    {
        self::migrateWithReport($pdo);
    }

    /**
     * @return array{registered:list<string>,applied:list<string>,pending:list<string>,appliedNow:list<string>}
     */
    public static function migrateWithReport(PDO $pdo): array
    {
        self::ensureTrackingTable($pdo);
        $driver = (string) $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        $mysqlLockHeld = false;
        $sqliteTransaction = false;
        $appliedNow = [];

        try {
            if ($driver === 'mysql') {
                $statement = $pdo->prepare('SELECT GET_LOCK(:lock_name, 30)');
                $statement->execute([':lock_name' => 'myroncms_schema_migrations']);
                if ((int) $statement->fetchColumn() !== 1) {
                    throw new RuntimeException('migration_lock_unavailable');
                }
                $mysqlLockHeld = true;
            } elseif ($driver === 'sqlite' && !$pdo->inTransaction()) {
                $pdo->exec('BEGIN IMMEDIATE');
                $sqliteTransaction = true;
            }

            $migrations = self::migrations($pdo);
            $appliedVersions = array_fill_keys(self::appliedVersions($pdo), true);

            foreach ($migrations as $version => $migration) {
                if (isset($appliedVersions[$version])) {
                    continue;
                }

                $perMigrationTransaction = !$sqliteTransaction && !$pdo->inTransaction();
                if ($perMigrationTransaction) {
                    $pdo->beginTransaction();
                }

                try {
                    if (is_callable($migration)) {
                        $migration($pdo);
                    } else {
                        foreach ($migration as $statement) {
                            self::runStatement($pdo, $statement);
                        }
                    }

                    $record = $pdo->prepare('INSERT INTO schema_migrations (version, applied_at) VALUES (:version, :applied_at)');
                    $record->execute([
                        ':version' => $version,
                        ':applied_at' => myronNow(),
                    ]);
                    if ($perMigrationTransaction && $pdo->inTransaction()) {
                        $pdo->commit();
                    } elseif ($driver === 'mysql') {
                        // MySQL DDL statements cause an implicit commit of the surrounding
                        // transaction but leave autocommit=0 on the connection. Without an
                        // explicit COMMIT here the schema_migrations INSERT would be rolled
                        // back when the connection closes.
                        $pdo->exec('COMMIT');
                    }
                    $appliedVersions[$version] = true;
                    $appliedNow[] = $version;
                } catch (Throwable $exception) {
                    if ($perMigrationTransaction && $pdo->inTransaction()) {
                        $pdo->rollBack();
                    }
                    throw $exception;
                }
            }

            if ($sqliteTransaction && $pdo->inTransaction()) {
                $pdo->commit();
                $sqliteTransaction = false;
            }
        } catch (Throwable $exception) {
            if ($sqliteTransaction && $pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw $exception;
        } finally {
            if ($mysqlLockHeld) {
                try {
                    $statement = $pdo->prepare('SELECT RELEASE_LOCK(:lock_name)');
                    $statement->execute([':lock_name' => 'myroncms_schema_migrations']);
                } catch (Throwable) {
                    // The database releases named locks when the connection closes.
                }
            }
        }

        $status = self::status($pdo);
        $status['appliedNow'] = $appliedNow;
        return $status;
    }

    /**
     * @return array{registered:list<string>,applied:list<string>,pending:list<string>}
     */
    public static function status(PDO $pdo): array
    {
        $migrations = self::migrations($pdo);
        $registered = array_values(array_map('strval', array_keys($migrations)));
        $applied = self::trackingTablePresent($pdo) ? self::appliedVersions($pdo) : [];
        $appliedLookup = array_fill_keys($applied, true);
        $pending = array_values(array_filter(
            $registered,
            static fn (string $version): bool => !isset($appliedLookup[$version])
        ));

        return [
            'registered' => $registered,
            'applied' => $applied,
            'pending' => $pending,
        ];
    }

    private static function ensureTrackingTable(PDO $pdo): void
    {
        self::runStatement(
            $pdo,
            'CREATE TABLE IF NOT EXISTS schema_migrations (
                version VARCHAR(64) PRIMARY KEY,
                applied_at TEXT NOT NULL
            )'
        );
    }

    /** @return list<string> */
    private static function appliedVersions(PDO $pdo): array
    {
        $applied = $pdo->query('SELECT version FROM schema_migrations ORDER BY version ASC')->fetchAll(PDO::FETCH_COLUMN);
        return array_values(array_map('strval', is_array($applied) ? $applied : []));
    }

    private static function trackingTablePresent(PDO $pdo): bool
    {
        try {
            return self::tableExists($pdo, 'schema_migrations');
        } catch (Throwable) {
            return false;
        }
    }

    /**
     * @return array<string, callable(PDO): void|list<string>>
     */
    private static function migrations(PDO $pdo): array
    {
        $driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        $tableSuffix = $driver === 'mysql'
            ? ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
            : '';

        $migrations = [
            '001_domain_foundation' => [
                'CREATE TABLE IF NOT EXISTS page_templates (
                    id VARCHAR(32) PRIMARY KEY,
                    template_key VARCHAR(100) NOT NULL UNIQUE,
                    name VARCHAR(160) NOT NULL,
                    description TEXT NOT NULL,
                    current_version_id VARCHAR(32) DEFAULT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )' . $tableSuffix,
                'CREATE TABLE IF NOT EXISTS page_template_versions (
                    id VARCHAR(32) PRIMARY KEY,
                    template_id VARCHAR(32) NOT NULL,
                    version_number INTEGER NOT NULL,
                    view_name VARCHAR(120) NOT NULL,
                    schema_json TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    UNIQUE(template_id, version_number)
                )' . $tableSuffix,
                'CREATE TABLE IF NOT EXISTS pages (
                    id VARCHAR(32) PRIMARY KEY,
                    template_id VARCHAR(32) NOT NULL,
                    slug VARCHAR(160) NOT NULL UNIQUE,
                    path VARCHAR(255) NOT NULL UNIQUE,
                    title VARCHAR(255) NOT NULL,
                    publication_state VARCHAR(32) NOT NULL DEFAULT \'draft\',
                    current_draft_version_id VARCHAR(32) DEFAULT NULL,
                    current_published_version_id VARCHAR(32) DEFAULT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                )' . $tableSuffix,
                'CREATE TABLE IF NOT EXISTS page_versions (
                    id VARCHAR(32) PRIMARY KEY,
                    page_id VARCHAR(32) NOT NULL,
                    template_version_id VARCHAR(32) NOT NULL,
                    version_number INTEGER NOT NULL,
                    title VARCHAR(255) NOT NULL,
                    seo_title VARCHAR(255) NOT NULL,
                    meta_description TEXT NOT NULL,
                    content_json TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    published_at TEXT DEFAULT NULL,
                    UNIQUE(page_id, version_number)
                )' . $tableSuffix,
            ],
            '002_page_schema_reconciliation' => static function (PDO $pdo) use ($driver, $tableSuffix): void {
                $pagesReady = self::tableHasColumns($pdo, 'pages', [
                    'template_id',
                    'path',
                    'publication_state',
                    'current_draft_version_id',
                    'current_published_version_id',
                ]);
                $versionsReady = self::tableHasColumns($pdo, 'page_versions', [
                    'template_version_id',
                    'title',
                    'meta_description',
                    'content_json',
                ]);

                if ($pagesReady && $versionsReady) {
                    return;
                }

                self::renameTableIfNeeded($pdo, $driver, 'page_versions', 'page_versions_legacy_apo016');
                self::renameTableIfNeeded($pdo, $driver, 'pages', 'pages_legacy_apo016');

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS pages (
                        id VARCHAR(32) PRIMARY KEY,
                        template_id VARCHAR(32) NOT NULL,
                        slug VARCHAR(160) NOT NULL UNIQUE,
                        path VARCHAR(255) NOT NULL UNIQUE,
                        title VARCHAR(255) NOT NULL,
                        publication_state VARCHAR(32) NOT NULL DEFAULT \'draft\',
                        current_draft_version_id VARCHAR(32) DEFAULT NULL,
                        current_published_version_id VARCHAR(32) DEFAULT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS page_versions (
                        id VARCHAR(32) PRIMARY KEY,
                        page_id VARCHAR(32) NOT NULL,
                        template_version_id VARCHAR(32) NOT NULL,
                        version_number INTEGER NOT NULL,
                        title VARCHAR(255) NOT NULL,
                        seo_title VARCHAR(255) NOT NULL,
                        meta_description TEXT NOT NULL,
                        content_json TEXT NOT NULL,
                        created_at TEXT NOT NULL,
                        published_at TEXT DEFAULT NULL,
                        UNIQUE(page_id, version_number)
                    )' . $tableSuffix
                );
            },
            '003_identity_content_structure_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS roles (
                        id VARCHAR(32) PRIMARY KEY,
                        role_key VARCHAR(100) NOT NULL UNIQUE,
                        name VARCHAR(160) NOT NULL,
                        description TEXT NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS users (
                        id VARCHAR(32) PRIMARY KEY,
                        email VARCHAR(255) NOT NULL UNIQUE,
                        display_name VARCHAR(160) NOT NULL,
                        password_hash TEXT NOT NULL,
                        role_id VARCHAR(32) NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        last_login_at TEXT DEFAULT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS menus (
                        id VARCHAR(32) PRIMARY KEY,
                        menu_key VARCHAR(100) NOT NULL UNIQUE,
                        name VARCHAR(160) NOT NULL,
                        placement VARCHAR(100) NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS menu_items (
                        id VARCHAR(32) PRIMARY KEY,
                        menu_id VARCHAR(32) NOT NULL,
                        page_id VARCHAR(32) NOT NULL,
                        label VARCHAR(255) NOT NULL,
                        sort_order INTEGER NOT NULL DEFAULT 0,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        UNIQUE(menu_id, page_id)
                    )' . $tableSuffix
                );

                self::addColumnIfMissing($pdo, 'pages', 'parent_page_id', 'VARCHAR(32) DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'menu_items', 'parent_menu_item_id', 'VARCHAR(32) DEFAULT NULL');
            },
            '004_pages_mail_recovery_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::addColumnIfMissing($pdo, 'menu_items', 'parent_menu_item_id', 'VARCHAR(32) DEFAULT NULL');

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS mail_settings (
                        id INTEGER PRIMARY KEY,
                        smtp_host VARCHAR(255) NOT NULL,
                        smtp_port INTEGER NOT NULL DEFAULT 587,
                        smtp_security VARCHAR(16) NOT NULL DEFAULT \'tls\',
                        smtp_username VARCHAR(255) NOT NULL,
                        smtp_password_encrypted TEXT NOT NULL,
                        from_email VARCHAR(255) NOT NULL,
                        from_name VARCHAR(255) NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS password_reset_tokens (
                        id VARCHAR(32) PRIMARY KEY,
                        user_id VARCHAR(32) NOT NULL,
                        selector VARCHAR(64) NOT NULL UNIQUE,
                        verifier_hash VARCHAR(255) NOT NULL,
                        requested_at TEXT NOT NULL,
                        expires_at TEXT NOT NULL,
                        consumed_at TEXT DEFAULT NULL
                    )' . $tableSuffix
                );
            },
            '005_files_users_shared_shell_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::addColumnIfMissing($pdo, 'users', 'username', 'VARCHAR(160) DEFAULT NULL');
                self::backfillUsernames($pdo);
                self::addUniqueIndexIfMissing($pdo, 'users', 'idx_users_username_unique', ['username']);

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS files (
                        id VARCHAR(32) PRIMARY KEY,
                        original_filename VARCHAR(255) NOT NULL,
                        stored_filename VARCHAR(255) NOT NULL UNIQUE,
                        storage_path VARCHAR(255) NOT NULL UNIQUE,
                        title VARCHAR(255) NOT NULL,
                        description TEXT NOT NULL,
                        alt_text TEXT NOT NULL,
                        caption TEXT NOT NULL,
                        mime_type VARCHAR(160) NOT NULL,
                        file_extension VARCHAR(32) NOT NULL,
                        file_size_bytes INTEGER NOT NULL,
                        image_width INTEGER DEFAULT NULL,
                        image_height INTEGER DEFAULT NULL,
                        uploaded_by_user_id VARCHAR(32) NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
            },
            '006_identity_expansion_registration_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::addColumnIfMissing($pdo, 'users', 'first_name', "VARCHAR(160) NOT NULL DEFAULT ''");
                self::addColumnIfMissing($pdo, 'users', 'last_name', "VARCHAR(160) NOT NULL DEFAULT ''");
                self::addColumnIfMissing($pdo, 'users', 'bio', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'avatar_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'facebook_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'x_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'pinterest_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'linkedin_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'youtube_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'instagram_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'tiktok_url', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'users', 'design_access', 'INTEGER NOT NULL DEFAULT 0');
                self::addColumnIfMissing($pdo, 'users', 'content_access', 'INTEGER NOT NULL DEFAULT 0');
                self::addColumnIfMissing($pdo, 'users', 'admin_access', 'INTEGER NOT NULL DEFAULT 0');
                self::addColumnIfMissing($pdo, 'users', 'is_active', 'INTEGER NOT NULL DEFAULT 1');
                self::addColumnIfMissing($pdo, 'users', 'email_verified_at', 'TEXT DEFAULT NULL');
                self::backfillIdentityAccess($pdo);
                self::backfillNullableIdentityProfileFields($pdo);

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS registration_settings (
                        id INTEGER PRIMARY KEY,
                        allowed_domains_json TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS registration_tokens (
                        id VARCHAR(32) PRIMARY KEY,
                        user_id VARCHAR(32) NOT NULL,
                        selector VARCHAR(64) NOT NULL UNIQUE,
                        verifier_hash VARCHAR(255) NOT NULL,
                        requested_at TEXT NOT NULL,
                        expires_at TEXT NOT NULL,
                        consumed_at TEXT DEFAULT NULL
                    )' . $tableSuffix
                );

                $statement = $pdo->prepare(
                    'INSERT INTO registration_settings (id, allowed_domains_json, updated_at)
                     SELECT 1, :allowed_domains_json, :updated_at
                     WHERE NOT EXISTS (SELECT 1 FROM registration_settings WHERE id = 1)'
                );
                $statement->execute([
                    ':allowed_domains_json' => json_encode([], JSON_THROW_ON_ERROR),
                    ':updated_at' => myronNow(),
                ]);
            },
            '007_menu_registry_and_placement_selection' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS menu_placements (
                        placement_key VARCHAR(100) PRIMARY KEY,
                        label VARCHAR(160) NOT NULL,
                        menu_id VARCHAR(32) DEFAULT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::backfillMenuPlacements($pdo);
            },
            '008_site_settings_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS site_settings (
                        id INTEGER PRIMARY KEY,
                        site_title VARCHAR(255) NOT NULL,
                        favicon_file_id VARCHAR(32) DEFAULT NULL,
                        site_logo_light_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        site_logo_dark_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        site_logo_alt VARCHAR(160) NOT NULL DEFAULT \'\',
                        site_language VARCHAR(32) NOT NULL,
                        timezone VARCHAR(120) NOT NULL,
                        date_format VARCHAR(32) NOT NULL,
                        time_format VARCHAR(32) NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                $timezone = date_default_timezone_get() ?: 'UTC';
                if (!in_array($timezone, timezone_identifiers_list(), true)) {
                    $timezone = 'UTC';
                }

                $statement = $pdo->prepare(
                    'INSERT INTO site_settings (
                        id,
                        site_title,
                        favicon_file_id,
                        site_logo_light_url,
                        site_logo_dark_url,
                        site_logo_alt,
                        site_language,
                        timezone,
                        date_format,
                        time_format,
                        updated_at
                     )
                     SELECT 1, :site_title, NULL, \'\', \'\', \'\', :site_language, :timezone, :date_format, :time_format, :updated_at
                     WHERE NOT EXISTS (SELECT 1 FROM site_settings WHERE id = 1)'
                );
                $statement->execute([
                    ':site_title' => 'MyronCMS',
                    ':site_language' => 'en-US',
                    ':timezone' => $timezone,
                    ':date_format' => 'F j, Y',
                    ':time_format' => 'g:i a',
                    ':updated_at' => myronNow(),
                ]);
            },
            '009_definitions_runtime_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS component_registry (
                        component_id VARCHAR(160) PRIMARY KEY,
                        classification VARCHAR(32) NOT NULL,
                        registry_address VARCHAR(255) NOT NULL,
                        source_path VARCHAR(255) NOT NULL,
                        role_scope_json TEXT DEFAULT NULL,
                        projected_role_scope_json TEXT DEFAULT NULL,
                        variant_count INTEGER NOT NULL DEFAULT 0,
                        record_json TEXT NOT NULL,
                        seeded_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS template_definitions (
                        template_id VARCHAR(160) PRIMARY KEY,
                        bundle_id VARCHAR(160) NOT NULL,
                        site_type VARCHAR(32) NOT NULL,
                        tier INTEGER NOT NULL DEFAULT 0,
                        source_path VARCHAR(255) NOT NULL,
                        role_scope_json TEXT DEFAULT NULL,
                        projected_role_scope_json TEXT DEFAULT NULL,
                        section_count INTEGER NOT NULL DEFAULT 0,
                        pending_section_count INTEGER NOT NULL DEFAULT 0,
                        record_json TEXT NOT NULL,
                        seeded_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS site_type_registry (
                        bundle_id VARCHAR(160) PRIMARY KEY,
                        code VARCHAR(32) NOT NULL UNIQUE,
                        name VARCHAR(255) NOT NULL,
                        bundle_type VARCHAR(32) NOT NULL,
                        parent_bundle_id VARCHAR(160) DEFAULT NULL,
                        indexable INTEGER NOT NULL DEFAULT 1,
                        source_path VARCHAR(255) NOT NULL,
                        default_template_count INTEGER NOT NULL DEFAULT 0,
                        optional_template_count INTEGER NOT NULL DEFAULT 0,
                        record_json TEXT NOT NULL,
                        seeded_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS bundle_activation_state (
                        bundle_id VARCHAR(160) PRIMARY KEY,
                        is_enabled INTEGER NOT NULL DEFAULT 1,
                        is_primary INTEGER NOT NULL DEFAULT 0,
                        activated_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS bundle_activation_audit (
                        id VARCHAR(32) PRIMARY KEY,
                        actor_id VARCHAR(32) NOT NULL,
                        context VARCHAR(120) NOT NULL,
                        primary_bundle_id VARCHAR(160) DEFAULT NULL,
                        before_json TEXT NOT NULL,
                        after_json TEXT NOT NULL,
                        created_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::addUniqueIndexIfMissing($pdo, 'component_registry', 'idx_component_registry_address_unique', ['registry_address']);
                self::addUniqueIndexIfMissing($pdo, 'template_definitions', 'idx_template_definitions_bundle_template', ['bundle_id', 'template_id']);
                self::addUniqueIndexIfMissing($pdo, 'site_type_registry', 'idx_site_type_registry_code_unique', ['code']);
            },
            '010_bundle_activation_audit' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS bundle_activation_audit (
                        id VARCHAR(32) PRIMARY KEY,
                        actor_id VARCHAR(32) NOT NULL,
                        context VARCHAR(120) NOT NULL,
                        primary_bundle_id VARCHAR(160) DEFAULT NULL,
                        before_json TEXT NOT NULL,
                        after_json TEXT NOT NULL,
                        created_at TEXT NOT NULL
                    )' . $tableSuffix
                );
            },
            '011_bundle_onboarding_instantiation_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS onboarding_state (
                        id INTEGER PRIMARY KEY,
                        primary_bundle_id VARCHAR(160) NOT NULL,
                        archetype_theme_id VARCHAR(160) NOT NULL,
                        applied_design_system_id VARCHAR(160) DEFAULT NULL,
                        setup_status VARCHAR(32) NOT NULL,
                        setup_completed_at TEXT DEFAULT NULL,
                        updated_by_user_id VARCHAR(32) DEFAULT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS bundle_seed_records (
                        seed_key VARCHAR(255) PRIMARY KEY,
                        bundle_id VARCHAR(160) NOT NULL,
                        seed_type VARCHAR(32) NOT NULL,
                        payload_json TEXT NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::addColumnIfMissing($pdo, 'page_templates', 'source_template_id', 'VARCHAR(160) DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'page_templates', 'source_bundle_id', 'VARCHAR(160) DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'pages', 'source_template_id', 'VARCHAR(160) DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'pages', 'source_bundle_id', 'VARCHAR(160) DEFAULT NULL');

                self::addUniqueIndexIfMissing($pdo, 'page_templates', 'idx_page_templates_source_template_unique', ['source_template_id']);
                self::addUniqueIndexIfMissing($pdo, 'pages', 'idx_pages_source_template_unique', ['source_template_id']);
            },
            '014_site_setup_selection_manifest' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'onboarding_state', 'selection_manifest_json', 'TEXT DEFAULT NULL');
            },
            '015_template_chrome_declaration_bridge' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'template_definitions', 'chrome_json', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'template_definitions', 'header_capabilities_json', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'page_templates', 'chrome_json', 'TEXT DEFAULT NULL');
                self::addColumnIfMissing($pdo, 'page_templates', 'header_capabilities_json', 'TEXT DEFAULT NULL');
            },
            '016_section_registry_contract' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS section_registry (
                        section_key VARCHAR(160) PRIMARY KEY,
                        label VARCHAR(255) NOT NULL,
                        jtbd_group VARCHAR(120) NOT NULL,
                        section_type VARCHAR(80) NOT NULL,
                        primary_components_json TEXT NOT NULL,
                        remediation_status VARCHAR(80) NOT NULL,
                        record_json TEXT NOT NULL,
                        seeded_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
            },
            '017_section_bridge_overrides' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS section_bridge_overrides (
                        section_key VARCHAR(160) PRIMARY KEY,
                        label_override VARCHAR(255) DEFAULT NULL,
                        description_override TEXT DEFAULT NULL,
                        primary_components_json TEXT NOT NULL,
                        remediation_status VARCHAR(80) NOT NULL,
                        default_content_editable INTEGER DEFAULT NULL,
                        record_json TEXT NOT NULL,
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
            },
            '018_section_component_defaults' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS section_component_defaults (
                        id VARCHAR(32) PRIMARY KEY,
                        component_id VARCHAR(160) NOT NULL,
                        variant VARCHAR(160) NOT NULL DEFAULT \'\',
                        default_content_json TEXT NOT NULL,
                        default_content_editable INTEGER DEFAULT NULL,
                        flags_json TEXT NOT NULL,
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::addUniqueIndexIfMissing($pdo, 'section_component_defaults', 'idx_section_component_defaults_identity', ['component_id', 'variant']);
            },
            '019_section_content_defaults' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS section_content_defaults (
                        id VARCHAR(32) PRIMARY KEY,
                        section_key VARCHAR(160) NOT NULL,
                        component_id VARCHAR(160) NOT NULL,
                        variant VARCHAR(160) NOT NULL DEFAULT \'\',
                        default_content_json TEXT NOT NULL,
                        default_content_editable INTEGER DEFAULT NULL,
                        flags_json TEXT NOT NULL,
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::addUniqueIndexIfMissing($pdo, 'section_content_defaults', 'idx_section_content_defaults_key', ['section_key']);
            },
            '020_navigation_design_settings' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS navigation_design_settings (
                        id INTEGER PRIMARY KEY,
                        desktop_alignment VARCHAR(32) NOT NULL,
                        menu_alignment VARCHAR(32) NOT NULL DEFAULT \'right\',
                        brand_display VARCHAR(32) NOT NULL DEFAULT \'title\',
                        brand_subtitle VARCHAR(120) NOT NULL DEFAULT \'\',
                        logo_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        logo_light_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        logo_dark_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        logo_alt VARCHAR(160) NOT NULL DEFAULT \'\',
                        utility_actions_display VARCHAR(32) NOT NULL DEFAULT \'auto\',
                        appearance_mode_policy VARCHAR(32) NOT NULL DEFAULT \'auto\',
                        density VARCHAR(32) NOT NULL,
                        mobile_mode VARCHAR(32) NOT NULL,
                        surface_treatment VARCHAR(32) NOT NULL,
                        position_behavior VARCHAR(32) NOT NULL,
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                $now = myronNow();
                $statement = $pdo->prepare(
                    'INSERT INTO navigation_design_settings (
                        id,
                        desktop_alignment,
                        menu_alignment,
                        brand_display,
                        brand_subtitle,
                        logo_url,
                        logo_alt,
                        utility_actions_display,
                        appearance_mode_policy,
                        density,
                        mobile_mode,
                        surface_treatment,
                        position_behavior,
                        updated_by_user_id,
                        created_at,
                        updated_at
                     )
                     SELECT
                        1,
                        \'split\',
                        \'right\',
                        \'title\',
                        \'\',
                        \'\',
                        \'\',
                        \'auto\',
                        \'auto\',
                        \'comfortable\',
                        \'takeover\',
                        \'bordered\',
                        \'static\',
                        \'\',
                        :created_at,
                        :updated_at
                     WHERE NOT EXISTS (
                        SELECT 1 FROM navigation_design_settings WHERE id = 1
                     )'
                );
                $statement->execute([
                    ':created_at' => $now,
                    ':updated_at' => $now,
                ]);
            },
            '021_navigation_header_governance' => static function (PDO $pdo) use ($tableSuffix): void {
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'menu_alignment', 'VARCHAR(32) NOT NULL DEFAULT \'right\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'brand_display', 'VARCHAR(32) NOT NULL DEFAULT \'title\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'brand_subtitle', 'VARCHAR(120) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'logo_url', 'VARCHAR(500) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'logo_light_url', 'VARCHAR(500) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'logo_dark_url', 'VARCHAR(500) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'logo_alt', 'VARCHAR(160) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'utility_actions_display', 'VARCHAR(32) NOT NULL DEFAULT \'auto\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'appearance_mode_policy', 'VARCHAR(32) NOT NULL DEFAULT \'auto\'');

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS navigation_header_overrides (
                        id VARCHAR(32) PRIMARY KEY,
                        scope_type VARCHAR(32) NOT NULL,
                        scope_key VARCHAR(255) NOT NULL,
                        header_variant VARCHAR(32) NOT NULL DEFAULT \'inherit\',
                        primary_menu_visibility VARCHAR(32) NOT NULL DEFAULT \'inherit\',
                        utility_actions_visibility VARCHAR(32) NOT NULL DEFAULT \'inherit\',
                        brand_visibility VARCHAR(32) NOT NULL DEFAULT \'inherit\',
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        UNIQUE(scope_type, scope_key)
                    )' . $tableSuffix
                );
            },
            '022_footer_design_workspace' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS footer_design_settings (
                        id INTEGER PRIMARY KEY,
                        active_variant VARCHAR(64) NOT NULL DEFAULT \'footer.standard\',
                        zone_visibility TEXT NOT NULL,
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_at VARCHAR(32) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );

                $now = date('Y-m-d\TH:i:s\Z');
                $pdo->exec(
                    'INSERT INTO footer_design_settings (id, active_variant, zone_visibility, updated_by_user_id, created_at, updated_at)
                     SELECT 1, \'footer.standard\', \'{}\', \'\', \'' . $now . '\', \'' . $now . '\'
                     WHERE NOT EXISTS (SELECT 1 FROM footer_design_settings WHERE id = 1)'
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS footer_variant_overrides (
                        id VARCHAR(32) PRIMARY KEY,
                        scope_type VARCHAR(32) NOT NULL,
                        scope_key VARCHAR(255) NOT NULL,
                        footer_variant VARCHAR(64) NOT NULL DEFAULT \'inherit\',
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_at VARCHAR(32) NOT NULL DEFAULT \'\',
                        UNIQUE(scope_type, scope_key)
                    )' . $tableSuffix
                );
            },
            '023_footer_zone_content' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS footer_design_settings (
                        id INTEGER PRIMARY KEY,
                        active_variant VARCHAR(64) NOT NULL DEFAULT \'footer.standard\',
                        zone_visibility TEXT NOT NULL,
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_at VARCHAR(32) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );
                self::addColumnIfMissing($pdo, 'footer_design_settings', 'zone_content_json', 'TEXT');
            },
            '023b_footer_recommendation_dismissals' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS footer_design_settings (
                        id INTEGER PRIMARY KEY,
                        active_variant VARCHAR(64) NOT NULL DEFAULT \'footer.standard\',
                        zone_visibility TEXT NOT NULL,
                        zone_content_json TEXT,
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_at VARCHAR(32) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );
                self::addColumnIfMissing($pdo, 'footer_design_settings', 'footer_recommendation_dismissals_json', 'TEXT');
            },
            '023c_chrome_orchestration_reset' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS footer_designs (
                        id VARCHAR(32) PRIMARY KEY,
                        variant_id VARCHAR(64) NOT NULL UNIQUE,
                        is_activated INTEGER NOT NULL DEFAULT 0,
                        zone_visibility_json TEXT NOT NULL,
                        zone_content_json TEXT NOT NULL,
                        is_default INTEGER NOT NULL DEFAULT 0,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        created_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\',
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS navigation_designs (
                        id VARCHAR(32) PRIMARY KEY,
                        name VARCHAR(160) NOT NULL UNIQUE,
                        is_activated INTEGER NOT NULL DEFAULT 0,
                        is_default INTEGER NOT NULL DEFAULT 0,
                        desktop_alignment VARCHAR(32) NOT NULL DEFAULT \'split\',
                        menu_alignment VARCHAR(32) NOT NULL DEFAULT \'right\',
                        brand_display VARCHAR(32) NOT NULL DEFAULT \'title\',
                        brand_subtitle VARCHAR(120) NOT NULL DEFAULT \'\',
                        logo_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        logo_light_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        logo_dark_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        logo_alt VARCHAR(160) NOT NULL DEFAULT \'\',
                        utility_actions_display VARCHAR(32) NOT NULL DEFAULT \'auto\',
                        appearance_mode_policy VARCHAR(32) NOT NULL DEFAULT \'auto\',
                        density VARCHAR(32) NOT NULL DEFAULT \'comfortable\',
                        mobile_mode VARCHAR(32) NOT NULL DEFAULT \'takeover\',
                        surface_treatment VARCHAR(32) NOT NULL DEFAULT \'bordered\',
                        position_behavior VARCHAR(32) NOT NULL DEFAULT \'static\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        created_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\',
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS template_chrome_assignments (
                        id VARCHAR(32) PRIMARY KEY,
                        template_id VARCHAR(255) NOT NULL UNIQUE,
                        footer_variant_id VARCHAR(64) DEFAULT NULL,
                        navigation_design_id VARCHAR(32) DEFAULT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );

                $now = date('Y-m-d\TH:i:s\Z');
                $footerSettings = [];
                if (self::tableExists($pdo, 'footer_design_settings')) {
                    $row = $pdo->query('SELECT * FROM footer_design_settings WHERE id = 1 LIMIT 1')?->fetch(PDO::FETCH_ASSOC);
                    $footerSettings = is_array($row) ? $row : [];
                }
                $activeVariant = trim((string) ($footerSettings['active_variant'] ?? 'footer.standard')) ?: 'footer.standard';
                $zoneVisibility = trim((string) ($footerSettings['zone_visibility'] ?? '{}')) ?: '{}';
                $zoneContent = trim((string) ($footerSettings['zone_content_json'] ?? '{}')) ?: '{}';
                $validVariants = [
                    'footer.standard',
                    'footer.portfolio',
                    'footer.editorial',
                    'footer.mega',
                    'footer.ecom',
                    'footer.local-business',
                    'footer.conversion',
                    'footer.creator',
                    'footer.campaign',
                    'footer.checkout',
                ];
                $insertFooter = $pdo->prepare(
                    'INSERT INTO footer_designs (
                        id, variant_id, is_activated, zone_visibility_json, zone_content_json,
                        is_default, created_at, updated_at, created_by_user_id, updated_by_user_id
                     )
                     SELECT :id, :variant_id, :is_activated, :zone_visibility_json, :zone_content_json,
                            :is_default, :created_at, :updated_at, \'\', \'\'
                     WHERE NOT EXISTS (SELECT 1 FROM footer_designs WHERE variant_id = :variant_id_existing)'
                );
                foreach ($validVariants as $variantId) {
                    $isActive = $variantId === $activeVariant;
                    $insertFooter->execute([
                        ':id' => 'fd_' . str_replace(['footer.', '-'], ['', '_'], $variantId),
                        ':variant_id' => $variantId,
                        ':is_activated' => $isActive ? 1 : 0,
                        ':zone_visibility_json' => $isActive ? $zoneVisibility : '{}',
                        ':zone_content_json' => $isActive ? $zoneContent : '{}',
                        ':is_default' => $isActive ? 1 : 0,
                        ':created_at' => $now,
                        ':updated_at' => $now,
                        ':variant_id_existing' => $variantId,
                    ]);
                }
                $defaultCount = (int) ($pdo->query('SELECT COUNT(*) FROM footer_designs WHERE is_default = 1')?->fetchColumn() ?: 0);
                if ($defaultCount === 0) {
                    self::runStatement($pdo, "UPDATE footer_designs SET is_activated = 1, is_default = 1 WHERE variant_id = 'footer.standard'");
                }

                $navigationSettings = [];
                if (self::tableExists($pdo, 'navigation_design_settings')) {
                    $row = $pdo->query('SELECT * FROM navigation_design_settings WHERE id = 1 LIMIT 1')?->fetch(PDO::FETCH_ASSOC);
                    $navigationSettings = is_array($row) ? $row : [];
                }
                $navDefaults = [
                    'desktop_alignment' => 'split',
                    'menu_alignment' => 'right',
                    'brand_display' => 'title',
                    'brand_subtitle' => '',
                    'logo_url' => '',
                    'logo_light_url' => '',
                    'logo_dark_url' => '',
                    'logo_alt' => '',
                    'utility_actions_display' => 'auto',
                    'appearance_mode_policy' => 'auto',
                    'density' => 'comfortable',
                    'mobile_mode' => 'takeover',
                    'surface_treatment' => 'bordered',
                    'position_behavior' => 'static',
                ];
                $nav = array_merge($navDefaults, array_intersect_key($navigationSettings, $navDefaults));
                $insertNav = $pdo->prepare(
                    'INSERT INTO navigation_designs (
                        id, name, is_activated, is_default,
                        desktop_alignment, menu_alignment, brand_display, brand_subtitle,
                        logo_url, logo_light_url, logo_dark_url, logo_alt,
                        utility_actions_display, appearance_mode_policy, density, mobile_mode,
                        surface_treatment, position_behavior,
                        created_at, updated_at, created_by_user_id, updated_by_user_id
                     )
                     SELECT :id, :name, 1, 1,
                        :desktop_alignment, :menu_alignment, :brand_display, :brand_subtitle,
                        :logo_url, :logo_light_url, :logo_dark_url, :logo_alt,
                        :utility_actions_display, :appearance_mode_policy, :density, :mobile_mode,
                        :surface_treatment, :position_behavior,
                        :created_at, :updated_at, \'\', \'\'
                     WHERE NOT EXISTS (SELECT 1 FROM navigation_designs WHERE id = :id_existing)'
                );
                $insertNav->execute([
                    ':id' => 'nav_primary',
                    ':id_existing' => 'nav_primary',
                    ':name' => 'Primary',
                    ':desktop_alignment' => (string) $nav['desktop_alignment'],
                    ':menu_alignment' => (string) $nav['menu_alignment'],
                    ':brand_display' => (string) $nav['brand_display'],
                    ':brand_subtitle' => (string) $nav['brand_subtitle'],
                    ':logo_url' => (string) $nav['logo_url'],
                    ':logo_light_url' => (string) $nav['logo_light_url'],
                    ':logo_dark_url' => (string) $nav['logo_dark_url'],
                    ':logo_alt' => (string) $nav['logo_alt'],
                    ':utility_actions_display' => (string) $nav['utility_actions_display'],
                    ':appearance_mode_policy' => (string) $nav['appearance_mode_policy'],
                    ':density' => (string) $nav['density'],
                    ':mobile_mode' => (string) $nav['mobile_mode'],
                    ':surface_treatment' => (string) $nav['surface_treatment'],
                    ':position_behavior' => (string) $nav['position_behavior'],
                    ':created_at' => $now,
                    ':updated_at' => $now,
                ]);

                $pageScopeCapture = [
                    'capturedAt' => $now,
                    'footer_variant_overrides' => [],
                    'navigation_header_overrides' => [],
                ];
                if (self::tableExists($pdo, 'footer_variant_overrides')) {
                    $pageRows = $pdo->query("SELECT * FROM footer_variant_overrides WHERE scope_type = 'page' ORDER BY scope_key ASC")?->fetchAll(PDO::FETCH_ASSOC) ?: [];
                    $pageScopeCapture['footer_variant_overrides'] = array_values(array_filter($pageRows, 'is_array'));
                    $templateRows = $pdo->query("SELECT * FROM footer_variant_overrides WHERE scope_type = 'template' ORDER BY scope_key ASC")?->fetchAll(PDO::FETCH_ASSOC) ?: [];
                    foreach ($templateRows as $row) {
                        if (!is_array($row)) {
                            continue;
                        }
                        $templateId = trim((string) ($row['scope_key'] ?? ''));
                        $variantId = trim((string) ($row['footer_variant'] ?? ''));
                        if ($templateId === '' || $variantId === '' || $variantId === 'inherit') {
                            continue;
                        }
                        $existingAssignment = $pdo->prepare('SELECT id FROM template_chrome_assignments WHERE template_id = :template_id LIMIT 1');
                        $existingAssignment->execute([':template_id' => $templateId]);
                        if ($existingAssignment->fetchColumn() !== false) {
                            $statement = $pdo->prepare(
                                'UPDATE template_chrome_assignments
                                 SET footer_variant_id = :footer_variant_id,
                                     updated_at = :updated_at
                                 WHERE template_id = :template_id'
                            );
                            $statement->execute([
                                ':template_id' => $templateId,
                                ':footer_variant_id' => $variantId,
                                ':updated_at' => $now,
                            ]);
                        } else {
                            $statement = $pdo->prepare(
                                'INSERT INTO template_chrome_assignments (
                                    id, template_id, footer_variant_id, navigation_design_id, created_at, updated_at, updated_by_user_id
                                 ) VALUES (
                                    :id, :template_id, :footer_variant_id, NULL, :created_at, :updated_at, \'\'
                                 )'
                            );
                            $statement->execute([
                                ':id' => 'tca_' . substr(sha1($templateId), 0, 12),
                                ':template_id' => $templateId,
                                ':footer_variant_id' => $variantId,
                                ':created_at' => $now,
                                ':updated_at' => $now,
                            ]);
                        }
                    }
                    self::runStatement($pdo, "DELETE FROM footer_variant_overrides WHERE scope_type IN ('template', 'page')");
                }
                if (self::tableExists($pdo, 'navigation_header_overrides')) {
                    $pageRows = $pdo->query("SELECT * FROM navigation_header_overrides WHERE scope_type = 'page' ORDER BY scope_key ASC")?->fetchAll(PDO::FETCH_ASSOC) ?: [];
                    $pageScopeCapture['navigation_header_overrides'] = array_values(array_filter($pageRows, 'is_array'));
                    $templateRows = $pdo->query("SELECT * FROM navigation_header_overrides WHERE scope_type = 'template' ORDER BY scope_key ASC")?->fetchAll(PDO::FETCH_ASSOC) ?: [];
                    foreach ($templateRows as $row) {
                        if (!is_array($row)) {
                            continue;
                        }
                        $templateId = trim((string) ($row['scope_key'] ?? ''));
                        if ($templateId === '') {
                            continue;
                        }
                        $existingAssignment = $pdo->prepare('SELECT id FROM template_chrome_assignments WHERE template_id = :template_id LIMIT 1');
                        $existingAssignment->execute([':template_id' => $templateId]);
                        if ($existingAssignment->fetchColumn() !== false) {
                            $statement = $pdo->prepare(
                                'UPDATE template_chrome_assignments
                                 SET navigation_design_id = :navigation_design_id,
                                     updated_at = :updated_at
                                 WHERE template_id = :template_id'
                            );
                            $statement->execute([
                                ':template_id' => $templateId,
                                ':navigation_design_id' => 'nav_primary',
                                ':updated_at' => $now,
                            ]);
                        } else {
                            $statement = $pdo->prepare(
                                'INSERT INTO template_chrome_assignments (
                                    id, template_id, footer_variant_id, navigation_design_id, created_at, updated_at, updated_by_user_id
                                 ) VALUES (
                                    :id, :template_id, NULL, :navigation_design_id, :created_at, :updated_at, \'\'
                                 )'
                            );
                            $statement->execute([
                                ':id' => 'tca_' . substr(sha1($templateId), 0, 12),
                                ':template_id' => $templateId,
                                ':navigation_design_id' => 'nav_primary',
                                ':created_at' => $now,
                                ':updated_at' => $now,
                            ]);
                        }
                    }
                    self::runStatement($pdo, "DELETE FROM navigation_header_overrides WHERE scope_type IN ('template', 'page')");
                }

                $storageDir = dirname(__DIR__, 2) . '/storage';
                if (!is_dir($storageDir)) {
                    mkdir($storageDir, 0775, true);
                }
                $capturePath = $storageDir . '/migration-page-scope-overrides-' . gmdate('YmdHis') . '.json';
                file_put_contents($capturePath, json_encode($pageScopeCapture, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
            },
            '025_navigation_appearance_policy_repair' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'appearance_mode_policy', 'VARCHAR(32) NOT NULL DEFAULT \'auto\'');
            },
            '026_navigation_mode_logo_assets' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'logo_light_url', 'VARCHAR(500) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'navigation_design_settings', 'logo_dark_url', 'VARCHAR(500) NOT NULL DEFAULT \'\'');
                self::runStatement(
                    $pdo,
                    "UPDATE navigation_design_settings
                     SET logo_light_url = CASE WHEN logo_light_url = '' THEN logo_url ELSE logo_light_url END,
                         logo_dark_url = CASE WHEN logo_dark_url = '' THEN logo_url ELSE logo_dark_url END
                     WHERE logo_url <> ''"
                );
            },
            '027_application_update_audit' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS application_updates (
                        id VARCHAR(32) PRIMARY KEY,
                        package_hash VARCHAR(128) NOT NULL DEFAULT \'\',
                        package_id VARCHAR(160) NOT NULL DEFAULT \'\',
                        target_version VARCHAR(64) NOT NULL DEFAULT \'\',
                        actor_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        status VARCHAR(32) NOT NULL,
                        manifest_json TEXT NOT NULL,
                        details_json TEXT NOT NULL,
                        error_message TEXT NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
            },
            '028_remote_update_listener_state' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS application_update_settings (
                        id VARCHAR(32) PRIMARY KEY,
                        update_channel VARCHAR(32) NOT NULL DEFAULT \'beta\',
                        auto_check_enabled INTEGER NOT NULL DEFAULT 1,
                        auto_check_interval_hours INTEGER NOT NULL DEFAULT 24,
                        check_on_admin_login INTEGER NOT NULL DEFAULT 1,
                        manual_check_cooldown_seconds INTEGER NOT NULL DEFAULT 60,
                        manifest_url VARCHAR(500) NOT NULL DEFAULT \'https://updates.myroncms.com/updates/v1/beta.json\',
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS application_update_state (
                        id VARCHAR(32) PRIMARY KEY,
                        installed_version VARCHAR(64) NOT NULL DEFAULT \'\',
                        last_checked_at VARCHAR(64) NOT NULL DEFAULT \'\',
                        last_manual_checked_at VARCHAR(64) NOT NULL DEFAULT \'\',
                        last_check_status VARCHAR(64) NOT NULL DEFAULT \'\',
                        last_check_error TEXT NOT NULL,
                        available_update_json TEXT NOT NULL,
                        dismissed_update_version VARCHAR(64) NOT NULL DEFAULT \'\',
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
            },
            '029_template_guidance_storage' => static function (PDO $pdo): void {
                // Adds the guidance_json column to template_definitions to carry
                // per-section architectural guidance authored during template
                // review (design notes, editorial guidelines, audit notes,
                // #derivedCards/#derivedContent flags). After governed ingestion,
                // this column is the canonical runtime source for the Site Type
                // Studio's in-situ guidance render.
                //
                // Schema for guidance_json (documentation):
                //   {
                //     "templatePurpose": "string",
                //     "sections": {
                //       "<sectionKey>:<position>": {
                //         "designNote": "string",
                //         "editorialGuideline": "string",
                //         "fields": ["string", ...],
                //         "derivedCards": true|false,
                //         "derivedContent": true|false,
                //         "auditNotes": ["string", ...]
                //       }
                //     },
                //     "templateAuditNotes": ["string", ...]
                //   }
                self::addColumnIfMissing($pdo, 'template_definitions', 'guidance_json', 'TEXT DEFAULT NULL');
            },
            '030_video_asset_renditions' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS video_assets (
                        id VARCHAR(32) PRIMARY KEY,
                        title VARCHAR(255) NOT NULL,
                        description TEXT NOT NULL,
                        poster_file_id VARCHAR(32) DEFAULT NULL,
                        captions_url VARCHAR(500) NOT NULL DEFAULT \'\',
                        created_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS video_asset_renditions (
                        id VARCHAR(32) PRIMARY KEY,
                        video_asset_id VARCHAR(32) NOT NULL,
                        quality_label VARCHAR(16) NOT NULL,
                        file_id VARCHAR(32) NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        UNIQUE(video_asset_id, quality_label)
                    )' . $tableSuffix
                );
            },
            '030b_media_accessibility_attachments' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS media_accessibility_attachments (
                        id VARCHAR(32) PRIMARY KEY,
                        target_type VARCHAR(32) NOT NULL,
                        target_id VARCHAR(32) NOT NULL,
                        attachment_kind VARCHAR(32) NOT NULL,
                        file_id VARCHAR(32) NOT NULL,
                        language VARCHAR(16) NOT NULL DEFAULT \'en\',
                        label VARCHAR(120) NOT NULL DEFAULT \'\',
                        created_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        UNIQUE(target_type, target_id, attachment_kind)
                    )' . $tableSuffix
                );

                self::addIndexIfMissing($pdo, 'media_accessibility_attachments', 'idx_media_accessibility_attachments_file', ['file_id']);
            },
            '031_drop_orphan_studio_page_defaults' => static function (PDO $pdo): void {
                // STN-012-015: Removes orphan bundle_seed_records rows from the deprecated
                // 'studio-page-default' Studio view. No code in app/modules/* or
                // app/system/* read these rows — they were written by a stub UI in
                // site-type-studio.php that has been removed in favor of the Page Templates
                // editor (which writes to page_template_versions.schema_json.defaultContent).
                //
                // The legacy code wrote two seed_type spellings due to a typo path:
                // 'studio-page-default' (in pageDefaultSeedKey) and 'studio_page_default'
                // (in INSERT statement). Both are cleared.
                if (!self::tableExists($pdo, 'bundle_seed_records')) {
                    return;
                }

                self::runStatement(
                    $pdo,
                    "DELETE FROM bundle_seed_records WHERE seed_type IN ('studio-page-default', 'studio_page_default')"
                );
            },
            '032_design_authoring_overrides' => static function (PDO $pdo) use ($driver, $tableSuffix): void {
                $customCssTextType = $driver === 'mysql' ? 'MEDIUMTEXT' : 'TEXT';
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS custom_css_settings (
                        id VARCHAR(32) PRIMARY KEY,
                        scope_type VARCHAR(32) NOT NULL,
                        scope_key VARCHAR(160) NOT NULL,
                        css_text ' . $customCssTextType . ' NOT NULL,
                        updated_by VARCHAR(32) DEFAULT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        UNIQUE(scope_type, scope_key)
                    )' . $tableSuffix
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS section_variants (
                        id VARCHAR(32) PRIMARY KEY,
                        name VARCHAR(160) NOT NULL,
                        section_key VARCHAR(160) DEFAULT NULL,
                        component_id VARCHAR(160) NOT NULL,
                        variant VARCHAR(160) NOT NULL,
                        design_json TEXT NOT NULL,
                        content_json TEXT NOT NULL,
                        created_by VARCHAR(32) DEFAULT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
                self::addUniqueIndexIfMissing($pdo, 'section_variants', 'idx_section_variants_component_name', ['component_id', 'name']);

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS token_patches (
                        token_name VARCHAR(160) PRIMARY KEY,
                        patch_value TEXT NOT NULL,
                        note TEXT NOT NULL,
                        updated_by VARCHAR(32) DEFAULT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL
                    )' . $tableSuffix
                );
            },
            '033_section_token_override_orphan_scan' => static function (PDO $pdo): void {
                self::scanAndPruneSectionTokenOverrideOrphans($pdo);
            },
            '034_site_settings_logo_identity' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'site_settings', 'site_logo_light_url', 'VARCHAR(500) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'site_settings', 'site_logo_dark_url', 'VARCHAR(500) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'site_settings', 'site_logo_alt', 'VARCHAR(160) NOT NULL DEFAULT \'\'');
                self::backfillSiteLogoIdentityFromNavigation($pdo);
            },
            '035_document_template_assets' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS document_templates (
                        id VARCHAR(32) PRIMARY KEY,
                        file_id VARCHAR(32) NOT NULL,
                        title VARCHAR(255) NOT NULL,
                        description TEXT NOT NULL,
                        is_active INTEGER NOT NULL DEFAULT 1,
                        created_by_user_id VARCHAR(32) NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        UNIQUE(file_id)
                    )' . $tableSuffix
                );

                self::addIndexIfMissing($pdo, 'document_templates', 'idx_document_templates_active', ['is_active']);
            },
            '036_page_category_assignments' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS content_categories (
                        id VARCHAR(32) PRIMARY KEY,
                        title VARCHAR(255) NOT NULL,
                        slug VARCHAR(160) NOT NULL,
                        description TEXT NOT NULL,
                        is_active INTEGER NOT NULL DEFAULT 1,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        UNIQUE(slug)
                    )' . $tableSuffix
                );

                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS page_category_assignments (
                        id VARCHAR(32) PRIMARY KEY,
                        page_id VARCHAR(32) NOT NULL,
                        category_id VARCHAR(32) NOT NULL,
                        created_at TEXT NOT NULL
                    )' . $tableSuffix
                );

                self::addIndexIfMissing($pdo, 'content_categories', 'idx_content_categories_active', ['is_active']);
                self::addUniqueIndexIfMissing($pdo, 'page_category_assignments', 'idx_page_category_assignments_page_category', ['page_id', 'category_id']);
                self::addIndexIfMissing($pdo, 'page_category_assignments', 'idx_page_category_assignments_category', ['category_id']);
            },
            '038_ai_integrations' => static function (PDO $pdo) use ($tableSuffix): void {
                // Timestamps stored as ISO-8601 in VARCHAR(40) so they can be indexed in MySQL.
                self::runStatement($pdo,
                    'CREATE TABLE IF NOT EXISTS ai_integrations (
                        id VARCHAR(32) PRIMARY KEY,
                        label VARCHAR(120) NOT NULL,
                        token_hash VARCHAR(255) NOT NULL,
                        token_prefix VARCHAR(16) NOT NULL,
                        scopes_json TEXT NOT NULL,
                        site_scope VARCHAR(120) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(40) NOT NULL,
                        created_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        last_used_at VARCHAR(40) DEFAULT NULL,
                        revoked_at VARCHAR(40) DEFAULT NULL,
                        request_count INTEGER NOT NULL DEFAULT 0,
                        rate_limit_config TEXT DEFAULT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'ai_integrations', 'idx_ai_integrations_token_prefix', ['token_prefix']);
                self::addIndexIfMissing($pdo, 'ai_integrations', 'idx_ai_integrations_revoked_at', ['revoked_at']);
            },
            '039_ai_audit_log' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement($pdo,
                    'CREATE TABLE IF NOT EXISTS ai_audit_log (
                        id VARCHAR(32) PRIMARY KEY,
                        audit_event_id VARCHAR(40) NOT NULL,
                        integration_id VARCHAR(32) DEFAULT NULL,
                        action_id VARCHAR(40) NOT NULL,
                        action_verb VARCHAR(120) NOT NULL,
                        action_domain VARCHAR(80) NOT NULL,
                        request_uri VARCHAR(255) NOT NULL,
                        request_method VARCHAR(10) NOT NULL,
                        input_sanitised TEXT,
                        output_sanitised TEXT,
                        outcome VARCHAR(20) NOT NULL,
                        error_code VARCHAR(80) DEFAULT NULL,
                        http_status INTEGER NOT NULL,
                        latency_ms INTEGER NOT NULL DEFAULT 0,
                        client_ip VARCHAR(45) NOT NULL DEFAULT \'\',
                        user_agent VARCHAR(255) NOT NULL DEFAULT \'\',
                        license_plan VARCHAR(40) NOT NULL DEFAULT \'\',
                        license_status VARCHAR(40) NOT NULL DEFAULT \'\',
                        entitlement_decision VARCHAR(40) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addColumnIfMissing($pdo, 'ai_audit_log', 'license_plan', 'VARCHAR(40) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'ai_audit_log', 'license_status', 'VARCHAR(40) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'ai_audit_log', 'entitlement_decision', 'VARCHAR(40) NOT NULL DEFAULT \'\'');
                self::addIndexIfMissing($pdo, 'ai_audit_log', 'idx_ai_audit_log_event', ['audit_event_id']);
                self::addIndexIfMissing($pdo, 'ai_audit_log', 'idx_ai_audit_log_integration', ['integration_id']);
                self::addIndexIfMissing($pdo, 'ai_audit_log', 'idx_ai_audit_log_created', ['created_at']);
                self::addIndexIfMissing($pdo, 'ai_audit_log', 'idx_ai_audit_log_verb', ['action_verb']);
                self::addIndexIfMissing($pdo, 'ai_audit_log', 'idx_ai_audit_log_license_plan', ['license_plan']);
            },
            '039a_ai_audit_log_license_context_repair' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'ai_audit_log', 'license_plan', 'VARCHAR(40) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'ai_audit_log', 'license_status', 'VARCHAR(40) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'ai_audit_log', 'entitlement_decision', 'VARCHAR(40) NOT NULL DEFAULT \'\'');
                self::addIndexIfMissing($pdo, 'ai_audit_log', 'idx_ai_audit_log_license_plan', ['license_plan']);
            },
            '040_application_update_audit_json_headroom' => static function (PDO $pdo) use ($driver): void {
                if ($driver !== 'mysql') {
                    return;
                }

                self::modifyColumnIfExists($pdo, 'application_updates', 'manifest_json', 'MEDIUMTEXT NOT NULL');
                self::modifyColumnIfExists($pdo, 'application_updates', 'details_json', 'MEDIUMTEXT NOT NULL');
                self::modifyColumnIfExists($pdo, 'application_updates', 'error_message', 'MEDIUMTEXT NOT NULL');
            },
            '041_file_upload_attribution_repair' => static function (PDO $pdo): void {
                if (!self::tableExists($pdo, 'files') || !self::tableExists($pdo, 'users')) {
                    return;
                }

                $statement = $pdo->query(
                    'SELECT id
                     FROM users
                     WHERE is_active = 1
                       AND (content_access = 1 OR admin_access = 1)
                     ORDER BY admin_access DESC, content_access DESC, created_at ASC, id ASC
                     LIMIT 1'
                );
                $uploaderId = $statement ? $statement->fetchColumn() : false;
                if (!is_string($uploaderId) || trim($uploaderId) === '') {
                    return;
                }

                $repair = $pdo->prepare(
                    'UPDATE files
                     SET uploaded_by_user_id = :uploader_id,
                         updated_at = :updated_at
                     WHERE uploaded_by_user_id = \'\'
                        OR NOT EXISTS (
                            SELECT 1
                            FROM users
                            WHERE users.id = files.uploaded_by_user_id
                        )'
                );
                $repair->execute([
                    ':uploader_id' => trim($uploaderId),
                    ':updated_at' => myronNow(),
                ]);
            },
            '042_custom_css_settings_large_payload_headroom' => static function (PDO $pdo) use ($driver): void {
                if ($driver !== 'mysql') {
                    return;
                }

                self::modifyColumnIfExists($pdo, 'custom_css_settings', 'css_text', 'MEDIUMTEXT NOT NULL');
            },
            '043_custom_section_templates' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS custom_section_templates (
                        section_key VARCHAR(160) PRIMARY KEY,
                        label VARCHAR(255) NOT NULL,
                        description TEXT NOT NULL,
                        jtbd_group VARCHAR(120) NOT NULL,
                        section_type VARCHAR(80) NOT NULL,
                        primary_component_id VARCHAR(160) NOT NULL,
                        variant VARCHAR(160) NOT NULL DEFAULT \'default\',
                        default_content_json TEXT NOT NULL,
                        default_content_editable INTEGER DEFAULT NULL,
                        editing_contract_json TEXT NOT NULL,
                        lifecycle_status VARCHAR(40) NOT NULL DEFAULT \'active\',
                        record_json TEXT NOT NULL,
                        created_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_by_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(40) NOT NULL,
                        updated_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'custom_section_templates', 'idx_custom_section_templates_status', ['lifecycle_status']);
                self::addIndexIfMissing($pdo, 'custom_section_templates', 'idx_custom_section_templates_component', ['primary_component_id']);
            },
            '044_license_management_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS license_activation_state (
                        id INTEGER PRIMARY KEY,
                        activation_id VARCHAR(120) NOT NULL DEFAULT \'\',
                        plan VARCHAR(40) NOT NULL DEFAULT \'core\',
                        status VARCHAR(40) NOT NULL DEFAULT \'missing\',
                        license_fingerprint VARCHAR(128) NOT NULL DEFAULT \'\',
                        license_mask VARCHAR(80) NOT NULL DEFAULT \'\',
                        deployment_key_fingerprint VARCHAR(128) NOT NULL DEFAULT \'\',
                        deployment_key_mask VARCHAR(80) NOT NULL DEFAULT \'\',
                        deployment_group VARCHAR(120) NOT NULL DEFAULT \'\',
                        environment VARCHAR(40) NOT NULL DEFAULT \'production\',
                        entitlement_json TEXT NOT NULL,
                        last_validation_status VARCHAR(40) NOT NULL DEFAULT \'never\',
                        last_validation_reason VARCHAR(120) NOT NULL DEFAULT \'\',
                        last_validated_at VARCHAR(40) DEFAULT NULL,
                        activated_at VARCHAR(40) DEFAULT NULL,
                        updated_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS license_audit_events (
                        id VARCHAR(32) PRIMARY KEY,
                        event_type VARCHAR(80) NOT NULL,
                        outcome VARCHAR(40) NOT NULL,
                        reason_code VARCHAR(120) NOT NULL DEFAULT \'\',
                        plan VARCHAR(40) NOT NULL DEFAULT \'\',
                        status VARCHAR(40) NOT NULL DEFAULT \'\',
                        deployment_group VARCHAR(120) NOT NULL DEFAULT \'\',
                        environment VARCHAR(40) NOT NULL DEFAULT \'\',
                        detail_json TEXT NOT NULL,
                        actor_user_id VARCHAR(32) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'license_audit_events', 'idx_license_audit_created', ['created_at']);
                self::addIndexIfMissing($pdo, 'license_audit_events', 'idx_license_audit_event', ['event_type', 'outcome']);
            },
            '044_license_activation_state_v2' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'license_activation_state', 'sku_model', 'VARCHAR(16) NOT NULL DEFAULT \'v1\'');
                if ($pdo->getAttribute(PDO::ATTR_DRIVER_NAME) === 'mysql') {
                    self::addColumnIfMissing($pdo, 'license_activation_state', 'active_skus_json', 'TEXT NULL');
                    self::runStatement(
                        $pdo,
                        'UPDATE license_activation_state
                         SET active_skus_json = \'[]\'
                         WHERE active_skus_json IS NULL OR active_skus_json = \'\''
                    );
                    self::modifyColumnIfExists($pdo, 'license_activation_state', 'active_skus_json', 'TEXT NOT NULL');
                } else {
                    self::addColumnIfMissing($pdo, 'license_activation_state', 'active_skus_json', 'TEXT NOT NULL DEFAULT \'[]\'');
                }
                self::addColumnIfMissing($pdo, 'license_activation_state', 'api_version_negotiated', 'VARCHAR(8) NOT NULL DEFAULT \'v1\'');
            },
            '045_dashboard_tips_feed_cache' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS dashboard_tip_feed_cache (
                        id INTEGER PRIMARY KEY,
                        feed_url VARCHAR(500) NOT NULL,
                        etag VARCHAR(255) NOT NULL DEFAULT \'\',
                        last_modified VARCHAR(255) NOT NULL DEFAULT \'\',
                        last_checked_at VARCHAR(40) NOT NULL,
                        updated_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS dashboard_tips (
                        id VARCHAR(32) PRIMARY KEY,
                        title VARCHAR(255) NOT NULL,
                        url VARCHAR(500) NOT NULL DEFAULT \'\',
                        summary TEXT NOT NULL,
                        categories_json TEXT NOT NULL,
                        published_at VARCHAR(40) NOT NULL DEFAULT \'\',
                        sort_order INTEGER NOT NULL DEFAULT 0,
                        updated_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'dashboard_tips', 'idx_dashboard_tips_sort', ['sort_order', 'published_at']);
            },
            '046_dashboard_personal_tab' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS dashboard_notes (
                        id VARCHAR(32) PRIMARY KEY,
                        user_id VARCHAR(32) NOT NULL,
                        title VARCHAR(255) NOT NULL,
                        content TEXT NOT NULL,
                        created_at VARCHAR(40) NOT NULL,
                        updated_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS dashboard_links (
                        id VARCHAR(32) PRIMARY KEY,
                        user_id VARCHAR(32) NOT NULL,
                        title VARCHAR(255) NOT NULL,
                        url VARCHAR(500) NOT NULL,
                        link_type VARCHAR(40) NOT NULL,
                        sort_order INTEGER NOT NULL DEFAULT 0,
                        created_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS dashboard_messages (
                        id VARCHAR(32) PRIMARY KEY,
                        from_user_id VARCHAR(32) NOT NULL,
                        to_user_id VARCHAR(32) NOT NULL,
                        body TEXT NOT NULL,
                        read_at VARCHAR(40) DEFAULT NULL,
                        created_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );

                self::addIndexIfMissing($pdo, 'dashboard_notes', 'idx_dashboard_notes_user', ['user_id', 'created_at']);
                self::addIndexIfMissing($pdo, 'dashboard_links', 'idx_dashboard_links_user', ['user_id', 'sort_order']);
                self::addIndexIfMissing($pdo, 'dashboard_messages', 'idx_dashboard_messages_to_user', ['to_user_id']);
                self::addIndexIfMissing($pdo, 'dashboard_messages', 'idx_dashboard_messages_from_user', ['from_user_id', 'created_at']);
            },
            '047_site_snapshot_import_audit' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS site_snapshot_import_audit (
                        id VARCHAR(32) PRIMARY KEY,
                        action VARCHAR(80) NOT NULL,
                        outcome VARCHAR(40) NOT NULL,
                        message TEXT NOT NULL,
                        entity_counts_json TEXT NOT NULL,
                        created_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'site_snapshot_import_audit', 'idx_site_snapshot_import_audit_created', ['created_at']);
                self::addIndexIfMissing($pdo, 'site_snapshot_import_audit', 'idx_site_snapshot_import_audit_outcome', ['outcome']);
            },
            'apo_225_analytics_foundation' => static function (PDO $pdo) use ($tableSuffix, $driver): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS site_pageviews (
                        id VARCHAR(32) PRIMARY KEY,
                        page_path VARCHAR(1000) NOT NULL,
                        page_template_id VARCHAR(32) DEFAULT NULL,
                        referrer_domain VARCHAR(255) NOT NULL DEFAULT \'\',
                        utm_source VARCHAR(100) NOT NULL DEFAULT \'\',
                        utm_medium VARCHAR(100) NOT NULL DEFAULT \'\',
                        utm_campaign VARCHAR(100) NOT NULL DEFAULT \'\',
                        utm_content VARCHAR(100) NOT NULL DEFAULT \'\',
                        utm_term VARCHAR(100) NOT NULL DEFAULT \'\',
                        session_hash VARCHAR(64) NOT NULL,
                        device_type VARCHAR(20) NOT NULL DEFAULT \'desktop\',
                        browser_family VARCHAR(50) NOT NULL DEFAULT \'Other\',
                        os_family VARCHAR(50) NOT NULL DEFAULT \'Other\',
                        country_code CHAR(2) NOT NULL DEFAULT \'\',
                        viewed_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS site_analytics_events (
                        id VARCHAR(32) PRIMARY KEY,
                        event_name VARCHAR(100) NOT NULL,
                        page_path VARCHAR(1000) NOT NULL,
                        session_hash VARCHAR(64) NOT NULL,
                        recorded_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS analytics_daily_salts (
                        salt_date VARCHAR(10) PRIMARY KEY,
                        salt_value VARCHAR(64) NOT NULL,
                        expires_at VARCHAR(40) NOT NULL,
                        created_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'site_pageviews', 'idx_site_pageviews_viewed', ['viewed_at']);
                self::addIndexIfMissing(
                    $pdo,
                    'site_pageviews',
                    'idx_site_pageviews_path_viewed',
                    $driver === 'mysql' ? ['page_path(191)', 'viewed_at'] : ['page_path', 'viewed_at']
                );
                self::addIndexIfMissing($pdo, 'site_pageviews', 'idx_site_pageviews_session_viewed', ['session_hash', 'viewed_at']);
                self::addIndexIfMissing($pdo, 'site_analytics_events', 'idx_site_analytics_events_name_recorded', ['event_name', 'recorded_at']);
                self::addColumnIfMissing($pdo, 'site_settings', 'analytics_enabled', 'INTEGER NOT NULL DEFAULT 1');
                self::addColumnIfMissing($pdo, 'site_settings', 'analytics_retention_days', 'INTEGER NOT NULL DEFAULT 90');
                self::addColumnIfMissing($pdo, 'site_settings', 'analytics_head_script', 'TEXT DEFAULT NULL');
            },
            'apo_223_verification_engine_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS verification_logs (
                        id VARCHAR(32) PRIMARY KEY,
                        form_key VARCHAR(120) NOT NULL,
                        outcome VARCHAR(20) NOT NULL,
                        risk_score INTEGER NOT NULL DEFAULT 0,
                        provider VARCHAR(40) NOT NULL DEFAULT \'native\',
                        reasons_json TEXT NOT NULL,
                        ip_hash VARCHAR(64) NOT NULL DEFAULT \'\',
                        user_agent_hash VARCHAR(64) NOT NULL DEFAULT \'\',
                        created_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'verification_logs', 'idx_verification_logs_created', ['created_at']);
                self::addIndexIfMissing($pdo, 'verification_logs', 'idx_verification_logs_form_created', ['form_key', 'created_at']);
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_enabled', 'INTEGER NOT NULL DEFAULT 1');
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_provider', 'VARCHAR(40) NOT NULL DEFAULT \'native\'');
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_risk_threshold', 'INTEGER NOT NULL DEFAULT 50');
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_challenge_enabled', 'INTEGER NOT NULL DEFAULT 1');
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_rate_limit_per_hour', 'INTEGER NOT NULL DEFAULT 10');
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_turnstile_site_key', 'VARCHAR(255) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_turnstile_secret_key', 'VARCHAR(255) NOT NULL DEFAULT \'\'');
                self::addColumnIfMissing($pdo, 'site_settings', 'verification_fail_open', 'INTEGER NOT NULL DEFAULT 1');
            },
            'apo_244_seo_foundation' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_244_seo_foundation.php';
                return $factory($tableSuffix);
            })(),
            'apo_248_forms_mail_substrate' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_248_forms_mail_substrate.php';
                return $factory($tableSuffix);
            })(),
            'apo_335_blog_comments' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_335_blog_comments.php';
                return $factory($tableSuffix);
            })(),
            'apo_250_bundle_disable_safety_net' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_250_bundle_disable_safety_net.php';
                return $factory($tableSuffix);
            })(),
            'apo_251_ai_seo_remediation' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_251_ai_seo_remediation.php';
                return $factory($tableSuffix);
            })(),
            'apo_226_creative_settings' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_226_creative_settings.php';
                return $factory($tableSuffix);
            })(),
            'apo_232_extension_system_foundation' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS extension_scope_grants (
                        vendor VARCHAR(80) NOT NULL UNIQUE,
                        name VARCHAR(160) NOT NULL DEFAULT \'\',
                        version VARCHAR(80) NOT NULL DEFAULT \'\',
                        enabled INTEGER NOT NULL DEFAULT 1,
                        granted_scopes TEXT NOT NULL,
                        created_at VARCHAR(40) NOT NULL,
                        updated_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS extension_load_errors (
                        vendor VARCHAR(80) NOT NULL,
                        error_message TEXT NOT NULL,
                        occurred_at VARCHAR(40) NOT NULL
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'extension_scope_grants', 'idx_extension_scope_grants_enabled', ['enabled']);
                self::addIndexIfMissing($pdo, 'extension_load_errors', 'idx_extension_load_errors_vendor', ['vendor', 'occurred_at']);
            },
            'apo_247_content_banks_tables' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS mic_tables (
                        id VARCHAR(32) PRIMARY KEY,
                        name VARCHAR(255) NOT NULL,
                        description TEXT NOT NULL,
                        caption TEXT NOT NULL,
                        columns_json TEXT NOT NULL,
                        rows_json TEXT NOT NULL,
                        header_style VARCHAR(32) NOT NULL DEFAULT \'default\',
                        responsive_strategy VARCHAR(32) NOT NULL DEFAULT \'scroll\',
                        created_at VARCHAR(40) NOT NULL,
                        updated_at VARCHAR(40) NOT NULL,
                        created_by VARCHAR(32) NOT NULL DEFAULT \'\',
                        updated_by VARCHAR(32) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );
                self::addIndexIfMissing($pdo, 'mic_tables', 'idx_mic_tables_updated', ['updated_at']);
                self::addIndexIfMissing($pdo, 'mic_tables', 'idx_mic_tables_name', ['name']);
            },
            'apo_249_user_scope_storage' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'users', 'granted_scopes_json', 'TEXT DEFAULT NULL');
            },
            'apo_249_backfill_user_scopes_and_drop_access_flags' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'users', 'granted_scopes_json', 'TEXT DEFAULT NULL');
                self::backfillUserGrantedScopes($pdo);
                self::dropColumnIfExists($pdo, 'users', 'design_access');
                self::dropColumnIfExists($pdo, 'users', 'content_access');
                self::dropColumnIfExists($pdo, 'users', 'admin_access');
            },
            'apo_269_search_documents' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_269_search_documents.php';
                return $factory($tableSuffix);
            })(),
            'apo_269_backfill_search_index' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_269_backfill_search_index.php';
                return $factory($tableSuffix);
            })(),
            'apo_284_public_site_mode' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'site_settings', 'public_mode', 'VARCHAR(20) NOT NULL DEFAULT \'off\'');
            },
            'apo_336_rails_design_surface' => static function (PDO $pdo) use ($tableSuffix): void {
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS rail_designs (
                        id VARCHAR(32) PRIMARY KEY,
                        rail_id VARCHAR(64) NOT NULL UNIQUE,
                        name VARCHAR(160) NOT NULL,
                        context_key VARCHAR(64) NOT NULL DEFAULT \'blog\',
                        is_activated INTEGER NOT NULL DEFAULT 0,
                        is_default INTEGER NOT NULL DEFAULT 0,
                        placement_default VARCHAR(16) NOT NULL DEFAULT \'right\',
                        content_json TEXT NOT NULL,
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        created_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\',
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );
                self::runStatement(
                    $pdo,
                    'CREATE TABLE IF NOT EXISTS template_rail_assignments (
                        id VARCHAR(32) PRIMARY KEY,
                        template_id VARCHAR(255) NOT NULL UNIQUE,
                        rail_id VARCHAR(64) NOT NULL,
                        placement VARCHAR(16) NOT NULL DEFAULT \'right\',
                        created_at TEXT NOT NULL,
                        updated_at TEXT NOT NULL,
                        updated_by_user_id VARCHAR(64) NOT NULL DEFAULT \'\'
                    )' . $tableSuffix
                );

                $now = myronNow();
                $content = json_encode([
                    'summary' => 'Core Blog sidebar rail for subscription, discovery, and related-content widgets.',
                    'slots' => [
                        ['key' => 'subscribe', 'label' => 'Newsletter signup', 'componentId' => 'newsletter-signup'],
                        ['key' => 'discover', 'label' => 'Categories and discovery', 'componentId' => 'stack'],
                        ['key' => 'related', 'label' => 'Related posts', 'componentId' => 'blog-teaser'],
                    ],
                ], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
                $insertRail = $pdo->prepare(
                    'INSERT INTO rail_designs (
                        id, rail_id, name, context_key, is_activated, is_default, placement_default,
                        content_json, created_at, updated_at, created_by_user_id, updated_by_user_id
                     )
                     SELECT :id, :rail_id, :name, :context_key, 1, 1, :placement_default,
                            :content_json, :created_at, :updated_at, \'\', \'\'
                     WHERE NOT EXISTS (SELECT 1 FROM rail_designs WHERE rail_id = :rail_id_existing)'
                );
                $insertRail->execute([
                    ':id' => 'rd_blog_standard',
                    ':rail_id' => 'rail.blog.standard',
                    ':rail_id_existing' => 'rail.blog.standard',
                    ':name' => 'Standard Blog Rail',
                    ':context_key' => 'blog',
                    ':placement_default' => 'right',
                    ':content_json' => $content,
                    ':created_at' => $now,
                    ':updated_at' => $now,
                ]);

                $insertAssignment = $pdo->prepare(
                    'INSERT INTO template_rail_assignments (
                        id, template_id, rail_id, placement, created_at, updated_at, updated_by_user_id
                     )
                     SELECT :id, :template_id, :rail_id, :placement, :created_at, :updated_at, \'\'
                     WHERE NOT EXISTS (SELECT 1 FROM template_rail_assignments WHERE template_id = :template_id_existing)'
                );
                foreach (['template.blog-index.blog.v1', 'template.post-single.blog.v1'] as $templateId) {
                    $insertAssignment->execute([
                        ':id' => 'tra_' . substr(sha1($templateId), 0, 12),
                        ':template_id' => $templateId,
                        ':template_id_existing' => $templateId,
                        ':rail_id' => 'rail.blog.standard',
                        ':placement' => 'right',
                        ':created_at' => $now,
                        ':updated_at' => $now,
                    ]);
                }
            },
            'apo_353_mcp_oauth_connectors' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_353_mcp_oauth_connectors.php';
                return $factory($tableSuffix);
            })(),
            'apo_418_page_navigation_visibility' => static function (PDO $pdo): void {
                self::addColumnIfMissing($pdo, 'pages', 'include_in_nav', 'INTEGER NOT NULL DEFAULT 1');
                self::backfillPageNavigationVisibility($pdo);
            },
            'apo_438_static_media_rendition_backfill' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_438_static_media_rendition_backfill.php';
                return $factory($tableSuffix);
            })(),
            'apo_455_page_roles' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_455_page_roles.php';
                return $factory($tableSuffix);
            })(),
            'apo_458_creative_nav_params' => (static function () use ($tableSuffix): callable {
                $factory = require dirname(__DIR__) . '/install/migrations/apo_458_creative_nav_params.php';
                return $factory($tableSuffix);
            })(),
        ];

        if (function_exists('myronModuleRegistry')) {
            $migrations = array_replace($migrations, myronModuleRegistry()->migrations());
        }

        return $migrations;
    }

    private static function backfillSiteLogoIdentityFromNavigation(PDO $pdo): void
    {
        if (!self::tableExists($pdo, 'site_settings')) {
            return;
        }

        $logo = [
            'logo_light_url' => '',
            'logo_dark_url' => '',
            'logo_alt' => '',
        ];

        if (self::tableExists($pdo, 'navigation_designs')) {
            $row = $pdo->query(
                'SELECT logo_url, logo_light_url, logo_dark_url, logo_alt
                 FROM navigation_designs
                 WHERE is_default = 1
                 LIMIT 1'
            )?->fetch(PDO::FETCH_ASSOC);
            if (is_array($row)) {
                $fallback = trim((string) ($row['logo_url'] ?? ''));
                $logo = [
                    'logo_light_url' => trim((string) ($row['logo_light_url'] ?? '')) ?: $fallback,
                    'logo_dark_url' => trim((string) ($row['logo_dark_url'] ?? '')) ?: $fallback,
                    'logo_alt' => trim((string) ($row['logo_alt'] ?? '')),
                ];
            }
        }

        if ($logo['logo_light_url'] === '' && self::tableExists($pdo, 'navigation_design_settings')) {
            $row = $pdo->query(
                'SELECT logo_url, logo_light_url, logo_dark_url, logo_alt
                 FROM navigation_design_settings
                 WHERE id = 1
                 LIMIT 1'
            )?->fetch(PDO::FETCH_ASSOC);
            if (is_array($row)) {
                $fallback = trim((string) ($row['logo_url'] ?? ''));
                $logo = [
                    'logo_light_url' => trim((string) ($row['logo_light_url'] ?? '')) ?: $fallback,
                    'logo_dark_url' => trim((string) ($row['logo_dark_url'] ?? '')) ?: $fallback,
                    'logo_alt' => trim((string) ($row['logo_alt'] ?? '')),
                ];
            }
        }

        if ($logo['logo_light_url'] === '' && $logo['logo_dark_url'] === '' && $logo['logo_alt'] === '') {
            return;
        }

        $statement = $pdo->prepare(
            'UPDATE site_settings
             SET site_logo_light_url = CASE WHEN COALESCE(site_logo_light_url, \'\') = \'\' THEN :site_logo_light_url ELSE site_logo_light_url END,
                 site_logo_dark_url = CASE WHEN COALESCE(site_logo_dark_url, \'\') = \'\' THEN :site_logo_dark_url ELSE site_logo_dark_url END,
                 site_logo_alt = CASE WHEN COALESCE(site_logo_alt, \'\') = \'\' THEN :site_logo_alt ELSE site_logo_alt END
             WHERE id = 1'
        );
        $statement->execute([
            ':site_logo_light_url' => $logo['logo_light_url'],
            ':site_logo_dark_url' => $logo['logo_dark_url'],
            ':site_logo_alt' => $logo['logo_alt'],
        ]);
    }

    private static function scanAndPruneSectionTokenOverrideOrphans(PDO $pdo): void
    {
        if (!self::tableExists($pdo, 'page_template_versions') || !self::tableExists($pdo, 'component_registry')) {
            return;
        }

        $editableTokensByComponent = self::editableTokensByComponent($pdo);
        if ($editableTokensByComponent === []) {
            return;
        }

        $select = $pdo->query('SELECT id, template_id, schema_json FROM page_template_versions');
        $rows = $select ? $select->fetchAll() : [];
        $report = [];
        $update = $pdo->prepare('UPDATE page_template_versions SET schema_json = :schema_json WHERE id = :id');

        foreach (is_array($rows) ? $rows : [] as $row) {
            if (!is_array($row)) {
                continue;
            }
            $schema = json_decode((string) ($row['schema_json'] ?? ''), true);
            if (!is_array($schema) || !is_array($schema['sections'] ?? null)) {
                continue;
            }

            $changed = false;
            foreach ($schema['sections'] as $position => $section) {
                if (!is_array($section) || !is_array($section['tokenOverrides'] ?? null)) {
                    continue;
                }

                $componentId = trim((string) ($section['componentId'] ?? ''));
                $allowed = $editableTokensByComponent[$componentId] ?? [];
                if ($allowed === []) {
                    continue;
                }

                foreach (array_keys($section['tokenOverrides']) as $tokenName) {
                    $tokenName = trim((string) $tokenName);
                    if ($tokenName === '' || isset($allowed[$tokenName])) {
                        continue;
                    }

                    unset($schema['sections'][$position]['tokenOverrides'][$tokenName]);
                    $changed = true;
                    $report[] = [
                        'templateVersionId' => (string) ($row['id'] ?? ''),
                        'templateId' => (string) ($row['template_id'] ?? ''),
                        'sectionPosition' => (int) $position,
                        'componentId' => $componentId,
                        'tokenName' => $tokenName,
                    ];
                }
            }

            if ($changed) {
                $update->execute([
                    ':schema_json' => json_encode($schema, JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES),
                    ':id' => (string) ($row['id'] ?? ''),
                ]);
            }
        }

        if ($report !== []) {
            self::writeSectionTokenOverrideOrphanReport($report);
        }
    }

    /**
     * @return array<string, array<string, true>>
     */
    private static function editableTokensByComponent(PDO $pdo): array
    {
        $statement = $pdo->query('SELECT component_id, record_json FROM component_registry');
        $rows = $statement ? $statement->fetchAll() : [];
        $tokensByComponent = [];

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

            $componentId = trim((string) ($row['component_id'] ?? ''));
            $record = json_decode((string) ($row['record_json'] ?? ''), true);
            if ($componentId === '' || !is_array($record)) {
                continue;
            }

            $tokenRows = is_array($record['editableTokens'] ?? null)
                ? $record['editableTokens']
                : array_map(
                    static fn (string $cssVar): array => ['cssVar' => $cssVar],
                    array_values(array_filter(array_map('strval', is_array($record['tokenDependencies'] ?? null) ? $record['tokenDependencies'] : [])))
                );
            $allowed = [];
            foreach ($tokenRows as $tokenRow) {
                if (!is_array($tokenRow)) {
                    continue;
                }
                $cssVar = trim((string) ($tokenRow['cssVar'] ?? $tokenRow['token'] ?? ''));
                if ($cssVar !== '' && preg_match('/^--myron-[a-z0-9_-]+$/', $cssVar)) {
                    $allowed[$cssVar] = true;
                }
            }

            $tokensByComponent[$componentId] = $allowed;
        }

        return $tokensByComponent;
    }

    /**
     * @param list<array<string, mixed>> $report
     */
    private static function writeSectionTokenOverrideOrphanReport(array $report): void
    {
        $storageDirectory = dirname(__DIR__, 2) . '/storage';
        if (!is_dir($storageDirectory) && !mkdir($storageDirectory, 0775, true) && !is_dir($storageDirectory)) {
            return;
        }

        $path = $storageDirectory . '/section-token-override-orphans-' . gmdate('Ymd-His') . '.json';
        file_put_contents($path, json_encode([
            'generatedAt' => myronNow(),
            'count' => count($report),
            'orphans' => $report,
        ], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
    }

    /**
     * @param list<string> $columns
     */
    public static function tableHasColumns(PDO $pdo, string $table, array $columns): bool
    {
        if (!self::tableExists($pdo, $table)) {
            return false;
        }

        $driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        $existing = [];

        if ($driver === 'sqlite') {
            $statement = $pdo->query('PRAGMA table_info(' . $table . ')');
            $rows = $statement ? $statement->fetchAll() : [];
            foreach ($rows as $row) {
                if (isset($row['name'])) {
                    $existing[] = (string) $row['name'];
                }
            }
        } else {
            $statement = $pdo->query('DESCRIBE ' . $table);
            $rows = $statement ? $statement->fetchAll() : [];
            foreach ($rows as $row) {
                if (isset($row['Field'])) {
                    $existing[] = (string) $row['Field'];
                }
            }
        }

        foreach ($columns as $column) {
            if (!in_array($column, $existing, true)) {
                return false;
            }
        }

        return true;
    }

    public static function tableExists(PDO $pdo, string $table): bool
    {
        $driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);

        if ($driver === 'sqlite') {
            $statement = $pdo->prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = :table");
            $statement->execute([':table' => $table]);
            return $statement->fetchColumn() !== false;
        }

        $statement = $pdo->prepare('SHOW TABLES LIKE :table');
        $statement->execute([':table' => $table]);
        return $statement->fetchColumn() !== false;
    }

    private static function renameTableIfNeeded(PDO $pdo, string $driver, string $table, string $legacyTable): void
    {
        if (!self::tableExists($pdo, $table) || self::tableExists($pdo, $legacyTable)) {
            return;
        }

        if ($driver === 'sqlite') {
            self::runStatement($pdo, sprintf('ALTER TABLE %s RENAME TO %s', $table, $legacyTable));
            return;
        }

        self::runStatement($pdo, sprintf('RENAME TABLE %s TO %s', $table, $legacyTable));
    }

    public static function addColumnIfMissing(PDO $pdo, string $table, string $column, string $definition): void
    {
        if (!self::tableExists($pdo, $table)) {
            return;
        }

        if (self::tableHasColumns($pdo, $table, [$column])) {
            return;
        }

        self::runStatement($pdo, sprintf('ALTER TABLE %s ADD COLUMN %s %s', $table, $column, $definition));
    }

    public static function dropColumnIfExists(PDO $pdo, string $table, string $column): void
    {
        if (!self::tableHasColumns($pdo, $table, [$column])) {
            return;
        }

        self::runStatement($pdo, sprintf('ALTER TABLE %s DROP COLUMN %s', $table, $column));
    }

    public static function modifyColumnIfExists(PDO $pdo, string $table, string $column, string $definition): void
    {
        if (!self::tableHasColumns($pdo, $table, [$column])) {
            return;
        }

        self::runStatement($pdo, sprintf('ALTER TABLE %s MODIFY COLUMN %s %s', $table, $column, $definition));
    }

    /**
     * @param list<string> $columns
     */
    public static function addIndexIfMissing(PDO $pdo, string $table, string $indexName, array $columns): void
    {
        if (self::indexExists($pdo, $table, $indexName)) {
            return;
        }

        try {
            self::runStatement(
                $pdo,
                sprintf('CREATE INDEX %s ON %s(%s)', $indexName, $table, implode(', ', $columns))
            );
        } catch (PDOException $exception) {
            if (self::isDuplicateIndexException($exception)) {
                return;
            }

            throw $exception;
        }
    }

    /**
     * @param list<string> $columns
     */
    public static function addUniqueIndexIfMissing(PDO $pdo, string $table, string $indexName, array $columns): void
    {
        if (self::indexExists($pdo, $table, $indexName)) {
            return;
        }

        try {
            self::runStatement(
                $pdo,
                sprintf('CREATE UNIQUE INDEX %s ON %s(%s)', $indexName, $table, implode(', ', $columns))
            );
        } catch (PDOException $exception) {
            if (self::isDuplicateIndexException($exception)) {
                return;
            }

            throw $exception;
        }
    }

    public static function runStatement(PDO $pdo, string $sql): void
    {
        $statement = $pdo->prepare($sql);
        if ($statement === false) {
            throw new RuntimeException('Failed to prepare migration SQL statement.');
        }

        $statement->execute();
    }

    private static function indexExists(PDO $pdo, string $table, string $indexName): bool
    {
        $driver = $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);

        if ($driver === 'sqlite') {
            $statement = $pdo->query('PRAGMA index_list(' . $table . ')');
            $rows = $statement ? $statement->fetchAll() : [];

            foreach ($rows as $row) {
                if (is_array($row) && (string) ($row['name'] ?? '') === $indexName) {
                    return true;
                }
            }

            return false;
        }

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

    private static function isDuplicateIndexException(PDOException $exception): bool
    {
        $errorInfo = $exception->errorInfo;
        $driverCode = isset($errorInfo[1]) ? (int) $errorInfo[1] : 0;

        return $driverCode === 1061 || str_contains($exception->getMessage(), 'Duplicate key name');
    }

    private static function backfillUsernames(PDO $pdo): void
    {
        if (!self::tableHasColumns($pdo, 'users', ['username'])) {
            return;
        }

        $statement = $pdo->query('SELECT id, email, username FROM users ORDER BY created_at ASC, id ASC');
        $rows = $statement ? $statement->fetchAll() : [];
        $taken = [];

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $username = trim((string) ($row['username'] ?? ''));
            if ($username !== '') {
                $taken[$username] = true;
            }
        }

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $current = trim((string) ($row['username'] ?? ''));
            if ($current !== '') {
                continue;
            }

            $email = trim((string) ($row['email'] ?? ''));
            $base = myronNormalizeUsername(explode('@', myronNormalizeEmail($email), 2)[0] ?? 'user');
            $candidate = $base;
            $suffix = 2;

            while (isset($taken[$candidate])) {
                $candidate = $base . '-' . $suffix;
                $suffix++;
            }

            $taken[$candidate] = true;
            $update = $pdo->prepare('UPDATE users SET username = :username WHERE id = :id');
            $update->execute([
                ':username' => $candidate,
                ':id' => (string) $row['id'],
            ]);
        }
    }

    private static function backfillIdentityAccess(PDO $pdo): void
    {
        if (!self::tableHasColumns($pdo, 'users', [
            'design_access',
            'content_access',
            'admin_access',
            'is_active',
            'email_verified_at',
        ])) {
            return;
        }

        $statement = $pdo->query(
            'SELECT users.id, users.created_at, roles.role_key
             FROM users
             LEFT JOIN roles ON roles.id = users.role_id
             ORDER BY users.created_at ASC, users.id ASC'
        );
        $rows = $statement ? $statement->fetchAll() : [];

        $update = $pdo->prepare(
            'UPDATE users
             SET design_access = :design_access,
                 content_access = :content_access,
                 admin_access = :admin_access,
                 is_active = :is_active,
                 email_verified_at = COALESCE(NULLIF(email_verified_at, \'\'), :email_verified_at)
             WHERE id = :id'
        );

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $roleKey = trim((string) ($row['role_key'] ?? ''));
            $designAccess = 0;
            $contentAccess = 0;
            $adminAccess = 0;

            if ($roleKey === 'administrator') {
                $designAccess = 1;
                $contentAccess = 1;
                $adminAccess = 1;
            } elseif ($roleKey === 'editor') {
                $contentAccess = 1;
            }

            $update->execute([
                ':design_access' => $designAccess,
                ':content_access' => $contentAccess,
                ':admin_access' => $adminAccess,
                ':is_active' => 1,
                ':email_verified_at' => (string) ($row['created_at'] ?? myronNow()),
                ':id' => (string) $row['id'],
            ]);
        }
    }

    private static function backfillUserGrantedScopes(PDO $pdo): void
    {
        if (!self::tableHasColumns($pdo, 'users', ['granted_scopes_json'])) {
            return;
        }

        $catalog = [
            'viewer' => ['design.read', 'content.read', 'administration.read'],
            'designer' => ['design.read', 'design.write', 'content.read', 'administration.read'],
            'editor' => ['design.read', 'content.read', 'content.write', 'administration.read'],
            'administrator' => [
                'design.read',
                'design.write',
                'content.read',
                'content.write',
                'administration.read',
                'administration.write',
                'administration.destructive',
            ],
        ];

        $statement = $pdo->query(
            'SELECT users.id, users.granted_scopes_json, roles.role_key
             FROM users
             LEFT JOIN roles ON roles.id = users.role_id
             ORDER BY users.created_at ASC, users.id ASC'
        );
        $rows = $statement ? $statement->fetchAll() : [];
        $update = $pdo->prepare(
            'UPDATE users
             SET granted_scopes_json = :granted_scopes_json,
                 updated_at = :updated_at
             WHERE id = :id'
        );

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $current = trim((string) ($row['granted_scopes_json'] ?? ''));
            if ($current !== '') {
                continue;
            }
            $roleKey = trim((string) ($row['role_key'] ?? 'viewer'));
            $scopes = $catalog[$roleKey] ?? $catalog['viewer'];
            $update->execute([
                ':granted_scopes_json' => json_encode($scopes, JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES),
                ':updated_at' => myronNow(),
                ':id' => (string) $row['id'],
            ]);
        }
    }

    private static function backfillNullableIdentityProfileFields(PDO $pdo): void
    {
        if (!self::tableHasColumns($pdo, 'users', ['bio', 'avatar_url', 'facebook_url', 'x_url', 'pinterest_url', 'linkedin_url', 'youtube_url', 'instagram_url', 'tiktok_url'])) {
            return;
        }

        self::runStatement($pdo, "UPDATE users SET bio = '' WHERE bio IS NULL");
        self::runStatement($pdo, "UPDATE users SET avatar_url = '' WHERE avatar_url IS NULL");
        self::runStatement($pdo, "UPDATE users SET facebook_url = '' WHERE facebook_url IS NULL");
        self::runStatement($pdo, "UPDATE users SET x_url = '' WHERE x_url IS NULL");
        self::runStatement($pdo, "UPDATE users SET pinterest_url = '' WHERE pinterest_url IS NULL");
        self::runStatement($pdo, "UPDATE users SET linkedin_url = '' WHERE linkedin_url IS NULL");
        self::runStatement($pdo, "UPDATE users SET youtube_url = '' WHERE youtube_url IS NULL");
        self::runStatement($pdo, "UPDATE users SET instagram_url = '' WHERE instagram_url IS NULL");
        self::runStatement($pdo, "UPDATE users SET tiktok_url = '' WHERE tiktok_url IS NULL");
    }

    private static function backfillPageNavigationVisibility(PDO $pdo): void
    {
        if (!self::tableHasColumns($pdo, 'pages', ['include_in_nav', 'template_id'])) {
            return;
        }

        $statement = $pdo->query(
            'SELECT
                pages.id,
                pages.template_id,
                pages.source_template_id,
                page_templates.template_key,
                page_templates.source_template_id AS template_source_template_id
             FROM pages
             LEFT JOIN page_templates ON page_templates.id = pages.template_id
             ORDER BY pages.created_at ASC, pages.id ASC'
        );
        $rows = $statement ? $statement->fetchAll() : [];
        $update = $pdo->prepare('UPDATE pages SET include_in_nav = :include_in_nav WHERE id = :id');

        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $templateClass = self::templateClassForNavigationBackfill([
                (string) ($row['source_template_id'] ?? ''),
                (string) ($row['template_source_template_id'] ?? ''),
                (string) ($row['template_key'] ?? ''),
                (string) ($row['template_id'] ?? ''),
            ]);

            $update->execute([
                ':include_in_nav' => $templateClass === 'post-single' ? 0 : 1,
                ':id' => (string) $row['id'],
            ]);
        }
    }

    /**
     * @param list<string> $candidates
     */
    private static function templateClassForNavigationBackfill(array $candidates): string
    {
        foreach ($candidates as $candidate) {
            $class = self::templateClassForTemplateIdentifier($candidate);
            if ($class !== '') {
                return $class;
            }
        }

        return '';
    }

    private static function templateClassForTemplateIdentifier(string $templateId): string
    {
        $normalized = trim($templateId);
        if ($normalized === '') {
            return '';
        }

        $customMarkerPosition = strpos($normalized, '::custom::');
        if ($customMarkerPosition !== false) {
            $normalized = substr($normalized, 0, $customMarkerPosition);
        }

        $parts = explode('.', $normalized);
        if (($parts[0] ?? '') === 'template') {
            array_shift($parts);
        }

        if ($parts !== [] && preg_match('/^v[0-9]+$/', (string) end($parts)) === 1) {
            array_pop($parts);
        }

        if (count($parts) > 1) {
            array_pop($parts);
        }

        $class = implode('.', $parts);
        return $class !== '' ? $class : $normalized;
    }

    private static function backfillMenuPlacements(PDO $pdo): void
    {
        if (!self::tableHasColumns($pdo, 'menu_placements', ['placement_key', 'label', 'menu_id'])) {
            return;
        }

        $now = myronNow();
        $insert = $pdo->prepare(
            'INSERT INTO menu_placements (placement_key, label, menu_id, created_at, updated_at)
             SELECT :placement_key, :label, :menu_id, :created_at, :updated_at
             WHERE NOT EXISTS (
                SELECT 1
                FROM menu_placements
                WHERE placement_key = :placement_key_existing
             )'
        );

        $legacyMenus = $pdo->query(
            'SELECT id, placement
             FROM menus
             WHERE placement IS NOT NULL
               AND TRIM(placement) <> \'\'
             ORDER BY created_at ASC, id ASC'
        );
        $legacyRows = $legacyMenus ? $legacyMenus->fetchAll() : [];

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

            $placementKey = trim((string) ($row['placement'] ?? ''));
            if ($placementKey === '' || isset($placements[$placementKey])) {
                continue;
            }

            $placements[$placementKey] = (string) ($row['id'] ?? '');
        }

        foreach ([
            'primary' => 'Primary Navigation',
            'footer' => 'Footer Navigation',
        ] as $placementKey => $label) {
            $insert->execute([
                ':placement_key' => $placementKey,
                ':label' => $label,
                ':menu_id' => $placements[$placementKey] ?? null,
                ':created_at' => $now,
                ':updated_at' => $now,
                ':placement_key_existing' => $placementKey,
            ]);
        }
    }
}
