<?php
/**
 * DataProvider
 *
 * All DB queries for the statistics dashboard.
 * Results are cached with short-lived transients to avoid repeated
 * aggregate queries on every dashboard load.
 *
 * @package Relatedly\Admin\Statistics
 * @since   2.0.0
 * @since   2.3.0 Added display-type breakdown, top sources, post analytics,
 *                period comparison, and daily counts by display type.
 */

namespace Relatedly\Admin\Statistics;

use Relatedly\Core\Cache;

defined( 'ABSPATH' ) || exit;

/**
 * Provides cached statistics for dashboards and reports.
 *
 * @since 2.0.0
 * @since 2.3.0 Added advanced analytics queries.
 */
class DataProvider {

	/**
	 * Cache service.
	 *
	 * @since 2.0.0
	 * @var Cache
	 */
	private Cache $cache;

	/** Statistics cache TTL — 10 minutes. */
	const CACHE_TTL = 600;

	/**
	 * Create the statistics data provider.
	 *
	 * @since 2.0.0
	 * @param Cache $cache Cache service.
	 */
	public function __construct( Cache $cache ) {
		$this->cache = $cache;
	}

	/**
	 * Get summary stats for a date range.
	 *
	 * @since 2.0.0
	 * @since 2.3.0 Added inline/end_content/floating breakdown to the result.
	 * @param string $start_date Y-m-d.
	 * @param string $end_date   Y-m-d.
	 * @return array{ total_clicks: int, total_impressions: int, ctr: float, breakdown: array<string, array{ clicks: int, impressions: int, ctr: float }> }
	 */
	public function get_summary( string $start_date, string $end_date ): array {
		$cache_key = 'stats_summary_' . md5( $start_date . $end_date );
		$cached    = $this->cache->get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );

		// The table name is built from $wpdb->prefix and escaped above; query values remain prepared.
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS total_clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS total_impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s",
				$start_date,
				$end_date
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$clicks      = (int) ( $row['total_clicks'] ?? 0 );
		$impressions = (int) ( $row['total_impressions'] ?? 0 );
		$ctr         = $impressions > 0 ? round( ( $clicks / $impressions ) * 100, 2 ) : 0.0;

		$result = [
			'total_clicks'      => $clicks,
			'total_impressions' => $impressions,
			'ctr'               => $ctr,
			'breakdown'         => $this->get_summary_by_display_type( $start_date, $end_date ),
		];

