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_revisions_table(); aieo_dmm_create_offer_targets_table(); aieo_dmm_create_offer_facts_table(); aieo_dmm_create_offer_redemptions_table(); aieo_dmm_create_offer_attributions_table(); aieo_dmm_create_loyalty_classes_table(); aieo_dmm_create_loyalty_assignments_table(); aieo_dmm_create_no_free_shipping_table(); aieo_dmm_create_branches_table(); aieo_dmm_create_branch_scan_log_table(); aieo_dmm_create_product_needs_table(); aieo_dmm_create_discount_schedules_table(); aieo_dmm_create_export_jobs_table(); aieo_dmm_create_feed_configs_table(); aieo_dmm_create_feed_terms_table(); aieo_dmm_create_feed_cat_map_table(); aieo_dmm_create_feed_rows_table(); aieo_dmm_create_feed_row_terms_table(); aieo_dmm_create_feed_image_queue_table(); aieo_dmm_create_feed_run_log_table(); aieo_dmm_create_cat_match_register_table(); aieo_dmm_create_stock_word_register_table(); aieo_dmm_create_product_fp_table(); aieo_dmm_create_user_fp_table(); aieo_dmm_create_complementary_table(); aieo_dmm_create_swatches_cache_table(); aieo_dmm_create_swatches_variation_map_table(); aieo_dmm_create_color_attributes_table(); aieo_dmm_create_category_extras_table(); aieo_dmm_create_brand_extras_table(); aieo_dmm_create_term_relations_table(); aieo_dmm_migrate_category_extras_to_term_relations(); aieo_dmm_create_supplier_extras_table(); aieo_dmm_migrate_brand_rows_from_category_extras(); aieo_dmm_create_need_extras_table(); aieo_dmm_create_supplier_preorders_table(); aieo_dmm_create_preorder_worksheet_table(); aieo_dmm_create_preorder_log_table(); aieo_dmm_create_store_pipeline_tables(); aieo_dmm_create_branch_stats_tables(); aieo_dmm_backfill_legacy_branch_matchers(); aieo_dmm_create_store_order_log_table(); aieo_dmm_ensure_store_status_lookup(); aieo_dmm_ensure_all_store_boards(); aieo_dmm_create_physical_recovery_tables(); aieo_dmm_migrate_branch_wide_to_narrow(); aieo_dmm_create_cancellation_reasons_table(); aieo_dmm_seed_default_cancellation_reasons(); aieo_dmm_create_order_cancellations_table(); aieo_dmm_create_withdrawal_declarations_table(); aieo_dmm_create_order_line_refunds_table(); aieo_dmm_create_product_behavior_metrics_table(); aieo_dmm_create_user_behavior_metrics_table(); aieo_dmm_create_campaigns_table(); $google_reviews_schema = AIEO_DIR . 'includes/dmm/sql/google-reviews-schema.php'; if (file_exists($google_reviews_schema)) { require_once $google_reviews_schema; if (function_exists('aieo_google_reviews_install_schema')) { aieo_google_reviews_install_schema(); } } $gsc_history_schema = AIEO_DIR . 'includes/dmm/sql/gsc-history-schema.php'; if (file_exists($gsc_history_schema)) { require_once $gsc_history_schema; if (function_exists('aieo_gsc_history_install_schema')) { aieo_gsc_history_install_schema(); } } $exchanger_schema = AIEO_DIR . 'includes/dmm/sql/exchanger-schema.php'; if (file_exists($exchanger_schema)) { require_once $exchanger_schema; if (function_exists('aieo_dmm_exchanger_install_schema')) { aieo_dmm_exchanger_install_schema(); } } $fulfillment_schema = AIEO_DIR . 'includes/dmm/sql/fulfillment-schema.php'; if (file_exists($fulfillment_schema)) { require_once $fulfillment_schema; if (function_exists('aieo_dmm_fulfillment_install_schema')) { aieo_dmm_fulfillment_install_schema(); } } $shipping_rates_schema = AIEO_DIR . 'includes/dmm/sql/shipping-rates-schema.php'; if (file_exists($shipping_rates_schema)) { require_once $shipping_rates_schema; if (function_exists('aieo_dmm_shipping_rates_install_schema')) { aieo_dmm_shipping_rates_install_schema(); } } $carts_schema = AIEO_DIR . 'includes/carts/sql/carts-schema.php'; if (file_exists($carts_schema)) { require_once $carts_schema; if (function_exists('aieo_carts_create_tables')) { aieo_carts_create_tables(); } } $checkout_timing_schema = AIEO_DIR . 'includes/dmm/sql/checkout-timing-schema.php'; if (file_exists($checkout_timing_schema)) { require_once $checkout_timing_schema; if (function_exists('aieo_checkout_timing_create_table')) { aieo_checkout_timing_create_table(); } } $server_events_schema = AIEO_DIR . 'includes/server-events/sql/server-events-schema.php'; if (file_exists($server_events_schema)) { require_once $server_events_schema; if (function_exists('aieo_server_events_log_create_table')) { aieo_server_events_log_create_table(); } } $login_security_schema = AIEO_DIR . 'includes/dmm/sql/login-security-schema.php'; if (file_exists($login_security_schema)) { require_once $login_security_schema; if (function_exists('aieo_login_security_create_table')) { aieo_login_security_create_table(); } } $custom_tabs_schema = AIEO_DIR . 'includes/dmm/sql/custom-tabs-schema.php'; if (file_exists($custom_tabs_schema)) { require_once $custom_tabs_schema; if (function_exists('aieo_dmm_custom_tabs_create_table')) { aieo_dmm_custom_tabs_create_table(); } } $seo_schema = AIEO_DIR . 'includes/dmm/sql/seo-schema.php'; if (file_exists($seo_schema)) { require_once $seo_schema; if (function_exists('aieo_seo_install_schema')) { aieo_seo_install_schema(); } } $subjects_schema = AIEO_DIR . 'includes/dmm/sql/subjects-schema.php'; if (file_exists($subjects_schema)) { require_once $subjects_schema; if (function_exists('aieo_subjects_install_schema')) { aieo_subjects_install_schema(); } } aieo_dmm_migrate_legacy_data(); 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) { if (function_exists('aieo_debug_log')) { aieo_debug_log('AIEO_DMM schema init failed: ' . $e->getMessage()); } } if (function_exists('aieo_dmm_forget_known_tables')) { aieo_dmm_forget_known_tables(); } $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); if ( ! function_exists( 'aieo_create_index_if_not_exists' ) ) { require_once dirname( __DIR__, 2 ) . '/sql/sprocs.php'; } 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); if ( ! function_exists( 'aieo_create_index_if_not_exists' ) ) { require_once dirname( __DIR__, 2 ) . '/sql/sprocs.php'; } aieo_create_index_if_not_exists('aieo_dmm_idx_bd_brand', $table, '(brand_term_id)'); aieo_create_index_if_not_exists('aieo_dmm_idx_bd_enabled', $table, '(enabled)'); aieo_create_index_if_not_exists('aieo_dmm_idx_bd_window', $table, '(date_from, date_to)'); aieo_dmm_ensure_column($table, 'category_term_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'rule_operator', "varchar(8) NOT NULL DEFAULT 'AND'"); aieo_dmm_ensure_column($table, 'need_term_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'label', 'varchar(191) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'min_subtotal', "decimal(10,2) NOT NULL DEFAULT '0.00'"); aieo_dmm_ensure_column($table, 'min_subtotal_scope', "varchar(16) NOT NULL DEFAULT 'cart_total'"); aieo_dmm_ensure_column($table, 'application_scope', "varchar(16) NOT NULL DEFAULT 'targeted'"); aieo_dmm_ensure_column($table, 'excluded_brand_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_category_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_need_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'product_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_product_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'code_patterns', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_code_patterns', 'longtext DEFAULT NULL'); } function aieo_dmm_create_brand_gift_offers_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_brand_gift_offers'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `enabled` tinyint(1) NOT NULL DEFAULT 1, `label` varchar(191) DEFAULT NULL, `brand_term_ids` longtext DEFAULT NULL, `min_subtotal` decimal(10,2) NOT NULL DEFAULT '0.00', `date_from` date DEFAULT NULL, `date_to` date DEFAULT NULL, `gift_product_ids` longtext DEFAULT NULL, `before_text` longtext DEFAULT NULL, `after_text` longtext DEFAULT NULL, `before_image_id` bigint unsigned DEFAULT NULL, `after_image_id` bigint unsigned DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_bgo_enabled', $table, '(enabled)'); aieo_create_index_if_not_exists('aieo_dmm_idx_bgo_window', $table, '(date_from, date_to)'); aieo_dmm_ensure_column($table, 'category_term_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'rule_operator', "varchar(8) NOT NULL DEFAULT 'AND'"); aieo_dmm_ensure_column($table, 'bar_bg_before', 'varchar(7) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'bar_bg_after', 'varchar(7) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'bar_fg_before', 'varchar(7) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'bar_fg_after', 'varchar(7) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'gift_item_bg', 'varchar(7) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'show_banner', "tinyint(1) NOT NULL DEFAULT 1"); aieo_dmm_ensure_column($table, 'show_rewards', "tinyint(1) NOT NULL DEFAULT 1"); aieo_dmm_ensure_column($table, 'need_term_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'gift_kind', "varchar(16) NOT NULL DEFAULT 'product'"); aieo_dmm_ensure_column($table, 'presentation_mode', "varchar(16) NOT NULL DEFAULT 'auto'"); aieo_dmm_ensure_column($table, 'show_when_pending', "tinyint(1) NOT NULL DEFAULT 1"); aieo_dmm_ensure_column($table, 'headline_pending', 'varchar(255) NOT NULL DEFAULT \'\''); aieo_dmm_ensure_column($table, 'headline_qualified', 'varchar(255) NOT NULL DEFAULT \'\''); aieo_dmm_ensure_column($table, 'pill_locked', 'varchar(80) NOT NULL DEFAULT \'\''); aieo_dmm_ensure_column($table, 'pill_available', 'varchar(80) NOT NULL DEFAULT \'\''); aieo_dmm_ensure_column($table, 'pill_claimed', 'varchar(80) NOT NULL DEFAULT \'\''); aieo_dmm_ensure_column($table, 'excluded_brand_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_category_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_need_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'product_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_product_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'code_patterns', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_code_patterns', 'longtext DEFAULT NULL'); } function aieo_dmm_create_offers_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offers'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `type` varchar(32) NOT NULL DEFAULT 'discount', `enabled` tinyint(1) NOT NULL DEFAULT 1, `label` varchar(191) NOT NULL DEFAULT '', `priority` smallint NOT NULL DEFAULT 100, `start_date` date DEFAULT NULL, `end_date` date DEFAULT NULL, `rule_operator` varchar(8) NOT NULL DEFAULT 'AND', -- Positive targeting (intersect on at least one of these to qualify) ----- `brand_term_ids` longtext DEFAULT NULL, `category_term_ids` longtext DEFAULT NULL, `need_term_ids` longtext DEFAULT NULL, `product_ids` longtext DEFAULT NULL, -- Negative targeting (exclude these even if positive matches) ------------ `excluded_brand_ids` longtext DEFAULT NULL, `excluded_category_ids` longtext DEFAULT NULL, `excluded_need_ids` longtext DEFAULT NULL, `excluded_product_ids` longtext DEFAULT NULL, `code_patterns` longtext DEFAULT NULL, `excluded_code_patterns` longtext DEFAULT NULL, -- Cart-level conditions -------------------------------------------------- `min_subtotal` decimal(10,2) NOT NULL DEFAULT '0.00', `min_subtotal_scope` varchar(16) NOT NULL DEFAULT 'targeted', `min_quantity` int unsigned NOT NULL DEFAULT 0, `min_quantity_scope` varchar(16) NOT NULL DEFAULT 'targeted', -- Application scope (what gets the offer applied) ----------------------- `application_scope` varchar(16) NOT NULL DEFAULT 'targeted', -- Customer eligibility --------------------------------------------------- `customer_role_slugs` longtext DEFAULT NULL, `customer_segment_ids` longtext DEFAULT NULL, -- Type-specific config + display overrides as JSON ----------------------- `payload` longtext DEFAULT NULL, `display` longtext DEFAULT NULL, -- The offer's lasting columns (schema step 7; aieo_dmm_ensure_offer_columns_v7() holds the same list) -- `definition` longtext DEFAULT NULL, `definition_version` smallint unsigned NOT NULL DEFAULT 0, `revision` int unsigned NOT NULL DEFAULT 0, `stack_group` varchar(32) NOT NULL DEFAULT 'public', `exclusive` tinyint(1) NOT NULL DEFAULT 1, `state` varchar(16) NOT NULL DEFAULT '', `starts_at` datetime DEFAULT NULL, `ends_at` datetime DEFAULT NULL, `campaign_id` bigint unsigned NOT NULL DEFAULT 0, `source_kind` varchar(16) NOT NULL DEFAULT '', `source_id` bigint unsigned NOT NULL DEFAULT 0, `origin` varchar(32) NOT NULL DEFAULT '', -- Migration tracking (empty for natively-created offers) ----------------- `legacy_table` varchar(64) NOT NULL DEFAULT '', `legacy_id` bigint unsigned NOT NULL DEFAULT 0, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_offers_type_enabled', $table, '(`type`, `enabled`)'); aieo_create_index_if_not_exists('aieo_dmm_idx_offers_legacy', $table, '(`legacy_table`, `legacy_id`)'); aieo_create_index_if_not_exists('aieo_dmm_idx_offers_window', $table, '(`enabled`, `start_date`, `end_date`)'); aieo_dmm_ensure_column($table, 'code_patterns', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'excluded_code_patterns', 'longtext DEFAULT NULL'); aieo_dmm_ensure_offer_columns_v7($table); } function aieo_dmm_ensure_offer_columns_v7($table) { global $wpdb; $columns = array( 'definition' => 'longtext DEFAULT NULL', 'definition_version' => 'smallint unsigned NOT NULL DEFAULT 0', 'revision' => 'int unsigned NOT NULL DEFAULT 0', 'stack_group' => "varchar(32) NOT NULL DEFAULT 'public'", 'exclusive' => 'tinyint(1) NOT NULL DEFAULT 1', 'state' => "varchar(16) NOT NULL DEFAULT ''", 'starts_at' => 'datetime DEFAULT NULL', 'ends_at' => 'datetime DEFAULT NULL', 'campaign_id' => 'bigint unsigned NOT NULL DEFAULT 0', 'source_kind' => "varchar(16) NOT NULL DEFAULT ''", 'source_id' => 'bigint unsigned NOT NULL DEFAULT 0', 'origin' => "varchar(32) NOT NULL DEFAULT ''", ); $indexes = array( 'aieo_dmm_idx_offers_state' => '(`state`, `starts_at`, `ends_at`)', 'aieo_dmm_idx_offers_campaign' => '(`campaign_id`)', 'aieo_dmm_idx_offers_source' => '(`source_kind`, `source_id`)', ); $prev_suppress = isset($wpdb->suppress_errors) ? (bool) $wpdb->suppress_errors : false; $wpdb->suppress_errors(true); $have = array_map('strtolower', (array) $wpdb->get_col($wpdb->prepare( 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ))); $add = array(); foreach ($columns as $name => $definition) { if (!in_array($name, $have, true)) { $add[] = "ADD COLUMN `{$name}` {$definition}"; } } if ($have && $add) { $wpdb->query("ALTER TABLE `{$table}` " . implode(', ', $add)); } $have_indexes = array_map('strtolower', (array) $wpdb->get_col($wpdb->prepare( 'SELECT DISTINCT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $table ))); $add = array(); foreach ($indexes as $name => $on) { if (!in_array($name, $have_indexes, true)) { $add[] = "ADD KEY `{$name}` {$on}"; } } if ($have && $add) { $wpdb->query("ALTER TABLE `{$table}` " . implode(', ', $add)); } $wpdb->suppress_errors($prev_suppress); $in = implode(', ', array_fill(0, count($columns), '%s')); return count($columns) === (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME IN ({$in})", array_merge(array($table), array_keys($columns)) )); } function aieo_dmm_create_offer_revisions_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_revisions'; $charset_collate = aieo_get_charset_collate(); $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `offer_id` bigint unsigned NOT NULL, `revision` int unsigned NOT NULL, `saved_gmt` datetime NOT NULL, `user_id` bigint unsigned NOT NULL DEFAULT 0, `saved_by` varchar(16) NOT NULL DEFAULT 'user', `reason` varchar(191) NOT NULL DEFAULT '', `row_json` longtext NOT NULL, `statement` longtext DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_offer_revision` (`offer_id`, `revision`), KEY `aieo_dmm_idx_offer_revision_saved` (`saved_gmt`) ) {$charset_collate};"); } function aieo_dmm_create_offer_targets_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_targets'; $charset_collate = aieo_get_charset_collate(); $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `offer_id` bigint unsigned NOT NULL, `kind` varchar(8) NOT NULL DEFAULT 'product', `taxonomy` varchar(32) NOT NULL DEFAULT '', `object_id` bigint unsigned NOT NULL DEFAULT 0, `pattern` varchar(64) NOT NULL DEFAULT '', `included` tinyint(1) NOT NULL DEFAULT 1, `side` varchar(8) NOT NULL DEFAULT 'target', `built_v` smallint unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `aieo_dmm_idx_offer_targets_object` (`kind`, `taxonomy`, `object_id`), KEY `aieo_dmm_idx_offer_targets_offer` (`offer_id`) ) {$charset_collate};"); } function aieo_dmm_create_offer_facts_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_facts'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `product_id` bigint(20) unsigned NOT NULL, `list_price` decimal(19,4) DEFAULT NULL, `price` decimal(19,4) DEFAULT NULL, `strike` decimal(19,4) DEFAULT NULL, `lowest_30d` decimal(19,4) DEFAULT NULL, `offers` longtext DEFAULT NULL, `set_hash` char(12) NOT NULL DEFAULT '', `badges` longtext DEFAULT NULL, `valid_until` datetime DEFAULT NULL, `built_gmt` datetime NOT NULL, `snapshot_v` int(10) unsigned NOT NULL DEFAULT 0, `built_v` smallint(5) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`product_id`), KEY `aieo_dmm_idx_facts_valid` (`valid_until`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_create_offer_redemptions_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_redemptions'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `order_id` bigint(20) unsigned NOT NULL, `order_item_id` bigint(20) unsigned NOT NULL DEFAULT 0, `item_type` varchar(16) NOT NULL DEFAULT 'line', `offer_id` bigint(20) unsigned NOT NULL DEFAULT 0, `offer_rev` int(10) unsigned NOT NULL DEFAULT 0, `offer_kind` varchar(32) NOT NULL DEFAULT '', `campaign_id` bigint(20) unsigned NOT NULL DEFAULT 0, `access_id` bigint(20) unsigned NOT NULL DEFAULT 0, `access_key` varchar(191) NOT NULL DEFAULT '', `grant_id` bigint(20) unsigned NOT NULL DEFAULT 0, `placement_id` bigint(20) unsigned NOT NULL DEFAULT 0, `place` varchar(32) NOT NULL DEFAULT '', `role` varchar(12) NOT NULL DEFAULT '', `sole` tinyint(1) NOT NULL DEFAULT 0, `set_hash` char(12) NOT NULL DEFAULT '', `tb` varchar(8) NOT NULL DEFAULT '', `part` varchar(16) NOT NULL DEFAULT 'benefit', `application` smallint(5) unsigned NOT NULL DEFAULT 0, `product_id` bigint(20) unsigned NOT NULL DEFAULT 0, `variation_id` bigint(20) unsigned NOT NULL DEFAULT 0, `units` int(10) unsigned NOT NULL DEFAULT 0, `list_value` decimal(19,4) NOT NULL DEFAULT '0.0000', `reduction` decimal(19,4) NOT NULL DEFAULT '0.0000', `alone` decimal(19,4) NOT NULL DEFAULT '0.0000', `marginal` decimal(19,4) NOT NULL DEFAULT '0.0000', `cost_goods` decimal(19,4) DEFAULT NULL, `margin_left` decimal(19,4) DEFAULT NULL, `cost_basis` varchar(20) NOT NULL DEFAULT 'unknown_proxy', `user_id` bigint(20) unsigned NOT NULL DEFAULT 0, `email_hash` char(64) NOT NULL DEFAULT '', `customer_new` tinyint(1) NOT NULL DEFAULT 0, `audience_ids` longtext DEFAULT NULL, `visit_id` varchar(64) NOT NULL DEFAULT '', `status` varchar(16) NOT NULL DEFAULT 'paid', `reconstructed` tinyint(1) NOT NULL DEFAULT 0, `doc` longtext DEFAULT NULL, `created_gmt` datetime NOT NULL, `updated_gmt` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_red_item` (`order_id`, `order_item_id`, `offer_id`, `part`), KEY `aieo_dmm_idx_red_offer` (`offer_id`, `created_gmt`), KEY `aieo_dmm_idx_red_campaign` (`campaign_id`, `created_gmt`), KEY `aieo_dmm_idx_red_product` (`product_id`, `created_gmt`), KEY `aieo_dmm_idx_red_status_created` (`status`, `created_gmt`), KEY `aieo_dmm_idx_red_user` (`user_id`), KEY `aieo_dmm_idx_red_email` (`email_hash`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_create_offer_attributions_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_attributions'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `attribution_id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_id` bigint unsigned NOT NULL, `offer_id` bigint unsigned NOT NULL DEFAULT 0, `offer_type` varchar(32) NOT NULL DEFAULT '', `offer_label` varchar(191) NOT NULL DEFAULT '', `applied_value` decimal(10,2) NOT NULL DEFAULT '0.00', `actual_cost` decimal(10,2) DEFAULT NULL, `cost_basis` varchar(20) NOT NULL DEFAULT 'unknown_proxy', `order_total` decimal(12,2) NOT NULL DEFAULT '0.00', `entry_channel` varchar(40) NOT NULL DEFAULT '', `user_id` bigint unsigned NOT NULL DEFAULT 0, `target_product_id` bigint unsigned NOT NULL DEFAULT 0, `target_units` int unsigned NOT NULL DEFAULT 0, `target_revenue` decimal(12,2) NOT NULL DEFAULT '0.00', `shipping_method` varchar(120) DEFAULT NULL, `shipping_method_id` varchar(60) DEFAULT NULL, `shipping_zone` varchar(80) DEFAULT NULL, `shipping_zone_id` int DEFAULT NULL, `fired_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`attribution_id`), KEY idx_order (`order_id`), KEY idx_offer (`offer_id`, `offer_type`), KEY idx_channel_type (`entry_channel`, `offer_type`), KEY idx_user_offer (`user_id`, `offer_type`), KEY idx_fired_at (`fired_at`), KEY idx_pending_cost (`offer_type`, `actual_cost`), KEY idx_method_zone (`shipping_method_id`, `shipping_zone_id`, `fired_at`) ) {$charset_collate};"; $wpdb->query($sql); aieo_dmm_ensure_column($table, 'shipping_method', 'varchar(120) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'shipping_method_id', 'varchar(60) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'shipping_zone', 'varchar(80) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'shipping_zone_id', 'int DEFAULT NULL'); aieo_dmm_ensure_column($table, 'entry_branch_id', 'bigint unsigned DEFAULT NULL'); aieo_create_index_if_not_exists('idx_entry_branch', $table, '(entry_branch_id)'); aieo_dmm_ensure_column($table, 'target_units', 'int unsigned NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'target_revenue', "decimal(12,2) NOT NULL DEFAULT '0.00'"); aieo_create_index_if_not_exists('idx_method_zone', $table, '(`shipping_method_id`, `shipping_zone_id`, `fired_at`)'); } function aieo_dmm_migrate_legacy_to_offers() { global $wpdb; $unified = $wpdb->prefix . 'aieo_dmm_offers'; $gifts = $wpdb->prefix . 'aieo_dmm_brand_gift_offers'; $discos = $wpdb->prefix . 'aieo_dmm_brand_discounts'; if (!aieo_dmm_table_exists($unified)) { return; } if (aieo_dmm_table_exists($gifts)) { $rows = $wpdb->get_results("SELECT * FROM {$gifts}", ARRAY_A); if (is_array($rows)) { foreach ($rows as $row) { aieo_dmm_upsert_offer_from_legacy($row, 'gift', $gifts); } } } if (aieo_dmm_table_exists($discos)) { $rows = $wpdb->get_results("SELECT * FROM {$discos}", ARRAY_A); if (is_array($rows)) { foreach ($rows as $row) { aieo_dmm_upsert_offer_from_legacy($row, 'discount', $discos); } } } } function aieo_dmm_upsert_offer_from_legacy(array $row, $offer_type, $legacy_table) { $legacy_id = (int) ($row['id'] ?? 0); if ($legacy_id <= 0) { return; } if (!class_exists('AIEO_DMM_Offer_Writer', false)) { require_once dirname(__DIR__) . '/services/class-aieo-dmm-offer-writer.php'; } AIEO_DMM_Offer_Writer::copy_from_legacy((string) $legacy_table, $legacy_id); } function aieo_dmm_create_loyalty_classes_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_loyalty_classes'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `slug` varchar(64) NOT NULL, `label` varchar(191) NOT NULL, `extra_percent` decimal(5,2) NOT NULL DEFAULT '0.00', `points_per_currency` decimal(8,4) NOT NULL DEFAULT '0.0000', `enabled` tinyint(1) NOT NULL DEFAULT 1, `description` text DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `date_from` date DEFAULT NULL, `date_to` date DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_lc_slug` (`slug`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_lc_enabled', $table, '(enabled)'); aieo_dmm_ensure_column($table, 'points_per_currency', "decimal(8,4) NOT NULL DEFAULT '0.0000'"); aieo_dmm_ensure_loyalty_window_columns(); $ledger = $wpdb->prefix . 'aieo_dmm_loyalty_points'; $sql2 = "CREATE TABLE IF NOT EXISTS {$ledger} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint unsigned NOT NULL, `delta` decimal(12,2) NOT NULL, `balance_after` decimal(14,2) NOT NULL DEFAULT '0.00', `source` varchar(32) NOT NULL, `source_ref` varchar(64) DEFAULT NULL, `class_id` bigint unsigned DEFAULT NULL, `notes` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) {$charset_collate};"; $wpdb->query($sql2); aieo_create_index_if_not_exists('aieo_dmm_idx_lp_user', $ledger, '(user_id)'); aieo_create_index_if_not_exists('aieo_dmm_idx_lp_source', $ledger, '(source, source_ref)'); aieo_create_index_if_not_exists('aieo_dmm_idx_lp_created', $ledger, '(created_at)'); } function aieo_dmm_ensure_loyalty_window_columns() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_loyalty_classes'; aieo_dmm_ensure_column($table, 'date_from', 'date DEFAULT NULL'); aieo_dmm_ensure_column($table, 'date_to', 'date DEFAULT NULL'); $found = (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME IN ('date_from', 'date_to')", $table )); if (2 !== $found) { return false; } if (class_exists('AIEO_DMM_Loyalty_Window')) { AIEO_DMM_Loyalty_Window::mark_ready(); } else { update_option('aieo_dmm_loyalty_window_ready', '1', true); } return true; } function aieo_dmm_create_loyalty_assignments_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_loyalty_assignments'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `user_id` bigint unsigned NOT NULL, `class_id` bigint unsigned NOT NULL, `assigned_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`, `class_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_la_class', $table, '(class_id)'); } function aieo_dmm_create_no_free_shipping_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_no_free_shipping'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `product_id` bigint unsigned NOT NULL, `sku` varchar(100) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`product_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_nfs_sku', $table, '(sku)'); } function aieo_dmm_create_branches_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_branches'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `slug` varchar(64) NOT NULL, `label` varchar(191) NOT NULL, `stock_table` varchar(128) NOT NULL, `match_col` varchar(64) NOT NULL DEFAULT 'gtin', `enabled` tinyint(1) NOT NULL DEFAULT 1, `sort_order` int NOT NULL DEFAULT 0, `erp_id` varchar(64) DEFAULT NULL, `match_patterns` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_br_slug` (`slug`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_br_enabled', $table, '(enabled)'); aieo_dmm_ensure_column($table, 'erp_id', 'varchar(64) DEFAULT NULL'); aieo_create_index_if_not_exists('aieo_dmm_idx_br_erp', $table, '(erp_id)'); aieo_dmm_ensure_column($table, 'match_patterns', 'varchar(255) DEFAULT NULL'); $has_soft1 = $wpdb->get_var($wpdb->prepare( 'SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s', $table, 'soft1_id' )); if ((int) $has_soft1 > 0) { $wpdb->query("UPDATE {$table} SET erp_id = CAST(soft1_id AS CHAR) WHERE erp_id IS NULL AND soft1_id IS NOT NULL"); $wpdb->query("ALTER TABLE {$table} DROP COLUMN soft1_id"); } aieo_dmm_ensure_column($table, 'scanner_page_id', 'bigint unsigned DEFAULT NULL'); $mt = $wpdb->prefix . 'aieo_dmm_branch_matchers'; $wpdb->query("CREATE TABLE IF NOT EXISTS {$mt} ( `branch_id` bigint unsigned NOT NULL, `pattern` varchar(191) NOT NULL, PRIMARY KEY (`branch_id`, `pattern`) ) {$charset_collate};"); } function aieo_dmm_create_branch_scan_log_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_branch_scan_log'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `barcode` varchar(100) NOT NULL, `status` varchar(32) NOT NULL DEFAULT 'missing', `branch_id` bigint unsigned DEFAULT NULL, `user_id` bigint unsigned DEFAULT NULL, `user_login` varchar(60) DEFAULT NULL, `image_url` text DEFAULT NULL, `image_path` text DEFAULT NULL, `user_agent` varchar(255) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_barcode', $table, '(barcode)'); aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_status', $table, '(status)'); aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_branch', $table, '(branch_id)'); aieo_create_index_if_not_exists('aieo_dmm_idx_bsl_created', $table, '(created_at)'); } function aieo_dmm_create_product_needs_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_product_needs'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `product_id` bigint unsigned NOT NULL, `selected_needs` text DEFAULT NULL, `need_order` text DEFAULT NULL, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`product_id`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_create_discount_schedules_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_discount_schedules'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `product_id` bigint unsigned NOT NULL, `sku` varchar(100) DEFAULT NULL, `percent` decimal(5,2) NOT NULL DEFAULT '0.00', `date_from` date DEFAULT NULL, `date_to` date DEFAULT NULL, `active` tinyint(1) NOT NULL DEFAULT 1, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_ds_product', $table, '(product_id)'); aieo_create_index_if_not_exists('aieo_dmm_idx_ds_active', $table, '(active)'); aieo_create_index_if_not_exists('aieo_dmm_idx_ds_window', $table, '(date_from, date_to)'); } function aieo_dmm_create_export_jobs_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_export_jobs'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `job_id` varchar(64) NOT NULL, `type` varchar(64) NOT NULL, `status` varchar(20) NOT NULL DEFAULT 'pending', `total` bigint unsigned NOT NULL DEFAULT 0, `processed` bigint unsigned NOT NULL DEFAULT 0, `last_id` bigint unsigned NOT NULL DEFAULT 0, `filename` varchar(255) DEFAULT NULL, `params` longtext DEFAULT NULL, `log` longtext DEFAULT NULL, `user_id` bigint unsigned DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`job_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_ej_type', $table, '(type)'); aieo_create_index_if_not_exists('aieo_dmm_idx_ej_status', $table, '(status)'); aieo_create_index_if_not_exists('aieo_dmm_idx_ej_user', $table, '(user_id)'); aieo_create_index_if_not_exists('aieo_dmm_idx_ej_created', $table, '(created_at)'); } function aieo_dmm_create_feed_configs_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_feed_configs'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `slug` varchar(64) NOT NULL, `label` varchar(191) NOT NULL, `format` varchar(16) NOT NULL DEFAULT 'xml', `template` varchar(64) DEFAULT NULL, `file_path` varchar(255) DEFAULT NULL, `include_ids` longtext DEFAULT NULL, `include_brand_ids` longtext DEFAULT NULL, `exclude_ids` longtext DEFAULT NULL, `exclude_brand_ids` longtext DEFAULT NULL, `field_map` longtext DEFAULT NULL, `schedule` varchar(32) NOT NULL DEFAULT 'manual', `cache_mode` varchar(32) NOT NULL DEFAULT 'version_query', `last_run_at` datetime DEFAULT NULL, `last_run_status` varchar(32) DEFAULT NULL, `last_run_count` bigint unsigned DEFAULT NULL, `last_run_etag` varchar(64) DEFAULT NULL, `enabled` tinyint(1) NOT NULL DEFAULT 1, `exclude_auto_restocked` tinyint(1) NOT NULL DEFAULT 0, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_fc_slug` (`slug`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_fc_enabled', $table, '(enabled)'); aieo_dmm_ensure_column($table, 'cache_mode', "varchar(32) NOT NULL DEFAULT 'version_query'"); aieo_dmm_ensure_column($table, 'last_run_etag', 'varchar(64) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'include_category_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'exclude_category_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'exclude_auto_restocked', 'tinyint(1) NOT NULL DEFAULT 0'); } function aieo_dmm_create_feed_terms_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_feed_terms'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `feed_id` bigint unsigned NOT NULL, `supplier_term_id` bigint unsigned NOT NULL DEFAULT 0, `kind` varchar(12) NOT NULL, `value` varchar(191) NOT NULL, `path` varchar(255) NOT NULL DEFAULT '', `parent_value` varchar(191) NOT NULL DEFAULT '', `product_count` int unsigned NOT NULL DEFAULT 0, `last_version` int unsigned NOT NULL DEFAULT 0, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_ft` (`feed_id`, `kind`, `value`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_ft_supplier', $table, '(supplier_term_id, kind)'); } function aieo_dmm_create_feed_cat_map_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_feed_cat_map'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `owner_scope` varchar(8) NOT NULL DEFAULT 'feed', `owner_key` bigint unsigned NOT NULL, `feed_id` bigint unsigned NOT NULL DEFAULT 0, `supplier_term_id` bigint unsigned NOT NULL DEFAULT 0, `vendor_value` varchar(191) NOT NULL, `vendor_path` varchar(255) NOT NULL, `path_hash` char(32) NOT NULL, `term_id` bigint unsigned NOT NULL DEFAULT 0, `score` decimal(4,3) NOT NULL DEFAULT 0.000, `status` varchar(12) NOT NULL DEFAULT 'suggested', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_fcm` (`owner_scope`, `owner_key`, `path_hash`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_fcm_status', $table, '(status)'); } function aieo_feed_migrate_path_hashes() { global $wpdb; if (!class_exists('AIEO_Feed_Mapping')) { return; } $target = (int) AIEO_Feed_Mapping::PATH_KEY_VERSION; if ((int) get_option('aieo_feed_path_key_version', 1) >= $target) { return; } $key = function ($path) { return md5(AIEO_Feed_Mapping::category_path_key(explode(' > ', (string) $path))); }; $t = $wpdb->prefix . 'aieo_dmm_feed_cat_map'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) { $rank = array('manual' => 5, 'confirmed' => 4, 'learned' => 3, 'auto' => 2, 'suggested' => 1, 'rejected' => 0); $seen = array(); foreach ((array) $wpdb->get_results("SELECT id, owner_scope, owner_key, vendor_path, status, score FROM `$t`") as $r) { $h = $key($r->vendor_path); $slot = $r->owner_scope . '|' . $r->owner_key . '|' . $h; $mine = array((int) ($rank[$r->status] ?? 0), (float) $r->score); if (isset($seen[$slot])) { $keep = $seen[$slot]; if ($mine > $keep['w']) { $wpdb->delete($t, array('id' => (int) $keep['id']), array('%d')); $seen[$slot] = array('id' => (int) $r->id, 'w' => $mine); $wpdb->update($t, array('path_hash' => $h), array('id' => (int) $r->id), array('%s'), array('%d')); } else { $wpdb->delete($t, array('id' => (int) $r->id), array('%d')); } continue; } $seen[$slot] = array('id' => (int) $r->id, 'w' => $mine); $wpdb->update($t, array('path_hash' => $h), array('id' => (int) $r->id), array('%s'), array('%d')); } } $t = $wpdb->prefix . 'aieo_dmm_cat_match_register'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) { $seen = array(); foreach ((array) $wpdb->get_results("SELECT id, vendor_path, vendor_value, hits FROM `$t`") as $r) { $h = $key($r->vendor_path); if (isset($seen[$h])) { $keep = $seen[$h]; if ((int) $r->hits > (int) $keep['hits']) { $wpdb->delete($t, array('id' => (int) $keep['id']), array('%d')); $seen[$h] = array('id' => (int) $r->id, 'hits' => (int) $r->hits + (int) $keep['hits']); $wpdb->update($t, array( 'path_hash' => $h, 'leaf_norm' => AIEO_Feed_Mapping::category_path_key(array((string) $r->vendor_value)), 'hits' => (int) $r->hits + (int) $keep['hits'], ), array('id' => (int) $r->id), array('%s', '%s', '%d'), array('%d')); } else { $wpdb->update($t, array('hits' => (int) $keep['hits'] + (int) $r->hits), array('id' => (int) $keep['id']), array('%d'), array('%d')); $seen[$h]['hits'] += (int) $r->hits; $wpdb->delete($t, array('id' => (int) $r->id), array('%d')); } continue; } $seen[$h] = array('id' => (int) $r->id, 'hits' => (int) $r->hits); $wpdb->update($t, array( 'path_hash' => $h, 'leaf_norm' => AIEO_Feed_Mapping::category_path_key(array((string) $r->vendor_value)), ), array('id' => (int) $r->id), array('%s', '%s'), array('%d')); } } $t = $wpdb->prefix . 'aieo_dmm_stock_word_register'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) { $seen = array(); foreach ((array) $wpdb->get_results("SELECT id, word FROM `$t`") as $r) { $n = AIEO_Feed_Mapping::category_path_key(array((string) $r->word)); if ($n === '' || isset($seen[$n])) { if (isset($seen[$n])) { $wpdb->delete($t, array('id' => (int) $r->id), array('%d')); } continue; } $seen[$n] = true; $wpdb->update($t, array('word_norm' => $n), array('id' => (int) $r->id), array('%s'), array('%d')); } } $t = $wpdb->prefix . 'aieo_dmm_feed_row_terms'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $t)) === $t) { $wpdb->query("TRUNCATE TABLE `$t`"); $rows = $wpdb->prefix . 'aieo_dmm_feed_rows'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $rows)) === $rows) { $wpdb->query("TRUNCATE TABLE `$rows`"); } } update_option('aieo_feed_path_key_version', $target, false); } function aieo_dmm_create_feed_rows_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_feed_rows'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `feed_id` bigint unsigned NOT NULL, `row_no` int unsigned NOT NULL, `feed_version` int unsigned NOT NULL DEFAULT 0, `sku` varchar(100) NOT NULL DEFAULT '', `row_hash` char(32) NOT NULL DEFAULT '', `price_hash` char(32) NOT NULL DEFAULT '', `image_hash` char(32) NOT NULL DEFAULT '', `row_group_hash` char(32) NOT NULL DEFAULT '', `price_group_hash` char(32) NOT NULL DEFAULT '', `title` varchar(255) NOT NULL DEFAULT '', `regular_price` decimal(12,4) DEFAULT NULL, `sale_price` decimal(12,4) DEFAULT NULL, `cost_price` decimal(12,4) DEFAULT NULL, `stock_qty` int DEFAULT NULL, `stock_status` varchar(20) NOT NULL DEFAULT '', `barcode` varchar(100) NOT NULL DEFAULT '', `brand` varchar(191) NOT NULL DEFAULT '', `category_path` varchar(255) NOT NULL DEFAULT '', `group_key` varchar(191) NOT NULL DEFAULT '', `image_count` smallint unsigned NOT NULL DEFAULT 0, `payload` mediumtext, `landed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_fr` (`feed_id`, `row_no`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_fr_sku', $table, '(feed_id, sku)'); aieo_create_index_if_not_exists('aieo_dmm_idx_fr_price', $table, '(feed_id, price_hash)'); foreach (array('row_group_hash', 'price_group_hash') as $col) { $has = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM `{$table}` LIKE %s", $col)); if (!$has) { $wpdb->query("ALTER TABLE `{$table}` ADD COLUMN `{$col}` char(32) NOT NULL DEFAULT ''"); } } aieo_create_index_if_not_exists('aieo_dmm_idx_fr_group', $table, '(feed_id, group_key)'); } function aieo_dmm_create_feed_image_queue_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_feed_image_queue'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `product_id` bigint unsigned NOT NULL, `position` smallint unsigned NOT NULL DEFAULT 0, `url` varchar(500) NOT NULL, `url_hash` char(32) NOT NULL, `status` varchar(10) NOT NULL DEFAULT 'pending', `attempts` tinyint unsigned NOT NULL DEFAULT 0, `last_error` varchar(191) NOT NULL DEFAULT '', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `done_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_fiq` (`product_id`, `url_hash`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_fiq_status', $table, '(status, id)'); } function aieo_dmm_create_feed_run_log_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_feed_run_log'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `feed_id` bigint unsigned NOT NULL, `started_at` datetime NOT NULL, `finished_at` datetime DEFAULT NULL, `ok` tinyint(1) NOT NULL DEFAULT 0, `complete` tinyint(1) NOT NULL DEFAULT 0, `processed` int unsigned NOT NULL DEFAULT 0, `created` int unsigned NOT NULL DEFAULT 0, `updated` int unsigned NOT NULL DEFAULT 0, `skipped` int unsigned NOT NULL DEFAULT 0, `filtered_out` int unsigned NOT NULL DEFAULT 0, `errors_count` int unsigned NOT NULL DEFAULT 0, `message` varchar(255) NOT NULL DEFAULT '', `details_json` longtext NULL, PRIMARY KEY (`id`), KEY `aieo_dmm_idx_frl_feed` (`feed_id`, `started_at`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_create_feed_row_terms_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_feed_row_terms'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `feed_id` bigint unsigned NOT NULL, `row_id` bigint unsigned NOT NULL, `kind` varchar(12) NOT NULL, `value_key` varchar(191) COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`row_id`, `kind`, `value_key`), KEY `aieo_dmm_idx_frt_lookup` (`feed_id`, `kind`, `value_key`) ) {$charset_collate};"; $wpdb->query($sql); $col = $wpdb->get_row("SHOW FULL COLUMNS FROM `{$table}` LIKE 'value_key'"); if ($col && isset($col->Collation) && 'utf8mb4_bin' !== $col->Collation) { $wpdb->query("ALTER TABLE `{$table}` MODIFY `value_key` varchar(191) COLLATE utf8mb4_bin NOT NULL"); } } function aieo_dmm_create_cat_match_register_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_cat_match_register'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `path_hash` char(32) NOT NULL, `vendor_value` varchar(191) NOT NULL, `vendor_path` varchar(255) NOT NULL, `leaf_norm` varchar(191) NOT NULL, `term_id` bigint unsigned NOT NULL, `term_label` varchar(191) NOT NULL DEFAULT '', `term_path` varchar(255) NOT NULL DEFAULT '', `source` varchar(12) NOT NULL DEFAULT 'confirmed', `hits` int unsigned NOT NULL DEFAULT 1, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_cmr_path` (`path_hash`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_cmr_leaf', $table, '(leaf_norm)'); } function aieo_dmm_create_stock_word_register_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_stock_word_register'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `word_norm` varchar(191) NOT NULL, `word` varchar(191) NOT NULL, `stock_status` varchar(16) NOT NULL DEFAULT '', `stock_qty` int DEFAULT NULL, `discontinued` tinyint(1) NOT NULL DEFAULT 0, `source` varchar(12) NOT NULL DEFAULT 'rule', `hits` int unsigned NOT NULL DEFAULT 1, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_swr_word` (`word_norm`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_ensure_column($table, $column, $definition) { global $wpdb; $prev_suppress = isset($wpdb->suppress_errors) ? (bool) $wpdb->suppress_errors : false; $wpdb->suppress_errors(true); $found = $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", $table, $column )); if ((int) $found === 0) { $wpdb->query("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}"); if (function_exists('aieo_dmm_forget_table_columns')) { aieo_dmm_forget_table_columns($table); } } $wpdb->suppress_errors($prev_suppress); } function aieo_dmm_ensure_index($table, $index, $columns) { global $wpdb; $prev_suppress = isset($wpdb->suppress_errors) ? (bool) $wpdb->suppress_errors : false; $wpdb->suppress_errors(true); $found = $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s", $table, $index )); if ((int) $found === 0) { $wpdb->query("ALTER TABLE `{$table}` ADD KEY `{$index}` {$columns}"); } $wpdb->suppress_errors($prev_suppress); } function aieo_dmm_create_product_fp_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_product_fp'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `product_id` bigint unsigned NOT NULL, `parent_id` bigint unsigned NOT NULL DEFAULT 0, `base_regular` decimal(10,2) DEFAULT NULL, `base_sale` decimal(10,2) DEFAULT NULL, `brand_term_ids` longtext DEFAULT NULL, `cat_term_ids` longtext DEFAULT NULL, `effective_brand_pct` decimal(5,2) NOT NULL DEFAULT '0.00', `effective_cat_pct` decimal(5,2) NOT NULL DEFAULT '0.00', `effective_combined_pct` decimal(5,2) NOT NULL DEFAULT '0.00', `active_schedule_pct` decimal(5,2) NOT NULL DEFAULT '0.00', `nfs_excluded` tinyint(1) NOT NULL DEFAULT 0, `eligible_gift_offer_ids` longtext DEFAULT NULL, `fp_version_key` varchar(48) NOT NULL DEFAULT '', `computed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`product_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_parent', $table, '(parent_id)'); aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_nfs', $table, '(nfs_excluded)'); aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_version', $table, '(fp_version_key)'); aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_computed', $table, '(computed_at)'); aieo_create_index_if_not_exists('aieo_dmm_idx_pfp_combined', $table, '(effective_combined_pct)'); } function aieo_dmm_create_user_fp_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_user_fp'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `user_id` bigint unsigned NOT NULL, `role_hash` varchar(40) NOT NULL DEFAULT '', `enabled_role_column` varchar(64) NOT NULL DEFAULT '', `loyalty_class_ids` longtext DEFAULT NULL, `loyalty_extra_pct_sum` decimal(5,2) NOT NULL DEFAULT '0.00', `personal_extra_pct` decimal(5,2) NOT NULL DEFAULT '0.00', `brand_overrides` longtext DEFAULT NULL, `sequence_pct` decimal(5,2) NOT NULL DEFAULT '0.00', `sequence_expires_at` datetime DEFAULT NULL, `fp_version_key` varchar(48) NOT NULL DEFAULT '', `computed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_ufp_role_hash', $table, '(role_hash)'); aieo_create_index_if_not_exists('aieo_dmm_idx_ufp_version', $table, '(fp_version_key)'); aieo_create_index_if_not_exists('aieo_dmm_idx_ufp_seq_expiry', $table, '(sequence_expires_at)'); } function aieo_dmm_migrate_legacy_data() { global $wpdb; $new_role_prices = $wpdb->prefix . 'aieo_dmm_role_prices'; $new_nfs = $wpdb->prefix . 'aieo_dmm_no_free_shipping'; $new_product_needs = $wpdb->prefix . 'aieo_dmm_product_needs'; $new_gift_offers = $wpdb->prefix . 'aieo_dmm_brand_gift_offers'; $legacy_role_prices = $wpdb->prefix . 'om_role_based_pricing'; $legacy_nfs = $wpdb->prefix . 'om_no_free_shipping'; $legacy_product_needs = $wpdb->prefix . 'pods_product_need'; if (aieo_dmm_table_exists($legacy_role_prices) && aieo_dmm_table_is_empty($new_role_prices)) { $wpdb->query(" INSERT IGNORE INTO {$new_role_prices} (product_id, parent_id, product_type, sku, created_at, updated_at) SELECT product_id, parent_id, product_type, sku, created_at, updated_at FROM {$legacy_role_prices} "); } if (aieo_dmm_table_exists($legacy_nfs) && aieo_dmm_table_is_empty($new_nfs)) { $legacy_has_sku = (bool) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = 'sku'", $legacy_nfs )); if ($legacy_has_sku) { $sku_expr = "COALESCE(sku, (SELECT meta_value FROM {$wpdb->postmeta} pm WHERE pm.post_id = {$legacy_nfs}.product_id AND pm.meta_key = '_sku' LIMIT 1))"; } else { $sku_expr = "(SELECT meta_value FROM {$wpdb->postmeta} pm WHERE pm.post_id = {$legacy_nfs}.product_id AND pm.meta_key = '_sku' LIMIT 1)"; } $wpdb->query(" INSERT IGNORE INTO {$new_nfs} (product_id, sku, created_at) SELECT product_id, {$sku_expr} AS sku, created_at FROM {$legacy_nfs} "); } if (aieo_dmm_table_exists($legacy_product_needs) && aieo_dmm_table_is_empty($new_product_needs)) { $wpdb->query(" INSERT IGNORE INTO {$new_product_needs} (product_id, selected_needs, updated_at) SELECT product_id, selected_needs, UTC_TIMESTAMP() FROM {$legacy_product_needs} WHERE product_id IS NOT NULL "); } if (aieo_dmm_table_is_empty($new_gift_offers)) { $legacy_offers = get_option('om_brand_gift_offers', array()); if (is_array($legacy_offers) && !empty($legacy_offers)) { foreach ($legacy_offers as $offer) { if (!is_array($offer)) { continue; } $brand_ids = array(); if (!empty($offer['brand_ids']) && is_array($offer['brand_ids'])) { $brand_ids = array_map('absint', $offer['brand_ids']); } $gift_ids = array(); if (!empty($offer['gift_product_ids']) && is_array($offer['gift_product_ids'])) { $gift_ids = array_map('absint', $offer['gift_product_ids']); } elseif (!empty($offer['gift_product_id'])) { $gift_ids = array(absint($offer['gift_product_id'])); } $wpdb->insert( $new_gift_offers, array( 'enabled' => (isset($offer['enabled']) && $offer['enabled'] === 'yes') ? 1 : 0, 'label' => isset($offer['label']) ? (string) $offer['label'] : null, 'brand_term_ids' => wp_json_encode($brand_ids), 'min_subtotal' => isset($offer['min_subtotal']) ? (float) $offer['min_subtotal'] : 0.0, 'date_from' => !empty($offer['date_from']) ? (string) $offer['date_from'] : null, 'date_to' => !empty($offer['date_to']) ? (string) $offer['date_to'] : null, 'gift_product_ids' => wp_json_encode($gift_ids), 'before_text' => isset($offer['before_threshold_text']) ? (string) $offer['before_threshold_text'] : null, 'after_text' => isset($offer['after_threshold_text']) ? (string) $offer['after_threshold_text'] : null, 'before_image_id' => isset($offer['before_threshold_image_id']) ? absint($offer['before_threshold_image_id']) : null, 'after_image_id' => isset($offer['after_threshold_image_id']) ? absint($offer['after_threshold_image_id']) : null, ), array('%d', '%s', '%s', '%f', '%s', '%s', '%s', '%s', '%s', '%d', '%d') ); } } } aieo_dmm_seed_legacy_branches(); aieo_dmm_migrate_swatches_option(); } function aieo_dmm_migrate_swatches_option() { if (get_option('aieo_dmm_swatches_settings', null) !== null) { return; } $legacy = get_option('om_product_swatches_settings', null); if (is_array($legacy)) { update_option('aieo_dmm_swatches_settings', $legacy, false); update_option('aieo_dmm_swatches_settings_hash', md5((string) wp_json_encode($legacy)), false); } } function aieo_dmm_seed_legacy_branches() { global $wpdb; $branches = $wpdb->prefix . 'aieo_dmm_branches'; if (!aieo_dmm_table_is_empty($branches)) { return; } $legacy = array( 'keratsini' => 'store_board_keratsini', 'piraeus' => 'store_board_piraeus', 'korydallos' => 'store_board_korydallos', 'kallithea' => 'store_board_kallithea', ); $order = 0; foreach ($legacy as $slug => $suffix) { $candidate = $wpdb->prefix . $suffix; if (!aieo_dmm_table_exists($candidate)) { continue; } $wpdb->insert( $branches, array( 'slug' => $slug, 'label' => ucfirst($slug), 'stock_table' => $candidate, 'match_col' => 'gtin', 'enabled' => 1, 'sort_order' => $order++, ), array('%s', '%s', '%s', '%s', '%d', '%d') ); } if (function_exists('aieo_dmm_backfill_legacy_branch_matchers')) { aieo_dmm_backfill_legacy_branch_matchers(); } } if (!function_exists('aieo_dmm_known_tables_version')) { function aieo_dmm_known_tables_version() { return defined('AIEO_VERSION') ? (string) AIEO_VERSION : (string) get_option('aieo_version', '0'); } } if (!function_exists('aieo_dmm_table_exists')) { function aieo_dmm_table_exists($table_name) { global $wpdb; static $group_declared = false; if (!$group_declared) { wp_cache_add_non_persistent_groups(array('aieo_schema_probe')); $group_declared = true; } $known = wp_cache_get('tables', 'aieo_schema_probe'); if (!is_array($known)) { $stored = get_option('aieo_known_tables'); $known = (is_array($stored) && isset($stored['v'], $stored['t']) && (string) $stored['v'] === aieo_dmm_known_tables_version() && is_array($stored['t'])) ? $stored['t'] : array(); wp_cache_set('tables', $known, 'aieo_schema_probe'); } $key = (string) $table_name; if (isset($known[$key])) { return true; } $found = $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s", $table_name )); $exists = ((int) $found) > 0; if ($exists) { $known[$key] = true; wp_cache_set('tables', $known, 'aieo_schema_probe'); } return $exists; } function aieo_dmm_table_is_empty($table_name) { global $wpdb; if (!aieo_dmm_table_exists($table_name)) { return true; } $count = $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}"); return ((int) $count) === 0; } function aieo_dmm_sanitize_role_column($role) { $clean = strtolower(preg_replace('/[^a-z0-9_]/', '_', $role)); if ($clean === '') { return 'role_r'; } if (preg_match('/^[0-9]/', $clean)) { $clean = 'r_' . $clean; } return 'role_' . $clean; } } function aieo_dmm_sync_role_columns(array $enabled_roles) { global $wpdb; if (empty($enabled_roles)) { return; } $targets = array( $wpdb->prefix . 'aieo_dmm_role_prices', $wpdb->prefix . 'om_role_based_pricing', ); foreach ($targets as $table) { if (!aieo_dmm_table_exists($table)) { continue; } $existing_cols = $wpdb->get_col("SHOW COLUMNS FROM {$table}"); $existing_map = array_flip((array) $existing_cols); foreach ($enabled_roles as $role) { $col = aieo_dmm_sanitize_role_column((string) $role); if (isset($existing_map[$col])) { continue; } $wpdb->query("ALTER TABLE {$table} ADD COLUMN `{$col}` DECIMAL(10,2) DEFAULT NULL"); } } } function aieo_dmm_create_complementary_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_complementary'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `enabled` tinyint(1) NOT NULL DEFAULT 1, `label` varchar(191) DEFAULT NULL, `source_product_id` bigint unsigned NOT NULL, `product_ids` longtext DEFAULT NULL, `offer_title` varchar(255) DEFAULT NULL, `button_text` varchar(100) DEFAULT NULL, `placement` varchar(64) NOT NULL DEFAULT 'before_add_to_cart', `checkboxes_state` varchar(16) NOT NULL DEFAULT 'unchecked', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `idx_source_product` (`source_product_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_compl_enabled', $table, '(enabled)'); } function aieo_dmm_create_swatches_cache_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_swatches_cache'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `product_id` bigint unsigned NOT NULL, `context` varchar(16) NOT NULL, `total_available` int unsigned NOT NULL DEFAULT 0, `stored_count` int unsigned NOT NULL DEFAULT 0, `variations_json` longtext NOT NULL, `variation_ids_json` longtext NOT NULL, `variations_map_json` longtext DEFAULT NULL, `grid_html` longtext DEFAULT NULL, `cache_hash` char(32) NOT NULL DEFAULT '', `built_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`product_id`, `context`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_sw_built', $table, '(built_at)'); aieo_create_index_if_not_exists('aieo_dmm_idx_sw_hash', $table, '(cache_hash)'); } function aieo_dmm_create_color_attributes_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_color_attributes'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `taxonomy` varchar(191) NOT NULL, `attribute_id` bigint unsigned NOT NULL DEFAULT 0, `attribute_label` varchar(191) NOT NULL DEFAULT '', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`taxonomy`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_create_category_extras_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_category_extras'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `taxonomy` varchar(64) NOT NULL, `term_id` bigint unsigned NOT NULL, `headline` text DEFAULT NULL, `description` longtext DEFAULT NULL, `image_mobile_id` bigint unsigned DEFAULT NULL, `image_desktop_id` bigint unsigned DEFAULT NULL, `seo_title` varchar(255) DEFAULT NULL, `sidebar_text` longtext DEFAULT NULL, `footer_text` longtext DEFAULT NULL, `columns` tinyint unsigned DEFAULT NULL, `disable_nested` tinyint(1) NOT NULL DEFAULT 0, `template` varchar(64) NOT NULL DEFAULT 'default', `pro_only` tinyint(1) NOT NULL DEFAULT 0, `reference_quantity` int unsigned DEFAULT NULL, `synonyms` longtext DEFAULT NULL, `season_start` date DEFAULT NULL, `season_end` date DEFAULT NULL, `season_midpoint` date DEFAULT NULL, `landing_pages` longtext DEFAULT NULL, `related_brand_ids` longtext DEFAULT NULL, `related_post_ids` longtext DEFAULT NULL, `related_campaign_ids` longtext DEFAULT NULL, `google_category_id` int unsigned DEFAULT NULL, `google_category_path` varchar(500) DEFAULT NULL, `facebook_category_id` int unsigned DEFAULT NULL, `facebook_category_path` varchar(500) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_ce_term` (`taxonomy`, `term_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_create_index_if_not_exists('aieo_dmm_idx_ce_taxonomy', $table, '(taxonomy)'); aieo_dmm_ensure_column($table, 'pro_only', 'tinyint(1) NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'reference_quantity', 'int unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'synonyms', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'season_start', 'date DEFAULT NULL'); aieo_dmm_ensure_column($table, 'season_end', 'date DEFAULT NULL'); aieo_dmm_ensure_column($table, 'season_midpoint', 'date DEFAULT NULL'); aieo_dmm_ensure_column($table, 'related_brand_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'related_post_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'related_campaign_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'template', "varchar(64) NOT NULL DEFAULT 'default'"); aieo_dmm_ensure_column($table, 'google_category_id', 'int unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'google_category_path', 'varchar(500) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'facebook_category_id', 'int unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'facebook_category_path', 'varchar(500) DEFAULT NULL'); } function aieo_dmm_create_brand_extras_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_brand_extras'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint unsigned NOT NULL, `taxonomy` varchar(64) NOT NULL DEFAULT 'product_brand', `logo_image_id` bigint unsigned DEFAULT NULL, `hero_image_id` bigint unsigned DEFAULT NULL, `headline` text DEFAULT NULL, `history_text` longtext DEFAULT NULL, `ambassadors_text` longtext DEFAULT NULL, `importer_text` longtext DEFAULT NULL, `website_url` varchar(500) DEFAULT NULL, `promo` tinyint NOT NULL DEFAULT 0, `priority` int NOT NULL DEFAULT 0, `bias` decimal(5,4) NOT NULL DEFAULT 0, `gross_margin` decimal(5,2) DEFAULT NULL, `supplier_term_id` bigint unsigned DEFAULT NULL, `footer_text` longtext DEFAULT NULL, `gallery_image_ids` longtext DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_be_term` (`taxonomy`, `term_id`), KEY `aieo_dmm_idx_be_priority` (`priority`), KEY `aieo_dmm_idx_be_supplier` (`supplier_term_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_dmm_ensure_column($table, 'taxonomy', "varchar(64) NOT NULL DEFAULT 'product_brand'"); aieo_dmm_ensure_column($table, 'logo_image_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'hero_image_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'headline', 'text DEFAULT NULL'); aieo_dmm_ensure_column($table, 'history_text', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'ambassadors_text', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'importer_text', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'website_url', 'varchar(500) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'promo', 'tinyint NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'priority', 'int NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'bias', 'decimal(5,4) NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'gross_margin', 'decimal(5,2) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'supplier_term_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'footer_text', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'gallery_image_ids','longtext DEFAULT NULL'); } function aieo_dmm_migrate_category_extras_to_term_relations() { global $wpdb; if (!class_exists('AIEO_DMM_Term_Relations')) { return 0; } $src_table = $wpdb->prefix . 'aieo_dmm_category_extras'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $src_table)) !== $src_table) { return 0; } $rows = $wpdb->get_results( "SELECT taxonomy, term_id, related_brand_ids, related_post_ids, related_campaign_ids FROM {$src_table} WHERE (related_brand_ids IS NOT NULL AND related_brand_ids NOT IN ('', '[]')) OR (related_post_ids IS NOT NULL AND related_post_ids NOT IN ('', '[]')) OR (related_campaign_ids IS NOT NULL AND related_campaign_ids NOT IN ('', '[]'))", ARRAY_A ); if (empty($rows)) { return 0; } $processed = 0; foreach ($rows as $r) { $tax = (string) $r['taxonomy']; $tid = (int) $r['term_id']; if ($tax === '' || $tid <= 0) { continue; } $brand_ids = json_decode((string) $r['related_brand_ids'], true); if (is_array($brand_ids) && !empty($brand_ids)) { AIEO_DMM_Term_Relations::set_targets( $tax, $tid, 'term', 'product_brand', array_map('intval', $brand_ids) ); } $post_ids = json_decode((string) $r['related_post_ids'], true); if (is_array($post_ids) && !empty($post_ids)) { AIEO_DMM_Term_Relations::set_targets( $tax, $tid, 'post', 'post', array_map('intval', $post_ids) ); } $campaign_ids = json_decode((string) $r['related_campaign_ids'], true); if (is_array($campaign_ids) && !empty($campaign_ids)) { AIEO_DMM_Term_Relations::set_targets( $tax, $tid, 'post', 'campaign', array_map('intval', $campaign_ids) ); } $processed++; } return $processed; } function aieo_dmm_migrate_brand_rows_from_category_extras() { global $wpdb; $src = $wpdb->prefix . 'aieo_dmm_category_extras'; $dst = $wpdb->prefix . 'aieo_dmm_brand_extras'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $src)) !== $src) { return 0; } if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $dst)) !== $dst) { return 0; } $rows = $wpdb->get_results( "SELECT term_id, headline, footer_text FROM {$src} WHERE taxonomy = 'product_brand' AND ((headline IS NOT NULL AND headline <> '') OR (footer_text IS NOT NULL AND footer_text <> ''))", ARRAY_A ); if (empty($rows)) { return 0; } $migrated = 0; foreach ($rows as $r) { $term_id = (int) $r['term_id']; $headline = (string) $r['headline']; $footer = (string) $r['footer_text']; if ($term_id <= 0) { continue; } $existing = $wpdb->get_row($wpdb->prepare( "SELECT id, headline, footer_text FROM {$dst} WHERE taxonomy = 'product_brand' AND term_id = %d LIMIT 1", $term_id ), ARRAY_A); $new_headline = ($existing && (string) $existing['headline'] !== '') ? $existing['headline'] : $headline; $new_footer = ($existing && (string) $existing['footer_text'] !== '') ? $existing['footer_text'] : $footer; if ($existing) { if ($new_headline !== $existing['headline'] || $new_footer !== $existing['footer_text']) { $wpdb->update($dst, array('headline' => $new_headline, 'footer_text' => $new_footer), array('id' => (int) $existing['id']) ); $migrated++; } } else { $now = current_time('mysql', true); $wpdb->insert($dst, array( 'term_id' => $term_id, 'taxonomy' => 'product_brand', 'headline' => $new_headline, 'footer_text' => $new_footer, 'created_at' => $now, 'updated_at' => $now, )); $migrated++; } } if (class_exists('AIEO_DMM_Brand_Extras_Data')) { AIEO_DMM_Brand_Extras_Data::flush_request_cache(); } return $migrated; } function aieo_dmm_create_need_extras_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_need_extras'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint unsigned NOT NULL, `taxonomy` varchar(64) NOT NULL DEFAULT 'pa_need', `icon_image_id` bigint unsigned DEFAULT NULL, `hero_image_id` bigint unsigned DEFAULT NULL, `headline` text DEFAULT NULL, `about_text` longtext DEFAULT NULL, `priority` int NOT NULL DEFAULT 0, `footer_text` longtext DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_ne_term` (`taxonomy`, `term_id`), KEY `aieo_dmm_idx_ne_priority` (`priority`) ) {$charset_collate};"; $wpdb->query($sql); aieo_dmm_ensure_column($table, 'taxonomy', "varchar(64) NOT NULL DEFAULT 'pa_need'"); aieo_dmm_ensure_column($table, 'icon_image_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'hero_image_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'headline', 'text DEFAULT NULL'); aieo_dmm_ensure_column($table, 'about_text', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'priority', 'int NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'footer_text', 'longtext DEFAULT NULL'); } function aieo_dmm_create_supplier_extras_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_supplier_extras'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `term_id` bigint unsigned NOT NULL, `taxonomy` varchar(64) NOT NULL DEFAULT 'supplier', `email` varchar(190) DEFAULT NULL, `lead_time_days` int DEFAULT NULL, `payment_terms_days` int DEFAULT NULL, `special_discount_pct` decimal(5,2) NOT NULL DEFAULT 0, `supplier_owner_user_id` bigint unsigned DEFAULT NULL, `notes` longtext DEFAULT NULL, `erp_id` varchar(64) DEFAULT NULL, `erp_sku_code` varchar(32) DEFAULT NULL, `vat_code` varchar(32) DEFAULT NULL, `address_line_1` varchar(255) DEFAULT NULL, `address_region` varchar(100) DEFAULT NULL, `address_city` varchar(100) DEFAULT NULL, `address_postal_code` varchar(30) DEFAULT NULL, `tel_1` varchar(30) DEFAULT NULL, `tel_2` varchar(30) DEFAULT NULL, `fax` varchar(30) DEFAULT NULL, `corporate_url` varchar(190) DEFAULT NULL, `corporate_email` varchar(190) DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_se_term` (`taxonomy`, `term_id`), KEY `aieo_dmm_idx_se_owner` (`supplier_owner_user_id`) ) {$charset_collate};"; $wpdb->query($sql); aieo_dmm_ensure_column($table, 'taxonomy', "varchar(64) NOT NULL DEFAULT 'supplier'"); aieo_dmm_ensure_column($table, 'email', 'varchar(190) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'lead_time_days', 'int DEFAULT NULL'); aieo_dmm_ensure_column($table, 'payment_terms_days', 'int DEFAULT NULL'); aieo_dmm_ensure_column($table, 'special_discount_pct', 'decimal(5,2) NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'supplier_owner_user_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'notes', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'erp_id', 'varchar(64) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'erp_sku_code', 'varchar(32) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'vat_code', 'varchar(32) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'address_line_1', 'varchar(255) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'address_region', 'varchar(100) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'address_city', 'varchar(100) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'address_postal_code', 'varchar(30) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'tel_1', 'varchar(30) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'tel_2', 'varchar(30) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'fax', 'varchar(30) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'corporate_url', 'varchar(190) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'corporate_email', 'varchar(190) DEFAULT NULL'); } function aieo_dmm_create_supplier_preorders_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_supplier_preorders'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `supplier_term_id` bigint unsigned DEFAULT NULL, `brand_term_id` bigint unsigned DEFAULT NULL, `product_id` bigint unsigned NOT NULL, `branch_id` bigint unsigned NOT NULL, `scanned_user_id` bigint unsigned NOT NULL DEFAULT 0, `requested_qty` int NOT NULL DEFAULT 0, `prior_existing` int DEFAULT NULL, `fulfilled` tinyint(1) NOT NULL DEFAULT 0, `fulfilled_at` datetime DEFAULT NULL, `fulfilled_qty` int DEFAULT NULL, `status` varchar(20) NOT NULL DEFAULT 'requested', `approved_by` bigint unsigned DEFAULT NULL, `approved_at` datetime DEFAULT NULL, `ordered_at` datetime DEFAULT NULL, `received_at` datetime DEFAULT NULL, `received_qty` int DEFAULT NULL, `approver_note` text DEFAULT NULL, `notes` text DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `aieo_dmm_idx_pr_supplier` (`supplier_term_id`, `fulfilled`, `created_at`), KEY `aieo_dmm_idx_pr_branch_product` (`branch_id`, `product_id`), KEY `aieo_dmm_idx_pr_product` (`product_id`, `created_at`), KEY `aieo_dmm_idx_pr_status` (`status`, `supplier_term_id`, `created_at`) ) {$charset_collate};"; $wpdb->query($sql); aieo_dmm_ensure_column($table, 'status', "varchar(20) NOT NULL DEFAULT 'requested'"); aieo_dmm_ensure_column($table, 'approved_by', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'approved_at', 'datetime DEFAULT NULL'); aieo_dmm_ensure_column($table, 'ordered_at', 'datetime DEFAULT NULL'); aieo_dmm_ensure_column($table, 'received_at', 'datetime DEFAULT NULL'); aieo_dmm_ensure_column($table, 'received_qty', 'int DEFAULT NULL'); aieo_dmm_ensure_column($table, 'approver_note', 'text DEFAULT NULL'); aieo_dmm_ensure_column($table, 'batch_ref', 'varchar(32) DEFAULT NULL'); aieo_dmm_ensure_index($table, 'aieo_dmm_idx_pr_batch', '(`batch_ref`)'); } function aieo_dmm_create_preorder_worksheet_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_preorder_worksheet'; $cc = aieo_get_charset_collate(); $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `branch_id` bigint unsigned NOT NULL, `product_id` bigint unsigned NOT NULL, `existing_stock` int DEFAULT NULL, `desired_qty` int DEFAULT NULL, `last_request_id` bigint unsigned DEFAULT NULL, `last_submitted_at` datetime DEFAULT NULL, `updated_by` bigint unsigned NOT NULL DEFAULT 0, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_pw_branch_product` (`branch_id`, `product_id`), KEY `aieo_dmm_idx_pw_pending` (`branch_id`, `desired_qty`) ) {$cc};"); } function aieo_dmm_create_preorder_log_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_preorder_log'; $cc = aieo_get_charset_collate(); $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `preorder_id` bigint unsigned NOT NULL, `created_at` datetime NOT NULL, `user_id` bigint unsigned NOT NULL DEFAULT 0, `source` varchar(32) NOT NULL DEFAULT 'queue', `from_status` varchar(20) DEFAULT NULL, `to_status` varchar(20) DEFAULT NULL, `qty_before` int DEFAULT NULL, `qty_after` int DEFAULT NULL, `note` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `aieo_dmm_idx_pl_preorder` (`preorder_id`, `created_at`), KEY `aieo_dmm_idx_pl_created` (`created_at`) ) {$cc};"); } function aieo_dmm_create_store_pipeline_tables() { global $wpdb; $cc = aieo_get_charset_collate(); $p = $wpdb->prefix; $branch_cols = ''; foreach (array('total','eshop_soft1','eshop_woo') as $b) { $branch_cols .= "`{$b}_30d` int DEFAULT 0,\n `{$b}_60d` int DEFAULT 0,\n `trend_of_{$b}` decimal(10,2) DEFAULT NULL,\n "; } $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_store_sales ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `product_id` bigint DEFAULT NULL, `image_url` varchar(500) DEFAULT NULL, `supplier` varchar(255) DEFAULT NULL, `brand` varchar(255) DEFAULT NULL, `sku` varchar(255) DEFAULT NULL, `sku_norm` varchar(255) DEFAULT NULL, `gtin` varchar(255) DEFAULT NULL, {$branch_cols}`created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_product_id_int` (`product_id`), KEY `idx_sku_norm` (`sku_norm`) ) {$cc};"); $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_preorder_vitals ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `product_id` bigint DEFAULT NULL, `parent_id` bigint DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `image_url` varchar(500) DEFAULT NULL, `parent_image_url` varchar(500) DEFAULT NULL, `brand` varchar(255) DEFAULT NULL, `menu_category` varchar(255) DEFAULT NULL, `head_category` varchar(255) DEFAULT NULL, `sub_category` varchar(255) DEFAULT NULL, `supplier_id` bigint DEFAULT NULL, `supplier` varchar(255) DEFAULT NULL, `sku` varchar(255) DEFAULT NULL, `parent_sku` varchar(255) DEFAULT NULL, `gtin` varchar(255) DEFAULT NULL, `vat` varchar(32) DEFAULT NULL, `sku_norm` varchar(255) DEFAULT NULL, `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_product_id` (`product_id`), KEY `idx_sku_norm` (`sku_norm`), KEY `idx_sku` (`sku`) ) {$cc};"); aieo_dmm_ensure_index("{$p}aieo_dmm_preorder_vitals", 'idx_sku', '(`sku`)'); $stat_cols = ''; foreach (array('total','eshop_soft1','eshop_woo') as $b) { $stat_cols .= "`{$b}_30d` int DEFAULT 0,\n `{$b}_60d` int DEFAULT 0,\n `trend_of_{$b}` decimal(10,2) DEFAULT NULL,\n "; } $perbranch = ''; $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_preorder_vitals_stats ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `created` datetime DEFAULT NULL, `modified` datetime DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `snapshot_date` date DEFAULT NULL, `product_id` bigint DEFAULT NULL, `parent_id` bigint DEFAULT NULL, `image_url` varchar(500) DEFAULT NULL, `parent_image_url` varchar(500) DEFAULT NULL, `brand` varchar(255) DEFAULT NULL, `menu_category` varchar(255) DEFAULT NULL, `head_category` varchar(255) DEFAULT NULL, `sub_category` varchar(255) DEFAULT NULL, `supplier_id` bigint DEFAULT NULL, `supplier` varchar(255) DEFAULT NULL, `sku` varchar(255) DEFAULT NULL, `parent_sku` varchar(255) DEFAULT NULL, `gtin` varchar(255) DEFAULT NULL, `vat` varchar(32) DEFAULT NULL, `sku_norm` varchar(255) DEFAULT NULL, {$stat_cols}{$perbranch}PRIMARY KEY (`id`), KEY `idx_product_id` (`product_id`), KEY `idx_snapshot` (`snapshot_date`), KEY `idx_sku_norm` (`sku_norm`) ) {$cc};"); $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_supplier_sku_map ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `sku` varchar(255) NOT NULL, `sku_norm` varchar(255) DEFAULT NULL, `product_id` bigint DEFAULT NULL, `supplier_term_id` bigint unsigned DEFAULT NULL, `supplier_erp_id` varchar(64) DEFAULT NULL, `supplier_name` varchar(255) DEFAULT NULL, `source` varchar(20) NOT NULL DEFAULT 'erp', `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_sku` (`sku`), KEY `idx_sku_norm` (`sku_norm`), KEY `idx_product` (`product_id`), KEY `idx_supplier` (`supplier_term_id`) ) {$cc};"); } function aieo_dmm_create_branch_stats_tables() { global $wpdb; $cc = aieo_get_charset_collate(); $p = $wpdb->prefix; $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_branch_sales ( `branch_id` bigint unsigned NOT NULL, `sku_norm` varchar(255) NOT NULL, `product_id` bigint DEFAULT NULL, `sales_30d` int NOT NULL DEFAULT 0, `sales_60d` int NOT NULL DEFAULT 0, `trend` decimal(10,2) DEFAULT NULL, `snapshot_date` date DEFAULT NULL, PRIMARY KEY (`branch_id`, `sku_norm`), KEY `idx_bs_product` (`product_id`) ) {$cc};"); $wpdb->query("CREATE TABLE IF NOT EXISTS {$p}aieo_dmm_branch_phys ( `scope` varchar(12) NOT NULL, `ref_id` bigint unsigned NOT NULL, `branch_id` bigint unsigned NOT NULL, `units` decimal(12,3) NOT NULL DEFAULT 0, `units_recent` decimal(12,3) NOT NULL DEFAULT 0, PRIMARY KEY (`scope`, `ref_id`, `branch_id`), KEY `idx_bp_branch` (`branch_id`) ) {$cc};"); } function aieo_dmm_migrate_branch_wide_to_narrow() { global $wpdb; $p = $wpdb->prefix; $bp = $p . 'aieo_dmm_branch_phys'; $reg = $p . 'aieo_dmm_branches'; if (!aieo_dmm_table_exists($bp) || !aieo_dmm_table_exists($reg)) { return; } $col_exists = static function ($table, $col) use ($wpdb) { return (int) $wpdb->get_var($wpdb->prepare( 'SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = %s AND column_name = %s', $table, $col )) > 0; }; $branches = (array) $wpdb->get_results("SELECT id, slug FROM {$reg}", ARRAY_A); $cand = $p . 'aieo_dmm_stock_recovery_candidates'; $gap = $p . 'aieo_dmm_catalog_gap_candidates'; $ev = $p . 'aieo_dmm_stock_recovery_events'; foreach ($branches as $b) { $slug = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $b['slug'])); $bid = (int) $b['id']; if ($slug === '' || $bid <= 0) { continue; } if (aieo_dmm_table_exists($ev) && $col_exists($ev, 'phys_' . $slug . '_recent')) { $wpdb->query("INSERT IGNORE INTO {$bp} (scope, ref_id, branch_id, units, units_recent) SELECT 'event', e.id, {$bid}, 0, e.`phys_{$slug}_recent` FROM {$ev} e WHERE COALESCE(e.`phys_{$slug}_recent`, 0) <> 0"); } if (aieo_dmm_table_exists($cand) && $col_exists($cand, 'phys_' . $slug)) { $wpdb->query("INSERT IGNORE INTO {$bp} (scope, ref_id, branch_id, units, units_recent) SELECT 'recovery', c.id, {$bid}, COALESCE(c.`phys_{$slug}`, 0), COALESCE(c.`phys_{$slug}_recent`, 0) FROM {$cand} c WHERE COALESCE(c.`phys_{$slug}`, 0) <> 0 OR COALESCE(c.`phys_{$slug}_recent`, 0) <> 0"); } if (aieo_dmm_table_exists($gap) && $col_exists($gap, 'phys_' . $slug)) { $wpdb->query("INSERT IGNORE INTO {$bp} (scope, ref_id, branch_id, units, units_recent) SELECT 'gap', g.id, {$bid}, COALESCE(g.`phys_{$slug}`, 0), 0 FROM {$gap} g WHERE COALESCE(g.`phys_{$slug}`, 0) <> 0"); } } $legacy_slugs = array('piraeus', 'keratsini', 'korydallos', 'kallithea'); foreach ($branches as $b) { $s = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $b['slug'])); if ($s !== '' && !in_array($s, $legacy_slugs, true)) { $legacy_slugs[] = $s; } } $legacy_slugs = array_values(array_diff($legacy_slugs, array('total', 'eshop_soft1', 'eshop_woo', 'eshop'))); $sales = $p . 'aieo_dmm_store_sales'; $stats = $p . 'aieo_dmm_preorder_vitals_stats'; foreach ($legacy_slugs as $s) { foreach (array( array($sales, array($s . '_30d', $s . '_60d', 'trend_of_' . $s)), array($stats, array($s . '_30d', $s . '_60d', 'trend_of_' . $s, 'existing_stock_' . $s, 'desired_qty_' . $s, 'last_pending_id_' . $s)), array($cand, array('phys_' . $s, 'phys_' . $s . '_recent')), array($gap, array('phys_' . $s)), array($ev, array('phys_' . $s . '_recent')), ) as $spec) { list($table, $cols) = $spec; if (!aieo_dmm_table_exists($table)) { continue; } $drops = array(); foreach ($cols as $c) { if ($col_exists($table, $c)) { $drops[] = "DROP COLUMN `{$c}`"; } } if (!empty($drops)) { $wpdb->query("ALTER TABLE {$table} " . implode(', ', $drops)); } } } } function aieo_dmm_backfill_legacy_branch_matchers() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_branches'; if (!aieo_dmm_table_exists($table)) { return; } $legacy_patterns = array( 'piraeus' => 'ΠΕΙΡΑΙΑΣ', 'keratsini' => 'ΚΕΡΑΤΣΙΝΙ', 'korydallos' => 'ΚΟΡΥΔΑΛΛΟΣ|ΝΙΚΑΙΑ', 'kallithea' => 'ΚΑΛΛΙΘΕΑ', ); $seen_legacy = false; foreach ($legacy_patterns as $slug => $patterns) { $updated = $wpdb->query($wpdb->prepare( "UPDATE {$table} SET match_patterns = %s WHERE slug = %s AND (match_patterns IS NULL OR match_patterns = '')", $patterns, $slug )); $has_row = $updated || $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE slug = %s LIMIT 1", $slug)); if ($has_row) { $seen_legacy = true; } } if ($seen_legacy && false === get_option('aieo_dmm_erp_eshop_invoice_type', false)) { add_option('aieo_dmm_erp_eshop_invoice_type', '7069', '', false); } if (!class_exists('AIEO_DMM_Branches')) { $f = AIEO_DIR . 'includes/dmm/services/class-aieo-dmm-branches.php'; if (is_readable($f)) { require_once $f; } } if (class_exists('AIEO_DMM_Branches')) { AIEO_DMM_Branches::sync(); } } function aieo_dmm_create_physical_recovery_tables() { global $wpdb; $cc = aieo_get_charset_collate(); $p = $wpdb->prefix; $pb = ''; $pbr = ''; $cand = $p . 'aieo_dmm_stock_recovery_candidates'; $wpdb->query("CREATE TABLE IF NOT EXISTS {$cand} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `product_id` bigint DEFAULT NULL, `parent_id` bigint DEFAULT NULL, `is_variation` tinyint(1) DEFAULT 0, `sku` varchar(255) DEFAULT NULL, `sku_norm` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `image_url` varchar(500) DEFAULT NULL, `oos_since` date DEFAULT NULL, `oos_source` varchar(16) DEFAULT NULL, `days_oos` int DEFAULT NULL, `eshop_stock` int DEFAULT NULL, `phys_units_after` decimal(12,3) DEFAULT 0, `phys_units_recent` decimal(12,3) DEFAULT 0, `phys_sale_days` int DEFAULT 0, `last_phys_sale_date` date DEFAULT NULL, `phys_turnover_after` decimal(14,2) DEFAULT 0, `phys_turnover_recent` decimal(14,2) DEFAULT 0, `eshop_price` decimal(20,2) DEFAULT NULL, `est_lost_sales` decimal(14,2) DEFAULT 0, `lost_optimistic` decimal(14,2) DEFAULT NULL, `lost_pessimistic` decimal(14,2) DEFAULT NULL, `lost_average` decimal(14,2) DEFAULT NULL, {$pb}{$pbr}`phys_basket_turnover_recent` decimal(16,2) DEFAULT 0, `phys_basket_orders_recent` int DEFAULT 0, `phys_branch_count` tinyint DEFAULT 0, `velocity_per_day` decimal(12,4) DEFAULT 0, `suggested_restock_qty` int DEFAULT 0, `meets_threshold` tinyint(1) DEFAULT 0, `confidence_score` decimal(5,2) DEFAULT NULL, `blocked_reason` varchar(32) DEFAULT NULL, `status` varchar(16) DEFAULT 'pending', `last_event_id` bigint unsigned DEFAULT NULL, `computed_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_product` (`product_id`), KEY `idx_meets_status` (`meets_threshold`,`status`), KEY `idx_last_sale` (`last_phys_sale_date`), KEY `idx_sku_norm` (`sku_norm`) ) {$cc};"); $gap = $p . 'aieo_dmm_catalog_gap_candidates'; $wpdb->query("CREATE TABLE IF NOT EXISTS {$gap} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `product_id` bigint DEFAULT NULL, `sku` varchar(255) DEFAULT NULL, `sku_norm` varchar(255) NOT NULL, `name` varchar(255) DEFAULT NULL, `supplier_name` varchar(255) DEFAULT NULL, `barcode` varchar(255) DEFAULT NULL, `post_status` varchar(20) DEFAULT NULL, `product_type` varchar(32) DEFAULT NULL, `phys_units` decimal(12,3) DEFAULT 0, `phys_units_recent` decimal(12,3) DEFAULT 0, `phys_sale_days` int DEFAULT 0, `last_phys_sale_date` date DEFAULT NULL, `phys_turnover` decimal(14,2) DEFAULT 0, `phys_units_30d` decimal(12,3) DEFAULT 0, `phys_turnover_30d` decimal(14,2) DEFAULT 0, {$pb}`velocity_per_day` decimal(12,4) DEFAULT 0, `priority_score` decimal(14,2) DEFAULT 0, `missing_image` tinyint(1) DEFAULT 0, `missing_desc` tinyint(1) DEFAULT 0, `missing_cats` tinyint(1) DEFAULT 0, `missing_brand` tinyint(1) DEFAULT 0, `needs_creation` tinyint(1) DEFAULT 0, `status` varchar(16) DEFAULT 'pending', `computed_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_sku_norm` (`sku_norm`), KEY `idx_priority` (`priority_score`), KEY `idx_status` (`status`) ) {$cc};"); $ev = $p . 'aieo_dmm_stock_recovery_events'; $wpdb->query("CREATE TABLE IF NOT EXISTS {$ev} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `product_id` bigint DEFAULT NULL, `parent_id` bigint DEFAULT NULL, `sku` varchar(255) DEFAULT NULL, `mode` varchar(12) DEFAULT 'manual', `applied_by` bigint unsigned DEFAULT NULL, `applied_at` datetime DEFAULT NULL, `oos_since_at_apply` date DEFAULT NULL, `days_oos_at_apply` int DEFAULT NULL, `phys_units_after_at_apply` decimal(12,3) DEFAULT NULL, `phys_sale_days_at_apply` int DEFAULT NULL, `phys_turnover_recent_at_apply` decimal(14,2) DEFAULT NULL, `phys_turnover_after_at_apply` decimal(14,2) DEFAULT NULL, `phys_basket_turnover_at_apply` decimal(16,2) DEFAULT NULL, `restock_qty` int DEFAULT NULL, `prior_stock` int DEFAULT NULL, `prior_stock_status` varchar(16) DEFAULT NULL, `prior_manage_stock` tinyint(1) DEFAULT NULL, `thresholds_json` longtext DEFAULT NULL, `outcome` varchar(16) DEFAULT 'pending', `outcome_reason` varchar(32) DEFAULT NULL, `eshop_units_recovered` decimal(12,3) DEFAULT 0, `eshop_turnover_recovered` decimal(14,2) DEFAULT 0, `eshop_orders_recovered` int DEFAULT 0, `eshop_basket_turnover` decimal(16,2) DEFAULT 0, `eshop_units_draft` decimal(12,3) DEFAULT 0, `eshop_turnover_draft` decimal(14,2) DEFAULT 0, `eshop_orders_draft` int DEFAULT 0, `eshop_basket_draft` decimal(16,2) DEFAULT 0, `failed_units` decimal(12,3) DEFAULT 0, `reverted_at` datetime DEFAULT NULL, `outcome_evaluated_at` datetime DEFAULT NULL, `created_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_product_applied` (`product_id`,`applied_at`), KEY `idx_outcome` (`outcome`), KEY `idx_mode` (`mode`) ) {$cc};"); $ge = $p . 'aieo_dmm_catalog_gap_events'; $wpdb->query("CREATE TABLE IF NOT EXISTS {$ge} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `product_id` bigint DEFAULT NULL, `sku` varchar(255) DEFAULT NULL, `sku_norm` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `published_at` datetime DEFAULT NULL, `published_by` bigint unsigned DEFAULT NULL, `was_needs_creation` tinyint(1) DEFAULT 0, `post_status_before` varchar(20) DEFAULT NULL, `phys_units_recent_at_publish` decimal(12,3) DEFAULT NULL, `phys_turnover_recent_at_publish` decimal(14,2) DEFAULT NULL, `phys_turnover_at_publish` decimal(14,2) DEFAULT NULL, `eshop_units_since` decimal(12,3) DEFAULT 0, `eshop_turnover_since` decimal(14,2) DEFAULT 0, `eshop_orders_since` int DEFAULT 0, `unpublished_at` datetime DEFAULT NULL, `outcome_evaluated_at` datetime DEFAULT NULL, `created_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_product` (`product_id`), KEY `idx_sku_norm` (`sku_norm`), KEY `idx_published` (`published_at`) ) {$cc};"); aieo_dmm_ensure_column($ev, 'eshop_orders_recovered', 'int DEFAULT 0'); aieo_dmm_ensure_column($ev, 'failed_units', 'decimal(12,3) DEFAULT 0'); aieo_dmm_ensure_column($ev, 'outcome_reason', "varchar(32) DEFAULT NULL"); aieo_dmm_ensure_column($ev, 'phys_turnover_recent_at_apply', 'decimal(14,2) DEFAULT NULL'); aieo_dmm_ensure_column($ev, 'phys_turnover_after_at_apply', 'decimal(14,2) DEFAULT NULL'); aieo_dmm_ensure_column($ev, 'phys_basket_turnover_at_apply', 'decimal(16,2) DEFAULT NULL'); aieo_dmm_ensure_column($ev, 'eshop_basket_turnover', 'decimal(16,2) DEFAULT 0'); aieo_dmm_ensure_column($ev, 'eshop_units_draft', 'decimal(12,3) DEFAULT 0'); aieo_dmm_ensure_column($ev, 'eshop_turnover_draft', 'decimal(14,2) DEFAULT 0'); aieo_dmm_ensure_column($ev, 'eshop_orders_draft', 'int DEFAULT 0'); aieo_dmm_ensure_column($ev, 'eshop_basket_draft', 'decimal(16,2) DEFAULT 0'); aieo_dmm_ensure_column($gap, 'phys_units_30d', 'decimal(12,3) DEFAULT 0'); aieo_dmm_ensure_column($gap, 'phys_turnover_30d', 'decimal(14,2) DEFAULT 0'); aieo_dmm_ensure_column($cand, 'phys_basket_turnover_recent', 'decimal(16,2) DEFAULT 0'); aieo_dmm_ensure_column($cand, 'phys_basket_orders_recent', 'int DEFAULT 0'); aieo_dmm_ensure_column($cand, 'confidence_score', 'decimal(5,2) DEFAULT NULL'); aieo_dmm_ensure_column($cand, 'blocked_reason', 'varchar(32) DEFAULT NULL'); aieo_dmm_ensure_column($cand, 'phys_turnover_after', 'decimal(14,2) DEFAULT 0'); aieo_dmm_ensure_column($cand, 'phys_turnover_recent', 'decimal(14,2) DEFAULT 0'); aieo_dmm_ensure_column($cand, 'eshop_price', 'decimal(20,2) DEFAULT NULL'); aieo_dmm_ensure_column($cand, 'est_lost_sales', 'decimal(14,2) DEFAULT NULL'); aieo_dmm_ensure_column($cand, 'lost_optimistic', 'decimal(14,2) DEFAULT NULL'); aieo_dmm_ensure_column($cand, 'lost_pessimistic', 'decimal(14,2) DEFAULT NULL'); aieo_dmm_ensure_column($cand, 'lost_average', 'decimal(14,2) DEFAULT NULL'); } function aieo_dmm_ensure_store_board($slug) { global $wpdb; $slug = preg_replace('/[^a-z0-9_]/', '', strtolower((string) $slug)); if ($slug === '') { return false; } $table = $wpdb->prefix . 'store_board_' . $slug; $cc = aieo_get_charset_collate(); $cols = array( 'product_id' => 'bigint unsigned NOT NULL', 'parent_id' => 'int DEFAULT NULL', 'image_url' => 'varchar(500) DEFAULT NULL', 'parent_image_url' => 'varchar(500) DEFAULT NULL', 'brand' => 'varchar(255) DEFAULT NULL', 'product_name' => 'varchar(255) DEFAULT NULL', 'supplier' => 'varchar(255) DEFAULT NULL', 'menu_category' => 'varchar(255) DEFAULT NULL', 'head_category' => 'varchar(255) DEFAULT NULL', 'sub_category' => 'varchar(255) DEFAULT NULL', 'sku' => 'varchar(255) DEFAULT NULL', 'parent_sku' => 'varchar(255) DEFAULT NULL', 'gtin' => 'varchar(255) DEFAULT NULL', 'barcode_index' => 'longtext DEFAULT NULL', 'sales_30d' => 'int DEFAULT NULL', 'sales_60d' => 'int DEFAULT NULL', 'sales_trend' => 'decimal(10,2) DEFAULT NULL', 'existing_stock' => 'int DEFAULT NULL', 'desired_qty' => 'int DEFAULT NULL', 'latest_desired_qty' => 'int DEFAULT NULL', 'latest_operator_modification' => 'datetime DEFAULT NULL', 'received_qty' => 'int DEFAULT NULL', 'supplier_preorder_id' => 'int DEFAULT NULL', 'snapshot_date' => 'date DEFAULT NULL', 'created' => 'datetime NOT NULL', 'modified' => 'datetime NOT NULL', 'supplier_id' => 'int DEFAULT NULL', 'status' => 'tinyint unsigned DEFAULT NULL', 'logger' => 'text DEFAULT NULL', 'logger_seq' => 'int unsigned NOT NULL DEFAULT 0', 'logger_last_user' => 'varchar(191) DEFAULT NULL', 'logger_last_device' => 'varchar(64) DEFAULT NULL', 'status_name' => 'varchar(32) DEFAULT NULL', 'origin_type' => 'varchar(32) DEFAULT NULL', 'origin_user' => 'varchar(191) DEFAULT NULL', 'status_id' => 'tinyint unsigned DEFAULT NULL', 'sku_norm' => 'varchar(255) DEFAULT NULL', ); $defs = ''; foreach ($cols as $name => $def) { $defs .= "`{$name}` {$def},\n "; } $wpdb->query("CREATE TABLE IF NOT EXISTS `{$table}` ( {$defs}PRIMARY KEY (`product_id`), KEY `idx_brand` (`brand`), KEY `idx_sku` (`sku`), KEY `idx_gtin` (`gtin`), KEY `idx_snapshot` (`snapshot_date`), KEY `idx_supplier_id` (`supplier_id`), KEY `idx_status_id` (`status_id`) ) {$cc};"); foreach ($cols as $name => $def) { if ($name === 'product_id') { continue; } aieo_dmm_ensure_column($table, $name, $def); } return $table; } function aieo_dmm_create_store_order_log_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_store_order_log'; $cc = aieo_get_charset_collate(); $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `name` varchar(191) DEFAULT NULL, `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `modified` datetime DEFAULT NULL, `product_id` bigint DEFAULT NULL, `branch_id` bigint unsigned DEFAULT NULL, `board_slug` varchar(64) DEFAULT NULL, `user_id` bigint unsigned DEFAULT NULL, `existing_stock` int DEFAULT NULL, `desired_qty` int DEFAULT NULL, `status_id` tinyint unsigned DEFAULT NULL, `origin_type` varchar(32) DEFAULT NULL, `origin_user` varchar(191) DEFAULT NULL, `origin_device` varchar(64) DEFAULT NULL, `sales_trend` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_product` (`product_id`, `created`), KEY `idx_branch` (`branch_id`, `created`) ) {$cc};"); } function aieo_dmm_ensure_store_status_lookup() { global $wpdb; $table = $wpdb->prefix . 'store_order_status_lookup'; $cc = aieo_get_charset_collate(); $wpdb->query("CREATE TABLE IF NOT EXISTS {$table} ( `status_id` tinyint unsigned NOT NULL, `status_name` varchar(64) NOT NULL, `is_final` tinyint(1) NOT NULL DEFAULT 0, `sort_order` int NOT NULL DEFAULT 0, PRIMARY KEY (`status_id`) ) {$cc};"); $n = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}"); if ($n === 0) { $wpdb->query("INSERT INTO {$table} (status_id, status_name, is_final, sort_order) VALUES (0, 'Ανοιχτή', 0, 1), (1, 'Ολοκληρωμένη', 1, 2), (2, 'Παραλήφθηκε', 1, 3)"); } } function aieo_dmm_ensure_store_board_triggers($slug, $branch_id = 0) { global $wpdb; $slug = preg_replace("/[^a-z0-9_]/", "", strtolower((string) $slug)); if ($slug === "") { return; } foreach (array("logger_before", "logger_after", "preorder_upsert") as $which) { $wpdb->query("DROP TRIGGER IF EXISTS `trg_{$slug}_{$which}`"); } } function aieo_dmm_ensure_all_store_boards() { global $wpdb; $reg = $wpdb->prefix . 'aieo_dmm_branches'; if (!aieo_dmm_table_exists($reg)) { return; } aieo_dmm_ensure_store_status_lookup(); $rows = $wpdb->get_results("SELECT id, slug FROM {$reg} WHERE enabled = 1", ARRAY_A); foreach ((array) $rows as $r) { $slug = (string) $r['slug']; if (aieo_dmm_ensure_store_board($slug)) { aieo_dmm_ensure_store_board_triggers($slug, (int) $r['id']); } } } function aieo_dmm_create_term_relations_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_term_relations'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `source_taxonomy` varchar(64) NOT NULL, `source_term_id` bigint unsigned NOT NULL, `target_kind` varchar(8) NOT NULL, `target_subtype` varchar(64) NOT NULL, `target_id` bigint unsigned NOT NULL, `rel_type` varchar(20) NOT NULL DEFAULT 'related', `weight` int NOT NULL DEFAULT 0, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_tr_edge` (`source_taxonomy`, `source_term_id`, `target_kind`, `target_subtype`, `target_id`, `rel_type`), KEY `aieo_dmm_idx_tr_src` (`source_taxonomy`, `source_term_id`, `target_kind`, `target_subtype`), KEY `aieo_dmm_idx_tr_tgt` (`target_kind`, `target_subtype`, `target_id`, `source_taxonomy`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_create_swatches_variation_map_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_swatches_variation_map'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `variation_id` bigint unsigned NOT NULL, `product_id` bigint unsigned NOT NULL, `attribute_combo_hash` char(40) NOT NULL DEFAULT '', `image_url` varchar(512) DEFAULT NULL, `swatch_image_url` varchar(512) DEFAULT NULL, `sku` varchar(100) DEFAULT NULL, `stock_status` varchar(20) DEFAULT NULL, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`variation_id`) ) {$charset_collate};"; $wpdb->query($sql); $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$table}" ); if ( is_array( $cols ) && ! in_array( 'swatch_image_url', $cols, true ) ) { $wpdb->query( "ALTER TABLE {$table} ADD COLUMN `swatch_image_url` varchar(512) DEFAULT NULL AFTER `image_url`" ); } aieo_create_index_if_not_exists('aieo_dmm_idx_swmap_prod', $table, '(product_id)'); aieo_create_index_if_not_exists('aieo_dmm_idx_swmap_hash', $table, '(attribute_combo_hash)'); } function aieo_dmm_create_cancellation_reasons_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_cancellation_reasons'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `slug` varchar(64) NOT NULL, `label` varchar(190) NOT NULL, `is_customer_visible` tinyint NOT NULL DEFAULT 1, `default_event_kind` tinyint DEFAULT NULL, `default_instigator` varchar(10) NOT NULL DEFAULT '', `default_frame` varchar(20) NOT NULL DEFAULT '', `sort_order` int NOT NULL DEFAULT 0, `enabled` tinyint NOT NULL DEFAULT 1, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_cr_slug` (`slug`) ) {$charset_collate};"; $wpdb->query($sql); aieo_dmm_ensure_column($table, 'default_event_kind', 'tinyint DEFAULT NULL'); aieo_dmm_ensure_column($table, 'default_instigator', "varchar(10) NOT NULL DEFAULT ''"); aieo_dmm_ensure_column($table, 'default_frame', "varchar(20) NOT NULL DEFAULT ''"); } function aieo_dmm_seed_default_cancellation_reasons() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_cancellation_reasons'; $defaults = aieo_dmm_cancellation_reason_defaults(); foreach ($defaults as $row) { list($slug, $label, $visible, $sort, $event_kind, $instigator, $frame) = $row; if ($event_kind === null) { $wpdb->query($wpdb->prepare( "INSERT IGNORE INTO {$table} (slug, label, is_customer_visible, sort_order, enabled, default_event_kind, default_instigator, default_frame) VALUES (%s, %s, %d, %d, 1, NULL, %s, %s)", $slug, $label, (int) $visible, (int) $sort, $instigator, $frame )); } else { $wpdb->query($wpdb->prepare( "INSERT IGNORE INTO {$table} (slug, label, is_customer_visible, sort_order, enabled, default_event_kind, default_instigator, default_frame) VALUES (%s, %s, %d, %d, 1, %d, %s, %s)", $slug, $label, (int) $visible, (int) $sort, (int) $event_kind, $instigator, $frame )); } } foreach ($defaults as $row) { list($slug, , , , , $instigator, $frame) = $row; if ($instigator !== '') { $wpdb->query($wpdb->prepare( "UPDATE {$table} SET default_instigator = %s WHERE slug = %s AND (default_instigator IS NULL OR default_instigator = '')", $instigator, $slug )); } if ($frame !== '') { $wpdb->query($wpdb->prepare( "UPDATE {$table} SET default_frame = %s WHERE slug = %s AND (default_frame IS NULL OR default_frame = '')", $frame, $slug )); } } if (get_option('aieo_dmm_cr_instigator_v2') !== 'done') { $corrections = array( array('payment_failed', 'system', 'customer'), array('address_unreachable', 'shop', 'customer'), array('duplicate_order', 'shop', 'customer'), array('fraud_suspected', 'shop', 'customer'), array('quality_issue', 'customer', 'shop'), array('wrong_item_sent', 'customer', 'shop'), array('late_delivery_returned', 'customer', 'shop'), array('customer_kept_refunded', 'customer', 'shop'), array('damaged_in_transit_kept', 'customer', 'shop'), array('not_received_claim', 'customer', 'shop'), ); foreach ($corrections as $c) { $wpdb->query($wpdb->prepare( "UPDATE {$table} SET default_instigator = %s WHERE slug = %s AND default_instigator = %s", $c[2], $c[0], $c[1] )); } $wpdb->query("UPDATE {$table} SET default_instigator = 'customer' WHERE default_instigator = 'system'"); update_option('aieo_dmm_cr_instigator_v2', 'done', false); } $kind_defaults = array( 'out_of_stock' => 0, 'customer_changed_mind' => 0, 'payment_failed' => 0, 'fraud_suspected' => 0, 'duplicate_order' => 0, 'quality_issue' => 1, 'wrong_item_sent' => 1, 'wrong_size' => 1, 'late_delivery' => 1, 'late_delivery_returned' => 1, 'customer_returned' => 1, 'customer_kept_refunded' => 2, 'damaged_in_transit_kept' => 2, 'address_unreachable' => 3, 'refused_on_arrival' => 3, 'never_collected_from_pickup' => 3, 'not_received_claim' => 4, 'lost_in_transit' => 4, ); foreach ($kind_defaults as $slug => $kind) { $wpdb->query($wpdb->prepare( "UPDATE {$table} SET default_event_kind = %d WHERE slug = %s AND default_event_kind IS NULL", (int) $kind, $slug )); } if (class_exists('AIEO_DMM_Cancellation_Reasons_Data') && method_exists('AIEO_DMM_Cancellation_Reasons_Data', 'flush_cache')) { AIEO_DMM_Cancellation_Reasons_Data::flush_cache(); } wp_cache_delete('aieo_cr_all', 'aieo_dmm'); } function aieo_dmm_cancellation_reason_defaults() { return array( array('out_of_stock', 'Out of stock', 1, 10, 0, 'shop', 'none'), array('duplicate_order', 'Duplicate order', 1, 60, 0, 'customer', 'none'), array('fraud_suspected', 'Suspected fraud', 0, 50, 0, 'customer', 'none'), array('payment_failed', 'Payment failed', 1, 30, 0, 'customer', 'none'), array('customer_changed_mind', 'Customer changed mind (before shipping)', 1, 20, 0, 'customer', 'withdrawal'), array('quality_issue', 'Quality issue (returned after receipt)', 1, 70, 1, 'shop', 'faulty_goods'), array('wrong_item_sent', 'Wrong item sent (returned after receipt)', 1, 75, 1, 'shop', 'faulty_goods'), array('wrong_size', 'Wrong size / fit (returned after receipt)', 1, 80, 1, 'customer', 'withdrawal'), array('late_delivery_returned', 'Late delivery (returned after receipt)', 1, 90, 1, 'shop', 'withdrawal'), array('customer_returned', 'Customer returned the items (after receipt)', 1, 95, 1, 'customer', 'withdrawal'), array('customer_kept_refunded', 'Refunded but customer kept items', 0, 110, 2, 'shop', 'faulty_goods'), array('damaged_in_transit_kept', 'Damaged in transit (customer kept + refunded)', 0, 115, 2, 'shop', 'faulty_goods'), array('refused_on_arrival', 'Refused on arrival (never opened)', 0, 130, 3, 'customer', 'withdrawal'), array('never_collected_from_pickup', 'Never collected from pickup point', 0, 140, 3, 'customer', 'withdrawal'), array('address_unreachable', 'Address unreachable (never delivered)', 1, 40, 3, 'customer', 'none'), array('not_received_claim', 'Customer reports non-delivery (under investigation)', 1, 118, 4, 'shop', 'non_delivery'), array('lost_in_transit', 'Lost in transit (confirmed written off)', 0, 120, 4, 'shop', 'non_delivery'), array('other', 'Other (see notes)', 1, 100, null, '', ''), ); } function aieo_dmm_create_withdrawal_declarations_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_withdrawal_declarations'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_id` bigint unsigned NOT NULL, `user_id` bigint unsigned NOT NULL DEFAULT 0, `billing_email` varchar(190) NOT NULL DEFAULT '', `tier` varchar(20) NOT NULL DEFAULT 'declaration', `channel` varchar(20) NOT NULL DEFAULT 'account', `is_full` tinyint NOT NULL DEFAULT 1, `product_ids` longtext DEFAULT NULL, `item_ids` longtext DEFAULT NULL, `reason_slug` varchar(64) DEFAULT NULL, `reason_note` varchar(500) DEFAULT NULL, `order_total` decimal(12,2) NOT NULL DEFAULT '0.00', `declared_total` decimal(12,2) NOT NULL DEFAULT '0.00', `status` varchar(20) NOT NULL DEFAULT 'pending', `receipt_basis` varchar(20) NOT NULL DEFAULT '', `deadline_gmt` datetime DEFAULT NULL, `declared_at_gmt` datetime NOT NULL, `acknowledged_at_gmt` datetime DEFAULT NULL, `processed_at_gmt` datetime DEFAULT NULL, `processed_by_user_id` bigint unsigned NOT NULL DEFAULT 0, `admin_note` varchar(500) DEFAULT NULL, `client_ip_hash` char(32) DEFAULT NULL, `received_at_gmt` datetime DEFAULT NULL, `refund_amount` decimal(12,2) DEFAULT NULL, `refund_method` varchar(20) DEFAULT NULL, `refunded_at_gmt` datetime DEFAULT NULL, `return_carrier` varchar(32) DEFAULT NULL, `return_voucher_no` varchar(64) DEFAULT NULL, `return_voucher_at_gmt` datetime DEFAULT NULL, `return_voucher_error` varchar(255) DEFAULT NULL, `return_shipping_cost` decimal(12,2) DEFAULT NULL, `approved_at_gmt` datetime DEFAULT NULL, `approved_by_user_id` bigint unsigned NOT NULL DEFAULT 0, `return_pickup` longtext DEFAULT NULL, `return_label_path` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `aieo_dmm_idx_wd_order` (`order_id`, `declared_at_gmt`), KEY `aieo_dmm_idx_wd_status` (`status`, `declared_at_gmt`), KEY `aieo_dmm_idx_wd_user` (`user_id`, `declared_at_gmt`), KEY `aieo_dmm_idx_wd_email` (`billing_email`, `declared_at_gmt`), KEY `aieo_dmm_idx_wd_tier` (`tier`, `declared_at_gmt`) ) {$charset_collate};"; $wpdb->query($sql); if (function_exists('aieo_dmm_ensure_column')) { aieo_dmm_ensure_column($table, 'received_at_gmt', "datetime DEFAULT NULL"); aieo_dmm_ensure_column($table, 'refund_amount', "decimal(12,2) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'refunded_at_gmt', "datetime DEFAULT NULL"); aieo_dmm_ensure_column($table, 'refund_method', "varchar(20) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'return_carrier', "varchar(32) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'return_voucher_no', "varchar(64) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'return_voucher_at_gmt', "datetime DEFAULT NULL"); aieo_dmm_ensure_column($table, 'return_voucher_error', "varchar(255) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'return_shipping_cost', "decimal(12,2) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'approved_at_gmt', "datetime DEFAULT NULL"); aieo_dmm_ensure_column($table, 'approved_by_user_id', "bigint unsigned NOT NULL DEFAULT 0"); aieo_dmm_ensure_column($table, 'return_pickup', "longtext DEFAULT NULL"); aieo_dmm_ensure_column($table, 'return_label_path', "varchar(255) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'rejected_at_gmt', "datetime DEFAULT NULL"); aieo_dmm_ensure_column($table, 'rejected_by_user_id', "bigint unsigned NOT NULL DEFAULT 0"); aieo_dmm_ensure_column($table, 'rejection_reasons', "varchar(255) DEFAULT NULL"); aieo_dmm_ensure_column($table, 'rejection_note', "varchar(500) DEFAULT NULL"); } } function aieo_dmm_create_order_cancellations_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_order_cancellations'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_id` bigint unsigned NOT NULL, `user_id` bigint unsigned NOT NULL DEFAULT 0, `reason_slug` varchar(64) NOT NULL DEFAULT 'other', `reason_note` varchar(500) DEFAULT NULL, `status_before` varchar(32) NOT NULL DEFAULT '', `status_after` varchar(32) NOT NULL DEFAULT 'cancelled', `order_total` decimal(12,2) NOT NULL DEFAULT '0.00', `product_ids` longtext DEFAULT NULL, `is_full_cancel` tinyint NOT NULL DEFAULT 1, `entry_channel` varchar(40) DEFAULT NULL, `entry_campaign_id` bigint unsigned DEFAULT NULL, `entry_branch_id` bigint unsigned DEFAULT NULL, `event_kind` tinyint NOT NULL DEFAULT 0, `instigator` varchar(10) NOT NULL DEFAULT '', `frame` varchar(20) NOT NULL DEFAULT '', `outbound_shipping_cost` decimal(12,2) DEFAULT NULL, `return_shipping_cost` decimal(12,2) DEFAULT NULL, `cancelled_by_user_id` bigint unsigned NOT NULL DEFAULT 0, `cancelled_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_oc_order` (`order_id`), KEY `aieo_dmm_idx_oc_reason_time` (`reason_slug`, `cancelled_at`), KEY `aieo_dmm_idx_oc_user` (`user_id`, `cancelled_at`), KEY `aieo_dmm_idx_oc_campaign` (`entry_campaign_id`, `cancelled_at`), KEY `aieo_dmm_idx_oc_channel` (`entry_channel`, `cancelled_at`), KEY `aieo_dmm_idx_oc_branch` (`entry_branch_id`, `cancelled_at`), KEY `aieo_dmm_idx_oc_event` (`event_kind`, `cancelled_at`), KEY `aieo_dmm_idx_oc_instigator` (`instigator`, `cancelled_at`), KEY `aieo_dmm_idx_oc_frame` (`frame`, `cancelled_at`) ) {$charset_collate};"; $wpdb->query($sql); aieo_dmm_ensure_column($table, 'reason_note', 'varchar(500) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'product_ids', 'longtext DEFAULT NULL'); aieo_dmm_ensure_column($table, 'is_full_cancel', 'tinyint NOT NULL DEFAULT 1'); aieo_dmm_ensure_column($table, 'entry_campaign_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'entry_branch_id', 'bigint unsigned DEFAULT NULL'); aieo_dmm_ensure_column($table, 'event_kind', 'tinyint NOT NULL DEFAULT 0'); aieo_dmm_ensure_column($table, 'instigator', "varchar(10) NOT NULL DEFAULT ''"); aieo_dmm_ensure_column($table, 'frame', "varchar(20) NOT NULL DEFAULT ''"); aieo_dmm_ensure_index($table, 'aieo_dmm_idx_oc_instigator', '(`instigator`, `cancelled_at`)'); aieo_dmm_ensure_index($table, 'aieo_dmm_idx_oc_frame', '(`frame`, `cancelled_at`)'); aieo_dmm_ensure_column($table, 'outbound_shipping_cost', 'decimal(12,2) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'return_shipping_cost', 'decimal(12,2) DEFAULT NULL'); aieo_dmm_ensure_column($table, 'cancelled_by_user_id', 'bigint unsigned NOT NULL DEFAULT 0'); aieo_create_index_if_not_exists('aieo_dmm_idx_oc_event', $table, '(event_kind, cancelled_at)'); } function aieo_dmm_create_order_line_refunds_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_order_line_refunds'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_id` bigint unsigned NOT NULL, `refund_id` bigint unsigned DEFAULT NULL, `user_id` bigint unsigned NOT NULL DEFAULT 0, `product_id` bigint unsigned NOT NULL, `variation_id` bigint unsigned NOT NULL DEFAULT 0, `qty_refunded` int NOT NULL DEFAULT 0, `amount_refunded` decimal(12,2) NOT NULL DEFAULT '0.00', `reason_slug` varchar(64) DEFAULT NULL, `reason_note` varchar(500) DEFAULT NULL, `refunded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_olr_refund_product` (`refund_id`, `product_id`), KEY `aieo_dmm_idx_olr_product` (`product_id`, `refunded_at`), KEY `aieo_dmm_idx_olr_order` (`order_id`), KEY `aieo_dmm_idx_olr_reason` (`reason_slug`, `refunded_at`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_create_product_behavior_metrics_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_product_behavior_metrics'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `product_id` bigint unsigned NOT NULL, `total_orders` int NOT NULL DEFAULT 0, `total_cancellations` int NOT NULL DEFAULT 0, `total_refund_lines` int NOT NULL DEFAULT 0, `cancellation_rate` decimal(6,4) NOT NULL DEFAULT 0, `refund_rate` decimal(6,4) NOT NULL DEFAULT 0, `last_cancelled_at` datetime DEFAULT NULL, `top_reason_slug` varchar(64) DEFAULT NULL, `computed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`product_id`), KEY `aieo_dmm_idx_pbm_rate` (`cancellation_rate`), KEY `aieo_dmm_idx_pbm_recent` (`last_cancelled_at`) ) {$charset_collate};"; $wpdb->query($sql); } function aieo_dmm_backfill_order_cancellations($since_days = 0) { global $wpdb; if (!function_exists('wc_get_order')) { return 0; } $hpos = $wpdb->prefix . 'wc_orders'; $hpos_present = AIEO_Order_Store::uses_hpos() && ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $hpos)) === $hpos); $cancel_statuses = array('wc-cancelled', 'wc-refunded', 'wc-failed'); $placeholders = implode(',', array_fill(0, count($cancel_statuses), '%s')); if ($hpos_present) { $sql_args = $cancel_statuses; $time_clause = ''; if ($since_days > 0) { $time_clause = " AND date_created_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)"; $sql_args[] = (int) $since_days; } $order_ids = $wpdb->get_col($wpdb->prepare( "SELECT id FROM {$hpos} WHERE status IN ({$placeholders}){$time_clause} ORDER BY id ASC", ...$sql_args )); } else { $sql_args = $cancel_statuses; $time_clause = ''; if ($since_days > 0) { $time_clause = " AND post_date_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)"; $sql_args[] = (int) $since_days; } $order_ids = $wpdb->get_col($wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'shop_order' AND post_status IN ({$placeholders}){$time_clause} ORDER BY ID ASC", ...$sql_args )); } $count = 0; foreach ((array) $order_ids as $order_id) { $order = wc_get_order((int) $order_id); if (!$order) { continue; } if (class_exists('AIEO_DMM_Order_Cancellations')) { AIEO_DMM_Order_Cancellations::on_order_status_changed( (int) $order_id, 'unknown', substr((string) $order->get_status('edit'), 0, 32), $order ); $count++; } } return $count; } function aieo_dmm_backfill_order_cancellations_chunked($cursor = 0, $limit = 3000, $since_days = 0) { global $wpdb; $zero = array('processed' => 0, 'has_more' => false, 'next_cursor' => (int) $cursor, 'total' => 0, 'chunk_size' => 0); if (!function_exists('wc_get_order')) { return $zero; } $hpos = $wpdb->prefix . 'wc_orders'; $hpos_present = AIEO_Order_Store::uses_hpos() && ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $hpos)) === $hpos); $cancel_statuses = array('wc-cancelled', 'wc-refunded', 'wc-failed'); $placeholders = implode(',', array_fill(0, count($cancel_statuses), '%s')); $cursor = max(0, (int) $cursor); $limit = max(1, min(5000, (int) $limit)); if ($hpos_present) { $count_args = $cancel_statuses; $chunk_args = $cancel_statuses; $time_clause = ''; if ($since_days > 0) { $time_clause = " AND date_created_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)"; $count_args[] = (int) $since_days; $chunk_args[] = (int) $since_days; } $total = (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM {$hpos} WHERE status IN ({$placeholders}){$time_clause}", ...$count_args )); $chunk_args[] = $cursor; $chunk_args[] = $limit; $order_ids = $wpdb->get_col($wpdb->prepare( "SELECT id FROM {$hpos} WHERE status IN ({$placeholders}){$time_clause} AND id > %d ORDER BY id ASC LIMIT %d", ...$chunk_args )); } else { $count_args = $cancel_statuses; $chunk_args = $cancel_statuses; $time_clause = ''; if ($since_days > 0) { $time_clause = " AND post_date_gmt >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)"; $count_args[] = (int) $since_days; $chunk_args[] = (int) $since_days; } $total = (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'shop_order' AND post_status IN ({$placeholders}){$time_clause}", ...$count_args )); $chunk_args[] = $cursor; $chunk_args[] = $limit; $order_ids = $wpdb->get_col($wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'shop_order' AND post_status IN ({$placeholders}){$time_clause} AND ID > %d ORDER BY ID ASC LIMIT %d", ...$chunk_args )); } $count = 0; $last_id = $cursor; foreach ((array) $order_ids as $order_id) { $oid = (int) $order_id; if ($oid > $last_id) { $last_id = $oid; } $order = wc_get_order($oid); if (!$order) { continue; } if (class_exists('AIEO_DMM_Order_Cancellations')) { AIEO_DMM_Order_Cancellations::on_order_status_changed( $oid, 'unknown', substr((string) $order->get_status('edit'), 0, 32), $order ); $count++; } } $fetched = is_array($order_ids) ? count($order_ids) : 0; return array( 'processed' => $count, 'has_more' => $fetched >= $limit, 'next_cursor' => $last_id, 'total' => $total, 'chunk_size' => $fetched, ); } function aieo_dmm_create_campaigns_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_campaigns'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `slug` varchar(120) NOT NULL, `label` varchar(190) NOT NULL, `channel_hint` varchar(40) DEFAULT NULL, `objective` varchar(40) DEFAULT NULL, `status` varchar(20) NOT NULL DEFAULT 'auto_draft', `start_at` datetime DEFAULT NULL, `end_at` datetime DEFAULT NULL, `budget_planned` decimal(12,2) DEFAULT NULL, `budget_spent` decimal(12,2) DEFAULT NULL, `notes` longtext DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_camp_slug` (`slug`), KEY `aieo_dmm_idx_camp_status` (`status`, `start_at`, `end_at`) ) {$charset_collate};"; $wpdb->query($sql); $sessions = $wpdb->prefix . 'aieo_sessions_statics'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $sessions)) === $sessions) { if (!$wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'entry_campaign_id'")) { $has_predecessor = (bool) $wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'entry_campaign'"); $after_clause = $has_predecessor ? ' AFTER `entry_campaign`' : ''; $wpdb->query("ALTER TABLE {$sessions} ADD COLUMN `entry_campaign_id` bigint unsigned DEFAULT NULL{$after_clause}"); } if ($wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'entry_campaign_id'")) { aieo_create_index_if_not_exists('aieo_idx_ss_camp_id', $sessions, '(entry_campaign_id)'); } if (!$wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'order_id'")) { $wpdb->query("ALTER TABLE {$sessions} ADD COLUMN `order_id` bigint(20) unsigned DEFAULT NULL COMMENT 'WC order id if session led to a purchase' AFTER `session_duration_seconds`"); } aieo_create_index_if_not_exists('aieo_idx_ss_order_id', $sessions, '(order_id)'); if (!$wpdb->get_var("SHOW COLUMNS FROM {$sessions} LIKE 'order_total'")) { $wpdb->query("ALTER TABLE {$sessions} ADD COLUMN `order_total` decimal(12,2) DEFAULT NULL COMMENT 'WC order grand total snapshot' AFTER `order_id`"); } } $offer_attr = $wpdb->prefix . 'aieo_dmm_offer_attributions'; if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $offer_attr)) === $offer_attr) { aieo_dmm_ensure_column($offer_attr, 'entry_campaign_id', 'bigint unsigned DEFAULT NULL'); aieo_create_index_if_not_exists('aieo_dmm_idx_oa_camp_id', $offer_attr, '(entry_campaign_id)'); } } function aieo_dmm_create_offer_overlaps_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_overlaps'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `pair_key` varchar(64) NOT NULL, `open_key` varchar(64) DEFAULT NULL, `a_store` char(2) NOT NULL, `a_id` bigint unsigned NOT NULL, `b_store` char(2) NOT NULL, `b_id` bigint unsigned NOT NULL, `relation` varchar(16) NOT NULL, `keeps` char(1) NOT NULL DEFAULT '', `tb` varchar(8) NOT NULL DEFAULT '', `a_value` decimal(9,4) NOT NULL DEFAULT 0.0000, `b_value` decimal(9,4) NOT NULL DEFAULT 0.0000, `products` int unsigned NOT NULL DEFAULT 0, `sample` longtext DEFAULT NULL, `from_gmt` datetime NOT NULL, `to_gmt` datetime DEFAULT NULL, `opened_by` varchar(32) NOT NULL DEFAULT '', `closed_by` varchar(32) NOT NULL DEFAULT '', `checked_gmt` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_ov_open` (`open_key`), KEY `aieo_dmm_idx_ov_a` (`a_store`, `a_id`, `from_gmt`), KEY `aieo_dmm_idx_ov_b` (`b_store`, `b_id`, `from_gmt`), KEY `aieo_dmm_idx_ov_pair` (`pair_key`, `from_gmt`), KEY `aieo_dmm_idx_ov_to` (`to_gmt`) ) {$charset_collate};"; return false !== $wpdb->query($sql); } function aieo_dmm_create_offer_sets_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_sets'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `set_hash` char(12) NOT NULL, `set_key` text NOT NULL, `members` smallint unsigned NOT NULL DEFAULT 0, `first_seen_gmt` datetime NOT NULL, `last_seen_gmt` datetime NOT NULL, PRIMARY KEY (`set_hash`), KEY `aieo_dmm_idx_sets_last` (`last_seen_gmt`) ) {$charset_collate};"; return false !== $wpdb->query($sql); } function aieo_dmm_create_offer_access_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_access'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `offer_id` bigint(20) unsigned NOT NULL, `kind` varchar(16) NOT NULL DEFAULT 'code', `code` varchar(64) DEFAULT NULL, `coupon_id` bigint(20) unsigned NOT NULL DEFAULT 0, `life_days` smallint(5) unsigned NOT NULL DEFAULT 0, `max_uses` int(10) unsigned NOT NULL DEFAULT 0, `max_uses_per_customer` int(10) unsigned NOT NULL DEFAULT 0, `combines` tinyint(1) NOT NULL DEFAULT 1, `state` varchar(16) NOT NULL DEFAULT 'active', `created_gmt` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `aieo_dmm_uq_acc_code` (`code`), KEY `aieo_dmm_idx_acc_offer` (`offer_id`, `kind`, `state`), KEY `aieo_dmm_idx_acc_coupon` (`coupon_id`) ) {$charset_collate};"; return false !== $wpdb->query($sql); } function aieo_dmm_create_placements_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_placements'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `campaign_id` bigint(20) unsigned NOT NULL DEFAULT 0, `offer_id` bigint(20) unsigned NOT NULL DEFAULT 0, `access_id` bigint(20) unsigned NOT NULL DEFAULT 0, `carrier` varchar(16) NOT NULL, `carrier_ref` varchar(64) NOT NULL DEFAULT '', `audience` longtext DEFAULT NULL, `starts_at` datetime DEFAULT NULL, `ends_at` datetime DEFAULT NULL, `state` varchar(16) NOT NULL DEFAULT 'active', `created_gmt` datetime NOT NULL, `updated_gmt` datetime NOT NULL, PRIMARY KEY (`id`), KEY `aieo_dmm_idx_pl_carrier` (`carrier`, `carrier_ref`), KEY `aieo_dmm_idx_pl_offer` (`offer_id`), KEY `aieo_dmm_idx_pl_campaign` (`campaign_id`), KEY `aieo_dmm_idx_pl_running` (`state`, `starts_at`, `ends_at`) ) {$charset_collate};"; return false !== $wpdb->query($sql); } function aieo_dmm_ensure_offer_access_columns() { global $wpdb; $grants = $wpdb->prefix . 'aieo_coupon_grants'; $campaigns = $wpdb->prefix . 'aieo_dmm_campaigns'; foreach (array($grants, $campaigns) as $table) { if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) !== $table) { return false; } } foreach (aieo_dmm_offer_access_columns() as $table_name => $columns) { $table = $wpdb->prefix . $table_name; foreach ($columns as $column => $definition) { aieo_dmm_ensure_column($table, $column, $definition); } } foreach (aieo_dmm_offer_access_indexes() as $table_name => $indexes) { foreach ($indexes as $index => $columns) { aieo_dmm_ensure_index($wpdb->prefix . $table_name, $index, $columns); } } return true; } function aieo_dmm_offer_access_columns() { return array( 'aieo_coupon_grants' => array( 'offer_id' => "bigint(20) unsigned NOT NULL DEFAULT 0", 'access_id' => "bigint(20) unsigned NOT NULL DEFAULT 0", 'placement_id' => "bigint(20) unsigned NOT NULL DEFAULT 0", 'kind' => "varchar(8) NOT NULL DEFAULT 'code'", 'terms' => "longtext DEFAULT NULL", 'visit_id' => "varchar(40) NOT NULL DEFAULT ''", ), 'aieo_dmm_campaigns' => array( 'goal' => "varchar(24) NOT NULL DEFAULT ''", 'budget' => "decimal(19,4) DEFAULT NULL", ), ); } function aieo_dmm_offer_access_indexes() { return array( 'aieo_coupon_grants' => array( 'aieo_cg_offer_status' => '(`offer_id`, `status`)', 'aieo_cg_placement' => '(`placement_id`)', 'aieo_cg_user_status' => '(`wp_user_id`, `status`)', 'aieo_cg_redeemed' => '(`redeemed_order_id`)', ), ); } function aieo_dmm_offer_access_schema_missing() { global $wpdb; $missing = array(); $have = static function ($sql) use ($wpdb) { return (int) $wpdb->get_var($sql) > 0; }; foreach (array('aieo_dmm_offer_access', 'aieo_dmm_placements') as $name) { if (!$have($wpdb->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s', $wpdb->prefix . $name))) { $missing[] = $name; } } foreach (aieo_dmm_offer_access_columns() as $name => $columns) { foreach (array_keys($columns) as $column) { if (!$have($wpdb->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s', $wpdb->prefix . $name, $column))) { $missing[] = $name . '.' . $column; } } } foreach (aieo_dmm_offer_access_indexes() as $name => $indexes) { foreach (array_keys($indexes) as $index) { if (!$have($wpdb->prepare('SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s', $wpdb->prefix . $name, $index))) { $missing[] = $name . '#' . $index; } } } return $missing; } function aieo_dmm_create_offer_events_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_events'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `offer_id` bigint unsigned NOT NULL DEFAULT 0, `event` varchar(16) NOT NULL DEFAULT '', `at_gmt` datetime NOT NULL, `revision` int unsigned NOT NULL DEFAULT 0, `moved_by` varchar(32) NOT NULL DEFAULT '', `detail` longtext DEFAULT NULL, PRIMARY KEY (`id`), KEY `aieo_dmm_idx_ev_offer` (`offer_id`, `id`), KEY `aieo_dmm_idx_ev_at` (`at_gmt`) ) {$charset_collate};"; return false !== $wpdb->query($sql); } function aieo_dmm_create_offer_counters_table() { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_counters'; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$table} ( `offer_id` bigint unsigned NOT NULL, `counter` varchar(16) NOT NULL, `scope` varchar(80) NOT NULL DEFAULT '', `period` varchar(10) NOT NULL DEFAULT '', `value` decimal(19,4) NOT NULL DEFAULT 0, PRIMARY KEY (`offer_id`, `counter`, `scope`, `period`) ) {$charset_collate};"; return false !== $wpdb->query($sql); } 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_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); }