schema.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.12
-- Dumped by pg_dump version 17.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: Hyuntaek; Type: SCHEMA; Schema: -; Owner: neondb_owner
--

CREATE SCHEMA "Hyuntaek";


ALTER SCHEMA "Hyuntaek" OWNER TO neondb_owner;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: ChatRoomUsers; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."ChatRoomUsers" (
    user_id text NOT NULL,
    joined_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    chat_id uuid
);


ALTER TABLE public."ChatRoomUsers" OWNER TO neondb_owner;

--
-- Name: CoursesSubjects; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."CoursesSubjects" (
    course_id uuid NOT NULL,
    subject_abbreviation text NOT NULL
);


ALTER TABLE public."CoursesSubjects" OWNER TO neondb_owner;

--
-- Name: EnrolledSections; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."EnrolledSections" (
    user_id text NOT NULL,
    section_id uuid NOT NULL
);


ALTER TABLE public."EnrolledSections" OWNER TO neondb_owner;

--
-- Name: InstructorsSections; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."InstructorsSections" (
    section_id uuid NOT NULL,
    instructor_id integer NOT NULL
);


ALTER TABLE public."InstructorsSections" OWNER TO neondb_owner;

--
-- Name: MajorsSubjects; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."MajorsSubjects" (
    major text NOT NULL,
    subject_abbreviation text NOT NULL
);


ALTER TABLE public."MajorsSubjects" OWNER TO neondb_owner;

--
-- Name: UsersMajors; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."UsersMajors" (
    major text NOT NULL,
    user_id text NOT NULL
);


ALTER TABLE public."UsersMajors" OWNER TO neondb_owner;

--
-- Name: VerifiedUser; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."VerifiedUser" (
    course_id uuid NOT NULL,
    user_id text NOT NULL
);


ALTER TABLE public."VerifiedUser" OWNER TO neondb_owner;

--
-- Name: chatRoom; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."chatRoom" (
    name text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    message_count integer NOT NULL,
    chat_id uuid NOT NULL
);


ALTER TABLE public."chatRoom" OWNER TO neondb_owner;

--
-- Name: chatRoomDailyMessageCount; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."chatRoomDailyMessageCount" (
    date date NOT NULL,
    message_count integer DEFAULT 0,
    chat_id uuid NOT NULL
);


ALTER TABLE public."chatRoomDailyMessageCount" OWNER TO neondb_owner;

--
-- Name: courseOffering; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public."courseOffering" (
    id uuid NOT NULL,
    course_id uuid NOT NULL,
    semester text NOT NULL
);


ALTER TABLE public."courseOffering" OWNER TO neondb_owner;

--
-- Name: course_grades; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.course_grades (
    course_id uuid NOT NULL,
    total integer DEFAULT 0,
    a_per numeric(5,2) DEFAULT 0,
    ab_per numeric(5,2) DEFAULT 0,
    b_per numeric(5,2) DEFAULT 0,
    bc_per numeric(5,2) DEFAULT 0,
    c_per numeric(5,2) DEFAULT 0,
    d_per numeric(5,2) DEFAULT 0,
    f_per numeric(5,2) DEFAULT 0,
    other_per numeric(5,2) DEFAULT 0
);


ALTER TABLE public.course_grades OWNER TO neondb_owner;

--
-- Name: courses; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.courses (
    id uuid NOT NULL,
    name text,
    views integer DEFAULT 0,
    number integer
);


ALTER TABLE public.courses OWNER TO neondb_owner;

--
-- Name: instructors; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.instructors (
    name text,
    id integer NOT NULL
);


ALTER TABLE public.instructors OWNER TO neondb_owner;

--
-- Name: majors; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.majors (
    name text NOT NULL
);


ALTER TABLE public.majors OWNER TO neondb_owner;

--
-- Name: review_likes; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.review_likes (
    course_id uuid NOT NULL,
    review_user_id text NOT NULL,
    liked_by_user_id text NOT NULL,
    created_at timestamp without time zone DEFAULT now()
);


ALTER TABLE public.review_likes OWNER TO neondb_owner;

--
-- Name: reviews; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.reviews (
    course_id uuid NOT NULL,
    user_id text NOT NULL,
    rating numeric(2,1),
    comment text NOT NULL,
    edited boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    like_count integer,
    CONSTRAINT check_rating CHECK (((rating >= (0)::numeric) AND (rating <= (5)::numeric)))
);


ALTER TABLE public.reviews OWNER TO neondb_owner;

