<?php
/**
 * Analytics Class
 * Tracks and reports on AI assistant usage
 *
 * @package AI_Commerce_Assistant
 */

if ( !defined( 'ABSPATH' ) ) {
	exit;
}

class AICA_Analytics {

	private static $instance = null;

	public static function get_instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	private function __construct() {
		add_action( 'aica_log_event', array( $this, 'log_event' ), 10, 3 );
	}

	public function log_event( $session_id, $event_type, $event_data = null ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert(
			$wpdb->prefix . 'aica_analytics',
			array(
				'session_id' => $session_id,
				'event_type' => $event_type,
				'event_data' => $event_data ? wp_json_encode( $event_data ) : null,
				'created_at' => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%s' )
		);

		return $result ? $wpdb->insert_id : false;
	}

	public function get_dashboard_stats( $period = 'month' ) {
		global $wpdb;

		// Validate period
		$valid_periods = array( 'today', 'week', 'month', 'year', 'all' );
		if ( ! in_array( $period, $valid_periods, true ) ) {
			$period = 'month';
		}

		$cache_key = 'aica_dashboard_stats_' . $period;
		$cached    = wp_cache_get( $cache_key, 'aica_analytics' );
		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
				$total_messages = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_conversations WHERE DATE(created_at) = CURDATE()" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$products_recommended = $wpdb->get_var( "SELECT COUNT(*) 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
				$cart_additions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' 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
				$total_messages = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$products_recommended = $wpdb->get_var( "SELECT COUNT(*) 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
				$cart_additions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' 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
				$total_messages = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$products_recommended = $wpdb->get_var( "SELECT COUNT(*) 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
				$cart_additions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' 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
				$total_messages = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$products_recommended = $wpdb->get_var( "SELECT COUNT(*) 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
				$cart_additions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart' 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
				$total_messages = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_conversations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$products_recommended = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended'" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$cart_additions = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'product_added_to_cart'" );
				break;
		}

		$avg_messages = $total_conversations > 0
			? round( $total_messages / $total_conversations, 1 )
			: 0;

		$conversion_rate = $products_recommended > 0
			? round( ( $cart_additions / $products_recommended ) * 100, 1 )
			: 0;

		$stats = array(
			'total_conversations'           => intval( $total_conversations ),
			'total_messages'                => intval( $total_messages ),
			'products_recommended'          => intval( $products_recommended ),
			'cart_additions'                => intval( $cart_additions ),
			'avg_messages_per_conversation' => $avg_messages,
			'conversion_rate'               => $conversion_rate,
		);

		wp_cache_set( $cache_key, $stats, 'aica_analytics', HOUR_IN_SECONDS );

		return $stats;
	}

	public function get_conversations_chart( $period = 'week' ) {
		global $wpdb;

		// Validate period
		$valid_periods = array( 'today', 'week', 'month', 'year', 'all' );
		if ( ! in_array( $period, $valid_periods, true ) ) {
			$period = 'week';
		}

		$cache_key = 'aica_conversations_chart_' . $period;
		$cached    = wp_cache_get( $cache_key, 'aica_analytics' );
		if ( false !== $cached ) {
			return $cached;
		}

		switch ( $period ) {
			case 'today':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') as date, COUNT(DISTINCT session_id) as count FROM {$wpdb->prefix}aica_conversations WHERE DATE(created_at) = CURDATE() GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d') ORDER BY date ASC" );
				break;
			case 'week':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') as date, COUNT(DISTINCT session_id) as count FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d') ORDER BY date ASC" );
				break;
			case 'month':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') as date, COUNT(DISTINCT session_id) as count FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d') ORDER BY date ASC" );
				break;
			case 'year':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( "SELECT DATE_FORMAT(created_at, '%Y-%m') as date, COUNT(DISTINCT session_id) as count FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY date ASC" );
				break;
			case 'all':
			default:
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results( "SELECT DATE_FORMAT(created_at, '%Y-%m-%d') as date, COUNT(DISTINCT session_id) as count FROM {$wpdb->prefix}aica_conversations GROUP BY DATE_FORMAT(created_at, '%Y-%m-%d') ORDER BY date ASC" );
				break;
		}

		$labels = array();
		$data   = array();

		foreach ( $results as $row ) {
			$labels[] = $row->date;
			$data[]   = intval( $row->count );
		}

		$chart = array(
			'labels' => $labels,
			'data'   => $data,
		);

		wp_cache_set( $cache_key, $chart, 'aica_analytics', HOUR_IN_SECONDS );

		return $chart;
	}

