<?php
/**
 * File Path: includes/migrations/class-mbs-migration-2-9.php
 *
 * MBS_Migration_2_9 — Sprint — Refresh global section order
 *
 * Replace global default section order with 11-section FREE-first canonical list.
 * Append missing new sections (series-order, related-books, goodreads,
 * kindle-preview) to all existing per-book custom orders.
 *
 * @package MyBookShowroom
 * @since   3.2.0
 */

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

/**
 * Class MBS_Migration_2_9
 *
 * Replace global default section order with 11-section FREE-first canonical list.
 * Append missing new sections (series-order, related-books, goodreads,
 * kindle-preview) to all existing per-book custom 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_2_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 '2.9';
	}

	/**
	 * {@inheritdoc}
	 *
	 * Replace global default section order with 11-section FREE-first canonical list.
 * Append missing new sections (series-order, related-books, goodreads,
 * kindle-preview) to all existing per-book custom orders.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;
		$so_table = $wpdb->prefix . 'mbs_section_order';

		// Replace global default order entirely.
		// 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
		$new_global = array(
			'about-the-book',
			'look-inside',
			'endorsements',
			'reviews',
			'series-order',
			'related-books',
			'goodreads',
			'social-share',
			'audio-sample',
			'amazon-reviews',
			'kindle-preview',
		);
		foreach ( $new_global as $i => $section ) {
			// 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
				"INSERT INTO {$so_table} (post_id, section_key, sort_order)
				 VALUES (0, %s, %d)
				 ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order)",
				$section,
				$i + 1
			) );
		}

		// Append missing new sections to any existing per-book custom orders.
		$new_keys = array( 'series-order', 'related-books', 'goodreads', 'kindle-preview' );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no user input
		$per_book_ids = $wpdb->get_col( "SELECT DISTINCT post_id 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
		foreach ( $per_book_ids as $pid ) {
			$pid = (int) $pid;
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$existing = $wpdb->get_col( $wpdb->prepare( "SELECT section_key FROM {$so_table} WHERE post_id = %d", $pid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$max_sort = (int) $wpdb->get_var( $wpdb->prepare( "SELECT MAX(sort_order) FROM {$so_table} WHERE post_id = %d", $pid ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$offset   = 1;
			foreach ( $new_keys as $section ) {
				if ( ! in_array( $section, $existing, true ) ) {
					$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
						$so_table,
						array(
							'post_id'     => $pid,
							'section_key' => $section,
							'sort_order'  => $max_sort + $offset,
						),
						array( '%d', '%s', '%d' )
					);
					$offset++;
				}
			}
		}
	}
}
