<?php

if (!defined('ABSPATH')) {
    exit;
}
require_once dirname( __DIR__, 2 ) . '/class-aieo-order-store.php'; 

function aieo_dmm_create_tables() {
    global $wpdb;

    
    
    
    
    
    if (function_exists('aieo_dmm_forget_known_tables')) {
        aieo_dmm_forget_known_tables();
    }

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

    try {
        aieo_dmm_create_role_prices_table();
        aieo_dmm_create_brand_discounts_table();
        aieo_dmm_create_brand_gift_offers_table();
        
        
        
        
        
        
        aieo_dmm_create_offers_table();
        
        
        
        
        
        aieo_dmm_create_offer_attributions_table();
        aieo_dmm_create_loyalty_classes_table();
        aieo_dmm_create_loyalty_assignments_table();
        aieo_dmm_create_no_free_shipping_table();
        aieo_dmm_create_branches_table();
        aieo_dmm_create_branch_scan_log_table();
        aieo_dmm_create_product_needs_table();
        aieo_dmm_create_discount_schedules_table();
        aieo_dmm_create_export_jobs_table();
        aieo_dmm_create_feed_configs_table();
        
        
        
        
        aieo_dmm_create_feed_terms_table();
        aieo_dmm_create_feed_cat_map_table();
        
        
        aieo_dmm_create_feed_rows_table();
        aieo_dmm_create_feed_row_terms_table();
        aieo_dmm_create_feed_image_queue_table();
        
        
        
        
        aieo_dmm_create_feed_run_log_table();
        aieo_dmm_create_cat_match_register_table();
        aieo_dmm_create_stock_word_register_table();
        aieo_dmm_create_product_fp_table();
        aieo_dmm_create_user_fp_table();
        aieo_dmm_create_complementary_table();
        aieo_dmm_create_swatches_cache_table();
        aieo_dmm_create_swatches_variation_map_table();
        
        
        
        
        aieo_dmm_create_color_attributes_table();
        aieo_dmm_create_category_extras_table();
        
        
        
        aieo_dmm_create_brand_extras_table();
        
        
        
        
        
        
        aieo_dmm_create_term_relations_table();
        
        
        
        
        
        
        aieo_dmm_migrate_category_extras_to_term_relations();
        
        
        
        
        
        
        aieo_dmm_create_supplier_extras_table();
        
        
        
        
        
        
        aieo_dmm_migrate_brand_rows_from_category_extras();
        
        
        
        
        
        aieo_dmm_create_need_extras_table();
        
        
        
        
        
        
        aieo_dmm_create_supplier_preorders_table();
        
        
        
        
        
        aieo_dmm_create_preorder_worksheet_table();
        aieo_dmm_create_preorder_log_table();
        
        
        
        
        aieo_dmm_create_store_pipeline_tables();
        
        
        
        aieo_dmm_create_branch_stats_tables();
        
        
        
        
        aieo_dmm_backfill_legacy_branch_matchers();
        
        
        aieo_dmm_create_store_order_log_table();
        aieo_dmm_ensure_store_status_lookup();
        
        
        
        aieo_dmm_ensure_all_store_boards();
        
        
        
        
        aieo_dmm_create_physical_recovery_tables();
        
        
        
        
        aieo_dmm_migrate_branch_wide_to_narrow();
        
        
        
        
        
        
        aieo_dmm_create_cancellation_reasons_table();
        aieo_dmm_seed_default_cancellation_reasons();
        aieo_dmm_create_order_cancellations_table();
        aieo_dmm_create_withdrawal_declarations_table();
        aieo_dmm_create_order_line_refunds_table();
        aieo_dmm_create_product_behavior_metrics_table();
        aieo_dmm_create_user_behavior_metrics_table();
        
        
        
        
        
        
        
        
        aieo_dmm_create_campaigns_table();

        
        
        
        
        $google_reviews_schema = AIEO_DIR . 'includes/dmm/sql/google-reviews-schema.php';
        if (file_exists($google_reviews_schema)) {
            require_once $google_reviews_schema;
            if (function_exists('aieo_google_reviews_install_schema')) {
                aieo_google_reviews_install_schema();
            }
        }

        
        
        
        
        
        $gsc_history_schema = AIEO_DIR . 'includes/dmm/sql/gsc-history-schema.php';
        if (file_exists($gsc_history_schema)) {
            require_once $gsc_history_schema;
            if (function_exists('aieo_gsc_history_install_schema')) {
                aieo_gsc_history_install_schema();
            }
        }

        
        
        
        $exchanger_schema = AIEO_DIR . 'includes/dmm/sql/exchanger-schema.php';
        if (file_exists($exchanger_schema)) {
            require_once $exchanger_schema;
            if (function_exists('aieo_dmm_exchanger_install_schema')) {
                aieo_dmm_exchanger_install_schema();
            }
        }

        
        
        
        
        $fulfillment_schema = AIEO_DIR . 'includes/dmm/sql/fulfillment-schema.php';
        if (file_exists($fulfillment_schema)) {
            require_once $fulfillment_schema;
            if (function_exists('aieo_dmm_fulfillment_install_schema')) {
                aieo_dmm_fulfillment_install_schema();
            }
        }

        
        
        
        $shipping_rates_schema = AIEO_DIR . 'includes/dmm/sql/shipping-rates-schema.php';
        if (file_exists($shipping_rates_schema)) {
            require_once $shipping_rates_schema;
            if (function_exists('aieo_dmm_shipping_rates_install_schema')) {
                aieo_dmm_shipping_rates_install_schema();
            }
        }

        
        
        
        
        
        
        $carts_schema = AIEO_DIR . 'includes/carts/sql/carts-schema.php';
        if (file_exists($carts_schema)) {
            require_once $carts_schema;
            if (function_exists('aieo_carts_create_tables')) {
                aieo_carts_create_tables();
            }
        }

        
        
        
        
        $checkout_timing_schema = AIEO_DIR . 'includes/dmm/sql/checkout-timing-schema.php';
        if (file_exists($checkout_timing_schema)) {
            require_once $checkout_timing_schema;
            if (function_exists('aieo_checkout_timing_create_table')) {
                aieo_checkout_timing_create_table();
            }
        }

        
        
        
        $server_events_schema = AIEO_DIR . 'includes/server-events/sql/server-events-schema.php';
        if (file_exists($server_events_schema)) {
            require_once $server_events_schema;
            if (function_exists('aieo_server_events_log_create_table')) {
                aieo_server_events_log_create_table();
            }
        }

        
        
        
        
        $login_security_schema = AIEO_DIR . 'includes/dmm/sql/login-security-schema.php';
        if (file_exists($login_security_schema)) {
            require_once $login_security_schema;
            if (function_exists('aieo_login_security_create_table')) {
                aieo_login_security_create_table();
            }
        }

        
        
        
        $custom_tabs_schema = AIEO_DIR . 'includes/dmm/sql/custom-tabs-schema.php';
        if (file_exists($custom_tabs_schema)) {
            require_once $custom_tabs_schema;
            if (function_exists('aieo_dmm_custom_tabs_create_table')) {
                aieo_dmm_custom_tabs_create_table();
            }
        }

        
        
        
        
        
        
        
        $seo_schema = AIEO_DIR . 'includes/dmm/sql/seo-schema.php';
        if (file_exists($seo_schema)) {
            require_once $seo_schema;
            if (function_exists('aieo_seo_install_schema')) {
                aieo_seo_install_schema();
            }
        }

        
        
        
        $subjects_schema = AIEO_DIR . 'includes/dmm/sql/subjects-schema.php';
        if (file_exists($subjects_schema)) {
            require_once $subjects_schema;
            if (function_exists('aieo_subjects_install_schema')) {
                aieo_subjects_install_schema();
            }
        }

        aieo_dmm_migrate_legacy_data();

        
        
        
        
        
        
        aieo_dmm_migrate_legacy_to_offers();

        if (get_option('aieo_dmm_version') === false) {
            add_option('aieo_dmm_version', '1.0.0', '', 'no');
        } else {
            update_option('aieo_dmm_version', '1.0.0', 'no');
        }
    } catch (Throwable $e) {
        
        aieo_debug_log('AIEO_DMM schema init failed: ' . $e->getMessage());
    }

    
    $wpdb->suppress_errors($prev_suppress);
    if ($prev_show) {
        $wpdb->show_errors();
    }
}

