= DDL =
== 1. Вовед ==
DDL-от беше генериран од страна на Visual Paradigm, испробан локално и дополнет со constraints. Потоа, направивме pg_dump и го импортиравме на нашата remote база за предметот.

== DDL: ==

CREATE TABLE "User" (
  user_id SERIAL,
  name varchar(255),
  surname varchar(255),
  email varchar(255),
  password varchar(255),
  phone_number varchar(255),
  registration_date date,
  PRIMARY KEY (user_id),

  CONSTRAINT email_check CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),

  CONSTRAINT password_complexity CHECK (
    password ~ '[0-9]' AND
    password ~ '[^a-zA-Z0-9]'
  )
);

CREATE TABLE VehicleType (
  vehicle_type_id SERIAL,
  vehicle_type varchar(255),
  PRIMARY KEY (vehicle_type_id)
);

CREATE TABLE Vehicle (
  vehicle_id SERIAL,
  registration varchar(255),
  vehicle_type_id int DEFAULT -1,
  PRIMARY KEY (vehicle_id),
  FOREIGN KEY (vehicle_type_id) REFERENCES VehicleType(vehicle_type_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Driver (
  driver_id int DEFAULT -1,
  vehicle_id int DEFAULT -1,
  status varchar(255),
  hire_date date,
  PRIMARY KEY (driver_id),
  FOREIGN KEY (driver_id) REFERENCES "User"(user_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (vehicle_id) REFERENCES Vehicle(vehicle_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Customer (
  customer_id int DEFAULT -1,
  PRIMARY KEY (customer_id),
  FOREIGN KEY (customer_id) REFERENCES "User"(user_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE LoyalCustomer (
  loyalty_id int DEFAULT -1,
  points int DEFAULT 0 CHECK (points >= 0),
  joined_day date,
  birthday date,
  PRIMARY KEY (loyalty_id),
  FOREIGN KEY (loyalty_id) REFERENCES Customer(customer_id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE City (
  city_id SERIAL,
  city_name varchar(255),
  postal_code int,
  PRIMARY KEY (city_id)
);

CREATE TABLE StoreType (
  store_type_id SERIAL,
  type_name varchar(255),
  description varchar(255),
  PRIMARY KEY (store_type_id)
);

CREATE TABLE Store (
  store_id SERIAL,
  store_name varchar(255),
  city_id int DEFAULT -1,
  store_type_id int DEFAULT -1,
  rating double precision CHECK (rating >= 0 AND rating <= 5),
  PRIMARY KEY (store_id),
  FOREIGN KEY (city_id) REFERENCES City(city_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (store_type_id) REFERENCES StoreType(store_type_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE DeliveryZone (
  zone_id SERIAL,
  city_id int DEFAULT -1,
  zone_name varchar(255),
  PRIMARY KEY (zone_id),
  FOREIGN KEY (city_id) REFERENCES City(city_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Address (
  address_id SERIAL,
  zone_id int DEFAULT -1,
  street varchar(255),
  street_number int,
  door_number int,
  PRIMARY KEY (address_id),
  FOREIGN KEY (zone_id) REFERENCES DeliveryZone(zone_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE StoreInstance (
  store_instance_id SERIAL,
  store_id int DEFAULT -1,
  address_id int DEFAULT -1,
  phone varchar(255),
  zone_id int DEFAULT -1,
  PRIMARY KEY (store_instance_id),
  FOREIGN KEY (store_id) REFERENCES Store(store_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (address_id) REFERENCES Address(address_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (zone_id) REFERENCES DeliveryZone(zone_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Delivery_zone_Store_instance (
  zone_id int DEFAULT -1,
  store_instance_id int DEFAULT -1,


  FOREIGN KEY (zone_id) REFERENCES DeliveryZone(zone_id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  FOREIGN KEY (store_instance_id) REFERENCES StoreInstance(store_instance_id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  PRIMARY KEY (zone_id, store_instance_id)
);

CREATE TABLE Product (
  product_id SERIAL,
  store_id int DEFAULT -1,
  name varchar(255),
  price int DEFAULT 0 CHECK (price >= 0),
  PRIMARY KEY (product_id),
  FOREIGN KEY (store_id) REFERENCES Store(store_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Promotion (
  promotion_id SERIAL,
  store_id int DEFAULT -1,
  promotion_name varchar(255),
  discount_percent int DEFAULT 0,
  start_date date,
  end_date date,
  PRIMARY KEY (promotion_id),
  FOREIGN KEY (store_id) REFERENCES Store(store_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  CHECK (discount_percent >= 0 AND discount_percent <= 100),
  CHECK (start_date <= end_date)
);

CREATE TABLE "Order" (
  order_id SERIAL,
  customer_id int DEFAULT -1,
  promotion_id int DEFAULT -1,
  driver_id int DEFAULT -1,
  store_instance_id int DEFAULT -1,
  delivery_zone_id int DEFAULT -1,
  status varchar(255),
  delivery_fee int DEFAULT 0 CHECK (delivery_fee >= 0),
  total_price int DEFAULT 0 CHECK (total_price >= 0),
  PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (promotion_id) REFERENCES Promotion(promotion_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (driver_id) REFERENCES Driver(driver_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (store_instance_id) REFERENCES StoreInstance(store_instance_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (delivery_zone_id) REFERENCES DeliveryZone(zone_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE OrderItem (
  order_item_id SERIAL,
  order_id int DEFAULT -1,
  product_id int DEFAULT -1,
  quantity int DEFAULT 1 CHECK (quantity > 0),
  PRIMARY KEY (order_item_id),
  FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (product_id) REFERENCES Product(product_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Review (
  review_id SERIAL,
  order_id int DEFAULT -1,
  customer_id int DEFAULT -1,
  rating double precision CHECK (rating >= 0 AND rating <= 5),
  comment varchar(255),
  review_date date,
  PRIMARY KEY (review_id),
  FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Products_review (
  review_id int DEFAULT -1,
  store_instance_id int DEFAULT -1,
  FOREIGN KEY (review_id  ) REFERENCES Review(review_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (store_instance_id ) REFERENCES StoreInstance(store_instance_id)
    ON DELETE SET default
    ON UPDATE CASCADE,
  PRIMARY KEY(review_id));

CREATE TABLE Delivery_review (
  review_id int DEFAULT -1,
  driver_id int DEFAULT -1,
  FOREIGN KEY (review_id  ) REFERENCES Review(review_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  FOREIGN KEY (driver_id ) REFERENCES Driver(driver_id)
    ON DELETE SET default
    ON UPDATE CASCADE,
  PRIMARY KEY(review_id));


CREATE TABLE Payment_method (
  payment_method_id SERIAL,
  payment varchar(255),
  PRIMARY KEY (payment_method_id)
);

CREATE TABLE Payment (
  payment_id SERIAL,
  status varchar(255),
  payment_date date,
  payment_method_id int DEFAULT -1,
  order_id int DEFAULT -1,
  PRIMARY KEY (payment_id),
  FOREIGN KEY (payment_method_id) REFERENCES Payment_method(payment_method_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE,
  FOREIGN KEY (order_id) REFERENCES "Order"(order_id)
    ON DELETE SET DEFAULT ON UPDATE CASCADE
);

CREATE TABLE Customer_payment_method (
  customer_id int DEFAULT -1,
  payment_method_id int DEFAULT -1,
  date_added date DEFAULT CURRENT_DATE,

  -- Композитен примарен клуч


  -- Референца кон Customer
  FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  -- Референца кон Payment_method
  FOREIGN KEY (payment_method_id) REFERENCES Payment_method(payment_method_id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  PRIMARY KEY (customer_id, payment_method_id)
);

CREATE TABLE Customer_Address (
  customer_id int DEFAULT -1,
  address_id int DEFAULT -1,
  address_label varchar(255), -- Опционално: за ознаки како 'Home', 'Work'
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  FOREIGN KEY (address_id) REFERENCES Address(address_id)
    ON DELETE CASCADE ON UPDATE CASCADE,

      PRIMARY KEY (customer_id, address_id)
);