<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-32.php
 *
 * Migration 4.32 -- UX-NAV-001: add is_visible column to mbs_section_order;
 * delete stale goodreads and suggested-reading-order rows.
 *
 * @package MyBookShowroom
 * @since   1.2.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_32 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.32';
	}

	/**
	 * Run the migration.
	 *
	 * Adds is_visible TINYINT(1) NOT NULL DEFAULT 1 AFTER sort_order to the
	 * mbs_section_order table when the column does not yet exist. All existing
	 * rows receive is_visible = 1 automatically via the column DEFAULT.
	 *
	 * Also deletes stale goodreads and suggested-reading-order rows from
	 * mbs_section_order. Both DELETEs are idempotent (no-op if rows absent).
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_section_order';

		// Idempotency guard -- only ALTER if the column does not yet exist.
		$cols = $wpdb->get_col( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SHOW COLUMNS FROM {$table} LIKE %s",
			'is_visible'
		) );
		if ( empty( $cols ) ) {
			// 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 is_visible TINYINT(1) NOT NULL DEFAULT 1
				 AFTER sort_order"
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// Delete stale section keys -- idempotent (no-op if rows are absent).
		$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"DELETE FROM {$table} WHERE section_key = %s",
			'goodreads'
		) );
		$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"DELETE FROM {$table} WHERE section_key = %s",
			'suggested-reading-order'
		) );
	}
}
