<?php
/**
 * Database access layer for stored redirect rules, including target URL
 * validation and caching.
 *
 * @package admin-optimizer
 */

namespace Yipresser\AdminOptimizer\Modules;

use Yipresser\AdminOptimizer\Helpers\Table_Rename_Helper;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Redirect_Manager_DB class
 */
class Redirect_Manager_DB {

	const DB_VERSION = '1.3';

	const OPTION_NAME = 'adminoptim_redirect_manager_db';


	const CACHE_GROUP = 'adminoptim_redirect_manager';

	const CACHE_VERSION_KEY = 'source_cache_version';

	const ADMIN_CACHE_TTL = 60;

	/**
	 * DB table name
	 *
	 * @var string
	 */
	private $tablename;

	/**
	 * Constructor
	 */
	public function __construct() {
		global $wpdb;
		$this->tablename = $wpdb->prefix . 'ao_redirects';
		$this->setup_db();
	}

	/**
	 * Install or upgrade database table if necessary
	 *
	 * @return void
	 */
	private function setup_db() {
		$db_option = get_option( self::OPTION_NAME, [] );

		if ( empty( $db_option ) || empty( $db_option['db_installed'] ) || self::DB_VERSION !== ( $db_option['db_version'] ?? '' ) ) {
			global $wpdb;
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			Table_Rename_Helper::maybe_rename( $wpdb->prefix . 'adminoptim_redirects', $this->tablename );
			$charset_collate = $wpdb->get_charset_collate();
			$sql             = "CREATE TABLE {$this->tablename} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				source_url varchar(512) NOT NULL,
				target_url varchar(512) NOT NULL,
				redirect_type smallint(5) unsigned NOT NULL DEFAULT 301,
				enabled tinyint(1) NOT NULL DEFAULT 1,
				preserve_query tinyint(1) NOT NULL DEFAULT 0,
				allow_external tinyint(1) unsigned NOT NULL DEFAULT 0,
				hit_count bigint(20) unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				PRIMARY KEY (id),
				UNIQUE KEY source_url (source_url(191))
			) {$charset_collate};";
			dbDelta( $sql );

			update_option(
				self::OPTION_NAME,
				[
					'db_installed' => 1,
					'db_version'   => self::DB_VERSION,
				],
				false
			);
		}
	}


	/**
	 * Retrieve all redirect records
	 *
	 * @return array
	 */
	public function get_all(): array {
		global $wpdb;
		$cache_key = 'all_redirects';
		$results   = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false === $results ) {
			$results = $wpdb->get_results( "SELECT * FROM `{$this->tablename}` ORDER BY id DESC" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			wp_cache_set( $cache_key, $results, self::CACHE_GROUP, self::ADMIN_CACHE_TTL );
		}
		return $results ? $results : [];
	}

	/**
	 * Count total redirect records.
	 *
	 * @return int
	 */
	public function count_all(): int {
		$cache_key = 'redirect_count';
		$count     = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false === $count ) {
			global $wpdb;
			$count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$this->tablename}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			wp_cache_set( $cache_key, $count, self::CACHE_GROUP, self::ADMIN_CACHE_TTL );
		}
		return (int) $count;
	}

	/**
	 * Retrieve a page of redirect records.
	 *
	 * @param int $per_page Rows per page.
	 * @param int $offset   Row offset.
	 *
	 * @return array
	 */
	public function get_paginated( int $per_page, int $offset ): array {
		global $wpdb;
		$per_page = max( 1, $per_page );
		$offset   = max( 0, $offset );
		$results  = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM `{$this->tablename}` ORDER BY id DESC LIMIT %d OFFSET %d", $per_page, $offset ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $results ? $results : [];
	}

	/**
	 * Retrieve a single record by ID
	 *
	 * @param int $id Record ID.
	 *
	 * @return object|null
	 */
	public function get_by_id( int $id ): ?object {
		global $wpdb;
		$cache_key = 'redirect_' . $id;
		$result    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false === $result ) {
			$result = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$this->tablename}` WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			wp_cache_set( $cache_key, $result, self::CACHE_GROUP, self::ADMIN_CACHE_TTL );
		}
		return $result ? $result : null;
	}

	/**
	 * Check whether a source URL is already used by another record
	 *
	 * @param string $source_url  Normalized source path.
	 * @param int    $exclude_id  Exclude this record ID from the check (for edits).
	 *
	 * @return bool
	 */
	public function source_exists( string $source_url, int $exclude_id = 0 ): bool {
		global $wpdb;
		if ( $exclude_id > 0 ) {
			$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$this->tablename}` WHERE source_url = %s AND id != %d", $source_url, $exclude_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		} else {
			$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$this->tablename}` WHERE source_url = %s", $source_url ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
		return (int) $count > 0;
	}

	/**
	 * Find an enabled redirect matching the given source path
	 *
	 * @param string $source_url Normalized source path.
	 *
	 * @return object|null
	 */
	public function find_by_source( string $source_url ): ?object {
		global $wpdb;
		$cache_key = $this->get_source_cache_key( $source_url );
		$result    = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false === $result ) {
			$result = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$this->tablename}` WHERE source_url = %s AND enabled = 1", $source_url ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			// Cache misses too. Most frontend requests will not have a redirect rule.
			wp_cache_set( $cache_key, $result ? $result : 0, self::CACHE_GROUP );
		}

		return $result ? $result : null;
	}

	/**
	 * Insert a new redirect record
	 *
	 * @param array $data Redirect data: source_url, target_url, redirect_type, preserve_query, allow_external.
	 *
	 * @return int Inserted row ID.
	 */
	public function insert( array $data ): int {
		global $wpdb;
		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$this->tablename,
			[
				'source_url'     => $data['source_url'],
				'target_url'     => $data['target_url'],
				'redirect_type'  => (int) $data['redirect_type'],
				'enabled'        => 1,
				'preserve_query' => ! empty( $data['preserve_query'] ) ? 1 : 0,
				'allow_external' => ! empty( $data['allow_external'] ) ? 1 : 0,
				'hit_count'      => 0,
				'created_at'     => current_time( 'mysql', true ),
			]
		);
		$this->clear_cache();
		return (int) $wpdb->insert_id;
	}

	/**
	 * Update an existing redirect record
	 *
	 * @param int   $id   Record ID.
	 * @param array $data Redirect data: source_url, target_url, redirect_type, preserve_query, allow_external.
	 *
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		global $wpdb;
		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->tablename,
			[
				'source_url'     => $data['source_url'],
				'target_url'     => $data['target_url'],
				'redirect_type'  => (int) $data['redirect_type'],
				'preserve_query' => ! empty( $data['preserve_query'] ) ? 1 : 0,
				'allow_external' => ! empty( $data['allow_external'] ) ? 1 : 0,
			],
			[ 'id' => $id ]
		);
		$this->clear_cache( $id );
		return false !== $result;
	}

	/**
	 * Toggle enabled state of a redirect record
	 *
	 * @param int $id      Record ID.
	 * @param int $enabled 1 to enable, 0 to disable.
	 *
	 * @return bool
	 */
	public function toggle_enabled( int $id, int $enabled ): bool {
		global $wpdb;
		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->tablename,
			[ 'enabled' => $enabled ],
			[ 'id' => $id ]
		);
		$this->clear_cache( $id );
		return false !== $result;
	}

	/**
	 * Increment hit counter for a redirect record
	 *
	 * @param int $id Record ID.
	 *
	 * @return void
	 */
	public function increment_hits( int $id ): void {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( "UPDATE `{$this->tablename}` SET hit_count = hit_count + 1 WHERE id = %d", $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Delete a single redirect record
	 *
	 * @param int $id Record ID.
	 *
	 * @return bool
	 */
	public function delete( int $id ): bool {
		global $wpdb;
		$result = $wpdb->delete( $this->tablename, [ 'id' => $id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->clear_cache( $id );
		return false !== $result;
	}

	/**
	 * Delete multiple redirect records
	 *
	 * @param array $ids Array of record IDs.
	 *
	 * @return int Number of rows deleted.
	 */
	public function delete_multiple( array $ids ): int {
		global $wpdb;
		$ids_int = array_map( 'intval', $ids );
		if ( empty( $ids_int ) ) {
			return 0;
		}
		$placeholders = implode( ',', array_fill( 0, count( $ids_int ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $placeholders is a dynamically built string of %d tokens, one per $ids_int element, so the count always matches the spread args.
		$result       = $wpdb->query( $wpdb->prepare( "DELETE FROM `{$this->tablename}` WHERE id IN ($placeholders)", ...$ids_int ) );
		$this->clear_cache();
		return (int) $result;
	}


	/**
	 * Invalidate cached redirect data
	 *
	 * @param int $id Optional specific record ID to also clear.
	 *
	 * @return void
	 */
	private function clear_cache( int $id = 0 ): void {
		wp_cache_delete( 'all_redirects', self::CACHE_GROUP );
		wp_cache_delete( 'redirect_count', self::CACHE_GROUP );
		if ( $id > 0 ) {
			wp_cache_delete( 'redirect_' . $id, self::CACHE_GROUP );
		}
		$this->bump_source_cache_version();
	}

	/**
	 * Normalize a URL or path to the canonical form used for storage and lookup.
	 *
	 * Strips query string, enforces a leading slash, removes trailing slash except for root.
	 *
	 * @param string $url Raw URL or path (e.g. REQUEST_URI or get_permalink() output).
	 *
	 * @return string
	 */
	public static function normalize_source( string $url ): string {
		$path = (string) wp_parse_url( $url, PHP_URL_PATH );
		$path = '/' . ltrim( $path, '/' );
		if ( '/' !== $path ) {
			$path = rtrim( $path, '/' );
		}
		return $path;
	}

	/**
	 * Validate and normalize a redirect target URL or relative path.
	 *
	 * @param string $target_url Raw target URL.
	 *
	 * @return string|\WP_Error
	 */
	public static function validate_target_url( string $target_url ) {
		$target_url = trim( $target_url );

		if ( '' === $target_url ) {
			return new \WP_Error( 'empty_target', __( 'Target URL is required.', 'admin-optimizer' ) );
		}

		if ( preg_match( '/[\x00-\x1F\x7F]/', $target_url ) ) {
			return new \WP_Error( 'invalid_target', __( 'Target URL cannot contain control characters or line breaks.', 'admin-optimizer' ) );
		}

		if ( str_starts_with( $target_url, '//' ) ) {
			return new \WP_Error( 'invalid_target', __( 'Protocol-relative target URLs are not allowed.', 'admin-optimizer' ) );
		}

		$scheme = (string) wp_parse_url( $target_url, PHP_URL_SCHEME );
		if ( '' !== $scheme ) {
			$scheme = strtolower( $scheme );
			if ( ! in_array( $scheme, [ 'http', 'https' ], true ) ) {
				return new \WP_Error( 'invalid_target_scheme', __( 'Target URL must use http or https.', 'admin-optimizer' ) );
			}

			$parts = wp_parse_url( $target_url );
			if ( false === $parts || empty( $parts['host'] ) || ! is_array( $parts ) ) {
				return new \WP_Error( 'invalid_target', __( 'Target URL is malformed.', 'admin-optimizer' ) );
			}

			if ( isset( $parts['user'] ) || isset( $parts['pass'] ) ) {
				return new \WP_Error( 'invalid_target_credentials', __( 'Target URL cannot include a username or password.', 'admin-optimizer' ) );
			}

			if ( ! self::is_valid_host( (string) $parts['host'] ) ) {
				return new \WP_Error( 'invalid_target_host', __( 'Target URL host is malformed.', 'admin-optimizer' ) );
			}

			return function_exists( 'esc_url_raw' ) ? esc_url_raw( $target_url, [ 'http', 'https' ] ) : $target_url;
		}

		if ( preg_match( '/^[A-Za-z][A-Za-z0-9+.-]*:/', $target_url ) ) {
			return new \WP_Error( 'invalid_target_scheme', __( 'Target URL must use http or https.', 'admin-optimizer' ) );
		}

		return $target_url;
	}

	/**
	 * Check whether a valid redirect target points outside this WordPress site.
	 *
	 * @param string $target_url Target URL or relative path.
	 *
	 * @return bool
	 */
	public static function is_external_target( string $target_url ): bool {
		$validated = self::validate_target_url( $target_url );
		if ( is_wp_error( $validated ) ) {
			return false;
		}

		$target_host = self::get_normalized_target_host( $validated );
		if ( '' === $target_host ) {
			return false;
		}

		$home_parts = wp_parse_url( home_url() );
		if ( false === $home_parts || ! is_array( $home_parts ) || empty( $home_parts['host'] ) ) {
			return true;
		}

		$home_host = self::normalize_host_for_compare(
			(string) $home_parts['host'],
			(string) ( $home_parts['scheme'] ?? '' ),
			isset( $home_parts['port'] ) ? (int) $home_parts['port'] : null
		);

		return $target_host !== $home_host;
	}

	/**
	 * Resolve the allow_external value that may be stored for a submitted target.
	 *
	 * @param string      $target_url               Validated submitted target.
	 * @param bool        $allow_external_submitted Whether the checkbox was submitted checked.
	 * @param object|null $existing                 Existing redirect row during edits.
	 *
	 * @return int|\WP_Error
	 */
	public static function resolve_allow_external_for_save( string $target_url, bool $allow_external_submitted, ?object $existing = null ) {
		if ( ! self::is_external_target( $target_url ) ) {
			return 0;
		}

		$submitted_host      = self::get_normalized_target_host( $target_url );
		$existing_host       = $existing ? self::get_normalized_target_host( (string) $existing->target_url ) : '';
		$same_external_host  = $existing && '' !== $submitted_host && $submitted_host === $existing_host;

		if ( ! $allow_external_submitted && ! ( $same_external_host && ! empty( $existing->allow_external ) ) ) {
			return new \WP_Error(
				'external_not_allowed',
				__( 'This target URL points to an external website. Enable "Allow external redirect" to continue.', 'admin-optimizer' )
			);
		}

		return 1;
	}

	/**
	 * Get the normalized host and non-default port from an absolute target URL.
	 *
	 * @param string $target_url Target URL or relative path.
	 *
	 * @return string
	 */
	public static function get_normalized_target_host( string $target_url ): string {
		$parts = wp_parse_url( $target_url );
		if ( false === $parts || ! is_array( $parts ) || empty( $parts['host'] ) ) {
			return '';
		}

		return self::normalize_host_for_compare(
			(string) $parts['host'],
			(string) ( $parts['scheme'] ?? '' ),
			isset( $parts['port'] ) ? (int) $parts['port'] : null
		);
	}

	/**
	 * Normalize host and default ports for exact comparisons.
	 *
	 * @param string   $host   Host name.
	 * @param string   $scheme URL scheme.
	 * @param int|null $port   URL port.
	 *
	 * @return string
	 */
	private static function normalize_host_for_compare( string $host, string $scheme = '', ?int $port = null ): string {
		$host   = strtolower( rtrim( trim( $host ), '.' ) );
		$scheme = strtolower( $scheme );

		if ( ( 'http' === $scheme && 80 === $port ) || ( 'https' === $scheme && 443 === $port ) ) {
			$port = null;
		}

		return null !== $port ? $host . ':' . $port : $host;
	}

	/**
	 * Validate a URL host without substring matching.
	 *
	 * @param string $host Host name from wp_parse_url().
	 *
	 * @return bool
	 */
	private static function is_valid_host( string $host ): bool {
		$host = trim( $host );
		if ( '' === $host || preg_match( '/[\s\x00-\x1F\x7F@]/', $host ) ) {
			return false;
		}

		$host = rtrim( $host, '.' );
		if ( filter_var( trim( $host, '[]' ), FILTER_VALIDATE_IP ) ) {
			return true;
		}

		if ( strlen( $host ) > 253 || ! preg_match( '/^[A-Za-z0-9.-]+$/', $host ) ) {
			return false;
		}

		foreach ( explode( '.', $host ) as $label ) {
			if ( '' === $label || strlen( $label ) > 63 || '-' === $label[0] || '-' === substr( $label, -1 ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Build a cache key for source path lookup.
	 *
	 * @param string $source_url Normalized source path.
	 *
	 * @return string
	 */
	private function get_source_cache_key( string $source_url ): string {
		return 'source_' . $this->get_source_cache_version() . '_' . md5( $source_url );
	}

	/**
	 * Retrieve the source lookup cache version.
	 *
	 * @return string
	 */
	private function get_source_cache_version(): string {
		$version = wp_cache_get( self::CACHE_VERSION_KEY, self::CACHE_GROUP );

		if ( false === $version ) {
			$version = '1';
			wp_cache_set( self::CACHE_VERSION_KEY, $version, self::CACHE_GROUP );
		}

		return (string) $version;
	}

	/**
	 * Invalidate all cached source lookups.
	 *
	 * @return void
	 */
	private function bump_source_cache_version(): void {
		wp_cache_set( self::CACHE_VERSION_KEY, (string) microtime( true ), self::CACHE_GROUP );
	}
}