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 courses(id VARCHAR, term_num INTEGER, term_code VARCHAR NOT NULL, term_name VARCHAR NOT NULL, academic_year VARCHAR NOT NULL, campus_code ENUM('MAIN', 'GZ'), campus_name ENUM('CWB Campus', 'GZ Campus') NOT NULL, campus_nickname ENUM('CWB', 'GZ') NOT NULL, department_code VARCHAR NOT NULL, department_nickname VARCHAR NOT NULL, school_code VARCHAR NOT NULL, career_code ENUM('UGRD', 'TPG', 'RPG', 'EXEC') NOT NULL, career_type ENUM('UG', 'PG') NOT NULL, prefix VARCHAR NOT NULL, number VARCHAR NOT NULL, title VARCHAR NOT NULL, min_credits DOUBLE NOT NULL, max_credits DOUBLE NOT NULL, vector VARCHAR NOT NULL, vector_display VARCHAR NOT NULL, description VARCHAR NOT NULL, previous VARCHAR NOT NULL, alternate VARCHAR NOT NULL, prerequisite VARCHAR NOT NULL, corequisite VARCHAR NOT NULL, exclusion VARCHAR NOT NULL, background VARCHAR NOT NULL, colist VARCHAR NOT NULL, equivalence VARCHAR NOT NULL, reference VARCHAR NOT NULL, attributes STRUCT("label" VARCHAR, "value" VARCHAR, description VARCHAR)[] NOT NULL, cilos STRUCT(description VARCHAR)[] NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE, status ENUM('ACTIVE', 'INACTIVE') NOT NULL, PRIMARY KEY(id, term_num, campus_code, "timestamp"));;
CREATE INDEX idx_course_code ON courses(prefix, number);;
CREATE INDEX idx_number ON courses(number);;
CREATE INDEX idx_prefix ON courses(prefix);;
CREATE INDEX idx_scope_latest ON courses(term_num, campus_code, id, "timestamp");;

