drop table if exists user_table cascade;
drop table if exists administrator cascade;
drop table if exists delivery_man cascade;
drop table if exists service_man cascade;
drop table if exists customer cascade;
drop table if exists warehouseman cascade;
drop table if exists warehouse cascade;
drop table if exists vehicle cascade;
drop table if exists delivery cascade;
drop table if exists review cascade;
drop table if exists order_table cascade;
drop table if exists manufacturer cascade;
drop table if exists category cascade;
drop table if exists subcategory cascade;
drop table if exists product cascade;
drop table if exists order_table_contains_product cascade;
drop table if exists product_is_in_category cascade;
drop table if exists product_is_in_stock_in_warehouse cascade;
drop table if exists vehicle_is_used_for_delivery cascade;


create table user_table (
    user_id serial primary key,
    username varchar(100) unique not null,
    name_user varchar(100) not null,  
    email varchar(100) unique not null,
    password varchar(100) not null,
    phone_number varchar(100),
    administrator_id integer
);
   
create table administrator(
    user_id serial primary key,
        
    constraint user_id_fk foreign key(user_id)
	references user_table(user_id)
);

alter table user_table add
	constraint administrator_id_fk foreign key(administrator_id)
	references administrator(user_id);
	
create table delivery_man(
    user_id serial primary key,
       
    constraint user_id_fk foreign key(user_id)
	references user_table(user_id)
);

create table service_man(
    user_id serial primary key,
       
    constraint user_id_fk foreign key(user_id)
	references user_table(user_id)
);

create table customer(
    user_id serial primary key,
        
    constraint user_id_fk foreign key(user_id)
	references user_table(user_id)
);

create table warehouse(
	warehouse_id serial primary key,
	warehouse_location varchar(100) not null
);

create table warehouseman(
    user_id serial primary key,
    warehouse_id integer not null,
       
    constraint user_id_fk foreign key(user_id)
    references user_table(user_id),
    constraint warehouse_id_fk foreign key(warehouse_id)
    references warehouse(warehouse_id)
);


create table order_table (
	order_id serial primary key,
	order_status varchar(100) not null,
	order_date Date not null,
	customer_id integer not null,
	
	constraint customer_id_fk foreign key(customer_id)
	references customer(user_id)
);


create table vehicle (
	vehicle_id serial primary key,
	vehicle_model varchar(100),
	vehicle_service Date,
	delivery_man_id integer not null,
	
	constraint delivery_man_id_fk foreign key(delivery_man_id)
	references delivery_man(user_id)
);

create table delivery (
	delivery_id serial primary key,
	delivery_status varchar(100) not null,
	delivery_address varchar(100) not null,
	delivery_man_id integer not null,
	order_id integer not null,
	
	constraint delivery_man_id_fk foreign key(delivery_man_id)
	references delivery_man(user_id),
	constraint order_id_fk foreign key(order_id)
	references order_table(order_id)
);



create table category (
	category_id serial primary key,
	category_name varchar(100) not null
);


create table subcategory (
	subcategory_id serial not null,
	subcategory_name varchar(100) not null,
	category_id integer not null,
	
	
	constraint category_id_fk foreign key(category_id)
	references category(category_id)
);


create table manufacturer (
	manufacturer_id serial primary key,
	manufacturer_name varchar(100)
);

create table product (
	product_id serial primary key,
	product_name varchar(100) not null,
	product_description varchar(3000) not null,
	product_price integer not null,
	product_warranty integer not null,
	product_image varchar not null,
	manufacturer_id integer not null,
	service_man_id integer not null,
	
	
	constraint manufacturer_id_fk foreign key(manufacturer_id)
	references manufacturer(manufacturer_id),
	constraint service_man_fk foreign key(service_man_id)
	references service_man(user_id)
);

create table review (
	review_id serial primary key,
	review_rating integer not null,
	review_description varchar(2000) not null,
	customer_id integer not null,
	product_id integer not null,
	
	constraint customer_id_fk foreign key(customer_id)
	references customer(user_id),
	constraint product_id_fk foreign key(product_id)
	references product(product_id)
	
);

create table vehicle_is_used_for_delivery (
	vehicle_id integer not null,
	delivery_id integer not null,
	
	constraint vehicle_id_fk foreign key(vehicle_id)
	references vehicle(vehicle_id),
	constraint delivery_id_fk foreign key(delivery_id)
	references delivery(delivery_id)
);

create table product_is_in_stock_in_warehouse (
	product_id integer not null,
	warehouse_id integer not null,
	quantity integer not null,
	
	constraint product_id_fk foreign key(product_id)
	references product(product_id),
	constraint warehouse_id_fk foreign key(warehouse_id)
	references warehouse(warehouse_id)
);

create table order_table_contains_product (
	order_id integer not null,
	product_id integer not null,
	quantity integer not null,
	
	constraint order_id_fk foreign key(order_id)
	references order_table(order_id),
	constraint product_id_fk foreign key(product_id)
	references product(product_id)
);

create table product_is_in_category (
	product_id integer not null,
	category_id integer not null,
	
	constraint product_id_fk foreign key(product_id)
	references product(product_id),
	constraint category_id_fk foreign key(category_id)
	references category(category_id)
);


alter table subcategory add constraint subcategory_pk unique (subcategory_id, category_id);

