-- A verification session is created when the /verify page loads. It holds the
-- server-issued token + challenge answer and is later resolved by /api/verify.
CREATE TABLE IF NOT EXISTS `verification_sessions` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `token`         CHAR(64)        NOT NULL,
    `user_id`       BIGINT UNSIGNED NULL,
    `ip`            VARCHAR(45)     NOT NULL,
    `user_agent`    VARCHAR(1024)   NULL,
    `challenge`     VARCHAR(40)     NULL,          -- provider used (puzzle/turnstile/...)
    `challenge_answer` VARCHAR(255) NULL,          -- server-side expected answer (puzzle)
    `status`        ENUM('pending','completed','expired') NOT NULL DEFAULT 'pending',
    `issued_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at`    DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_vs_token` (`token`),
    KEY `idx_vs_status` (`status`),
    KEY `idx_vs_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;