-- Migration: Ajouter les colonnes de validation manquantes
-- Date: 2026-01-04
-- Description: Ajoute les colonnes quiz_passed, validated_at et last_position
--              à la table v2_student_sessions pour permettre la persistence
--              de la validation des séances

-- Vérifier si les colonnes existent déjà avant de les ajouter
SET @dbname = DATABASE();
SET @tablename = 'v2_student_sessions';

-- Ajouter quiz_passed si elle n'existe pas
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'quiz_passed';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE v2_student_sessions ADD COLUMN quiz_passed TINYINT(1) DEFAULT 0 AFTER is_completed',
    'SELECT "Column quiz_passed already exists" AS msg');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ajouter validated_at si elle n'existe pas
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'validated_at';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE v2_student_sessions ADD COLUMN validated_at DATETIME DEFAULT NULL AFTER completed_at',
    'SELECT "Column validated_at already exists" AS msg');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Ajouter last_position si elle n'existe pas
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND COLUMN_NAME = 'last_position';

SET @query = IF(@col_exists = 0,
    'ALTER TABLE v2_student_sessions ADD COLUMN last_position VARCHAR(255) DEFAULT NULL AFTER validated_at',
    'SELECT "Column last_position already exists" AS msg');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Créer un index sur validated_at pour les requêtes de filtrage
SET @index_exists = 0;
SELECT COUNT(*) INTO @index_exists 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = @dbname 
AND TABLE_NAME = @tablename 
AND INDEX_NAME = 'idx_validated_at';

SET @query = IF(@index_exists = 0,
    'ALTER TABLE v2_student_sessions ADD INDEX idx_validated_at (validated_at)',
    'SELECT "Index idx_validated_at already exists" AS msg');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Mettre à jour les données existantes
-- Les sessions déjà complétées avec quiz réussi doivent avoir quiz_passed = 1
UPDATE v2_student_sessions ss
INNER JOIN v2_sessions s ON ss.session_id = s.id
INNER JOIN v2_quizzes q ON q.session_id = s.id
INNER JOIN v2_quiz_results qr ON qr.quiz_id = q.id AND qr.student_id = ss.student_id
SET 
    ss.quiz_passed = 1,
    ss.validated_at = COALESCE(ss.completed_at, qr.completed_at, NOW())
WHERE 
    ss.is_completed = 1 
    AND qr.passed = 1
    AND ss.quiz_passed IS NULL;

-- Afficher un résumé des changements
SELECT 
    'Migration terminée avec succès' AS status,
    (SELECT COUNT(*) FROM v2_student_sessions WHERE quiz_passed = 1) AS sessions_validees,
    (SELECT COUNT(*) FROM v2_student_sessions WHERE validated_at IS NOT NULL) AS sessions_avec_date_validation;
