-- =============================================
-- MIGRATION : Ajout des fonctionnalités avancées
-- =============================================
-- 1. Limite de participants + file d'attente
-- 2. Âge minimum par tournoi
-- 3. Système d'authentification

USE tournament_db;

-- =============================================
-- TABLE DES UTILISATEURS (Connexion)
-- =============================================
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;

-- Créer un compte admin par défaut
-- Username: admin
-- Password: Admin123!
-- Hash généré avec password_hash()
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')
ON DUPLICATE KEY UPDATE username = username;

-- Créer un compte opérateur de test
-- Username: operateur
-- 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')
ON DUPLICATE KEY UPDATE username = username;

-- =============================================
-- TABLE DES 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;

-- =============================================
-- Ajouter max_participants et age_minimum aux tournois
-- =============================================
ALTER TABLE tournaments 
ADD COLUMN IF NOT EXISTS max_participants INT NOT NULL DEFAULT 32 AFTER description;

ALTER TABLE tournaments 
ADD COLUMN IF NOT EXISTS age_minimum INT NOT NULL DEFAULT 0 AFTER max_participants;

-- Mettre à jour les valeurs pour les tournois existants
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';

-- =============================================
-- S'assurer que la colonne position_attente existe
-- =============================================
ALTER TABLE participants 
ADD COLUMN IF NOT EXISTS position_attente INT NULL AFTER inscription_date;

-- =============================================
-- Modifier le ENUM du statut pour inclure "Liste d'attente"
-- =============================================
ALTER TABLE participants 
MODIFY COLUMN status ENUM('En lice', 'Éliminé', 'Liste d\'attente') DEFAULT 'En lice';

-- =============================================
-- TABLE DES LOGS D'ACTIVITÉ
-- =============================================
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;

-- =============================================
-- TRIGGER : Gérer automatiquement la file d'attente
-- =============================================
DELIMITER $$

DROP TRIGGER IF EXISTS before_participant_insert$$

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$$

DELIMITER ;

-- =============================================
-- TRIGGER : Promouvoir automatiquement de la liste d'attente
-- =============================================
DELIMITER $$

DROP TRIGGER IF EXISTS after_participant_delete$$

CREATE TRIGGER after_participant_delete
AFTER DELETE ON participants
FOR EACH ROW
BEGIN
    -- Si c'était un participant "En lice", promouvoir le premier de la liste
    IF OLD.status = 'En lice' THEN
        UPDATE participants
        SET status = 'En lice', position_attente = NULL
        WHERE tournament_id = OLD.tournament_id
        AND status = 'Liste d\'attente'
        ORDER BY position_attente ASC
        LIMIT 1;
        
        -- Réorganiser les positions dans la file d'attente
        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$$

DELIMITER ;

-- =============================================
-- PROCÉDURE : Mettre à jour la file d'attente manuellement
-- =============================================
DELIMITER $$

DROP PROCEDURE IF EXISTS reorganize_waiting_list$$

CREATE PROCEDURE reorganize_waiting_list(IN p_tournament_id VARCHAR(50))
BEGIN
    SET @pos = 0;
    UPDATE participants
    SET position_attente = (@pos := @pos + 1)
    WHERE tournament_id = p_tournament_id
    AND status = 'Liste d\'attente'
    ORDER BY inscription_date ASC;
END$$

DELIMITER ;

-- =============================================
-- VUE : Statistiques des tournois
-- =============================================
CREATE OR REPLACE VIEW tournament_stats AS
SELECT 
    t.id,
    t.name,
    t.max_participants,
    t.age_minimum,
    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 = 'Éliminé' THEN 1 ELSE 0 END) as elimines,
    SUM(CASE WHEN p.status = 'Liste d\'attente' THEN 1 ELSE 0 END) as liste_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
WHERE t.status = 'active'
GROUP BY t.id, t.name, t.max_participants, t.age_minimum;

-- =============================================
-- INDEX POUR OPTIMISATION
-- =============================================
CREATE INDEX IF NOT EXISTS idx_participants_tournament_status ON participants(tournament_id, status);
CREATE INDEX IF NOT EXISTS idx_participants_attente ON participants(tournament_id, position_attente);
CREATE INDEX IF NOT EXISTS idx_participants_age ON participants(age);

-- =============================================
-- AFFICHER LE RÉSUMÉ
-- =============================================
SELECT '========================================' as '';
SELECT '✅ MIGRATION COMPLÈTE' as '';
SELECT '========================================' as '';

SELECT 'Tables créées :' as info;
SELECT '  - users (authentification)' as '';
SELECT '  - sessions (gestion des sessions)' as '';
SELECT '  - activity_logs (historique)' as '';
SELECT '' as '';

SELECT 'Colonnes ajoutées aux tournois :' as info;
SELECT '  - max_participants (limite)' as '';
SELECT '  - age_minimum (restriction d\'âge)' as '';
SELECT '' as '';

SELECT 'Triggers créés :' as info;
SELECT '  - before_participant_insert (file d\'attente auto)' as '';
SELECT '  - after_participant_delete (promotion auto)' as '';
SELECT '' as '';

SELECT 'Comptes créés :' as info;
SELECT '  - admin / Admin123!' as '';
SELECT '  - operateur / Oper123!' as '';
SELECT '' as '';

SELECT '========================================' as '';
SELECT 'MIGRATION TERMINÉE AVEC SUCCÈS !' as '';
SELECT '========================================' as '';
