<?php
/**
 * Profile management for Mail Doctor.
 *
 * @package MailDoctor
 */

namespace MailDoctor;

use MailDoctor\Helpers\Sanitize;
use wpdb;

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

/**
 * Handles persistence and resolution of SMTP profiles.
 */
class Profiles {


	/**
	 * Return table name with prefix.
	 *
	 * @global wpdb $wpdb
	 * @return string
	 */
	public static function table_name(): string {
		global $wpdb;

		return $wpdb->prefix . 'maildoctor_profile';
	}

	/**
	 * Create or update the database schema.
	 *
	 * @return void
	 */
	public static function install(): void {
		global $wpdb;

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

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			name VARCHAR(191) NOT NULL,
			host VARCHAR(191) NOT NULL,
			port SMALLINT UNSIGNED NOT NULL DEFAULT 587,
			secure VARCHAR(16) NOT NULL DEFAULT 'tls',
			auth VARCHAR(32) NOT NULL DEFAULT 'password',
			user VARCHAR(191) NULL,
			secret_ref VARCHAR(191) NULL,
			from_email VARCHAR(191) NULL,
			from_name VARCHAR(191) NULL,
			scope LONGTEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			deleted TINYINT(1) NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			KEY deleted (deleted),
			KEY name (name)
		) {$charset};";

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

