<?php
/**
 * What visitors searched for.
 *
 * @package SharelyAI
 */

namespace SharelyAI;

defined( 'ABSPATH' ) || exit;

/**
 * The local search log, kept in aggregate (a port of Drupal's QueryLog).
 *
 * The platform meters its own calls but shows query analytics only to a
 * signed-in Sharely administrator, so the numbers a site owner wants are
 * recorded here.
 *
 * Deliberately thin: the query, how many results it produced, and which engine
 * answered. No user id, no IP, no session. A search is a sentence someone
 * typed, and tying it to a person is the part that turns a counter into
 * surveillance.
 *
 * Nothing writes here until Search exists; the table and this class come first
 * so the screen and its retention job are in place when it does.
 */
final class Stats {

	/**
	 * Bumped when the table changes, so dbDelta runs again on upgrade.
	 */
	public const SCHEMA_VERSION = 1;

	private const SCHEMA_OPTION = 'sharelyai_stats_schema';

	/**
	 * Constructor.
	 *
	 * @param Connection $connection Whether recording is on, and for how long.
	 */
	public function __construct( private Connection $connection ) {}

	/**
	 * This table's name, without the prefix.
	 */
	private const TABLE = 'sharelyai_query_stat';

	/**
	 * The table name, with this install's prefix.
	 */
	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * Creates or updates the table. Safe to call repeatedly.
	 */
	public static function install_table(): void {
		global $wpdb;

		if ( (int) get_option( self::SCHEMA_OPTION, 0 ) === self::SCHEMA_VERSION ) {
			return;
		}

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

		$table   = self::table();
		$collate = $wpdb->get_charset_collate();
		// `query` is indexed by its first 191 characters: the longest a utf8mb4
		// index column can be, and far longer than anything anyone types.
		dbDelta(
			"CREATE TABLE {$table} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				query varchar(255) NOT NULL DEFAULT '',
				results int(11) NOT NULL DEFAULT 0,
				source varchar(16) NOT NULL DEFAULT '',
				created bigint(20) unsigned NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				KEY created (created),
				KEY query_created (query(191), created)
			) {$collate}"
		);

		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
	}

	/**
	 * Forgets that the table was ever installed. For uninstall only.
	 */
	public static function forget_schema(): void {
		delete_option( self::SCHEMA_OPTION );
	}

	/**
	 * Whether recording is switched on.
	 */
	public function enabled(): bool {
		return (bool) $this->connection->get( 'stats_enabled', true );
	}

	/**
	 * Records one search.
	 *
	 * @param string $keys    What was typed.
	 * @param int    $results How many results the visitor was shown, after access filtering.
	 * @param string $source  Which engine answered: semantic, lexical or core.
	 */
	public function record( string $keys, int $results, string $source ): void {
		$keys = trim( mb_substr( $keys, 0, 255 ) );
		if ( ! $this->enabled() || '' === $keys ) {
			return;
		}

		global $wpdb;
		// A search must never fail because a counter could not be written.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table, no API, nothing to cache.
		$wpdb->insert(
			self::table(),
			array(
				'query'   => mb_strtolower( $keys ),
				'results' => max( 0, $results ),
				'source'  => mb_substr( $source, 0, 16 ),
				'created' => time(),
			),
			array( '%s', '%d', '%s', '%d' )
		);
	}

	/**
	 * Headline counts for a window.
	 *
	 * @param int $days How far back to look.
	 *
	 * @return array{searches:int, empty:int, terms:int, by_source:array<string,int>}
	 */
	public function summary( int $days = 30 ): array {
		global $wpdb;
		$since = time() - ( max( 1, $days ) * DAY_IN_SECONDS );
		$table = $wpdb->prefix . self::TABLE;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table; the name is not user input and cannot be a placeholder.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(*) AS searches,
				        SUM(CASE WHEN results = 0 THEN 1 ELSE 0 END) AS empty_results,
				        COUNT(DISTINCT query) AS terms
				 FROM {$table} WHERE created >= %d",
				$since
			),
			ARRAY_A
		);

		$by_source = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT source, COUNT(*) AS n FROM {$table} WHERE created >= %d GROUP BY source",
				$since
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$sources = array();
		foreach ( (array) $by_source as $source ) {
			$sources[ (string) $source['source'] ] = (int) $source['n'];
		}

		return array(
			'searches'  => (int) ( $row['searches'] ?? 0 ),
			'empty'     => (int) ( $row['empty_results'] ?? 0 ),
			'terms'     => (int) ( $row['terms'] ?? 0 ),
			'by_source' => $sources,
		);
	}

	/**
	 * The most-searched queries in a window.
	 *
	 * @param int  $days       How far back to look.
	 * @param int  $limit      How many rows.
	 * @param bool $empty_only Only searches that found nothing.
	 *
	 * @return array<int, array{query:string, n:int, results:float}>
	 */
	public function top( int $days = 30, int $limit = 10, bool $empty_only = false ): array {
		global $wpdb;
		$since = time() - ( max( 1, $days ) * DAY_IN_SECONDS );
		$table = $wpdb->prefix . self::TABLE;

		// Two whole queries rather than one with a condition interpolated into
		// it. The fragment was a choice between two literals and perfectly
		// safe, but a scanner cannot see that and neither can a reviewer
		// reading it quickly — and this is the file where being obviously
		// safe is worth more than being short.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table; a table name cannot be a placeholder.
		$rows = $empty_only
			? $wpdb->get_results(
				$wpdb->prepare(
					"SELECT query, COUNT(*) AS n, AVG(results) AS avg_results
					 FROM {$table} WHERE created >= %d AND results = 0
					 GROUP BY query ORDER BY n DESC, query ASC LIMIT %d",
					$since,
					max( 1, $limit )
				),
				ARRAY_A
			)
			: $wpdb->get_results(
				$wpdb->prepare(
					"SELECT query, COUNT(*) AS n, AVG(results) AS avg_results
					 FROM {$table} WHERE created >= %d
					 GROUP BY query ORDER BY n DESC, query ASC LIMIT %d",
					$since,
					max( 1, $limit )
				),
				ARRAY_A
			);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$out = array();
		foreach ( (array) $rows as $row ) {
			$out[] = array(
				'query'   => (string) $row['query'],
				'n'       => (int) $row['n'],
				'results' => round( (float) $row['avg_results'], 1 ),
			);
		}
		return $out;
	}

	/**
	 * Drops rows past the retention window.
	 *
	 * @return int Rows removed.
	 */
	public function prune(): int {
		global $wpdb;
		$days  = max( 1, (int) $this->connection->get( 'stats_retention_days', 30 ) );
		$table = $wpdb->prefix . self::TABLE;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table; a table name cannot be a placeholder.
		return (int) $wpdb->query(
			$wpdb->prepare( "DELETE FROM {$table} WHERE created < %d", time() - ( $days * DAY_IN_SECONDS ) )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Forgets every recorded search.
	 *
	 * DELETE rather than TRUNCATE. Truncating is faster, but it is DDL: it
	 * commits the surrounding transaction, which breaks the isolation the test
	 * suite relies on, and some managed hosts do not grant it. These tables hold
	 * a few thousand rows at most, so the speed is not worth either.
	 */
	public function clear(): void {
		global $wpdb;
		$table = $wpdb->prefix . self::TABLE;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- own table, no API for it.
		$wpdb->query( "DELETE FROM {$table}" );
	}
}