create extension if not exists fuzzystrmatch;

$$ language sql;
create or replace function thc2tc(thc numrange, cbd numrange) returns numeric as $$
	select ((lower(thc) + upper(thc)) / 2) / (((lower(thc) + upper(thc)) / 2) + ((lower(cbd) + upper(cbd)) / 2))

	_type product_type = null

	_type product_type = null,
	_in_stock boolean = null

				store_id = _store_id or _store_id is null

				(store_id = _store_id or _store_id is null) and
				case
					when _in_stock then stock > 0
					when not _in_stock then stock = 0
					else true
				end

$$ language sql;
create or replace function similar_products(
	_product_id integer,
	_store_id integer = null,
	_in_stock boolean = null
) returns table(
	id integer,
	type product_type,
	producer text,
	brand text,
	name text,
	species species,
	strain text,
	terpenes citext[],
	image text,
	cbd numrange,
	thc numrange,
	min_price decimal(5, 2),
	quantity decimal
) as $$
	with p as (
		select
			strain_id,
			type,
			species,
			thc2tc(range_merge(thc), range_merge(cbd)) as thc2tc,
			terpenes
		from
			product
			inner join variant using (product_id)
			inner join menu_item using (variant_id)
		where product_id = _product_id
		group by product_id
	)
--future state: 	select * from get_products(_store_id => _store_id)
--where product_id != _product_id--what are performance implications of nested where? any chance this can be inlined with get_products where?
	select
		product_id,
		type,
		producer,
		brand,
		product.display_name,
		species,
		strain.display_name,
		terpenes,
		destination,
		cbd,
		thc,
		min,
		first
	from
		product
		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)
		inner join (--aggregate first, then join https://stackoverflow.com/a/27626358 I think this counts as "retrieve all or most"? obviously more filters maybe not, but let's optimize later
			select
				product_id,
				range_merge(cbd) as cbd,
				range_merge(thc) as thc,
				min(price),
				first(quantity)--this is used to convert mg / g to mg / piece for edibles
			from
				menu_item
				inner join variant using (variant_id)
			where
				store_id = _store_id or _store_id is null and
				case
					when _in_stock then stock > 0
					when not _in_stock then stock = 0
					else true
				end
			group by
				product_id
		) as product_agg using (product_id)
	where
		show = true and
		product_id != _product_id--this is the only thing stopping function reuse, and referencing columns not in select in the order by
	order by
		strain_id = (select strain_id from p) desc nulls last,
		type = (select type from p) desc,--should this be optional?
		species = (select species from p) desc,
		levenshtein(terpenes[1], (select terpenes[1] from p)) <= 2 desc nulls last,--temporary until similarity algorithm between two rank ordered lists is found
		levenshtein(terpenes[1], (select terpenes[2] from p)) <= 2 desc nulls last,
		levenshtein(terpenes[1], (select terpenes[3] from p)) <= 2 desc nulls last,
		round(@ thc2tc(thc, cbd) - (select thc2tc from p), 2) asc nulls last,
		round(@ ((lower(thc) + upper(thc)) / 2) - (select ((lower(thc) + upper(thc)) / 2) from p), -1) asc nulls last--this is wrong thc_mg, but temporary(take into account user pref for flower, recommend at variant level) but maybe doesn't matter since same type? need to fix if type becomes optional though
		--nice la
		--since thc_mg is last, don't need to round.
	limit 5

					}
					if(typeof parameters.in_stock === 'boolean') {
						qs += `_in_stock => $${params.push(parameters.in_stock)},`;

			case 'similar_products': {
				let params = [];
				let qs = '';
				if(parameters) {
					let s = Number(parameters.store);
					if(Number.isInteger(s)) {
						qs += `_store_id => $${params.push(s)},`;
					}
					if(typeof parameters.in_stock === 'boolean') {
						qs += `_in_stock => $${params.push(parameters.in_stock)},`;
					}
					let p = Number(parameters.product_id);
					if(Number.isInteger(p)) {
						qs += `_product_id => $${params.push(p)},`;
						ws.send(JSON.stringify({
							response_ID: request_ID,
							data: (await pool.query(`select * from similar_products(${qs.slice(0, -1)})`, params)).rows
						}));
						break;
					}
				}
				//if you get here, you fucked up.
				break;
			}