$$ language sql;
create or replace function get_product_dynamic (
	_variant_id integer
) returns table (
	cbd numrange,
	thc numrange,
	min_price decimal(5, 2),
	max_price decimal(5, 2),
	stores json
) as $$
	select
		range_merge(cbd),--todo: if min(range) === max(range), return a singular value. is this even possible?
		range_merge(thc),
		min(price),
		max(price),
		json_agg(json_build_object(
			'store', json_build_object(
				--distance
				--closed or open. if closed, when is it opening next?
				'id', store_id,
				'name', store.name,
				'image', (select store_image.url from store_image where store_image.store_id = store_id limit 1)--order by rank someday
			),
			'cbd', cbd,
			'thc', thc,
			'price', price::text,--otherwise 32.40 will become 32.4 ... fuck json
			'stock', stock,
			'url', store.url || path,
			'menu_item', menu_item_id
		) order by type)--display online stores last
	from
		menu_item
		left outer join store using (store_id)
	where
		variant_id = _variant_id

create or replace function get_menu_item (
	_variant_id integer,
	_store_id integer
) returns table (
	cbd numrange,
	thc numrange,
	price decimal(5, 2),
	stock smallint,
	path text,
	menu_item integer
) as $$
	select
		cbd,
		thc,
		price,
		stock,
		path,
		menu_item_id
	from menu_item
	where
		variant_id = _variant_id and
		store_id = _store_id
$$ language sql;
create or replace function menu_item_loader (
	_store_id integer
) returns table (
	id integer,
	type product_type,
	producer text,
	brand text,
	name text,
	species species,
	strain text,
	description text,
	terpenes citext[],
	variants json
) as $$
	select
		product_id,
		type,
		producer,
		brand,
		product.display_name,
		species,
		strain.display_name,
		description,
		terpenes,
		json_agg
	from
		product
		left outer join strain using (strain_id)
		left outer join (
			select
				product_id,
				json_agg(json_strip_nulls(json_build_object(
					'id', variant_id,
					'flavor', flavor,
					'gram_equivalency', gram_equivalency,
					'portions', portions,
					'quantity', quantity,
					'checked', price is not null,
					'menu_item', menu_item_id,
					'min_thc', lower(thc),
					'max_thc', upper(thc),
					'min_cbd', lower(cbd),
					'max_cbd', upper(cbd),
					'price', price,
					'stock', stock,
					'featured', featured
				)) order by portions, quantity)
			from
				variant
				left outer join (
					select
						menu_item_id,
						variant_id,
						cbd,
						thc,
						price,
						stock,
						featured
					from menu_item
					where store_id = _store_id
				) as menu_item_filtered using (variant_id)
			group by
				product_id
		) as variant_agg using (product_id)
$$ language sql;

					let query = '';

					let response;

						query += `and store = $${params.push(s)}`;

						response = (await pool.query(`select * from get_menu_item($1, $${params.push(s)})`, params)).rows;
					} else {
						response = (await pool.query(`select * from get_product_dynamic($1)`, params)).rows;

					//todo: if min(range) === max(range), return a singular value
					//distance
					//closed or open. if closed, when is it opening next?
					let response = (await pool.query(`
						select
							range_merge(cbd) as cbd,
							range_merge(thc) as thc,
							min(price) as price_min,
							max(price) as price_max,
							json_agg(json_build_object(
								'store', json_build_object(
									'id', store.id,
									'name', store.name,
									'image', (select store_image.url from store_image where store_image.store = store.id limit 1),--order by rank someday
									'online', URL is not null and address is null and address2 is null and city is null and postal_code is null and coordinate is null and timezone is null
								),
								'cbd', cbd,
								'thc', thc,
								'price', price,
								'stock', stock,
								'url', store.URL || path,
								'menu_item', menu_item.id
							) order by (store.address) asc) as stores
						from
							menu_item
							left outer join store on menu_item.store = store.id
						where
							variant = $1
							${query}
						`,
						params
					)).rows;

							data: (await pool.query(
								`select
									product.id,
									type,
									producer,
									brand,
									product.display_name as name,
									strain,
									strain_family.display_name as strain_family,
									product.description,
									terpenes,
									variant_agg.variants
								from
									product
									left outer join strain_family on strain_family.id = product.strain_family
									left outer join (
										select
											product,
											json_agg(json_strip_nulls(json_build_object(
													'id', variant.id,
													'flavor', variant.flavor,
													'gram_equivalency', variant.gram_equivalency,
													'portions', variant.portions,
													'quantity', variant.quantity,
													'checked', menu_item_filtered.price is not null,
													'menu_item', menu_item_filtered.id,
													'min_thc', lower(menu_item_filtered.thc),
													'max_thc', upper(menu_item_filtered.thc),
													'min_cbd', lower(menu_item_filtered.cbd),
													'max_cbd', upper(menu_item_filtered.cbd),
													'price', menu_item_filtered.price,
													'stock', menu_item_filtered.stock,
													'featured', menu_item_filtered.featured
											)) order by variant.portions nulls first, variant.quantity) as variants
										from
											variant
											left outer join (select id, variant, cbd, thc, price, stock, featured from menu_item where store = $1) as menu_item_filtered on variant.id = menu_item_filtered.variant
										group by
											product
									) as variant_agg on product.id = variant_agg.product`,
								[parameters.store]
							)).rows

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

							shit = (await pool.query({text: query.slice(0, -1) + ` on conflict on constraint unique_menu_item do update set

							shit = (await pool.query({text: query.slice(0, -1) + ` on conflict (variant_id, store_id) do update set

							(excluded.cbd, excluded.thc, excluded.price, excluded.stock, excluded.featured) returning id

							(excluded.cbd, excluded.thc, excluded.price, excluded.stock, excluded.featured) returning menu_item_id