<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-42.php
 *
 * Migration 4.42 -- Add idx_post_id to mbs_book_club_files (DAT-022b)
 *
 * The mbs_book_club_files schema (MBS_DB_Schema::book_club_files) defines
 * KEY idx_post_id (post_id), but dbDelta does not add indexes to existing
 * tables. On databases created before this key was added to the DDL the index
 * may be absent, causing get() calls on MBS_Book_Club_Files_DAO to perform
 * full table scans. This migration adds the index if absent.
 *
 * @package MyBookShowroom
 * @since   4.42.0
 */

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

/**
 * Migration 4.42: add KEY idx_post_id (post_id) to mbs_book_club_files if missing.
 */
// 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_42 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Returns the migration version string.
	 *
	 * @return string
	 */
	public function version(): string { return '4.42'; }

	/**
	 * Run the migration.
	 *
	 * Adds KEY idx_post_id (post_id) to mbs_book_club_files only when the index
	 * does not already exist. Guard uses information_schema.STATISTICS so the
	 * migration is safe on both MySQL 5.7 and MySQL 8.x.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		$table = $wpdb->prefix . 'mbs_book_club_files';

		$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_id'",
				$table
			)
		);

		if ( ! $exists ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "ALTER TABLE `{$table}` ADD KEY idx_post_id (post_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
		}
	}
}
