<?php
/**
 * Cache module — Web Vitals aggregator
 *
 * Daily cron that:
 *   - Computes p75 of each (url_hash, device, metric) over the previous 24h
 *     and upserts into be_web_vitals_p75.
 *   - Deletes raw samples older than the configured retention window.
 *
 * Aggregation is what makes the dashboard fast — querying p75 over 30 days of
 * raw samples on a busy site is too expensive to run on every page load.
 *
 * @package BlaminhorEssentials
 */

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

class Blaminhor_Essentials_Cache_Webvitals_Aggregator {

	const CRON_HOOK = 'blaminhor_essentials_cache_webvitals_aggregate';

	/** @var Blaminhor_Essentials_Module_Cache */
	private $parent;

	public function __construct( Blaminhor_Essentials_Module_Cache $parent ) {
		$this->parent = $parent;
	}

	public function register_hooks() {
		add_action( self::CRON_HOOK, array( $this, 'run' ) );

		if ( $this->parent->get_setting( 'webvitals_enabled', false ) ) {
			if ( ! wp_next_scheduled( self::CRON_HOOK ) ) {
				wp_schedule_event( time() + 600, 'daily', self::CRON_HOOK );
			}
		} else {
			$ts = wp_next_scheduled( self::CRON_HOOK );
			if ( $ts ) {
				wp_unschedule_event( $ts, self::CRON_HOOK );
			}
		}
	}

	public function run() {
		global $wpdb;
		$raw_table = $wpdb->prefix . 'be_web_vitals';
		$agg_table = $wpdb->prefix . 'be_web_vitals_p75';

		if ( ! blaminhor_essentials_validate_table_name( $raw_table ) || ! blaminhor_essentials_validate_table_name( $agg_table ) ) {
			return;
		}

		$blog_id = is_multisite() ? (int) get_current_blog_id() : 0;
		$today   = current_time( 'Y-m-d' );

		// p75 calc: pull all samples from "yesterday" grouped by (url, device, metric),
		// compute p75 in PHP (cheaper than a non-portable SQL percentile UDF),
		// upsert into the agg table.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT url_hash, url, device, metric, value
				 FROM {$raw_table}
				 WHERE blog_id = %d
				   AND recorded_at >= DATE_SUB(%s, INTERVAL 1 DAY)
				 ORDER BY url_hash, device, metric",
				$blog_id,
				$today
			)
		);

		if ( ! is_array( $rows ) || empty( $rows ) ) {
			$this->cleanup_old_samples();
			return;
		}

		// Bucket samples.
		$buckets = array();
		foreach ( $rows as $r ) {
			$key = $r->url_hash . '|' . $r->device . '|' . $r->metric;
			if ( ! isset( $buckets[ $key ] ) ) {
				$buckets[ $key ] = array(
					'url_hash' => $r->url_hash,
					'url'      => $r->url,
					'device'   => $r->device,
					'metric'   => $r->metric,
					'values'   => array(),
				);
			}
			$buckets[ $key ]['values'][] = (float) $r->value;
		}

		// Upsert p75 per bucket.
		foreach ( $buckets as $bucket ) {
			$values = $bucket['values'];
			if ( empty( $values ) ) {
				continue;
			}
			sort( $values );
			$idx = (int) ceil( 0.75 * count( $values ) ) - 1;
			$idx = max( 0, $idx );
			$p75 = $values[ $idx ];

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query(
				$wpdb->prepare(
					"INSERT INTO {$agg_table}
						(blog_id, period_date, url_hash, url, device, metric, p75_value, sample_count)
					 VALUES (%d, %s, %s, %s, %s, %s, %f, %d)
					 ON DUPLICATE KEY UPDATE p75_value = VALUES(p75_value), sample_count = VALUES(sample_count)",
					$blog_id,
					$today,
					$bucket['url_hash'],
					$bucket['url'],
					$bucket['device'],
					$bucket['metric'],
					$p75,
					count( $values )
				)
			);
		}

		$this->cleanup_old_samples();
	}

	private function cleanup_old_samples() {
		global $wpdb;
		$raw_table = $wpdb->prefix . 'be_web_vitals';
		$agg_table = $wpdb->prefix . 'be_web_vitals_p75';

		$retention = max( 7, min( 365, (int) $this->parent->get_setting( 'webvitals_retention_days', 30 ) ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$raw_table} WHERE recorded_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
				$retention
			)
		);
		// p75 retention = 90 days (small table, no real cost).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			"DELETE FROM {$agg_table} WHERE period_date < DATE_SUB(CURDATE(), INTERVAL 90 DAY)"
		);
	}

	/**
	 * Pull data for the dashboard. Returns last $days of p75 per metric.
	 *
	 * @param int $days
	 * @return array
	 */
	public function get_dashboard_data( $days = 30 ) {
		global $wpdb;
		$agg_table = $wpdb->prefix . 'be_web_vitals_p75';
		$blog_id   = is_multisite() ? (int) get_current_blog_id() : 0;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT period_date, device, metric, AVG(p75_value) AS value, SUM(sample_count) AS samples
				 FROM {$agg_table}
				 WHERE blog_id = %d AND period_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
				 GROUP BY period_date, device, metric
				 ORDER BY period_date ASC",
				$blog_id,
				(int) $days
			),
			ARRAY_A
		);

		// Top 10 worst URLs (current p75 across all metrics, weighted by samples).
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$worst = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT url, device, metric, AVG(p75_value) AS value, SUM(sample_count) AS samples
				 FROM {$agg_table}
				 WHERE blog_id = %d AND period_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
				   AND metric IN ('LCP','INP','CLS')
				 GROUP BY url_hash, device, metric
				 ORDER BY value DESC
				 LIMIT 20",
				$blog_id
			),
			ARRAY_A
		);

		return array(
			'series' => is_array( $rows ) ? $rows : array(),
			'worst'  => is_array( $worst ) ? $worst : array(),
		);
	}
}
