CREATE SCHEMA project AUTHORIZATION db_202122z_va_prj_pcpicco_owner;

CREATE TABLE project."User" (
	user_id int4 NOT NULL,
	"Password" varchar NOT NULL,
	CONSTRAINT user_pk PRIMARY KEY (user_id)
);

CREATE TABLE project."Admin" (
	admin_id int4 NOT NULL,
	CONSTRAINT admin_pk PRIMARY KEY (admin_id),
	CONSTRAINT admin_fk FOREIGN KEY (admin_id) REFERENCES project."User"(user_id)
);

CREATE TABLE project.seller_admin (
	admin_id int4 NOT NULL,
	seller_id int4 NOT NULL,
	store_name varchar NOT NULL,
	"name" varchar NOT NULL,
	surname varchar NOT NULL,
	"e-mail" varchar NOT NULL,
	CONSTRAINT seller_admin_pk PRIMARY KEY (seller_id),
	CONSTRAINT seller_admin_fk FOREIGN KEY (admin_id) REFERENCES project."Admin"(admin_id),
	CONSTRAINT seller_admin_fk_1 FOREIGN KEY (seller_id) REFERENCES project."User"(user_id),
	CONSTRAINT seller_admin_fk_2 FOREIGN KEY (store_name) REFERENCES project.store(store_name)
);

CREATE TABLE project.distributor (
	distributor_id int4 NOT NULL,
	"name" varchar NOT NULL,
	surname varchar NOT NULL,
	contact_number int4 NOT NULL,
	CONSTRAINT distributor_pk PRIMARY KEY (distributor_id),
	CONSTRAINT distributor_fk FOREIGN KEY (distributor_id) REFERENCES project."User"(user_id)
);

CREATE TABLE project.client (
	client_id int4 NOT NULL,
	address varchar NOT NULL,
	"name" varchar NOT NULL,
	"e-mail" varchar NOT NULL,
	CONSTRAINT client_pk PRIMARY KEY (client_id),
	CONSTRAINT client_fk FOREIGN KEY (client_id) REFERENCES project."User"(user_id)
);

CREATE TABLE project.store (
	store_name varchar NOT NULL,
	locations varchar NOT NULL,
	contact varchar NOT NULL,
	CONSTRAINT store_pk PRIMARY KEY (store_name)
);

CREATE TABLE project.product (
	prod_id int4 NOT NULL,
	availability bool NOT NULL,
	"name" varchar NOT NULL,
	description varchar NOT NULL,
	price int4 NOT NULL,
	"type" varchar NOT NULL,
	quantity int4 NOT NULL,
	store_name varchar NOT NULL,
	CONSTRAINT product_pk PRIMARY KEY (prod_id),
	CONSTRAINT product_fk FOREIGN KEY (store_name) REFERENCES project.store(store_name)
);


CREATE TABLE project.delivery (
	delivery_id int4 NOT NULL,
	delivery_address varchar NOT NULL,
	"date" date NOT NULL,
	status varchar NOT NULL,
	client_id int4 NOT NULL,
	distributor_id int4 NOT NULL,
	CONSTRAINT delivery_pk PRIMARY KEY (delivery_id),
	CONSTRAINT delivery_fk FOREIGN KEY (distributor_id) REFERENCES project.distributor(distributor_id),
	CONSTRAINT delivery_fk_1 FOREIGN KEY (client_id) REFERENCES project.client(client_id)
);

CREATE TABLE project.price_history (
	price_history_id int4 NOT NULL,
	dates date NOT NULL,
	prod_id int4 NOT NULL,
	CONSTRAINT price_history_pk PRIMARY KEY (price_history_id),
	CONSTRAINT price_history_fk FOREIGN KEY (prod_id) REFERENCES project.product(prod_id)
);

CREATE TABLE project.wishlist (
	wishlist_id int4 NOT NULL,
	client_id int4 NOT NULL,
	CONSTRAINT wishlist_pk PRIMARY KEY (wishlist_id, client_id),
	CONSTRAINT wishlist_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id)
);

CREATE TABLE project.cart (
	cart_id int4 NOT NULL,
	client_id int4 NOT NULL,
	CONSTRAINT cart_pk PRIMARY KEY (cart_id),
	CONSTRAINT cart_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id)
);

CREATE TABLE project."order" (
	order_id int4 NOT NULL,
	order_status varchar NOT NULL,
	total_price int4 NOT NULL,
	client_id int4 NOT NULL,
	delivery_id int4 NOT NULL,
	CONSTRAINT order_pk PRIMARY KEY (order_id),
	CONSTRAINT order_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id),
	CONSTRAINT order_fk1 FOREIGN KEY (delivery_id) REFERENCES project.delivery(delivery_id)
);

CREATE TABLE project."Configuration" (
	config_id int4 NOT NULL,
	"name" varchar NOT NULL,
	price int4 NOT NULL,
	"type" varchar NOT NULL,
	CONSTRAINT configuration_pk PRIMARY KEY (config_id)
);

