<?php
if (!defined('ABSPATH')) die('Restricted Access');

class AIFLT_Funnel_Analyzer_Logic {

    /**
     * NEW: Analyzes and retrieves page speed alerts for key funnel pages.
     *
     * @return array An array containing the status and a list of slow pages.
     */
    public static function get_page_speed_alerts() {
        global $wpdb;
        $table_name      = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $aiflt_start_date      = gmdate('Y-m-d H:i:s', strtotime("-7 days"));
        $speed_threshold = 2.5; // LCP threshold in seconds. 2.5s is Google's recommendation.

        $slow_pages = $wpdb->get_results(
            $wpdb->prepare(
                "
                SELECT page_name, AVG(page_speed) as avg_lcp
                FROM {$table_name}
                WHERE 
                    timestamp >= %s 
                    AND page_name IN ('landing', 'product', 'cart') 
                    AND page_speed IS NOT NULL AND page_speed > 0
                GROUP BY page_name
                HAVING avg_lcp > %f
                ORDER BY avg_lcp DESC
                ",
                $aiflt_start_date,
                $speed_threshold
            ),
            ARRAY_A
        );

        $page_name_map = [
            'landing' => __('Landing Page', 'ai-flash-tune'),
            'product' => __('Product Pages', 'ai-flash-tune'),
            'cart' => __('Cart Page', 'ai-flash-tune'),
        ];

        $aiflt_alerts = [];
        if (!empty($slow_pages)) {
            foreach ($slow_pages as $page) {
                $aiflt_alerts[] = [
                    'name' => isset($page_name_map[$page['page_name']]) ? $page_name_map[$page['page_name']] : ucfirst($page['page_name']),
                    'speed' => round($page['avg_lcp'], 2)
                ];
            }
        }

        return [
            'status' => empty($aiflt_alerts) ? 'good' : 'warning',
            'alerts' => $aiflt_alerts
        ];
    }


    /**
     * Analyzes the WooCommerce checkout fields to detect potential overload.
     *
     * @return array An array containing the field count, status, and display properties.
     */
    public static function get_checkout_field_data() {
        if (!class_exists('WooCommerce')) {
            return [
                'count' => 0,
                'status' => 'N/A',
                'message' => __('WooCommerce is not active.', 'ai-flash-tune'),
                'css_class' => 'ai-flash-tune-text-gray-500',
                'icon_class' => 'fas fa-question-circle'
            ];
        }

        $fields = WC()->checkout()->get_checkout_fields();
        $total_fields = 0;

        foreach ($fields as $fieldset) {
            foreach ($fieldset as $key => $field) {
                // Check if the field is present AND not explicitly hidden by a CSS class.
                // Hidden fields often use the 'hidden' or 'form-row-hidden' class.
                $is_hidden = isset($field['class']) && (
                    in_array('hidden', $field['class']) || 
                    in_array('form-row-hidden', $field['class']) ||
                    in_array('form-row-none', $field['class']) // Some themes/plugins use other classes
                );

                if (!$is_hidden) {
                    $total_fields++;
                }
            }
        }

        $threshold = 15; // Recommended maximum number of fields

        if ($total_fields > $threshold) {
            return [
                'count' => $total_fields,
                'status' => 'High Friction',
                'message' => sprintf(
                    /* translators: %d: Number of fields with high friction detected */
                    __( 'High Friction Detected: %d Fields', 'ai-flash-tune' ),
                    $total_fields
                ),
                'css_class' => 'ai-flash-tune-text-orange-500',
                'icon_class' => 'fas fa-exclamation-triangle'
            ];
        } else {
            return [
                'count' => $total_fields,
                'status' => 'Good',
                'message' => sprintf(
                /* translators: %d: Number of fields */
                    __( 'Good: %d Fields', 'ai-flash-tune' ),
                    $total_fields
                ),
                'css_class' => 'ai-flash-tune-text-green-500',
                'icon_class' => 'fas fa-check-circle'
            ];
        }
    }

    /**
     * Logs a checkout attempt for each product in the order.
     * This function is triggered by the 'woocommerce_thankyou' hook.
     *
     * @param int $order_id The ID of the order.
     */
    public static function log_checkout_attempt($order_id) {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }
        if (!$order_id) return;
        $order = wc_get_order($order_id);
        if (!$order) return;

