-- Create publish_servers table for managing remote publishing
CREATE TABLE IF NOT EXISTS `publish_servers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `host` varchar(255) NOT NULL,
  `port` int(11) DEFAULT 21,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `remote_path` varchar(500) DEFAULT '/',
  `data_types` json DEFAULT NULL COMMENT 'Array of data types to publish: html_dashboard, json_data, pdf_results, csv_data',
  `update_interval` int(11) DEFAULT 30 COMMENT 'Update interval in seconds',
  `is_active` tinyint(1) DEFAULT 1,
  `last_publish` timestamp NULL DEFAULT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `event_id` (`event_id`),
  KEY `is_active` (`is_active`),
  CONSTRAINT `publish_servers_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create publish_logs table for tracking publish history
CREATE TABLE IF NOT EXISTS `publish_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `server_id` int(11) NOT NULL,
  `status` enum('success','error') NOT NULL,
  `message` text DEFAULT NULL,
  `files_uploaded` int(11) DEFAULT 0,
  `file_list` json DEFAULT NULL,
  `publish_time` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `server_id` (`server_id`),
  KEY `publish_time` (`publish_time`),
  CONSTRAINT `publish_logs_ibfk_1` FOREIGN KEY (`server_id`) REFERENCES `publish_servers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for testing (optional)
-- INSERT INTO `publish_servers` (`event_id`, `name`, `host`, `port`, `username`, `password`, `remote_path`, `data_types`, `update_interval`, `is_active`) 
-- VALUES 
-- (1, 'Main Results Server', 'ftp.example.com', 21, 'ftpuser', 'ftppass', '/public_html/results/', '["html_dashboard", "json_data"]', 30, 1),
-- (1, 'Backup Server', 'backup.example.com', 21, 'backup_user', 'backup_pass', '/results/', '["csv_data", "pdf_results"]', 300, 1);
