<?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 IN ('table','view') 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;
        };

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

        $triggerExists = static function (string $trigger) use ($pdo, $driver): bool {
            if ($driver !== 'sqlite') {
                return false;
            }
            $statement = $pdo->query("SELECT name FROM sqlite_master WHERE type='trigger' AND name=" . $pdo->quote($trigger));
            return $statement !== false && $statement->fetchColumn() !== false;
        };

        $run = static function (string $sql) use ($pdo): void {
            $pdo->exec($sql);
        };

        // 1. Base table (driver-uniform shape; only ENGINE/CHARSET suffix differs).
        $run(
            'CREATE TABLE IF NOT EXISTS search_documents (
                id VARCHAR(32) PRIMARY KEY,
                entity_type VARCHAR(32) NOT NULL,
                entity_id VARCHAR(64) NOT NULL,
                bundle_id VARCHAR(160) DEFAULT NULL,
                site_type VARCHAR(32) DEFAULT NULL,
                title TEXT NOT NULL,
                path TEXT NOT NULL,
                url TEXT NOT NULL,
                body_text TEXT NOT NULL,
                meta_text TEXT NOT NULL,
                language VARCHAR(16) DEFAULT NULL,
                published_at TEXT DEFAULT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )' . $tableSuffix
        );

        // 2. Uniqueness on the natural key (entity_type, entity_id).
        if (!$indexExists('search_documents', 'uq_search_documents_entity')) {
            $run('CREATE UNIQUE INDEX uq_search_documents_entity ON search_documents (entity_type, entity_id)');
        }

        // 3. Secondary lookup for entity_type filters and bundle filters.
        if (!$indexExists('search_documents', 'idx_search_documents_type_bundle')) {
            $run('CREATE INDEX idx_search_documents_type_bundle ON search_documents (entity_type, bundle_id)');
        }

        if ($driver === 'mysql') {
            // 4a. MySQL FULLTEXT over title + body + meta. Skip if already present.
            $check = $pdo->prepare(
                'SELECT COUNT(*)
                 FROM information_schema.statistics
                 WHERE table_schema = DATABASE()
                   AND table_name = :table
                   AND index_name = :index_name'
            );
            $check->execute([':table' => 'search_documents', ':index_name' => 'ft_search_documents_text']);
            if ((int) $check->fetchColumn() === 0) {
                $run('CREATE FULLTEXT INDEX ft_search_documents_text ON search_documents (title, body_text, meta_text)');
            }
            return;
        }

        // 4b. SQLite FTS5 companion table + triggers.
        if ($driver !== 'sqlite') {
            return;
        }

        if (!$tableExists('search_documents_fts')) {
            $run(
                "CREATE VIRTUAL TABLE search_documents_fts USING fts5(
                    title,
                    body_text,
                    meta_text,
                    content='search_documents',
                    content_rowid='rowid',
                    tokenize='unicode61'
                )"
            );
        }

        if (!$triggerExists('search_documents_ai')) {
            $run(
                "CREATE TRIGGER search_documents_ai AFTER INSERT ON search_documents BEGIN
                    INSERT INTO search_documents_fts(rowid, title, body_text, meta_text)
                    VALUES (new.rowid, new.title, new.body_text, new.meta_text);
                END"
            );
        }

        if (!$triggerExists('search_documents_ad')) {
            $run(
                "CREATE TRIGGER search_documents_ad AFTER DELETE ON search_documents BEGIN
                    INSERT INTO search_documents_fts(search_documents_fts, rowid, title, body_text, meta_text)
                    VALUES('delete', old.rowid, old.title, old.body_text, old.meta_text);
                END"
            );
        }

        if (!$triggerExists('search_documents_au')) {
            $run(
                "CREATE TRIGGER search_documents_au AFTER UPDATE ON search_documents BEGIN
                    INSERT INTO search_documents_fts(search_documents_fts, rowid, title, body_text, meta_text)
                    VALUES('delete', old.rowid, old.title, old.body_text, old.meta_text);
                    INSERT INTO search_documents_fts(rowid, title, body_text, meta_text)
                    VALUES (new.rowid, new.title, new.body_text, new.meta_text);
                END"
            );
        }
    };
};
