--https://stackoverflow.com/q/32582600/
\set name :name
select case
	when :'name' = ':name' then 'lobomj_development'
	else :'name'
end as "name" \gset
drop database :name;
create database :name;
\c :name
begin;
create extension if not exists citext;
create extension if not exists btree_gist;--used for periods and exclude constraint for minute range and store
create extension if not exists periods cascade;
create extension if not exists pg_libphonenumber;
create extension if not exists first_last_agg;
create extension if not exists fuzzystrmatch;
create extension if not exists pg_trgm;
create extension if not exists range_agg;
create aggregate range_merge(anyrange)
(
	sfunc = range_merge,
	stype = anyrange
);
--some day https://dba.stackexchange.com/a/101010 or https://github.com/pjungwir/range_agg or https://schinckel.net/2019/07/12/merging-adjacent-ranges-in-postgres/
--not as useful: https://wiki.postgresql.org/wiki/Range_aggregation , https://stackoverflow.com/q/26329513 , https://www.postgresql.org/message-id/flat/CAMp0ubfwAFFW3O_NgKqpRPmm56M4weTEXjprb2gP_NrDaEC4Eg%40mail.gmail.com , https://www.simononsoftware.com/how-to-calculate-length-of-overlapping-ranges-in-postgres/
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';
create or replace function minute_in_range_week(int4range[]) returns boolean as $$
	select every(wow is not null and wow <@ int4range(0, 10080)) from unnest($1) as t(wow);
$$ language sql immutable strict;
create table store_temp (
	joined_name citext,
	address citext,
	address2 citext,
	city citext,
	region citext,
	country citext,
	postal_code citext
);
create table store (
	store_id serial primary key,
	featured boolean default false not null,--rank someday(null means unranked): https://stackoverflow.com/a/49956113 Store bidding here
	joined_name citext not null,
	name text not null,
	url text,
	address citext,
	address2 citext,
	city citext,
	region citext not null,
	country citext not null,
	postal_code citext,
	longitude decimal(10, 7) check (longitude > -180 and longitude <= 180),
	latitude decimal(9, 7) check (latitude between -90 and 90),
	osrm_hint_foot text,
	osrm_hint_car text,
	timezone text,
	description text,
	phone packed_phone_number,--https://dba.stackexchange.com/a/164797
	extension citext,
	tags citext[] not null default '{}',--debit, credit, cash only, etc
	partner boolean not null default true,
	delivery boolean not null default true,
	pickup boolean not null default true,
	prepayment boolean not null default true,--right now, this means the dumb af "give me credit card number" over phone, or etransfer, but eventually will be moneris
	open int4range[] check (array_ndims(open) = 1 and minute_in_range_week(open)),
	check (
		address is null and address2 is null and city is null and postal_code is null and longitude is null and latitude is null or
		address is not null and address2 is not null and city is not null and postal_code is not null and longitude is not null and latitude is not null and (country = 'canada' and postal_code ~* '^([a-z]\d){3}$' or country != 'canada')
	)
);
create unique index upi_store_3 on store (joined_name, region, country) where address is null and address2 is null and city is null and postal_code is null;
create unique index upi_store_7 on store (joined_name, address, address2, city, region, country, postal_code) where address is not null and address2 is not null and city is not null and postal_code is not null;
create or replace function s(anyarray) returns anyarray as $$
	select array_agg(distinct n order by n) from unnest($1) as t(n);
$$ language sql immutable;
create type product_type as enum ('flower', 'preroll', 'cartridge', 'disposable', 'oil', 'spray', 'capsule', 'tea', 'rtd', 'powder', 'liquid', 'shot', 'gummy', 'mint', 'chocolate', 'baked');
create table store_tax (
	store_tax_id serial primary key,
	store_id integer not null references store on delete cascade,
	name text not null,
	rate decimal not null check (rate > 0 and rate < 1),
	types product_type[] check (cardinality(types) > 0 and cardinality(types) < array_length(enum_range(null::product_type), 1) and array_ndims(types) = 1)
);
create unique index upi_store_tax_2 on store_tax (store_id, name) where types is null;
create unique index upi_store_tax_3 on store_tax (store_id, name, s(types)) where types is not null;
create or replace function remove_duplicates_in_array() returns trigger as $$ begin
	if new.types is not null then
		new.types = s(new.types);
		if array_length(new.types, 1) = array_length(enum_range(null::product_type), 1) then
			new.types = null;
		end if;
	end if;
	return new;
