VIGS24FLOPPTBLIBZIB2LFMYCDZ3AO4FBXYADAKQMJFNPM4JHAGQC
BUCBGGRUTH7HNAADSU24U4BPHPUY7QYB3KRZPIIDUERZTL3YNTZQC
EGNRBQUMOZHAF5B7KCMTAPMB4H3USK4MK33Q6T5V26D6HOJNW6LQC
E4HFJ4L4PAGV7R7EXVG2B2CWDGGW7XWU4D37VURZ66HZ3ILHCFUAC
ZI2RJOZ2HXBHX7L54BTSDKESY6NG5KBJLVHCNY7K7ZLGIUOIHQUQC
UCY5PVFCT2QIOGR47KFS4KBF2ZPHRBOAIOH6PJEVWCHLMBRBLOMQC
4WREYORZT3SWUXADWHSS6B4PQSP3QSLH77CGNKRH6IRKUMX4TARAC
WYTMZJFYVKHR4QH7AV5JUNWXT6NAC5NNQNPZCQSDI6LGI7DVXFYAC
5FCMFAPUTCCO2JILRWLC4PHOSHYFCQNH3D7PKLA267MHVZADOX2AC
EDVJECDIUUOSU4TS2DCL25MJDR5RQG7PQVTYEM6R3B4SFGKSX6MAC
let query = 'insert into menu_item values ';//add on conflict. since we're god, trusting parameters.store is fine. otherwise, need to check
let query = 'insert into menu_item values ';//since we're god, trusting parameters.store is fine. otherwise, need to check
where (menu_item.cbd, menu_item.thc, menu_item.price, menu_item.featured) is distinct from
(excluded.cbd, excluded.thc, excluded.price, excluded.featured)
where (menu_item.cbd, menu_item.thc, menu_item.price, menu_item.stock, menu_item.featured) is distinct from
(excluded.cbd, excluded.thc, excluded.price, excluded.stock, excluded.featured)
//persist cart to db
console.log(parameters);
/*let cart = (await pool.query('select id from cart where user_account = $1 and store = $2', [ws.user_ID, parameters.store])).rows;
console.log(cart);
if(cart.length === 0) {
//great, create new cart
//append line item
} else if(rows.length === 1) {
//append line items
} else {
return;
}*/
//cart[0].id is the magic
//await
//find line item with cart id and same menu_item_with_history
//I guess we can append line item no problem...
//search for an existing cart where id = ws, store = parameters.store
//https://medium.com/@betakuang/why-postgresqls-on-conflict-cannot-find-my-partial-unique-index-552327b85e1
let cart = (await pool.query(`insert into cart (user_account, store, placed_at) values ($1, $2, null) on conflict (user_account, store) where placed_at is null do nothing returning id`, [ws.user_ID, parameters.store])).rows;
//https://medium.com/@betakuang/why-postgresqls-on-conflict-cannot-find-my-partial-unique-index-552327b85e1
let cart = (await pool.query(`insert into cart (user_account, store) values ($1, $2) on conflict (user_account, store) where status is null or status = 'placed' or status = 'ready' do nothing returning id`, [ws.user_ID, parameters.store])).rows;
//unhandled: currently pending order should not be allowed to create new cart.! if we allowed creating new cart while pending order, when an order gets cancelled, status is set to null, and cart will reappear. now there will be 2 carts for same store
//can we solve this with where status is null | placed | ready do nothing ? will it still use the unique partial index? I think so - just update the partial index
cart = (await pool.query(`select id from cart where user_account = $1 and store = $2`, [ws.user_ID, parameters.store])).rows[0].id;
} else {
let kart = (await pool.query(`select id from cart where user_account = $1 and store = $2 and status is null`, [ws.user_ID, parameters.store])).rows;
if(kart.length === 0) {
console.log('wtf??');
} else if(kart.length === 1) {
cart = kart[0].id;
} else {
console.log('kys');
}
} else if(cart.length === 1) {
console.log(cart);//unhandled: adding menu_item from other store and currently pending order.
await pool.query(`insert into line_item (cart, product_with_history, variant_with_history, menu_item_with_history, quantity) values ($1, $2, $3, $4, 1) on conflict (cart, product_with_history, variant_with_history, menu_item_with_history) do update set quantity = line_item.quantity + excluded.quantity returning id`, [cart, parameters.product, parameters.variant, parameters.menu_item]);//no fk is dangerous. sanetize pls
console.log(cart);
response_ID: request_ID
response_ID: request_ID,
data: (await pool.query(`insert into line_item (cart, menu_item, quantity)
select $1, id, 1 from menu_item where variant = $2 and store = $3
on conflict on constraint unique_line_item do update set quantity = line_item.quantity + excluded.quantity returning id`,
[cart, parameters.variant, parameters.store]
)).rows.length//should always be 1
case 'cart'://does this ensure the right cart is returned? not passing in store...
//todo: join with product, variant, menu_item
///I don't like group by cart.created_at, is id better?
//maybe we should include cart.placed_at
let cart = (await pool.query('select cart.id, json_agg(line_item) as line_items from cart inner join line_item on cart.id = line_item.cart where cart.user_account = $1 and cart.placed_at is null group by cart.id order by cart.id desc', [ws.user_ID])).rows;//we need store for tax
console.log('cart');
let idddd;
console.log(cart);//if there's ever more than 1 we gave fucked up
if(cart.length === 0) {
cart = 'nothing'
} else {
idddd = cart[0].id;
cart = cart[0].line_items;
case 'cart':
if(isLoggedIn(ws)) {
ws.send(JSON.stringify({
response_ID: request_ID,
data: (await pool.query(`select
cart.id,
store.name,
json_agg(json_build_object(
'brand', product.display_brand,
'name', product.display_name,
'type', product.type,
'portions', variant.portions,
'quantity', variant.quantity,
'cbd', menu_item.cbd,
'thc', menu_item.thc,
'price', menu_item.price,
'fuck', line_item.quantity
)) as line_items
from
cart
inner join store on cart.store = store.id
inner join line_item on line_item.cart = cart.id
inner join menu_item on line_item.menu_item = menu_item.id
inner join variant on menu_item.variant = variant.id
inner join product on variant.product = product.id
where
user_account = $1 and
status is null
group by
cart.id,
store.id
`, [ws.user_ID])
).rows
}));
//do we want cart as parameter? hm
//update cart placed_at to now()
let kartik = (await pool.query('update cart set placed_at = now() where cart.user_account = $1 and cart.placed_at is null returning id', [ws.user_ID])).rows;
console.log('kartik');
console.log(kartik);
let blacktshirt;
if(kartik.length === 0) {
blacktshirt = 'failed to reserve';
} else if(kartik.length === 1) {
//
} else {
blacktshirt = 'multiple carts kaboom';
//check if parameters.cart belongs to the currently logged in user
//check if > 30g dried equivalency
//check if everything is in stock
if(isLoggedIn(ws)) {//https://build.affinity.co/understanding-tricky-joins-and-multi-table-updates-in-postgresql-using-boolean-algebra-7b329606ca45 as to why need to subselect vs from from_list
ws.send(JSON.stringify({
response_ID: request_ID,
data: (await pool.query(`update cart set status = 'placed'
from (
select
sum(
--coalesce(variant.portions, 1) * variant.quantity * line_item.quantity
--need to take into account measurement unit as well...
--how can I show the results of this query?
variant.quantity * line_item.quantity /
case
when product.type = 'flower' or product.type = 'preroll' then 30
end
) <= 1 as within_limits,
bool_and(menu_item.stock >= line_item.quantity) as in_stock,
cart.id
from
cart
inner join line_item on cart.id = line_item.cart
inner join menu_item on line_item.menu_item = menu_item.id
inner join variant on menu_item.variant = variant.id
inner join product on variant.product = product.id
where
cart.user_account = $1 and
cart.status is null and
cart.id = $2
group by
cart.id
) as fuck
where
fuck.within_limits = true and
fuck.in_stock = true and
fuck.id = cart.id
returning cart.id
`, [ws.user_ID, parameters.cart])).rows
}));