<?php
/**
 * TMDO_User_Stress_Tester — User entity 壓力測試 / Benchmark 工具
 *
 * 由 WP Cron 驅動的批次任務，自動建立大量測試使用者並填滿所有 user 相關 flat tables。
 * 完成後自動產出 benchmark 報告（寫入速率 / DB 容量 / 查詢效能）。
 *
 * 兩種模式：
 * - fast：直接 bulk INSERT 到 wp_users + flat tables（跳過 wp_insert_user 與 user_register hook）
 *   速率 ~5,000-10,000 users/秒，僅核心 caps meta 寫到 wp_usermeta
 * - realistic：走 wp_insert_user + update_user_meta，讓 Hook Bus 自然攔截
 *   速率 ~50-200 users/秒，完整測試生產寫入路徑
 *
 * @package WP_Data_Optimizer
 * @since   2.6.7
 */

// phpcs:ignore WPDO.AntiEAV -- platform stress tester: intentional raw meta SQL for baseline comparison

declare(strict_types=1);

// phpcs:disable Squiz.Commenting,Generic.Commenting,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,Generic.CodeAnalysis.UnusedFunctionParameter,WordPress.WP.I18n.MissingTranslatorsComment,WordPress.PHP.NoSilencedErrors,WordPress.PHP.YodaConditions,WordPress.WP.AlternativeFunctions.rand_mt_rand,WordPress.PHP.DiscouragedPHPFunctions.serialize_serialize -- 測試工具：mt_rand 用於杜撰測試資料；serialize() 為 WP capabilities 標準格式

defined( 'ABSPATH' ) || exit;

final class TMDO_User_Stress_Tester {

	public const TEST_USER_PREFIX    = 'test';
	public const TEST_PASSWORD       = 'PassWord2026!';
	public const TEST_EMAIL_DOMAIN   = 'stresstest.local';
	public const DEFAULT_BATCH_SIZE  = 500;
	public const MAX_BATCH_SIZE      = 2000;
	public const MIN_BATCH_DELAY_SEC = 1;
	public const OPT_STATE           = 'wpdo_stress_test_state';
	public const CRON_HOOK           = 'wpdo_stress_test_batch';
	public const MODE_FAST           = 'fast';
	public const MODE_REALISTIC      = 'realistic';

	/** Wall-clock budget per batch (sec). Bounded so polling stays responsive and far below nginx 60s timeout. */
	public const BATCH_DEADLINE_SEC = 8;

	/** Transient key for cancel/cleanup signalling — in-flight batches檢查此旗標即時中止. */
	public const CANCEL_FLAG = 'wpdo_stress_cancel_flag';

	private const FIRST_NAMES = array(
		'Alice',
		'Bob',
		'Carol',
		'David',
		'Eve',
		'Frank',
		'Grace',
		'Henry',
		'Ivy',
		'Jack',
		'Kate',
		'Leo',
		'Mia',
		'Nick',
		'Olivia',
		'Peter',
		'Queen',
		'Ryan',
		'Sara',
		'Tom',
		'Uma',
		'Vince',
		'Wendy',
		'Xander',
		'Yuki',
		'Zoe',
	);

	private const LAST_NAMES = array(
		'Wang',
		'Lin',
		'Chen',
		'Lee',
		'Liu',
		'Yang',
		'Huang',
		'Zhao',
		'Wu',
		'Zhou',
		'Smith',
		'Johnson',
		'Brown',
		'Davis',
		'Miller',
		'Wilson',
		'Moore',
		'Taylor',
		'Anderson',
		'Thomas',
	);

	private const MEMBERSHIP_LEVELS = array( 'bronze', 'silver', 'gold', 'platinum', 'diamond' );
	private const TIER_SOURCES      = array( 'manual', 'auto', 'imported', 'default' );
	private const LOCALES           = array( 'zh_TW', 'en_US', 'ja', 'zh_CN' );
	private const SPECIALTIES_POOL  = array( 'music', 'art', 'sports', 'tech', 'food', 'travel', 'reading', 'gaming', 'fitness', 'design' );

	/**
	 * v2.8.2: data pools for the user entity groups added in v2.7.0.
	 * Keep stress fixtures aligned with TMDO_Member_Fields registration.
	 */
	private const SOCIAL_KEYS = array(
		'facebook',
		'twitter',
		'instagram',
		'youtube',
		'tiktok',
		'linkedin',
		'vimeo',
		'vkontakte',
		'mastodon',
		'medium',
		'wordpress',
		'odnoklassniki',
		'pinterest',
		'dribbble',
		'github',
	);

	private const BILLING_KEYS = array(
		'billing_first_name',
		'billing_last_name',
		'billing_company',
		'billing_address_1',
		'billing_address_2',
		'billing_city',
		'billing_state',
		'billing_postcode',
		'billing_country',
		'billing_email',
		'billing_phone',
	);

	private const SHIPPING_KEYS = array(
		'shipping_first_name',
		'shipping_last_name',
		'shipping_company',
		'shipping_address_1',
		'shipping_address_2',
		'shipping_city',
		'shipping_state',
		'shipping_postcode',
		'shipping_country',
		'shipping_phone',
	);

	private const COUNTRY_POOL = array( 'TW', 'US', 'JP', 'CN', 'HK', 'SG', 'MY' );
	private const STATE_POOL   = array( 'Taipei', 'New Taipei', 'Taichung', 'CA', 'NY', 'TX', 'Tokyo', 'Osaka', 'Beijing', 'Shanghai' );
	private const CITY_POOL    = array( 'Taipei', 'Taichung', 'Kaohsiung', 'San Francisco', 'New York', 'Tokyo', 'Osaka', 'Beijing', 'Hong Kong', 'Singapore' );
	private const BIO_POOL     = array(
		'陶藝創作者，專注於生活陶藝與茶道器具。',
		'皮革職人，堅持使用植鞣皮革與手縫工藝。',
		'花藝設計師，融合東西方花藝美學。',
		'金工師傅，融合日本金工技法與台灣原住民圖騰元素。',
		'法式甜點師，堅持使用天然食材與進口原料。',
		'Coffee aficionado, single-origin specialist.',
		'Indie music producer & sound engineer.',
		'Yoga instructor — Hatha, Vinyasa, Yin.',
		'Software engineer at a Taipei startup.',
		'Travel writer & photographer.',
	);

	// ─────────────────────────────────────────────────────────
	// 對外入口
	// ─────────────────────────────────────────────────────────