end $$ language plpgsql volatile;
--drop trigger if exists remove_duplicates_in_array_trigger on store_tax;
create trigger remove_duplicates_in_array_trigger before insert or update on store_tax for each row execute function remove_duplicates_in_array();
create table store_open_exception (
	store_open_exception_id serial primary key,
	store_id integer not null references store on delete cascade,
	during tsrange not null,
	open int4range[] check (array_ndims(open) = 1 and minute_in_range_week(open))
);
create table store_image (
	store_image_id serial primary key,
	store_id integer not null references store on delete cascade,
	url text not null,
	featured boolean default false not null,--rank someday(null means unranked): https://stackoverflow.com/a/49956113
	description text,
	unique(store_id, url)
);
create type species as enum('indica', 'sativa', 'hybrid');
create table strain (
	strain_id serial primary key,
	display_name text not null unique,
	joined_name citext not null unique,--() in name parse from scrape
	species species not null
);
create table product (
	product_id serial primary key,
	producer text,--null means unknown
	brand text not null,--'' means producer is the brand
	check (brand != '' or producer is not null),
	display_name text not null,
	joined_name citext not null,
	type product_type not null,
	species species,
	blend boolean not null,
	strain_id integer references strain on delete restrict,
	check (
		blend and species is not null and strain_id is null or
		not blend and (species is null) != (strain_id is null)
	),
	description text,
	show boolean default false not null,
	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
	featured boolean default false not null,--rank someday(null means unranked): https://stackoverflow.com/a/49956113 LP bidding here
	dosage decimal check (dosage > 0)--dosist 2.25mg, if a spray explicitly states not 0.1ml per spray, if a all in one vape says how many pulls, dose size should be consistent across variants, so computed value() can be stored up here, etc
);
create unique index upi_product_3 on product (brand, joined_name, type) where producer is null;
create unique index upi_product_4 on product (producer, brand, joined_name, type) where producer is not null;
create index product_terpenes on product using gin (terpenes);
create table product_image (
	product_image_id serial primary key,
	product_id integer not null references product on delete cascade,
	source text,--nullable, human upload
	destination text not null,--should not be citext because full path
	featured boolean default false not null,--rank someday(null means unranked): https://stackoverflow.com/a/49956113
	description text,
	x smallint,
	y smallint,
	unique(product_id, destination)
);
create table variant (
	variant_id serial primary key,
	product_id integer not null references product on delete cascade,
	portions smallint not null default 1 check (portions > 0),
	quantity decimal check (quantity > 0),
	flavor citext,
	gram_equivalency decimal
);
create unique index upi_variant_2 on variant (product_id, portions) where quantity is null and flavor is null;
create unique index upi_variant_3_portions on variant (product_id, portions, quantity) where quantity is not null and flavor is null;
create unique index upi_variant_3_flavor on variant (product_id, portions, flavor) where quantity is null and flavor is not null;
create unique index upi_variant_4 on variant (product_id, portions, quantity, flavor) where quantity is not null and flavor is not null;
create table variant_identifier (
	variant_identifier_id serial primary key,
	variant_id integer not null references variant on delete cascade,
	gtin text not null,
	unique(variant_id, gtin)
);
create table menu_item (
	menu_item_id serial primary key,
	variant_id integer not null references variant on delete cascade,
	store_id integer not null references store on delete cascade,
	cbd numrange not null check (lower(cbd) >= 0),
	thc numrange not null check (lower(thc) >= 0),
	price decimal(5, 2) not null check (price >= 0),
	stock integer not null check (stock >= 0),
	featured boolean default false not null,--rank someday(null means unranked): https://stackoverflow.com/a/49956113
	path text,--online stores have stupid ass numbers so initially, use for compute, but once a product(if viewing product) / variant(if viewing a variant of a product) has more than one menu_item, disable 
	unique(variant_id, store_id),
	check (lower(cbd) + upper(cbd) > 0 or lower(thc) + upper(thc) > 0)
);
--thc
create type comt as enum ('inconclusive', 'Val/Val', 'Val/Met', 'Met/Met');
create type akt1 as enum ('inconclusive', 'T/T', 'C/T', 'C/C');
create type cyp2c9 as enum ('inconclusive', '*1/*1', '*1/*3', '*3/*3');
--cbd(may be collapse - 10 total states)
create type cyp2c19_2 as enum ('inconclusive', '*1/*1', '*1/*2', '*2/*2');
create type cyp2c19_3 as enum ('inconclusive', '*1/*1', '*1/*3', '*3/*3');
create type cyp2c19_17 as enum ('inconclusive', '*1/*1', '*1/*17', '*17/*17');
create table usr (--only because user is a reserved keyword
	user_id serial primary key,
	email citext not null unique,
	passphrase_hash text not null,
	--implement deserialized argon2 someday
	--variant
	--version
	--memory
	--iterations
	--parallelism smallint
	--salt bytea
	--hash bytea
	token_hash bytea,
	token_expiry timestamptz,
	god boolean not null default false,
	otp_hash bytea,
	otp_expiry timestamptz
);--not done. more research needed on best practices, sign in count, IP address, etc. survey?
create table user_store (
	user_store_id serial primary key,
	user_id integer references usr on delete cascade,
	store_id integer references store on delete cascade,
	unique(user_id, store_id),
	owner boolean not null--otherwise just worker
);
create table user_address (
	user_store_id serial primary key,
	user_id integer references usr on delete cascade,
	address citext not null,
	address2 citext not null,
	city citext not null,
	region citext not null,
	country citext not null,
	postal_code citext not null,
	longitude decimal not null,
	latitude decimal not null,
	osrm_hint_foot text not null,
	osrm_hint_car text not null,
	unique(user_id, address, city, region, country, postal_code)
);
create type status as enum ('active', 'placed', 'ready', 'paid', 'user_cancelled', 'store_cancelled', 'no_show');
create table cart (
	cart_id serial primary key,
	user_id integer references usr on delete cascade,
	store_id integer not null references store on delete cascade,
	status status not null default 'active',
	story text
);
create unique index upi_cart on cart (user_id, store_id) where status = 'active';
create table line_item (
	line_item_id serial primary key,
	cart_id integer not null references cart on delete cascade,
	menu_item_id integer not null references menu_item on delete cascade,
	quantity smallint not null check (quantity > 0) default 1,
	unique(cart_id, menu_item_id)
);
--todo: store reviews, product reviews, notifications, watched products, mixology, full text search, discounts
select (
	periods.add_system_time_period('store'),
	periods.add_system_versioning('store'),
	periods.add_system_time_period('store_tax'),
	periods.add_system_versioning('store_tax'),
	periods.add_system_time_period('store_open_exception'),
	periods.add_system_versioning('store_open_exception'),
	periods.add_system_time_period('store_image'),
	periods.add_system_versioning('store_image'),
	periods.add_system_time_period('strain'),
	periods.add_system_versioning('strain'),
	periods.add_system_time_period('product'),
	periods.add_system_versioning('product'),
	periods.add_system_time_period('product_image'),
	periods.add_system_versioning('product_image'),
	periods.add_system_time_period('variant'),
	periods.add_system_versioning('variant'),
	periods.add_system_time_period('variant_identifier'),
	periods.add_system_versioning('variant_identifier'),
	periods.add_system_time_period('menu_item'),
	periods.add_system_versioning('menu_item'),
	periods.add_system_time_period('usr', excluded_column_names => ARRAY['token_hash', 'token_expiry']),
	periods.add_system_versioning('usr'),
	periods.add_system_time_period('user_store'),
	periods.add_system_versioning('user_store'),
	periods.add_system_time_period('user_address'),
	periods.add_system_versioning('user_address'),
	periods.add_system_time_period('cart'),
	periods.add_system_versioning('cart'),
	periods.add_system_time_period('line_item'),
	periods.add_system_versioning('line_item')
);--brackets are to make it spit out only one column
insert into strain values
	(default, 'Original Glue', 'glueoriginal', 'sativa'),
	(default, 'AK-47', 'ak47', 'sativa'),
	(default, 'Platinum GSC', 'gscplatinum', 'sativa'),
	(default, 'LSD', 'lsd', 'sativa'),
	(default, 'Bruce Banner', 'bannerbruce', 'sativa'),
	(default, 'Trainwreck', 'trainwreck', 'sativa'),
	(default, 'Sherbert', 'sherbert', 'sativa'),
	(default, 'MasterKush', 'masterkush', 'sativa'),
	(default, 'Strawberry Cough', 'coughstrawberry', 'sativa'),
	(default, 'Space Queen', 'queenspace', 'sativa'),
	(default, 'J1', 'j1', 'sativa'),
	(default, 'Snowcap', 'snowcap', 'sativa'),
	(default, 'Dutch Hawaiian', 'dutchhawaiian', 'sativa'),
	(default, 'Canna-Tsu', 'cannatsu', 'sativa'),
	(default, 'Galactic Jack', 'galacticjack', 'sativa'),
	(default, 'Durban Poison', 'durbanpoison', 'sativa'),
	(default, 'Golden Goat', 'goatgolden', 'sativa'),
	(default, 'Harlequin', 'harlequin', 'sativa'),
	(default, 'Lamb''s Bread', 'breadlambs', 'sativa'),
	(default, 'Cinderella 99', '99cinderella', 'sativa'),
	(default, 'Tahoe OG Kush', 'kushogtahoe', 'hybrid'),
	(default, 'Lavender', 'lavender', 'indica'),
	(default, '9 Pound Hammer', '9hammerpound', 'indica'),
	(default, 'Blackberry Kush', 'blackberrykush', 'indica'),
	(default, 'Do-Si-Dos', 'dosidos', 'indica'),
	(default, 'Remedy', 'remedy', 'indica'),
	(default, 'God''s Gift', 'giftgods', 'indica'),
	(default, 'Cotton candy Kush', 'candycottonkush', 'hybrid'),
	(default, 'Vanilla Kush', 'kushvanilla', 'indica'),
	(default, 'Pineapple Express', 'expresspineapple', 'hybrid'),
	(default, 'Lemon Haze', 'hazelemon', 'sativa'),
	(default, 'Jillybean', 'jillybean', 'hybrid'),
	(default, 'ACDC', 'acdc', 'sativa'),
	(default, 'Flo', 'flo', 'hybrid'),
	(default, 'Cheese Quake', 'cheesequake', 'hybrid'),
	(default, 'Blue Widow', 'bluewidow', 'hybrid'),
	(default, 'Harle-Tsu', 'harletsu', 'hybrid'),
	(default, 'Plushberry', 'plushberry', 'indica'),
	(default, 'CBD Critical Mass', 'cbdcriticalmass', 'indica'),
	(default, 'Sweet and Sour Widow', 'andsoursweetwidow', 'indica'),
	(default, 'Sherbet', 'sherbet', 'hybrid'),
	(default, 'G13', 'g13', 'indica'),
	(default, 'Animal Cookies', 'animalcookies', 'hybrid'),
	(default, 'Sour Diesel', 'dieselsour', 'sativa'),
	(default, 'Super Lemon Haze', 'hazelemonsuper', 'sativa'),
	(default, 'Super Silver Haze', 'hazesilversuper', 'sativa'),
	(default, 'Amnesia Haze', 'amnesiahaze', 'sativa'),
	(default, 'Tangerine Dream', 'dreamtangerine', 'sativa'),
	(default, 'White Widow', 'whitewidow', 'hybrid'),
	(default, 'Blueberry', 'blueberry', 'indica'),
	(default, 'Candyland', 'candyland', 'sativa'),
	(default, 'Cannatonic', 'cannatonic', 'sativa'),
	(default, 'Kali Mist', 'kalimist', 'sativa'),
	(default, 'Liberty Haze', 'hazeliberty', 'sativa'),
	(default, 'Candy Jack', 'candyjack', 'sativa'),
	(default, 'MediHaze', 'medihaze', 'sativa'),
	(default, 'Green Crack', 'crackgreen', 'sativa'),
	(default, 'Jack Herer', 'hererjack', 'sativa'),
	(default, 'Ghost Train Haze', 'ghosthazetrain', 'sativa'),
	(default, 'Northern Lights', 'lightsnorthern', 'indica'),
	(default, 'Blueberry Kush', 'blueberrykush', 'indica'),
	(default, 'Granddaddy Purple', 'granddaddypurple', 'indica'),
	(default, 'Bubba Kush', 'bubbakush', 'indica'),
	(default, 'Chemdog', 'chemdog', 'hybrid'),
	(default, 'Maui Wowie', 'mauiwowie', 'sativa'),
	(default, 'Island Sweet Skunk', 'islandskunksweet', 'sativa'),
	(default, 'Critical Mass', 'criticalmass', 'indica'),
	(default, 'Pennywise', 'pennywise', 'indica'),
	(default, 'Kosher Kush', 'kosherkush', 'indica'),
	(default, 'Sensi Star', 'sensistar', 'indica'),
	(default, 'Shishkaberry', 'shishkaberry', 'indica'),
	(default, 'Pink Kush', 'kushpink', 'indica'),
	(default, 'Wappa', 'wappa', 'hybrid'),
	(default, 'Mango Haze', 'hazemango', 'sativa'),
	(default, 'Jean Guy', 'guyjean', 'hybrid'),
	(default, 'Nordle', 'nordle', 'indica'),
	(default, 'Sour Kush', 'kushsour', 'hybrid'),
	(default, 'Cold Creek Kush', 'coldcreekkush', 'hybrid'),
	(default, 'Delahaze', 'delahaze', 'sativa'),
	(default, 'Warlock', 'warlock', 'hybrid'),
	(default, 'OG Kush', 'kushog', 'hybrid'),
	(default, 'Critical Kush', 'criticalkush', 'indica'),
	(default, 'Hash Plant', 'hashplant', 'indica'),
	(default, 'SFV OG', 'ogsfv', 'hybrid'),
	(default, 'Blue Dream', 'bluedream', 'hybrid'),
	(default, 'MK Ultra', 'mkultra', 'sativa'),
	(default, 'Ultra Sour', 'sourultra', 'hybrid'),
	(default, 'UK Cheese', 'cheeseuk', 'hybrid'),
	(default, 'White Walker Kush', 'kushwalkerwhite', 'hybrid'),
	(default, 'Star Killer', 'killerstar', 'indica'),
	(default, 'Sour OG', 'ogsour', 'hybrid'),
	(default, 'Headstash', 'headstash', 'indica'),
	(default, 'Frost Monster', 'frostmonster', 'hybrid'),
	(default, 'Afghani', 'afghani', 'indica'),
	(default, 'Alien Dawg', 'aliendawg', 'indica'),
	(default, 'All Kush', 'allkush', 'indica'),
	(default, 'Banana Split', 'bananasplit', 'hybrid'),
	(default, 'Blue Haze', 'bluehaze', 'hybrid'),
	(default, 'Bubba Erkle Cookies', 'bubbacookieserkle', 'hybrid'),
	(default, 'Buddha Haze', 'buddhahaze', 'sativa'),
	(default, 'Buddhas Sister', 'buddhassister', 'indica'),
	(default, 'California Orange', 'californiaorange', 'hybrid'),
	(default, 'God Bud', 'budgod', 'indica'),
	(default, 'CBD Shark', 'cbdshark', 'indica'),
	(default, 'Shark Shock', 'sharkshock', 'indica'),
	(default, 'Skunk Haze', 'hazeskunk', 'hybrid'),
	(default, 'Pakistani Chitral Kush', 'chitralkushpakistani', 'indica'),
	(default, 'Chocolate Fondue', 'chocolatefondue', 'sativa'),
	(default, 'Chocolate Thai', 'chocolatethai', 'sativa'),
	(default, 'City Lights', 'citylights', 'hybrid'),
	(default, 'Dancehall', 'dancehall', 'hybrid'),
	(default, 'Durga Mata II', '2durgamata', 'hybrid'),
	(default, 'Durga Mata', 'durgamata', 'indica'),
	(default, 'Gold Kush', 'goldkush', 'indica'),
	(default, 'Great White Shark', 'greatsharkwhite', 'sativa'),
	(default, 'Ice Cream', 'creamice', 'indica'),
	(default, 'Ken''s Kush', 'kenskush', 'hybrid'),
	(default, 'LA Confidential', 'confidentialla', 'indica'),
	(default, 'Lemon Skunk', 'lemonskunk', 'hybrid'),
	(default, 'Mongolian', 'mongolian', 'indica'),
	(default, 'Ocean View', 'oceanview', 'sativa'),
	(default, 'Quantum Kush', 'kushquantum', 'sativa'),
	(default, 'Royal Purple Kush', 'kushpurpleroyal', 'indica'),
	(default, 'Spoetnik #1', '1spoetnik', 'indica'),
	(default, 'Strawberry Banana', 'bananastrawberry', 'hybrid'),
	(default, 'Super Skunk', 'skunksuper', 'indica'),
	(default, 'Treasure Island', 'islandtreasure', 'hybrid'),
	(default, 'Wabanaki', 'wabanaki', 'sativa'),
	(default, 'Royal Highness', 'highnessroyal', 'hybrid'),
	(default, 'Rockstar', 'rockstar', 'indica'),
	(default, 'Brian Berry Citrus Blend', 'berryblendbriancitrus', 'hybrid'),
	(default, 'brian berry cough', 'berrybriancough', 'hybrid'),
	(default, 'Sour Tangie', 'sourtangie', 'sativa'),
	(default, 'Hindu Kush', 'hindukush', 'indica'),
	(default, 'Larry OG', 'larryog', 'hybrid'),
	(default, 'Conspiracy Kush', 'conspiracykush', 'indica'),
	(default, 'NYC Diesel', 'dieselnyc', 'sativa'),
	(default, 'Texada Timewarp', 'texadatimewarp', 'sativa'),
	(default, 'Lemon Thai', 'lemonthai', 'sativa'),
	(default, 'Atomical Haze', 'atomicalhaze', 'sativa'),
	(default, 'Belladonna', 'belladonna', 'hybrid'),
	(default, 'Purple Kush', 'kushpurple', 'indica'),
	(default, 'CBD Critical Cure', 'cbdcriticalcure', 'indica'),
	(default, 'Headband', 'headband', 'hybrid'),
	(default, 'White Russian', 'russianwhite', 'hybrid'),
	(default, 'Strawberry Ice', 'icestrawberry', 'sativa'),
	(default, 'Acadia', 'acadia', 'sativa'),
	(default, 'Sunset Haze', 'hazesunset', 'hybrid'),
	(default, 'Nana''s Fix', 'fixnanas', 'hybrid'),
	(default, 'Critical Plus', 'criticalplus', 'hybrid'),
	(default, 'Mango', 'mango', 'indica'),
	(default, 'GSC', 'gsc', 'indica'),
	(default, 'Josh D OG', 'djoshog', 'indica'),
	(default, 'Orange Cookies', 'cookiesorange', 'hybrid'),
	(default, 'Raskal OG', 'ograskal', 'hybrid'),
	(default, 'Jack Haze', 'hazejack', 'sativa'),
	(default, 'White Rhino', 'rhinowhite', 'indica'),
	(default, 'Intergalactic', 'intergalactic', 'hybrid'),
	(default, 'White Castle', 'castlewhite', 'hybrid'),
	(default, 'Afghan Kush', 'afghankush', 'indica'),
	(default, 'Glueberry OG', 'glueberryog', 'sativa'),
	(default, 'Cinderella Jack', 'cinderellajack', 'hybrid'),
	(default, 'Orange Bud', 'budorange', 'hybrid'),
	(default, 'Sinmint Cookies', 'cookiessinmint', 'hybrid'),
	(default, 'Chocolope', 'chocolope', 'sativa'),
	(default, 'Moby Dick', 'dickmoby', 'sativa'),
	(default, 'Moonbeam', 'moonbeam', 'indica'),
	(default, 'Purple Haze', 'hazepurple', 'sativa'),
	(default, 'Riverview Ride', 'rideriverview', 'indica'),
	(default, 'Rockstar Kush', 'kushrockstar', 'indica'),
	(default, 'Sweet Deep Grapefruit', 'deepgrapefruitsweet', 'indica'),
	(default, 'Zkittlez', 'zkittlez', 'indica');