function aieo_dmm_create_role_prices_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_role_prices';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `product_id` bigint unsigned NOT NULL,
        `parent_id` bigint unsigned DEFAULT NULL,
        `product_type` varchar(20) NOT NULL DEFAULT 'simple',
        `sku` varchar(100) NOT NULL DEFAULT '',
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`product_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_rp_parent', $table, '(parent_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_rp_type', $table, '(product_type)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_rp_sku', $table, '(sku)');
}

function aieo_dmm_create_brand_discounts_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_brand_discounts';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `brand_term_id` bigint unsigned NOT NULL DEFAULT 0,
        `category_term_ids` longtext DEFAULT NULL,
        `rule_operator` varchar(8) NOT NULL DEFAULT 'AND',
        `percent` decimal(5,2) NOT NULL DEFAULT '0.00',
        `date_from` date DEFAULT NULL,
        `date_to` date DEFAULT NULL,
        `enabled` tinyint(1) NOT NULL DEFAULT 1,
        `notes` varchar(255) DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_bd_brand', $table, '(brand_term_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_bd_enabled', $table, '(enabled)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_bd_window', $table, '(date_from, date_to)');

    
    aieo_dmm_ensure_column($table, 'category_term_ids', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'rule_operator',     "varchar(8) NOT NULL DEFAULT 'AND'");
    
    
    aieo_dmm_ensure_column($table, 'need_term_ids',     'longtext DEFAULT NULL');
    
    aieo_dmm_ensure_column($table, 'label',             'varchar(191) DEFAULT NULL');

    
    
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'min_subtotal',          "decimal(10,2) NOT NULL DEFAULT '0.00'");
    aieo_dmm_ensure_column($table, 'min_subtotal_scope',    "varchar(16) NOT NULL DEFAULT 'cart_total'");
    aieo_dmm_ensure_column($table, 'application_scope',     "varchar(16) NOT NULL DEFAULT 'targeted'");
    aieo_dmm_ensure_column($table, 'excluded_brand_ids',    'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_category_ids', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_need_ids',     'longtext DEFAULT NULL');
    
    
    
    aieo_dmm_ensure_column($table, 'product_ids',           'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_product_ids',  'longtext DEFAULT NULL');
    
    
    
    
    aieo_dmm_ensure_column($table, 'code_patterns',          'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_code_patterns', 'longtext DEFAULT NULL');
}

function aieo_dmm_create_brand_gift_offers_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_brand_gift_offers';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `enabled` tinyint(1) NOT NULL DEFAULT 1,
        `label` varchar(191) DEFAULT NULL,
        `brand_term_ids` longtext DEFAULT NULL,
        `min_subtotal` decimal(10,2) NOT NULL DEFAULT '0.00',
        `date_from` date DEFAULT NULL,
        `date_to` date DEFAULT NULL,
        `gift_product_ids` longtext DEFAULT NULL,
        `before_text` longtext DEFAULT NULL,
        `after_text` longtext DEFAULT NULL,
        `before_image_id` bigint unsigned DEFAULT NULL,
        `after_image_id` bigint unsigned DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_bgo_enabled', $table, '(enabled)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_bgo_window', $table, '(date_from, date_to)');

    
    aieo_dmm_ensure_column($table, 'category_term_ids', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'rule_operator',     "varchar(8) NOT NULL DEFAULT 'AND'");
    
    
    aieo_dmm_ensure_column($table, 'bar_bg_before', 'varchar(7) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'bar_bg_after',  'varchar(7) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'bar_fg_before', 'varchar(7) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'bar_fg_after',  'varchar(7) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'gift_item_bg',  'varchar(7) DEFAULT NULL');
    
    
    
    
    aieo_dmm_ensure_column($table, 'show_banner',  "tinyint(1) NOT NULL DEFAULT 1");
    aieo_dmm_ensure_column($table, 'show_rewards', "tinyint(1) NOT NULL DEFAULT 1");
    
    
    aieo_dmm_ensure_column($table, 'need_term_ids', 'longtext DEFAULT NULL');

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'gift_kind',          "varchar(16) NOT NULL DEFAULT 'product'");
    aieo_dmm_ensure_column($table, 'presentation_mode',  "varchar(16) NOT NULL DEFAULT 'auto'");
    aieo_dmm_ensure_column($table, 'show_when_pending',  "tinyint(1) NOT NULL DEFAULT 1");
    aieo_dmm_ensure_column($table, 'headline_pending',   'varchar(255) NOT NULL DEFAULT \'\'');
    aieo_dmm_ensure_column($table, 'headline_qualified', 'varchar(255) NOT NULL DEFAULT \'\'');
    aieo_dmm_ensure_column($table, 'pill_locked',        'varchar(80) NOT NULL DEFAULT \'\'');
    aieo_dmm_ensure_column($table, 'pill_available',     'varchar(80) NOT NULL DEFAULT \'\'');
    aieo_dmm_ensure_column($table, 'pill_claimed',       'varchar(80) NOT NULL DEFAULT \'\'');

    
    
    
    
    
    aieo_dmm_ensure_column($table, 'excluded_brand_ids',    'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_category_ids', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_need_ids',     'longtext DEFAULT NULL');

    
    
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'product_ids',           'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_product_ids',  'longtext DEFAULT NULL');
    
    
    
    
    aieo_dmm_ensure_column($table, 'code_patterns',          'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_code_patterns', 'longtext DEFAULT NULL');
}

function aieo_dmm_create_offers_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_offers';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `type` varchar(32) NOT NULL DEFAULT 'discount',
        `enabled` tinyint(1) NOT NULL DEFAULT 1,
        `label` varchar(191) NOT NULL DEFAULT '',
        `priority` smallint NOT NULL DEFAULT 100,
        `start_date` date DEFAULT NULL,
        `end_date` date DEFAULT NULL,
        `rule_operator` varchar(8) NOT NULL DEFAULT 'AND',
        -- Positive targeting (intersect on at least one of these to qualify) -----
        `brand_term_ids` longtext DEFAULT NULL,
        `category_term_ids` longtext DEFAULT NULL,
        `need_term_ids` longtext DEFAULT NULL,
        `product_ids` longtext DEFAULT NULL,
        -- Negative targeting (exclude these even if positive matches) ------------
        `excluded_brand_ids` longtext DEFAULT NULL,
        `excluded_category_ids` longtext DEFAULT NULL,
        `excluded_need_ids` longtext DEFAULT NULL,
        `excluded_product_ids` longtext DEFAULT NULL,
        `code_patterns` longtext DEFAULT NULL,
        `excluded_code_patterns` longtext DEFAULT NULL,
        -- Cart-level conditions --------------------------------------------------
        `min_subtotal` decimal(10,2) NOT NULL DEFAULT '0.00',
        `min_subtotal_scope` varchar(16) NOT NULL DEFAULT 'targeted',
        `min_quantity` int unsigned NOT NULL DEFAULT 0,
        `min_quantity_scope` varchar(16) NOT NULL DEFAULT 'targeted',
        -- Application scope (what gets the offer applied) -----------------------
        `application_scope` varchar(16) NOT NULL DEFAULT 'targeted',
        -- Customer eligibility ---------------------------------------------------
        `customer_role_slugs` longtext DEFAULT NULL,
        `customer_segment_ids` longtext DEFAULT NULL,
        -- Type-specific config + display overrides as JSON -----------------------
        `payload` longtext DEFAULT NULL,
        `display` longtext DEFAULT NULL,
        -- Migration tracking (empty for natively-created offers) -----------------
        `legacy_table` varchar(64) NOT NULL DEFAULT '',
        `legacy_id` bigint unsigned NOT NULL DEFAULT 0,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_offers_type_enabled', $table, '(`type`, `enabled`)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_offers_legacy',       $table, '(`legacy_table`, `legacy_id`)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_offers_window',       $table, '(`enabled`, `start_date`, `end_date`)');

    
    
    
    
    aieo_dmm_ensure_column($table, 'code_patterns',          'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'excluded_code_patterns', 'longtext DEFAULT NULL');
}

function aieo_dmm_create_offer_attributions_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_offer_attributions';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `attribution_id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `order_id` bigint unsigned NOT NULL,
        `offer_id` bigint unsigned NOT NULL DEFAULT 0,
        `offer_type` varchar(32) NOT NULL DEFAULT '',
        `offer_label` varchar(191) NOT NULL DEFAULT '',
        `applied_value` decimal(10,2) NOT NULL DEFAULT '0.00',
        `actual_cost` decimal(10,2) DEFAULT NULL,
        `cost_basis` varchar(20) NOT NULL DEFAULT 'unknown_proxy',
        `order_total` decimal(12,2) NOT NULL DEFAULT '0.00',
        `entry_channel` varchar(40) NOT NULL DEFAULT '',
        `user_id` bigint unsigned NOT NULL DEFAULT 0,
        `target_product_id` bigint unsigned NOT NULL DEFAULT 0,
        `target_units` int unsigned NOT NULL DEFAULT 0,
        `target_revenue` decimal(12,2) NOT NULL DEFAULT '0.00',
        `shipping_method` varchar(120) DEFAULT NULL,
        `shipping_method_id` varchar(60) DEFAULT NULL,
        `shipping_zone` varchar(80) DEFAULT NULL,
        `shipping_zone_id` int DEFAULT NULL,
        `fired_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`attribution_id`),
        KEY idx_order (`order_id`),
        KEY idx_offer (`offer_id`, `offer_type`),
        KEY idx_channel_type (`entry_channel`, `offer_type`),
        KEY idx_user_offer (`user_id`, `offer_type`),
        KEY idx_fired_at (`fired_at`),
        KEY idx_pending_cost (`offer_type`, `actual_cost`),
        KEY idx_method_zone (`shipping_method_id`, `shipping_zone_id`, `fired_at`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    aieo_dmm_ensure_column($table, 'shipping_method',    'varchar(120) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'shipping_method_id', 'varchar(60) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'shipping_zone',      'varchar(80) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'shipping_zone_id',   'int DEFAULT NULL');
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'entry_branch_id',    'bigint unsigned DEFAULT NULL');
    aieo_create_index_if_not_exists('idx_entry_branch', $table, '(entry_branch_id)');
    
    
    
    aieo_dmm_ensure_column($table, 'target_units',       'int unsigned NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'target_revenue',     "decimal(12,2) NOT NULL DEFAULT '0.00'");
    
    
    
    
    
    aieo_create_index_if_not_exists('idx_method_zone', $table, '(`shipping_method_id`, `shipping_zone_id`, `fired_at`)');
}

function aieo_dmm_migrate_legacy_to_offers() {
    global $wpdb;
    $unified = $wpdb->prefix . 'aieo_dmm_offers';
    $gifts   = $wpdb->prefix . 'aieo_dmm_brand_gift_offers';
    $discos  = $wpdb->prefix . 'aieo_dmm_brand_discounts';
    if (!aieo_dmm_table_exists($unified)) { return; }

    if (aieo_dmm_table_exists($gifts)) {
        
        $rows = $wpdb->get_results("SELECT * FROM {$gifts}", ARRAY_A);
        if (is_array($rows)) {
            foreach ($rows as $row) {
                aieo_dmm_upsert_offer_from_legacy($row, 'gift', $gifts);
            }
        }
    }

    if (aieo_dmm_table_exists($discos)) {
        
        $rows = $wpdb->get_results("SELECT * FROM {$discos}", ARRAY_A);
        if (is_array($rows)) {
            foreach ($rows as $row) {
                aieo_dmm_upsert_offer_from_legacy($row, 'discount', $discos);
            }
        }
    }
}

function aieo_dmm_upsert_offer_from_legacy(array $row, $offer_type, $legacy_table) {
    global $wpdb;
    $unified  = $wpdb->prefix . 'aieo_dmm_offers';
    $legacy_id = (int) ($row['id'] ?? 0);
    if ($legacy_id <= 0) { return; }

    
    
    
    $legacy_short = preg_replace('/^' . preg_quote($wpdb->prefix, '/') . '/', '', $legacy_table);

    
    
    
    
    
    $default_min_scope = ($offer_type === 'gift') ? 'targeted' : 'cart_total';

    
    $data = array(
        'type'                => $offer_type,
        'enabled'             => (int) ($row['enabled'] ?? 1) ? 1 : 0,
        'label'               => (string) ($row['label'] ?? ''),
        'priority'            => 100,
        'start_date'          => !empty($row['date_from']) ? $row['date_from'] : null,
        'end_date'            => !empty($row['date_to'])   ? $row['date_to']   : null,
        'rule_operator'       => (isset($row['rule_operator']) && strtoupper((string) $row['rule_operator']) === 'OR') ? 'OR' : 'AND',
        'brand_term_ids'      => isset($row['brand_term_ids'])    ? (string) $row['brand_term_ids']    : '[]',
        'category_term_ids'   => isset($row['category_term_ids']) ? (string) $row['category_term_ids'] : '[]',
        'need_term_ids'       => isset($row['need_term_ids'])     ? (string) $row['need_term_ids']     : '[]',
        'product_ids'         => isset($row['product_ids'])       ? (string) $row['product_ids']       : '[]',
        'excluded_brand_ids'    => isset($row['excluded_brand_ids'])    ? (string) $row['excluded_brand_ids']    : '[]',
        'excluded_category_ids' => isset($row['excluded_category_ids']) ? (string) $row['excluded_category_ids'] : '[]',
        'excluded_need_ids'     => isset($row['excluded_need_ids'])     ? (string) $row['excluded_need_ids']     : '[]',
        'excluded_product_ids'  => isset($row['excluded_product_ids'])  ? (string) $row['excluded_product_ids']  : '[]',
        'code_patterns'         => isset($row['code_patterns'])          ? (string) $row['code_patterns']          : '[]',
        'excluded_code_patterns' => isset($row['excluded_code_patterns']) ? (string) $row['excluded_code_patterns'] : '[]',
        'min_subtotal'        => (float) ($row['min_subtotal'] ?? 0),
        'min_subtotal_scope'  => isset($row['min_subtotal_scope'])
            ? (string) $row['min_subtotal_scope']
            : $default_min_scope,
        'min_quantity'        => 0,
        'min_quantity_scope'  => 'targeted',
        'application_scope'   => isset($row['application_scope'])
            ? (string) $row['application_scope']
            : 'targeted',
        'customer_role_slugs' => '[]',
        'customer_segment_ids'=> '[]',
        'legacy_table'        => $legacy_short,
        'legacy_id'           => $legacy_id,
    );

    
    if ($offer_type === 'gift') {
        $data['payload'] = wp_json_encode(array(
            'gift_product_ids'   => isset($row['gift_product_ids']) ? json_decode((string) $row['gift_product_ids'], true) : array(),
            'presentation_mode'  => isset($row['presentation_mode']) ? (string) $row['presentation_mode'] : 'auto',
            'show_when_pending'  => (int) ($row['show_when_pending'] ?? 1) ? 1 : 0,
            'gift_kind'          => isset($row['gift_kind']) ? (string) $row['gift_kind'] : 'product',
        ));
        $data['display'] = wp_json_encode(array(
            'before_text'        => (string) ($row['before_text']  ?? ''),
            'after_text'         => (string) ($row['after_text']   ?? ''),
            'before_image_id'    => (int)    ($row['before_image_id'] ?? 0),
            'after_image_id'     => (int)    ($row['after_image_id']  ?? 0),
            'bar_bg_before'      => (string) ($row['bar_bg_before'] ?? ''),
            'bar_bg_after'       => (string) ($row['bar_bg_after']  ?? ''),
            'bar_fg_before'      => (string) ($row['bar_fg_before'] ?? ''),
            'bar_fg_after'       => (string) ($row['bar_fg_after']  ?? ''),
            'gift_item_bg'       => (string) ($row['gift_item_bg']  ?? ''),
            'show_banner'        => (int) ($row['show_banner']  ?? 1) ? 1 : 0,
            'show_rewards'       => (int) ($row['show_rewards'] ?? 1) ? 1 : 0,
            'headline_pending'   => (string) ($row['headline_pending']   ?? ''),
            'headline_qualified' => (string) ($row['headline_qualified'] ?? ''),
            'pill_locked'        => (string) ($row['pill_locked']    ?? ''),
            'pill_available'     => (string) ($row['pill_available'] ?? ''),
            'pill_claimed'       => (string) ($row['pill_claimed']   ?? ''),
        ));
    } else {
        
        
        if (!empty($row['brand_term_id']) && (string) $data['brand_term_ids'] === '[]') {
            $data['brand_term_ids'] = wp_json_encode(array((int) $row['brand_term_id']));
        }
        $data['payload'] = wp_json_encode(array(
            'discount_type'  => 'percent',
            'discount_value' => (float) ($row['percent'] ?? 0),
        ));
        $data['display'] = wp_json_encode(array(
            'notes' => (string) ($row['notes'] ?? ''),
        ));
    }

    
    
    $existing = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT id FROM {$unified} WHERE legacy_table = %s AND legacy_id = %d LIMIT 1",
        $legacy_short, $legacy_id
    ));
    if ($existing > 0) {
        $wpdb->update($unified, $data, array('id' => $existing));
    } else {
        $wpdb->insert($unified, $data);
    }
}

function aieo_dmm_create_loyalty_classes_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_loyalty_classes';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `slug` varchar(64) NOT NULL,
        `label` varchar(191) NOT NULL,
        `extra_percent` decimal(5,2) NOT NULL DEFAULT '0.00',
        `points_per_currency` decimal(8,4) NOT NULL DEFAULT '0.0000',
        `enabled` tinyint(1) NOT NULL DEFAULT 1,
        `description` text DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `date_from` date DEFAULT NULL,
        `date_to` date DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_lc_slug` (`slug`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_lc_enabled', $table, '(enabled)');

    
    aieo_dmm_ensure_column($table, 'points_per_currency', "decimal(8,4) NOT NULL DEFAULT '0.0000'");
    
    aieo_dmm_ensure_loyalty_window_columns();

    
    
    $ledger = $wpdb->prefix . 'aieo_dmm_loyalty_points';
    $sql2 = "CREATE TABLE IF NOT EXISTS {$ledger} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `user_id` bigint unsigned NOT NULL,
        `delta` decimal(12,2) NOT NULL,
        `balance_after` decimal(14,2) NOT NULL DEFAULT '0.00',
        `source` varchar(32) NOT NULL,
        `source_ref` varchar(64) DEFAULT NULL,
        `class_id` bigint unsigned DEFAULT NULL,
        `notes` varchar(255) DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) {$charset_collate};";
    $wpdb->query($sql2);
    aieo_create_index_if_not_exists('aieo_dmm_idx_lp_user',    $ledger, '(user_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_lp_source',  $ledger, '(source, source_ref)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_lp_created', $ledger, '(created_at)');
}

function aieo_dmm_ensure_loyalty_window_columns() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_loyalty_classes';
    aieo_dmm_ensure_column($table, 'date_from', 'date DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'date_to', 'date DEFAULT NULL');

    $found = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME IN ('date_from', 'date_to')",
        $table
    ));
    if (2 !== $found) {
        return false;
    }
    if (class_exists('AIEO_DMM_Loyalty_Window')) {
        AIEO_DMM_Loyalty_Window::mark_ready();
    } else {
        update_option('aieo_dmm_loyalty_window_ready', '1', true);
    }
    return true;
}

function aieo_dmm_create_loyalty_assignments_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_loyalty_assignments';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `user_id` bigint unsigned NOT NULL,
        `class_id` bigint unsigned NOT NULL,
        `assigned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`user_id`, `class_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_la_class', $table, '(class_id)');
}

function aieo_dmm_create_no_free_shipping_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_no_free_shipping';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `product_id` bigint unsigned NOT NULL,
        `sku` varchar(100) DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`product_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_nfs_sku', $table, '(sku)');
}

function aieo_dmm_create_branches_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_branches';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `slug` varchar(64) NOT NULL,
        `label` varchar(191) NOT NULL,
        `stock_table` varchar(128) NOT NULL,
        `match_col` varchar(64) NOT NULL DEFAULT 'gtin',
        `enabled` tinyint(1) NOT NULL DEFAULT 1,
        `sort_order` int NOT NULL DEFAULT 0,
        `erp_id` varchar(64) DEFAULT NULL,
        `match_patterns` varchar(255) DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_br_slug` (`slug`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_br_enabled', $table, '(enabled)');

    
    
    
    
    
    aieo_dmm_ensure_column($table, 'erp_id', 'varchar(64) DEFAULT NULL');
    aieo_create_index_if_not_exists('aieo_dmm_idx_br_erp', $table, '(erp_id)');

    
    
    
    aieo_dmm_ensure_column($table, 'match_patterns', 'varchar(255) DEFAULT NULL');

    
    
    
    
    $has_soft1 = $wpdb->get_var($wpdb->prepare(
        'SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s',
        $table, 'soft1_id'
    ));
    if ((int) $has_soft1 > 0) {
        $wpdb->query("UPDATE {$table} SET erp_id = CAST(soft1_id AS CHAR) WHERE erp_id IS NULL AND soft1_id IS NOT NULL");
        $wpdb->query("ALTER TABLE {$table} DROP COLUMN soft1_id");
    }

    
    
    
    aieo_dmm_ensure_column($table, 'scanner_page_id', 'bigint unsigned DEFAULT NULL');

    
    
    
    
    $mt = $wpdb->prefix . 'aieo_dmm_branch_matchers';
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$mt} (
        `branch_id` bigint unsigned NOT NULL,
        `pattern` varchar(191) NOT NULL,
        PRIMARY KEY (`branch_id`, `pattern`)
    ) {$charset_collate};");
}

function aieo_dmm_create_branch_scan_log_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_branch_scan_log';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `barcode` varchar(100) NOT NULL,
        `status` varchar(32) NOT NULL DEFAULT 'missing',
        `branch_id` bigint unsigned DEFAULT NULL,
        `user_id` bigint unsigned DEFAULT NULL,
        `user_login` varchar(60) DEFAULT NULL,
        `image_url` text DEFAULT NULL,
        `image_path` text DEFAULT NULL,
        `user_agent` varchar(255) DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_barcode', $table, '(barcode)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_status', $table, '(status)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_branch', $table, '(branch_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_created', $table, '(created_at)');
}

function aieo_dmm_create_product_needs_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_product_needs';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `product_id` bigint unsigned NOT NULL,
        `selected_needs` text DEFAULT NULL,
        `need_order` text DEFAULT NULL,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`product_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_create_discount_schedules_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_discount_schedules';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `product_id` bigint unsigned NOT NULL,
        `sku` varchar(100) DEFAULT NULL,
        `percent` decimal(5,2) NOT NULL DEFAULT '0.00',
        `date_from` date DEFAULT NULL,
        `date_to` date DEFAULT NULL,
        `active` tinyint(1) NOT NULL DEFAULT 1,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_ds_product', $table, '(product_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_ds_active', $table, '(active)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_ds_window', $table, '(date_from, date_to)');
}

function aieo_dmm_create_export_jobs_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_export_jobs';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `job_id` varchar(64) NOT NULL,
        `type` varchar(64) NOT NULL,
        `status` varchar(20) NOT NULL DEFAULT 'pending',
        `total` bigint unsigned NOT NULL DEFAULT 0,
        `processed` bigint unsigned NOT NULL DEFAULT 0,
        `last_id` bigint unsigned NOT NULL DEFAULT 0,
        `filename` varchar(255) DEFAULT NULL,
        `params` longtext DEFAULT NULL,
        `log` longtext DEFAULT NULL,
        `user_id` bigint unsigned DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`job_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_ej_type', $table, '(type)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_ej_status', $table, '(status)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_ej_user', $table, '(user_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_ej_created', $table, '(created_at)');
}

function aieo_dmm_create_feed_configs_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_feed_configs';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `slug` varchar(64) NOT NULL,
        `label` varchar(191) NOT NULL,
        `format` varchar(16) NOT NULL DEFAULT 'xml',
        `template` varchar(64) DEFAULT NULL,
        `file_path` varchar(255) DEFAULT NULL,
        `include_ids` longtext DEFAULT NULL,
        `include_brand_ids` longtext DEFAULT NULL,
        `exclude_ids` longtext DEFAULT NULL,
        `exclude_brand_ids` longtext DEFAULT NULL,
        `field_map` longtext DEFAULT NULL,
        `schedule` varchar(32) NOT NULL DEFAULT 'manual',
        `cache_mode` varchar(32) NOT NULL DEFAULT 'version_query',
        `last_run_at` datetime DEFAULT NULL,
        `last_run_status` varchar(32) DEFAULT NULL,
        `last_run_count` bigint unsigned DEFAULT NULL,
        `last_run_etag` varchar(64) DEFAULT NULL,
        `enabled` tinyint(1) NOT NULL DEFAULT 1,
        `exclude_auto_restocked` tinyint(1) NOT NULL DEFAULT 0,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_fc_slug` (`slug`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_fc_enabled', $table, '(enabled)');

    
    aieo_dmm_ensure_column($table, 'cache_mode', "varchar(32) NOT NULL DEFAULT 'version_query'");
    aieo_dmm_ensure_column($table, 'last_run_etag', 'varchar(64) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'include_category_ids', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'exclude_category_ids', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'exclude_auto_restocked', 'tinyint(1) NOT NULL DEFAULT 0');
}

function aieo_dmm_create_feed_terms_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_feed_terms';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `feed_id` bigint unsigned NOT NULL,
        `supplier_term_id` bigint unsigned NOT NULL DEFAULT 0,
        `kind` varchar(12) NOT NULL,
        `value` varchar(191) NOT NULL,
        `path` varchar(255) NOT NULL DEFAULT '',
        `parent_value` varchar(191) NOT NULL DEFAULT '',
        `product_count` int unsigned NOT NULL DEFAULT 0,
        `last_version` int unsigned NOT NULL DEFAULT 0,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_ft` (`feed_id`, `kind`, `value`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_ft_supplier', $table, '(supplier_term_id, kind)');
}