	public function get_top_products( $limit = 10, $period = 'month' ) {
		global $wpdb;

		// Validate period
		$valid_periods = array( 'today', 'week', 'month', 'year', 'all' );
		if ( ! in_array( $period, $valid_periods, true ) ) {
			$period = 'month';
		}

		$cache_key = 'aica_top_products_raw_' . $period . '_' . $limit;
		$cached    = wp_cache_get( $cache_key, 'aica_analytics' );
		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 JSON_EXTRACT(event_data, '$.product_ids') as product_ids, COUNT(*) as recommendation_count FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND DATE(created_at) = CURDATE() GROUP BY JSON_EXTRACT(event_data, '$.product_ids') ORDER BY recommendation_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 JSON_EXTRACT(event_data, '$.product_ids') as product_ids, COUNT(*) as recommendation_count FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY JSON_EXTRACT(event_data, '$.product_ids') ORDER BY recommendation_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 JSON_EXTRACT(event_data, '$.product_ids') as product_ids, COUNT(*) as recommendation_count FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY JSON_EXTRACT(event_data, '$.product_ids') ORDER BY recommendation_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 JSON_EXTRACT(event_data, '$.product_ids') as product_ids, COUNT(*) as recommendation_count FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY JSON_EXTRACT(event_data, '$.product_ids') ORDER BY recommendation_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 JSON_EXTRACT(event_data, '$.product_ids') as product_ids, COUNT(*) as recommendation_count FROM {$wpdb->prefix}aica_analytics WHERE event_type = 'products_recommended' GROUP BY JSON_EXTRACT(event_data, '$.product_ids') ORDER BY recommendation_count DESC LIMIT %d", $limit ) );
				break;
		}

		$products = array();

		foreach ( $results as $row ) {
			$product_ids = json_decode( $row->product_ids, true );

			if ( is_array( $product_ids ) ) {
				foreach ( $product_ids as $product_id ) {
					if ( !isset( $products[ $product_id ] ) ) {
						$product = wc_get_product( $product_id );

						if ( $product ) {
							$products[ $product_id ] = array(
								'id'                   => $product_id,
								'name'                 => $product->get_name(),
								'price'                => $product->get_price(),
								'image'                => wp_get_attachment_url( $product->get_image_id() ),
								'recommendation_count' => 0,
							);
						}
					}

					if ( isset( $products[ $product_id ] ) ) {
						++$products[ $product_id ]['recommendation_count'];
					}
				}
			}
		}

		usort(
			$products,
			function ( $a, $b ) {
				return $b['recommendation_count'] - $a['recommendation_count'];
			}
		);

		$top_products = array_slice( $products, 0, $limit );

		wp_cache_set( $cache_key, $top_products, 'aica_analytics', HOUR_IN_SECONDS );

