<?php

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

class AIEO_DMM_Data_Prep_Scheduler extends AIEO_DMM_Module_Base {

    const SETTINGS_OPTION = 'aieo_dmm_data_prep_schedule';
    const LAST_RUN_OPTION = 'aieo_dmm_data_prep_last_run';
    const CRON_HOOK       = 'aieo_dmm_data_prep_run';

    public function get_id()       { return 'data_prep_scheduler'; }
    public function get_label()    { return __('Data prep schedule', 'ai-eshop-optimizer'); }
    public function get_priority() { return 76; } 
    public function get_group()    { return 'import_export'; }

    public function register_hooks() {
        add_action('plugins_loaded', array($this, 'maybe_schedule'), 26);
        add_action(self::CRON_HOOK, array($this, 'cron_run_pipeline'));
        add_action('wp_ajax_aieo_dmm_data_prep_save', array($this, 'ajax_save'));
        add_action('wp_ajax_aieo_dmm_data_prep_run_now', array($this, 'ajax_run_now'));
        add_action('wp_ajax_aieo_dmm_data_prep_full_substrate', array($this, 'ajax_full_substrate'));
    }

    
    public static function defaults() {
        return array(
            'frequency' => 'daily',     
            'time'      => '03:30',     
        );
    }

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

    
    public function maybe_schedule() {
        $cfg = self::settings();
        $next  = (int) wp_next_scheduled(self::CRON_HOOK);
        $sched = wp_get_schedule(self::CRON_HOOK);

        if ($cfg['frequency'] === 'off') {
            if ($next > 0) { wp_clear_scheduled_hook(self::CRON_HOOK); }
            return;
        }
        $desired_recurrence = $cfg['frequency'] === 'hourly' ? 'hourly' : 'daily';
        if ($sched === $desired_recurrence && $next > 0) { return; } 

        wp_clear_scheduled_hook(self::CRON_HOOK);
        $first_ts = $cfg['frequency'] === 'hourly'
            ? (time() + MINUTE_IN_SECONDS)
            : $this->next_athens_at($cfg['time']);
        wp_schedule_event($first_ts, $desired_recurrence, self::CRON_HOOK);
    }