function aieo_dmm_create_feed_cat_map_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_feed_cat_map';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `owner_scope` varchar(8) NOT NULL DEFAULT 'feed',
        `owner_key` bigint unsigned NOT NULL,
        `feed_id` bigint unsigned NOT NULL DEFAULT 0,
        `supplier_term_id` bigint unsigned NOT NULL DEFAULT 0,
        `vendor_value` varchar(191) NOT NULL,
        `vendor_path` varchar(255) NOT NULL,
        `path_hash` char(32) NOT NULL,
        `term_id` bigint unsigned NOT NULL DEFAULT 0,
        `score` decimal(4,3) NOT NULL DEFAULT 0.000,
        `status` varchar(12) NOT NULL DEFAULT 'suggested',
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_fcm` (`owner_scope`, `owner_key`, `path_hash`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_fcm_status', $table, '(status)');
}

function aieo_feed_migrate_path_hashes() {
    global $wpdb;

    if (!class_exists('AIEO_Feed_Mapping')) { return; }
    $target = (int) AIEO_Feed_Mapping::PATH_KEY_VERSION;
    if ((int) get_option('aieo_feed_path_key_version', 1) >= $target) { return; }

    $key = function ($path) {
        return md5(AIEO_Feed_Mapping::category_path_key(explode(' > ', (string) $path)));
    };

    
    $t = $wpdb->prefix . 'aieo_dmm_feed_cat_map';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) {
        
        $rank = array('manual' => 5, 'confirmed' => 4, 'learned' => 3, 'auto' => 2, 'suggested' => 1, 'rejected' => 0);
        $seen = array();
        foreach ((array) $wpdb->get_results("SELECT id, owner_scope, owner_key, vendor_path, status, score FROM `$t`") as $r) {
            $h    = $key($r->vendor_path);
            $slot = $r->owner_scope . '|' . $r->owner_key . '|' . $h;
            $mine = array((int) ($rank[$r->status] ?? 0), (float) $r->score);
            if (isset($seen[$slot])) {
                $keep = $seen[$slot];
                if ($mine > $keep['w']) {
                    $wpdb->delete($t, array('id' => (int) $keep['id']), array('%d'));
                    $seen[$slot] = array('id' => (int) $r->id, 'w' => $mine);
                    $wpdb->update($t, array('path_hash' => $h), array('id' => (int) $r->id), array('%s'), array('%d'));
                } else {
                    $wpdb->delete($t, array('id' => (int) $r->id), array('%d'));
                }
                continue;
            }
            $seen[$slot] = array('id' => (int) $r->id, 'w' => $mine);
            $wpdb->update($t, array('path_hash' => $h), array('id' => (int) $r->id), array('%s'), array('%d'));
        }
    }

    
    $t = $wpdb->prefix . 'aieo_dmm_cat_match_register';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) {
        $seen = array();
        foreach ((array) $wpdb->get_results("SELECT id, vendor_path, vendor_value, hits FROM `$t`") as $r) {
            $h = $key($r->vendor_path);
            if (isset($seen[$h])) {
                
                $keep = $seen[$h];
                if ((int) $r->hits > (int) $keep['hits']) {
                    $wpdb->delete($t, array('id' => (int) $keep['id']), array('%d'));
                    $seen[$h] = array('id' => (int) $r->id, 'hits' => (int) $r->hits + (int) $keep['hits']);
                    $wpdb->update($t, array(
                        'path_hash' => $h,
                        'leaf_norm' => AIEO_Feed_Mapping::category_path_key(array((string) $r->vendor_value)),
                        'hits'      => (int) $r->hits + (int) $keep['hits'],
                    ), array('id' => (int) $r->id), array('%s', '%s', '%d'), array('%d'));
                } else {
                    $wpdb->update($t, array('hits' => (int) $keep['hits'] + (int) $r->hits),
                        array('id' => (int) $keep['id']), array('%d'), array('%d'));
                    $seen[$h]['hits'] += (int) $r->hits;
                    $wpdb->delete($t, array('id' => (int) $r->id), array('%d'));
                }
                continue;
            }
            $seen[$h] = array('id' => (int) $r->id, 'hits' => (int) $r->hits);
            $wpdb->update($t, array(
                'path_hash' => $h,
                'leaf_norm' => AIEO_Feed_Mapping::category_path_key(array((string) $r->vendor_value)),
            ), array('id' => (int) $r->id), array('%s', '%s'), array('%d'));
        }
    }

    
    $t = $wpdb->prefix . 'aieo_dmm_stock_word_register';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) {
        $seen = array();
        foreach ((array) $wpdb->get_results("SELECT id, word FROM `$t`") as $r) {
            $n = AIEO_Feed_Mapping::category_path_key(array((string) $r->word));
            if ($n === '' || isset($seen[$n])) {
                if (isset($seen[$n])) { $wpdb->delete($t, array('id' => (int) $r->id), array('%d')); }
                continue;
            }
            $seen[$n] = true;
            $wpdb->update($t, array('word_norm' => $n), array('id' => (int) $r->id), array('%s'), array('%d'));
        }
    }

    
    
    $t = $wpdb->prefix . 'aieo_dmm_feed_row_terms';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) {
        $wpdb->query("TRUNCATE TABLE `$t`");
        $rows = $wpdb->prefix . 'aieo_dmm_feed_rows';
        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $rows)) === $rows) {
            $wpdb->query("TRUNCATE TABLE `$rows`");
        }
    }

    update_option('aieo_feed_path_key_version', $target, false);
}

function aieo_dmm_create_feed_rows_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_feed_rows';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `feed_id` bigint unsigned NOT NULL,
        `row_no` int unsigned NOT NULL,
        `feed_version` int unsigned NOT NULL DEFAULT 0,
        `sku` varchar(100) NOT NULL DEFAULT '',
        `row_hash` char(32) NOT NULL DEFAULT '',
        `price_hash` char(32) NOT NULL DEFAULT '',
        `image_hash` char(32) NOT NULL DEFAULT '',
        `row_group_hash` char(32) NOT NULL DEFAULT '',
        `price_group_hash` char(32) NOT NULL DEFAULT '',
        `title` varchar(255) NOT NULL DEFAULT '',
        `regular_price` decimal(12,4) DEFAULT NULL,
        `sale_price` decimal(12,4) DEFAULT NULL,
        `cost_price` decimal(12,4) DEFAULT NULL,
        `stock_qty` int DEFAULT NULL,
        `stock_status` varchar(20) NOT NULL DEFAULT '',
        `barcode` varchar(100) NOT NULL DEFAULT '',
        `brand` varchar(191) NOT NULL DEFAULT '',
        `category_path` varchar(255) NOT NULL DEFAULT '',
        `group_key` varchar(191) NOT NULL DEFAULT '',
        `image_count` smallint unsigned NOT NULL DEFAULT 0,
        `payload` mediumtext,
        `landed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_fr` (`feed_id`, `row_no`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_fr_sku',   $table, '(feed_id, sku)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_fr_price', $table, '(feed_id, price_hash)');

    
    foreach (array('row_group_hash', 'price_group_hash') as $col) {
        $has = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM `{$table}` LIKE %s", $col));
        if (!$has) {
            $wpdb->query("ALTER TABLE `{$table}` ADD COLUMN `{$col}` char(32) NOT NULL DEFAULT ''");
        }
    }
    aieo_create_index_if_not_exists('aieo_dmm_idx_fr_group', $table, '(feed_id, group_key)');
}

function aieo_dmm_create_feed_image_queue_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_feed_image_queue';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `product_id` bigint unsigned NOT NULL,
        `position` smallint unsigned NOT NULL DEFAULT 0,
        `url` varchar(500) NOT NULL,
        `url_hash` char(32) NOT NULL,
        `status` varchar(10) NOT NULL DEFAULT 'pending',
        `attempts` tinyint unsigned NOT NULL DEFAULT 0,
        `last_error` varchar(191) NOT NULL DEFAULT '',
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `done_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_fiq` (`product_id`, `url_hash`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    aieo_create_index_if_not_exists('aieo_dmm_idx_fiq_status', $table, '(status, id)');
}

function aieo_dmm_create_feed_run_log_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_feed_run_log';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `feed_id` bigint unsigned NOT NULL,
        `started_at` datetime NOT NULL,
        `finished_at` datetime DEFAULT NULL,
        `ok` tinyint(1) NOT NULL DEFAULT 0,
        `complete` tinyint(1) NOT NULL DEFAULT 0,
        `processed` int unsigned NOT NULL DEFAULT 0,
        `created` int unsigned NOT NULL DEFAULT 0,
        `updated` int unsigned NOT NULL DEFAULT 0,
        `skipped` int unsigned NOT NULL DEFAULT 0,
        `filtered_out` int unsigned NOT NULL DEFAULT 0,
        `errors_count` int unsigned NOT NULL DEFAULT 0,
        `message` varchar(255) NOT NULL DEFAULT '',
        `details_json` longtext NULL,
        PRIMARY KEY (`id`),
        KEY `aieo_dmm_idx_frl_feed` (`feed_id`, `started_at`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_create_feed_row_terms_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_feed_row_terms';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `feed_id` bigint unsigned NOT NULL,
        `row_id` bigint unsigned NOT NULL,
        `kind` varchar(12) NOT NULL,
        `value_key` varchar(191) COLLATE utf8mb4_bin NOT NULL,
        PRIMARY KEY (`row_id`, `kind`, `value_key`),
        KEY `aieo_dmm_idx_frt_lookup` (`feed_id`, `kind`, `value_key`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    $col = $wpdb->get_row("SHOW FULL COLUMNS FROM `{$table}` LIKE 'value_key'");
    if ($col && isset($col->Collation) && 'utf8mb4_bin' !== $col->Collation) {
        $wpdb->query("ALTER TABLE `{$table}` MODIFY `value_key` varchar(191) COLLATE utf8mb4_bin NOT NULL");
    }
}

function aieo_dmm_create_cat_match_register_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_cat_match_register';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `path_hash` char(32) NOT NULL,
        `vendor_value` varchar(191) NOT NULL,
        `vendor_path` varchar(255) NOT NULL,
        `leaf_norm` varchar(191) NOT NULL,
        `term_id` bigint unsigned NOT NULL,
        `term_label` varchar(191) NOT NULL DEFAULT '',
        `term_path` varchar(255) NOT NULL DEFAULT '',
        `source` varchar(12) NOT NULL DEFAULT 'confirmed',
        `hits` int unsigned NOT NULL DEFAULT 1,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_cmr_path` (`path_hash`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_cmr_leaf', $table, '(leaf_norm)');
}

function aieo_dmm_create_stock_word_register_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_stock_word_register';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `word_norm` varchar(191) NOT NULL,
        `word` varchar(191) NOT NULL,
        `stock_status` varchar(16) NOT NULL DEFAULT '',
        `stock_qty` int DEFAULT NULL,
        `discontinued` tinyint(1) NOT NULL DEFAULT 0,
        `source` varchar(12) NOT NULL DEFAULT 'rule',
        `hits` int unsigned NOT NULL DEFAULT 1,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_swr_word` (`word_norm`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_ensure_column($table, $column, $definition) {
    global $wpdb;

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

    $found = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
        $table,
        $column
    ));
    if ((int) $found === 0) {
        
        $wpdb->query("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
    }

    $wpdb->suppress_errors($prev_suppress);
}

function aieo_dmm_ensure_index($table, $index, $columns) {
    global $wpdb;

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

    $found = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s",
        $table,
        $index
    ));
    if ((int) $found === 0) {
        
        $wpdb->query("ALTER TABLE `{$table}` ADD KEY `{$index}` {$columns}");
    }

    $wpdb->suppress_errors($prev_suppress);
}

function aieo_dmm_create_product_fp_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_product_fp';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `product_id` bigint unsigned NOT NULL,
        `parent_id` bigint unsigned NOT NULL DEFAULT 0,
        `base_regular` decimal(10,2) DEFAULT NULL,
        `base_sale` decimal(10,2) DEFAULT NULL,
        `brand_term_ids` longtext DEFAULT NULL,
        `cat_term_ids` longtext DEFAULT NULL,
        `effective_brand_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
        `effective_cat_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
        `effective_combined_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
        `active_schedule_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
        `nfs_excluded` tinyint(1) NOT NULL DEFAULT 0,
        `eligible_gift_offer_ids` longtext DEFAULT NULL,
        `fp_version_key` varchar(48) NOT NULL DEFAULT '',
        `computed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`product_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_parent',      $table, '(parent_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_nfs',         $table, '(nfs_excluded)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_version',     $table, '(fp_version_key)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_computed',    $table, '(computed_at)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_combined',    $table, '(effective_combined_pct)');
}

function aieo_dmm_create_user_fp_table() {
    global $wpdb;

    $table = $wpdb->prefix . 'aieo_dmm_user_fp';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `user_id` bigint unsigned NOT NULL,
        `role_hash` varchar(40) NOT NULL DEFAULT '',
        `enabled_role_column` varchar(64) NOT NULL DEFAULT '',
        `loyalty_class_ids` longtext DEFAULT NULL,
        `loyalty_extra_pct_sum` decimal(5,2) NOT NULL DEFAULT '0.00',
        `personal_extra_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
        `brand_overrides` longtext DEFAULT NULL,
        `sequence_pct` decimal(5,2) NOT NULL DEFAULT '0.00',
        `sequence_expires_at` datetime DEFAULT NULL,
        `fp_version_key` varchar(48) NOT NULL DEFAULT '',
        `computed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`user_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_ufp_role_hash',  $table, '(role_hash)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_ufp_version',    $table, '(fp_version_key)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_ufp_seq_expiry', $table, '(sequence_expires_at)');
}

function aieo_dmm_migrate_legacy_data() {
    global $wpdb;

    $new_role_prices    = $wpdb->prefix . 'aieo_dmm_role_prices';
    $new_nfs            = $wpdb->prefix . 'aieo_dmm_no_free_shipping';
    $new_product_needs  = $wpdb->prefix . 'aieo_dmm_product_needs';
    $new_gift_offers    = $wpdb->prefix . 'aieo_dmm_brand_gift_offers';

    $legacy_role_prices   = $wpdb->prefix . 'om_role_based_pricing';
    $legacy_nfs           = $wpdb->prefix . 'om_no_free_shipping';
    $legacy_product_needs = $wpdb->prefix . 'pods_product_need';

    
    
    if (aieo_dmm_table_exists($legacy_role_prices) && aieo_dmm_table_is_empty($new_role_prices)) {
        $wpdb->query("
            INSERT IGNORE INTO {$new_role_prices} (product_id, parent_id, product_type, sku, created_at, updated_at)
            SELECT product_id, parent_id, product_type, sku, created_at, updated_at
            FROM {$legacy_role_prices}
        ");
    }

    
    
    
    
    if (aieo_dmm_table_exists($legacy_nfs) && aieo_dmm_table_is_empty($new_nfs)) {
        $legacy_has_sku = (bool) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'sku'",
            $legacy_nfs
        ));
        if ($legacy_has_sku) {
            $sku_expr = "COALESCE(sku, (SELECT meta_value FROM {$wpdb->postmeta} pm
                                          WHERE pm.post_id = {$legacy_nfs}.product_id
                                          AND pm.meta_key = '_sku' LIMIT 1))";
        } else {
            $sku_expr = "(SELECT meta_value FROM {$wpdb->postmeta} pm
                          WHERE pm.post_id = {$legacy_nfs}.product_id
                          AND pm.meta_key = '_sku' LIMIT 1)";
        }
        $wpdb->query("
            INSERT IGNORE INTO {$new_nfs} (product_id, sku, created_at)
            SELECT product_id,
                   {$sku_expr} AS sku,
                   created_at
            FROM {$legacy_nfs}
        ");
    }

    
    if (aieo_dmm_table_exists($legacy_product_needs) && aieo_dmm_table_is_empty($new_product_needs)) {
        
        $wpdb->query("
            INSERT IGNORE INTO {$new_product_needs} (product_id, selected_needs, updated_at)
            SELECT product_id, selected_needs, UTC_TIMESTAMP()
            FROM {$legacy_product_needs}
            WHERE product_id IS NOT NULL
        ");
    }

    
    if (aieo_dmm_table_is_empty($new_gift_offers)) {
        $legacy_offers = get_option('om_brand_gift_offers', array());
        if (is_array($legacy_offers) && !empty($legacy_offers)) {
            foreach ($legacy_offers as $offer) {
                if (!is_array($offer)) {
                    continue;
                }
                $brand_ids = array();
                if (!empty($offer['brand_ids']) && is_array($offer['brand_ids'])) {
                    $brand_ids = array_map('absint', $offer['brand_ids']);
                }
                $gift_ids = array();
                if (!empty($offer['gift_product_ids']) && is_array($offer['gift_product_ids'])) {
                    $gift_ids = array_map('absint', $offer['gift_product_ids']);
                } elseif (!empty($offer['gift_product_id'])) {
                    $gift_ids = array(absint($offer['gift_product_id']));
                }

                $wpdb->insert(
                    $new_gift_offers,
                    array(
                        'enabled'          => (isset($offer['enabled']) && $offer['enabled'] === 'yes') ? 1 : 0,
                        'label'            => isset($offer['label']) ? (string) $offer['label'] : null,
                        'brand_term_ids'   => wp_json_encode($brand_ids),
                        'min_subtotal'     => isset($offer['min_subtotal']) ? (float) $offer['min_subtotal'] : 0.0,
                        'date_from'        => !empty($offer['date_from']) ? (string) $offer['date_from'] : null,
                        'date_to'          => !empty($offer['date_to']) ? (string) $offer['date_to'] : null,
                        'gift_product_ids' => wp_json_encode($gift_ids),
                        'before_text'      => isset($offer['before_threshold_text']) ? (string) $offer['before_threshold_text'] : null,
                        'after_text'       => isset($offer['after_threshold_text']) ? (string) $offer['after_threshold_text'] : null,
                        'before_image_id'  => isset($offer['before_threshold_image_id']) ? absint($offer['before_threshold_image_id']) : null,
                        'after_image_id'   => isset($offer['after_threshold_image_id']) ? absint($offer['after_threshold_image_id']) : null,
                    ),
                    array('%d', '%s', '%s', '%f', '%s', '%s', '%s', '%s', '%s', '%d', '%d')
                );
            }
        }
    }

    
    
    aieo_dmm_seed_legacy_branches();

    
    
    
    aieo_dmm_migrate_swatches_option();
}

