-- GeoIP Service Database Schema

CREATE TABLE api_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_key VARCHAR(64) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) DEFAULT NULL,
    status ENUM('active','disabled') DEFAULT 'active',
    daily_limit INT DEFAULT 10000,
    monthly_limit INT DEFAULT 300000,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ip_cache (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45) NOT NULL UNIQUE,
    country VARCHAR(100),
    region VARCHAR(100),
    city VARCHAR(100),
    latitude DECIMAL(9,6),
    longitude DECIMAL(9,6),
    timezone VARCHAR(100),
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip (ip)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE rate_limits (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    api_key_id INT NOT NULL,
    ip VARCHAR(45) NOT NULL,
    window_start DATETIME NOT NULL,
    window_type ENUM('minute','hour','day') NOT NULL,
    request_count INT NOT NULL DEFAULT 0,
    INDEX idx_rate_limits (api_key_id, ip, window_type, window_start),
    CONSTRAINT fk_rate_limits_api_key FOREIGN KEY (api_key_id)
        REFERENCES api_keys(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE api_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    api_key_id INT,
    client_ip VARCHAR(45),
    query_ip VARCHAR(45),
    country VARCHAR(100),
    region VARCHAR(100),
    city VARCHAR(100),
    user_agent VARCHAR(255),
    referer VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_api_logs_key_time (api_key_id, created_at),
    CONSTRAINT fk_api_logs_api_key FOREIGN KEY (api_key_id)
        REFERENCES api_keys(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
