RelationalDesign: kreiranje.sql

drop schema if exists project cascade;
create schema project;

drop table if exists users;
drop table if exists client;
drop table if exists shipping_employee;
drop table if exists roles;
drop table if exists online_stores;
drop table if exists shipping;
drop table if exists payment;
drop table if exists category;
drop table if exists store_employee;
drop table if exists product_in_store;
drop table if exists catalogue;
drop table if exists price;
drop table if exists shopping_bag;
drop table if exists product;
drop table if exists works_as;
drop table if exists exists_;
drop table if exists orders;

create table users (
  
  user_id integer primary key,
  user_name varchar(50) not null,
  surname varchar(50) not null,
  email varchar(50) not null,
  username varchar(50) not null,
  user_password varchar(50) not null

);

create table roles (

   id_role integer primary key,
   role_name varchar(50) not null,
   role_desc varchar(200) not null

);

create table online_stores (

  store_id integer primary key,
  names varchar(50) not null,
  web_address varchar(50) not null,
  phone_number varchar(50) not null,
  email varchar(50) not null,
  social_media varchar(50) not null

);

create table category(

   category_id integer primary key,
   category_name varchar(50) not null

);

create table client (
   
   client_number_id serial not null,
   user_id integer not null,
   constraint fk_client_users foreign key (user_id) references users(user_id),
   constraint pk_client_users primary key (user_id)
);


create table shipping_employee (

    shipping_employee_id serial not null,
    user_id integer not null,
        constraint fk_shipping_employee_users foreign key (user_id) references users(user_id),
        constraint pk_shipping_employee_users primary key (user_id)

);

create table store_employee (

  store_employee_id integer not null,
  store_id integer not null,
  user_id integer not null,
  constraint fk_id_store foreign key (store_id) references online_stores(store_id),
  constraint fk_store_employee_users foreign key (user_id) references users(user_id),
  constraint pk_store_employee_users primary key (user_id)

);

create table works_as (
   
  works_from timestamp not null,
  works_to timestamp,
  id_role integer not null,
  user_id integer not null,
  constraint fk_role_id foreign key (id_role) references roles(id_role),
  constraint fk_id_employee_store foreign key (user_id) references users(user_id),
  constraint pk_works_as primary key (id_role, user_id)
  
);

create table catalogue(

  catalogue_id integer,
  catalogue_from timestamp not null,
  catalogue_to timestamp not null,
  type_ varchar(50) not null,
  store_id integer not null,
  constraint fk_catalogue_online_stores foreign key (store_id) references online_stores(store_id),
  constraint pk_catalogue_id primary key (catalogue_id, store_id)

);

create table product (

   product_id integer primary key,
   names varchar(50) not null,
   in_store bool not null,
   sizes varchar(50) not null,
   color varchar(50) not null,
   category_id integer not null,
   constraint fk_product_category foreign key (category_id) references category (category_id)

);


create table product_in_store (
  
   product_in_store_id integer primary key,
   store_id integer not null,
   product_id integer not null,
   constraint fk_product_in_store_online_stores foreign key (store_id) references online_stores (store_id),
   constraint fk_product_in_store_product foreign key (product_id) references product (product_id)
     
);

create table existss (

  product_in_store_id integer not null,
  catalogue_id integer not null,
  store_id integer not null,
  constraint fk_id_catalogue foreign key (catalogue_id, store_id) references catalogue (catalogue_id, store_id),
  constraint fk_id_product_in_store2 foreign key (product_in_store_id) references product_in_store (product_in_store_id),
  constraint pk_exists primary key (catalogue_id, store_id, product_in_store_id)

  );
 
 create table price (

  price_id integer not null,
  price integer not null,
  price_from timestamp not null,
  price_to timestamp not null,
  product_in_store_id integer not null,
  constraint fk_price_product_in_store foreign key (product_in_store_id) references product_in_store (product_in_store_id),
  constraint pk_price_product_in_store primary key (product_in_store_id, price_id)

);


create table shopping_bag(

  shopping_id integer primary key,
  order_date timestamp not null,
  shipping_date timestamp not null,
  modified timestamp not null,
  user_id integer not null,
  constraint fk_id_client_number foreign key (user_id) references client (user_id)  

);

create table payment(

 payment_id integer primary key,
 info varchar(50) not null,
 shopping_id integer not null,
 constraint fk_payment_shopping_id foreign key (shopping_id) references shopping_bag (shopping_id)

);


create table shipping (

   shipping_id integer primary key,
   locations varchar(50) not null,
   date_from timestamp not null,
   date_to timestamp not null,
   user_id integer not null,
   shopping_id integer not null,
   constraint fk_shipping_user foreign key (user_id) references users(user_id),
   constraint fk_shipping_shopping foreign key (shopping_id) references shopping_bag(shopping_id)

);
        
create table orders (

 amount integer not null,
 shopping_id integer not null,
 product_in_store_id integer not null,
 price_id integer not null,
 constraint fk_order_shopping foreign key (shopping_id) references shopping_bag(shopping_id),
 constraint fk_order_product_in_store foreign key (product_in_store_id, price_id) references price(product_in_store_id, price_id),
 constraint pk_order primary key(shopping_id, product_in_store_id, price_id)
);