-- Medusa 3.1.0 Features Database Schema Update
-- Adds support for advanced browser fingerprinting, geolocation targeting, and social media traffic


-- Add fingerprint column to traffic_logs table
ALTER TABLE `traffic_logs` ADD COLUMN `fingerprint` text DEFAULT NULL AFTER `response_time_ms`;

-- Add device type column to traffic_logs table
ALTER TABLE `traffic_logs` ADD COLUMN `device_type` enum('desktop','mobile','tablet') DEFAULT 'desktop' AFTER `fingerprint`;

-- Add automation_script column to traffic_logs table
ALTER TABLE `traffic_logs` ADD COLUMN `automation_script` text DEFAULT NULL AFTER `device_type`;

-- Add targeting columns to campaigns table
ALTER TABLE `campaigns` ADD COLUMN `targeting_countries` text DEFAULT NULL AFTER `end_date`;
ALTER TABLE `campaigns` ADD COLUMN `targeting_regions` text DEFAULT NULL AFTER `targeting_countries`;
ALTER TABLE `campaigns` ADD COLUMN `targeting_isps` text DEFAULT NULL AFTER `targeting_regions`;
ALTER TABLE `campaigns` ADD COLUMN `proxy_type` enum('any','residential','mobile','datacenter') DEFAULT 'any' AFTER `targeting_isps`;
ALTER TABLE `campaigns` ADD COLUMN `traffic_source` enum('direct','search','social_media','referral') DEFAULT 'direct' AFTER `proxy_type`;

-- Add geolocation columns to proxies table
ALTER TABLE `proxies` ADD COLUMN `city` varchar(100) DEFAULT NULL AFTER `country`;
ALTER TABLE `proxies` ADD COLUMN `region` varchar(100) DEFAULT NULL AFTER `city`;
ALTER TABLE `proxies` ADD COLUMN `isp` varchar(100) DEFAULT NULL AFTER `region`;

-- Update proxy type enum
ALTER TABLE `proxies` MODIFY COLUMN `type` enum('http','https','socks4','socks5','residential','mobile','datacenter') NOT NULL DEFAULT 'http';

-- Create indexes for performance
CREATE INDEX `idx_traffic_logs_device_type` ON `traffic_logs` (`device_type`);
CREATE INDEX `idx_campaigns_traffic_source` ON `campaigns` (`traffic_source`);
CREATE INDEX `idx_campaigns_proxy_type` ON `campaigns` (`proxy_type`);
CREATE INDEX `idx_proxies_city` ON `proxies` (`city`);
CREATE INDEX `idx_proxies_region` ON `proxies` (`region`);
CREATE INDEX `idx_proxies_isp` ON `proxies` (`isp`);

