quantity smallint not null check (quantity > 0),

	quantity smallint not null check (quantity > 0) default 1,

create or replace function valid_line_item_as_of_now() returns trigger as $$
declare
	proposed_gram_equivalency_line_item decimal;
	gram_equivalency_in_cart decimal;
	held integer;
	_stock integer;
begin
	if new.quantity < 1 then
		delete from line_item where line_item.cart_id = new.cart_id and line_item.menu_item_id = new.menu_item_id;
		delete from cart where cart_id = new.cart_id and (select count(*) from line_item where line_item.cart_id = new.cart_id) = 0;
		return null;
	else
		select new.quantity * case
			when type in ('oil', 'spray', 'capsule') then gram_equivalency
			else portions * variant.quantity / case
				when type in ('flower', 'preroll') then 1
				when type in ('cartridge', 'disposable') then .25
				else 15
			end
		end into strict proposed_gram_equivalency_line_item
		from menu_item inner join variant using (variant_id) inner join product using (product_id)
		where menu_item.menu_item_id = new.menu_item_id;
		select coalesce(sum(case
			when type in ('oil', 'spray', 'capsule') then gram_equivalency
			else portions * variant.quantity / case
				when type in ('flower', 'preroll') then 1
				when type in ('cartridge', 'disposable') then .25
				else 15
			end
		end * line_item.quantity), 0)
		into strict gram_equivalency_in_cart
		from line_item inner join menu_item using (menu_item_id) inner join variant using (variant_id) inner join product using (product_id)
		where line_item.cart_id = new.cart_id and line_item.menu_item_id != new.menu_item_id;
		select coalesce(sum(line_item.quantity), 0)
		into strict held
		from cart inner join line_item using (cart_id)
		where status <= 'ready' and cart.cart_id != new.cart_id and line_item.menu_item_id = new.menu_item_id;
		select stock
		into strict _stock
		from menu_item
		where menu_item.menu_item_id = new.menu_item_id;
		--raise exception 'Value: %, %, %, %', proposed_gram_equivalency_line_item, gram_equivalency_in_cart, held, stock;
		if proposed_gram_equivalency_line_item + gram_equivalency_in_cart > 30 then
			raise exception 'exceeded 30g limit';
		elsif _stock - held < new.quantity then
			raise exception 'out of stock';
		else
			return new;
		end if;
	end if;
end $$ language plpgsql volatile;
drop trigger if exists valid_line_item_as_of_now_trigger on line_item;
create trigger valid_line_item_as_of_now_trigger before insert or update on line_item for each row execute function valid_line_item_as_of_now();
create or replace function modifycart (
	_menu_item_id integer,
	qty smallint = 1,
	_user_id integer = null,
	cart_ids integer[] = null,
	out rtn integer
) as $$
declare
	tmp integer;
begin
	if _user_id is null and cart_ids is null then
		with ins1 as (--let's hope qty < 1 will revert the transaction and cart won't exist. 
			insert into cart (store_id) values ((
				select store_id
				from menu_item
				where menu_item_id = _menu_item_id
			)) returning cart_id
		)
		insert into line_item (cart_id, menu_item_id, quantity)
		select cart_id, _menu_item_id, qty from ins1
		returning cart_id into rtn;
	elsif _user_id is null then--implies cart_ids is not null
		insert into line_item (cart_id, menu_item_id, quantity)
		select cart_id, _menu_item_id, qty
		from cart inner join store using (store_id) inner join menu_item using (store_id)
		where
			cart_id = any(cart_ids) and
			user_id is null and
			status = 'active' and
			menu_item_id = _menu_item_id
		on conflict (cart_id, menu_item_id) do update
		set quantity = excluded.quantity
		where line_item.quantity != excluded.quantity;--dba.stackexchange.com/a/118214
		--todo: what should be returned?
	elsif cart_ids is null then--implies _user_id is not null
		insert into cart (user_id, store_id)
		select _user_id, store_id from menu_item where menu_item_id = _menu_item_id
		on conflict (user_id, store_id) where status <= 'ready' do nothing
		returning cart_id into tmp;
		if tmp is null then
			select cart_id
			from cart
			where
				user_id = _user_id and
				store_id = (select store_id from menu_item where menu_item_id = _menu_item_id) and
				status = 'active'
			into tmp;
		end if;
		if tmp is null then
			raise exception 'failed to add to cart. you already have an order in progress with this store.';
		else
			insert into line_item (cart_id, menu_item_id, quantity)
			values (tmp, _menu_item_id, qty)
			on conflict (cart_id, menu_item_id) do update
			set quantity = excluded.quantity
			where line_item.quantity != excluded.quantity;--dba.stackexchange.com/a/118214
			--todo: what should be returned?
		end if;
	else
		raise exception 'I dont know what 2 do poop';
	end if;
