CREATE TYPE general_requirement AS ENUM (
'WAY-A-II', 'WAY-AQR', 'WAY-CE', 'WAY-EDP', 'WAY-ER', 'WAY-FR', 'WAY-SI', 'WAY-SMA',
'Language', 'Writing 1', 'Writing 2', 'Writing SLE', 'GER:DB-Hum', 'GER:DB-Math', 'GER:DB-SocSci',
'GER:DB-EngrAppSci', 'GER:DB-NatSci', 'GER:EC-EthicReas', 'GER:EC-GlobalCom', 'GER:EC-AmerCul', 'GER:EC-Gender',
'IHUM1', 'IHUM2', 'IHUM3');

CREATE TYPE foundation AS ENUM ('logic_automata_and_complexity', 'probability', 'algorithmic_analysis', 'computer_organization_and_systems', 'principles_of_computer_systems');

CREATE TYPE breadth AS ENUM ('formal_foundations', 'learning_and_modeling', 'systems', 'people_and_society');

CREATE TYPE season AS ENUM ('autumn', 'winter', 'spring', 'summer');

CREATE TYPE day AS ENUM ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

CREATE TYPE level AS ENUM ('MS', 'BS', 'PhD');
            
CREATE TYPE specialization AS ENUM ('AI', 'Computational Biology', 'Human-Computer Interaction', 'Information Management & Analytics', 'Visual Computing', 'Software Theory', 'Systems', 'Theoretical Computer Science', 'general', Computer & Network Security', Computer & Network Security');

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    max_units INT,
    title TEXT,
    grading TEXT, // values: 'Satisfactory/No Credit', 'Letter or Credit/No Credit'.
    course_codes TEXT[],
    general_requirements general_requirement[],
    min_units INT,
    description TEXT,
    average_hours_spent FLOAT, // time a student spends on the course
    foundations_requirement foundation[],
    significant_implementation_requirement BOOLEAN,
    breadth_requirement breadth[],
    prerequisite_course_codes TEXT[],
);

CREATE TABLE offerings (
    course_id INT,
    days day[],
    start_time TIME,
    end_time TIME,
    instructor_names TEXT[],
    season season, // name of the quarter
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE ratings (
    rating_id SERIAL PRIMARY KEY,
    course_id INT,
    instructor_names TEXT[],
    average_rating FLOAT, // rating on a 5 point scale
    num_ratings INT,
    term_id INT,
    start_year INT,
    end_year INT,
    season season,  // name of the quarter
    reviews TEXT[], // student reviews of the course
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
        
CREATE TABLE programs (
    program_id SERIAL PRIMARY KEY,
    level level, // educational level of the program
    specialization specialization,
    sheet_requirements TEXT,
    sheet_url TEXT,
    sheet_path TEXT
)