<?php

if (!defined('ABSPATH')) { exit; }

if (!function_exists('aieo_analytics_jobs_proc_bodies')) {
function aieo_analytics_jobs_proc_bodies() {
    $b = array();
    $b['order_stats'] = <<<'AIEO_JOB_SQL'
-- ALTER TABLE wp_aieo_order_stats ADD COLUMN statsclientid DECIMAL(12,0) NULL;
-- ALTER TABLE `femmefatale`.`wp_aieo_order_stats`  DROP  COLUMN `statsclientid` ;
-- Refresh per-order attribution from AIEO's OWN session attribution for any
-- converted order not already recorded. INSERT IGNORE preserves the historical
-- PYS snapshot + any already-captured AIEO row (order_id is the PK). This is
-- what replaces the PixelYourSite dependency going forward.
INSERT IGNORE INTO wp_aieo_order_attribution
  (order_id, channel, utm_source, utm_medium, utm_campaign, utm_term, utm_content, origin, updated_at)
SELECT s.order_id, NULLIF(s.entry_channel,''), NULLIF(s.entry_source,''), NULLIF(s.entry_medium,''),
       NULLIF(s.entry_campaign,''), NULLIF(s.entry_term,''), NULLIF(s.entry_content,''), 'aieo', NOW()
FROM (
  SELECT order_id, entry_channel, entry_source, entry_medium, entry_campaign, entry_term, entry_content,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY last_timestamp DESC, id DESC) AS rn
  FROM wp_aieo_sessions_statics
  WHERE order_id IS NOT NULL AND order_id > 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).
INSERT IGNORE INTO wp_aieo_order_attribution
  (order_id, channel, 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,
  NULLIF(p.utm_source,''), NULLIF(p.utm_medium,''), NULLIF(p.utm_campaign,''),
  NULLIF(p.utm_term,''), NULLIF(p.utm_content,''), 'wc_native', NOW()
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
  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('<a href=/wp-admin/post.php?post=',wor.id, '&action=edit >View Order Details</a>') 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.
  (SELECT oi.order_item_name FROM wp_woocommerce_order_items oi WHERE oi.order_id=wor.id AND oi.order_item_type='shipping' AND 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.
CASE WHEN pom.meta_value = 'yes' OR aom.meta_value = 'admin' THEN 'Phone Orders' WHEN attr.channel = 'internal' THEN 'direct' ELSE 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_fb / …) 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. Result: google=organic only, paid_google=all paid.
  WHEN attr.channel LIKE 'paid\_%' THEN 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 attr.channel IN ('internal','direct') THEN '(direct)'
  WHEN COALESCE(attr.utm_source, '') = '' AND COALESCE(attr.channel, '') <> '' THEN attr.channel
  ELSE attr.utm_source
END as orderutmsource,
attr.utm_medium   as orderutmmedium,
attr.utm_campaign as orderutmcampaign,
attr.utm_term     as orderutmterm,
attr.utm_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')
join `wp_wc_orders_meta` wom on wor.id=wom.order_id -- and wom.meta_key='_wcpop_phone_orders_payments_phone_order'
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;
create table IF NOT EXISTS wp_aieo_cumulative_order_stats 
(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(AVG(ws2.orderspend),2) as TotalAverageOrder, 
Round(AVG(ws1.orderspend)/(ws1.numberofitemsperorder),2) as AveSpendOfUserPerItemPerOrder, 
 ws1.numberoforders as TotalNumberofOrders,  
Round(AVG(ws2.numberofitemsperorder),2) as AveItemsOfUserPerOrder, 
Round(STD(ws2.numberofitemsperorder),2) as STDItemsOfUser, Round( STD(ws2.numberofitemsperorder) /MIN(ws2.numberofitemsperorder) ,2) as STDPerMinItemsOfUser, 
Round( STD(ws2.numberofitemsperorder) /AVG(ws2.numberofitemsperorder) ,2) as STDPerAverageItemsOfUser, Round( STD(ws2.numberofitemsperorder) /MAX(ws2.numberofitemsperorder) ,2) as STDPerMaxItemsOfUser,  
Round(AVG(ws2.orderspend),2) as AveSpendOfUserPerOrder, 
Round(STD(ws2.orderspend),2) as STDSpendOfUser, Round( STD(ws2.orderspend) /MIN(ws2.orderspend) ,2) as STDPerMinSpendOfUser, 
Round( STD(ws2.orderspend) /AVG(ws2.orderspend) ,2) as STDPerAverageSpendOfUser, Round( STD(ws2.orderspend) /MAX(ws2.orderspend) ,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 wp_aieo_order_stats ws2 on ws1.customeremail = ws2.customeremail 
Group by ws1.customeremail,  ws1.orderid, ws1.numberofitemsperorder, ws1.numberoforders, ws1.orderspend,ws1.buyerspayrol,
ws1.buyersday,
ws1.buyerstime,
ws1.link_to_order_details
Order by ws1.customeremail;
ALTER TABLE wp_aieo_cumulative_order_stats ADD COLUMN `id` BIGINT(45) UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);	
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;
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('<a href="', MAX(sp.canonical_url), '">', 'Δείτε το προϊόν', '</a>')
         ELSE CONCAT('<a href=/?p=', product_id, '>', 'Δείτε το προϊόν', '</a>') END AS link_to_view,
    CONCAT('<a href=/get-orders-per-product/?product_id=', product_id, '>', 'Δείτε τις αγορές', '</a>') AS link_to_orders,
    CONCAT('<a href=/get-orders-of-products-which-this-product-is-a-crossell/?product_id=', product_id, '>', 'Δείτε linked αγορές', '</a>') 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('<a href="', MAX(sp.canonical_url), '">', 'Δείτε το προϊόν', '</a>')
         ELSE CONCAT('<a href=/?p=', product_id, '>', 'Δείτε το προϊόν', '</a>') END AS link_to_view,
    CONCAT('<a href=/get-orders-per-product/?product_id=', product_id, '>', 'Δείτε τις αγορές', '</a>') AS link_to_orders,
    CONCAT('<a href=/get-orders-of-products-which-this-product-is-a-crossell/?product_id=', product_id, '>', 'Δείτε linked αγορές', '</a>') 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('<a href="', MAX(sp.canonical_url), '">', 'Δείτε το προϊόν', '</a>')
         ELSE CONCAT('<a href=/?p=', product_id, '>', 'Δείτε το προϊόν', '</a>') END AS link_to_view,
    CONCAT('<a href=/get-orders-per-product/?product_id=', product_id, '>', 'Δείτε τις αγορές', '</a>') AS link_to_orders,
    CONCAT('<a href=/get-orders-of-products-which-this-product-is-a-crossell/?product_id=', product_id, '>', 'Δείτε linked αγορές', '</a>') 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(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_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,
  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 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);

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);

/* ===========================================
   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 shippingtype LIKE '%Παραλαβή%'
  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.shippingtype LIKE '%Παραλαβή%'
          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.shippingtype LIKE '%Παραλαβή%'
          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(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_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   := CURRENT_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,
  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 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);

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);

/* ===========================================
   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 shippingtype LIKE '%Παραλαβή%'
  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.shippingtype LIKE '%Παραλαβή%'
          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.shippingtype LIKE '%Παραλαβή%'
          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`);

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, CURDATE());
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)), NOW()
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)), NOW()
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)), NOW()
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)), NOW()
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)), NOW()
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)), NOW()
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 COALESCE(NULLIF(opa.variation_id, 0), opa.product_id) = 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 @asof + 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 @asof + 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 @asof + 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 @asof
    WHEN chosen_items_per_day > 0 AND current_stock > 0 AND current_stock / chosen_items_per_day <= 3650 THEN @asof + 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 @asof 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',
       NOW()
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);
AIEO_JOB_SQL;

    
    
    
    
    
    
    
    
    $b['store_sales_stats'] = <<<'AIEO_JOB_SQL'
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,
  piraeus_30d, piraeus_60d, trend_of_piraeus,
  keratsini_30d, keratsini_60d, trend_of_keratsini,
  korydallos_30d, korydallos_60d, trend_of_korydallos,
  kallithea_30d, kallithea_60d, trend_of_kallithea,
  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.piraeus_30d, a.piraeus_60d,
  CASE WHEN a.piraeus_60d = 0 THEN NULL ELSE ROUND(((a.piraeus_30d/30)-(a.piraeus_60d/60))/(a.piraeus_60d/60)*100, 2) END,
  a.keratsini_30d, a.keratsini_60d,
  CASE WHEN a.keratsini_60d = 0 THEN NULL ELSE ROUND(((a.keratsini_30d/30)-(a.keratsini_60d/60))/(a.keratsini_60d/60)*100, 2) END,
  a.korydallos_30d, a.korydallos_60d,
  CASE WHEN a.korydallos_60d = 0 THEN NULL ELSE ROUND(((a.korydallos_30d/30)-(a.korydallos_60d/60))/(a.korydallos_60d/60)*100, 2) END,
  a.kallithea_30d, a.kallithea_60d,
  CASE WHEN a.kallithea_60d = 0 THEN NULL ELSE ROUND(((a.kallithea_30d/30)-(a.kallithea_60d/60))/(a.kallithea_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,
  NOW(), NOW()
FROM (
  SELECT t.SKU,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 30 DAY THEN t.qty ELSE 0 END) AS total_30d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 60 DAY THEN t.qty ELSE 0 END) AS total_60d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 30 DAY AND t.is_piraeus_store THEN t.qty ELSE 0 END) AS piraeus_30d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 60 DAY AND t.is_piraeus_store THEN t.qty ELSE 0 END) AS piraeus_60d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 30 DAY AND t.is_keratsini THEN t.qty ELSE 0 END) AS keratsini_30d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 60 DAY AND t.is_keratsini THEN t.qty ELSE 0 END) AS keratsini_60d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 30 DAY AND t.is_korydallos THEN t.qty ELSE 0 END) AS korydallos_30d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 60 DAY AND t.is_korydallos THEN t.qty ELSE 0 END) AS korydallos_60d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 30 DAY AND t.is_kallithea THEN t.qty ELSE 0 END) AS kallithea_30d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 60 DAY AND t.is_kallithea THEN t.qty ELSE 0 END) AS kallithea_60d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_DATE - INTERVAL 30 DAY AND t.is_eshop THEN t.qty ELSE 0 END) AS eshop_30d,
    SUM(CASE WHEN t.OrderDate >= CURRENT_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,
      ( s.Branch LIKE '%ΠΕΙΡΑΙΑΣ%' AND (s.InvoiceType IS NULL OR s.InvoiceType <> 7069) ) AS is_piraeus_store,
      ( s.Branch LIKE '%ΚΕΡΑΤΣΙΝΙ%' ) AS is_keratsini,
      ( s.Branch LIKE '%ΚΟΡΥΔΑΛΛΟΣ%' OR s.Branch LIKE '%ΝΙΚΑΙΑ%' ) AS is_korydallos,
      ( s.Branch LIKE '%ΚΑΛΛΙΘΕΑ%' ) AS is_kallithea,
      ( s.Branch LIKE '%ΠΕΙΡΑΙΑΣ%' AND s.InvoiceType = 7069 ) AS is_eshop
    FROM wp_s1erp_temp_store_orders_table s
    WHERE s.OrderDate >= CURRENT_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;

-- eShop (WooCommerce) split from AIEO's own order analytics.
UPDATE wp_aieo_dmm_store_sales ss
JOIN (
  SELECT COALESCE(NULLIF(opa.variation_id, 0), opa.product_id) AS pid,
         SUM(CASE WHEN opa.date_created_gmt >= CURDATE() - INTERVAL 30 DAY THEN opa.quantity ELSE 0 END) AS w30,
         SUM(CASE WHEN opa.date_created_gmt >= CURDATE() - INTERVAL 60 DAY THEN opa.quantity ELSE 0 END) AS w60
  FROM wp_aieo_order_products_analytics opa
  WHERE opa.date_created_gmt >= CURDATE() - 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;
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, ' ', '')),
  NOW(), NOW()
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;

-- 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), '');

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,
  piraeus_30d, piraeus_60d, trend_of_piraeus,
  keratsini_30d, keratsini_60d, trend_of_keratsini,
  korydallos_30d, korydallos_60d, trend_of_korydallos,
  kallithea_30d, kallithea_60d, trend_of_kallithea,
  eshop_soft1_30d, eshop_soft1_60d, trend_of_eshop_soft1,
  eshop_woo_30d, eshop_woo_60d, trend_of_eshop_woo)
SELECT
  NOW(), NOW(), pv.name, CURDATE(),
  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.piraeus_30d,0),     IFNULL(ss.piraeus_60d,0),     ss.trend_of_piraeus,
  IFNULL(ss.keratsini_30d,0),   IFNULL(ss.keratsini_60d,0),   ss.trend_of_keratsini,
  IFNULL(ss.korydallos_30d,0),  IFNULL(ss.korydallos_60d,0),  ss.trend_of_korydallos,
  IFNULL(ss.kallithea_30d,0),   IFNULL(ss.kallithea_60d,0),   ss.trend_of_kallithea,
  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, CURDATE());
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_use_piraeus     = COALESCE(@pr_use_piraeus, 1);
SET @pr_use_keratsini   = COALESCE(@pr_use_keratsini, 1);
SET @pr_use_korydallos  = COALESCE(@pr_use_korydallos, 1);
SET @pr_use_kallithea   = COALESCE(@pr_use_kallithea, 1);
SET @pr_gap_min_units   = COALESCE(@pr_gap_min_units, 2);
SET @pr_gap_recency_days= COALESCE(@pr_gap_recency_days, 60);
-- 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);

-- 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,
  ( s.Branch LIKE '%ΠΕΙΡΑΙΑΣ%' AND (s.InvoiceType IS NULL OR s.InvoiceType <> 7069) AND @pr_use_piraeus = 1 ) AS is_piraeus,
  ( s.Branch LIKE '%ΚΕΡΑΤΣΙΝΙ%' AND @pr_use_keratsini = 1 ) AS is_keratsini,
  ( ( s.Branch LIKE '%ΚΟΡΥΔΑΛΛΟΣ%' OR s.Branch LIKE '%ΝΙΚΑΙΑ%' ) AND @pr_use_korydallos = 1 ) AS is_korydallos,
  ( s.Branch LIKE '%ΚΑΛΛΙΘΕΑ%' AND @pr_use_kallithea = 1 ) AS is_kallithea
FROM wp_s1erp_temp_store_orders_table s
-- 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.
JOIN (
  SELECT OrderId, MAX(COALESCE(DATE(ContentLastUpdated), DATE(OrderDate))) AS _import_day
  FROM wp_s1erp_temp_store_orders_table
  WHERE OrderDate >= LEAST(@pr_asof, @pr_data_asof) - INTERVAL GREATEST(@pr_recency_days, @pr_gap_recency_days, 180) DAY
  GROUP BY OrderId
) imp ON imp.OrderId = s.OrderId AND COALESCE(DATE(s.ContentLastUpdated), DATE(s.OrderDate)) = imp._import_day
WHERE s.OrderDate >= LEAST(@pr_asof, @pr_data_asof) - INTERVAL GREATEST(@pr_recency_days, @pr_gap_recency_days, 180) DAY
  AND s.SKU IS NOT NULL AND s.SKU <> ''
HAVING (is_piraeus OR is_keratsini OR is_korydallos OR is_kallithea);
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,
   phys_piraeus, phys_keratsini, phys_korydallos, phys_kallithea,
   phys_piraeus_recent, phys_keratsini_recent, phys_korydallos_recent, phys_kallithea_recent,
   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,
  COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') AND r.is_piraeus    THEN r.qty ELSE 0 END), 0),
  COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') AND r.is_keratsini  THEN r.qty ELSE 0 END), 0),
  COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') AND r.is_korydallos THEN r.qty ELSE 0 END), 0),
  COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') AND r.is_kallithea  THEN r.qty ELSE 0 END), 0),
  -- Windowed (last @pr_recency_days) per-branch units: where the pickers should
  -- pull the recent stock from. Same window as phys_units_recent.
  COALESCE(SUM(CASE WHEN r.OrderDate > COALESCE(o.oos_since, '1900-01-01') AND r.OrderDate >= @pr_asof - INTERVAL @pr_recency_days DAY AND r.is_piraeus    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 AND r.is_keratsini  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 AND r.is_korydallos 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 AND r.is_kallithea  THEN r.qty ELSE 0 END), 0),
  'pending', NOW()
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;

-- Derived metrics: branch count, velocity, suggested qty.
UPDATE wp_aieo_dmm_stock_recovery_candidates SET
  phys_branch_count = (phys_piraeus > 0) + (phys_keratsini > 0) + (phys_korydallos > 0) + (phys_kallithea > 0),
  velocity_per_day  = ROUND(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';
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,
  -- Per-branch units (windowed by the gap-recency filter below) so the
  -- Missing-Hot-Sellers list shows which stores are selling each SKU.
  SUM(CASE WHEN r.is_piraeus    THEN r.qty ELSE 0 END) AS phys_piraeus,
  SUM(CASE WHEN r.is_keratsini  THEN r.qty ELSE 0 END) AS phys_keratsini,
  SUM(CASE WHEN r.is_korydallos THEN r.qty ELSE 0 END) AS phys_korydallos,
  SUM(CASE WHEN r.is_kallithea  THEN r.qty ELSE 0 END) AS phys_kallithea
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,
   phys_piraeus, phys_keratsini, phys_korydallos, phys_kallithea,
   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),
  g.phys_piraeus, g.phys_keratsini, g.phys_korydallos, g.phys_kallithea,
  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', NOW()
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);
AIEO_JOB_SQL;

    
    
    
    
    
    
    
    $b['stock_recovery_outcomes'] = <<<'AIEO_JOB_SQL'
SET @pr_asof = COALESCE(@pr_asof, CURDATE());
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 COALESCE(NULLIF(opa.variation_id, 0), opa.product_id) = 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).
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 COALESCE(NULLIF(opa.variation_id, 0), opa.product_id) = 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
) 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).
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 COALESCE(NULLIF(opa.variation_id, 0), opa.product_id) = 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
  ) 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 COALESCE(NULLIF(opa.variation_id, 0), opa.product_id) = 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 COALESCE(NULLIF(opa.variation_id, 0), opa.product_id) = 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 = NOW()
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 = NOW();
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 < (NOW() - 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 < (NOW() - 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;

    return $b;
}
}
