<?php

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

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

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

require_once __DIR__ . '/class-aieo-analytics-collation.php';

if (!function_exists('aieo_analytics_jobs_definitions')) {
    
    function aieo_analytics_jobs_definitions() {
        return array(
            'order_stats' => array(
                'orders' => true, 
                '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(
                'orders' => true, 
                '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',
                'erp' => array('s1erp_temp_store_orders_table'), 
                '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',
                'erp' => array('s1erp_temp_store_orders_table'), 
                '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',
                'erp' => array('s1erp_temp_unique_product_catalogue_table'), 
                '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',
            ),
            'variations_stock' => array(
                'orders' => true, 
                
                
                
                
                
                'proc' => 'AIEO_GenerateVariationsStockCondition',
                'deps' => array(),
                'label' => 'Variations stock condition (per variable parent)',
                'table' => 'wp_aieo_variations_stock_condition',
                'default_time' => '05:25',
            ),
            
            
            'supplier_sku_sync' => array(
                'proc' => 'AIEO_SyncSupplierSkuMap',
                'erp' => array('s1erp_temp_unique_product_catalogue_table'), 
                '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',
                'erp' => array('s1erp_temp_store_orders_table'), 
                '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',
                'erp' => array('s1erp_temp_unique_product_catalogue_table'), 
                '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',
                'erp' => array('s1erp_temp_store_orders_table', 's1erp_temp_unique_product_catalogue_table'), 
                '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(
                'orders' => true, 
                '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,
  `effective_pid` decimal(12,0) AS (COALESCE(NULLIF(`variation_id`,0),`product_id`)) STORED,
  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`),
  KEY `idx_opa_effective_pid` (`effective_pid`,`date_created_gmt`)
) 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,
  `channel_group` varchar(32) 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_channel_group` (`channel_group`),
  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";

        
        
        
        
        
        
        
        
        
        $variations_stock = "CREATE TABLE IF NOT EXISTS `wp_aieo_variations_stock_condition` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `menu_category` varchar(255) DEFAULT NULL,
  `sub_category` varchar(255) DEFAULT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `total_variations` int NOT NULL DEFAULT 0,
  `instock_variations` int NOT NULL DEFAULT 0,
  `out_of_stock_variations` int NOT NULL DEFAULT 0,
  `pct_out_of_stock` decimal(7,2) NOT NULL DEFAULT 0.00,
  `latest_sale_gmt` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_product_id` (`product_id`),
  KEY `idx_pct_out_of_stock` (`pct_out_of_stock`)
) 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, $variations_stock);
    }
}

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();

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

        
        
        
        
        
        
        
        
        
        $missing = static function ($table, $view, $field, $name) use ($wpdb) {
            return !(int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM information_schema.{$view} WHERE table_schema = DATABASE() AND table_name = %s AND {$field} = %s", $table, $name)); 
        };
        $opa = $prefix . 'aieo_order_products_analytics';
        if ($missing($opa, 'COLUMNS', 'COLUMN_NAME', 'effective_pid')) {
            $wpdb->query("ALTER TABLE `{$opa}` ADD COLUMN `effective_pid` decimal(12,0) AS (COALESCE(NULLIF(`variation_id`,0),`product_id`)) STORED");
            if ($wpdb->last_error) { $errors[] = 'opa.effective_pid col: ' . $wpdb->last_error; }
        }
        if ($missing($opa, 'STATISTICS', 'INDEX_NAME', 'idx_opa_effective_pid')) {
            $wpdb->query("ALTER TABLE `{$opa}` ADD INDEX `idx_opa_effective_pid` (`effective_pid`,`date_created_gmt`)");
            if ($wpdb->last_error) { $errors[] = 'opa.effective_pid idx: ' . $wpdb->last_error; }
        }

        
        
        
        
        
        
        
        
        
        $attr_tbl = $prefix . 'aieo_order_attribution';
        if ($missing($attr_tbl, 'COLUMNS', 'COLUMN_NAME', 'channel_group')) {
            $wpdb->query("ALTER TABLE `{$attr_tbl}` ADD COLUMN `channel_group` varchar(32) DEFAULT NULL AFTER `channel`");
            if ($wpdb->last_error) { $errors[] = 'attr.channel_group col: ' . $wpdb->last_error; }
        }
        if ($missing($attr_tbl, 'STATISTICS', 'INDEX_NAME', 'idx_channel_group')) {
            $wpdb->query("ALTER TABLE `{$attr_tbl}` ADD INDEX `idx_channel_group` (`channel_group`)");
            if ($wpdb->last_error) { $errors[] = 'attr.channel_group idx: ' . $wpdb->last_error; }
        }

        
        
        if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) {
            $wpdb->suppress_errors($prev_suppress);
            return array('analytics need MySQL 8.0.21 or MariaDB 10.3 or newer');
        }
        $om_tbl  = $prefix . 'wc_orders_meta';
        $have_om = (int) $wpdb->get_var("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='{$om_tbl}'");
        if (class_exists('AIEO_Attribution')) {
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            
            $entry_expr = $have_om ? 'm.session_entry' : "''";
            $type_expr  = $have_om ? 'm.source_type' : "''";
            $joins      = $have_om
                ? "LEFT JOIN (\n"
                . "                     SELECT order_id,\n"
                . "                       MAX(CASE WHEN meta_key = '_wc_order_attribution_session_entry' THEN meta_value END) AS session_entry,\n"
                . "                       MAX(CASE WHEN meta_key = '_wc_order_attribution_source_type'   THEN meta_value END) AS source_type\n"
                . "                     FROM `{$om_tbl}`\n"
                . "                     WHERE meta_key IN ('_wc_order_attribution_session_entry','_wc_order_attribution_source_type')\n"
                . "                     GROUP BY order_id\n"
                . "                   ) m ON m.order_id = a.order_id"
                : '';
            $case = AIEO_Attribution::canonical_channel_sql('a.channel', 'a.utm_source', 'a.utm_medium', $entry_expr, $type_expr);
            $wpdb->query(
                "UPDATE `{$attr_tbl}` a
                 {$joins}
                 SET a.channel_group = {$case}
                 WHERE a.channel_group IS NULL"
            );
            if ($wpdb->last_error) { $errors[] = 'attr.channel_group backfill: ' . $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', UTC_TIMESTAMP()
                 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(array('wp_', 'utf8mb4_uca1400_ai_ci'), array($prefix, $coll), $bodies[$id]);
            
            
            $body = (string) preg_replace('/[\x{10000}-\x{10FFFF}]/u', '', $body);
            
            
            
            
            
            
            
            if (class_exists('AIEO_Attribution')) {
                
                
                
                $body = str_replace(
                    '/*AIEO_CHANNEL_GROUP_CASE_SESSION*/',
                    AIEO_Attribution::canonical_channel_sql('s.entry_channel', 's.entry_source', 's.entry_medium', "''", "''"),
                    $body
                );
                $body = str_replace(
                    '/*AIEO_CHANNEL_GROUP_CASE*/',
                    AIEO_Attribution::canonical_channel_sql("''", 'p.utm_source', 'p.utm_medium', 'p.session_entry', 'p.source_type'),
                    $body
                );
                
                
                
                foreach (array(
                    '/*AIEO_UTM_DECODE_CAMPAIGN*/' => 'attr.utm_campaign',
                    '/*AIEO_UTM_DECODE_TERM*/'     => 'attr.utm_term',
                    '/*AIEO_UTM_DECODE_CONTENT*/'  => 'attr.utm_content',
                ) as $marker => $col) {
                    $body = str_replace($marker, AIEO_Attribution::canonical_utm_value_sql($col), $body);
                }
            }
            $proc = $prefix . $def['proc'];
            $wpdb->query("DROP PROCEDURE IF EXISTS `{$proc}`");
            $wpdb->query("CREATE PROCEDURE `{$proc}`()\nSQL SECURITY INVOKER\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(array('wp_', 'utf8mb4_uca1400_ai_ci'), array($prefix, $coll), $body);
                $body = (string) preg_replace('/[\x{10000}-\x{10FFFF}]/u', '', $body); 
                $proc = $prefix . $name;
                $wpdb->query("DROP PROCEDURE IF EXISTS `{$proc}`");
                $wpdb->query("CREATE PROCEDURE `{$proc}`()\nSQL SECURITY INVOKER\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;
    }
}