	/**
	 * 啟動壓力測試。建立 state + 排程第一個 batch。
	 */
	public static function start( int $target_count, string $mode = self::MODE_FAST, int $batch_size = self::DEFAULT_BATCH_SIZE ): array {
		if ( $target_count < 1 ) {
			return array(
				'ok'    => false,
				'error' => 'target_count must be >= 1',
			);
		}
		if ( $target_count > 1000000 ) {
			return array(
				'ok'    => false,
				'error' => 'target_count too large (max 1,000,000)',
			);
		}
		if ( ! in_array( $mode, array( self::MODE_FAST, self::MODE_REALISTIC ), true ) ) {
			return array(
				'ok'    => false,
				'error' => 'invalid mode',
			);
		}
		$batch_size = max( 1, min( self::MAX_BATCH_SIZE, $batch_size ) );

		$current = self::get_state();
		if ( ! empty( $current['status'] ) && 'running' === $current['status'] ) {
			return array(
				'ok'    => false,
				'error' => 'already_running',
				'state' => $current,
			);
		}

		$state = array(
			'job_id'       => uniqid( 'stress_', true ),
			'status'       => 'running',
			'mode'         => $mode,
			'target'       => $target_count,
			'batch_size'   => $batch_size,
			'started_at'   => time(),
			'processed'    => 0,
			'last_user_id' => 0,
			'batches_done' => 0,
			'batches_log'  => array(),
			'errors'       => array(),
			'peak_memory'  => 0,
			'completed_at' => null,
			'benchmark'    => null,
		);
		update_option( self::OPT_STATE, $state, false );

		// 清掉前次留下的 cancellation flag（避免新測試一啟動就被誤判為已取消）
		delete_transient( self::CANCEL_FLAG );

		wp_clear_scheduled_hook( self::CRON_HOOK );
		wp_schedule_single_event( time(), self::CRON_HOOK );

		// 注意：不在這裡同步執行 run_batch()。
		// 若 batch_size 大或 mode=realistic，run_batch() 可能跑數十秒到數分鐘，
		// PHP-FPM / nginx 會在 60s 時 504 Gateway Timeout。
		// 第一個 batch 由前端 polling 進來時的 pump_if_due() 推進，每個 batch 有 wall-clock 限制。
		return array(
			'ok'    => true,
			'state' => $state,
		);
	}

	/**
	 * 取消執行中的測試。
	 *
	 * 設 cancellation transient flag，in-flight 的 run_batch() 與 run_batch_realistic()
	 * 會在每個 user 迭代之前檢查並提早 break；run_batch() 結尾的 update_option 會
	 * 重讀 state 確認 status 仍是 'running' 才覆寫，避免 race condition 把 cancelled
	 * 重新覆蓋為 running。
	 */
	public static function cancel(): array {
		$state = self::get_state();
		if ( empty( $state ) ) {
			return array(
				'ok'      => true,
				'message' => 'no_active_job',
			);
		}

		// 設 flag 給 in-flight batch 看到，提早 break
		set_transient( self::CANCEL_FLAG, 1, 600 );

		wp_clear_scheduled_hook( self::CRON_HOOK );

		// 重讀 state（避免覆寫 in-flight batch 已寫入的 progress）
		$state                 = self::get_state();
		$state['status']       = 'cancelled';
		$state['completed_at'] = time();
		update_option( self::OPT_STATE, $state, false );

		return array(
			'ok'    => true,
			'state' => $state,
		);
	}

	/**
	 * 取得目前進度狀態。
	 */
	public static function get_state(): array {
		$state = get_option( self::OPT_STATE, array() );
		return is_array( $state ) ? $state : array();
	}

	/**
	 * 取得進度（含計算的速率與 ETA）。
	 *
	 * 副作用：若狀態為 running 且 wp-cron 沒按時觸發（dev 環境常見），主動同步推進一個 batch，
	 * 確保 admin polling 看得到進度，不依賴外部 cron worker。
	 *
	 * @param bool $pump 是否在偵測到延誤時主動推進。預設 true，REST status endpoint 用。
	 */
	public static function get_progress( bool $pump = true ): array {
		if ( $pump ) {
			self::pump_if_due();
		}

		$state = self::get_state();
		if ( empty( $state ) ) {
			return array(
				'status'    => 'idle',
				'processed' => 0,
				'target'    => 0,
				'pct'       => 0,
			);
		}

		$processed = (int) ( $state['processed'] ?? 0 );
		$target    = (int) ( $state['target'] ?? 0 );
		$started   = (int) ( $state['started_at'] ?? 0 );
		$ended     = (int) ( $state['completed_at'] ?? 0 );

		$now     = $ended > 0 ? $ended : time();
		$elapsed = max( 1, $now - $started );
		$rate    = $processed > 0 ? round( $processed / $elapsed, 1 ) : 0;
		$eta_sec = ( $rate > 0 && $processed < $target ) ? (int) ceil( ( $target - $processed ) / $rate ) : 0;
		$pct     = $target > 0 ? round( ( $processed / $target ) * 100, 1 ) : 0;

		return array_merge(
			$state,
			array(
				'pct'             => $pct,
				'rate_per_sec'    => $rate,
				'elapsed_sec'     => $elapsed,
				'eta_sec'         => $eta_sec,
				'test_user_count' => self::count_test_users(),
			)
		);
	}

	/**
	 * 檢查是否需要主動推進一個 batch。
	 *
	 * 觸發條件：
	 * - state.status === 'running'
	 * - 距離上次 batch 已超過 N 秒（避免 polling 太密集連續跑）
	 * - 沒有其他 request 在執行（用 transient lock 防併發）
	 *
	 * 之所以這樣設計：dev / 低流量環境的 wp-cron 可能不會準時跑（CLI 沒 web request、
	 * spawn_cron 是 async fire-and-forget）。讓 polling 自帶推進可確保進度條會動。
	 */
	public static function pump_if_due(): void {
		$state = self::get_state();
		if ( empty( $state ) || 'running' !== ( $state['status'] ?? '' ) ) {
			return;
		}

		// 至少間隔 1 秒推一次，避免極端密集 polling 把 DB 壓垮
		$last_pushed_at = (int) ( $state['last_pushed_at'] ?? $state['started_at'] ?? 0 );
		if ( time() - $last_pushed_at < 1 ) {
			return;
		}

		// Transient lock 防多個 polling request 同時執行（30s TTL，確保即使崩潰也會自動釋放）
		$lock_key = 'wpdo_stress_pump_lock';
		if ( false !== get_transient( $lock_key ) ) {
			return;
		}
		set_transient( $lock_key, 1, 30 );

		// 確保 PHP 有足夠時間跑滿 batch deadline；不超過 nginx 60s timeout
		if ( function_exists( 'set_time_limit' ) ) {
			@set_time_limit( self::BATCH_DEADLINE_SEC + 10 );
		}

		try {
			self::run_batch();
		} finally {
			delete_transient( $lock_key );
		}
	}

