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

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

try {
    // Create online_exam_questions table
    $sql = "CREATE TABLE IF NOT EXISTS `online_exam_questions` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `online_exam_id` int(11) NOT NULL,
        `subject_id` int(11) NOT NULL,
        `question_text` text NOT NULL,
        `question_type` enum('mcq','short','long') DEFAULT 'mcq',
        `option_a` text DEFAULT NULL,
        `option_b` text DEFAULT NULL,
        `option_c` text DEFAULT NULL,
        `option_d` text DEFAULT NULL,
        `correct_answer` varchar(5) DEFAULT NULL,
        `marks` int(11) DEFAULT 1,
        `question_order` int(11) DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
        `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`),
        KEY `online_exam_id` (`online_exam_id`),
        KEY `subject_id` (`subject_id`),
        CONSTRAINT `online_exam_questions_ibfk_1` FOREIGN KEY (`online_exam_id`) REFERENCES `online_exams` (`id`) ON DELETE CASCADE,
        CONSTRAINT `online_exam_questions_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

?>