-- ========================================
-- 🏦 AJOUT CHAMPS BANCAIRES TABLE CLIENTS
-- ========================================
-- Ajouter les colonnes RIB et Compte bancaire
-- À exécuter sur : crmgaujdev ET crmgaujprod
-- ========================================

-- Ajouter colonne RIB si elle n'existe pas
SET @col_rib = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'clients' 
    AND COLUMN_NAME = 'rib');

SET @sql_rib = IF(@col_rib = 0, 
    'ALTER TABLE `clients` ADD COLUMN `rib` VARCHAR(30) DEFAULT NULL COMMENT "Relevé d Identité Bancaire (24 chiffres)"',
    'SELECT "Column rib already exists" AS message');
PREPARE stmt FROM @sql_rib;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ajouter colonne compte_bancaire si elle n'existe pas
SET @col_compte = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'clients' 
    AND COLUMN_NAME = 'compte_bancaire');

SET @sql_compte = IF(@col_compte = 0, 
    'ALTER TABLE `clients` ADD COLUMN `compte_bancaire` VARCHAR(20) DEFAULT NULL COMMENT "Numéro de Compte bancaire (16 chiffres)"',
    'SELECT "Column compte_bancaire already exists" AS message');
PREPARE stmt FROM @sql_compte;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================
-- ✅ VÉRIFICATION
-- ========================================
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMN_COMMENT
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'clients' 
AND COLUMN_NAME IN ('rib', 'compte_bancaire');

