<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-43.php
 *
 * Migration 4.43 -- Add book_title column to mbs_esp_queue; backfill list_id (DAT-003 / V2-D-015)
 *
 * The mbs_esp_queue table previously stored book_title and list_id only inside
 * the payload JSON blob, making the table non-queryable by those fields.
 * This migration:
 *   1. Adds the book_title VARCHAR(255) column if it does not already exist.
 *   2. Backfills book_title and list_id from the payload JSON blob for any
 *      existing rows where the dedicated columns are empty.
 *
 * @package MyBookShowroom
 * @since   4.43.0
 */

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

/**
 * Migration 4.43: add book_title column to mbs_esp_queue; backfill list_id.
 */
// 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_43 implements MBS_Migration { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Returns the migration version string.
	 *
	 * @return string
	 */
	public function version(): string { return '4.43'; }

	/**
	 * Run the migration.
	 *
	 * Step 1: Add book_title VARCHAR(255) NOT NULL DEFAULT '' to mbs_esp_queue
	 *         only if the column does not already exist.
	 * Step 2: Backfill book_title and list_id from the payload JSON blob for
	 *         any rows where the dedicated columns are still empty strings.
	 *
	 * @return void
	 */
	public function up(): void {
		global $wpdb;

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

		// ── Step 1: Add book_title column if absent ──────────────────────────

		$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  = 'book_title'",
				$table
			)
		);

		if ( ! $col_exists ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN book_title VARCHAR(255) NOT NULL DEFAULT '' AFTER list_id" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- DAO layer -- direct DB access is intentional per MBS architecture
		}

		// ── Step 2: Backfill book_title and list_id from payload JSON ────────

		// Fetch rows where at least one dedicated column is still empty.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT id, payload, book_title, list_id FROM `{$table}` WHERE book_title = '' OR list_id = ''"
		);

		if ( empty( $rows ) ) {
			return;
		}

		foreach ( $rows as $row ) {
			$record = json_decode( (string) $row->payload, true );
			if ( ! is_array( $record ) ) {
				continue;
			}

			// F-04: only backfill a column that is CURRENTLY empty AND for which the
			// payload supplies a non-empty value. Never overwrite a populated column
			// with '' just because the OR-matched the other column.
			$update = array();
			$fmt    = array();

			$payload_title = isset( $record['book_title'] ) ? sanitize_text_field( (string) $record['book_title'] ) : '';
			$payload_list  = isset( $record['list_id'] )    ? sanitize_text_field( (string) $record['list_id'] )    : '';

			if ( '' === (string) $row->book_title && '' !== $payload_title ) {
				$update['book_title'] = $payload_title;
				$fmt[]                = '%s';
			}
			if ( '' === (string) $row->list_id && '' !== $payload_list ) {
				$update['list_id'] = $payload_list;
				$fmt[]             = '%s';
			}

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

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$table,
				$update,
				array( 'id' => (int) $row->id ),
				$fmt,
				array( '%d' )
			);
		}
	}
}
