<?php
/**
 * Storage for AI crawl and referral events.
 *
 * @package AcidStudios\AEO
 */

declare(strict_types=1);

namespace AcidStudios\AEO\Citations;

use AcidStudios\AEO\Cache;

defined( 'ABSPATH' ) || exit;

/**
 * The one table this plugin owns, and every query that touches it.
 *
 * An event is one line: what happened (`crawl` or `referral`), who it was
 * (a crawler token from `Bots\Registry`, or an engine slug from
 * `Bots\Engines`), which URL, which post, a truncated daily-salted hash of the
 * visitor's IP and when. That is deliberately as little as it can be while
 * still answering "is anything citing us" — see `Modules\Citations` for what
 * the hash is and is not.
 *
 * Rows are cheap and many, so the reads are aggregates: `stats()` runs four
 * grouped queries and caches the result for five minutes, and the row-level
 * `events()` is paginated. Everything goes through `$wpdb->prepare()`; the
 * table name is the only interpolated part, and it comes from `table()`.
 */
final class Store {

	/**
	 * Schema version, bumped whenever `install()` changes the table.
	 *
	 * @var int
	 */
	public const DB_VERSION = 1;

	/**
	 * Option holding the installed schema version.
	 *
	 * Autoloaded, unlike the other bookkeeping options this plugin writes:
	 * `maybe_upgrade()` reads it on every request, and an option that is not
	 * autoloaded costs a `SELECT` of its own each time. One value in the
	 * alloptions array is cheaper than a query per page view.
	 *
	 * @var string
	 */
	public const VERSION_OPTION = 'acid_aeo_db_version';

	/**
	 * Table name, without the site prefix.
	 *
	 * @var string
	 */
	private const TABLE = 'acid_aeo_events';

	/**
	 * Longest URL the table stores, in characters.
	 *
	 * @var int
	 */
	private const URL_MAX = 2048;

	/**
	 * How long `stats()` keeps its answer, in seconds.
	 *
	 * @var int
	 */
	private const STATS_TTL = 300;

	/**
	 * The day windows the admin screens and the CLI ask `stats()` for.
	 *
	 * `purge()` drops the cached answer for each of them, so a purge is visible
	 * immediately rather than up to `STATS_TTL` later. A window outside this
	 * list is still cached — it is just as stale as any other five minute
	 * cache, which is what the caller asked for by using one.
	 *
	 * @var array<int,int>
	 */
	private const STATS_WINDOWS = [ 7, 14, 30, 90, 180, 365 ];

	/**
	 * Full, prefixed table name.
	 *
	 * @return string Table name, e.g. `wp_acid_aeo_events`.
	 */
	public static function table(): string {
		global $wpdb;

		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * Brings the table up to date when the installed schema is not the current one.
	 *
	 * The migration hook. Activation is not enough on its own: a plugin updated
	 * in place is never deactivated, so a version that changes the table would
	 * otherwise reach a site whose table is still the old shape. This runs on
	 * `plugins_loaded`, costs nothing on a request where nothing has changed
	 * (`VERSION_OPTION` is autoloaded, so the value is already in memory), and
	 * calls the installer — which is also the upgrader, because `dbDelta()`
	 * only issues what is missing — on the one request after an update where
	 * it has.
	 *
	 * @return bool True when the installer ran.
	 */
	public static function maybe_upgrade(): bool {
		if ( (int) get_option( self::VERSION_OPTION, 0 ) === self::DB_VERSION ) {
			return false;
		}

		self::install();

		return true;
	}

	/**
	 * Creates or updates the events table.
	 *
	 * Safe to call again: `dbDelta()` compares the statement with the table as
	 * it is and only issues what is missing, which is what makes this both the
	 * installer and the upgrader.
	 *
	 * @return void
	 */
	public static function install(): void {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table   = self::table();
		$collate = $wpdb->get_charset_collate();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table names cannot be prepared; this one is built from $wpdb->prefix in table().
		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_type varchar(10) NOT NULL,
			agent varchar(64) NOT NULL,
			url varchar(2048) NOT NULL,
			post_id bigint(20) unsigned DEFAULT NULL,
			ip_hash char(16) NOT NULL,
			created_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY type_created (event_type, created_at),
			KEY agent (agent),
			KEY post (post_id)
		) {$collate};";

		dbDelta( $sql );

		update_option( self::VERSION_OPTION, self::DB_VERSION, true );
	}

