source: scripts/kreiranje.sql

--delete tables
drop table if exists project.UserFavourites;
drop table if exists project.UserComments;
drop table if exists project.Ratings;
drop table if exists project.WatchListProducts;
drop table if exists project.ProductImages;
drop table if exists project.ProductInstances;
drop table if exists project.belongs_to;
drop table if exists project.Users;
drop table if exists project.Roles;
drop table if exists project.Products;
drop table if exists project.ProductCharacteristics;
drop table if exists project.Manufacturers;
drop table if exists project.Categories;
drop table if exists project.Stores;


--create tables
create table project.Roles(
        role_id serial not null,
        role_description varchar(300) not null,
        role_name varchar(100) not null,
        
        constraint pk_Roles primary key (role_id),
        constraint un_Roles_roleName unique (role_name)
);

create table project.Users(
        user_id serial not null,
        user_username varchar(100) not null,
        user_password varchar(200) not null,
        user_name varchar(100) not null,
        user_surname varchar(100) not null,
        user_picture_url varchar(200),
        user_email varchar(100) not null,
        user_phone_number varchar(100) not null,
        
        role_id integer not null,
        
        constraint pk_Users primary key (user_id),
        constraint un_Users_userUsername unique (user_username),
        constraint un_Users_userEmail unique (user_email),
        constraint fk_Users_Roles foreign key (role_id) references project.Roles(role_id)
);


create table project.Manufacturers(
        manufacturer_id serial not null,
        manufacturer_name varchar(100) not null,
        manufacturer_country varchar(100) not null,
        
        constraint pk_Manufacturers primary key (manufacturer_id),
        constraint un_Manufacturers_manufacturerName unique (manufacturer_name)
);

create table project.Categories(
        category_id serial not null,
        parent_category_id integer,
        category_name varchar(100) not null,
        
        constraint pk_Categories primary key (category_id),
        constraint un_Categories_categoryName unique (category_name),
        constraint fk_Categories_Categories foreign key (parent_category_id) references project.Categories(category_id)
);

create table project.ProductCharacteristics(
        characteristic_id serial not null,
        characteristic_description varchar(300) not null,
        
        constraint pk_ProductCharacteristics primary key (characteristic_id)
);

create table project.Products (
        product_id serial not null,
        product_name varchar(100) not null,
        post_date date not null,
        
        manufacturer_id integer not null,
        category_id integer not null,
        characteristic_id integer not null,
        
        constraint pk_Products primary key (product_id),
        constraint un_Products_productName unique (product_name),
        constraint un_Products_characteristicId unique (characteristic_id),
        constraint fk_Products_Manufacturers foreign key (manufacturer_id) references project.Manufacturers(manufacturer_id),
        constraint fk_Products_Categories foreign key (category_id) references project.Categories(category_id),
        constraint fk_Products_ProductCharacteristics foreign key (characteristic_id) references project.ProductCharacteristics(characteristic_id)
);

create table project.UserFavourites(
        user_favourite_id serial not null,
        user_id integer not null,
        product_id integer not null,
        
        constraint pk_UserFavourites primary key (user_favourite_id),
        constraint fk_UserFavourites_Users foreign key (user_id) references project.Users(user_id),
        constraint fk_UserFavourites_Products foreign key (product_id) references project.Products(product_id)
);

create table project.UserComments(
        comment_id serial not null,
        user_comment varchar(300) not null,

        user_id integer not null,
        product_id integer not null,
        
        constraint pk_UserComments primary key (comment_id),
        constraint fk_UserComments_Users foreign key (user_id) references project.Users(user_id),
        constraint fk_UserComments_Products foreign key (product_id) references project.Products(product_id)
);

create table project.Ratings (
        rating_id serial not null,
        rating_value integer not null,
        
        user_id integer not null,
        product_id integer not null,
        
        constraint pk_Ratings primary key (rating_id),
        constraint fk_Ratings_Users foreign key (user_id) references project.Users(user_id),
        constraint fk_Ratings_Products foreign key (product_id) references project.Products(product_id)
);

create table project.WatchListProducts(
        watch_list_product_id serial not null,
        price_limit integer not null,
        
        user_id integer not null,       
        product_id integer not null,
        
        constraint pk_WatchListProducts primary key (watch_list_product_id),
        constraint fk_WatchListProducts_Users foreign key (user_id) references project.Users(user_id),
        constraint fk_WatchListProducts_Products foreign key (product_id) references project.Products(product_id)
);

create table project.ProductImages(
        product_image_id serial not null,
        product_image_url varchar(100) not null,
        
        product_id integer not null,
        
        constraint pk_ProductImages primary key (product_image_id),
        constraint fk_ProductImages_Products foreign key (product_id) references project.Products(product_id)
);

create table project.Stores(
        store_id serial not null,
        store_name varchar(100) not null,
        
        constraint pk_Stores primary key (store_id),
        constraint un_Stores_storeName unique (store_name)
);

create table project.ProductInstances(
        product_instance_id serial not null,
        product_instance_price integer not null,
        
        product_id integer not null,
        store_id integer not null,

        constraint pk_ProductInstances primary key (product_instance_id),
        constraint fk_ProductInstances_Products foreign key (product_id) references project.Products(product_id),
        constraint fk_ProductInstances_Stores foreign key (store_id) references project.Stores(store_id)
);


create table project.belongs_to(
        product_id integer not null,
        category_id integer not null,
        
        constraint pk_belongs_to primary key (product_id, category_id),
        constraint fk_belongs_to_Products foreign key (product_id) references project.Products(product_id),
        constraint fk_belongs_to_Categories foreign key (category_id) references project.Categories(category_id)
);