<?php
/**
 * Database access for the crawler log table.
 *
 * @package PressRoost\AIVE
 */

namespace PressRoost\AIVE\Data;

use PressRoost\AIVE\Database;

/**
 * Every SQL statement that touches `aive_crawler_log`.
 *
 * The module and the admin page hold no SQL of their own. Concentrating it here
 * means the ORDER BY allow-list, the prepared bindings and the error handling
 * are written once and reviewed once, rather than repeated at each call site
 * where one of them could be forgotten.
 *
 * Two conventions apply throughout:
 *
 * - **`visited_at_utc` is the time authority.** Every comparison, ordering and
 *   aggregate below reads that column and nothing else, and every datetime
 *   string crossing this class's boundary is UTC. The sibling `visited_at`
 *   column holds the same instant as a site-local reading and is written for
 *   compatibility only; it is deliberately never read here. An offset-free
 *   local `DATETIME` cannot order or measure the two distinct instants that
 *   share a wall-clock reading inside a daylight-saving fall-back hour, so a
 *   window built on it is wrong by up to an hour twice a year - and a later
 *   change of the site timezone would retroactively move it. Nothing relies on
 *   the column's `CURRENT_TIMESTAMP` default either, which would be the
 *   database server's clock rather than the site's.
 * - **A failed query returns null, never an empty result.** Callers must be able
 *   to tell "no visits recorded" from "the query did not run", because showing
 *   an empty table for a broken query would misreport the site's data. Errors
 *   are suppressed around each statement so a database fault can never emit
 *   output into a page or a frontend response.
 *
 * @since 1.1.0
 */
final class CrawlerLogStore {

	/**
	 * Maximum rows the Recent Visits query returns.
	 *
	 * @since 1.1.0
	 * @var int
	 */
	public const RECENT_LIMIT = 100;

	/**
	 * Separator GROUP_CONCAT uses to join distinct bot names in daily_activity().
	 *
	 * A bot name cannot itself contain a comma - it is bounded to the
	 * characters BOT_TOKEN_PATTERN in Helpers allows, which excludes it - so
	 * splitting on this separator never reassembles two names into one.
	 *
	 * @since 1.7.0
	 * @var string
	 */
	private const BOT_NAME_SEPARATOR = ',';

	/**
	 * Sort keys the Visits by Bot query accepts, mapped to SQL expressions.
	 *
	 * Request input selects a key from this map; it never reaches the statement.
	 * The values are the only ORDER BY expressions the query can be built with,
	 * so no caller - and no crafted `orderby` parameter - can extend the sort
	 * surface or inject into it.
	 *
	 * @since 1.1.0
	 * @var array<string, string>
	 */
	private const SORT_COLUMNS = array(
		'bot_name'    => 'bot_name',
		'bot_type'    => 'bot_type',
		'visit_count' => 'visit_count',
		'last_visit'  => 'last_visit',
	);

	/**
	 * Returns the sort keys the Visits by Bot table accepts.
	 *
	 * @since 1.1.0
	 *
	 * @return array<int, string> Accepted sort keys.
	 */
	public static function sort_keys() {
		return array_keys( self::SORT_COLUMNS );
	}

	/**
	 * Returns the prefixed crawler log table name.
	 *
	 * @since 1.1.0
	 *
	 * @return string Table name.
	 */
	public function table() {
		return Database::table_name( 'crawler_log' );
	}

	/**
	 * Returns the table name backtick-quoted for interpolation.
	 *
	 * A table identifier cannot be bound as a prepared parameter, so every
	 * statement below interpolates this. The value is `$wpdb->prefix` plus a
	 * hard-coded identifier and carries no caller data; stripping backticks
	 * matches `Database::drop_tables()` and means the quoting cannot be broken
	 * out of even if a site's prefix contained one.
	 *
	 * @since 1.1.0
	 *
	 * @return string Quoted table identifier.
	 */
	private function quoted_table() {
		return '`' . str_replace( '`', '', $this->table() ) . '`';
	}

