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

declare( strict_types=1 );
namespace DonationRouter;

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

/**
 * Persists and retrieves PayPal_Account value objects using wpdb.
 *
 * Credentials are encrypted with AES-256-GCM before storage and decrypted
 * on retrieval. All results are cached in the WordPress object cache.
 *
 * @since 1.0.0
 */
class Account_Repository implements Account_Repository_Interface {

	use Encryption;

	/**
	 * 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 accounts table name.
	 *
	 * @since 1.0.0
	 *
	 * @return string
	 */
	private function table(): string {
		return $this->wpdb->prefix . 'ador_accounts';
	}

	// phpcs:disable Squiz.Commenting.FunctionCommentThrowTag.WrongNumber

	/**
	 * Retrieve a single account by its primary key.
	 *
	 * @since 1.0.0
	 *
	 * @param  int $account_id  Database row ID.
	 *
	 * @return PayPal_Account
	 *
	 * @throws Account_Not_Found_Exception       When no account matches the given ID.
	 * @throws Credential_Decryption_Exception   When stored credentials cannot be decrypted.
	 */
	public function find_by_id( int $account_id ): PayPal_Account {
		$cache_key = $this->cache->get_account_by_id_key( $account_id );
		$cached    = wp_cache_get( $cache_key, Cache_Manager::GROUP );

		if ( $cached instanceof PayPal_Account ) {
			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", $account_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 Account_Not_Found_Exception(
				sprintf( 'PayPal account with ID %d was not found.', $account_id ) // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
			);
		}

		$account = $this->row_to_account( $row );
		wp_cache_set( $cache_key, $account, Cache_Manager::GROUP, Cache_Manager::TTL_RECORD );