function aieo_dmm_migrate_swatches_option() {
    if (get_option('aieo_dmm_swatches_settings', null) !== null) {
        return;
    }
    $legacy = get_option('om_product_swatches_settings', null);
    if (is_array($legacy)) {
        update_option('aieo_dmm_swatches_settings', $legacy, false);
        update_option('aieo_dmm_swatches_settings_hash', md5((string) wp_json_encode($legacy)), false);
    }
}

function aieo_dmm_seed_legacy_branches() {
    global $wpdb;

    $branches = $wpdb->prefix . 'aieo_dmm_branches';
    if (!aieo_dmm_table_is_empty($branches)) {
        return;
    }

    $legacy = array(
        'keratsini'  => 'store_board_keratsini',
        'piraeus'    => 'store_board_piraeus',
        'korydallos' => 'store_board_korydallos',
        'kallithea'  => 'store_board_kallithea',
    );

    $order = 0;
    foreach ($legacy as $slug => $suffix) {
        $candidate = $wpdb->prefix . $suffix;
        if (!aieo_dmm_table_exists($candidate)) {
            continue;
        }
        $wpdb->insert(
            $branches,
            array(
                'slug'        => $slug,
                'label'       => ucfirst($slug),
                'stock_table' => $candidate,
                'match_col'   => 'gtin',
                'enabled'     => 1,
                'sort_order'  => $order++,
            ),
            array('%s', '%s', '%s', '%s', '%d', '%d')
        );
    }

    
    
    
    if (function_exists('aieo_dmm_backfill_legacy_branch_matchers')) {
        aieo_dmm_backfill_legacy_branch_matchers();
    }
}

if (!function_exists('aieo_dmm_table_exists')) {

function aieo_dmm_table_exists($table_name) {
    global $wpdb;

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    static $known = null;
    $version = defined('AIEO_VERSION') ? AIEO_VERSION : '0';

    if (null === $known) {
        $stored = get_option('aieo_known_tables');
        $known  = (is_array($stored) && isset($stored['v'], $stored['t'])
                   && $stored['v'] === $version && is_array($stored['t']))
                ? $stored['t']
                : array();
    }

    $key = (string) $table_name;
    if (isset($known[$key])) { return true; }

    $found = $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = %s",
        $table_name
    ));

    $exists = ((int) $found) > 0;
    if ($exists) {
        $known[$key] = true;
        update_option('aieo_known_tables', array('v' => $version, 't' => $known), true);
    }

    return $exists;
}

function aieo_dmm_table_is_empty($table_name) {
    global $wpdb;

    if (!aieo_dmm_table_exists($table_name)) {
        return true;
    }

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

function aieo_dmm_sanitize_role_column($role) {
    $clean = strtolower(preg_replace('/[^a-z0-9_]/', '_', $role));
    if ($clean === '') {
        return 'role_r';
    }
    if (preg_match('/^[0-9]/', $clean)) {
        $clean = 'r_' . $clean;
    }
    return 'role_' . $clean;
}

} 

function aieo_dmm_sync_role_columns(array $enabled_roles) {
    global $wpdb;

    if (empty($enabled_roles)) {
        return;
    }

    $targets = array(
        $wpdb->prefix . 'aieo_dmm_role_prices',
        $wpdb->prefix . 'om_role_based_pricing',
    );

    foreach ($targets as $table) {
        if (!aieo_dmm_table_exists($table)) {
            continue;
        }

        $existing_cols = $wpdb->get_col("SHOW COLUMNS FROM {$table}");
        $existing_map  = array_flip((array) $existing_cols);

        foreach ($enabled_roles as $role) {
            $col = aieo_dmm_sanitize_role_column((string) $role);
            if (isset($existing_map[$col])) {
                continue;
            }
            
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN `{$col}` DECIMAL(10,2) DEFAULT NULL");
        }
    }
}

function aieo_dmm_create_complementary_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_complementary';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `enabled` tinyint(1) NOT NULL DEFAULT 1,
        `label` varchar(191) DEFAULT NULL,
        `source_product_id` bigint unsigned NOT NULL,
        `product_ids` longtext DEFAULT NULL,
        `offer_title` varchar(255) DEFAULT NULL,
        `button_text` varchar(100) DEFAULT NULL,
        `placement` varchar(64) NOT NULL DEFAULT 'before_add_to_cart',
        `checkboxes_state` varchar(16) NOT NULL DEFAULT 'unchecked',
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `idx_source_product` (`source_product_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_compl_enabled', $table, '(enabled)');
}

function aieo_dmm_create_swatches_cache_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_swatches_cache';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `product_id` bigint unsigned NOT NULL,
        `context` varchar(16) NOT NULL,
        `total_available` int unsigned NOT NULL DEFAULT 0,
        `stored_count` int unsigned NOT NULL DEFAULT 0,
        `variations_json` longtext NOT NULL,
        `variation_ids_json` longtext NOT NULL,
        `variations_map_json` longtext DEFAULT NULL,
        `grid_html` longtext DEFAULT NULL,
        `cache_hash` char(32) NOT NULL DEFAULT '',
        `built_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`product_id`, `context`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_sw_built',  $table, '(built_at)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_sw_hash',   $table, '(cache_hash)');
}

function aieo_dmm_create_color_attributes_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_color_attributes';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `taxonomy` varchar(191) NOT NULL,
        `attribute_id` bigint unsigned NOT NULL DEFAULT 0,
        `attribute_label` varchar(191) NOT NULL DEFAULT '',
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`taxonomy`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_create_category_extras_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_category_extras';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `taxonomy` varchar(64) NOT NULL,
        `term_id` bigint unsigned NOT NULL,
        `headline` text DEFAULT NULL,
        `description` longtext DEFAULT NULL,
        `image_mobile_id` bigint unsigned DEFAULT NULL,
        `image_desktop_id` bigint unsigned DEFAULT NULL,
        `seo_title` varchar(255) DEFAULT NULL,
        `sidebar_text` longtext DEFAULT NULL,
        `footer_text` longtext DEFAULT NULL,
        `columns` tinyint unsigned DEFAULT NULL,
        `disable_nested` tinyint(1) NOT NULL DEFAULT 0,
        `template` varchar(64) NOT NULL DEFAULT 'default',
        `pro_only` tinyint(1) NOT NULL DEFAULT 0,
        `reference_quantity` int unsigned DEFAULT NULL,
        `synonyms` longtext DEFAULT NULL,
        `season_start` date DEFAULT NULL,
        `season_end` date DEFAULT NULL,
        `season_midpoint` date DEFAULT NULL,
        `landing_pages` longtext DEFAULT NULL,
        `related_brand_ids` longtext DEFAULT NULL,
        `related_post_ids` longtext DEFAULT NULL,
        `related_campaign_ids` longtext DEFAULT NULL,
        `google_category_id` int unsigned DEFAULT NULL,
        `google_category_path` varchar(500) DEFAULT NULL,
        `facebook_category_id` int unsigned DEFAULT NULL,
        `facebook_category_path` varchar(500) DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_ce_term` (`taxonomy`, `term_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_create_index_if_not_exists('aieo_dmm_idx_ce_taxonomy', $table, '(taxonomy)');

    
    
    aieo_dmm_ensure_column($table, 'pro_only',             'tinyint(1) NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'reference_quantity',   'int unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'synonyms',             'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'season_start',         'date DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'season_end',           'date DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'season_midpoint',      'date DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'related_brand_ids',    'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'related_post_ids',     'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'related_campaign_ids', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'template',             "varchar(64) NOT NULL DEFAULT 'default'");
    
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'google_category_id',     'int unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'google_category_path',   'varchar(500) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'facebook_category_id',   'int unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'facebook_category_path', 'varchar(500) DEFAULT NULL');
}

function aieo_dmm_create_brand_extras_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_brand_extras';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `term_id` bigint unsigned NOT NULL,
        `taxonomy` varchar(64) NOT NULL DEFAULT 'product_brand',
        `logo_image_id` bigint unsigned DEFAULT NULL,
        `hero_image_id` bigint unsigned DEFAULT NULL,
        `headline` text DEFAULT NULL,
        `history_text` longtext DEFAULT NULL,
        `ambassadors_text` longtext DEFAULT NULL,
        `importer_text` longtext DEFAULT NULL,
        `website_url` varchar(500) DEFAULT NULL,
        `promo` tinyint NOT NULL DEFAULT 0,
        `priority` int NOT NULL DEFAULT 0,
        `bias` decimal(5,4) NOT NULL DEFAULT 0,
        `gross_margin` decimal(5,2) DEFAULT NULL,
        `supplier_term_id` bigint unsigned DEFAULT NULL,
        `footer_text` longtext DEFAULT NULL,
        `gallery_image_ids` longtext DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_be_term` (`taxonomy`, `term_id`),
        KEY `aieo_dmm_idx_be_priority` (`priority`),
        KEY `aieo_dmm_idx_be_supplier` (`supplier_term_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    aieo_dmm_ensure_column($table, 'taxonomy',         "varchar(64) NOT NULL DEFAULT 'product_brand'");
    aieo_dmm_ensure_column($table, 'logo_image_id',    'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'hero_image_id',    'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'headline',         'text DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'history_text',     'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'ambassadors_text', 'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'importer_text',    'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'website_url',      'varchar(500) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'promo',            'tinyint NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'priority',         'int NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'bias',             'decimal(5,4) NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'gross_margin',     'decimal(5,2) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'supplier_term_id', 'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'footer_text',      'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'gallery_image_ids','longtext DEFAULT NULL');
}

function aieo_dmm_migrate_category_extras_to_term_relations() {
    global $wpdb;
    if (!class_exists('AIEO_DMM_Term_Relations')) {
        return 0;
    }
    $src_table = $wpdb->prefix . 'aieo_dmm_category_extras';

    
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $src_table)) !== $src_table) {
        return 0;
    }

    $rows = $wpdb->get_results(
        "SELECT taxonomy, term_id, related_brand_ids, related_post_ids, related_campaign_ids
           FROM {$src_table}
          WHERE (related_brand_ids    IS NOT NULL AND related_brand_ids    NOT IN ('', '[]'))
             OR (related_post_ids     IS NOT NULL AND related_post_ids     NOT IN ('', '[]'))
             OR (related_campaign_ids IS NOT NULL AND related_campaign_ids NOT IN ('', '[]'))",
        ARRAY_A
    );
    if (empty($rows)) { return 0; }

    $processed = 0;
    foreach ($rows as $r) {
        $tax = (string) $r['taxonomy'];
        $tid = (int) $r['term_id'];
        if ($tax === '' || $tid <= 0) { continue; }

        $brand_ids = json_decode((string) $r['related_brand_ids'], true);
        if (is_array($brand_ids) && !empty($brand_ids)) {
            AIEO_DMM_Term_Relations::set_targets(
                $tax, $tid, 'term', 'product_brand',
                array_map('intval', $brand_ids)
            );
        }
        $post_ids = json_decode((string) $r['related_post_ids'], true);
        if (is_array($post_ids) && !empty($post_ids)) {
            AIEO_DMM_Term_Relations::set_targets(
                $tax, $tid, 'post', 'post',
                array_map('intval', $post_ids)
            );
        }
        $campaign_ids = json_decode((string) $r['related_campaign_ids'], true);
        if (is_array($campaign_ids) && !empty($campaign_ids)) {
            AIEO_DMM_Term_Relations::set_targets(
                $tax, $tid, 'post', 'campaign',
                array_map('intval', $campaign_ids)
            );
        }
        $processed++;
    }
    return $processed;
}

function aieo_dmm_migrate_brand_rows_from_category_extras() {
    global $wpdb;
    $src = $wpdb->prefix . 'aieo_dmm_category_extras';
    $dst = $wpdb->prefix . 'aieo_dmm_brand_extras';

    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $src)) !== $src) { return 0; }
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $dst)) !== $dst) { return 0; }

    $rows = $wpdb->get_results(
        "SELECT term_id, headline, footer_text
           FROM {$src}
          WHERE taxonomy = 'product_brand'
            AND ((headline IS NOT NULL AND headline <> '')
              OR (footer_text IS NOT NULL AND footer_text <> ''))",
        ARRAY_A
    );
    if (empty($rows)) { return 0; }

    $migrated = 0;
    foreach ($rows as $r) {
        $term_id  = (int) $r['term_id'];
        $headline = (string) $r['headline'];
        $footer   = (string) $r['footer_text'];
        if ($term_id <= 0) { continue; }

        
        $existing = $wpdb->get_row($wpdb->prepare(
            "SELECT id, headline, footer_text FROM {$dst}
              WHERE taxonomy = 'product_brand' AND term_id = %d LIMIT 1",
            $term_id
        ), ARRAY_A);

        $new_headline = ($existing && (string) $existing['headline'] !== '') ? $existing['headline'] : $headline;
        $new_footer   = ($existing && (string) $existing['footer_text'] !== '') ? $existing['footer_text'] : $footer;

        if ($existing) {
            
            if ($new_headline !== $existing['headline'] || $new_footer !== $existing['footer_text']) {
                $wpdb->update($dst,
                    array('headline' => $new_headline, 'footer_text' => $new_footer),
                    array('id' => (int) $existing['id'])
                );
                $migrated++;
            }
        } else {
            $now = current_time('mysql', true);
            $wpdb->insert($dst, array(
                'term_id'     => $term_id,
                'taxonomy'    => 'product_brand',
                'headline'    => $new_headline,
                'footer_text' => $new_footer,
                'created_at'  => $now,
                'updated_at'  => $now,
            ));
            $migrated++;
        }
    }

    
    if (class_exists('AIEO_DMM_Brand_Extras_Data')) {
        AIEO_DMM_Brand_Extras_Data::flush_request_cache();
    }
    return $migrated;
}

