<?php
/**
 * File Path: includes/migrations/class-mbs-migration-3-9.php
 *
 * MBS_Migration_3_9 -- UX-004 -- Remove social-share from section order
 *
 * Social share moved to the book hero as a toggle button (UX-005 Sprint B).
 * Remove social-share from the global default section order (post_id = 0)
 * and from all per-book custom orders. The section-social-share.php template
 * gates on $is_hero so any remaining rows would silently render nothing;
 * this migration cleans them up for correctness.
 *
 * @package MyBookShowroom
 * @since   1.0.0
 */

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

/**
 * Class MBS_Migration_3_9
 *
 * Remove social-share from global default section order and per-book orders.
 *
 * @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_9 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * {@inheritdoc}
	 *
	 * @return string
	 */
	public function version(): string {
		return '3.9';
	}

	/**
	 * {@inheritdoc}
	 *
	 * Replace global default section order with 11-section list (social-share
	 * removed). Delete social-share from all per-book custom orders.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;
		$so_table = $wpdb->prefix . 'mbs_section_order';

		// V5-D-007: Wrap DELETE + re-INSERT in a transaction so the global
		// default section order is never left empty if the process dies mid-way.
		$wpdb->query( 'START TRANSACTION' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture

		// Replace global default order entirely (social-share removed).
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no user input
		$wpdb->query( "DELETE FROM {$so_table} WHERE post_id = 0" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $wpdb->last_error ) {
			$wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			throw new \RuntimeException( 'MBS Migration 3.9: DELETE failed — ' . $wpdb->last_error ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- exception message contains DB error string, never rendered as HTML
		}

		$new_global = array(
			'about-the-book',
			'look-inside',
			'endorsements',
			'reviews',
			'series-order',
			'related-books',
			'goodreads',
			'audio-sample',
			'amazon-reviews',
			'kindle-preview',
			'book-club-resources',
		);

		foreach ( $new_global as $i => $section ) {
			$wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				"INSERT INTO `{$so_table}` (post_id, section_key, sort_order, is_visible)
				VALUES (0, %s, %d, 1)
				ON DUPLICATE KEY UPDATE sort_order = %d, is_visible = 1",
				$section, $i + 1, $i + 1
			) );
			if ( $wpdb->last_error ) {
				$wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				throw new \RuntimeException( 'MBS Migration 3.9: INSERT failed -- ' . $wpdb->last_error ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- exception message contains DB error string, never rendered as HTML
			}
		}

		$wpdb->query( 'COMMIT' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture

		// Remove social-share from all per-book custom section orders.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no user input
		$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 `{$so_table}` WHERE post_id != 0 AND section_key = %s",
			'social-share'
		) );
	}
}
