<?php

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

class AIEO_DMM_Product_Orders_Analytics extends AIEO_DMM_Module_Base {

    const SETTINGS_OPTION = 'aieo_analytics_jobs_schedule';   
    const LAST_RUN_OPTION = 'aieo_analytics_jobs_last_run';   
    const SCHEMA_OPTION   = 'aieo_analytics_jobs_sproc_version';
    const SCHEMA_VERSION  = '22'; 
    const CRON_HOOK       = 'aieo_analytics_job_run';          
    const PAGES_OPTION    = 'aieo_wpdt_enhance_pages';         
    const GRAIN_OPTION    = 'aieo_sales_predictor_grain';      
    const REFERENCE_OPTION = 'aieo_sales_predictor_reference'; 
    const STALENESS_OPTION = 'aieo_sales_predictor_staleness'; 

    public function get_id()       { return 'product_orders_analytics'; }
    public function get_label()    { return __('Product/Orders analytics', 'ai-eshop-optimizer'); }
    public function get_priority() { return 86; }
    public function get_group()    { return 'tools'; }

    
    private static function load_sprocs_file() {
        $f = AIEO_DIR . 'includes/sql/analytics-jobs-sprocs.php';
        if (is_readable($f)) { require_once $f; }
    }

    
    public static function jobs() {
        self::load_sprocs_file();
        if (!function_exists('aieo_analytics_jobs_definitions')) { return array(); }
        $defs  = aieo_analytics_jobs_definitions();
        $sched = self::schedule();
        foreach ($defs as $id => &$def) {
            $def['frequency'] = isset($sched[$id]['frequency']) ? $sched[$id]['frequency'] : 'off';
            $def['time']      = isset($sched[$id]['time'])      ? $sched[$id]['time']      : $def['default_time'];
        }
        unset($def);
        return $defs;
    }

    
    public static function schedule() {
        $stored = get_option(self::SETTINGS_OPTION, array());
        return is_array($stored) ? $stored : array();
    }

    
    public static function grain() {
        $g = (string) get_option(self::GRAIN_OPTION, 'both');
        return in_array($g, array('variation', 'parent', 'both'), true) ? $g : 'both';
    }

    
    public static function reference_mode() {
        $m = (string) get_option(self::REFERENCE_OPTION, 'auto');
        return in_array($m, array('today', 'last_sales_day', 'auto'), true) ? $m : 'auto';
    }

    
    public static function staleness_days() {
        $d = (int) get_option(self::STALENESS_OPTION, 3);
        return ($d >= 1 && $d <= 90) ? $d : 3;
    }

    
    public static function resolve_asof() {
        global $wpdb;
        $today = current_time('Y-m-d');
        $mode  = self::reference_mode();
        if ($mode === 'today') { return $today; }

        $opa = $wpdb->prefix . 'aieo_order_products_analytics';
        if ((string) $wpdb->get_var("SHOW TABLES LIKE '{$opa}'") !== $opa) { return $today; }
        $last = $wpdb->get_var("SELECT MAX(DATE(date_created_gmt)) FROM `{$opa}`");
        if (empty($last)) { return $today; }

        if ($mode === 'last_sales_day') { return (string) $last; }

        
        $gap = (int) $wpdb->get_var($wpdb->prepare('SELECT DATEDIFF(%s, %s)', $today, $last));
        return ($gap > self::staleness_days()) ? (string) $last : $today;
    }

    public function register_hooks() {
        add_action('admin_init', array($this, 'maybe_install_sprocs'));
        add_action('plugins_loaded', array($this, 'maybe_schedule'), 27);
        add_action(self::CRON_HOOK, array($this, 'cron_run_job'), 10, 1);
        add_filter('cron_schedules', array($this, 'add_cron_schedules'));

        add_action('wp_ajax_aieo_analytics_save_schedule', array($this, 'ajax_save_schedule'));
        add_action('wp_ajax_aieo_analytics_run_now',       array($this, 'ajax_run_now'));
        add_action('wp_ajax_aieo_analytics_predictor_step', array($this, 'ajax_predictor_step'));
        add_action('wp_ajax_aieo_analytics_run_all',       array($this, 'ajax_run_all'));
        add_action('wp_ajax_aieo_analytics_save_pages',    array($this, 'ajax_save_pages'));
        add_action('wp_ajax_aieo_analytics_reinstall',     array($this, 'ajax_reinstall'));
    }

    

    public function maybe_install_sprocs() {
        if (get_option(self::SCHEMA_OPTION, '') === self::SCHEMA_VERSION) { return; }
        self::install_sprocs();
    }

