use webs;
drop function if exists cart;
drop function if exists add_to_cart;

delimiter $$
create function cart(user_id int) -- get current order for user_id (cart order id)
returns int
begin
	set @order_id = (select id from `order` where status = 1 and user = user_id);
	if @order_id is not null then
		return @order_id;
	end if;
	insert into `order` (`user`) values (user_id);
	set @order_id = (select id from `order` where status = 1 and user = user_id);
	return @order_id;
end$$

create function add_to_cart(product_id int, user_id int)
returns boolean
begin
	set @orderproduct_id = (select id from orderproduct where product = product_id and `order` = cart(user_id));
	if @orderproduct_id is not null then
		update orderproduct set count = count + 1 where id = @orderproduct_id;
		return true;
	end if;
	insert into orderproduct (`product`, `count`, `order`) values (product_id, 1, cart(user_id));
	return false;
end$$

create function cart_sum(order_id int) -- get cart sum for order
returns int
begin
	return (
		with precalc
		as (
			select
				product.price,
				orderproduct.count,
				promotion.count_buff,
				promotion.price_buff,
				floor(orderproduct.count / promotion.count_buff) * promotion.count_buff as qualify_count,
				mod(orderproduct.count, promotion.count_buff) as remainder_count
			from orderproduct
			join product on product.id = orderproduct.product
			join promotion on promotion.product = product.id
			where `order` = order_id
		)
		select sum(price * qualify_count * price_buff + price * remainder_count)
		from precalc
	);
end$$

delimiter ;