postgres.sql

-- Copyright (©) 2024 - Zenlit Technology Solutions
-- Licensed under the GNU AGPLv3, check LICENSE for more details.
-- Auto generated, Don't edit by hand

CREATE TABLE locale (
  id   SMALLINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  name TEXT CHECK (LENGTH(name) <= 30) UNIQUE NOT NULL
);

CREATE TABLE catalogue_type (
  id     SMALLINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  name   TEXT CHECK (LENGTH(name) <= 30) NOT NULL UNIQUE,
  parent SMALLINT NOT NULL REFERENCES catalogue_type (id)
);

CREATE TABLE catalogue (
  id         SMALLINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  type       SMALLINT NOT NULL REFERENCES catalogue_type (id),
  name       TEXT CHECK (LENGTH(name) <= 100) NOT NULL,
  icon       TEXT CHECK (LENGTH(icon) <= 255),
  attributes JSONB,
  parent     SMALLINT NOT NULL REFERENCES catalogue (id)
);

CREATE TABLE document (
  id         INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  name       TEXT CHECK (LENGTH(name) <= 255) NOT NULL,
  content    TEXT NOT NULL,
  is_public  BOOLEAN,
  c_ids      TEXT CHECK (LENGTH(c_ids) <= 255),
  locale     SMALLINT REFERENCES locale (id),
  created_at BIGINT NOT NULL,
  updated_at BIGINT NOT NULL
);

CREATE TABLE document_catalogue (
  document_id  INTEGER REFERENCES document (id),
  catalogue_id SMALLINT REFERENCES catalogue (id),
  PRIMARY KEY (document_id, catalogue_id)
);

CREATE TABLE pdf (
  id          INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  file_name   TEXT CHECK (LENGTH(file_name) <= 255) NOT NULL,
  file_url    TEXT CHECK (LENGTH(file_url) <= 255) NOT NULL,
  locale      SMALLINT REFERENCES locale (id),
  c_ids       TEXT CHECK (LENGTH(c_ids) <= 255),
  is_public   BOOLEAN,
  uploaded_at BIGINT NOT NULL
);

CREATE TABLE pdf_catalogue (
  pdf_id       INTEGER REFERENCES pdf (id),
  catalogue_id SMALLINT REFERENCES catalogue (id),
  PRIMARY KEY (pdf_id, catalogue_id)
);

CREATE TABLE account (
  id             BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  email          TEXT CHECK (LENGTH(email) <= 255) UNIQUE,
  phone          TEXT CHECK (LENGTH(phone) <= 15) UNIQUE,
  auth_providers JSONB,
  permission     BYTEA,
  details_filled BOOLEAN,
  c_ids          TEXT CHECK (LENGTH(c_ids) <= 255),
  first_name     TEXT CHECK (LENGTH(first_name) <= 100),
  last_name      TEXT CHECK (LENGTH(last_name) <= 100),
  profile_pic    TEXT CHECK (LENGTH(profile_pic) <= 2000),
  gender         SMALLINT CHECK (gender > 0 AND gender < 4),
  date_of_birth  BIGINT,
  created_at     BIGINT NOT NULL,
  updated_at     BIGINT NOT NULL
);

CREATE TABLE course (
  id               SMALLINT PRIMARY KEY REFERENCES catalogue (id),
  cid              TEXT CHECK (LENGTH(cid) <= 3) UNIQUE NOT NULL,
  name             TEXT CHECK (LENGTH(name) <= 255) NOT NULL,
  locale           SMALLINT REFERENCES locale (id),
  validity         SMALLINT,
  price            SMALLINT NOT NULL,
  discount_percent SMALLINT,
  is_public        BOOLEAN NOT NULL,
  is_open          BOOLEAN NOT NULL,
  info             TEXT CHECK (LENGTH(info) <= 100),
  description      INTEGER REFERENCES document (id),
  thumbnail        TEXT CHECK (LENGTH(thumbnail) <= 255),
  starts_at        BIGINT,
  ends_at          BIGINT,
  created_at       BIGINT NOT NULL,
  updated_at       BIGINT NOT NULL
);

CREATE TABLE course_teacher (
  course_id     SMALLINT REFERENCES course (id),
  teacher_id    BIGINT REFERENCES account (id),
  subject       TEXT CHECK (LENGTH(subject) <= 50),
  experience    SMALLINT,
  qualification TEXT CHECK (LENGTH(qualification) <= 50),
  about         INTEGER REFERENCES document (id),
  PRIMARY KEY (course_id, teacher_id)
);

