<?php
/**
 * MySQL-backed repository for country routing records.
 *
 * @since   1.0.0
 * @package DonationRouter
 */

declare( strict_types=1 );
namespace DonationRouter;

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

/**
 * Persists and retrieves Country_Routing value objects using wpdb.
 *
 * The save() method validates that both the referenced form post and the
 * referenced account exist and are active before writing to the database.
 *
 * @since 1.0.0
 */
class Country_Routing_Repository implements Country_Routing_Repository_Interface {

	/**
	 * Database connection.
	 *
	 * @since 1.0.0
	 *
	 * @var \wpdb
	 */
	private \wpdb $wpdb;

	/**
	 * Cache key manager.
	 *
	 * @since 1.0.0
	 *
	 * @var Cache_Manager
	 */
	private Cache_Manager $cache;

	/**
	 * Initialise the repository with a database connection.
	 *
	 * @since 1.0.0
	 *
	 * @param \wpdb $wpdb  WordPress database abstraction object.
	 */
	public function __construct( \wpdb $wpdb ) {
		$this->wpdb  = $wpdb;
		$this->cache = new Cache_Manager();
	}

	/**
	 * Return the fully-qualified routing table name.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function table(): string {
		return $this->wpdb->prefix . 'ador_country_routing';
	}

	/**
	 * Return the fully-qualified accounts table name.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function accounts_table(): string {
		return $this->wpdb->prefix . 'ador_accounts';
	}

	/**
	 * Retrieve a single routing rule by its primary key.
	 *
	 * @since 1.0.0
	 *
	 * @param  int $routing_id  Database row ID.
	 *
	 * @return Country_Routing
	 *
	 * @throws Country_Routing_Not_Found_Exception  When no rule exists with the given ID.
	 */
	public function find_by_id( int $routing_id ): Country_Routing {
		$cache_key = 'routing_id_' . $routing_id;
		$cached    = wp_cache_get( $cache_key, Cache_Manager::GROUP );

		if ( $cached instanceof Country_Routing ) {
			return $cached;
		}

		$table = $this->table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// @phpstan-ignore-next-line
		$sql = $this->wpdb->prepare( "SELECT * FROM `{$table}` WHERE id = %d LIMIT 1", $routing_id );
		$row = $this->wpdb->get_row( $sql, ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! is_array( $row ) ) {
			throw new Country_Routing_Not_Found_Exception(
				'No routing rule found with the given ID.'
			);
		}

		$routing = Country_Routing::from_database_row( $row );

		wp_cache_set( $cache_key, $routing, Cache_Manager::GROUP, Cache_Manager::TTL_RECORD );

		return $routing;
	}

	/**
	 * Retrieve a single routing rule by its country code.
	 *
	 * @since 1.0.0
	 *
	 * @param  string $country_code  ISO 3166-1 alpha-2 country code.
	 *
	 * @return Country_Routing
	 *
	 * @throws Country_Routing_Not_Found_Exception  When no active routing rule matches the country code.
	 */
	public function find_by_country_code( string $country_code ): Country_Routing {
		$cache_key = $this->cache->get_country_routing_by_country_key( $country_code );
		$cached    = wp_cache_get( $cache_key, Cache_Manager::GROUP );

		if ( $cached instanceof Country_Routing ) {
			return $cached;
		}

		$table = $this->table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// @phpstan-ignore-next-line
		$sql = $this->wpdb->prepare( "SELECT * FROM `{$table}` WHERE country_code = %s AND is_active = 1 LIMIT 1", strtoupper( $country_code ) );
		$row = $this->wpdb->get_row( $sql, ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! is_array( $row ) ) {
			throw new Country_Routing_Not_Found_Exception(
				'No active routing rule found for the given country code.'
			);
		}

		$routing = Country_Routing::from_database_row( $row );

		wp_cache_set( $cache_key, $routing, Cache_Manager::GROUP, Cache_Manager::TTL_RECORD );

		return $routing;
	}

	/**
	 * Retrieve a single routing rule by its associated donation form ID.
	 *
	 * @since 1.0.0
	 *
	 * @param  int $form_id  GiveWP donation form post ID.
	 *
	 * @return Country_Routing
	 *
	 * @throws Country_Routing_Not_Found_Exception  When no active routing rule matches the form ID.
	 */
	public function find_by_form_id( int $form_id ): Country_Routing {
		$cache_key = $this->cache->get_country_routing_by_form_key( $form_id );
		$cached    = wp_cache_get( $cache_key, Cache_Manager::GROUP );

		if ( $cached instanceof Country_Routing ) {
			return $cached;
		}

		$table = $this->table();

		// Deterministic ordering: a form should map to a single account, but if
		// more than one active rule references the same form the lowest sort_order
		// (then lowest id) wins consistently instead of an arbitrary MySQL order.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// @phpstan-ignore-next-line
		$sql = $this->wpdb->prepare( "SELECT * FROM `{$table}` WHERE form_id = %d AND is_active = 1 ORDER BY sort_order ASC, id ASC LIMIT 1", $form_id );
		$row = $this->wpdb->get_row( $sql, ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! is_array( $row ) ) {
			throw new Country_Routing_Not_Found_Exception(
				'No active routing rule found for the given form ID.'
			);
		}

		$routing = Country_Routing::from_database_row( $row );

		wp_cache_set( $cache_key, $routing, Cache_Manager::GROUP, Cache_Manager::TTL_RECORD );

		return $routing;
	}