	/**
	 * Cron 觸發點：執行一個批次。完成則啟動 benchmark；未完成 reschedule。
	 */
	public static function run_batch(): void {
		$state = self::get_state();
		if ( empty( $state ) || 'running' !== ( $state['status'] ?? '' ) ) {
			return;
		}

		// 開頭就檢查 cancellation flag — 即使 cron 已排到，看到 flag 立即放棄
		if ( false !== get_transient( self::CANCEL_FLAG ) ) {
			return;
		}

		$target     = (int) $state['target'];
		$processed  = (int) $state['processed'];
		$batch_size = (int) $state['batch_size'];
		$mode       = (string) $state['mode'];
		$remaining  = $target - $processed;
		if ( $remaining <= 0 ) {
			self::finalize( $state );
			return;
		}
		$this_batch_size = min( $batch_size, $remaining );

		$batch_started = microtime( true );
		try {
			if ( self::MODE_FAST === $mode ) {
				$inserted = self::run_batch_fast( $this_batch_size );
			} else {
				$inserted = self::run_batch_realistic( $this_batch_size );
			}
		} catch ( \Throwable $e ) {
			$state['errors'][]     = array(
				'time'    => time(),
				'message' => $e->getMessage(),
			);
			$state['status']       = 'failed';
			$state['completed_at'] = time();
			update_option( self::OPT_STATE, $state, false );
			if ( class_exists( 'TMDO_Logger' ) ) {
				TMDO_Logger::error( 'stress_test', 'user_batch_failed', $e->getMessage() );
			}
			return;
		}
		$batch_elapsed = microtime( true ) - $batch_started;

		// 重讀 state — 中間可能被 cancel() 改成 'cancelled'，不能用本地快照覆寫
		$latest = self::get_state();
		if ( empty( $latest ) ) {
			return; // state 已被 cleanup() 刪光，不再寫入
		}
		$is_cancelled = ( 'cancelled' === ( $latest['status'] ?? '' ) ) || false !== get_transient( self::CANCEL_FLAG );

		// 累加 progress 到「最新」state（保留 cancel 寫入的 status）
		$latest['processed']     = ( (int) ( $latest['processed'] ?? 0 ) ) + $inserted;
		$latest['batches_done']  = ( (int) ( $latest['batches_done'] ?? 0 ) ) + 1;
		$latest['batches_log'][] = array(
			'n'           => $inserted,
			'duration_ms' => (int) round( $batch_elapsed * 1000 ),
		);
		if ( count( $latest['batches_log'] ) > 200 ) {
			$latest['batches_log'] = array_slice( $latest['batches_log'], -200 );
		}
		$latest['peak_memory']    = max( (int) ( $latest['peak_memory'] ?? 0 ), (int) memory_get_peak_usage( true ) );
		$latest['last_pushed_at'] = time();

		// 若已被 cancel：保留 'cancelled' 狀態，僅累加 progress 給 UI 顯示「實際已寫入幾筆」
		// 不重新排 cron、不 finalize benchmark
		if ( $is_cancelled ) {
			update_option( self::OPT_STATE, $latest, false );
			return;
		}

		update_option( self::OPT_STATE, $latest, false );

		if ( $latest['processed'] >= $target ) {
			self::finalize( $latest );
			return;
		}

		// 排程下一個批次
		wp_schedule_single_event( time() + self::MIN_BATCH_DELAY_SEC, self::CRON_HOOK );
	}

