<?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';
	}

	/**
	 * 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 '',
			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)
		) {$charset_collate};";

		dbDelta($sql);
		self::create_or_update_referrer_debug_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);
	}

	/**
	 * 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 ''",
		);

		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 = ''");
		}
	}

	/**
	 * 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;
	}

	/**
	 * 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();
		$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.
		return $wpdb->query(
			$wpdb->prepare(
				'DELETE FROM ' . esc_sql($table_name) . ' WHERE viewed_at < %s',
				$threshold
			)
		);
	}
	/**
	 * 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 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();

		// 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']
			)
		);
		$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,
			'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,
		);

		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();

		// 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;

		$diagnostics = array(
			'table_exists'         => $table_exists,
			'table_name'           => $table_name,
			'db_version'           => get_option('a360_insights_db_version', ''),
			'total_events'         => 0,
			'last_event'           => '',
			'ai_events'            => 0,
			'google_profile_events' => 0,
			'referrer_debug_rows'  => self::get_recent_referrer_debug_rows(12),
		);

		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));

		$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
		);
	}

	/**
	 * 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();

		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')
		);

		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();

		$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 -- 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 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;
	}
}