<?php
/**
 * Conversion Tracking Class
 * Tracks when AI-recommended products are purchased
 *
 * @package AI_Commerce_Assistant
 */

if ( !defined( 'ABSPATH' ) ) {
	exit;
}

class AICA_Conversion_Tracking {

	private static $instance = null;

	public static function get_instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	private function __construct() {
		// Track cart additions via chat
		add_action( 'woocommerce_add_to_cart', array( $this, 'track_cart_addition' ), 10, 6 );

		// Track completed orders
		add_action( 'woocommerce_thankyou', array( $this, 'track_conversion' ), 10, 1 );

		// Add meta to cart items to track source
		add_filter( 'woocommerce_add_cart_item_data', array( $this, 'add_cart_item_meta' ), 10, 3 );
	}

	/**
	 * Add meta to cart items to track AI recommendations
	 *
	 * @param array $cart_item_data Cart item data
	 * @param int   $product_id Product ID
	 * @param int   $variation_id Variation ID
	 * @return array Modified cart item data
	 */
	public function add_cart_item_meta( $cart_item_data, $product_id, $variation_id ) {
		// Check if this was added via AI assistant
		if ( isset( $_SERVER['HTTP_X_AICA_SESSION'] ) ) {
			$cart_item_data['aica_session_id'] = sanitize_text_field( $_SERVER['HTTP_X_AICA_SESSION'] );
			$cart_item_data['aica_added_at']   = current_time( 'timestamp' );
		}

		return $cart_item_data;
	}

