<?php
/**
 * Add Email Verified Index Migration
 *
 * Adds a composite index on wp_usermeta (meta_key, meta_value) for the
 * _ambh_email_verified key so that ambassador list queries that filter
 * by verification status remain fast at scale.
 *
 * @package AmbassadorHub\Accounts\Migrations
 */

namespace AmbassadorHub\Accounts\Migrations;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange

/**
 * Adds idx_ambh_email_verified index to wp_usermeta.
 *
 * Uses a prefix index on meta_value(1) — sufficient since the only stored
 * values are '0' and '1'. Checks INFORMATION_SCHEMA first so the ALTER TABLE
 * is safe to run on any MySQL/MariaDB version without IF NOT EXISTS support.
 */
class AddEmailVerifiedIndex {

	/**
	 * Run the migration.
	 *
	 * @return void
	 */
	public static function up(): void {
		global $wpdb;

		$index_exists = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*)
				 FROM INFORMATION_SCHEMA.STATISTICS
				 WHERE table_schema = DATABASE()
				   AND table_name   = %s
				   AND index_name   = 'idx_ambh_email_verified'",
				$wpdb->usermeta
			)
		);

		if ( $index_exists ) {
			return;
		}

		$wpdb->query(
			"ALTER TABLE {$wpdb->usermeta}
			 ADD INDEX idx_ambh_email_verified (meta_key(32), meta_value(1))"
		);
	}
}