	/**
	 * Records one event.
	 *
	 * Every value is cut to the width of its column first: a crawler is free to
	 * request a 10 kB URL, and a strict SQL mode would answer that with an
	 * error rather than a truncated row.
	 *
	 * @param string   $type    Event type, `crawl` or `referral`.
	 * @param string   $agent   Crawler token or engine slug.
	 * @param string   $url     URL that was visited.
	 * @param int|null $post_id Post the URL resolves to, when it is a single post.
	 * @param string   $ip_hash Truncated daily-salted hash of the visitor's IP.
	 * @return bool True when the row was written.
	 */
	public static function insert( string $type, string $agent, string $url, ?int $post_id, string $ip_hash ): bool {
		global $wpdb;

		$url = self::truncate( $url, self::URL_MAX );

		if ( '' === $type || '' === $agent || '' === $url ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Writing one row into the plugin's own table; $wpdb->insert() prepares it.
		$written = $wpdb->insert(
			self::table(),
			[
				'event_type' => self::truncate( $type, 10 ),
				'agent'      => self::truncate( $agent, 64 ),
				'url'        => $url,
				'post_id'    => ( null !== $post_id && $post_id > 0 ) ? $post_id : null,
				'ip_hash'    => substr( $ip_hash, 0, 16 ),
				'created_at' => current_time( 'mysql', true ),
			],
			[ '%s', '%s', '%s', '%d', '%s', '%s' ]
		);

		return false !== $written;
	}

	/**
	 * Aggregates the last `$days` days, cached for five minutes.
	 *
	 * The window is day aligned in UTC and includes today, so `stats( 7 )` is
	 * "today and the six days before it" — which is what makes `timeline` a
	 * dense series the admin screens can draw without filling gaps themselves.
	 *
	 * @param int $days Number of days to cover, 1 to 730.
	 * @return array<string,mixed> Keys `totals`, `by_agent`, `top_pages` and `timeline`.
	 */
	public static function stats( int $days ): array {
		$days   = max( 1, min( 730, $days ) );
		$key    = 'cit_stats_' . $days;
		$cached = Cache::get( $key );

		if ( is_array( $cached ) ) {
			return $cached;
		}

		$start = self::window_start( $days );

		$stats = [
			'totals'    => self::totals( $start ),
			'by_agent'  => self::by_agent( $start ),
			'top_pages' => self::top_pages( $start ),
			'timeline'  => self::timeline( $start, $days ),
		];

		Cache::set( $key, $stats, self::STATS_TTL );

		return $stats;
	}

	/**
	 * One page of events, newest first.
	 *
	 * @param int         $page     Page number, 1 based.
	 * @param int         $per_page Rows per page, 1 to 200.
	 * @param string|null $type     Restrict to `crawl` or `referral`; null for both.
	 * @return array<string,mixed> Keys `rows` (list of events) and `total` (rows matching the filter).
	 */
	public static function events( int $page, int $per_page, ?string $type = null ): array {
		global $wpdb;

		$page     = max( 1, $page );
		$per_page = max( 1, min( 200, $per_page ) );
		$offset   = ( $page - 1 ) * $per_page;
		$type     = in_array( $type, [ 'crawl', 'referral' ], true ) ? $type : null;
		$table    = self::table();

		if ( null === $type ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading the plugin's own table; the statement is prepared, the table name through %i.
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, event_type, agent, url, post_id, created_at FROM %i ORDER BY id DESC LIMIT %d OFFSET %d', $table, $per_page, $offset ), ARRAY_A );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counting the plugin's own table; the table name goes through %i.
			$total = $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading the plugin's own table; the statement is prepared, the table name through %i.
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, event_type, agent, url, post_id, created_at FROM %i WHERE event_type = %s ORDER BY id DESC LIMIT %d OFFSET %d', $table, $type, $per_page, $offset ), ARRAY_A );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counting the plugin's own table; the statement is prepared, the table name through %i.
			$total = $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE event_type = %s', $table, $type ) );
		}

		$events = [];

		foreach ( is_array( $rows ) ? $rows : [] as $row ) {
			$events[] = [
				'id'         => (int) $row['id'],
				'event_type' => (string) $row['event_type'],
				'agent'      => (string) $row['agent'],
				'url'        => (string) $row['url'],
				'post_id'    => null === $row['post_id'] ? null : (int) $row['post_id'],
				'created_at' => (string) $row['created_at'],
			];
		}

		return [
			'rows'  => $events,
			'total' => (int) $total,
		];
	}

	/**
	 * Deletes events, optionally only the ones older than `$older_than_days`.
	 *
	 * Only `null` means "everything". A zero or negative age is not a request
	 * to empty the table — it is a retention setting that has not been read
	 * properly, or a `?older_than_days=0` in a URL — so it deletes nothing.
	 *
	 * @param int|null $older_than_days Age in days above which an event goes. `null` empties the table; a value below 1 deletes nothing.
	 * @return int Number of rows deleted.
	 */
	public static function purge( ?int $older_than_days = null ): int {
		global $wpdb;

		if ( null !== $older_than_days && $older_than_days < 1 ) {
			return 0;
		}

		$table = self::table();

		if ( null === $older_than_days ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Emptying the plugin's own table; the table name goes through %i.
			$deleted = $wpdb->query( $wpdb->prepare( 'DELETE FROM %i', $table ) );
		} else {
			$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $older_than_days * DAY_IN_SECONDS ) );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Pruning the plugin's own table; the statement is prepared, the table name through %i.
			$deleted = $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', $table, $cutoff ) );
		}

