<?php

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

require_once dirname(__FILE__) . '/../dmm/sql/dmm-schema.php';

require_once dirname(__FILE__) . '/brand-vitals-schema.php';

if (!function_exists('aieo_get_wp_collation')) {

function aieo_get_wp_collation() {
    global $wpdb;

    
    
    
    
    
    
    
    static $memo = null;
    if ($memo !== null) { return $memo; }

    
    $collation = $wpdb->get_var(
        "SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = '{$wpdb->posts}'
         LIMIT 1"
    );

    
    if (empty($collation) && !empty($wpdb->collate)) {
        $collation = $wpdb->collate;
    }

    
    if (empty($collation)) {
        $collation = 'utf8mb4_unicode_ci';
    }

    return ($memo = $collation);
}

function aieo_get_wp_charset() {
    global $wpdb;

    
    
    static $memo = null;
    if ($memo !== null) { return $memo; }

    
    $charset = $wpdb->get_var(
        "SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = '{$wpdb->posts}'
         AND COLUMN_NAME = 'post_title'
         LIMIT 1"
    );

    
    if (empty($charset) && !empty($wpdb->charset)) {
        $charset = $wpdb->charset;
    }

    
    if (empty($charset)) {
        $charset = 'utf8mb4';
    }

    return ($memo = $charset);
}

function aieo_get_charset_collate() {
    $charset = aieo_get_wp_charset();
    $collate = aieo_get_wp_collation();

    $charset_collate = '';

    if (!empty($charset)) {
        $charset_collate = "DEFAULT CHARACTER SET {$charset}";
    }

    if (!empty($collate)) {
        $charset_collate .= " COLLATE {$collate}";
    }

    return $charset_collate;
}

function aieo_dbdelta($sql) {
    if (!function_exists('dbDelta')) {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    }
    $clean = array();
    foreach (preg_split('/\r\n|\r|\n/', (string) $sql) as $line) {
        
        
        
        $line = preg_replace('/(^|\s)--(\s.*)?$/', '', $line);
        if (trim($line) === '') {
            continue; 
        }
        $clean[] = rtrim($line);
    }
    $sql = implode("\n", $clean);
    
    
    
    
    
    if (preg_match('/^\s*CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+`?([^\s`(]+)`?/i', $sql, $m)) {
        global $wpdb;
        if ((int) $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $m[1]))) {
            return array();
        }
        $wpdb->query($sql);
        return '' === $wpdb->last_error ? array($m[1] => 'Created table ' . $m[1]) : array();
    }
    return dbDelta($sql);
}

} 

function aieo_drop_index_if_exists($index_name, $table_name) {
    global $wpdb;

    
    $index_exists = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
         WHERE table_schema = DATABASE()
         AND table_name = %s
         AND index_name = %s",
        $table_name,
        $index_name
    ));

    
    if ($index_exists > 0) {
        $wpdb->query("DROP INDEX {$index_name} ON {$table_name}");
    }
}

function aieo_create_index_if_not_exists($index_name, $table_name, $columns, $unique = false) {
    global $wpdb;

    
    $wpdb->suppress_errors(true);

    
    $index_exists = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
         WHERE table_schema = DATABASE()
         AND table_name = %s
         AND index_name = %s",
        $table_name,
        $index_name
    ));

    $wpdb->suppress_errors(false);

    
    if ($index_exists == 0) {
        $kind = $unique ? 'CREATE UNIQUE INDEX' : 'CREATE INDEX';
        $wpdb->query("{$kind} {$index_name} ON {$table_name} {$columns}");
        return true;
    }

    return false;
}

function aieo_drop_function_if_exists($function_name) {
    global $wpdb;

    
    $function_exists = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES
         WHERE ROUTINE_SCHEMA = DATABASE()
         AND ROUTINE_NAME = %s
         AND ROUTINE_TYPE = 'FUNCTION'",
        $function_name
    ));

    
    if ($function_exists > 0) {
        $wpdb->query("DROP FUNCTION IF EXISTS {$function_name}");
    }
}

function aieo_drop_procedure_if_exists($procedure_name) {
    global $wpdb;

    
    $procedure_exists = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES
         WHERE ROUTINE_SCHEMA = DATABASE()
         AND ROUTINE_NAME = %s
         AND ROUTINE_TYPE = 'PROCEDURE'",
        $procedure_name
    ));

    
    if ($procedure_exists > 0) {
        $wpdb->query("DROP PROCEDURE IF EXISTS {$procedure_name}");
    }
}

function aieo_can_create_stored_routines() {
    global $wpdb;

    
    $log_bin = $wpdb->get_var("SELECT @@GLOBAL.log_bin");

    
    if ($log_bin == 0 || $log_bin === '0' || $log_bin === 'OFF') {
        return true;
    }

    
    $trust_creators = $wpdb->get_var("SELECT @@GLOBAL.log_bin_trust_function_creators");

    if ($trust_creators == 1) {
        return true;
    }

    
    
    
    $grants = $wpdb->get_results("SHOW GRANTS FOR CURRENT_USER");
    if (!empty($grants)) {
        foreach ($grants as $grant) {
            $grant_str = reset($grant);

            
            if (stripos($grant_str, 'SUPER') !== false ||
                stripos($grant_str, 'BINLOG_ADMIN') !== false) {
                return true;
            }

            
            if (stripos($grant_str, 'ALL PRIVILEGES') !== false &&
                stripos($grant_str, ' ON *.*') !== false) {
                return true;
            }
        }
    }

    return false;
}

function aieo_create_core_product_attributes_table()
{
    global $wpdb;

    
    $core_product_attributes_table_name = $wpdb->prefix . 'aieo_core_product_attributes';

    
    $charset_collate = aieo_get_charset_collate();

    $sql_table = "CREATE TABLE IF NOT EXISTS {$core_product_attributes_table_name} (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255)  DEFAULT NULL,
    `brand` varchar(255)  DEFAULT NULL,
    `brand_id` int DEFAULT NULL,
    `brand_name` varchar(255)  DEFAULT NULL,
    `vendor` varchar(255)  DEFAULT NULL,
    `vendor_id` int DEFAULT NULL,
    `brand_slug` varchar(255)  DEFAULT NULL,
    `product_id` bigint DEFAULT NULL,
    `size` varchar(255)  DEFAULT NULL,
    `color` varchar(255)  DEFAULT NULL,
    `product_count` decimal(12,0) DEFAULT NULL,
    `product_stock` decimal(12,0) DEFAULT NULL,
    `parent_product_id` bigint DEFAULT NULL,
    `effective_product_id` bigint DEFAULT NULL,
    `supplier_id` bigint DEFAULT NULL,
    `supplier` varchar(255)  DEFAULT NULL,
    `brand_bias` decimal(5,2) DEFAULT NULL,
    `campaigns` text DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) {$charset_collate};";

    $wpdb->query($sql_table);

    
    $indexes = array(
        'aieo_idx_cpb_brands_brand_id' => '(brand_id)',
        'aieo_idx_cpb_brands_product' => '(product_id)',
        'aieo_idx_cpb_brands_parent_product' => '(parent_product_id)',
        'aieo_idx_cpb_effective' => '(effective_product_id)'
    );

    foreach ($indexes as $index_name => $columns) {
        aieo_create_index_if_not_exists($index_name, $core_product_attributes_table_name, $columns);
    }
}

function aieo_create_core_product_category_hierarchy_table()
{
  global $wpdb;

  
  $core_product_category_hierarchy_table_name = $wpdb->prefix . 'aieo_core_product_category_hierarchy';

  
  $charset_collate = aieo_get_charset_collate();

  
  $sql_table = "CREATE TABLE IF NOT EXISTS {$core_product_category_hierarchy_table_name} (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255)   DEFAULT NULL,
  `product_id` bigint DEFAULT NULL,
  `top_category_id` int DEFAULT NULL,
  `top_category` varchar(150)   DEFAULT NULL,
  `second_level_category_id` int DEFAULT NULL,
  `second_level_category` varchar(150)   DEFAULT NULL,
  `third_level_category_id` int DEFAULT NULL,
  `third_level_category` varchar(255)   DEFAULT NULL,
  `fourth_level_category_id` int DEFAULT NULL,
  `fourth_level_category` varchar(255)   DEFAULT NULL,
  `fifth_level_category_id` int DEFAULT NULL,
  `fifth_level_category` varchar(255)   DEFAULT NULL,
  `reference_quantity` int DEFAULT NULL,
  `season_start` date DEFAULT NULL,
  `season_end` date DEFAULT NULL,
  `season_midpoint` date DEFAULT NULL,
  `campaigns` text DEFAULT NULL,
  `category_ids` varchar(500)  DEFAULT NULL,
  `tag_ids` varchar(230)  DEFAULT NULL,
  `tag_names` varchar(1000) ,
  `vendor` varchar(255)   DEFAULT NULL,
  `vendor_id` int DEFAULT NULL,
  `google_product_category` varchar(500) DEFAULT NULL,
  `facebook_product_category` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
  ) {$charset_collate};";

  
  
  $wpdb->query($sql_table);

  
  
  
  
  
  if (function_exists('aieo_dmm_ensure_column')) {
    aieo_dmm_ensure_column($core_product_category_hierarchy_table_name, 'google_product_category',   'varchar(500) DEFAULT NULL');
    aieo_dmm_ensure_column($core_product_category_hierarchy_table_name, 'facebook_product_category', 'varchar(500) DEFAULT NULL');
  }

  
  $indexes = array(
    'aieo_idx_hierarchy_product_id' => '(product_id)',
    'aieo_idx_wppch_t_category_id' => '(top_category_id)',
    'aieo_idx_wppch_sl_category_id' => '(second_level_category_id)',
    'aieo_idx_wppch_tl_category_id' => '(third_level_category_id)'
  );

  foreach ($indexes as $index_name => $columns) {
    aieo_create_index_if_not_exists($index_name, $core_product_category_hierarchy_table_name, $columns);
  }
}

function aieo_create_core_category_brand_popularity_table()
{
  global $wpdb;

  
  $table_name = $wpdb->prefix . 'aieo_core_category_brand_popularity';

  
  $charset_collate = aieo_get_charset_collate();

  
  $sql_table = "CREATE TABLE IF NOT EXISTS {$table_name} (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,

    -- Category Identifiers
    `category_id` int DEFAULT NULL,
    `category_name` varchar(255) DEFAULT NULL,
    `category_slug` varchar(255) DEFAULT NULL,
    `category_level` tinyint DEFAULT NULL,
    `parent_category_id` int DEFAULT NULL,

    -- Brand Identifiers (NULL for category-only aggregates)
    `brand_id` int DEFAULT NULL,
    `brand_name` varchar(255) DEFAULT NULL,
    `brand_slug` varchar(255) DEFAULT NULL,

    -- Sales Metrics (Aggregated from store_orders)
    `total_sales` int DEFAULT 0,
    `total_turnover` decimal(12,2) DEFAULT 0.00,
    `total_orders` int DEFAULT 0,
    `product_count` int DEFAULT 0,

    -- Global Rankings
    `category_rank_by_sales` int DEFAULT NULL,
    `category_rank_by_turnover` int DEFAULT NULL,
    `brand_rank_by_sales` int DEFAULT NULL,
    `brand_rank_by_turnover` int DEFAULT NULL,

    -- Contextual Rankings (within parent/category/brand)
    `subcategory_rank_in_parent_sales` int DEFAULT NULL,
    `subcategory_rank_in_parent_turnover` int DEFAULT NULL,
    `brand_rank_in_category_sales` int DEFAULT NULL,
    `brand_rank_in_category_turnover` int DEFAULT NULL,
    `category_rank_in_brand_sales` int DEFAULT NULL,
    `category_rank_in_brand_turnover` int DEFAULT NULL,

    -- Metadata
    `last_updated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `aieo_cbp_category_brand_unique` (`category_id`, `brand_id`),
    KEY `aieo_cbp_idx_category_id` (`category_id`),
    KEY `aieo_cbp_idx_brand_id` (`brand_id`),
    KEY `aieo_cbp_idx_parent_category` (`parent_category_id`),
    KEY `aieo_cbp_idx_cat_rank_sales` (`category_rank_by_sales`),
    KEY `aieo_cbp_idx_subcat_rank` (`parent_category_id`, `subcategory_rank_in_parent_sales`),
    KEY `aieo_cbp_idx_brand_in_cat` (`category_id`, `brand_rank_in_category_sales`)
  ) {$charset_collate};";

  
  $wpdb->query($sql_table);

  
  if ($wpdb->last_error) {
    aieo_debug_log('AIEO: Error creating category_brand_popularity table: ' . $wpdb->last_error);
  } else {
    aieo_debug_log('AIEO: Successfully created/verified category_brand_popularity table');
  }
}

function aieo_create_core_product_vitals_table()
{
  global $wpdb;

  
  $core_product_vitals_table_name = $wpdb->prefix . 'aieo_core_product_vitals';

  
  $charset_collate = aieo_get_charset_collate();

  
  $sql_table = "CREATE TABLE IF NOT EXISTS {$core_product_vitals_table_name} (
      `id` bigint unsigned NOT NULL AUTO_INCREMENT,
      `image_url` longtext  ,
      `name` varchar(255)   DEFAULT NULL,
      `product_id` bigint DEFAULT NULL,
      `parent_id` bigint DEFAULT NULL,
      `parent_product_id` bigint DEFAULT NULL,
      `category_ids` varchar(500)  DEFAULT NULL,
      `product_creation_date` datetime DEFAULT NULL,
      `product_age` int DEFAULT NULL,
      `product_first_sale_date` datetime DEFAULT NULL,
      `product_latest_sale_date` datetime DEFAULT NULL,
      `parent_name` varchar(255)   DEFAULT NULL,
      `parent_sku` varchar(200)   DEFAULT NULL,
      `category_id` int DEFAULT NULL,
      `brand_id` int DEFAULT NULL,
      `brand` varchar(255)   DEFAULT NULL,
      `supplier_id` bigint DEFAULT NULL,
      `supplier` varchar(255) DEFAULT NULL,
      `menu_id` int DEFAULT NULL,
      `menu_category` varchar(255)   DEFAULT NULL,
      `head_id` int DEFAULT NULL,
      `head_category` varchar(255)   DEFAULT NULL,
      `subcat_id` int DEFAULT NULL,
      `sub_category` varchar(255)   DEFAULT NULL,
      `reference_quantity` decimal(12,0) DEFAULT NULL,
      `brand_bias` decimal(5,2) DEFAULT NULL,
      `campaigns` text DEFAULT NULL,
      `season_start` date DEFAULT NULL,
      `season_end` date DEFAULT NULL,
      `season_midpoint` date DEFAULT NULL,
      `terminated_product` varchar(50) DEFAULT NULL,
      `replacement_product` varchar(50) DEFAULT NULL,
      `sku` varchar(200)   DEFAULT NULL,
      `gtin` varchar(40)   DEFAULT NULL,
      `sku_index` text DEFAULT NULL,
      `barcode_index` text DEFAULT NULL,
      `soft1_id` varchar(50) DEFAULT NULL,
      `profdiscount` varchar(10) DEFAULT NULL,
      `price` decimal(10,2) DEFAULT NULL,
      `cost` decimal(10,2) DEFAULT NULL,
      `status` int DEFAULT NULL,
      `product_url` varchar(300)   DEFAULT NULL,
      `link_to_view` varchar(500)   DEFAULT NULL,
      `link_to_cart` longtext  ,
      `link_to_fav` varchar(255)   DEFAULT NULL,
      `short_description` text DEFAULT NULL,
      `weight` decimal(18,4) DEFAULT NULL,
      `color` varchar(255)   DEFAULT NULL,
      `stock` decimal(12,0) DEFAULT NULL,
      `stock_status` varchar(40)   DEFAULT NULL,
      `regular_price` decimal(10,2) DEFAULT NULL,
      `onsale` int DEFAULT NULL,
      `role_special_pricing` int  NULL,
      `product_name` varchar(255)   DEFAULT NULL,
      `parent_product_name` varchar(255)   DEFAULT NULL,
      `category_names` varchar(1000) ,
      `tag_ids` varchar(230)  DEFAULT NULL,
      `tag_names` varchar(1000) ,
      `product_description` longtext  ,
      `vendor` varchar(255) DEFAULT NULL,
      `vendor_id` int DEFAULT NULL,
      `brand_slug` varchar(255)   DEFAULT NULL,
      `length` decimal(18,4) DEFAULT NULL,
      `width` decimal(18,4) DEFAULT NULL,
      `height` decimal(18,4) DEFAULT NULL,
      `size` varchar(255)   DEFAULT NULL,
      `image_gallery_urls` longtext  ,
      `erp_id` varchar(50)   DEFAULT NULL,
      `content_created` datetime DEFAULT CURRENT_TIMESTAMP,
      `content_last_updated` datetime DEFAULT CURRENT_TIMESTAMP,
      `ProdIdGraphUUID` BINARY(16),
      `PPIdGraphUUID` BINARY(16),
      `is_variation_parent` int DEFAULT 0,
      `variation_count` int DEFAULT 0,
      `in_stock_variation_offset` varchar(10) DEFAULT NULL,
      `variation_selector` int DEFAULT NULL,
      `variation_attribute_description` varchar(500) DEFAULT NULL,
      `variation_swatch` varchar(500) DEFAULT NULL,
      `google_product_category` varchar(500) DEFAULT NULL,
      `facebook_product_category` varchar(500) DEFAULT NULL,
      `is_auto_restocked` tinyint(1) DEFAULT 0,
      PRIMARY KEY (`id`)
  ) {$charset_collate};";

  
  $wpdb->query($sql_table);

  
  
  
  
  
  
  if (function_exists('aieo_dmm_ensure_column')) {
    aieo_dmm_ensure_column($core_product_vitals_table_name, 'google_product_category',   'varchar(500) DEFAULT NULL');
    aieo_dmm_ensure_column($core_product_vitals_table_name, 'facebook_product_category', 'varchar(500) DEFAULT NULL');
    
    
    
    aieo_dmm_ensure_column($core_product_vitals_table_name, 'sku_index',     'text DEFAULT NULL');
    aieo_dmm_ensure_column($core_product_vitals_table_name, 'barcode_index', 'text DEFAULT NULL');
    
    
    
    
    
    aieo_dmm_ensure_column($core_product_vitals_table_name, 'is_auto_restocked', 'tinyint(1) DEFAULT 0');
    
    
    
    
    
    aieo_dmm_ensure_column($core_product_vitals_table_name, 'rating_value', 'decimal(3,2) DEFAULT NULL');
    aieo_dmm_ensure_column($core_product_vitals_table_name, 'rating_count', 'int(10) unsigned NOT NULL DEFAULT 0');
  }

  
  
  
  $purl_len = (int) $wpdb->get_var($wpdb->prepare(
    "SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'product_url'",
    $core_product_vitals_table_name
  ));
  if ($purl_len > 0 && $purl_len < 300) {
    $wpdb->query("ALTER TABLE `{$core_product_vitals_table_name}` MODIFY `product_url` varchar(300) DEFAULT NULL");
  }

  
  $indexes = array(
    'aieo_idx_vitals_product_id' => '(product_id)',
    'aieo_idx_vitals_wppv_parent_product' => '(parent_product_id)',
    
    
    
    
    'idx_aieo_parent_id' => '(parent_id)',
    'aieo_idx_vitals_wppv_sku' => '(sku)',
    'aieo_idx_vitals_wppv_parent_sku' => '(parent_sku)',
    'aieo_idx_vitals_wppv_stock' => '(stock)',
    'aieo_idx_vitals_wppv_brand' => '(brand_id)',
    'aieo_idx_vitals_wppv_menu_id' => '(menu_id)',
    'aieo_idx_vitals_wppv_head_id' => '(head_id)',
    'aieo_idx_vitals_wppv_subcat_id' => '(subcat_id)'
  );

  foreach ($indexes as $index_name => $columns) {
    aieo_create_index_if_not_exists($index_name, $core_product_vitals_table_name, $columns);
  }
}

function aieo_create_core_variation_vitals_table()
{
  global $wpdb;

  
  $core_variation_vitals_table_name = $wpdb->prefix . 'aieo_core_variation_vitals';

  
  $charset_collate = aieo_get_charset_collate();

  
  $sql_table = "CREATE TABLE IF NOT EXISTS {$core_variation_vitals_table_name} (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255)   DEFAULT NULL,
    `product_id` bigint DEFAULT NULL,
    `parent_id` bigint DEFAULT NULL,
    `product_creation_date` datetime DEFAULT NULL,
    `product_age` int DEFAULT NULL,
    `product_first_sale_date` datetime DEFAULT NULL,
    `product_latest_sale_date` datetime DEFAULT NULL,
    `sku` varchar(255)   DEFAULT NULL,
    `gtin` varchar(50)   DEFAULT NULL,
    `price` decimal(10,2) DEFAULT NULL,
    `stock` decimal(12,0) DEFAULT NULL,
    `special_price` varchar(10) DEFAULT NULL,
    `weight` decimal(18,4) DEFAULT NULL,
    `imagepath` varchar(255)   DEFAULT NULL,
    `soft1_id` varchar(50) DEFAULT NULL,
    `variation_attribute_description` varchar(500) DEFAULT NULL,
    `variation_swatch` varchar(500) DEFAULT NULL,
    `variation_selector` int DEFAULT NULL,
    `cost` decimal(10,2) DEFAULT NULL,
    `regular_price` decimal(10,2) DEFAULT NULL,
    `onsale` int DEFAULT NULL,
    `role_special_pricing` int  NULL,
    `length` decimal(18,4) DEFAULT NULL,
    `width` decimal(18,4) DEFAULT NULL,
    `height` decimal(18,4) DEFAULT NULL,
    `size` varchar(255)   DEFAULT NULL,
    `color` varchar(255)   DEFAULT NULL,
    `erp_id` varchar(50)   DEFAULT NULL,
    `content_created` datetime DEFAULT CURRENT_TIMESTAMP,
    `content_last_updated` datetime DEFAULT CURRENT_TIMESTAMP,
    `ProdIdGraphUUID` BINARY(16),
    PRIMARY KEY (`id`)
  ) {$charset_collate};";

  
  $wpdb->query($sql_table);

  
  $indexes = array(
    'aieo_idx_var_vitals_product_id' => '(product_id)',
    'aieo_idx_var_vitals_parent_id' => '(parent_id)',
    'aieo_idx_var_vitals_sku' => '(sku)',
    'aieo_idx_var_vitals_erpid' => '(erp_id)'
  );

  foreach ($indexes as $index_name => $columns) {
    aieo_create_index_if_not_exists($index_name, $core_variation_vitals_table_name, $columns);
  }
}

function aieo_create_core_reciprocal_products_table()
{
  global $wpdb;

  
  $core_reciprocal_products_table_name = $wpdb->prefix . 'aieo_core_reciprocal_products';

  
  $charset_collate = aieo_get_charset_collate();

  
  $sql_table = "CREATE TABLE IF NOT EXISTS {$core_reciprocal_products_table_name} (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `old_product_id` bigint DEFAULT NULL,
  `old_parent_product_id` bigint DEFAULT NULL,
  `product_id` bigint DEFAULT NULL,
  `parent_product_id` bigint DEFAULT NULL,
  `sku` varchar(100)  DEFAULT NULL,
  `replacement_cause` int DEFAULT NULL,
  `replacement_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
  ) {$charset_collate};";

  
  
  $wpdb->query($sql_table);

  
  $indexes = array(
    'aieo_idx_rpt_source_product_id' => '(old_product_id)',
    'aieo_idx_rpt_source_parent_product_id' => '(old_parent_product_id)',
    'aieo_idx_rpt_target_product_id' => '(product_id)',
    'aieo_idx_rpt_target_parent_product_id' => '(parent_product_id)',
    'aieo_idx_rpt_target_sku' => '(sku)'
  );

  foreach ($indexes as $index_name => $columns) {
    aieo_create_index_if_not_exists($index_name, $core_reciprocal_products_table_name, $columns);
  }

  
  
  
  
  
  
  
  
  $dupes = (int) $wpdb->get_var(
    "SELECT COUNT(*) FROM (
       SELECT 1 FROM {$core_reciprocal_products_table_name}
        GROUP BY old_product_id, replacement_cause HAVING COUNT(*) > 1
     ) d"
  );
  if ($dupes === 0) {
    aieo_create_index_if_not_exists(
      'aieo_uniq_rpt_old_cause',
      $core_reciprocal_products_table_name,
      '(old_product_id, replacement_cause)',
      true
    );
  } elseif (function_exists('aieo_debug_log')) {
    aieo_debug_log(sprintf(
      'AIEO: reciprocal_products has %d duplicate (old_product_id, cause) groups; unique index not applied.',
      $dupes
    ));
  }
}

function aieo_create_temp_orders_table()
{
  global $wpdb;

  
  $temp_orders_table_name = $wpdb->prefix . 'aieo_temp_orders';

  
  $charset_collate = aieo_get_charset_collate();

  
  $sql_table = "CREATE TABLE IF NOT EXISTS {$temp_orders_table_name} (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OrderId` bigint unsigned NOT NULL,
        `OrderStatus` varchar(40)  DEFAULT NULL,
        `SKU` varchar(230)  DEFAULT NULL,
        `ParentSKU` varchar(200)   DEFAULT NULL,
        `ProductId` int DEFAULT NULL,
        `ParentProductId` int DEFAULT NULL,
        `CategoryId` varchar(230)  DEFAULT NULL,
        `TagId` varchar(230)  DEFAULT NULL,
        `OrderDate` datetime DEFAULT NULL,
        `PrimaryQty` bigint unsigned NOT NULL DEFAULT '0',
        `ItemSequence` bigint unsigned NOT NULL DEFAULT '0',
        `Stock` int DEFAULT NULL,
        `OrderSize` bigint DEFAULT '0',
        `FinalTouchpoint` varchar(255)   DEFAULT NULL,
        `FinalTouchpointId` int DEFAULT NULL,
        `EngagementTime` int DEFAULT NULL,
        `JourneyLinearWeight` decimal(10,6) DEFAULT NULL,
        `AnonymousWeight` varchar(1)  NOT NULL DEFAULT '',
        `Price` decimal(10,2) DEFAULT NULL,
        `RegularPrice` decimal(10,2) DEFAULT NULL,
        `OnSale` int DEFAULT NULL,
        `RoleBasedSpecialPricing` int DEFAULT NULL,
        `Discontinued` int DEFAULT NULL,
        `HasReciprocal` int DEFAULT NULL,
        `LineValue` decimal(10,2) DEFAULT NULL,
        `OrderItemsTotal` decimal(10,2) DEFAULT NULL,
        `ValueLinearWeight` decimal(10,6) DEFAULT NULL,
        `CurrentPrice` decimal(10,2) DEFAULT NULL,
        `CurrentRegularPrice` decimal(10,2) DEFAULT NULL,
        `ItemName` longtext  NULL,
        `ParentItemName` longtext ,
        `ItemNameDiff` varchar(500)  DEFAULT NULL,
        `TotalItemSales` int DEFAULT NULL,
        `DistinctOrderSales` int DEFAULT NULL,
        `TotalTurnover` decimal(10,2) DEFAULT NULL,
        `UniqueItemSales` int DEFAULT NULL,
        `FirstItemSales` int DEFAULT NULL,
        `LastItemSales` int DEFAULT NULL,
        `AvgJLW` decimal(10,7) DEFAULT NULL,
        `AvgVLW` decimal(10,7) DEFAULT NULL,
        `ProfitabilityIndex` int DEFAULT NULL,
        `BrandId` int DEFAULT NULL,
        `Brand` varchar(255)  DEFAULT NULL,
        `BrandSlug` varchar(255)  DEFAULT NULL,
        `Vendor` varchar(255)   DEFAULT NULL,
        `VendorId` int DEFAULT NULL,
        `MenuId` INT DEFAULT NULL,
        `MenuCategory` varchar(255)   DEFAULT NULL,
        `HeadId` INT DEFAULT NULL,
        `HeadCategory` varchar(255)   DEFAULT NULL,
        `SubcatId` INT DEFAULT NULL,
        `SubCategory` varchar(255)   DEFAULT NULL,
        `Weight` decimal(18,4) DEFAULT NULL,
        `Length` decimal(18,4) DEFAULT NULL,
        `Width` decimal(18,4) DEFAULT NULL,
        `Height` decimal(18,4) DEFAULT NULL,
        `Size` varchar(255)   DEFAULT NULL,
        `Color` varchar(255)   DEFAULT NULL,
        `ImageUrl` longtext   DEFAULT NULL,
        `ImageGalleryUrl` longtext   DEFAULT NULL,
        `ProductUrl` varchar(255)   DEFAULT NULL,
        `ERPId` varchar(50)   DEFAULT NULL,
        `BrandAvgJLW` decimal(10,7) DEFAULT NULL,
        `BrandAvgVLW` decimal(10,7) DEFAULT NULL,
        `BrandTotalTurnover` decimal(10,7) DEFAULT NULL,
        `ContentWordCount` int DEFAULT NULL,
        `ContentIntro` longtext ,
        `ContentIntroPlain` longtext ,
        `ContentSentimentArray` varchar(300)  DEFAULT NULL,
        `ContentViews` int DEFAULT NULL,
        `ContentCreated` datetime DEFAULT CURRENT_TIMESTAMP,
        `ContentLastUpdated` datetime DEFAULT CURRENT_TIMESTAMP,
        `ContentIncoming` int DEFAULT NULL,
        `ContentOutgoing` int DEFAULT NULL,
        `ContentCorIndex` decimal(10,7) DEFAULT NULL,
        `CategoryName` longtext ,
        `CategoryCorIndex` decimal(10,7) DEFAULT NULL,
        `TagName` longtext ,
        `TagCorIndex` decimal(10,7) DEFAULT NULL,
        `KeyVerb` varchar(50)  DEFAULT NULL,
        `KeySentimentArray` varchar(500)  DEFAULT NULL,
        `EponymousID` int DEFAULT NULL,
        `LoyaltyID` int DEFAULT NULL,
        `LoyaltyPoints` int DEFAULT NULL,
        `EshopCustomerID` int DEFAULT NULL,
        `StoreCustomerID` int DEFAULT NULL,
        `CustomerEshopSince` datetime DEFAULT NULL,
        `CustomerLoyaltySince` datetime DEFAULT NULL,
        `CustomerStoreSince` datetime DEFAULT NULL,
        `CustomerName` varchar(150)   DEFAULT NULL,
        `CustomerEmail` varchar(80)   DEFAULT NULL,
        `CustomerPhone` varchar(30)   DEFAULT NULL,
        `CustomerPostalCode` varchar(50)   DEFAULT NULL,
        `CustomerLocality` varchar(50)   DEFAULT NULL,
        `AvgOrderSize` decimal(10,2) DEFAULT NULL,
        `MaxOrderSize` int DEFAULT NULL,
        `MinOrderSize` int  DEFAULT NULL,
        `SDOrderSize` DECIMAL(10,7) DEFAULT NULL, 
        `zOrderSize` DECIMAL(10,7) DEFAULT NULL, 
        `AvgSpend` decimal(10,2) DEFAULT NULL,
        `MaxSpend` decimal(10,2) DEFAULT NULL,
        `MinSpend` decimal(10,2) DEFAULT NULL,
        `SDSpend` DECIMAL(10,7) DEFAULT NULL,
        `zSpend` DECIMAL(10,7) DEFAULT NULL,
        `TotalCustomerItems` int DEFAULT NULL,
        `TotalCustomerOrders` int DEFAULT NULL,
        `TotalCustomerTurnover` decimal(10,2) DEFAULT NULL,
        `Fav1Product` int DEFAULT NULL,
        `Fav2Product` int DEFAULT NULL,
        `Fav3Product` int DEFAULT NULL,
        `Fav1Brand` int DEFAULT NULL,
        `Fav2Brand` int DEFAULT NULL,
        `Fav3Brand` int DEFAULT NULL,
        `Fav1MainCat` int DEFAULT NULL,
        `Fav2MainCat` int DEFAULT NULL,
        `Fav3MainCat` int DEFAULT NULL,
        `Fav1HeadCat` int DEFAULT NULL,
        `Fav2HeadCat` int DEFAULT NULL,
        `Fav3HeadCat` int DEFAULT NULL,
        `Fav1SubCat` int DEFAULT NULL,
        `Fav2SubCat` int DEFAULT NULL,
        `Fav3SubCat` int DEFAULT NULL,
        `Fav1Size` varchar(50)  DEFAULT NULL,
        `Fav2Size` varchar(50)  DEFAULT NULL,
        `Fav3Size` varchar(50)  DEFAULT NULL,
        `Fav1Color` varchar(50)  DEFAULT NULL,
        `Fav2Color` varchar(50)  DEFAULT NULL,
        `Fav3Color` varchar(50)  DEFAULT NULL,    
        `LastTransactionDate` datetime DEFAULT NULL,
        `LastTransactionLocation` varchar(80) DEFAULT NULL,
        `LastOrderSpend` decimal(10,2) DEFAULT NULL,
        `CurrentDBO` INT DEFAULT NULL,
        `AvgDBO` decimal(10,2) DEFAULT NULL,
        `MaxDBO` INT DEFAULT NULL,
        `MinDBO` INT DEFAULT NULL,
        `SDDBO` DECIMAL(10,7) DEFAULT NULL,  
        `zDBO` DECIMAL(10,7) DEFAULT NULL,
        `maDBO` DECIMAL(10,7) DEFAULT NULL,
        `Seasonality` varchar(1)  NOT NULL DEFAULT '',
        `PartnerId` int DEFAULT NULL,
        `OriginId` int DEFAULT NULL,
        `OriginType` varchar(50)  DEFAULT NULL,
        `EventId` int DEFAULT NULL,
        `EventType` varchar(50)  DEFAULT NULL,
        `EventLocation` varchar(255)  DEFAULT NULL,
        `AffiliateId` int DEFAULT NULL,
        `GASource` varchar(50)  DEFAULT NULL,
        `GAMedium` varchar(50)  DEFAULT NULL,
        `GACampaign` varchar(50)  DEFAULT NULL,
        `GAContent` varchar(50)  DEFAULT NULL,
        `GATerm` varchar(50)  DEFAULT NULL,
        `GABrand` varchar(50)  DEFAULT NULL,
        `CustomerDevice` varchar(100)  DEFAULT NULL,
        `CustomerSegment` varchar(100)  DEFAULT NULL,
        `CustomerFeedback` varchar(100)  DEFAULT NULL,
        `CustomerFeedforward` varchar(100)  DEFAULT NULL,
        `JourneyEventsArray` tinytext ,
        `JourneyDuration` int DEFAULT NULL,
        `HitType` int DEFAULT NULL,
        `HitRate` decimal(10,7) DEFAULT NULL,
        `Velocity` decimal(10,7) DEFAULT NULL,
        `Momentum` decimal(10,7) DEFAULT NULL,
        `Closeness` decimal(10,7) DEFAULT NULL,
        `AdamicAdar` decimal(10,7) DEFAULT NULL,
        `Jaccard` decimal(10,7) DEFAULT NULL,
        `Louvain` decimal(10,7) DEFAULT NULL,
        `BFS` decimal(10,7) DEFAULT NULL,
        `MotivesArray` decimal(10,7) DEFAULT NULL,
        `CalabiYau` decimal(10,7) DEFAULT NULL,
        `Perspective` varchar(230)  NOT NULL DEFAULT '',
        `Transparency` decimal(10,7) DEFAULT NULL,
        `ABTesting` varchar(40)  NOT NULL DEFAULT '',
        `Neo4jParams` tinytext ,
        `OrderIdGraphUUID` BINARY(16),
        `OrderItemIdGraphUUID` BINARY(16),
        `PPIdGraphUUID` BINARY(16),
        `ProdIdGraphUUID` BINARY(16),
        `EpoIdGraphUUID` BINARY(16),
        `O_PP_GraphUUID` BINARY(16),
        `O_PP_Freq` INT,
        `O_Prod_GraphUUID` BINARY(16),
        `O_Prod_Freq` INT,
        `O_Epo_GraphUUID` BINARY(16),
        `O_Epo_Freq` INT,
        `PP_Epo_GraphUUID` BINARY(16),
        `PP_Epo_Freq` INT,
        `Prod_Epo_GraphUUID` BINARY(16),
        `Prod_Epo_Freq` INT,
        PRIMARY KEY (`Id`)
    ) {$charset_collate};";

  
  
  $wpdb->query($sql_table);

  
  $indexes = array(
    "aieo_idx_orderid_orders" => "(OrderId)",
    "aieo_idx_productid_orders" => "(ProductId)",
    "aieo_idx_parentproductid_orders" => "(ParentProductId)",
    "aieo_idx_brandid_orders" => "(BrandId)",
    "aieo_idx_eponymousid_orders" => "(EponymousID)",
    "aieo_idx_loyaltyid_orders" => "(LoyaltyID)"
  );

  
  foreach ($indexes as $index_name => $columns) {
    aieo_drop_index_if_exists($index_name, $temp_orders_table_name);
    $wpdb->query("CREATE INDEX {$index_name} ON {$temp_orders_table_name} {$columns};");
  }
}

