<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-12.php
 *
 * Migration 4.12 -- mbs_book_collection_positions table
 *
 * Creates mbs_book_collection_positions with one row per book-collection pair,
 * storing the per-collection position that was previously scattered across
 * dynamic postmeta keys of the form mbs_collection_{term_id}_position.
 *
 * Backfills by reading all postmeta rows whose meta_key matches the pattern
 * mbs_collection_%_position, extracting the term_id from the key, and
 * inserting into the new table. INSERT IGNORE on the UNIQUE KEY makes the
 * backfill safe to replay.
 *
 * @package MyBookShowroom
 * @since   1.7.0
 */

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

/**
 * Migration 4.12: mbs_book_collection_positions table.
 */
// 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_12 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.12';
	}

	/**
	 * Run the migration.
	 *
	 * Idempotent: SHOW TABLES guard prevents double-create.
	 * INSERT IGNORE on UNIQUE KEY (post_id, term_id) is safe to replay.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

		$table           = $wpdb->prefix . 'mbs_book_collection_positions';
		$charset_collate = $wpdb->get_charset_collate();

		// --- DDL: create table if not present ------------------------------------
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( MBS_DB_Schema::book_collection_positions( $table, $charset_collate ) );

		// --- Backfill: read all mbs_collection_{term_id}_position postmeta -------
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT post_id, meta_key, meta_value
			 FROM {$wpdb->postmeta}
			 WHERE meta_key LIKE 'mbs\_collection\_%\_position'
			   AND meta_value != ''
			   AND meta_value > 0",
			ARRAY_A
		);

		foreach ( (array) $rows as $row ) {
			// Extract term_id from key: mbs_collection_{term_id}_position
			if ( ! preg_match( '/^mbs_collection_(\d+)_position$/', $row['meta_key'], $m ) ) {
				continue;
			}
			$post_id  = (int) $row['post_id'];
			$term_id  = (int) $m[1];
			$position = (int) $row['meta_value'];

			if ( $post_id <= 0 || $term_id <= 0 || $position <= 0 ) {
				continue;
			}

			// 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 IGNORE INTO {$table} (post_id, term_id, position)
					 VALUES (%d, %d, %d)",
					$post_id,
					$term_id,
					$position
				)
			);
		}
	}
}
