<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-28.php
 *
 * Migration 4.28 -- Session 184: ARC Review URLs Child Table (R2-004)
 *
 * Creates mbs_arc_review_urls (id, recipient_id, url, sort_order) and migrates
 * any existing review_urls JSON data from mbs_arc_recipients into the new table.
 * The review_urls TEXT column is retained in mbs_arc_recipients for schema
 * compatibility but is no longer written by the plugin after this migration.
 *
 * Idempotency: dbDelta only creates the table if absent. The per-URL existence
 * check prevents duplicate rows if the migration is somehow re-run.
 *
 * @package MyBookShowroom
 * @since   1.4.0
 */

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

/**
 * Migration 4.28: Create mbs_arc_review_urls and migrate JSON data.
 *
 * @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_4_28 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.4.0
	 */
	public function version(): string {
		return '4.28';
	}

	/**
	 * Run the migration.
	 *
	 * 1. Creates mbs_arc_review_urls via dbDelta (idempotent).
	 * 2. Reads every mbs_arc_recipients row where review_urls IS NOT NULL.
	 * 3. For each URL in the JSON array, inserts a row into the new table
	 *    if that (recipient_id, url) pair does not already exist.
	 *
	 * @return void
	 * @since 1.4.0
	 */
	public function up(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$urls_table      = $wpdb->prefix . 'mbs_arc_review_urls';
		$charset_collate = $wpdb->get_charset_collate();

		// Step 1: Create the child table.
		dbDelta( MBS_DB_Schema::arc_review_urls( $urls_table, $charset_collate ) );

		// Step 2: Migrate existing JSON blobs into the new table.
		$recipients_table = $wpdb->prefix . 'mbs_arc_recipients';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix
		$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 id, review_urls FROM {$recipients_table} WHERE review_urls IS NOT NULL"
		);

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

		foreach ( $rows as $row ) {
			$urls = json_decode( (string) $row->review_urls, true );
			if ( ! is_array( $urls ) ) {
				continue;
			}
			$sort = 0;
			foreach ( $urls as $url ) {
				$url = trim( (string) $url );
				if ( '' === $url ) {
					continue;
				}
				// Idempotency: skip if already present in the child table.
				$exists = (int) $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(
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						"SELECT COUNT(*) FROM {$urls_table} WHERE recipient_id = %d AND url = %s",
						(int) $row->id,
						$url
					)
				);
				if ( 0 === $exists ) {
					$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
						$urls_table,
						array(
							'recipient_id' => (int) $row->id,
							'url'          => $url,
							'sort_order'   => $sort,
						)
					);
				}
				$sort++;
			}
		}
	}
}