		return $top_products;
	}

	public function get_popular_queries( $limit = 10, $period = 'month' ) {
		global $wpdb;

		// Validate period
		$valid_periods = array( 'today', 'week', 'month', 'year', 'all' );
		if ( ! in_array( $period, $valid_periods, true ) ) {
			$period = 'month';
		}

		$cache_key = 'aica_popular_queries_' . $period . '_' . $limit;
		$cached    = wp_cache_get( $cache_key, 'aica_analytics' );
		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 message, COUNT(*) as count FROM {$wpdb->prefix}aica_conversations WHERE role = 'user' AND DATE(created_at) = CURDATE() GROUP BY message ORDER BY 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 message, COUNT(*) as count FROM {$wpdb->prefix}aica_conversations WHERE role = 'user' AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY message ORDER BY 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 message, COUNT(*) as count FROM {$wpdb->prefix}aica_conversations WHERE role = 'user' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY message ORDER BY 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 message, COUNT(*) as count FROM {$wpdb->prefix}aica_conversations WHERE role = 'user' AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY message ORDER BY 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 message, COUNT(*) as count FROM {$wpdb->prefix}aica_conversations WHERE role = 'user' GROUP BY message ORDER BY count DESC LIMIT %d", $limit ) );
				break;
		}

		$popular = array_map(
			function ( $row ) {
				return array(
					'query' => $row->message,
					'count' => intval( $row->count ),
				);
			},
			$results
		);

		wp_cache_set( $cache_key, $popular, 'aica_analytics', HOUR_IN_SECONDS );

		return $popular;
	}

	/**
	 * Get WHERE clause for date filtering
	 *
	 * @param string $period Period
	 * @return string SQL WHERE clause
	 */
	private function get_where_clause( $period ) {
		switch ( $period ) {
			case 'today':
				return 'WHERE DATE(created_at) = CURDATE()';
			case 'week':
				return 'WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)';
			case 'month':
				return 'WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)';
			case 'year':
				return 'WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)';
			case 'all':
			default:
				return '';
		}
	}

	/**
	 * DEPRECATED: Use get_where_clause() instead
	 */
	public function get_date_condition( $period ) {
		return $this->get_where_clause( $period );
	}

	public function get_engagement_metrics( $period = 'month' ) {
		global $wpdb;

		// Validate period
		$valid_periods = array( 'today', 'week', 'month', 'year', 'all' );
		if ( ! in_array( $period, $valid_periods, true ) ) {
			$period = 'month';
		}

		$cache_key = 'aica_engagement_metrics_' . $period;
		$cached    = wp_cache_get( $cache_key, 'aica_analytics' );
		if ( false !== $cached ) {
			return $cached;
		}

		switch ( $period ) {
			case 'today':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$avg_duration = $wpdb->get_var( "SELECT AVG(duration) FROM (SELECT TIMESTAMPDIFF(MINUTE, MIN(created_at), MAX(created_at)) as duration FROM {$wpdb->prefix}aica_conversations WHERE DATE(created_at) = CURDATE() GROUP BY session_id HAVING duration > 0) as durations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$single_message_sessions = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT session_id FROM {$wpdb->prefix}aica_conversations WHERE DATE(created_at) = CURDATE() GROUP BY session_id HAVING COUNT(*) = 1) as single_messages" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_sessions = $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
				$returning_users = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT user_id FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND DATE(created_at) = CURDATE() GROUP BY user_id HAVING COUNT(DISTINCT session_id) > 1) as returning_users" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_users = $wpdb->get_var( "SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND DATE(created_at) = CURDATE()" );
				break;
			case 'week':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$avg_duration = $wpdb->get_var( "SELECT AVG(duration) FROM (SELECT TIMESTAMPDIFF(MINUTE, MIN(created_at), MAX(created_at)) as duration FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY session_id HAVING duration > 0) as durations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$single_message_sessions = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT session_id FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY session_id HAVING COUNT(*) = 1) as single_messages" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_sessions = $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
				$returning_users = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT user_id FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY user_id HAVING COUNT(DISTINCT session_id) > 1) as returning_users" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_users = $wpdb->get_var( "SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)" );
				break;
			case 'month':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$avg_duration = $wpdb->get_var( "SELECT AVG(duration) FROM (SELECT TIMESTAMPDIFF(MINUTE, MIN(created_at), MAX(created_at)) as duration FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY session_id HAVING duration > 0) as durations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$single_message_sessions = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT session_id FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY session_id HAVING COUNT(*) = 1) as single_messages" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_sessions = $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
				$returning_users = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT user_id FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY user_id HAVING COUNT(DISTINCT session_id) > 1) as returning_users" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_users = $wpdb->get_var( "SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)" );
				break;
			case 'year':
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$avg_duration = $wpdb->get_var( "SELECT AVG(duration) FROM (SELECT TIMESTAMPDIFF(MINUTE, MIN(created_at), MAX(created_at)) as duration FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY session_id HAVING duration > 0) as durations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$single_message_sessions = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT session_id FROM {$wpdb->prefix}aica_conversations WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY session_id HAVING COUNT(*) = 1) as single_messages" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_sessions = $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
				$returning_users = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT user_id FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR) GROUP BY user_id HAVING COUNT(DISTINCT session_id) > 1) as returning_users" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_users = $wpdb->get_var( "SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL AND created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)" );
				break;
			case 'all':
			default:
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$avg_duration = $wpdb->get_var( "SELECT AVG(duration) FROM (SELECT TIMESTAMPDIFF(MINUTE, MIN(created_at), MAX(created_at)) as duration FROM {$wpdb->prefix}aica_conversations GROUP BY session_id HAVING duration > 0) as durations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$single_message_sessions = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT session_id FROM {$wpdb->prefix}aica_conversations GROUP BY session_id HAVING COUNT(*) = 1) as single_messages" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_sessions = $wpdb->get_var( "SELECT COUNT(DISTINCT session_id) FROM {$wpdb->prefix}aica_conversations" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$returning_users = $wpdb->get_var( "SELECT COUNT(*) FROM (SELECT user_id FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL GROUP BY user_id HAVING COUNT(DISTINCT session_id) > 1) as returning_users" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$total_users = $wpdb->get_var( "SELECT COUNT(DISTINCT user_id) FROM {$wpdb->prefix}aica_conversations WHERE user_id IS NOT NULL" );
				break;
		}

		$bounce_rate = $total_sessions > 0
			? round( ( $single_message_sessions / $total_sessions ) * 100, 1 )
			: 0;

		$return_rate = $total_users > 0
			? round( ( $returning_users / $total_users ) * 100, 1 )
			: 0;

		$metrics = array(
			'avg_session_duration' => round( $avg_duration ?? 0, 1 ),
			'bounce_rate'          => $bounce_rate,
			'return_rate'          => $return_rate,
		);

		wp_cache_set( $cache_key, $metrics, 'aica_analytics', HOUR_IN_SECONDS );

		return $metrics;
	}

	public function cleanup_old_data( $days = 90 ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->prefix}aica_analytics
            WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
				$days
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->prefix}aica_conversations
            WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
				$days
			)
		);

		return $deleted;
	}
}

// Initialize
// AICA_Analytics::get_instance(); // Removed, handled by main plugin class