LogicalAndPhysicalDesign: schema_creation.sql

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 listings CASCADE;
DROP TABLE IF EXISTS animals 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,
  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,
  CONSTRAINT clients_PK PRIMARY KEY (user_id),
  CONSTRAINT clients_user_FK FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON DELETE RESTRICT
);

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),
  city        VARCHAR(80)  NOT NULL,
  address     VARCHAR(200) NOT NULL,
  approved_by BIGINT,
  CONSTRAINT vet_clinics_PK PRIMARY KEY (clinic_id),
  CONSTRAINT vet_clinics_admin_FK FOREIGN KEY (approved_by)
    REFERENCES admins(user_id)
    ON DELETE SET NULL
);

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,
  type          VARCHAR(40),
  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 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 reviews (
  review_id    BIGSERIAL,
  reviewer_id  BIGINT     NOT NULL,
  rating       INT        NOT NULL,
  comment      TEXT,
  created_at   TIMESTAMP  NOT NULL DEFAULT NOW(),
  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
);

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