		$this->cache->set( $cache_key, $result, self::CACHE_TTL );
		return $result;
	}

	/**
	 * Get summary stats broken down by display type.
	 *
	 * @since 2.3.0
	 * @param string $start_date Y-m-d.
	 * @param string $end_date   Y-m-d.
	 * @return array<string, array{ clicks: int, impressions: int, ctr: float }>
	 */
	public function get_summary_by_display_type( string $start_date, string $end_date ): array {
		$cache_key = 'stats_breakdown_' . md5( $start_date . $end_date );
		$cached    = $this->cache->get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
				display_type,
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			GROUP BY display_type",
				$start_date,
				$end_date
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$types  = [ 'inline', 'end_content', 'floating' ];
		$result = [];
		foreach ( $types as $type ) {
			$result[ $type ] = [
				'clicks'      => 0,
				'impressions' => 0,
				'ctr'         => 0.0,
			];
		}

		foreach ( $rows as $row ) {
			$type                           = (string) $row['display_type'];
			$clicks                         = (int) $row['clicks'];
			$impressions                    = (int) $row['impressions'];
			$result[ $type ]['clicks']      = $clicks;
			$result[ $type ]['impressions'] = $impressions;
			$result[ $type ]['ctr']         = $impressions > 0 ? round( ( $clicks / $impressions ) * 100, 2 ) : 0.0;
		}

		$this->cache->set( $cache_key, $result, self::CACHE_TTL );
		return $result;
	}

	/**
	 * Get top clicked posts for a date range.
	 *
	 * @since 2.0.0
	 * @since 2.3.0 Added impressions and CTR per post.
	 * @param string $start_date Y-m-d.
	 * @param string $end_date   Y-m-d.
	 * @param int    $limit      Number of rows to return.
	 * @return array<int, array{ post_id: int, title: string, clicks: int, impressions: int, ctr: float }>
	 */
	public function get_top_clicked( string $start_date, string $end_date, int $limit = 10 ): array {
		$cache_key = 'stats_top_clicked_' . md5( $start_date . $end_date . $limit );
		$cached    = $this->cache->get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
				clicked_post_id,
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			GROUP BY clicked_post_id
			HAVING clicks > 0
			ORDER BY clicks DESC
			LIMIT %d",
				$start_date,
				$end_date,
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$result = [];
		foreach ( $rows as $row ) {
			$post_id     = (int) $row['clicked_post_id'];
			$clicks      = (int) $row['clicks'];
			$impressions = (int) $row['impressions'];
			$title       = (string) get_the_title( $post_id );

			$result[] = [
				'post_id'     => $post_id,
				'title'       => '' !== $title ? $title : __( '(deleted post)', 'relatedly' ),
				'clicks'      => $clicks,
				'impressions' => $impressions,
				'ctr'         => $impressions > 0 ? round( ( $clicks / $impressions ) * 100, 2 ) : 0.0,
			];
		}

		$this->cache->set( $cache_key, $result, self::CACHE_TTL );
		return $result;
	}

	/**
	 * Get top source posts (posts that generated the most clicks to related posts).
	 *
	 * @since 2.3.0
	 * @param string $start_date Y-m-d.
	 * @param string $end_date   Y-m-d.
	 * @param int    $limit      Number of rows to return.
	 * @return array<int, array{ post_id: int, title: string, clicks: int, impressions: int, ctr: float }>
	 */
	public function get_top_sources( string $start_date, string $end_date, int $limit = 10 ): array {
		$cache_key = 'stats_top_sources_' . md5( $start_date . $end_date . $limit );
		$cached    = $this->cache->get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
				source_post_id,
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			GROUP BY source_post_id
			HAVING clicks > 0
			ORDER BY clicks DESC
			LIMIT %d",
				$start_date,
				$end_date,
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$result = [];
		foreach ( $rows as $row ) {
			$post_id     = (int) $row['source_post_id'];
			$clicks      = (int) $row['clicks'];
			$impressions = (int) $row['impressions'];
			$title       = (string) get_the_title( $post_id );

			$result[] = [
				'post_id'     => $post_id,
				'title'       => '' !== $title ? $title : __( '(deleted post)', 'relatedly' ),
				'clicks'      => $clicks,
				'impressions' => $impressions,
				'ctr'         => $impressions > 0 ? round( ( $clicks / $impressions ) * 100, 2 ) : 0.0,
			];
		}

		$this->cache->set( $cache_key, $result, self::CACHE_TTL );
		return $result;
	}

	/**
	 * Get daily click + impression counts for a date range (for charts).
	 *
	 * @since 2.0.0
	 * @param string $start_date Y-m-d.
	 * @param string $end_date   Y-m-d.
	 * @return array<int, array{ date: string, clicks: int, impressions: int }>
	 */
	public function get_daily_counts( string $start_date, string $end_date ): array {
		$cache_key = 'stats_daily_' . md5( $start_date . $end_date );
		$cached    = $this->cache->get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
				DATE( event_date ) AS date,
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			GROUP BY DATE( event_date )
			ORDER BY date ASC",
				$start_date,
				$end_date
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$result = array_map(
			fn( $r ) => [
				'date'        => $r['date'],
				'clicks'      => (int) $r['clicks'],
				'impressions' => (int) $r['impressions'],
			],
			$rows
		);

		$this->cache->set( $cache_key, $result, self::CACHE_TTL );
		return $result;
	}

	/**
	 * Get daily click counts split by display type.
	 *
	 * @since 2.3.0
	 * @param string $start_date Y-m-d.
	 * @param string $end_date   Y-m-d.
	 * @return array<int, array{ date: string, inline: int, end_content: int, floating: int }>
	 */
	public function get_daily_counts_by_display_type( string $start_date, string $end_date ): array {
		$cache_key = 'stats_daily_by_type_' . md5( $start_date . $end_date );
		$cached    = $this->cache->get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
				DATE( event_date ) AS date,
				display_type,
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END ) AS clicks
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			GROUP BY DATE( event_date ), display_type
			ORDER BY date ASC",
				$start_date,
				$end_date
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$grouped = [];
		foreach ( $rows as $row ) {
			$date = $row['date'];
			if ( ! isset( $grouped[ $date ] ) ) {
				$grouped[ $date ] = [
					'date'        => $date,
					'inline'      => 0,
					'end_content' => 0,
					'floating'    => 0,
				];
			}
			$type = (string) $row['display_type'];
			if ( isset( $grouped[ $date ][ $type ] ) ) {
				$grouped[ $date ][ $type ] = (int) $row['clicks'];
			}
		}

		$result = array_values( $grouped );
		$this->cache->set( $cache_key, $result, self::CACHE_TTL );
		return $result;
	}

	/**
	 * Get detailed analytics for a single post.
	 *
	 * @since 2.3.0
	 * @param int    $post_id    Post ID.
	 * @param string $start_date Y-m-d.
	 * @param string $end_date   Y-m-d.
	 * @return array{ clicked: array{ clicks: int, impressions: int, ctr: float }, source: array{ clicks: int, impressions: int, ctr: float }, top_related: array<int, array{ post_id: int, title: string, clicks: int, impressions: int, ctr: float }>, daily: array<int, array{ date: string, clicks: int, impressions: int }> }
	 */
	public function get_post_analytics( int $post_id, string $start_date, string $end_date ): array {
		$cache_key = 'stats_post_' . md5( $post_id . $start_date . $end_date );
		$cached    = $this->cache->get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$clicked = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			  AND clicked_post_id = %d",
				$start_date,
				$end_date,
				$post_id
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$source = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			  AND source_post_id = %d",
				$start_date,
				$end_date,
				$post_id
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$top_related_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
				clicked_post_id,
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			  AND source_post_id = %d
			GROUP BY clicked_post_id
			HAVING clicks > 0
			ORDER BY clicks DESC
			LIMIT 10",
				$start_date,
				$end_date,
				$post_id
			),
			ARRAY_A
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$daily_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
				DATE( event_date ) AS date,
				SUM( CASE WHEN event_type = 'click' THEN 1 ELSE 0 END )      AS clicks,
				SUM( CASE WHEN event_type = 'impression' THEN 1 ELSE 0 END ) AS impressions
			FROM `{$table}`
			WHERE DATE( event_date ) BETWEEN %s AND %s
			  AND ( source_post_id = %d OR clicked_post_id = %d )
			GROUP BY DATE( event_date )
			ORDER BY date ASC",
				$start_date,
				$end_date,
				$post_id,
				$post_id
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$top_related = [];
		foreach ( $top_related_rows as $row ) {
			$related_id  = (int) $row['clicked_post_id'];
			$clicks      = (int) $row['clicks'];
			$impressions = (int) $row['impressions'];
			$title       = (string) get_the_title( $related_id );

			$top_related[] = [
				'post_id'     => $related_id,
				'title'       => '' !== $title ? $title : __( '(deleted post)', 'relatedly' ),
				'clicks'      => $clicks,
				'impressions' => $impressions,
				'ctr'         => $impressions > 0 ? round( ( $clicks / $impressions ) * 100, 2 ) : 0.0,
			];
		}

		$daily = array_map(
			fn( $r ) => [
				'date'        => $r['date'],
				'clicks'      => (int) $r['clicks'],
				'impressions' => (int) $r['impressions'],
			],
			$daily_rows
		);

		$clicked_clicks      = (int) ( $clicked['clicks'] ?? 0 );
		$clicked_impressions = (int) ( $clicked['impressions'] ?? 0 );
		$source_clicks       = (int) ( $source['clicks'] ?? 0 );
		$source_impressions  = (int) ( $source['impressions'] ?? 0 );

		$result = [
			'clicked'     => [
				'clicks'      => $clicked_clicks,
				'impressions' => $clicked_impressions,
				'ctr'         => $clicked_impressions > 0 ? round( ( $clicked_clicks / $clicked_impressions ) * 100, 2 ) : 0.0,
			],
			'source'      => [
				'clicks'      => $source_clicks,
				'impressions' => $source_impressions,
				'ctr'         => $source_impressions > 0 ? round( ( $source_clicks / $source_impressions ) * 100, 2 ) : 0.0,
			],
			'top_related' => $top_related,
			'daily'       => $daily,
		];

		$this->cache->set( $cache_key, $result, self::CACHE_TTL );
		return $result;
	}

	/**
	 * Compare two date ranges.
	 *
	 * @since 2.3.0
	 * @param string $period1_start Y-m-d.
	 * @param string $period1_end   Y-m-d.
	 * @param string $period2_start Y-m-d.
	 * @param string $period2_end   Y-m-d.
	 * @return array{ period1: array{ label: string, summary: array }, period2: array{ label: string, summary: array }, differences: array{ clicks: int, clicks_percent: float, impressions: int, impressions_percent: float, ctr: float } }
	 */
	public function get_comparison( string $period1_start, string $period1_end, string $period2_start, string $period2_end ): array {
		$summary1 = $this->get_summary( $period1_start, $period1_end );
		$summary2 = $this->get_summary( $period2_start, $period2_end );

		$clicks_diff      = $summary2['total_clicks'] - $summary1['total_clicks'];
		$impressions_diff = $summary2['total_impressions'] - $summary1['total_impressions'];
		$ctr_diff         = $summary2['ctr'] - $summary1['ctr'];

		return [
			'period1'     => [
				'label'   => $period1_start . ' — ' . $period1_end,
				'summary' => $summary1,
			],
			'period2'     => [
				'label'   => $period2_start . ' — ' . $period2_end,
				'summary' => $summary2,
			],
			'differences' => [
				'clicks'              => $clicks_diff,
				'clicks_percent'      => $summary1['total_clicks'] > 0
					? round( ( $clicks_diff / $summary1['total_clicks'] ) * 100, 2 )
					: 0.0,
				'impressions'         => $impressions_diff,
				'impressions_percent' => $summary1['total_impressions'] > 0
					? round( ( $impressions_diff / $summary1['total_impressions'] ) * 100, 2 )
					: 0.0,
				'ctr'                 => $ctr_diff,
			],
		];
	}

	/**
	 * Get total row count in the tracking table (for the "purge" UI).
	 *
	 * @since 2.0.0
	 */
	public function get_total_rows(): int {
		global $wpdb;
		$table = esc_sql( $wpdb->prefix . 'relatedly_clicks' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
	}
}
