<?php

/**
 * Statistics Helper.
 *
 * @link    https://aiopodcasting.com
 * @since   1.0.0
 *
 * @package AiO_Podcast_Studio
 */

// Exit if accessed directly.
if ( ! defined( 'WPINC' ) ) {
	die;
}

/**
 * AIOPOD_Statistics_Helper class.
 *
 * @since 1.0.0
 */
class AIOPOD_Statistics_Helper {

	/**
	 * Event type: file download via a podcast app or browser.
	 *
	 * @since 1.0.0
	 * @var   int
	 */
	const EVENT_DOWNLOAD = 1;

	/**
	 * Event type: playback triggered by the embedded web player.
	 *
	 * @since 1.0.0
	 * @var   int
	 */
	const EVENT_PLAY = 2;

	/**
	 * The single instance of the class.
	 *
	 * @since  1.0.0
	 * @access private
	 * @static
	 * @var    AIOPOD_Statistics_Helper|null
	 */
	private static $instance = null;

	/**
	 * Full name (with prefix) of the unified event logs table.
	 *
	 * Stores one row per logged download or play event.
	 *
	 * @since  1.0.0
	 * @access private
	 * @var    string
	 */
	private $events_table;

	/**
	 * Full name (with prefix) of the pre-aggregated daily stats table.
	 *
	 * @since  1.0.0
	 * @access private
	 * @var    string
	 */
	private $daily_table;

	/**
	 * Deduplication window in minutes.
	 *
	 * An event from the same IP + user-agent + episode + event type within
	 * this window is treated as a single event and discarded. Applies to
	 * podcast app downloads; see log_event() for browser handling.
	 *
	 * @since  1.0.0
	 * @access private
	 * @var    int
	 */
	private $dedup_window_minutes = 60;

	/**
	 * Return the single instance of the class, creating it on first call.
	 *
	 * @since  1.0.0
	 * @static
	 * @return AIOPOD_Statistics_Helper
	 */
	public static function get_instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	/**
	 * Return the slug-to-label map for known podcast client applications.
	 *
	 * The slugs are the values stored in the events table by this class.
	 * Any code that needs to convert a stored slug (e.g. 'apple_podcasts')
	 * to a human-readable label should call this method rather than
	 * maintaining its own copy of the map.
	 *
	 * @since  1.0.0
	 * @return array Associative array of slug => display label.
	 */
	public static function get_client_label_map() {
		return array(
			'apple_podcasts' => 'Apple Podcasts',
			'spotify'        => 'Spotify',
			'overcast'       => 'Overcast',
			'pocket_casts'   => 'Pocket Casts',
			'podcast_addict' => 'Podcast Addict',
			'castbox'        => 'Castbox',
			'castro'         => 'Castro',
			'podbean'        => 'Podbean',
			'stitcher'       => 'Stitcher',
			'iheartradio'    => 'iHeartRadio',
			'tunein'         => 'TuneIn',
			'browser'        => 'Browser',
			'unknown'        => 'Other',
		);
	}

	/**
	 * Prevent cloning of the singleton instance.
	 *
	 * @since  1.0.0
	 * @access private
	 * @return void
	 */
	private function __clone() {}

	/**
	 * Prevent unserializing of the singleton instance.
	 *
	 * @since  1.0.0
	 * @return void
	 */
	public function __wakeup() {}

	/**
	 * Constructor — resolve table names against the current DB prefix.
	 *
	 * @since  1.0.0
	 * @access private
	 */
	private function __construct() {
		global $wpdb;

		$this->events_table = $wpdb->prefix . 'aiopod_event_logs';
		$this->daily_table  = $wpdb->prefix . 'aiopod_daily_stats';
	}

