CREATE SCHEMA project AUTHORIZATION db_202122z_va_prj_pcpicco_owner;


CREATE TABLE project.cart (
	cart_id int4 NOT NULL,
	cart_product_list varchar(150) NOT NULL,
	cart_config_list varchar(150) NOT NULL,
	CONSTRAINT cart_pkey PRIMARY KEY (cart_id)
);


CREATE TABLE project."configuration" (
	config_id int4 NOT NULL,
	config_name varchar(150) NOT NULL,
	config_type varchar(150) NOT NULL,
	config_price int4 NOT NULL,
	CONSTRAINT configuration_pkey PRIMARY KEY (config_id)
);

CREATE TABLE project.delivery (
	delivery_id int4 NOT NULL,
	address varchar NOT NULL,
	"Date" date NOT NULL,
	status varchar NOT NULL,
	CONSTRAINT delivery_pk PRIMARY KEY (delivery_id)
);


CREATE TABLE project."order" (
	"Order-id" int4 NOT NULL,
	total_price int4 NOT NULL,
	"Order-status" varchar NOT NULL,
	CONSTRAINT order_pk PRIMARY KEY ("Order-id")
);

CREATE TABLE project.payment (
	payment_id int4 NOT NULL,
	creditcard_number int4 NOT NULL,
	CONSTRAINT payment_pk PRIMARY KEY (payment_id)
);


CREATE TABLE project.price_history (
	price_history_id int4 NOT NULL,
	price_history_list varchar NOT NULL,
	price_date date NOT NULL,
	CONSTRAINT price_history_pkey PRIMARY KEY (price_history_id)
);



CREATE TABLE project.product (
	product_id serial NOT NULL,
	product_name varchar(150) NOT NULL,
	product_price int4 NOT NULL,
	product_type varchar(150) NOT NULL,
	product_description varchar(150) NOT NULL,
	product_availability bool NOT NULL,
	product_quantity int4 NULL,
	CONSTRAINT product_pkey PRIMARY KEY (product_id)
);


CREATE TABLE project.store (
	store_name varchar NOT NULL,
	store_locations varchar(150) NOT NULL,
	store_contact varchar(150) NOT NULL,
	CONSTRAINT store_pkey PRIMARY KEY (store_name)
);


CREATE TABLE project."user" (
	user_id int4 NOT NULL,
	user_password varchar(20) NOT NULL,
	CONSTRAINT user_pkey PRIMARY KEY (user_id)
);


CREATE TABLE project.wishlist (
	wishlist_id int4 NOT NULL,
	product_list varchar(150) NOT NULL,
	config_list varchar(150) NOT NULL,
	CONSTRAINT wishlist_pkey PRIMARY KEY (wishlist_id)
);


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


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


CREATE TABLE project.client (
	"Name" varchar(20) NOT NULL,
	address varchar(100) NOT NULL,
	"E-mail" varchar(20) NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT client_pk PRIMARY KEY (user_id),
	CONSTRAINT client_fk null
);


CREATE TABLE project.config_to_whishlist (
	config_id int4 NOT NULL,
	whishlist_id int4 NOT NULL,
	CONSTRAINT config_to_whishlist_pk null,
	CONSTRAINT config_to_whishlist_fk FOREIGN KEY (config_id) REFERENCES project.configuration(config_id),
	CONSTRAINT config_to_whishlist_fk_1 FOREIGN KEY (whishlist_id) REFERENCES project.wishlist(wishlist_id)
);


CREATE TABLE project."contains" (
	product_id int4 NOT NULL,
	config_id int4 NOT NULL,
	CONSTRAINT contains_pk null,
	CONSTRAINT contains_fk FOREIGN KEY (product_id) REFERENCES project.product(product_id),
	CONSTRAINT contains_fk_1 FOREIGN KEY (config_id) REFERENCES project.configuration(config_id)
);


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


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


CREATE TABLE project.current_price (
	order_id int4 NOT NULL,
	price_history_id 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")
);


CREATE TABLE project.delivers (
	user_id int4 NOT NULL,
	delivery_id int4 NOT NULL,
	CONSTRAINT delivers_pk null,
	CONSTRAINT delivers_fk null,
	CONSTRAINT delivers_fk_1 null
);

CREATE TABLE project.distributor (
	distributor_name varchar(13) NOT NULL,
	distributor_surname varchar(13) NOT NULL,
	distributor_contact varchar(9) NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT distributor_pk PRIMARY KEY (user_id),
	CONSTRAINT distributor_fk FOREIGN KEY (user_id) REFERENCES project."user"(user_id)
);

CREATE TABLE project.has_cart (
	cart_id int4 NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT has_cart_pk PRIMARY KEY (cart_id),
	CONSTRAINT has_cart_fk FOREIGN KEY (user_id) REFERENCES project.client(user_id),
	CONSTRAINT has_cart_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
);


CREATE TABLE project.manages_configuration (
	user_id int4 NOT NULL,
	config_id int4 NOT NULL,
	CONSTRAINT manages_configuration_pk PRIMARY KEY (user_id, config_id),
	CONSTRAINT manages_configuration_fk FOREIGN KEY (user_id) REFERENCES project.admin(user_id),
	CONSTRAINT manages_configuration_fk_1 FOREIGN KEY (config_id) REFERENCES project.configuration(config_id)
);


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


CREATE TABLE project.seller_admin (
	"Name" varchar(20) NOT NULL,
	surname varchar(20) NOT NULL,
	"E-mail" varchar(20) NOT NULL,
	user_id int4 NOT NULL,
	CONSTRAINT seller_admin_pk PRIMARY KEY (user_id),
	CONSTRAINT seller_admin_fk FOREIGN KEY (user_id) REFERENCES project."user"(user_id)
);


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


CREATE TABLE project.view_product (
	user_id int4 NOT NULL,
	product_id int4 NOT NULL,
	time_stamp date NOT NULL,
	CONSTRAINT view_product_pk PRIMARY KEY (user_id, product_id),
	CONSTRAINT view_product_fk FOREIGN KEY (user_id) REFERENCES project.client(user_id),
	CONSTRAINT view_product_fk_1 FOREIGN KEY (product_id) REFERENCES project.product(product_id)
);


CREATE TABLE project.works_for (
	user_id int4 NOT NULL,
	store_name varchar NOT NULL,
	CONSTRAINT works_for_pk PRIMARY KEY (user_id, store_name),
	CONSTRAINT works_for_fk FOREIGN KEY (store_name) REFERENCES project.store(store_name),
	CONSTRAINT works_for_fk_1 FOREIGN KEY (user_id) REFERENCES project.seller_admin(user_id)
);