function aieo_create_temp_store_orders_table()
{
  global $wpdb;

  
  $temp_store_orders_table_name = $wpdb->prefix . 'aieo_temp_store_orders';

  
  $charset_collate = aieo_get_charset_collate();

  
  $sql_table = "CREATE TABLE IF NOT EXISTS {$temp_store_orders_table_name} (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OrderId` bigint unsigned NOT NULL,
        `OrderStatus` varchar(40)  DEFAULT NULL,
        `SKU` varchar(230)  DEFAULT NULL,
        `ParentSKU` varchar(200)   DEFAULT NULL,
        `ProductId` int DEFAULT NULL,
        `ParentProductId` int DEFAULT NULL,
        `CategoryId` varchar(230)  DEFAULT NULL,
        `TagId` varchar(230)  DEFAULT NULL,
        `OrderDate` datetime DEFAULT NULL,
        `PrimaryQty` bigint unsigned NOT NULL DEFAULT '0',
        `ItemSequence` bigint unsigned NOT NULL DEFAULT '0',
        `Stock` int DEFAULT NULL,
        `OrderSize` bigint DEFAULT '0',
        `FinalTouchpoint` varchar(255)   DEFAULT NULL,
        `FinalTouchpointId` int DEFAULT NULL,
        `EngagementTime` int DEFAULT NULL,
        `JourneyLinearWeight` decimal(10,6) DEFAULT NULL,
        `AnonymousWeight` varchar(1)  NOT NULL DEFAULT '',
        `Price` decimal(10,2) DEFAULT NULL,
        `RegularPrice` decimal(10,2) DEFAULT NULL,
        `OnSale` int DEFAULT NULL,
        `RoleBasedSpecialPricing` int DEFAULT NULL,
        `Discontinued` int DEFAULT NULL,
        `HasReciprocal` int DEFAULT NULL,
        `LineValue` decimal(10,2) DEFAULT NULL,
        `OrderItemsTotal` decimal(10,2) DEFAULT NULL,
        `TotalOrderValue` decimal(20,6) DEFAULT NULL,
        `ValueLinearWeight` decimal(20,6) DEFAULT NULL,
        `CurrentPrice` decimal(10,2) DEFAULT NULL,
        `CurrentRegularPrice` decimal(10,2) DEFAULT NULL,
        `ItemName` longtext  NULL,
        `ParentItemName` longtext ,
        `ItemNameDiff` varchar(500)  DEFAULT NULL,
        `TotalItemSales` int DEFAULT NULL,
        `DistinctOrderSales` int DEFAULT NULL,
        `TotalTurnover` decimal(10,2) DEFAULT NULL,
        `UniqueItemSales` int DEFAULT NULL,
        `FirstItemSales` int DEFAULT NULL,
        `LastItemSales` int DEFAULT NULL,
        `AvgJLW` decimal(10,7) DEFAULT NULL,
        `AvgVLW` decimal(10,7) DEFAULT NULL,
        `ProfitabilityIndex` int DEFAULT NULL,
        `BrandId` int DEFAULT NULL,
        `Brand` varchar(255)  DEFAULT NULL,
        `BrandSlug` varchar(255)  DEFAULT NULL,
        `Vendor` varchar(255)   DEFAULT NULL,
        `VendorId` int DEFAULT NULL,
        `MenuId` INT DEFAULT NULL,
        `MenuCategory` varchar(255)   DEFAULT NULL,
        `HeadId` INT DEFAULT NULL,
        `HeadCategory` varchar(255)   DEFAULT NULL,
        `SubcatId` INT DEFAULT NULL,
        `SubCategory` varchar(255)   DEFAULT NULL,
        `Weight` decimal(18,4) DEFAULT NULL,
        `Length` decimal(18,4) DEFAULT NULL,
        `Width` decimal(18,4) DEFAULT NULL,
        `Height` decimal(18,4) DEFAULT NULL,
        `Size` varchar(255)   DEFAULT NULL,
        `Color` varchar(255)   DEFAULT NULL,
        `ImageUrl` longtext   DEFAULT NULL,
        `ImageGalleryUrl` longtext   DEFAULT NULL,
        `ProductUrl` varchar(255)   DEFAULT NULL,
        `ERPId` varchar(50)   DEFAULT NULL,
        `BrandAvgJLW` decimal(10,7) DEFAULT NULL,
        `BrandAvgVLW` decimal(10,7) DEFAULT NULL,
        `BrandTotalTurnover` decimal(10,7) DEFAULT NULL,
        `ContentWordCount` int DEFAULT NULL,
        `ContentIntro` longtext ,
        `ContentIntroPlain` longtext ,
        `ContentSentimentArray` varchar(300)  DEFAULT NULL,
        `ContentViews` int DEFAULT NULL,
        `ContentCreated` datetime DEFAULT CURRENT_TIMESTAMP,
        `ContentLastUpdated` datetime DEFAULT CURRENT_TIMESTAMP,
        `ContentIncoming` int DEFAULT NULL,
        `ContentOutgoing` int DEFAULT NULL,
        `ContentCorIndex` decimal(10,7) DEFAULT NULL,
        `CategoryName` longtext ,
        `CategoryCorIndex` decimal(10,7) DEFAULT NULL,
        `TagName` longtext ,
        `TagCorIndex` decimal(10,7) DEFAULT NULL,
        `KeyVerb` varchar(50)  DEFAULT NULL,
        `KeySentimentArray` varchar(500)  DEFAULT NULL,
        `EponymousID` int DEFAULT NULL,
        `LoyaltyID` int DEFAULT NULL,
        `LoyaltyPoints` int DEFAULT NULL,
        `EshopCustomerID` int DEFAULT NULL,
        `StoreCustomerID` int DEFAULT NULL,
        `CustomerEshopSince` datetime DEFAULT NULL,
        `CustomerLoyaltySince` datetime DEFAULT NULL,
        `CustomerStoreSince` datetime DEFAULT NULL,
        `CustomerName` varchar(150)   DEFAULT NULL,
        `CustomerEmail` varchar(80)   DEFAULT NULL,
        `CustomerPhone` varchar(30)   DEFAULT NULL,
        `CustomerPostalCode` varchar(50)   DEFAULT NULL,
        `CustomerLocality` varchar(50)   DEFAULT NULL,
        `AvgOrderSize` decimal(10,2) DEFAULT NULL,
        `MaxOrderSize` int DEFAULT NULL,
        `MinOrderSize` int  DEFAULT NULL,
        `SDOrderSize` DECIMAL(10,7) DEFAULT NULL, 
        `zOrderSize` DECIMAL(10,7) DEFAULT NULL, 
        `AvgSpend` decimal(10,2) DEFAULT NULL,
        `MaxSpend` decimal(10,2) DEFAULT NULL,
        `MinSpend` decimal(10,2) DEFAULT NULL,
        `SDSpend` DECIMAL(10,7) DEFAULT NULL,
        `zSpend` DECIMAL(10,7) DEFAULT NULL,
        `TotalCustomerItems` int DEFAULT NULL,
        `TotalCustomerOrders` int DEFAULT NULL,
        `TotalCustomerTurnover` decimal(10,2) DEFAULT NULL,
        `Fav1Product` int DEFAULT NULL,
        `Fav2Product` int DEFAULT NULL,
        `Fav3Product` int DEFAULT NULL,
        `Fav1Brand` int DEFAULT NULL,
        `Fav2Brand` int DEFAULT NULL,
        `Fav3Brand` int DEFAULT NULL,
        `Fav1MainCat` int DEFAULT NULL,
        `Fav2MainCat` int DEFAULT NULL,
        `Fav3MainCat` int DEFAULT NULL,
        `Fav1HeadCat` int DEFAULT NULL,
        `Fav2HeadCat` int DEFAULT NULL,
        `Fav3HeadCat` int DEFAULT NULL,
        `Fav1SubCat` int DEFAULT NULL,
        `Fav2SubCat` int DEFAULT NULL,
        `Fav3SubCat` int DEFAULT NULL,
        `Fav1Size` varchar(50)  DEFAULT NULL,
        `Fav2Size` varchar(50)  DEFAULT NULL,
        `Fav3Size` varchar(50)  DEFAULT NULL,
        `Fav1Color` varchar(50)  DEFAULT NULL,
        `Fav2Color` varchar(50)  DEFAULT NULL,
        `Fav3Color` varchar(50)  DEFAULT NULL,    
        `LastTransactionDate` datetime DEFAULT NULL,
        `LastTransactionLocation` varchar(80) DEFAULT NULL,
        `LastOrderSpend` decimal(10,2) DEFAULT NULL,
        `CurrentDBO` INT DEFAULT NULL,
        `AvgDBO` decimal(10,2) DEFAULT NULL,
        `MaxDBO` INT DEFAULT NULL,
        `MinDBO` INT DEFAULT NULL,
        `SDDBO` DECIMAL(10,7) DEFAULT NULL,  
        `zDBO` DECIMAL(10,7) DEFAULT NULL,
        `maDBO` DECIMAL(10,7) DEFAULT NULL,
        `Seasonality` int  DEFAULT NULL,
        `PartnerId` int DEFAULT NULL,
        `OriginId` int DEFAULT NULL,
        `OriginType` varchar(50)  DEFAULT NULL,
        `EventId` int DEFAULT NULL,
        `EventType` varchar(50)  DEFAULT NULL,
        `EventLocation` varchar(255)  DEFAULT NULL,
        `AffiliateId` int DEFAULT NULL,
        `GASource` varchar(50)  DEFAULT NULL,
        `GAMedium` varchar(50)  DEFAULT NULL,
        `GACampaign` varchar(50)  DEFAULT NULL,
        `GAContent` varchar(50)  DEFAULT NULL,
        `GATerm` varchar(50)  DEFAULT NULL,
        `GABrand` varchar(50)  DEFAULT NULL,
        `CustomerDevice` varchar(100)  DEFAULT NULL,
        `CustomerSegment` varchar(100)  DEFAULT NULL,
        `CustomerFeedback` varchar(100)  DEFAULT NULL,
        `CustomerFeedforward` varchar(100)  DEFAULT NULL,
        `JourneyEventsArray` tinytext ,
        `JourneyDuration` int DEFAULT NULL,
        `HitType` int DEFAULT NULL,
        `HitRate` decimal(10,7) DEFAULT NULL,
        `Velocity` decimal(10,7) DEFAULT NULL,
        `Momentum` decimal(10,7) DEFAULT NULL,
        `Closeness` decimal(10,7) DEFAULT NULL,
        `AdamicAdar` decimal(10,7) DEFAULT NULL,
        `Jaccard` decimal(10,7) DEFAULT NULL,
        `Louvain` decimal(10,7) DEFAULT NULL,
        `BFS` decimal(10,7) DEFAULT NULL,
        `MotivesArray` decimal(10,7) DEFAULT NULL,
        `CalabiYau` decimal(10,7) DEFAULT NULL,
        `Perspective` varchar(230)  NOT NULL DEFAULT '',
        `Transparency` decimal(10,7) DEFAULT NULL,
        `ABTesting` varchar(40)  NOT NULL DEFAULT '',
        `Neo4jParams` tinytext ,
        `OrderIdGraphUUID` BINARY(16),
        `OrderItemIdGraphUUID` BINARY(16),
        `PPIdGraphUUID` BINARY(16),
        `ProdIdGraphUUID` BINARY(16),
        `EpoIdGraphUUID` BINARY(16),
        `O_PP_GraphUUID` BINARY(16),
        `O_PP_Freq` INT,
        `O_Prod_GraphUUID` BINARY(16),
        `O_Prod_Freq` INT,
        `O_Epo_GraphUUID` BINARY(16),
        `O_Epo_Freq` INT,
        `PP_Epo_GraphUUID` BINARY(16),
        `PP_Epo_Freq` INT,
        `Prod_Epo_GraphUUID` BINARY(16),
        `Prod_Epo_Freq` INT,
        PRIMARY KEY (`Id`)
    ) {$charset_collate};";

  
  
  $wpdb->query($sql_table);

  
  $indexes = array(
    "aieo_idx_orderid_store_orders" => "(OrderId)",
    "aieo_idx_productid_store_orders" => "(ProductId)",
    "aieo_idx_parentproductid_store_orders" => "(ParentProductId)",
    "aieo_idx_brandid_store_orders" => "(BrandId)",
    "aieo_idx_eponymousid_store_orders" => "(EponymousID)",
    "aieo_idx_loyaltyid_store_orders" => "(LoyaltyID)"
  );

  
  foreach ($indexes as $index_name => $columns) {
    aieo_drop_index_if_exists($index_name, $temp_store_orders_table_name);
    $wpdb->query("CREATE INDEX {$index_name} ON {$temp_store_orders_table_name} {$columns};");
  }
}

function aieo_create_temp_products_table()
{
  global $wpdb;

  
  $temp_products_table_name = $wpdb->prefix . 'aieo_temp_product_catalogue';

  
  $charset_collate = aieo_get_charset_collate();

  $sql_table = "CREATE TABLE IF NOT EXISTS {$temp_products_table_name}  (
    `Id` int NOT NULL AUTO_INCREMENT,
    `OrderId` bigint unsigned NOT NULL,
    `OrderStatus` varchar(40)  DEFAULT NULL,
    `SKU` varchar(230)  DEFAULT NULL,
    `ParentSKU` varchar(200)   DEFAULT NULL,
    `ProductId` int DEFAULT NULL,
    `ParentProductId` int DEFAULT NULL,
    `CategoryId` varchar(230)  DEFAULT NULL,
    `TagId` varchar(230)  DEFAULT NULL,
    `OrderDate` datetime DEFAULT NULL,
    `PrimaryQty` int DEFAULT NULL,
    `ItemSequence` bigint unsigned NOT NULL DEFAULT '0',
    `Stock` int DEFAULT NULL,
    `OrderSize` bigint DEFAULT '0',
    `EngagementTime` int DEFAULT NULL,
    `JourneyLinearWeight` decimal(25,4) DEFAULT NULL,
    `AnonymousWeight` varchar(1)  NOT NULL DEFAULT '',
    `Price` decimal(10,2) DEFAULT NULL,
    `RegularPrice` decimal(10,2) DEFAULT NULL,
    `OnSale` int DEFAULT NULL,
    `RoleBasedSpecialPricing` int DEFAULT NULL,
    `Discontinued` int DEFAULT NULL,
    `HasReciprocal` int DEFAULT NULL,
    `LineValue` decimal(10,2) DEFAULT NULL,
    `OrderItemsTotal` decimal(10,2) DEFAULT NULL,
    `ValueLinearWeight` decimal(10,2) DEFAULT NULL,
    `CurrentPrice` decimal(10,2) DEFAULT NULL,
    `CurrentRegularPrice` decimal(10,2) DEFAULT NULL,
    `ItemName` longtext  NULL,
    `ParentItemName` longtext ,
    `ItemNameDiff` varchar(500)  DEFAULT NULL,
    `TotalItemSales` int DEFAULT NULL,
    `DistinctOrderSales` int DEFAULT NULL,
    `TotalTurnover` decimal(10,2) DEFAULT NULL,
    `UniqueItemSales` int DEFAULT NULL,
    `FirstItemSales` int DEFAULT NULL,
    `LastItemSales` int DEFAULT NULL,
    `AvgJLW` decimal(10,7) DEFAULT NULL,
    `AvgVLW` decimal(10,7) DEFAULT NULL,
    `ProfitabilityIndex` int DEFAULT NULL,
    `BrandId` int DEFAULT NULL,
    `Brand` varchar(255)  DEFAULT NULL,
    `BrandSlug` varchar(255)  DEFAULT NULL,    
    `Vendor` varchar(255)   DEFAULT NULL,
    `VendorId` int DEFAULT NULL,   
    `MenuId` INT DEFAULT NULL,
    `MenuCategory` varchar(255)   DEFAULT NULL,
    `HeadId` INT DEFAULT NULL,
    `HeadCategory` varchar(255)   DEFAULT NULL,
    `SubcatId` INT DEFAULT NULL,
    `SubCategory` varchar(255)   DEFAULT NULL,
    `Weight` decimal(18,4) DEFAULT NULL,
    `Length` decimal(18,4) DEFAULT NULL,
    `Width` decimal(18,4) DEFAULT NULL,
    `Height` decimal(18,4) DEFAULT NULL,
    `Size` varchar(255)   DEFAULT NULL,
    `Color` varchar(255)   DEFAULT NULL,
    `ImageUrl` longtext   DEFAULT NULL,
    `ImageGalleryUrl` longtext   DEFAULT NULL,
    `ProductUrl` varchar(255)   DEFAULT NULL,
    `ERPId` varchar(50)   DEFAULT NULL,        
    `BrandAvgJLW` decimal(10,7) DEFAULT NULL,
    `BrandAvgVLW` decimal(10,7) DEFAULT NULL,
    `BrandTotalTurnover` decimal(10,7) DEFAULT NULL,
    `ContentWordCount` int DEFAULT NULL,
    `ContentIntro` longtext ,
    `ContentIntroPlain` longtext ,
    `ContentSentimentArray` varchar(300)  DEFAULT NULL,
    `ContentViews` int DEFAULT NULL,
    `ContentCreated` datetime DEFAULT CURRENT_TIMESTAMP,
    `ContentLastUpdated` datetime DEFAULT CURRENT_TIMESTAMP,
    `ContentIncoming` int DEFAULT NULL,
    `ContentOutgoing` int DEFAULT NULL,
    `ContentCorIndex` decimal(10,7) DEFAULT NULL,
    `CategoryName` longtext ,
    `CategoryCorIndex` decimal(10,7) DEFAULT NULL,
    `TagName` longtext ,
    `TagCorIndex` decimal(10,7) DEFAULT NULL,
    `KeyVerb` varchar(50)  DEFAULT NULL,
    `KeySentimentArray` varchar(500)  DEFAULT NULL,
    `EponymousID` int DEFAULT NULL,
    `AvgOrderSize` decimal(10,2) DEFAULT NULL,
    `MaxOrderSize` int DEFAULT NULL,
    `MinOrderSize` int  DEFAULT NULL,
    `SDOrderSize` DECIMAL(10,7) DEFAULT NULL, 
    `zOrderSize` DECIMAL(10,7) DEFAULT NULL, 
    `AvgSpend` decimal(10,2) DEFAULT NULL,
    `MaxSpend` decimal(10,2) DEFAULT NULL,
    `MinSpend` decimal(10,2) DEFAULT NULL,
    `SDSpend` DECIMAL(10,7) DEFAULT NULL,
    `zSpend` DECIMAL(10,7) DEFAULT NULL,
    `TotalCustomerItems` int DEFAULT NULL,
    `TotalCustomerOrders` int DEFAULT NULL,
    `TotalCustomerTurnover` decimal(10,2) DEFAULT NULL,
    `Fav1Product` int DEFAULT NULL,
    `Fav2Product` int DEFAULT NULL,
    `Fav3Product` int DEFAULT NULL,
    `Fav1Brand` int DEFAULT NULL,
    `Fav2Brand` int DEFAULT NULL,
    `Fav3Brand` int DEFAULT NULL,
    `Fav1MainCat` int DEFAULT NULL,
    `Fav2MainCat` int DEFAULT NULL,
    `Fav3MainCat` int DEFAULT NULL,
    `Fav1HeadCat` int DEFAULT NULL,
    `Fav2HeadCat` int DEFAULT NULL,
    `Fav3HeadCat` int DEFAULT NULL,
    `Fav1SubCat` int DEFAULT NULL,
    `Fav2SubCat` int DEFAULT NULL,
    `Fav3SubCat` int DEFAULT NULL,
    `Fav1Size` varchar(50)  DEFAULT NULL,
    `Fav2Size` varchar(50)  DEFAULT NULL,
    `Fav3Size` varchar(50)  DEFAULT NULL,
    `Fav1Color` varchar(50)  DEFAULT NULL,
    `Fav2Color` varchar(50)  DEFAULT NULL,
    `Fav3Color` varchar(50)  DEFAULT NULL,
    `LastTransactionDate` datetime DEFAULT NULL,
    `LastTransactionLocation` varchar(80) DEFAULT NULL,
    `LastOrderSpend` decimal(10,2) DEFAULT NULL,
    `CurrentDBO` INT DEFAULT NULL,
    `AvgDBO` decimal(10,2) DEFAULT NULL,
    `MaxDBO` INT DEFAULT NULL,
    `MinDBO` INT DEFAULT NULL,
    `SDDBO` DECIMAL(10,7) DEFAULT NULL,  
    `zDBO` DECIMAL(10,7) DEFAULT NULL,
    `maDBO` DECIMAL(10,7) DEFAULT NULL,
    `Seasonality` varchar(1)  NOT NULL DEFAULT '',
    `PartnerId` int DEFAULT NULL,
    `OriginId` int DEFAULT NULL,
    `OriginType` varchar(50)  DEFAULT NULL,
    `EventId` int DEFAULT NULL,
    `EventType` varchar(50)  DEFAULT NULL,
    `EventLocation` varchar(255)  DEFAULT NULL,
    `AffiliateId` int DEFAULT NULL,
    `GASource` varchar(50)  DEFAULT NULL,
    `GAMedium` varchar(50)  DEFAULT NULL,
    `GACampaign` varchar(50)  DEFAULT NULL,
    `GAContent` varchar(50)  DEFAULT NULL,
    `GATerm` varchar(50)  DEFAULT NULL,
    `GABrand` varchar(50)  DEFAULT NULL,
    `CustomerDevice` varchar(100)  DEFAULT NULL,
    `CustomerSegment` varchar(100)  DEFAULT NULL,
    `CustomerFeedback` varchar(100)  DEFAULT NULL,
    `CustomerFeedforward` varchar(100)  DEFAULT NULL,
    `JourneyEventsArray` tinytext ,
    `JourneyDuration` int DEFAULT NULL,
    `HitType` int DEFAULT NULL,
    `HitRate` decimal(10,7) DEFAULT NULL,
    `Velocity` decimal(10,7) DEFAULT NULL,
    `Momentum` decimal(10,7) DEFAULT NULL,
    `Closeness` decimal(10,7) DEFAULT NULL,
    `AdamicAdar` decimal(10,7) DEFAULT NULL,
    `Jaccard` decimal(10,7) DEFAULT NULL,
    `Louvain` decimal(10,7) DEFAULT NULL,
    `BFS` decimal(10,7) DEFAULT NULL,
    `MotivesArray` decimal(10,7) DEFAULT NULL,
    `CalabiYau` decimal(10,7) DEFAULT NULL,
    `Perspective` varchar(230)  NOT NULL DEFAULT '',
    `Transparency` decimal(10,7) DEFAULT NULL,
    `ABTesting` varchar(40)  NOT NULL DEFAULT '',
    `Neo4jParams` tinytext ,
    `OrderIdGraphUUID` BINARY(16),            -- UUID for distinct OrderId (BINARY for 2-3x faster JOINs)
    `OrderItemIdGraphUUID` BINARY(16),        -- UUID for distinct OrderItemId
    `PPIdGraphUUID` BINARY(16),               -- UUID for distinct ParentProductId
    `ProdIdGraphUUID` BINARY(16),             -- UUID for distinct ProductId
    `EpoIdGraphUUID` BINARY(16),              -- UUID for distinct EponymousId
    `O_PP_GraphUUID` BINARY(16),              -- UUID for distinct relation between OrderId and ParentProductId
    `O_PP_Freq` INT,
    `O_Prod_GraphUUID` BINARY(16),            -- UUID for distinct relation between OrderId and ProductId
    `O_Prod_Freq` INT,
    `O_Epo_GraphUUID` BINARY(16),             -- UUID for distinct relation between OrderId and EponymousId
    `O_Epo_Freq` INT,
    `PP_Epo_GraphUUID` BINARY(16),            -- UUID for distinct relation between ParentProductId and EponymousId
    `PP_Epo_Freq` INT,
    `Prod_Epo_GraphUUID` BINARY(16),          -- UUID for distinct relation between ProductId and EponymousId
    `Prod_Epo_Freq` INT,
    PRIMARY KEY (`Id`)
   ) {$charset_collate};";

  
  $wpdb->query($sql_table);

  
  $indexes = array(
    "aieo_idx_productid_products" => "(ProductId)",
    "aieo_idx_parentproductid_products" => "(ParentProductId)",
    "aieo_idx_SKU_products" => "(SKU)",
    "aieo_idx_parentSKU_products" => "(ParentSKU)",
    "aieo_idx_brandid_products" => "(BrandId)"
  );

  
  foreach ($indexes as $index_name => $columns) {
    aieo_drop_index_if_exists($index_name, $temp_products_table_name);
    $wpdb->query("CREATE INDEX {$index_name} ON {$temp_products_table_name} {$columns};");
  }
}

function aieo_create_function_AIEO_StripHTML()
{
  global $wpdb;

  
  if (!aieo_can_create_stored_routines()) {
      aieo_debug_log('AIEO: Skipping AIEO_StripHTML function creation - binary logging enabled but insufficient privileges. The plugin will work without this function. To enable: set log_bin_trust_function_creators=1 or grant SUPER privilege.');
      return false;
  }

  
  aieo_drop_function_if_exists('AIEO_StripHTML');

  $sql = "CREATE FUNCTION AIEO_StripHTML (
    input LONGTEXT
)
RETURNS LONGTEXT

  DETERMINISTIC
  READS SQL DATA
SQL SECURITY INVOKER
BEGIN
    DECLARE start INT DEFAULT 0;
    DECLARE end INT DEFAULT 0;
    DECLARE length INT DEFAULT 0;
    loop_label: LOOP
        SET start = LOCATE('<', input);
        IF start = 0 THEN
            LEAVE loop_label;
        END IF;
        SET end = LOCATE('>', input, start);
        SET length = (end - start) + 1;
        SET input = INSERT(input, start, length, '');
    END LOOP loop_label;

    RETURN input;
END;
";

  $result = $wpdb->query($sql);
  if ($result === false) {
      aieo_debug_log('AIEO: Failed to create AIEO_StripHTML function - ' . $wpdb->last_error);
      return false;
  }
  return true;
}

function aieo_create_function_AIEO_CountWords()
{
  global $wpdb;

  
  if (!aieo_can_create_stored_routines()) {
      aieo_debug_log('AIEO: Skipping AIEO_CountWords function creation - binary logging enabled but insufficient privileges. The plugin will work without this function. To enable: set log_bin_trust_function_creators=1 or grant SUPER privilege.');
      return false;
  }

  
  aieo_drop_function_if_exists('AIEO_CountWords');

  $sql = "CREATE FUNCTION AIEO_CountWords(input_text TEXT) RETURNS INT
    DETERMINISTIC READS SQL DATA
    SQL SECURITY INVOKER
    BEGIN
        DECLARE wordCount INT;

        -- Remove leading and trailing spaces
        SET input_text = TRIM(input_text);

        -- Count spaces and add 1 to get word count
        SET wordCount = LENGTH(input_text) - LENGTH(REPLACE(input_text, ' ', '')) + 1;

        -- If the text is empty, we should return 0
        IF LENGTH(input_text) = 0 THEN
            SET wordCount = 0;
        END IF;

        RETURN wordCount;
    END;
";

  $result = $wpdb->query($sql);
  if ($result === false) {
      aieo_debug_log('AIEO: Failed to create AIEO_CountWords function - ' . $wpdb->last_error);
      return false;
  }
  return true;
}

function aieo_create_function_AIEO_CountLinks()
{
  global $wpdb;

  
  if (!aieo_can_create_stored_routines()) {
      aieo_debug_log('AIEO: Skipping AIEO_CountLinks function creation - binary logging enabled but insufficient privileges. The plugin will work without this function. To enable: set log_bin_trust_function_creators=1 or grant SUPER privilege.');
      return false;
  }

  
  aieo_drop_function_if_exists('AIEO_CountLinks');

  $sql = "CREATE FUNCTION AIEO_CountLinks(input_text TEXT) RETURNS INT
    DETERMINISTIC READS SQL DATA
    SQL SECURITY INVOKER
    BEGIN
        DECLARE linkCount INT;

        SET linkCount = (LENGTH(input_text) - LENGTH(REPLACE(input_text, '<a', ''))) / LENGTH('<a');

        RETURN linkCount;
    END;
";

  $result = $wpdb->query($sql);
  if ($result === false) {
      aieo_debug_log('AIEO: Failed to create AIEO_CountLinks function - ' . $wpdb->last_error);
      return false;
  }
  return true;
}

