<?php
/**
 * Data Helpers Trait
 *
 * Groups data helper methods extracted from the dashboard class.
 *
 * @package opti-behavior
 * @since 1.0.0
 */

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

// 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_Data_Helpers_Trait' ) ) {
	/**
	 * Data Helpers Trait
	 *
	 * Provides data retrieval and processing methods for analytics dashboard.
	 *
	 * @since 1.0.0
	 */
	trait Opti_Behavior_Data_Helpers_Trait {

		/**
		 * Get the SQL clause to exclude spam traffic.
		 *
		 * THRESHOLD-BASED: In addition to checking traffic_type, also filters by
		 * duration threshold so that unclassified sessions (heartbeat never fired for
		 * short visits) are correctly excluded even when traffic_type = 'human' (default).
		 *
		 * @since 1.0.5
		 * @param string $table_alias Optional table alias (e.g., 's' for sessions table).
		 * @return string SQL clause like "AND (...)" or empty string.
		 */
		private function get_spam_exclusion_clause( $table_alias = '' ) {
			$exclude_spam = isset( $GLOBALS['opti_behavior_exclude_spam'] ) && $GLOBALS['opti_behavior_exclude_spam'];
			if ( ! $exclude_spam ) {
				return '';
			}
			$prefix = $table_alias ? $table_alias . '.' : '';

			$traffic_settings = get_option( 'opti_behavior_traffic_settings', array(
				'spam_detection_enabled'    => true,
				'spam_duration_threshold'   => 10,
				'spam_min_clicks_threshold' => 1,
			) );
			$spam_detection_enabled = ! empty( $traffic_settings['spam_detection_enabled'] );

			if ( $spam_detection_enabled ) {
				// Threshold-based: exclude sessions classified as spam OR with duration below
				// the configured threshold (catches unclassified spam with default traffic_type).
				// Note: click_count lives in the recordings table; only duration is checked here.
				$spam_duration = intval( $traffic_settings['spam_duration_threshold'] ?? 10 );
				return " AND (" . $prefix . "traffic_type IS NULL OR (" . $prefix . "traffic_type != 'spam' AND " . $prefix . "duration >= " . $spam_duration . "))";
			}

			// Spam detection disabled — only exclude sessions explicitly classified as spam.
			return " AND (" . $prefix . "traffic_type IS NULL OR " . $prefix . "traffic_type != 'spam')";
		}

		/**
		 * Check if spam exclusion is enabled.
		 *
		 * @since 1.0.5
		 * @return bool True if spam should be excluded.
		 */
		private function is_spam_excluded() {
			return isset( $GLOBALS['opti_behavior_exclude_spam'] ) && $GLOBALS['opti_behavior_exclude_spam'];
		}

		/**
		 * Get dashboard data for specified period.
		 *
		 * @since 1.0.0
		 * @global wpdb $wpdb WordPress database abstraction object.
		 * @param string      $period         Period identifier.
		 * @param string|null $start_override Optional start date override.
		 * @param string|null $end_override   Optional end date override.
		 * @return array Dashboard data including stats, charts, and realtime data.
		 */
		private function get_dashboard_data_impl( $period, $start_override = null, $end_override = null ) {
			global $wpdb;

			if ( 'custom' === $period && $start_override && $end_override ) {
				$start_date = sanitize_text_field( $start_override ) . ' 00:00:00';
				$end_date   = sanitize_text_field( $end_override ) . ' 23:59:59';
				$date_range = array(
					'start'      => $start_date,
					'end'        => $end_date,
					'start_date' => new DateTime( $start_date, wp_timezone() ),
					'end_date'   => new DateTime( $end_date, wp_timezone() ),
				);
			} else {
				$date_range = $this->get_date_range( $period );
				$start_date = $date_range['start'];
				$end_date   = $date_range['end'];
			}

			$stats = array(
				'sessions'         => $this->get_sessions_count( $start_date, $end_date ),
				'visitors'         => $this->get_visitors_count( $start_date, $end_date ),
				'pageviews'        => $this->get_pageviews_count( $start_date, $end_date ),
				'avg_session_time' => $this->get_avg_session_time( $start_date, $end_date ),
				'avg_scroll_depth' => $this->get_avg_scroll_depth( $start_date, $end_date ),
				'bounce_rate'      => $this->get_bounce_rate( $start_date, $end_date ),
			);

			$charts = array(
				'sessions_chart'     => $this->get_sessions_chart_data( $start_date, $end_date ),
				'top_pages'          => $this->get_top_pages_data( $start_date, $end_date ),
				'countries'          => $this->get_countries_data( $start_date, $end_date ),
				'browsers'           => $this->get_browsers_data( $start_date, $end_date ),
				'device_types'       => $this->get_device_types_data( $start_date, $end_date ),
				'operating_systems'  => $this->get_operating_systems_data( $start_date, $end_date ),
				'screen_resolutions' => $this->get_screen_resolution_data( $start_date, $end_date ),
				'referrers'          => $this->get_referrers_data( $start_date, $end_date ),
			);

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

			$duration   = max( 1, strtotime( $end_date ) - strtotime( $start_date ) + 1 );
			$prev_end   = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - 1 );
			$prev_start = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - $duration );
			$prev_stats = array(
				'sessions'         => $this->get_sessions_count( $prev_start, $prev_end ),
				'visitors'         => $this->get_visitors_count( $prev_start, $prev_end ),
				'pageviews'        => $this->get_pageviews_count( $prev_start, $prev_end ),
				'avg_session_time' => $this->get_avg_session_time( $prev_start, $prev_end ),
				'avg_scroll_depth' => $this->get_avg_scroll_depth( $prev_start, $prev_end ),
				'bounce_rate'      => $this->get_bounce_rate( $prev_start, $prev_end ),
			);

			$pct = function ( $cur, $prev ) {
				if ( $prev <= 0 ) {
					return $cur > 0 ? 100 : 0;
				}
				return ( ( $cur - $prev ) / $prev ) * 100;
			};

			$changes = array(
				'visitors'         => $pct( $stats['visitors'], $prev_stats['visitors'] ),
				'sessions'         => $pct( $stats['sessions'], $prev_stats['sessions'] ),
				'pageviews'        => $pct( $stats['pageviews'], $prev_stats['pageviews'] ),
				'avg_session_time' => $pct( $stats['avg_session_time'], $prev_stats['avg_session_time'] ),
				'avg_scroll_depth' => $pct( $stats['avg_scroll_depth'], $prev_stats['avg_scroll_depth'] ),
				'bounce_rate'      => $pct( $stats['bounce_rate'], $prev_stats['bounce_rate'] ),
			);

			foreach ( $changes as $k => $v ) {
				$changes[ $k ] = round( (float) $v );
			}

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

			// Get traffic classification data
			$traffic_classification = method_exists( $this, 'get_traffic_classification_data_impl' ) ? $this->get_traffic_classification_data_impl( $start_date, $end_date ) : array();

			// Get bot traffic data
			$bot_traffic = method_exists( $this, 'get_bot_traffic_data_impl' ) ? $this->get_bot_traffic_data_impl( $start_date, $end_date ) : array();

			// Get new vs returning visitors data
			$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();

			// Get visited directories data
			$visited_directories = method_exists( $this, 'get_visited_directories_data_impl' ) ? $this->get_visited_directories_data_impl( $start_date, $end_date ) : array();

			// Get new registered users data
			$new_registered_users = method_exists( $this, 'get_new_registered_users_data_impl' ) ? $this->get_new_registered_users_data_impl( $start_date, $end_date ) : array();

			return array(
				'stats'                  => $stats,
				'charts'                 => $charts,
				'realtime'               => $realtime,
				'user_intent'            => $user_intent,
				'traffic_classification' => $traffic_classification,
				'bot_traffic'            => $bot_traffic,
				'new_vs_returning'       => $new_vs_returning,
				'visited_directories'    => $visited_directories,
				'new_registered_users'   => $new_registered_users,
				'period'                 => $period,
				'date_range'             => $date_range,
				'changes'                => $changes,
			);
		}

		/**
		 * Get only stats and date range for fast initial page load.
		 * Widget data will be loaded asynchronously via AJAX.
		 *
		 * @since 3.0.7
		 * @param string      $period        Period identifier.
		 * @param string|null $start_override Custom start date.
		 * @param string|null $end_override   Custom end date.
		 * @return array Minimal dashboard data with stats only.
		 */
		private function get_dashboard_stats_only_impl( $period, $start_override = null, $end_override = null ) {
			global $wpdb;

			if ( 'custom' === $period && $start_override && $end_override ) {
				$start_date = sanitize_text_field( $start_override ) . ' 00:00:00';
				$end_date   = sanitize_text_field( $end_override ) . ' 23:59:59';
				$date_range = array(
					'start'      => $start_date,
					'end'        => $end_date,
					'start_date' => new DateTime( $start_date, wp_timezone() ),
					'end_date'   => new DateTime( $end_date, wp_timezone() ),
				);
			} else {
				$date_range = $this->get_date_range( $period );
				$start_date = $date_range['start'];
				$end_date   = $date_range['end'];
			}

			$stats = array(
				'sessions'         => $this->get_sessions_count( $start_date, $end_date ),
				'visitors'         => $this->get_visitors_count( $start_date, $end_date ),
				'pageviews'        => $this->get_pageviews_count( $start_date, $end_date ),
				'avg_session_time' => $this->get_avg_session_time( $start_date, $end_date ),
				'avg_scroll_depth' => $this->get_avg_scroll_depth( $start_date, $end_date ),
				'bounce_rate'      => $this->get_bounce_rate( $start_date, $end_date ),
			);

			$duration   = max( 1, strtotime( $end_date ) - strtotime( $start_date ) + 1 );
			$prev_end   = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - 1 );
			$prev_start = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - $duration );
			$prev_stats = array(
				'sessions'         => $this->get_sessions_count( $prev_start, $prev_end ),
				'visitors'         => $this->get_visitors_count( $prev_start, $prev_end ),
				'pageviews'        => $this->get_pageviews_count( $prev_start, $prev_end ),
				'avg_session_time' => $this->get_avg_session_time( $prev_start, $prev_end ),
				'avg_scroll_depth' => $this->get_avg_scroll_depth( $prev_start, $prev_end ),
				'bounce_rate'      => $this->get_bounce_rate( $prev_start, $prev_end ),
			);

			$pct = function ( $cur, $prev ) {
				if ( $prev <= 0 ) {
					return $cur > 0 ? 100 : 0;
				}
				return ( ( $cur - $prev ) / $prev ) * 100;
			};

			$changes = array(
				'visitors'         => $pct( $stats['visitors'], $prev_stats['visitors'] ),
				'sessions'         => $pct( $stats['sessions'], $prev_stats['sessions'] ),
				'pageviews'        => $pct( $stats['pageviews'], $prev_stats['pageviews'] ),
				'avg_session_time' => $pct( $stats['avg_session_time'], $prev_stats['avg_session_time'] ),
				'avg_scroll_depth' => $pct( $stats['avg_scroll_depth'], $prev_stats['avg_scroll_depth'] ),
				'bounce_rate'      => $pct( $stats['bounce_rate'], $prev_stats['bounce_rate'] ),
			);

			foreach ( $changes as $k => $v ) {
				$changes[ $k ] = round( (float) $v );
			}

			// Skip slow queries in stats-only mode - they will be loaded via AJAX
			// Get daily history data for stats cards (fast - uses pre-aggregated data)
			$daily_history = $this->get_daily_stats_history( $start_date, $end_date );

			return array(
				'stats'      => $stats,
				'period'     => $period,
				'date_range' => $date_range,
				'changes'    => $changes,
				'daily_history' => $daily_history,
				// Empty arrays/structures for widgets - will be loaded async
				'charts'                 => array(
					'sessions_chart'     => array(),
					'top_pages'          => array(),
					'countries'          => array(),
					'browsers'           => array(),
					'device_types'       => array(),
					'operating_systems'  => array(),
					'screen_resolutions' => array(),
					'referrers'          => array(),
				),
				'realtime'               => array(
					'active_visitors' => array(),
					'recent_sessions' => array(),
				),
				'user_intent'            => array(),
				'traffic_classification' => array(),
				'bot_traffic'            => array(),
				'new_vs_returning'       => array(),
				'visited_directories'    => array(),
				'new_registered_users'   => array(),
			);
		}

		/**
		 * Get summary stats data for the stats cards (loaded via AJAX for fast page render).
		 *
		 * OPTIMIZED: Reduced from 9 queries to 3 queries by combining aggregations.
		 *
		 * @since 1.0.8.42
		 * @param string $start_date Start date.
		 * @param string $end_date   End date.
		 * @return array Summary stats with current and previous period comparison.
		 */
		private function get_summary_stats_data( $start_date, $end_date ) {
			global $wpdb;

			// Check cache first (short TTL; filterable via opti_behavior_dashboard_cache_ttl).
			// Before 1.2.9 this was 15 minutes with no invalidation, causing stat cards
			// to stay stale for up to 15 min after new visitor activity.
			$exclude_spam = $this->is_spam_excluded();
			$cache_key = 'opti_behavior_summary_stats_' . md5( $start_date . $end_date . ( $exclude_spam ? '1' : '0' ) );

			// Honour a force-refresh request (Refresh button) by bypassing the cache.
			if ( function_exists( 'opti_behavior_is_force_refresh_requested' ) && opti_behavior_is_force_refresh_requested() ) {
				delete_transient( $cache_key );
			} else {
				$cached = get_transient( $cache_key );
				if ( false !== $cached ) {
					return $cached;
				}
			}

			// Calculate previous period dates
			$duration   = max( 1, strtotime( $end_date ) - strtotime( $start_date ) + 1 );
			$prev_end   = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - 1 );
			$prev_start = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - $duration );

			// Build spam exclusion clause using threshold-based helper for consistency.
			$spam_clause = $this->get_spam_exclusion_clause();

			$table = $wpdb->prefix . "optibehavior_sessions";

			// OPTIMIZED: Single combined query for ALL current period stats
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$current_stats = $wpdb->get_row( $wpdb->prepare(
				"SELECT
					COUNT(*) as sessions,
					COUNT(DISTINCT visitor_id) as visitors,
					COALESCE(SUM(page_views), 0) as pageviews,
					COALESCE(AVG(CASE WHEN duration > 0 THEN duration END), 0) as avg_duration,
					SUM(CASE WHEN is_bounce = 1 THEN 1 ELSE 0 END) as bounces
				FROM " . $table . "
				WHERE start_time BETWEEN %s AND %s" . $spam_clause,
				$start_date, $end_date
			) );

			$sessions = (int) $current_stats->sessions;
			$visitors = (int) $current_stats->visitors;
			$pageviews = (int) $current_stats->pageviews;
			$avg_duration = (float) $current_stats->avg_duration;
			$bounces = (int) $current_stats->bounces;
			$bounce_rate = $sessions > 0 ? ( $bounces / $sessions ) * 100 : 0;

			// OPTIMIZED: Single combined query for ALL previous period stats
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$prev_stats_row = $wpdb->get_row( $wpdb->prepare(
				"SELECT
					COUNT(*) as sessions,
					COUNT(DISTINCT visitor_id) as visitors,
					COALESCE(SUM(page_views), 0) as pageviews
				FROM " . $table . "
				WHERE start_time BETWEEN %s AND %s" . $spam_clause,
				$prev_start, $prev_end
			) );

			$prev_sessions = (int) $prev_stats_row->sessions;
			$prev_visitors = (int) $prev_stats_row->visitors;
			$prev_pageviews = (int) $prev_stats_row->pageviews;

			// Scroll depth - sampled query (separate table)
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$avg_scroll = (float) $wpdb->get_var( $wpdb->prepare(
				"SELECT COALESCE(AVG(scroll_depth), 0) FROM (
					SELECT scroll_depth FROM " . $wpdb->prefix . "optibehavior_pageviews
					WHERE view_time BETWEEN %s AND %s AND scroll_depth > 0
					LIMIT 10000
				) as sample",
				$start_date, $end_date
			) );

			// Build stats arrays
			$stats = array(
				'sessions'         => $sessions,
				'visitors'         => $visitors,
				'pageviews'        => $pageviews,
				'avg_session_time' => $avg_duration,
				'avg_scroll_depth' => $avg_scroll,
				'bounce_rate'      => $bounce_rate,
			);

			$prev_stats = array(
				'sessions'         => $prev_sessions,
				'visitors'         => $prev_visitors,
				'pageviews'        => $prev_pageviews,
				'avg_session_time' => $avg_duration,
				'avg_scroll_depth' => $avg_scroll,
				'bounce_rate'      => $bounce_rate,
			);

			// Calculate percentage changes
			$pct = function ( $cur, $prev ) {
				if ( $prev <= 0 ) {
					return $cur > 0 ? 100 : 0;
				}
				return ( ( $cur - $prev ) / $prev ) * 100;
			};

			$changes = array(
				'visitors'         => round( $pct( $stats['visitors'], $prev_stats['visitors'] ) ),
				'sessions'         => round( $pct( $stats['sessions'], $prev_stats['sessions'] ) ),
				'pageviews'        => round( $pct( $stats['pageviews'], $prev_stats['pageviews'] ) ),
				'avg_session_time' => round( $pct( $stats['avg_session_time'], $prev_stats['avg_session_time'] ) ),
				'avg_scroll_depth' => round( $pct( $stats['avg_scroll_depth'], $prev_stats['avg_scroll_depth'] ) ),
				'bounce_rate'      => round( $pct( $stats['bounce_rate'], $prev_stats['bounce_rate'] ) ),
			);

			// Get daily history data for stat card mini-charts
			$daily_history = $this->get_daily_stats_history( $start_date, $end_date );

			$result = array(
				'stats'         => $stats,
				'changes'       => $changes,
				'daily_history' => $daily_history,
			);

			// Cache for a short TTL (default 30 s, filterable). Invalidated on session insert/update.
			$ttl = function_exists( 'opti_behavior_dashboard_cache_ttl' ) ? opti_behavior_dashboard_cache_ttl() : 30;
			set_transient( $cache_key, $result, $ttl );

			return $result;
		}

		/**
		 * Get top engaged users data.
		 *
		 * @since 1.0.8.42
		 * @param string $start_date Start date.
		 * @param string $end_date   End date.
		 * @return array Top engaged users.
		 */
		private function get_top_engaged_users_data( $start_date, $end_date ) {
			global $wpdb;

			// Check cache first (short TTL; filterable via opti_behavior_dashboard_cache_ttl).
			$exclude_spam = $this->is_spam_excluded();
			$cache_key = 'opti_behavior_top_users_' . md5( $start_date . $end_date . ( $exclude_spam ? '1' : '0' ) );

			// Honour a force-refresh request (Refresh button) by bypassing the cache.
			if ( function_exists( 'opti_behavior_is_force_refresh_requested' ) && opti_behavior_is_force_refresh_requested() ) {
				delete_transient( $cache_key );
			} else {
				$cached = get_transient( $cache_key );
				if ( false !== $cached ) {
					return $cached;
				}
			}

			$spam_clause = $this->get_spam_exclusion_clause( 's' );

			// Get top 10 users by engagement score (total_duration + sessions bonus)
			// Only include visitors with average session duration >= 60 seconds to ensure quality engagement
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$results = $wpdb->get_results( $wpdb->prepare(
				"SELECT
					s.visitor_id,
					COUNT(DISTINCT s.id) as total_sessions,
					SUM(s.duration) as total_duration,
					AVG(s.duration) as avg_duration,
					SUM(s.page_views) as total_pageviews,
					MAX(s.start_time) as last_visit
				FROM " . $wpdb->prefix . "optibehavior_sessions s
				WHERE s.start_time BETWEEN %s AND %s" . $spam_clause . "
				  AND s.duration > 0
				GROUP BY s.visitor_id
				HAVING AVG(s.duration) >= 60
				ORDER BY (total_duration + (total_sessions * 60)) DESC
				LIMIT 10",
				$start_date,
				$end_date
			) );

			$data = array();
			foreach ( $results as $row ) {
				$data[] = array(
					'visitor_id'     => $row->visitor_id,
					'sessions'       => intval( $row->total_sessions ),
					'total_duration' => intval( $row->total_duration ),
					'avg_duration'   => round( floatval( $row->avg_duration ) ),
					'pageviews'      => intval( $row->total_pageviews ),
					'last_visit'     => $row->last_visit,
				);
			}

			// Cache for a short TTL (default 30 s, filterable). Invalidated on session insert/update.
			$ttl = function_exists( 'opti_behavior_dashboard_cache_ttl' ) ? opti_behavior_dashboard_cache_ttl() : 30;
			set_transient( $cache_key, $data, $ttl );

			return $data;
		}

		/**
		 * Get session data with pagination and filters.
		 *
		 * @since 1.0.0
		 * @global wpdb $wpdb WordPress database abstraction object.
		 * @param int   $page     Page number.
		 * @param int   $per_page Items per page.
		 * @param array $filters  Filter criteria.
		 * @return array Session data with pagination info.
		 */
		private function get_session_data_impl( $page, $per_page, $filters ) {
			global $wpdb;

			$offset = ( $page - 1 ) * $per_page;

			// Ensure offset and per_page are positive integers
			$offset = absint( $offset );
			$per_page = absint( $per_page );

			$prepare_args  = array();
			$has_date_from = ! empty( $filters['date_from'] );
			$has_date_to   = ! empty( $filters['date_to'] );
			$has_device    = ! empty( $filters['device_type'] );
			$has_country   = ! empty( $filters['country'] );

			$where_conditions = array();
			if ( $has_date_from ) {
				$where_conditions[] = 's.start_time >= %s';
				$prepare_args[]     = $filters['date_from'];
			}
			if ( $has_date_to ) {
				$where_conditions[] = 's.start_time <= %s';
				$prepare_args[]     = $filters['date_to'];
			}
			if ( $has_device ) {
				$where_conditions[] = 'v.device_type = %s';
				$prepare_args[]     = $filters['device_type'];
			}
			if ( $has_country ) {
				$where_conditions[] = 'v.country = %s';
				$prepare_args[]     = $filters['country'];
			}

			$where_clause = ! empty( $where_conditions ) ? implode( ' AND ', $where_conditions ) : '1=1';
			// WHERE clause is safe: built from validated placeholders only (%s), actual values are in $prepare_args

			$prepare_args[] = $per_page;
			$prepare_args[] = $offset;

			$sessions_table = esc_sql( $wpdb->prefix . 'optibehavior_sessions' );
			$visitors_table = esc_sql( $wpdb->prefix . 'optibehavior_visitors' );

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names are esc_sql escaped, WHERE clause contains only %s placeholders (values in prepare_args)
			$sql = "SELECT
				s.id,
				s.start_time,
				s.end_time,
				s.duration,
				s.page_views,
				s.events_count,
				s.is_bounce,
				s.entry_page,
				s.exit_page,
				v.device_type,
				v.browser,
				v.os,
				v.country,
				v.country_name,
				v.city
			FROM " . $sessions_table . " s
			LEFT JOIN " . $visitors_table . " v ON s.visitor_id = v.id
			WHERE " . $where_clause . "
			ORDER BY s.start_time DESC
			LIMIT %d OFFSET %d";

			// Prepare and execute the query with dynamic number of parameters
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause built from validated placeholders, table names are esc_sql escaped, all user inputs properly parameterized
			$sessions = $wpdb->get_results( $wpdb->prepare( $sql, ...$prepare_args ) );

			$count_prepare_args = array_slice( $prepare_args, 0, -2 );

			if ( empty( $count_prepare_args ) ) {
				$total = $wpdb->get_var(
					"SELECT COUNT(*)
					FROM " . $sessions_table . " s
					LEFT JOIN " . $visitors_table . " v ON s.visitor_id = v.id
					WHERE 1=1"
				);
			} else {
				$count_sql = "SELECT COUNT(*)
				FROM " . $sessions_table . " s
				LEFT JOIN " . $visitors_table . " v ON s.visitor_id = v.id
				WHERE " . $where_clause;

				// Prepare and execute the count query with dynamic number of parameters
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- WHERE clause built from validated placeholders, table names are esc_sql escaped, all user inputs properly parameterized
				$total = $wpdb->get_var( $wpdb->prepare( $count_sql, ...$count_prepare_args ) );
			}

			return array(
				'sessions'    => $sessions,
				'total'       => intval( $total ),
				'page'        => $page,
				'per_page'    => $per_page,
				'total_pages' => ceil( $total / $per_page ),
			);
		}

		/**
		 * Get analytics data by type and period.
		 *
		 * @since 1.0.0
		 * @param string $type   Analytics type.
		 * @param string $period Period identifier.
		 * @return array Analytics data.
		 */
		private function get_analytics_data_impl( $type, $period ) {
			$date_range = $this->get_date_range( $period );

			switch ( $type ) {
				case 'funnel':
					return $this->get_funnel_data( $date_range );
				case 'user_journey':
					return $this->get_user_journey_data( $date_range );
				case 'conversion':
					return $this->get_conversion_data( $date_range );
				case 'retention':
					return $this->get_retention_data( $date_range );
				default:
					return $this->get_overview_analytics( $date_range );
			}
		}

		/**
		 * Get date range for specified period.
		 *
		 * @since 1.0.0
		 * @param string $period Period identifier.
		 * @return array Date range with start/end dates.
		 */
		private function get_date_range_impl( $period ) {
			$now = current_time( 'mysql' );

			switch ( $period ) {
				case 'today':
					$start = gmdate( 'Y-m-d 00:00:00', strtotime( $now ) );
					$end   = gmdate( 'Y-m-d 23:59:59', strtotime( $now ) );
					break;

				case 'yesterday':
					$start = gmdate( 'Y-m-d 00:00:00', strtotime( '-1 day', strtotime( $now ) ) );
					$end   = gmdate( 'Y-m-d 23:59:59', strtotime( '-1 day', strtotime( $now ) ) );
					break;

				case 'last7days':
					$start = gmdate( 'Y-m-d 00:00:00', strtotime( '-7 days', strtotime( $now ) ) );
					$end   = gmdate( 'Y-m-d 23:59:59', strtotime( $now ) );
					break;

				case 'last30days':
					$start = gmdate( 'Y-m-d 00:00:00', strtotime( '-30 days', strtotime( $now ) ) );
					$end   = gmdate( 'Y-m-d 23:59:59', strtotime( $now ) );
					break;

				case 'thismonth':
					$start = gmdate( 'Y-m-01 00:00:00', strtotime( $now ) );
					$end   = gmdate( 'Y-m-d 23:59:59', strtotime( $now ) );
					break;

				case 'lastmonth':
					$start = gmdate( 'Y-m-01 00:00:00', strtotime( 'first day of last month', strtotime( $now ) ) );
					$end   = gmdate( 'Y-m-t 23:59:59', strtotime( 'last day of last month', strtotime( $now ) ) );
					break;

				default:
					$start = gmdate( 'Y-m-d 00:00:00', strtotime( '-7 days', strtotime( $now ) ) );
					$end   = gmdate( 'Y-m-d 23:59:59', strtotime( $now ) );
			}

			// Check if the requested start date is before the first available data
			// If so, adjust the start date to the first available data to avoid empty charts
			$first_data_date = $this->get_first_available_data_date_impl();

			if ( $first_data_date && strtotime( $start ) < strtotime( $first_data_date ) ) {
				// Only adjust for periods that show historical data (not fixed periods like 'yesterday' or 'lastmonth')
				$adjustable_periods = array( 'last7days', 'last30days', 'today', 'thismonth' );

				if ( in_array( $period, $adjustable_periods, true ) ) {
					$start = gmdate( 'Y-m-d 00:00:00', strtotime( $first_data_date ) );
				}
			}

			$timezone = wp_timezone();
			$start_dt = new DateTime( $start, $timezone );
			$end_dt   = new DateTime( $end, $timezone );

			return array(
				'start'      => $start,
				'end'        => $end,
				'start_date' => $start_dt,
				'end_date'   => $end_dt,
			);
		}

		/**
		 * Get the first available data date from the database.
		 * Checks both sessions and pageviews tables to find the earliest data.
		 *
		 * @since 1.0.0
		 * @global wpdb $wpdb WordPress database abstraction object.
		 * @return string|null The first available date in 'Y-m-d H:i:s' format, or null if no data exists.
		 */
		private function get_first_available_data_date_impl() {
			global $wpdb;

			// Get the earliest session start_time
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$first_session = $wpdb->get_var(
				"SELECT MIN(start_time) FROM " . $wpdb->prefix . "optibehavior_sessions"
			);

			// Get the earliest pageview view_time
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$first_pageview = $wpdb->get_var(
				"SELECT MIN(view_time) FROM " . $wpdb->prefix . "optibehavior_pageviews"
			);

			// Find the earliest date between the two
			$dates = array_filter( array( $first_session, $first_pageview ) );

			if ( empty( $dates ) ) {
				return null;
			}

			return min( $dates );
		}

		/**
		 * Batch get session counts by page IDs.
		 *
		 * @since 1.0.0
		 * @global wpdb $wpdb WordPress database abstraction object.
		 * @param array       $page_ids   Array of page IDs.
		 * @param string|null $start_date Optional start date.
		 * @param string|null $end_date   Optional end date.
		 * @return array Map of page ID to session count.
		 */
		private function batch_sessions_counts_by_page_impl( $page_ids, $start_date = null, $end_date = null ) {
			global $wpdb;

			if ( empty( $page_ids ) ) {
				return array();
			}

			// Use recordings table to match device split counts (consistent data source)
			$recordings_table     = esc_sql( $wpdb->prefix . 'optibehavior_recordings' );
			$session_pages_table  = esc_sql( $wpdb->prefix . 'optibehavior_session_pages' );
			$placeholders = implode( ',', array_fill( 0, count( $page_ids ), '%d' ) );
			$prepare_args = $page_ids;

			// Check if recordings table exists
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$table_exists = $wpdb->get_var( "SHOW TABLES LIKE '" . $recordings_table . "'" ) === $recordings_table;

			if ( ! $table_exists ) {
				// Fallback to pageviews if recordings table doesn't exist (free version)
				$pv_table = esc_sql( $wpdb->prefix . 'optibehavior_pageviews' );
				if ( $start_date && $end_date ) {
					$prepare_args[] = $start_date;
					$prepare_args[] = $end_date;
					$sql = "SELECT pv.page_id, COUNT(DISTINCT pv.session_id) c
					FROM " . $pv_table . " pv
					WHERE pv.page_id IN ($placeholders)
					AND pv.view_time BETWEEN %s AND %s
					GROUP BY pv.page_id";
				} else {
					$sql = "SELECT pv.page_id, COUNT(DISTINCT pv.session_id) c
					FROM " . $pv_table . " pv
					WHERE pv.page_id IN ($placeholders)
					GROUP BY pv.page_id";
				}
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
				$rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$prepare_args ) );
			} else {
				// Use recordings table for consistent counting with device split
				if ( $start_date && $end_date ) {
					$prepare_args[] = $start_date;
					$prepare_args[] = $end_date;

					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$sql = "SELECT sp.page_id, COUNT(DISTINCT r.session_id) c
					FROM " . $recordings_table . " r
					INNER JOIN " . $session_pages_table . " sp ON sp.session_id = r.session_id
					WHERE sp.page_id IN ($placeholders)
					AND r.start_time BETWEEN %s AND %s
					GROUP BY sp.page_id";

					// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
					$rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$prepare_args ) );
				} else {
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$sql = "SELECT sp.page_id, COUNT(DISTINCT r.session_id) c
					FROM " . $recordings_table . " r
					INNER JOIN " . $session_pages_table . " sp ON sp.session_id = r.session_id
					WHERE sp.page_id IN ($placeholders)
					GROUP BY sp.page_id";

					// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
					$rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$prepare_args ) );
				}
			}

			$map = array();
			foreach ( $rows as $r ) {
				$map[ (int) $r->page_id ] = (int) $r->c;
			}
			return $map;
		}

		/**
		 * Get traffic classification data
		 *
		 * @param string $start_date Start date (Y-m-d H:i:s).
		 * @param string $end_date End date (Y-m-d H:i:s).
		 * @return array Traffic classification data.
		 */
		private function get_traffic_classification_data_impl( $start_date, $end_date ) {
			global $wpdb;

			// Check cache first (5-minute TTL)
			$cache_key = 'opti_behavior_traffic_class_' . md5( $start_date . $end_date );
			$cached = get_transient( $cache_key );
			if ( false !== $cached ) {
				return $cached;
			}

			// Get traffic type counts (exclude bot traffic)
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$traffic_counts = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						traffic_type,
						COUNT(*) as count
					FROM " . $wpdb->prefix . "optibehavior_sessions
					WHERE start_time BETWEEN %s AND %s
						AND traffic_type != 'bot'
					GROUP BY traffic_type",
					$start_date,
					$end_date
				),
				ARRAY_A
			);

			// Get spam reason breakdown
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$spam_reasons = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						spam_reason,
						COUNT(*) as count
					FROM " . $wpdb->prefix . "optibehavior_sessions
					WHERE start_time BETWEEN %s AND %s
						AND traffic_type = 'spam'
						AND spam_reason IS NOT NULL
					GROUP BY spam_reason",
					$start_date,
					$end_date
				),
				ARRAY_A
			);

			// Calculate totals and percentages (excluding bot traffic)
			$total = 0;
			$data = array(
				'human'     => array( 'count' => 0, 'percentage' => 0 ),
				'automated' => array( 'count' => 0, 'percentage' => 0 ),
				'spam'      => array( 'count' => 0, 'percentage' => 0, 'reasons' => array() ),
			);

			foreach ( $traffic_counts as $row ) {
				$type = $row['traffic_type'];
				$count = intval( $row['count'] );
				$total += $count;

				if ( isset( $data[ $type ] ) ) {
					$data[ $type ]['count'] = $count;
				}
			}

			// Calculate percentages
			if ( $total > 0 ) {
				foreach ( $data as $type => $info ) {
					$data[ $type ]['percentage'] = round( ( $info['count'] / $total ) * 100, 1 );
				}
			}

			// Add spam reason breakdown
			foreach ( $spam_reasons as $row ) {
				$data['spam']['reasons'][ $row['spam_reason'] ] = intval( $row['count'] );
			}

			$data['total'] = $total;

			// Get previous period data for comparison
			$duration = strtotime( $end_date ) - strtotime( $start_date );
			$prev_end = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - 1 );
			$prev_start = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - $duration );

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$prev_total = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM " . $wpdb->prefix . "optibehavior_sessions WHERE start_time BETWEEN %s AND %s",
					$prev_start,
					$prev_end
				)
			);

			$prev_total = intval( $prev_total );
			if ( $prev_total > 0 ) {
				$data['change_percentage'] = round( ( ( $total - $prev_total ) / $prev_total ) * 100 );
			} else {
				$data['change_percentage'] = $total > 0 ? 100 : 0;
			}

			// Cache for 5 minutes
			set_transient( $cache_key, $data, 300 ); // 5 minutes

			return $data;
		}

		/**
		 * Get bot traffic data
		 *
		 * @param string $start_date Start date (Y-m-d H:i:s).
		 * @param string $end_date End date (Y-m-d H:i:s).
		 * @return array Bot traffic data.
		 */
		private function get_bot_traffic_data_impl( $start_date, $end_date ) {
			global $wpdb;

			// Check cache first (5-minute TTL)
			$cache_key = 'opti_behavior_bot_traffic_' . md5( $start_date . $end_date );
			$cached = get_transient( $cache_key );
			if ( false !== $cached ) {
				return $cached;
			}

			// Get bot visit counts by bot type (limited to top 20 for performance)
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$bot_counts = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						bot_type,
						COUNT(*) as count
					FROM " . $wpdb->prefix . "optibehavior_bot_visits
					WHERE visit_time BETWEEN %s AND %s
					GROUP BY bot_type
					ORDER BY count DESC
					LIMIT 20",
					$start_date,
					$end_date
				),
				ARRAY_A
			);

			// Calculate total and percentages
			$total = 0;
			$data = array();

			foreach ( $bot_counts as $row ) {
				$bot_type = $row['bot_type'];
				$count = intval( $row['count'] );
				$total += $count;

				$data[ $bot_type ] = array(
					'count' => $count,
					'percentage' => 0,
				);
			}

			// Calculate percentages
			if ( $total > 0 ) {
				foreach ( $data as $bot_type => $info ) {
					$data[ $bot_type ]['percentage'] = round( ( $info['count'] / $total ) * 100, 1 );
				}
			}

			// Get previous period data for comparison
			$duration = strtotime( $end_date ) - strtotime( $start_date );
			$prev_end = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - 1 );
			$prev_start = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - $duration );

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$prev_total = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM " . $wpdb->prefix . "optibehavior_bot_visits WHERE visit_time BETWEEN %s AND %s",
					$prev_start,
					$prev_end
				)
			);

			$prev_total = intval( $prev_total );
			if ( $prev_total > 0 ) {
				$change_percentage = round( ( ( $total - $prev_total ) / $prev_total ) * 100 );
			} else {
				$change_percentage = $total > 0 ? 100 : 0;
			}

			$result = array(
				'total' => $total,
				'bots' => $data,
				'change_percentage' => $change_percentage,
			);

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

			return $result;
		}

		/**
		 * Get new vs returning visitors data
		 *
		 * @param string $start_date Start date (Y-m-d H:i:s).
		 * @param string $end_date End date (Y-m-d H:i:s).
		 * @return array New vs returning visitors data.
		 */
		private function get_new_vs_returning_visitors_data_impl( $start_date, $end_date ) {
			global $wpdb;

			// Try to use pre-aggregated data from daily_stats table (fast)
			$daily_stats_table = $wpdb->prefix . 'optibehavior_daily_stats';
			$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $daily_stats_table ) );

			if ( $table_exists ) {
				$start_date_only = gmdate( 'Y-m-d', strtotime( $start_date ) );
				$end_date_only = gmdate( 'Y-m-d', strtotime( $end_date ) );

				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
				// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
				$aggregated = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT SUM(new_visitors) as new_visitors, SUM(returning_visitors) as returning_visitors
						FROM " . $daily_stats_table . "
						WHERE stat_date BETWEEN %s AND %s",
						$start_date_only,
						$end_date_only
					),
					ARRAY_A
				);

				if ( $aggregated && ( intval($aggregated['new_visitors'] ?? 0) > 0 || intval($aggregated['returning_visitors'] ?? 0) > 0 ) ) {
					$new_visitors = intval( $aggregated['new_visitors'] ?? 0 );
					$returning_visitors = intval( $aggregated['returning_visitors'] ?? 0 );
					$total = $new_visitors + $returning_visitors;

					$new_percentage = $total > 0 ? round( ( $new_visitors / $total ) * 100, 1 ) : 0;
					$returning_percentage = $total > 0 ? round( ( $returning_visitors / $total ) * 100, 1 ) : 0;

					$duration = strtotime( $end_date ) - strtotime( $start_date );
					$prev_end = gmdate( 'Y-m-d', strtotime( $start_date ) - 1 );
					$prev_start = gmdate( 'Y-m-d', strtotime( $start_date ) - $duration );

					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
					// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
					$prev_aggregated = $wpdb->get_row(
						$wpdb->prepare(
							"SELECT SUM(new_visitors) as new_visitors, SUM(returning_visitors) as returning_visitors
							FROM " . $daily_stats_table . "
							WHERE stat_date BETWEEN %s AND %s",
							$prev_start,
							$prev_end
						),
						ARRAY_A
					);

					$prev_new = intval( $prev_aggregated['new_visitors'] ?? 0 );
					$prev_returning = intval( $prev_aggregated['returning_visitors'] ?? 0 );

					$new_change = $prev_new > 0 ? round( ( ( $new_visitors - $prev_new ) / $prev_new ) * 100 ) : ( $new_visitors > 0 ? 100 : 0 );
					$returning_change = $prev_returning > 0 ? round( ( ( $returning_visitors - $prev_returning ) / $prev_returning ) * 100 ) : ( $returning_visitors > 0 ? 100 : 0 );

					return array(
						'new_visitors' => $new_visitors,
						'returning_visitors' => $returning_visitors,
						'total' => $total,
						'new_percentage' => $new_percentage,
						'returning_percentage' => $returning_percentage,
						'new_change' => $new_change,
						'returning_change' => $returning_change,
					);
				}
			}

			// Fallback: Use sampling with LIMIT to avoid timeout
			$spam_clause = $this->get_spam_exclusion_clause( 's' );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$visitor_stats = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
						COUNT(DISTINCT CASE WHEN v.visit_count = 1 THEN sampled.visitor_id END) as new_visitors,
						COUNT(DISTINCT CASE WHEN v.visit_count > 1 THEN sampled.visitor_id END) as returning_visitors
					FROM (
						SELECT s.visitor_id
						FROM " . $wpdb->prefix . "optibehavior_sessions s
						WHERE s.start_time BETWEEN %s AND %s" . $spam_clause . "
						LIMIT 50000
					) sampled
					INNER JOIN " . $wpdb->prefix . "optibehavior_visitors v ON sampled.visitor_id = v.id",
					$start_date,
					$end_date
				),
				ARRAY_A
			);

			$new_visitors = intval( $visitor_stats['new_visitors'] ?? 0 );
			$returning_visitors = intval( $visitor_stats['returning_visitors'] ?? 0 );
			$total = $new_visitors + $returning_visitors;

			$new_percentage = $total > 0 ? round( ( $new_visitors / $total ) * 100, 1 ) : 0;
			$returning_percentage = $total > 0 ? round( ( $returning_visitors / $total ) * 100, 1 ) : 0;

			return array(
				'new_visitors' => $new_visitors,
				'returning_visitors' => $returning_visitors,
				'total' => $total,
				'new_percentage' => $new_percentage,
				'returning_percentage' => $returning_percentage,
				'new_change' => 0,
				'returning_change' => 0,
			);
		}

		/**
		 * Get visited directories/pages data - AGGREGATED BY TAXONOMY TYPE
		 *
		 * @param string $start_date Start date (Y-m-d H:i:s).
		 * @param string $end_date End date (Y-m-d H:i:s).
		 * @return array Visited directories data aggregated by taxonomy type.
		 */
	private function get_visited_directories_data_impl( $start_date, $end_date ) {
		global $wpdb;

		// Build spam filter join if needed
		$spam_join = '';
		$spam_where = '';
		if ( $this->is_spam_excluded() ) {
			$spam_join = "INNER JOIN " . $wpdb->prefix . "optibehavior_sessions s ON pv.session_id = s.id";
			$spam_where = " AND s.traffic_type IN ('human', 'automated', 'bot')";
		}

		// Get current period data - AGGREGATED BY TAXONOMY TYPE (not individual pages)
		// Use sampling for performance with large datasets
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table query for analytics data
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$current_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					p.taxonomy,
					p.object_type,
					COUNT(DISTINCT sampled.session_id) as sessions,
					COUNT(*) as pageviews
				FROM (
					SELECT pv.session_id, pv.page_id
					FROM " . $wpdb->prefix . "optibehavior_pageviews pv
					" . $spam_join . "
					WHERE pv.view_time BETWEEN %s AND %s" . $spam_where . "
					LIMIT 50000
				) sampled
				INNER JOIN " . $wpdb->prefix . "optibehavior_pages p ON sampled.page_id = p.id
				GROUP BY p.taxonomy, p.object_type
				ORDER BY sessions DESC
				LIMIT 50",
				$start_date,
				$end_date
			),
			ARRAY_A
		);

		// Get previous period data for comparison
		$duration = strtotime( $end_date ) - strtotime( $start_date );
		$prev_end = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - 1 );
		$prev_start = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - $duration );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table query for analytics data
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$prev_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					p.taxonomy,
					p.object_type,
					COUNT(DISTINCT sampled.session_id) as sessions
				FROM (
					SELECT pv.session_id, pv.page_id
					FROM " . $wpdb->prefix . "optibehavior_pageviews pv
					" . $spam_join . "
					WHERE pv.view_time BETWEEN %s AND %s" . $spam_where . "
					LIMIT 50000
				) sampled
				INNER JOIN " . $wpdb->prefix . "optibehavior_pages p ON sampled.page_id = p.id
				GROUP BY p.taxonomy, p.object_type
				LIMIT 50",
				$prev_start,
				$prev_end
			),
			ARRAY_A
		);

		// Index previous data by taxonomy+object_type for quick lookup
		$prev_sessions_by_type = array();
		foreach ( $prev_data as $item ) {
			$key = ( $item['taxonomy'] ?? '' ) . '|' . ( $item['object_type'] ?? '' );
			$prev_sessions_by_type[ $key ] = intval( $item['sessions'] );
		}

		// Process aggregated taxonomy types
		$type_stats = array();
		foreach ( $current_data as $item ) {
			$taxonomy = $item['taxonomy'] ?? '';
			$object_type = $item['object_type'] ?? '';
			$key = $taxonomy . '|' . $object_type;

			$current_sessions = intval( $item['sessions'] );
			$pageviews = intval( $item['pageviews'] );
			$prev_sessions = $prev_sessions_by_type[ $key ] ?? 0;

			// Calculate percentage change
			$change = $prev_sessions > 0 ? round( ( ( $current_sessions - $prev_sessions ) / $prev_sessions ) * 100 ) : ( $current_sessions > 0 ? 100 : 0 );

			// Get display name and URL for this taxonomy type (not specific item)
			$display_info = $this->get_taxonomy_type_display_info( $taxonomy, $object_type );

			$type_stats[] = array(
				'type'      => $display_info['display_name'],
				'url'       => $display_info['url'],
				'taxonomy'  => $taxonomy,
				'sessions'  => $current_sessions,
				'pageviews' => $pageviews,
				'change'    => $change,
			);
		}

		$result = array(
			'directories' => $type_stats,
			'total'       => count( $type_stats ),
		);

		return $result;
	}

	/**
	 * Get taxonomy key for classification grouping
	 *
	 * @param array $page Page data array with object_type and taxonomy.
	 * @return string Taxonomy key for grouping.
	 */
	private function get_taxonomy_key_for_classification( $page ) {
		$object_type = $page['object_type'] ?? '';
		$taxonomy = $page['taxonomy'] ?? '';

		// Home page
		if ( $object_type === 'home' ) {
			return 'home';
		}

		// Author archives
		if ( $object_type === 'author' ) {
			return array(
				'display_name' => __( 'Authors', 'opti-behavior' ),
				'url'          => home_url( '/author/' ),
			);
		}

		// Search pages
		if ( $object_type === 'search' ) {
			return array(
				'display_name' => __( 'Search', 'opti-behavior' ),
				'url'          => home_url( '/?s=' ),
			);
		}

		// Date archives
		if ( $object_type === 'date_archive' ) {
			return array(
				'display_name' => __( 'Date Archives', 'opti-behavior' ),
				'url'          => home_url( '/' ),
			);
		}

		// Custom post type archives
		if ( $object_type === 'post_type_archive' && ! empty( $taxonomy ) ) {
			$post_type_object = get_post_type_object( $taxonomy );
			if ( $post_type_object && isset( $post_type_object->labels->name ) ) {
				$display_name = $post_type_object->labels->name;
				$archive_link = get_post_type_archive_link( $taxonomy );
			} else {
				$display_name = __( 'Archives', 'opti-behavior' );
				$archive_link = home_url( '/' );
			}

			return array(
				'display_name' => $display_name,
				'url'          => $archive_link ?: home_url( '/' ),
			);
		}

		// 404 pages
		if ( $object_type === '404' ) {
			return array(
				'display_name' => __( '404 Pages', 'opti-behavior' ),
				'url'          => '#',
			);
		}

		// Terms (categories, tags, custom taxonomies)
		if ( $object_type === 'term' && ! empty( $taxonomy ) ) {
			return 'term_' . $taxonomy;
		}

		// Post
		if ( $object_type === 'post' ) {
			return 'post';
		}

		// Page
		if ( $object_type === 'page' ) {
			return 'page';
		}

		// Product
		if ( $object_type === 'product' ) {
			return 'product';
		}

		// Archive pages
		if ( strpos( $object_type, 'archive' ) !== false ) {
			return $object_type;
		}

		// Other/unknown
		return 'other';
	}

	/**
	 * Detect taxonomy information from URL (wrapper for analytics class method)
	 *
	 * @param string $url Page URL.
	 * @return array|null Array with post_id, term_id, taxonomy, and object_type.
	 */
	private function detect_taxonomy_from_url( $url ) {
		global $wpdb;

		$result = array(
			'post_id'     => null,
			'term_id'     => null,
			'taxonomy'    => null,
			'object_type' => null,
		);

		// Parse URL
		$parsed_url = wp_parse_url( $url );
		$path = isset( $parsed_url['path'] ) ? trim( $parsed_url['path'], '/' ) : '';

		// Remove WordPress base path
		$site_url = get_site_url();
		$site_path = wp_parse_url( $site_url, PHP_URL_PATH );
		if ( $site_path && strpos( $path, ltrim( $site_path, '/' ) ) === 0 ) {
			$path = substr( $path, strlen( ltrim( $site_path, '/' ) ) );
			$path = ltrim( $path, '/' );
		}

		// Check for home page
		if ( empty( $path ) ) {
			$result['object_type'] = 'home';
			return $result;
		}

		// Split path into parts
		$path_parts = explode( '/', $path );

		// Check for category archives
		if ( $path_parts[0] === 'category' && isset( $path_parts[1] ) ) {
			$slug = $path_parts[1];
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- One-time taxonomy lookup; table names from $wpdb properties.
			$term = $wpdb->get_row( $wpdb->prepare(
				"SELECT t.term_id, t.name, tt.taxonomy
				FROM " . $wpdb->terms . " t
				INNER JOIN " . $wpdb->term_taxonomy . " tt ON t.term_id = tt.term_id
				WHERE t.slug = %s AND tt.taxonomy = 'category'
				LIMIT 1",
				$slug
			) );

			if ( $term ) {
				$result['term_id'] = (int) $term->term_id;
				$result['taxonomy'] = 'category';
				$result['object_type'] = 'term';
			}
			return $result;
		}

		// Check for tag archives
		if ( $path_parts[0] === 'tag' && isset( $path_parts[1] ) ) {
			$slug = $path_parts[1];
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- One-time taxonomy lookup; table names from $wpdb properties.
			$term = $wpdb->get_row( $wpdb->prepare(
				"SELECT t.term_id, t.name, tt.taxonomy
				FROM " . $wpdb->terms . " t
				INNER JOIN " . $wpdb->term_taxonomy . " tt ON t.term_id = tt.term_id
				WHERE t.slug = %s AND tt.taxonomy = 'post_tag'
				LIMIT 1",
				$slug
			) );

			if ( $term ) {
				$result['term_id'] = (int) $term->term_id;
				$result['taxonomy'] = 'post_tag';
				$result['object_type'] = 'term';
			}
			return $result;
		}

		// Try to find post by slug
		$slug = $path_parts[ count( $path_parts ) - 1 ];
		if ( ! empty( $slug ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- One-time post lookup; table name from $wpdb property.
			$post = $wpdb->get_row( $wpdb->prepare(
				"SELECT ID, post_name, post_type
				FROM " . $wpdb->posts . "
				WHERE post_name = %s AND post_status = 'publish'
				LIMIT 1",
				$slug
			) );

			if ( $post ) {
				$result['post_id'] = (int) $post->ID;
				$result['object_type'] = 'post';
			}
		}

		return $result;
	}

	/**
	 * Get display information for taxonomy data
	 *
	 * @param array $data Data array with taxonomy, object_type, term_id, post_id.
	 * @return array|null Array with display_name and url, or null if data invalid.
	 */
	private function get_taxonomy_display_info( $data ) {
		global $wpdb;

		$object_type = $data['object_type'] ?? null;
		$taxonomy = $data['taxonomy'] ?? null;
		$term_id = isset( $data['term_id'] ) ? intval( $data['term_id'] ) : null;
		$post_id = isset( $data['post_id'] ) ? intval( $data['post_id'] ) : null;

		// Handle special cases
		if ( $object_type === 'home' ) {
			return array(
				'display_name' => 'Home',
				'url'          => home_url( '/' ),
			);
		}

		if ( $object_type === 'system' ) {
			return array(
				'display_name' => 'System Pages',
				'url'          => admin_url(),
			);
		}

		// Handle terms (categories, tags, custom taxonomies)
		if ( $object_type === 'term' && $term_id ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Querying term data; table names from $wpdb properties.
			$term = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT t.name, t.slug, tt.taxonomy
					FROM " . $wpdb->terms . " t
					INNER JOIN " . $wpdb->term_taxonomy . " tt ON t.term_id = tt.term_id
					WHERE t.term_id = %d
					LIMIT 1",
					$term_id
				)
			);

			if ( $term ) {
				// Format display name based on taxonomy
				$display_prefix = '';
				switch ( $term->taxonomy ) {
					case 'category':
						$display_prefix = 'Category: ';
						break;
					case 'post_tag':
						$display_prefix = 'Tag: ';
						break;
					case 'product_cat':
						$display_prefix = 'Product Category: ';
						break;
					case 'product_tag':
						$display_prefix = 'Product Tag: ';
						break;
					default:
						// Format custom taxonomy name
						$tax_name = str_replace( array( '_', '-' ), ' ', $term->taxonomy );
						$display_prefix = ucwords( $tax_name ) . ': ';
						break;
				}

				// Get term link
				$term_link = get_term_link( $term_id, $term->taxonomy );
				if ( is_wp_error( $term_link ) ) {
					$term_link = home_url( '/' );
				}

				return array(
					'display_name' => $display_prefix . $term->name,
					'url'          => $term_link,
				);
			}
		}

		// Handle posts/pages
		if ( $object_type === 'post' && $post_id ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Querying post data; table name from $wpdb property.
			$post = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT post_title, post_name, post_type
					FROM " . $wpdb->posts . "
					WHERE ID = %d
					LIMIT 1",
					$post_id
				)
			);

			if ( $post ) {
				$post_link = get_permalink( $post_id );
				if ( ! $post_link ) {
					$post_link = home_url( '/' );
				}

				$display_name = $post->post_title;
				if ( empty( $display_name ) ) {
					$display_name = ucwords( str_replace( array( '-', '_' ), ' ', $post->post_name ) );
				}

				return array(
					'display_name' => $display_name,
					'url'          => $post_link,
				);
			}
		}

		// Default for unknown types
		return array(
			'display_name' => 'Other',
			'url'          => home_url( '/' ),
		);
	}

	/**
	 * Get display information for a taxonomy type (not a specific term/post)
	 *
	 * @param string $taxonomy Taxonomy name.
	 * @param string $object_type Object type (home, term, post, system, post_type_archive).
	 * @return array Array with display_name and url.
	 */
	private function get_taxonomy_type_display_info( $taxonomy, $object_type ) {
		// Home page
		if ( $object_type === 'home' ) {
			return array(
				'display_name' => __( 'Home Page', 'opti-behavior' ),
				'url'          => home_url( '/' ),
			);
		}

		// Posts
		if ( $object_type === 'post' ) {
			return array(
				'display_name' => __( 'Posts', 'opti-behavior' ),
				'url'          => get_post_type_archive_link( 'post' ) ?: home_url( '/' ),
			);
		}

		// Pages
		if ( $object_type === 'page' ) {
			return array(
				'display_name' => __( 'Pages', 'opti-behavior' ),
				'url'          => '#',
			);
		}

		// Products
		if ( $object_type === 'product' ) {
			return array(
				'display_name' => __( 'Products', 'opti-behavior' ),
				'url'          => get_post_type_archive_link( 'product' ) ?: home_url( '/' ),
			);
		}

		// Author archives
		if ( $object_type === 'author' ) {
			return array(
				'display_name' => __( 'Authors', 'opti-behavior' ),
				'url'          => home_url( '/author/' ),
			);
		}

		// Search pages
		if ( $object_type === 'search' ) {
			return array(
				'display_name' => __( 'Search', 'opti-behavior' ),
				'url'          => home_url( '/?s=' ),
			);
		}

		// Date archives
		if ( $object_type === 'date_archive' ) {
			return array(
				'display_name' => __( 'Date Archives', 'opti-behavior' ),
				'url'          => home_url( '/' ),
			);
		}

		// Custom post type archives
		if ( $object_type === 'post_type_archive' && ! empty( $taxonomy ) ) {
			$post_type_object = get_post_type_object( $taxonomy );
			if ( $post_type_object && isset( $post_type_object->labels->name ) ) {
				$display_name = $post_type_object->labels->name;
				$archive_link = get_post_type_archive_link( $taxonomy );
			} else {
				$display_name = __( 'Archives', 'opti-behavior' );
				$archive_link = home_url( '/' );
			}

			return array(
				'display_name' => $display_name,
				'url'          => $archive_link ?: home_url( '/' ),
			);
		}

		// 404 pages
		if ( $object_type === '404' ) {
			return array(
				'display_name' => __( '404 Pages', 'opti-behavior' ),
				'url'          => '#',
			);
		}

		// Terms (categories, tags, custom taxonomies)
		if ( $object_type === 'term' && ! empty( $taxonomy ) ) {
			switch ( $taxonomy ) {
				case 'category':
					return array(
						'display_name' => __( 'Categories', 'opti-behavior' ),
						'url'          => home_url( '/category/' ),
					);

				case 'post_tag':
					return array(
						'display_name' => __( 'Tags', 'opti-behavior' ),
						'url'          => home_url( '/tag/' ),
					);

				case 'product_cat':
					return array(
						'display_name' => __( 'Product Categories', 'opti-behavior' ),
						'url'          => home_url( '/product-category/' ),
					);

				case 'product_tag':
					return array(
						'display_name' => __( 'Product Tags', 'opti-behavior' ),
						'url'          => home_url( '/product-tag/' ),
					);

				default:
					// Custom taxonomy - try to get from WordPress
					$tax_object = get_taxonomy( $taxonomy );
					if ( $tax_object && isset( $tax_object->labels->name ) ) {
						$display_name = $tax_object->labels->name;
					} else {
						// Fallback: format taxonomy slug
						$display_name = ucfirst( str_replace( array( '_', '-' ), ' ', $taxonomy ) );
					}

					return array(
						'display_name' => $display_name,
						'url'          => home_url( '/' ),
					);
			}
		}

		// Archive pages
		if ( strpos( $object_type, 'archive' ) !== false ) {
			return array(
				'display_name' => __( 'Archive Pages', 'opti-behavior' ),
				'url'          => home_url( '/' ),
			);
		}

		// Other/unknown
		return array(
			'display_name' => __( 'Other', 'opti-behavior' ),
			'url'          => home_url( '/' ),
		);
	}


		/**
		 * Get new registered users data
		 *
		 * @param string $start_date Start date (Y-m-d H:i:s).
		 * @param string $end_date End date (Y-m-d H:i:s).
		 * @return array New registered users data.
		 */

	/**
	 * Get display information for an individual taxonomy item (term, post, page, etc.)
	 *
	 * @param array $item Item data with taxonomy, object_type, term_id, post_id, and url.
	 * @return array Array with display_name and url.
	 */
	private function get_taxonomy_item_display_info( $item ) {
		$object_type = $item['object_type'] ?? '';
		$taxonomy = $item['taxonomy'] ?? '';
		$term_id = isset( $item['term_id'] ) ? intval( $item['term_id'] ) : null;
		$post_id = isset( $item['post_id'] ) ? intval( $item['post_id'] ) : null;
		$url = $item['url'] ?? '';

		// Home page
		if ( $object_type === 'home' ) {
			return array(
				'display_name' => __( 'Home Page', 'opti-behavior' ),
				'url'          => home_url( '/' ),
			);
		}

		// Terms (categories, tags, authors, custom taxonomies)
		if ( $object_type === 'term' && $term_id ) {
			$term = get_term( $term_id, $taxonomy );
			if ( $term && ! is_wp_error( $term ) ) {
				// Format display name based on taxonomy
				$tax_object = get_taxonomy( $taxonomy );
				$tax_label = '';
				
				if ( $tax_object && isset( $tax_object->labels->singular_name ) ) {
					$tax_label = $tax_object->labels->singular_name;
				} else {
					// Fallback: format taxonomy slug
					switch ( $taxonomy ) {
						case 'category':
							$tax_label = __( 'Category', 'opti-behavior' );
							break;
						case 'post_tag':
							$tax_label = __( 'Tag', 'opti-behavior' );
							break;
						case 'product_cat':
							$tax_label = __( 'Product Category', 'opti-behavior' );
							break;
						case 'product_tag':
							$tax_label = __( 'Product Tag', 'opti-behavior' );
							break;
						default:
							$tax_label = ucwords( str_replace( array( '_', '-' ), ' ', $taxonomy ) );
							break;
					}
				}

				// Get term link
				$term_link = get_term_link( $term_id, $taxonomy );
				if ( is_wp_error( $term_link ) ) {
					$term_link = $url ?: home_url( '/' );
				}

				return array(
					'display_name' => $tax_label . ': ' . $term->name,
					'url'          => $term_link,
				);
			}
		}

		// Posts/Pages/Products
		if ( in_array( $object_type, array( 'post', 'page', 'product' ), true ) && $post_id ) {
			$post_title = get_the_title( $post_id );
			$permalink = get_permalink( $post_id );

			if ( $post_title ) {
				// Format display name based on post type
				$type_label = '';
				switch ( $object_type ) {
					case 'post':
						$type_label = __( 'Post', 'opti-behavior' );
						break;
					case 'page':
						$type_label = __( 'Page', 'opti-behavior' );
						break;
					case 'product':
						$type_label = __( 'Product', 'opti-behavior' );
						break;
					default:
						$type_label = ucfirst( $object_type );
						break;
				}

				return array(
					'display_name' => $type_label . ': ' . $post_title,
					'url'          => $permalink ?: ( $url ?: home_url( '/' ) ),
				);
			}
		}

		// Fallback: use URL or generic name
		if ( ! empty( $url ) ) {
			// Try to extract a meaningful name from the URL
			$path = wp_parse_url( $url, PHP_URL_PATH );
			$display_name = basename( $path ) ?: __( 'Unknown Page', 'opti-behavior' );
			$display_name = ucwords( str_replace( array( '-', '_' ), ' ', $display_name ) );

			return array(
				'display_name' => $display_name,
				'url'          => $url,
			);
		}

		// Final fallback
		return array(
			'display_name' => __( 'Unknown Page', 'opti-behavior' ),
			'url'          => home_url( '/' ),
		);
	}

	private function get_new_registered_users_data_impl( $start_date, $end_date ) {
		global $wpdb;

		$spam_clause = $this->get_spam_exclusion_clause( 's' );

		// Get total unique visitors for current period
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table query for analytics data
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_visitors = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT v.id)
				FROM " . $wpdb->prefix . "optibehavior_visitors v
				INNER JOIN " . $wpdb->prefix . "optibehavior_sessions s ON v.id = s.visitor_id
				WHERE s.start_time BETWEEN %s AND %s" . $spam_clause,
				$start_date,
				$end_date
			)
		);

		$total_visitors = intval( $total_visitors );

		// Get logged in visitors (visitors who had logged-in sessions) for current period
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table query for analytics data
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$logged_in_visitors = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT v.id)
				FROM " . $wpdb->prefix . "optibehavior_visitors v
				INNER JOIN " . $wpdb->prefix . "optibehavior_sessions s ON v.id = s.visitor_id
				WHERE s.start_time BETWEEN %s AND %s" . $spam_clause . "
				AND s.user_id IS NOT NULL
				AND s.user_id > 0",
				$start_date,
				$end_date
			)
		);

		$logged_in_visitors = intval( $logged_in_visitors );

		// Get new registered users count for current period
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WordPress users table query; table name from $wpdb property.
		$new_registrations = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*)
				FROM " . $wpdb->users . "
				WHERE user_registered BETWEEN %s AND %s",
				$start_date,
				$end_date
			)
		);

		$new_registrations = intval( $new_registrations );

		// Get previous period data for comparison
		$duration = strtotime( $end_date ) - strtotime( $start_date );
		$prev_end = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - 1 );
		$prev_start = gmdate( 'Y-m-d H:i:s', strtotime( $start_date ) - $duration );

		// Previous period: total visitors
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table query for analytics data
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$prev_total_visitors = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT v.id)
				FROM " . $wpdb->prefix . "optibehavior_visitors v
				INNER JOIN " . $wpdb->prefix . "optibehavior_sessions s ON v.id = s.visitor_id
				WHERE s.start_time BETWEEN %s AND %s" . $spam_clause,
				$prev_start,
				$prev_end
			)
		);

		$prev_total_visitors = intval( $prev_total_visitors );

		// Previous period: logged in visitors
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Custom table query for analytics data
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$prev_logged_in = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT v.id)
				FROM " . $wpdb->prefix . "optibehavior_visitors v
				INNER JOIN " . $wpdb->prefix . "optibehavior_sessions s ON v.id = s.visitor_id
				WHERE s.start_time BETWEEN %s AND %s" . $spam_clause . "
				AND s.user_id IS NOT NULL
				AND s.user_id > 0",
				$prev_start,
				$prev_end
			)
		);

		$prev_logged_in = intval( $prev_logged_in );

		// Previous period: new registrations
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- WordPress users table query; table name from $wpdb property.
		$prev_registrations = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*)
				FROM " . $wpdb->users . "
				WHERE user_registered BETWEEN %s AND %s",
				$prev_start,
				$prev_end
			)
		);

		$prev_registrations = intval( $prev_registrations );

		$result = array(
			'total_visitors' => $total_visitors,
			'logged_in_visitors' => $logged_in_visitors,
			'new_registrations' => $new_registrations,
			'previous_total_visitors' => $prev_total_visitors,
			'previous_logged_in_visitors' => $prev_logged_in,
			'previous_new_registrations' => $prev_registrations,
			'total' => $total_visitors,
		);

		return $result;
	}

	/**
	 * Get daily breakdown stats for history bar charts
	 * Uses pre-aggregated daily_stats table for fast loading with fallback to real-time queries.
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s).
	 * @param string $end_date End date (Y-m-d H:i:s).
	 * @return array Daily stats breakdown for all metrics.
	 */
	private function get_daily_stats_history( $start_date, $end_date ) {
		global $wpdb;

		// Try to use pre-aggregated data first (much faster for large datasets)
		$aggregated_result = $this->get_daily_stats_from_aggregated( $start_date, $end_date );
		if ( false !== $aggregated_result ) {
			return $aggregated_result;
		}

		// Fallback to real-time queries if aggregated data not available
		return $this->get_daily_stats_realtime( $start_date, $end_date );
	}

	/**
	 * Get daily stats from pre-aggregated daily_stats table.
	 * Returns false if table doesn't exist or data coverage is insufficient.
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s).
	 * @param string $end_date End date (Y-m-d H:i:s).
	 * @return array|false Daily stats or false if not available.
	 */
	private function get_daily_stats_from_aggregated( $start_date, $end_date ) {
		global $wpdb;

		$daily_stats_table = $wpdb->prefix . 'optibehavior_daily_stats';

		// Check if daily_stats table exists
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $daily_stats_table ) );
		if ( ! $table_exists ) {
			return false;
		}

		// Extract date parts for query
		$start_date_only = gmdate( 'Y-m-d', strtotime( $start_date ) );
		$end_date_only   = gmdate( 'Y-m-d', strtotime( $end_date ) );

		// Get all dates in the range
		$start_dt = new DateTime( $start_date );
		$end_dt   = new DateTime( $end_date );
		$interval = DateInterval::createFromDateString( '1 day' );
		$period   = new DatePeriod( $start_dt, $interval, $end_dt->modify( '+1 day' ) );

		// Initialize result arrays
		$daily_data = array(
			'visitors'         => array(),
			'sessions'         => array(),
			'pageviews'        => array(),
			'avg_session_time' => array(),
			'avg_scroll_depth' => array(),
			'bounce_rate'      => array(),
			'dates'            => array(),
		);

		// Initialize all dates with zero values
		foreach ( $period as $dt ) {
			$date_key                                    = $dt->format( 'Y-m-d' );
			$daily_data['dates'][]                       = $date_key;
			$daily_data['visitors'][ $date_key ]         = 0;
			$daily_data['sessions'][ $date_key ]         = 0;
			$daily_data['pageviews'][ $date_key ]        = 0;
			$daily_data['avg_session_time'][ $date_key ] = 0;
			$daily_data['avg_scroll_depth'][ $date_key ] = 0;
			$daily_data['bounce_rate'][ $date_key ]      = 0;
		}

		// Query pre-aggregated data (single query instead of 6!)
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$aggregated_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					stat_date,
					sessions,
					visitors,
					pageviews,
					bounce_sessions,
					total_duration,
					total_scroll_depth,
					scroll_depth_count,
					human_sessions,
					spam_sessions
				FROM " . $daily_stats_table . "
				WHERE stat_date BETWEEN %s AND %s
				ORDER BY stat_date ASC",
				$start_date_only,
				$end_date_only
			),
			ARRAY_A
		);

		// Check if we have sufficient coverage (at least 50% of dates)
		$expected_days = count( $daily_data['dates'] );
		$actual_days   = count( $aggregated_data );

		if ( $actual_days < ( $expected_days * 0.5 ) ) {
			// Not enough aggregated data, fall back to real-time
			return false;
		}

		// Check if spam should be excluded
		$exclude_spam = $this->is_spam_excluded();

		// Populate from aggregated data
		foreach ( $aggregated_data as $row ) {
			$date_key = $row['stat_date'];

			if ( ! isset( $daily_data['visitors'][ $date_key ] ) ) {
				continue; // Date not in our range.
			}

			// Use human_sessions if spam is excluded, otherwise total sessions.
			$sessions = $exclude_spam ? intval( $row['human_sessions'] ) : intval( $row['sessions'] );

			$daily_data['visitors'][ $date_key ]  = intval( $row['visitors'] );
			$daily_data['sessions'][ $date_key ]  = $sessions;
			$daily_data['pageviews'][ $date_key ] = intval( $row['pageviews'] );

			// Calculate average session time from totals.
			$total_duration = intval( $row['total_duration'] );
			$daily_data['avg_session_time'][ $date_key ] = $sessions > 0 ? round( $total_duration / $sessions ) : 0;

			// Calculate average scroll depth from totals.
			$total_scroll  = intval( $row['total_scroll_depth'] );
			$scroll_count  = intval( $row['scroll_depth_count'] );
			$daily_data['avg_scroll_depth'][ $date_key ] = $scroll_count > 0 ? round( $total_scroll / $scroll_count, 1 ) : 0;

			// Calculate bounce rate.
			$bounce_sessions = intval( $row['bounce_sessions'] );
			$daily_data['bounce_rate'][ $date_key ] = $sessions > 0 ? round( ( $bounce_sessions / $sessions ) * 100, 1 ) : 0;
		}

		// Convert associative arrays to indexed arrays for easier JS consumption.
		return array(
			'dates'            => $daily_data['dates'],
			'visitors'         => array_values( $daily_data['visitors'] ),
			'sessions'         => array_values( $daily_data['sessions'] ),
			'pageviews'        => array_values( $daily_data['pageviews'] ),
			'avg_session_time' => array_values( $daily_data['avg_session_time'] ),
			'avg_scroll_depth' => array_values( $daily_data['avg_scroll_depth'] ),
			'bounce_rate'      => array_values( $daily_data['bounce_rate'] ),
		);
	}

	/**
	 * Get daily stats using real-time queries (fallback for when aggregated data not available).
	 *
	 * @param string $start_date Start date (Y-m-d H:i:s).
	 * @param string $end_date End date (Y-m-d H:i:s).
	 * @return array Daily stats breakdown for all metrics.
	 */
	private function get_daily_stats_realtime( $start_date, $end_date ) {
		global $wpdb;

		$spam_clause          = $this->get_spam_exclusion_clause( 's' );
		$spam_clause_no_alias = $this->get_spam_exclusion_clause();

		// Initialize result arrays
		$daily_data = array(
			'visitors'         => array(),
			'sessions'         => array(),
			'pageviews'        => array(),
			'avg_session_time' => array(),
			'avg_scroll_depth' => array(),
			'bounce_rate'      => array(),
			'dates'            => array(),
		);

		// Get all dates in the range
		$start_dt = new DateTime( $start_date );
		$end_dt   = new DateTime( $end_date );
		$interval = DateInterval::createFromDateString( '1 day' );
		$period   = new DatePeriod( $start_dt, $interval, $end_dt->modify( '+1 day' ) );

		// Initialize all dates with zero values
		foreach ( $period as $dt ) {
			$date_key                                    = $dt->format( 'Y-m-d' );
			$daily_data['dates'][]                       = $date_key;
			$daily_data['visitors'][ $date_key ]         = 0;
			$daily_data['sessions'][ $date_key ]         = 0;
			$daily_data['pageviews'][ $date_key ]        = 0;
			$daily_data['avg_session_time'][ $date_key ] = 0;
			$daily_data['avg_scroll_depth'][ $date_key ] = 0;
			$daily_data['bounce_rate'][ $date_key ]      = 0;
		}

		// Get daily visitors count
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$visitors_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					DATE(s.start_time) as date,
					COUNT(DISTINCT s.visitor_id) as count
				FROM " . $wpdb->prefix . "optibehavior_sessions s
				WHERE s.start_time BETWEEN %s AND %s" . $spam_clause . "
				GROUP BY DATE(s.start_time)
				ORDER BY date ASC",
				$start_date,
				$end_date
			),
			ARRAY_A
		);

		foreach ( $visitors_data as $row ) {
			$daily_data['visitors'][ $row['date'] ] = intval( $row['count'] );
		}

		// Get daily sessions count
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sessions_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					DATE(start_time) as date,
					COUNT(*) as count
				FROM " . $wpdb->prefix . "optibehavior_sessions
				WHERE start_time BETWEEN %s AND %s" . $spam_clause_no_alias . "
				GROUP BY DATE(start_time)
				ORDER BY date ASC",
				$start_date,
				$end_date
			),
			ARRAY_A
		);

		foreach ( $sessions_data as $row ) {
			$daily_data['sessions'][ $row['date'] ] = intval( $row['count'] );
		}

		// Get daily pageviews count - need to join sessions for spam filtering
		if ( $this->is_spam_excluded() ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$pageviews_data = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						DATE(pv.view_time) as date,
						COUNT(*) as count
					FROM " . $wpdb->prefix . "optibehavior_pageviews pv
					INNER JOIN " . $wpdb->prefix . "optibehavior_sessions s ON pv.session_id = s.id
					WHERE pv.view_time BETWEEN %s AND %s
					AND s.traffic_type IN ('human', 'automated', 'bot')
					GROUP BY DATE(pv.view_time)
					ORDER BY date ASC",
					$start_date,
					$end_date
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$pageviews_data = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						DATE(view_time) as date,
						COUNT(*) as count
					FROM " . $wpdb->prefix . "optibehavior_pageviews
					WHERE view_time BETWEEN %s AND %s
					GROUP BY DATE(view_time)
					ORDER BY date ASC",
					$start_date,
					$end_date
				),
				ARRAY_A
			);
		}

		foreach ( $pageviews_data as $row ) {
			$daily_data['pageviews'][ $row['date'] ] = intval( $row['count'] );
		}

		// Get daily average session time
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$session_time_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					DATE(start_time) as date,
					AVG(duration) as avg_time
				FROM " . $wpdb->prefix . "optibehavior_sessions
				WHERE start_time BETWEEN %s AND %s" . $spam_clause_no_alias . "
				GROUP BY DATE(start_time)
				ORDER BY date ASC",
				$start_date,
				$end_date
			),
			ARRAY_A
		);

		foreach ( $session_time_data as $row ) {
			$daily_data['avg_session_time'][ $row['date'] ] = round( floatval( $row['avg_time'] ) );
		}

		// Get daily average scroll depth - need to join sessions for spam filtering
		if ( $this->is_spam_excluded() ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$scroll_depth_data = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						DATE(pv.view_time) as date,
						AVG(pv.scroll_depth) as avg_depth
					FROM " . $wpdb->prefix . "optibehavior_pageviews pv
					INNER JOIN " . $wpdb->prefix . "optibehavior_sessions s ON pv.session_id = s.id
					WHERE pv.view_time BETWEEN %s AND %s
					AND pv.scroll_depth > 0
					AND s.traffic_type IN ('human', 'automated', 'bot')
					GROUP BY DATE(pv.view_time)
					ORDER BY date ASC",
					$start_date,
					$end_date
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$scroll_depth_data = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						DATE(pv.view_time) as date,
						AVG(pv.scroll_depth) as avg_depth
					FROM " . $wpdb->prefix . "optibehavior_pageviews pv
					WHERE pv.view_time BETWEEN %s AND %s
					AND pv.scroll_depth > 0
					GROUP BY DATE(pv.view_time)
					ORDER BY date ASC",
					$start_date,
					$end_date
				),
				ARRAY_A
			);
		}

		foreach ( $scroll_depth_data as $row ) {
			$daily_data['avg_scroll_depth'][ $row['date'] ] = round( floatval( $row['avg_depth'] ), 1 );
		}

		// Get daily bounce rate
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$bounce_rate_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					DATE(start_time) as date,
					SUM(CASE WHEN is_bounce = 1 THEN 1 ELSE 0 END) as bounces,
					COUNT(*) as total
				FROM " . $wpdb->prefix . "optibehavior_sessions
				WHERE start_time BETWEEN %s AND %s" . $spam_clause_no_alias . "
				GROUP BY DATE(start_time)
				ORDER BY date ASC",
				$start_date,
				$end_date
			),
			ARRAY_A
		);

		foreach ( $bounce_rate_data as $row ) {
			$total                                        = intval( $row['total'] );
			$bounces                                      = intval( $row['bounces'] );
			$daily_data['bounce_rate'][ $row['date'] ] = $total > 0 ? round( ( $bounces / $total ) * 100, 1 ) : 0;
		}

		// Convert associative arrays to indexed arrays for easier JS consumption
		return array(
			'dates'            => $daily_data['dates'],
			'visitors'         => array_values( $daily_data['visitors'] ),
			'sessions'         => array_values( $daily_data['sessions'] ),
			'pageviews'        => array_values( $daily_data['pageviews'] ),
			'avg_session_time' => array_values( $daily_data['avg_session_time'] ),
			'avg_scroll_depth' => array_values( $daily_data['avg_scroll_depth'] ),
			'bounce_rate'      => array_values( $daily_data['bounce_rate'] ),
		);
	}
}
} // End if ( ! trait_exists(...) ).

