DatabaseCreation: BlinkBuyDDL.sql

SET search_path TO myschema;

-- 1. ОСНОВНИ ШИФРАРНИЦИ (Табели без надворешни зависности)
CREATE TABLE ROLES (
  role_id     BIGSERIAL PRIMARY KEY,
  role_name   varchar(255) NOT NULL UNIQUE,
  description varchar(255)
);

CREATE TABLE CATEGORIES (
  category_id        BIGSERIAL PRIMARY KEY,
  category_name      varchar(255) NOT NULL,
  parent_category_id int8,
  CONSTRAINT PARENT_OF FOREIGN KEY (parent_category_id) REFERENCES CATEGORIES (category_id) ON UPDATE Cascade ON DELETE Restrict
);

CREATE TABLE PRODUCT_ATTRIBUTES (
  attribute_id   BIGSERIAL PRIMARY KEY,
  attribute_name varchar(255) NOT NULL
);

CREATE TABLE SHIPPING_METHODS (
  method_id      BIGSERIAL PRIMARY KEY,
  method_name    varchar(255) NOT NULL,
  cost           numeric(12, 2) NOT NULL CHECK (cost >= 0),
  estimated_days int4 NOT NULL CHECK (estimated_days >= 0)
);

CREATE TABLE COUPONS (
  coupon_id       BIGSERIAL PRIMARY KEY,
  code            varchar(255) NOT NULL UNIQUE,
  discount_value  numeric(12, 2) NOT NULL CHECK (discount_value > 0),
  discount_type   varchar(255) NOT NULL,
  valid_from      timestamp with time zone NOT NULL,
  valid_to        timestamp with time zone NOT NULL,
  min_order_value numeric(10, 2),
  CONSTRAINT chk_coupons_valid_dates
CHECK (valid_to >= valid_from)
);

CREATE TABLE MANUFACTURERS (
  manufacturer_id BIGSERIAL PRIMARY KEY,
  name            varchar(100) NOT NULL,
  contact_info    text
);

-- 2. КОРИСНИЧКИ МОДУЛ
CREATE TABLE USERS (
  user_id       BIGSERIAL PRIMARY KEY,
  email         varchar(255) NOT NULL UNIQUE CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  password_hash varchar(255) NOT NULL,
  status        varchar(255) NOT NULL,
  created_at    timestamp with time zone NOT NULL,
  CONSTRAINT chk_users_status
    CHECK (status IN (
    'ACTIVE',
    'INACTIVE',
    'SUSPENDED',
    'DELETED'
  ))
);

CREATE TABLE USER_PROFILES (
  profile_id          BIGSERIAL PRIMARY KEY,
  user_id             int8 NOT NULL UNIQUE,
  first_name          varchar(255) NOT NULL,
  last_name           varchar(255) NOT NULL,
  phone_number        varchar(255) CHECK (phone_number IS NULL OR phone_number ~ '^\+?[0-9]{7,15}$'),
  profile_picture_url varchar(255),
  CONSTRAINT HAS_PROFILE FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
);

CREATE TABLE ADDRESSES (
  address_id     BIGSERIAL PRIMARY KEY,
  user_id        int8 NOT NULL,
  address_type   varchar(255),
  street_address varchar(255) NOT NULL,
  city           varchar(255) NOT NULL,
  country        varchar(255) NOT NULL,
  is_default     bool NOT NULL,
  CONSTRAINT HAS_ADDRESS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
);

CREATE TABLE USER_ROLES (
  user_id     int8 NOT NULL,
  role_id     int8 NOT NULL,
  assigned_at date NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT HAS_ROLE_ASSIGNMENT FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT IS_ASSIGNED_TO_USER FOREIGN KEY (role_id) REFERENCES ROLES (role_id) ON UPDATE Cascade ON DELETE Restrict
);

