<?php
/**
 * File Path: includes/migrations/class-mbs-migration-3-8.php
 *
 * MBS_Migration_3_8 -- Schema 3.8
 *
 * Adds two per-format edition columns to mbs_format_rows so that edition
 * metadata can differ across formats (e.g. paperback = 2nd edition, hardcover
 * = 1st edition):
 *
 *   edition_number  TINYINT UNSIGNED -- ordinal edition number (1, 2, 3 ...)
 *   edition_type    VARCHAR(10)      -- ONIX List 21 code (e.g. "REV", "ILL")
 *
 * The book-level post_meta keys mbs_edition_number and mbs_edition_type are
 * intentionally left in place. Code after this migration simply ignores them;
 * no data copy is performed ("start fresh" per Gregg, 2026-05-03 Sprint S110).
 *
 * The `edition` (Edition Statement) column added in 3.7 is retained in the
 * schema but removed from the UI. Existing data is preserved.
 *
 * Each ALTER TABLE is guarded by an INFORMATION_SCHEMA existence check so the
 * migration is idempotent and safe to re-run.
 *
 * @package MyBookShowroom
 * @since   3.8.0
 */

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

/**
 * Class MBS_Migration_3_8
 *
 * @implements MBS_Migration
 */
// 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_3_8 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	public function version(): string {
		return '3.8';
	}

	public function up(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_format_rows';

		// ── Add edition_number column (idempotent) ────────────────────────────
		$num_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  = 'edition_number'",
				$table
			)
		);

		if ( ! $num_exists ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from $wpdb->prefix; DDL is static.
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN edition_number TINYINT UNSIGNED NULL DEFAULT NULL AFTER edition" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// ── Add edition_type column (idempotent) ──────────────────────────────
		$type_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  = 'edition_type'",
				$table
			)
		);

		if ( ! $type_exists ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- table name from $wpdb->prefix; DDL is static.
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN edition_type VARCHAR(10) NULL DEFAULT NULL AFTER edition_number" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}
	}
}
