<?php

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

require_once __DIR__ . '/analytics-jobs-procs.php';

require_once __DIR__ . '/analytics-jobs-predictor-staged.php';

if (!function_exists('aieo_analytics_jobs_definitions')) {
    
    function aieo_analytics_jobs_definitions() {
        return array(
            'order_stats' => array(
                'file' => 'generatepodsorderstats.sql',
                'proc' => 'AIEO_GenerateOrderStats',
                'deps' => array(),
                'label' => 'Per-order statistics',
                'table' => 'wp_aieo_order_stats',
                'default_time' => '04:00',
            ),
            'cum_order_stats' => array(
                'file' => 'generatecumorderstats.sql',
                'proc' => 'AIEO_GenerateCumOrderStats',
                'deps' => array('order_stats'),
                'label' => 'Cumulative (per-customer) statistics',
                'table' => 'wp_aieo_cumulative_order_stats',
                'default_time' => '04:20',
            ),
            'order_products_analytics' => array(
                'file' => 'generatepodsorderproductsanalytics.sql',
                'proc' => 'AIEO_GenerateOrderProductsAnalytics',
                'deps' => array(),
                'label' => 'Order products analytics (per line item)',
                'table' => 'wp_aieo_order_products_analytics',
                'default_time' => '04:30',
            ),
            'order_products_behaviours' => array(
                'file' => 'generateorderproductsbehaviours.sql',
                'proc' => 'AIEO_GenerateOrderProductsBehaviours',
                'deps' => array('order_products_analytics'),
                'label' => 'Order products behaviours (entry / exit / single)',
                'table' => 'wp_aieo_order_products_entry',
                'default_time' => '04:35',
            ),
            'cart_frequencies' => array(
                'file' => 'generateorderproductscartfrequencies.sql',
                'proc' => 'AIEO_GenerateOrderProductsCartFrequencies',
                'deps' => array('order_products_behaviours'),
                'label' => 'Product cart frequencies (first / last / single choice)',
                'table' => 'wp_aieo_order_products_freq_entry',
                'default_time' => '04:40',
            ),
            'invoice_match_prev_month' => array(
                'file' => 'previous_month_salesmatching.sql',
                'proc' => 'AIEO_GenerateInvoiceMatchPrevMonth',
                'deps' => array('order_stats', 'order_products_analytics'),
                'label' => 'Woo↔ERP invoice matching — previous month',
                'table' => 'wp_aieo_order_invoice_match_report',
                'default_time' => '04:50',
            ),
            'invoice_match_curr_month' => array(
                'file' => 'current_month_salesmatching.sql',
                'proc' => 'AIEO_GenerateInvoiceMatchCurrentMonth',
                'deps' => array('order_stats', 'order_products_analytics'),
                'label' => 'Woo↔ERP invoice matching — current month',
                'table' => 'wp_aieo_order_invoice_match_report_current_month',
                'default_time' => '04:55',
            ),
            'store_only_products' => array(
                'file' => 'preparestoreonlyproducttables.sql',
                'proc' => 'AIEO_PrepareStoreOnlyProductTables',
                'deps' => array(),
                'label' => 'ERP store-only product tables (reciprocal IDs)',
                'table' => 'wp_aieo_store_only_products',
                'default_time' => '05:05',
            ),
            'sales_predictor' => array(
                
                'proc' => 'AIEO_GenerateSalesPredictor',
                'deps' => array('order_products_analytics'),
                'label' => 'Sales / out-of-stock predictor (self-tuning, per grain)',
                'table' => 'wp_aieo_sales_predictor',
                'default_time' => '05:10',
            ),
            
            
            'supplier_sku_sync' => array(
                'proc' => 'AIEO_SyncSupplierSkuMap',
                'deps' => array(),
                'label' => 'Supplier ↔ SKU map (per-product supplier from ERP)',
                'table' => 'wp_aieo_dmm_supplier_sku_map',
                'default_time' => '07:15',
            ),
            'store_sales_stats' => array(
                'proc' => 'AIEO_GenerateStoreSalesStats',
                'deps' => array(),
                'label' => 'Store sales stats — 30/60-day per branch (from ERP)',
                'table' => 'wp_aieo_dmm_store_sales',
                'default_time' => '07:18',
            ),
            'preorder_vitals' => array(
                'proc' => 'AIEO_GeneratePreorderVitals',
                'deps' => array('store_sales_stats', 'supplier_sku_sync'),
                'label' => 'Preorder vitals + per-branch store boards',
                'table' => 'wp_aieo_dmm_preorder_vitals_stats',
                'default_time' => '07:20',
            ),
            
            
            'stock_recovery' => array(
                'proc' => 'AIEO_GeneratePhysicalRecovery',
                'deps' => array(),
                'label' => 'Stock recovery — false-OOS detection + catalog gaps (physical sales)',
                'table' => 'wp_aieo_dmm_stock_recovery_candidates',
                'default_time' => '05:15',
            ),
            'stock_recovery_outcomes' => array(
                'proc' => 'AIEO_GeneratePhysicalRecoveryOutcomes',
                'deps' => array('stock_recovery', 'order_products_analytics'),
                'label' => 'Stock recovery — restock outcomes / sales recovered',
                'table' => 'wp_aieo_dmm_stock_recovery_events',
                'default_time' => '05:20',
            ),
            
            'push_dispatch_prune' => array(
                'proc' => 'AIEO_PrunePushDispatch',
                'deps' => array(),
                'label' => 'Web Push — prune dispatch log (old suppressed rows)',
                'table' => 'wp_aieo_push_dispatch',
                'default_time' => '03:30',
            ),
        );
    }
}

