-- ========================================
-- 🎯 SCRIPT FINAL COMPLET POUR PRODUCTION
-- ========================================
-- Ce script contient TOUTES les modifications de base de données
-- effectuées pour le CRM GaultMillau Maroc
--
-- ⚠️ À EXÉCUTER SUR : crmgaujdev ET crmgaujprod
-- ========================================

-- Désactiver temporairement le mode strict
SET sql_mode = '';

-- ========================================
-- 📋 PARTIE 1 : TABLE company_info
-- ========================================
-- Création de la table avec toutes les informations de l'entreprise

CREATE TABLE IF NOT EXISTS `company_info` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(255) NOT NULL DEFAULT 'GaultMillau',
  `company_legal_form` VARCHAR(255) DEFAULT 'Société au capital de 90 000 Dhs',
  `company_capital` VARCHAR(100) DEFAULT '90.000 Dhs',
  `company_ice` VARCHAR(50) DEFAULT '003770977000059',
  `company_rc` VARCHAR(50) DEFAULT 'En cours d\'attribution',
  `company_if` VARCHAR(50) DEFAULT 'En cours',
  `company_address` VARCHAR(255) DEFAULT 'Angle Rue General Moinier et Rue Mardoch, 6ème étage',
  `company_city` VARCHAR(100) DEFAULT 'Casablanca',
  `company_country` VARCHAR(100) DEFAULT 'Maroc',
  `company_phone` VARCHAR(50) DEFAULT '',
  `company_email` VARCHAR(255) DEFAULT 'contact@gaultmillau.ma',
  `company_rib` VARCHAR(255) DEFAULT '225780039137500651010365',
  `company_website` VARCHAR(255) DEFAULT 'www.gaultmillau.ma',
  `company_logo` VARCHAR(255) DEFAULT '',
  `cgv_text` TEXT,
  `footer_text` VARCHAR(500) DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insérer les données de GaultMillau
INSERT INTO `company_info` (
    `id`,
    `company_name`,
    `company_legal_form`,
    `company_capital`,
    `company_ice`,
    `company_rc`,
    `company_if`,
    `company_address`,
    `company_city`,
    `company_country`,
    `company_phone`,
    `company_email`,
    `company_rib`,
    `company_website`,
    `company_logo`,
    `cgv_text`,
    `footer_text`
) VALUES (
    1,
    'GaultMillau',
    'Société au capital de 90 000 Dhs',
    '90.000 Dhs',
    '003770977000059',
    'En cours d\'attribution',
    'En cours',
    'Angle Rue General Moinier et Rue Mardoch, 6ème étage',
    'Casablanca',
    'Maroc',
    '',
    'contact@gaultmillau.ma',
    '225780039137500651010365',
    'www.gaultmillau.ma',
    '',
    'Conditions Générales de Vente - Gault&Millau Maroc',
    'GaultMillau - Société au capital de 90.000 Dhs - ICE: 003770977000059 - RC: En cours d\'attribution - IF: En cours'
) ON DUPLICATE KEY UPDATE
    `company_name` = VALUES(`company_name`),
    `company_legal_form` = VALUES(`company_legal_form`),
    `company_capital` = VALUES(`company_capital`),
    `company_ice` = VALUES(`company_ice`),
    `company_rc` = VALUES(`company_rc`),
    `company_if` = VALUES(`company_if`),
    `company_address` = VALUES(`company_address`),
    `company_city` = VALUES(`company_city`),
    `company_country` = VALUES(`company_country`),
    `company_email` = VALUES(`company_email`),
    `company_rib` = VALUES(`company_rib`),
    `company_website` = VALUES(`company_website`),
    `footer_text` = VALUES(`footer_text`),
    `updated_at` = CURRENT_TIMESTAMP;

-- ========================================
-- 📋 PARTIE 2 : TABLE clients
-- ========================================
-- Ajout des nouveaux champs pour les clients marocains

-- Vérifier si la colonne 'pays' existe et la renommer en 'paya'
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'clients' 
    AND COLUMN_NAME = 'pays');

