<?php
/**
 * Read side of analytics: totals, series and KPIs from the daily aggregates.
 *
 * @package KarmaDocs
 */

namespace KarmaDocs\Analytics;

defined( 'ABSPATH' ) || exit;

/**
 * Every dashboard reads `kdocs_stats_daily` / `kdocs_search_daily` through this
 * class, never the raw events (those are only kept for the retention period).
 */
class Stats {

	/**
	 * Metric columns of the daily stats table.
	 */
	const COLUMNS = array( 'views', 'visitors', 'sessions', 'reads_50', 'reads_100', 'read_secs', 'helpful_yes', 'helpful_no', 'contacts', 'contact_sessions' );

	/**
	 * Date range ending today, plus the previous range of the same length.
	 *
	 * @param int $days Days (1–365).
	 * @return array{from:string,to:string,prev_from:string,prev_to:string,days:int}
	 */
	public static function range( $days ) {
		$days  = max( 1, min( 365, (int) $days ) );
		$today = current_time( 'Y-m-d' );
		$from  = gmdate( 'Y-m-d', strtotime( $today . ' -' . ( $days - 1 ) . ' days' ) );
		return array(
			'from'      => $from,
			'to'        => $today,
			'prev_from' => gmdate( 'Y-m-d', strtotime( $from . ' -' . $days . ' days' ) ),
			'prev_to'   => gmdate( 'Y-m-d', strtotime( $from . ' -1 day' ) ),
			'days'      => $days,
		);
	}

	/**
	 * Summed metrics over a date range.
	 *
	 * @param string $from    Y-m-d.
	 * @param string $to      Y-m-d.
	 * @param int    $kb      Knowledge base (0 = site).
	 * @param int    $article Article (0 = whole KB / site).
	 * @return array<string,int>
	 */
	public static function totals( $from, $to, $kb = 0, $article = 0 ) {
		global $wpdb;
		$table = esc_sql( Store::table( 'stats_daily' ) );
		$sums  = esc_sql( implode( ', ', array_map( static fn( $c ) => "COALESCE(SUM({$c}), 0) AS {$c}", self::COLUMNS ) ) );
		$where = $article ? $wpdb->prepare( 'article_id = %d', $article ) : $wpdb->prepare( 'kb_id = %d AND article_id = 0', $kb );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT {$sums} FROM {$table} WHERE day BETWEEN %s AND %s AND {$where}", $from, $to ), ARRAY_A );
		return array_map( 'intval', (array) $row );
	}

	/**
	 * Daily values of some metrics, one point per day (missing days are zero).
	 *
	 * @param string   $from    Y-m-d.
	 * @param string   $to      Y-m-d.
	 * @param string[] $metrics Columns.
	 * @param int      $kb      Knowledge base (0 = site).
	 * @param int      $article Article (0 = whole KB / site).
	 * @return array[] [ { date, metric: value, … } ]
	 */
	public static function series( $from, $to, array $metrics, $kb = 0, $article = 0 ) {
		global $wpdb;
		$metrics = array_values( array_intersect( $metrics, self::COLUMNS ) );
		$table   = esc_sql( Store::table( 'stats_daily' ) );
		$select  = esc_sql( implode( ', ', array_map( static fn( $c ) => "SUM({$c}) AS {$c}", $metrics ) ) );
		$where   = $article ? $wpdb->prepare( 'article_id = %d', $article ) : $wpdb->prepare( 'kb_id = %d AND article_id = 0', $kb );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT day, {$select} FROM {$table} WHERE day BETWEEN %s AND %s AND {$where} GROUP BY day", $from, $to ), OBJECT_K );

		$out = array();
		for ( $day = $from; $day <= $to; $day = gmdate( 'Y-m-d', strtotime( $day . ' +1 day' ) ) ) {
			$point = array( 'date' => $day );
			foreach ( $metrics as $metric ) {
				$point[ $metric ] = isset( $rows[ $day ] ) ? (int) $rows[ $day ]->$metric : 0;
			}
			$out[] = $point;
		}
		return $out;
	}

