drop table transcript_items, prerequisites, required_textbooks, offerings,
    professors, textbooks, rooms, courses, users;

create table users (
    id INT,
    name VARCHAR(50),
    password VARCHAR(20),
    status ENUM('student', 'faculty'),

    PRIMARY KEY(id)
);

create table courses (
    course_code VARCHAR(15),
    offered ENUM('spring', 'fall', 'both'),
    dept VARCHAR(10),
    short_name VARCHAR(10),
    name VARCHAR(100),

    PRIMARY KEY(course_code),
    UNIQUE INDEX (short_name),
    UNIQUE INDEX (name)
);

create table rooms (
    number INT,
    seats INT,

    PRIMARY KEY(number)
);

create table textbooks (
    id INT AUTO_INCREMENT,
    name VARCHAR(100),

    PRIMARY KEY(id),
    UNIQUE INDEX (name)
);

create table professors (
    id INT AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(50),
    dept VARCHAR(10),

    PRIMARY KEY(id),
    KEY (user_id),
    UNIQUE INDEX (user_id),

    FOREIGN KEY(user_id) REFERENCES users(id)
);

create table offerings (
    id INT AUTO_INCREMENT,
    course_code VARCHAR(15),
    semester ENUM('spring', 'fall'),
    year INT,
    hours INT,
    time VARCHAR(10),
    professor_id INT,
    room INT,
    max_students INT,
    current_students INT,

    PRIMARY KEY(id),
    UNIQUE INDEX (course_code, semester, year),

    FOREIGN KEY(course_code) REFERENCES courses(course_code),
    FOREIGN KEY(professor_id) REFERENCES professors(id),
    FOREIGN KEY(room) REFERENCES rooms(number)
);

create table required_textbooks (
    offering_id INT,
    textbook_id INT,

    PRIMARY KEY(offering_id, textbook_id),

    FOREIGN KEY(offering_id) REFERENCES offerings(id),
    FOREIGN KEY(textbook_id) REFERENCES textbooks(id)
);

create table prerequisites (
    offering_id INT,
    course_code VARCHAR(15),

    PRIMARY KEY(offering_id, course_code),

    FOREIGN KEY(offering_id) REFERENCES offerings(id),
    FOREIGN KEY(course_code) REFERENCES courses(course_code)
);

create table transcript_items (
    user_id INT,
    offering_id INT,
    grade ENUM('A','B','C','D','F','I'),

    PRIMARY KEY (user_id,offering_id),

    FOREIGN KEY (user_id) references users(id),
    FOREIGN KEY (offering_id) references offerings(id)
);