	/**
	 * Create (or upgrade) the custom database tables.
	 *
	 * @since  1.0.0
	 * @return void
	 */
	public function create_custom_tables() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		// Unified event log — one row per unique download or play event.
		$sql = "CREATE TABLE {$this->events_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			podcast_id BIGINT UNSIGNED NOT NULL,
			episode_id BIGINT UNSIGNED NOT NULL,
			event_type TINYINT UNSIGNED NOT NULL,
			ip VARBINARY(16) DEFAULT NULL,
			user_agent VARCHAR(255) DEFAULT NULL,
			client VARCHAR(50) DEFAULT NULL,
			country VARCHAR(2) DEFAULT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY (id),
			KEY podcast_id (podcast_id),
			KEY event_type (event_type),
			KEY created_at (created_at),
			KEY country (country),
			KEY episode_event_ip_ua_time (episode_id, event_type, ip, user_agent(191), created_at)
		) $charset_collate;";

		dbDelta( $sql );

		// Daily stats — pre-aggregated download and play counts per episode per day.
		$sql = "CREATE TABLE {$this->daily_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			podcast_id BIGINT UNSIGNED NOT NULL,
			episode_id BIGINT UNSIGNED NOT NULL,
			stat_date DATE NOT NULL,
			downloads INT UNSIGNED NOT NULL DEFAULT 0,
			plays INT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY episode_date (episode_id, stat_date),
			KEY podcast_date (podcast_id, stat_date),
			KEY stat_date (stat_date)
		) $charset_collate;";

		dbDelta( $sql );
	}

	/**
	 * Convert a valid IP address string to its packed binary representation.
	 *
	 * @since  1.0.0
	 * @access private
	 * @param  string      $ip Dotted-decimal IPv4 or colon-notation IPv6 string.
	 * @return string|null     Packed binary string, or null on failure.
	 */
	private function ip_to_binary( $ip ) {
		$binary = inet_pton( $ip );
		return ( false !== $binary ) ? $binary : null;
	}

	/**
	 * Retrieve and validate the real client IP address.
	 *
	 * @since  1.0.0
	 * @return string A validated IP address string, or '' if none found.
	 */
	public function get_client_ip() {
		$candidates = array();

		// Cloudflare sets this to the original visitor IP — most reliable when present.
		if ( ! empty( $_SERVER['HTTP_CF_CONNECTING_IP'] ) ) {
			$candidates[] = sanitize_text_field( wp_unslash( $_SERVER['HTTP_CF_CONNECTING_IP'] ) );
		}

		// Standard proxy forwarding header — trust only the leftmost (client) entry.
		if ( ! empty( $_SERVER['HTTP_X_FORWARDED_FOR'] ) ) {
			$forwarded    = sanitize_text_field( wp_unslash( $_SERVER['HTTP_X_FORWARDED_FOR'] ) );
			$candidates[] = trim( explode( ',', $forwarded )[0] );
		}

		// Direct TCP connection address — always present, never spoofable.
		if ( ! empty( $_SERVER['REMOTE_ADDR'] ) ) {
			$candidates[] = sanitize_text_field( wp_unslash( $_SERVER['REMOTE_ADDR'] ) );
		}

		foreach ( $candidates as $ip ) {
			if ( filter_var( $ip, FILTER_VALIDATE_IP ) ) {
				return $ip;
			}
		}

		return '';
	}

	/**
	 * Detect the podcast client application from the HTTP User-Agent string.
	 *
	 * @since  1.0.0
	 * @param  string $user_agent Raw HTTP User-Agent header value.
	 * @return string             Client slug, e.g. 'spotify', 'apple_podcasts',
	 *                            'browser', or 'unknown'.
	 */
	public function detect_client( $user_agent ) {
		$ua = strtolower( $user_agent );

		static $known_clients = array(
			'spotify'        => 'spotify',
			'applecoremedia' => 'apple_podcasts',
			'overcast'       => 'overcast',
			'pocketcasts'    => 'pocket_casts',
			'podcastaddict'  => 'podcast_addict',
			'castbox'        => 'castbox',
			'castro'         => 'castro',
			'podbean'        => 'podbean',
			'stitcher'       => 'stitcher',
			'iheartradio'    => 'iheartradio',
			'tunein'         => 'tunein',
		);

		foreach ( $known_clients as $needle => $slug ) {
			if ( false !== strpos( $ua, $needle ) ) {
				return $slug;
			}
		}

		// Generic browser check — must come after all specific app checks.
		if ( false !== strpos( $ua, 'mozilla' ) ) {
			return 'browser';
		}

		return 'unknown';
	}

	/**
	 * Log a podcast file download event.
	 *
	 * @since  1.0.0
	 * @param  int    $podcast_id The podcast (parent post) ID.
	 * @param  int    $episode_id The episode post ID.
	 * @param  string $ip         Optional. Client IP address. Reads from request if omitted.
	 * @param  string $ua         Optional. Client User-Agent string. Reads from request if omitted.
	 * @return bool               True on successful insert, false if skipped or failed.
	 */
	public function log_download( $podcast_id, $episode_id, $ip = null, $ua = null ) {
		return $this->log_event( $podcast_id, $episode_id, self::EVENT_DOWNLOAD, $ip, $ua );
	}

	/**
	 * Log a web-player play event.
	 *
	 * @since  1.0.0
	 * @param  int  $podcast_id The podcast (parent post) ID.
	 * @param  int  $episode_id The episode post ID.
	 * @return bool             True on successful insert, false if skipped or failed.
	 */
	public function log_play( $podcast_id, $episode_id ) {
		return $this->log_event( $podcast_id, $episode_id, self::EVENT_PLAY );
	}

	/**
	 * Core event logging routine shared by log_download() and log_play().
	 *
	 * Deduplication uses WordPress transients keyed on episode + event type + IP.
	 * Transients are persistent across requests and automatically backed by
	 * Redis or Memcached when a persistent object cache plugin is active,
	 * falling back to wp_options otherwise.
	 *
	 * @since  1.0.0
	 * @access private
	 * @param  int    $podcast_id The podcast (parent post) ID.
	 * @param  int    $episode_id The episode post ID.
	 * @param  int    $event_type EVENT_DOWNLOAD or EVENT_PLAY class constant.
	 * @param  string $ip         Optional. Client IP. Reads from current request if null.
	 * @param  string $ua         Optional. User-Agent. Reads from current request if null.
	 * @return bool               True on successful insert, false if skipped or failed.
	 */
	private function log_event( $podcast_id, $episode_id, $event_type, $ip = null, $ua = null ) {
		global $wpdb;

		$podcast_id = absint( $podcast_id );
		$episode_id = absint( $episode_id );

		if ( $podcast_id <= 0 || $episode_id <= 0 ) {
			return false;
		}

		// Use explicitly passed values (from async loopback) or fall back to
		// the current request context (Strategy 1 / play events).
		if ( null === $ip ) {
			$ip = $this->get_client_ip();
		}

		if ( null === $ua ) {
			$ua = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';
		}

		$ip_bin        = ! empty( $ip ) ? $this->ip_to_binary( $ip ) : null;
		$client        = $this->detect_client( $ua );
		$transient_key = '';

		// Dedup: one event per episode per IP within the configured window.
		// Transients persist across requests (unlike WP object cache on sites
		// without Redis/Memcached) and use the fast backend when available.
		if ( ! empty( $ip_bin ) ) {
			$prefix        = ( self::EVENT_DOWNLOAD === $event_type ) ? 'dl' : 'pl';
			$transient_key = 'aiopod_' . $prefix . '_' . $episode_id . '_' . bin2hex( $ip_bin );

			if ( get_transient( $transient_key ) ) {
				return false;
			}
		}

		$inserted = $wpdb->insert(
			$this->events_table,
			array(
				'podcast_id' => $podcast_id,
				'episode_id' => $episode_id,
				'event_type' => $event_type,
				'ip'         => $ip_bin,
				'user_agent' => $ua,
				'client'     => $client,
				'created_at' => current_time( 'mysql' ),
			),
			array( '%d', '%d', '%d', '%s', '%s', '%s', '%s' )
		);

		if ( ! $inserted ) {
			return false;
		}

		// Set the transient only after a confirmed insert.
		if ( ! empty( $transient_key ) ) {
			set_transient( $transient_key, 1, $this->dedup_window_minutes * MINUTE_IN_SECONDS );
		}

		$this->update_daily_stats( $podcast_id, $episode_id, $event_type );
		$this->update_post_meta_counts( $podcast_id, $episode_id );

		return true;
	}

	/**
	 * Atomically increment the pre-aggregated daily stats counter.
	 *
	 * @since  1.0.0
	 * @access private
	 * @param  int     $podcast_id The podcast (parent post) ID.
	 * @param  int     $episode_id The episode post ID.
	 * @param  int     $event_type EVENT_DOWNLOAD or EVENT_PLAY class constant.
	 * @return void
	 */
	private function update_daily_stats( $podcast_id, $episode_id, $event_type ) {
		global $wpdb;

		// Date in site timezone so daily buckets align with the admin's calendar.
		$date = current_time( 'Y-m-d' );

		if ( self::EVENT_DOWNLOAD === $event_type ) {
			$sql = $wpdb->prepare(
				"INSERT INTO {$this->daily_table}
				(podcast_id, episode_id, stat_date, downloads, plays)
				VALUES (%d, %d, %s, 1, 0)
				ON DUPLICATE KEY UPDATE downloads = downloads + 1",
				absint( $podcast_id ),
				absint( $episode_id ),
				$date
			);
		} else {
			$sql = $wpdb->prepare(
				"INSERT INTO {$this->daily_table}
				(podcast_id, episode_id, stat_date, downloads, plays)
				VALUES (%d, %d, %s, 0, 1)
				ON DUPLICATE KEY UPDATE plays = plays + 1",
				absint( $podcast_id ),
				absint( $episode_id ),
				$date
			);
		}

		$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Return the total download count across all podcasts for a date range.
	 *
	 * @since  1.0.0
	 * @param  string $start Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end   End date in 'Y-m-d' format (inclusive).
	 * @return int           Total download count, or 0 on failure.
	 */
	public function get_downloads_by_range( $start, $end ) {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT SUM(downloads)
				FROM {$this->daily_table}
				WHERE stat_date BETWEEN %s AND %s",
				$start,
				$end
			)
		);
	}

	/**
	 * Return the total play count across all podcasts for a date range.
	 *
	 * @since  1.0.0
	 * @param  string $start Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end   End date in 'Y-m-d' format (inclusive).
	 * @return int           Total play count, or 0 on failure.
	 */
	public function get_plays_by_range( $start, $end ) {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT SUM(plays)
				FROM {$this->daily_table}
				WHERE stat_date BETWEEN %s AND %s",
				$start,
				$end
			)
		);
	}

	/**
	 * Return the total download count for a single podcast over a date range.
	 *
	 * @since  1.0.0
	 * @param  int    $podcast_id The podcast post ID.
	 * @param  string $start      Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end        End date in 'Y-m-d' format (inclusive).
	 * @return int                Download count, or 0 on failure.
	 */
	public function get_podcast_downloads_by_range( $podcast_id, $start, $end ) {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT SUM(downloads)
				FROM {$this->daily_table}
				WHERE podcast_id = %d
				AND stat_date BETWEEN %s AND %s",
				absint( $podcast_id ),
				$start,
				$end
			)
		);
	}

	/**
	 * Return the total download count for a single episode over a date range.
	 *
	 * @since  1.0.0
	 * @param  int    $episode_id The episode post ID.
	 * @param  string $start      Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end        End date in 'Y-m-d' format (inclusive).
	 * @return int                Download count, or 0 on failure.
	 */
	public function get_episode_downloads_by_range( $episode_id, $start, $end ) {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT SUM(downloads)
				FROM {$this->daily_table}
				WHERE episode_id = %d
				AND stat_date BETWEEN %s AND %s",
				absint( $episode_id ),
				$start,
				$end
			)
		);
	}

	/**
	 * Return the total play count for a single podcast over a date range.
	 *
	 * @since  1.0.0
	 * @param  int    $podcast_id The podcast post ID.
	 * @param  string $start      Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end        End date in 'Y-m-d' format (inclusive).
	 * @return int                Play count, or 0 on failure.
	 */
	public function get_podcast_plays_by_range( $podcast_id, $start, $end ) {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT SUM(plays)
				FROM {$this->daily_table}
				WHERE podcast_id = %d
				AND stat_date BETWEEN %s AND %s",
				absint( $podcast_id ),
				$start,
				$end
			)
		);
	}

	/**
	 * Return the total play count for a single episode over a date range.
	 *
	 * @since  1.0.0
	 * @param  int    $episode_id The episode post ID.
	 * @param  string $start      Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end        End date in 'Y-m-d' format (inclusive).
	 * @return int                Play count, or 0 on failure.
	 */
	public function get_episode_plays_by_range( $episode_id, $start, $end ) {
		global $wpdb;

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT SUM(plays)
				FROM {$this->daily_table}
				WHERE episode_id = %d
				AND stat_date BETWEEN %s AND %s",
				absint( $episode_id ),
				$start,
				$end
			)
		);
	}

	/**
	 * Return daily trend data (downloads + plays per day) for a date range.
	 *
	 * Optionally filtered to a single podcast or episode. Returns an array of
	 * associative arrays with keys: 'label' (Y-m-d), 'downloads', 'plays'.
	 *
	 * @since  1.0.0
	 * @param  string $start      Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end        End date in 'Y-m-d' format (inclusive).
	 * @param  int    $podcast_id Optional podcast post ID filter. Default 0 (global).
	 * @param  int    $episode_id Optional episode post ID filter. Default 0 (global).
	 * @return array              Rows ordered ascending by date.
	 */
	public function get_trend_data( $start, $end, $podcast_id = 0, $episode_id = 0 ) {
		global $wpdb;

		$where = 'WHERE stat_date BETWEEN %s AND %s';
		$args  = array( $start, $end );

		if ( $episode_id > 0 ) {
			$where   .= ' AND episode_id = %d';
			$args[]   = absint( $episode_id );
		} elseif ( $podcast_id > 0 ) {
			$where   .= ' AND podcast_id = %d';
			$args[]   = absint( $podcast_id );
		}

		$sql = "SELECT stat_date AS label, SUM(downloads) AS downloads, SUM(plays) AS plays
			FROM {$this->daily_table}
			{$where}
			GROUP BY stat_date
			ORDER BY stat_date ASC";
			
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Return hourly trend data (downloads + plays per hour) for a single day.
	 *
	 * Queries the event_logs table. Optionally filtered to a single podcast or
	 * episode. Returns an array of associative arrays with keys: 'label' (HH:00),
	 * 'downloads', 'plays'.
	 *
	 * @since  1.0.0
	 * @param  string $date       Date in 'Y-m-d' format.
	 * @param  int    $podcast_id Optional podcast post ID filter. Default 0.
	 * @param  int    $episode_id Optional episode post ID filter. Default 0.
	 * @return array              Rows ordered ascending by hour.
	 */
	public function get_trend_data_hourly( $date, $podcast_id = 0, $episode_id = 0 ) {
		global $wpdb;

		$where = 'WHERE created_at BETWEEN %s AND %s';
		$args  = array(
			self::EVENT_DOWNLOAD,
			self::EVENT_PLAY,
			$date . ' 00:00:00',
			$date . ' 23:59:59',
		);

		if ( $episode_id > 0 ) {
			$where   .= ' AND episode_id = %d';
			$args[]   = absint( $episode_id );
		} elseif ( $podcast_id > 0 ) {
			$where   .= ' AND podcast_id = %d';
			$args[]   = absint( $podcast_id );
		}

		$sql  = "SELECT
				LPAD(HOUR(created_at), 2, '0') AS hour_num,
				SUM(event_type = %d) AS downloads,
				SUM(event_type = %d) AS plays
			FROM {$this->events_table}
			{$where}
			GROUP BY HOUR(created_at)
			ORDER BY HOUR(created_at) ASC";
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		if ( ! is_array( $rows ) ) {
			return array();
		}

		$result = array();

		foreach ( $rows as $row ) {
			$result[] = array(
				'label'     => $row['hour_num'] . ':00',
				'downloads' => (int) $row['downloads'],
				'plays'     => (int) $row['plays'],
			);
		}

		return $result;
	}

	/**
	 * Return the top podcasts ranked by total downloads and plays for a date range.
	 *
	 * @since  1.0.0
	 * @param  string $start Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end   End date in 'Y-m-d' format (inclusive).
	 * @param  int    $limit Maximum number of results. Default 10.
	 * @return array         Array of objects with podcast_id, downloads, plays.
	 */
	public function get_top_podcasts_by_range( $start, $end, $limit = 10 ) {
		global $wpdb;

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT podcast_id, SUM(downloads) AS downloads, SUM(plays) AS plays, (SUM(downloads) + SUM(plays)) AS total
				FROM {$this->daily_table}
				WHERE stat_date BETWEEN %s AND %s
				GROUP BY podcast_id
				ORDER BY total DESC
				LIMIT %d",
				$start,
				$end,
				absint( $limit )
			)
		);
	}

	/**
	 * Return the top episodes ranked by total downloads and plays for a date range.
	 *
	 * Optionally filtered to a single podcast. Returns objects with episode_id,
	 * podcast_id, downloads, plays.
	 *
	 * @since  1.0.0
	 * @param  string $start      Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end        End date in 'Y-m-d' format (inclusive).
	 * @param  int    $podcast_id Optional podcast post ID filter. Default 0 (global).
	 * @param  int    $limit      Maximum number of results. Default 10.
	 * @return array              Array of objects with episode_id, podcast_id, downloads, plays.
	 */
	public function get_top_episodes_by_range( $start, $end, $podcast_id = 0, $limit = 10 ) {
		global $wpdb;

		if ( $podcast_id > 0 ) {
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT episode_id, podcast_id, SUM(downloads) AS downloads, SUM(plays) AS plays, (SUM(downloads) + SUM(plays)) AS total
					FROM {$this->daily_table}
					WHERE podcast_id = %d
					AND stat_date BETWEEN %s AND %s
					GROUP BY episode_id
					ORDER BY total DESC
					LIMIT %d",
					absint( $podcast_id ),
					$start,
					$end,
					absint( $limit )
				)
			);
		}

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT episode_id, podcast_id, SUM(downloads) AS downloads, SUM(plays) AS plays, (SUM(downloads) + SUM(plays)) AS total
				FROM {$this->daily_table}
				WHERE stat_date BETWEEN %s AND %s
				GROUP BY episode_id
				ORDER BY total DESC
				LIMIT %d",
				$start,
				$end,
				absint( $limit )
			)
		);
	}

	/**
	 * Return download or play event counts broken down by client for a date range.
	 *
	 * Queries the event_logs table. Optionally filtered to a single podcast or episode.
	 * Returns objects with client (string) and total_count (int).
	 *
	 * @since  1.0.0
	 * @param  int    $event_type EVENT_DOWNLOAD or EVENT_PLAY constant.
	 * @param  string $start      Start date in 'Y-m-d' format (inclusive).
	 * @param  string $end        End date in 'Y-m-d' format (inclusive).
	 * @param  int    $podcast_id Optional podcast post ID filter. Default 0.
	 * @param  int    $episode_id Optional episode post ID filter. Default 0.
	 * @return array              Array of objects with client and total_count.
	 */
	public function get_client_distribution_by_range( $event_type, $start, $end, $podcast_id = 0, $episode_id = 0 ) {
		global $wpdb;

		$where = 'WHERE event_type = %d AND created_at BETWEEN %s AND %s';
		$args  = array(
			absint( $event_type ),
			$start . ' 00:00:00',
			$end . ' 23:59:59',
		);

		if ( $episode_id > 0 ) {
			$where   .= ' AND episode_id = %d';
			$args[]   = absint( $episode_id );
		} elseif ( $podcast_id > 0 ) {
			$where   .= ' AND podcast_id = %d';
			$args[]   = absint( $podcast_id );
		}

		$sql = "SELECT client, COUNT(*) AS total_count
			FROM {$this->events_table}
			{$where}
			GROUP BY client
			ORDER BY total_count DESC";

		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * AJAX callback — log a play event from the embedded web player.
	 *
	 * Accepts POST: episode_id (int), security (nonce).
	 * Registered for both logged-in and non-logged-in users.
	 *
	 * @since  1.0.0
	 * @return void
	 */
	public function ajax_callback_track_play() {
		check_ajax_referer( 'aiopod_ajax_nonce', 'security' );

		$episode_id = absint( isset( $_POST['episode_id'] ) ? wp_unslash( $_POST['episode_id'] ) : 0 );

		if ( $episode_id <= 0 ) {
			wp_send_json_error();
		}

		$episode = get_post( $episode_id );

		if ( ! $episode || 'aiopod_episodes' !== $episode->post_type || 'publish' !== $episode->post_status ) {
			wp_send_json_error();
		}

		$podcast_id = (int) $episode->post_parent;

		$this->log_play( $podcast_id, $episode_id );

		wp_send_json_success();
	}

	/**
	 * Intercept a tracking URL request, redirect to the actual file, and log the download.
	 *
	 * Hooked to template_redirect at priority 0. Checks for the aiopod_download
	 * query var set by the /file/ rewrite rule, sends a 302 redirect immediately,
	 * then logs the download event after the client connection is closed where
	 * possible (PHP-FPM / LiteSpeed), or synchronously on other server types.
	 *
	 * @since  1.0.0
	 * @return void
	 */
	public function handle_download_request() {
		if ( ! get_query_var( 'aiopod_download' ) ) {
			return;
		}

		$episode_slug = get_query_var( 'aiopod_episode' );

		if ( empty( $episode_slug ) ) {
			return;
		}

		$episodes = get_posts( array(
			'name'           => sanitize_title( $episode_slug ),
			'post_type'      => 'aiopod_episodes',
			'post_status'    => 'publish',
			'posts_per_page' => 1,
			'no_found_rows'  => true,
		) );

		$episode = ! empty( $episodes ) ? $episodes[0] : null;

		if ( ! $episode ) {
			wp_die( esc_html__( 'Episode not found.', 'aio-podcast-studio' ), '', array( 'response' => 404 ) );
		}

		// Check user permissions before serving a restricted episode. Checked ahead of the
		// HEAD request handling below so that it covers every request method
		if ( ! aiopod_current_user_can( 'view_aiopod_podcast', $episode->ID ) ) {
			wp_die( esc_html__( 'You do not have permission to download this episode.', 'aio-podcast-studio' ), '', array( 'response' => 403 ) );
		}

		$file_url = get_post_meta( $episode->ID, 'episode_file', true );

		if ( empty( $file_url ) ) {
			wp_die( esc_html__( 'Episode file not found.', 'aio-podcast-studio' ), '', array( 'response' => 404 ) );
		}

		$podcast_id = (int) $episode->post_parent;
		$episode_id = $episode->ID;

		// Capture client data now, while we still have the original request context,
		// and pass them explicitly to log_event() through all three strategies.
		$client_ip = $this->get_client_ip();
		$client_ua = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';

		// HEAD requests are used by podcast directories and validators to confirm
		// a URL resolves. Redirect without logging — they are not real downloads.
		if ( 'HEAD' === $_SERVER['REQUEST_METHOD'] ) {
			header( 'Location: ' . esc_url_raw( $file_url ), true, 302 );
			exit;
		}

		// Send the redirect immediately — before any logging work.
		header( 'Location: ' . esc_url_raw( $file_url ), true, 302 );
		header( 'Content-Length: 0' );
		header( 'Connection: close' );

		// Discard any buffered output and flush headers to the client.
		while ( ob_get_level() > 0 ) {
			ob_end_clean();
		}

		flush();

		// Strategy 1 — PHP-FPM (Nginx, Apache + PHP-FPM).
		// fastcgi_finish_request() closes the FastCGI connection so the client
		// receives the redirect before any logging queries run, while this PHP
		// worker continues to execute. No secondary HTTP request needed.
		if ( function_exists( 'fastcgi_finish_request' ) ) {
			fastcgi_finish_request();
			$this->log_download( $podcast_id, $episode_id, $client_ip, $client_ua );
			exit;
		}

		// Strategy 2 — LiteSpeed.
		// litespeed_finish_request() is LiteSpeed's equivalent of fastcgi_finish_request().
		// It closes the connection to the client while letting PHP continue running.
		if ( function_exists( 'litespeed_finish_request' ) ) {
			litespeed_finish_request();
			$this->log_download( $podcast_id, $episode_id, $client_ip, $client_ua );
			exit;
		}

		// Strategy 3 — Apache + mod_php, IIS, and any other SAPI.
		// Log synchronously. The Connection: close + Content-Length: 0 headers
		// above already signal to well-behaved HTTP clients (all podcast apps
		// and directories) that the response is complete, so they follow the
		// redirect without waiting for PHP to exit. The ~5–20 ms logging
		// overhead is imperceptible and well within any directory's timeout.
		$this->log_download( $podcast_id, $episode_id, $client_ip, $client_ua );

		exit;
	}

	/**
	 * Update total_downloads, total_plays, and total_downloads_plays post meta
	 * for an episode and its parent podcast so WP_Query can sort on these values.
	 *
	 * Called after every successfully logged event.
	 *
	 * @since  1.0.0
	 * @access private
	 * @param  int $podcast_id The podcast post ID.
	 * @param  int $episode_id The episode post ID.
	 * @return void
	 */
	private function update_post_meta_counts( $podcast_id, $episode_id ) {
		global $wpdb;

		// Episode all-time totals.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT SUM(downloads) AS downloads, SUM(plays) AS plays
				FROM {$this->daily_table}
				WHERE episode_id = %d",
				absint( $episode_id )
			)
		);

		$ep_downloads = $row ? (int) $row->downloads : 0;
		$ep_plays     = $row ? (int) $row->plays     : 0;

		update_post_meta( $episode_id, 'total_downloads', $ep_downloads );
		update_post_meta( $episode_id, 'total_plays', $ep_plays );
		update_post_meta( $episode_id, 'total_downloads_plays', $ep_downloads + $ep_plays );

		// Podcast all-time totals — aggregate all child episodes.
		if ( $podcast_id > 0 ) {
			$row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT SUM(downloads) AS downloads, SUM(plays) AS plays
					FROM {$this->daily_table}
					WHERE podcast_id = %d",
					absint( $podcast_id )
				)
			);

			$pod_downloads = $row ? (int) $row->downloads : 0;
			$pod_plays     = $row ? (int) $row->plays     : 0;

			update_post_meta( $podcast_id, 'total_downloads', $pod_downloads );
			update_post_meta( $podcast_id, 'total_plays', $pod_plays );
			update_post_meta( $podcast_id, 'total_downloads_plays', $pod_downloads + $pod_plays );
		}
	}

	/**
	 * Delete all statistics rows for an episode and refresh the parent podcast's
	 * post meta totals. Hooked to before_delete_post.
	 *
	 * @since  1.0.0
	 * @param  int $post_id The post ID being deleted.
	 * @return void
	 */
	public function delete_episode_stats( $post_id ) {
		global $wpdb;

		if ( 'aiopod_episodes' !== get_post_type( $post_id ) ) {
			return;
		}

		// When the parent podcast is being deleted, skip per-episode cleanup entirely.
		// delete_podcast_stats() will remove all rows in two bulk DELETEs by podcast_id,
		// and the podcast post meta is discarded with the post.
		if ( defined( 'AIOPOD_DELETING_PODCAST' ) && AIOPOD_DELETING_PODCAST ) {
			return;
		}

		$episode_id = absint( $post_id );
		$podcast_id = (int) get_post_field( 'post_parent', $episode_id );

		$wpdb->delete( $this->events_table, array( 'episode_id' => $episode_id ), array( '%d' ) );
		$wpdb->delete( $this->daily_table,  array( 'episode_id' => $episode_id ), array( '%d' ) );

		// Recompute podcast totals from remaining episodes.
		if ( $podcast_id > 0 ) {
			$row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT SUM(downloads) AS downloads, SUM(plays) AS plays
					FROM {$this->daily_table}
					WHERE podcast_id = %d",
					$podcast_id
				)
			);

			$downloads = $row ? (int) $row->downloads : 0;
			$plays     = $row ? (int) $row->plays     : 0;

			update_post_meta( $podcast_id, 'total_downloads', $downloads );
			update_post_meta( $podcast_id, 'total_plays', $plays );
			update_post_meta( $podcast_id, 'total_downloads_plays', $downloads + $plays );
		}
	}

	/**
	 * Delete all statistics rows for a podcast. Catches any rows not already
	 * removed by delete_episode_stats() when child episodes were deleted.
	 * Hooked to before_delete_post.
	 *
	 * @since  1.0.0
	 * @param  int $post_id The post ID being deleted.
	 * @return void
	 */
	public function delete_podcast_stats( $post_id ) {
		global $wpdb;

		if ( 'aiopod_podcasts' !== get_post_type( $post_id ) ) {
			return;
		}

		$podcast_id = absint( $post_id );

		$wpdb->delete( $this->events_table, array( 'podcast_id' => $podcast_id ), array( '%d' ) );
		$wpdb->delete( $this->daily_table,  array( 'podcast_id' => $podcast_id ), array( '%d' ) );
	}

	/**
	 * Schedule or unschedule the daily statistics log cleanup cron job.
	 *
	 * Hooked to the `wp` action so the cron state is kept in sync with the
	 * current setting on every page load. Schedules the job when a retention
	 * period is configured; removes it when the setting is empty.
	 *
	 * @since  1.0.0
	 * @return void
	 */
	public function maybe_schedule_cleanup() {
		$cleanup_settings = aiopod_get_option( 'aiopod_cleanup_settings' );
		
		$retention = isset( $cleanup_settings['stats_log_retention'] ) ? absint( $cleanup_settings['stats_log_retention'] ) : 0;

		if ( $retention > 0 ) {
			if ( ! wp_next_scheduled( 'aiopod_cleanup_statistics_logs' ) ) {
				wp_schedule_event( time(), 'daily', 'aiopod_cleanup_statistics_logs' );
			}
		} else {
			wp_clear_scheduled_hook( 'aiopod_cleanup_statistics_logs' );
		}
	}

	/**
	 * WP Cron callback — delete raw event log records older than the
	 * configured retention period.
	 *
	 * Reads the retention setting at run time so changes take effect on the
	 * next scheduled run without rescheduling the job.
	 *
	 * @since  1.0.0
	 * @return void
	 */
	public function run_cleanup_cron() {
		$cleanup_settings = aiopod_get_option( 'aiopod_cleanup_settings' );
		
		$retention = isset( $cleanup_settings['stats_log_retention'] ) ? absint( $cleanup_settings['stats_log_retention'] ) : 0;

		if ( $retention > 0 ) {
			$this->cleanup_old_logs( $retention );
		}
	}

	/**
	 * Delete raw event log records older than the retention period.
	 *
	 * Raw logs grow continuously. This method prunes old rows while leaving
	 * the pre-aggregated daily stats table intact. Intended to be called from
	 * a scheduled WP-Cron job registered elsewhere in the plugin.
	 *
	 * @since  1.0.0
	 * @param  int $days Number of days of logs to retain. Default 365.
	 * @return int       Number of rows deleted, or 0 on failure.
	 */
	public function cleanup_old_logs( $days = 365 ) {
		global $wpdb;

		$days = absint( $days );

		// Filter the number of days of raw event logs to retain.
		$days = (int) apply_filters( 'aiopod_statistics_log_retention_days', $days );

		if ( $days <= 0 ) {
			return 0;
		}

		// Cutoff in site timezone to match the timezone used when storing created_at.
		$cutoff = wp_date( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );

		return (int) $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$this->events_table}
				WHERE created_at < %s",
				$cutoff
			)
		);
	}
	
}