-- 3. КАТАЛОГ И ИНВЕНТАР
CREATE TABLE PRODUCTS (
  product_id      BIGSERIAL PRIMARY KEY,
  category_id     int8 NOT NULL,
  manufacturer_id int8 NOT NULL,
  name            varchar(255) NOT NULL,
  brand           varchar(255),
  description     text,
  CONSTRAINT CONTAINS_PRODUCT FOREIGN KEY (category_id) REFERENCES CATEGORIES (category_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT PRODUCED_BY FOREIGN KEY (manufacturer_id) REFERENCES MANUFACTURERS (manufacturer_id) ON UPDATE Cascade ON DELETE Restrict
);

CREATE TABLE PRODUCT_VARIANTS (
  variant_id      BIGSERIAL PRIMARY KEY,
  product_id      int8 NOT NULL,
  manufacturer_id int8 NOT NULL,
  sku             varchar(100) NOT NULL UNIQUE,
  price           numeric(12, 2) NOT NULL CHECK (price >= 0),
  stock_total     int4 NOT NULL CHECK (stock_total >= 0),
  CONSTRAINT HAS_VARIANT FOREIGN KEY (product_id) REFERENCES PRODUCTS (product_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT V_PRODUCED_BY FOREIGN KEY (manufacturer_id) REFERENCES MANUFACTURERS (manufacturer_id) ON UPDATE Cascade ON DELETE Restrict
);

CREATE TABLE WAREHOUSES (
  warehouse_id   BIGSERIAL PRIMARY KEY,
  warehouse_name varchar(255) NOT NULL,
  location       varchar(255),
  user_id        int8 NOT NULL, -- Сопственик (Seller)
  CONSTRAINT OWNED_BY_SELLER FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict
);

CREATE TABLE INVENTORY_ITEMS (
  inventory_id BIGSERIAL PRIMARY KEY,
  warehouse_id int8 NOT NULL,
  variant_id   int8 NOT NULL,
  quantity     int8 NOT NULL CHECK (quantity >= 0),
  CONSTRAINT MANAGES_STOCK FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (warehouse_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT STOCKED_AS FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict
);

CREATE TABLE PRODUCT_INSTANCES (
  instance_id   BIGSERIAL PRIMARY KEY,
  variant_id    int8 NOT NULL,
  warehouse_id  int8 NOT NULL,
  serial_number varchar(255) NOT NULL UNIQUE,
  status        varchar(20) NOT NULL,
  CONSTRAINT LOCATED_IN FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (warehouse_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT HAS_INSTANCE FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT chk_product_instances_status
  CHECK (status IN (
    'AVAILABLE',
    'RESERVED',
    'SHIPPED',
    'DELIVERED',
    'RETURNED',
    'DEFECTIVE'
  ))
);

CREATE TABLE WARRANTIES (
  warranty_id      BIGSERIAL PRIMARY KEY,
  instance_id      int8 NOT NULL UNIQUE,
  duration_months  int8 NOT NULL,
  terms_conditions text,
  CONSTRAINT PROVIDES_WARANTY FOREIGN KEY (instance_id) REFERENCES PRODUCT_INSTANCES (instance_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT chk_warranties_duration
CHECK (duration_months > 0)
);

CREATE TABLE PRODUCT_ATTRIBUTE_VALUES (
  variant_id   int8 NOT NULL,
  attribute_id int8 NOT NULL,
  attr_value   varchar(255) NOT NULL,
  PRIMARY KEY (variant_id, attribute_id),
  CONSTRAINT DEFINES_VALUE FOREIGN KEY (attribute_id) REFERENCES PRODUCT_ATTRIBUTES (attribute_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT HAS_ATTRIBUTE_VALUES FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade
);

CREATE TABLE PRODUCT_PRICE_HISTORY (
  history_id  BIGSERIAL PRIMARY KEY,
  variant_id  int8 NOT NULL,
  old_price   numeric(12, 2) NOT NULL CHECK (old_price >= 0),
  new_price   numeric(12, 2) NOT NULL CHECK (new_price >= 0),
  change_date timestamp with time zone NOT NULL,
  CONSTRAINT HAS_PRICE_HISTORY FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict
);

-- 4. ПРОДАЖБА И ТРАНСАКЦИИ (Виртуелна кошничка логика)
CREATE TABLE ORDERS (
  order_id           BIGSERIAL PRIMARY KEY,
  user_id            int8 NOT NULL,
  shipping_method_id int8 NOT NULL, -- Секоја кошничка мора да има иницијален метод
  order_date         timestamp with time zone, -- NULL додека е во статус 'CART'
  total_amount       numeric(12, 2) CHECK (total_amount >= 0), -- NULL додека се полни
  status             varchar(255) NOT NULL, -- 'CART', 'PLACED', 'COMPLETED'
  CONSTRAINT USED_FOR_ORDER FOREIGN KEY (shipping_method_id) REFERENCES SHIPPING_METHODS (method_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT PLACES_ORDER FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT chk_orders_status
  CHECK (status IN (
    'CART',
    'PLACED',
    'PAID',
    'SHIPPED',
    'COMPLETED',
    'CANCELLED'
  )),

  CONSTRAINT chk_orders_cart_logic
CHECK (
    (status = 'CART' AND order_date IS NULL AND total_amount IS NULL)
    OR
    (status <> 'CART' AND order_date IS NOT NULL AND total_amount IS NOT NULL)
  )
);

CREATE TABLE ORDER_ITEMS (
  order_item_id    BIGSERIAL PRIMARY KEY,
  order_id         int8 NOT NULL,
  price_history_id int8 NOT NULL,
  variant_id       int8 NOT NULL,
  quantity         int4 NOT NULL CHECK (quantity > 0),
  unit_price       numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
  CONSTRAINT CONTAINS_ORDER_ITEM FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT IS_ORDERED_AS_ITEM FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT DEFINES_ORDER_ITEM_PRICE FOREIGN KEY (price_history_id) REFERENCES PRODUCT_PRICE_HISTORY (history_id) ON UPDATE Cascade ON DELETE Restrict
);

CREATE TABLE PAYMENTS (
  payment_id     BIGSERIAL PRIMARY KEY,
  order_id       int8 NOT NULL,
  payment_method varchar(255) NOT NULL,
  amount         numeric(12, 2) NOT NULL CHECK (amount >= 0),
  payment_status varchar(255) NOT NULL,
  transaction_id varchar(255) NOT NULL UNIQUE,
  payment_date   timestamp with time zone NOT NULL,
  CONSTRAINT HAS_PAYMENT_ATTEMPT FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT chk_payments_status
  CHECK (payment_status IN (
    'PENDING',
    'SUCCESS',
    'FAILED',
    'REFUNDED'
  ))
);

CREATE TABLE SHIPMENTS (
  shipment_id       BIGSERIAL PRIMARY KEY,
  order_id          int8 NOT NULL,
  address_id        int8 NOT NULL,
  tracking_number   varchar(255)  UNIQUE,
  shipped_date      timestamp with time zone ,
  estimated_arrival date ,
  delivered_date timestamp with time zone,
  courier_id        int8 NOT NULL,
  status VARCHAR(20) NOT NULL,
  CONSTRAINT chk_shipments_status
  CHECK (status IN (
    'PENDING',
    'PROCESSING',
    'SHIPPED',
    'IN_TRANSIT',
    'DELIVERED',
    'FAILED',
    'RETURNED'
  )),
  CONSTRAINT chk_shipments_estimated_after_shipped
 CHECK (
    estimated_arrival IS NULL
    OR shipped_date IS NULL
    OR estimated_arrival >= shipped_date::date
 ),
  CONSTRAINT GENERATES_SHIPMENTS FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT IS_SHIPPED_TO FOREIGN KEY (address_id) REFERENCES ADDRESSES (address_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT RESPONSIBLE_FOR_SHIPMENT FOREIGN KEY (courier_id) REFERENCES USERS (user_id),
  CONSTRAINT chk_shipments_dates_tracking_by_status
CHECK (
    (
        status IN ('PENDING', 'PROCESSING')
        AND tracking_number IS NULL
        AND shipped_date IS NULL
        AND estimated_arrival IS NULL
        AND delivered_date IS NULL
    )
    OR
    (
        status IN ('SHIPPED', 'IN_TRANSIT')
        AND tracking_number IS NOT NULL
        AND shipped_date IS NOT NULL
        AND estimated_arrival IS NOT NULL
        AND delivered_date IS NULL
    )
    OR
    (
        status = 'DELIVERED'
        AND tracking_number IS NOT NULL
        AND shipped_date IS NOT NULL
        AND estimated_arrival IS NOT NULL
        AND delivered_date IS NOT NULL
    )
    OR
    (
        status IN ('FAILED', 'RETURNED')
        AND tracking_number IS NOT NULL
        AND shipped_date IS NOT NULL
        AND estimated_arrival IS NOT NULL
    )
)
);

CREATE TABLE SHIPMENT_ITEMS (
  shipment_item_id int8 PRIMARY KEY, -- Референца кон ORDER_ITEM_ID
  shipment_id      int8 NOT NULL,
  instance_id      int8 NOT NULL,
  quantity_shipped int4 NOT NULL DEFAULT 1,
  CONSTRAINT chk_shipment_items_quantity
  CHECK (quantity_shipped = 1),
  CONSTRAINT CONTAINS_SHIPMENT_ITEM FOREIGN KEY (shipment_id) REFERENCES SHIPMENTS (shipment_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT IS_SHIPPED_AS FOREIGN KEY (instance_id) REFERENCES PRODUCT_INSTANCES (instance_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT IS_FULFILLED_IN_SHIPMENT FOREIGN KEY (shipment_item_id) REFERENCES ORDER_ITEMS (order_item_id)
);

CREATE TABLE ORDER_STATUS_HISTORY (
  history_id  BIGSERIAL PRIMARY KEY,
  order_id    int8 NOT NULL,
  old_status  varchar(255) NOT NULL,
  new_status  varchar(255) NOT NULL,
  change_date timestamp with time zone NOT NULL,
  CONSTRAINT HAS_STATUS_CHANGE FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT chk_order_status_history_old_status
  CHECK (old_status IN (
    'CART',
    'PLACED',
    'PAID',
    'SHIPPED',
    'COMPLETED',
    'CANCELLED'
  )),
  CONSTRAINT chk_order_status_history_new_status
  CHECK (new_status IN (
    'CART',
    'PLACED',
    'PAID',
    'SHIPPED',
    'COMPLETED',
    'CANCELLED'
  ))
);

-- 5. МАРКЕТИНГ И ИНТЕРАКЦИЈА
CREATE TABLE REVIEWS (
  review_id    BIGSERIAL PRIMARY KEY,
  user_id      int8 NOT NULL,
  product_id   int8 NOT NULL,
  rating       int4 NOT NULL CHECK (rating >= 1 AND rating <= 5),
  comment_text text,
  created_at   timestamp with time zone NOT NULL,
  CONSTRAINT HAS_REVIEW FOREIGN KEY (product_id) REFERENCES PRODUCTS (product_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT WRITES_REVIEW FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict
);

CREATE TABLE REVIEW_HELPFULNESS (
  user_id   int8 NOT NULL,
  review_id int8 NOT NULL,
  vote_type varchar(255) NOT NULL,
  voted_at  timestamp with time zone NOT NULL,
  PRIMARY KEY (user_id, review_id),
  CONSTRAINT MARKS_HELPFULL FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT RECIEVES_HELPFULL_VOTE FOREIGN KEY (review_id) REFERENCES REVIEWS (review_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT chk_review_helpfulness_vote
  CHECK (vote_type IN (
    'HELPFUL',
    'NOT_HELPFUL'
  ))
);

CREATE TABLE LOYALTY_ACCOUNTS (
  loyalty_account_id BIGSERIAL PRIMARY KEY,
  user_id            int8 NOT NULL UNIQUE,
  current_balance    int4 NOT NULL CHECK (current_balance >= 0),
  last_updated       timestamp with time zone NOT NULL,
  CONSTRAINT HAS_LOYALTY_POINTS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
);

CREATE TABLE LOYALTY_HISTORY (
  history_id         BIGSERIAL PRIMARY KEY,
  loyalty_account_id int8 NOT NULL,
  order_id           int8 NOT NULL,
  payment_id         int8 NOT NULL,
  points_change      int4 NOT NULL,
  transaction_type   varchar(50) NOT NULL,
  description        text,
  created_at         timestamp with time zone NOT NULL,
  CONSTRAINT LOGS_BALANCE_CHANGE FOREIGN KEY (loyalty_account_id) REFERENCES LOYALTY_ACCOUNTS (loyalty_account_id),
  CONSTRAINT REDEEMS_POINTS_ON_ORDER FOREIGN KEY (order_id) REFERENCES ORDERS (order_id),
  CONSTRAINT EARNS_POINTS_BY_PAYMENT FOREIGN KEY (payment_id) REFERENCES PAYMENTS (payment_id),
  CONSTRAINT chk_loyalty_transaction_type
  CHECK (transaction_type IN (
    'EARN',
    'REDEEM',
    'REFUND',
    'ADJUSTMENT'
  ))
);

CREATE TABLE WISHLISTS (
  wishlist_id BIGSERIAL PRIMARY KEY,
  user_id     int8 NOT NULL UNIQUE,
  created_at  timestamp with time zone NOT NULL,
  CONSTRAINT HAS_WISHLIST FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
);

CREATE TABLE WISHLIST_ITEMS (
  wishlist_id int8 NOT NULL,
  variant_id  int8 NOT NULL,
  added_at    timestamp with time zone NOT NULL,
  PRIMARY KEY (wishlist_id, variant_id),
  CONSTRAINT CONTAINS_WISHLIST_ITEM FOREIGN KEY (wishlist_id) REFERENCES WISHLISTS (wishlist_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT IS_IN_WISHLIST FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade
);

CREATE TABLE PRODUCT_WAITLISTS (
  waitlist_id BIGSERIAL PRIMARY KEY,
  user_id     int8 NOT NULL,
  variant_id  int8 NOT NULL,
  added_at    timestamp with time zone NOT NULL,
  status      varchar(255) NOT NULL,
  CONSTRAINT uq_product_waitlist_user_variant UNIQUE (user_id, variant_id),
  CONSTRAINT JOINS_WAITLIST FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT HAS_WAITLIST_ENTRY FOREIGN KEY (variant_id) REFERENCES PRODUCT_VARIANTS (variant_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT chk_product_waitlists_status
  CHECK (status IN (
    'ACTIVE',
    'NOTIFIED',
    'REMOVED',
    'FULFILLED'
  ))
  
);

CREATE TABLE ORDER_DISCOUNTS (
  order_id     int8 NOT NULL,
  coupon_id    int8 NOT NULL,
  applied_at   timestamp with time zone NOT NULL,
  amount_saved numeric(12, 2) NOT NULL CHECK (amount_saved >= 0),
  PRIMARY KEY (order_id, coupon_id),
  CONSTRAINT APPLIES_COUPON FOREIGN KEY (order_id) REFERENCES ORDERS (order_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT IS_APPLIED_IN_ORDER FOREIGN KEY (coupon_id) REFERENCES COUPONS (coupon_id) ON UPDATE Cascade ON DELETE Restrict
);

-- 6. ОСТАНАТИ МОДУЛИ
CREATE TABLE AUTH_LOGS (
  log_id          BIGSERIAL PRIMARY KEY,
  user_id         int8 NOT NULL,
  login_timestamp timestamp with time zone NOT NULL,
  ip_address      varchar(45),
  device_info     varchar(255),
  status          varchar(255) NOT NULL,
  CONSTRAINT GENERATES_AUTH_LOGS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict,
    CONSTRAINT chk_auth_logs_status
  CHECK (status IN (
    'SUCCESS',
    'FAILED'
  ))
);

CREATE TABLE USER_SESSIONS (
  session_id    BIGSERIAL PRIMARY KEY,
  user_id       int8 NOT NULL,
  session_token varchar(255) NOT NULL UNIQUE,
  login_time    timestamp with time zone NOT NULL,
  expiry_time   timestamp with time zone NOT NULL,
  CONSTRAINT HAS_SESSION FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Restrict,
  CONSTRAINT chk_user_sessions_expiry
  CHECK (expiry_time > login_time)
);

CREATE TABLE USER_NOTIFICATIONS (
  notification_id BIGSERIAL PRIMARY KEY,
  user_id         int8 NOT NULL,
  type            varchar(255) NOT NULL,
  message         varchar(255) NOT NULL,
  sent_at         timestamp with time zone NOT NULL,
  CONSTRAINT RECIEVES_NOTIFICATIONS FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade
);

CREATE TABLE SUPPORT_TICKETS (
  ticket_id    BIGSERIAL PRIMARY KEY,
  user_id      int8 NOT NULL,
  subject      varchar(255) NOT NULL,
  message_body text NOT NULL,
  status       varchar(255) NOT NULL,
  priority     varchar(255) NOT NULL,
  CONSTRAINT CREATES_TICKET FOREIGN KEY (user_id) REFERENCES USERS (user_id) ON UPDATE Cascade ON DELETE Cascade,
  CONSTRAINT chk_support_tickets_status
CHECK (status IN (
    'OPEN',
    'IN_PROGRESS',
    'RESOLVED',
    'CLOSED'
)),

CONSTRAINT chk_support_tickets_priority
CHECK (priority IN (
    'LOW',
    'MEDIUM',
    'HIGH'
))
);