-- Anti-Bot Database Setup
-- Run this in phpMyAdmin or MySQL CLI

CREATE DATABASE IF NOT EXISTS antibot_db;
USE antibot_db;

-- Visitors table - logs every visit with device & language info
CREATE TABLE IF NOT EXISTS visitors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45) NOT NULL,
    ip_range VARCHAR(45) NOT NULL,
    country VARCHAR(10) DEFAULT '??',
    user_agent TEXT,
    device_type ENUM('mobile','tablet','desktop','bot','unknown') DEFAULT 'unknown',
    browser VARCHAR(100),
    os VARCHAR(100),
    browser_language VARCHAR(10),
    accept_language VARCHAR(255),
    hostname VARCHAR(255),
    referrer VARCHAR(500),
    request_uri VARCHAR(500),
    request_method VARCHAR(10),
    score INT DEFAULT 0,
    status ENUM('HUMAN','SUSPICIOUS','BLOCKED_BOT','BLOCKED_BLACKLIST','MANUAL_BLOCK') DEFAULT 'HUMAN',
    reasons TEXT,
    challenge_cookie VARCHAR(64),
    session_id VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip (ip),
    INDEX idx_range (ip_range),
    INDEX idx_status (status),
    INDEX idx_device (device_type),
    INDEX idx_language (browser_language),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Blacklist table - blocked IPs and ranges
CREATE TABLE IF NOT EXISTS blacklist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('IP','RANGE') DEFAULT 'IP',
    value VARCHAR(45) NOT NULL,
    trigger_ip VARCHAR(45),
    reason VARCHAR(50),
    score INT DEFAULT 0,
    hostname VARCHAR(255),
    user_agent TEXT,
    blocked_by ENUM('AUTO','MANUAL') DEFAULT 'AUTO',
    blocked_by_ip VARCHAR(45),
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_value (value),
    INDEX idx_expires (expires_at),
    INDEX idx_type (type)
) ENGINE=InnoDB;

-- Strikes table - tracks suspicious behavior before blocking
CREATE TABLE IF NOT EXISTS strikes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45) NOT NULL,
    session_hash VARCHAR(64),
    strike_count INT DEFAULT 1,
    scores JSON,
    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_ip_session (ip, session_hash),
    INDEX idx_last_seen (last_seen)
) ENGINE=InnoDB;

-- Detection logs table
CREATE TABLE IF NOT EXISTS detection_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(20),
    ip VARCHAR(45),
    details TEXT,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at),
    INDEX idx_action (action)
) ENGINE=InnoDB;

-- Admin actions table - who did what
CREATE TABLE IF NOT EXISTS admin_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(50),
    target_ip VARCHAR(45),
    admin_ip VARCHAR(45),
    admin_ua TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Config table - system settings
CREATE TABLE IF NOT EXISTS config (
    config_key VARCHAR(50) PRIMARY KEY,
    config_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Bot signatures table - detectable bot patterns
CREATE TABLE IF NOT EXISTS bot_signatures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    signature VARCHAR(100) NOT NULL UNIQUE,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default configuration
INSERT INTO config (config_key, config_value) VALUES
('block_threshold', '70'),
('strike_threshold', '40'),
('max_strikes', '3'),
('block_duration', '86400'),
('range_prefix', '24'),
('auto_range', '1')
ON DUPLICATE KEY UPDATE config_value = VALUES(config_value);

-- Insert default bot signatures
INSERT IGNORE INTO bot_signatures (signature) VALUES
('bot'),('crawler'),('spider'),('scraper'),('curl'),('wget'),
('python-requests'),('httpclient'),('java/'),('libwww'),
('go-http'),('node-fetch'),('axios'),('postman'),('insomnia'),
('headless'),('puppeteer'),('selenium'),('playwright'),('phantom'),
('scrapy'),('ahrefs'),('semrush'),('mj12bot'),('dotbot'),
('bingbot'),('googlebot'),('yandex'),('baidu'),
('facebookexternalhit'),('twitterbot'),('slackbot'),
('discordbot'),('telegrambot'),('whatsapp'),('linkedinbot'),
('pinterest'),('applebot'),('chrome-lighthouse'),
('pingdom'),('gtmetrix'),('pagespeed'),('cloudflare'),
('cache.google.com'),('googleusercontent'),('googleweblight');