CREATE TABLE IF NOT EXISTS admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(64) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
 setting_key VARCHAR(64) PRIMARY KEY,
 setting_value TEXT NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS banned_ips (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 ip VARCHAR(45) NOT NULL UNIQUE,
 reason VARCHAR(255) NOT NULL DEFAULT 'Panel üzerinden yasaklandı',
 source VARCHAR(32) NOT NULL DEFAULT 'panel',
 created_by VARCHAR(64) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_banned_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS exempt_ips (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 ip VARCHAR(45) NOT NULL UNIQUE,
 reason VARCHAR(255) NOT NULL DEFAULT 'Panel üzerinden muaf edildi',
 created_by VARCHAR(64) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS countries (
 code CHAR(2) PRIMARY KEY,
 mode ENUM('blacklist','whitelist') NOT NULL DEFAULT 'blacklist',
 note VARCHAR(255) NULL,
 updated_by VARCHAR(64) NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS connections (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 nick VARCHAR(64) NOT NULL,
 ip VARCHAR(45) NOT NULL,
 port INT NULL,
 identhost VARCHAR(255) NULL,
 country_code CHAR(2) NULL,
 country_name VARCHAR(128) NULL,
 city VARCHAR(128) NULL,
 isp VARCHAR(255) NULL,
 asn VARCHAR(64) NULL,
 proxy ENUM('yes','no','unknown') NOT NULL DEFAULT 'unknown',
 risk VARCHAR(32) NULL,
 event_type ENUM('GIRIS','CIKIS') NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_conn_ip(ip), INDEX idx_conn_nick(nick), INDEX idx_conn_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS security_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_type VARCHAR(64) NOT NULL,
 nick VARCHAR(64) NULL,
 ip VARCHAR(45) NULL,
 country_code CHAR(2) NULL,
 reason VARCHAR(255) NULL,
 source VARCHAR(32) NOT NULL DEFAULT 'bot',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_sec_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS daily_stats (
 stat_date DATE PRIMARY KEY,
 giris INT UNSIGNED NOT NULL DEFAULT 0,
 cikis INT UNSIGNED NOT NULL DEFAULT 0,
 proxy INT UNSIGNED NOT NULL DEFAULT 0,
 vpn INT UNSIGNED NOT NULL DEFAULT 0,
 zline INT UNSIGNED NOT NULL DEFAULT 0,
 kill_count INT UNSIGNED NOT NULL DEFAULT 0,
 yasak_ip INT UNSIGNED NOT NULL DEFAULT 0,
 yasak_ulke INT UNSIGNED NOT NULL DEFAULT 0,
 muaf INT UNSIGNED NOT NULL DEFAULT 0,
 http_hata INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 endpoint VARCHAR(128) NOT NULL,
 method VARCHAR(10) NOT NULL,
 status_code SMALLINT NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings(setting_key,setting_value) VALUES
('country_mode','blacklist'),('proxy_ban','1'),('zline_sure','0'),('debug','1')
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);