table_create.sql

CREATE TABLE faculties (
    faculty_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('QA_Admin', 'Admin', 'Dean', 'Instructor', 'Module_Leader', 'Student') NOT NULL,
    level INT DEFAULT NULL,
    faculty_id INT DEFAULT NULL,
    university_id VARCHAR(100) DEFAULT NULL,
    university_id_file_path VARCHAR(255) DEFAULT NULL,
    email_verification_token VARCHAR(255) DEFAULT NULL,
    email_verified_at TIMESTAMP NULL DEFAULT NULL,
    approval_status ENUM('Pending', 'Approved', 'Rejected') NOT NULL DEFAULT 'Approved',
    approval_reviewed_at TIMESTAMP NULL DEFAULT NULL,
    approval_message TEXT DEFAULT NULL,
    pending_password_plain VARCHAR(255) DEFAULT NULL,
    password_changed_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (faculty_id) REFERENCES faculties(faculty_id) ON DELETE SET NULL,
    INDEX idx_faculty_id (faculty_id),
    INDEX idx_role_faculty (role, faculty_id),
    INDEX idx_role (role),
    INDEX idx_student_verification (role, email_verified_at, approval_status),
    INDEX idx_university_id (university_id)
);



CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    level INT NOT NULL,
    semesters JSON DEFAULT NULL,
    faculty_id INT,
    module_leader_id INT,
    FOREIGN KEY (faculty_id) REFERENCES faculties(faculty_id) ON DELETE CASCADE,
    FOREIGN KEY (module_leader_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_faculty_id (faculty_id),
    INDEX idx_module_leader_id (module_leader_id)
);

CREATE TABLE course_students (
    course_id INT,
    student_id INT,
    PRIMARY KEY (course_id, student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE course_instructors (
    course_id INT,
    instructor_id INT,
    PRIMARY KEY (course_id, instructor_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    FOREIGN KEY (instructor_id) REFERENCES users(user_id) ON DELETE CASCADE
);


CREATE TABLE appeal_sessions (
    session_id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(100) NOT NULL,
    term ENUM('Spring', 'Summer', 'Fall') NOT NULL,
    academic_year INT NOT NULL,
    max_appeals_per_student INT DEFAULT 1,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL, 
    status ENUM('Open', 'Closed') DEFAULT 'Open',
    activeLevels JSON Default ['L1', 'L2', 'L3', 'L4'],
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE TABLE exempt_Courses_Session (
    session_id INT NOT NULL,
    course_id INT NOT NULL,
    PRIMARY KEY (session_id, course_id),
    FOREIGN KEY (session_id) REFERENCES appeal_sessions(session_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);


CREATE TABLE exempt_Faculties_Session (
    session_id INT NOT NULL,
    faculty_id INT NOT NULL,
    PRIMARY KEY (session_id, faculty_id),
    FOREIGN KEY (session_id) REFERENCES appeal_sessions(session_id) ON DELETE CASCADE,
    FOREIGN KEY (faculty_id) REFERENCES faculties(faculty_id) ON DELETE CASCADE
);

CREATE TABLE grade_appeals (
    appeal_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    session_id INT NOT NULL,
    assigned_instructor_id INT DEFAULT NULL,
    original_grade VARCHAR(10) NOT NULL,
    new_grade VARCHAR(10) DEFAULT NULL,
    reason TEXT NOT NULL,
    note TEXT,
    status ENUM('Pending', 'Under Review', 'Resolved', 'Rejected') DEFAULT 'Pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (student_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES appeal_sessions(session_id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_instructor_id) REFERENCES users(user_id) ON DELETE SET NULL
);


CREATE TABLE surveys (
    survey_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    qr_code VARCHAR(255) DEFAULT NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NOT NULL,
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE TABLE course_surveys (
    survey_id INT,
    course_id INT,
    PRIMARY KEY (survey_id, course_id),
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

CREATE TABLE survey_questions (
    question_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    question_text TEXT NOT NULL,
    options JSON DEFAULT NULL,
    is_required BOOLEAN DEFAULT TRUE,
    display_order INT DEFAULT 0,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE
);

CREATE TABLE survey_responses (
    response_id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    student_id INT NOT NULL,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (survey_id) REFERENCES surveys(survey_id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE answers (
    answer_id INT AUTO_INCREMENT PRIMARY KEY,
    response_id INT NOT NULL,
    question_id INT NOT NULL,
    answer_text TEXT NOT NULL,
    FOREIGN KEY (response_id) REFERENCES survey_responses(response_id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES survey_questions(question_id) ON DELETE CASCADE
);

CREATE TABLE notifications (
    notification_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    sender_id INT DEFAULT NULL,
    sender_type VARCHAR(100),
    receiver_id INT NOT NULL,
    notify_by_email BOOLEAN DEFAULT FALSE,
    is_read BOOLEAN DEFAULT FALSE, 
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (receiver_id) REFERENCES users(user_id) ON DELETE CASCADE
);

CREATE TABLE support_tickets (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,
    user_name VARCHAR(255),  
    user_email VARCHAR(255),
    message TEXT NOT NULL,
    status ENUM('Open', 'In Progress', 'Resolved') DEFAULT 'Open',
    type VARCHAR(50) NOT NULL,
    priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
);


CREATE TABLE password_resets (
    email VARCHAR(255) NOT NULL,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    is_used BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (token),
    INDEX (email),
    FOREIGN KEY (email) REFERENCES users(email) ON DELETE CASCADE
);

CREATE TABLE password_history (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);


CREATE TABLE files (
    file_id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    num_rows INT DEFAULT NULL,
    num_cols INT DEFAULT NULL,
    uploaded_by INT DEFAULT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (uploaded_by) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE TABLE instructor_profiles (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    instructor_id INT NOT NULL UNIQUE,
    bio TEXT,
    office_location VARCHAR(255),
    office_hours VARCHAR(255),
    phone_number VARCHAR(20),
    email VARCHAR(255),
    research_interests TEXT,
    qualifications TEXT,
    photo_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (instructor_id) REFERENCES users(user_id) ON DELETE CASCADE
);