--ios means insert or select
create or replace function ios_store_id(
	_name text,
	_region citext,
	_country citext,
	_address citext = null,
	_address2 citext = null,
	_city citext = null,
	_postal_code citext = null,
	_url text = null,
	_longitude decimal = null,
	_latitude decimal = null,
	_osrm_hint_foot text = null,
	_osrm_hint_car text = null,
	_timezone text = null,
	_phone packed_phone_number = null,
	_extension citext = null,
	_partner boolean = false,
	_delivery boolean = false,
	_pickup boolean = false,
	_prepayment boolean = false,
	out _store_id integer
) as $$
begin
	loop
		select store_id
		from store
		where
			joined_name = _name and
			region = _region and
			country = _country and
			address is not distinct from _address and
			address2 is not distinct from _address2 and
			city is not distinct from _city and
			postal_code is not distinct from _postal_code
		into _store_id;
		exit when found;
		insert into store (
			joined_name,
			name,
			region,
			country,
			address,
			address2,
			city,
			postal_code,
			url,
			longitude,
			latitude,
			osrm_hint_foot,
			osrm_hint_car,
			timezone,
			phone,
			extension,
			partner,
			delivery,
			pickup,
			prepayment
		) values (
			_name,
			_name,
			_region,
			_country,
			_address,
			_address2,
			_city,
			_postal_code,
			_url,
			_longitude,
			_latitude,
			_osrm_hint_foot,
			_osrm_hint_car,
			_timezone,
			_phone,
			_extension,
			_partner,
			_delivery,
			_pickup,
			_prepayment
		) on conflict do nothing
		returning store_id
		into _store_id;
		exit when found;
	end loop;
