<?php
/**
 * REST API Class
 *
 * @package InsightPress
 */

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

class InsightPress_API
{
    /**
     * Namespace.
     */
    protected $namespace = 'insightpress/v1';

    /**
     * Constructor.
     */
    public function __construct()
    {
        add_action('rest_api_init', array($this, 'register_routes'));
    }

    /**
     * Register routes.
     */
    public function register_routes()
    {
        register_rest_route($this->namespace, '/analytics/sales', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_sales_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/product', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_product_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/traffic', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_traffic_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/cart-abandonment', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_cart_abandonment_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/funnel', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_funnel_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/product-pairings', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_product_pairings'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/time-to-purchase', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_time_to_purchase_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/post/(?P<id>\d+)', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_single_post_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/product/(?P<id>\d+)', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_single_product_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/product/(?P<id>\d+)/customers', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_product_customers'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/analytics/posts', array(
            'methods' => 'GET',
            'callback' => array($this, 'get_posts_analytics'),
            'permission_callback' => array($this, 'get_permissions'),
        ));

        register_rest_route($this->namespace, '/suggest-feature', array(
            'methods' => 'POST',
            'callback' => array($this, 'handle_feature_suggestion'),
            'permission_callback' => array($this, 'get_permissions'),
        ));
    }

    /**
     * Check permissions.
     */
    public function get_permissions()
    {
        return current_user_can('manage_woocommerce');
    }

    /**
     * Handle feature suggestion form submission
     */
    public function handle_feature_suggestion($request)
    {
        $params = $request->get_params();

        $name = isset($params['name']) ? sanitize_text_field($params['name']) : '';
        $email = isset($params['email']) ? sanitize_email($params['email']) : '';
        $site_url = isset($params['site_url']) ? esc_url_raw($params['site_url']) : '';
        $message = isset($params['message']) ? sanitize_textarea_field($params['message']) : '';

        if (empty($name) || empty($email) || empty($message)) {
            return new WP_Error('missing_fields', 'Name, Email, and Message are required.', array('status' => 400));
        }

        $to = 'suggest@dominopress.com';
        $subject = 'New Feature Suggestion for InsightPress';

        $body = "Name: $name\n";
        $body .= "Email: $email\n";
        $body .= "Site URL: $site_url\n";
        $body .= "Plugin: InsightPress\n\n";
        $body .= "Message:\n$message\n";

        $headers = array('Content-Type: text/plain; charset=UTF-8');
        $headers[] = 'Reply-To: ' . $name . ' <' . $email . '>';

        $sent = wp_mail($to, $subject, $body, $headers);

        if ($sent) {
            return rest_ensure_response(array('success' => true, 'message' => 'Suggestion sent successfully.'));
        } else {
            return new WP_Error('send_failed', 'Failed to send suggestion.', array('status' => 500));
        }
    }

    /**
     * Get sales analytics data.
     */
    public function get_sales_analytics($request)
    {
        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $args = array(
            'date_created' => $start_date . ' 00:00:00...' . $end_date . ' 23:59:59',
            'status' => array('wc-completed', 'wc-processing', 'wc-on-hold'),
            'limit' => -1,
            'return' => 'ids',
        );

        $order_ids = wc_get_orders($args);

        $gross_revenue = 0;
        $net_revenue = 0;
        $total_tax = 0;
        $total_shipping = 0;
        $total_discount = 0;
        $total_refunded = 0;
        $total_cogs = 0;
        $order_count = count($order_ids);
        $returned_products = array();
        $browser_data = array();
        $free_shipping_products = array();
        $device_data = array('desktop' => 0, 'mobile' => 0, 'tablet' => 0);
        $geographic_data = array();
        $unique_customers = array();
        $coupon_performance = array();

        foreach ($order_ids as $order_id) {
            $order = wc_get_order($order_id);
            if ($order) {
                $total = (float) $order->get_total();
                $tax = (float) $order->get_total_tax();
                $shipping = (float) $order->get_shipping_total();
                $discount = (float) $order->get_discount_total();
                $refunded = method_exists($order, 'get_total_refunded') ? (float) $order->get_total_refunded() : 0;

                $gross_revenue += $total + $discount + $refunded;
                $total_tax += $tax;
                $total_shipping += $shipping;
                $total_discount += $discount;
                $total_refunded += $refunded;

                // Net Revenue = Customer paid - Tax - Shipping - Refunded
                $net_revenue += ($total - $tax - $shipping - $refunded);

                // Track Device & Browser
                if (method_exists($order, 'get_customer_user_agent')) {
                    $user_agent = $order->get_customer_user_agent();
                    if ($user_agent) {
                        // Device detection
                        if (preg_match('/Tablet|iPad/i', $user_agent)) {
                            $device_data['tablet']++;
                        } elseif (preg_match('/Mobi|Android|iPhone/i', $user_agent)) {
                            $device_data['mobile']++;
                        } else {
                            $device_data['desktop']++;
                        }

                        // Simple Browser detection
                        $browser = 'Other';
                        if (preg_match('/Chrome/i', $user_agent) && !preg_match('/Edge|Edg/i', $user_agent)) {
                            $browser = 'Chrome';
                        } elseif (preg_match('/Safari/i', $user_agent) && !preg_match('/Chrome/i', $user_agent)) {
                            $browser = 'Safari';
                        } elseif (preg_match('/Firefox/i', $user_agent)) {
                            $browser = 'Firefox';
                        } elseif (preg_match('/Edge|Edg/i', $user_agent)) {
                            $browser = 'Edge';
                        }

                        if (!isset($browser_data[$browser])) {
                            $browser_data[$browser] = 0;
                        }
                        $browser_data[$browser]++;
                    }
                }

                // Track Geographic Data
                if (method_exists($order, 'get_billing_country')) {
                    $country_code = $order->get_billing_country();
                    $city = method_exists($order, 'get_billing_city') ? $order->get_billing_city() : '';
                    if ($country_code) {
                        if (!isset($geographic_data[$country_code])) {
                            $geographic_data[$country_code] = array(
                                'country' => WC()->countries->countries[$country_code] ?: $country_code,
                                'revenue' => 0,
                                'orders' => 0,
                                'cities' => array()
                            );
                        }
                        $geographic_data[$country_code]['revenue'] += ($total - $tax - $shipping - $refunded);
                        $geographic_data[$country_code]['orders']++;

                        if ($city) {
                            if (!isset($geographic_data[$country_code]['cities'][$city])) {
                                $geographic_data[$country_code]['cities'][$city] = 0;
                            }
                            $geographic_data[$country_code]['cities'][$city] += ($total - $tax - $shipping - $refunded);
                        }
                    }
                }

                // Track unique customers (by ID or Email)
                if (method_exists($order, 'get_customer_id')) {
                    $customer_id = $order->get_customer_id();
                    $customer_key = $customer_id ?: (method_exists($order, 'get_billing_email') ? $order->get_billing_email() : null);
                    if ($customer_key) {
                        if (!isset($unique_customers[$customer_key])) {
                            $unique_customers[$customer_key] = array(
                                'id' => $customer_id,
                                'email' => method_exists($order, 'get_billing_email') ? $order->get_billing_email() : '',
                                'total_spent' => 0,
                                'order_count' => 0,
                                'coupon_orders' => 0,
                                'last_order_date' => $order->get_date_created()->date('Y-m-d')
                            );
                        }
                        $unique_customers[$customer_key]['total_spent'] += $total;
                        $unique_customers[$customer_key]['order_count']++;

                        if ($discount > 0) {
                            $unique_customers[$customer_key]['coupon_orders']++;
                        }

                        $current_date = $order->get_date_created()->date('Y-m-d');
                        if (strtotime($current_date) > strtotime($unique_customers[$customer_key]['last_order_date'])) {
                            $unique_customers[$customer_key]['last_order_date'] = $current_date;
                        }
                    }
                }

                // Track coupon performance
                if (method_exists($order, 'get_coupon_codes')) {
                    $applied_coupons = $order->get_coupon_codes();
                    if (!empty($applied_coupons)) {
                        foreach ($applied_coupons as $coupon_code) {
                            if (!isset($coupon_performance[$coupon_code])) {
                                $coupon_performance[$coupon_code] = array(
                                    'code' => $coupon_code,
                                    'usage_count' => 0,
                                    'total_discount' => 0
                                );
                            }
                            $coupon_performance[$coupon_code]['usage_count']++;
                        }

                        // Note: WC doesn't easily store individual discount per coupon in the order object directly 
                        // without looking at the line items. Let's look at coupon items for precision.
                        if (method_exists($order, 'get_items')) {
                            foreach ($order->get_items('coupon') as $item) {
                                if (!method_exists($item, 'get_code') || !method_exists($item, 'get_discount')) {
                                    continue;
                                }

                                $code = $item->get_code();
                                $discount_amount = (float) $item->get_discount();
                                if (isset($coupon_performance[$code])) {
                                    $coupon_performance[$code]['total_discount'] += $discount_amount;
                                }
                            }
                        }
                    }
                }

                // Track returned products if there are refunds
                if ($refunded > 0 && method_exists($order, 'get_refunds')) {
                    foreach ($order->get_refunds() as $refund) {
                        $reason = method_exists($refund, 'get_reason') ? $refund->get_reason() : '';
                        if (method_exists($refund, 'get_items')) {
                            foreach ($refund->get_items() as $item) {
                                if (!method_exists($item, 'get_product_id')) {
                                    continue;
                                }

                                $product_id = $item->get_product_id();
                                if (!$product_id) {
                                    continue;
                                }

                                $product_name = $item->get_name();
                                $qty = abs($item->get_quantity());
                                $amount = method_exists($item, 'get_total') ? abs($item->get_total()) : 0;

                                if (!isset($returned_products[$product_id])) {
                                    $product_obj = wc_get_product($product_id);
                                    $returned_products[$product_id] = array(
                                        'name' => $product_name,
                                        'image' => $product_obj ? wp_get_attachment_image_url($product_obj->get_image_id(), 'thumbnail') : '',
                                        'count' => 0,
                                        'amount' => 0,
                                        'reasons' => array()
                                    );
                                }
                                $returned_products[$product_id]['count'] += $qty;
                                $returned_products[$product_id]['amount'] += $amount;
                                if ($reason && !in_array($reason, $returned_products[$product_id]['reasons'])) {
                                    $returned_products[$product_id]['reasons'][] = $reason;
                                }
                            }
                        }
                    }
                }

                // Calculate COGS
                foreach ($order->get_items() as $item) {
                    if (!is_a($item, 'WC_Order_Item_Product')) {
                        continue;
                    }

                    $product_id = $item->get_product_id();
                    $variation_id = $item->get_variation_id();
                    $pid = $variation_id ?: $product_id;

                    $quantity = $item->get_quantity();
                    $cogs_val = get_post_meta($pid, '_insightpress_cogs', true);

                    if (!$cogs_val && $variation_id) {
                        // Fallback to parent COGS if variation doesn't have one
                        $cogs_val = get_post_meta($product_id, '_insightpress_cogs', true);
                    }

                    if ($cogs_val) {
                        $total_cogs += ((float) $cogs_val * $quantity);
                    }

                    // Track Free Shipping Products
                    if ($shipping == 0) {
                        if (!isset($free_shipping_products[$pid])) {
                            $product_obj = wc_get_product($pid);
                            $free_shipping_products[$pid] = array(
                                'name' => $item->get_name(),
                                'image' => $product_obj ? wp_get_attachment_image_url($product_obj->get_image_id(), 'thumbnail') : '',
                                'units' => 0,
                                'orders' => 0,
                                'revenue' => 0,
                                'order_ids' => array()
                            );
                        }
                        $free_shipping_products[$pid]['units'] += $quantity;
                        $free_shipping_products[$pid]['revenue'] += (float) $item->get_total();
                        if (!in_array($order_id, $free_shipping_products[$pid]['order_ids'])) {
                            $free_shipping_products[$pid]['order_ids'][] = $order_id;
                            $free_shipping_products[$pid]['orders']++;
                        }
                    }
                }
            }
        }

        // Clean up order_ids before sending to frontend
        foreach ($free_shipping_products as &$fsp) {
            unset($fsp['order_ids']);
        }

        // Sort free shipping products by revenue
        uasort($free_shipping_products, function ($a, $b) {
            return $b['revenue'] <=> $a['revenue'];
        });
        $top_free_shipping = array_slice($free_shipping_products, 0, 10, true);

        // Sort returned products by count
        uasort($returned_products, function ($a, $b) {
            return $b['count'] - $a['count'];
        });
        $top_returned = array_slice($returned_products, 0, 5, true);

        // Sort coupon performance by discount amount
        uasort($coupon_performance, function ($a, $b) {
            return $b['total_discount'] <=> $a['total_discount'];
        });

        $average_order_value = $order_count > 0 ? ($net_revenue / $order_count) : 0;
        $customer_count = count($unique_customers);
        $purchase_frequency = $customer_count > 0 ? ($order_count / $customer_count) : 0;

        // Calculate Segmentation Metrics
        $segments = array(
            'vip' => array('count' => 0, 'label' => 'VIP Spenders', 'description' => 'Top 10% spenders or > 3 orders'),
            'bargain_hunters' => array('count' => 0, 'label' => 'Bargain Hunters', 'description' => 'Mostly buy with coupons'),
            'at_risk' => array('count' => 0, 'label' => 'At Risk', 'description' => 'Haven\'t bought in 60+ days'),
            'potential' => array('count' => 0, 'label' => 'Rising Stars', 'description' => 'First time buyers/recent'),
        );

        $threshold_vip_spent = $customer_count > 0 ? ($net_revenue / $customer_count) * 2 : 0;
        $today = time();

        foreach ($unique_customers as $customer) {
            $last_seen = strtotime($customer['last_order_date']);
            $days_since_last = floor(($today - $last_seen) / (60 * 60 * 24));

            if ($customer['total_spent'] >= $threshold_vip_spent || $customer['order_count'] >= 3) {
                $segments['vip']['count']++;
            } elseif ($customer['coupon_orders'] / $customer['order_count'] >= 0.7) {
                $segments['bargain_hunters']['count']++;
            } elseif ($days_since_last > 60) {
                $segments['at_risk']['count']++;
            } else {
                $segments['potential']['count']++;
            }
        }

        // Calculate New vs Returning Customers
        $new_customers = 0;
        $returning_customers = 0;

        foreach (array_keys($unique_customers) as $customer_key) {
            $is_returning = false;

            // Check if customer had orders before start_date
            $args_before = array(
                'status' => array('wc-completed', 'wc-processing', 'wc-on-hold'),
                'limit' => 1,
                'return' => 'ids',
                'date_created' => '<' . $start_date,
            );

            if (is_numeric($customer_key)) {
                $args_before['customer_id'] = $customer_key;
            } else {
                $args_before['billing_email'] = $customer_key;
            }

            $previous_orders = wc_get_orders($args_before);
            if (!empty($previous_orders)) {
                $returning_customers++;
            } else {
                $new_customers++;
            }
        }

        // Estimated CLV calculation: AOV * Purchase Frequency * Constant (e.g., 3 standard periods/years)
        // For simplicity in reporting, we often use historical period CLV: AOV * Frequency
        $clv_estimated = $average_order_value * $purchase_frequency;

        // Sort Geographic data by revenue
        uasort($geographic_data, function ($a, $b) {
            return $b['revenue'] <=> $a['revenue'];
        });

        // Flatten top cities
        $top_cities = array();
        foreach ($geographic_data as $country_info) {
            foreach ($country_info['cities'] as $city_name => $revenue) {
                $top_cities[] = array(
                    'city' => $city_name,
                    'country' => $country_info['country'],
                    'revenue' => $revenue
                );
            }
        }
        uasort($top_cities, function ($a, $b) {
            return $b['revenue'] <=> $a['revenue'];
        });
        $top_cities = array_slice($top_cities, 0, 10);

        // Sort browsers by count
        arsort($browser_data);

        return rest_ensure_response(array(
            'summary' => array(
                'gross_revenue' => $gross_revenue,
                'net_revenue' => $net_revenue,
                'total_tax' => $total_tax,
                'total_shipping' => $total_shipping,
                'total_discount' => $total_discount,
                'total_refunded' => $total_refunded,
                'order_count' => $order_count,
                'customer_count' => $customer_count,
                'new_customers' => $new_customers,
                'returning_customers' => $returning_customers,
                'purchase_frequency' => round($purchase_frequency, 2),
                'clv' => $clv_estimated,
                'average_order_value' => $average_order_value,
                'total_cogs' => $total_cogs,
                'actual_profit' => $net_revenue - $total_cogs,
                'refund_rate' => $order_count > 0 ? ($total_refunded / ($gross_revenue ?: 1)) * 100 : 0,
            ),
            'top_returned' => array_values($top_returned),
            'coupon_performance' => array_values($coupon_performance),
            'free_shipping_products' => array_values($top_free_shipping),
            'customer_segments' => array_values($segments),
            'geographic_distribution' => array_values($geographic_data),
            'top_cities' => array_values($top_cities),
            'device_stats' => $device_data,
            'browser_stats' => $browser_data,
            'currency_symbol' => html_entity_decode(get_woocommerce_currency_symbol()),
        ));
    }

