<?php

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

if (!function_exists('aieo_push_install_sprocs')) {
    
    function aieo_push_install_sprocs() {
        global $wpdb;
        $prev_show     = isset($wpdb->show_errors) ? (bool) $wpdb->show_errors : false;
        $prev_suppress = isset($wpdb->suppress_errors) ? (bool) $wpdb->suppress_errors : false;
        $wpdb->hide_errors();
        $wpdb->suppress_errors(true);

        $ok = true;
        try {
            aieo_push_create_sp_recompute_customer_intelligence();
            aieo_push_create_sp_assign_segments();
        } catch (\Throwable $e) {
            $ok = false;
            if (function_exists('aieo_debug_log')) {
                aieo_debug_log('AIEO_Push sproc install failed: ' . $e->getMessage());
            }
        }

        $wpdb->suppress_errors($prev_suppress);
        if ($prev_show) { $wpdb->show_errors(); }
        return $ok;
    }
}

if (!function_exists('aieo_push_create_sp_recompute_customer_intelligence')) {
    
    function aieo_push_create_sp_recompute_customer_intelligence() {
        global $wpdb;
        $sp_name = 'aieo_sp_recompute_customer_intelligence_batch';

        $tbl_ci      = $wpdb->prefix . 'aieo_customer_intelligence';
        $tbl_users   = $wpdb->prefix . 'users';
        $tbl_addcart = $wpdb->prefix . 'aieo_addcart_events';

        $wpdb->query("DROP PROCEDURE IF EXISTS {$sp_name}");

        
        
        
        
        $sql = "CREATE PROCEDURE {$sp_name}(IN p_source_table VARCHAR(64), IN p_offset INT, IN p_batch_size INT)
SQL SECURITY INVOKER
BEGIN
    -- =====================================================================
    -- Step 0: whitelist source table name (defence in depth even though the
    -- PHP wrapper also validates).
    -- =====================================================================
    IF p_source_table NOT REGEXP '^wp_aieo_[0-9]{4}_[0-9]{2}_orders\$'
       AND p_source_table <> 'wp_aieo_temp_orders' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid source table for recompute';
    END IF;

    -- =====================================================================
    -- Step 0.5: capture the dataset's most-recent OrderDate as the recency
    -- reference. Using UTC_TIMESTAMP() on stale dev backups (or pipelines that ran a
    -- week ago) makes everyone look dormant; anchoring to the latest order
    -- in the source table makes recency_days meaningful regardless of when
    -- the recompute runs.
    -- =====================================================================
    -- Hoist: the snapshot is static for the duration of a recompute run and
    -- all batch CALLs of one run share the PHP wrapper's single connection,
    -- so MAX(OrderDate) is identical for every CALL. Scan once on the first
    -- batch (p_offset = 0) and cache in session variables; later batches
    -- reuse the cached value instead of re-scanning ~3.6M rows per CALL.
    -- A fresh connection (chunked AJAX continuation) or a different source
    -- table recomputes — worst case degrades to the old per-call scan,
    -- never a wrong value.
    IF p_offset = 0
       OR @aieo_recency_ref_val IS NULL
       OR NOT (@aieo_recency_ref_src <=> p_source_table) THEN
        SET @aieo_recency_ref = NULL;
        SET @sql = CONCAT('SELECT MAX(OrderDate) INTO @aieo_recency_ref FROM ', p_source_table);
        PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
        SET @aieo_recency_ref = COALESCE(@aieo_recency_ref, UTC_TIMESTAMP());
        SET @aieo_recency_ref_val = @aieo_recency_ref;
        SET @aieo_recency_ref_src = p_source_table;
    ELSE
        SET @aieo_recency_ref = @aieo_recency_ref_val;
    END IF;

    -- =====================================================================
    -- Step 1: candidate EponymousID batch (deterministic order for paging).
    -- Keyset pagination when the previous batch of THIS run (same
    -- connection, same source table, contiguous offset) left its
    -- high-water mark in @aieo_ci_last_epo: EponymousID > mark avoids
    -- re-reading and discarding p_offset leading ids on every CALL. Any
    -- guard miss falls back to the original OFFSET scan — identical output
    -- either way, because the distinct-id set is static and both schemes
    -- paginate the same EponymousID ASC order.
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _ci_eponymous;
    IF p_offset > 0
       AND @aieo_ci_last_epo IS NOT NULL
       AND @aieo_ci_next_off <=> p_offset
       AND @aieo_ci_src <=> p_source_table THEN
        SET @sql = CONCAT(
            'CREATE TEMPORARY TABLE _ci_eponymous (eponymous_id BIGINT UNSIGNED PRIMARY KEY) ENGINE=MEMORY ',
            'AS SELECT DISTINCT EponymousID AS eponymous_id ',
            'FROM ', p_source_table, ' ',
            'WHERE EponymousID IS NOT NULL AND EponymousID > 0 ',
            'AND EponymousID > @aieo_ci_last_epo ',
            'ORDER BY EponymousID ASC ',
            'LIMIT ', CAST(p_batch_size AS CHAR)
        );
    ELSE
        SET @sql = CONCAT(
            'CREATE TEMPORARY TABLE _ci_eponymous (eponymous_id BIGINT UNSIGNED PRIMARY KEY) ENGINE=MEMORY ',
            'AS SELECT DISTINCT EponymousID AS eponymous_id ',
            'FROM ', p_source_table, ' ',
            'WHERE EponymousID IS NOT NULL AND EponymousID > 0 ',
            'ORDER BY EponymousID ASC ',
            'LIMIT ', CAST(p_batch_size AS CHAR), ' OFFSET ', CAST(p_offset AS CHAR)
        );
    END IF;
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    -- High-water mark for the next batch's keyset guard. NULL on an empty
    -- batch, so the next CALL falls back to the OFFSET path (also empty).
    SELECT MAX(eponymous_id) INTO @aieo_ci_last_epo FROM _ci_eponymous;
    SET @aieo_ci_next_off = p_offset + p_batch_size;
    SET @aieo_ci_src = p_source_table;

    -- =====================================================================
    -- Step 2: per-customer scalar metrics (denormalised constants in the
    -- AIEO snapshot — MAX() picks them off any of the customer's line items).
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _ci_stats;
    SET @sql = CONCAT(
        'CREATE TEMPORARY TABLE _ci_stats AS SELECT ',
        '  o.EponymousID AS eponymous_id, ',
        '  MAX(o.CustomerEmail) AS email, ',
        '  MIN(o.OrderDate)     AS first_order_at, ',
        -- Prefer LastTransactionDate when populated; fall back to MAX(OrderDate).
        '  COALESCE(MAX(NULLIF(o.LastTransactionDate, ''0000-00-00 00:00:00'')), MAX(o.OrderDate)) AS last_order_at, ',
        '  MAX(o.TotalCustomerOrders)  AS orders_count, ',
        '  MAX(o.TotalCustomerTurnover) AS ltv_total, ',
        '  MAX(o.AvgSpend)     AS aov, ',
        '  MAX(o.AvgDBO)       AS avg_dbo, ',
        '  MAX(o.CurrentDBO)   AS current_dbo, ',
        '  MAX(o.MaxDBO)       AS max_dbo, ',
        '  MAX(o.MinDBO)       AS min_dbo, ',
        '  MAX(o.SDDBO)        AS sd_dbo, ',
        -- Historical discount + price signals (per-line aggregation).
        -- temp_orders.OnSale + Price + RegularPrice are the snapshot at the
        -- time of the order (CurrentPrice exists as a separate column for
        -- todays state); see class-aieo-dmm-surveys::verify_price_historical_status
        -- for empirical spot-check.
        '  SUM(IF(o.OnSale = 1 OR (o.Price IS NOT NULL AND o.RegularPrice IS NOT NULL AND o.RegularPrice > 0 AND o.Price < o.RegularPrice), 1, 0)) AS discounted_lines, ',
        '  COUNT(*) AS total_lines, ',
        '  AVG(IF(o.Price > 0, o.Price, NULL)) AS avg_item_price ',
        'FROM ', p_source_table, ' o ',
        'JOIN _ci_eponymous u ON u.eponymous_id = o.EponymousID ',
        'GROUP BY o.EponymousID'
    );
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    -- =====================================================================
    -- Step 3: top-10 brand affinity per customer (SUM(LineValue) per brand).
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _ci_brand;
    SET @sql = CONCAT(
        'CREATE TEMPORARY TABLE _ci_brand AS SELECT ',
        '  user_id AS eponymous_id, ',
        '  MAX(CASE WHEN rnk = 1 THEN brand_id ELSE 0 END) AS top_brand_id, ',
        '  MAX(CASE WHEN rnk = 1 AND total_spend > 0 THEN ROUND(spend / total_spend, 4) ELSE 0 END) AS top_brand_share, ',
        '  CONCAT(\"{\", ',
        '    GROUP_CONCAT( ',
        '      CASE WHEN rnk <= 10 AND total_spend > 0 ',
        -- Use ASCII 34 (\") for the JSON string-key quote.
        '           THEN CONCAT(CHAR(34), brand_id, CHAR(34), \":\", ROUND(spend / total_spend, 4)) ',
        '           ELSE NULL END ',
        '      ORDER BY rnk SEPARATOR \",\"), ',
        '  \"}\") AS brand_affinity_json ',
        'FROM ( ',
        '  SELECT user_id, brand_id, spend, ',
        '    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY spend DESC, brand_id ASC) AS rnk, ',
        '    SUM(spend) OVER (PARTITION BY user_id) AS total_spend ',
        '  FROM ( ',
        '    SELECT o.EponymousID AS user_id, o.BrandId AS brand_id, ',
        '           SUM(IFNULL(o.LineValue, 0)) AS spend ',
        '    FROM ', p_source_table, ' o ',
        '    JOIN _ci_eponymous u ON u.eponymous_id = o.EponymousID ',
        '    WHERE o.BrandId IS NOT NULL AND o.BrandId > 0 ',
        '    GROUP BY o.EponymousID, o.BrandId ',
        '  ) src ',
        ') ranked ',
        'GROUP BY user_id'
    );
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    -- =====================================================================
    -- Step 4: top-10 head-category affinity per customer.
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _ci_cat;
    SET @sql = CONCAT(
        'CREATE TEMPORARY TABLE _ci_cat AS SELECT ',
        '  user_id AS eponymous_id, ',
        '  MAX(CASE WHEN rnk = 1 THEN cat_id ELSE 0 END) AS top_category_id, ',
        '  MAX(CASE WHEN rnk = 1 AND total_spend > 0 THEN ROUND(spend / total_spend, 4) ELSE 0 END) AS top_category_share, ',
        '  CONCAT(\"{\", ',
        '    GROUP_CONCAT( ',
        '      CASE WHEN rnk <= 10 AND total_spend > 0 ',
        '           THEN CONCAT(CHAR(34), cat_id, CHAR(34), \":\", ROUND(spend / total_spend, 4)) ',
        '           ELSE NULL END ',
        '      ORDER BY rnk SEPARATOR \",\"), ',
        '  \"}\") AS category_affinity_json ',
        'FROM ( ',
        '  SELECT user_id, cat_id, spend, ',
        '    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY spend DESC, cat_id ASC) AS rnk, ',
        '    SUM(spend) OVER (PARTITION BY user_id) AS total_spend ',
        '  FROM ( ',
        '    SELECT o.EponymousID AS user_id, o.HeadId AS cat_id, ',
        '           SUM(IFNULL(o.LineValue, 0)) AS spend ',
        '    FROM ', p_source_table, ' o ',
        '    JOIN _ci_eponymous u ON u.eponymous_id = o.EponymousID ',
        '    WHERE o.HeadId IS NOT NULL AND o.HeadId > 0 ',
        '    GROUP BY o.EponymousID, o.HeadId ',
        '  ) src ',
        ') ranked ',
        'GROUP BY user_id'
    );
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

    -- =====================================================================
    -- Step 4.5 — badge-context purchase counters per customer (Phase 8).
    -- Aggregates wp_aieo_addcart_events rows where order_id > 0 (i.e. the
    -- click converted into a paid line) by joining session-level user_id
    -- through wp_users → email → eponymous_id. Only the customers in the
    -- current batch are scanned.
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _ci_badges;
    CREATE TEMPORARY TABLE _ci_badges AS
    SELECT
        s.eponymous_id,
        SUM(IF(ae.bcs_was_on_sale = 1, 1, 0))                       AS sale_purchase_cnt,
        SUM(IF(ae.bcs_is_new = 1, 1, 0))                            AS new_product_purchase_cnt,
        SUM(IF(ae.bcs_gift_offer_id IS NOT NULL AND ae.bcs_gift_offer_id > 0, 1, 0))    AS gift_completed_count,
        SUM(IF(ae.bcs_monthly_offer_id IS NOT NULL AND ae.bcs_monthly_offer_id > 0, 1, 0)) AS monthly_offer_purchase_cnt
    FROM _ci_stats s
    JOIN {$tbl_users} u   ON u.user_email = s.email AND s.email <> ''
    JOIN {$tbl_addcart} ae ON ae.user_id = u.ID AND ae.order_id > 0
    GROUP BY s.eponymous_id;

    -- =====================================================================
    -- Step 5: UPSERT into wp_aieo_customer_intelligence.
    -- Static SQL — no dynamic table references here.
    -- =====================================================================
    INSERT INTO {$tbl_ci} (
        eponymous_id, wp_user_id, email,
        first_order_at, last_order_at, orders_count, ltv_total, aov, recency_days,
        discount_share, avg_item_price,
        top_brand_id, top_brand_share, brand_affinity_json,
        top_category_id, top_category_share, category_affinity_json,
        replenishment_json,
        sale_purchase_cnt, new_product_purchase_cnt, gift_completed_count, monthly_offer_purchase_cnt,
        source_table,
        intelligence_version, updated_at
    )
    SELECT
        s.eponymous_id,
        IFNULL(u.ID, 0),
        IFNULL(s.email, ''),
        s.first_order_at,
        s.last_order_at,
        IFNULL(s.orders_count, 0),
        IFNULL(s.ltv_total,    0),
        IFNULL(s.aov,          0),
        CASE WHEN s.last_order_at IS NULL THEN 0
             ELSE LEAST(65535, GREATEST(0, DATEDIFF(@aieo_recency_ref, s.last_order_at)))
        END,
        IFNULL(s.discounted_lines / NULLIF(s.total_lines, 0), 0),
        IFNULL(s.avg_item_price, 0),
        IFNULL(b.top_brand_id, 0),
        IFNULL(b.top_brand_share, 0),
        IFNULL(b.brand_affinity_json, '{}'),
        IFNULL(c.top_category_id, 0),
        IFNULL(c.top_category_share, 0),
        IFNULL(c.category_affinity_json, '{}'),
        CONCAT('{\"avg_dbo\":',     IFNULL(s.avg_dbo, 0),
              ',\"current_dbo\":', IFNULL(s.current_dbo, 0),
              ',\"max_dbo\":',     IFNULL(s.max_dbo, 0),
              ',\"min_dbo\":',     IFNULL(s.min_dbo, 0),
              ',\"sd_dbo\":',      IFNULL(s.sd_dbo, 0), '}'),
        IFNULL(bc.sale_purchase_cnt, 0),
        IFNULL(bc.new_product_purchase_cnt, 0),
        IFNULL(bc.gift_completed_count, 0),
        IFNULL(bc.monthly_offer_purchase_cnt, 0),
        p_source_table,
        1, UTC_TIMESTAMP()
    FROM _ci_stats s
    LEFT JOIN _ci_brand b ON b.eponymous_id = s.eponymous_id
    LEFT JOIN _ci_cat   c ON c.eponymous_id = s.eponymous_id
    LEFT JOIN _ci_badges bc ON bc.eponymous_id = s.eponymous_id
    LEFT JOIN {$tbl_users} u ON u.user_email = s.email AND s.email <> ''
    ON DUPLICATE KEY UPDATE
        wp_user_id             = VALUES(wp_user_id),
        email                  = VALUES(email),
        first_order_at         = VALUES(first_order_at),
        last_order_at          = VALUES(last_order_at),
        orders_count           = VALUES(orders_count),
        ltv_total              = VALUES(ltv_total),
        aov                    = VALUES(aov),
        recency_days           = VALUES(recency_days),
        discount_share         = VALUES(discount_share),
        avg_item_price         = VALUES(avg_item_price),
        top_brand_id           = VALUES(top_brand_id),
        top_brand_share        = VALUES(top_brand_share),
        brand_affinity_json    = VALUES(brand_affinity_json),
        top_category_id        = VALUES(top_category_id),
        top_category_share     = VALUES(top_category_share),
        category_affinity_json = VALUES(category_affinity_json),
        replenishment_json     = VALUES(replenishment_json),
        sale_purchase_cnt          = VALUES(sale_purchase_cnt),
        new_product_purchase_cnt   = VALUES(new_product_purchase_cnt),
        gift_completed_count       = VALUES(gift_completed_count),
        monthly_offer_purchase_cnt = VALUES(monthly_offer_purchase_cnt),
        source_table           = VALUES(source_table),
        intelligence_version   = 1,
        updated_at             = UTC_TIMESTAMP();

    -- =====================================================================
    -- Cleanup
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _ci_brand;
    DROP TEMPORARY TABLE IF EXISTS _ci_cat;
    DROP TEMPORARY TABLE IF EXISTS _ci_badges;
    DROP TEMPORARY TABLE IF EXISTS _ci_stats;
    DROP TEMPORARY TABLE IF EXISTS _ci_eponymous;
END";

        $wpdb->query($sql);
    }
}

if (!function_exists('aieo_push_create_sp_assign_segments')) {
    
    function aieo_push_create_sp_assign_segments() {
        global $wpdb;
        if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) {
            return; 
        }
        $sp_name = 'aieo_sp_assign_segments';
        $tbl_ci  = $wpdb->prefix . 'aieo_customer_intelligence';

        
        
        
        
        $tbl_cp    = $wpdb->prefix . 'aieo_competition_participants';
        $cp_exists = ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl_cp)) === $tbl_cp);
        $comp_case = $cp_exists
            ? ",\n            CASE WHEN EXISTS (SELECT 1 FROM {$tbl_cp} cp WHERE cp.eponymous_id = {$tbl_ci}.eponymous_id) THEN '\"competition_participant\"' END"
            : '';

        $wpdb->query("DROP PROCEDURE IF EXISTS {$sp_name}");

        $sql = "CREATE PROCEDURE {$sp_name}()
