-- =============================================
-- SCRIPT DE VÉRIFICATION POST-INSTALLATION
-- =============================================
-- À exécuter après avoir appliqué FIX_ENUM_STATUS.sql
-- pour vérifier que tout est correctement installé

USE tournament_db;

-- =============================================
-- 1. VÉRIFICATION DE LA STRUCTURE
-- =============================================
SELECT '========================================' as '';
SELECT '📋 VÉRIFICATION DE LA STRUCTURE' as '';
SELECT '========================================' as '';

-- Vérifier les colonnes de la table participants
SELECT 
    '✓ Structure table participants' as check_type,
    COLUMN_NAME as colonne,
    COLUMN_TYPE as type,
    IS_NULLABLE as nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'tournament_db'
AND TABLE_NAME = 'participants'
ORDER BY ORDINAL_POSITION;

SELECT '' as '';

-- =============================================
-- 2. VÉRIFICATION DES STATUTS
-- =============================================
SELECT '========================================' as '';
SELECT '📊 VÉRIFICATION DES STATUTS' as '';
SELECT '========================================' as '';

-- Vérifier que l'ENUM contient les bonnes valeurs
SELECT 
    '✓ ENUM Status' as check_type,
    COLUMN_TYPE as valeurs_possibles
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'tournament_db'
AND TABLE_NAME = 'participants'
AND COLUMN_NAME = 'status';

SELECT '' as '';

-- Vérifier les statuts actuellement utilisés
SELECT 
    '✓ Statuts en usage' as check_type,
    status,
    COUNT(*) as nombre_participants
FROM participants
GROUP BY status
ORDER BY 
    CASE status
        WHEN 'en_lice' THEN 1
        WHEN 'elimine' THEN 2
        WHEN 'attente' THEN 3
        ELSE 4
    END;

SELECT '' as '';

-- =============================================
-- 3. VÉRIFICATION DES TRIGGERS
-- =============================================
SELECT '========================================' as '';
SELECT '⚡ VÉRIFICATION DES TRIGGERS' as '';
SELECT '========================================' as '';

SELECT 
    '✓ Triggers actifs' as check_type,
    TRIGGER_NAME as nom_trigger,
    EVENT_MANIPULATION as evenement,
    ACTION_TIMING as timing
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'tournament_db'
AND EVENT_OBJECT_TABLE = 'participants'
ORDER BY TRIGGER_NAME;

SELECT '' as '';

-- =============================================
-- 4. VÉRIFICATION DES DONNÉES
-- =============================================
SELECT '========================================' as '';
SELECT '📈 VÉRIFICATION DES DONNÉES' as '';
SELECT '========================================' as '';

-- Statistiques par tournoi
SELECT 
    t.name as tournoi,
    t.max_participants as limite,
    COUNT(p.id) as total_inscrits,
    SUM(CASE WHEN p.status = 'en_lice' THEN 1 ELSE 0 END) as en_lice,
    SUM(CASE WHEN p.status = 'elimine' THEN 1 ELSE 0 END) as elimines,
    SUM(CASE WHEN p.status = 'attente' THEN 1 ELSE 0 END) as en_attente,
    (t.max_participants - SUM(CASE WHEN p.status = 'en_lice' THEN 1 ELSE 0 END)) as places_libres
FROM tournaments t
LEFT JOIN participants p ON t.id = p.tournament_id
GROUP BY t.id, t.name, t.max_participants;

SELECT '' as '';

-- =============================================
-- 5. VÉRIFICATION DES LISTES D'ATTENTE
-- =============================================
SELECT '========================================' as '';
SELECT '⏱️ VÉRIFICATION LISTES D\'ATTENTE' as '';
SELECT '========================================' as '';

-- Vérifier les positions d'attente
SELECT 
    p.id,
    t.name as tournoi,
    COALESCE(p.prenom, p.name) as prenom,
    COALESCE(p.nom, '') as nom,
    p.status,
    p.position_attente,
    p.inscription_date
FROM participants p
JOIN tournaments t ON p.tournament_id = t.id
WHERE p.status = 'attente'
ORDER BY t.name, p.position_attente;

-- Si aucune liste d'attente
SELECT 
    CASE 
        WHEN COUNT(*) = 0 THEN '✓ Aucun participant en liste d\'attente'
        ELSE CONCAT('⏱️ ', COUNT(*), ' participant(s) en attente')
    END as resultat
FROM participants
WHERE status = 'attente';

SELECT '' as '';

-- =============================================
-- 6. VÉRIFICATION DES COLONNES OBLIGATOIRES
-- =============================================
SELECT '========================================' as '';
SELECT '🔍 VÉRIFICATION COLONNES OBLIGATOIRES' as '';
SELECT '========================================' as '';

SELECT 
    'age' as colonne,
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'tournament_db' 
            AND TABLE_NAME = 'participants' 
            AND COLUMN_NAME = 'age'
        ) THEN '✅ OK'
        ELSE '❌ MANQUANT'
    END as statut
UNION ALL
SELECT 
    'prenom',
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'tournament_db' 
            AND TABLE_NAME = 'participants' 
            AND COLUMN_NAME = 'prenom'
        ) THEN '✅ OK'
        ELSE '❌ MANQUANT'
    END
UNION ALL
SELECT 
    'nom',
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'tournament_db' 
            AND TABLE_NAME = 'participants' 
            AND COLUMN_NAME = 'nom'
        ) THEN '✅ OK'
        ELSE '❌ MANQUANT'
    END
UNION ALL
SELECT 
    'telephone',
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'tournament_db' 
            AND TABLE_NAME = 'participants' 
            AND COLUMN_NAME = 'telephone'
        ) THEN '✅ OK'
        ELSE '❌ MANQUANT'
    END