function aieo_dmm_create_need_extras_table() {
    global $wpdb;
    $table           = $wpdb->prefix . 'aieo_dmm_need_extras';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `term_id` bigint unsigned NOT NULL,
        `taxonomy` varchar(64) NOT NULL DEFAULT 'pa_need',
        `icon_image_id` bigint unsigned DEFAULT NULL,
        `hero_image_id` bigint unsigned DEFAULT NULL,
        `headline` text DEFAULT NULL,
        `about_text` longtext DEFAULT NULL,
        `priority` int NOT NULL DEFAULT 0,
        `footer_text` longtext DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_ne_term` (`taxonomy`, `term_id`),
        KEY `aieo_dmm_idx_ne_priority` (`priority`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    aieo_dmm_ensure_column($table, 'taxonomy',      "varchar(64) NOT NULL DEFAULT 'pa_need'");
    aieo_dmm_ensure_column($table, 'icon_image_id', 'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'hero_image_id', 'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'headline',      'text DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'about_text',    'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'priority',      'int NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'footer_text',   'longtext DEFAULT NULL');
}

function aieo_dmm_create_supplier_extras_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_supplier_extras';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `term_id` bigint unsigned NOT NULL,
        `taxonomy` varchar(64) NOT NULL DEFAULT 'supplier',
        `email` varchar(190) DEFAULT NULL,
        `lead_time_days` int DEFAULT NULL,
        `payment_terms_days` int DEFAULT NULL,
        `special_discount_pct` decimal(5,2) NOT NULL DEFAULT 0,
        `supplier_owner_user_id` bigint unsigned DEFAULT NULL,
        `notes` longtext DEFAULT NULL,
        `erp_id` varchar(64) DEFAULT NULL,
        `erp_sku_code` varchar(32) DEFAULT NULL,
        `vat_code` varchar(32) DEFAULT NULL,
        `address_line_1` varchar(255) DEFAULT NULL,
        `address_region` varchar(100) DEFAULT NULL,
        `address_city` varchar(100) DEFAULT NULL,
        `address_postal_code` varchar(30) DEFAULT NULL,
        `tel_1` varchar(30) DEFAULT NULL,
        `tel_2` varchar(30) DEFAULT NULL,
        `fax` varchar(30) DEFAULT NULL,
        `corporate_url` varchar(190) DEFAULT NULL,
        `corporate_email` varchar(190) DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_se_term` (`taxonomy`, `term_id`),
        KEY `aieo_dmm_idx_se_owner` (`supplier_owner_user_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    aieo_dmm_ensure_column($table, 'taxonomy',                "varchar(64) NOT NULL DEFAULT 'supplier'");
    aieo_dmm_ensure_column($table, 'email',                   'varchar(190) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'lead_time_days',          'int DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'payment_terms_days',      'int DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'special_discount_pct',    'decimal(5,2) NOT NULL DEFAULT 0');
    aieo_dmm_ensure_column($table, 'supplier_owner_user_id',  'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'notes',                   'longtext DEFAULT NULL');
    
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'erp_id',                  'varchar(64) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'erp_sku_code',            'varchar(32) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'vat_code',                'varchar(32) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'address_line_1',          'varchar(255) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'address_region',          'varchar(100) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'address_city',            'varchar(100) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'address_postal_code',     'varchar(30) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'tel_1',                   'varchar(30) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'tel_2',                   'varchar(30) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'fax',                     'varchar(30) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'corporate_url',           'varchar(190) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'corporate_email',         'varchar(190) DEFAULT NULL');
}

function aieo_dmm_create_supplier_preorders_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_supplier_preorders';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `supplier_term_id` bigint unsigned DEFAULT NULL,
        `brand_term_id` bigint unsigned DEFAULT NULL,
        `product_id` bigint unsigned NOT NULL,
        `branch_id` bigint unsigned NOT NULL,
        `scanned_user_id` bigint unsigned NOT NULL DEFAULT 0,
        `requested_qty` int NOT NULL DEFAULT 0,
        `prior_existing` int DEFAULT NULL,
        `fulfilled` tinyint(1) NOT NULL DEFAULT 0,
        `fulfilled_at` datetime DEFAULT NULL,
        `fulfilled_qty` int DEFAULT NULL,
        `status` varchar(20) NOT NULL DEFAULT 'requested',
        `approved_by` bigint unsigned DEFAULT NULL,
        `approved_at` datetime DEFAULT NULL,
        `ordered_at` datetime DEFAULT NULL,
        `received_at` datetime DEFAULT NULL,
        `received_qty` int DEFAULT NULL,
        `approver_note` text DEFAULT NULL,
        `notes` text DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        KEY `aieo_dmm_idx_pr_supplier` (`supplier_term_id`, `fulfilled`, `created_at`),
        KEY `aieo_dmm_idx_pr_branch_product` (`branch_id`, `product_id`),
        KEY `aieo_dmm_idx_pr_product` (`product_id`, `created_at`),
        KEY `aieo_dmm_idx_pr_status` (`status`, `supplier_term_id`, `created_at`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    
    
    
    aieo_dmm_ensure_column($table, 'status',        "varchar(20) NOT NULL DEFAULT 'requested'");
    aieo_dmm_ensure_column($table, 'approved_by',   'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'approved_at',   'datetime DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'ordered_at',    'datetime DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'received_at',   'datetime DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'received_qty',  'int DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'approver_note', 'text DEFAULT NULL');
    
    
    
    
    aieo_dmm_ensure_column($table, 'batch_ref',     'varchar(32) DEFAULT NULL');
    aieo_dmm_ensure_index($table, 'aieo_dmm_idx_pr_batch', '(`batch_ref`)'); 
}

function aieo_dmm_create_preorder_worksheet_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_preorder_worksheet';
    $cc    = aieo_get_charset_collate();
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `branch_id` bigint unsigned NOT NULL,
        `product_id` bigint unsigned NOT NULL,
        `existing_stock` int DEFAULT NULL,
        `desired_qty` int DEFAULT NULL,
        `last_request_id` bigint unsigned DEFAULT NULL,
        `last_submitted_at` datetime DEFAULT NULL,
        `updated_by` bigint unsigned NOT NULL DEFAULT 0,
        `updated_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_pw_branch_product` (`branch_id`, `product_id`),
        KEY `aieo_dmm_idx_pw_pending` (`branch_id`, `desired_qty`)
    ) {$cc};");
}

function aieo_dmm_create_preorder_log_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_preorder_log';
    $cc    = aieo_get_charset_collate();
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `preorder_id` bigint unsigned NOT NULL,
        `created_at` datetime NOT NULL,
        `user_id` bigint unsigned NOT NULL DEFAULT 0,
        `source` varchar(32) NOT NULL DEFAULT 'queue',
        `from_status` varchar(20) DEFAULT NULL,
        `to_status` varchar(20) DEFAULT NULL,
        `qty_before` int DEFAULT NULL,
        `qty_after` int DEFAULT NULL,
        `note` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `aieo_dmm_idx_pl_preorder` (`preorder_id`, `created_at`),
        KEY `aieo_dmm_idx_pl_created` (`created_at`)
    ) {$cc};");
}

function aieo_dmm_create_store_pipeline_tables() {
    global $wpdb;
    $cc = aieo_get_charset_collate();
    $p  = $wpdb->prefix;

    
    
    
    
    
    
    $branch_cols = '';
    foreach (array('total','eshop_soft1','eshop_woo') as $b) {
        $branch_cols .= "`{$b}_30d` int DEFAULT 0,\n        `{$b}_60d` int DEFAULT 0,\n        `trend_of_{$b}` decimal(10,2) DEFAULT NULL,\n        ";
    }
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_store_sales (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(255) DEFAULT NULL,
        `product_id` bigint DEFAULT NULL,
        `image_url` varchar(500) DEFAULT NULL,
        `supplier` varchar(255) DEFAULT NULL,
        `brand` varchar(255) DEFAULT NULL,
        `sku` varchar(255) DEFAULT NULL,
        `sku_norm` varchar(255) DEFAULT NULL,
        `gtin` varchar(255) DEFAULT NULL,
        {$branch_cols}`created` datetime DEFAULT NULL,
        `modified` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_product_id_int` (`product_id`),
        KEY `idx_sku_norm` (`sku_norm`)
    ) {$cc};");

    
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_preorder_vitals (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `product_id` bigint DEFAULT NULL,
        `parent_id` bigint DEFAULT NULL,
        `name` varchar(255) DEFAULT NULL,
        `image_url` varchar(500) DEFAULT NULL,
        `parent_image_url` varchar(500) DEFAULT NULL,
        `brand` varchar(255) DEFAULT NULL,
        `menu_category` varchar(255) DEFAULT NULL,
        `head_category` varchar(255) DEFAULT NULL,
        `sub_category` varchar(255) DEFAULT NULL,
        `supplier_id` bigint DEFAULT NULL,
        `supplier` varchar(255) DEFAULT NULL,
        `sku` varchar(255) DEFAULT NULL,
        `parent_sku` varchar(255) DEFAULT NULL,
        `gtin` varchar(255) DEFAULT NULL,
        `vat` varchar(32) DEFAULT NULL,
        `sku_norm` varchar(255) DEFAULT NULL,
        `created` datetime DEFAULT NULL,
        `modified` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_product_id` (`product_id`),
        KEY `idx_sku_norm` (`sku_norm`),
        KEY `idx_sku` (`sku`)
    ) {$cc};");
    
    
    
    
    
    aieo_dmm_ensure_index("{$p}aieo_dmm_preorder_vitals", 'idx_sku', '(`sku`)');

    
    
    
    
    
    
    $stat_cols = '';
    foreach (array('total','eshop_soft1','eshop_woo') as $b) {
        $stat_cols .= "`{$b}_30d` int DEFAULT 0,\n        `{$b}_60d` int DEFAULT 0,\n        `trend_of_{$b}` decimal(10,2) DEFAULT NULL,\n        ";
    }
    $perbranch = '';
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_preorder_vitals_stats (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `created` datetime DEFAULT NULL,
        `modified` datetime DEFAULT NULL,
        `name` varchar(255) DEFAULT NULL,
        `snapshot_date` date DEFAULT NULL,
        `product_id` bigint DEFAULT NULL,
        `parent_id` bigint DEFAULT NULL,
        `image_url` varchar(500) DEFAULT NULL,
        `parent_image_url` varchar(500) DEFAULT NULL,
        `brand` varchar(255) DEFAULT NULL,
        `menu_category` varchar(255) DEFAULT NULL,
        `head_category` varchar(255) DEFAULT NULL,
        `sub_category` varchar(255) DEFAULT NULL,
        `supplier_id` bigint DEFAULT NULL,
        `supplier` varchar(255) DEFAULT NULL,
        `sku` varchar(255) DEFAULT NULL,
        `parent_sku` varchar(255) DEFAULT NULL,
        `gtin` varchar(255) DEFAULT NULL,
        `vat` varchar(32) DEFAULT NULL,
        `sku_norm` varchar(255) DEFAULT NULL,
        {$stat_cols}{$perbranch}PRIMARY KEY (`id`),
        KEY `idx_product_id` (`product_id`),
        KEY `idx_snapshot` (`snapshot_date`),
        KEY `idx_sku_norm` (`sku_norm`)
    ) {$cc};");

    
    
    
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_supplier_sku_map (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `sku` varchar(255) NOT NULL,
        `sku_norm` varchar(255) DEFAULT NULL,
        `product_id` bigint DEFAULT NULL,
        `supplier_term_id` bigint unsigned DEFAULT NULL,
        `supplier_erp_id` varchar(64) DEFAULT NULL,
        `supplier_name` varchar(255) DEFAULT NULL,
        `source` varchar(20) NOT NULL DEFAULT 'erp',
        `updated_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_sku` (`sku`),
        KEY `idx_sku_norm` (`sku_norm`),
        KEY `idx_product` (`product_id`),
        KEY `idx_supplier` (`supplier_term_id`)
    ) {$cc};");
}

function aieo_dmm_create_branch_stats_tables() {
    global $wpdb;
    $cc = aieo_get_charset_collate();
    $p  = $wpdb->prefix;

    $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_branch_sales (
        `branch_id` bigint unsigned NOT NULL,
        `sku_norm` varchar(255) NOT NULL,
        `product_id` bigint DEFAULT NULL,
        `sales_30d` int NOT NULL DEFAULT 0,
        `sales_60d` int NOT NULL DEFAULT 0,
        `trend` decimal(10,2) DEFAULT NULL,
        `snapshot_date` date DEFAULT NULL,
        PRIMARY KEY (`branch_id`, `sku_norm`),
        KEY `idx_bs_product` (`product_id`)
    ) {$cc};");

    $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_branch_phys (
        `scope` varchar(12) NOT NULL,
        `ref_id` bigint unsigned NOT NULL,
        `branch_id` bigint unsigned NOT NULL,
        `units` decimal(12,3) NOT NULL DEFAULT 0,
        `units_recent` decimal(12,3) NOT NULL DEFAULT 0,
        PRIMARY KEY (`scope`, `ref_id`, `branch_id`),
        KEY `idx_bp_branch` (`branch_id`)
    ) {$cc};");
}

function aieo_dmm_migrate_branch_wide_to_narrow() {
    global $wpdb;
    $p    = $wpdb->prefix;
    $bp   = $p . 'aieo_dmm_branch_phys';
    $reg  = $p . 'aieo_dmm_branches';
    if (!aieo_dmm_table_exists($bp) || !aieo_dmm_table_exists($reg)) { return; }

    $col_exists = static function ($table, $col) use ($wpdb) {
        return (int) $wpdb->get_var($wpdb->prepare(
            'SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s',
            $table, $col
        )) > 0;
    };

    $branches = (array) $wpdb->get_results("SELECT id, slug FROM {$reg}", ARRAY_A);

    
    $cand = $p . 'aieo_dmm_stock_recovery_candidates';
    $gap  = $p . 'aieo_dmm_catalog_gap_candidates';
    $ev   = $p . 'aieo_dmm_stock_recovery_events';
    foreach ($branches as $b) {
        $slug = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $b['slug']));
        $bid  = (int) $b['id'];
        if ($slug === '' || $bid <= 0) { continue; }
        if (aieo_dmm_table_exists($ev) && $col_exists($ev, 'phys_' . $slug . '_recent')) {
            $wpdb->query("INSERT IGNORE INTO {$bp} (scope, ref_id, branch_id, units, units_recent)
                SELECT 'event', e.id, {$bid}, 0, e.`phys_{$slug}_recent` FROM {$ev} e
                 WHERE COALESCE(e.`phys_{$slug}_recent`, 0) <> 0");
        }
        if (aieo_dmm_table_exists($cand) && $col_exists($cand, 'phys_' . $slug)) {
            $wpdb->query("INSERT IGNORE INTO {$bp} (scope, ref_id, branch_id, units, units_recent)
                SELECT 'recovery', c.id, {$bid}, COALESCE(c.`phys_{$slug}`, 0), COALESCE(c.`phys_{$slug}_recent`, 0) FROM {$cand} c
                 WHERE COALESCE(c.`phys_{$slug}`, 0) <> 0 OR COALESCE(c.`phys_{$slug}_recent`, 0) <> 0");
        }
        if (aieo_dmm_table_exists($gap) && $col_exists($gap, 'phys_' . $slug)) {
            $wpdb->query("INSERT IGNORE INTO {$bp} (scope, ref_id, branch_id, units, units_recent)
                SELECT 'gap', g.id, {$bid}, COALESCE(g.`phys_{$slug}`, 0), 0 FROM {$gap} g
                 WHERE COALESCE(g.`phys_{$slug}`, 0) <> 0");
        }
    }

    
    
    
    
    $legacy_slugs = array('piraeus', 'keratsini', 'korydallos', 'kallithea');
    foreach ($branches as $b) {
        $s = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $b['slug']));
        if ($s !== '' && !in_array($s, $legacy_slugs, true)) { $legacy_slugs[] = $s; }
    }
    
    
    
    $legacy_slugs = array_values(array_diff($legacy_slugs, array('total', 'eshop_soft1', 'eshop_woo', 'eshop')));
    $sales = $p . 'aieo_dmm_store_sales';
    $stats = $p . 'aieo_dmm_preorder_vitals_stats';
    foreach ($legacy_slugs as $s) {
        foreach (array(
            array($sales, array($s . '_30d', $s . '_60d', 'trend_of_' . $s)),
            array($stats, array($s . '_30d', $s . '_60d', 'trend_of_' . $s, 'existing_stock_' . $s, 'desired_qty_' . $s, 'last_pending_id_' . $s)),
            array($cand,  array('phys_' . $s, 'phys_' . $s . '_recent')),
            array($gap,   array('phys_' . $s)),
            array($ev,    array('phys_' . $s . '_recent')),
        ) as $spec) {
            list($table, $cols) = $spec;
            if (!aieo_dmm_table_exists($table)) { continue; }
            $drops = array();
            foreach ($cols as $c) {
                if ($col_exists($table, $c)) { $drops[] = "DROP COLUMN `{$c}`"; }
            }
            if (!empty($drops)) {
                $wpdb->query("ALTER TABLE {$table} " . implode(', ', $drops));
            }
        }
    }
}

function aieo_dmm_backfill_legacy_branch_matchers() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_branches';
    if (!aieo_dmm_table_exists($table)) { return; }

    $legacy_patterns = array(
        'piraeus'    => 'ΠΕΙΡΑΙΑΣ',
        'keratsini'  => 'ΚΕΡΑΤΣΙΝΙ',
        'korydallos' => 'ΚΟΡΥΔΑΛΛΟΣ|ΝΙΚΑΙΑ',
        'kallithea'  => 'ΚΑΛΛΙΘΕΑ',
    );
    $seen_legacy = false;
    foreach ($legacy_patterns as $slug => $patterns) {
        $updated = $wpdb->query($wpdb->prepare(
            "UPDATE {$table} SET match_patterns = %s WHERE slug = %s AND (match_patterns IS NULL OR match_patterns = '')",
            $patterns, $slug
        ));
        $has_row = $updated || $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE slug = %s LIMIT 1", $slug));
        if ($has_row) { $seen_legacy = true; }
    }

    
    
    
    if ($seen_legacy && false === get_option('aieo_dmm_erp_eshop_invoice_type', false)) {
        add_option('aieo_dmm_erp_eshop_invoice_type', '7069', '', false);
    }

    if (!class_exists('AIEO_DMM_Branches')) {
        $f = AIEO_DIR . 'includes/dmm/services/class-aieo-dmm-branches.php';
        if (is_readable($f)) { require_once $f; }
    }
    if (class_exists('AIEO_DMM_Branches')) {
        AIEO_DMM_Branches::sync();
    }
}

function aieo_dmm_create_physical_recovery_tables() {
    global $wpdb;
    $cc = aieo_get_charset_collate();
    $p  = $wpdb->prefix;

    
    
    
    
    
    $pb = '';
    $pbr = '';

    
    
    
    $cand = $p . 'aieo_dmm_stock_recovery_candidates';
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$cand} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `product_id` bigint DEFAULT NULL,
        `parent_id` bigint DEFAULT NULL,
        `is_variation` tinyint(1) DEFAULT 0,
        `sku` varchar(255) DEFAULT NULL,
        `sku_norm` varchar(255) DEFAULT NULL,
        `name` varchar(255) DEFAULT NULL,
        `image_url` varchar(500) DEFAULT NULL,
        `oos_since` date DEFAULT NULL,
        `oos_source` varchar(16) DEFAULT NULL,
        `days_oos` int DEFAULT NULL,
        `eshop_stock` int DEFAULT NULL,
        `phys_units_after` decimal(12,3) DEFAULT 0,
        `phys_units_recent` decimal(12,3) DEFAULT 0,
        `phys_sale_days` int DEFAULT 0,
        `last_phys_sale_date` date DEFAULT NULL,
        `phys_turnover_after` decimal(14,2) DEFAULT 0,
        `phys_turnover_recent` decimal(14,2) DEFAULT 0,
        `eshop_price` decimal(20,2) DEFAULT NULL,
        `est_lost_sales` decimal(14,2) DEFAULT 0,
        `lost_optimistic` decimal(14,2) DEFAULT NULL,
        `lost_pessimistic` decimal(14,2) DEFAULT NULL,
        `lost_average` decimal(14,2) DEFAULT NULL,
        {$pb}{$pbr}`phys_basket_turnover_recent` decimal(16,2) DEFAULT 0,
        `phys_basket_orders_recent` int DEFAULT 0,
        `phys_branch_count` tinyint DEFAULT 0,
        `velocity_per_day` decimal(12,4) DEFAULT 0,
        `suggested_restock_qty` int DEFAULT 0,
        `meets_threshold` tinyint(1) DEFAULT 0,
        `confidence_score` decimal(5,2) DEFAULT NULL,
        `blocked_reason` varchar(32) DEFAULT NULL,
        `status` varchar(16) DEFAULT 'pending',
        `last_event_id` bigint unsigned DEFAULT NULL,
        `computed_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_product` (`product_id`),
        KEY `idx_meets_status` (`meets_threshold`,`status`),
        KEY `idx_last_sale` (`last_phys_sale_date`),
        KEY `idx_sku_norm` (`sku_norm`)
    ) {$cc};");

    
    
    $gap = $p . 'aieo_dmm_catalog_gap_candidates';
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$gap} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `product_id` bigint DEFAULT NULL,
        `sku` varchar(255) DEFAULT NULL,
        `sku_norm` varchar(255) NOT NULL,
        `name` varchar(255) DEFAULT NULL,
        `supplier_name` varchar(255) DEFAULT NULL,
        `barcode` varchar(255) DEFAULT NULL,
        `post_status` varchar(20) DEFAULT NULL,
        `product_type` varchar(32) DEFAULT NULL,
        `phys_units` decimal(12,3) DEFAULT 0,
        `phys_units_recent` decimal(12,3) DEFAULT 0,
        `phys_sale_days` int DEFAULT 0,
        `last_phys_sale_date` date DEFAULT NULL,
        `phys_turnover` decimal(14,2) DEFAULT 0,
        `phys_units_30d` decimal(12,3) DEFAULT 0,
        `phys_turnover_30d` decimal(14,2) DEFAULT 0,
        {$pb}`velocity_per_day` decimal(12,4) DEFAULT 0,
        `priority_score` decimal(14,2) DEFAULT 0,
        `missing_image` tinyint(1) DEFAULT 0,
        `missing_desc` tinyint(1) DEFAULT 0,
        `missing_cats` tinyint(1) DEFAULT 0,
        `missing_brand` tinyint(1) DEFAULT 0,
        `needs_creation` tinyint(1) DEFAULT 0,
        `status` varchar(16) DEFAULT 'pending',
        `computed_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uq_sku_norm` (`sku_norm`),
        KEY `idx_priority` (`priority_score`),
        KEY `idx_status` (`status`)
    ) {$cc};");

    
    $ev = $p . 'aieo_dmm_stock_recovery_events';
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$ev} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `product_id` bigint DEFAULT NULL,
        `parent_id` bigint DEFAULT NULL,
        `sku` varchar(255) DEFAULT NULL,
        `mode` varchar(12) DEFAULT 'manual',
        `applied_by` bigint unsigned DEFAULT NULL,
        `applied_at` datetime DEFAULT NULL,
        `oos_since_at_apply` date DEFAULT NULL,
        `days_oos_at_apply` int DEFAULT NULL,
        `phys_units_after_at_apply` decimal(12,3) DEFAULT NULL,
        `phys_sale_days_at_apply` int DEFAULT NULL,
        `phys_turnover_recent_at_apply` decimal(14,2) DEFAULT NULL,
        `phys_turnover_after_at_apply` decimal(14,2) DEFAULT NULL,
        `phys_basket_turnover_at_apply` decimal(16,2) DEFAULT NULL,
        `restock_qty` int DEFAULT NULL,
        `prior_stock` int DEFAULT NULL,
        `prior_stock_status` varchar(16) DEFAULT NULL,
        `prior_manage_stock` tinyint(1) DEFAULT NULL,
        `thresholds_json` longtext DEFAULT NULL,
        `outcome` varchar(16) DEFAULT 'pending',
        `outcome_reason` varchar(32) DEFAULT NULL,
        `eshop_units_recovered` decimal(12,3) DEFAULT 0,
        `eshop_turnover_recovered` decimal(14,2) DEFAULT 0,
        `eshop_orders_recovered` int DEFAULT 0,
        `eshop_basket_turnover` decimal(16,2) DEFAULT 0,
        `eshop_units_draft` decimal(12,3) DEFAULT 0,
        `eshop_turnover_draft` decimal(14,2) DEFAULT 0,
        `eshop_orders_draft` int DEFAULT 0,
        `eshop_basket_draft` decimal(16,2) DEFAULT 0,
        `failed_units` decimal(12,3) DEFAULT 0,
        `reverted_at` datetime DEFAULT NULL,
        `outcome_evaluated_at` datetime DEFAULT NULL,
        `created_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_product_applied` (`product_id`,`applied_at`),
        KEY `idx_outcome` (`outcome`),
        KEY `idx_mode` (`mode`)
    ) {$cc};");

    
    
    
    
    $ge = $p . 'aieo_dmm_catalog_gap_events';
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$ge} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `product_id` bigint DEFAULT NULL,
        `sku` varchar(255) DEFAULT NULL,
        `sku_norm` varchar(255) DEFAULT NULL,
        `name` varchar(255) DEFAULT NULL,
        `published_at` datetime DEFAULT NULL,
        `published_by` bigint unsigned DEFAULT NULL,
        `was_needs_creation` tinyint(1) DEFAULT 0,
        `post_status_before` varchar(20) DEFAULT NULL,
        `phys_units_recent_at_publish` decimal(12,3) DEFAULT NULL,
        `phys_turnover_recent_at_publish` decimal(14,2) DEFAULT NULL,
        `phys_turnover_at_publish` decimal(14,2) DEFAULT NULL,
        `eshop_units_since` decimal(12,3) DEFAULT 0,
        `eshop_turnover_since` decimal(14,2) DEFAULT 0,
        `eshop_orders_since` int DEFAULT 0,
        `unpublished_at` datetime DEFAULT NULL,
        `outcome_evaluated_at` datetime DEFAULT NULL,
        `created_at` datetime DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_product` (`product_id`),
        KEY `idx_sku_norm` (`sku_norm`),
        KEY `idx_published` (`published_at`)
    ) {$cc};");

    
    aieo_dmm_ensure_column($ev, 'eshop_orders_recovered', 'int DEFAULT 0');
    aieo_dmm_ensure_column($ev, 'failed_units',           'decimal(12,3) DEFAULT 0');
    aieo_dmm_ensure_column($ev, 'outcome_reason',         "varchar(32) DEFAULT NULL");
    
    
    
    aieo_dmm_ensure_column($ev, 'phys_turnover_recent_at_apply', 'decimal(14,2) DEFAULT NULL');
    aieo_dmm_ensure_column($ev, 'phys_turnover_after_at_apply',  'decimal(14,2) DEFAULT NULL');
    
    
    aieo_dmm_ensure_column($ev, 'phys_basket_turnover_at_apply', 'decimal(16,2) DEFAULT NULL');
    
    
    
    aieo_dmm_ensure_column($ev, 'eshop_basket_turnover', 'decimal(16,2) DEFAULT 0');
    
    
    
    aieo_dmm_ensure_column($ev, 'eshop_units_draft',    'decimal(12,3) DEFAULT 0');
    aieo_dmm_ensure_column($ev, 'eshop_turnover_draft', 'decimal(14,2) DEFAULT 0');
    aieo_dmm_ensure_column($ev, 'eshop_orders_draft',   'int DEFAULT 0');
    
    
    aieo_dmm_ensure_column($ev, 'eshop_basket_draft',   'decimal(16,2) DEFAULT 0');
    
    
    
    
    
    
    aieo_dmm_ensure_column($gap, 'phys_units_30d',    'decimal(12,3) DEFAULT 0');
    aieo_dmm_ensure_column($gap, 'phys_turnover_30d', 'decimal(14,2) DEFAULT 0');
    aieo_dmm_ensure_column($cand, 'phys_basket_turnover_recent', 'decimal(16,2) DEFAULT 0');
    aieo_dmm_ensure_column($cand, 'phys_basket_orders_recent',   'int DEFAULT 0');
    aieo_dmm_ensure_column($cand, 'confidence_score',     'decimal(5,2) DEFAULT NULL');
    aieo_dmm_ensure_column($cand, 'blocked_reason',       'varchar(32) DEFAULT NULL');
    aieo_dmm_ensure_column($cand, 'phys_turnover_after',  'decimal(14,2) DEFAULT 0');
    aieo_dmm_ensure_column($cand, 'phys_turnover_recent', 'decimal(14,2) DEFAULT 0');
    aieo_dmm_ensure_column($cand, 'eshop_price',          'decimal(20,2) DEFAULT NULL');
    aieo_dmm_ensure_column($cand, 'est_lost_sales',       'decimal(14,2) DEFAULT NULL');
    aieo_dmm_ensure_column($cand, 'lost_optimistic',      'decimal(14,2) DEFAULT NULL');
    aieo_dmm_ensure_column($cand, 'lost_pessimistic',     'decimal(14,2) DEFAULT NULL');
    aieo_dmm_ensure_column($cand, 'lost_average',         'decimal(14,2) DEFAULT NULL');
}

