-- =============================================
-- SCRIPT DE CORRECTION AUTOMATIQUE - SYSTÈME COMPLET
-- =============================================
-- Ce script crée TOUT ce qui est nécessaire, même si ça existe déjà

USE tournament_db;

-- =============================================
-- ÉTAPE 1 : Créer la table users (si elle n'existe pas)
-- =============================================
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    role ENUM('admin', 'operator') NOT NULL DEFAULT 'operator',
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- ÉTAPE 2 : Supprimer les anciens comptes et recréer
-- =============================================
DELETE FROM users WHERE username IN ('admin', 'operateur');

-- Créer le compte admin
-- Password: Admin123!
INSERT INTO users (username, password, nom, prenom, email, role) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrateur', 'Système', 'admin@tournois.local', 'admin');

-- Créer le compte opérateur
-- Password: Oper123!
INSERT INTO users (username, password, nom, prenom, email, role) VALUES 
('operateur', '$2y$10$vHT.KqMZQqT0GqKbLxhzO.YkqJ3pqZ5qQf9q3h4CqJ3pqZ5qQf9q3', 'Test', 'Opérateur', 'operateur@tournois.local', 'operator');

-- =============================================
-- ÉTAPE 3 : Créer la table sessions
-- =============================================
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(255) NOT NULL,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- ÉTAPE 4 : Créer la table activity_logs
-- =============================================
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id VARCHAR(100) NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- ÉTAPE 5 : Ajouter les colonnes manquantes aux tournois
-- =============================================
-- Vérifier et ajouter max_participants
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS 
                   WHERE TABLE_SCHEMA = 'tournament_db' 
                   AND TABLE_NAME = 'tournaments' 
                   AND COLUMN_NAME = 'max_participants');

SET @query = IF(@col_exists = 0,
    'ALTER TABLE tournaments ADD COLUMN max_participants INT NOT NULL DEFAULT 32 AFTER description',
    'SELECT "Colonne max_participants existe déjà" as info');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Vérifier et ajouter age_minimum
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS 
                   WHERE TABLE_SCHEMA = 'tournament_db' 
                   AND TABLE_NAME = 'tournaments' 
                   AND COLUMN_NAME = 'age_minimum');

SET @query = IF(@col_exists = 0,
    'ALTER TABLE tournaments ADD COLUMN age_minimum INT NOT NULL DEFAULT 0 AFTER max_participants',
    'SELECT "Colonne age_minimum existe déjà" as info');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Mettre à jour les valeurs par défaut
UPDATE tournaments SET max_participants = 32, age_minimum = 16 WHERE id = 'fortnite';
UPDATE tournaments SET max_participants = 24, age_minimum = 12 WHERE id = 'mariokart';
UPDATE tournaments SET max_participants = 16, age_minimum = 13 WHERE id = 'smash';
UPDATE tournaments SET max_participants = 20, age_minimum = 16 WHERE id = 'fc26';

-- =============================================
-- ÉTAPE 6 : Ajouter position_attente aux participants
-- =============================================
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS 
                   WHERE TABLE_SCHEMA = 'tournament_db' 
                   AND TABLE_NAME = 'participants' 
                   AND COLUMN_NAME = 'position_attente');

SET @query = IF(@col_exists = 0,
    'ALTER TABLE participants ADD COLUMN position_attente INT NULL AFTER inscription_date',
    'SELECT "Colonne position_attente existe déjà" as info');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- =============================================
-- ÉTAPE 7 : Modifier le ENUM de status
-- =============================================
ALTER TABLE participants 
MODIFY COLUMN status ENUM('En lice', 'Éliminé', 'Liste d\'attente') DEFAULT 'En lice';

-- =============================================
-- ÉTAPE 8 : Supprimer les anciens triggers (si ils existent)
-- =============================================
DROP TRIGGER IF EXISTS before_participant_insert;
DROP TRIGGER IF EXISTS after_participant_delete;

-- =============================================
-- ÉTAPE 9 : Créer les triggers
-- =============================================
DELIMITER $$