	/**
	 * Return all active routing rules ordered by sort_order and country_code ascending.
	 *
	 * @since 1.0.0
	 *
	 * @return Country_Routing[]
	 */
	public function find_all_active(): array {
		$cache_key = $this->cache->get_all_active_routings_key();
		$cached    = wp_cache_get( $cache_key, Cache_Manager::GROUP );

		if ( is_array( $cached ) ) {
			return $cached;
		}

		$table = $this->table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $this->wpdb->get_results( "SELECT * FROM `{$table}` WHERE is_active = 1 ORDER BY sort_order ASC, country_code ASC", ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

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

		$routings = array();
		foreach ( $rows as $row ) {
			$routings[] = Country_Routing::from_database_row( $row );
		}

		wp_cache_set( $cache_key, $routings, Cache_Manager::GROUP, Cache_Manager::TTL_LIST );

		return $routings;
	}

	/**
	 * Return all routing rules (active and inactive) ordered by sort_order and country_code ascending.
	 *
	 * @since 1.0.0
	 *
	 * @return Country_Routing[]
	 */
	public function find_all(): array {
		$cache_key = $this->cache->get_all_routings_key();
		$cached    = wp_cache_get( $cache_key, Cache_Manager::GROUP );

		if ( is_array( $cached ) ) {
			return $cached;
		}

		$table = $this->table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $this->wpdb->get_results( "SELECT * FROM `{$table}` ORDER BY sort_order ASC, country_code ASC", ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

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

		$routings = array();
		foreach ( $rows as $row ) {
			$routings[] = Country_Routing::from_database_row( $row );
		}

		wp_cache_set( $cache_key, $routings, Cache_Manager::GROUP, Cache_Manager::TTL_LIST );

		return $routings;
	}

	/**
	 * Return the active routing rule for a given country code, excluding a specific record.
	 *
	 * Used to enforce the one-active-routing-per-country uniqueness constraint.
	 *
	 * @since 1.0.0
	 *
	 * @param  string   $country_code  ISO 3166-1 alpha-2 country code.
	 * @param  int|null $exclude_id    Routing ID to exclude (the record being updated), or null.
	 *
	 * @return Country_Routing|null  Existing conflicting rule, or null if no conflict.
	 */
	public function find_active_by_country_excluding( string $country_code, ?int $exclude_id ): ?Country_Routing {
		$table = $this->table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		if ( null !== $exclude_id ) {
			// @phpstan-ignore-next-line
			$sql = $this->wpdb->prepare( "SELECT * FROM `{$table}` WHERE country_code = %s AND is_active = 1 AND id != %d LIMIT 1", strtoupper( $country_code ), $exclude_id ); // phpcs:ignore Generic.Files.LineLength.TooLong
		} else {
			// @phpstan-ignore-next-line
			$sql = $this->wpdb->prepare( "SELECT * FROM `{$table}` WHERE country_code = %s AND is_active = 1 LIMIT 1", strtoupper( $country_code ) );
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$row = $this->wpdb->get_row( $sql, ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! is_array( $row ) ) {
			return null;
		}

		return Country_Routing::from_database_row( $row );
	}

	// The @throws below documents \InvalidArgumentException propagated from the
	// assert helpers; the Squiz throw-tag sniff only counts direct `throw`
	// statements, so it is disabled for this doc block.
	// phpcs:disable Squiz.Commenting.FunctionCommentThrowTag.WrongNumber
	/**
	 * Persist a new or existing routing rule and return its row ID.
	 *
	 * Validates that the referenced form_id is a registered give_forms post and
	 * that the referenced account_id points to an active account before writing.
	 *
	 * @since 1.0.0
	 *
	 * @param  Country_Routing $routing  The routing rule to save.
	 *
	 * @return int  The inserted or updated row ID.
	 *
	 * @throws \InvalidArgumentException  When the form or account reference is invalid (propagated from the assert helpers).
	 * @throws Persistence_Exception      When the database write fails.
	 */
	public function save( Country_Routing $routing ): int {
		// phpcs:enable Squiz.Commenting.FunctionCommentThrowTag.WrongNumber
		$this->assert_form_exists( $routing->form_id );
		$this->assert_account_active( $routing->account_id );

		$data = array(
			'country_code'        => $routing->country_code,
			'form_id'             => $routing->form_id,
			'account_id'          => $routing->account_id,
			'display_label'       => $routing->display_label,
			'display_description' => $routing->display_description,
			'sort_order'          => $routing->sort_order,
			'is_active'           => $routing->is_active ? 1 : 0,
		);

		$formats = array( '%s', '%d', '%d', '%s', '%s', '%d', '%d' );

		/*
		 * One row per country (schema UNIQUE KEY country_code). deactivate() keeps
		 * the row and only flips is_active, so "adding" a rule for a country that
		 * already has a — possibly deactivated — row must reuse that row via UPDATE.
		 * A blind INSERT would hit the unique constraint and, before this guard,
		 * fail silently (insert_id 0) while the handler reported success. The
		 * handler has already confirmed no *active* rule conflicts.
		 */
		$target_id = $routing->id ?? $this->find_row_id_by_country_code( $routing->country_code );

		if ( null === $target_id ) {
			$result = $this->wpdb->insert( $this->table(), $data, $formats ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			if ( false === $result ) {
				throw new Persistence_Exception(
					esc_html__( 'Could not save the routing rule. Please try again.', 'alamin-donation-router' )
				);
			}
			$new_id = (int) $this->wpdb->insert_id;
		} else {
			$result = $this->wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				$this->table(),
				$data,
				array( 'id' => $target_id ),
				$formats,
				array( '%d' )
			);
			if ( false === $result ) {
				throw new Persistence_Exception(
					esc_html__( 'Could not save the routing rule. Please try again.', 'alamin-donation-router' )
				);
			}
			$new_id = $target_id;
		}

		$this->cache->invalidate_country_routing( $routing );

		return $new_id;
	}

	/**
	 * Return the row ID for a country code regardless of active state, or null.
	 *
	 * Unlike find_by_country_code() this ignores is_active, so save() can reuse a
	 * deactivated row rather than violate the UNIQUE(country_code) constraint.
	 *
	 * @since 1.0.0
	 *
	 * @param string $country_code  ISO 3166-1 alpha-2 code.
	 *
	 * @return int|null  Row ID, or null when no row exists for the country.
	 */
	private function find_row_id_by_country_code( string $country_code ): ?int {
		$table = $this->table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// @phpstan-ignore-next-line
		$sql = $this->wpdb->prepare( "SELECT id FROM `{$table}` WHERE country_code = %s LIMIT 1", strtoupper( $country_code ) );
		$id  = $this->wpdb->get_var( $sql );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return null !== $id ? (int) $id : null;
	}

	/**
	 * Mark a routing rule inactive by its primary key.
	 *
	 * @since 1.0.0
	 *
	 * @param  int $routing_id  Database row ID.
	 *
	 * @return bool  True if at least one row was updated.
	 */
	public function deactivate( int $routing_id ): bool {
		$rows = $this->wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$this->table(),
			array( 'is_active' => 0 ),
			array( 'id' => $routing_id ),
			array( '%d' ),
			array( '%d' )
		);

		if ( false !== $rows && $rows > 0 ) {
			// Full invalidation, not just the active-list key: the single-record
			// hot-path caches (routing_id_/_cc_/_fid_) that the credential router and
			// selector read would otherwise keep serving the just-deactivated rule
			// for up to TTL_RECORD. find_by_id() only supplies the immutable
			// country/form/id used to build those keys, so a stale cache read is fine.
			try {
				$this->cache->invalidate_country_routing( $this->find_by_id( $routing_id ) );
			} catch ( Country_Routing_Not_Found_Exception $e ) {
				$this->cache->invalidate_all();
			}
		}

		return false !== $rows && $rows > 0;
	}

	/**
	 * Assert that the given post ID exists and is a give_forms post type.
	 *
	 * @since 1.0.0
	 *
	 * @param  int $form_id  GiveWP donation form post ID.
	 *
	 * @return void
	 *
	 * @throws \InvalidArgumentException  When the post does not exist or is not a give_forms post.
	 */
	private function assert_form_exists( int $form_id ): void {
		$post_type = get_post_type( $form_id );

		if ( 'give_forms' !== $post_type ) {
			throw new \InvalidArgumentException(
				sprintf( 'form_id %d is not a valid give_forms post.', $form_id ) // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
			);
		}
	}

	/**
	 * Assert that the given account ID references an active account record.
	 *
	 * @since 1.0.0
	 *
	 * @param  int $account_id  Foreign key to ador_accounts.
	 *
	 * @return void
	 *
	 * @throws \InvalidArgumentException  When the account does not exist or is inactive.
	 */
	private function assert_account_active( int $account_id ): void {
		$accounts_table = $this->accounts_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// @phpstan-ignore-next-line
		$sql       = $this->wpdb->prepare( "SELECT is_active FROM `{$accounts_table}` WHERE id = %d LIMIT 1", $account_id );
		$is_active = $this->wpdb->get_var( $sql );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( '1' !== (string) $is_active ) {
			throw new \InvalidArgumentException(
				sprintf( 'account_id %d does not reference an active PayPal account.', $account_id ) // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
			);
		}
	}
}