	/**
	 * 清除所有 test_* 使用者及其 flat table 資料。
	 *
	 * 也設 cancellation flag，確保 in-flight batch 看到立即中止，避免 cleanup 後
	 * 又被新建一筆 user 進來。
	 */
	public static function cleanup(): array {
		global $wpdb;

		// 通知 in-flight batch 立即停（最壞情況等 ~3 秒讓當前 user 完成）
		set_transient( self::CANCEL_FLAG, 1, 600 );
		wp_clear_scheduled_hook( self::CRON_HOOK );

		$user_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT ID FROM {$wpdb->users} WHERE user_login LIKE %s",
				$wpdb->esc_like( self::TEST_USER_PREFIX ) . '%'
			)
		);

		if ( empty( $user_ids ) ) {
			return array(
				'ok'      => true,
				'deleted' => 0,
			);
		}

		$count   = count( $user_ids );
		$id_list = implode( ',', array_map( 'absint', $user_ids ) );

		// 先刪 flat tables
		$flat_tables = self::get_user_flat_tables();
		foreach ( $flat_tables as $tbl ) {
			$wpdb->query( "DELETE FROM `{$tbl}` WHERE user_id IN ({$id_list})" );
		}

		// 刪 usermeta + users
		$wpdb->query( "DELETE FROM {$wpdb->usermeta} WHERE user_id IN ({$id_list})" );
		$wpdb->query( "DELETE FROM {$wpdb->users} WHERE ID IN ({$id_list})" );

		// 重置 state + cancel flag（後者確保下一次啟動不會被誤判為 cancelled）
		delete_option( self::OPT_STATE );
		delete_transient( self::CANCEL_FLAG );

		// 清空 cache（每個 user 各自清，clean_user_cache 不接受 array）
		foreach ( $user_ids as $uid ) {
			clean_user_cache( (int) $uid );
		}

		return array(
			'ok'      => true,
			'deleted' => $count,
		);
	}

	/**
	 * 取得目前 test_* 使用者數量。
	 */
	public static function count_test_users(): int {
		global $wpdb;
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->users} WHERE user_login LIKE %s",
				$wpdb->esc_like( self::TEST_USER_PREFIX ) . '%'
			)
		);
	}

	// ─────────────────────────────────────────────────────────
	// 批次寫入：Fast Mode
	// ─────────────────────────────────────────────────────────

	private static function run_batch_fast( int $count ): int {
		global $wpdb;

		// 一次計算密碼 hash（重複用同一個 hash，若密碼相同 phpass 會比對通過嗎？
		// 注意：phpass 每次 hash_password 會產生不同 salt，所以這裡用同一 hash 對所有 user 是 OK 的
		// 因為 wp_check_password 只是用儲存的 hash 校驗輸入密碼，相同密碼+相同 hash 會通過
		static $cached_hash = null;
		if ( null === $cached_hash ) {
			$cached_hash = wp_hash_password( self::TEST_PASSWORD );
		}

		$registered_at = current_time( 'mysql', true );

		// 先取得目前最大 test 序號（避免衝突）
		$next_seq = self::next_test_user_seq();

		// 準備 wp_users bulk INSERT
		$user_rows = array();
		$user_data = array();
		for ( $i = 0; $i < $count; $i++ ) {
			$seq     = $next_seq + $i;
			$login   = self::TEST_USER_PREFIX . $seq;
			$email   = $login . '@' . self::TEST_EMAIL_DOMAIN;
			$first   = self::FIRST_NAMES[ $seq % count( self::FIRST_NAMES ) ];
			$last    = self::LAST_NAMES[ ( $seq * 7 ) % count( self::LAST_NAMES ) ];
			$display = $first . ' ' . $last . ' #' . $seq;

			$user_rows[]       = $wpdb->prepare(
				'(%s,%s,%s,%s,%s,%s,%s,%d)',
				$login,
				$cached_hash,
				$login,
				$email,
				'',
				$registered_at,
				$display,
				0
			);
			$user_data[ $seq ] = array(
				'login'   => $login,
				'email'   => $email,
				'first'   => $first,
				'last'    => $last,
				'display' => $display,
			);
		}

		$sql = "INSERT INTO {$wpdb->users}
			(user_login, user_pass, user_nicename, user_email, user_url, user_registered, display_name, user_status)
			VALUES " . implode( ',', $user_rows );
		$wpdb->query( $sql );

		// 取出剛 INSERT 的 user IDs（用 user_login 對應）
		$placeholders = implode( ',', array_fill( 0, count( $user_data ), '%s' ) );
		$logins       = array_map( fn( $d ) => $d['login'], $user_data );
		$rows         = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ID, user_login FROM {$wpdb->users} WHERE user_login IN ({$placeholders})",
				...$logins
			),
			ARRAY_A
		);
		$login_to_id  = array();
		foreach ( (array) $rows as $row ) {
			$login_to_id[ $row['user_login'] ] = (int) $row['ID'];
		}

		// 從 user_login 反查回 seq
		$seq_to_id = array();
		foreach ( $user_data as $seq => $data ) {
			if ( isset( $login_to_id[ $data['login'] ] ) ) {
				$seq_to_id[ $seq ] = $login_to_id[ $data['login'] ];
			}
		}

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

		// usermeta：必填的 caps + nickname + first_name + last_name
		self::insert_usermeta_bulk( $seq_to_id, $user_data );

		// flat tables — existing v2.5.x groups
		self::insert_flat_hot( $seq_to_id );
		self::insert_flat_membership( $seq_to_id );
		self::insert_flat_activity( $seq_to_id );
		self::insert_flat_profile( $seq_to_id, $user_data );
		self::insert_flat_sso( $seq_to_id );
		self::insert_flat_cold( $seq_to_id );
		self::insert_points_ledger( $seq_to_id );

		// v2.7.0+ groups: legacy WP/WC/HP keys absorbed into entity bridge
		self::insert_flat_core_profile( $seq_to_id, $user_data );
		self::insert_flat_social( $seq_to_id );
		self::insert_flat_commerce( $seq_to_id, $user_data );
		self::insert_flat_hp_user( $seq_to_id );

		return count( $seq_to_id );
	}

	private static function next_test_user_seq(): int {
		global $wpdb;
		$max = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT MAX(CAST(SUBSTRING(user_login, %d) AS UNSIGNED)) FROM {$wpdb->users} WHERE user_login LIKE %s",
				strlen( self::TEST_USER_PREFIX ) + 1,
				$wpdb->esc_like( self::TEST_USER_PREFIX ) . '%'
			)
		);
		return ( null === $max ) ? 1 : ( (int) $max ) + 1;
	}

	/**
	 * Bulk-insert the WP-core-required usermeta rows for a batch of test users.
	 *
	 * v2.8.3: drops `nickname` / `first_name` / `last_name` rows — those keys
	 * are managed by the v2.7.0 `core_profile` entity group and written directly
	 * into `wp_wpdo_user_core_profile` by `insert_flat_core_profile()`. Writing
	 * them to `wp_usermeta` here was a direct-SQL bypass of Hook Bus aeav_only
	 * short-circuit logic and inflated the wp_users:wp_usermeta ratio by 3 rows
	 * per test user (1:5 instead of the expected 1:2). Only `wp_capabilities`
	 * and `wp_user_level` are kept because WP core itself writes them on every
	 * `wp_insert_user()` and they are NOT registered as entity fields.
	 *
	 * @param array $seq_to_id Map seq → user_id from the bulk wp_users insert.
	 * @param array $user_data Per-seq generated user data (unused in v2.8.3).
	 */
	private static function insert_usermeta_bulk( array $seq_to_id, array $user_data ): void {
		global $wpdb;
		unset( $user_data ); // v2.8.3: name fields no longer written here.
		$caps_value = serialize( array( 'subscriber' => true ) );

		$rows = array();
		foreach ( $seq_to_id as $uid ) {
			$rows[] = $wpdb->prepare( '(%d,%s,%s)', $uid, $wpdb->prefix . 'capabilities', $caps_value );
			$rows[] = $wpdb->prepare( '(%d,%s,%s)', $uid, $wpdb->prefix . 'user_level', '0' );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO {$wpdb->usermeta} (user_id, meta_key, meta_value) VALUES " . implode( ',', $rows ) );
		}
	}

	private static function insert_flat_hot( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_hot";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$money  = mt_rand( 0, 1000000 ) / 100;
			$orders = mt_rand( 0, 50 );
			$last   = time() - mt_rand( 0, 365 * DAY_IN_SECONDS );
			$rows[] = $wpdb->prepare( '(%d,%f,%d,%d)', $uid, $money, $orders, $last );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, _money_spent, _order_count, _last_order) VALUES " . implode( ',', $rows ) );
		}
	}

	private static function insert_flat_membership( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_membership";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$level     = self::MEMBERSHIP_LEVELS[ $seq % count( self::MEMBERSHIP_LEVELS ) ];
			$points    = mt_rand( 0, 50000 );
			$expires   = gmdate( 'Y-m-d H:i:s', time() + mt_rand( 30, 730 ) * DAY_IN_SECONDS );
			$activated = gmdate( 'Y-m-d H:i:s', time() - mt_rand( 1, 365 ) * DAY_IN_SECONDS );
			$source    = self::TIER_SOURCES[ ( $seq * 3 ) % count( self::TIER_SOURCES ) ];
			$label     = ucfirst( $level ) . ' Tier';
			$rows[]    = $wpdb->prepare( '(%d,%s,%d,%s,%s,%s,%s)', $uid, $level, $points, $expires, $activated, $source, $label );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, membership_level, points_balance, membership_expires_at, membership_activated_at, tier_source, custom_tier_label) VALUES " . implode( ',', $rows ) );
		}
	}

	private static function insert_flat_activity( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_activity";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$login_count = mt_rand( 1, 500 );
			$last_active = gmdate( 'Y-m-d H:i:s', time() - mt_rand( 0, 30 * DAY_IN_SECONDS ) );
			$last_login  = gmdate( 'Y-m-d H:i:s', time() - mt_rand( 0, 7 * DAY_IN_SECONDS ) );
			$last_order  = gmdate( 'Y-m-d H:i:s', time() - mt_rand( 0, 90 * DAY_IN_SECONDS ) );
			$sessions    = mt_rand( 0, 200 );
			$flags       = mt_rand( 0, 7 );
			$rows[]      = $wpdb->prepare( '(%d,%d,%s,%s,%s,%d,%d)', $uid, $login_count, $last_active, $last_login, $last_order, $sessions, $flags );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, login_count, last_active_at, last_login_at, last_order_at, session_count, account_flags) VALUES " . implode( ',', $rows ) );
		}
	}

	private static function insert_flat_profile( array $seq_to_id, array $user_data ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_profile";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$d              = $user_data[ $seq ];
			$num_specs      = ( $seq % 3 ) + 1;
			$specs_array    = array_slice( self::SPECIALTIES_POOL, $seq % 7, $num_specs );
			$specialties    = wp_json_encode( $specs_array );
			$bio_url        = 'https://example.com/' . $d['login'];
			$avatar_url     = 'https://example.com/avatars/' . $d['login'] . '.jpg';
			$display_custom = $d['display'];
			$locale         = self::LOCALES[ $seq % count( self::LOCALES ) ];
			$rows[]         = $wpdb->prepare( '(%d,%s,%s,%s,%s,%s)', $uid, $specialties, $bio_url, $avatar_url, $display_custom, $locale );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, specialties, bio_url, avatar_url, display_name_custom, locale) VALUES " . implode( ',', $rows ) );
		}
	}

	private static function insert_flat_sso( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_sso";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$hub_id      = 'hub_' . wp_generate_password( 16, false );
			$picture     = 'https://example.com/sso/' . $uid . '.jpg';
			$token_hash  = hash( 'sha256', 'token_' . $uid );
			$refresh_enc = 'enc:v1:' . base64_encode( random_bytes( 32 ) );
			$expires     = gmdate( 'Y-m-d H:i:s', time() + 3600 );
			$last_login  = gmdate( 'Y-m-d H:i:s', time() - mt_rand( 0, 7 * DAY_IN_SECONDS ) );
			$count       = mt_rand( 1, 100 );
			$rows[]      = $wpdb->prepare( '(%d,%s,%s,%s,%s,%s,%s,%d)', $uid, $hub_id, $picture, $token_hash, $refresh_enc, $expires, $last_login, $count );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, hub_global_user_id, picture_url, last_id_token_hash, refresh_token_enc, token_expires_at, sso_last_login_at, sso_login_count) VALUES " . implode( ',', $rows ) );
		}
	}

	private static function insert_flat_cold( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_cold";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$picture = 'https://cdn.example.com/' . $uid . '.png';
			$tok     = 'tok_legacy_' . wp_generate_password( 32, false );
			$ref     = 'ref_legacy_' . wp_generate_password( 32, false );
			$rows[]  = $wpdb->prepare( '(%d,%s,%s,%s)', $uid, $picture, $tok, $ref );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, _tmso_picture_url, _tmso_last_id_token, _tmso_refresh_token) VALUES " . implode( ',', $rows ) );
		}
	}

	/**
	 * v2.7.0 core_profile — nickname/first_name/last_name/description.
	 *
	 * @since 2.8.2
	 */
	private static function insert_flat_core_profile( array $seq_to_id, array $user_data ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_core_profile";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$d           = $user_data[ $seq ];
			$nickname    = $d['login'];
			$first       = $d['first'];
			$last        = $d['last'];
			$description = self::BIO_POOL[ $seq % count( self::BIO_POOL ) ] . ' #' . $seq;
			$rows[]      = $wpdb->prepare( '(%d,%s,%s,%s,%s)', $uid, $nickname, $first, $last, $description );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, nickname, first_name, last_name, description) VALUES " . implode( ',', $rows ) );
		}
	}

	/**
	 * v2.7.0 social — 15 social profile URLs (textarea per H2 fix).
	 *
	 * @since 2.8.2
	 */
	private static function insert_flat_social( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_social";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$cols    = array_merge( array( 'user_id' ), self::SOCIAL_KEYS );
		$col_sql = implode( ',', array_map( fn( $c ) => "`{$c}`", $cols ) );

		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$values  = array( $uid );
			$formats = array( '%d' );
			foreach ( self::SOCIAL_KEYS as $i => $key ) {
				// Populate ~3 of the 15 social URLs per user — realistic for vendor profiles.
				if ( ( $seq + $i ) % 5 === 0 ) {
					$values[]  = sprintf( 'https://%s.example.com/%s', $key, $uid );
					$formats[] = '%s';
				} else {
					$values[]  = null;
					$formats[] = '%s';
				}
			}
			$rows[] = $wpdb->prepare( '(' . implode( ',', $formats ) . ')', ...$values );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` ({$col_sql}) VALUES " . implode( ',', $rows ) );
		}
	}

	/**
	 * v2.7.0 commerce — WooCommerce billing + shipping address fields.
	 *
	 * @since 2.8.2
	 */
	private static function insert_flat_commerce( array $seq_to_id, array $user_data ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_commerce";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}

		$all_keys = array_merge( self::BILLING_KEYS, self::SHIPPING_KEYS );
		$cols     = array_merge( array( 'user_id' ), $all_keys );
		$col_sql  = implode( ',', array_map( fn( $c ) => "`{$c}`", $cols ) );

		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$d       = $user_data[ $seq ];
			$first   = $d['first'];
			$last    = $d['last'];
			$country = self::COUNTRY_POOL[ $seq % count( self::COUNTRY_POOL ) ];
			$state   = self::STATE_POOL[ $seq % count( self::STATE_POOL ) ];
			$city    = self::CITY_POOL[ $seq % count( self::CITY_POOL ) ];
			$company = 'Test Co. #' . $seq;
			$addr1   = sprintf( '%d %s St.', ( $seq * 13 ) % 9999 + 1, $city );
			$addr2   = ( $seq % 3 === 0 ) ? sprintf( 'Apt %d', $seq % 100 ) : '';
			$post    = sprintf( '%05d', ( $seq * 7 ) % 99999 );
			$email   = $d['login'] . '@' . self::TEST_EMAIL_DOMAIN;
			$phone   = sprintf( '+886-%d-%07d', mt_rand( 2, 9 ), mt_rand( 1000000, 9999999 ) );

			$values  = array( $uid );
			$formats = array( '%d' );

			// billing block
			$billing_values = array(
				'billing_first_name' => $first,
				'billing_last_name'  => $last,
				'billing_company'    => $company,
				'billing_address_1'  => $addr1,
				'billing_address_2'  => $addr2,
				'billing_city'       => $city,
				'billing_state'      => $state,
				'billing_postcode'   => $post,
				'billing_country'    => $country,
				'billing_email'      => $email,
				'billing_phone'      => $phone,
			);
			foreach ( self::BILLING_KEYS as $k ) {
				$values[]  = $billing_values[ $k ];
				$formats[] = '%s';
			}

			// shipping block — 60% of users have shipping = billing, rest different
			$same_address = ( $seq % 5 ) < 3;
			foreach ( self::SHIPPING_KEYS as $k ) {
				$billing_equiv = str_replace( 'shipping_', 'billing_', $k );
				if ( isset( $billing_values[ $billing_equiv ] ) && $same_address ) {
					$values[] = $billing_values[ $billing_equiv ];
				} else {
					$values[] = $billing_values[ $billing_equiv ] ?? '';
				}
				$formats[] = '%s';
			}

			$rows[] = $wpdb->prepare( '(' . implode( ',', $formats ) . ')', ...$values );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` ({$col_sql}) VALUES " . implode( ',', $rows ) );
		}
	}

	/**
	 * v2.7.0 hp_user — HivePress favorites (json array) + avatar attachment.
	 *
	 * @since 2.8.2
	 */
	private static function insert_flat_hp_user( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_hp_user";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$rows = array();
		foreach ( $seq_to_id as $seq => $uid ) {
			$num_favs = $seq % 5; // 0..4 favorites
			$favs     = array();
			for ( $i = 0; $i < $num_favs; $i++ ) {
				$favs[] = 1700 + ( ( $seq + $i ) % 60 ); // listing IDs 1700-1759
			}
			$favs_json = wp_json_encode( $favs );
			$hp_image  = ( $seq % 4 === 0 ) ? (string) ( 2000 + ( $seq % 100 ) ) : '';
			$rows[]    = $wpdb->prepare( '(%d,%s,%s)', $uid, $favs_json, $hp_image );
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, hp_favorited_listings, hp_image) VALUES " . implode( ',', $rows ) );
		}
	}

	private static function insert_points_ledger( array $seq_to_id ): void {
		global $wpdb;
		$tbl = "{$wpdb->prefix}wpdo_user_points_ledger";
		if ( ! self::table_exists( $tbl ) ) {
			return;
		}
		$reasons = array( 'signup_bonus', 'order_reward', 'referral', 'promo', 'manual_adjust' );
		$rows    = array();
		$created = current_time( 'mysql', true );
		foreach ( $seq_to_id as $seq => $uid ) {
			$num_entries = ( $seq % 3 ) + 1; // 1-3 筆
			$balance     = 0;
			for ( $i = 0; $i < $num_entries; $i++ ) {
				$delta    = mt_rand( -200, 1000 );
				$balance += $delta;
				$reason   = $reasons[ ( $seq + $i ) % count( $reasons ) ];
				$rows[]   = $wpdb->prepare(
					'(%d,%d,%d,%s,%d,%s,%s)',
					$uid,
					$delta,
					$balance,
					$reason,
					0,
					'',
					$created
				);
			}
		}
		if ( ! empty( $rows ) ) {
			$wpdb->query( "INSERT INTO `{$tbl}` (user_id, delta, balance_after, reason, ref_id, ref_type, created_at) VALUES " . implode( ',', $rows ) );
		}
	}

	// ─────────────────────────────────────────────────────────
	// 批次寫入：Realistic Mode（走 wp_insert_user + update_user_meta）
	// ─────────────────────────────────────────────────────────

	private static function run_batch_realistic( int $count ): int {
		// Realistic mode：走 wp_insert_user + update_user_meta，每個 user 1-3 秒。
		// 加 wall-clock deadline，避免 PHP-FPM / nginx 504 Gateway Timeout（預設 60 秒）。
		$deadline = microtime( true ) + self::BATCH_DEADLINE_SEC;
		$next_seq = self::next_test_user_seq();
		$inserted = 0;
		for ( $i = 0; $i < $count; $i++ ) {
			if ( microtime( true ) > $deadline ) {
				break; // 超時：把已完成的回傳，剩餘讓下一個 pump/cron 接手
			}
			// 使用者點 cancel：每個 user 開始前檢查 — realistic mode 每 user ~3s，
			// 最壞情況 cancel 後 3 秒內即可中止
			if ( false !== get_transient( self::CANCEL_FLAG ) ) {
				break;
			}

			$seq   = $next_seq + $i;
			$login = self::TEST_USER_PREFIX . $seq;
			$email = $login . '@' . self::TEST_EMAIL_DOMAIN;
			$first = self::FIRST_NAMES[ $seq % count( self::FIRST_NAMES ) ];
			$last  = self::LAST_NAMES[ ( $seq * 7 ) % count( self::LAST_NAMES ) ];

			$uid = wp_insert_user(
				array(
					'user_login'   => $login,
					'user_pass'    => self::TEST_PASSWORD,
					'user_email'   => $email,
					'first_name'   => $first,
					'last_name'    => $last,
					'display_name' => $first . ' ' . $last . ' #' . $seq,
					'role'         => 'subscriber',
				)
			);
			if ( is_wp_error( $uid ) ) {
				continue;
			}
			++$inserted;

			// 走 update_user_meta，讓 Hook Bus 自然攔截到 flat tables
			$meta = self::generate_realistic_meta( $seq );
			foreach ( $meta as $key => $value ) {
				update_user_meta( $uid, $key, $value );
			}
		}
		return $inserted;
	}

	private static function generate_realistic_meta( int $seq ): array {
		$first    = self::FIRST_NAMES[ $seq % count( self::FIRST_NAMES ) ];
		$last     = self::LAST_NAMES[ ( $seq * 7 ) % count( self::LAST_NAMES ) ];
		$login    = self::TEST_USER_PREFIX . $seq;
		$country  = self::COUNTRY_POOL[ $seq % count( self::COUNTRY_POOL ) ];
		$city     = self::CITY_POOL[ $seq % count( self::CITY_POOL ) ];
		$num_favs = $seq % 5;
		$favs     = array();
		for ( $i = 0; $i < $num_favs; $i++ ) {
			$favs[] = 1700 + ( ( $seq + $i ) % 60 );
		}

		return array(
			// hot
			'_money_spent'            => mt_rand( 0, 1000000 ) / 100,
			'_order_count'            => mt_rand( 0, 50 ),
			'_last_order'             => time() - mt_rand( 0, 365 * DAY_IN_SECONDS ),
			// membership
			'membership_level'        => self::MEMBERSHIP_LEVELS[ $seq % count( self::MEMBERSHIP_LEVELS ) ],
			'points_balance'          => mt_rand( 0, 50000 ),
			'membership_expires_at'   => gmdate( 'Y-m-d H:i:s', time() + mt_rand( 30, 730 ) * DAY_IN_SECONDS ),
			'membership_activated_at' => gmdate( 'Y-m-d H:i:s', time() - mt_rand( 1, 365 ) * DAY_IN_SECONDS ),
			'tier_source'             => self::TIER_SOURCES[ ( $seq * 3 ) % count( self::TIER_SOURCES ) ],
			// activity
			'login_count'             => mt_rand( 1, 500 ),
			'last_active_at'          => gmdate( 'Y-m-d H:i:s', time() - mt_rand( 0, 30 * DAY_IN_SECONDS ) ),
			'last_login_at'           => gmdate( 'Y-m-d H:i:s', time() - mt_rand( 0, 7 * DAY_IN_SECONDS ) ),
			'session_count'           => mt_rand( 0, 200 ),
			// profile
			'specialties'             => array_slice( self::SPECIALTIES_POOL, $seq % 7, ( $seq % 3 ) + 1 ),
			'bio_url'                 => 'https://example.com/test' . $seq,
			'locale'                  => self::LOCALES[ $seq % count( self::LOCALES ) ],
			// sso
			'hub_global_user_id'      => 'hub_' . wp_generate_password( 16, false ),
			'sso_login_count'         => mt_rand( 1, 100 ),
			// v2.7.0 core_profile (note: nickname/first_name/last_name are also
			// set via wp_insert_user, but description is unique to this group)
			'description'             => self::BIO_POOL[ $seq % count( self::BIO_POOL ) ] . ' #' . $seq,
			// v2.7.0 social — populate 3 of 15 to mimic real vendor profile usage
			'facebook'                => sprintf( 'https://facebook.com/%s', $login ),
			'instagram'               => sprintf( 'https://instagram.com/%s', $login ),
			'youtube'                 => sprintf( 'https://youtube.com/@%s', $login ),
			// v2.7.0 commerce (subset; 5 representative billing fields)
			'billing_first_name'      => $first,
			'billing_last_name'       => $last,
			'billing_email'           => $login . '@' . self::TEST_EMAIL_DOMAIN,
			'billing_country'         => $country,
			'billing_city'            => $city,
			// v2.7.0 hp_user
			'hp_favorited_listings'   => $favs,
			'hp_image'                => ( $seq % 4 === 0 ) ? (string) ( 2000 + ( $seq % 100 ) ) : '',
		);
	}

	// ─────────────────────────────────────────────────────────
	// Benchmark
	// ─────────────────────────────────────────────────────────

	private static function finalize( array $state ): void {
		// 清掉殘留的 cron event（避免完成後仍有過期 event）
		wp_clear_scheduled_hook( self::CRON_HOOK );

		$state['status']       = 'benchmarking';
		$state['completed_at'] = time();
		update_option( self::OPT_STATE, $state, false );

		$report = self::run_benchmark( $state );

		$state['benchmark'] = $report;
		$state['status']    = 'completed';
		update_option( self::OPT_STATE, $state, false );
	}

	/**
	 * 執行 benchmark：寫入指標 + DB 容量 + 查詢效能。
	 */
	public static function run_benchmark( ?array $state = null ): array {
		global $wpdb;
		$state = $state ?? self::get_state();

		// 1. 寫入指標
		$write_metrics = self::compute_write_metrics( $state );

		// 2. DB 容量
		$db_sizes = self::measure_db_sizes();

		// 3. 查詢效能
		$query_perf = self::measure_query_performance();

		return array(
			'generated_at' => time(),
			'write'        => $write_metrics,
			'db_sizes'     => $db_sizes,
			'query'        => $query_perf,
		);
	}

	private static function compute_write_metrics( array $state ): array {
		$started   = (int) ( $state['started_at'] ?? 0 );
		$ended     = (int) ( $state['completed_at'] ?? time() );
		$processed = (int) ( $state['processed'] ?? 0 );
		$elapsed   = max( 1, $ended - $started );
		$batches   = $state['batches_log'] ?? array();

		$durations = array_column( $batches, 'duration_ms' );
		$min_ms    = ! empty( $durations ) ? min( $durations ) : 0;
		$max_ms    = ! empty( $durations ) ? max( $durations ) : 0;
		$avg_ms    = ! empty( $durations ) ? (int) ( array_sum( $durations ) / count( $durations ) ) : 0;

		return array(
			'mode'           => $state['mode'] ?? '',
			'target'         => (int) ( $state['target'] ?? 0 ),
			'processed'      => $processed,
			'elapsed_sec'    => $elapsed,
			'rate_per_sec'   => round( $processed / $elapsed, 2 ),
			'batches_done'   => (int) ( $state['batches_done'] ?? 0 ),
			'batch_min_ms'   => $min_ms,
			'batch_max_ms'   => $max_ms,
			'batch_avg_ms'   => $avg_ms,
			'peak_memory_mb' => round( (int) ( $state['peak_memory'] ?? 0 ) / 1048576, 1 ),
		);
	}

	private static function measure_db_sizes(): array {
		global $wpdb;
		$tables       = array_merge( array( $wpdb->users, $wpdb->usermeta ), self::get_user_flat_tables() );
		$placeholders = implode( ',', array_fill( 0, count( $tables ), '%s' ) );
		// MySQL only — SQLite 不支援 information_schema.TABLES，跳過
		if ( ! self::is_mysql() ) {
			return array_map(
				fn( $t ) => array(
					'table'   => $t,
					'rows'    => self::table_row_count( $t ),
					'size_mb' => null,
				),
				$tables
			);
		}

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT TABLE_NAME AS t, TABLE_ROWS AS rows_count, DATA_LENGTH AS dl, INDEX_LENGTH AS il
				 FROM information_schema.TABLES
				 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$placeholders})",
				...$tables
			),
			ARRAY_A
		);

		$out = array();
		foreach ( $rows as $r ) {
			$out[] = array(
				'table'     => $r['t'],
				'rows'      => (int) $r['rows_count'],
				'data_mb'   => round( $r['dl'] / 1048576, 2 ),
				'index_mb'  => round( $r['il'] / 1048576, 2 ),
				'total_mb'  => round( ( $r['dl'] + $r['il'] ) / 1048576, 2 ),
				'avg_bytes' => $r['rows_count'] > 0 ? (int) ( ( $r['dl'] + $r['il'] ) / $r['rows_count'] ) : 0,
			);
		}
		return $out;
	}

	private static function measure_query_performance(): array {
		global $wpdb;

		$sample_ids = $wpdb->get_col(
			"SELECT ID FROM {$wpdb->users} WHERE user_login LIKE '" . esc_sql( self::TEST_USER_PREFIX ) . "%' ORDER BY ID DESC LIMIT 100"
		);
		$sample_ids = array_map( 'intval', $sample_ids );

		if ( empty( $sample_ids ) ) {
			return array( 'note' => 'no_test_users_found' );
		}

		$results = array();

		// Q1: 讀單一 meta（透過 Hook Bus / get_user_meta 攔截路徑），走 flat table
		$results['get_field_membership_level'] = self::time_calls(
			function () use ( $sample_ids ) {
				foreach ( $sample_ids as $id ) {
					get_user_meta( $id, 'membership_level', true );
				}
			},
			count( $sample_ids )
		);

		// Q2: 讀多個 meta key（模擬整 entity 讀取，覆蓋多個 group）
		$multi_keys                 = array(
			'membership_level',  // membership group
			'points_balance',    // membership
			'login_count',       // activity
			'last_active_at',    // activity
			'_money_spent',      // hot
			'specialties',       // profile
			'locale',            // profile
		);
		$results['get_entity_full'] = self::time_calls(
			function () use ( $sample_ids, $multi_keys ) {
				foreach ( $sample_ids as $id ) {
					foreach ( $multi_keys as $key ) {
						get_user_meta( $id, $key, true );
					}
				}
			},
			count( $sample_ids )
		);

		// Q3: 索引範圍查詢 — gold 等級且 points > 5000 的用戶總數
		$tbl = $wpdb->prefix . 'wpdo_user_membership';
		if ( self::table_exists( $tbl ) ) {
			$results['range_gold_high_points'] = self::time_query(
				"SELECT COUNT(*) FROM `{$tbl}` WHERE membership_level = 'gold' AND points_balance > 5000"
			);
		}

		// Q4: 排序查詢 — last_active_at DESC LIMIT 100
		$tbl = $wpdb->prefix . 'wpdo_user_activity';
		if ( self::table_exists( $tbl ) ) {
			$results['sort_recent_active_100'] = self::time_query(
				"SELECT user_id, last_active_at FROM `{$tbl}` ORDER BY last_active_at DESC LIMIT 100"
			);
		}

		// Q5: JOIN — top 100 most active gold members
		$mtbl = $wpdb->prefix . 'wpdo_user_membership';
		$atbl = $wpdb->prefix . 'wpdo_user_activity';
		if ( self::table_exists( $mtbl ) && self::table_exists( $atbl ) ) {
			$results['join_top_gold_active'] = self::time_query(
				"SELECT m.user_id, m.points_balance, a.login_count
				 FROM `{$mtbl}` m
				 JOIN `{$atbl}` a ON a.user_id = m.user_id
				 WHERE m.membership_level = 'gold'
				 ORDER BY a.last_active_at DESC LIMIT 100"
			);
		}

		// Q6 baseline：原生 EAV 等價查詢（usermeta 範圍查詢）
		$results['eav_range_baseline'] = self::time_query(
			"SELECT COUNT(*) FROM {$wpdb->usermeta} m1
			 JOIN {$wpdb->usermeta} m2 ON m1.user_id = m2.user_id
			 WHERE m1.meta_key = 'membership_level' AND m1.meta_value = 'gold'
			 AND m2.meta_key = 'points_balance' AND CAST(m2.meta_value AS UNSIGNED) > 5000"
		);

		// v2.8.2 — exercise v2.7.0 groups too:

		// Q7: billing_email indexed lookup (commerce group, the only searchable
		// commerce field; representative WC customer-search workload)
		$ctbl = $wpdb->prefix . 'wpdo_user_commerce';
		if ( self::table_exists( $ctbl ) ) {
			$sample_email                     = self::TEST_USER_PREFIX . ( $sample_ids[0] ?? 1 ) . '@' . self::TEST_EMAIL_DOMAIN;
			$results['commerce_email_lookup'] = self::time_query(
				$wpdb->prepare(
					"SELECT user_id FROM `{$ctbl}` WHERE billing_email = %s LIMIT 1",
					$sample_email
				)
			);
		}

		// Q8: core_profile fulltext-ish search (display_name lookup pattern)
		$cptbl = $wpdb->prefix . 'wpdo_user_core_profile';
		if ( self::table_exists( $cptbl ) ) {
			$results['core_profile_first_name_scan'] = self::time_query(
				"SELECT user_id, first_name FROM `{$cptbl}` WHERE first_name = 'Alice' LIMIT 100"
			);
		}

		// Q9: hp_user JSON read (favorites count distribution — N+1 pattern through Hook Bus)
		$hpttbl = $wpdb->prefix . 'wpdo_user_hp_user';
		if ( self::table_exists( $hpttbl ) ) {
			$results['hp_favorites_full_scan'] = self::time_query(
				"SELECT user_id, hp_favorited_listings FROM `{$hpttbl}` LIMIT 100"
			);
		}

		// Q10: simulate a realistic "render a vendor profile" — read 1 user across
		// ALL 9 groups via Hook Bus (=10 get_user_meta calls hitting flat tables)
		if ( ! empty( $sample_ids ) ) {
			$probe_keys                              = array(
				'membership_level',
				'points_balance',                       // membership
				'last_active_at',
				'login_count',                            // activity
				'specialties',
				'locale',                                    // profile
				'hub_global_user_id',
				'sso_login_count',                    // sso
				'nickname',
				'first_name',
				'last_name',
				'description',       // core_profile
				'facebook',
				'instagram',
				'youtube',                         // social
				'billing_email',
				'billing_country',
				'billing_first_name',   // commerce
				'hp_favorited_listings',
				'hp_image',                        // hp_user
			);
			$results['hook_bus_full_profile_render'] = self::time_calls(
				function () use ( $sample_ids, $probe_keys ) {
					$first_id = $sample_ids[0];
					foreach ( $probe_keys as $key ) {
						get_user_meta( $first_id, $key, true );
					}
				},
				count( $probe_keys )
			);
		}

		return $results;
	}

	private static function time_calls( callable $cb, int $n ): array {
		$start = microtime( true );
		$cb();
		$elapsed_ms = ( microtime( true ) - $start ) * 1000;
		return array(
			'n'        => $n,
			'total_ms' => round( $elapsed_ms, 2 ),
			'avg_ms'   => $n > 0 ? round( $elapsed_ms / $n, 3 ) : 0,
			'qps'      => $elapsed_ms > 0 ? round( $n / ( $elapsed_ms / 1000 ), 1 ) : 0,
		);
	}

	private static function time_query( string $sql ): array {
		global $wpdb;
		$start = microtime( true );
		$wpdb->get_results( $sql );
		$elapsed_ms = ( microtime( true ) - $start ) * 1000;
		return array(
			'duration_ms' => round( $elapsed_ms, 2 ),
		);
	}

	// ─────────────────────────────────────────────────────────
	// 輔助
	// ─────────────────────────────────────────────────────────

	private static function get_user_flat_tables(): array {
		global $wpdb;
		return array_filter(
			array_map(
				fn( $name ) => $wpdb->prefix . 'wpdo_user_' . $name,
				array(
					// v2.5.x groups
					'hot',
					'cold',
					'membership',
					'activity',
					'profile',
					'sso',
					'points_ledger',
					// v2.7.0 groups
					'core_profile',
					'social',
					'commerce',
					'hp_user',
					// v2.8.4 group
					'admin_prefs',
				)
			),
			array( __CLASS__, 'table_exists' )
		);
	}

	private static function table_exists( string $table ): bool {
		global $wpdb;
		static $cache = array();
		if ( isset( $cache[ $table ] ) ) {
			return $cache[ $table ];
		}
		$found           = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
		$cache[ $table ] = ( $found === $table );
		return $cache[ $table ];
	}

	private static function table_row_count( string $table ): int {
		global $wpdb;
		return self::table_exists( $table ) ? (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" ) : 0;
	}

	private static function is_mysql(): bool {
		return ! ( class_exists( 'WP_SQLite_DB' ) || class_exists( 'WP_SQLite_Translator' ) || class_exists( 'WP_SQLite_Driver' ) );
	}
}