d' AND o.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND DATE(o.post_date) BETWEEN %s AND %s GROUP BY oim_product.meta_value ) current_sales ON current_sales.product_id = p.ID LEFT JOIN ( SELECT oim_product.meta_value as product_id, SUM(CAST(oim.meta_value AS SIGNED)) as total_sold FROM {$wpdb->prefix}woocommerce_order_itemmeta oim INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON oi.order_item_id = oim.order_item_id INNER JOIN {$wpdb->posts} o ON o.ID = oi.order_id INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_product ON oim_product.order_item_id = oi.order_item_id WHERE oim.meta_key = '_qty' AND oim_product.meta_key = '_product_id' AND o.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND DATE(o.post_date) BETWEEN %s AND %s GROUP BY oim_product.meta_value ) previous_sales ON previous_sales.product_id = p.ID WHERE p.post_type = 'product' AND p.post_status = 'publish' AND (COALESCE(previous_sales.total_sold, 0) > 0) HAVING sales_change < 0 ORDER BY change_percentage ASC LIMIT %d ", $start_date, $end_date, $previous_period_start, $previous_period_end, $limit)); return $results ?: array(); } catch (Exception $e) { error_log("WC AI Consultant: Error getting declining products: " . $e->getMessage()); return array(); } } // Continue with more REAL implementations... private function get_top_customers($start_date, $end_date, $limit = 5) { global $wpdb; try { if ($this->is_hpos_enabled()) { $results = $wpdb->get_results($wpdb->prepare(" SELECT u.display_name, u.user_email, o.customer_id, SUM(o.total_amount) as total_spent, COUNT(o.id) as order_count FROM {$wpdb->prefix}wc_orders o INNER JOIN {$wpdb->users} u ON u.ID = o.customer_id WHERE o.customer_id > 0 AND o.status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND DATE(o.date_created_gmt) BETWEEN %s AND %s GROUP BY o.customer_id, u.display_name, u.user_email ORDER BY total_spent DESC LIMIT %d ", $start_date, $end_date, $limit)); } else { $results = $wpdb->get_results($wpdb->prepare(" SELECT u.display_name, u.user_email, pm.meta_value as customer_id, SUM(CAST(pm2.meta_value AS DECIMAL(10,2))) as total_spent, COUNT(p.ID) as order_count FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id INNER JOIN {$wpdb->postmeta} pm2 ON p.ID = pm2.post_id INNER JOIN {$wpdb->users} u ON u.ID = pm.meta_value WHERE pm.meta_key = '_customer_user' AND pm.meta_value > 0 AND pm2.meta_key = '_order_total' AND p.post_type = 'shop_order' AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND DATE(p.post_date) BETWEEN %s AND %s GROUP BY pm.meta_value, u.display_name, u.user_email ORDER BY total_spent DESC LIMIT %d ", $start_date, $end_date, $limit)); } return $results ?: array(); } catch (Exception $e) { error_log("WC AI Consultant: Error getting top customers: " . $e->getMessage()); return array(); } } private function get_geographic_data($start_date, $end_date) { global $wpdb; try { if ($this->is_hpos_enabled()) { $results = $wpdb->get_results($wpdb->prepare(" SELECT oa.country, oa.state, COUNT(o.id) as order_count, SUM(o.total_amount) as total_revenue FROM {$wpdb->prefix}wc_orders o INNER JOIN {$wpdb->prefix}wc_order_addresses oa ON o.id = oa.order_id WHERE o.status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND oa.address_type = 'billing' AND DATE(o.date_created_gmt) BETWEEN %s AND %s GROUP BY oa.country, oa.state ORDER BY total_revenue DESC LIMIT 20 ", $start_date, $end_date)); } else { $results = $wpdb->get_results($wpdb->prepare(" SELECT pm_country.meta_value as country, pm_state.meta_value as state, COUNT(p.ID) as order_count, SUM(CAST(pm_total.meta_value AS DECIMAL(10,2))) as total_revenue FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm_total ON p.ID = pm_total.post_id LEFT JOIN {$wpdb->postmeta} pm_country ON p.ID = pm_country.post_id LEFT JOIN {$wpdb->postmeta} pm_state ON p.ID = pm_state.post_id WHERE p.post_type = 'shop_order' AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND pm_total.meta_key = '_order_total' AND pm_country.meta_key = '_billing_country' AND pm_state.meta_key = '_billing_state' AND DATE(p.post_date) BETWEEN %s AND %s GROUP BY pm_country.meta_value, pm_state.meta_value ORDER BY total_revenue DESC LIMIT 20 ", $start_date, $end_date)); } return $results ?: array(); } catch (Exception $e) { error_log("WC AI Consultant: Error getting geographic data: " . $e->getMessage()); return array(); } } private function get_category_performance($start_date, $end_date) { global $wpdb; try { $results = $wpdb->get_results($wpdb->prepare(" SELECT t.name as category_name, t.term_id, SUM(CAST(oim.meta_value AS SIGNED)) as total_sold, SUM(CAST(oim2.meta_value AS DECIMAL(10,2))) as total_revenue, COUNT(DISTINCT o.ID) as order_count FROM {$wpdb->prefix}woocommerce_order_itemmeta oim INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON oi.order_item_id = oim.order_item_id INNER JOIN {$wpdb->posts} o ON o.ID = oi.order_id INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_product ON oim_product.order_item_id = oi.order_item_id INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = oim_product.meta_value INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id INNER JOIN {$wpdb->terms} t ON t.term_id = tt.term_id LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key = '_line_total' WHERE oim.meta_key = '_qty' AND oim_product.meta_key = '_product_id' AND tt.taxonomy = 'product_cat' AND o.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND DATE(o.post_date) BETWEEN %s AND %s GROUP BY t.term_id, t.name ORDER BY total_revenue DESC LIMIT 15 ", $start_date, $end_date)); return $results ?: array(); } catch (Exception $e) { error_log("WC AI Consultant: Error getting category performance: " . $e->getMessage()); return array(); } } private function calculate_customer_lifetime_value() { global $wpdb; try { if ($this->is_hpos_enabled()) { $result = $wpdb->get_row(" SELECT AVG(customer_totals.total_spent) as avg_clv, COUNT(*) as customer_count FROM ( SELECT customer_id, SUM(total_amount) as total_spent FROM {$wpdb->prefix}wc_orders WHERE customer_id > 0 AND status IN ('wc-completed', 'wc-processing') GROUP BY customer_id ) customer_totals "); } else { $result = $wpdb->get_row(" SELECT AVG(customer_totals.total_spent) as avg_clv, COUNT(*) as customer_count FROM ( SELECT pm.meta_value as customer_id, SUM(CAST(pm2.meta_value AS DECIMAL(10,2))) as total_spent FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id INNER JOIN {$wpdb->postmeta} pm2 ON p.ID = pm2.post_id WHERE pm.meta_key = '_customer_user' AND pm.meta_value > 0 AND pm2.meta_key = '_order_total' AND p.post_type = 'shop_order' AND p.post_status IN ('wc-completed', 'wc-processing') GROUP BY pm.meta_value ) customer_totals "); } return array( 'average_clv' => floatval($result->avg_clv ?? 0), 'customer_count' => intval($result->customer_count ?? 0) ); } catch (Exception $e) { error_log("WC AI Consultant: Error calculating CLV: " . $e->getMessage()); return array('average_clv' => 0, 'customer_count' => 0); } } // Continue with all other methods... private function format_duration($seconds) { if (!$seconds) return '0:00'; $minutes = floor($seconds / 60); $seconds = $seconds % 60; return sprintf('%d:%02d', $minutes, $seconds); } private function get_priority_class($score) { if ($score >= 8) return 'high'; if ($score >= 6) return 'medium'; return 'low'; } private function get_todays_consultation() { global $wpdb; $table_name = $wpdb->prefix . 'wc_ai_consultations'; $today = date('Y-m-d'); return $wpdb->get_row($wpdb->prepare( "SELECT * FROM $table_name WHERE consultation_date = %s", $today ), ARRAY_A); } private function save_consultation($wc_data, $ga4_data, $ai_analysis, $audio_result, $consultation_script) { global $wpdb; $table_name = $wpdb->prefix . 'wc_ai_consultations'; $today = date('Y-m-d'); $success = $wpdb->replace($table_name, array( 'consultation_date' => $today, 'wc_data' => json_encode($wc_data), 'ga4_data' => $ga4_data ? json_encode($ga4_data) : null, 'ai_analysis' => json_encode($ai_analysis), 'consultation_audio_url' => $audio_result['audio_url'], 'consultation_duration' => $audio_result['duration'], 'insights_count' => $ai_analysis['insights_count'], 'recommendations_count' => $ai_analysis['recommendations_count'], 'priority_score' => $ai_analysis['priority_score'], 'data_accuracy_score' => $ai_analysis['data_accuracy_score'] ?? 0, 'created_at' => current_time('mysql') )); if ($success === false) { throw new Exception(__('Failed to save consultation to database.', 'wc-audio-ai-consultant')); } return true; } private function get_average_order_value($start_date, $end_date) { global $wpdb; try { if ($this->is_hpos_enabled()) { $result = $wpdb->get_row($wpdb->prepare(" SELECT COUNT(*) as order_count, SUM(total_amount) as total_sales FROM {$wpdb->prefix}wc_orders WHERE status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND date_created_gmt BETWEEN %s AND %s ", $start_date . ' 00:00:00', $end_date . ' 23:59:59')); } else { $result = $wpdb->get_row($wpdb->prepare(" SELECT COUNT(*) as order_count, SUM(CAST(pm.meta_value AS DECIMAL(10,2))) as total_sales FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE pm.meta_key = '_order_total' AND p.post_type = 'shop_order' AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND DATE(p.post_date) BETWEEN %s AND %s ", $start_date, $end_date)); } if ($result && $result->order_count > 0) { return floatval($result->total_sales) / intval($result->order_count); } return 0; } catch (Exception $e) { error_log("WC AI Consultant: Error calculating AOV: " . $e->getMessage()); return 0; } } private function get_top_products($start_date, $end_date, $limit = 10) { global $wpdb; try { $results = $wpdb->get_results($wpdb->prepare(" SELECT p.post_title, p.ID as product_id, SUM(CAST(oim.meta_value AS SIGNED)) as total_sold, SUM(CAST(oim2.meta_value AS DECIMAL(10,2))) as total_revenue FROM {$wpdb->prefix}woocommerce_order_itemmeta oim INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON oi.order_item_id = oim.order_item_id INNER JOIN {$wpdb->posts} o ON o.ID = oi.order_id INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_product ON oim_product.order_item_id = oi.order_item_id INNER JOIN {$wpdb->posts} p ON p.ID = oim_product.meta_value LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key = '_line_total' WHERE oim.meta_key = '_qty' AND oim_product.meta_key = '_product_id' AND o.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold') AND DATE(o.post_date) BETWEEN %s AND %s GROUP BY oim_product.meta_value, p.post_title ORDER BY total_sold DESC LIMIT %d ", $start_date, $end_date, $limit)); return $results ?: array(); } catch (Exception $e) { error_log("WC AI Consultant: Error getting top products: " . $e->getMessage()); return array(); } } private function get_out_of_stock_products() { global $wpdb; try { $results = $wpdb->get_results(" SELECT p.post_title, p.ID as product_id, pm.meta_value as stock_status FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id WHERE p.post_type = 'product' AND p.post_status = 'publish' AND pm.meta_key = '_stock_status' AND pm.meta_value = 'outofstock' ORDER BY p.post_title LIMIT 20 "); return $results ?: array(); } catch (Exception $e) { error_log("WC AI Consultant: Error getting out of stock products: " . $e->getMessage()); return array(); } } private function get_low_stock_products($threshold = 5) { global $wpdb; try { $results = $wpdb->get_results($wpdb->prepare(" SELECT p.post_title, p.ID as product_id, CAST(pm_stock.meta_value AS SIGNED) as stock_quantity, pm_status.meta_value as stock_status FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} pm_stock ON p.ID = pm_stock.post_id INNER JOIN {$wpdb->postmeta} pm_status ON p.ID = pm_status.post_id WHERE p.post_type = 'product' AND p.post_status = 'publish' AND pm_stock.meta_key = '_stock' AND pm_status.meta_key = '_stock_status' AND pm_status.meta_value = 'instock' AND CAST(pm_stock.meta_value AS SIGNED) <= %d AND CAST(pm_stock.meta_value AS SIGNED) > 0 ORDER BY CAST(pm_stock.meta_value AS SIGNED) ASC LIMIT 20 ", $threshold)); return $results ?: array(); } catch (Exception $e) { error_log("WC AI Consultant: Error getting low stock products: " . $e->getMessage()); return array(); } } private function get_new_products($start_date, $end_date) { global $wpdb; try { $results = $wpdb->get_results($wpdb->prepare(" SELECT p.post_title, p.ID as product_id, p.post_date, COALESCE(sales.total_sold, 0) as sales_since_launch FROM {$wpdb->posts} p LEFT JOIN ( SELECT oim_product.meta_value as product_id, SUM(CAST(oim.meta_value AS SIGNED)) as total_sold FROM {$wpdb->prefix}woocommerce_order_itemmeta oim INNER JOIN {$wpdb->prefix}woocommerce_order_items oi ON oi.order_item_id = oim.order_item_id INNER JOIN {$wpdb->posts} o ON o.ID = oi.order_id INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_product ON oim_product.order_item_id = oi.order_item_id WHERE oim.meta_key = '_qty' AND oim_product.meta_key = '_product_i