UNION ALL
SELECT 
    'position_attente',
    CASE 
        WHEN EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'tournament_db' 
            AND TABLE_NAME = 'participants' 
            AND COLUMN_NAME = 'position_attente'
        ) THEN '✅ OK'
        ELSE '❌ MANQUANT'
    END;

SELECT '' as '';

-- =============================================
-- 7. TEST DE COHÉRENCE DES DONNÉES
-- =============================================
SELECT '========================================' as '';
SELECT '🔬 TESTS DE COHÉRENCE' as '';
SELECT '========================================' as '';

-- Vérifier qu'il n'y a pas de statuts invalides
SELECT 
    'Statuts valides' as test,
    CASE 
        WHEN COUNT(*) = 0 THEN '✅ OK - Tous les statuts sont valides'
        ELSE CONCAT('❌ ERREUR - ', COUNT(*), ' participant(s) avec statut invalide')
    END as resultat
FROM participants
WHERE status NOT IN ('en_lice', 'elimine', 'attente');

-- Vérifier que les positions d'attente sont cohérentes
SELECT 
    'Positions d\'attente' as test,
    CASE 
        WHEN COUNT(*) = 0 THEN '✅ OK - Positions cohérentes'
        ELSE CONCAT('⚠️ ', COUNT(*), ' participant(s) en attente sans position')
    END as resultat
FROM participants
WHERE status = 'attente' AND position_attente IS NULL;

-- Vérifier les âges
SELECT 
    'Âges valides' as test,
    CASE 
        WHEN COUNT(*) = 0 THEN '✅ OK - Tous les âges sont renseignés'
        ELSE CONCAT('⚠️ ', COUNT(*), ' participant(s) sans âge ou âge = 0')
    END as resultat
FROM participants
WHERE age IS NULL OR age = 0;

-- Vérifier les limites de tournoi
SELECT 
    'Limites de tournoi' as test,
    CASE 
        WHEN COUNT(*) = 0 THEN '✅ OK - Aucun dépassement de limite'
        ELSE CONCAT('❌ ERREUR - ', COUNT(*), ' tournoi(s) en dépassement')
    END as resultat
FROM (
    SELECT 
        t.id,
        COUNT(p.id) as nb_en_lice,
        t.max_participants
    FROM tournaments t
    LEFT JOIN participants p ON t.id = p.tournament_id AND p.status = 'en_lice'
    GROUP BY t.id, t.max_participants
    HAVING nb_en_lice > max_participants
) as depassements;

SELECT '' as '';

-- =============================================
-- 8. RÉSUMÉ FINAL
-- =============================================
SELECT '========================================' as '';
SELECT '📊 RÉSUMÉ FINAL' as '';
SELECT '========================================' as '';

SELECT 
    'Total participants' as statistique,
    COUNT(*) as valeur
FROM participants
UNION ALL
SELECT 
    'Participants en lice',
    COUNT(*)
FROM participants WHERE status = 'en_lice'
UNION ALL
SELECT 
    'Participants éliminés',
    COUNT(*)
FROM participants WHERE status = 'elimine'
UNION ALL
SELECT 
    'Participants en attente',
    COUNT(*)
FROM participants WHERE status = 'attente'
UNION ALL
SELECT 
    'Tournois actifs',
    COUNT(*)
FROM tournaments WHERE status = 'active';

SELECT '' as '';

-- =============================================
-- 9. DIAGNOSTIC FINAL
-- =============================================
SELECT '========================================' as '';
SELECT '🎯 DIAGNOSTIC FINAL' as '';
SELECT '========================================' as '';

SELECT 
    CASE 
        -- Vérifier que tout est OK
        WHEN (
            -- ENUM correct
            (SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS 
             WHERE TABLE_SCHEMA = 'tournament_db' 
             AND TABLE_NAME = 'participants' 
             AND COLUMN_NAME = 'status') = "enum('en_lice','elimine','attente')"
            
            -- Colonnes présentes
            AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = 'tournament_db' 
                       AND TABLE_NAME = 'participants' 
                       AND COLUMN_NAME = 'age')
            
            -- Triggers présents
            AND (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS 
                WHERE TRIGGER_SCHEMA = 'tournament_db' 
                AND EVENT_OBJECT_TABLE = 'participants') >= 3
            
            -- Pas de statuts invalides
            AND NOT EXISTS (SELECT 1 FROM participants 
                          WHERE status NOT IN ('en_lice', 'elimine', 'attente'))
        ) THEN '
╔════════════════════════════════════════╗
║   ✅ INSTALLATION RÉUSSIE !           ║
╠════════════════════════════════════════╣
║                                        ║
║  Tous les tests sont passés avec      ║
║  succès. Votre système est prêt       ║
║  à être utilisé !                      ║
║                                        ║
║  Prochaines étapes :                   ║
║  1. Tester avec test-status-fix.html  ║
║  2. Mettre à jour le frontend         ║
║  3. Tester le dashboard complet       ║
║                                        ║
╚════════════════════════════════════════╝
'
        ELSE '
╔════════════════════════════════════════╗
║   ⚠️ ATTENTION - PROBLÈMES DÉTECTÉS   ║
╠════════════════════════════════════════╣
║                                        ║
║  Vérifiez les résultats ci-dessus     ║
║  et corrigez les erreurs signalées.   ║
║                                        ║
║  Si besoin, réexécutez :              ║
║  database/FIX_ENUM_STATUS.sql         ║
║                                        ║
╚════════════════════════════════════════╝
'
    END as resultat;

SELECT '========================================' as '';
SELECT 'Script terminé - ' as '', NOW() as timestamp;
SELECT '========================================' as '';
