<?php
/**
 * Daily aggregation + retention.
 *
 * A cron job (hourly) rebuilds the rollup rows for "today" and "yesterday"
 * from the raw hits/sessions tables, then prunes raw hits older than the
 * retention window. Every dashboard query reads only the rollup table, so it
 * stays fast even with years of history.
 *
 * rollup grain: (day, metric, dkey) -> counts
 *   metric 'totals'      dkey ''            site totals for the day
 *   metric 'country'     dkey 'GB'
 *   metric 'source'      dkey 'search'      + extra = friendly name is per-row via 'source_name' metric
 *   metric 'source_name' dkey 'Google'
 *   metric 'ref_domain'  dkey 'bbc.co.uk'
 *   metric 'campaign'    dkey 'spring-sale'
 *   metric 'page'        dkey '/pricing'
 *   metric 'entry_page'  dkey '/'
 *   metric 'exit_page'   dkey '/thanks'
 *   metric 'device'      dkey 'mobile'
 *   metric 'browser'     dkey 'Chrome'
 *   metric 'os'          dkey 'Windows'
 */

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

class SXANL_Rollup {

	const CRON_HOOK    = 'sxanl_rollup_cron';
	const SALT_HOOK    = 'sxanl_salt_cron';
	const CRON_INTERVAL = 'sxanl_10min';

	/**
	 * Register the 10-minute cron interval. Hooked to `cron_schedules`.
	 *
	 * @param array $schedules Existing schedules.
	 * @return array
	 */
	public static function add_interval( $schedules ) {
		$schedules[ self::CRON_INTERVAL ] = array(
			'interval' => 600,
			'display'  => __( 'Every 10 minutes (Sentrixa Analytics)', 'sentrixa-analytics' ),
		);
		return $schedules;
	}

	/**
	 * Schedule cron events (idempotent). The rollup runs every 10 minutes as a
	 * backstop; a dashboard read also rebuilds "today" on demand
	 * (SXANL_Stats_Query::ensure_fresh) so figures are live to within a minute.
	 */
	public static function schedule() {
		// Migrate an existing hourly schedule to the 10-minute one.
		if ( self::CRON_INTERVAL !== wp_get_schedule( self::CRON_HOOK ) ) {
			wp_clear_scheduled_hook( self::CRON_HOOK );
		}
		if ( ! wp_next_scheduled( self::CRON_HOOK ) ) {
			wp_schedule_event( time() + 120, self::CRON_INTERVAL, self::CRON_HOOK );
		}
		if ( ! wp_next_scheduled( self::SALT_HOOK ) ) {
			// Just after local midnight.
			wp_schedule_event( self::next_local_midnight(), 'daily', self::SALT_HOOK );
		}
	}

	public static function clear_schedule() {
		wp_clear_scheduled_hook( self::CRON_HOOK );
		wp_clear_scheduled_hook( self::SALT_HOOK );
	}

	/**
	 * Cron entry point.
	 */
	public static function run() {
		$today     = sxanl_site_date();
		$yesterday = sxanl_site_date( time() - DAY_IN_SECONDS );

		self::rebuild_day( $today );
		self::rebuild_day( $yesterday );
		self::prune();
	}

	/**
	 * Rotate the visitor salt (daily cron). Also nudges a rollup so the
	 * finished day is closed off.
	 */
	public static function rotate_salt_cron() {
		SXANL_Visitor::rotate_salt();
		self::rebuild_day( sxanl_site_date( time() - DAY_IN_SECONDS ) );
	}

