<?php

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

if (!function_exists('aieo_sales_predictor_staged_bodies')) {

function aieo_sales_predictor_staged_bodies() {
    $s = array();

    
    $s['AIEO_SP_Prepare'] = <<<'AIEO_SP_SQL'
SET @asof       = COALESCE(@aieo_predictor_asof, UTC_DATE());
SET @grain_mode = COALESCE(NULLIF(@aieo_predictor_grain, ''), 'both');
SET @bt_h       = 28;
SET @acc_alpha  = 0.30;

CREATE TABLE IF NOT EXISTS wp_aieo_sp_run (
  id tinyint unsigned NOT NULL PRIMARY KEY,
  asof date DEFAULT NULL,
  grain varchar(16) DEFAULT 'both',
  next_batch int DEFAULT 0,
  total_batches int DEFAULT 0,
  total_products int DEFAULT 0,
  status varchar(20) DEFAULT NULL,
  updated_at datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- Snapshot the vitals attributes the per-row UPDATE joins need (one consistent
-- read), keyed for joins. Persistent so Batch (a separate connection) can read it.
DROP TABLE IF EXISTS wp_aieo_sp_vit;
CREATE TABLE wp_aieo_sp_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 wp_aieo_sp_vit ADD KEY k (product_id);
ANALYZE TABLE wp_aieo_sp_vit;

-- Parent stock = SUM of each parent's variations' stock (one scan).
DROP TABLE IF EXISTS wp_aieo_sp_pstock;
CREATE TABLE wp_aieo_sp_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 wp_aieo_sp_pstock ADD KEY k (pid);
ANALYZE TABLE wp_aieo_sp_pstock;

-- Work queue: every product processed (sellable units + parents), numbered
-- into fixed-size batches by ascending product_id.
DROP TABLE IF EXISTS wp_aieo_sp_queue;
CREATE TABLE wp_aieo_sp_queue (
  product_id bigint NOT NULL PRIMARY KEY,
  batch_no int NOT NULL,
  KEY k_batch (batch_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
INSERT INTO wp_aieo_sp_queue (product_id, batch_no)
SELECT product_id, FLOOR((ROW_NUMBER() OVER (ORDER BY product_id) - 1) / 2000)
FROM (
  SELECT product_id FROM wp_aieo_core_product_vitals WHERE status = 1
  UNION
  SELECT product_id FROM wp_aieo_core_product_vitals WHERE parent_id = product_id
) u;
ANALYZE TABLE wp_aieo_sp_queue;

-- ---------------------------------------------------------------------
-- BACKTEST -> accuracy scoreboard (global + per-category, per method).
-- tmp_bt is TEMPORARY — only needed within this single Prepare call.
-- ---------------------------------------------------------------------
-- Realized units in the (snapshot, snapshot+bt_h] window, pre-aggregated ONCE
-- (per effective_pid for the variation grain; per product_id for the parent
-- grain) instead of a correlated per-row scalar subquery that block-nested-
-- looped the whole order-lines table for every history row (~1.9B rows / 490s
-- on femme). All history rows share one snapshot_date, so the window is
-- constant → the pre-aggregate is value-identical to the per-row subquery.
SET @bt_from = DATE(@asof - INTERVAL @bt_h DAY);
SET @bt_to   = @bt_from + INTERVAL @bt_h DAY;
DROP TEMPORARY TABLE IF EXISTS tmp_bt_real;
CREATE TEMPORARY TABLE tmp_bt_real AS
  SELECT CAST('variation' AS CHAR) COLLATE utf8mb4_uca1400_ai_ci AS grain,
         effective_pid AS product_id, SUM(quantity) AS realized
    FROM wp_aieo_order_products_analytics
   WHERE date_created_gmt > @bt_from AND date_created_gmt <= @bt_to
   GROUP BY effective_pid
  UNION ALL
  SELECT CAST('parent' AS CHAR) COLLATE utf8mb4_uca1400_ai_ci AS grain,
         product_id, SUM(quantity)
    FROM wp_aieo_order_products_analytics
   WHERE date_created_gmt > @bt_from AND date_created_gmt <= @bt_to
   GROUP BY product_id;
ALTER TABLE tmp_bt_real ADD KEY k (grain, product_id);

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,
  COALESCE(r.realized, 0) AS realized
FROM wp_aieo_sales_predictor_history h
LEFT JOIN wp_aieo_sp_vit v ON v.product_id = h.product_id
LEFT JOIN tmp_bt_real r ON r.grain = h.grain AND r.product_id = h.product_id
WHERE h.snapshot_date = DATE(@asof - INTERVAL @bt_h DAY);
DROP TEMPORARY TABLE IF EXISTS tmp_bt_real;

INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at)
SELECT grain, 'global', '', 'linear', COUNT(*),
       AVG(ABS(ipd_linear * @bt_h - realized) / GREATEST(realized, 1)) * 100,
       AVG(ipd_linear * @bt_h - realized),
       AVG(ABS(ipd_linear * @bt_h - realized)), UTC_TIMESTAMP()
FROM tmp_bt WHERE ipd_linear IS NOT NULL GROUP BY grain
ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias),
       ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at);

INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at)
SELECT grain, 'global', '', 'ewma', COUNT(*),
       AVG(ABS(ipd_ewma * @bt_h - realized) / GREATEST(realized, 1)) * 100,
       AVG(ipd_ewma * @bt_h - realized),
       AVG(ABS(ipd_ewma * @bt_h - realized)), UTC_TIMESTAMP()
FROM tmp_bt WHERE ipd_ewma IS NOT NULL GROUP BY grain
ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias),
       ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at);

INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at)
SELECT grain, 'global', '', 'average', COUNT(*),
       AVG(ABS(ipd_average * @bt_h - realized) / GREATEST(realized, 1)) * 100,
       AVG(ipd_average * @bt_h - realized),
       AVG(ABS(ipd_average * @bt_h - realized)), UTC_TIMESTAMP()
FROM tmp_bt WHERE ipd_average IS NOT NULL GROUP BY grain
ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias),
       ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at);

INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at)
SELECT grain, 'category', category_key, 'linear', COUNT(*),
       AVG(ABS(ipd_linear * @bt_h - realized) / GREATEST(realized, 1)) * 100,
       AVG(ipd_linear * @bt_h - realized),
       AVG(ABS(ipd_linear * @bt_h - realized)), UTC_TIMESTAMP()
FROM tmp_bt WHERE ipd_linear IS NOT NULL AND category_key <> '0' GROUP BY grain, category_key
ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias),
       ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at);

INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at)
SELECT grain, 'category', category_key, 'ewma', COUNT(*),
       AVG(ABS(ipd_ewma * @bt_h - realized) / GREATEST(realized, 1)) * 100,
       AVG(ipd_ewma * @bt_h - realized),
       AVG(ABS(ipd_ewma * @bt_h - realized)), UTC_TIMESTAMP()
FROM tmp_bt WHERE ipd_ewma IS NOT NULL AND category_key <> '0' GROUP BY grain, category_key
ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias),
       ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at);

INSERT INTO wp_aieo_sales_predictor_accuracy (grain, segment_type, segment_key, method, n_samples, mape, bias, ewma_abs_err, updated_at)
SELECT grain, 'category', category_key, 'average', COUNT(*),
       AVG(ABS(ipd_average * @bt_h - realized) / GREATEST(realized, 1)) * 100,
       AVG(ipd_average * @bt_h - realized),
       AVG(ABS(ipd_average * @bt_h - realized)), UTC_TIMESTAMP()
FROM tmp_bt WHERE ipd_average IS NOT NULL AND category_key <> '0' GROUP BY grain, category_key
ON DUPLICATE KEY UPDATE n_samples = VALUES(n_samples), mape = VALUES(mape), bias = VALUES(bias),
       ewma_abs_err = (1 - @acc_alpha) * ewma_abs_err + @acc_alpha * VALUES(ewma_abs_err), updated_at = VALUES(updated_at);

DROP TEMPORARY TABLE IF EXISTS tmp_bt;

-- Previous snapshot price (latest history row before @asof) for price tracking.
DROP TABLE IF EXISTS wp_aieo_sp_prev;
CREATE TABLE wp_aieo_sp_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 wp_aieo_sp_prev ADD KEY k (grain, product_id);
ANALYZE TABLE wp_aieo_sp_prev;

