<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-38.php
 *
 * Migration 4.38 -- DB Index Optimization
 * (DB-001 / DB-002 / DB-003 / DB-005-cleanup / DB-006)
 *
 * Removes three redundant single-column indexes whose leftmost prefix
 * is fully covered by an existing UNIQUE KEY on the same table:
 *
 *   DB-001  mbs_pos_inventory.idx_location_id
 *           Covered by UNIQUE KEY idx_loc_format (location_id, format_row_id).
 *
 *   DB-002  mbs_author_social_links.idx_term_id
 *           Covered by UNIQUE KEY term_platform (term_id, platform).
 *
 *   DB-003  mbs_book_classification_codes.idx_post_id
 *           Covered by UNIQUE KEY idx_post_scheme_code (post_id, scheme, code).
 *
 * Removes one redundant index superseded by the composite covering index
 * added in migration 4.18:
 *
 *   DB-005-cleanup  mbs_arc_applicants.idx_post_id
 *                   Superseded by idx_post_status (post_id, status);
 *                   idx_post_id is a strict left-prefix subset.
 *
 * Normalises the isbn13 column width on mbs_book_meta (DB-006):
 *
 *   mbs_book_meta.isbn13 VARCHAR(20) -> VARCHAR(17), matching mbs_format_rows.
 *   ISBN-13 with hyphens is 17 chars max (000-0-000-00000-0).
 *   Guard: only executed when the column is still wider than 17.
 *
 * All steps are idempotent -- INFORMATION_SCHEMA guards prevent double-drops
 * or redundant ALTER TABLE calls.
 *
 * @package MyBookShowroom
 * @since   4.38.0
 */

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

/**
 * Migration 4.38: drop redundant indexes; normalize mbs_book_meta.isbn13 width.
 */
// 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_38 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.38'; }

	/**
	 * Run the migration.
	 *
	 * Each DROP INDEX is guarded by an INFORMATION_SCHEMA.STATISTICS check
	 * so the migration is safe to replay. The isbn13 VARCHAR change is
	 * guarded by an INFORMATION_SCHEMA.COLUMNS check.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		// -----------------------------------------------------------------
		// DB-001: DROP idx_location_id on mbs_pos_inventory
		// Redundant: UNIQUE KEY idx_loc_format (location_id, format_row_id)
		// covers all WHERE location_id = %d queries via leftmost prefix.
		// -----------------------------------------------------------------
		$this->drop_index_if_exists( $wpdb->prefix . 'mbs_pos_inventory', 'idx_location_id' );

		// -----------------------------------------------------------------
		// DB-002: DROP idx_term_id on mbs_author_social_links
		// Redundant: UNIQUE KEY term_platform (term_id, platform) covers it.
		// -----------------------------------------------------------------
		$this->drop_index_if_exists( $wpdb->prefix . 'mbs_author_social_links', 'idx_term_id' );

		// -----------------------------------------------------------------
		// DB-003: DROP idx_post_id on mbs_book_classification_codes
		// Redundant: UNIQUE KEY idx_post_scheme_code (post_id, scheme, code)
		// covers all WHERE post_id = %d queries via leftmost prefix.
		// -----------------------------------------------------------------
		$this->drop_index_if_exists( $wpdb->prefix . 'mbs_book_classification_codes', 'idx_post_id' );

		// -----------------------------------------------------------------
		// DB-005-cleanup: DROP idx_post_id on mbs_arc_applicants
		// Superseded by composite idx_post_status (post_id, status) added in
		// migration 4.18. idx_post_id is a strict left-prefix subset and
		// provides no query benefit that idx_post_status does not already cover.
		// -----------------------------------------------------------------
		$this->drop_index_if_exists( $wpdb->prefix . 'mbs_arc_applicants', 'idx_post_id' );

		// -----------------------------------------------------------------
		// DB-006: normalize mbs_book_meta.isbn13 to VARCHAR(17)
		// mbs_format_rows.isbn13 is VARCHAR(17); mbs_book_meta was VARCHAR(20).
		// Guard: only ALTER if the column is still wider than 17 characters.
		// -----------------------------------------------------------------
		$bm_table = $wpdb->prefix . 'mbs_book_meta';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col_length = $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 CHARACTER_MAXIMUM_LENGTH
				   FROM information_schema.COLUMNS
				  WHERE table_schema = DATABASE()
				    AND table_name   = %s
				    AND column_name  = 'isbn13'",
				$bm_table
			)
		);

		if ( (int) $col_length > 17 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "ALTER TABLE `{$bm_table}` MODIFY COLUMN isbn13 VARCHAR(17) NULL DEFAULT NULL" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}
	}

	/**
	 * Drop an index on a table if it currently exists.
	 *
	 * Uses INFORMATION_SCHEMA.STATISTICS to check for the index before
	 * issuing DROP INDEX. Safe to call when the index no longer exists.
	 *
	 * @param string $table      Fully-qualified table name (with prefix).
	 * @param string $index_name Index name to drop.
	 * @return void
	 */
	private function drop_index_if_exists( string $table, string $index_name ): void {
		global $wpdb;

		// 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   = %s",
				$table,
				$index_name
			)
		);

		if ( (int) $exists > 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "ALTER TABLE `{$table}` DROP INDEX `{$index_name}`" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}
	}
}
