<?php
/**
 * File Path: includes/migrations/class-mbs-migration-3-0.php
 *
 * MBS_Migration_3_0 — Sprint BOOK-CLUB-UI — book-club-resources section
 *
 * Insert book-club-resources into global default section order and
 * append to any existing per-book custom orders that lack it.
 *
 * @package MyBookShowroom
 * @since   3.2.0
 */

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

/**
 * Class MBS_Migration_3_0
 *
 * Insert book-club-resources into global default section order and
 * append to any existing per-book custom orders that lack it.
 *
 * @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_0 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.0';
	}

	/**
	 * {@inheritdoc}
	 *
	 * Insert book-club-resources into global default section order and
 * append to any existing per-book custom orders that lack it.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;
		$so_table = $wpdb->prefix . 'mbs_section_order';

		// Append book-club-resources to the global default order.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- no user input
		$max_so = (int) $wpdb->get_var( "SELECT MAX(sort_order) 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
		// 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 = sort_order",
			'book-club-resources',
			$max_so + 1
		) );

		// Append to any existing per-book custom orders that do not yet include it.
		// 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
			$has = (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				"SELECT COUNT(*) FROM {$so_table} WHERE post_id = %d AND section_key = 'book-club-resources'",
				$pid
			) );
			if ( $has ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$book_max = (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
				"SELECT MAX(sort_order) FROM {$so_table} WHERE post_id = %d",
				$pid
			) );
			$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' => 'book-club-resources',
					'sort_order'  => $book_max + 1,
				),
				array( '%d', '%s', '%d' )
			);
		}
	}
}
