
CREATE DATABASE IF NOT EXISTS exam_system;
USE exam_system;
CREATE TABLE IF NOT EXISTS admins (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) UNIQUE, password VARCHAR(255));
CREATE TABLE IF NOT EXISTS teachers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150), username VARCHAR(100) UNIQUE, password VARCHAR(255), created_by INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS classes (id INT AUTO_INCREMENT PRIMARY KEY, class_name VARCHAR(100) UNIQUE);
CREATE TABLE IF NOT EXISTS students (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150), regno VARCHAR(100) UNIQUE, class_id INT, password VARCHAR(255), teacher_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS subjects (id INT AUTO_INCREMENT PRIMARY KEY, subject_name VARCHAR(150));
CREATE TABLE IF NOT EXISTS exams (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200), subject_id INT, teacher_id INT, status ENUM('pending','active','stopped') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS exam_classes (id INT AUTO_INCREMENT PRIMARY KEY, exam_id INT, class_id INT, duration INT);
CREATE TABLE IF NOT EXISTS questions (id INT AUTO_INCREMENT PRIMARY KEY, exam_id INT, question_text TEXT, option_a VARCHAR(255), option_b VARCHAR(255), option_c VARCHAR(255), option_d VARCHAR(255), correct_answer CHAR(1), image_path VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS results (id INT AUTO_INCREMENT PRIMARY KEY, exam_id INT, student_id INT, score INT, submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS uploads (id INT AUTO_INCREMENT PRIMARY KEY, path VARCHAR(255), uploaded_by INT, role VARCHAR(20), filename VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT IGNORE INTO classes(class_name) VALUES ('Class A'),('Class B');
INSERT IGNORE INTO subjects(subject_name) VALUES ('Mathematics');
