-- =============================================
-- BASE DE DONNÉES COMPLÈTE - VERSION FINALE
-- =============================================

USE tournament_db;

-- Supprimer les anciennes tables dans le bon ordre

-- =============================================
-- TABLE TOURNAMENTS
-- =============================================
CREATE TABLE tournaments (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    icon VARCHAR(10) NOT NULL,
    description TEXT,
    max_participants INT NOT NULL DEFAULT 32,
    age_minimum INT NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive', 'completed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insérer les tournois
INSERT INTO tournaments (id, name, icon, description, max_participants, age_minimum) VALUES
('fortnite', 'Fortnite Battle Royale', '🏗️', 'Tournoi de Battle Royale', 32, 16),
('mariokart', 'Mario Kart 8 Deluxe', '🏎️', 'Course de kart arcade', 24, 12),
('smash', 'Super Smash Bros Ultimate', '⚔️', 'Jeu de combat', 16, 13),
('fc26', 'FC 26', '⚽', 'Simulation de football', 20, 16);

-- =============================================
-- TABLE USERS
-- =============================================
CREATE TABLE 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;

-- =============================================
-- TABLE PARTICIPANTS
-- =============================================
CREATE TABLE participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tournament_id VARCHAR(50) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    nom VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    telephone VARCHAR(20) NOT NULL,
    status ENUM('En lice', 'Éliminé', 'Liste d\'attente') DEFAULT 'En lice',
    inscription_date DATE NOT NULL,
    position_attente INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    INDEX idx_tournament (tournament_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TABLE POOLS
-- =============================================
CREATE TABLE pools (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tournament_id VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    pool_number INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tournament_id) REFERENCES tournaments(id) ON DELETE CASCADE,
    INDEX idx_tournament (tournament_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TABLE POOL_PARTICIPANTS
-- =============================================
CREATE TABLE pool_participants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pool_id INT NOT NULL,
    participant_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pool_id) REFERENCES pools(id) ON DELETE CASCADE,
    FOREIGN KEY (participant_id) REFERENCES participants(id) ON DELETE CASCADE,
    UNIQUE KEY unique_participant_pool (pool_id, participant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TABLE SESSIONS
-- =============================================
CREATE TABLE 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;

-- =============================================
-- TABLE ACTIVITY_LOGS
-- =============================================
CREATE TABLE 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;

-- =============================================
-- TRIGGERS POUR FILE D'ATTENTE AUTOMATIQUE
-- =============================================
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;
    
    SELECT max_participants, age_minimum INTO max_count, min_age
    FROM tournaments WHERE id = NEW.tournament_id;
    
    IF NEW.age < min_age THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Âge minimum non atteint';
    END IF;
    
    SELECT COUNT(*) INTO current_count
    FROM participants
    WHERE tournament_id = NEW.tournament_id AND status = 'En lice';
    
    IF current_count >= max_count THEN
        SET NEW.status = 'Liste 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$$

DROP TRIGGER IF EXISTS after_participant_delete$$
CREATE TRIGGER after_participant_delete
AFTER DELETE ON participants
FOR EACH ROW
BEGIN
    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;
        
        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 ;

-- =============================================
-- DONNÉES DE TEST
-- =============================================
INSERT INTO participants (tournament_id, prenom, nom, age, telephone, inscription_date) VALUES
('fortnite', 'Jean', 'Dupont', 18, '0601020304', '2024-10-01'),
('fortnite', 'Marie', 'Martin', 20, '0605060708', '2024-10-02'),
('mariokart', 'Pierre', 'Durand', 15, '0609101112', '2024-10-01'),
('smash', 'Sophie', 'Bernard', 16, '0613141516', '2024-10-01'),
('fc26', 'Luc', 'Moreau', 19, '0617181920', '2024-10-01');

SELECT '========================================' as '';
SELECT '✅ BASE DE DONNÉES CRÉÉE AVEC SUCCÈS !' as '';
SELECT '========================================' as '';
SELECT '' as '';
SELECT 'Tournois créés:' as info;
SELECT CONCAT('  ', icon, ' ', name, ' (', max_participants, ' places, ', age_minimum, '+ ans)') as tournoi FROM tournaments;
SELECT '' as '';
SELECT 'Participants de test:' as info;
SELECT COUNT(*) as total FROM participants;
SELECT '' as '';
SELECT '⚠️ IMPORTANT: Créez maintenant les comptes utilisateurs !' as '';
SELECT 'Exécutez le fichier: fix-password.php' as '';