--
-- Name: COLUMN reviews.rating; Type: COMMENT; Schema: public; Owner: neondb_owner
--

COMMENT ON COLUMN public.reviews.rating IS 'Rating between 0 and 5';


--
-- Name: section_grades; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.section_grades (
    section_id uuid NOT NULL,
    total integer DEFAULT 0,
    a_per numeric(5,2) DEFAULT 0,
    ab_per numeric(5,2) DEFAULT 0,
    b_per numeric(5,2) DEFAULT 0,
    bc_per numeric(5,2) DEFAULT 0,
    c_per numeric(5,2) DEFAULT 0,
    d_per numeric(5,2) DEFAULT 0,
    f_per numeric(5,2) DEFAULT 0,
    other_per numeric(5,2) DEFAULT 0
);


ALTER TABLE public.section_grades OWNER TO neondb_owner;

--
-- Name: sections; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.sections (
    id uuid NOT NULL,
    number integer,
    section_type text,
    courseoffering_id uuid,
    start_time time without time zone,
    end_time time without time zone,
    "roomCode" text,
    "facilityCode" text,
    days text
);


ALTER TABLE public.sections OWNER TO neondb_owner;

--
-- Name: subjects; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.subjects (
    abbreviation text NOT NULL,
    name text,
    code integer
);


ALTER TABLE public.subjects OWNER TO neondb_owner;

--
-- Name: users; Type: TABLE; Schema: public; Owner: neondb_owner
--

CREATE TABLE public.users (
    id text NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    profile_picture text,
    is_admin boolean DEFAULT false,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    is_new_user boolean DEFAULT true
);


ALTER TABLE public.users OWNER TO neondb_owner;

--
-- Name: COLUMN users.id; Type: COMMENT; Schema: public; Owner: neondb_owner
--

COMMENT ON COLUMN public.users.id IS 'Firebase Auth UID';


--
-- Name: subjects Subject_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.subjects
    ADD CONSTRAINT "Subject_pkey" PRIMARY KEY (abbreviation);


--
-- Name: chatRoomDailyMessageCount chatRoomDailyMessageCount_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."chatRoomDailyMessageCount"
    ADD CONSTRAINT "chatRoomDailyMessageCount_pkey" PRIMARY KEY (date, chat_id);


--
-- Name: chatRoom chatRoom_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."chatRoom"
    ADD CONSTRAINT "chatRoom_pkey" PRIMARY KEY (chat_id);


--
-- Name: courseOffering courseOffering_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."courseOffering"
    ADD CONSTRAINT "courseOffering_pkey" PRIMARY KEY (id);


--
-- Name: course_grades course_grades_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.course_grades
    ADD CONSTRAINT course_grades_pkey PRIMARY KEY (course_id);


--
-- Name: courses courses_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.courses
    ADD CONSTRAINT courses_pkey PRIMARY KEY (id);


--
-- Name: CoursesSubjects coursessubjects_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."CoursesSubjects"
    ADD CONSTRAINT coursessubjects_pkey PRIMARY KEY (course_id, subject_abbreviation);


--
-- Name: EnrolledSections enrolledcourse_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."EnrolledSections"
    ADD CONSTRAINT enrolledcourse_pkey PRIMARY KEY (user_id, section_id);


--
-- Name: instructors instructors_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.instructors
    ADD CONSTRAINT instructors_pkey PRIMARY KEY (id);


--
-- Name: InstructorsSections instructorssections_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."InstructorsSections"
    ADD CONSTRAINT instructorssections_pkey PRIMARY KEY (section_id, instructor_id);


--
-- Name: majors majors_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.majors
    ADD CONSTRAINT majors_pkey PRIMARY KEY (name);


--
-- Name: MajorsSubjects majorssubjects_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."MajorsSubjects"
    ADD CONSTRAINT majorssubjects_pkey PRIMARY KEY (major, subject_abbreviation);


--
-- Name: review_likes review_likes_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.review_likes
    ADD CONSTRAINT review_likes_pkey PRIMARY KEY (course_id, review_user_id, liked_by_user_id);


--
-- Name: reviews reviews_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_pkey PRIMARY KEY (course_id, user_id);


--
-- Name: section_grades section_grades_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.section_grades
    ADD CONSTRAINT section_grades_pkey PRIMARY KEY (section_id);


--
-- Name: sections sections_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.sections
    ADD CONSTRAINT sections_pkey PRIMARY KEY (id);


--
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_key UNIQUE (email);