-- Per-(grain, product) inverse-error method weights (category, else global),
-- flattened from the accuracy scoreboard for every queued product × both grains.
DROP TABLE IF EXISTS wp_aieo_sp_pw;
CREATE TABLE wp_aieo_sp_pw AS
SELECT gp.grain, gp.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 (
  -- COLLATE the grain literals to the predictor table's collation: a bare
  -- literal takes the connection collation (utf8mb4_unicode_520_ci here),
  -- which would clash with sp.grain (utf8mb4_uca1400_ai_ci) on the later
  -- w.grain = sp.grain join (column=column → illegal mix, no coercion).
  SELECT CAST('variation' AS CHAR) COLLATE utf8mb4_uca1400_ai_ci AS grain, v.product_id, v.category_key
    FROM wp_aieo_sp_vit v JOIN wp_aieo_sp_queue q ON q.product_id = v.product_id
  UNION ALL
  SELECT CAST('parent' AS CHAR) COLLATE utf8mb4_uca1400_ai_ci AS grain, v.product_id, v.category_key
    FROM wp_aieo_sp_vit v JOIN wp_aieo_sp_queue q ON q.product_id = v.product_id
) gp
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 = gp.grain AND ac.segment_key = gp.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 = gp.grain;
ALTER TABLE wp_aieo_sp_pw ADD KEY k (grain, product_id);
ANALYZE TABLE wp_aieo_sp_pw;

TRUNCATE wp_aieo_sales_predictor;

REPLACE INTO wp_aieo_sp_run (id, asof, grain, next_batch, total_batches, total_products, status, updated_at)
SELECT 1, @asof, @grain_mode, 0, COALESCE(MAX(batch_no) + 1, 0), COUNT(*), 'batching', UTC_TIMESTAMP()
FROM wp_aieo_sp_queue;
AIEO_SP_SQL;

    
    $s['AIEO_SP_Batch'] = <<<'AIEO_SP_SQL'
SELECT asof, grain, next_batch INTO @asof, @grain_mode, @bn FROM wp_aieo_sp_run WHERE id = 1;
SET @reorder_h = 56;

-- VARIATION grain rows for this slice. INSERT IGNORE so a re-run of a batch
-- that aborted mid-way (after the inserts committed) is a no-op, not a dup error.
INSERT IGNORE INTO wp_aieo_sales_predictor
  (grain, product_id, parent_product_id, prediction_date, current_stock, price,
   total_product_sales, total_product_turnover, quantity_sold_yesterday, turnover_yesterday,
   quantity_last_7_days, quantity_last_14_days, quantity_last_28_days, quantity_last_56_days, quantity_last_175_days,
   turnover_last_7_days, turnover_last_14_days, turnover_last_28_days, turnover_last_56_days, turnover_last_175_days,
   quantity_next_7_days, quantity_next_14_days, quantity_next_28_days, quantity_next_56_days, quantity_next_175_days,
   turnover_next_7_days, turnover_next_14_days, turnover_next_28_days, turnover_next_56_days, turnover_next_175_days,
   product_availability_last_year_7, product_availability_last_year_14, product_availability_last_year_28,
   product_availability_last_year_56, product_availability_last_year_175, transactions_total, out_of_stock_days)
SELECT
  'variation', v.product_id, v.parent_id, @asof, v.stock, v.price,
  COALESCE(SUM(opa.quantity), 0), COALESCE(SUM(opa.price), 0),
  COALESCE(SUM(CASE WHEN DATE(opa.date_created_gmt) = DATE(v.product_latest_sale_date) THEN opa.quantity ELSE 0 END), 0),
  COALESCE(SUM(CASE WHEN DATE(opa.date_created_gmt) = DATE(v.product_latest_sale_date) THEN opa.price ELSE 0 END), 0),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 7  DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 7  DAY)) THEN opa.quantity ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 14 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 14 DAY)) THEN opa.quantity ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 28 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 28 DAY)) THEN opa.quantity ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 56 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 56 DAY)) THEN opa.quantity ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 175 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 175 DAY)) THEN opa.quantity ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 7  DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 7  DAY)) THEN opa.price ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 14 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 14 DAY)) THEN opa.price ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 28 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 28 DAY)) THEN opa.price ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 56 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 56 DAY)) THEN opa.price ELSE 0 END),
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 175 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 175 DAY)) THEN opa.price ELSE 0 END),
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7   DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 7   DAY THEN opa.quantity ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14  DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 14  DAY THEN opa.quantity ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28  DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 28  DAY THEN opa.quantity ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56  DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 56  DAY THEN opa.quantity ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN opa.quantity ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7   DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 7   DAY THEN opa.price ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14  DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 14  DAY THEN opa.price ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28  DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 28  DAY THEN opa.price ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56  DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 56  DAY THEN opa.price ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN SUM(CASE WHEN opa.date_created_gmt BETWEEN @asof - INTERVAL 1 YEAR AND @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN opa.price ELSE 0 END) ELSE NULL END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 7   DAY THEN 1 ELSE 0 END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 14  DAY THEN 1 ELSE 0 END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 28  DAY THEN 1 ELSE 0 END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 56  DAY THEN 1 ELSE 0 END,
  CASE WHEN DATE(v.product_creation_date) <= @asof - INTERVAL 1 YEAR + INTERVAL 175 DAY THEN 1 ELSE 0 END,
  SUM(CASE WHEN ((v.stock >= 1 AND opa.date_created_gmt >= @asof - INTERVAL 175 DAY) OR (v.stock < 1 AND opa.date_created_gmt >= v.product_latest_sale_date - INTERVAL 175 DAY)) AND opa.order_id IS NOT NULL THEN 1 ELSE 0 END),
  CASE WHEN v.stock < 1 THEN GREATEST(DATEDIFF(@asof, COALESCE(v.product_latest_sale_date, MAX(opa.date_created_gmt))), 0) ELSE 0 END
