-- Script de création des tables manquantes
-- Exécutez ce script sur la base de données kuofkculearn

-- Table v2_student_sessions (suivi des sessions par étudiant)
CREATE TABLE IF NOT EXISTS `v2_student_sessions` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `student_id` VARCHAR(36) NOT NULL,
  `session_id` VARCHAR(36) NOT NULL,
  `status` ENUM('available', 'in_progress', 'completed') DEFAULT 'available',
  `is_completed` TINYINT(1) DEFAULT 0,
  `started_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `progress` DECIMAL(5,2) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_student_session` (`student_id`, `session_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table v2_student_tests (suivi des tests par étudiant)
CREATE TABLE IF NOT EXISTS `v2_student_tests` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `student_id` VARCHAR(36) NOT NULL,
  `test_id` VARCHAR(36) NOT NULL,
  `status` ENUM('available', 'in_progress', 'completed', 'failed') DEFAULT 'available',
  `score` DECIMAL(5,2) NULL,
  `started_at` DATETIME NULL,
  `completed_at` DATETIME NULL,
  `attempts` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_student_test` (`student_id`, `test_id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table v2_training_tests (tests associés aux formations)
CREATE TABLE IF NOT EXISTS `v2_training_tests` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `formation_id` VARCHAR(36) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `type` ENUM('quiz', 'exam', 'practice') DEFAULT 'quiz',
  `duration_minutes` INT DEFAULT 30,
  `passing_score` DECIMAL(5,2) DEFAULT 70,
  `max_attempts` INT DEFAULT 3,
  `is_active` TINYINT(1) DEFAULT 1,
  `order_index` INT DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_formation` (`formation_id`),
  INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table v2_certificates (certificats obtenus)
CREATE TABLE IF NOT EXISTS `v2_certificates` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `student_id` VARCHAR(36) NOT NULL,
  `formation_id` VARCHAR(36) NOT NULL,
  `certificate_number` VARCHAR(50) UNIQUE,
  `issued_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `pdf_url` VARCHAR(500) NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_student` (`student_id`),
  INDEX `idx_formation` (`formation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table v2_sessions (sessions de formation - si elle n'existe pas)
CREATE TABLE IF NOT EXISTS `v2_sessions` (
  `id` VARCHAR(36) NOT NULL PRIMARY KEY,
  `formation_id` VARCHAR(36) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `type` ENUM('lesson', 'quiz', 'practice', 'live') DEFAULT 'lesson',
  `duration_minutes` INT DEFAULT 60,
  `scheduled_date` DATE NULL,
  `has_materials` TINYINT(1) DEFAULT 0,
  `order_index` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_formation` (`formation_id`),
  INDEX `idx_scheduled` (`scheduled_date`),
  INDEX `idx_order` (`order_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table v2_password_resets (pour le système de reset password)
CREATE TABLE IF NOT EXISTS `v2_password_resets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(100) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_token` (`token`),
  INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
