CREATE DATABASE IF NOT EXISTS cloaker_server CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cloaker_server;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE settings (
    `key` VARCHAR(50) PRIMARY KEY,
    `value` TEXT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE visits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    page_id VARCHAR(100) DEFAULT '',
    ip VARCHAR(45) NOT NULL DEFAULT '',
    user_agent TEXT,
    referrer TEXT,
    request_uri TEXT,
    result ENUM('passed','blocked') NOT NULL DEFAULT 'blocked',
    reason VARCHAR(500) DEFAULT '',
    source VARCHAR(20) DEFAULT 'other',
    country VARCHAR(100) DEFAULT '',
    city VARCHAR(100) DEFAULT '',
    device_type VARCHAR(20) DEFAULT 'desktop',
    os VARCHAR(50) DEFAULT '',
    browser VARCHAR(50) DEFAULT '',
    raw_headers JSON,
    cookie_id VARCHAR(64) DEFAULT '',
    query_params TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_result (result),
    INDEX idx_page_id (page_id),
    INDEX idx_ip (ip),
    INDEX idx_source (source),
    INDEX idx_created (created_at),
    INDEX idx_cookie (cookie_id)
) ENGINE=InnoDB;

CREATE TABLE bot_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type ENUM('ua_contains','ua_regex','ip_exact','ip_range','rdns_contains') NOT NULL,
    value VARCHAR(500) NOT NULL,
    description VARCHAR(255) DEFAULT '',
    is_active TINYINT(1) DEFAULT 1,
    hit_count INT UNSIGNED DEFAULT 0,
    last_hit_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type_active (type, is_active)
) ENGINE=InnoDB;

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cookie_id VARCHAR(64) DEFAULT '',
    page_id VARCHAR(100) DEFAULT '',
    event_type VARCHAR(50) NOT NULL,
    event_data JSON,
    ip VARCHAR(45) DEFAULT '',
    url VARCHAR(2000) DEFAULT '',
    referrer VARCHAR(2000) DEFAULT '',
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cookie (cookie_id),
    INDEX idx_type (event_type),
    INDEX idx_page (page_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Varsayilan ayarlar
INSERT INTO settings (`key`, `value`) VALUES
('site_title', 'Cloaker Panel');
