
CREATE TABLE IF NOT EXISTS classes (
    class_id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

ALTER TABLE users ADD COLUMN class_id INT DEFAULT NULL;
ALTER TABLE users ADD CONSTRAINT fk_user_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL;
ALTER TABLE users ADD COLUMN full_name VARCHAR(100) DEFAULT NULL;

ALTER TABLE questions ADD COLUMN option_e VARCHAR(255) DEFAULT NULL AFTER option_d;
ALTER TABLE questions ADD COLUMN image_path VARCHAR(255) DEFAULT NULL AFTER question_text;
ALTER TABLE questions ADD COLUMN passage_id INT DEFAULT NULL AFTER image_path;
ALTER TABLE questions ADD COLUMN media_id INT DEFAULT NULL AFTER passage_id;
ALTER TABLE questions ADD COLUMN session VARCHAR(20) DEFAULT NULL;
ALTER TABLE questions ADD COLUMN term VARCHAR(20) DEFAULT NULL;
ALTER TABLE questions MODIFY COLUMN correct_answer ENUM('A', 'B', 'C', 'D', 'E') NOT NULL;

ALTER TABLE exams ADD COLUMN class_id INT DEFAULT NULL;
ALTER TABLE exams ADD COLUMN subject_id INT DEFAULT NULL;
ALTER TABLE exams ADD COLUMN randomize_questions TINYINT(1) DEFAULT 1;
ALTER TABLE exams ADD COLUMN session VARCHAR(20) DEFAULT NULL;
ALTER TABLE exams ADD COLUMN term VARCHAR(20) DEFAULT NULL;
ALTER TABLE exams ADD COLUMN question_quota INT DEFAULT NULL;
ALTER TABLE exams ADD COLUMN show_result_to_student TINYINT(1) DEFAULT 0;
ALTER TABLE exams ADD CONSTRAINT fk_exam_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL;
ALTER TABLE exams ADD CONSTRAINT fk_exam_subject FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS passages (
    passage_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) DEFAULT NULL,
    content TEXT NOT NULL,
    subject_id INT DEFAULT NULL,
    class_id INT DEFAULT NULL,
    session VARCHAR(20) DEFAULT NULL,
    term VARCHAR(20) DEFAULT NULL,
    FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE SET NULL,
    FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS media_library (
    media_id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255) NOT NULL,
    caption VARCHAR(255) DEFAULT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ALTER TABLE questions ADD CONSTRAINT fk_q_passage FOREIGN KEY (passage_id) REFERENCES passages(passage_id) ON DELETE SET NULL;
ALTER TABLE questions ADD CONSTRAINT fk_q_media FOREIGN KEY (media_id) REFERENCES media_library(media_id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS exam_assignments (
    assignment_id INT AUTO_INCREMENT PRIMARY KEY,
    exam_id INT NOT NULL,
    user_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES exams(exam_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    UNIQUE KEY unique_assignment (exam_id, user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
    setting_id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('school_name', 'My School CBT'), ('school_logo', '');