function aieo_dmm_ensure_store_board($slug) {
    global $wpdb;
    $slug = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $slug));
    if ($slug === '') { return false; }
    $table = $wpdb->prefix . 'store_board_' . $slug;
    $cc = aieo_get_charset_collate();

    
    
    
    
    $cols = array(
        'product_id'                   => 'bigint unsigned NOT NULL',
        'parent_id'                    => 'int DEFAULT NULL',
        'image_url'                    => 'varchar(500) DEFAULT NULL',
        'parent_image_url'             => 'varchar(500) DEFAULT NULL',
        'brand'                        => 'varchar(255) DEFAULT NULL',
        'product_name'                 => 'varchar(255) DEFAULT NULL',
        'supplier'                     => 'varchar(255) DEFAULT NULL',
        'menu_category'                => 'varchar(255) DEFAULT NULL',
        'head_category'                => 'varchar(255) DEFAULT NULL',
        'sub_category'                 => 'varchar(255) DEFAULT NULL',
        'sku'                          => 'varchar(255) DEFAULT NULL',
        'parent_sku'                   => 'varchar(255) DEFAULT NULL',
        'gtin'                         => 'varchar(255) DEFAULT NULL',
        'barcode_index'                => 'longtext DEFAULT NULL',
        'sales_30d'                    => 'int DEFAULT NULL',
        'sales_60d'                    => 'int DEFAULT NULL',
        'sales_trend'                  => 'decimal(10,2) DEFAULT NULL',
        'existing_stock'               => 'int DEFAULT NULL',
        'desired_qty'                  => 'int DEFAULT NULL',
        'latest_desired_qty'           => 'int DEFAULT NULL',
        'latest_operator_modification' => 'datetime DEFAULT NULL',
        'received_qty'                 => 'int DEFAULT NULL',
        'supplier_preorder_id'         => 'int DEFAULT NULL',
        'snapshot_date'                => 'date DEFAULT NULL',
        'created'                      => 'datetime NOT NULL',
        'modified'                     => 'datetime NOT NULL',
        'supplier_id'                  => 'int DEFAULT NULL',
        'status'                       => 'tinyint unsigned DEFAULT NULL',
        'logger'                       => 'text DEFAULT NULL',
        'logger_seq'                   => 'int unsigned NOT NULL DEFAULT 0',
        'logger_last_user'             => 'varchar(191) DEFAULT NULL',
        'logger_last_device'           => 'varchar(64) DEFAULT NULL',
        'status_name'                  => 'varchar(32) DEFAULT NULL',
        'origin_type'                  => 'varchar(32) DEFAULT NULL',
        'origin_user'                  => 'varchar(191) DEFAULT NULL',
        'status_id'                    => 'tinyint unsigned DEFAULT NULL',
        'sku_norm'                     => 'varchar(255) DEFAULT NULL',
    );

    $defs = '';
    foreach ($cols as $name => $def) { $defs .= "`{$name}` {$def},\n        "; }
    $wpdb->query("CREATE TABLE IF NOT EXISTS `{$table}` (
        {$defs}PRIMARY KEY (`product_id`),
        KEY `idx_brand` (`brand`),
        KEY `idx_sku` (`sku`),
        KEY `idx_gtin` (`gtin`),
        KEY `idx_snapshot` (`snapshot_date`),
        KEY `idx_supplier_id` (`supplier_id`),
        KEY `idx_status_id` (`status_id`)
    ) {$cc};");

    
    
    foreach ($cols as $name => $def) {
        if ($name === 'product_id') { continue; }
        aieo_dmm_ensure_column($table, $name, $def);
    }
    return $table;
}

function aieo_dmm_create_store_order_log_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_store_order_log';
    $cc = aieo_get_charset_collate();
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(191) DEFAULT NULL,
        `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `modified` datetime DEFAULT NULL,
        `product_id` bigint DEFAULT NULL,
        `branch_id` bigint unsigned DEFAULT NULL,
        `board_slug` varchar(64) DEFAULT NULL,
        `user_id` bigint unsigned DEFAULT NULL,
        `existing_stock` int DEFAULT NULL,
        `desired_qty` int DEFAULT NULL,
        `status_id` tinyint unsigned DEFAULT NULL,
        `origin_type` varchar(32) DEFAULT NULL,
        `origin_user` varchar(191) DEFAULT NULL,
        `origin_device` varchar(64) DEFAULT NULL,
        `sales_trend` decimal(10,2) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_product` (`product_id`, `created`),
        KEY `idx_branch` (`branch_id`, `created`)
    ) {$cc};");
}

function aieo_dmm_ensure_store_status_lookup() {
    global $wpdb;
    $table = $wpdb->prefix . 'store_order_status_lookup';
    $cc = aieo_get_charset_collate();
    $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} (
        `status_id` tinyint unsigned NOT NULL,
        `status_name` varchar(64) NOT NULL,
        `is_final` tinyint(1) NOT NULL DEFAULT 0,
        `sort_order` int NOT NULL DEFAULT 0,
        PRIMARY KEY (`status_id`)
    ) {$cc};");
    $n = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
    if ($n === 0) {
        $wpdb->query("INSERT INTO {$table} (status_id, status_name, is_final, sort_order) VALUES
            (0, 'Ανοιχτή', 0, 1), (1, 'Ολοκληρωμένη', 1, 2), (2, 'Παραλήφθηκε', 1, 3)");
    }
}

function aieo_dmm_ensure_store_board_triggers($slug, $branch_id = 0) { 
    global $wpdb;
    $slug = preg_replace("/[^a-z0-9_]/", "", strtolower((string) $slug));
    if ($slug === "") { return; }
    foreach (array("logger_before", "logger_after", "preorder_upsert") as $which) {
        $wpdb->query("DROP TRIGGER IF EXISTS `trg_{$slug}_{$which}`");
    }
}

function aieo_dmm_ensure_all_store_boards() {
    global $wpdb;
    $reg = $wpdb->prefix . 'aieo_dmm_branches';
    if (!aieo_dmm_table_exists($reg)) { return; }
    aieo_dmm_ensure_store_status_lookup();
    $rows = $wpdb->get_results("SELECT id, slug FROM {$reg} WHERE enabled = 1", ARRAY_A);
    foreach ((array) $rows as $r) {
        $slug = (string) $r['slug'];
        if (aieo_dmm_ensure_store_board($slug)) {
            aieo_dmm_ensure_store_board_triggers($slug, (int) $r['id']);
        }
    }
}

