-- =============================================
-- SOLUTION ULTIME - Suppression et recréation complète
-- =============================================

USE tournament_db;

-- SUPPRIMER L'ANCIENNE TABLE USERS

-- CRÉER LA NOUVELLE TABLE USERS AVEC TOUTES LES COLONNES
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;

-- CRÉER LES COMPTES AVEC LE BON MOT DE PASSE
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'),
('operateur', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test', 'Opérateur', 'operateur@tournois.local', 'operator');

-- CRÉER LA 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;

-- CRÉER LA 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;

-- Ajouter les colonnes manquantes aux tournois (si elles n'existent pas)
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
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';

-- Ajouter position_attente aux participants
ALTER TABLE participants 
ADD COLUMN IF NOT EXISTS position_attente INT NULL AFTER inscription_date;

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

-- AFFICHER LE RÉSULTAT
SELECT '========================================' as '';
SELECT '✅ RÉPARATION TERMINÉE !' as '';
SELECT '========================================' as '';
SELECT '' as '';
SELECT 'Vérification des comptes :' as '';
SELECT username, role, 'Mot de passe: Admin123!' as password FROM users;
SELECT '' as '';
SELECT '✅ Vous pouvez maintenant vous connecter !' as '';
SELECT 'URL: http://192.168.1.182/tournament-project/frontend/login.html' as '';
SELECT 'Username: admin' as '';
SELECT 'Password: Admin123!' as '';
