-- The outcome of a single verification: the computed score, classification,
-- the decision taken, and a denormalized summary for fast dashboard reads.
CREATE TABLE IF NOT EXISTS `verification_results` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `session_token`  CHAR(64)        NULL,
    `user_id`        BIGINT UNSIGNED NULL,
    `ip`             VARCHAR(45)     NOT NULL,
    `country`        CHAR(2)         NULL,
    `user_agent`     VARCHAR(1024)   NULL,
    `browser`        VARCHAR(60)     NULL,
    `browser_version` VARCHAR(40)    NULL,
    `os`             VARCHAR(60)     NULL,
    `device_type`    VARCHAR(20)     NULL,          -- desktop/mobile/tablet/bot
    `fingerprint_hash` CHAR(64)      NULL,
    `score`          TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `classification` ENUM('human','suspicious','bot') NOT NULL DEFAULT 'bot',
    `decision`       VARCHAR(30)     NOT NULL,       -- redirect_primary/redirect_alternate/block/allow
    `redirect_url`   VARCHAR(2048)   NULL,
    `js_enabled`     TINYINT(1)      NULL,
    `cookies_enabled` TINYINT(1)     NULL,
    `captcha_result` VARCHAR(20)     NULL,
    `referrer_type`  VARCHAR(20)     NULL,
    `breakdown`      JSON            NULL,           -- per-module summary
    `created_at`     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_vr_user` (`user_id`),
    KEY `idx_vr_class` (`classification`),
    KEY `idx_vr_created` (`created_at`),
    KEY `idx_vr_ip` (`ip`),
    KEY `idx_vr_fp` (`fingerprint_hash`),
    CONSTRAINT `fk_vr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;