<?php
/**
 * Post Metabox Class
 *
 * Handles the heatmap analytics meta box on post/page edit screens.
 *
 * @package opti-behavior
 * @copyright 2025 Opti-User
 * @version 1.0.3
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * Post Metabox Class
 *
 * Provides analytics meta box for post/page editor screens.
 *
 * @since 1.0.0
 */
class Opti_Behavior_Heatmap_Post_Metabox {

	/**
	 * Core instance.
	 *
	 * @since 1.0.0
	 * @var Opti_Behavior_Heatmap_Core
	 */
	private $core;

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 * @param Opti_Behavior_Heatmap_Core $core Core instance.
	 */
	public function __construct( $core ) {
		$this->core = $core;
		$this->init_hooks();
	}

	/**
	 * Initialize hooks.
	 *
	 * @since 1.0.0
	 */
	private function init_hooks() {
		add_action( 'add_meta_boxes', array( $this, 'add_editor_analytics_box' ) );
		add_action( 'admin_enqueue_scripts', array( $this, 'enqueue_metabox_assets' ) );
		add_action( 'wp_ajax_optibehavior_post_analytics', array( $this, 'ajax_post_analytics' ) );
		add_action( 'wp_ajax_optibehavior_post_timeseries', array( $this, 'ajax_post_timeseries' ) );
		add_action( 'wp_ajax_optibehavior_post_analytics_chart', array( $this, 'ajax_post_analytics_chart' ) );
		add_action( 'wp_ajax_optibehavior_post_referrers', array( $this, 'ajax_post_referrers' ) );
		add_action( 'wp_ajax_optibehavior_post_outbound_clicks', array( $this, 'ajax_post_outbound_clicks' ) );
		add_action( 'wp_ajax_optibehavior_post_countries', array( $this, 'ajax_post_countries' ) );
		add_action( 'wp_ajax_optibehavior_post_browsers', array( $this, 'ajax_post_browsers' ) );
		add_action( 'wp_ajax_optibehavior_post_devices', array( $this, 'ajax_post_devices' ) );
	}

	/**
	 * Register heatmap analytics meta box.
	 *
	 * @since 1.0.0
	 */
	public function add_editor_analytics_box() {
		if ( ! is_admin() || ! current_user_can( 'edit_posts' ) ) {
			return;
		}

		add_meta_box(
			'optibehavior_editor_analytics',
			'<i data-lucide="flame" style="width:16px;height:16px;vertical-align:middle;margin-right:4px;"></i>' . __( 'Post Analytics', 'opti-behavior' ),
			array( $this, 'render_editor_analytics_box' ),
			array( 'post', 'page' ),
			'normal',
			'default'
		);
	}