end
$$ language plpgsql volatile;
create or replace function ios_product_id(_producer text, _brand text, _display_name text, _joined_name citext, _type product_type, _species species, _blend boolean, _strain citext, _description text, _show boolean, _terpenes citext[], _dosage decimal, out _product_id integer) as $$
begin
	loop
		select product_id
		from product
		where
			producer is not distinct from _producer and
			brand = _brand and
			joined_name = _joined_name and
			type = _type
		into _product_id;
		exit when found;
		insert into product (
			producer,
			brand,
			display_name,
			joined_name,
			type,
			species,
			blend,
			strain_id,
			description,
			show,
			terpenes,
			dosage
		)
		select
			_producer,
			_brand,
			_display_name,
			_joined_name,
			_type,
			case when strain_id is null then _species end,
			_blend,
			strain_id,
			_description,
			_show,
			_terpenes,
			_dosage
		from
			(values (null)) as w0t
			left join strain on (joined_name = _strain)
		on conflict do nothing
		returning product_id
		into _product_id;
		exit when found;
	end loop;
end
$$ language plpgsql volatile;
create or replace function ios_variant_id(_product_id integer, _portions smallint, _quantity decimal, _flavor citext = null, _gram_equivalency decimal = null, out _variant_id integer, out g decimal) as $$
begin
	loop
		select
			variant_id,
			gram_equivalency
		from variant
		where
			product_id = _product_id and
			quantity is not distinct from _quantity and
			portions = _portions and
			flavor is not distinct from _flavor
		into _variant_id, g;
		exit when found;
		insert into variant (
			product_id,
			portions,
			quantity,
			flavor,
			gram_equivalency
		) values (
			_product_id,
			_portions,
			_quantity,
			_flavor,
			_gram_equivalency
		) on conflict do nothing
		returning variant_id
		into _variant_id;
		exit when found;
	end loop;
