CREATE TABLE IF NOT EXISTS AppUser(
  user_id      SERIAL PRIMARY KEY,
  first_name   VARCHAR(255) NOT NULL,
  last_name    VARCHAR(255) NOT NULL,
  email        VARCHAR(255) NOT NULL CHECK (email LIKE '%@%.%'),
  password     VARCHAR(255) NOT NULL CHECK (LENGTH(password) >= 8),
  country_code VARCHAR(4) CHECK (country_code LIKE '^\+\d{1,3}$'),
  phone_number INT,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_verified  SMALLINT DEFAULT 0 NOT NULL CHECK (is_verified IN (0,1)),
  is_active    SMALLINT DEFAULT 1 NOT NULL CHECK (is_active IN (0,1))
);



CREATE TABLE IF NOT exists UserAddress (
  user_address_id SERIAL PRIMARY KEY,
  user_id         INT NOT NULL,
  country         VARCHAR(255) NOT NULL,
  city            VARCHAR(255) NOT NULL,
  street          VARCHAR(255) NOT NULL,
  house_number    INT NOT NULL,
  is_primary      SMALLINT NOT NULL,
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE IF NOT exists Category (
  category_id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description VARCHAR(255) NOT NULL,
  parent_category INT,
  CONSTRAINT fk_parent_category FOREIGN KEY(parent_category) REFERENCES Category(category_id) ON DELETE SET NULL ON UPDATE CASCADE
);


CREATE TABLE IF NOT exists CategoryAttributes (
  category_attribute_id SERIAL PRIMARY KEY,
  category_id INT NOT NULL,
  attribute_name VARCHAR(255) NOT NULL,
  CONSTRAINT fk_category FOREIGN KEY(category_id) REFERENCES Category(category_id) ON DELETE CASCADE ON UPDATE CASCADE
);



CREATE TABLE IF NOT exists Product (
  product_id  SERIAL PRIMARY KEY,
  title       VARCHAR(255) NOT NULL,
  description VARCHAR(255) NOT NULL,
  price       DECIMAL(10,2) NOT NULL,
  currency    VARCHAR(255) NOT NULL CHECK (currency IN ('MKD','EUR','USD')),
  category_id INT NOT NULL,
  seller_id   INT NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_active   BOOLEAN DEFAULT TRUE NOT NULL,
  quantity    INT DEFAULT 1,
  CONSTRAINT fk_product_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES Category(category_id) ON DELETE RESTRICT ON UPDATE CASCADE
);



CREATE TABLE if not exists ProductImages (
  image_id   SERIAL PRIMARY KEY,
  product_id INT NOT NULL,
  image_url  VARCHAR(255) NOT NULL,
  CONSTRAINT fk_image_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
);



CREATE TABLE IF NOT exists ProductAttributes (
  attribute_id          SERIAL PRIMARY KEY,
  product_id            INT NOT NULL,
  category_attribute_id INT NOT NULL,
  attribute_value       VARCHAR(255) NOT NULL,
  CONSTRAINT fk_attr_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_attr_category_attr FOREIGN KEY (category_attribute_id) REFERENCES CategoryAttributes(category_attribute_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE IF NOT exists ProductStats (
  product_id      INT PRIMARY KEY,
  views_count     INT DEFAULT 0 NOT NULL,
  favorites_count INT DEFAULT 0 NOT NULL,
  sells_count     INT DEFAULT 0 NOT NULL,
  CONSTRAINT fk_stats_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE IF NOT exists ProductViews (
  view_id    SERIAL PRIMARY KEY,
  user_id    INT NOT NULL,
  product_id INT NOT NULL,
  viewed_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_views_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_views_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE SET NULL ON UPDATE CASCADE
);


CREATE TABLE Package (
  package_id       SERIAL PRIMARY KEY,
  name             VARCHAR(255) NOT NULL CHECK (name IN ('BRONZE','SILVER','GOLD')), 
  description      VARCHAR(255) NOT NULL,
  currency         VARCHAR(3) DEFAULT 'EUR' NOT NULL CHECK (currency IN ('EUR','USD','MKD')),
  price            INT NOT NULL CHECK (
    (name = 'BRONZE' AND price = 2) OR
    (name = 'SILVER' AND price = 5) OR
    (name = 'GOLD' AND price = 10)
  ), 
  visibility_level VARCHAR(255) NOT NULL CHECK (visibility_level IN ('BASIC','STANDARD','PREMIUM')), 
  duration_days    INT NOT NULL DEFAULT 30
);


CREATE TABLE UserPackages (
  user_package_id SERIAL PRIMARY KEY, 
  seller_id       INT NOT NULL, 
  package_id      INT NOT NULL, 
  start_date      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  end_date        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  CONSTRAINT fk_user_package_package FOREIGN KEY (package_id) REFERENCES Package(package_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_user_package_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE Favorites (
  favorite_id SERIAL PRIMARY KEY,
  user_id     INT NOT NULL,
  product_id  INT NOT NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_fav_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_fav_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE Cart (
  cart_id     SERIAL PRIMARY KEY,
  user_id     INT NOT NULL,
  created_at  DATE NOT NULL,
  total_price INT NOT NULL,
  CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);



CREATE TABLE CartItems (
  cart_item_id  SERIAL PRIMARY KEY,
  cart_id       INT NOT NULL,
  product_id    INT NOT NULL,
  quantity      INT NOT NULL,
  price_at_time INT NOT NULL,
  CONSTRAINT fk_cartitem_cart FOREIGN KEY (cart_id) REFERENCES Cart(cart_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cartitem_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TYPE order_status AS ENUM ('PENDING','CONFIRMED','SHIPPED','DELIVERED','CANCELLED');

CREATE TABLE "Order" (
  order_id    SERIAL PRIMARY KEY,
  buyer_id    INT NOT NULL,
  cart_id     INT,
  total_price DECIMAL(10,2) NOT NULL,
  status      order_status NOT NULL DEFAULT 'PENDING',
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_order_buyer FOREIGN KEY (buyer_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_order_cart FOREIGN KEY (cart_id) REFERENCES Cart(cart_id) ON DELETE SET NULL ON UPDATE CASCADE
);


CREATE TABLE OrderItems (
  order_item_id SERIAL PRIMARY KEY,
  order_id      INT NOT NULL,
  product_id    INT NOT NULL,
  seller_id     INT NOT NULL,
  price_at_time DECIMAL(10,2) NOT NULL,
  quantity      INT NOT NULL CHECK (quantity > 0),
  CONSTRAINT fk_orderitem_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_orderitem_order FOREIGN KEY (order_id) REFERENCES "Order"(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_orderitem_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
);



CREATE TABLE Carriers (
  carrier_id    SERIAL PRIMARY KEY,
  name          VARCHAR(255) NOT NULL,
  website       VARCHAR(255),
  contact_email VARCHAR(255) NOT NULL,
  contact_phone VARCHAR(20) NOT NULL CHECK (contact_phone LIKE '+%'),
  joined_at     DATE NOT NULL,
  is_active     SMALLINT DEFAULT 1 NOT NULL CHECK (is_active IN (0,1))
);



CREATE TABLE Shipment (
  shipment_id             SERIAL PRIMARY KEY,
  order_id                INT NOT NULL,
  carrier_id              INT NOT NULL,
  user_address_id         INT NOT NULL,
  tracking_number         VARCHAR(255) NOT NULL UNIQUE,
  status                  VARCHAR(255) NOT NULL CHECK (status IN ('PROCESSING','TRANSIT','DELIVERED')),
  delivery_method         VARCHAR(255) NOT NULL CHECK (delivery_method IN ('STANDARD','EXPRESS')),
  estimated_delivery_date DATE NOT NULL,
  actual_delivery_date    DATE,
  created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_shipment_address FOREIGN KEY (user_address_id) REFERENCES UserAddress(user_address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_shipment_order FOREIGN KEY (order_id) REFERENCES "Order"(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_shipment_carrier FOREIGN KEY (carrier_id) REFERENCES Carriers(carrier_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE Payment (
  payment_id       SERIAL PRIMARY KEY,
  user_id          INT NOT NULL,
  order_id         INT NOT NULL,
  package_id       INT NOT NULL,
  amount           INT NOT NULL,
  payment_method   VARCHAR(255) NOT NULL CHECK (payment_method IN ('CARD','PAYPAL','CRYPTO','CASH')),
  transaction_date DATE NOT NULL,
  CONSTRAINT fk_payment_user FOREIGN KEY (user_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_payment_order FOREIGN KEY (order_id) REFERENCES "Order"(order_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_payment_package FOREIGN KEY (package_id) REFERENCES UserPackages(user_package_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE Transactions (
  transaction_id   SERIAL PRIMARY KEY,
  payment_id       INT NOT NULL,
  seller_id        INT NOT NULL,
  amount           INT NOT NULL,
  status           VARCHAR(255) NOT NULL CHECK (status IN ('PENDING','PROCESSING','COMPLETED','FAILED','CANCELLED')),
  transaction_date DATE NOT NULL,
  CONSTRAINT fk_transaction_payment FOREIGN KEY (payment_id) REFERENCES Payment(payment_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_transaction_seller FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE RESTRICT ON UPDATE CASCADE
);



CREATE TABLE Conversation (
  conversation_id SERIAL PRIMARY KEY,
  product_id      INT NOT NULL,
  buyer_id        INT NOT NULL,
  seller_id       INT NOT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  UNIQUE (product_id, buyer_id, seller_id),
  CHECK (buyer_id <> seller_id),
  
  CONSTRAINT fk_conversation_product FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_conversation_seller  FOREIGN KEY (seller_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_conversation_buyer   FOREIGN KEY (buyer_id) REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE Message (
  message_id      SERIAL PRIMARY KEY,
  conversation_id INT NOT NULL,
  sender_id       INT NOT NULL,
  text            VARCHAR(255) NOT NULL,
  send_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  seen            SMALLINT NOT NULL CHECK (seen IN (0,1)),

  CONSTRAINT fk_message_conversation FOREIGN KEY (conversation_id) 
      REFERENCES Conversation(conversation_id) ON DELETE CASCADE,
  CONSTRAINT fk_message_sender FOREIGN KEY (sender_id) 
      REFERENCES AppUser(user_id) ON DELETE RESTRICT
);


CREATE TABLE Badge (
  badge_id    SERIAL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL CHECK (name IN (
    'NEW_SELLER','VERIFIED','TOP_SELLER','TRUSTED_SELLER','POPULAR',
    'ACTIVE_USER','LOYAL_CUSTOMER',
    'FIRST_SALE','HIGH_VOLUME',
    'TOP_RATED','RECOMMENDED','PREMIUM_SELLER','GOLD_SELLER'
  )),
  description VARCHAR(255) NOT NULL,
  icon_url    VARCHAR(255) NOT NULL
);

CREATE TABLE UserBadge (
  user_badge_id SERIAL PRIMARY KEY,
  user_id       INT NOT NULL,
  badge_id      INT NOT NULL,
  awarded_at    DATE NOT NULL,
  CONSTRAINT fk_userbadge_user FOREIGN KEY (user_id)
      REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_userbadge_badge FOREIGN KEY (badge_id)
      REFERENCES Badge(badge_id) ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE Review (
  review_id   SERIAL PRIMARY KEY,
  product_id  INT NOT NULL,
  buyer_id    INT NOT NULL,
  seller_id   INT NOT NULL,
  rating      INT NOT NULL CHECK (rating > 0 AND rating < 6),
  comment     VARCHAR(255) NOT NULL,
  created_at  DATE NOT NULL,
  CONSTRAINT fk_review_product FOREIGN KEY (product_id)
      REFERENCES Product(product_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_review_seller FOREIGN KEY (seller_id)
      REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_review_buyer FOREIGN KEY (buyer_id)
      REFERENCES AppUser(user_id) ON DELETE SET NULL ON UPDATE CASCADE
);


CREATE TABLE SavedSearches (
  search_id   SERIAL PRIMARY KEY,
  user_id     INT NOT NULL,
  category_id INT NOT NULL,
  min_price   INT NOT NULL,
  max_price   INT NOT NULL,
  location    VARCHAR(255) NOT NULL,
  filters     VARCHAR(255),
  CONSTRAINT fk_savedsearch_user FOREIGN KEY (user_id)
      REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_savedsearch_category FOREIGN KEY (category_id)
      REFERENCES Category(category_id) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE SavedSearchesAttributes (
  saved_search_attribute_id SERIAL PRIMARY KEY,
  saved_search_id           INT NOT NULL,
  category_attribute_id     INT NOT NULL,
  attribute_value           VARCHAR(255) NOT NULL,
  CONSTRAINT fk_savedsearchattr_savedsearch FOREIGN KEY (saved_search_id)
      REFERENCES SavedSearches(search_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_savedsearchattr_categoryattr FOREIGN KEY (category_attribute_id)
      REFERENCES CategoryAttributes(category_attribute_id) ON DELETE CASCADE ON UPDATE CASCADE
);



CREATE TABLE Notifications (
  notification_id SERIAL PRIMARY KEY,
  user_id         INT NOT NULL,
  type            VARCHAR(255) NOT NULL,
  title           VARCHAR(255) NOT NULL,
  message         VARCHAR(255) NOT NULL,
  favorite_id     INT NOT NULL,
  saved_search_id INT NOT NULL,
  message_id      INT,
  order_id        INT NOT NULL,
  CONSTRAINT fk_notifications_order FOREIGN KEY (order_id)
      REFERENCES "Order"(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_notifications_favorite FOREIGN KEY (favorite_id)
      REFERENCES Favorites(favorite_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_notifications_savedsearch FOREIGN KEY (saved_search_id)
      REFERENCES SavedSearches(search_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id)
      REFERENCES AppUser(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);




CREATE TABLE IF NOT EXISTS Country (
  country_id   SERIAL PRIMARY KEY,
  country_name VARCHAR(255) NOT NULL,
  country_code VARCHAR(4) NOT NULL CHECK (country_code LIKE '^\+\d{1,3}$')
);


ALTER TABLE AppUser
ADD COLUMN country_id INT;



ALTER TABLE AppUser
ADD CONSTRAINT fk_user_country
FOREIGN KEY (country_id)
REFERENCES Country(country_id);