CREATE TABLE project.payment (
	payment_id int4 NOT NULL,
	creditcard_number int4 NOT NULL,
	order_id int4 NOT NULL,
	CONSTRAINT payment_pk PRIMARY KEY (payment_id),
	CONSTRAINT payment_fk FOREIGN KEY (order_id) REFERENCES project."order"(order_id)
);

CREATE TABLE project.view_product (
	client_id int4 NOT NULL,
	prod_id int4 NOT NULL,
	time_stamp varchar NOT NULL,
	CONSTRAINT view_product_pk PRIMARY KEY (prod_id, client_id),
	CONSTRAINT view_product_fk FOREIGN KEY (client_id) REFERENCES project.client(client_id),
	CONSTRAINT view_product_fk_1 FOREIGN KEY (prod_id) REFERENCES project.product(prod_id)
);

CREATE TABLE project.manages_configuration (
	config_id int4 NOT NULL,
	admin_id int4 NOT NULL,
	CONSTRAINT manages_configuration_pk PRIMARY KEY (config_id, admin_id),
	CONSTRAINT manages_configuration_fk FOREIGN KEY (admin_id) REFERENCES project."Admin"(admin_id),
	CONSTRAINT manages_configuration_fk_1 FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id)
);

CREATE TABLE project.product_to_wishlist (
	prod_id int4 NOT NULL,
	wishlist_id int4 NOT NULL,
	CONSTRAINT product_to_wishlist_pk PRIMARY KEY (prod_id, wishlist_id),
	CONSTRAINT product_to_wishlist_fk FOREIGN KEY (prod_id) REFERENCES project.product(prod_id),
	CONSTRAINT product_to_wishlist_fk_1 FOREIGN KEY (wishlist_id, wishlist_id) REFERENCES project.wishlist(wishlist_id, client_id)
);

CREATE TABLE project.config_to_wishlist (
	config_id int4 NOT NULL,
	wishlist_id int4 NOT NULL,
	CONSTRAINT config_to_wishlist_pk PRIMARY KEY (config_id, wishlist_id),
	CONSTRAINT config_to_wishlist_fk FOREIGN KEY (wishlist_id, wishlist_id) REFERENCES project.wishlist(wishlist_id, client_id),
	CONSTRAINT config_to_wishlist_fk_1 FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id)
);

CREATE TABLE project.updates (
	seller_id int4 NOT NULL,
	product_id int4 NOT NULL,
	time_stamp date NOT NULL,
	CONSTRAINT updates_pk PRIMARY KEY (seller_id, product_id),
	CONSTRAINT updates_fk FOREIGN KEY (seller_id) REFERENCES project.seller_admin(seller_id),
	CONSTRAINT updates_fk_1 FOREIGN KEY (product_id) REFERENCES project.product(prod_id)
);

CREATE TABLE project.config_contains_product (
	config_id int4 NOT NULL,
	prod_id int4 NOT NULL,
	num_products_config int4 NOT NULL,
	CONSTRAINT config_contains_product_pk PRIMARY KEY (config_id, prod_id),
	CONSTRAINT config_contains_product_fk FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id),
	CONSTRAINT config_contains_product_fk_1 FOREIGN KEY (prod_id) REFERENCES project.product(prod_id)
);

CREATE TABLE project.contains_config (
	config_id int4 NOT NULL,
	cart_id int4 NOT NULL,
	CONSTRAINT contains_config_pk PRIMARY KEY (cart_id, config_id),
	CONSTRAINT contains_config_fk FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id),
	CONSTRAINT contains_config_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
);

CREATE TABLE project.contains_product (
	prod_id int4 NOT NULL,
	cart_id int4 NOT NULL,
	CONSTRAINT contains_product_pk PRIMARY KEY (prod_id, cart_id),
	CONSTRAINT contains_product_fk FOREIGN KEY (prod_id) REFERENCES project.product(prod_id),
	CONSTRAINT contains_product_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
);



CREATE TABLE project.orders_configuration (
	config_id int4 NOT NULL,
	order_id int4 NOT NULL,
	CONSTRAINT orders_configuration_pk PRIMARY KEY (config_id, order_id),
	CONSTRAINT orders_configuration_fk FOREIGN KEY (config_id) REFERENCES project."Configuration"(config_id),
	CONSTRAINT orders_configuration_fk_1 FOREIGN KEY (order_id) REFERENCES project."order"(order_id)
);



CREATE TABLE project.checkout (
	cart_id int4 NOT NULL,
	order_id int4 NOT NULL,
	CONSTRAINT checkout_pk PRIMARY KEY (cart_id, order_id),
	CONSTRAINT checkout_fk FOREIGN KEY (order_id) REFERENCES project."order"(order_id),
	CONSTRAINT checkout_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
);

CREATE TABLE project.current_price (
	price_history_id int4 NOT NULL,
	order_id int4 NOT NULL,
	num_products int4 NOT NULL,
	CONSTRAINT current_price_pk PRIMARY KEY (order_id, price_history_id),
	CONSTRAINT current_price_fk FOREIGN KEY (price_history_id) REFERENCES project.price_history(price_history_id),
	CONSTRAINT current_price_fk_1 FOREIGN KEY (order_id) REFERENCES project."order"(order_id)
);
