<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-34.php
 *
 * Migration 4.34 -- Sprint Reading Order Display:
 * Add series_reading_order_position, series_reading_order_hidden,
 * universe_reading_order_hidden columns to mbs_book_meta.
 * Migrate mbs_series_reading_order_position postmeta to mbs_book_meta.
 * Migrate mbs_series_default_sort term_meta to mbs_series_archive_display_mode.
 *
 * All three sub-steps are idempotent.
 *
 * @package MyBookShowroom
 * @since   4.34.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_34 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.34'; }

	/**
	 * Run the migration.
	 *
	 * Sub-step A: Add columns to mbs_book_meta (idempotent via SHOW COLUMNS guard).
	 * Sub-step B: Migrate mbs_series_reading_order_position postmeta to mbs_book_meta.
	 * Sub-step C: Migrate mbs_series_default_sort term_meta to mbs_series_archive_display_mode.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

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

		// ---- Sub-step A: Add columns (idempotent per-column guard) (AUD-014) ----
		$wanted = array(
			'series_reading_order_position' => 'DECIMAL(10,2) NULL AFTER series_position',
			'series_reading_order_hidden'   => 'TINYINT(1) NOT NULL DEFAULT 0 AFTER series_reading_order_position',
			'universe_reading_order_hidden' => 'TINYINT(1) NOT NULL DEFAULT 0 AFTER series_reading_order_hidden',
		);
		$adds = array();
		foreach ( $wanted as $col => $def ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$exists = $wpdb->get_var( "SHOW COLUMNS FROM `{$bm_table}` LIKE '" . esc_sql( $col ) . "'" );
			if ( ! $exists ) {
				$adds[] = 'ADD COLUMN ' . $col . ' ' . $def;
			}
		}
		if ( $adds ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "ALTER TABLE `{$bm_table}` " . implode( ', ', $adds ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- columns are a fixed internal allowlist
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// Index add, guarded via information_schema.STATISTICS so a replay does NOT
		// re-issue a duplicate-key ALTER (mirrors migration 4-19's idx_series_position
		// guard). Without this guard the AUD-006 last_error check would treat the
		// duplicate-index error as a migration failure and halt repair(). (AUD-014)
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$idx_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   = 'idx_series_ro_pos'",
				$bm_table
			)
		);
		if ( (int) $idx_exists === 0 ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "ALTER TABLE `{$bm_table}` ADD KEY idx_series_ro_pos (series_reading_order_position)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- idempotent index add
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange
		}

		// ---- Sub-step B: Migrate postmeta to mbs_book_meta ----
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			"SELECT post_id, meta_value FROM {$wpdb->postmeta}
			 WHERE meta_key = 'mbs_series_reading_order_position' AND meta_value != ''"
		);
		if ( $rows ) {
			foreach ( $rows as $row ) {
				$pos = max( 0.0, (float) $row->meta_value );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
				$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
					"INSERT INTO `{$bm_table}` (post_id, series_reading_order_position)
					 VALUES (%d, %f)
					 ON DUPLICATE KEY UPDATE
					   series_reading_order_position = COALESCE(series_reading_order_position, VALUES(series_reading_order_position))",
					$row->post_id, $pos
				) );
				// V5-D-016: Delete postmeta only for this row, only when the INSERT
				// succeeded. Bulk delete at end would silently drop data if any INSERT fails.
				if ( ! $wpdb->last_error ) {
					delete_post_meta( (int) $row->post_id, 'mbs_series_reading_order_position' );
				}
			}
			// Bulk delete removed — per-row delete above is now the only cleanup path.
		}

		// ---- Sub-step C: Migrate mbs_series_default_sort term_meta ----
		$terms = get_terms( array(
			'taxonomy'   => 'mbs_series',
			'hide_empty' => false,
			'meta_key'   => 'mbs_series_default_sort', // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- standard WP query pattern; VIP rule does not apply to this plugin
			'fields'     => 'ids',
		) );
		if ( ! is_wp_error( $terms ) && ! empty( $terms ) ) {
			foreach ( $terms as $term_id ) {
				$val = get_term_meta( $term_id, 'mbs_series_default_sort', true );
				update_term_meta( $term_id, 'mbs_series_archive_display_mode', $val );
				delete_term_meta( $term_id, 'mbs_series_default_sort' );
			}
		}
	}
}
