BEGIN;

DROP TABLE IF EXISTS health_records CASCADE;
DROP TABLE IF EXISTS clinic_reviews CASCADE;
DROP TABLE IF EXISTS user_reviews CASCADE;
DROP TABLE IF EXISTS reviews CASCADE;
DROP TABLE IF EXISTS appointments CASCADE;
DROP TABLE IF EXISTS favorite_listings CASCADE;
DROP TABLE IF EXISTS listings CASCADE;
DROP TABLE IF EXISTS animals CASCADE;
DROP TABLE IF EXISTS vet_clinic_applications CASCADE;
DROP TABLE IF EXISTS vet_clinics CASCADE;
DROP TABLE IF EXISTS notifications CASCADE;
DROP TABLE IF EXISTS owners CASCADE;
DROP TABLE IF EXISTS clients CASCADE;
DROP TABLE IF EXISTS admins CASCADE;
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
                       user_id        BIGSERIAL,
                       username       VARCHAR(30)  NOT NULL,
                       email          VARCHAR(254) NOT NULL,
                       name           VARCHAR(60)  NOT NULL,
                       surname        VARCHAR(60)  NOT NULL,
                       password_hash  TEXT         NOT NULL,
                       created_at     TIMESTAMP    NOT NULL DEFAULT NOW(),
                       CONSTRAINT users_PK PRIMARY KEY (user_id),
                       CONSTRAINT users_username_UQ UNIQUE (username),
                       CONSTRAINT users_email_UQ    UNIQUE (email)
);

CREATE TABLE admins (
                        user_id BIGINT,
                        CONSTRAINT admins_PK PRIMARY KEY (user_id),
                        CONSTRAINT admins_user_FK FOREIGN KEY (user_id)
                            REFERENCES users(user_id)
                            ON DELETE RESTRICT
);

CREATE TABLE clients (
                         user_id        BIGINT,
                         is_blocked     BOOLEAN     NOT NULL DEFAULT FALSE,
                         blocked_at     TIMESTAMP,
                         blocked_reason TEXT,
                         blocked_by     BIGINT,
                         CONSTRAINT clients_PK PRIMARY KEY (user_id),
                         CONSTRAINT clients_user_FK FOREIGN KEY (user_id)
                             REFERENCES users(user_id)
                             ON DELETE RESTRICT,
                         CONSTRAINT clients_blocked_by_FK FOREIGN KEY (blocked_by)
                             REFERENCES admins(user_id)
                             ON DELETE SET NULL
);

CREATE TABLE owners (
                        user_id BIGINT,
                        CONSTRAINT owners_PK PRIMARY KEY (user_id),
                        CONSTRAINT owners_client_FK FOREIGN KEY (user_id)
                            REFERENCES clients(user_id)
                            ON DELETE RESTRICT
);

CREATE TABLE notifications (
                               notification_id BIGSERIAL,
                               user_id         BIGINT       NOT NULL,
                               type            VARCHAR(40)  NOT NULL,
                               message         TEXT         NOT NULL,
                               is_read         BOOLEAN      NOT NULL DEFAULT FALSE,
                               created_at      TIMESTAMP    NOT NULL DEFAULT NOW(),
                               CONSTRAINT notifications_PK PRIMARY KEY (notification_id),
                               CONSTRAINT notifications_user_FK FOREIGN KEY (user_id)
                                   REFERENCES users(user_id)
                                   ON DELETE RESTRICT
);

CREATE TABLE vet_clinics (
                             clinic_id BIGSERIAL,
                             name      VARCHAR(120) NOT NULL,
                             email     VARCHAR(254),
                             phone     VARCHAR(40),
                             location  VARCHAR(120),
                             city      VARCHAR(80)  NOT NULL,
                             address   VARCHAR(200) NOT NULL,
                             CONSTRAINT vet_clinics_PK PRIMARY KEY (clinic_id)
);

CREATE TABLE vet_clinic_applications (
                                         application_id BIGSERIAL,
                                         clinic_id      BIGINT       NOT NULL UNIQUE,
                                         name           VARCHAR(120) NOT NULL,
                                         email          VARCHAR(254),
                                         phone          VARCHAR(40),
                                         city           VARCHAR(80)  NOT NULL,
                                         address        VARCHAR(200) NOT NULL,
                                         submitted_at   TIMESTAMP    NOT NULL DEFAULT NOW(),
                                         status         VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
                                         reviewed_at    TIMESTAMP,
                                         reviewed_by    BIGINT,
                                         denial_reason  TEXT,

                                         CONSTRAINT vet_clinic_applications_PK PRIMARY KEY (application_id),

                                         CONSTRAINT vet_clinic_applications_clinic_FK FOREIGN KEY (clinic_id)
                                             REFERENCES vet_clinics(clinic_id)
                                             ON DELETE RESTRICT,

                                         CONSTRAINT vet_clinic_applications_admin_FK FOREIGN KEY (reviewed_by)
                                             REFERENCES admins(user_id)
                                             ON DELETE SET NULL,

                                         CONSTRAINT vet_clinic_applications_status_chk CHECK (
                                             status IN ('PENDING','APPROVED','DENIED')
                                             )
);

CREATE TABLE animals (
                         animal_id     BIGSERIAL,
                         owner_id      BIGINT      NOT NULL,
                         name          VARCHAR(80) NOT NULL,
                         sex           VARCHAR(10) NOT NULL,
                         date_of_birth DATE,
                         photo_url     TEXT,
                         species       VARCHAR(60),
                         breed         VARCHAR(60),
                         located_name  VARCHAR(120),
                         CONSTRAINT animals_PK PRIMARY KEY (animal_id),
                         CONSTRAINT animals_owner_FK FOREIGN KEY (owner_id)
                             REFERENCES owners(user_id)
                             ON DELETE RESTRICT,
                         CONSTRAINT animals_sex_CHK CHECK (sex IN ('MALE','FEMALE','UNKNOWN'))
);