--
-- Name: users users_firebase_uid_key; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_firebase_uid_key UNIQUE (id);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: UsersMajors usersmajors_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."UsersMajors"
    ADD CONSTRAINT usersmajors_pkey PRIMARY KEY (major, user_id);


--
-- Name: VerifiedUser verifieduser_pkey; Type: CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."VerifiedUser"
    ADD CONSTRAINT verifieduser_pkey PRIMARY KEY (course_id, user_id);


--
-- Name: ChatRoomUsers ChatRoomUsers_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."ChatRoomUsers"
    ADD CONSTRAINT "ChatRoomUsers_user_id_fkey" FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: courseOffering courseOffering_course_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."courseOffering"
    ADD CONSTRAINT "courseOffering_course_id_fkey" FOREIGN KEY (course_id) REFERENCES public.courses(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: course_grades course_grades_course_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.course_grades
    ADD CONSTRAINT course_grades_course_id_fkey FOREIGN KEY (course_id) REFERENCES public.courses(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: CoursesSubjects coursessubjects_course_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."CoursesSubjects"
    ADD CONSTRAINT coursessubjects_course_id_fkey FOREIGN KEY (course_id) REFERENCES public.courses(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: CoursesSubjects coursessubjects_subject_abbreviation_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."CoursesSubjects"
    ADD CONSTRAINT coursessubjects_subject_abbreviation_fkey FOREIGN KEY (subject_abbreviation) REFERENCES public.subjects(abbreviation) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: EnrolledSections enrolledcourse_course_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."EnrolledSections"
    ADD CONSTRAINT enrolledcourse_course_id_fkey FOREIGN KEY (section_id) REFERENCES public.sections(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: EnrolledSections enrolledcourse_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."EnrolledSections"
    ADD CONSTRAINT enrolledcourse_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: InstructorsSections instructor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."InstructorsSections"
    ADD CONSTRAINT instructor_id_fkey FOREIGN KEY (instructor_id) REFERENCES public.instructors(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: review_likes review_likes_course_id_review_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.review_likes
    ADD CONSTRAINT review_likes_course_id_review_user_id_fkey FOREIGN KEY (course_id, review_user_id) REFERENCES public.reviews(course_id, user_id) ON DELETE CASCADE;


--
-- Name: review_likes review_likes_liked_by_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.review_likes
    ADD CONSTRAINT review_likes_liked_by_user_id_fkey FOREIGN KEY (liked_by_user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: reviews reviews_course_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_course_id_fkey FOREIGN KEY (course_id) REFERENCES public.courses(id) ON DELETE CASCADE;


--
-- Name: reviews reviews_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: section_grades section_grades_section_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.section_grades
    ADD CONSTRAINT section_grades_section_id_fkey FOREIGN KEY (section_id) REFERENCES public.sections(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: InstructorsSections section_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."InstructorsSections"
    ADD CONSTRAINT section_id_fkey FOREIGN KEY (section_id) REFERENCES public.sections(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: sections sections_courseOffering_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public.sections
    ADD CONSTRAINT "sections_courseOffering_id_fkey" FOREIGN KEY (courseoffering_id) REFERENCES public."courseOffering"(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: UsersMajors usersmajors_major_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."UsersMajors"
    ADD CONSTRAINT usersmajors_major_fkey FOREIGN KEY (major) REFERENCES public.majors(name) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: UsersMajors usersmajors_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."UsersMajors"
    ADD CONSTRAINT usersmajors_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: VerifiedUser verifieduser_course_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."VerifiedUser"
    ADD CONSTRAINT verifieduser_course_id_fkey FOREIGN KEY (course_id) REFERENCES public.courses(id) ON UPDATE CASCADE;


--
-- Name: VerifiedUser verifieduser_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: neondb_owner
--

ALTER TABLE ONLY public."VerifiedUser"
    ADD CONSTRAINT verifieduser_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: DEFAULT PRIVILEGES FOR SEQUENCES; Type: DEFAULT ACL; Schema: public; Owner: cloud_admin
--

ALTER DEFAULT PRIVILEGES FOR ROLE cloud_admin IN SCHEMA public GRANT ALL ON SEQUENCES TO neon_superuser WITH GRANT OPTION;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: public; Owner: cloud_admin
--

ALTER DEFAULT PRIVILEGES FOR ROLE cloud_admin IN SCHEMA public GRANT SELECT,INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON TABLES TO neon_superuser WITH GRANT OPTION;


--
-- PostgreSQL database dump complete
--