	/**
	 * Per-article totals over a range.
	 *
	 * @param string $from Y-m-d.
	 * @param string $to   Y-m-d.
	 * @param int    $kb   Knowledge base (0 = all).
	 * @return array<int,array<string,int>> Article id => metrics.
	 */
	public static function by_article( $from, $to, $kb = 0 ) {
		global $wpdb;
		$table = esc_sql( Store::table( 'stats_daily' ) );
		$sums  = esc_sql( implode( ', ', array_map( static fn( $c ) => "SUM({$c}) AS {$c}", self::COLUMNS ) ) );
		$kb_w  = $kb ? $wpdb->prepare( ' AND kb_id = %d', $kb ) : '';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $kb_w is prepared above.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT article_id, {$sums} FROM {$table} WHERE day BETWEEN %s AND %s AND article_id > 0{$kb_w} GROUP BY article_id", $from, $to ), ARRAY_A );
		$out  = array();
		foreach ( $rows as $row ) {
			$id         = (int) $row['article_id'];
			$out[ $id ] = array_map( 'intval', array_diff_key( $row, array( 'article_id' => 1 ) ) );
		}
		return $out;
	}

	/**
	 * Search totals over a range.
	 *
	 * @param string $from Y-m-d.
	 * @param string $to   Y-m-d.
	 * @param int    $kb   Knowledge base (0 = all).
	 * @return array<string,int>
	 */
	public static function search_totals( $from, $to, $kb = 0 ) {
		global $wpdb;
		$table = esc_sql( Store::table( 'search_daily' ) );
		$kb_w  = $kb ? $wpdb->prepare( ' AND kb_id = %d', $kb ) : '';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $kb_w is prepared above.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT COALESCE(SUM(searches),0) AS searches, COALESCE(SUM(zero_results),0) AS zero_results, COALESCE(SUM(clicks),0) AS clicks, COALESCE(SUM(successes),0) AS successes, COALESCE(SUM(refinements),0) AS refinements, COALESCE(SUM(click_pos),0) AS click_pos FROM {$table} WHERE day BETWEEN %s AND %s{$kb_w}", $from, $to ), ARRAY_A );
		return array_map( 'intval', (array) $row );
	}

	/**
	 * First day with click data (imported search history has none).
	 *
	 * @param string $from Requested start.
	 * @return string Start for click-based rates.
	 */
	public static function rates_from( $from ) {
		$since = (string) get_option( Rollup::SINCE_OPTION, '' );
		return $since && $since > $from ? $since : $from;
	}

	/**
	 * Per-term search metrics over a range.
	 *
	 * @param string $from  Y-m-d.
	 * @param string $to    Y-m-d.
	 * @param int    $kb    Knowledge base (0 = all).
	 * @param int    $limit Rows.
	 * @return array[]
	 */
	public static function terms( $from, $to, $kb = 0, $limit = 200 ) {
		global $wpdb;
		$table = esc_sql( Store::table( 'search_daily' ) );
		$kb_w  = $kb ? $wpdb->prepare( ' AND kb_id = %d', $kb ) : '';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $kb_w is prepared above.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT term, SUM(searches) AS searches, SUM(zero_results) AS zero_results, SUM(clicks) AS clicks, SUM(successes) AS successes, SUM(refinements) AS refinements, SUM(click_pos) AS click_pos FROM {$table} WHERE day BETWEEN %s AND %s{$kb_w} GROUP BY term ORDER BY searches DESC LIMIT %d", $from, $to, $limit ), ARRAY_A );
		return array_map(
			static function ( $row ) {
				$row      = array_map( static fn( $v ) => is_numeric( $v ) ? (int) $v : $v, $row );
				$searches = max( 1, $row['searches'] );
				return $row + array(
					'ctr'          => round( 100 * $row['clicks'] / $searches ),
					'success_rate' => round( 100 * $row['successes'] / $searches ),
					'avg_position' => $row['clicks'] ? round( $row['click_pos'] / $row['clicks'], 1 ) : null,
				);
			},
			$rows
		);
	}