end
$$ language plpgsql volatile;
create or replace function get_stores(_store_id integer = null) returns table(
	id integer,
	name text,
	url text,
	address text,
	longitude decimal,
	latitude decimal,
	images json,
	open integer[][2],
	timezone text,
	phone packed_phone_number,
	partner boolean,
	delivery boolean,
	pickup boolean,
	prepayment boolean
) as $$
	select
		store_id,
		name,
		url,
		case when address is not null then concat_ws(', ', address, address2, city, region, country, postal_code) end,
		longitude,
		latitude,
		(select json_agg(url) from store_image where store_image.store_id = store.store_id),
		(select array_agg(array[lower(x), upper(x)]) from unnest(open) as t(x)),
		timezone,
		phone,
		partner,
		delivery,
		pickup,
		prepayment
	from store
	where _store_id is null or store_id = _store_id
$$ language sql stable;
create or replace function avg_range(numrange) returns numeric as $$
	select (lower($1) + upper($1)) / 2
$$ language sql immutable;--idk if immutable actually helps or makes sense(documentation is a little unclear. is argument a value or a reference to a row's column??)
create or replace function thc2tc(thc numrange, cbd numrange) returns numeric as $$--how does nested inlining work?? stackoverflow.com/q/53040170. time to raise notice or case when pg_sleep(.01) is null then 2 else actual end.... yay fun. constant folding? can't explain analyze functions, so leave as separate exercise dba.stackexchange.com/q/84414
	select avg_range(thc) / (avg_range(thc) + avg_range(cbd))
$$ language sql immutable;
create or replace function contains_contained_by (anyarray, anyarray) returns boolean as $$
	select $1 <@ $2 and $1 @> $2 
$$ language sql parallel safe immutable; 
create operator <@> (
	procedure = contains_contained_by, 
	leftarg = anyarray, 
	rightarg = anyarray
);
create or replace function get_products(
	_store_ids integer[] = null,
	_species species[] = null,
	_blend boolean = null,
	_strain_ids integer[] = null,
	_types product_type[] = null,
	in_stock boolean = null,--true in stock only. false oos only. null everything
	search text = null,
	min_thc2tc decimal = null,
	max_thc2tc decimal = null,
	min_quantity decimal = null,
	max_quantity decimal = null
) returns table(
	product_id integer,
	variant_ids json,
	type product_type,
	producer text,
	brand text,
	name text,
	species species,
	blend boolean,
	strain_id integer,
	strain text,
	terpenes citext[],
	image text,
	x smallint,
	y smallint,
	cbd numrange,
	thc numrange,
	min_price decimal(5, 2),
	min_price_g text,
	min_portions smallint,
	max_portions smallint
) as $$
	select
		product_id,
		v,
		type,
		producer,
		brand,
		product.display_name,
		coalesce(product.species, strain.species),
		blend,
		strain_id,
		strain.display_name,
		terpenes,
		destination,
		x,
		y,
		cbd,
		thc,
		min,
		something,
		min_p,
		max_p
	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,
				x,
				y
			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
				poop.product_id,
				case
					when type in ('capsule', 'tea', 'gummy', 'mint', 'chocolate', 'baked') and not array_agg(distinct variant_id) <@> (select array_agg(variant_id) from variant where product_id = poop.product_id) then json_agg(distinct variant_id)
				end as v,
				range_merge(cbd) as cbd,--change to conditional: if edible, divide by portions in future
				range_merge(thc) as thc,--ditto
				min(price * (1 + tax)),
				case
					when type in ('flower', 'preroll', 'cartridge', 'disposable') then min(price / (portions * quantity))::decimal(5,2)::text
				end as something,
				--to construct ranges of concentrations per piece on client side, `${concentration / min_p} to ${concentation / max_p}`. in future, ranges of concentrations per pack on client side multiply instead of divide.  
				case
					when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then min(portions)
				end as min_p,
				case
					when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then max(portions)
				end as max_p
			from
				menu_item
				inner join variant using (variant_id)
				inner join product as poop using (product_id)
				left join lateral (
					select coalesce(sum(rate), 0) as tax
					from store_tax
					where
						menu_item.store_id = store_tax.store_id and
						(types is null or poop.type = any(types))
				) as t on true
				left join lateral (
					select (coalesce(sum(quantity), 0)) as reserved
					from line_item inner join cart using (cart_id)
					where
						status <= 'ready' and
						line_item.menu_item_id = menu_item.menu_item_id
				) as w on true
			where
				show and
				(store_id = any(_store_ids) or _store_ids is null) and
				case
					when in_stock then stock > reserved
					when not in_stock then stock <= reserved
					else true
				end and
				(min_quantity is null or quantity >= min_quantity) and
				(max_quantity is null or quantity <= max_quantity)
			group by
				poop.product_id,
				type,
				case
					when type = 'capsule' then quantity
					when type in ('tea', 'gummy', 'mint', 'chocolate', 'baked') then avg_range(thc) + avg_range(cbd)--group by concentration per pack. divide sum by portions once we recommend by concentration per piece in future
				end
		) as product_agg using (product_id)
	where
		(strain_id = any(_strain_ids) or _strain_ids is null) and
		(type = any(_types) or _types is null) and
		(coalesce(product.species, strain.species) = any(_species) or _species is null) and
		(blend = _blend or _blend is null) and
		(min_thc2tc is null or max_thc2tc is null or thc2tc(thc, cbd) between min_thc2tc and max_thc2tc) and
		(search is null or search <% concat_ws(' ', producer, brand, product.display_name))
	order by
		--ranking whenever that lands
		case when search is not null then word_similarity(search, concat_ws(' ', producer, brand, product.display_name)) end desc