function aieo_dmm_create_term_relations_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_term_relations';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `source_taxonomy` varchar(64) NOT NULL,
        `source_term_id` bigint unsigned NOT NULL,
        `target_kind` varchar(8) NOT NULL,
        `target_subtype` varchar(64) NOT NULL,
        `target_id` bigint unsigned NOT NULL,
        `rel_type` varchar(20) NOT NULL DEFAULT 'related',
        `weight` int NOT NULL DEFAULT 0,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_tr_edge` (`source_taxonomy`, `source_term_id`, `target_kind`, `target_subtype`, `target_id`, `rel_type`),
        KEY `aieo_dmm_idx_tr_src` (`source_taxonomy`, `source_term_id`, `target_kind`, `target_subtype`),
        KEY `aieo_dmm_idx_tr_tgt` (`target_kind`, `target_subtype`, `target_id`, `source_taxonomy`)
    ) {$charset_collate};";

    $wpdb->query($sql);
}

function aieo_dmm_create_swatches_variation_map_table() {
    global $wpdb;

    $table           = $wpdb->prefix . 'aieo_dmm_swatches_variation_map';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `variation_id` bigint unsigned NOT NULL,
        `product_id` bigint unsigned NOT NULL,
        `attribute_combo_hash` char(40) NOT NULL DEFAULT '',
        `image_url` varchar(512) DEFAULT NULL,
        `swatch_image_url` varchar(512) DEFAULT NULL,
        `sku` varchar(100) DEFAULT NULL,
        `stock_status` varchar(20) DEFAULT NULL,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`variation_id`)
    ) {$charset_collate};";

    $wpdb->query($sql);

    
    
    
    
    
    $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$table}" );
    if ( is_array( $cols ) && ! in_array( 'swatch_image_url', $cols, true ) ) {
        $wpdb->query( "ALTER TABLE {$table} ADD COLUMN `swatch_image_url` varchar(512) DEFAULT NULL AFTER `image_url`" );
    }

    aieo_create_index_if_not_exists('aieo_dmm_idx_swmap_prod',  $table, '(product_id)');
    aieo_create_index_if_not_exists('aieo_dmm_idx_swmap_hash',  $table, '(attribute_combo_hash)');
}

function aieo_dmm_create_cancellation_reasons_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_cancellation_reasons';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `slug` varchar(64) NOT NULL,
        `label` varchar(190) NOT NULL,
        `is_customer_visible` tinyint NOT NULL DEFAULT 1,
        `default_event_kind` tinyint DEFAULT NULL,
        `default_instigator` varchar(10) NOT NULL DEFAULT '',
        `default_frame` varchar(20) NOT NULL DEFAULT '',
        `sort_order` int NOT NULL DEFAULT 0,
        `enabled` tinyint NOT NULL DEFAULT 1,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_cr_slug` (`slug`)
    ) {$charset_collate};";
    $wpdb->query($sql);

    aieo_dmm_ensure_column($table, 'default_event_kind', 'tinyint DEFAULT NULL');

    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'default_instigator', "varchar(10) NOT NULL DEFAULT ''");
    aieo_dmm_ensure_column($table, 'default_frame',      "varchar(20) NOT NULL DEFAULT ''");
}

function aieo_dmm_seed_default_cancellation_reasons() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_cancellation_reasons';
    $defaults = aieo_dmm_cancellation_reason_defaults();

    foreach ($defaults as $row) {
        list($slug, $label, $visible, $sort, $event_kind, $instigator, $frame) = $row;
        
        if ($event_kind === null) {
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO {$table}
                    (slug, label, is_customer_visible, sort_order, enabled, default_event_kind, default_instigator, default_frame)
                 VALUES (%s, %s, %d, %d, 1, NULL, %s, %s)",
                $slug, $label, (int) $visible, (int) $sort, $instigator, $frame
            ));
        } else {
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO {$table}
                    (slug, label, is_customer_visible, sort_order, enabled, default_event_kind, default_instigator, default_frame)
                 VALUES (%s, %s, %d, %d, 1, %d, %s, %s)",
                $slug, $label, (int) $visible, (int) $sort, (int) $event_kind, $instigator, $frame
            ));
        }
    }

    
    
    
    
    foreach ($defaults as $row) {
        list($slug, , , , , $instigator, $frame) = $row;
        if ($instigator !== '') {
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table} SET default_instigator = %s WHERE slug = %s AND (default_instigator IS NULL OR default_instigator = '')",
                $instigator, $slug
            ));
        }
        if ($frame !== '') {
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table} SET default_frame = %s WHERE slug = %s AND (default_frame IS NULL OR default_frame = '')",
                $frame, $slug
            ));
        }
    }

    
    
    
    
    
    
    
    
    if (get_option('aieo_dmm_cr_instigator_v2') !== 'done') {
        $corrections = array(
            
            array('payment_failed',          'system',   'customer'),
            
            array('address_unreachable',     'shop',     'customer'),
            
            array('duplicate_order',         'shop',     'customer'),
            array('fraud_suspected',         'shop',     'customer'),
            
            
            array('quality_issue',           'customer', 'shop'),
            array('wrong_item_sent',         'customer', 'shop'),
            array('late_delivery_returned',  'customer', 'shop'),
            array('customer_kept_refunded',  'customer', 'shop'),
            array('damaged_in_transit_kept', 'customer', 'shop'),
            array('not_received_claim',      'customer', 'shop'),
        );
        foreach ($corrections as $c) {
            $wpdb->query($wpdb->prepare(
                "UPDATE {$table} SET default_instigator = %s WHERE slug = %s AND default_instigator = %s",
                $c[2], $c[0], $c[1]
            ));
        }
        
        $wpdb->query("UPDATE {$table} SET default_instigator = 'customer' WHERE default_instigator = 'system'");
        update_option('aieo_dmm_cr_instigator_v2', 'done', false);
    }

    
    
    
    
    
    
    
    
    
    
    
    
    
    $kind_defaults = array(
        'out_of_stock'                => 0,
        'customer_changed_mind'       => 0,
        'payment_failed'              => 0,
        'fraud_suspected'             => 0,
        'duplicate_order'             => 0,
        'quality_issue'               => 1,
        'wrong_item_sent'             => 1,
        'wrong_size'                  => 1,
        'late_delivery'               => 1, 
        'late_delivery_returned'      => 1,
        'customer_returned'           => 1,
        'customer_kept_refunded'      => 2,
        'damaged_in_transit_kept'     => 2,
        'address_unreachable'         => 3, 
        'refused_on_arrival'          => 3,
        'never_collected_from_pickup' => 3,
        'not_received_claim'          => 4,
        'lost_in_transit'             => 4,
    );
    foreach ($kind_defaults as $slug => $kind) {
        $wpdb->query($wpdb->prepare(
            "UPDATE {$table}
                SET default_event_kind = %d
              WHERE slug = %s
                AND default_event_kind IS NULL",
            (int) $kind, $slug
        ));
    }

    
    if (class_exists('AIEO_DMM_Cancellation_Reasons_Data')
        && method_exists('AIEO_DMM_Cancellation_Reasons_Data', 'flush_cache')) {
        AIEO_DMM_Cancellation_Reasons_Data::flush_cache();
    }
    wp_cache_delete('aieo_cr_all', 'aieo_dmm');
}

function aieo_dmm_cancellation_reason_defaults() {
    return array(
        
        array('out_of_stock',          'Out of stock',                            1,  10, 0, 'shop',     'none'),
        
        array('duplicate_order',       'Duplicate order',                         1,  60, 0, 'customer', 'none'),
        
        array('fraud_suspected',       'Suspected fraud',                         0,  50, 0, 'customer', 'none'),
        
        
        array('payment_failed',        'Payment failed',                          1,  30, 0, 'customer', 'none'),
        array('customer_changed_mind', 'Customer changed mind (before shipping)', 1,  20, 0, 'customer', 'withdrawal'),

        
        
        
        array('quality_issue',          'Quality issue (returned after receipt)',      1,  70, 1, 'shop',     'faulty_goods'),
        array('wrong_item_sent',        'Wrong item sent (returned after receipt)',    1,  75, 1, 'shop',     'faulty_goods'),
        
        array('wrong_size',             'Wrong size / fit (returned after receipt)',   1,  80, 1, 'customer', 'withdrawal'),
        
        
        array('late_delivery_returned', 'Late delivery (returned after receipt)',      1,  90, 1, 'shop',     'withdrawal'),
        array('customer_returned',      'Customer returned the items (after receipt)', 1,  95, 1, 'customer', 'withdrawal'),

        
        
        
        array('customer_kept_refunded',  'Refunded but customer kept items',              0, 110, 2, 'shop', 'faulty_goods'),
        array('damaged_in_transit_kept', 'Damaged in transit (customer kept + refunded)', 0, 115, 2, 'shop', 'faulty_goods'),

        
        
        
        array('refused_on_arrival',          'Refused on arrival (never opened)',     0, 130, 3, 'customer', 'withdrawal'),
        array('never_collected_from_pickup', 'Never collected from pickup point',     0, 140, 3, 'customer', 'withdrawal'),
        
        array('address_unreachable',         'Address unreachable (never delivered)', 1,  40, 3, 'customer', 'none'),

        
        
        
        
        
        array('not_received_claim', 'Customer reports non-delivery (under investigation)', 1, 118, 4, 'shop', 'non_delivery'),
        array('lost_in_transit',    'Lost in transit (confirmed written off)',             0, 120, 4, 'shop', 'non_delivery'),

        
        
        
        array('other', 'Other (see notes)', 1, 100, null, '', ''),
    );
}

function aieo_dmm_create_withdrawal_declarations_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_withdrawal_declarations';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `order_id` bigint unsigned NOT NULL,
        `user_id` bigint unsigned NOT NULL DEFAULT 0,
        `billing_email` varchar(190) NOT NULL DEFAULT '',
        `tier` varchar(20) NOT NULL DEFAULT 'declaration',
        `channel` varchar(20) NOT NULL DEFAULT 'account',
        `is_full` tinyint NOT NULL DEFAULT 1,
        `product_ids` longtext DEFAULT NULL,
        `item_ids` longtext DEFAULT NULL,
        `reason_slug` varchar(64) DEFAULT NULL,
        `reason_note` varchar(500) DEFAULT NULL,
        `order_total` decimal(12,2) NOT NULL DEFAULT '0.00',
        `declared_total` decimal(12,2) NOT NULL DEFAULT '0.00',
        `status` varchar(20) NOT NULL DEFAULT 'pending',
        `receipt_basis` varchar(20) NOT NULL DEFAULT '',
        `deadline_gmt` datetime DEFAULT NULL,
        `declared_at_gmt` datetime NOT NULL,
        `acknowledged_at_gmt` datetime DEFAULT NULL,
        `processed_at_gmt` datetime DEFAULT NULL,
        `processed_by_user_id` bigint unsigned NOT NULL DEFAULT 0,
        `admin_note` varchar(500) DEFAULT NULL,
        `client_ip_hash` char(32) DEFAULT NULL,
        `received_at_gmt` datetime DEFAULT NULL,
        `refund_amount` decimal(12,2) DEFAULT NULL,
        `refund_method` varchar(20) DEFAULT NULL,
        `refunded_at_gmt` datetime DEFAULT NULL,
        `return_carrier` varchar(32) DEFAULT NULL,
        `return_voucher_no` varchar(64) DEFAULT NULL,
        `return_voucher_at_gmt` datetime DEFAULT NULL,
        `return_voucher_error` varchar(255) DEFAULT NULL,
        `return_shipping_cost` decimal(12,2) DEFAULT NULL,
        `approved_at_gmt` datetime DEFAULT NULL,
        `approved_by_user_id` bigint unsigned NOT NULL DEFAULT 0,
        `return_pickup` longtext DEFAULT NULL,
        `return_label_path` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `aieo_dmm_idx_wd_order` (`order_id`, `declared_at_gmt`),
        KEY `aieo_dmm_idx_wd_status` (`status`, `declared_at_gmt`),
        KEY `aieo_dmm_idx_wd_user` (`user_id`, `declared_at_gmt`),
        KEY `aieo_dmm_idx_wd_email` (`billing_email`, `declared_at_gmt`),
        KEY `aieo_dmm_idx_wd_tier` (`tier`, `declared_at_gmt`)
    ) {$charset_collate};";
    $wpdb->query($sql);

    
    
    
    
    
    if (function_exists('aieo_dmm_ensure_column')) {
        aieo_dmm_ensure_column($table, 'received_at_gmt', "datetime DEFAULT NULL");
        aieo_dmm_ensure_column($table, 'refund_amount',   "decimal(12,2) DEFAULT NULL");
        aieo_dmm_ensure_column($table, 'refunded_at_gmt', "datetime DEFAULT NULL");
        
        
        
        
        aieo_dmm_ensure_column($table, 'refund_method',    "varchar(20) DEFAULT NULL");

        
        
        
        
        
        
        
        aieo_dmm_ensure_column($table, 'return_carrier',        "varchar(32) DEFAULT NULL");
        aieo_dmm_ensure_column($table, 'return_voucher_no',     "varchar(64) DEFAULT NULL");
        aieo_dmm_ensure_column($table, 'return_voucher_at_gmt', "datetime DEFAULT NULL");
        
        
        aieo_dmm_ensure_column($table, 'return_voucher_error',  "varchar(255) DEFAULT NULL");
        aieo_dmm_ensure_column($table, 'return_shipping_cost',  "decimal(12,2) DEFAULT NULL");

        
        
        
        
        
        aieo_dmm_ensure_column($table, 'approved_at_gmt',      "datetime DEFAULT NULL");
        aieo_dmm_ensure_column($table, 'approved_by_user_id',  "bigint unsigned NOT NULL DEFAULT 0");

        
        
        
        
        
        
        
        
        
        aieo_dmm_ensure_column($table, 'return_pickup',        "longtext DEFAULT NULL");

        
        
        
        
        
        
        aieo_dmm_ensure_column($table, 'return_label_path',    "varchar(255) DEFAULT NULL");

        
        
        
        
        
        
        
        
        
        
        
        aieo_dmm_ensure_column($table, 'rejected_at_gmt',      "datetime DEFAULT NULL");
        aieo_dmm_ensure_column($table, 'rejected_by_user_id',  "bigint unsigned NOT NULL DEFAULT 0");

        
        
        
        
        
        
        aieo_dmm_ensure_column($table, 'rejection_reasons',    "varchar(255) DEFAULT NULL");

        
        
        aieo_dmm_ensure_column($table, 'rejection_note',       "varchar(500) DEFAULT NULL");
    }
}

function aieo_dmm_create_order_cancellations_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_order_cancellations';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `order_id` bigint unsigned NOT NULL,
        `user_id` bigint unsigned NOT NULL DEFAULT 0,
        `reason_slug` varchar(64) NOT NULL DEFAULT 'other',
        `reason_note` varchar(500) DEFAULT NULL,
        `status_before` varchar(32) NOT NULL DEFAULT '',
        `status_after` varchar(32) NOT NULL DEFAULT 'cancelled',
        `order_total` decimal(12,2) NOT NULL DEFAULT '0.00',
        `product_ids` longtext DEFAULT NULL,
        `is_full_cancel` tinyint NOT NULL DEFAULT 1,
        `entry_channel` varchar(40) DEFAULT NULL,
        `entry_campaign_id` bigint unsigned DEFAULT NULL,
        `entry_branch_id` bigint unsigned DEFAULT NULL,
        `event_kind` tinyint NOT NULL DEFAULT 0,
        `instigator` varchar(10) NOT NULL DEFAULT '',
        `frame` varchar(20) NOT NULL DEFAULT '',
        `outbound_shipping_cost` decimal(12,2) DEFAULT NULL,
        `return_shipping_cost` decimal(12,2) DEFAULT NULL,
        `cancelled_by_user_id` bigint unsigned NOT NULL DEFAULT 0,
        `cancelled_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_oc_order` (`order_id`),
        KEY `aieo_dmm_idx_oc_reason_time` (`reason_slug`, `cancelled_at`),
        KEY `aieo_dmm_idx_oc_user` (`user_id`, `cancelled_at`),
        KEY `aieo_dmm_idx_oc_campaign` (`entry_campaign_id`, `cancelled_at`),
        KEY `aieo_dmm_idx_oc_channel` (`entry_channel`, `cancelled_at`),
        KEY `aieo_dmm_idx_oc_branch` (`entry_branch_id`, `cancelled_at`),
        KEY `aieo_dmm_idx_oc_event` (`event_kind`, `cancelled_at`),
        KEY `aieo_dmm_idx_oc_instigator` (`instigator`, `cancelled_at`),
        KEY `aieo_dmm_idx_oc_frame` (`frame`, `cancelled_at`)
    ) {$charset_collate};";
    $wpdb->query($sql);

    
    
    
    
    aieo_dmm_ensure_column($table, 'reason_note',            'varchar(500) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'product_ids',            'longtext DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'is_full_cancel',         'tinyint NOT NULL DEFAULT 1');
    aieo_dmm_ensure_column($table, 'entry_campaign_id',      'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'entry_branch_id',        'bigint unsigned DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'event_kind',             'tinyint NOT NULL DEFAULT 0');

    
    
    
    
    
    
    
    aieo_dmm_ensure_column($table, 'instigator',             "varchar(10) NOT NULL DEFAULT ''");
    aieo_dmm_ensure_column($table, 'frame',                  "varchar(20) NOT NULL DEFAULT ''");
    aieo_dmm_ensure_index($table, 'aieo_dmm_idx_oc_instigator', '(`instigator`, `cancelled_at`)');
    aieo_dmm_ensure_index($table, 'aieo_dmm_idx_oc_frame',      '(`frame`, `cancelled_at`)');
    aieo_dmm_ensure_column($table, 'outbound_shipping_cost', 'decimal(12,2) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'return_shipping_cost',   'decimal(12,2) DEFAULT NULL');
    aieo_dmm_ensure_column($table, 'cancelled_by_user_id',   'bigint unsigned NOT NULL DEFAULT 0');
    aieo_create_index_if_not_exists('aieo_dmm_idx_oc_event', $table, '(event_kind, cancelled_at)');
}

