<?php
// Script to add online_exam_student_responses table to the database

require_once __DIR__ . '/../config/database.php';

try {
    // Create online_exam_student_responses table
    $sql = "CREATE TABLE IF NOT EXISTS `online_exam_student_responses` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `online_exam_id` int(11) NOT NULL,
        `student_id` int(11) NOT NULL,
        `question_id` int(11) NOT NULL,
        `selected_option` varchar(5) DEFAULT NULL,
        `written_answer` text DEFAULT NULL,
        `marks_obtained` int(11) DEFAULT 0,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
        `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`),
        UNIQUE KEY `unique_student_question` (`online_exam_id`, `student_id`, `question_id`),
        KEY `online_exam_id` (`online_exam_id`),
        KEY `student_id` (`student_id`),
        KEY `question_id` (`question_id`),
        CONSTRAINT `online_exam_student_responses_ibfk_1` FOREIGN KEY (`online_exam_id`) REFERENCES `online_exams` (`id`) ON DELETE CASCADE,
        CONSTRAINT `online_exam_student_responses_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
        CONSTRAINT `online_exam_student_responses_ibfk_3` FOREIGN KEY (`question_id`) REFERENCES `online_exam_questions` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

    $pdo->exec($sql);
    
    echo "Table 'online_exam_student_responses' created successfully!\n";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

?>