$$ language sql stable;
create or replace function get_product_static(
	_product_id integer,
	_store_id integer = null
) returns table(
	producer text,
	brand text,
	name text,
	type product_type,
	species species,
	blend boolean,
	strain text,
	description text,
	terpenes json,
	images json,
	variants json
) as $$
	select
		producer,
		brand,
		product.display_name,
		type,
		coalesce(product.species, strain.species),
		blend,
		strain.display_name,
		description,
		array_to_json(terpenes),
		coalesce((select json_agg(destination) from product_image where product_image.product_id = product.product_id), '[]'),--aggregate first, then join https://stackoverflow.com/a/27626358 use subquery since small selection
		coalesce((select json_strip_nulls(json_agg(json_build_object(
			'id', variant_id,
			'portions', portions,
			'quantity', quantity,
			'flavor', flavor,
			'disabled', case
				when _store_id is null then null
				else not exists(select 1 from menu_item where menu_item.variant_id = variant.variant_id and menu_item.store_id = _store_id)
			end
		))) from variant where variant.product_id = product.product_id), '[]')
	from
		product left join
		strain using (strain_id)
	where
		product_id = $1
$$ language sql stable;
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', store_id,
			'cbd', cbd,
			'thc', thc,
			'price', price::text,--otherwise 32.40 will become 32.4 ... fuck json
			'stock', stock,
			'url', s.url || path,
			'menu_item', menu_item_id
		) order by partner)
	from
		menu_item
		left outer join store as s using (store_id)
	where
		variant_id = _variant_id
