<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-37.php
 *
 * Migration 4.37 -- UX-RO-002:
 * Add universe_reading_order_position DECIMAL(10,2) NULL column to
 * mbs_book_meta. Enables per-book universe reading-order position storage,
 * matching the series_reading_order_position pattern (migration 4.34).
 *
 * Idempotent: SHOW COLUMNS guard prevents duplicate ALTER TABLE.
 *
 * @package MyBookShowroom
 * @since   4.37.0
 */

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

// 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_37 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.37'; }

	/**
	 * Run the migration.
	 *
	 * Adds universe_reading_order_position DECIMAL(10,2) NULL DEFAULT NULL
	 * to mbs_book_meta, positioned after universe_reading_order_hidden.
	 * Adds an index for ORDER BY queries. Both steps are idempotent.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		$bm_table = $wpdb->prefix . 'mbs_book_meta';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$col_exists = $wpdb->get_var( "SHOW COLUMNS FROM `{$bm_table}` LIKE 'universe_reading_order_position'" );
		if ( ! $col_exists ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				"ALTER TABLE `{$bm_table}`
				  ADD COLUMN universe_reading_order_position DECIMAL(10,2) NULL DEFAULT NULL
				    AFTER universe_reading_order_hidden"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "ALTER TABLE `{$bm_table}` ADD KEY idx_universe_ro_pos (universe_reading_order_position)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}
	}
}
