-- Add FTP servers table for user-specific FTP configurations
CREATE TABLE `ftp_server` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `host` varchar(255) NOT NULL,
  `port` int(11) NOT NULL DEFAULT 21,
  `username` varchar(255) NOT NULL,
  `password` text DEFAULT NULL,
  `protocol` enum('FTP','FTPS','SFTP') NOT NULL DEFAULT 'FTP',
  `encryption` enum('None','SSL','TLS') NOT NULL DEFAULT 'None',
  `passive_mode` tinyint(1) NOT NULL DEFAULT 1,
  `ssl` tinyint(1) NOT NULL DEFAULT 0,
  `remote_path` varchar(500) NOT NULL DEFAULT '/',
  `remember_password` tinyint(1) NOT NULL DEFAULT 1,
  `server_type` enum('development','production') NOT NULL DEFAULT 'development',
  `last_connected` datetime(3) DEFAULT NULL,
  `created_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `updated_at` datetime(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3),
  PRIMARY KEY (`id`),
  KEY `ftp_server_user_id_fkey` (`user_id`),
  KEY `idx_ftp_server_user_type` (`user_id`, `server_type`),
  CONSTRAINT `ftp_server_user_id_fkey` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