if (!function_exists('aieo_analytics_jobs_owned_schema')) {
    
    function aieo_analytics_jobs_owned_schema() {
        $order_stats = "CREATE TABLE IF NOT EXISTS `wp_aieo_order_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `orderid` decimal(12,0) DEFAULT NULL,
  `customeremail` varchar(255) DEFAULT NULL,
  `customerid` decimal(12,0) DEFAULT NULL,
  `birthyear` decimal(12,0) DEFAULT NULL,
  `birthday` varchar(50) DEFAULT NULL,
  `firstname` varchar(60) DEFAULT NULL,
  `lastname` varchar(90) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `postcode` varchar(50) DEFAULT NULL,
  `numberofitemsperorder` decimal(12,0) DEFAULT NULL,
  `numberoforders` decimal(12,0) DEFAULT NULL,
  `orderspend` decimal(12,2) DEFAULT NULL,
  `shippingcharged` decimal(12,2) DEFAULT NULL,
  `shippingtype` varchar(255) DEFAULT NULL,
  `freeshipping` varchar(5) DEFAULT NULL,
  `totalshipping` decimal(12,2) DEFAULT NULL,
  `netspendonproducts` decimal(12,2) DEFAULT NULL,
  `orderdate` date NOT NULL DEFAULT '0000-00-00',
  `orderdatetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `orderday` decimal(2,0) DEFAULT NULL,
  `orderdaymonth` decimal(2,0) DEFAULT NULL,
  `buyerspayrol` varchar(20) DEFAULT NULL,
  `buyersday` varchar(20) DEFAULT NULL,
  `buyerstime` varchar(20) DEFAULT NULL,
  `buyersdevice` varchar(100) DEFAULT NULL,
  `link_to_order_details` varchar(200) DEFAULT NULL,
  `paymenttype` varchar(80) DEFAULT NULL,
  `pickuplocation` varchar(150) DEFAULT NULL,
  `phoneorder` varchar(5) DEFAULT NULL,
  `orderstatus` varchar(50) DEFAULT NULL,
  `ordersource` varchar(255) DEFAULT NULL,
  `orderutmsource` varchar(255) DEFAULT NULL,
  `orderutmmedium` varchar(255) DEFAULT NULL,
  `orderutmcampaign` varchar(255) DEFAULT NULL,
  `orderutmterm` varchar(255) DEFAULT NULL,
  `orderutmcontent` varchar(255) DEFAULT NULL,
  `statsclientid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id_idx` (`orderid`,`customeremail`),
  KEY `idx_customeremail` (`customeremail`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        $order_products = "CREATE TABLE IF NOT EXISTS `wp_aieo_order_products_analytics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `order_id` decimal(12,0) DEFAULT NULL,
  `product_id` decimal(12,0) DEFAULT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `quantity` decimal(12,0) DEFAULT NULL,
  `price` decimal(12,2) DEFAULT NULL,
  `unit_price` decimal(12,2) DEFAULT NULL,
  `order_item_id` decimal(12,0) DEFAULT NULL,
  `order_sequence` decimal(12,0) DEFAULT NULL,
  `order_items` decimal(12,0) DEFAULT NULL,
  `order_item_name` varchar(255) DEFAULT NULL,
  `order_item_parent_name` varchar(255) DEFAULT NULL,
  `order_total` decimal(12,2) DEFAULT NULL,
  `customer_id` decimal(12,0) DEFAULT NULL,
  `customer_email` varchar(255) DEFAULT NULL,
  `date_created_gmt` datetime DEFAULT NULL,
  `payment_method` varchar(255) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `category_id` decimal(12,0) DEFAULT NULL,
  `menu_category` varchar(200) DEFAULT NULL,
  `head_category` varchar(200) DEFAULT NULL,
  `sub_category` varchar(200) DEFAULT NULL,
  `prof_discount` varchar(3) DEFAULT NULL,
  `list_price` varchar(255) DEFAULT NULL,
  `soft1_id` varchar(255) DEFAULT NULL,
  `parent_product_id` decimal(12,0) DEFAULT NULL,
  `order_item_parent_product_id` decimal(12,0) DEFAULT NULL,
  `stock` decimal(12,0) DEFAULT NULL,
  `variation_id` decimal(12,0) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_analytics_idx` (`order_id`,`product_id`,`customer_email`),
  KEY `idx_poda_date_created_gmt` (`date_created_gmt`),
  KEY `idx_opa_product_date` (`product_id`,`date_created_gmt`),
  KEY `idx_variation_date_created_gmt` (`variation_id`,`date_created_gmt`),
  KEY `idx_variation_id` (`variation_id`),
  KEY `idx_order_id_sequence` (`order_id`,`order_sequence`),
  KEY `idx_order_products_email_order` (`customer_email`,`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        $attribution = "CREATE TABLE IF NOT EXISTS `wp_aieo_order_attribution` (
  `order_id` bigint(20) unsigned NOT NULL,
  `channel` varchar(255) DEFAULT NULL,
  `utm_source` varchar(255) DEFAULT NULL,
  `utm_medium` varchar(255) DEFAULT NULL,
  `utm_campaign` varchar(255) DEFAULT NULL,
  `utm_term` varchar(255) DEFAULT NULL,
  `utm_content` varchar(255) DEFAULT NULL,
  `origin` varchar(12) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_channel` (`channel`),
  KEY `idx_origin` (`origin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        
        
        
        
        $reciprocal_ids = "CREATE TABLE IF NOT EXISTS `wp_aieo_reciprocal_ids` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `SKU` varchar(100) DEFAULT NULL,
  `Soft1Ids` varchar(120) DEFAULT NULL,
  `EshopId` bigint(20) unsigned DEFAULT NULL,
  `EshopParentId` bigint(20) unsigned DEFAULT NULL,
  `ExcludeFromUpdate` int(11) DEFAULT NULL,
  `ContentCreated` datetime DEFAULT current_timestamp(),
  `ContentLastUpdated` datetime DEFAULT current_timestamp(),
  `update_on_next_run` int(11) DEFAULT 0,
  `Soft1Id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `idx_sku` (`SKU`),
  KEY `idx_eshopid` (`EshopId`),
  KEY `idx_eshopparentid` (`EshopParentId`),
  KEY `idx_soft1ids` (`Soft1Ids`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        $store_only = "CREATE TABLE IF NOT EXISTS `wp_aieo_store_only_products` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Soft1Id` bigint(20) unsigned DEFAULT NULL,
  `CompanyIds` bigint(20) unsigned DEFAULT NULL,
  `Soft1Ids` varchar(120) DEFAULT NULL,
  `Soft1ProductName` varchar(930) DEFAULT NULL,
  `EshopParentId` bigint(20) unsigned DEFAULT NULL,
  `EshopId` bigint(20) unsigned DEFAULT NULL,
  `SKU` varchar(230) DEFAULT NULL,
  `SKU_norm` varchar(120) DEFAULT NULL,
  `Barcode` varchar(60) DEFAULT NULL,
  `SupplierId` bigint(20) unsigned DEFAULT NULL,
  `regular_price` varchar(10) DEFAULT NULL,
  `sale_price` varchar(10) DEFAULT NULL,
  `stock` varchar(10) DEFAULT NULL,
  `customer_hairdresser_price` varchar(10) DEFAULT NULL,
  `customer_makigiaz_price` varchar(10) DEFAULT NULL,
  `customer_manicure_price` varchar(10) DEFAULT NULL,
  `customer_beautician_price` varchar(10) DEFAULT NULL,
  `ContentCreated` datetime DEFAULT current_timestamp(),
  `ContentLastUpdated` datetime DEFAULT current_timestamp(),
  `ExcludeFromUpdate` int(11) DEFAULT NULL,
  `ProductStatus` int(11) DEFAULT NULL,
  `VAT` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `idx_soft1id` (`Soft1Id`),
  KEY `idx_eshopid` (`EshopId`),
  KEY `idx_eshopparentid` (`EshopParentId`),
  KEY `idx_sku` (`SKU`),
  KEY `idx_barcode` (`Barcode`),
  KEY `idx_supplierid` (`SupplierId`),
  KEY `idx_sku_norm` (`SKU_norm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        
        
        
        
        
        $sales_predictor = "CREATE TABLE IF NOT EXISTS `wp_aieo_sales_predictor` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `grain` varchar(12) NOT NULL DEFAULT 'variation',
  `product_id` bigint DEFAULT NULL,
  `parent_product_id` bigint DEFAULT NULL,
  `prediction_date` date NOT NULL,
  `current_stock` bigint DEFAULT NULL,
  `out_of_stock_days` bigint DEFAULT NULL,
  `out_of_stock_repetitions` bigint DEFAULT 0,
  `reorder_date` datetime DEFAULT NULL,
  `reorder_quantity` decimal(20,7) DEFAULT NULL,
  `price` decimal(20,2) DEFAULT NULL,
  `price_before_adjustment` decimal(20,2) DEFAULT NULL,
  `price_adjustment_percentage` decimal(20,7) DEFAULT NULL,
  `latest_price_adjustment_date` datetime DEFAULT NULL,
  `price_update_repetitions` bigint DEFAULT 0,
  `total_product_sales` decimal(20,7) DEFAULT NULL,
  `total_product_turnover` decimal(20,7) DEFAULT NULL,
  `quantity_sold_yesterday` decimal(20,7) DEFAULT NULL,
  `turnover_yesterday` decimal(20,7) DEFAULT NULL,
  `quantity_last_7_days` decimal(20,7) DEFAULT NULL,
  `quantity_last_14_days` decimal(20,7) DEFAULT NULL,
  `quantity_last_28_days` decimal(20,7) DEFAULT NULL,
  `quantity_last_56_days` decimal(20,7) DEFAULT NULL,
  `quantity_last_175_days` decimal(20,7) DEFAULT NULL,
  `quantity_next_7_days` decimal(20,7) DEFAULT NULL,
  `quantity_next_14_days` decimal(20,7) DEFAULT NULL,
  `quantity_next_28_days` decimal(20,7) DEFAULT NULL,
  `quantity_next_56_days` decimal(20,7) DEFAULT NULL,
  `quantity_next_175_days` decimal(20,7) DEFAULT NULL,
  `turnover_last_7_days` decimal(20,7) DEFAULT NULL,
  `turnover_last_14_days` decimal(20,7) DEFAULT NULL,
  `turnover_last_28_days` decimal(20,7) DEFAULT NULL,
  `turnover_last_56_days` decimal(20,7) DEFAULT NULL,
  `turnover_last_175_days` decimal(20,7) DEFAULT NULL,
  `turnover_next_7_days` decimal(20,7) DEFAULT NULL,
  `turnover_next_14_days` decimal(20,7) DEFAULT NULL,
  `turnover_next_28_days` decimal(20,7) DEFAULT NULL,
  `turnover_next_56_days` decimal(20,7) DEFAULT NULL,
  `turnover_next_175_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_last_7_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_last_14_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_last_28_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_last_56_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_last_175_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_next_7_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_next_14_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_next_28_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_next_56_days` decimal(20,7) DEFAULT NULL,
  `items_per_day_next_175_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_last_7_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_last_14_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_last_28_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_last_56_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_last_175_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_next_7_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_next_14_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_next_28_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_next_56_days` decimal(20,7) DEFAULT NULL,
  `turnover_per_day_next_175_days` decimal(20,7) DEFAULT NULL,
  `product_availability_last_year_7` tinyint DEFAULT NULL,
  `product_availability_last_year_14` tinyint DEFAULT NULL,
  `product_availability_last_year_28` tinyint DEFAULT NULL,
  `product_availability_last_year_56` tinyint DEFAULT NULL,
  `product_availability_last_year_175` tinyint DEFAULT NULL,
  `linear_items_per_day` decimal(20,7) DEFAULT NULL,
  `ewma_items_per_day` decimal(20,7) DEFAULT NULL,
  `optimistic_estimator_value` decimal(20,7) DEFAULT NULL,
  `optimistic_estimator_name` varchar(40) DEFAULT NULL,
  `pessimistic_estimator_value` decimal(20,7) DEFAULT NULL,
  `pessimistic_estimator_name` varchar(40) DEFAULT NULL,
  `average_estimator_value` decimal(20,7) DEFAULT NULL,
  `linear_estimator_value` decimal(20,7) DEFAULT NULL,
  `ewma_estimator_value` decimal(20,7) DEFAULT NULL,
  `days_until_stock_run_out_linear` decimal(20,7) DEFAULT NULL,
  `days_until_stock_run_out_ewma` decimal(20,7) DEFAULT NULL,
  `estimated_zero_stock_date` datetime DEFAULT NULL,
  `days_to_sell_10_units_linear` decimal(20,7) DEFAULT NULL,
  `days_to_sell_10_units_ewma` decimal(20,7) DEFAULT NULL,
  `estimated_date_to_sell_10_units` datetime DEFAULT NULL,
  `optimistic_cost` decimal(20,7) DEFAULT NULL,
  `pessimistic_cost` decimal(20,7) DEFAULT NULL,
  `average_cost` decimal(20,7) DEFAULT NULL,
  `chosen_method` varchar(40) DEFAULT NULL,
  `chosen_items_per_day` decimal(20,7) DEFAULT NULL,
  `chosen_days_until_runout` decimal(20,7) DEFAULT NULL,
  `chosen_zero_stock_date` datetime DEFAULT NULL,
  `chosen_estimator_value` decimal(20,7) DEFAULT NULL,
  `prediction_confidence` decimal(6,4) DEFAULT NULL,
  `reliability_score` decimal(6,4) DEFAULT NULL,
  `transactions_total` bigint DEFAULT 0,
  `variation_share_of_parent` decimal(6,4) DEFAULT NULL,
  `uses_parent_signal` tinyint DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_grain_product_date` (`grain`,`product_id`,`prediction_date`),
  KEY `idx_grain_date` (`grain`,`prediction_date`),
  KEY `idx_current_stock` (`current_stock`),
  KEY `idx_parent` (`parent_product_id`),
  KEY `idx_reorder_date` (`reorder_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        
        $sales_predictor_history = "CREATE TABLE IF NOT EXISTS `wp_aieo_sales_predictor_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `grain` varchar(12) NOT NULL DEFAULT 'variation',
  `product_id` bigint DEFAULT NULL,
  `snapshot_date` date NOT NULL,
  `current_stock` bigint DEFAULT NULL,
  `price` decimal(20,2) DEFAULT NULL,
  `ipd_linear` decimal(20,7) DEFAULT NULL,
  `ipd_ewma` decimal(20,7) DEFAULT NULL,
  `ipd_average` decimal(20,7) DEFAULT NULL,
  `chosen_ipd` decimal(20,7) DEFAULT NULL,
  `chosen_method` varchar(40) DEFAULT NULL,
  `est_zero_stock_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_grain_product_snap` (`grain`,`product_id`,`snapshot_date`),
  KEY `idx_snap` (`snapshot_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        
        $sales_predictor_accuracy = "CREATE TABLE IF NOT EXISTS `wp_aieo_sales_predictor_accuracy` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `grain` varchar(12) NOT NULL DEFAULT 'variation',
  `segment_type` varchar(20) NOT NULL DEFAULT 'global',
  `segment_key` varchar(64) NOT NULL DEFAULT '',
  `method` varchar(40) NOT NULL,
  `n_samples` int DEFAULT 0,
  `mape` decimal(10,4) DEFAULT NULL,
  `bias` decimal(20,7) DEFAULT NULL,
  `ewma_abs_err` decimal(20,7) DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_grain_segment_method` (`grain`,`segment_type`,`segment_key`,`method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci";

        return array($order_stats, $order_products, $attribution, $reciprocal_ids, $store_only, $sales_predictor, $sales_predictor_history, $sales_predictor_accuracy);
    }
}

if (!function_exists('aieo_analytics_jobs_install_sprocs')) {
    
    function aieo_analytics_jobs_install_sprocs() {
        global $wpdb;
        $prefix = $wpdb->prefix;

        $prev_suppress = isset($wpdb->suppress_errors) ? (bool) $wpdb->suppress_errors : false;
        $wpdb->hide_errors();
        $wpdb->suppress_errors(true);

        $errors = array();

        
        foreach (aieo_analytics_jobs_owned_schema() as $create_sql) {
            $wpdb->query(str_replace('wp_', $prefix, $create_sql));
            if ($wpdb->last_error) { $errors[] = 'schema: ' . $wpdb->last_error; }
        }

        
        
        
        
        $attr   = $prefix . 'aieo_order_attribution';
        $legacy = $prefix . 'pods_order_stats';
        $have_attr   = (int) $wpdb->get_var("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='{$attr}'");
        $have_legacy = (int) $wpdb->get_var("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='{$legacy}'");
        $attr_rows   = $have_attr ? (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$attr}`") : 0;
        if ($have_attr && $have_legacy && $attr_rows === 0) {
            $wpdb->query(
                "INSERT IGNORE INTO `{$attr}` (order_id, channel, utm_source, utm_medium, utm_campaign, utm_term, utm_content, origin, updated_at)
                 SELECT orderid, NULLIF(ordersource,''), NULLIF(orderutmsource,''), NULLIF(orderutmmedium,''),
                        NULLIF(orderutmcampaign,''), NULLIF(orderutmterm,''), NULLIF(orderutmcontent,''), 'pys', NOW()
                 FROM `{$legacy}`
                 WHERE orderid IS NOT NULL AND orderid > 0
                   AND (ordersource<>'' OR orderutmsource<>'' OR orderutmmedium<>'' OR orderutmcampaign<>'' OR orderutmterm<>'' OR orderutmcontent<>'')"
            );
            if ($wpdb->last_error) { $errors[] = 'pys_backfill: ' . $wpdb->last_error; }
        }

        
        $bodies = function_exists('aieo_analytics_jobs_proc_bodies') ? aieo_analytics_jobs_proc_bodies() : array();
        foreach (aieo_analytics_jobs_definitions() as $id => $def) {
            if (empty($bodies[$id])) { $errors[] = "missing body: {$id}"; continue; }
            
            
            $body = str_replace('wp_', $prefix, $bodies[$id]);
            $proc = $prefix . $def['proc'];
            $wpdb->query("DROP PROCEDURE IF EXISTS `{$proc}`");
            $wpdb->query("CREATE PROCEDURE `{$proc}`()\nBEGIN\n" . $body . "\nEND");
            if ($wpdb->last_error) { $errors[] = $def['proc'] . ': ' . $wpdb->last_error; }
        }

        
        
        
        
        if (function_exists('aieo_sales_predictor_staged_bodies')) {
            foreach (aieo_sales_predictor_staged_bodies() as $name => $body) {
                $body = str_replace('wp_', $prefix, $body);
                $proc = $prefix . $name;
                $wpdb->query("DROP PROCEDURE IF EXISTS `{$proc}`");
                $wpdb->query("CREATE PROCEDURE `{$proc}`()\nBEGIN\n" . $body . "\nEND");
                if ($wpdb->last_error) { $errors[] = $name . ': ' . $wpdb->last_error; }
            }
        }

        $wpdb->suppress_errors($prev_suppress);

        if (!empty($errors) && function_exists('aieo_debug_log')) {
            aieo_debug_log('AIEO analytics-jobs sproc install issues: ' . implode(' | ', $errors));
        }
        return empty($errors) ? true : $errors;
    }
}
