-- Migration 020 — Accès FTP/SFTP sur la fiche site (onglet Accès)
--
-- Stocke les credentials d'accès FTP/SFTP du site WP du client.
-- Mot de passe chiffré via app_encrypt() comme les autres blocs sensibles.
--
-- Migration idempotente : check de l'existence de chaque colonne avant ADD.

-- 1. ftp_host
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sites' AND COLUMN_NAME = 'ftp_host'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE sites ADD COLUMN ftp_host VARCHAR(255) NULL DEFAULT NULL COMMENT "Adresse hôte FTP/SFTP (ex: ftp.cluster011.hosting.ovh.net)"',
    'SELECT "ftp_host déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. ftp_port (string pour gérer plage personnalisée éventuelle)
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sites' AND COLUMN_NAME = 'ftp_port'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE sites ADD COLUMN ftp_port VARCHAR(10) NULL DEFAULT NULL COMMENT "Port FTP (21 par défaut) / SFTP (22 par défaut) / FTPS (990 ou 21 selon implicit/explicit)"',
    'SELECT "ftp_port déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3. ftp_protocol (énum souple en VARCHAR pour évolutivité)
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sites' AND COLUMN_NAME = 'ftp_protocol'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE sites ADD COLUMN ftp_protocol VARCHAR(10) NULL DEFAULT NULL COMMENT "Protocole : ftp, ftps, sftp"',
    'SELECT "ftp_protocol déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4. ftp_username
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sites' AND COLUMN_NAME = 'ftp_username'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE sites ADD COLUMN ftp_username VARCHAR(120) NULL DEFAULT NULL COMMENT "Identifiant FTP/SFTP"',
    'SELECT "ftp_username déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5. ftp_password_enc (TEXT car chiffrement AES-GCM en base64 ≈ 1.5x taille originale + overhead)
SET @col := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sites' AND COLUMN_NAME = 'ftp_password_enc'
);
SET @sql := IF(@col = 0,
    'ALTER TABLE sites ADD COLUMN ftp_password_enc TEXT NULL DEFAULT NULL COMMENT "Mot de passe FTP chiffré (app_encrypt AES-256-GCM)"',
    'SELECT "ftp_password_enc déjà présent" AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