    /**
     * Get product and inventory analytics.
     */
    public function get_product_analytics($request)
    {
        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $days_diff = (strtotime($end_date) - strtotime($start_date)) / (60 * 60 * 24);
        $days_diff = max(1, $days_diff);

        $args = array(
            'date_created' => $start_date . ' 00:00:00...' . $end_date . ' 23:59:59',
            'status' => array('wc-completed', 'wc-processing', 'wc-on-hold'),
            'limit' => -1,
            'return' => 'ids',
        );

        $order_ids = wc_get_orders($args);
        global $wpdb;
        $product_stats = array();
        $product_pairings = array();
        $category_stats = array();
        $tag_stats = array();
        $trend_data = array();

        // Initialize trend data with all days in range
        $current = strtotime($start_date);
        $last = strtotime($end_date);
        while ($current <= $last) {
            $d = date('Y-m-d', $current);
            $trend_data[$d] = array('date' => $d, 'orders' => 0, 'revenue' => 0);
            $current = strtotime('+1 day', $current);
        }

        foreach ($order_ids as $order_id) {
            $order = wc_get_order($order_id);
            if ($order) {
                $order_date = $order->get_date_created()->date('Y-m-d');
                if (isset($trend_data[$order_date])) {
                    $trend_data[$order_date]['orders']++;
                    $trend_data[$order_date]['revenue'] += (float) $order->get_total();
                }

                $items = $order->get_items();
                $order_product_ids = array();

                foreach ($items as $item) {
                    if (!method_exists($item, 'get_product_id'))
                        continue;
                    $product_id = $item->get_product_id();
                    if (!$product_id)
                        continue;

                    $order_product_ids[] = $product_id;
                    $quantity = $item->get_quantity();
                    $line_total = method_exists($item, 'get_total') ? (float) $item->get_total() : 0;

                    if (!isset($product_stats[$product_id])) {
                        $product_stats[$product_id] = array(
                            'id' => $product_id,
                            'name' => $item->get_name(),
                            'units_sold' => 0,
                            'revenue' => 0,
                            'order_count' => 0,
                            'order_ids' => array(),
                            'current_order_revenue' => array(),
                        );
                    }
                    $product_stats[$product_id]['units_sold'] += $quantity;
                    $product_stats[$product_id]['revenue'] += $line_total;
                    $product_stats[$product_id]['current_order_revenue'][$order_id] = $line_total;

                    if (!in_array($order_id, $product_stats[$product_id]['order_ids'])) {
                        $product_stats[$product_id]['order_ids'][] = $order_id;
                        $product_stats[$product_id]['order_count']++;
                    }

                    // Track Categories & Tags
                    if (method_exists($item, 'get_product')) {
                        $product_obj = $item->get_product();
                        if ($product_obj) {
                            // Categories
                            $categories = wp_get_post_terms($product_obj->get_id(), 'product_cat', array('fields' => 'names'));
                            foreach ($categories as $cat_name) {
                                if (!isset($category_stats[$cat_name])) {
                                    $category_stats[$cat_name] = array('name' => $cat_name, 'revenue' => 0, 'units_sold' => 0, 'order_count' => 0, 'order_ids' => array());
                                }
                                $category_stats[$cat_name]['revenue'] += $line_total;
                                $category_stats[$cat_name]['units_sold'] += $quantity;
                                if (!in_array($order_id, $category_stats[$cat_name]['order_ids'])) {
                                    $category_stats[$cat_name]['order_ids'][] = $order_id;
                                    $category_stats[$cat_name]['order_count']++;
                                }
                            }

                            // Tags
                            $tags = wp_get_post_terms($product_obj->get_id(), 'product_tag', array('fields' => 'names'));
                            foreach ($tags as $tag_name) {
                                if (!isset($tag_stats[$tag_name])) {
                                    $tag_stats[$tag_name] = array('name' => $tag_name, 'revenue' => 0, 'units_sold' => 0, 'order_count' => 0, 'order_ids' => array());
                                }
                                $tag_stats[$tag_name]['revenue'] += $line_total;
                                $tag_stats[$tag_name]['units_sold'] += $quantity;
                                if (!in_array($order_id, $tag_stats[$tag_name]['order_ids'])) {
                                    $tag_stats[$tag_name]['order_ids'][] = $order_id;
                                    $tag_stats[$tag_name]['order_count']++;
                                }
                            }
                        }
                    }
                } // end foreach items

                // Track product pairings for Frequently Bought Together
                $order_product_ids = array_values(array_unique($order_product_ids));
                if (count($order_product_ids) > 1) {
                    for ($i = 0; $i < count($order_product_ids); $i++) {
                        for ($j = $i + 1; $j < count($order_product_ids); $j++) {
                            $p1 = $order_product_ids[$i];
                            $p2 = $order_product_ids[$j];
                            $pair = $p1 < $p2 ? "$p1-$p2" : "$p2-$p1";

                            // Calculate combined revenue for this specific pair in this order
                            $pair_revenue = (isset($product_stats[$p1]) ? $product_stats[$p1]['current_order_revenue'][$order_id] : 0) +
                                (isset($product_stats[$p2]) ? $product_stats[$p2]['current_order_revenue'][$order_id] : 0);

                            if (!isset($product_pairings[$pair])) {
                                $product_pairings[$pair] = array('p1' => $p1, 'p2' => $p2, 'count' => 0, 'revenue' => 0);
                            }
                            $product_pairings[$pair]['count']++;
                            $product_pairings[$pair]['revenue'] += $pair_revenue;
                        }
                    }
                }
            } // end if ($order)
        } // end foreach ($order_ids)

        // Get all products to identify "dead stock" (worst performing)
        $all_products = wc_get_products(array('limit' => -1, 'status' => 'publish'));
        $inventory_data = array();

        foreach ($all_products as $product) {
            $pid = $product->get_id();
            $sold = isset($product_stats[$pid]) ? $product_stats[$pid]['units_sold'] : 0;
            $rev = isset($product_stats[$pid]) ? $product_stats[$pid]['revenue'] : 0;

            $prod_cogs = get_post_meta($pid, '_insightpress_cogs', true) ?: 0;
            $total_prod_cogs = (float) $prod_cogs * $sold;
            $profit = $rev - $total_prod_cogs;

            $velocity = $sold / $days_diff;
            $stock = (int) $product->get_stock_quantity();
            $runway = ($velocity > 0 && $stock > 0) ? floor($stock / $velocity) : null;

            // Fetch clicks (views) from events table
            $events_table = InsightPress_Product_Tracker::get_table_name();
            $clicks = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM $events_table WHERE product_id = %d AND event_type = 'view' AND created_at BETWEEN %s AND %s",
                $pid,
                $start_date . ' 00:00:00',
                $end_date . ' 23:59:59'
            ));

