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 faculty_profiles(email VARCHAR, emails VARCHAR[] NOT NULL, display_name VARCHAR NOT NULL, full_name VARCHAR NOT NULL, chinese_name VARCHAR, phones VARCHAR[] NOT NULL, office_address VARCHAR, related_document_urls VARCHAR[] NOT NULL, profile_url VARCHAR NOT NULL, image_url VARCHAR, orcid VARCHAR, scopus_url VARCHAR, positions STRUCT(title VARCHAR, unit_name VARCHAR, unit_url VARCHAR)[] NOT NULL, affiliations STRUCT(unit_name VARCHAR, unit_url VARCHAR)[] NOT NULL, research_interests VARCHAR[] NOT NULL, held_positions VARCHAR[] NOT NULL, education_qualifications STRUCT(description VARCHAR, "year" VARCHAR)[] NOT NULL, sustainable_development_goals VARCHAR[] NOT NULL, fingerprints STRUCT("label" VARCHAR, category VARCHAR, score DOUBLE)[] NOT NULL, projects STRUCT(title VARCHAR, url VARCHAR, details VARCHAR[])[] NOT NULL, research_outputs STRUCT(title VARCHAR, url VARCHAR, details VARCHAR[])[] NOT NULL, activities STRUCT(title VARCHAR, url VARCHAR, details VARCHAR[])[] NOT NULL, awards STRUCT(title VARCHAR, url VARCHAR, details VARCHAR[])[] NOT NULL, courses STRUCT(title VARCHAR, url VARCHAR, details VARCHAR[])[] NOT NULL, status ENUM('ACTIVE', 'INACTIVE') NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE, PRIMARY KEY(email, "timestamp"));;
CREATE INDEX idx_faculty_profiles_active_latest ON faculty_profiles(status, email, "timestamp");;
CREATE INDEX idx_faculty_profiles_display_name ON faculty_profiles(display_name);;
CREATE INDEX idx_faculty_profiles_email ON faculty_profiles(email);;
CREATE INDEX idx_faculty_profiles_full_name ON faculty_profiles(full_name);;
CREATE INDEX idx_faculty_profiles_latest ON faculty_profiles(email, "timestamp");;

