0 ) s WHERE s.rn = 1; -- WC native order attribution feed. Fills order_attribution for every converted -- order not already captured above, pivoting the WooCommerce core -- _wc_order_attribution_* meta (HPOS table wp_wc_orders_meta) into our columns. -- INSERT IGNORE keeps the AIEO/PYS rows already present (order_id is the key), -- so this only adds the orders the session path never linked -- complete back to -- the first WooCommerce-attribution order. channel is derived from source_type + -- the utm medium/source (paid_* when the medium looks paid). -- `channel` stays the RAW value the campaign URL produced -- it is the only -- thing that can reveal a silent retagging. `channel_group` is the canonical -- vocabulary reports group by; its CASE is generated from -- AIEO_Attribution::canonical_channel_sql() and injected at install time so -- the proc and the live PHP resolver can never disagree. INSERT IGNORE INTO wp_aieo_order_attribution (order_id, channel, channel_group, utm_source, utm_medium, utm_campaign, utm_term, utm_content, origin, updated_at) SELECT p.order_id, CASE WHEN p.source_type = 'utm' AND LOWER(COALESCE(p.utm_medium,'')) REGEXP 'cpc|ppc|paid|display|cpm' AND COALESCE(p.utm_source,'') <> '' THEN CONCAT('paid_', LOWER(p.utm_source)) WHEN p.source_type = 'utm' AND COALESCE(p.utm_source,'') <> '' THEN LOWER(p.utm_source) WHEN p.source_type = 'organic' THEN 'organic' WHEN p.source_type = 'typein' THEN 'direct' WHEN p.source_type = 'referral' THEN 'referral' WHEN p.source_type = 'admin' THEN 'admin' ELSE NULLIF(p.source_type,'') END AS channel, /*AIEO_CHANNEL_GROUP_CASE*/ AS channel_group, NULLIF(p.utm_source,''), NULLIF(p.utm_medium,''), NULLIF(p.utm_campaign,''), NULLIF(p.utm_term,''), NULLIF(p.utm_content,''), 'wc_native', UTC_TIMESTAMP() FROM ( SELECT order_id, MAX(CASE WHEN meta_key = '_wc_order_attribution_source_type' THEN meta_value END) AS source_type, MAX(CASE WHEN meta_key = '_wc_order_attribution_utm_source' THEN meta_value END) AS utm_source, MAX(CASE WHEN meta_key = '_wc_order_attribution_utm_medium' THEN meta_value END) AS utm_medium, MAX(CASE WHEN meta_key = '_wc_order_attribution_utm_campaign' THEN meta_value END) AS utm_campaign, MAX(CASE WHEN meta_key = '_wc_order_attribution_utm_term' THEN meta_value END) AS utm_term, MAX(CASE WHEN meta_key = '_wc_order_attribution_utm_content' THEN meta_value END) AS utm_content, MAX(CASE WHEN meta_key = '_wc_order_attribution_session_entry' THEN meta_value END) AS session_entry FROM wp_wc_orders_meta WHERE meta_key LIKE '_wc_order_attribution_%' GROUP BY order_id ) p WHERE p.order_id > 0 AND (p.utm_source <> '' OR p.utm_medium <> '' OR p.utm_campaign <> '' OR p.utm_term <> '' OR p.utm_content <> '' OR p.source_type <> ''); truncate table wp_aieo_order_stats; INSERT INTO wp_aieo_order_stats (`name`, `orderid`, `customeremail`, `customerid`, `birthyear`, `birthday`, `firstname`, `lastname`, `city`, `postcode`, `numberofitemsperorder`, `numberoforders`, `orderspend`, `shippingcharged`, `shippingtype`, `freeshipping`, `totalshipping`, `netspendonproducts`, `orderdate`, `orderdatetime`, `orderday`, `orderdaymonth`, `buyerspayrol`, `buyersday`, `buyerstime`, `buyersdevice`, `link_to_order_details`, `paymenttype`, `pickuplocation`, `phoneorder`, `orderstatus`, `ordersource`, `orderutmsource`, `orderutmmedium`, `orderutmcampaign`, `orderutmterm`, `orderutmcontent`) SELECT CONCAT(woa.first_name, ' ', woa.last_name, ' - ', wor.id) as name, wor.id as orderid, wor.billing_email as customeremail, wos.customer_id as customerid, (Select left(meta_value, 4) FROM wp_usermeta where meta_key like '%_automatewoo_birthday_full%' and user_id=wos.customer_id) as birthyear, (Select concat (SUBSTRING_INDEX(meta_value,'-', -1),'/', SUBSTRING_INDEX(right(meta_value,5),'-', 1),'/', left(meta_value, 4) ) FROM wp_usermeta where meta_key like '%_automatewoo_birthday_full%' and user_id=wos.customer_id) as birthday, woa.first_name as firstname, woa.last_name as lastname, woa.city as city, woa.postcode as postcode, count(distinct(order_item_id)) as numberofitemsperorder, (SELECT count(distinct(nord.id)) as numberoforders FROM `wp_wc_orders` nord where wor.billing_email=nord.billing_email and (nord.billing_email is not null or nord.billing_email <>'') group by nord.billing_email) as numberoforders, (wor.total_amount) as orderspend, wood.shipping_total_amount as shippingcharged, (SELECT order_item_name FROM wp_woocommerce_order_items where order_item_type='shipping' and order_id=wor.id LIMIT 1) as shippingtype, case when wood.shipping_total_amount=0 THEN 'ΝΑΙ' ELSE 'ΟΧΙ' END as freeSsipping, CASE WHEN wor.payment_method_title='Αντικαταβολή' THEN wood.shipping_total_amount+1.5 ELSE wood.shipping_total_amount END AS totalshipping, (wor.total_amount) - CASE WHEN wor.payment_method_title='Αντικαταβολή' THEN wood.shipping_total_amount+1.5 ELSE wood.shipping_total_amount END as netspendonproducts, DATE(wos.date_created) As orderdate, CAST(COALESCE(wos.date_created, '1970-01-01 00:00:00') AS DATETIME) AS orderdatetime, /* concat (from_unixtime(wos.date_created, '%d'), '/', from_unixtime(wos.date_created, '%m'), '/', from_unixtime(wos.date_created, '%Y')) as OrderDate, from_unixtime(wos.date_created, '%d %m %Y %H:%i:%s') as OrderDateTime, */ dayofweek(wos.date_created) as orderday, dayofmonth(wos.date_created) as orderdaymonth, CASE WHEN dayofmonth(wos.date_created) > 0 and dayofmonth(wos.date_created) < 11 THEN 'Conservative' WHEN dayofmonth(wos.date_created) > 10 and dayofmonth(wos.date_created) < 21 THEN 'Sound' WHEN dayofmonth(wos.date_created) > 20 and dayofmonth(wos.date_created) < 28 THEN 'Optimist' ELSE 'End of Month' END AS buyerspayrol, CASE WHEN dayofweek(wos.date_created) =1 or dayofweek(wos.date_created) =7 THEN 'Weekend' WHEN dayofweek(wos.date_created) >1 and dayofweek(wos.date_created) <5 THEN 'Beginning of Week' WHEN dayofweek(wos.date_created) >4 and dayofweek(wos.date_created) <6 THEN 'Mid Week' ELSE 'Friday' END as buyersday, CASE WHEN hour(wos.date_created) > 4 and hour(wos.date_created) < 11 THEN 'Early Bird' WHEN hour(wos.date_created) > 10 and hour(wos.date_created) < 14 THEN 'Morning' WHEN hour(wos.date_created) > 13 and hour(wos.date_created) < 21 THEN 'Afternoon' ELSE 'Night Bird' END AS buyerstime, CASE WHEN LENGTH(wor.user_agent) > 0 THEN LEFT(substr(wor.user_agent,instr(wor.user_agent,"(") + 1, instr(wor.user_agent,")")),LOCATE(';',substr(wor.user_agent,instr(wor.user_agent,"(") + 1, instr(wor.user_agent,")"))) - 1) ELSE 'Uknown' END as buyersdevice, Concat('View Order Details') as link_to_order_details, wor.payment_method_title as paymenttype, COALESCE( -- New canonical per-order pickup field (AIEO Local Pickup). (SELECT meta_value FROM wp_wc_orders_meta WHERE order_id=wor.id AND meta_key='_aieo_pickup_location' LIMIT 1), -- WC Blocks pickup_location item meta (the store name). (SELECT oim.meta_value FROM wp_woocommerce_order_items oi INNER JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id=oi.order_item_id WHERE oi.order_id=wor.id AND oim.meta_key='pickup_location' LIMIT 1), -- Legacy pickup plugin item meta. (SELECT oim.meta_value FROM wp_woocommerce_order_items oi INNER JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id=oi.order_item_id WHERE oi.order_id=wor.id AND oim.meta_key='_pickup_location_name' LIMIT 1), -- Fallback: the pickup shipping line's own name: a local pickup method (11.3.8), or a line named Παραλαβή. (SELECT oi.order_item_name FROM wp_woocommerce_order_items oi LEFT JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id=oi.order_item_id AND oim.meta_key='method_id' WHERE oi.order_id=wor.id AND oi.order_item_type='shipping' AND (oim.meta_value IN ('local_pickup','pickup_location') OR oi.order_item_name LIKE '%Παραλαβή%') LIMIT 1), 'Χώρος Πελάτη' ) as pickuplocation, CASE WHEN (SELECT meta_value FROM wp_wc_orders pwo INNER JOIN wp_wc_orders_meta pwom ON pwom.order_id = pwo.id AND pwom.order_id=wor.id AND pwom.meta_key = '_phone_order' limit 1 ) ='yes' then 'NAI' else 'OXI' end as phoneorder, right(wos.status,(length(wos.status)-3)) as OrderStatus, -- Marketing attribution: AIEO-owned per-order attribution (replaces the -- PixelYourSite wp_pys_stat_* join). `channel` = resolved source; the five -- standard UTM params are separate columns. Historical rows were snapshotted -- from PYS (origin 'pys'); new orders come from AIEO sessions (origin 'aieo'). -- Phone / admin (staff-placed) orders have no web session, so their attribution -- is empty: label them "Phone Orders" (instead of "(none)" / a bare "admin") so -- the ROI + UTM reports attribute them. pom/aom = the phone-order + WC admin -- attribution flags joined below. -- 🚨 channel_group, NOT channel. wp_aieo_order_stats is the REPORTING -- projection -- the ROI Wizard, the UTM Explorer and the Order-patterns report -- all read ordersource from here -- so this is where the canonical vocabulary -- has to land. Grouping on the raw channel is what split one Meta account -- across paid_meta / paid_fb / paid_ig / fb / ig and filed 326 paid orders as -- free; the ROI Wizard had to carry a hand-maintained alias map to survive it. -- COALESCE keeps a row that predates the column from vanishing from the report. CASE WHEN pom.meta_value = 'yes' OR aom.meta_value = 'admin' THEN 'Phone Orders' WHEN COALESCE(attr.channel_group, attr.channel) = 'internal' THEN 'direct' ELSE COALESCE(attr.channel_group, attr.channel) END as ordersource, -- Source (utm_source): when there is no real UTM source (organic search, -- gclid-only paid, internal, direct…) fall back to the RESOLVED CHANNEL so the -- Source breakdown shows the true origin instead of "(none)". Phone/admin first. CASE WHEN pom.meta_value = 'yes' OR aom.meta_value = 'admin' THEN 'Phone Orders' -- Paid traffic: label by its PAID CHANNEL (paid_google / paid_facebook / …) -- even when the raw utm_source is generic — Google Ads auto-tagging and -- organic search BOTH send utm_source=google, so without this paid clicks hide -- inside the organic 'google'/'fb' Source buckets. Now that the channel is -- canonical, `paid_%` is a closed set of five rather than whatever the media -- buyer typed after 'paid_'. WHEN COALESCE(attr.channel_group, attr.channel) LIKE 'paid\_%' THEN COALESCE(attr.channel_group, attr.channel) -- Self-referral ('internal') and no-referrer ('direct') with no real UTM source -- both fold into the standard "(direct)" bucket — matching WooCommerce and -- avoiding a confusing standalone "internal"/"direct" split in the Source view. WHEN COALESCE(attr.utm_source, '') = '' AND COALESCE(attr.channel_group, attr.channel) IN ('internal','direct') THEN '(direct)' WHEN COALESCE(attr.utm_source, '') = '' AND COALESCE(attr.channel_group, attr.channel, '') <> '' THEN COALESCE(attr.channel_group, attr.channel) ELSE attr.utm_source END as orderutmsource, attr.utm_medium as orderutmmedium, -- Campaign / term / content are DECODED here (the '+' a query string uses for a -- space), so one campaign is one row. The expression is generated from -- AIEO_Attribution::canonical_utm_value_sql() and injected at install time, the -- same way the channel CASE is, so the report and the PHP path cannot drift. -- The raw values stay untouched in wp_aieo_order_attribution. /*AIEO_UTM_DECODE_CAMPAIGN*/ as orderutmcampaign, /*AIEO_UTM_DECODE_TERM*/ as orderutmterm, /*AIEO_UTM_DECODE_CONTENT*/ as orderutmcontent FROM `wp_wc_orders` wor join `wp_wc_order_addresses` woa on woa.order_id=wor.id join `wp_wc_order_operational_data` wood on wor.id=wood.order_id join `wp_wc_order_stats` wos on wor.id=wos.order_id join `wp_woocommerce_order_items` woi on woi.order_id=wor.id and (order_item_type='line_item' or order_item_type='shipping') -- Phantom row-multiplier removed: `join wp_wc_orders_meta wom` had its meta_key filter -- commented out and no wom.* column is referenced anywhere in this SELECT, so it only -- duplicated every pre-GROUP row once per meta row of the order (~x20-40) and re-ran -- the SELECT-list correlated subqueries on each duplicate. The sole aggregate here is -- COUNT(DISTINCT order_item_id), which is duplication-proof, so dropping the join is -- output-identical. The phone-order flag it once targeted is served by `pom` below. left join `wp_aieo_order_attribution` attr on attr.order_id = wor.id left join `wp_wc_orders_meta` pom on pom.order_id = wor.id and pom.meta_key = '_phone_order' left join `wp_wc_orders_meta` aom on aom.order_id = wor.id and aom.meta_key = '_wc_order_attribution_source_type' where wor.id > 0 and wos.date_created is not null and wor.total_amount > 0 and (wor.status is null or wor.status not in ('wc-checkout-draft','auto-draft','trash')) -- and (Select STR_TO_DATE(meta_value, '%Y-%m-%d') FROM wp_usermeta where meta_key like '%_automatewoo_birthday_full%' and user_id=wos.customer_id) is not null group by wor.id order by wor.id desc LIMIT 500000; -- ALTER TABLE wp_aieo_order_stats ADD COLUMN statsclientid DECIMAL(12,0) NULL; CREATE TEMPORARY TABLE temp_customer_ids AS SELECT customeremail, @rn := @rn + 1 AS statsclientid FROM ( SELECT DISTINCT customeremail FROM wp_aieo_order_stats WHERE customeremail IS NOT NULL AND customeremail <> '' ORDER BY customeremail ) AS sub CROSS JOIN (SELECT @rn := 0) AS vars; ALTER TABLE temp_customer_ids ADD INDEX idx_customeremail (customeremail); UPDATE wp_aieo_order_stats t JOIN temp_customer_ids tmp ON t.customeremail = tmp.customeremail SET t.statsclientid = tmp.statsclientid; DROP TEMPORARY TABLE temp_customer_ids; UPDATE wp_aieo_order_stats SET buyerspayrol='Unknown', buyersday='Unknown', buyerstime='Unknown', buyersdevice='Unknown' where orderID < 49338; AIEO_JOB_SQL; $b['cum_order_stats'] = <<<'AIEO_JOB_SQL' drop table if exists wp_aieo_cumulative_order_stats_new; drop table if exists wp_aieo_cumulative_order_stats_old; -- Fresh stats on the just-rebuilt order_stats before we read it (ANALYZE-after-rebuild). ANALYZE TABLE wp_aieo_order_stats; -- Per-customer spend/items aggregates computed ONCE. This replaces a customeremail -- self-join (ws1 JOIN ws2 ON ws1.customeremail = ws2.customeremail) that was -- per-customer quadratic — the ~2.8k empty-email orders alone formed one ~8M-row -- group. Value-identical: order_stats is exactly one row per order, NULL-email orders -- are dropped by both the old INNER self-join and this INNER join, and ''-email orders -- are lumped into one group by both. DROP TEMPORARY TABLE IF EXISTS _cum_cust; CREATE TEMPORARY TABLE _cum_cust AS SELECT customeremail, AVG(orderspend) AS avg_spend, STD(orderspend) AS std_spend, MIN(orderspend) AS min_spend, MAX(orderspend) AS max_spend, AVG(numberofitemsperorder) AS avg_items, STD(numberofitemsperorder) AS std_items, MIN(numberofitemsperorder) AS min_items, MAX(numberofitemsperorder) AS max_items FROM wp_aieo_order_stats GROUP BY customeremail; ALTER TABLE _cum_cust ADD KEY k (customeremail); create table wp_aieo_cumulative_order_stats_new (INDEX order_id_idx (OrderID, CustomerEmail)) SELECT ws1.orderid as OrderID, ws1.customeremail as CustomerEmail, ws1.statsclientid as StatsClientID, ws1.postcode as Postcode, ws1.orderspend as OrderSpend, ws1.numberofitemsperorder as NumberOfItemsPerOrder, Round(a.avg_spend,2) as TotalAverageOrder, Round(ws1.orderspend/(ws1.numberofitemsperorder),2) as AveSpendOfUserPerItemPerOrder, ws1.numberoforders as TotalNumberofOrders, Round(a.avg_items,2) as AveItemsOfUserPerOrder, Round(a.std_items,2) as STDItemsOfUser, Round( a.std_items /a.min_items ,2) as STDPerMinItemsOfUser, Round( a.std_items /a.avg_items ,2) as STDPerAverageItemsOfUser, Round( a.std_items /a.max_items ,2) as STDPerMaxItemsOfUser, Round(a.avg_spend,2) as AveSpendOfUserPerOrder, Round(a.std_spend,2) as STDSpendOfUser, Round( a.std_spend /a.min_spend ,2) as STDPerMinSpendOfUser, Round( a.std_spend /a.avg_spend ,2) as STDPerAverageSpendOfUser, Round( a.std_spend /a.max_spend ,2) as STDPerMaxSpendOfUser, ws1.buyerspayrol as BuyersPayrol, ws1.buyersday as BuyersDay, ws1.buyerstime as BuyersTime, ws1.link_to_order_details as link_to_order_details FROM wp_aieo_order_stats ws1 JOIN _cum_cust a on a.customeremail = ws1.customeremail Order by ws1.customeremail; DROP TEMPORARY TABLE IF EXISTS _cum_cust; ALTER TABLE wp_aieo_cumulative_order_stats_new ADD COLUMN `id` BIGINT(45) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`); -- The swap. `CREATE TABLE IF NOT EXISTS ... LIKE` makes the first-ever run -- work: RENAME needs both names to exist, and on a fresh install the live one -- does not yet. After that this is a no-op. CREATE TABLE IF NOT EXISTS wp_aieo_cumulative_order_stats LIKE wp_aieo_cumulative_order_stats_new; RENAME TABLE wp_aieo_cumulative_order_stats TO wp_aieo_cumulative_order_stats_old, wp_aieo_cumulative_order_stats_new TO wp_aieo_cumulative_order_stats; DROP TABLE IF EXISTS wp_aieo_cumulative_order_stats_old; AIEO_JOB_SQL; $b['order_products_analytics'] = <<<'AIEO_JOB_SQL' TRUNCATE TABLE wp_aieo_order_products_analytics; INSERT INTO wp_aieo_order_products_analytics (`name`,`order_id`,`product_id`,`variation_id`,`sku`,`quantity`,`price`,`unit_price`, `order_item_id`,`order_sequence`,`order_items`,`order_item_name`,`order_item_parent_name`, `order_total`,`customer_id`,`customer_email`,`date_created_gmt`,`payment_method`, `user_agent`,`brand`,`category_id`,`menu_category`,`head_category`,`sub_category`, `prof_discount`,`list_price`,`soft1_id`,`parent_product_id`,`order_item_parent_product_id`,`stock`) WITH line_items AS ( SELECT oi.order_item_id, oi.order_id, oi.order_item_name, MAX(CASE WHEN oim.meta_key = '_product_id' THEN CAST(oim.meta_value AS UNSIGNED) END) AS parent_product_id, MAX(CASE WHEN oim.meta_key = '_variation_id' THEN CAST(oim.meta_value AS UNSIGNED) END) AS variation_id, MAX(CASE WHEN oim.meta_key = '_qty' THEN CAST(oim.meta_value AS DECIMAL(10,3)) END) AS qty, MAX(CASE WHEN oim.meta_key = '_line_subtotal' THEN CAST(oim.meta_value AS DECIMAL(20,6)) END) AS line_subtotal FROM wp_woocommerce_order_items oi JOIN wp_woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id WHERE oi.order_item_type = 'line_item' GROUP BY oi.order_item_id, oi.order_id, oi.order_item_name ), li AS ( SELECT li.*, CASE WHEN COALESCE(li.variation_id,0) > 0 THEN li.variation_id ELSE li.parent_product_id END AS product_id_to_join FROM line_items li ) SELECT COALESCE(pv.name, li.order_item_name) AS `name`, li.order_id AS order_id, li.parent_product_id AS product_id, -- parent (WC _product_id) NULLIF(li.variation_id,0) AS variation_id, -- WC _variation_id (NULL if simple) pv.sku AS sku, -- variation SKU when available CAST(li.qty AS DECIMAL(10,3)) AS quantity, CAST(li.line_subtotal AS DECIMAL(12,2)) AS price, CAST(li.line_subtotal / NULLIF(li.qty,0) AS DECIMAL(12,2)) AS unit_price, li.order_item_id AS order_item_id, ROW_NUMBER() OVER (PARTITION BY li.order_id ORDER BY li.order_item_id) AS order_sequence, COUNT(*) OVER (PARTITION BY li.order_id) AS order_items, li.order_item_name AS order_item_name, pv.parent_name AS order_item_parent_name, CAST(o.total_amount AS DECIMAL(12,2)) AS order_total, o.customer_id, o.billing_email AS customer_email, CAST(COALESCE(o.date_created_gmt,'1970-01-01 00:00:00') AS DATETIME) AS date_created_gmt, o.payment_method, CASE WHEN LENGTH(o.user_agent) > 0 THEN LEFT(SUBSTR(o.user_agent, INSTR(o.user_agent,'(')+1, INSTR(o.user_agent,')')), LOCATE(';', SUBSTR(o.user_agent, INSTR(o.user_agent,'(')+1, INSTR(o.user_agent,')'))) - 1) ELSE 'Unknown' END AS user_agent, pv.brand, pv.category_id, pv.menu_category, pv.head_category, pv.sub_category, pv.profdiscount AS prof_discount, pv.price AS list_price, pv.soft1_id AS soft1_id, pv.parent_id AS parent_product_id, pv.parent_id AS order_item_parent_product_id, pv.stock AS stock FROM li JOIN wp_wc_orders o ON o.id = li.order_id LEFT JOIN wp_aieo_core_product_vitals pv ON pv.product_id = li.product_id_to_join -- AIEO vitals (replaces wp_pods_product_vitals); variation first, else parent WHERE li.order_id > 49290 ORDER BY li.order_id DESC; -- Refresh stats after the full TRUNCATE+INSERT rebuild (also populates the -- effective_pid STORED column) so every downstream job that joins this table on -- effective_pid / product_id gets an indexed nested-loop, not a stale-stats -- full×full BNL — the systemic pipeline-swap root cause. ANALYZE TABLE wp_aieo_order_products_analytics; AIEO_JOB_SQL; $b['order_products_behaviours'] = <<<'AIEO_JOB_SQL' -- Drop existing table if it exists DROP TABLE IF EXISTS wp_aieo_order_products_entry; -- Drop existing table if it exists DROP TABLE IF EXISTS wp_aieo_order_products_exit; -- Drop existing table if it exists DROP TABLE IF EXISTS wp_aieo_order_products_single_orders; -- Create the table using an optimized query CREATE TABLE wp_aieo_order_products_entry AS SELECT a1.id, a1.order_id, a1.order_item_name, a1.order_item_parent_name, a1.customer_id, a1.customer_email, a1.date_created_gmt, a1.product_id, a1.SKU, a1.brand, a1.order_sequence, o.number_of_products, CASE WHEN o.number_of_products = 1 THEN 'Single Product Order' ELSE 'Multiple Products Order' END AS order_singularity, a1.menu_category, a1.head_category, a1.sub_category, a1.stock FROM wp_aieo_order_products_analytics a1 JOIN ( SELECT order_id, MIN(order_sequence) AS min_order_sequence, COUNT(*) AS number_of_products FROM wp_aieo_order_products_analytics GROUP BY order_id ) o ON a1.order_id = o.order_id WHERE a1.order_sequence = o.min_order_sequence; -- ORDER BY a1.product_id; -- Optional, remove if not needed -- Add index to the new table ALTER TABLE wp_aieo_order_products_entry ADD PRIMARY KEY (id), ADD INDEX order_ep_idx (order_id, product_id, customer_email); -- Create the table using an optimized query CREATE TABLE wp_aieo_order_products_exit AS SELECT a1.id, a1.order_id, a1.order_item_name, a1.order_item_parent_name, a1.customer_id, a1.customer_email, a1.date_created_gmt, a1.product_id, a1.SKU, a1.brand, a1.order_sequence, o.number_of_products, CASE WHEN o.number_of_products = 1 THEN 'Single Product Order' ELSE 'Multiple Products Order' END AS order_singularity, a1.menu_category, a1.head_category, a1.sub_category, a1.stock FROM wp_aieo_order_products_analytics a1 JOIN ( SELECT order_id, MAX(order_sequence) AS max_order_sequence, COUNT(*) AS number_of_products FROM wp_aieo_order_products_analytics GROUP BY order_id ) o ON a1.order_id = o.order_id WHERE a1.order_sequence = o.max_order_sequence; -- ORDER BY a1.product_id; -- Optional, remove if not needed -- Add index to the new table ALTER TABLE wp_aieo_order_products_exit ADD PRIMARY KEY (id), ADD INDEX order_exp_idx (order_id, product_id, customer_email); -- Create the table using an optimized query CREATE TABLE wp_aieo_order_products_single_orders AS SELECT a1.id, a1.order_id, a1.order_item_name, a1.order_item_parent_name, a1.customer_id, a1.customer_email, a1.date_created_gmt, a1.product_id, a1.SKU, a1.brand, a1.order_sequence, o.number_of_products, 'Single Product Order' AS order_singularity, a1.menu_category, a1.head_category, a1.sub_category, a1.stock FROM wp_aieo_order_products_analytics a1 JOIN ( SELECT order_id, COUNT(*) AS number_of_products, MAX(order_sequence) AS order_sequence FROM wp_aieo_order_products_analytics GROUP BY order_id HAVING COUNT(*) = 1 ) o ON a1.order_id = o.order_id WHERE a1.order_sequence = o.order_sequence; -- Add index to the new table ALTER TABLE wp_aieo_order_products_single_orders ADD PRIMARY KEY (id), ADD INDEX order_sp_idx (order_id, product_id, customer_email); AIEO_JOB_SQL; $b['cart_frequencies'] = <<<'AIEO_JOB_SQL' -- Drop existing tables if they exist DROP TABLE IF EXISTS wp_aieo_order_products_freq_entry; DROP TABLE IF EXISTS wp_aieo_order_products_freq_exit; DROP TABLE IF EXISTS wp_aieo_order_products_freq_single_item_orders; -- Create wp_aieo_order_products_freq_entry CREATE TABLE wp_aieo_order_products_freq_entry AS SELECT product_id, MAX(SKU) AS SKU, MAX(order_item_parent_name) AS order_item_name, COUNT(*) AS Frequency, MAX(menu_category) AS menu_category, MAX(head_category) AS head_category, MAX(sub_category) AS sub_category, MAX(stock) AS stock, CASE WHEN MAX(sp.canonical_url) IS NOT NULL AND MAX(sp.canonical_url) <> '' THEN CONCAT('', 'Δείτε το προϊόν', '') ELSE CONCAT('', 'Δείτε το προϊόν', '') END AS link_to_view, CONCAT('', 'Δείτε τις αγορές', '') AS link_to_orders, CONCAT('', 'Δείτε linked αγορές', '') AS link_to_rev_orders FROM wp_aieo_order_products_entry LEFT JOIN wp_aieo_seo_posts sp ON sp.object_type = 'product' AND sp.object_id = product_id GROUP BY product_id ORDER BY Frequency DESC LIMIT 1000000; -- Add 'id' column and primary key, then index 'product_id' ALTER TABLE wp_aieo_order_products_freq_entry ADD COLUMN id BIGINT(45) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST, ADD INDEX product_freqent_idx (product_id); -- Create wp_aieo_order_products_freq_exit CREATE TABLE wp_aieo_order_products_freq_exit AS SELECT product_id, MAX(SKU) AS SKU, MAX(order_item_parent_name) AS order_item_name, COUNT(*) AS Frequency, MAX(menu_category) AS menu_category, MAX(head_category) AS head_category, MAX(sub_category) AS sub_category, MAX(stock) AS stock, CASE WHEN MAX(sp.canonical_url) IS NOT NULL AND MAX(sp.canonical_url) <> '' THEN CONCAT('', 'Δείτε το προϊόν', '') ELSE CONCAT('', 'Δείτε το προϊόν', '') END AS link_to_view, CONCAT('', 'Δείτε τις αγορές', '') AS link_to_orders, CONCAT('', 'Δείτε linked αγορές', '') AS link_to_rev_orders FROM wp_aieo_order_products_exit LEFT JOIN wp_aieo_seo_posts sp ON sp.object_type = 'product' AND sp.object_id = product_id GROUP BY product_id ORDER BY Frequency DESC LIMIT 1000000; -- Add 'id' column and primary key, then index 'product_id' ALTER TABLE wp_aieo_order_products_freq_exit ADD COLUMN id BIGINT(45) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST, ADD INDEX product_freqexit_idx (product_id); -- Create wp_aieo_order_products_freq_single_item_orders CREATE TABLE wp_aieo_order_products_freq_single_item_orders AS SELECT product_id, MAX(SKU) AS SKU, MAX(order_item_parent_name) AS order_item_name, COUNT(*) AS Frequency, MAX(menu_category) AS menu_category, MAX(head_category) AS head_category, MAX(sub_category) AS sub_category, MAX(stock) AS stock, CASE WHEN MAX(sp.canonical_url) IS NOT NULL AND MAX(sp.canonical_url) <> '' THEN CONCAT('', 'Δείτε το προϊόν', '') ELSE CONCAT('', 'Δείτε το προϊόν', '') END AS link_to_view, CONCAT('', 'Δείτε τις αγορές', '') AS link_to_orders, CONCAT('', 'Δείτε linked αγορές', '') AS link_to_rev_orders FROM wp_aieo_order_products_single_orders LEFT JOIN wp_aieo_seo_posts sp ON sp.object_type = 'product' AND sp.object_id = product_id GROUP BY product_id ORDER BY Frequency DESC LIMIT 1000000; -- Add 'id' column and primary key, then index 'product_id' ALTER TABLE wp_aieo_order_products_freq_single_item_orders ADD COLUMN id BIGINT(45) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST, ADD INDEX product_freqsingle_idx (product_id); AIEO_JOB_SQL; $b['invoice_match_prev_month'] = <<<'AIEO_JOB_SQL' /* ============================ MONTH + MATCHING PARAMETERS ============================ */ -- First day of the previous month (DATE) SET @report_month := DATE_SUB(DATE_SUB(UTC_DATE(), INTERVAL DAYOFMONTH(UTC_DATE())-1 DAY), INTERVAL 1 MONTH); -- Orders created within this range (upper bound exclusive) SET @from_date := @report_month; SET @to_date := LAST_DAY(@report_month) + INTERVAL 1 DAY; -- Forward-only invoice window after each order SET @invoice_max_lag_days := 10; -- Price tolerance (as ratios; 0.50 = 50%) SET @exact_price_tol := 0.50; -- tighten in prod if desired SET @probable_price_tol := 0.50; -- wider band for pickups -- SKU/Qty thresholds (ratios) SET @min_overlap_exact := 0.90; -- >= 90% of Woo SKUs present SET @min_qty_similarity_exact := 0.85; -- quantities ~equal SET @min_overlap_prob := 0.40; -- >= 40% of Woo SKUs present SET @min_qty_similarity_prob := 0.10; -- loose qty alignment for pickup -- Line-level closeness (per-SKU) SET @unit_price_tol := 0.10; -- 10% unit-price tolerance per SKU /* ============================ WOO ORDERS (MONTH HEADERS) ============================ */ DROP TEMPORARY TABLE IF EXISTS tmp_woo_orders_month; CREATE TEMPORARY TABLE tmp_woo_orders_month AS SELECT opa.order_id, MIN(os.customeremail) AS customer_email, MIN(os.orderdatetime) AS order_datetime, DATE(MIN(os.orderdatetime)) AS order_date, MIN(os.shippingtype) AS shippingtype, -- A pickup order, known by its shipping method, the name Παραλαβή kept as a fallback (11.3.8). /*AIEO_PICKUP_ORDER*/ AS is_pickup, MIN(os.orderstatus) AS order_status, -- Totals MAX(opa.order_total) AS woo_total, -- full Woo total (incl. shipping/fees) MIN(os.netspendonproducts) AS woo_net_total, -- authoritative products-only total SUM(COALESCE(opa.price, COALESCE(opa.unit_price,0)*COALESCE(opa.quantity,0), 0)) AS woo_items_total, -- fallback COUNT(*) AS woo_line_items, SUM(COALESCE(opa.quantity,0)) AS woo_qty_sum, GROUP_CONCAT(DISTINCT opa.sku ORDER BY opa.sku SEPARATOR ',') AS woo_sku_list, COUNT(DISTINCT opa.sku) AS woo_sku_count FROM wp_aieo_order_products_analytics opa JOIN wp_aieo_order_stats os ON os.orderid = opa.order_id WHERE opa.date_created_gmt >= @from_date AND opa.date_created_gmt < @to_date GROUP BY opa.order_id; CREATE INDEX ix_w_orders_id_date ON tmp_woo_orders_month(order_id, order_date); /* =========================================== INVOICE LINES (FORWARD WINDOW, LINE TOTALS) =========================================== */ DROP TEMPORARY TABLE IF EXISTS tmp_inv_month_lines; CREATE TEMPORARY TABLE tmp_inv_month_lines AS SELECT s.OrderId, s.OrderCode, s.Branch, s.OrderDate, s.SKU, CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3)) AS qty, CAST(REPLACE(COALESCE(s.Line_price, '0'), ',', '.') AS DECIMAL(12,3)) AS line_total, CASE WHEN CAST(REPLACE(COALESCE(s.Quantity,'0'), ',', '.') AS DECIMAL(12,3)) > 0 THEN CAST(REPLACE(COALESCE(s.Line_price,'0'), ',', '.') AS DECIMAL(12,3)) / CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3)) ELSE NULL END AS unit_price FROM ( -- Dedup the daily-re-appended ERP feed (same fix as tmp_praw in -- AIEO_GeneratePhysicalRecovery): the sync re-imports orders with a fresh -- ContentLastUpdated (edited/refunded state), so summing ALL rows -- double-counts their lines and inflates inv_total_est / inv_line_items / -- inv_qty_sum + the per-SKU qty/value aggregates below. Keep only each -- OrderId's LATEST import-day snapshot (single-pass window MAX; no -- self-join, so immune to the stale-stats plan flip). SELECT s.OrderId, s.OrderCode, s.Branch, s.OrderDate, s.SKU, s.Quantity, s.Line_price, COALESCE(DATE(s.ContentLastUpdated), DATE(s.OrderDate)) AS _row_import_day, MAX(COALESCE(DATE(s.ContentLastUpdated), DATE(s.OrderDate))) OVER (PARTITION BY s.OrderId) AS _max_import_day FROM wp_s1erp_temp_store_orders_table s -- forward-only window that can match any order in the month WHERE s.OrderDate >= @from_date AND s.OrderDate < (@to_date + INTERVAL @invoice_max_lag_days DAY) ) s WHERE s._row_import_day = s._max_import_day; CREATE INDEX ix_i_lines_orderdate ON tmp_inv_month_lines(OrderDate); CREATE INDEX ix_i_lines_id ON tmp_inv_month_lines(OrderId); CREATE INDEX ix_i_lines_sku ON tmp_inv_month_lines(SKU); DROP TEMPORARY TABLE IF EXISTS tmp_inv_month; CREATE TEMPORARY TABLE tmp_inv_month AS SELECT OrderId, MIN(OrderCode) AS OrderCode, MIN(Branch) AS Branch, MIN(OrderDate) AS OrderDate, SUM(line_total) AS inv_total_est, -- ERP product line totals (already aligned; no VAT uplift) COUNT(*) AS inv_line_items, SUM(qty) AS inv_qty_sum FROM tmp_inv_month_lines GROUP BY OrderId; CREATE INDEX ix_i_hdr_date_total ON tmp_inv_month(OrderDate, inv_total_est); CREATE INDEX ix_i_hdr_id ON tmp_inv_month(OrderId); -- Fresh stats on the just-built temp tables BEFORE the tmp_cand_exact / -- tmp_cand_prob OrderDate-range + total-tolerance joins below -- (ANALYZE-after-rebuild): freshly CTAS-built tables can sit at ~1-row -- InnoDB stats at proc time, flipping those joins into quadratic scans. ANALYZE TABLE tmp_woo_orders_month; ANALYZE TABLE tmp_inv_month_lines; ANALYZE TABLE tmp_inv_month; /* =========================================== PER-SKU AGGREGATES (qty, value, unit price) =========================================== */ DROP TEMPORARY TABLE IF EXISTS tmp_woo_skus_agg; CREATE TEMPORARY TABLE tmp_woo_skus_agg AS SELECT opa.order_id, opa.sku, SUM(COALESCE(opa.quantity,0)) AS qty, SUM(COALESCE(opa.price, COALESCE(opa.unit_price,0)*COALESCE(opa.quantity,0), 0)) AS value, CASE WHEN SUM(COALESCE(opa.quantity,0)) > 0 THEN SUM(COALESCE(opa.price, COALESCE(opa.unit_price,0)*COALESCE(opa.quantity,0), 0)) / SUM(COALESCE(opa.quantity,0)) ELSE NULL END AS unit_price FROM wp_aieo_order_products_analytics opa JOIN tmp_woo_orders_month w ON w.order_id = opa.order_id GROUP BY opa.order_id, opa.sku; CREATE INDEX ix_w_skus_agg ON tmp_woo_skus_agg(order_id, sku); DROP TEMPORARY TABLE IF EXISTS tmp_inv_skus_agg; CREATE TEMPORARY TABLE tmp_inv_skus_agg AS SELECT l.OrderId AS inv_id, l.SKU AS sku, SUM(l.qty) AS qty, SUM(l.line_total) AS value, CASE WHEN SUM(l.qty) > 0 THEN SUM(l.line_total) / SUM(l.qty) ELSE NULL END AS unit_price FROM tmp_inv_month_lines l GROUP BY l.OrderId, l.SKU; CREATE INDEX ix_i_skus_agg ON tmp_inv_skus_agg(inv_id, sku); /* ========================= PASS A: EXACT MATCHES ========================= */ DROP TEMPORARY TABLE IF EXISTS tmp_cand_exact; CREATE TEMPORARY TABLE tmp_cand_exact AS SELECT w.order_id, i.OrderId AS inv_id FROM tmp_woo_orders_month w JOIN tmp_inv_month i ON i.OrderDate >= w.order_date AND i.OrderDate <= (w.order_date + INTERVAL @invoice_max_lag_days DAY) AND i.inv_total_est BETWEEN COALESCE(w.woo_net_total, w.woo_items_total) * (1 - @exact_price_tol) AND COALESCE(w.woo_net_total, w.woo_items_total) * (1 + @exact_price_tol); CREATE INDEX ix_cand_exact ON tmp_cand_exact(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_overlap_exact; CREATE TEMPORARY TABLE tmp_overlap_exact AS SELECT c.order_id, c.inv_id, COUNT(DISTINCT ws.sku) AS overlap_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_woo_skus_agg WHERE order_id = c.order_id) AS woo_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_inv_skus_agg WHERE inv_id = c.inv_id) AS inv_sku_count, -- Quantity overlap SUM(LEAST(ws.qty, isv.qty)) AS qty_overlap_min, SUM(GREATEST(ws.qty, isv.qty)) AS qty_overlap_max, -- Line-value closeness SUM(ABS(ws.value - isv.value)) AS line_value_diff_sum, SUM(ws.value) AS line_value_sum_woo, -- Unit-price closeness (per overlapping SKU) SUM(CASE WHEN ws.unit_price IS NOT NULL AND ws.unit_price > 0 AND isv.unit_price IS NOT NULL AND ABS(isv.unit_price - ws.unit_price) / ws.unit_price <= @unit_price_tol THEN 1 ELSE 0 END) AS unit_price_match_count FROM tmp_cand_exact c JOIN tmp_woo_skus_agg ws ON ws.order_id = c.order_id JOIN tmp_inv_skus_agg isv ON isv.inv_id = c.inv_id AND isv.sku = ws.sku GROUP BY c.order_id, c.inv_id; CREATE INDEX ix_overlap_exact ON tmp_overlap_exact(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_exact_rank; CREATE TEMPORARY TABLE tmp_exact_rank AS SELECT o.order_id, o.inv_id, o.overlap_sku_count, o.woo_sku_count, o.inv_sku_count, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) AS overlap_ratio_woo, (o.overlap_sku_count / NULLIF(o.inv_sku_count,0)) AS overlap_ratio_inv, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) AS qty_similarity, -- Line-level metrics GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) AS line_value_similarity, (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) AS unit_price_match_ratio, w.woo_total, w.woo_net_total, w.woo_items_total, i.inv_total_est, i.OrderCode AS inv_code, i.Branch AS inv_branch, i.OrderDate AS inv_date, i.inv_line_items, i.inv_qty_sum, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) AS price_diff_ratio, DATEDIFF(DATE(i.OrderDate), w.order_date) AS date_diff_days, ROW_NUMBER() OVER ( PARTITION BY o.order_id ORDER BY (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) DESC, GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) DESC, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) DESC, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) DESC, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) ASC, DATEDIFF(DATE(i.OrderDate), w.order_date) ASC ) AS rn FROM tmp_overlap_exact o JOIN tmp_woo_orders_month w ON w.order_id = o.order_id JOIN tmp_inv_month i ON i.OrderId = o.inv_id WHERE (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) >= @min_overlap_exact AND (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) >= @min_qty_similarity_exact; DROP TEMPORARY TABLE IF EXISTS tmp_exact_best; CREATE TEMPORARY TABLE tmp_exact_best AS SELECT order_id, inv_id, inv_code, inv_branch, inv_date, inv_total_est AS inv_total, inv_line_items, inv_qty_sum, overlap_sku_count, overlap_ratio_woo, overlap_ratio_inv, qty_similarity, line_value_similarity, unit_price_match_ratio, price_diff_ratio, date_diff_days, ( 1400 * COALESCE(unit_price_match_ratio,0) + 1200 * COALESCE(line_value_similarity,0) + 1100 * COALESCE(qty_similarity,0) + 300 * COALESCE(overlap_ratio_woo,0) + 150 * COALESCE(overlap_ratio_inv,0) - 35 * COALESCE(price_diff_ratio,0) - 2 * COALESCE(date_diff_days,0) ) AS match_score FROM tmp_exact_rank WHERE rn = 1; CREATE INDEX ix_exact_best ON tmp_exact_best(order_id); /* ============================= PASS B: PROBABLE PICKUPS ============================= */ DROP TEMPORARY TABLE IF EXISTS tmp_pickup_orders; CREATE TEMPORARY TABLE tmp_pickup_orders AS SELECT * FROM tmp_woo_orders_month WHERE is_pickup = 1 AND order_id NOT IN (SELECT order_id FROM tmp_exact_best); CREATE INDEX ix_pickup_orders ON tmp_pickup_orders(order_id, order_date); DROP TEMPORARY TABLE IF EXISTS tmp_cand_prob; CREATE TEMPORARY TABLE tmp_cand_prob AS SELECT w.order_id, i.OrderId AS inv_id FROM tmp_pickup_orders w JOIN tmp_inv_month i ON i.OrderDate >= w.order_date AND i.OrderDate <= (w.order_date + INTERVAL @invoice_max_lag_days DAY) AND i.inv_total_est BETWEEN COALESCE(w.woo_net_total, w.woo_items_total) * (1 - @probable_price_tol) AND COALESCE(w.woo_net_total, w.woo_items_total) * (1 + @probable_price_tol); CREATE INDEX ix_cand_prob ON tmp_cand_prob(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_overlap_prob; CREATE TEMPORARY TABLE tmp_overlap_prob AS SELECT c.order_id, c.inv_id, COUNT(DISTINCT ws.sku) AS overlap_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_woo_skus_agg WHERE order_id = c.order_id) AS woo_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_inv_skus_agg WHERE inv_id = c.inv_id) AS inv_sku_count, SUM(LEAST(ws.qty, isv.qty)) AS qty_overlap_min, SUM(GREATEST(ws.qty, isv.qty)) AS qty_overlap_max, SUM(ABS(ws.value - isv.value)) AS line_value_diff_sum, SUM(ws.value) AS line_value_sum_woo, SUM(CASE WHEN ws.unit_price IS NOT NULL AND ws.unit_price > 0 AND isv.unit_price IS NOT NULL AND ABS(isv.unit_price - ws.unit_price) / ws.unit_price <= @unit_price_tol THEN 1 ELSE 0 END) AS unit_price_match_count FROM tmp_cand_prob c JOIN tmp_woo_skus_agg ws ON ws.order_id = c.order_id JOIN tmp_inv_skus_agg isv ON isv.inv_id = c.inv_id AND isv.sku = ws.sku GROUP BY c.order_id, c.inv_id; CREATE INDEX ix_overlap_prob ON tmp_overlap_prob(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_prob_rank; CREATE TEMPORARY TABLE tmp_prob_rank AS SELECT o.order_id, o.inv_id, o.overlap_sku_count, o.woo_sku_count, o.inv_sku_count, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) AS overlap_ratio_woo, (o.overlap_sku_count / NULLIF(o.inv_sku_count,0)) AS overlap_ratio_inv, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) AS qty_similarity, GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) AS line_value_similarity, (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) AS unit_price_match_ratio, w.woo_total, w.woo_net_total, w.woo_items_total, i.inv_total_est, i.OrderCode AS inv_code, i.Branch AS inv_branch, i.OrderDate AS inv_date, i.inv_line_items, i.inv_qty_sum, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) AS price_diff_ratio, DATEDIFF(DATE(i.OrderDate), w.order_date) AS date_diff_days, ROW_NUMBER() OVER ( PARTITION BY o.order_id ORDER BY (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) DESC, GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) DESC, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) DESC, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) DESC, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) ASC, DATEDIFF(DATE(i.OrderDate), w.order_date) ASC ) AS rn FROM tmp_overlap_prob o JOIN tmp_pickup_orders w ON w.order_id = o.order_id JOIN tmp_inv_month i ON i.OrderId = o.inv_id WHERE (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) >= @min_overlap_prob AND (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) >= @min_qty_similarity_prob; DROP TEMPORARY TABLE IF EXISTS tmp_prob_best; CREATE TEMPORARY TABLE tmp_prob_best AS SELECT order_id, inv_id, inv_code, inv_branch, inv_date, inv_total_est AS inv_total, inv_line_items, inv_qty_sum, overlap_sku_count, overlap_ratio_woo, overlap_ratio_inv, qty_similarity, line_value_similarity, unit_price_match_ratio, price_diff_ratio, date_diff_days, ( 1200 * COALESCE(unit_price_match_ratio,0) + 1000 * COALESCE(line_value_similarity,0) + 900 * COALESCE(qty_similarity,0) + 250 * COALESCE(overlap_ratio_woo,0) + 120 * COALESCE(overlap_ratio_inv,0) - 20 * COALESCE(price_diff_ratio,0) - 2 * COALESCE(date_diff_days,0) ) AS match_score FROM tmp_prob_rank WHERE rn = 1; CREATE INDEX ix_prob_best ON tmp_prob_best(order_id); /* ========================================== BEST CANDIDATE PER ORDER (EXACT > PROB) ========================================== */ DROP TEMPORARY TABLE IF EXISTS tmp_best_candidate_per_order; CREATE TEMPORARY TABLE tmp_best_candidate_per_order AS SELECT * FROM tmp_exact_best UNION ALL SELECT p.* FROM tmp_prob_best p WHERE NOT EXISTS (SELECT 1 FROM tmp_exact_best e WHERE e.order_id = p.order_id); CREATE INDEX ix_best_candidate ON tmp_best_candidate_per_order(order_id); /* =========================== FINAL MONTHLY MATCH REPORT =========================== */ DROP TEMPORARY TABLE IF EXISTS tmp_monthly_match_report; CREATE TEMPORARY TABLE tmp_monthly_match_report AS SELECT DATE_FORMAT(@from_date, '%Y-%m') AS report_month, w.order_id, w.customer_email, w.order_datetime, w.woo_total, w.woo_net_total, -- products-only amount from Woo (shown) w.woo_line_items, w.woo_qty_sum, w.shippingtype, w.order_status, bc.inv_id AS invoice_id, bc.inv_code AS invoice_code, bc.inv_branch AS invoice_branch, bc.inv_date AS invoice_date, bc.inv_total AS invoice_total, -- ERP product line totals (shown) bc.inv_line_items, bc.inv_qty_sum, bc.overlap_sku_count, ROUND(100 * COALESCE(bc.overlap_ratio_woo,0), 1) AS overlap_pct_vs_order, ROUND(100 * COALESCE(bc.overlap_ratio_inv,0), 1) AS overlap_pct_vs_invoice, ROUND(100 * COALESCE(bc.price_diff_ratio,0), 2) AS price_diff_pct, COALESCE(bc.date_diff_days, NULL) AS date_diff_days, COALESCE(bc.match_score, 0) AS match_score, CASE WHEN bc.inv_id IS NULL THEN 'Not matched' WHEN (bc.overlap_ratio_woo >= @min_overlap_exact AND bc.qty_similarity >= @min_qty_similarity_exact AND bc.price_diff_ratio <= @exact_price_tol) THEN 'Matched' WHEN (w.is_pickup = 1 AND bc.overlap_ratio_woo >= @min_overlap_prob AND bc.qty_similarity >= @min_qty_similarity_prob AND bc.price_diff_ratio <= @probable_price_tol) THEN 'Probable pickup' ELSE 'Not matched' END AS match_status, CASE WHEN bc.inv_id IS NULL THEN 'No invoice found after order within window.' WHEN (bc.overlap_ratio_woo >= @min_overlap_exact AND bc.qty_similarity >= @min_qty_similarity_exact AND bc.price_diff_ratio <= @exact_price_tol) THEN 'Exact SKU coverage, quantities aligned, totals near-identical.' WHEN (w.is_pickup = 1 AND bc.overlap_ratio_woo >= @min_overlap_prob AND bc.qty_similarity >= @min_qty_similarity_prob AND bc.price_diff_ratio <= @probable_price_tol) THEN 'Pickup: strong SKU/qty alignment within wider total tolerance.' ELSE 'Insufficient SKU/qty alignment or totals too far apart.' END AS notes FROM tmp_woo_orders_month w LEFT JOIN tmp_best_candidate_per_order bc ON bc.order_id = w.order_id; CREATE INDEX ix_report_status ON tmp_monthly_match_report(match_status); CREATE INDEX ix_report_dt ON tmp_monthly_match_report(order_datetime); /* ============================================================ PERSIST FINAL MONTHLY MATCHES FOR WPDataTables / phpMyAdmin ============================================================ */ CREATE TABLE IF NOT EXISTS wp_aieo_order_invoice_match_report ( report_month CHAR(7) NULL, -- 'YYYY-MM' order_id BIGINT UNSIGNED NULL, customer_email VARCHAR(255) NULL, order_datetime DATETIME NULL, woo_total DECIMAL(12,2) NULL, -- full Woo total woo_net_total DECIMAL(12,2) NULL, -- products-only total (for comparisons) woo_line_items INT UNSIGNED NULL, woo_qty_sum DECIMAL(12,3) NULL, shippingtype VARCHAR(255) NULL, order_status VARCHAR(50) NULL, invoice_id BIGINT UNSIGNED NULL, invoice_code VARCHAR(40) NULL, invoice_branch VARCHAR(150) NULL, invoice_date DATETIME NULL, invoice_total DECIMAL(12,2) NULL, -- ERP line totals (no VAT uplift) invoice_line_items INT UNSIGNED NULL, invoice_qty_sum DECIMAL(12,3) NULL, overlap_sku_count INT UNSIGNED NULL, overlap_pct_vs_order DECIMAL(5,1) NULL, overlap_pct_vs_invoice DECIMAL(5,1) NULL, price_diff_pct DECIMAL(6,2) NULL, date_diff_days INT NULL, match_status VARCHAR(20) NULL, notes VARCHAR(255) NULL, PRIMARY KEY (report_month, order_id), KEY idx_oimr_match_status (match_status), KEY idx_oimr_order_datetime (order_datetime), KEY idx_oimr_customer_email (customer_email), KEY idx_oimr_invoice_id (invoice_id), KEY idx_oimr_invoice_branch (invoice_branch), KEY idx_oimr_order_status (order_status), KEY idx_oimr_report_month_stat (report_month, match_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; TRUNCATE TABLE wp_aieo_order_invoice_match_report; INSERT INTO wp_aieo_order_invoice_match_report ( report_month, order_id, customer_email, order_datetime, woo_total, woo_net_total, woo_line_items, woo_qty_sum, shippingtype, order_status, invoice_id, invoice_code, invoice_branch, invoice_date, invoice_total, invoice_line_items, invoice_qty_sum, overlap_sku_count, overlap_pct_vs_order, overlap_pct_vs_invoice, price_diff_pct, date_diff_days, match_status, notes ) SELECT report_month, order_id, customer_email, order_datetime, woo_total, woo_net_total, woo_line_items, woo_qty_sum, shippingtype, order_status, invoice_id, invoice_code, invoice_branch, invoice_date, invoice_total, inv_line_items AS invoice_line_items, inv_qty_sum AS invoice_qty_sum, overlap_sku_count, overlap_pct_vs_order, overlap_pct_vs_invoice, price_diff_pct, date_diff_days, match_status, notes FROM tmp_monthly_match_report; AIEO_JOB_SQL; $b['invoice_match_curr_month'] = <<<'AIEO_JOB_SQL' /* ============================ MONTH + MATCHING PARAMETERS ============================ */ -- First day of the *current* month (DATE) SET @report_month := DATE_SUB(UTC_DATE(), INTERVAL DAYOFMONTH(UTC_DATE())-1 DAY); -- Orders created within this range: [@from_date, @to_date) -- Use start of *tomorrow* so that *today* is included. SET @from_date := @report_month; SET @to_date := UTC_DATE() + INTERVAL 1 DAY; -- Forward-only invoice window after each order SET @invoice_max_lag_days := 10; -- Price tolerance (as ratios; 0.50 = 50%) SET @exact_price_tol := 0.50; -- tighter in prod if desired SET @probable_price_tol := 0.50; -- wider band for pickups -- SKU/Qty thresholds (ratios) SET @min_overlap_exact := 0.90; -- >= 90% of Woo SKUs present SET @min_qty_similarity_exact := 0.85; -- quantities ~equal SET @min_overlap_prob := 0.40; -- >= 40% of Woo SKUs present SET @min_qty_similarity_prob := 0.10; -- loose qty alignment for pickup -- Line-level closeness (new) SET @unit_price_tol := 0.10; -- 10% unit-price tolerance per SKU /* ============================ WOO ORDERS (MONTH HEADERS) ============================ */ DROP TEMPORARY TABLE IF EXISTS tmp_woo_orders_month; CREATE TEMPORARY TABLE tmp_woo_orders_month AS SELECT opa.order_id, MIN(os.customeremail) AS customer_email, MIN(os.orderdatetime) AS order_datetime, DATE(MIN(os.orderdatetime)) AS order_date, MIN(os.shippingtype) AS shippingtype, -- A pickup order, known by its shipping method, the name Παραλαβή kept as a fallback (11.3.8). /*AIEO_PICKUP_ORDER*/ AS is_pickup, MIN(os.orderstatus) AS order_status, -- Totals MAX(opa.order_total) AS woo_total, -- full Woo total (incl. shipping/fees) MIN(os.netspendonproducts) AS woo_net_total, -- authoritative products-only total SUM(COALESCE(opa.price, COALESCE(opa.unit_price,0)*COALESCE(opa.quantity,0), 0)) AS woo_items_total, -- fallback COUNT(*) AS woo_line_items, SUM(COALESCE(opa.quantity,0)) AS woo_qty_sum, GROUP_CONCAT(DISTINCT opa.sku ORDER BY opa.sku SEPARATOR ',') AS woo_sku_list, COUNT(DISTINCT opa.sku) AS woo_sku_count FROM wp_aieo_order_products_analytics opa JOIN wp_aieo_order_stats os ON os.orderid = opa.order_id WHERE opa.date_created_gmt >= @from_date AND opa.date_created_gmt < @to_date GROUP BY opa.order_id; CREATE INDEX ix_w_orders_id_date ON tmp_woo_orders_month(order_id, order_date); /* =========================================== INVOICE LINES (FORWARD WINDOW, LINE TOTALS) =========================================== */ DROP TEMPORARY TABLE IF EXISTS tmp_inv_month_lines; CREATE TEMPORARY TABLE tmp_inv_month_lines AS SELECT s.OrderId, s.OrderCode, s.Branch, s.OrderDate, s.SKU, CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3)) AS qty, CAST(REPLACE(COALESCE(s.Line_price, '0'), ',', '.') AS DECIMAL(12,3)) AS line_total, CASE WHEN CAST(REPLACE(COALESCE(s.Quantity,'0'), ',', '.') AS DECIMAL(12,3)) > 0 THEN CAST(REPLACE(COALESCE(s.Line_price,'0'), ',', '.') AS DECIMAL(12,3)) / CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3)) ELSE NULL END AS unit_price FROM ( -- Dedup the daily-re-appended ERP feed (same fix as the prev-month twin + -- AIEO_GeneratePhysicalRecovery's tmp_praw): the sync re-imports each order with a -- fresh ContentLastUpdated (edited/refunded state), so summing ALL rows double-counts -- its lines and inflates inv_total_est / inv_line_items / inv_qty_sum — and the -- current month is exactly where the daily re-appends pile up. Keep only each -- OrderId's LATEST import-day snapshot (single-pass window MAX; no self-join, so -- immune to the stale-stats plan flip). SELECT s.OrderId, s.OrderCode, s.Branch, s.OrderDate, s.SKU, s.Quantity, s.Line_price, COALESCE(DATE(s.ContentLastUpdated), DATE(s.OrderDate)) AS _row_import_day, MAX(COALESCE(DATE(s.ContentLastUpdated), DATE(s.OrderDate))) OVER (PARTITION BY s.OrderId) AS _max_import_day FROM wp_s1erp_temp_store_orders_table s -- forward-only window that can match any order in the month WHERE s.OrderDate >= @from_date AND s.OrderDate < (@to_date + INTERVAL @invoice_max_lag_days DAY) ) s WHERE s._row_import_day = s._max_import_day; CREATE INDEX ix_i_lines_orderdate ON tmp_inv_month_lines(OrderDate); CREATE INDEX ix_i_lines_id ON tmp_inv_month_lines(OrderId); CREATE INDEX ix_i_lines_sku ON tmp_inv_month_lines(SKU); DROP TEMPORARY TABLE IF EXISTS tmp_inv_month; CREATE TEMPORARY TABLE tmp_inv_month AS SELECT OrderId, MIN(OrderCode) AS OrderCode, MIN(Branch) AS Branch, MIN(OrderDate) AS OrderDate, SUM(line_total) AS inv_total_est, -- ERP sum of product line totals (shipping not included) COUNT(*) AS inv_line_items, SUM(qty) AS inv_qty_sum FROM tmp_inv_month_lines GROUP BY OrderId; CREATE INDEX ix_i_hdr_date_total ON tmp_inv_month(OrderDate, inv_total_est); CREATE INDEX ix_i_hdr_id ON tmp_inv_month(OrderId); -- Fresh stats on the just-built temp tables BEFORE the tmp_cand_exact / tmp_cand_prob -- OrderDate-range + total-tolerance joins below (ANALYZE-after-CTAS): a freshly -- CTAS-built table can sit at ~1-row InnoDB stats at proc time, flipping those -- keyless range joins into quadratic scans. Mirrors the prev-month twin's guard. ANALYZE TABLE tmp_woo_orders_month; ANALYZE TABLE tmp_inv_month_lines; ANALYZE TABLE tmp_inv_month; /* =========================================== WOO & ERP PER-SKU AGGREGATES (qty, value, unit price) =========================================== */ DROP TEMPORARY TABLE IF EXISTS tmp_woo_skus_agg; CREATE TEMPORARY TABLE tmp_woo_skus_agg AS SELECT opa.order_id, opa.sku, SUM(COALESCE(opa.quantity,0)) AS qty, SUM(COALESCE(opa.price, COALESCE(opa.unit_price,0)*COALESCE(opa.quantity,0), 0)) AS value, CASE WHEN SUM(COALESCE(opa.quantity,0)) > 0 THEN SUM(COALESCE(opa.price, COALESCE(opa.unit_price,0)*COALESCE(opa.quantity,0), 0)) / SUM(COALESCE(opa.quantity,0)) ELSE NULL END AS unit_price FROM wp_aieo_order_products_analytics opa JOIN tmp_woo_orders_month w ON w.order_id = opa.order_id GROUP BY opa.order_id, opa.sku; CREATE INDEX ix_w_skus_agg ON tmp_woo_skus_agg(order_id, sku); DROP TEMPORARY TABLE IF EXISTS tmp_inv_skus_agg; CREATE TEMPORARY TABLE tmp_inv_skus_agg AS SELECT l.OrderId AS inv_id, l.SKU AS sku, SUM(l.qty) AS qty, SUM(l.line_total) AS value, CASE WHEN SUM(l.qty) > 0 THEN SUM(l.line_total) / SUM(l.qty) ELSE NULL END AS unit_price FROM tmp_inv_month_lines l GROUP BY l.OrderId, l.SKU; CREATE INDEX ix_i_skus_agg ON tmp_inv_skus_agg(inv_id, sku); /* ========================= PASS A: EXACT MATCHES ========================= */ DROP TEMPORARY TABLE IF EXISTS tmp_cand_exact; CREATE TEMPORARY TABLE tmp_cand_exact AS SELECT w.order_id, i.OrderId AS inv_id FROM tmp_woo_orders_month w JOIN tmp_inv_month i ON i.OrderDate >= w.order_date AND i.OrderDate <= (w.order_date + INTERVAL @invoice_max_lag_days DAY) AND i.inv_total_est BETWEEN COALESCE(w.woo_net_total, w.woo_items_total) * (1 - @exact_price_tol) AND COALESCE(w.woo_net_total, w.woo_items_total) * (1 + @exact_price_tol); CREATE INDEX ix_cand_exact ON tmp_cand_exact(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_overlap_exact; CREATE TEMPORARY TABLE tmp_overlap_exact AS SELECT c.order_id, c.inv_id, COUNT(DISTINCT ws.sku) AS overlap_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_woo_skus_agg WHERE order_id = c.order_id) AS woo_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_inv_skus_agg WHERE inv_id = c.inv_id) AS inv_sku_count, -- Quantity overlap SUM(LEAST(ws.qty, isv.qty)) AS qty_overlap_min, SUM(GREATEST(ws.qty, isv.qty)) AS qty_overlap_max, -- Line-value closeness SUM(ABS(ws.value - isv.value)) AS line_value_diff_sum, SUM(ws.value) AS line_value_sum_woo, -- Unit-price closeness (per overlapping SKU) SUM(CASE WHEN ws.unit_price IS NOT NULL AND ws.unit_price > 0 AND isv.unit_price IS NOT NULL AND ABS(isv.unit_price - ws.unit_price) / ws.unit_price <= @unit_price_tol THEN 1 ELSE 0 END) AS unit_price_match_count FROM tmp_cand_exact c JOIN tmp_woo_skus_agg ws ON ws.order_id = c.order_id JOIN tmp_inv_skus_agg isv ON isv.inv_id = c.inv_id AND isv.sku = ws.sku GROUP BY c.order_id, c.inv_id; CREATE INDEX ix_overlap_exact ON tmp_overlap_exact(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_exact_rank; CREATE TEMPORARY TABLE tmp_exact_rank AS SELECT o.order_id, o.inv_id, o.overlap_sku_count, o.woo_sku_count, o.inv_sku_count, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) AS overlap_ratio_woo, (o.overlap_sku_count / NULLIF(o.inv_sku_count,0)) AS overlap_ratio_inv, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) AS qty_similarity, -- New line-level metrics GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) AS line_value_similarity, (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) AS unit_price_match_ratio, w.woo_total, w.woo_net_total, w.woo_items_total, i.inv_total_est, i.OrderCode AS inv_code, i.Branch AS inv_branch, i.OrderDate AS inv_date, i.inv_line_items, i.inv_qty_sum, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) AS price_diff_ratio, DATEDIFF(DATE(i.OrderDate), w.order_date) AS date_diff_days, ROW_NUMBER() OVER ( PARTITION BY o.order_id ORDER BY -- prioritize line-level & quantity closeness first (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) DESC, GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) DESC, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) DESC, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) DESC, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) ASC, DATEDIFF(DATE(i.OrderDate), w.order_date) ASC ) AS rn FROM tmp_overlap_exact o JOIN tmp_woo_orders_month w ON w.order_id = o.order_id JOIN tmp_inv_month i ON i.OrderId = o.inv_id WHERE (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) >= @min_overlap_exact AND (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) >= @min_qty_similarity_exact; DROP TEMPORARY TABLE IF EXISTS tmp_exact_best; CREATE TEMPORARY TABLE tmp_exact_best AS SELECT order_id, inv_id, inv_code, inv_branch, inv_date, inv_total_est AS inv_total, inv_line_items, inv_qty_sum, overlap_sku_count, overlap_ratio_woo, overlap_ratio_inv, qty_similarity, line_value_similarity, unit_price_match_ratio, price_diff_ratio, date_diff_days, ( 1400 * COALESCE(unit_price_match_ratio,0) + 1200 * COALESCE(line_value_similarity,0) + 1100 * COALESCE(qty_similarity,0) + 300 * COALESCE(overlap_ratio_woo,0) + 150 * COALESCE(overlap_ratio_inv,0) - 35 * COALESCE(price_diff_ratio,0) - 2 * COALESCE(date_diff_days,0) ) AS match_score FROM tmp_exact_rank WHERE rn = 1; CREATE INDEX ix_exact_best ON tmp_exact_best(order_id); /* ============================= PASS B: PROBABLE PICKUPS ============================= */ DROP TEMPORARY TABLE IF EXISTS tmp_pickup_orders; CREATE TEMPORARY TABLE tmp_pickup_orders AS SELECT * FROM tmp_woo_orders_month WHERE is_pickup = 1 AND order_id NOT IN (SELECT order_id FROM tmp_exact_best); CREATE INDEX ix_pickup_orders ON tmp_pickup_orders(order_id, order_date); DROP TEMPORARY TABLE IF EXISTS tmp_cand_prob; CREATE TEMPORARY TABLE tmp_cand_prob AS SELECT w.order_id, i.OrderId AS inv_id FROM tmp_pickup_orders w JOIN tmp_inv_month i ON i.OrderDate >= w.order_date AND i.OrderDate <= (w.order_date + INTERVAL @invoice_max_lag_days DAY) AND i.inv_total_est BETWEEN COALESCE(w.woo_net_total, w.woo_items_total) * (1 - @probable_price_tol) AND COALESCE(w.woo_net_total, w.woo_items_total) * (1 + @probable_price_tol); CREATE INDEX ix_cand_prob ON tmp_cand_prob(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_overlap_prob; CREATE TEMPORARY TABLE tmp_overlap_prob AS SELECT c.order_id, c.inv_id, COUNT(DISTINCT ws.sku) AS overlap_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_woo_skus_agg WHERE order_id = c.order_id) AS woo_sku_count, (SELECT COUNT(DISTINCT sku) FROM tmp_inv_skus_agg WHERE inv_id = c.inv_id) AS inv_sku_count, SUM(LEAST(ws.qty, isv.qty)) AS qty_overlap_min, SUM(GREATEST(ws.qty, isv.qty)) AS qty_overlap_max, SUM(ABS(ws.value - isv.value)) AS line_value_diff_sum, SUM(ws.value) AS line_value_sum_woo, SUM(CASE WHEN ws.unit_price IS NOT NULL AND ws.unit_price > 0 AND isv.unit_price IS NOT NULL AND ABS(isv.unit_price - ws.unit_price) / ws.unit_price <= @unit_price_tol THEN 1 ELSE 0 END) AS unit_price_match_count FROM tmp_cand_prob c JOIN tmp_woo_skus_agg ws ON ws.order_id = c.order_id JOIN tmp_inv_skus_agg isv ON isv.inv_id = c.inv_id AND isv.sku = ws.sku GROUP BY c.order_id, c.inv_id; CREATE INDEX ix_overlap_prob ON tmp_overlap_prob(order_id, inv_id); DROP TEMPORARY TABLE IF EXISTS tmp_prob_rank; CREATE TEMPORARY TABLE tmp_prob_rank AS SELECT o.order_id, o.inv_id, o.overlap_sku_count, o.woo_sku_count, o.inv_sku_count, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) AS overlap_ratio_woo, (o.overlap_sku_count / NULLIF(o.inv_sku_count,0)) AS overlap_ratio_inv, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) AS qty_similarity, GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) AS line_value_similarity, (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) AS unit_price_match_ratio, w.woo_total, w.woo_net_total, w.woo_items_total, i.inv_total_est, i.OrderCode AS inv_code, i.Branch AS inv_branch, i.OrderDate AS inv_date, i.inv_line_items, i.inv_qty_sum, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) AS price_diff_ratio, DATEDIFF(DATE(i.OrderDate), w.order_date) AS date_diff_days, ROW_NUMBER() OVER ( PARTITION BY o.order_id ORDER BY (o.unit_price_match_count / NULLIF(o.overlap_sku_count,0)) DESC, GREATEST(0, 1 - (o.line_value_diff_sum / NULLIF(o.line_value_sum_woo,0))) DESC, (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) DESC, (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) DESC, ABS(i.inv_total_est - COALESCE(w.woo_net_total, w.woo_items_total)) / NULLIF(COALESCE(w.woo_net_total, w.woo_items_total),0) ASC, DATEDIFF(DATE(i.OrderDate), w.order_date) ASC ) AS rn FROM tmp_overlap_prob o JOIN tmp_pickup_orders w ON w.order_id = o.order_id JOIN tmp_inv_month i ON i.OrderId = o.inv_id WHERE (o.overlap_sku_count / NULLIF(o.woo_sku_count,0)) >= @min_overlap_prob AND (o.qty_overlap_min / NULLIF(o.qty_overlap_max,0)) >= @min_qty_similarity_prob; DROP TEMPORARY TABLE IF EXISTS tmp_prob_best; CREATE TEMPORARY TABLE tmp_prob_best AS SELECT order_id, inv_id, inv_code, inv_branch, inv_date, inv_total_est AS inv_total, inv_line_items, inv_qty_sum, overlap_sku_count, overlap_ratio_woo, overlap_ratio_inv, qty_similarity, line_value_similarity, unit_price_match_ratio, price_diff_ratio, date_diff_days, ( 1200 * COALESCE(unit_price_match_ratio,0) + 1000 * COALESCE(line_value_similarity,0) + 900 * COALESCE(qty_similarity,0) + 250 * COALESCE(overlap_ratio_woo,0) + 120 * COALESCE(overlap_ratio_inv,0) - 20 * COALESCE(price_diff_ratio,0) - 2 * COALESCE(date_diff_days,0) ) AS match_score FROM tmp_prob_rank WHERE rn = 1; CREATE INDEX ix_prob_best ON tmp_prob_best(order_id); /* ========================================== BEST CANDIDATE PER ORDER (EXACT > PROB) ========================================== */ DROP TEMPORARY TABLE IF EXISTS tmp_best_candidate_per_order; CREATE TEMPORARY TABLE tmp_best_candidate_per_order AS SELECT * FROM tmp_exact_best UNION ALL SELECT p.* FROM tmp_prob_best p WHERE NOT EXISTS (SELECT 1 FROM tmp_exact_best e WHERE e.order_id = p.order_id); CREATE INDEX ix_best_candidate ON tmp_best_candidate_per_order(order_id); /* =========================== FINAL MONTHLY MATCH REPORT =========================== */ DROP TEMPORARY TABLE IF EXISTS tmp_monthly_match_report; CREATE TEMPORARY TABLE tmp_monthly_match_report AS SELECT DATE_FORMAT(@from_date, '%Y-%m') AS report_month, w.order_id, w.customer_email, w.order_datetime, w.woo_total, w.woo_net_total, -- products-only amount from Woo (shown) w.woo_line_items, w.woo_qty_sum, w.shippingtype, w.order_status, bc.inv_id AS invoice_id, bc.inv_code AS invoice_code, bc.inv_branch AS invoice_branch, bc.inv_date AS invoice_date, bc.inv_total AS invoice_total, -- ERP product line totals (shown) bc.inv_line_items, bc.inv_qty_sum, bc.overlap_sku_count, ROUND(100 * COALESCE(bc.overlap_ratio_woo,0), 1) AS overlap_pct_vs_order, ROUND(100 * COALESCE(bc.overlap_ratio_inv,0), 1) AS overlap_pct_vs_invoice, ROUND(100 * COALESCE(bc.price_diff_ratio,0), 2) AS price_diff_pct, COALESCE(bc.date_diff_days, NULL) AS date_diff_days, COALESCE(bc.match_score, 0) AS match_score, CASE WHEN bc.inv_id IS NULL THEN 'Not matched' WHEN (bc.overlap_ratio_woo >= @min_overlap_exact AND bc.qty_similarity >= @min_qty_similarity_exact AND bc.price_diff_ratio <= @exact_price_tol) THEN 'Matched' WHEN (w.is_pickup = 1 AND bc.overlap_ratio_woo >= @min_overlap_prob AND bc.qty_similarity >= @min_qty_similarity_prob AND bc.price_diff_ratio <= @probable_price_tol) THEN 'Probable pickup' ELSE 'Not matched' END AS match_status, CASE WHEN bc.inv_id IS NULL THEN 'No invoice found after order within window.' WHEN (bc.overlap_ratio_woo >= @min_overlap_exact AND bc.qty_similarity >= @min_qty_similarity_exact AND bc.price_diff_ratio <= @exact_price_tol) THEN 'Exact SKU coverage, quantities aligned, totals near-identical.' WHEN (w.is_pickup = 1 AND bc.overlap_ratio_woo >= @min_overlap_prob AND bc.qty_similarity >= @min_qty_similarity_prob AND bc.price_diff_ratio <= @probable_price_tol) THEN 'Pickup: strong SKU/qty alignment within wider total tolerance.' ELSE 'Insufficient SKU/qty alignment or totals too far apart.' END AS notes FROM tmp_woo_orders_month w LEFT JOIN tmp_best_candidate_per_order bc ON bc.order_id = w.order_id; CREATE INDEX ix_report_status ON tmp_monthly_match_report(match_status); CREATE INDEX ix_report_dt ON tmp_monthly_match_report(order_datetime); /* ============================================================ PERSIST FINAL MATCHES (CURRENT MONTH) ============================================================ */ CREATE TABLE IF NOT EXISTS wp_aieo_order_invoice_match_report_current_month ( report_month CHAR(7) NULL, -- 'YYYY-MM' order_id BIGINT UNSIGNED NULL, customer_email VARCHAR(255) NULL, order_datetime DATETIME NULL, woo_total DECIMAL(12,2) NULL, -- full Woo total woo_net_total DECIMAL(12,2) NULL, -- products-only total (for comparisons) woo_line_items INT UNSIGNED NULL, woo_qty_sum DECIMAL(12,3) NULL, shippingtype VARCHAR(255) NULL, order_status VARCHAR(50) NULL, invoice_id BIGINT UNSIGNED NULL, invoice_code VARCHAR(40) NULL, invoice_branch VARCHAR(150) NULL, invoice_date DATETIME NULL, invoice_total DECIMAL(12,2) NULL, -- ERP product line totals invoice_line_items INT UNSIGNED NULL, invoice_qty_sum DECIMAL(12,3) NULL, overlap_sku_count INT UNSIGNED NULL, overlap_pct_vs_order DECIMAL(5,1) NULL, overlap_pct_vs_invoice DECIMAL(5,1) NULL, price_diff_pct DECIMAL(6,2) NULL, date_diff_days INT NULL, match_status VARCHAR(20) NULL, notes VARCHAR(255) NULL, PRIMARY KEY (report_month, order_id), KEY idx_oimr_match_status (match_status), KEY idx_oimr_order_datetime (order_datetime), KEY idx_oimr_customer_email (customer_email), KEY idx_oimr_invoice_id (invoice_id), KEY idx_oimr_invoice_branch (invoice_branch), KEY idx_oimr_order_status (order_status), KEY idx_oimr_report_month_stat (report_month, match_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci; TRUNCATE TABLE wp_aieo_order_invoice_match_report_current_month; INSERT INTO wp_aieo_order_invoice_match_report_current_month ( report_month, order_id, customer_email, order_datetime, woo_total, woo_net_total, woo_line_items, woo_qty_sum, shippingtype, order_status, invoice_id, invoice_code, invoice_branch, invoice_date, invoice_total, invoice_line_items, invoice_qty_sum, overlap_sku_count, overlap_pct_vs_order, overlap_pct_vs_invoice, price_diff_pct, date_diff_days, match_status, notes ) SELECT report_month, order_id, customer_email, order_datetime, woo_total, woo_net_total, woo_line_items, woo_qty_sum, shippingtype, order_status, invoice_id, invoice_code, invoice_branch, invoice_date, invoice_total, inv_line_items AS invoice_line_items, inv_qty_sum AS invoice_qty_sum, overlap_sku_count, overlap_pct_vs_order, overlap_pct_vs_invoice, price_diff_pct, date_diff_days, match_status, notes FROM tmp_monthly_match_report; AIEO_JOB_SQL; $b['store_only_products'] = <<<'AIEO_JOB_SQL' /* ALTER TABLE `wp_aieo_reciprocal_ids` CHANGE `Soft1Id` `Soft1Ids` VARCHAR(120) DEFAULT NULL, ADD COLUMN `ContentCreated` DATETIME DEFAULT CURRENT_TIMESTAMP(), ADD COLUMN `ContentLastUpdated` DATETIME DEFAULT CURRENT_TIMESTAMP(); ALTER TABLE `wp_s1erp_temp_unique_product_catalogue_table` MODIFY COLUMN `Soft1Ids` VARCHAR(120) DEFAULT NULL; ALTER TABLE `wp_aieo_store_only_products` MODIFY COLUMN `Soft1Ids` VARCHAR(120) DEFAULT NULL; */ TRUNCATE TABLE wp_aieo_reciprocal_ids; INSERT INTO `wp_aieo_reciprocal_ids` ( `SKU`, `Soft1Id`, `Soft1Ids`, `EshopId`, `EshopParentId`, `ContentCreated`, `ContentLastUpdated`, `ExcludeFromUpdate`, `update_on_next_run` ) SELECT t.`SKU`, t.`Soft1Id`, t.`Soft1Ids`, v.`product_id` AS EshopId, v.`parent_id` AS EshopParentId, t.`ContentCreated`, t.`ContentLastUpdated`, t.`ExcludeFromUpdate`, IF( COALESCE( CAST(REPLACE(t.sale_price, ',', '.') AS DECIMAL(12,2)), CAST(REPLACE(t.regular_price, ',', '.') AS DECIMAL(12,2)) ) <> v.price, 1, 0 ) AS update_on_next_run FROM `wp_s1erp_temp_unique_product_catalogue_table` t INNER JOIN `wp_aieo_core_product_vitals` v ON t.`SKU` = v.`sku` WHERE t.`SKU` IS NOT NULL AND t.`SKU` <> '' GROUP BY t.`SKU` ON DUPLICATE KEY UPDATE `Soft1Ids` = VALUES(`Soft1Ids`), `EshopId` = VALUES(`EshopId`), `EshopParentId` = VALUES(`EshopParentId`), `ContentCreated` = VALUES(`ContentCreated`), `ContentLastUpdated` = VALUES(`ContentLastUpdated`), `ExcludeFromUpdate` = VALUES(`ExcludeFromUpdate`), `update_on_next_run` = VALUES(`update_on_next_run`); -- Refresh stats after the TRUNCATE+rebuild of reciprocal_ids so the store_only_products -- anti-join below (WHERE NOT EXISTS … r.SKU = t.SKU) plans its semijoin against real -- cardinality instead of ~1-row stale stats (stale-stats guard). ANALYZE TABLE `wp_aieo_reciprocal_ids`; TRUNCATE TABLE `wp_aieo_store_only_products`; INSERT INTO `wp_aieo_store_only_products` ( `Soft1Id`, `CompanyIds`, `Soft1Ids`, `Soft1ProductName`, `EshopParentId`, `EshopId`, `SKU`, `SKU_norm`, `Barcode`, `SupplierId`, `regular_price`, `sale_price`, `stock`, `customer_hairdresser_price`, `customer_makigiaz_price`, `customer_manicure_price`, `customer_beautician_price`, `ContentCreated`, `ContentLastUpdated`, `ExcludeFromUpdate`, `ProductStatus`, `VAT` ) SELECT t.`Soft1Id`, t.`CompanyIds`, t.`Soft1Ids`, t.`Soft1ProductName`, t.`EshopParentId`, t.`EshopId`, t.`SKU`, t.`SKU` AS SKU_norm, t.`Barcode`, t.`SupplierId`, t.`regular_price`, t.`sale_price`, t.`stock`, t.`customer_hairdresser_price`, t.`customer_makigiaz_price`, t.`customer_manicure_price`, t.`customer_beautician_price`, t.`ContentCreated`, t.`ContentLastUpdated`, t.`ExcludeFromUpdate`, t.`ProductStatus`, t.`VAT` FROM `wp_s1erp_temp_unique_product_catalogue_table` t WHERE NOT EXISTS ( SELECT 1 FROM `wp_aieo_reciprocal_ids` r WHERE r.`SKU` = t.`SKU` ); AIEO_JOB_SQL; $b['sales_predictor'] = <<<'AIEO_JOB_SQL' SET @grain_mode = COALESCE(NULLIF(@aieo_predictor_grain, ''), 'both'); SET @asof = COALESCE(@aieo_predictor_asof, UTC_DATE()); SET @acc_alpha = 0.30; -- EWMA smoothing for the rolling accuracy scoreboard SET @bt_h = 28; -- backtest horizon (days) — score the snapshot taken @bt_h days ago SET @reorder_h = 56; -- reorder horizon (days of cover the suggested qty should buy) -- Snapshot the vitals attributes the proc needs into a session-private temp -- table (one consistent read at proc start). Every downstream JOIN uses this -- instead of the live vitals table, so the predictor never lock-wait-times-out -- against a concurrent vitals rebuild (status/age recompute). Keyed for joins. DROP TEMPORARY TABLE IF EXISTS tmp_vit; CREATE TEMPORARY TABLE tmp_vit AS SELECT product_id, product_age, product_latest_sale_date, CAST(COALESCE(category_id, 0) AS CHAR) COLLATE utf8mb4_uca1400_ai_ci AS category_key FROM wp_aieo_core_product_vitals WHERE status = 1 OR parent_id = product_id; ALTER TABLE tmp_vit ADD KEY k (product_id); -- Parent stock = SUM of each parent's variations' stock, precomputed ONCE -- (one scan of vitals) so the parent-grain INSERT joins it instead of -- running a correlated subquery per group on the unindexed parent_id. DROP TEMPORARY TABLE IF EXISTS tmp_pstock; CREATE TEMPORARY TABLE tmp_pstock AS SELECT parent_id AS pid, COALESCE(SUM(stock), 0) AS sum_stock FROM wp_aieo_core_product_vitals GROUP BY parent_id; ALTER TABLE tmp_pstock ADD KEY k (pid); -- --------------------------------------------------------------------- -- 1) BACKTEST -> accuracy scoreboard. Compare each method's projected -- units (rate * @bt_h) from the snapshot @bt_h days ago against units -- actually sold since. EWMA-update per (grain, segment, method). -- Safe no-op until history has matured to @bt_h days. -- --------------------------------------------------------------------- DROP TEMPORARY TABLE IF EXISTS tmp_bt; CREATE TEMPORARY TABLE tmp_bt AS SELECT h.grain, h.product_id, COALESCE(v.category_key, '0') AS category_key, h.ipd_linear, h.ipd_ewma, h.ipd_average, (SELECT COALESCE(SUM(o.quantity), 0) FROM wp_aieo_order_products_analytics o WHERE (CASE WHEN h.grain = 'parent' THEN o.product_id = h.product_id ELSE COALESCE(NULLIF(o.variation_id, 0), o.product_id) = h.product_id END) AND o.date_created_gmt > h.snapshot_date AND o.date_created_gmt <= h.snapshot_date + INTERVAL @bt_h DAY ) AS realized FROM wp_aieo_sales_predictor_history h LEFT JOIN tmp_vit v ON v.product_id = h.product_id WHERE h.snapshot_date = DATE(@asof - INTERVAL @bt_h DAY); -- One upsert per (method, segment). VALUES() is the MariaDB 10.6 idiom. INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at) SELECT grain, 'global', '', 'linear', COUNT(*), AVG(ABS(ipd_linear * @bt_h - realized) / GREATEST(realized, 1)) * 100, AVG(ipd_linear * @bt_h - realized), AVG(ABS(ipd_linear * @bt_h - realized)), UTC_TIMESTAMP() FROM tmp_bt WHERE ipd_linear IS NOT NULL GROUP BY grain ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias), ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at); INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at) SELECT grain, 'global', '', 'ewma', COUNT(*), AVG(ABS(ipd_ewma * @bt_h - realized) / GREATEST(realized, 1)) * 100, AVG(ipd_ewma * @bt_h - realized), AVG(ABS(ipd_ewma * @bt_h - realized)), UTC_TIMESTAMP() FROM tmp_bt WHERE ipd_ewma IS NOT NULL GROUP BY grain ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias), ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at); INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at) SELECT grain, 'global', '', 'average', COUNT(*), AVG(ABS(ipd_average * @bt_h - realized) / GREATEST(realized, 1)) * 100, AVG(ipd_average * @bt_h - realized), AVG(ABS(ipd_average * @bt_h - realized)), UTC_TIMESTAMP() FROM tmp_bt WHERE ipd_average IS NOT NULL GROUP BY grain ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias), ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at); INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at) SELECT grain, 'category', category_key, 'linear', COUNT(*), AVG(ABS(ipd_linear * @bt_h - realized) / GREATEST(realized, 1)) * 100, AVG(ipd_linear * @bt_h - realized), AVG(ABS(ipd_linear * @bt_h - realized)), UTC_TIMESTAMP() FROM tmp_bt WHERE ipd_linear IS NOT NULL AND category_key <> '0' GROUP BY grain, category_key ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias), ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at); INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at) SELECT grain, 'category', category_key, 'ewma', COUNT(*), AVG(ABS(ipd_ewma * @bt_h - realized) / GREATEST(realized, 1)) * 100, AVG(ipd_ewma * @bt_h - realized), AVG(ABS(ipd_ewma * @bt_h - realized)), UTC_TIMESTAMP() FROM tmp_bt WHERE ipd_ewma IS NOT NULL AND category_key <> '0' GROUP BY grain, category_key ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias), ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at); INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at) SELECT grain, 'category', category_key, 'average', COUNT(*), AVG(ABS(ipd_average * @bt_h - realized) / GREATEST(realized, 1)) * 100, AVG(ipd_average * @bt_h - realized), AVG(ABS(ipd_average * @bt_h - realized)), UTC_TIMESTAMP() FROM tmp_bt WHERE ipd_average IS NOT NULL AND category_key <> '0' GROUP BY grain, category_key ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias), ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at); -- Previous snapshot (latest history row strictly before @asof) for price-change tracking. DROP TEMPORARY TABLE IF EXISTS tmp_prev; CREATE TEMPORARY TABLE tmp_prev AS SELECT h.grain, h.product_id, h.price AS prev_price FROM wp_aieo_sales_predictor_history h JOIN (SELECT grain, product_id, MAX(snapshot_date) AS msd FROM wp_aieo_sales_predictor_history WHERE snapshot_date < @asof GROUP BY grain, product_id) m ON m.grain = h.grain AND m.product_id = h.product_id AND m.msd = h.snapshot_date; ALTER TABLE tmp_prev ADD KEY k (grain, product_id); -- --------------------------------------------------------------------- -- 2) REBUILD the snapshot for @asof (both grains; trimmed to mode later). -- --------------------------------------------------------------------- TRUNCATE wp_aieo_sales_predictor; -- VARIATION grain: one row per sellable unit (vitals.status=1). The window -- anchor is @asof while in stock, else the product's own last sale date -- (so an out-of-stock item is measured over its last sellable N days). INSERT INTO wp_aieo_sales_predictor (grain, product_id, parent_product_id, prediction_date, current_stock, price, total_product_sales, total_product_turnover, quantity_sold_yesterday, turnover_yesterday, quantity_last_7_days, quantity_last_14_days, quantity_last_28_days, quantity_last_56_days, quantity_last_175_days, turnover_last_7_days, turnover_last_14_days, turnover_last_28_days, turnover_last_56_days, turnover_last_175_days, quantity_next_7_days, quantity_next_14_days, quantity_next_28_days, quantity_next_56_days, quantity_next_175_days, turnover_next_7_days, turnover_next_14_days, turnover_next_28_days, turnover_next_56_days, turnover_next_175_days, product_availability_last_year_7, product_availability_last_year_14, product_availability_last_year_28, product_availability_last_year_56, product_availability_last_year_175, transactions_total, out_of_stock_days) SELECT 'variation', v.product_id, v.parent_id, @asof, v.stock, v.price, COALESCE(SUM(opa.quantity), 0), COALESCE(SUM(opa.price), 0), COALESCE(SUM(CASE WHEN DATE(opa.date_created_gmt) = DATE(v.product_latest_sale_date) THEN opa.quantity ELSE 0 END), 0), COALESCE(SUM(CASE WHEN DATE(opa.date_created_gmt) = DATE(v.product_latest_sale_date) THEN opa.price ELSE 0 END), 0), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 7 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 7 DAY)) THEN opa.quantity ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 14 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 14 DAY)) THEN opa.quantity ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 28 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 28 DAY)) THEN opa.quantity ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 56 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 56 DAY)) THEN opa.quantity ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 175 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 175 DAY)) THEN opa.quantity ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 7 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 7 DAY)) THEN opa.price ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 14 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 14 DAY)) THEN opa.price ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 28 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 28 DAY)) THEN opa.price ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 56 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 56 DAY)) THEN opa.price ELSE 0 END), SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 175 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 175 DAY)) THEN opa.price ELSE 0 END), CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN 1 ELSE 0 END, SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 175 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 175 DAY)) AND opa.order_id IS NOT NULL THEN 1 ELSE 0 END), CASE WHEN v.stock < 1 THEN GREATEST(DATEDIFF(@asof, COALESCE(v.product_latest_sale_date, MAX(opa.date_created_gmt))), 0) ELSE 0 END FROM wp_aieo_core_product_vitals v LEFT JOIN wp_aieo_order_products_analytics opa ON opa.effective_pid = v.product_id WHERE v.status = 1 GROUP BY v.product_id, v.parent_id, v.stock, v.price, v.product_creation_date, v.product_latest_sale_date HAVING SUM(opa.quantity) > 0; -- PARENT grain: one row per parent product (vitals.parent_id = product_id). -- current_stock = SUM of the parent's variations' stock; windows anchored -- at @asof (a parent is "in stock" if any variation is). INSERT INTO wp_aieo_sales_predictor (grain, product_id, parent_product_id, prediction_date, current_stock, price, total_product_sales, total_product_turnover, quantity_sold_yesterday, turnover_yesterday, quantity_last_7_days, quantity_last_14_days, quantity_last_28_days, quantity_last_56_days, quantity_last_175_days, turnover_last_7_days, turnover_last_14_days, turnover_last_28_days, turnover_last_56_days, turnover_last_175_days, quantity_next_7_days, quantity_next_14_days, quantity_next_28_days, quantity_next_56_days, quantity_next_175_days, turnover_next_7_days, turnover_next_14_days, turnover_next_28_days, turnover_next_56_days, turnover_next_175_days, product_availability_last_year_7, product_availability_last_year_14, product_availability_last_year_28, product_availability_last_year_56, product_availability_last_year_175, transactions_total, out_of_stock_days) SELECT 'parent', v.product_id, v.product_id, @asof, COALESCE(ps.sum_stock, 0), v.price, COALESCE(SUM(opa.quantity), 0), COALESCE(SUM(opa.price), 0), COALESCE(SUM(CASE WHEN DATE(opa.date_created_gmt) = DATE(v.product_latest_sale_date) THEN opa.quantity ELSE 0 END), 0), COALESCE(SUM(CASE WHEN DATE(opa.date_created_gmt) = DATE(v.product_latest_sale_date) THEN opa.price ELSE 0 END), 0), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 7 DAY THEN opa.quantity ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 14 DAY THEN opa.quantity ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 28 DAY THEN opa.quantity ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 56 DAY THEN opa.quantity ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 175 DAY THEN opa.quantity ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 7 DAY THEN opa.price ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 14 DAY THEN opa.price ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 28 DAY THEN opa.price ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 56 DAY THEN opa.price ELSE 0 END), SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 175 DAY THEN opa.price ELSE 0 END), CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN opa.price ELSE 0 END) ELSE NULL END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56 DAY THEN 1 ELSE 0 END, CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN 1 ELSE 0 END, SUM(CASE WHEN opa.date_created_gmt >= @asof - INTERVAL 175 DAY AND opa.order_id IS NOT NULL THEN 1 ELSE 0 END), CASE WHEN COALESCE(ps.sum_stock, 0) < 1 THEN GREATEST(DATEDIFF(@asof, COALESCE(v.product_latest_sale_date, MAX(opa.date_created_gmt))), 0) ELSE 0 END FROM wp_aieo_core_product_vitals v LEFT JOIN tmp_pstock ps ON ps.pid = v.product_id LEFT JOIN wp_aieo_order_products_analytics opa ON opa.product_id = v.product_id WHERE v.parent_id = v.product_id GROUP BY v.product_id, v.price, v.product_creation_date, v.product_latest_sale_date HAVING SUM(opa.quantity) > 0; -- --------------------------------------------------------------------- -- 3) Per-window items/turnover per day (age-adjusted) + out-of-stock days. -- next_* divide by the seasonal window length N (only when available). -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor sp JOIN tmp_vit v ON v.product_id = sp.product_id SET sp.items_per_day_last_7_days = CASE WHEN sp.quantity_last_7_days > 0 THEN ROUND(sp.quantity_last_7_days / LEAST(GREATEST(COALESCE(v.product_age, 7), 1), 7), 7) ELSE NULL END, sp.items_per_day_last_14_days = CASE WHEN sp.quantity_last_14_days > 0 THEN ROUND(sp.quantity_last_14_days / LEAST(GREATEST(COALESCE(v.product_age, 14), 1), 14), 7) ELSE NULL END, sp.items_per_day_last_28_days = CASE WHEN sp.quantity_last_28_days > 0 THEN ROUND(sp.quantity_last_28_days / LEAST(GREATEST(COALESCE(v.product_age, 28), 1), 28), 7) ELSE NULL END, sp.items_per_day_last_56_days = CASE WHEN sp.quantity_last_56_days > 0 THEN ROUND(sp.quantity_last_56_days / LEAST(GREATEST(COALESCE(v.product_age, 56), 1), 56), 7) ELSE NULL END, sp.items_per_day_last_175_days = CASE WHEN sp.quantity_last_175_days > 0 THEN ROUND(sp.quantity_last_175_days / LEAST(GREATEST(COALESCE(v.product_age, 175), 1), 175), 7) ELSE NULL END, sp.items_per_day_next_7_days = CASE WHEN sp.product_availability_last_year_7 = 1 AND sp.quantity_next_7_days > 0 THEN ROUND(sp.quantity_next_7_days / 7, 7) ELSE NULL END, sp.items_per_day_next_14_days = CASE WHEN sp.product_availability_last_year_14 = 1 AND sp.quantity_next_14_days > 0 THEN ROUND(sp.quantity_next_14_days / 14, 7) ELSE NULL END, sp.items_per_day_next_28_days = CASE WHEN sp.product_availability_last_year_28 = 1 AND sp.quantity_next_28_days > 0 THEN ROUND(sp.quantity_next_28_days / 28, 7) ELSE NULL END, sp.items_per_day_next_56_days = CASE WHEN sp.product_availability_last_year_56 = 1 AND sp.quantity_next_56_days > 0 THEN ROUND(sp.quantity_next_56_days / 56, 7) ELSE NULL END, sp.items_per_day_next_175_days = CASE WHEN sp.product_availability_last_year_175 = 1 AND sp.quantity_next_175_days > 0 THEN ROUND(sp.quantity_next_175_days / 175, 7) ELSE NULL END, sp.turnover_per_day_last_7_days = CASE WHEN sp.turnover_last_7_days > 0 THEN ROUND(sp.turnover_last_7_days / LEAST(GREATEST(COALESCE(v.product_age, 7), 1), 7), 7) ELSE NULL END, sp.turnover_per_day_last_14_days = CASE WHEN sp.turnover_last_14_days > 0 THEN ROUND(sp.turnover_last_14_days / LEAST(GREATEST(COALESCE(v.product_age, 14), 1), 14), 7) ELSE NULL END, sp.turnover_per_day_last_28_days = CASE WHEN sp.turnover_last_28_days > 0 THEN ROUND(sp.turnover_last_28_days / LEAST(GREATEST(COALESCE(v.product_age, 28), 1), 28), 7) ELSE NULL END, sp.turnover_per_day_last_56_days = CASE WHEN sp.turnover_last_56_days > 0 THEN ROUND(sp.turnover_last_56_days / LEAST(GREATEST(COALESCE(v.product_age, 56), 1), 56), 7) ELSE NULL END, sp.turnover_per_day_last_175_days = CASE WHEN sp.turnover_last_175_days > 0 THEN ROUND(sp.turnover_last_175_days / LEAST(GREATEST(COALESCE(v.product_age, 175), 1), 175), 7) ELSE NULL END, sp.turnover_per_day_next_7_days = CASE WHEN sp.product_availability_last_year_7 = 1 AND sp.turnover_next_7_days > 0 THEN ROUND(sp.turnover_next_7_days / 7, 7) ELSE NULL END, sp.turnover_per_day_next_14_days = CASE WHEN sp.product_availability_last_year_14 = 1 AND sp.turnover_next_14_days > 0 THEN ROUND(sp.turnover_next_14_days / 14, 7) ELSE NULL END, sp.turnover_per_day_next_28_days = CASE WHEN sp.product_availability_last_year_28 = 1 AND sp.turnover_next_28_days > 0 THEN ROUND(sp.turnover_next_28_days / 28, 7) ELSE NULL END, sp.turnover_per_day_next_56_days = CASE WHEN sp.product_availability_last_year_56 = 1 AND sp.turnover_next_56_days > 0 THEN ROUND(sp.turnover_next_56_days / 56, 7) ELSE NULL END, sp.turnover_per_day_next_175_days = CASE WHEN sp.product_availability_last_year_175 = 1 AND sp.turnover_next_175_days > 0 THEN ROUND(sp.turnover_next_175_days / 175, 7) ELSE NULL END WHERE sp.prediction_date = @asof; -- --------------------------------------------------------------------- -- 4) linear (age-weighted blend) + EWMA (recency-weighted blend) rates. -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor sp JOIN tmp_vit v ON v.product_id = sp.product_id SET sp.linear_items_per_day = ROUND(CASE WHEN COALESCE(v.product_age, 0) > 365 THEN 0.05 * COALESCE(sp.items_per_day_last_175_days, 0) + 0.05 * COALESCE(sp.items_per_day_next_175_days, 0) + 0.075 * COALESCE(sp.items_per_day_last_56_days, 0) + 0.075 * COALESCE(sp.items_per_day_next_56_days, 0) + 0.1 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.1 * COALESCE(sp.items_per_day_next_28_days, 0) + 0.1 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.1 * COALESCE(sp.items_per_day_next_14_days, 0) + 0.2 * COALESCE(sp.items_per_day_last_7_days, 0) + 0.2 * COALESCE(sp.items_per_day_next_7_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 175 AND 365 THEN 0.05 * COALESCE(sp.items_per_day_last_175_days, 0) + 0.075 * COALESCE(sp.items_per_day_last_56_days, 0) + 0.15 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.2 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.4 * COALESCE(sp.items_per_day_last_7_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 56 AND 174 THEN 0.1 * COALESCE(sp.items_per_day_last_56_days, 0) + 0.2 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.3 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.4 * COALESCE(sp.items_per_day_last_7_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 28 AND 55 THEN 0.25 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.35 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.4 * COALESCE(sp.items_per_day_last_7_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 14 AND 27 THEN 0.4 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.6 * COALESCE(sp.items_per_day_last_7_days, 0) ELSE COALESCE(sp.items_per_day_last_7_days, 0) END, 7), sp.ewma_items_per_day = ROUND(CASE WHEN COALESCE(v.product_age, 0) > 365 THEN 0.5 * COALESCE(sp.items_per_day_last_7_days, 0) + 0.5 * COALESCE(sp.items_per_day_next_7_days, 0) + 0.25 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.25 * COALESCE(sp.items_per_day_next_14_days, 0) + 0.1 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.1 * COALESCE(sp.items_per_day_next_28_days, 0) + 0.07 * COALESCE(sp.items_per_day_last_56_days, 0) + 0.07 * COALESCE(sp.items_per_day_next_56_days, 0) + 0.03 * COALESCE(sp.items_per_day_last_175_days, 0) + 0.03 * COALESCE(sp.items_per_day_next_175_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 175 AND 365 THEN 0.5 * COALESCE(sp.items_per_day_last_7_days, 0) + 0.25 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.15 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.07 * COALESCE(sp.items_per_day_last_56_days, 0) + 0.03 * COALESCE(sp.items_per_day_last_175_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 56 AND 174 THEN 0.5 * COALESCE(sp.items_per_day_last_7_days, 0) + 0.25 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.15 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.07 * COALESCE(sp.items_per_day_last_56_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 28 AND 55 THEN 0.5 * COALESCE(sp.items_per_day_last_28_days, 0) + 0.3 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.2 * COALESCE(sp.items_per_day_last_7_days, 0) WHEN COALESCE(v.product_age, 0) BETWEEN 14 AND 27 THEN 0.65 * COALESCE(sp.items_per_day_last_14_days, 0) + 0.35 * COALESCE(sp.items_per_day_last_7_days, 0) ELSE COALESCE(sp.items_per_day_last_7_days, 0) END, 7) WHERE sp.prediction_date = @asof; -- --------------------------------------------------------------------- -- 5) Optimistic / pessimistic / average estimators (over the 10 windows). -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor SET optimistic_estimator_value = NULLIF(GREATEST( COALESCE(items_per_day_last_175_days, -1e15), COALESCE(items_per_day_last_56_days, -1e15), COALESCE(items_per_day_last_28_days, -1e15), COALESCE(items_per_day_last_14_days, -1e15), COALESCE(items_per_day_last_7_days, -1e15), COALESCE(items_per_day_next_7_days, -1e15), COALESCE(items_per_day_next_14_days, -1e15), COALESCE(items_per_day_next_28_days, -1e15), COALESCE(items_per_day_next_56_days, -1e15), COALESCE(items_per_day_next_175_days, -1e15)), -1e15), pessimistic_estimator_value = NULLIF(LEAST( COALESCE(items_per_day_last_175_days, 1e15), COALESCE(items_per_day_last_56_days, 1e15), COALESCE(items_per_day_last_28_days, 1e15), COALESCE(items_per_day_last_14_days, 1e15), COALESCE(items_per_day_last_7_days, 1e15), COALESCE(items_per_day_next_7_days, 1e15), COALESCE(items_per_day_next_14_days, 1e15), COALESCE(items_per_day_next_28_days, 1e15), COALESCE(items_per_day_next_56_days, 1e15), COALESCE(items_per_day_next_175_days, 1e15)), 1e15), average_estimator_value = ( COALESCE(items_per_day_last_175_days, 0) + COALESCE(items_per_day_last_56_days, 0) + COALESCE(items_per_day_last_28_days, 0) + COALESCE(items_per_day_last_14_days, 0) + COALESCE(items_per_day_last_7_days, 0) + COALESCE(items_per_day_next_7_days, 0) + COALESCE(items_per_day_next_14_days, 0) + COALESCE(items_per_day_next_28_days, 0) + COALESCE(items_per_day_next_56_days, 0) + COALESCE(items_per_day_next_175_days, 0) ) / NULLIF( (items_per_day_last_175_days IS NOT NULL) + (items_per_day_last_56_days IS NOT NULL) + (items_per_day_last_28_days IS NOT NULL) + (items_per_day_last_14_days IS NOT NULL) + (items_per_day_last_7_days IS NOT NULL) + (items_per_day_next_7_days IS NOT NULL) + (items_per_day_next_14_days IS NOT NULL) + (items_per_day_next_28_days IS NOT NULL) + (items_per_day_next_56_days IS NOT NULL) + (items_per_day_next_175_days IS NOT NULL), 0), linear_estimator_value = linear_items_per_day, ewma_estimator_value = ewma_items_per_day WHERE prediction_date = @asof; UPDATE wp_aieo_sales_predictor SET optimistic_estimator_name = CASE WHEN optimistic_estimator_value = items_per_day_last_7_days THEN 'last_7_days' WHEN optimistic_estimator_value = items_per_day_last_14_days THEN 'last_14_days' WHEN optimistic_estimator_value = items_per_day_last_28_days THEN 'last_28_days' WHEN optimistic_estimator_value = items_per_day_last_56_days THEN 'last_56_days' WHEN optimistic_estimator_value = items_per_day_last_175_days THEN 'last_175_days' WHEN optimistic_estimator_value = items_per_day_next_7_days THEN 'next_7_days' WHEN optimistic_estimator_value = items_per_day_next_14_days THEN 'next_14_days' WHEN optimistic_estimator_value = items_per_day_next_28_days THEN 'next_28_days' WHEN optimistic_estimator_value = items_per_day_next_56_days THEN 'next_56_days' WHEN optimistic_estimator_value = items_per_day_next_175_days THEN 'next_175_days' ELSE NULL END, pessimistic_estimator_name = CASE WHEN pessimistic_estimator_value = items_per_day_last_7_days THEN 'last_7_days' WHEN pessimistic_estimator_value = items_per_day_last_14_days THEN 'last_14_days' WHEN pessimistic_estimator_value = items_per_day_last_28_days THEN 'last_28_days' WHEN pessimistic_estimator_value = items_per_day_last_56_days THEN 'last_56_days' WHEN pessimistic_estimator_value = items_per_day_last_175_days THEN 'last_175_days' WHEN pessimistic_estimator_value = items_per_day_next_7_days THEN 'next_7_days' WHEN pessimistic_estimator_value = items_per_day_next_14_days THEN 'next_14_days' WHEN pessimistic_estimator_value = items_per_day_next_28_days THEN 'next_28_days' WHEN pessimistic_estimator_value = items_per_day_next_56_days THEN 'next_56_days' WHEN pessimistic_estimator_value = items_per_day_next_175_days THEN 'next_175_days' ELSE NULL END WHERE prediction_date = @asof; -- --------------------------------------------------------------------- -- 6) Single-window run-out + days-to-sell-10 (linear & EWMA references). -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor SET days_until_stock_run_out_linear = CASE WHEN linear_items_per_day > 0 AND current_stock > 0 THEN ROUND(current_stock / linear_items_per_day, 7) ELSE NULL END, days_until_stock_run_out_ewma = CASE WHEN ewma_items_per_day > 0 AND current_stock > 0 THEN ROUND(current_stock / ewma_items_per_day, 7) ELSE NULL END, estimated_zero_stock_date = CASE WHEN ewma_items_per_day > 0 AND current_stock > 0 AND current_stock / ewma_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(current_stock / ewma_items_per_day) DAY ELSE NULL END, days_to_sell_10_units_linear = CASE WHEN linear_items_per_day > 0 THEN ROUND(10 / linear_items_per_day, 7) ELSE NULL END, days_to_sell_10_units_ewma = CASE WHEN ewma_items_per_day > 0 THEN ROUND(10 / ewma_items_per_day, 7) ELSE NULL END, estimated_date_to_sell_10_units = CASE WHEN ewma_items_per_day > 0 AND 10 / ewma_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(10 / ewma_items_per_day) DAY ELSE NULL END WHERE prediction_date = @asof; -- Resolve per-product inverse-error method weights (category, else global). -- MariaDB forbids referencing a TEMPORARY table twice in one query, so we -- flatten category+global into one row per (grain, product_id) here; the -- accuracy table is a base table and may be read twice in the two subqueries. DROP TEMPORARY TABLE IF EXISTS tmp_pw; CREATE TEMPORARY TABLE tmp_pw AS SELECT sp.grain, sp.product_id, COALESCE(ac.w_linear, ag.w_linear) AS w_linear, COALESCE(ac.w_ewma, ag.w_ewma) AS w_ewma, COALESCE(ac.w_average, ag.w_average) AS w_average, COALESCE(ac.mape_ewma, ag.mape_ewma) AS mape_ewma FROM wp_aieo_sales_predictor sp JOIN tmp_vit v ON v.product_id = sp.product_id LEFT JOIN ( SELECT grain, segment_key, MAX(CASE WHEN method = 'linear' THEN 1 / (ewma_abs_err + 0.5) END) AS w_linear, MAX(CASE WHEN method = 'ewma' THEN 1 / (ewma_abs_err + 0.5) END) AS w_ewma, MAX(CASE WHEN method = 'average' THEN 1 / (ewma_abs_err + 0.5) END) AS w_average, MAX(CASE WHEN method = 'ewma' THEN mape END) AS mape_ewma FROM wp_aieo_sales_predictor_accuracy WHERE segment_type = 'category' GROUP BY grain, segment_key ) ac ON ac.grain = sp.grain AND ac.segment_key = v.category_key LEFT JOIN ( SELECT grain, MAX(CASE WHEN method = 'linear' THEN 1 / (ewma_abs_err + 0.5) END) AS w_linear, MAX(CASE WHEN method = 'ewma' THEN 1 / (ewma_abs_err + 0.5) END) AS w_ewma, MAX(CASE WHEN method = 'average' THEN 1 / (ewma_abs_err + 0.5) END) AS w_average, MAX(CASE WHEN method = 'ewma' THEN mape END) AS mape_ewma FROM wp_aieo_sales_predictor_accuracy WHERE segment_type = 'global' GROUP BY grain ) ag ON ag.grain = sp.grain WHERE sp.prediction_date = @asof; ALTER TABLE tmp_pw ADD KEY k (grain, product_id); -- --------------------------------------------------------------------- -- 7) Reliability + measured confidence. reliability = data sufficiency -- (transactions saturate towards 1); confidence folds in the segment's -- measured EWMA accuracy (MAPE) when the scoreboard has matured. -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor sp LEFT JOIN tmp_pw w ON w.grain = sp.grain AND w.product_id = sp.product_id SET sp.reliability_score = ROUND(sp.transactions_total / (sp.transactions_total + 8), 4), sp.prediction_confidence = ROUND( (sp.transactions_total / (sp.transactions_total + 8)) * (1 - LEAST(GREATEST(COALESCE(w.mape_ewma, 50) / 100, 0), 0.9)), 4) WHERE sp.prediction_date = @asof; -- --------------------------------------------------------------------- -- 8) Adaptive (self-tuned) rate: inverse-error-weighted ensemble of the -- linear/ewma/average methods, weights from the per-segment scoreboard -- (category, else global). Falls back to EWMA until the board matures. -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor sp LEFT JOIN tmp_pw w ON w.grain = sp.grain AND w.product_id = sp.product_id SET sp.chosen_items_per_day = CASE WHEN COALESCE(w.w_linear, w.w_ewma, w.w_average) IS NULL THEN COALESCE(sp.ewma_items_per_day, sp.linear_items_per_day, sp.average_estimator_value, 0) ELSE ROUND(( COALESCE(sp.linear_items_per_day, 0) * (CASE WHEN sp.linear_items_per_day IS NOT NULL THEN COALESCE(w.w_linear, 0) ELSE 0 END) + COALESCE(sp.ewma_items_per_day, 0) * (CASE WHEN sp.ewma_items_per_day IS NOT NULL THEN COALESCE(w.w_ewma, 0) ELSE 0 END) + COALESCE(sp.average_estimator_value, 0) * (CASE WHEN sp.average_estimator_value IS NOT NULL THEN COALESCE(w.w_average, 0) ELSE 0 END) ) / NULLIF( (CASE WHEN sp.linear_items_per_day IS NOT NULL THEN COALESCE(w.w_linear, 0) ELSE 0 END) + (CASE WHEN sp.ewma_items_per_day IS NOT NULL THEN COALESCE(w.w_ewma, 0) ELSE 0 END) + (CASE WHEN sp.average_estimator_value IS NOT NULL THEN COALESCE(w.w_average, 0) ELSE 0 END), 0), 7) END, sp.chosen_method = CASE WHEN COALESCE(w.w_linear, w.w_ewma, w.w_average) IS NULL THEN 'ewma_fallback' WHEN COALESCE(w.w_ewma, 0) >= COALESCE(w.w_linear, 0) AND COALESCE(w.w_ewma, 0) >= COALESCE(w.w_average, 0) THEN 'ensemble:ewma' WHEN COALESCE(w.w_linear, 0) >= COALESCE(w.w_average, 0) THEN 'ensemble:linear' ELSE 'ensemble:average' END WHERE sp.prediction_date = @asof; -- --------------------------------------------------------------------- -- 9) Substitution-aware parent blend (variation grain only). A thinly -- traded variation's own history understates demand because stock-outs -- push buyers to siblings; blend toward the parent's rate apportioned -- by this variation's historical share. Weight = its reliability. -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor vr JOIN wp_aieo_sales_predictor pr ON pr.grain = 'parent' AND pr.prediction_date = vr.prediction_date AND pr.product_id = vr.parent_product_id SET vr.variation_share_of_parent = CASE WHEN pr.total_product_sales > 0 THEN LEAST(1, vr.total_product_sales / pr.total_product_sales) ELSE NULL END, vr.chosen_items_per_day = ROUND( vr.reliability_score * COALESCE(vr.chosen_items_per_day, 0) + (1 - vr.reliability_score) * COALESCE(pr.chosen_items_per_day, 0) * (CASE WHEN pr.total_product_sales > 0 THEN LEAST(1, vr.total_product_sales / pr.total_product_sales) ELSE 0 END), 7), vr.uses_parent_signal = CASE WHEN (1 - vr.reliability_score) > 0.5 THEN 1 ELSE 0 END WHERE vr.grain = 'variation' AND vr.prediction_date = @asof AND vr.reliability_score < 1; -- --------------------------------------------------------------------- -- 10) Final chosen run-out + costs + reorder, derived from chosen rate. -- reorder_date = projected zero-stock day; reorder_quantity = the -- chosen rate over @reorder_h days of cover. -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor SET chosen_estimator_value = chosen_items_per_day, chosen_days_until_runout = CASE WHEN chosen_items_per_day > 0 AND current_stock > 0 THEN ROUND(current_stock / chosen_items_per_day, 7) ELSE NULL END, chosen_zero_stock_date = CASE WHEN chosen_items_per_day > 0 AND current_stock > 0 AND current_stock / chosen_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(current_stock / chosen_items_per_day) DAY ELSE NULL END, optimistic_cost = CASE WHEN current_stock < 1 AND optimistic_estimator_value IS NOT NULL THEN ROUND(optimistic_estimator_value * out_of_stock_days * price, 7) ELSE NULL END, pessimistic_cost = CASE WHEN current_stock < 1 AND pessimistic_estimator_value IS NOT NULL THEN ROUND(pessimistic_estimator_value * out_of_stock_days * price, 7) ELSE NULL END, average_cost = CASE WHEN current_stock < 1 AND average_estimator_value IS NOT NULL THEN ROUND(average_estimator_value * out_of_stock_days * price, 7) ELSE NULL END, reorder_date = CASE WHEN current_stock < 1 THEN CAST(@asof AS DATE) WHEN chosen_items_per_day > 0 AND current_stock > 0 AND current_stock / chosen_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(current_stock / chosen_items_per_day) DAY ELSE NULL END, reorder_quantity = CASE WHEN chosen_items_per_day > 0 THEN ROUND(chosen_items_per_day * @reorder_h, 0) ELSE NULL END WHERE prediction_date = @asof; -- --------------------------------------------------------------------- -- 11) Price-change tracking vs the previous snapshot (history) + OOS -- repetition + price-update counts over the recent history window. -- --------------------------------------------------------------------- UPDATE wp_aieo_sales_predictor sp LEFT JOIN tmp_prev pv ON pv.grain = sp.grain AND pv.product_id = sp.product_id SET sp.price_before_adjustment = CASE WHEN pv.prev_price IS NOT NULL AND pv.prev_price <> sp.price THEN pv.prev_price ELSE NULL END, sp.price_adjustment_percentage = CASE WHEN pv.prev_price IS NOT NULL AND pv.prev_price <> 0 AND pv.prev_price <> sp.price THEN ROUND((sp.price - pv.prev_price) / pv.prev_price * 100, 7) ELSE NULL END, sp.latest_price_adjustment_date = CASE WHEN pv.prev_price IS NOT NULL AND pv.prev_price <> sp.price THEN CAST(@asof AS DATE) ELSE NULL END, sp.price_update_repetitions = (SELECT COUNT(DISTINCT h.price) FROM wp_aieo_sales_predictor_history h WHERE h.grain = sp.grain AND h.product_id = sp.product_id AND h.snapshot_date >= @asof - INTERVAL 365 DAY), sp.out_of_stock_repetitions = (SELECT COUNT(*) FROM wp_aieo_sales_predictor_history h WHERE h.grain = sp.grain AND h.product_id = sp.product_id AND h.current_stock < 1 AND h.snapshot_date >= @asof - INTERVAL 90 DAY) WHERE sp.prediction_date = @asof; -- --------------------------------------------------------------------- -- 12) Trim to the requested grain mode. -- --------------------------------------------------------------------- DELETE FROM wp_aieo_sales_predictor WHERE prediction_date = @asof AND ((@grain_mode = 'variation' AND grain <> 'variation') OR (@grain_mode = 'parent' AND grain <> 'parent')); -- --------------------------------------------------------------------- -- 13) Append today's snapshot to history (backtest log) + prune > 180d. -- --------------------------------------------------------------------- INSERT INTO wp_aieo_sales_predictor_history (grain, product_id, snapshot_date, current_stock, price, ipd_linear, ipd_ewma, ipd_average, chosen_ipd, chosen_method, est_zero_stock_date) SELECT grain, product_id, @asof, current_stock, price, linear_items_per_day, ewma_items_per_day, average_estimator_value, chosen_items_per_day, chosen_method, chosen_zero_stock_date FROM wp_aieo_sales_predictor WHERE prediction_date = @asof ON DUPLICATE KEY UPDATE current_stock = VALUES(current_stock), price = VALUES(price), ipd_linear = VALUES(ipd_linear), ipd_ewma = VALUES(ipd_ewma), ipd_average = VALUES(ipd_average), chosen_ipd = VALUES(chosen_ipd), chosen_method = VALUES(chosen_method), est_zero_stock_date = VALUES(est_zero_stock_date); DELETE FROM wp_aieo_sales_predictor_history WHERE snapshot_date < @asof - INTERVAL 180 DAY; DROP TEMPORARY TABLE IF EXISTS tmp_bt; DROP TEMPORARY TABLE IF EXISTS tmp_pw; DROP TEMPORARY TABLE IF EXISTS tmp_prev; DROP TEMPORARY TABLE IF EXISTS tmp_vit; DROP TEMPORARY TABLE IF EXISTS tmp_pstock; AIEO_JOB_SQL; $b['supplier_sku_sync'] = <<<'AIEO_JOB_SQL' TRUNCATE TABLE wp_aieo_dmm_supplier_sku_map; INSERT INTO wp_aieo_dmm_supplier_sku_map (sku, sku_norm, product_id, supplier_erp_id, source, updated_at) SELECT c.SKU, UPPER(REPLACE(c.SKU, ' ', '')), pv.product_id, CAST(c.SupplierId AS CHAR), 'erp', UTC_TIMESTAMP() FROM wp_s1erp_temp_unique_product_catalogue_table c LEFT JOIN wp_aieo_core_product_vitals pv ON pv.sku = c.SKU AND COALESCE(pv.is_variation_parent, 0) = 0 WHERE c.SKU IS NOT NULL AND TRIM(c.SKU) <> '' AND c.SupplierId IS NOT NULL AND c.SupplierId > 0 ON DUPLICATE KEY UPDATE sku_norm = VALUES(sku_norm), product_id = VALUES(product_id), supplier_erp_id = VALUES(supplier_erp_id), updated_at = VALUES(updated_at); -- Refresh stats after the TRUNCATE+rebuild so the later preorder_vitals job's -- `m.sku = pv.sku` join plans against real cardinality (stale-stats guard). ANALYZE TABLE wp_aieo_dmm_supplier_sku_map; AIEO_JOB_SQL; $b['store_sales_stats'] = <<<'AIEO_JOB_SQL' -- Branch shape N (v30): this build carries the CHANNEL columns only (total / -- eshop_soft1, plus the eshop_woo UPDATE below). Physical branches are rows -- in wp_aieo_dmm_branch_sales, built further down from the registry-derived -- matcher table — a new location needs no sproc reinstall and no DDL. The -- eshop marker is the invoice type (@aieo_eshop_it; run_job always sets it, -- and a raw CALL COALESCEs to the legacy Soft1 default). SET @aieo_ss_eit = COALESCE(@aieo_eshop_it, '7069'); TRUNCATE TABLE wp_aieo_dmm_store_sales; INSERT INTO wp_aieo_dmm_store_sales (name, product_id, image_url, supplier, brand, sku, sku_norm, gtin, total_30d, total_60d, trend_of_total, eshop_soft1_30d, eshop_soft1_60d, trend_of_eshop_soft1, created, modified) SELECT COALESCE(NULLIF(pv.product_name, ''), pv.name) AS name, pv.product_id, LEFT(pv.image_url, 500), pv.supplier, pv.brand, a.SKU, UPPER(REPLACE(a.SKU, ' ', '')), pv.gtin, a.total_30d, a.total_60d, CASE WHEN a.total_60d = 0 THEN NULL ELSE ROUND(((a.total_30d/30)-(a.total_60d/60))/(a.total_60d/60)*100, 2) END, a.eshop_30d, a.eshop_60d, CASE WHEN a.eshop_60d = 0 THEN NULL ELSE ROUND(((a.eshop_30d/30)-(a.eshop_60d/60))/(a.eshop_60d/60)*100, 2) END, UTC_TIMESTAMP(), UTC_TIMESTAMP() FROM ( SELECT t.SKU, SUM(CASE WHEN t.OrderDate >= UTC_DATE() - INTERVAL 30 DAY THEN t.qty ELSE 0 END) AS total_30d, SUM(CASE WHEN t.OrderDate >= UTC_DATE() - INTERVAL 60 DAY THEN t.qty ELSE 0 END) AS total_60d, SUM(CASE WHEN t.OrderDate >= UTC_DATE() - INTERVAL 30 DAY AND t.is_eshop THEN t.qty ELSE 0 END) AS eshop_30d, SUM(CASE WHEN t.OrderDate >= UTC_DATE() - INTERVAL 60 DAY AND t.is_eshop THEN t.qty ELSE 0 END) AS eshop_60d FROM ( SELECT s.SKU, s.OrderDate, CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3)) AS qty, -- The ERP eshop channel = the dedicated invoice type, wherever it -- rides (generalises the legacy Piraeus-only 7069 flag). ( @aieo_ss_eit <> '' AND s.InvoiceType IS NOT NULL AND CAST(s.InvoiceType AS CHAR) = @aieo_ss_eit ) AS is_eshop FROM wp_s1erp_temp_store_orders_table s WHERE s.OrderDate >= UTC_DATE() - INTERVAL 60 DAY ) t GROUP BY t.SKU HAVING total_60d <> 0 ) a LEFT JOIN wp_aieo_core_product_vitals pv ON pv.sku = a.SKU; -- Refresh stats after the TRUNCATE+rebuild — the UPDATE…JOIN below plans against ss, -- just rebuilt (~1-row stats otherwise → flip to full × derived-w). Stale-stats guard. ANALYZE TABLE wp_aieo_dmm_store_sales; -- eShop (WooCommerce) split from AIEO's own order analytics. UPDATE wp_aieo_dmm_store_sales ss JOIN ( SELECT opa.effective_pid AS pid, SUM(CASE WHEN opa.date_created_gmt >= UTC_DATE() - INTERVAL 30 DAY THEN opa.quantity ELSE 0 END) AS w30, SUM(CASE WHEN opa.date_created_gmt >= UTC_DATE() - INTERVAL 60 DAY THEN opa.quantity ELSE 0 END) AS w60 FROM wp_aieo_order_products_analytics opa WHERE opa.date_created_gmt >= UTC_DATE() - INTERVAL 60 DAY GROUP BY pid ) w ON w.pid = ss.product_id SET ss.eshop_woo_30d = w.w30, ss.eshop_woo_60d = w.w60, ss.trend_of_eshop_woo = CASE WHEN w.w60 = 0 THEN NULL ELSE ROUND(((w.w30/30)-(w.w60/60))/(w.w60/60)*100, 2) END; -- --------------------------------------------------------------------- -- The NARROW per-branch sales table — the ONLY per-branch sales shape -- (v30; the v29 dual-write reconciled exactly against the wide columns -- on 3.67M live rows before they were dropped). One row per (branch, -- sku_norm); an eshop-invoice row never counts as physical. -- --------------------------------------------------------------------- -- Resolve each DISTINCT staging branch string to a registry branch id ONCE -- (millions of rows, a handful of distinct branch strings), then equi-join -- rows to the map — plan-stable, no per-row LIKE evaluation. A branch -- string matching several registries deterministically takes the lowest id. DROP TEMPORARY TABLE IF EXISTS tmp_ss_branch_map; CREATE TEMPORARY TABLE tmp_ss_branch_map AS SELECT d.Branch, MIN(bm.branch_id) AS branch_id FROM (SELECT DISTINCT s.Branch FROM wp_s1erp_temp_store_orders_table s WHERE s.OrderDate >= UTC_DATE() - INTERVAL 60 DAY AND s.Branch IS NOT NULL) d JOIN wp_aieo_dmm_branch_matchers bm ON d.Branch LIKE CONCAT('%', bm.pattern, '%') GROUP BY d.Branch; DELETE FROM wp_aieo_dmm_branch_sales; INSERT INTO wp_aieo_dmm_branch_sales (branch_id, sku_norm, product_id, sales_30d, sales_60d, trend, snapshot_date) SELECT a.branch_id, a.sku_norm, MAX(pv.product_id), a.q30, a.q60, CASE WHEN a.q60 = 0 THEN NULL ELSE ROUND(((a.q30/30)-(a.q60/60))/(a.q60/60)*100, 2) END, UTC_DATE() FROM ( SELECT bmap.branch_id, UPPER(REPLACE(CAST(s.SKU AS CHAR), ' ', '')) AS sku_norm, MAX(CAST(s.SKU AS CHAR)) AS raw_sku, SUM(CASE WHEN s.OrderDate >= UTC_DATE() - INTERVAL 30 DAY THEN CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3)) ELSE 0 END) AS q30, SUM(CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3))) AS q60 FROM wp_s1erp_temp_store_orders_table s JOIN tmp_ss_branch_map bmap ON bmap.Branch = s.Branch WHERE s.OrderDate >= UTC_DATE() - INTERVAL 60 DAY AND s.SKU IS NOT NULL AND s.SKU <> '' -- A row carrying the eshop invoice type is the eshop channel, never a -- physical branch (generalises the legacy Piraeus-only 7069 guard). AND (@aieo_ss_eit = '' OR s.InvoiceType IS NULL OR CAST(s.InvoiceType AS CHAR) <> @aieo_ss_eit) GROUP BY bmap.branch_id, sku_norm HAVING q60 <> 0 OR q30 <> 0 ) a LEFT JOIN wp_aieo_core_product_vitals pv ON pv.sku = a.raw_sku GROUP BY a.branch_id, a.sku_norm, a.q30, a.q60; DROP TEMPORARY TABLE IF EXISTS tmp_ss_branch_map; ANALYZE TABLE wp_aieo_dmm_branch_sales; AIEO_JOB_SQL; $b['variations_stock'] = <<<'AIEO_JOB_SQL' TRUNCATE TABLE wp_aieo_variations_stock_condition; INSERT INTO wp_aieo_variations_stock_condition (product_id, name, brand, menu_category, sub_category, image_url, total_variations, instock_variations, out_of_stock_variations, pct_out_of_stock, latest_sale_gmt, created) SELECT agg.parent_id, COALESCE(NULLIF(pv.product_name, ''), NULLIF(pv.name, ''), agg.parent_title), pv.brand, pv.menu_category, pv.sub_category, LEFT(pv.image_url, 500), agg.total, agg.instock, agg.oos, -- agg.total >= 1 by construction (every group has at least one row), so -- this can never divide by zero — the B13/B58 class stays impossible here. ROUND(agg.oos * 100 / agg.total, 2), ls.latest_sale_gmt, UTC_TIMESTAMP() FROM ( SELECT v.post_parent AS parent_id, MAX(par.post_title) AS parent_title, COUNT(*) AS total, SUM(ml.stock_status = 'instock') AS instock, SUM(ml.stock_status = 'outofstock') AS oos FROM wp_posts v JOIN wp_posts par ON par.ID = v.post_parent AND par.post_type = 'product' AND par.post_status = 'publish' JOIN wp_wc_product_meta_lookup ml ON ml.product_id = v.ID WHERE v.post_type = 'product_variation' AND v.post_status = 'publish' GROUP BY v.post_parent ) agg LEFT JOIN wp_aieo_core_product_vitals pv ON pv.product_id = agg.parent_id LEFT JOIN ( SELECT l.product_id, MAX(o.date_created_gmt) AS latest_sale_gmt FROM wp_wc_order_product_lookup l JOIN wp_wc_orders o ON o.id = l.order_id WHERE o.status NOT IN ('wc-cancelled','wc-failed','wc-checkout-draft','auto-draft','trash') GROUP BY l.product_id ) ls ON ls.product_id = agg.parent_id; ANALYZE TABLE wp_aieo_variations_stock_condition; AIEO_JOB_SQL; $b['preorder_vitals'] = <<<'AIEO_JOB_SQL' TRUNCATE TABLE wp_aieo_dmm_preorder_vitals; INSERT INTO wp_aieo_dmm_preorder_vitals (product_id, parent_id, name, image_url, parent_image_url, brand, menu_category, head_category, sub_category, supplier_id, supplier, sku, parent_sku, gtin, vat, sku_norm, created, modified) SELECT pv.product_id, pv.parent_id, COALESCE(NULLIF(pv.product_name, ''), pv.name), LEFT(pv.image_url, 500), NULL, pv.brand, pv.menu_category, pv.head_category, pv.sub_category, m.supplier_term_id, t.name, pv.sku, pv.parent_sku, pv.gtin, NULL, UPPER(REPLACE(pv.sku, ' ', '')), UTC_TIMESTAMP(), UTC_TIMESTAMP() FROM wp_aieo_core_product_vitals pv JOIN wp_aieo_dmm_supplier_sku_map m ON m.sku = pv.sku LEFT JOIN wp_terms t ON t.term_id = m.supplier_term_id WHERE pv.sku IS NOT NULL AND TRIM(pv.sku) <> '' AND m.supplier_term_id IS NOT NULL AND pv.status = 1 -- Only simple products + variations carry a real ERP SKU; variable-product -- parents have dummy Woo-only SKUs, so never build a preorder row for them. AND COALESCE(pv.is_variation_parent, 0) = 0; -- Refresh stats after the TRUNCATE+rebuild — the UPDATE…JOIN below joins the freshly -- built pv to the ERP catalogue on SKU. With fresh stats + the new KEY(sku) on this -- table, the join stays a keyed lookup instead of flipping to a keyless full scan. ANALYZE TABLE wp_aieo_dmm_preorder_vitals; -- Pull the ERP product name + VAT over the vitals name where available. UPDATE wp_aieo_dmm_preorder_vitals pv JOIN wp_s1erp_temp_unique_product_catalogue_table c ON c.SKU = pv.sku SET pv.name = COALESCE(NULLIF(c.Soft1ProductName, ''), pv.name), pv.vat = NULLIF(TRIM(c.VAT), ''); -- Branch shape N (v30): channel columns only — per-branch sales are read -- from the narrow wp_aieo_dmm_branch_sales at join time by the worksheet -- definitions and the board upsert, keyed on sku_norm + registry branch id. TRUNCATE TABLE wp_aieo_dmm_preorder_vitals_stats; INSERT INTO wp_aieo_dmm_preorder_vitals_stats (created, modified, name, snapshot_date, product_id, parent_id, image_url, parent_image_url, brand, menu_category, head_category, sub_category, supplier_id, supplier, sku, parent_sku, gtin, vat, sku_norm, total_30d, total_60d, trend_of_total, eshop_soft1_30d, eshop_soft1_60d, trend_of_eshop_soft1, eshop_woo_30d, eshop_woo_60d, trend_of_eshop_woo) SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP(), pv.name, UTC_DATE(), pv.product_id, pv.parent_id, pv.image_url, pv.parent_image_url, pv.brand, pv.menu_category, pv.head_category, pv.sub_category, pv.supplier_id, pv.supplier, pv.sku, pv.parent_sku, pv.gtin, pv.vat, pv.sku_norm, IFNULL(ss.total_30d,0), IFNULL(ss.total_60d,0), ss.trend_of_total, IFNULL(ss.eshop_soft1_30d,0), IFNULL(ss.eshop_soft1_60d,0), ss.trend_of_eshop_soft1, IFNULL(ss.eshop_woo_30d,0), IFNULL(ss.eshop_woo_60d,0), ss.trend_of_eshop_woo FROM wp_aieo_dmm_preorder_vitals pv LEFT JOIN wp_aieo_dmm_store_sales ss ON ss.product_id = pv.product_id; AIEO_JOB_SQL; $b['stock_recovery'] = <<<'AIEO_JOB_SQL' SET @pr_asof = COALESCE(@pr_asof, UTC_DATE()); SET @pr_recency_days = COALESCE(@pr_recency_days, 30); SET @pr_min_units = COALESCE(@pr_min_units, 2); SET @pr_min_days = COALESCE(@pr_min_days, 2); SET @pr_min_days_oos = COALESCE(@pr_min_days_oos, 3); SET @pr_qty_cap = COALESCE(@pr_qty_cap, 5); SET @pr_cover_days = COALESCE(@pr_cover_days, 14); SET @pr_cooldown_days = COALESCE(@pr_cooldown_days, 14); SET @pr_gap_min_units = COALESCE(@pr_gap_min_units, 2); SET @pr_gap_recency_days= COALESCE(@pr_gap_recency_days, 60); -- Branch shape N: the eshop invoice-type marker ('' = none; the COALESCE -- default keeps a raw CALL on legacy Soft1 behaviour — run_job always sets -- it) and the registry branch IDS the operator unticked from detection -- (CSV consumed via FIND_IN_SET; '' = count every branch). SET @sr_eit = COALESCE(@aieo_eshop_it, '7069'); SET @pr_branch_exclude = COALESCE(@pr_branch_exclude, ''); -- Anchor the catalog-gap "recent good sellers" window to the LAST DAY OF VALID -- ERP data rather than today, so a lagging feed still surfaces real sellers -- ("good sellers over the most recent period for which we have valid data"). SET @pr_data_asof = COALESCE((SELECT DATE(MAX(OrderDate)) FROM wp_s1erp_temp_store_orders_table), @pr_asof); -- Resolve each DISTINCT staging branch string to a registry branch id ONCE -- (millions of rows, a handful of distinct branch strings), then equi-join — -- plan-stable, no per-row LIKE. Rows whose string matches no enabled branch -- (or an excluded one) get NULL and are classified only by the legacy flags. DROP TEMPORARY TABLE IF EXISTS tmp_sr_branch_map; CREATE TEMPORARY TABLE tmp_sr_branch_map AS SELECT d.Branch, MIN(bm.branch_id) AS branch_id FROM (SELECT DISTINCT s.Branch FROM wp_s1erp_temp_store_orders_table s WHERE s.OrderDate >= LEAST(@pr_asof, @pr_data_asof) - INTERVAL GREATEST(@pr_recency_days, @pr_gap_recency_days, 180) DAY AND s.Branch IS NOT NULL) d JOIN wp_aieo_dmm_branch_matchers bm ON d.Branch LIKE CONCAT('%', bm.pattern, '%') WHERE NOT FIND_IN_SET(bm.branch_id, @pr_branch_exclude) GROUP BY d.Branch; -- Raw physical-store sale rows (last 180d), branch-flagged, branch-filter applied, -- normalised SKU. is_* are mutually exclusive (a row belongs to one branch); a row -- counts only if it matches an ENABLED physical branch (eshop/Soft1 rows excluded). DROP TEMPORARY TABLE IF EXISTS tmp_praw; CREATE TEMPORARY TABLE tmp_praw AS SELECT UPPER(REPLACE(CAST(s.SKU AS CHAR), ' ', '')) COLLATE utf8mb4_uca1400_ai_ci AS sku_norm, CAST(s.SKU AS CHAR) AS raw_sku, s.OrderDate, -- Transaction id: groups the line-items of one physical-store order/invoice -- so we can sum the WHOLE basket a product appeared in (the halo metric). s.OrderId AS order_id, CAST(REPLACE(COALESCE(s.Quantity, '0'), ',', '.') AS DECIMAL(12,3)) AS qty, -- Net line revenue. Net_Line_price is the reliably-populated column (per -- unit/line, ex-VAT); Line_price (gross, = net*VAT) is mostly NULL in the -- ERP feed, so COALESCE prefers Net_Line_price → real turnover, not €0. CAST(REPLACE(COALESCE(NULLIF(s.Net_Line_price, ''), s.Line_price, '0'), ',', '.') AS DECIMAL(14,2)) AS line_price, -- Branch shape N (v30): the registry-resolved branch id is THE branch -- classification (the per-slug LIKE flags are gone; the v29 dual-write -- reconciled the matcher against them exactly on live data first). An -- eshop-invoice row is never a physical branch (generalises the -- Piraeus-only 7069 guard). CASE WHEN (@sr_eit = '' OR s.InvoiceType IS NULL OR CAST(s.InvoiceType AS CHAR) <> @sr_eit) THEN bmap.branch_id ELSE NULL END AS branch_id -- DEDUP daily re-imports: the ERP sync re-appends every order each day (a fresh -- ContentLastUpdated + new Id, same OrderCode and lines), so a frozen / re-synced -- order can accumulate many identical copies and inflate EVERY turnover and unit -- figure. Keep only the LATEST import-day per order = its current state. This -- preserves legitimate one-line-per-unit purchases (all in the same import) while -- collapsing the duplicate daily copies. A no-op on cleanly once-imported orders. -- -- SINGLE-PASS window-function dedup: MAX(import_day) OVER (PARTITION BY OrderId) -- tags each order's latest-import day in ONE scan + sort; the outer keeps rows on -- that day. This REPLACES a correlated self-join to a per-OrderId MAX() derived -- table whose join predicate COALESCE(DATE(ContentLastUpdated),DATE(OrderDate)) = -- _import_day was a runtime function no index could serve — that plan flipped -- (stale stats after the daily re-import bulk-load) to a full 3.5M-row scan -- probing the derived table by OrderId, ~1.95 BILLION rows examined / ~489s. -- The window MAX is computed over the OrderDate window WITHOUT the SKU filter -- (identical to the old derived subquery); the SKU filter stays in the outer WHERE -- (identical to the old outer) — verified byte-for-byte equal on live data -- (282,304 rows, identical SUM(qty)/SUM(line_price)). Bounded ~2x table size, -- immune to the stats-driven plan flip. FROM ( SELECT s.SKU, s.OrderDate, s.OrderId, s.Quantity, s.Net_Line_price, s.Line_price, s.Branch, s.InvoiceType, COALESCE(DATE(s.ContentLastUpdated), DATE(s.OrderDate)) AS _row_import_day, MAX(COALESCE(DATE(s.ContentLastUpdated), DATE(s.OrderDate))) OVER (PARTITION BY s.OrderId) AS _max_import_day FROM wp_s1erp_temp_store_orders_table s WHERE s.OrderDate >= LEAST(@pr_asof, @pr_data_asof) - INTERVAL GREATEST(@pr_recency_days, @pr_gap_recency_days, 180) DAY ) s LEFT JOIN tmp_sr_branch_map bmap ON bmap.Branch = s.Branch WHERE s._row_import_day = s._max_import_day AND s.SKU IS NOT NULL AND s.SKU <> '' -- A row belongs in praw iff the registry matcher resolves it to an enabled, -- counted branch — any shop's branches, any number of them. HAVING branch_id IS NOT NULL; ALTER TABLE tmp_praw ADD KEY k (sku_norm); ALTER TABLE tmp_praw ADD KEY k_order (order_id); -- --------------------------------------------------------------------- -- (A) RECOVERY: published, sellable products currently OOS in the eshop, -- with their OOS-since date (history streak, else last eshop sale). -- --------------------------------------------------------------------- DROP TEMPORARY TABLE IF EXISTS tmp_oos; CREATE TEMPORARY TABLE tmp_oos AS SELECT v.product_id, v.parent_id, v.sku, UPPER(REPLACE(CAST(v.sku AS CHAR), ' ', '')) COLLATE utf8mb4_uca1400_ai_ci AS sku_norm, COALESCE(NULLIF(v.product_name, ''), v.name) AS name, LEFT(v.image_url, 500) AS image_url, CAST(v.stock AS SIGNED) AS eshop_stock, v.price AS eshop_price, CASE WHEN h.last_instock IS NOT NULL THEN DATE(h.last_instock + INTERVAL 1 DAY) WHEN v.product_latest_sale_date IS NOT NULL THEN DATE(v.product_latest_sale_date) ELSE NULL END AS oos_since, CASE WHEN h.last_instock IS NOT NULL THEN 'history' WHEN v.product_latest_sale_date IS NOT NULL THEN 'latest_sale' ELSE 'unknown' END AS oos_source, GREATEST( COALESCE(DATEDIFF(@pr_asof, h.last_instock), 0), COALESCE(DATEDIFF(@pr_asof, v.product_latest_sale_date), 0) ) AS days_oos FROM wp_aieo_core_product_vitals v LEFT JOIN ( SELECT product_id, MAX(snapshot_date) AS last_instock FROM wp_aieo_sales_predictor_history WHERE current_stock >= 1 AND snapshot_date <= @pr_asof GROUP BY product_id ) h ON h.product_id = v.product_id WHERE COALESCE(v.is_variation_parent, 0) = 0 AND v.status = 1 AND (v.stock < 1 OR v.stock_status = 'outofstock') AND v.sku IS NOT NULL AND v.sku <> ''; ALTER TABLE tmp_oos ADD KEY k (sku_norm); TRUNCATE TABLE wp_aieo_dmm_stock_recovery_candidates; INSERT INTO wp_aieo_dmm_stock_recovery_candidates (product_id, parent_id, is_variation, sku, sku_norm, name, image_url, oos_since, oos_source, days_oos, eshop_stock, phys_units_after, phys_units_recent, phys_sale_days, last_phys_sale_date, phys_turnover_after, phys_turnover_recent, eshop_price, status, computed_at) SELECT o.product_id, o.parent_id, CASE WHEN o.parent_id IS NOT NULL AND o.parent_id <> 0 AND o.parent_id <> o.product_id THEN 1 ELSE 0 END, o.sku, o.sku_norm, o.name, o.image_url, o.oos_since, o.oos_source, o.days_oos, o.eshop_stock, COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') THEN r.qty ELSE 0 END), 0), COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') AND r.OrderDate >= @pr_asof - INTERVAL @pr_recency_days DAY THEN r.qty ELSE 0 END), 0), COUNT(DISTINCT CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') THEN DATE(r.OrderDate) END), MAX(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') THEN DATE(r.OrderDate) END), COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') THEN r.line_price ELSE 0 END), 0), COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') AND r.OrderDate >= @pr_asof - INTERVAL @pr_recency_days DAY THEN r.line_price ELSE 0 END), 0), o.eshop_price, 'pending', UTC_TIMESTAMP() FROM tmp_oos o JOIN tmp_praw r ON r.sku_norm = o.sku_norm GROUP BY o.product_id, o.parent_id, o.sku, o.sku_norm, o.name, o.image_url, o.oos_since, o.oos_source, o.days_oos, o.eshop_stock, o.eshop_price HAVING SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') THEN r.qty ELSE 0 END) > 0; -- Branch shape N: the NARROW per-branch units — one row per (candidate, -- branch), keyed by the candidate row's own id (units = since-OOS, -- units_recent = the recency window; the pickers' view). Rebuilt with its -- parent every run; the append-only 'event' scope is written by PHP at -- apply time and never touched here. Zero-rows are omitted (a missing row -- reads as 0). DELETE FROM wp_aieo_dmm_branch_phys WHERE scope IN ('recovery', 'gap'); INSERT INTO wp_aieo_dmm_branch_phys (scope, ref_id, branch_id, units, units_recent) SELECT 'recovery', c.id, r.branch_id, COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(c.oos_since, '1900-01-01') THEN r.qty ELSE 0 END), 0), COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(c.oos_since, '1900-01-01') AND r.OrderDate >= @pr_asof - INTERVAL @pr_recency_days DAY THEN r.qty ELSE 0 END), 0) FROM wp_aieo_dmm_stock_recovery_candidates c JOIN tmp_praw r ON r.sku_norm = c.sku_norm COLLATE utf8mb4_uca1400_ai_ci WHERE r.branch_id IS NOT NULL GROUP BY c.id, r.branch_id HAVING SUM(CASE WHEN r.OrderDate > COALESCE(c.oos_since, '1900-01-01') THEN r.qty ELSE 0 END) <> 0 OR SUM(CASE WHEN r.OrderDate > COALESCE(c.oos_since, '1900-01-01') AND r.OrderDate >= @pr_asof - INTERVAL @pr_recency_days DAY THEN r.qty ELSE 0 END) <> 0; -- Derived metrics: branch count (from the narrow rows just written), -- velocity, suggested qty. UPDATE wp_aieo_dmm_stock_recovery_candidates c LEFT JOIN ( SELECT ref_id, SUM(units > 0) AS n FROM wp_aieo_dmm_branch_phys WHERE scope = 'recovery' GROUP BY ref_id ) bc ON bc.ref_id = c.id SET c.phys_branch_count = COALESCE(bc.n, 0), c.velocity_per_day = ROUND(c.phys_units_recent / GREATEST(@pr_recency_days, 1), 4); UPDATE wp_aieo_dmm_stock_recovery_candidates SET -- Rate-based: cover-days worth of demand at the recent velocity, floored at -- 1 and capped at qty_cap. We deliberately do NOT use the raw recent TOTAL -- (phys_units_recent) as a floor — that pushed slow movers straight to the -- cap. Fast movers still reach the cap on velocity alone, which is correct. suggested_restock_qty = LEAST(GREATEST(CEIL(velocity_per_day * @pr_cover_days), 1), @pr_qty_cap); -- Basket-turnover halo: the total value of the whole physical-store orders -- (baskets) that contained each product, within the recency window. A product -- that is out of stock online but anchors big in-store baskets is worth far -- more than its own line revenue — without it the shopper may take the entire -- basket elsewhere. tmp_basket_ord = each orders full value (all its lines); -- tmp_basket_sku = per product, the sum of the baskets it appeared in. DROP TEMPORARY TABLE IF EXISTS tmp_basket_ord; CREATE TEMPORARY TABLE tmp_basket_ord AS SELECT order_id, SUM(line_price) AS order_total FROM tmp_praw WHERE order_id IS NOT NULL AND order_id <> 0 AND OrderDate >= @pr_asof - INTERVAL @pr_recency_days DAY GROUP BY order_id; ALTER TABLE tmp_basket_ord ADD KEY k (order_id); DROP TEMPORARY TABLE IF EXISTS tmp_basket_sku; CREATE TEMPORARY TABLE tmp_basket_sku AS SELECT pl.sku_norm, SUM(bo.order_total) AS basket_turnover, COUNT(*) AS basket_orders FROM (SELECT DISTINCT sku_norm, order_id FROM tmp_praw WHERE order_id IS NOT NULL AND order_id <> 0 AND OrderDate >= @pr_asof - INTERVAL @pr_recency_days DAY) pl JOIN tmp_basket_ord bo ON bo.order_id = pl.order_id GROUP BY pl.sku_norm; ALTER TABLE tmp_basket_sku ADD KEY k (sku_norm); -- Explicit COLLATE on the persistent column matches the temp tables sku_norm -- (built as utf8mb4_uca1400_ai_ci), so the join never hits an illegal-mix-of- -- collations error even if a future installs table default collation differs. UPDATE wp_aieo_dmm_stock_recovery_candidates c JOIN tmp_basket_sku b ON b.sku_norm = c.sku_norm COLLATE utf8mb4_uca1400_ai_ci SET c.phys_basket_turnover_recent = ROUND(b.basket_turnover, 2), c.phys_basket_orders_recent = b.basket_orders; -- Estimated lost ONLINE sales while OOS — taken straight from the sales -- predictor's modelled lost-sales COST (estimated daily rate × days-OOS × -- price), the same optimistic/pessimistic/average figures the OOS reports use. -- est_lost_sales = the average; the range (pessimistic..optimistic) is kept for -- display. NULL when the product has no predictor row (UI shows "—"). UPDATE wp_aieo_dmm_stock_recovery_candidates c LEFT JOIN ( SELECT product_id, optimistic_cost, pessimistic_cost, average_cost FROM wp_aieo_sales_predictor WHERE grain = 'variation' AND prediction_date = (SELECT MAX(prediction_date) FROM wp_aieo_sales_predictor WHERE grain = 'variation') ) sp ON sp.product_id = c.product_id SET c.lost_optimistic = ROUND(sp.optimistic_cost, 2), c.lost_pessimistic = ROUND(sp.pessimistic_cost, 2), c.lost_average = ROUND(sp.average_cost, 2), c.est_lost_sales = ROUND(sp.average_cost, 2); -- Cooldown + blocklist (from product meta), then meets_threshold + confidence. UPDATE wp_aieo_dmm_stock_recovery_candidates c JOIN wp_postmeta pm ON pm.post_id = c.product_id AND pm.meta_key = '_aieo_sr_blocklisted' AND pm.meta_value = '1' SET c.blocked_reason = 'blocklist'; -- v37: a Dismiss holds for the stock-out it was given in (product meta _aieo_sr_dismissed = that stock-out's start, -- 'Y-m-d', or the day of the Dismiss when the start was unknown); the TRUNCATE above lost it every night. A stock-out -- that started later brings the product back. Compared as 'Y-m-d' strings: no date conversion of a meta value, and a -- value of another shape counts as no Dismiss. UPDATE wp_aieo_dmm_stock_recovery_candidates c JOIN wp_postmeta pm ON pm.post_id = c.product_id AND pm.meta_key = '_aieo_sr_dismissed' SET c.status = 'dismissed' WHERE c.status = 'pending' AND pm.meta_value LIKE '____-__-__' AND (c.oos_since IS NULL OR DATE_FORMAT(c.oos_since, '%Y-%m-%d') <= pm.meta_value); UPDATE wp_aieo_dmm_stock_recovery_candidates c JOIN wp_postmeta pm ON pm.post_id = c.product_id AND pm.meta_key = '_aieo_sr_last_restock_at' SET c.blocked_reason = 'cooldown' WHERE c.blocked_reason IS NULL AND STR_TO_DATE(pm.meta_value, '%Y-%m-%d %H:%i:%s') >= @pr_asof - INTERVAL @pr_cooldown_days DAY; UPDATE wp_aieo_dmm_stock_recovery_candidates SET meets_threshold = CASE WHEN blocked_reason IS NULL AND (@pr_min_days_oos = 0 OR oos_source <> 'unknown') AND phys_units_after >= @pr_min_units AND phys_sale_days >= @pr_min_days AND days_oos >= @pr_min_days_oos AND last_phys_sale_date >= @pr_asof - INTERVAL @pr_recency_days DAY THEN 1 ELSE 0 END, confidence_score = LEAST(100, ROUND( (phys_units_after / GREATEST(@pr_min_units, 1)) * 45 + (phys_sale_days / GREATEST(@pr_min_days, 1)) * 35 + phys_branch_count * 10, 2)); -- Re-derive applied status from the append-only events log (candidates are -- rebuilt each run; the log is the source of truth). UPDATE wp_aieo_dmm_stock_recovery_candidates c JOIN wp_aieo_dmm_stock_recovery_events ev ON ev.id = (SELECT MAX(e2.id) FROM wp_aieo_dmm_stock_recovery_events e2 WHERE e2.product_id = c.product_id AND e2.reverted_at IS NULL) SET c.last_event_id = ev.id, c.status = CASE WHEN ev.mode = 'auto' THEN 'auto_applied' ELSE 'applied' END; -- --------------------------------------------------------------------- -- (B) CATALOG GAPS: physically-selling SKUs whose Woo product is NOT -- published (the daily draft imports) or absent. Ranked by demand. -- --------------------------------------------------------------------- DROP TEMPORARY TABLE IF EXISTS tmp_pgap; CREATE TEMPORARY TABLE tmp_pgap AS SELECT r.sku_norm, MAX(r.raw_sku) AS sku, SUM(r.qty) AS phys_units, SUM(CASE WHEN r.OrderDate >= @pr_data_asof - INTERVAL @pr_gap_recency_days DAY THEN r.qty ELSE 0 END) AS phys_units_recent, COUNT(DISTINCT DATE(r.OrderDate)) AS phys_sale_days, MAX(DATE(r.OrderDate)) AS last_phys_sale_date, SUM(r.line_price) AS phys_turnover, -- Last-30-days sub-window (units + net turnover), anchored to the same data -- as-of as the gap window. Accurate when the gap-recency window is >= 30d -- (default 60); bounded by it otherwise (the tmp_pgap WHERE below). SUM(CASE WHEN r.OrderDate >= @pr_data_asof - INTERVAL 30 DAY THEN r.qty ELSE 0 END) AS phys_units_30d, SUM(CASE WHEN r.OrderDate >= @pr_data_asof - INTERVAL 30 DAY THEN r.line_price ELSE 0 END) AS phys_turnover_30d FROM tmp_praw r WHERE r.OrderDate >= @pr_data_asof - INTERVAL @pr_gap_recency_days DAY GROUP BY r.sku_norm HAVING phys_units >= @pr_gap_min_units; ALTER TABLE tmp_pgap ADD KEY k (sku_norm); -- SKU -> Woo product via _sku postmeta (incl. drafts). has_published flags a -- live SKU (excluded from gaps); draft_post_id = a representative non-published post. DROP TEMPORARY TABLE IF EXISTS tmp_skupost; CREATE TEMPORARY TABLE tmp_skupost AS SELECT x.sku_norm, MAX(x.is_pub) AS has_published, MAX(CASE WHEN x.is_pub = 0 THEN x.post_id END) AS draft_post_id FROM ( SELECT UPPER(REPLACE(CAST(pm.meta_value AS CHAR), ' ', '')) COLLATE utf8mb4_uca1400_ai_ci AS sku_norm, pm.post_id, (p.post_status = 'publish') AS is_pub FROM wp_postmeta pm JOIN wp_posts p ON p.ID = pm.post_id AND p.post_type IN ('product', 'product_variation') WHERE pm.meta_key = '_sku' AND pm.meta_value <> '' ) x GROUP BY x.sku_norm; ALTER TABLE tmp_skupost ADD KEY k (sku_norm); TRUNCATE TABLE wp_aieo_dmm_catalog_gap_candidates; INSERT INTO wp_aieo_dmm_catalog_gap_candidates (product_id, sku, sku_norm, name, supplier_name, barcode, post_status, product_type, phys_units, phys_units_recent, phys_sale_days, last_phys_sale_date, phys_turnover, phys_units_30d, phys_turnover_30d, velocity_per_day, priority_score, missing_image, missing_desc, missing_cats, missing_brand, needs_creation, status, computed_at) SELECT sp.draft_post_id, g.sku, g.sku_norm, COALESCE(NULLIF(dp.post_title, ''), NULLIF(cat.Soft1ProductName, ''), g.sku) AS name, NULL AS supplier_name, cat.Barcode, dp.post_status, dp.post_type, g.phys_units, g.phys_units_recent, g.phys_sale_days, g.last_phys_sale_date, ROUND(g.phys_turnover, 2), g.phys_units_30d, ROUND(g.phys_turnover_30d, 2), ROUND(g.phys_units_recent / GREATEST(@pr_gap_recency_days, 1), 4), ROUND(g.phys_turnover, 2) AS priority_score, CASE WHEN sp.draft_post_id IS NULL THEN 0 WHEN EXISTS (SELECT 1 FROM wp_postmeta tm WHERE tm.post_id = sp.draft_post_id AND tm.meta_key = '_thumbnail_id' AND tm.meta_value <> '' AND tm.meta_value <> '0') THEN 0 ELSE 1 END, CASE WHEN sp.draft_post_id IS NULL THEN 0 WHEN COALESCE(dp.post_content, '') <> '' THEN 0 ELSE 1 END, CASE WHEN sp.draft_post_id IS NULL THEN 0 WHEN EXISTS (SELECT 1 FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat' WHERE tr.object_id = sp.draft_post_id) THEN 0 ELSE 1 END, CASE WHEN sp.draft_post_id IS NULL THEN 0 WHEN EXISTS (SELECT 1 FROM wp_term_relationships tr JOIN wp_term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_brand' WHERE tr.object_id = sp.draft_post_id) THEN 0 ELSE 1 END, CASE WHEN sp.draft_post_id IS NULL THEN 1 ELSE 0 END, 'pending', UTC_TIMESTAMP() FROM tmp_pgap g LEFT JOIN tmp_skupost sp ON sp.sku_norm = g.sku_norm LEFT JOIN wp_posts dp ON dp.ID = sp.draft_post_id LEFT JOIN wp_s1erp_temp_unique_product_catalogue_table cat ON UPPER(REPLACE(CAST(cat.SKU AS CHAR), ' ', '')) COLLATE utf8mb4_uca1400_ai_ci = g.sku_norm WHERE (sp.has_published IS NULL OR sp.has_published = 0); -- Branch shape N: the NARROW per-branch units for the gap rows — one row -- per (gap row, branch), same gap-recency window as tmp_pgap (this is how -- the Missing-Hot-Sellers list knows which stores sell each SKU). -- units_recent stays 0 for this scope (no per-branch recency sub-window). INSERT INTO wp_aieo_dmm_branch_phys (scope, ref_id, branch_id, units, units_recent) SELECT 'gap', g.id, r.branch_id, SUM(r.qty), 0 FROM wp_aieo_dmm_catalog_gap_candidates g JOIN tmp_praw r ON r.sku_norm = g.sku_norm COLLATE utf8mb4_uca1400_ai_ci WHERE r.branch_id IS NOT NULL AND r.OrderDate >= @pr_data_asof - INTERVAL @pr_gap_recency_days DAY GROUP BY g.id, r.branch_id HAVING SUM(r.qty) <> 0; DROP TEMPORARY TABLE IF EXISTS tmp_sr_branch_map; AIEO_JOB_SQL; $b['stock_recovery_outcomes'] = <<<'AIEO_JOB_SQL' SET @pr_asof = COALESCE(@pr_asof, UTC_DATE()); SET @pr_outcome_window_days = COALESCE(@pr_outcome_window_days, 21); -- Eshop sales recovered (units/turnover/orders) per open event, from NON-failed -- orders placed after the restock (and before any revert). UPDATE wp_aieo_dmm_stock_recovery_events ev LEFT JOIN ( SELECT e.id AS event_id, COALESCE(SUM(opa.quantity), 0) AS units, COALESCE(SUM(opa.price), 0) AS turnover, COUNT(DISTINCT opa.order_id) AS orders FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_order_products_analytics opa ON opa.effective_pid = e.product_id AND opa.date_created_gmt > e.applied_at AND (e.reverted_at IS NULL OR opa.date_created_gmt < e.reverted_at) LEFT JOIN wp_wc_orders o ON o.id = CAST(opa.order_id AS UNSIGNED) WHERE (o.status IS NULL OR o.status NOT IN ('wc-cancelled', 'wc-refunded', 'wc-failed', 'wc-checkout-draft', 'auto-draft', 'trash')) GROUP BY e.id ) win ON win.event_id = ev.id SET ev.eshop_units_recovered = COALESCE(win.units, 0), ev.eshop_turnover_recovered = COALESCE(win.turnover, 0), ev.eshop_orders_recovered = COALESCE(win.orders, 0) WHERE ev.outcome NOT IN ('reverted'); -- Drafts: the same products sitting in UNFINISHED checkouts since the restock -- (wc-checkout-draft / auto-draft) — pent-up demand not yet finalised. Tracked -- alongside the real recovered revenue so the operator sees both the booked sales -- and the pipeline. NOT counted as recovered (those are finalised orders only). -- v26: TWO sources, because WooCommerce 11.0 stopped producing the first one. -- (a) WC < 11: an unfinished checkout left a wc-checkout-draft ORDER behind. -- (b) WC >= 11: draft order creation is deferred to place-order time, so an -- unfinished checkout produces NO order at all. It lives in the durable -- Abandoned-Carts capture instead (wp_aieo_carts + wp_aieo_cart_items, -- written live by AIEO_Runtime_Carts_Live_Capture). Without (b) every draft -- metric silently reads zero on 11.0 and looks like demand collapsed. -- The cart side is split into two branches on purpose: matching with -- COALESCE(NULLIF(variation_id,0),product_id) is not sargable, and this proc has -- twice been bitten by function-predicate joins flipping to billion-row plans. UPDATE wp_aieo_dmm_stock_recovery_events ev LEFT JOIN ( SELECT u.event_id, COALESCE(SUM(u.units), 0) AS units, COALESCE(SUM(u.turnover), 0) AS turnover, COALESCE(SUM(u.orders), 0) AS orders FROM ( SELECT e.id AS event_id, COALESCE(SUM(opa.quantity), 0) AS units, COALESCE(SUM(opa.price), 0) AS turnover, COUNT(DISTINCT opa.order_id) AS orders FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_order_products_analytics opa ON opa.effective_pid = e.product_id AND opa.date_created_gmt > e.applied_at AND (e.reverted_at IS NULL OR opa.date_created_gmt < e.reverted_at) JOIN wp_wc_orders o ON o.id = CAST(opa.order_id AS UNSIGNED) WHERE o.status IN ('wc-checkout-draft', 'auto-draft') GROUP BY e.id UNION ALL SELECT e.id AS event_id, COALESCE(SUM(ci.quantity), 0) AS units, COALESCE(SUM(ci.line_total), 0) AS turnover, COUNT(DISTINCT ci.cart_id) AS orders FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_cart_items ci ON ci.variation_id = e.product_id AND ci.variation_id <> 0 JOIN wp_aieo_carts c ON c.id = ci.cart_id WHERE c.wc_order_id IS NULL AND c.status = 'abandoned' AND c.created_at > e.applied_at AND (e.reverted_at IS NULL OR c.created_at < e.reverted_at) GROUP BY e.id UNION ALL SELECT e.id AS event_id, COALESCE(SUM(ci.quantity), 0) AS units, COALESCE(SUM(ci.line_total), 0) AS turnover, COUNT(DISTINCT ci.cart_id) AS orders FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_cart_items ci ON ci.product_id = e.product_id AND ci.variation_id = 0 JOIN wp_aieo_carts c ON c.id = ci.cart_id WHERE c.wc_order_id IS NULL AND c.status = 'abandoned' AND c.created_at > e.applied_at AND (e.reverted_at IS NULL OR c.created_at < e.reverted_at) GROUP BY e.id ) u GROUP BY u.event_id ) dr ON dr.event_id = ev.id SET ev.eshop_units_draft = COALESCE(dr.units, 0), ev.eshop_turnover_draft = COALESCE(dr.turnover, 0), ev.eshop_orders_draft = COALESCE(dr.orders, 0) WHERE ev.outcome NOT IN ('reverted'); -- Draft basket halo: total value of the whole UNFINISHED-checkout orders (drafts) -- that contained the restocked product since restock (each order once) — the draft -- equivalent of eshop_basket_turnover (pipeline basket value sitting in checkout). -- v26: same two sources as the draft units/turnover block above. On WC >= 11 the -- "unfinished checkout" is a captured cart, and its whole-basket value is -- wp_aieo_carts.total. Each basket is counted once per event either way. UPDATE wp_aieo_dmm_stock_recovery_events ev LEFT JOIN ( SELECT po.event_id, SUM(po.order_total) AS basket FROM ( SELECT e.id AS event_id, opa.order_id AS basket_id, MAX(opa.order_total) AS order_total FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_order_products_analytics opa ON opa.effective_pid = e.product_id AND opa.date_created_gmt > e.applied_at AND (e.reverted_at IS NULL OR opa.date_created_gmt < e.reverted_at) JOIN wp_wc_orders o ON o.id = CAST(opa.order_id AS UNSIGNED) WHERE o.status IN ('wc-checkout-draft', 'auto-draft') GROUP BY e.id, opa.order_id UNION ALL SELECT e.id AS event_id, ci.cart_id AS basket_id, MAX(c.total) AS order_total FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_cart_items ci ON ci.variation_id = e.product_id AND ci.variation_id <> 0 JOIN wp_aieo_carts c ON c.id = ci.cart_id WHERE c.wc_order_id IS NULL AND c.status = 'abandoned' AND c.created_at > e.applied_at AND (e.reverted_at IS NULL OR c.created_at < e.reverted_at) GROUP BY e.id, ci.cart_id UNION ALL SELECT e.id AS event_id, ci.cart_id AS basket_id, MAX(c.total) AS order_total FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_cart_items ci ON ci.product_id = e.product_id AND ci.variation_id = 0 JOIN wp_aieo_carts c ON c.id = ci.cart_id WHERE c.wc_order_id IS NULL AND c.status = 'abandoned' AND c.created_at > e.applied_at AND (e.reverted_at IS NULL OR c.created_at < e.reverted_at) GROUP BY e.id, ci.cart_id ) po GROUP BY po.event_id ) bk ON bk.event_id = ev.id SET ev.eshop_basket_draft = COALESCE(bk.basket, 0) WHERE ev.outcome NOT IN ('reverted'); -- Basket halo (online, SINCE restock): the total value of the WHOLE online orders -- placed after the restock (non-failed, before any revert) that contained the -- restocked product, each order counted ONCE at its full order_total. Shows the -- whole-cart revenue these products were part of online, not just their own line. -- Inner GROUP BY (event, order) collapses the products multiple lines per order -- to one row at the order total; the outer SUM adds the distinct orders per event. UPDATE wp_aieo_dmm_stock_recovery_events ev LEFT JOIN ( SELECT po.event_id, SUM(po.order_total) AS basket FROM ( SELECT e.id AS event_id, opa.order_id, MAX(opa.order_total) AS order_total FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_order_products_analytics opa ON opa.effective_pid = e.product_id AND opa.date_created_gmt > e.applied_at AND (e.reverted_at IS NULL OR opa.date_created_gmt < e.reverted_at) LEFT JOIN wp_wc_orders o ON o.id = CAST(opa.order_id AS UNSIGNED) WHERE (o.status IS NULL OR o.status NOT IN ('wc-cancelled', 'wc-refunded', 'wc-failed', 'wc-checkout-draft', 'auto-draft', 'trash')) GROUP BY e.id, opa.order_id ) po GROUP BY po.event_id ) bk ON bk.event_id = ev.id SET ev.eshop_basket_turnover = COALESCE(bk.basket, 0) WHERE ev.outcome NOT IN ('reverted'); -- Failed units: orders for the product placed after restock that ended cancelled/refunded/failed. UPDATE wp_aieo_dmm_stock_recovery_events ev LEFT JOIN ( SELECT e.id AS event_id, COALESCE(SUM(opa.quantity), 0) AS funits FROM wp_aieo_dmm_stock_recovery_events e JOIN wp_aieo_order_products_analytics opa ON opa.effective_pid = e.product_id AND opa.date_created_gmt > e.applied_at AND (e.reverted_at IS NULL OR opa.date_created_gmt < e.reverted_at) JOIN wp_wc_orders o ON o.id = CAST(opa.order_id AS UNSIGNED) WHERE o.status IN ('wc-cancelled', 'wc-refunded', 'wc-failed') GROUP BY e.id ) fail ON fail.event_id = ev.id SET ev.failed_units = COALESCE(fail.funits, 0) WHERE ev.outcome NOT IN ('reverted'); -- Verdict (skip reverted; pending events only get promoted): -- failure: had failed orders & ~no clean recovery; OR re-OOS with no sale past grace. -- success: recovered > 0. -- inconclusive: past the outcome window with no sale (and not failed). UPDATE wp_aieo_dmm_stock_recovery_events ev LEFT JOIN wp_aieo_core_product_vitals v ON v.product_id = ev.product_id SET ev.outcome = CASE WHEN ev.failed_units > 0 AND ev.eshop_units_recovered <= 0 THEN 'failure' WHEN ev.eshop_units_recovered > 0 THEN 'success' WHEN (v.stock < 1 OR v.stock_status = 'outofstock') AND ev.eshop_units_recovered <= 0 AND ev.applied_at <= @pr_asof - INTERVAL @pr_outcome_window_days DAY THEN 'failure' WHEN ev.applied_at <= @pr_asof - INTERVAL @pr_outcome_window_days DAY THEN 'inconclusive' ELSE 'pending' END, ev.outcome_reason = CASE WHEN ev.failed_units > 0 AND ev.eshop_units_recovered <= 0 THEN 'cancelled' WHEN ev.eshop_units_recovered > 0 THEN 'sold' WHEN (v.stock < 1 OR v.stock_status = 'outofstock') AND ev.eshop_units_recovered <= 0 AND ev.applied_at <= @pr_asof - INTERVAL @pr_outcome_window_days DAY THEN 'reoos_nosale' WHEN ev.applied_at <= @pr_asof - INTERVAL @pr_outcome_window_days DAY THEN 'still_instock_nosale' ELSE ev.outcome_reason END, ev.outcome_evaluated_at = UTC_TIMESTAMP() WHERE ev.outcome NOT IN ('reverted'); -- Catalog-gap published-benefit: for every Missing Hot Seller that went LIVE -- online (a gap-publish event), attribute the eshop sales it has earned SINCE -- its publish timestamp (and before any unpublish), excluding failed orders. -- Match the published post on either the parent product id or the variation id. UPDATE wp_aieo_dmm_catalog_gap_events ge LEFT JOIN ( SELECT e.id AS event_id, COALESCE(SUM(opa.quantity), 0) AS units, COALESCE(SUM(opa.price), 0) AS turnover, COUNT(DISTINCT opa.order_id) AS orders FROM wp_aieo_dmm_catalog_gap_events e JOIN wp_aieo_order_products_analytics opa ON (opa.product_id = e.product_id OR opa.variation_id = e.product_id) AND opa.date_created_gmt > e.published_at AND (e.unpublished_at IS NULL OR opa.date_created_gmt < e.unpublished_at) LEFT JOIN wp_wc_orders o ON o.id = CAST(opa.order_id AS UNSIGNED) WHERE (o.status IS NULL OR o.status NOT IN ('wc-cancelled', 'wc-refunded', 'wc-failed', 'wc-checkout-draft', 'auto-draft', 'trash')) GROUP BY e.id ) gw ON gw.event_id = ge.id SET ge.eshop_units_since = COALESCE(gw.units, 0), ge.eshop_turnover_since = COALESCE(gw.turnover, 0), ge.eshop_orders_since = COALESCE(gw.orders, 0), ge.outcome_evaluated_at = UTC_TIMESTAMP(); AIEO_JOB_SQL; $b['push_dispatch_prune'] = <<<'AIEO_JOB_SQL' -- Web Push dispatch-log maintenance. The dispatcher writes one row per -- candidate every 5-min tick (incl. every cap / quiet-hours suppression), so -- wp_aieo_push_dispatch balloons (~100k rows/day). Keep real outcomes long; -- prune old SUPPRESSED rows. Batched DELETE (state-indexed) so it never -- long-locks; the loop drains the backlog then steady-states on the daily run. -- Retention: suppressed 7 days, outcomes (sent/clicked/dismissed/failed) 365 days. DECLARE v_guard INT DEFAULT 0; prune_supp: REPEAT DELETE FROM wp_aieo_push_dispatch WHERE state = 'suppressed' AND created_at < (UTC_TIMESTAMP() - INTERVAL 7 DAY) LIMIT 5000; SET v_guard = v_guard + 1; IF ROW_COUNT() = 0 OR v_guard >= 2000 THEN LEAVE prune_supp; END IF; UNTIL 0 END REPEAT prune_supp; SET v_guard = 0; prune_old: REPEAT DELETE FROM wp_aieo_push_dispatch WHERE state IN ('sent','clicked','dismissed','failed') AND created_at < (UTC_TIMESTAMP() - INTERVAL 365 DAY) LIMIT 5000; SET v_guard = v_guard + 1; IF ROW_COUNT() = 0 OR v_guard >= 2000 THEN LEAVE prune_old; END IF; UNTIL 0 END REPEAT prune_old; AIEO_JOB_SQL; foreach ($b as $id => $sql) { $b[$id] = str_replace('/*AIEO_PICKUP_ORDER*/', aieo_analytics_pickup_order_sql('opa.order_id', 'MIN(os.shippingtype)'), $sql); } return $b; } } if (!function_exists('aieo_analytics_pickup_order_sql')) { function aieo_analytics_pickup_order_sql($order_id, $shipping_name) { return "(CASE WHEN EXISTS (SELECT 1 FROM wp_woocommerce_order_items pk_oi" . " JOIN wp_woocommerce_order_itemmeta pk_oim ON pk_oim.order_item_id = pk_oi.order_item_id AND pk_oim.meta_key = 'method_id'" . " WHERE pk_oi.order_id = {$order_id} AND pk_oi.order_item_type = 'shipping' AND pk_oim.meta_value IN ('local_pickup', 'pickup_location'))" . " OR EXISTS (SELECT 1 FROM wp_wc_orders_meta pk_om WHERE pk_om.order_id = {$order_id} AND pk_om.meta_key = '_aieo_pickup_location' AND pk_om.meta_value <> '')" . " OR {$shipping_name} LIKE '%Παραλαβή%' THEN 1 ELSE 0 END)"; } }