-- ===============================================
-- HOMOGÉNÉISATION DES NOMS DE PAYS EN FRANÇAIS
-- Table: eex_v2_mega_catalog
-- ===============================================

-- 1. DIAGNOSTIC - Voir l'état actuel des pays
SELECT '=== ÉTAT ACTUEL DES PAYS ===' as diagnostic;

SELECT DISTINCT country_code, country_name, COUNT(*) as count
FROM eex_v2_mega_catalog
WHERE provider = 'EEX' AND country_code IS NOT NULL
GROUP BY country_code, country_name
ORDER BY country_code, country_name;

-- 2. DÉTECTER LES DOUBLONS
SELECT '=== DOUBLONS DÉTECTÉS ===' as diagnostic;

SELECT country_code, 
       GROUP_CONCAT(DISTINCT country_name) as noms_actuels, 
       COUNT(DISTINCT country_name) as nb_versions
FROM eex_v2_mega_catalog
WHERE provider = 'EEX' AND country_code IS NOT NULL
GROUP BY country_code
HAVING COUNT(DISTINCT country_name) > 1
ORDER BY country_code;

-- 3. CORRECTIONS - Homogénéisation en français
SELECT '=== DÉBUT DES CORRECTIONS ===' as action;

-- Pays européens principaux
UPDATE eex_v2_mega_catalog SET country_name = 'Allemagne' WHERE country_code = 'DE' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'France' WHERE country_code = 'FR' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Autriche' WHERE country_code = 'AT' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Belgique' WHERE country_code = 'BE' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Pays-Bas' WHERE country_code = 'NL' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Suisse' WHERE country_code = 'CH' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Italie' WHERE country_code = 'IT' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Espagne' WHERE country_code = 'ES' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Portugal' WHERE country_code = 'PT' AND provider = 'EEX';

-- Europe de l'Est
UPDATE eex_v2_mega_catalog SET country_name = 'Pologne' WHERE country_code = 'PL' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'République tchèque' WHERE country_code = 'CZ' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Slovaquie' WHERE country_code = 'SK' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Hongrie' WHERE country_code = 'HU' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Slovénie' WHERE country_code = 'SI' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Croatie' WHERE country_code = 'HR' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Roumanie' WHERE country_code = 'RO' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Bulgarie' WHERE country_code = 'BG' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Grèce' WHERE country_code = 'GR' AND provider = 'EEX';

-- Europe du Nord
UPDATE eex_v2_mega_catalog SET country_name = 'Danemark' WHERE country_code = 'DK' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Suède' WHERE country_code = 'SE' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Norvège' WHERE country_code = 'NO' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Finlande' WHERE country_code = 'FI' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Islande' WHERE country_code = 'IS' AND provider = 'EEX';

-- Pays Baltes
UPDATE eex_v2_mega_catalog SET country_name = 'Estonie' WHERE country_code = 'EE' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Lettonie' WHERE country_code = 'LV' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Lituanie' WHERE country_code = 'LT' AND provider = 'EEX';

-- Autres pays européens
UPDATE eex_v2_mega_catalog SET country_name = 'Luxembourg' WHERE country_code = 'LU' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Irlande' WHERE country_code = 'IE' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Royaume-Uni' WHERE country_code = 'GB' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Malte' WHERE country_code = 'MT' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Chypre' WHERE country_code = 'CY' AND provider = 'EEX';

-- Europe de l'Ouest supplémentaire
UPDATE eex_v2_mega_catalog SET country_name = 'Monaco' WHERE country_code = 'MC' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Liechtenstein' WHERE country_code = 'LI' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Andorre' WHERE country_code = 'AD' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Saint-Marin' WHERE country_code = 'SM' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Vatican' WHERE country_code = 'VA' AND provider = 'EEX';

-- Europe du Sud-Est
UPDATE eex_v2_mega_catalog SET country_name = 'Serbie' WHERE country_code = 'RS' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Monténégro' WHERE country_code = 'ME' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Bosnie-Herzégovine' WHERE country_code = 'BA' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Macédoine du Nord' WHERE country_code = 'MK' AND provider = 'EEX';
UPDATE eex_v2_mega_catalog SET country_name = 'Albanie' WHERE country_code = 'AL' AND provider = 'EEX';

-- 4. VÉRIFICATION FINALE
SELECT '=== RÉSULTAT APRÈS CORRECTIONS ===' as verification;

SELECT DISTINCT country_code, country_name, COUNT(*) as count
FROM eex_v2_mega_catalog
WHERE provider = 'EEX' AND country_code IS NOT NULL
GROUP BY country_code, country_name
ORDER BY country_code;

-- 5. STATISTIQUES FINALES
SELECT '=== STATISTIQUES FINALES ===' as stats;

SELECT 
    COUNT(DISTINCT country_code) as nb_pays_distincts,
    COUNT(*) as total_produits_eex,
    SUM(CASE WHEN is_favorite = 1 THEN 1 ELSE 0 END) as total_favoris
FROM eex_v2_mega_catalog
WHERE provider = 'EEX' AND country_code IS NOT NULL;

SELECT '=== CORRECTION TERMINÉE ===' as fin;