table_name = $wpdb->prefix . 'aieo_chat_business_info'; add_action('wp_ajax_aieo_get_business_info_entries', array($this, 'ajax_get_entries')); add_action('wp_ajax_aieo_save_business_info', array($this, 'ajax_save_entry')); add_action('wp_ajax_aieo_delete_business_info', array($this, 'ajax_delete_entry')); add_action('wp_ajax_aieo_sync_wc_table_rates', array($this, 'ajax_sync_wc_table_rates')); add_action('wp_ajax_aieo_import_page_as_business_info', array($this, 'ajax_import_page')); } public function create_table() { global $wpdb; $charset_collate = aieo_get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} ( id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, info_type varchar(50) NOT NULL, title varchar(255) NOT NULL, content TEXT NOT NULL, priority int(11) DEFAULT 0 COMMENT 'Higher priority = shown first', is_active tinyint(1) DEFAULT 1, metadata TEXT DEFAULT NULL COMMENT 'JSON for extra data like coordinates, postal codes', source varchar(50) DEFAULT 'manual' COMMENT 'manual, wc_table_rates, wc_zones, etc.', source_id varchar(100) DEFAULT NULL COMMENT 'ID from source if synced', created_at datetime DEFAULT CURRENT_TIMESTAMP, updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY info_type (info_type), KEY is_active (is_active), KEY priority (priority), KEY source (source, source_id) ) $charset_collate;"; require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); function_exists('aieo_dbdelta') ? aieo_dbdelta($sql) : dbDelta($sql); } public function get_info_types() { return array( self::TYPE_STORE_LOCATION => __('Store Locations', 'ai-eshop-optimizer'), self::TYPE_OPENING_HOURS => __('Opening Hours', 'ai-eshop-optimizer'), self::TYPE_SHIPPING => __('Shipping Information', 'ai-eshop-optimizer'), self::TYPE_CONTACT => __('Contact Information', 'ai-eshop-optimizer'), self::TYPE_RETURNS => __('Returns & Refunds', 'ai-eshop-optimizer'), self::TYPE_FAQ => __('Frequently Asked Questions', 'ai-eshop-optimizer'), self::TYPE_CUSTOM => __('Custom Information', 'ai-eshop-optimizer'), ); } public function save_entry($data) { global $wpdb; $defaults = array( 'info_type' => self::TYPE_CUSTOM, 'title' => '', 'content' => '', 'priority' => 0, 'is_active' => 1, 'metadata' => null, 'source' => 'manual', 'source_id' => null, ); $data = wp_parse_args($data, $defaults); if (is_array($data['metadata'])) { $data['metadata'] = json_encode($data['metadata']); } if (!empty($data['id'])) { $id = $data['id']; unset($data['id']); $wpdb->update( $this->table_name, $data, array('id' => $id), array('%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s'), array('%d') ); return $id; } $wpdb->insert( $this->table_name, $data, array('%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s') ); return $wpdb->insert_id; } public function get_entry($id) { global $wpdb; $entry = $wpdb->get_row($wpdb->prepare( "SELECT * FROM {$this->table_name} WHERE id = %d", $id )); if ($entry && $entry->metadata) { $entry->metadata = json_decode($entry->metadata, true); } return $entry; } public function get_entries($args = array()) { global $wpdb; $defaults = array( 'info_type' => null, 'is_active' => null, 'source' => null, 'orderby' => 'priority', 'order' => 'DESC', 'limit' => 100, ); $args = wp_parse_args($args, $defaults); $where = array('1=1'); $values = array(); if ($args['info_type'] !== null) { $where[] = 'info_type = %s'; $values[] = $args['info_type']; } if ($args['is_active'] !== null) { $where[] = 'is_active = %d'; $values[] = $args['is_active']; } if ($args['source'] !== null) { $where[] = 'source = %s'; $values[] = $args['source']; } $where_clause = implode(' AND ', $where); $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']); $limit = absint($args['limit']); $sql = "SELECT * FROM {$this->table_name} WHERE {$where_clause} ORDER BY {$orderby} LIMIT {$limit}"; if (!empty($values)) { $sql = $wpdb->prepare($sql, $values); } $entries = $wpdb->get_results($sql); foreach ($entries as &$entry) { if ($entry->metadata) { $entry->metadata = json_decode($entry->metadata, true); } } return $entries; } public function delete_entry($id) { global $wpdb; return $wpdb->delete( $this->table_name, array('id' => $id), array('%d') ) !== false; } public function delete_by_source($source) { global $wpdb; return $wpdb->delete( $this->table_name, array('source' => $source), array('%s') ); } public function get_context_for_chat($types = null) { $entries = $this->get_entries(array( 'is_active' => 1, 'orderby' => 'info_type, priority', 'order' => 'ASC', 'limit' => 500, )); if (empty($entries)) { return ''; } if ($types !== null && is_array($types)) { $entries = array_filter($entries, function($entry) use ($types) { return in_array($entry->info_type, $types); }); } $grouped = array(); foreach ($entries as $entry) { if (!isset($grouped[$entry->info_type])) { $grouped[$entry->info_type] = array(); } $grouped[$entry->info_type][] = $entry; } $output = array(); $type_labels = $this->get_info_types(); foreach ($grouped as $type => $type_entries) { $type_label = isset($type_labels[$type]) ? $type_labels[$type] : ucfirst($type); $section = "## {$type_label}\n\n"; foreach ($type_entries as $entry) { $section .= "### {$entry->title}\n"; $section .= "{$entry->content}\n\n"; if (!empty($entry->metadata)) { if ($type === self::TYPE_STORE_LOCATION) { if (!empty($entry->metadata['address'])) { $section .= "Address: {$entry->metadata['address']}\n"; } if (!empty($entry->metadata['phone'])) { $section .= "Phone: {$entry->metadata['phone']}\n"; } if (!empty($entry->metadata['email'])) { $section .= "Email: {$entry->metadata['email']}\n"; } } $section .= "\n"; } } $output[] = $section; } return implode("\n", $output); } private function format_price_plain($price) { $currency_symbol = get_woocommerce_currency_symbol(); $decimals = wc_get_price_decimals(); $decimal_sep = wc_get_price_decimal_separator(); $thousand_sep = wc_get_price_thousand_separator(); $formatted = number_format((float) $price, $decimals, $decimal_sep, $thousand_sep); $position = get_option('woocommerce_currency_pos', 'left'); switch ($position) { case 'left': return $currency_symbol . $formatted; case 'right': return $formatted . $currency_symbol; case 'left_space': return $currency_symbol . ' ' . $formatted; case 'right_space': return $formatted . ' ' . $currency_symbol; default: return $currency_symbol . $formatted; } } public function sync_wc_table_rates() { global $wpdb; if (!class_exists('WC_Shipping_Zones')) { return array( 'success' => false, 'message' => __('WooCommerce is not active', 'ai-eshop-optimizer'), ); } $shipping_zones = WC_Shipping_Zones::get_zones(); $zone_0 = WC_Shipping_Zones::get_zone(0); $shipping_zones['0'] = array( 'zone_id' => 0, 'zone_name' => __('Rest of the World', 'ai-eshop-optimizer'), 'shipping_methods' => $zone_0->get_shipping_methods(), ); $this->delete_by_source('wc_shipping_zones'); $this->delete_by_source('wc_table_rates'); $synced = 0; $table_rates_table = $wpdb->prefix . 'woocommerce_shipping_table_rates'; $has_table_rate_plugin = class_exists('WC_Table_Rate_Shipping'); foreach ($shipping_zones as $zone) { $zone_id = $zone['zone_id']; $zone_name = $zone['zone_name']; $zone_obj = WC_Shipping_Zones::get_zone($zone_id); $zone_locations = $zone_obj->get_zone_locations(); $countries = array(); $states = array(); $postcodes = array(); foreach ($zone_locations as $location) { if ($location->type === 'postcode') { $postcodes[] = $location->code; } elseif ($location->type === 'state') { $parts = explode(':', $location->code); if (count($parts) === 2) { $state_list = WC()->countries->get_states($parts[0]); $states[] = isset($state_list[$parts[1]]) ? $state_list[$parts[1]] : $location->code; } else { $states[] = $location->code; } } elseif ($location->type === 'country') { $countries[] = isset(WC()->countries->countries[$location->code]) ? WC()->countries->countries[$location->code] : $location->code; } } if ($zone_id != 0 && empty($countries) && empty($states) && empty($postcodes)) { continue; } $shipping_options = array(); $lowest_price = null; $has_free_shipping = false; $free_shipping_conditions = ''; foreach ($zone['shipping_methods'] as $method) { if (!$method->is_enabled()) { continue; } $method_info = array( 'name' => $method->title, 'type' => $method->id, 'min_cost' => null, ); if ($method->id === 'table_rate' && $has_table_rate_plugin) { $instance_id = $method->instance_id; $rates = $wpdb->get_results($wpdb->prepare( "SELECT rate_cost FROM {$table_rates_table} WHERE shipping_method_id = %d ORDER BY rate_cost ASC LIMIT 1", $instance_id ), ARRAY_A); if (!empty($rates)) { $method_info['min_cost'] = floatval($rates[0]['rate_cost']); } } if ($method->id === 'aieo_table_rate') { $instance_id = $method->instance_id; $aieo_rates_table = $wpdb->prefix . 'aieo_dmm_shipping_rates'; $aieo_rules = $wpdb->get_results($wpdb->prepare( "SELECT rate_condition, rate_min, rate_cost, rate_cost_per_item, rate_cost_per_weight_unit, rate_cost_percent FROM {$aieo_rates_table} WHERE shipping_method_id = %d AND enabled = 1 AND rate_abort = 0", $instance_id ), ARRAY_A); $min = null; $free_over = null; foreach ((array) $aieo_rules as $rule) { $cost = floatval($rule['rate_cost']); $has_extra = (floatval($rule['rate_cost_per_item']) != 0) || (floatval($rule['rate_cost_per_weight_unit']) != 0) || (floatval($rule['rate_cost_percent']) != 0); if ($cost == 0 && !$has_extra && $rule['rate_condition'] === 'price' && $rule['rate_min'] !== null && floatval($rule['rate_min']) > 0) { $threshold = floatval($rule['rate_min']); if ($free_over === null || $threshold < $free_over) { $free_over = $threshold; } continue; } if ($cost == 0 && $has_extra) { continue; } if ($min === null || $cost < $min) { $min = $cost; } } if ($min !== null) { $method_info['min_cost'] = $min; } if ($free_over !== null) { $method_info['name'] .= ' ' . sprintf( __('(FREE for orders over %s)', 'ai-eshop-optimizer'), $this->format_price_plain($free_over) ); } } if ($method->id === 'flat_rate') { $cost = $method->get_option('cost', 0); if (!empty($cost)) { if (is_numeric($cost)) { $method_info['min_cost'] = floatval($cost); } else { $method_info['min_cost'] = 'varies'; } } } if ($method->id === 'free_shipping') { $has_free_shipping = true; $method_info['min_cost'] = 0; $min_amount = floatval($method->get_option('min_amount', 0)); $requires = $method->get_option('requires', ''); $conditions = array(); if ($min_amount > 0) { $conditions[] = sprintf( __('orders over %s', 'ai-eshop-optimizer'), $this->format_price_plain($min_amount) ); } if ($requires === 'coupon') { $conditions[] = __('with free shipping coupon', 'ai-eshop-optimizer'); } elseif ($requires === 'both' && $min_amount > 0) { $conditions[] = __('and free shipping coupon', 'ai-eshop-optimizer'); } if (!empty($conditions)) { $free_shipping_conditions = implode(' ', $conditions); } } if ($method->id === 'local_pickup' || $method->id === 'pickup_location') { $cost = $method->get_option('cost', 0); $method_info['min_cost'] = is_numeric($cost) ? floatval($cost) : 0; } if ($method_info['min_cost'] !== null && $method_info['min_cost'] !== 'varies') { if ($method->id !== 'free_shipping' || empty($free_shipping_conditions)) { if ($lowest_price === null || floatval($method_info['min_cost']) < $lowest_price) { $lowest_price = floatval($method_info['min_cost']); } } } $shipping_options[] = $method_info; } if (empty($shipping_options)) { continue; } $content_parts = array(); $content_parts[] = sprintf(__('SHIPPING ZONE: %s', 'ai-eshop-optimizer'), $zone_name); $content_parts[] = ''; if (!empty($countries)) { $content_parts[] = __('Countries covered', 'ai-eshop-optimizer') . ': ' . implode(', ', $countries); } if (!empty($states)) { $content_parts[] = __('Regions/States covered', 'ai-eshop-optimizer') . ': ' . implode(', ', $states); } if (!empty($postcodes)) { $content_parts[] = __('Postal codes covered', 'ai-eshop-optimizer') . ': ' . implode(', ', $postcodes); } if ($zone_id == 0) { $content_parts[] = __('This zone covers all locations not included in other shipping zones.', 'ai-eshop-optimizer'); } $content_parts[] = ''; $content_parts[] = __('AVAILABLE SHIPPING OPTIONS:', 'ai-eshop-optimizer'); foreach ($shipping_options as $option) { if ($option['min_cost'] === null) { $content_parts[] = sprintf('• %s', $option['name']); } elseif ($option['min_cost'] === 'varies') { $content_parts[] = sprintf('• %s: %s', $option['name'], __('price varies based on order', 'ai-eshop-optimizer')); } elseif ($option['min_cost'] == 0 && $option['type'] === 'free_shipping') { if (!empty($free_shipping_conditions)) { $content_parts[] = sprintf('• %s: %s %s', $option['name'], __('FREE', 'ai-eshop-optimizer'), $free_shipping_conditions); } else { $content_parts[] = sprintf('• %s: %s', $option['name'], __('FREE', 'ai-eshop-optimizer')); } } elseif ($option['min_cost'] == 0) { $content_parts[] = sprintf('• %s: %s', $option['name'], __('FREE', 'ai-eshop-optimizer')); } else { $content_parts[] = sprintf( '• %s: %s %s', $option['name'], __('from', 'ai-eshop-optimizer'), $this->format_price_plain((float) $option['min_cost']) ); } } $content_parts[] = ''; if ($lowest_price !== null) { if ($lowest_price == 0) { $content_parts[] = __('Shipping can be FREE for this zone.', 'ai-eshop-optimizer'); } else { $content_parts[] = sprintf( __('Shipping for this zone starts at %s.', 'ai-eshop-optimizer'), $this->format_price_plain($lowest_price) ); } } $content_parts[] = __('Exact shipping cost depends on order contents and delivery address. Please add items to cart and proceed to checkout to see exact shipping cost for your order.', 'ai-eshop-optimizer'); $metadata = array( 'zone_id' => $zone_id, 'zone_name' => $zone_name, 'countries' => $countries, 'states' => $states, 'postcodes' => $postcodes, 'shipping_options' => $shipping_options, 'lowest_price' => $lowest_price, 'has_free_shipping' => $has_free_shipping, ); $this->save_entry(array( 'info_type' => self::TYPE_SHIPPING, 'title' => sprintf(__('Shipping to %s', 'ai-eshop-optimizer'), $zone_name), 'content' => implode("\n", $content_parts), 'priority' => ($zone_id == 0) ? 5 : 10, 'source' => 'wc_shipping_zones', 'source_id' => 'zone_' . $zone_id, 'metadata' => $metadata, )); $synced++; } return array( 'success' => true, 'synced' => $synced, 'message' => sprintf( __('Successfully synced %d shipping zones', 'ai-eshop-optimizer'), $synced ), ); } public function get_counts_by_type() { global $wpdb; $results = $wpdb->get_results( "SELECT info_type, COUNT(*) as count FROM {$this->table_name} GROUP BY info_type", OBJECT_K ); $counts = array(); $types = $this->get_info_types(); foreach ($types as $type => $label) { $counts[$type] = isset($results[$type]) ? (int) $results[$type]->count : 0; } return $counts; } public function table_exists() { global $wpdb; return $wpdb->get_var("SHOW TABLES LIKE '{$this->table_name}'") === $this->table_name; } public function ajax_get_entries() { check_ajax_referer('aieo_business_info_nonce', 'nonce'); if (!current_user_can('manage_options')) { wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer'))); } $entries = $this->get_entries(); foreach ($entries as &$entry) { $entry->priority = $this->priority_to_label($entry->priority); } wp_send_json_success(array('entries' => $entries)); } public function ajax_save_entry() { check_ajax_referer('aieo_business_info_nonce', 'nonce'); if (!current_user_can('manage_options')) { wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer'))); } $title = isset($_POST['title']) ? sanitize_text_field(wp_unslash( $_POST['title'] )) : ''; $content = isset($_POST['content']) ? sanitize_textarea_field(wp_unslash( $_POST['content'] )) : ''; $info_type = isset($_POST['info_type']) ? sanitize_text_field(wp_unslash( $_POST['info_type'] )) : 'faq'; $priority_level = isset($_POST['priority']) ? sanitize_text_field(wp_unslash( $_POST['priority'] )) : 'normal'; if (empty($title) || empty($content)) { wp_send_json_error(array('message' => __('Title and content are required', 'ai-eshop-optimizer'))); } $priority_map = array( 'normal' => 0, 'high' => 10, 'critical' => 20, ); $priority = isset($priority_map[$priority_level]) ? $priority_map[$priority_level] : 0; $entry_id = $this->save_entry(array( 'title' => $title, 'content' => $content, 'info_type' => $info_type, 'priority' => $priority, )); if ($entry_id) { wp_send_json_success(array( 'message' => __('Entry saved successfully', 'ai-eshop-optimizer'), 'entry_id' => $entry_id, )); } else { wp_send_json_error(array('message' => __('Failed to save entry', 'ai-eshop-optimizer'))); } } public function ajax_delete_entry() { check_ajax_referer('aieo_business_info_nonce', 'nonce'); if (!current_user_can('manage_options')) { wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer'))); } $entry_id = isset($_POST['entry_id']) ? absint($_POST['entry_id']) : 0; if (!$entry_id) { wp_send_json_error(array('message' => __('Invalid entry ID', 'ai-eshop-optimizer'))); } if ($this->delete_entry($entry_id)) { wp_send_json_success(array('message' => __('Entry deleted', 'ai-eshop-optimizer'))); } else { wp_send_json_error(array('message' => __('Failed to delete entry', 'ai-eshop-optimizer'))); } } public function ajax_sync_wc_table_rates() { check_ajax_referer('aieo_business_info_nonce', 'nonce'); if (!current_user_can('manage_options')) { wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer'))); } $result = $this->sync_wc_table_rates(); if ($result['success']) { wp_send_json_success($result); } else { wp_send_json_error($result); } } public function ajax_import_page() { check_ajax_referer('aieo_business_info_nonce', 'nonce'); if (!current_user_can('manage_options')) { wp_send_json_error(array('message' => __('Permission denied', 'ai-eshop-optimizer'))); } $page_id = isset($_POST['page_id']) ? absint($_POST['page_id']) : 0; $info_type = isset($_POST['info_type']) ? sanitize_text_field(wp_unslash( $_POST['info_type'] )) : 'faq'; if (!$page_id) { wp_send_json_error(array('message' => __('Please select a page', 'ai-eshop-optimizer'))); } $page = get_post($page_id); if (!$page || $page->post_type !== 'page') { wp_send_json_error(array('message' => __('Page not found', 'ai-eshop-optimizer'))); } $content = $page->post_content; $content = strip_shortcodes($content); $content = wp_strip_all_tags($content); $content = preg_replace('/\s+/', ' ', $content); $content = trim($content); if (empty($content)) { wp_send_json_error(array('message' => __('Page has no content to import', 'ai-eshop-optimizer'))); } $entry_id = $this->save_entry(array( 'title' => $page->post_title, 'content' => $content, 'info_type' => $info_type, 'priority' => 5, 'source' => 'page_import', 'source_id' => $page_id, )); if ($entry_id) { wp_send_json_success(array( 'message' => sprintf( __('Imported "%1$s" as %2$s information', 'ai-eshop-optimizer'), $page->post_title, $this->get_info_types()[$info_type] ?? $info_type ), 'entry_id' => $entry_id, )); } else { wp_send_json_error(array('message' => __('Failed to import page', 'ai-eshop-optimizer'))); } } public function priority_to_label($priority) { if ($priority >= 20) return 'critical'; if ($priority >= 10) return 'high'; return 'normal'; } }