-- =============================================
-- SCRIPT DE TEST - Vérification complète du système v2.0
-- =============================================
-- Exécutez ce script pour vérifier que tout fonctionne

USE tournament_db;

SELECT '========================================' as '';
SELECT '🧪 TESTS DU SYSTÈME v2.0' as '';
SELECT '========================================' as '';
SELECT '' as '';

-- =============================================
-- TEST 1 : Tables créées
-- =============================================
SELECT '📋 TEST 1 : Vérification des tables' as test;
SELECT 
    CASE 
        WHEN COUNT(*) = 7 THEN '✅ SUCCÈS - Toutes les tables présentes'
        ELSE CONCAT('❌ ÉCHEC - ', COUNT(*), ' tables sur 7')
    END as resultat
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'tournament_db' 
AND TABLE_NAME IN ('tournaments', 'participants', 'pools', 'pool_participants', 'users', 'sessions', 'activity_logs');

SELECT '' as '';

-- =============================================
-- TEST 2 : Comptes utilisateurs
-- =============================================
SELECT '👥 TEST 2 : Vérification des comptes' as test;
SELECT 
    username,
    role,
    CASE 
        WHEN username = 'admin' AND role = 'admin' THEN '✅ OK'
        WHEN username = 'operateur' AND role = 'operator' THEN '✅ OK'
        ELSE '❌ ERREUR'
    END as statut
FROM users
ORDER BY role DESC;

SELECT '' as '';

-- =============================================
-- TEST 3 : Colonnes ajoutées aux tournois
-- =============================================
SELECT '🏆 TEST 3 : Colonnes des tournois' as test;
SELECT 
    CASE 
        WHEN COUNT(*) = 2 THEN '✅ SUCCÈS - max_participants et age_minimum présents'
        ELSE CONCAT('❌ ÉCHEC - Colonnes manquantes')
    END as resultat
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'tournament_db' 
AND TABLE_NAME = 'tournaments'
AND COLUMN_NAME IN ('max_participants', 'age_minimum');

-- Afficher les paramètres actuels
SELECT 
    id,
    name,
    max_participants,
    age_minimum,
    CONCAT(max_participants, ' places, ', age_minimum, '+ ans') as parametres
FROM tournaments;

SELECT '' as '';

-- =============================================
-- TEST 4 : Triggers créés
-- =============================================
SELECT '⚡ TEST 4 : Vérification des triggers' as test;
SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION as evenement,
    CASE 
        WHEN TRIGGER_NAME = 'before_participant_insert' THEN '✅ File d\'attente automatique'
        WHEN TRIGGER_NAME = 'after_participant_delete' THEN '✅ Promotion automatique'
        ELSE '❓ Inconnu'
    END as fonction
FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'tournament_db';

SELECT '' as '';

-- =============================================
-- TEST 5 : Test de la file d'attente
-- =============================================
SELECT '⏳ TEST 5 : Simulation file d\'attente' as test;

-- Simuler un tournoi avec 32 places max
UPDATE tournaments SET max_participants = 32 WHERE id = 'fortnite';

-- Compter les participants en lice pour Fortnite
SELECT 
    COUNT(*) as participants_en_lice,
    (SELECT max_participants FROM tournaments WHERE id = 'fortnite') as limite,
    CASE 
        WHEN COUNT(*) < (SELECT max_participants FROM tournaments WHERE id = 'fortnite') 
        THEN CONCAT('✅ ', (SELECT max_participants FROM tournaments WHERE id = 'fortnite') - COUNT(*), ' places disponibles')
        WHEN COUNT(*) = (SELECT max_participants FROM tournaments WHERE id = 'fortnite')
        THEN '⚠️ COMPLET - Prochaine inscription → File d\'attente'
        ELSE '❌ SURCHARGE - Vérifier les données'
    END as statut
FROM participants 
WHERE tournament_id = 'fortnite' AND status = 'En lice';

SELECT '' as '';

-- =============================================
-- TEST 6 : Test du contrôle d'âge
-- =============================================
SELECT '🎂 TEST 6 : Contrôle d\'âge' as test;

SELECT 
    t.id,
    t.name,
    t.age_minimum,
    COUNT(p.id) as participants,
    MIN(p.age) as age_min_inscrit,
    MAX(p.age) as age_max_inscrit,
    CASE 
        WHEN MIN(p.age) >= t.age_minimum THEN '✅ Tous âges valides'
        ELSE CONCAT('❌ Participant(s) trop jeune(s) - Min requis: ', t.age_minimum)
    END as verification
