[2026-11-10 07:30:06] production.INFO: API Request {"browserinfo":[{"Browser":null,"Version":null,"IP":null,"Mobile":false,"Cookie":true,"OS":null,"OSVersion":null,"Screen":null,"WindowSize":null,"FlashVersion":null,"Dattime":"2026-11-10T07:30:06.161Z"}],"email":"sudo","password":"gnQnY?+Y3l9VsIpEq7{6","nocomp":true,"cmp":"sys","dispensary":null,"isvirtual":null} [2026-11-10 07:30:06] production.INFO: SP response {"res":"{\"status\" : true, \"errorcode\" : null, \"msg\" : \"Login successful\", \"result\" : {\"userid\" : \"5f74d3fa-06ac-11ea-9cac-6fc0c2648c68\", \"token\" : \"8d235ca4-dcd8-11f1-be58-cf9cfe57c8c3\", \"name\" : \"Super Admin\", \"session_id\" : \"8d235ca4-dcd8-11f1-be58-cf9cfe57c8c3\", \"cmp\" : \"sys\", \"usertype\" : \"0\", \"rolename\" : \"SUDO\", \"logo\" : null, \"cmpname\" : null, \"storeid\" : null, \"apikey\" : \"\"}}"} [2026-11-10 07:30:06] production.INFO: API response {"res":"{\"status\":true,\"errorcode\":null,\"msg\":\"Login successful\",\"result\":{\"userid\":\"5f74d3fa-06ac-11ea-9cac-6fc0c2648c68\",\"token\":\"eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJsdW1lbi1qd3QiLCJzdWIiOiI1Zjc0ZDNmYS0wNmFjLTExZWEtOWNhYy02ZmMwYzI2NDhjNjgiLCJpYXQiOjE3OTQyOTU4MDYsImV4cCI6MTc5NDM4MjIwNn0.SPGdIP4IQ_aV0lQGFntC08d_sZo6LNtA7elbMrygCG0\",\"name\":\"Super Admin\",\"session_id\":\"8d235ca4-dcd8-11f1-be58-cf9cfe57c8c3\",\"cmp\":\"sys\",\"usertype\":\"0\",\"rolename\":\"SUDO\",\"logo\":null,\"cmpname\":null,\"storeid\":null,\"apikey\":\"\",\"company_metadata\":null,\"has_old_dashboard\":true,\"has_new_dashboard\":true}}"} [2026-11-10 07:39:02] production.INFO: before redis data for cova api [2026-11-10 07:39:21] production.INFO: params{"dispensary":"6ea08aa9-20a0-4079-b0db-d4c48310260c","cmp":"179"} [2026-11-10 07:39:26] production.INFO: before redis data for cova api {"integratorid":"39e42a12-dda1-4a7b-8877-26cc09235084","integclientid":"VFISandbox.VFITechnology","integapikey":"2hX97oYsCkEBmf0eiw0p61x1","integdispid":"381255","locationId":381256,"username":"VFITechnology.COVA.APIUser.VFISandbox","password":"swaTVPl3qh","posName":"cova","dispensary":"6ea08aa9-20a0-4079-b0db-d4c48310260c","access_token":"NjlKRry0b0Y2GWlGNn6bQzY5SkY1OUpGA18If19JOjQOQHwWWlMsB1VYDj91WHl1T0sadH1hEglBbjt0Ww8ZIndqchRSCggFeWEP","expires_at":"2026-11-10 19:39:21"} [2026-11-10 07:39:26] production.INFO: redis data for cova api {"integratorid":"39e42a12-dda1-4a7b-8877-26cc09235084","integclientid":"VFISandbox.VFITechnology","integapikey":"2hX97oYsCkEBmf0eiw0p61x1","integdispid":"381255","locationId":381256,"username":"VFITechnology.COVA.APIUser.VFISandbox","password":"swaTVPl3qh","posName":"cova","dispensary":"6ea08aa9-20a0-4079-b0db-d4c48310260c","access_token":"NjlKRry0b0Y2GWlGNn6bQzY5SkY1OUpGA18If19JOjQOQHwWWlMsB1VYDj91WHl1T0sadH1hEglBbjt0Ww8ZIndqchRSCggFeWEP","expires_at":"2026-11-10 19:39:21"} [2026-11-10 08:26:47] production.INFO: Prepare Cova Product Query: with _discount_conditions as ( select bb.* from ( select prd.*, pds.bgimg, pds.bgtheme, pds.bgcolor, pds.show_on_catalog, pds.show_on_stealsdeals, pds.bgorder,pds.name, case when (prd.discount_method = 'PERCENT' and prd.discount_type = 'EachMatchedPercentOff') then round((coalesce(pd_sub.sellingprice, 0) - (coalesce(pd_sub.sellingprice, 0) * prd.discount_amount) / 100)::numeric, 2) when prd.discount_method = 'DOLLAR' and coalesce(pd_sub.sellingprice, 0) >= prd.discount_amount then (coalesce(pd_sub.sellingprice, 0) - prd.discount_amount) else null end as discount_value, row_number() over ( partition by prd.prodid order by case when (prd.discount_method = 'PERCENT' and prd.discount_type = 'EachMatchedPercentOff') then round((coalesce(pd_sub.sellingprice, 0) - (coalesce(pd_sub.sellingprice, 0) * prd.discount_amount) / 100)::numeric, 2) when prd.discount_method = 'DOLLAR' and coalesce(pd_sub.sellingprice, 0) >= prd.discount_amount then (coalesce(pd_sub.sellingprice, 0) - prd.discount_amount) else null end asc ) as prdrow from ( with bundle_criteria as ( select d.id, d.discount_id, rule->>'criteria' as criteria, rule->'values' as values from cmp179.pos_discounts d cross join lateral jsonb_array_elements( case when jsonb_typeof(d.criteria_condition) = 'array' then d.criteria_condition else jsonb_build_array(d.criteria_condition) end ) obj cross join lateral jsonb_array_elements(obj->'sets') rule where d.discount_method = 'BUNDLE' and d.isactive = true and d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb ), bundle_products as ( select distinct bc.discount_id, p.id as product_id, p.puid as product_puid, p.dispensaryid, p.categoryid, p.brandid from bundle_criteria bc join cmp179.product p on ( (bc.criteria = 'category' and p.categoryid in ( select jsonb_array_elements_text(bc.values)::int )) or (bc.criteria = 'brand' and p.brandid in ( select jsonb_array_elements_text(bc.values)::int )) or (bc.criteria = 'product_ids' and p.puid::text in ( select jsonb_array_elements_text(bc.values) )) ) where p.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c' ), criteria_values as ( -- one row per (discount, category-value) for non-BUNDLE discounts select d.discount_id, d.criteria_operator, 'category'::text as criteria_type, (jsonb_array_elements_text(d.criteria_condition->'category'))::int as value from cmp179.pos_discounts d where d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and d.isactive = true and d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb and (d.criteria_condition->'category') is not null union all -- one row per (discount, brand-value) for non-BUNDLE discounts select d.discount_id, d.criteria_operator, 'brand'::text as criteria_type, (jsonb_array_elements_text(d.criteria_condition->'brand'))::int as value from cmp179.pos_discounts d where d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and d.isactive = true and d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb and (d.criteria_condition->'brand') is not null ), criteria_required_types as ( -- which criteria types (category/brand) each discount actually specifies select discount_id, criteria_operator, array_agg(distinct criteria_type) as required_types from criteria_values group by discount_id, criteria_operator ), criteria_product_matches as ( -- indexable equality match: product against each specified value, per type select distinct cv.discount_id, cv.criteria_type, p2.id as product_id from criteria_values cv join cmp179.product p2 on p2.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c' and ( (cv.criteria_type = 'category' and p2.categoryid = cv.value) or (cv.criteria_type = 'brand' and p2.brandid = cv.value) ) ), criteria_match_summary as ( -- which types matched, per (discount, product) select discount_id, product_id, array_agg(distinct criteria_type) as matched_types from criteria_product_matches group by discount_id, product_id ), narrowed_discount_ids as ( -- non-BUNDLE discounts that ALSO carry an explicit product list -- (e.g. a CatalogId sub-condition AND-ed alongside category/brand in -- the original promotion) — for these, pos_discount_products is a -- narrowing filter on top of the category/brand match, not the sole -- matching mechanism (that role is reserved for criteria_condition IS -- NULL discounts, handled separately via the ELSE p.prodid branch below) select distinct pos_discount_id as discount_id from cmp179.pos_discount_products ), discount_product_narrowing as ( select distinct pdp.pos_discount_id as discount_id, p2.id as product_id from cmp179.pos_discount_products pdp join cmp179.product p2 on p2.puid = pdp.product_id and p2.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c' ), criteria_matches as ( -- apply AND/OR semantics, then pull fresh product columns select cmt.discount_id, p2.id as product_id, p2.puid as product_puid, p2.categoryid, p2.brandid from criteria_match_summary cmt join criteria_required_types crt on crt.discount_id = cmt.discount_id join cmp179.product p2 on p2.id = cmt.product_id where ( crt.criteria_operator = 'OR' or (crt.criteria_operator = 'AND' and cmt.matched_types @> crt.required_types) ) and ( not exists ( select 1 from narrowed_discount_ids ndi where ndi.discount_id = cmt.discount_id ) or exists ( select 1 from discount_product_narrowing dpn where dpn.discount_id = cmt.discount_id and dpn.product_id = p2.id ) ) ) select d.id, d.discount_id, d.discount_title, d.discount_amount, d.discount_method, d.criteria_condition, d.discount_type, d.number_to_match, d.grams_per_match_unit, d.discount_stackable, d.discount_condition_detail, d.criteria_operator, d.other_criteria_operator, d.other_criteria_condition, CASE WHEN d.discount_method = 'BUNDLE' then bp.product_id WHEN d.criteria_condition IS NOT NULL THEN crm.product_id ELSE p.prodid END AS prodid, '6ea08aa9-20a0-4079-b0db-d4c48310260c' as dispensary_id, case when d.discount_method = 'BUNDLE' then bp.product_puid when d.criteria_condition is not null then crm.product_puid else p.product_id::uuid end as puid, d.createdon, d.createdby, d.updatedon, d.updatedby, d.isactive, d.isdelete, CASE WHEN d.discount_method = 'BUNDLE' then bp.categoryid WHEN d.criteria_condition IS NOT NULL THEN crm.categoryid ELSE null END AS categoryid, CASE WHEN d.discount_method = 'BUNDLE' then bp.brandid WHEN d.criteria_condition IS NOT NULL THEN crm.brandid ELSE null END AS brandid, d.createdon_pos from cmp179.pos_discounts d left join cmp179.pos_discount_products p on d.discount_id = p.pos_discount_id and d.criteria_condition is null left join criteria_matches crm on d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and crm.discount_id = d.discount_id left join bundle_products bp on d.discount_method = 'BUNDLE' AND bp.discount_id = d.discount_id where d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb and d.isactive = true and ( ( d.discount_method = 'BUNDLE' and exists ( select 1 from jsonb_array_elements( case when jsonb_typeof(d.criteria_condition) = 'array' then d.criteria_condition else jsonb_build_array(d.criteria_condition) end ) obj where ( (obj->>'set_operator' = 'AND' and not exists ( select 1 from jsonb_array_elements(obj->'sets') s where not ( (s->>'criteria' = 'category' and bp.categoryid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'brand' and bp.brandid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'product_ids' and bp.product_puid::text in ( select jsonb_array_elements_text(s->'values') )) ) ) ) or (obj->>'set_operator' = 'OR' and exists ( select 1 from jsonb_array_elements(obj->'sets') s where (s->>'criteria' = 'category' and bp.categoryid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'brand' and bp.brandid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'product_ids' and bp.product_puid::text in ( select jsonb_array_elements_text(s->'values') )) ) ) ) ) ) or ( d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and crm.product_id is not null ) or d.criteria_condition is null ) ) prd inner join cmp179.productdetails pd_sub on prd.prodid = pd_sub.productid and prd.dispensary_id = pd_sub.dispensaryid::text and pd_sub.isdefault = 1 left join cmp179.pos_discount_status pds on prd.discount_id = pds.discount_id and pds.dispensaryid = prd.dispensary_id left join cmp179.pos_discount_conditions pdc on pdc.discount_id = prd.id and pdc.isactive = true where coalesce(pds.isactive, true) = true and prd.dispensary_id = '6ea08aa9-20a0-4079-b0db-d4c48310260c' and prd.isactive = true and case when pdc.discount_id is null then true else ((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone)::date between pdc.start_date and coalesce(pdc.end_date, ((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone)::date) end and case when pdc.discount_id is null then true else (case when (pdc.start_time is not null or pdc.end_time is not null) then ((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone)::time between pdc.start_time and pdc.end_time else true end) end and case when (pdc.discount_id is null) then true when (pdc.read_condition = 'REPEAT_INTERVAL_ON_DAYS') then ( position(TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'DY') in pdc.repeat_days) > 0 and (((current_timestamp at time zone 'America/Indiana/Indianapolis')::date - (pdc.start_date - extract(DOW from pdc.start_date)::int)::date) / 7) % pdc.repeat_interval = 0 ) when (pdc.read_condition = 'WEEKLY' or pdc.read_condition = 'DAILY') then position(TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'DY') in pdc.repeat_days) > 0 when (pdc.read_condition = 'WEEK_DAY') then TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'FMDay') = pdc.repeat_days when (pdc.read_condition = 'ANNUALLY_ON_MONTH_DAY') then (TRIM(leading '0' from TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'FMMonth FMDD')) = pdc.repeat_days) when (pdc.read_condition = 'MONTHLY_ON_NUMERIC_DAY') then extract(day from (current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone) = pdc.repeat_days::int when (pdc.read_condition = 'MONTHLY_ON_STRING_DAY') then concat( case to_char(current_timestamp, 'FMW')::int when 1 then 'first' when 2 then 'second' when 3 then 'third' when 4 then 'fourth' when 5 then 'fifth' end, ' ', to_char((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'FMDay') ) = pdc.repeat_days when (pdc.read_condition = 'DO_NO_REPEAT') then true else false end order by discount_value asc) bb where (bb.prdrow = 1 or bb.prdrow is null) )select p.custdiscountid, p.id, p.seolabel, p.name, p.isnew, p.dispensaryid, p.isactive, p.published, p.shortdesc, p.tags, p.new_arrival, p.hidfrmscren, pd.price_type, pd.sellingprice, pd.id as proddetailid, pd.img1, round(pd.totalmgcbd::numeric, 0) as totalmgcbd, round(pd.totalmgthc::numeric, 0) as totalmgthc, pd.tier_method, pd.effects_icon, pd.variantname, ct.name as catname, ct.poscategoryid as poscategoryid, (ct.catorder)::int as catorder, b.label as brandlabel, b.name as brandname, tpd.discount_method as discount_method, tpd.discount_amount as discount_amount, coalesce(tpd.name,tpd.discount_title) as discount_title, tpd.prdrow as tpdrow, tpd.prodid as prodid, tpd.bgimg, tpd.bgtheme, tpd.bgcolor, 0 as min_price, 0 as max_price, 0 as weight, c.name as classname, c.remark as classcolor, u.name as uom, u.code as uomcode, (coalesce(prodrating->>'rating', '0')::int / coalesce(prodrating->>'custcount', '1')::int) as prodrating, coalesce(pd.sellingprice, 0) as price, coalesce(pd.price, 0) as regular_price, lr.lab_results, pd.effects, ct.hide_from_mobile, round(pd.weight_amount, 2) as weight_amount, pd.sku, p.puid, null as tier_prices, p.createdon, sc.name as subcatname, tpd.show_on_catalog, tpd.show_on_stealsdeals, c.name as classcode , ct.show_labresults , p.metatitle , pd.total_flower_weight_g , tpd.discount_condition_detail as discount_condition_detail , pd.img2 , pd.img3, (ct.productorder)::jsonb as productorder, p.categoryid, tpd.bgorder as bogoorder, tpd.discount_value, p.periodicsales, pd.attr_general, pd.flavors, pd.ingredients, pd.aroma, lr.terpenes, p.is_medical from cmp179.product p inner join cmp179.productdetails pd on p.id = pd.productid and p.dispensaryid = pd.dispensaryid and pd.isdefault = 1 inner join cmp179.category ct on ct.id = p.categoryid and p.dispensaryid = ct.dispensaryid left join cmp179.subcategory sc on sc.id = p.subcatid and p.dispensaryid = sc.dispensaryid left join cmp179.brand b on b.id = p.brandid and p.dispensaryid = b.dispensaryid left join _discount_conditions tpd on (tpd.prodid=p.id) left join cmp179.mom c on c.id = p.typeid and p.dispensaryid = c.dispensaryid left join cmp179.mom u on u.id = p.uom::int and p.dispensaryid = u.dispensaryid left join lateral( with mapped as ( select plr.product_id, plr.minimum_value, plr.maximum_value, COALESCE(NULLIF(lrc.uom, ''),plr.amount_type) AS amount_type, lrc.cannabinoids, lrc.terpene, coalesce(nullif(lrc.field_code, ''), lrc.display_label, plr.content_type) as mapped_label, nullif(lrc.field_code, '') as field_code, lrc.name as original_name from cmp179.product_lab_results plr inner join cmp179.lab_results_content lrc on lower(plr.content_type) = lower(lrc.name) and lrc.category = ct.treez_name and ct.isactive = true and lrc.isactive = true and lrc.uom = plr.amount_type where plr.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c'::uuid and plr.product_id = p.id ), dedup as ( select m.* from mapped m where not exists ( select 1 from mapped x where x.field_code = m.original_name and m.field_code is null ) ) select product_id, jsonb_agg( jsonb_build_object( 'content_type', mapped_label, 'minimum_value', round(minimum_value, 2), 'maximum_value', round(maximum_value, 2), 'amount_type', amount_type, 'cannabinoids', cannabinoids, 'terpene', terpene ) ) as lab_results, case when bool_or(terpene = true) then jsonb_build_object( 'terpene', true, 'content_type', 'Total Terpene', 'minimum_value', round(sum(minimum_value) filter (where terpene = true), 2), 'maximum_value', round(sum(maximum_value) filter (where terpene = true), 2), 'amount_type', max(amount_type) filter (where terpene = true) ) else null end as terpenes from dedup group by product_id ) lr on lr.product_id = p.id where p.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c'::uuid and p.isactive = true and pd.sellingprice>0 and p.published = true [2026-11-10 12:00:29] production.INFO: Prepare Cova Product Query: with _discount_conditions as ( select bb.* from ( select prd.*, pds.bgimg, pds.bgtheme, pds.bgcolor, pds.show_on_catalog, pds.show_on_stealsdeals, pds.bgorder,pds.name, case when prd.discount_method = 'PERCENT' and prd.discount_type = 'EachMatchedPercentOff' and prd.discount_amount < coalesce(pd_sub.sellingprice, 0) then round((coalesce(pd_sub.sellingprice, 0) - (coalesce(pd_sub.sellingprice, 0) * prd.discount_amount) / 100)::numeric, 2) when prd.discount_method = 'DOLLAR' and prd.discount_amount < coalesce(pd_sub.sellingprice, 0) then (coalesce(pd_sub.sellingprice, 0) - prd.discount_amount) else null end as discount_value, row_number() over ( partition by prd.prodid order by case when prd.discount_method = 'PERCENT' and prd.discount_type = 'EachMatchedPercentOff' and prd.discount_amount < coalesce(pd_sub.sellingprice, 0) then round((coalesce(pd_sub.sellingprice, 0) - (coalesce(pd_sub.sellingprice, 0) * prd.discount_amount) / 100)::numeric, 2) when prd.discount_method = 'DOLLAR' and prd.discount_amount < coalesce(pd_sub.sellingprice, 0) then (coalesce(pd_sub.sellingprice, 0) - prd.discount_amount) else null end asc ) as prdrow from ( with bundle_criteria as ( select d.id, d.discount_id, rule->>'criteria' as criteria, rule->'values' as values from cmp179.pos_discounts d cross join lateral jsonb_array_elements( case when jsonb_typeof(d.criteria_condition) = 'array' then d.criteria_condition else jsonb_build_array(d.criteria_condition) end ) obj cross join lateral jsonb_array_elements(obj->'sets') rule where d.discount_method = 'BUNDLE' and d.isactive = true and d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb ), bundle_products as ( select distinct bc.discount_id, p.id as product_id, p.puid as product_puid, p.dispensaryid, p.categoryid, p.brandid from bundle_criteria bc join cmp179.product p on ( (bc.criteria = 'category' and p.categoryid in ( select jsonb_array_elements_text(bc.values)::int )) or (bc.criteria = 'brand' and p.brandid in ( select jsonb_array_elements_text(bc.values)::int )) or (bc.criteria = 'product_ids' and p.puid::text in ( select jsonb_array_elements_text(bc.values) )) ) where p.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c' ), criteria_values as ( -- one row per (discount, category-value) for non-BUNDLE discounts select d.discount_id, d.criteria_operator, 'category'::text as criteria_type, (jsonb_array_elements_text(d.criteria_condition->'category'))::int as value from cmp179.pos_discounts d where d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and d.isactive = true and d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb and (d.criteria_condition->'category') is not null union all -- one row per (discount, brand-value) for non-BUNDLE discounts select d.discount_id, d.criteria_operator, 'brand'::text as criteria_type, (jsonb_array_elements_text(d.criteria_condition->'brand'))::int as value from cmp179.pos_discounts d where d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and d.isactive = true and d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb and (d.criteria_condition->'brand') is not null ), criteria_required_types as ( -- which criteria types (category/brand) each discount actually specifies select discount_id, criteria_operator, array_agg(distinct criteria_type) as required_types from criteria_values group by discount_id, criteria_operator ), criteria_product_matches as ( -- indexable equality match: product against each specified value, per type select distinct cv.discount_id, cv.criteria_type, p2.id as product_id from criteria_values cv join cmp179.product p2 on p2.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c' and ( (cv.criteria_type = 'category' and p2.categoryid = cv.value) or (cv.criteria_type = 'brand' and p2.brandid = cv.value) ) ), criteria_match_summary as ( -- which types matched, per (discount, product) select discount_id, product_id, array_agg(distinct criteria_type) as matched_types from criteria_product_matches group by discount_id, product_id ), narrowed_discount_ids as ( -- non-BUNDLE discounts that ALSO carry an explicit product list -- (e.g. a CatalogId sub-condition AND-ed alongside category/brand in -- the original promotion) — for these, pos_discount_products is a -- narrowing filter on top of the category/brand match, not the sole -- matching mechanism (that role is reserved for criteria_condition IS -- NULL discounts, handled separately via the ELSE p.prodid branch below) select distinct pos_discount_id as discount_id from cmp179.pos_discount_products ), discount_product_narrowing as ( select distinct pdp.pos_discount_id as discount_id, p2.id as product_id from cmp179.pos_discount_products pdp join cmp179.product p2 on p2.puid = pdp.product_id and p2.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c' ), criteria_matches as ( -- apply AND/OR semantics, then pull fresh product columns select cmt.discount_id, p2.id as product_id, p2.puid as product_puid, p2.categoryid, p2.brandid from criteria_match_summary cmt join criteria_required_types crt on crt.discount_id = cmt.discount_id join cmp179.product p2 on p2.id = cmt.product_id where ( crt.criteria_operator = 'OR' or (crt.criteria_operator = 'AND' and cmt.matched_types @> crt.required_types) ) and ( not exists ( select 1 from narrowed_discount_ids ndi where ndi.discount_id = cmt.discount_id ) or exists ( select 1 from discount_product_narrowing dpn where dpn.discount_id = cmt.discount_id and dpn.product_id = p2.id ) ) ) select d.id, d.discount_id, d.discount_title, d.discount_amount, d.discount_method, d.criteria_condition, d.discount_type, d.number_to_match, d.grams_per_match_unit, d.discount_stackable, d.discount_condition_detail, d.criteria_operator, d.other_criteria_operator, d.other_criteria_condition, CASE WHEN d.discount_method = 'BUNDLE' then bp.product_id WHEN d.criteria_condition IS NOT NULL THEN crm.product_id ELSE p.prodid END AS prodid, '6ea08aa9-20a0-4079-b0db-d4c48310260c' as dispensary_id, case when d.discount_method = 'BUNDLE' then bp.product_puid when d.criteria_condition is not null then crm.product_puid else p.product_id::uuid end as puid, d.createdon, d.createdby, d.updatedon, d.updatedby, d.isactive, d.isdelete, CASE WHEN d.discount_method = 'BUNDLE' then bp.categoryid WHEN d.criteria_condition IS NOT NULL THEN crm.categoryid ELSE null END AS categoryid, CASE WHEN d.discount_method = 'BUNDLE' then bp.brandid WHEN d.criteria_condition IS NOT NULL THEN crm.brandid ELSE null END AS brandid, d.createdon_pos from cmp179.pos_discounts d left join cmp179.pos_discount_products p on d.discount_id = p.pos_discount_id and d.criteria_condition is null left join criteria_matches crm on d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and crm.discount_id = d.discount_id left join bundle_products bp on d.discount_method = 'BUNDLE' AND bp.discount_id = d.discount_id where d.dispensary_id @> '["6ea08aa9-20a0-4079-b0db-d4c48310260c"]'::jsonb and d.isactive = true and ( ( d.discount_method = 'BUNDLE' and exists ( select 1 from jsonb_array_elements( case when jsonb_typeof(d.criteria_condition) = 'array' then d.criteria_condition else jsonb_build_array(d.criteria_condition) end ) obj where ( (obj->>'set_operator' = 'AND' and not exists ( select 1 from jsonb_array_elements(obj->'sets') s where not ( (s->>'criteria' = 'category' and bp.categoryid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'brand' and bp.brandid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'product_ids' and bp.product_puid::text in ( select jsonb_array_elements_text(s->'values') )) ) ) ) or (obj->>'set_operator' = 'OR' and exists ( select 1 from jsonb_array_elements(obj->'sets') s where (s->>'criteria' = 'category' and bp.categoryid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'brand' and bp.brandid in ( select jsonb_array_elements_text(s->'values')::int )) or (s->>'criteria' = 'product_ids' and bp.product_puid::text in ( select jsonb_array_elements_text(s->'values') )) ) ) ) ) ) or ( d.discount_method <> 'BUNDLE' and d.criteria_condition is not null and crm.product_id is not null ) or d.criteria_condition is null ) ) prd inner join cmp179.productdetails pd_sub on prd.prodid = pd_sub.productid and prd.dispensary_id = pd_sub.dispensaryid::text and pd_sub.isdefault = 1 left join cmp179.pos_discount_status pds on prd.discount_id = pds.discount_id and pds.dispensaryid = prd.dispensary_id left join cmp179.pos_discount_conditions pdc on pdc.discount_id = prd.id and pdc.isactive = true where coalesce(pds.isactive, true) = true and prd.dispensary_id = '6ea08aa9-20a0-4079-b0db-d4c48310260c' and prd.isactive = true and ( prd.discount_method not in ('PERCENT', 'DOLLAR') or prd.discount_amount < coalesce(pd_sub.sellingprice, 0) ) and case when pdc.discount_id is null then true else ((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone)::date between pdc.start_date and coalesce(pdc.end_date, ((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone)::date) end and case when pdc.discount_id is null then true else (case when (pdc.start_time is not null or pdc.end_time is not null) then ((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone)::time between pdc.start_time and pdc.end_time else true end) end and case when (pdc.discount_id is null) then true when (pdc.read_condition = 'REPEAT_INTERVAL_ON_DAYS') then ( position(TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'DY') in pdc.repeat_days) > 0 and (((current_timestamp at time zone 'America/Indiana/Indianapolis')::date - (pdc.start_date - extract(DOW from pdc.start_date)::int)::date) / 7) % pdc.repeat_interval = 0 ) when (pdc.read_condition = 'WEEKLY' or pdc.read_condition = 'DAILY') then position(TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'DY') in pdc.repeat_days) > 0 when (pdc.read_condition = 'WEEK_DAY') then TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'FMDay') = pdc.repeat_days when (pdc.read_condition = 'ANNUALLY_ON_MONTH_DAY') then (TRIM(leading '0' from TO_CHAR((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'FMMonth FMDD')) = pdc.repeat_days) when (pdc.read_condition = 'MONTHLY_ON_NUMERIC_DAY') then extract(day from (current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone) = pdc.repeat_days::int when (pdc.read_condition = 'MONTHLY_ON_STRING_DAY') then concat( case to_char(current_timestamp, 'FMW')::int when 1 then 'first' when 2 then 'second' when 3 then 'third' when 4 then 'fourth' when 5 then 'fifth' end, ' ', to_char((current_timestamp at time zone 'America/Indiana/Indianapolis')::timestamp without time zone, 'FMDay') ) = pdc.repeat_days when (pdc.read_condition = 'DO_NO_REPEAT') then true else false end order by discount_value asc) bb where (bb.prdrow = 1 or bb.prdrow is null) )select p.custdiscountid, p.id, p.seolabel, p.name, p.isnew, p.dispensaryid, p.isactive, p.published, p.shortdesc, p.tags, p.new_arrival, p.hidfrmscren, pd.price_type, pd.sellingprice, pd.id as proddetailid, pd.img1, round(pd.totalmgcbd::numeric, 0) as totalmgcbd, round(pd.totalmgthc::numeric, 0) as totalmgthc, pd.tier_method, pd.effects_icon, pd.variantname, ct.name as catname, ct.poscategoryid as poscategoryid, (ct.catorder)::int as catorder, b.label as brandlabel, b.name as brandname, tpd.discount_method as discount_method, tpd.discount_amount as discount_amount, coalesce(tpd.name,tpd.discount_title) as discount_title, tpd.prdrow as tpdrow, tpd.prodid as prodid, tpd.bgimg, tpd.bgtheme, tpd.bgcolor, 0 as min_price, 0 as max_price, 0 as weight, c.name as classname, c.remark as classcolor, u.name as uom, u.code as uomcode, (coalesce(prodrating->>'rating', '0')::int / coalesce(prodrating->>'custcount', '1')::int) as prodrating, coalesce(pd.sellingprice, 0) as price, coalesce(pd.price, 0) as regular_price, lr.lab_results, pd.effects, ct.hide_from_mobile, round(pd.weight_amount, 2) as weight_amount, pd.sku, p.puid, null as tier_prices, p.createdon, sc.name as subcatname, tpd.show_on_catalog, tpd.show_on_stealsdeals, c.name as classcode , ct.show_labresults , p.metatitle , pd.total_flower_weight_g , tpd.discount_condition_detail as discount_condition_detail , pd.img2 , pd.img3, (ct.productorder)::jsonb as productorder, p.categoryid, tpd.bgorder as bogoorder, tpd.discount_value, p.periodicsales, pd.attr_general, pd.flavors, pd.ingredients, pd.aroma, lr.terpenes, p.is_medical from cmp179.product p inner join cmp179.productdetails pd on p.id = pd.productid and p.dispensaryid = pd.dispensaryid and pd.isdefault = 1 inner join cmp179.category ct on ct.id = p.categoryid and p.dispensaryid = ct.dispensaryid left join cmp179.subcategory sc on sc.id = p.subcatid and p.dispensaryid = sc.dispensaryid left join cmp179.brand b on b.id = p.brandid and p.dispensaryid = b.dispensaryid left join _discount_conditions tpd on (tpd.prodid=p.id) left join cmp179.mom c on c.id = p.typeid and p.dispensaryid = c.dispensaryid left join cmp179.mom u on u.id = p.uom::int and p.dispensaryid = u.dispensaryid left join lateral( with mapped as ( select plr.product_id, plr.minimum_value, plr.maximum_value, COALESCE(NULLIF(lrc.uom, ''),plr.amount_type) AS amount_type, lrc.cannabinoids, lrc.terpene, coalesce(nullif(lrc.field_code, ''), lrc.display_label, plr.content_type) as mapped_label, nullif(lrc.field_code, '') as field_code, lrc.name as original_name from cmp179.product_lab_results plr inner join cmp179.lab_results_content lrc on lower(plr.content_type) = lower(lrc.name) and lrc.category = ct.treez_name and ct.isactive = true and lrc.isactive = true and lrc.uom = plr.amount_type where plr.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c'::uuid and plr.product_id = p.id ), dedup as ( select m.* from mapped m where not exists ( select 1 from mapped x where x.field_code = m.original_name and m.field_code is null ) ) select product_id, jsonb_agg( jsonb_build_object( 'content_type', mapped_label, 'minimum_value', round(minimum_value, 2), 'maximum_value', round(maximum_value, 2), 'amount_type', amount_type, 'cannabinoids', cannabinoids, 'terpene', terpene ) ) as lab_results, case when bool_or(terpene = true) then jsonb_build_object( 'terpene', true, 'content_type', 'Total Terpene', 'minimum_value', round(sum(minimum_value) filter (where terpene = true), 2), 'maximum_value', round(sum(maximum_value) filter (where terpene = true), 2), 'amount_type', max(amount_type) filter (where terpene = true) ) else null end as terpenes from dedup group by product_id ) lr on lr.product_id = p.id where p.dispensaryid = '6ea08aa9-20a0-4079-b0db-d4c48310260c'::uuid and p.isactive = true and pd.sellingprice>0 and p.published = true [2026-11-10 12:03:09] production.INFO: params{"dispensary":"6ea08aa9-20a0-4079-b0db-d4c48310260c","cmp":179} [2026-11-10 12:40:31] production.INFO: params{"dispensary":"6ea08aa9-20a0-4079-b0db-d4c48310260c","cmp":179}