	/**
	 * Fetch all available profiles.
	 *
	 * @param bool $include_deleted Whether to include soft-deleted entries.
	 * @return array<int, array<string, mixed>>
	 */
	public static function all( bool $include_deleted = false ): array {
		global $wpdb;

		$where = $include_deleted ? '1=1' : 'deleted = 0';
		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Where clause uses static trusted values.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT * FROM {$table} WHERE {$where} ORDER BY name ASC"
			),
			ARRAY_A
		);

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

		return array_map( array( __CLASS__, 'hydrate' ), $rows );
	}

	/**
	 * Find profile by identifier.
	 *
	 * @param int $id Profile identifier.
	 * @return array<string, mixed>|null
	 */
	public static function find( int $id ): ?array {
		global $wpdb;

		$table = self::table_name();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
				"SELECT * FROM {$table} WHERE id = %d AND deleted = 0",
				$id
			),
			ARRAY_A
		);

		return $row ? self::hydrate( $row ) : null;
	}

	/**
	 * Attempt to locate profile by slug stored in scope payload.
	 *
	 * @param string $slug Profile slug identifier.
	 * @return array<string, mixed>|null
	 */
	public static function find_by_slug( string $slug ): ?array {
		$slug = sanitize_key( $slug );

		if ( '' === $slug ) {
			return null;
		}

		foreach ( self::all( true ) as $profile ) {
			if ( isset( $profile['scope']['slug'] ) && $slug === $profile['scope']['slug'] ) {
				return $profile;
			}
		}

		return null;
	}

	/**
	 * Provide dropdown choices for admin UI.
	 *
	 * @return array<string, string>
	 */
	public static function choices(): array {
		$choices = array(
			'default' => __( 'Default SMTP Settings', 'mail-doctor' ),
		);

		foreach ( self::all() as $profile ) {
			$key             = (string) $profile['id'];
			$choices[ $key ] = $profile['name'];
		}

		return $choices;
	}

	/**
	 * Persist profile changes supplied through admin form.
	 *
	 * @param array<int, mixed> $profiles_input Raw form payload.
	 * @return array<int, int> Saved profile identifiers.
	 */
	public static function sync_from_form( array $profiles_input ): array {
		if ( empty( $profiles_input ) ) {
			return array();
		}

		$saved = array();

		foreach ( $profiles_input as $index => $raw ) {
			if ( ! is_array( $raw ) ) {
				continue;
			}

			$raw = self::normalize_form_row( $raw );
			if ( isset( $raw['_delete'] ) && $raw['_delete'] ) {
				if ( $raw['id'] ) {
					self::soft_delete( $raw['id'] );
				}
				continue;
			}

			if ( '' === $raw['name'] || '' === $raw['host'] ) {
				continue;
			}

			$profile_id = self::save_profile( $raw );

			if ( $profile_id ) {
				$saved[] = $profile_id;
			}
		}

		return $saved;
	}

	/**
	 * Soft delete an existing profile.
	 *
	 * @param int $id Profile identifier.
	 * @return void
	 */
	public static function soft_delete( int $id ): void {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			self::table_name(),
			array(
				'deleted'    => 1,
				'updated_at' => gmdate( 'Y-m-d H:i:s' ),
			),
			array( 'id' => $id ),
			array( '%d', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Resolve best matching profile for provided context.
	 *
	 * @param array<string, mixed> $context Context data (email_id, roles, site_id).
	 * @return array<string, mixed>|null
	 */
	public static function resolve_for_context( array $context ): ?array {
		$matches = array();

		foreach ( self::all() as $profile ) {
			$scope = $profile['scope']['rules'] ?? array();

			$email_match = empty( $scope['emails'] ) || ( ! empty( $context['email_id'] ) && in_array( $context['email_id'], $scope['emails'], true ) );
			$role_match  = empty( $scope['roles'] ) || self::roles_intersect( (array) ( $context['roles'] ?? array() ), (array) $scope['roles'] );
			$site_match  = empty( $scope['sites'] ) || ( isset( $context['site_id'] ) && in_array( (int) $context['site_id'], array_map( 'intval', (array) $scope['sites'] ), true ) );

			if ( ! $email_match || ! $role_match || ! $site_match ) {
				continue;
			}

			$rules = array(
				'emails' => ! empty( $scope['emails'] ),
				'roles'  => ! empty( $scope['roles'] ),
				'sites'  => ! empty( $scope['sites'] ),
			);

			$priority  = (int) ( $scope['priority'] ?? 0 );
			$priority += $rules['emails'] ? 50 : 0;
			$priority += $rules['roles'] ? 20 : 0;
			$priority += $rules['sites'] ? 10 : 0;

			$matches[] = array(
				'profile'  => $profile,
				'priority' => $priority,
				'updated'  => isset( $profile['updated_at'] ) ? strtotime( $profile['updated_at'] ) : 0,
				'id'       => (int) $profile['id'],
			);
		}

		if ( empty( $matches ) ) {
			return null;
		}

		usort(
			$matches,
			static function ( $a, $b ) {
				if ( $a['priority'] === $b['priority'] ) {
					if ( $a['updated'] === $b['updated'] ) {
						return $a['id'] <=> $b['id'];
					}

					return $b['updated'] <=> $a['updated'];
				}

				return $b['priority'] <=> $a['priority'];
			}
		);

		return $matches[0]['profile'];
	}

	/**
	 * Retrieve SMTP credentials array for PHPMailer.
	 *
	 * @param int $id Profile identifier.
	 * @return array<string, mixed>
	 */
	public static function credentials( int $id ): array {
		$profile = self::find( $id );

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

		$secret_payload = self::secret_payload( $profile );

		return array(
			'id'         => (int) $profile['id'],
			'name'       => $profile['name'],
			'host'       => $profile['host'],
			'port'       => (int) $profile['port'],
			'secure'     => $profile['secure'],
			'auth'       => $profile['auth'],
			'username'   => $profile['user'],
			'from_email' => $profile['from_email'],
			'from_name'  => $profile['from_name'],
			'secret_ref' => $profile['secret_ref'] ?? '',
			'secret'     => $secret_payload,
			'scope'      => $profile['scope'],
		);
	}

	/**
	 * Retrieve OAuth payload for a profile.
	 *
	 * @param int $id Profile identifier.
	 * @return array<string, mixed>
	 */
	public static function oauth_details( int $id ): array {
		$profile = self::find( $id );

		if ( ! $profile || 'oauth2' !== $profile['auth'] ) {
			return array();
		}

		$secret = self::secret_payload( $profile );
		$scope  = $profile['scope']['oauth'] ?? array();

		return array(
			'profile_id'    => $profile['id'],
			'provider'      => $secret['provider'] ?? ( $scope['provider'] ?? '' ),
			'client_id'     => $secret['client_id'] ?? ( $scope['client_id'] ?? '' ),
			'client_secret' => $secret['client_secret'] ?? '',
			'refresh_token' => $secret['refresh_token'] ?? '',
			'access_token'  => $secret['access_token'] ?? '',
			'expires_at'    => isset( $secret['expires_at'] ) ? (int) $secret['expires_at'] : 0,
			'tenant'        => $secret['tenant'] ?? ( $scope['tenant'] ?? 'common' ),
			'scope'         => $secret['scope'] ?? ( $scope['scope'] ?? '' ),
			'redirect_uri'  => $scope['redirect_uri'] ?? self::oauth_redirect_uri(),
			'secret_ref'    => $profile['secret_ref'] ?? '',
		);
	}

	/**
	 * Persist refreshed OAuth tokens.
	 *
	 * @param int                  $id     Profile identifier.
	 * @param array<string, mixed> $tokens Token payload (access_token, refresh_token, expires_at).
	 * @return bool
	 */
	public static function update_oauth_tokens( int $id, array $tokens ): bool {
		$details = self::oauth_details( $id );

		if ( empty( $details ) ) {
			return false;
		}

		$payload = array(
			'type'          => 'oauth2',
			'provider'      => $details['provider'],
			'client_id'     => $details['client_id'],
			'client_secret' => $details['client_secret'],
			'tenant'        => $details['tenant'],
			'scope'         => $details['scope'],
			'refresh_token' => $tokens['refresh_token'] ?? $details['refresh_token'],
			'access_token'  => $tokens['access_token'] ?? $details['access_token'],
			'expires_at'    => isset( $tokens['expires_at'] ) ? (int) $tokens['expires_at'] : $details['expires_at'],
		);

		if ( empty( $details['secret_ref'] ) ) {
			$secret_ref = Secrets::store( wp_json_encode( $payload ), array( 'auth' => 'oauth2' ) );
			self::assign_secret_ref( $id, $secret_ref );
		} else {
			Secrets::rotate( $details['secret_ref'], wp_json_encode( $payload ), array( 'auth' => 'oauth2' ) );
		}

		self::update_scope_data(
			$id,
			array(
				'oauth' => array(
					'client_id' => $payload['client_id'],
					'tenant'    => $payload['tenant'],
					'scope'     => $payload['scope'],
				),
			)
		);

		self::touch( $id );

		return true;
	}

	/**
	 * Update OAuth client metadata.
	 *
	 * @param int                  $id   Profile identifier.
	 * @param array<string, mixed> $data Fields to merge (client_id, client_secret, scope, tenant).
	 * @return bool
	 */
	public static function update_oauth_metadata( int $id, array $data ): bool {
		$details = self::oauth_details( $id );

		if ( empty( $details ) ) {
			return false;
		}

		$payload = array(
			'type'          => 'oauth2',
			'provider'      => $data['provider'] ?? $details['provider'],
			'client_id'     => $data['client_id'] ?? $details['client_id'],
			'client_secret' => $data['client_secret'] ?? $details['client_secret'],
			'tenant'        => $data['tenant'] ?? $details['tenant'],
			'scope'         => $data['scope'] ?? $details['scope'],
			'refresh_token' => $details['refresh_token'],
			'access_token'  => $details['access_token'],
			'expires_at'    => $details['expires_at'],
		);

		if ( empty( $details['secret_ref'] ) ) {
			$secret_ref = Secrets::store( wp_json_encode( $payload ), array( 'auth' => 'oauth2' ) );
			self::assign_secret_ref( $id, $secret_ref );
		} else {
			Secrets::rotate( $details['secret_ref'], wp_json_encode( $payload ), array( 'auth' => 'oauth2' ) );
		}

		self::update_scope_data(
			$id,
			array(
				'oauth' => array(
					'provider'     => $payload['provider'],
					'client_id'    => $payload['client_id'],
					'tenant'       => $payload['tenant'],
					'scope'        => $payload['scope'],
					'redirect_uri' => $details['redirect_uri'],
				),
			)
		);

		self::touch( $id );

		return true;
	}

	/**
	 * Save profile row to the database.
	 *
	 * @param array<string, mixed> $data Normalized profile data.
	 * @return int Profile identifier.
	 */
	protected static function persist_row( array $data ): int {
		global $wpdb;

		$now = gmdate( 'Y-m-d H:i:s' );
		$id  = (int) $data['id'];

		$scope = array(
			'slug'  => $data['slug'],
			'rules' => array(
				'roles'    => $data['roles'],
				'sites'    => $data['sites'],
				'emails'   => $data['emails'],
				'priority' => $data['priority'],
			),
		);

		if ( ! empty( $data['oauth'] ) ) {
			$scope['oauth'] = $data['oauth'];
		}

		$record = array(
			'name'       => $data['name'],
			'host'       => $data['host'],
			'port'       => (int) $data['port'],
			'secure'     => $data['secure'],
			'auth'       => $data['auth'],
			'user'       => $data['username'],
			'secret_ref' => $data['secret_ref'] ?: null,
			'from_email' => $data['from_email'] ?: null,
			'from_name'  => $data['from_name'] ?: null,
			'scope'      => wp_json_encode( $scope ),
			'updated_at' => $now,
			'deleted'    => 0,
		);

		$formats = array(
			'%s', // name.
			'%s', // host.
			'%d', // port.
			'%s', // secure.
			'%s', // auth.
			'%s', // user.
			'%s', // secret.
			'%s', // from email.
			'%s', // from name.
			'%s', // scope.
			'%s', // updated_at.
			'%d', // deleted.
		);

		if ( $id > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				self::table_name(),
				$record,
				array( 'id' => $id ),
				$formats,
				array( '%d' )
			);

			return $id;
		}

		$record['created_at'] = $now;
		$formats[]            = '%s';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert(
			self::table_name(),
			$record,
			$formats
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Convert database row into structured array.
	 *
	 * @param array<string, mixed> $row Raw database row.
	 * @return array<string, mixed>
	 */
	protected static function hydrate( array $row ): array {
		$scope = array();

		if ( ! empty( $row['scope'] ) ) {
			$decoded = json_decode( (string) $row['scope'], true );
			if ( is_array( $decoded ) ) {
				$scope = $decoded;
			}
		}

		$rules = $scope['rules'] ?? array();

		return array(
			'id'         => (int) $row['id'],
			'name'       => $row['name'],
			'host'       => $row['host'],
			'port'       => (int) $row['port'],
			'secure'     => $row['secure'],
			'auth'       => $row['auth'],
			'user'       => $row['user'],
			'secret_ref' => $row['secret_ref'],
			'from_email' => $row['from_email'],
			'from_name'  => $row['from_name'],
			'scope'      => array(
				'slug'  => $scope['slug'] ?? '',
				'rules' => array(
					'roles'    => isset( $rules['roles'] ) ? array_map( 'sanitize_key', (array) $rules['roles'] ) : array(),
					'sites'    => isset( $rules['sites'] ) ? array_map( 'intval', (array) $rules['sites'] ) : array(),
					'emails'   => isset( $rules['emails'] ) ? array_map( 'sanitize_key', (array) $rules['emails'] ) : array(),
					'priority' => isset( $rules['priority'] ) ? (int) $rules['priority'] : 0,
				),
			) + ( isset( $scope['oauth'] ) ? array( 'oauth' => $scope['oauth'] ) : array() ),
			'deleted'    => (bool) $row['deleted'],
			'created_at' => $row['created_at'],
			'updated_at' => $row['updated_at'],
		);
	}

	/**
	 * Normalize incoming form row.
	 *
	 * @param array<string, mixed> $row Raw row.
	 * @return array<string, mixed>
	 */
	protected static function normalize_form_row( array $row ): array {
		$id   = isset( $row['id'] ) ? (int) $row['id'] : 0;
		$name = isset( $row['name'] ) ? sanitize_text_field( $row['name'] ) : '';

		$slug_input = isset( $row['slug'] ) ? sanitize_key( $row['slug'] ) : '';

		if ( '' === $slug_input && '' !== $name ) {
			$slug_input = sanitize_key( sanitize_title( $name ) );
		}

		$slug = self::ensure_unique_slug( $slug_input, $id );

		$secure = isset( $row['secure'] ) ? strtolower( sanitize_text_field( $row['secure'] ) ) : 'tls';
		if ( ! in_array( $secure, array( 'none', 'ssl', 'tls' ), true ) ) {
			$secure = 'tls';
		}

		$auth = isset( $row['auth'] ) ? strtolower( sanitize_text_field( $row['auth'] ) ) : 'password';
		if ( ! in_array( $auth, array( 'password', 'oauth2' ), true ) ) {
			$auth = 'password';
		}

		$priority = isset( $row['priority'] ) ? (int) $row['priority'] : 0;
		$roles    = array();
		if ( ! empty( $row['roles'] ) ) {
			$roles = array_filter(
				array_map( 'sanitize_key', (array) $row['roles'] ),
				static function ( $role ) {
					return '' !== $role;
				}
			);
		}

		$sites = array();
		if ( ! empty( $row['sites'] ) ) {
			$sites = array_filter(
				array_map( 'intval', (array) $row['sites'] ),
				static function ( $site ) {
					return $site > 0;
				}
			);
		}

		$emails = array();
		if ( ! empty( $row['emails'] ) ) {
			$emails = array_filter(
				array_map( 'sanitize_key', (array) $row['emails'] ),
				static function ( $email_id ) {
					return '' !== $email_id;
				}
			);
		}

		$oauth = array();

		if ( 'oauth2' === $auth ) {
			$provider  = isset( $row['oauth']['provider'] ) ? sanitize_key( $row['oauth']['provider'] ) : '';
			$client_id = isset( $row['oauth']['client_id'] ) ? sanitize_text_field( $row['oauth']['client_id'] ) : '';
			$scope     = isset( $row['oauth']['scope'] ) ? sanitize_text_field( $row['oauth']['scope'] ) : '';
			$tenant    = isset( $row['oauth']['tenant'] ) ? sanitize_text_field( $row['oauth']['tenant'] ) : 'common';

			$oauth = array(
				'provider'     => $provider,
				'client_id'    => $client_id,
				'tenant'       => $tenant ?: 'common',
				'scope'        => $scope,
				'redirect_uri' => self::oauth_redirect_uri(),
			);
		}

		$password = '';
		if ( isset( $row['password'] ) && '' !== $row['password'] ) {
			$password = wp_unslash( $row['password'] );
		}

		$client_secret = '';
		if ( isset( $row['oauth']['client_secret'] ) && '' !== $row['oauth']['client_secret'] ) {
			$client_secret = wp_unslash( $row['oauth']['client_secret'] );
		}

		$delete = isset( $row['_delete'] ) ? (bool) $row['_delete'] : false;

		return array(
			'id'            => $id,
			'name'          => $name,
			'host'          => isset( $row['host'] ) ? sanitize_text_field( $row['host'] ) : '',
			'port'          => isset( $row['port'] ) ? max( 1, Sanitize::int( $row['port'], 587 ) ) : 587,
			'secure'        => $secure,
			'auth'          => $auth,
			'username'      => isset( $row['username'] ) ? sanitize_text_field( $row['username'] ) : '',
			'from_email'    => isset( $row['from_email'] ) ? sanitize_email( $row['from_email'] ) : '',
			'from_name'     => isset( $row['from_name'] ) ? sanitize_text_field( $row['from_name'] ) : '',
			'roles'         => $roles,
			'sites'         => $sites,
			'emails'        => $emails,
			'priority'      => $priority,
			'slug'          => $slug,
			'password'      => $password,
			'oauth'         => $oauth,
			'client_secret' => $client_secret,
			'secret_ref'    => isset( $row['secret_ref'] ) ? sanitize_text_field( $row['secret_ref'] ) : '',
			'_delete'       => $delete,
		);
	}

	/**
	 * Ensure unique slug.
	 *
	 * @param string $candidate Requested slug.
	 * @param int    $current_id Current profile ID (for updates).
	 * @return string
	 */
	protected static function ensure_unique_slug( string $candidate, int $current_id ): string {
		if ( '' === $candidate ) {
			$candidate = 'profile';
		}

		$slugs = array();
		foreach ( self::all( true ) as $existing ) {
			$slug = $existing['scope']['slug'] ?? '';

			if ( '' === $slug ) {
				continue;
			}

			if ( (int) $existing['id'] === $current_id ) {
				continue;
			}

			$slugs[ $slug ] = true;
		}

		$base    = $candidate;
		$counter = 1;

		while ( isset( $slugs[ $candidate ] ) ) {
			$candidate = $base . '-' . $counter;
			++$counter;
		}

		return $candidate;
	}

	/**
	 * Persist secret payload based on authentication method.
	 *
	 * @param array<string, mixed> $data Normalized payload with password/client secret values.
	 * @return array<string, mixed> Updated payload with secret reference.
	 */
	protected static function persist_secret( array $data ): array {
		$secret_ref = $data['secret_ref'];

		if ( 'password' === $data['auth'] ) {
			if ( '' !== $data['password'] ) {
				$payload = wp_json_encode(
					array(
						'type'     => 'password',
						'password' => $data['password'],
					)
				);

				if ( $secret_ref ) {
					Secrets::rotate( $secret_ref, $payload, array( 'auth' => 'password' ) );
				} else {
					$secret_ref = Secrets::store( $payload, array( 'auth' => 'password' ) );
				}
			}

			$data['secret_ref'] = $secret_ref;
			return $data;
		}

		if ( 'oauth2' === $data['auth'] ) {
			$existing_payload = array();

			if ( $secret_ref ) {
				$raw = Secrets::retrieve( $secret_ref );
				if ( $raw ) {
					$decoded = json_decode( $raw, true );
					if ( is_array( $decoded ) ) {
						$existing_payload = $decoded;
					}
				}
			}

			$payload = array_merge(
				array(
					'type'          => 'oauth2',
					'provider'      => $data['oauth']['provider'] ?? '',
					'refresh_token' => $existing_payload['refresh_token'] ?? '',
					'access_token'  => $existing_payload['access_token'] ?? '',
					'expires_at'    => $existing_payload['expires_at'] ?? 0,
					'scope'         => $data['oauth']['scope'] ?? '',
				),
				array(
					'client_secret' => $data['client_secret'] ?: ( $existing_payload['client_secret'] ?? '' ),
					'client_id'     => $data['oauth']['client_id'] ?? ( $existing_payload['client_id'] ?? '' ),
					'tenant'        => $data['oauth']['tenant'] ?? ( $existing_payload['tenant'] ?? 'common' ),
				)
			);

			if ( $secret_ref ) {
				Secrets::rotate( $secret_ref, wp_json_encode( $payload ), array( 'auth' => 'oauth2' ) );
			} else {
				$secret_ref = Secrets::store( wp_json_encode( $payload ), array( 'auth' => 'oauth2' ) );
			}

			$data['secret_ref'] = $secret_ref;
			return $data;
		}

		return $data;
	}

	/**
	 * Roles intersection helper.
	 *
	 * @param array<int|string> $lhs First role set.
	 * @param array<int|string> $rhs Second role set.
	 * @return bool
	 */
	protected static function roles_intersect( array $lhs, array $rhs ): bool {
		if ( empty( $lhs ) || empty( $rhs ) ) {
			return false;
		}

		$lhs = array_map( 'sanitize_key', $lhs );
		$rhs = array_map( 'sanitize_key', $rhs );

		return (bool) array_intersect( $lhs, $rhs );
	}

	/**
	 * Resolve OAuth redirect URI for profile connections.
	 *
	 * @return string
	 */
	public static function oauth_redirect_uri(): string {
		return admin_url( 'admin-post.php?action=order_mail_doctor_oauth_callback' );
	}

	/**
	 * Inject persisted secret reference before saving.
	 *
	 * @param array<string, mixed> $data Normalized data.
	 * @return array<string, mixed>
	 */
	protected static function prepare_for_save( array $data ): array {
		$data = self::persist_secret( $data );

		unset( $data['password'], $data['client_secret'] );

		return $data;
	}

	/**
	 * Save row ensuring secrets handled correctly.
	 *
	 * @param array<string, mixed> $data Normalized data.
	 * @return int
	 */
	protected static function save_profile( array $data ): int {
		$data = self::prepare_for_save( $data );
		return self::persist_row( $data );
	}

	/**
	 * Retrieve secret payload for profile.
	 *
	 * @param array<string, mixed> $profile Hydrated profile.
	 * @return array<string, mixed>
	 */
	protected static function secret_payload( array $profile ): array {
		$secret_payload = array();
		$secret_ref     = $profile['secret_ref'] ?? '';

		if ( $secret_ref ) {
			$secret_raw = Secrets::retrieve( $secret_ref );
			if ( $secret_raw ) {
				$decoded = json_decode( $secret_raw, true );
				if ( is_array( $decoded ) ) {
					$secret_payload = $decoded;
				} else {
					$secret_payload = array(
						'type'     => 'password',
						'password' => $secret_raw,
					);
				}
			}
		}

		return $secret_payload;
	}

	/**
	 * Update secret reference for profile.
	 *
	 * @param int    $id        Profile identifier.
	 * @param string $secret_ref Secret reference token.
	 * @return void
	 */
	protected static function assign_secret_ref( int $id, string $secret_ref ): void {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			self::table_name(),
			array(
				'secret_ref' => $secret_ref,
				'updated_at' => gmdate( 'Y-m-d H:i:s' ),
			),
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Touch updated_at timestamp for profile.
	 *
	 * @param int $id Profile identifier.
	 * @return void
	 */
	protected static function touch( int $id ): void {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			self::table_name(),
			array( 'updated_at' => gmdate( 'Y-m-d H:i:s' ) ),
			array( 'id' => $id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Update scope meta for a profile.
	 *
	 * @param int                  $id     Profile identifier.
	 * @param array<string, mixed> $merge  Data merged into existing scope.
	 * @return void
	 */
	protected static function update_scope_data( int $id, array $merge ): void {
		$profile = self::find( $id );

		if ( ! $profile ) {
			return;
		}

		$current = $profile['scope'];

		$updated = self::array_replace_recursive( $current, $merge );

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			self::table_name(),
			array(
				'scope'      => wp_json_encode( $updated ),
				'updated_at' => gmdate( 'Y-m-d H:i:s' ),
			),
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Recursive array replacement preserving numeric indexes.
	 *
	 * @param array<string, mixed> $base   Base array.
	 * @param array<string, mixed> $merge  Merge data.
	 * @return array<string, mixed>
	 */
	protected static function array_replace_recursive( array $base, array $merge ): array {
		foreach ( $merge as $key => $value ) {
			if ( is_array( $value ) && isset( $base[ $key ] ) && is_array( $base[ $key ] ) ) {
				$base[ $key ] = self::array_replace_recursive( $base[ $key ], $value );
			} else {
				$base[ $key ] = $value;
			}
		}

		return $base;
	}
}
