<?php
/**
 * Analytics Endpoint for BoostBot REST API.
 *
 * @package BoostBot
 */

// Prevent direct access to the file.
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if (!defined('ABSPATH')) {
    exit;
}

/**
 * BoostBot_Analytics_Endpoint class.
 *
 * Provides analytics and statistics (Admin only).
 *
 * @since 1.0.0
 */
class BoostBot_Analytics_Endpoint extends BoostBot_Base_Endpoint
{
    /**
     * The base path for this endpoint.
     *
     * @since 1.0.0
     * @access protected
     * @var string $rest_base
     */
    protected $rest_base = 'analytics';

    /**
     * Register the routes for this endpoint.
     *
     * @since 1.0.0
     */
    public function register()
    {
        $language_arg = [
            'language' => [
                'type'              => 'string',
                'sanitize_callback' => 'sanitize_text_field',
            ],
        ];

        // GET /wp-json/boostbot/v1/analytics - Dashboard overview
        $this->register_route(
            '',
            'GET',
            [$this, 'get_dashboard'],
            [$this, 'admin_access'],
            $language_arg
        );

        // GET /wp-json/boostbot/v1/analytics/trends - Trends over time
        $this->register_route(
            '/trends',
            'GET',
            [$this, 'get_trends'],
            [$this, 'admin_access'],
            $language_arg
        );

        // GET /wp-json/boostbot/v1/analytics/usage - Usage statistics
        $this->register_route(
            '/usage',
            'GET',
            [$this, 'get_usage'],
            [$this, 'admin_access']
        );

        // GET /wp-json/boostbot/v1/analytics/sources - Top converting pages
        $this->register_route(
            '/sources',
            'GET',
            [$this, 'get_sources'],
            [$this, 'admin_access'],
            $language_arg
        );

        // GET /wp-json/boostbot/v1/analytics/widgets - Widget performance
        $this->register_route(
            '/widgets',
            'GET',
            [$this, 'get_widget_analytics'],
            [$this, 'admin_access'],
            $language_arg
        );

        // GET /wp-json/boostbot/v1/analytics/funnel - Conversion funnel
        $this->register_route(
            '/funnel',
            'GET',
            [$this, 'get_funnel'],
            [$this, 'admin_access'],
            $language_arg
        );

        // GET /wp-json/boostbot/v1/analytics/bots - Bot performance
        $this->register_route(
            '/bots',
            'GET',
            [$this, 'get_bot_analytics'],
            [$this, 'admin_access'],
            $language_arg
        );

        register_rest_route(
            $this->namespace,
            '/commerce-events',
            array(
                'methods'             => 'POST',
                'callback'            => array( $this, 'ingest_commerce_events' ),
                'permission_callback' => array( $this, 'public_with_api_rate_limit' ),
            )
        );

        register_rest_route(
            $this->namespace,
            '/commerce-analytics',
            array(
                'methods'             => 'GET',
                'callback'            => array( $this, 'get_commerce_analytics' ),
                'permission_callback' => array( $this, 'admin_access' ),
                'args'                => array(
                    'period'   => array(
                        'type'              => 'string',
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                    'bot_id'   => array(
                        'type'              => 'integer',
                        'sanitize_callback' => 'absint',
                    ),
                    'language' => array(
                        'type'              => 'string',
                        'sanitize_callback' => 'sanitize_text_field',
                    ),
                ),
            )
        );
    }

    /**
     * Get dashboard overview.
     *
     * @since 1.0.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response The response.
     */
    public function get_dashboard($request)
    {
        BoostBot_Logger::debug('Getting analytics dashboard');

        $language = BoostBot_Language::sanitize( $request->get_param('language') );

        // Check cache first (5 minute TTL)
        $cache_key = 'boostbot_dashboard_stats' . ($language ? '_lang_' . $language : '');
        $cached = get_transient($cache_key);
        if ($cached !== false) {
            return $this->success($cached);
        }

        global $wpdb;

        // Language filter fragments
        $leads_lang_filter = '';
        $conv_lang_filter = '';
        $meetings_lang_filter = '';
        if ($language) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $conv_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $meetings_lang_filter = $wpdb->prepare(' AND language = %s', $language);
        }

        // Leads stats
        $leads_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_leads' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$leads_table} WHERE 1=1{$leads_lang_filter}");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_today = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$leads_table} WHERE DATE(created_at) = CURDATE(){$leads_lang_filter}"
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_this_week = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$leads_table} WHERE YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1){$leads_lang_filter}"
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_this_month = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$leads_table} 
             WHERE MONTH(created_at) = MONTH(CURDATE()) 
             AND YEAR(created_at) = YEAR(CURDATE()){$leads_lang_filter}"
        );

        // Leads by status distribution
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_by_status_raw = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT lead_status, COUNT(*) as count FROM {$leads_table} WHERE 1=1{$leads_lang_filter} GROUP BY lead_status",
            ARRAY_A
        );
        $leads_by_status = [
            'new'       => 0,
            'contacted' => 0,
            'qualified' => 0,
            'converted' => 0,
            'lost'      => 0,
        ];
        foreach ($leads_by_status_raw as $row) {
            $status = $row['lead_status'] ?? 'new';
            if (isset($leads_by_status[$status])) {
                $leads_by_status[$status] = (int) $row['count'];
            } else {
                $leads_by_status['new'] += (int) $row['count'];
            }
        }

        // Conversations stats
        $conv_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_conversations' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $conv_total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$conv_table} WHERE 1=1{$conv_lang_filter}");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $conv_today = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$conv_table} WHERE DATE(started_at) = CURDATE(){$conv_lang_filter}"
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $conv_this_week = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$conv_table} WHERE YEARWEEK(started_at, 1) = YEARWEEK(CURDATE(), 1){$conv_lang_filter}"
        );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $conv_this_month = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$conv_table} 
             WHERE MONTH(started_at) = MONTH(CURDATE()) 
             AND YEAR(started_at) = YEAR(CURDATE()){$conv_lang_filter}"
        );

        // Messages stats
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $total_messages = (int) $wpdb->get_var("SELECT SUM(total_messages) FROM {$conv_table} WHERE 1=1{$conv_lang_filter}");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $avg_messages = (float) $wpdb->get_var("SELECT AVG(total_messages) FROM {$conv_table} WHERE 1=1{$conv_lang_filter}");

        // Conversion stats
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $converted = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$conv_table} WHERE conversion_achieved = 1{$conv_lang_filter}"
        );
        $conversion_rate = $conv_total > 0 ? round(($converted / $conv_total) * 100, 1) : 0;

        // Satisfaction
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $avg_satisfaction = (float) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT AVG(satisfaction_score) FROM {$conv_table} WHERE satisfaction_score IS NOT NULL{$conv_lang_filter}"
        );

        // Meetings stats
        $meetings_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $meetings_total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$meetings_table} WHERE 1=1{$meetings_lang_filter}");
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $meetings_this_month = (int) $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$meetings_table} 
             WHERE MONTH(created_at) = MONTH(CURDATE()) 
             AND YEAR(created_at) = YEAR(CURDATE()){$meetings_lang_filter}"
        );

        // Usage (monthly cap removed; keep stats only)
        $usage = [];
        if (class_exists('BoostBot_Usage_Tracker')) {
            $usage = BoostBot_Usage_Tracker::get_usage();
            $usage['limit'] = -1;
            $usage['percentage'] = 0;
            $usage['remaining'] = null;
        }

        $dashboard = [
            'leads'         => [
                'total'      => $leads_total,
                'today'      => $leads_today,
                'this_week'  => $leads_this_week,
                'this_month' => $leads_this_month,
                'by_status'  => $leads_by_status,
            ],
            'conversations' => [
                'total'           => $conv_total,
                'today'           => $conv_today,
                'this_week'       => $conv_this_week,
                'this_month'      => $conv_this_month,
                'avg_messages'    => round($avg_messages, 1),
                'total_messages'  => $total_messages,
            ],
            'conversions'   => [
                'total' => $converted,
                'rate'  => $conversion_rate,
            ],
            'satisfaction'  => [
                'average' => round($avg_satisfaction, 1),
            ],
            'meetings'      => [
                'total'      => $meetings_total,
                'this_month' => $meetings_this_month,
            ],
            'usage'         => $usage,
        ];

        // Cache for 5 minutes
        set_transient($cache_key, $dashboard, 5 * MINUTE_IN_SECONDS);

        return $this->success($dashboard);
    }

    /**
     * Get trends over time.
     *
     * @since 1.0.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response The response.
     */
    public function get_trends($request)
    {
        BoostBot_Logger::debug('Getting analytics trends');

        $period = sanitize_text_field($request->get_param('period')) ?: '7days';
        $language = BoostBot_Language::sanitize( $request->get_param('language') );

        // Determine date range
        switch ($period) {
            case '30days':
                $days = 30;
                break;
            case '90days':
                $days = 90;
                break;
            case '7days':
            default:
                $days = 7;
        }

        // Check cache first (5 minute TTL)
        $cache_key = 'boostbot_trends_' . $period . ($language ? '_lang_' . $language : '');
        $cached = get_transient($cache_key);
        if ($cached !== false) {
            return $this->success($cached);
        }

        global $wpdb;

        // Language filter fragments
        $leads_lang_filter = '';
        $conv_lang_filter = '';
        $meetings_lang_filter = '';
        if ($language) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $conv_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $meetings_lang_filter = $wpdb->prepare(' AND language = %s', $language);
        }

        // Leads trend
        $leads_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_leads' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_trend = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT DATE(created_at) as date, COUNT(*) as count
                 FROM {$leads_table}
                 WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$leads_lang_filter}
                 GROUP BY DATE(created_at)
                 ORDER BY date ASC",
                $days
            ),
            ARRAY_A
        );

        // Conversations trend
        $conv_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_conversations' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $conversations_trend = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT DATE(started_at) as date, COUNT(*) as count
                 FROM {$conv_table}
                 WHERE started_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$conv_lang_filter}
                 GROUP BY DATE(started_at)
                 ORDER BY date ASC",
                $days
            ),
            ARRAY_A
        );

        // Messages trend
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $messages_trend = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT DATE(started_at) as date, SUM(total_messages) as count
                 FROM {$conv_table}
                 WHERE started_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$conv_lang_filter}
                 GROUP BY DATE(started_at)
                 ORDER BY date ASC",
                $days
            ),
            ARRAY_A
        );

        // Meetings trend
        $meetings_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $meetings_trend = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT DATE(created_at) as date, COUNT(*) as count
                 FROM {$meetings_table}
                 WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$meetings_lang_filter}
                 GROUP BY DATE(created_at)
                 ORDER BY date ASC",
                $days
            ),
            ARRAY_A
        );

        // Fill in missing dates with zeros
        $leads_filled = $this->fill_date_gaps($leads_trend, $days);
        $conversations_filled = $this->fill_date_gaps($conversations_trend, $days);
        $messages_filled = $this->fill_date_gaps($messages_trend, $days);
        $meetings_filled = $this->fill_date_gaps($meetings_trend, $days);

        // Merge into single trends array (format frontend expects)
        $trends = [];
        foreach ($leads_filled as $i => $lead_row) {
            $trends[] = [
                'date'          => $lead_row['date'],
                'leads'         => $lead_row['count'],
                'conversations' => $conversations_filled[$i]['count'] ?? 0,
                'messages'      => $messages_filled[$i]['count'] ?? 0,
                'meetings'      => $meetings_filled[$i]['count'] ?? 0,
            ];
        }

        $result = [
            'period' => $period,
            'trends' => $trends,
            'leads'         => $leads_filled,
            'conversations' => $conversations_filled,
            'messages'      => $messages_filled,
            'meetings'      => $meetings_filled,
        ];

        // Cache for 5 minutes
        set_transient($cache_key, $result, 5 * MINUTE_IN_SECONDS);

        return $this->success($result);
    }

    /**
     * Get usage statistics.
     *
     * @since 1.0.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response The response.
     */
    public function get_usage($request)
    {
        BoostBot_Logger::debug('Getting usage stats');

        $usage = [];

        if (class_exists('BoostBot_Usage_Tracker')) {
            $usage = BoostBot_Usage_Tracker::get_usage();
            $usage['percentage'] = BoostBot_Usage_Tracker::get_percentage();
            $usage['remaining'] = BoostBot_Usage_Tracker::get_remaining();
            $usage['limit'] = -1;
        }

        // License info (use is_pro() to include Dev Mode)
        $license_status = 'free';
        $is_pro = false;
        if (class_exists('BoostBot_License_Manager')) {
            $license_manager = new BoostBot_License_Manager();
            $license_status = $license_manager->get_license_status();
            $is_pro = $license_manager->is_pro();
        }

        $usage['license_status'] = $license_status;
        $usage['is_pro'] = $is_pro;

        return $this->success($usage);
    }

    /**
     * Fill date gaps in trend data.
     *
     * @since 1.0.0
     * @param array $data Trend data.
     * @param int   $days Number of days.
     * @return array Filled trend data.
     */
    private function fill_date_gaps($data, $days)
    {
        $filled = [];
        $data_by_date = [];

        // Index data by date
        foreach ($data as $row) {
            $data_by_date[$row['date']] = (int) $row['count'];
        }

        // NOTE: gmdate() returns UTC dates while SQL CURDATE() uses MySQL server timezone.
        // In non-UTC timezones this can cause ≤1 day boundary mismatch — accepted as low-risk.
        for ($i = $days - 1; $i >= 0; $i--) {
            $date = gmdate('Y-m-d', strtotime("-{$i} days"));
            $filled[] = [
                'date'  => $date,
                'count' => $data_by_date[$date] ?? 0,
            ];
        }

        return $filled;
    }

    /**
     * Get source analytics - which pages generate leads/conversations.
     *
     * @since 1.8.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response The response.
     */
    public function get_sources($request)
    {
        BoostBot_Logger::debug('Getting source analytics');

        $language = BoostBot_Language::sanitize( $request->get_param('language') );

        // Check cache first (5 minute TTL)
        $cache_key = 'boostbot_analytics_sources' . ($language ? '_lang_' . $language : '');
        $cached = get_transient($cache_key);
        if ($cached !== false) {
            return $this->success($cached);
        }

        global $wpdb;
        $leads_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_leads' );
        $conversations_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_conversations' );

        // Language filter fragments
        $leads_lang_filter = '';
        $conv_lang_filter = '';
        if ($language) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $conv_lang_filter = $wpdb->prepare(' AND language = %s', $language);
        }

        // Top pages by leads (from source_page column)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $lead_sources = $wpdb->get_results(
            "SELECT 
                source_page as page_url,
                COUNT(*) as lead_count
             FROM {$leads_table}
             WHERE source_page IS NOT NULL AND source_page != ''{$leads_lang_filter}
             GROUP BY source_page
             ORDER BY lead_count DESC
             LIMIT 10",
            ARRAY_A
        );

        // Top pages by conversations (from context_data JSON)
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $conversation_sources = $wpdb->get_results(
            "SELECT 
                JSON_UNQUOTE(JSON_EXTRACT(context_data, '$.page_url')) as page_url,
                COUNT(*) as conversation_count
             FROM {$conversations_table}
             WHERE context_data IS NOT NULL 
               AND context_data != '[]'
               AND JSON_EXTRACT(context_data, '$.page_url') IS NOT NULL{$conv_lang_filter}
             GROUP BY page_url
             ORDER BY conversation_count DESC
             LIMIT 10",
            ARRAY_A
        );

        // Clean up null values
        $conversation_sources = array_filter($conversation_sources, function($row) {
            return !empty($row['page_url']);
        });

        $result = [
            'lead_sources'         => array_values($lead_sources ?: []),
            'conversation_sources' => array_values($conversation_sources ?: []),
        ];

        // Cache for 5 minutes
        set_transient($cache_key, $result, 5 * MINUTE_IN_SECONDS);

        return $this->success($result);
    }

    /**
     * Get widget analytics - which widgets generate leads/meetings.
     *
     * @since 1.8.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response The response.
     */
    public function get_widget_analytics($request)
    {
        BoostBot_Logger::debug('Getting widget analytics');

        $bot_id = absint($request->get_param('bot_id'));
        $language = BoostBot_Language::sanitize( $request->get_param('language') );

        // Check cache first (5 minute TTL)
        $cache_key = 'boostbot_analytics_widgets' . ($bot_id ? '_bot' . $bot_id : '') . ($language ? '_lang_' . $language : '');
        $cached = get_transient($cache_key);
        if ($cached !== false) {
            return $this->success($cached);
        }

        global $wpdb;
        $leads_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_leads' );
        $meetings_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );
        $widgets_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_widgets' );
        $widget_usage_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_widget_usage' );

        // Bot filter for leads
        $leads_bot_filter = '';
        if ($bot_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_bot_filter = $wpdb->prepare(
                " AND CAST(JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.bot_id')) AS UNSIGNED) = %d",
                $bot_id
            );
        }

        // Bot filter for meetings
        $meetings_bot_filter = '';
        if ($bot_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $meetings_bot_filter = $wpdb->prepare(" AND m.chatbot_id = %d", $bot_id);
        }

        // Language filter fragments
        $leads_lang_filter = '';
        $meetings_lang_filter = '';
        $usage_lang_filter = '';
        $widgets_lang_filter = '';
        if ($language) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $meetings_lang_filter = $wpdb->prepare(' AND m.language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $usage_lang_filter = $wpdb->prepare(' AND wu.language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $widgets_lang_filter = $wpdb->prepare(' AND w.language = %s', $language);
        }

        // Leads by widget (from metadata JSON)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_by_widget = $wpdb->get_results(
            "SELECT 
                CAST(JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.widget_id')) AS UNSIGNED) as widget_id,
                JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.widget_type')) as widget_type,
                COUNT(*) as lead_count
             FROM {$leads_table}
             WHERE metadata IS NOT NULL 
               AND JSON_EXTRACT(metadata, '$.widget_id') IS NOT NULL
               AND JSON_EXTRACT(metadata, '$.widget_id') != 'null'
               AND JSON_EXTRACT(metadata, '$.widget_id') != '0'
               {$leads_bot_filter}{$leads_lang_filter}
             GROUP BY widget_id, widget_type
             ORDER BY lead_count DESC
             LIMIT 10",
            ARRAY_A
        );

        // Enrich with widget names
        foreach ($leads_by_widget as &$row) {
            $widget_id = (int) $row['widget_id'];
            if ($widget_id > 0) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
                $widget = $wpdb->get_row($wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    "SELECT name FROM {$widgets_table} WHERE id = %d",
                    $widget_id
                ));
                $row['widget_name'] = $widget ? $widget->name : __('Unknown Widget', 'boostbot');
            } else {
                $row['widget_name'] = __('Unknown Widget', 'boostbot');
            }
        }

        // Meetings by widget
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $meetings_by_widget = $wpdb->get_results(
            "SELECT 
                m.widget_id,
                w.name as widget_name,
                COUNT(*) as meeting_count
             FROM {$meetings_table} m
             LEFT JOIN {$widgets_table} w ON m.widget_id = w.id
             WHERE m.widget_id > 0
             {$meetings_bot_filter}{$meetings_lang_filter}
             GROUP BY m.widget_id
             ORDER BY meeting_count DESC
             LIMIT 10",
            ARRAY_A
        );

        // General widget usage (from widget_usage tracking table)
        $usage_bot_filter = '';
        if ($bot_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $usage_bot_filter = $wpdb->prepare(" AND wu.bot_id = %d", $bot_id);
        }
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $widget_usage = $wpdb->get_results(
            "SELECT 
                wu.widget_id,
                w.name as widget_name,
                w.type as widget_type,
                SUM(wu.usage_count) as total_uses
             FROM {$widget_usage_table} wu
             LEFT JOIN {$widgets_table} w ON wu.widget_id = w.id
             WHERE 1=1 {$usage_bot_filter}{$usage_lang_filter}{$widgets_lang_filter}
             GROUP BY wu.widget_id
             ORDER BY total_uses DESC
             LIMIT 15",
            ARRAY_A
        );

        $result = [
            'leads_by_widget'    => array_values($leads_by_widget ?: []),
            'meetings_by_widget' => array_values($meetings_by_widget ?: []),
            'widget_usage'       => array_values($widget_usage ?: []),
        ];

        // Cache for 5 minutes
        set_transient($cache_key, $result, 5 * MINUTE_IN_SECONDS);

        return $this->success($result);
    }

    /**
     * Get conversion funnel analytics.
     *
     * @since 1.8.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response The response.
     */
    public function get_funnel($request)
    {
        BoostBot_Logger::debug('Getting funnel analytics');

        $period = sanitize_text_field($request->get_param('period')) ?: '30days';
        $bot_id = absint($request->get_param('bot_id'));
        $language = BoostBot_Language::sanitize( $request->get_param('language') );
        $days = $this->period_to_days($period);

        // Check cache first (5 minute TTL)
        $cache_key = 'boostbot_analytics_funnel_' . $period . ($bot_id ? '_bot' . $bot_id : '') . ($language ? '_lang_' . $language : '');
        $cached = get_transient($cache_key);
        if ($cached !== false) {
            return $this->success($cached);
        }

        global $wpdb;
        $conversations_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_conversations' );
        $leads_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_leads' );
        $meetings_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );

        // Build bot filter for leads (bot_id stored in metadata JSON)
        $leads_bot_filter = '';
        if ($bot_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_bot_filter = $wpdb->prepare(
                " AND CAST(JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.bot_id')) AS UNSIGNED) = %d",
                $bot_id
            );
        }

        // Build bot filter for conversations (bot_id stored in context_data JSON)
        $conv_bot_filter = '';
        if ($bot_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $conv_bot_filter = $wpdb->prepare(
                " AND CAST(JSON_UNQUOTE(JSON_EXTRACT(context_data, '$.bot_id')) AS UNSIGNED) = %d",
                $bot_id
            );
        }

        // Build bot filter for meetings (direct chatbot_id column)
        $meetings_bot_filter = '';
        if ($bot_id > 0) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $meetings_bot_filter = $wpdb->prepare(" AND chatbot_id = %d", $bot_id);
        }

        // Language filter fragments
        $leads_lang_filter = '';
        $conv_lang_filter = '';
        $meetings_lang_filter = '';
        if ($language) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $conv_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $meetings_lang_filter = $wpdb->prepare(' AND language = %s', $language);
        }

        // Total conversations in period
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $total_conversations = (int) $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$conversations_table} 
             WHERE started_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$conv_bot_filter}{$conv_lang_filter}",
            $days
        ));

        // Leads generated from conversations (via any contact widget)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $leads_count = (int) $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$leads_table}
             WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$leads_bot_filter}{$leads_lang_filter}",
            $days
        ));

        // Qualified leads
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $qualified_leads = (int) $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$leads_table}
             WHERE lead_status IN ('qualified', 'converted')
             AND created_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$leads_bot_filter}{$leads_lang_filter}",
            $days
        ));

        // Converted leads
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $converted_leads = (int) $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$leads_table}
             WHERE lead_status = 'converted'
             AND created_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$leads_bot_filter}{$leads_lang_filter}",
            $days
        ));

        // Meetings booked (parallel metric, not a funnel stage)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $meetings_booked = (int) $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$meetings_table}
             WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$meetings_bot_filter}{$meetings_lang_filter}",
            $days
        ));

        // Average satisfaction from survey submissions
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $avg_satisfaction = (float) $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT AVG(satisfaction_score) FROM {$conversations_table} 
             WHERE satisfaction_score IS NOT NULL
             AND started_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$conv_bot_filter}{$conv_lang_filter}",
            $days
        ));
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $survey_count = (int) $wpdb->get_var($wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SELECT COUNT(*) FROM {$conversations_table} 
             WHERE satisfaction_score IS NOT NULL
             AND started_at >= DATE_SUB(CURDATE(), INTERVAL %d DAY){$conv_bot_filter}{$conv_lang_filter}",
            $days
        ));

        $result = [
            'period' => $period,
            'bot_id' => $bot_id ?: null,
            'funnel' => [
                [
                    'stage' => 'conversations',
                    'label' => __('Conversations', 'boostbot'),
                    'label_he' => 'שיחות',
                    'count' => $total_conversations,
                ],
                [
                    'stage' => 'leads',
                    'label' => __('Leads', 'boostbot'),
                    'label_he' => 'לידים',
                    'count' => $leads_count,
                ],
                [
                    'stage' => 'qualified',
                    'label' => __('Qualified', 'boostbot'),
                    'label_he' => 'מתאימים',
                    'count' => $qualified_leads,
                ],
                [
                    'stage' => 'converted',
                    'label' => __('Converted', 'boostbot'),
                    'label_he' => 'הומרו',
                    'count' => $converted_leads,
                ],
            ],
            'meetings' => [
                'count' => $meetings_booked,
                'label' => __('Meetings Booked', 'boostbot'),
                'label_he' => 'פגישות שנקבעו',
            ],
            'satisfaction' => [
                'average' => round($avg_satisfaction, 1),
                'count' => $survey_count,
                'label' => __('Avg Rating', 'boostbot'),
                'label_he' => 'דירוג ממוצע',
            ],
        ];

        // Cache for 5 minutes
        set_transient($cache_key, $result, 5 * MINUTE_IN_SECONDS);

        return $this->success($result);
    }

    /**
     * Get bot analytics - which bots generate leads.
     *
     * @since 1.8.0
     * @param WP_REST_Request $request The REST request object.
     * @return WP_REST_Response The response.
     */
    public function get_bot_analytics($request)
    {
        BoostBot_Logger::debug('Getting bot analytics');

        $language = BoostBot_Language::sanitize( $request->get_param('language') );

        // Check cache first (5 minute TTL)
        $cache_key = 'boostbot_analytics_bots' . ($language ? '_lang_' . $language : '');
        $cached = get_transient($cache_key);
        if ($cached !== false) {
            return $this->success($cached);
        }

        global $wpdb;
        $leads_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_leads' );
        $bots_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bots' );
        $conversations_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_conversations' );
        $meetings_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );
        $widget_usage_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_widget_usage' );
        $widgets_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_widgets' );

        // Language filter fragments for data tables
        $leads_lang_filter = '';
        $conv_lang_filter = '';
        $meetings_lang_filter = '';
        $usage_lang_filter = '';
        if ($language) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $leads_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $conv_lang_filter = $wpdb->prepare(' AND c.language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $meetings_lang_filter = $wpdb->prepare(' AND language = %s', $language);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $usage_lang_filter = $wpdb->prepare(' AND wu.language = %s', $language);
        }

        // Get bots — filter by language if provided
        if ($language) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $all_bots = $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                $wpdb->prepare(
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    "SELECT id, bot_name FROM {$bots_table} WHERE language = %s ORDER BY id",
                    $language
                ),
                ARRAY_A
            );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $all_bots = $wpdb->get_results(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT id, bot_name FROM {$bots_table} ORDER BY id",
                ARRAY_A
            );
        }

        $bots_performance = [];

        foreach ($all_bots as $bot) {
            $bid = (int) $bot['id'];

            // Leads by this bot
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $lead_count = (int) $wpdb->get_var($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT COUNT(*) FROM {$leads_table}
                 WHERE metadata IS NOT NULL
                   AND CAST(JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.bot_id')) AS UNSIGNED) = %d{$leads_lang_filter}",
                $bid
            ));

            // Meetings by this bot
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $meeting_count = (int) $wpdb->get_var($wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SELECT COUNT(*) FROM {$meetings_table} WHERE chatbot_id = %d{$meetings_lang_filter}",
                $bid
            ));

            // Satisfaction rating from survey (via conversations)
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $avg_rating = (float) $wpdb->get_var($wpdb->prepare(
                "SELECT AVG(c.satisfaction_score) 
                 FROM {$conversations_table} c
                 WHERE c.satisfaction_score IS NOT NULL
                   AND c.context_data IS NOT NULL
                   AND CAST(JSON_UNQUOTE(JSON_EXTRACT(c.context_data, '$.bot_id')) AS UNSIGNED) = %d{$conv_lang_filter}",
                $bid
            ));
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $rating_count = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) 
                 FROM {$conversations_table} c
                 WHERE c.satisfaction_score IS NOT NULL
                   AND c.context_data IS NOT NULL
                   AND CAST(JSON_UNQUOTE(JSON_EXTRACT(c.context_data, '$.bot_id')) AS UNSIGNED) = %d{$conv_lang_filter}",
                $bid
            ));

            // Widget usage for this bot
            // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $widget_usage = $wpdb->get_results($wpdb->prepare(
                "SELECT wu.widget_id, w.name as widget_name, w.type as widget_type, wu.usage_count
                 FROM {$widget_usage_table} wu
                 LEFT JOIN {$widgets_table} w ON wu.widget_id = w.id
                 WHERE wu.bot_id = %d{$usage_lang_filter}
                 ORDER BY wu.usage_count DESC
                 LIMIT 10",
                $bid
            ), ARRAY_A);

            $bots_performance[] = [
                'bot_id'       => $bid,
                'bot_name'     => $bot['bot_name'] ?: __('Default Bot', 'boostbot'),
                'lead_count'   => $lead_count,
                'meeting_count' => $meeting_count,
                'avg_rating'   => round($avg_rating, 1),
                'rating_count' => $rating_count,
                'widget_usage' => array_values($widget_usage ?: []),
            ];
        }

        // Sort by lead count descending
        usort($bots_performance, function($a, $b) {
            return $b['lead_count'] - $a['lead_count'];
        });

        // Legacy field for backward compatibility
        $leads_by_bot = array_map(function($bot) {
            return [
                'bot_id'    => $bot['bot_id'],
                'bot_name'  => $bot['bot_name'],
                'lead_count' => $bot['lead_count'],
            ];
        }, $bots_performance);

        $result = [
            'leads_by_bot'    => array_values($leads_by_bot),
            'bots_performance' => array_values($bots_performance),
        ];

        // Cache for 5 minutes
        set_transient($cache_key, $result, 5 * MINUTE_IN_SECONDS);

        return $this->success($result);
    }

    /**
     * Store batched commerce telemetry events.
     *
     * @param WP_REST_Request $request REST request.
     * @return WP_REST_Response|WP_Error
     */
    public function ingest_commerce_events( $request ) {
        global $wpdb;

        $body   = $request->get_json_params();
        $events = isset( $body['events'] ) && is_array( $body['events'] ) ? array_slice( $body['events'], 0, 100 ) : array();
        if ( empty( $events ) ) {
            return $this->validation_error( array( 'events' => __( 'At least one commerce event is required.', 'boostbot' ) ) );
        }

        $table  = self::sanitize_identifier( $wpdb->prefix . 'boostbot_commerce_events' );
        $stored = 0;

        foreach ( $events as $raw_event ) {
            if ( ! is_array( $raw_event ) ) {
                continue;
            }

            $prepared = $this->prepare_commerce_event_row( $raw_event );
            if ( empty( $prepared ) ) {
                continue;
            }

            $inserted = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
                $table,
                $prepared['data'],
                $prepared['formats']
            );

            if ( false !== $inserted ) {
                ++$stored;
            }
        }

        return $this->success(
            array(
                'stored' => $stored,
            )
        );
    }

    /**
     * Get aggregated commerce analytics for the admin dashboard.
     *
     * @param WP_REST_Request $request REST request.
     * @return WP_REST_Response
     */
    public function get_commerce_analytics( $request ) {
        global $wpdb;

        $table      = self::sanitize_identifier( $wpdb->prefix . 'boostbot_commerce_events' );
        $bots_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bots' );
        $period     = sanitize_text_field( $request->get_param( 'period' ) ) ?: '30days';
        $days       = $this->period_to_days( $period );
        $language   = $this->get_commerce_request_language( $request );
        $bot_id     = absint( $request->get_param( 'bot_id' ) );

        $filters = array( 'occurred_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)' );
        $args    = array( $days );

        if ( $language ) {
            $filters[] = 'language = %s';
            $args[]    = $language;
        }

        if ( $bot_id > 0 ) {
            $filters[] = 'bot_id = %d';
            $args[]    = $bot_id;
        }

        $where_sql = 'WHERE ' . implode( ' AND ', $filters );

        $summary_sql = "
            SELECT
                SUM(CASE WHEN event_type = 'product_card_viewed' THEN 1 ELSE 0 END) AS product_views,
                SUM(CASE WHEN event_type IN ('product_added_to_cart', 'bundle_added_to_cart') THEN 1 ELSE 0 END) AS add_to_cart,
                SUM(CASE WHEN event_type = 'checkout_clicked' THEN 1 ELSE 0 END) AS checkout_clicks,
                SUM(CASE WHEN event_type = 'coupon_applied' THEN 1 ELSE 0 END) AS coupons_applied,
                SUM(CASE WHEN event_type = 'bundle_added_to_cart' THEN 1 ELSE 0 END) AS bundles_added
            FROM {$table}
            {$where_sql}
        ";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $summary_row = $wpdb->get_row( $wpdb->prepare( $summary_sql, $args ), ARRAY_A );

        $revenue_sql = "
            SELECT currency_code, SUM(COALESCE(order_value, cart_total, bundle_value, 0)) AS amount
            FROM {$table}
            {$where_sql}
              AND event_type = 'checkout_clicked'
              AND currency_code IS NOT NULL
              AND currency_code <> ''
            GROUP BY currency_code
            ORDER BY amount DESC
        ";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $revenue_rows = $wpdb->get_results( $wpdb->prepare( $revenue_sql, $args ), ARRAY_A );

        $daily_sql = "
            SELECT
                DATE(occurred_at) AS date,
                SUM(CASE WHEN event_type = 'product_card_viewed' THEN 1 ELSE 0 END) AS views,
                SUM(CASE WHEN event_type IN ('product_added_to_cart', 'bundle_added_to_cart') THEN 1 ELSE 0 END) AS add_to_cart,
                SUM(CASE WHEN event_type = 'checkout_clicked' THEN 1 ELSE 0 END) AS checkout_clicks,
                SUM(CASE WHEN event_type = 'coupon_applied' THEN 1 ELSE 0 END) AS coupons_applied,
                SUM(CASE WHEN event_type = 'bundle_added_to_cart' THEN 1 ELSE 0 END) AS bundles_added
            FROM {$table}
            {$where_sql}
            GROUP BY DATE(occurred_at)
            ORDER BY date ASC
        ";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $daily_rows = $wpdb->get_results( $wpdb->prepare( $daily_sql, $args ), ARRAY_A );

        $daily_views = array();
        $daily_add_to_cart = array();
        $daily_checkout = array();
        $daily_coupons = array();
        $daily_bundles = array();
        foreach ( $daily_rows as $row ) {
            $daily_views[] = array(
                'date'  => $row['date'],
                'count' => (int) $row['views'],
            );
            $daily_add_to_cart[] = array(
                'date'  => $row['date'],
                'count' => (int) $row['add_to_cart'],
            );
            $daily_checkout[] = array(
                'date'  => $row['date'],
                'count' => (int) $row['checkout_clicks'],
            );
            $daily_coupons[] = array(
                'date'  => $row['date'],
                'count' => (int) $row['coupons_applied'],
            );
            $daily_bundles[] = array(
                'date'  => $row['date'],
                'count' => (int) $row['bundles_added'],
            );
        }

        $filled_views      = $this->fill_date_gaps( $daily_views, $days );
        $filled_add_to_cart = $this->fill_date_gaps( $daily_add_to_cart, $days );
        $filled_checkout   = $this->fill_date_gaps( $daily_checkout, $days );
        $filled_coupons    = $this->fill_date_gaps( $daily_coupons, $days );
        $filled_bundles    = $this->fill_date_gaps( $daily_bundles, $days );

        $daily_trends = array();
        foreach ( $filled_views as $index => $row ) {
            $daily_trends[] = array(
                'date'            => $row['date'],
                'views'           => (int) $row['count'],
                'add_to_cart'     => (int) ( $filled_add_to_cart[ $index ]['count'] ?? 0 ),
                'checkout_clicks' => (int) ( $filled_checkout[ $index ]['count'] ?? 0 ),
                'coupons_applied' => (int) ( $filled_coupons[ $index ]['count'] ?? 0 ),
                'bundles_added'   => (int) ( $filled_bundles[ $index ]['count'] ?? 0 ),
            );
        }

        $by_language_sql = "
            SELECT
                language,
                COUNT(*) AS total_events,
                SUM(CASE WHEN event_type = 'product_card_viewed' THEN 1 ELSE 0 END) AS views,
                SUM(CASE WHEN event_type IN ('product_added_to_cart', 'bundle_added_to_cart') THEN 1 ELSE 0 END) AS add_to_cart,
                SUM(CASE WHEN event_type = 'checkout_clicked' THEN 1 ELSE 0 END) AS checkout_clicks
            FROM {$table}
            {$where_sql}
            GROUP BY language
            ORDER BY total_events DESC
        ";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $by_language = $wpdb->get_results( $wpdb->prepare( $by_language_sql, $args ), ARRAY_A );

        $by_bot_sql = "
            SELECT
                ce.bot_id,
                COALESCE(b.bot_name, %s) AS bot_name,
                COUNT(*) AS total_events,
                SUM(CASE WHEN ce.event_type = 'product_card_viewed' THEN 1 ELSE 0 END) AS views,
                SUM(CASE WHEN ce.event_type IN ('product_added_to_cart', 'bundle_added_to_cart') THEN 1 ELSE 0 END) AS add_to_cart,
                SUM(CASE WHEN ce.event_type = 'checkout_clicked' THEN 1 ELSE 0 END) AS checkout_clicks,
                SUM(COALESCE(ce.order_value, ce.cart_total, ce.bundle_value, 0)) AS tracked_value
            FROM {$table} ce
            LEFT JOIN {$bots_table} b ON ce.bot_id = b.id
            WHERE " . implode( ' AND ', $filters ) . "
            GROUP BY ce.bot_id, b.bot_name
            ORDER BY total_events DESC
            LIMIT 10
        ";
        $by_bot_args = array_merge( array( __( 'Unknown bot', 'boostbot' ) ), $args );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $by_bot = $wpdb->get_results( $wpdb->prepare( $by_bot_sql, $by_bot_args ), ARRAY_A );

        $top_products_sql = "
            SELECT
                product_id,
                MAX(product_name) AS product_name,
                SUM(CASE WHEN event_type = 'product_card_viewed' THEN 1 ELSE 0 END) AS views,
                SUM(CASE WHEN event_type = 'product_added_to_cart' THEN 1 ELSE 0 END) AS add_to_cart,
                SUM(CASE WHEN event_type = 'product_add_to_cart_failed' THEN 1 ELSE 0 END) AS failures
            FROM {$table}
            {$where_sql}
              AND product_id IS NOT NULL
            GROUP BY product_id
            ORDER BY add_to_cart DESC, views DESC
            LIMIT 10
        ";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $top_products = $wpdb->get_results( $wpdb->prepare( $top_products_sql, $args ), ARRAY_A );

        $top_coupons_sql = "
            SELECT
                coupon_code,
                SUM(CASE WHEN event_type = 'coupon_viewed' THEN 1 ELSE 0 END) AS views,
                SUM(CASE WHEN event_type = 'coupon_copied' THEN 1 ELSE 0 END) AS copies,
                SUM(CASE WHEN event_type = 'coupon_applied' THEN 1 ELSE 0 END) AS applies,
                SUM(CASE WHEN event_type = 'coupon_apply_failed' THEN 1 ELSE 0 END) AS failures
            FROM {$table}
            {$where_sql}
              AND coupon_code IS NOT NULL
              AND coupon_code <> ''
            GROUP BY coupon_code
            ORDER BY applies DESC, views DESC
            LIMIT 10
        ";
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $top_coupons = $wpdb->get_results( $wpdb->prepare( $top_coupons_sql, $args ), ARRAY_A );

        $summary = array(
            'views'           => (int) ( $summary_row['product_views'] ?? 0 ),
            'add_to_cart'     => (int) ( $summary_row['add_to_cart'] ?? 0 ),
            'checkout_clicks' => (int) ( $summary_row['checkout_clicks'] ?? 0 ),
            'coupons_applied' => (int) ( $summary_row['coupons_applied'] ?? 0 ),
            'bundles_added'   => (int) ( $summary_row['bundles_added'] ?? 0 ),
            'revenue_by_currency' => array_map(
                static function( $row ) {
                    return array(
                        'currency_code' => $row['currency_code'],
                        'amount'        => round( (float) $row['amount'], 2 ),
                    );
                },
                $revenue_rows ?: array()
            ),
        );

        $result = array(
            'period'       => $period,
            'bot_id'       => $bot_id ?: null,
            'language'     => $language ?: null,
            'summary'      => $summary,
            'funnel'       => array(
                array(
                    'stage' => 'views',
                    'label' => 'Views',
                    'count' => $summary['views'],
                ),
                array(
                    'stage' => 'add_to_cart',
                    'label' => 'Add to cart',
                    'count' => $summary['add_to_cart'],
                ),
                array(
                    'stage' => 'checkout_clicks',
                    'label' => 'Checkout clicks',
                    'count' => $summary['checkout_clicks'],
                ),
            ),
            'daily_trends' => $daily_trends,
            'by_language'  => array_values( $by_language ?: array() ),
            'by_bot'       => array_values( $by_bot ?: array() ),
            'top_products' => array_values( $top_products ?: array() ),
            'top_coupons'  => array_values( $top_coupons ?: array() ),
        );

        return $this->success( $result );
    }

    /**
     * Prepare a sanitized commerce event row for DB insertion.
     *
     * @param array $event Raw event payload.
     * @return array<string, mixed>
     */
    private function prepare_commerce_event_row( array $event ) {
        $allowed_types = array(
            'product_card_viewed',
            'product_viewed',
            'product_added_to_cart',
            'product_add_to_cart_failed',
            'cart_opened',
            'cart_item_quantity_updated',
            'cart_item_removed',
            'checkout_clicked',
            'coupon_viewed',
            'coupon_copied',
            'coupon_applied',
            'coupon_apply_failed',
            'coupon_removed',
            'bundle_built',
            'bundle_item_swapped',
            'bundle_added_to_cart',
            'bundle_add_to_cart_failed',
        );

        $event_type = sanitize_key( $event['event_type'] ?? '' );
        if ( ! in_array( $event_type, $allowed_types, true ) ) {
            return array();
        }

        $metadata = array();
        if ( isset( $event['metadata'] ) && is_array( $event['metadata'] ) ) {
            $metadata = $event['metadata'];
        }

        $data = array(
            'event_type'     => $event_type,
            'occurred_at'    => $this->normalize_commerce_timestamp( $event['timestamp'] ?? '' ),
            'conversation_id'=> $this->sanitize_nullable_text( $event['conversation_id'] ?? null, 191 ),
            'visitor_id'     => $this->sanitize_nullable_text( $event['visitor_id'] ?? null, 191 ),
            'bot_id'         => $this->sanitize_nullable_int( $event['bot_id'] ?? null ),
            'language'       => $this->sanitize_nullable_text( $event['language'] ?? null, 10 ) ?: 'he',
            'currency_code'  => $this->sanitize_nullable_text( $event['currency_code'] ?? null, 10 ),
            'widget_type'    => $this->sanitize_nullable_text( $event['widget_type'] ?? null, 50 ),
            'widget_id'      => $this->sanitize_nullable_int( $event['widget_id'] ?? null ),
            'source_page'    => $this->sanitize_nullable_text( $event['source_page'] ?? null, 500 ),
            'product_id'     => $this->sanitize_nullable_int( $event['product_id'] ?? null ),
            'product_name'   => $this->sanitize_nullable_text( $event['product_name'] ?? null, 255 ),
            'variation_id'   => $this->sanitize_nullable_int( $event['variation_id'] ?? null ),
            'quantity'       => $this->sanitize_nullable_int( $event['quantity'] ?? null ),
            'cart_item_key'  => $this->sanitize_nullable_text( $event['cart_item_key'] ?? null, 191 ),
            'cart_item_count'=> $this->sanitize_nullable_int( $event['cart_item_count'] ?? null ),
            'cart_subtotal'  => $this->sanitize_nullable_decimal( $event['cart_subtotal'] ?? null ),
            'cart_total'     => $this->sanitize_nullable_decimal( $event['cart_total'] ?? null ),
            'discount_total' => $this->sanitize_nullable_decimal( $event['discount_total'] ?? null ),
            'coupon_code'    => $this->sanitize_nullable_text( $event['coupon_code'] ?? null, 100 ),
            'bundle_id'      => $this->sanitize_nullable_int( $event['bundle_id'] ?? null ),
            'bundle_size'    => $this->sanitize_nullable_int( $event['bundle_size'] ?? null ),
            'bundle_value'   => $this->sanitize_nullable_decimal( $event['bundle_value'] ?? null ),
            'order_value'    => $this->sanitize_nullable_decimal( $event['order_value'] ?? null ),
            'metadata'       => ! empty( $metadata ) ? wp_json_encode( $metadata ) : null,
        );

        return array(
            'data'    => $data,
            'formats' => array(
                '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%d', '%s',
                '%d', '%s', '%d', '%d', '%s', '%d', '%f', '%f', '%f', '%s',
                '%d', '%d', '%f', '%f', '%s',
            ),
        );
    }

    /**
     * Normalize a telemetry timestamp to MySQL UTC format.
     *
     * @param mixed $timestamp Raw timestamp.
     * @return string
     */
    private function normalize_commerce_timestamp( $timestamp ) {
        if ( is_string( $timestamp ) && ! empty( $timestamp ) ) {
            try {
                $date = new DateTimeImmutable( $timestamp );
                return $date->setTimezone( new DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' );
            } catch ( \Throwable $e ) {
                // Fall through to current time below.
            }
        }

        return current_time( 'mysql', true );
    }

    /**
     * Resolve analytics language; treat Polylang's "all" as no filter.
     *
     * @param WP_REST_Request $request REST request.
     * @return string
     */
    private function get_commerce_request_language( $request ) {
        $raw = sanitize_text_field( $request->get_param( 'language' ) );
        if ( empty( $raw ) || 'all' === strtolower( $raw ) ) {
            return '';
        }

        return BoostBot_Language::sanitize( $raw );
    }

    /**
     * Sanitize nullable string values.
     *
     * @param mixed $value Raw value.
     * @param int   $max_length Max length.
     * @return string|null
     */
    private function sanitize_nullable_text( $value, $max_length = 255 ) {
        if ( null === $value || '' === $value ) {
            return null;
        }

        $sanitized = sanitize_text_field( (string) $value );
        if ( '' === $sanitized ) {
            return null;
        }

        return substr( $sanitized, 0, $max_length );
    }

    /**
     * Sanitize nullable integer values.
     *
     * @param mixed $value Raw value.
     * @return int|null
     */
    private function sanitize_nullable_int( $value ) {
        if ( null === $value || '' === $value ) {
            return null;
        }

        return is_numeric( $value ) ? (int) $value : null;
    }

    /**
     * Sanitize nullable decimal values.
     *
     * @param mixed $value Raw value.
     * @return float|null
     */
    private function sanitize_nullable_decimal( $value ) {
        if ( null === $value || '' === $value ) {
            return null;
        }

        return is_numeric( $value ) ? (float) $value : null;
    }

    /**
     * Convert period string to days.
     *
     * @since 1.8.0
     * @param string $period Period string (7days, 30days, 90days).
     * @return int Number of days.
     */
    private function period_to_days($period)
    {
        switch ($period) {
            case '90days':
                return 90;
            case '30days':
                return 30;
            case '7days':
            default:
                return 7;
        }
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}