	/**
	 * Writes one crawler visit.
	 *
	 * The caller is responsible for bounding and normalising the values; this
	 * method binds them and reports whether the row landed. Column formats are
	 * stated explicitly so a null `ip_address` or `post_id` is written as SQL
	 * NULL rather than coerced to an empty string or zero.
	 *
	 * @since 1.1.0
	 *
	 * @param array{bot_name: string, bot_type: string, request_uri: string, request_method: string, user_agent: string, ip_address: string|null, visited_at: string, visited_at_utc: string, post_id: int|null} $visit Visit to record.
	 * @return bool True when one row was inserted.
	 */
	public function insert( array $visit ) {
		global $wpdb;

		$previous = $wpdb->suppress_errors( true );

		$inserted = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Append-only write to the plugin's own table; there is no cache to update.
			$this->table(),
			array(
				'bot_name'       => $visit['bot_name'],
				'bot_type'       => $visit['bot_type'],
				'request_uri'    => $visit['request_uri'],
				'request_method' => $visit['request_method'],
				'user_agent'     => $visit['user_agent'],
				'ip_address'     => $visit['ip_address'],
				'status_code'    => null,
				'visited_at'     => $visit['visited_at'],
				'visited_at_utc' => $visit['visited_at_utc'],
				'post_id'        => $visit['post_id'],
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%d' )
		);

		$wpdb->suppress_errors( $previous );

		return 1 === $inserted;
	}

