<?php
/**
 * Central analytics dashboard and tracking.
 *
 * @package Niwa_AI
 * @since   0.11.18
 */

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

class Niwa_AI_Analytics_Dashboard {

	const EVENTS_TABLE            = 'niwa_ai_frontend_analytics';
	const ORDER_RECORDED_META_KEY = '_niwa_ai_recommended_order_recorded';
	const RECOMMENDED_META_KEY    = '_niwa_ai_recommended';
	const SESSION_META_KEY        = '_niwa_ai_session';
	const SOURCE_META_KEY         = '_niwa_ai_source';
	const PAGE_URL_META_KEY       = '_niwa_ai_page_url';
	const CONTEXT_META_KEY        = '_niwa_ai_context';

	/**
	 * Register hooks.
	 */
	public function register_hooks() {
		add_action( 'admin_menu', array( $this, 'add_submenu' ) );
		add_action( 'wp_ajax_niwa_ai_track_frontend_event', array( $this, 'ajax_track_event' ) );
		add_action( 'wp_ajax_nopriv_niwa_ai_track_frontend_event', array( $this, 'ajax_track_event' ) );

		add_filter( 'woocommerce_add_cart_item_data', array( $this, 'capture_cart_item_data' ), 10, 4 );
		add_action( 'woocommerce_checkout_create_order_line_item', array( $this, 'add_order_item_meta' ), 10, 4 );
		add_action( 'woocommerce_order_status_processing', array( $this, 'track_recommended_order' ), 10, 1 );
		add_action( 'woocommerce_order_status_completed', array( $this, 'track_recommended_order' ), 10, 1 );
		add_action( 'woocommerce_order_status_on-hold', array( $this, 'track_recommended_order' ), 10, 1 );
	}

	/**
	 * Add submenu page.
	 */
	public function add_submenu() {
		add_submenu_page(
			'niwa-ai',
			__( 'Analytics', 'niwa-ai' ),
			__( 'Analytics', 'niwa-ai' ),
			'manage_options',
			'niwa-ai-analytics',
			array( $this, 'render_page' )
		);
	}