		$deleted = is_numeric( $deleted ) ? (int) $deleted : 0;

		if ( $deleted > 0 ) {
			self::flush_stats();
		}

		return $deleted;
	}

	/**
	 * How many events are stored.
	 *
	 * @return int Row count.
	 */
	public static function count(): int {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counting the plugin's own table; the table name goes through %i.
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
	}

	/**
	 * Crawls and referrals since `$start`.
	 *
	 * @param string $start Start of the window, `Y-m-d H:i:s` in UTC.
	 * @return array<string,int> Keys `crawl` and `referral`.
	 */
	private static function totals( string $start ): array {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading the plugin's own table; the statement is prepared, the table name through %i, and stats() caches the result.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT event_type, COUNT(*) AS total FROM %i WHERE created_at >= %s GROUP BY event_type', $table, $start ), ARRAY_A );

		$totals = [
			'crawl'    => 0,
			'referral' => 0,
		];

		foreach ( is_array( $rows ) ? $rows : [] as $row ) {
			$type = (string) $row['event_type'];

			if ( isset( $totals[ $type ] ) ) {
				$totals[ $type ] = (int) $row['total'];
			}
		}

		return $totals;
	}

	/**
	 * Events since `$start`, grouped by agent and type, busiest first.
	 *
	 * @param string $start Start of the window, `Y-m-d H:i:s` in UTC.
	 * @return array<int,array<string,mixed>> Rows of `agent`, `type` and `count`.
	 */
	private static function by_agent( string $start ): array {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading the plugin's own table; the statement is prepared, the table name through %i, and stats() caches the result.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT agent, event_type, COUNT(*) AS total FROM %i WHERE created_at >= %s GROUP BY agent, event_type ORDER BY total DESC, agent ASC', $table, $start ), ARRAY_A );

		$out = [];

		foreach ( is_array( $rows ) ? $rows : [] as $row ) {
			$out[] = [
				'agent' => (string) $row['agent'],
				'type'  => (string) $row['event_type'],
				'count' => (int) $row['total'],
			];
		}

		return $out;
	}

	/**
	 * The ten most referred pages since `$start`.
	 *
	 * Referrals only: a crawler hitting a page says nothing about whether a
	 * human ever saw it, and mixing the two would rank the site's sitemap above
	 * its best article.
	 *
	 * @param string $start Start of the window, `Y-m-d H:i:s` in UTC.
	 * @return array<int,array<string,mixed>> Rows of `url`, `post_id` and `count`.
	 */
	private static function top_pages( string $start ): array {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading the plugin's own table; the statement is prepared, the table name through %i, and stats() caches the result.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT url, post_id, COUNT(*) AS total FROM %i WHERE event_type = %s AND created_at >= %s GROUP BY url, post_id ORDER BY total DESC, url ASC LIMIT 10', $table, 'referral', $start ), ARRAY_A );

		$out = [];

		foreach ( is_array( $rows ) ? $rows : [] as $row ) {
			$out[] = [
				'url'     => (string) $row['url'],
				'post_id' => null === $row['post_id'] ? null : (int) $row['post_id'],
				'count'   => (int) $row['total'],
			];
		}

		return $out;
	}

	/**
	 * One row per day of the window, days without events included.
	 *
	 * @param string $start Start of the window, `Y-m-d H:i:s` in UTC.
	 * @param int    $days  Length of the window, in days.
	 * @return array<int,array<string,mixed>> Rows of `day`, `crawl` and `referral`, oldest first.
	 */
	private static function timeline( string $start, int $days ): array {
		global $wpdb;

		$table = self::table();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading the plugin's own table; the statement is prepared, the table name through %i, and stats() caches the result.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT DATE(created_at) AS day, event_type, COUNT(*) AS total FROM %i WHERE created_at >= %s GROUP BY day, event_type', $table, $start ), ARRAY_A );

		$counts = [];

		foreach ( is_array( $rows ) ? $rows : [] as $row ) {
			$day  = (string) $row['day'];
			$type = (string) $row['event_type'];

			if ( ! isset( $counts[ $day ] ) ) {
				$counts[ $day ] = [
					'crawl'    => 0,
					'referral' => 0,
				];
			}

			if ( isset( $counts[ $day ][ $type ] ) ) {
				$counts[ $day ][ $type ] = (int) $row['total'];
			}
		}

		$first    = strtotime( $start . ' UTC' );
		$timeline = [];

		for ( $offset = 0; $offset < $days; $offset++ ) {
			$day = gmdate( 'Y-m-d', $first + ( $offset * DAY_IN_SECONDS ) );

			$timeline[] = [
				'day'      => $day,
				'crawl'    => $counts[ $day ]['crawl'] ?? 0,
				'referral' => $counts[ $day ]['referral'] ?? 0,
			];
		}

		return $timeline;
	}

	/**
	 * Midnight UTC, `$days` days ago counting today as one.
	 *
	 * @param int $days Length of the window, in days.
	 * @return string Start of the window, `Y-m-d H:i:s` in UTC.
	 */
	private static function window_start( int $days ): string {
		return gmdate( 'Y-m-d', time() - ( ( $days - 1 ) * DAY_IN_SECONDS ) ) . ' 00:00:00';
	}

	/**
	 * Drops the cached `stats()` answers a purge just made wrong.
	 *
	 * @return void
	 */
	private static function flush_stats(): void {
		foreach ( self::STATS_WINDOWS as $days ) {
			Cache::delete( 'cit_stats_' . $days );
		}
	}

	/**
	 * Cuts a value to a column's width, without splitting a character in two.
	 *
	 * @param string $value Value to cut.
	 * @param int    $limit Column width, in characters.
	 * @return string Value, at most `$limit` characters long.
	 */
	private static function truncate( string $value, int $limit ): string {
		if ( function_exists( 'mb_substr' ) ) {
			return mb_strlen( $value ) > $limit ? mb_substr( $value, 0, $limit ) : $value;
		}

		return strlen( $value ) > $limit ? substr( $value, 0, $limit ) : $value;
	}
}