<?php

declare(strict_types=1);

return static function (string $tableSuffix): callable {
    return static function (PDO $pdo) use ($tableSuffix): void {
        $run = static function (string $sql) use ($pdo): void {
            try {
                $pdo->exec($sql);
            } catch (PDOException $exception) {
                $message = strtolower($exception->getMessage());
                if (!str_contains($message, 'already exists') && !str_contains($message, 'duplicate key name')) {
                    throw $exception;
                }
            }
        };

        $run('CREATE TABLE IF NOT EXISTS mcp_oauth_clients (
            id VARCHAR(80) PRIMARY KEY,
            client_name VARCHAR(255) NOT NULL DEFAULT \'\',
            client_secret_hash TEXT NULL,
            redirect_uris_json TEXT NOT NULL,
            scopes_json TEXT NOT NULL,
            created_at VARCHAR(40) NOT NULL,
            revoked_at VARCHAR(40) DEFAULT NULL
        )' . $tableSuffix);

        $run('CREATE TABLE IF NOT EXISTS mcp_oauth_authorization_codes (
            id VARCHAR(80) PRIMARY KEY,
            code_prefix VARCHAR(32) NOT NULL,
            code_hash TEXT NOT NULL,
            client_id VARCHAR(80) NOT NULL,
            redirect_uri TEXT NOT NULL,
            resource TEXT NOT NULL,
            scopes_json TEXT NOT NULL,
            code_challenge VARCHAR(255) NOT NULL DEFAULT \'\',
            code_challenge_method VARCHAR(16) NOT NULL DEFAULT \'\',
            created_by_user_id VARCHAR(80) NOT NULL DEFAULT \'\',
            created_at VARCHAR(40) NOT NULL,
            expires_at VARCHAR(40) NOT NULL,
            consumed_at VARCHAR(40) DEFAULT NULL
        )' . $tableSuffix);

        $run('CREATE TABLE IF NOT EXISTS mcp_oauth_access_tokens (
            id VARCHAR(80) PRIMARY KEY,
            token_prefix VARCHAR(32) NOT NULL,
            token_hash TEXT NOT NULL,
            client_id VARCHAR(80) NOT NULL,
            integration_id VARCHAR(80) NOT NULL,
            resource TEXT NOT NULL,
            scopes_json TEXT NOT NULL,
            created_at VARCHAR(40) NOT NULL,
            expires_at VARCHAR(40) NOT NULL,
            revoked_at VARCHAR(40) DEFAULT NULL,
            last_used_at VARCHAR(40) DEFAULT NULL
        )' . $tableSuffix);

        $run('CREATE TABLE IF NOT EXISTS mcp_oauth_refresh_tokens (
            id VARCHAR(80) PRIMARY KEY,
            token_prefix VARCHAR(32) NOT NULL,
            token_hash TEXT NOT NULL,
            client_id VARCHAR(80) NOT NULL,
            integration_id VARCHAR(80) NOT NULL,
            resource TEXT NOT NULL,
            scopes_json TEXT NOT NULL,
            created_at VARCHAR(40) NOT NULL,
            expires_at VARCHAR(40) NOT NULL,
            revoked_at VARCHAR(40) DEFAULT NULL
        )' . $tableSuffix);

        foreach ([
            'CREATE INDEX idx_mcp_oauth_codes_prefix ON mcp_oauth_authorization_codes(code_prefix)',
            'CREATE INDEX idx_mcp_oauth_access_prefix ON mcp_oauth_access_tokens(token_prefix)',
            'CREATE INDEX idx_mcp_oauth_refresh_prefix ON mcp_oauth_refresh_tokens(token_prefix)',
            'CREATE INDEX idx_mcp_oauth_access_integration ON mcp_oauth_access_tokens(integration_id)',
            'CREATE INDEX idx_mcp_oauth_refresh_integration ON mcp_oauth_refresh_tokens(integration_id)',
        ] as $sql) {
            $run($sql);
        }
    };
};
