<?php
declare( strict_types=1 );

namespace CodeBold\Redirect\Redirects;

use CodeBold\Redirect\Core\Installer;

defined( 'ABSPATH' ) || exit;

/**
 * Reading and writing the redirects table.
 *
 * The table has no core API behind it, so the queries here are direct — each prepared,
 * and each with a phpcs:ignore naming the sniff, one line at a time.
 *
 * Two lookups run on every front-end request and are built to be cheap:
 *
 *  - exact rules, by the hash of the requested path — one indexed query, which returns
 *    nothing at all on the overwhelming majority of page views;
 *  - pattern rules (wildcard and regex), which cannot be looked up and must each be
 *    tried. They are few on almost every site, so they are kept in one option and
 *    loaded with the others WordPress already reads at start-up.
 */
final class Repository {

	private const PATTERN_CACHE = 'codebold_redirect_patterns';

	/**
	 * Past this many pattern rules the cache stops being autoloaded. A site that imported
	 * thousands of wildcards should not carry all of them into every admin page load.
	 */
	private const AUTOLOAD_LIMIT = 300;

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

	public function get( int $id ): ?Rule {
		global $wpdb;

		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return $row ? Rule::from_row( $row ) : null;
	}

	/**
	 * Store a rule that has already been through the Validator.
	 */
	public function insert( Rule $rule ): int {
		global $wpdb;

		$now  = current_time( 'mysql', true );
		$data = $rule->to_row() + [
			'created_at' => '' !== $rule->created_at ? $rule->created_at : $now,
			'updated_at' => $now,
		];

		// An explicit id only when putting back a rule that was just deleted, so that an
		// undo leaves every link to it — in the browser history, in the CLI — working.
		if ( $rule->id > 0 && null === $this->get( $rule->id ) ) {
			$data = [ 'id' => $rule->id ] + $data;
		}

		$wpdb->insert( $this->table(), $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery

		$this->changed();

		return (int) $wpdb->insert_id;
	}

	public function update( Rule $rule ): bool {
		global $wpdb;

		$data = $rule->to_row() + [ 'updated_at' => current_time( 'mysql', true ) ];

		$done = false !== $wpdb->update( $this->table(), $data, [ 'id' => $rule->id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery

		$this->changed();

		return $done;
	}

	/**
	 * Delete, returning what was deleted so the screen can offer to put it back.
	 *
	 * @param list<int> $ids
	 * @return list<Rule>
	 */
	public function delete( array $ids ): array {
		global $wpdb;

		$rules = $this->many( $ids );

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

		$in = implode( ',', array_map( static fn( Rule $r ): int => $r->id, $rules ) );

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

		$this->changed();

		return $rules;
	}

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

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

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

		$in   = implode( ',', $ids );
		$rows = $wpdb->get_results( "SELECT * FROM {$this->table()} WHERE id IN ({$in})" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- absint above.

		return array_map( [ Rule::class, 'from_row' ], (array) $rows );
	}

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

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

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

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

		$count = (int) $wpdb->query( $wpdb->prepare( "UPDATE {$this->table()} SET enabled = %d, updated_at = %s WHERE id IN ({$in})", (int) $enabled, current_time( 'mysql', true ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$this->changed();

		return $count;
	}

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

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

		if ( [] === $ids || ! in_array( $code, Rule::CODES, true ) ) {
			return 0;
		}

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

		$count = (int) $wpdb->query( $wpdb->prepare( "UPDATE {$this->table()} SET status_code = %d, updated_at = %s WHERE id IN ({$in})", $code, current_time( 'mysql', true ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$this->changed();

		return $count;
	}

	/**
	 * @param list<int> $ids
	 */
	public function reset_hits( 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( "UPDATE {$this->table()} SET hits = 0, last_hit = NULL WHERE id IN ({$in})" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * @param list<int> $ids
	 */
	public function set_group( array $ids, string $group ): 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 group_name = %s WHERE id IN ({$in})", Rule::plain_text( $group, 100 ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/* ---------------------------------------------------------------------
	 * The two lookups every request makes
	 * ------------------------------------------------------------------ */

	/**
	 * Enabled exact rules for a path. Usually none; occasionally one; more only when the
	 * same address has variants by query string, case or condition.
	 *
	 * @return list<Rule>
	 */
	public function exact( Address $address ): array {
		global $wpdb;

		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE source_hash = %s AND match_type = 'exact' AND enabled = 1", $address->hash() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array_map( [ Rule::class, 'from_row' ], (array) $rows );
	}

	/**
	 * Enabled wildcard and regex rules, in the order they are tried.
	 *
	 * @return list<Rule>
	 */
	public function patterns(): array {
		$cached = get_option( self::PATTERN_CACHE, null );

		if ( ! is_array( $cached ) ) {
			global $wpdb;

			$cached = (array) $wpdb->get_results( "SELECT * FROM {$this->table()} WHERE match_type IN ('wildcard','regex') AND enabled = 1 ORDER BY priority ASC, id ASC", ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			// Hit counts change on every redirect and are not needed to match, so they are
			// left out — otherwise the cache would be stale the moment it was written.
			foreach ( $cached as &$row ) {
				unset( $row['hits'], $row['last_hit'] );
			}
			unset( $row );

			update_option( self::PATTERN_CACHE, $cached, count( $cached ) <= self::AUTOLOAD_LIMIT );
		}

		return array_map( [ Rule::class, 'from_row' ], $cached );
	}

	/**
	 * One UPDATE, no read. Counting is a nicety and must cost the visitor as little as
	 * possible on the way through.
	 */
	public function record_hit( int $id ): void {
		global $wpdb;

		$wpdb->query( $wpdb->prepare( "UPDATE {$this->table()} SET hits = hits + 1, last_hit = %s WHERE id = %d", current_time( 'mysql', true ), $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/* ---------------------------------------------------------------------
	 * The admin list
	 * ------------------------------------------------------------------ */

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

		$where = $this->filter_where( $args );
		$join  = '';

		switch ( $args['view'] ?? 'all' ) {
			case 'enabled':
				$where[] = 'r.enabled = 1';
				break;
			case 'disabled':
				$where[] = 'r.enabled = 0';
				break;
			case 'auto':
				$where[] = "r.origin IN ('auto','post','trash')";
				break;
			case 'unused':
				$where[] = 'r.hits = 0';
				break;
			case 'broken':
				$join    = $this->broken_join();
				$where[] = $this->broken_where();
				break;
		}

		if ( ! empty( $args['code'] ) ) {
			$where[] = $wpdb->prepare( 'r.status_code = %d', (int) $args['code'] );
		}

		$columns = [
			'created'  => 'r.id',
			'source'   => 'r.source',
			'hits'     => 'r.hits',
			'last_hit' => 'r.last_hit',
			'code'     => 'r.status_code',
		];

		$orderby  = $columns[ $args['orderby'] ?? 'created' ] ?? 'r.id';
		$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 -- every fragment above is prepared or a fixed string.
		$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table()} r {$join} WHERE {$sql_where}" );
		$rows  = $wpdb->get_results( "SELECT r.* FROM {$this->table()} r {$join} WHERE {$sql_where} ORDER BY {$orderby} {$order}, r.id DESC LIMIT {$per_page} OFFSET {$offset}" );
		// phpcs:enable

		return [
			'items' => array_map( [ Rule::class, 'from_row' ], (array) $rows ),
			'total' => $total,
		];
	}

	/**
	 * A rule pointing at a page that has since been deleted, unpublished or trashed still
	 * redirects — to the address the page last had — but somebody should look at it.
	 */
	private function broken_join(): string {
		global $wpdb;

		return "LEFT JOIN {$wpdb->posts} p ON ( r.target_type = 'post' AND p.ID = r.target_id )";
	}

	private function broken_where(): string {
		return "r.target_type = 'post' AND ( p.ID IS NULL OR p.post_status NOT IN ('publish','inherit') )";
	}

	/**
	 * The search box and the group filter, as SQL. Shared by the list and its counts, so
	 * that "Off 3" next to a search means three of the redirects that search found.
	 *
	 * @param array{search?: string, group?: string} $args
	 * @return list<string>
	 */
	private function filter_where( array $args ): array {
		global $wpdb;

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

		if ( '' !== $search ) {
			$like    = '%' . $wpdb->esc_like( $search ) . '%';
			$where[] = $wpdb->prepare( '(r.source LIKE %s OR r.target LIKE %s OR r.note LIKE %s OR r.group_name LIKE %s)', $like, $like, $like, $like );
		}

		if ( ! empty( $args['group'] ) ) {
			$where[] = $wpdb->prepare( 'r.group_name = %s', (string) $args['group'] );
		}

		return $where;
	}

	/**
	 * How many redirects are in each view — of everything, or of what a search and group
	 * filter currently show.
	 *
	 * @param array{search?: string, group?: string} $filter
	 * @return array{all: int, enabled: int, disabled: int, auto: int, unused: int, broken: int, hits: int, everything: int}
	 */
	public function counts( array $filter = [] ): array {
		global $wpdb;

		$where = implode( ' AND ', $this->filter_where( $filter ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- fragments prepared in filter_where().
		$row = (array) $wpdb->get_row(
			"SELECT COUNT(*) AS all_rules,
				SUM(r.enabled = 1) AS enabled,
				SUM(r.enabled = 0) AS disabled,
				SUM(r.origin IN ('auto','post','trash')) AS auto,
				SUM(r.hits = 0) AS unused,
				SUM(r.hits) AS hits
			FROM {$this->table()} r WHERE {$where}"
		);

		$broken = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$this->table()} r {$this->broken_join()} WHERE {$this->broken_where()} AND {$where}" );
		// phpcs:enable

		return [
			'all'      => (int) ( $row['all_rules'] ?? 0 ),
			'enabled'  => (int) ( $row['enabled'] ?? 0 ),
			'disabled' => (int) ( $row['disabled'] ?? 0 ),
			'auto'     => (int) ( $row['auto'] ?? 0 ),
			'unused'   => (int) ( $row['unused'] ?? 0 ),
			'broken'   => $broken,
			'hits'     => (int) ( $row['hits'] ?? 0 ),

			// Every redirect, whatever is filtered — what the tab's own count shows.
			'everything' => [] === array_filter( $filter ) ? (int) ( $row['all_rules'] ?? 0 ) : $this->count_all(),
		];
	}

	/** @return list<string> */
	public function groups(): array {
		global $wpdb;

		return array_map( 'strval', (array) $wpdb->get_col( "SELECT DISTINCT group_name FROM {$this->table()} WHERE group_name <> '' ORDER BY group_name" ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Every rule, in batches, for export and for the checks that compare a new rule with
	 * the rest.
	 *
	 * @return \Generator<Rule>
	 */
	public function each( bool $enabled_only = false ): \Generator {
		global $wpdb;

		$last  = 0;
		$where = $enabled_only ? 'AND enabled = 1' : '';

		do {
			$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE id > %d {$where} ORDER BY id ASC LIMIT 500", $last ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			foreach ( $rows as $row ) {
				$last = (int) $row->id;

				yield Rule::from_row( $row );
			}

			$full = 500 === count( $rows );
		} while ( $full );
	}

	/* ---------------------------------------------------------------------
	 * Rules tied to content
	 * ------------------------------------------------------------------ */

	/**
	 * @return list<Rule>
	 */
	public function for_post( int $post_id, ?string $origin = null ): array {
		global $wpdb;

		$sql = $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE post_id = %d", $post_id ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( null !== $origin ) {
			$sql .= $wpdb->prepare( ' AND origin = %s', $origin );
		}

		return array_map( [ Rule::class, 'from_row' ], (array) $wpdb->get_results( $sql ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Rules whose destination is this post or term — the "old addresses that lead here".
	 *
	 * @return list<Rule>
	 */
	public function pointing_at( string $type, int $id ): array {
		global $wpdb;

		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE target_type = %s AND target_id = %d ORDER BY hits DESC, id DESC LIMIT 100", $type, $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array_map( [ Rule::class, 'from_row' ], (array) $rows );
	}

	/**
	 * Exact rules for one source, enabled or not — what the duplicate check and the slug
	 * watcher need.
	 *
	 * @return list<Rule>
	 */
	public function by_source( string $source ): array {
		global $wpdb;

		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE source_hash = %s AND match_type = 'exact'", Address::parse( $source )->hash() ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return array_map( [ Rule::class, 'from_row' ], (array) $rows );
	}

	/**
	 * Rules whose destination is a typed address equal to this one. Used to offer to
	 * re-point them when this address becomes a redirect itself.
	 *
	 * @return list<Rule>
	 */
	public function targeting_url( string $path ): array {
		global $wpdb;

		$path  = Address::parse( $path )->path;
		$home  = untrailingslashit( home_url() );
		$forms = array_unique( [ $path, $path . '/', $home . $path, $home . $path . '/' ] );

		$placeholders = implode( ',', array_fill( 0, count( $forms ), '%s' ) );

		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$this->table()} WHERE target_type = 'url' AND target IN ({$placeholders}) LIMIT 100", ...$forms ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

		return array_map( [ Rule::class, 'from_row' ], (array) $rows );
	}

	/**
	 * Called after any write. The pattern cache is simply dropped and rebuilt on the next
	 * request that needs it, which is simpler and safer than patching it in place.
	 */
	public function changed(): void {
		delete_option( self::PATTERN_CACHE );

		/**
		 * Fires after redirects change — for a page cache that should be purged.
		 */
		do_action( 'codebold_redirect_rules_changed' );
	}

	public function count_all(): int {
		global $wpdb;

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