create or replace function f_raise(_lvl text = 'exception', _msg text = 'Default error msg.') returns void as $$
begin
	case lower(_lvl)
		when 'exception' then raise exception '%', _msg;
		when 'warning'   then raise warning   '%', _msg;
		when 'notice'    then raise notice    '%', _msg;
		when 'debug'     then raise debug     '%', _msg;
		when 'log'       then raise log       '%', _msg;
		when 'info'      then raise info      '%', _msg;
		else raise exception 'f_raise(): unexpected raise-level: "%"', _lvl;
	end CASE;
END
$$ language plpgsql;
comment on function f_raise(text, text) is 'Raise error or given level with msg and context.
Call from inside another function instead of raising an error directly
  to get plpgsql to add CONTEXT (with line number) to error message.
$1 .. error level: EXCEPTION | WARNING | NOTICE | DEBUG | LOG | INFO
$2 .. error message';

	out rtn integer

	out cart_id integer

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

	with s as (
		select cart_id

			cart_id = any(cart_ids) and
			user_id is null and

			(cart_id = any(cart_ids) and user_id is null or
			user_id = _user_id) and

		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

		limit 1
	), i as (

		select _user_id, store_id from menu_item where menu_item_id = _menu_item_id

		--values (_user_id, (select store_id from menu_item where menu_item_id = _menu_item_id)) to make early exit and throw not null exception?? format error message in backend. are there side effects other than incremented cart_id serial?
		select _user_id, store_id from menu_item
		where not exists (select 1 from s) and menu_item_id = _menu_item_id

		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 (

		returning cart_id
	), c as (
		select * from s union select * from i
	), cc as (
		--condition is wrong! consider the case where a user adds n menu items from the same store simulatenously (n tabs press add2cart same time, or restore expired cart client side will send n modifycart, first cart gets created, but some others may not see the cart yet since parallel(can fix by having another endpoint that takes in multi, or waits for first to return and then execute others)) maybe solution is do update set status = 'active' where status = 'active'(empty update)??
		select case when not exists (select 1 from c) and _user_id is not null then f_raise('exception', 'failed to add to cart. you already have an order in progress with this store.') end
	)
	insert into line_item (cart_id, menu_item_id, quantity)
	select cart_id, _menu_item_id, qty from c full join cc on true--https://dba.stackexchange.com/a/69650 force evaluation of cte
	--1 * 0 => 1 row, fine since we're selecting what we want
	--0 * 1 => 1 row, fine since the exception will skip the insert anyways. before insert was skipped due to 0 rows, but this is equivalent? wait no... if invalid menu_item_id, insert will still run, but caught by valid_line_item_as_of_now(). I'd much rather make 0 * 0 though... don't really care about that?
	on conflict (cart_id, menu_item_id) do update
	set quantity = excluded.quantity
	where line_item.quantity != excluded.quantity--dba.stackexchange.com/a/118214
	returning case when cart_id is distinct from (select cart_id from s) and _user_id is null then cart_id end;
$$ language sql volatile;
create or replace function active_carts() returns table (
	user_id integer,
	cart_id integer,

		user_id,
		cart_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'

	where status = 'active'

$$ language sql stable;
create or replace function anonymous_carts(cart_ids integer[]) returns table (
	cart_id integer,
	store_id integer,
	store_name text,
	static_expiry timestamptz,
	dynamic_expiry timestamptz,
	taxes json,
	line_items json
) as $$
	select distinct on (store_id)
		cart_id,
		store_id,
		store_name,
		static_expiry,
		dynamic_expiry,
		taxes,
		line_items
	from active_carts()
	where cart_id = any(cart_ids)

create or replace function authenticated_carts(_user_id integer) returns table (
	store_id integer,
	store_name text,
	static_expiry timestamptz,
	dynamic_expiry timestamptz,
	taxes json,
	line_items json
) as $$
	select
		store_id,
		store_name,
		static_expiry,
		dynamic_expiry,
		taxes,
		line_items
	from active_carts()
	where user_id = _user_id
$$ language sql stable;
create or replace function user_reservations(_user_id integer) returns table (
	store_id integer,
	store_name text,
	statuses json,
	taxes json,
	line_items json
) as $$
	select--have to use first() because history tables do not have a primary key! https://www.postgresql.org/message-id/CAOw_LSEzXc_pFk7W5bTRQ%2B6qy0wG8g0yX2s%2Bi8TY1HF-LbZP3Q%40mail.gmail.com
		first(cart.store_id),
		first(store__as_of.name),
		first(cart.statuses),
		(select json_agg(json_build_object(
			'name', name,
			'rate', rate::text
		)) from store_tax__as_of(first(cart.system_time_start)) where store_id = first(cart.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__as_of.display_name,
			'type', product__as_of.type,
			'strain', strain__as_of.display_name,
			'portions', portions,
			'quantity', variant__as_of.quantity::text,
			'flavor', flavor,
			'gram_equivalency', gram_equivalency::text,
			'thc', thc,
			'cbd', cbd,
			'price', price::text,
			'howmany', line_item.quantity,
			'image', (
				select distinct on(product_id)--https://dba.stackexchange.com/a/159899
					destination
				from
					product_image__as_of(cart.system_time_start)
				where product_image__as_of.product_id = product__as_of.product_id
				--order by rank
			)
		)))
	from
(select
	cart_id,
	row_number() OVER (ORDER BY 	case
		when last(status order by system_time_start) = 'ready' then 2
		when last(status order by system_time_start) = 'placed' then 1
		else 0
	end desc,
	max(system_time_start) desc) AS rn,
	first(store_id) as store_id,
	min(system_time_start) as system_time_start,--system_time_start of 'placed' or 'paid' since that's what the customer has agreed to. right now is just 'placed'
	json_agg(json_build_object(
		'status', status,
		'as_of', system_time_start
	) order by
		system_time_start
	) as statuses
from cart_with_history
where
	user_id = _user_id and
	status >= 'placed'
group by cart_id
order by
	case
		when last(status order by system_time_start) = 'ready' then 2
		when last(status order by system_time_start) = 'placed' then 1
		else 0
	end desc,
	max(system_time_start) desc
) as cart
		inner join store__as_of(cart.system_time_start) using (store_id)
		inner join line_item using (cart_id)
		inner join menu_item__as_of(cart.system_time_start) using (menu_item_id)
		inner join variant__as_of(cart.system_time_start) using (variant_id)
		inner join product__as_of(cart.system_time_start) using (product_id)
		left outer join strain__as_of(cart.system_time_start) using (strain_id)
	group by
		cart_id,
		rn
	order by rn
$$ language sql stable;
create or replace function store_reservations(_store_id integer) returns table (
	user_id integer,
	user_email citext,
	statuses json,
	taxes json,
	line_items json
) as $$
	select--have to use first() because history tables do not have a primary key! https://www.postgresql.org/message-id/CAOw_LSEzXc_pFk7W5bTRQ%2B6qy0wG8g0yX2s%2Bi8TY1HF-LbZP3Q%40mail.gmail.com
		first(cart.user_id),
		first(usr__as_of.email),
		first(cart.statuses),
		(select json_agg(json_build_object(
			'name', name,
			'rate', rate::text
		)) from store_tax__as_of(first(cart.system_time_start)) where store_id = _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__as_of.display_name,
			'type', product__as_of.type,
			'strain', strain__as_of.display_name,
			'portions', portions,
			'quantity', variant__as_of.quantity::text,
			'flavor', flavor,
			'gram_equivalency', gram_equivalency::text,
			'thc', thc,
			'cbd', cbd,
			'price', price::text,
			'howmany', line_item.quantity,
			'image', (
				select distinct on(product_id)--https://dba.stackexchange.com/a/159899
					destination
				from
					product_image__as_of(cart.system_time_start)
				where product_image__as_of.product_id = product__as_of.product_id
				--order by rank
			)
		)))
	from