end $$ language plpgsql volatile;
create or replace function vuecart (
	_user_id integer = null,
	cart_ids integer[] = null
) returns table (
	store_id integer,
	store_name text,
	taxes json,
	line_items json
) as $$
	select
		store.store_id,
		name,
		(select coalesce(json_agg(json_build_object(
			'name', name,
			'rate', rate::text
		)), '[]') from store_tax where store_id = store.store_id),
		json_agg(json_strip_nulls(json_build_object(
			'product_id', product_id,
			'variant_id', variant_id,
			'menu_item', menu_item_id,
			'producer', producer,
			'brand', brand,
			'name', product.display_name,
			'type', product.type,
			'strain', strain.display_name,
			'portions', portions,
			'quantity', variant.quantity::text,
			'flavor', flavor,
			'gram_equivalency', gram_equivalency::text,
			'thc', thc,
			'cbd', cbd,
			'price', price::text,
			'howmany', line_item.quantity,
			'image', destination
		)))
	from
		cart
		inner join store using (store_id)
		inner join line_item using (cart_id)
		inner join menu_item using (menu_item_id)
		inner join variant using (variant_id)
		inner join product using (product_id)
		left outer join strain using (strain_id)
		left outer join (
			select distinct on(product_id)--https://dba.stackexchange.com/a/159899
				product_id,
				destination
			from
				product_image
			--order by rank
		) as product_image using (product_id)
	where
		(user_id = _user_id or _user_id is null) and
		(cart_id = any(cart_ids) or cart_ids is null) and
		status = 'active'
	group by
		cart_id,
		store.store_id
$$ language sql stable;

			case 'add2cart':
				//todo: timeout / decrementing stock(probably a cron job) ) idea: can only add to cart if stock minus sum of all line items menu items from carts that are less than or equal to ready. once beyond ready, actually decrement stock
				//or settimeout to delete. if server crashes, on next initialization check all cart age?
				if(isLoggedIn(ws)) {
					let cart = (await pool.query(`insert into cart (user_id, store_id) values ($1, (select store from menu_item where id = $2)) on conflict (user_id, store_id) where status <= 'ready' do nothing returning id`, [ws.user_ID, parameters.menu_item])).rows;
					if(cart.length === 0) {//https://stackoverflow.com/a/42217872/1201238
						let kart = (await pool.query(`select id from cart where user_id = $1 and store = (select store from menu_item where id = $2) and status is null`, [ws.user_ID, parameters.menu_item])).rows;
						if(kart.length === 0) {
							ws.send(JSON.stringify({
								response_ID: request_ID,
								data: 'failed to add to cart. you already have an order in progress with this store.'
							}));
							break;
						} else if(kart.length === 1) {
							cart = kart[0].id;
						} else {
							ws.send(JSON.stringify({
								response_ID: request_ID,
								data: 'kys: ' + kart.length
							}));
							break;
						}
					} else if(cart.length === 1) {
						cart = cart[0].id;
					} else {
						ws.send(JSON.stringify({
							response_ID: request_ID,
							data: 'how the fuck can this happen??: ' + cart.length
						}));
						break;

			case 'modifycart':
				if(Number.isInteger(Number(parameters.menu_item))) {
					let params = [];
					let qs = [`_menu_item_id => $${params.push(parameters.menu_item)}`];
					if(Number.isInteger(Number(parameters.qty))) {
						qs.push(`qty => $${params.push(parameters.qty)}`);

					//check qty here? and current limit of cart

					if(isLoggedIn(ws)) {
						qs.push(`_user_id => $${params.push(ws.user_ID)}`);
					} else if(Number.isInteger(Number(parameters.carts))) {
						qs.push(`_cart_ids => $${params.push(parameters.carts)}`);
					}
					let data;
					try {
						data = (await pool.query(`select modifycart(${qs.join(',')})`,
							params
						)).rows[0].modifycart;
					} catch(e) {
						data = e.message;
					}

						data: (await pool.query(`insert into line_item (cart, menu_item, quantity)
							values ($1, $2, 1)
							on conflict on constraint unique_line_item do update set quantity = line_item.quantity + excluded.quantity returning id`,
							[cart, parameters.menu_item]
						)).rows.length//should always be 1

						data

				} else {
					//todo: and not logged in(cart id as input and output. localstorage array of ids on clientside
					//maybe ws onclose, delete the cart? better hope their connection is good

				break;
			case 'removefromcart':
				//is this good enough check line_item belongs to user?
				await pool.query('delete from line_item using cart where line_item.cart = cart.id and cart.user_id = $1 and line_item.id = $2', [ws.user_ID, parameters.line_item]);
				ws.send(JSON.stringify({
					response_ID: request_ID
				}));

						data: (await pool.query(`select
								cart.id,
								store.name,
								sum(variant.gram_equivalency) as idk,
								json_agg(json_build_object(
									'id', line_item.id,
									'brand', product.brand,
									'name', product.display_name,
									'type', product.type,
									'portions', variant.portions,
									'quantity', variant.quantity,
									'cbd', menu_item.cbd,
									'thc', menu_item.thc,
									'price', menu_item.price,
									'fuck', line_item.quantity
								)) as line_items
							from
								cart
								inner join store on cart.store = store.id
								inner join line_item on line_item.cart = cart.id
								inner join menu_item on line_item.menu_item = menu_item.id
								inner join variant on menu_item.variant = variant.id
								inner join product on variant.product = product.id
							where
								user_id = $1 and
								status is null
							group by
								cart.id,
								store.id
							`, [ws.user_ID])
						).rows

						data: (await pool.query(`select * from vuecart(_user_id => $1)`, [ws.user_ID])).rows
					}));
				} else if(Array.isArray(parameters.carts) && parameters.carts.every(c => Number.isInteger(Number(c)))) {
					ws.send(JSON.stringify({
						response_ID: request_ID,
						data: (await pool.query(`select * from vuecart(_cart_ids => $1)`, [parameters.carts])).rows