	/**
	 * Recompute every rollup row for one day.
	 *
	 * @param string $day Y-m-d.
	 */
	public static function rebuild_day( $day ) {
		global $wpdb;
		$hits     = SXANL_Schema::table( 'hits' );
		$sessions = SXANL_Schema::table( 'sessions' );
		$rollup   = SXANL_Schema::table( 'rollup' );

		$wpdb->query( $wpdb->prepare( "DELETE FROM $rollup WHERE day = %s", $day ) ); // phpcs:ignore WordPress.DB

		$rows = array();

		// ---- totals ----
		$t = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
				   COUNT(DISTINCT visitor_hash) AS visitors,
				   COUNT(DISTINCT session_id)   AS sessions,
				   COUNT(*)                     AS pageviews,
				   COUNT(DISTINCT CASE WHEN is_new_visitor = 1 THEN visitor_hash END) AS new_visitors,
				   SUM(CASE WHEN is_entry = 1 AND is_bounce = 1 THEN 1 ELSE 0 END)    AS bounces
				 FROM $hits WHERE day = %s", // phpcs:ignore WordPress.DB
				$day
			),
			ARRAY_A
		);
		$engaged = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COALESCE(SUM(engaged_seconds),0) FROM $sessions WHERE day = %s", $day ) ); // phpcs:ignore WordPress.DB

		$rows[] = array(
			'metric'          => 'totals',
			'dkey'            => '',
			'visitors'        => (int) ( $t['visitors'] ?? 0 ),
			'sessions'        => (int) ( $t['sessions'] ?? 0 ),
			'pageviews'       => (int) ( $t['pageviews'] ?? 0 ),
			'new_visitors'    => (int) ( $t['new_visitors'] ?? 0 ),
			'bounces'         => (int) ( $t['bounces'] ?? 0 ),
			'engaged_seconds' => $engaged,
			'extra'           => '',
		);

		// ---- visit-level breakdowns (one value per session, from the
		//      sessions table — country, traffic source, device, etc. don't
		//      change mid-visit, and counting them per-pageview would bias
		//      towards chatty sessions) ----
		$session_dims = array(
			'country'     => 'country_code',
			'source'      => 'source_type',
			'source_name' => 'source_name',
			'ref_domain'  => 'referrer_host',
			'campaign'    => 'utm_campaign',
			'device'      => 'device_type',
			'browser'     => 'browser',
			'os'          => 'os',
		);
		foreach ( $session_dims as $metric => $col ) {
			$res = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT $col AS dkey,
					        COUNT(DISTINCT visitor_hash) AS visitors,
					        COUNT(*)                     AS sessions,
					        SUM(pageviews)               AS pageviews,
					        COUNT(DISTINCT CASE WHEN is_new_visitor = 1 THEN visitor_hash END) AS new_visitors
					 FROM $sessions
					 WHERE day = %s AND $col <> ''
					 GROUP BY $col", // phpcs:ignore WordPress.DB
					$day
				),
				ARRAY_A
			);
			foreach ( (array) $res as $r ) {
				$rows[] = array(
					'metric'          => $metric,
					'dkey'            => sxanl_str_limit( (string) $r['dkey'], 160 ),
					'visitors'        => (int) $r['visitors'],
					'sessions'        => (int) $r['sessions'],
					'pageviews'       => (int) $r['pageviews'],
					'new_visitors'    => (int) $r['new_visitors'],
					'bounces'         => 0,
					'engaged_seconds' => 0,
					'extra'           => '',
				);
			}
		}

		// ---- page views (per path, from hits) ----
		$res = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT path AS dkey,
				        COUNT(DISTINCT visitor_hash) AS visitors,
				        COUNT(DISTINCT session_id)   AS sessions,
				        COUNT(*)                     AS pageviews
				 FROM $hits
				 WHERE day = %s AND path <> ''
				 GROUP BY path", // phpcs:ignore WordPress.DB
				$day
			),
			ARRAY_A
		);
		foreach ( (array) $res as $r ) {
			$rows[] = array(
				'metric'          => 'page',
				'dkey'            => sxanl_str_limit( (string) $r['dkey'], 160 ),
				'visitors'        => (int) $r['visitors'],
				'sessions'        => (int) $r['sessions'],
				'pageviews'       => (int) $r['pageviews'],
				'new_visitors'    => 0,
				'bounces'         => 0,
				'engaged_seconds' => 0,
				'extra'           => '',
			);
		}

		// ---- entry / exit pages from sessions ----
		foreach ( array( 'entry_page' => 'entry_path', 'exit_page' => 'exit_path' ) as $metric => $col ) {
			$res = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT $col AS dkey, COUNT(*) AS sessions
					 FROM $sessions WHERE day = %s AND $col <> '' GROUP BY $col", // phpcs:ignore WordPress.DB
					$day
				),
				ARRAY_A
			);
			foreach ( (array) $res as $r ) {
				$rows[] = array(
					'metric'          => $metric,
					'dkey'            => sxanl_str_limit( (string) $r['dkey'], 160 ),
					'visitors'        => 0,
					'sessions'        => (int) $r['sessions'],
					'pageviews'       => 0,
					'new_visitors'    => 0,
					'bounces'         => 0,
					'engaged_seconds' => 0,
					'extra'           => '',
				);
			}
		}

		// ---- bulk insert ----
		foreach ( array_chunk( $rows, 100 ) as $chunk ) {
			$values = array();
			$args   = array();
			foreach ( $chunk as $row ) {
				$values[] = '(%s,%s,%s,%d,%d,%d,%d,%d,%d,%s)';
				array_push(
					$args,
					$day,
					$row['metric'],
					$row['dkey'],
					$row['visitors'],
					$row['sessions'],
					$row['pageviews'],
					$row['new_visitors'],
					$row['bounces'],
					$row['engaged_seconds'],
					$row['extra']
				);
			}
			$sql = "INSERT INTO $rollup (day,metric,dkey,visitors,sessions,pageviews,new_visitors,bounces,engaged_seconds,extra) VALUES "
				. implode( ',', $values );
			$wpdb->query( $wpdb->prepare( $sql, $args ) ); // phpcs:ignore WordPress.DB
		}

		update_option( 'sxanl_last_rollup', current_time( 'mysql' ), false );
	}

	/**
	 * Delete raw hits (and dead sessions) older than the retention window.
	 * Rollups are kept forever.
	 */
	public static function prune() {
		global $wpdb;
		$days  = max( 7, (int) SXANL_Settings::get( 'retention_days' ) );
		$hits  = SXANL_Schema::table( 'hits' );
		$sess  = SXANL_Schema::table( 'sessions' );
		$cut   = sxanl_site_date( time() - $days * DAY_IN_SECONDS );

		$wpdb->query( $wpdb->prepare( "DELETE FROM $hits WHERE day < %s", $cut ) );      // phpcs:ignore WordPress.DB
		$wpdb->query( $wpdb->prepare( "DELETE FROM $sess WHERE day < %s", $cut ) );      // phpcs:ignore WordPress.DB

		// On-site search + GSC rows: keep 400 days.
		$ss  = SXANL_Schema::table( 'site_search' );
		$sc  = SXANL_Schema::table( 'search_console' );
		$cut2 = sxanl_site_date( time() - 400 * DAY_IN_SECONDS );
		$wpdb->query( $wpdb->prepare( "DELETE FROM $ss WHERE day < %s", $cut2 ) );       // phpcs:ignore WordPress.DB
		$wpdb->query( $wpdb->prepare( "DELETE FROM $sc WHERE day < %s", $cut2 ) );       // phpcs:ignore WordPress.DB
	}

	/**
	 * Unix ts of the next local midnight.
	 *
	 * @return int
	 */
	private static function next_local_midnight() {
		$midnight = new DateTime( 'tomorrow', wp_timezone() );
		return $midnight->getTimestamp() + 120;
	}
}