-- Rate Limiting ve Brute Force Protection Tabloları

-- Rate Limits Tablosu
CREATE TABLE IF NOT EXISTS `rate_limits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `action` varchar(50) NOT NULL,
  `attempts` int(11) DEFAULT 1,
  `first_attempt` timestamp DEFAULT CURRENT_TIMESTAMP,
  `last_attempt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `blocked_until` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_action` (`ip_address`, `action`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_action` (`action`),
  KEY `idx_blocked_until` (`blocked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Account Lockouts Tablosu
CREATE TABLE IF NOT EXISTS `account_lockouts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `failed_attempts` int(11) DEFAULT 1,
  `first_failed_attempt` timestamp DEFAULT CURRENT_TIMESTAMP,
  `last_failed_attempt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `locked_until` timestamp NULL DEFAULT NULL,
  `lockout_reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_ip` (`email`, `ip_address`),
  KEY `idx_email` (`email`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_locked_until` (`locked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- IP Blacklist Tablosu
CREATE TABLE IF NOT EXISTS `ip_blacklist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `reason` varchar(255) NOT NULL,
  `blocked_until` timestamp NULL DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip_address` (`ip_address`),
  KEY `idx_blocked_until` (`blocked_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Security Logs Tablosu
CREATE TABLE IF NOT EXISTS `security_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` varchar(50) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text,
  `email` varchar(255) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `details` text,
  `severity` enum('low','medium','high','critical') DEFAULT 'medium',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_email` (`email`),
  KEY `idx_admin_id` (`admin_id`),
  KEY `idx_severity` (`severity`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin Logs Tablosu (Mevcut admins tablosuna ek alanlar)
ALTER TABLE `admins` 
ADD COLUMN IF NOT EXISTS `last_logout` timestamp NULL DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `logout_ip` varchar(45) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `session_duration` int(11) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS `failed_login_attempts` int(11) DEFAULT 0,
ADD COLUMN IF NOT EXISTS `last_failed_login` timestamp NULL DEFAULT NULL;

-- Eski kayıtları temizleme (30 günden eski)
-- Bu sorguları manuel olarak çalıştırabilirsiniz
-- DELETE FROM rate_limits WHERE last_attempt < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- DELETE FROM account_lockouts WHERE last_failed_attempt < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- DELETE FROM security_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);