	/**
	 * Create analytics events table.
	 */
	public function create_events_table() {
		global $wpdb;

		$table_name      = $wpdb->prefix . self::EVENTS_TABLE;
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_type varchar(60) NOT NULL DEFAULT '',
			source varchar(80) NOT NULL DEFAULT '',
			page_url text NULL,
			page_path varchar(255) NOT NULL DEFAULT '',
			user_session varchar(100) NOT NULL DEFAULT '',
			user_ip varchar(45) NOT NULL DEFAULT '',
			product_id bigint(20) unsigned DEFAULT NULL,
			product_name varchar(255) NOT NULL DEFAULT '',
			coupon_code varchar(50) NOT NULL DEFAULT '',
			order_id bigint(20) unsigned DEFAULT NULL,
			quantity int(11) DEFAULT 0,
			revenue decimal(12,2) DEFAULT NULL,
			order_total decimal(12,2) DEFAULT NULL,
			meta longtext NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
			PRIMARY KEY (id),
			KEY event_type (event_type),
			KEY source (source),
			KEY created_at (created_at),
			KEY page_path (page_path(191)),
			KEY product_id (product_id),
			KEY order_id (order_id),
			KEY user_session (user_session)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * AJAX endpoint for frontend events.
	 */
	public function ajax_track_event() {
		check_ajax_referer( 'niwa_ai_chat_nonce', 'nonce' );
		if ( isset( $_SERVER['CONTENT_LENGTH'] ) && (int) $_SERVER['CONTENT_LENGTH'] > 32768 ) {
			wp_send_json_error( array( 'message' => 'Analytics payload is too large.' ), 413 );
		}

		$event_type = isset( $_POST['event_type'] ) ? sanitize_key( wp_unslash( $_POST['event_type'] ) ) : '';
		if ( ! in_array( $event_type, $this->get_allowed_event_types(), true ) ) {
			wp_send_json_error(
				array( 'message' => __( 'Invalid analytics event type.', 'niwa-ai' ) ),
				400
			);
		}

		$session = Niwa_AI_Session_Utils::resolve_user_session();
		$ip      = Niwa_AI_Session_Utils::get_client_ip();
		foreach (
			array(
				array( 'analytics_global_minute', 'global', 2000 ),
				array( 'analytics_ip_minute', $ip, 120 ),
				array( 'analytics_session_minute', $session, 120 ),
			) as $limit
		) {
			if ( ! Niwa_AI_Session_Utils::consume_fixed_window_limit( $limit[0], $limit[1], $limit[2], MINUTE_IN_SECONDS ) ) {
				wp_send_json_error( array( 'message' => 'Analytics request limit reached.' ), 429 );
			}
		}

		$ok = $this->record_event(
			array(
				'event_type'   => $event_type,
				'source'       => isset( $_POST['source'] ) ? sanitize_text_field( wp_unslash( $_POST['source'] ) ) : '',
				'page_url'     => isset( $_POST['page_url'] ) ? esc_url_raw( wp_unslash( $_POST['page_url'] ) ) : home_url( '/' ),
				'user_session' => $session,
				'user_ip'      => $ip,
				'product_id'   => isset( $_POST['product_id'] ) ? absint( $_POST['product_id'] ) : 0,
				'product_name' => isset( $_POST['product_name'] ) ? sanitize_text_field( wp_unslash( $_POST['product_name'] ) ) : '',
				'coupon_code'  => isset( $_POST['coupon_code'] ) ? sanitize_text_field( wp_unslash( $_POST['coupon_code'] ) ) : '',
				'order_id'     => isset( $_POST['order_id'] ) ? absint( $_POST['order_id'] ) : 0,
				'quantity'     => isset( $_POST['quantity'] ) ? max( 0, intval( wp_unslash( $_POST['quantity'] ) ) ) : 0,
				'revenue'      => isset( $_POST['revenue'] ) ? $this->sanitize_money( wp_unslash( $_POST['revenue'] ) ) : null,
				'order_total'  => isset( $_POST['order_total'] ) ? $this->sanitize_money( wp_unslash( $_POST['order_total'] ) ) : null,
				'meta'         => isset( $_POST['meta'] ) ? wp_unslash( $_POST['meta'] ) : '',
			)
		);

		if ( ! $ok ) {
			wp_send_json_error(
				array( 'message' => __( 'Failed to store analytics event.', 'niwa-ai' ) ),
				500
			);
		}

		wp_send_json_success( array( 'tracked' => true ) );
	}

	/**
	 * Add Niwa attribution data to cart items.
	 *
	 * @param array $cart_item_data Existing cart item data.
	 * @return array
	 */
	public function capture_cart_item_data( $cart_item_data, $product_id = 0, $variation_id = 0, $quantity = 0 ) {
		unset( $product_id, $variation_id, $quantity );

		if ( ! $this->has_niwa_request_attribution() ) {
			return $cart_item_data;
		}

		$session = Niwa_AI_Session_Utils::resolve_verified_session( false );
		if ( '' === $session ) {
			return $cart_item_data;
		}

		$cart_item_data[ self::RECOMMENDED_META_KEY ] = 'yes';
		$cart_item_data[ self::SESSION_META_KEY ]     = substr( $session, 0, Niwa_AI_Constants::SESSION_MAX_LENGTH );
		$cart_item_data[ self::SOURCE_META_KEY ]      = isset( $_REQUEST['niwa_ai_source'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['niwa_ai_source'] ) ) : 'niwa_card';
		$cart_item_data[ self::PAGE_URL_META_KEY ]    = isset( $_REQUEST['niwa_ai_page_url'] ) ? esc_url_raw( wp_unslash( $_REQUEST['niwa_ai_page_url'] ) ) : home_url( '/' );
		$cart_item_data[ self::CONTEXT_META_KEY ]     = isset( $_REQUEST['niwa_ai_context'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['niwa_ai_context'] ) ) : 'recommendation';

		return $cart_item_data;
	}

	/**
	 * Persist attribution data from cart item to order line item.
	 *
	 * @param WC_Order_Item_Product $item  Order item.
	 * @param string                $key   Cart item key.
	 * @param array                 $vals  Cart item values.
	 * @param WC_Order              $order Order.
	 */
	public function add_order_item_meta( $item, $key, $vals, $order ) {
		unset( $key, $order );

		if ( empty( $vals[ self::RECOMMENDED_META_KEY ] ) ) {
			return;
		}

		$item->add_meta_data( self::RECOMMENDED_META_KEY, 'yes', true );

		foreach ( array( self::SESSION_META_KEY, self::SOURCE_META_KEY, self::PAGE_URL_META_KEY, self::CONTEXT_META_KEY ) as $meta_key ) {
			if ( ! isset( $vals[ $meta_key ] ) || '' === (string) $vals[ $meta_key ] ) {
				continue;
			}

			$value = (string) $vals[ $meta_key ];
			if ( self::PAGE_URL_META_KEY === $meta_key ) {
				$value = esc_url_raw( $value );
			} else {
				$value = sanitize_text_field( $value );
			}

			$item->add_meta_data( $meta_key, $value, true );
		}
	}

	/**
	 * Record recommended product sales once per order.
	 *
	 * @param int $order_id WooCommerce order ID.
	 */
	public function track_recommended_order( $order_id ) {
		$order = function_exists( 'wc_get_order' ) ? wc_get_order( $order_id ) : false;
		if ( ! $order || $order->get_meta( self::ORDER_RECORDED_META_KEY, true ) ) {
			return;
		}

		$user_ip  = $order->get_customer_ip_address();
		$recorded = false;

		foreach ( $order->get_items( 'line_item' ) as $item ) {
			if ( 'yes' !== (string) $item->get_meta( self::RECOMMENDED_META_KEY, true ) ) {
				continue;
			}

			$this->record_event(
				array(
					'event_type'   => 'recommended_product_order',
					'source'       => sanitize_text_field( (string) $item->get_meta( self::SOURCE_META_KEY, true ) ) ?: 'recommended_product',
					'page_url'     => esc_url_raw( (string) $item->get_meta( self::PAGE_URL_META_KEY, true ) ),
					'user_session' => sanitize_text_field( (string) $item->get_meta( self::SESSION_META_KEY, true ) ),
					'user_ip'      => '' !== $user_ip ? $user_ip : Niwa_AI_Session_Utils::get_client_ip(),
					'product_id'   => $item->get_product_id(),
					'product_name' => $item->get_name(),
					'order_id'     => (int) $order_id,
					'quantity'     => max( 1, (int) $item->get_quantity() ),
					'revenue'      => (float) $item->get_total(),
					'order_total'  => (float) $order->get_total(),
					'meta'         => wp_json_encode(
						array(
							'context'      => sanitize_text_field( (string) $item->get_meta( self::CONTEXT_META_KEY, true ) ),
							'order_status' => $order->get_status(),
							'coupon_codes' => $order->get_coupon_codes(),
						)
					),
				)
			);

			$recorded = true;
		}

		if ( $recorded ) {
			$order->update_meta_data( self::ORDER_RECORDED_META_KEY, current_time( 'mysql' ) );
			$order->save();
		}
	}

	/**
	 * Render analytics page.
	 */
	public function render_page() {
		$this->create_events_table();
		$this->ensure_dependency_tables();

		$filters           = $this->get_filters();
		$frontend_summary      = $this->get_frontend_summary( $filters );
		$attention_summary     = $this->get_attention_summary( $filters );
		$coupon_summary        = $this->get_coupon_summary( $filters );
		$leave_stoper_summary  = $this->get_leave_stoper_summary( $filters );
		$all_influenced_orders = $this->get_influenced_orders( $filters, 0 );
		$influenced_orders     = array_slice( $all_influenced_orders, 0, 12 );
		$influenced_stats      = $this->build_influenced_summary( $all_influenced_orders );
		$top_products      = $this->get_top_products( $filters, 10 );
		$top_pages         = $this->get_top_pages( $filters, 10 );
		$recent_events     = $this->get_recent_frontend_events( $filters, 12 );

		echo '<div class="wrap niwa-wrap">';
		echo '<div class="niwa-page-header">';
		echo '<div class="niwa-page-header__icon">';
		echo '<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round"><path d="M3 3v18h18"/><path d="M7 14l3-3 3 2 4-6"/></svg>';
		echo '</div>';
		echo '<div class="niwa-page-header__text">';
		echo '<h1 class="niwa-page-header__title">' . esc_html__( 'Analytics', 'niwa-ai' ) . '</h1>';
		echo '<p class="niwa-page-header__subtitle">' . esc_html__( 'Unified Niwa AI performance dashboard for chathead interactions, recommendations, coupons, and influenced sales.', 'niwa-ai' ) . '</p>';
		echo '</div>';
		echo '</div>';

		$this->render_filters_card( $filters );

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Overview', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body"><div class="niwa-stats">';
		$this->render_stat_card( __( 'Tracked Events', 'niwa-ai' ), (string) $frontend_summary['total_events'] );
		$this->render_stat_card( __( 'Unique Sessions', 'niwa-ai' ), (string) $frontend_summary['unique_sessions'] );
		$this->render_stat_card( __( 'Chathead Opens', 'niwa-ai' ), (string) $frontend_summary['chathead_open'] );
		$this->render_stat_card( __( 'Chat Messages', 'niwa-ai' ), (string) $frontend_summary['chat_message_sent'] );
		$this->render_stat_card( __( 'Card Clicks', 'niwa-ai' ), (string) $frontend_summary['product_card_click'] );
		$this->render_stat_card( __( 'Add To Cart', 'niwa-ai' ), (string) $frontend_summary['product_add_to_cart'] );
		$this->render_stat_card( __( 'Buy Now', 'niwa-ai' ), (string) $frontend_summary['product_buy_now'] );
		$this->render_stat_card( __( 'Influenced Orders', 'niwa-ai' ), (string) $influenced_stats['orders'] );
		$this->render_stat_card( __( 'Influenced Revenue', 'niwa-ai' ), $this->format_money( $influenced_stats['revenue'] ) );
		echo '</div></div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Recommendation Performance', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body"><div class="niwa-stats">';
		$this->render_stat_card( __( 'Impressions', 'niwa-ai' ), (string) $frontend_summary['recommendation_impression'] );
		$this->render_stat_card( __( 'Card Click Rate', 'niwa-ai' ), $this->format_percentage( $frontend_summary['card_click_rate'] ) );
		$this->render_stat_card( __( 'Checkout Clicks', 'niwa-ai' ), (string) $frontend_summary['product_checkout_click'] );
		$this->render_stat_card( __( 'Products Sold', 'niwa-ai' ), (string) $frontend_summary['products_sold'] );
		$this->render_stat_card( __( 'Attributed Orders', 'niwa-ai' ), (string) $frontend_summary['attributed_orders'] );
		$this->render_stat_card( __( 'Recommendation Revenue', 'niwa-ai' ), $this->format_money( $frontend_summary['attributed_revenue'] ) );
		echo '</div></div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Attention Capture', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body"><div class="niwa-stats">';
		$this->render_stat_card( __( 'Bubbles Shown', 'niwa-ai' ), (string) $attention_summary['bubble_shown'] );
		$this->render_stat_card( __( 'Bubble Clicks', 'niwa-ai' ), (string) $attention_summary['bubble_clicked'] );
		$this->render_stat_card( __( 'Action Clicks', 'niwa-ai' ), (string) $attention_summary['action_clicked'] );
		$this->render_stat_card( __( 'CB Mini Starts', 'niwa-ai' ), (string) $attention_summary['cb_mini_started'] );
		$this->render_stat_card( __( 'Launcher Clicks', 'niwa-ai' ), (string) $attention_summary['launcher_click'] );
		$this->render_stat_card( __( 'Bubble CTR', 'niwa-ai' ), $this->format_percentage( $attention_summary['click_rate'] ) );
		echo '</div></div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Conversion Busters', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body"><div class="niwa-stats">';
		$this->render_stat_card( __( 'Coupons Generated', 'niwa-ai' ), (string) $coupon_summary['generated'] );
		$this->render_stat_card( __( 'Coupons Used', 'niwa-ai' ), (string) $coupon_summary['used'] );
		$this->render_stat_card( __( 'Coupon Copy Clicks', 'niwa-ai' ), (string) $frontend_summary['coupon_copy'] );
		$this->render_stat_card( __( 'Redeem Rate', 'niwa-ai' ), $this->format_percentage( $coupon_summary['redeem_rate'] ) );
		$this->render_stat_card( __( 'Coupon Revenue', 'niwa-ai' ), $this->format_money( $coupon_summary['coupon_revenue'] ) );
		$this->render_stat_card( __( 'Discount Given', 'niwa-ai' ), $this->format_money( $coupon_summary['discount_total'] ) );
		echo '</div></div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Leave Stoper', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body"><div class="niwa-stats">';
		$this->render_stat_card( __( 'Triggers', 'niwa-ai' ), (string) $leave_stoper_summary['trigger_count'] );
		$this->render_stat_card( __( 'Messages Shown', 'niwa-ai' ), (string) $leave_stoper_summary['message_shown_count'] );
		$this->render_stat_card( __( 'Triggered Sessions', 'niwa-ai' ), (string) $leave_stoper_summary['triggered_sessions'] );
		$this->render_stat_card( __( 'Post-LS Interactions', 'niwa-ai' ), (string) $leave_stoper_summary['interaction_count'] );
		$this->render_stat_card( __( 'LS Engagement Rate', 'niwa-ai' ), $this->format_percentage( $leave_stoper_summary['engagement_rate'] ) );
		$this->render_stat_card( __( 'Pre-LS Baseline', 'niwa-ai' ), $this->format_percentage( $leave_stoper_summary['pre_engagement_rate'] ) );
		$this->render_stat_card( __( 'Recovery Rate', 'niwa-ai' ), $this->format_percentage( $leave_stoper_summary['recovery_rate'] ) );
		echo '</div></div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Top Recommended Products', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body">';
		$this->render_top_products_table( $top_products );
		echo '</div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Top Pages', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body">';
		$this->render_top_pages_table( $top_pages );
		echo '</div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Recent Influenced Orders', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body">';
		$this->render_influenced_orders_table( $influenced_orders );
		echo '</div></div>';

		echo '<div class="niwa-card"><div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Recent Frontend Events', 'niwa-ai' ) . '</h2></div><div class="niwa-card__body">';
		$this->render_recent_events_table( $recent_events );
		echo '</div></div>';

		/**
		 * Extension point for additional analytics sections. Modules
		 * (Cart Recovery, future features) attach their own renderers
		 * here so the dashboard stays agnostic.
		 *
		 * @param array  $filters Current dashboard filters.
		 * @param object $this    The dashboard instance — use for
		 *                        `render_stat_card` / `format_money`.
		 */
		do_action( 'niwa_ai_analytics_dashboard_extra_sections', $filters, $this );

		echo '</div>';
	}