SET @sql = IF(@col_exists > 0, 
    'ALTER TABLE `clients` CHANGE COLUMN `pays` `paya` TEXT DEFAULT NULL',
    'SELECT "Column pays does not exist, skipping rename" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ajouter les nouvelles colonnes si elles n'existent pas (MySQL compatible)
SET @col_rc = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'clients' AND COLUMN_NAME = 'rc');
SET @sql_rc = IF(@col_rc = 0, 'ALTER TABLE `clients` ADD COLUMN `rc` VARCHAR(100) DEFAULT NULL COMMENT "Registre de Commerce"', 'SELECT "Column rc already exists" AS message');
PREPARE stmt FROM @sql_rc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_ice_lower = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'clients' AND COLUMN_NAME = 'ice');
SET @sql_ice_lower = IF(@col_ice_lower = 0, 'ALTER TABLE `clients` ADD COLUMN `ice` VARCHAR(50) DEFAULT NULL COMMENT "Identifiant Commun de l Entreprise (ICE) - 15 chiffres"', 'SELECT "Column ice already exists" AS message');
PREPARE stmt FROM @sql_ice_lower;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_ice_upper = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'clients' AND COLUMN_NAME = 'Ice');
SET @sql_ice_upper = IF(@col_ice_upper = 0, 'ALTER TABLE `clients` ADD COLUMN `Ice` VARCHAR(50) DEFAULT NULL COMMENT "Identifiant Fiscal (IF)"', 'SELECT "Column Ice already exists" AS message');
PREPARE stmt FROM @sql_ice_upper;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_raison = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'clients' AND COLUMN_NAME = 'raison_sociale');
SET @sql_raison = IF(@col_raison = 0, 'ALTER TABLE `clients` ADD COLUMN `raison_sociale` VARCHAR(255) DEFAULT NULL COMMENT "Raison sociale de l entreprise"', 'SELECT "Column raison_sociale already exists" AS message');
PREPARE stmt FROM @sql_raison;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_tel = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'clients' AND COLUMN_NAME = 'telephone_secondaire');
SET @sql_tel = IF(@col_tel = 0, 'ALTER TABLE `clients` ADD COLUMN `telephone_secondaire` VARCHAR(50) DEFAULT NULL COMMENT "Téléphone secondaire"', 'SELECT "Column telephone_secondaire already exists" AS message');
PREPARE stmt FROM @sql_tel;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Modifier la colonne paya pour mettre 'Maroc' par défaut
ALTER TABLE `clients` 
MODIFY COLUMN `paya` TEXT DEFAULT 'Maroc';

-- Mettre à jour les enregistrements existants
UPDATE `clients` SET `paya` = 'Maroc' WHERE `paya` IS NULL OR `paya` = '';

-- ========================================
-- 📋 PARTIE 3 : TABLE bon_de_commande
-- ========================================
-- Corrections des timestamps et valeurs par défaut

-- Corriger les valeurs invalides de created_at
UPDATE `bon_de_commande` 
SET `created_at` = '2024-01-01 00:00:00' 
WHERE `created_at` = '0000-00-00 00:00:00' OR `created_at` IS NULL;

-- Corriger les valeurs invalides de updated_at
UPDATE `bon_de_commande` 
SET `updated_at` = '2024-01-01 00:00:00' 
WHERE `updated_at` = '0000-00-00 00:00:00' OR `updated_at` IS NULL;

