<?php
/**
 * Funnel Analytics Page Class
 *
 * Handles funnel visualization, conversion tracking, and user journey analytics.
 *
 * @package opti-behavior
 * @copyright 2025 OptiUser
 * @version 1.0.0
 */

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

// Load funnel views trait and database class.
require_once OPTI_BEHAVIOR_HEATMAP_INCLUDES_DIR . 'trait-opti-behavior-funnels-views.php';
require_once OPTI_BEHAVIOR_HEATMAP_INCLUDES_DIR . 'class-opti-behavior-funnel-database.php';

/**
 * Funnel Analytics Page Class
 *
 * @since 1.0.0
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
class Opti_Behavior_Funnel_Page {
	use Opti_Behavior_Funnels_Views_Trait;

	/**
	 * opti-behavior Heatmap instance
	 *
	 * @var Opti_Behavior_Heatmap_Core
	 */
	private $heatmap;

	/**
	 * Constructor
	 *
	 * @param Opti_Behavior_Heatmap_Core $heatmap opti-behavior Heatmap instance.
	 */
	public function __construct( $heatmap ) {
		$this->heatmap = $heatmap;
		$this->init_database();
		$this->init_hooks();
	}

	/**
	 * Initialize database tables
	 */
	private function init_database() {
		Opti_Behavior_Funnel_Database::create_tables();
	}

	/**
	 * Initialize hooks
	 */
	private function init_hooks() {
		add_action( 'admin_enqueue_scripts', array( $this, 'enqueue_funnel_assets' ) );
		add_action( 'wp_ajax_optibehavior_funnel_data', array( $this, 'ajax_funnel_data' ) );
		add_action( 'wp_ajax_optibehavior_get_funnels', array( $this, 'ajax_get_funnels' ) );
		add_action( 'wp_ajax_optibehavior_get_funnel', array( $this, 'ajax_get_funnel' ) );
		add_action( 'wp_ajax_optibehavior_save_funnel', array( $this, 'ajax_save_funnel' ) );
		add_action( 'wp_ajax_optibehavior_delete_funnel', array( $this, 'ajax_delete_funnel' ) );
		add_action( 'wp_ajax_optibehavior_reset_funnel_data', array( $this, 'ajax_reset_funnel_data' ) );
		add_action( 'wp_ajax_optibehavior_get_funnel_countries', array( $this, 'ajax_get_funnel_countries' ) );

		// Frontend tracking
		add_action( 'wp_enqueue_scripts', array( $this, 'enqueue_frontend_tracking' ) );
		add_action( 'wp_ajax_optibehavior_track_funnel', array( $this, 'ajax_track_funnel' ) );
		add_action( 'wp_ajax_nopriv_optibehavior_track_funnel', array( $this, 'ajax_track_funnel' ) );

		// Universal URL tracking - tracks EVERY WordPress request
		add_action( 'wp', array( $this, 'track_all_requests' ), 1 );

		// Prevent Autoptimize from merging/concatenating the funnel tracker script.
		add_filter( 'autoptimize_filter_js_exclude', array( $this, 'exclude_from_autoptimize' ) );

		// LiteSpeed Cache: exclude funnel-tracker from JS combine and defer.
		add_filter( 'litespeed_optimize_js_excludes', array( $this, 'litespeed_js_excludes' ) );
		add_filter( 'litespeed_optm_js_defer_exc',    array( $this, 'litespeed_js_excludes' ) );

		// WP Rocket: exclude from JS combine and delay-JS execution.
		add_filter( 'rocket_exclude_js',          array( $this, 'wp_rocket_js_excludes' ) );
		add_filter( 'rocket_delay_js_exclusions', array( $this, 'wp_rocket_delay_exclusions' ) );

		// SG Optimizer: exclude funnel-tracker from JS combine.
		add_filter( 'sgo_javascript_combine_exclude', array( $this, 'sgo_js_excludes' ) );
	}

	/**
	 * Exclude funnel-tracker from Autoptimize JS aggregation.
	 *
	 * Autoptimize's "Aggregate JS" option may merge this script into a bundle,
	 * separating it from the inline wp_localize_script variables it depends on.
	 *
	 * @param string $exclude Comma-separated list of exclusion patterns.
	 * @return string Updated exclusion list.
	 */
	public function exclude_from_autoptimize( $exclude ) {
		$exclude .= ', funnel-tracker.js';
		return $exclude;
	}

	/**
	 * Exclude funnel-tracker from LiteSpeed Cache JS optimization.
	 *
	 * @param array $excludes Current exclusion list.
	 * @return array Updated exclusion list.
	 */
	public function litespeed_js_excludes( $excludes ) {
		$excludes[] = 'funnel-tracker.js';
		return $excludes;
	}

	/**
	 * Exclude funnel-tracker from WP Rocket JS combine.
	 *
	 * @param array $excludes Current exclusion list.
	 * @return array Updated exclusion list.
	 */
	public function wp_rocket_js_excludes( $excludes ) {
		$excludes[] = '/opti-behavior(.*)funnel-tracker\.js/';
		return $excludes;
	}

	/**
	 * Exclude funnel-tracker from WP Rocket delay-JS execution.
	 *
	 * @param array $exclusions Current exclusion list.
	 * @return array Updated exclusion list.
	 */
	public function wp_rocket_delay_exclusions( $exclusions ) {
		$exclusions[] = 'funnel-tracker.js';
		return $exclusions;
	}

	/**
	 * Exclude funnel-tracker from SG Optimizer JS combine.
	 *
	 * @param array $excludes Current exclusion list.
	 * @return array Updated exclusion list.
	 */
	public function sgo_js_excludes( $excludes ) {
		$excludes[] = 'funnel-tracker.js';
		return $excludes;
	}

	/**
	 * Enqueue funnel page assets
	 *
	 * @param string $hook_suffix Current admin page.
	 */
	public function enqueue_funnel_assets( $hook_suffix ) {
		// Only load on funnel page.
		if ( 'opti-behavior_page_opti-behavior-funnels' !== $hook_suffix ) {
			return;
		}

		// Enqueue Chart.js (already registered by dashboard).
		wp_enqueue_script( 'chart-js' );

		// Enqueue flag-icons CSS for country flags (bundled locally).
		wp_enqueue_style(
			'flag-icons',
			plugins_url( 'assets/css/flag-icons.min.css', dirname( __FILE__ ) ),
			array(),
			'7.2.3'
		);

		// Enqueue heatmaps CSS for header styles.
		wp_enqueue_style(
			'opti-behavior-heatmaps',
			plugins_url( 'assets/css/heatmaps.css', dirname( __FILE__ ) ),
			array( 'opti-behavior-dashboard' ),
			OPTI_BEHAVIOR_HEATMAP_VERSION
		);

		// Enqueue funnel CSS.
		wp_enqueue_style(
			'opti-behavior-funnels',
			plugins_url( 'assets/css/funnels.css', dirname( __FILE__ ) ),
			array( 'opti-behavior-dashboard', 'opti-behavior-heatmaps' ),
			OPTI_BEHAVIOR_HEATMAP_VERSION . '-' . time()
		);

		// Enqueue funnel JavaScript.
		wp_enqueue_script(
			'opti-behavior-funnels',
			plugins_url( 'assets/js/funnels.js', dirname( __FILE__ ) ),
			array( 'jquery', 'chart-js' ),
			OPTI_BEHAVIOR_HEATMAP_VERSION . '-' . time(),
			true
		);

		// Localize script with AJAX URL and nonce.
		wp_localize_script(
			'opti-behavior-funnels',
			'optiBehaviorFunnels',
			array(
				'ajaxUrl' => admin_url( 'admin-ajax.php' ),
				'nonce'   => wp_create_nonce( 'opti_behavior_funnels' ),
				'strings' => array(
					'loading'        => __( 'Loading...', 'opti-behavior' ),
					'noData'         => __( 'No data available', 'opti-behavior' ),
					'error'          => __( 'Error loading data', 'opti-behavior' ),
					'step'           => __( 'Step', 'opti-behavior' ),
					'steps'          => __( 'Steps', 'opti-behavior' ),
					'completed'      => __( 'Completed', 'opti-behavior' ),
					'abandoned'      => __( 'Abandoned', 'opti-behavior' ),
					'filters'        => __( 'Filters', 'opti-behavior' ),
					'conversionRate' => __( 'Conversion Rate', 'opti-behavior' ),
					'dropOffRate'    => __( 'Drop-off Rate', 'opti-behavior' ),
					'users'          => __( 'users', 'opti-behavior' ),
					'views'          => __( 'views', 'opti-behavior' ),
					'saveFunnel'     => __( 'Save Funnel', 'opti-behavior' ),
					'updateFunnel'   => __( 'Update Funnel', 'opti-behavior' ),
					'description'    => __( 'Description', 'opti-behavior' ),
					'stepNamePlaceholder' => __( 'Step name (e.g., Landing Page)', 'opti-behavior' ),
					'urlPatternPlaceholder' => __( 'URL pattern (e.g., /cart)', 'opti-behavior' ),
					'anyPage'        => __( 'Any Page', 'opti-behavior' ),
					'exactUrl'       => __( 'Exact URL', 'opti-behavior' ),
					'urlContains'    => __( 'URL Contains', 'opti-behavior' ),
					'urlStartsWith'  => __( 'URL Starts With', 'opti-behavior' ),
					'urlEndsWith'    => __( 'URL Ends With', 'opti-behavior' ),
					'regex'          => __( 'Regex', 'opti-behavior' ),
					'anyPageView'    => __( 'Any Page View', 'opti-behavior' ),
					'funnelSavedSuccess' => __( 'Funnel saved successfully!', 'opti-behavior' ),
					'sevenDays'      => __( '7 Days', 'opti-behavior' ),
					'thirtyDays'     => __( '30 Days', 'opti-behavior' ),
					'ninetyDays'     => __( '90 Days', 'opti-behavior' ),
					'customRange'    => __( 'Custom Range', 'opti-behavior' ),
					'allDevices'     => __( 'All Devices', 'opti-behavior' ),
					'desktop'        => __( 'Desktop', 'opti-behavior' ),
					'mobile'         => __( 'Mobile', 'opti-behavior' ),
					'tablet'         => __( 'Tablet', 'opti-behavior' ),
					'allCountries'   => __( 'All Countries', 'opti-behavior' ),
					'searchCountries' => __( 'Search countries...', 'opti-behavior' ),
					'clear'          => __( 'Clear', 'opti-behavior' ),
					'apply'          => __( 'Apply', 'opti-behavior' ),
					'editFunnel'            => __( 'Edit Funnel', 'opti-behavior' ),
					'duplicate'             => __( 'Duplicate', 'opti-behavior' ),
					'resetData'             => __( 'Reset Data', 'opti-behavior' ),
					'delete'                => __( 'Delete', 'opti-behavior' ),
					'resetDataSuccess'      => __( 'Funnel data has been reset successfully. The funnel configuration (steps and URLs) has been preserved.', 'opti-behavior' ),
					'resetDataError'        => __( 'Error resetting funnel data', 'opti-behavior' ),
					'resetDataNetworkError' => __( 'Error resetting funnel data. Please try again.', 'opti-behavior' ),
					'unknownError'          => __( 'Unknown error', 'opti-behavior' ),
				),
			)
		);
	}

	/**
	 * Enqueue frontend funnel tracking script
	 */
	public function enqueue_frontend_tracking() {
		// Don't load on admin pages
		if ( is_admin() ) {
			return;
		}

		// Get all active funnels
		global $wpdb;
		$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$funnels = $wpdb->get_results(
			"SELECT id, name, steps FROM {$table_funnels} WHERE status = 'active'",
			ARRAY_A
		);

		// If no active funnels, don't load tracking script
		if ( empty( $funnels ) ) {
			return;
		}

		// Decode steps for each funnel
		foreach ( $funnels as &$funnel ) {
			$funnel['steps'] = json_decode( $funnel['steps'], true );
		}

		// Enqueue funnel tracker script
		wp_enqueue_script(
			'opti-behavior-funnel-tracker',
			plugins_url( 'assets/js/funnel-tracker.js', dirname( __FILE__ ) ),
			array( 'opti-behavior-debug' ),
			OPTI_BEHAVIOR_HEATMAP_VERSION,
			true
		);

		// Localize script with funnel data
		wp_localize_script(
			'opti-behavior-funnel-tracker',
			'optiBehaviorFunnelTracker',
			array(
				'ajaxUrl' => admin_url( 'admin-ajax.php' ),
				'nonce'   => wp_create_nonce( 'opti_behavior_funnels' ),
				'funnels' => $funnels,
			)
		);
	}

	/**
	 * AJAX handler for funnel data
	 */
	public function ajax_funnel_data() {
		// Verify nonce.
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		// Check permissions.
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Permission denied' ) );
		}

		// Get period from request.
		$period = isset( $_POST['period'] ) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : '7days';
		$start_date = isset( $_POST['start_date'] ) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : '';
		$end_date = isset( $_POST['end_date'] ) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : '';
		$funnel_id = isset( $_POST['funnel_id'] ) ? intval( $_POST['funnel_id'] ) : 0;
		$filter = isset( $_POST['filter'] ) ? sanitize_text_field( wp_unslash( $_POST['filter'] ) ) : 'all';
		$country = isset( $_POST['country'] ) ? sanitize_text_field( wp_unslash( $_POST['country'] ) ) : 'all';

		// Calculate date range.
		$date_range = $this->calculate_date_range( $period, $start_date, $end_date );

		// Get funnel data based on user-defined funnel.
		if ( $funnel_id > 0 ) {
			$funnel_data = $this->get_user_funnel_analytics( $funnel_id, $date_range['start'], $date_range['end'], $filter, $country );
		} else {
			// Get first active funnel as default.
			global $wpdb;
			$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$default_funnel = $wpdb->get_row(
				"SELECT id FROM {$table_funnels} WHERE status = 'active' ORDER BY created_at ASC LIMIT 1"
			);

			if ( $default_funnel ) {
				$funnel_data = $this->get_user_funnel_analytics( $default_funnel->id, $date_range['start'], $date_range['end'], $filter, $country );
			} else {
				// No funnels defined, return empty data.
				$funnel_data = array(
					'total_entries'    => 0,
					'completed'        => 0,
					'conversion_rate'  => '0%',
					'dropoff_rate'     => '100%',
					'funnel_steps'     => array(),
					'entry_pages'      => array(),
					'exit_pages'       => array(),
				);
			}
		}

		wp_send_json_success( $funnel_data );
	}

	/**
	 * Calculate date range based on period
	 *
	 * @param string $period Period string.
	 * @param string $start_date Custom start date.
	 * @param string $end_date Custom end date.
	 * @return array Date range array.
	 */
	private function calculate_date_range( $period, $start_date = '', $end_date = '' ) {
		$end = gmdate( 'Y-m-d 23:59:59' );
		$start = gmdate( 'Y-m-d 00:00:00', strtotime( '-30 days' ) );

		switch ( $period ) {
			case '7days':
				$start = gmdate( 'Y-m-d 00:00:00', strtotime( '-7 days' ) );
				break;
			case '90days':
				$start = gmdate( 'Y-m-d 00:00:00', strtotime( '-90 days' ) );
				break;
			case 'custom':
				if ( $start_date && $end_date ) {
					$start = gmdate( 'Y-m-d 00:00:00', strtotime( $start_date ) );
					$end = gmdate( 'Y-m-d 23:59:59', strtotime( $end_date ) );
				}
				break;
		}

		return array(
			'start' => $start,
			'end'   => $end,
		);
	}

	/**
	 * Get user-defined funnel analytics data
	 *
	 * @param int    $funnel_id Funnel ID.
	 * @param string $start_date Start date.
	 * @param string $end_date End date.
	 * @param string $filter Device filter (all, desktop, mobile, tablet).
	 * @param string $country Country filter (all or country code).
	 * @return array Funnel data.
	 */
	private function get_user_funnel_analytics( $funnel_id, $start_date, $end_date, $filter = 'all', $country = 'all' ) {
		global $wpdb;
		$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';
		$table_tracking = $wpdb->prefix . 'opti_behavior_funnel_tracking';
		$table_sessions = $wpdb->prefix . 'optibehavior_sessions';
		$table_visitors = $wpdb->prefix . 'optibehavior_visitors';

		// Get funnel definition.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$funnel = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table_funnels} WHERE id = %d AND status = 'active'",
				$funnel_id
			)
		);

		if ( ! $funnel ) {
			return array(
				'total_entries'    => 0,
				'completed'        => 0,
				'conversion_rate'  => '0%',
				'dropoff_rate'     => '100%',
				'funnel_steps'     => array(),
				'entry_pages'      => array(),
				'exit_pages'       => array(),
			);
		}

		$steps_definitions = json_decode( $funnel->steps, true );

		// Build device and country filter clause.
		// Only JOIN to sessions/visitors tables when device or country filtering is actually needed.
		// An unconditional INNER JOIN would silently drop all funnel records whose session_id
		// has no matching row in optibehavior_sessions (e.g. anonymous/pre-consent sessions).
		$needs_join    = ( $filter !== 'all' || $country !== 'all' );
		$filter_join   = $needs_join
			? "INNER JOIN {$table_sessions} s ON t.session_id = s.id
			   INNER JOIN {$table_visitors} v ON s.visitor_id = v.id"
			: '';
		$filter_where  = '';
		$filters_applied = array();

		if ( $filter !== 'all' ) {
			$filters_applied[] = $wpdb->prepare( "v.device_type = %s", $filter );
		}

		if ( $country !== 'all' ) {
			// Support multiple countries (comma-separated)
			$country_codes = array_map( 'trim', explode( ',', $country ) );
			$country_codes = array_map( 'strtoupper', $country_codes );
			$country_codes = array_filter( $country_codes ); // Remove empty values

			if ( count( $country_codes ) === 1 ) {
				$filters_applied[] = $wpdb->prepare( "v.country = %s", $country_codes[0] );
			} elseif ( count( $country_codes ) > 1 ) {
				$placeholders = implode( ',', array_fill( 0, count( $country_codes ), '%s' ) );
				// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Prepared fragment is used in the final query below.
				$filters_applied[] = $wpdb->prepare( "v.country IN ($placeholders)", ...$country_codes );
			}
		}

		if ( ! empty( $filters_applied ) ) {
			$filter_where = 'AND ' . implode( ' AND ', $filters_applied );
		}

		// Get all tracking records in date range.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_entries = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT t.session_id)
				FROM {$table_tracking} t
				{$filter_join}
				WHERE t.funnel_id = %d
					AND t.entry_time >= %s
					AND t.entry_time <= %s
					{$filter_where}",
				$funnel_id,
				$start_date,
				$end_date
			)
		);

		if ( ! $total_entries ) {
			$total_entries = 0;
		}

		// Calculate step-by-step progression.
		$funnel_steps = array();
		$initial_entries = $total_entries; // Keep initial count for all comparisons

		foreach ( $steps_definitions as $index => $step_def ) {
			$step_number = $index + 1;

			// Count sessions that reached this step or beyond.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$completed = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(DISTINCT t.session_id)
					FROM {$table_tracking} t
					{$filter_join}
					WHERE t.funnel_id = %d
						AND t.entry_time >= %s
						AND t.entry_time <= %s
						AND t.max_step_reached >= %d
						{$filter_where}",
					$funnel_id,
					$start_date,
					$end_date,
					$step_number
				)
			);

			if ( ! $completed ) {
				$completed = 0;
			}

			// Always calculate against initial entries (Step 1), not previous step
			$abandoned = $initial_entries - $completed;
			$completion_rate = $initial_entries > 0 ? round( ( $completed / $initial_entries ) * 100, 1 ) : 0;
			$abandonment_rate = round( 100 - $completion_rate, 1 );

			$funnel_steps[] = array(
				'step_number'      => $step_number,
				'step_name'        => $step_def['name'],
				'completed'        => $completed,
				'abandoned'        => $abandoned,
				'completion_rate'  => $completion_rate,
				'abandonment_rate' => $abandonment_rate,
				'total_from_prev'  => $initial_entries, // Always show total from initial
			);
		}

		// Get completed count (sessions that completed all steps).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$completed = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT t.session_id)
				FROM {$table_tracking} t
				{$filter_join}
				WHERE t.funnel_id = %d
					AND t.entry_time >= %s
					AND t.entry_time <= %s
					AND t.completed = 1
					{$filter_where}",
				$funnel_id,
				$start_date,
				$end_date
			)
		);

		if ( ! $completed ) {
			$completed = 0;
		}

		$conversion_rate = $total_entries > 0 ? round( ( $completed / $total_entries ) * 100, 1 ) : 0;
		$dropoff_rate = 100 - $conversion_rate;

		// Get entry pages.
		$entry_pages = $this->get_top_entry_pages( $start_date, $end_date, 10 );

		// Get exit pages.
		$exit_pages = $this->get_top_exit_pages( $start_date, $end_date, 10 );

		return array(
			'funnel_id'        => $funnel_id,
			'funnel_name'      => $funnel->name,
			'total_entries'    => (int) $total_entries,
			'completed'        => (int) $completed,
			'conversion_rate'  => $conversion_rate . '%',
			'dropoff_rate'     => $dropoff_rate . '%',
			'funnel_steps'     => $funnel_steps,
			'entry_pages'      => $entry_pages,
			'exit_pages'       => $exit_pages,
		);
	}

	/**
	 * Get funnel analytics data (legacy - for page depth tracking)
	 *
	 * @param string $start_date Start date.
	 * @param string $end_date End date.
	 * @return array Funnel data.
	 */
	private function get_funnel_analytics( $start_date, $end_date ) {
		global $wpdb;
		$table_sessions = $wpdb->prefix . 'opti_behavior_sessions';
		$table_events = $wpdb->prefix . 'opti_behavior_events';

		// Get all sessions in date range.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_sessions = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT session_id)
				FROM {$table_sessions}
				WHERE created_at >= %s AND created_at <= %s",
				$start_date,
				$end_date
			)
		);

		if ( ! $total_sessions ) {
			$total_sessions = 0;
		}

		// Define funnel steps based on page depth.
		$funnel_steps = $this->calculate_funnel_steps( $start_date, $end_date );

		// Get entry pages.
		$entry_pages = $this->get_top_entry_pages( $start_date, $end_date, 10 );

		// Get exit pages.
		$exit_pages = $this->get_top_exit_pages( $start_date, $end_date, 10 );

		// Calculate overall metrics.
		$completed = isset( $funnel_steps[count( $funnel_steps ) - 1] ) ? $funnel_steps[count( $funnel_steps ) - 1]['completed'] : 0;
		$conversion_rate = $total_sessions > 0 ? round( ( $completed / $total_sessions ) * 100, 1 ) : 0;
		$dropoff_rate = 100 - $conversion_rate;

		return array(
			'total_entries'    => (int) $total_sessions,
			'completed'        => (int) $completed,
			'conversion_rate'  => $conversion_rate . '%',
			'dropoff_rate'     => $dropoff_rate . '%',
			'funnel_steps'     => $funnel_steps,
			'entry_pages'      => $entry_pages,
			'exit_pages'       => $exit_pages,
		);
	}

	/**
	 * Calculate funnel steps based on session progression
	 *
	 * @param string $start_date Start date.
	 * @param string $end_date End date.
	 * @return array Funnel steps data.
	 */
	private function calculate_funnel_steps( $start_date, $end_date ) {
		global $wpdb;
		$table_sessions = $wpdb->prefix . 'opti_behavior_sessions';

		// Get session page counts distribution.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$page_depth_data = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					page_count,
					COUNT(DISTINCT session_id) as sessions
				FROM {$table_sessions}
				WHERE created_at >= %s AND created_at <= %s
				GROUP BY page_count
				ORDER BY page_count ASC",
				$start_date,
				$end_date
			),
			ARRAY_A
		);

		// Define funnel steps: Entry, 2+ pages, 3+ pages, 4+ pages, 5+ pages, 6+ pages.
		$steps = array(
			array( 'name' => 'All Sessions', 'min_pages' => 1 ),
			array( 'name' => '2+ Page Views', 'min_pages' => 2 ),
			array( 'name' => '3+ Page Views', 'min_pages' => 3 ),
			array( 'name' => '4+ Page Views', 'min_pages' => 4 ),
			array( 'name' => '5+ Page Views', 'min_pages' => 5 ),
			array( 'name' => '6+ Page Views', 'min_pages' => 6 ),
		);

		$funnel_steps = array();
		$total_sessions = 0;

		// Calculate total sessions.
		foreach ( $page_depth_data as $row ) {
			$total_sessions += (int) $row['sessions'];
		}

		$previous_completed = $total_sessions;

		// Calculate each step.
		foreach ( $steps as $index => $step ) {
			$completed = 0;

			// Sum sessions that meet minimum page requirement.
			foreach ( $page_depth_data as $row ) {
				if ( (int) $row['page_count'] >= $step['min_pages'] ) {
					$completed += (int) $row['sessions'];
				}
			}

			$abandoned = $previous_completed - $completed;
			$completion_rate = $previous_completed > 0 ? round( ( $completed / $previous_completed ) * 100, 1 ) : 0;
			$abandonment_rate = round( 100 - $completion_rate, 1 );

			$funnel_steps[] = array(
				'step_number'      => $index + 1,
				'step_name'        => $step['name'],
				'completed'        => $completed,
				'abandoned'        => $abandoned,
				'completion_rate'  => $completion_rate,
				'abandonment_rate' => $abandonment_rate,
				'total_from_prev'  => $previous_completed,
			);

			$previous_completed = $completed;
		}

		return $funnel_steps;
	}

	/**
	 * Get top entry pages
	 *
	 * @param string $start_date Start date.
	 * @param string $end_date End date.
	 * @param int    $limit Result limit.
	 * @return array Entry pages data.
	 */
	private function get_top_entry_pages( $start_date, $end_date, $limit = 10 ) {
		global $wpdb;
		$table_sessions = $wpdb->prefix . 'opti_behavior_sessions';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					entry_page,
					COUNT(*) as entries,
					AVG(page_count) as avg_depth
				FROM {$table_sessions}
				WHERE created_at >= %s AND created_at <= %s
					AND entry_page IS NOT NULL
					AND entry_page != ''
				GROUP BY entry_page
				ORDER BY entries DESC
				LIMIT %d",
				$start_date,
				$end_date,
				$limit
			),
			ARRAY_A
		);

		return array_map(
			function( $row ) {
				return array(
					'page'       => $row['entry_page'],
					'entries'    => (int) $row['entries'],
					'avg_depth'  => round( (float) $row['avg_depth'], 1 ),
				);
			},
			$results
		);
	}

	/**
	 * Get top exit pages
	 *
	 * @param string $start_date Start date.
	 * @param string $end_date End date.
	 * @param int    $limit Result limit.
	 * @return array Exit pages data.
	 */
	private function get_top_exit_pages( $start_date, $end_date, $limit = 10 ) {
		global $wpdb;
		$table_sessions = $wpdb->prefix . 'opti_behavior_sessions';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					exit_page,
					COUNT(*) as exits,
					AVG(page_count) as avg_depth
				FROM {$table_sessions}
				WHERE created_at >= %s AND created_at <= %s
					AND exit_page IS NOT NULL
					AND exit_page != ''
				GROUP BY exit_page
				ORDER BY exits DESC
				LIMIT %d",
				$start_date,
				$end_date,
				$limit
			),
			ARRAY_A
		);

		return array_map(
			function( $row ) {
				return array(
					'page'      => $row['exit_page'],
					'exits'     => (int) $row['exits'],
					'avg_depth' => round( (float) $row['avg_depth'], 1 ),
				);
			},
			$results
		);
	}

	/**
	 * AJAX handler to get all funnels
	 */
	public function ajax_get_funnels() {
		// Verify nonce.
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		// Check permissions.
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Permission denied' ) );
		}

		global $wpdb;
		$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$funnels = $wpdb->get_results(
			"SELECT id, name, description, steps, status, created_at
			FROM {$table_funnels}
			WHERE status = 'active'
			ORDER BY created_at DESC",
			ARRAY_A
		);

		// Decode steps JSON for each funnel.
		foreach ( $funnels as &$funnel ) {
			$funnel['steps'] = json_decode( $funnel['steps'], true );
		}

		wp_send_json_success( $funnels );
	}

	/**
	 * AJAX handler to get a single funnel by ID
	 */
	public function ajax_get_funnel() {
		// Verify nonce.
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		// Check permissions.
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Permission denied' ) );
		}

		$funnel_id = isset( $_POST['funnel_id'] ) ? intval( $_POST['funnel_id'] ) : 0;

		if ( $funnel_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid funnel ID' ) );
		}

		global $wpdb;
		$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$funnel = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id, name, description, steps, status, created_at
				FROM {$table_funnels}
				WHERE id = %d",
				$funnel_id
			),
			ARRAY_A
		);

		if ( ! $funnel ) {
			wp_send_json_error( array( 'message' => 'Funnel not found' ) );
		}

		// Decode steps JSON.
		$funnel['steps'] = json_decode( $funnel['steps'], true );

		wp_send_json_success( $funnel );
	}

	/**
	 * AJAX handler to save funnel
	 */
	public function ajax_save_funnel() {
		// Verify nonce.
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		// Check permissions.
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Permission denied' ) );
		}

		// Get funnel data.
		$funnel_id = isset( $_POST['funnel_id'] ) ? intval( $_POST['funnel_id'] ) : 0;
		$name = isset( $_POST['name'] ) ? sanitize_text_field( wp_unslash( $_POST['name'] ) ) : '';
		$description = isset( $_POST['description'] ) ? sanitize_textarea_field( wp_unslash( $_POST['description'] ) ) : '';
		$steps = isset( $_POST['steps'] ) ? sanitize_text_field( wp_unslash( $_POST['steps'] ) ) : '';

		// Validate.
		if ( empty( $name ) || empty( $steps ) ) {
			wp_send_json_error( array( 'message' => 'Name and steps are required' ) );
		}

		// Decode and validate steps.
		$steps_array = json_decode( $steps, true );
		if ( ! is_array( $steps_array ) || empty( $steps_array ) ) {
			wp_send_json_error( array( 'message' => 'Invalid steps format' ) );
		}

		global $wpdb;
		$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';

		$data = array(
			'name'        => $name,
			'description' => $description,
			'steps'       => wp_json_encode( $steps_array ),
			'status'      => 'active',
		);

		if ( $funnel_id > 0 ) {
			// Update existing funnel.
			$wpdb->update(
				$table_funnels,
				$data,
				array( 'id' => $funnel_id ),
				array( '%s', '%s', '%s', '%s' ),
				array( '%d' )
			);
		} else {
			// Insert new funnel.
			$wpdb->insert(
				$table_funnels,
				$data,
				array( '%s', '%s', '%s', '%s' )
			);
			$funnel_id = $wpdb->insert_id;
		}

		wp_send_json_success( array( 'funnel_id' => $funnel_id, 'message' => 'Funnel saved successfully' ) );
	}

	/**
	 * AJAX handler to delete funnel
	 */
	public function ajax_delete_funnel() {
		// Verify nonce.
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		// Check permissions.
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Permission denied' ) );
		}

		$funnel_id = isset( $_POST['funnel_id'] ) ? intval( $_POST['funnel_id'] ) : 0;

		if ( $funnel_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid funnel ID' ) );
		}

		global $wpdb;
		$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';

		// Soft delete by setting status to 'deleted'.
		$wpdb->update(
			$table_funnels,
			array( 'status' => 'deleted' ),
			array( 'id' => $funnel_id ),
			array( '%s' ),
			array( '%d' )
		);

		wp_send_json_success( array( 'message' => 'Funnel deleted successfully' ) );
	}

	/**
	 * AJAX handler to reset funnel data (keeps configuration, deletes tracking data)
	 */
	public function ajax_reset_funnel_data() {
		// Verify nonce.
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		// Check permissions.
		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Permission denied' ) );
		}

		$funnel_id = isset( $_POST['funnel_id'] ) ? intval( $_POST['funnel_id'] ) : 0;

		if ( $funnel_id <= 0 ) {
			wp_send_json_error( array( 'message' => 'Invalid funnel ID' ) );
		}

		global $wpdb;
		$table_tracking = $wpdb->prefix . 'opti_behavior_funnel_tracking';

		// Delete all tracking data for this funnel (but keep the funnel configuration)
		$deleted = $wpdb->delete(
			$table_tracking,
			array( 'funnel_id' => $funnel_id ),
			array( '%d' )
		);

		if ( $deleted === false ) {
			wp_send_json_error( array( 'message' => 'Error resetting funnel data' ) );
		}

		wp_send_json_success( array(
			'message' => 'Funnel data reset successfully',
			'deleted_count' => $deleted
		) );
	}

	/**
	 * AJAX handler to track funnel progression
	 */
	public function ajax_track_funnel() {
		// Verify nonce.
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		// Get tracking data.
		$session_id = isset( $_POST['session_id'] ) ? sanitize_text_field( wp_unslash( $_POST['session_id'] ) ) : '';
		$funnel_id = isset( $_POST['funnel_id'] ) ? intval( $_POST['funnel_id'] ) : 0;
		$current_url = isset( $_POST['current_url'] ) ? esc_url_raw( wp_unslash( $_POST['current_url'] ) ) : '';

		if ( empty( $session_id ) || $funnel_id <= 0 || empty( $current_url ) ) {
			wp_send_json_error( array( 'message' => 'Invalid tracking data' ) );
		}

		global $wpdb;
		$table_funnels = $wpdb->prefix . 'opti_behavior_funnels';
		$table_tracking = $wpdb->prefix . 'opti_behavior_funnel_tracking';

		// Get funnel definition.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$funnel = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT steps FROM {$table_funnels} WHERE id = %d AND status = 'active'",
				$funnel_id
			)
		);

		if ( ! $funnel ) {
			wp_send_json_error( array( 'message' => 'Funnel not found' ) );
		}

		$steps = json_decode( $funnel->steps, true );
		if ( empty( $steps ) || ! is_array( $steps ) ) {
			wp_send_json_error( array( 'message' => 'Funnel has no steps' ) );
		}

		// Deduplication guard: if the server-side PHP tracker (track_all_requests)
		// already claimed this session+funnel+url in the same request cycle, skip
		// the AJAX advancement to prevent double-counting. Without this guard, a
		// single page visit advances two steps when consecutive steps both match
		// the same URL (e.g. back-to-back 'any'/'pageview' steps).
		$php_tracker_url = get_transient( 'ob_srv_' . md5( $session_id . '_' . $funnel_id ) );
		if ( false !== $php_tracker_url && $php_tracker_url === $current_url ) {
			wp_send_json_success( array( 'message' => 'Server-side tracker handled this request' ) );
			return;
		}

		// Fast-exit: if no step pattern matches this URL at all, skip the DB work.
		$any_match = false;
		foreach ( $steps as $step ) {
			if ( $this->url_matches_pattern( $current_url, $step['url_pattern'], $step['match_type'] ) ) {
				$any_match = true;
				break;
			}
		}

		if ( ! $any_match ) {
			wp_send_json_success( array( 'message' => 'URL does not match any funnel step' ) );
			return;
		}

		// Delegate to save_funnel_tracking — it is context-aware and will decide
		// whether to enter the funnel at step 1 or advance the session by +1.
		// We never pre-commit to a "matched step" here, because overlapping
		// patterns (e.g. a step 1 with match_type='any') would hijack every
		// advancement by matching the URL first.
		$outcome = $this->save_funnel_tracking( $session_id, $funnel_id, 0, $current_url, $steps );

		if ( is_array( $outcome ) && ! empty( $outcome['advanced'] ) ) {
			wp_send_json_success( array(
				'step'      => (int) $outcome['step'],
				'completed' => (int) $outcome['completed'],
			) );
		}

		wp_send_json_success( array( 'message' => 'URL is not the next sequential step for this session' ) );
	}

	/**
	 * Universal request tracker - OPTIMIZED for millions of users
	 * Uses caching and deferred writes for maximum performance
	 */
	public function track_all_requests() {
		// FAST EXIT 1: Don't track admin pages (no DB query)
		if ( is_admin() ) {
			return;
		}

		// FAST EXIT 2: Don't track bots (string check only)
		if ( $this->is_bot_request() ) {
			return;
		}

		// FAST EXIT 3: No session = no tracking
		$session_id = isset( $_COOKIE['optibehavior_sid'] ) ? sanitize_text_field( wp_unslash( $_COOKIE['optibehavior_sid'] ) ) : '';
		if ( empty( $session_id ) ) {
			$session_id = isset( $_COOKIE['opti_behavior_session_id'] ) ? sanitize_text_field( wp_unslash( $_COOKIE['opti_behavior_session_id'] ) ) : '';
		}

		if ( empty( $session_id ) ) {
			return;
		}

		// PERFORMANCE: Cache active funnels (5 min cache = only 1 DB query per 300 requests!)
		static $active_funnels = null;
		static $cache_time = 0;

		if ( $active_funnels === null || ( time() - $cache_time ) > 300 ) {
			global $wpdb;
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			$active_funnels = $wpdb->get_results(
				"SELECT id, steps FROM {$wpdb->prefix}opti_behavior_funnels WHERE status = 'active' LIMIT 50",
				ARRAY_A
			);
			$cache_time = time();
		}

		// FAST EXIT 4: No funnels
		if ( empty( $active_funnels ) ) {
			return;
		}

		// Build URL once
		$http_host   = isset( $_SERVER['HTTP_HOST'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_HOST'] ) ) : '';
		$request_uri = isset( $_SERVER['REQUEST_URI'] ) ? sanitize_text_field( wp_unslash( $_SERVER['REQUEST_URI'] ) ) : '';
		$current_url = ( is_ssl() ? 'https://' : 'http://' ) . $http_host . $request_uri;

		// Check funnels (in-memory only, very fast)
		foreach ( $active_funnels as $funnel ) {
			$steps = json_decode( $funnel['steps'], true );
			if ( empty( $steps ) ) {
				continue;
			}

			// Fast-exit pre-filter: queue a DB write only if AT LEAST ONE step
			// pattern matches this URL. The real step decision (which step to
			// advance to, if any) is made in save_funnel_tracking() based on the
			// session's current progress — we must not commit to a "matched step"
			// here, because overlapping patterns (e.g. step 1 = 'any') would
			// otherwise hijack every advancement.
			$any_match = false;
			foreach ( $steps as $step ) {
				if ( $this->url_matches_pattern( $current_url, $step['url_pattern'], $step['match_type'] ) ) {
					$any_match = true;
					break;
				}
			}

			if ( $any_match ) {
				// Mark this session+funnel+url as being handled by the server-side
				// tracker. The JS AJAX tracker checks this flag and skips its AJAX
				// call so that a single page visit never advances more than one step
				// (double-advancement can happen when consecutive steps both match
				// the same URL, e.g. two 'any'/'pageview' steps back-to-back).
				// 10-second TTL — long enough for the JS AJAX call to arrive.
				set_transient( 'ob_srv_' . md5( $session_id . '_' . $funnel['id'] ), $current_url, 10 );

				// PERFORMANCE: Defer DB write to shutdown (non-blocking).
				$this->queue_tracking( $session_id, (int) $funnel['id'], 0, $current_url, $steps );
			}
		}
	}

	/**
	 * Queue tracking for deferred write (non-blocking performance optimization)
	 */
	private function queue_tracking( $session_id, $funnel_id, $matched_step, $current_url, $steps ) {
		static $queue = array();
		static $registered = false;

		$queue[] = compact( 'session_id', 'funnel_id', 'matched_step', 'current_url', 'steps' );

		if ( ! $registered ) {
			$self = $this;
			add_action( 'shutdown', function() use ( &$queue, $self ) {
				foreach ( $queue as $item ) {
					$self->save_funnel_tracking(
						$item['session_id'],
						$item['funnel_id'],
						$item['matched_step'],
						$item['current_url'],
						$item['steps']
					);
				}
			}, 999 );
			$registered = true;
		}
	}

	/**
	 * Save funnel tracking data.
	 *
	 * Context-aware: the real step decision is made here based on the session's
	 * current progress, NOT on the caller's `$matched_step`. That parameter is
	 * retained only for backward compatibility with `queue_tracking()` and is
	 * ignored — using it led to the "any-match hijack" bug where a step 1 with
	 * `match_type = 'any'` matched every URL, causing callers to pass
	 * `$matched_step = 1` for a visit to step 2's URL and the method to reject
	 * the advancement as "not the next sequential step".
	 *
	 * The authoritative rule applied here:
	 *   - New session:  URL must match step 1's pattern → insert with step=1.
	 *   - Existing session: URL must match the next-expected step's pattern
	 *     (steps[max_step_reached]) → advance by +1. Otherwise do nothing.
	 *
	 * @param string $session_id   Session identifier.
	 * @param int    $funnel_id    Funnel row ID.
	 * @param int    $matched_step IGNORED — kept for backward compatibility.
	 * @param string $current_url  URL the visitor is on right now.
	 * @param array  $steps        Decoded funnel step definitions.
	 * @return array|null Outcome: ['advanced' => bool, 'step' => int, 'completed' => int]
	 *                   when a decision was made, or null on invalid input.
	 */
	private function save_funnel_tracking( $session_id, $funnel_id, $matched_step, $current_url, $steps ) {
		unset( $matched_step ); // Intentionally ignored — see docblock.

		global $wpdb;
		$table_tracking = $wpdb->prefix . 'opti_behavior_funnel_tracking';

		if ( empty( $steps ) || ! is_array( $steps ) ) {
			return null;
		}

		// Check if tracking already exists for this session and funnel.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$tracking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$table_tracking} WHERE session_id = %s AND funnel_id = %d",
				$session_id,
				$funnel_id
			)
		);

		if ( $tracking ) {
			// The session is already in the funnel. Advance only if the CURRENT
			// URL matches the NEXT expected step's pattern explicitly. Overlapping
			// patterns (e.g. step 1 = 'any') would otherwise falsely block every
			// advancement, because the caller's "first-match" hint points at
			// step 1 even on a step-2 URL.
			$next_index = (int) $tracking->max_step_reached; // 0-based index of the next step.

			if ( $next_index >= count( $steps ) ) {
				return array( 'advanced' => false, 'step' => (int) $tracking->max_step_reached, 'completed' => (int) $tracking->completed );
			}

			$next_step = $steps[ $next_index ];
			if ( ! $this->url_matches_pattern( $current_url, $next_step['url_pattern'], $next_step['match_type'] ) ) {
				return array( 'advanced' => false, 'step' => (int) $tracking->max_step_reached, 'completed' => (int) $tracking->completed );
			}

			$advanced_step = $next_index + 1;
			$is_completed  = ( $advanced_step === count( $steps ) ) ? 1 : 0;

			$wpdb->update(
				$table_tracking,
				array(
					'current_step'      => $advanced_step,
					'max_step_reached'  => $advanced_step,
					'completed'         => $is_completed,
					'completion_time'   => $is_completed ? current_time( 'mysql' ) : null,
				),
				array( 'id' => $tracking->id ),
				array( '%d', '%d', '%d', '%s' ),
				array( '%d' )
			);

			return array( 'advanced' => true, 'step' => $advanced_step, 'completed' => $is_completed );
		}

		// New session: must explicitly match step 1 to enter the funnel. No
		// mid-funnel entries allowed, so someone landing directly on the
		// checkout page is never counted as having completed the funnel.
		$first_step = $steps[0];
		if ( ! $this->url_matches_pattern( $current_url, $first_step['url_pattern'], $first_step['match_type'] ) ) {
			return array( 'advanced' => false, 'step' => 0, 'completed' => 0 );
		}

		$is_completed = ( 1 === count( $steps ) ) ? 1 : 0;

		$wpdb->insert(
			$table_tracking,
			array(
				'session_id'        => $session_id,
				'funnel_id'         => $funnel_id,
				'current_step'      => 1,
				'max_step_reached'  => 1,
				'completed'         => $is_completed,
				'entry_time'        => current_time( 'mysql' ),
				'completion_time'   => $is_completed ? current_time( 'mysql' ) : null,
			),
			array( '%s', '%d', '%d', '%d', '%d', '%s', '%s' )
		);

		return array( 'advanced' => true, 'step' => 1, 'completed' => $is_completed );
	}

	/**
	 * Check if current request is from a bot
	 */
	private function is_bot_request() {
		$user_agent = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';
		$bot_patterns = array(
			'bot', 'crawl', 'spider', 'slurp', 'search', 'index',
			'facebook', 'twitter', 'linkedin', 'pinterest',
		);

		foreach ( $bot_patterns as $pattern ) {
			if ( stripos( $user_agent, $pattern ) !== false ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Check if URL matches pattern.
	 *
	 * Defensive normalization: if `match_type` is `any` or `pageview` BUT a
	 * specific `url_pattern` is provided (non-empty and not the `*` wildcard),
	 * interpret it as a `contains` match. Rationale: `any` / `pageview` return
	 * true unconditionally, so any `url_pattern` set by the user is silently
	 * ignored. In practice users configure a real URL pattern (e.g. for a
	 * /checkout step) and forget to change the default match_type — this used
	 * to cause every page visit to falsely "advance" past those steps because
	 * the URL pattern was ignored. Respecting the pattern matches user intent
	 * while remaining backwards-compatible: true entry triggers with an empty
	 * pattern or `*` still behave as "match any URL".
	 *
	 * @param string $url        URL to check.
	 * @param string $pattern    Pattern to match.
	 * @param string $match_type Match type (any, exact, contains, starts_with, ends_with, regex, pageview).
	 * @return bool True if matches.
	 */
	private function url_matches_pattern( $url, $pattern, $match_type ) {
		// Defensive normalization — see docblock.
		if ( ( 'any' === $match_type || 'pageview' === $match_type )
			&& is_string( $pattern )
			&& '' !== $pattern
			&& '*' !== $pattern
		) {
			$match_type = 'contains';
		}

		switch ( $match_type ) {
			case 'any':
				return true;
			case 'exact':
				return $url === $pattern;
			case 'contains':
				// Guard: empty pattern matches everything (avoids ValueError on PHP 8.0+ where
				// stripos() rejects an empty needle). Keeps parity with the JS implementation.
				if ( ! is_string( $pattern ) || '' === $pattern ) {
					return true;
				}
				return stripos( $url, $pattern ) !== false;
			case 'starts_with':
				// Guard: same PHP 8.0 empty-needle protection as 'contains'.
				if ( ! is_string( $pattern ) || '' === $pattern ) {
					return true;
				}
				return stripos( $url, $pattern ) === 0;
			case 'ends_with':
				// Guard: empty pattern matches everything. PHP's substr($url, -0) returns the
				// full string and strcasecmp(full_url,'') is non-zero (returns false), while JS
				// url.substring(len-0)='' and ''==='' is true. The guard aligns both sides.
				if ( ! is_string( $pattern ) || '' === $pattern ) {
					return true;
				}
				$pattern_len = strlen( $pattern );
				return strcasecmp( substr( $url, -$pattern_len ), $pattern ) === 0;
			case 'regex':
				// Use a different delimiter to avoid conflicts with / in URLs
				$safe_pattern = str_replace( '#', '\#', $pattern );
				return @preg_match( '#' . $safe_pattern . '#', $url ) === 1;
			case 'pageview':
				return true; // Any page view counts.
			default:
				return false;
		}
	}

	/**
	 * AJAX handler to get available countries from funnel tracking data
	 * Only returns countries that have data for the specified funnel
	 */
	public function ajax_get_funnel_countries() {
		check_ajax_referer( 'opti_behavior_funnels', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Unauthorized' ) );
		}

		// Get funnel ID, period, and date range from request
		$funnel_id = isset( $_POST['funnel_id'] ) ? intval( $_POST['funnel_id'] ) : 0;
		$period = isset( $_POST['period'] ) ? sanitize_text_field( wp_unslash( $_POST['period'] ) ) : '7days';
		$start_date = isset( $_POST['start_date'] ) ? sanitize_text_field( wp_unslash( $_POST['start_date'] ) ) : '';
		$end_date = isset( $_POST['end_date'] ) ? sanitize_text_field( wp_unslash( $_POST['end_date'] ) ) : '';

		// Calculate date range
		$date_range = $this->calculate_date_range( $period, $start_date, $end_date );

		global $wpdb;
		$table_sessions = $wpdb->prefix . 'optibehavior_sessions';
		$table_visitors = $wpdb->prefix . 'optibehavior_visitors';
		$table_tracking = $wpdb->prefix . 'opti_behavior_funnel_tracking';

		// Get unique countries from visitors who have data for the specific funnel
		if ( $funnel_id > 0 ) {
			// Only get countries that have tracking data for this specific funnel in the date range
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$countries = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT DISTINCT v.country, v.country_name
					FROM {$table_visitors} v
					INNER JOIN {$table_sessions} s ON v.id = s.visitor_id
					INNER JOIN {$table_tracking} t ON s.id = t.session_id
					WHERE t.funnel_id = %d
					AND t.entry_time >= %s
					AND t.entry_time <= %s
					AND v.country IS NOT NULL
					AND v.country != ''
					AND v.country != 'UN'
					ORDER BY v.country_name ASC",
					$funnel_id,
					$date_range['start'],
					$date_range['end']
				),
				ARRAY_A
			);
		} else {
			// Fallback: get all countries with any session data
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name safely derived from $wpdb->prefix.
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$countries = $wpdb->get_results(
				"SELECT DISTINCT v.country, v.country_name
				FROM {$table_visitors} v
				INNER JOIN {$table_sessions} s ON v.id = s.visitor_id
				WHERE v.country IS NOT NULL
				AND v.country != ''
				AND v.country != 'UN'
				ORDER BY v.country_name ASC",
				ARRAY_A
			);
		}

		$country_list = array();
		foreach ( $countries as $country ) {
			$country_code = strtoupper( $country['country'] );
			$country_name = ! empty( $country['country_name'] ) ? $country['country_name'] : $country_code;

			$country_list[] = array(
				'code' => $country_code,
				'name' => $country_name,
			);
		}

		wp_send_json_success( array( 'countries' => $country_list ) );
	}
}
