<?php
/**
 * Read side: everything the dashboard, the widgets and the REST API need,
 * served entirely from the rollup table (fast).
 *
 * "Realtime" (last 30 min) is the one method that touches the raw hits table.
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class SXANL_Stats_Query {

	/** Don't re-roll "today" more often than this (seconds). */
	const FRESH_THROTTLE = 45;

	/**
	 * Bring the rollup for today (and yesterday, for the midnight boundary)
	 * up to date before serving a dashboard read, so figures are live to
	 * within a minute rather than the hourly cron. Throttled with a transient
	 * so concurrent dashboard loads don't each trigger a rebuild.
	 */
	public static function ensure_fresh() {
		if ( get_transient( 'sxanl_fresh_lock' ) ) {
			return;
		}
		set_transient( 'sxanl_fresh_lock', 1, self::FRESH_THROTTLE );
		SXANL_Rollup::rebuild_day( sxanl_site_date() );
		SXANL_Rollup::rebuild_day( sxanl_site_date( time() - DAY_IN_SECONDS ) );
	}

	/**
	 * Resolve a range keyword or explicit dates to [from, to] (Y-m-d) plus the
	 * matching previous period for deltas.
	 *
	 * @param string $range 'today'|'7d'|'28d'|'90d'|'12mo'|'custom'
	 * @param string $from  Y-m-d when custom.
	 * @param string $to    Y-m-d when custom.
	 * @return array{from:string,to:string,prev_from:string,prev_to:string,days:int}
	 */
	public static function resolve_range( $range = '28d', $from = '', $to = '' ) {
		$today = sxanl_site_date();

		$map = array(
			'today' => 0,
			'7d'    => 6,
			'28d'   => 27,
			'90d'   => 89,
			'12mo'  => 364,
		);

		if ( 'custom' === $range && $from && $to ) {
			$f = $from;
			$t = $to;
		} else {
			$back = isset( $map[ $range ] ) ? $map[ $range ] : 27;
			$f    = sxanl_site_date( strtotime( $today ) - $back * DAY_IN_SECONDS );
			$t    = $today;
		}

		$span      = max( 1, (int) round( ( strtotime( $t ) - strtotime( $f ) ) / DAY_IN_SECONDS ) + 1 );
		$prev_to   = sxanl_site_date( strtotime( $f ) - DAY_IN_SECONDS );
		$prev_from = sxanl_site_date( strtotime( $prev_to ) - ( $span - 1 ) * DAY_IN_SECONDS );

		return array(
			'from'      => $f,
			'to'        => $t,
			'prev_from' => $prev_from,
			'prev_to'   => $prev_to,
			'days'      => $span,
			// A single-day range gets an hour-by-hour trend line instead of one dot.
			'unit'      => ( 1 === $span ) ? 'hour' : 'day',
		);
	}

	/**
	 * Headline totals for a range, with % change vs the previous period.
	 *
	 * @param array $r resolve_range() output.
	 * @return array
	 */
	public static function totals( array $r ) {
		$cur  = self::sum_totals( $r['from'], $r['to'] );
		$prev = self::sum_totals( $r['prev_from'], $r['prev_to'] );

		$mk = function ( $key, $invert = false ) use ( $cur, $prev ) {
			$c = (float) $cur[ $key ];
			$p = (float) $prev[ $key ];
			$delta = ( $p > 0 ) ? round( ( $c - $p ) / $p * 100, 1 ) : null;
			return array(
				'value'  => $key === 'avg_engaged' || $key === 'bounce_rate' ? round( $c, $key === 'bounce_rate' ? 1 : 0 ) : (int) $c,
				'prev'   => $key === 'avg_engaged' || $key === 'bounce_rate' ? round( $p, $key === 'bounce_rate' ? 1 : 0 ) : (int) $p,
				'change' => $delta,
				'better' => null === $delta ? null : ( $invert ? $delta < 0 : $delta > 0 ),
			);
		};

		return array(
			'visitors'    => $mk( 'visitors' ),
			'sessions'    => $mk( 'sessions' ),
			'pageviews'   => $mk( 'pageviews' ),
			'avg_engaged' => $mk( 'avg_engaged' ),
			'bounce_rate' => $mk( 'bounce_rate', true ),
			'range'       => array( 'from' => $r['from'], 'to' => $r['to'], 'days' => $r['days'] ),
		);
	}

	/**
	 * @return array{visitors:int,sessions:int,pageviews:int,new_visitors:int,bounces:int,avg_engaged:float,bounce_rate:float}
	 */
	private static function sum_totals( $from, $to ) {
		global $wpdb;
		$rollup = SXANL_Schema::table( 'rollup' );
		$row    = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COALESCE(SUM(visitors),0) v, COALESCE(SUM(sessions),0) s,
				        COALESCE(SUM(pageviews),0) p, COALESCE(SUM(new_visitors),0) nv,
				        COALESCE(SUM(bounces),0) b, COALESCE(SUM(engaged_seconds),0) e
				 FROM $rollup WHERE metric = 'totals' AND day BETWEEN %s AND %s", // phpcs:ignore WordPress.DB
				$from,
				$to
			),
			ARRAY_A
		);
		$s = (int) $row['s'];
		return array(
			'visitors'     => (int) $row['v'],
			'sessions'     => $s,
			'pageviews'    => (int) $row['p'],
			'new_visitors' => (int) $row['nv'],
			'bounces'      => (int) $row['b'],
			'avg_engaged'  => $s > 0 ? (float) $row['e'] / $s : 0.0,
			'bounce_rate'  => $s > 0 ? (float) $row['b'] / $s * 100 : 0.0,
		);
	}

	/**
	 * Per-day series for the trend chart + sparklines.
	 *
	 * @param array $r resolve_range() output.
	 * @return array<int,array{date:string,visitors:int,pageviews:int,sessions:int}>
	 */
	/**
	 * Hour-by-hour trend for a single day, straight from the raw hits table
	 * (always current — the tracker writes it live). Rows keep the timeseries
	 * shape but carry a short 'label' ("14:00") for the axis.
	 *
	 * @param string $day Y-m-d in the site timezone.
	 * @return array<int,array>
	 */
	public static function hourly_series( $day ) {
		global $wpdb;
		$hits = SXANL_Schema::table( 'hits' );
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT HOUR(created_at) AS h,
				        COUNT(*) AS pageviews,
				        COUNT(DISTINCT NULLIF(visitor_hash, '')) AS visitors,
				        COUNT(DISTINCT NULLIF(session_id, '')) AS sessions
				 FROM $hits WHERE day = %s GROUP BY HOUR(created_at)", // phpcs:ignore WordPress.DB
				$day
			),
			OBJECT_K
		);

		$end_h = ( $day === sxanl_site_date() ) ? (int) wp_date( 'G' ) : 23;

		$out = array();
		for ( $h = 0; $h <= $end_h; $h++ ) {
			$row   = isset( $rows[ $h ] ) ? $rows[ $h ] : null;
			$out[] = array(
				'date'      => sprintf( '%s %02d:00', $day, $h ),
				'label'     => sprintf( '%02d:00', $h ),
				'visitors'  => $row ? (int) $row->visitors : 0,
				'pageviews' => $row ? (int) $row->pageviews : 0,
				'sessions'  => $row ? (int) $row->sessions : 0,
			);
		}
		return $out;
	}

	public static function timeseries( array $r ) {
		if ( isset( $r['unit'] ) && 'hour' === $r['unit'] ) {
			return self::hourly_series( $r['from'] );
		}
		global $wpdb;
		$rollup = SXANL_Schema::table( 'rollup' );
		$rows   = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT day, visitors, pageviews, sessions
				 FROM $rollup WHERE metric = 'totals' AND day BETWEEN %s AND %s", // phpcs:ignore WordPress.DB
				$r['from'],
				$r['to']
			),
			OBJECT_K
		);

		$out = array();
		for ( $ts = strtotime( $r['from'] ); $ts <= strtotime( $r['to'] ); $ts += DAY_IN_SECONDS ) {
			$d   = wp_date( 'Y-m-d', $ts );
			$row = isset( $rows[ $d ] ) ? $rows[ $d ] : null;
			$out[] = array(
				'date'      => $d,
				'visitors'  => $row ? (int) $row->visitors : 0,
				'pageviews' => $row ? (int) $row->pageviews : 0,
				'sessions'  => $row ? (int) $row->sessions : 0,
			);
		}
		return $out;
	}

	/**
	 * A ranked breakdown for one metric.
	 *
	 * @param string $metric Rollup metric name.
	 * @param array  $r      resolve_range() output.
	 * @param int    $limit  Rows.
	 * @param string $order  'visitors'|'sessions'|'pageviews'
	 * @return array<int,array>
	 */
	public static function breakdown( $metric, array $r, $limit = 10, $order = 'visitors' ) {
		global $wpdb;
		$rollup = SXANL_Schema::table( 'rollup' );
		$order  = in_array( $order, array( 'visitors', 'sessions', 'pageviews' ), true ) ? $order : 'visitors';

		return (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT dkey,
				        SUM(visitors) AS visitors,
				        SUM(sessions) AS sessions,
				        SUM(pageviews) AS pageviews
				 FROM $rollup
				 WHERE metric = %s AND day BETWEEN %s AND %s AND dkey <> ''
				 GROUP BY dkey
				 ORDER BY $order DESC
				 LIMIT %d", // phpcs:ignore WordPress.DB
				$metric,
				$r['from'],
				$r['to'],
				max( 1, (int) $limit )
			),
			ARRAY_A
		);
	}

	/**
	 * Country map data: full list (for the choropleth) + top N.
	 *
	 * @param array $r resolve_range() output.
	 * @return array{all:array<string,int>,top:array}
	 */
	public static function countries( array $r ) {
		$rows = self::breakdown( 'country', $r, 300, 'visitors' );
		$all  = array();
		foreach ( $rows as $row ) {
			$all[ strtoupper( $row['dkey'] ) ] = (int) $row['visitors'];
		}
		return array(
			'all' => $all,
			'top' => array_slice( $rows, 0, 10 ),
		);
	}

	/**
	 * Referrer groups (Search / Social / Referral / Direct / Campaign / Internal)
	 * plus the top named sources and domains.
	 *
	 * @param array $r resolve_range() output.
	 * @return array
	 */
	public static function referrers( array $r ) {
		return array(
			'by_type'    => self::breakdown( 'source', $r, 10, 'sessions' ),
			'by_name'    => self::breakdown( 'source_name', $r, 12, 'sessions' ),
			'by_domain'  => self::breakdown( 'ref_domain', $r, 12, 'sessions' ),
			'campaigns'  => self::breakdown( 'campaign', $r, 12, 'sessions' ),
		);
	}

	/**
	 * On-site search box terms.
	 *
	 * @param array $r     resolve_range() output.
	 * @param int   $limit Rows.
	 * @return array
	 */
	public static function site_search( array $r, $limit = 25 ) {
		global $wpdb;
		$table = SXANL_Schema::table( 'site_search' );
		return (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT term, SUM(searches) AS searches,
				        MIN(results_min) AS results_min, MAX(results_max) AS results_max
				 FROM $table WHERE day BETWEEN %s AND %s AND term <> ''
				 GROUP BY term ORDER BY searches DESC LIMIT %d", // phpcs:ignore WordPress.DB
				$r['from'],
				$r['to'],
				max( 1, (int) $limit )
			),
			ARRAY_A
		);
	}

	/**
	 * Visitors active in the last N minutes and the pages they are on.
	 *
	 * @param int $minutes Window.
	 * @return array{visitors:int,pageviews:int,pages:array}
	 */
	public static function realtime( $minutes = 30 ) {
		global $wpdb;
		$hits    = SXANL_Schema::table( 'hits' );
		$minutes = max( 1, min( 60, (int) $minutes ) );
		// created_at is stored in site-local time (current_time('mysql')).
		$since   = $wpdb->get_var(
			$wpdb->prepare( 'SELECT DATE_SUB(%s, INTERVAL %d MINUTE)', current_time( 'mysql' ), $minutes )
		);

		$visitors = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(DISTINCT visitor_hash) FROM $hits WHERE created_at >= %s", $since ) // phpcs:ignore WordPress.DB
		);
		$pv = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM $hits WHERE created_at >= %s", $since ) // phpcs:ignore WordPress.DB
		);
		$pages = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT path, COUNT(*) AS views FROM $hits
				 WHERE created_at >= %s GROUP BY path ORDER BY views DESC LIMIT 10", // phpcs:ignore WordPress.DB
				$since
			),
			ARRAY_A
		);
		return array(
			'visitors'  => $visitors,
			'pageviews' => $pv,
			'window'    => $minutes,
			'pages'     => $pages,
		);
	}

	/**
	 * Everything the main dashboard needs, in one call.
	 *
	 * @param string $range Range keyword.
	 * @param string $from  Custom from.
	 * @param string $to    Custom to.
	 * @return array
	 */
	public static function dashboard_bundle( $range = '28d', $from = '', $to = '' ) {
		self::ensure_fresh();
		$r = self::resolve_range( $range, $from, $to );
		return array(
			'range'        => $r,
			'granularity'  => isset( $r['unit'] ) ? $r['unit'] : 'day',
			'totals'       => self::totals( $r ),
			'timeseries'   => self::timeseries( $r ),
			'countries'    => self::countries( $r ),
			'referrers'    => self::referrers( $r ),
			'pages'        => self::breakdown( 'page', $r, 15, 'pageviews' ),
			'entry_pages'  => self::breakdown( 'entry_page', $r, 10, 'sessions' ),
			'exit_pages'   => self::breakdown( 'exit_page', $r, 10, 'sessions' ),
			'devices'      => self::breakdown( 'device', $r, 6, 'sessions' ),
			'browsers'     => self::breakdown( 'browser', $r, 8, 'sessions' ),
			'os'           => self::breakdown( 'os', $r, 8, 'sessions' ),
			'site_search'  => self::site_search( $r, 25 ),
			'gsc'          => array(
				'has_data' => SXANL_Search_Console::has_data(),
				'queries'  => SXANL_Search_Console::top_queries( $r['from'], $r['to'], 25 ),
			),
			'generated_at' => current_time( 'mysql' ),
			'last_rollup'  => get_option( 'sxanl_last_rollup', '' ),
		);
	}
}