<?php
/**
 * Redirect Database Operations
 *
 * Handles the custom database table for redirects and all CRUD operations.
 *
 * @package SEOvaultSEO\RedirectManager
 */

namespace SEOvaultSEO\RedirectManager;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Database layer for the redirect manager.
 */
class RedirectDb {

	/**
	 * Current schema version. Bump when the CREATE TABLE SQL changes so
	 * {@see \SEOvaultSEO\Core\Upgrade::maybe_upgrade()} can re-run dbDelta().
	 */
	const DB_VERSION = '1.1';

	/**
	 * Option name storing the installed schema version.
	 */
	const DB_VERSION_OPTION = 'seovaultseo_redirects_db_version';

	/**
	 * Get the full table name with prefix.
	 *
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . 'seovaultseo_redirects';
	}

	/**
	 * Create or upgrade the custom database table.
	 * Called on plugin activation and schema upgrades.
	 *
	 * @return void
	 */
	public static function create_table() {
		global $wpdb;

		$table   = self::table_name();
		$charset = $wpdb->get_charset_collate();

		// DATETIME columns have no zero-date default (MySQL 5.7+/8.0 strict mode).
		// Inserts/updates always supply current_time( 'mysql' ).
		$sql = "CREATE TABLE {$table} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			origin VARCHAR(255) NOT NULL DEFAULT '',
			target VARCHAR(255) NOT NULL DEFAULT '',
			type SMALLINT(5) UNSIGNED NOT NULL DEFAULT 301,
			format VARCHAR(10) NOT NULL DEFAULT 'plain',
			created DATETIME NOT NULL,
			updated DATETIME NOT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY origin_format (origin(191), format(10)),
			KEY format (format(10))
		) {$charset};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION, false );
	}

	/**
	 * Get a single redirect by its database ID.
	 *
	 * @param int $id Redirect ID.
	 * @return Redirect|null
	 */
	public static function get( $id ) {
		global $wpdb;

		$table = self::table_name();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d LIMIT 1", (int) $id ),
			ARRAY_A
		);
		// phpcs:enable

		if ( ! $row ) {
			return null;
		}

		return Redirect::from_array( $row );
	}

	/**
	 * Get a redirect by origin and format.
	 *
	 * @param string $origin Origin URL.
	 * @param string $format Redirect format (plain|regex).
	 * @return Redirect|null
	 */
	public static function get_by_origin( $origin, $format = Redirect::FORMAT_PLAIN ) {
		global $wpdb;

		$table = self::table_name();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE origin = %s AND format = %s LIMIT 1", $origin, $format ),
			ARRAY_A
		);
		// phpcs:enable

		if ( ! $row ) {
			return null;
		}

		return Redirect::from_array( $row );
	}

	/**
	 * Get all redirects, optionally filtered by format.
	 *
	 * @param string $format Optional format filter (plain|regex).
	 * @param int    $limit  Max results. 0 = all.
	 * @param int    $offset Offset.
	 * @return Redirect[]
	 */
	public static function get_all( $format = '', $limit = 0, $offset = 0 ) {
		global $wpdb;

		$table  = self::table_name();
		$where  = '';
		$params = array();

		if ( ! empty( $format ) && Redirect::is_valid_format( $format ) ) {
			$where    = ' WHERE format = %s';
			$params[] = $format;
		}

		$limit_clause = '';
		if ( $limit > 0 ) {
			$limit_clause = ' LIMIT %d OFFSET %d';
			$params[]     = (int) $limit;
			$params[]     = (int) $offset;
		}

		$sql = "SELECT * FROM {$table}{$where} ORDER BY id ASC{$limit_clause}";

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin-owned table; WHERE/LIMIT use prepare() when params exist.
		if ( ! empty( $params ) ) {
			$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );
		} else {
			$rows = $wpdb->get_results( $sql, ARRAY_A );
		}
		// phpcs:enable

		if ( ! $rows ) {
			return array();
		}

		$redirects = array();
		foreach ( $rows as $row ) {
			$redirects[] = Redirect::from_array( $row );
		}

		return $redirects;
	}

	/**
	 * Insert a new redirect into the database.
	 *
	 * @param Redirect $redirect Redirect to insert.
	 * @return int|false Inserted ID on success, false on failure.
	 */
	public static function insert( Redirect $redirect ) {
		global $wpdb;

		$table = self::table_name();
		$now   = current_time( 'mysql' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert(
			$table,
			array(
				'origin'  => $redirect->get_origin(),
				'target'  => $redirect->get_target(),
				'type'    => $redirect->get_type(),
				'format'  => $redirect->get_format(),
				'created' => $now,
				'updated' => $now,
			),
			array( '%s', '%s', '%d', '%s', '%s', '%s' )
		);
		// phpcs:enable

		if ( false === $result ) {
			return false;
		}

		$id = (int) $wpdb->insert_id;
		$redirect->set_id( $id );

		return $id;
	}

	/**
	 * Update an existing redirect.
	 *
	 * @param int      $id       Redirect ID.
	 * @param Redirect $redirect New redirect data.
	 * @return bool True on success, false on failure.
	 */
	public static function update( $id, Redirect $redirect ) {
		global $wpdb;

		$table = self::table_name();
		$now   = current_time( 'mysql' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			$table,
			array(
				'origin'  => $redirect->get_origin(),
				'target'  => $redirect->get_target(),
				'type'    => $redirect->get_type(),
				'format'  => $redirect->get_format(),
				'updated' => $now,
			),
			array( 'id' => (int) $id ),
			array( '%s', '%s', '%d', '%s', '%s' ),
			array( '%d' )
		);
		// phpcs:enable

		return false !== $result;
	}

	/**
	 * Delete a redirect by ID.
	 *
	 * @param int $id Redirect ID.
	 * @return bool True on success, false on failure.
	 */
	public static function delete( $id ) {
		global $wpdb;

		$table = self::table_name();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->delete( $table, array( 'id' => (int) $id ), array( '%d' ) );
		// phpcs:enable

		return false !== $result;
	}

	/**
	 * Delete multiple redirects by IDs.
	 *
	 * @param array $ids Array of redirect IDs.
	 * @return int Number of rows deleted.
	 */
	public static function delete_many( array $ids ) {
		global $wpdb;

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

		if ( empty( $ids ) ) {
			return 0;
		}

		$table     = self::table_name();
		$placehold = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Plugin-owned table; IN list uses prepare().
		return (int) $wpdb->query(
			$wpdb->prepare( "DELETE FROM {$table} WHERE id IN ({$placehold})", $ids )
		);
		// phpcs:enable
	}

	/**
	 * Get the total count of redirects.
	 *
	 * @param string $format Optional format filter.
	 * @return int
	 */
	public static function count( $format = '' ) {
		global $wpdb;

		$table = self::table_name();
		$where = '';

		if ( ! empty( $format ) && Redirect::is_valid_format( $format ) ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
			$where = $wpdb->prepare( ' WHERE format = %s', $format );
			// phpcs:enable
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $where uses prepare(); table name is plugin-owned.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}{$where}" );
		// phpcs:enable
	}

	/**
	 * Check if a redirect with the given origin and format already exists.
	 *
	 * @param string   $origin Origin URL.
	 * @param string   $format Redirect format.
	 * @param int|null $exclude_id Optional ID to exclude (for updates).
	 * @return bool
	 */
	public static function exists( $origin, $format, $exclude_id = null ) {
		global $wpdb;

		$table = self::table_name();

		if ( null !== $exclude_id ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$table} WHERE origin = %s AND format = %s AND id != %d",
					$origin,
					$format,
					(int) $exclude_id
				)
			);
			// phpcs:enable
		} else {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin-owned table name.
			$count = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$table} WHERE origin = %s AND format = %s",
					$origin,
					$format
				)
			);
			// phpcs:enable
		}

		return $count > 0;
	}

	/**
	 * Find a matching redirect for a given request URI.
	 *
	 * Checks plain redirects first (exact match), then regex redirects.
	 *
	 * @param string $uri Request URI (relative path, no query string).
	 * @return Redirect|null
	 */
	public static function find_match( $uri ) {
		$uri = ltrim( $uri, '/' );

		// Try plain redirects first (exact match).
		$plain = self::get_by_origin( $uri, Redirect::FORMAT_PLAIN );
		if ( $plain ) {
			return $plain;
		}

		// Try regex redirects.
		$regex_redirects = self::get_all( Redirect::FORMAT_REGEX );
		foreach ( $regex_redirects as $redirect ) {
			if ( $redirect->origin_matches( $uri ) ) {
				return $redirect;
			}
		}

		return null;
	}
}