	/**
	 * Lower bound of the Wilson score interval (95%) of the share of helpful votes.
	 * Ranks articles fairly when some have only a handful of votes.
	 *
	 * @param int $yes Helpful.
	 * @param int $no  Not helpful.
	 * @return float 0–1.
	 */
	public static function wilson( $yes, $no ) {
		$n = $yes + $no;
		if ( ! $n ) {
			return 0.0;
		}
		$z = 1.96;
		$p = $yes / $n;
		return ( $p + $z * $z / ( 2 * $n ) - $z * sqrt( ( $p * ( 1 - $p ) + $z * $z / ( 4 * $n ) ) / $n ) ) / ( 1 + $z * $z / $n );
	}

	/**
	 * Percentage, or null without a base.
	 *
	 * @param int|float $part  Part.
	 * @param int|float $whole Whole.
	 * @return int|null
	 */
	public static function percent( $part, $whole ) {
		return $whole ? (int) round( 100 * $part / $whole ) : null;
	}

	/**
	 * Change in percent between two values (null when there is no previous value).
	 *
	 * @param int|float|null $current  Current.
	 * @param int|float|null $previous Previous.
	 * @return int|null
	 */
	public static function change( $current, $previous ) {
		if ( null === $current || null === $previous || ! $previous ) {
			return null;
		}
		return (int) round( 100 * ( $current - $previous ) / $previous );
	}

	/**
	 * KPI tiles: value, previous period, change and a daily sparkline.
	 *
	 * Free: visitors, views, helpfulness. Pro adds search success and self-service.
	 *
	 * @param int  $days Days.
	 * @param int  $kb   Knowledge base (0 = site).
	 * @param bool $pro  Include Pro KPIs.
	 * @return array[]
	 */
	public static function kpis( $days = 30, $kb = 0, $pro = false ) {
		Rollup::refresh_today();
		$range  = self::range( $days );
		$now    = self::totals( $range['from'], $range['to'], $kb );
		$before = self::totals( $range['prev_from'], $range['prev_to'], $kb );
		$series = self::series( $range['from'], $range['to'], array( 'views', 'visitors', 'helpful_yes', 'helpful_no', 'sessions', 'contact_sessions' ), $kb );

		$helpful = static fn( $t ) => self::percent( $t['helpful_yes'], $t['helpful_yes'] + $t['helpful_no'] );
		$self    = static fn( $t ) => $t['sessions'] ? self::percent( $t['sessions'] - $t['contact_sessions'], $t['sessions'] ) : null;

		$kpis = array(
			array(
				'id'       => 'visitors',
				'value'    => $now['visitors'],
				'previous' => $before['visitors'],
				'spark'    => wp_list_pluck( $series, 'visitors' ),
			),
			array(
				'id'       => 'views',
				'value'    => $now['views'],
				'previous' => $before['views'],
				'spark'    => wp_list_pluck( $series, 'views' ),
			),
			array(
				'id'       => 'helpful',
				'value'    => $helpful( $now ),
				'previous' => $helpful( $before ),
				'unit'     => '%',
				'spark'    => array_map( static fn( $p ) => self::percent( $p['helpful_yes'], $p['helpful_yes'] + $p['helpful_no'] ), $series ),
			),
		);

		if ( $pro ) {
			$search      = self::search_totals( self::rates_from( $range['from'] ), $range['to'], $kb );
			$search_prev = self::search_totals( self::rates_from( $range['prev_from'] ), $range['prev_to'], $kb );
			$kpis[]      = array(
				'id'       => 'search_success',
				'value'    => self::percent( $search['successes'], $search['searches'] ),
				'previous' => self::percent( $search_prev['successes'], $search_prev['searches'] ),
				'unit'     => '%',
				'spark'    => array(),
			);
			$kpis[]      = array(
				'id'       => 'self_service',
				'value'    => $self( $now ),
				'previous' => $self( $before ),
				'unit'     => '%',
				'spark'    => array_map( static fn( $p ) => $p['sessions'] ? self::percent( $p['sessions'] - $p['contact_sessions'], $p['sessions'] ) : null, $series ),
			);
		}

		foreach ( $kpis as &$kpi ) {
			$kpi['change'] = self::change( $kpi['value'], $kpi['previous'] );
		}
		unset( $kpi );
		return $kpis;
	}
}