create extension if not exists pg_libphonenumber;

	description text,
	phone packed_phone_number,
	tags citext[] not null default '{}',--debit, credit, cash only, etc

	terpenes text[] not null default '{}',--http://www.databasesoup.com/2015/01/tag-all-things-part-3.html I guess we'll deal with uniqueness and order in application layer or https://stackoverflow.com/a/22677978 ?? will need to do a good job normalizing though. how to search when order matters?? https://stackoverflow.com/questions/866465

	terpenes citext[],--http://www.databasesoup.com/2015/01/tag-all-things-part-3.html I guess we'll deal with uniqueness and order in application layer or https://stackoverflow.com/a/22677978 ?? will need to do a good job normalizing though. how to search when order matters?? https://stackoverflow.com/questions/866465

			case 'store':
/*
						{p.type}<br />
						{p.strain}<br />
						{p.name}<br />
						{p.brand}<br />
						thc range<br />
						cbd range<br />
						price range todo: flip and terpenes and other pic?<br />
*/
				let store = (await pool.query(`
					select
						store.*,
						(
							select json_agg(item) from (
								select distinct on(product.id)
									product.id,
									product.display_name as name,
									product.type,
									product.strain,
									product.display_brand as brand

			case 'store': {
				let s = Number(parameters.id);
				let data = 'invalid id';
				if(Number.isInteger(s)) {
					let response = (await pool.query(`
						select
							name,
							URL,
							address,
							address2,
							city,
							region,
							country,
							postal_code,
							coordinate,
							timezone,
							images,
							times
						from
							store
							left outer join (
								select
									store,
									json_agg(URL) as images

									product
									left outer join variant on product.id = variant.product
									inner join menu_item on menu_item.variant = variant.id
								where
									menu_item.store = store.id
							) as item
						) as products
					from
						store
					where
						store.id = $1
				`, [parameters.id])).rows[0];//need to join with menu_item -> variant -> product agg
				//make sure parameters.id is number
				if(store === undefined) {
					ws.send(JSON.stringify({
						response_ID: request_ID,
						data: 'store does not exist'
					}));
				} else {
					//ws.subscribe('store/' + parameters.store);
					ws.send(JSON.stringify({
						response_ID: request_ID,
						data: store
					}));

									store_image
								--order by rank
								group by
									store
							) as store_image on store_image.store = store.id
							left outer join (
								select
									store,
									json_agg(open) as times--need to agg the range
								from
									store_time
								group by
									store
							) as store_time on store_time.store = store.id
						where
							store.id = $1
					`, [s])).rows;
					if(response.length === 0) {
						data = 'store not found';
					} else {
						data = response[0];
						//ws.subscribe('store/' + parameters.store);
					}

				ws.send(JSON.stringify({
					response_ID: request_ID,
					data: data
				}));

			}

			case 'products'://in need of revamp 20200206 WHERE ARE THE TEREPENES?
				//for sort by thc, cbd, price, need to join with variant, menu items as well
				//simple case is return all though :)

			case 'products': {
				let s = Number(parameters && parameters.store);
				let params = [];
				let query = '';
				if(Number.isInteger(s)) {
					query += `where store = $${params.push(s)}`
				}

					response_ID: request_ID,//https://dba.stackexchange.com/a/159899

					response_ID: request_ID,//https://dba.stackexchange.com/a/159899 and https://stackoverflow.com/a/27626358

						select distinct on(product.id)

						select

							coalesce(json_agg(json_build_object('id', variant.id, 'portions', portions, 'quantity', quantity)) filter (where quantity is not null), '[]') as variants,

							terpenes,

							product_image.destination

							image,
							cbd,
							thc,
							min_price

							left outer join strain_family on (product.strain_family = strain_family.id)
							left outer join variant on (product.id = variant.product)
							left outer join product_image on (product.id = product_image.product)--and product-image.featured = true

							left outer join strain_family on strain_family.id = product.strain_family
							left outer join (
								select distinct on(product)
									product,
									destination as image
								from
									product_image
								--order by rank
							) as product_image on product_image.product = product.id
							inner join (
								select
									variant.product,
									range_merge(cbd) as cbd,
									range_merge(thc) as thc,
									min(price) as min_price
								from
									menu_item
									inner join variant on variant.id = menu_item.variant
								${query}
								group by
									variant.product
							) as product_agg on product_agg.product = product.id

						group by
							product.id,
							strain_family.id,
							product_image.id
						--order by
							--product_image.rank
					`)).rows//I guess it's necessary to group by strain_family.id as well because there's no way for postgres to know that there'll only be one row in the result per strain_family. ask dba

							--display_brand = $1
						--order by cbd, thc, min_price, etc. idk if ranges are sortable... looks like you can
					`, params)).rows

				break;

				break;}

							data: (await pool.query('select * from menu_item where store = $1', [parameters.store])).rows

							data: (await pool.query(
								`select
									product.id,
									type,
									producer,
									display_brand as brand,
									product.display_name as name,
									strain,
									strain_family.display_name as strain_family,
									product.description,
									concentration_unit,
									measurement_unit,
									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
											))) 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