		return $account;
	}

	/**
	 * Retrieve a single account by its PayPal merchant ID.
	 *
	 * @since 1.0.0
	 *
	 * @param  string $merchant_id  PayPal-assigned merchant identifier.
	 *
	 * @return PayPal_Account
	 *
	 * @throws Account_Not_Found_Exception       When no account matches the given merchant ID.
	 * @throws Credential_Decryption_Exception   When stored credentials cannot be decrypted.
	 */
	public function find_by_merchant_id( string $merchant_id ): PayPal_Account {
		$cache_key = $this->cache->get_account_by_merchant_id_key( $merchant_id );
		$cached    = wp_cache_get( $cache_key, Cache_Manager::GROUP );

		if ( $cached instanceof PayPal_Account ) {
			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 merchant_id = %s LIMIT 1", $merchant_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 Account_Not_Found_Exception( 'PayPal account not found for the given merchant ID.' );
		}

		$account = $this->row_to_account( $row );
		wp_cache_set( $cache_key, $account, Cache_Manager::GROUP, Cache_Manager::TTL_RECORD );

		return $account;
	}

	// phpcs:disable Squiz.Commenting.FunctionCommentThrowTag.WrongNumber
	/**
	 * Locate the account for a merchant in a specific PayPal environment.
	 *
	 * With the (merchant_id, mode) uniqueness a merchant can be managed once per
	 * mode; this resolves the exact row.
	 *
	 * @since 1.1.0
	 *
	 * @param  string $merchant_id  PayPal-assigned merchant identifier.
	 * @param  string $mode         'live' or 'sandbox'.
	 *
	 * @return PayPal_Account
	 *
	 * @throws Account_Not_Found_Exception       When no account matches the merchant ID and mode.
	 * @throws Credential_Decryption_Exception   When stored credentials cannot be decrypted.
	 */
	public function find_by_merchant_id_and_mode( string $merchant_id, string $mode ): PayPal_Account {
		$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 merchant_id = %s AND mode = %s LIMIT 1", $merchant_id, $mode );
		$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 Account_Not_Found_Exception( 'PayPal account not found for the given merchant ID and mode.' );
		}

		return $this->row_to_account( $row );
	}
	// phpcs:enable Squiz.Commenting.FunctionCommentThrowTag.WrongNumber

	/**
	 * Return all accounts (active and inactive) ordered by account_label ascending.
	 *
	 * @since 1.0.0
	 *
	 * @return PayPal_Account[]
	 */
	public function find_all(): array {
		$cache_key = $this->cache->get_all_accounts_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 account_label 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();
		}

		$accounts = array();
		foreach ( $rows as $row ) {
			$accounts[] = $this->row_to_account( $row );
		}

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

		return $accounts;
	}

	/**
	 * Return all active accounts ordered by account_label ascending.
	 *
	 * @since 1.0.0
	 *
	 * @return PayPal_Account[]
	 */
	public function find_all_active(): array {
		$cache_key = $this->cache->get_all_active_accounts_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 account_label 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();
		}

		$accounts = array();
		foreach ( $rows as $row ) {
			$accounts[] = $this->row_to_account( $row );
		}

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

		return $accounts;
	}

	/**
	 * Persist a new or existing account and return its row ID.
	 *
	 * @since 1.0.0
	 *
	 * @param  PayPal_Account $account  The account to save.
	 *
	 * @return int  The inserted or updated row ID.
	 *
	 * @throws Persistence_Exception  When the database write fails (e.g. duplicate Merchant ID).
	 */
	public function save( PayPal_Account $account ): int {
		$encrypted = $this->encrypt(
			(string) wp_json_encode( $account->get_credentials() )
		);

		$data = array(
			'account_label'         => $account->account_label,
			'merchant_id'           => $account->merchant_id,
			'mode'                  => $account->mode,
			'account_country'       => $account->account_country,
			'legal_entity_name'     => $account->legal_entity_name,
			'legal_entity_tax_id'   => $account->legal_entity_tax_id,
			'legal_entity_address'  => $account->legal_entity_address,
			'encrypted_credentials' => $encrypted,
			'webhook_id'            => $account->webhook_id,
			'is_active'             => $account->is_active ? 1 : 0,
		);

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

		if ( null === $account->id ) {
			$result = $this->wpdb->insert( $this->table(), $data, $formats ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			if ( false === $result ) {
				// UNIQUE KEY (merchant_id, mode) — a duplicate lands here.
				throw new Persistence_Exception(
					esc_html__( 'Could not save the account. A PayPal account with this Merchant ID may already exist for this mode.', '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' => $account->id ),
				$formats,
				array( '%d' )
			);
			if ( false === $result ) {
				throw new Persistence_Exception(
					esc_html__( 'Could not save the account. Please try again.', 'alamin-donation-router' )
				);
			}
			$new_id = $account->id;
		}

		$this->cache->invalidate_account( $account );

		return $new_id;
	}

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

		if ( false !== $rows && $rows > 0 ) {
			// Full invalidation, not just id + active-list: the merchant-id and
			// all-accounts caches would otherwise keep serving the just-deactivated
			// account. find_by_id() only supplies the immutable keys, so a stale
			// cache read is harmless here.
			try {
				$this->cache->invalidate_account( $this->find_by_id( $account_id ) );
			} catch ( Account_Not_Found_Exception $e ) {
				$this->cache->invalidate_all();
			}
		}

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

	/**
	 * Mark an account active by its primary key.
	 *
	 * @since 1.1.0
	 *
	 * @param  int $account_id  Database row ID.
	 *
	 * @return bool  True if at least one row was updated.
	 */
	public function activate( int $account_id ): bool {
		$rows = $this->wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$this->table(),
			array( 'is_active' => 1 ),
			array( 'id' => $account_id ),
			array( '%d' ),
			array( '%d' )
		);

		if ( false !== $rows && $rows > 0 ) {
			// Full invalidation so the merchant-id and all-accounts caches stop
			// serving the stale inactive copy (mirrors deactivate()).
			try {
				$this->cache->invalidate_account( $this->find_by_id( $account_id ) );
			} catch ( Account_Not_Found_Exception $e ) {
				$this->cache->invalidate_all();
			}
		}

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

	/**
	 * Permanently delete an account row by its primary key.
	 *
	 * The account is loaded first so every cache key (id, merchant-id, mode,
	 * all-accounts) can be invalidated after the row is gone.
	 *
	 * @since 1.1.0
	 *
	 * @param  int $account_id  Database row ID.
	 *
	 * @return bool  True if a row was deleted.
	 */
	public function delete( int $account_id ): bool {
		try {
			$account = $this->find_by_id( $account_id );
		} catch ( Account_Not_Found_Exception $e ) {
			return false;
		}

		$rows = $this->wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$this->table(),
			array( 'id' => $account_id ),
			array( '%d' )
		);

		if ( false !== $rows && $rows > 0 ) {
			$this->cache->invalidate_account( $account );
			return true;
		}

		return false;
	}

	/**
	 * Set (or clear) the PayPal webhook ID for an account.
	 *
	 * @since 1.1.0
	 *
	 * @param  int         $account_id  Database row ID.
	 * @param  string|null $webhook_id  PayPal webhook ID, or null to clear it.
	 *
	 * @return bool  True if a row was updated.
	 *
	 * @throws Persistence_Exception  When the update fails.
	 */
	public function update_webhook_id( int $account_id, ?string $webhook_id ): bool {
		$rows = $this->wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$this->table(),
			array( 'webhook_id' => $webhook_id ),
			array( 'id' => $account_id ),
			array( '%s' ),
			array( '%d' )
		);

		if ( false === $rows ) {
			throw new Persistence_Exception(
				esc_html__( 'Could not update the webhook ID. Please try again.', 'alamin-donation-router' )
			);
		}

		if ( $rows > 0 ) {
			// Invalidate every cache view of this row; find_by_id supplies the
			// immutable keys needed for a targeted invalidation.
			try {
				$this->cache->invalidate_account( $this->find_by_id( $account_id ) );
			} catch ( Account_Not_Found_Exception $e ) {
				$this->cache->invalidate_all();
			}
		}

		return $rows > 0;
	}

	/**
	 * Hydrate a PayPal_Account from a raw database row.
	 *
	 * @since 1.0.0
	 *
	 * @param  array<string,mixed> $row  Associative database row.
	 *
	 * @return PayPal_Account
	 *
	 * @throws Credential_Decryption_Exception  When stored credentials cannot be decrypted.
	 */
	private function row_to_account( array $row ): PayPal_Account {
		return PayPal_Account::from_database_row(
			$row,
			$this->decrypt( (string) $row['encrypted_credentials'] )
		);
	}
}
