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

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

try {
    // Create exam_questions table
    $sql = "CREATE TABLE IF NOT EXISTS `exam_questions` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `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,
        `chapter_topic` varchar(255) DEFAULT NULL,
        `difficulty_level` enum('easy','medium','hard') DEFAULT 'medium',
        `is_starred` tinyint(1) 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`),
        KEY `exam_id` (`exam_id`),
        KEY `subject_id` (`subject_id`),
        CONSTRAINT `exam_questions_ibfk_1` FOREIGN KEY (`exam_id`) REFERENCES `exams` (`id`) ON DELETE CASCADE,
        CONSTRAINT `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 'exam_questions' created successfully!\n";
    
    // Create question_bank table
    $sql = "CREATE TABLE IF NOT EXISTS `question_bank` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `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,
        `chapter_topic` varchar(255) DEFAULT NULL,
        `difficulty_level` enum('easy','medium','hard') DEFAULT 'medium',
        `is_starred` tinyint(1) DEFAULT 0,
        `created_by` int(11) NOT 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 `subject_id` (`subject_id`),
        KEY `created_by` (`created_by`),
        CONSTRAINT `question_bank_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE,
        CONSTRAINT `question_bank_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

?>