SQL SECURITY INVOKER
BEGIN
    -- =====================================================================
    -- Step A: RFM bands via NTILE(5) over site-wide quintiles.
    -- Recency band is inverted (5 = freshest, 1 = stalest) so the rfm_cell
    -- string reads naturally (\"5_5_5\" = best customers).
    -- =====================================================================
    DROP TEMPORARY TABLE IF EXISTS _ci_bands;
    CREATE TEMPORARY TABLE _ci_bands (
        eponymous_id BIGINT UNSIGNED PRIMARY KEY,
        monetary_band TINYINT UNSIGNED,
        frequency_band TINYINT UNSIGNED,
        recency_band TINYINT UNSIGNED,
        price_band TINYINT UNSIGNED
    ) ENGINE=MEMORY;

    INSERT INTO _ci_bands (eponymous_id, monetary_band, frequency_band, recency_band, price_band)
    SELECT
        eponymous_id,
        NTILE(5) OVER (ORDER BY ltv_total ASC, eponymous_id ASC)        AS monetary_band,
        NTILE(5) OVER (ORDER BY orders_count ASC, eponymous_id ASC)     AS frequency_band,
        -- recency_days ASC means lowest = most recent = highest band
        6 - NTILE(5) OVER (ORDER BY recency_days ASC, eponymous_id ASC) AS recency_band,
        -- price_band: quintile of per-customer avg item price.  Customers
        -- with avg_item_price = 0 (no priced lines) get NULL via the WHERE
        -- below; the catch-all UPDATE then zeroes them.
        NTILE(5) OVER (ORDER BY avg_item_price ASC, eponymous_id ASC)   AS price_band
    FROM {$tbl_ci}
    WHERE orders_count > 0 AND avg_item_price > 0;

    UPDATE {$tbl_ci} ci
    JOIN _ci_bands b ON b.eponymous_id = ci.eponymous_id
    SET
        ci.monetary_band  = b.monetary_band,
        ci.frequency_band = b.frequency_band,
        ci.recency_band   = b.recency_band,
        ci.price_band     = b.price_band,
        ci.rfm_cell       = CONCAT(b.recency_band, b.frequency_band, b.monetary_band);

    -- Customers with zero orders or no priced lines: zero out the bands
    -- so they don't accidentally match any segment / priority rule.
    UPDATE {$tbl_ci}
    SET monetary_band = 0, frequency_band = 0, recency_band = 0, price_band = 0, rfm_cell = ''
    WHERE orders_count = 0 OR avg_item_price = 0;

    DROP TEMPORARY TABLE IF EXISTS _ci_bands;

    -- =====================================================================
    -- Step A.5: dormancy cohorts + reactivation propensity score.
    -- A 73%+ dormant cohort is the largest revenue opportunity in any
    -- mature store, but treating it as one bucket wastes spend on lost
    -- causes and undersells recoverable VIPs. Split by recency tiers and
    -- score each customer 0-100 by how worth-it a reactivation attempt is.
    -- =====================================================================
    UPDATE {$tbl_ci}
    SET
        dormancy_cohort = CASE
            WHEN orders_count = 0     THEN ''
            WHEN recency_days < 60    THEN ''
            WHEN recency_days <= 90   THEN 'drifting'
            WHEN recency_days <= 180  THEN 'lapsed'
            WHEN recency_days <= 365  THEN 'long_dormant'
            ELSE 'lost'
        END,
        -- Score formula:
        --   base = monetary_band * 12   →  0-60 from LTV quintile (highest signal)
        --        + frequency_band * 6   →  0-30 from order count quintile
        --        + freshness gate       →  0-30 — full credit at recency=60d, -5/month after
        --        - attempt penalty      →  0 to -30 — stop hammering non-responders
        --
        -- Then multiplied by a cohort-decay coefficient. Without this gate,
        -- a former-VIP at 18 months silent still scores 80+ and floods the
        -- win-back queue. The multiplier reflects empirical reactivation
        -- rates: drifting recovers at 8-15%, lost at <1%. The dispatcher
        -- gets a queue that already triages by realistic ROI.
        reactivation_score = CASE
            WHEN orders_count = 0 OR recency_days < 60 THEN 0
            ELSE GREATEST(0, LEAST(100, FLOOR(
                (monetary_band * 12
                 + frequency_band * 6
                 + GREATEST(0, 30 - FLOOR(GREATEST(0, recency_days - 60) / 30) * 5)
                 - LEAST(30, reactivation_attempts_count * 10))
                *
                CASE
                    WHEN recency_days <= 90  THEN 1.0      -- drifting: full credit
                    WHEN recency_days <= 180 THEN 0.8      -- lapsed: -20%
                    WHEN recency_days <= 365 THEN 0.5      -- long-dormant: half
                    ELSE                          0.25     -- lost: 25% — most are gone
                END
            )))
        END;

    -- =====================================================================
    -- Step A.6: detect reactivation success.
    -- A customer counts as reactivated when they were attempted (we sent at
    -- least one win-back) AND they are now active again. Setting reactivated_at
    -- once is the success signal we measure for cohort-level effectiveness.
    -- Once set, the date sticks for ROI analysis (we never overwrite it).
    -- =====================================================================
    UPDATE {$tbl_ci}
    SET reactivated_at = UTC_TIMESTAMP()
    WHERE reactivated_at IS NULL
      AND reactivation_attempts_count > 0
      AND orders_count > 0
      AND recency_days < 60;

    -- =====================================================================
    -- Step B: assign segments. CONCAT_WS(',', ...) skips NULLs, so each
    -- CASE returns either the segment string OR NULL — a clean way to build
    -- a sparse JSON array of conditional segments.
    --
    -- WHY TWO SEGMENT NAMES CHANGED HERE:
    -- The segment vocabulary and the dormancy_cohort vocabulary had grown two
    -- different meanings for the same two words, and both were rendered on the
    -- same admin page, so the page contradicted itself.
    --   'dormant': the segment named 13,883 customers because it carried a
    --   relative gate (recency_band <= 2) on top of the 60-day rule, while the
    --   dormancy_cohort tile beside it named 33,222 off recency_days >= 60
    --   alone. Same word, 2.4x apart, no way for an operator to tell which
    --   number a campaign would actually reach. The segment is deleted; Step
    --   A.5's dormancy_cohort is now the ONLY dormancy vocabulary, and anything
    --   that wants dormancy reads that column.
    --   'lapsed': the segment named 1,872 people (overdue against their OWN
    --   cadence) and the dormancy_cohort of the same name named 2,013 people
    --   (a flat 61-180 day calendar window). They overlapped by 128 rows —
    --   6.6%. Two nearly-disjoint populations under one word. The segment is
    --   renamed to 'overdue', which is what its rule has always measured; the
    --   calendar cohort keeps 'lapsed'. The RULE below is unchanged.
    -- =====================================================================
    UPDATE {$tbl_ci}
    SET segments_json = CONCAT('[',
        IFNULL(CONCAT_WS(',',
            CASE WHEN monetary_band = 5 AND recency_band >= 4
                 THEN '\"vip\"' END,
            CASE WHEN orders_count >= 5 AND recency_band >= 3
                  AND NOT (monetary_band = 5 AND recency_band >= 4)
                 THEN '\"loyalist\"' END,
            CASE WHEN orders_count = 1 AND recency_days < 60
                 THEN '\"newbie\"' END,
            CASE WHEN top_brand_share > 0.7000 AND orders_count >= 3
                 THEN '\"brand_devoted\"' END,
            CASE WHEN orders_count >= 3
                  AND CAST(JSON_VALUE(replenishment_json, '$.avg_dbo') AS DECIMAL(10,2)) BETWEEN 0.1 AND 30
                 THEN '\"replenisher\"' END,
            -- Overdue: late relative to THEIR OWN cadence. For a weekly buyer,
            -- 21 days = overdue; for a quarterly buyer, 21 days = nothing.
            -- This is the AIEO-USP play: only achievable because the snapshot
            -- already gives us avg_dbo (advanced-behavior step's output).
            -- Floor at 14 absolute days so daily-orderers don't trip after one
            -- skipped weekend. Named 'overdue' rather than 'lapsed' because
            -- dormancy_cohort already spends 'lapsed' on a flat calendar window
            -- (61-180 days) that this rule agrees with only 6.6% of the time.
            CASE WHEN orders_count >= 3
                  AND CAST(JSON_VALUE(replenishment_json, '$.avg_dbo') AS DECIMAL(10,2)) > 0
                  AND recency_days >= 14
                  AND recency_days > 2.5 * CAST(JSON_VALUE(replenishment_json, '$.avg_dbo') AS DECIMAL(10,2))
                 THEN '\"overdue\"' END,
            -- Discount Hunter: 50%+ of order lines bought with a coupon
            -- applied OR average effective discount over 30%. Previously
            -- deferred (discount_share was unpopulated by the AIEO data-prep
            -- pipeline); now fires correctly because compute_discount_and_
            -- price_from_wc populates the column directly from WC line items.
            -- Surfacing this segment unlocks heavy-coupon-user targeting in
            -- newsletter sync, push triggers, and template-variant selection.
            CASE WHEN discount_share >= 0.5
                 THEN '\"discount_hunter\"' END{$comp_case}
        ), ''),
    ']');
END";

        $wpdb->query($sql);
    }
}