$$ language sql stable;
create or replace function get_menu_item (
	_variant_id integer,
	_store_id integer
) returns table (
	cbd numrange,
	thc numrange,
	price decimal(5, 2),
	stock integer,
	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 stable;
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,
	blend boolean,
	strain text,
	variants json
) as $$
	select
		product_id,
		type,
		producer,
		brand,
		product.display_name,
		coalesce(product.species, strain.species),
		blend,
		strain.display_name,
		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,
					'default_concentrations', (select json_agg(json_build_object('name', store.name, 'min_thc', lower(thc), 'max_thc', upper(thc), 'min_cbd',lower(cbd), 'max_cbd', upper(cbd))) from menu_item inner join store using (store_id) where not store.partner and variant_id = v.variant_id)
				)) order by portions, quantity)
			from
				variant as v
				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)
	order by
		case
			when brand = '' then lower(producer)
			else lower(brand)
		end asc,
		lower(product.display_name) asc
$$ language sql stable;
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
	--raise exception 'Value: %, %, %, %', proposed_gram_equivalency_line_item, gram_equivalency_in_cart, held, stock;
	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
				when type in ('rtd', 'shot', 'liquid') then 70
				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
				when type in ('rtd', 'shot', 'liquid') then 70
				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;

		if proposed_gram_equivalency_line_item + gram_equivalency_in_cart > 30 then
			raise exception 'exceeded 30g limit';
		end if;

		if not (old.cart_id is not null and old.cart_id != new.cart_id and old.menu_item_id = new.menu_item_id and old.quantity = new.quantity) then
			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;
			if _stock - held < new.quantity then
				raise exception 'out of stock';
			end if;
		end if;

		return new;
	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 decrement_stock() returns trigger as $$ begin
	if old.status = 'ready' and new.status = 'paid' then
		update menu_item
		set stock = greatest(stock - quantity, 0)
		from line_item
		where
			menu_item.menu_item_id = line_item.menu_item_id and
			line_item.cart_id = new.cart_id;
	end if;
	return new;