FROM tournaments t
LEFT JOIN participants p ON t.id = p.tournament_id
GROUP BY t.id, t.name, t.age_minimum;

SELECT '' as '';

-- =============================================
-- TEST 7 : Test de sécurité des mots de passe
-- =============================================
SELECT '🔐 TEST 7 : Sécurité des mots de passe' as test;

SELECT 
    username,
    LEFT(password, 7) as hash_debut,
    LENGTH(password) as longueur_hash,
    CASE 
        WHEN LEFT(password, 4) = '$2y$' THEN '✅ Hash bcrypt valide'
        ELSE '❌ Hash invalide ou non sécurisé'
    END as validation
FROM users;

SELECT '' as '';

-- =============================================
-- TEST 8 : Structure des participants
-- =============================================
SELECT '👤 TEST 8 : Structure des participants' as test;

SELECT 
    CASE 
        WHEN COUNT(*) = 5 THEN '✅ SUCCÈS - Toutes les colonnes présentes'
        ELSE CONCAT('❌ ÉCHEC - ', COUNT(*), ' colonnes sur 5')
    END as resultat
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'tournament_db' 
AND TABLE_NAME = 'participants'
AND COLUMN_NAME IN ('prenom', 'nom', 'age', 'telephone', 'position_attente');

SELECT '' as '';

-- =============================================
-- TEST 9 : Vérification des données de test
-- =============================================
SELECT '📊 TEST 9 : Données de test' as test;

SELECT 
    'Tournois' as table_name,
    COUNT(*) as nombre_enregistrements,
    CASE 
        WHEN COUNT(*) >= 4 THEN '✅ OK'
        ELSE '⚠️ Données manquantes'
    END as statut
FROM tournaments

UNION ALL

SELECT 
    'Participants' as table_name,
    COUNT(*) as nombre_enregistrements,
    CASE 
        WHEN COUNT(*) > 0 THEN '✅ OK'
        ELSE '⚠️ Aucun participant'
    END as statut
FROM participants

UNION ALL

SELECT 
    'Utilisateurs' as table_name,
    COUNT(*) as nombre_enregistrements,
    CASE 
        WHEN COUNT(*) >= 2 THEN '✅ OK'
        ELSE '⚠️ Comptes manquants'
    END as statut
FROM users;

SELECT '' as '';

-- =============================================
-- TEST 10 : Statistiques par tournoi
-- =============================================
SELECT '📈 TEST 10 : Statistiques des tournois' as test;

SELECT 
    t.icon,
    t.name,
    t.max_participants as limite,
    COUNT(p.id) as inscrits,
    SUM(CASE WHEN p.status = 'En lice' THEN 1 ELSE 0 END) as en_lice,
    SUM(CASE WHEN p.status = 'Éliminé' THEN 1 ELSE 0 END) as elimines,
    SUM(CASE WHEN p.status = 'Liste d\'attente' THEN 1 ELSE 0 END) as attente,
    (t.max_participants - SUM(CASE WHEN p.status = 'En lice' THEN 1 ELSE 0 END)) as places_restantes
FROM tournaments t
LEFT JOIN participants p ON t.id = p.tournament_id
GROUP BY t.id, t.icon, t.name, t.max_participants
ORDER BY t.name;

SELECT '' as '';

-- =============================================
-- TEST 11 : Vérification des index
-- =============================================
SELECT '🔍 TEST 11 : Index de performance' as test;

SELECT 
    TABLE_NAME as table_name,
    INDEX_NAME as index_name,
    GROUP_CONCAT(COLUMN_NAME) as colonnes,
    '✅ OK' as statut
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'tournament_db'
AND TABLE_NAME IN ('participants', 'users', 'sessions', 'tournaments')
GROUP BY TABLE_NAME, INDEX_NAME;

SELECT '' as '';

-- =============================================
-- TEST 12 : Simulation d'inscription
-- =============================================
SELECT '🎮 TEST 12 : Simulation d\'inscription' as test;

-- Créer un participant de test
INSERT INTO participants (tournament_id, prenom, nom, age, telephone, inscription_date)
VALUES ('fortnite', 'Test', 'Utilisateur', 25, '0600000000', CURDATE());

