<?php

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

function aieo_dmm_create_tables() {
    global $wpdb;

    $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_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_store_pipeline_tables();
        
        
        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_create_cancellation_reasons_table();
        aieo_dmm_seed_default_cancellation_reasons();
        aieo_dmm_create_order_cancellations_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();
            }
        }

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

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

        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');
}

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');
}

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,
        -- 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`)');
}

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,
        `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_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']  : '[]',
        '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,
        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'");

    
    
    $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_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,
        `soft1_id` int 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, 'soft1_id', 'int DEFAULT NULL');
    aieo_create_index_if_not_exists('aieo_dmm_idx_br_soft1', $table, '(soft1_id)');
}

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_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_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_table_exists')) {

function aieo_dmm_table_exists($table_name) {
    global $wpdb;

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

    return ((int) $found) > 0;
}

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,
        `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');
}

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');
}

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

    
    
    
    $branch_cols = '';
    foreach (array('total','piraeus','keratsini','korydallos','kallithea','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`)
    ) {$cc};");

    
    $stat_cols = '';
    foreach (array('total','piraeus','keratsini','korydallos','kallithea','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 = '';
    foreach (array('piraeus','keratsini','korydallos','kallithea') as $b) {
        $perbranch .= "`existing_stock_{$b}` int DEFAULT NULL,\n        `desired_qty_{$b}` int DEFAULT NULL,\n        `last_pending_id_{$b}` bigint DEFAULT NULL,\n        ";
    }
    $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_physical_recovery_tables() {
    global $wpdb;
    $cc = aieo_get_charset_collate();
    $p  = $wpdb->prefix;

    
    
    
    $pb = '';
    $pbr = '';
    foreach (array('piraeus', 'keratsini', 'korydallos', 'kallithea') as $b) {
        $pb  .= "`phys_{$b}` decimal(12,3) DEFAULT 0,\n        ";
        $pbr .= "`phys_{$b}_recent` decimal(12,3) DEFAULT 0,\n        ";
    }

    
    
    
    $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,
        `phys_piraeus_recent` decimal(12,3) DEFAULT 0,
        `phys_keratsini_recent` decimal(12,3) DEFAULT 0,
        `phys_korydallos_recent` decimal(12,3) DEFAULT 0,
        `phys_kallithea_recent` decimal(12,3) 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');
    
    
    foreach (array('piraeus', 'keratsini', 'korydallos', 'kallithea') as $b) {
        aieo_dmm_ensure_column($ev, 'phys_' . $b . '_recent', 'decimal(12,3) DEFAULT 0');
    }
    
    foreach (array('piraeus', 'keratsini', 'korydallos', 'kallithea') as $b) {
        aieo_dmm_ensure_column($cand, 'phys_' . $b . '_recent', 'decimal(12,3) DEFAULT 0');
        aieo_dmm_ensure_column($gap,  'phys_' . $b,             'decimal(12,3) 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) {
    global $wpdb;
    $slug = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $slug));
    if ($slug === '') { return; }
    $p     = $wpdb->prefix;
    $board = $p . 'store_board_' . $slug;
    if (!aieo_dmm_table_exists($board)) { return; }
    $bid   = (int) $branch_id;
    $repl  = array('__BOARD__' => $board, '__P__' => $p, '__BID__' => (string) $bid);

    foreach (array('logger_before', 'logger_after', 'preorder_upsert') as $which) {
        $wpdb->query("DROP TRIGGER IF EXISTS `trg_{$slug}_{$which}`");
    }

    $before = <<<'SQL'
CREATE TRIGGER `trg___SLUG___logger_before` BEFORE UPDATE ON `__BOARD__` FOR EACH ROW BEGIN
  DECLARE v_changed TINYINT DEFAULT 0;
  DECLARE v_line VARCHAR(1000);
  DECLARE v_now  VARCHAR(19);
  DECLARE v_seq  INT UNSIGNED;
  DECLARE v_user VARCHAR(191);
  DECLARE v_old_status_id   TINYINT UNSIGNED;
  DECLARE v_new_status_id   TINYINT UNSIGNED;
  DECLARE v_old_status_name VARCHAR(64);
  DECLARE v_new_status_name VARCHAR(64);
  DECLARE v_lookup_id   TINYINT UNSIGNED;
  DECLARE v_lookup_name VARCHAR(64);
  IF COALESCE(@BULK_LOAD,0) = 0 THEN
    IF NOT (NEW.product_id <=> OLD.product_id) THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'product_id is immutable';
    END IF;
    SET NEW.logger := OLD.logger;
    SET NEW.logger_seq := OLD.logger_seq;
    SET v_old_status_id := OLD.status_id;
    SET v_old_status_name := OLD.status_name;
    IF NEW.status_name IS NOT NULL AND (OLD.status_name IS NULL OR NEW.status_name <> OLD.status_name OR NEW.status_id IS NULL) THEN
      SELECT l.status_id INTO v_lookup_id FROM __P__store_order_status_lookup l WHERE l.status_name = NEW.status_name COLLATE utf8mb4_general_ci LIMIT 1;
      IF v_lookup_id IS NOT NULL THEN SET NEW.status_id := v_lookup_id; END IF;
    END IF;
    IF NEW.status_id IS NOT NULL AND (OLD.status_id IS NULL OR NEW.status_id <> OLD.status_id) THEN
      SELECT l.status_name INTO v_lookup_name FROM __P__store_order_status_lookup l WHERE l.status_id = NEW.status_id LIMIT 1;
      IF v_lookup_name IS NOT NULL THEN SET NEW.status_name := v_lookup_name; END IF;
    END IF;
    IF NEW.status IS NOT NULL AND (OLD.status IS NULL OR NEW.status <> OLD.status) THEN
      SET NEW.status_id := NEW.status;
      SELECT l.status_name INTO v_lookup_name FROM __P__store_order_status_lookup l WHERE l.status_id = NEW.status LIMIT 1;
      IF v_lookup_name IS NOT NULL THEN SET NEW.status_name := v_lookup_name; END IF;
    END IF;
    IF NEW.status_id IS NOT NULL THEN SET NEW.status := NEW.status_id; END IF;
    SET v_new_status_id := NEW.status_id;
    SET v_new_status_name := NEW.status_name;
    IF ( (NEW.existing_stock <=> OLD.existing_stock) = 0 OR (NEW.desired_qty <=> OLD.desired_qty) = 0 OR (v_new_status_id <=> v_old_status_id) = 0 ) THEN
      SET v_changed := 1;
    END IF;
    IF v_changed = 1 THEN
      IF NEW.origin_user IS NOT NULL AND CHAR_LENGTH(TRIM(NEW.origin_user)) > 0 THEN SET v_user := NEW.origin_user; ELSE SET v_user := NULL; END IF;
      SET v_now := DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
      SET v_seq := OLD.logger_seq + 1;
      SET v_line := CONCAT(v_now, ' [#', LPAD(v_seq,4,'0'), '] ', IFNULL(CONCAT('u:', v_user),'u:0'), ' (', COALESCE(NEW.origin_type,'table_entry'), ') ');
      IF (NEW.existing_stock <=> OLD.existing_stock) = 0 THEN SET v_line := CONCAT(v_line, 'stock ', IFNULL(OLD.existing_stock,'-'), '->', IFNULL(NEW.existing_stock,'-'), '; '); END IF;
      IF (NEW.desired_qty <=> OLD.desired_qty) = 0 THEN SET v_line := CONCAT(v_line, 'qty ', IFNULL(OLD.desired_qty,'-'), '->', IFNULL(NEW.desired_qty,'-'), '; '); END IF;
      IF (v_new_status_id <=> v_old_status_id) = 0 THEN SET v_line := CONCAT(v_line, 'status ', COALESCE(v_old_status_id,-1), '->', COALESCE(v_new_status_id,-1), '; '); END IF;
      IF NEW.sales_trend IS NOT NULL THEN SET v_line := CONCAT(v_line, 'trend ', NEW.sales_trend, ';'); END IF;
      SET NEW.logger := TRIM(CONCAT(v_line, '\n', COALESCE(OLD.logger, '')));
      IF (LENGTH(NEW.logger) - LENGTH(REPLACE(NEW.logger, '\n', ''))) >= 10 THEN SET NEW.logger := SUBSTRING_INDEX(NEW.logger, '\n', 10); END IF;
      SET NEW.logger_seq := v_seq;
      SET NEW.logger_last_user := v_user;
    END IF;
  END IF;
END
SQL;

    $upsert = <<<'SQL'
CREATE TRIGGER `trg___SLUG___preorder_upsert` BEFORE UPDATE ON `__BOARD__` FOR EACH ROW BEGIN
  DECLARE v_qty_changed TINYINT DEFAULT 0;
  DECLARE v_supplier BIGINT;
  DECLARE v_brand BIGINT;
  IF COALESCE(@BULK_LOAD,0) = 0 THEN
    SET NEW.origin_type = COALESCE(@ORIGIN_TYPE, 'table_entry');
    SET NEW.origin_user = COALESCE(@ORIGIN_USER, 'anonymous');
    SET v_qty_changed = NOT (NEW.desired_qty <=> OLD.desired_qty);
    IF v_qty_changed = 1 AND COALESCE(NEW.desired_qty,0) > 0 THEN
      SET v_brand := (SELECT v.brand_id FROM __P__aieo_core_product_vitals v WHERE v.product_id = NEW.product_id LIMIT 1);
      SET v_supplier := COALESCE(
        (SELECT m.supplier_term_id FROM __P__aieo_dmm_supplier_sku_map m WHERE m.sku = NEW.sku LIMIT 1),
        (SELECT be.supplier_term_id FROM __P__aieo_dmm_brand_extras be WHERE be.term_id = v_brand LIMIT 1)
      );
      INSERT INTO __P__aieo_dmm_supplier_preorders
        (supplier_term_id, brand_term_id, product_id, branch_id, scanned_user_id,
         requested_qty, prior_existing, fulfilled, status, notes, created_at)
      VALUES (
        v_supplier, v_brand, NEW.product_id, __BID__,
        COALESCE((SELECT u.ID FROM __P__users u WHERE u.user_login = NEW.origin_user LIMIT 1), 0),
        NEW.desired_qty, OLD.existing_stock, 0, 'requested',
        CONCAT('board ', '__SLUG__', ' via ', COALESCE(NEW.origin_type,'table_entry')), NOW()
      );
    END IF;
    IF v_qty_changed = 1 OR NOT (NEW.existing_stock <=> OLD.existing_stock) THEN
      SET NEW.modified = NOW();
    END IF;
  END IF;
END
SQL;

    $after = <<<'SQL'
CREATE TRIGGER `trg___SLUG___logger_after` AFTER UPDATE ON `__BOARD__` FOR EACH ROW BEGIN
  DECLARE v_changed TINYINT DEFAULT 0;
  DECLARE v_user_id BIGINT DEFAULT 0;
  DECLARE v_user VARCHAR(191);
  IF COALESCE(@BULK_LOAD,0) = 0 THEN
    IF ( (NEW.existing_stock <=> OLD.existing_stock) = 0 OR (NEW.desired_qty <=> OLD.desired_qty) = 0 OR (NEW.status_id <=> OLD.status_id) = 0 ) THEN
      SET v_changed := 1;
    END IF;
    IF v_changed = 1 THEN
      IF NEW.origin_user IS NOT NULL AND CHAR_LENGTH(TRIM(NEW.origin_user)) > 0 THEN SET v_user := NEW.origin_user; ELSE SET v_user := NULL; END IF;
      IF v_user IS NOT NULL THEN SELECT ID INTO v_user_id FROM __P__users WHERE user_login = v_user LIMIT 1; SET v_user_id := COALESCE(v_user_id,0); END IF;
      INSERT INTO __P__aieo_dmm_store_order_log
        (name, created, modified, product_id, branch_id, board_slug, user_id,
         existing_stock, desired_qty, status_id, origin_type, origin_user, origin_device, sales_trend)
      VALUES
        (CONCAT('p', NEW.product_id, '-', '__SLUG__', '-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')),
         NOW(), NOW(), NEW.product_id, __BID__, '__SLUG__', v_user_id,
         NEW.existing_stock, NEW.desired_qty, NEW.status_id, NEW.origin_type, v_user, NULL, NEW.sales_trend);
    END IF;
  END IF;
END
SQL;

    $repl['__SLUG__'] = $slug;
    foreach (array($before, $upsert, $after) as $sql) {
        $wpdb->query(strtr($sql, $repl));
    }
}

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,
        `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');
}

function aieo_dmm_seed_default_cancellation_reasons() {
    global $wpdb;
    $table = $wpdb->prefix . 'aieo_dmm_cancellation_reasons';
    $defaults = array(
        
        
        array('out_of_stock',              'Out of stock',                                  1,  10, 0),
        array('customer_changed_mind',     'Customer changed mind (before shipping)',       1,  20, 0),
        array('payment_failed',            'Payment failed',                                1,  30, 0),
        array('fraud_suspected',           'Suspected fraud',                               0,  50, 0),
        array('duplicate_order',           'Duplicate order',                               1,  60, 0),
        
        array('quality_issue',             'Quality issue (returned after receipt)',        1,  70, 1),
        array('wrong_size',                'Wrong size / fit (returned after receipt)',     1,  80, 1),
        array('late_delivery_returned',    'Late delivery (returned after receipt)',        1,  90, 1),
        array('customer_returned',         'Customer returned the items (after receipt)',   1,  95, 1),
        
        array('customer_kept_refunded',    'Refunded but customer kept items',              0, 110, 2),
        array('damaged_in_transit_kept',   'Damaged in transit (customer kept + refunded)', 0, 115, 2),
        
        array('refused_on_arrival',        'Refused on arrival (never opened)',             0, 130, 3),
        array('never_collected_from_pickup', 'Never collected from pickup point',           0, 140, 3),
        array('address_unreachable',       'Address unreachable (never delivered)',         1,  40, 3),
        
        array('lost_in_transit',           'Lost in transit',                               0, 120, null),
        array('other',                     'Other (see notes)',                             1, 100, null),
    );
    foreach ($defaults as $row) {
        list($slug, $label, $visible, $sort, $event_kind) = $row;
        
        if ($event_kind === null) {
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO {$table}
                    (slug, label, is_customer_visible, sort_order, enabled, default_event_kind)
                 VALUES (%s, %s, %d, %d, 1, NULL)",
                $slug, $label, (int) $visible, (int) $sort
            ));
        } else {
            $wpdb->query($wpdb->prepare(
                "INSERT IGNORE INTO {$table}
                    (slug, label, is_customer_visible, sort_order, enabled, default_event_kind)
                 VALUES (%s, %s, %d, %d, 1, %d)",
                $slug, $label, (int) $visible, (int) $sort, (int) $event_kind
            ));
        }
    }

    
    
    
    
    
    $kind_defaults = array(
        'out_of_stock'              => 0,
        'customer_changed_mind'     => 0,
        'payment_failed'            => 0,
        'fraud_suspected'           => 0,
        'duplicate_order'           => 0,
        'quality_issue'             => 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,
    );
    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_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,
        `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`)
    ) {$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, '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 = ($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(NOW(), 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(NOW(), 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 = ($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(NOW(), 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(NOW(), 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);
}
