<?php

declare(strict_types=1);

return static function (string $tableSuffix): callable {
    return static function (PDO $pdo) use ($tableSuffix): void {
        $driver = (string) $pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        $tableExists = static function (string $table) use ($pdo, $driver): bool {
            if ($driver === 'sqlite') {
                $statement = $pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name=" . $pdo->quote($table));
                return $statement !== false && $statement->fetchColumn() !== false;
            }

            $statement = $pdo->prepare('SHOW TABLES LIKE :table');
            $statement->execute([':table' => $table]);
            return $statement->fetchColumn() !== false;
        };
        $hasColumn = static function (string $table, string $column) use ($pdo, $driver): bool {
            if ($driver === 'sqlite') {
                $statement = $pdo->query('PRAGMA table_info(' . $table . ')');
                foreach (($statement ? $statement->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                    if (strtolower((string) ($row['name'] ?? '')) === strtolower($column)) {
                        return true;
                    }
                }
                return false;
            }

            $statement = $pdo->prepare(
                'SELECT COUNT(*)
                 FROM information_schema.columns
                 WHERE table_schema = DATABASE()
                   AND table_name = :table
                   AND column_name = :column'
            );
            $statement->execute([':table' => $table, ':column' => $column]);
            return (int) $statement->fetchColumn() > 0;
        };
        $indexExists = static function (string $table, string $index) use ($pdo, $driver): bool {
            if ($driver === 'sqlite') {
                $statement = $pdo->query('PRAGMA index_list(' . $table . ')');
                foreach (($statement ? $statement->fetchAll(PDO::FETCH_ASSOC) : []) as $row) {
                    if ((string) ($row['name'] ?? '') === $index) {
                        return true;
                    }
                }
                return false;
            }

            $statement = $pdo->prepare(
                'SELECT COUNT(*)
                 FROM information_schema.statistics
                 WHERE table_schema = DATABASE()
                   AND table_name = :table
                   AND index_name = :index_name'
            );
            $statement->execute([':table' => $table, ':index_name' => $index]);
            return (int) $statement->fetchColumn() > 0;
        };
        $run = static function (string $sql) use ($pdo): void {
            $pdo->exec($sql);
        };
        $columnIsBlobOrText = static function (string $table, string $column) use ($pdo, $driver): bool {
            if ($driver !== 'mysql') {
                return false;
            }
            $statement = $pdo->prepare(
                'SELECT DATA_TYPE
                 FROM information_schema.columns
                 WHERE table_schema = DATABASE()
                   AND table_name = :table
                   AND column_name = :column'
            );
            $statement->execute([':table' => $table, ':column' => $column]);
            $type = strtolower((string) $statement->fetchColumn());
            return in_array($type, ['text', 'tinytext', 'mediumtext', 'longtext', 'blob', 'tinyblob', 'mediumblob', 'longblob'], true);
        };
        $addIndex = static function (string $table, string $index, array $columns) use ($run, $indexExists, $columnIsBlobOrText): void {
            if ($indexExists($table, $index)) {
                return;
            }
            // MySQL requires a key-length prefix when indexing TEXT/BLOB columns. A 191-
            // char prefix fits within InnoDB's utf8mb4 single-column index size limit
            // (767 bytes / 4 bytes-per-char). VARCHAR columns can be indexed without a
            // prefix and a prefix longer than the column is itself an error.
            $colSpec = implode(', ', array_map(
                static fn (string $col): string => $columnIsBlobOrText($table, $col) ? $col . '(191)' : $col,
                $columns
            ));
            $run('CREATE INDEX ' . $index . ' ON ' . $table . '(' . $colSpec . ')');
        };

        if ($tableExists('pages') && !$hasColumn('pages', 'disabled_by_bundle_removal_at')) {
            $run('ALTER TABLE pages ADD COLUMN disabled_by_bundle_removal_at TEXT DEFAULT NULL');
        }

        if ($tableExists('pages')) {
            $addIndex('pages', 'idx_pages_disabled_by_bundle_removal_at', ['disabled_by_bundle_removal_at']);
        }

        $run(
            'CREATE TABLE IF NOT EXISTS disabled_page_redirects (
                slug VARCHAR(255) PRIMARY KEY,
                bundle_id VARCHAR(160) NOT NULL,
                fallback_url VARCHAR(2048) NOT NULL,
                audit_id VARCHAR(32) NOT NULL,
                created_at TEXT NOT NULL
            )' . $tableSuffix
        );
        $addIndex('disabled_page_redirects', 'idx_disabled_page_redirects_bundle_id', ['bundle_id']);
    };
};
