<?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);
        $timestampColumn = $driver === 'mysql' ? 'VARCHAR(40)' : 'TEXT';
        $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 blog_comment_settings (
            id INTEGER PRIMARY KEY,
            comments_enabled INTEGER NOT NULL DEFAULT 1,
            default_post_comments_enabled INTEGER NOT NULL DEFAULT 1,
            moderation_required INTEGER NOT NULL DEFAULT 1,
            notification_email_id VARCHAR(32) DEFAULT NULL,
            updated_by VARCHAR(32) DEFAULT NULL,
            updated_at ' . $timestampColumn . ' NOT NULL
        )' . $tableSuffix);

        $run('CREATE TABLE IF NOT EXISTS blog_comments (
            id VARCHAR(32) PRIMARY KEY,
            page_id VARCHAR(32) NOT NULL,
            page_version_id VARCHAR(32) DEFAULT NULL,
            author_name VARCHAR(160) NOT NULL,
            author_email VARCHAR(255) NOT NULL,
            body TEXT NOT NULL,
            status VARCHAR(24) NOT NULL DEFAULT \'pending\',
            submitted_at ' . $timestampColumn . ' NOT NULL,
            moderated_at ' . $timestampColumn . ' DEFAULT NULL,
            moderated_by VARCHAR(32) DEFAULT NULL,
            source_url TEXT DEFAULT NULL,
            ip_hash VARCHAR(64) NOT NULL DEFAULT \'\',
            user_agent VARCHAR(500) DEFAULT NULL,
            created_at ' . $timestampColumn . ' NOT NULL,
            updated_at ' . $timestampColumn . ' NOT NULL
        )' . $tableSuffix);

        $run('CREATE TABLE IF NOT EXISTS blog_comment_moderation_events (
            id VARCHAR(32) PRIMARY KEY,
            comment_id VARCHAR(32) NOT NULL,
            action VARCHAR(32) NOT NULL,
            from_status VARCHAR(24) NOT NULL,
            to_status VARCHAR(24) NOT NULL,
            reason TEXT NOT NULL,
            actor_id VARCHAR(32) DEFAULT NULL,
            created_at ' . $timestampColumn . ' NOT NULL
        )' . $tableSuffix);

        $insertSettings = $pdo->prepare(
            'INSERT INTO blog_comment_settings (
                id, comments_enabled, default_post_comments_enabled, moderation_required,
                notification_email_id, updated_by, updated_at
             )
             SELECT 1, 1, 1, 1, NULL, NULL, :updated_at
             WHERE NOT EXISTS (SELECT 1 FROM blog_comment_settings WHERE id = 1)'
        );
        $insertSettings->execute([':updated_at' => myronNow()]);

        foreach ([
            'CREATE INDEX idx_blog_comments_page_status ON blog_comments(page_id, status)',
            'CREATE INDEX idx_blog_comments_status_submitted ON blog_comments(status, submitted_at)',
            'CREATE INDEX idx_blog_comments_page_submitted ON blog_comments(page_id, submitted_at)',
            'CREATE INDEX idx_blog_comment_events_comment ON blog_comment_moderation_events(comment_id, created_at)',
        ] as $sql) {
            $run($sql);
        }
    };
};
