-- Migration pour ajouter les statuts Cdiscount
-- À exécuter sur les bases de données existantes

USE cdiscount_manager;

-- Vérifier et ajouter la colonne cdiscount_status
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'products' 
AND COLUMN_NAME = 'cdiscount_status';

SET @query = IF(@col_exists = 0, 
    'ALTER TABLE products ADD COLUMN cdiscount_status VARCHAR(50) AFTER cdiscount_product_id', 
    'SELECT "La colonne cdiscount_status existe déjà"');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Vérifier et ajouter la colonne cdiscount_status_message
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'products' 
AND COLUMN_NAME = 'cdiscount_status_message';

SET @query = IF(@col_exists = 0, 
    'ALTER TABLE products ADD COLUMN cdiscount_status_message TEXT AFTER cdiscount_status', 
    'SELECT "La colonne cdiscount_status_message existe déjà"');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Vérifier et ajouter la colonne is_online
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'products' 
AND COLUMN_NAME = 'is_online';

SET @query = IF(@col_exists = 0, 
    'ALTER TABLE products ADD COLUMN is_online TINYINT(1) DEFAULT 0 AFTER cdiscount_status_message', 
    'SELECT "La colonne is_online existe déjà"');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ajouter un index sur le statut Cdiscount si il n'existe pas
SET @index_exists = 0;
SELECT COUNT(*) INTO @index_exists 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'products' 
AND INDEX_NAME = 'idx_cdiscount_status';

SET @query = IF(@index_exists = 0, 
    'ALTER TABLE products ADD INDEX idx_cdiscount_status (cdiscount_status)', 
    'SELECT "L\'index idx_cdiscount_status existe déjà"');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'Migration terminée avec succès !' as message;