-- Vérifier qu'il a été créé avec le bon statut
SELECT 
    CONCAT(prenom, ' ', nom) as participant,
    age,
    status,
    position_attente,
    CASE 
        WHEN status = 'En lice' AND position_attente IS NULL THEN '✅ Inscription normale'
        WHEN status = 'Liste d\'attente' AND position_attente IS NOT NULL THEN '✅ File d\'attente (position ' + CAST(position_attente AS CHAR) + ')'
        ELSE '❌ Erreur de statut'
    END as resultat
FROM participants
WHERE prenom = 'Test' AND nom = 'Utilisateur'
ORDER BY id DESC
LIMIT 1;

-- Nettoyer le participant de test
DELETE FROM participants WHERE prenom = 'Test' AND nom = 'Utilisateur';

SELECT '🧹 Participant de test nettoyé' as info;

SELECT '' as '';

-- =============================================
-- RÉSUMÉ FINAL
-- =============================================
SELECT '========================================' as '';
SELECT '📋 RÉSUMÉ DES TESTS' as '';
SELECT '========================================' as '';

SELECT 
    'Tables' as categorie,
    (SELECT COUNT(*) FROM information_schema.TABLES 
     WHERE TABLE_SCHEMA = 'tournament_db' 
     AND TABLE_NAME IN ('tournaments', 'participants', 'pools', 'pool_participants', 'users', 'sessions', 'activity_logs')) as attendu,
    7 as requis,
    CASE 
        WHEN (SELECT COUNT(*) FROM information_schema.TABLES 
              WHERE TABLE_SCHEMA = 'tournament_db' 
              AND TABLE_NAME IN ('tournaments', 'participants', 'pools', 'pool_participants', 'users', 'sessions', 'activity_logs')) = 7 
        THEN '✅'
        ELSE '❌'
    END as statut

UNION ALL

SELECT 
    'Utilisateurs' as categorie,
    (SELECT COUNT(*) FROM users) as attendu,
    2 as requis,
    CASE WHEN (SELECT COUNT(*) FROM users) >= 2 THEN '✅' ELSE '❌' END as statut

UNION ALL

SELECT 
    'Triggers' as categorie,
    (SELECT COUNT(*) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'tournament_db') as attendu,
    2 as requis,
    CASE WHEN (SELECT COUNT(*) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'tournament_db') >= 2 THEN '✅' ELSE '❌' END as statut

UNION ALL

SELECT 
    'Colonnes tournois' as categorie,
    (SELECT COUNT(*) FROM information_schema.COLUMNS 
     WHERE TABLE_SCHEMA = 'tournament_db' AND TABLE_NAME = 'tournaments'
     AND COLUMN_NAME IN ('max_participants', 'age_minimum')) as attendu,
    2 as requis,
    CASE WHEN (SELECT COUNT(*) FROM information_schema.COLUMNS 
               WHERE TABLE_SCHEMA = 'tournament_db' AND TABLE_NAME = 'tournaments'
               AND COLUMN_NAME IN ('max_participants', 'age_minimum')) = 2 THEN '✅' ELSE '❌' END as statut

UNION ALL

SELECT 
    'Tournois' as categorie,
    (SELECT COUNT(*) FROM tournaments) as attendu,
    4 as requis,
    CASE WHEN (SELECT COUNT(*) FROM tournaments) >= 4 THEN '✅' ELSE '❌' END as statut;

SELECT '' as '';
SELECT '========================================' as '';
SELECT CASE 
    WHEN (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'tournament_db' AND TABLE_NAME IN ('tournaments', 'participants', 'pools', 'pool_participants', 'users', 'sessions', 'activity_logs')) = 7
    AND (SELECT COUNT(*) FROM users) >= 2
    AND (SELECT COUNT(*) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'tournament_db') >= 2
    THEN '🎉 TOUS LES TESTS RÉUSSIS ! 🎉'
    ELSE '⚠️ CERTAINS TESTS ONT ÉCHOUÉ'
END as resultat_final;
SELECT '========================================' as '';

SELECT '' as '';
SELECT '✅ Système prêt pour la production !' as message;
SELECT 'Vous pouvez maintenant vous connecter à :' as '';
SELECT 'http://192.168.1.182/tournament-project/frontend/login.html' as url;
SELECT '' as '';
SELECT 'Comptes de connexion :' as '';
SELECT '  👑 Admin    : admin / Admin123!' as '';
SELECT '  👨‍💼 Opérateur : operateur / Oper123!' as '';
SELECT '' as '';