(select
	cart_id,
	row_number() OVER (ORDER BY 	case
		when last(status order by system_time_start) = 'placed' then 2
		when last(status order by system_time_start) = 'ready' then 1
		else 0
	end desc,
	max(system_time_start) desc) AS rn,
	first(user_id) as user_id,
	min(system_time_start) as system_time_start,--system_time_start of 'placed' or 'paid' since that's what the customer has agreed to. right now is just 'placed'
	json_agg(json_build_object(
		'status', status,
		'as_of', system_time_start
	) order by
		system_time_start
	) as statuses
from cart_with_history
where
	store_id = _store_id and
	status >= 'placed'
group by cart_id
order by--maybe this is unnecessary?
	case
		when last(status order by system_time_start) = 'placed' then 2
		when last(status order by system_time_start) = 'ready' then 1
		else 0
	end desc,
	max(system_time_start) desc
) as cart
		inner join usr__as_of(cart.system_time_start) using (user_id)
		inner join line_item using (cart_id)
		inner join menu_item__as_of(cart.system_time_start) using (menu_item_id)
		inner join variant__as_of(cart.system_time_start) using (variant_id)
		inner join product__as_of(cart.system_time_start) using (product_id)
		left outer join strain__as_of(cart.system_time_start) using (strain_id)
	group by
		cart_id,
		rn
	order by rn
