<?php
/**
 * AJAX Handlers Trait
 *
 * Groups AJAX handlers for dashboard and analytics functionality.
 *
 * @package Opti_Behavior
 * @version 1.0.3
 */

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

/**
 * AJAX Handlers Trait
 *
 * Provides AJAX handler methods for dashboard operations.
 *
 * @since 1.0.0
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
if ( ! trait_exists( 'opti_behavior_Ajax_Handlers_Trait' ) ) {
	trait Opti_Behavior_Ajax_Handlers_Trait {
		/**
		 * AJAX handler for heatmaps table.
		 *
		 * @since 1.0.0
		 * @global wpdb $wpdb WordPress database abstraction object.
		 */
		private function ajax_heatmaps_table_impl() {
            check_ajax_referer( 'opti_behavior_dashboard_nonce', 'nonce' );
            if ( ! current_user_can( 'manage_options' ) ) {
                wp_die( esc_html__( 'Unauthorized', 'opti-behavior' ) );
            }
            $orderby = isset($_POST['orderby']) ? sanitize_text_field( wp_unslash( $_POST['orderby'] ) ) : 'last_updated';
            $order   = isset($_POST['order']) ? strtolower( sanitize_text_field( wp_unslash( $_POST['order'] ) ) ) : 'desc';
            $page    = isset($_POST['paged']) ? max(1, intval( wp_unslash( $_POST['paged'] ) )) : 1;
            $per_page = isset($_POST['per_page']) ? max(1, intval( wp_unslash( $_POST['per_page'] ) )) : 5;
            $period  = isset($_POST['period']) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : 'last30days';
            $start   = isset($_POST['start_date']) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : '';
            $end     = isset($_POST['end_date']) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : '';
            $search  = isset($_POST['search']) ? sanitize_text_field( wp_unslash( $_POST['search'] ) ) : '';

            // Debug logging
            $debug_manager = $this->heatmap->get_debug_manager();
            $debug_manager->log( 'ajax_heatmaps_table params - orderby=' . $orderby . ' order=' . $order . ' page=' . $page . ' per_page=' . $per_page . ' period=' . $period, 'debug', 'ajax' );

            // Debug: bypass transient cache to ensure fresh HTML every request
            ob_start();
            $this->render_heatmap_table_inner( compact('orderby','order','page','per_page','period','start','end','search') );
            $html = ob_get_clean();

            // Debug: log HTML length and check for data
            $debug_manager->log( 'Generated HTML length: ' . strlen( $html ), 'debug', 'ajax' );
            $debug_manager->log( 'HTML contains "No heatmaps available": ' . ( strpos( $html, 'No heatmaps available' ) !== false ? 'YES' : 'NO' ), 'debug', 'ajax' );

            wp_send_json_success( array( 'html' => $html ) );
        }

        /**
         * AJAX handler for heatmaps sessions.
         *
         * @since 1.0.0
         * @global wpdb $wpdb WordPress database abstraction object.
         */
        private function ajax_heatmaps_sessions_impl() {
            check_ajax_referer( 'opti_behavior_dashboard_nonce', 'nonce' );
            if ( ! current_user_can( 'manage_options' ) ) {
                wp_die( esc_html__( 'Unauthorized', 'opti-behavior' ) );
            }
            // Unslash first, then sanitize with intval
            $pageIds = isset($_POST['page_ids']) && is_array($_POST['page_ids']) ? array_map('intval', wp_unslash( $_POST['page_ids'] ) ) : array();
            $period  = isset($_POST['period']) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : 'last30days';
            $start   = isset($_POST['start_date']) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : '';
            $end     = isset($_POST['end_date']) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : '';
            if ($period === 'custom' && $start && $end) { $start_date = $start.' 00:00:00'; $end_date = $end.' 23:59:59'; }
            else { $range = $this->get_date_range($period); $start_date = $range['start']; $end_date = $range['end']; }
            $debug_manager = $this->heatmap->get_debug_manager();
            $debug_manager->log( 'ajax_heatmaps_sessions ids=' . wp_json_encode( $pageIds ) . ' period=' . $period . ' start=' . $start_date . ' end=' . $end_date, 'debug', 'ajax' );
            $map = $this->batch_sessions_counts_by_page($pageIds, $start_date, $end_date);
            $debug_manager->log( 'ajax_heatmaps_sessions result=' . wp_json_encode( $map ), 'debug', 'ajax' );
            wp_send_json_success(array('sessions'=>$map));
        }

        /**
         * AJAX handler for dashboard data.
         *
         * Supports async widget loading via 'widget' parameter.
         * When widget is specified, returns only that widget's data.
         * When widget is not specified, returns minimal data (stats only).
         *
         * @since 1.0.0
         */
        private function ajax_dashboard_data_impl() {
            check_ajax_referer( 'opti_behavior_dashboard_nonce', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_die( esc_html__( 'Unauthorized', 'opti-behavior' ) );
            }

            $period = isset( $_POST['period'] ) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : 'last30days';
            $start = isset($_POST['start_date']) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : null;
            $end   = isset($_POST['end_date']) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : null;
            $widget = isset($_POST['widget']) ? sanitize_text_field( wp_unslash( $_POST['widget'] ) ) : '';

            // Get exclude_spam parameter - store globally for use in queries
            $exclude_spam = isset( $_POST['exclude_spam'] ) && sanitize_text_field( wp_unslash( $_POST['exclude_spam'] ) ) === '1';
            $GLOBALS['opti_behavior_exclude_spam'] = $exclude_spam;

            // If widget is specified, return only that widget's data
            if ( ! empty( $widget ) ) {
                $data = $this->get_widget_data( $widget, $period, $start, $end );
                wp_send_json_success( $data );
                return;
            }

            // Otherwise return full dashboard data (for backward compatibility)
            $data = $this->get_dashboard_data( $period, $start, $end );

            wp_send_json_success( $data );
        }

        /**
         * Get data for a specific widget.
         *
         * @since 1.0.0
         * @param string      $widget Widget identifier.
         * @param string      $period Period identifier.
         * @param string|null $start  Start date override.
         * @param string|null $end    End date override.
         * @return array Widget data.
         */
        private function get_widget_data( $widget, $period, $start = null, $end = null ) {
            if ( 'custom' === $period && $start && $end ) {
                $start_date = sanitize_text_field( $start ) . ' 00:00:00';
                $end_date   = sanitize_text_field( $end ) . ' 23:59:59';
            } else {
                $date_range = $this->get_date_range( $period );
                $start_date = $date_range['start'];
                $end_date   = $date_range['end'];
            }

            switch ( $widget ) {
                case 'sessions_chart':
                    return array( 'sessions_chart' => $this->get_sessions_chart_data( $start_date, $end_date ) );

                case 'browsers':
                    return array( 'browsers' => $this->get_browsers_data( $start_date, $end_date ) );

                case 'device_types':
                    return array( 'device_types' => $this->get_device_types_data( $start_date, $end_date ) );

                case 'operating_systems':
                    return array( 'operating_systems' => $this->get_operating_systems_data( $start_date, $end_date ) );

                case 'user_intent':
                    return array( 'user_intent' => method_exists( $this, 'get_user_intent_data' ) ? $this->get_user_intent_data( $start_date, $end_date ) : array() );

                case 'countries':
                    return array( 'countries' => $this->get_countries_data( $start_date, $end_date ) );

                case 'realtime':
                    return array(
                        'active_visitors' => $this->get_active_visitors(),
                        'recent_sessions' => $this->get_recent_sessions( 10 )
                    );

                case 'top_pages':
                    return array( 'top_pages' => $this->get_top_pages_data( $start_date, $end_date ) );

                case 'referrers':
                    return array( 'referrers' => $this->get_referrers_data( $start_date, $end_date ) );

                case 'screen_resolutions':
                    return array( 'screen_resolutions' => $this->get_screen_resolution_data( $start_date, $end_date ) );

                case 'traffic_classification':
                    return array( 'traffic_classification' => method_exists( $this, 'get_traffic_classification_data_impl' ) ? $this->get_traffic_classification_data_impl( $start_date, $end_date ) : array() );

                case 'bot_traffic':
                    return array( 'bot_traffic' => method_exists( $this, 'get_bot_traffic_data_impl' ) ? $this->get_bot_traffic_data_impl( $start_date, $end_date ) : array() );

                case 'new_vs_returning':
                    return array( 'new_vs_returning' => method_exists( $this, 'get_new_vs_returning_visitors_data_impl' ) ? $this->get_new_vs_returning_visitors_data_impl( $start_date, $end_date ) : array() );

                case 'visited_directories':
                    return array( 'visited_directories' => method_exists( $this, 'get_visited_directories_data_impl' ) ? $this->get_visited_directories_data_impl( $start_date, $end_date ) : array() );

                case 'new_registered_users':
                    return array( 'new_registered_users' => method_exists( $this, 'get_new_registered_users_data_impl' ) ? $this->get_new_registered_users_data_impl( $start_date, $end_date ) : array() );

                case 'summary_stats':
                    // Get summary stats for the stats cards (visitors, sessions, pageviews, etc.)
                    return $this->get_summary_stats_data( $start_date, $end_date );

                case 'top_users':
                    return array( 'top_users' => $this->get_top_engaged_users_data( $start_date, $end_date ) );

                default:
                    return array();
            }
        }

        /**
         * AJAX handler for session data.
         *
         * @since 1.0.0
         */
        private function ajax_session_data_impl() {
            check_ajax_referer( 'opti_behavior_dashboard_nonce', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_die( esc_html__( 'Unauthorized', 'opti-behavior' ) );
            }

            $page = isset( $_POST['page'] ) ? intval( wp_unslash( $_POST['page'] ) ) : 1;
            $per_page = isset( $_POST['per_page'] ) ? intval( wp_unslash( $_POST['per_page'] ) ) : 10;
            $filters = isset( $_POST['filters'] ) ? array_map( 'sanitize_text_field', wp_unslash( $_POST['filters'] ) ) : array();

            $data = $this->get_session_data( $page, $per_page, $filters );

            wp_send_json_success( $data );
        }

        /**
         * AJAX handler for analytics data.
         *
         * @since 1.0.0
         */
        private function ajax_analytics_data_impl() {
            check_ajax_referer( 'opti_behavior_dashboard_nonce', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_die( esc_html__( 'Unauthorized', 'opti-behavior' ) );
            }

            $type = isset( $_POST['type'] ) ? sanitize_text_field( wp_unslash( $_POST['type'] ) ) : 'overview';
            $period = isset( $_POST['period'] ) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : 'last30days';

            $data = $this->get_analytics_data( $type, $period );

            wp_send_json_success( $data );
        }
        /**
         * AJAX handler for top engaged users.
         *
         * Returns Top Engaged Users for the selected period.
         * Version: 2025-09-30-v3 - Always resolve country name from code.
         *
         * @since 1.0.0
         * @global wpdb $wpdb WordPress database abstraction object.
         */
        private function ajax_top_users_impl() {
            check_ajax_referer('optibehavior_top_users','nonce');
            if ( ! current_user_can('manage_options') ) { wp_die( esc_html__( 'Unauthorized', 'opti-behavior' ) ); }

            $period = isset($_POST['period']) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : 'last30days';
            $start_q = isset($_POST['start_date']) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : '';
            $end_q   = isset($_POST['end_date']) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : '';

            // Get exclude_spam parameter
            $exclude_spam = isset( $_POST['exclude_spam'] ) && sanitize_text_field( wp_unslash( $_POST['exclude_spam'] ) ) === '1';
            $GLOBALS['opti_behavior_exclude_spam'] = $exclude_spam;

            if ($period === 'custom' && $start_q && $end_q) {
                $start_date = $start_q . ' 00:00:00';
                $end_date   = $end_q   . ' 23:59:59';
            } else {
                $range = $this->get_date_range($period);
                $start_date = $range['start'];
                $end_date   = $range['end'];
            }

            // Transient cache for Top Users per date-range (include spam filter in key).
            // Default TTL: 30s (filterable) — was 15 min before 1.2.9, which hid new visitor activity.
            $cache_key = 'optibehavior_top_users_' . md5($start_date . '|' . $end_date . '|' . ($exclude_spam ? '1' : '0'));
            $default_ttl = function_exists( 'opti_behavior_dashboard_cache_ttl' ) ? opti_behavior_dashboard_cache_ttl() : 30;
            $cache_ttl = apply_filters('opti_behavior_top_users_cache_ttl', $default_ttl);

            // Honour force-refresh: skip reading the cache on explicit refresh.
            $force_refresh = function_exists( 'opti_behavior_is_force_refresh_requested' ) && opti_behavior_is_force_refresh_requested();
            if ( $force_refresh ) {
                delete_transient( $cache_key );
            } else {
                $cached = get_transient($cache_key);
                if ($cached !== false) {
                    wp_send_json_success(array('items' => $cached));
                }
            }


            global $wpdb;
            // Escape table names for WordPress Plugin Check compliance
            $sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
            $summary_table = esc_sql( $wpdb->prefix . 'optibehavior_visitor_daily_stats' );
            $visitors_table = esc_sql( $wpdb->prefix . 'optibehavior_visitors' );
            $users_table = esc_sql( $wpdb->users );

            // OPTIMIZED: Use IN clause for spam filter instead of != for better index usage
            $spam_filter_clause = '';
            if ( $exclude_spam ) {
                $spam_filter_clause = " AND ds.traffic_type IN ('human', 'automated', 'bot')";
            }

            // Check if summary table exists and has data for this date range
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
            $summary_exists = $wpdb->get_var( "SHOW TABLES LIKE '" . $summary_table . "'" );
            $use_summary = false;
            if ( $summary_exists ) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                $summary_count = $wpdb->get_var( $wpdb->prepare(
                    "SELECT COUNT(*) FROM " . $summary_table . " WHERE stat_date BETWEEN %s AND %s",
                    substr($start_date, 0, 10), substr($end_date, 0, 10)
                ) );
                $use_summary = ( $summary_count > 0 );
            }

            if ( $use_summary ) {
                // FAST PATH: Use pre-aggregated summary table (4x faster)
                // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
                $rows = $wpdb->get_results( $wpdb->prepare(
                    "SELECT
                        agg.visitor_id,
                        agg.user_id,
                        agg.sessions,
                        agg.active_days,
                        agg.pages_per_session,
                        agg.total_time,
                        agg.avg_session_time,
                        agg.last_seen,
                        UPPER(COALESCE(NULLIF(TRIM(v.country),''), '')) AS country_code,
                        v.country_name,
                        u.user_login,
                        u.display_name
                     FROM (
                        SELECT
                            ds.visitor_id,
                            MAX(ds.user_id) AS user_id,
                            SUM(ds.sessions) AS sessions,
                            COUNT(DISTINCT ds.stat_date) AS active_days,
                            ROUND(SUM(ds.total_page_views) / NULLIF(SUM(ds.sessions), 0), 2) AS pages_per_session,
                            SUM(ds.total_duration) AS total_time,
                            ROUND(SUM(ds.total_duration) / NULLIF(SUM(ds.sessions), 0)) AS avg_session_time,
                            MAX(ds.last_seen) AS last_seen
                        FROM " . $summary_table . " ds
                        WHERE ds.stat_date BETWEEN %s AND %s" . $spam_filter_clause . "
                        GROUP BY ds.visitor_id
                        ORDER BY total_time DESC
                        LIMIT 100
                     ) agg
                     LEFT JOIN " . $visitors_table . " v ON v.id = agg.visitor_id
                     LEFT JOIN " . $users_table . " u ON u.ID = agg.user_id",
                     substr($start_date, 0, 10), substr($end_date, 0, 10)
                ) );
            } else {
                // FALLBACK: Use sessions table directly (slower but always works)
                $spam_filter_sessions = '';
                if ( $exclude_spam ) {
                    $spam_filter_sessions = " AND s.traffic_type IN ('human', 'automated', 'bot')";
                }
                // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
                $rows = $wpdb->get_results( $wpdb->prepare(
                    "SELECT
                        agg.visitor_id,
                        agg.user_id,
                        agg.sessions,
                        agg.active_days,
                        agg.pages_per_session,
                        agg.total_time,
                        agg.avg_session_time,
                        agg.last_seen,
                        UPPER(COALESCE(NULLIF(TRIM(v.country),''), '')) AS country_code,
                        v.country_name,
                        u.user_login,
                        u.display_name
                     FROM (
                        SELECT
                            s.visitor_id,
                            MAX(s.user_id) AS user_id,
                            COUNT(*) AS sessions,
                            COUNT(DISTINCT DATE(s.start_time)) AS active_days,
                            COALESCE(ROUND(AVG(NULLIF(s.page_views,0)), 2), 0) AS pages_per_session,
                            SUM(GREATEST(s.duration, 0)) AS total_time,
                            AVG(GREATEST(s.duration, 0)) AS avg_session_time,
                            MAX(COALESCE(s.end_time, s.start_time)) AS last_seen
                        FROM " . $sessions_table . " s
                        WHERE s.start_time BETWEEN %s AND %s" . $spam_filter_sessions . "
                        GROUP BY s.visitor_id
                        ORDER BY total_time DESC
                        LIMIT 100
                     ) agg
                     LEFT JOIN " . $visitors_table . " v ON v.id = agg.visitor_id
                     LEFT JOIN " . $users_table . " u ON u.ID = agg.user_id",
                     $start_date, $end_date
                ) );
            }

            $now = current_time('timestamp');
            $items = array();
            foreach ((array)$rows as $r){
                $vid = (string)$r->visitor_id;
                $short = $vid;
                if (strlen($vid) > 12) {
                    $short = substr($vid,0,8) . '…' . substr($vid,-4);
                }
                $active_days = max(1, (int)$r->active_days);
                $daily_freq = (float)$r->sessions / $active_days;
                $last_ts = strtotime((string)$r->last_seen);

                // Apply country name fallback logic - ALWAYS resolve from country code
                $country_code = strtoupper(trim((string)$r->country_code));

                // Always resolve country name from country code if we have a valid code
                if (!empty($country_code) && $country_code !== 'UN' && $country_code !== 'NULL') {
                    $country_name = $this->get_country_name($country_code);
                    // If get_country_name returns 'Unknown', keep the original country_name from DB if available
                    if ($country_name === 'Unknown' && !empty($r->country_name)) {
                        $country_name = $r->country_name;
                    }
                } else {
                    // Use country_name from database if available, otherwise use 'Unknown'
                    $country_name = !empty($r->country_name) ? $r->country_name : 'Unknown';
                    // If we have no valid country code, set to 'UN' for display consistency
                    if (empty($country_code) || $country_code === 'NULL') {
                        $country_code = 'UN';
                    }
                }

                // Check if this visitor is a WordPress user
                $user_id = !empty($r->user_id) ? (int)$r->user_id : 0;
                $user_login = !empty($r->user_login) ? (string)$r->user_login : '';
                $display_name = !empty($r->display_name) ? (string)$r->display_name : '';
                $profile_url = $user_id > 0 ? admin_url('user-edit.php?user_id=' . $user_id) : '';

                $items[] = array(
                    'visitor_id'       => $vid,
                    'visitor_short'    => $short,
                    'user_id'          => $user_id,
                    'user_login'       => $user_login,
                    'display_name'     => $display_name,
                    'profile_url'      => $profile_url,
                    'sessions'         => (int)$r->sessions,
                    'pages_per_session'=> (float)$r->pages_per_session,
                    'total_time'       => (int)$r->total_time,
                    'avg_session_time' => (int)round((float)$r->avg_session_time),
                    'daily_freq'       => round($daily_freq, 2),
                    'country_code'     => $country_code,
                    'country_name'     => $country_name,
                    'last_seen'        => (string)$r->last_seen,
                    'last_seen_human'  => ($last_ts? human_time_diff($last_ts, $now) . ' ago' : ''),
                );
            }

            set_transient($cache_key, $items, $cache_ttl);
            wp_send_json_success(array('items'=>$items));
        }

        /**
         * Get country name from country code.
         *
         * @since 1.0.0
         * @param string $country_code Country code.
         * @return string Country name.
         */
        private function get_country_name( $country_code ) {
            $country_code = strtoupper(trim((string)$country_code));
            $countries = array(
                'AF' => 'Afghanistan', 'AL' => 'Albania', 'DZ' => 'Algeria', 'AS' => 'American Samoa',
                'AD' => 'Andorra', 'AO' => 'Angola', 'AI' => 'Anguilla', 'AQ' => 'Antarctica',
                'AG' => 'Antigua and Barbuda', 'AR' => 'Argentina', 'AM' => 'Armenia', 'AW' => 'Aruba',
                'AU' => 'Australia', 'AT' => 'Austria', 'AZ' => 'Azerbaijan', 'BS' => 'Bahamas',
                'BH' => 'Bahrain', 'BD' => 'Bangladesh', 'BB' => 'Barbados', 'BY' => 'Belarus',
                'BE' => 'Belgium', 'BZ' => 'Belize', 'BJ' => 'Benin', 'BM' => 'Bermuda',
                'BT' => 'Bhutan', 'BO' => 'Bolivia', 'BA' => 'Bosnia and Herzegovina', 'BW' => 'Botswana',
                'BR' => 'Brazil', 'BN' => 'Brunei', 'BG' => 'Bulgaria', 'BF' => 'Burkina Faso',
                'BI' => 'Burundi', 'CV' => 'Cabo Verde', 'KH' => 'Cambodia', 'CM' => 'Cameroon',
                'CA' => 'Canada', 'KY' => 'Cayman Islands', 'CF' => 'Central African Republic', 'TD' => 'Chad',
                'CL' => 'Chile', 'CN' => 'China', 'CO' => 'Colombia', 'KM' => 'Comoros',
                'CG' => 'Congo', 'CD' => 'Congo, Democratic Republic', 'CK' => 'Cook Islands', 'CR' => 'Costa Rica',
                'CI' => 'Cote d\'Ivoire', 'HR' => 'Croatia', 'CU' => 'Cuba', 'CY' => 'Cyprus',
                'CZ' => 'Czech Republic', 'DK' => 'Denmark', 'DJ' => 'Djibouti', 'DM' => 'Dominica',
                'DO' => 'Dominican Republic', 'EC' => 'Ecuador', 'EG' => 'Egypt', 'SV' => 'El Salvador',
                'GQ' => 'Equatorial Guinea', 'ER' => 'Eritrea', 'EE' => 'Estonia', 'SZ' => 'Eswatini',
                'ET' => 'Ethiopia', 'FK' => 'Falkland Islands', 'FO' => 'Faroe Islands', 'FJ' => 'Fiji',
                'FI' => 'Finland', 'FR' => 'France', 'GF' => 'French Guiana', 'PF' => 'French Polynesia',
                'GA' => 'Gabon', 'GM' => 'Gambia', 'GE' => 'Georgia', 'DE' => 'Germany',
                'GH' => 'Ghana', 'GI' => 'Gibraltar', 'GR' => 'Greece', 'GL' => 'Greenland',
                'GD' => 'Grenada', 'GP' => 'Guadeloupe', 'GU' => 'Guam', 'GT' => 'Guatemala',
                'GG' => 'Guernsey', 'GN' => 'Guinea', 'GW' => 'Guinea-Bissau', 'GY' => 'Guyana',
                'HT' => 'Haiti', 'HN' => 'Honduras', 'HK' => 'Hong Kong', 'HU' => 'Hungary',
                'IS' => 'Iceland', 'IN' => 'India', 'ID' => 'Indonesia', 'IR' => 'Iran',
                'IQ' => 'Iraq', 'IE' => 'Ireland', 'IM' => 'Isle of Man', 'IL' => 'Israel',
                'IT' => 'Italy', 'JM' => 'Jamaica', 'JP' => 'Japan', 'JE' => 'Jersey',
                'JO' => 'Jordan', 'KZ' => 'Kazakhstan', 'KE' => 'Kenya', 'KI' => 'Kiribati',
                'KP' => 'North Korea', 'KR' => 'South Korea', 'KW' => 'Kuwait', 'KG' => 'Kyrgyzstan',
                'LA' => 'Laos', 'LV' => 'Latvia', 'LB' => 'Lebanon', 'LS' => 'Lesotho',
                'LR' => 'Liberia', 'LY' => 'Libya', 'LI' => 'Liechtenstein', 'LT' => 'Lithuania',
                'LU' => 'Luxembourg', 'MO' => 'Macao', 'MG' => 'Madagascar', 'MW' => 'Malawi',
                'MY' => 'Malaysia', 'MV' => 'Maldives', 'ML' => 'Mali', 'MT' => 'Malta',
                'MH' => 'Marshall Islands', 'MQ' => 'Martinique', 'MR' => 'Mauritania', 'MU' => 'Mauritius',
                'YT' => 'Mayotte', 'MX' => 'Mexico', 'FM' => 'Micronesia', 'MD' => 'Moldova',
                'MC' => 'Monaco', 'MN' => 'Mongolia', 'ME' => 'Montenegro', 'MS' => 'Montserrat',
                'MA' => 'Morocco', 'MZ' => 'Mozambique', 'MM' => 'Myanmar', 'NA' => 'Namibia',
                'NR' => 'Nauru', 'NP' => 'Nepal', 'NL' => 'Netherlands', 'NC' => 'New Caledonia',
                'NZ' => 'New Zealand', 'NI' => 'Nicaragua', 'NE' => 'Niger', 'NG' => 'Nigeria',
                'NU' => 'Niue', 'NF' => 'Norfolk Island', 'MK' => 'North Macedonia', 'MP' => 'Northern Mariana Islands',
                'NO' => 'Norway', 'OM' => 'Oman', 'PK' => 'Pakistan', 'PW' => 'Palau',
                'PS' => 'Palestine', 'PA' => 'Panama', 'PG' => 'Papua New Guinea', 'PY' => 'Paraguay',
                'PE' => 'Peru', 'PH' => 'Philippines', 'PN' => 'Pitcairn', 'PL' => 'Poland',
                'PT' => 'Portugal', 'PR' => 'Puerto Rico', 'QA' => 'Qatar', 'RE' => 'Reunion',
                'RO' => 'Romania', 'RU' => 'Russia', 'RW' => 'Rwanda', 'BL' => 'Saint Barthelemy',
                'SH' => 'Saint Helena', 'KN' => 'Saint Kitts and Nevis', 'LC' => 'Saint Lucia',
                'MF' => 'Saint Martin', 'PM' => 'Saint Pierre and Miquelon', 'VC' => 'Saint Vincent and the Grenadines',
                'WS' => 'Samoa', 'SM' => 'San Marino', 'ST' => 'Sao Tome and Principe', 'SA' => 'Saudi Arabia',
                'SN' => 'Senegal', 'RS' => 'Serbia', 'SC' => 'Seychelles', 'SL' => 'Sierra Leone',
                'SG' => 'Singapore', 'SX' => 'Sint Maarten', 'SK' => 'Slovakia', 'SI' => 'Slovenia',
                'SB' => 'Solomon Islands', 'SO' => 'Somalia', 'ZA' => 'South Africa', 'SS' => 'South Sudan',
                'ES' => 'Spain', 'LK' => 'Sri Lanka', 'SD' => 'Sudan', 'SR' => 'Suriname',
                'SJ' => 'Svalbard and Jan Mayen', 'SE' => 'Sweden', 'CH' => 'Switzerland', 'SY' => 'Syria',
                'TW' => 'Taiwan', 'TJ' => 'Tajikistan', 'TZ' => 'Tanzania', 'TH' => 'Thailand',
                'TL' => 'Timor-Leste', 'TG' => 'Togo', 'TK' => 'Tokelau', 'TO' => 'Tonga',
                'TT' => 'Trinidad and Tobago', 'TN' => 'Tunisia', 'TR' => 'Turkey', 'TM' => 'Turkmenistan',
                'TC' => 'Turks and Caicos Islands', 'TV' => 'Tuvalu', 'UG' => 'Uganda', 'UA' => 'Ukraine',
                'AE' => 'United Arab Emirates', 'GB' => 'United Kingdom', 'US' => 'United States',
                'UY' => 'Uruguay', 'UZ' => 'Uzbekistan', 'VU' => 'Vanuatu', 'VE' => 'Venezuela',
                'VN' => 'Vietnam', 'VG' => 'British Virgin Islands', 'VI' => 'U.S. Virgin Islands',
                'WF' => 'Wallis and Futuna', 'EH' => 'Western Sahara', 'YE' => 'Yemen',
                'ZM' => 'Zambia', 'ZW' => 'Zimbabwe'
            );

            // Fallback: if code not in map, show the code itself (never "Unknown")
            return $countries[$country_code] ?? ($country_code ?: 'Other');
        }

        /**
         * AJAX handler for cleanup recordings by date.
         *
         * @since 1.0.0
         * @global wpdb $wpdb WordPress database abstraction object.
         */
        private function ajax_cleanup_by_date_impl() {
            check_ajax_referer('opti_behavior_cleanup', 'nonce');

            if (!current_user_can('manage_options')) {
                wp_send_json_error(array('message' => esc_html__( 'Permission denied', 'opti-behavior' )));
            }

            $date = isset($_POST['date']) ? sanitize_text_field( wp_unslash( $_POST['date'] ) ) : '';
            if (empty($date)) {
                wp_send_json_error(array('message' => esc_html__( 'Invalid date', 'opti-behavior' )));
            }

            global $wpdb;
            $table = $wpdb->prefix . 'optibehavior_recordings';

            // Convert date to timestamp
            $timestamp = strtotime($date . ' 23:59:59');
            if (!$timestamp) {
                wp_send_json_error(array('message' => esc_html__( 'Invalid date format', 'opti-behavior' )));
            }

            // Get file storage instance (only if Pro is active)
            $file_storage = null;
            if (opti_behavior_pro_active() && class_exists('Opti_Behavior_Heatmap_File_Storage')) {
                $file_storage = new Opti_Behavior_Heatmap_File_Storage( $this->heatmap->get_debug_manager() );
            }

            // Escape table name for WordPress Plugin Check compliance
            $safe_table = esc_sql( $table );

            // Get recordings before date (use start_time instead of created_at)
            $recordings = $wpdb->get_results($wpdb->prepare(
                "SELECT id, session_id, file_path, storage_type, start_time, duration
                FROM " . $safe_table . "
                WHERE start_time < %s",
                gmdate('Y-m-d H:i:s', $timestamp)
            ));

            $deleted_count = 0;
            $deleted_size = 0;

            foreach ($recordings as $recording) {
                // Delete file if it exists (only for file storage)
                if ($recording->storage_type === 'file' && !empty($recording->file_path)) {
                    // Get proper file path using WordPress upload directory
                    $upload_dir = wp_upload_dir();
                    $base_dir = trailingslashit($upload_dir['basedir']) . 'opti-behavior-data/recordings/';

                    // If Pro version is active and has file storage, use its base directory
                    if ($file_storage && method_exists($file_storage, 'get_base_dir')) {
                        $base_dir = trailingslashit($file_storage->get_base_dir());
                    }

                    $full_path = $base_dir . ltrim($recording->file_path, '/\\');
                    if (file_exists($full_path)) {
                        $deleted_size += filesize($full_path);
                        wp_delete_file($full_path);
                    }
                }

                // Delete database record
                $wpdb->delete($table, array('id' => $recording->id), array('%d'));
                $deleted_count++;
            }

            wp_send_json_success(array(
                'message' => sprintf(
                    'Deleted %d recording(s) (%.2f MB) created before %s',
                    $deleted_count,
                    $deleted_size / 1024 / 1024,
                    gmdate('Y-m-d', $timestamp)
                )
            ));
        }

        /**
         * AJAX handler for cleanup recordings by duration.
         *
         * @since 1.0.0
         * @global wpdb $wpdb WordPress database abstraction object.
         */
        private function ajax_cleanup_by_duration_impl() {
            check_ajax_referer('opti_behavior_cleanup', 'nonce');

            if (!current_user_can('manage_options')) {
                wp_send_json_error(array('message' => esc_html__( 'Permission denied', 'opti-behavior' )));
            }

            $min_duration = isset($_POST['duration']) ? intval( wp_unslash( $_POST['duration'] ) ) : 0;
            if ($min_duration < 1) {
                wp_send_json_error(array('message' => esc_html__( 'Invalid duration', 'opti-behavior' )));
            }

            global $wpdb;
            $table = $wpdb->prefix . 'optibehavior_recordings';

            // Get file storage instance (only if Pro is active)
            $file_storage = null;
            if (opti_behavior_pro_active() && class_exists('Opti_Behavior_Heatmap_File_Storage')) {
                $file_storage = new Opti_Behavior_Heatmap_File_Storage( $this->heatmap->get_debug_manager() );
            }

            // Escape table name for WordPress Plugin Check compliance
            $safe_table = esc_sql( $table );

            // Bug 10: Filter by duration threshold in SQL and cap batch size to avoid memory exhaustion.
            // recording_data (large blob) and start_time are excluded as they are not needed here.
            $recordings = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, session_id, file_path, storage_type, duration
                FROM " . $safe_table . "
                WHERE duration > 0 AND duration < %d
                LIMIT 500",
                $min_duration
            ) );

            $deleted_count = 0;
            $deleted_size = 0;

            foreach ($recordings as $recording) {
                // Use duration from database if available
                $duration = isset($recording->duration) ? intval($recording->duration) : 0;

                // Delete if duration is below threshold
                if ($duration > 0 && $duration < $min_duration) {
                    // Delete file if it exists (only for file storage)
                    if ($recording->storage_type === 'file' && !empty($recording->file_path)) {
                        // Get proper file path using WordPress upload directory
                        $upload_dir = wp_upload_dir();
                        $base_dir = trailingslashit($upload_dir['basedir']) . 'opti-behavior-data/recordings/';

                        // If Pro version is active and has file storage, use its base directory
                        if ($file_storage && method_exists($file_storage, 'get_base_dir')) {
                            $base_dir = trailingslashit($file_storage->get_base_dir());
                        }

                        $full_path = $base_dir . ltrim($recording->file_path, '/\\');
                        if (file_exists($full_path)) {
                            $deleted_size += filesize($full_path);
                            wp_delete_file($full_path);
                        }
                    }

                    // Delete database record
                    $wpdb->delete($table, array('id' => $recording->id), array('%d'));
                    $deleted_count++;
                }
            }

            wp_send_json_success(array(
                'message' => sprintf(
                    'Deleted %d recording(s) (%.2f MB) shorter than %d seconds',
                    $deleted_count,
                    $deleted_size / 1024 / 1024,
                    $min_duration
                )
            ));
        }

        /**
         * AJAX handler for cleanup orphaned recording files.
         *
         * @since 1.0.0
         * @global wpdb $wpdb WordPress database abstraction object.
         */
        private function ajax_cleanup_orphaned_files_impl() {
            check_ajax_referer('opti_behavior_cleanup', 'nonce');

            if (!current_user_can('manage_options')) {
                wp_send_json_error(array('message' => esc_html__( 'Permission denied', 'opti-behavior' )));
            }

            global $wpdb;
            $table = $wpdb->prefix . 'optibehavior_recordings';

            // Escape table name for WordPress Plugin Check compliance
            $safe_table = esc_sql( $table );

            // Get all file paths from database
            $db_file_paths = $wpdb->get_col(
                "SELECT file_path FROM " . $safe_table . " WHERE file_path IS NOT NULL AND file_path != ''"
            );

            // Get proper file path using WordPress upload directory
            $upload_dir = wp_upload_dir();
            $base_dir = trailingslashit($upload_dir['basedir']) . 'opti-behavior-data/recordings/';

            // Get file storage instance (only if Pro is active)
            $file_storage = null;
            if (opti_behavior_pro_active() && class_exists('Opti_Behavior_Heatmap_File_Storage')) {
                $file_storage = new Opti_Behavior_Heatmap_File_Storage( $this->heatmap->get_debug_manager() );
                // If Pro version has file storage, use its base directory
                if (method_exists($file_storage, 'get_base_dir')) {
                    $base_dir = trailingslashit($file_storage->get_base_dir());
                }
            }

            if (!is_dir($base_dir)) {
                wp_send_json_error(array('message' => esc_html__( 'Recordings directory not found', 'opti-behavior' )));
            }

            // Scan recordings directory for all .json.gz files
            $deleted_count = 0;
            $deleted_size = 0;

            $iterator = new RecursiveIteratorIterator(
                new RecursiveDirectoryIterator($base_dir, RecursiveDirectoryIterator::SKIP_DOTS),
                RecursiveIteratorIterator::SELF_FIRST
            );

            foreach ($iterator as $file) {
                if ($file->isFile() && pathinfo($file->getFilename(), PATHINFO_EXTENSION) === 'gz') {
                    // Get relative path from base directory
                    $relative_path = str_replace($base_dir, '', $file->getPathname());
                    $relative_path = str_replace('\\', '/', $relative_path);

                    // Check if this file has a database record
                    if (!in_array($relative_path, $db_file_paths, true)) {
                        // This is an orphaned file - delete it
                        $deleted_size += $file->getSize();
                        wp_delete_file($file->getPathname());
                        $deleted_count++;
                    }
                }
            }

            wp_send_json_success(array(
                'message' => sprintf(
                    'Deleted %d orphaned file(s) (%.2f MB)',
                    $deleted_count,
                    $deleted_size / 1024 / 1024
                )
            ));
        }

        /**
         * AJAX handler for Visitor Heatmap widget
         *
         * @since 1.0.4
         * @global wpdb $wpdb WordPress database abstraction object.
         */
        private function ajax_visitor_heatmap_impl() {
            // Verify nonce
            check_ajax_referer( 'opti_behavior_dashboard_nonce', 'nonce' );

            // Verify user capabilities
            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => esc_html__( 'Insufficient permissions', 'opti-behavior' ) ) );
            }

            // Get and sanitize parameters
            $period = isset( $_POST['period'] ) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : 'last30days';
            $start_date = isset( $_POST['start_date'] ) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : null;
            $end_date = isset( $_POST['end_date'] ) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : null;

            // Get exclude_spam parameter
            $exclude_spam = isset( $_POST['exclude_spam'] ) && sanitize_text_field( wp_unslash( $_POST['exclude_spam'] ) ) === '1';
            $GLOBALS['opti_behavior_exclude_spam'] = $exclude_spam;

            // Get date range
            $date_range = $this->get_date_range_impl( $period, $start_date, $end_date );
            $start_date = $date_range['start'];
            $end_date = $date_range['end'];

            global $wpdb;
            // Escape table name for Plugin Check compliance
            $sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );

            // Get spam exclusion clause
            $spam_clause = $this->get_spam_exclusion_clause();

            // Get visitor counts grouped by day of week, hour, and date to calculate averages
            // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            $results = $wpdb->get_results( $wpdb->prepare(
                "SELECT
                    DAYOFWEEK(start_time) as day_of_week,
                    HOUR(start_time) as hour,
                    DATE(start_time) as visit_date,
                    COUNT(DISTINCT visitor_id) as visit_count
                 FROM " . $sessions_table . "
                 WHERE start_time BETWEEN %s AND %s" . $spam_clause . "
                 GROUP BY DAYOFWEEK(start_time), HOUR(start_time), DATE(start_time)
                 ORDER BY day_of_week, hour, visit_date",
                 $start_date, $end_date
            ) );

            // Transform results into a structured array and calculate averages
            // First, group by day/hour and collect all visit counts for that combination
            $grouped_data = array();
            foreach ($results as $row) {
                $day = (int)$row->day_of_week; // 1=Sunday, 2=Monday, ..., 7=Saturday
                $hour = (int)$row->hour;
                $count = (int)$row->visit_count;

                if (!isset($grouped_data[$day])) {
                    $grouped_data[$day] = array();
                }
                if (!isset($grouped_data[$day][$hour])) {
                    $grouped_data[$day][$hour] = array();
                }
                $grouped_data[$day][$hour][] = $count;
            }

            // Calculate the average (mean) for each day/hour combination
            $heatmap_data = array();
            foreach ($grouped_data as $day => $hours) {
                if (!isset($heatmap_data[$day])) {
                    $heatmap_data[$day] = array();
                }
                foreach ($hours as $hour => $counts) {
                    // Calculate mean: sum of all counts / number of occurrences
                    $sum = array_sum($counts);
                    $num_occurrences = count($counts);
                    $average = $num_occurrences > 0 ? round($sum / $num_occurrences) : 0;
                    $heatmap_data[$day][$hour] = $average;
                }
            }

            wp_send_json_success( array(
                'heatmap_data' => $heatmap_data,
                'period' => $period,
                'start_date' => $start_date,
                'end_date' => $end_date
            ) );
        }

        /**
         * AJAX handler for batch spam recalculation.
         * Processes sessions in batches to avoid timeout and provides progress updates.
         *
         * @since 1.0.8.29
         */
        public function ajax_recalculate_spam() {
            check_ajax_referer( 'opti_behavior_settings_nonce', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            global $wpdb;
            $sessions_table = $wpdb->prefix . 'optibehavior_sessions';
            $events_table = $wpdb->prefix . 'optibehavior_events';

            // Get parameters
            $batch_size = isset( $_POST['batch_size'] ) ? absint( $_POST['batch_size'] ) : 500;
            $offset = isset( $_POST['offset'] ) ? absint( $_POST['offset'] ) : 0;
            $action_type = isset( $_POST['action_type'] ) ? sanitize_text_field( wp_unslash( $_POST['action_type'] ) ) : 'recalculate';

            // Get spam settings
            $settings = get_option( 'opti_behavior_traffic_settings', array() );
            $spam_duration_threshold = isset( $settings['spam_duration_threshold'] ) ? intval( $settings['spam_duration_threshold'] ) : 10;
            $spam_min_scrolls_threshold = isset( $settings['spam_min_scrolls_threshold'] ) ? intval( $settings['spam_min_scrolls_threshold'] ) : 1;
            $spam_min_clicks_threshold = isset( $settings['spam_min_clicks_threshold'] ) ? intval( $settings['spam_min_clicks_threshold'] ) : 1;

            // First call - get total count and reset spam flags
            if ( $offset === 0 && $action_type === 'start' ) {
                // Get total sessions count (excluding bots)
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
                $total_sessions = $wpdb->get_var(
                    "SELECT COUNT(*) FROM " . $sessions_table . " WHERE traffic_type != 'bot' OR traffic_type IS NULL"
                );

                // Reset all spam sessions to human (keep bots as bots)
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
                $wpdb->query(
                    "UPDATE " . $sessions_table . " SET traffic_type = 'human', spam_reason = NULL
                     WHERE traffic_type = 'spam'"
                );

                // Store total in transient for progress tracking
                set_transient( 'opti_behavior_spam_recalc_total', $total_sessions, 3600 );
                set_transient( 'opti_behavior_spam_recalc_processed', 0, 3600 );

                wp_send_json_success( array(
                    'status' => 'started',
                    'total' => (int) $total_sessions,
                    'processed' => 0,
                    'progress' => 0,
                    'next_offset' => 0
                ) );
            }

            // Get batch of session IDs to process
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
            // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            $sessions = $wpdb->get_col( $wpdb->prepare(
                "SELECT id FROM " . $sessions_table . "
                 WHERE (traffic_type = 'human' OR traffic_type IS NULL)
                 ORDER BY id ASC
                 LIMIT %d OFFSET %d",
                $batch_size,
                $offset
            ) );

            if ( empty( $sessions ) ) {
                // All done - clear transients and caches
                delete_transient( 'opti_behavior_spam_recalc_total' );
                delete_transient( 'opti_behavior_spam_recalc_processed' );

                // Clear related caches
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_optibehavior_top_users_%'" );
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_timeout_optibehavior_top_users_%'" );
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_opti_behavior_traffic_class_%'" );
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_timeout_opti_behavior_traffic_class_%'" );

                wp_send_json_success( array(
                    'status' => 'completed',
                    'message' => __( 'Spam recalculation completed successfully.', 'opti-behavior' )
                ) );
            }

            // Process this batch - mark sessions as spam based on criteria
            $session_ids_placeholder = implode( ',', array_fill( 0, count( $sessions ), '%s' ) );

            // Update sessions that fail ANY of the spam criteria:
            // A legitimate session must meet ALL: duration >= threshold AND scrolls >= min AND clicks >= min
            // So spam = fails at least one criterion (OR logic)
            // phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
            $query = $wpdb->prepare(
                "UPDATE " . $sessions_table . " s
                 LEFT JOIN (
                     SELECT session_id, COUNT(*) as scroll_count
                     FROM " . $events_table . "
                     WHERE session_id IN (" . $session_ids_placeholder . ")
                     AND event IN (32, 33)
                     GROUP BY session_id
                 ) sc ON s.id = sc.session_id
                 LEFT JOIN (
                     SELECT session_id, COUNT(*) as click_count
                     FROM " . $events_table . "
                     WHERE session_id IN (" . $session_ids_placeholder . ")
                     AND event IN (16, 17)
                     GROUP BY session_id
                 ) cc ON s.id = cc.session_id
                 SET s.traffic_type = 'spam',
                     s.spam_reason = CONCAT_WS(',',
                         CASE WHEN (
                             CASE WHEN s.duration > 0 THEN s.duration
                                  ELSE TIMESTAMPDIFF(SECOND, s.start_time, COALESCE(s.end_time, s.start_time))
                             END
                         ) < %d THEN 'short_duration' ELSE NULL END,
                         CASE WHEN COALESCE(sc.scroll_count, 0) < %d THEN 'few_scrolls' ELSE NULL END,
                         CASE WHEN COALESCE(cc.click_count, 0) < %d THEN 'few_clicks' ELSE NULL END
                     )
                 WHERE s.id IN (" . $session_ids_placeholder . ")
                 AND (
                     (
                         CASE
                             WHEN s.duration > 0 THEN s.duration
                             ELSE TIMESTAMPDIFF(SECOND, s.start_time, COALESCE(s.end_time, s.start_time))
                         END
                     ) < %d
                     OR COALESCE(sc.scroll_count, 0) < %d
                     OR COALESCE(cc.click_count, 0) < %d
                 )",
                array_merge( $sessions, $sessions, array( $spam_duration_threshold, $spam_min_scrolls_threshold, $spam_min_clicks_threshold ), $sessions, array( $spam_duration_threshold, $spam_min_scrolls_threshold, $spam_min_clicks_threshold ) )
            );
            // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->query( $query );
            // phpcs:enable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

            // Update progress
            $total = get_transient( 'opti_behavior_spam_recalc_total' );
            $processed = $offset + count( $sessions );
            set_transient( 'opti_behavior_spam_recalc_processed', $processed, 3600 );

            $progress = $total > 0 ? round( ( $processed / $total ) * 100 ) : 100;

            wp_send_json_success( array(
                'status' => 'processing',
                'total' => (int) $total,
                'processed' => (int) $processed,
                'progress' => (int) $progress,
                'next_offset' => $offset + $batch_size
            ) );
        }

        /**
         * AJAX handler to get current spam recalculation status.
         *
         * @since 1.0.8.29
         */
        public function ajax_recalculate_spam_status() {
            check_ajax_referer( 'opti_behavior_settings_nonce', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            $total = get_transient( 'opti_behavior_spam_recalc_total' );
            $processed = get_transient( 'opti_behavior_spam_recalc_processed' );

            if ( false === $total ) {
                wp_send_json_success( array(
                    'status' => 'idle',
                    'message' => __( 'No recalculation in progress.', 'opti-behavior' )
                ) );
            }

            $progress = $total > 0 ? round( ( $processed / $total ) * 100 ) : 0;

            wp_send_json_success( array(
                'status' => 'processing',
                'total' => (int) $total,
                'processed' => (int) $processed,
                'progress' => (int) $progress
            ) );
        }

        /**
         * AJAX handler for batched deletion of all analytics data.
         * Deletes tables one by one with progress updates.
         *
         * @since 1.0.8.30
         */
        public function ajax_delete_all_data() {
            check_ajax_referer( 'opti_behavior_delete_all_ajax', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            global $wpdb;

            // Get current step
            $step = isset( $_POST['step'] ) ? absint( $_POST['step'] ) : 0;

            // Define category-to-tables mapping (10 categories, 30 tables total; +2 Pro-only A/B tables when Pro is active).
            // Category keys are the canonical identifiers sent from JS via the `categories` POST param.
            $categories_map = array(
                'sessions'   => array(
                    array( 'name' => 'optibehavior_sessions',            'label' => __( 'Sessions', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_pageviews',           'label' => __( 'Pageviews', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_session_pages',       'label' => __( 'Session Pages', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_pages',               'label' => __( 'Pages', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_daily_stats',         'label' => __( 'Daily Stats', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_visitor_daily_stats', 'label' => __( 'Visitor Daily Stats', 'opti-behavior' ) ),
                ),
                'visitors'   => array(
                    array( 'name' => 'optibehavior_visitors',            'label' => __( 'Visitors', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_bot_visits',          'label' => __( 'Bot Visits', 'opti-behavior' ) ),
                ),
                'events'     => array(
                    array( 'name' => 'optibehavior_events',              'label' => __( 'Events & Interactions', 'opti-behavior' ) ),
                ),
                'heatmaps'   => array(
                    array( 'name' => 'optibehavior_heatmap_pages',       'label' => __( 'Heatmap Pages', 'opti-behavior' ) ),
                ),
                'recordings' => array(
                    array( 'name' => 'optibehavior_recordings',          'label' => __( 'Recordings', 'opti-behavior' ) ),
                ),
                'traffic'    => array(
                    array( 'name' => 'optibehavior_referrers',           'label' => __( 'Referrers', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_outbound_clicks',     'label' => __( 'Outbound Clicks', 'opti-behavior' ) ),
                ),
                'errors'     => array(
                    array( 'name' => 'optibehavior_errors',              'label' => __( 'Errors', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_error_types',         'label' => __( 'Error Types', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_broken_links',        'label' => __( 'Broken Links', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_friction',            'label' => __( 'Friction Data', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_performance',         'label' => __( 'Performance Data', 'opti-behavior' ) ),
                ),
                'funnels'    => array(
                    array( 'name' => 'opti_behavior_funnels',            'label' => __( 'Funnels', 'opti-behavior' ) ),
                    array( 'name' => 'opti_behavior_funnel_tracking',    'label' => __( 'Funnel Tracking', 'opti-behavior' ) ),
                ),
                'forms'      => array(
                    array( 'name' => 'optibehavior_form_interactions',   'label' => __( 'Form Interactions', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_form_submissions',    'label' => __( 'Form Submissions', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_journey_groups',      'label' => __( 'Journey Groups', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_report_logs',         'label' => __( 'Report Logs', 'opti-behavior' ) ),
                ),
                'ab_testing' => array(
                    array( 'name' => 'optibehavior_ab_daily_stats',      'label' => __( 'A/B Daily Stats', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_ab_conversions',      'label' => __( 'A/B Conversions', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_ab_impressions',      'label' => __( 'A/B Impressions', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_ab_goals',            'label' => __( 'A/B Goals', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_ab_variants',         'label' => __( 'A/B Variants', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_ab_tests',            'label' => __( 'A/B Tests', 'opti-behavior' ) ),
                    // Pro-only tables (silently skipped when table does not exist)
                    array( 'name' => 'optibehavior_ab_targeting_rules',  'label' => __( 'A/B Targeting Rules', 'opti-behavior' ) ),
                    array( 'name' => 'optibehavior_ab_schedule',         'label' => __( 'A/B Schedule', 'opti-behavior' ) ),
                ),
            );

            $all_category_keys = array_keys( $categories_map );

            // Resolve selected categories from POST.
            // If the `categories` param is absent or empty, default to ALL categories
            // so the handler remains fully backward-compatible.
            if ( isset( $_POST['categories'] ) && is_array( $_POST['categories'] ) ) { // phpcs:ignore WordPress.Security.NonceVerification.Missing -- nonce already checked above
                $raw_cats = array_map( 'sanitize_key', (array) $_POST['categories'] ); // phpcs:ignore WordPress.Security.NonceVerification.Missing
                $selected_categories = array_values(
                    array_filter( $raw_cats, function ( $cat ) use ( $all_category_keys ) {
                        return in_array( $cat, $all_category_keys, true );
                    } )
                );
            } else {
                $selected_categories = array();
            }

            // Empty / missing categories list → delete everything (backward compatibility)
            if ( empty( $selected_categories ) ) {
                $selected_categories = $all_category_keys;
            }

            // Build the filtered table list from only the selected categories
            $tables = array();
            foreach ( $selected_categories as $cat_key ) {
                if ( isset( $categories_map[ $cat_key ] ) ) {
                    foreach ( $categories_map[ $cat_key ] as $tbl ) {
                        $tables[] = $tbl;
                    }
                }
            }

            $total_tables     = count( $tables );
            $is_full_deletion = ( count( $selected_categories ) === count( $all_category_keys ) );

            // ---------------------------------------------------------------
            // Final step: all table truncations are done — clean up files and
            // finish. This block is reached when step == total_tables.
            // ---------------------------------------------------------------
            if ( $step >= $total_tables ) {
                $upload_dir    = wp_upload_dir();
                $data_dir      = $upload_dir['basedir'] . '/opti-behavior-data';
                $files_deleted = 0;

                global $wp_filesystem;
                if ( empty( $wp_filesystem ) ) {
                    require_once ABSPATH . '/wp-admin/includes/file.php';
                    WP_Filesystem();
                }

                if ( $is_full_deletion ) {
                    // Backward-compatible path: wipe everything inside opti-behavior-data/
                    if ( is_dir( $data_dir ) ) {
                        $iterator = new \RecursiveIteratorIterator(
                            new \RecursiveDirectoryIterator( $data_dir, \RecursiveDirectoryIterator::SKIP_DOTS ),
                            \RecursiveIteratorIterator::CHILD_FIRST
                        );
                        foreach ( $iterator as $item ) {
                            if ( $item->isFile() ) {
                                wp_delete_file( $item->getPathname() );
                                $files_deleted++;
                            } elseif ( $item->isDir() ) {
                                $wp_filesystem->rmdir( $item->getPathname() );
                            }
                        }
                    }
                } else {
                    // Selective path: only remove the file directories that belong
                    // to the categories the user chose to delete.
                    //   events     → opti-behavior-data/events/
                    //   recordings → opti-behavior-data/recordings/
                    //   heatmaps   → opti-behavior-data/heatmaps/
                    $file_dirs = array();
                    if ( in_array( 'events', $selected_categories, true ) ) {
                        $file_dirs[] = $data_dir . '/events';
                    }
                    if ( in_array( 'recordings', $selected_categories, true ) ) {
                        $file_dirs[] = $data_dir . '/recordings';
                    }
                    if ( in_array( 'heatmaps', $selected_categories, true ) ) {
                        $file_dirs[] = $data_dir . '/heatmaps';
                    }

                    foreach ( $file_dirs as $dir ) {
                        if ( ! is_dir( $dir ) ) {
                            continue;
                        }
                        $iterator = new \RecursiveIteratorIterator(
                            new \RecursiveDirectoryIterator( $dir, \RecursiveDirectoryIterator::SKIP_DOTS ),
                            \RecursiveIteratorIterator::CHILD_FIRST
                        );
                        foreach ( $iterator as $item ) {
                            if ( $item->isFile() ) {
                                wp_delete_file( $item->getPathname() );
                                $files_deleted++;
                            } elseif ( $item->isDir() ) {
                                $wp_filesystem->rmdir( $item->getPathname() );
                            }
                        }
                    }
                }

                // Optimize only the tables that were actually truncated in this run
                foreach ( $tables as $tbl ) {
                    $tbl_name = $wpdb->prefix . $tbl['name'];
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                    $tbl_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $tbl_name ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                    if ( $tbl_exists ) {
                        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
                        $wpdb->query( "OPTIMIZE TABLE `" . $tbl_name . "`" );
                    }
                }

                // Clear transient caches (safe to wipe all — they will be regenerated)
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_optibehavior_%'" );
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_timeout_optibehavior_%'" );
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_opti_behavior_%'" );
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $wpdb->query( "DELETE FROM " . $wpdb->prefix . "options WHERE option_name LIKE '_transient_timeout_opti_behavior_%'" );

                // Build a completion message that accurately reflects what was deleted
                if ( $is_full_deletion ) {
                    $completion_message = sprintf(
                        /* translators: 1: number of tables, 2: number of files */
                        __( 'Successfully deleted data from %1$d tables and %2$d recording files.', 'opti-behavior' ),
                        $total_tables,
                        $files_deleted
                    );
                } else {
                    $completion_message = sprintf(
                        /* translators: 1: number of tables, 2: number of files */
                        __( 'Successfully deleted selected data from %1$d tables and %2$d files.', 'opti-behavior' ),
                        $total_tables,
                        $files_deleted
                    );
                }

                wp_send_json_success( array(
                    'status'  => 'completed',
                    'message' => $completion_message,
                ) );
            }

            // ---------------------------------------------------------------
            // Regular step: truncate a small batch of tables for this cursor.
            // This keeps progress incremental while avoiding one full
            // WordPress/admin-ajax bootstrap per table.
            // ---------------------------------------------------------------
            $batch_size = (int) apply_filters( 'opti_behavior_delete_all_data_batch_size', 5 );
            $batch_size = max( 1, min( 10, $batch_size ) );

            $start_step       = min( $step, $total_tables );
            $next_step        = min( $start_step + $batch_size, $total_tables );
            $processed_labels = array();

            for ( $table_index = $start_step; $table_index < $next_step; $table_index++ ) {
                $current_table = $tables[ $table_index ];
                $table_name    = $wpdb->prefix . $current_table['name'];

                // Check if table exists before truncating.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

                if ( $table_exists ) {
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                    // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
                    $wpdb->query( "TRUNCATE TABLE `" . $table_name . "`" );
                }

                $processed_labels[] = $current_table['label'];
            }

            $progress = round( ( $next_step / ( $total_tables + 1 ) ) * 100 );

            wp_send_json_success( array(
                'status'           => 'processing',
                'step'             => $next_step,
                'total'            => $total_tables + 1, // +1 for the file-cleanup step.
                'progress'         => (int) $progress,
                'current_table'    => end( $processed_labels ),
                'processed_tables' => $processed_labels,
                'message'          => sprintf(
                    /* translators: 1: number of processed table steps, 2: total number of table steps */
                    __( 'Deleted %1$d of %2$d table groups...', 'opti-behavior' ),
                    $next_step,
                    $total_tables
                ),
            ) );
        }

        /**
         * AJAX handler for batched deletion of analytics data by date range.
         * Deletes data from tables one by one with date filtering.
         *
         * @since 1.0.8.30
         */
        public function ajax_delete_data_by_range() {
            check_ajax_referer( 'opti_behavior_delete_range_ajax', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            global $wpdb;

            // Get parameters
            $step = isset( $_POST['step'] ) ? absint( $_POST['step'] ) : 0;
            $start_date = isset( $_POST['start_date'] ) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : '';
            $end_date = isset( $_POST['end_date'] ) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : '';

            // Validate dates
            if ( empty( $start_date ) || empty( $end_date ) ) {
                wp_send_json_error( array( 'message' => __( 'Invalid date range', 'opti-behavior' ) ) );
            }

            // Ensure end date includes the full day
            $end_date_full = $end_date . ' 23:59:59';
            $start_date_full = $start_date . ' 00:00:00';

            // Define tables with their date columns for deletion
            $tables = array(
                array(
                    'name' => 'optibehavior_events',
                    'label' => __( 'Events & Interactions', 'opti-behavior' ),
                    'date_column' => 'event_time'
                ),
                array(
                    'name' => 'optibehavior_sessions',
                    'label' => __( 'Sessions', 'opti-behavior' ),
                    'date_column' => 'start_time'
                ),
                array(
                    'name' => 'optibehavior_pageviews',
                    'label' => __( 'Pageviews', 'opti-behavior' ),
                    'date_column' => 'view_time'
                ),
                array(
                    'name' => 'optibehavior_visitors',
                    'label' => __( 'Visitors', 'opti-behavior' ),
                    'date_column' => 'first_visit'
                ),
                array(
                    'name' => 'optibehavior_recordings',
                    'label' => __( 'Recordings', 'opti-behavior' ),
                    'date_column' => 'created_at'
                ),
                array(
                    'name' => 'optibehavior_session_pages',
                    'label' => __( 'Session Pages', 'opti-behavior' ),
                    'date_column' => 'entry_time'
                ),
                array(
                    'name' => 'optibehavior_referrers',
                    'label' => __( 'Referrers', 'opti-behavior' ),
                    'date_column' => 'created_at'
                ),
                array(
                    'name' => 'optibehavior_outbound_clicks',
                    'label' => __( 'Outbound Clicks', 'opti-behavior' ),
                    'date_column' => 'created_at'
                ),
                array(
                    'name' => 'optibehavior_bot_visits',
                    'label' => __( 'Bot Visits', 'opti-behavior' ),
                    'date_column' => 'visit_time'
                ),
                array(
                    'name' => 'optibehavior_heatmap_pages',
                    'label' => __( 'Heatmap Pages', 'opti-behavior' ),
                    'date_column' => 'created_at'
                ),
                array(
                    'name' => 'optibehavior_daily_stats',
                    'label' => __( 'Daily Stats', 'opti-behavior' ),
                    'date_column' => 'stat_date'
                ),
                array(
                    'name' => 'optibehavior_errors',
                    'label' => __( 'Errors', 'opti-behavior' ),
                    'date_column' => 'error_time'
                ),
                array(
                    'name' => 'optibehavior_friction',
                    'label' => __( 'Friction Data', 'opti-behavior' ),
                    'date_column' => 'event_time'
                ),
                array(
                    'name' => 'optibehavior_performance',
                    'label' => __( 'Performance Data', 'opti-behavior' ),
                    'date_column' => 'recorded_at'
                ),
                array(
                    'name' => 'optibehavior_broken_links',
                    'label' => __( 'Broken Links', 'opti-behavior' ),
                    'date_column' => 'first_detected'
                ),
                array(
                    'name' => 'optibehavior_form_interactions',
                    'label' => __( 'Form Interactions', 'opti-behavior' ),
                    'date_column' => 'created_at'
                ),
                array(
                    'name' => 'optibehavior_form_submissions',
                    'label' => __( 'Form Submissions', 'opti-behavior' ),
                    'date_column' => 'created_at'
                ),
            );

            $total_tables = count( $tables );
            $total_deleted = 0;

            // Check if we're done
            if ( $step >= $total_tables ) {
                // Get total deleted count from transient
                $total_deleted = get_transient( 'opti_behavior_range_delete_count' );
                delete_transient( 'opti_behavior_range_delete_count' );

                // Delete files within date range from all data directories
                $files_deleted = 0;
                $start_ts = strtotime( $start_date_full );
                $end_ts = strtotime( $end_date_full );

                // Main data directories: heatmaps, recordings, events
                $upload_dir = wp_upload_dir();
                $data_base = $upload_dir['basedir'] . '/opti-behavior-data';
                $data_dirs = array(
                    $data_base . '/heatmaps',
                    $data_base . '/recordings',
                    $data_base . '/events',
                );

                foreach ( $data_dirs as $dir ) {
                    if ( ! is_dir( $dir ) ) {
                        continue;
                    }
                    $iterator = new RecursiveIteratorIterator(
                        new RecursiveDirectoryIterator( $dir, RecursiveDirectoryIterator::SKIP_DOTS ),
                        RecursiveIteratorIterator::CHILD_FIRST
                    );
                    foreach ( $iterator as $item ) {
                        if ( $item->isFile() ) {
                            $file_time = $item->getMTime();
                            if ( $file_time >= $start_ts && $file_time <= $end_ts ) {
                                wp_delete_file( $item->getRealPath() );
                                $files_deleted++;
                            }
                        }
                    }
                }

                // Legacy recordings directory
                $legacy_dir = WP_CONTENT_DIR . '/opti-behavior-recordings';
                if ( is_dir( $legacy_dir ) ) {
                    $files = glob( $legacy_dir . '/*.json' );
                    if ( $files ) {
                        foreach ( $files as $file ) {
                            if ( is_file( $file ) ) {
                                $file_time = filemtime( $file );
                                if ( $file_time >= $start_ts && $file_time <= $end_ts ) {
                                    wp_delete_file( $file );
                                    $files_deleted++;
                                }
                            }
                        }
                    }
                }

                wp_send_json_success( array(
                    'status' => 'completed',
                    'message' => sprintf(
                        /* translators: 1: number of records, 2: number of files, 3: start date, 4: end date */
                        __( 'Successfully deleted %1$d records and %2$d data files (heatmaps, recordings, events) between %3$s and %4$s.', 'opti-behavior' ),
                        $total_deleted ? $total_deleted : 0,
                        $files_deleted,
                        $start_date,
                        $end_date
                    )
                ) );
            }

            // Delete from current table
            $current_table = $tables[ $step ];
            $table_name = $wpdb->prefix . $current_table['name'];
            $date_column = $current_table['date_column'];

            // Check if table and column exist
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $table_exists = $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
                DB_NAME,
                $table_name
            ) );

            $deleted_rows = 0;
            if ( $table_exists ) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
                $deleted_rows = $wpdb->query( $wpdb->prepare(
                    "DELETE FROM " . $table_name . " WHERE " . $date_column . " BETWEEN %s AND %s",
                    $start_date_full,
                    $end_date_full
                ) );

                if ( $deleted_rows === false ) {
                    $deleted_rows = 0;
                }
            }

            // Update running total in transient
            $running_total = get_transient( 'opti_behavior_range_delete_count' );
            $running_total = $running_total ? $running_total : 0;
            $running_total += $deleted_rows;
            set_transient( 'opti_behavior_range_delete_count', $running_total, 3600 );

            $progress = round( ( ( $step + 1 ) / ( $total_tables + 1 ) ) * 100 );

            wp_send_json_success( array(
                'status' => 'processing',
                'step' => $step + 1,
                'total' => $total_tables + 1,
                'progress' => (int) $progress,
                'deleted_rows' => $deleted_rows,
                'current_table' => $current_table['label'],
                'message' => sprintf(
                    /* translators: %s: table label */
                    __( 'Deleting %s...', 'opti-behavior' ),
                    $current_table['label']
                )
            ) );
        }

        // =====================================================================
        // Smart Data Cleanup AJAX Handlers
        // =====================================================================

        /**
         * AJAX handler: Preview count of sessions matching conditions.
         *
         * @since 1.0.9
         */
        public function ajax_smart_cleanup_preview() {
            check_ajax_referer( 'opti_behavior_smart_cleanup', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Sanitized via map_deep and sanitize_cleanup_conditions below.
            $conditions = isset( $_POST['conditions'] ) ? $this->sanitize_cleanup_conditions( map_deep( wp_unslash( $_POST['conditions'] ), 'sanitize_text_field' ) ) : array();

            if ( empty( $conditions ) ) {
                wp_send_json_error( array( 'message' => __( 'No conditions specified.', 'opti-behavior' ) ) );
            }

            $count = $this->count_sessions_by_conditions( $conditions );

            wp_send_json_success( array(
                'count'   => $count,
                'message' => sprintf(
                    /* translators: %d: number of sessions */
                    _n( '%d session matches your conditions.', '%d sessions match your conditions.', $count, 'opti-behavior' ),
                    $count
                ),
            ) );
        }

        /**
         * AJAX handler: Execute batched deletion of sessions matching conditions.
         *
         * @since 1.0.9
         */
        public function ajax_smart_cleanup_execute() {
            check_ajax_referer( 'opti_behavior_smart_cleanup', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Sanitized via map_deep and sanitize_cleanup_conditions below.
            $conditions = isset( $_POST['conditions'] ) ? $this->sanitize_cleanup_conditions( map_deep( wp_unslash( $_POST['conditions'] ), 'sanitize_text_field' ) ) : array();
            $step = isset( $_POST['step'] ) ? absint( $_POST['step'] ) : 0;

            if ( empty( $conditions ) ) {
                wp_send_json_error( array( 'message' => __( 'No conditions specified.', 'opti-behavior' ) ) );
            }

            $result = $this->delete_sessions_by_conditions_impl( $conditions, $step );

            wp_send_json_success( $result );
        }

        /**
         * AJAX handler: Execute batched bot/spam session deletion.
         *
         * @since 1.0.9
         */
        public function ajax_bot_cleanup() {
            check_ajax_referer( 'opti_behavior_smart_cleanup', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            $step = isset( $_POST['step'] ) ? absint( $_POST['step'] ) : 0;

            $result = $this->delete_bot_sessions_impl( $step );

            wp_send_json_success( $result );
        }

        /**
         * AJAX handler: Save auto-cleanup schedule settings.
         *
         * @since 1.0.9
         */
        public function ajax_save_auto_cleanup_settings() {
            check_ajax_referer( 'opti_behavior_smart_cleanup', 'nonce' );

            if ( ! current_user_can( 'manage_options' ) ) {
                wp_send_json_error( array( 'message' => __( 'Unauthorized', 'opti-behavior' ) ) );
            }

            // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- Array values are sanitized individually below.
            $raw_settings = isset( $_POST['settings'] ) ? map_deep( wp_unslash( $_POST['settings'] ), 'sanitize_text_field' ) : array();

            $settings = array(
                'enabled'                  => ! empty( $raw_settings['enabled'] ),
                'frequency'                => isset( $raw_settings['frequency'] ) ? sanitize_text_field( $raw_settings['frequency'] ) : 'weekly',
                'conditions'               => isset( $raw_settings['conditions'] ) ? $this->sanitize_cleanup_conditions( $raw_settings['conditions'] ) : array(),
                'delete_orphaned_visitors' => ! empty( $raw_settings['delete_orphaned_visitors'] ),
                'last_run'                 => null,
            );

            // Validate frequency
            if ( ! in_array( $settings['frequency'], array( 'daily', 'weekly', 'monthly' ), true ) ) {
                $settings['frequency'] = 'weekly';
            }

            // Preserve last_run from existing settings
            $existing = get_option( 'opti_behavior_auto_cleanup_settings', array() );
            if ( ! empty( $existing['last_run'] ) ) {
                $settings['last_run'] = $existing['last_run'];
            }

            update_option( 'opti_behavior_auto_cleanup_settings', $settings );

            // Manage cron schedule
            $hook = 'opti_behavior_scheduled_smart_cleanup';
            $timestamp = wp_next_scheduled( $hook );

            if ( $settings['enabled'] ) {
                // Schedule or reschedule
                if ( $timestamp ) {
                    wp_unschedule_event( $timestamp, $hook );
                }
                $recurrence = $settings['frequency'];
                if ( $recurrence === 'monthly' ) {
                    // WordPress doesn't have monthly by default, use a custom interval
                    $recurrence = 'daily'; // We'll check inside the callback
                }
                wp_schedule_event( time() + HOUR_IN_SECONDS, $recurrence, $hook );
            } else {
                // Unschedule
                if ( $timestamp ) {
                    wp_unschedule_event( $timestamp, $hook );
                }
            }

            wp_send_json_success( array(
                'message' => __( 'Auto-cleanup settings saved successfully.', 'opti-behavior' ),
                'settings' => $settings,
            ) );
        }

        /**
         * Sanitize cleanup conditions array.
         *
         * @since 1.0.9
         * @param array|string $raw Raw conditions data.
         * @return array Sanitized conditions.
         */
        private function sanitize_cleanup_conditions( $raw ) {
            if ( is_string( $raw ) ) {
                $raw = json_decode( $raw, true );
            }
            if ( ! is_array( $raw ) ) {
                return array();
            }

            $sanitized = array();

            $int_keys = array(
                'older_than_days', 'inactive_visitor_days',
                'min_duration', 'min_duration_age_days',
                'max_duration', 'max_duration_age_days',
                'min_events', 'min_events_age_days',
                'max_events',
                'min_page_views',
                'bounce_age_days',
                'single_visit_age_days',
            );

            $bool_keys = array(
                'bounce_only', 'include_bots', 'single_visit_only', 'delete_orphaned_visitors',
            );

            foreach ( $int_keys as $key ) {
                if ( isset( $raw[ $key ] ) ) {
                    $sanitized[ $key ] = absint( $raw[ $key ] );
                }
            }

            foreach ( $bool_keys as $key ) {
                if ( isset( $raw[ $key ] ) ) {
                    $sanitized[ $key ] = filter_var( $raw[ $key ], FILTER_VALIDATE_BOOLEAN );
                }
            }

            return $sanitized;
        }

		/**
		 * AJAX handler: Start Pro free trial.
		 *
		 * Saves trial start timestamp to WP options and attempts to notify the API.
		 * Trial activates locally even if API is unreachable (graceful degradation).
		 *
		 * @since 1.1.3
		 */
		private function ajax_start_pro_trial_impl() {
			// Verify nonce
			check_ajax_referer( 'opti_behavior_start_trial', 'nonce' );

			// Verify capabilities
			if ( ! current_user_can( 'manage_options' ) ) {
				wp_send_json_error( array( 'message' => __( 'Insufficient permissions.', 'opti-behavior' ) ) );
			}

			// Check if trial already started
			$trial_started = get_option( 'opti_behavior_pro_trial_started', 0 );
			if ( $trial_started > 0 ) {
				$expires   = $trial_started + ( 180 * DAY_IN_SECONDS );
				$days_left = max( 0, (int) ceil( ( $expires - time() ) / DAY_IN_SECONDS ) );
				wp_send_json_success( array(
					'message'      => __( 'Your Pro trial is already active!', 'opti-behavior' ),
					'days_left'    => $days_left,
					'download_url' => $this->get_pro_download_url(),
				) );
			}

			// Activate trial locally
			$now = time();
			update_option( 'opti_behavior_pro_trial_started', $now );
			delete_option( 'opti_behavior_pro_trial_banner_dismissed' );

			// Calculate expiry
			$expires   = $now + ( 180 * DAY_IN_SECONDS );
			$days_left = 180;

			// Attempt to notify the API (non-blocking, informational)
			$this->notify_api_trial_started();

			wp_send_json_success( array(
				'message'      => __( 'Pro trial activated! You have 6 months of free access to all Pro features.', 'opti-behavior' ),
				'days_left'    => $days_left,
				'download_url' => $this->get_pro_download_url(),
			) );
		}

		/**
		 * AJAX handler: Dismiss trial banner.
		 *
		 * Stores a dismiss timestamp. Banner will re-appear after 30 days.
		 *
		 * @since 1.1.3
		 */
		private function ajax_dismiss_trial_banner_impl() {
			check_ajax_referer( 'opti_behavior_dismiss_trial', 'nonce' );

			if ( ! current_user_can( 'manage_options' ) ) {
				wp_send_json_error( array( 'message' => __( 'Insufficient permissions.', 'opti-behavior' ) ) );
			}

			update_option( 'opti_behavior_pro_trial_banner_dismissed', time() );
			wp_send_json_success( array( 'message' => 'Banner dismissed' ) );
		}

		/**
		 * Get Pro download URL with access code.
		 *
		 * Mirrors the logic in recordings-upgrade-page.php — requests a one-time
		 * access code from the API so the download page can verify the visitor.
		 *
		 * @since 1.1.3
		 * @return string Download URL.
		 */
		private function get_pro_download_url() {
			$current_user = wp_get_current_user();
			$cache_key    = 'ob_dl_access_' . md5( $current_user->user_login . site_url() );
			$access_code  = get_transient( $cache_key );

			if ( ! $access_code ) {
				$api_response = wp_remote_post(
					'https://api.optiuser.com/request-download-access',
					array(
						'body'    => wp_json_encode( array(
							'site_url' => site_url(),
							'username' => $current_user->user_login,
							'email'    => $current_user->user_email,
						) ),
						'headers' => array( 'Content-Type' => 'application/json' ),
						'timeout' => 10,
					)
				);
				if ( ! is_wp_error( $api_response ) ) {
					$api_body = json_decode( wp_remote_retrieve_body( $api_response ), true );
					if ( ! empty( $api_body['data']['access_code'] ) ) {
						$access_code = $api_body['data']['access_code'];
						set_transient( $cache_key, $access_code, 10 * MINUTE_IN_SECONDS );
					}
				}
			}

			return add_query_arg(
				array(
					'site_url'    => rawurlencode( site_url() ),
					'username'    => rawurlencode( $current_user->user_login ),
					'email'       => rawurlencode( $current_user->user_email ),
					'access_code' => rawurlencode( $access_code ? $access_code : '' ),
				),
				'https://optiuser.com/opti-behavior/ob-download-pro/'
			);
		}

		/**
		 * Notify the API that a trial was started (non-blocking, informational).
		 *
		 * @since 1.1.3
		 */
		private function notify_api_trial_started() {
			$current_user = wp_get_current_user();

			wp_remote_post(
				'https://api.optiuser.com/trial-started',
				array(
					'body'     => wp_json_encode( array(
						'site_url' => site_url(),
						'domain'   => wp_parse_url( home_url(), PHP_URL_HOST ),
						'email'    => get_option( 'admin_email' ),
						'username' => $current_user->user_login,
						'plugin'   => 'opti-behavior',
						'version'  => OPTI_BEHAVIOR_HEATMAP_VERSION,
					) ),
					'headers'  => array( 'Content-Type' => 'application/json' ),
					'timeout'  => 5,
					'blocking' => false,
				)
			);
		}

    }
}

