-- ============================================================
--  IKnow / FINKI - DDL script (PostgreSQL)
--  Drops and creates all tables of the database
-- ============================================================

-- ---------- DROP TABLES (reverse dependency order) ----------
DROP TABLE IF EXISTS professour_subjects CASCADE;
DROP TABLE IF EXISTS dependency_subject  CASCADE;
DROP TABLE IF EXISTS major_subjects      CASCADE;
DROP TABLE IF EXISTS enrollment_info     CASCADE;
DROP TABLE IF EXISTS payment             CASCADE;
DROP TABLE IF EXISTS token               CASCADE;
DROP TABLE IF EXISTS user_documents      CASCADE;
DROP TABLE IF EXISTS documents           CASCADE;
DROP TABLE IF EXISTS passed_subjects     CASCADE;
DROP TABLE IF EXISTS semesters_subjects  CASCADE;
DROP TABLE IF EXISTS enrolled_semesters  CASCADE;
DROP TABLE IF EXISTS subjects            CASCADE;
DROP TABLE IF EXISTS active_semesters    CASCADE;
DROP TABLE IF EXISTS major               CASCADE;
DROP TABLE IF EXISTS contact             CASCADE;
DROP TABLE IF EXISTS high_school         CASCADE;
DROP TABLE IF EXISTS users               CASCADE;

-- ---------- DROP ENUM TYPES ----------
DROP TYPE IF EXISTS user_role     CASCADE;
DROP TYPE IF EXISTS hs_type       CASCADE;
DROP TYPE IF EXISTS quota_type    CASCADE;
DROP TYPE IF EXISTS semester_type CASCADE;
DROP TYPE IF EXISTS grade_type    CASCADE;

-- ---------- ENUM TYPES ----------
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');
CREATE TYPE semester_type AS ENUM ('summer', 'winter');
CREATE TYPE grade_type    AS ENUM ('6', '7', '8', '9', '10');

-- ============================================================
--  TABLES
-- ============================================================

CREATE TABLE users (
    id          INTEGER      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,
    created_at  TIMESTAMP    NOT NULL DEFAULT now()
);

CREATE TABLE high_school (
    user_id  INTEGER PRIMARY KEY REFERENCES users (id),
    gpa      REAL    NOT NULL,
    type     hs_type NOT NULL
);

CREATE TABLE contact (
    user_id          INTEGER PRIMARY KEY 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,
    name  VARCHAR(150) NOT NULL
);

CREATE TABLE active_semesters (
    id     INTEGER       PRIMARY KEY,
    year_  INTEGER       NOT NULL,
    type   semester_type NOT NULL
);

CREATE TABLE subjects (
    id                 INTEGER      PRIMARY KEY,
    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,
    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)
);

CREATE TABLE semesters_subjects (
    id                     INTEGER PRIMARY KEY,
    user_id                INTEGER NOT NULL REFERENCES users (id),
    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 (
    passed_id    INTEGER    PRIMARY KEY,
    enrolled_id  INTEGER    NOT NULL REFERENCES semesters_subjects (id),
    grade        grade_type NOT NULL,
    date_passed  TIMESTAMP  NOT NULL
);

CREATE TABLE documents (
    id     INTEGER      PRIMARY KEY,
    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,
    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,
    user_id        INTEGER NOT NULL REFERENCES users (id),
    enrollment_id  INTEGER NOT NULL REFERENCES enrolled_semesters (id),
    amount         INTEGER NOT NULL,
    ukim           INTEGER NOT NULL
);

CREATE TABLE enrollment_info (
    id               INTEGER    PRIMARY KEY,
    user_id          INTEGER    NOT NULL REFERENCES users (id),
    quota            quota_type NOT NULL,
    major_id         INTEGER    NOT NULL REFERENCES major (id),
    enrollment_year  INTEGER    NOT NULL
);

CREATE TABLE major_subjects (
    major_id            INTEGER NOT NULL REFERENCES major (id),
    subject_id          INTEGER NOT NULL REFERENCES subjects (id),
    manditory_semester  INTEGER NOT NULL,
    akreditacija        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)
);

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)
);