$$ language sql stable;

					} else if(Number.isInteger(Number(parameters.carts))) {
						qs.push(`_cart_ids => $${params.push(parameters.carts)}`);

					} else if(Array.isArray(parameters.carts) && parameters.carts.every(c => Number.isInteger(Number(c)))) {
						qs.push(`cart_ids => $${params.push(parameters.carts)}`);

						data: (await pool.query(`select * from vuecart(_user_id => $1)`, [ws.user_ID])).rows

						data: (await pool.query(`select * from authenticated_carts($1)`, [ws.user_ID])).rows

				} else if(Array.isArray(parameters.carts) && parameters.carts.every(c => Number.isInteger(Number(c)))) {

				} else if(parameters && Array.isArray(parameters.carts) && parameters.carts.every(c => Number.isInteger(Number(c)))) {

						data: (await pool.query(`select * from vuecart(_cart_ids => $1)`, [parameters.carts])).rows

						data: (await pool.query(`select * from anonymous_carts($1)`, [parameters.carts])).rows

				//check if parameters.cart belongs to the currently logged in user
				//check if > 30g dried equivalency
				//check if everything is in stock
				if(isLoggedIn(ws)) {//https://build.affinity.co/understanding-tricky-joins-and-multi-table-updates-in-postgresql-using-boolean-algebra-7b329606ca45 as to why need to subselect vs from from_list

				if(isLoggedIn(ws)) {
					let data = await pool.query(`update cart set status = 'placed' where cart.user_id = $1 and cart.status = 'active' and cart.store_id = $2 returning system_time_start, cart_id`, [ws.user_ID, parameters.store]);

						data: (await pool.query(`update cart set status = 'placed'
		 					from (
		 						select
		 							sum(
		 								--coalesce(variant.portions, 1) * variant.quantity * line_item.quantity
		 								--need to take into account measurement unit as well... actually I forget
		 								--how can I show the results of this query?
		 								variant.quantity * line_item.quantity /
		 								case
		 									when product.type = 'flower' or product.type = 'preroll' then 30
		 								end
		 							) <= 1 as within_limits,
		 							bool_and(menu_item.stock >= line_item.quantity) as in_stock,
		 							cart.id
		 						from
		 							cart
		 							inner join line_item on cart.id = line_item.cart
		 							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
									cart.user_id = $1 and
									cart.status = 'active' and
									cart.id = $2
								group by
									cart.id
		 					) as fuck
							where
								fuck.within_limits = true and
								fuck.in_stock = true and
								fuck.id = cart.id
							returning cart.id
							`, [ws.user_ID, parameters.cart])).rows

						data: data.rowCount

				}

					if(data.rowCount === 1) {
						let email = pool.query(`select email from usr where user_id = $1`, [ws.user_ID]);
						let taxes = pool.query(`select name, rate::text from store_tax where store_id = $1`, [parameters.store]);
						let li = pool.query(`
							select distinct on (product_id)
								product_id,
								variant_id,
								menu_item_id as menu_item,
								producer,
								brand,
								product.display_name as name,
								type,
								strain.display_name as strain,
								portions,
								variant.quantity::text as quantity,
								flavor,
								gram_equivalency::text,
								thc,
								cbd,
								price::text,
								line_item.quantity as howmany,
								destination as image
							from
								line_item
								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 product_image using (product_id)
							where cart_id = $1
						`, [data.rows[0].cart_id])
						ws.publish('store/reservations/' + parameters.store, JSON.stringify({
							what: 'store/reservations/' + parameters.store,
							how: 'add',
							data: {
								user_id: ws.user_ID,
								user_email: (await email).rows[0].email,
								statuses: [{status: 'placed', as_of: data.rows[0].system_time_start}],
								taxes: (await taxes).rows,
								line_items: (await li).rows
							}
						}));
					}
					//send notification to store
				}// else if()//parameters.cart_id, paramaters.email, parameters.password
					//if login success
					//delete from cart where user_id = $1 and status = 'active' and store_id = $2
					//update cart set user_id = $1 where cart_id = $2//this will fail if there's another placed || ready with same store, which is ok. let them know
					//if login failure
					//do nothing with carts, ask to retry
				//else if () //parameters.cart_id, paramaters.email
/* but with empty password ""
update cart set status = 'placed', user_id = $2 where cart_id = $1
subquery doesn't work :(
					try {
						let newUser = await pool.query('insert into usr (email, passphrase_hash) values ($1, $2) returning user_id, email, type', [parameters.email, await argon2.hash(parameters.passphrase)]);//technically, we only need the id??
						ws.send(JSON.stringify({
							response_ID: request_ID
						}));
						ws.publish('user/s', JSON.stringify({
							what: "users",
							how: 'add',
							data: newUser.rows[0]
						}));
						//ws.publish('/users/' + id? dunno if needed, JSON.stringify(event.json id, email, add))
					} catch(e) {
						let error = 'user already exists'; do nothing with carts, ask to retry
						if(e.constraint !== 'usr_email_key') {
							console.error(e);
							error = 'error';
						}
						ws.send(JSON.stringify({
							response_ID: request_ID,
							data: error
						}));
					}
*/

			case 'list_unfullfilled_orders_store':

			case 'user_reservations':

					if(is(ws, 'god')) {

					if(is(ws, 'god') && parameters && Number.isInteger(Number(parameters.user_ID))) {
						//trust parameters.user_id
					} else {

							data: (await pool.query('select cart.id, cart.placed_at, json_agg(line_item) as line_items from cart inner join line_item on cart.id = line_item.cart where cart.store = $1 and cart.placed_at is not null and cart.ready_at is null group by cart.id order by cart.id desc', [Number(parameters.store)])).rows

							data: (await pool.query(`select * from user_reservations($1)`, [ws.user_ID])).rows

					}
				} else {
					ws.send(JSON.stringify({
						response_ID: request_ID,
						data: 'unauthorized'
					}));
				}
				break;
			case 'user_cancel_reservation':
				if(isLoggedIn(ws)) {
					if(is(ws, 'god') && parameters && Number.isInteger(Number(parameters.user_ID))) {
						//trust parameters.user_id

							data: 'forbidden'

							data: (await pool.query(`update cart set status = 'user_cancelled' where user_id = $1 and store_id = $2 and (status = 'placed' or status = 'ready')`, [ws.user_ID, parameters.store])).rowCount

						//send notification to store

			case 'list_orders':
				//only stuff that has placed_at

			case 'store_reservations':
				if(isLoggedIn(ws)) {
					if(is(ws, 'god') && parameters && Number.isInteger(Number(parameters.store))) {
						ws.subscribe('store/reservations/' + parameters.store)
						ws.send(JSON.stringify({
							response_ID: request_ID,
							data: (await pool.query(`select * from store_reservations($1)`, [parameters.store])).rows
						}));
					}
				}

			case 'fullfill':
				//update cart ready_at
				console.log(parameters);
				await pool.query('update cart set ready_at = now() where id = $1', [Number(parameters.cart)]);//todo, order not found, make sure this user is owner of the store, etc
				ws.send(JSON.stringify({
					response_ID: request_ID
				}));
				console.log('sending message to ' + 'order/' + parameters.cart)
				ws.publish('order/' + parameters.cart, JSON.stringify({
					what: 'order/' + parameters.cart,
					how: 'update',
					data: 'ready4pickup'
				}));

			case 'store_update_reservation':
				if(isLoggedIn(ws)) {
					if(is(ws, 'god') && parameters && Number.isInteger(Number(parameters.store)) && Number.isInteger(Number(parameters.user))) {
						let data = 'invalid update';
						switch(parameters.update) {
							case 'ready':
								data = (await pool.query(
									`update cart set status = 'ready' where store_id = $1 and user_id = $2 and status = 'placed'`,
									[parameters.store, parameters.user])).rowCount
								break;
							case 'paid'://todo: decrement stock. maybe trigger?
								data = (await pool.query(
									`update cart set status = 'paid' where store_id = $1 and user_id = $2 and status = 'ready'`,
									[parameters.store, parameters.user])).rowCount
								break;
							case 'cancel':
								data = (await pool.query(
									`update cart set status = 'store_cancelled' where store_id = $1 and user_id = $2 and status = 'placed'`,
									[parameters.store, parameters.user])).rowCount
								break;
							case 'noshow':
								data = (await pool.query(
									`update cart set status = 'no_show' where store_id = $1 and user_id = $2 and status = 'ready'`,
									[parameters.store, parameters.user])).rowCount
						}
						ws.send(JSON.stringify({
							response_ID: request_ID,
							data
						}));
					}
				}

			case 'unsubscribe':
				if(parameters && typeof parameters.what === 'string' && parameters.what.length) {
					ws.send(JSON.stringify({
						response_ID: request_ID,
						data: parameters.what === 'all' ? ws.unsubscribeAll() : ws.unsubscribe(parameters.what)
					}));
				}
				break;