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

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

try {
    // Create online_exams table
    $sql = "CREATE TABLE IF NOT EXISTS `online_exams` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(100) NOT NULL,
        `class_id` int(11) NOT NULL,
        `subject_id` int(11) NOT NULL,
        `exam_date` date NOT NULL,
        `start_time` time DEFAULT NULL,
        `end_time` time DEFAULT NULL,
        `duration` int(11) DEFAULT 60 COMMENT 'Duration in minutes',
        `total_marks` int(11) DEFAULT 100,
        `pass_marks` int(11) DEFAULT 40,
        `status` enum('scheduled','ongoing','completed','cancelled') DEFAULT 'scheduled',
        `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 `class_id` (`class_id`),
        KEY `subject_id` (`subject_id`),
        KEY `created_by` (`created_by`),
        CONSTRAINT `online_exams_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ON DELETE CASCADE,
        CONSTRAINT `online_exams_ibfk_2` FOREIGN KEY (`subject_id`) REFERENCES `subjects` (`id`) ON DELETE CASCADE,
        CONSTRAINT `online_exams_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

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

?>