    public static function install_sprocs() {
        self::load_sprocs_file();
        if (!function_exists('aieo_analytics_jobs_install_sprocs')) { return false; }
        $res = aieo_analytics_jobs_install_sprocs();
        if ($res === true) {
            update_option(self::SCHEMA_OPTION, self::SCHEMA_VERSION, false);
        }
        return $res;
    }

    
    public static function repoint_wpdatatables() {
        global $wpdb;
        $t = $wpdb->prefix . 'wpdatatables';
        if ((string) $wpdb->get_var("SHOW TABLES LIKE '{$t}'") !== $t) {
            return array('updated' => 0, 'note' => 'no wpdatatables table');
        }
        $p = $wpdb->prefix;
        
        
        $map = array(
            $p . 'pods_order_stats'                           => $p . 'aieo_order_stats',
            $p . 'pods_order_products_analytics'              => $p . 'aieo_order_products_analytics',
            $p . 'wiz_cummulative_order_stats'                => $p . 'aieo_cumulative_order_stats',
            $p . 'wiz_order_products_freq_single_item_orders' => $p . 'aieo_order_products_freq_single_item_orders',
            $p . 'wiz_order_products_freq_entry'              => $p . 'aieo_order_products_freq_entry',
            $p . 'wiz_order_products_freq_exit'               => $p . 'aieo_order_products_freq_exit',
            $p . 'wiz_order_products_single_orders'           => $p . 'aieo_order_products_single_orders',
            $p . 'wiz_order_products_entry'                   => $p . 'aieo_order_products_entry',
            $p . 'wiz_order_products_exit'                    => $p . 'aieo_order_products_exit',
            $p . 'order_invoice_match_report_current_month'   => $p . 'aieo_order_invoice_match_report_current_month',
            $p . 's1erp_store_only_products_table'            => $p . 'aieo_store_only_products',
            $p . 's1erp_reciprocal_ids_table'                 => $p . 'aieo_reciprocal_ids',
            
            
            
            $p . 'pods_product_vitals'                        => $p . 'aieo_core_product_vitals',
            $p . 'wiz_product_vitals'                         => $p . 'aieo_core_product_vitals',
            
            $p . 'wiz_order_products_analytics'               => $p . 'aieo_order_products_analytics',
            $p . 'wiz_order_stats'                            => $p . 'aieo_order_stats',
            
            $p . 'pods_store_sales'                           => $p . 'aieo_dmm_store_sales',
            $p . 'pods_preorder_vitals'                       => $p . 'aieo_dmm_preorder_vitals',
        );
        
        
        $inv_re = '/' . preg_quote($p . 'order_invoice_match_report', '/') . '(?![_a-zA-Z0-9])/';
        $inv_to = $p . 'aieo_order_invoice_match_report';

        
        
        
        
        
        $qe = static function ($name) use ($p) { return preg_quote($p . $name, '/'); };
        $col_remaps = array(
            
            '/(' . $qe('aieo_order_stats') . '\.)(?:`OrderTrafficSource`|OrderTrafficSource(?![A-Za-z0-9_]))(?!\s+AS\s+`?OrderTrafficSource`?)/i'
                => '${1}`ordersource` AS `OrderTrafficSource`',
            
            '/(' . $qe('aieo_dmm_preorder_vitals') . '\.)(?:`supplier_name`|supplier_name(?![A-Za-z0-9_]))(?!\s+AS\s+`?supplier_name`?)/i'
                => '${1}`supplier` AS `supplier_name`',
            
            
            '/(' . $qe('aieo_dmm_store_sales') . '\.)(?:`trend_of_eshop`|trend_of_eshop(?![A-Za-z0-9_]))(?!\s+AS\s+`?trend_of_eshop`?)/i'
                => '${1}`trend_of_eshop_soft1` AS `trend_of_eshop`',
            
            
            
            '/(' . preg_quote($p, '/') . 'store_board_[A-Za-z0-9_]+\.)(?:`supplier_name`|supplier_name(?![A-Za-z0-9_]))(?!\s+AS\s+`?supplier_name`?)/i'
                => '${1}`supplier` AS `supplier_name`',
        );

        $rows = $wpdb->get_results("SELECT id, content, mysql_table_name FROM `{$t}`", ARRAY_A);
        $updated = 0; $skipped = 0;
        foreach ((array) $rows as $r) {
            $c  = (string) $r['content'];
            $mt = (string) $r['mysql_table_name'];
            $oc = $c; $omt = $mt;

            
            
            
            
            if (strpos($c, $p . 'pods_supplier_preorders') !== false) {
                $c  = self::supplier_preorders_aieo_sql($p);
                $mt = '';
            } else {
                
                foreach ($map as $old => $new) {
                    $c  = str_replace($old, $new, $c);
                    $mt = str_replace($old, $new, $mt);
                }
                $c  = preg_replace($inv_re, $inv_to, $c);
                $mt = preg_replace($inv_re, $inv_to, $mt);

                
                foreach ($col_remaps as $pat => $rep) {
                    $c = preg_replace($pat, $rep, $c);
                }
                
                
                
                if (strpos($c, $p . 'aieo_dmm_preorder_vitals') !== false) {
                    $c = preg_replace('/(?<![A-Za-z0-9_.`])supplier_name(?![A-Za-z0-9_`])/i', '`supplier` AS `supplier_name`', $c);
                }
                
                
                if (strpos($c, $p . 'aieo_order_products_analytics') !== false) {
                    $c = preg_replace('/[A-Za-z_][A-Za-z0-9_]*\.`?sequence_flags`?(?![A-Za-z0-9_])/i', 'NULL AS `sequence_flags`', $c);
                }
            }

            if ($c !== $oc || $mt !== $omt) {
                $wpdb->update($t, array('content' => $c, 'mysql_table_name' => $mt), array('id' => (int) $r['id']), array('%s', '%s'), array('%d'));
                $updated++;
            } elseif (preg_match('/' . preg_quote($p, '/') . '(pods_|wiz_)/i', $c)) {
                
                
                
                $skipped++;
            }
        }
        return array('updated' => $updated, 'skipped_no_target' => $skipped);
    }

    
    private static function supplier_preorders_aieo_sql($p) {
        return
            "SELECT\n" .
            "       sp.`id` AS `id`,\n" .
            "       concat('<a href=\"/?p=', COALESCE(pv.`parent_id`, sp.`product_id`), '\" target=\"_blank\">', COALESCE(NULLIF(pv.`product_name`, ''), pv.`name`), '</a>') AS `name`,\n" .
            "       sp.`created_at` AS `created`,\n" .
            "       sp.`created_at` AS `modified`,\n" .
            "       COALESCE((SELECT tm.`meta_value` FROM {$p}termmeta tm WHERE tm.`term_id` = sp.`supplier_term_id` AND tm.`meta_key` = 'aieo_supplier_erp_id' LIMIT 1), sp.`supplier_term_id`) AS `supplier_id`,\n" .
            "       t.`name` AS `supplier`,\n" .
            "       sp.`branch_id` AS `shop_id`,\n" .
            "       br.`label` AS `shop_name`,\n" .
            "       pv.`sku` AS `sku`,\n" .
            "       pv.`gtin` AS `barcode`,\n" .
            "       sp.`requested_qty` AS `quantity_requested`,\n" .
            "       sp.`product_id` AS `product_id`,\n" .
            "       sp.`prior_existing` AS `quantity_before_order`,\n" .
            "       sp.`status` AS `status`,\n" .
            "       NULL AS `origin_type`,\n" .
            "       sp.`scanned_user_id` AS `origin_user`,\n" .
            "       pv.`image_url` AS `image_url`,\n" .
            "       NULL AS `parent_image_url`\n" .
            "FROM {$p}aieo_dmm_supplier_preorders sp\n" .
            "LEFT JOIN {$p}aieo_core_product_vitals pv ON pv.`product_id` = sp.`product_id`\n" .
            "LEFT JOIN {$p}aieo_dmm_branches br ON br.`id` = sp.`branch_id`\n" .
            "LEFT JOIN {$p}terms t ON t.`term_id` = sp.`supplier_term_id`";
    }

    

    
    public function add_cron_schedules($schedules) {
        if (!isset($schedules['weekly'])) {
            $schedules['weekly'] = array('interval' => WEEK_IN_SECONDS, 'display' => __('Once Weekly', 'ai-eshop-optimizer'));
        }
        if (!isset($schedules['aieo_monthly'])) {
            $schedules['aieo_monthly'] = array('interval' => 30 * DAY_IN_SECONDS, 'display' => __('Once Monthly (~30 days)', 'ai-eshop-optimizer'));
        }
        return $schedules;
    }