            $inventory_data[] = array(
                'id' => $pid,
                'name' => $product->get_name(),
                'sku' => $product->get_sku(),
                'image' => wp_get_attachment_image_url($product->get_image_id(), 'thumbnail') ?: '',
                'stock' => $stock,
                'units_sold' => $sold,
                'order_count' => isset($product_stats[$pid]) ? $product_stats[$pid]['order_count'] : 0,
                'clicks' => $clicks,
                'revenue' => $rev,
                'cogs' => $total_prod_cogs,
                'profit' => $profit,
                'price' => $product->get_price(),
                'velocity' => round($velocity, 2),
                'runway_days' => $runway,
            );
        }

        // Filter and Sort for Top Performing (Only items with sales)
        $top_performing = array_filter($inventory_data, function ($item) {
            return $item['units_sold'] > 0;
        });
        usort($top_performing, function ($a, $b) {
            return $b['units_sold'] <=> $a['units_sold'];
        });
        $top_performing = array_slice($top_performing, 0, 10);

        // Sort for Worst Performing (low/zero sales, but still in stock)
        $worst_performing_candidates = array_filter($inventory_data, function ($item) {
            return $item['stock'] > 0;
        });
        usort($worst_performing_candidates, function ($a, $b) {
            // First by units sold (ascending), then by stock (descending)
            if ($a['units_sold'] === $b['units_sold']) {
                return $b['stock'] <=> $a['stock'];
            }
            return $a['units_sold'] <=> $b['units_sold'];
        });
        $worst_performing = array_slice($worst_performing_candidates, 0, 10);

        // Sort for Dead Stock (stock <= 0)
        $dead_stock_candidates = array_filter($inventory_data, function ($item) {
            return $item['stock'] <= 0;
        });
        // Typically, dead stock can just be sorted by name or highest price, etc. We'll sort by units sold ascending just for consistency.
        usort($dead_stock_candidates, function ($a, $b) {
            if ($a['units_sold'] === $b['units_sold']) {
                return $a['stock'] <=> $b['stock'];
            }
            return $a['units_sold'] <=> $b['units_sold'];
        });
        $dead_stock = array_slice($dead_stock_candidates, 0, 10);

        // Depletion forecasting (top 10 closest to out of stock)
        uasort($category_stats, function ($a, $b) {
            return $b['revenue'] <=> $a['revenue'];
        });

        // Sort Tags by Revenue
        uasort($tag_stats, function ($a, $b) {
            return $b['revenue'] <=> $a['revenue'];
        });

        // Depletion forecasting (top 10 closest to out of stock)
        $forecasting = array_filter($inventory_data, function ($item) {
            return $item['runway_days'] !== null && $item['runway_days'] < 90; // Show items running out in < 3 months
        });

        usort($forecasting, function ($a, $b) {
            return $a['runway_days'] <=> $b['runway_days'];
        });

        return rest_ensure_response(array(
            'top_performing' => $top_performing,
            'worst_performing' => $worst_performing,
            'dead_stock' => $dead_stock,
            'inventory_forecasting' => array_slice($forecasting, 0, 10),
            'category_performance' => array_map(function ($item) {
                unset($item['order_ids']);
                return $item;
            }, array_values($category_stats)),
            'tag_performance' => array_map(function ($item) {
                unset($item['order_ids']);
                return $item;
            }, array_values($tag_stats)),
            'trend_data' => array_values($trend_data),
            'currency_symbol' => html_entity_decode(get_woocommerce_currency_symbol()),
            'currency_code' => get_woocommerce_currency(),
        ));
    }

    /**
     * Get traffic & UTM attribution analytics.
     */
    public function get_traffic_analytics($request)
    {
        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $args = array(
            'date_created' => $start_date . '...' . $end_date,
            'status' => array('wc-completed', 'wc-processing'),
            'limit' => -1,
            'return' => 'ids',
        );

        $order_ids = wc_get_orders($args);

        $by_source = array();
        $by_medium = array();
        $by_campaign = array();
        $campaign_detail = array();
        $total_attributed_orders = 0;
        $total_attributed_revenue = 0;

        foreach ($order_ids as $order_id) {
            $order = wc_get_order($order_id);
            if (!$order)
                continue;

            $source = get_post_meta($order_id, '_insightpress_utm_source', true);
            $medium = get_post_meta($order_id, '_insightpress_utm_medium', true);
            $campaign = get_post_meta($order_id, '_insightpress_utm_campaign', true);

            if (!$source && !$medium && !$campaign)
                continue;

            $revenue = (float) $order->get_total();
            $total_attributed_orders++;
            $total_attributed_revenue += $revenue;

            // By Source
            $src_key = strtolower($source ?: 'unknown');
            if (!isset($by_source[$src_key])) {
                $by_source[$src_key] = array('name' => $src_key, 'orders' => 0, 'revenue' => 0);
            }
            $by_source[$src_key]['orders']++;
            $by_source[$src_key]['revenue'] += $revenue;

            // By Medium
            $med_key = strtolower($medium ?: 'unknown');
            if (!isset($by_medium[$med_key])) {
                $by_medium[$med_key] = array('name' => $med_key, 'orders' => 0, 'revenue' => 0);
            }
            $by_medium[$med_key]['orders']++;
            $by_medium[$med_key]['revenue'] += $revenue;

            // By Campaign
            $camp_key = strtolower($campaign ?: 'unknown');
            if (!isset($by_campaign[$camp_key])) {
                $by_campaign[$camp_key] = array('name' => $camp_key, 'orders' => 0, 'revenue' => 0);
            }
            $by_campaign[$camp_key]['orders']++;
            $by_campaign[$camp_key]['revenue'] += $revenue;

            // Full campaign detail row
            $campaign_detail[] = array(
                'source' => $source,
                'medium' => $medium,
                'campaign' => $campaign,
                'orders' => 1,
                'revenue' => $revenue,
                'order_id' => $order_id,
            );
        }

        // Sort all by revenue desc
        $sort_revenue = function ($a, $b) {
            return $b['revenue'] <=> $a['revenue'];
        };
        uasort($by_source, $sort_revenue);
        uasort($by_medium, $sort_revenue);
        uasort($by_campaign, $sort_revenue);
        usort($campaign_detail, $sort_revenue);

        return rest_ensure_response(array(
            'total_attributed_orders' => $total_attributed_orders,
            'total_attributed_revenue' => $total_attributed_revenue,
            'by_source' => array_values($by_source),
            'by_medium' => array_values($by_medium),
            'by_campaign' => array_values($by_campaign),
            'campaign_detail' => array_slice($campaign_detail, 0, 50),
            'currency_symbol' => html_entity_decode(get_woocommerce_currency_symbol()),
        ));
    }

    /**
     * Get cart abandonment analytics.
     */
    public function get_cart_abandonment_analytics($request)
    {
        global $wpdb;

        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $table = InsightPress_Cart_Tracker::get_table_name();

        // Check table exists
        if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
            return rest_ensure_response(array(
                'total_abandoned' => 0,
                'total_converted' => 0,
                'abandonment_rate' => 0,
                'lost_revenue' => 0,
                'recovered_revenue' => 0,
                'top_abandoned_products' => array(),
                'daily_trend' => array(),
                'currency_symbol' => html_entity_decode(get_woocommerce_currency_symbol()),
            ));
        }

        $start_dt = $start_date . ' 00:00:00';
        $end_dt = $end_date . ' 23:59:59';

        // Totals by status
        $stats = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT status, COUNT(*) as count, SUM(cart_total) as total_revenue
                 FROM $table
                 WHERE updated_at BETWEEN %s AND %s
                 GROUP BY status",
                $start_dt,
                $end_dt
            ),
            ARRAY_A
        );

        $total_abandoned = 0;
        $total_converted = 0;
        $lost_revenue = 0;
        $recovered_revenue = 0;

        foreach ($stats as $row) {
            if ($row['status'] === 'abandoned') {
                $total_abandoned = (int) $row['count'];
                $lost_revenue = (float) $row['total_revenue'];
            } elseif ($row['status'] === 'converted') {
                $total_converted = (int) $row['count'];
                $recovered_revenue = (float) $row['total_revenue'];
            }
        }

        $total_carts = $total_abandoned + $total_converted;
        $abandonment_rate = $total_carts > 0 ? round(($total_abandoned / $total_carts) * 100, 1) : 0;

        // Daily trend
        $daily = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT DATE(updated_at) as date, status, COUNT(*) as count, SUM(cart_total) as revenue
                 FROM $table
                 WHERE updated_at BETWEEN %s AND %s AND status IN ('abandoned', 'converted')
                 GROUP BY DATE(updated_at), status
                 ORDER BY date ASC",
                $start_dt,
                $end_dt
            ),
            ARRAY_A
        );

        // Pivot daily into {date, abandoned, converted}
        $daily_map = array();
        foreach ($daily as $row) {
            $d = $row['date'];
            if (!isset($daily_map[$d])) {
                $daily_map[$d] = array('date' => $d, 'abandoned' => 0, 'converted' => 0, 'lost_revenue' => 0);
            }
            if ($row['status'] === 'abandoned') {
                $daily_map[$d]['abandoned'] += (int) $row['count'];
                $daily_map[$d]['lost_revenue'] += (float) $row['revenue'];
            } else {
                $daily_map[$d]['converted'] += (int) $row['count'];
            }
        }

        // Calculate daily conversion rate
        foreach ($daily_map as $date => $val) {
            $total = $val['abandoned'] + $val['converted'];
            $daily_map[$date]['conversion_rate'] = $total > 0 ? round(($val['converted'] / $total) * 100, 1) : 0;
        }

        // Top abandoned products — parse cart_contents JSON
        $abandoned_carts = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT cart_contents FROM $table
                 WHERE status = 'abandoned' AND updated_at BETWEEN %s AND %s
                 LIMIT 200",
                $start_dt,
                $end_dt
            )
        );

        $product_counts = array();
        foreach ($abandoned_carts as $cart_json) {
            $cart_items = json_decode($cart_json, true);
            if (!is_array($cart_items))
                continue;

            foreach ($cart_items as $item) {
                $pid = isset($item['product_id']) ? (int) $item['product_id'] : 0;
                $qty = isset($item['quantity']) ? (int) $item['quantity'] : 1;
                $subtotal = isset($item['line_subtotal']) ? (float) $item['line_subtotal'] : 0;
                if (!$pid)
                    continue;

                if (!isset($product_counts[$pid])) {
                    $product = wc_get_product($pid);
                    $product_counts[$pid] = array(
                        'id' => $pid,
                        'name' => $product ? $product->get_name() : "Product #$pid",
                        'times_abandoned' => 0,
                        'qty_abandoned' => 0,
                        'lost_revenue' => 0,
                    );
                }
                $product_counts[$pid]['times_abandoned']++;
                $product_counts[$pid]['qty_abandoned'] += $qty;
                $product_counts[$pid]['lost_revenue'] += $subtotal;
            }
        }

        usort($product_counts, function ($a, $b) {
            return $b['times_abandoned'] <=> $a['times_abandoned'];
        });

        return rest_ensure_response(array(
            'total_abandoned' => $total_abandoned,
            'total_converted' => $total_converted,
            'abandonment_rate' => $abandonment_rate,
            'lost_revenue' => $lost_revenue,
            'recovered_revenue' => $recovered_revenue,
            'top_abandoned_products' => array_slice($product_counts, 0, 10),
            'daily_trend' => array_values($daily_map),
            'currency_symbol' => html_entity_decode(get_woocommerce_currency_symbol()),
        ));
    }

    /**
     * Get checkout funnel drop-off analytics.
     */
    public function get_funnel_analytics($request)
    {
        global $wpdb;

        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $table = InsightPress_Funnel_Tracker::get_table_name();

        if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
            return rest_ensure_response(array('steps' => array(), 'total_sessions' => 0));
        }

        $start_dt = $start_date . ' 00:00:00';
        $end_dt = $end_date . ' 23:59:59';

        $step_order = array(
            'cart_viewed',
            'checkout_initiated',
            'billing_filled',
            'shipping_calculated',
            'payment_selected',
            'order_placed',
        );

        $step_labels = array(
            'cart_viewed' => 'Cart Viewed',
            'checkout_initiated' => 'Checkout Page',
            'billing_filled' => 'Billing Filled',
            'shipping_calculated' => 'Shipping Calculated',
            'payment_selected' => 'Payment Selected',
            'order_placed' => 'Order Placed',
        );

        // Count distinct sessions per step within date range
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT step, COUNT(DISTINCT session_id) as sessions
                 FROM $table
                 WHERE created_at BETWEEN %s AND %s
                 GROUP BY step",
                $start_dt,
                $end_dt
            ),
            ARRAY_A
        );

        $counts = array();
        foreach ($rows as $row) {
            $counts[$row['step']] = (int) $row['sessions'];
        }

        // Build ordered steps array with drop-off metrics
        $steps = array();
        $prev = null;
        $top = isset($counts['cart_viewed']) ? max($counts['cart_viewed'], 1) : 1;

        foreach ($step_order as $step) {
            $count = isset($counts[$step]) ? $counts[$step] : 0;
            $dropoff = $prev !== null ? max(0, $prev - $count) : 0;
            $dropoff_rate = $prev !== null && $prev > 0 ? round(($dropoff / $prev) * 100, 1) : 0;
            $reach_rate = $top > 0 ? round(($count / $top) * 100, 1) : 0;

            $steps[] = array(
                'step' => $step,
                'label' => $step_labels[$step],
                'sessions' => $count,
                'dropoff' => $dropoff,
                'dropoff_rate' => $dropoff_rate,
                'reach_rate' => $reach_rate,
            );
            $prev = $count;
        }

        // Daily trend: count of sessions reaching order_placed per day
        $daily = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT DATE(created_at) as date, step, COUNT(DISTINCT session_id) as sessions
                 FROM $table
                 WHERE created_at BETWEEN %s AND %s
                 GROUP BY DATE(created_at), step
                 ORDER BY date ASC",
                $start_dt,
                $end_dt
            ),
            ARRAY_A
        );

        $daily_map = array();
        foreach ($daily as $row) {
            $d = $row['date'];
            if (!isset($daily_map[$d])) {
                $daily_map[$d] = array('date' => $d);
                foreach ($step_order as $s) {
                    $daily_map[$d][$s] = 0;
                }
            }
            if (isset($daily_map[$d][$row['step']])) {
                $daily_map[$d][$row['step']] += (int) $row['sessions'];
            }
        }

        return rest_ensure_response(array(
            'steps' => $steps,
            'total_sessions' => isset($counts['cart_viewed']) ? $counts['cart_viewed'] : 0,
            'daily_trend' => array_values($daily_map),
        ));
    }

    /**
     * Get Time to Purchase analytics.
     * Measures days and site visits between a user's first interaction and completed order.
     */
    public function get_time_to_purchase_analytics($request)
    {
        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $order_ids = wc_get_orders(array(
            'date_created' => $start_date . '...' . $end_date,
            'status' => array('wc-completed', 'wc-processing'),
            'limit' => -1,
            'return' => 'ids',
        ));

        // Accumulators
        $days_values = array();
        $sessions_values = array();
        $pageviews_values = array();

        // Day buckets: 0=same day, 1-3, 4-7, 8-30, 30+
        $day_buckets = array(
            'same_day' => 0,  // 0 days
            '1_3_days' => 0,
            '4_7_days' => 0,
            '8_30_days' => 0,
            '30_plus' => 0,
        );

        // Session buckets: 1, 2-3, 4-5, 6-10, 10+
        $session_buckets = array(
            '1_session' => 0,
            '2_3_sessions' => 0,
            '4_5_sessions' => 0,
            '6_10_sessions' => 0,
            '10_plus' => 0,
        );

        $orders_with_data = 0;

        foreach ($order_ids as $order_id) {
            $days = get_post_meta($order_id, '_insightpress_days_to_purchase', true);
            $sessions = get_post_meta($order_id, '_insightpress_sessions_to_purchase', true);
            $pageviews = get_post_meta($order_id, '_insightpress_page_views_to_purchase', true);

            if ($days === '' && $sessions === '')
                continue;
            $orders_with_data++;

            // Days distribution
            if ($days !== '') {
                $d = (int) $days;
                $days_values[] = $d;
                if ($d === 0)
                    $day_buckets['same_day']++;
                elseif ($d <= 3)
                    $day_buckets['1_3_days']++;
                elseif ($d <= 7)
                    $day_buckets['4_7_days']++;
                elseif ($d <= 30)
                    $day_buckets['8_30_days']++;
                else
                    $day_buckets['30_plus']++;
            }

            // Session distribution
            if ($sessions !== '') {
                $s = (int) $sessions;
                $sessions_values[] = $s;
                if ($s <= 1)
                    $session_buckets['1_session']++;
                elseif ($s <= 3)
                    $session_buckets['2_3_sessions']++;
                elseif ($s <= 5)
                    $session_buckets['4_5_sessions']++;
                elseif ($s <= 10)
                    $session_buckets['6_10_sessions']++;
                else
                    $session_buckets['10_plus']++;
            }

            if ($pageviews !== '') {
                $pageviews_values[] = (int) $pageviews;
            }
        }

        // Averages and medians
        $avg_days = count($days_values) > 0 ? round(array_sum($days_values) / count($days_values), 1) : 0;
        $avg_sessions = count($sessions_values) > 0 ? round(array_sum($sessions_values) / count($sessions_values), 1) : 0;
        $avg_pageviews = count($pageviews_values) > 0 ? round(array_sum($pageviews_values) / count($pageviews_values), 1) : 0;

        // Median days
        $median_days = 0;
        if (count($days_values) > 0) {
            sort($days_values);
            $mid = (int) floor(count($days_values) / 2);
            $median_days = count($days_values) % 2 === 0
                ? round(($days_values[$mid - 1] + $days_values[$mid]) / 2, 1)
                : $days_values[$mid];
        }

        // Build chart-ready day bucket array
        $day_distribution = array(
            array('label' => 'Same Day', 'key' => 'same_day', 'orders' => $day_buckets['same_day']),
            array('label' => '1–3 Days', 'key' => '1_3_days', 'orders' => $day_buckets['1_3_days']),
            array('label' => '4–7 Days', 'key' => '4_7_days', 'orders' => $day_buckets['4_7_days']),
            array('label' => '8–30 Days', 'key' => '8_30_days', 'orders' => $day_buckets['8_30_days']),
            array('label' => '30+ Days', 'key' => '30_plus', 'orders' => $day_buckets['30_plus']),
        );

        $session_distribution = array(
            array('label' => '1 Visit', 'key' => '1_session', 'orders' => $session_buckets['1_session']),
            array('label' => '2–3 Visits', 'key' => '2_3_sessions', 'orders' => $session_buckets['2_3_sessions']),
            array('label' => '4–5 Visits', 'key' => '4_5_sessions', 'orders' => $session_buckets['4_5_sessions']),
            array('label' => '6–10 Visits', 'key' => '6_10_sessions', 'orders' => $session_buckets['6_10_sessions']),
            array('label' => '10+ Visits', 'key' => '10_plus', 'orders' => $session_buckets['10_plus']),
        );

        return rest_ensure_response(array(
            'total_orders_tracked' => $orders_with_data,
            'avg_days_to_purchase' => $avg_days,
            'median_days' => $median_days,
            'avg_sessions' => $avg_sessions,
            'avg_pageviews' => $avg_pageviews,
            'day_distribution' => $day_distribution,
            'session_distribution' => $session_distribution,
        ));
    }

    /**
     * Get single product analytics.
     */
    public function get_single_product_analytics($request)
    {
        $product_id = (int) $request['id'];
        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $product = wc_get_product($product_id);
        if (!$product) {
            return new WP_Error('invalid_product', 'Product not found.', array('status' => 404));
        }

        global $wpdb;

        // --- Sales Metrics using WC Data Store for better accuracy ---
        // WC 4.0+ uses a lookup table which is much faster and accurate for analytics
        $lookup_table = $wpdb->prefix . 'wc_order_product_lookup';
        $stats_table = $wpdb->prefix . 'wc_order_stats';

        // Handle variations
        $product_ids = [$product_id];
        if ($product->is_type('variable')) {
            $product_ids = array_merge($product_ids, $product->get_children());
        }
        $product_ids_placeholder = implode(',', array_fill(0, count($product_ids), '%d'));

        // Align with standard analytics statuses
        $valid_statuses = array('wc-completed', 'wc-processing', 'wc-on-hold');

        $query = $wpdb->prepare(
            "SELECT 
                SUM(lookup.product_net_revenue) as revenue,
                SUM(lookup.product_qty) as qty,
                COUNT(DISTINCT lookup.order_id) as order_count
            FROM $lookup_table lookup
            INNER JOIN $stats_table stats ON lookup.order_id = stats.order_id
            WHERE lookup.product_id IN ($product_ids_placeholder)
            AND stats.status IN ('" . implode("','", $valid_statuses) . "')
            AND lookup.date_created >= %s AND lookup.date_created <= %s",
            ...array_merge($product_ids, [$start_date . ' 00:00:00', $end_date . ' 23:59:59'])
        );

        $sales_data = $wpdb->get_row($query);

        $total_revenue = (float) ($sales_data->revenue ?? 0);
        $total_orders = (int) ($sales_data->order_count ?? 0);
        $qty_sold = (int) ($sales_data->qty ?? 0);
        $aov = $total_orders > 0 ? $total_revenue / $total_orders : 0;

        // If lookup table is empty (e.g. not populated), fallback to the older method but keep it precise
        if ($total_orders === 0 && $total_revenue == 0) {
            // Precise manual fallback using order items
            $order_items_table = $wpdb->prefix . 'woocommerce_order_items';
            $order_itemmeta_table = $wpdb->prefix . 'woocommerce_order_itemmeta';

            $fallback_query = $wpdb->prepare(
                "SELECT 
                    SUM(CAST(im_total.meta_value AS DECIMAL(19,4))) as revenue,
                    SUM(CAST(im_qty.meta_value AS DECIMAL(19,4))) as qty,
                    COUNT(DISTINCT items.order_id) as order_count
                FROM $order_items_table items
                INNER JOIN $order_itemmeta_table im_product ON items.order_item_id = im_product.order_item_id 
                    AND im_product.meta_key IN ('_product_id', '_variation_id') 
                    AND im_product.meta_value IN ($product_ids_placeholder)
                INNER JOIN $order_itemmeta_table im_total ON items.order_item_id = im_total.order_item_id AND im_total.meta_key = '_line_total'
                INNER JOIN $order_itemmeta_table im_qty ON items.order_item_id = im_qty.order_item_id AND im_qty.meta_key = '_qty'
                INNER JOIN $stats_table stats ON items.order_id = stats.order_id
                WHERE stats.status IN ('wc-completed', 'wc-processing', 'wc-on-hold')
                AND stats.date_created >= %s AND stats.date_created <= %s",
                ...array_merge($product_ids, [$start_date . ' 00:00:00', $end_date . ' 23:59:59'])
            );
            $fallback_data = $wpdb->get_row($fallback_query);
            $total_revenue = (float) ($fallback_data->revenue ?? 0);
            $total_orders = (int) ($fallback_data->order_count ?? 0);
            $qty_sold = (int) ($fallback_data->qty ?? 0);
            $aov = $total_orders > 0 ? $total_revenue / $total_orders : 0;
        }

        // Refund Calculations
        // Refunds in WC are stored in different ways depending on the version. 
        // We'll calculate it from the lookup table as well for consistency.
        $refund_query = $wpdb->prepare(
            "SELECT ABS(SUM(product_net_revenue)) as refunded
            FROM $lookup_table
            WHERE product_id IN ($product_ids_placeholder)
            AND product_net_revenue < 0
            AND date_created >= %s AND date_created <= %s",
            ...array_merge($product_ids, [$start_date . ' 00:00:00', $end_date . ' 23:59:59'])
        );
        $refund_amount = (float) ($wpdb->get_var($refund_query) ?? 0);
        $net_revenue = $total_revenue - $refund_amount;

        // --- Revenue Trend ---
        $trends_query = $wpdb->prepare(
            "SELECT 
                DATE(date_created) as date,
                SUM(product_net_revenue) as revenue,
                SUM(product_qty) as qty,
                COUNT(DISTINCT order_id) as orders
            FROM $lookup_table
            WHERE product_id IN ($product_ids_placeholder)
            AND date_created >= %s AND date_created <= %s
            GROUP BY DATE(date_created)
            ORDER BY date ASC",
            ...array_merge($product_ids, [$start_date . ' 00:00:00', $end_date . ' 23:59:59'])
        );
        $trends = $wpdb->get_results($trends_query);

        // --- Engagement Metrics ---
        $events_table = $wpdb->prefix . 'insightpress_product_events';
        $engagement = array();
        if ($wpdb->get_var("SHOW TABLES LIKE '$events_table'") === $events_table) {
            $engagement = $wpdb->get_results($wpdb->prepare(
                "SELECT event_type, COUNT(*) as count, COUNT(DISTINCT session_id) as unique_count
                FROM $events_table
                WHERE product_id = %d
                AND created_at >= %s AND created_at <= %s
                GROUP BY event_type",
                $product_id,
                $start_date . ' 00:00:00',
                $end_date . ' 23:59:59'
            ));
        }

        $metrics = array(
            'views' => 0,
            'unique_visitors' => 0,
            'add_to_cart' => 0,
            'checkout_initiated' => 0,
        );

        foreach ($engagement as $row) {
            if ($row->event_type === 'view') {
                $metrics['views'] = (int) $row->count;
                $metrics['unique_visitors'] = (int) $row->unique_count;
            } elseif ($row->event_type === 'add_to_cart') {
                $metrics['add_to_cart'] = (int) $row->count;
            } elseif ($row->event_type === 'checkout_initiated') {
                $metrics['checkout_initiated'] = (int) $row->count;
            }
        }

        // --- Traffic Source Analysis ---
        $sources_data = array();
        if ($wpdb->get_var("SHOW TABLES LIKE '$events_table'") === $events_table) {
            $sources_results = $wpdb->get_results($wpdb->prepare(
                "SELECT source, COUNT(*) as count, COUNT(DISTINCT session_id) as unique_count
                FROM $events_table
                WHERE product_id = %d
                AND created_at >= %s AND created_at <= %s
                GROUP BY source
                ORDER BY count DESC",
                $product_id,
                $start_date . ' 00:00:00',
                $end_date . ' 23:59:59'
            ));

            foreach ($sources_results as $row) {
                // Calculate 'purchased' for this specific source (approximate based on sessions)
                // In a more complex system, we'd link session_id to order_id.
                // For now, providing view/visit breakdown per source.
                $sources_data[] = array(
                    'source' => $row->source,
                    'views' => (int) $row->count,
                    'users' => (int) $row->unique_count,
                    'percent' => $metrics['views'] > 0 ? round(($row->count / $metrics['views']) * 100, 1) : 0
                );
            }
        }

        // --- Customer Analytics Aggregation ---
        $customer_stats = array('new' => 0, 'returning' => 0);
        $device_stats = array('Mobile' => 0, 'Tablet' => 0, 'Desktop' => 0);
        $browser_stats = array();
        $country_stats = array();
        $city_stats = array();

        if ($wpdb->get_var("SHOW TABLES LIKE '$events_table'") === $events_table) {
            $customer_results = $wpdb->get_results($wpdb->prepare(
                "SELECT customer_type, device_type, browser, country, city, COUNT(*) as count
                FROM $events_table
                WHERE product_id = %d
                AND created_at >= %s AND created_at <= %s
                GROUP BY customer_type, device_type, browser, country, city",
                $product_id,
                $start_date . ' 00:00:00',
                $end_date . ' 23:59:59'
            ));

            foreach ($customer_results as $row) {
                // Customer Type
                $type_key = strtolower($row->customer_type);
                if (isset($customer_stats[$type_key])) {
                    $customer_stats[$type_key] += (int) $row->count;
                }

                // Device
                if (isset($device_stats[$row->device_type])) {
                    $device_stats[$row->device_type] += (int) $row->count;
                }

                // Browser
                if (!isset($browser_stats[$row->browser])) {
                    $browser_stats[$row->browser] = 0;
                }
                $browser_stats[$row->browser] += (int) $row->count;

                // Country
                if (!isset($country_stats[$row->country])) {
                    $country_name = class_exists('WC') && isset(WC()->countries->countries[$row->country]) ? WC()->countries->countries[$row->country] : $row->country;
                    $country_stats[$row->country] = array('name' => $country_name, 'count' => 0);
                }
                $country_stats[$row->country]['count'] += (int) $row->count;

                // City
                if ($row->city && $row->city !== 'Unknown') {
                    if (!isset($city_stats[$row->city])) {
                        $city_stats[$row->city] = 0;
                    }
                    $city_stats[$row->city] += (int) $row->count;
                }
            }
        }

        // Final sorting/formatting
        arsort($browser_stats);
        uasort($country_stats, function ($a, $b) {
            return $b['count'] - $a['count'];
        });
        arsort($city_stats);

        $start_timestamp = $start_date . ' 00:00:00';
        $end_timestamp = $end_date . ' 23:59:59';

        $conversion_rate = $metrics['unique_visitors'] > 0 ? ($total_orders / $metrics['unique_visitors']) * 100 : 0;

        // --- Funnel Logic ---
        $funnel = [
            [
                'label' => __('Product Views', 'insightpress-advanced-analytics-for-woocommerce'),
                'value' => $metrics['views'],
                'unique' => $metrics['unique_visitors'],
                'percent' => 100,
                'dropoff' => 0
            ],
            [
                'label' => __('Add to Cart', 'insightpress-advanced-analytics-for-woocommerce'),
                'value' => $metrics['add_to_cart'],
                'unique' => $metrics['add_to_cart'], // Unique add to carts not tracked separately yet
                'percent' => $metrics['views'] > 0 ? round(($metrics['add_to_cart'] / $metrics['views']) * 100, 2) : 0,
                'dropoff' => $metrics['views'] > 0 ? round((1 - ($metrics['add_to_cart'] / $metrics['views'])) * 100, 2) : 0
            ],
            [
                'label' => __('Checkout Initiated', 'insightpress-advanced-analytics-for-woocommerce'),
                'value' => $metrics['checkout_initiated'],
                'unique' => $metrics['checkout_initiated'],
                'percent' => $metrics['add_to_cart'] > 0 ? round(($metrics['checkout_initiated'] / $metrics['add_to_cart']) * 100, 2) : 0,
                'dropoff' => $metrics['add_to_cart'] > 0 ? round((1 - ($metrics['checkout_initiated'] / $metrics['add_to_cart'])) * 100, 2) : 0
            ],
            [
                'label' => __('Purchased', 'insightpress-advanced-analytics-for-woocommerce'),
                'value' => $total_orders,
                'unique' => $total_orders,
                'percent' => $metrics['checkout_initiated'] > 0 ? round(($total_orders / $metrics['checkout_initiated']) * 100, 2) : 0,
                'dropoff' => $metrics['checkout_initiated'] > 0 ? round((1 - ($total_orders / $metrics['checkout_initiated'])) * 100, 2) : 0
            ]
        ];

        $prod_cogs = get_post_meta($product_id, '_insightpress_cogs', true) ?: 0;
        $total_cogs = (float) $prod_cogs * $qty_sold;

        return rest_ensure_response(array(
            'product_name' => $product->get_name(),
            'currency_symbol' => html_entity_decode(get_woocommerce_currency_symbol()),
            'sales' => array(
                'revenue' => round($total_revenue, 2),
                'orders' => $total_orders,
                'qty' => $qty_sold,
                'aov' => round($aov, 2),
                'refund_amount' => round($refund_amount, 2),
                'net_revenue' => round($net_revenue, 2),
                'total_cogs' => round($total_cogs, 2),
                'actual_profit' => round($net_revenue - $total_cogs, 2),
            ),
            'engagement' => array(
                'views' => $metrics['views'],
                'unique_visitors' => $metrics['unique_visitors'],
                'add_to_cart' => $metrics['add_to_cart'],
                'checkout_initiated' => $metrics['checkout_initiated'],
                'conversion_rate' => round($conversion_rate, 2),
            ),
            'funnel' => $funnel,
            'trends' => $trends,
            'sources' => $sources_data,
            'customers' => array(
                'types' => $customer_stats,
                'geo' => array(
                    'countries' => array_slice(array_values($country_stats), 0, 5),
                    'cities' => array_slice($city_stats, 0, 5)
                ),
                'devices' => $device_stats,
                'browsers' => array_slice($browser_stats, 0, 5)
            )
        ));
    }

    /**
     * Get single post analytics.
     */
    public function get_single_post_analytics($request)
    {
        global $wpdb;
        $post_id = $request->get_param('id');
        $start_date = $request->get_param('start_date') ?: date('Y-m-d', strtotime('-30 days'));
        $end_date = $request->get_param('end_date') ?: date('Y-m-d');

        $table = InsightPress_Post_Tracker::get_table_name();

        if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
            return new WP_Error('no_table', 'Analytics table not found.', array('status' => 500));
        }

        $post = get_post($post_id);
        if (!$post) {
            return new WP_Error('no_post', 'Post not found.', array('status' => 404));
        }

        // Metrics Query
        $metrics = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                SUM(CASE WHEN event_type = 'view' THEN 1 ELSE 0 END) as views,
                COUNT(DISTINCT CASE WHEN event_type = 'view' THEN session_id ELSE NULL END) as unique_visitors,
                AVG(CASE WHEN event_type = 'view' THEN time_spent ELSE NULL END) as avg_time,
                SUM(CASE WHEN event_type = 'view' AND time_spent < 5 THEN 1 ELSE 0 END) as bounces,
                SUM(CASE WHEN event_type = 'exit' THEN 1 ELSE 0 END) as exits
            FROM $table
            WHERE post_id = %d
            AND created_at >= %s AND created_at <= %s",
            $post_id,
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $bounce_rate = $metrics->views > 0 ? ($metrics->bounces / $metrics->views) * 100 : 0;
        $exit_rate = $metrics->views > 0 ? ($metrics->exits / $metrics->views) * 100 : 0;

        // Sources Query
        $sources_raw = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                source, 
                COUNT(*) as views,
                COUNT(DISTINCT session_id) as users
            FROM $table 
            WHERE post_id = %d 
            AND event_type = 'view'
            AND created_at >= %s AND created_at <= %s
            GROUP BY source 
            ORDER BY views DESC",
            $post_id,
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $sources = array();
        foreach ($sources_raw as $row) {
            $source_name = $row->source ?: 'Direct';
            $sources[] = array(
                'source' => $source_name,
                'views' => (int) $row->views,
                'users' => (int) $row->users,
                'percent' => $metrics->views > 0 ? round(($row->views / $metrics->views) * 100, 1) : 0
            );
        }

        // Behavioral Queries (Scroll & Clicks)
        $behavior_raw = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                event_type, 
                event_label,
                COUNT(*) as count
            FROM $table 
            WHERE post_id = %d 
            AND created_at >= %s AND created_at <= %s
            AND event_type IN ('scroll', 'click')
            GROUP BY event_type, event_label",
            $post_id,
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $behavior = array(
            'scroll' => array(
                '25%' => 0,
                '50%' => 0,
                '75%' => 0,
                '100%' => 0,
            ),
            'clicks' => array(
                'cta' => 0,
                'internal' => 0,
                'external' => 0,
                'details' => array()
            )
        );

        foreach ($behavior_raw as $row) {
            if ($row->event_type === 'scroll') {
                $behavior['scroll'][$row->event_label] = (int) $row->count;
            } elseif ($row->event_type === 'click') {
                if (strpos($row->event_label, 'CTA:') !== false) {
                    $behavior['clicks']['cta'] += (int) $row->count;
                } elseif (strpos($row->event_label, 'External:') !== false) {
                    $behavior['clicks']['external'] += (int) $row->count;
                } else {
                    $behavior['clicks']['internal'] += (int) $row->count;
                }

                $behavior['clicks']['details'][] = array(
                    'label' => $row->event_label,
                    'count' => (int) $row->count
                );
            }
        }

        // Audience Queries
        $audience_stats = array(
            'types' => array('New' => 0, 'Returning' => 0),
            'devices' => array('Mobile' => 0, 'Tablet' => 0, 'Desktop' => 0),
            'browsers' => array(),
            'countries' => array()
        );

        $audience_raw = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                visitor_type, device_type, browser, country, 
                COUNT(*) as count
            FROM $table 
            WHERE post_id = %d 
            AND event_type = 'view'
            AND created_at >= %s AND created_at <= %s
            GROUP BY visitor_type, device_type, browser, country",
            $post_id,
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        foreach ($audience_raw as $row) {
            if (isset($audience_stats['types'][$row->visitor_type])) {
                $audience_stats['types'][$row->visitor_type] += (int) $row->count;
            }
            if (isset($audience_stats['devices'][$row->device_type])) {
                $audience_stats['devices'][$row->device_type] += (int) $row->count;
            }
            if (!isset($audience_stats['browsers'][$row->browser])) {
                $audience_stats['browsers'][$row->browser] = 0;
            }
            $audience_stats['browsers'][$row->browser] += (int) $row->count;

            if (!isset($audience_stats['countries'][$row->country])) {
                $country_name = class_exists('WC') && isset(WC()->countries->countries[$row->country]) ? WC()->countries->countries[$row->country] : $row->country;
                $audience_stats['countries'][$row->country] = array('name' => $country_name, 'count' => 0);
            }
            $audience_stats['countries'][$row->country]['count'] += (int) $row->count;
        }

        // Sort results
        arsort($audience_stats['browsers']);
        uasort($audience_stats['countries'], function ($a, $b) {
            return $b['count'] - $a['count'];
        });

        return rest_ensure_response(array(
            'post_title' => $post->post_title,
            'sources' => $sources,
            'behavior' => $behavior,
            'audience' => array(
                'types' => $audience_stats['types'],
                'devices' => $audience_stats['devices'],
                'browsers' => array_slice($audience_stats['browsers'], 0, 5),
                'geo' => array_slice(array_values($audience_stats['countries']), 0, 5)
            ),
            'engagement' => array(
                'views' => (int) $metrics->views,
                'unique_visitors' => (int) $metrics->unique_visitors,
                'avg_time_spent' => round((float) $metrics->avg_time, 2),
                'bounce_rate' => round((float) $bounce_rate, 2),
                'exit_rate' => round((float) $exit_rate, 2),
                'total_exits' => (int) $metrics->exits
            )
        ));
    }

    /**
     * Get overall posts analytics.
     */
    public function get_posts_analytics($request)
    {
        global $wpdb;
        $start_date = $request->get_param('start_date') ?: date('Y-m-d', strtotime('-30 days'));
        $end_date = $request->get_param('end_date') ?: date('Y-m-d');

        $table = InsightPress_Post_Tracker::get_table_name();

        if ($wpdb->get_var("SHOW TABLES LIKE '$table'") !== $table) {
            return rest_ensure_response(array(
                'summary' => array(
                    'total_views' => 0,
                    'total_visitors' => 0,
                    'avg_time' => 0,
                    'avg_bounce_rate' => 0,
                    'engagement_score' => 0
                ),
                'top_post' => null,
                'posts' => array()
            ));
        }

        // Summary Stats
        $summary = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(*) as total_views,
                COUNT(DISTINCT session_id) as total_visitors,
                AVG(time_spent) as avg_time,
                SUM(CASE WHEN time_spent < 5 THEN 1 ELSE 0 END) as total_bounces
            FROM $table
            WHERE event_type = 'view'
            AND created_at >= %s AND created_at <= %s",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $bounce_rate = $summary->total_views > 0 ? ($summary->total_bounces / $summary->total_views) * 100 : 0;

        // Engagement Score Logic: Views (40%) + Uniques (30%) + Time on Page (30%)
        // Normalized roughly for a "Score" feel
        $engagement_score = ($summary->total_views * 0.4) + ($summary->total_visitors * 0.3) + ($summary->avg_time * 0.3);

        // Top Performing Post
        $top_post_raw = $wpdb->get_row($wpdb->prepare(
            "SELECT post_id, COUNT(*) as views 
            FROM $table 
            WHERE event_type = 'view'
            AND created_at >= %s AND created_at <= %s
            GROUP BY post_id 
            ORDER BY views DESC 
            LIMIT 1",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $top_post = null;
        if ($top_post_raw) {
            $post = get_post($top_post_raw->post_id);
            if ($post) {
                $top_post = array(
                    'id' => $post->ID,
                    'title' => $post->post_title,
                    'views' => (int) $top_post_raw->views,
                    'thumbnail' => get_the_post_thumbnail_url($post->ID, 'thumbnail') ?: '',
                    'permalink' => get_permalink($post->ID)
                );
            }
        }

        return rest_ensure_response(array(
            'summary' => array(
                'total_views' => (int) $summary->total_views,
                'total_visitors' => (int) $summary->total_visitors,
                'avg_time' => round((float) $summary->avg_time, 2),
                'avg_bounce_rate' => round((float) $bounce_rate, 2),
                'engagement_score' => round((float) $engagement_score, 1)
            ),
            'sources' => $this->get_global_posts_sources($start_date, $end_date),
            'devices' => $this->get_global_posts_devices($start_date, $end_date),
            'top_post' => $top_post,
            'trends' => $this->get_posts_trends($start_date, $end_date),
            'posts' => $this->get_individual_posts_data($start_date, $end_date)
        ));
    }

    /**
     * Get global posts devices.
     */
    protected function get_global_posts_devices($start_date, $end_date)
    {
        global $wpdb;
        $table = InsightPress_Post_Tracker::get_table_name();

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT device_type, COUNT(*) as count
            FROM $table
            WHERE event_type = 'view'
            AND created_at >= %s AND created_at <= %s
            GROUP BY device_type",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $devices = array('Mobile' => 0, 'Desktop' => 0, 'Tablet' => 0);
        $total = 0;
        foreach ($results as $row) {
            if (isset($devices[$row->device_type])) {
                $devices[$row->device_type] = (int) $row->count;
                $total += (int) $row->count;
            }
        }

        $percents = array();
        foreach ($devices as $type => $count) {
            $percents[$type] = $total > 0 ? round(($count / $total) * 100, 1) : 0;
        }

        return $percents;
    }

    /**
     * Get individual posts data.
     */
    protected function get_individual_posts_data($start_date, $end_date)
    {
        global $wpdb;
        $table = InsightPress_Post_Tracker::get_table_name();

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT 
                post_id,
                COUNT(*) as views,
                COUNT(DISTINCT session_id) as visitors,
                AVG(time_spent) as avg_time,
                SUM(time_spent) as total_time,
                SUM(CASE WHEN time_spent < 5 THEN 1 ELSE 0 END) as bounces
            FROM $table
            WHERE event_type = 'view'
            AND created_at >= %s AND created_at <= %s
            GROUP BY post_id
            ORDER BY views DESC",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $data = array();
        foreach ($rows as $row) {
            $post = get_post($row->post_id);
            if (!$post)
                continue;

            $categories = get_the_category($post->ID);
            $cat_names = array_map(function ($c) {
                return $c->name;
            }, $categories);

            $bounce_rate = $row->views > 0 ? ($row->bounces / $row->views) * 100 : 0;
            $score = ($row->views * 0.4) + ($row->visitors * 0.3) + ($row->avg_time * 0.3);

            // Mock conversion for now (e.g. users who scrolled 100% or clicked something)
            $conversions = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id) FROM $table 
                WHERE post_id = %d AND (event_type = 'event' AND event_label IN ('scroll_100', 'click'))
                AND created_at >= %s AND created_at <= %s",
                $row->post_id,
                $start_date . ' 00:00:00',
                $end_date . ' 23:59:59'
            ));

            $data[] = array(
                'id' => $post->ID,
                'title' => $post->post_title,
                'categories' => $cat_names,
                'thumbnail' => get_the_post_thumbnail_url($post->ID, 'thumbnail') ?: '',
                'permalink' => get_permalink($post->ID),
                'views' => (int) $row->views,
                'visitors' => (int) $row->visitors,
                'avg_time' => round((float) $row->avg_time, 1),
                'total_time' => (float) $row->total_time,
                'bounce_rate' => round((float) $bounce_rate, 1),
                'conversion' => (int) $conversions,
                'score' => round((float) $score, 1)
            );
        }

        return $data;
    }

    /**
     * Get global posts sources.
     */
    protected function get_global_posts_sources($start_date, $end_date)
    {
        global $wpdb;
        $table = InsightPress_Post_Tracker::get_table_name();

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT source, COUNT(*) as count
            FROM $table
            WHERE event_type = 'view'
            AND created_at >= %s AND created_at <= %s
            GROUP BY source
            ORDER BY count DESC",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        $sources = array();
        $total = 0;
        foreach ($results as $row) {
            $total += (int) $row->count;
        }

        foreach ($results as $row) {
            $sources[] = array(
                'source' => $row->source ?: 'Direct',
                'count' => (int) $row->count,
                'percent' => $total > 0 ? round(($row->count / $total) * 100, 1) : 0
            );
        }

        return $sources;
    }

    /**
     * Get posts trends.
     */
    protected function get_posts_trends($start_date, $end_date)
    {
        global $wpdb;
        $table = InsightPress_Post_Tracker::get_table_name();

        $results = $wpdb->get_results($wpdb->prepare(
            "SELECT DATE(created_at) as date, COUNT(*) as views
            FROM $table
            WHERE event_type = 'view'
            AND created_at >= %s AND created_at <= %s
            GROUP BY DATE(created_at)
            ORDER BY date ASC",
            $start_date . ' 00:00:00',
            $end_date . ' 23:59:59'
        ));

        // Fill in missing dates
        $trends = array();
        $current = strtotime($start_date);
        $last = strtotime($end_date);

        $results_map = array();
        foreach ($results as $row) {
            $results_map[$row->date] = (int) $row->views;
        }

        while ($current <= $last) {
            $date = date('Y-m-d', $current);
            $trends[] = array(
                'date' => $date,
                'views' => isset($results_map[$date]) ? $results_map[$date] : 0
            );
            $current = strtotime('+1 day', $current);
        }

        return $trends;
    }

    /**
     * Get customer insights for a specific product.
     * Returns paginated list of customers who ordered this product.
     *
     * HPOS note: In High-Performance Order Storage mode, orders live in
     * wp_wc_orders and customer_id is a direct column on that table.
     * In legacy mode, orders are in wp_posts and customer ID comes from
     * wp_postmeta (_customer_user).
     */
    public function get_product_customers($request)
    {
        global $wpdb;

        $product_id = (int) $request->get_param('id');
        $start_date = sanitize_text_field($request->get_param('start_date') ?: date('Y-m-d', strtotime('-30 days')));
        $end_date = sanitize_text_field($request->get_param('end_date') ?: date('Y-m-d'));
        $page = max(1, (int) ($request->get_param('page') ?: 1));
        $per_page = min(50, max(5, (int) ($request->get_param('per_page') ?: 10)));
        $offset = ($page - 1) * $per_page;

        $date_start = $start_date . ' 00:00:00';
        $date_end = $end_date . ' 23:59:59';
        $statuses = array('wc-completed', 'wc-processing', 'wc-on-hold');
        $status_ph = implode(',', array_fill(0, count($statuses), '%s'));

        // Detect HPOS
        $use_hpos = class_exists('Automattic\WooCommerce\Utilities\OrderUtil')
            && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();

        if ($use_hpos) {
            // ── HPOS path ────────────────────────────────────────────────
            // customer_id is a direct column on wc_orders; no postmeta needed.
            $count_args = array_merge($statuses, array($date_start, $date_end, $product_id));
            $count_sql = $wpdb->prepare(
                "SELECT COUNT(DISTINCT o.customer_id)
                FROM {$wpdb->prefix}woocommerce_order_items AS oi
                INNER JOIN {$wpdb->prefix}wc_orders AS o
                    ON o.id = oi.order_id
                    AND o.status IN ($status_ph)
                    AND o.date_created_gmt BETWEEN %s AND %s
                    AND o.customer_id > 0
                INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim_prod
                    ON oim_prod.order_item_id = oi.order_item_id
                    AND oim_prod.meta_key IN ('_product_id','_variation_id')
                    AND oim_prod.meta_value = %d
                WHERE oi.order_item_type = 'line_item'",
                $count_args
            );
            $total_customers = (int) $wpdb->get_var($count_sql);

            $data_args = array_merge($statuses, array($date_start, $date_end, $product_id, $per_page, $offset));
            $data_sql = $wpdb->prepare(
                "SELECT
                    o.customer_id                                      AS customer_id,
                    COUNT(DISTINCT oi.order_id)                        AS order_count,
                    SUM(CAST(oim_line.meta_value AS DECIMAL(10,2)))    AS total_spent,
                    MAX(o.date_created_gmt)                            AS last_order_date
                FROM {$wpdb->prefix}woocommerce_order_items AS oi
                INNER JOIN {$wpdb->prefix}wc_orders AS o
                    ON o.id = oi.order_id
                    AND o.status IN ($status_ph)
                    AND o.date_created_gmt BETWEEN %s AND %s
                    AND o.customer_id > 0
                INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim_prod
                    ON oim_prod.order_item_id = oi.order_item_id
                    AND oim_prod.meta_key IN ('_product_id','_variation_id')
                    AND oim_prod.meta_value = %d
                LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim_line
                    ON oim_line.order_item_id = oi.order_item_id
                    AND oim_line.meta_key = '_line_total'
                WHERE oi.order_item_type = 'line_item'
                GROUP BY o.customer_id
                ORDER BY total_spent DESC
                LIMIT %d OFFSET %d",
                $data_args
            );
        } else {
            // ── Legacy path ───────────────────────────────────────────────
            // Orders are in wp_posts; customer ID comes from wp_postmeta._customer_user
            $count_args = array_merge($statuses, array($date_start, $date_end, $product_id));
            $count_sql = $wpdb->prepare(
                "SELECT COUNT(DISTINCT pm_cust.meta_value)
                FROM {$wpdb->prefix}woocommerce_order_items AS oi
                INNER JOIN {$wpdb->prefix}posts AS p
                    ON p.ID = oi.order_id
                    AND p.post_type   = 'shop_order'
                    AND p.post_status IN ($status_ph)
                    AND p.post_date BETWEEN %s AND %s
                INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim_prod
                    ON oim_prod.order_item_id = oi.order_item_id
                    AND oim_prod.meta_key IN ('_product_id','_variation_id')
                    AND oim_prod.meta_value = %d
                INNER JOIN {$wpdb->prefix}postmeta AS pm_cust
                    ON pm_cust.post_id  = oi.order_id
                    AND pm_cust.meta_key = '_customer_user'
                    AND pm_cust.meta_value > 0
                WHERE oi.order_item_type = 'line_item'",
                $count_args
            );
            $total_customers = (int) $wpdb->get_var($count_sql);

            $data_args = array_merge($statuses, array($date_start, $date_end, $product_id, $per_page, $offset));
            $data_sql = $wpdb->prepare(
                "SELECT
                    pm_cust.meta_value                                 AS customer_id,
                    COUNT(DISTINCT oi.order_id)                        AS order_count,
                    SUM(CAST(oim_line.meta_value AS DECIMAL(10,2)))    AS total_spent,
                    MAX(p.post_date)                                   AS last_order_date
                FROM {$wpdb->prefix}woocommerce_order_items AS oi
                INNER JOIN {$wpdb->prefix}posts AS p
                    ON p.ID = oi.order_id
                    AND p.post_type   = 'shop_order'
                    AND p.post_status IN ($status_ph)
                    AND p.post_date BETWEEN %s AND %s
                INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim_prod
                    ON oim_prod.order_item_id = oi.order_item_id
                    AND oim_prod.meta_key IN ('_product_id','_variation_id')
                    AND oim_prod.meta_value = %d
                LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS oim_line
                    ON oim_line.order_item_id = oi.order_item_id
                    AND oim_line.meta_key = '_line_total'
                INNER JOIN {$wpdb->prefix}postmeta AS pm_cust
                    ON pm_cust.post_id  = oi.order_id
                    AND pm_cust.meta_key = '_customer_user'
                    AND pm_cust.meta_value > 0
                WHERE oi.order_item_type = 'line_item'
                GROUP BY pm_cust.meta_value
                ORDER BY total_spent DESC
                LIMIT %d OFFSET %d",
                $data_args
            );
        }

        $rows = $wpdb->get_results($data_sql);
        $customers = array();
        $tracker_table = $wpdb->prefix . 'insightpress_product_events';

        foreach ($rows as $row) {
            $user = get_userdata((int) $row->customer_id);
            if (!$user)
                continue;

            // Fetch device/browser from tracker if available
            $engagement = $wpdb->get_row($wpdb->prepare(
                "SELECT device_type, browser
                FROM $tracker_table
                WHERE session_id = %s
                AND product_id = %d
                ORDER BY created_at DESC
                LIMIT 1",
                'user_' . $user->ID,
                $product_id
            ));

            $country = get_user_meta($user->ID, 'billing_country', true);
            $city = get_user_meta($user->ID, 'billing_city', true);
            $phone = get_user_meta($user->ID, 'billing_phone', true);

            $customers[] = array(
                'id' => (int) $row->customer_id,
                'name' => trim($user->first_name . ' ' . $user->last_name) ?: $user->display_name,
                'email' => $user->user_email,
                'avatar' => get_avatar_url($user->ID, array('size' => 40)),
                'order_count' => (int) $row->order_count,
                'total_spent' => round((float) $row->total_spent, 2),
                'last_order' => $row->last_order_date ? date('Y-m-d', strtotime($row->last_order_date)) : null,
                'country' => $country ?: '—',
                'city' => $city ?: '—',
                'phone' => $phone ?: '—',
                'device' => $engagement ? $engagement->device_type : 'Unknown',
                'browser' => $engagement ? $engagement->browser : 'Unknown',
                'edit_url' => admin_url('user-edit.php?user_id=' . $user->ID),
                'orders_url' => admin_url('edit.php?post_type=shop_order&_customer_user=' . $user->ID),
            );
        }

        return rest_ensure_response(array(
            'customers' => $customers,
            'total' => $total_customers,
            'page' => $page,
            'per_page' => $per_page,
            'total_pages' => (int) ceil($total_customers / max(1, $per_page)),
        ));
    }

    /**
     * Get product pairings for Frequently Bought Together with independent filtering and backend pagination.
     */
    public function get_product_pairings($request)
    {
        $start_date = $request->get_param('start_date');
        $end_date = $request->get_param('end_date');
        $page = (int) $request->get_param('page') ?: 1;
        $per_page = (int) $request->get_param('per_page') ?: 5;

        if (!$start_date || !$end_date) {
            return new WP_Error('invalid_dates', 'Please provide start_date and end_date.', array('status' => 400));
        }

        $args = array(
            'date_created' => $start_date . ' 00:00:00...' . $end_date . ' 23:59:59',
            'status' => array('wc-completed', 'wc-processing', 'wc-on-hold'),
            'limit' => -1,
            'return' => 'ids',
        );

        $order_ids = wc_get_orders($args);
        $product_stats = array();
        $product_pairings = array();

        foreach ($order_ids as $order_id) {
            $order = wc_get_order($order_id);
            if ($order) {
                $items = $order->get_items();
                $order_product_ids = array();

                foreach ($items as $item) {
                    if (!method_exists($item, 'get_product_id'))
                        continue;
                    $product_id = $item->get_product_id();
                    if (!$product_id)
                        continue;

                    $order_product_ids[] = $product_id;
                    $line_total = method_exists($item, 'get_total') ? (float) $item->get_total() : 0;

                    if (!isset($product_stats[$product_id])) {
                        $product_stats[$product_id] = array('current_order_revenue' => array());
                    }
                    $product_stats[$product_id]['current_order_revenue'][$order_id] = $line_total;
                }

                $order_product_ids = array_values(array_unique($order_product_ids));
                if (count($order_product_ids) > 1) {
                    for ($i = 0; $i < count($order_product_ids); $i++) {
                        for ($j = $i + 1; $j < count($order_product_ids); $j++) {
                            $p1 = $order_product_ids[$i];
                            $p2 = $order_product_ids[$j];
                            $pair = $p1 < $p2 ? "$p1-$p2" : "$p2-$p1";

                            $pair_revenue = (isset($product_stats[$p1]) ? $product_stats[$p1]['current_order_revenue'][$order_id] : 0) +
                                (isset($product_stats[$p2]) ? $product_stats[$p2]['current_order_revenue'][$order_id] : 0);

                            if (!isset($product_pairings[$pair])) {
                                $product_pairings[$pair] = array('p1' => $p1, 'p2' => $p2, 'count' => 0, 'revenue' => 0);
                            }
                            $product_pairings[$pair]['count']++;
                            $product_pairings[$pair]['revenue'] += $pair_revenue;
                        }
                    }
                }
            }
        }

        uasort($product_pairings, function ($a, $b) {
            return $b['count'] <=> $a['count'];
        });

        $total_items = count($product_pairings);
        $offset = ($page - 1) * $per_page;
        $paginated_pairings = array_slice($product_pairings, $offset, $per_page);

        $frequently_bought_together = array();
        foreach ($paginated_pairings as $pair) {
            $prod1 = wc_get_product($pair['p1']);
            $prod2 = wc_get_product($pair['p2']);
            if ($prod1 && $prod2) {
                $frequently_bought_together[] = array(
                    'product_a' => $prod1->get_name(),
                    'image_a' => wp_get_attachment_image_url($prod1->get_image_id(), 'thumbnail') ?: '',
                    'product_b' => $prod2->get_name(),
                    'image_b' => wp_get_attachment_image_url($prod2->get_image_id(), 'thumbnail') ?: '',
                    'count' => $pair['count'],
                    'revenue' => $pair['revenue']
                );
            }
        }

        return rest_ensure_response(array(
            'pairings' => $frequently_bought_together,
            'total_items' => $total_items,
            'total_pages' => ceil($total_items / $per_page),
            'page' => $page,
            'total_count' => $total_items
        ));
    }
}