function aieo_create_sp_UpdateProductCentricStats($sp_prefix)
{
  global $wpdb;

  $sp_name = $sp_prefix . "AIEO_UpdateProductCentricStats";

  $create_sql = "CREATE PROCEDURE $sp_name(IN target_table_name VARCHAR(255), IN use_product_id TINYINT)
        SQL SECURITY INVOKER
        BEGIN
          DECLARE column_id VARCHAR(255);
          SET column_id = IF(use_product_id = 1, 'ProductId', 'ParentProductId');
          
          -- Update TotalItemSales
          SET @sql = CONCAT('UPDATE ', target_table_name, ' AS t1 
          INNER JOIN (
              SELECT ', column_id, ', SUM(PrimaryQty) AS TotalItemSales
              FROM ', target_table_name, '
              WHERE OriginId <> 2 OR LoyaltyID IS NOT NULL 
              GROUP BY ', column_id, '
          ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
          SET t1.TotalItemSales = t2.TotalItemSales;
          ');

          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
          
          -- Update DistinctOrderSales
          SET @sql = CONCAT('
              UPDATE ', target_table_name, ' AS t1
              INNER JOIN (
                  SELECT ', column_id, ', COUNT(DISTINCT OrderId) AS DistinctOrderSales
                  FROM ', target_table_name, '
                  WHERE OriginId <> 2 OR LoyaltyID IS NOT NULL 
                  GROUP BY ', column_id, '
              ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
              SET t1.DistinctOrderSales = t2.DistinctOrderSales;
          ');
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          -- Update TotalTurnover
          SET @sql = CONCAT('
              UPDATE ', target_table_name, ' AS t1
              INNER JOIN (
                  SELECT ', column_id, ', SUM(PrimaryQty * Price) AS TotalTurnover
                  FROM ', target_table_name, '
                  WHERE OriginId <> 2 OR LoyaltyID IS NOT NULL 
                  GROUP BY ', column_id, '
              ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
              SET t1.TotalTurnover = t2.TotalTurnover;
          ');
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          -- Update UniqueItemSales with ItemSequence = 1 AND OrderSize = 1
          SET @sql = CONCAT('
              UPDATE ', target_table_name, ' AS t1
              INNER JOIN (
                  SELECT ', column_id, ', COUNT(DISTINCT OrderId) AS UniqueItemSales
                  FROM ', target_table_name, '
                  WHERE ItemSequence = 1 AND OrderSize = 1 AND (OriginId <> 2 OR LoyaltyID IS NOT NULL) 
                  GROUP BY ', column_id, '
              ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
              SET t1.UniqueItemSales = t2.UniqueItemSales;
          ');
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          -- Update FirstItemSales with ItemSequence = 1
          SET @sql = CONCAT('
              UPDATE ', target_table_name, ' AS t1
              INNER JOIN (
                  SELECT ', column_id, ', COUNT(DISTINCT OrderId) AS FirstItemSales
                  FROM ', target_table_name, '
                  WHERE ItemSequence = 1 AND (OriginId <> 2 OR LoyaltyID IS NOT NULL) 
                  GROUP BY ', column_id, '
              ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
              SET t1.FirstItemSales = t2.FirstItemSales;
          ');
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          -- Update LastItemSales with ItemSequence = OrderSize
          SET @sql = CONCAT('
              UPDATE ', target_table_name, ' AS t1
              INNER JOIN (
                  SELECT ', column_id, ', COUNT(DISTINCT OrderId) AS LastItemSales
                  FROM ', target_table_name, '
                  WHERE ItemSequence = OrderSize AND (OriginId <> 2 OR LoyaltyID IS NOT NULL) 
                  GROUP BY ', column_id, '
              ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
              SET t1.LastItemSales = t2.LastItemSales;
          ');
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          -- Update AvgJLW
          SET @sql = CONCAT('
              UPDATE ', target_table_name, ' AS t1
              INNER JOIN (
                  SELECT ', column_id, ', AVG(JourneyLinearWeight) AS AvgJLWValue
                  FROM ', target_table_name, '
                  WHERE OriginId <> 2 OR LoyaltyID IS NOT NULL 
                  GROUP BY ', column_id, '
              ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
              SET t1.AvgJLW = t2.AvgJLWValue;
          ');
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          -- Update AvgVLW
          SET @sql = CONCAT('
              UPDATE ', target_table_name, ' AS t1
              INNER JOIN (
                  SELECT ', column_id, ', AVG(ValueLinearWeight) AS AvgVLWValue
                  FROM ', target_table_name, '
                  WHERE OriginId <> 2 OR LoyaltyID IS NOT NULL 
                  GROUP BY ', column_id, '
              ) AS t2 ON t1.', column_id, ' = t2.', column_id, '
              SET t1.AvgVLW = t2.AvgVLWValue;
          ');
          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

          PREPARE stmt FROM @sql;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;

      END;
";

  $wpdb->query($create_sql);
}

function aieo_create_sp_AIEO_GenerateGraphDBUUIDFreqs($sp_prefix)
{
  global $wpdb;

  $procedure_name = "{$sp_prefix}AIEO_GenerateGraphDBUUIDFreqs";

  $sql = "CREATE PROCEDURE {$procedure_name}(IN target_table_name VARCHAR(255))
  SQL SECURITY INVOKER
  BEGIN
-- O_PP_Freq based on O_PP_GraphUUID:

SET @sql37 = CONCAT('UPDATE ', target_table_name, ' t1 
    JOIN (
        SELECT Prod_Epo_GraphUUID, COUNT(*) as FreqCount
        FROM ', target_table_name, '
        GROUP BY Prod_Epo_GraphUUID
    ) t2 ON t1.Prod_Epo_GraphUUID = t2.Prod_Epo_GraphUUID
    SET t1.Prod_Epo_Freq = t2.FreqCount;');
PREPARE stmt37 FROM @sql37;
EXECUTE stmt37;
DEALLOCATE PREPARE stmt37;

-- O_PP_Freq based on O_PP_GraphUUID:
SET @sql38 = CONCAT('UPDATE ', target_table_name, ' t1 
    JOIN (
        SELECT O_PP_GraphUUID, COUNT(*) as FreqCount
        FROM ', target_table_name, '
        GROUP BY O_PP_GraphUUID
    ) t2 ON t1.O_PP_GraphUUID = t2.O_PP_GraphUUID
    SET t1.O_PP_Freq = t2.FreqCount;');
PREPARE stmt38 FROM @sql38;
EXECUTE stmt38;
DEALLOCATE PREPARE stmt38;

-- O_Prod_Freq based on O_Prod_GraphUUID:
SET @sql39 = CONCAT('UPDATE ', target_table_name, ' t1 
    JOIN (
        SELECT O_Prod_GraphUUID, COUNT(*) as FreqCount
        FROM ', target_table_name, '
        GROUP BY O_Prod_GraphUUID
    ) t2 ON t1.O_Prod_GraphUUID = t2.O_Prod_GraphUUID
    SET t1.O_Prod_Freq = t2.FreqCount;');
PREPARE stmt39 FROM @sql39;
EXECUTE stmt39;
DEALLOCATE PREPARE stmt39;

-- O_Epo_Freq based on O_Epo_GraphUUID:
SET @sql40 = CONCAT('UPDATE ', target_table_name, ' t1 
    JOIN (
        SELECT O_Epo_GraphUUID, COUNT(*) as FreqCount
        FROM ', target_table_name, '
        GROUP BY O_Epo_GraphUUID
    ) t2 ON t1.O_Epo_GraphUUID = t2.O_Epo_GraphUUID
    SET t1.O_Epo_Freq = t2.FreqCount;');
PREPARE stmt40 FROM @sql40;
EXECUTE stmt40;
DEALLOCATE PREPARE stmt40;

-- PP_Epo_Freq based on PP_Epo_GraphUUID:
SET @sql41 = CONCAT('UPDATE ', target_table_name, ' t1 
    JOIN (
        SELECT PP_Epo_GraphUUID, COUNT(*) as FreqCount
        FROM ', target_table_name, '
        GROUP BY PP_Epo_GraphUUID
    ) t2 ON t1.PP_Epo_GraphUUID = t2.PP_Epo_GraphUUID
    SET t1.PP_Epo_Freq = t2.FreqCount;');
PREPARE stmt41 FROM @sql41;
EXECUTE stmt41;
DEALLOCATE PREPARE stmt41;

SET @sql39 = CONCAT('UPDATE ', target_table_name, ' t1 
    JOIN (
        SELECT O_Prod_GraphUUID, COUNT(*) as FreqCount
        FROM ', target_table_name, '
        GROUP BY O_Prod_GraphUUID
    ) t2 ON t1.O_Prod_GraphUUID = t2.O_Prod_GraphUUID
    SET t1.O_Prod_Freq = t2.FreqCount;');
PREPARE stmt39 FROM @sql39;
EXECUTE stmt39;
DEALLOCATE PREPARE stmt39;

  END;

";

  
  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_GenerateGraphDBUUIDs($sp_prefix)
{
  global $wpdb;

  $procedure_name = "{$sp_prefix}AIEO_GenerateGraphDBUUIDs";

  $sql = "CREATE PROCEDURE {$procedure_name}(IN target_table_name VARCHAR(255))
  SQL SECURITY INVOKER
  BEGIN

  -- For EponymousId (EpoIdGraphUUID)
  SET @sql13 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs13 (
            originalValue BIGINT,
            generatedUUID BINARY(16),
            INDEX idx_original (originalValue)
        ) ENGINE=InnoDB;');
        PREPARE stmt13 FROM @sql13;
        EXECUTE stmt13;
        DEALLOCATE PREPARE stmt13;

        SET @sql14 = CONCAT('INSERT INTO tempUUIDs13 (originalValue, generatedUUID)
            SELECT DISTINCT EponymousId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
            FROM ', target_table_name, ';');
        PREPARE stmt14 FROM @sql14;
        EXECUTE stmt14;
        DEALLOCATE PREPARE stmt14;

        SET @sql15 = CONCAT('UPDATE ', target_table_name, ' 
            JOIN tempUUIDs13 ON ', target_table_name, '.EponymousId = tempUUIDs13.originalValue 
            SET ', target_table_name, '.EpoIdGraphUUID = tempUUIDs13.generatedUUID;');
        PREPARE stmt15 FROM @sql15;
        EXECUTE stmt15;
        DEALLOCATE PREPARE stmt15;

        SET @sql16 = 'DROP TEMPORARY TABLE tempUUIDs13;';
        PREPARE stmt16 FROM @sql16;
        EXECUTE stmt16;
        DEALLOCATE PREPARE stmt16;

        
-- For O_Epo_GraphUUID (OrderId and EponymousId)
SET @sql25 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs25 (
    OrderIdValue BIGINT,
    EponymousIdValue BIGINT,
    generatedUUID BINARY(16),
    INDEX idx_composite (OrderIdValue, EponymousIdValue)
) ENGINE=InnoDB;');
PREPARE stmt25 FROM @sql25;
EXECUTE stmt25;
DEALLOCATE PREPARE stmt25;

SET @sql26 = CONCAT('INSERT INTO tempUUIDs25 (OrderIdValue, EponymousIdValue, generatedUUID)
    SELECT DISTINCT OrderId, EponymousId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
    FROM ', target_table_name, ' 
    WHERE EponymousId <> 0 AND EponymousId IS NOT NULL;');
PREPARE stmt26 FROM @sql26;
EXECUTE stmt26;
DEALLOCATE PREPARE stmt26;

SET @sql27 = CONCAT('UPDATE ', target_table_name, ' 
    JOIN tempUUIDs25 ON ', target_table_name, '.OrderId = tempUUIDs25.OrderIdValue AND ', target_table_name, '.EponymousId = tempUUIDs25.EponymousIdValue
    SET ', target_table_name, '.O_Epo_GraphUUID = tempUUIDs25.generatedUUID;');
PREPARE stmt27 FROM @sql27;
EXECUTE stmt27;
DEALLOCATE PREPARE stmt27;

SET @sql28 = 'DROP TEMPORARY TABLE tempUUIDs25;';
PREPARE stmt28 FROM @sql28;
EXECUTE stmt28;
DEALLOCATE PREPARE stmt28;

-- For PP_Epo_GraphUUID (ParentProductId and EponymousId)
SET @sql29 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs29 (
    ParentProductIdValue BIGINT,
    EponymousIdValue BIGINT,
    generatedUUID BINARY(16),
    INDEX idx_composite (ParentProductIdValue, EponymousIdValue)
) ENGINE=InnoDB;');
PREPARE stmt29 FROM @sql29;
EXECUTE stmt29;
DEALLOCATE PREPARE stmt29;

SET @sql30 = CONCAT('INSERT INTO tempUUIDs29 (ParentProductIdValue, EponymousIdValue, generatedUUID)
    SELECT DISTINCT ParentProductId, EponymousId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
    FROM ', target_table_name, ' 
    WHERE EponymousId <> 0 AND EponymousId IS NOT NULL;');
PREPARE stmt30 FROM @sql30;
EXECUTE stmt30;
DEALLOCATE PREPARE stmt30;

SET @sql31 = CONCAT('UPDATE ', target_table_name, ' 
    JOIN tempUUIDs29 ON ', target_table_name, '.ParentProductId = tempUUIDs29.ParentProductIdValue AND ', target_table_name, '.EponymousId = tempUUIDs29.EponymousIdValue
    SET ', target_table_name, '.PP_Epo_GraphUUID = tempUUIDs29.generatedUUID;');
PREPARE stmt31 FROM @sql31;
EXECUTE stmt31;
DEALLOCATE PREPARE stmt31;

SET @sql32 = 'DROP TEMPORARY TABLE tempUUIDs29;';
PREPARE stmt32 FROM @sql32;
EXECUTE stmt32;
DEALLOCATE PREPARE stmt32;

-- For Prod_Epo_GraphUUID (ProductId and EponymousId)
SET @sql33 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs33 (
    ProductIdValue BIGINT,
    EponymousIdValue BIGINT,
    generatedUUID BINARY(16),
    INDEX idx_composite (ProductIdValue, EponymousIdValue)
) ENGINE=InnoDB;');
PREPARE stmt33 FROM @sql33;
EXECUTE stmt33;
DEALLOCATE PREPARE stmt33;

SET @sql34 = CONCAT('INSERT INTO tempUUIDs33 (ProductIdValue, EponymousIdValue, generatedUUID)
    SELECT DISTINCT ProductId, EponymousId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
    FROM ', target_table_name, ' 
    WHERE EponymousId <> 0 AND EponymousId IS NOT NULL;');
PREPARE stmt34 FROM @sql34;
EXECUTE stmt34;
DEALLOCATE PREPARE stmt34;

SET @sql35 = CONCAT('UPDATE ', target_table_name, ' 
    JOIN tempUUIDs33 ON ', target_table_name, '.ProductId = tempUUIDs33.ProductIdValue AND ', target_table_name, '.EponymousId = tempUUIDs33.EponymousIdValue
    SET ', target_table_name, '.Prod_Epo_GraphUUID = tempUUIDs33.generatedUUID;');
PREPARE stmt35 FROM @sql35;
EXECUTE stmt35;
DEALLOCATE PREPARE stmt35;

SET @sql36 = 'DROP TEMPORARY TABLE tempUUIDs33;';
PREPARE stmt36 FROM @sql36;
EXECUTE stmt36;
DEALLOCATE PREPARE stmt36;
      

      -- For ParentProductId
      -- First set of operations

      SET @sql1 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs1 (
          originalValue BIGINT,
          generatedUUID BINARY(16),
          INDEX idx_original (originalValue)
      ) ENGINE=InnoDB;');
      PREPARE stmt1 FROM @sql1;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;

      SET @sql2 = CONCAT('INSERT INTO tempUUIDs1 (originalValue, generatedUUID)
          SELECT DISTINCT ParentProductId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
          FROM ', target_table_name, ';');
      PREPARE stmt2 FROM @sql2;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;

      SET @sql3 = CONCAT('UPDATE ', target_table_name, ' 
          JOIN tempUUIDs1 ON ', target_table_name, '.ParentProductId = tempUUIDs1.originalValue 
          SET ', target_table_name, '.PPIdGraphUUID = tempUUIDs1.generatedUUID;');
      PREPARE stmt3 FROM @sql3;
      EXECUTE stmt3;
      DEALLOCATE PREPARE stmt3;

      SET @sql4 = 'DROP TEMPORARY TABLE tempUUIDs1;';
      PREPARE stmt4 FROM @sql4;
      EXECUTE stmt4;
      DEALLOCATE PREPARE stmt4;

      -- For OrderId
      SET @sql5 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs5 (
          originalValue BIGINT,
          generatedUUID BINARY(16),
          INDEX idx_original (originalValue)
      ) ENGINE=InnoDB;');
      PREPARE stmt5 FROM @sql5;
      EXECUTE stmt5;
      DEALLOCATE PREPARE stmt5;

      SET @sql6 = CONCAT('INSERT INTO tempUUIDs5 (originalValue, generatedUUID)
          SELECT DISTINCT OrderId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
          FROM ', target_table_name, ';');
      PREPARE stmt6 FROM @sql6;
      EXECUTE stmt6;
      DEALLOCATE PREPARE stmt6;

      SET @sql7 = CONCAT('UPDATE ', target_table_name, ' 
          JOIN tempUUIDs5 ON ', target_table_name, '.OrderId = tempUUIDs5.originalValue 
          SET ', target_table_name, '.OrderIdGraphUUID = tempUUIDs5.generatedUUID;');
      PREPARE stmt7 FROM @sql7;
      EXECUTE stmt7;
      DEALLOCATE PREPARE stmt7;

      SET @sql8 = 'DROP TEMPORARY TABLE tempUUIDs5;';
      PREPARE stmt8 FROM @sql8;
      EXECUTE stmt8;
      DEALLOCATE PREPARE stmt8;

        -- For ProductId (ProdIdGraphUUID)
        SET @sql9 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs19 (
            originalValue BIGINT,
            generatedUUID BINARY(16),
            INDEX idx_original (originalValue)
        ) ENGINE=InnoDB;');
        PREPARE stmt9 FROM @sql9;
        EXECUTE stmt9;
        DEALLOCATE PREPARE stmt9;

        SET @sql10 = CONCAT('INSERT INTO tempUUIDs19 (originalValue, generatedUUID)
            SELECT DISTINCT ProductId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
            FROM ', target_table_name, ';');
        PREPARE stmt10 FROM @sql10;
        EXECUTE stmt10;
        DEALLOCATE PREPARE stmt10;

        SET @sql11 = CONCAT('UPDATE ', target_table_name, ' 
            JOIN tempUUIDs19 ON ', target_table_name, '.ProductId = tempUUIDs19.originalValue 
            SET ', target_table_name, '.ProdIdGraphUUID = tempUUIDs19.generatedUUID;');
        PREPARE stmt11 FROM @sql11;
        EXECUTE stmt11;
        DEALLOCATE PREPARE stmt11;

        SET @sql12 = 'DROP TEMPORARY TABLE tempUUIDs19;';
        PREPARE stmt12 FROM @sql12;
        EXECUTE stmt12;
        DEALLOCATE PREPARE stmt12;

        
-- For O_PP_GraphUUID (OrderId and ParentProductId)
SET @sql17 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs17 (
            OrderIdValue BIGINT,
            ParentProductIdValue BIGINT,
            generatedUUID BINARY(16),
            INDEX idx_composite (OrderIdValue, ParentProductIdValue)
        ) ENGINE=InnoDB;');
        PREPARE stmt17 FROM @sql17;
        EXECUTE stmt17;
        DEALLOCATE PREPARE stmt17;

        SET @sql18 = CONCAT('INSERT INTO tempUUIDs17 (OrderIdValue, ParentProductIdValue, generatedUUID)
            SELECT DISTINCT OrderId, ParentProductId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
            FROM ', target_table_name, ';');
        PREPARE stmt18 FROM @sql18;
        EXECUTE stmt18;
        DEALLOCATE PREPARE stmt18;

        SET @sql19 = CONCAT('UPDATE ', target_table_name, ' 
            JOIN tempUUIDs17 ON ', target_table_name, '.OrderId = tempUUIDs17.OrderIdValue AND ', target_table_name, '.ParentProductId = tempUUIDs17.ParentProductIdValue
            SET ', target_table_name, '.O_PP_GraphUUID = tempUUIDs17.generatedUUID;');
        PREPARE stmt19 FROM @sql19;
        EXECUTE stmt19;
        DEALLOCATE PREPARE stmt19;

        SET @sql20 = 'DROP TEMPORARY TABLE tempUUIDs17;';
        PREPARE stmt20 FROM @sql20;
        EXECUTE stmt20;
        DEALLOCATE PREPARE stmt20;

-- For O_Prod_GraphUUID (OrderId and ProductId)
SET @sql21 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS tempUUIDs_O_Prod (
    OrderIdValue BIGINT,
    ProductIdValue BIGINT,
    generatedUUID BINARY(16),
    INDEX idx_composite (OrderIdValue, ProductIdValue)
) ENGINE=InnoDB;');
PREPARE stmt21 FROM @sql21;
EXECUTE stmt21;
DEALLOCATE PREPARE stmt21;

SET @sql22 = CONCAT('INSERT INTO tempUUIDs_O_Prod (OrderIdValue, ProductIdValue, generatedUUID)
    SELECT DISTINCT OrderId, ProductId, UNHEX(REPLACE(UUID(), \'-\', \'\')) 
    FROM ', target_table_name, ';');
PREPARE stmt22 FROM @sql22;
EXECUTE stmt22;
DEALLOCATE PREPARE stmt22;

SET @sql23 = CONCAT('UPDATE ', target_table_name, ' 
    JOIN tempUUIDs_O_Prod ON ', target_table_name, '.OrderId = tempUUIDs_O_Prod.OrderIdValue AND ', target_table_name, '.ProductId = tempUUIDs_O_Prod.ProductIdValue
    SET ', target_table_name, '.O_Prod_GraphUUID = tempUUIDs_O_Prod.generatedUUID;');
PREPARE stmt23 FROM @sql23;
EXECUTE stmt23;
DEALLOCATE PREPARE stmt23;

END";

  
  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_UpdateOrdersWithUnsold($sp_prefix, $sp_orders_table, $sp_products_table)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_UpdateOrdersWithUnsold(OUT warning_message VARCHAR(255))
SQL SECURITY INVOKER
proc_label: BEGIN

  -- Check if the orders table is empty
  IF (SELECT COUNT(*) FROM $sp_orders_table) = 0 THEN
      SET warning_message = CONCAT('Warning: ', '$sp_orders_table', ' is empty. The operation cannot proceed.');
      LEAVE proc_label;
  END IF;

  START TRANSACTION;

  -- INSERT Operation
  INSERT INTO $sp_orders_table 
  (OrderStatus, SKU, ProductId, ParentProductId, CategoryId, TagId, CurrentPrice, CurrentRegularPrice, Stock, ItemName, ParentItemName, Brand, CategoryName, TagName, ContentIntro, ContentIntroPlain, ContentCreated, ContentLastUpdated)
  SELECT 
  st.OrderStatus, st.SKU, st.ProductId, st.ParentProductId, st.CategoryId, st.TagId, 
  st.CurrentPrice, st.CurrentRegularPrice, st.Stock, st.ItemName, st.ParentItemName, 
  st.Brand, st.CategoryName, st.TagName, st.ContentIntro, st.ContentIntroPlain, 
  st.ContentCreated, st.ContentLastUpdated
  FROM $sp_products_table st
  LEFT JOIN $sp_orders_table atot ON atot.ProductId = st.ProductId
  WHERE atot.ProductId IS NULL;

  -- UPDATE Operation
  UPDATE $sp_orders_table atot
  JOIN $sp_products_table st
  ON atot.ProductId = st.ProductId
  SET 
  atot.PPIdGraphUUID = st.PPIdGraphUUID,
  atot.ProdIdGraphUUID = st.ProdIdGraphUUID;

  COMMIT;

  -- Set the success message
  SET warning_message = ', and included the products that have no sales';

END;";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_StripHTMLAndReplicateContentIntro($sp_prefix, $sp_orders_table)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_StripHTMLAndReplicateContentIntro()
  SQL SECURITY INVOKER
  BEGIN
  
    -- Declare variables
    DECLARE v_done INT DEFAULT 0;
    DECLARE v_ProductId INT;
    DECLARE v_ContentIntro TEXT;
  
    -- Declare cursor for unique ProductIds
    DECLARE cur_ProductIds CURSOR FOR 
        SELECT DISTINCT ProductId 
        FROM $sp_orders_table; 
  
    -- Declare handlers
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
  
    -- Open the cursor
    OPEN cur_ProductIds;
  
    -- Start the loop
    read_loop: LOOP
    
        FETCH cur_ProductIds INTO v_ProductId;
  
        IF v_done THEN
            LEAVE read_loop;
        END IF;
  
        -- Start a transaction
        START TRANSACTION;
  
        -- Update ContentIntro for the first occurrence based on OrderId for the current ProductId
        UPDATE $sp_orders_table 
        SET ContentIntro = AIEO_StripHTML(ContentIntro)
        WHERE ProductId = v_ProductId
        ORDER BY OrderId ASC
        LIMIT 1;
  
        -- Fetch the updated value
        SELECT ContentIntro INTO v_ContentIntro
        FROM $sp_orders_table
        WHERE ProductId = v_ProductId
        LIMIT 1;
  
        -- Copy the value to other instances with different OrderId
        UPDATE $sp_orders_table
        SET ContentIntro = v_ContentIntro
        WHERE ProductId = v_ProductId;
  
        -- Commit the transaction
        COMMIT;
  
    END LOOP;
  
    -- Close the cursor
    CLOSE cur_ProductIds;
  
  END;
";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_RenameTable($sp_prefix)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_RenameTable(IN oldTableName VARCHAR(255), IN newTableName VARCHAR(255))
  SQL SECURITY INVOKER
  BEGIN
        -- Start transaction
        START TRANSACTION;
    
        -- Drop the table with new name if it already exists
        SET @drop_query = CONCAT('DROP TABLE IF EXISTS ', newTableName);
        PREPARE drop_stmt FROM @drop_query;
        EXECUTE drop_stmt;
        DEALLOCATE PREPARE drop_stmt;
    
        -- Rename the old table to the new table name
        SET @rename_query = CONCAT('RENAME TABLE ', oldTableName , ' TO ', newTableName);
        PREPARE rename_stmt FROM @rename_query;
        EXECUTE rename_stmt;
        DEALLOCATE PREPARE rename_stmt;
    
        -- Commit the transaction to finalize changes
        COMMIT;
  END;
  ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_UpdateContentIntroPlain($sp_prefix)
{
  global $wpdb;

  $sql = "
    CREATE PROCEDURE " . $sp_prefix . "AIEO_UpdateContentIntroPlain (IN tableName VARCHAR(255))
    SQL SECURITY INVOKER
    BEGIN
        -- Single set-based UPDATE. The previous implementation cursored over
        -- every (ParentProductId, ContentIntro) pair and CONCAT'd the cleaned
        -- TEXT into a fresh dynamic UPDATE per row: any apostrophe in product
        -- copy broke the spliced statement (a certainty on Greek-language
        -- catalogs), NULL intros made @updateText NULL and failed the PREPARE,
        -- and splicing arbitrary product text into SQL is stored-SQLi-shaped.
        -- AIEO_StripHTMLAndReplicateContentIntro has already replicated
        -- ContentIntro uniformly across each product's rows by the time this
        -- runs, so the row-wise strip below is semantically identical — one
        -- pass, no temp table, no cursor, and product text never enters the
        -- SQL string (only the internal table NAME stays dynamic).
        SET @sqlText = CONCAT('UPDATE ', tableName, ' SET ContentIntroPlain = AIEO_StripHTML(ContentIntro)');
        PREPARE stmt FROM @sqlText;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END;
    ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_UpdateContentWordCount($sp_prefix)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_UpdateContentWordCount(
      IN _table_name VARCHAR(255))
    SQL SECURITY INVOKER
    BEGIN
        SET @query = CONCAT('UPDATE ', _table_name, ' SET ContentWordCount = AIEO_CountWords(ContentIntroPlain)');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END;
    ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_UpdateContentOutgoingLinks($sp_prefix)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_UpdateContentOutgoingLinks (
        IN _table_name VARCHAR(255))
      SQL SECURITY INVOKER
      BEGIN
          SET @query = CONCAT('UPDATE ', _table_name, ' SET ContentOutgoing = AIEO_CountLinks(ContentIntro)');
          PREPARE stmt FROM @query;
          EXECUTE stmt;
          DEALLOCATE PREPARE stmt;
      END;
      ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_FindDifferences($sp_prefix)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_FindDifferences(IN in_table_name VARCHAR(255))
    DETERMINISTIC READS SQL DATA
    SQL SECURITY INVOKER
    BEGIN
        -- Variables declaration
        DECLARE v_sql TEXT;
        DECLARE v_diff_part TEXT DEFAULT '';
        DECLARE v_word_count_parent INT DEFAULT 0;
        DECLARE v_word_count_item INT DEFAULT 0;
        DECLARE v_iter INT DEFAULT 1;
    
        -- Calculate word count of the ParentItemName for one record as an example.
        SET @v_sql = CONCAT('SELECT ROUND((CHAR_LENGTH(ParentItemName) - CHAR_LENGTH(REPLACE(ParentItemName, '' '', ''''))) / CHAR_LENGTH('' '') + 1) INTO @v_word_count_temp FROM ', in_table_name, ' LIMIT 1');
        PREPARE stmt1 FROM @v_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    
        -- Calculate word count of the ItemName for the same record.
        SET @v_sql = CONCAT('SELECT ROUND((CHAR_LENGTH(ItemName) - CHAR_LENGTH(REPLACE(ItemName, '' '', ''''))) / CHAR_LENGTH('' '') + 1) INTO @v_word_count_temp_item FROM ', in_table_name, ' LIMIT 1');
        PREPARE stmt2 FROM @v_sql;
        EXECUTE stmt2;
        DEALLOCATE PREPARE stmt2;
    
        SET v_word_count_parent = @v_word_count_temp;
        SET v_word_count_item = @v_word_count_temp_item;
    
        -- Construct the column of differing words from ParentItemName not in ItemName
        WHILE v_iter <= v_word_count_parent DO
            SET v_diff_part = CONCAT(v_diff_part, 'IF(ItemName NOT LIKE CONCAT(''%'', SUBSTRING_INDEX(SUBSTRING_INDEX(ParentItemName, '' '', ', v_iter, '), '' '', -1), ''%''), CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(ParentItemName, '' '', ', v_iter, '), '' '', -1), '' ''), '''')', ',');
            SET v_iter = v_iter + 1;
        END WHILE;
    
        SET v_iter = 1;
    
        -- Construct the column of differing words from ItemName not in ParentItemName
        WHILE v_iter <= v_word_count_item DO
            SET v_diff_part = CONCAT(v_diff_part, 'IF(ParentItemName NOT LIKE CONCAT(''%'', SUBSTRING_INDEX(SUBSTRING_INDEX(ItemName, '' '', ', v_iter, '), '' '', -1), ''%''), CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(ItemName, '' '', ', v_iter, '), '' '', -1), '' ''), '''')', ',');
            SET v_iter = v_iter + 1;
        END WHILE;
    
        -- Trim the last comma
        SET v_diff_part = LEFT(v_diff_part, CHAR_LENGTH(v_diff_part) - 1);
    
        -- Begin the construction of the dynamic SQL statement to update ItemNameDiff
        SET @v_sql = CONCAT('UPDATE ', in_table_name, ' SET ItemNameDiff = REPLACE(TRIM(CONCAT_WS('' '', ', v_diff_part, ')), ''  '', '' '')');
    
        -- Execute the dynamic SQL
        PREPARE dynamic_statement FROM @v_sql;
        EXECUTE dynamic_statement;
        DEALLOCATE PREPARE dynamic_statement;
    END;
    ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_Create_Product_Catalogue_HPOS($sp_prefix, $sp_products_table)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_Create_Product_Catalogue_HPOS(
    IN _price_sql_choice INT, 
    IN _text_sql_choice INT,
    IN _full_text_sql_choice INT
     
  ) SQL SECURITY INVOKER BEGIN 
  TRUNCATE TABLE $sp_products_table;
  INSERT INTO $sp_products_table (OrderId, OrderStatus, SKU, ParentSKU, ProductId, ParentProductId, CategoryId, TagId, Stock, OnSale, RoleBasedSpecialPricing, CurrentPrice, CurrentRegularPrice, ItemName, ParentItemName, ProfitabilityIndex, VendorId, Vendor, BrandId, Brand, BrandSlug, MenuId, 
  MenuCategory, HeadId, HeadCategory, SubcatId, SubCategory, Weight, Length, Width, Height, Size, Color, ImageUrl, ImageGalleryUrl, ContentIntro, ContentIntroPlain, ProductUrl, ERPId, CategoryName, TagName, Seasonality, ContentCreated, ContentLastUpdated, ProdIdGraphUUID, PPIdGraphUUID)
  SELECT 
      0 AS OrderId, 
      'unsold' AS OrderStatus,
      pv.sku AS SKU,
      pv.parent_sku AS ParentSKU,
      pv.product_id AS ProductId, 
      pv.parent_product_id AS ParentProductId, 
      pv.category_ids AS CategoryId, 
      pv.tag_ids AS TagId, 
      pv.stock AS Stock,
      pv.onsale AS OnSale,
      pv.role_special_pricing AS RoleBasedSpecialPricing,     
      (CASE 
        WHEN _price_sql_choice = 0 THEN '' 
        ELSE pv.price END
      ) AS CurrentPrice,
      (CASE 
      WHEN _price_sql_choice = 0 THEN '' 
      ELSE pv.regular_price END
      ) AS CurrentRegularPrice,
      (
        CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.product_name END
        ) AS ItemName, 
      (
        CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.parent_product_name END
        ) AS ParentItemName, 
      '1' AS ProfitabilityIndex, 
      pv.vendor_id AS VendorId,
      (CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.vendor END
      )   AS Vendor,
      pv.brand_id AS BrandId,
      (CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.brand END
      )   AS Brand, 
      (CASE 
      WHEN _text_sql_choice = 0 THEN '' 
      ELSE pv.brand_slug END) AS BrandSlug,   
      pv.menu_id AS MenuId,
      (CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.menu_category END
        ) AS MenuCategory,
      pv.head_id AS HeadId,
      ( CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.head_category END
        ) AS HeadCategory,
      pv.subcat_id AS SubcatId,
      (
        CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.sub_category END
        ) AS SubCategory, 
      pv.weight AS Weight,
      pv.length AS Length,
      pv.width AS Width,
      pv.height AS Height,
      pv.size AS Size,
      pv.color AS Color, 
      pv.image_url AS ImageUrl, 
      pv.image_gallery_urls AS ImageGalleryUrl, 
      (
        CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.product_description END
        ) AS ContentIntro,
        (
          CASE 
          WHEN _text_sql_choice = 0 THEN '' 
          ELSE  pv.product_description END
          ) AS ContentIntroPlain,
      pv.product_url AS ProductUrl,
      pv.erp_id AS ERPId,
      (
        CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.category_names END
        ) AS CategoryName, 
      (
        CASE 
        WHEN _text_sql_choice = 0 THEN '' 
        ELSE  pv.tag_names END
        ) AS TagName, 
      '0' AS Seasonality, 
      pv.content_created AS ContentCreated,
      pv.content_last_updated AS ContentLastUpdated,
      pv.ProdIdGraphUUID AS ProdIdGraphUUID, 
      pvp.PPIdGraphUUID AS PPIdGraphUUID
  FROM 
  ".$sp_prefix."aieo_core_product_vitals pv
  INNER JOIN 
  ".$sp_prefix."aieo_core_product_vitals pvp ON pv.parent_product_id = pvp.product_id;
      END;       
      ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_Export_Orders_HPOS($sp_prefix, $sp_orders_table)
{
  if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) { return; } 
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_Export_Orders_HPOS(
    IN _price_sql_choice INT, 
    IN _text_sql_choice INT, 
    IN _full_text_sql_choice INT,
    IN _eponymous_sql_choice INT, 
    IN _seasonality_sql_choice INT, 
    IN _order_id INT, 
    IN _max_records INT
  ) SQL SECURITY INVOKER BEGIN 
  TRUNCATE TABLE $sp_orders_table;
  INSERT INTO $sp_orders_table
  (
    OrderIdGraphUUID,
    OrderItemIdGraphUUID,
    PPIdGraphUUID,
    ProdIdGraphUUID,
    EpoIdGraphUUID,
    O_PP_GraphUUID,
    O_Prod_GraphUUID,
    O_Epo_GraphUUID,
    PP_Epo_GraphUUID,
    Prod_Epo_GraphUUID,
    OriginId,
    OrderId,
    OrderStatus,
    SKU,
    ParentSKU,
    ProductId,
    ParentProductId,
    CategoryId,
    TagId,
    OrderDate,
    PrimaryQty,
    ItemSequence,
    Stock,
    OrderSize,
    JourneyLinearWeight,
    AnonymousWeight,
    Price,
    OnSale,
    RoleBasedSpecialPricing,
    LineValue,
    OrderItemsTotal,
    ValueLinearWeight,
    CurrentPrice,
    CurrentRegularPrice,
    ItemName,
    ParentItemName,
    ItemNameDiff,
    ProfitabilityIndex,
    Vendor,
    VendorId,
    Brand,
    BrandId,
    BrandSlug,
    MenuId,
    MenuCategory,
    HeadId,
    HeadCategory,
    SubcatId,
    Subcategory,
    ContentIntro,
    ContentIntroPlain,
    ContentCreated,
    ContentLastUpdated,
    CategoryName,
    TagName,
    EponymousID,
    Seasonality,
    CustomerName,
    CustomerEmail,
    CustomerPhone,
    CustomerPostalCode
)
SELECT 
UNHEX(REPLACE(UUID(), '-', '')) AS OrderIdGraphUUID, 
UNHEX(MD5(CONCAT(opl.order_item_id))) AS OrderItemIdGraphUUID,
COALESCE(var_catalog.PPIdGraphUUID, simple_catalog.PPIdGraphUUID) AS PPIdGraphUUID,
COALESCE(var_catalog.ProdIdGraphUUID, simple_catalog.ProdIdGraphUUID) AS ProdIdGraphUUID,
UNHEX(MD5(CONCAT(opl.customer_id))) AS EpoIdGraphUUID,
UNHEX(MD5(CONCAT(COALESCE(var_catalog.ParentProductId, simple_catalog.ParentProductId), opl.order_id))) AS O_PP_GraphUUID,
UNHEX(MD5(CONCAT(COALESCE(var_catalog.ProductId, simple_catalog.ProductId), opl.order_id))) AS O_Prod_GraphUUID,
UNHEX(MD5(CONCAT(opl.order_id, opl.customer_id))) AS O_Epo_GraphUUID,
UNHEX(MD5(CONCAT(COALESCE(var_catalog.ParentProductId, simple_catalog.ParentProductId), opl.customer_id))) AS PP_Epo_GraphUUID,
UNHEX(MD5(CONCAT(COALESCE(var_catalog.ProductId, simple_catalog.ProductId), opl.customer_id))) AS Prod_Epo_GraphUUID,
'1' AS OriginId,
opl.order_id AS OrderId, 
os.status AS OrderStatus, 
COALESCE(var_catalog.SKU, simple_catalog.SKU) AS SKU,
COALESCE(var_catalog.ParentSKU, simple_catalog.ParentSKU) AS ParentSKU,
COALESCE(var_catalog.ProductId, simple_catalog.ProductId) AS ProductId,
COALESCE(var_catalog.ParentProductId, simple_catalog.ParentProductId) AS ParentProductId,
COALESCE(var_catalog.CategoryId, simple_catalog.CategoryId) AS CategoryId, 
COALESCE(var_catalog.TagId, simple_catalog.TagId) AS TagId, 
os.date_created AS OrderDate, 
opl.product_qty AS PrimaryQty, 
ROW_NUMBER() OVER (PARTITION BY opl.order_id ORDER BY opl.order_item_id) AS ItemSequence,
COALESCE(var_catalog.Stock, simple_catalog.Stock) AS Stock,
COUNT(opl.order_item_id) OVER (PARTITION BY opl.order_id) AS OrderSize,
CASE 
  WHEN COUNT(opl.order_item_id) OVER (PARTITION BY opl.order_id) = 0 THEN 0
  ELSE CAST((CAST(ROW_NUMBER() OVER (PARTITION BY opl.order_id ORDER BY opl.order_item_id) AS DECIMAL(10,6)) / COUNT(opl.order_item_id) OVER (PARTITION BY opl.order_id)) AS DECIMAL(10,6))
END AS JourneyLinearWeight,
'1' AS AnonymousWeight,

(CASE 
  WHEN opl.product_qty = 0 OR _price_sql_choice = 0 THEN 0
  ELSE CAST((opl.product_net_revenue / opl.product_qty) AS decimal(10,2))
END) AS Price,
COALESCE(var_catalog.OnSale, simple_catalog.OnSale) AS OnSale,
COALESCE(var_catalog.RoleBasedSpecialPricing, simple_catalog.RoleBasedSpecialPricing) AS RoleBasedSpecialPricing,
(CASE WHEN _price_sql_choice = 0 THEN ' ' ELSE opl.product_net_revenue END) AS LineValue,
os.net_total AS OrderItemsTotal,
CASE 
  WHEN os.net_total = 0 OR _price_sql_choice = 0 THEN 0
  ELSE CAST((opl.product_net_revenue / os.net_total) AS DECIMAL(10,6))
END AS ValueLinearWeight,
COALESCE(var_catalog.CurrentPrice, simple_catalog.CurrentPrice) AS CurrentPrice,
COALESCE(var_catalog.CurrentRegularPrice, simple_catalog.CurrentRegularPrice) AS CurrentRegularPrice,
COALESCE(var_catalog.ItemName, simple_catalog.ItemName) AS ItemName,
COALESCE(var_catalog.ParentItemName, simple_catalog.ParentItemName) AS ParentItemName,
'' AS ItemNameDiff,
'1' AS ProfitabilityIndex,
COALESCE(var_catalog.Vendor, simple_catalog.Vendor) AS Vendor,
COALESCE(var_catalog.VendorId, simple_catalog.VendorId) AS VendorId,
COALESCE(var_catalog.Brand, simple_catalog.Brand) AS Brand,
COALESCE(var_catalog.BrandId, simple_catalog.BrandId) AS BrandId,
COALESCE(var_catalog.BrandSlug, simple_catalog.BrandSlug) AS BrandSlug,
COALESCE(var_catalog.MenuId, simple_catalog.MenuId) AS MenuId,
COALESCE(var_catalog.MenuCategory, simple_catalog.MenuCategory) AS MenuCategory,
COALESCE(var_catalog.HeadId, simple_catalog.HeadId) AS HeadId,
COALESCE(var_catalog.HeadCategory, simple_catalog.HeadCategory) AS HeadCategory,
COALESCE(var_catalog.SubcatId, simple_catalog.SubcatId) AS SubcatId,
COALESCE(var_catalog.Subcategory, simple_catalog.Subcategory) AS Subcategory,
COALESCE(var_catalog.ContentIntro, simple_catalog.ContentIntro) AS ContentIntro,
COALESCE(var_catalog.ContentIntroPlain, simple_catalog.ContentIntroPlain) AS ContentIntroPlain,
COALESCE(var_catalog.ContentCreated, simple_catalog.ContentCreated) AS ContentCreated,
COALESCE(var_catalog.ContentLastUpdated, simple_catalog.ContentLastUpdated) AS ContentLastUpdated,
COALESCE(var_catalog.CategoryName, simple_catalog.CategoryName) AS CategoryName,
COALESCE(var_catalog.TagName, simple_catalog.TagName) AS TagName,
(CASE WHEN _eponymous_sql_choice = 0 THEN '' ELSE opl.customer_id END) AS EponymousID,
(CASE WHEN _seasonality_sql_choice = 0 THEN '' ELSE '1' END) AS Seasonality,
(CASE WHEN _eponymous_sql_choice = 0 THEN '' ELSE CONCAT(cust.first_name, ' ', cust.last_name) END) AS CustomerName,
(CASE WHEN _eponymous_sql_choice = 0 THEN '' ELSE cust.email END) AS CustomerEmail,
(CASE WHEN _eponymous_sql_choice = 0 THEN '' ELSE cust.phone END) AS CustomerPhone,
(CASE WHEN _eponymous_sql_choice = 0 THEN '' ELSE cust.postcode END) AS CustomerPostalCode
FROM ".$sp_prefix."wc_order_product_lookup opl
JOIN ".$sp_prefix."wc_order_stats os ON os.order_id = opl.order_id
JOIN ".$sp_prefix."wc_order_addresses cust ON cust.order_id = opl.order_id AND cust.address_type = 'billing'
LEFT JOIN ".$sp_prefix."aieo_core_reciprocal_products rprod
    ON rprod.old_product_id = opl.product_id
LEFT JOIN ".$sp_prefix."aieo_temp_product_catalogue simple_catalog
    ON simple_catalog.ProductId = COALESCE(rprod.product_id, opl.product_id) AND opl.variation_id = 0
LEFT JOIN ".$sp_prefix."aieo_temp_product_catalogue var_catalog
    ON var_catalog.ProductId = COALESCE(rprod.parent_product_id, opl.variation_id) AND opl.variation_id != 0
WHERE cust.email IS NOT NULL 
AND opl.order_id >= _order_id
-- AND (simple_catalog.ProductId IS NOT NULL OR var_catalog.ProductId IS NOT NULL)
LIMIT _max_records;  
      END;";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_Export_Store_Orders($sp_prefix, $sp_store_orders_table, $sp_erp_store_products_table_name, $sp_erp_store_orders_table_name, $sp_erp_store_customers_table_name)
{
  if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) { return; } 
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_Export_Store_Orders(
    IN _price_sql_choice INT, 
    IN _eponymous_sql_choice INT, 
    IN _seasonality_sql_choice INT,
    IN _days_past_sql_choice INT,
    IN _days_after_sql_choice INT,
    IN _instore_invoices_sql_choice INT,
    IN _instore_invoices_list_sql_choice VARCHAR(255),
    IN _instore_anonymous_sql_choice INT,
    IN _instore_anonymous_list_sql_choice VARCHAR(255)
  ) SQL SECURITY INVOKER BEGIN 

  TRUNCATE TABLE $sp_store_orders_table;
  
  INSERT INTO $sp_store_orders_table
  (
    OrderStatus,
    StoreCustomerID,
    LoyaltyId,
    CustomerName,
    CustomerEmail,
    CustomerPhone,
    CustomerPostalCode,
    CustomerStoreSince,
    CustomerLoyaltySince,
    LoyaltyPoints,
    LastTransactionDate,
    LastTransactionLocation,
    OrderId,
    OriginId,
    OriginType,
    OrderDate,
    EventLocation,
    LineValue,
    PrimaryQty,
    Price,
    OrderItemsTotal, 
    ValueLinearWeight,
    ProductId,
    ParentProductId,
    ItemName,
    ParentItemName,
    ProductURL,
    ImageURL,
    CurrentPrice,
    CurrentRegularPrice,
    RoleBasedSpecialPricing,
    Seasonality,
    Vendor,
    VendorId,
    Brand,
    BrandId,
    BrandSlug,
    CategoryId,
    CategoryName,
    TagId,
    TagName,
    MenuId,
    MenuCategory,
    HeadId,
    HeadCategory,
    SubcatId,
    SubCategory,
    Stock,
    Sku,
    ParentSku
    )
    SELECT 
    (CASE 
    WHEN o.Quantity < 0 THEN 'erp-returned'
    ELSE 'erp-completed'
END) AS OrderStatus,
        c.CustomerId as StoreCustomerID,
        c.LoyaltyId AS LoyaltyId,
        c.CustomerName AS CustomerName,
        c.CustomerEmail AS CustomerEmail,
        c.CustomerPhone AS CustomerPhone,
        c.CustomerPostalCode AS CustomerPostalCode,
        c.CustomerFirstTransaction AS CustomerStoreSince,
        c.CustomerLoyaltySince AS CustomerLoyaltySince,
        c.BonusPointsTotal AS LoyaltyPoints,
        c.CustomerLatestTransaction AS LastTransactionDate,
        c.CustomerLatestLocation AS LastTransactionLocation,
        o.OrderId,
        '2' as OriginId,
        o.InvoiceType as OriginType, 
        o.OrderDate AS OrderDate,
        o.Branch AS EventLocation,
        o.Line_price AS LineValue,
        o.Quantity AS PrimaryQty,
        (CASE 
            WHEN _price_sql_choice = 0 OR o.Quantity IS NULL OR o.Quantity <= 0 THEN 0
            ELSE CAST((o.Line_price / NULLIF(o.Quantity, 0)) AS DECIMAL(10,2))
        END) AS Price,
        (CASE 
            WHEN _price_sql_choice = 0 THEN 0 ELSE 
         CAST(SUM(CASE WHEN o.Line_price >= 0 THEN o.Line_price ELSE 0 END) OVER (PARTITION BY c.CustomerId, o.OrderId) AS DECIMAL(10,6)) END) AS OrderItemsTotal,
        (CASE 
            WHEN  _price_sql_choice = 0 OR o.Quantity IS NULL OR o.Quantity <= 0 THEN 0
            ELSE CAST((o.Line_price / NULLIF(SUM(CASE WHEN o.Line_price >= 0 THEN o.Line_price ELSE 0 END) OVER (PARTITION BY c.CustomerId, o.OrderId), 0)) AS DECIMAL(10,6))
        END) AS ValueLinearWeight,
        p.ProductId AS ProductId,
        p.ParentProductId AS ParentProductId,
        p.ItemName AS ItemName,
        p.ParentItemName AS ParentItemName,
        p.ProductURL AS ProductURL,
        p.ImageURL AS ImageURL,
        p.CurrentPrice AS CurrentPrice,
        p.CurrentRegularPrice AS CurrentRegularPrice,
        p.RoleBasedSpecialPricing AS RoleBasedSpecialPricing,
        _seasonality_sql_choice AS Seasonality,
        p.Vendor AS Vendor,
        p.VendorId AS VendorId,
        p.Brand AS Brand,
        p.BrandId AS BrandId,
        p.BrandSlug AS BrandSlug,
        p.CategoryId AS CategoryId,
        p.CategoryName AS CategoryName,
        p.TagId AS TagId,
        p.TagName AS TagName,
        p.MenuId AS MenuId,
        p.MenuCategory AS MenuCategory,
        p.HeadId AS HeadId,
        p.HeadCategory AS HeadCategory,
        p.SubcatId AS SubcatId,
        p.SubCategory AS SubCategory,
        p.Stock AS Stock,
        o.sku AS Sku, 
        p.ParentSku AS ParentSku
    FROM 
    ".$sp_erp_store_customers_table_name." c
    JOIN
    ".$sp_erp_store_orders_table_name." o ON c.Loyaltyid = o.LoyaltyId AND o.Line_price  > 0
    LEFT JOIN
    ".$sp_prefix."aieo_temp_product_catalogue p ON o.SKU = p.sku 
    WHERE
    (
      _seasonality_sql_choice <> 1 OR
      (
          (
              DATE_FORMAT(o.OrderDate, '%m-%d') >= DATE_FORMAT(DATE_SUB(UTC_DATE(), INTERVAL _days_past_sql_choice DAY), '%m-%d')
              AND DATE_FORMAT(o.OrderDate, '%m-%d') <= DATE_FORMAT(DATE_ADD(UTC_DATE(), INTERVAL _days_after_sql_choice DAY), '%m-%d')
          )
          OR
          (
              DATE_FORMAT(o.OrderDate, '%m-%d') >= DATE_FORMAT(DATE_SUB(CONCAT(YEAR(o.OrderDate),'-',MONTH(UTC_DATE()),'-',DAY(UTC_DATE())), INTERVAL _days_past_sql_choice DAY), '%m-%d')
              AND DATE_FORMAT(o.OrderDate, '%m-%d') <= DATE_FORMAT(DATE_ADD(CONCAT(YEAR(o.OrderDate),'-',MONTH(UTC_DATE()),'-',DAY(UTC_DATE())), INTERVAL _days_after_sql_choice DAY), '%m-%d')
         )
    )
    )
    AND
    (
        _instore_invoices_sql_choice <> 1 OR
        NOT FIND_IN_SET(o.InvoiceType, REPLACE(_instore_invoices_list_sql_choice, ' ', ''))
    );  
    
    

    
INSERT INTO $sp_store_orders_table
  (
    OrderStatus,
    StoreCustomerID,
    EponymousID,
    OrderId,
    OriginId,
    OriginType,
    OrderDate,
    EventLocation,
    LineValue,
    PrimaryQty,
    Price,
    OrderItemsTotal, 
    ValueLinearWeight,
    ProductId,
    ParentProductId,
    ItemName,
    ParentItemName,
    ProductURL,
    ImageURL,
    CurrentPrice,
    CurrentRegularPrice,
    RoleBasedSpecialPricing,
    Seasonality,
    Vendor,
    VendorId,
    Brand,
    BrandId,
    BrandSlug,
    CategoryId,
    CategoryName,
    TagId,
    TagName,
    MenuId,
    MenuCategory,
    HeadId,
    HeadCategory,
    SubcatId,
    SubCategory,
    Stock,
    Sku,
    ParentSku
    )
    SELECT 
        (CASE 
        WHEN o.Quantity < 0 THEN 'erp-returned'
        ELSE 'erp-completed'
        END) AS OrderStatus,
        o.CustomerId as StoreCustomerID,
        '0' as EponymousId,
        o.OrderId,
        '2' as OriginId,
        o.InvoiceType as OriginType, 
        o.OrderDate AS OrderDate,
        o.Branch AS EventLocation,
        o.Line_price AS LineValue,
        0 AS PrimaryQty,
        0 AS Price,
        0 AS OrderItemsTotal,
        0 AS ValueLinearWeight,
        p.ProductId AS ProductId,
        p.ParentProductId AS ParentProductId,
        p.ItemName AS ItemName,
        p.ParentItemName AS ParentItemName,
        p.ProductURL AS ProductURL,
        p.ImageURL AS ImageURL,
        p.CurrentPrice AS CurrentPrice,
        p.CurrentRegularPrice AS CurrentRegularPrice,
        p.RoleBasedSpecialPricing AS RoleBasedSpecialPricing,
        _seasonality_sql_choice AS Seasonality,
        p.Vendor AS Vendor,
        p.VendorId AS VendorId,
        p.Brand AS Brand,
        p.BrandId AS BrandId,
        p.BrandSlug AS BrandSlug,
        p.CategoryId AS CategoryId,
        p.CategoryName AS CategoryName,
        p.TagId AS TagId,
        p.TagName AS TagName,
        p.MenuId AS MenuId,
        p.MenuCategory AS MenuCategory,
        p.HeadId AS HeadId,
        p.HeadCategory AS HeadCategory,
        p.SubcatId AS SubcatId,
        p.SubCategory AS SubCategory,
        p.Stock AS Stock,
        o.sku AS Sku, 
        p.ParentSku AS ParentSku
    FROM
    ".$sp_erp_store_orders_table_name." o
    LEFT JOIN
    ".$sp_prefix."aieo_temp_product_catalogue p ON o.SKU = p.sku 
    WHERE
    (
      _seasonality_sql_choice <> 1 OR
      (
          (
              DATE_FORMAT(o.OrderDate, '%m-%d') >= DATE_FORMAT(DATE_SUB(UTC_DATE(), INTERVAL _days_past_sql_choice DAY), '%m-%d')
              AND DATE_FORMAT(o.OrderDate, '%m-%d') <= DATE_FORMAT(DATE_ADD(UTC_DATE(), INTERVAL _days_after_sql_choice DAY), '%m-%d')
          )
          OR
          (
              DATE_FORMAT(o.OrderDate, '%m-%d') >= DATE_FORMAT(DATE_SUB(CONCAT(YEAR(o.OrderDate),'-',MONTH(UTC_DATE()),'-',DAY(UTC_DATE())), INTERVAL _days_past_sql_choice DAY), '%m-%d')
              AND DATE_FORMAT(o.OrderDate, '%m-%d') <= DATE_FORMAT(DATE_ADD(CONCAT(YEAR(o.OrderDate),'-',MONTH(UTC_DATE()),'-',DAY(UTC_DATE())), INTERVAL _days_after_sql_choice DAY), '%m-%d')
         )
    )
    )
    AND (
      FIND_IN_SET(o.CustomerId, REPLACE(_instore_anonymous_list_sql_choice, ' ', ''))
      AND _instore_anonymous_sql_choice = 1 
  );
    END;";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_Merge_Store_Orders($sp_prefix)
{
  global $wpdb;

  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_Merge_Store_Orders (
    IN _store_orders_table_name VARCHAR(255),
    IN _temp_orders_table_name VARCHAR(255),
    IN _analytics_eponymous_id_field VARCHAR(255),
    IN instore_product_threshold_sql_choice INT,
    IN instore_product_threshold DECIMAL(10, 2),
    IN instore_products_list_sql_choice INT,
    IN instore_products_list TEXT
  )
  SQL SECURITY INVOKER
  BEGIN
    DECLARE query TEXT;
    DECLARE exclude_skus TEXT;

    -- Prepare the exclusion list for SKUs
    IF instore_products_list_sql_choice = 1 THEN
      SET exclude_skus = REPLACE(instore_products_list, ',', ''',''');
      SET exclude_skus = CONCAT('''', exclude_skus, '''');
    END IF;

    SET query = CONCAT('
      INSERT INTO ', _temp_orders_table_name, ' (
        OrderStatus,
        StoreCustomerID,
        LoyaltyId,
        CustomerName,
        CustomerEmail,
        CustomerPhone,
        CustomerPostalCode,
        CustomerStoreSince,
        CustomerLoyaltySince,
        LoyaltyPoints,
        LastTransactionDate,
        LastTransactionLocation,
        OrderId,
        OriginId,
        OriginType,
        OrderDate,
        EventLocation,
        LineValue,
        PrimaryQty,
        Price,
        OrderItemsTotal, 
        ValueLinearWeight,
        ProductId,
        ParentProductId,
        ItemName,
        ParentItemName,
        ProductURL,
        ImageURL,
        CurrentPrice,
        CurrentRegularPrice,
        RoleBasedSpecialPricing,
        Seasonality,
        Vendor,
        VendorId,
        Brand,
        BrandId,
        BrandSlug,
        CategoryId,
        CategoryName,
        TagId,
        TagName,
        MenuId,
        MenuCategory,
        HeadId,
        HeadCategory,
        SubcatId,
        SubCategory,
        Stock,
        Sku,
        ParentSku,
        EponymousId
      )
      SELECT 
        OrderStatus,
        StoreCustomerID,
        LoyaltyId,
        CustomerName,
        CustomerEmail,
        CustomerPhone,
        CustomerPostalCode,
        CustomerStoreSince,
        CustomerLoyaltySince,
        LoyaltyPoints,
        LastTransactionDate,
        LastTransactionLocation,
        OrderId,
        OriginId,
        OriginType,
        OrderDate,
        EventLocation,
        LineValue,
        PrimaryQty,
        Price,
        OrderItemsTotal, 
        ValueLinearWeight,
        ProductId,
        ParentProductId,
        ItemName,
        ParentItemName,
        ProductURL,
        ImageURL,
        CurrentPrice,
        CurrentRegularPrice,
        RoleBasedSpecialPricing,
        Seasonality,
        Vendor,
        VendorId,
        Brand,
        BrandId,
        BrandSlug,
        CategoryId,
        CategoryName,
        TagId,
        TagName,
        MenuId,
        MenuCategory,
        HeadId,
        HeadCategory,
        SubcatId,
        SubCategory,
        Stock,
        Sku,
        ParentSku,
        ', _analytics_eponymous_id_field, ' AS EponymousId
      FROM ', _store_orders_table_name,
      ' WHERE 1 = 1');

    -- Add condition for product threshold
    IF instore_product_threshold_sql_choice = 1 THEN
      SET query = CONCAT(query, ' AND LineValue > ', instore_product_threshold);
    END IF;
    
    -- Add condition to exclude specific SKUs
    IF instore_products_list_sql_choice = 1 THEN
      SET query = CONCAT(query, ' AND Sku NOT IN (', exclude_skus, ')');
    END IF;

    -- Execute the dynamic SQL statement
    SET @sql = query;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  
  END;
  ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_Export_Orders_non_HPOS($sp_prefix, $sp_orders_table)
{
  if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) { return; } 
  global $wpdb;
  
  
  
  $sql = "CREATE PROCEDURE " . $sp_prefix . "AIEO_Export_Orders_non_HPOS(
        IN _price_sql_choice INT, 
        IN _text_sql_choice INT, 
        IN _full_text_sql_choice INT,
        IN _eponymous_sql_choice INT, 
        IN _seasonality_sql_choice INT, 
        IN _order_id INT, 
        IN _max_records INT
  ) SQL SECURITY INVOKER BEGIN 
  TRUNCATE TABLE $sp_orders_table;
  INSERT INTO $sp_orders_table
(OrderId, OrderStatus, SKU,  ProductId, ParentProductId, CategoryId, TagId, OrderDate, PrimaryQty, ItemSequence, Stock, OrderSize, 
JourneyLinearWeight, AnonymousWeight, Price, LineValue, OrderItemsTotal, ValueLinearWeight, CurrentPrice, CurrentRegularPrice, ItemName,  ParentItemName, ItemNameDiff, 
ProfitabilityIndex, Brand, ContentIntro, ContentIntroPlain, ContentCreated, ContentLastUpdated, 
CategoryName, TagName, EponymousID, Seasonality)

SELECT  o.id as OrderId,
o.post_status as OrderStatus,
case 
when _text_sql_choice = 0
then ''
else COALESCE(psku.meta_value, '')
end as SKU,
COALESCE(case 
when oimpv.meta_value = 0
then oimp.meta_value
else oimpv.meta_value
end, '') as ProductId,
COALESCE(oimp.meta_value, '') as ParentProductId,
COALESCE(pc.category_ids, '') as CategoryId,
COALESCE(pt.tag_ids, '') as TagId,
o.post_date_gmt as OrderDate,
COALESCE(CONVERT(oimq.meta_value, decimal(3, 0)), '') as PrimaryQty,
ROW_NUMBER() over (
partition by o.id order by oi.order_item_id
) as ItemSequence,
COALESCE(pstock.meta_value, '') as Stock,
os.OrderSize,
ROW_NUMBER() over (
partition by o.id order by oi.order_item_id
) / os.OrderSize as JourneyLinearWeight,
(
case 
when _eponymous_sql_choice = 0
then '0'
else '1'
end
) as AnonymousWeight,
(
case 
when _price_sql_choice = 0
then ''
else convert(oims.meta_value / oimq.meta_value, decimal(5, 2))
end
) as Price,
(
case 
when _price_sql_choice = 0
then ''
else convert(oims.meta_value, decimal(7, 2))
end
) as LineValue,
( CASE WHEN _price_sql_choice = 0 THEN '' ELSE convert(pmot.meta_value , 
decimal(7, 2)) END ) as OrderItemsTotal,
(
case 
when _price_sql_choice = 0
then ''
else (
case 
when pmot.meta_value = 0
or pmot.meta_value is null
then '0'
else convert(oims.meta_value / pmot.meta_value, decimal(7, 2))
end
)
end
) as ValueLinearWeight,
(
    CASE 
      WHEN _price_sql_choice = 0 THEN '' 
      ELSE (
        CASE 
          WHEN pm_price.meta_value REGEXP '^[0-9.]+$' THEN CONVERT(pm_price.meta_value, DECIMAL(7, 2)) 
          ELSE NULL 
        END 
      ) 
  END
  ) AS CurrentPrice,
  
  (
    CASE 
      WHEN _price_sql_choice = 0 THEN '' 
      ELSE (
        CASE 
          WHEN pm_regular_price.meta_value REGEXP '^[0-9.]+$' THEN CONVERT(pm_regular_price.meta_value, DECIMAL(7, 2)) 
          ELSE NULL 
        END
      ) 
  END
) AS CurrentRegularPrice,
(
case 
when _text_sql_choice = 0
then ''
else oi.order_item_name
end
) as ItemName,
(
case 
when _text_sql_choice = 0
then ''
else (
case 
when oimpv.meta_value = 0
then oi.order_item_name
else pmp.post_title
end
)
end
) as ParentItemName,
'' as ItemNameDiff,

  '1' as ProfitabilityIndex,
'' as Brand,
(
case 
when _full_text_sql_choice = 0
then ''
else (
case 
when oimpv.meta_value = 0
then pmp.post_content
else (
select parent.post_content
from wp_posts parent
where parent.ID = oimp.meta_value
)
end
)
end
) as ContentIntro,
(
    case 
    when _full_text_sql_choice = 0
    then ''
    else (
    case 
    when oimpv.meta_value = 0
    then pmp.post_content
    else (
    select parent.post_content
    from wp_posts parent
    where parent.ID = oimp.meta_value
    )
    end
    )
    end
    ) as ContentIntroPlain,
COALESCE(pmp.post_date_gmt, '2000-01-01 00:00:00') as ContentCreated,
COALESCE(pmp.post_modified_gmt, '2000-01-01 00:00:00') as ContentLastUpdated,
(
case 
when _text_sql_choice = 0
then ''
else COALESCE(pc.category_names, '')
end
) as CategoryName,

(
case 
when _text_sql_choice = 0
then ''
else COALESCE(pt.tag_names, '')
end
) as TagName,
(
case 
when _eponymous_sql_choice = 0
then '0'
else pmu.meta_value
end
) as EponymousID,
(
case 
when _seasonality_sql_choice = 0
then '0'
else '1'
end
) as Seasonality
FROM wp_woocommerce_order_items oi

INNER JOIN wp_posts o
ON o.ID = oi.order_id
AND o.post_type = 'shop_order'

INNER JOIN wp_woocommerce_order_itemmeta oimp
ON oi.order_item_id = oimp.order_item_id
AND oimp.meta_key = '_product_id'

LEFT JOIN wp_woocommerce_order_itemmeta oimpv
ON oi.order_item_id = oimpv.order_item_id
AND oimpv.meta_key = '_variation_id'

LEFT JOIN wp_woocommerce_order_itemmeta oimq
ON oi.order_item_id = oimq.order_item_id
AND oimq.meta_key = '_qty'

LEFT JOIN wp_woocommerce_order_itemmeta oims
ON oi.order_item_id = oims.order_item_id
AND oims.meta_key = '_line_subtotal'

INNER JOIN wp_postmeta as pmu
ON pmu.post_id = o.ID
AND pmu.meta_key = '_customer_user'

INNER JOIN wp_postmeta as pmot
ON pmot.post_id = o.ID
AND pmot.meta_key = '_order_total'

LEFT JOIN wp_posts pmp
ON pmp.ID = oimp.meta_value

LEFT JOIN wp_postmeta as psku
ON psku.post_id = CASE 
    WHEN oimpv.meta_value != '0' THEN oimpv.meta_value
    ELSE pmp.ID
END
AND psku.meta_key = '_sku'
LEFT JOIN wp_postmeta AS pm_price 
ON (
    CASE 
        WHEN oimpv.meta_value != '0' THEN oimpv.meta_value  
        ELSE oimp.meta_value                               
    END
) = pm_price.post_id 
AND pm_price.meta_key = '_price'

LEFT JOIN wp_postmeta AS pm_regular_price 
ON (
    CASE 
        WHEN oimpv.meta_value IS NOT NULL AND oimpv.meta_value != '0' THEN oimpv.meta_value
        ELSE oimp.meta_value
    END
) = pm_regular_price.post_id 
AND pm_regular_price.meta_key = '_regular_price'

LEFT JOIN wp_postmeta pstock
ON (
    CASE 
        WHEN oimpv.meta_value != '0' THEN oimpv.meta_value  
        ELSE oimp.meta_value                               
    END
) = pstock.post_id 
AND pstock.meta_key = '_stock'

left join (
select oi.order_id,
COUNT(distinct oimp.meta_value) as OrderSize
from wp_woocommerce_order_items oi
inner join wp_woocommerce_order_itemmeta oimp
on oi.order_item_id = oimp.order_item_id
and oimp.meta_key = '_product_id'
group by oi.order_id
) as os
on o.ID = os.order_id
left join (
select tr.object_id as product_id,
GROUP_CONCAT(t.term_id order by t.term_id asc) as category_ids,
GROUP_CONCAT(t.name order by t.term_id asc) as category_names -- Include category names
from wp_term_relationships tr
inner join wp_term_taxonomy tt
on tr.term_taxonomy_id = tt.term_taxonomy_id
and tt.taxonomy = 'product_cat'
inner join wp_terms t
on tt.term_id = t.term_id
group by tr.object_id
) as pc
on pc.product_id = CAST(oimp.meta_value AS UNSIGNED)
left join (
select tr.object_id as product_id,
GROUP_CONCAT(t.term_id order by t.term_id asc) as tag_ids,
GROUP_CONCAT(t.name order by t.term_id asc) as tag_names
from wp_term_relationships tr
inner join wp_term_taxonomy tt
on tr.term_taxonomy_id = tt.term_taxonomy_id
and tt.taxonomy = 'product_tag'
inner join wp_terms t
on tt.term_id = t.term_id
group by tr.object_id
) as pt
on pt.product_id = CAST(oimp.meta_value AS UNSIGNED)
where oi.order_item_type = 'line_item'
and oi.order_id >= _order_id
LIMIT _max_records;

END;      
    ";

  $wpdb->query($sql);
}

function aieo_create_sp_AIEO_InsertCoreProductAttributes($sp_prefix, $sp_core_product_attributes_table_name)
{
    global $wpdb;

    $procedure_name = $sp_prefix . "AIEO_InsertCoreProductAttributes";

    
    
    $tax = function_exists('aieo_column_charset_collate') ? aieo_column_charset_collate($sp_prefix . 'term_taxonomy', 'taxonomy') : '';

    $sql = "CREATE PROCEDURE $procedure_name(
                IN vendorTaxonomy VARCHAR(255) $tax,
                IN brandTaxonomy VARCHAR(255) $tax,
                IN sizeTaxonomies VARCHAR(255) $tax,
                IN colorTaxonomies VARCHAR(255) $tax
            )
            SQL SECURITY INVOKER
            BEGIN
                -- Simplified brand query - populate all products with brands if available
                -- Uses subquery to ensure ONE brand per product
                TRUNCATE TABLE $sp_core_product_attributes_table_name;

                INSERT INTO $sp_core_product_attributes_table_name
                  (name, product_id, parent_product_id, effective_product_id,
                   brand_id, brand_name,
                   product_stock, product_count,
                   supplier_id, supplier,
                   brand_bias, campaigns)
                SELECT
                    COALESCE(brands.brand_name, 'No Brand') AS name,
                    p.ID AS product_id,
                    CASE WHEN p.post_parent = 0 THEN p.ID ELSE p.post_parent END AS parent_product_id,
                    CASE WHEN p.post_parent = 0 THEN p.ID ELSE p.post_parent END AS effective_product_id,
                    COALESCE(brands.brand_id, 0) AS brand_id,
                    COALESCE(brands.brand_slug, 'no-brand') AS brand_name,
                    '0' AS product_stock,
                    '0' AS product_count,
                    NULL AS supplier_id,
                    NULL AS supplier,
                    NULL AS brand_bias,
                    '' AS campaigns
                FROM " . $sp_prefix . "posts p
                LEFT JOIN (
                    SELECT
                        tr.object_id AS product_id,
                        t.name AS brand_name,
                        t.term_id AS brand_id,
                        t.slug AS brand_slug
                    FROM " . $sp_prefix . "term_relationships tr
                    INNER JOIN " . $sp_prefix . "term_taxonomy x
                        ON x.term_taxonomy_id = tr.term_taxonomy_id
                        AND x.taxonomy = brandTaxonomy
                    INNER JOIN " . $sp_prefix . "terms t
                        ON t.term_id = x.term_id
                    GROUP BY tr.object_id
                ) AS brands ON brands.product_id = p.ID
                -- Statuses matched to core_product_vitals, which filters on
                -- post_type alone and so carries draft and private rows too.
                -- Filtering to publish here meant a product retired by a
                -- duplicate merge kept its vitals row but lost its BRAND, and
                -- with it the brand attribution of every past sale it made.
                -- Measured 2026-08-17: 1368 analytics rows on drafted products,
                -- brand populated on zero of them, against 113581 published
                -- rows with brand on all of them. Trash and auto-draft stay out.
                WHERE p.post_type IN ('product', 'product_variation')
                  AND p.post_status IN ('publish', 'draft', 'private')
                LIMIT 5000000;
            END";

    aieo_drop_sp_AIEO_InsertCoreProductAttributes($sp_prefix);
    $wpdb->query($sql);
}

function aieo_create_sp_AIEO_InsertProductCategoryHierarchy($sp_prefix, $sp_core_product_category_hierarchy_table_name)
{
  if (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) { return; } 
    global $wpdb;

    $procedure_name = $sp_prefix . "AIEO_InsertProductCategoryHierarchy";

    $sql = "CREATE PROCEDURE $procedure_name()
            SQL SECURITY INVOKER
            BEGIN
                -- Optimized hierarchy logic from generateproductcathierarchy.sql
                TRUNCATE TABLE $sp_core_product_category_hierarchy_table_name;

                INSERT INTO $sp_core_product_category_hierarchy_table_name
                (
                  name,
                  product_id,
                  top_category_id,
                  top_category,
                  second_level_category_id,
                  second_level_category,
                  third_level_category_id,
                  third_level_category,
                  fourth_level_category_id,
                  fourth_level_category,
                  fifth_level_category_id,
                  fifth_level_category,
                  reference_quantity,
                  season_start,
                  season_end,
                  season_midpoint,
                  campaigns,
                  category_ids,
                  google_product_category,
                  facebook_product_category
                )
                SELECT
                  p.ID AS name,
                  p.ID AS product_id,

                  tc.term_id  AS top_category_id,
                  tc.name     AS top_category,

                  sc.term_id  AS second_level_category_id,
                  sc.name     AS second_level_category,

                  thc.term_id AS third_level_category_id,
                  thc.name    AS third_level_category,

                  frc.term_id AS fourth_level_category_id,
                  frc.name    AS fourth_level_category,

                  ffc.term_id AS fifth_level_category_id,
                  ffc.name    AS fifth_level_category,

                  -- Season and campaign fields (not available without Pods framework)
                  NULL AS reference_quantity,
                  NULL AS season_start,
                  NULL AS season_end,
                  NULL AS season_midpoint,
                  NULL AS campaigns,

                  -- Gather all category IDs in a comma-delimited list
                  (
                    SELECT GROUP_CONCAT(DISTINCT tt2.term_id ORDER BY tt2.term_id SEPARATOR ',')
                    FROM " . $sp_prefix . "term_relationships tr2
                    JOIN " . $sp_prefix . "term_taxonomy tt2
                         ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
                    WHERE tr2.object_id = p.ID
                      AND tt2.taxonomy = 'product_cat'
                  ) AS category_ids,

                  -- Google Product Category — leaf-first COALESCE across the
                  -- 5-level category chain. Each level LEFT-JOINs to
                  -- wp_aieo_dmm_category_extras (admin-authored WC-cat →
                  -- Google mapping). The most-specific (deepest) mapping wins;
                  -- if a leaf isn't mapped, inherit from its parent, then
                  -- grandparent, all the way up to the top category.
                  -- NULLIF guards against empty-string saves from the picker.
                  COALESCE(
                    NULLIF(gce_5.google_category_path, ''),
                    NULLIF(gce_4.google_category_path, ''),
                    NULLIF(gce_3.google_category_path, ''),
                    NULLIF(gce_2.google_category_path, ''),
                    NULLIF(gce_1.google_category_path, '')
                  ) AS google_product_category,

                  -- Facebook Marketplace Product Category — identical
                  -- resolution against the facebook_category_path column.
                  COALESCE(
                    NULLIF(fce_5.facebook_category_path, ''),
                    NULLIF(fce_4.facebook_category_path, ''),
                    NULLIF(fce_3.facebook_category_path, ''),
                    NULLIF(fce_2.facebook_category_path, ''),
                    NULLIF(fce_1.facebook_category_path, '')
                  ) AS facebook_product_category

                FROM " . $sp_prefix . "posts p

                -- Each level subquery walks UP the term-taxonomy chain via
                -- explicit self-joins instead of deeply-nested IN subqueries.
                -- The original nested-IN form (matching the canonical
                -- generateproductcathierarchy.sql) triggers a MariaDB 10.6
                -- optimizer pathology: when the deepest LEFT JOIN's inner
                -- subquery returns 0 rows, the planner mis-treats the LEFT
                -- JOIN as INNER and the whole hierarchy collapses to a
                -- single row. Chained JOINs avoid the bug entirely and
                -- are also faster + more readable.
                --
                -- Per-product tie-break stays MIN(term_id) ASC (matches
                -- the canonical script's behaviour).

                -- First (top) level — product directly tagged with parent=0 cat
                JOIN (
                    SELECT ranked.product_id, ranked.term_id, t.name
                    FROM (
                        SELECT tr.object_id AS product_id, tt.term_id,
                               ROW_NUMBER() OVER (PARTITION BY tr.object_id ORDER BY tt.term_id) AS rn
                        FROM " . $sp_prefix . "term_relationships tr
                        JOIN " . $sp_prefix . "term_taxonomy tt
                             ON tr.term_taxonomy_id = tt.term_taxonomy_id
                            AND tt.taxonomy = 'product_cat'
                            AND tt.parent = 0
                    ) ranked
                    JOIN " . $sp_prefix . "terms t ON t.term_id = ranked.term_id
                    WHERE ranked.rn = 1
                ) AS tc
                  ON p.ID = tc.product_id

                -- Second level — leaf whose parent is parent=0
                LEFT JOIN (
                    SELECT ranked.product_id, ranked.term_id, t.name
                    FROM (
                        SELECT tr.object_id AS product_id, tt.term_id,
                               ROW_NUMBER() OVER (PARTITION BY tr.object_id ORDER BY tt.term_id) AS rn
                        FROM " . $sp_prefix . "term_relationships tr
                        JOIN " . $sp_prefix . "term_taxonomy tt
                             ON tr.term_taxonomy_id = tt.term_taxonomy_id
                            AND tt.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p1
                             ON p1.term_id = tt.parent
                            AND p1.taxonomy = 'product_cat'
                            AND p1.parent = 0
                    ) ranked
                    JOIN " . $sp_prefix . "terms t ON t.term_id = ranked.term_id
                    WHERE ranked.rn = 1
                ) AS sc
                  ON p.ID = sc.product_id

                -- Third level — leaf whose grandparent is parent=0
                LEFT JOIN (
                    SELECT ranked.product_id, ranked.term_id, t.name
                    FROM (
                        SELECT tr.object_id AS product_id, tt.term_id,
                               ROW_NUMBER() OVER (PARTITION BY tr.object_id ORDER BY tt.term_id) AS rn
                        FROM " . $sp_prefix . "term_relationships tr
                        JOIN " . $sp_prefix . "term_taxonomy tt
                             ON tr.term_taxonomy_id = tt.term_taxonomy_id
                            AND tt.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p1
                             ON p1.term_id = tt.parent
                            AND p1.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p2
                             ON p2.term_id = p1.parent
                            AND p2.taxonomy = 'product_cat'
                            AND p2.parent = 0
                    ) ranked
                    JOIN " . $sp_prefix . "terms t ON t.term_id = ranked.term_id
                    WHERE ranked.rn = 1
                ) AS thc
                  ON p.ID = thc.product_id

                -- Fourth level
                LEFT JOIN (
                    SELECT ranked.product_id, ranked.term_id, t.name
                    FROM (
                        SELECT tr.object_id AS product_id, tt.term_id,
                               ROW_NUMBER() OVER (PARTITION BY tr.object_id ORDER BY tt.term_id) AS rn
                        FROM " . $sp_prefix . "term_relationships tr
                        JOIN " . $sp_prefix . "term_taxonomy tt
                             ON tr.term_taxonomy_id = tt.term_taxonomy_id
                            AND tt.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p1
                             ON p1.term_id = tt.parent
                            AND p1.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p2
                             ON p2.term_id = p1.parent
                            AND p2.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p3
                             ON p3.term_id = p2.parent
                            AND p3.taxonomy = 'product_cat'
                            AND p3.parent = 0
                    ) ranked
                    JOIN " . $sp_prefix . "terms t ON t.term_id = ranked.term_id
                    WHERE ranked.rn = 1
                ) AS frc
                  ON p.ID = frc.product_id

                -- Fifth level
                LEFT JOIN (
                    SELECT ranked.product_id, ranked.term_id, t.name
                    FROM (
                        SELECT tr.object_id AS product_id, tt.term_id,
                               ROW_NUMBER() OVER (PARTITION BY tr.object_id ORDER BY tt.term_id) AS rn
                        FROM " . $sp_prefix . "term_relationships tr
                        JOIN " . $sp_prefix . "term_taxonomy tt
                             ON tr.term_taxonomy_id = tt.term_taxonomy_id
                            AND tt.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p1
                             ON p1.term_id = tt.parent
                            AND p1.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p2
                             ON p2.term_id = p1.parent
                            AND p2.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p3
                             ON p3.term_id = p2.parent
                            AND p3.taxonomy = 'product_cat'
                        JOIN " . $sp_prefix . "term_taxonomy p4
                             ON p4.term_id = p3.parent
                            AND p4.taxonomy = 'product_cat'
                            AND p4.parent = 0
                    ) ranked
                    JOIN " . $sp_prefix . "terms t ON t.term_id = ranked.term_id
                    WHERE ranked.rn = 1
                ) AS ffc
                  ON p.ID = ffc.product_id

                -- Marketplace-taxonomy mappings per WC-category. Two LEFT
                -- JOINs per breadcrumb level — one each for Google +
                -- Facebook category_extras — so the COALESCE above can
                -- inherit the leaf-most mapping up the category chain.
                -- Joins resolve on the term_id at each level; if a level
                -- isn't reached for this product (e.g. only 3 levels deep)
                -- the joined row is simply absent → NULL → COALESCE skips it.
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras gce_1
                  ON gce_1.taxonomy = 'product_cat' AND gce_1.term_id = tc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras gce_2
                  ON gce_2.taxonomy = 'product_cat' AND gce_2.term_id = sc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras gce_3
                  ON gce_3.taxonomy = 'product_cat' AND gce_3.term_id = thc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras gce_4
                  ON gce_4.taxonomy = 'product_cat' AND gce_4.term_id = frc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras gce_5
                  ON gce_5.taxonomy = 'product_cat' AND gce_5.term_id = ffc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras fce_1
                  ON fce_1.taxonomy = 'product_cat' AND fce_1.term_id = tc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras fce_2
                  ON fce_2.taxonomy = 'product_cat' AND fce_2.term_id = sc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras fce_3
                  ON fce_3.taxonomy = 'product_cat' AND fce_3.term_id = thc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras fce_4
                  ON fce_4.taxonomy = 'product_cat' AND fce_4.term_id = frc.term_id
                LEFT JOIN " . $sp_prefix . "aieo_dmm_category_extras fce_5
                  ON fce_5.taxonomy = 'product_cat' AND fce_5.term_id = ffc.term_id

                -- Same widening as InsertCoreProductAttributes above, and for
                -- the same reason: a retired product that keeps its vitals row
                -- but loses its CATEGORY takes the category attribution of its
                -- past sales with it. Matched to vitals; trash stays out.
                WHERE
                    p.post_type IN ('product', 'product_variation')
                    AND p.post_status IN ('publish', 'draft', 'private')

                GROUP BY p.ID
                ORDER BY p.ID;
            END";

    aieo_drop_sp_AIEO_InsertProductCategoryHierarchy($sp_prefix);
    $wpdb->query($sql);
}

function aieo_create_sp_AIEO_InsertProductVitals($sp_prefix, $sp_core_product_vitals_table_name, $domain_name)
{
    global $wpdb;

    
    $sp_core_product_attributes_table_name = get_option('aieo_core_product_attributes_table');
    $sp_core_product_category_hierarchy_table_name = get_option('aieo_core_product_category_hierarchy_table');

    $procedure_name = $sp_prefix . "AIEO_InsertProductVitals";

    $sql = "CREATE PROCEDURE $procedure_name()
            SQL SECURITY INVOKER
            BEGIN
                -- Optimized logic from generatefastfinderv2.sql (parents only)
                TRUNCATE TABLE $sp_core_product_vitals_table_name;

                INSERT INTO $sp_core_product_vitals_table_name (
                    `image_url`,
                    `name`,
                    `product_id`,
                    `parent_id`,
                    `parent_product_id`,
                    `category_ids`,
                    `product_creation_date`,
                    `product_age`,
                    `product_first_sale_date`,
                    `product_latest_sale_date`,
                    `parent_name`,
                    `parent_sku`,
                    `category_id`,
                    `brand_id`,
                    `brand`,
                    `supplier_id`,
                    `supplier`,
                    `menu_id`,
                    `menu_category`,
                    `head_id`,
                    `head_category`,
                    `subcat_id`,
                    `sub_category`,
                    `reference_quantity`,
                    `brand_bias`,
                    `campaigns`,
                    `season_start`,
                    `season_end`,
                    `season_midpoint`,
                    `terminated_product`,
                    `replacement_product`,
                    `sku`,
                    `gtin`,
                    `soft1_id`,
                    `profdiscount`,
                    `price`,
                    `regular_price`,
                    `cost`,
                    `onsale`,
                    `status`,
                    `product_url`,
                    `link_to_view`,
                    `link_to_cart`,
                    `short_description`,
                    `weight`,
                    `length`,
                    `width`,
                    `height`,
                    `color`,
                    `stock`,
                    `google_product_category`,
                    `facebook_product_category`
                )
                SELECT
                    CONCAT('/wp-content/uploads/', pm2.meta_value) AS image_url, -- B65: host-relative; readers rehost
                    CAST(p.post_title AS CHAR) AS name,
                    p.ID AS product_id,
                    p.ID AS parent_id,
                    p.ID AS parent_product_id,
                    tapc.category_ids AS category_ids,
                    CAST(COALESCE(p.post_date, '2022-01-01 00:00:00') AS DATETIME) AS product_creation_date,
                    DATEDIFF(UTC_DATE(), p.post_date) AS product_age,
                    CAST(COALESCE(MIN(o.date_created_gmt), NULL) AS DATETIME) AS product_first_sale_date,
                    CAST(COALESCE(MAX(o.date_created_gmt), NULL) AS DATETIME) AS product_latest_sale_date,
                    CAST(p.post_title AS CHAR) AS parent_name,
                    CAST(pm4.meta_value AS CHAR) AS parent_sku,
                    tapc.top_category_id AS category_id,
                    tat.brand_id AS brand_id,
                    CAST(tat.name AS CHAR) AS brand,
                    NULL AS supplier_id,  -- Supplier logic removed
                    NULL AS supplier,
                    tapc.top_category_id AS menu_id,
                    CAST(tapc.top_category AS CHAR) AS menu_category,
                    tapc.second_level_category_id AS head_id,
                    CAST(tapc.second_level_category AS CHAR) AS head_category,
                    tapc.third_level_category_id AS subcat_id,
                    CAST(tapc.third_level_category AS CHAR) AS sub_category,
                    COALESCE(CAST(NULLIF(pm11.meta_value, '') AS DECIMAL(12,0)), tapc.reference_quantity) AS reference_quantity,
                    IFNULL(tat.brand_bias, '0.5') AS brand_bias,
                    CONCAT_WS(', ',
                      NULLIF(GROUP_CONCAT(DISTINCT CAST(NULLIF(tat.campaigns,  '') AS CHAR) SEPARATOR ', '), ''),
                      NULLIF(GROUP_CONCAT(DISTINCT CAST(NULLIF(tapc.campaigns, '') AS CHAR) SEPARATOR ', '), '')
                    ) AS campaigns,
                    tapc.season_start AS season_start,
                    tapc.season_end AS season_end,
                    tapc.season_midpoint AS season_midpoint,
                    pm7.meta_value AS terminated_product,
                    pm8.meta_value AS replacement_product,
                    CAST(pm4.meta_value AS CHAR) AS sku,
                    CAST(pm0.meta_value AS CHAR) AS gtin,
                    CAST(pm9.meta_value AS CHAR) AS soft1_id,
                    CASE WHEN pm6.meta_value IS NOT NULL THEN 'NAI' ELSE 'OXI' END AS ProfDiscount,
                    CAST(REPLACE(COALESCE(NULLIF(pm3.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) AS price,
                    CAST(REPLACE(COALESCE(NULLIF(pm13.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) AS regular_price,
                    CAST(REPLACE(COALESCE(NULLIF(pm12.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) AS cost,
                    CASE
                        WHEN CAST(REPLACE(COALESCE(NULLIF(pm13.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) > CAST(REPLACE(COALESCE(NULLIF(pm3.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2))
                        THEN 1
                        ELSE 0
                    END AS onsale,
                    CASE WHEN p.post_status = 'publish' THEN 1 ELSE 0 END AS status,
                    CONCAT('$domain_name/?p=', p.ID) AS product_url,
                    CONCAT('/?p=', p.ID) AS link_to_view, -- B65: a URL, not rendered HTML with a baked host + Greek label
                    CASE
                      WHEN pm5.meta_value > 0 THEN CONCAT(
                        '<div class=\"loop-button-wrap button-layout2\">',
                        '<a href=\"?add-to-cart=', p.ID,
                        '\" data-quantity=\"1\" class=\"button product_type_simple add_to_cart_button ajax_add_to_cart\" ',
                        'data-product_id=\"', p.ID, '\" data-product_sku=\"', CAST(pm4.meta_value AS CHAR),
                        '\" rel=\"nofollow\">',
                        'Αγορά',
                        '</a></div>'
                      )
                      ELSE ''
                    END AS link_to_cart,
                    LEFT(p.post_excerpt, 160) AS short_description,
                    CAST(NULLIF(pm10.meta_value, '') AS DECIMAL(10,2)) AS weight,
                    CAST(NULLIF(pm14.meta_value, '') AS DECIMAL(10,2)) AS length,
                    CAST(NULLIF(pm15.meta_value, '') AS DECIMAL(10,2)) AS width,
                    CAST(NULLIF(pm16.meta_value, '') AS DECIMAL(10,2)) AS height,
                    '' AS color,
                    CAST(NULLIF(pm5.meta_value, '') AS SIGNED) AS stock,
                    tapc.google_product_category   AS google_product_category,
                    tapc.facebook_product_category AS facebook_product_category
                FROM " . $sp_prefix . "posts AS p
                LEFT JOIN $sp_core_product_category_hierarchy_table_name AS tapc
                       ON p.ID = tapc.product_id
                LEFT JOIN " . $sp_prefix . "postmeta AS pm0
                       ON p.ID = pm0.post_id AND pm0.meta_key = '_global_unique_id'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm1
                       ON p.ID = pm1.post_id AND pm1.meta_key = '_thumbnail_id'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm2
                       ON pm1.meta_value = pm2.post_id AND pm2.meta_key = '_wp_attached_file'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm3
                       ON p.ID = pm3.post_id AND pm3.meta_key = '_price'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm4
                       ON p.ID = pm4.post_id AND pm4.meta_key = '_sku'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm5
                       ON p.ID = pm5.post_id AND pm5.meta_key = '_stock'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm6
                       ON p.ID = pm6.post_id AND pm6.meta_key = 'woocommerce_ultimate_pricing_prices' AND pm6.meta_value LIKE '%customer_%'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm7
                       ON p.ID = pm7.post_id AND pm7.meta_key = 'terminated_product'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm8
                       ON p.ID = pm8.post_id AND pm8.meta_key = 'replaced_by'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm9
                       ON p.ID = pm9.post_id AND pm9.meta_key = 'soft1_id'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm10
                       ON p.ID = pm10.post_id AND pm10.meta_key = '_weight'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm11
                       ON p.ID = pm11.post_id AND pm11.meta_key = 'reference_quantity'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm12
                       ON p.ID = pm12.post_id AND pm12.meta_key = '_cogs_total_value'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm13
                       ON p.ID = pm13.post_id AND pm13.meta_key = '_regular_price'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm14
                       ON p.ID = pm14.post_id AND pm14.meta_key = '_length'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm15
                       ON p.ID = pm15.post_id AND pm15.meta_key = '_width'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm16
                       ON p.ID = pm16.post_id AND pm16.meta_key = '_height'
                LEFT JOIN $sp_core_product_attributes_table_name AS tat
                       ON p.ID = tat.product_id
                LEFT JOIN " . $sp_prefix . "wc_order_product_lookup AS opl
                       ON p.ID = opl.product_id
                LEFT JOIN " . $sp_prefix . "wc_orders AS o
                       ON opl.order_id = o.id
                WHERE p.post_type = 'product'
                GROUP BY p.ID
                ORDER BY p.ID, tapc.top_category
                LIMIT 5000000;
            END";

    aieo_drop_sp_AIEO_InsertProductVitals($sp_prefix);
    $wpdb->query($sql);
}

function aieo_create_sp_AIEO_InsertVariationVitals($sp_prefix, $sp_core_variation_vitals_table_name)
{
    global $wpdb;

    $procedure_name = $sp_prefix . "AIEO_InsertVariationVitals";

    $sql = "CREATE PROCEDURE $procedure_name()
            SQL SECURITY INVOKER
            BEGIN
                -- Simplified variation vitals logic WITHOUT slow terms JOINs
                -- This version removes cartesian explosion from terms/taxonomy joins
                TRUNCATE TABLE $sp_core_variation_vitals_table_name;

                -- Pre-resolve variation_id -> colour ONCE into a keyed temp table.
                -- Same flag-driven walk (postmeta attribute_% -> colour-flag table
                -- -> term NAME) that used to run as a correlated scalar subquery
                -- per variation row (854s / 595 calls). MIN(t_clr.name) reproduces
                -- the old ORDER BY t_clr.name LIMIT 1 exactly, and GROUP BY post_id
                -- guarantees one row per variation (no fan-out on the join below).
                DROP TEMPORARY TABLE IF EXISTS tmp_variation_colour;
                CREATE TEMPORARY TABLE tmp_variation_colour (
                    post_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
                    color   VARCHAR(200) CHARACTER SET utf8mb4 NULL
                ) ENGINE=InnoDB;

                INSERT INTO tmp_variation_colour (post_id, color)
                SELECT
                    pm_clr.post_id,
                    MIN(t_clr.name)
                FROM " . $sp_prefix . "postmeta AS pm_clr
                INNER JOIN " . $sp_prefix . "aieo_dmm_color_attributes AS ca_clr
                    ON ca_clr.taxonomy = SUBSTRING(pm_clr.meta_key, CHAR_LENGTH('attribute_') + 1)
                INNER JOIN " . $sp_prefix . "term_taxonomy AS tt_clr
                    ON tt_clr.taxonomy = ca_clr.taxonomy
                INNER JOIN " . $sp_prefix . "terms AS t_clr
                    ON t_clr.term_id = tt_clr.term_id
                   AND t_clr.slug = pm_clr.meta_value
                WHERE pm_clr.meta_key LIKE 'attribute_%'
                  AND pm_clr.meta_value <> ''
                GROUP BY pm_clr.post_id;

                INSERT INTO $sp_core_variation_vitals_table_name (
                  name,
                  product_id,
                  parent_id,
                  product_creation_date,
                  product_age,
                  product_first_sale_date,
                  product_latest_sale_date,
                  sku,
                  gtin,
                  price,
                  stock,
                  special_price,
                  weight,
                  length,
                  width,
                  height,
                  size,
                  color,
                  imagepath,
                  soft1_id,
                  erp_id,
                  regular_price,
                  onsale,
                  role_special_pricing,
                  variation_attribute_description,
                  variation_swatch,
                  variation_selector,
                  cost,
                  content_created,
                  content_last_updated,
                  ProdIdGraphUUID
                )
                SELECT
                    p.post_title AS name,
                    p.ID AS product_id,
                    p.post_parent AS parent_id,
                    CAST(COALESCE(p.post_date, '2022-01-01 00:00:00') AS DATETIME) AS product_creation_date,
                    DATEDIFF(UTC_DATE(), p.post_date) AS product_age,
                    NULL AS product_first_sale_date,
                    NULL AS product_latest_sale_date,
                    CAST(pm_sku.meta_value AS CHAR) AS sku,
                    CAST(pm_gtin.meta_value AS CHAR) AS gtin,
                    CAST(REPLACE(COALESCE(NULLIF(pm_price.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) AS price,
                    CAST(NULLIF(pm_stock.meta_value, '') AS SIGNED) AS stock,
                    CASE WHEN pm_special.meta_value IS NOT NULL THEN 'NAI' ELSE 'OXI' END AS special_price,
                    CAST(NULLIF(COALESCE(pm_weight.meta_value, parent_pm_weight.meta_value, ''), '') AS DECIMAL(10,2)) AS weight,
                    CAST(NULLIF(pm_length.meta_value, '') AS DECIMAL(10,2)) AS length,
                    CAST(NULLIF(pm_width.meta_value, '') AS DECIMAL(10,2)) AS width,
                    CAST(NULLIF(pm_height.meta_value, '') AS DECIMAL(10,2)) AS height,
                    pm_size.meta_value AS size,
                    -- color: resolved from the variation's COLOUR-FLAGGED attribute
                    -- (operator ticks 'AIEO colour context' on the attribute; the flag
                    -- lives in {$sp_prefix}aieo_dmm_color_attributes). We take the term
                    -- NAME (e.g. 'French Caramel'), NOT the slug, and only for flagged
                    -- attributes — so femme's pa_alezori-* image swatches now yield a
                    -- real colour. Pre-resolved ONCE into tmp_variation_colour above
                    -- (keyed LEFT JOIN => exactly one value per variation, no row
                    -- fan-out). Empty when the variation has no
                    -- flagged colour attribute (flag-only, per operator: 'only real
                    -- colours'). This supersedes the old attribute_pa_%color% slug grab
                    -- (which matched nothing here) and the combine title-strip heuristic.
                    vc.color AS color,
                    -- imagepath precedence walks both WC variation-image fields:
                    --   1. _product_image_gallery (FIRST attachment id) — the WC 10.9
                    --      canonical key that AIEO migrated TO from
                    --      botiga_variation_gallery on 2026-05-23.
                    --   2. _thumbnail_id — the legacy / still-standard variation
                    --      featured-image key, used for the 8,458 variations not
                    --      yet picked up by the WC 10.9 migration.
                    -- The original SQL joined `_wp_attached_file` directly on the
                    -- variation post (always NULL — variations aren't attachments),
                    -- which is why every variation fell through to the 40x40 swatch
                    -- chip in the feed.
                    COALESCE(NULLIF(pm_pig_file.meta_value, ''), NULLIF(pm_thumb_file.meta_value, '')) AS imagepath,
                    CAST(pm_soft1.meta_value AS CHAR) AS soft1_id,
                    CAST(pm_soft1.meta_value AS CHAR) AS erp_id,
                    CAST(REPLACE(COALESCE(NULLIF(pm_regular.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) AS regular_price,
                    CASE
                        WHEN CAST(REPLACE(COALESCE(NULLIF(pm_regular.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) >
                             CAST(REPLACE(COALESCE(NULLIF(pm_price.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2))
                        THEN 1
                        ELSE 0
                    END AS onsale,
                    CASE WHEN pm_special.meta_value IS NOT NULL THEN 1 ELSE 0 END AS role_special_pricing,
                    '' AS variation_attribute_description,
                    '' AS variation_swatch,
                    0 AS variation_selector,
                    CAST(REPLACE(COALESCE(NULLIF(pm_cost.meta_value, ''), '0'), ',', '.') AS DECIMAL(12,2)) AS cost,
                    p.post_date_gmt AS content_created,
                    p.post_modified_gmt AS content_last_updated,
                    UNHEX(REPLACE(UUID(), '-', '')) AS ProdIdGraphUUID
                FROM " . $sp_prefix . "posts AS p
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_sku ON p.ID = pm_sku.post_id AND pm_sku.meta_key = '_sku'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_gtin ON p.ID = pm_gtin.post_id AND pm_gtin.meta_key = '_global_unique_id'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_price ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_stock ON p.ID = pm_stock.post_id AND pm_stock.meta_key = '_stock'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_weight ON p.ID = pm_weight.post_id AND pm_weight.meta_key = '_weight'
                LEFT JOIN " . $sp_prefix . "postmeta AS parent_pm_weight ON p.post_parent = parent_pm_weight.post_id AND parent_pm_weight.meta_key = '_weight'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_special ON p.ID = pm_special.post_id
                    AND pm_special.meta_key = 'woocommerce_ultimate_pricing_prices'
                    AND pm_special.meta_value LIKE '%customer_%'
                -- Variation image — two-hop via either the new WC 10.9 key
                -- (_product_image_gallery → first attachment ID) OR the legacy
                -- _thumbnail_id. Both lookups then resolve to _wp_attached_file
                -- on the attachment post. See the COALESCE in the SELECT above.
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_pig       ON pm_pig.post_id      = p.ID AND pm_pig.meta_key      = '_product_image_gallery'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_pig_file  ON pm_pig_file.post_id  = CAST(SUBSTRING_INDEX(NULLIF(pm_pig.meta_value, ''), ',', 1) AS UNSIGNED) AND pm_pig_file.meta_key  = '_wp_attached_file'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_thumb_id  ON pm_thumb_id.post_id  = p.ID AND pm_thumb_id.meta_key  = '_thumbnail_id'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_thumb_file ON pm_thumb_file.post_id = CAST(NULLIF(pm_thumb_id.meta_value, '') AS UNSIGNED) AND pm_thumb_file.meta_key = '_wp_attached_file'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_soft1 ON p.ID = pm_soft1.post_id AND pm_soft1.meta_key = 'iconic_cffv_100545_soft1_id'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_regular ON p.ID = pm_regular.post_id AND pm_regular.meta_key = '_regular_price'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_length ON p.ID = pm_length.post_id AND pm_length.meta_key = '_length'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_width ON p.ID = pm_width.post_id AND pm_width.meta_key = '_width'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_height ON p.ID = pm_height.post_id AND pm_height.meta_key = '_height'
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_size ON p.ID = pm_size.post_id AND pm_size.meta_key LIKE 'attribute_pa_%size%'
                -- (pm_color join removed: colour is now the flag-driven term-NAME
                --  lookup pre-resolved into tmp_variation_colour above, not an
                --  attribute_pa_%color% slug grab.)
                LEFT JOIN tmp_variation_colour AS vc ON vc.post_id = p.ID
                LEFT JOIN " . $sp_prefix . "postmeta AS pm_cost ON p.ID = pm_cost.post_id AND pm_cost.meta_key = '_cogs_total_value'
                WHERE p.post_type = 'product_variation'
                AND p.post_status = 'publish';

                DROP TEMPORARY TABLE IF EXISTS tmp_variation_colour;
            END";

    aieo_drop_sp_AIEO_InsertVariationVitals($sp_prefix);
    $wpdb->query($sql);
}

function aieo_create_sp_AIEO_CombineVariationVitalsInProductVitals($sp_prefix, $sp_core_product_vitals_table_name, $domain_name)
{
    global $wpdb;

    
    $sp_core_product_attributes_table_name = get_option('aieo_core_product_attributes_table');
    $sp_core_product_category_hierarchy_table_name = get_option('aieo_core_product_category_hierarchy_table');
    $sp_core_variation_vitals_table_name = get_option('aieo_core_variation_vitals_table');

    $procedure_name = $sp_prefix . "AIEO_CombineVariationVitalsInProductVitals";

    $sql = "CREATE PROCEDURE $procedure_name()
            SQL SECURITY INVOKER
            BEGIN
                -- Insert variations into product_vitals
                INSERT INTO $sp_core_product_vitals_table_name (
                    `image_url`,
                    `name`,
                    `product_id`,
                    `parent_id`,
                    `parent_product_id`,
                    `product_creation_date`,
                    `product_age`,
                    `product_first_sale_date`,
                    `product_latest_sale_date`,
                    `parent_name`,
                    `parent_sku`,
                    `category_id`,
                    `brand`,
                    `supplier_id`,
                    `supplier`,
                    `menu_id`,
                    `menu_category`,
                    `head_id`,
                    `head_category`,
                    `subcat_id`,
                    `sub_category`,
                    `reference_quantity`,
                    `brand_bias`,
                    `campaigns`,
                    `season_start`,
                    `season_end`,
                    `season_midpoint`,
                    `sku`,
                    `gtin`,
                    `soft1_id`,
                    `profdiscount`,
                    `price`,
                    `cost`,
                    `product_url`,
                    `link_to_view`,
                    `link_to_cart`,
                    `short_description`,
                    `weight`,
                    `color`,
                    `stock`,
                    `status`,
                    variation_selector,
                    variation_attribute_description,
                    variation_swatch,
                    `google_product_category`,
                    `facebook_product_category`
                )
                SELECT DISTINCT
                  -- Variation image precedence for feed exports:
                  --   1. variation's own _wp_attached_file (rare on variation posts)
                  --   2. PARENT product's featured image (full-size product photo —
                  --      this is the right fallback for the vast majority of variations
                  --      that don't carry their own image; e.g. Dalon Body Milk shades
                  --      all share the same bottle photo). Added 2026-05-26 to fix
                  --      feed pollution with 40x40 swatch chips.
                  --   3/4. swatch-map images (LAST resort — these are 40x40 chips, only
                  --      acceptable for marketplaces when the parent has no image at all).
                  -- Marketplaces (Skroutz/Bestprice/etc.) won't index a row with empty
                  -- <image>, so this fallback chain still gets every variation into
                  -- the feed — just with a SHOPPABLE-sized image now, not a swatch chip.
                  -- B65: HOST-RELATIVE, like the parent branch above. The host
                  -- was baked in from get_site_url() AT SPROC-INSTALL TIME, so
                  -- every variation row carried whichever hostname happened to
                  -- install the procedures and stayed wrong after any move
                  -- (8,161 of 13,613 rows on ffnew when this was found — the
                  -- s25 fix reached the simple-product branch only). Readers
                  -- rehost: the admin tables via the `rehost` image format, the
                  -- marketplace feeds via AIEO_DMM_Marketplace_Feeds::absolutise().
                  COALESCE(
                    NULLIF(CONCAT('/wp-content/uploads/', vv.imagepath), '/wp-content/uploads/'),
                    NULLIF(CONCAT('/wp-content/uploads/', parent_thumb.meta_value), '/wp-content/uploads/'),
                    NULLIF(svm.image_url, ''),
                    NULLIF(svm.swatch_image_url, '')
                  ) AS image_url,
                  CAST(p.post_title AS CHAR) AS name,
                  p.ID AS product_id,
                  vv.parent_id AS parent_id,
                  vv.parent_id AS parent_product_id,
                  CAST(COALESCE(vv.product_creation_date, '2022-01-01 00:00:00') AS DATETIME) AS product_creation_date,
                  vv.product_age AS product_age,
                  CAST(COALESCE(MAX(vv.product_first_sale_date),  NULL) AS DATETIME) AS product_first_sale_date,
                  CAST(COALESCE(MAX(vv.product_latest_sale_date), NULL) AS DATETIME) AS product_latest_sale_date,
                  CAST(pmmpp.post_title AS CHAR) AS parent_name,
                  CAST(pmmpps.meta_value AS CHAR) AS parent_sku,
                  tapcv.top_category_id AS category_id,
                  CAST(tatv.name AS CHAR) AS brand,
                  NULL AS supplier_id,  -- Supplier logic removed
                  NULL AS supplier,
                  tapcv.top_category_id AS menu_id,
                  CAST(tapcv.top_category AS CHAR) AS menu_category,
                  tapcv.second_level_category_id AS head_id,
                  CAST(tapcv.second_level_category AS CHAR) AS head_category,
                  tapcv.third_level_category_id AS subcat_id,
                  CAST(tapcv.third_level_category AS CHAR) AS sub_category,
                  tapcv.reference_quantity AS reference_quantity,
                  IFNULL(tatv.brand_bias, '0.5') AS brand_bias,
                  CONCAT_WS(', ',
                    NULLIF(GROUP_CONCAT(DISTINCT CAST(NULLIF(tatv.campaigns,  '') AS CHAR) SEPARATOR ', '), ''),
                    NULLIF(GROUP_CONCAT(DISTINCT CAST(NULLIF(tapcv.campaigns, '') AS CHAR) SEPARATOR ', '), '')
                  ) AS campaigns,
                  tapcv.season_start AS season_start,
                  tapcv.season_end AS season_end,
                  tapcv.season_midpoint AS season_midpoint,
                  CAST(vv.sku AS CHAR) AS sku,
                  CAST(vv.gtin AS CHAR) AS gtin,
                  CAST(vv.soft1_id AS CHAR) AS soft1_id,
                  vv.special_price AS ProfDiscount,
                  CAST(REPLACE(vv.price, ',', '.') AS DECIMAL(12,2)) AS price,
                  CAST(REPLACE(vv.cost,  ',', '.') AS DECIMAL(12,2)) AS cost,
                  CONCAT('$domain_name/?p=',  p.post_parent) AS product_url,
                  CONCAT('/?p=', vv.parent_id) AS link_to_view, -- B65: a URL, not rendered HTML with a baked host + Greek label
                  CASE WHEN p.post_parent <> 0 AND vv.stock > 0 THEN
                    CONCAT(
                      '<div class=\"loop-button-wrap button-layout2\"><a href=\"?add-to-cart=', p.id,
                      '\" data-quantity=\"1\" class=\"button product_type_simple add_to_cart_button ajax_add_to_cart\" ',
                      'data-product_id=\"', p.id, '\" data-product_sku=\"', CAST(vv.sku AS CHAR), '\" rel=\"nofollow\">',
                      'Αγορά',
                      '</a></div>'
                    )
                    ELSE ''
                  END AS link_to_cart,
                  LEFT(pmmpp.post_excerpt, 160) AS short_description,
                  vv.weight AS weight,
                  -- colour now comes straight from variation_vitals (the flag-driven
                  -- term NAME populated by AIEO_InsertVariationVitals), NOT the old
                  -- title-minus-parent strip. Empty when the variation has no
                  -- COLOUR-flagged attribute. Parents keep NULL (Google puts colour on
                  -- each variant + variesBy:color on the group, never on the parent).
                  vv.color AS color,
                  vv.stock AS stock,
                  CASE WHEN pmmpp.post_status = 'publish' THEN 1 ELSE 0 END AS status,
                  vv.variation_selector AS variation_selector,
                  vv.variation_attribute_description AS variation_attribute_description,
                  vv.variation_swatch AS variation_swatch,
                  -- Variations inherit the parent's marketplace-taxonomy
                  -- mapping from wp_aieo_core_product_category_hierarchy
                  -- (joined as tapcv, keyed on post_parent above). Same
                  -- COALESCE chain InsertProductVitals already resolved.
                  tapcv.google_product_category   AS google_product_category,
                  tapcv.facebook_product_category AS facebook_product_category
                FROM `" . $sp_prefix . "posts` AS p
                LEFT JOIN " . $sp_prefix . "posts pmmp                 ON pmmp.ID            = p.id
                LEFT JOIN " . $sp_prefix . "posts pmmpp                ON pmmpp.ID           = pmmp.post_parent
                LEFT JOIN `" . $sp_prefix . "postmeta` pmmpps          ON pmmpp.ID           = pmmpps.post_id AND pmmpps.meta_key = '_sku'
                LEFT JOIN `$sp_core_variation_vitals_table_name` vv ON p.id               = vv.product_id
                LEFT JOIN `$sp_core_product_attributes_table_name`  tatv ON p.ID              = tatv.product_id
                LEFT JOIN `$sp_core_product_category_hierarchy_table_name` tapcv ON p.post_parent = tapcv.product_id
                LEFT JOIN `" . $sp_prefix . "aieo_dmm_swatches_variation_map` svm ON svm.variation_id = p.ID
                -- Parent product's featured image — two-hop postmeta walk:
                -- variation's parent → _thumbnail_id → attachment → _wp_attached_file.
                -- Used by the COALESCE image precedence above as the fallback when
                -- the variation has no own imagepath, so we get the FULL product photo
                -- in the feed instead of a 40x40 swatch chip.
                LEFT JOIN `" . $sp_prefix . "postmeta` parent_thumb_id ON parent_thumb_id.post_id = p.post_parent AND parent_thumb_id.meta_key = '_thumbnail_id'
                LEFT JOIN `" . $sp_prefix . "postmeta` parent_thumb    ON parent_thumb.post_id    = CAST(NULLIF(parent_thumb_id.meta_value, '') AS UNSIGNED) AND parent_thumb.meta_key    = '_wp_attached_file'
                WHERE p.post_type IN ('product_variation')
                  AND p.post_status = 'publish'
                GROUP BY p.ID
                ORDER BY p.id, tapcv.top_category;

                -- Fresh optimizer stats on the just-rebuilt vitals table
                -- (InsertProductVitals TRUNCATEs it, the bulk INSERT above
                -- repopulates it) BEFORE the childSum/childIdx self-join passes
                -- below, which are otherwise planned against stale near-empty
                -- table statistics. ANALYZE inside a proc is an established
                -- pattern here (see analytics job procs); the extra CALL result
                -- set it emits is drained by wpdb flush.
                ANALYZE TABLE $sp_core_product_vitals_table_name;

                -- Mark parents that have at least one variation
                UPDATE $sp_core_product_vitals_table_name p
                LEFT JOIN (
                  SELECT DISTINCT parent_id
                  FROM $sp_core_variation_vitals_table_name
                  WHERE parent_id IS NOT NULL AND parent_id <> 0
                ) v ON v.parent_id = p.product_id
                SET p.is_variation_parent = CASE WHEN v.parent_id IS NULL THEN 0 ELSE 1 END
                WHERE p.parent_id = p.product_id;

                -- NOTE: the former first childSum pass (parent stock +
                -- variation_count) that lived here was DELETED as redundant:
                -- it computed the identical childSum derived table as the
                -- in_stock_variation_offset pass further below, which overwrites
                -- BOTH columns it set, and no statement in between reads stock
                -- or variation_count. childSum aggregates only variation rows
                -- (parent_id <> product_id), which that pass never wrote, so the
                -- surviving pass produces byte-identical final values.

                -- Point product_url at the SEO canonical when one exists, ABSOLUTISED
                -- with the site origin. The materialised canonical_url is stored as a
                -- portable site-RELATIVE path (/product/...); product_url is an ABSOLUTE
                -- URL by contract (the ?p=ID seed above is absolute), and the marketplace
                -- feeds + any external consumer need a full link. Mirror the sitemap's
                -- rule (build_urlset): already-absolute canonicals (operator overrides)
                -- pass through unchanged, relative ones get the $domain_name prefix.
                -- INNER JOIN + the WHERE leave rows WITHOUT a canonical (or whose FINAL
                -- absolute URL exceeds 300) untouched, so they keep their ?p=ID reference.
                -- LEFT(...,300) is a final hard cap so a stray long URL can never overflow
                -- product_url and fail this build. Variations inherit via parent_product_id.
                UPDATE $sp_core_product_vitals_table_name v
                INNER JOIN " . $sp_prefix . "aieo_seo_posts sp
                        ON sp.object_type = 'product'
                       AND sp.object_id   = v.parent_product_id
                SET v.product_url = LEFT(
                        CASE
                            WHEN sp.canonical_url LIKE 'http://%' OR sp.canonical_url LIKE 'https://%'
                                THEN sp.canonical_url
                            ELSE CONCAT('$domain_name', '/', TRIM(LEADING '/' FROM sp.canonical_url))
                        END, 300)
                WHERE sp.canonical_url IS NOT NULL
                  AND sp.canonical_url <> ''
                  AND CHAR_LENGTH(
                        CASE
                            WHEN sp.canonical_url LIKE 'http://%' OR sp.canonical_url LIKE 'https://%'
                                THEN sp.canonical_url
                            ELSE CONCAT('$domain_name', '/', TRIM(LEADING '/' FROM sp.canonical_url))
                        END
                      ) <= 300;

                -- Set swatches offset and in_stock_variation_offset
                SET @swatches_offset = 5;

                UPDATE $sp_core_product_vitals_table_name AS parent
                JOIN (
                    SELECT
                        parent_id,
                        SUM(stock) AS sum_variation_stock,
                        SUM(CASE WHEN stock > 0 THEN 1 ELSE 0 END) AS variations_in_stock
                    FROM $sp_core_product_vitals_table_name
                    WHERE parent_id <> product_id
                    GROUP BY parent_id
                ) AS childSum
                    ON parent.product_id = childSum.parent_id
                SET
                    parent.stock = childSum.sum_variation_stock,
                    parent.variation_count = childSum.variations_in_stock,
                    parent.in_stock_variation_offset = CASE
                        WHEN childSum.variations_in_stock > @swatches_offset
                            THEN CONCAT('+', childSum.variations_in_stock - @swatches_offset)
                        ELSE ''
                    END;

                -- Per-parent flat index of every variation's SKU / barcode(gtin),
                -- so the storefront reads ONE flat vitals row instead of joining
                -- or querying WooCommerce per request. Computed once here in the
                -- (hourly) build — replaces the legacy pods sku_index/barcode_index
                -- that generatefastfinderv2 used to build.
                SET SESSION group_concat_max_len = 1000000;
                UPDATE $sp_core_product_vitals_table_name AS parent
                JOIN (
                    SELECT
                        parent_id,
                        GROUP_CONCAT(NULLIF(sku, '')  ORDER BY product_id SEPARATOR ',') AS sku_idx,
                        GROUP_CONCAT(NULLIF(gtin, '') ORDER BY product_id SEPARATOR ',') AS barcode_idx
                    FROM $sp_core_product_vitals_table_name
                    WHERE parent_id <> product_id
                    GROUP BY parent_id
                ) AS childIdx
                    ON parent.product_id = childIdx.parent_id
                SET
                    parent.sku_index     = childIdx.sku_idx,
                    parent.barcode_index = childIdx.barcode_idx;

                -- The supplier per product, by the preorder chain's own
                -- precedence: direct `supplier` term on the parent -> ERP
                -- SKU map by the row's own SKU -> the brand's supplier.
                -- supplier_id / supplier existed from day one and were
                -- inserted NULL ('Supplier logic removed'); the reorder
                -- surfaces now read the vitals row for this answer, so it is
                -- computed here, once per build, with DERIVED tables -- this
                -- proc's history punishes correlated per-row subqueries.
                UPDATE $sp_core_product_vitals_table_name v
                LEFT JOIN (
                    SELECT trel.object_id, MIN(tt.term_id) AS term_id
                    FROM " . $sp_prefix . "term_relationships trel
                    JOIN " . $sp_prefix . "term_taxonomy tt
                      ON tt.term_taxonomy_id = trel.term_taxonomy_id
                    WHERE tt.taxonomy = 'supplier'
                    GROUP BY trel.object_id
                ) AS directsup ON directsup.object_id = v.parent_product_id
                LEFT JOIN (
                    SELECT sku, MIN(supplier_term_id) AS supplier_term_id
                    FROM " . $sp_prefix . "aieo_dmm_supplier_sku_map
                    WHERE supplier_term_id IS NOT NULL AND sku <> ''
                    GROUP BY sku
                ) AS skumap ON skumap.sku = v.sku
                LEFT JOIN " . $sp_prefix . "aieo_dmm_brand_extras be
                       ON be.term_id = v.brand_id AND be.supplier_term_id IS NOT NULL
                SET v.supplier_id = COALESCE(directsup.term_id, skumap.supplier_term_id, be.supplier_term_id),
                    v.supplier    = NULL;

                UPDATE $sp_core_product_vitals_table_name v
                JOIN " . $sp_prefix . "terms st ON st.term_id = v.supplier_id
                SET v.supplier = st.name;
            END";

    aieo_drop_sp_AIEO_CombineVariationVitalsInProductVitals($sp_prefix);
    $wpdb->query($sql);
}

function aieo_drop_sp_AIEO_InsertCoreProductAttributes($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_InsertCoreProductAttributes");
}

function aieo_drop_sp_AIEO_InsertProductCategoryHierarchy($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_InsertProductCategoryHierarchy");
}

function aieo_drop_sp_AIEO_InsertProductVitals($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_InsertProductVitals");
}

function aieo_drop_sp_AIEO_InsertVariationVitals($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_InsertVariationVitals");
}

function aieo_drop_sp_AIEO_CombineVariationVitalsInProductVitals($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_CombineVariationVitalsInProductVitals");
}

function aieo_create_sp_AIEO_UpdateCustomerCentricStdStats_v2($sp_prefix, $temp_orders_table_name)
{
    global $wpdb;

    $procedure_name = $sp_prefix . "AIEO_UpdateCustomerCentricStdStats_v2";
    $base_order_stats_table = $wpdb->prefix . 'aieo_base_order_stats';
    $cumulative_stats_table = $wpdb->prefix . 'aieo_cumulative_customer_stats';

    $sql = "CREATE PROCEDURE $procedure_name(
                IN target_table_name VARCHAR(255),
                IN TargetDate INT
            )
            SQL SECURITY INVOKER
            BEGIN
                -- STEP 1: Create base order stats table (one row per order)
                -- Similar to wp_pods_order_stats from cronjobs
                DROP TABLE IF EXISTS $base_order_stats_table;

                CREATE TABLE $base_order_stats_table (
                    `EponymousId` INT NOT NULL,
                    `OrderId` BIGINT NOT NULL,
                    `OrderSpend` DECIMAL(12,2),
                    `OrderSize` INT,
                    `NumberOfOrders` INT,
                    INDEX idx_eponymous (EponymousId),
                    INDEX idx_order (OrderId)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

                -- Populate base order stats - one row per order with aggregated order-level data
                -- Using dynamic SQL to handle table name parameter
                SET @sql1 = CONCAT('
                    INSERT INTO $base_order_stats_table (EponymousId, OrderId, OrderSpend, OrderSize, NumberOfOrders)
                    SELECT
                        EponymousId,
                        OrderId,
                        MAX(OrderItemsTotal) as OrderSpend,
                        MAX(OrderSize) as OrderSize,
                        (SELECT COUNT(DISTINCT o2.OrderId)
                         FROM ', target_table_name, ' o2
                         WHERE o2.EponymousId = o1.EponymousId
                         AND o2.EponymousId <> 0) as NumberOfOrders
                    FROM ', target_table_name, ' o1
                    WHERE EponymousId <> 0
                    GROUP BY EponymousId, OrderId
                ');
                PREPARE stmt1 FROM @sql1;
                EXECUTE stmt1;
                DEALLOCATE PREPARE stmt1;

                -- STEP 2: Create cumulative stats table via self-join
                -- This is the key optimization from generatecumorderstats.sql
                DROP TABLE IF EXISTS $cumulative_stats_table;

                CREATE TABLE $cumulative_stats_table (
                    INDEX idx_eponymous_order (EponymousId, OrderId)
                )
                SELECT
                    ws1.OrderId,
                    ws1.EponymousId,
                    ws1.OrderSpend,
                    ws1.OrderSize,
                    ws1.NumberOfOrders,
                    ROUND(ws1.OrderSpend / NULLIF(ws1.OrderSize, 0), 2) as AveSpendPerItemPerOrder,
                    ROUND(AVG(ws2.OrderSpend), 2) as TotalAverageOrder,
                    ROUND(AVG(ws2.OrderSize), 2) as AveItemsPerOrder,
                    ROUND(STD(ws2.OrderSize), 2) as STDItemsOfUser,
                    ROUND(STD(ws2.OrderSize) / NULLIF(MIN(ws2.OrderSize), 0), 2) as STDPerMinItemsOfUser,
                    ROUND(STD(ws2.OrderSize) / NULLIF(AVG(ws2.OrderSize), 0), 2) as STDPerAverageItemsOfUser,
                    ROUND(STD(ws2.OrderSize) / NULLIF(MAX(ws2.OrderSize), 0), 2) as STDPerMaxItemsOfUser,
                    ROUND(AVG(ws2.OrderSpend), 2) as AveSpendPerOrder,
                    ROUND(STD(ws2.OrderSpend), 2) as STDSpendOfUser,
                    ROUND(STD(ws2.OrderSpend) / NULLIF(MIN(ws2.OrderSpend), 0), 2) as STDPerMinSpendOfUser,
                    ROUND(STD(ws2.OrderSpend) / NULLIF(AVG(ws2.OrderSpend), 0), 2) as STDPerAverageSpendOfUser,
                    ROUND(STD(ws2.OrderSpend) / NULLIF(MAX(ws2.OrderSpend), 0), 2) as STDPerMaxSpendOfUser
                FROM $base_order_stats_table ws1
                JOIN $base_order_stats_table ws2 ON ws1.EponymousId = ws2.EponymousId
                GROUP BY ws1.EponymousId, ws1.OrderId, ws1.OrderSize, ws1.NumberOfOrders, ws1.OrderSpend
                ORDER BY ws1.EponymousId;

                -- Add primary key after table creation for better performance
                ALTER TABLE $cumulative_stats_table
                ADD COLUMN `id` BIGINT(45) UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
                ADD PRIMARY KEY (`id`);

                -- STEP 3: Update the temp orders table with the 6 basic statistics
                -- These are the ONLY columns that exist in temp_orders (from original procedure)
                SET @sql2 = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT
                            EponymousId,
                            AVG(OrderSize) AS AvgOrderSize,
                            MAX(OrderSize) AS MaxOrderSize,
                            MIN(OrderSize) AS MinOrderSize,
                            AVG(OrderSpend) AS AvgSpend,
                            MAX(OrderSpend) AS MaxSpend,
                            MIN(OrderSpend) AS MinSpend
                        FROM $base_order_stats_table
                        WHERE EponymousId <> 0
                        GROUP BY EponymousId
                    ) AS cs ON t1.EponymousId = cs.EponymousId
                    SET
                        t1.AvgOrderSize = cs.AvgOrderSize,
                        t1.MaxOrderSize = cs.MaxOrderSize,
                        t1.MinOrderSize = cs.MinOrderSize,
                        t1.AvgSpend = cs.AvgSpend,
                        t1.MaxSpend = cs.MaxSpend,
                        t1.MinSpend = cs.MinSpend
                ');
                PREPARE stmt2 FROM @sql2;
                EXECUTE stmt2;
                DEALLOCATE PREPARE stmt2;

                -- STEP 4: Update total customer statistics
                SET @sql3 = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT
                            EponymousId,
                            SUM(PrimaryQty) AS TotalCustomerItems,
                            COUNT(DISTINCT OrderId) AS TotalCustomerOrders,
                            SUM(LineValue) AS TotalCustomerTurnover
                        FROM ', target_table_name, '
                        WHERE EponymousId <> 0
                        GROUP BY EponymousId
                    ) AS cs ON t1.EponymousId = cs.EponymousId
                    SET
                        t1.TotalCustomerItems = cs.TotalCustomerItems,
                        t1.TotalCustomerOrders = cs.TotalCustomerOrders,
                        t1.TotalCustomerTurnover = cs.TotalCustomerTurnover
                ');
                PREPARE stmt3 FROM @sql3;
                EXECUTE stmt3;
                DEALLOCATE PREPARE stmt3;

            END";

    aieo_drop_sp_AIEO_UpdateCustomerCentricStdStats_v2($sp_prefix);
    $wpdb->query($sql);
}

function aieo_drop_sp_AIEO_UpdateCustomerCentricStdStats_v2($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_UpdateCustomerCentricStdStats_v2");
}

function aieo_create_sp_AIEO_UpdateCustomerCentricStdStats_Smart($sp_prefix, $temp_orders_table_name)
{
    global $wpdb;

    $procedure_name = $sp_prefix . "AIEO_UpdateCustomerCentricStdStats";
    $base_order_stats_table = $wpdb->prefix . 'aieo_base_order_stats';
    $cumulative_stats_table = $wpdb->prefix . 'aieo_cumulative_customer_stats';

    $sql = "CREATE PROCEDURE $procedure_name(
                IN target_table_name VARCHAR(255),
                IN TargetDate INT,
                IN batch_offset INT,
                IN batch_size INT
            )
            SQL SECURITY INVOKER
            BEGIN
                -- If batch_offset = 0, we're starting - create the base tables
                IF batch_offset = 0 THEN
                    -- STEP 1: Create base order stats table
                    DROP TABLE IF EXISTS $base_order_stats_table;

                    CREATE TABLE $base_order_stats_table (
                        `EponymousId` INT NOT NULL,
                        `OrderId` BIGINT NOT NULL,
                        `OrderSpend` DECIMAL(12,2),
                        `OrderSize` INT,
                        `NumberOfOrders` INT,
                        INDEX idx_eponymous (EponymousId),
                        INDEX idx_order (OrderId)
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

                    SET @sql1 = CONCAT('
                        INSERT INTO $base_order_stats_table (EponymousId, OrderId, OrderSpend, OrderSize, NumberOfOrders)
                        SELECT
                            EponymousId,
                            OrderId,
                            MAX(OrderItemsTotal) as OrderSpend,
                            MAX(OrderSize) as OrderSize,
                            (SELECT COUNT(DISTINCT o2.OrderId)
                             FROM ', target_table_name, ' o2
                             WHERE o2.EponymousId = o1.EponymousId
                             AND o2.EponymousId <> 0) as NumberOfOrders
                        FROM ', target_table_name, ' o1
                        WHERE EponymousId <> 0
                        GROUP BY EponymousId, OrderId
                    ');
                    PREPARE stmt1 FROM @sql1;
                    EXECUTE stmt1;
                    DEALLOCATE PREPARE stmt1;

                    -- STEP 2: Create cumulative stats table (if needed - optional for basic stats)
                    -- Skipped for now to keep it simple - only basic stats
                END IF;

                -- STEP 3: Update customers in this batch (ID range based on batch_offset and batch_size)
                -- First, create a temp table with the customer IDs for this batch (MariaDB workaround for LIMIT in subquery)
                DROP TEMPORARY TABLE IF EXISTS temp_batch_customers;
                SET @sql_temp = CONCAT('
                    CREATE TEMPORARY TABLE temp_batch_customers AS
                    SELECT DISTINCT EponymousId
                    FROM $base_order_stats_table
                    WHERE EponymousId <> 0
                    ORDER BY EponymousId
                    LIMIT ', batch_size, ' OFFSET ', batch_offset, '
                ');
                PREPARE stmt_temp FROM @sql_temp;
                EXECUTE stmt_temp;
                DEALLOCATE PREPARE stmt_temp;

                -- Now update using the temp table
                SET @sql2 = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT
                            EponymousId,
                            AVG(OrderSize) AS AvgOrderSize,
                            MAX(OrderSize) AS MaxOrderSize,
                            MIN(OrderSize) AS MinOrderSize,
                            AVG(OrderSpend) AS AvgSpend,
                            MAX(OrderSpend) AS MaxSpend,
                            MIN(OrderSpend) AS MinSpend
                        FROM $base_order_stats_table
                        WHERE EponymousId <> 0
                        AND EponymousId IN (SELECT EponymousId FROM temp_batch_customers)
                        GROUP BY EponymousId
                    ) AS cs ON t1.EponymousId = cs.EponymousId
                    SET
                        t1.AvgOrderSize = cs.AvgOrderSize,
                        t1.MaxOrderSize = cs.MaxOrderSize,
                        t1.MinOrderSize = cs.MinOrderSize,
                        t1.AvgSpend = cs.AvgSpend,
                        t1.MaxSpend = cs.MaxSpend,
                        t1.MinSpend = cs.MinSpend
                ');
                PREPARE stmt2 FROM @sql2;
                EXECUTE stmt2;
                DEALLOCATE PREPARE stmt2;

                -- STEP 4: Update total customer statistics for this batch (reuse temp_batch_customers)
                SET @sql3 = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT
                            EponymousId,
                            SUM(PrimaryQty) AS TotalCustomerItems,
                            COUNT(DISTINCT OrderId) AS TotalCustomerOrders,
                            SUM(LineValue) AS TotalCustomerTurnover
                        FROM ', target_table_name, '
                        WHERE EponymousId <> 0
                        AND EponymousId IN (SELECT EponymousId FROM temp_batch_customers)
                        GROUP BY EponymousId
                    ) AS cs ON t1.EponymousId = cs.EponymousId
                    SET
                        t1.TotalCustomerItems = cs.TotalCustomerItems,
                        t1.TotalCustomerOrders = cs.TotalCustomerOrders,
                        t1.TotalCustomerTurnover = cs.TotalCustomerTurnover
                ');
                PREPARE stmt3 FROM @sql3;
                EXECUTE stmt3;
                DEALLOCATE PREPARE stmt3;

                -- ============================================================
                -- STEP 5: Top-3 favourites per customer (denormalized FavN
                -- columns on every line of temp_orders).
                --
                -- Rank dimensions (each independent — same shape, different
                -- source column):
                --   ProductId → Fav1Product / Fav2Product / Fav3Product
                --   BrandId   → Fav1Brand   / Fav2Brand   / Fav3Brand
                --   MenuId    → Fav1MainCat / Fav2MainCat / Fav3MainCat
                --   HeadId    → Fav1HeadCat / Fav2HeadCat / Fav3HeadCat
                --   SubcatId  → Fav1SubCat  / Fav2SubCat  / Fav3SubCat
                --
                -- For each (customer, dimension), GROUP BY (id, customer)
                -- summing LineValue → ROW_NUMBER() rank by total desc → pivot
                -- top-3 into 3 cols via MAX(IF(rnk=N, id, NULL)). Tie-break
                -- by id ASC so ordering is deterministic across re-runs.
                -- Scoped to the current batch via temp_batch_customers, so
                -- the cost stays linear in batch size regardless of total
                -- customer count.
                --
                -- Note: the catalog-hierarchy sproc only sets MenuId/HeadId
                -- when a product is directly tagged with a parent=0
                -- product_cat. Most FF products are tagged on leaf
                -- subcategories only, so coverage on those two columns is
                -- low (~10/170k lines). SubcatId has full coverage and is
                -- the most useful favourite-category signal until the
                -- upstream hierarchy walker is fixed.
                -- ============================================================

                -- Fav1Brand / Fav2Brand / Fav3Brand
                SET @sql_fav_brand = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT EponymousId,
                            MAX(IF(rnk = 1, BrandId, NULL)) AS f1,
                            MAX(IF(rnk = 2, BrandId, NULL)) AS f2,
                            MAX(IF(rnk = 3, BrandId, NULL)) AS f3
                        FROM (
                            SELECT EponymousId, BrandId,
                                ROW_NUMBER() OVER (
                                    PARTITION BY EponymousId
                                    ORDER BY total DESC, BrandId ASC
                                ) AS rnk
                            FROM (
                                SELECT EponymousId, BrandId,
                                    SUM(IFNULL(LineValue, 0)) AS total
                                FROM ', target_table_name, '
                                WHERE EponymousId <> 0
                                  AND EponymousId IN (SELECT EponymousId FROM temp_batch_customers)
                                  AND BrandId IS NOT NULL AND BrandId > 0
                                GROUP BY EponymousId, BrandId
                            ) totals
                        ) ranked
                        WHERE rnk <= 3
                        GROUP BY EponymousId
                    ) f ON f.EponymousId = t1.EponymousId
                    SET t1.Fav1Brand = f.f1,
                        t1.Fav2Brand = f.f2,
                        t1.Fav3Brand = f.f3
                ');
                PREPARE stmt_fav_brand FROM @sql_fav_brand;
                EXECUTE stmt_fav_brand;
                DEALLOCATE PREPARE stmt_fav_brand;

                -- Fav1Product / Fav2Product / Fav3Product
                SET @sql_fav_product = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT EponymousId,
                            MAX(IF(rnk = 1, ProductId, NULL)) AS f1,
                            MAX(IF(rnk = 2, ProductId, NULL)) AS f2,
                            MAX(IF(rnk = 3, ProductId, NULL)) AS f3
                        FROM (
                            SELECT EponymousId, ProductId,
                                ROW_NUMBER() OVER (
                                    PARTITION BY EponymousId
                                    ORDER BY total DESC, ProductId ASC
                                ) AS rnk
                            FROM (
                                SELECT EponymousId, ProductId,
                                    SUM(IFNULL(LineValue, 0)) AS total
                                FROM ', target_table_name, '
                                WHERE EponymousId <> 0
                                  AND EponymousId IN (SELECT EponymousId FROM temp_batch_customers)
                                  AND ProductId IS NOT NULL AND ProductId > 0
                                GROUP BY EponymousId, ProductId
                            ) totals
                        ) ranked
                        WHERE rnk <= 3
                        GROUP BY EponymousId
                    ) f ON f.EponymousId = t1.EponymousId
                    SET t1.Fav1Product = f.f1,
                        t1.Fav2Product = f.f2,
                        t1.Fav3Product = f.f3
                ');
                PREPARE stmt_fav_product FROM @sql_fav_product;
                EXECUTE stmt_fav_product;
                DEALLOCATE PREPARE stmt_fav_product;

                -- Fav1MainCat / Fav2MainCat / Fav3MainCat (MenuId source)
                SET @sql_fav_maincat = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT EponymousId,
                            MAX(IF(rnk = 1, MenuId, NULL)) AS f1,
                            MAX(IF(rnk = 2, MenuId, NULL)) AS f2,
                            MAX(IF(rnk = 3, MenuId, NULL)) AS f3
                        FROM (
                            SELECT EponymousId, MenuId,
                                ROW_NUMBER() OVER (
                                    PARTITION BY EponymousId
                                    ORDER BY total DESC, MenuId ASC
                                ) AS rnk
                            FROM (
                                SELECT EponymousId, MenuId,
                                    SUM(IFNULL(LineValue, 0)) AS total
                                FROM ', target_table_name, '
                                WHERE EponymousId <> 0
                                  AND EponymousId IN (SELECT EponymousId FROM temp_batch_customers)
                                  AND MenuId IS NOT NULL AND MenuId > 0
                                GROUP BY EponymousId, MenuId
                            ) totals
                        ) ranked
                        WHERE rnk <= 3
                        GROUP BY EponymousId
                    ) f ON f.EponymousId = t1.EponymousId
                    SET t1.Fav1MainCat = f.f1,
                        t1.Fav2MainCat = f.f2,
                        t1.Fav3MainCat = f.f3
                ');
                PREPARE stmt_fav_maincat FROM @sql_fav_maincat;
                EXECUTE stmt_fav_maincat;
                DEALLOCATE PREPARE stmt_fav_maincat;

                -- Fav1HeadCat / Fav2HeadCat / Fav3HeadCat (HeadId source)
                SET @sql_fav_headcat = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT EponymousId,
                            MAX(IF(rnk = 1, HeadId, NULL)) AS f1,
                            MAX(IF(rnk = 2, HeadId, NULL)) AS f2,
                            MAX(IF(rnk = 3, HeadId, NULL)) AS f3
                        FROM (
                            SELECT EponymousId, HeadId,
                                ROW_NUMBER() OVER (
                                    PARTITION BY EponymousId
                                    ORDER BY total DESC, HeadId ASC
                                ) AS rnk
                            FROM (
                                SELECT EponymousId, HeadId,
                                    SUM(IFNULL(LineValue, 0)) AS total
                                FROM ', target_table_name, '
                                WHERE EponymousId <> 0
                                  AND EponymousId IN (SELECT EponymousId FROM temp_batch_customers)
                                  AND HeadId IS NOT NULL AND HeadId > 0
                                GROUP BY EponymousId, HeadId
                            ) totals
                        ) ranked
                        WHERE rnk <= 3
                        GROUP BY EponymousId
                    ) f ON f.EponymousId = t1.EponymousId
                    SET t1.Fav1HeadCat = f.f1,
                        t1.Fav2HeadCat = f.f2,
                        t1.Fav3HeadCat = f.f3
                ');
                PREPARE stmt_fav_headcat FROM @sql_fav_headcat;
                EXECUTE stmt_fav_headcat;
                DEALLOCATE PREPARE stmt_fav_headcat;

                -- Fav1SubCat / Fav2SubCat / Fav3SubCat (SubcatId source)
                SET @sql_fav_subcat = CONCAT('
                    UPDATE ', target_table_name, ' AS t1
                    INNER JOIN (
                        SELECT EponymousId,
                            MAX(IF(rnk = 1, SubcatId, NULL)) AS f1,
                            MAX(IF(rnk = 2, SubcatId, NULL)) AS f2,
                            MAX(IF(rnk = 3, SubcatId, NULL)) AS f3
                        FROM (
                            SELECT EponymousId, SubcatId,
                                ROW_NUMBER() OVER (
                                    PARTITION BY EponymousId
                                    ORDER BY total DESC, SubcatId ASC
                                ) AS rnk
                            FROM (
                                SELECT EponymousId, SubcatId,
                                    SUM(IFNULL(LineValue, 0)) AS total
                                FROM ', target_table_name, '
                                WHERE EponymousId <> 0
                                  AND EponymousId IN (SELECT EponymousId FROM temp_batch_customers)
                                  AND SubcatId IS NOT NULL AND SubcatId > 0
                                GROUP BY EponymousId, SubcatId
                            ) totals
                        ) ranked
                        WHERE rnk <= 3
                        GROUP BY EponymousId
                    ) f ON f.EponymousId = t1.EponymousId
                    SET t1.Fav1SubCat = f.f1,
                        t1.Fav2SubCat = f.f2,
                        t1.Fav3SubCat = f.f3
                ');
                PREPARE stmt_fav_subcat FROM @sql_fav_subcat;
                EXECUTE stmt_fav_subcat;
                DEALLOCATE PREPARE stmt_fav_subcat;

                -- Clean up temp table
                DROP TEMPORARY TABLE IF EXISTS temp_batch_customers;

            END";

    aieo_drop_sp_AIEO_UpdateCustomerCentricStdStats($sp_prefix);
    $wpdb->query($sql);
}

function aieo_drop_sp_AIEO_UpdateCustomerCentricStdStats($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_UpdateCustomerCentricStdStats");
}

function aieo_create_sp_AIEO_UpdateCustomerCentricAdvStats($sp_prefix, $sp_orders_table)
{
    global $wpdb;

    $procedure_name = $sp_prefix . "AIEO_UpdateCustomerCentricAdvStats";

    $sql = "CREATE PROCEDURE $procedure_name(
                IN target_table_name VARCHAR(255),
                IN use_product_id TINYINT,
                IN batch_offset INT,
                IN batch_size INT
            )
            SQL SECURITY INVOKER
            BEGIN
                DECLARE column_id VARCHAR(255);
                SET column_id = IF(use_product_id = 1, 'ProductId', 'ParentProductId');

                -- If batch_offset = 0, we're starting - create the persistent temp tables
                IF batch_offset = 0 THEN
                    -- Create a persistent table with a row number for ALL customers
                    -- Only select columns we need for advanced stats calculations
                    -- Using regular table (not TEMPORARY) so it persists across AJAX requests
                    DROP TABLE IF EXISTS {$sp_prefix}aieo_adv_stats_orders_rn;
                    SET @sql = CONCAT('
                        CREATE TABLE {$sp_prefix}aieo_adv_stats_orders_rn (
                            EponymousId INT,
                            OrderId BIGINT,
                            OrderDate DATETIME,
                            OrderItemsTotal DECIMAL(10,2),
                            rn INT,
                            INDEX idx_eponymous (EponymousId),
                            INDEX idx_eponymous_rn (EponymousId, rn)
                        ) ENGINE=InnoDB AS
                        -- ONE ROW PER ORDER, NOT PER LINE.
                        --
                        -- This used to ROW_NUMBER() the raw snapshot, which is one row
                        -- per order LINE. A four-line order therefore became four
                        -- pseudo-orders a fraction of a second apart, and every
                        -- days-between-orders gap computed downstream collapsed toward
                        -- zero. Decisive invariant: a customer with exactly TWO orders
                        -- has exactly ONE gap, so AvgDBO must EQUAL MaxDBO. Measured on
                        -- the reference bed before this fix, across 21,306 such
                        -- customers, AvgDBO/MaxDBO = 0.2354 -- against a mean of 3.52
                        -- lines per order, and 1/3.52 = 0.284. The ratio was the
                        -- line-count reciprocal, which is what a grain error looks like.
                        --
                        -- It was not cosmetic: push-schema.php maps these columns into
                        -- customer_intelligence.replenishment_json, which is what the
                        -- replenisher / overdue segments and the replenishment push
                        -- trigger select on. The CDP believed the average customer
                        -- reordered every 13.9 days, so 1,096 replenishers carried a
                        -- mean recency of 674 days.
                        --
                        -- PAID ORDERS ONLY, same list as AIEO_Customer_Purchases::
                        -- PURCHASE_STATUSES. A cancelled or failed order is not a
                        -- purchase and must not define a customer cadence.
                        --
                        -- OrderId breaks ties in the ORDER BY so rn is deterministic
                        -- across re-runs; two orders can share a timestamp.
                        SELECT EponymousId, OrderId, OrderDate, OrderItemsTotal,
                               ROW_NUMBER() OVER (PARTITION BY EponymousId ORDER BY OrderDate, OrderId) AS rn
                        FROM (
                            SELECT EponymousId,
                                   OrderId,
                                   MIN(OrderDate)       AS OrderDate,
                                   MAX(OrderItemsTotal) AS OrderItemsTotal
                            FROM ', target_table_name, '
                            WHERE EponymousId <> 0
                              AND OrderStatus IN (''wc-completed'', ''wc-processing'', ''erp-completed'')
                            GROUP BY EponymousId, OrderId
                        ) o;
                    ');

                    PREPARE temp_stmt FROM @sql;
                    EXECUTE temp_stmt;
                    DEALLOCATE PREPARE temp_stmt;

                    -- CLEAR CUSTOMERS THE PASS CAN NO LONGER REACH.
                    --
                    -- The rn table now excludes anyone with no PAID order, so the
                    -- UPDATE below never visits them -- and they would keep whatever
                    -- cadence a previous run left on them, forever. Measured after
                    -- the grain fix: 258 customers still carrying the old line-grain
                    -- numbers (their AvgDBO/MaxDBO ratio was 0.294, the pre-fix
                    -- artefact) purely because every one of their orders was
                    -- on-hold or cancelled.
                    --
                    -- NULL is the correct value: a customer with no completed order
                    -- has no cadence, and NULL keeps them out of the replenisher and
                    -- overdue segments rather than leaving a stale number that puts
                    -- them in. Same reasoning as the pillars unflag sweep.
                    SET @sql = CONCAT('
                        UPDATE `', target_table_name, '` t
                           SET t.AvgDBO = NULL, t.MaxDBO = NULL, t.MinDBO = NULL,
                               t.CurrentDBO = NULL, t.SDDBO = NULL, t.zDBO = NULL,
                               t.LastOrderSpend = NULL
                         WHERE t.EponymousId <> 0
                           AND NOT EXISTS (
                               SELECT 1 FROM {$sp_prefix}aieo_adv_stats_orders_rn r
                                WHERE r.EponymousId = t.EponymousId );
                    ');
                    PREPARE temp_stmt FROM @sql;
                    EXECUTE temp_stmt;
                    DEALLOCATE PREPARE temp_stmt;

                    -- Create a copy for self-join with same indexes
                    DROP TABLE IF EXISTS {$sp_prefix}aieo_adv_stats_orders_rn_copy;
                    CREATE TABLE {$sp_prefix}aieo_adv_stats_orders_rn_copy (
                        EponymousId INT,
                        OrderId BIGINT,
                        OrderDate DATETIME,
                        OrderItemsTotal DECIMAL(10,2),
                        rn INT,
                        INDEX idx_eponymous (EponymousId),
                        INDEX idx_eponymous_rn (EponymousId, rn)
                    ) ENGINE=InnoDB AS SELECT * FROM {$sp_prefix}aieo_adv_stats_orders_rn;

                    -- Fresh stats on the just-built ranking tables before the rn⨝rn_copy
                    -- self-join below — a freshly CTAS-built table sits at ~1-row InnoDB
                    -- stats, which flips the self-join into a full×full BNL (stale-stats
                    -- guard; same class as the propagate_brand incident).
                    ANALYZE TABLE {$sp_prefix}aieo_adv_stats_orders_rn;
                    ANALYZE TABLE {$sp_prefix}aieo_adv_stats_orders_rn_copy;
                END IF;

                -- Process customers in this batch using LIMIT/OFFSET
                -- First, create a temp table with the customer IDs for this batch (MariaDB workaround for LIMIT in subquery)
                DROP TEMPORARY TABLE IF EXISTS temp_batch_customers_adv;
                SET @sql_temp = CONCAT('
                    CREATE TEMPORARY TABLE temp_batch_customers_adv (
                        EponymousId INT PRIMARY KEY
                    ) ENGINE=MEMORY AS
                    SELECT DISTINCT EponymousId
                    FROM {$sp_prefix}aieo_adv_stats_orders_rn
                    WHERE EponymousId <> 0
                    ORDER BY EponymousId
                    LIMIT ', batch_size, ' OFFSET ', batch_offset, '
                ');
                PREPARE stmt_temp FROM @sql_temp;
                EXECUTE stmt_temp;
                DEALLOCATE PREPARE stmt_temp;

                -- Now update using the temp table
                SET @sql = CONCAT('
                UPDATE `', target_table_name, '` AS t1
                INNER JOIN (
                    -- MaxDBO / MinDBO no longer suppress a zero gap. The CASE
                    -- wrappers existed to hide the artefact the grain error created
                    -- -- lines of ONE order reading as same-day pseudo-orders. Now
                    -- rn is per order, a zero gap is a real same-day reorder and
                    -- MinDBO = 0 is the honest answer.
                    --
                    -- CurrentDBO is measured from the DATASET anchor, not wall clock.
                    -- With UTC_TIMESTAMP() every customer aged by one day for each day
                    -- Data Prep was not re-run, so zDBO drifted and the overdue segment
                    -- grew on its own between runs.
                    --
                    -- The anchor is read from the rn table rather than held in a
                    -- session variable: this procedure is re-entered once per AJAX
                    -- batch, each on its own connection, so a @var set at
                    -- batch_offset = 0 would be NULL for every batch after the first
                    -- and CurrentDBO would silently become NULL. The rn table is
                    -- regular (not TEMPORARY) precisely because it has to survive
                    -- those requests, which makes it the one thing that CAN carry the
                    -- anchor -- and it is frozen for the whole run, so every batch
                    -- measures from the same instant.
                    SELECT a.EponymousId,
                           AVG(DATEDIFF(a.OrderDate, b.OrderDate)) AS AvgDBO,
                           MAX(DATEDIFF(a.OrderDate, b.OrderDate)) AS MaxDBO,
                           MIN(DATEDIFF(a.OrderDate, b.OrderDate)) AS MinDBO,
                           DATEDIFF((SELECT MAX(OrderDate) FROM {$sp_prefix}aieo_adv_stats_orders_rn), MAX(a.OrderDate)) AS CurrentDBO,
                           STDDEV(DATEDIFF(a.OrderDate, b.OrderDate)) AS SDDBO,
                           -- The last order value. FIRST_VALUE() is a WINDOW
                           -- function in a GROUP BY query, so it read an arbitrary
                           -- row of the group; keying off MAX(rn) is deterministic.
                           MAX(CASE WHEN a.rn = c.max_rn THEN a.OrderItemsTotal END) AS LastOrderSpend
                    FROM {$sp_prefix}aieo_adv_stats_orders_rn AS a
                    LEFT JOIN {$sp_prefix}aieo_adv_stats_orders_rn_copy AS b ON a.EponymousId = b.EponymousId AND a.rn = b.rn + 1
                    LEFT JOIN (
                        SELECT EponymousId, MAX(rn) AS max_rn
                        FROM {$sp_prefix}aieo_adv_stats_orders_rn GROUP BY EponymousId
                    ) AS c ON c.EponymousId = a.EponymousId
                    WHERE a.EponymousId <> 0
                      AND a.EponymousId IN (SELECT EponymousId FROM temp_batch_customers_adv)
                    GROUP BY a.EponymousId
                ) AS t2 ON t1.EponymousId = t2.EponymousId
                SET
                    t1.AvgDBO = t2.AvgDBO,
                    t1.MaxDBO = t2.MaxDBO,
                    t1.MinDBO = t2.MinDBO,
                    t1.CurrentDBO = t2.CurrentDBO,
                    t1.SDDBO = t2.SDDBO,
                    t1.zDBO = (t2.CurrentDBO - t2.AvgDBO) / NULLIF(t2.SDDBO, 0),
                    t1.LastOrderSpend = t2.LastOrderSpend;
                ');

                PREPARE stmt2 FROM @sql;
                EXECUTE stmt2;
                DEALLOCATE PREPARE stmt2;

                -- Clean up batch temp table
                DROP TEMPORARY TABLE IF EXISTS temp_batch_customers_adv;

                -- Note: Persistent helper tables {$sp_prefix}aieo_adv_stats_orders_rn* will be cleaned up by PHP after completion
            END";

    aieo_drop_sp_AIEO_UpdateCustomerCentricAdvStats($sp_prefix);
    $wpdb->query($sql);
}

function aieo_drop_sp_AIEO_UpdateCustomerCentricAdvStats($sp_prefix)
{
    aieo_drop_sp_common($sp_prefix, "AIEO_UpdateCustomerCentricAdvStats");
}

function aieo_create_sp_AIEO_UpdateCustomerCentricStdStats($sp_prefix, $temp_orders_table_name)
{
    global $wpdb;

    $procedure_name = $sp_prefix . "AIEO_UpdateCustomerCentricStdStats";
    $base_order_stats_table = $wpdb->prefix . 'aieo_base_order_stats';
    $cumulative_stats_table = $wpdb->prefix . 'aieo_cumulative_customer_stats';
    $progress_table = $wpdb->prefix . 'aieo_customer_stats_progress';

    $sql = "CREATE PROCEDURE $procedure_name(
                IN target_table_name VARCHAR(255),
                IN TargetDate INT,
                IN batch_size INT
            )
            SQL SECURITY INVOKER
            BEGIN
                DECLARE done INT DEFAULT FALSE;
                DECLARE customer_id INT;
                DECLARE total_customers INT;
                DECLARE processed_customers INT DEFAULT 0;

                -- Create progress tracking table
                DROP TABLE IF EXISTS $progress_table;
                CREATE TABLE $progress_table (
                    total INT,
                    processed INT,
                    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=MEMORY;

                -- STEP 1: Create base order stats table (same as v2 - fast bulk operation)
                DROP TABLE IF EXISTS $base_order_stats_table;

                CREATE TABLE $base_order_stats_table (
                    `EponymousId` INT NOT NULL,
                    `OrderId` BIGINT NOT NULL,
                    `OrderSpend` DECIMAL(12,2),
                    `OrderSize` INT,
                    `NumberOfOrders` INT,
                    INDEX idx_eponymous (EponymousId),
                    INDEX idx_order (OrderId)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

                SET @sql1 = CONCAT('
                    INSERT INTO $base_order_stats_table (EponymousId, OrderId, OrderSpend, OrderSize, NumberOfOrders)
                    SELECT
                        EponymousId,
                        OrderId,
                        MAX(OrderItemsTotal) as OrderSpend,
                        MAX(OrderSize) as OrderSize,
                        (SELECT COUNT(DISTINCT o2.OrderId)
                         FROM ', target_table_name, ' o2
                         WHERE o2.EponymousId = o1.EponymousId
                         AND o2.EponymousId <> 0) as NumberOfOrders
                    FROM ', target_table_name, ' o1
                    WHERE EponymousId <> 0
                    GROUP BY EponymousId, OrderId
                ');
                PREPARE stmt1 FROM @sql1;
                EXECUTE stmt1;
                DEALLOCATE PREPARE stmt1;

                -- STEP 2: Create cumulative stats table (same as v2 - fast self-join)
                DROP TABLE IF EXISTS $cumulative_stats_table;

                CREATE TABLE $cumulative_stats_table (
                    INDEX idx_eponymous_order (EponymousId, OrderId)
                )
                SELECT
                    ws1.OrderId,
                    ws1.EponymousId,
                    ws1.OrderSpend,
                    ws1.OrderSize,
                    ws1.NumberOfOrders,
                    ROUND(ws1.OrderSpend / NULLIF(ws1.OrderSize, 0), 2) as AveSpendPerItemPerOrder,
                    ROUND(AVG(ws2.OrderSpend), 2) as TotalAverageOrder,
                    ROUND(AVG(ws2.OrderSize), 2) as AveItemsPerOrder,
                    ROUND(STD(ws2.OrderSize), 2) as STDItemsOfUser,
                    ROUND(STD(ws2.OrderSize) / NULLIF(MIN(ws2.OrderSize), 0), 2) as STDPerMinItemsOfUser,
                    ROUND(STD(ws2.OrderSize) / NULLIF(AVG(ws2.OrderSize), 0), 2) as STDPerAverageItemsOfUser,
                    ROUND(STD(ws2.OrderSize) / NULLIF(MAX(ws2.OrderSize), 0), 2) as STDPerMaxItemsOfUser,
                    ROUND(AVG(ws2.OrderSpend), 2) as AveSpendPerOrder,
                    ROUND(STD(ws2.OrderSpend), 2) as STDSpendOfUser,
                    ROUND(STD(ws2.OrderSpend) / NULLIF(MIN(ws2.OrderSpend), 0), 2) as STDPerMinSpendOfUser,
                    ROUND(STD(ws2.OrderSpend) / NULLIF(AVG(ws2.OrderSpend), 0), 2) as STDPerAverageSpendOfUser,
                    ROUND(STD(ws2.OrderSpend) / NULLIF(MAX(ws2.OrderSpend), 0), 2) as STDPerMaxSpendOfUser
                FROM $base_order_stats_table ws1
                JOIN $base_order_stats_table ws2 ON ws1.EponymousId = ws2.EponymousId
                GROUP BY ws1.EponymousId, ws1.OrderId, ws1.OrderSize, ws1.NumberOfOrders, ws1.OrderSpend
                ORDER BY ws1.EponymousId;

                -- Get total distinct customers
                SELECT COUNT(DISTINCT EponymousId) INTO total_customers
                FROM $base_order_stats_table
                WHERE EponymousId <> 0;

                -- Initialize progress
                INSERT INTO $progress_table (total, processed) VALUES (total_customers, 0);

                -- STEP 3 & 4: Update customers in batches with progress tracking
                -- Cursor to iterate through distinct customers
                BEGIN
                    DECLARE customer_cursor CURSOR FOR
                        SELECT DISTINCT EponymousId
                        FROM $base_order_stats_table
                        WHERE EponymousId <> 0
                        ORDER BY EponymousId;

                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

                    OPEN customer_cursor;

                    customer_loop: LOOP
                        FETCH customer_cursor INTO customer_id;
                        IF done THEN
                            LEAVE customer_loop;
                        END IF;

                        -- Update basic statistics for this customer
                        SET @sql2 = CONCAT('
                            UPDATE ', target_table_name, ' AS t1
                            INNER JOIN (
                                SELECT
                                    EponymousId,
                                    AVG(OrderSize) AS AvgOrderSize,
                                    MAX(OrderSize) AS MaxOrderSize,
                                    MIN(OrderSize) AS MinOrderSize,
                                    AVG(OrderSpend) AS AvgSpend,
                                    MAX(OrderSpend) AS MaxSpend,
                                    MIN(OrderSpend) AS MinSpend
                                FROM $base_order_stats_table
                                WHERE EponymousId = ', customer_id, '
                                GROUP BY EponymousId
                            ) AS cs ON t1.EponymousId = cs.EponymousId
                            SET
                                t1.AvgOrderSize = cs.AvgOrderSize,
                                t1.MaxOrderSize = cs.MaxOrderSize,
                                t1.MinOrderSize = cs.MinOrderSize,
                                t1.AvgSpend = cs.AvgSpend,
                                t1.MaxSpend = cs.MaxSpend,
                                t1.MinSpend = cs.MinSpend
                        ');
                        PREPARE stmt2 FROM @sql2;
                        EXECUTE stmt2;
                        DEALLOCATE PREPARE stmt2;

                        -- Update total customer statistics for this customer
                        SET @sql3 = CONCAT('
                            UPDATE ', target_table_name, ' AS t1
                            INNER JOIN (
                                SELECT
                                    EponymousId,
                                    SUM(PrimaryQty) AS TotalCustomerItems,
                                    COUNT(DISTINCT OrderId) AS TotalCustomerOrders,
                                    SUM(LineValue) AS TotalCustomerTurnover
                                FROM ', target_table_name, '
                                WHERE EponymousId = ', customer_id, '
                                GROUP BY EponymousId
                            ) AS cs ON t1.EponymousId = cs.EponymousId
                            SET
                                t1.TotalCustomerItems = cs.TotalCustomerItems,
                                t1.TotalCustomerOrders = cs.TotalCustomerOrders,
                                t1.TotalCustomerTurnover = cs.TotalCustomerTurnover
                        ');
                        PREPARE stmt3 FROM @sql3;
                        EXECUTE stmt3;
                        DEALLOCATE PREPARE stmt3;

                        SET processed_customers = processed_customers + 1;

                        -- Update progress every batch_size customers
                        IF MOD(processed_customers, batch_size) = 0 THEN
                            UPDATE $progress_table SET processed = processed_customers;
                        END IF;

                    END LOOP;

                    CLOSE customer_cursor;
                END;

                -- Final progress update
                UPDATE $progress_table SET processed = total_customers;

            END";

    aieo_drop_sp_AIEO_UpdateCustomerCentricStdStats($sp_prefix);
    $wpdb->query($sql);
}

function aieo_drop_core_product_attributes_table($sp_core_product_attributes_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_core_product_attributes_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_cpb_brands_brand_id', $sp_core_product_attributes_table_name);
    aieo_drop_index_if_exists('aieo_idx_cpb_brands_product', $sp_core_product_attributes_table_name);
    aieo_drop_index_if_exists('aieo_idx_cpb_brands_parent_product', $sp_core_product_attributes_table_name);
    aieo_drop_index_if_exists('aieo_idx_cpb_effective', $sp_core_product_attributes_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_core_product_attributes_table_name}`");
}

function aieo_drop_core_product_category_hierarchy_table($sp_core_product_category_hierarchy_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_core_product_category_hierarchy_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_hierarchy_product_id', $sp_core_product_category_hierarchy_table_name);
    aieo_drop_index_if_exists('aieo_idx_wppch_t_category_id', $sp_core_product_category_hierarchy_table_name);
    aieo_drop_index_if_exists('aieo_idx_wppch_sl_category_id', $sp_core_product_category_hierarchy_table_name);
    aieo_drop_index_if_exists('aieo_idx_wppch_tl_category_id', $sp_core_product_category_hierarchy_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_core_product_category_hierarchy_table_name}`");
}

function aieo_drop_core_product_vitals_table($sp_core_product_vitals_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_core_product_vitals_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_vitals_product_id', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_parent_product', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_sku', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_parent_sku', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_stock', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_brand', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_menu_id', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_head_id', $sp_core_product_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_vitals_wppv_subcat_id', $sp_core_product_vitals_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_core_product_vitals_table_name}`");
}

function aieo_drop_core_variation_vitals_table($sp_core_variation_vitals_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_core_variation_vitals_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_var_vitals_product_id', $sp_core_variation_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_var_vitals_parent_id', $sp_core_variation_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_var_vitals_sku', $sp_core_variation_vitals_table_name);
    aieo_drop_index_if_exists('aieo_idx_var_vitals_erpid', $sp_core_variation_vitals_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_core_variation_vitals_table_name}`");
}

function aieo_drop_core_reciprocal_products_table($sp_core_reciprocal_products_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_core_reciprocal_products_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_rpt_source_product_id', $sp_core_reciprocal_products_table_name);
    aieo_drop_index_if_exists('aieo_idx_rpt_source_parent_product_id', $sp_core_reciprocal_products_table_name);
    aieo_drop_index_if_exists('aieo_idx_rpt_target_product_id', $sp_core_reciprocal_products_table_name);
    aieo_drop_index_if_exists('aieo_idx_rpt_target_parent_product_id', $sp_core_reciprocal_products_table_name);
    aieo_drop_index_if_exists('aieo_idx_rpt_target_sku', $sp_core_reciprocal_products_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_core_reciprocal_products_table_name}`");
}

function aieo_drop_temp_orders_table($sp_temp_orders_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_temp_orders_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_orderid_orders', $sp_temp_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_productid_orders', $sp_temp_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_parentproductid_orders', $sp_temp_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_brandid_orders', $sp_temp_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_eponymousid_orders', $sp_temp_orders_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_temp_orders_table_name}`"); 
}

function aieo_drop_temp_store_orders_table($sp_temp_store_orders_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_temp_store_orders_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_orderid_store_orders', $sp_temp_store_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_productid_store_orders', $sp_temp_store_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_parentproductid_store_orders', $sp_temp_store_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_brandid_store_orders', $sp_temp_store_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_eponymousid_store_orders', $sp_temp_store_orders_table_name);
    aieo_drop_index_if_exists('aieo_idx_loyaltyid_store_orders', $sp_temp_store_orders_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_temp_store_orders_table_name}`"); 
}

function aieo_drop_temp_products_table($sp_temp_products_table_name)
{
  global $wpdb;

  
  $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sp_temp_products_table_name}'");

  if ($table_exists) {
    
    aieo_drop_index_if_exists('aieo_idx_productid_products', $sp_temp_products_table_name);
    aieo_drop_index_if_exists('aieo_idx_parentproductid_products', $sp_temp_products_table_name);
    aieo_drop_index_if_exists('aieo_idx_brandid_products', $sp_temp_products_table_name);
  }

  
  $wpdb->query("DROP TABLE IF EXISTS `{$sp_temp_products_table_name}`"); 
}

function aieo_drop_sp_common($sp_prefix, $procedure_name_suffix)
{
  global $wpdb;

  
  $sp_prefix = sanitize_text_field($sp_prefix);
  if (!preg_match('/^\w+$/', $sp_prefix)) {
    
    wp_die(esc_html__('Invalid input detected in prefix', 'ai-eshop-optimizer'));
  }

  
  $procedure_name_suffix = sanitize_text_field($procedure_name_suffix);
  if (!preg_match('/^\w+$/', $procedure_name_suffix)) {
    
    wp_die(esc_html__('Invalid input detected in procedure name suffix', 'ai-eshop-optimizer'));
  }

  
  $procedure_name = $sp_prefix . $procedure_name_suffix;

  
  $query = "DROP PROCEDURE IF EXISTS `{$procedure_name}`";

  
  $wpdb->query($query);
}

function aieo_drop_sp_AIEO_UpdateOrdersWithUnsold($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_UpdateOrdersWithUnsold");
}

function aieo_drop_sp_AIEO_StripHTMLAndReplicateContentIntro($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_StripHTMLAndReplicateContentIntro");
}

function aieo_drop_sp_AIEO_RenameTable($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_RenameTable");
}

function aieo_drop_sp_AIEO_FindDifferences($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_FindDifferences");
}

function aieo_drop_sp_AIEO_Create_Product_Catalogue_HPOS($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_Create_Product_Catalogue_HPOS");
}

function aieo_drop_sp_AIEO_Export_Orders_HPOS($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_Export_Orders_HPOS");
}

function aieo_drop_sp_AIEO_Export_Store_Orders($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_Export_Store_Orders");
}

function aieo_drop_sp_AIEO_Export_Orders_non_HPOS($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_Export_Orders_non_HPOS");
}

function aieo_drop_sp_AIEO_Merge_Store_Orders($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_Merge_Store_Orders");

}

function aieo_drop_sp_AIEO_UpdateProductCentricStats($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_UpdateProductCentricStats");
}
function aieo_drop_sp_GenerateGraphDBUUIDs($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_GenerateGraphDBUUIDs");
}

function aieo_drop_sp_GenerateGraphDBUUIDFreqs($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_GenerateGraphDBUUIDFreqs");
}

function aieo_drop_sp_AIEO_GenerateGraphDBUUIDs($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_GenerateGraphDBUUIDs");
}

function aieo_drop_sp_AIEO_GenerateGraphDBUUIDFreqs($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_GenerateGraphDBUUIDFreqs");
}

function aieo_drop_sp_AIEO_UpdateContentWordCount($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_UpdateContentWordCount");
}

function aieo_drop_sp_AIEO_UpdateContentOutgoingLinks($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_UpdateContentOutgoingLinks");
}

function aieo_drop_sp_AIEO_UpdateContentIntroPlain($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_UpdateContentIntroPlain");
}

function aieo_drop_sp_AIEO_Include_Products_Without_Sales()
{
  global $wpdb;
  $wpdb->query("DROP PROCEDURE IF EXISTS AIEO_Include_Products_Without_Sales");
}

function aieo_drop_sp_AIEO_PopulateProductNeeds($sp_prefix)
{
  aieo_drop_sp_common($sp_prefix, "AIEO_PopulateProductNeeds");
}

function aieo_create_sp_AIEO_PopulateProductNeeds($sp_prefix)
{
  global $wpdb;

  
  $needs_attribute = get_option('aieo_core_attribute_need', 'pa_need');

  
  $sp_prefix = sanitize_text_field($sp_prefix);
  if (!preg_match('/^\w+$/', $sp_prefix)) {
    wp_die(esc_html__('Invalid input detected in prefix', 'ai-eshop-optimizer'));
  }

  $procedure_name = $sp_prefix . 'AIEO_PopulateProductNeeds';

  $sql = "
CREATE PROCEDURE `{$procedure_name}`()
SQL SECURITY INVOKER
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Step 1: Build parent needs with correct ordering
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_needs;
    CREATE TEMPORARY TABLE tmp_parent_needs (
        parent_id      INT NOT NULL PRIMARY KEY,
        ids_csv        VARCHAR(2000) NULL,
        primary_id     INT NULL,
        secondary_id   INT NULL,
        rest_ids       VARCHAR(1800) NULL,
        primary_name   VARCHAR(255) NULL,
        secondary_name VARCHAR(255) NULL,
        other_names    TEXT NULL
    ) ENGINE=InnoDB;

    -- Get parent products with their needs ordered by term name
    INSERT INTO tmp_parent_needs (parent_id, ids_csv, primary_id, secondary_id, rest_ids)
    SELECT
        pv.product_id AS parent_id,

        -- Get all need IDs ordered by term name
        GROUP_CONCAT(
            tt.term_id
            ORDER BY t.name ASC
            SEPARATOR ','
        ) AS ids_csv,

        -- Extract first ID as primary
        CAST(SUBSTRING_INDEX(
            GROUP_CONCAT(
                tt.term_id
                ORDER BY t.name ASC
                SEPARATOR ','
            ),
            ',', 1
        ) AS UNSIGNED) AS primary_id,

        -- Extract second ID as secondary
        CASE
            WHEN (LENGTH(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','))
                  - LENGTH(REPLACE(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','), ',', ''))) >= 1
            THEN CAST(SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                        GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','),
                        ',', 2
                    ),
                    ',', -1
                ) AS UNSIGNED)
            ELSE NULL
        END AS secondary_id,

        -- Extract remaining IDs (3rd onwards)
        CASE
            WHEN (LENGTH(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','))
                  - LENGTH(REPLACE(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','), ',', ''))) >= 2
            THEN SUBSTRING(
                    GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','),
                    LOCATE(',', GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','),
                           LOCATE(',', GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ',')) + 1) + 1
                )
            ELSE ''
        END AS rest_ids

    FROM {$wpdb->prefix}aieo_core_product_vitals pv
    -- A published parent, simple or variable. Not pv.status: the sellable flag of the pipeline
    -- (run_stage_sellable_status_flag()) sets the status of a variable parent to 0, so a build run
    -- after it (the needs editor, the chat trainer) skipped every variable product (11.3.3).
    JOIN {$wpdb->prefix}posts p
        ON p.ID = pv.product_id
        AND pv.product_id = pv.parent_id  -- parents only
        AND p.post_status = 'publish'
    JOIN {$wpdb->prefix}term_relationships tr
        ON tr.object_id = p.ID
    JOIN {$wpdb->prefix}term_taxonomy tt
        ON tt.term_taxonomy_id = tr.term_taxonomy_id
        AND tt.taxonomy = '{$needs_attribute}'
    JOIN {$wpdb->prefix}terms t
        ON t.term_id = tt.term_id
    GROUP BY pv.product_id
    HAVING ids_csv IS NOT NULL;

    -- Step 2: Resolve primary/secondary names
    UPDATE tmp_parent_needs n
    LEFT JOIN {$wpdb->prefix}terms t1 ON t1.term_id = n.primary_id
    LEFT JOIN {$wpdb->prefix}terms t2 ON t2.term_id = n.secondary_id
    SET n.primary_name   = COALESCE(t1.name, ''),
        n.secondary_name = COALESCE(t2.name, '');

    -- Step 3: Build other_names from rest_ids list
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_other;
    CREATE TEMPORARY TABLE tmp_parent_other (
        parent_id   INT NOT NULL PRIMARY KEY,
        other_names TEXT NULL
    ) ENGINE=InnoDB;

    INSERT INTO tmp_parent_other (parent_id, other_names)
    SELECT
        n.parent_id,
        GROUP_CONCAT(t.name ORDER BY FIND_IN_SET(t.term_id, n.rest_ids) SEPARATOR ', ')
    FROM tmp_parent_needs n
    JOIN {$wpdb->prefix}term_taxonomy tt
        ON tt.taxonomy = '{$needs_attribute}'
        AND n.rest_ids <> ''
        AND FIND_IN_SET(tt.term_id, n.rest_ids) > 0
    JOIN {$wpdb->prefix}terms t
        ON t.term_id = tt.term_id
    GROUP BY n.parent_id;

    UPDATE tmp_parent_needs n
    LEFT JOIN tmp_parent_other o ON o.parent_id = n.parent_id
    SET n.other_names = COALESCE(o.other_names, '');

    -- Step 4: Apply to PARENTS in vitals table
    UPDATE {$wpdb->prefix}aieo_core_product_vitals pv
    JOIN tmp_parent_needs n ON n.parent_id = pv.product_id
    SET
        pv.primary_need_id   = NULLIF(n.primary_id, 0),
        pv.secondary_need_id = NULLIF(n.secondary_id, 0),
        pv.other_needs_ids   = COALESCE(n.rest_ids, ''),
        pv.primary_need      = COALESCE(n.primary_name, ''),
        pv.secondary_need    = COALESCE(n.secondary_name, ''),
        pv.other_needs       = COALESCE(n.other_names, '')
    WHERE pv.parent_id = pv.product_id;

    -- Step 5: The needs of a product live on its own row only, never on the rows of its variations
    -- (the owner, 11.3.3: the association has to happen on the parent product); readers take them
    -- from the parent. Builds before 11.3.3 copied them onto every variation: each build clears them.
    UPDATE {$wpdb->prefix}aieo_core_product_vitals
    SET
        primary_need_id   = NULL,
        secondary_need_id = NULL,
        other_needs_ids   = NULL,
        primary_need      = NULL,
        secondary_need    = NULL,
        other_needs       = NULL
    WHERE product_id <> parent_id
        AND (primary_need_id IS NOT NULL OR primary_need IS NOT NULL OR other_needs_ids IS NOT NULL);

    -- Cleanup
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_other;
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_needs;

    COMMIT;
END";

  $wpdb->query($sql);
}

function aieo_drop_function_AIEO_StripHTML()
{
  global $wpdb;
  $wpdb->query("DROP FUNCTION IF EXISTS AIEO_StripHTML");
}

function aieo_drop_function_AIEO_CountWords()
{
  global $wpdb;
  $wpdb->query("DROP FUNCTION IF EXISTS AIEO_CountWords");
}

function aieo_drop_function_AIEO_CountLinks()
{
  global $wpdb;
  $wpdb->query("DROP FUNCTION IF EXISTS AIEO_CountLinks");
}

function aieo_create_sessions_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_sessions';

    
    aieo_create_session_statics_table();

    
    $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'") === $table_name;

    if ($table_exists) {
        
        $has_old_structure = $wpdb->get_var(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
             AND TABLE_NAME = '{$table_name}'
             AND COLUMN_NAME = 'user_agent'"
        );

        if ($has_old_structure) {
            
            aieo_migrate_sessions_to_statics();

            
            $wpdb->query("ALTER TABLE {$table_name} DROP COLUMN IF EXISTS user_id");
            $wpdb->query("ALTER TABLE {$table_name} DROP COLUMN IF EXISTS user_agent");
            $wpdb->query("ALTER TABLE {$table_name} DROP COLUMN IF EXISTS device_type");
            $wpdb->query("ALTER TABLE {$table_name} DROP COLUMN IF EXISTS browser");
            $wpdb->query("ALTER TABLE {$table_name} DROP COLUMN IF EXISTS os");
            $wpdb->query("ALTER TABLE {$table_name} DROP COLUMN IF EXISTS referrer");
        }

        
        
        
        
        if ( ! $wpdb->get_var( "SHOW COLUMNS FROM {$table_name} LIKE 'event_id'" ) ) {
            $wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN event_id VARCHAR(36) DEFAULT NULL AFTER id" );
            $wpdb->query( "ALTER TABLE {$table_name} ADD UNIQUE KEY uq_event_id (event_id)" );
        }
    } else {
        
        $charset_collate = aieo_get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            event_id VARCHAR(36) DEFAULT NULL COMMENT 'Client-minted id for idempotent INSERT IGNORE (Fix-A §8.2)',
            session_id VARCHAR(255) NOT NULL,
            user_id BIGINT UNSIGNED DEFAULT NULL,
            event_type VARCHAR(50) NOT NULL,
            product_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Extracted from event_data',
            product_name VARCHAR(512) DEFAULT NULL,
            category_id BIGINT(20) UNSIGNED DEFAULT NULL,
            category_name VARCHAR(512) DEFAULT NULL COMMENT 'Decoded category name',
            scroll_depth TINYINT(3) UNSIGNED DEFAULT NULL COMMENT '0-100%',
            click_count SMALLINT(5) UNSIGNED DEFAULT NULL,

            -- SEQUENCE & POSITION ANALYTICS (for order analysis)
            event_sequence INT(10) UNSIGNED DEFAULT NULL COMMENT 'Position in session (1st, 2nd, 3rd event)',
            is_first_event TINYINT(1) DEFAULT 0 COMMENT '1 if landing page/product',
            is_last_event TINYINT(1) DEFAULT 0 COMMENT '1 if exit page/product',
            is_only_event TINYINT(1) DEFAULT 0 COMMENT '1 if bounce (first AND last)',

            -- TIME & ATTENTION ANALYTICS (for JLW/VLW calculations)
            time_on_page INT(10) UNSIGNED DEFAULT NULL COMMENT 'Seconds spent on this page/product',
            visit_number SMALLINT(5) UNSIGNED DEFAULT NULL COMMENT 'Nth visit to this product in session (hesitation)',

            -- CONVERSION & CHECKOUT TRACKING
            order_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'WooCommerce order ID if session led to purchase',
            checkout_step VARCHAR(50) DEFAULT NULL COMMENT 'cart, billing, shipping, payment, order_received',

            event_data LONGTEXT,
            page_url VARCHAR(512),
            timestamp BIGINT UNSIGNED NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_event_id (event_id),
            -- DB #33 index trim: idx_session_id(session_id) and
            -- idx_product_id(product_id) are intentionally NOT declared here.
            -- Both are pure left-prefixes of wider composites that already
            -- serve those single-column lookups (idx_session_product /
            -- idx_sequence cover session_id; idx_product_event covers
            -- product_id), so they were write amplification with no read
            -- benefit on this ~5s-per-visitor INSERT table. Existing installs
            -- get them dropped by aieo_run_db_migrations() (db_version 1).
            INDEX idx_user_id (user_id),
            INDEX idx_event_type (event_type),
            INDEX idx_timestamp (timestamp),
            INDEX idx_created_at (created_at),
            INDEX idx_product_event (product_id, event_type, timestamp),
            INDEX idx_category_id (category_id),
            INDEX idx_session_product (session_id, product_id, event_type),
            INDEX idx_sequence (session_id, event_sequence),
            INDEX idx_first_page (product_id, is_first_event),
            INDEX idx_last_page (product_id, is_last_event),
            INDEX idx_bounce (product_id, is_only_event),
            INDEX idx_order_id (order_id),
            INDEX idx_checkout_step (checkout_step),
            INDEX idx_hesitation (product_id, visit_number)
        ) {$charset_collate};";

        $wpdb->query($sql);
    }
}

function aieo_migrate_sessions_to_statics()
{
    global $wpdb;
    $sessions_table = $wpdb->prefix . 'aieo_sessions';
    $statics_table = $wpdb->prefix . 'aieo_sessions_statics';

    
    
    $wpdb->query("
        INSERT IGNORE INTO {$statics_table}
            (session_id, user_agent, device_type, browser, os, landing_page, referrer, first_timestamp, created_at)
        SELECT
            session_id,
            user_agent,
            device_type,
            browser,
            os,
            page_url AS landing_page,
            referrer,
            MIN(timestamp) AS first_timestamp,
            MIN(created_at) AS created_at
        FROM {$sessions_table}
        GROUP BY session_id
    ");
}

function aieo_create_session_statics_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_sessions_statics';
    $charset_collate = aieo_get_charset_collate();

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        session_id VARCHAR(255) NOT NULL UNIQUE,
        user_id BIGINT UNSIGNED DEFAULT NULL,
        user_agent TEXT,
        device_type VARCHAR(50),
        browser VARCHAR(100),
        os VARCHAR(100),
        landing_page VARCHAR(512),
        referrer VARCHAR(512),
        entry_channel VARCHAR(40) NOT NULL DEFAULT '',
        entry_source VARCHAR(80) NOT NULL DEFAULT '',
        entry_campaign VARCHAR(120) NOT NULL DEFAULT '',
        entry_medium VARCHAR(40) NOT NULL DEFAULT '',
        entry_term VARCHAR(80) NOT NULL DEFAULT '',
        entry_content VARCHAR(80) NOT NULL DEFAULT '',
        entry_campaign_id BIGINT UNSIGNED DEFAULT NULL,
        first_timestamp BIGINT UNSIGNED NOT NULL,
        last_timestamp BIGINT UNSIGNED DEFAULT NULL,
        total_events INT(10) UNSIGNED DEFAULT 0,
        total_product_views INT(10) UNSIGNED DEFAULT 0,
        total_add_to_cart INT(10) UNSIGNED DEFAULT 0,
        total_scroll_events INT(10) UNSIGNED DEFAULT 0,
        total_click_events INT(10) UNSIGNED DEFAULT 0,
        session_duration_seconds INT(10) UNSIGNED DEFAULT 0,
        order_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'WC order id if session led to a purchase',
        order_total DECIMAL(12,2) DEFAULT NULL COMMENT 'WC order grand total snapshot',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_device_type (device_type),
        INDEX idx_first_timestamp (first_timestamp),
        INDEX idx_created_at (created_at),
        INDEX idx_updated_at (updated_at),
        INDEX idx_entry_channel (entry_channel),
        INDEX aieo_idx_ss_camp_id (entry_campaign_id),
        INDEX aieo_idx_ss_order_id (order_id)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_drop_sessions_table()
{
    global $wpdb;

    
    $sessions_table = $wpdb->prefix . 'aieo_sessions';
    $wpdb->query("DROP TABLE IF EXISTS {$sessions_table}");

    
    $statics_table = $wpdb->prefix . 'aieo_sessions_statics';
    $wpdb->query("DROP TABLE IF EXISTS {$statics_table}");

    
    $archive_table = $wpdb->prefix . 'aieo_sessions_archive';
    $wpdb->query("DROP TABLE IF EXISTS {$archive_table}");

    $archive_statics_table = $wpdb->prefix . 'aieo_sessions_statics_archive';
    $wpdb->query("DROP TABLE IF EXISTS {$archive_statics_table}");
}

function aieo_create_session_archive_tables()
{
    global $wpdb;
    $charset_collate = aieo_get_charset_collate();

    
    $archive_table = $wpdb->prefix . 'aieo_sessions_archive';
    $sql_archive = "CREATE TABLE IF NOT EXISTS {$archive_table} (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        session_id VARCHAR(255) NOT NULL,
        user_id BIGINT UNSIGNED DEFAULT NULL,
        event_type VARCHAR(50) NOT NULL,
        product_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Extracted from event_data',
        product_name VARCHAR(512) DEFAULT NULL,
        category_id BIGINT(20) UNSIGNED DEFAULT NULL,
        category_name VARCHAR(512) DEFAULT NULL COMMENT 'Decoded category name',
        scroll_depth TINYINT(3) UNSIGNED DEFAULT NULL COMMENT '0-100%',
        click_count SMALLINT(5) UNSIGNED DEFAULT NULL,

        -- SEQUENCE & POSITION ANALYTICS (for order analysis)
        event_sequence INT(10) UNSIGNED DEFAULT NULL COMMENT 'Position in session (1st, 2nd, 3rd event)',
        is_first_event TINYINT(1) DEFAULT 0 COMMENT '1 if landing page/product',
        is_last_event TINYINT(1) DEFAULT 0 COMMENT '1 if exit page/product',
        is_only_event TINYINT(1) DEFAULT 0 COMMENT '1 if bounce (first AND last)',

        -- TIME & ATTENTION ANALYTICS (for JLW/VLW calculations)
        time_on_page INT(10) UNSIGNED DEFAULT NULL COMMENT 'Seconds spent on this page/product',
        visit_number SMALLINT(5) UNSIGNED DEFAULT NULL COMMENT 'Nth visit to this product in session (hesitation)',

        -- CONVERSION & CHECKOUT TRACKING
        order_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'WooCommerce order ID if session led to purchase',
        checkout_step VARCHAR(50) DEFAULT NULL COMMENT 'cart, billing, shipping, payment, order_received',

        event_data LONGTEXT,
        page_url VARCHAR(512),
        timestamp BIGINT UNSIGNED NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        archived_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        -- DB #33 index trim: idx_session_id / idx_product_id omitted (left-prefix
        -- duplicates of idx_sequence / idx_product_event). Existing installs get
        -- them dropped by aieo_run_db_migrations() (db_version 1).
        INDEX idx_user_id (user_id),
        INDEX idx_event_type (event_type),
        INDEX idx_timestamp (timestamp),
        INDEX idx_created_at (created_at),
        INDEX idx_archived_at (archived_at),
        INDEX idx_product_event (product_id, event_type, timestamp),
        INDEX idx_category_id (category_id),
        INDEX idx_sequence (session_id, event_sequence),
        INDEX idx_first_page (product_id, is_first_event),
        INDEX idx_last_page (product_id, is_last_event),
        INDEX idx_bounce (product_id, is_only_event),
        INDEX idx_order_id (order_id),
        INDEX idx_checkout_step (checkout_step),
        INDEX idx_hesitation (product_id, visit_number)
    ) {$charset_collate};";

    $wpdb->query($sql_archive);

    
    $archive_statics_table = $wpdb->prefix . 'aieo_sessions_statics_archive';
    $sql_archive_statics = "CREATE TABLE IF NOT EXISTS {$archive_statics_table} (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        session_id VARCHAR(255) NOT NULL,
        user_id BIGINT UNSIGNED DEFAULT NULL,
        user_agent TEXT,
        device_type VARCHAR(50),
        browser VARCHAR(100),
        os VARCHAR(100),
        landing_page VARCHAR(512),
        referrer VARCHAR(512),
        first_timestamp BIGINT UNSIGNED NOT NULL,
        last_timestamp BIGINT UNSIGNED DEFAULT NULL,
        total_events INT(10) UNSIGNED DEFAULT 0,
        total_product_views INT(10) UNSIGNED DEFAULT 0,
        total_add_to_cart INT(10) UNSIGNED DEFAULT 0,
        total_scroll_events INT(10) UNSIGNED DEFAULT 0,
        total_click_events INT(10) UNSIGNED DEFAULT 0,
        session_duration_seconds INT(10) UNSIGNED DEFAULT 0,
        created_at DATETIME,
        updated_at DATETIME,
        archived_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_device_type (device_type),
        INDEX idx_first_timestamp (first_timestamp),
        INDEX idx_created_at (created_at),
        INDEX idx_archived_at (archived_at)
    ) {$charset_collate};";

    $wpdb->query($sql_archive_statics);
}

function aieo_migrate_session_journeys_table()
{
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_session_journeys';

    $cols = $wpdb->get_results($wpdb->prepare(
        "SELECT COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS
          WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s",
        $table
    ), ARRAY_A);
    if (!$cols) {
        return; 
    }
    $have = array();
    foreach ($cols as $c) {
        $have[strtolower($c['COLUMN_NAME'])] = (string) $c['COLUMN_TYPE'];
    }

    if (!isset($have['object_type'])) {
        
        $wpdb->query("ALTER TABLE `{$table}` ADD COLUMN object_type VARCHAR(32) NOT NULL DEFAULT 'product'");
    }
    if (isset($have['event_type']) && false === strpos($have['event_type'], 'content_view')) {
        
        $wpdb->query(
            "ALTER TABLE `{$table}` MODIFY COLUMN event_type
             ENUM('product_view','add_to_cart','remove_from_cart','add_to_wishlist','content_view','page_view') NOT NULL"
        );
    }
}

function aieo_create_session_journeys_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_session_journeys';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        session_id VARCHAR(255) NOT NULL,
        user_id BIGINT(20) UNSIGNED DEFAULT NULL,
        /* object_id in all but name. Historically product-only, so the column
           keeps its name for every existing consumer; object_type says what it
           points at. WP post ids are globally unique across types, so the id
           alone is already unambiguous. */
        product_id BIGINT(20) UNSIGNED NOT NULL,
        object_type VARCHAR(32) NOT NULL DEFAULT 'product',
        product_name VARCHAR(512) DEFAULT NULL,
        event_type ENUM('product_view','add_to_cart','remove_from_cart','add_to_wishlist','content_view','page_view') NOT NULL,
        event_timestamp BIGINT(20) UNSIGNED NOT NULL,
        event_sequence INT(10) UNSIGNED NOT NULL,
        total_events_in_session INT(10) UNSIGNED NOT NULL,
        is_first_event TINYINT(1) DEFAULT 0,
        is_last_event TINYINT(1) DEFAULT 0,
        journey_position DECIMAL(5,4) DEFAULT NULL,
        time_on_page INT(10) UNSIGNED DEFAULT 0,
        max_scroll_depth TINYINT(3) UNSIGNED DEFAULT 0,
        click_count SMALLINT(5) UNSIGNED DEFAULT 0,
        device_type VARCHAR(50) DEFAULT NULL,
        session_duration INT(10) UNSIGNED DEFAULT 0,
        session_event_count INT(10) UNSIGNED DEFAULT 0,
        did_add_to_cart TINYINT(1) DEFAULT 0,
        add_to_cart_sequence INT(10) DEFAULT NULL,
        time_to_cart INT(10) DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_session_id (session_id),
        KEY idx_product_id (product_id),
        KEY idx_event_type (event_type),
        KEY idx_sequence (session_id, event_sequence),
        KEY idx_first_page (product_id, is_first_event),
        KEY idx_last_page (product_id, is_last_event),
        KEY idx_conversion (product_id, did_add_to_cart),
        KEY idx_timestamp (event_timestamp),
        KEY idx_created_at (created_at),
        KEY idx_product_event (product_id, event_type, event_timestamp)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_create_product_session_metrics_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_product_session_metrics';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        product_id BIGINT(20) UNSIGNED NOT NULL,
        total_views INT(10) UNSIGNED DEFAULT 0,
        unique_sessions_viewed INT(10) UNSIGNED DEFAULT 0,
        avg_time_on_page DECIMAL(10,2) DEFAULT 0,
        avg_engaged_sec DECIMAL(10,2) DEFAULT 0,
        avg_scroll_depth DECIMAL(5,2) DEFAULT 0,
        avg_clicks_per_view DECIMAL(5,2) DEFAULT 0,
        times_first_page INT(10) UNSIGNED DEFAULT 0,
        times_last_page INT(10) UNSIGNED DEFAULT 0,
        times_only_page INT(10) UNSIGNED DEFAULT 0,
        total_add_to_cart INT(10) UNSIGNED DEFAULT 0,
        unique_sessions_added_to_cart INT(10) UNSIGNED DEFAULT 0,
        view_to_cart_rate DECIMAL(5,4) DEFAULT 0,
        avg_time_to_cart DECIMAL(10,2) DEFAULT 0,
        view_to_cart_rank INT(10) UNSIGNED DEFAULT 0,
        engagement_score DECIMAL(10,2) DEFAULT 0,
        engagement_rank INT(10) UNSIGNED DEFAULT 0,
        bounce_rate DECIMAL(5,4) DEFAULT 0,
        bounce_rank INT(10) UNSIGNED DEFAULT 0,
        last_calculated DATETIME DEFAULT CURRENT_TIMESTAMP,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY idx_product_id (product_id),
        KEY idx_view_to_cart_rate (view_to_cart_rate),
        KEY idx_engagement_score (engagement_score),
        KEY idx_bounce_rate (bounce_rate),
        KEY idx_view_to_cart_rank (view_to_cart_rank),
        KEY idx_engagement_rank (engagement_rank),
        KEY idx_bounce_rank (bounce_rank),
        KEY idx_last_calculated (last_calculated)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    
    
    
    
    
    
    
    
    
    $has = $wpdb->get_var($wpdb->prepare(
        "SHOW COLUMNS FROM {$table_name} LIKE %s",
        'avg_engaged_sec'
    ));
    if (!$has) {
        
        $wpdb->query("ALTER TABLE {$table_name} ADD COLUMN avg_engaged_sec DECIMAL(10,2) DEFAULT 0 AFTER avg_time_on_page");
    }
}

function aieo_create_chat_embeddings_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_chat_embeddings';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        post_id BIGINT(20) UNSIGNED NOT NULL,
        post_type VARCHAR(50) NOT NULL DEFAULT 'product',
        engine_type VARCHAR(50) NOT NULL DEFAULT 'voyage-2',
        deduplicated TINYINT(1) NOT NULL DEFAULT 0,
        embedding LONGTEXT NOT NULL,
        content_hash VARCHAR(32) NOT NULL,
        failure_count INT(11) NULL DEFAULT 0 COMMENT 'Number of failed embedding generation attempts',
        last_failure_reason VARCHAR(500) NULL COMMENT 'Reason for last failure',
        last_failure_date DATETIME NULL COMMENT 'Timestamp of last failure',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY post_id_type_engine_dedup (post_id, post_type, engine_type, deduplicated),
        KEY post_type (post_type),
        KEY engine_type (engine_type),
        KEY deduplicated (deduplicated),
        KEY created_at (created_at),
        KEY lookup_idx (post_id, engine_type, deduplicated),
        KEY stats_idx (engine_type, deduplicated, created_at),
        KEY failure_idx (failure_count, last_failure_date)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_drop_chat_embeddings_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_chat_embeddings';

    
    $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'");

    if ($table_exists) {
        
        aieo_drop_index_if_exists('aieo_idx_chat_emb_post_id_type', $table_name);
        aieo_drop_index_if_exists('aieo_idx_chat_emb_post_type', $table_name);
        aieo_drop_index_if_exists('aieo_idx_chat_emb_engine_type', $table_name);
        aieo_drop_index_if_exists('aieo_idx_chat_emb_created_at', $table_name);
    }

    
    $wpdb->query("DROP TABLE IF EXISTS {$table_name}");
}

function aieo_create_chat_history_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_chat_history';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        conversation_id VARCHAR(64) NOT NULL,
        session_id VARCHAR(64) NOT NULL,
        user_id BIGINT(20) UNSIGNED DEFAULT NULL,
        user_message TEXT NOT NULL,
        assistant_message TEXT NOT NULL,
        model_used VARCHAR(100) DEFAULT 'claude-3-5-sonnet-20241022',
        embedder_used VARCHAR(50) DEFAULT NULL,
        products_found TEXT DEFAULT NULL,
        potential_suggestions_found TEXT DEFAULT NULL,
        user_rating TINYINT UNSIGNED DEFAULT NULL COMMENT 'User feedback rating 0-100, only stored if changed from default 50',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY conversation_id (conversation_id),
        KEY session_id (session_id),
        KEY user_id (user_id),
        KEY created_at (created_at),
        KEY user_rating (user_rating)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_drop_chat_history_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_chat_history';

    
    $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$table_name}'");

    if ($table_exists) {
        
        aieo_drop_index_if_exists('aieo_idx_chat_hist_conversation_id', $table_name);
        aieo_drop_index_if_exists('aieo_idx_chat_hist_session_id', $table_name);
        aieo_drop_index_if_exists('aieo_idx_chat_hist_user_id', $table_name);
        aieo_drop_index_if_exists('aieo_idx_chat_hist_created_at', $table_name);
    }

    
    $wpdb->query("DROP TABLE IF EXISTS {$table_name}");
}

function aieo_create_ai_models_table()
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'aieo_ai_models';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        provider varchar(50) NOT NULL,
        model_id varchar(100) NOT NULL,
        model_name varchar(255) NOT NULL,
        model_type varchar(50) NOT NULL DEFAULT 'chat',
        description text DEFAULT NULL,
        max_tokens int(11) DEFAULT NULL,
        dimensions int(11) DEFAULT NULL,
        is_recommended tinyint(1) DEFAULT 0,
        is_active tinyint(1) DEFAULT 1,
        sort_order int(11) DEFAULT 0,
        metadata longtext DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY provider_model (provider, model_id),
        KEY model_type (model_type),
        KEY is_active (is_active),
        KEY sort_order (sort_order)
    ) {$charset_collate};";

    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    aieo_dbdelta($sql); 

    
    $count = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}");
    if ($count == 0) {
        aieo_insert_default_ai_models($table_name);
    }
}

function aieo_insert_default_ai_models($table_name)
{
    global $wpdb;

    $models = array(
        
        array('anthropic', 'claude-opus-4-5-20251101', 'Claude Opus 4.5', 'chat', 'Most intelligent model - hybrid reasoning', 16384, null, 1, 1, 10),
        array('anthropic', 'claude-sonnet-4-5-20250929', 'Claude Sonnet 4.5', 'chat', 'Balanced performance and speed', 16384, null, 0, 1, 20),
        array('anthropic', 'claude-haiku-4-5-20251001', 'Claude Haiku 4.5', 'chat', 'Fastest and most economical', 8192, null, 0, 1, 30),
        array('anthropic', 'claude-opus-4-1-20250805', 'Claude Opus 4.1', 'chat', 'Advanced reasoning capabilities', 8192, null, 0, 1, 40),
        array('anthropic', 'claude-sonnet-4-20250514', 'Claude Sonnet 4', 'chat', 'Balanced Claude 4 model', 8192, null, 0, 1, 50),
        array('anthropic', 'claude-3-5-sonnet-20241022', 'Claude 3.5 Sonnet', 'chat', 'Previous generation model', 8192, null, 0, 1, 60),
        array('anthropic', 'claude-3-5-haiku-20241022', 'Claude 3.5 Haiku', 'chat', 'Fast and economical', 8192, null, 0, 1, 70),

        
        array('openai', 'gpt-4o', 'GPT-4o', 'chat', 'Most capable multimodal model', 16384, null, 1, 1, 10),
        array('openai', 'gpt-4o-mini', 'GPT-4o Mini', 'chat', 'Fast and economical', 16384, null, 0, 1, 20),
        array('openai', 'gpt-4-turbo', 'GPT-4 Turbo', 'chat', 'GPT-4 Turbo with vision', 4096, null, 0, 1, 30),
        array('openai', 'o1', 'O1', 'chat', 'Advanced reasoning model', 32768, null, 0, 1, 40),
        array('openai', 'o1-mini', 'O1 Mini', 'chat', 'Reasoning model - economical', 16384, null, 0, 1, 50),

        
        array('openai', 'text-embedding-3-large', 'Text Embedding 3 Large', 'embedding', 'Best quality embeddings', null, 3072, 1, 1, 10),
        array('openai', 'text-embedding-3-small', 'Text Embedding 3 Small', 'embedding', 'Good quality, lower cost', null, 1536, 0, 1, 20),
        array('openai', 'text-embedding-ada-002', 'Text Embedding Ada 002', 'embedding', 'Legacy embedding model', null, 1536, 0, 1, 30),

        
        
        
        
        array('voyage', 'voyage-4-large', 'Voyage 4 Large', 'embedding', 'Best retrieval quality, multilingual', null, 1024, 0, 1, 10),
        array('voyage', 'voyage-4', 'Voyage 4', 'embedding', 'General purpose, multilingual', null, 1024, 1, 1, 20),
        array('voyage', 'voyage-4-lite', 'Voyage 4 Lite', 'embedding', 'Lowest cost and latency', null, 1024, 0, 1, 30),
        array('voyage', 'voyage-code-4', 'Voyage Code 4', 'embedding', 'Code and technical documentation', null, 1024, 0, 1, 40),
        array('voyage', 'voyage-finance-2', 'Voyage Finance 2', 'embedding', 'Financial text', null, 1024, 0, 1, 50),
        array('voyage', 'voyage-law-2', 'Voyage Law 2', 'embedding', 'Legal text', null, 1024, 0, 1, 60),
        array('voyage', 'voyage-3.5', 'Voyage 3.5', 'embedding', 'Previous generation', null, 1024, 0, 1, 70),
        array('voyage', 'voyage-3.5-lite', 'Voyage 3.5 Lite', 'embedding', 'Previous generation, low cost', null, 1024, 0, 1, 80),
        array('voyage', 'voyage-3-large', 'Voyage 3 Large', 'embedding', 'Previous generation', null, 1024, 0, 1, 90),
        array('voyage', 'voyage-3', 'Voyage 3', 'embedding', 'Previous generation', null, 1024, 0, 1, 100),
        array('voyage', 'voyage-3-lite', 'Voyage 3 Lite', 'embedding', 'Previous generation, low cost', null, 512, 0, 1, 110),
        array('voyage', 'voyage-code-3', 'Voyage Code 3', 'embedding', 'Previous generation, code', null, 1024, 0, 1, 120),
        array('voyage', 'voyage-multilingual-2', 'Voyage Multilingual 2', 'embedding', 'Previous generation, multilingual', null, 1024, 0, 1, 130),
        array('voyage', 'voyage-large-2', 'Voyage Large 2', 'embedding', 'Legacy model', null, 1536, 0, 1, 140),
        array('voyage', 'voyage-2', 'Voyage 2', 'embedding', 'Legacy model', null, 1024, 0, 1, 150),
    );

    
    
    
    
    
    
    foreach ($models as $model) {
        $max_tokens_ph = ($model[5] === null) ? 'NULL' : '%d';
        $dimensions_ph = ($model[6] === null) ? 'NULL' : '%d';

        $params = array($model[0], $model[1], $model[2], $model[3], $model[4]);
        if ($model[5] !== null) { $params[] = (int) $model[5]; }
        if ($model[6] !== null) { $params[] = (int) $model[6]; }
        $params[] = (int) $model[7];
        $params[] = (int) $model[8];
        $params[] = (int) $model[9];

        
        $wpdb->query($wpdb->prepare(
            "INSERT IGNORE INTO {$table_name}
                (provider, model_id, model_name, model_type, description, max_tokens, dimensions, is_recommended, is_active, sort_order)
             VALUES (%s, %s, %s, %s, %s, {$max_tokens_ph}, {$dimensions_ph}, %d, %d, %d)",
            $params
        ));
    }
}

function aieo_create_product_recommendations_table() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'aieo_product_recommendations';
    $charset_collate = aieo_get_charset_collate();

    
    $table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) === $table_name;
    if ( $table_exists ) {
        return false;
    }

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        `product_id` bigint(20) UNSIGNED NOT NULL,
        `relation_type` varchar(50) NOT NULL,
        `recommended_products` longtext DEFAULT NULL,
        `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `expires_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `idx_product_relation` (`product_id`, `relation_type`),
        KEY `idx_relation_type` (`relation_type`),
        KEY `idx_expires_at` (`expires_at`)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    aieo_dbdelta( $sql ); 

    return true;
}

function aieo_create_reco_reverse_refs_table() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'aieo_reco_reverse_refs';
    $charset_collate = aieo_get_charset_collate();

    
    $table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) === $table_name;
    if ( $table_exists ) {
        return false;
    }

    
    
    
    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        `referenced_product_id` BIGINT(20) UNSIGNED NOT NULL,
        `cache_entry_id`        BIGINT(20) UNSIGNED NOT NULL,
        PRIMARY KEY (`referenced_product_id`, `cache_entry_id`),
        KEY `idx_cache_entry` (`cache_entry_id`)
    ) {$charset_collate};";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    aieo_dbdelta( $sql ); 

    return true;
}

function aieo_create_product_need_suggestions_table() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'aieo_product_need_suggestions';
    $charset_collate = aieo_get_charset_collate();

    
    $table_exists = $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) === $table_name;
    if ( $table_exists ) {
        return false;
    }

    $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
        `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        `product_id` bigint(20) UNSIGNED NOT NULL COMMENT 'Source product ID',
        `suggested_products` text DEFAULT NULL COMMENT 'JSON array of up to 6 product IDs sorted by sales',
        `match_score` int(11) DEFAULT 0 COMMENT 'Total needs match count for best suggestion',
        `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `idx_product_id` (`product_id`)
    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    aieo_dbdelta( $sql ); 

    return true;
}