function aieo_dmm_create_order_line_refunds_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_order_line_refunds';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `order_id` bigint unsigned NOT NULL,
        `refund_id` bigint unsigned DEFAULT NULL,
        `user_id` bigint unsigned NOT NULL DEFAULT 0,
        `product_id` bigint unsigned NOT NULL,
        `variation_id` bigint unsigned NOT NULL DEFAULT 0,
        `qty_refunded` int NOT NULL DEFAULT 0,
        `amount_refunded` decimal(12,2) NOT NULL DEFAULT '0.00',
        `reason_slug` varchar(64) DEFAULT NULL,
        `reason_note` varchar(500) DEFAULT NULL,
        `refunded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_olr_refund_product` (`refund_id`, `product_id`),
        KEY `aieo_dmm_idx_olr_product` (`product_id`, `refunded_at`),
        KEY `aieo_dmm_idx_olr_order` (`order_id`),
        KEY `aieo_dmm_idx_olr_reason` (`reason_slug`, `refunded_at`)
    ) {$charset_collate};";
    $wpdb->query($sql);
}

function aieo_dmm_create_product_behavior_metrics_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_product_behavior_metrics';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `product_id` bigint unsigned NOT NULL,
        `total_orders` int NOT NULL DEFAULT 0,
        `total_cancellations` int NOT NULL DEFAULT 0,
        `total_refund_lines` int NOT NULL DEFAULT 0,
        `cancellation_rate` decimal(6,4) NOT NULL DEFAULT 0,
        `refund_rate` decimal(6,4) NOT NULL DEFAULT 0,
        `last_cancelled_at` datetime DEFAULT NULL,
        `top_reason_slug` varchar(64) DEFAULT NULL,
        `computed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`product_id`),
        KEY `aieo_dmm_idx_pbm_rate` (`cancellation_rate`),
        KEY `aieo_dmm_idx_pbm_recent` (`last_cancelled_at`)
    ) {$charset_collate};";
    $wpdb->query($sql);
}

function aieo_dmm_backfill_order_cancellations($since_days = 0) {
    global $wpdb;
    if (!function_exists('wc_get_order')) { return 0; }

    $hpos = $wpdb->prefix . 'wc_orders';
    
    $hpos_present = AIEO_Order_Store::uses_hpos() && ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $hpos)) === $hpos);

    $cancel_statuses = array('wc-cancelled', 'wc-refunded', 'wc-failed');
    $placeholders    = implode(',', array_fill(0, count($cancel_statuses), '%s'));

    if ($hpos_present) {
        $sql_args = $cancel_statuses;
        $time_clause = '';
        if ($since_days > 0) {
            $time_clause = " AND date_created_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)";
            $sql_args[] = (int) $since_days;
        }
        $order_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT id FROM {$hpos}
              WHERE status IN ({$placeholders}){$time_clause}
              ORDER BY id ASC",
            ...$sql_args
        ));
    } else {
        $sql_args = $cancel_statuses;
        $time_clause = '';
        if ($since_days > 0) {
            $time_clause = " AND post_date_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)";
            $sql_args[] = (int) $since_days;
        }
        $order_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT ID FROM {$wpdb->posts}
              WHERE post_type = 'shop_order'
                AND post_status IN ({$placeholders}){$time_clause}
              ORDER BY ID ASC",
            ...$sql_args
        ));
    }

    $count = 0;
    foreach ((array) $order_ids as $order_id) {
        $order = wc_get_order((int) $order_id);
        if (!$order) { continue; }
        if (class_exists('AIEO_DMM_Order_Cancellations')) {
            AIEO_DMM_Order_Cancellations::on_order_status_changed(
                (int) $order_id,
                'unknown',
                substr((string) $order->get_status('edit'), 0, 32),
                $order
            );
            $count++;
        }
    }
    return $count;
}

function aieo_dmm_backfill_order_cancellations_chunked($cursor = 0, $limit = 3000, $since_days = 0) {
    global $wpdb;
    $zero = array('processed' => 0, 'has_more' => false, 'next_cursor' => (int) $cursor, 'total' => 0, 'chunk_size' => 0);
    if (!function_exists('wc_get_order')) { return $zero; }

    $hpos = $wpdb->prefix . 'wc_orders';
    
    $hpos_present = AIEO_Order_Store::uses_hpos() && ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $hpos)) === $hpos);

    $cancel_statuses = array('wc-cancelled', 'wc-refunded', 'wc-failed');
    $placeholders    = implode(',', array_fill(0, count($cancel_statuses), '%s'));

    $cursor = max(0, (int) $cursor);
    $limit  = max(1, min(5000, (int) $limit));

    if ($hpos_present) {
        $count_args = $cancel_statuses;
        $chunk_args = $cancel_statuses;
        $time_clause = '';
        if ($since_days > 0) {
            $time_clause   = " AND date_created_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)";
            $count_args[]  = (int) $since_days;
            $chunk_args[]  = (int) $since_days;
        }
        $total = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$hpos} WHERE status IN ({$placeholders}){$time_clause}",
            ...$count_args
        ));
        $chunk_args[] = $cursor;
        $chunk_args[] = $limit;
        $order_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT id FROM {$hpos}
              WHERE status IN ({$placeholders}){$time_clause}
                AND id > %d
              ORDER BY id ASC
              LIMIT %d",
            ...$chunk_args
        ));
    } else {
        $count_args = $cancel_statuses;
        $chunk_args = $cancel_statuses;
        $time_clause = '';
        if ($since_days > 0) {
            $time_clause   = " AND post_date_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)";
            $count_args[]  = (int) $since_days;
            $chunk_args[]  = (int) $since_days;
        }
        $total = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts}
              WHERE post_type = 'shop_order'
                AND post_status IN ({$placeholders}){$time_clause}",
            ...$count_args
        ));
        $chunk_args[] = $cursor;
        $chunk_args[] = $limit;
        $order_ids = $wpdb->get_col($wpdb->prepare(
            "SELECT ID FROM {$wpdb->posts}
              WHERE post_type = 'shop_order'
                AND post_status IN ({$placeholders}){$time_clause}
                AND ID > %d
              ORDER BY ID ASC
              LIMIT %d",
            ...$chunk_args
        ));
    }

    $count   = 0;
    $last_id = $cursor;
    foreach ((array) $order_ids as $order_id) {
        $oid = (int) $order_id;
        if ($oid > $last_id) { $last_id = $oid; }
        $order = wc_get_order($oid);
        if (!$order) { continue; }
        if (class_exists('AIEO_DMM_Order_Cancellations')) {
            AIEO_DMM_Order_Cancellations::on_order_status_changed(
                $oid,
                'unknown',
                substr((string) $order->get_status('edit'), 0, 32),
                $order
            );
            $count++;
        }
    }

    $fetched = is_array($order_ids) ? count($order_ids) : 0;
    return array(
        'processed'   => $count,
        'has_more'    => $fetched >= $limit,
        'next_cursor' => $last_id,
        'total'       => $total,
        'chunk_size'  => $fetched,
    );
}

function aieo_dmm_create_campaigns_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_campaigns';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `id` bigint unsigned NOT NULL AUTO_INCREMENT,
        `slug` varchar(120) NOT NULL,
        `label` varchar(190) NOT NULL,
        `channel_hint` varchar(40) DEFAULT NULL,
        `objective` varchar(40) DEFAULT NULL,
        `status` varchar(20) NOT NULL DEFAULT 'auto_draft',
        `start_at` datetime DEFAULT NULL,
        `end_at` datetime DEFAULT NULL,
        `budget_planned` decimal(12,2) DEFAULT NULL,
        `budget_spent` decimal(12,2) DEFAULT NULL,
        `notes` longtext DEFAULT NULL,
        `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `aieo_dmm_uq_camp_slug` (`slug`),
        KEY `aieo_dmm_idx_camp_status` (`status`, `start_at`, `end_at`)
    ) {$charset_collate};";
    $wpdb->query($sql);

    
    
    
    
    $sessions = $wpdb->prefix . 'aieo_sessions_statics';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $sessions)) === $sessions) {
        if (!$wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'entry_campaign_id'")) {
            
            
            
            
            
            
            $has_predecessor = (bool) $wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'entry_campaign'");
            $after_clause    = $has_predecessor ? ' AFTER `entry_campaign`' : '';
            $wpdb->query("ALTER TABLE {$sessions} ADD COLUMN `entry_campaign_id` bigint unsigned DEFAULT NULL{$after_clause}");
        }
        
        
        
        
        if ($wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'entry_campaign_id'")) {
            aieo_create_index_if_not_exists('aieo_idx_ss_camp_id', $sessions, '(entry_campaign_id)');
        }

        
        
        
        if (!$wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'order_id'")) {
            $wpdb->query("ALTER TABLE {$sessions} ADD COLUMN `order_id` bigint(20) unsigned DEFAULT NULL COMMENT 'WC order id if session led to a purchase' AFTER `session_duration_seconds`");
        }
        aieo_create_index_if_not_exists('aieo_idx_ss_order_id', $sessions, '(order_id)');

        
        
        if (!$wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'order_total'")) {
            $wpdb->query("ALTER TABLE {$sessions} ADD COLUMN `order_total` decimal(12,2) DEFAULT NULL COMMENT 'WC order grand total snapshot' AFTER `order_id`");
        }
    }

    
    $offer_attr = $wpdb->prefix . 'aieo_dmm_offer_attributions';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $offer_attr)) === $offer_attr) {
        aieo_dmm_ensure_column($offer_attr, 'entry_campaign_id', 'bigint unsigned DEFAULT NULL');
        aieo_create_index_if_not_exists('aieo_dmm_idx_oa_camp_id', $offer_attr, '(entry_campaign_id)');
    }
}

function aieo_dmm_classify_event_kind() {
    global $wpdb;
    $tbl = $wpdb->prefix . 'aieo_dmm_order_cancellations';
    $cr  = $wpdb->prefix . 'aieo_dmm_cancellation_reasons';

    
    $by_reason = (int) $wpdb->query(
        "UPDATE {$tbl} oc
           JOIN {$cr}  cr ON cr.slug = oc.reason_slug
            SET oc.event_kind = cr.default_event_kind
          WHERE cr.default_event_kind IS NOT NULL
            AND oc.event_kind = 0
            AND cr.default_event_kind <> 0"
    );

    
    
    $by_status = (int) $wpdb->query(
        "UPDATE {$tbl} oc
      LEFT JOIN {$cr}  cr ON cr.slug = oc.reason_slug
            SET oc.event_kind = 1
          WHERE oc.status_after = 'refunded'
            AND oc.event_kind = 0
            AND (cr.default_event_kind IS NULL OR cr.default_event_kind = 0)"
    );

    
    $cancel_zeroed = (int) $wpdb->query(
        "UPDATE {$tbl}
            SET outbound_shipping_cost = NULL,
                return_shipping_cost   = NULL
          WHERE event_kind = 0
            AND (outbound_shipping_cost IS NOT NULL OR return_shipping_cost IS NOT NULL)"
    );

    
    $refund_only_zeroed = (int) $wpdb->query(
        "UPDATE {$tbl}
            SET return_shipping_cost = NULL
          WHERE event_kind = 2
            AND return_shipping_cost IS NOT NULL"
    );

    
    $counts = $wpdb->get_results(
        "SELECT event_kind, COUNT(*) AS n FROM {$tbl} GROUP BY event_kind",
        ARRAY_A
    );
    $by_kind = array(0 => 0, 1 => 0, 2 => 0);
    foreach ((array) $counts as $r) {
        $by_kind[(int) $r['event_kind']] = (int) $r['n'];
    }

    return array(
        'updated_by_reason'  => $by_reason,
        'updated_by_status'  => $by_status,
        'cancel_costs_cleared' => $cancel_zeroed,
        'refund_only_return_cleared' => $refund_only_zeroed,
        'counts_by_kind'     => $by_kind,
    );
}

function aieo_dmm_backfill_outbound_shipping_costs() {
    global $wpdb;
    $oc = $wpdb->prefix . 'aieo_dmm_order_cancellations';
    $touched = 0;

    
    
    
    $od = $wpdb->prefix . 'wc_order_operational_data';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $od)) === $od) {
        $col = $wpdb->get_var("SHOW COLUMNS FROM {$od} LIKE 'shipping_total_amount'");
        $col_name = $col ? 'shipping_total_amount' : 'shipping_total';
        $touched += (int) $wpdb->query(
            "UPDATE {$oc} oc
               JOIN {$od} od ON od.order_id = oc.order_id
                SET oc.outbound_shipping_cost = od.{$col_name}
              WHERE oc.outbound_shipping_cost IS NULL
                AND od.{$col_name} IS NOT NULL"
        );
    }

    
    
    $pm = $wpdb->postmeta;
    $touched += (int) $wpdb->query(
        "UPDATE {$oc} oc
           JOIN {$pm} pm ON pm.post_id = oc.order_id AND pm.meta_key = '_order_shipping'
            SET oc.outbound_shipping_cost = CAST(pm.meta_value AS DECIMAL(12,2))
          WHERE oc.outbound_shipping_cost IS NULL
            AND pm.meta_value IS NOT NULL
            AND pm.meta_value <> ''"
    );

    return $touched;
}

function aieo_dmm_backfill_campaigns() {
    global $wpdb;
    if (!class_exists('AIEO_DMM_Campaigns')) { return array('campaigns' => 0, 'sessions' => 0); }

    $sessions = $wpdb->prefix . 'aieo_sessions_statics';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $sessions)) !== $sessions) {
        return array('campaigns' => 0, 'sessions' => 0);
    }

    
    $rows = $wpdb->get_results(
        "SELECT entry_campaign AS slug,
                MAX(entry_channel) AS channel_hint,
                COUNT(*) AS n
           FROM {$sessions}
          WHERE entry_campaign IS NOT NULL
            AND entry_campaign <> ''
          GROUP BY entry_campaign
          ORDER BY n DESC",
        ARRAY_A
    );
    if (!is_array($rows) || empty($rows)) {
        return array('campaigns' => 0, 'sessions' => 0);
    }

    $created = 0;
    foreach ($rows as $r) {
        $slug = (string) $r['slug'];
        $hint = (string) $r['channel_hint'];
        $id = AIEO_DMM_Campaigns::touch($slug, $hint);
        if ($id) { $created++; }
    }

    
    
    $camps = $wpdb->prefix . 'aieo_dmm_campaigns';
    $touched = (int) $wpdb->query(
        "UPDATE {$sessions} s
            JOIN {$camps} c ON c.slug = s.entry_campaign
            SET s.entry_campaign_id = c.id
          WHERE s.entry_campaign_id IS NULL
            AND s.entry_campaign IS NOT NULL
            AND s.entry_campaign <> ''"
    );

    
    $offer_attr = $wpdb->prefix . 'aieo_dmm_offer_attributions';
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $offer_attr)) === $offer_attr) {
        $col = $wpdb->get_var("SHOW COLUMNS FROM {$offer_attr} LIKE 'entry_campaign_id'");
        if ($col) {
            
            
            $oa_rows = $wpdb->get_results(
                "SELECT id, order_id FROM {$offer_attr} WHERE entry_campaign_id IS NULL LIMIT 5000",
                ARRAY_A
            );
            if (is_array($oa_rows)) {
                foreach ($oa_rows as $oa) {
                    $order = function_exists('wc_get_order') ? wc_get_order((int) $oa['order_id']) : null;
                    if (!$order) { continue; }
                    $slug = (string) $order->get_meta('_aieo_entry_campaign', true);
                    if ($slug === '') { continue; }
                    $cid = AIEO_DMM_Campaigns::id_for_slug($slug);
                    if ($cid) {
                        $wpdb->update($offer_attr, array('entry_campaign_id' => (int) $cid), array('id' => (int) $oa['id']), array('%d'), array('%d'));
                    }
                }
            }
        }
    }

    return array('campaigns' => $created, 'sessions' => $touched);
}

function aieo_dmm_create_user_behavior_metrics_table() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_user_behavior_metrics';
    $charset_collate = aieo_get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS {$table} (
        `user_id` bigint unsigned NOT NULL,
        `total_orders` int NOT NULL DEFAULT 0,
        `total_cancellations` int NOT NULL DEFAULT 0,
        `cancellation_rate` decimal(6,4) NOT NULL DEFAULT 0,
        `last_cancelled_at` datetime DEFAULT NULL,
        `top_reason_slug` varchar(64) DEFAULT NULL,
        `risk_band` tinyint NOT NULL DEFAULT 0,
        `computed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`user_id`),
        KEY `aieo_dmm_idx_ubm_rate` (`cancellation_rate`),
        KEY `aieo_dmm_idx_ubm_risk` (`risk_band`)
    ) {$charset_collate};";
    $wpdb->query($sql);
}