create database lobomj;
\c lobomj
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS first_last_agg;
-- Create a function that always returns the first non-NULL item
CREATE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $1;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE public.FIRST (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);
 
-- Create a function that always returns the last non-NULL item
CREATE FUNCTION public.last_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT AS $$
        SELECT $2;
$$;
 
-- And then wrap an aggregate around it
CREATE AGGREGATE public.LAST (
        sfunc    = public.last_agg,
        basetype = anyelement,
        stype    = anyelement
);
create type product_type as enum ('flower', 'preroll', 'oil', 'spray', 'capsule');
create type concentration_unit as enum ('mg', 'proportion');
create type measurement_unit as enum('ml', 'mg', 'g', 'capsule');
create type currency_abbreviation as enum('CAD', 'USD');
create table store (
	ID serial primary key,
	name text not null,
	URL text not null unique,
	address text,
	address2 text,
	city text,
	region text not null,
	country text not null,
	postal_code text,
	currency_abbreviation currency_abbreviation not null
);
--store_with_history view
--store_history table via trigger
--https://stackoverflow.com/questions/4464898/best-way-to-store-working-hours-and-query-it-efficiently
--do we care about store hours history?
create table product (
	ID serial primary key,
	joined_brand text not null,
	joined_name text not null,
	type product_type not null,
	concentration_unit concentration_unit not null,
	measurement_unit measurement_unit not null,
	display_brand text not null,
	display_producer text,
	display_name text not null,
	display_strain text not null,
	display_description text not null,
	show boolean default false,
	created_at timestamptz not null default now(),--useful for "new arrivals". do we need this at store level too? todo: terpene text array, image_paths text array
	daily boolean default false not null,
	weekly boolean default false not null,
	monthly boolean default false not null,
	rarely boolean default false not null,
	happy boolean default false not null,
	relaxed boolean default false not null,
	balanced boolean default false not null,
	constraint unique_product unique(joined_brand, joined_name, type)
);
--product_with_history view
--product_history table via trigger
create table variant (
	ID serial primary key,
	product integer not null references product on delete cascade,
	portions smallint not null,
	quantity decimal(6, 3) not null,
	constraint unique_variant unique(product, portions, quantity)
);
--variant_with_history view
--variant_history table via trigger
create table menu_item (
	ID serial primary key,
	variant integer not null references variant on delete cascade,--perhaps restrict makes more sense. in other areas too... but with temporal_table is it safe to cascade since it'll still be present in history table?
	store integer not null references store on delete cascade,
	CBD_min decimal(6, 4) not null,
	CBD_max decimal(6, 4) not null,
	THC_min decimal(6, 4) not null,
	THC_max decimal(6, 4) not null,
	price decimal(5, 2) not null,
	stock smallint not null,
	featured boolean default false not null,
	constraint unique_menu_item unique(variant, store)
);
--menu_item_with_history view
--menu_item_history table via trigger
--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 type user_type as enum ('god', 'store owner', 'store worker', 'user');
create table user_account (
	ID serial primary key,
	email citext not null unique,
	password_hash text not null unique,
	--implement deserialized argon2 someday
	--variant
	--version
	--memory
	--iterations
	--parallelism smallint
	--salt bytea
	--hash bytea
	token_hash bytea,
	address text,
	address2 text,
	city text,
	region text,
	country text,
	postal_code text,
	type user_type not null,
	COMT COMT,
	AKT1 AKT1,
	CYP2C9 CYP2C9,
	"CYP2C19*2" "CYP2C19*2",
	"CYP2C19*3" "CYP2C19*3",
	"CYP2C19*17" "CYP2C19*17"
);--not done. more research needed on best practices, email reset, sign in count, IP address, etc. survey?
--todo: store reviews, product reviews, notifications, watched products, mixology, bidding for product placement on home page
create table cart (
	ID serial primary key,
	user_account integer references user_account on delete cascade, --nullable, for no user
	store integer not null, --references store on delete cascade, since temporal_table relevation lol
	created_at timestamptz not null default now(),
	abandoned_at timestamptz,--when cart is cleared or they start adding stuff from other store
	placed_at timestamptz,--before this, they are editing their cart
	want_at timestamptz, --user could want it later ie not immidiately. null means now
	ready_at timestamptz,
	payed_at timestamptz,
	delivered_at timestamptz,
	user_cancelled_at timestamptz,
	store_cancelled_at timestamptz,
	no_show_at timestamptz,
	story text
	--todo: discounts and loyalty
);--compute total in application layer?
--history table enough for cart?
create table line_item (
	ID serial primary key,
	cart integer not null references cart on delete cascade,
	product_with_history integer not null,--can I fk this? https://stackoverflow.com/a/10183157/1201238
	variant_with_history integer not null,--ditto
	menu_item_with_history integer not null,--yo
	quantity smallint not null
);
--create table mix_item (--mix -> add2cart will apply the mix_item. will get removed after placed_at and line_item do not match??
--	ID serial primary key,
--	cart integer not null references cart on delete cascade,
--	mix integer not null references mix on delete cascade
--);
--maybe line_item and cart no fk link to with_history
--can only fk if no deletes ie restrict
--everything else on delete cascade, since temporal_table solves