	/**
	 * Enqueue metabox assets.
	 *
	 * @since 1.0.0
	 * @global WP_Post $post Current post object.
	 * @param string   $hook_suffix Current admin page hook suffix.
	 */
	public function enqueue_metabox_assets( $hook_suffix ) {
		if ( ! in_array( $hook_suffix, array( 'post.php', 'post-new.php' ), true ) ) {
			return;
		}

		global $post;
		if ( ! $post ) {
			return;
		}

		// Enqueue Lucide Icons (bundled locally, MIT licensed)
		wp_enqueue_script(
			'lucide-icons',
			OPTI_BEHAVIOR_HEATMAP_ASSETS_URL . 'js/lucide.min.js',
			array(),
			OPTI_BEHAVIOR_HEATMAP_VERSION,
			true
		);

		// Initialize Lucide Icons after page load
		wp_add_inline_script(
			'lucide-icons',
			'document.addEventListener("DOMContentLoaded", function() {
				if (typeof lucide !== "undefined") {
					lucide.createIcons();
				}
			});'
		);

		wp_enqueue_script(
			'chart-js',
			OPTI_BEHAVIOR_HEATMAP_ASSETS_URL . 'js/chart.umd.min.js',
			array(),
			'4.4.0',
			true
		);

		wp_enqueue_script(
			'opti-behavior-metabox-analytics',
			OPTI_BEHAVIOR_HEATMAP_ASSETS_URL . 'js/metabox-analytics.js',
			array( 'chart-js', 'jquery' ),
			OPTI_BEHAVIOR_HEATMAP_VERSION,
			true
		);

		// Localize script data for metabox analytics
		$nonce = wp_create_nonce( 'optibehavior_post_analytics_' . $post->ID );
		wp_localize_script(
			'opti-behavior-metabox-analytics',
			'optiBehaviorMetaboxData',
			array(
				'postId'   => $post->ID,
				'nonce'    => $nonce,
				'ajaxUrl'  => admin_url( 'admin-ajax.php' ),
				'postDate' => get_the_date( 'c', $post->ID ),
				'i18n'     => array(
					'desktop' => __( 'Desktop', 'opti-behavior' ),
					'mobile'  => __( 'Mobile', 'opti-behavior' ),
				),
			)
		);

		// Enqueue metabox styles from external file
		wp_enqueue_style(
			'opti-behavior-metabox',
			OPTI_BEHAVIOR_HEATMAP_ASSETS_URL . 'css/metabox.css',
			array(),
			OPTI_BEHAVIOR_HEATMAP_VERSION
		);

		// Enqueue tooltips CSS for metabox
		wp_enqueue_style(
			'opti-behavior-tooltips',
			OPTI_BEHAVIOR_HEATMAP_ASSETS_URL . 'css/tooltips.css',
			array(),
			OPTI_BEHAVIOR_HEATMAP_VERSION
		);
	}

	/**
	 * Get metabox styles
	 *
	 * NOTE: CSS code has been moved to external file: assets/css/metabox.css
	 * The external file is enqueued in this class (lines 118-123)
	 */
	/**
	 * Render the meta box UI (loads data via AJAX)
	 *
	 * @param WP_Post $post Current post object.
	 */
	public function render_editor_analytics_box( $post ) {
		$nonce = wp_create_nonce( 'optibehavior_post_analytics_' . $post->ID );
		$metabox_tooltips = opti_behavior_get_metabox_tooltips();
		?>
		<!-- Note: Metabox styles are loaded from external file: assets/css/metabox.css -->
		<!-- 3-Column Analytics Layout -->
		<div class="optibehavior-analytics-container" data-post-id="<?php echo esc_attr( $post->ID ); ?>" data-nonce="<?php echo esc_attr( $nonce ); ?>">
			<div class="optibehavior-analytics-grid">
				<!-- Column 1: Entry Sources -->
				<div class="optibehavior-analytics-column optibehavior-entry-column">
					<div class="optibehavior-column-header">
						<div class="optibehavior-column-icon"><i data-lucide="link" style="width:18px;height:18px;"></i></div>
						<h3 class="optibehavior-column-title"><?php echo esc_html__( 'Entry Sources', 'opti-behavior' ); ?> <?php opti_behavior_tooltip_e( $metabox_tooltips['entry_sources']['title'], $metabox_tooltips['entry_sources']['content'], $metabox_tooltips['entry_sources']['simple'] ); ?></h3>
						<div class="optibehavior-column-subtitle"><?php echo esc_html__( 'How visitors arrive', 'opti-behavior' ); ?></div>
					</div>
					<div class="optibehavior-column-content">
						<div id="optibehavior-referrers-content">
							<div id="optibehavior-referrers-loading" class="optibehavior-loading"><?php echo esc_html__( 'Loading referrer data...', 'opti-behavior' ); ?></div>
							<div id="optibehavior-referrers-list" class="optibehavior-scroll-area"></div>
							<div id="optibehavior-referrers-empty" class="optibehavior-empty-state">
								<i data-lucide="link" style="width: 48px; height: 48px; color: #9ca3af; stroke-width: 1.5;"></i>
								<div class="optibehavior-empty-title"><?php echo esc_html__( 'No referrer data available', 'opti-behavior' ); ?></div>
								<div class="optibehavior-empty-sub"><?php echo esc_html__( 'Data will appear once visitors access this page.', 'opti-behavior' ); ?></div>
							</div>
						</div>
					</div>
				</div>

				<!-- Column 2: Visitor Behavior -->
				<div class="optibehavior-analytics-column optibehavior-behavior-column">
					<div class="optibehavior-column-header">
						<div class="optibehavior-column-icon"><i data-lucide="bar-chart-3" style="width:18px;height:18px;"></i></div>
						<h3 class="optibehavior-column-title"><?php echo esc_html__( 'Visitor Behavior', 'opti-behavior' ); ?> <?php opti_behavior_tooltip_e( $metabox_tooltips['visitor_behavior']['title'], $metabox_tooltips['visitor_behavior']['content'], $metabox_tooltips['visitor_behavior']['simple'] ); ?></h3>
						<div class="optibehavior-column-subtitle"><?php echo esc_html__( 'On-page analytics', 'opti-behavior' ); ?></div>
					</div>
					<div class="optibehavior-column-content">
						<div class="optibehavior-behavior-stats">
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="clock" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Avg Time on Page', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['avg_time_on_page']['title'], $metabox_tooltips['avg_time_on_page']['content'], $metabox_tooltips['avg_time_on_page']['simple'], $metabox_tooltips['avg_time_on_page']['example'], array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value">
									<span class="optibehavior-stat-badge" id="optibehavior-avg">—</span>
								</div>
							</div>
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="mouse-pointer-click" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Total Interactions', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['total_interactions']['title'], $metabox_tooltips['total_interactions']['content'], $metabox_tooltips['total_interactions']['simple'], '', array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value">
									<span class="optibehavior-stat-badge" id="optibehavior-int">—</span>
								</div>
							</div>
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="monitor" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Desktop Events', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['desktop_events']['title'], $metabox_tooltips['desktop_events']['content'], $metabox_tooltips['desktop_events']['simple'], '', array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value">
									<span class="optibehavior-stat-badge" id="optibehavior-pc-events">—</span>
								</div>
							</div>
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="smartphone" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Mobile Events', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['mobile_events']['title'], $metabox_tooltips['mobile_events']['content'], $metabox_tooltips['mobile_events']['simple'], '', array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value">
									<span class="optibehavior-stat-badge" id="optibehavior-mobile-events">—</span>
								</div>
							</div>
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="arrow-down" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Avg. Scroll Depth', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['avg_scroll_depth']['title'], $metabox_tooltips['avg_scroll_depth']['content'], $metabox_tooltips['avg_scroll_depth']['simple'], '', array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value">
									<span class="optibehavior-stat-badge" id="optibehavior-scroll">—</span>
								</div>
							</div>
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="log-out" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Bounce Rate', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['bounce_rate_page']['title'], $metabox_tooltips['bounce_rate_page']['content'], $metabox_tooltips['bounce_rate_page']['simple'], '', array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value">
									<span class="optibehavior-stat-badge" id="optibehavior-bounce">—</span>
								</div>
							</div>
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="users" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Visitor Type', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['visitor_type']['title'], $metabox_tooltips['visitor_type']['content'], $metabox_tooltips['visitor_type']['simple'], '', array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value" id="optibehavior-visitor-type">—</div>
							</div>
							<div class="optibehavior-behavior-stat">
								<div class="optibehavior-behavior-label">
									<i data-lucide="refresh-cw" style="width:14px;height:14px;margin-right:4px;vertical-align:middle;"></i>
									<?php echo esc_html__( 'Last Updated', 'opti-behavior' ); ?>
									<?php opti_behavior_tooltip_e( $metabox_tooltips['last_updated']['title'], $metabox_tooltips['last_updated']['content'], $metabox_tooltips['last_updated']['simple'], '', array( 'position' => 'right' ) ); ?>
								</div>
								<div class="optibehavior-behavior-value" id="optibehavior-upd">—</div>
							</div>
						</div>
					</div>
				</div>

				<!-- Column 3: Exit Behavior -->
				<div class="optibehavior-analytics-column optibehavior-exit-column">
					<div class="optibehavior-column-header">
						<div class="optibehavior-column-icon"><i data-lucide="external-link" style="width:18px;height:18px;"></i></div>
						<h3 class="optibehavior-column-title"><?php echo esc_html__( 'Exit Behavior', 'opti-behavior' ); ?> <?php opti_behavior_tooltip_e( $metabox_tooltips['exit_behavior']['title'], $metabox_tooltips['exit_behavior']['content'], $metabox_tooltips['exit_behavior']['simple'] ); ?></h3>
						<div class="optibehavior-column-subtitle"><?php echo esc_html__( 'Where visitors go', 'opti-behavior' ); ?></div>
					</div>
					<div class="optibehavior-column-content">
						<div id="optibehavior-outbound-content">
							<div id="optibehavior-outbound-loading" class="optibehavior-loading"><?php echo esc_html__( 'Loading outbound click data...', 'opti-behavior' ); ?></div>
							<div id="optibehavior-outbound-list" class="optibehavior-scroll-area"></div>
							<div id="optibehavior-outbound-empty" class="optibehavior-empty-state">
								<i data-lucide="external-link" style="width: 48px; height: 48px; color: #9ca3af; stroke-width: 1.5;"></i>
								<div class="optibehavior-empty-title"><?php echo esc_html__( 'No outbound clicks recorded', 'opti-behavior' ); ?></div>
								<div class="optibehavior-empty-sub"><?php echo esc_html__( 'Data will appear once visitors click links on this page.', 'opti-behavior' ); ?></div>
							</div>
						</div>
					</div>
				</div>
			</div>

			<!-- New Analytics Row: Countries, Browsers, Device Types -->
			<div class="optibehavior-analytics-grid optibehavior-secondary-grid">
				<!-- Countries Column -->
				<div class="optibehavior-analytics-column optibehavior-countries-column">
					<div class="optibehavior-column-header">
						<div class="optibehavior-column-icon"><i data-lucide="globe" style="width:18px;height:18px;"></i></div>
						<h3 class="optibehavior-column-title"><?php echo esc_html__( 'Countries', 'opti-behavior' ); ?> <?php opti_behavior_tooltip_e( $metabox_tooltips['countries_page']['title'], $metabox_tooltips['countries_page']['content'], $metabox_tooltips['countries_page']['simple'] ); ?></h3>
					</div>
					<div class="optibehavior-column-content">
						<div id="optibehavior-countries-content">
							<div id="optibehavior-countries-table" class="optibehavior-scroll-area"></div>
							<div id="optibehavior-countries-empty" class="optibehavior-empty-state">
								<i data-lucide="globe" style="width: 48px; height: 48px; color: #9ca3af; stroke-width: 1.5;"></i>
								<div class="optibehavior-empty-title"><?php echo esc_html__( 'No country data available', 'opti-behavior' ); ?></div>
								<div class="optibehavior-empty-sub"><?php echo esc_html__( 'Data will appear once visitors access this page.', 'opti-behavior' ); ?></div>
							</div>
						</div>
					</div>
				</div>

				<!-- Browsers Column -->
				<div class="optibehavior-analytics-column optibehavior-browsers-column">
					<div class="optibehavior-column-header">
						<div class="optibehavior-column-icon"><i data-lucide="compass" style="width:18px;height:18px;"></i></div>
						<h3 class="optibehavior-column-title"><?php echo esc_html__( 'Browsers', 'opti-behavior' ); ?> <?php opti_behavior_tooltip_e( $metabox_tooltips['browsers_page']['title'], $metabox_tooltips['browsers_page']['content'], $metabox_tooltips['browsers_page']['simple'] ); ?></h3>
					</div>
					<div class="optibehavior-column-content">
						<div id="optibehavior-browsers-content">
							<div id="optibehavior-browsers-table" class="optibehavior-scroll-area"></div>
							<div id="optibehavior-browsers-empty" class="optibehavior-empty-state">
								<i data-lucide="compass" style="width: 48px; height: 48px; color: #9ca3af; stroke-width: 1.5;"></i>
								<div class="optibehavior-empty-title"><?php echo esc_html__( 'No browser data available', 'opti-behavior' ); ?></div>
								<div class="optibehavior-empty-sub"><?php echo esc_html__( 'Data will appear once visitors access this page.', 'opti-behavior' ); ?></div>
							</div>
						</div>
					</div>
				</div>

				<!-- Device Types Column -->
				<div class="optibehavior-analytics-column optibehavior-devices-column">
					<div class="optibehavior-column-header">
						<div class="optibehavior-column-icon"><i data-lucide="monitor-smartphone" style="width:18px;height:18px;"></i></div>
						<h3 class="optibehavior-column-title"><?php echo esc_html__( 'Device Types', 'opti-behavior' ); ?> <?php opti_behavior_tooltip_e( $metabox_tooltips['devices_page']['title'], $metabox_tooltips['devices_page']['content'], $metabox_tooltips['devices_page']['simple'] ); ?></h3>
					</div>
					<div class="optibehavior-column-content">
						<div id="optibehavior-devices-content">
							<div id="optibehavior-devices-table" class="optibehavior-scroll-area"></div>
							<div id="optibehavior-devices-empty" class="optibehavior-empty-state">
								<i data-lucide="monitor-smartphone" style="width: 48px; height: 48px; color: #9ca3af; stroke-width: 1.5;"></i>
								<div class="optibehavior-empty-title"><?php echo esc_html__( 'No device data available', 'opti-behavior' ); ?></div>
								<div class="optibehavior-empty-sub"><?php echo esc_html__( 'Data will appear once visitors access this page.', 'opti-behavior' ); ?></div>
							</div>
						</div>
					</div>
				</div>
			</div>

			<!-- Controls Row -->
			<div class="optibehavior-controls-row">
				<div class="optibehavior-heatmap-controls">
					<?php
					// Get the heatmap page_id from the URL (not the WordPress post ID)
					$permalink = get_permalink( $post->ID );
					$heatmap_page_id = null;

					if ( $permalink ) {
						global $wpdb;
						// Try exact match first
						$heatmap_page_id = $wpdb->get_var( $wpdb->prepare(
							"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
							$permalink
						) );

						// If exact match not found, try with trailing slash variations
						if ( ! $heatmap_page_id ) {
							$url_alt = rtrim( $permalink, '/' ) . '/';
							if ( $url_alt === $permalink ) {
								$url_alt = rtrim( $permalink, '/' );
							}
							$heatmap_page_id = $wpdb->get_var( $wpdb->prepare(
								"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
								$url_alt
							) );
						}
					}

					// Only show View Heatmap button if we have a valid heatmap page ID
					if ( $heatmap_page_id ) :
						$heatmap_url = add_query_arg(
							array(
								'page'    => 'opti-behavior-heatmap-detail',
								'page_id' => $heatmap_page_id,
								'type'    => 'click',
								'device'  => 'desktop',
							),
							admin_url( 'admin.php' )
						);
					?>
						<div class="optibehavior-heatmap-links">
							<a href="<?php echo esc_url( $heatmap_url ); ?>" class="optibehavior-heatmap-link" target="_blank">
								<i data-lucide="flame" style="width:16px;height:16px;vertical-align:middle;"></i>
								<span><?php echo esc_html__( 'View Heatmap', 'opti-behavior' ); ?></span>
							</a>
							<?php opti_behavior_tooltip_e( $metabox_tooltips['view_heatmap']['title'], $metabox_tooltips['view_heatmap']['content'], $metabox_tooltips['view_heatmap']['simple'], '', array( 'position' => 'bottom' ) ); ?>
						</div>
					<?php endif; ?>
					<div class="optibehavior-time-filters">
						<a data-range="24h" class="optibehavior-time-filter active"><?php echo esc_html__( 'Last 24 Hours', 'opti-behavior' ); ?></a>
						<a data-range="7d" class="optibehavior-time-filter"><?php echo esc_html__( 'Last 7 Days', 'opti-behavior' ); ?></a>
						<a data-range="30d" class="optibehavior-time-filter"><?php echo esc_html__( 'Last 30 Days', 'opti-behavior' ); ?></a>
						<a data-range="90d" class="optibehavior-time-filter"><?php echo esc_html__( 'Last 3 Months', 'opti-behavior' ); ?></a>
						<a data-range="all" class="optibehavior-time-filter"><?php echo esc_html__( 'All Time', 'opti-behavior' ); ?></a>
					</div>
					<?php if ( $heatmap_page_id ) : ?>
						<div class="optibehavior-action-buttons">
							<?php if ( opti_behavior_pro_active() ) : ?>
								<?php
								$recordings_url = add_query_arg(
									array(
										'page'    => 'opti-behavior-recordings',
										'page_id' => $heatmap_page_id,
									),
									admin_url( 'admin.php' )
								);
								?>
								<a href="<?php echo esc_url( $recordings_url ); ?>" class="optibehavior-heatmap-link" target="_blank">
									<i data-lucide="video" style="width:16px;height:16px;vertical-align:middle;"></i>
									<span><?php echo esc_html__( 'View Recordings', 'opti-behavior' ); ?></span>
								</a>
								<?php opti_behavior_tooltip_e( $metabox_tooltips['view_recordings']['title'], $metabox_tooltips['view_recordings']['content'], $metabox_tooltips['view_recordings']['simple'], '', array( 'position' => 'bottom', 'pro' => true ) ); ?>
							<?php else : ?>
								<a href="#" class="optibehavior-heatmap-link optibehavior-pro-only" title="<?php echo esc_attr__( 'Upgrade to Pro to view session recordings', 'opti-behavior' ); ?>">
									<i data-lucide="video" style="width:16px;height:16px;vertical-align:middle;"></i>
									<span><?php echo esc_html__( 'Session Recordings', 'opti-behavior' ); ?></span>
									<span class="optibehavior-pro-badge">PRO</span>
								</a>
								<?php opti_behavior_tooltip_e( $metabox_tooltips['view_recordings']['title'], $metabox_tooltips['view_recordings']['content'], $metabox_tooltips['view_recordings']['simple'], '', array( 'position' => 'bottom', 'pro' => true ) ); ?>
							<?php endif; ?>
						</div>
					<?php endif; ?>
					<!-- Hidden elements for JavaScript compatibility -->
					<div style="display: none;">
						<a id="optibehavior-view-pc" href="#" target="_blank"><span></span></a>
						<a id="optibehavior-view-mob" href="#" target="_blank"><span></span></a>
					</div>
				</div>
				<div id="optibehavior-chart-loading" class="optibehavior-loading-state" style="display: flex; flex-direction: column; align-items: center; justify-content: center; padding: 40px 20px; gap: 12px;">
					<span class="spinner is-active"></span>
					<span style="color: #64748b; font-size: 14px;"><?php echo esc_html__( 'Loading analytics data...', 'opti-behavior' ); ?></span>
				</div>
				<canvas id="optibehavior-chart" height="280" style="display: none;"></canvas>
				<div id="optibehavior-empty" class="optibehavior-empty-state" style="display: none;">
					<i data-lucide="bar-chart-3" style="width: 48px; height: 48px; color: #9ca3af; stroke-width: 1.5;"></i>
					<div class="optibehavior-empty-title"><?php echo esc_html__( 'No heatmap data yet', 'opti-behavior' ); ?></div>
					<div class="optibehavior-empty-sub"><?php echo esc_html__( 'Data will appear once visitors access this page.', 'opti-behavior' ); ?></div>
				</div>
			</div>
		</div>
		<?php
		// Note: Metabox analytics script is now properly enqueued via wp_enqueue_script() in enqueue_metabox_assets() method
		// Script data is localized via wp_localize_script() in enqueue_metabox_assets() method
	}

	/**
	 * AJAX handler for editor analytics box
	 */
	public function ajax_post_analytics() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'No post_id provided', 'opti-behavior' ) ) );
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

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

		// Check if post is published - only show analytics for published posts
		$post_status = get_post_status( $post_id );
		if ( 'publish' !== $post_status ) {
			wp_send_json_error( array(
				'message' => esc_html__( 'Post not published yet', 'opti-behavior' ),
				'not_published' => true,
				'post_status' => $post_status
			) );
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error( array( 'message' => esc_html__( 'No permalink found', 'opti-behavior' ) ) );
		}

		// Get page_id for this URL (needed for file storage)
		$page_id_for_events = $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
			$url
		) );

		// If exact match not found, try with trailing slash variations
		if ( ! $page_id_for_events ) {
			$url_alt = rtrim( $url, '/' ) . '/';
			if ( $url_alt === $url ) {
				$url_alt = rtrim( $url, '/' );
			}
			$page_id_for_events = $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
				$url_alt
			) );
		}

		// Check storage mode
		$file_storage = $this->core->get_file_storage();
		$storage_settings = $file_storage ? $file_storage->get_settings() : array( 'storage_mode' => 'database' );
		$is_file_storage = ( $storage_settings['storage_mode'] === 'file' && $file_storage && $page_id_for_events );

		// Get PC clicks - use URL-based filtering to ensure all events are counted
		if ( $is_file_storage && $page_id_for_events ) {
			$pc = intval( $file_storage->count_events( $page_id_for_events, 16 ) );
		} else {
			$pc = 0;
		}
		// Always verify with database query for consistency
		$pc_db = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->prefix}optibehavior_events e JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id WHERE e.event=16 AND (p.url=%s OR p.url LIKE %s)",
			$url, $url . '%' ) ) );
		$pc = max( $pc, $pc_db );

		// Get mobile clicks - use URL-based filtering to ensure all events are counted
		if ( $is_file_storage && $page_id_for_events ) {
			$mb = intval( $file_storage->count_events( $page_id_for_events, 17 ) );
		} else {
			$mb = 0;
		}
		// Always verify with database query for consistency
		$mb_db = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->prefix}optibehavior_events e JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id WHERE e.event=17 AND (p.url=%s OR p.url LIKE %s)",
			$url, $url . '%' ) ) );
		$mb = max( $mb, $mb_db );

		$total = $pc + $mb;
		// Note: Outbound clicks are already included in PC/MB event counts (events 16/17)
		// The optibehavior_outbound_clicks table tracks WHERE users clicked (which links)
		// but the click events themselves are recorded in the events table as event 16 or 17

		// Get average time on page - try multiple approaches
		$avg_time = 0;

		// First try: get directly from pageviews table where time_on_page > 0 (most accurate)
		$avg_time = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT AVG(time_on_page) FROM {$wpdb->prefix}optibehavior_pageviews
			 WHERE (url=%s OR url LIKE %s) AND time_on_page > 0",
			$url, $url . '%' ) ) );

		// Second try: calculate from session timestamps for sessions with missing duration
		if ( $avg_time <= 0 ) {
			$avg_time = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT AVG(dur) FROM (
					SELECT GREATEST(30, TIMESTAMPDIFF(SECOND, s.start_time, COALESCE(s.end_time, NOW()))) AS dur
					FROM {$wpdb->prefix}optibehavior_sessions s
					JOIN {$wpdb->prefix}optibehavior_pageviews pv ON s.id = pv.session_id
					WHERE (pv.url=%s OR pv.url LIKE %s) AND s.start_time IS NOT NULL
					AND TIMESTAMPDIFF(SECOND, s.start_time, COALESCE(s.end_time, NOW())) BETWEEN 5 AND 7200
				) t",
				$url, $url . '%' ) ) );
		}

		// Third try: estimate from events time span per session (less accurate but better than 0)
		if ( $avg_time <= 0 ) {
			$avg_time = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT AVG(dur) FROM (
					SELECT GREATEST(30, TIMESTAMPDIFF(SECOND, MIN(e.insert_at), MAX(e.insert_at))) AS dur
					FROM {$wpdb->prefix}optibehavior_events e
					JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id
					WHERE (p.url=%s OR p.url LIKE %s) AND e.session_id IS NOT NULL
					GROUP BY e.session_id
					HAVING COUNT(e.id) > 1
				) t",
				$url, $url . '%' ) ) );
		}

		// If still no data, provide a reasonable default for pages with interactions
		if ( $avg_time <= 0 && $total > 0 ) {
			$avg_time = 45; // Default 45 seconds for pages with clicks but no time data
		}

		// Get last updated timestamp from multiple sources
		$last_from_events = null;
		if ( $is_file_storage ) {
			// Get latest event from file storage
			$pc_events = $file_storage->read_events( $page_id_for_events, 16, 1 );
			$mb_events = $file_storage->read_events( $page_id_for_events, 17, 1 );

			$pc_time = ! empty( $pc_events ) && isset( $pc_events[0]['insert_at'] ) ? $pc_events[0]['insert_at'] : null;
			$mb_time = ! empty( $mb_events ) && isset( $mb_events[0]['insert_at'] ) ? $mb_events[0]['insert_at'] : null;

			if ( $pc_time && $mb_time ) {
				$last_from_events = ( strtotime( $pc_time ) > strtotime( $mb_time ) ) ? $pc_time : $mb_time;
			} elseif ( $pc_time ) {
				$last_from_events = $pc_time;
			} elseif ( $mb_time ) {
				$last_from_events = $mb_time;
			}
		} else {
			$last_from_events = $wpdb->get_var( $wpdb->prepare(
				"SELECT MAX(e.insert_at) FROM {$wpdb->prefix}optibehavior_events e JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id WHERE p.url=%s OR p.url LIKE %s",
				$url, $url . '%' ) );
		}

		$last_from_pageviews = $wpdb->get_var( $wpdb->prepare(
			"SELECT MAX(view_time) FROM {$wpdb->prefix}optibehavior_pageviews WHERE url=%s OR url LIKE %s",
			$url, $url . '%' ) );

		$last_from_outbound = null;
		if ( $page_id_for_events ) {
			$last_from_outbound = $wpdb->get_var( $wpdb->prepare(
				"SELECT MAX(created_at) FROM {$wpdb->prefix}optibehavior_outbound_clicks WHERE page_id=%d",
				$page_id_for_events
			) );
		}

		// Get the most recent timestamp from all sources
		$timestamps = array_filter( array( $last_from_events, $last_from_pageviews, $last_from_outbound ) );
		$last_updated_raw = ! empty( $timestamps ) ? max( $timestamps ) : null;

		// Format the timestamp for display
		$last_updated = null;
		if ( $last_updated_raw ) {
			$diff = current_time( 'timestamp' ) - strtotime( $last_updated_raw );
			if ( $diff < 60 ) {
				$last_updated = __( 'Just now', 'opti-behavior' );
			} elseif ( $diff < 3600 ) {
				/* translators: %d: number of minutes */
				$last_updated = sprintf( __( '%d min ago', 'opti-behavior' ), floor( $diff / 60 ) );
			} elseif ( $diff < 86400 ) {
				/* translators: %d: number of hours */
				$last_updated = sprintf( __( '%d hours ago', 'opti-behavior' ), floor( $diff / 3600 ) );
			} else {
				/* translators: %d: number of days */
				$last_updated = sprintf( __( '%d days ago', 'opti-behavior' ), floor( $diff / 86400 ) );
			}
		}

		$mobile_pct = $total > 0 ? round( ( $mb / $total ) * 100 ) : 0;

		// Get average scroll depth
		$avg_scroll_depth = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT AVG(scroll_depth) FROM {$wpdb->prefix}optibehavior_pageviews
			 WHERE (url=%s OR url LIKE %s) AND scroll_depth > 0",
			$url, $url . '%' ) ) );

		// Get bounce rate - sessions with only 1 pageview
		$total_sessions = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT pv.session_id) FROM {$wpdb->prefix}optibehavior_pageviews pv
			 WHERE (pv.url=%s OR pv.url LIKE %s)",
			$url, $url . '%' ) ) );

		$bounced_sessions = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT pv.session_id) FROM {$wpdb->prefix}optibehavior_pageviews pv
			 WHERE (pv.url=%s OR pv.url LIKE %s)
			   AND pv.session_id IN (
				   SELECT session_id FROM {$wpdb->prefix}optibehavior_pageviews
				   GROUP BY session_id
				   HAVING COUNT(*) = 1
			   )",
			$url, $url . '%' ) ) );

		$bounce_rate = $total_sessions > 0 ? round( ( $bounced_sessions / $total_sessions ) * 100 ) : 0;

		// Calculate new vs returning visitors
		// New visitors: visitors whose first_visit matches their current session
		// Returning visitors: visitors who have visited before (first_visit < current session)
		$new_visitors = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT pv.visitor_id)
			 FROM {$wpdb->prefix}optibehavior_pageviews pv
			 INNER JOIN {$wpdb->prefix}optibehavior_visitors v ON pv.visitor_id = v.id
			 WHERE (pv.url=%s OR pv.url LIKE %s)
			   AND v.visit_count = 1",
			$url, $url . '%'
		) ) );

		$returning_visitors = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT pv.visitor_id)
			 FROM {$wpdb->prefix}optibehavior_pageviews pv
			 INNER JOIN {$wpdb->prefix}optibehavior_visitors v ON pv.visitor_id = v.id
			 WHERE (pv.url=%s OR pv.url LIKE %s)
			   AND v.visit_count > 1",
			$url, $url . '%'
		) ) );

		$total_visitors = $new_visitors + $returning_visitors;
		$returning_pct = $total_visitors > 0 ? round( ( $returning_visitors / $total_visitors ) * 100 ) : 0;

		// Get page ID for heatmap viewing URLs using the analytics class method
		$analytics = $this->core->get_analytics();
		$page_id2 = $analytics->get_or_create_page_id( $url, get_the_title( $post_id ) );
		$pc_url = $page_id2 ? $url . ( ( strpos( $url, '?' ) === false ? '?' : '&' ) . 'opti-behavior=click_pc-' . $page_id2 ) : '';
		$mb_url = $page_id2 ? $url . ( ( strpos( $url, '?' ) === false ? '?' : '&' ) . 'opti-behavior=click_mobile-' . $page_id2 . '&mobile_view=1&vw=375&vh=667' ) : '';

		wp_send_json_success( array(
			'total_interactions' => $total,
			'pc_clicks' => $pc,
			'mobile_clicks' => $mb,
			'avg_time' => $avg_time,
			'avg_scroll_depth' => $avg_scroll_depth,
			'bounce_rate' => $bounce_rate,
			'mobile_pct' => $mobile_pct,
			'new_visitors' => $new_visitors,
			'returning_visitors' => $returning_visitors,
			'returning_pct' => $returning_pct,
			'last_updated' => $last_updated,
			'view_pc_url' => $pc_url,
			'view_mobile_url' => $mb_url,
		) );
	}

	/**
	 * AJAX provider for timeseries data (interactions over time)
	 */
	public function ajax_post_timeseries() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error();
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

		if ( ! current_user_can( 'edit_post', $post_id ) ) {
			wp_send_json_error();
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error();
		}

		$range = isset( $_POST['range'] ) ? sanitize_text_field( wp_unslash( $_POST['range'] ) ) : '7d';
		$start = null;
		$group_format = '%Y-%m-%d';

		switch ( $range ) {
			case '24h':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 24 * 3600 );
				$group_format = '%Y-%m-%d %H:00:00';
				break;
			case '7d':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 7 * 86400 );
				break;
			case '30d':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 30 * 86400 );
				break;
			case '90d':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 90 * 86400 );
				break;
			case 'all':
			default:
				$start = '1970-01-01 00:00:00';
		}

		// Get page_id2 for this URL
		$page_id2 = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
			$url
		) ) );

		if ( ! $page_id2 ) {
			// Try to find by URL pattern
			$page_id2 = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT e.page_id2 FROM {$wpdb->prefix}optibehavior_events e
				 JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id
				 WHERE (p.url=%s OR p.url LIKE %s)
				 GROUP BY e.page_id2
				 ORDER BY COUNT(*) DESC
				 LIMIT 1",
				$url, $url . '%'
			) ) );
		}

		// Build SQL based on group format to avoid variable interpolation
		if ( $page_id2 > 0 ) {
			if ( $group_format === '%Y-%m-%d %H:00:00' ) {
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d %%H:00:00') AS bucket,
							SUM(CASE WHEN e.event=16 THEN 1 ELSE 0 END) AS pc,
							SUM(CASE WHEN e.event=17 THEN 1 ELSE 0 END) AS mobile
						 FROM {$wpdb->prefix}optibehavior_events e
						 WHERE e.page_id2=%d AND e.insert_at >= %s
						 GROUP BY bucket
						 ORDER BY bucket ASC",
						$page_id2, $start
					),
					ARRAY_A
				);
			} else {
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d') AS bucket,
							SUM(CASE WHEN e.event=16 THEN 1 ELSE 0 END) AS pc,
							SUM(CASE WHEN e.event=17 THEN 1 ELSE 0 END) AS mobile
						 FROM {$wpdb->prefix}optibehavior_events e
						 WHERE e.page_id2=%d AND e.insert_at >= %s
						 GROUP BY bucket
						 ORDER BY bucket ASC",
						$page_id2, $start
					),
					ARRAY_A
				);
			}
		} else {
			if ( $group_format === '%Y-%m-%d %H:00:00' ) {
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d %%H:00:00') AS bucket,
							SUM(CASE WHEN e.event=16 THEN 1 ELSE 0 END) AS pc,
							SUM(CASE WHEN e.event=17 THEN 1 ELSE 0 END) AS mobile
						 FROM {$wpdb->prefix}optibehavior_events e
						 JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id
						 WHERE (p.url=%s OR p.url LIKE %s) AND e.insert_at >= %s
						 GROUP BY bucket
					 ORDER BY bucket ASC",
					$url, $url . '%', $start
				),
				ARRAY_A
			);
			} else {
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d') AS bucket,
							SUM(CASE WHEN e.event=16 THEN 1 ELSE 0 END) AS pc,
							SUM(CASE WHEN e.event=17 THEN 1 ELSE 0 END) AS mobile
						 FROM {$wpdb->prefix}optibehavior_events e
						 JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id
						 WHERE (p.url=%s OR p.url LIKE %s) AND e.insert_at >= %s
						 GROUP BY bucket
					 ORDER BY bucket ASC",
					$url, $url . '%', $start
				),
				ARRAY_A
			);
			}
		}
		$labels = array();
		$pc = array();
		$mobile = array();

		foreach ( $rows as $r ) {
			$labels[] = $r['bucket'];
			$pc[] = intval( $r['pc'] );
			$mobile[] = intval( $r['mobile'] );
		}

		wp_send_json_success( array(
			'labels' => $labels,
			'pc' => $pc,
			'mobile' => $mobile,
			'group' => $group_format
		) );
	}

	/**
	 * AJAX handler for enhanced post analytics chart data.
	 * Returns Total Visitors, Desktop Visitors, Desktop Events, Mobile Visitors, Mobile Events.
	 *
	 * @since 1.0.3
	 */
	public function ajax_post_analytics_chart() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error( array( 'message' => esc_html__( 'No post_id provided', 'opti-behavior' ) ) );
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

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

		// Check if post is published
		$post_status = get_post_status( $post_id );
		if ( 'publish' !== $post_status ) {
			wp_send_json_success( array(
				'labels' => array(),
				'total_visitors' => array(),
				'desktop_visitors' => array(),
				'mobile_visitors' => array(),
				'desktop_events' => array(),
				'mobile_events' => array(),
				'group' => '%Y-%m-%d',
			) );
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error( array( 'message' => esc_html__( 'No permalink found', 'opti-behavior' ) ) );
		}

		$range = isset( $_POST['range'] ) ? sanitize_text_field( wp_unslash( $_POST['range'] ) ) : '7d';
		$start = null;
		$group_format = '%Y-%m-%d';
		$fallback_start = null;

		switch ( $range ) {
			case '24h':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 24 * 3600 );
				$group_format = '%Y-%m-%d %H:00:00';
				$fallback_start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 7 * 86400 ); // Fallback to 7 days if no data in 24h
				break;
			case '7d':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 7 * 86400 );
				break;
			case '30d':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 30 * 86400 );
				break;
			case '90d':
				$start = gmdate( 'Y-m-d H:i:s', current_time( 'timestamp' ) - 90 * 86400 );
				break;
			case 'all':
			default:
				$start = '1970-01-01 00:00:00';
		}

		// Get page_id2 first for all queries
		$page_id2 = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
			$url
		) ) );

		// If not found, try with/without trailing slash
		if ( ! $page_id2 ) {
			$url_alt = rtrim( $url, '/' ) . '/';
			if ( $url_alt === $url ) {
				$url_alt = rtrim( $url, '/' );
			}
			$page_id2 = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
				$url_alt
			) ) );
		}

		// If still not found, try URL LIKE match
		if ( ! $page_id2 ) {
			$page_id2 = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url LIKE %s LIMIT 1",
				$url . '%'
			) ) );
		}

		// Get unique visitors and sessions (Total, Desktop and Mobile) over time
		// We need to get data from both pageviews AND events to ensure consistency
		if ( $group_format === '%Y-%m-%d %H:00:00' ) {
			// Get visitors from pageviews
			$pv_visitors_data = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT DATE_FORMAT(pv.view_time, '%%Y-%%m-%%d %%H:00:00') AS bucket,
						COUNT(DISTINCT pv.session_id) AS total_sessions,
						COUNT(DISTINCT CASE WHEN v.device_type = 'Desktop' THEN pv.session_id END) AS desktop_sessions,
						COUNT(DISTINCT CASE WHEN v.device_type = 'Mobile' THEN pv.session_id END) AS mobile_sessions
					 FROM {$wpdb->prefix}optibehavior_pageviews pv
					 LEFT JOIN {$wpdb->prefix}optibehavior_visitors v ON pv.visitor_id = v.id
					 WHERE (pv.url=%s OR pv.url LIKE %s) AND pv.view_time >= %s
					 GROUP BY bucket
					 ORDER BY bucket ASC",
					$url, $url . '%', $start
				),
				ARRAY_A
			);

			// Get visitors from events (for cases where pageview doesn't exist but events do)
			// Use the SAME approach as the working stats query - join with pages table by URL
			$ev_visitors_data = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d %%H:00:00') AS bucket,
						COUNT(DISTINCT e.session_id) AS total_sessions,
						COUNT(DISTINCT CASE WHEN v.device_type = 'Desktop' THEN e.session_id END) AS desktop_sessions,
						COUNT(DISTINCT CASE WHEN v.device_type = 'Mobile' THEN e.session_id END) AS mobile_sessions
					 FROM {$wpdb->prefix}optibehavior_events e
					 JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id
					 LEFT JOIN {$wpdb->prefix}optibehavior_sessions s ON e.session_id = s.id
					 LEFT JOIN {$wpdb->prefix}optibehavior_visitors v ON s.visitor_id = v.id
					 WHERE (p.url=%s OR p.url LIKE %s) AND e.insert_at >= %s
					 GROUP BY bucket
					 ORDER BY bucket ASC",
					$url, $url . '%', $start
				),
				ARRAY_A
			);
		} else {
			// Get visitors from pageviews
			$pv_visitors_data = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT DATE_FORMAT(pv.view_time, '%%Y-%%m-%%d') AS bucket,
						COUNT(DISTINCT pv.session_id) AS total_sessions,
						COUNT(DISTINCT CASE WHEN v.device_type = 'Desktop' THEN pv.session_id END) AS desktop_sessions,
						COUNT(DISTINCT CASE WHEN v.device_type = 'Mobile' THEN pv.session_id END) AS mobile_sessions
					 FROM {$wpdb->prefix}optibehavior_pageviews pv
					 LEFT JOIN {$wpdb->prefix}optibehavior_visitors v ON pv.visitor_id = v.id
					 WHERE (pv.url=%s OR pv.url LIKE %s) AND pv.view_time >= %s
					 GROUP BY bucket
					 ORDER BY bucket ASC",
					$url, $url . '%', $start
				),
				ARRAY_A
			);

			// Get visitors from events (for cases where pageview doesn't exist but events do)
			$ev_visitors_data = array();
			if ( $page_id2 > 0 ) {
				$ev_visitors_data = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d') AS bucket,
							COUNT(DISTINCT e.session_id) AS total_sessions,
							COUNT(DISTINCT CASE WHEN v.device_type = 'Desktop' THEN e.session_id END) AS desktop_sessions,
							COUNT(DISTINCT CASE WHEN v.device_type = 'Mobile' THEN e.session_id END) AS mobile_sessions
						 FROM {$wpdb->prefix}optibehavior_events e
						 LEFT JOIN {$wpdb->prefix}optibehavior_sessions s ON e.session_id = s.id
						 LEFT JOIN {$wpdb->prefix}optibehavior_visitors v ON s.visitor_id = v.id
						 WHERE e.page_id2=%d AND e.insert_at >= %s
						 GROUP BY bucket
						 ORDER BY bucket ASC",
						$page_id2, $start
					),
					ARRAY_A
				);
			}
		}

		// Merge pageview and event visitor data to get the maximum count
		$visitors_data_merged = array();
		foreach ( $pv_visitors_data as $row ) {
			$bucket = $row['bucket'];
			$visitors_data_merged[ $bucket ] = $row;
		}
		foreach ( $ev_visitors_data as $row ) {
			$bucket = $row['bucket'];
			if ( ! isset( $visitors_data_merged[ $bucket ] ) ) {
				$visitors_data_merged[ $bucket ] = $row;
			} else {
				// Take the maximum of both sources
				$visitors_data_merged[ $bucket ]['total_sessions'] = max(
					intval( $visitors_data_merged[ $bucket ]['total_sessions'] ),
					intval( $row['total_sessions'] )
				);
				$visitors_data_merged[ $bucket ]['desktop_sessions'] = max(
					intval( $visitors_data_merged[ $bucket ]['desktop_sessions'] ),
					intval( $row['desktop_sessions'] )
				);
				$visitors_data_merged[ $bucket ]['mobile_sessions'] = max(
					intval( $visitors_data_merged[ $bucket ]['mobile_sessions'] ),
					intval( $row['mobile_sessions'] )
				);
			}
		}
		$visitors_data = array_values( $visitors_data_merged );

		// Check storage mode for events
		$file_storage      = $this->core->get_file_storage();
		$storage_settings  = $file_storage ? $file_storage->get_settings() : array( 'storage_mode' => 'database' );
		$is_file_storage   = ( $storage_settings['storage_mode'] === 'file' && $file_storage && $page_id2 );

		// Get events (Desktop=16, Mobile=17) over time
		$events_data = array();
		if ( $page_id2 > 0 ) {
			if ( $is_file_storage ) {
				// Get events from file storage - need to read files and group by time buckets
				// Get all events from file storage using read_events (not get_events)
				// Parameters: page_id, event_id, limit (0=all), start_date, end_date
				$all_events_desktop = $file_storage->read_events( $page_id2, 16, 0, gmdate( 'Y-m-d', strtotime( $start ) ), gmdate( 'Y-m-d' ) );
				$all_events_mobile  = $file_storage->read_events( $page_id2, 17, 0, gmdate( 'Y-m-d', strtotime( $start ) ), gmdate( 'Y-m-d' ) );

				// If file storage returns no events, fallback to database
				if ( empty( $all_events_desktop ) && empty( $all_events_mobile ) ) {
					$is_file_storage = false; // Force database query
				} else {
					// Group events by time buckets
					$events_by_bucket = array();
					$start_timestamp  = strtotime( $start );

					// Process desktop events
				foreach ( $all_events_desktop as $event ) {
					// Events may have 'timestamp', 'time', 't', or 'insert_at' field
					$event_time = null;
					if ( isset( $event['timestamp'] ) ) {
						$event_time = is_numeric( $event['timestamp'] ) ? $event['timestamp'] : strtotime( $event['timestamp'] );
					} elseif ( isset( $event['insert_at'] ) ) {
						$event_time = is_numeric( $event['insert_at'] ) ? $event['insert_at'] : strtotime( $event['insert_at'] );
					} elseif ( isset( $event['time'] ) ) {
						$event_time = is_numeric( $event['time'] ) ? $event['time'] : strtotime( $event['time'] );
					} elseif ( isset( $event['t'] ) ) {
						$event_time = is_numeric( $event['t'] ) ? $event['t'] : strtotime( $event['t'] );
					}

					if ( $event_time && $event_time >= $start_timestamp ) {
						$bucket = gmdate( $group_format === '%Y-%m-%d %H:00:00' ? 'Y-m-d H:00:00' : 'Y-m-d', $event_time );
						if ( ! isset( $events_by_bucket[ $bucket ] ) ) {
							$events_by_bucket[ $bucket ] = array( 'desktop_events' => 0, 'mobile_events' => 0 );
						}
						$events_by_bucket[ $bucket ]['desktop_events']++;
					}
				}

				// Process mobile events
				foreach ( $all_events_mobile as $event ) {
					// Events may have 'timestamp', 'time', 't', or 'insert_at' field
					$event_time = null;
					if ( isset( $event['timestamp'] ) ) {
						$event_time = is_numeric( $event['timestamp'] ) ? $event['timestamp'] : strtotime( $event['timestamp'] );
					} elseif ( isset( $event['insert_at'] ) ) {
						$event_time = is_numeric( $event['insert_at'] ) ? $event['insert_at'] : strtotime( $event['insert_at'] );
					} elseif ( isset( $event['time'] ) ) {
						$event_time = is_numeric( $event['time'] ) ? $event['time'] : strtotime( $event['time'] );
					} elseif ( isset( $event['t'] ) ) {
						$event_time = is_numeric( $event['t'] ) ? $event['t'] : strtotime( $event['t'] );
					}

					if ( $event_time && $event_time >= $start_timestamp ) {
						$bucket = gmdate( $group_format === '%Y-%m-%d %H:00:00' ? 'Y-m-d H:00:00' : 'Y-m-d', $event_time );
						if ( ! isset( $events_by_bucket[ $bucket ] ) ) {
							$events_by_bucket[ $bucket ] = array( 'desktop_events' => 0, 'mobile_events' => 0 );
						}
						$events_by_bucket[ $bucket ]['mobile_events']++;
					}
				}

				// Convert to array format matching database results
				foreach ( $events_by_bucket as $bucket => $counts ) {
					$events_data[] = array(
						'bucket'         => $bucket,
						'desktop_events' => $counts['desktop_events'],
						'mobile_events'  => $counts['mobile_events'],
					);
				}

				// Sort by bucket
				usort( $events_data, function ( $a, $b ) {
					return strcmp( $a['bucket'], $b['bucket'] );
				} );
				}
			}

			if ( ! $is_file_storage ) {
				// Get events from database
				if ( $group_format === '%Y-%m-%d %H:00:00' ) {
					$events_data = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d %%H:00:00') AS bucket,
								SUM(CASE WHEN e.event=16 THEN 1 ELSE 0 END) AS desktop_events,
								SUM(CASE WHEN e.event=17 THEN 1 ELSE 0 END) AS mobile_events
							 FROM {$wpdb->prefix}optibehavior_events e
							 WHERE e.page_id2=%d AND e.insert_at >= %s
							 GROUP BY bucket
							 ORDER BY bucket ASC",
							$page_id2, $start
						),
						ARRAY_A
					);
				} else {
					$events_data = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d') AS bucket,
								SUM(CASE WHEN e.event=16 THEN 1 ELSE 0 END) AS desktop_events,
								SUM(CASE WHEN e.event=17 THEN 1 ELSE 0 END) AS mobile_events
							 FROM {$wpdb->prefix}optibehavior_events e
							 WHERE e.page_id2=%d AND e.insert_at >= %s
							 GROUP BY bucket
							 ORDER BY bucket ASC",
							$page_id2, $start
						),
						ARRAY_A
					);
				}
			}
		}

		// Merge all data by bucket (time period)
		$merged = array();

		foreach ( $visitors_data as $row ) {
			$bucket = $row['bucket'];
			if ( ! isset( $merged[ $bucket ] ) ) {
				$merged[ $bucket ] = array(
					'total_visitors' => 0,
					'desktop_visitors' => 0,
					'mobile_visitors' => 0,
					'desktop_events' => 0,
					'mobile_events' => 0,
				);
			}
			$merged[ $bucket ]['total_visitors'] = intval( $row['total_sessions'] );
			$merged[ $bucket ]['desktop_visitors'] = intval( $row['desktop_sessions'] );
			$merged[ $bucket ]['mobile_visitors'] = intval( $row['mobile_sessions'] );
		}

		foreach ( $events_data as $row ) {
			$bucket = $row['bucket'];
			if ( ! isset( $merged[ $bucket ] ) ) {
				$merged[ $bucket ] = array(
					'total_visitors' => 0,
					'desktop_visitors' => 0,
					'mobile_visitors' => 0,
					'desktop_events' => 0,
					'mobile_events' => 0,
				);
			}
			$merged[ $bucket ]['desktop_events'] = intval( $row['desktop_events'] );
			$merged[ $bucket ]['mobile_events'] = intval( $row['mobile_events'] );
		}

		// Sort by bucket and prepare output
		ksort( $merged );

		// If no data found and we have a fallback start time (24h range), retry with fallback
		if ( empty( $merged ) && isset( $fallback_start ) && $fallback_start ) {
			$start = $fallback_start;

			// Re-query visitors data with fallback time
			if ( $group_format === '%Y-%m-%d %H:00:00' ) {
				$pv_visitors_data = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT DATE_FORMAT(pv.view_time, '%%Y-%%m-%%d %%H:00:00') AS bucket,
							COUNT(DISTINCT pv.session_id) AS total_sessions,
							COUNT(DISTINCT CASE WHEN v.device_type = 'Desktop' THEN pv.session_id END) AS desktop_sessions,
							COUNT(DISTINCT CASE WHEN v.device_type = 'Mobile' THEN pv.session_id END) AS mobile_sessions
						 FROM {$wpdb->prefix}optibehavior_pageviews pv
						 LEFT JOIN {$wpdb->prefix}optibehavior_visitors v ON pv.visitor_id = v.id
						 WHERE (pv.url=%s OR pv.url LIKE %s) AND pv.view_time >= %s
						 GROUP BY bucket
						 ORDER BY bucket ASC",
						$url, $url . '%', $start
					),
					ARRAY_A
				);

				$ev_visitors_data = array();
				if ( $page_id2 > 0 ) {
					$ev_visitors_data = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d %%H:00:00') AS bucket,
								COUNT(DISTINCT e.session_id) AS total_sessions,
								COUNT(DISTINCT CASE WHEN v.device_type = 'Desktop' THEN e.session_id END) AS desktop_sessions,
								COUNT(DISTINCT CASE WHEN v.device_type = 'Mobile' THEN e.session_id END) AS mobile_sessions
							 FROM {$wpdb->prefix}optibehavior_events e
							 LEFT JOIN {$wpdb->prefix}optibehavior_sessions s ON e.session_id = s.id
							 LEFT JOIN {$wpdb->prefix}optibehavior_visitors v ON s.visitor_id = v.id
							 WHERE e.page_id2=%d AND e.insert_at >= %s
							 GROUP BY bucket
							 ORDER BY bucket ASC",
							$page_id2, $start
						),
						ARRAY_A
					);
				}

				// Merge visitor data
				$visitors_data_merged = array();
				foreach ( $pv_visitors_data as $row ) {
					$bucket = $row['bucket'];
					$visitors_data_merged[ $bucket ] = $row;
				}
				foreach ( $ev_visitors_data as $row ) {
					$bucket = $row['bucket'];
					if ( ! isset( $visitors_data_merged[ $bucket ] ) ) {
						$visitors_data_merged[ $bucket ] = $row;
					} else {
						$visitors_data_merged[ $bucket ]['total_sessions'] = max(
							intval( $visitors_data_merged[ $bucket ]['total_sessions'] ),
							intval( $row['total_sessions'] )
						);
						$visitors_data_merged[ $bucket ]['desktop_sessions'] = max(
							intval( $visitors_data_merged[ $bucket ]['desktop_sessions'] ),
							intval( $row['desktop_sessions'] )
						);
						$visitors_data_merged[ $bucket ]['mobile_sessions'] = max(
							intval( $visitors_data_merged[ $bucket ]['mobile_sessions'] ),
							intval( $row['mobile_sessions'] )
						);
					}
				}
				$visitors_data = array_values( $visitors_data_merged );

				// Re-query events data with fallback time
				if ( $page_id2 > 0 ) {
					$events_data = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT DATE_FORMAT(e.insert_at, '%%Y-%%m-%%d %%H:00:00') AS bucket,
								SUM(CASE WHEN e.event=16 THEN 1 ELSE 0 END) AS desktop_events,
								SUM(CASE WHEN e.event=17 THEN 1 ELSE 0 END) AS mobile_events
							 FROM {$wpdb->prefix}optibehavior_events e
							 WHERE e.page_id2=%d AND e.insert_at >= %s
							 GROUP BY bucket
							 ORDER BY bucket ASC",
							$page_id2, $start
						),
						ARRAY_A
					);
				}

				// Re-merge data
				$merged = array();
				foreach ( $visitors_data as $row ) {
					$bucket = $row['bucket'];
					if ( ! isset( $merged[ $bucket ] ) ) {
						$merged[ $bucket ] = array(
							'total_visitors' => 0,
							'desktop_visitors' => 0,
							'mobile_visitors' => 0,
							'desktop_events' => 0,
							'mobile_events' => 0,
						);
					}
					$merged[ $bucket ]['total_visitors'] = intval( $row['total_sessions'] );
					$merged[ $bucket ]['desktop_visitors'] = intval( $row['desktop_sessions'] );
					$merged[ $bucket ]['mobile_visitors'] = intval( $row['mobile_sessions'] );
				}

				foreach ( $events_data as $row ) {
					$bucket = $row['bucket'];
					if ( ! isset( $merged[ $bucket ] ) ) {
						$merged[ $bucket ] = array(
							'total_visitors' => 0,
							'desktop_visitors' => 0,
							'mobile_visitors' => 0,
							'desktop_events' => 0,
							'mobile_events' => 0,
						);
					}
					$merged[ $bucket ]['desktop_events'] = intval( $row['desktop_events'] );
					$merged[ $bucket ]['mobile_events'] = intval( $row['mobile_events'] );
				}

				ksort( $merged );
			}
		}

		$labels = array();
		$total_visitors = array();
		$desktop_visitors = array();
		$mobile_visitors = array();
		$desktop_events = array();
		$mobile_events = array();

		foreach ( $merged as $bucket => $data ) {
			$labels[] = $bucket;
			$total_visitors[] = $data['total_visitors'];
			$desktop_visitors[] = $data['desktop_visitors'];
			$mobile_visitors[] = $data['mobile_visitors'];
			$desktop_events[] = $data['desktop_events'];
			$mobile_events[] = $data['mobile_events'];
		}

		wp_send_json_success( array(
			'labels' => $labels,
			'total_visitors' => $total_visitors,
			'desktop_visitors' => $desktop_visitors,
			'mobile_visitors' => $mobile_visitors,
			'desktop_events' => $desktop_events,
			'mobile_events' => $mobile_events,
			'group' => $group_format,
		) );
	}

	/**
	 * AJAX handler for post referrers data
	 */
	public function ajax_post_referrers() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error();
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

		if ( ! current_user_can( 'edit_post', $post_id ) ) {
			wp_send_json_error();
		}

		// Check if post is published
		$post_status = get_post_status( $post_id );
		if ( 'publish' !== $post_status ) {
			wp_send_json_success( array() );
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error();
		}

		// Resolve page_id robustly (handle normalized URL2 and slight URL variations)
		$page_id = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
			$url
		) ) );

		// If exact match not found, try with trailing slash variations
		if ( ! $page_id ) {
			$url_alt = rtrim( $url, '/' ) . '/';
			if ( $url_alt === $url ) {
				$url_alt = rtrim( $url, '/' );
			}
			$page_id = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
				$url_alt
			) ) );
		}

		if ( ! $page_id ) {
			// Try matching by normalized url2
			$database = $this->core->get_database();
			$options  = $this->core->get_options();
			$query_filter    = $database->make_url_filter( true );
			$fragment_filter = $options['keep_url_hash'];
			$url2 = $database->rebuild_url( $url, $query_filter, $fragment_filter );

			$page_id = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url2=%s LIMIT 1",
				$url2
			) ) );
		}

		if ( ! $page_id ) {
			// Fallback: infer most active page_id2 for this URL pattern
			$page_id = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT e.page_id2 FROM {$wpdb->prefix}optibehavior_events e
				 JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id
				 WHERE (p.url=%s OR p.url LIKE %s)
				 GROUP BY e.page_id2
				 ORDER BY COUNT(*) DESC
				 LIMIT 1",
				$url, $url . '%'
			) ) );
		}

		if ( ! $page_id ) {
			wp_send_json_success( array() );
		}

		$session = $this->core->get_session();
		$referrers = $session->get_page_referrers( $page_id, 10 );

		wp_send_json_success( $referrers );
	}

	/**
	 * AJAX handler for post outbound clicks data
	 */
	public function ajax_post_outbound_clicks() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error();
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

		if ( ! current_user_can( 'edit_post', $post_id ) ) {
			wp_send_json_error();
		}

		// Check if post is published
		$post_status = get_post_status( $post_id );
		if ( 'publish' !== $post_status ) {
			wp_send_json_success( array() );
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error();
		}

		// Resolve page_id robustly (handle normalized URL2 and slight URL variations)
		$page_id = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
			$url
		) ) );

		// If exact match not found, try with trailing slash variations
		if ( ! $page_id ) {
			$url_alt = rtrim( $url, '/' ) . '/';
			if ( $url_alt === $url ) {
				$url_alt = rtrim( $url, '/' );
			}
			$page_id = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url=%s LIMIT 1",
				$url_alt
			) ) );
		}

		if ( ! $page_id ) {
			// Try matching by normalized url2
			$database = $this->core->get_database();
			$options  = $this->core->get_options();
			$query_filter    = $database->make_url_filter( true );
			$fragment_filter = $options['keep_url_hash'];
			$url2 = $database->rebuild_url( $url, $query_filter, $fragment_filter );

			$page_id = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}optibehavior_pages WHERE url2=%s LIMIT 1",
				$url2
			) ) );
		}

		if ( ! $page_id ) {
			// Fallback: infer most active page_id2 for this URL pattern
			$page_id = intval( $wpdb->get_var( $wpdb->prepare(
				"SELECT e.page_id2 FROM {$wpdb->prefix}optibehavior_events e
				 JOIN {$wpdb->prefix}optibehavior_pages p ON e.page_id2=p.id
				 WHERE (p.url=%s OR p.url LIKE %s)
				 GROUP BY e.page_id2
				 ORDER BY COUNT(*) DESC
				 LIMIT 1",
				$url, $url . '%'
			) ) );
		}

		if ( ! $page_id ) {
			wp_send_json_success( array() );
		}

		$session = $this->core->get_session();
		$outbound_clicks = $session->get_page_outbound_clicks( $page_id, 10 );

		// Count sessions that truly left without clicking any link
		$total_sessions = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT pv.session_id)
			 FROM {$wpdb->prefix}optibehavior_pageviews pv
			 WHERE pv.url=%s OR pv.url LIKE %s",
			$url, $url . '%'
		) ) );

		$sessions_with_clicks = intval( $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(DISTINCT oc.session_id)
			 FROM {$wpdb->prefix}optibehavior_outbound_clicks oc
			 WHERE oc.page_id=%d",
			$page_id
		) ) );

		$sessions_left_without_click = $total_sessions - $sessions_with_clicks;

		// Only add "Left page" if there are sessions that actually closed the page without clicking
		if ( $sessions_left_without_click > 0 ) {
			// Get the most recent timestamp from sessions that left without clicking
			$last_left = $wpdb->get_var( $wpdb->prepare(
				"SELECT MAX(pv.view_time)
				 FROM {$wpdb->prefix}optibehavior_pageviews pv
				 WHERE (pv.url=%s OR pv.url LIKE %s)
				 AND pv.session_id NOT IN (
					 SELECT DISTINCT session_id
					 FROM {$wpdb->prefix}optibehavior_outbound_clicks
					 WHERE page_id=%d
				 )",
				$url, $url . '%', $page_id
			) );

			$outbound_clicks[] = array(
				'target_url'  => 'Left page',
				'click_type'  => 'left',
				'element_tag' => null,
				'element_text'=> null,
				'count'       => $sessions_left_without_click,
				'last_click'  => $last_left ?: current_time( 'mysql' ),
			);
		}

		// Collapse duplicates (same click_type + target_url) to avoid multiple "Left page" rows
		if ( is_array( $outbound_clicks ) ) {
			$collapsed = array();
			foreach ( $outbound_clicks as $o ) {
				$ctype = isset( $o['click_type'] ) ? (string) $o['click_type'] : '';
				$turl  = isset( $o['target_url'] ) ? (string) $o['target_url'] : '';
				$key   = $ctype . '|' . $turl;
				if ( ! isset( $collapsed[ $key ] ) ) {
					$collapsed[ $key ] = $o;
					$collapsed[ $key ]['count'] = intval( $o['count'] ?? 0 );
				} else {
					$collapsed[ $key ]['count'] += intval( $o['count'] ?? 0 );
					// Keep the most recent timestamp if available
					if ( isset( $o['last_click'] ) && isset( $collapsed[ $key ]['last_click'] ) ) {
						$collapsed[ $key ]['last_click'] = max( $collapsed[ $key ]['last_click'], $o['last_click'] );
					}
				}
			}
			$outbound_clicks = array_values( $collapsed );
			// Sort by count desc then last_click desc if present
			usort( $outbound_clicks, function ( $a, $b ) {
				$ca = intval( $a['count'] ?? 0 );
				$cb = intval( $b['count'] ?? 0 );
				if ( $ca === $cb ) {
					$la = isset( $a['last_click'] ) ? strtotime( $a['last_click'] ) : 0;
					$lb = isset( $b['last_click'] ) ? strtotime( $b['last_click'] ) : 0;
					return $lb <=> $la;
				}
				return $cb <=> $ca;
			} );
		}

		wp_send_json_success( $outbound_clicks );
	}

	/**
	 * AJAX handler for post countries data
	 */
	public function ajax_post_countries() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error();
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

		if ( ! current_user_can( 'edit_post', $post_id ) ) {
			wp_send_json_error();
		}

		// Check if post is published
		$post_status = get_post_status( $post_id );
		if ( 'publish' !== $post_status ) {
			wp_send_json_success( array() );
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error();
		}

		// Get sessions for this URL and their visitor countries
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT UPPER(COALESCE(NULLIF(TRIM(vis.country),''), '')) AS country,
					vis.country_name,
					COUNT(DISTINCT s.id) AS count
			 FROM {$wpdb->prefix}optibehavior_sessions s
			 LEFT JOIN {$wpdb->prefix}optibehavior_visitors vis ON s.visitor_id = vis.id
			 LEFT JOIN {$wpdb->prefix}optibehavior_pageviews pv ON s.id = pv.session_id
			 WHERE (pv.url=%s OR pv.url LIKE %s)
			   AND COALESCE(NULLIF(TRIM(vis.country),''),'UN') <> 'UN'
			 GROUP BY country, vis.country_name
			 ORDER BY count DESC
			 LIMIT 10",
			$url, $url . '%'
		) );

		$data = array();
		foreach ( $results as $row ) {
			$code = strtoupper( $row->country ?: '' );
			$name = $row->country_name ?: ( $code ?: 'Global' );
			$data[] = array(
				'country' => $code ?: 'Global',
				'country_name' => $name,
				'count' => intval( $row->count ),
			);
		}

		wp_send_json_success( $data );
	}

	/**
	 * AJAX handler for post browsers data
	 */
	public function ajax_post_browsers() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error();
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

		if ( ! current_user_can( 'edit_post', $post_id ) ) {
			wp_send_json_error();
		}

		// Check if post is published
		$post_status = get_post_status( $post_id );
		if ( 'publish' !== $post_status ) {
			wp_send_json_success( array() );
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error();
		}

		// Get browsers for sessions viewing this URL
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT
				CASE
					WHEN vis.browser IS NULL OR TRIM(vis.browser) = '' THEN 'Unknown'
					WHEN LOWER(vis.browser) IN ('unknown','undefined','other') THEN 'Unknown'
					ELSE TRIM(vis.browser)
				END AS browser,
				COUNT(DISTINCT s.id) AS count
			 FROM {$wpdb->prefix}optibehavior_sessions s
			 LEFT JOIN {$wpdb->prefix}optibehavior_visitors vis ON s.visitor_id = vis.id
			 LEFT JOIN {$wpdb->prefix}optibehavior_pageviews pv ON s.id = pv.session_id
			 WHERE (pv.url=%s OR pv.url LIKE %s)
			 GROUP BY browser
			 ORDER BY count DESC
			 LIMIT 10",
			$url, $url . '%'
		) );

		$data = array();
		foreach ( $results as $row ) {
			$data[] = array(
				'name' => $row->browser ?: 'Unknown',
				'count' => intval( $row->count ),
			);
		}

		wp_send_json_success( $data );
	}

	/**
	 * AJAX handler for post devices data
	 */
	public function ajax_post_devices() {
		if ( ! isset( $_POST['post_id'] ) ) {
			wp_send_json_error();
		}

		$post_id = intval( $_POST['post_id'] );
		check_ajax_referer( 'optibehavior_post_analytics_' . $post_id, 'nonce' );

		if ( ! current_user_can( 'edit_post', $post_id ) ) {
			wp_send_json_error();
		}

		// Check if post is published
		$post_status = get_post_status( $post_id );
		if ( 'publish' !== $post_status ) {
			wp_send_json_success( array() );
		}

		global $wpdb;
		$url = get_permalink( $post_id );
		if ( ! $url ) {
			wp_send_json_error();
		}

		// Get device types for sessions viewing this URL
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT
				CASE
					WHEN vis.device_type IS NULL OR TRIM(vis.device_type) = '' THEN 'Unknown'
					WHEN LOWER(vis.device_type) IN ('unknown','undefined','other') THEN 'Unknown'
					ELSE TRIM(vis.device_type)
				END AS device,
				COUNT(DISTINCT s.id) AS count
			 FROM {$wpdb->prefix}optibehavior_sessions s
			 LEFT JOIN {$wpdb->prefix}optibehavior_visitors vis ON s.visitor_id = vis.id
			 LEFT JOIN {$wpdb->prefix}optibehavior_pageviews pv ON s.id = pv.session_id
			 WHERE (pv.url=%s OR pv.url LIKE %s)
			 GROUP BY device
			 ORDER BY count DESC
			 LIMIT 10",
			$url, $url . '%'
		) );

		$data = array();
		foreach ( $results as $row ) {
			$data[] = array(
				'name' => $row->device ?: 'Unknown',
				'count' => intval( $row->count ),
			);
		}

		wp_send_json_success( $data );
	}
}