        foreach ($order->get_items() as $aiflt_item) {
            $aiflt_product_id = $aiflt_item->get_product_id();
            self::aiflt_log_funnel_event([
                'page_name' => 'checkout_attempt',
                'post_id'   => $aiflt_product_id,
                'order_id'  => $order_id,
            ]);
        }
    }

    /**
     * Logs a confirmed sale for each product in the order.
     * This function is triggered by the 'woocommerce_order_status_completed' hook.
     *
     * @param int $order_id The ID of the order.
     */
    public static function log_confirmed_sale($order_id) {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }
        if (!$order_id) return;
        $order = wc_get_order($order_id);
        if (!$order) return;

        $status = 'wc-' . $order->get_status();
        if (!self::aiflt_is_revenue_status($status)) {
            return;
        }

        global $wpdb;
        $table = $wpdb->prefix . 'aiflt_funnel_data';
        $existing_sale = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE order_id = %d AND page_name = %s",
            $order_id,
            'sale'
        ));
        if ($existing_sale > 0) {
            return;
        }

        foreach ($order->get_items() as $aiflt_item) {
            $aiflt_product_id = $aiflt_item->get_product_id();
            self::aiflt_log_funnel_event([
                'page_name' => 'sale',
                'post_id'   => $aiflt_product_id,
                'order_id'  => $order_id,
            ]);
        }

        self::aiflt_mark_recovery_for_order($order_id);
    }

    /**
     * Remove confirmed-sale funnel events when an order is later cancelled, failed, refunded, or drafted.
     * This keeps conversion metrics aligned with real WooCommerce order status.
     */
    public static function remove_confirmed_sale($order_id) {
        if (!$order_id) return;
        global $wpdb;
        $wpdb->delete(
            $wpdb->prefix . 'aiflt_funnel_data',
            ['order_id' => absint($order_id), 'page_name' => 'sale'],
            ['%d', '%s']
        );
    }

    /**
     * Revenue-positive statuses for AI Flash Tune conversion/sales reporting.
     * pending/on-hold are checkout/order attempts, not confirmed sales by default.
     */
    public static function aiflt_revenue_statuses() {
        return apply_filters('aiflt_revenue_order_statuses', ['wc-processing', 'wc-completed']);
    }

    public static function aiflt_is_revenue_status($status) {
        $status = is_string($status) ? $status : '';
        if ($status && strpos($status, 'wc-') !== 0) {
            $status = 'wc-' . $status;
        }
        return in_array($status, self::aiflt_revenue_statuses(), true);
    }

    /**
     * Checks whether a plugin-owned table contains a column before using it in SQL.
     * This keeps older installs safe when addon DB upgrades have not run yet.
     *
     * @param string $table  Full table name.
     * @param string $column Column name to check.
     * @return bool
     */
    private static function aiflt_table_column_exists($table, $column) {
        global $wpdb;

        $table  = esc_sql($table);
        $column = sanitize_key($column);

        if (empty($table) || empty($column)) {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name is escaped and column lookup value is prepared.
        return (bool) $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM {$table} LIKE %s", $column));
    }
    /**
     * If the Cart Recovery addon is present, connect the recovered order back to its recovery token.
     */
    private static function aiflt_mark_recovery_for_order($order_id) {
        global $wpdb;
        $tokens_table = $wpdb->prefix . 'aiftcr_recovery_tokens';
        $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tokens_table));
        if ($exists !== $tokens_table) {
            return;
        }

        $session_id = self::get_session_id();
        if (empty($session_id)) {
            return;
        }

        if (self::aiflt_table_column_exists($tokens_table, 'recovered_at')) {
            $wpdb->query($wpdb->prepare(
                "UPDATE {$tokens_table}
                 SET recovered_order_id = %d, recovered_at = COALESCE(recovered_at, %s)
                 WHERE session_id = %s
                   AND (recovered_order_id IS NULL OR recovered_order_id = 0)
                 ORDER BY clicked_at DESC, created_at DESC
                 LIMIT 1",
                absint($order_id),
                current_time('mysql'),
                $session_id
            ));
        } else {
            $wpdb->query($wpdb->prepare(
                "UPDATE {$tokens_table}
                 SET recovered_order_id = %d
                 WHERE session_id = %s
                   AND (recovered_order_id IS NULL OR recovered_order_id = 0)
                 ORDER BY clicked_at DESC, created_at DESC
                 LIMIT 1",
                absint($order_id),
                $session_id
            ));
        }
    }

    /**
     * Inserts a funnel event into the custom database table.
     *
     * @param array $data The event data to be logged.
     */
    private static function aiflt_log_funnel_event($data) {
        global $wpdb;
        $table = $wpdb->prefix . 'aiflt_funnel_data';
        // Check if IP anonymization is enabled
        $anonymize_ip = get_option('aiflt_anonymize_ip', '0') === '1';
        $ip_address = sanitize_text_field($_SERVER['REMOTE_ADDR'] ?? '');
        if ($anonymize_ip) {
            $ip_address = wp_privacy_anonymize_ip($ip_address);
        }

        $wpdb->insert($table, [
            'page_name'  => $data['page_name'],
            'post_id'    => $data['post_id'],
            'order_id'   => $data['order_id'] ?? null,
            'timestamp'  => current_time('mysql'),
            'session_id' => self::get_session_id(),
            'user_id'    => get_current_user_id(),
            'device_type' => wp_is_mobile() ? 'mobile' : 'desktop',
            'ip_address' => $ip_address,

        ]);
    }

    /**
     * Handles the AJAX request to track a funnel page visit.
     */
    public static function track_funnel_page_callback() {

        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }
        // Verify nonce for security
        check_ajax_referer('aiflt-tracking-nonce', '_ajax_nonce'); // Check nonce passed from frontend


        // Get the unified session ID from the JS payload.
        $session_id = isset($_POST['session_id']) ? sanitize_text_field($_POST['session_id']) : '';
        if (empty($session_id)) {
            wp_send_json_error(__('Session ID is missing.', 'ai-flash-tune'));
        }

        $page_name = sanitize_text_field($_POST['page_name']);
        $aiflt_device_type = wp_is_mobile() ? 'mobile' : 'desktop';
        $user_id = get_current_user_id();
        $post_id = isset($_POST['post_id']) ? intval($_POST['post_id']) : 0;
        
        // ---> FIX: This is the crucial part. We get the current cart data during the AJAX call.
        $aiflt_cart_data = self::get_woocommerce_cart_data();

        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aiflt_funnel_data');

        // Check if IP anonymization is enabled
        $anonymize_ip = get_option('aiflt_anonymize_ip', '0') === '1';
        $ip_address = sanitize_text_field($_SERVER['REMOTE_ADDR'] ?? '');
        if ($anonymize_ip) {
            $ip_address = wp_privacy_anonymize_ip($ip_address);
        }

        // For 'cart' events triggered by 'add_to_cart', we use REPLACE to ensure the latest cart data is there.
        if ($page_name === 'cart') {
            // For a cart event, we update an existing entry or insert a new one to capture the latest cart state.
            
            $record_id = $wpdb->get_var($wpdb->prepare(
                "SELECT id FROM {$table_name} WHERE session_id = %s AND page_name = %s",
                $session_id,
                $page_name
            ));

            $data = [
                'user_id'     => $user_id,
                'device_type' => $aiflt_device_type,
                'timestamp'   => current_time('mysql'),
                'post_id'     => $post_id,
                'cart_data'   => maybe_serialize($aiflt_cart_data),
                'ip_address'  => $ip_address,
            ];

            if ($record_id) {
                // Record exists: UPDATE
                $wpdb->update(
                    $table_name,
                    $data,
                    ['id' => $record_id],
                    ['%d', '%s', '%s', '%d', '%s', '%s'], // Data formats
                    ['%d'] // WHERE format
                );
            } else {
                // Record does not exist: INSERT (needs session_id and page_name added to data)
                $data['session_id'] = $session_id;
                $data['page_name'] = $page_name;
                $wpdb->insert($table_name, $data);
            }
        } else {
            // For other page views (Landing, Product, Checkout, Shop).

            // Determine if we need a simple page check or a complex page+post_id check
            $page_is_product = ($page_name === 'product');

            $check_sql = "SELECT COUNT(*) FROM {$table_name} WHERE session_id = %s AND page_name = %s";
            $check_args = [$session_id, $page_name];

            if ($page_is_product && $post_id) {
                // CRITICAL FIX: If it's a product page, check for uniqueness by post_id as well.
                // This ensures visiting Product ID 63 and then Product ID 64 both get logged.
                $check_sql .= " AND post_id = %d";
                $check_args[] = $post_id;
            }

            // Execute the check (using ellipsis operator for array spread if PHP >= 5.6)
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The $check_sql variable is safely constructed using a prefixed table name and controlled string concatenation.
            $page_already_tracked = $wpdb->get_var($wpdb->prepare(
                $check_sql,
                ...$check_args
            ));
            
            if ($page_already_tracked == 0) {
                // Record does not exist: INSERT
                $wpdb->insert(
                    $table_name,
                    [
                        'session_id'  => $session_id,
                        'user_id'     => $user_id,
                        'page_name'   => $page_name,
                        'device_type' => $aiflt_device_type,
                        'timestamp'   => current_time('mysql'),
                        'post_id'     => $post_id,
                        'cart_data'   => maybe_serialize($aiflt_cart_data),
                        'ip_address'  => $ip_address,
                    ]
                );
            }
            // If page_already_tracked > 0, we do nothing (ignore subsequent views).
        }

        wp_send_json_success(__('Data saved to custom table', 'ai-flash-tune'));
    }

    /**
     * Handles the AJAX request to save page speed data.
     */
    public static function save_page_speed_callback() {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }
        check_ajax_referer('aiflt-tracking-nonce', '_ajax_nonce');
        
        // ---> FIX: Get session ID from the JS payload.
        $session_id = isset($_POST['session_id']) ? sanitize_text_field($_POST['session_id']) : '';
        if (empty($session_id)) {
            wp_send_json_error(__('Session ID is missing for page speed tracking.', 'ai-flash-tune'));
        }

        $page_name = sanitize_text_field($_POST['page_name']); // Get the page name from the JS
        $lcp_value = floatval(sanitize_text_field($_POST['lcp_value']));

        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        
        // Update the existing entry for the current session and page
        $wpdb->update(
            $table_name,
            ['page_speed' => $lcp_value],
            ['session_id' => $session_id, 'page_name' => $page_name],
            ['%f'],
            ['%s', '%s']
        );

        wp_send_json_success(__('Page speed saved successfully.', 'ai-flash-tune'));
    }
    
    /**
     * Handles the AJAX request to log batched behavioral data (e.g., rage clicks).
     */
    public static function log_behavior_callback() {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }
        check_ajax_referer('aiflt-tracking-nonce', '_ajax_nonce'); // Check nonce passed from frontend
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        // Data is now sent as 'events' key, which is JSON encoded string from JS
        $events_json = isset($_POST['events']) ? wp_unslash($_POST['events']) : '';
        $events = json_decode($events_json, true);

        if (!is_array($events) || empty($events)) {
            // If data fails to parse or is empty, log the error.
            wp_send_json_error(__('Invalid or empty behavioral data received.', 'ai-flash-tune'));
        }

        // Check if IP anonymization is enabled
        $anonymize_ip = get_option('aiflt_anonymize_ip', '0') === '1';
        $ip_address = sanitize_text_field($_SERVER['REMOTE_ADDR'] ?? '');
        if ($anonymize_ip) {
            $ip_address = wp_privacy_anonymize_ip($ip_address);
        }

        foreach ($events as $event) {
            $wpdb->insert($table_name, [
                'session_id'     => sanitize_text_field($event['session_id']),
                'user_id'        => get_current_user_id(),
                'product_id'     => intval($event['product_id']),
                'behavior_type'  => sanitize_text_field($event['behavior_type']),
                'behavior_value' => isset($event['behavior_value']) ? sanitize_text_field($event['behavior_value']) : null,
                'user_agent'     => sanitize_text_field($_SERVER['HTTP_USER_AGENT'] ?? ''),
                'ip_address'     => $ip_address, // Use the (potentially anonymized) IP address
                'timestamp'      => current_time('mysql')
            ]);
        }

        wp_send_json_success(__('Logged', 'ai-flash-tune'));
    }

    /**
     * Handles the AJAX request to track simpler behavioral data (e.g., traffic source).
     */
    function ajax_track_simple_behavior() {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }
        global $wpdb;
        check_ajax_referer('aiflt-tracking-nonce', '_ajax_nonce');
        $table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        $user_id = get_current_user_id();
        $traffic_source = isset($_POST['traffic_source']) ? sanitize_text_field($_POST['traffic_source']) : 'Unknown';
        // Check if IP anonymization is enabled
        $anonymize_ip = get_option('aiflt_anonymize_ip', '0') === '1';
        $ip_address = sanitize_text_field($_SERVER['REMOTE_ADDR'] ?? '');
        if ($anonymize_ip) {
            $ip_address = wp_privacy_anonymize_ip($ip_address);
        }

        $wpdb->insert($table, [
            'user_id'        => $user_id,
            'session_id'     => self::get_session_id(),
            'product_id'     => isset($_POST['product_id']) ? intval($_POST['product_id']) : null,
            'page_url'       => isset($_POST['page_url']) ? esc_url_raw(wp_unslash($_POST['page_url'])) : '',
            'behavior_type'  => sanitize_text_field($_POST['event_type']),
            'traffic_source' => $traffic_source,
            'timestamp'      => current_time('mysql'),
            'ip_address' => $ip_address,
        ]);

        wp_die();
    }
    
    /**
     * Get WooCommerce cart data, including total and contents.
     *
     * @return array
     */
    public static function get_woocommerce_cart_data() {
        if (!class_exists('WooCommerce') || !WC()->cart) {
            return [];
        }

        $aiflt_cart = WC()->cart;
        WC()->cart->calculate_totals(); 
        $aiflt_cart_contents = [];
        foreach ($aiflt_cart->get_cart() as $aiflt_cart_item_key => $aiflt_cart_item) {
            $aiflt_product = $aiflt_cart_item['data'];
            $aiflt_cart_contents[] = [
                'product_id' => $aiflt_product->get_id(),
                'name' => $aiflt_product->get_name(),
                'quantity' => $aiflt_cart_item['quantity'],
                'price' => $aiflt_product->get_price(),
            ];
        }

        return [
            'cart_total' => $aiflt_cart->get_cart_total(),
            'item_count' => $aiflt_cart->get_cart_contents_count(),
            'contents' => $aiflt_cart_contents,
        ];
    }

    /**
     * Gets a simplified version of cart data for front-end use.
     * This method is called via wp_localize_script.
     *
     * @return array
     */
    public static function get_woocommerce_cart_data_simplified() {
        $aiflt_cart_data = self::get_woocommerce_cart_data();
        $simplified_data = [];

        if (isset($aiflt_cart_data['contents']) && is_array($aiflt_cart_data['contents'])) {
            foreach ($aiflt_cart_data['contents'] as $aiflt_item) {
                $simplified_data[] = [
                    'product_id' => $aiflt_item['product_id'],
                    'quantity' => $aiflt_item['quantity'],
                ];
            }
        }
        return $simplified_data;
    }
    
    /**
     * HANDLE AJAX REQUEST: Get AI Analysis
     */
    public static function get_ai_analysis_callback() {
        // Verify nonce for security
        check_ajax_referer('aiftaa-generate-report-nonce', '_ajax_nonce'); // Check specific nonce for this action
        if (!current_user_can('manage_options')) {
            wp_send_json_error(__('You do not have permission to perform this action.', 'ai-flash-tune'));
        }
        
        $prompt = sanitize_textarea_field($_POST['prompt']);
        // CHANGE: Capture the language and preset fields.
        $language = isset($_POST['language']) ? sanitize_text_field($_POST['language']) : 'English';
        $preset = isset($_POST['preset']) ? sanitize_key($_POST['preset']) : 'custom';
        
        // CHANGE: Pass language and preset to the logic function.
        $response = self::get_ai_analysis($prompt, 'checkout', $language, $preset);
        wp_send_json_success($response);
    }

    /**
     * Handles the AJAX request to get funnel data.
     */
    public static function get_funnel_data_callback() {
        check_ajax_referer('aiflt-get-funnel-nonce', '_ajax_nonce'); // Check specific nonce for this action
        
        if ( ! current_user_can( 'manage_options' ) ) {
            wp_send_json_error( __( 'Permission denied.', 'ai-flash-tune' ) );
        }
        // Sanitize and get the device type from the AJAX request
        $aiflt_device_type = isset($_GET['device']) ? sanitize_text_field($_GET['device']) : 'all';
        $aiflt_funnel_data = self::get_funnel_data($aiflt_device_type);
        wp_send_json_success($aiflt_funnel_data);
    }

    /**
     * Retrieves the top drop-off point and its metrics from the funnel data.
     *
     * @return array
     */
    public static function get_top_drop_off_metrics() {
        global $wpdb;
        $funnel_table = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        $aiflt_start_date = gmdate('Y-m-d H:i:s', strtotime('-7 days'));

        // Use the same funnel engine as the visual funnel so dashboard percentages stay consistent.
        $aiflt_funnel_snapshot = self::get_funnel_data('all', gmdate('Y-m-d', strtotime('-7 days')), gmdate('Y-m-d'));
        $drop_offs = [
            'product'  => isset($aiflt_funnel_snapshot['drop_off_product']) ? (float) $aiflt_funnel_snapshot['drop_off_product'] : 0,
            'cart'     => isset($aiflt_funnel_snapshot['drop_off_cart']) ? (float) $aiflt_funnel_snapshot['drop_off_cart'] : 0,
            'checkout' => isset($aiflt_funnel_snapshot['drop_off_checkout']) ? (float) $aiflt_funnel_snapshot['drop_off_checkout'] : 0,
        ];

        $top_drop_off_page = 'product';
        $top_drop_off_rate = 0;

        if (!empty($drop_offs)) {
            $max_rate = max($drop_offs);
            if ($max_rate > 0) {
                $top_drop_off_page = array_search($max_rate, $drop_offs, true);
            }
            $top_drop_off_rate = round((float) $max_rate, 2);
        }

        $page_names = [
            'product' => __('Product Page', 'ai-flash-tune'),
            'cart' => __('Cart Page', 'ai-flash-tune'),
            'checkout' => __('Checkout Page', 'ai-flash-tune'),
        ];
        
        // --- REVISED: Combine behavioral queries into one for better performance ---
        $behavior_results = $wpdb->get_results(
            $wpdb->prepare(
                "
                SELECT b.behavior_type, COUNT(b.id) as total
                FROM {$behavior_table} b
                JOIN (
                    SELECT DISTINCT session_id 
                    FROM {$funnel_table} 
                    WHERE page_name = %s AND timestamp >= %s
                ) AS f ON b.session_id = f.session_id
                WHERE b.behavior_type IN ('rage_click', 'quick_bounce', 'scroll_stop')
                GROUP BY b.behavior_type
                ",
                $top_drop_off_page,
                $aiflt_start_date
            ),
            ARRAY_A
        );

        $behavior_counts = [
            'rage_click'   => 0,
            'quick_bounce' => 0,
            'scroll_stop'  => 0,
        ];

        foreach ($behavior_results as $row) {
            if (isset($behavior_counts[$row['behavior_type']])) {
                $behavior_counts[$row['behavior_type']] = (int) $row['total'];
            }
        }
        
        // --- Generate dynamic AI insight text ---
        $ai_insights = self::generate_ai_insight([
            'page' => $page_names[$top_drop_off_page],
            'rate' => $top_drop_off_rate,
            'rage_clicks' => $behavior_counts['rage_click'],
            'quick_bounces' => $behavior_counts['quick_bounce'],
            'scroll_stops' => $behavior_counts['scroll_stop'],
        ]);

        // ---> MODIFIED: Generate and add the basic_suggestion to the return array.
        $basic_suggestion = __('No single critical leak is strong enough yet. Keep collecting traffic, then review the highest drop-off step, abandoned carts, product pages, and checkout behavior before making major changes.', 'ai-flash-tune');
        switch ($top_drop_off_page) {
            case 'product':
                $basic_suggestion = __('Product pages are the current top leak. Shoppers are viewing products but not moving forward. Start with safe fixes: make the main product benefit clear above the fold, improve the first image, make price and delivery expectations obvious, and keep the Add to Cart button visible. After changes, compare product-view to cart-add rate for the next 7–14 days.', 'ai-flash-tune');
                break;
            case 'cart':
                $basic_suggestion = __('The cart page is the current top leak. Shoppers have shown buying intent, so friction here usually comes from surprise cost, unclear delivery, weak trust, or too many distractions. Start with safe fixes: show shipping/tax expectations early, keep the checkout button prominent, reduce competing links, and add a short reassurance about secure checkout or returns.', 'ai-flash-tune');
                break;
            case 'checkout':
                $basic_suggestion = __('Checkout is the current top leak. Shoppers are reaching the final step but not completing, which usually means friction, uncertainty, or unexpected cost. Start with safe fixes: remove optional checkout fields, keep guest checkout simple, show shipping/taxes/payment options clearly, and add a short trust reassurance near payment. Watch checkout drop-off for 7–14 days after each change.', 'ai-flash-tune');
                break;
        }

        return [
            'page_name' => $page_names[$top_drop_off_page],
            'drop_off_rate' => $top_drop_off_rate,
            'ai_insights' => $ai_insights,
            'rage_clicks' => $behavior_counts['rage_click'],
            'scroll_stops' => $behavior_counts['scroll_stop'],
            'quick_bounces' => $behavior_counts['quick_bounce'],
            'basic_suggestion' => $basic_suggestion,
        ];
    }

    /**
     * Generates a contextual AI-like analysis based on drop-off metrics.
     *
     * @param array $aiflt_metrics An array containing the page, rate, and behavioral data.
     * @return string
     */
    private static function generate_ai_insight($aiflt_metrics) {
        $aiflt_insight = '';
        switch ($aiflt_metrics['page']) {
            case 'Product Page':
                if ($aiflt_metrics['rage_clicks'] > 0) {
                    $aiflt_insight = sprintf(
                        /* translators: %1$s: Number of rage clicks on the Product Page (with strong tags) */
                        __( 'The AI has detected a high drop-off on your Product Page, correlated with %1$s. This suggests user frustration, possibly with product options or a call-to-action button.', 'ai-flash-tune' ),
                        '<strong>' . intval( $aiflt_metrics['rage_clicks'] ) . ' rage clicks</strong>'
                    );
                } elseif ($aiflt_metrics['quick_bounces'] > 0) {
                    $aiflt_insight = sprintf(
                        /* translators: %1$d: Number of quick bounces on the Product Page */
                        __( 'A high number of %1$d quick bounces on your Product Page indicates that users are not finding what they expected. Review your product title and images for clarity.', 'ai-flash-tune' ),
                        $aiflt_metrics['quick_bounces']
                    );
                } else {
                    if ($aiflt_metrics['rate'] <= 0) { // Check if the rate is zero or less
                        $aiflt_insight = __( 'Great! Currently, there is no significant drop-off detected on your Product Pages based on available data. Keep monitoring as more visitors interact with your store.', 'ai-flash-tune' );
                    } else { // Keep the original message for actual high drop-off rates
                        $aiflt_insight = sprintf(
                            /* translators: %1$d: Drop-off rate percentage on the Product Page */
                            __( 'AI has found a high drop-off rate of %1$d%% on your Product Page. This may be due to a slow page load speed or unclear product information.', 'ai-flash-tune' ),
                            $aiflt_metrics['rate']
                        );
                    }
                }
                break;
            case 'Cart Page':
                if ($aiflt_metrics['scroll_stops'] > 0) {
                    $aiflt_insight = sprintf(
                        /* translators: %d: Number of scroll stops on the Cart Page */
                        __( 'The AI suggests that a high drop-off rate on your Cart Page is linked to **%d scroll stops**. Users may be hesitating at the shipping cost calculator or a complex form field.', 'ai-flash-tune' ),
                        $aiflt_metrics['scroll_stops']
                    );
                } else {
                    $aiflt_insight = sprintf(
                        /* translators: %d: Drop-off rate percentage on the Cart Page */
                        __( 'A drop-off rate of **%d%%** on the Cart Page is a major issue. This could be caused by unexpected costs, a lack of trust signals, or a confusing checkout process.', 'ai-flash-tune' ),
                        $aiflt_metrics['rate']
                    );
                }
                break;
            case 'Checkout Page':
                if ($aiflt_metrics['rage_clicks'] > 0) {
                    $aiflt_insight = sprintf(
                        /* translators: %1$d: Number of rage clicks on the checkout page */
                        __( 'Your Checkout Page is a top drop-off point with %1$s rage clicks. This often happens when users are frustrated with a buggy form, a broken payment gateway, or a sudden change in shipping costs.', 'ai-flash-tune' ),
                        '<strong>' . intval( $aiflt_metrics['rage_clicks'] ) . '</strong>'
                    );
                } else {
                    $aiflt_insight = sprintf(
                        /* translators: %1$d: Drop-off rate percentage on the checkout page */
                        __( 'A significant drop-off rate of %1$d%% on your Checkout Page suggests friction in the final step. Review your payment options, form fields, and security badges to build trust.', 'ai-flash-tune' ),
                        $aiflt_metrics['rate']
                    );
                 }
                break;
            default:
                $aiflt_insight = __('Our AI has analyzed user behavior on your top drop-off page and is working on an insight report.', 'ai-flash-tune');
                break;
        }
        return $aiflt_insight;
    }
    
    /**
     * Retrieves all funnel data for metrics calculation, with a date range for performance.
     *
     * @param string $aiflt_device_type
     * @param int $days The number of days of data to retrieve.
     * @return array
     */
    public static function get_all_funnel_data($aiflt_device_type = 'all', $days = 30) {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $aiflt_start_date = gmdate('Y-m-d H:i:s', strtotime("-$days days"));

        if ($aiflt_device_type !== 'all') {
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$table_name} WHERE timestamp >= %s AND device_type = %s",
                    $aiflt_start_date,
                    $aiflt_device_type
                ),
                ARRAY_A
            );
        } else {
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$table_name} WHERE timestamp >= %s",
                    $aiflt_start_date
                ),
                ARRAY_A
            );
        }

        return $results;
    }

    /**
     * Retrieves a string indicating when the last update occurred.
     *
     * @return string
     */
    public static function get_last_updated_string() {
        global $wpdb;
        $funnel_table = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        $latest_funnel = $wpdb->get_var("SELECT MAX(timestamp) FROM $funnel_table");
        $latest_behavior = $wpdb->get_var("SELECT MAX(timestamp) FROM $behavior_table");

        // FIX: Ensure NULL is not passed to strtotime(). 
        // Coalesce NULL database results to '0' so max() and strtotime() can handle them safely.
        $latest_funnel_ts = strtotime($latest_funnel ?? '0');
        $latest_behavior_ts = strtotime($latest_behavior ?? '0');
        
        $latest_timestamp = max($latest_funnel_ts, $latest_behavior_ts);
        
        if ($latest_timestamp > 0) {
            $diff = time() - $latest_timestamp;
            
            if ($diff < 60) {
                return sprintf(
                    /* translators: %1$d: Number of seconds ago visitor tracking data was recorded */
                    __( 'Last Updated: %1$d seconds ago (Visitor Data)', 'ai-flash-tune' ),
                    $diff
                );
            } elseif ($diff < 3600) {
                $minutes = round($diff / 60);
                return sprintf(
                    /* translators: %1$d: Number of minutes ago visitor tracking data was recorded */
                    __( 'Last Updated: %1$d minutes ago (Visitor Data)', 'ai-flash-tune' ),
                    $minutes
                );
            } else {
                $hours = round( $diff / 3600 );
                return sprintf(
                    /* translators: %1$d: Number of hours ago visitor tracking data was recorded */
                    __( 'Last Updated: %1$d hours ago (Visitor Data)', 'ai-flash-tune' ),
                    $hours
                );
            }
        }
        
        // CHANGE: Provide a clear message when no tracking data exists.
        return esc_html__('No visitor tracking data recorded yet.', 'ai-flash-tune');
    }
    
    /**
     * Retrieves a simplified set of metrics for the dashboard overview.
     *
     * @return array
     */
    public static function get_dashboard_metrics() {
        global $wpdb;
        $funnel_table = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');
        $aiflt_start_date = gmdate('Y-m-d H:i:s', strtotime("-7 days"));
        $aiflt_end_date = gmdate('Y-m-d H:i:s');
        
        // ---> FIX: Replaced the old logic with the new, robust abandonment calculation for consistency.
        
        // Step 1: Get completed sessions in the last 7 days.
        $completed_sessions = $wpdb->get_col($wpdb->prepare(
            "SELECT DISTINCT session_id FROM {$funnel_table} WHERE page_name = 'checkout_attempt' AND timestamp BETWEEN %s AND %s",
            $aiflt_start_date, $aiflt_end_date
        ));

        // Step 2: Get sessions with cart intent in the last 7 days.
        $sessions_with_cart_intent = $wpdb->get_results($wpdb->prepare(
            "
            (SELECT DISTINCT session_id FROM {$funnel_table} WHERE page_name = 'cart' AND timestamp BETWEEN %s AND %s)
            UNION
            (SELECT DISTINCT session_id FROM {$behavior_table} WHERE behavior_type = 'add_to_cart' AND timestamp BETWEEN %s AND %s)
            ",
            $aiflt_start_date, $aiflt_end_date, $aiflt_start_date, $aiflt_end_date
        ), ARRAY_A);
        $sessions_with_cart_intent_ids = wp_list_pluck($sessions_with_cart_intent, 'session_id');

        // Step 3: Use the same non-empty cart logic as the Abandoned Carts page for trust consistency.
        $abandoned_metrics = class_exists('AIFLT_Abandoned_Carts_Logic')
            ? AIFLT_Abandoned_Carts_Logic::get_abandoned_carts_metrics(gmdate('Y-m-d', strtotime('-7 days')), gmdate('Y-m-d'))
            : ['total_abandoned_carts' => 0];
        $abandoned_carts = (int) ($abandoned_metrics['total_abandoned_carts'] ?? 0);

        // --- Calculate other dashboard metrics ---
        $total_sessions = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT session_id) FROM {$funnel_table} WHERE timestamp >= %s", $aiflt_start_date));
        $total_sales_sessions = count($completed_sessions);
        $conversion_rate = ($total_sessions > 0) ? round(($total_sales_sessions / $total_sessions) * 100, 2) : 0;
        $avg_page_speed = (float) $wpdb->get_var($wpdb->prepare("SELECT AVG(page_speed) FROM {$funnel_table} WHERE page_speed IS NOT NULL AND timestamp >= %s", $aiflt_start_date));
        
        return [
            'total_sessions' => $total_sessions,
            'conversion_rate' => $conversion_rate,
            'abandoned_carts' => $abandoned_carts, // Use the new, correct count
            'page_speed' => round($avg_page_speed, 2),
            'last_updated' => self::get_last_updated_string(),
        ];
    }

    /**
     * Helper function to calculate funnel metrics for a specific period.
     * Accepts $segment_filter to ensure Sales/Completed Sessions match the segment.
     *
     * @param string $aiflt_start_date
     * @param string $aiflt_end_date
     * @param string $aiflt_device_type
     * @param string $segment_filter (Optional) SQL fragment for segmentation
     * @return array
     */
    private static function calculate_metrics_for_period($aiflt_start_date, $aiflt_end_date, $aiflt_device_type, $segment_filter = '') {
        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $device_filter = ($aiflt_device_type !== 'all') ? $wpdb->prepare(" AND device_type = %s", $aiflt_device_type) : "";

        // Query 1: Get unique session counts for each funnel page
        // CHANGE: Added {$segment_filter} to the SQL
        $page_counts_raw = $wpdb->get_results(
            $wpdb->prepare(
                "
                SELECT page_name, COUNT(DISTINCT session_id) as unique_sessions
                FROM {$table_name}
                WHERE timestamp BETWEEN %s AND %s {$device_filter} {$segment_filter}
                GROUP BY page_name
                ",
                $aiflt_start_date,
                $aiflt_end_date
            ),
            ARRAY_A
        );

        $page_counts = ['landing' => 0, 'shop' => 0, 'product' => 0, 'cart' => 0, 'checkout' => 0, 'checkout_attempt' => 0, 'sale' => 0];
        foreach ($page_counts_raw as $row) {
            if (isset($page_counts[$row['page_name']])) {
                $page_counts[$row['page_name']] = (int) $row['unique_sessions'];
            }
        }
        
        // 1. Get TOTAL unique sessions
        // Added {$segment_filter} to the SQL
        $total_sessions = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT session_id) FROM {$table_name} WHERE timestamp BETWEEN %s AND %s {$device_filter} {$segment_filter}",
            $aiflt_start_date,
            $aiflt_end_date
        ));
        
        // 2. Get abandoned sessions. Count only non-empty carts for consistency with the Abandoned Carts page.
        $abandoned_session_ids = AIFLT_Abandoned_Carts_Logic::get_abandoned_session_ids($aiflt_start_date, $aiflt_end_date);
        $abandoned_metrics = AIFLT_Abandoned_Carts_Logic::get_abandoned_carts_metrics(
            gmdate('Y-m-d', strtotime($aiflt_start_date)),
            gmdate('Y-m-d', strtotime($aiflt_end_date))
        );
        $drop_off_sessions_count = (int) ($abandoned_metrics['total_abandoned_carts'] ?? 0);

        // 3. Calculate Lost Sales
        $lost_sales = 0;
        if($drop_off_sessions_count > 0) {
            $session_ids_placeholders = implode(', ', array_fill(0, count($abandoned_session_ids), '%s'));
            
            // This query correctly gets the LATEST cart data for each abandoned session.
            $aiflt_cart_data_results = $wpdb->get_col(
                $wpdb->prepare(
                    "
                    SELECT f.cart_data FROM {$table_name} f 
                    INNER JOIN ( 
                        SELECT session_id, MAX(timestamp) as max_ts 
                        FROM {$table_name} 
                        WHERE session_id IN ({$session_ids_placeholders}) AND cart_data IS NOT NULL AND cart_data != '[]' 
                        GROUP BY session_id 
                    ) AS latest ON f.session_id = latest.session_id AND f.timestamp = latest.max_ts
                    ",
                    $abandoned_session_ids
                )
            );
            
            foreach($aiflt_cart_data_results as $aiflt_cart_data_serialized) {
                $aiflt_cart_data = maybe_unserialize($aiflt_cart_data_serialized);
                if (is_array($aiflt_cart_data) && !empty($aiflt_cart_data['item_count']) && $aiflt_cart_data['item_count'] > 0 && isset($aiflt_cart_data['cart_total'])) {
                    $decoded_total = html_entity_decode($aiflt_cart_data['cart_total']);
                    $total_string = preg_replace('/[^0-9.]/', '', $decoded_total);
                    $lost_sales += floatval($total_string);
                }
            }
        }

        // --- Return corrected numbers ---
        return [
            'total_sessions' => $total_sessions,
            'completed_sessions' => $page_counts['sale'], // Confirmed sales only: processing/completed by default.
            'drop_off_sessions' => $drop_off_sessions_count,
            'lost_sales' => $lost_sales,
            // CORRECTED: Conversion Rate is now based on ALL sessions, not just landing page sessions.
            'conversion_rate' => ($total_sessions > 0) ? round(($page_counts['sale'] / $total_sessions) * 100, 2) : 0
        ];
    }

    /**
     * Count recovered sessions from the Cart Recovery addon if available.
     * Preferred source: recovery token attribution. Fallback: old email log status.
     */
    private static function aiflt_get_recovered_drop_off_count($start, $end, $device_filter = '') {
        global $wpdb;
        $tokens_table = $wpdb->prefix . 'aiftcr_recovery_tokens';
        $tokens_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tokens_table));

        if ($tokens_exists === $tokens_table) {
            $recovered_date_expression = self::aiflt_table_column_exists($tokens_table, 'recovered_at')
                ? 'COALESCE(recovered_at, created_at)'
                : 'created_at';

            return (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id)
                 FROM {$tokens_table}
                 WHERE recovered_order_id IS NOT NULL
                   AND recovered_order_id > 0
                   AND {$recovered_date_expression} BETWEEN %s AND %s",
                $start,
                $end
            ));
        }

        $log_table = esc_sql($wpdb->prefix . 'aiftcr_email_log');
        $log_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $log_table));
        if ($log_exists === $log_table) {
            return (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id)
                 FROM {$log_table}
                 WHERE status = %s
                   AND sent_at BETWEEN %s AND %s",
                'Recovered',
                $start,
                $end
            ));
        }

        return 0;
    }

    /**
     * Retrieves the funnel data from the custom database table and calculates metrics.
     *
     * @param string $aiflt_device_type Filter data by 'desktop' or 'mobile' or 'all'.
     * @param string|null $aiflt_start_date The start date for filtering (Y-m-d).
     * @param string|null $aiflt_end_date The end date for filtering (Y-m-d).
     * @return array
     */
    public static function get_funnel_data($aiflt_device_type = 'all', $aiflt_start_date = null, $aiflt_end_date = null, $segment_type = null, $segment_value = null) {
        // ---> FIX: Removed the 'die("in");' statement that was blocking execution.
        global $wpdb;
        $funnel_table = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data'); // Add behavior table

        $current_period_start = $aiflt_start_date ? $aiflt_start_date . ' 00:00:00' : gmdate('Y-m-d H:i:s', strtotime("-30 days"));
        $current_period_end = $aiflt_end_date ? $aiflt_end_date . ' 23:59:59' : gmdate('Y-m-d H:i:s');
        $device_filter = ($aiflt_device_type !== 'all') ? $wpdb->prepare(" AND device_type = %s", $aiflt_device_type) : "";

        // --- NEW: Add segmentation logic ---
        $segment_filter = '';
        if ($segment_type && $segment_value) {
            if ($segment_type === 'device') {
                $segment_filter = $wpdb->prepare(" AND device_type = %s", $segment_value);
            } elseif ($segment_type === 'user_type') {
                if ($segment_value === 'guest') {
                    $segment_filter = " AND (user_id IS NULL OR user_id = 0)";
                } elseif ($segment_value === 'registered') {
                    $segment_filter = " AND user_id > 0";
                }
            }
        }
        // Calculate Grand Total sessions (regardless of device type filter)
        $grand_total_sessions = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id) FROM {$funnel_table} WHERE timestamp BETWEEN %s AND %s",
                $current_period_start,
                $current_period_end
            )
        );
        // Then calculate $total_sessions_count using the $device_filter as before (this is the FILTERED count)
        $total_sessions_count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(DISTINCT session_id) FROM {$funnel_table} WHERE timestamp BETWEEN %s AND %s {$device_filter}",
                $current_period_start,
                $current_period_end
            )
        );

        // If there are no sessions, return a complete default structure to prevent all warnings.
        if ($total_sessions_count === 0) {
            return [
                'total_sessions'        => 0,
                'completed_sessions'    => 0,
                'drop_off_sessions'     => 0,
                'recovered_drop_offs'   => 0, // ---> FIX: Added the missing key with a default value.
                'lost_sales'            => function_exists('wc_price') ? wc_price(0) : '0.00',
                'conversion_rate'       => 0,
                'recovery_rate'         => 0,
                'is_empty'              => true,
                'trends'                => [
                    'total_sessions'        => 'down',
                    'completed_sessions'    => 'down',
                    'drop_off_sessions'     => 'down',
                    'lost_sales'            => 'down',
                    'conversion_rate'       => 'down',
                    'recovery_rate'         => 'down',
                ],
                'landing_page_visits'   => 0,
                'product_page_views'    => 0, // This key remains for compatibility but is now part of 'product_interest_sessions'
                'product_interest_sessions' => 0,
                'cart_entries'          => 0,
                'checkout_starts'       => 0,
                'sales'                 => 0,
                'conversion_product'    => 0, // Renamed to conversion_interest
                'drop_off_product'      => 0, // Renamed to drop_off_interest
                'conversion_cart'       => 0,
                'drop_off_cart'         => 0,
                'conversion_checkout'   => 0,
                'drop_off_checkout'     => 0,
                'conversion_sales'      => 0,
            ];
        }

        // If we get here, there is data to process. Proceed with the original logic.
        $previous_period_days = (strtotime($current_period_end) - strtotime($current_period_start)) / 86400;
        $previous_period_start = gmdate('Y-m-d H:i:s', strtotime("$current_period_start -{$previous_period_days} days"));
        $previous_period_end = gmdate('Y-m-d H:i:s', strtotime("$current_period_start -1 second"));

        // Get metrics for both periods using the new helper function
        $current_metrics = self::calculate_metrics_for_period($current_period_start, $current_period_end, $aiflt_device_type, $segment_filter);
        $previous_metrics = self::calculate_metrics_for_period($previous_period_start, $previous_period_end, $aiflt_device_type, $segment_filter);
        
        // This section remains the same for calculating the funnel visualization data for the CURRENT period
        $page_counts_raw = $wpdb->get_results(
            $wpdb->prepare(
                "
                SELECT page_name, COUNT(DISTINCT session_id) as unique_sessions
                FROM {$funnel_table}
                WHERE timestamp BETWEEN %s AND %s {$device_filter} {$segment_filter}
                GROUP BY page_name
                ",
                $current_period_start,
                $current_period_end
            ),
            ARRAY_A
        );
        
        // ---> MODIFIED: Include 'shop' in the page counts array
        $page_counts = ['landing' => 0, 'shop' => 0, 'product' => 0, 'cart' => 0, 'checkout' => 0, 'sale' => 0];
        foreach ($page_counts_raw as $row) {
            if (isset($page_counts[$row['page_name']])) {
                $page_counts[$row['page_name']] = (int) $row['unique_sessions'];
            }
        }
        
        // --- FIX: Calculate Cart Initiated Sessions, respecting the DEVICE FILTER ---

        // Step 1: Get all sessions that had an 'add_to_cart' behavior (regardless of device)
        $raw_cart_intent_sessions = $wpdb->get_col($wpdb->prepare(
            "
            SELECT DISTINCT session_id FROM {$behavior_table} 
            WHERE behavior_type = 'add_to_cart' AND timestamp BETWEEN %s AND %s
            ",
            $current_period_start,
            $current_period_end
        ));

        $raw_cart_intent_sessions_count = count($raw_cart_intent_sessions);

        // CHANGE: Check if ANY filter is active (Global Device Filter OR Segment Filter)
        $needs_filtering = ($aiflt_device_type !== 'all') || !empty($segment_filter);

        if ($raw_cart_intent_sessions_count > 0 && $needs_filtering) {
            $placeholders = implode( ', ', array_fill( 0, $raw_cart_intent_sessions_count, '%s' ) );
            
            // Step 2: Cross-reference against funnel table to respect Device AND Segment filters
            // We reuse the $device_filter and $segment_filter strings which are already prepared safe SQL.
            $sql = "
                SELECT DISTINCT session_id 
                FROM {$funnel_table}
                WHERE session_id IN ({$placeholders})
                {$device_filter}
                {$segment_filter}
            ";
            
            $aiflt_cart_sessions_on_device = $wpdb->get_col($wpdb->prepare(
                $sql,
                $raw_cart_intent_sessions // Pass only the session IDs for the placeholders
            ));
            
            $aiflt_cart_initiated_sessions = count($aiflt_cart_sessions_on_device);

        } else {
            // If no filters are active, use the raw behavior count
            $aiflt_cart_initiated_sessions = $raw_cart_intent_sessions_count;
        }

        $page_counts['cart'] = $aiflt_cart_initiated_sessions;


        $aiflt_product_interest_sessions = (int) $wpdb->get_var($wpdb->prepare(
            "
            SELECT COUNT(DISTINCT session_id) FROM {$funnel_table} 
            WHERE page_name IN ('shop', 'product') AND timestamp BETWEEN %s AND %s {$device_filter} {$segment_filter}
            ",
            $current_period_start,
            $current_period_end
        ));

        // Direct cart/add-to-cart visitors may never hit a tracked product/landing page.
        // Treat cart intent as product interest so the visual funnel never shows impossible counts.
        $aiflt_product_interest_sessions = max(
            $aiflt_product_interest_sessions,
            (int) $page_counts['cart'],
            (int) $page_counts['checkout'],
            (int) $current_metrics['completed_sessions']
        );

        // The first funnel card represents Store Entry. Use all tracked sessions for the selected view,
        // not only literal landing-page hits, so direct cart/checkout traffic is included.
        $entry_sessions = max(
            (int) $current_metrics['total_sessions'],
            (int) $page_counts['landing'],
            (int) $page_counts['shop'],
            (int) $aiflt_product_interest_sessions,
            (int) $page_counts['cart'],
            (int) $page_counts['checkout'],
            (int) $current_metrics['completed_sessions']
        );
        $page_counts['landing'] = $entry_sessions;

        $page_counts['checkout'] = min($page_counts['cart'], $page_counts['checkout']);
        $abandoned_sessions = $wpdb->get_results(
            $wpdb->prepare(
                "
                SELECT DISTINCT session_id, user_id
                FROM {$funnel_table}
                WHERE session_id IN (
                    SELECT session_id 
                    FROM {$funnel_table} 
                    WHERE page_name = %s
                      AND timestamp BETWEEN %s AND %s {$device_filter}
                )
                AND session_id NOT IN (
                    SELECT session_id 
                    FROM {$funnel_table} 
                    WHERE page_name = %s
                      AND timestamp BETWEEN %s AND %s {$device_filter}
                )
                ",
                'cart', $current_period_start, $current_period_end,
                'sale', $current_period_start, $current_period_end
            ),
            ARRAY_A
        );

        $abandoned_user_ids = array_unique(
            array_column(
                array_filter($abandoned_sessions, function($s) {
                    return $s['user_id'] > 0;
                }),
                'user_id'
            )
        );
        $recovered_drop_offs = 0;
        if ( ! empty( $abandoned_user_ids ) ) {
            $placeholders = implode( ', ', array_fill( 0, count( $abandoned_user_ids ), '%d' ) );

            // Merge values in correct order: first 'sale', then user IDs, then $aiflt_start_date
            $params = array_merge( [ 'sale' ], $abandoned_user_ids, [ $current_period_start, $current_period_end ] );

            $recovered_drop_offs = (int) $wpdb->get_var(
                $wpdb->prepare(
                    "
                    SELECT COUNT(DISTINCT user_id) 
                    FROM {$funnel_table}
                    WHERE page_name = %s
                      AND user_id IN ($placeholders)
                      AND timestamp BETWEEN %s AND %s {$device_filter}
                    ",
                    $params
                )
            );
        }
        $aiflt_recovery_rate = ($current_metrics['drop_off_sessions'] > 0) ? round(($recovered_drop_offs / $current_metrics['drop_off_sessions']) * 100, 2) : 0;
        
        // ---> MODIFIED: Update all calculations to use the new "Product Interest" step.
        $conversion_interest = ($page_counts['landing'] > 0) ? round(($aiflt_product_interest_sessions / $page_counts['landing']) * 100, 2) : 0;
        $drop_off_interest = ($page_counts['landing'] > 0) ? 100 - $conversion_interest : 0;

        // ---> FIX: Conversion to cart is now based on 'Product Interest', not just 'Product Page'.
        $conversion_cart = ($aiflt_product_interest_sessions > 0) ? round(($page_counts['cart'] / $aiflt_product_interest_sessions) * 100, 2) : 0;
        $drop_off_cart = ($aiflt_product_interest_sessions > 0) ? 100 - $conversion_cart : 0;
        
        $conversion_checkout = ($page_counts['cart'] > 0) ? round(($page_counts['checkout'] / $page_counts['cart']) * 100, 2) : 0;
        $drop_off_checkout = ($page_counts['cart'] > 0) ? 100 - $conversion_checkout : 0;

        $conversion_sales = ($page_counts['checkout'] > 0) ? round(($current_metrics['completed_sessions'] / $page_counts['checkout']) * 100, 2) : 0;
        
        // REVISED: Calculate trends by comparing current vs previous period
        $trends = [
            'total_sessions' => ($current_metrics['total_sessions'] >= $previous_metrics['total_sessions']) ? 'up' : 'down',
            'completed_sessions' => ($current_metrics['completed_sessions'] >= $previous_metrics['completed_sessions']) ? 'up' : 'down',
            'drop_off_sessions' => ($current_metrics['drop_off_sessions'] <= $previous_metrics['drop_off_sessions']) ? 'down' : 'up', // Lower is better (trend down)
            'lost_sales' => ($current_metrics['lost_sales'] <= $previous_metrics['lost_sales']) ? 'down' : 'up', // Lower is better (trend down)
            'conversion_rate' => ($current_metrics['conversion_rate'] >= $previous_metrics['conversion_rate']) ? 'up' : 'down',
            'recovery_rate' => ($aiflt_recovery_rate >= 0) ? 'up' : 'down' // Simplified: recovery is always good
        ];

        return [
            'is_empty' => false, // We know this is false if we reached this point
            'total_sessions' => $grand_total_sessions,
            'completed_sessions' => $current_metrics['completed_sessions'],
            'drop_off_sessions' => $current_metrics['drop_off_sessions'],
            'recovered_drop_offs' => $recovered_drop_offs,
            'lost_sales' => function_exists('wc_price') ? wc_price($current_metrics['lost_sales']) : round($current_metrics['lost_sales'], 2),
            'conversion_rate' => $current_metrics['conversion_rate'],
            'recovery_rate' => $aiflt_recovery_rate,
            'landing_page_visits' => $page_counts['landing'],
            
            // ---> FIX: Return the combined count under the original key to prevent errors.
            'product_page_views' => $aiflt_product_interest_sessions,

            'cart_entries' => $page_counts['cart'],
            'checkout_starts' => $page_counts['checkout'],
            'sales' => $current_metrics['completed_sessions'],
            'conversion_product' => $conversion_interest, // Mapped to old key for JS compatibility
            'drop_off_product' => round($drop_off_interest, 2),     // Mapped to old key for JS compatibility
            'conversion_cart' => $conversion_cart,
            'drop_off_cart' => round($drop_off_cart, 2),
            'conversion_checkout' => $conversion_checkout,
            'drop_off_checkout' => round($drop_off_checkout, 2),
            'conversion_sales' => $conversion_sales,
            'trends' => $trends
        ];
    }
    
    /**
     * Gets a unique session ID for tracking.
     * This uses a cookie to ensure consistency across requests.
     *
     * @return string
     */
    // ---> FIX: Changed visibility from 'private' to 'public' to make it accessible.
    public static function get_session_id() {
        if (!isset($_COOKIE['aiflt_session_id'])) {
            $session_id = uniqid('aiflt_session_', true);
            // Set cookie with the SameSite=Lax attribute for modern browser compatibility.
            $options = [
                'expires' => time() + (86400 * 30),
                'path' => '/',
                'samesite' => 'Lax'
            ];
            setcookie('aiflt_session_id', $session_id, $options);
            $_COOKIE['aiflt_session_id'] = $session_id; // Make it available immediately on the same page load.
            return $session_id;
        }
        return sanitize_text_field($_COOKIE['aiflt_session_id']);
    }
    /**
     * Convert mixed formatted values into readable plain text for local AI fallbacks.
     */
    private static function aiflt_clean_metric_text($value): string {
        if (is_array($value) || is_object($value)) {
            return '';
        }
        $text = html_entity_decode(wp_strip_all_tags((string) $value), ENT_QUOTES, 'UTF-8');
        $text = preg_replace('/\s+/', ' ', $text);
        return trim((string) $text);
    }

    private static function aiflt_format_percentage($value): string {
        $number = is_numeric($value) ? (float) $value : 0.0;
        $text = number_format($number, 2, '.', '');
        $text = rtrim(rtrim($text, '0'), '.');
        return $text . '%';
    }

    /**
     * Strong preset instructions for the four Funnel Analysis AI buttons.
     */
    private static function aiflt_get_funnel_ai_preset_instructions(string $preset): string {
        $shared = implode("\n", [
            'Use only the sanitized aggregate funnel data in the JSON payload.',
            'Use the requested response language when possible.',
            'Treat fewer than 50 sessions as early / directional data.',
            'Separate evidence-backed facts from likely causes that still need verification.',
            'Use Estimated abandoned cart value, not Lost Sales.',
            'Never present payment issues, trust issues, shipping/tax issues, mobile issues, pricing issues, product issues, or UX/UI issues as proven unless those exact signals are supplied.',
            'Do not recommend discounts, free shipping, fake urgency, testimonials, retargeting ads, heatmaps, surveys, A/B tests, broad redesigns, or new payment gateways as primary actions unless the data supports them.',
            'Prefer AI Flash Tune pages: Funnel Analysis, Checkout Optimizer, Cart Recovery, Product Optimizer, Reports.',
            'Keep the answer short, operational, and safe to act on.',
            'Each preset must produce a different answer shape. Do not reuse the same generic structure for every button.',
        ]);

        $preset_lines = [
            'checkout_dropoff' => [
                'This preset answers: Why is checkout dropping?',
                'Return exactly these sections: Summary, Evidence Used, Likely Causes To Verify, Safe Next Checks, What Not To Change Yet.',
                'Focus on the checkout leak, cart-to-checkout gap, checkout starts, checkout drop-off, checkout field pressure if present, guest checkout/account clarity if unknown, and safe verification steps.',
                'Do not discuss cart recovery emails except as a secondary note if recovery rate is supplied.',
            ],
            'cart_abandonment' => [
                'This preset answers: Why are carts abandoned?',
                'Return exactly these sections: Summary, Evidence Used, Likely Causes To Verify, Safe Recovery Checks, What Not To Change Yet.',
                'Focus on abandoned carts/drop-off sessions, recovery rate, recovery clicks/recovered drop-offs, estimated abandoned cart value, top abandoned products if provided, cart restore links, email delivery, and recovery attribution.',
                'Do not turn this into a checkout field audit. Mention checkout friction only when cart entries are higher than checkout starts.',
            ],
            'fix_first' => [
                'This preset answers: What should I fix first?',
                'Return exactly these sections: Summary, Ranked Safe Fixes, What Not To Do First.',
                'Rank 3 to 4 actions only. For each action include Evidence, Action, Where to check, Safety, and Measure.',
                'Rank only safe fixes that are supported by the supplied data. Do not include unsupported payment, discount, trust, heatmap, survey, A/B test, or redesign recommendations.',
            ],
            'simple_words' => [
                'This preset answers: Explain in simple words.',
                'Return exactly these sections: Simple Explanation, What To Do First, What Not To Do Yet.',
                'Use plain English for a non-technical store owner. Avoid jargon, long labels, confidence scores, and blame.',
                'Keep it short: 4 to 8 short sentences plus a short numbered list.',
            ],
            'custom' => [
                'Answer the custom question, but keep the same safety rules. Include Summary, Evidence Used, Safe Next Steps, What Not To Change Yet.',
            ],
        ];

        $selected = $preset_lines[$preset] ?? $preset_lines['custom'];
        return $shared . "\n" . implode("\n", $selected);
    }

    /**
     * Deterministic local fallback for Funnel Analysis when external AI is unavailable or quota is reached.
     */
    private static function aiflt_build_funnel_ai_local_fallback(string $preset, array $data, string $language = 'English'): string {
        $total_sessions = (int) ($data['total_sessions'] ?? 0);
        $completed_sessions = (int) ($data['completed_sessions'] ?? 0);
        $drop_off_sessions = (int) ($data['drop_off_sessions'] ?? 0);
        $recovered_drop_offs = (int) ($data['recovered_drop_offs'] ?? 0);
        $cart_entries = (int) ($data['cart_entries'] ?? 0);
        $checkout_starts = (int) ($data['checkout_starts'] ?? 0);
        $checkout_drop = self::aiflt_format_percentage($data['drop_off_checkout'] ?? 0);
        $conversion_rate = self::aiflt_format_percentage($data['conversion_rate'] ?? 0);
        $recovery_rate = self::aiflt_format_percentage($data['recovery_rate'] ?? 0);
        $estimated_value = self::aiflt_clean_metric_text($data['lost_sales'] ?? '0');
        $cart_to_checkout_gap = max(0, $cart_entries - $checkout_starts);
        $confidence = $total_sessions < 50
            ? 'Data confidence is early / directional because fewer than 50 sessions are available.'
            : 'Data confidence is stronger, but changes should still be measured carefully.';

        if ($preset === 'simple_words') {
            return implode("\n\n", [
                '### Simple Explanation',
                sprintf('People are adding products to the cart, but many are not reaching or finishing checkout. Out of %1$d sessions, %2$d ended in a purchase.', $total_sessions, $completed_sessions),
                sprintf('%1$d cart entries became only %2$d checkout starts. That means the first place to check is the path from cart to checkout.', $cart_entries, $checkout_starts),
                sprintf('The estimated abandoned cart value is %1$s. Treat this as a helpful warning, not guaranteed lost revenue.', $estimated_value),
                '### What To Do First',
                '1. Test the store yourself on desktop and mobile from product → cart → checkout.' . "\n" .
                '2. Open AI Flash Tune → Checkout Optimizer and review checkout field pressure.' . "\n" .
                '3. Open AI Flash Tune → Cart Recovery and confirm recovery emails and restore links work.' . "\n" .
                '4. Change only one small, reversible thing and watch the result for 7–14 days.',
                '### What Not To Do Yet',
                'Do not change prices, add discounts, add payment gateways, run A/B tests, or redesign the store yet. The data is still small, so start with safe checks first.',
            ]);
        }

        if ($preset === 'fix_first') {
            return implode("\n\n", [
                '### Summary',
                sprintf('Fix checkout friction first, then verify cart recovery. The clearest visible leak is that %1$d cart entries became only %2$d checkout starts. %3$s', $cart_entries, $checkout_starts, $confidence),
                '### Ranked Safe Fixes',
                '#### 1. Review checkout field pressure' . "\n" .
                sprintf('- Evidence: Checkout drop-off is %1$s, and %2$d cart entries became %3$d checkout starts.', $checkout_drop, $cart_entries, $checkout_starts) . "\n" .
                '- Action: Open Checkout Optimizer and review the field audit before changing fields.' . "\n" .
                '- Where to check: AI Flash Tune → Checkout Optimizer.' . "\n" .
                '- Safety: Do not remove core billing, tax, shipping, fraud-prevention, or support fields.' . "\n" .
                '- Measure: Checkout starts, checkout completions, and support questions for 7–14 days.' . "\n\n" .
                '#### 2. Test the cart-to-checkout path' . "\n" .
                sprintf('- Evidence: %1$d cart entries became only %2$d checkout starts, leaving a gap of %3$d.', $cart_entries, $checkout_starts, $cart_to_checkout_gap) . "\n" .
                '- Action: Run one desktop and one mobile guest checkout test.' . "\n" .
                '- Where to check: Funnel Analysis, then the cart and checkout pages.' . "\n" .
                '- Safety: Do not change payment, tax, shipping, or layout settings during the first test.' . "\n" .
                '- Measure: Checkout starts and checkout completion rate.' . "\n\n" .
                '#### 3. Verify cart recovery basics' . "\n" .
                sprintf('- Evidence: %1$d drop-off sessions, %2$d recovered drop-offs, and %3$s recovery rate.', $drop_off_sessions, $recovered_drop_offs, $recovery_rate) . "\n" .
                '- Action: Confirm recovery capture, email delivery, restore links, and recovered-order attribution.' . "\n" .
                '- Where to check: AI Flash Tune → Cart Recovery.' . "\n" .
                '- Safety: Keep recovery emails calm. Do not add discounts or fake urgency by default.' . "\n" .
                '- Measure: Recovery clicks and recovered orders.' . "\n\n" .
                '#### 4. Wait before broader optimization' . "\n" .
                sprintf('- Evidence: Only %1$d sessions are available.', $total_sessions) . "\n" .
                '- Action: Make one low-risk change, then wait for fresh data.' . "\n" .
                '- Where to check: AI Flash Tune → Reports / Funnel Analysis.' . "\n" .
                '- Safety: Avoid bundled changes that make results hard to read.' . "\n" .
                '- Measure: Conversion rate and drop-off sessions for 7–14 days.',
                '### What Not To Do First',
                'Do not start with discounts, new payment gateways, trust badges, A/B testing, heatmaps, surveys, retargeting ads, or broad redesigns unless new data proves they are needed.',
            ]);
        }

        if ($preset === 'cart_abandonment') {
            return implode("\n\n", [
                '### Summary',
                sprintf('The store has %1$d abandoned/drop-off sessions and a %2$s recovery rate. This does not prove why shoppers abandoned, but it shows recovery tracking and recovery emails should be verified before changing prices or offering discounts. %3$s', $drop_off_sessions, $recovery_rate, $confidence),
                '### Evidence Used',
                sprintf('- Abandoned/drop-off sessions: %1$d', $drop_off_sessions) . "\n" .
                sprintf('- Estimated abandoned cart value: %1$s', $estimated_value) . "\n" .
                sprintf('- Recovery rate: %1$s', $recovery_rate) . "\n" .
                sprintf('- Recovered drop-offs: %1$d', $recovered_drop_offs) . "\n" .
                sprintf('- Cart entries → checkout starts: %1$d → %2$d', $cart_entries, $checkout_starts),
                '### Likely Causes To Verify',
                '- Recovery emails may not be enabled, delivered, opened, or clicked.' . "\n" .
                '- Cart restore links may not have been tested yet.' . "\n" .
                '- Checkout friction may still be contributing because cart entries are higher than checkout starts.' . "\n" .
                '- Product variation, shipping, tax, or return clarity may need review for top abandoned products, but do not treat that as proven yet.',
                '### Safe Recovery Checks',
                '- Open AI Flash Tune → Cart Recovery and confirm recovery capture is working.' . "\n" .
                '- Create a test abandoned cart and confirm it appears in the recovery list.' . "\n" .
                '- Send a test recovery email and check inbox/spam placement.' . "\n" .
                '- Open the restore link in a private browser and confirm the correct products and quantities return.' . "\n" .
                '- Complete one test order through the restore link and confirm recovered-order attribution works.',
                '### What Not To Change Yet',
                'Do not add discounts, free shipping, fake urgency, testimonials, retargeting ads, pricing changes, or payment changes until recovery capture, delivery, restore links, attribution, and checkout friction have been verified.',
            ]);
        }

        // Default and checkout_dropoff.
        return implode("\n\n", [
            '### Summary',
            sprintf('Checkout is the clearest visible leak. %1$d cart entries became %2$d checkout starts, and checkout drop-off is %3$s. %4$s', $cart_entries, $checkout_starts, $checkout_drop, $confidence),
            '### Evidence Used',
            sprintf('- Total sessions: %1$d', $total_sessions) . "\n" .
            sprintf('- Completed sessions: %1$d', $completed_sessions) . "\n" .
            sprintf('- Cart entries: %1$d', $cart_entries) . "\n" .
            sprintf('- Checkout starts: %1$d', $checkout_starts) . "\n" .
            sprintf('- Checkout drop-off: %1$s', $checkout_drop) . "\n" .
            sprintf('- Estimated abandoned cart value: %1$s', $estimated_value),
            '### Likely Causes To Verify',
            '- The cart-to-checkout transition may be unclear or too easy to miss.' . "\n" .
            '- Checkout field pressure may be contributing, but verify this in Checkout Optimizer before changing fields.' . "\n" .
            '- Guest checkout/account clarity may matter, but only change account settings after confirming the current setting.',
            '### Safe Next Checks',
            '- Open AI Flash Tune → Checkout Optimizer and review checkout field pressure.' . "\n" .
            '- Run one desktop and one mobile guest checkout test from product → cart → checkout.' . "\n" .
            '- Check cart button visibility, cart totals, coupon area, variation details, and shipping/tax clarity without changing payment settings.',
            '### What Not To Change Yet',
            'Do not add payment gateways, discounts, trust badges, A/B tests, heatmaps, surveys, or broad redesigns until the internal funnel and checkout checks are complete.',
        ]);
    }

    /**
     * JSON-first instructions for Zywrap base wrappers. OpenAI/local keep the existing Markdown prompt.
     */
    private static function aiflt_get_funnel_ai_zywrap_instructions(string $preset): string {
        $shared = [
            'Return only one valid JSON object. Do not wrap JSON in Markdown or a code fence.',
            'Use only the supplied sanitized aggregate funnel data. Do not invent causes, revenue, user motivations, payment problems, shipping problems, product problems, mobile problems, or trust problems.',
            'If total sessions are below 50, set confidence or dataReadiness to early/directional and avoid strong conclusions.',
            'Use supplied numbers exactly when available: total sessions, completed sessions, cart entries, checkout starts, checkout drop-off, conversion rate, recovered drop-offs, and estimated abandoned cart value.',
            'Treat estimated abandoned cart value as directional value at risk, not accounting-grade revenue.',
            'Do not recommend discounts, free shipping, fake urgency, testimonials, retargeting ads, new payment gateways, heatmaps, surveys, A/B tests, broad redesigns, or competitor analysis as first actions unless the supplied data supports that exact action.',
            'Every action must be safe, reversible, measurable, and practical for a WooCommerce store owner.',
            'Prefer AI Flash Tune pages in whereToCheck: Funnel Analysis, Checkout Optimizer, Cart Recovery, Product Optimizer, Reports. Mention WooCommerce settings only when needed.',
        ];

        $by_preset = [
            'checkout_dropoff' => [
                'Use keys when possible: summary, confidence, dataReadiness, evidenceUsed, likelyCausesToVerify, safeNextChecks, whatNotToChangeYet.',
                'Focus on the checkout leak, cart-to-checkout gap, checkout starts, checkout drop-off, checkout field pressure if present, guest checkout/account clarity if unknown, and safe verification steps.',
            ],
            'cart_abandonment' => [
                'Use keys when possible: summary, confidence, dataReadiness, evidenceUsed, likelyCausesToVerify, safeRecoveryChecks, whatNotToChangeYet.',
                'Focus on abandoned carts/drop-off sessions, recovery rate, recovery clicks/recovered drop-offs, estimated abandoned cart value, top abandoned products if provided, cart restore links, email delivery, and recovery attribution.',
            ],
            'fix_first' => [
                'Use keys when possible: summary, confidence, dataReadiness, evidenceUsed, rankedSafeFixes, whatNotToDoFirst.',
                'Rank 3 to 4 actions only. For each action include title, evidence, action, whereToCheck, safety, and measure.',
            ],
            'simple_words' => [
                'Use keys when possible: simpleExplanation, confidence, evidenceUsed, whatToDoFirst, whatNotToDoYet.',
                'Use plain English for a non-technical store owner. Keep it short and avoid jargon.',
            ],
            'custom' => [
                'Use keys when possible: summary, confidence, dataReadiness, evidenceUsed, safeNextSteps, whatNotToChangeYet.',
                'Answer the custom question directly while keeping all safety rules.',
            ],
        ];

        return implode("\n", array_merge($shared, $by_preset[$preset] ?? $by_preset['custom']));
    }

    /**
     * Gets an AI-generated analysis of a specific drop-off point through the shared AI service.
     */
    public static function get_ai_analysis($prompt, $stage, $language = 'English', $preset = 'custom') {
        $aiflt_funnel_data = self::get_funnel_data('all');
        $preset = sanitize_key($preset ?: 'custom');
        $local_fallback = self::aiflt_build_funnel_ai_local_fallback($preset, $aiflt_funnel_data, $language);

        if (!class_exists('AIFLT_AI_Service')) {
            return $local_fallback;
        }

        $provider = sanitize_key((string) get_option('aiflt_ai_provider', 'local'));
        $instructions = ($provider === 'zywrap')
            ? self::aiflt_get_funnel_ai_zywrap_instructions($preset)
            : self::aiflt_get_funnel_ai_preset_instructions($preset);

        $result = AIFLT_AI_Service::run_action('free_dropoff_question', [
            'question' => sanitize_textarea_field($prompt),
            'question_preset' => $preset,
            'funnel_stage' => sanitize_text_field($stage),
            'response_language' => sanitize_text_field($language),
            'funnel_data' => $aiflt_funnel_data,
        ], [
            'instructions' => $instructions,
            'local_fallback' => $local_fallback,
        ]);

        if ($result['status'] === 'success' || $result['status'] === 'local') {
            return $result['content'];
        }

        if (!empty($result['content'])) {
            return $result['content'] . "\n\n" . '### AI status' . "\n" . sprintf(
                /* translators: %s: AI availability reason */
                __('External AI was not used: %s', 'ai-flash-tune'),
                sanitize_text_field($result['message'] ?? '')
            );
        }

        return $local_fallback;
    }

    /** Centralized API key getter (supports old/new option names) */
    private static function get_openai_api_key(): string {
        $key = trim((string) get_option('aiflt_ai_api_key', ''));
        return $key;
    }

    /** Pull compact 30-day stats from your two tables */
    private static function aiflt_get_system_stats(): array {
        global $wpdb;

        $funnel = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        // Funnel stats
        $funnel_stats = $wpdb->get_results("
            SELECT page_name, COUNT(*) AS visits, ROUND(AVG(page_speed),2) AS avg_speed
            FROM {$funnel}
            WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY page_name
            ORDER BY visits DESC
            LIMIT 10
        ", ARRAY_A) ?: [];

        // Behavior stats
        $behavior_stats = $wpdb->get_results("
            SELECT behavior_type, COUNT(*) AS total
            FROM {$behavior}
            WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY behavior_type
            ORDER BY total DESC
            LIMIT 10
        ", ARRAY_A) ?: [];

        // Abandoned carts (your event type)
        $abandoned_carts = (int) $wpdb->get_var("
            SELECT COUNT(*)
            FROM {$behavior}
            WHERE behavior_type = 'cart_abandon'
              AND timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        ");

        // Device breakdown
        $device_stats = $wpdb->get_results("
            SELECT device_type, COUNT(*) as total
            FROM {$funnel}
            WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            GROUP BY device_type
        ", ARRAY_A) ?: [];

        return [
            'funnel'          => $funnel_stats,
            'behavior'        => $behavior_stats,
            'abandoned_carts' => $abandoned_carts,
            'devices'         => $device_stats,
        ];
    }

    /**
     * Helper to get the native language name from a locale code.
     * Uses WP API first, then falls back to a comprehensive local list.
     */
    private static function get_language_name_from_locale($locale) {
        // 1. Try WordPress Core Translation API first (most accurate for installed languages)
        require_once( ABSPATH . 'wp-admin/includes/translation-install.php' );
        $translations = wp_get_available_translations();
        
        if ( isset( $translations[$locale] ) && isset( $translations[$locale]['native_name'] ) ) {
            return $translations[$locale]['native_name'];
        }
        
        // 2. Comprehensive Fallback List (Matches the dropdown list)
        $all_languages = [
            'en_US' => 'English (US)',
            'en_GB' => 'English (UK)',
            'af'    => 'Afrikaans',
            'ar'    => 'Arabic',
            'az'    => 'Azerbaijani',
            'be'    => 'Belarusian',
            'bg_BG' => 'Bulgarian',
            'bn_BD' => 'Bengali',
            'bs_BA' => 'Bosnian',
            'ca'    => 'Catalan',
            'ceb'   => 'Cebuano',
            'cs_CZ' => 'Czech',
            'cy'    => 'Welsh',
            'da_DK' => 'Danish',
            'de_DE' => 'German',
            'de_CH' => 'German (Switzerland)',
            'el'    => 'Greek',
            'es_ES' => 'Spanish (Spain)',
            'es_MX' => 'Spanish (Mexico)',
            'es_CO' => 'Spanish (Colombia)',
            'es_AR' => 'Spanish (Argentina)',
            'es_CL' => 'Spanish (Chile)',
            'et'    => 'Estonian',
            'eu'    => 'Basque',
            'fa_IR' => 'Persian',
            'fi'    => 'Finnish',
            'fr_FR' => 'French (France)',
            'fr_CA' => 'French (Canada)',
            'fr_BE' => 'French (Belgium)',
            'ga'    => 'Irish',
            'gl_ES' => 'Galician',
            'gu'    => 'Gujarati',
            'he_IL' => 'Hebrew',
            'hi_IN' => 'Hindi',
            'hr'    => 'Croatian',
            'hu_HU' => 'Hungarian',
            'hy'    => 'Armenian',
            'id_ID' => 'Indonesian',
            'is_IS' => 'Icelandic',
            'it_IT' => 'Italian',
            'ja'    => 'Japanese',
            'ka_GE' => 'Georgian',
            'km'    => 'Khmer',
            'kn'    => 'Kannada',
            'ko_KR' => 'Korean',
            'lt_LT' => 'Lithuanian',
            'lv'    => 'Latvian',
            'mk_MK' => 'Macedonian',
            'ml_IN' => 'Malayalam',
            'mn'    => 'Mongolian',
            'mr'    => 'Marathi',
            'ms_MY' => 'Malay',
            'my_MM' => 'Burmese',
            'nb_NO' => 'Norwegian (Bokmål)',
            'nl_NL' => 'Dutch',
            'nl_BE' => 'Dutch (Belgium)',
            'pa_IN' => 'Punjabi',
            'pl_PL' => 'Polish',
            'pt_PT' => 'Portuguese (Portugal)',
            'pt_BR' => 'Portuguese (Brazil)',
            'ro_RO' => 'Romanian',
            'ru_RU' => 'Russian',
            'si_LK' => 'Sinhala',
            'sk_SK' => 'Slovak',
            'sl_SI' => 'Slovenian',
            'sq'    => 'Albanian',
            'sr_RS' => 'Serbian',
            'sv_SE' => 'Swedish',
            'sw'    => 'Swahili',
            'ta_IN' => 'Tamil',
            'te'    => 'Telugu',
            'th'    => 'Thai',
            'tl'    => 'Tagalog',
            'tr_TR' => 'Turkish',
            'uk'    => 'Ukrainian',
            'ur'    => 'Urdu',
            'uz_UZ' => 'Uzbek',
            'vi'    => 'Vietnamese',
            'zh_CN' => 'Chinese (Simplified)',
            'zh_TW' => 'Chinese (Traditional)',
            'zh_HK' => 'Chinese (Hong Kong)',
        ];

        // Check for exact match
        if ( isset( $all_languages[$locale] ) ) {
            return $all_languages[$locale];
        }

        // Check for partial match (e.g. 'fr_FR' matches 'fr')
        $short_code = substr($locale, 0, 2);
        foreach ($all_languages as $key => $val) {
            if (substr($key, 0, 2) === $short_code) {
                return $val;
            }
        }
        
        return 'English'; // Default fallback
    }
    /**
     * Generate the limited free weekly AI report through the shared AI service.
     */
    private static function aiflt_generate_ai_report(): array {
        $stats = self::aiflt_get_system_stats();
        $language_context = class_exists('AIFLT_AI_Service') ? AIFLT_AI_Service::get_ai_response_language_context() : [
            'locale' => get_locale(),
            'name' => self::get_language_name_from_locale(get_locale()),
        ];
        $current_locale = sanitize_text_field((string) ($language_context['locale'] ?? get_locale()));
        $language_name = sanitize_text_field((string) ($language_context['name'] ?? self::get_language_name_from_locale($current_locale)));

        $local = __('Local snapshot: review your dashboard priority checks first. Connect OpenAI or Zywrap in Settings to generate a short AI weekly report.', 'ai-flash-tune');

        if (!class_exists('AIFLT_AI_Service')) {
            return [
                'status' => 'success',
                'report' => $local,
                'locale' => $current_locale,
            ];
        }
        $result = AIFLT_AI_Service::run_action('free_weekly_ai_report', [
            'response_language' => sanitize_text_field($language_name),
            'locale' => sanitize_text_field($current_locale),
            'stats' => $stats,
        ], [
            'instructions' => __('Write a short weekly report in 1-2 sentences. Highlight only the most important friction point or say that more data is needed.', 'ai-flash-tune'),
            'local_fallback' => $local,
        ]);

        if ($result['status'] === 'success' || $result['status'] === 'local') {
            return [
                'status' => 'success',
                'report' => (string) $result['content'],
                'locale' => $current_locale,
            ];
        }

        return [
            'status' => 'error',
            'error_code' => 'ai_service_error',
            'error_message' => $result['message'],
        ];
    }

    /**
     * Public: fetch weekly report using options-table cache.
     * Returns: ['report' => string, 'next_available' => int, 'status' => 'success'|'error']
     */
    public static function get_ai_weekly_report(): array {
        $option_key = 'aiflt_ai_report';
        $cache_days = 7;
        $now        = current_time('timestamp');
        $current_locale = get_locale(); // CHANGE: Get current locale

        $saved = get_option($option_key, []);

        // Reuse only successful cached report IF the language matches
        if (!empty($saved['status']) && $saved['status'] === 'success' && !empty($saved['timestamp'])) {
            $days_diff = (int) floor( ($now - (int) $saved['timestamp']) / DAY_IN_SECONDS );
            
            // Check if cached locale matches current site locale
            $cached_locale = isset($saved['locale']) ? $saved['locale'] : 'en_US'; // Default to en_US for old caches
            
            if ($days_diff < $cache_days && !empty($saved['report']) && $cached_locale === $current_locale) {
                return [
                    'report'         => (string) $saved['report'],
                    'next_available' => max(0, $cache_days - $days_diff),
                    'status'         => 'success',
                ];
            }
        }

        // Generate fresh
        $result = self::aiflt_generate_ai_report();

        // Save to options
        $to_save = [
            'status'    => $result['status'],
            'timestamp' => $now,
            'locale'    => $current_locale, // CHANGE: Save the locale
        ];

        if ($result['status'] === 'success') {
            $to_save['report'] = $result['report'];
            if ( false === get_option($option_key, false) ) {
                add_option($option_key, $to_save, '', 'no');
            } else {
                update_option($option_key, $to_save);
            }
            return [
                'report'         => $result['report'],
                'next_available' => $cache_days,
                'status'         => 'success',
            ];
        }

        // Error path: store error meta but do NOT lock for 7 days (allows quick retry after fixing)
        $to_save['error_code']    = $result['error_code'] ?? 'unknown';
        $to_save['error_message'] = $result['error_message'] ?? 'Unknown error';

        if ( false === get_option($option_key, false) ) {
            add_option($option_key, $to_save, '', 'no');
        } else {
            update_option($option_key, $to_save);
        }

        return [
            'report'         => '⚠️ ' . esc_html($to_save['error_message']),
            'next_available' => 0,            // can retry immediately after fixing
            'status'         => 'error',
        ];
    }
    /**
     * New helper method to check if tracking is allowed based on roles, IP, and master switch.
     */
    private static function is_tracking_allowed() {
        // 1. Get all relevant settings
        $settings = [
            'enable_tracking' => get_option('aiflt_enable_tracking', '1'),
            'excluded_roles'  => get_option('aiflt_excluded_roles', []),
            'excluded_ips'    => get_option('aiflt_excluded_ips', ''),
        ];

        // 2. Master switch check
        if (empty($settings['enable_tracking']) || $settings['enable_tracking'] !== '1') {
            return false;
        }

        // 3. User role check
        if (is_user_logged_in()) {
            $user = wp_get_current_user();
            $user_roles = (array) $user->roles;
            $excluded_roles = (array) $settings['excluded_roles'];
            if (!empty(array_intersect($user_roles, $excluded_roles))) {
                return false;
            }
        }

        // 4. IP address check (CRITICAL FIX for the user's issue)
        $user_ip = sanitize_text_field($_SERVER['REMOTE_ADDR'] ?? '');
        $excluded_ips_raw = $settings['excluded_ips'];
        if (!empty($excluded_ips_raw) && !empty($user_ip)) {
            $excluded_ips = array_map('trim', explode("\n", str_replace("\r", "", $excluded_ips_raw)));
            if (in_array($user_ip, $excluded_ips)) {
                return false;
            }
        }

        return true;
    }
     /**
     * Server-side tracking for 'add to cart' actions to ensure 100% accuracy.
     * This captures non-AJAX additions from single product pages.
     *
     * @param string $aiflt_cart_item_key
     * @param int    $aiflt_product_id
     * @param int    $quantity
     * @param int    $variation_id
     * @param array  $variation
     * @param array  $aiflt_cart_item_data
     */
    public static function log_add_to_cart_action01($arg1 = null, $arg2 = null, $arg3 = null, $arg4 = null, $arg5 = null, $arg6 = null) {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }

        // --- Step 1: Identify which hook called the function ---
        $current_hook = current_action();

        if ($current_hook === 'woocommerce_add_to_cart') {
            // This handles item addition (all 6 parameters are available, use arg2 for product_id)
            $aiflt_product_id = $arg2; 
            // ... logging logic for addition ...
            
        } elseif ($current_hook === 'woocommerce_after_cart_item_quantity_update') {
            // This handles quantity update (only 3 parameters available, arg2 is new quantity)
            $aiflt_cart_item_key = $arg1;
            $quantity = $arg2; 
            
            // Since the cart is updated, we can simply call the general cart logging logic:
            // Force cart recalculation and logging (as already fixed in previous steps)
            if (WC()->cart) {
                WC()->cart->calculate_totals(); 
                // Call the final tracking function once the cart is stable
                // ... (Your final logging code here) ...
            }
        }

        $session_id = self::get_session_id();
        $aiflt_device_type = wp_is_mobile() ? 'mobile' : 'desktop';
        $user_id = get_current_user_id();
        
        // die('012');
        // ---> FIX: This is the crucial part. We get the current cart data during the AJAX call.
        $aiflt_cart_data = self::get_woocommerce_cart_data();

        global $wpdb;
        $funnel_table = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        // Check if IP anonymization is enabled
        $anonymize_ip = get_option('aiflt_anonymize_ip', '0') === '1';
        $ip_address = sanitize_text_field($_SERVER['REMOTE_ADDR'] ?? '');
        if ($anonymize_ip) {
            $ip_address = wp_privacy_anonymize_ip($ip_address);
        }

        $record_id = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$funnel_table} WHERE session_id = %s AND page_name = %s",
            $session_id,
            'cart'
        ));

       $data = [
                'user_id'     => $user_id,
                'device_type' => $aiflt_device_type,
                'timestamp'   => current_time('mysql'),
                'cart_data'   => maybe_serialize($aiflt_cart_data),
                'ip_address'  => $ip_address,
            ];

            if ($record_id) {
                // Record exists: UPDATE
                $wpdb->update(
                    $funnel_table,
                    $data,
                    ['id' => $record_id],
                    ['%d', '%s', '%s', '%s', '%s', '%s'], // Data formats
                    ['%d'] // WHERE format
                );
            } else {
                // Record does not exist: INSERT (needs session_id and page_name added to data)
                $data['session_id'] = $session_id;
                $data['page_name'] = 'cart';
                $wpdb->insert($funnel_table, $data);
            }

        // --- Log the Behavior Event ('add_to_cart') ---

        $wpdb->insert($behavior_table, [
            'session_id'     => $session_id,
            'user_id'        => $user_id,
            'product_id'     => intval($aiflt_product_id),
            'behavior_type'  => 'add_to_cart',
            'behavior_value' => wp_json_encode(['quantity' => $quantity]),
            'user_agent'     => sanitize_text_field($_SERVER['HTTP_USER_AGENT'] ?? ''),
            'ip_address'     => $ip_address, // Use the (potentially anonymized) IP address
            'timestamp'      => current_time('mysql')
        ]);
    }
    public static function log_add_to_cart_action($arg1 = null, $arg2 = null, $arg3 = null, $arg4 = null, $arg5 = null, $arg6 = null) {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }

        // --- Step 1: Identify which hook called the function ---
        $current_hook = current_action();
        
        // --- Initialize core variables based on the hook ---
        $aiflt_product_id = 0;
        $quantity = 0;
        
        if ($current_hook === 'woocommerce_add_to_cart') {
            // Hook (6 args): $aiflt_cart_item_key, $aiflt_product_id, $quantity, $variation_id, $variation, $aiflt_cart_item_data
            $aiflt_product_id = $arg2; // Product ID is the 2nd argument
            $quantity = $arg3;   // Quantity is the 3rd argument
            
        } elseif ($current_hook === 'woocommerce_after_cart_item_quantity_update') {
            // Hook (3 args): $aiflt_cart_item_key, $quantity, $old_quantity
            $aiflt_cart_item_key = $arg1;
            $quantity = $arg2; // New quantity is the 2nd argument
            
            // Find product ID from cart item key
            $aiflt_cart_item = WC()->cart->get_cart_item($aiflt_cart_item_key);
            if ($aiflt_cart_item) {
                 $aiflt_product_id = $aiflt_cart_item['product_id'];
            }
        }

        // Exit if essential data is missing
        if (empty($aiflt_product_id) || empty($quantity)) {
            return;
        }
        
        // --- Force cart data to be accurate before logging ---
        if (WC()->cart) {
            WC()->cart->calculate_totals(); 
        }
        
        $session_id = self::get_session_id();
        $aiflt_device_type = wp_is_mobile() ? 'mobile' : 'desktop';
        $user_id = get_current_user_id();
        $aiflt_cart_data = self::get_woocommerce_cart_data(); // Fetch updated cart data

        global $wpdb;
        $funnel_table = esc_sql($wpdb->prefix . 'aiflt_funnel_data');
        $behavior_table = esc_sql($wpdb->prefix . 'aiflt_behavior_data');

        // Check if IP anonymization is enabled
        $anonymize_ip = get_option('aiflt_anonymize_ip', '0') === '1';
        $ip_address = sanitize_text_field($_SERVER['REMOTE_ADDR'] ?? '');
        if ($anonymize_ip) {
            $ip_address = wp_privacy_anonymize_ip($ip_address);
        }

        $record_id = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$funnel_table} WHERE session_id = %s AND page_name = %s",
            $session_id,
            'cart'
        ));

        $data = [
                'user_id'     => $user_id,
                'device_type' => $aiflt_device_type,
                'timestamp'   => current_time('mysql'),
                'cart_data'   => maybe_serialize($aiflt_cart_data),
                'ip_address'  => $ip_address,
            ];

            if ($record_id) {
                // Record exists: UPDATE
                $wpdb->update(
                    $funnel_table,
                    $data,
                    ['id' => $record_id],
                    ['%d', '%s', '%s', '%s', '%s', '%s'], // Data formats
                    ['%d'] // WHERE format
                );
            } else {
                // Record does not exist: INSERT (needs session_id and page_name added to data)
                $data['session_id'] = $session_id;
                $data['page_name'] = 'cart';
                $wpdb->insert($funnel_table, $data);
            }

        // --- Log the Behavior Event ('add_to_cart') ---

        $wpdb->insert($behavior_table, [
            'session_id'     => $session_id,
            'user_id'        => $user_id,
            'product_id'     => intval($aiflt_product_id),
            'behavior_type'  => 'add_to_cart',
            'behavior_value' => wp_json_encode(['quantity' => $quantity]),
            'user_agent'     => sanitize_text_field($_SERVER['HTTP_USER_AGENT'] ?? ''),
            'ip_address'     => $ip_address, // Use the (potentially anonymized) IP address
            'timestamp'      => current_time('mysql')
        ]);
    }
    /**
     * --- NEW: AJAX callback to save a guest's email address. ---
     */
    public static function ajax_capture_guest_email() {
        // CRITICAL FIX: Check if tracking is allowed based on roles, IP, and master switch.
        if (!self::is_tracking_allowed()) {
            return;
        }
        check_ajax_referer('aiflt-tracking-nonce', '_ajax_nonce'); // Check nonce passed from frontend

        $session_id = isset($_POST['session_id']) ? sanitize_text_field($_POST['session_id']) : '';
        $guest_email = isset($_POST['guest_email']) ? sanitize_email($_POST['guest_email']) : '';

        if (empty($session_id) || !is_email($guest_email)) {
            wp_send_json_error(__('Invalid data provided.', 'ai-flash-tune'));
        }

        global $wpdb;
        $table_name = esc_sql($wpdb->prefix . 'aiflt_funnel_data');

        // Update all records for this session with the guest's email.
        $wpdb->update(
            $table_name,
            ['guest_email' => $guest_email],
            ['session_id' => $session_id],
            ['%s'],
            ['%s']
        );

        wp_send_json_success(__('Guest email captured.', 'ai-flash-tune'));
    }

    public static function ajax_get_segmented_data() {
        check_ajax_referer('aiftfs-get-segmented-nonce', '_ajax_nonce');
        // check_ajax_referer('aiftfs-nonce', '_ajax_nonce');
        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Permission denied.', 'ai-flash-tune')]);
        }

        $aiflt_start_date = isset($_POST['start_date']) ? sanitize_text_field($_POST['start_date']) : null;
        $aiflt_end_date = isset($_POST['end_date']) ? sanitize_text_field($_POST['end_date']) : null;

        $segment_a_type = isset($_POST['segment_a_type']) ? sanitize_key($_POST['segment_a_type']) : 'device';
        $segment_a_value = isset($_POST['segment_a_value']) ? sanitize_key($_POST['segment_a_value']) : 'desktop';
        
        $segment_b_type = isset($_POST['segment_b_type']) ? sanitize_key($_POST['segment_b_type']) : 'device';
        $segment_b_value = isset($_POST['segment_b_value']) ? sanitize_key($_POST['segment_b_value']) : 'mobile';

        // Fetch data for both segments by calling the main plugin's (modified) logic function
        $data_a = AIFLT_Funnel_Analyzer_Logic::get_funnel_data('all', $aiflt_start_date, $aiflt_end_date, $segment_a_type, $segment_a_value);
        $data_b = AIFLT_Funnel_Analyzer_Logic::get_funnel_data('all', $aiflt_start_date, $aiflt_end_date, $segment_b_type, $segment_b_value);

        wp_send_json_success([
            'segment_a' => $data_a,
            'segment_b' => $data_b,
        ]);
    }
}
