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 course_records(ref_no VARCHAR, until_term_num INTEGER NOT NULL, until_term_code VARCHAR NOT NULL, until_term_name VARCHAR NOT NULL, country VARCHAR NOT NULL, institution VARCHAR NOT NULL, source_entries STRUCT(subject VARCHAR, code VARCHAR, title VARCHAR)[] NOT NULL, target_entries STRUCT(code VARCHAR, title VARCHAR, credits DOUBLE)[] NOT NULL, requirements VARCHAR NOT NULL, restrictions VARCHAR NOT NULL, status ENUM('ACTIVE', 'INACTIVE') NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE, PRIMARY KEY(ref_no, "timestamp"));;
CREATE TABLE exam_records(ref_no VARCHAR, until_term_num INTEGER NOT NULL, until_term_code VARCHAR NOT NULL, until_term_name VARCHAR NOT NULL, qualification VARCHAR NOT NULL, source_entries STRUCT(subject VARCHAR, code VARCHAR, title VARCHAR)[] NOT NULL, target_entries STRUCT(code VARCHAR, title VARCHAR, credits DOUBLE)[] NOT NULL, requirements VARCHAR NOT NULL, restrictions VARCHAR NOT NULL, status ENUM('ACTIVE', 'INACTIVE') NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE, PRIMARY KEY(ref_no, "timestamp"));;
CREATE INDEX idx_course_records_active_latest ON course_records(status, ref_no, "timestamp");;
CREATE INDEX idx_course_records_country ON course_records(country);;
CREATE INDEX idx_course_records_institution ON course_records(institution);;
CREATE INDEX idx_course_records_latest ON course_records(ref_no, "timestamp");;
CREATE INDEX idx_course_records_ref_no ON course_records(ref_no);;
CREATE INDEX idx_exam_records_active_latest ON exam_records(status, ref_no, "timestamp");;
CREATE INDEX idx_exam_records_latest ON exam_records(ref_no, "timestamp");;
CREATE INDEX idx_exam_records_qualification ON exam_records(qualification);;
CREATE INDEX idx_exam_records_ref_no ON exam_records(ref_no);;

