<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-19.php
 *
 * Migration 4.19 — mbs_book_meta: add series_position + title_sort_key columns
 *
 * Sprint 163-C resolves FLAG-1 and FLAG-2 from POSTMETA-SCOPE.md:
 *
 *   FLAG-1: mbs_series_position — fails C1; MBS_Series_Reading_Order_DAO::
 *           compute_default_order() LEFT JOINs wp_postmeta and ORDER BYs the
 *           DECIMAL-cast meta_value. Migrating to a typed DECIMAL(10,2) column
 *           in mbs_book_meta eliminates the postmeta JOIN on every series
 *           reading-order page load.
 *
 *   FLAG-2: mbs_title_sort_key — fails C1; MBS_Book_Query::build() passes
 *           orderby=>meta_value + meta_key=>mbs_title_sort_key to WP_Query,
 *           forcing a postmeta JOIN. Migrating to a VARCHAR column and using a
 *           posts_clauses filter to JOIN mbs_book_meta directly removes the
 *           postmeta dependency from the front-end book listing sort.
 *
 * Idempotency:
 *   - Column additions guarded by information_schema.COLUMNS check.
 *   - Index additions guarded by information_schema.STATISTICS check.
 *   - Backfill uses INSERT ... ON DUPLICATE KEY UPDATE; safe to replay.
 *
 * No destructive changes. Postmeta keys are retained (dual-write) until a
 * future migration explicitly retires them.
 *
 * @package MyBookShowroom
 * @since   2.5.0
 */

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

/**
 * Migration 4.19: add series_position + title_sort_key columns to mbs_book_meta.
 */
// 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_19 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * @return string Migration version string.
	 */
	public function version(): string {
		return '4.19';
	}

	/**
	 * Run the migration.
	 *
	 * Idempotent: all schema changes are guarded; backfill uses ODKU so
	 * replaying is safe.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

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

		// -----------------------------------------------------------------
		// 1. ADD COLUMN series_position DECIMAL(10,2) NULL DEFAULT NULL
		// -----------------------------------------------------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col_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.COLUMNS
				 WHERE table_schema = DATABASE()
				   AND table_name   = %s
				   AND column_name  = 'series_position'",
				$table
			)
		);
		if ( (int) $col_exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN series_position DECIMAL(10,2) NULL DEFAULT NULL" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -----------------------------------------------------------------
		// 2. ADD KEY idx_series_position (series_position)
		// -----------------------------------------------------------------
		// 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_position'",
				$table
			)
		);
		if ( (int) $idx_exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD KEY idx_series_position (series_position)" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -----------------------------------------------------------------
		// 3. ADD COLUMN title_sort_key VARCHAR(255) NOT NULL DEFAULT ''
		// -----------------------------------------------------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col_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.COLUMNS
				 WHERE table_schema = DATABASE()
				   AND table_name   = %s
				   AND column_name  = 'title_sort_key'",
				$table
			)
		);
		if ( (int) $col_exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD COLUMN title_sort_key VARCHAR(255) NOT NULL DEFAULT ''" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -----------------------------------------------------------------
		// 4. ADD KEY idx_title_sort_key (title_sort_key(191))
		//    191-char prefix: 191 * 4 bytes = 764 < 767-byte InnoDB limit.
		// -----------------------------------------------------------------
		// 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_title_sort_key'",
				$table
			)
		);
		if ( (int) $idx_exists === 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$table} ADD KEY idx_title_sort_key (title_sort_key(191))" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -----------------------------------------------------------------
		// 5. Backfill series_position from wp_postmeta
		//    INSERT ... ON DUPLICATE KEY UPDATE: safe to replay.
		// -----------------------------------------------------------------
		$pm = $wpdb->postmeta;
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"INSERT INTO {$table} (post_id, series_position)
				 SELECT pm.post_id,
				        CAST(pm.meta_value AS DECIMAL(10,2))
				 FROM {$pm} pm
				 WHERE pm.meta_key   = %s
				   AND pm.meta_value != ''
				   AND CAST(pm.meta_value AS DECIMAL(10,2)) > 0
				 ON DUPLICATE KEY UPDATE
				   series_position = VALUES(series_position)",
				'mbs_series_position'
			)
		);

		// -----------------------------------------------------------------
		// 6. Backfill title_sort_key from wp_postmeta
		//    INSERT ... ON DUPLICATE KEY UPDATE: safe to replay.
		// -----------------------------------------------------------------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"INSERT INTO {$table} (post_id, title_sort_key)
				 SELECT pm.post_id,
				        pm.meta_value
				 FROM {$pm} pm
				 WHERE pm.meta_key   = %s
				   AND pm.meta_value != ''
				 ON DUPLICATE KEY UPDATE
				   title_sort_key = VALUES(title_sort_key)",
				'mbs_title_sort_key'
			)
		);
	}
}
