<?php
if (!defined('ABSPATH')) {
	exit;
}

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
/**
 * Handles frontend audio tracking and admin analytics.
 */
class Podcastmaker_Audio_Analytics
{
	private $events_table;
	private $library_table;

	public function __construct()
	{
		global $wpdb;
		$this->events_table = self::events_table_name();
		$this->library_table = self::library_table_name();

		add_action('wp_enqueue_scripts', array($this, 'enqueue_tracking_script'));
		add_action('wp_ajax_podcastmaker_track_audio_event', array($this, 'ajax_track_audio_event'));
		add_action('wp_ajax_nopriv_podcastmaker_track_audio_event', array($this, 'ajax_track_audio_event'));
		add_action('admin_menu', array($this, 'add_admin_menu'));
		add_action('admin_init', array($this, 'maybe_download_raw_logs_csv'));
		add_action('admin_enqueue_scripts', array($this, 'enqueue_admin_assets'));
	}

	public static function events_table_name()
	{
		global $wpdb;
		return $wpdb->prefix . 'podcastmaker_audio_events';
	}

	public static function library_table_name()
	{
		global $wpdb;
		return $wpdb->prefix . 'podcastmaker_audio_library';
	}

	/**
	 * Sanitize a stored speaker map JSON string.
	 *
	 * @param mixed $speaker_map_json The raw JSON payload.
	 * @return string
	 */
	private static function sanitize_speaker_map_json($speaker_map_json)
	{
		if (is_array($speaker_map_json)) {
			$decoded_map = $speaker_map_json;
		}
		elseif (is_string($speaker_map_json) && $speaker_map_json !== '') {
			$decoded_map = json_decode($speaker_map_json, true);
		}
		else {
			$decoded_map = array();
		}

		if (!is_array($decoded_map)) {
			return wp_json_encode(array());
		}

		$sanitized_map = array();
		foreach ($decoded_map as $speaker_name => $voice_name) {
			$normalized_speaker_name = trim(preg_replace('/[:：]+/u', '', sanitize_text_field((string) $speaker_name)));
			$normalized_voice_name = sanitize_text_field((string) $voice_name);
			if ($normalized_speaker_name === '' || $normalized_voice_name === '') {
				continue;
			}
			$sanitized_map[$normalized_speaker_name] = $normalized_voice_name;
		}

		return wp_json_encode($sanitized_map);
	}

	/**
	 * Decode a speaker map JSON string into an array.
	 *
	 * @param mixed $speaker_map_json The raw JSON payload.
	 * @return array<string, string>
	 */
	private static function decode_speaker_map_json($speaker_map_json)
	{
		$sanitized_json = self::sanitize_speaker_map_json($speaker_map_json);
		$decoded_map = json_decode($sanitized_json, true);
		return is_array($decoded_map) ? $decoded_map : array();
	}

	/**
	 * Count the speakers stored in a speaker map JSON string.
	 *
	 * @param mixed $speaker_map_json The raw JSON payload.
	 * @return int
	 */
	private static function count_speakers_in_map_json($speaker_map_json)
	{
		return count(self::decode_speaker_map_json($speaker_map_json));
	}

	public static function register_audio_library_entry($data)
	{
		global $wpdb;

		$audio_url = isset($data['audio_url']) ? esc_url_raw($data['audio_url']) : '';
		if (empty($audio_url)) {
			return false;
		}

		$audio_key = md5($audio_url);
		$table_name = self::library_table_name();
		$attachment_id = isset($data['attachment_id']) ? absint($data['attachment_id']) : 0;
		$script_post_id = isset($data['script_post_id']) ? absint($data['script_post_id']) : 0;
		$source_post_id = isset($data['source_post_id']) ? absint($data['source_post_id']) : 0;
		$audio_mode = isset($data['audio_mode']) && $data['audio_mode'] === 'multi' ? 'multi' : 'single';
		$voice_id = isset($data['voice_id']) ? sanitize_text_field($data['voice_id']) : '';
		$voice_label = isset($data['voice_label']) ? sanitize_text_field($data['voice_label']) : $voice_id;
		$voice_summary_label = isset($data['voice_summary_label']) ? sanitize_text_field($data['voice_summary_label']) : $voice_label;
		$speaker_map_json = isset($data['speaker_map_json']) ? wp_unslash($data['speaker_map_json']) : '';
		$speaker_map_json = self::sanitize_speaker_map_json($speaker_map_json);
		$speaker_count = isset($data['speaker_count']) ? absint($data['speaker_count']) : self::count_speakers_in_map_json($speaker_map_json);
		if ($speaker_count <= 0) {
			$speaker_count = $audio_mode === 'multi' ? max(2, self::count_speakers_in_map_json($speaker_map_json)) : 1;
		}

		$payload = array(
			'audio_key' => $audio_key,
			'audio_url' => $audio_url,
			'attachment_id' => $attachment_id,
			'script_post_id' => $script_post_id,
			'source_post_id' => $source_post_id,
			'audio_mode' => $audio_mode,
			'voice_id' => $voice_id,
			'voice_label' => $voice_label,
			'voice_summary_label' => $voice_summary_label,
			'speaker_map_json' => $speaker_map_json,
			'speaker_count' => $speaker_count,
			'updated_at' => current_time('mysql'),
		);

		$formats = array('%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s');

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$existing = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$table_name} WHERE audio_key = %s", $audio_key), ARRAY_A);

		if ($existing) {
			$update = array();
			if ($attachment_id > 0 || empty($existing['attachment_id'])) {
				$update['attachment_id'] = $attachment_id;
			}
			if ($script_post_id > 0 || empty($existing['script_post_id'])) {
				$update['script_post_id'] = $script_post_id;
			}
			if ($source_post_id > 0 || empty($existing['source_post_id'])) {
				$update['source_post_id'] = $source_post_id;
			}
			if (!empty($audio_mode) || empty($existing['audio_mode'])) {
				$update['audio_mode'] = $audio_mode;
			}
			if (!empty($voice_id) || empty($existing['voice_id'])) {
				$update['voice_id'] = $voice_id;
			}
			if (!empty($voice_label) || empty($existing['voice_label'])) {
				$update['voice_label'] = $voice_label;
			}
			if (!empty($voice_summary_label) || empty($existing['voice_summary_label'])) {
				$update['voice_summary_label'] = $voice_summary_label;
			}
			if (!empty($speaker_map_json) || empty($existing['speaker_map_json'])) {
				$update['speaker_map_json'] = $speaker_map_json;
			}
			if ($speaker_count > 0 || empty($existing['speaker_count'])) {
				$update['speaker_count'] = $speaker_count;
			}
			$update['audio_url'] = $audio_url;
			$update['updated_at'] = current_time('mysql');

			if (!empty($update)) {
				$update_formats = array();
				foreach (array_keys($update) as $field) {
					$update_formats[] = in_array($field, array('attachment_id', 'script_post_id', 'source_post_id', 'speaker_count'), true) ? '%d' : '%s';
				}
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
				return false !== $wpdb->update($table_name, $update, array('audio_key' => $audio_key), $update_formats, array('%s'));
			}
			return true;
		}

