<?php
declare(strict_types=1);
namespace ConfManager\Members;

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


use ConfManager\Traits\Singleton;
use ConfManager\Security\Encryption;
use ConfManager\Security\AuditLog;

/**
 * 会員CRUD管理
 *
 * 会員の作成・取得・更新・論理削除・検索を行う。
 * メールアドレス・電話番号・住所は AES-256-GCM で暗号化して保存。
 *
 * @since 1.0.0
 */
class MemberManager {

	use Singleton;

	private const TABLE = 'conf_members';

	/**
	 * 有効な会員種別
	 */
	private const VALID_TYPES = [ 'regular', 'student', 'associate', 'honorary', 'general' ];

	/**
	 * 有効なステータス
	 */
	private const VALID_STATUSES = [ 'active', 'inactive', 'deleted' ];

	/**
	 * 暗号化対象カラム
	 */
	private const ENCRYPTED_COLUMNS = [ 'email', 'phone', 'org_address' ];

	/**
	 * 初期化
	 */
	protected function init(): void {}

	/**
	 * テーブル名を取得
	 *
	 * @return string
	 */
	private function table(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * 会員を作成する
	 *
	 * @param array $data {
	 *     会員データ
	 *     @type string $member_no     会員番号（任意）
	 *     @type string $member_type   会員種別（regular|student|associate|honorary）
	 *     @type string $name_sei      姓
	 *     @type string $name_mei      名
	 *     @type string $name_sei_kana 姓カナ（任意）
	 *     @type string $name_mei_kana 名カナ（任意）
	 *     @type string $email         メールアドレス（平文）
	 *     @type string $phone         電話番号（平文、任意）
	 *     @type string $org_name      所属組織名（任意）
	 *     @type string $org_dept      所属部署（任意）
	 *     @type string $org_address   所属住所（平文、任意）
	 *     @type int    $wp_user_id    WPユーザーID（任意）
	 * }
	 * @return int|WP_Error 作成された会員ID、またはエラー
	 */
	public function create( array $data ): int|\WP_Error {
		global $wpdb;

		// バリデーション
		$error = $this->validate( $data );
		if ( is_wp_error( $error ) ) {
			return $error;
		}

		// メール重複チェック
		$email_hash = Encryption::hash_email( $data['email'] );
		if ( $this->email_exists( $email_hash ) ) {
			return new \WP_Error(
				'duplicate_email',
				__( 'This email address is already registered.', 'conferencemanager' )
			);
		}

		// 会員番号の重複チェック
		if ( ! empty( $data['member_no'] ) && $this->member_no_exists( $data['member_no'] ) ) {
			return new \WP_Error(
				'duplicate_member_no',
				__( 'This member number is already in use.', 'conferencemanager' )
			);
		}

		$enc = Encryption::instance();

		$is_member   = isset( $data['is_member'] ) ? (int) (bool) $data['is_member'] : 1;
		$member_type = sanitize_text_field( $data['member_type'] ?? ( $is_member ? 'regular' : 'general' ) );

		$insert_data = [
			'member_type'    => $member_type,
			'is_member'      => $is_member,
			'name_sei'       => sanitize_text_field( $data['name_sei'] ),
			'name_mei'       => sanitize_text_field( $data['name_mei'] ),
			'name_sei_kana'  => sanitize_text_field( $data['name_sei_kana'] ?? '' ),
			'name_mei_kana'  => sanitize_text_field( $data['name_mei_kana'] ?? '' ),
			'email'          => $enc->encrypt( strtolower( trim( $data['email'] ) ) ),
			'email_hash'     => $email_hash,
			'phone'          => ! empty( $data['phone'] ) ? $enc->encrypt( $data['phone'] ) : null,
			'org_name'       => sanitize_text_field( $data['org_name'] ?? '' ),
			'org_dept'       => sanitize_text_field( $data['org_dept'] ?? '' ),
			'org_address'    => ! empty( $data['org_address'] ) ? $enc->encrypt( $data['org_address'] ) : null,
			'email_verified'  => ! empty( $data['email_verified'] ) ? 1 : 0,
			'registered_via'  => sanitize_text_field( $data['registered_via'] ?? 'admin' ),
			'approval_status' => sanitize_text_field( $data['approval_status'] ?? 'auto_approved' ),
			'status'          => sanitize_text_field( $data['status'] ?? 'active' ),
		];

		if ( ! empty( $data['member_no'] ) ) {
			$insert_data['member_no'] = sanitize_text_field( $data['member_no'] );
		}

		if ( ! empty( $data['wp_user_id'] ) ) {
			$insert_data['wp_user_id'] = absint( $data['wp_user_id'] );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; request-local write, no caching needed.
		$result = $wpdb->insert( $this->table(), $insert_data );

		if ( false === $result ) {
			return new \WP_Error(
				'db_insert_error',
				__( 'Failed to create member.', 'conferencemanager' ),
				$wpdb->last_error
			);
		}

		$member_id = (int) $wpdb->insert_id;

		AuditLog::instance()->log( 'create_member', 'member', $member_id, [
			'name' => $data['name_sei'] . ' ' . $data['name_mei'],
		] );

		return $member_id;
	}

	/**
	 * 会員を取得する（復号済み）
	 *
	 * @param int $id 会員ID
	 * @return array|null 会員データ（復号済み）、または null
	 */
	public function get( int $id ): ?array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table()} WHERE id = %d AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
				$id
			),
			ARRAY_A
		);

		if ( null === $row ) {
			return null;
		}

		return $this->decrypt_row( $row );
	}

	/**
	 * メールハッシュで会員を取得する
	 *
	 * @param string $email メールアドレス（平文）
	 * @return array|null 会員データ（復号済み）、または null
	 */
	public function get_by_email( string $email ): ?array {
		global $wpdb;

		$email_hash = Encryption::hash_email( $email );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table()} WHERE email_hash = %s AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
				$email_hash
			),
			ARRAY_A
		);

		if ( null === $row ) {
			return null;
		}

		return $this->decrypt_row( $row );
	}

	/**
	 * 会員番号で会員を取得する
	 *
	 * @param string $member_no 会員番号
	 * @return array|null 会員データ（復号済み）、または null
	 */
	public function get_by_member_no( string $member_no ): ?array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table()} WHERE member_no = %s AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
				$member_no
			),
			ARRAY_A
		);

		if ( null === $row ) {
			return null;
		}

		return $this->decrypt_row( $row );
	}

	/**
	 * WordPressユーザーIDで会員を取得する
	 *
	 * @param int $wp_user_id WPユーザーID
	 * @return array|null 会員データ（復号済み）、見つからない場合は null
	 */
	public function get_by_wp_user_id( int $wp_user_id ): ?array {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table()} WHERE wp_user_id = %d AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
				$wp_user_id
			),
			ARRAY_A
		);

		if ( null === $row ) {
			return null;
		}

		return $this->decrypt_row( $row );
	}

	/**
	 * 会員を更新する
	 *
	 * @param int   $id   会員ID
	 * @param array $data 更新データ（create() と同じキー）
	 * @return true|WP_Error 成功時は true、エラー時は WP_Error
	 */
	public function update( int $id, array $data ): true|\WP_Error {
		global $wpdb;

		$existing = $this->get( $id );
		if ( null === $existing ) {
			return new \WP_Error( 'not_found', __( 'Member not found.', 'conferencemanager' ) );
		}

		// メールが変更される場合の重複チェック
		if ( ! empty( $data['email'] ) ) {
			$new_hash = Encryption::hash_email( $data['email'] );
			if ( $new_hash !== $existing['email_hash'] && $this->email_exists( $new_hash ) ) {
				return new \WP_Error(
					'duplicate_email',
					__( 'This email address is already registered.', 'conferencemanager' )
				);
			}
		}

		// 会員番号が変更される場合の重複チェック
		if ( ! empty( $data['member_no'] ) && $data['member_no'] !== $existing['member_no'] ) {
			if ( $this->member_no_exists( $data['member_no'], $id ) ) {
				return new \WP_Error(
					'duplicate_member_no',
					__( 'This member number is already in use.', 'conferencemanager' )
				);
			}
		}

		// 会員種別のバリデーション
		if ( isset( $data['member_type'] ) && ! in_array( $data['member_type'], self::VALID_TYPES, true ) ) {
			return new \WP_Error( 'invalid_member_type', __( 'Invalid member type.', 'conferencemanager' ) );
		}

		$enc         = Encryption::instance();
		$update_data = [];

		// テキストフィールド（member_no は分離）
		$text_fields = [ 'member_type', 'name_sei', 'name_mei', 'name_sei_kana', 'name_mei_kana', 'org_name', 'org_dept' ];
		foreach ( $text_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$update_data[ $field ] = sanitize_text_field( $data[ $field ] );
			}
		}

		// 会員番号は UNIQUE KEY のため空文字を保存すると重複衝突する。
		// create() と同様に空の場合は NULL に正規化する（NULL は UNIQUE 制約の対象外）。
		if ( isset( $data['member_no'] ) ) {
			$member_no                = sanitize_text_field( $data['member_no'] );
			$update_data['member_no'] = '' !== $member_no ? $member_no : null;
		}

		// 暗号化フィールド
		if ( ! empty( $data['email'] ) ) {
			$update_data['email']      = $enc->encrypt( strtolower( trim( $data['email'] ) ) );
			$update_data['email_hash'] = Encryption::hash_email( $data['email'] );
		}

		if ( isset( $data['phone'] ) ) {
			$update_data['phone'] = '' !== $data['phone'] ? $enc->encrypt( $data['phone'] ) : null;
		}

		if ( isset( $data['org_address'] ) ) {
			$update_data['org_address'] = '' !== $data['org_address'] ? $enc->encrypt( $data['org_address'] ) : null;
		}

		// WPユーザーID
		if ( isset( $data['wp_user_id'] ) ) {
			$update_data['wp_user_id'] = absint( $data['wp_user_id'] ) ?: null;
		}

		// 会員/非会員フラグ
		if ( isset( $data['is_member'] ) ) {
			$update_data['is_member'] = (int) (bool) $data['is_member'];
		}

		// メール配信停止フラグ
		if ( isset( $data['email_opt_out'] ) ) {
			$new_val = (int) (bool) $data['email_opt_out'];
			if ( $new_val !== (int) ( $existing['email_opt_out'] ?? 0 ) ) {
				$update_data['email_opt_out']    = $new_val;
				$update_data['email_opt_out_at'] = current_time( 'mysql' );
			}
		}

		if ( empty( $update_data ) ) {
			return true;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; request-local write, no caching needed.
		$result = $wpdb->update( $this->table(), $update_data, [ 'id' => $id ] );

		if ( false === $result ) {
			return new \WP_Error(
				'db_update_error',
				__( 'Failed to update member.', 'conferencemanager' ),
				$wpdb->last_error
			);
		}

		AuditLog::instance()->log( 'update_member', 'member', $id, [
			'fields' => array_keys( $update_data ),
		] );

		return true;
	}

	/**
	 * 承認待ち会員を承認する
	 */
	public function approve( int $id ): true|\WP_Error {
		global $wpdb;

		$existing = $this->get( $id );
		if ( null === $existing ) {
			return new \WP_Error( 'not_found', __( 'Member not found.', 'conferencemanager' ) );
		}

		if ( 'pending' !== $existing['approval_status'] ) {
			return new \WP_Error( 'not_pending', __( 'This member is not pending approval.', 'conferencemanager' ) );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; request-local write, no caching needed.
		$wpdb->update(
			$this->table(),
			[ 'approval_status' => 'approved', 'status' => 'active' ],
			[ 'id' => $id ]
		);

		AuditLog::instance()->log( 'approve_member', 'member', $id, [
			'name' => $existing['name_sei'] . ' ' . $existing['name_mei'],
		] );

		return true;
	}

	/**
	 * 承認待ち会員を却下する
	 */
	public function reject( int $id ): true|\WP_Error {
		global $wpdb;

		$existing = $this->get( $id );
		if ( null === $existing ) {
			return new \WP_Error( 'not_found', __( 'Member not found.', 'conferencemanager' ) );
		}

		if ( 'pending' !== $existing['approval_status'] ) {
			return new \WP_Error( 'not_pending', __( 'This member is not pending approval.', 'conferencemanager' ) );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; request-local write, no caching needed.
		$wpdb->update(
			$this->table(),
			[ 'approval_status' => 'rejected', 'status' => 'deleted' ],
			[ 'id' => $id ]
		);

		AuditLog::instance()->log( 'reject_member', 'member', $id, [
			'name' => $existing['name_sei'] . ' ' . $existing['name_mei'],
		] );

		return true;
	}

	/**
	 * 会員を論理削除する
	 *
	 * @param int $id 会員ID
	 * @return true|WP_Error 成功時は true、エラー時は WP_Error
	 */
	public function delete( int $id ): true|\WP_Error {
		global $wpdb;

		$existing = $this->get( $id );
		if ( null === $existing ) {
			return new \WP_Error( 'not_found', __( 'Member not found.', 'conferencemanager' ) );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; request-local write, no caching needed.
		$result = $wpdb->update(
			$this->table(),
			[ 'status' => 'deleted' ],
			[ 'id' => $id ]
		);

		if ( false === $result ) {
			return new \WP_Error(
				'db_delete_error',
				__( 'Failed to delete member.', 'conferencemanager' ),
				$wpdb->last_error
			);
		}

		AuditLog::instance()->log( 'delete_member', 'member', $id, [
			'name' => $existing['name_sei'] . ' ' . $existing['name_mei'],
		] );

		return true;
	}

	/**
	 * 会員を検索する
	 *
	 * @param array $args {
	 *     検索条件
	 *     @type string $keyword     名前・カナ・所属で部分一致検索
	 *     @type string $member_type 会員種別で絞り込み
	 *     @type string $status      ステータスで絞り込み（デフォルト: active）
	 *     @type string $member_no   会員番号で前方一致検索
	 *     @type string $orderby     ソートカラム（デフォルト: id）
	 *     @type string $order       ソート順（ASC|DESC、デフォルト: DESC）
	 *     @type int    $per_page    1ページあたりの件数（デフォルト: 20）
	 *     @type int    $page        ページ番号（デフォルト: 1）
	 * }
	 * @return array{items: array, total: int, page: int, per_page: int}
	 */
	public function search( array $args = [] ): array {
		global $wpdb;

		$table    = $this->table();
		$where    = [];
		$values   = [];
		$per_page = min( 100, max( 1, absint( $args['per_page'] ?? 20 ) ) );
		$page     = max( 1, absint( $args['page'] ?? 1 ) );

		// ステータス（デフォルト: active のみ。deleted は除外）
		$status = sanitize_text_field( $args['status'] ?? 'active' );
		if ( 'all' === $status ) {
			$where[]  = "status != 'deleted'";
		} else {
			$where[]  = 'status = %s';
			$values[] = $status;
		}

		// キーワード検索（名前・カナ・所属で部分一致）
		if ( ! empty( $args['keyword'] ) ) {
			$keyword  = '%' . $wpdb->esc_like( sanitize_text_field( $args['keyword'] ) ) . '%';
			$where[]  = '(name_sei LIKE %s OR name_mei LIKE %s OR name_sei_kana LIKE %s OR name_mei_kana LIKE %s OR org_name LIKE %s)';
			$values[] = $keyword;
			$values[] = $keyword;
			$values[] = $keyword;
			$values[] = $keyword;
			$values[] = $keyword;
		}

		// 会員種別
		if ( ! empty( $args['member_type'] ) && in_array( $args['member_type'], self::VALID_TYPES, true ) ) {
			$where[]  = 'member_type = %s';
			$values[] = $args['member_type'];
		}

		// 会員/非会員フィルタ
		if ( isset( $args['is_member'] ) && '' !== $args['is_member'] ) {
			$where[]  = 'is_member = %d';
			$values[] = (int) $args['is_member'];
		}

		// 承認ステータス
		if ( ! empty( $args['approval_status'] ) ) {
			$where[]  = 'approval_status = %s';
			$values[] = sanitize_text_field( $args['approval_status'] );
		}

		// 会員番号（前方一致）
		if ( ! empty( $args['member_no'] ) ) {
			$where[]  = 'member_no LIKE %s';
			$values[] = $wpdb->esc_like( sanitize_text_field( $args['member_no'] ) ) . '%';
		}

		$where_sql = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : '';

		// ソート
		$allowed_orderby = [ 'id', 'member_no', 'name_sei', 'member_type', 'org_name', 'created_at', 'updated_at' ];
		$orderby         = in_array( $args['orderby'] ?? 'id', $allowed_orderby, true )
			? $args['orderby'] ?? 'id'
			: 'id';
		$order           = 'ASC' === strtoupper( $args['order'] ?? 'DESC' ) ? 'ASC' : 'DESC';

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Hardcoded WP prefix table; $where_sql uses validated placeholders; values pass through $wpdb->prepare().
		// 総件数
		$count_sql = "SELECT COUNT(*) FROM {$table} {$where_sql}";
		$total     = ! empty( $values )
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic query built with safe values
			? (int) $wpdb->get_var( $wpdb->prepare( $count_sql, ...$values ) )
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic query built with safe values
			: (int) $wpdb->get_var( $count_sql );

		// データ取得
		$offset       = ( $page - 1 ) * $per_page;
		$query_values = array_merge( $values, [ $per_page, $offset ] );
		$data_sql     = "SELECT * FROM {$table} {$where_sql} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic query built with safe values
		$rows         = $wpdb->get_results(
			$wpdb->prepare( $data_sql, ...$query_values ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		$items = array_map( [ $this, 'decrypt_row' ], $rows ?: [] );

		return [
			'items'    => $items,
			'total'    => $total,
			'page'     => $page,
			'per_page' => $per_page,
		];
	}

	/**
	 * 会員数を取得する
	 *
	 * @param string $status ステータス（デフォルト: active）
	 * @return int
	 */
	public function count( string $status = 'active' ): int {
		global $wpdb;

		if ( 'all' === $status ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
			return (int) $wpdb->get_var(
				"SELECT COUNT(*) FROM {$this->table()} WHERE status != 'deleted'" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table()} WHERE status = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
				$status
			)
		);
	}

	/**
	 * メールアドレスの存在チェック（ハッシュベース）
	 *
	 * @param string $email_hash SHA-256ハッシュ
	 * @param int    $exclude_id 除外するID（更新時の自分自身）
	 * @return bool
	 */
	public function email_exists( string $email_hash, int $exclude_id = 0 ): bool {
		global $wpdb;

		if ( $exclude_id > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
			return (bool) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->table()} WHERE email_hash = %s AND id != %d AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
					$email_hash,
					$exclude_id
				)
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
		return (bool) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table()} WHERE email_hash = %s AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
				$email_hash
			)
		);
	}

	/**
	 * 会員番号の存在チェック
	 *
	 * @param string $member_no  会員番号
	 * @param int    $exclude_id 除外するID
	 * @return bool
	 */
	public function member_no_exists( string $member_no, int $exclude_id = 0 ): bool {
		global $wpdb;

		if ( $exclude_id > 0 ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
			return (bool) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->table()} WHERE member_no = %s AND id != %d AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
					$member_no,
					$exclude_id
				)
			);
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query.
		return (bool) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table()} WHERE member_no = %s AND status != 'deleted'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from safe internal method.
				$member_no
			)
		);
	}

	/**
	 * 入力データのバリデーション
	 *
	 * @param array $data 会員データ
	 * @return true|WP_Error
	 */
	private function validate( array $data ): true|\WP_Error {
		if ( empty( $data['name_sei'] ) || empty( $data['name_mei'] ) ) {
			return new \WP_Error( 'missing_name', __( 'Last name and first name are required.', 'conferencemanager' ) );
		}

		if ( empty( $data['email'] ) || ! is_email( $data['email'] ) ) {
			return new \WP_Error( 'invalid_email', __( 'Please enter a valid email address.', 'conferencemanager' ) );
		}

		if ( isset( $data['member_type'] ) && ! in_array( $data['member_type'], self::VALID_TYPES, true ) ) {
			return new \WP_Error( 'invalid_member_type', __( 'Invalid member type.', 'conferencemanager' ) );
		}

		return true;
	}

	/**
	 * DBから取得した行の暗号化カラムを復号する
	 *
	 * @param array $row DBの行データ
	 * @return array 復号済みデータ
	 */
	private function decrypt_row( array $row ): array {
		$enc = Encryption::instance();

		foreach ( self::ENCRYPTED_COLUMNS as $col ) {
			if ( ! empty( $row[ $col ] ) ) {
				try {
					$row[ $col ] = $enc->decrypt( $row[ $col ] );
				} catch ( \RuntimeException $e ) {
					$row[ $col ] = '';
				}
			}
		}

		return $row;
	}

	/**
	 * 有効な会員種別一覧を取得
	 *
	 * @return array
	 */
	public static function get_valid_types(): array {
		return self::VALID_TYPES;
	}
}
