<?php
/**
 * Add an index on user_id to the aisentic_conversations table.
 *
 * @package Aisentic
 */

defined( 'ABSPATH' ) || exit;

use Aisentic\Database\Migration;

/**
 * Adds a composite index (user_id, updated_at) to aisentic_conversations.
 *
 * Conversation_Repository::list() filters by user_id and orders by updated_at
 * DESC; the original create-table migration declared user_id with no key,
 * forcing a full table scan + filesort on every listing. This composite index
 * covers both the WHERE filter and the ORDER BY.
 */
// phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- Migration_Runner binds the class name to the filename; renaming would re-run the migration.
class Add_User_Id_Index_To_Conversations extends Migration {
	/**
	 * Serial number for execution order. Runs after the table exists (serial 1).
	 *
	 * @var int
	 */
	protected int $serial = 3;

	/**
	 * Plugin version when this migration was written.
	 *
	 * @var string
	 */
	protected string $version = '1.0.2';

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

		$table = $wpdb->prefix . 'aisentic_conversations';
		$index = 'user_id_updated_at';

		// Idempotency guard: ADD INDEX errors on a duplicate key name, and the
		// index may already exist where it was added manually/out-of-band.
		$exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", $index ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name from trusted $wpdb->prefix; $index bound via prepare().

		if ( null === $exists ) {
			$wpdb->query( "ALTER TABLE `{$table}` ADD INDEX `{$index}` (`user_id`, `updated_at`)" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/index names from trusted, non-user sources.
		}
	}

	/**
	 * Reverse the migration.
	 *
	 * @return void
	 */
	public function down() {
		global $wpdb;

		$table = $wpdb->prefix . 'aisentic_conversations';
		$index = 'user_id_updated_at';

		$exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM `{$table}` WHERE Key_name = %s", $index ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name from trusted $wpdb->prefix; $index bound via prepare().

		if ( null !== $exists ) {
			$wpdb->query( "ALTER TABLE `{$table}` DROP INDEX `{$index}`" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table/index names from trusted, non-user sources.
		}
	}
}