CREATE TABLE course_faq (
  id        SMALLINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  question  TEXT CHECK (LENGTH(question) <= 2000) NOT NULL,
  answer    TEXT CHECK (LENGTH(answer) <= 2000) NOT NULL,
  course_id SMALLINT REFERENCES course (id)
);

CREATE TABLE coupon (
  id               SMALLINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  code             TEXT CHECK (LENGTH(code) <= 10) UNIQUE NOT NULL,
  discount_percent SMALLINT NOT NULL,
  course_id        SMALLINT REFERENCES course (id),
  use_limit        SMALLINT,
  valid_till       BIGINT
);

CREATE TABLE payment (
  id           BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  user_id      BIGINT REFERENCES account (id),
  course_id    SMALLINT REFERENCES course (id),
  amount       INTEGER NOT NULL,
  status       SMALLINT NOT NULL CHECK (status > 0 AND status < 4),
  initiated_at BIGINT NOT NULL,
  coupon_id    SMALLINT REFERENCES coupon (id)
);

CREATE TABLE refund (
  payment_id BIGINT PRIMARY KEY REFERENCES payment (id),
  refund_id  TEXT CHECK (LENGTH(refund_id) <= 100) NOT NULL,
  issued_at  BIGINT NOT NULL
);

CREATE TABLE test (
  id                 INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  name               TEXT NOT NULL,
  locale             SMALLINT REFERENCES locale (id),
  is_multi_type      BOOLEAN,
  paragraphs         JSONB,
  time_limit         SMALLINT,
  total_questions    SMALLINT,
  marks_per_question SMALLINT,
  parent_id          INTEGER REFERENCES test (id)
);

CREATE TABLE test_catalogue (
  test_id      INTEGER REFERENCES test (id),
  catalogue_id SMALLINT REFERENCES catalogue (id),
  PRIMARY KEY (test_id, catalogue_id)
);

CREATE TABLE question (
  id                BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  test_id           INTEGER NOT NULL REFERENCES test (id),
  question_type     SMALLINT NOT NULL CHECK (question_type > 0 AND question_type < 7),
  question_text     TEXT,
  question_metadata JSONB NOT NULL,
  answer            TEXT NOT NULL,
  difficulty        SMALLINT CHECK (difficulty > 0 AND difficulty < 4),
  related_para      SMALLINT
);

CREATE TABLE test_submission (
  id              BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  test_id         INTEGER NOT NULL REFERENCES test (id),
  correct_answers SMALLINT NOT NULL,
  wrong_answers   SMALLINT NOT NULL,
  questions_left  SMALLINT
);

CREATE TABLE reported_question (
  id          SMALLINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  question_id BIGINT NOT NULL REFERENCES question (id),
  reportd_by  BIGINT NOT NULL REFERENCES account (id),
  reviewed    BOOLEAN,
  reviewed_by BIGINT REFERENCES account (id),
  reported_on BIGINT NOT NULL
);

CREATE TABLE video (
  id               INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
  title            TEXT CHECK (LENGTH(title) <= 255) NOT NULL,
  is_public        BOOLEAN NOT NULL,
  processing_state SMALLINT NOT NULL CHECK (processing_state > 0 AND processing_state < 4),
  duration         SMALLINT NOT NULL,
  video_url        TEXT CHECK (LENGTH(video_url) <= 255),
  thumbnail_url    TEXT CHECK (LENGTH(thumbnail_url) <= 255),
  processed_url    TEXT CHECK (LENGTH(processed_url) <= 255),
  uploaded_at      BIGINT NOT NULL,
  uploaded_by      BIGINT NOT NULL REFERENCES account (id)
);

CREATE TABLE video_catalogue (
  video_id     INTEGER REFERENCES video (id),
  catalogue_id SMALLINT REFERENCES catalogue (id),
  PRIMARY KEY (video_id, catalogue_id)
);

CREATE TABLE live_class (
  id           BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  course_id    SMALLINT REFERENCES course (id),
  catalogue_id SMALLINT REFERENCES catalogue (id),
  teacher_id   BIGINT REFERENCES account (id),
  starts_at    BIGINT NOT NULL,
  duration     SMALLINT NOT NULL,
  class_status SMALLINT NOT NULL CHECK (class_status > 0 AND class_status < 5)
);

CREATE TABLE live_class_material (
  id            BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 10101 INCREMENT BY 1),
  live_class_id BIGINT REFERENCES live_class (id),
  document_id   INTEGER REFERENCES document (id),
  pdf_id        INTEGER REFERENCES pdf (id),
  test_id       INTEGER REFERENCES test (id),
  video_id      INTEGER REFERENCES video (id)
);