    public function maybe_schedule() {
        foreach (self::jobs() as $id => $def) {
            $args = array($id);
            $next  = (int) wp_next_scheduled(self::CRON_HOOK, $args);
            $sched = wp_get_schedule(self::CRON_HOOK, $args);

            if ($def['frequency'] === 'off') {
                if ($next > 0) { wp_unschedule_event($next, self::CRON_HOOK, $args); }
                continue;
            }
            $recurrence_map = array('hourly' => 'hourly', 'daily' => 'daily', 'weekly' => 'weekly', 'monthly' => 'aieo_monthly');
            $recurrence = isset($recurrence_map[$def['frequency']]) ? $recurrence_map[$def['frequency']] : 'daily';
            if ($sched === $recurrence && $next > 0) { continue; }
            if ($next > 0) { wp_unschedule_event($next, self::CRON_HOOK, $args); }
            $first = ($def['frequency'] === 'hourly')
                ? (time() + MINUTE_IN_SECONDS)
                : self::next_athens_at($def['time']);
            wp_schedule_event($first, $recurrence, self::CRON_HOOK, $args);
        }
    }

    private static function next_athens_at($hhmm) {
        if (!preg_match('/^\d{2}:\d{2}$/', (string) $hhmm)) { $hhmm = '04:00'; }
        try {
            $tz   = new \DateTimeZone('Europe/Athens');
            $next = new \DateTime('today ' . $hhmm . ':00', $tz);
            if ($next->getTimestamp() <= time()) { $next->modify('+1 day'); }
            return (int) $next->getTimestamp();
        } catch (\Throwable $e) {
            return time() + DAY_IN_SECONDS;
        }
    }