	/**
	 * Deletes rows older than a cutoff.
	 *
	 * Strictly older: a row whose canonical instant equals the cutoff is inside
	 * the retention window and stays. Running this twice deletes nothing the
	 * second time, which is what makes the cleanup cron safe to fire repeatedly.
	 *
	 * @since 1.1.0
	 *
	 * @param string $cutoff UTC `Y-m-d H:i:s` boundary.
	 * @return int|null Rows deleted, or null when the query failed.
	 */
	public function delete_older_than( $cutoff ) {
		global $wpdb;

		$sql = $wpdb->prepare( 'DELETE FROM ' . $this->quoted_table() . ' WHERE visited_at_utc < %s', $cutoff ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only the table identifier is interpolated; see quoted_table(). The cutoff is bound.

		return $this->run(
			static function () use ( $wpdb, $sql ) {
				return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Statement prepared by the caller; retention cleanup on the plugin's own table has nothing to cache.
			},
			static function ( $result ) {
				return (int) $result;
			}
		);
	}

	/**
	 * Counts rows recorded at or after a boundary.
	 *
	 * Inclusive on the lower bound, so a visit landing exactly on the boundary
	 * is inside the reporting window.
	 *
	 * @since 1.1.0
	 *
	 * @param string $since UTC `Y-m-d H:i:s` lower bound.
	 * @return int|null Visit count, or null when the query failed.
	 */
	public function count_since( $since ) {
		global $wpdb;

		$sql = $wpdb->prepare( 'SELECT COUNT(*) FROM ' . $this->quoted_table() . ' WHERE visited_at_utc >= %s', $since ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only the table identifier is interpolated; see quoted_table(). The bound is bound.

		return $this->run(
			static function () use ( $wpdb, $sql ) {
				return $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Statement prepared by the caller. Aggregates an append-only log; a cached answer would be stale by design.
			},
			static function ( $result ) {
				return (int) $result;
			}
		);
	}

	/**
	 * Counts distinct bot names recorded at or after a boundary.
	 *
	 * @since 1.1.0
	 *
	 * @param string $since UTC `Y-m-d H:i:s` lower bound.
	 * @return int|null Distinct bot count, or null when the query failed.
	 */
	public function count_distinct_bots_since( $since ) {
		global $wpdb;

		$sql = $wpdb->prepare( 'SELECT COUNT(DISTINCT bot_name) FROM ' . $this->quoted_table() . ' WHERE visited_at_utc >= %s', $since ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only the table identifier is interpolated; see quoted_table(). The bound is bound.

		return $this->run(
			static function () use ( $wpdb, $sql ) {
				return $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Statement prepared by the caller. Aggregates an append-only log; a cached answer would be stale by design.
			},
			static function ( $result ) {
				return (int) $result;
			}
		);
	}

	/**
	 * Returns one aggregate row per recorded bot name.
	 *
	 * Grouped by `bot_name` alone, because the admin table shows one row per
	 * bot. `MAX( bot_type )` picks the type: a bot's type is fixed by the
	 * registry, so the rows of one bot normally agree and the aggregate is that
	 * single value. They can disagree only if the registry reclassified the
	 * crawler between two visits, and then MAX() at least resolves it the same
	 * way on every request instead of leaving it to row order. It also keeps the
	 * statement valid under `ONLY_FULL_GROUP_BY`.
	 *
	 * @since 1.1.0
	 *
	 * `last_visit` is the maximum canonical instant, so it is UTC like every
	 * other datetime this class returns.
	 *
	 * @param string $orderby Sort key; an unrecognised value falls back to `visit_count`.
	 * @param string $order   `asc` or `desc`; anything else falls back to `desc`.
	 * @return array<int, array{bot_name: string, bot_type: string, visit_count: int, last_visit: string}>|null Aggregates with a UTC `last_visit`, or null when the query failed.
	 */
	public function visits_by_bot( $orderby = 'visit_count', $order = 'desc' ) {
		global $wpdb;

		$column    = isset( self::SORT_COLUMNS[ $orderby ] ) ? self::SORT_COLUMNS[ $orderby ] : self::SORT_COLUMNS['visit_count'];
		$direction = 'asc' === strtolower( (string) $order ) ? 'ASC' : 'DESC';

		/*
		 * `bot_name ASC` is appended as a secondary sort so that bots tied on the
		 * chosen column come back in the same order on every request. Without it
		 * the table would reshuffle between page loads at the database's
		 * discretion, which reads as data changing when nothing has.
		 */
		$sql = 'SELECT bot_name, MAX( bot_type ) AS bot_type, COUNT(*) AS visit_count, MAX( visited_at_utc ) AS last_visit'
			. ' FROM ' . $this->quoted_table()
			. ' GROUP BY bot_name'
			. ' ORDER BY ' . $column . ' ' . $direction . ', bot_name ASC';

		return $this->run(
			static function () use ( $wpdb, $sql ) {
				return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- No caller data in the statement: the table name is built from $wpdb->prefix and the sort comes from self::SORT_COLUMNS. Aggregates over an append-only log; a cached answer would be stale by design.
			},
			static function ( $rows ) {
				$aggregates = array();

				foreach ( (array) $rows as $row ) {
					$aggregates[] = array(
						'bot_name'    => (string) $row['bot_name'],
						'bot_type'    => (string) $row['bot_type'],
						'visit_count' => (int) $row['visit_count'],
						'last_visit'  => (string) $row['last_visit'],
					);
				}

				return $aggregates;
			}
		);
	}

	/**
	 * Returns the newest visits, optionally limited to one bot.
	 *
	 * The bot filter is bound as a value, so an unknown or crafted name yields
	 * an empty result rather than an error or a widened query.
	 *
	 * @since 1.1.0
	 *
	 * @param string|null $bot   Bot name to filter by, or null for every bot.
	 * @param int         $limit Maximum rows to return.
	 * @return array<int, array{id: int, bot_name: string, bot_type: string, request_uri: string, ip_address: string|null, visited_at_utc: string, post_id: int|null}>|null Visits newest first with a UTC instant, or null when the query failed.
	 */
	public function recent_visits( $bot = null, $limit = self::RECENT_LIMIT ) {
		global $wpdb;

		$limit = max( 1, (int) $limit );

		/*
		 * Written out as two complete statements rather than assembled from
		 * fragments. The placeholders then stay visible to $wpdb->prepare() and
		 * to the static analysis that counts them, which an assembled string
		 * hides from both.
		 *
		 * Ordered by the canonical instant, which is the only column that can
		 * put two visits an hour apart inside a fall-back hour in the right
		 * order. `id DESC` breaks ties, because a DATETIME has one-second
		 * resolution and so cannot order two visits inside the same second.
		 */
		if ( null === $bot ) {
			$sql = $wpdb->prepare( 'SELECT id, bot_name, bot_type, request_uri, ip_address, visited_at_utc, post_id FROM ' . $this->quoted_table() . ' ORDER BY visited_at_utc DESC, id DESC LIMIT %d', $limit ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only the table identifier is interpolated; see quoted_table(). The limit is bound.
		} else {
			$sql = $wpdb->prepare( 'SELECT id, bot_name, bot_type, request_uri, ip_address, visited_at_utc, post_id FROM ' . $this->quoted_table() . ' WHERE bot_name = %s ORDER BY visited_at_utc DESC, id DESC LIMIT %d', $bot, $limit ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only the table identifier is interpolated; see quoted_table(). The bot name and limit are bound.
		}

		return $this->run(
			static function () use ( $wpdb, $sql ) {
				return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Statement prepared by the caller. Reads an append-only log; a cached answer would be stale by design.
			},
			static function ( $rows ) {
				$visits = array();

				foreach ( (array) $rows as $row ) {
					$visits[] = array(
						'id'             => (int) $row['id'],
						'bot_name'       => (string) $row['bot_name'],
						'bot_type'       => (string) $row['bot_type'],
						'request_uri'    => (string) $row['request_uri'],
						'ip_address'     => null === $row['ip_address'] ? null : (string) $row['ip_address'],
						'visited_at_utc' => (string) $row['visited_at_utc'],
						'post_id'        => null === $row['post_id'] ? null : (int) $row['post_id'],
					);
				}

				return $visits;
			}
		);
	}

	/**
	 * Returns one row per UTC calendar date with at least one visit in a window.
	 *
	 * Grouped by `DATE( visited_at_utc )`, which is the same canonical column
	 * every other aggregate here reads. A date with no visits is simply absent
	 * from the result; the caller fills the bounded 14-date window with zeroes,
	 * because this class has no notion of which dates the caller's window
	 * actually spans.
	 *
	 * @since 1.7.0
	 *
	 * @param string $since_utc UTC `Y-m-d H:i:s` inclusive lower bound.
	 * @param string $until_utc UTC `Y-m-d H:i:s` inclusive upper bound.
	 * @return array<string, array{visit_count: int, bot_names: array<int, string>}>|null Rows keyed by `Y-m-d`, or null when the query failed.
	 */
	public function daily_activity( $since_utc, $until_utc ) {
		global $wpdb;

		$query = 'SELECT DATE( visited_at_utc ) AS day, COUNT(*) AS visit_count, GROUP_CONCAT( DISTINCT bot_name ORDER BY bot_name SEPARATOR "' . self::BOT_NAME_SEPARATOR . '" ) AS bot_names FROM ' . $this->quoted_table() . ' WHERE visited_at_utc >= %s AND visited_at_utc <= %s GROUP BY DATE( visited_at_utc )'; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Only the table identifier and the fixed separator literal are interpolated; see quoted_table(). The bounds are bound below.

		$sql = $wpdb->prepare( $query, $since_utc, $until_utc ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Statement built above; the bounds are bound here.

		return $this->run(
			static function () use ( $wpdb, $sql ) {
				return $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Statement prepared by the caller. Aggregates over an append-only log; a cached answer would be stale by design.
			},
			static function ( $rows ) {
				$days = array();

				foreach ( (array) $rows as $row ) {
					$names = '' === (string) $row['bot_names']
						? array()
						: explode( self::BOT_NAME_SEPARATOR, (string) $row['bot_names'] );

					$days[ (string) $row['day'] ] = array(
						'visit_count' => (int) $row['visit_count'],
						'bot_names'   => $names,
					);
				}

				return $days;
			}
		);
	}

	/**
	 * Runs one statement with errors suppressed and maps a successful result.
	 *
	 * `$wpdb->flush()` clears `last_error` before the statement so that a fault
	 * left by an earlier query cannot be read as this one's. Suppression is what
	 * keeps a database fault from printing into a rendered page or a frontend
	 * response; the caller learns about it from the null return instead.
	 *
	 * @since 1.1.0
	 *
	 * @param callable $query Runs the statement and returns $wpdb's raw result.
	 * @param callable $map   Converts a successful raw result to the public shape.
	 * @return mixed|null Mapped result, or null when the statement failed.
	 */
	private function run( callable $query, callable $map ) {
		global $wpdb;

		$previous = $wpdb->suppress_errors( true );

		$wpdb->flush();

		$result = $query();
		$failed = false === $result || '' !== (string) $wpdb->last_error;

		$wpdb->suppress_errors( $previous );

		return $failed ? null : $map( $result );
	}
}