    private function next_athens_at($hhmm) {
        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_pipeline() {
        $this->run_pipeline('cron');
    }

    
    public function run_pipeline($source = 'manual') {
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        $lock_key = 'aieo_dmm_data_prep_running';
        if (get_transient($lock_key)) {
            return array(
                'source'     => $source,
                'started_at' => current_time('mysql', true),
                'stages'     => array(),
                'ok'         => false,
                'error'      => __('Data prep pipeline already running in another process — skipped concurrent invocation.', 'ai-eshop-optimizer'),
                'skipped'    => true,
            );
        }
        set_transient($lock_key, time(), 90 * MINUTE_IN_SECONDS);

        
        
        
        
        register_shutdown_function(function () use ($lock_key) {
            $err = error_get_last();
            $fatal_types = array(E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR, E_USER_ERROR);
            if ($err && in_array($err['type'], $fatal_types, true)) {
                delete_transient($lock_key);
            }
        });

        global $wpdb;
        @set_time_limit(0); 
        $started = microtime(true);
        $log = array(
            'source' => $source,
            'started_at' => current_time('mysql', true),
            'stages' => array(),
            'ok' => true,
            'error' => '',
        );
        $prefix = $wpdb->prefix;
        $stages = array(
            'category_hierarchy' => array(
                'sproc'      => $prefix . 'AIEO_InsertProductCategoryHierarchy',
                'count_sql'  => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_category_hierarchy",
                'notes_sql'  => "SELECT CONCAT(
                    COUNT(DISTINCT top_category_id), ' menu / ',
                    COUNT(DISTINCT second_level_category_id), ' head / ',
                    COUNT(DISTINCT third_level_category_id), ' leaf categories'
                  ) FROM {$wpdb->prefix}aieo_core_product_category_hierarchy",
            ),
            
            
            
            
            
            
            
            
            
            
            
            'product_attributes' => array(
                'callback'  => array($this, 'run_stage_product_attributes'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_attributes WHERE brand_id > 0",
                'notes_sql' => "SELECT CONCAT(
                    SUM(brand_id > 0), ' / ', COUNT(*), ' with brand · ',
                    COUNT(DISTINCT CASE WHEN brand_id > 0 THEN brand_id END), ' distinct brands'
                  ) FROM {$wpdb->prefix}aieo_core_product_attributes",
            ),
            'product_vitals' => array(
                'sproc'      => $prefix . 'AIEO_InsertProductVitals',
                'count_sql'  => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_vitals WHERE parent_id = product_id",
                'notes_sql'  => "SELECT CONCAT(
                    SUM(variation_count = 0 OR variation_count IS NULL), ' simple + ',
                    SUM(variation_count > 0), ' variable parents'
                  ) FROM {$wpdb->prefix}aieo_core_product_vitals WHERE parent_id = product_id",
            ),
            'variation_vitals' => array(
                'sproc'      => $prefix . 'AIEO_InsertVariationVitals',
                'count_sql'  => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_variation_vitals",
                'notes_sql'  => "SELECT CONCAT(
                    COUNT(*), ' variations · avg ',
                    ROUND(COUNT(*) / NULLIF(COUNT(DISTINCT parent_id), 0), 1), ' per parent'
                  ) FROM {$wpdb->prefix}aieo_core_variation_vitals",
            ),
            'combine_variations' => array(
                'sproc'      => $prefix . 'AIEO_CombineVariationVitalsInProductVitals',
                'count_sql'  => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_vitals",
                'notes_sql'  => "SELECT CONCAT(
                    SUM(parent_id = product_id), ' parent + ',
                    SUM(parent_id <> product_id), ' variation rows'
                  ) FROM {$wpdb->prefix}aieo_core_product_vitals",
            ),
            
            
            
            
            
            
            
            
            
            'propagate_brand' => array(
                'callback'  => array($this, 'run_stage_propagate_brand'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_vitals WHERE product_id <> parent_id AND brand IS NOT NULL AND brand <> '' AND brand <> 'No Brand'",
                'notes_sql' => "SELECT CONCAT(
                    SUM(product_id <> parent_id AND brand IS NOT NULL AND brand <> '' AND brand <> 'No Brand'),
                    ' / ',
                    SUM(product_id <> parent_id),
                    ' variations now branded'
                  ) FROM {$wpdb->prefix}aieo_core_product_vitals",
            ),
            
            
            
            
            
            
            'needs_population' => array(
                'callback'  => array($this, 'run_stage_needs_population'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_vitals WHERE primary_need_id IS NOT NULL",
                'notes_sql' => "SELECT CONCAT(
                    SUM(primary_need_id IS NOT NULL), ' / ', COUNT(*), ' with primary need · ',
                    COUNT(DISTINCT primary_need_id), ' distinct needs'
                  ) FROM {$wpdb->prefix}aieo_core_product_vitals",
            ),
            
            
            
            
            
            
            'variation_swatches' => array(
                'callback'  => array($this, 'run_stage_variation_swatches'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_vitals WHERE variation_swatch IS NOT NULL AND variation_swatch <> ''",
                'notes_sql' => "SELECT CONCAT(
                    SUM(variation_swatch IS NOT NULL AND variation_swatch <> ''),
                    ' / ',
                    SUM(product_id <> parent_id),
                    ' variations have a swatch image'
                  ) FROM {$wpdb->prefix}aieo_core_product_vitals",
            ),
            
            
            
            
            
            
            
            
            
            
            
            'sellable_status_flag' => array(
                'callback'  => array($this, 'run_stage_sellable_status_flag'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_product_vitals WHERE status = 1",
                'notes_sql' => "SELECT CONCAT(
                    SUM(status = 1), ' sellable · ',
                    SUM(status = 0 AND variation_count > 0), ' variable parents hidden'
                  ) FROM {$wpdb->prefix}aieo_core_product_vitals",
            ),
            
            
            
            
            
            
            
            
            
            
            
            
            'brand_vitals' => array(
                'callback'  => array($this, 'run_stage_brand_vitals'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_core_brand_vitals",
                'notes_sql' => "SELECT CONCAT(
                    SUM(product_count > 0), ' brands w/ products · top: ',
                    COALESCE((SELECT CONCAT(brand_name, ' (', product_count, ')')
                                FROM {$wpdb->prefix}aieo_core_brand_vitals
                               WHERE product_count > 0
                            ORDER BY product_count DESC LIMIT 1), '—')
                  ) FROM {$wpdb->prefix}aieo_core_brand_vitals",
            ),
            
            
            
            
            
            'effective_prices' => array(
                'callback'  => array($this, 'run_stage_effective_prices'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_effective_prices",
                'notes_sql' => "SELECT CONCAT(
                    SUM(was_on_sale = 1), ' on sale · ',
                    SUM(role_price_applied = 1), ' role-priced · ',
                    SUM(monthly_offer_active = 1), ' monthly offer'
                  ) FROM {$wpdb->prefix}aieo_effective_prices",
            ),
            'effective_eligibility' => array(
                'callback'  => array($this, 'run_stage_effective_eligibility'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_effective_eligibility",
                'notes_sql' => "SELECT CONCAT(
                    SUM(eligible = 1), ' eligible across ',
                    COUNT(DISTINCT surface), ' surfaces'
                  ) FROM {$wpdb->prefix}aieo_effective_eligibility",
            ),
            
            
            
            
            
            
            
            'seo_head_cache' => array(
                'callback'  => array($this, 'run_stage_seo_head_cache'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_seo_head_cache",
                'notes_sql' => "SELECT CONCAT(COUNT(*), ' cached heads') FROM {$wpdb->prefix}aieo_seo_head_cache",
            ),
            
            
            
            
            'seo_sitemap' => array(
                'callback'  => array($this, 'run_stage_seo_sitemap'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_seo_posts WHERE canonical_url <> '' AND (robots_noindex = 0 OR robots_noindex IS NULL)",
                'notes_sql' => "SELECT CONCAT(COUNT(*), ' sitemap URLs') FROM {$wpdb->prefix}aieo_seo_posts WHERE canonical_url <> '' AND (robots_noindex = 0 OR robots_noindex IS NULL)",
            ),
            
            
            
            
            'seo_scores' => array(
                'callback'  => array($this, 'run_stage_seo_scores'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_seo_scores",
                'notes_sql' => "SELECT CONCAT(COUNT(*), ' scored · avg ', COALESCE(ROUND(AVG(score)),0), '/100 · ', COALESCE(SUM(fix_count),0), ' fixes') FROM {$wpdb->prefix}aieo_seo_scores",
            ),
            
            
            
            'seo_pillars' => array(
                'callback'  => array($this, 'run_stage_seo_pillars'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_seo_scores WHERE is_pillar = 1",
                'notes_sql' => "SELECT CONCAT(COUNT(*), ' pillar products') FROM {$wpdb->prefix}aieo_seo_scores WHERE is_pillar = 1",
            ),
            
            
            
            
            'seo_image_alt' => array(
                'callback'  => array($this, 'run_stage_seo_image_alt'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = '_wp_attachment_image_alt' AND meta_value <> ''",
                'notes_sql' => "SELECT CONCAT(COUNT(*), ' image alts set') FROM {$wpdb->postmeta} WHERE meta_key = '_wp_attachment_image_alt' AND meta_value <> ''",
            ),
            
            
            
            
            'seo_redirect_map' => array(
                'callback'  => array($this, 'run_stage_seo_redirect_map'),
                'count_sql' => "SELECT COUNT(*) FROM {$wpdb->prefix}aieo_seo_redirects WHERE enabled = 1",
                'notes_sql' => "SELECT CONCAT(COUNT(*), ' edge redirects') FROM {$wpdb->prefix}aieo_seo_redirects WHERE enabled = 1",
            ),
            
            
            
            
            
        );

        foreach ($stages as $name => $stage) {
            $t0 = microtime(true);

            if (isset($stage['callback']) && is_callable($stage['callback'])) {
                try {
                    call_user_func($stage['callback']);
                } catch (\Throwable $e) {
                    $log['ok'] = false;
                    $log['error'] = $name . ': ' . $e->getMessage();
                    $log['stages'][$name] = array('error' => $e->getMessage(), 'duration' => round(microtime(true) - $t0, 2));
                    break;
                }
                $count = (int) $wpdb->get_var($stage['count_sql']);
                $notes = !empty($stage['notes_sql'])
                    ? (string) $wpdb->get_var($stage['notes_sql'])
                    : '';
                $log['stages'][$name] = array(
                    'rows'     => $count,
                    'duration' => round(microtime(true) - $t0, 2),
                    'notes'    => $notes,
                );
                continue;
            }

            
            $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",
                $stage['sproc']
            )) === $stage['sproc'];
            if (!$exists) {
                $log['stages'][$name] = array('skipped' => 'sproc_missing', 'duration' => 0);
                continue;
            }
            $wpdb->query("CALL `{$stage['sproc']}`()");
            if ($wpdb->last_error) {
                $log['ok'] = false;
                $log['error'] = $name . ': ' . $wpdb->last_error;
                $log['stages'][$name] = array('error' => $wpdb->last_error, 'duration' => round(microtime(true) - $t0, 2));
                break;
            }
            $count = (int) $wpdb->get_var($stage['count_sql']);
            $notes = !empty($stage['notes_sql'])
                ? (string) $wpdb->get_var($stage['notes_sql'])
                : '';
            $log['stages'][$name] = array(
                'rows'     => $count,
                'duration' => round(microtime(true) - $t0, 2),
                'notes'    => $notes,
            );
        }
        $log['total_duration'] = round(microtime(true) - $started, 2);
        $log['finished_at']    = current_time('mysql', true);
        update_option(self::LAST_RUN_OPTION, $log, false);
        if (function_exists('aieo_debug_log')) {
            aieo_debug_log('AIEO_DMM_Data_Prep_Scheduler ' . $source . ' run: ' . wp_json_encode($log));
        }
        delete_transient($lock_key);
        return $log;
    }

    
    public function run_stage_brand_vitals() {
        if (!class_exists('AIEO_Core_Brand_Vitals')) { return; }
        $bv = AIEO_Core_Brand_Vitals::instance();
        $bv->drain_dirty();
        $bv->refresh();
    }

    
    public function run_stage_seo_head_cache() {
        if (!class_exists('AIEO_SEO_Head_Builder') || !class_exists('AIEO_SEO_Store') || !AIEO_SEO_Store::is_active_provider()) {
            return;
        }
        
        
        
        
        AIEO_SEO_Head_Builder::drain_dirty(1000);
        AIEO_SEO_Head_Builder::run_backfill(2000);
    }

    
    public function run_stage_seo_sitemap() {
        if (!class_exists('AIEO_SEO_Sitemap')) { return; }
        AIEO_SEO_Sitemap::build();
    }

    
    public function run_stage_seo_scores() {
        if (!class_exists('AIEO_SEO_Score')) { return; }
        AIEO_SEO_Score::run_backfill(1500);
    }

    
    public function run_stage_seo_pillars() {
        if (!class_exists('AIEO_SEO_Pillars')) { return; }
        AIEO_SEO_Pillars::maybe_rebuild();
    }

    
    public function run_stage_seo_redirect_map() {
        if (!class_exists('AIEO_SEO_Redirects')) { return; }
        AIEO_SEO_Redirects::export_edge_map();
    }

    
    public function run_stage_seo_image_alt() {
        if (class_exists('AIEO_DMM_Settings') && ! AIEO_DMM_Settings::get('seo.image_alt_enabled', true)) {
            return;
        }
        if (class_exists('AIEO_SEO_Image')) {
            AIEO_SEO_Image::backfill_alt(300);
        }
    }

    
    public function run_stage_product_attributes() {
        global $wpdb;
        $sp = $wpdb->prefix . 'AIEO_InsertCoreProductAttributes';
        
        
        $exists = (string) $wpdb->get_var($wpdb->prepare(
            "SELECT ROUTINE_NAME FROM information_schema.ROUTINES
              WHERE ROUTINE_SCHEMA = DATABASE()
                AND ROUTINE_NAME = %s
                AND ROUTINE_TYPE = 'PROCEDURE'",
            $sp
        )) === $sp;
        if (!$exists) { return; }

        $vendor = (string) get_option('aieo_core_attribute_vendor', '');
        $brands = (string) get_option('aieo_core_attribute_brands', 'product_brand');
        $sizes  = (string) get_option('aieo_core_attribute_size',   '');
        $colors = (string) get_option('aieo_core_attribute_color',  '');
        if ($brands === '') { $brands = 'product_brand'; }

        $wpdb->query($wpdb->prepare(
            "CALL `{$sp}` (%s, %s, %s, %s)",
            $vendor,
            $brands,
            $sizes,
            $colors
        ));
    }

    
    public function run_stage_propagate_brand() {
        global $wpdb;
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             INNER JOIN {$wpdb->prefix}aieo_core_product_vitals p
                 ON p.product_id = v.parent_id
                 AND p.product_id <> v.product_id
             SET v.brand      = p.brand,
                 v.brand_id   = p.brand_id,
                 v.brand_slug = p.brand_slug,
                 v.brand_bias = p.brand_bias
             WHERE v.product_id <> v.parent_id
               AND p.brand IS NOT NULL
               AND p.brand <> ''
               AND p.brand <> 'No Brand'"
        );
    }

    
    public function run_stage_propagate_marketplace_categories() {
        global $wpdb;
        $ce  = $wpdb->prefix . 'aieo_dmm_category_extras';
        $pv  = $wpdb->prefix . 'aieo_core_product_vitals';
        
        
        
        
        if ($wpdb->get_var("SHOW TABLES LIKE '{$ce}'") !== $ce) { return; }
        
        $wpdb->query(
            "UPDATE {$pv} v
             LEFT JOIN {$ce} ce_sub  ON ce_sub.taxonomy  = 'product_cat' AND ce_sub.term_id  = v.subcat_id
             LEFT JOIN {$ce} ce_head ON ce_head.taxonomy = 'product_cat' AND ce_head.term_id = v.head_id
             LEFT JOIN {$ce} ce_menu ON ce_menu.taxonomy = 'product_cat' AND ce_menu.term_id = v.menu_id
             SET v.google_product_category = COALESCE(
                   NULLIF(ce_sub.google_category_path,  ''),
                   NULLIF(ce_head.google_category_path, ''),
                   NULLIF(ce_menu.google_category_path, '')
                 ),
                 v.facebook_product_category = COALESCE(
                   NULLIF(ce_sub.facebook_category_path,  ''),
                   NULLIF(ce_head.facebook_category_path, ''),
                   NULLIF(ce_menu.facebook_category_path, '')
                 )"
        );
    }

    
    public function run_stage_needs_population() {
        $needs_file = plugin_dir_path(dirname(dirname(__FILE__))) . 'class-product-needs.php';
        if (!class_exists('AI_eShop_Product_Needs') && file_exists($needs_file)) {
            require_once $needs_file;
        }
        if (class_exists('AI_eShop_Product_Needs')) {
            AI_eShop_Product_Needs::get_instance()->populate_product_needs();
        }
    }

    
    public function run_stage_variation_swatches() {
        global $wpdb;

        $uploads = wp_upload_dir();
        if (empty($uploads['baseurl'])) {
            return; 
        }
        $base = esc_sql(trailingslashit($uploads['baseurl']));

        
        
        
        
        
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             INNER JOIN {$wpdb->postmeta} pm_attr
                 ON pm_attr.post_id = v.product_id
                 AND pm_attr.meta_key LIKE 'attribute_pa_%'
             INNER JOIN {$wpdb->terms} t
                 ON t.slug = pm_attr.meta_value
             INNER JOIN {$wpdb->term_taxonomy} tt
                 ON tt.term_id = t.term_id
                 AND tt.taxonomy = REPLACE(pm_attr.meta_key, 'attribute_', '')
             INNER JOIN {$wpdb->termmeta} tm
                 ON tm.term_id = t.term_id
                 AND tm.meta_key = '_aieo_swatch_image_id'
                 AND tm.meta_value <> ''
                 AND tm.meta_value <> '0'
             INNER JOIN {$wpdb->postmeta} pm_file
                 ON pm_file.post_id = tm.meta_value
                 AND pm_file.meta_key = '_wp_attached_file'
             SET v.variation_swatch = CONCAT('{$base}', pm_file.meta_value)
             WHERE v.product_id <> v.parent_id"
        );
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             INNER JOIN {$wpdb->postmeta} pm_attr
                 ON pm_attr.post_id = v.product_id
                 AND pm_attr.meta_key LIKE 'attribute_pa_%'
             INNER JOIN {$wpdb->terms} t
                 ON t.slug = pm_attr.meta_value
             INNER JOIN {$wpdb->term_taxonomy} tt
                 ON tt.term_id = t.term_id
                 AND tt.taxonomy = REPLACE(pm_attr.meta_key, 'attribute_', '')
             INNER JOIN {$wpdb->termmeta} tm
                 ON tm.term_id = t.term_id
                 AND tm.meta_key = 'product_attribute_image'
                 AND tm.meta_value <> ''
                 AND tm.meta_value <> '0'
             INNER JOIN {$wpdb->postmeta} pm_file
                 ON pm_file.post_id = tm.meta_value
                 AND pm_file.meta_key = '_wp_attached_file'
             SET v.variation_swatch = CONCAT('{$base}', pm_file.meta_value)
             WHERE v.product_id <> v.parent_id
               AND (v.variation_swatch IS NULL OR v.variation_swatch = '')"
        );

        
        
        
        
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             INNER JOIN {$wpdb->posts} p
                 ON p.ID = v.product_id
                 AND p.post_type = 'product_variation'
             INNER JOIN {$wpdb->postmeta} pm_gal
                 ON pm_gal.post_id = v.product_id
                 AND pm_gal.meta_key = '_product_image_gallery'
                 AND pm_gal.meta_value <> ''
             INNER JOIN {$wpdb->postmeta} pm_file
                 ON pm_file.post_id = SUBSTRING_INDEX(pm_gal.meta_value, ',', 1)
                 AND pm_file.meta_key = '_wp_attached_file'
             SET v.image_url = CONCAT('{$base}', pm_file.meta_value)"
        );
        
        
        
        
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             INNER JOIN {$wpdb->posts} p
                 ON p.ID = v.product_id
                 AND p.post_type = 'product_variation'
             INNER JOIN {$wpdb->postmeta} pm_gal
                 ON pm_gal.post_id = v.product_id
                 AND pm_gal.meta_key = 'botiga_variation_gallery'
                 AND pm_gal.meta_value <> ''
                 AND pm_gal.meta_value <> 'a:0:{}'
             INNER JOIN {$wpdb->postmeta} pm_file
                 ON pm_file.post_id = SUBSTRING_INDEX(SUBSTRING_INDEX(pm_gal.meta_value, ':\"', -1), '\";', 1)
                 AND pm_file.meta_key = '_wp_attached_file'
             SET v.image_url = CONCAT('{$base}', pm_file.meta_value)
             WHERE v.image_url IS NULL OR v.image_url = ''"
        );

        
        
        
        
        
        
        
        
        
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             JOIN (
                 SELECT pm.post_id, GROUP_CONCAT(CONCAT('{$base}', pm_file.meta_value) ORDER BY n.n SEPARATOR ',') AS urls
                 FROM {$wpdb->postmeta} pm
                 JOIN (
                     SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
                     UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
                     UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15
                     UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
                 ) n ON CHAR_LENGTH(pm.meta_value) - CHAR_LENGTH(REPLACE(pm.meta_value, ',', '')) >= n.n - 1
                 JOIN {$wpdb->postmeta} pm_file
                     ON pm_file.post_id = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(pm.meta_value, ',', n.n), ',', -1) AS UNSIGNED)
                     AND pm_file.meta_key = '_wp_attached_file'
                 WHERE pm.meta_key = '_product_image_gallery' AND pm.meta_value <> ''
                 GROUP BY pm.post_id
             ) gal ON gal.post_id = v.product_id
             SET v.image_gallery_urls = gal.urls"
        );
        
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             INNER JOIN {$wpdb->posts} p
                 ON p.ID = v.product_id
                 AND p.post_type = 'product_variation'
             INNER JOIN {$wpdb->postmeta} pm_gal
                 ON pm_gal.post_id = v.product_id
                 AND pm_gal.meta_key = 'botiga_variation_gallery'
                 AND pm_gal.meta_value <> ''
                 AND pm_gal.meta_value <> 'a:0:{}'
             INNER JOIN {$wpdb->postmeta} pm_file
                 ON pm_file.post_id = SUBSTRING_INDEX(SUBSTRING_INDEX(pm_gal.meta_value, ':\"', -1), '\";', 1)
                 AND pm_file.meta_key = '_wp_attached_file'
             SET v.image_gallery_urls = CONCAT('{$base}', pm_file.meta_value)
             WHERE v.image_gallery_urls IS NULL OR v.image_gallery_urls = ''"
        );

        
        
        
        
        
        
        if (class_exists('AIEO_DMM_Variations_Gallery') && has_filter('aieo_variation_gallery_image_ids')) {
            $variation_ids = $wpdb->get_col(
                "SELECT v.ID FROM {$wpdb->posts} v WHERE v.post_type = 'product_variation' AND v.post_status IN ('publish','private')"
            );
            foreach ($variation_ids as $vid) {
                $ids = AIEO_DMM_Variations_Gallery::resolve_image_ids((int) $vid, 'data_prep');
                if (empty($ids)) { continue; }
                
                
                
                $canonical = (string) get_post_meta((int) $vid, '_product_image_gallery', true);
                $canonical_ids = $canonical !== ''
                    ? array_values(array_filter(array_map('intval', explode(',', $canonical))))
                    : array();
                if ($canonical_ids === $ids) { continue; } 
                $urls = array();
                foreach ($ids as $aid) {
                    $url = wp_get_attachment_image_url($aid, 'full');
                    if ($url) { $urls[] = $url; }
                }
                if (!empty($urls)) {
                    $wpdb->update(
                        $wpdb->prefix . 'aieo_core_product_vitals',
                        array('image_gallery_urls' => implode(',', $urls)),
                        array('product_id' => (int) $vid)
                    );
                }
            }
        }
    }

    
    public function run_stage_sellable_status_flag() {
        global $wpdb;

        
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             SET v.status = 0
             WHERE v.product_id = v.parent_id
               AND EXISTS (
                   SELECT 1 FROM {$wpdb->prefix}aieo_core_product_vitals c
                   WHERE c.parent_id = v.product_id
                     AND c.product_id <> v.product_id
               )"
        );

        
        
        $wpdb->query(
            "UPDATE {$wpdb->prefix}aieo_core_product_vitals v
             INNER JOIN {$wpdb->posts} p ON p.ID = v.product_id
             INNER JOIN {$wpdb->posts} pp ON pp.ID = v.parent_id
             SET v.status = 1
             WHERE v.product_id <> v.parent_id
               AND p.post_status  = 'publish'
               AND pp.post_status = 'publish'"
        );
    }

    
    public function run_stage_effective_prices() {
        $this->drain_substrate_queue('effective_prices', 'AIEO_Effective_Prices');
    }

    
    public function run_stage_effective_eligibility() {
        $this->drain_substrate_queue('effective_eligibility', 'AIEO_Effective_Eligibility');
    }

    
    private function drain_substrate_queue($surface, $worker_class) {
        if (!class_exists($worker_class) || !class_exists('AIEO_Recompute_Queue')) { return; }
        if (!AIEO_Recompute_Queue::table_exists()) { return; }
        global $wpdb;
        $tbl   = AIEO_Recompute_Queue::table_name();
        $batch = 200;
        $max_rounds = 25; 
        while ($max_rounds-- > 0) {
            
            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT id, entity_id, audience_key, attempts FROM {$tbl}
                 WHERE surface = %s AND attempts < 3
                 ORDER BY id ASC LIMIT %d",
                $surface, $batch
            ), ARRAY_A);
            if (empty($rows)) { break; }
            foreach ($rows as $r) {
                $err = null;
                try {
                    if (!empty($r['audience_key'])) {
                        $worker_class::recompute((int) $r['entity_id'], (string) $r['audience_key']);
                    } else {
                        $worker_class::recompute_all_audiences((int) $r['entity_id']);
                    }
                } catch (\Throwable $e) {
                    $err = $e->getMessage();
                }
                if ($err === null) {
                    $wpdb->delete($tbl, array('id' => (int) $r['id']), array('%d'));
                } else {
                    $wpdb->update(
                        $tbl,
                        array('attempts' => ((int) $r['attempts']) + 1, 'last_error' => substr($err, 0, 250)),
                        array('id' => (int) $r['id']),
                        array('%d', '%s'),
                        array('%d')
                    );
                }
            }
        }
    }

    
    public function ajax_full_substrate() {
        AIEO_DMM::verify_ajax_request();
        @set_time_limit(0); 
        global $wpdb;
        $started = microtime(true);

        if (!class_exists('AIEO_Recompute_Queue') || !AIEO_Recompute_Queue::table_exists()) {
            wp_send_json_error(array('message' => __('Recompute queue not installed.', 'ai-eshop-optimizer')));
        }

        
        
        
        do_action('aieo_dmm_cache_version_bumped', 'gifts');
        $tbl = AIEO_Recompute_Queue::table_name();
        $depth_after_enqueue = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl}");

        
        $this->run_stage_effective_prices();
        $this->run_stage_effective_eligibility();
        $depth_remaining = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl}");

        $duration = round(microtime(true) - $started, 2);
        wp_send_json_success(array(
            'message' => sprintf(
                
                __('Enqueued %1$s, drained %2$s, %3$s left in queue (will be processed on next ticks). Took %4$ss.', 'ai-eshop-optimizer'),
                number_format_i18n($depth_after_enqueue),
                number_format_i18n($depth_after_enqueue - $depth_remaining),
                number_format_i18n($depth_remaining),
                $duration
            ),
            'enqueued'  => $depth_after_enqueue,
            'remaining' => $depth_remaining,
            'duration'  => $duration,
        ));
    }

    public function ajax_save() {
        AIEO_DMM::verify_ajax_request();
        $freq = isset($_POST['frequency']) ? sanitize_key((string) wp_unslash($_POST['frequency'])) : '';
        $time = isset($_POST['time'])      ? sanitize_text_field((string) wp_unslash($_POST['time'])) : '03:30';
        if (!in_array($freq, array('off', 'hourly', 'daily'), true)) { $freq = 'off'; }
        if (!preg_match('/^\d{2}:\d{2}$/', $time)) { $time = '03:30'; }
        update_option(self::SETTINGS_OPTION, array('frequency' => $freq, 'time' => $time), false);
        $this->maybe_schedule();
        wp_send_json_success(array(
            'message' => __('Schedule saved.', 'ai-eshop-optimizer'),
            'next_at' => (int) wp_next_scheduled(self::CRON_HOOK),
        ));
    }

    public function ajax_run_now() {
        AIEO_DMM::verify_ajax_request();
        $log = $this->run_pipeline('manual');
        wp_send_json_success(array(
            'log'     => $log,
            'message' => $log['ok']
                
                ? sprintf(__('Pipeline completed in %ss.', 'ai-eshop-optimizer'), $log['total_duration'])
                
                : sprintf(__('Failed: %s', 'ai-eshop-optimizer'), $log['error']),
        ));
    }

    public function render_subtab() {
        $cfg     = self::settings();
        $last    = (array) get_option(self::LAST_RUN_OPTION, array());
        $next_ts = (int) wp_next_scheduled(self::CRON_HOOK);
        $nonce   = wp_create_nonce(AIEO_DMM::NONCE_ACTION);
        $ajax    = admin_url('admin-ajax.php');

        echo '<div class="aieo-dmm-section">';
        echo '<h2>' . esc_html__('Data prep schedule', 'ai-eshop-optimizer') . '</h2>';
        echo '<p class="description">'
            . esc_html__('Refreshes the AIEO flat tables that marketplace feeds, recommendations, and the storefront read from — wp_aieo_core_product_vitals, brand vitals, plus the substrate tables that drive badge eligibility (wp_aieo_effective_prices) and per-product offer eligibility (wp_aieo_effective_eligibility). The substrate stages drain queued recomputes in steady state; use "Full substrate rebuild" after a sweeping rule change.', 'ai-eshop-optimizer')
            . '</p>';

        
        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Schedule', 'ai-eshop-optimizer') . '</h3>';
        echo '<p>';
        echo '<label style="margin-right:18px;"><input type="radio" name="aieo-dp-freq" value="off" '
            . checked('off', $cfg['frequency'], false) . '> ' . esc_html__('Off (manual only)', 'ai-eshop-optimizer') . '</label>';
        echo '<label style="margin-right:18px;"><input type="radio" name="aieo-dp-freq" value="hourly" '
            . checked('hourly', $cfg['frequency'], false) . '> ' . esc_html__('Every hour', 'ai-eshop-optimizer') . '</label>';
        echo '<label style="margin-right:18px;"><input type="radio" name="aieo-dp-freq" value="daily" '
            . checked('daily', $cfg['frequency'], false) . '> ' . esc_html__('Daily at', 'ai-eshop-optimizer') . ' ';
        echo '<input type="time" id="aieo-dp-time" value="' . esc_attr($cfg['time']) . '" style="width:100px;"> ';
        echo '<span style="color:#6b7280;">' . esc_html__('(Europe/Athens)', 'ai-eshop-optimizer') . '</span>';
        echo '</label>';
        echo '</p>';

        echo '<p>';
        echo '<button type="button" class="button button-primary" id="aieo-dp-save">'
            . esc_html__('Save schedule', 'ai-eshop-optimizer') . '</button>';
        echo ' <button type="button" class="button" id="aieo-dp-run">'
            . esc_html__('Run now', 'ai-eshop-optimizer') . '</button>';
        echo ' <button type="button" class="button" id="aieo-dp-substrate" title="' . esc_attr__('Re-enqueues all products for effective_prices + effective_eligibility, then drains. Use after changing a rule that affects every product (e.g. monthly-offer batch import, gift offer activation).', 'ai-eshop-optimizer') . '">'
            . esc_html__('Full substrate rebuild', 'ai-eshop-optimizer') . '</button>';
        echo ' <span id="aieo-dp-status" style="margin-left:10px;color:#646970;font-size:13px;"></span>';
        echo '</p>';

        echo '<p style="margin:8px 0 0;font-size:12px;color:#374151;">';
        echo '<strong>' . esc_html__('Next scheduled run:', 'ai-eshop-optimizer') . '</strong> ';
        if ($next_ts > 0) {
            echo esc_html(human_time_diff($next_ts) . ' (' . wp_date('Y-m-d H:i', $next_ts) . ')');
        } else {
            echo esc_html__('not scheduled', 'ai-eshop-optimizer');
        }
        echo '</p>';
        echo '</div>';

        
        echo '<div class="aieo-dmm-card">';
        echo '<h3>' . esc_html__('Last run', 'ai-eshop-optimizer') . '</h3>';
        if (empty($last)) {
            echo '<p style="font-size:12px;color:#9ca3af;">' . esc_html__('No runs yet — click "Run now" or wait for the first scheduled tick.', 'ai-eshop-optimizer') . '</p>';
        } else {
            $color = ($last['ok'] ?? true) ? '#15803d' : '#b91c1c';
            echo '<p style="font-size:12px;">';
            echo '<strong>' . esc_html__('When:', 'ai-eshop-optimizer') . '</strong> '
                . esc_html((string) ($last['finished_at'] ?? ''));
            echo ' &middot; <strong>' . esc_html__('Source:', 'ai-eshop-optimizer') . '</strong> '
                . esc_html((string) ($last['source'] ?? ''));
            echo ' &middot; <strong>' . esc_html__('Total:', 'ai-eshop-optimizer') . '</strong> '
                . esc_html((string) ($last['total_duration'] ?? '?')) . 's';
            echo ' &middot; <strong style="color:' . esc_attr($color) . ';">' . esc_html(($last['ok'] ?? true) ? 'OK' : 'FAILED') . '</strong>';
            echo '</p>';
            if (!empty($last['error'])) {
                echo '<p style="color:#b91c1c;font-size:12px;">' . esc_html((string) $last['error']) . '</p>';
            }
            if (!empty($last['stages']) && is_array($last['stages'])) {
                echo '<table class="widefat striped" style="font-size:12px;margin-top:8px;">';
                echo '<thead><tr><th>' . esc_html__('Stage', 'ai-eshop-optimizer') . '</th><th>' . esc_html__('Rows', 'ai-eshop-optimizer') . '</th><th>' . esc_html__('Duration', 'ai-eshop-optimizer') . '</th><th>' . esc_html__('Notes', 'ai-eshop-optimizer') . '</th></tr></thead><tbody>';
                foreach ($last['stages'] as $name => $info) {
                    echo '<tr>';
                    echo '<td><code>' . esc_html((string) $name) . '</code></td>';
                    echo '<td>' . esc_html(isset($info['rows']) ? number_format_i18n((int) $info['rows']) : '—') . '</td>';
                    echo '<td>' . esc_html(isset($info['duration']) ? $info['duration'] . 's' : '—') . '</td>';
                    echo '<td style="color:#6b7280;">';
                    if (!empty($info['error']))   { echo '<span style="color:#b91c1c;">' . esc_html((string) $info['error']) . '</span>'; }
                    elseif (!empty($info['skipped'])) { echo esc_html((string) $info['skipped']); }
                    elseif (!empty($info['notes']))   { echo esc_html((string) $info['notes']); }
                    else { echo '—'; }
                    echo '</td>';
                    echo '</tr>';
                }
                echo '</tbody></table>';
            }
        }
        echo '</div>';

        
        echo '<script>(function(){
            var freqInputs = document.querySelectorAll("input[name=aieo-dp-freq]");
            var timeInp    = document.getElementById("aieo-dp-time");
            var saveBtn    = document.getElementById("aieo-dp-save");
            var runBtn     = document.getElementById("aieo-dp-run");
            var status     = document.getElementById("aieo-dp-status");
            var post = function(action, extra){
                var fd = new FormData();
                fd.set("action", action);
                fd.set("nonce", "' . esc_js($nonce) . '");
                if (extra) { Object.keys(extra).forEach(function(k){ fd.set(k, extra[k]); }); }
                return fetch("' . esc_js($ajax) . '", {method:"POST", body:fd, credentials:"same-origin"})
                    .then(function(r){return r.json();});
            };
            saveBtn.addEventListener("click", function(){
                var freq = "off";
                freqInputs.forEach(function(i){ if (i.checked) freq = i.value; });
                status.textContent = "' . esc_js(__('Saving…', 'ai-eshop-optimizer')) . '";
                post("aieo_dmm_data_prep_save", {frequency: freq, time: timeInp.value}).then(function(j){
                    if (j && j.success) {
                        status.style.color = "#15803d";
                        status.textContent = j.data.message;
                        setTimeout(function(){ window.location.reload(); }, 800);
                    } else {
                        status.style.color = "#b91c1c";
                        status.textContent = (j && j.data && j.data.message) || "' . esc_js(__('Save failed.', 'ai-eshop-optimizer')) . '";
                    }
                });
            });
            runBtn.addEventListener("click", function(){
                runBtn.disabled = true;
                status.style.color = "#646970";
                status.textContent = "' . esc_js(__('Running pipeline… can take 30-90 seconds.', 'ai-eshop-optimizer')) . '";
                post("aieo_dmm_data_prep_run_now", {}).then(function(j){
                    runBtn.disabled = false;
                    if (j && j.success) {
                        status.style.color = (j.data.log && j.data.log.ok) ? "#15803d" : "#b91c1c";
                        status.textContent = j.data.message;
                        setTimeout(function(){ window.location.reload(); }, 1200);
                    } else {
                        status.style.color = "#b91c1c";
                        status.textContent = (j && j.data && j.data.message) || "' . esc_js(__('Run failed.', 'ai-eshop-optimizer')) . '";
                    }
                });
            });
            var substrateBtn = document.getElementById("aieo-dp-substrate");
            if (substrateBtn) {
                substrateBtn.addEventListener("click", function(){
                    if (!confirm("' . esc_js(__('Bulk-enqueue every published product for prices + eligibility recompute and drain inline. Can take 1-3 minutes for large catalogs. Continue?', 'ai-eshop-optimizer')) . '")) return;
                    substrateBtn.disabled = true;
                    status.style.color = "#646970";
                    status.textContent = "' . esc_js(__('Enqueueing + draining substrate… please wait.', 'ai-eshop-optimizer')) . '";
                    post("aieo_dmm_data_prep_full_substrate", {}).then(function(j){
                        substrateBtn.disabled = false;
                        if (j && j.success) {
                            status.style.color = "#15803d";
                            status.textContent = j.data.message;
                            setTimeout(function(){ window.location.reload(); }, 2000);
                        } else {
                            status.style.color = "#b91c1c";
                            status.textContent = (j && j.data && j.data.message) || "' . esc_js(__('Substrate rebuild failed.', 'ai-eshop-optimizer')) . '";
                        }
                    });
                });
            }
        })();</script>';

        echo '</div>';
    }
}
