-- MADDE 3: Security Logging ve Monitoring SQL
-- Güvenlik izleme ve raporlama sistemi

-- 1. Güvenlik dashboard tablosu (opsiyonel)
CREATE TABLE IF NOT EXISTS `security_dashboard` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `metric_name` varchar(100) NOT NULL,
  `metric_value` text NOT NULL,
  `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `metric_name` (`metric_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Güvenlik istatistikleri tablosu
CREATE TABLE IF NOT EXISTS `security_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `total_logins` int(11) DEFAULT 0,
  `failed_logins` int(11) DEFAULT 0,
  `blocked_ips` int(11) DEFAULT 0,
  `locked_accounts` int(11) DEFAULT 0,
  `security_events` int(11) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Güvenlik alarmları tablosu
CREATE TABLE IF NOT EXISTS `security_alerts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_type` varchar(50) NOT NULL,
  `severity` enum('low','medium','high','critical') DEFAULT 'medium',
  `message` text NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_alert_type` (`alert_type`),
  KEY `idx_severity` (`severity`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Güvenlik raporları tablosu
CREATE TABLE IF NOT EXISTS `security_reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_type` varchar(50) NOT NULL,
  `report_data` longtext NOT NULL,
  `generated_by` int(11) DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_report_type` (`report_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Başlangıç verileri
INSERT INTO `security_dashboard` (`metric_name`, `metric_value`) VALUES
('total_security_events_today', '0'),
('failed_login_attempts_today', '0'),
('blocked_ips_today', '0'),
('active_alerts', '0'),
('last_security_scan', ''),
('system_security_score', '100');

-- İndeksler ekle
ALTER TABLE `security_logs` 
ADD INDEX `idx_ip_address` (`ip_address`),
ADD INDEX `idx_created_at_date` (DATE(`created_at`));

ALTER TABLE `ip_limits` 
ADD INDEX `idx_blocked_until` (`blocked_until`),
ADD INDEX `idx_last_attempt` (`last_attempt`); 