	/**
	 * Ensure dependent analytics tables exist.
	 */
	private function ensure_dependency_tables() {
		$attention_capture = new Niwa_AI_Attention_Capture();
		$attention_capture->create_analytics_table();

		$conversion_busters = new Niwa_AI_Conversion_Busters();
		$conversion_busters->create_analytics_table();
	}

	/**
	 * Return current dashboard filters.
	 *
	 * @return array
	 */
	private function get_filters() {
		$days = isset( $_GET['analytics_days'] ) ? absint( wp_unslash( $_GET['analytics_days'] ) ) : 30;
		$page = isset( $_GET['analytics_page_path'] ) ? sanitize_text_field( wp_unslash( $_GET['analytics_page_path'] ) ) : '';

		return array(
			'days'          => max( 1, $days ),
			'page_contains' => $page,
		);
	}

	/**
	 * Get frontend summary counters.
	 *
	 * @param array $filters Dashboard filters.
	 * @return array
	 */
	private function get_frontend_summary( $filters ) {
		global $wpdb;

		$table_name = $wpdb->prefix . self::EVENTS_TABLE;
		$params     = array();
		$where_sql  = $this->build_events_where_clause( $filters, $params );

		$query = $wpdb->prepare(
			"SELECT
				COUNT(*) AS total_events,
				COUNT(DISTINCT user_session) AS unique_sessions,
				SUM(CASE WHEN event_type = 'chathead_open' THEN 1 ELSE 0 END) AS chathead_open,
				SUM(CASE WHEN event_type = 'chat_message_sent' THEN 1 ELSE 0 END) AS chat_message_sent,
				SUM(CASE WHEN event_type = 'recommendation_impression' THEN 1 ELSE 0 END) AS recommendation_impression,
				SUM(CASE WHEN event_type = 'product_card_click' THEN 1 ELSE 0 END) AS product_card_click,
				SUM(CASE WHEN event_type = 'product_add_to_cart' THEN 1 ELSE 0 END) AS product_add_to_cart,
				SUM(CASE WHEN event_type = 'product_buy_now' THEN 1 ELSE 0 END) AS product_buy_now,
				SUM(CASE WHEN event_type = 'product_checkout_click' THEN 1 ELSE 0 END) AS product_checkout_click,
				SUM(CASE WHEN event_type = 'coupon_copy' THEN 1 ELSE 0 END) AS coupon_copy,
				SUM(CASE WHEN event_type = 'recommended_product_order' THEN quantity ELSE 0 END) AS products_sold,
				COUNT(DISTINCT CASE WHEN event_type = 'recommended_product_order' THEN order_id ELSE NULL END) AS attributed_orders,
				SUM(CASE WHEN event_type = 'recommended_product_order' THEN revenue ELSE 0 END) AS attributed_revenue
			FROM {$table_name}
			WHERE {$where_sql}",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( $query, ARRAY_A );

		$impressions = isset( $row['recommendation_impression'] ) ? (int) $row['recommendation_impression'] : 0;
		$clicks      = isset( $row['product_card_click'] ) ? (int) $row['product_card_click'] : 0;

		return array(
			'total_events'              => isset( $row['total_events'] ) ? (int) $row['total_events'] : 0,
			'unique_sessions'           => isset( $row['unique_sessions'] ) ? (int) $row['unique_sessions'] : 0,
			'chathead_open'             => isset( $row['chathead_open'] ) ? (int) $row['chathead_open'] : 0,
			'chat_message_sent'         => isset( $row['chat_message_sent'] ) ? (int) $row['chat_message_sent'] : 0,
			'recommendation_impression' => $impressions,
			'product_card_click'        => $clicks,
			'product_add_to_cart'       => isset( $row['product_add_to_cart'] ) ? (int) $row['product_add_to_cart'] : 0,
			'product_buy_now'           => isset( $row['product_buy_now'] ) ? (int) $row['product_buy_now'] : 0,
			'product_checkout_click'    => isset( $row['product_checkout_click'] ) ? (int) $row['product_checkout_click'] : 0,
			'coupon_copy'               => isset( $row['coupon_copy'] ) ? (int) $row['coupon_copy'] : 0,
			'products_sold'             => isset( $row['products_sold'] ) ? (int) $row['products_sold'] : 0,
			'attributed_orders'         => isset( $row['attributed_orders'] ) ? (int) $row['attributed_orders'] : 0,
			'attributed_revenue'        => isset( $row['attributed_revenue'] ) ? (float) $row['attributed_revenue'] : 0.0,
			'card_click_rate'           => $impressions > 0 ? round( ( $clicks / $impressions ) * 100, 1 ) : 0.0,
		);
	}

	/**
	 * Get Attention Capture summary counters.
	 *
	 * @param array $filters Dashboard filters.
	 * @return array
	 */
	private function get_attention_summary( $filters ) {
		global $wpdb;

		$table_name = $wpdb->prefix . Niwa_AI_Attention_Capture::ANALYTICS_TABLE;
		$params     = array();
		$where_sql  = $this->build_time_page_where_clause( $filters, $params, 'created_at', 'page_path' );

		$query = $wpdb->prepare(
			"SELECT
				SUM(CASE WHEN event_type = 'bubble_shown' THEN 1 ELSE 0 END) AS bubble_shown,
				SUM(CASE WHEN event_type = 'bubble_clicked' THEN 1 ELSE 0 END) AS bubble_clicked,
				SUM(CASE WHEN event_type = 'action_button_clicked' THEN 1 ELSE 0 END) AS action_clicked,
				SUM(CASE WHEN event_type = 'cb_mini_started' THEN 1 ELSE 0 END) AS cb_mini_started,
				SUM(CASE WHEN event_type = 'launcher_click' THEN 1 ELSE 0 END) AS launcher_click
			FROM {$table_name}
			WHERE {$where_sql}",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( $query, ARRAY_A );

		$bubble_shown   = isset( $row['bubble_shown'] ) ? (int) $row['bubble_shown'] : 0;
		$bubble_clicked = isset( $row['bubble_clicked'] ) ? (int) $row['bubble_clicked'] : 0;

		return array(
			'bubble_shown'    => $bubble_shown,
			'bubble_clicked'  => $bubble_clicked,
			'action_clicked'  => isset( $row['action_clicked'] ) ? (int) $row['action_clicked'] : 0,
			'cb_mini_started' => isset( $row['cb_mini_started'] ) ? (int) $row['cb_mini_started'] : 0,
			'launcher_click'  => isset( $row['launcher_click'] ) ? (int) $row['launcher_click'] : 0,
			'click_rate'      => $bubble_shown > 0 ? round( ( $bubble_clicked / $bubble_shown ) * 100, 1 ) : 0.0,
		);
	}

	/**
	 * Get coupon summary counters.
	 *
	 * @param array $filters Dashboard filters.
	 * @return array
	 */
	private function get_coupon_summary( $filters ) {
		global $wpdb;

		$table_name = $wpdb->prefix . Niwa_AI_Conversion_Busters::ANALYTICS_TABLE;
		$params     = array();
		$where_sql  = $this->build_time_where_clause( $filters, $params, 'created_at' );

		$query = $wpdb->prepare(
			"SELECT
				COUNT(*) AS generated,
				SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) AS expired,
				SUM(CASE WHEN status = 'created' THEN 1 ELSE 0 END) AS active
			FROM {$table_name}
			WHERE {$where_sql}",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$created_row = $wpdb->get_row( $query, ARRAY_A );

		$params    = array();
		$where_sql = $this->build_time_where_clause( $filters, $params, 'used_at' );
		$query     = $wpdb->prepare(
			"SELECT
				COUNT(*) AS used,
				SUM(COALESCE(order_total, 0)) AS coupon_revenue,
				SUM(COALESCE(discount_amount, 0)) AS discount_total
			FROM {$table_name}
			WHERE {$where_sql}
			  AND status = 'used'",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$used_row  = $wpdb->get_row( $query, ARRAY_A );
		$generated = isset( $created_row['generated'] ) ? (int) $created_row['generated'] : 0;
		$used      = isset( $used_row['used'] ) ? (int) $used_row['used'] : 0;

		return array(
			'generated'      => $generated,
			'used'           => $used,
			'expired'        => isset( $created_row['expired'] ) ? (int) $created_row['expired'] : 0,
			'active'         => isset( $created_row['active'] ) ? (int) $created_row['active'] : 0,
			'coupon_revenue' => isset( $used_row['coupon_revenue'] ) ? (float) $used_row['coupon_revenue'] : 0.0,
			'discount_total' => isset( $used_row['discount_total'] ) ? (float) $used_row['discount_total'] : 0.0,
			'redeem_rate'    => $generated > 0 ? round( ( $used / $generated ) * 100, 1 ) : 0.0,
		);
	}

	/**
	 * Get Leave Stoper interaction and recovery summary.
	 *
	 * @param array $filters Dashboard filters.
	 * @return array
	 */
	private function get_leave_stoper_summary( $filters ) {
		global $wpdb;

		$table_name = $wpdb->prefix . self::EVENTS_TABLE;
		$params     = array();
		$where_sql  = $this->build_events_where_clause( $filters, $params );

		$query = $wpdb->prepare(
			"SELECT user_session, event_type, meta, created_at
			FROM {$table_name}
			WHERE {$where_sql}",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $query, ARRAY_A );

		return $this->calculate_leave_stoper_summary_from_rows( $rows );
	}

	/**
	 * Calculate Leave Stoper summary from event rows.
	 *
	 * @param array $rows Analytics event rows.
	 * @return array
	 */
	private function calculate_leave_stoper_summary_from_rows( $rows ) {
		$engagement_events = array(
			'chat_message_sent',
			'product_card_click',
			'product_add_to_cart',
			'product_buy_now',
			'product_checkout_click',
			'coupon_copy',
			'recommended_product_order',
		);

		$all_sessions             = array();
		$triggered_sessions       = array();
		$trigger_times            = array();
		$control_sessions         = array();
		$engaged_after_sessions   = array();
		$engaged_before_sessions  = array();
		$engaged_control_sessions = array();
		$recovered_sessions       = array();
		$trigger_count            = 0;
		$message_shown_count      = 0;
		$interaction_count        = 0;

		foreach ( $rows as $row ) {
			$session = isset( $row['user_session'] ) ? (string) $row['user_session'] : '';
			if ( '' === $session ) {
				continue;
			}

			$all_sessions[ $session ] = true;
			$event_type               = isset( $row['event_type'] ) ? (string) $row['event_type'] : '';

			if ( 'leave_stoper_triggered' === $event_type ) {
				$triggered_sessions[ $session ] = true;
				$trigger_count++;

				$created_at = isset( $row['created_at'] ) ? (string) $row['created_at'] : '';
				if ( '' !== $created_at && ( ! isset( $trigger_times[ $session ] ) || $created_at < $trigger_times[ $session ] ) ) {
					$trigger_times[ $session ] = $created_at;
				}
			}

			if ( 'leave_stoper_message_shown' === $event_type ) {
				$message_shown_count++;
			}
		}

		foreach ( $rows as $row ) {
			$session = isset( $row['user_session'] ) ? (string) $row['user_session'] : '';
			if ( '' === $session ) {
				continue;
			}

			$event_type = isset( $row['event_type'] ) ? (string) $row['event_type'] : '';
			if ( ! in_array( $event_type, $engagement_events, true ) ) {
				continue;
			}

			$meta         = $this->decode_event_meta( $row['meta'] ?? '' );
			$created_at   = isset( $row['created_at'] ) ? (string) $row['created_at'] : '';
			$has_trigger  = isset( $triggered_sessions[ $session ] );
			$trigger_time = $trigger_times[ $session ] ?? '';
			$is_after     = $has_trigger && ! empty( $meta['leave_stoper_active'] );

			if ( $has_trigger && '' !== $created_at && '' !== $trigger_time && $created_at >= $trigger_time ) {
				$is_after = true;
			}

			if ( $has_trigger && $is_after ) {
				$engaged_after_sessions[ $session ] = true;
				$interaction_count++;
				continue;
			}

			if ( $has_trigger ) {
				$engaged_before_sessions[ $session ] = true;
			}
		}

		foreach ( array_keys( $all_sessions ) as $session ) {
			if ( isset( $triggered_sessions[ $session ] ) ) {
				continue;
			}

			$control_sessions[ $session ] = true;
		}

		foreach ( $rows as $row ) {
			$session    = isset( $row['user_session'] ) ? (string) $row['user_session'] : '';
			$event_type = isset( $row['event_type'] ) ? (string) $row['event_type'] : '';
			$meta       = $this->decode_event_meta( $row['meta'] ?? '' );

			if ( '' === $session || ! isset( $control_sessions[ $session ] ) || ! in_array( $event_type, $engagement_events, true ) ) {
				continue;
			}

			if ( ! empty( $meta['leave_stoper_active'] ) ) {
				continue;
			}

			$engaged_control_sessions[ $session ] = true;
		}

		foreach ( array_keys( $engaged_after_sessions ) as $session ) {
			if ( isset( $engaged_before_sessions[ $session ] ) ) {
				continue;
			}

			$recovered_sessions[ $session ] = true;
		}

		$triggered_count = count( $triggered_sessions );
		$control_count   = count( $control_sessions );
		$engagement_rate = $triggered_count > 0 ? round( ( count( $engaged_after_sessions ) / $triggered_count ) * 100, 1 ) : 0.0;
		$pre_rate        = $triggered_count > 0 ? round( ( count( $engaged_before_sessions ) / $triggered_count ) * 100, 1 ) : 0.0;
		$recoverable     = max( 0, $triggered_count - count( $engaged_before_sessions ) );
		$recovery_rate   = $recoverable > 0 ? round( ( count( $recovered_sessions ) / $recoverable ) * 100, 1 ) : 0.0;
		$control_rate    = $control_count > 0 ? round( ( count( $engaged_control_sessions ) / $control_count ) * 100, 1 ) : 0.0;

		return array(
			'trigger_count'           => $trigger_count,
			'message_shown_count'     => $message_shown_count,
			'triggered_sessions'      => $triggered_count,
			'interaction_count'       => $interaction_count,
			'engaged_sessions'        => count( $engaged_after_sessions ),
			'pre_engaged_sessions'    => count( $engaged_before_sessions ),
			'recovered_sessions'      => count( $recovered_sessions ),
			'recoverable_sessions'    => $recoverable,
			'control_sessions'        => $control_count,
			'control_engaged'         => count( $engaged_control_sessions ),
			'engagement_rate'         => $engagement_rate,
			'pre_engagement_rate'     => $pre_rate,
			'recovery_rate'           => $recovery_rate,
			'control_engagement_rate' => $control_rate,
		);
	}

	/**
	 * Get top recommended products.
	 *
	 * @param array $filters Dashboard filters.
	 * @param int   $limit   Max rows.
	 * @return array
	 */
	private function get_top_products( $filters, $limit ) {
		global $wpdb;

		$table_name = $wpdb->prefix . self::EVENTS_TABLE;
		$params     = array();
		$where_sql  = $this->build_events_where_clause( $filters, $params );
		$params[]   = max( 1, absint( $limit ) );

		$query = $wpdb->prepare(
			"SELECT
				product_id,
				MAX(product_name) AS product_name,
				SUM(CASE WHEN event_type = 'recommendation_impression' THEN 1 ELSE 0 END) AS impressions,
				SUM(CASE WHEN event_type = 'product_card_click' THEN 1 ELSE 0 END) AS clicks,
				SUM(CASE WHEN event_type = 'product_add_to_cart' THEN 1 ELSE 0 END) AS add_to_cart,
				SUM(CASE WHEN event_type = 'product_buy_now' THEN 1 ELSE 0 END) AS buy_now,
				SUM(CASE WHEN event_type = 'recommended_product_order' THEN quantity ELSE 0 END) AS sold_qty,
				SUM(CASE WHEN event_type = 'recommended_product_order' THEN revenue ELSE 0 END) AS revenue
			FROM {$table_name}
			WHERE {$where_sql}
			  AND product_id IS NOT NULL
			  AND product_id > 0
			GROUP BY product_id
			ORDER BY revenue DESC, sold_qty DESC, clicks DESC
			LIMIT %d",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_results( $query, ARRAY_A );
	}

	/**
	 * Get merged top pages across AC and frontend interactions.
	 *
	 * @param array $filters Dashboard filters.
	 * @param int   $limit   Max rows.
	 * @return array
	 */
	private function get_top_pages( $filters, $limit ) {
		global $wpdb;

		$pages = array();

		$events_table = $wpdb->prefix . self::EVENTS_TABLE;
		$params       = array();
		$where_sql    = $this->build_time_page_where_clause( $filters, $params, 'created_at', 'page_path' );
		$query        = $wpdb->prepare(
			"SELECT
				page_path,
				COUNT(*) AS total_events,
				SUM(CASE WHEN event_type = 'product_card_click' THEN 1 ELSE 0 END) AS card_clicks,
				SUM(CASE WHEN event_type IN ('product_add_to_cart', 'product_buy_now', 'product_checkout_click') THEN 1 ELSE 0 END) AS conversion_actions
			FROM {$events_table}
			WHERE {$where_sql}
			  AND page_path <> ''
			GROUP BY page_path",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		foreach ( $wpdb->get_results( $query, ARRAY_A ) as $row ) {
			$page_path = $row['page_path'] ?? '/';
			$pages[ $page_path ] = array(
				'page_path'          => $page_path,
				'total_events'       => (int) ( $row['total_events'] ?? 0 ),
				'card_clicks'        => (int) ( $row['card_clicks'] ?? 0 ),
				'conversion_actions' => (int) ( $row['conversion_actions'] ?? 0 ),
				'bubble_shown'       => 0,
				'bubble_engagements' => 0,
			);
		}

		$ac_table  = $wpdb->prefix . Niwa_AI_Attention_Capture::ANALYTICS_TABLE;
		$params    = array();
		$where_sql = $this->build_time_page_where_clause( $filters, $params, 'created_at', 'page_path' );
		$query     = $wpdb->prepare(
			"SELECT
				page_path,
				SUM(CASE WHEN event_type = 'bubble_shown' THEN 1 ELSE 0 END) AS bubble_shown,
				SUM(CASE WHEN event_type IN ('bubble_clicked', 'action_button_clicked', 'launcher_click') THEN 1 ELSE 0 END) AS bubble_engagements
			FROM {$ac_table}
			WHERE {$where_sql}
			  AND page_path <> ''
			GROUP BY page_path",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		foreach ( $wpdb->get_results( $query, ARRAY_A ) as $row ) {
			$page_path = $row['page_path'] ?? '/';
			if ( ! isset( $pages[ $page_path ] ) ) {
				$pages[ $page_path ] = array(
					'page_path'          => $page_path,
					'total_events'       => 0,
					'card_clicks'        => 0,
					'conversion_actions' => 0,
					'bubble_shown'       => 0,
					'bubble_engagements' => 0,
				);
			}

			$pages[ $page_path ]['bubble_shown']       += (int) ( $row['bubble_shown'] ?? 0 );
			$pages[ $page_path ]['bubble_engagements'] += (int) ( $row['bubble_engagements'] ?? 0 );
		}

		usort(
			$pages,
			static function ( $left, $right ) {
				$left_total  = (int) $left['total_events'] + (int) $left['bubble_shown'] + (int) $left['bubble_engagements'];
				$right_total = (int) $right['total_events'] + (int) $right['bubble_shown'] + (int) $right['bubble_engagements'];
				return $right_total <=> $left_total;
			}
		);

		return array_slice( $pages, 0, max( 1, absint( $limit ) ) );
	}

	/**
	 * Get recent tracked frontend events.
	 *
	 * @param array $filters Dashboard filters.
	 * @param int   $limit   Max rows.
	 * @return array
	 */
	private function get_recent_frontend_events( $filters, $limit ) {
		global $wpdb;

		$table_name = $wpdb->prefix . self::EVENTS_TABLE;
		$params     = array();
		$where_sql  = $this->build_events_where_clause( $filters, $params );
		$params[]   = max( 1, absint( $limit ) );

		$query = $wpdb->prepare(
			"SELECT event_type, source, product_name, coupon_code, page_path, created_at
			FROM {$table_name}
			WHERE {$where_sql}
			ORDER BY created_at DESC
			LIMIT %d",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_results( $query, ARRAY_A );
	}

	/**
	 * Get influenced orders from both recommendations and coupons.
	 *
	 * @param array $filters Dashboard filters.
	 * @param int   $limit   Max rows.
	 * @return array
	 */
	private function get_influenced_orders( $filters, $limit ) {
		global $wpdb;

		$orders = array();

		$events_table = $wpdb->prefix . self::EVENTS_TABLE;
		$params       = array();
		$where_sql    = $this->build_time_where_clause( $filters, $params, 'created_at' );
		$query        = $wpdb->prepare(
			"SELECT order_id, MAX(order_total) AS order_total, MAX(created_at) AS happened_at
			FROM {$events_table}
			WHERE {$where_sql}
			  AND event_type = 'recommended_product_order'
			  AND order_id IS NOT NULL
			  AND order_id > 0
			GROUP BY order_id",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		foreach ( $wpdb->get_results( $query, ARRAY_A ) as $row ) {
			$order_id = (int) ( $row['order_id'] ?? 0 );
			if ( $order_id <= 0 ) {
				continue;
			}

			$orders[ $order_id ] = array(
				'order_id'    => $order_id,
				'order_total' => isset( $row['order_total'] ) ? (float) $row['order_total'] : 0.0,
				'happened_at' => isset( $row['happened_at'] ) ? (string) $row['happened_at'] : '',
				'sources'     => array( 'recommendation' ),
			);
		}

		$coupons_table = $wpdb->prefix . Niwa_AI_Conversion_Busters::ANALYTICS_TABLE;
		$params        = array();
		$where_sql     = $this->build_time_where_clause( $filters, $params, 'used_at' );
		$query         = $wpdb->prepare(
			"SELECT order_id, MAX(order_total) AS order_total, MAX(used_at) AS happened_at
			FROM {$coupons_table}
			WHERE {$where_sql}
			  AND status = 'used'
			  AND order_id IS NOT NULL
			  AND order_id > 0
			GROUP BY order_id",
			$params
		);

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		foreach ( $wpdb->get_results( $query, ARRAY_A ) as $row ) {
			$order_id = (int) ( $row['order_id'] ?? 0 );
			if ( $order_id <= 0 ) {
				continue;
			}

			if ( ! isset( $orders[ $order_id ] ) ) {
				$orders[ $order_id ] = array(
					'order_id'    => $order_id,
					'order_total' => isset( $row['order_total'] ) ? (float) $row['order_total'] : 0.0,
					'happened_at' => isset( $row['happened_at'] ) ? (string) $row['happened_at'] : '',
					'sources'     => array(),
				);
			}

			if ( ! in_array( 'coupon', $orders[ $order_id ]['sources'], true ) ) {
				$orders[ $order_id ]['sources'][] = 'coupon';
			}

			$orders[ $order_id ]['order_total'] = max(
				(float) $orders[ $order_id ]['order_total'],
				isset( $row['order_total'] ) ? (float) $row['order_total'] : 0.0
			);

			if ( isset( $row['happened_at'] ) && $row['happened_at'] > $orders[ $order_id ]['happened_at'] ) {
				$orders[ $order_id ]['happened_at'] = (string) $row['happened_at'];
			}
		}

		usort(
			$orders,
			static function ( $left, $right ) {
				return strcmp( (string) $right['happened_at'], (string) $left['happened_at'] );
			}
		);

		if ( $limit <= 0 ) {
			return array_values( $orders );
		}

		return array_slice( $orders, 0, max( 1, absint( $limit ) ) );
	}

	/**
	 * Build influenced order stats from rows.
	 *
	 * @param array $orders Influenced orders.
	 * @return array
	 */
	private function build_influenced_summary( $orders ) {
		$revenue = 0.0;

		foreach ( $orders as $order ) {
			$revenue += isset( $order['order_total'] ) ? (float) $order['order_total'] : 0.0;
		}

		return array(
			'orders'  => count( $orders ),
			'revenue' => $revenue,
		);
	}

	/**
	 * Render filters.
	 *
	 * @param array $filters Filters.
	 */
	private function render_filters_card( $filters ) {
		$reset_url = admin_url( 'admin.php?page=niwa-ai-analytics' );

		echo '<div class="niwa-card">';
		echo '<div class="niwa-card__header"><h2 class="niwa-card__title">' . esc_html__( 'Filters', 'niwa-ai' ) . '</h2></div>';
		echo '<div class="niwa-card__body">';
		echo '<form method="get" style="display:flex;gap:12px;flex-wrap:wrap;align-items:flex-end;">';
		echo '<input type="hidden" name="page" value="niwa-ai-analytics" />';

		echo '<div class="niwa-field" style="margin-bottom:0;">';
		echo '<label class="niwa-field__label">' . esc_html__( 'Period', 'niwa-ai' ) . '</label>';
		echo '<select name="analytics_days">';
		foreach ( array( 1, 7, 14, 30, 90 ) as $days ) {
			$label = sprintf( 'Last %d day%s', $days, 1 === $days ? '' : 's' );
			echo '<option value="' . esc_attr( (string) $days ) . '" ' . selected( (int) $filters['days'], $days, false ) . '>' . esc_html( $label ) . '</option>';
		}
		echo '</select>';
		echo '</div>';

		echo '<div class="niwa-field" style="margin-bottom:0;">';
		echo '<label class="niwa-field__label">' . esc_html__( 'Page contains', 'niwa-ai' ) . '</label>';
		echo '<input type="text" name="analytics_page_path" value="' . esc_attr( $filters['page_contains'] ) . '" placeholder="/product/..." class="niwa-input--wide" />';
		echo '</div>';

		echo '<div style="display:flex;gap:8px;align-items:flex-end;padding-bottom:2px;">';
		submit_button( __( 'Apply Filters', 'niwa-ai' ), 'primary niwa-btn niwa-btn--primary', '', false );
		echo '<a class="button niwa-btn niwa-btn--secondary" href="' . esc_url( $reset_url ) . '">' . esc_html__( 'Reset', 'niwa-ai' ) . '</a>';
		echo '</div>';

		echo '</form>';
		echo '</div>';
		echo '</div>';
	}

	/**
	 * Render a stat card.
	 *
	 * @param string $label Label.
	 * @param string $value Value.
	 */
	public function render_stat_card( $label, $value ) {
		echo '<div class="niwa-stat">';
		echo '<div class="niwa-stat__label">' . esc_html( $label ) . '</div>';
		echo '<div class="niwa-stat__value">' . esc_html( $value ) . '</div>';
		echo '</div>';
	}

	/**
	 * Render top products table.
	 *
	 * @param array $rows Product rows.
	 */
	private function render_top_products_table( $rows ) {
		if ( empty( $rows ) ) {
			$this->render_empty_notice( __( 'No recommendation product data for selected filters.', 'niwa-ai' ) );
			return;
		}

		echo '<table class="niwa-table"><thead><tr>';
		echo '<th>' . esc_html__( 'Product', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Impressions', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Clicks', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Add To Cart', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Buy Now', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Sold Qty', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Revenue', 'niwa-ai' ) . '</th>';
		echo '</tr></thead><tbody>';

		foreach ( $rows as $row ) {
			echo '<tr>';
			echo '<td>' . esc_html( $row['product_name'] ?: '#' . (int) $row['product_id'] ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['impressions'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['clicks'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['add_to_cart'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['buy_now'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['sold_qty'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( $this->format_money( $row['revenue'] ?? 0 ) ) . '</td>';
			echo '</tr>';
		}

		echo '</tbody></table>';
	}

	/**
	 * Render top pages table.
	 *
	 * @param array $rows Page rows.
	 */
	private function render_top_pages_table( $rows ) {
		if ( empty( $rows ) ) {
			$this->render_empty_notice( __( 'No page interaction data for selected filters.', 'niwa-ai' ) );
			return;
		}

		echo '<table class="niwa-table"><thead><tr>';
		echo '<th>' . esc_html__( 'Page Path', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Frontend Events', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Card Clicks', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Conversion Actions', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Bubbles Shown', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Bubble Engagements', 'niwa-ai' ) . '</th>';
		echo '</tr></thead><tbody>';

		foreach ( $rows as $row ) {
			echo '<tr>';
			echo '<td>' . esc_html( $row['page_path'] ?: '/' ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['total_events'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['card_clicks'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['conversion_actions'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['bubble_shown'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( (string) (int) ( $row['bubble_engagements'] ?? 0 ) ) . '</td>';
			echo '</tr>';
		}

		echo '</tbody></table>';
	}

	/**
	 * Render influenced orders table.
	 *
	 * @param array $rows Order rows.
	 */
	private function render_influenced_orders_table( $rows ) {
		if ( empty( $rows ) ) {
			$this->render_empty_notice( __( 'No influenced orders for selected filters.', 'niwa-ai' ) );
			return;
		}

		echo '<table class="niwa-table"><thead><tr>';
		echo '<th>' . esc_html__( 'Order', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Sources', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Order Total', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Time', 'niwa-ai' ) . '</th>';
		echo '</tr></thead><tbody>';

		foreach ( $rows as $row ) {
			echo '<tr>';
			echo '<td>#' . esc_html( (string) (int) ( $row['order_id'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( implode( ', ', array_map( 'ucfirst', $row['sources'] ?? array() ) ) ) . '</td>';
			echo '<td>' . esc_html( $this->format_money( $row['order_total'] ?? 0 ) ) . '</td>';
			echo '<td>' . esc_html( $row['happened_at'] ?? '' ) . '</td>';
			echo '</tr>';
		}

		echo '</tbody></table>';
	}

	/**
	 * Render recent events table.
	 *
	 * @param array $rows Event rows.
	 */
	private function render_recent_events_table( $rows ) {
		if ( empty( $rows ) ) {
			$this->render_empty_notice( __( 'No frontend events for selected filters.', 'niwa-ai' ) );
			return;
		}

		echo '<table class="niwa-table"><thead><tr>';
		echo '<th>' . esc_html__( 'Time', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Event', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Source', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Product / Coupon', 'niwa-ai' ) . '</th>';
		echo '<th>' . esc_html__( 'Page Path', 'niwa-ai' ) . '</th>';
		echo '</tr></thead><tbody>';

		foreach ( $rows as $row ) {
			$label = ! empty( $row['product_name'] ) ? $row['product_name'] : ( $row['coupon_code'] ?? '' );
			echo '<tr>';
			echo '<td>' . esc_html( $row['created_at'] ?? '' ) . '</td>';
			echo '<td>' . esc_html( $row['event_type'] ?? '' ) . '</td>';
			echo '<td>' . esc_html( $row['source'] ?? '' ) . '</td>';
			echo '<td>' . esc_html( $label ) . '</td>';
			echo '<td>' . esc_html( $row['page_path'] ?: '/' ) . '</td>';
			echo '</tr>';
		}

		echo '</tbody></table>';
	}

	/**
	 * Render empty table notice.
	 *
	 * @param string $message Notice text.
	 */
	private function render_empty_notice( $message ) {
		echo '<div class="niwa-alert niwa-alert--info">';
		echo '<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24" fill="none" stroke="currentColor" stroke-width="2" stroke-linecap="round" stroke-linejoin="round" width="16" height="16"><circle cx="12" cy="12" r="10"/><line x1="12" y1="16" x2="12" y2="12"/><line x1="12" y1="8" x2="12.01" y2="8"/></svg>';
		echo esc_html( $message );
		echo '</div>';
	}

	/**
	 * Insert a row into the central analytics table.
	 *
	 * @param array $event Event payload.
	 * @return bool
	 */
	private function record_event( $event ) {
		global $wpdb;

		$table_name = $wpdb->prefix . self::EVENTS_TABLE;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		if ( $wpdb->get_var( "SHOW TABLES LIKE '{$table_name}'" ) !== $table_name ) {
			$this->create_events_table();
		}

		$meta_json = '';
		if ( is_array( $event['meta'] ?? null ) ) {
			$meta_json = wp_json_encode( $event['meta'] );
		} elseif ( is_string( $event['meta'] ?? '' ) ) {
			$meta_json = (string) $event['meta'];
		}

		$result = $wpdb->insert(
			$table_name,
			array(
				'event_type'   => sanitize_key( (string) ( $event['event_type'] ?? '' ) ),
				'source'       => sanitize_text_field( (string) ( $event['source'] ?? '' ) ),
				'page_url'     => esc_url_raw( (string) ( $event['page_url'] ?? '' ) ),
				'page_path'    => $this->extract_path( (string) ( $event['page_url'] ?? '' ) ),
				'user_session' => substr( sanitize_text_field( (string) ( $event['user_session'] ?? '' ) ), 0, Niwa_AI_Constants::SESSION_MAX_LENGTH ),
				'user_ip'      => sanitize_text_field( (string) ( $event['user_ip'] ?? '' ) ),
				'product_id'   => ! empty( $event['product_id'] ) ? absint( $event['product_id'] ) : null,
				'product_name' => sanitize_text_field( (string) ( $event['product_name'] ?? '' ) ),
				'coupon_code'  => sanitize_text_field( (string) ( $event['coupon_code'] ?? '' ) ),
				'order_id'     => ! empty( $event['order_id'] ) ? absint( $event['order_id'] ) : null,
				'quantity'     => isset( $event['quantity'] ) ? intval( $event['quantity'] ) : 0,
				'revenue'      => isset( $event['revenue'] ) ? $this->sanitize_money( $event['revenue'] ) : null,
				'order_total'  => isset( $event['order_total'] ) ? $this->sanitize_money( $event['order_total'] ) : null,
				'meta'         => $meta_json,
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%d', '%d', '%f', '%f', '%s' )
		);

		return false !== $result;
	}

	/**
	 * Build WHERE clause for event queries.
	 *
	 * @param array $filters Filters.
	 * @param array $params  Prepared params.
	 * @return string
	 */
	private function build_events_where_clause( $filters, &$params ) {
		return $this->build_time_page_where_clause( $filters, $params, 'created_at', 'page_path' );
	}

	/**
	 * Build WHERE clause for a timestamp column.
	 *
	 * @param array  $filters      Filters.
	 * @param array  $params       Prepared params.
	 * @param string $datetime_col Datetime column.
	 * @return string
	 */
	private function build_time_where_clause( $filters, &$params, $datetime_col ) {
		$params  = array();
		$clauses = array( '1=1' );
		$days    = max( 1, absint( $filters['days'] ?? 30 ) );

		$clauses[] = "{$datetime_col} >= %s";
		$params[]  = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );

		return implode( ' AND ', $clauses );
	}

	/**
	 * Build WHERE clause for time and page filters.
	 *
	 * @param array  $filters      Filters.
	 * @param array  $params       Prepared params.
	 * @param string $datetime_col Datetime column.
	 * @param string $page_col     Page path column.
	 * @return string
	 */
	private function build_time_page_where_clause( $filters, &$params, $datetime_col, $page_col ) {
		global $wpdb;

		$where_sql = $this->build_time_where_clause( $filters, $params, $datetime_col );
		if ( ! empty( $filters['page_contains'] ) ) {
			$where_sql .= " AND {$page_col} LIKE %s";
			$params[]   = '%' . $wpdb->esc_like( $filters['page_contains'] ) . '%';
		}

		return $where_sql;
	}

	/**
	 * Extract a URL path.
	 *
	 * @param string $page_url Full URL.
	 * @return string
	 */
	private function extract_path( $page_url ) {
		$path = (string) wp_parse_url( $page_url, PHP_URL_PATH );
		return '' !== $path ? $path : '/';
	}

	/**
	 * Sanitize monetary values.
	 *
	 * @param mixed $value Raw money value.
	 * @return float|null
	 */
	private function sanitize_money( $value ) {
		if ( null === $value || '' === $value ) {
			return null;
		}

		if ( function_exists( 'wc_format_decimal' ) ) {
			return (float) wc_format_decimal( $value, 2 );
		}

		$normalized = preg_replace( '/[^0-9,\.\-]/', '', (string) $value );
		$normalized = str_replace( ',', '.', (string) $normalized );

		return (float) $normalized;
	}

	/**
	 * Check whether the current request carries Niwa attribution data.
	 *
	 * @return bool
	 */
	private function has_niwa_request_attribution() {
		return isset( $_REQUEST['niwa_ai_recommended'] ) && '1' === sanitize_text_field( wp_unslash( $_REQUEST['niwa_ai_recommended'] ) );
	}

	/**
	 * Decode stored event meta JSON.
	 *
	 * @param mixed $meta Raw meta payload.
	 * @return array
	 */
	private function decode_event_meta( $meta ) {
		if ( is_array( $meta ) ) {
			return $meta;
		}

		if ( ! is_string( $meta ) || '' === $meta ) {
			return array();
		}

		$decoded = json_decode( $meta, true );
		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * Allowed central event types.
	 *
	 * @return array
	 */
	private function get_allowed_event_types() {
		return array(
			'chathead_open',
			'chat_message_sent',
			'leave_stoper_triggered',
			'leave_stoper_message_shown',
			'recommendation_impression',
			'product_card_click',
			'product_add_to_cart',
			'product_buy_now',
			'product_checkout_click',
			'coupon_copy',
			'recommended_product_order',
		);
	}

	/**
	 * Format percentages for cards.
	 *
	 * @param float $value Percent value.
	 * @return string
	 */
	private function format_percentage( $value ) {
		return number_format_i18n( (float) $value, 1 ) . '%';
	}

	/**
	 * Format money values.
	 *
	 * @param float $value Value.
	 * @return string
	 */
	public function format_money( $value ) {
		$value = (float) $value;

		if ( function_exists( 'wc_price' ) ) {
			return wp_strip_all_tags( html_entity_decode( wc_price( $value ), ENT_QUOTES, get_bloginfo( 'charset' ) ) );
		}

		return number_format_i18n( $value, 2 );
	}
}
