<?php
/**
 * Daily aggregation of reader events.
 *
 * @package KarmaDocs
 */

namespace KarmaDocs\Analytics;

use KarmaDocs\Jobs;
use KarmaDocs\Settings;

defined( 'ABSPATH' ) || exit;

/**
 * Turns raw events into `kdocs_stats_daily` and `kdocs_search_daily` rows.
 *
 * Runs every night for the days not aggregated yet (catching up after downtime),
 * and on demand for today (throttled) so dashboards are live. Rolling up a day
 * again replaces its rows, so it is safe to repeat. Raw events older than the
 * retention setting are deleted afterwards; aggregates are kept forever.
 */
class Rollup {

	/**
	 * Nightly job hook.
	 */
	const HOOK = 'kdocs_analytics_rollup';

	/**
	 * Option: last day fully aggregated (Y-m-d).
	 */
	const DONE_OPTION = 'kdocs_rollup_done';

	/**
	 * Option: first day of event collection (Y-m-d). Earlier days hold imported
	 * history and are never rolled up again.
	 */
	const SINCE_OPTION = 'kdocs_analytics_since';

	/**
	 * Seconds after a search during which another search counts as a refinement.
	 */
	const REFINE_WINDOW = 60;

	/**
	 * Hooks.
	 */
	public function register() {
		add_action( self::HOOK, array( __CLASS__, 'run' ) );
		add_action( 'admin_init', array( $this, 'schedule' ) );
	}

	/**
	 * Schedules the nightly run (shortly after midnight, site time).
	 */
	public function schedule() {
		$first = strtotime( 'tomorrow 00:20', current_time( 'timestamp' ) ) - (int) ( get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ); // phpcs:ignore WordPress.DateTime.CurrentTimeTimestamp.Requested -- Local midnight.
		Jobs::recurring( DAY_IN_SECONDS, self::HOOK, array(), $first );
	}

	/**
	 * Aggregates every finished day not done yet (at most 60), then prunes old events.
	 */
	public static function run() {
		$today = current_time( 'Y-m-d' );
		$done  = (string) get_option( self::DONE_OPTION, '' );
		$day   = $done ? gmdate( 'Y-m-d', strtotime( $done . ' +1 day' ) ) : self::first_event_day();
		$limit = 60;
		while ( $day && $day < $today && $limit-- > 0 ) {
			self::day( $day );
			update_option( self::DONE_OPTION, $day, false );
			$day = gmdate( 'Y-m-d', strtotime( $day . ' +1 day' ) );
		}
		self::prune();
	}

	/**
	 * Re-aggregates today, at most every five minutes (dashboards call this).
	 */
	public static function refresh_today() {
		if ( get_transient( 'kdocs_rollup_today' ) ) {
			return;
		}
		set_transient( 'kdocs_rollup_today', 1, 5 * MINUTE_IN_SECONDS );
		// Also catch up if the nightly job hasn't run (WP-Cron on a quiet site).
		$done = (string) get_option( self::DONE_OPTION, '' );
		if ( ! $done || $done < gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -1 day' ) ) ) {
			self::run();
		}
		self::day( current_time( 'Y-m-d' ) );
	}

