CREATE DATABASE IF NOT EXISTS classroom_reservation;

USE classroom_reservation;

CREATE TABLE IF NOT EXISTS admin (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(250) NULL,
    username VARCHAR(250) NULL,
    password VARCHAR(250) NULL
);

INSERT INTO admin (name, username, password)
VALUES ('admin', 'admin', 'admin');


CREATE TABLE IF NOT EXISTS faculty (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_no VARCHAR(250) NULL,
    faculty_name VARCHAR(250) NULL,
    status VARCHAR(250) NULL
);

CREATE TABLE IF NOT EXISTS room (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_code VARCHAR(250) NULL,
    room_name VARCHAR(250) NULL,
    room_information VARCHAR(250) NULL,
    type ENUM('Laboratory', 'Lecture', 'Other') NULL,
    status VARCHAR(250) NULL
);


CREATE TABLE IF NOT EXISTS curriculum (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(250) NULL,
    track VARCHAR(250) NULL,
    code VARCHAR(250) NULL,
    status VARCHAR(250) NULL
);

CREATE TABLE IF NOT EXISTS subject (
    id INT AUTO_INCREMENT PRIMARY KEY,
    curriculum_id INT NULL,
    track_id VARCHAR(250) NULL,
    subject_code VARCHAR(250) NULL,
    subject_description VARCHAR(250) NULL,
    semester VARCHAR(100) NULL,
    lab INT NULL,
    lec INT NULL,
    year VARCHAR(100) NULL,
    pre_requisite VARCHAR(250) NULL
);

CREATE TABLE IF NOT EXISTS section (
    id INT AUTO_INCREMENT PRIMARY KEY,
    curriculum_id INT NULL,
    section_name VARCHAR(100) NULL,
    year_level VARCHAR(100) NULL,
    status VARCHAR(100) NULL
);

CREATE TABLE IF NOT EXISTS school_year (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sy VARCHAR(100) NULL,
    status VARCHAR(100) NULL
);


CREATE TABLE approval (
    id INT AUTO_INCREMENT PRIMARY KEY,
    curriculum_id INT NULL,
    name VARCHAR(100) NULL,
    degree VARCHAR(50),        -- For "PhD", "DIT", "Deng", etc.
    position VARCHAR(100) NULL,
    type ENUM('Prepared by','Checked by','Verified by') NOT NULL  -- role type
);


CREATE TABLE IF NOT EXISTS schedule (
    id INT AUTO_INCREMENT PRIMARY KEY,
    days VARCHAR(250) NULL,
    faculty_id VARCHAR(250) NULL,
    curriculum_id VARCHAR(250) NULL,
    section_id VARCHAR(250) NULL,
    year_level VARCHAR(250) NULL,
    semester VARCHAR(250) NULL,     
    subject_id VARCHAR(250) NULL,
    start_time VARCHAR(250) NULL,
    end_time VARCHAR(250) NULL,
    room_id VARCHAR(250) NULL,
    `date` DATE DEFAULT CURRENT_DATE
);