CREATE TABLE IF NOT EXISTS honeypot_hits (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip_hash VARCHAR(64),
    trap_route VARCHAR(255),
    user_agent_enc TEXT,
    device_fingerprint_enc TEXT,
    request_method VARCHAR(10) DEFAULT 'GET',
    referer_enc TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_hp_ip (ip_hash),
    INDEX idx_hp_created (created_at),
    INDEX idx_hp_route (trap_route)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS visitor_sessions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    session_hash VARCHAR(64) UNIQUE,
    ip_hash VARCHAR(64),
    device_fingerprint_enc TEXT,
    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    page_views INT DEFAULT 0,
    routes_visited TEXT,
    source_views INT DEFAULT 0,
    source_view_time_ms INT DEFAULT 0,
    total_time_ms INT DEFAULT 0,
    ip_changes INT DEFAULT 0,
    has_account TINYINT DEFAULT 0,
    user_id INT NULL,
    visits_before_signup INT DEFAULT 0,
    ad_source VARCHAR(100),
    avg_ad_time_ms INT DEFAULT 0,
    is_returning TINYINT DEFAULT 0,
    visit_count INT DEFAULT 1,
    risk_score INT DEFAULT 0,
    INDEX idx_vs_ip (ip_hash),
    INDEX idx_vs_last (last_seen),
    INDEX idx_vs_risk (risk_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
