<?php

/**
 * Database layer.
 *
 * @package A360_Insights
 */

defined('ABSPATH') || exit;

/**
 * Database helper.
 */
class A360_Insights_DB
{
	/**
	 * AI source keys.
	 *
	 * @return array
	 */
	private static function ai_sources()
	{
		return array(
			'chatgpt',
			'openai',
			'codex',
			'gemini',
			'copilot',
			'claude',
			'perplexity',
			'meta_ai',
			'mistral',
			'you',
			'poe',
			'phind',
			'deepseek',
			'grok',
		);
	}

	/**
	 * Get visits table name.
	 *
	 * @return string
	 */
	public static function table_name()
	{
		global $wpdb;
		return $wpdb->prefix . 'a360_insights_events';
	}

	/**
	 * Get temporary full referrer debug table name.
	 *
	 * @return string
	 */
	public static function referrer_debug_table_name()
	{
		global $wpdb;
		return $wpdb->prefix . 'a360_insights_referrer_debug';
	}

	/**
	 * Get engagement events table name.
	 *
	 * @return string
	 */
	public static function engagement_table_name()
	{
		global $wpdb;
		return $wpdb->prefix . 'a360_insights_engagement_events';
	}

	/**
	 * Get shareable reports table name.
	 *
	 * @return string
	 */
	public static function shared_reports_table_name()
	{
		global $wpdb;
		return $wpdb->prefix . 'a360_insights_shared_reports';
	}

	/**
	 * Get sessions table name.
	 *
	 * @return string
	 */
	public static function sessions_table_name()
	{
		global $wpdb;
		return $wpdb->prefix . 'a360_insights_sessions';
	}

	/**
	 * Get supported anonymous engagement event types.
	 *
	 * @return array
	 */
	public static function allowed_engagement_events()
	{
		return array(
			'scroll_25',
			'scroll_50',
			'scroll_75',
			'scroll_90',
			'outbound_click',
			'file_download',
			'phone_click',
			'email_click',
			'form_start',
			'form_submit',
		);
	}

	/**
	 * Create or update DB table.
	 *
	 * @return void
	 */
	public static function activate()
	{
		self::create_or_update_table();
		add_option(A360_Insights_Settings::OPTION_NAME, A360_Insights_Settings::defaults(), '', false);
		update_option('a360_insights_show_onboarding', '1', false);
		update_option('a360_insights_db_version', A360_INSIGHTS_DB_VERSION, false);
	}

	/**
	 * Maybe run DB upgrade.
	 *
	 * @return void
	 */
	public static function maybe_upgrade()
	{
		$current = get_option('a360_insights_db_version');

		if (A360_INSIGHTS_DB_VERSION !== (string) $current) {
			self::create_or_update_table();
			update_option('a360_insights_db_version', A360_INSIGHTS_DB_VERSION, false);
		}
	}
	// private static function ai_like_condition()
	// {
	// 	global $wpdb;

	// 	$conditions = array();
	// 	$values     = array();

	// 	foreach (self::ai_sources() as $source) {
	// 		$like = '%' . $wpdb->esc_like($source) . '%';

	// 		$conditions[] = 'source LIKE %s';
	// 		$values[]     = $like;

	// 		$conditions[] = 'referrer_host LIKE %s';
	// 		$values[]     = $like;

	// 		$conditions[] = 'referrer_url LIKE %s';
	// 		$values[]     = $like;
	// 	}

	// 	return array(
	// 		'sql'    => '(' . implode(' OR ', $conditions) . ')',
	// 		'values' => $values,
	// 	);
	// }
	/**
	 * Create/update table and repair missing columns from older versions.
	 *
	 * @return void
	 */
	public static function create_or_update_table()
	{
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

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

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL,
			viewed_at datetime NOT NULL,
			source varchar(100) NOT NULL DEFAULT 'direct',
			referrer_host varchar(191) NOT NULL DEFAULT '',
			referrer_url text NULL,
			utm_source varchar(100) NOT NULL DEFAULT '',
			utm_medium varchar(100) NOT NULL DEFAULT '',
			utm_campaign varchar(191) NOT NULL DEFAULT '',
			session_hash char(64) NOT NULL DEFAULT '',
			PRIMARY KEY  (id),
			KEY post_id (post_id),
			KEY viewed_at (viewed_at),
			KEY post_id_viewed_at (post_id, viewed_at),
			KEY source (source),
			KEY referrer_host (referrer_host),
			KEY session_hash (session_hash)
		) {$charset_collate};";

