source: sql/schema_creation.sql@ 1b20b22

DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;

SET search_path TO project;

CREATE TYPE user_role     AS ENUM ('student', 'prof', 'admin');
CREATE TYPE hs_type       AS ENUM ('strucen', 'gimnazija');
CREATE TYPE quota_type    AS ENUM ('privatna', 'drzavna', 'stipendija');
CREATE TYPE semester_type AS ENUM ('summer', 'winter');
CREATE TYPE grade_type    AS ENUM ('6', '7', '8', '9', '10');

CREATE TABLE users (
    id               INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    embg             VARCHAR(13),
    surname          VARCHAR(100) NOT NULL,
    "index"          VARCHAR(20),
    bday             TIMESTAMP,
    email            VARCHAR(150) NOT NULL UNIQUE,
    password         VARCHAR(255) NOT NULL,
    role             user_role    NOT NULL,
    quota            quota_type,
    enrollment_year  INTEGER,
    created_at       TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE TABLE high_school (
    id       INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    user_id  INTEGER NOT NULL UNIQUE REFERENCES users (id),
    gpa      REAL    NOT NULL,
    type     hs_type NOT NULL
);

CREATE TABLE contact (
    id               INTEGER      PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    user_id          INTEGER      NOT NULL UNIQUE REFERENCES users (id),
    city             VARCHAR(100) NOT NULL,
    municipality     VARCHAR(100) NOT NULL,
    address          VARCHAR(150) NOT NULL,
    number           VARCHAR(20)  NOT NULL,
    microsoft_email  VARCHAR(150)
);

CREATE TABLE major (
    id    INTEGER      PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name  VARCHAR(150) NOT NULL
);

CREATE TABLE active_semesters (
    id     INTEGER       PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    year   INTEGER       NOT NULL,
    type   semester_type NOT NULL,
    UNIQUE (year, type)
);

CREATE TABLE subjects (
    id                 INTEGER      PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name               VARCHAR(150) NOT NULL UNIQUE,
    code               VARCHAR(30)  NOT NULL UNIQUE,
    awarded_credits    INTEGER      NOT NULL,
    dependency_credit  INTEGER
);

CREATE TABLE enrolled_semesters (
    id               INTEGER    PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    user_id          INTEGER    NOT NULL REFERENCES users (id),
    quota            quota_type NOT NULL,
    major_id         INTEGER    NOT NULL REFERENCES major (id),
    note             VARCHAR(255),
    student_comment  VARCHAR(255),
    created_at       TIMESTAMP  NOT NULL DEFAULT now(),
    last_change      TIMESTAMP,
    completed        TIMESTAMP,
    semester_id      INTEGER    NOT NULL REFERENCES active_semesters (id),
    UNIQUE (user_id, semester_id)
);

CREATE TABLE semesters_subjects (
    id                     INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    enrolled_semesters_id  INTEGER NOT NULL REFERENCES enrolled_semesters (id),
    subjects_id            INTEGER NOT NULL REFERENCES subjects (id),
    professor_id           INTEGER NOT NULL REFERENCES users (id),
    signature              BOOLEAN NOT NULL DEFAULT FALSE,
    UNIQUE (enrolled_semesters_id, subjects_id)
);

CREATE TABLE passed_subjects (
    id           INTEGER    PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    enrolled_id  INTEGER    NOT NULL UNIQUE REFERENCES semesters_subjects (id),
    grade        grade_type NOT NULL,
    date_passed  TIMESTAMP  NOT NULL
);

CREATE TABLE documents (
    id     INTEGER      PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    type   VARCHAR(100) NOT NULL,
    body   TEXT         NOT NULL,
    cost   INTEGER      NOT NULL
);

CREATE TABLE user_documents (
    user_id      INTEGER NOT NULL REFERENCES users (id),
    document_id  INTEGER NOT NULL REFERENCES documents (id),
    PRIMARY KEY (user_id, document_id)
);

CREATE TABLE token (
    id          INTEGER      PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    user_id     INTEGER      NOT NULL REFERENCES users (id),
    token       VARCHAR(255) NOT NULL,
    expires_at  TIMESTAMP    NOT NULL,
    is_valid    BOOLEAN      NOT NULL DEFAULT TRUE
);

CREATE TABLE payment (
    id             INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    user_id        INTEGER NOT NULL REFERENCES users (id),
    enrollment_id  INTEGER NOT NULL REFERENCES enrolled_semesters (id),
    amount         INTEGER NOT NULL
);

CREATE TABLE major_subjects (
    major_id            INTEGER NOT NULL REFERENCES major (id),
    subject_id          INTEGER NOT NULL REFERENCES subjects (id),
    mandatory_semester  INTEGER NOT NULL,
    PRIMARY KEY (major_id, subject_id)
);

CREATE TABLE dependency_subject (
    subject_id     INTEGER NOT NULL REFERENCES subjects (id),
    dependency_id  INTEGER NOT NULL REFERENCES subjects (id),
    PRIMARY KEY (subject_id, dependency_id),
    CHECK (subject_id <> dependency_id)
);

CREATE TABLE professour_subjects (
    prof_id            INTEGER NOT NULL REFERENCES users (id),
    active_semester_id INTEGER NOT NULL REFERENCES active_semesters (id),
    subject_id         INTEGER NOT NULL REFERENCES subjects (id),
    PRIMARY KEY (prof_id, active_semester_id, subject_id)
);