-- Schema for antibot.sqlite

-- Table for storing combined User-Agent and IP blacklists
CREATE TABLE IF NOT EXISTS blacklists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT NOT NULL, -- 'ua_redirect', 'ua_generic', 'ip'
    value TEXT NOT NULL UNIQUE,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Index for faster lookups
CREATE INDEX IF NOT EXISTS idx_blacklists_type_value ON blacklists (type, value);

-- Table for caching IP geolocation data
CREATE TABLE IF NOT EXISTS geolocation_cache (
    ip TEXT PRIMARY KEY,
    country_code TEXT,
    country_name TEXT,
    city TEXT,
    isp TEXT,
    cached_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table for logging every request for rate limiting
CREATE TABLE IF NOT EXISTS request_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ip TEXT NOT NULL,
    request_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Index for faster rate limit counting
CREATE INDEX IF NOT EXISTS idx_request_log_ip_time ON request_log (ip, request_time);

-- Table for logging bot detections and human passes
CREATE TABLE IF NOT EXISTS traffic_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    log_type TEXT NOT NULL, -- 'bot_detected', 'human_passed', 'captcha_failed', 'redirected'
    ip TEXT NOT NULL,
    user_agent TEXT,
    reason TEXT,
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Index for traffic logs
CREATE INDEX IF NOT EXISTS idx_traffic_log_time ON traffic_log (log_time);
CREATE INDEX IF NOT EXISTS idx_traffic_log_type_ip ON traffic_log (log_type, ip);