	/**
	 * Track cart addition
	 *
	 * @param string $cart_item_key Cart item key
	 * @param int    $product_id Product ID
	 * @param int    $quantity Quantity
	 * @param int    $variation_id Variation ID
	 * @param array  $variation Variation data
	 * @param array  $cart_item_data Cart item data
	 * @return void
	 */
	public function track_cart_addition( $cart_item_key, $product_id, $quantity, $variation_id, $variation, $cart_item_data ) {
		// Only track if added via AI assistant
		if ( !isset( $cart_item_data['aica_session_id'] ) ) {
			return;
		}

		global $wpdb;

		// Log event
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert(
			$wpdb->prefix . 'aica_analytics',
			array(
				'session_id' => $cart_item_data['aica_session_id'],
				'event_type' => 'product_added_to_cart',
				'event_data' => wp_json_encode(
					array(
						'product_id'    => $product_id,
						'variation_id'  => $variation_id,
						'quantity'      => $quantity,
						'cart_item_key' => $cart_item_key,
					)
				),
				'created_at' => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%s' )
		);
	}

	/**
	 * Track conversion when order is completed
	 *
	 * @param int $order_id Order ID
	 * @return void
	 */
	public function track_conversion( $order_id ) {
		if ( !$order_id ) {
			return;
		}

		$order = wc_get_order( $order_id );
		if ( !$order ) {
			return;
		}

		global $wpdb;
		$conversions = array();

		// Check each order item
		foreach ( $order->get_items() as $item_id => $item ) {
			$product_id   = $item->get_product_id();
			$variation_id = $item->get_variation_id();

			// Check if this product was recommended by AI
			$session_id = $this->get_recommendation_session( $product_id, $order->get_date_created() );

			if ( $session_id ) {
				$conversions[] = array(
					'session_id'   => $session_id,
					'product_id'   => $product_id,
					'variation_id' => $variation_id,
					'quantity'     => $item->get_quantity(),
					'total'        => $item->get_total(),
				);

				// Log conversion event
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				$wpdb->insert(
					$wpdb->prefix . 'aica_analytics',
					array(
						'session_id' => $session_id,
						'event_type' => 'conversion',
						'event_data' => wp_json_encode(
							array(
								'order_id'     => $order_id,
								'product_id'   => $product_id,
								'variation_id' => $variation_id,
								'quantity'     => $item->get_quantity(),
								'revenue'      => $item->get_total(),
							)
						),
						'created_at' => current_time( 'mysql' ),
					),
					array( '%s', '%s', '%s', '%s' )
				);
			}
		}

		// Store conversion data in order meta
		if ( !empty( $conversions ) ) {
			update_post_meta( $order_id, '_aica_conversions', $conversions );
			update_post_meta( $order_id, '_aica_conversion_value', $order->get_total() );
		}
	}

	/**
	 * Get recommendation session for a product
	 *
	 * @param int         $product_id Product ID
	 * @param WC_DateTime $order_date Order date
	 * @return string|null Session ID or null
	 */
	private function get_recommendation_session( $product_id, $order_date ) {
		global $wpdb;

		// Look for recommendations in the last 7 days
		$date_limit = $order_date->modify( '-7 days' )->format( 'Y-m-d H:i:s' );

		// Check cache
		$cache_key  = 'aica_rec_session_' . $product_id . '_' . strtotime( $date_limit );
		$session_id = wp_cache_get( $cache_key, 'aica_conversion_tracking' );

		if ( false === $session_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$session_id = $wpdb->get_var(
				$wpdb->prepare(
					"
				SELECT session_id
				FROM {$wpdb->prefix}aica_analytics
				WHERE event_type = 'products_recommended'
				AND JSON_CONTAINS(event_data, %s, '$.product_ids')
				AND created_at >= %s
				ORDER BY created_at DESC
				LIMIT 1
			",
					wp_json_encode( $product_id ),
					$date_limit
				)
			);
			wp_cache_set( $cache_key, $session_id, 'aica_conversion_tracking', HOUR_IN_SECONDS );
		}

		return $session_id;
	}

	/**
	 * Get conversion stats
	 *
	 * @param string $period Period (today, week, month, year, all)
	 * @return array Stats
	 */
	public function get_conversion_stats( $period = 'month' ) {
		global $wpdb;

		$cache_key = 'aica_conversion_stats_' . $period;
		$cached    = wp_cache_get( $cache_key, 'aica_conversion_tracking' );
		if ( false !== $cached ) {
			return $cached;
		}

		switch ( $period ) {
			case 'today':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_revenue = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND DATE(created_at) = CURDATE()" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND DATE(created_at) = CURDATE()" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_recommendations = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.count') AS UNSIGNED)) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND DATE(created_at) = CURDATE()" );
				break;
			case 'week':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_revenue = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_recommendations = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.count') AS UNSIGNED)) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				break;
			case 'month':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_revenue = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_recommendations = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.count') AS UNSIGNED)) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				break;
			case 'year':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_revenue = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_recommendations = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.count') AS UNSIGNED)) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				break;
			case 'all':
			default:
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_revenue = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion'" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion'" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_recommendations = $wpdb->get_var( "SELECT SUM(CAST(JSON_EXTRACT(event_data, '$.count') AS UNSIGNED)) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended'" );
				break;
		}

		// Conversion rate
		$conversion_rate = $total_recommendations > 0
			? round( ( $total_conversions / $total_recommendations ) * 100, 2 )
			: 0;

		// Average order value
		$avg_order_value = $total_conversions > 0
			? round( $total_revenue / $total_conversions, 2 )
			: 0;

		$stats = array(
			'total_revenue'         => floatval( $total_revenue ),
			'total_conversions'     => intval( $total_conversions ),
			'total_recommendations' => intval( $total_recommendations ),
			'conversion_rate'       => $conversion_rate,
			'avg_order_value'       => $avg_order_value,
		);

		wp_cache_set( $cache_key, $stats, 'aica_conversion_tracking', HOUR_IN_SECONDS );

		return $stats;
	}

	/**
	 * Get top converting products
	 *
	 * @param int    $limit Limit
	 * @param string $period Period
	 * @return array Products
	 */
	public function get_top_converting_products( $limit = 10, $period = 'month' ) {
		global $wpdb;

		$analytics  = AICA_Analytics::get_instance();

		// Validate period
		$valid_periods = array( 'today', 'week', 'month', 'year', 'all' );
		if ( ! in_array( $period, $valid_periods, true ) ) {
			$period = 'month';
		}

		$cache_key = 'aica_top_products_' . $period . '_' . $limit;
		$cached    = wp_cache_get( $cache_key, 'aica_conversion_tracking' );
		if ( false !== $cached ) {
			return $cached;
		}

		switch ( $period ) {
			case 'today':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( $wpdb->prepare( "SELECT CAST(JSON_EXTRACT(event_data, '$.product_id') AS UNSIGNED) as product_id, COUNT(*) as conversion_count, SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) as total_revenue FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND DATE(created_at) = CURDATE() GROUP BY product_id ORDER BY conversion_count DESC LIMIT %d", $limit ) );
				break;
			case 'week':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( $wpdb->prepare( "SELECT CAST(JSON_EXTRACT(event_data, '$.product_id') AS UNSIGNED) as product_id, COUNT(*) as conversion_count, SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) as total_revenue FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY product_id ORDER BY conversion_count DESC LIMIT %d", $limit ) );
				break;
			case 'month':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( $wpdb->prepare( "SELECT CAST(JSON_EXTRACT(event_data, '$.product_id') AS UNSIGNED) as product_id, COUNT(*) as conversion_count, SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) as total_revenue FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY product_id ORDER BY conversion_count DESC LIMIT %d", $limit ) );
				break;
			case 'year':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( $wpdb->prepare( "SELECT CAST(JSON_EXTRACT(event_data, '$.product_id') AS UNSIGNED) as product_id, COUNT(*) as conversion_count, SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) as total_revenue FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY product_id ORDER BY conversion_count DESC LIMIT %d", $limit ) );
				break;
			case 'all':
			default:
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( $wpdb->prepare( "SELECT CAST(JSON_EXTRACT(event_data, '$.product_id') AS UNSIGNED) as product_id, COUNT(*) as conversion_count, SUM(CAST(JSON_EXTRACT(event_data, '$.revenue') AS DECIMAL(10,2))) as total_revenue FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' GROUP BY product_id ORDER BY conversion_count DESC LIMIT %d", $limit ) );
				break;
		}

		$products = array();

		foreach ( $results as $row ) {
			$product = wc_get_product( $row->product_id );

			if ( $product ) {
				$products[] = array(
					'id'          => $row->product_id,
					'name'        => $product->get_name(),
					'image'       => wp_get_attachment_url( $product->get_image_id() ),
					'price'       => $product->get_price(),
					'conversions' => intval( $row->conversion_count ),
					'revenue'     => floatval( $row->total_revenue ),
				);
			}
		}

		wp_cache_set( $cache_key, $products, 'aica_conversion_tracking', HOUR_IN_SECONDS );

		return $products;
	}

	/**
	 * Get conversion funnel data
	 *
	 * @param string $period Period
	 * @return array Funnel data
	 */
	public function get_conversion_funnel( $period = 'month' ) {
		global $wpdb;

		$cache_key = 'aica_conversion_funnel_' . $period;
		$cached    = wp_cache_get( $cache_key, 'aica_conversion_tracking' );
		if ( false !== $cached ) {
			return $cached;
		}

		switch ( $period ) {
			case 'today':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_conversations WHERE DATE(created_at) = CURDATE()" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$with_recommendations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND DATE(created_at) = CURDATE()" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$added_to_cart = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' AND DATE(created_at) = CURDATE()" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$conversions = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND DATE(created_at) = CURDATE()" );
				break;
			case 'week':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$with_recommendations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$added_to_cart = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$conversions = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				break;
			case 'month':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$with_recommendations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$added_to_cart = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$conversions = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				break;
			case 'year':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$with_recommendations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$added_to_cart = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$conversions = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				break;
			case 'all':
			default:
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_conversations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_conversations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$with_recommendations = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended'" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$added_to_cart = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart'" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$conversions = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'conversion'" );
				break;
		}

		$funnel = array(
			array(
				'step'       => 'Conversations',
				'count'      => intval( $total_conversations ),
				'percentage' => 100,
			),
			array(
				'step'       => 'Saw Recommendations',
				'count'      => intval( $with_recommendations ),
				'percentage' => $total_conversations > 0 ? round( ( $with_recommendations / $total_conversations ) * 100, 1 ) : 0,
			),
			array(
				'step'       => 'Added to Cart',
				'count'      => intval( $added_to_cart ),
				'percentage' => $total_conversations > 0 ? round( ( $added_to_cart / $total_conversations ) * 100, 1 ) : 0,
			),
			array(
				'step'       => 'Purchased',
				'count'      => intval( $conversions ),
				'percentage' => $total_conversations > 0 ? round( ( $conversions / $total_conversations ) * 100, 1 ) : 0,
			),
		);

		wp_cache_set( $cache_key, $funnel, 'aica_conversion_tracking', HOUR_IN_SECONDS );

		return $funnel;
	}
}

// Initialize
// AICA_Conversion_Tracking::get_instance(); // Removed, handled by main plugin class