		dbDelta($sql);
		self::create_or_update_referrer_debug_table();
		self::create_or_update_engagement_table();
		self::create_or_update_shared_reports_table();
		self::create_or_update_sessions_table();
		self::repair_schema();
		self::migrate_from_legacy_views_table();
	}

	/**
	 * Create/update temporary full referrer diagnostics table.
	 *
	 * @return void
	 */
	private static function create_or_update_referrer_debug_table()
	{
		global $wpdb;

		$table_name      = self::referrer_debug_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			viewed_at datetime NOT NULL,
			post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			source varchar(100) NOT NULL DEFAULT '',
			referrer_host varchar(191) NOT NULL DEFAULT '',
			full_referrer text NULL,
			request_path text NULL,
			PRIMARY KEY  (id),
			KEY viewed_at (viewed_at),
			KEY source (source),
			KEY referrer_host (referrer_host)
		) {$charset_collate};";

		dbDelta($sql);
	}

	/**
	 * Create/update shareable reports table.
	 *
	 * @return void
	 */
	private static function create_or_update_shared_reports_table()
	{
		global $wpdb;

		$table_name      = self::shared_reports_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			token_hash char(64) NOT NULL DEFAULT '',
			label varchar(191) NOT NULL DEFAULT '',
			created_at datetime NOT NULL,
			expires_at datetime NULL,
			last_viewed_at datetime NULL,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			PRIMARY KEY  (id),
			UNIQUE KEY token_hash (token_hash),
			KEY post_id (post_id),
			KEY is_active (is_active)
		) {$charset_collate};";

		dbDelta($sql);
	}

	/**
	 * Create/update engagement events table.
	 *
	 * @return void
	 */
	private static function create_or_update_engagement_table()
	{
		global $wpdb;

		$table_name      = self::engagement_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_at datetime NOT NULL,
			post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			event_type varchar(50) NOT NULL DEFAULT '',
			event_value smallint(5) unsigned NOT NULL DEFAULT 0,
			event_label text NULL,
			source varchar(100) NOT NULL DEFAULT 'direct',
			referrer_host varchar(191) NOT NULL DEFAULT '',
			referrer_url text NULL,
			request_path text NULL,
			session_hash char(64) NOT NULL DEFAULT '',
			PRIMARY KEY  (id),
			KEY post_id (post_id),
			KEY event_at (event_at),
			KEY event_type (event_type),
			KEY event_value (event_value),
			KEY source (source),
			KEY session_hash (session_hash)
		) {$charset_collate};";

		dbDelta($sql);
	}

	/**
	 * Create/update anonymous sessions table.
	 *
	 * @return void
	 */
	private static function create_or_update_sessions_table()
	{
		global $wpdb;

		$table_name      = self::sessions_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			session_hash char(64) NOT NULL DEFAULT '',
			started_at datetime NOT NULL,
			last_seen_at datetime NOT NULL,
			landing_post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			landing_path text NULL,
			source varchar(100) NOT NULL DEFAULT 'direct',
			referrer_host varchar(191) NOT NULL DEFAULT '',
			referrer_url text NULL,
			utm_source varchar(100) NOT NULL DEFAULT '',
			utm_medium varchar(100) NOT NULL DEFAULT '',
			utm_campaign varchar(191) NOT NULL DEFAULT '',
			page_views int unsigned NOT NULL DEFAULT 0,
			engagement_events int unsigned NOT NULL DEFAULT 0,
			is_engaged tinyint(1) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY session_hash (session_hash),
			KEY started_at (started_at),
			KEY last_seen_at (last_seen_at),
			KEY landing_post_id (landing_post_id),
			KEY source (source),
			KEY is_engaged (is_engaged)
		) {$charset_collate};";

		dbDelta($sql);
	}

	/**
	 * Ensure columns exist when upgrading from older single-file versions.
	 *
	 * @return void
	 */
	private static function repair_schema()
	{
		global $wpdb;

		$table_name = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Schema inspection for plugin table repair.
		$columns = $wpdb->get_col("DESC {$table_name}", 0);

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

		$wanted = array(
			'source'        => "ALTER TABLE {$table_name} ADD source varchar(100) NOT NULL DEFAULT 'direct'",
			'referrer_host' => "ALTER TABLE {$table_name} ADD referrer_host varchar(191) NOT NULL DEFAULT ''",
			'referrer_url'  => "ALTER TABLE {$table_name} ADD referrer_url text NULL",
			'utm_source'    => "ALTER TABLE {$table_name} ADD utm_source varchar(100) NOT NULL DEFAULT ''",
			'utm_medium'    => "ALTER TABLE {$table_name} ADD utm_medium varchar(100) NOT NULL DEFAULT ''",
			'utm_campaign'  => "ALTER TABLE {$table_name} ADD utm_campaign varchar(191) NOT NULL DEFAULT ''",
			'session_hash'  => "ALTER TABLE {$table_name} ADD session_hash char(64) NOT NULL DEFAULT ''",
		);

		foreach ($wanted as $column => $sql) {
			if (! in_array($column, $columns, true)) {
				$wpdb->query($sql); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema repair query built from trusted internal column map.
			}
		}

		if (in_array('source_type', $columns, true)) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Legacy schema migration for plugin table.
			$wpdb->query("UPDATE {$table_name} SET source = source_type WHERE source = '' OR source = 'direct'");
		}

		if (in_array('referer_domain', $columns, true)) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Legacy schema migration for plugin table.
			$wpdb->query("UPDATE {$table_name} SET referrer_host = referer_domain WHERE referrer_host = ''");
		}

		if (in_array('referer_url', $columns, true)) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Legacy schema migration for plugin table.
			$wpdb->query("UPDATE {$table_name} SET referrer_url = referer_url WHERE referrer_url IS NULL OR referrer_url = ''");
		}

		$engagement_table = self::engagement_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Schema inspection for plugin engagement table repair.
		$engagement_columns = $wpdb->get_col("DESC {$engagement_table}", 0);

		if (! empty($engagement_columns) && ! in_array('session_hash', $engagement_columns, true)) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Schema repair query for plugin engagement table.
			$wpdb->query("ALTER TABLE {$engagement_table} ADD session_hash char(64) NOT NULL DEFAULT ''");
		}
	}

	/**
	 * Migrate rows accidentally written to the 0.4.0/0.4.1 table name.
	 *
	 * @return void
	 */
	private static function migrate_from_legacy_views_table()
	{
		global $wpdb;

		$target = self::table_name();
		$legacy = $wpdb->prefix . 'a360_insights_views';

		if ($legacy === $target) {
			return;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Checking optional legacy plugin table.
		$exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $legacy));

		if ($exists !== $legacy) {
			return;
		}

		$sql = 'INSERT INTO ' . esc_sql($target) . '
			(post_id, viewed_at, source, referrer_host, referrer_url, utm_source, utm_medium, utm_campaign)
			SELECT v.post_id, v.viewed_at, v.source, v.referrer_host, v.referrer_url, v.utm_source, v.utm_medium, v.utm_campaign
			FROM ' . esc_sql($legacy) . ' v
			WHERE NOT EXISTS (
				SELECT 1 FROM ' . esc_sql($target) . ' e
				WHERE e.post_id = v.post_id
				AND e.viewed_at = v.viewed_at
				AND e.source = v.source
				AND e.referrer_host = v.referrer_host
				LIMIT 1
			)';

		$wpdb->query($sql); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Internal SQL for legacy data migration between plugin tables.
	}

	/**
	 * Insert view event.
	 *
	 * @param int   $post_id Post ID.
	 * @param array $data Visit data.
	 * @return void
	 */
	public static function insert_view($post_id, array $data)
	{
		global $wpdb;

		$post_id    = absint($post_id);
		$table_name = self::table_name();

		$row = array(
			'post_id'       => $post_id,
			'viewed_at'     => current_time('mysql'),
			'source'        => sanitize_key($data['source'] ?? 'direct'),
			'referrer_host' => sanitize_text_field($data['referrer_host'] ?? ''),
			'referrer_url'  => esc_url_raw($data['referrer_url'] ?? ''),
			'utm_source'    => sanitize_text_field($data['utm_source'] ?? ''),
			'utm_medium'    => sanitize_text_field($data['utm_medium'] ?? ''),
			'utm_campaign'  => sanitize_text_field($data['utm_campaign'] ?? ''),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Inserts tracking event into custom plugin table.
		$inserted = $wpdb->insert(
			$table_name,
			$row,
			array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
		);

		if (false === $inserted) {
			self::create_or_update_table();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Retries insert after schema repair.
			$inserted = $wpdb->insert(
				$table_name,
				$row,
				array('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
			);
		}

		if (false !== $inserted) {
			$total = (int) get_post_meta($post_id, '_a360_insights_total_views', true);
			update_post_meta($post_id, '_a360_insights_total_views', $total + 1);
		}

		self::clear_cache_for_post($post_id);
		wp_cache_delete('dashboard_summary', 'a360_insights');
	}

	/**
	 * Update a recent direct view with referrer detected by browser-side JavaScript.
	 *
	 * @param int   $post_id Post ID.
	 * @param array $data    Referrer data.
	 * @return int|false Number of updated rows or false on invalid input.
	 */
	public static function update_recent_direct_view($post_id, array $data)
	{
		global $wpdb;

		$post_id    = absint($post_id);
		$table_name = self::table_name();

		if (! $post_id) {
			return false;
		}

		$source        = sanitize_key($data['source'] ?? '');
		$referrer_host = sanitize_text_field($data['referrer_host'] ?? '');
		$referrer_url  = esc_url_raw($data['referrer_url'] ?? '');

		if (! $source || ! $referrer_host || ! $referrer_url || 'direct' === $source) {
			return false;
		}

		$threshold = date('Y-m-d H:i:s', current_time('timestamp') - 300);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Repairs only the latest direct view when browser-side referrer is available.
		$updated = $wpdb->query(
			$wpdb->prepare(
				'UPDATE ' . esc_sql($table_name) . '
				SET source = %s,
					referrer_host = %s,
					referrer_url = %s
				WHERE post_id = %d
				AND source = %s
				AND viewed_at >= %s
				ORDER BY id DESC
				LIMIT 1',
				$source,
				$referrer_host,
				$referrer_url,
				$post_id,
				'direct',
				$threshold
			)
		);

		if ($updated) {
			self::clear_cache_for_post($post_id);
			wp_cache_delete('dashboard_summary', 'a360_insights');
		}

		return $updated;
	}

	/**
	 * Insert a browser engagement event such as scroll depth.
	 *
	 * @param int   $post_id Post ID.
	 * @param array $data    Event data.
	 * @return int|false Insert ID, 0 or false.
	 */
	public static function insert_engagement_event($post_id, array $data)
	{
		global $wpdb;

		$post_id    = absint($post_id);
		$table_name = self::engagement_table_name();

		if (! $post_id) {
			return false;
		}

		$event_type  = sanitize_key($data['event_type'] ?? '');
		$event_value = absint($data['event_value'] ?? 0);

		if (! $event_type || ! in_array($event_type, self::allowed_engagement_events(), true)) {
			return false;
		}

		if (0 !== $event_value && ! in_array($event_value, array(25, 50, 75, 90), true)) {
			return false;
		}

		$row = array(
			'event_at'      => current_time('mysql'),
			'post_id'       => $post_id,
			'event_type'    => $event_type,
			'event_value'   => $event_value,
			'event_label'   => sanitize_text_field($data['event_label'] ?? ''),
			'source'        => sanitize_key($data['source'] ?? 'direct'),
			'referrer_host' => sanitize_text_field($data['referrer_host'] ?? ''),
			'referrer_url'  => esc_url_raw($data['referrer_url'] ?? ''),
			'request_path'  => sanitize_text_field($data['request_path'] ?? ''),
			'session_hash'  => self::hash_session_id($data['session_id'] ?? ''),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Inserts anonymous engagement event into custom plugin table.
		$inserted = $wpdb->insert(
			$table_name,
			$row,
			array('%s', '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s')
		);

		if (false === $inserted) {
			self::create_or_update_table();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Retries insert after schema repair.
			$inserted = $wpdb->insert(
				$table_name,
				$row,
				array('%s', '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s')
			);
		}

		if (false !== $inserted) {
			if (! empty($row['session_hash'])) {
				self::mark_session_engaged($row['session_hash']);
			}

			self::clear_cache_for_post($post_id);
			wp_cache_delete('dashboard_summary', 'a360_insights');
			return (int) $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Create or update an anonymous browser session.
	 *
	 * @param int    $post_id    Current post ID.
	 * @param string $session_id Browser session ID.
	 * @param array  $data       Session data.
	 * @return string|false Session hash or false.
	 */
	public static function upsert_session($post_id, $session_id, array $data)
	{
		global $wpdb;

		$post_id      = absint($post_id);
		$session_hash = self::hash_session_id($session_id);

		if (! $post_id || '' === $session_hash) {
			return false;
		}

		$table_name = self::sessions_table_name();
		$now        = current_time('mysql');

		$row = array(
			'session_hash'    => $session_hash,
			'started_at'      => $now,
			'last_seen_at'    => $now,
			'landing_post_id' => $post_id,
			'landing_path'    => sanitize_text_field($data['landing_path'] ?? ''),
			'source'          => sanitize_key($data['source'] ?? 'direct'),
			'referrer_host'   => sanitize_text_field($data['referrer_host'] ?? ''),
			'referrer_url'    => esc_url_raw($data['referrer_url'] ?? ''),
			'utm_source'      => sanitize_text_field($data['utm_source'] ?? ''),
			'utm_medium'      => sanitize_text_field($data['utm_medium'] ?? ''),
			'utm_campaign'    => sanitize_text_field($data['utm_campaign'] ?? ''),
			'page_views'      => 1,
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Checks anonymous session existence by hash.
		$existing_id = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT id FROM ' . esc_sql($table_name) . ' WHERE session_hash = %s LIMIT 1',
				$session_hash
			)
		);

		if ($existing_id > 0) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates anonymous session activity counters.
			$updated = $wpdb->query(
				$wpdb->prepare(
					'UPDATE ' . esc_sql($table_name) . '
					SET last_seen_at = %s,
						page_views = page_views + 1,
						is_engaged = IF(page_views + 1 >= 2, 1, is_engaged)
					WHERE id = %d',
					$now,
					$existing_id
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Inserts anonymous browser session.
			$updated = $wpdb->insert(
				$table_name,
				$row,
				array('%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d')
			);

			if (false === $updated) {
				self::create_or_update_table();

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Retries anonymous browser session insert after schema repair.
				$updated = $wpdb->insert(
					$table_name,
					$row,
					array('%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d')
				);
			}
		}

		self::attach_recent_view_to_session($post_id, $session_hash);

		if (false === $updated) {
			return false;
		}

		wp_cache_delete('dashboard_summary', 'a360_insights');
		return $session_hash;
	}

	/**
	 * Attach the latest page view to a browser session hash.
	 *
	 * @param int    $post_id      Post ID.
	 * @param string $session_hash Session hash.
	 * @return int|false
	 */
	private static function attach_recent_view_to_session($post_id, $session_hash)
	{
		global $wpdb;

		$post_id      = absint($post_id);
		$session_hash = preg_match('/^[a-f0-9]{64}$/', (string) $session_hash) ? (string) $session_hash : '';

		if (! $post_id || '' === $session_hash) {
			return false;
		}

		$table_name = self::table_name();
		$threshold  = date('Y-m-d H:i:s', current_time('timestamp') - 300);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Attaches only latest anonymous page view to browser session hash.
		return $wpdb->query(
			$wpdb->prepare(
				'UPDATE ' . esc_sql($table_name) . '
				SET session_hash = %s
				WHERE post_id = %d
				AND viewed_at >= %s
				AND session_hash = ""
				ORDER BY id DESC
				LIMIT 1',
				$session_hash,
				$post_id,
				$threshold
			)
		);
	}

	/**
	 * Mark a session as engaged after an interaction event.
	 *
	 * @param string $session_hash Session hash.
	 * @return int|false
	 */
	private static function mark_session_engaged($session_hash)
	{
		global $wpdb;

		$session_hash = preg_match('/^[a-f0-9]{64}$/', (string) $session_hash) ? (string) $session_hash : '';

		if ('' === $session_hash) {
			return false;
		}

		$table_name = self::sessions_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates anonymous session engagement counters.
		return $wpdb->query(
			$wpdb->prepare(
				'UPDATE ' . esc_sql($table_name) . '
				SET last_seen_at = %s,
					engagement_events = engagement_events + 1,
					is_engaged = 1
				WHERE session_hash = %s',
				current_time('mysql'),
				$session_hash
			)
		);
	}

	/**
	 * Hash an anonymous browser session ID.
	 *
	 * @param string $session_id Plain browser session ID.
	 * @return string
	 */
	private static function hash_session_id($session_id)
	{
		$session_id = trim((string) $session_id);

		if ('' === $session_id || strlen($session_id) > 128 || ! preg_match('/^[A-Za-z0-9._-]+$/', $session_id)) {
			return '';
		}

		return hash_hmac('sha256', $session_id, wp_salt('auth'));
	}

	/**
	 * Create a shareable report token for a post.
	 *
	 * @param int    $post_id Post ID.
	 * @param string $label   Optional label.
	 * @return array|false Report row data with plain token or false.
	 */
	public static function create_shared_report($post_id, $label = '', $expires_at = '')
	{
		global $wpdb;

		$post_id = absint($post_id);

		if (! $post_id) {
			return false;
		}

		$table_name = self::shared_reports_table_name();
		$token      = wp_generate_password(32, false, false);
		$hash       = self::hash_report_token($token);
		$label      = sanitize_text_field($label);
		$expires_at = self::sanitize_report_expires_at($expires_at);

		$row = array(
			'post_id'    => $post_id,
			'token_hash' => $hash,
			'label'      => $label,
			'created_at' => current_time('mysql'),
			'expires_at' => $expires_at,
			'is_active'  => 1,
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Creates explicitly requested shareable report token.
		$inserted = $wpdb->insert(
			$table_name,
			$row,
			array('%d', '%s', '%s', '%s', '%s', '%d')
		);

		if (false === $inserted) {
			self::create_or_update_table();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Retries after schema repair.
			$inserted = $wpdb->insert(
				$table_name,
				$row,
				array('%d', '%s', '%s', '%s', '%s', '%d')
			);
		}

		if (false === $inserted) {
			return false;
		}

		return array(
			'id'      => (int) $wpdb->insert_id,
			'post_id' => $post_id,
			'token'   => $token,
			'url'     => add_query_arg('a360_report', rawurlencode($token), home_url('/')),
		);
	}

	/**
	 * Get shareable reports for a post.
	 *
	 * @param int $post_id Post ID.
	 * @return array
	 */
	public static function get_shared_reports_for_post($post_id)
	{
		global $wpdb;

		$post_id = absint($post_id);

		if (! $post_id) {
			return array();
		}

		$table_name = self::shared_reports_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads explicitly created report links for admin UI.
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, post_id, label, created_at, expires_at, last_viewed_at, is_active
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				ORDER BY created_at DESC
				LIMIT 20',
				$post_id
			),
			ARRAY_A
		);
	}

	/**
	 * Get recent shareable reports across all posts.
	 *
	 * @param int $limit Result limit.
	 * @return array
	 */
	public static function get_shared_reports($limit = 50)
	{
		global $wpdb;

		$limit = max(1, min(200, absint($limit)));

		$table_name = self::shared_reports_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads explicitly created report links for admin UI.
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, post_id, label, created_at, expires_at, last_viewed_at, is_active
				FROM ' . esc_sql($table_name) . '
				ORDER BY created_at DESC
				LIMIT %d',
				$limit
			),
			ARRAY_A
		);
	}

	/**
	 * Disable a shareable report link.
	 *
	 * @param int $report_id Report ID.
	 * @param int $post_id   Post ID.
	 * @return int|false
	 */
	public static function revoke_shared_report($report_id, $post_id)
	{
		global $wpdb;

		$report_id = absint($report_id);
		$post_id   = absint($post_id);

		if (! $report_id || ! $post_id) {
			return false;
		}

		$table_name = self::shared_reports_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Revokes explicitly selected report link.
		return $wpdb->update(
			$table_name,
			array('is_active' => 0),
			array(
				'id'      => $report_id,
				'post_id' => $post_id,
			),
			array('%d'),
			array('%d', '%d')
		);
	}

	/**
	 * Get active shared report by plain token.
	 *
	 * @param string $token Plain token from URL.
	 * @return array|null Report row.
	 */
	public static function get_shared_report_by_token($token)
	{
		global $wpdb;

		$token = trim((string) $token);

		if ('' === $token) {
			return null;
		}

		$table_name = self::shared_reports_table_name();
		$hash       = self::hash_report_token($token);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads public report token row.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM ' . esc_sql($table_name) . '
				WHERE token_hash = %s
				AND is_active = 1
				AND (expires_at IS NULL OR expires_at = "0000-00-00 00:00:00" OR expires_at > %s)
				LIMIT 1',
				$hash,
				current_time('mysql')
			),
			ARRAY_A
		);

		if (! $row) {
			return null;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates last viewed timestamp for report token.
		$wpdb->update(
			$table_name,
			array('last_viewed_at' => current_time('mysql')),
			array('id' => absint($row['id'])),
			array('%s'),
			array('%d')
		);

		return $row;
	}

	/**
	 * Hash a public report token.
	 *
	 * @param string $token Plain token.
	 * @return string
	 */
	private static function hash_report_token($token)
	{
		return hash_hmac('sha256', (string) $token, wp_salt('auth'));
	}

	/**
	 * Sanitize optional report expiration date.
	 *
	 * @param string $expires_at Date in YYYY-MM-DD format or empty.
	 * @return string|null
	 */
	private static function sanitize_report_expires_at($expires_at)
	{
		$expires_at = trim((string) $expires_at);

		if ('' === $expires_at) {
			return null;
		}

		if (! preg_match('/^\d{4}-\d{2}-\d{2}$/', $expires_at)) {
			return null;
		}

		$date = DateTimeImmutable::createFromFormat('Y-m-d H:i:s', $expires_at . ' 23:59:59', wp_timezone());

		if (! $date) {
			return null;
		}

		return $date->format('Y-m-d H:i:s');
	}

	/**
	 * Store a temporary full referrer debug row.
	 *
	 * @param int   $post_id Post ID.
	 * @param array $data Visit data.
	 * @return void
	 */
	public static function insert_referrer_debug($post_id, array $data)
	{
		global $wpdb;

		$table_name = self::referrer_debug_table_name();
		$row        = array(
			'viewed_at'     => current_time('mysql'),
			'post_id'       => absint($post_id),
			'source'        => isset($data['source']) ? (string) $data['source'] : '',
			'referrer_host' => isset($data['referrer_host']) ? (string) $data['referrer_host'] : '',
			'full_referrer' => isset($data['full_referrer']) ? (string) $data['full_referrer'] : '',
			'request_path'  => isset($data['request_path']) ? (string) $data['request_path'] : '',
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Temporary diagnostics table for full referrer inspection.
		$inserted = $wpdb->insert(
			$table_name,
			$row,
			array('%s', '%d', '%s', '%s', '%s', '%s')
		);

		if (false === $inserted) {
			self::create_or_update_referrer_debug_table();

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Retries after diagnostics table repair.
			$wpdb->insert(
				$table_name,
				$row,
				array('%s', '%d', '%s', '%s', '%s', '%s')
			);
		}

		self::trim_referrer_debug_rows(500);
	}

	/**
	 * Keep only the newest full referrer debug rows.
	 *
	 * @param int $limit Maximum rows to keep.
	 * @return void
	 */
	private static function trim_referrer_debug_rows($limit = 500)
	{
		global $wpdb;

		$table_name = self::referrer_debug_table_name();
		$limit      = max(1, absint($limit));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Temporary diagnostics cleanup.
		$wpdb->query(
			$wpdb->prepare(
				'DELETE FROM ' . esc_sql($table_name) . '
				WHERE id NOT IN (
					SELECT id FROM (
						SELECT id FROM ' . esc_sql($table_name) . '
						ORDER BY id DESC
						LIMIT %d
					) keep_rows
				)',
				$limit
			)
		);
	}


	/**
	 * Delete events older than selected retention period.
	 *
	 * @return int|false Number of deleted rows or false.
	 */
	public static function delete_old_events()
	{
		global $wpdb;

		$retention_days = absint(A360_Insights_Settings::get_value('retention_days', 0));

		if (! $retention_days) {
			return 0;
		}

		$table_name       = self::table_name();
		$engagement_table = self::engagement_table_name();
		$sessions_table   = self::sessions_table_name();
		$threshold        = gmdate('Y-m-d H:i:s', time() - ($retention_days * DAY_IN_SECONDS));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes old plugin events based on explicit retention setting.
		$deleted = $wpdb->query(
			$wpdb->prepare(
				'DELETE FROM ' . esc_sql($table_name) . ' WHERE viewed_at < %s',
				$threshold
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes old anonymous engagement events based on explicit retention setting.
		$engagement_deleted = $wpdb->query(
			$wpdb->prepare(
				'DELETE FROM ' . esc_sql($engagement_table) . ' WHERE event_at < %s',
				$threshold
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes old anonymous sessions based on explicit retention setting.
		$sessions_deleted = $wpdb->query(
			$wpdb->prepare(
				'DELETE FROM ' . esc_sql($sessions_table) . ' WHERE last_seen_at < %s',
				$threshold
			)
		);

		return (int) $deleted + (int) $engagement_deleted + (int) $sessions_deleted;
	}
	/**
	 * Get traffic trend data for dashboard chart.
	 *
	 * Day  = hourly points.
	 * Week = daily points.
	 * Month = daily points.
	 * Year = monthly points.
	 *
	 * @param string $range  Range key.
	 * @param int    $offset Period offset.
	 * @return array
	 */
	public static function get_traffic_trend($range = 'month', $offset = 0, $source_filter = '', $referrer_filter = '')
	{
		global $wpdb;
		$table_name = self::table_name();
		$bounds     = self::date_bounds($range, '', $offset);
		$range      = sanitize_key($range);
		$source_filter   = sanitize_key($source_filter);
		$referrer_filter = sanitize_text_field($referrer_filter);
		$cache_key = 'traffic_trend_' . md5($range . '_' . (int) $offset . '_' . $source_filter . '_' . $referrer_filter);

		if ('day' === $range) {
			$rows      = wp_cache_get($cache_key, 'a360_insights');

			if (false === $rows) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				$rows = $wpdb->get_results(
					$wpdb->prepare(
						'
			SELECT HOUR(viewed_at) AS period, COUNT(*) AS views
			FROM `' . esc_sql($table_name) . '`
			WHERE viewed_at >= %s
			AND viewed_at < %s
			AND ( %s = "" OR source = %s )
			AND ( %s = "" OR referrer_host = %s )
			GROUP BY period
			ORDER BY period ASC
			',
						$bounds['start'],
						$bounds['end'],
						$source_filter,
						$source_filter,
						$referrer_filter,
						$referrer_filter
					),
					ARRAY_A
				);

				wp_cache_set($cache_key, $rows, 'a360_insights', MINUTE_IN_SECONDS);
			}

			$indexed = array();

			foreach ($rows as $row) {
				$indexed[(int) $row['period']] = (int) $row['views'];
			}

			$data = array();

			for ($hour = 0; $hour < 24; $hour++) {
				$data[] = array(
					'label' => sprintf('%02d:00', $hour),
					'views' => isset($indexed[$hour]) ? (int) $indexed[$hour] : 0,
				);
			}

			return $data;
		}

		if ('year' === $range) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'
				SELECT DATE_FORMAT(viewed_at, "%%Y-%%m") AS period, COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE viewed_at >= %s
				AND viewed_at < %s
				AND ( %s = "" OR source = %s )
				AND ( %s = "" OR referrer_host = %s )
				GROUP BY period
				ORDER BY period ASC
				',
					$bounds['start'],
					$bounds['end'],
					$source_filter,
					$source_filter,
					$referrer_filter,
					$referrer_filter
				),
				ARRAY_A
			);

			$indexed = array();

			foreach ($rows as $row) {
				$indexed[$row['period']] = (int) $row['views'];
			}

			$timezone = wp_timezone();
			$start    = new DateTimeImmutable($bounds['start'], $timezone);
			$data     = array();

			for ($i = 0; $i < 12; $i++) {
				$date = $start->modify('+' . $i . ' months');
				$key  = $date->format('Y-m');

				$data[] = array(
					'label' => $date->format('M Y'),
					'views' => isset($indexed[$key]) ? (int) $indexed[$key] : 0,
				);
			}

			return $data;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'
			SELECT DATE(viewed_at) AS period, COUNT(*) AS views
			FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s
			AND ( %s = "" OR source = %s )
			AND ( %s = "" OR referrer_host = %s )
			GROUP BY period
			ORDER BY period ASC
			',
				$bounds['start'],
				$bounds['end'],
				$source_filter,
				$source_filter,
				$referrer_filter,
				$referrer_filter
			),
			ARRAY_A
		);

		$indexed = array();

		foreach ($rows as $row) {
			$indexed[$row['period']] = (int) $row['views'];
		}

		$timezone = wp_timezone();
		$start    = new DateTimeImmutable($bounds['start'], $timezone);
		$end      = new DateTimeImmutable($bounds['end'], $timezone);
		$data     = array();

		for ($date = $start; $date < $end; $date = $date->modify('+1 day')) {
			$key = $date->format('Y-m-d');

			$data[] = array(
				'label' => $key,
				'views' => isset($indexed[$key]) ? (int) $indexed[$key] : 0,
			);
		}

		return $data;
	}

	/**
	 * Cache key.
	 *
	 * @param int $post_id Post ID.
	 * @return string
	 */
	public static function cache_key($post_id)
	{
		return 'post_' . absint($post_id) . '_stats';
	}

	/**
	 * Get total views.
	 *
	 * @param int $post_id Post ID.
	 * @return int
	 */
	public static function get_total_views($post_id)
	{
		global $wpdb;

		$post_id   = absint($post_id);
		$cache_key = 'total_' . $post_id;
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return absint($cached);
		}

		$table_name = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads aggregate count from custom plugin table; cached below.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . ' WHERE post_id = %d',
				$post_id
			)
		);

		$meta_total = (int) get_post_meta($post_id, '_a360_insights_total_views', true);
		$total      = max($total, $meta_total);

		wp_cache_set($cache_key, $total, 'a360_insights', 5 * MINUTE_IN_SECONDS);

		return $total;
	}

	/**
	 * Get top AI assistant sources.
	 *
	 * @param int $limit Number of rows.
	 * @return array
	 */
	public static function get_top_ai_sources($limit = 5, $range = 'month', $offset = 0)
	{
		global $wpdb;

		$table_name = esc_sql(self::table_name());
		$limit      = absint($limit);
		$bounds     = self::date_bounds($range, '', $offset);

		if ($limit < 1) {
			$limit = 5;
		}

		$cache_key = 'ai_sources_' . md5(
			$bounds['start'] . '_' . $bounds['end'] . '_' . $limit
		);

		$cached = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}

		$ai_sources = self::ai_sources();

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is escaped with esc_sql() and source placeholders are generated from a fixed internal list.
		$prepared = $wpdb->prepare(
			'
	SELECT
		COALESCE(NULLIF(source, ""), NULLIF(referrer_host, ""), "unknown") AS source,
		COUNT(*) AS views
	FROM ' . esc_sql($table_name) . '
	WHERE viewed_at >= %s
	AND viewed_at < %s
	AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )
	GROUP BY source
	ORDER BY views DESC
	LIMIT %d
	',
			$bounds['start'],
			$bounds['end'],
			$ai_sources[0],
			$ai_sources[1],
			$ai_sources[2],
			$ai_sources[3],
			$ai_sources[4],
			$ai_sources[5],
			$ai_sources[6],
			$ai_sources[7],
			$ai_sources[8],
			$ai_sources[9],
			$ai_sources[10],
			$ai_sources[11],
			$ai_sources[12],
			$ai_sources[13],
			$limit
		);

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

		wp_cache_set($cache_key, $rows, 'a360_insights', MINUTE_IN_SECONDS);

		return $rows;
	}

	/**
	 * Get AI sources overview for dashboard.
	 *
	 * @param string $range  Range key.
	 * @param int    $offset Period offset.
	 * @return array
	 */
	public static function get_ai_sources_overview($range = 'month', $offset = 0)
	{
		global $wpdb;

		$table_name = esc_sql(self::table_name());
		$bounds     = self::date_bounds($range, '', $offset);
		$ai_sources = self::ai_sources();
		$cache_key  = 'ai_sources_overview_' . md5($bounds['start'] . '_' . $bounds['end']);

		$cached = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Table name is escaped and source placeholders are generated from a fixed internal list.
		$prepared = $wpdb->prepare(
			'
			SELECT source, COUNT(*) AS views
			FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s
			AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )
			GROUP BY source
			',
			$bounds['start'],
			$bounds['end'],
			$ai_sources[0],
			$ai_sources[1],
			$ai_sources[2],
			$ai_sources[3],
			$ai_sources[4],
			$ai_sources[5],
			$ai_sources[6],
			$ai_sources[7],
			$ai_sources[8],
			$ai_sources[9],
			$ai_sources[10],
			$ai_sources[11],
			$ai_sources[12],
			$ai_sources[13]
		);

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

		$views_by_source = array();
		$total_views     = 0;
		$active_sources  = 0;
		$top_source      = array(
			'source' => '',
			'views'  => 0,
		);

		foreach ($rows as $row) {
			$source = sanitize_key($row['source'] ?? '');
			$views  = absint($row['views'] ?? 0);

			if ('' === $source) {
				continue;
			}

			$views_by_source[$source] = $views;
			$total_views             += $views;

			if ($views > 0) {
				$active_sources++;
			}

			if ($views > $top_source['views']) {
				$top_source = array(
					'source' => $source,
					'views'  => $views,
				);
			}
		}

		$sources = array();

		foreach ($ai_sources as $source) {
			$sources[] = array(
				'source' => $source,
				'views'  => $views_by_source[$source] ?? 0,
				'active' => ! empty($views_by_source[$source]),
			);
		}

		usort(
			$sources,
			static function ($a, $b) {
				if ($a['views'] === $b['views']) {
					return strcmp($a['source'], $b['source']);
				}

				return $b['views'] <=> $a['views'];
			}
		);

		$data = array(
			'total_views'    => $total_views,
			'active_sources' => $active_sources,
			'total_sources'  => count($ai_sources),
			'top_source'     => $top_source,
			'sources'        => $sources,
		);

		wp_cache_set($cache_key, $data, 'a360_insights', MINUTE_IN_SECONDS);

		return $data;
	}

	/**
	 * Get top referrers.
	 *
	 * @param int $limit Number of rows.
	 * @return array
	 */
	public static function get_top_referrers($limit = 8, $range = 'month', $offset = 0)
	{
		global $wpdb;

		$table_name = self::table_name();
		$limit      = absint($limit);
		$bounds = self::date_bounds($range, '', $offset);

		if ($limit < 1) {
			$limit = 8;
		}

		$cache_key = 'top_sources_' . md5(
			$range . '_' . $offset . '_' . $limit . '_' . $bounds['start'] . '_' . $bounds['end']
		);

		$rows = wp_cache_get($cache_key, 'a360_insights');

		if (false === $rows) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom analytics table query cached with wp_cache.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'
			SELECT
				CASE
					WHEN referrer_host <> "" THEN referrer_host
					WHEN source <> "" THEN source
					ELSE "direct"
				END AS source,
				COUNT(*) AS views
			FROM `' . esc_sql($table_name) . '`
			WHERE viewed_at >= %s
			AND viewed_at < %s
			GROUP BY source
			ORDER BY views DESC
			LIMIT %d
			',
					$bounds['start'],
					$bounds['end'],
					$limit
				),
				ARRAY_A
			);

			wp_cache_set($cache_key, $rows, 'a360_insights', MINUTE_IN_SECONDS);
		}

		return $rows;
	}

	/**
	 * Get AI assistant traffic views.
	 *
	 * @return int
	 */
	public static function get_ai_traffic_views()
	{
		global $wpdb;

		$table_name = self::table_name();

		$conditions = array();

		foreach (self::ai_sources() as $source) {
			$like = '%' . $wpdb->esc_like($source) . '%';

			$conditions[] = $wpdb->prepare('source LIKE %s', $like);
			$conditions[] = $wpdb->prepare('referrer_host LIKE %s', $like);
			$conditions[] = $wpdb->prepare('referrer_url LIKE %s', $like);
		}

		$sql = 'SELECT COUNT(*) FROM `' . esc_sql($table_name) . '` WHERE ' . implode(' OR ', $conditions);

		$cache_key = 'ai_total_all_' . md5($sql);
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return (int) $cached;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom analytics table query; conditions are prepared above and result is cached.
		$count = (int) $wpdb->get_var($sql);

		wp_cache_set($cache_key, $count, 'a360_insights', MINUTE_IN_SECONDS);

		return $count;
	}

	/**
	 * Get referral traffic views.
	 *
	 * @return int
	 */
	public static function get_referral_traffic_views()
	{
		global $wpdb;

		$table_name = self::table_name();

		$cache_key = 'a360_referral_views';

		$cached = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return (int) $cached;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$count = (int) $wpdb->get_var(
			'SELECT COUNT(*) FROM `' . esc_sql($table_name) . '`
	WHERE source = "referral"'
		);

		wp_cache_set($cache_key, $count, 'a360_insights', MINUTE_IN_SECONDS);

		return $count;
	}
	/**
	 * Get search traffic views.
	 *
	 * @return int
	 */
	public static function get_search_traffic_views()
	{
		global $wpdb;

		$cache_key = 'a360_search_traffic_views';
		$cache_group = 'a360_insights';

		$cached = wp_cache_get($cache_key, $cache_group);

		if (false !== $cached) {
			return (int) $cached;
		}

		$table_name = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$count = (int) $wpdb->get_var(
			'SELECT COUNT(*) FROM `' . esc_sql($table_name) . '`
		WHERE source IN ("google","bing","yahoo","duckduckgo","search")'
		);

		wp_cache_set($cache_key, $count, $cache_group, 300);

		return $count;
	}
	/**
	 * Get total views across all content.
	 *
	 * @return int
	 */
	public static function get_site_total_views()
	{
		global $wpdb;

		$cache_key = 'site_total_views';
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return (int) $cached;
		}

		$table_name = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total = (int) $wpdb->get_var(
			'SELECT COUNT(*) FROM ' . esc_sql($table_name)
		);

		wp_cache_set(
			$cache_key,
			$total,
			'a360_insights',
			5 * MINUTE_IN_SECONDS
		);

		return $total;
	}

	/**
	 * Get daily views for the whole site.
	 *
	 * @param int $days Number of days.
	 * @return array
	 */
	public static function get_site_daily_views($range = 'month', $offset = 0)
	{
		global $wpdb;

		$table_name = self::table_name();
		$bounds     = self::date_bounds($range, '', $offset);

		$cache_key = 'site_daily_views_' . sanitize_key($range) . '_' . absint($offset);
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'
			SELECT DATE(viewed_at) AS label, COUNT(*) AS views
			FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s
			GROUP BY label
			ORDER BY label ASC
			',
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);

		$indexed = array();

		foreach ($rows as $row) {
			$indexed[$row['label']] = (int) $row['views'];
		}

		$timezone = wp_timezone();
		$start    = new DateTimeImmutable($bounds['start'], $timezone);
		$end      = new DateTimeImmutable($bounds['end'], $timezone);
		$data     = array();

		for ($date = $start; $date < $end; $date = $date->modify('+1 day')) {
			$key = $date->format('Y-m-d');

			$data[] = array(
				'label' => $key,
				'views' => isset($indexed[$key]) ? (int) $indexed[$key] : 0,
			);
		}

		wp_cache_set($cache_key, $data, 'a360_insights', 5 * MINUTE_IN_SECONDS);

		return $data;
	}
	/**
	 * Get top viewed content.
	 *
	 * @param int $limit Number of rows.
	 * @return array
	 */
	public static function get_top_content($limit = 8, $range = 'month', $offset = 0)
	{
		global $wpdb;

		$table_name = self::table_name();
		$limit      = absint($limit);
		$bounds     = self::date_bounds($range, '', $offset);

		if ($limit < 1) {
			$limit = 8;
		}

		$cache_key = 'top_content_' . sanitize_key($range) . '_' . absint($offset) . '_' . absint($limit);
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'
			SELECT
				p.ID,
				p.post_title,
				p.post_type,
				COUNT(e.id) AS views
			FROM ' . esc_sql($table_name) . ' e
			INNER JOIN ' . esc_sql($wpdb->posts) . ' p ON p.ID = e.post_id
			WHERE p.post_status = "publish"
			AND e.viewed_at >= %s
			AND e.viewed_at < %s
			GROUP BY p.ID
			ORDER BY views DESC
			LIMIT %d
			',
				$bounds['start'],
				$bounds['end'],
				$limit
			),
			ARRAY_A
		);

		wp_cache_set($cache_key, $rows, 'a360_insights', 300);

		return $rows;
	}

	/**
	 * Get top landing pages by anonymous sessions.
	 *
	 * @param int    $limit  Number of rows.
	 * @param string $range  Date range.
	 * @param int    $offset Range offset.
	 * @return array
	 */
	public static function get_top_landing_pages($limit = 8, $range = 'month', $offset = 0)
	{
		global $wpdb;

		$sessions_table   = self::sessions_table_name();
		$engagement_table = self::engagement_table_name();
		$limit            = absint($limit);
		$bounds           = self::date_bounds($range, '', $offset);
		$key_events       = array_values(array_intersect(
			(array) A360_Insights_Settings::get_value('key_events', array('phone_click', 'email_click', 'form_submit')),
			A360_Insights_Settings::available_key_events()
		));

		if ($limit < 1) {
			$limit = 8;
		}

		$cache_key = 'top_landing_pages_' . sanitize_key($range) . '_' . absint($offset) . '_' . absint($limit) . '_' . md5(wp_json_encode($key_events));
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}

		$conversion_join   = '';
		$conversion_select = '0 AS conversion_sessions';
		$query_values      = array();

		if (! empty($key_events)) {
			$placeholders = implode(', ', array_fill(0, count($key_events), '%s'));
			$conversion_select = 'COUNT(c.session_hash) AS conversion_sessions';
			$conversion_join = '
				LEFT JOIN (
					SELECT DISTINCT session_hash
					FROM ' . esc_sql($engagement_table) . '
					WHERE event_type IN (' . $placeholders . ')
					AND event_at >= %s
					AND event_at < %s
					AND session_hash <> ""
				) c ON c.session_hash = s.session_hash';
			$query_values = array_merge($key_events, array($bounds['start'], $bounds['end']));
		}

		$query_values[] = $bounds['start'];
		$query_values[] = $bounds['end'];
		$query_values[] = $limit;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom table report query; dynamic conversion subquery uses sanitized event placeholders.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					s.landing_post_id AS ID,
					COUNT(*) AS sessions,
					SUM(CASE WHEN s.is_engaged = 1 THEN 1 ELSE 0 END) AS engaged_sessions,
					' . $conversion_select . '
				FROM ' . esc_sql($sessions_table) . ' s
				' . $conversion_join . '
				INNER JOIN ' . esc_sql($wpdb->posts) . ' p ON p.ID = s.landing_post_id
				WHERE p.post_status = "publish"
				AND s.started_at >= %s
				AND s.started_at < %s
				AND s.landing_post_id > 0
				GROUP BY s.landing_post_id
				ORDER BY sessions DESC
				LIMIT %d',
				$query_values
			),
			ARRAY_A
		);

		$normalized = array();

		foreach ($rows as $row) {
			$post_id             = absint($row['ID'] ?? 0);
			$sessions            = absint($row['sessions'] ?? 0);
			$engaged_sessions    = absint($row['engaged_sessions'] ?? 0);
			$conversion_sessions = absint($row['conversion_sessions'] ?? 0);
			$post                = $post_id ? get_post($post_id) : null;

			if (! $post) {
				continue;
			}

			$title = get_the_title($post);

			if ('' === $title) {
				$title = __('(no title)', 'a360-insights');
			}

			$post_type_object = get_post_type_object($post->post_type);
			$post_type_label  = $post_type_object ? $post_type_object->labels->singular_name : $post->post_type;

			$normalized[] = array(
				'ID'                  => $post_id,
				'post_title'          => $title,
				'post_type'           => $post_type_label,
				'sessions'            => $sessions,
				'engaged_sessions'    => $engaged_sessions,
				'conversion_sessions' => $conversion_sessions,
				'engagement_rate'     => $sessions > 0 ? round(($engaged_sessions / $sessions) * 100, 1) : 0,
				'conversion_rate'     => $sessions > 0 ? round(($conversion_sessions / $sessions) * 100, 1) : 0,
			);
		}

		if (! empty($normalized)) {
			$post_ids     = wp_list_pluck($normalized, 'ID');
			$placeholders = implode(', ', array_fill(0, count($post_ids), '%d'));
			$source_values = array_merge($post_ids, array($bounds['start'], $bounds['end']));

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Post ID placeholder list is generated from normalized integer IDs.
			$source_rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT landing_post_id, source, COUNT(*) AS sessions
					FROM ' . esc_sql($sessions_table) . '
					WHERE landing_post_id IN (' . $placeholders . ')
					AND started_at >= %s
					AND started_at < %s
					GROUP BY landing_post_id, source
					ORDER BY landing_post_id ASC, sessions DESC',
					$source_values
				),
				ARRAY_A
			);

			$top_sources = array();

			foreach ($source_rows as $source_row) {
				$landing_post_id = absint($source_row['landing_post_id'] ?? 0);

				if (! $landing_post_id || isset($top_sources[$landing_post_id])) {
					continue;
				}

				$top_sources[$landing_post_id] = array(
					'source'   => sanitize_key($source_row['source'] ?? 'direct'),
					'sessions' => absint($source_row['sessions'] ?? 0),
				);
			}

			foreach ($normalized as $index => $row) {
				$post_id = absint($row['ID'] ?? 0);
				$source  = $top_sources[$post_id] ?? array('source' => 'direct', 'sessions' => 0);

				$normalized[$index]['top_source']          = $source['source'];
				$normalized[$index]['top_source_sessions'] = $source['sessions'];
			}
		}

		wp_cache_set($cache_key, $normalized, 'a360_insights', 300);

		return $normalized;
	}

	/**
	 * Get daily views by traffic type.
	 *
	 * @param string $type Traffic type.
	 * @param int    $days Number of days.
	 * @return array
	 */
	public static function get_daily_views_by_type($type = 'site', $range = 'month', $offset = 0)
	{
		global $wpdb;

		$table_name = self::table_name();
		$bounds     = self::date_bounds($range, '', $offset);
		$range      = sanitize_key($range);

		$source_filter = '';

		switch ($type) {
			case 'ai':
				$source_filter = 'ai';
				break;

			case 'search':
				$source_filter = 'search';
				break;

			case 'referral':
				$source_filter = 'referral';
				break;
		}

		$ai_sources = self::ai_sources();

		if ('day' === $range) {
			$cache_key = 'traffic_hourly_' . sanitize_key($range) . '_' . absint($offset) . '_' . sanitize_key($source_filter);
			$cached    = wp_cache_get($cache_key, 'a360_insights');

			if (false !== $cached) {
				return $cached;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'
			SELECT HOUR(viewed_at) AS period, COUNT(*) AS views
			FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s
			AND (
				%s = ""
						OR (%s = "ai" AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ))
				OR (%s = "search" AND source IN ("google","bing","yahoo","duckduckgo","search"))
				OR (%s = "referral" AND source = "referral")
			)
			GROUP BY period
			ORDER BY period ASC
			',
					$bounds['start'],
					$bounds['end'],
					$source_filter,
					$source_filter,
					$ai_sources[0],
					$ai_sources[1],
					$ai_sources[2],
					$ai_sources[3],
					$ai_sources[4],
					$ai_sources[5],
					$ai_sources[6],
					$ai_sources[7],
					$ai_sources[8],
					$ai_sources[9],
					$ai_sources[10],
					$ai_sources[11],
					$ai_sources[12],
					$ai_sources[13],
					$source_filter,
					$source_filter
				),
				ARRAY_A
			);

			$indexed = array();

			foreach ($rows as $row) {
				$indexed[(int) $row['period']] = (int) $row['views'];
			}

			$data = array();

			for ($hour = 0; $hour < 24; $hour++) {
				$data[] = isset($indexed[$hour]) ? (int) $indexed[$hour] : 0;
			}

			wp_cache_set($cache_key, $data, 'a360_insights', 300);

			return $data;
		}

		if ('year' === $range) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'
				SELECT DATE_FORMAT(viewed_at, "%%Y-%%m") AS period, COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE viewed_at >= %s
				AND viewed_at < %s
				AND (
					%s = ""
					OR (%s = "ai" AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ))
					OR (%s = "search" AND source IN ("google","bing","yahoo","duckduckgo","search"))
					OR (%s = "referral" AND source = "referral")
				)
				GROUP BY period
				ORDER BY period ASC
				',
					$bounds['start'],
					$bounds['end'],
					$source_filter,
					$source_filter,
					$ai_sources[0],
					$ai_sources[1],
					$ai_sources[2],
					$ai_sources[3],
					$ai_sources[4],
					$ai_sources[5],
					$ai_sources[6],
					$ai_sources[7],
					$ai_sources[8],
					$ai_sources[9],
					$ai_sources[10],
					$ai_sources[11],
					$ai_sources[12],
					$ai_sources[13],
					$source_filter,
					$source_filter
				),
				ARRAY_A
			);

			$indexed = array();

			foreach ($rows as $row) {
				$indexed[$row['period']] = (int) $row['views'];
			}

			$timezone = wp_timezone();
			$start    = new DateTimeImmutable($bounds['start'], $timezone);
			$data     = array();

			for ($i = 0; $i < 12; $i++) {
				$key    = $start->modify('+' . $i . ' months')->format('Y-m');
				$data[] = isset($indexed[$key]) ? (int) $indexed[$key] : 0;
			}

			return $data;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'
			SELECT DATE(viewed_at) AS period, COUNT(*) AS views
			FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s
			AND (
				%s = ""
					OR (%s = "ai" AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ))
				OR (%s = "search" AND source IN ("google","bing","yahoo","duckduckgo","search"))
				OR (%s = "referral" AND source = "referral")
			)
			GROUP BY period
			ORDER BY period ASC
			',
				$bounds['start'],
				$bounds['end'],
				$source_filter,
				$source_filter,
				$ai_sources[0],
				$ai_sources[1],
				$ai_sources[2],
				$ai_sources[3],
				$ai_sources[4],
				$ai_sources[5],
				$ai_sources[6],
				$ai_sources[7],
				$ai_sources[8],
				$ai_sources[9],
				$ai_sources[10],
				$ai_sources[11],
				$ai_sources[12],
				$ai_sources[13],
				$source_filter,
				$source_filter
			),
			ARRAY_A
		);

		$indexed = array();

		foreach ($rows as $row) {
			$indexed[$row['period']] = (int) $row['views'];
		}

		$timezone = wp_timezone();
		$start    = new DateTimeImmutable($bounds['start'], $timezone);
		$end      = new DateTimeImmutable($bounds['end'], $timezone);
		$data     = array();

		for ($date = $start; $date < $end; $date = $date->modify('+1 day')) {
			$key    = $date->format('Y-m-d');
			$data[] = isset($indexed[$key]) ? (int) $indexed[$key] : 0;
		}

		return $data;
	}

	public static function get_period_label($range = 'month', $offset = 0)
	{
		$bounds   = self::date_bounds($range, '', $offset);
		$timezone = wp_timezone();
		$start    = new DateTimeImmutable($bounds['start'], $timezone);
		$end      = (new DateTimeImmutable($bounds['end'], $timezone))->modify('-1 second');

		switch (sanitize_key($range)) {
			case 'day':
				return wp_date('F j, Y', $start->getTimestamp());

			case 'week':
				return sprintf(
					'%s – %s',
					wp_date('F j, Y', $start->getTimestamp()),
					wp_date('F j, Y', $end->getTimestamp())
				);

			case 'year':
				return wp_date('Y', $start->getTimestamp());

			case 'month':
			default:
				return sprintf(
					'%s – %s',
					wp_date('F j, Y', $start->getTimestamp()),
					wp_date('F j, Y', $end->getTimestamp())
				);
		}
	}
	/**
	 * Get dashboard KPI stats.
	 *
	 * @param string $range Range key.
	 * @return array
	 */

	public static function get_dashboard_kpi($range = 'month', $offset = 0)
	{
		global $wpdb;

		$table_name = self::table_name();
		$bounds     = self::date_bounds($range, '', $offset);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_views = (int) $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL contains escaped table name and prepared AI LIKE placeholders.
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s',
				$bounds['start'],
				$bounds['end']
			)
		);

		$ai_sources = self::ai_sources();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom analytics table query with prepared values.
		$ai_views = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . '
		WHERE viewed_at >= %s
		AND viewed_at < %s
		AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )',
				$bounds['start'],
				$bounds['end'],
				$ai_sources[0],
				$ai_sources[1],
				$ai_sources[2],
				$ai_sources[3],
				$ai_sources[4],
				$ai_sources[5],
				$ai_sources[6],
				$ai_sources[7],
				$ai_sources[8],
				$ai_sources[9],
				$ai_sources[10],
				$ai_sources[11],
				$ai_sources[12],
				$ai_sources[13]
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$search_views = (int) $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s
			AND source IN ("google","bing","yahoo","duckduckgo","search")',
				$bounds['start'],
				$bounds['end']
			)
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$referral_views = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . '
			WHERE viewed_at >= %s
			AND viewed_at < %s
			AND source = "referral"',
				$bounds['start'],
				$bounds['end']
			)
		);

		return array(
			'total_views'    => $total_views,
			'ai_views'       => $ai_views,
			'search_views'   => $search_views,
			'referral_views' => $referral_views,
		);
	}
	/**
	 * Get date range SQL condition.
	 *
	 * @param string $range Range key.
	 * @return string
	 */
	private static function get_range_condition($range)
	{
		$range = sanitize_key($range);

		switch ($range) {
			case 'day':
				return 'viewed_at >= UTC_DATE()';

			case 'week':
				return 'viewed_at >= DATE_SUB(UTC_DATE(), INTERVAL 6 DAY)';

			case 'year':
				return 'viewed_at >= DATE_SUB(UTC_DATE(), INTERVAL 364 DAY)';

			case 'month':
			default:
				return 'viewed_at >= DATE_SUB(UTC_DATE(), INTERVAL 29 DAY)';
		}
	}
	/**
	 * Get stats for modal.
	 *
	 * @param int    $post_id Post ID.
	 * @param string $range Range.
	 * @param string $anchor Anchor date.
	 * @return array
	 */
	public static function get_stats($post_id, $range = 'month', $anchor = '')
	{
		global $wpdb;

		$post_id = absint($post_id);
		$range   = self::sanitize_range($range);
		$anchor  = self::sanitize_anchor_date($anchor);

		$cache_key = self::cache_key($post_id) . '_' . $range . '_' . $anchor;
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}

		$bounds           = self::date_bounds($range, $anchor);
		$table_name       = self::table_name();
		$engagement_table = self::engagement_table_name();
		$sessions_table   = self::sessions_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads period aggregate from custom plugin table; cached below.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . ' WHERE post_id = %d AND viewed_at >= %s AND viewed_at < %s',
				$post_id,
				$bounds['start'],
				$bounds['end']
			)
		);

		if ('day' === $range) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads chart data from custom plugin table; cached below.
			$chart_rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT DATE_FORMAT(viewed_at, "%%Y-%%m-%%d %%H:00") AS label, COUNT(*) AS views
					FROM ' . esc_sql($table_name) . '
					WHERE post_id = %d
					AND viewed_at >= %s
					AND viewed_at < %s
					GROUP BY label
					ORDER BY label ASC',
					$post_id,
					$bounds['start'],
					$bounds['end']
				),
				ARRAY_A
			);
		} elseif ('year' === $range) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads chart data from custom plugin table; cached below.
			$chart_rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT DATE_FORMAT(viewed_at, "%%Y-%%m") AS label, COUNT(*) AS views
					FROM ' . esc_sql($table_name) . '
					WHERE post_id = %d
					AND viewed_at >= %s
					AND viewed_at < %s
					GROUP BY label
					ORDER BY label ASC',
					$post_id,
					$bounds['start'],
					$bounds['end']
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads chart data from custom plugin table; cached below.
			$chart_rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT DATE(viewed_at) AS label, COUNT(*) AS views
					FROM ' . esc_sql($table_name) . '
					WHERE post_id = %d
					AND viewed_at >= %s
					AND viewed_at < %s
					GROUP BY label
					ORDER BY label ASC',
					$post_id,
					$bounds['start'],
					$bounds['end']
				),
				ARRAY_A
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads referrer stats from custom plugin table; cached below.
		$top_referrers = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					CASE
						WHEN referrer_host = "" THEN source
						ELSE referrer_host
					END AS label,
					source,
					COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d AND viewed_at >= %s AND viewed_at < %s
				GROUP BY label, source
				ORDER BY views DESC
				LIMIT 10',
				$post_id,
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);

		$ai_sources = self::ai_sources();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads traffic source groups from custom plugin table; cached below.
		$sources = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					CASE
						WHEN source IN ("google_business_profile", "google_business_profile_candidate") THEN source
						WHEN utm_campaign <> "" OR utm_source <> "" OR utm_medium <> "" THEN "campaign"
						WHEN source = "direct" THEN "direct"
						WHEN source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) THEN "ai_traffic"
						WHEN source IN ("google", "bing", "yahoo", "duckduckgo", "search") THEN "organic_search"
						WHEN source IN ("facebook", "instagram", "youtube", "x", "twitter", "linkedin", "tiktok", "pinterest", "social") THEN "social"
						ELSE "referral"
					END AS channel,
					COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d AND viewed_at >= %s AND viewed_at < %s
				GROUP BY channel
				ORDER BY views DESC',
				$ai_sources[0],
				$ai_sources[1],
				$ai_sources[2],
				$ai_sources[3],
				$ai_sources[4],
				$ai_sources[5],
				$ai_sources[6],
				$ai_sources[7],
				$ai_sources[8],
				$ai_sources[9],
				$ai_sources[10],
				$ai_sources[11],
				$ai_sources[12],
				$ai_sources[13],
				$post_id,
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads UTM campaign stats from custom plugin table; cached below.
		$campaigns = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					utm_campaign,
					utm_source,
					utm_medium,
					COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				AND viewed_at >= %s
				AND viewed_at < %s
				AND (utm_campaign <> "" OR utm_source <> "" OR utm_medium <> "")
				GROUP BY utm_campaign, utm_source, utm_medium
				ORDER BY views DESC
				LIMIT 20',
				$post_id,
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$recent = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->prepare(
				'SELECT viewed_at, source, referrer_host, utm_source, utm_medium, utm_campaign
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				ORDER BY viewed_at DESC
				LIMIT 10',
				$post_id
			),
			ARRAY_A
		);

		$ai_sources = self::ai_sources();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom analytics table query.
		$ai_views = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*)
			FROM ' . esc_sql($table_name) . '
			WHERE post_id = %d
			AND viewed_at >= %s
			AND viewed_at < %s
			AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )',
				$post_id,
				$bounds['start'],
				$bounds['end'],
				$ai_sources[0],
				$ai_sources[1],
				$ai_sources[2],
				$ai_sources[3],
				$ai_sources[4],
				$ai_sources[5],
				$ai_sources[6],
				$ai_sources[7],
				$ai_sources[8],
				$ai_sources[9],
				$ai_sources[10],
				$ai_sources[11],
				$ai_sources[12],
				$ai_sources[13]
			)
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$search_views = (int) $wpdb->get_var(
			$wpdb->prepare(
				'
		SELECT COUNT(*)
		FROM ' . esc_sql($table_name) . '
		WHERE post_id = %d
		AND viewed_at >= %s
		AND viewed_at < %s
		AND source IN ("google","bing","yahoo","duckduckgo","search")
		',
				$post_id,
				$bounds['start'],
				$bounds['end']
			)
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$referral_views = (int) $wpdb->get_var(
			$wpdb->prepare(
				'
		SELECT COUNT(*)
		FROM ' . esc_sql($table_name) . '
		WHERE post_id = %d
		AND viewed_at >= %s
		AND viewed_at < %s
		AND source = "referral"
		',
				$post_id,
				$bounds['start'],
				$bounds['end']
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counts anonymous sessions attached to this content.
		$sessions = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(DISTINCT session_hash)
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				AND viewed_at >= %s
				AND viewed_at < %s
				AND session_hash <> ""',
				$post_id,
				$bounds['start'],
				$bounds['end']
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counts anonymous engaged sessions attached to this content.
		$engaged_sessions = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(DISTINCT e.session_hash)
				FROM ' . esc_sql($table_name) . ' e
				INNER JOIN ' . esc_sql($sessions_table) . ' s ON e.session_hash = s.session_hash
				WHERE e.post_id = %d
				AND e.viewed_at >= %s
				AND e.viewed_at < %s
				AND e.session_hash <> ""
				AND s.is_engaged = 1',
				$post_id,
				$bounds['start'],
				$bounds['end']
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads anonymous engagement events for this content.
		$engagement_rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT event_type, event_value, COUNT(*) AS events
				FROM ' . esc_sql($engagement_table) . '
				WHERE post_id = %d
				AND event_at >= %s
				AND event_at < %s
				GROUP BY event_type, event_value',
				$post_id,
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);

		$funnel = self::get_content_funnel($post_id, $bounds);
		$conversions = self::get_content_conversions($post_id, $bounds, $sessions);

		$data = array(
			'total'         => $total,
			'chart'         => self::normalize_chart_rows($chart_rows, $range, $bounds),
			'top_referrers' => self::normalize_top_referrers($top_referrers),
			'sources'       => self::normalize_source_groups($sources),
			'campaigns'     => self::normalize_campaign_rows($campaigns),
			'recent'        => self::normalize_recent_rows($recent),
			'range'         => $range,
			'anchor'        => $anchor,
			'period_label'  => self::period_label($range, $bounds),
			'can_go_next'   => self::can_go_next($bounds),
			'ai_views'       => $ai_views,
			'search_views'   => $search_views,
			'referral_views' => $referral_views,
			'sessions'       => $sessions,
			'engaged_sessions' => $engaged_sessions,
			'ai_percent'     => $total > 0 ? round(($ai_views / $total) * 100, 1) : 0,
			'search_percent' => $total > 0 ? round(($search_views / $total) * 100, 1) : 0,
			'referral_percent' => $total > 0 ? round(($referral_views / $total) * 100, 1) : 0,
			'engagement_rate' => $sessions > 0 ? round(($engaged_sessions / $sessions) * 100, 1) : 0,
			'conversions'     => $conversions['total'],
			'conversion_sessions' => $conversions['sessions'],
			'conversion_rate' => $sessions > 0 ? round(($conversions['sessions'] / $sessions) * 100, 1) : 0,
			'key_events'      => $conversions['events'],
			'engagement'       => self::normalize_engagement_rows($engagement_rows, $total),
			'funnel'           => $funnel,
		);

		wp_cache_set($cache_key, $data, 'a360_insights', 5 * MINUTE_IN_SECONDS);

		return $data;
	}

	/**
	 * Get stats for a traffic source modal.
	 *
	 * @param string $source Source key.
	 * @param string $range  Range.
	 * @param string $anchor Anchor date.
	 * @return array
	 */
	public static function get_source_stats($source, $range = 'month', $anchor = '')
	{
		global $wpdb;

		$source = sanitize_key($source);
		$range  = self::sanitize_range($range);
		$anchor = self::sanitize_anchor_date($anchor);

		if ('' === $source) {
			return array();
		}

		$cache_key = 'source_stats_' . md5($source . '_' . $range . '_' . $anchor);
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}

		$bounds     = self::date_bounds($range, $anchor);
		$table_name = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads source aggregate from custom plugin table; cached below.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . '
				WHERE source = %s
				AND viewed_at >= %s
				AND viewed_at < %s',
				$source,
				$bounds['start'],
				$bounds['end']
			)
		);

		if ('day' === $range) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads chart data from custom plugin table; cached below.
			$chart_rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT DATE_FORMAT(viewed_at, "%%Y-%%m-%%d %%H:00") AS label, COUNT(*) AS views
					FROM ' . esc_sql($table_name) . '
					WHERE source = %s
					AND viewed_at >= %s
					AND viewed_at < %s
					GROUP BY label
					ORDER BY label ASC',
					$source,
					$bounds['start'],
					$bounds['end']
				),
				ARRAY_A
			);
		} elseif ('year' === $range) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads chart data from custom plugin table; cached below.
			$chart_rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT DATE_FORMAT(viewed_at, "%%Y-%%m") AS label, COUNT(*) AS views
					FROM ' . esc_sql($table_name) . '
					WHERE source = %s
					AND viewed_at >= %s
					AND viewed_at < %s
					GROUP BY label
					ORDER BY label ASC',
					$source,
					$bounds['start'],
					$bounds['end']
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads chart data from custom plugin table; cached below.
			$chart_rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT DATE(viewed_at) AS label, COUNT(*) AS views
					FROM ' . esc_sql($table_name) . '
					WHERE source = %s
					AND viewed_at >= %s
					AND viewed_at < %s
					GROUP BY label
					ORDER BY label ASC',
					$source,
					$bounds['start'],
					$bounds['end']
				),
				ARRAY_A
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads top content for source modal; cached below.
		$top_content_rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT post_id, COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE source = %s
				AND viewed_at >= %s
				AND viewed_at < %s
				GROUP BY post_id
				ORDER BY views DESC
				LIMIT 10',
				$source,
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);

		$top_content = array();

		foreach ($top_content_rows as $row) {
			$post_id = absint($row['post_id'] ?? 0);
			$post    = $post_id ? get_post($post_id) : null;

			if (! $post) {
				continue;
			}

			$title = get_the_title($post);

			if ('' === $title) {
				$title = __('(no title)', 'a360-insights');
			}

			$top_content[] = array(
				'id'    => $post_id,
				'title' => $title,
				'views' => absint($row['views'] ?? 0),
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads UTM campaign stats from custom plugin table; cached below.
		$campaigns = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					utm_campaign,
					utm_source,
					utm_medium,
					COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE source = %s
				AND viewed_at >= %s
				AND viewed_at < %s
				AND (utm_campaign <> "" OR utm_source <> "" OR utm_medium <> "")
				GROUP BY utm_campaign, utm_source, utm_medium
				ORDER BY views DESC
				LIMIT 20',
				$source,
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads recent visits from custom plugin table; cached below.
		$recent = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT viewed_at, source, referrer_host, utm_source, utm_medium, utm_campaign
				FROM ' . esc_sql($table_name) . '
				WHERE source = %s
				ORDER BY viewed_at DESC
				LIMIT 10',
				$source
			),
			ARRAY_A
		);

		$ai_views = in_array($source, self::ai_sources(), true) ? $total : 0;

		$data = array(
			'total'            => $total,
			'chart'            => self::normalize_chart_rows($chart_rows, $range, $bounds),
			'top_referrers'    => $top_content,
			'sources'          => array(
				array(
					'label' => ucwords(str_replace(array('-', '_'), ' ', $source)),
					'views' => $total,
				),
			),
			'campaigns'        => self::normalize_campaign_rows($campaigns),
			'recent'           => self::normalize_recent_rows($recent),
			'range'            => $range,
			'anchor'           => $anchor,
			'period_label'     => self::period_label($range, $bounds),
			'can_go_next'      => self::can_go_next($bounds),
			'ai_views'         => $ai_views,
			'search_views'     => 0,
			'referral_views'   => 0,
			'ai_percent'       => $total > 0 ? round(($ai_views / $total) * 100, 1) : 0,
			'search_percent'   => 0,
			'referral_percent' => 0,
		);

		wp_cache_set($cache_key, $data, 'a360_insights', MINUTE_IN_SECONDS);

		return $data;
	}

	/**
	 * Get summary data for WordPress dashboard widget.
	 *
	 * @return array
	 */
	public static function get_dashboard_summary()
	{
		global $wpdb;

		$cache_key = 'dashboard_summary';
		$cached    = wp_cache_get($cache_key, 'a360_insights');

		if (false !== $cached) {
			return $cached;
		}

		$table_name = self::table_name();
		$timezone   = wp_timezone();
		$today      = new DateTimeImmutable('today', $timezone);
		$tomorrow   = $today->modify('+1 day');
		$last_7     = $today->modify('-6 days');

		$today_start = $today->format('Y-m-d H:i:s');
		$today_end   = $tomorrow->format('Y-m-d H:i:s');
		$week_start  = $last_7->format('Y-m-d H:i:s');

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads dashboard aggregate from custom plugin table; cached below.
		$views_today = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . ' WHERE viewed_at >= %s AND viewed_at < %s',
				$today_start,
				$today_end
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads dashboard aggregate from custom plugin table; cached below.
		$views_7_days = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . ' WHERE viewed_at >= %s AND viewed_at < %s',
				$week_start,
				$today_end
			)
		);

		$ai_sources = self::ai_sources();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Reads AI aggregate from custom plugin table; cached below.
		$ai_views_7_days = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COUNT(*) FROM ' . esc_sql($table_name) . '
					WHERE viewed_at >= %s
					AND viewed_at < %s
					AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )',
					$week_start,
					$today_end,
					$ai_sources[0],
					$ai_sources[1],
					$ai_sources[2],
					$ai_sources[3],
					$ai_sources[4],
					$ai_sources[5],
					$ai_sources[6],
					$ai_sources[7],
					$ai_sources[8],
					$ai_sources[9],
					$ai_sources[10],
					$ai_sources[11],
					$ai_sources[12],
					$ai_sources[13]
				)
			);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads dashboard aggregate from custom plugin table; cached below.
		$total_views = (int) $wpdb->get_var('SELECT COUNT(*) FROM ' . esc_sql($table_name));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads top content for dashboard widget; cached below.
		$top_content_rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT post_id, COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE viewed_at >= %s AND viewed_at < %s
				GROUP BY post_id
				ORDER BY views DESC
				LIMIT 5',
				$week_start,
				$today_end
			),
			ARRAY_A
		);

		$top_content = array();

		foreach ($top_content_rows as $row) {
			$post_id = absint($row['post_id'] ?? 0);
			$post    = $post_id ? get_post($post_id) : null;

			if (! $post) {
				continue;
			}

			$title = get_the_title($post);

			if ('' === $title) {
				$title = __('(no title)', 'a360-insights');
			}

			$top_content[] = array(
				'id'    => $post_id,
				'title' => $title,
				'views' => absint($row['views'] ?? 0),
				'edit'  => get_edit_post_link($post_id, ''),
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads top campaigns for dashboard widget; cached below.
		$top_campaigns = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					utm_campaign,
					utm_source,
					utm_medium,
					COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE viewed_at >= %s
				AND viewed_at < %s
				AND (utm_campaign <> "" OR utm_source <> "" OR utm_medium <> "")
				GROUP BY utm_campaign, utm_source, utm_medium
				ORDER BY views DESC
				LIMIT 5',
				$week_start,
				$today_end
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Reads top AI sources for dashboard widget; cached below.
		$top_ai_sources = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT source, COUNT(*) AS views
					FROM ' . esc_sql($table_name) . '
					WHERE viewed_at >= %s
					AND viewed_at < %s
					AND source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )
					GROUP BY source
					ORDER BY views DESC
					LIMIT 5',
					$week_start,
					$today_end,
					$ai_sources[0],
					$ai_sources[1],
					$ai_sources[2],
					$ai_sources[3],
					$ai_sources[4],
					$ai_sources[5],
					$ai_sources[6],
					$ai_sources[7],
					$ai_sources[8],
					$ai_sources[9],
					$ai_sources[10],
					$ai_sources[11],
					$ai_sources[12],
					$ai_sources[13]
				),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads top traffic sources for dashboard widget; cached below.
		$top_sources = $wpdb->get_results(
			$wpdb->prepare(
					'SELECT
						CASE
							WHEN source IN ("google_business_profile", "google_business_profile_candidate") THEN source
							WHEN utm_campaign <> "" OR utm_source <> "" OR utm_medium <> "" THEN "campaign"
							WHEN source = "direct" THEN "direct"
							WHEN source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) THEN "ai_traffic"
							WHEN source IN ("google", "bing", "yahoo", "duckduckgo", "search") THEN "organic_search"
							WHEN source IN ("facebook", "instagram", "youtube", "x", "twitter", "linkedin", "tiktok", "pinterest", "social") THEN "social"
							ELSE "referral"
					END AS channel,
					COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
					WHERE viewed_at >= %s AND viewed_at < %s
					GROUP BY channel
					ORDER BY views DESC
					LIMIT 5',
					$ai_sources[0],
					$ai_sources[1],
					$ai_sources[2],
					$ai_sources[3],
					$ai_sources[4],
					$ai_sources[5],
					$ai_sources[6],
					$ai_sources[7],
					$ai_sources[8],
					$ai_sources[9],
					$ai_sources[10],
					$ai_sources[11],
					$ai_sources[12],
					$ai_sources[13],
					$week_start,
					$today_end
				),
			ARRAY_A
		);

		$data = array(
			'views_today'    => $views_today,
			'views_7_days'   => $views_7_days,
			'total_views'    => $total_views,
			'ai_views_7_days' => $ai_views_7_days,
			'ai_share_7_days' => $views_7_days > 0 ? round(($ai_views_7_days / $views_7_days) * 100, 1) : 0,
			'top_content'    => $top_content,
			'top_campaigns' => self::normalize_campaign_rows($top_campaigns),
			'top_sources'   => self::normalize_source_groups($top_sources),
			'top_ai_sources' => $top_ai_sources,
		);

		wp_cache_set($cache_key, $data, 'a360_insights', 5 * MINUTE_IN_SECONDS);

		return $data;
	}
	/**
	 * Get diagnostics data for settings screen.
	 *
	 * @return array
	 */
	public static function get_diagnostics()
	{
		global $wpdb;

		$table_name       = self::table_name();
		$engagement_table = self::engagement_table_name();
		$sessions_table   = self::sessions_table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics checks plugin table existence.
		$table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table_name)) === $table_name;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics checks engagement table existence.
		$engagement_table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $engagement_table)) === $engagement_table;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics checks sessions table existence.
		$sessions_table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $sessions_table)) === $sessions_table;

		$diagnostics = array(
			'table_exists'         => $table_exists,
			'table_name'           => $table_name,
			'engagement_table_exists' => $engagement_table_exists,
			'engagement_table_name' => $engagement_table,
			'sessions_table_exists' => $sessions_table_exists,
			'sessions_table_name'  => $sessions_table,
			'db_version'           => get_option('a360_insights_db_version', ''),
			'total_events'         => 0,
			'engagement_events'    => 0,
			'sessions'             => 0,
			'engaged_sessions'     => 0,
			'last_event'           => '',
			'ai_events'            => 0,
			'google_profile_events' => 0,
			'referrer_debug_rows'  => self::get_recent_referrer_debug_rows(12),
			'recent_engagement_events' => self::get_recent_engagement_events(8),
		);

		if (! $table_exists) {
			return $diagnostics;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics count from plugin table.
		$diagnostics['total_events'] = (int) $wpdb->get_var('SELECT COUNT(*) FROM ' . esc_sql($table_name));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics latest event from plugin table.
		$diagnostics['last_event'] = (string) $wpdb->get_var('SELECT MAX(viewed_at) FROM ' . esc_sql($table_name));

		if ($engagement_table_exists) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics engagement count from plugin table.
			$diagnostics['engagement_events'] = (int) $wpdb->get_var('SELECT COUNT(*) FROM ' . esc_sql($engagement_table));
		}

		if ($sessions_table_exists) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics session count from plugin table.
			$diagnostics['sessions'] = (int) $wpdb->get_var('SELECT COUNT(*) FROM ' . esc_sql($sessions_table));
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics engaged session count from plugin table.
			$diagnostics['engaged_sessions'] = (int) $wpdb->get_var('SELECT COUNT(*) FROM ' . esc_sql($sessions_table) . ' WHERE is_engaged = 1');
		}

		$ai_sources = self::ai_sources();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics AI count from plugin table.
		$diagnostics['ai_events'] = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM ' . esc_sql($table_name) . '
		WHERE source IN ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )',
				$ai_sources[0],
				$ai_sources[1],
				$ai_sources[2],
				$ai_sources[3],
				$ai_sources[4],
				$ai_sources[5],
				$ai_sources[6],
				$ai_sources[7],
				$ai_sources[8],
				$ai_sources[9],
				$ai_sources[10],
				$ai_sources[11],
				$ai_sources[12],
				$ai_sources[13]
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics count from plugin table.
		$diagnostics['google_profile_events'] = (int) $wpdb->get_var(
			'SELECT COUNT(*) FROM ' . esc_sql($table_name) . " WHERE source IN ('google_business_profile', 'google_business_profile_candidate')"
		);

		return $diagnostics;
	}

	/**
	 * Get recent full referrer diagnostics rows.
	 *
	 * @param int $limit Number of rows.
	 * @return array
	 */
	public static function get_recent_referrer_debug_rows($limit = 12)
	{
		global $wpdb;

		$table_name = self::referrer_debug_table_name();
		$limit      = max(1, min(50, absint($limit)));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics table existence check.
		if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table_name)) !== $table_name) {
			return array();
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- Reads recent temporary diagnostics rows.
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT viewed_at, post_id, source, referrer_host, full_referrer, request_path
				FROM ' . esc_sql($table_name) . '
				ORDER BY id DESC
				LIMIT %d',
				$limit
			),
			ARRAY_A
		);
	}

	/**
	 * Get recent engagement events.
	 *
	 * @param int $limit Number of rows.
	 * @return array
	 */
	public static function get_recent_engagement_events($limit = 8)
	{
		global $wpdb;

		$table_name = self::engagement_table_name();
		$limit      = max(1, min(50, absint($limit)));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Diagnostics table existence check.
		if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table_name)) !== $table_name) {
			return array();
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- Reads recent anonymous engagement events.
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT event_at, post_id, event_type, event_value, event_label, source, referrer_host, request_path
				FROM ' . esc_sql($table_name) . '
				ORDER BY id DESC
				LIMIT %d',
				$limit
			),
			ARRAY_A
		);
	}

	/**
	 * Reset views for a single post.
	 *
	 * @param int $post_id Post ID.
	 * @return int|false Number of deleted rows or false.
	 */
	public static function reset_post_views($post_id)
	{
		global $wpdb;

		$post_id    = absint($post_id);
		$table_name = self::table_name();
		$engagement_table = self::engagement_table_name();
		$sessions_table = self::sessions_table_name();

		if (! $post_id) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes rows from custom plugin table on explicit admin reset.
		$deleted = $wpdb->delete(
			$table_name,
			array('post_id' => $post_id),
			array('%d')
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes anonymous engagement rows for explicit post reset.
		$wpdb->delete(
			$engagement_table,
			array('post_id' => $post_id),
			array('%d')
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes anonymous sessions that landed on this post for explicit post reset.
		$wpdb->delete(
			$sessions_table,
			array('landing_post_id' => $post_id),
			array('%d')
		);

		delete_post_meta($post_id, '_a360_insights_total_views');
		self::clear_cache_for_post($post_id);
		wp_cache_delete('dashboard_summary', 'a360_insights');

		return $deleted;
	}

	/**
	 * Reset all stored views.
	 *
	 * @return int|false Number of deleted rows or false.
	 */
	public static function reset_all_views()
	{
		global $wpdb;

		$table_name = self::table_name();
		$debug_table = self::referrer_debug_table_name();
		$engagement_table = self::engagement_table_name();
		$sessions_table = self::sessions_table_name();

		$deleted = $wpdb->query('DELETE FROM ' . esc_sql($table_name)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Deletes all rows from custom plugin table on explicit admin reset.

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Clears temporary full referrer diagnostics on explicit admin reset.
		$wpdb->query('DELETE FROM ' . esc_sql($debug_table));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Clears anonymous engagement events on explicit admin reset.
		$wpdb->query('DELETE FROM ' . esc_sql($engagement_table));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Clears anonymous sessions on explicit admin reset.
		$wpdb->query('DELETE FROM ' . esc_sql($sessions_table));

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes plugin post meta on explicit admin reset.
		$wpdb->delete(
			$wpdb->postmeta,
			array('meta_key' => '_a360_insights_total_views'),
			array('%s')
		);

		if (function_exists('wp_cache_flush_group')) {
			wp_cache_flush_group('a360_insights');
		} else {
			wp_cache_delete('admin_counts', 'a360_insights');
		}

		wp_cache_delete('dashboard_summary', 'a360_insights');

		return $deleted;
	}

	/**
	 * Clear cached values for post.
	 *
	 * @param int $post_id Post ID.
	 * @return void
	 */
	private static function clear_cache_for_post($post_id)
	{
		$post_id = absint($post_id);

		foreach (array('day', 'week', 'month', 'year') as $range) {
			wp_cache_delete(self::cache_key($post_id) . '_' . $range . '_' . current_time('Y-m-d'), 'a360_insights');
		}
	}

	/**
	 * Get export rows.
	 *
	 * @param int $post_id Post ID.
	 * @return array
	 */
	public static function get_export_rows($post_id)
	{
		global $wpdb;

		$table_name = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reads export rows from custom plugin table.
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT viewed_at, source, referrer_host, utm_source, utm_medium, utm_campaign
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				ORDER BY viewed_at DESC
				LIMIT 5000',
				absint($post_id)
			),
			ARRAY_A
		);
	}

	/**
	 * Sanitize range.
	 *
	 * @param string $range Range.
	 * @return string
	 */
	private static function sanitize_range($range)
	{
		$range = sanitize_key($range);
		return in_array($range, array('day', 'week', 'month', 'year'), true) ? $range : 'month';
	}

	/**
	 * Sanitize anchor date.
	 *
	 * @param string $anchor Anchor date.
	 * @return string
	 */
	private static function sanitize_anchor_date($anchor)
	{
		$anchor = sanitize_text_field((string) $anchor);

		if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $anchor)) {
			return $anchor;
		}

		return current_time('Y-m-d');
	}

	/**
	 * Date bounds.
	 *
	 * @param string $range Range.
	 * @param string $anchor Anchor date.
	 * @return array
	 */
	private static function date_bounds($range, $anchor = '', $offset = 0)
	{
		$timezone = wp_timezone();
		$range    = sanitize_key($range);
		$offset   = absint($offset);

		$now = new DateTimeImmutable(
			self::sanitize_anchor_date($anchor) . ' 12:00:00',
			$timezone
		);

		switch ($range) {
			case 'day':
				$start = $now->setTime(0, 0, 0)->modify('-' . $offset . ' days');
				$end   = $start->modify('+1 day');
				break;

			case 'week':
				$end   = $now->setTime(0, 0, 0)->modify('+1 day')->modify('-' . ($offset * 7) . ' days');
				$start = $end->modify('-7 days');
				break;

			case 'year':
				$end   = $now->setTime(0, 0, 0)->modify('+1 day')->modify('-' . ($offset * 365) . ' days');
				$start = $end->modify('-365 days');
				break;

			case 'month':
			default:
				$end   = $now->setTime(0, 0, 0)->modify('+1 day')->modify('-' . ($offset * 30) . ' days');
				$start = $end->modify('-30 days');
				break;
		}

		return array(
			'start' => $start->format('Y-m-d H:i:s'),
			'end'   => $end->format('Y-m-d H:i:s'),
		);
	}

	/**
	 * Build a human readable label for the selected period.
	 *
	 * @param string $range Range.
	 * @param array  $bounds Date bounds.
	 * @return string
	 */
	private static function period_label($range, array $bounds)
	{
		$timezone = wp_timezone();
		$start    = new DateTimeImmutable($bounds['start'], $timezone);
		$end      = new DateTimeImmutable($bounds['end'], $timezone);

		switch ($range) {
			case 'day':
				return wp_date(get_option('date_format'), $start->getTimestamp(), $timezone);

			case 'week':
				$last_day = $end->modify('-1 day');
				return sprintf(
					/* translators: 1: start date, 2: end date. */
					__('%1$s – %2$s', 'a360-insights'),
					wp_date(get_option('date_format'), $start->getTimestamp(), $timezone),
					wp_date(get_option('date_format'), $last_day->getTimestamp(), $timezone)
				);

			case 'year':
				return wp_date('Y', $start->getTimestamp(), $timezone);

			case 'month':
			default:
				return wp_date('F Y', $start->getTimestamp(), $timezone);
		}
	}

	/**
	 * Check whether the next period is not in the future.
	 *
	 * @param array $bounds Date bounds.
	 * @return bool
	 */
	private static function can_go_next(array $bounds)
	{
		$timezone = wp_timezone();
		$now      = new DateTimeImmutable('now', $timezone);
		$end      = new DateTimeImmutable($bounds['end'], $timezone);

		return $end < $now;
	}

	/**
	 * Normalize chart rows and fill missing buckets with zeroes.
	 *
	 * @param array  $rows Rows.
	 * @param string $range Range.
	 * @param array  $bounds Date bounds.
	 * @return array
	 */
	private static function normalize_chart_rows(array $rows, $range, array $bounds)
	{
		$found = array();

		foreach ($rows as $row) {
			$found[sanitize_text_field($row['label'])] = absint($row['views']);
		}

		$labels = array();
		$values = array();
		$start  = new DateTimeImmutable($bounds['start'], wp_timezone());
		$end    = new DateTimeImmutable($bounds['end'], wp_timezone());

		switch ($range) {
			case 'day':
				$interval = new DateInterval('PT1H');
				$format   = 'Y-m-d H:00';
				break;

			case 'year':
				$interval = new DateInterval('P1M');
				$format   = 'Y-m';
				break;

			case 'week':
			case 'month':
			default:
				$interval = new DateInterval('P1D');
				$format   = 'Y-m-d';
				break;
		}

		for ($cursor = $start; $cursor < $end; $cursor = $cursor->add($interval)) {
			$label    = $cursor->format($format);
			$labels[] = $label;
			$values[] = isset($found[$label]) ? absint($found[$label]) : 0;
		}

		return array(
			'labels' => $labels,
			'values' => $values,
		);
	}

	/**
	 * Normalize top referrers and group social redirect hosts.
	 *
	 * @param array $rows Rows.
	 * @return array
	 */
	private static function normalize_top_referrers(array $rows)
	{
		$grouped = array();

		foreach ($rows as $row) {
			$label = self::normalize_source_label($row['label'] ?? '', $row['source'] ?? 'direct');

			if (! isset($grouped[$label])) {
				$grouped[$label] = 0;
			}

			$grouped[$label] += absint($row['views'] ?? 0);
		}

		arsort($grouped);

		$normalized = array();

		foreach (array_slice($grouped, 0, 10, true) as $label => $views) {
			$normalized[] = array(
				'label' => $label,
				'views' => $views,
			);
		}

		return $normalized;
	}

	/**
	 * Normalize grouped traffic channels.
	 *
	 * @param array $rows Rows.
	 * @return array
	 */
	private static function normalize_source_groups(array $rows)
	{
		$labels = array(
			'direct'         => __('Direct', 'a360-insights'),
			'ai_traffic'     => __('AI Traffic', 'a360-insights'),
			'google_business_profile' => __('Google Business Profile', 'a360-insights'),
			'google_business_profile_candidate' => __('Likely Google Business Profile', 'a360-insights'),
			'organic_search' => __('Organic Search', 'a360-insights'),
			'referral'       => __('Referral', 'a360-insights'),
			'social'         => __('Social', 'a360-insights'),
			'campaign'       => __('Campaign', 'a360-insights'),
			'unknown'        => __('Unknown', 'a360-insights'),
		);

		$normalized = array();

		foreach ($rows as $row) {
			$key = sanitize_key($row['channel'] ?? 'unknown');

			if (! isset($labels[$key])) {
				$key = 'unknown';
			}

			$normalized[] = array(
				'key'   => $key,
				'label' => $labels[$key],
				'views' => absint($row['views'] ?? 0),
			);
		}

		return $normalized;
	}

	/**
	 * Normalize UTM campaign rows.
	 *
	 * @param array $rows Rows.
	 * @return array
	 */
	private static function normalize_campaign_rows(array $rows)
	{
		$normalized = array();

		foreach ($rows as $row) {
			$normalized[] = array(
				'campaign' => sanitize_text_field($row['utm_campaign'] ?? ''),
				'source'   => sanitize_text_field($row['utm_source'] ?? ''),
				'medium'   => sanitize_text_field($row['utm_medium'] ?? ''),
				'views'    => absint($row['views'] ?? 0),
			);
		}

		return $normalized;
	}

	/**
	 * Normalize recent rows for display.
	 *
	 * @param array $rows Rows.
	 * @return array
	 */
	private static function normalize_recent_rows(array $rows)
	{
		foreach ($rows as $index => $row) {
			$rows[$index]['label'] = self::normalize_source_label($row['referrer_host'] ?? '', $row['source'] ?? 'direct');
		}

		return $rows;
	}

	/**
	 * Normalize engagement rows.
	 *
	 * @param array $rows  Rows.
	 * @param int   $total Total page views.
	 * @return array
	 */
	private static function normalize_engagement_rows(array $rows, $total)
	{
		$scroll = array();
		$events = array();

		foreach ($rows as $row) {
			$event_type = sanitize_key($row['event_type'] ?? '');
			$count      = absint($row['events'] ?? 0);

			if (0 === $count) {
				continue;
			}

			if (0 === strpos($event_type, 'scroll_')) {
				$scroll[absint($row['event_value'] ?? 0)] = $count;
				continue;
			}

			if (! isset($events[$event_type])) {
				$events[$event_type] = 0;
			}

			$events[$event_type] += $count;
		}

		$total      = max(0, absint($total));
		$thresholds = array(25, 50, 75, 90);
		$result     = array();

		foreach ($thresholds as $threshold) {
			$count = $scroll[$threshold] ?? 0;

			$result[] = array(
				'label'     => 'Scroll ' . $threshold . '%',
				'threshold' => $threshold,
				'events'    => $count,
				'percent'   => $total > 0 ? round(($count / $total) * 100, 1) : 0,
			);
		}

		$event_labels = array(
			'outbound_click' => __('Outbound clicks', 'a360-insights'),
			'file_download'  => __('File downloads', 'a360-insights'),
			'phone_click'    => __('Phone clicks', 'a360-insights'),
			'email_click'    => __('Email clicks', 'a360-insights'),
			'form_start'     => __('Form starts', 'a360-insights'),
			'form_submit'    => __('Form submits', 'a360-insights'),
		);

		foreach ($event_labels as $event_type => $label) {
			$count = $events[$event_type] ?? 0;

			$result[] = array(
				'label'      => $label,
				'event_type' => $event_type,
				'events'     => $count,
				'percent'    => $total > 0 ? round(($count / $total) * 100, 1) : 0,
			);
		}

		return $result;
	}

	/**
	 * Build a simple landing page funnel from anonymous sessions and engagement events.
	 *
	 * @param int   $post_id Post ID.
	 * @param array $bounds  Date bounds.
	 * @return array
	 */
	private static function get_content_funnel($post_id, array $bounds)
	{
		global $wpdb;

		$post_id          = absint($post_id);
		$table_name       = self::table_name();
		$engagement_table = self::engagement_table_name();

		if (! $post_id) {
			return array();
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counts anonymous sessions for funnel steps.
		$session_rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT session_hash, COUNT(*) AS views
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				AND viewed_at >= %s
				AND viewed_at < %s
				AND session_hash <> ""
				GROUP BY session_hash',
				$post_id,
				$bounds['start'],
				$bounds['end']
			),
			ARRAY_A
		);

		$sessions = array();

		foreach ($session_rows as $row) {
			$hash = (string) ($row['session_hash'] ?? '');

			if ('' === $hash) {
				continue;
			}

			$sessions[$hash] = array(
				'viewed'       => true,
				'scroll_75'    => false,
				'cta_click'    => false,
				'form_start'   => false,
				'form_submit'  => false,
			);
		}

		if (empty($sessions)) {
			return self::normalize_funnel_counts(array(), 0);
		}

		$hashes = array_keys($sessions);
		$placeholders = implode(', ', array_fill(0, count($hashes), '%s'));
		$event_query_values = array_merge(array($post_id, $bounds['start'], $bounds['end']), $hashes);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Placeholder list is generated from session hash count and values are prepared.
		$event_rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT session_hash, event_type
				FROM ' . esc_sql($engagement_table) . '
				WHERE post_id = %d
				AND event_at >= %s
				AND event_at < %s
				AND session_hash IN (' . $placeholders . ')',
				$event_query_values
			),
			ARRAY_A
		);

		foreach ($event_rows as $row) {
			$hash       = (string) ($row['session_hash'] ?? '');
			$event_type = sanitize_key($row['event_type'] ?? '');

			if (! isset($sessions[$hash])) {
				continue;
			}

			if ('scroll_75' === $event_type || 'scroll_90' === $event_type) {
				$sessions[$hash]['scroll_75'] = true;
			}

			if (in_array($event_type, array('outbound_click', 'phone_click', 'email_click', 'file_download'), true)) {
				$sessions[$hash]['cta_click'] = true;
			}

			if ('form_start' === $event_type) {
				$sessions[$hash]['form_start'] = true;
			}

			if ('form_submit' === $event_type) {
				$sessions[$hash]['form_submit'] = true;
				$sessions[$hash]['form_start']  = true;
			}
		}

		$counts = array(
			'viewed'      => count($sessions),
			'scroll_75'   => 0,
			'cta_click'   => 0,
			'form_start'  => 0,
			'form_submit' => 0,
		);

		foreach ($sessions as $session) {
			foreach (array('scroll_75', 'cta_click', 'form_start', 'form_submit') as $step) {
				if (! empty($session[$step])) {
					$counts[$step]++;
				}
			}
		}

		return self::normalize_funnel_counts($counts, count($sessions));
	}

	/**
	 * Count configured key events for content.
	 *
	 * @param int   $post_id  Post ID.
	 * @param array $bounds   Date bounds.
	 * @param int   $sessions Total sessions.
	 * @return array
	 */
	private static function get_content_conversions($post_id, array $bounds, $sessions = 0)
	{
		global $wpdb;

		$post_id = absint($post_id);
		$key_events = array_values(array_intersect(
			(array) A360_Insights_Settings::get_value('key_events', array('phone_click', 'email_click', 'form_submit')),
			A360_Insights_Settings::available_key_events()
		));

		if (! $post_id || empty($key_events)) {
			return array(
				'total'    => 0,
				'sessions' => 0,
				'events'   => array(),
			);
		}

		$table_name   = self::engagement_table_name();
		$placeholders = implode(', ', array_fill(0, count($key_events), '%s'));
		$query_values = array_merge(array($post_id, $bounds['start'], $bounds['end']), $key_events);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Key event placeholder list is generated from sanitized configured event keys.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT event_type, COUNT(*) AS events, COUNT(DISTINCT NULLIF(session_hash, "")) AS sessions
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				AND event_at >= %s
				AND event_at < %s
				AND event_type IN (' . $placeholders . ')
				GROUP BY event_type
				ORDER BY events DESC',
				$query_values
			),
			ARRAY_A
		);

		$total_events = 0;
		$event_labels = self::conversion_event_labels();
		$normalized = array();

		foreach ($rows as $row) {
			$event_type = sanitize_key($row['event_type'] ?? '');
			$events     = absint($row['events'] ?? 0);
			$event_sessions = absint($row['sessions'] ?? 0);

			$total_events += $events;

			$normalized[] = array(
				'event_type' => $event_type,
				'label'      => $event_labels[$event_type] ?? ucwords(str_replace('_', ' ', $event_type)),
				'events'     => $events,
				'sessions'   => $event_sessions,
				'rate'       => $sessions > 0 ? round(($event_sessions / $sessions) * 100, 1) : 0,
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Key event placeholder list is generated from sanitized configured event keys.
		$total_sessions = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(DISTINCT NULLIF(session_hash, ""))
				FROM ' . esc_sql($table_name) . '
				WHERE post_id = %d
				AND event_at >= %s
				AND event_at < %s
				AND event_type IN (' . $placeholders . ')',
				$query_values
			)
		);

		return array(
			'total'    => $total_events,
			'sessions' => $total_sessions,
			'events'   => $normalized,
		);
	}

	/**
	 * Labels for conversion event reports.
	 *
	 * @return array
	 */
	private static function conversion_event_labels()
	{
		return array(
			'outbound_click' => __('Outbound clicks', 'a360-insights'),
			'file_download'  => __('File downloads', 'a360-insights'),
			'phone_click'    => __('Phone clicks', 'a360-insights'),
			'email_click'    => __('Email clicks', 'a360-insights'),
			'form_start'     => __('Form starts', 'a360-insights'),
			'form_submit'    => __('Form submits', 'a360-insights'),
			'scroll_75'      => __('Scroll 75%', 'a360-insights'),
			'scroll_90'      => __('Scroll 90%', 'a360-insights'),
		);
	}

	/**
	 * Normalize funnel counts for UI.
	 *
	 * @param array $counts Step counts.
	 * @param int   $total  Total sessions.
	 * @return array
	 */
	private static function normalize_funnel_counts(array $counts, $total)
	{
		$total = max(0, absint($total));
		$steps = array(
			'viewed' => __('Viewed landing page', 'a360-insights'),
			'scroll_75' => __('Scrolled 75%+', 'a360-insights'),
			'cta_click' => __('Clicked CTA / link', 'a360-insights'),
			'form_start' => __('Started form', 'a360-insights'),
			'form_submit' => __('Submitted form', 'a360-insights'),
		);
		$result = array();

		foreach ($steps as $key => $label) {
			$count = absint($counts[$key] ?? 0);

			$result[] = array(
				'key'     => $key,
				'label'   => $label,
				'count'   => $count,
				'percent' => $total > 0 ? round(($count / $total) * 100, 1) : 0,
			);
		}

		return $result;
	}

	/**
	 * Normalize source label.
	 *
	 * @param string $host Host or label.
	 * @param string $source Source.
	 * @return string
	 */
	private static function normalize_source_label($host, $source = 'direct')
	{
		$host   = strtolower(trim((string) $host));
		$source = strtolower(trim((string) $source));

		if ('' === $host || 'direct' === $host) {
			return $source ? ucfirst($source) : 'Direct';
		}

		$host = preg_replace('/^www\./', '', $host);

		$site_host = wp_parse_url(home_url(), PHP_URL_HOST);
		$site_host = is_string($site_host) ? strtolower(preg_replace('/^www\./', '', $site_host)) : '';

		if ('internal' === $source || ($site_host && ($host === $site_host || substr($host, -1 * (strlen($site_host) + 1)) === '.' . $site_host))) {
			return 'Direct';
		}

		if ('google_business_profile' === $source) {
			return 'Google Business Profile';
		}

		if ('google_business_profile_candidate' === $source) {
			return 'Likely Google Business Profile';
		}

		if (preg_match('/(^|\.)facebook\.com$|^fb\.com$/', $host)) {
			return 'Facebook';
		}

		if (preg_match('/(^|\.)youtube\.com$|^youtu\.be$/', $host)) {
			return 'YouTube';
		}

		if (preg_match('/(^|\.)twitter\.com$|(^|\.)x\.com$|^t\.co$/', $host)) {
			return 'X';
		}

		if (preg_match('/(^|\.)linkedin\.com$/', $host)) {
			return 'LinkedIn';
		}

		if (preg_match('/(^|\.)instagram\.com$/', $host)) {
			return 'Instagram';
		}

		return $host;
	}
}