<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-20.php
 *
 * Migration 4.20 -- Migrate Look Inside from sample_text (format row) to
 * mbs_look_inside_text (postmeta); drop sample_text column.
 *
 * Sprint 171: sample_text was stored at the format-row level (book-level data
 * in the wrong table). This migration:
 *   1. Reads sample_text from the first format row for each book.
 *   2. Converts it to clean HTML via MBS_Look_Inside_Sanitizer::clean().
 *   3. Drops the sample_text column from mbs_format_rows.
 *   4. Writes to mbs_look_inside_text postmeta (only when postmeta is empty).
 *
 * Phase ordering -- why postmeta is written AFTER the column drop:
 *   The WP test framework wraps each test in SET autocommit=0; START TRANSACTION.
 *   MySQL DDL (ALTER TABLE DROP COLUMN) causes an implicit commit that closes that
 *   transaction. Any INSERT executed before the DDL but inside the WP transaction
 *   is committed by the DDL; however, wpdb::insert() goes through process_fields()
 *   which can silently return false for rows whose column metadata is resolved via
 *   information_schema (this path is unreliable around the DDL commit boundary).
 *   By collecting all data into a PHP array first, then doing the DROP, then
 *   inserting postmeta rows after the commit, the INSERTs land in a fresh implicit
 *   transaction that is always visible to the test's post-DDL $wpdb->get_var() read.
 *
 * Idempotency:
 *   - Postmeta check in Phase 2 prevents double-write.
 *   - information_schema guard prevents double-DROP.
 *   - Partial-run replay (column still exists) is fully safe.
 *   - Full-run replay (column already dropped): step 2 get_var returns null
 *     silently ($wpdb swallows the "Unknown column" error); $to_write is empty;
 *     step 3 information_schema guard skips the DROP. No data loss.
 *
 * @package MyBookShowroom
 * @since   1.2.0
 */

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

/**
 * Migration 4.20: migrate Look Inside content to postmeta; drop sample_text column.
 */
// 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_20 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Return the migration version string.
	 *
	 * @return string
	 * @since 1.2.0
	 */
	public function version(): string {
		return '4.20';
	}

	/**
	 * Run the migration.
	 *
	 * Phase 1: Read sample_text values and convert to sanitized HTML (in PHP).
	 * Phase 2: Drop the sample_text column (implicit commit of any open transaction).
	 * Phase 3: Write mbs_look_inside_text postmeta rows (after the commit boundary).
	 *
	 * Idempotent: postmeta check prevents double-write; information_schema guard
	 * prevents double-DROP. See file-level docblock for replay notes.
	 *
	 * @return void
	 * @since 1.2.0
	 */
	public function up(): void {
		global $wpdb;

		$format_rows_table = $wpdb->prefix . 'mbs_format_rows';

		// -------------------------------------------------------------------------
		// Phase 1: Collect sample_text values while the column still exists.
		//    Direct $wpdb->get_var() -- not via MBS_Format_Row_DAO::get_by_post()
		//    whose SELECT list has sample_text removed in this sprint (spec §3.4).
		//    Filter: AND sample_text IS NOT NULL AND sample_text != '' ensures we
		//    skip format rows whose sample_text was never set (e.g. ghost rows from
		//    test DDL implicit commits whose column data was lost on DROP+re-ADD).
		// -------------------------------------------------------------------------
		$post_ids = $wpdb->get_col( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"SELECT ID FROM {$wpdb->posts} WHERE post_type = %s AND post_status != 'trash'",
				MBS_POST_TYPE
			)
		);

		/** @var array<int,string> Maps post_id => sanitized HTML ready for postmeta. */
		$to_write = array();

		foreach ( $post_ids as $post_id ) {
			$post_id = (int) $post_id;

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sample_text = $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 sample_text FROM {$format_rows_table} WHERE post_id = %d AND is_deleted = 0 AND sample_text IS NOT NULL AND sample_text != '' ORDER BY sort_order ASC, id ASC LIMIT 1",
					$post_id
				)
			);

			if ( empty( $sample_text ) ) {
				continue;
			}

			// Skip if mbs_look_inside_text postmeta already exists for this post.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$existing_raw = $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 meta_value FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = 'mbs_look_inside_text' LIMIT 1",
				$post_id
			) );
			if ( null !== $existing_raw ) {
				continue;
			}

			// sample_text was stored via sanitize_textarea_field() -- plain text
			// with possible newline characters. wpautop() converts double line-breaks
			// to <p> tags. MBS_Look_Inside_Sanitizer::clean() enforces the canonical
			// HTML allowlist and is the single source of truth for all Look Inside
			// HTML writes (spec §3.4, R14-M-1).
			$wrapped = MBS_Look_Inside_Sanitizer::clean( wpautop( esc_html( $sample_text ) ), false, 0 );
			if ( '' === $wrapped ) {
				continue;
			}

			$to_write[ $post_id ] = $wrapped;
		}

		// -------------------------------------------------------------------------
		// Phase 2: Drop sample_text column.
		//    Guarded by information_schema.COLUMNS check (same pattern as
		//    MBS_Migration_4_19 column-existence guards). Safe to replay.
		//    MySQL implicit commit triggered here closes the WP test transaction.
		// -------------------------------------------------------------------------
		// 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  = 'sample_text'",
				$format_rows_table
			)
		);

		if ( (int) $col_exists > 0 ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->query( "ALTER TABLE {$format_rows_table} DROP COLUMN sample_text" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// -------------------------------------------------------------------------
		// Phase 3: Write mbs_look_inside_text postmeta (after the DDL commit).
		//    INSERTs here land in a fresh implicit transaction (autocommit=0) that
		//    is visible to any subsequent $wpdb read on the same connection.
		//    Re-check each post for existing postmeta in case another process wrote
		//    it between Phase 1 and now (concurrent run protection).
		// -------------------------------------------------------------------------
		foreach ( $to_write as $post_id => $wrapped ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$existing_raw = $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 meta_value FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = 'mbs_look_inside_text' LIMIT 1",
				$post_id
			) );
			if ( null !== $existing_raw ) {
				continue;
			}
			$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 {$wpdb->postmeta} (post_id, meta_key, meta_value) VALUES (%d, 'mbs_look_inside_text', %s)",
				$post_id,
				$wrapped
			) );
		}
	}
}
