<?php

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

class AIEO_DMM_Physical_Recovery extends AIEO_DMM_Module_Base {

    
    const OPT_AUTO            = 'aieo_sr_auto_apply';
    const OPT_MIN_UNITS       = 'aieo_sr_min_units';
    const OPT_MIN_DAYS        = 'aieo_sr_min_days';
    const OPT_RECENCY_DAYS    = 'aieo_sr_recency_days';
    const OPT_MIN_DAYS_OOS    = 'aieo_sr_min_days_oos';
    const OPT_QTY_CAP         = 'aieo_sr_qty_cap';
    const OPT_COVER_DAYS      = 'aieo_sr_cover_days';
    const OPT_COOLDOWN_DAYS   = 'aieo_sr_cooldown_days';
    const OPT_BRANCH_FILTER   = 'aieo_sr_branch_filter';    
    const OPT_OUTCOME_WINDOW  = 'aieo_sr_outcome_window_days';
    const OPT_GAP_MIN_UNITS   = 'aieo_gap_min_units';
    const OPT_GAP_RECENCY     = 'aieo_gap_recency_days';
    const OPT_CHANNEL_PCT     = 'aieo_sr_channel_pct';      
    
    
    
    const LOST_PESS_FACTOR    = 0.6;
    const LOST_OPT_FACTOR     = 2.0;
    const OPT_GAP_BLOCKLIST   = 'aieo_gap_blocklist';       
    const OPT_GAP_STATUSES    = 'aieo_gap_statuses';        
    const OPT_GAP_NAME_EXCLUDES = 'aieo_gap_name_excludes'; 
    const GAP_STATUS_NC       = '__needs_creation__';       

    const FLAP_STRIKES = 3; 

    const BRANCHES = array('piraeus', 'keratsini', 'korydallos', 'kallithea');

    public function get_id()        { return 'stock_recovery'; }
    public function get_label()     { return __('Stock Recovery', 'ai-eshop-optimizer'); }
    public function get_group()     { return 'catalog_sync'; }
    public function get_priority()  { return 72; }
    public function is_pro_locked() { return true; }

    public function get_pro_promo() {
        return array(
            'title'       => __('Stock Recovery — sell what the stores have', 'ai-eshop-optimizer'),
            'description' => __('When the ERP wrongly marks a product out of stock but the physical stores keep selling it, recover the lost online sales automatically — and see exactly what it earned you.', 'ai-eshop-optimizer'),
            'bullets'     => array(
                __('Detects OOS eshop products that still sell in your stores', 'ai-eshop-optimizer'),
                __('Auto-restocks with manager confidence thresholds', 'ai-eshop-optimizer'),
                __('Tracks recovered sales (€) and a success/failure precision score', 'ai-eshop-optimizer'),
                __('Catalog-gap list: high-demand SKUs still sitting as draft imports', 'ai-eshop-optimizer'),
            ),
        );
    }

    
    private static function t_cand()       { global $wpdb; return $wpdb->prefix . 'aieo_dmm_stock_recovery_candidates'; }
    private static function t_gap()        { global $wpdb; return $wpdb->prefix . 'aieo_dmm_catalog_gap_candidates'; }
    private static function t_events()     { global $wpdb; return $wpdb->prefix . 'aieo_dmm_stock_recovery_events'; }
    private static function t_gap_events() { global $wpdb; return $wpdb->prefix . 'aieo_dmm_catalog_gap_events'; }

    private static function table_exists($t) {
        global $wpdb;
        return (string) $wpdb->get_var("SHOW TABLES LIKE '" . esc_sql($t) . "'") === $t;
    }

    
    private static function clamp_int($opt, $def, $min, $max) {
        $v = (int) get_option($opt, $def);
        if ($v < $min) { $v = $min; }
        if ($v > $max) { $v = $max; }
        return $v;
    }
    public static function auto_apply_enabled() { return (int) get_option(self::OPT_AUTO, 0) === 1; }
    public static function min_units()      { return self::clamp_int(self::OPT_MIN_UNITS, 2, 1, 999); }
    public static function min_days()       { return self::clamp_int(self::OPT_MIN_DAYS, 2, 1, 60); }
    public static function recency_days()   { return self::clamp_int(self::OPT_RECENCY_DAYS, 30, 3, 365); }
    public static function min_days_oos()   { return self::clamp_int(self::OPT_MIN_DAYS_OOS, 3, 0, 365); }
    public static function qty_cap()        { return self::clamp_int(self::OPT_QTY_CAP, 5, 1, 999); }
    public static function cover_days()     { return self::clamp_int(self::OPT_COVER_DAYS, 14, 1, 120); }
    public static function cooldown_days()  { return self::clamp_int(self::OPT_COOLDOWN_DAYS, 14, 1, 180); }
    public static function outcome_window() { return self::clamp_int(self::OPT_OUTCOME_WINDOW, 21, 3, 120); }
    public static function gap_min_units()  { return self::clamp_int(self::OPT_GAP_MIN_UNITS, 2, 1, 999); }
    public static function gap_recency()    { return self::clamp_int(self::OPT_GAP_RECENCY, 60, 3, 365); }
    
    
    
    
    public static function channel_pct()    { return self::clamp_int(self::OPT_CHANNEL_PCT, 17, 1, 100); }
    public static function channel_ratio()  { return self::channel_pct() / 100.0; }

    
    public static function enabled_branches() {
        $f = get_option(self::OPT_BRANCH_FILTER, array());
        if (!is_array($f) || empty($f)) { return self::BRANCHES; }
        return array_values(array_intersect(self::BRANCHES, $f));
    }
    
    public static function gap_blocklist() {
        $b = get_option(self::OPT_GAP_BLOCKLIST, array());
        return is_array($b) ? $b : array();
    }
    
    public static function gap_statuses() {
        $v = get_option(self::OPT_GAP_STATUSES, array('draft', self::GAP_STATUS_NC));
        if (!is_array($v)) { $v = array('draft', self::GAP_STATUS_NC); }
        $allowed = array('draft', 'private', 'pending', self::GAP_STATUS_NC);
        return array_values(array_intersect($allowed, $v)); 
    }
    
