CREATE TYPE campus_code AS ENUM ( 'MAIN', 'GZ' );;;
CREATE TYPE campus_name AS ENUM ( 'CWB Campus', 'GZ Campus' );;;
CREATE TYPE campus_nickname AS ENUM ( 'CWB', 'GZ' );;;
CREATE TYPE career AS ENUM ( 'UGRD', 'TPG', 'RPG', 'EXEC' );;;
CREATE TYPE career_type AS ENUM ( 'UG', 'PG' );;;
CREATE TYPE class_role AS ENUM ( 'E', 'N' );;;
CREATE TYPE class_type AS ENUM ( 'LEC', 'TUT', 'LAB', 'IND' );;;
CREATE TYPE status AS ENUM ( 'ACTIVE', 'INACTIVE' );;;
CREATE TYPE weekday AS ENUM ( 'Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat' );;;
CREATE TABLE classes(term_num INTEGER, term_code VARCHAR NOT NULL, term_name VARCHAR NOT NULL, course_id VARCHAR NOT NULL, section VARCHAR NOT NULL, number INTEGER, "role" ENUM('E', 'N') NOT NULL, "type" ENUM('LEC', 'TUT', 'LAB', 'IND') NOT NULL, association INTEGER, remarks VARCHAR NOT NULL, capacity INTEGER NOT NULL, enroll INTEGER NOT NULL, wait INTEGER NOT NULL, consent BOOLEAN NOT NULL, open BOOLEAN NOT NULL, schedules STRUCT(weekday ENUM('Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'), date_from DATE, date_to DATE, time_from TIME, time_to TIME, venue VARCHAR, venue_name VARCHAR, instructors VARCHAR[])[] NOT NULL, reservations STRUCT("name" VARCHAR, quota INTEGER, enroll INTEGER)[] NOT NULL, status ENUM('ACTIVE', 'INACTIVE') NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE, PRIMARY KEY(term_num, number, "timestamp"), UNIQUE(term_num, course_id, section, "timestamp"));;
CREATE TABLE courses(term_num INTEGER, term_code VARCHAR NOT NULL, term_name VARCHAR NOT NULL, id VARCHAR, prefix VARCHAR NOT NULL, number VARCHAR NOT NULL, career ENUM('UGRD', 'TPG', 'RPG', 'EXEC') NOT NULL, title VARCHAR NOT NULL, description VARCHAR NOT NULL, credits DOUBLE NOT NULL, previous VARCHAR NOT NULL, prerequisite VARCHAR NOT NULL, corequisite VARCHAR NOT NULL, exclusion VARCHAR NOT NULL, attributes STRUCT("label" VARCHAR, "value" VARCHAR, description VARCHAR)[] NOT NULL, status ENUM('ACTIVE', 'INACTIVE') NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE, PRIMARY KEY(term_num, id, "timestamp"), UNIQUE(term_num, prefix, number, "timestamp"));;
CREATE INDEX idx_classes_active_latest ON classes(term_num, status, number, "timestamp");;
CREATE INDEX idx_classes_course_id ON classes(course_id);;
CREATE INDEX idx_classes_course_id_section ON classes(course_id, section);;
CREATE INDEX idx_classes_latest ON classes(term_num, number, "timestamp");;
CREATE INDEX idx_classes_number ON classes(number);;
CREATE INDEX idx_classes_term_code ON classes(term_code);;
CREATE INDEX idx_classes_term_num ON classes(term_num);;
CREATE INDEX idx_courses_active_latest ON courses(term_num, status, id, "timestamp");;
CREATE INDEX idx_courses_id ON courses(id);;
CREATE INDEX idx_courses_latest ON courses(term_num, id, "timestamp");;
CREATE INDEX idx_courses_prefix ON courses(prefix);;
CREATE INDEX idx_courses_prefix_number ON courses(prefix, number);;
CREATE INDEX idx_courses_term_code ON courses(term_code);;
CREATE INDEX idx_courses_term_num ON courses(term_num);;