CREATE TRIGGER before_participant_insert
BEFORE INSERT ON participants
FOR EACH ROW
BEGIN
    DECLARE current_count INT;
    DECLARE max_count INT;
    DECLARE min_age INT;
    
    -- Récupérer les paramètres du tournoi
    SELECT max_participants, age_minimum INTO max_count, min_age
    FROM tournaments
    WHERE id = NEW.tournament_id;
    
    -- Vérifier l'âge minimum
    IF NEW.age < min_age THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Âge minimum non atteint pour ce tournoi';
    END IF;
    
    -- Récupérer le nombre actuel de participants "En lice"
    SELECT COUNT(*) INTO current_count
    FROM participants
    WHERE tournament_id = NEW.tournament_id
    AND status = 'En lice';
    
    -- Si le tournoi est complet, mettre en liste d'attente
    IF current_count >= max_count THEN
        SET NEW.status = 'Liste d\'attente';
        
        -- Calculer la position dans la file d'attente
        SELECT COALESCE(MAX(position_attente), 0) + 1 INTO NEW.position_attente
        FROM participants
        WHERE tournament_id = NEW.tournament_id
        AND status = 'Liste d\'attente';
    ELSE
        SET NEW.status = 'En lice';
        SET NEW.position_attente = NULL;
    END IF;
END$$

CREATE TRIGGER after_participant_delete
AFTER DELETE ON participants
FOR EACH ROW
BEGIN
    DECLARE next_participant_id INT;
    
    -- Si c'était un participant "En lice", promouvoir le premier de la liste
    IF OLD.status = 'En lice' THEN
        -- Trouver le premier de la liste d'attente
        SELECT id INTO next_participant_id
        FROM participants
        WHERE tournament_id = OLD.tournament_id
        AND status = 'Liste d\'attente'
        ORDER BY position_attente ASC
        LIMIT 1;
        
        -- Le promouvoir si trouvé
        IF next_participant_id IS NOT NULL THEN
            UPDATE participants
            SET status = 'En lice', position_attente = NULL
            WHERE id = next_participant_id;
            
            -- Réorganiser les positions
            SET @pos = 0;
            UPDATE participants
            SET position_attente = (@pos := @pos + 1)
            WHERE tournament_id = OLD.tournament_id
            AND status = 'Liste d\'attente'
            ORDER BY inscription_date ASC;
        END IF;
    END IF;
END$$

DELIMITER ;

-- =============================================
-- AFFICHER LE RÉSULTAT
-- =============================================
SELECT '========================================' as '';
SELECT '✅ CORRECTION AUTOMATIQUE TERMINÉE !' as '';
SELECT '========================================' as '';

SELECT 'Tables créées/vérifiées :' as info;
SELECT CONCAT('  ✅ users : ', COUNT(*), ' utilisateur(s)') as '' FROM users;
SELECT '  ✅ sessions' as '';
SELECT '  ✅ activity_logs' as '';

SELECT '' as '';
SELECT 'Comptes utilisateurs :' as info;
SELECT CONCAT('  👑 ', username, ' (', role, ')') as compte FROM users ORDER BY role DESC;

SELECT '' as '';
SELECT 'Triggers créés :' as info;
SELECT CONCAT('  ⚡ ', TRIGGER_NAME) as trigger_name 
FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = 'tournament_db';

SELECT '' as '';
SELECT 'Paramètres des tournois :' as info;
SELECT CONCAT('  ', icon, ' ', name, ' : ', max_participants, ' places, ', age_minimum, '+ ans') as config
FROM tournaments;

SELECT '' as '';
SELECT '========================================' as '';
SELECT '🎉 VOUS POUVEZ MAINTENANT VOUS CONNECTER !' as '';
SELECT '========================================' as '';
SELECT '' as '';
SELECT 'URL : http://192.168.1.182/tournament-project/frontend/login.html' as '';
SELECT 'Username : admin' as '';
SELECT 'Password : Admin123!' as '';
SELECT '' as '';