		$payload['created_at'] = current_time('mysql');
		$formats[] = '%s';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		return false !== $wpdb->insert($table_name, $payload, $formats);
	}

	public function add_admin_menu()
	{
		add_submenu_page(
			'Podcastmaker',
			esc_html__('Audio Analytics', 'quikvox-ai'),
			esc_html__('Audio Analytics', 'quikvox-ai'),
			'edit_posts',
			$this->get_admin_page_slug(),
			array($this, 'render_admin_page'),
			30
		);
	}

	private function get_admin_page_slug()
	{
		return 'podcastmaker_audio_analytics';
	}

	private function is_analytics_admin_page()
	{
		if (!is_admin()) {
			return false;
		}

		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$page = isset($_GET['page']) ? sanitize_text_field(wp_unslash($_GET['page'])) : '';
		return $page === $this->get_admin_page_slug();
	}

	public function enqueue_admin_assets()
	{
		if (!$this->is_analytics_admin_page()) {
			return;
		}

		wp_enqueue_style(
			'podcastmaker-admin-css',
			plugin_dir_url(dirname(__FILE__)) . 'assets/css/admin.css',
			array(),
			PODCASTMAKER_VERSION
		);

		wp_enqueue_script(
			'podcastmaker-chart-js',
			plugin_dir_url(dirname(__FILE__)) . 'assets/vendor/chartjs/chart.umd.min.js',
			array(),
			'4.4.0',
			true
		);

		wp_enqueue_script(
			'podcastmaker-audio-analytics-admin',
			plugin_dir_url(dirname(__FILE__)) . 'assets/js/audio-analytics-admin.js',
			array('podcastmaker-chart-js'),
			PODCASTMAKER_VERSION,
			true
		);

		wp_localize_script(
			'podcastmaker-audio-analytics-admin',
			'PodcastmakerAudioAnalyticsAdmin',
			$this->get_dashboard_chart_config($this->get_selected_period_days())
		);
	}

	public function enqueue_tracking_script()
	{
		if (is_admin() || !is_singular()) {
			return;
		}

		$post = get_queried_object();
		if (!$post instanceof WP_Post || !$this->post_has_audio($post)) {
			return;
		}

		wp_enqueue_script(
			'podcastmaker-audio-analytics',
			plugin_dir_url(dirname(__FILE__)) . 'assets/js/audio-analytics.js',
			array(),
			PODCASTMAKER_VERSION,
			true
		);

		wp_localize_script('podcastmaker-audio-analytics', 'PodcastmakerAudioAnalytics', array(
			'ajaxUrl' => admin_url('admin-ajax.php'),
			'action' => 'podcastmaker_track_audio_event',
			'nonce' => wp_create_nonce('podcastmaker_track_audio'),
			'postId' => (int) $post->ID,
			'generatedPrefix' => 'podcast_audio_',
		));
	}

	private function post_has_audio(WP_Post $post)
	{
		$content = (string) $post->post_content;
		return strpos($content, '<audio') !== false || has_block('core/audio', $post);
	}

	public function ajax_track_audio_event()
	{
		check_ajax_referer('podcastmaker_track_audio', 'nonce');
		$event_type = isset($_POST['event_type']) ? sanitize_key(wp_unslash($_POST['event_type'])) : '';
		$allowed_event_types = array('start', 'progress', 'checkpoint', 'complete');
		if (!in_array($event_type, $allowed_event_types, true)) {
			wp_send_json_error(array('message' => 'Invalid event type.'), 400);
		}

		$audio_url = isset($_POST['audio_url']) ? esc_url_raw(wp_unslash($_POST['audio_url'])) : '';
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
		$playback_session_id = isset($_POST['playback_session_id']) ? $this->sanitize_public_id(wp_unslash($_POST['playback_session_id'])) : '';
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
		$visitor_id = isset($_POST['visitor_id']) ? $this->sanitize_public_id(wp_unslash($_POST['visitor_id'])) : '';

		if (empty($audio_url) || empty($playback_session_id) || (empty($visitor_id) && !is_user_logged_in())) {
			wp_send_json_error(array('message' => 'Missing tracking payload.'), 400);
		}

		$post_id = isset($_POST['post_id']) ? absint($_POST['post_id']) : 0;
		$audio_index = isset($_POST['audio_index']) ? absint($_POST['audio_index']) : 0;
		$progress_bucket = isset($_POST['progress_bucket']) ? absint($_POST['progress_bucket']) : 0;
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
		$progress_percent = isset($_POST['progress_percent']) ? (float) wp_unslash($_POST['progress_percent']) : 0.0;
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
		$duration_seconds = isset($_POST['duration_seconds']) ? (float) wp_unslash($_POST['duration_seconds']) : 0.0;

		$progress_percent = max(0.0, min(100.0, $progress_percent));
		$duration_seconds = max(0.0, $duration_seconds);
		$progress_bucket = max(0, min(100, $progress_bucket));

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$wpdb->insert(
			$this->events_table,
			array(
				'playback_session_id' => $playback_session_id,
				'visitor_id' => $visitor_id,
				'user_id' => get_current_user_id(),
				'post_id' => $post_id,
				'audio_key' => md5($audio_url),
				'audio_url' => $audio_url,
				'audio_index' => $audio_index,
				'event_type' => $event_type,
				'progress_bucket' => $progress_bucket,
				'progress_percent' => $progress_percent,
				'duration_seconds' => $duration_seconds,
				'created_at' => current_time('mysql'),
			),
			array('%s', '%s', '%d', '%d', '%s', '%s', '%d', '%s', '%d', '%f', '%f', '%s')
		);

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

	private function sanitize_public_id($value)
	{
		$value = sanitize_text_field($value);
		return preg_replace('/[^a-zA-Z0-9_\-]/', '', $value);
	}

	private function now_timestamp()
	{
		return current_time('timestamp');
	}

	private function sql_datetime_from_offset_days($days)
	{
		return wp_date('Y-m-d H:i:s', $this->now_timestamp() - (DAY_IN_SECONDS * $days));
	}

	private function listener_expr()
	{
		return "CASE WHEN events.user_id > 0 THEN CONCAT('u:', events.user_id) ELSE CONCAT('v:', events.visitor_id) END";
	}

	private function get_period_metrics($start, $end = null)
	{
		global $wpdb;

		if ($end !== null) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$unique_listeners = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT CASE WHEN events.user_id > 0 THEN CONCAT('u:', events.user_id) ELSE CONCAT('v:', events.visitor_id) END) FROM {$this->events_table} AS events WHERE events.event_type = 'start' AND events.created_at >= %s AND events.created_at < %s",
				$start,
				$end
			)
		);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$total_plays = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(DISTINCT events.playback_session_id) FROM {$this->events_table} AS events WHERE events.event_type = 'start' AND events.created_at >= %s AND events.created_at < %s",
					$start,
					$end
				)
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$summary = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT COUNT(*) AS session_count,
						AVG(max_progress) AS avg_progress,
						SUM(CASE WHEN max_progress >= 90 THEN 1 ELSE 0 END) AS completion_90_count
					FROM (
						SELECT events.playback_session_id, MAX(events.progress_percent) AS max_progress
						FROM {$this->events_table} AS events
						WHERE events.created_at >= %s AND events.created_at < %s
						GROUP BY events.playback_session_id
					) AS session_summary",
					$start,
					$end
				)
			);
		}
		else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$unique_listeners = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT CASE WHEN events.user_id > 0 THEN CONCAT('u:', events.user_id) ELSE CONCAT('v:', events.visitor_id) END) FROM {$this->events_table} AS events WHERE events.event_type = 'start' AND events.created_at >= %s",
				$start
			)
		);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$total_plays = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(DISTINCT events.playback_session_id) FROM {$this->events_table} AS events WHERE events.event_type = 'start' AND events.created_at >= %s",
					$start
				)
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$summary = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT COUNT(*) AS session_count,
						AVG(max_progress) AS avg_progress,
						SUM(CASE WHEN max_progress >= 90 THEN 1 ELSE 0 END) AS completion_90_count
					FROM (
						SELECT events.playback_session_id, MAX(events.progress_percent) AS max_progress
						FROM {$this->events_table} AS events
						WHERE events.created_at >= %s
						GROUP BY events.playback_session_id
					) AS session_summary",
					$start
				)
			);
		}

		$session_count = $summary ? (int) $summary->session_count : 0;
		$completion_90_count = $summary ? (int) $summary->completion_90_count : 0;
		$average_play_rate = $summary && $summary->avg_progress !== null ? round((float) $summary->avg_progress, 1) : 0.0;
		$completion_90_rate = $session_count > 0 ? round(($completion_90_count / $session_count) * 100, 1) : 0.0;

		return array(
			'total_plays' => $total_plays,
			'unique_listeners' => $unique_listeners,
			'completion_90_rate' => $completion_90_rate,
			'average_play_rate' => $average_play_rate,
		);
	}

	private function get_delta($current, $previous)
	{
		if ((float) $previous === 0.0) {
			return 0.0;
		}
		return round((($current - $previous) / $previous) * 100, 1);
	}

	private function get_daily_bucket_stats($days = 7)
	{
		global $wpdb;

		$start = $this->sql_datetime_from_offset_days(max(0, $days - 1));
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_results($wpdb->prepare(
			"SELECT stat_date,
				COUNT(*) AS total_plays,
				SUM(CASE WHEN max_progress < 30 THEN 1 ELSE 0 END) AS bucket_under_30,
				SUM(CASE WHEN max_progress >= 30 AND max_progress < 60 THEN 1 ELSE 0 END) AS bucket_30_59,
				SUM(CASE WHEN max_progress >= 60 AND max_progress < 90 THEN 1 ELSE 0 END) AS bucket_60_89,
				SUM(CASE WHEN max_progress >= 90 THEN 1 ELSE 0 END) AS bucket_90_plus
			FROM (
				SELECT playback_session_id,
					DATE(MIN(created_at)) AS stat_date,
					MAX(progress_percent) AS max_progress
				FROM {$this->events_table}
				WHERE created_at >= %s
				GROUP BY playback_session_id
			) AS session_summary
			GROUP BY stat_date
			ORDER BY stat_date ASC",
			$start
		), ARRAY_A);
	}

	private function compare_sort_values($left, $right, $order)
	{
		if ($left === $right) {
			return 0;
		}
		if ($order === 'asc') {
			return ($left < $right) ? -1 : 1;
		}
		return ($left > $right) ? -1 : 1;
	}

	private function sort_audio_performance_rows(&$rows, $sort, $order)
	{
		usort($rows, function ($left, $right) use ($sort, $order) {
			$sort_map = array(
				'plays' => 'total_plays',
				'reach_30' => 'reach_30_rate',
				'reach_60' => 'reach_60_rate',
				'reach_90' => 'reach_90_rate',
				'avg' => 'average_play_rate',
			);
			$field = isset($sort_map[$sort]) ? $sort_map[$sort] : 'total_plays';
			$comparison = $this->compare_sort_values((float) $left[$field], (float) $right[$field], $order);
			if ($comparison !== 0) {
				return $comparison;
			}
			$plays_compare = $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], 'desc');
			if ($plays_compare !== 0) {
				return $plays_compare;
			}
			return $this->compare_sort_values((float) $left['reach_90_rate'], (float) $right['reach_90_rate'], 'desc');
		});
	}

	private function get_audio_performance_rows($days = 30, $limit = 5, $min_plays = 3, $sort = 'plays', $order = 'desc')
	{
		global $wpdb;

		$start = $this->sql_datetime_from_offset_days(max(0, $days - 1));
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sessions = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT events.playback_session_id,
					MAX(events.post_id) AS post_id,
					events.audio_key,
					MAX(events.audio_url) AS audio_url,
					MAX(events.progress_percent) AS max_progress
				FROM {$this->events_table} AS events
				WHERE events.created_at >= %s
				GROUP BY events.playback_session_id, events.audio_key",
				$start
			),
			ARRAY_A
		);
		if (empty($sessions)) {
			return array();
		}

		$grouped = array();
		foreach ($sessions as $session) {
			$key = $session['post_id'] . '|' . $session['audio_key'];
			if (!isset($grouped[$key])) {
				$grouped[$key] = array(
					'post_id' => (int) $session['post_id'],
					'audio_key' => $session['audio_key'],
					'audio_url' => $session['audio_url'],
					'total_plays' => 0,
					'reach_30_count' => 0,
					'reach_60_count' => 0,
					'reach_90_count' => 0,
					'progress_sum' => 0.0,
				);
			}

			$max_progress = (float) $session['max_progress'];
			$grouped[$key]['total_plays']++;
			$grouped[$key]['progress_sum'] += $max_progress;
			if ($max_progress >= 30) {
				$grouped[$key]['reach_30_count']++;
			}
			if ($max_progress >= 60) {
				$grouped[$key]['reach_60_count']++;
			}
			if ($max_progress >= 90) {
				$grouped[$key]['reach_90_count']++;
			}
		}

		$rows = array();
		foreach ($grouped as $row) {
			if ($row['total_plays'] < $min_plays) {
				continue;
			}
			$row['average_play_rate'] = $row['total_plays'] > 0 ? $row['progress_sum'] / $row['total_plays'] : 0.0;
			$row['completion_90_rate'] = $row['total_plays'] > 0 ? ($row['reach_90_count'] / $row['total_plays']) * 100 : 0.0;
			$row['reach_30_rate'] = $row['total_plays'] > 0 ? ($row['reach_30_count'] / $row['total_plays']) * 100 : 0.0;
			$row['reach_60_rate'] = $row['total_plays'] > 0 ? ($row['reach_60_count'] / $row['total_plays']) * 100 : 0.0;
			$row['reach_90_rate'] = $row['completion_90_rate'];
			unset($row['progress_sum']);
			$rows[] = $row;
		}

		$this->sort_audio_performance_rows($rows, $sort, $order);

		$rows = array_slice($rows, 0, $limit);
		return $this->hydrate_audio_rows($rows);
	}

	private function get_daily_rollups($days = 30, $limit = 12, $sort = 'date', $order = 'desc')
	{
		global $wpdb;

		$start = $this->sql_datetime_from_offset_days(max(0, $days - 1));
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sessions = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(MIN(events.created_at)) AS stat_date,
					MAX(events.post_id) AS post_id,
					events.audio_key,
					MAX(events.audio_url) AS audio_url,
					MAX(events.progress_percent) AS max_progress
				FROM {$this->events_table} AS events
				WHERE events.created_at >= %s
				GROUP BY events.playback_session_id, events.audio_key",
				$start
			),
			ARRAY_A
		);
		if (empty($sessions)) {
			return array();
		}

		$grouped = array();
		foreach ($sessions as $session) {
			$key = $session['stat_date'] . '|' . $session['post_id'] . '|' . $session['audio_key'];
			if (!isset($grouped[$key])) {
				$grouped[$key] = array(
					'stat_date' => $session['stat_date'],
					'post_id' => (int) $session['post_id'],
					'audio_key' => $session['audio_key'],
					'audio_url' => $session['audio_url'],
					'total_plays' => 0,
					'bucket_under_30' => 0,
					'bucket_30_59' => 0,
					'bucket_60_89' => 0,
					'bucket_90_plus' => 0,
				);
			}

			$max_progress = (float) $session['max_progress'];
			$grouped[$key]['total_plays']++;
			if ($max_progress < 30) {
				$grouped[$key]['bucket_under_30']++;
			} elseif ($max_progress < 60) {
				$grouped[$key]['bucket_30_59']++;
			} elseif ($max_progress < 90) {
				$grouped[$key]['bucket_60_89']++;
			} else {
				$grouped[$key]['bucket_90_plus']++;
			}
		}

		$rows = array_values($grouped);
		usort($rows, function ($left, $right) use ($sort, $order) {
			if ($sort === 'plays') {
				$play_compare = $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], $order);
				if ($play_compare !== 0) {
					return $play_compare;
				}
				return strcmp($right['stat_date'], $left['stat_date']);
			}

			$date_compare = $order === 'asc'
				? strcmp($left['stat_date'], $right['stat_date'])
				: strcmp($right['stat_date'], $left['stat_date']);
			if ($date_compare !== 0) {
				return $date_compare;
			}
			return $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], 'desc');
		});

		$rows = array_slice($rows, 0, $limit);
		return $this->hydrate_audio_rows($rows);
	}

	private function get_voice_rankings($days = 30, $limit = 10, $sort = 'plays', $order = 'desc')
	{
		$audio_rows = $this->get_audio_performance_rows($days, 500, 1, 'plays', 'desc');
		if (empty($audio_rows)) {
			return array();
		}

		$voices = array();
		foreach ($audio_rows as $audio_row) {
			$voice_name = !empty($audio_row['voice_label']) ? $audio_row['voice_label'] : 'Unknown Voice';
			if (!isset($voices[$voice_name])) {
				$voices[$voice_name] = array(
					'voice_name' => $voice_name,
					'voice_id' => isset($audio_row['voice_id']) ? $audio_row['voice_id'] : '',
					'total_plays' => 0,
					'progress_weighted_sum' => 0.0,
					'completion_weighted_sum' => 0.0,
				);
			}

			$plays = (int) $audio_row['total_plays'];
			$voices[$voice_name]['total_plays'] += $plays;
			$voices[$voice_name]['progress_weighted_sum'] += ((float) $audio_row['average_play_rate']) * $plays;
			$voices[$voice_name]['completion_weighted_sum'] += ((float) $audio_row['completion_90_rate']) * $plays;
		}

		$rows = array();
		foreach ($voices as $voice_row) {
			$total_plays = max(1, (int) $voice_row['total_plays']);
			$rows[] = array(
				'voice_name' => $voice_row['voice_name'],
				'voice_id' => $voice_row['voice_id'],
				'total_plays' => $voice_row['total_plays'],
				'average_play_rate' => round($voice_row['progress_weighted_sum'] / $total_plays, 1),
				'completion_90_rate' => round($voice_row['completion_weighted_sum'] / $total_plays, 1),
				'reach_90_rate' => round($voice_row['completion_weighted_sum'] / $total_plays, 1),
			);
		}

		usort($rows, function ($left, $right) use ($sort, $order) {
			$sort_map = array(
				'plays' => 'total_plays',
				'reach_90' => 'completion_90_rate',
				'avg' => 'average_play_rate',
			);
			$field = isset($sort_map[$sort]) ? $sort_map[$sort] : 'total_plays';
			$comparison = $this->compare_sort_values((float) $left[$field], (float) $right[$field], $order);
			if ($comparison !== 0) {
				return $comparison;
			}
			return $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], 'desc');
		});

		return array_slice($rows, 0, $limit);
	}

	private function hydrate_audio_rows($rows)
	{
		foreach ($rows as &$row) {
			$row = $this->resolve_audio_context($row);
			$source_post_id = !empty($row['source_post_id']) ? (int) $row['source_post_id'] : (int) $row['post_id'];
			$row['source_post_id'] = $source_post_id;
			$row['post_title'] = $source_post_id ? get_the_title($source_post_id) : 'Unknown post';
			$row['post_link'] = $source_post_id ? get_permalink($source_post_id) : '';
			$row['post_edit_link'] = $source_post_id ? get_edit_post_link($source_post_id, '') : '';
			$row['audio_label'] = $this->format_audio_label($row['audio_url'], $row['audio_key']);
			$row['audio_link'] = $row['audio_url'];
			$row['media_edit_link'] = !empty($row['attachment_id']) ? get_edit_post_link((int) $row['attachment_id'], '') : '';
			$row['audio_mode'] = isset($row['audio_mode']) && $row['audio_mode'] === 'multi' ? 'multi' : 'single';
			$row['voice_label'] = !empty($row['voice_label']) ? $row['voice_label'] : (!empty($row['voice_id']) ? $row['voice_id'] : 'Unknown Voice');
			$row['voice_summary_label'] = !empty($row['voice_summary_label']) ? $row['voice_summary_label'] : $row['voice_label'];
			$row['speaker_map_json'] = self::sanitize_speaker_map_json(isset($row['speaker_map_json']) ? $row['speaker_map_json'] : '');
			$row['speaker_count'] = isset($row['speaker_count']) ? (int) $row['speaker_count'] : self::count_speakers_in_map_json($row['speaker_map_json']);
			if (isset($row['total_plays'])) {
				$total_plays = max(1, (int) $row['total_plays']);
				$row['reach_30_rate'] = isset($row['reach_30_count']) ? round(((int) $row['reach_30_count'] / $total_plays) * 100, 1) : 0.0;
				$row['reach_60_rate'] = isset($row['reach_60_count']) ? round(((int) $row['reach_60_count'] / $total_plays) * 100, 1) : 0.0;
				$row['reach_90_rate'] = isset($row['reach_90_count']) ? round(((int) $row['reach_90_count'] / $total_plays) * 100, 1) : 0.0;
			}
			if (isset($row['average_play_rate'])) {
				$row['average_play_rate'] = round((float) $row['average_play_rate'], 1);
			}
			if (isset($row['completion_90_rate'])) {
				$row['completion_90_rate'] = round((float) $row['completion_90_rate'], 1);
			}
		}
		return $rows;
	}

	private function resolve_audio_context($row)
	{
		$audio_url = isset($row['audio_url']) ? $row['audio_url'] : '';
		$post_id = isset($row['post_id']) ? (int) $row['post_id'] : 0;
		$library = $this->find_or_build_library_entry($audio_url, $post_id);
		if (!$library) {
			return $row;
		}

		$row['attachment_id'] = !empty($row['attachment_id']) ? $row['attachment_id'] : $library['attachment_id'];
		$row['source_post_id'] = !empty($row['source_post_id']) ? $row['source_post_id'] : $library['source_post_id'];
		$row['audio_mode'] = !empty($row['audio_mode']) ? $row['audio_mode'] : $library['audio_mode'];
		$row['voice_id'] = !empty($row['voice_id']) ? $row['voice_id'] : $library['voice_id'];
		$row['voice_label'] = !empty($row['voice_label']) ? $row['voice_label'] : $library['voice_label'];
		$row['voice_summary_label'] = !empty($row['voice_summary_label']) ? $row['voice_summary_label'] : $library['voice_summary_label'];
		$row['speaker_map_json'] = !empty($row['speaker_map_json']) ? $row['speaker_map_json'] : $library['speaker_map_json'];
		$row['speaker_count'] = !empty($row['speaker_count']) ? $row['speaker_count'] : $library['speaker_count'];

		return $row;
	}

	private function find_or_build_library_entry($audio_url, $post_id = 0)
	{
		if (empty($audio_url)) {
			return null;
		}

		global $wpdb;
		$audio_key = md5($audio_url);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$existing = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$this->library_table} WHERE audio_key = %s", $audio_key), ARRAY_A);
		if ($existing && !empty($existing['source_post_id']) && (!empty($existing['voice_id']) || !empty($existing['voice_summary_label']) || !empty($existing['speaker_map_json']))) {
			return $existing;
		}

		$resolved = array(
			'audio_url' => $audio_url,
			'attachment_id' => $existing ? (int) $existing['attachment_id'] : 0,
			'script_post_id' => $existing ? (int) $existing['script_post_id'] : 0,
			'source_post_id' => $existing ? (int) $existing['source_post_id'] : 0,
			'audio_mode' => $existing && isset($existing['audio_mode']) && $existing['audio_mode'] === 'multi' ? 'multi' : 'single',
			'voice_id' => $existing ? (string) $existing['voice_id'] : '',
			'voice_label' => $existing ? (string) $existing['voice_label'] : '',
			'voice_summary_label' => $existing && isset($existing['voice_summary_label']) ? (string) $existing['voice_summary_label'] : '',
			'speaker_map_json' => $existing && isset($existing['speaker_map_json']) ? (string) $existing['speaker_map_json'] : wp_json_encode(array()),
			'speaker_count' => $existing && isset($existing['speaker_count']) ? (int) $existing['speaker_count'] : 1,
		);

		if ($resolved['attachment_id'] <= 0) {
			$resolved['attachment_id'] = attachment_url_to_postid($audio_url);
		}

		if ($resolved['source_post_id'] <= 0 && $post_id > 0) {
			$resolved['source_post_id'] = $post_id;
		}

		$script_match = $this->find_script_by_audio_url($audio_url);
		if ($script_match) {
			if ($resolved['script_post_id'] <= 0) {
				$resolved['script_post_id'] = (int) $script_match['script_post_id'];
			}
			if ($resolved['source_post_id'] <= 0 && !empty($script_match['source_post_id'])) {
				$resolved['source_post_id'] = (int) $script_match['source_post_id'];
			}
			if (isset($script_match['audio_mode']) && $script_match['audio_mode'] === 'multi') {
				$resolved['audio_mode'] = 'multi';
			}
			if (empty($resolved['voice_id']) && !empty($script_match['voice_id'])) {
				$resolved['voice_id'] = (string) $script_match['voice_id'];
			}
			if (empty($resolved['voice_summary_label']) && !empty($script_match['voice_summary_label'])) {
				$resolved['voice_summary_label'] = (string) $script_match['voice_summary_label'];
			}
			if (empty($resolved['speaker_map_json']) && !empty($script_match['speaker_map_json'])) {
				$resolved['speaker_map_json'] = (string) $script_match['speaker_map_json'];
			}
		}

		if ($resolved['source_post_id'] <= 0) {
			$resolved['source_post_id'] = $this->find_source_post_by_audio_url($audio_url);
		}

		if (empty($resolved['voice_label']) && !empty($resolved['voice_id'])) {
			$resolved['voice_label'] = $resolved['voice_id'];
		}
		if (empty($resolved['voice_summary_label'])) {
			$resolved['voice_summary_label'] = !empty($resolved['voice_label']) ? $resolved['voice_label'] : 'Unknown Voice';
		}
		$resolved['speaker_map_json'] = self::sanitize_speaker_map_json($resolved['speaker_map_json']);
		$resolved['speaker_count'] = max(1, self::count_speakers_in_map_json($resolved['speaker_map_json']));

		if (
			$resolved['attachment_id'] > 0
			|| $resolved['script_post_id'] > 0
			|| $resolved['source_post_id'] > 0
			|| !empty($resolved['voice_id'])
			|| !empty($resolved['voice_summary_label'])
		) {
			self::register_audio_library_entry($resolved);
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$stored = $wpdb->get_row($wpdb->prepare("SELECT * FROM {$this->library_table} WHERE audio_key = %s", $audio_key), ARRAY_A);
			return $stored ? $stored : $resolved;
		}

		return $existing ? $existing : null;
	}

	private function find_script_by_audio_url($audio_url)
	{
		global $wpdb;

		$postmeta = $wpdb->postmeta;
		$posts = $wpdb->posts;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_row($wpdb->prepare("
			SELECT meta_audio.post_id AS script_post_id,
				meta_source.meta_value AS source_post_id,
				meta_voice.meta_value AS voice_id,
				meta_mode.meta_value AS audio_mode,
				meta_voice_summary.meta_value AS voice_summary_label,
				meta_speaker_map.meta_value AS speaker_map_json
			FROM {$postmeta} AS meta_audio
			INNER JOIN {$posts} AS script_post ON script_post.ID = meta_audio.post_id
			LEFT JOIN {$postmeta} AS meta_source ON meta_source.post_id = meta_audio.post_id AND meta_source.meta_key = '_Podcastmaker_source_post_id'
			LEFT JOIN {$postmeta} AS meta_voice ON meta_voice.post_id = meta_audio.post_id AND meta_voice.meta_key = '_Podcastmaker_voice_id'
			LEFT JOIN {$postmeta} AS meta_mode ON meta_mode.post_id = meta_audio.post_id AND meta_mode.meta_key = '_Podcastmaker_script_mode'
			LEFT JOIN {$postmeta} AS meta_voice_summary ON meta_voice_summary.post_id = meta_audio.post_id AND meta_voice_summary.meta_key = '_Podcastmaker_voice_summary_label'
			LEFT JOIN {$postmeta} AS meta_speaker_map ON meta_speaker_map.post_id = meta_audio.post_id AND meta_speaker_map.meta_key = '_Podcastmaker_multi_speaker_map'
			WHERE meta_audio.meta_key = '_Podcastmaker_audio_url'
				AND meta_audio.meta_value = %s
				AND script_post.post_type = 'podcastmaker_script'
			LIMIT 1
		", $audio_url), ARRAY_A);
	}

	private function find_source_post_by_audio_url($audio_url)
	{
		global $wpdb;

		$posts = $wpdb->posts;
		$like = '%' . $wpdb->esc_like($audio_url) . '%';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->get_var($wpdb->prepare("
			SELECT ID
			FROM {$posts}
			WHERE post_type IN ('post', 'page')
				AND post_status IN ('publish', 'draft', 'future', 'private')
				AND post_content LIKE %s
			ORDER BY post_status = 'publish' DESC, ID DESC
			LIMIT 1
		", $like));
	}

	private function format_audio_label($audio_url, $audio_key)
	{
		$path = (string) wp_parse_url($audio_url, PHP_URL_PATH);
		$basename = $path ? wp_basename($path) : '';
		if (!empty($basename)) {
			return $basename;
		}
		return 'Audio ' . substr((string) $audio_key, 0, 8);
	}

	public function maybe_download_raw_logs_csv()
	{
		if (!is_admin() || !current_user_can('edit_posts')) {
			return;
		}

		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$export = isset($_GET['export']) ? sanitize_key(wp_unslash($_GET['export'])) : '';
		if (!$this->is_analytics_admin_page() || !in_array($export, array('raw_csv', 'csv'), true)) {
			return;
		}

		check_admin_referer('podcastmaker_export_csv');

		$this->download_raw_logs_csv($this->get_selected_period_days());
		exit;
	}

	private function get_raw_event_rows($days)
	{
		global $wpdb;

		$start = $this->sql_datetime_from_offset_days(max(0, $days - 1));
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT *
				FROM {$this->events_table}
				WHERE created_at >= %s
				ORDER BY created_at DESC, id DESC",
				$start
			),
			ARRAY_A
		);
		if (empty($rows)) {
			return array();
		}

		foreach ($rows as &$row) {
			$row = $this->resolve_audio_context($row);
			$source_post_id = !empty($row['source_post_id']) ? (int) $row['source_post_id'] : (int) $row['post_id'];
			$row['post_title'] = $source_post_id ? get_the_title($source_post_id) : '';
			$row['post_link'] = $source_post_id ? get_permalink($source_post_id) : '';
			$row['audio_label'] = $this->format_audio_label($row['audio_url'], $row['audio_key']);
			$row['voice_label'] = !empty($row['voice_summary_label']) ? $row['voice_summary_label'] : (!empty($row['voice_label']) ? $row['voice_label'] : (!empty($row['voice_id']) ? $row['voice_id'] : 'Unknown Voice'));
		}

		return $rows;
	}

	private function download_raw_logs_csv($days)
	{
		$rows = $this->get_raw_event_rows($days);
		$filename = sprintf('audio-analytics-%s.csv', wp_date('Ymd-His'));

		nocache_headers();
		header('Content-Type: text/csv; charset=UTF-8');
		header('Content-Disposition: attachment; filename=' . $filename);

		$output = fopen('php://output', 'w');
		if ($output === false) {
			return;
		}

		fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));

		fputcsv($output, array(
			'id',
			'created_at',
			'event_type',
			'playback_session_id',
			'visitor_id',
			'user_id',
			'post_id',
			'post_title',
			'post_link',
			'audio_key',
			'audio_url',
			'audio_label',
			'audio_mode',
			'voice_id',
			'voice_label',
			'voice_summary_label',
			'speaker_map_json',
			'speaker_count',
			'audio_index',
			'progress_bucket',
			'progress_percent',
			'duration_seconds',
		));

		foreach ($rows as $row) {
			fputcsv($output, array(
				isset($row['id']) ? (int) $row['id'] : 0,
				isset($row['created_at']) ? $row['created_at'] : '',
				isset($row['event_type']) ? $row['event_type'] : '',
				isset($row['playback_session_id']) ? $row['playback_session_id'] : '',
				isset($row['visitor_id']) ? $row['visitor_id'] : '',
				isset($row['user_id']) ? (int) $row['user_id'] : 0,
				isset($row['post_id']) ? (int) $row['post_id'] : 0,
				isset($row['post_title']) ? $row['post_title'] : '',
				isset($row['post_link']) ? $row['post_link'] : '',
				isset($row['audio_key']) ? $row['audio_key'] : '',
				isset($row['audio_url']) ? $row['audio_url'] : '',
				isset($row['audio_label']) ? $row['audio_label'] : '',
				isset($row['audio_mode']) ? $row['audio_mode'] : 'single',
				isset($row['voice_id']) ? $row['voice_id'] : '',
				isset($row['voice_label']) ? $row['voice_label'] : '',
				isset($row['voice_summary_label']) ? $row['voice_summary_label'] : '',
				isset($row['speaker_map_json']) ? $row['speaker_map_json'] : '',
				isset($row['speaker_count']) ? (int) $row['speaker_count'] : 1,
				isset($row['audio_index']) ? (int) $row['audio_index'] : 0,
				isset($row['progress_bucket']) ? (int) $row['progress_bucket'] : 0,
				isset($row['progress_percent']) ? (float) $row['progress_percent'] : 0,
				isset($row['duration_seconds']) ? (float) $row['duration_seconds'] : 0,
			));
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		fclose($output);
	}

	private function get_selected_period_days()
	{
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$period = isset($_GET['period']) ? absint(wp_unslash($_GET['period'])) : 30;
		return in_array($period, array(7, 30, 90), true) ? $period : 30;
	}

	private function get_sort_param($param_name, $allowed, $default)
	{
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$value = isset($_GET[$param_name]) ? sanitize_key(wp_unslash($_GET[$param_name])) : $default;
		return in_array($value, $allowed, true) ? $value : $default;
	}

	private function get_order_param($param_name, $default = 'desc')
	{
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended
		$value = isset($_GET[$param_name]) ? sanitize_key(wp_unslash($_GET[$param_name])) : $default;
		return in_array($value, array('asc', 'desc'), true) ? $value : $default;
	}

	private function build_admin_url($overrides = array())
	{
		$params = array(
			'page' => $this->get_admin_page_slug(),
			'period' => $this->get_selected_period_days(),
		);

		foreach ($overrides as $key => $value) {
			if ($value === null) {
				unset($params[$key]);
				continue;
			}
			$params[$key] = $value;
		}

		return add_query_arg($params, admin_url('admin.php'));
	}

	private function get_sort_toggle_url($sort_param, $order_param, $target_sort, $current_sort, $current_order)
	{
		$next_order = ($current_sort === $target_sort && $current_order === 'desc') ? 'asc' : 'desc';
		return $this->build_admin_url(array(
			$sort_param => $target_sort,
			$order_param => $next_order,
		));
	}

	private function render_sortable_header($label, $sort_param, $order_param, $target_sort, $current_sort, $current_order)
	{
		$classes = array('Podcastmaker-sort-link');
		$indicator = '';
		if ($current_sort === $target_sort) {
			$classes[] = 'is-active';
			$classes[] = $current_order === 'asc' ? 'is-asc' : 'is-desc';
			$indicator = $current_order === 'asc' ? ' ↑' : ' ↓';
		}

		return sprintf(
			'<a class="%1$s" href="%2$s">%3$s%4$s</a>',
			esc_attr(implode(' ', $classes)),
			esc_url($this->get_sort_toggle_url($sort_param, $order_param, $target_sort, $current_sort, $current_order)),
			esc_html($label),
			esc_html($indicator)
		);
	}

	private function get_dashboard_period_range($days)
	{
		$today = (new DateTimeImmutable('now', wp_timezone()))->setTime(0, 0, 0);
		$start = $today->modify('-' . max(0, $days - 1) . ' days');
		$end = $today->modify('+1 day');
		$previous_start = $start->modify('-' . $days . ' days');
		$previous_end = $start;
		$display_end = $end->modify('-1 day');

		return array(
			'start_sql' => $start->format('Y-m-d H:i:s'),
			'end_sql' => $end->format('Y-m-d H:i:s'),
			'previous_start_sql' => $previous_start->format('Y-m-d H:i:s'),
			'previous_end_sql' => $previous_end->format('Y-m-d H:i:s'),
			'start_label' => $start->format('Y-m-d'),
			'end_label' => $display_end->format('Y-m-d'),
			'label' => $start->format('Y-m-d') . ' – ' . $display_end->format('Y-m-d'),
		);
	}

	private function get_dashboard_cache_key($days, $range)
	{
		return 'podcastmaker_audio_dashboard_' . md5($days . '|' . $range['start_sql'] . '|' . $range['end_sql']);
	}

	private function get_dashboard_chart_config($days)
	{
		$dashboard = $this->get_dashboard_bundle($days);

		return array(
			'trend' => $dashboard['trend_chart'],
			'hours' => $dashboard['hour_chart'],
			'voiceScatter' => $dashboard['voice_scatter_chart'],
		);
	}

	private function get_dashboard_bundle($days)
	{
		$range = $this->get_dashboard_period_range($days);
		$cache_key = $this->get_dashboard_cache_key($days, $range);
		$cached = get_transient($cache_key);
		if (is_array($cached)) {
			return $cached;
		}

		$current_metrics = $this->get_dashboard_metrics($range['start_sql'], $range['end_sql']);
		$previous_metrics = $this->get_dashboard_metrics($range['previous_start_sql'], $range['previous_end_sql']);
		$audio_rows = $this->get_dashboard_audio_rows($range['start_sql'], $range['end_sql']);
		$voice_rows = $this->build_dashboard_voice_rows($audio_rows);
		$qualified_voice_rows = $this->get_dashboard_qualified_voice_rows($voice_rows);
		$post_rows = $this->get_dashboard_post_rows($range['start_sql'], $range['end_sql'], $audio_rows);

		$dashboard = array(
			'range' => $range,
			'current_metrics' => $current_metrics,
			'previous_metrics' => $previous_metrics,
			'kpi_rows' => $this->get_dashboard_kpi_rows($current_metrics, $previous_metrics, $days),
			'retention_rows' => $this->get_dashboard_retention_rows($current_metrics),
			'trend_chart' => $this->get_dashboard_trend_chart_data($range['start_sql'], $range['end_sql'], $range),
			'hour_chart' => $this->get_dashboard_hour_chart_data($range['start_sql'], $range['end_sql']),
			'voice_scatter_chart' => $this->get_dashboard_voice_scatter_chart_data($voice_rows),
			'voice_rows' => $voice_rows,
			'voice_count' => count($voice_rows),
			'qualified_voice_count' => count($qualified_voice_rows),
			'top_voice_rows' => array_slice($qualified_voice_rows, 0, 10),
			'post_rows' => $post_rows,
			'post_count' => count($post_rows),
			'audio_count' => count($audio_rows),
			'has_data' => (int) $current_metrics['total_plays'] > 0,
		);

		set_transient($cache_key, $dashboard, 5 * MINUTE_IN_SECONDS);

		return $dashboard;
	}

	private function get_dashboard_session_summary_sql($start, $end)
	{
		global $wpdb;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->prepare(
			"SELECT
				events.playback_session_id,
				MAX(events.post_id) AS post_id,
				MAX(events.audio_key) AS audio_key,
				MAX(events.audio_url) AS audio_url,
				MAX(events.audio_index) AS audio_index,
				MAX(events.duration_seconds) AS duration_seconds,
				MAX(events.progress_percent) AS session_max_progress,
				MAX(CASE WHEN events.event_type = 'complete' THEN 1 ELSE 0 END) AS has_complete,
				MAX(CASE WHEN events.visitor_id <> '' THEN events.visitor_id ELSE NULL END) AS visitor_id
			FROM {$this->events_table} AS events
			WHERE events.created_at >= %s
				AND events.created_at < %s
			GROUP BY events.playback_session_id",
			$start,
			$end
		);
	}

	private function get_dashboard_metrics($start, $end)
	{
		global $wpdb;

		$session_sql = $this->get_dashboard_session_summary_sql($start, $end);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$row = $wpdb->get_row("SELECT
			COUNT(*) AS total_plays,
			COUNT(DISTINCT session_summary.visitor_id) AS unique_listeners,
			AVG(session_summary.session_max_progress) AS average_play_rate,
			SUM(session_summary.has_complete) AS completed_count,
			SUM(CASE WHEN session_summary.session_max_progress >= 30 THEN 1 ELSE 0 END) AS reach_30_count,
			SUM(CASE WHEN session_summary.session_max_progress >= 60 THEN 1 ELSE 0 END) AS reach_60_count,
			SUM(CASE WHEN session_summary.session_max_progress >= 90 THEN 1 ELSE 0 END) AS reach_90_count
		FROM ({$session_sql}) AS session_summary", ARRAY_A);

		$total_plays = isset($row['total_plays']) ? (int) $row['total_plays'] : 0;
		$completed_count = isset($row['completed_count']) ? (int) $row['completed_count'] : 0;
		$reach_30_count = isset($row['reach_30_count']) ? (int) $row['reach_30_count'] : 0;
		$reach_60_count = isset($row['reach_60_count']) ? (int) $row['reach_60_count'] : 0;
		$reach_90_count = isset($row['reach_90_count']) ? (int) $row['reach_90_count'] : 0;

		return array(
			'total_plays' => $total_plays,
			'unique_listeners' => isset($row['unique_listeners']) ? (int) $row['unique_listeners'] : 0,
			'completed_count' => $completed_count,
			'completion_rate' => $total_plays > 0 ? round(($completed_count / $total_plays) * 100, 1) : 0.0,
			'average_play_rate' => isset($row['average_play_rate']) && $row['average_play_rate'] !== null ? round((float) $row['average_play_rate'], 1) : 0.0,
			'reach_30_count' => $reach_30_count,
			'reach_30_rate' => $total_plays > 0 ? round(($reach_30_count / $total_plays) * 100, 1) : 0.0,
			'reach_60_count' => $reach_60_count,
			'reach_60_rate' => $total_plays > 0 ? round(($reach_60_count / $total_plays) * 100, 1) : 0.0,
			'reach_90_count' => $reach_90_count,
			'reach_90_rate' => $total_plays > 0 ? round(($reach_90_count / $total_plays) * 100, 1) : 0.0,
		);
	}

	private function get_dashboard_trend_chart_data($start, $end, $range)
	{
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					DATE(events.created_at) AS stat_date,
					COUNT(DISTINCT events.playback_session_id) AS play_count
				FROM {$this->events_table} AS events
				WHERE events.event_type = 'start'
					AND events.created_at >= %s
					AND events.created_at < %s
				GROUP BY DATE(events.created_at)
				ORDER BY stat_date ASC",
				$start,
				$end
			),
			ARRAY_A
		);
		$play_map = array();
		foreach ($rows as $row) {
			$play_map[$row['stat_date']] = (int) $row['play_count'];
		}

		$labels = array();
		$values = array();
		$current = new DateTimeImmutable($range['start_label'], wp_timezone());
		$end_date = new DateTimeImmutable($range['end_label'], wp_timezone());

		while ($current <= $end_date) {
			$key = $current->format('Y-m-d');
			$labels[] = $key;
			$values[] = isset($play_map[$key]) ? $play_map[$key] : 0;
			$current = $current->modify('+1 day');
		}

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

	private function get_dashboard_hour_chart_data($start, $end)
	{
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					HOUR(events.created_at) AS hour_of_day,
					COUNT(DISTINCT events.playback_session_id) AS play_count
				FROM {$this->events_table} AS events
				WHERE events.event_type = 'start'
					AND events.created_at >= %s
					AND events.created_at < %s
				GROUP BY HOUR(events.created_at)
				ORDER BY hour_of_day ASC",
				$start,
				$end
			),
			ARRAY_A
		);
		$hour_map = array();
		foreach ($rows as $row) {
			$hour_map[(int) $row['hour_of_day']] = (int) $row['play_count'];
		}

		$labels = array();
		$values = array();
		$sorted_hours = array();
		for ($hour = 0; $hour < 24; $hour++) {
			$labels[] = $hour . ':00';
			$values[] = isset($hour_map[$hour]) ? $hour_map[$hour] : 0;
			$sorted_hours[$hour] = isset($hour_map[$hour]) ? $hour_map[$hour] : 0;
		}

		arsort($sorted_hours);
		$peak_hours = array();
		foreach ($sorted_hours as $hour => $count) {
			if ($count <= 0) {
				continue;
			}

			$peak_hours[] = (int) $hour;
			if (count($peak_hours) === 3) {
				break;
			}
		}

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

	private function get_dashboard_audio_rows($start, $end)
	{
		global $wpdb;

		$session_sql = $this->get_dashboard_session_summary_sql($start, $end);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results("SELECT
			session_summary.post_id,
			session_summary.audio_key,
			MAX(session_summary.audio_url) AS audio_url,
			MAX(session_summary.audio_index) AS audio_index,
			COUNT(*) AS total_plays,
			COUNT(DISTINCT session_summary.visitor_id) AS unique_listeners,
			AVG(session_summary.duration_seconds) AS average_duration_seconds,
			AVG(session_summary.session_max_progress) AS average_play_rate,
			SUM(session_summary.has_complete) AS completed_count,
			SUM(CASE WHEN session_summary.session_max_progress >= 30 THEN 1 ELSE 0 END) AS reach_30_count,
			SUM(CASE WHEN session_summary.session_max_progress >= 60 THEN 1 ELSE 0 END) AS reach_60_count,
			SUM(CASE WHEN session_summary.session_max_progress >= 90 THEN 1 ELSE 0 END) AS reach_90_count
		FROM ({$session_sql}) AS session_summary
		GROUP BY session_summary.post_id, session_summary.audio_key
		ORDER BY total_plays DESC, session_summary.post_id DESC", ARRAY_A);
		if (empty($rows)) {
			return array();
		}

		$rows = $this->hydrate_audio_rows($rows);
		foreach ($rows as &$row) {
			$total_plays = max(1, (int) $row['total_plays']);
			$row['completion_rate'] = round((((int) $row['completed_count']) / $total_plays) * 100, 1);
			$row['average_duration_seconds'] = isset($row['average_duration_seconds']) ? round((float) $row['average_duration_seconds'], 2) : 0.0;
		}
		unset($row);

		return $rows;
	}

	private function build_dashboard_voice_rows($audio_rows)
	{
		if (empty($audio_rows)) {
			return array();
		}

		$voices = array();
		foreach ($audio_rows as $audio_row) {
			$voice_entries = $this->extract_voice_attribution_entries($audio_row);
			foreach ($voice_entries as $voice_entry) {
				$voice_id = $voice_entry['voice_id'];
				if (!isset($voices[$voice_id])) {
					$voices[$voice_id] = array(
						'voice_id' => $voice_id,
						'voice_name' => $voice_entry['voice_name'],
						'total_plays' => 0,
						'completed_count' => 0,
						'reach_30_count' => 0,
						'reach_60_count' => 0,
						'reach_90_count' => 0,
						'average_play_rate_weighted' => 0.0,
						'average_duration_weighted' => 0.0,
					);
				}

				$plays = (int) $audio_row['total_plays'];
				$voices[$voice_id]['total_plays'] += $plays;
				$voices[$voice_id]['completed_count'] += (int) $audio_row['completed_count'];
				$voices[$voice_id]['reach_30_count'] += (int) $audio_row['reach_30_count'];
				$voices[$voice_id]['reach_60_count'] += (int) $audio_row['reach_60_count'];
				$voices[$voice_id]['reach_90_count'] += (int) $audio_row['reach_90_count'];
				$voices[$voice_id]['average_play_rate_weighted'] += ((float) $audio_row['average_play_rate']) * $plays;
				$voices[$voice_id]['average_duration_weighted'] += ((float) $audio_row['average_duration_seconds']) * $plays;
			}
		}

		$rows = array();
		foreach ($voices as $voice_row) {
			$total_plays = max(1, (int) $voice_row['total_plays']);
			$rows[] = array(
				'voice_id' => $voice_row['voice_id'],
				'voice_name' => $voice_row['voice_name'],
				'total_plays' => (int) $voice_row['total_plays'],
				'completion_rate' => round(($voice_row['completed_count'] / $total_plays) * 100, 1),
				'reach_90_rate' => round(($voice_row['reach_90_count'] / $total_plays) * 100, 1),
				'reach_60_rate' => round(($voice_row['reach_60_count'] / $total_plays) * 100, 1),
				'reach_30_rate' => round(($voice_row['reach_30_count'] / $total_plays) * 100, 1),
				'average_play_rate' => round($voice_row['average_play_rate_weighted'] / $total_plays, 1),
				'average_duration_seconds' => round($voice_row['average_duration_weighted'] / $total_plays, 2),
			);
		}

		usort($rows, function ($left, $right) {
			$plays_compare = $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], 'desc');
			if ($plays_compare !== 0) {
				return $plays_compare;
			}

			return $this->compare_sort_values((float) $left['completion_rate'], (float) $right['completion_rate'], 'desc');
		});

		return $rows;
	}

	/**
	 * Expand one audio row into one or more voice attribution entries.
	 *
	 * @param array<string, mixed> $audio_row The hydrated audio row.
	 * @return array<int, array<string, string>>
	 */
	private function extract_voice_attribution_entries($audio_row)
	{
		$audio_mode = isset($audio_row['audio_mode']) && $audio_row['audio_mode'] === 'multi' ? 'multi' : 'single';
		if ($audio_mode === 'multi') {
			$speaker_map = self::decode_speaker_map_json(isset($audio_row['speaker_map_json']) ? $audio_row['speaker_map_json'] : '');
			$voice_entries = array();
			foreach ($speaker_map as $speaker_name => $voice_name) {
				$voice_id = sanitize_title($voice_name);
				if ($voice_id === '') {
					$voice_id = sanitize_title($speaker_name);
				}
				if ($voice_id === '') {
					$voice_id = 'unknown-voice';
				}

				$voice_entries[] = array(
					'voice_id' => $voice_id,
					'voice_name' => $voice_name,
				);
			}

			if (!empty($voice_entries)) {
				return $voice_entries;
			}
		}

		$voice_name = !empty($audio_row['voice_label']) ? $audio_row['voice_label'] : 'Unknown Voice';
		$voice_id = !empty($audio_row['voice_id']) ? $audio_row['voice_id'] : sanitize_title($voice_name);
		if ($voice_id === '') {
			$voice_id = 'unknown-voice';
		}

		return array(
			array(
				'voice_id' => $voice_id,
				'voice_name' => $voice_name,
			),
		);
	}

	private function get_dashboard_qualified_voice_rows($voice_rows)
	{
		$qualified = array_values(array_filter($voice_rows, function ($voice_row) {
			return (int) $voice_row['total_plays'] >= 5;
		}));

		usort($qualified, function ($left, $right) {
			$completion_compare = $this->compare_sort_values((float) $left['completion_rate'], (float) $right['completion_rate'], 'desc');
			if ($completion_compare !== 0) {
				return $completion_compare;
			}

			return $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], 'desc');
		});

		return $qualified;
	}

	private function get_dashboard_voice_scatter_chart_data($voice_rows)
	{
		$points = array();
		$play_values = array();
		$completion_values = array();

		foreach ($voice_rows as $voice_row) {
			$play_values[] = (int) $voice_row['total_plays'];
			$completion_values[] = (float) $voice_row['completion_rate'];
			$points[] = array(
				'name' => $voice_row['voice_name'],
				'plays' => (int) $voice_row['total_plays'],
				'completion' => round((float) $voice_row['completion_rate'], 1),
				'avgPlayRate' => round((float) $voice_row['average_play_rate'], 1),
			);
		}

		return array(
			'points' => $points,
			'medianPlays' => $this->get_dashboard_median_value($play_values),
			'medianCompletion' => $this->get_dashboard_median_value($completion_values),
			'labelsToShow' => $this->get_dashboard_voice_labels_to_show($voice_rows),
		);
	}

	private function get_dashboard_voice_labels_to_show($voice_rows)
	{
		if (empty($voice_rows)) {
			return array();
		}

		$top_by_plays = $voice_rows;
		usort($top_by_plays, function ($left, $right) {
			return $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], 'desc');
		});

		$bottom_by_completion = $voice_rows;
		usort($bottom_by_completion, function ($left, $right) {
			$completion_compare = $this->compare_sort_values((float) $left['completion_rate'], (float) $right['completion_rate'], 'asc');
			if ($completion_compare !== 0) {
				return $completion_compare;
			}

			return $this->compare_sort_values((int) $left['total_plays'], (int) $right['total_plays'], 'asc');
		});

		$labels = array();
		foreach (array_slice($top_by_plays, 0, 5) as $voice_row) {
			$labels[$voice_row['voice_name']] = $voice_row['voice_name'];
		}
		foreach (array_slice($bottom_by_completion, 0, 3) as $voice_row) {
			$labels[$voice_row['voice_name']] = $voice_row['voice_name'];
		}

		return array_values($labels);
	}

	private function get_dashboard_median_value($values)
	{
		if (empty($values)) {
			return 0;
		}

		sort($values, SORT_NUMERIC);
		$count = count($values);
		$middle = (int) floor($count / 2);

		if ($count % 2 === 1) {
			return (float) $values[$middle];
		}

		return ((float) $values[$middle - 1] + (float) $values[$middle]) / 2;
	}

	private function get_dashboard_post_rows($start, $end, $audio_rows)
	{
		global $wpdb;

		$representative_audio = array();
		$post_voice_labels = array();
		foreach ($audio_rows as $audio_row) {
			$post_id = (int) $audio_row['post_id'];
			if (!isset($post_voice_labels[$post_id])) {
				$post_voice_labels[$post_id] = array();
			}

			$summary_label = !empty($audio_row['voice_summary_label']) ? (string) $audio_row['voice_summary_label'] : (!empty($audio_row['voice_label']) ? (string) $audio_row['voice_label'] : 'Unknown Voice');
			$post_voice_labels[$post_id][$summary_label] = $summary_label;
			if (!isset($representative_audio[$post_id]) || (int) $audio_row['total_plays'] > (int) $representative_audio[$post_id]['total_plays']) {
				$representative_audio[$post_id] = $audio_row;
			}
		}

		$session_sql = $this->get_dashboard_session_summary_sql($start, $end);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results("SELECT
			session_summary.post_id,
			COUNT(*) AS total_plays,
			COUNT(DISTINCT session_summary.visitor_id) AS unique_listeners,
			AVG(session_summary.session_max_progress) AS average_play_rate,
			SUM(session_summary.has_complete) AS completed_count
		FROM ({$session_sql}) AS session_summary
		GROUP BY session_summary.post_id
		ORDER BY total_plays DESC, session_summary.post_id DESC", ARRAY_A);
		if (empty($rows)) {
			return array();
		}

		$post_rows = array();
		foreach ($rows as $row) {
			$post_id = (int) $row['post_id'];
			$plays = (int) $row['total_plays'];
			$representative = isset($representative_audio[$post_id]) ? $representative_audio[$post_id] : null;
			$unique_voice_labels = isset($post_voice_labels[$post_id]) ? array_values($post_voice_labels[$post_id]) : array();
			$post_voice_label = count($unique_voice_labels) > 1
				? 'Mixed'
				: ($representative ? $representative['voice_summary_label'] : 'Unknown Voice');
			$post_rows[] = array(
				'post_id' => $post_id,
				'post_title' => $post_id > 0 ? get_the_title($post_id) : 'Unknown post',
				'post_link' => $post_id > 0 ? get_permalink($post_id) : '',
				'voice_label' => $post_voice_label,
				'duration_seconds' => $representative ? (float) $representative['average_duration_seconds'] : 0.0,
				'total_plays' => $plays,
				'unique_listeners' => isset($row['unique_listeners']) ? (int) $row['unique_listeners'] : 0,
				'completion_rate' => $plays > 0 ? round((((int) $row['completed_count']) / $plays) * 100, 1) : 0.0,
				'average_play_rate' => isset($row['average_play_rate']) && $row['average_play_rate'] !== null ? round((float) $row['average_play_rate'], 1) : 0.0,
			);
		}

		return $post_rows;
	}

	private function get_dashboard_kpi_rows($current_metrics, $previous_metrics, $period_days)
	{
		return array(
			array(
				'label' => __('Total Plays', 'quikvox-ai'),
				'value' => $this->format_dashboard_count((int) $current_metrics['total_plays']),
				'meta' => $this->format_dashboard_delta_meta($current_metrics['total_plays'], $previous_metrics['total_plays'], $period_days),
				'meta_class' => $this->get_dashboard_delta_state_class($current_metrics['total_plays'], $previous_metrics['total_plays']),
				'accent_class' => 'is-blue',
			),
			array(
				'label' => __('Completion Rate', 'quikvox-ai'),
				'value' => $this->format_dashboard_percentage((float) $current_metrics['completion_rate']),
				'meta' => $this->format_dashboard_delta_meta($current_metrics['completion_rate'], $previous_metrics['completion_rate'], $period_days),
				'meta_class' => $this->get_dashboard_delta_state_class($current_metrics['completion_rate'], $previous_metrics['completion_rate']),
				'accent_class' => 'is-green',
			),
			array(
				'label' => __('Avg. Play Rate', 'quikvox-ai'),
				'value' => $this->format_dashboard_percentage((float) $current_metrics['average_play_rate']),
				'meta' => $this->format_dashboard_delta_meta($current_metrics['average_play_rate'], $previous_metrics['average_play_rate'], $period_days),
				'meta_class' => $this->get_dashboard_delta_state_class($current_metrics['average_play_rate'], $previous_metrics['average_play_rate']),
				'accent_class' => 'is-purple',
			),
			array(
				'label' => __('Unique Listeners (Est.)', 'quikvox-ai'),
				'value' => $this->format_dashboard_count((int) $current_metrics['unique_listeners']),
				'meta' => $this->format_dashboard_delta_meta($current_metrics['unique_listeners'], $previous_metrics['unique_listeners'], $period_days),
				'meta_class' => $this->get_dashboard_delta_state_class($current_metrics['unique_listeners'], $previous_metrics['unique_listeners']),
				'accent_class' => 'is-orange',
			),
		);
	}

	private function get_dashboard_retention_rows($metrics)
	{
		$total_plays = (int) $metrics['total_plays'];

		return array(
			array(
				'label' => __('Play Start (0%)', 'quikvox-ai'),
				'count' => $total_plays,
				'percentage' => $total_plays > 0 ? 100 : 0,
				'color' => '#2271b1',
				'value_class' => '',
			),
			array(
				'label' => __('Reached 30%', 'quikvox-ai'),
				'count' => (int) $metrics['reach_30_count'],
				'percentage' => (float) $metrics['reach_30_rate'],
				'color' => '#2271b1',
				'value_class' => '',
			),
			array(
				'label' => __('Reached 60%', 'quikvox-ai'),
				'count' => (int) $metrics['reach_60_count'],
				'percentage' => (float) $metrics['reach_60_rate'],
				'color' => '#5c90c8',
				'value_class' => '',
			),
			array(
				'label' => __('Reached 90%', 'quikvox-ai'),
				'count' => (int) $metrics['reach_90_count'],
				'percentage' => (float) $metrics['reach_90_rate'],
				'color' => '#8cb4e0',
				'value_class' => '',
			),
			array(
				'label' => __('Completed (100%)', 'quikvox-ai'),
				'count' => (int) $metrics['completed_count'],
				'percentage' => (float) $metrics['completion_rate'],
				'color' => '#00a32a',
				'value_class' => 'is-complete',
			),
		);
	}

	private function format_dashboard_delta_meta($current, $previous, $period_days)
	{
		if ((float) $previous === 0.0) {
			if ((float) $current === 0.0) {
				return 'No change vs previous ' . $period_days . ' days';
			}

			return '▲ New vs previous ' . $period_days . ' days';
		}

		$delta = $this->get_delta((float) $current, (float) $previous);
		if ($delta > 0) {
			return '▲ ' . $this->format_dashboard_percentage(abs($delta)) . ' vs previous ' . $period_days . ' days';
		}
		if ($delta < 0) {
			return '▼ ' . $this->format_dashboard_percentage(abs($delta)) . ' vs previous ' . $period_days . ' days';
		}

		return '0% vs previous ' . $period_days . ' days';
	}

	private function get_dashboard_delta_state_class($current, $previous)
	{
		if ((float) $previous === 0.0 && (float) $current === 0.0) {
			return 'is-flat';
		}

		$delta = $this->get_delta((float) $current, (float) $previous);
		if ($delta > 0 || ((float) $previous === 0.0 && (float) $current > 0.0)) {
			return 'is-up';
		}
		if ($delta < 0) {
			return 'is-down';
		}

		return 'is-flat';
	}

	private function format_dashboard_count($value)
	{
		return number_format_i18n((int) $value);
	}

	private function format_dashboard_percentage($value)
	{
		$rounded = round((float) $value, 1);
		$decimals = abs($rounded - round($rounded)) < 0.05 ? 0 : 1;
		return number_format_i18n($rounded, $decimals) . '%';
	}

	private function format_dashboard_seconds($value)
	{
		return number_format_i18n((int) round((float) $value)) . 's';
	}

	private function format_dashboard_duration_mmss($seconds)
	{
		$total_seconds = max(0, (int) round((float) $seconds));
		$minutes = (int) floor($total_seconds / 60);
		$remaining_seconds = $total_seconds % 60;

		return sprintf('%d:%02d', $minutes, $remaining_seconds);
	}

	private function render_dashboard_inline_percentage_bar($percent, $mode = 'completion')
	{
		$bar_color = $this->get_dashboard_inline_bar_color($percent, $mode);

		return sprintf(
			'<div class="Podcastmaker-inline-bar"><span class="Podcastmaker-inline-bar__track"><span class="Podcastmaker-inline-bar__fill" style="width:%1$s%%; background:%2$s;"></span></span><span class="Podcastmaker-inline-bar__value" style="color:%2$s;">%3$s</span></div>',
			esc_attr((string) max(0.0, min(100.0, (float) $percent))),
			esc_attr($bar_color),
			esc_html($this->format_dashboard_percentage((float) $percent))
		);
	}

	private function get_dashboard_inline_bar_color($percent, $mode = 'completion')
	{
		$percent = (float) $percent;
		if ($mode === 'reach') {
			if ($percent >= 85) {
				return '#00a32a';
			}
			if ($percent >= 65) {
				return '#dba617';
			}
			return '#d63638';
		}

		if ($percent >= 80) {
			return '#00a32a';
		}
		if ($percent >= 60) {
			return '#dba617';
		}
		return '#d63638';
	}

	private function render_dashboard_post_cell($row)
	{
		$title = !empty($row['post_title']) ? $row['post_title'] : 'Unknown post';
		$post_id = isset($row['post_id']) ? (int) $row['post_id'] : 0;
		$post_link = !empty($row['post_link']) ? $row['post_link'] : '';
		$link_html = '';
		if (!empty($post_link)) {
			$link_html = ' <span class="Podcastmaker-post-title-cell__separator">|</span> <a href="' . esc_url($post_link) . '" target="_blank" rel="noreferrer noopener">View Post ↗</a>';
		}

		return sprintf(
			'<div class="Podcastmaker-post-title-cell"><div class="Podcastmaker-post-title-cell__title">%1$s</div><div class="Podcastmaker-post-title-cell__meta">ID: %2$d%3$s</div></div>',
			esc_html($title),
			$post_id,
			$link_html
		);
	}

	private function render_dashboard_voice_tag($voice_label)
	{
		$voice_label = !empty($voice_label) ? $voice_label : 'Unknown Voice';
		$modifier = 'is-primary';
		if ($voice_label === 'Unknown Voice') {
			$modifier = 'is-muted';
		}
		elseif ($voice_label === 'Mixed') {
			$modifier = 'is-mixed';
		}

		return sprintf(
			'<span class="Podcastmaker-voice-tag %1$s">%2$s</span>',
			esc_attr($modifier),
			esc_html($voice_label)
		);
	}

public function render_admin_page()
	{
		$period_days = $this->get_selected_period_days();
		$dashboard = $this->get_dashboard_bundle($period_days);
		?>
		<div class="wrap Podcastmaker-audio-analytics">
			<h1 class="Podcastmaker-page-title"><?php echo esc_html__('Audio Analytics', 'quikvox-ai'); ?></h1>

			<div class="Podcastmaker-period-bar">
				<div class="Podcastmaker-period-bar__controls" role="tablist" aria-label="<?php echo esc_attr__('Analytics period', 'quikvox-ai'); ?>">
					<span class="Podcastmaker-period-bar__label"><?php echo esc_html__('Period:', 'quikvox-ai'); ?></span>
					<a class="Podcastmaker-period-button <?php echo $period_days === 7 ? 'is-active' : ''; ?>" href="<?php echo esc_url($this->build_admin_url(array('period' => 7))); ?>"><?php echo esc_html__('7 Days', 'quikvox-ai'); ?></a>
					<a class="Podcastmaker-period-button <?php echo $period_days === 30 ? 'is-active' : ''; ?>" href="<?php echo esc_url($this->build_admin_url(array('period' => 30))); ?>"><?php echo esc_html__('30 Days', 'quikvox-ai'); ?></a>
					<a class="Podcastmaker-period-button <?php echo $period_days === 90 ? 'is-active' : ''; ?>" href="<?php echo esc_url($this->build_admin_url(array('period' => 90))); ?>"><?php echo esc_html__('90 Days', 'quikvox-ai'); ?></a>
					<span class="Podcastmaker-period-bar__separator">|</span>
					<span class="Podcastmaker-period-bar__range"><?php echo esc_html($dashboard['range']['label']); ?></span>
				</div>
				<a class="button button-primary Podcastmaker-export-button" href="<?php echo esc_url(wp_nonce_url($this->build_admin_url(array('export' => 'csv')), 'podcastmaker_export_csv')); ?>"><?php echo esc_html__('Export CSV', 'quikvox-ai'); ?></a>
			</div>

			<div class="Podcastmaker-kpi-grid">
				<?php foreach ($dashboard['kpi_rows'] as $kpi_row) : ?>
					<div class="Podcastmaker-kpi-card <?php echo esc_attr($kpi_row['accent_class']); ?>">
						<div class="Podcastmaker-kpi-card__label"><?php echo esc_html($kpi_row['label']); ?></div>
						<div class="Podcastmaker-kpi-card__value"><?php echo esc_html($kpi_row['value']); ?></div>
						<div class="Podcastmaker-kpi-card__meta <?php echo esc_attr($kpi_row['meta_class']); ?>"><?php echo esc_html($kpi_row['meta']); ?></div>
					</div>
				<?php endforeach; ?>
			</div>

			<div class="Podcastmaker-chart-grid Podcastmaker-chart-grid--primary">
				<div class="Podcastmaker-dashboard-card">
					<div class="Podcastmaker-dashboard-card__header">
						<div>
							<?php /* translators: %s is the number of days displayed in the trend chart. */ ?>
							<h2 class="Podcastmaker-dashboard-card__title"><?php echo esc_html(sprintf(__('Daily Plays Trend (%s Days)', 'quikvox-ai'), esc_html($period_days))); ?></h2>
							<p class="Podcastmaker-dashboard-card__subtitle"><?php echo esc_html__('Counted as unique start events per session', 'quikvox-ai'); ?></p>
						</div>
					</div>
					<div class="Podcastmaker-dashboard-card__body">
						<?php if ($dashboard['has_data']) : ?>
							<div class="Podcastmaker-chart-wrap Podcastmaker-chart-wrap--trend">
								<canvas id="podcastmaker-audio-trend-chart" aria-label="Daily plays trend chart"></canvas>
							</div>
						<?php else : ?>
							<div class="Podcastmaker-chart-empty"><?php echo esc_html__('No playback data yet for this period.', 'quikvox-ai'); ?></div>
						<?php endif; ?>
					</div>
				</div>

				<div class="Podcastmaker-dashboard-card">
					<div class="Podcastmaker-dashboard-card__header">
						<div>
							<h2 class="Podcastmaker-dashboard-card__title"><?php echo esc_html__('Plays by Hour of Day', 'quikvox-ai'); ?></h2>
							<p class="Podcastmaker-dashboard-card__subtitle"><?php echo esc_html__('start events by hour (0–23)', 'quikvox-ai'); ?></p>
						</div>
					</div>
					<div class="Podcastmaker-dashboard-card__body">
						<?php if ($dashboard['has_data']) : ?>
							<div class="Podcastmaker-chart-wrap Podcastmaker-chart-wrap--hour">
								<canvas id="podcastmaker-audio-hour-chart" aria-label="Plays by hour chart"></canvas>
							</div>
						<?php else : ?>
							<div class="Podcastmaker-chart-empty"><?php echo esc_html__('No playback data yet for this period.', 'quikvox-ai'); ?></div>
						<?php endif; ?>
					</div>
				</div>
			</div>

			<div class="Podcastmaker-chart-grid Podcastmaker-chart-grid--secondary">
				<div class="Podcastmaker-dashboard-card">
					<div class="Podcastmaker-dashboard-card__header">
						<div>
							<h2 class="Podcastmaker-dashboard-card__title"><?php echo esc_html__('Listening Retention Funnel', 'quikvox-ai'); ?></h2>
							<p class="Podcastmaker-dashboard-card__subtitle"><?php echo esc_html__('Retention rate at each checkpoint (start = 100%)', 'quikvox-ai'); ?></p>
						</div>
					</div>
					<div class="Podcastmaker-dashboard-card__body">
						<div class="Podcastmaker-retention-list">
							<?php foreach ($dashboard['retention_rows'] as $retention_row) : ?>
								<div class="Podcastmaker-retention-item">
									<div class="Podcastmaker-retention-item__header">
										<span class="Podcastmaker-retention-item__label"><?php echo esc_html($retention_row['label']); ?></span>
										<span class="Podcastmaker-retention-item__value <?php echo esc_attr($retention_row['value_class']); ?>"><?php echo esc_html($this->format_dashboard_count((int) $retention_row['count']) . ' plays (' . $this->format_dashboard_percentage((float) $retention_row['percentage']) . ')'); ?></span>
									</div>
									<div class="Podcastmaker-retention-bar">
										<span class="Podcastmaker-retention-bar__fill" style="width:<?php echo esc_attr(max(0.0, min(100.0, (float) $retention_row['percentage']))); ?>%; background:<?php echo esc_attr($retention_row['color']); ?>;"></span>
									</div>
								</div>
							<?php endforeach; ?>
						</div>
					</div>
				</div>

				<div class="Podcastmaker-dashboard-card">
					<div class="Podcastmaker-dashboard-card__header">
						<div>
							<h2 class="Podcastmaker-dashboard-card__title"><?php echo esc_html__('Voice: Plays × Completion Rate', 'quikvox-ai'); ?></h2>
							<?php /* translators: %s is the number of voices shown in the scatter chart. */ ?>
							<p class="Podcastmaker-dashboard-card__subtitle"><?php echo esc_html(sprintf(__('Top-right = popular & high-completion voices (%s voices). Multi-speaker plays are attributed to each participating voice.', 'quikvox-ai'), esc_html($dashboard['voice_count']))); ?></p>
						</div>
						<div class="Podcastmaker-quadrant-legend" aria-hidden="true">
							<span><i class="is-top"></i><?php echo esc_html__('Top', 'quikvox-ai'); ?></span>
							<span><i class="is-gem"></i><?php echo esc_html__('Hidden Gem', 'quikvox-ai'); ?></span>
							<span><i class="is-work"></i><?php echo esc_html__('Needs Work', 'quikvox-ai'); ?></span>
							<span><i class="is-low"></i><?php echo esc_html__('Low Data', 'quikvox-ai'); ?></span>
						</div>
					</div>
					<div class="Podcastmaker-dashboard-card__body Podcastmaker-dashboard-card__body--tight">
						<?php if (!empty($dashboard['voice_rows'])) : ?>
							<div class="Podcastmaker-chart-wrap Podcastmaker-chart-wrap--scatter">
								<canvas id="podcastmaker-audio-voice-scatter" aria-label="Voice scatter chart"></canvas>
							</div>
						<?php else : ?>
							<div class="Podcastmaker-chart-empty"><?php echo esc_html__('No voice data yet for this period.', 'quikvox-ai'); ?></div>
						<?php endif; ?>
					</div>
				</div>
			</div>

			<div class="Podcastmaker-dashboard-card Podcastmaker-dashboard-card--table">
				<div class="Podcastmaker-dashboard-card__header">
					<div>
						<h2 class="Podcastmaker-dashboard-card__title"><?php echo esc_html__('Voice Performance Top 10 (by Completion Rate)', 'quikvox-ai'); ?></h2>
						<p class="Podcastmaker-dashboard-card__subtitle"><?php echo esc_html__('Voices with 5+ plays. Multi-speaker sessions count toward each participating voice.', 'quikvox-ai'); ?></p>
					</div>
					<?php /* translators: %s is the total number of voices. */ ?>
					<div class="Podcastmaker-dashboard-card__aside"><?php echo esc_html(sprintf(__('Out of %s voices', 'quikvox-ai'), esc_html($dashboard['voice_count']))); ?></div>
				</div>
				<div class="Podcastmaker-dashboard-table-wrap">
					<table class="Podcastmaker-analytics-table">
						<thead>
							<tr>
								<th style="width:32px;">#</th>
								<th><?php echo esc_html__('Voice', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Plays', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Completion', 'quikvox-ai'); ?></th>
								<th title="<?php echo esc_attr__('% of sessions that reached the 90% point', 'quikvox-ai'); ?>"><?php echo esc_html__('90% Reached', 'quikvox-ai'); ?></th>
								<th title="<?php echo esc_attr__('% of sessions that reached the 60% point', 'quikvox-ai'); ?>"><?php echo esc_html__('60% Reached', 'quikvox-ai'); ?></th>
								<th title="<?php echo esc_attr__('% of sessions that reached the 30% point', 'quikvox-ai'); ?>"><?php echo esc_html__('30% Reached', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Avg. Duration', 'quikvox-ai'); ?></th>
							</tr>
						</thead>
						<tbody>
							<?php if (empty($dashboard['top_voice_rows'])) : ?>
								<tr><td colspan="8" class="Podcastmaker-table-empty"><?php echo esc_html__('No voice data yet.', 'quikvox-ai'); ?></td></tr>
							<?php else : ?>
								<?php foreach ($dashboard['top_voice_rows'] as $index => $voice_row) : ?>
									<tr>
										<td class="Podcastmaker-rank-cell"><?php echo esc_html((string) ($index + 1)); ?></td>
										<td class="Podcastmaker-voice-name-cell"><?php echo esc_html($voice_row['voice_name']); ?></td>
										<td><?php echo esc_html($this->format_dashboard_count((int) $voice_row['total_plays'])); ?></td>
										<?php // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
										<td><?php echo $this->render_dashboard_inline_percentage_bar((float) $voice_row['completion_rate'], 'completion'); ?></td>
										<?php // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
										<td><?php echo $this->render_dashboard_inline_percentage_bar((float) $voice_row['reach_90_rate'], 'reach'); ?></td>
										<?php // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
										<td><?php echo $this->render_dashboard_inline_percentage_bar((float) $voice_row['reach_60_rate'], 'reach'); ?></td>
										<?php // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
										<td><?php echo $this->render_dashboard_inline_percentage_bar((float) $voice_row['reach_30_rate'], 'reach'); ?></td>
										<td><?php echo esc_html($this->format_dashboard_seconds((float) $voice_row['average_duration_seconds'])); ?></td>
									</tr>
								<?php endforeach; ?>
							<?php endif; ?>
						</tbody>
					</table>
				</div>
				<?php /* translators: %s is the total number of voices. */ ?>
				<div class="Podcastmaker-dashboard-card__footer"><?php echo esc_html(sprintf(__('Out of %s voices · voices with 5+ plays', 'quikvox-ai'), esc_html($dashboard['voice_count']))); ?></div>
			</div>

			<div class="Podcastmaker-dashboard-card Podcastmaker-dashboard-card--table">
				<div class="Podcastmaker-dashboard-card__header">
					<div>
						<h2 class="Podcastmaker-dashboard-card__title"><?php echo esc_html__('Post Performance', 'quikvox-ai'); ?></h2>
						<p class="Podcastmaker-dashboard-card__subtitle"><?php echo esc_html__('Aggregated by post_id. Plays = unique playback sessions (start events).', 'quikvox-ai'); ?></p>
					</div>
					<?php /* translators: 1: post count, 2: audio count. */ ?>
					<div class="Podcastmaker-dashboard-card__aside"><?php echo esc_html(sprintf(__('%1$s posts / %2$s audios', 'quikvox-ai'), esc_html($dashboard['post_count']), esc_html($dashboard['audio_count']))); ?></div>
				</div>
				<div class="Podcastmaker-dashboard-table-wrap">
					<table class="Podcastmaker-analytics-table">
						<thead>
							<tr>
								<th><?php echo esc_html__('Post Title', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Voice', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Duration', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Plays', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Unique', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Completion', 'quikvox-ai'); ?></th>
								<th><?php echo esc_html__('Avg. Play Rate', 'quikvox-ai'); ?></th>
							</tr>
						</thead>
						<tbody>
							<?php if (empty($dashboard['post_rows'])) : ?>
								<tr><td colspan="7" class="Podcastmaker-table-empty"><?php echo esc_html__('No post data yet.', 'quikvox-ai'); ?></td></tr>
							<?php else : ?>
								<?php foreach ($dashboard['post_rows'] as $post_row) : ?>
									<tr>
										<?php // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
										<td><?php echo $this->render_dashboard_post_cell($post_row); ?></td>
										<?php // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
										<td><?php echo $this->render_dashboard_voice_tag($post_row['voice_label']); ?></td>
										<td><?php echo esc_html($this->format_dashboard_duration_mmss((float) $post_row['duration_seconds'])); ?></td>
										<td><strong><?php echo esc_html($this->format_dashboard_count((int) $post_row['total_plays'])); ?></strong></td>
										<td><?php echo esc_html($this->format_dashboard_count((int) $post_row['unique_listeners'])); ?></td>
										<?php // phpcs:ignore WordPress.Security.EscapeOutput.OutputNotEscaped ?>
										<td><?php echo $this->render_dashboard_inline_percentage_bar((float) $post_row['completion_rate'], 'completion'); ?></td>
										<td class="Podcastmaker-strong-value"><?php echo esc_html($this->format_dashboard_percentage((float) $post_row['average_play_rate'])); ?></td>
									</tr>
								<?php endforeach; ?>
							<?php endif; ?>
						</tbody>
					</table>
				</div>
			</div>

			<div class="Podcastmaker-data-note">
				<strong><?php echo esc_html__('Data source:', 'quikvox-ai'); ?></strong>
				audio analytics table / Counts: unique playback_session_id = plays, max(progress_percent) per session = reach rate, complete event present = completed. visitor_id is an estimated browser-level identifier.
				For multi-speaker audio, voice-level charts attribute one playback session to each participating voice, so voice chart totals can exceed the global KPI totals.
			</div>
		</div>
		<?php
	}

	private function get_kpi_rows($current_metrics, $previous_metrics)
	{
		return array(
			array(
				'label' => __('Total Plays', 'quikvox-ai'),
				'value' => number_format_i18n($current_metrics['total_plays']),
				'delta' => $this->format_delta($current_metrics['total_plays'], $previous_metrics['total_plays']),
				'delta_class' => $this->get_delta($current_metrics['total_plays'], $previous_metrics['total_plays']) >= 0 ? 'is-up' : 'is-down',
			),
			array(
				'label' => __('Unique Listeners', 'quikvox-ai'),
				'value' => number_format_i18n($current_metrics['unique_listeners']),
				'delta' => $this->format_delta($current_metrics['unique_listeners'], $previous_metrics['unique_listeners']),
				'delta_class' => $this->get_delta($current_metrics['unique_listeners'], $previous_metrics['unique_listeners']) >= 0 ? 'is-up' : 'is-down',
			),
			array(
				'label' => __('90% Completion Rate', 'quikvox-ai'),
				'value' => number_format_i18n($current_metrics['completion_90_rate'], 1) . '%',
				'delta' => $this->format_delta($current_metrics['completion_90_rate'], $previous_metrics['completion_90_rate']),
				'delta_class' => $this->get_delta($current_metrics['completion_90_rate'], $previous_metrics['completion_90_rate']) >= 0 ? 'is-up' : 'is-down',
			),
			array(
				'label' => __('Average Play Rate', 'quikvox-ai'),
				'value' => number_format_i18n($current_metrics['average_play_rate'], 1) . '%',
				'delta' => $this->format_delta($current_metrics['average_play_rate'], $previous_metrics['average_play_rate']),
				'delta_class' => $this->get_delta($current_metrics['average_play_rate'], $previous_metrics['average_play_rate']) >= 0 ? 'is-up' : 'is-down',
			),
		);
	}

	private function get_comparison_rows($current, $previous)
	{
		return array(
			array(
				'label' => __('Total plays', 'quikvox-ai'),
				'current' => number_format_i18n($current['total_plays']),
				'previous' => number_format_i18n($previous['total_plays']),
				'delta' => $this->format_delta($current['total_plays'], $previous['total_plays']),
			),
			array(
				'label' => __('Unique listeners', 'quikvox-ai'),
				'current' => number_format_i18n($current['unique_listeners']),
				'previous' => number_format_i18n($previous['unique_listeners']),
				'delta' => $this->format_delta($current['unique_listeners'], $previous['unique_listeners']),
			),
			array(
				'label' => __('90% completion rate', 'quikvox-ai'),
				'current' => number_format_i18n($current['completion_90_rate'], 1) . '%',
				'previous' => number_format_i18n($previous['completion_90_rate'], 1) . '%',
				'delta' => $this->format_delta($current['completion_90_rate'], $previous['completion_90_rate']),
			),
			array(
				'label' => __('Average play rate', 'quikvox-ai'),
				'current' => number_format_i18n($current['average_play_rate'], 1) . '%',
				'previous' => number_format_i18n($previous['average_play_rate'], 1) . '%',
				'delta' => $this->format_delta($current['average_play_rate'], $previous['average_play_rate']),
			),
		);
	}

	private function format_delta($current, $previous)
	{
		$delta = $this->get_delta((float) $current, (float) $previous);
		$prefix = $delta > 0 ? '+' : '';
		return $prefix . number_format_i18n($delta, 1) . '%';
	}

	private function render_post_cell($row)
	{
		$title = esc_html($row['post_title']);
		$parts = array();
		if (!empty($row['post_link'])) {
			$parts[] = '<a href="' . esc_url($row['post_link']) . '" target="_blank" rel="noreferrer noopener">' . $title . '</a>';
		} else {
			$parts[] = $title;
		}
		if (!empty($row['post_edit_link'])) {
			$parts[] = '<a class="Podcastmaker-analytics-secondary-link" href="' . esc_url($row['post_edit_link']) . '">' . esc_html__('Edit', 'quikvox-ai') . '</a>';
		}
		return implode(' ', $parts);
	}

	private function render_audio_cell($row)
	{
		$label = '<code>' . esc_html($row['audio_label']) . '</code>';
		$parts = array();
		if (!empty($row['audio_link'])) {
			$parts[] = '<a href="' . esc_url($row['audio_link']) . '" target="_blank" rel="noreferrer noopener">' . $label . '</a>';
		} else {
			$parts[] = $label;
		}
		if (!empty($row['media_edit_link'])) {
			$parts[] = '<a class="Podcastmaker-analytics-secondary-link" href="' . esc_url($row['media_edit_link']) . '">' . esc_html__('Media', 'quikvox-ai') . '</a>';
		}
		return implode(' ', $parts);
	}
}
// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