	/**
	 * Day of the oldest stored event.
	 *
	 * @return string|null
	 */
	private static function first_event_day() {
		global $wpdb;
		$table = esc_sql( Store::table( 'events' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$day = $wpdb->get_var( "SELECT MIN(day) FROM {$table}" );
		return $day ? (string) $day : null;
	}

	/**
	 * Aggregates one day.
	 *
	 * @param string $day Y-m-d.
	 */
	public static function day( $day ) {
		global $wpdb;
		$since = (string) get_option( self::SINCE_OPTION, '' );
		if ( $since && $day < $since ) {
			return;
		}
		$events = esc_sql( Store::table( 'events' ) );
		$stats  = esc_sql( Store::table( 'stats_daily' ) );
		$search = esc_sql( Store::table( 'search_daily' ) );

		// Per article, per knowledge base (article 0) and site (KB 0, article 0).
		$metrics = "COUNT(CASE WHEN type = 'view' THEN 1 END),
			COUNT(DISTINCT CASE WHEN type = 'view' THEN visitor END),
			COUNT(DISTINCT CASE WHEN type = 'view' THEN NULLIF(session, '') END),
			COUNT(CASE WHEN type = 'read' AND n1 >= 50 THEN 1 END),
			COUNT(CASE WHEN type = 'read' AND n1 >= 90 THEN 1 END),
			COALESCE(SUM(CASE WHEN type = 'read' THEN n2 END), 0),
			COUNT(CASE WHEN type = 'feedback' AND label = 'yes' THEN 1 END),
			COUNT(CASE WHEN type = 'feedback' AND label = 'no' THEN 1 END),
			COUNT(CASE WHEN type = 'contact_click' THEN 1 END),
			COUNT(DISTINCT CASE WHEN type = 'contact_click' THEN NULLIF(session, '') END)";
		$columns = esc_sql( 'day, kb_id, article_id, views, visitors, sessions, reads_50, reads_100, read_secs, helpful_yes, helpful_no, contacts, contact_sessions' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$stats} WHERE day = %s", $day ) );
		$wpdb->query( $wpdb->prepare( "INSERT INTO {$stats} ({$columns}) SELECT day, kb_id, article_id, {$metrics} FROM {$events} WHERE day = %s AND article_id > 0 GROUP BY day, kb_id, article_id", $day ) );
		$wpdb->query( $wpdb->prepare( "INSERT INTO {$stats} ({$columns}) SELECT day, kb_id, 0, {$metrics} FROM {$events} WHERE day = %s AND kb_id > 0 GROUP BY day, kb_id", $day ) );
		$wpdb->query( $wpdb->prepare( "INSERT INTO {$stats} ({$columns}) SELECT day, 0, 0, {$metrics} FROM {$events} WHERE day = %s GROUP BY day", $day ) );

		// The day collection started: keep the views the earlier Pro counter recorded that morning.
		$legacy = esc_sql( $wpdb->prefix . 'kdocs_views' );
		if ( $day === $since && $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $legacy ) ) === $legacy ) {
			$merge = "ON DUPLICATE KEY UPDATE views = {$stats}.views + VALUES(views)";
			$wpdb->query( $wpdb->prepare( "INSERT INTO {$stats} (day, kb_id, article_id, views) SELECT day, kb_id, article_id, views FROM {$legacy} WHERE day = %s {$merge}", $day ) );
			$wpdb->query( $wpdb->prepare( "INSERT INTO {$stats} (day, kb_id, article_id, views) SELECT day, kb_id, 0, SUM(views) FROM {$legacy} WHERE day = %s GROUP BY day, kb_id {$merge}", $day ) );
			$wpdb->query( $wpdb->prepare( "INSERT INTO {$stats} (day, kb_id, article_id, views) SELECT day, 0, 0, SUM(views) FROM {$legacy} WHERE day = %s GROUP BY day {$merge}", $day ) );
		}

		// Searches: a search "succeeds" when a result was clicked and no new search followed
		// within the refinement window; a "refinement" is a search followed by another one.
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$search} WHERE day = %s", $day ) );
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$search} (day, kb_id, term, searches, zero_results, clicks, successes, refinements, click_pos)
				SELECT s.day, s.kb_id, LEFT(s.label, 100),
					COUNT(*),
					SUM(s.n1 = 0),
					SUM(c.search_id IS NOT NULL),
					SUM(c.search_id IS NOT NULL AND r.id IS NULL),
					SUM(r.id IS NOT NULL),
					COALESCE(SUM(c.pos), 0)
				FROM {$events} s
				LEFT JOIN (
					SELECT search_id, MIN(n1) AS pos FROM {$events}
					WHERE day = %s AND type = 'search_click' AND search_id <> '' GROUP BY search_id
				) c ON c.search_id = s.search_id AND s.search_id <> ''
				LEFT JOIN {$events} r ON r.id = (
					SELECT r2.id FROM {$events} r2
					WHERE r2.session = s.session AND s.session <> '' AND r2.type = 'search' AND r2.id > s.id
						AND r2.ts <= DATE_ADD(s.ts, INTERVAL %d SECOND)
					LIMIT 1
				)
				WHERE s.day = %s AND s.type = 'search'
				GROUP BY s.day, s.kb_id, LEFT(s.label, 100)",
				$day,
				self::REFINE_WINDOW,
				$day
			)
		);
		// phpcs:enable
	}

	/**
	 * Deletes raw events older than the retention period.
	 */
	public static function prune() {
		global $wpdb;
		$days   = max( 7, (int) Settings::get( 'analytics_retention', 90 ) );
		$cutoff = gmdate( 'Y-m-d', strtotime( current_time( 'Y-m-d' ) . ' -' . $days . ' days' ) );
		$table  = esc_sql( Store::table( 'events' ) );
		// Batches keep each delete short on busy sites.
		do {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$deleted = (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE day < %s LIMIT 5000", $cutoff ) );
		} while ( 5000 === $deleted );
	}
}