<?php
/**
 * File Path: includes/migrations/class-mbs-migration-4-22.php
 *
 * Migration 4.22 -- Migrate affiliate custom stores from wp_options JSON blob
 * to the mbs_affiliate_stores table (RF-012).
 *
 * Source option: mbs_setting_affiliate_custom_stores.
 * Each record carries: store_key, store_name, affiliate_url.
 *
 * sort_order is assigned by array position (0-indexed).
 * created_at / updated_at set to current UTC time on insert.
 *
 * Idempotency: skips if mbs_affiliate_stores already contains rows.
 *
 * @package MyBookShowroom
 * @since   1.3.0
 */

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

/**
 * Migration 4.22: Affiliate custom stores wp_options -> mbs_affiliate_stores 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_22 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.3.0
	 */
	public function version(): string {
		return '4.22';
	}

	/**
	 * Run the migration.
	 *
	 * Reads the mbs_setting_affiliate_custom_stores option and inserts each store
	 * into the mbs_affiliate_stores table. Skips if the table already has rows.
	 *
	 * @return void
	 * @since 1.3.0
	 */
	public function up(): void {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_affiliate_stores';

		// Idempotency guard.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$existing = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
		if ( $existing > 0 ) {
			return;
		}

		$stores = get_option( 'mbs_setting_affiliate_custom_stores', array() );
		if ( ! is_array( $stores ) || empty( $stores ) ) {
			return;
		}

		$now = current_time( 'mysql', true ); // UTC (A-005).

		foreach ( $stores as $i => $store ) {
			if ( ! is_array( $store ) ) {
				continue;
			}

			// Old option format (JS): {id, name, url, code, pattern, favicon_url}.
			$raw_id    = $store['id'] ?? '';
			$store_key = '' !== $raw_id ? sanitize_key( $raw_id ) : sanitize_key( $store['name'] ?? '' );
			if ( '' === $store_key ) {
				$store_key = 'store_' . ( $i + 1 );
			}
			$store_name = sanitize_text_field( $store['name'] ?? '' );

			// Build affiliate_url from pattern + code substitution, or fall back to url.
			$pattern      = trim( $store['pattern'] ?? '' );
			$code         = trim( $store['code'] ?? '' );
			$affiliate_url = ( '' !== $pattern && '' !== $code )
				? esc_url_raw( str_replace( '{CODE}', $code, $pattern ) )
				: esc_url_raw( $store['url'] ?? '' );

			if ( '' === $affiliate_url ) {
				continue;
			}

			$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- DAO layer -- direct DB access is intentional per MBS architecture
				$table,
				array(
					'store_key'     => $store_key,
					'store_name'    => $store_name,
					'affiliate_url' => $affiliate_url,
					'sort_order'    => $i,
					'created_at'    => $now,
					'updated_at'    => $now,
				),
				array( '%s', '%s', '%s', '%d', '%s', '%s' )
			);
		}
	}
}