-- Modifier created_at pour avoir une valeur par défaut
ALTER TABLE `bon_de_commande` 
MODIFY COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- Modifier updated_at pour avoir une valeur par défaut et mise à jour automatique
ALTER TABLE `bon_de_commande` 
MODIFY COLUMN `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- Ajouter AUTO_INCREMENT à la colonne id
ALTER TABLE `bon_de_commande` 
MODIFY COLUMN `id` BIGINT NOT NULL AUTO_INCREMENT;

-- Ajouter valeurs par défaut sur tous les champs obligatoires
ALTER TABLE `bon_de_commande` MODIFY COLUMN `domaine_activite` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `type_prestation` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `details_prestation` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `siteweb` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `date_campagne` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `date_bdc_du_jour` VARCHAR(50) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `debut_payment_date` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `fin_payment_date` VARCHAR(50) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `final_payment_date` VARCHAR(50) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `date_signature_electronique` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `nb_reglements` VARCHAR(255) DEFAULT '1';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `mode_paiement` VARCHAR(255) DEFAULT 'Virement';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `detail_reglements` VARCHAR(500) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `montant_ht` VARCHAR(255) DEFAULT '0';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `montant_ttc` VARCHAR(20) DEFAULT '0';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `taux_tva` VARCHAR(11) DEFAULT '20';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `montant_tva` VARCHAR(11) DEFAULT '0';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `devise` VARCHAR(255) DEFAULT 'MAD';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `statut_bdc` VARCHAR(50) DEFAULT 'en_attente';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `facture_cree` VARCHAR(10) DEFAULT 'non';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `transition_old_new` VARCHAR(20) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `upload_url` VARCHAR(100) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `upload_url_bdc_signe` VARCHAR(255) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `token` VARCHAR(100) DEFAULT '';
ALTER TABLE `bon_de_commande` MODIFY COLUMN `bdc_du_jour` INT DEFAULT 0;
ALTER TABLE `bon_de_commande` MODIFY COLUMN `manager_id` INT DEFAULT 0;

-- Nettoyage des données existantes
UPDATE `bon_de_commande` SET `domaine_activite` = '' WHERE `domaine_activite` IS NULL;
UPDATE `bon_de_commande` SET `date_campagne` = '' WHERE `date_campagne` IS NULL;
UPDATE `bon_de_commande` SET `nb_reglements` = '1' WHERE `nb_reglements` IS NULL;
UPDATE `bon_de_commande` SET `mode_paiement` = 'Virement' WHERE `mode_paiement` IS NULL;
UPDATE `bon_de_commande` SET `montant_ht` = '0' WHERE `montant_ht` IS NULL;
UPDATE `bon_de_commande` SET `montant_ttc` = '0' WHERE `montant_ttc` IS NULL;
UPDATE `bon_de_commande` SET `taux_tva` = '20' WHERE `taux_tva` IS NULL;
UPDATE `bon_de_commande` SET `montant_tva` = '0' WHERE `montant_tva` IS NULL;
UPDATE `bon_de_commande` SET `devise` = 'MAD' WHERE `devise` IS NULL;
UPDATE `bon_de_commande` SET `siteweb` = '' WHERE `siteweb` IS NULL;
UPDATE `bon_de_commande` SET `type_prestation` = '' WHERE `type_prestation` IS NULL;
UPDATE `bon_de_commande` SET `details_prestation` = '' WHERE `details_prestation` IS NULL;
UPDATE `bon_de_commande` SET `statut_bdc` = 'en_attente' WHERE `statut_bdc` IS NULL;
UPDATE `bon_de_commande` SET `upload_url` = '' WHERE `upload_url` IS NULL;
UPDATE `bon_de_commande` SET `facture_cree` = 'non' WHERE `facture_cree` IS NULL;
UPDATE `bon_de_commande` SET `final_payment_date` = '' WHERE `final_payment_date` IS NULL;
UPDATE `bon_de_commande` SET `bdc_du_jour` = 0 WHERE `bdc_du_jour` IS NULL;
UPDATE `bon_de_commande` SET `date_bdc_du_jour` = '' WHERE `date_bdc_du_jour` IS NULL;
UPDATE `bon_de_commande` SET `detail_reglements` = '' WHERE `detail_reglements` IS NULL;
UPDATE `bon_de_commande` SET `upload_url_bdc_signe` = '' WHERE `upload_url_bdc_signe` IS NULL;
UPDATE `bon_de_commande` SET `debut_payment_date` = '' WHERE `debut_payment_date` IS NULL;
UPDATE `bon_de_commande` SET `fin_payment_date` = '' WHERE `fin_payment_date` IS NULL;
UPDATE `bon_de_commande` SET `transition_old_new` = '' WHERE `transition_old_new` IS NULL;
UPDATE `bon_de_commande` SET `date_signature_electronique` = '' WHERE `date_signature_electronique` IS NULL;
UPDATE `bon_de_commande` SET `token` = '' WHERE `token` IS NULL;
UPDATE `bon_de_commande` SET `manager_id` = 0 WHERE `manager_id` IS NULL;

-- ========================================
-- 📋 PARTIE 4 : TABLE addedproducts
-- ========================================
-- Ajout des valeurs par défaut

ALTER TABLE `addedproducts` MODIFY COLUMN `surface` VARCHAR(255) DEFAULT '';
ALTER TABLE `addedproducts` MODIFY COLUMN `categorie_produit` VARCHAR(50) DEFAULT '';
ALTER TABLE `addedproducts` MODIFY COLUMN `region` VARCHAR(255) DEFAULT '';
ALTER TABLE `addedproducts` MODIFY COLUMN `date_campagne` VARCHAR(20) DEFAULT '';
ALTER TABLE `addedproducts` MODIFY COLUMN `created_at` VARCHAR(100) DEFAULT '';
ALTER TABLE `addedproducts` MODIFY COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `addedproducts` MODIFY COLUMN `journaliste` VARCHAR(255) DEFAULT 'Non';
ALTER TABLE `addedproducts` MODIFY COLUMN `photographe` VARCHAR(255) DEFAULT 'Non';
ALTER TABLE `addedproducts` MODIFY COLUMN `facture_cree` VARCHAR(10) DEFAULT 'non';
ALTER TABLE `addedproducts` MODIFY COLUMN `bdc_id` INT DEFAULT 0;
ALTER TABLE `addedproducts` MODIFY COLUMN `commercial_id` INT DEFAULT 0;
ALTER TABLE `addedproducts` MODIFY COLUMN `manager_id` INT DEFAULT 0;
ALTER TABLE `addedproducts` MODIFY COLUMN `caller_id` INT DEFAULT 0;
ALTER TABLE `addedproducts` MODIFY COLUMN `client_id` INT DEFAULT 0;

-- Nettoyage des données existantes
UPDATE `addedproducts` SET `surface` = '' WHERE `surface` IS NULL;
UPDATE `addedproducts` SET `region` = '' WHERE `region` IS NULL;
UPDATE `addedproducts` SET `journaliste` = 'Non' WHERE `journaliste` IS NULL;
UPDATE `addedproducts` SET `photographe` = 'Non' WHERE `photographe` IS NULL;
UPDATE `addedproducts` SET `categorie_produit` = '' WHERE `categorie_produit` IS NULL;
UPDATE `addedproducts` SET `date_campagne` = '' WHERE `date_campagne` IS NULL;
UPDATE `addedproducts` SET `facture_cree` = 'non' WHERE `facture_cree` IS NULL;
UPDATE `addedproducts` SET `created_at` = '' WHERE `created_at` IS NULL;
UPDATE `addedproducts` SET `bdc_id` = 0 WHERE `bdc_id` IS NULL;
UPDATE `addedproducts` SET `commercial_id` = 0 WHERE `commercial_id` IS NULL;
UPDATE `addedproducts` SET `manager_id` = 0 WHERE `manager_id` IS NULL;
UPDATE `addedproducts` SET `caller_id` = 0 WHERE `caller_id` IS NULL;
UPDATE `addedproducts` SET `client_id` = 0 WHERE `client_id` IS NULL;
UPDATE `addedproducts` SET `updated_at` = NOW() WHERE `updated_at` = '0000-00-00 00:00:00' OR `updated_at` IS NULL;

-- ========================================
-- 📋 PARTIE 5 : TABLE factures
-- ========================================
-- Ajout des valeurs par défaut

ALTER TABLE `factures` MODIFY COLUMN `created_at` VARCHAR(10) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `updated_at` VARCHAR(50) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `number_bdc` VARCHAR(255) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `nb_reglements` INT DEFAULT 1;
ALTER TABLE `factures` MODIFY COLUMN `montant_tva` VARCHAR(11) DEFAULT '0';
ALTER TABLE `factures` MODIFY COLUMN `devise` VARCHAR(10) DEFAULT 'MAD';
ALTER TABLE `factures` MODIFY COLUMN `caller_id` INT DEFAULT 0;
ALTER TABLE `factures` MODIFY COLUMN `facture_du_jour` INT DEFAULT 1;
ALTER TABLE `factures` MODIFY COLUMN `date_facture_du_jour` VARCHAR(50) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `upload_url` VARCHAR(100) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `bdc_id` INT DEFAULT 0;
ALTER TABLE `factures` MODIFY COLUMN `detail_reglements` VARCHAR(500) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `debut_payment_date` VARCHAR(255) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `quantite` VARCHAR(10) DEFAULT '0';
ALTER TABLE `factures` MODIFY COLUMN `commercial_id` INT DEFAULT 0;
ALTER TABLE `factures` MODIFY COLUMN `paiement_recu` VARCHAR(255) DEFAULT 'Non';
ALTER TABLE `factures` MODIFY COLUMN `transition_old_new` VARCHAR(20) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `tva_declare` VARCHAR(50) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `manager_id` INT DEFAULT 0;
ALTER TABLE `factures` MODIFY COLUMN `date_derniere_mensualite` VARCHAR(50) DEFAULT '';
ALTER TABLE `factures` MODIFY COLUMN `ufer` VARCHAR(20) DEFAULT 'non';
ALTER TABLE `factures` MODIFY COLUMN `date_envoi_facture` VARCHAR(50) DEFAULT '';

-- Nettoyage des données existantes
UPDATE `factures` SET `created_at` = '' WHERE `created_at` IS NULL;
UPDATE `factures` SET `updated_at` = '' WHERE `updated_at` IS NULL;
UPDATE `factures` SET `number_bdc` = '' WHERE `number_bdc` IS NULL;
UPDATE `factures` SET `nb_reglements` = 1 WHERE `nb_reglements` IS NULL;
UPDATE `factures` SET `montant_tva` = '0' WHERE `montant_tva` IS NULL;
UPDATE `factures` SET `devise` = 'MAD' WHERE `devise` IS NULL;
UPDATE `factures` SET `caller_id` = 0 WHERE `caller_id` IS NULL;
UPDATE `factures` SET `facture_du_jour` = 1 WHERE `facture_du_jour` IS NULL;
UPDATE `factures` SET `date_facture_du_jour` = '' WHERE `date_facture_du_jour` IS NULL;
UPDATE `factures` SET `upload_url` = '' WHERE `upload_url` IS NULL;
UPDATE `factures` SET `bdc_id` = 0 WHERE `bdc_id` IS NULL;
UPDATE `factures` SET `detail_reglements` = '' WHERE `detail_reglements` IS NULL;
UPDATE `factures` SET `debut_payment_date` = '' WHERE `debut_payment_date` IS NULL;
UPDATE `factures` SET `quantite` = '0' WHERE `quantite` IS NULL;
UPDATE `factures` SET `commercial_id` = 0 WHERE `commercial_id` IS NULL;
UPDATE `factures` SET `paiement_recu` = 'Non' WHERE `paiement_recu` IS NULL;
UPDATE `factures` SET `transition_old_new` = '' WHERE `transition_old_new` IS NULL;
UPDATE `factures` SET `tva_declare` = '' WHERE `tva_declare` IS NULL;
UPDATE `factures` SET `manager_id` = 0 WHERE `manager_id` IS NULL;
UPDATE `factures` SET `date_derniere_mensualite` = '' WHERE `date_derniere_mensualite` IS NULL;
UPDATE `factures` SET `ufer` = 'non' WHERE `ufer` IS NULL;
UPDATE `factures` SET `date_envoi_facture` = '' WHERE `date_envoi_facture` IS NULL;

-- Réactiver le mode SQL par défaut
SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- ========================================
-- ✅ VÉRIFICATION FINALE
-- ========================================
SELECT 'company_info' AS Table_Name, COUNT(*) AS Record_Count FROM company_info
UNION ALL
SELECT 'clients - colonnes ajoutées' AS Info, 
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = DATABASE() 
     AND TABLE_NAME = 'clients' 
     AND COLUMN_NAME IN ('rc', 'ice', 'Ice', 'raison_sociale', 'paya')) AS Count
UNION ALL
SELECT 'bon_de_commande - id AUTO_INCREMENT' AS Info,
    IF((SELECT EXTRA FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'bon_de_commande' 
        AND COLUMN_NAME = 'id') = 'auto_increment', 1, 0) AS IsSet
UNION ALL
SELECT 'addedproducts - defaults OK' AS Info, 1 AS OK;

-- ========================================
-- 📋 RÉSUMÉ DES MODIFICATIONS
-- ========================================
-- ✅ Table company_info créée avec toutes les infos GaultMillau
-- ✅ Table clients : ajout RC, IF (Ice), ICE (ice), raison_sociale, telephone_secondaire
-- ✅ Table clients : colonne 'pays' renommée en 'paya' avec défaut 'Maroc'
-- ✅ Table bon_de_commande : AUTO_INCREMENT sur id
-- ✅ Table bon_de_commande : 27 champs avec valeurs par défaut
-- ✅ Table addedproducts : 14 champs avec valeurs par défaut
-- ✅ Devise par défaut : MAD (Dirham marocain)
-- ✅ TVA par défaut : 20%
-- ========================================

