CREATE TABLE "basiclecture" (
  "id" INTEGER NOT NULL,
  "course_code" TEXT NOT NULL,
  "name" TEXT NOT NULL,
  PRIMARY KEY ("id", "course_code")
);

CREATE TABLE "faculty" (
  "id" INTEGER NOT NULL,
  "name" TEXT NOT NULL,
  "faculty_code" TEXT NOT NULL,
  PRIMARY KEY ("id", "faculty_code")
);

CREATE TABLE "department" (
  "id" INTEGER NOT NULL,
  "name" TEXT NOT NULL,
  "department_code" TEXT NOT NULL,
  "faculty_id" INTEGER NOT NULL,
  "faculty_faculty_code" TEXT NOT NULL,
  PRIMARY KEY ("id", "department_code")
);

CREATE INDEX "idx_department__faculty_id_faculty_faculty_code" ON "department" ("faculty_id", "faculty_faculty_code");

ALTER TABLE "department" ADD CONSTRAINT "fk_department__faculty_id__faculty_faculty_code" FOREIGN KEY ("faculty_id", "faculty_faculty_code") REFERENCES "faculty" ("id", "faculty_code");

CREATE TABLE "lecturer" (
  "id" INTEGER NOT NULL,
  "first_name" TEXT NOT NULL,
  "last_name" TEXT NOT NULL,
  "lecturer_code" TEXT NOT NULL,
  "department_id" INTEGER NOT NULL,
  "department_department_code" TEXT NOT NULL,
  PRIMARY KEY ("id", "lecturer_code")
);

CREATE INDEX "idx_lecturer__department_id_department_department_code" ON "lecturer" ("department_id", "department_department_code");

ALTER TABLE "lecturer" ADD CONSTRAINT "fk_lecturer__department_id__department_department_code" FOREIGN KEY ("department_id", "department_department_code") REFERENCES "department" ("id", "department_code");

CREATE TABLE "lecture" (
  "id" SERIAL PRIMARY KEY,
  "section_no" TEXT NOT NULL,
  "lecturer_id" INTEGER NOT NULL,
  "lecturer_lecturer_code" TEXT NOT NULL,
  "basic_lecture_id" INTEGER NOT NULL,
  "basic_lecture_course_code" TEXT NOT NULL,
  "term" TEXT NOT NULL,
  "year" TIME NOT NULL
);

CREATE INDEX "idx_lecture__basic_lecture_id_basic_lecture_course_code" ON "lecture" ("basic_lecture_id", "basic_lecture_course_code");

CREATE INDEX "idx_lecture__lecturer_id_lecturer_lecturer_code" ON "lecture" ("lecturer_id", "lecturer_lecturer_code");

ALTER TABLE "lecture" ADD CONSTRAINT "fk_lecture__basic_lecture_id__basic_lecture_course_code" FOREIGN KEY ("basic_lecture_id", "basic_lecture_course_code") REFERENCES "basiclecture" ("id", "course_code");

ALTER TABLE "lecture" ADD CONSTRAINT "fk_lecture__lecturer_id__lecturer_lecturer_code" FOREIGN KEY ("lecturer_id", "lecturer_lecturer_code") REFERENCES "lecturer" ("id", "lecturer_code");

CREATE TABLE "principle" (
  "id" SERIAL PRIMARY KEY,
  "content" TEXT NOT NULL,
  "importance" INTEGER NOT NULL,
  "basic_lecture_id" INTEGER NOT NULL,
  "basic_lecture_course_code" TEXT NOT NULL
);

CREATE INDEX "idx_principle__basic_lecture_id_basic_lecture_course_code" ON "principle" ("basic_lecture_id", "basic_lecture_course_code");

ALTER TABLE "principle" ADD CONSTRAINT "fk_principle__basic_lecture_id__basic_lecture_course_code" FOREIGN KEY ("basic_lecture_id", "basic_lecture_course_code") REFERENCES "basiclecture" ("id", "course_code");

CREATE TABLE "student" (
  "id" INTEGER NOT NULL,
  "student_code" TEXT NOT NULL,
  "first_name" TEXT NOT NULL,
  "last_name" TEXT NOT NULL,
  "department_id" INTEGER NOT NULL,
  "department_department_code" TEXT NOT NULL,
  PRIMARY KEY ("id", "student_code")
);

CREATE INDEX "idx_student__department_id_department_department_code" ON "student" ("department_id", "department_department_code");

ALTER TABLE "student" ADD CONSTRAINT "fk_student__department_id__department_department_code" FOREIGN KEY ("department_id", "department_department_code") REFERENCES "department" ("id", "department_code");

CREATE TABLE "lecture_student" (
  "lecture" INTEGER NOT NULL,
  "student_id" INTEGER NOT NULL,
  "student_student_code" TEXT NOT NULL,
  PRIMARY KEY ("lecture", "student_id", "student_student_code")
);

CREATE INDEX "idx_lecture_student" ON "lecture_student" ("student_id", "student_student_code");

ALTER TABLE "lecture_student" ADD CONSTRAINT "fk_lecture_student__lecture" FOREIGN KEY ("lecture") REFERENCES "lecture" ("id");

ALTER TABLE "lecture_student" ADD CONSTRAINT "fk_lecture_student__student_id__student_student_code" FOREIGN KEY ("student_id", "student_student_code") REFERENCES "student" ("id", "student_code");

CREATE TABLE "vote" (
  "id" SERIAL PRIMARY KEY,
  "lecture" INTEGER NOT NULL,
  "student_id" INTEGER NOT NULL,
  "student_student_code" TEXT NOT NULL,
  "value" INTEGER NOT NULL
);

CREATE INDEX "idx_vote__lecture" ON "vote" ("lecture");

CREATE INDEX "idx_vote__student_id_student_student_code" ON "vote" ("student_id", "student_student_code");

ALTER TABLE "vote" ADD CONSTRAINT "fk_vote__lecture" FOREIGN KEY ("lecture") REFERENCES "lecture" ("id");

ALTER TABLE "vote" ADD CONSTRAINT "fk_vote__student_id__student_student_code" FOREIGN KEY ("student_id", "student_student_code") REFERENCES "student" ("id", "student_code")