FROM wp_aieo_core_product_vitals v
LEFT JOIN wp_aieo_order_products_analytics opa
  ON opa.effective_pid = v.product_id   -- effective_pid = COALESCE(NULLIF(variation_id,0),product_id), indexed
WHERE v.status = 1
  AND v.product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn)
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 rows for this slice (INSERT IGNORE — see variation note above).
INSERT IGNORE 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 wp_aieo_sp_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
  AND v.product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn)
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).
UPDATE wp_aieo_sales_predictor sp
JOIN wp_aieo_sp_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
  AND sp.product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

-- 4) linear (age-weighted) + EWMA (recency-weighted) rates.
UPDATE wp_aieo_sales_predictor sp
JOIN wp_aieo_sp_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
  AND sp.product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

-- 5) optimistic / pessimistic / average estimators + their names.
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
  AND product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

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
  AND product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

-- 6) Single-window run-out + days-to-sell-10 (linear & EWMA references).
UPDATE wp_aieo_sales_predictor SET
  days_until_stock_run_out_linear = CASE WHEN linear_items_per_day > 0 AND current_stock > 0 THEN ROUND(current_stock / linear_items_per_day, 7) ELSE NULL END,
  days_until_stock_run_out_ewma   = CASE WHEN ewma_items_per_day   > 0 AND current_stock > 0 THEN ROUND(current_stock / ewma_items_per_day, 7)   ELSE NULL END,
  estimated_zero_stock_date       = CASE WHEN ewma_items_per_day   > 0 AND current_stock > 0 AND current_stock / ewma_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(current_stock / ewma_items_per_day) DAY ELSE NULL END,
  days_to_sell_10_units_linear    = CASE WHEN linear_items_per_day > 0 THEN ROUND(10 / linear_items_per_day, 7) ELSE NULL END,
  days_to_sell_10_units_ewma      = CASE WHEN ewma_items_per_day   > 0 THEN ROUND(10 / ewma_items_per_day, 7)   ELSE NULL END,
  estimated_date_to_sell_10_units = CASE WHEN ewma_items_per_day   > 0 AND 10 / ewma_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(10 / ewma_items_per_day) DAY ELSE NULL END
WHERE prediction_date = @asof
  AND product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

-- 7) Reliability + measured confidence.
UPDATE wp_aieo_sales_predictor sp
LEFT JOIN wp_aieo_sp_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
  AND sp.product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

-- 8) Adaptive ensemble rate (inverse-error weights; EWMA fallback).
UPDATE wp_aieo_sales_predictor sp
LEFT JOIN wp_aieo_sp_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
  AND sp.product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