    public static function gap_name_excludes() {
        $v = get_option(self::OPT_GAP_NAME_EXCLUDES, array());
        if (!is_array($v)) { return array(); }
        $out = array();
        foreach ($v as $s) { $s = trim((string) $s); if ($s !== '' && !in_array($s, $out, true)) { $out[] = $s; } }
        return $out;
    }

    
    private static function gap_filter_sql($a = '') {
        global $wpdb;
        $sql = " AND {$a}status = 'pending'";
        
        $bl = self::gap_blocklist();
        if (!empty($bl)) {
            $place = implode(',', array_fill(0, count($bl), '%s'));
            $sql  .= $wpdb->prepare(" AND {$a}sku_norm NOT IN ($place)", $bl);
        }
        
        $st       = self::gap_statuses();
        $statuses = array_values(array_diff($st, array(self::GAP_STATUS_NC)));
        $legs     = array();
        if (!empty($statuses)) {
            $place  = implode(',', array_fill(0, count($statuses), '%s'));
            $legs[] = $wpdb->prepare("{$a}post_status IN ($place)", $statuses);
        }
        if (in_array(self::GAP_STATUS_NC, $st, true)) { $legs[] = "{$a}needs_creation = 1"; }
        $sql .= !empty($legs) ? ' AND (' . implode(' OR ', $legs) . ')' : ' AND 0';
        
        foreach (self::gap_name_excludes() as $needle) {
            $like = '%' . $wpdb->esc_like($needle) . '%';
            $sql .= $wpdb->prepare(" AND {$a}name NOT LIKE %s AND {$a}sku NOT LIKE %s", $like, $like);
        }
        return $sql;
    }

    
    public static function get_gap_excluded() {
        global $wpdb;
        $bl = self::gap_blocklist();
        if (!self::table_exists(self::t_gap())) {
            return array_map(static function ($s) { return array('sku_norm' => $s, 'name' => '', 'sku' => $s); }, $bl);
        }
        if (!empty($bl)) {
            $place = implode(',', array_fill(0, count($bl), '%s'));
            $where = $wpdb->prepare("status = 'dismissed' OR sku_norm IN ($place)", $bl);
        } else {
            $where = "status = 'dismissed'";
        }
        $rows = (array) $wpdb->get_results("SELECT sku_norm, name, sku FROM `" . self::t_gap() . "` WHERE {$where} ORDER BY name ASC LIMIT 500", ARRAY_A);
        $seen = array();
        foreach ($rows as $r) { $seen[$r['sku_norm']] = true; }
        foreach ($bl as $s) { if (empty($seen[$s])) { $rows[] = array('sku_norm' => $s, 'name' => '', 'sku' => $s); } }
        return $rows;
    }

    
    public static function get_data_asof() {
        global $wpdb;
        $erp = $wpdb->prefix . 's1erp_temp_store_orders_table';
        return self::table_exists($erp) ? $wpdb->get_var("SELECT DATE(MAX(OrderDate)) FROM `{$erp}`") : null;
    }

    
    public static function get_catalog_gaps($limit = 500) {
        global $wpdb;
        if (!self::table_exists(self::t_gap())) { return array(); }
        
        
        
        return (array) $wpdb->get_results(
            "SELECT g.*, DATE(p.post_date) AS first_created, DATEDIFF(NOW(), p.post_date) AS days_not_live
             FROM `" . self::t_gap() . "` g
             LEFT JOIN `{$wpdb->posts}` p ON p.ID = g.product_id
             WHERE 1=1" . self::gap_filter_sql('g.') . "
             ORDER BY g.phys_units DESC, g.priority_score DESC LIMIT " . (int) $limit, ARRAY_A);
    }

    
    public static function get_catalog_gap_totals() {
        global $wpdb;
        $win  = self::gap_recency();
        $zero = array('count' => 0, 'units' => 0.0, 'turnover' => 0.0, 'units_30d' => 0.0, 'turnover_30d' => 0.0, 'recoverable' => 0.0, 'recoverable_pess' => 0.0, 'recoverable_opt' => 0.0, 'window_days' => $win);
        if (!self::table_exists(self::t_gap())) { return $zero; }
        $r = $wpdb->get_row("SELECT COUNT(*) c, SUM(phys_units) u, SUM(phys_turnover) t, SUM(phys_units_30d) u30, SUM(phys_turnover_30d) t30 FROM `" . self::t_gap() . "` WHERE 1=1" . self::gap_filter_sql(''), ARRAY_A);
        $turnover = (float) ($r['t'] ?? 0);
        $cr       = self::channel_ratio();
        return array(
            'count'            => (int) ($r['c'] ?? 0),
            'units'            => (float) ($r['u'] ?? 0),
            'turnover'         => $turnover,
            'units_30d'        => (float) ($r['u30'] ?? 0),
            'turnover_30d'     => (float) ($r['t30'] ?? 0),
            'recoverable'      => $cr * $turnover,
            'recoverable_pess' => $cr * $turnover * self::LOST_PESS_FACTOR,
            'recoverable_opt'  => $cr * $turnover * self::LOST_OPT_FACTOR,
            'window_days'      => $win,
        );
    }

    

    private static function load_xlsx_writer() {
        if (!class_exists('AIEO_DMM_XLSX_Writer')) {
            require_once AIEO_DIR . 'includes/dmm/services/class-aieo-dmm-xlsx-writer.php';
        }
    }

    
    public function ajax_export_gaps() {
        AIEO_DMM::verify_ajax_request();
        self::load_xlsx_writer();
        $rows = self::get_catalog_gaps(100000); 
        $yn = static function ($v) { return ((int) $v === 1) ? __('yes', 'ai-eshop-optimizer') : ''; };
        $w = new AIEO_DMM_XLSX_Writer('Missing Hot Sellers');
        $w->add_header_row(array(
            '#', __('Product', 'ai-eshop-optimizer'), __('SKU', 'ai-eshop-optimizer'),
            __('Phys. units', 'ai-eshop-optimizer'), __('Sale-days', 'ai-eshop-optimizer'),
            __('Last sold', 'ai-eshop-optimizer'), __('Turnover net (€)', 'ai-eshop-optimizer'),
            __('Units (30d)', 'ai-eshop-optimizer'), __('Turnover 30d net (€)', 'ai-eshop-optimizer'),
            __('First created', 'ai-eshop-optimizer'), __('Days not live', 'ai-eshop-optimizer'),
            __('Status', 'ai-eshop-optimizer'), __('Needs image', 'ai-eshop-optimizer'),
            __('Needs description', 'ai-eshop-optimizer'), __('Needs categories', 'ai-eshop-optimizer'),
            __('Needs brand', 'ai-eshop-optimizer'), __('Not in eshop', 'ai-eshop-optimizer'),
            __('Edit URL', 'ai-eshop-optimizer'),
        ));
        $rank = 0;
        foreach ($rows as $g) {
            $rank++;
            $pid   = (int) $g['product_id'];
            $needs = ((int) $g['needs_creation'] === 1);
            $w->add_row(array(
                $rank,
                (string) ($g['name'] ?: $g['sku']),
                (string) $g['sku'],
                (float) $g['phys_units'],
                (int) $g['phys_sale_days'],
                (string) $g['last_phys_sale_date'],
                (float) $g['phys_turnover'],
                (float) ($g['phys_units_30d'] ?? 0),
                (float) ($g['phys_turnover_30d'] ?? 0),
                (string) ($g['first_created'] ?? ''),
                (isset($g['days_not_live']) && $g['days_not_live'] !== null && $g['days_not_live'] !== '') ? max(0, (int) $g['days_not_live']) : '',
                $needs ? __('not created', 'ai-eshop-optimizer') : (string) $g['post_status'],
                $needs ? '' : $yn($g['missing_image']),
                $needs ? '' : $yn($g['missing_desc']),
                $needs ? '' : $yn($g['missing_cats']),
                $needs ? '' : $yn($g['missing_brand']),
                $needs ? __('yes', 'ai-eshop-optimizer') : '',
                $pid > 0 ? admin_url('post.php?post=' . $pid . '&action=edit') : '',
            ), array(0 => 'integer', 3 => 'number', 4 => 'integer', 6 => 'currency', 7 => 'number', 8 => 'currency', 10 => 'integer'));
        }
        $w->stream_download('missing-hot-sellers-' . gmdate('Ymd-His') . '.xlsx');
    }

    
    public function ajax_export_recovery() {
        global $wpdb;
        AIEO_DMM::verify_ajax_request();
        self::load_xlsx_writer();
        $rows = self::table_exists(self::t_cand())
            ? (array) $wpdb->get_results(
                "SELECT * FROM `" . self::t_cand() . "` WHERE phys_units_recent > 0 AND status <> 'dismissed'
                 ORDER BY phys_turnover_recent DESC, meets_threshold DESC, confidence_score DESC, last_phys_sale_date DESC LIMIT 100000", ARRAY_A)
            : array();
        $num = static function ($v) { return ($v === null || $v === '') ? '' : (float) $v; };
        $w = new AIEO_DMM_XLSX_Writer('Stock Recovery');
        $w->add_header_row(array(
            __('Product', 'ai-eshop-optimizer'), __('SKU', 'ai-eshop-optimizer'),
            __('Days OOS', 'ai-eshop-optimizer'), __('OOS source', 'ai-eshop-optimizer'),
            __('Eshop stock', 'ai-eshop-optimizer'), __('Phys. units (after OOS)', 'ai-eshop-optimizer'),
            __('Phys. units (recent)', 'ai-eshop-optimizer'), __('Sale-days', 'ai-eshop-optimizer'),
            __('Last sold', 'ai-eshop-optimizer'), __('Piraeus', 'ai-eshop-optimizer'),
            __('Keratsini', 'ai-eshop-optimizer'), __('Korydallos', 'ai-eshop-optimizer'),
            __('Kallithea', 'ai-eshop-optimizer'), __('In-store net (€)', 'ai-eshop-optimizer'),
            __('Basket turnover (€)', 'ai-eshop-optimizer'), __('Basket orders', 'ai-eshop-optimizer'),
            __('Est. lost online — avg (€)', 'ai-eshop-optimizer'), __('Lost — pessimistic (€)', 'ai-eshop-optimizer'),
            __('Lost — optimistic (€)', 'ai-eshop-optimizer'), __('Velocity / day', 'ai-eshop-optimizer'),
            __('Suggested qty', 'ai-eshop-optimizer'), __('Meets threshold', 'ai-eshop-optimizer'),
            __('Confidence', 'ai-eshop-optimizer'), __('Status', 'ai-eshop-optimizer'),
            __('Blocked reason', 'ai-eshop-optimizer'),
        ));
        foreach ($rows as $c) {
            $w->add_row(array(
                (string) ($c['name'] ?: ('#' . (int) $c['product_id'])),
                (string) $c['sku'],
                (int) $c['days_oos'],
                (string) $c['oos_source'],
                (int) $c['eshop_stock'],
                (float) $c['phys_units_after'],
                (float) $c['phys_units_recent'],
                (int) $c['phys_sale_days'],
                (string) $c['last_phys_sale_date'],
                (float) $c['phys_piraeus'],
                (float) $c['phys_keratsini'],
                (float) $c['phys_korydallos'],
                (float) $c['phys_kallithea'],
                (float) $c['phys_turnover_after'],
                (float) $c['phys_basket_turnover_recent'],
                (int) $c['phys_basket_orders_recent'],
                $num($c['est_lost_sales']),
                $num($c['lost_pessimistic']),
                $num($c['lost_optimistic']),
                $num($c['velocity_per_day']),
                (int) $c['suggested_restock_qty'],
                ((int) $c['meets_threshold'] === 1) ? __('yes', 'ai-eshop-optimizer') : '',
                $num($c['confidence_score']),
                (string) $c['status'],
                (string) $c['blocked_reason'],
            ), array(
                2 => 'integer', 4 => 'integer', 5 => 'number', 6 => 'number', 7 => 'integer',
                9 => 'number', 10 => 'number', 11 => 'number', 12 => 'number',
                13 => 'currency', 14 => 'currency', 15 => 'integer',
                16 => 'currency', 17 => 'currency', 18 => 'currency',
                19 => 'number', 20 => 'integer', 22 => 'number',
            ));
        }
        $w->stream_download('stock-recovery-' . gmdate('Ymd-His') . '.xlsx');
    }

    
    public function ajax_export_restocks() {
        global $wpdb;
        AIEO_DMM::verify_ajax_request();
        self::load_xlsx_writer();
        $rows = self::table_exists(self::t_events())
            ? (array) $wpdb->get_results(
                "SELECT e.*, COALESCE(NULLIF(v.product_name, ''), v.name, p.post_title) AS product_name
                   FROM `" . self::t_events() . "` e
                   LEFT JOIN `" . $wpdb->prefix . "aieo_core_product_vitals` v ON v.product_id = e.product_id
                   LEFT JOIN `{$wpdb->posts}` p ON p.ID = e.product_id
                  ORDER BY e.id DESC LIMIT 100000", ARRAY_A)
            : array();
        $num = static function ($v) { return ($v === null || $v === '') ? '' : (float) $v; };
        $int = static function ($v) { return ($v === null || $v === '') ? '' : (int) $v; };
        $w = new AIEO_DMM_XLSX_Writer('Restocks');
        $w->add_header_row(array(
            __('When', 'ai-eshop-optimizer'), __('Product', 'ai-eshop-optimizer'), __('SKU', 'ai-eshop-optimizer'),
            __('Mode', 'ai-eshop-optimizer'), __('Restock qty', 'ai-eshop-optimizer'),
            __('Days OOS at apply', 'ai-eshop-optimizer'), __('Phys. units at apply', 'ai-eshop-optimizer'),
            __('Piraeus', 'ai-eshop-optimizer'), __('Keratsini', 'ai-eshop-optimizer'),
            __('Korydallos', 'ai-eshop-optimizer'), __('Kallithea', 'ai-eshop-optimizer'),
            __('Outcome', 'ai-eshop-optimizer'), __('Outcome reason', 'ai-eshop-optimizer'),
            __('Units recovered', 'ai-eshop-optimizer'), __('Turnover recovered (€)', 'ai-eshop-optimizer'),
            __('Orders recovered', 'ai-eshop-optimizer'), __('Reverted at', 'ai-eshop-optimizer'),
            __('Units in drafts', 'ai-eshop-optimizer'), __('Turnover in drafts (€)', 'ai-eshop-optimizer'),
        ));
        foreach ($rows as $e) {
            $w->add_row(array(
                (string) $e['applied_at'],
                (string) ($e['product_name'] ?: ('#' . (int) $e['product_id'])),
                (string) $e['sku'],
                (string) $e['mode'],
                $num($e['restock_qty']),
                $int($e['days_oos_at_apply']),
                $num($e['phys_units_after_at_apply']),
                $num($e['phys_piraeus_recent']),
                $num($e['phys_keratsini_recent']),
                $num($e['phys_korydallos_recent']),
                $num($e['phys_kallithea_recent']),
                (string) $e['outcome'],
                (string) $e['outcome_reason'],
                $num($e['eshop_units_recovered']),
                $num($e['eshop_turnover_recovered']),
                $int($e['eshop_orders_recovered']),
                (string) $e['reverted_at'],
                $num($e['eshop_units_draft']),
                $num($e['eshop_turnover_draft']),
            ), array(
                4 => 'number', 5 => 'integer', 6 => 'number',
                7 => 'number', 8 => 'number', 9 => 'number', 10 => 'number',
                13 => 'number', 14 => 'currency', 15 => 'integer',
                17 => 'number', 18 => 'currency',
            ));
        }
        $w->stream_download('stock-recovery-restocks-' . gmdate('Ymd-His') . '.xlsx');
    }

    
    public static function session_vars() {
        $en = self::enabled_branches();
        return array(
            '@pr_recency_days'       => (string) self::recency_days(),
            '@pr_min_units'          => (string) self::min_units(),
            '@pr_min_days'           => (string) self::min_days(),
            '@pr_min_days_oos'       => (string) self::min_days_oos(),
            '@pr_qty_cap'            => (string) self::qty_cap(),
            '@pr_cover_days'         => (string) self::cover_days(),
            '@pr_cooldown_days'      => (string) self::cooldown_days(),
            '@pr_use_piraeus'        => in_array('piraeus', $en, true) ? '1' : '0',
            '@pr_use_keratsini'      => in_array('keratsini', $en, true) ? '1' : '0',
            '@pr_use_korydallos'     => in_array('korydallos', $en, true) ? '1' : '0',
            '@pr_use_kallithea'      => in_array('kallithea', $en, true) ? '1' : '0',
            '@pr_gap_min_units'      => (string) self::gap_min_units(),
            '@pr_gap_recency_days'   => (string) self::gap_recency(),
            '@pr_outcome_window_days'=> (string) self::outcome_window(),
        );
    }

    
    public static function thresholds_snapshot() {
        return array(
            'min_units'     => self::min_units(),
            'min_days'      => self::min_days(),
            'recency_days'  => self::recency_days(),
            'min_days_oos'  => self::min_days_oos(),
            'qty_cap'       => self::qty_cap(),
            'cover_days'    => self::cover_days(),
            'branches'      => self::enabled_branches(),
            'auto'          => self::auto_apply_enabled() ? 1 : 0,
        );
    }

    
    public function register_hooks() {
        add_action('wp_ajax_aieo_pr_save_settings',     array($this, 'ajax_save_settings'));
        add_action('wp_ajax_aieo_pr_save_gap_settings', array($this, 'ajax_save_gap_settings'));
        add_action('wp_ajax_aieo_pr_apply_one',     array($this, 'ajax_apply_one'));
        add_action('wp_ajax_aieo_pr_apply_bulk',    array($this, 'ajax_apply_bulk'));
        add_action('wp_ajax_aieo_pr_dismiss',       array($this, 'ajax_dismiss'));
        add_action('wp_ajax_aieo_pr_blocklist',     array($this, 'ajax_blocklist'));
        add_action('wp_ajax_aieo_pr_revert',        array($this, 'ajax_revert'));
        add_action('wp_ajax_aieo_pr_run_detection', array($this, 'ajax_run_detection'));
        add_action('wp_ajax_aieo_pr_run_now',       array($this, 'ajax_run_now'));
        add_action('wp_ajax_aieo_pr_gap_dismiss',   array($this, 'ajax_gap_dismiss'));
        add_action('wp_ajax_aieo_pr_gap_restore',   array($this, 'ajax_gap_restore'));
        add_action('wp_ajax_aieo_pr_export_gaps',     array($this, 'ajax_export_gaps'));
        add_action('wp_ajax_aieo_pr_export_recovery', array($this, 'ajax_export_recovery'));
        add_action('wp_ajax_aieo_pr_export_restocks', array($this, 'ajax_export_restocks'));
        add_action('admin_enqueue_scripts',           array($this, 'maybe_enqueue_variation_focus'));
    }

    
    public function maybe_enqueue_variation_focus($hook) {
        if ($hook !== 'post.php' || empty($_GET['aieo_focus_variation'])) { return; } 
        $screen = function_exists('get_current_screen') ? get_current_screen() : null;
        if (!$screen || $screen->post_type !== 'product') { return; }
        if (!defined('AIEO_FILE')) { return; }
        $rel  = 'assets/js/dmm-variation-focus.js';
        $path = dirname(AIEO_FILE) . '/' . $rel;
        if (!is_readable($path)) { return; }
        wp_enqueue_script('aieo-dmm-variation-focus', plugins_url($rel, AIEO_FILE), array('jquery'), (string) filemtime($path), true);
    }

    
    
    public static function apply_restock($pid, $mode = 'manual', $user_id = null) {
        global $wpdb;
        $pid = (int) $pid;
        if (!function_exists('wc_get_product')) { return array('ok' => false, 'error' => 'wc_unavailable'); }
        if (get_post_meta($pid, '_aieo_sr_blocklisted', true) === '1') { return array('ok' => false, 'skipped' => 'blocklist'); }

        $last = get_post_meta($pid, '_aieo_sr_last_restock_at', true);
        if ($last && strtotime($last . ' UTC') >= strtotime('-' . self::cooldown_days() . ' days')) {
            return array('ok' => false, 'skipped' => 'cooldown');
        }
        $p = wc_get_product($pid);
        if (!$p) { return array('ok' => false, 'error' => 'no_product'); }
        if ($p->get_stock_status() === 'instock') { return array('ok' => false, 'skipped' => 'already_instock'); }

        $managing      = $p->managing_stock();
        $prior_status  = $p->get_stock_status();
        $prior_qty     = $p->get_stock_quantity();
        $prior_manage  = $managing ? 1 : 0;

        $cand = self::table_exists(self::t_cand())
            ? $wpdb->get_row($wpdb->prepare('SELECT * FROM `' . self::t_cand() . '` WHERE product_id = %d', $pid), ARRAY_A)
            : null;
        $qty = $cand ? (int) $cand['suggested_restock_qty'] : 1;
        if ($qty < 1) { $qty = 1; }

        
        
        if ($p->is_type('variation')) {
            
            
            
            
            if ($managing) { wc_update_product_stock($pid, $qty, 'set'); }
            wc_update_product_stock_status($pid, 'instock');
            wc_delete_product_transients($pid);
            
            
            $parent_for_sync = $p->get_parent_id();
            if ($parent_for_sync) {
                wc_update_product_stock_status($parent_for_sync, 'instock');
                wc_delete_product_transients($parent_for_sync);
            }
        } else {
            if ($managing) { $p->set_stock_quantity($qty); }
            $p->set_stock_status('instock');
            $p->save();
        }

        
        $vit = $wpdb->prefix . 'aieo_core_product_vitals';
        if (self::table_exists($vit)) {
            $wpdb->query($wpdb->prepare("UPDATE `{$vit}` SET stock = %d, stock_status = 'instock' WHERE product_id = %d", $managing ? $qty : 1, $pid));
        }

        $now = current_time('mysql', true); 
        $wpdb->insert(self::t_events(), array(
            'product_id'                => $pid,
            'parent_id'                 => $cand ? (int) $cand['parent_id'] : null,
            'sku'                       => $cand ? $cand['sku'] : $p->get_sku(),
            'mode'                      => $mode,
            'applied_by'                => $user_id ? (int) $user_id : null,
            'applied_at'                => $now,
            'oos_since_at_apply'        => $cand ? $cand['oos_since'] : null,
            'days_oos_at_apply'         => $cand ? (int) $cand['days_oos'] : null,
            'phys_units_after_at_apply' => $cand ? $cand['phys_units_after'] : null,
            'phys_sale_days_at_apply'   => $cand ? (int) $cand['phys_sale_days'] : null,
            
            
            
            'phys_turnover_recent_at_apply' => $cand ? $cand['phys_turnover_recent'] : null,
            'phys_turnover_after_at_apply'  => $cand ? $cand['phys_turnover_after'] : null,
            
            
            
            'phys_basket_turnover_at_apply' => $cand ? $cand['phys_basket_turnover_recent'] : null,
            
            
            'phys_piraeus_recent'       => $cand ? $cand['phys_piraeus_recent'] : null,
            'phys_keratsini_recent'     => $cand ? $cand['phys_keratsini_recent'] : null,
            'phys_korydallos_recent'    => $cand ? $cand['phys_korydallos_recent'] : null,
            'phys_kallithea_recent'     => $cand ? $cand['phys_kallithea_recent'] : null,
            'restock_qty'               => $managing ? $qty : null,
            'prior_stock'               => ($prior_qty === null || $prior_qty === '') ? null : (int) $prior_qty,
            'prior_stock_status'        => $prior_status,
            'prior_manage_stock'        => $prior_manage,
            'thresholds_json'           => wp_json_encode(self::thresholds_snapshot()),
            'outcome'                   => 'pending',
            'created_at'                => $now,
        ));
        $event_id = (int) $wpdb->insert_id;

        update_post_meta($pid, '_aieo_sr_last_restock_at', $now);
        $cnt = (int) get_post_meta($pid, '_aieo_sr_restock_count', true);
        update_post_meta($pid, '_aieo_sr_restock_count', $cnt + 1);

        if ($cand) {
            $wpdb->update(self::t_cand(),
                array('status' => ($mode === 'auto' ? 'auto_applied' : 'applied'), 'last_event_id' => $event_id),
                array('product_id' => $pid), array('%s', '%d'), array('%d'));
        }
        
        
        self::sync_auto_restock_vitals_flag($pid);
        do_action('aieo_dmm_after_stock_recovery', $pid, $mode, $qty, $event_id);
        return array('ok' => true, 'event_id' => $event_id, 'qty' => ($managing ? $qty : 0));
    }

    
    public static function revert_restock($event_id) {
        global $wpdb;
        $event_id = (int) $event_id;
        $ev = $wpdb->get_row($wpdb->prepare('SELECT * FROM `' . self::t_events() . '` WHERE id = %d', $event_id), ARRAY_A);
        if (!$ev) { return array('ok' => false, 'error' => 'no_event'); }
        if (!empty($ev['reverted_at'])) { return array('ok' => false, 'skipped' => 'already_reverted'); }
        $pid = (int) $ev['product_id'];
        if (function_exists('wc_get_product') && ($p = wc_get_product($pid))) {
            $prior_qty_revert    = ((int) $ev['prior_manage_stock'] === 1 && $ev['prior_stock'] !== null) ? (int) $ev['prior_stock'] : null;
            $prior_status_revert = $ev['prior_stock_status'] ?: 'outofstock';
            if ($p->is_type('variation')) {
                
                if ($prior_qty_revert !== null) { wc_update_product_stock($pid, $prior_qty_revert, 'set'); }
                wc_update_product_stock_status($pid, $prior_status_revert);
                wc_delete_product_transients($pid);
                
                
                $parent_for_sync = $p->get_parent_id();
                if ($parent_for_sync) {
                    $sib_instock = $wpdb->get_var($wpdb->prepare(
                        "SELECT 1 FROM {$wpdb->posts} pp JOIN {$wpdb->postmeta} pm ON pm.post_id = pp.ID AND pm.meta_key = '_stock_status'
                         WHERE pp.post_parent = %d AND pp.post_type = 'product_variation' AND pm.meta_value = 'instock' LIMIT 1",
                        $parent_for_sync));
                    wc_update_product_stock_status($parent_for_sync, $sib_instock ? 'instock' : 'outofstock');
                    wc_delete_product_transients($parent_for_sync);
                }
            } else {
                if ($prior_qty_revert !== null) { $p->set_stock_quantity($prior_qty_revert); }
                $p->set_stock_status($prior_status_revert);
                $p->save();
            }
            $vit = $wpdb->prefix . 'aieo_core_product_vitals';
            if (self::table_exists($vit)) {
                $wpdb->query($wpdb->prepare("UPDATE `{$vit}` SET stock = %d, stock_status = %s WHERE product_id = %d",
                    ($ev['prior_stock'] !== null ? (int) $ev['prior_stock'] : 0), ($ev['prior_stock_status'] ?: 'outofstock'), $pid));
            }
        }
        $wpdb->update(self::t_events(),
            array('outcome' => 'reverted', 'outcome_reason' => 'manual_revert', 'reverted_at' => current_time('mysql', true), 'outcome_evaluated_at' => current_time('mysql', true)),
            array('id' => $event_id), array('%s', '%s', '%s', '%s'), array('%d'));
        
        self::sync_auto_restock_vitals_flag($pid);
        return array('ok' => true);
    }

    
    public static function run_auto_apply() {
        global $wpdb;
        if (!self::table_exists(self::t_cand())) { return 0; }
        $ids = $wpdb->get_col("SELECT product_id FROM `" . self::t_cand() . "` WHERE meets_threshold = 1 AND status = 'pending' AND blocked_reason IS NULL");
        $n = 0;
        foreach ((array) $ids as $pid) {
            $r = self::apply_restock((int) $pid, 'auto', null);
            if (!empty($r['ok'])) { $n++; }
        }
        return $n;
    }

    
    public static function sync_auto_restock_vitals_flag($pid = 0) {
        global $wpdb;
        $vit = $wpdb->prefix . 'aieo_core_product_vitals';
        if (!self::table_exists($vit) || !self::table_exists(self::t_events())) { return; }
        if (function_exists('aieo_dmm_ensure_column')) {
            aieo_dmm_ensure_column($vit, 'is_auto_restocked', 'tinyint(1) DEFAULT 0');
        }
        $ev = self::t_events();

        if ((int) $pid > 0) {
            $pid = (int) $pid;
            
            $is_auto = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT (ev.mode = 'auto') FROM `{$ev}` ev
                  WHERE ev.id = (SELECT MAX(e2.id) FROM `{$ev}` e2 WHERE e2.product_id = %d AND e2.reverted_at IS NULL)",
                $pid));
            $wpdb->query($wpdb->prepare("UPDATE `{$vit}` SET is_auto_restocked = %d WHERE product_id = %d", $is_auto ? 1 : 0, $pid));
            return;
        }

        
        
        $wpdb->query("UPDATE `{$vit}` SET is_auto_restocked = 0 WHERE is_auto_restocked = 1");
        $ids = $wpdb->get_col(
            "SELECT e.product_id FROM `{$ev}` e
               JOIN (SELECT product_id, MAX(id) mid FROM `{$ev}` WHERE reverted_at IS NULL GROUP BY product_id) m
                 ON m.product_id = e.product_id AND m.mid = e.id
              WHERE e.mode = 'auto'");
        if (!empty($ids)) {
            $ids = array_map('intval', $ids);
            $ph  = implode(',', array_fill(0, count($ids), '%d'));
            
            $wpdb->query($wpdb->prepare("UPDATE `{$vit}` SET is_auto_restocked = 1 WHERE product_id IN ($ph)", $ids));
        }
    }

    
    public function ajax_save_settings() {
        AIEO_DMM::verify_ajax_request();
        $map = array(
            self::OPT_AUTO           => array('auto',           0, 1),
            self::OPT_MIN_UNITS      => array('min_units',      1, 999),
            self::OPT_MIN_DAYS       => array('min_days',       1, 60),
            self::OPT_RECENCY_DAYS   => array('recency_days',   3, 365),
            self::OPT_MIN_DAYS_OOS   => array('min_days_oos',   0, 365),
            self::OPT_QTY_CAP        => array('qty_cap',        1, 999),
            self::OPT_COVER_DAYS     => array('cover_days',     1, 120),
            self::OPT_COOLDOWN_DAYS  => array('cooldown_days',  1, 180),
            self::OPT_OUTCOME_WINDOW => array('outcome_window', 3, 120),
            self::OPT_GAP_MIN_UNITS  => array('gap_min_units',  1, 999),
            self::OPT_GAP_RECENCY    => array('gap_recency',    3, 365),
            self::OPT_CHANNEL_PCT    => array('channel_pct',    1, 100),
        );
        foreach ($map as $opt => $spec) {
            list($key, $min, $max) = $spec;
            if (isset($_POST[$key])) {
                $v = (int) $_POST[$key];
                if ($v < $min) { $v = $min; }
                if ($v > $max) { $v = $max; }
                update_option($opt, $v, false);
            }
        }
        
        $branches = isset($_POST['branches']) ? (array) wp_unslash($_POST['branches']) : array();
        $branches = array_values(array_intersect(self::BRANCHES, array_map('sanitize_key', $branches)));
        update_option(self::OPT_BRANCH_FILTER, $branches, false);

        wp_send_json_success(array('message' => __('Settings saved.', 'ai-eshop-optimizer')));
    }

    
    public function ajax_save_gap_settings() {
        AIEO_DMM::verify_ajax_request();
        if (isset($_POST['gap_min_units'])) {
            $v = (int) $_POST['gap_min_units']; $v = max(1, min(999, $v));
            update_option(self::OPT_GAP_MIN_UNITS, $v, false);
        }
        if (isset($_POST['gap_recency'])) {
            $v = (int) $_POST['gap_recency']; $v = max(3, min(365, $v));
            update_option(self::OPT_GAP_RECENCY, $v, false);
        }
        if (isset($_POST['gap_statuses'])) {
            $allowed = array('draft', 'private', 'pending', self::GAP_STATUS_NC);
            $in = array_map('sanitize_text_field', (array) wp_unslash($_POST['gap_statuses']));
            update_option(self::OPT_GAP_STATUSES, array_values(array_intersect($allowed, $in)), false);
        }
        if (isset($_POST['gap_name_excludes'])) {
            $parts = preg_split('/[\r\n,]+/', (string) wp_unslash($_POST['gap_name_excludes']));
            $out   = array();
            foreach ((array) $parts as $p) {
                $p = sanitize_text_field(trim($p));
                if ($p !== '' && !in_array($p, $out, true)) { $out[] = $p; }
            }
            update_option(self::OPT_GAP_NAME_EXCLUDES, $out, false);
        }
        wp_send_json_success(array('message' => __('Settings saved.', 'ai-eshop-optimizer')));
    }

    public function ajax_apply_one() {
        AIEO_DMM::verify_ajax_request();
        $pid = isset($_POST['product_id']) ? (int) $_POST['product_id'] : 0;
        if ($pid <= 0) { wp_send_json_error(array('message' => __('Invalid product.', 'ai-eshop-optimizer'))); }
        $r = self::apply_restock($pid, 'manual', get_current_user_id());
        if (empty($r['ok'])) {
            wp_send_json_error(array('message' => isset($r['skipped']) ? sprintf(__('Skipped (%s).', 'ai-eshop-optimizer'), $r['skipped']) : __('Could not restock.', 'ai-eshop-optimizer')));
        }
        wp_send_json_success($r);
    }

    public function ajax_apply_bulk() {
        AIEO_DMM::verify_ajax_request();
        global $wpdb;
        @set_time_limit(0); 
        $offset = isset($_POST['offset']) ? max(0, (int) $_POST['offset']) : 0;
        $batch  = 50;
        if (!self::table_exists(self::t_cand())) { wp_send_json_error(array('message' => __('Run detection first.', 'ai-eshop-optimizer'))); }
        $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM `" . self::t_cand() . "` WHERE meets_threshold = 1 AND status = 'pending' AND blocked_reason IS NULL");
        $ids   = $wpdb->get_col($wpdb->prepare(
            "SELECT product_id FROM `" . self::t_cand() . "` WHERE meets_threshold = 1 AND status = 'pending' AND blocked_reason IS NULL ORDER BY product_id ASC LIMIT %d",
            $batch
        )); 
        $applied = 0; $skipped = 0; $skip_ids = array();
        foreach ((array) $ids as $pid) {
            $r = self::apply_restock((int) $pid, 'bulk', get_current_user_id());
            if (!empty($r['ok'])) { $applied++; } else { $skipped++; $skip_ids[] = (int) $pid; }
        }
        
        
        
        
        
        if (!empty($skip_ids)) {
            $ph = implode(',', array_fill(0, count($skip_ids), '%d'));
            
            $wpdb->query($wpdb->prepare(
                "UPDATE `" . self::t_cand() . "` SET blocked_reason = 'skipped' WHERE product_id IN ($ph) AND status = 'pending' AND blocked_reason IS NULL",
                $skip_ids
            ));
        }
        $remaining = (int) $wpdb->get_var("SELECT COUNT(*) FROM `" . self::t_cand() . "` WHERE meets_threshold = 1 AND status = 'pending' AND blocked_reason IS NULL");
        wp_send_json_success(array(
            'applied'   => $applied,
            'skipped'   => $skipped,
            'remaining' => $remaining,
            'total'     => $total,
            'done'      => ($remaining <= 0 || (count((array) $ids) === 0)),
            'next_offset' => $offset, 
        ));
    }

    public function ajax_dismiss() {
        AIEO_DMM::verify_ajax_request();
        global $wpdb;
        $pid = isset($_POST['product_id']) ? (int) $_POST['product_id'] : 0;
        if ($pid > 0 && self::table_exists(self::t_cand())) {
            $wpdb->update(self::t_cand(), array('status' => 'dismissed'), array('product_id' => $pid), array('%s'), array('%d'));
        }
        wp_send_json_success();
    }

    public function ajax_blocklist() {
        AIEO_DMM::verify_ajax_request();
        $pid = isset($_POST['product_id']) ? (int) $_POST['product_id'] : 0;
        $on  = isset($_POST['on']) ? (int) $_POST['on'] : 1;
        if ($pid > 0) {
            if ($on) { update_post_meta($pid, '_aieo_sr_blocklisted', '1'); }
            else { delete_post_meta($pid, '_aieo_sr_blocklisted'); }
        }
        wp_send_json_success();
    }

    public function ajax_revert() {
        AIEO_DMM::verify_ajax_request();
        $eid = isset($_POST['event_id']) ? (int) $_POST['event_id'] : 0;
        $r = self::revert_restock($eid);
        if (empty($r['ok'])) { wp_send_json_error(array('message' => __('Could not revert.', 'ai-eshop-optimizer'))); }
        wp_send_json_success($r);
    }

    public function ajax_gap_dismiss() {
        AIEO_DMM::verify_ajax_request();
        $sku_norm = isset($_POST['sku_norm']) ? sanitize_text_field(wp_unslash($_POST['sku_norm'])) : '';
        if ($sku_norm !== '') {
            $bl = self::gap_blocklist();
            if (!in_array($sku_norm, $bl, true)) { $bl[] = $sku_norm; }
            update_option(self::OPT_GAP_BLOCKLIST, $bl, false);
            global $wpdb;
            if (self::table_exists(self::t_gap())) {
                $wpdb->update(self::t_gap(), array('status' => 'dismissed'), array('sku_norm' => $sku_norm), array('%s'), array('%s'));
            }
        }
        wp_send_json_success();
    }

    
    public function ajax_gap_restore() {
        AIEO_DMM::verify_ajax_request();
        $sku_norm = isset($_POST['sku_norm']) ? sanitize_text_field(wp_unslash($_POST['sku_norm'])) : '';
        if ($sku_norm !== '') {
            update_option(self::OPT_GAP_BLOCKLIST, array_values(array_diff(self::gap_blocklist(), array($sku_norm))), false);
            global $wpdb;
            if (self::table_exists(self::t_gap())) {
                $wpdb->update(self::t_gap(), array('status' => 'pending'), array('sku_norm' => $sku_norm, 'status' => 'dismissed'), array('%s'), array('%s', '%s'));
            }
        }
        wp_send_json_success();
    }

    
    public function ajax_run_detection() {
        AIEO_DMM::verify_ajax_request();
        if (!class_exists('AIEO_DMM_Product_Orders_Analytics')) {
            wp_send_json_error(array('message' => __('Analytics module unavailable.', 'ai-eshop-optimizer')));
        }
        $mod = new AIEO_DMM_Product_Orders_Analytics();
        $det = $mod->run_job('stock_recovery', 'manual');
        $out = $mod->run_job('stock_recovery_outcomes', 'manual');
        if (empty($det['ok'])) {
            wp_send_json_error(array('message' => sprintf(__('Detection failed: %s', 'ai-eshop-optimizer'), $det['error'] ?? 'error')));
        }
        wp_send_json_success(array(
            'message' => sprintf(__('Detection done — %s candidates.', 'ai-eshop-optimizer'), number_format_i18n((int) ($det['rows'] ?? 0))),
            'detection' => $det,
            'outcomes'  => $out,
        ));
    }

    
    public function ajax_run_now() {
        AIEO_DMM::verify_ajax_request();
        if (!class_exists('AIEO_DMM_Product_Orders_Analytics')) {
            wp_send_json_error(array('message' => __('Analytics module unavailable.', 'ai-eshop-optimizer')));
        }
        if (!self::table_exists(self::t_cand())) {
            wp_send_json_error(array('message' => __('Run detection first.', 'ai-eshop-optimizer')));
        }
        
        $applied = (int) self::run_auto_apply();
        
        
        $mod = new AIEO_DMM_Product_Orders_Analytics();
        $mod->run_job('stock_recovery', 'manual');
        $mod->run_job('stock_recovery_outcomes', 'manual');
        wp_send_json_success(array(
            'applied' => $applied,
            'message' => $applied > 0
                ? sprintf(
                    
                    _n('Restocked %d product and re-scanned the queue.', 'Restocked %d products and re-scanned the queue.', $applied, 'ai-eshop-optimizer'),
                    $applied
                )
                : __('Nothing met the thresholds (or all are in cooldown), so nothing was restocked. Widen the recency window / lower the minimums above, click Save settings, then Run now.', 'ai-eshop-optimizer'),
        ));
    }

    
    public function render_subtab() {
        global $wpdb;
        $have = self::table_exists(self::t_cand());

        $candidates = array(); $events = array(); $events_total = 0; $kpis = array();
        $cand_totals = array('count' => 0, 'count_recent' => 0, 'units' => 0.0, 'units_recent' => 0.0, 'turnover' => 0.0, 'turnover_recent' => 0.0, 'lost' => 0.0, 'lost_pess' => 0.0, 'lost_opt' => 0.0, 'lost_recent' => 0.0, 'lost_pess_recent' => 0.0, 'lost_opt_recent' => 0.0);
        if ($have) {
            $candidates = $wpdb->get_results(
                "SELECT * FROM `" . self::t_cand() . "` WHERE phys_units_recent > 0 AND status <> 'dismissed'
                 ORDER BY phys_turnover_recent DESC, meets_threshold DESC, confidence_score DESC, last_phys_sale_date DESC LIMIT 500", ARRAY_A);
            
            
            $ct = $wpdb->get_row(
                "SELECT COUNT(*) c, SUM(phys_units_recent > 0) c_recent,
                        SUM(phys_units_after) u, SUM(phys_turnover_after) t,
                        SUM(phys_units_recent) ur, SUM(phys_turnover_recent) tr
                 FROM `" . self::t_cand() . "` WHERE phys_units_after > 0 AND status <> 'dismissed'", ARRAY_A);
            if (is_array($ct)) {
                
                
                
                
                
                
                
                
                
                
                
                
                
                $r        = self::channel_ratio();
                $tov_win  = (float) $ct['tr'];   
                $tov_life = (float) $ct['t'];    
                $cand_totals = array(
                    'count'           => (int) $ct['c'],
                    'count_recent'    => (int) $ct['c_recent'],
                    'units'           => (float) $ct['u'],
                    'units_recent'    => (float) $ct['ur'],
                    'turnover'        => $tov_life,
                    'turnover_recent' => $tov_win,
                    'lost'             => $r * $tov_life,                            
                    'lost_recent'      => $r * $tov_win,                             
                    'lost_pess_recent' => $r * $tov_win * self::LOST_PESS_FACTOR,
                    'lost_opt_recent'  => $r * $tov_win * self::LOST_OPT_FACTOR,
                );
            }
        }
        if (self::table_exists(self::t_events())) {
            $events = $wpdb->get_results(
                "SELECT e.*, COALESCE(NULLIF(v.product_name, ''), v.name, p.post_title) AS product_name, v.image_url
                   FROM `" . self::t_events() . "` e
                   LEFT JOIN `" . $wpdb->prefix . "aieo_core_product_vitals` v ON v.product_id = e.product_id
                   LEFT JOIN `{$wpdb->posts}` p ON p.ID = e.product_id
                  ORDER BY e.id DESC LIMIT 1000", ARRAY_A);
            $events_total = (int) $wpdb->get_var("SELECT COUNT(*) FROM `" . self::t_events() . "`");
            $row = $wpdb->get_row(
                "SELECT
                   COUNT(*) AS total,
                   SUM(mode='auto') AS autos,
                   SUM(mode<>'auto') AS manuals,
                   SUM(outcome='success') AS successes,
                   SUM(outcome='failure') AS failures,
                   SUM(outcome='pending') AS pendings,
                   SUM(eshop_units_recovered) AS units,
                   SUM(eshop_turnover_recovered) AS turnover,
                   SUM(CASE WHEN mode='auto' THEN eshop_units_recovered ELSE 0 END) AS auto_units,
                   SUM(CASE WHEN mode='auto' THEN eshop_turnover_recovered ELSE 0 END) AS auto_turnover,
                   SUM(CASE WHEN mode<>'auto' THEN eshop_turnover_recovered ELSE 0 END) AS manual_turnover,
                   SUM(outcome_reason='reoos_nosale') AS reoos,
                   SUM(outcome_reason IN ('cancelled','refunded')) AS cancelled,
                   SUM(CASE WHEN reverted_at IS NULL THEN COALESCE(phys_turnover_recent_at_apply,0) ELSE 0 END) AS target_instore_recent,
                   SUM(CASE WHEN reverted_at IS NULL THEN COALESCE(eshop_basket_turnover,0) ELSE 0 END) AS basket_turnover_total,
                   SUM(eshop_units_draft) AS units_draft,
                   SUM(eshop_turnover_draft) AS turnover_draft,
                   SUM(eshop_orders_draft) AS orders_draft,
                   SUM(CASE WHEN reverted_at IS NULL THEN COALESCE(eshop_basket_draft,0) ELSE 0 END) AS basket_draft_total
                 FROM `" . self::t_events() . "`", ARRAY_A);
            $kpis = is_array($row) ? $row : array();
            $den  = (int) ($kpis['successes'] ?? 0) + (int) ($kpis['failures'] ?? 0);
            $kpis['precision'] = $den > 0 ? round(100 * (int) $kpis['successes'] / $den, 1) : null;
            
            
            
            
            $kpis['target_recoverable'] = self::channel_ratio() * (float) ($kpis['target_instore_recent'] ?? 0);
            $kpis['channel_pct']        = self::channel_pct();

            
            
            
            
            $opa = $wpdb->prefix . 'aieo_order_products_analytics';
            $ord = $wpdb->prefix . 'wc_orders';
            if (self::table_exists($opa) && self::table_exists($ord)) {
                $et = self::t_events();
                $bj = "JOIN `{$opa}` 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 `{$ord}` o ON o.id=CAST(opa.order_id AS UNSIGNED) WHERE e.reverted_at IS NULL";
                $kpis['basket_turnover_total'] = (float) $wpdb->get_var("SELECT COALESCE(SUM(order_total),0) FROM (SELECT DISTINCT opa.order_id, opa.order_total FROM `{$et}` e {$bj} AND o.status NOT IN ('wc-cancelled','wc-refunded','wc-failed','wc-checkout-draft','auto-draft','trash')) z");
                $kpis['basket_draft_total']    = (float) $wpdb->get_var("SELECT COALESCE(SUM(order_total),0) FROM (SELECT DISTINCT opa.order_id, opa.order_total FROM `{$et}` e {$bj} AND o.status IN ('wc-checkout-draft','auto-draft')) z");
            }

            
            
            
            
            
            
            
            
            $oc = $wpdb->prefix . 'aieo_dmm_order_cancellations';
            $kpis['oos_cancel'] = 0;
            if (self::table_exists($oc)) {
                $et2 = self::t_events();
                $win = (int) self::outcome_window();
                $kpis['oos_cancel'] = (int) $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(DISTINCT e.id)
                       FROM `{$et2}` e
                       JOIN `{$oc}` c
                         ON c.reason_slug = 'out_of_stock'
                        AND c.product_ids IS NOT NULL AND JSON_VALID(c.product_ids)
                        AND c.cancelled_at > e.applied_at
                        AND c.cancelled_at <= (e.applied_at + INTERVAL %d DAY)
                        AND ( JSON_CONTAINS(c.product_ids, CAST(e.product_id AS CHAR))
                              OR (e.parent_id > 0 AND JSON_CONTAINS(c.product_ids, CAST(e.parent_id AS CHAR))) )
                      WHERE e.reverted_at IS NULL",
                    $win
                ));
            }
        }

        $this->render_template('physical-recovery.php', array(
            'nonce'        => wp_create_nonce(AIEO_DMM::NONCE_ACTION),
            'have_tables'  => $have,
            'data_asof'    => self::get_data_asof(),
            'candidates'   => (array) $candidates,
            'events'       => (array) $events,
            'events_total' => $events_total,
            'kpis'         => $kpis,
            'cand_totals'  => $cand_totals,
            'settings'     => array(
                'auto'           => self::auto_apply_enabled() ? 1 : 0,
                'min_units'      => self::min_units(),
                'min_days'       => self::min_days(),
                'recency_days'   => self::recency_days(),
                'min_days_oos'   => self::min_days_oos(),
                'qty_cap'        => self::qty_cap(),
                'cover_days'     => self::cover_days(),
                'cooldown_days'  => self::cooldown_days(),
                'outcome_window' => self::outcome_window(),
                'gap_min_units'  => self::gap_min_units(),
                'gap_recency'    => self::gap_recency(),
                'channel_pct'    => self::channel_pct(),
                'branches'       => self::enabled_branches(),
            ),
            'all_branches' => self::BRANCHES,
        ));
    }

    
    
    public static function on_product_status_change($new_status, $old_status, $post) {
        if (!is_object($post) || empty($post->post_type)) { return; }
        if (!in_array($post->post_type, array('product', 'product_variation'), true)) { return; }
        if ($new_status === $old_status) { return; }
        if ($new_status === 'publish' && $old_status !== 'publish') {
            self::log_gap_publish((int) $post->ID, (string) $old_status);
        } elseif ($old_status === 'publish' && $new_status !== 'publish') {
            self::close_gap_publish((int) $post->ID);
        }
    }

    
    private static function norm_sku($sku) {
        return strtoupper(str_replace(' ', '', (string) $sku));
    }

    
    public static function log_gap_publish($pid, $old_status = '') {
        global $wpdb;
        $pid = (int) $pid;
        if ($pid <= 0 || !self::table_exists(self::t_gap_events())) { return; }

        $sku = '';
        if (function_exists('wc_get_product') && ($p = wc_get_product($pid))) { $sku = (string) $p->get_sku(); }
        if ($sku === '') { $sku = (string) get_post_meta($pid, '_sku', true); }
        $sku_norm = self::norm_sku($sku);

        
        
        
        $gap = null;
        if (self::table_exists(self::t_gap())) {
            $gap = $wpdb->get_row($wpdb->prepare(
                'SELECT * FROM `' . self::t_gap() . '` WHERE product_id = %d' .
                ($sku_norm !== '' ? ' OR sku_norm = %s' : '') .
                ' ORDER BY (product_id = %d) DESC LIMIT 1',
                $sku_norm !== '' ? array($pid, $sku_norm, $pid) : array($pid, $pid)
            ), ARRAY_A);
        }
        if (!$gap) { return; }

        
        $open = (int) $wpdb->get_var($wpdb->prepare(
            'SELECT id FROM `' . self::t_gap_events() . '` WHERE product_id = %d AND unpublished_at IS NULL LIMIT 1', $pid));
        if ($open > 0) { return; }

        $now = current_time('mysql', true); 
        $wpdb->insert(self::t_gap_events(), array(
            'product_id'                      => $pid,
            'sku'                             => $sku !== '' ? $sku : ($gap['sku'] ?? null),
            'sku_norm'                        => $sku_norm !== '' ? $sku_norm : ($gap['sku_norm'] ?? null),
            'name'                            => $gap['name'] ?? null,
            'published_at'                    => $now,
            'published_by'                    => get_current_user_id() ?: null,
            'was_needs_creation'              => (int) ($gap['needs_creation'] ?? 0),
            'post_status_before'              => $old_status !== '' ? $old_status : ($gap['post_status'] ?? null),
            'phys_units_recent_at_publish'    => $gap['phys_units_recent'] ?? null,
            'phys_turnover_recent_at_publish' => $gap['phys_turnover'] ?? null,
            'phys_turnover_at_publish'        => $gap['phys_turnover'] ?? null,
            'created_at'                      => $now,
        ));

        
        
        if (self::table_exists(self::t_gap())) {
            $wpdb->update(self::t_gap(), array('status' => 'published'), array('product_id' => $pid), array('%s'), array('%d'));
            if ($sku_norm !== '') {
                $wpdb->update(self::t_gap(), array('status' => 'published'), array('sku_norm' => $sku_norm), array('%s'), array('%s'));
            }
        }
    }

    
    public static function close_gap_publish($pid) {
        global $wpdb;
        $pid = (int) $pid;
        if ($pid <= 0 || !self::table_exists(self::t_gap_events())) { return; }
        $wpdb->query($wpdb->prepare(
            'UPDATE `' . self::t_gap_events() . '` SET unpublished_at = %s WHERE product_id = %d AND unpublished_at IS NULL',
            current_time('mysql', true), $pid));
    }

    
    public static function get_gap_published_kpis() {
        global $wpdb;
        $zero = array('published_count' => 0, 'open_count' => 0, 'units_since' => 0.0, 'turnover_since' => 0.0, 'orders_since' => 0, 'instore_turnover' => 0.0);
        if (!self::table_exists(self::t_gap_events())) { return $zero; }
        $r = $wpdb->get_row(
            "SELECT COUNT(*) AS published_count,
                    SUM(unpublished_at IS NULL) AS open_count,
                    SUM(eshop_units_since) AS units_since,
                    SUM(eshop_turnover_since) AS turnover_since,
                    SUM(eshop_orders_since) AS orders_since,
                    SUM(COALESCE(phys_turnover_recent_at_publish, 0)) AS instore_turnover
             FROM `" . self::t_gap_events() . "`", ARRAY_A);
        return is_array($r) ? array_map(static function ($v) { return $v === null ? 0 : $v; }, $r) : $zero;
    }

    
    public static function get_gap_published_events($limit = 200) {
        global $wpdb;
        if (!self::table_exists(self::t_gap_events())) { return array(); }
        return (array) $wpdb->get_results(
            "SELECT ge.*, COALESCE(NULLIF(v.product_name, ''), v.name, p.post_title) AS product_name, v.image_url
               FROM `" . self::t_gap_events() . "` ge
               LEFT JOIN `" . $wpdb->prefix . "aieo_core_product_vitals` v ON v.product_id = ge.product_id
               LEFT JOIN `{$wpdb->posts}` p ON p.ID = ge.product_id
              ORDER BY ge.published_at DESC LIMIT " . (int) $limit, ARRAY_A);
    }
}

if (!has_action('transition_post_status', array('AIEO_DMM_Physical_Recovery', 'on_product_status_change'))) {
    add_action('transition_post_status', array('AIEO_DMM_Physical_Recovery', 'on_product_status_change'), 20, 3);
}
