CREATE TABLE IF NOT EXISTS `proxies` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `provider` VARCHAR(255) NULL,
  `ip` VARCHAR(255) NOT NULL,
  `port` INT NOT NULL,
  `username` VARCHAR(255) NULL,
  `password` VARCHAR(255) NULL,
  `country` VARCHAR(100) NULL,
  `status` ENUM('active', 'inactive', 'banned', 'testing') DEFAULT 'testing',
  `last_used` TIMESTAMP NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `ip_port` (`ip`, `port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `keywords` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `keyword` TEXT NOT NULL,
  `target_url` TEXT NOT NULL,
  `language` VARCHAR(50) NULL,
  `country` VARCHAR(50) NULL,
  `active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_agents` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_agent` TEXT NOT NULL,
  `device_type` VARCHAR(50) NULL COMMENT 'Desktop, Mobile, Tablet',
  `os` VARCHAR(100) NULL,
  `browser` VARCHAR(100) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tasks` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `keyword_id` INT NULL,
  `proxy_id` INT NULL,
  `user_agent_id` INT NULL,
  `status` ENUM('pending', 'running', 'completed', 'failed', 'captcha') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `started_at` TIMESTAMP NULL,
  `finished_at` TIMESTAMP NULL,
  FOREIGN KEY (`keyword_id`) REFERENCES `keywords`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`proxy_id`) REFERENCES `proxies`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_agent_id`) REFERENCES `user_agents`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `logs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `task_id` INT NULL,
  `status` VARCHAR(255) NOT NULL,
  `response_code` INT NULL,
  `duration` FLOAT NULL COMMENT 'in seconds',
  `details` TEXT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `username` VARCHAR(50) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('admin','moderator') DEFAULT 'moderator',
  `last_login` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `proxy_id` INT NULL,
  `keyword_id` INT NULL,
  `task_id` INT NULL,
  `status_code` INT NULL,
  `action` VARCHAR(255) NULL COMMENT 'e.g., GOOGLE_SEARCH, URL_CLICK, PROXY_TEST',
  `message` TEXT NULL,
  `duration` FLOAT NULL COMMENT 'in seconds',
  `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`proxy_id`) REFERENCES `proxies`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`keyword_id`) REFERENCES `keywords`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Örnek User-Agent'lar
INSERT INTO `user_agents` (`user_agent`, `device_type`, `os`, `browser`) VALUES
('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Safari/537.36', 'Desktop', 'Windows 10', 'Chrome'),
('Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.4 Safari/605.1.15', 'Desktop', 'macOS', 'Safari'),
('Mozilla/5.0 (iPhone; CPU iPhone OS 15_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.4 Mobile/15E148 Safari/604.1', 'Mobile', 'iOS', 'Safari'),
('Mozilla/5.0 (Linux; Android 12; SM-G991B) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.127 Mobile Safari/537.36', 'Mobile', 'Android', 'Chrome');

-- Örnek Admin Kullanıcısı (Şifre: admin123 - Lütfen ilk girişten sonra değiştirin!)
-- Şifre 'admin123' için bcrypt hash'i (PHP password_hash('admin123', PASSWORD_DEFAULT) ile üretilmiştir)
-- Gerçek bir uygulamada bu hash'i PHP tarafında üretip eklemek daha güvenlidir.
-- Bu örnekte, kolaylık olması açısından doğrudan hash eklenmiştir.
-- $2y$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx gibi bir hash olmalı.
-- Şimdilik boş bırakıyorum, ilk kullanıcıyı manuel eklemeniz veya bir kayıt scripti ile oluşturmanız daha iyi olacaktır.
-- INSERT INTO `users` (`username`, `password`, `role`) VALUES ('admin', '$2y$10$YOUR_BCRYPT_HASH_HERE', 'admin');

ALTER TABLE `proxies` ADD INDEX `idx_proxies_status` (`status`);
ALTER TABLE `proxies` ADD INDEX `idx_proxies_country` (`country`);
ALTER TABLE `keywords` ADD INDEX `idx_keywords_active` (`active`);
ALTER TABLE `tasks` ADD INDEX `idx_tasks_status` (`status`);
ALTER TABLE `tasks` ADD INDEX `idx_tasks_created_at` (`created_at`);
ALTER TABLE `activity_logs` ADD INDEX `idx_activity_logs_timestamp` (`timestamp`);
ALTER TABLE `activity_logs` ADD INDEX `idx_activity_logs_action` (`action`);