-- 11) Price-change tracking vs the previous snapshot + repetition counts.
UPDATE wp_aieo_sales_predictor sp
LEFT JOIN wp_aieo_sp_prev pv ON pv.grain = sp.grain AND pv.product_id = sp.product_id
SET
  sp.price_before_adjustment = CASE WHEN pv.prev_price IS NOT NULL AND pv.prev_price <> sp.price THEN pv.prev_price ELSE NULL END,
  sp.price_adjustment_percentage = CASE WHEN pv.prev_price IS NOT NULL AND pv.prev_price <> 0 AND pv.prev_price <> sp.price THEN ROUND((sp.price - pv.prev_price) / pv.prev_price * 100, 7) ELSE NULL END,
  sp.latest_price_adjustment_date = CASE WHEN pv.prev_price IS NOT NULL AND pv.prev_price <> sp.price THEN CAST(@asof AS DATE) ELSE NULL END,
  sp.price_update_repetitions = (SELECT COUNT(DISTINCT h.price) FROM wp_aieo_sales_predictor_history h WHERE h.grain = sp.grain AND h.product_id = sp.product_id AND h.snapshot_date >= @asof - INTERVAL 365 DAY),
  sp.out_of_stock_repetitions = (SELECT COUNT(*) FROM wp_aieo_sales_predictor_history h WHERE h.grain = sp.grain AND h.product_id = sp.product_id AND h.current_stock < 1 AND h.snapshot_date >= @asof - INTERVAL 90 DAY)
WHERE sp.prediction_date = @asof
  AND sp.product_id IN (SELECT product_id FROM wp_aieo_sp_queue WHERE batch_no = @bn);

UPDATE wp_aieo_sp_run SET next_batch = next_batch + 1, status = 'batching', updated_at = UTC_TIMESTAMP() WHERE id = 1;
AIEO_SP_SQL;

    
    $s['AIEO_SP_Finalize'] = <<<'AIEO_SP_SQL'
SELECT asof, grain INTO @asof, @grain_mode FROM wp_aieo_sp_run WHERE id = 1;
SET @reorder_h = 56;

-- Fresh stats before the cross-row passes: the snapshot was TRUNCATEd in Prepare
-- and bulk-filled by the Batch loop, so InnoDB stats read ~0 rows here — without
-- this the step-9 self-join can flip to a full×full BNL (~40K² ≈ 1.6B rows).
ANALYZE TABLE wp_aieo_sales_predictor;

-- 9) Substitution-aware parent blend (variation grain only).
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, from the (post-blend) chosen rate.
UPDATE wp_aieo_sales_predictor SET
  chosen_estimator_value    = chosen_items_per_day,
  chosen_days_until_runout  = CASE WHEN chosen_items_per_day > 0 AND current_stock > 0 THEN ROUND(current_stock / chosen_items_per_day, 7) ELSE NULL END,
  chosen_zero_stock_date    = CASE WHEN chosen_items_per_day > 0 AND current_stock > 0 AND current_stock / chosen_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(current_stock / chosen_items_per_day) DAY ELSE NULL END,
  optimistic_cost  = CASE WHEN current_stock < 1 AND optimistic_estimator_value  IS NOT NULL THEN ROUND(optimistic_estimator_value  * out_of_stock_days * price, 7) ELSE NULL END,
  pessimistic_cost = CASE WHEN current_stock < 1 AND pessimistic_estimator_value IS NOT NULL THEN ROUND(pessimistic_estimator_value * out_of_stock_days * price, 7) ELSE NULL END,
  average_cost     = CASE WHEN current_stock < 1 AND average_estimator_value     IS NOT NULL THEN ROUND(average_estimator_value     * out_of_stock_days * price, 7) ELSE NULL END,
  reorder_date     = CASE
    WHEN current_stock < 1 THEN CAST(@asof AS DATE)
    WHEN chosen_items_per_day > 0 AND current_stock > 0 AND current_stock / chosen_items_per_day <= 3650 THEN CAST(@asof AS DATE) + INTERVAL CEIL(current_stock / chosen_items_per_day) DAY
    ELSE NULL END,
  reorder_quantity = CASE WHEN chosen_items_per_day > 0 THEN ROUND(chosen_items_per_day * @reorder_h, 0) ELSE NULL END
WHERE prediction_date = @asof;

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

-- Cleanup staging.
DROP TABLE IF EXISTS wp_aieo_sp_vit;
DROP TABLE IF EXISTS wp_aieo_sp_pstock;
DROP TABLE IF EXISTS wp_aieo_sp_prev;
DROP TABLE IF EXISTS wp_aieo_sp_pw;
DROP TABLE IF EXISTS wp_aieo_sp_queue;

UPDATE wp_aieo_sp_run SET status = 'done', updated_at = UTC_TIMESTAMP() WHERE id = 1;
AIEO_SP_SQL;

    return $s;
}
}