-- =============================================================
--  CityFix - schema_creation.sql
--  Креирање на шемата project и сите табели (PostgreSQL)
--  Релационен модел добиен со парцијална трансформација
--  од ЕР моделот v01.
--  Скриптата може да се извршува повеќепати: ја брише
--  постоечката шема и ја креира одново.
-- =============================================================

DROP SCHEMA IF EXISTS project CASCADE;
CREATE SCHEMA project;

SET search_path TO project;

-- -------------------------------------------------------------
--  ADMINS
-- -------------------------------------------------------------
CREATE TABLE admins (
    admin_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    full_name   VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    password    VARCHAR(255) NOT NULL,

    CONSTRAINT pk_admins PRIMARY KEY (admin_id),
    CONSTRAINT uq_admins_email UNIQUE (email),
    CONSTRAINT ck_admins_email CHECK (email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$')
);

-- -------------------------------------------------------------
--  WORKERS
-- -------------------------------------------------------------
CREATE TABLE workers (
    worker_id   INTEGER GENERATED BY DEFAULT AS IDENTITY,
    full_name   VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    password    VARCHAR(255) NOT NULL,

    CONSTRAINT pk_workers PRIMARY KEY (worker_id),
    CONSTRAINT uq_workers_email UNIQUE (email),
    CONSTRAINT ck_workers_email CHECK (email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$')
);

-- -------------------------------------------------------------
--  CITIZENS
-- -------------------------------------------------------------
CREATE TABLE citizens (
    citizen_id  INTEGER GENERATED BY DEFAULT AS IDENTITY,
    full_name   VARCHAR(100) NOT NULL,
    email       VARCHAR(150) NOT NULL,
    phone       VARCHAR(20),
    password    VARCHAR(255) NOT NULL,

    CONSTRAINT pk_citizens PRIMARY KEY (citizen_id),
    CONSTRAINT uq_citizens_email UNIQUE (email),
    CONSTRAINT ck_citizens_email CHECK (email ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$'),
    CONSTRAINT ck_citizens_phone CHECK (phone ~ '^\+?[0-9]{6,19}$')
);

-- -------------------------------------------------------------
--  CATEGORIES   (Manages: N:1 кон admins, тотално учество)
-- -------------------------------------------------------------
CREATE TABLE categories (
    category_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    name        VARCHAR(80) NOT NULL,
    admin_id    INTEGER     NOT NULL,

    CONSTRAINT pk_categories PRIMARY KEY (category_id),
    CONSTRAINT uq_categories_name UNIQUE (name),
    CONSTRAINT fk_categories_admin FOREIGN KEY (admin_id)
        REFERENCES admins (admin_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
);

-- -------------------------------------------------------------
--  REPORTS   (Submits: N:1 кон citizens, Classifies: N:1 кон categories)
-- -------------------------------------------------------------
CREATE TABLE reports (
    report_id     INTEGER GENERATED BY DEFAULT AS IDENTITY,
    description   TEXT          NOT NULL,
    location_text VARCHAR(255),
    latitude      NUMERIC(9,6),
    longitude     NUMERIC(9,6),
    status        VARCHAR(20)   NOT NULL DEFAULT 'submitted',
    priority      VARCHAR(10)   NOT NULL DEFAULT 'low',
    created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    citizen_id    INTEGER       NOT NULL,
    category_id   INTEGER       NOT NULL,

    CONSTRAINT pk_reports PRIMARY KEY (report_id),
    CONSTRAINT fk_reports_citizen FOREIGN KEY (citizen_id)
        REFERENCES citizens (citizen_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_reports_category FOREIGN KEY (category_id)
        REFERENCES categories (category_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT ck_reports_status
        CHECK (status IN ('submitted', 'received', 'in_progress', 'resolved', 'rejected')),
    CONSTRAINT ck_reports_priority
        CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
    CONSTRAINT ck_reports_latitude  CHECK (latitude  BETWEEN -90  AND 90),
    CONSTRAINT ck_reports_longitude CHECK (longitude BETWEEN -180 AND 180),
    -- координатите се внесуваат заедно или воопшто не се внесуваат
    CONSTRAINT ck_reports_coordinates
        CHECK ((latitude IS NULL) = (longitude IS NULL)),
    -- мора да постои барем адреса или координати
    CONSTRAINT ck_reports_location
        CHECK (location_text IS NOT NULL OR latitude IS NOT NULL)
);

-- -------------------------------------------------------------
--  PHOTOS   (Has: N:1 кон reports, тотално учество)
-- -------------------------------------------------------------
CREATE TABLE photos (
    photo_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    image_url   VARCHAR(500) NOT NULL,
    report_id   INTEGER      NOT NULL,

    CONSTRAINT pk_photos PRIMARY KEY (photo_id),
    CONSTRAINT fk_photos_report FOREIGN KEY (report_id)
        REFERENCES reports (report_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

-- -------------------------------------------------------------
--  STATUS_LOGS   (Logs: N:1 кон reports, тотално учество;
--                 Updates: N:1 кон workers, парцијално учество)
-- -------------------------------------------------------------
CREATE TABLE status_logs (
    log_id      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    status      VARCHAR(20) NOT NULL,
    changed_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    note        TEXT,
    report_id   INTEGER     NOT NULL,
    worker_id   INTEGER,

    CONSTRAINT pk_status_logs PRIMARY KEY (log_id),
    CONSTRAINT fk_status_logs_report FOREIGN KEY (report_id)
        REFERENCES reports (report_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_status_logs_worker FOREIGN KEY (worker_id)
        REFERENCES workers (worker_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT ck_status_logs_status
        CHECK (status IN ('submitted', 'received', 'in_progress', 'resolved', 'rejected'))
);

-- -------------------------------------------------------------
--  COMMENTS   (Comments on: N:1 кон reports; Writes: N:1 кон workers)
-- -------------------------------------------------------------
CREATE TABLE comments (
    comment_id  INTEGER GENERATED BY DEFAULT AS IDENTITY,
    content     TEXT      NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    report_id   INTEGER   NOT NULL,
    worker_id   INTEGER   NOT NULL,

    CONSTRAINT pk_comments PRIMARY KEY (comment_id),
    CONSTRAINT fk_comments_report FOREIGN KEY (report_id)
        REFERENCES reports (report_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_comments_worker FOREIGN KEY (worker_id)
        REFERENCES workers (worker_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
);

-- -------------------------------------------------------------
--  ASSIGNMENTS   (Assigns: N:1 кон admins; Assigned via: N:1 кон reports;
--                 Receives: N:1 кон workers; сите со тотално учество)
-- -------------------------------------------------------------
CREATE TABLE assignments (
    assignment_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    assigned_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    note          TEXT,
    admin_id      INTEGER   NOT NULL,
    report_id     INTEGER   NOT NULL,
    worker_id     INTEGER   NOT NULL,

    CONSTRAINT pk_assignments PRIMARY KEY (assignment_id),
    CONSTRAINT fk_assignments_admin FOREIGN KEY (admin_id)
        REFERENCES admins (admin_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_assignments_report FOREIGN KEY (report_id)
        REFERENCES reports (report_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_assignments_worker FOREIGN KEY (worker_id)
        REFERENCES workers (worker_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    -- истиот работник не може двапати да биде доделен на иста пријава
    CONSTRAINT uq_assignments_report_worker UNIQUE (report_id, worker_id)
);