<?php
declare( strict_types=1 );

namespace CodeBold\Redirect\Monitor;

use CodeBold\Redirect\Core\Installer;
use CodeBold\Redirect\Redirects\Address;

defined( 'ABSPATH' ) || exit;

/**
 * The addresses people asked for that did not exist.
 *
 * One row per address, with a count — see Installer for why. Direct queries throughout,
 * prepared, for the same reason as the redirects table: there is no core API to use.
 */
final class NotFoundLog {

	/**
	 * The list is capped. A site under a scanner can produce tens of thousands of
	 * distinct junk addresses a day; past this many, the least-visited, longest-quiet
	 * rows are dropped first, which are the ones nobody would have fixed anyway.
	 */
	public const MAX_ROWS = 10000;

	public function table(): string {
		return Installer::not_found_table();
	}

	/**
	 * Count one visit. One statement, whether the address is new or not.
	 */
	public function hit( Address $address, string $referrer, string $user_agent, bool $is_bot ): void {
		global $wpdb;

		$now = current_time( 'mysql', true );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- our own table; every value is a placeholder.
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$this->table()} (url, url_hash, hits, first_seen, last_seen, referrer, user_agent, is_bot)
				VALUES (%s, %s, 1, %s, %s, %s, %s, %d)
				ON DUPLICATE KEY UPDATE
					hits = hits + 1,
					last_seen = VALUES(last_seen),
					referrer = IF(VALUES(referrer) <> '', VALUES(referrer), referrer),
					user_agent = VALUES(user_agent),
					is_bot = VALUES(is_bot)",
				mb_substr( $address->path, 0, 2000 ),
				$address->hash(),
				$now,
				$now,
				mb_substr( $referrer, 0, 1000 ),
				mb_substr( $user_agent, 0, 255 ),
				(int) $is_bot
			)
		);
		// phpcs:enable
	}

	/**
	 * @param array{search?: string, view?: string, orderby?: string, order?: string, page?: int, per_page?: int} $args
	 * @return array{items: list<array<string, mixed>>, total: int}
	 */
	public function find( array $args = [] ): array {
		global $wpdb;

		$where = [ '1=1' ];

		$search = trim( (string) ( $args['search'] ?? '' ) );

		if ( '' !== $search ) {
			$like    = '%' . $wpdb->esc_like( $search ) . '%';
			$where[] = $wpdb->prepare( '(url LIKE %s OR referrer LIKE %s)', $like, $like );
		}

		switch ( $args['view'] ?? 'active' ) {
			case 'ignored':
				$where[] = 'ignored = 1';
				break;
			case 'people':
				$where[] = 'ignored = 0 AND is_bot = 0';
				break;
			case 'bots':
				$where[] = 'ignored = 0 AND is_bot = 1';
				break;
			case 'referred':
				$where[] = "ignored = 0 AND referrer <> ''";
				break;
			case 'all':
				break;
			default:
				$where[] = 'ignored = 0';
		}

		$columns = [
			'hits'       => 'hits',
			'last_seen'  => 'last_seen',
			'first_seen' => 'first_seen',
			'url'        => 'url',
		];

		$orderby  = $columns[ $args['orderby'] ?? 'hits' ] ?? 'hits';
		$order    = 'asc' === strtolower( (string) ( $args['order'] ?? 'desc' ) ) ? 'ASC' : 'DESC';
		$per_page = max( 1, min( 500, (int) ( $args['per_page'] ?? 25 ) ) );
		$page     = max( 1, (int) ( $args['page'] ?? 1 ) );
		$offset   = ( $page - 1 ) * $per_page;

		$sql_where = implode( ' AND ', $where );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- fragments prepared above.
		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table()} WHERE {$sql_where}" );
		$rows  = (array) $wpdb->get_results( "SELECT * FROM {$this->table()} WHERE {$sql_where} ORDER BY {$orderby} {$order}, id DESC LIMIT {$per_page} OFFSET {$offset}", ARRAY_A );
		// phpcs:enable

		return [ 'items' => array_map( [ self::class, 'shape' ], $rows ), 'total' => $total ];
	}

	/**
	 * @param array<string, mixed> $row
	 * @return array<string, mixed>
	 */
	public static function shape( array $row ): array {
		return [
			'id'         => (int) $row['id'],
			'url'        => (string) $row['url'],
			'hits'       => (int) $row['hits'],
			'first_seen' => (string) $row['first_seen'],
			'last_seen'  => (string) $row['last_seen'],
			'referrer'   => (string) $row['referrer'],
			'user_agent' => (string) $row['user_agent'],
			'is_bot'     => (bool) $row['is_bot'],
			'ignored'    => (bool) $row['ignored'],
		];
	}

	/**
	 * @param list<int> $ids
	 * @return list<array<string, mixed>>
	 */
	public function many( array $ids ): array {
		global $wpdb;

		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );

		if ( [] === $ids ) {
			return [];
		}

		$in = implode( ',', $ids );

		return array_map( [ self::class, 'shape' ], (array) $wpdb->get_results( "SELECT * FROM {$this->table()} WHERE id IN ({$in})", ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * @param list<int> $ids
	 */
	public function set_ignored( array $ids, bool $ignored ): int {
		global $wpdb;

		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );

		if ( [] === $ids ) {
			return 0;
		}

		$in = implode( ',', $ids );

		return (int) $wpdb->query( $wpdb->prepare( "UPDATE {$this->table()} SET ignored = %d WHERE id IN ({$in})", (int) $ignored ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * @param list<int> $ids
	 */
	public function delete( array $ids ): int {
		global $wpdb;

		$ids = array_values( array_filter( array_map( 'absint', $ids ) ) );

		if ( [] === $ids ) {
			return 0;
		}

		$in = implode( ',', $ids );

		return (int) $wpdb->query( "DELETE FROM {$this->table()} WHERE id IN ({$in})" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Empty the list. Ignored addresses are kept unless asked, because ignoring is a
	 * decision somebody made and "clear the list" is not a request to undo it.
	 */
	public function clear( bool $include_ignored = false ): int {
		global $wpdb;

		$where = $include_ignored ? '' : 'WHERE ignored = 0';

		return (int) $wpdb->query( "DELETE FROM {$this->table()} {$where}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * @return list<array<string, mixed>>
	 */
	public function by_hash( string $hash ): array {
		global $wpdb;

		return array_map( [ self::class, 'shape' ], (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE url_hash = %s AND ignored = 0", $hash ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Put back rows taken out by a fix or a delete — the undo.
	 *
	 * Visits that arrived in the meantime are added to the restored count rather than
	 * lost, and the earliest first-seen and latest last-seen win, so an undo never makes
	 * the history shorter than it really is.
	 *
	 * @param list<array<string, mixed>> $rows As shape() returns them.
	 */
	public function restore( array $rows ): int {
		global $wpdb;

		$count = 0;

		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) || empty( $row['url'] ) ) {
				continue;
			}

			$address = Address::from_request( (string) $row['url'] );
			$first   = self::datetime( $row['first_seen'] ?? '' );
			$last    = self::datetime( $row['last_seen'] ?? '' );

			// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- our own table; every value is a placeholder.
			$done = $wpdb->query(
				$wpdb->prepare(
					"INSERT INTO {$this->table()} (url, url_hash, hits, first_seen, last_seen, referrer, user_agent, is_bot, ignored)
					VALUES (%s, %s, %d, %s, %s, %s, %s, %d, %d)
					ON DUPLICATE KEY UPDATE
						hits = hits + VALUES(hits),
						first_seen = LEAST(first_seen, VALUES(first_seen)),
						last_seen = GREATEST(last_seen, VALUES(last_seen)),
						referrer = IF(referrer = '', VALUES(referrer), referrer),
						ignored = VALUES(ignored)",
					mb_substr( $address->path, 0, 2000 ),
					$address->hash(),
					max( 1, (int) ( $row['hits'] ?? 1 ) ),
					$first,
					$last,
					mb_substr( esc_url_raw( (string) ( $row['referrer'] ?? '' ) ), 0, 1000 ),
					mb_substr( sanitize_text_field( (string) ( $row['user_agent'] ?? '' ) ), 0, 255 ),
					empty( $row['is_bot'] ) ? 0 : 1,
					empty( $row['ignored'] ) ? 0 : 1
				)
			);
			// phpcs:enable

			if ( false !== $done ) {
				++$count;
			}
		}

		return $count;
	}

	/** A stored date from a client, or now if it is not one. */
	private static function datetime( mixed $value ): string {
		$value = (string) $value;

		return preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $value ) ? $value : current_time( 'mysql', true );
	}

	public function delete_by_hash( string $hash ): int {
		global $wpdb;

		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$this->table()} WHERE url_hash = %s AND ignored = 0", $hash ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Rows to try a new pattern rule against. Bounded, because this runs while somebody
	 * waits for a save to finish.
	 *
	 * @return list<array{id: int, url: string}>
	 */
	public function recent_urls( int $limit = 5000 ): array {
		global $wpdb;

		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT id, url FROM {$this->table()} WHERE ignored = 0 ORDER BY last_seen DESC LIMIT %d", $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array_map( static fn( array $r ): array => [ 'id' => (int) $r['id'], 'url' => (string) $r['url'] ], $rows );
	}

	/**
	 * Drop addresses not seen for a while, then trim to the cap.
	 */
	public function prune( int $days ): int {
		global $wpdb;

		$cutoff  = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
		$removed = (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$this->table()} WHERE last_seen < %s AND ignored = 0", $cutoff ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table()}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( $total > self::MAX_ROWS ) {
			$excess = $total - self::MAX_ROWS;

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$removed += (int) $wpdb->query( $wpdb->prepare( "DELETE FROM {$this->table()} WHERE ignored = 0 ORDER BY hits ASC, last_seen ASC LIMIT %d", $excess ) );
		}

		return $removed;
	}

	/** @return array{active: int, ignored: int, people: int, bots: int, referred: int, new: int} */
	public function counts( ?string $since = null ): array {
		global $wpdb;

		$since = $since ?? gmdate( 'Y-m-d H:i:s', time() - WEEK_IN_SECONDS );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- our own table; the date is a placeholder.
		$row = (array) $wpdb->get_row(
			$wpdb->prepare(
				"SELECT SUM(ignored = 0) AS active,
					SUM(ignored = 1) AS ignored,
					SUM(ignored = 0 AND is_bot = 0) AS people,
					SUM(ignored = 0 AND is_bot = 1) AS bots,
					SUM(ignored = 0 AND referrer <> '') AS referred,
					SUM(ignored = 0 AND first_seen > %s) AS new_rows
				FROM {$this->table()}",
				$since
			)
		);
		// phpcs:enable

		return [
			'active'   => (int) ( $row['active'] ?? 0 ),
			'ignored'  => (int) ( $row['ignored'] ?? 0 ),
			'people'   => (int) ( $row['people'] ?? 0 ),
			'bots'     => (int) ( $row['bots'] ?? 0 ),
			'referred' => (int) ( $row['referred'] ?? 0 ),
			'new'      => (int) ( $row['new_rows'] ?? 0 ),
		];
	}

	/**
	 * The most-requested missing addresses, for the dashboard.
	 *
	 * @return list<array<string, mixed>>
	 */
	public function top( int $limit = 5, int $days = 7 ): array {
		global $wpdb;

		$since = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE ignored = 0 AND last_seen > %s ORDER BY hits DESC LIMIT %d", $since, $limit ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array_map( [ self::class, 'shape' ], $rows );
	}
}