<?php
require_once 'config/database.php';

try {
    $pdo = getDB();

    // Create exam_attempts table to track student exam attempts
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS exam_attempts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            exam_id INT NOT NULL,
            student_id INT NOT NULL,
            attempt_number INT NOT NULL DEFAULT 1,
            status ENUM('in_progress', 'completed', 'abandoned') NOT NULL DEFAULT 'in_progress',
            started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at TIMESTAMP NULL,
            time_spent INT DEFAULT 0, -- in seconds
            answers JSON,
            score DECIMAL(5,2) DEFAULT 0.00,
            max_score DECIMAL(5,2) DEFAULT 0.00,
            percentage DECIMAL(5,2) DEFAULT 0.00,
            is_passed BOOLEAN DEFAULT FALSE,
            ip_address VARCHAR(45),
            browser_info JSON,
            last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            feedback TEXT,
            graded_at TIMESTAMP NULL,
            graded_by INT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_exam_student_attempt (exam_id, student_id, attempt_number),
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (graded_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Add indexes for better performance
    $pdo->exec("CREATE INDEX idx_exam_attempts_exam ON exam_attempts (exam_id)");
    $pdo->exec("CREATE INDEX idx_exam_attempts_student ON exam_attempts (student_id)");
    $pdo->exec("CREATE INDEX idx_exam_attempts_status ON exam_attempts (status)");
    $pdo->exec("CREATE INDEX idx_exam_attempts_started ON exam_attempts (started_at)");

    echo "Exam attempts migration applied successfully.";
} catch (PDOException $e) {
    echo "Error applying exam attempts migration: " . $e->getMessage();
}
?>