    public function cron_run_job($job_id) {
        $this->run_job((string) $job_id, 'cron');
    }

    

    
    public function run_job($job_id, $source = 'manual') {
        global $wpdb;
        $jobs = self::jobs();
        if (!isset($jobs[$job_id])) {
            return array('ok' => false, 'error' => 'unknown_job', 'job' => $job_id);
        }
        $def  = $jobs[$job_id];
        $proc = $wpdb->prefix . $def['proc'];

        $lock = 'aieo_analytics_job_' . $job_id;
        if (get_transient($lock)) {
            return array('ok' => false, 'skipped' => true, 'error' => 'already_running', 'job' => $job_id);
        }
        set_transient($lock, time(), 90 * MINUTE_IN_SECONDS);
        register_shutdown_function(function () use ($lock) {
            $e = error_get_last();
            $fatal = array(E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR, E_USER_ERROR);
            if ($e && in_array($e['type'], $fatal, true)) { delete_transient($lock); }
        });
        @set_time_limit(0); 

        
        $exists = (string) $wpdb->get_var($wpdb->prepare(
            "SELECT routine_name FROM information_schema.routines
              WHERE routine_schema = DATABASE() AND routine_type = 'PROCEDURE' AND routine_name = %s",
            $proc
        )) === $proc;
        if (!$exists) { self::install_sprocs(); }

        
        
        
        
        
        if ($job_id === 'sales_predictor') {
            $wpdb->query($wpdb->prepare('SET @aieo_predictor_grain = %s', self::grain()));
            $wpdb->query($wpdb->prepare('SET @aieo_predictor_asof = %s', self::resolve_asof()));
        }

        
        
        if (($job_id === 'stock_recovery' || $job_id === 'stock_recovery_outcomes')
            && class_exists('AIEO_DMM_Physical_Recovery')) {
            foreach (AIEO_DMM_Physical_Recovery::session_vars() as $var => $val) {
                $wpdb->query($wpdb->prepare("SET {$var} = %s", $val)); 
            }
        }

        $t0 = microtime(true);
        if ($job_id === 'sales_predictor') {
            
            
            
            
            $err = self::run_sales_predictor_staged();
        } else {
            $wpdb->query("CALL `{$proc}`()");
            $err = $wpdb->last_error;
        }

        
        
        if (!$err && in_array($job_id, array('supplier_sku_sync', 'preorder_vitals'), true)) {
            $sb = AIEO_DIR . 'includes/dmm/services/class-aieo-dmm-store-builder.php';
            if (is_readable($sb)) { require_once $sb; }
            if (class_exists('AIEO_DMM_Store_Builder')) {
                if ($job_id === 'supplier_sku_sync') { AIEO_DMM_Store_Builder::sync_supplier_terms(); }
                else { AIEO_DMM_Store_Builder::upsert_boards(); }
                $err = $wpdb->last_error;
            }
        }

        
        
        
        if (!$err && $job_id === 'stock_recovery'
            && class_exists('AIEO_DMM_Physical_Recovery')
            && AIEO_DMM_Physical_Recovery::auto_apply_enabled()) {
            AIEO_DMM_Physical_Recovery::run_auto_apply();
            $err = $wpdb->last_error;
        }
        $dur = round(microtime(true) - $t0, 1);

        $rows = null;
        if (!$err && !empty($def['table'])) {
            $tbl  = str_replace('wp_', $wpdb->prefix, $def['table']);
            $rows = (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$tbl}`");
        }

        $entry = array(
            'finished_at' => current_time('mysql', true),
            'source'      => $source,
            'duration'    => $dur,
            'rows'        => $rows,
            'ok'          => empty($err),
            'error'       => $err ? (string) $err : '',
        );
        $all = get_option(self::LAST_RUN_OPTION, array());
        if (!is_array($all)) { $all = array(); }
        $all[$job_id] = $entry;
        update_option(self::LAST_RUN_OPTION, $all, false);

        if ($err && function_exists('aieo_debug_log')) {
            aieo_debug_log("AIEO analytics job {$job_id} ({$source}) failed: {$err}");
        }
        delete_transient($lock);
        $entry['job'] = $job_id;
        return $entry;
    }

    
    private static function run_sales_predictor_staged() {
        global $wpdb;
        $p = $wpdb->prefix;
        
        $have = (string) $wpdb->get_var($wpdb->prepare(
            "SELECT routine_name FROM information_schema.routines
              WHERE routine_schema = DATABASE() AND routine_name = %s",
            $p . 'AIEO_SP_Prepare'
        )) === $p . 'AIEO_SP_Prepare';
        if (!$have) { self::install_sprocs(); }

        $wpdb->query("CALL `{$p}AIEO_SP_Prepare`()");
        if ($wpdb->last_error) { return (string) $wpdb->last_error; }
        $tb    = (int) $wpdb->get_var("SELECT total_batches FROM `{$p}aieo_sp_run` WHERE id = 1");
        $guard = $tb + 50;
        for ($i = 0; $i <= $guard; $i++) {
            $cur = (int) $wpdb->get_var("SELECT next_batch FROM `{$p}aieo_sp_run` WHERE id = 1");
            if ($cur >= $tb) { break; }
            $wpdb->query("CALL `{$p}AIEO_SP_Batch`()");
            if ($wpdb->last_error) { return (string) $wpdb->last_error; }
        }
        $wpdb->query("CALL `{$p}AIEO_SP_Finalize`()");
        return (string) $wpdb->last_error;
    }

    
    public function ajax_predictor_step() {
        AIEO_DMM::verify_ajax_request();
        global $wpdb;
        $phase = isset($_POST['phase']) ? sanitize_key((string) wp_unslash($_POST['phase'])) : '';
        $p     = $wpdb->prefix;
        $lock  = 'aieo_analytics_job_sales_predictor';
        @set_time_limit(0); 

        if ($phase === 'prepare') {
            if (get_transient($lock)) {
                wp_send_json_error(array('message' => __('The predictor is already running.', 'ai-eshop-optimizer')));
            }
            set_transient($lock, time(), 90 * MINUTE_IN_SECONDS);
            set_transient('aieo_sp_started', microtime(true), 90 * MINUTE_IN_SECONDS);

            
            $have = (string) $wpdb->get_var($wpdb->prepare(
                "SELECT routine_name FROM information_schema.routines
                  WHERE routine_schema = DATABASE() AND routine_type = 'PROCEDURE' AND routine_name = %s",
                $p . 'AIEO_SP_Prepare'
            )) === $p . 'AIEO_SP_Prepare';
            if (!$have) { self::install_sprocs(); }

            
            $wpdb->query($wpdb->prepare('SET @aieo_predictor_grain = %s', self::grain()));
            $wpdb->query($wpdb->prepare('SET @aieo_predictor_asof = %s', self::resolve_asof()));
            $wpdb->query("CALL `{$p}AIEO_SP_Prepare`()");
            if ($wpdb->last_error) { delete_transient($lock); wp_send_json_error(array('message' => $wpdb->last_error)); }

            $row = $wpdb->get_row("SELECT total_batches, total_products FROM `{$p}aieo_sp_run` WHERE id = 1", ARRAY_A);
            wp_send_json_success(array(
                'phase'          => 'prepare',
                'total_batches'  => (int) ($row['total_batches'] ?? 0),
                'total_products' => (int) ($row['total_products'] ?? 0),
                'next_batch'     => 0,
            ));
        }

        if ($phase === 'batch') {
            if (!get_transient($lock)) { set_transient($lock, time(), 90 * MINUTE_IN_SECONDS); }
            $wpdb->query("CALL `{$p}AIEO_SP_Batch`()");
            if ($wpdb->last_error) { delete_transient($lock); wp_send_json_error(array('message' => $wpdb->last_error)); }
            $row = $wpdb->get_row("SELECT next_batch, total_batches FROM `{$p}aieo_sp_run` WHERE id = 1", ARRAY_A);
            $nb  = (int) ($row['next_batch'] ?? 0);
            $tb  = (int) ($row['total_batches'] ?? 0);
            wp_send_json_success(array('phase' => 'batch', 'next_batch' => $nb, 'total_batches' => $tb, 'done' => ($nb >= $tb)));
        }

        if ($phase === 'finalize') {
            $wpdb->query("CALL `{$p}AIEO_SP_Finalize`()");
            $err  = $wpdb->last_error;
            $asof = (string) $wpdb->get_var("SELECT asof FROM `{$p}aieo_sp_run` WHERE id = 1");
            $rows = $err ? null : (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM `{$p}aieo_sales_predictor` WHERE prediction_date = %s", $asof
            ));
            $started = (float) get_transient('aieo_sp_started');
            $dur     = $started ? round(microtime(true) - $started, 1) : null;

            $entry = array(
                'finished_at' => current_time('mysql', true),
                'source'      => 'manual',
                'duration'    => $dur,
                'rows'        => $rows,
                'ok'          => empty($err),
                'error'       => $err ? (string) $err : '',
            );
            $all = get_option(self::LAST_RUN_OPTION, array());
            if (!is_array($all)) { $all = array(); }
            $all['sales_predictor'] = $entry;
            update_option(self::LAST_RUN_OPTION, $all, false);

            delete_transient($lock);
            delete_transient('aieo_sp_started');
            if ($err) { wp_send_json_error(array('message' => $err)); }
            wp_send_json_success(array(
                'phase'    => 'finalize',
                'rows'     => $rows,
                'duration' => $dur,
                
                'message'  => sprintf(__('Done — %1$s rows in %2$ss.', 'ai-eshop-optimizer'), number_format_i18n((int) $rows), $dur),
            ));
        }

        wp_send_json_error(array('message' => 'unknown phase'));
    }

    
    public function run_all($source = 'manual') {
        $log = array();
        foreach (array_keys(self::jobs()) as $id) {
            $log[$id] = $this->run_job($id, $source);
        }
        return $log;
    }

    

    public function ajax_save_schedule() {
        AIEO_DMM::verify_ajax_request();
        $jobs = self::jobs();
        $raw  = isset($_POST['schedule_json']) ? json_decode((string) wp_unslash($_POST['schedule_json']), true) : array();
        $out  = array();
        if (is_array($raw)) {
            foreach ($jobs as $id => $def) {
                $freq = isset($raw[$id]['frequency']) ? sanitize_key((string) $raw[$id]['frequency']) : 'off';
                $time = isset($raw[$id]['time']) ? sanitize_text_field((string) $raw[$id]['time']) : $def['default_time'];
                if (!in_array($freq, array('off', 'hourly', 'daily', 'weekly', 'monthly'), true)) { $freq = 'off'; }
                if (!preg_match('/^\d{2}:\d{2}$/', $time)) { $time = $def['default_time']; }
                $out[$id] = array('frequency' => $freq, 'time' => $time);
            }
        }
        update_option(self::SETTINGS_OPTION, $out, false);

        
        if (isset($_POST['predictor_grain'])) {
            $g = sanitize_key((string) wp_unslash($_POST['predictor_grain']));
            if (!in_array($g, array('variation', 'parent', 'both'), true)) { $g = 'both'; }
            update_option(self::GRAIN_OPTION, $g, false);
        }
        
        if (isset($_POST['predictor_reference'])) {
            $r = sanitize_key((string) wp_unslash($_POST['predictor_reference']));
            if (!in_array($r, array('today', 'last_sales_day', 'auto'), true)) { $r = 'auto'; }
            update_option(self::REFERENCE_OPTION, $r, false);
        }
        if (isset($_POST['predictor_staleness'])) {
            $s = (int) $_POST['predictor_staleness'];
            if ($s < 1)  { $s = 1; }
            if ($s > 90) { $s = 90; }
            update_option(self::STALENESS_OPTION, $s, false);
        }

        $this->maybe_schedule();
        wp_send_json_success(array('message' => __('Schedules saved.', 'ai-eshop-optimizer')));
    }

    public function ajax_run_now() {
        AIEO_DMM::verify_ajax_request();
        $job_id = isset($_POST['job']) ? sanitize_key((string) wp_unslash($_POST['job'])) : '';
        $entry  = $this->run_job($job_id, 'manual');
        wp_send_json_success(array(
            'entry'   => $entry,
            'message' => !empty($entry['ok'])
                
                ? sprintf(__('Done — %1$s rows in %2$ss.', 'ai-eshop-optimizer'), number_format_i18n((int) ($entry['rows'] ?? 0)), $entry['duration'] ?? '?')
                
                : sprintf(__('Failed: %s', 'ai-eshop-optimizer'), $entry['error'] ?? 'error'),
        ));
    }

    public function ajax_run_all() {
        AIEO_DMM::verify_ajax_request();
        $log  = $this->run_all('manual');
        $okc  = 0; $fail = 0;
        foreach ($log as $e) { if (!empty($e['ok'])) { $okc++; } else { $fail++; } }
        wp_send_json_success(array(
            'log'     => $log,
            
            'message' => sprintf(__('Ran all jobs — %1$d ok, %2$d failed.', 'ai-eshop-optimizer'), $okc, $fail),
        ));
    }

    public function ajax_save_pages() {
        AIEO_DMM::verify_ajax_request();
        $csv = isset($_POST['page_ids']) ? sanitize_text_field((string) wp_unslash($_POST['page_ids'])) : '';
        $ids = array();
        foreach (preg_split('/[\s,]+/', $csv) as $p) {
            $p = (int) $p;
            if ($p > 0) { $ids[] = $p; }
        }
        $ids = array_values(array_unique($ids));
        update_option(self::PAGES_OPTION, $ids, false);
        wp_send_json_success(array(
            'message'  => __('Enhancement pages saved.', 'ai-eshop-optimizer'),
            'page_ids' => implode(', ', $ids),
        ));
    }

    public function ajax_reinstall() {
        AIEO_DMM::verify_ajax_request();
        delete_option(self::SCHEMA_OPTION);
        $res = self::install_sprocs();
        wp_send_json_success(array(
            'message' => ($res === true)
                ? __('Stored procedures reinstalled.', 'ai-eshop-optimizer')
                : __('Reinstall reported issues — see debug log.', 'ai-eshop-optimizer'),
        ));
    }

    

    public function render_subtab() {
        $jobs  = self::jobs();
        $last  = (array) get_option(self::LAST_RUN_OPTION, array());
        $nonce = wp_create_nonce(AIEO_DMM::NONCE_ACTION);
        $ajax  = admin_url('admin-ajax.php');

        echo '<div class="aieo-dmm-section">';
        echo '<h2>' . esc_html__('Product/Orders analytics', 'ai-eshop-optimizer') . '</h2>';
        echo '<p class="description" style="max-width:880px;">'
            . esc_html__('Rebuilds the order/product analytics tables that the logistics report pages (wpDataTables) read from — per-order stats, cumulative customer stats, order-products analytics, behaviours, cart frequencies and Woo↔ERP invoice matching. Each job runs a server-side stored procedure; product data is sourced from the AIEO product vitals table. Set a schedule per job (Europe/Athens) or run on demand.', 'ai-eshop-optimizer')
            . '</p>';

        
        echo '<div class="aieo-dmm-card">';
        echo '<h3 style="margin-top:0;">' . esc_html__('Analytics jobs', 'ai-eshop-optimizer') . '</h3>';
        echo '<table class="widefat striped" id="aieo-analytics-jobs" style="font-size:13px;">';
        echo '<thead><tr>'
            . '<th>' . esc_html__('Job', 'ai-eshop-optimizer') . '</th>'
            . '<th>' . esc_html__('Output table', 'ai-eshop-optimizer') . '</th>'
            . '<th>' . esc_html__('Schedule (Athens)', 'ai-eshop-optimizer') . '</th>'
            . '<th>' . esc_html__('Last run', 'ai-eshop-optimizer') . '</th>'
            . '<th></th>'
            . '</tr></thead><tbody>';

        foreach ($jobs as $id => $def) {
            $li      = isset($last[$id]) && is_array($last[$id]) ? $last[$id] : array();
            $deps    = !empty($def['deps']) ? implode(', ', $def['deps']) : '';
            echo '<tr data-job="' . esc_attr($id) . '">';

            echo '<td><strong>' . esc_html($def['label']) . '</strong><br><code style="font-size:11px;color:#6b7280;">' . esc_html($id) . '</code>';
            if ($deps !== '') {
                echo '<br><span style="font-size:11px;color:#9ca3af;">' . esc_html__('runs after:', 'ai-eshop-optimizer') . ' ' . esc_html($deps) . '</span>';
            }
            echo '</td>';

            echo '<td><code style="font-size:11px;">' . esc_html($def['table']) . '</code></td>';

            
            echo '<td style="white-space:nowrap;">';
            echo '<select class="aieo-aj-freq" data-job="' . esc_attr($id) . '">';
            foreach (array('off' => __('Off', 'ai-eshop-optimizer'), 'hourly' => __('Hourly', 'ai-eshop-optimizer'), 'daily' => __('Daily', 'ai-eshop-optimizer'), 'weekly' => __('Weekly', 'ai-eshop-optimizer'), 'monthly' => __('Monthly', 'ai-eshop-optimizer')) as $v => $lbl) {
                echo '<option value="' . esc_attr($v) . '" ' . selected($def['frequency'], $v, false) . '>' . esc_html($lbl) . '</option>';
            }
            echo '</select> ';
            echo '<input type="time" class="aieo-aj-time" data-job="' . esc_attr($id) . '" value="' . esc_attr($def['time']) . '" style="width:96px;">';
            if ($id === 'sales_predictor') {
                $grain = self::grain();
                $ref   = self::reference_mode();
                $stale = self::staleness_days();
                
                
                
                echo '<div class="aieo-aj-predictor-opts" style="margin-top:8px;display:flex;flex-direction:column;gap:6px;white-space:normal;max-width:340px;">';

                echo '<div style="display:flex;align-items:center;gap:8px;">';
                echo '<span style="flex:0 0 52px;font-size:11px;color:#6b7280;">' . esc_html__('Grain:', 'ai-eshop-optimizer') . '</span>';
                echo '<select id="aieo-aj-grain" style="flex:1 1 auto;min-width:0;max-width:250px;">';
                foreach (array(
                    'both'      => __('Both (variation + parent)', 'ai-eshop-optimizer'),
                    'variation' => __('Per variation', 'ai-eshop-optimizer'),
                    'parent'    => __('Per parent product', 'ai-eshop-optimizer'),
                ) as $v => $lbl) {
                    echo '<option value="' . esc_attr($v) . '" ' . selected($grain, $v, false) . '>' . esc_html($lbl) . '</option>';
                }
                echo '</select>';
                echo '</div>';

                echo '<div style="display:flex;align-items:center;gap:8px;flex-wrap:wrap;">';
                echo '<span title="' . esc_attr__('Which day the demand windows treat as “now”. Use the last real sales day when the store goes quiet so velocity is not read as zero.', 'ai-eshop-optimizer') . '" style="flex:0 0 52px;font-size:11px;color:#6b7280;cursor:help;">' . esc_html__('Anchor:', 'ai-eshop-optimizer') . '</span>';
                echo '<select id="aieo-aj-reference" style="flex:1 1 auto;min-width:0;max-width:250px;">';
                foreach (array(
                    'auto'           => __('Auto (today, fall back when quiet)', 'ai-eshop-optimizer'),
                    'today'          => __('Always today', 'ai-eshop-optimizer'),
                    'last_sales_day' => __('Last day with sales', 'ai-eshop-optimizer'),
                ) as $v => $lbl) {
                    echo '<option value="' . esc_attr($v) . '" ' . selected($ref, $v, false) . '>' . esc_html($lbl) . '</option>';
                }
                echo '</select>';
                echo '<span id="aieo-aj-stale-wrap" style="' . ($ref === 'auto' ? 'display:inline-flex;' : 'display:none;') . 'align-items:center;gap:4px;font-size:11px;color:#6b7280;margin-left:60px;">' . esc_html__('quiet after', 'ai-eshop-optimizer') . ' <input type="number" id="aieo-aj-staleness" min="1" max="90" step="1" value="' . esc_attr($stale) . '" style="width:54px;"> ' . esc_html__('days', 'ai-eshop-optimizer') . '</span>';
                echo '</div>';

                echo '</div>';
            }
            echo '</td>';

            
            echo '<td style="font-size:12px;">';
            if (empty($li)) {
                echo '<span style="color:#9ca3af;">' . esc_html__('never', 'ai-eshop-optimizer') . '</span>';
            } else {
                $color = !empty($li['ok']) ? '#15803d' : '#b91c1c';
                echo '<span style="color:' . esc_attr($color) . ';font-weight:600;">' . esc_html(!empty($li['ok']) ? 'OK' : 'FAIL') . '</span> ';
                echo esc_html((isset($li['rows']) && $li['rows'] !== null ? number_format_i18n((int) $li['rows']) . ' rows · ' : '') . ($li['duration'] ?? '?') . 's');
                echo '<br><span style="color:#9ca3af;">' . esc_html(($li['finished_at'] ?? '') . ' · ' . ($li['source'] ?? '')) . '</span>';
                if (!empty($li['error'])) {
                    echo '<br><span style="color:#b91c1c;">' . esc_html(mb_substr((string) $li['error'], 0, 160)) . '</span>';
                }
            }
            echo '</td>';

            echo '<td><button type="button" class="button button-small aieo-aj-run" data-job="' . esc_attr($id) . '">' . esc_html__('Run now', 'ai-eshop-optimizer') . '</button></td>';
            echo '</tr>';
        }
        echo '</tbody></table>';

        echo '<p style="margin-top:12px;">';
        echo '<button type="button" class="button button-primary" id="aieo-aj-save">' . esc_html__('Save schedules', 'ai-eshop-optimizer') . '</button> ';
        echo '<button type="button" class="button" id="aieo-aj-runall" title="' . esc_attr__('Runs every job once, in dependency order.', 'ai-eshop-optimizer') . '">' . esc_html__('Run all (in order)', 'ai-eshop-optimizer') . '</button> ';
        echo '<button type="button" class="button" id="aieo-aj-reinstall" title="' . esc_attr__('Drop + recreate the stored procedures from the shipped SQL.', 'ai-eshop-optimizer') . '">' . esc_html__('Reinstall procedures', 'ai-eshop-optimizer') . '</button> ';
        echo '<span id="aieo-aj-status" style="margin-left:10px;color:#646970;font-size:13px;"></span>';
        echo '</p>';
        echo '<p class="description">' . esc_html__('Run-now / Run-all edit historical analytics tables and can take a few minutes (the cumulative-stats job is the slowest). Run off-peak.', 'ai-eshop-optimizer') . '</p>';
        echo '</div>';

        
        $pages = (array) get_option(self::PAGES_OPTION, array());
        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Report-page enhancements (wpDataTables)', 'ai-eshop-optimizer') . '</h3>';
        echo '<p class="description" style="max-width:820px;">' . esc_html__('Page IDs that get the glass-tooltip (hidden "logger" column on hover) + origin-user tagging treatment on their wpDataTables. Comma-separated WordPress page IDs.', 'ai-eshop-optimizer') . '</p>';
        echo '<p><input type="text" id="aieo-aj-pages" class="regular-text" style="width:480px;" value="' . esc_attr(implode(', ', array_map('intval', $pages))) . '" placeholder="204717, 205185, 205209 …"> ';
        echo '<button type="button" class="button" id="aieo-aj-pages-save">' . esc_html__('Save pages', 'ai-eshop-optimizer') . '</button> ';
        echo '<span id="aieo-aj-pages-status" style="margin-left:8px;color:#646970;font-size:12px;"></span></p>';
        echo '</div>';

        
        ?>
        <script>
        (function(){
            var ajax = <?php echo wp_json_encode($ajax); ?>;
            var nonce = <?php echo wp_json_encode($nonce); ?>;
            var status = document.getElementById('aieo-aj-status');
            function post(action, extra){
                var fd = new FormData();
                fd.set('action', action); fd.set('nonce', nonce);
                if (extra) { Object.keys(extra).forEach(function(k){ fd.set(k, extra[k]); }); }
                return fetch(ajax, {method:'POST', body:fd, credentials:'same-origin'}).then(function(r){return r.json();});
            }
            function collectSchedule(){
                var s = {};
                document.querySelectorAll('#aieo-analytics-jobs tr[data-job]').forEach(function(tr){
                    var job = tr.getAttribute('data-job');
                    var freq = tr.querySelector('.aieo-aj-freq').value;
                    var time = tr.querySelector('.aieo-aj-time').value;
                    s[job] = {frequency: freq, time: time};
                });
                return s;
            }
            var refSel = document.getElementById('aieo-aj-reference');
            var staleWrap = document.getElementById('aieo-aj-stale-wrap');
            if (refSel && staleWrap) {
                refSel.addEventListener('change', function(){
                    staleWrap.style.display = (refSel.value === 'auto') ? 'inline-flex' : 'none';
                });
            }
            var saveBtn = document.getElementById('aieo-aj-save');
            saveBtn && saveBtn.addEventListener('click', function(){
                status.style.color = '#646970'; status.textContent = '<?php echo esc_js(__('Saving…', 'ai-eshop-optimizer')); ?>';
                var grainEl = document.getElementById('aieo-aj-grain');
                var refEl   = document.getElementById('aieo-aj-reference');
                var staleEl = document.getElementById('aieo-aj-staleness');
                var extra = {schedule_json: JSON.stringify(collectSchedule())};
                if (grainEl) { extra.predictor_grain = grainEl.value; }
                if (refEl)   { extra.predictor_reference = refEl.value; }
                if (staleEl) { extra.predictor_staleness = staleEl.value; }
                post('aieo_analytics_save_schedule', extra).then(function(j){
                    if (j && j.success) { status.style.color='#15803d'; status.textContent=j.data.message; setTimeout(function(){location.reload();},800); }
                    else { status.style.color='#b91c1c'; status.textContent=(j&&j.data&&j.data.message)||'error'; }
                });
            });
            // The sales predictor is too slow for one synchronous request, so it
            // runs as a JS-driven batched sequence (prepare → N batches → finalize)
            // against the staged procs. Every other job stays a single Run-now call.
            function runPredictorStaged(btn){
                function fail(e){ btn.disabled=false; status.style.color='#b91c1c'; status.textContent='sales_predictor: '+(typeof e==='string'?e:'<?php echo esc_js(__('network error', 'ai-eshop-optimizer')); ?>'); }
                btn.disabled = true; status.style.color='#646970';
                status.textContent='sales_predictor: <?php echo esc_js(__('preparing…', 'ai-eshop-optimizer')); ?>';
                post('aieo_analytics_predictor_step', {phase:'prepare'}).then(function(j){
                    if (!j || !j.success) { fail((j&&j.data&&j.data.message)||'prepare failed'); return; }
                    var total = j.data.total_batches||0;
                    function doFinalize(){
                        status.textContent='sales_predictor: <?php echo esc_js(__('finalizing…', 'ai-eshop-optimizer')); ?>';
                        post('aieo_analytics_predictor_step', {phase:'finalize'}).then(function(f){
                            btn.disabled=false;
                            if (f && f.success) { status.style.color='#15803d'; status.textContent='sales_predictor: '+f.data.message; setTimeout(function(){location.reload();},1200); }
                            else { fail((f&&f.data&&f.data.message)||'finalize failed'); }
                        }).catch(function(){ fail('network error'); });
                    }
                    function doBatch(){
                        post('aieo_analytics_predictor_step', {phase:'batch'}).then(function(b){
                            if (!b || !b.success) { fail((b&&b.data&&b.data.message)||'batch failed'); return; }
                            var nb=b.data.next_batch||0, tb=b.data.total_batches||total;
                            status.textContent='sales_predictor: <?php echo esc_js(__('batch', 'ai-eshop-optimizer')); ?> '+Math.min(nb,tb)+'/'+tb+'…';
                            if (b.data.done) { doFinalize(); } else { doBatch(); }
                        }).catch(function(){ fail('network error'); });
                    }
                    if (total <= 0) { doFinalize(); } else { doBatch(); }
                }).catch(function(){ fail('network error'); });
            }
            document.querySelectorAll('.aieo-aj-run').forEach(function(btn){
                btn.addEventListener('click', function(){
                    var job = btn.getAttribute('data-job');
                    if (job === 'sales_predictor') { runPredictorStaged(btn); return; }
                    btn.disabled = true; status.style.color='#646970'; status.textContent='<?php echo esc_js(__('Running', 'ai-eshop-optimizer')); ?> '+job+'…';
                    post('aieo_analytics_run_now', {job: job}).then(function(j){
                        btn.disabled = false;
                        if (j && j.success) { status.style.color='#15803d'; status.textContent=job+': '+j.data.message; setTimeout(function(){location.reload();},1000); }
                        else { status.style.color='#b91c1c'; status.textContent=(j&&j.data&&j.data.message)||'error'; }
                    }).catch(function(){ btn.disabled=false; status.style.color='#b91c1c'; status.textContent='network error'; });
                });
            });
            var runAll = document.getElementById('aieo-aj-runall');
            runAll && runAll.addEventListener('click', function(){
                if (!confirm('<?php echo esc_js(__('Run every analytics job now, in order? This can take several minutes.', 'ai-eshop-optimizer')); ?>')) return;
                runAll.disabled = true; status.style.color='#646970'; status.textContent='<?php echo esc_js(__('Running all jobs…', 'ai-eshop-optimizer')); ?>';
                post('aieo_analytics_run_all', {}).then(function(j){
                    runAll.disabled = false;
                    if (j && j.success) { status.style.color='#15803d'; status.textContent=j.data.message; setTimeout(function(){location.reload();},1200); }
                    else { status.style.color='#b91c1c'; status.textContent=(j&&j.data&&j.data.message)||'error'; }
                }).catch(function(){ runAll.disabled=false; status.style.color='#b91c1c'; status.textContent='network error'; });
            });
            var reinstall = document.getElementById('aieo-aj-reinstall');
            reinstall && reinstall.addEventListener('click', function(){
                reinstall.disabled = true; status.style.color='#646970'; status.textContent='…';
                post('aieo_analytics_reinstall', {}).then(function(j){
                    reinstall.disabled = false;
                    status.style.color = (j&&j.success)?'#15803d':'#b91c1c';
                    status.textContent = (j&&j.data&&j.data.message)||'error';
                });
            });
            var pagesSave = document.getElementById('aieo-aj-pages-save');
            var pagesStatus = document.getElementById('aieo-aj-pages-status');
            pagesSave && pagesSave.addEventListener('click', function(){
                pagesStatus.style.color='#646970'; pagesStatus.textContent='<?php echo esc_js(__('Saving…', 'ai-eshop-optimizer')); ?>';
                post('aieo_analytics_save_pages', {page_ids: document.getElementById('aieo-aj-pages').value}).then(function(j){
                    if (j && j.success) { pagesStatus.style.color='#15803d'; pagesStatus.textContent=j.data.message; }
                    else { pagesStatus.style.color='#b91c1c'; pagesStatus.textContent=(j&&j.data&&j.data.message)||'error'; }
                });
            });
        })();
        </script>
        <?php
        echo '</div>'; 
    }
}