CREATE TABLE listings (
                          listing_id   BIGSERIAL,
                          owner_id     BIGINT        NOT NULL,
                          animal_id    BIGINT        NOT NULL,
                          status       VARCHAR(20)   NOT NULL,
                          price        NUMERIC(12,2) NOT NULL,
                          description  TEXT,
                          created_at   TIMESTAMP     NOT NULL DEFAULT NOW(),

                          CONSTRAINT listings_PK PRIMARY KEY (listing_id),
                          CONSTRAINT listings_owner_FK FOREIGN KEY (owner_id)
                              REFERENCES owners(user_id)
                              ON DELETE RESTRICT,
                          CONSTRAINT listings_animal_FK FOREIGN KEY (animal_id)
                              REFERENCES animals(animal_id)
                              ON DELETE RESTRICT,
                          CONSTRAINT listings_status_CHK CHECK (status IN ('DRAFT','ACTIVE','SOLD','ARCHIVED')),
                          CONSTRAINT listings_price_CHK CHECK (price >= 0)
);

CREATE TABLE favorite_listings (
                                   client_id  BIGINT NOT NULL,
                                   listing_id BIGINT NOT NULL,
                                   CONSTRAINT favorite_listings_PK PRIMARY KEY (client_id, listing_id),
                                   CONSTRAINT favorite_listings_client_FK FOREIGN KEY (client_id)
                                       REFERENCES clients(user_id)
                                       ON DELETE CASCADE,
                                   CONSTRAINT favorite_listings_listing_FK FOREIGN KEY (listing_id)
                                       REFERENCES listings(listing_id)
                                       ON DELETE CASCADE
);

CREATE TABLE appointments (
                              appointment_id       BIGSERIAL,
                              clinic_id            BIGINT      NOT NULL,
                              animal_id            BIGINT      NOT NULL,
                              responsible_owner_id BIGINT      NOT NULL,
                              status               VARCHAR(20) NOT NULL,
                              date_time            TIMESTAMP   NOT NULL,
                              notes                TEXT,

                              CONSTRAINT appointments_PK PRIMARY KEY (appointment_id),
                              CONSTRAINT appointments_clinic_FK FOREIGN KEY (clinic_id)
                                  REFERENCES vet_clinics(clinic_id)
                                  ON DELETE RESTRICT,
                              CONSTRAINT appointments_animal_FK FOREIGN KEY (animal_id)
                                  REFERENCES animals(animal_id)
                                  ON DELETE RESTRICT,
                              CONSTRAINT appointments_owner_FK FOREIGN KEY (responsible_owner_id)
                                  REFERENCES owners(user_id)
                                  ON DELETE RESTRICT,
                              CONSTRAINT appointments_status_CHK CHECK (status IN ('CONFIRMED','CANCELLED','DONE','NO_SHOW'))
);

CREATE TABLE health_records (
                                healthrecord_id BIGSERIAL,
                                animal_id       BIGINT      NOT NULL,
                                appointment_id  BIGINT      NOT NULL,
                                type            VARCHAR(40) NOT NULL,
                                description     TEXT,
                                date            DATE        NOT NULL,

                                CONSTRAINT health_records_PK PRIMARY KEY (healthrecord_id),
                                CONSTRAINT health_records_animal_FK FOREIGN KEY (animal_id)
                                    REFERENCES animals(animal_id)
                                    ON DELETE RESTRICT,
                                CONSTRAINT health_records_appointment_FK FOREIGN KEY (appointment_id)
                                    REFERENCES appointments(appointment_id)
                                    ON DELETE RESTRICT
);

CREATE TABLE reviews (
                         review_id    BIGSERIAL,
                         reviewer_id  BIGINT     NOT NULL,
                         rating       INT        NOT NULL,
                         comment      TEXT,
                         created_at   TIMESTAMP  NOT NULL DEFAULT NOW(),
                         updated_at   TIMESTAMP,
                         is_deleted   BOOLEAN    NOT NULL DEFAULT FALSE,
                         CONSTRAINT reviews_PK PRIMARY KEY (review_id),
                         CONSTRAINT reviews_reviewer_FK FOREIGN KEY (reviewer_id)
                             REFERENCES clients(user_id)
                             ON DELETE RESTRICT,
                         CONSTRAINT reviews_rating_CHK CHECK (rating BETWEEN 1 AND 5)
);

CREATE TABLE user_reviews (
                              review_id      BIGINT,
                              target_user_id BIGINT NOT NULL,

                              CONSTRAINT user_reviews_PK PRIMARY KEY (review_id),
                              CONSTRAINT user_reviews_review_FK FOREIGN KEY (review_id)
                                  REFERENCES reviews(review_id)
                                  ON DELETE RESTRICT,
                              CONSTRAINT user_reviews_target_FK FOREIGN KEY (target_user_id)
                                  REFERENCES users(user_id)
                                  ON DELETE RESTRICT
);

CREATE TABLE clinic_reviews (
                                review_id        BIGINT,
                                target_clinic_id BIGINT NOT NULL,

                                CONSTRAINT clinic_reviews_PK PRIMARY KEY (review_id),
                                CONSTRAINT clinic_reviews_review_FK FOREIGN KEY (review_id)
                                    REFERENCES reviews(review_id)
                                    ON DELETE RESTRICT,
                                CONSTRAINT clinic_reviews_target_FK FOREIGN KEY (target_clinic_id)
                                    REFERENCES vet_clinics(clinic_id)
                                    ON DELETE RESTRICT
);
COMMIT ;
