<?php
/**
 * Aggregated 404 storage.
 *
 * @package AdminWatch
 */

defined( 'ABSPATH' ) || exit;

class WebStudio_Admin_Watch_404_Repository {
	const DB_VERSION = '1.1.0';

	/** @return string */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'admin_watch_404';
	}

	/** @return string */
	public static function daily_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'admin_watch_404_daily';
	}

	/** Create or upgrade the plugin's small aggregate tables. */
	public static function install() {
		global $wpdb;

		$table           = self::table_name();
		$daily_table     = self::daily_table_name();
		$charset_collate = $wpdb->get_charset_collate();
		$sql             = "CREATE TABLE $table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			url_hash char(64) NOT NULL,
			url text NOT NULL,
			hit_count bigint(20) unsigned NOT NULL DEFAULT 1,
			first_seen datetime NOT NULL,
			last_seen datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY url_hash (url_hash),
			KEY last_seen (last_seen)
		) $charset_collate;
		CREATE TABLE $daily_table (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_date date NOT NULL,
			hit_count bigint(20) unsigned NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY event_date (event_date)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
		update_option( 'admin_watch_db_version', self::DB_VERSION );
	}

	/**
	 * Record one 404 request in both URL and day aggregates.
	 *
	 * @param string $url Normalised request path.
	 */
	public function record( $url ) {
		global $wpdb;

		$table       = self::table_name();
		$daily_table = self::daily_table_name();
		$hash        = hash( 'sha256', $url );
		$now         = current_time( 'mysql', true );
		$event_date  = wp_date( 'Y-m-d' );

		// SHA-256 provides a collision-resistant, fixed-size unique lookup key for bounded paths.
		$url_sql = $wpdb->prepare(
			'INSERT INTO %i (url_hash, url, hit_count, first_seen, last_seen) VALUES (%s, %s, 1, %s, %s) ON DUPLICATE KEY UPDATE hit_count = hit_count + 1, last_seen = VALUES(last_seen)',
			$table,
			$hash,
			$url,
			$now,
			$now
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared custom-table aggregate write; caching would make counts inaccurate.
		$wpdb->query( $url_sql );

		$daily_sql = $wpdb->prepare(
			'INSERT INTO %i (event_date, hit_count) VALUES (%s, 1) ON DUPLICATE KEY UPDATE hit_count = hit_count + 1',
			$daily_table,
			$event_date
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared custom-table aggregate write; caching would make counts inaccurate.
		$wpdb->query( $daily_sql );
	}

	/** @return array<int,object> */
	public function get_items( $page, $per_page ) {
		global $wpdb;

		$offset = max( 0, ( $page - 1 ) * $per_page );
		$sql    = $wpdb->prepare(
			'SELECT url, hit_count, last_seen FROM %i ORDER BY last_seen DESC LIMIT %d OFFSET %d',
			self::table_name(),
			$per_page,
			$offset
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared, paginated custom-table query.
		return $wpdb->get_results( $sql );
	}

	/** @return int */
	public function count_items() {
		global $wpdb;

		$sql = $wpdb->prepare( 'SELECT COUNT(*) FROM %i', self::table_name() );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared custom-table count query.
		return (int) $wpdb->get_var( $sql );
	}

	/** Return the total retained hits across aggregated URL rows. */
	public function total_hits() {
		global $wpdb;

		$sql = $wpdb->prepare( 'SELECT COALESCE(SUM(hit_count), 0) FROM %i', self::table_name() );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared custom-table aggregate query.
		return (int) $wpdb->get_var( $sql );
	}

	/**
	 * Return an accurate count of requests in a site-local date range.
	 *
	 * @param string $start_date Inclusive Y-m-d date.
	 * @param string $end_date   Inclusive Y-m-d date.
	 * @return int
	 */
	public function daily_hit_count( $start_date, $end_date ) {
		global $wpdb;

		$sql = $wpdb->prepare(
			'SELECT COALESCE(SUM(hit_count), 0) FROM %i WHERE event_date BETWEEN %s AND %s',
			self::daily_table_name(),
			$start_date,
			$end_date
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared daily aggregate query.
		return (int) $wpdb->get_var( $sql );
	}

	/** Remove expired URL and daily aggregate rows. */
	public function cleanup( $days ) {
		global $wpdb;

		$days         = in_array( absint( $days ), array( 7, 30, 90 ), true ) ? absint( $days ) : 30;
		$url_cutoff   = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );
		$daily_cutoff = ( new DateTimeImmutable( 'today', wp_timezone() ) )->modify( '-' . $days . ' days' )->format( 'Y-m-d' );
		$url_sql      = $wpdb->prepare( 'DELETE FROM %i WHERE last_seen < %s', self::table_name(), $url_cutoff );
		$daily_sql    = $wpdb->prepare( 'DELETE FROM %i WHERE event_date < %s', self::daily_table_name(), $daily_cutoff );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared retention cleanup for custom plugin tables.
		$wpdb->query( $url_sql );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared retention cleanup for custom plugin tables.
		$wpdb->query( $daily_sql );
	}
}