<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-39.php
 *
 * Migration 4.39 -- Add series_number to mbs_format_rows
 *
 * The series_number column (DECIMAL(5,2)) was added to the mbs_format_rows
 * base schema DDL in Session 224, but dbDelta() only runs on fresh installs.
 * Existing databases need this ALTER TABLE to pick up the column so that the
 * MyBookTable importer's save_mbt_format_rows() can write series_number values
 * and the ONIX Reconciler tests can pass.
 *
 * Guard: INFORMATION_SCHEMA.COLUMNS check prevents double-application.
 * The migration is fully idempotent.
 *
 * @package MyBookShowroom
 * @since   4.39.0
 */

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

/**
 * Migration 4.39: add series_number DECIMAL(5,2) column to mbs_format_rows.
 */
// 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_39 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.39'; }

	/**
	 * Run the migration.
	 *
	 * Adds series_number DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL to
	 * mbs_format_rows if the column does not already exist.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

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

		$exists = (int) $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.COLUMNS
				  WHERE table_schema = DATABASE()
				    AND table_name   = %s
				    AND column_name  = 'series_number'",
				$table
			)
		);

		if ( 0 === $exists ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
				"ALTER TABLE `{$table}`
				   ADD COLUMN series_number DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL
				   AFTER isbn13"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}
	}
}