end $$ language plpgsql volatile;
--drop trigger if exists decrement_stock_trigger on cart;
create trigger decrement_stock_trigger before update on cart for each row execute function decrement_stock();
create or replace function modifycart (
	_menu_item_id integer,
	qty smallint = 1,
	_user_id integer = null,
	cart_ids integer[] = null,
	out cart_id integer
) as $$
	with s as (
		select cart_id
		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 or
			user_id = _user_id) and
			status = 'active' and
			menu_item_id = _menu_item_id and
			partner
		limit 1
	), i as (
		insert into cart (user_id, store_id)
		select _user_id, store_id
		from menu_item join store using (store_id)
		where
			not exists (select from s) and
			menu_item_id = _menu_item_id and
			partner
		returning cart_id
	), c as (
		select * from s union all select * from i
	)
	insert into line_item (cart_id, menu_item_id, quantity)
	select cart_id, _menu_item_id, qty from c
	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,
	store_id integer,
	static_expiry timestamptz,
	dynamic_expiry timestamptz,
	taxes json,
	line_items json
) as $$
	select
		user_id,
		cart_id,
		cart.store_id,
		cart.system_time_start + '30 minutes',
		(
			select
				greatest(
					max(system_time_start),
					max(case--just to handle deleted line items. worth?
						when system_time_end = 'infinity' then '-infinity'
						else system_time_end
					end)
				)
			from line_item__between(cart.system_time_start, now())
			where cart_id = cart.cart_id
		) + '5 minutes',
		(select json_agg(json_build_object(
			'name', name,
			'rate', rate::text,
			'types', types
		)) from store_tax where store_id = cart.store_id and (types is null or types && array_agg(product.type))),
		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,
			'species', coalesce(product.species, strain.species),
			'blend', blend,
			'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 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 status = 'active'
	group by cart_id
$$ language sql stable;
create or replace function anonymous_carts(cart_ids integer[]) returns table (
	cart_id integer,
	store_id integer,
	static_expiry timestamptz,
	dynamic_expiry timestamptz,
	taxes json,
	line_items json
) as $$
	select distinct on (store_id)
		cart_id,
		store_id,
		static_expiry,
		dynamic_expiry,
		taxes,
		line_items
	from active_carts()
	where cart_id = any(cart_ids)
$$ language sql stable;
create or replace function authenticated_carts(_user_id integer) returns table (
	cart_id integer,
	store_id integer,
	static_expiry timestamptz,
	dynamic_expiry timestamptz,
	taxes json,
	line_items json
) as $$
	select
		cart_id,
		store_id,
		static_expiry,
		dynamic_expiry,
		taxes,
		line_items
	from active_carts()
	where user_id = _user_id
$$ language sql stable;

create or replace function reservation(_cart_id integer) returns table (
	store_id integer,
	store_name text,
	statuses json,
	taxes json,
	line_items json
) as $$
	with blah as (
		select *
		from cart
		where
			cart_id = _cart_id and
			user_id is null and
			status > 'active'
	), blah2 as (
		select * from blah union all 
		select * from cart_history
		where
			exists (select * from blah) and
			cart_id = _cart_id and
			user_id is null and
			status > 'active'
	)--change to cart_with_history? since it doesn't need to be present in cart. if store got deleted,
	--cascade delete to cart, but should still be visible (store_cancelled)? zzz...
	select
		first(cart.store_id),
		first(store__as_of.name),
		first(statuses),
		(select json_agg(json_build_object(
			'name', name,
			'rate', rate::text,
			'types', types
		)) from store_tax__as_of(first(cart.system_time_start)) where store_id = first(cart.store_id) and (types is null or types && array_agg(product__as_of.type))),
		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,
			'species', coalesce(product__as_of.species, strain__as_of.species),
			'blend', blend,
			'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,
			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,
				'end', system_time_end
			) order by
				system_time_start
			) as statuses
		from blah2
		group by cart_id) 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
$$ language sql stable;

create or replace function user_reservations(_user_id integer) returns table (
	cart_id integer,
	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
		cart_id,
		first(cart.store_id),
		first(store__as_of.name),
		first(statuses),
		(select json_agg(json_build_object(
			'name', name,
			'rate', rate::text,
			'types', types
		)) from store_tax__as_of(first(cart.system_time_start)) where store_id = first(cart.store_id) and (types is null or types && array_agg(product__as_of.type))),
		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,
			'species', coalesce(product__as_of.species, strain__as_of.species),
			'blend', blend,
			'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,
				'end', system_time_end
			) 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 (
	cart_id integer,
	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
		cart_id,
		first(statuses),
		(select json_agg(json_build_object(
			'name', name,
			'rate', rate::text,
			'types', types
		)) from store_tax__as_of(first(cart.system_time_start)) where store_id = _store_id and (types is null or types && array_agg(product__as_of.type))),
		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,
			'species', coalesce(product__as_of.species, strain__as_of.species),
			'blend', blend,
			'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,
			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,
				'end', system_time_end
			) 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 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 invalidate_stale_carts() returns void as $$
	delete from cart where cart_id in (
		select cart_id
		from cart inner join line_item__between(system_time_start, now()) as l using (cart_id)
		where status = 'active'
		group by cart_id
		having now() >= least(
			cart.system_time_start + '30 minutes',
			greatest(
				max(l.system_time_start),
				max(case
					when l.system_time_end = 'infinity' then '-infinity'
					else l.system_time_end
				end)
			) + '5 minutes'
		)
	)
$$ language sql volatile;
commit;