<?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);
        $run = static function (string $sql) use ($pdo): void {
            try {
                $pdo->exec($sql);
            } catch (PDOException $exception) {
                $message = strtolower($exception->getMessage());
                if (!str_contains($message, 'duplicate column') && !str_contains($message, 'already exists') && !str_contains($message, 'duplicate key name')) {
                    throw $exception;
                }
            }
        };
        $hasColumn = static function (string $table, string $column) use ($pdo, $driver): bool {
            if ($driver === 'sqlite') {
                $rows = $pdo->query('PRAGMA table_info(' . $table . ')')->fetchAll();
                foreach ($rows as $row) {
                    if (is_array($row) && (string) ($row['name'] ?? '') === $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;
        };
        $addColumn = static function (string $table, string $column, string $definition) use ($hasColumn, $run): void {
            if (!$hasColumn($table, $column)) {
                $run('ALTER TABLE ' . $table . ' ADD COLUMN ' . $column . ' ' . $definition);
            }
        };
        $timestampColumn = $driver === 'mysql' ? 'VARCHAR(40)' : 'TEXT';

        $run('CREATE TABLE IF NOT EXISTS notification_emails (
            id VARCHAR(32) PRIMARY KEY,
            address VARCHAR(255) NOT NULL UNIQUE,
            label VARCHAR(255) NOT NULL,
            verified_at ' . $timestampColumn . ' DEFAULT NULL,
            verification_token_hash VARCHAR(255) DEFAULT NULL,
            verification_sent_at ' . $timestampColumn . ' DEFAULT NULL,
            created_by VARCHAR(32) DEFAULT NULL,
            created_at ' . $timestampColumn . ' NOT NULL
        )' . $tableSuffix);

        $addColumn('site_settings', 'default_notification_email_id', 'VARCHAR(32) DEFAULT NULL');

        $run('CREATE TABLE IF NOT EXISTS form_definitions (
            id VARCHAR(32) PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT NOT NULL,
            submit_button_label VARCHAR(120) NOT NULL DEFAULT \'Submit\',
            success_message TEXT NOT NULL,
            fields_json TEXT NOT NULL,
            success_strategy VARCHAR(32) NOT NULL DEFAULT \'inline\',
            success_redirect_url TEXT DEFAULT NULL,
            notification_email_id VARCHAR(32) DEFAULT NULL,
            embed_mode VARCHAR(32) NOT NULL DEFAULT \'inline\',
            cta_label VARCHAR(120) DEFAULT NULL,
            completion_routing_json TEXT NOT NULL,
            honeypot_field_name VARCHAR(64) NOT NULL,
            created_by VARCHAR(32) DEFAULT NULL,
            created_at ' . $timestampColumn . ' NOT NULL,
            updated_by VARCHAR(32) DEFAULT NULL,
            updated_at ' . $timestampColumn . ' NOT NULL
        )' . $tableSuffix);

        $run('CREATE TABLE IF NOT EXISTS form_submissions (
            id VARCHAR(32) PRIMARY KEY,
            form_id VARCHAR(32) NOT NULL,
            submitted_at ' . $timestampColumn . ' NOT NULL,
            fields_json TEXT NOT NULL,
            source_url TEXT DEFAULT NULL,
            ip_hash VARCHAR(64) NOT NULL,
            user_agent VARCHAR(500) DEFAULT NULL
        )' . $tableSuffix);

        $run('CREATE TABLE IF NOT EXISTS form_notification_failures (
            id VARCHAR(32) PRIMARY KEY,
            submission_id VARCHAR(32) NOT NULL,
            form_id VARCHAR(32) NOT NULL,
            attempted_at ' . $timestampColumn . ' NOT NULL,
            recipient_email VARCHAR(255) DEFAULT NULL,
            failure_reason TEXT NOT NULL,
            retry_count INTEGER NOT NULL DEFAULT 0
        )' . $tableSuffix);

        if ($driver === 'mysql') {
            $run('ALTER TABLE notification_emails MODIFY verified_at VARCHAR(40) DEFAULT NULL');
            $run('ALTER TABLE notification_emails MODIFY verification_sent_at VARCHAR(40) DEFAULT NULL');
            $run('ALTER TABLE notification_emails MODIFY created_at VARCHAR(40) NOT NULL');
            $run('ALTER TABLE form_definitions MODIFY created_at VARCHAR(40) NOT NULL');
            $run('ALTER TABLE form_definitions MODIFY updated_at VARCHAR(40) NOT NULL');
            $run('ALTER TABLE form_submissions MODIFY submitted_at VARCHAR(40) NOT NULL');
            $run('ALTER TABLE form_notification_failures MODIFY attempted_at VARCHAR(40) NOT NULL');
        }

        foreach ([
            'CREATE INDEX idx_form_submissions_form_id ON form_submissions(form_id)',
            'CREATE INDEX idx_form_submissions_submitted_at ON form_submissions(submitted_at)',
            'CREATE INDEX idx_form_notification_failures_form_id ON form_notification_failures(form_id)',
            'CREATE INDEX idx_form_notification_failures_attempted_at ON form_notification_failures(attempted_at)',
        ] as $sql) {
            $run($sql);
        }
    };
};
