<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-18.php
 *
 * Migration 4.18 — Index coverage: activity_log, book_stats, arc_applicants
 *
 * Sprint 163-B index audit found three custom tables with query-pattern gaps:
 *
 *   mbs_activity_log    — get_filtered() uses WHERE action = %s and WHERE user_id = %d;
 *                         neither column has a covering index.
 *   mbs_book_stats      — get_needing_review() uses WHERE review_count >= %d;
 *                         review_count column has no index.
 *   mbs_arc_applicants  — get_pending_for_book() uses WHERE post_id = %d AND status = 'pending';
 *                         no composite index on (post_id, status).
 *
 * Each ALTER TABLE is guarded by an information_schema.STATISTICS check so the
 * migration is idempotent and safe to replay.
 *
 * No data backfill required — index additions do not change stored values.
 *
 * @package MyBookShowroom
 * @since   2.5.0
 */

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

/**
 * Migration 4.18: add missing indexes to activity_log, book_stats, arc_applicants.
 */
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_Migration_4_18 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * @return string Migration version string.
	 */
	public function version(): string {
		return '4.18';
	}

	/**
	 * Run the migration.
	 *
	 * Each index addition is wrapped in an information_schema guard so repeated
	 * calls are no-ops. Safe to replay without side effects.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		// -----------------------------------------------------------------
		// mbs_activity_log — idx_action (action)
		// -----------------------------------------------------------------
		$table = $wpdb->prefix . 'mbs_activity_log';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT COUNT(*) FROM information_schema.STATISTICS
				 WHERE table_schema = DATABASE()
				   AND table_name   = %s
				   AND index_name   = 'idx_action'",
				$table
			)
		);
		if ( (int) $exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD KEY idx_action (action)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -----------------------------------------------------------------
		// mbs_activity_log — idx_user_id (user_id)
		// -----------------------------------------------------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT COUNT(*) FROM information_schema.STATISTICS
				 WHERE table_schema = DATABASE()
				   AND table_name   = %s
				   AND index_name   = 'idx_user_id'",
				$table
			)
		);
		if ( (int) $exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD KEY idx_user_id (user_id)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -----------------------------------------------------------------
		// mbs_book_stats — idx_review_count (review_count)
		// -----------------------------------------------------------------
		$table = $wpdb->prefix . 'mbs_book_stats';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT COUNT(*) FROM information_schema.STATISTICS
				 WHERE table_schema = DATABASE()
				   AND table_name   = %s
				   AND index_name   = 'idx_review_count'",
				$table
			)
		);
		if ( (int) $exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD KEY idx_review_count (review_count)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -----------------------------------------------------------------
		// mbs_arc_applicants — idx_post_status (post_id, status)
		// -----------------------------------------------------------------
		$table = $wpdb->prefix . 'mbs_arc_applicants';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT COUNT(*) FROM information_schema.STATISTICS
				 WHERE table_schema = DATABASE()
				   AND table_name   = %s
				   AND index_name   = 'idx_post_status'",
				$table
			)
		);
		if ( (int) $exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD KEY idx_post_status (post_id, status)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}
	}
}
