<?php
/**
 * Migration: Appearance Preset Default-Ref Refactor
 *
 * Transforms the appearance preset linking mechanism:
 * 1. In the default preset: resolves all { type: 'global', ref: 'sizes.*' } etc.
 *    to concrete { type: 'custom', value: '<resolved>' } values.
 * 2. In non-default presets: converts global refs to either { type: 'default' }
 *    (if the resolved value matches the default) or { type: 'custom', value: '...' }.
 * 3. Strips buttons, formFields, effects, sizes, spacing from global_values
 *    in all presets and from the system global values option.
 *
 * Idempotent: safe to run multiple times.
 *
 * @package BoostBot
 * @since   1.3.0
 */

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class BoostBot_Migration_Appearance_Default_Ref {

	private static $flag_key = 'boostbot_migration_appearance_default_ref_v3_done';

	private static $strip_sections = array( 'buttons', 'formFields', 'effects', 'sizes', 'spacing' );

	/**
	 * Run the migration.
	 */
	public static function run(): bool {
		if ( '' !== get_option( self::$flag_key, '' ) ) {
			return true;
		}

		global $wpdb;
		$table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_appearance_presets' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$table_exists = $wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$wpdb->prepare( 'SHOW TABLES LIKE %s', $table )
		);
		if ( ! $table_exists ) {
			update_option( self::$flag_key, current_time( 'mysql' ) );
			return true;
		}

		$languages = self::get_active_languages();
		$total_processed = 0;
		$total_fields_converted = 0;

		foreach ( $languages as $lang ) {
			$result = self::migrate_language( $wpdb, $table, $lang );
			$total_processed += $result['presets'];
			$total_fields_converted += $result['fields'];
		}

		// Also process presets with empty language (safety net)
		$result = self::migrate_language( $wpdb, $table, '' );
		$total_processed += $result['presets'];
		$total_fields_converted += $result['fields'];

		// Strip removed sections from system global values
		self::clean_system_global_values();

		// Fix bots linked to a preset in the wrong language.
		$bots_fixed = self::fix_bot_preset_language_mismatch( $wpdb );

		BoostBot_Logger::info( sprintf(
			'[Migration] appearance-default-ref v2: processed %d presets, converted %d fields across %d languages, fixed %d bot-preset mismatches.',
			$total_processed,
			$total_fields_converted,
			count( $languages ),
			$bots_fixed
		) );

		update_option( self::$flag_key, current_time( 'mysql' ) );
		return true;
	}

	/**
	 * Migrate presets for a single language.
	 */
	private static function migrate_language( $wpdb, $table, string $lang ): array {
		$result = array( 'presets' => 0, 'fields' => 0 );

		// 1. Load the default preset for this language.
		if ( '' !== $lang ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
			$default_preset = $wpdb->get_row( $wpdb->prepare(
				"SELECT * FROM {$table} WHERE is_default = 1 AND language = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$lang
			) );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
			$default_preset = $wpdb->get_row(
				"SELECT * FROM {$table} WHERE is_default = 1 AND (language = '' OR language IS NULL) LIMIT 1" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			);
		}

		if ( ! $default_preset ) {
			return $result;
		}

		$default_gv = self::decode_json( $default_preset->global_values );
		$default_es = self::decode_json( $default_preset->element_settings );
		$default_ws = self::decode_json( $default_preset->widget_settings );

		// Fallback: if the preset's own global_values is empty or missing
		// the sections we need to resolve (sizes, effects, etc.), merge in
		// defaults from the system option or seed file so refs can resolve.
		if ( empty( $default_gv ) || ! isset( $default_gv['sizes'] ) ) {
			$default_gv = self::merge_with_fallback_gv( $default_gv );
		}

		// 2. Resolve all global refs in the default preset to concrete values.
		$resolve_count = 0;
		$default_es = self::resolve_global_refs_to_custom( $default_es, $default_gv, $resolve_count );
		$default_ws = self::resolve_global_refs_to_custom( $default_ws, $default_gv, $resolve_count );

		// 3. Strip removed sections from default preset's global_values.
		$default_gv = self::strip_sections( $default_gv );

		// Save the default preset.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			$table,
			array(
				'global_values'    => wp_json_encode( $default_gv ),
				'element_settings' => wp_json_encode( $default_es ),
				'widget_settings'  => wp_json_encode( $default_ws ),
			),
			array( 'id' => $default_preset->id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
		$result['presets']++;
		$result['fields'] += $resolve_count;

		// 4. Process non-default presets for this language.
		if ( '' !== $lang ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
			$others = $wpdb->get_results( $wpdb->prepare(
				"SELECT * FROM {$table} WHERE is_default = 0 AND language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$lang
			) );
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
			$others = $wpdb->get_results(
				"SELECT * FROM {$table} WHERE is_default = 0 AND (language = '' OR language IS NULL)" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			);
		}

		if ( empty( $others ) ) {
			return $result;
		}

		foreach ( $others as $preset ) {
			$gv = self::decode_json( $preset->global_values );
			$es = self::decode_json( $preset->element_settings );
			$ws = self::decode_json( $preset->widget_settings );

			if ( empty( $gv ) || ! isset( $gv['sizes'] ) ) {
				$gv = self::merge_with_fallback_gv( $gv );
			}

			$field_count = 0;

			// Resolve global refs, then compare to default to decide default vs custom.
			$es = self::convert_non_default_preset( $es, $gv, $default_es, $field_count );
			$ws = self::convert_non_default_preset( $ws, $gv, $default_ws, $field_count );

			// Strip removed sections.
			$gv = self::strip_sections( $gv );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$table,
				array(
					'global_values'    => wp_json_encode( $gv ),
					'element_settings' => wp_json_encode( $es ),
					'widget_settings'  => wp_json_encode( $ws ),
				),
				array( 'id' => $preset->id ),
				array( '%s', '%s', '%s' ),
				array( '%d' )
			);
			$result['presets']++;
			$result['fields'] += $field_count;
		}

		return $result;
	}

	/**
	 * Merge preset global_values with fallback sources so refs can be resolved
	 * even when the preset's own global_values was never populated in the DB.
	 */
	private static function merge_with_fallback_gv( array $gv ): array {
		// Try the system-level wp_option first.
		$sys = get_option( 'boostbot_appearance_global_values', array() );
		if ( is_array( $sys ) && ! empty( $sys ) && isset( $sys['sizes'] ) ) {
			foreach ( $sys as $section => $values ) {
				if ( ! isset( $gv[ $section ] ) ) {
					$gv[ $section ] = $values;
				}
			}
			return $gv;
		}

		// Hardcoded defaults for the deprecated sections that refs may point to.
		// These are the v1.2.0 production defaults from the original seed file.
		$defaults = self::get_legacy_global_value_defaults();
		foreach ( $defaults as $section => $values ) {
			if ( ! isset( $gv[ $section ] ) ) {
				$gv[ $section ] = $values;
			}
		}

		return $gv;
	}

	/**
	 * v1.2.0 production defaults for the deprecated global_values sections.
	 * These are hardcoded here because the seed file is updated to the new
	 * format and no longer contains these sections.
	 */
	private static function get_legacy_global_value_defaults(): array {
		return array(
			'buttons' => array(
				'primary' => array(
					'background'      => '#3BC2D0',
					'text'            => '#FFFFFF',
					'hoverBackground' => '#2BA8B5',
					'borderRadius'    => '14px',
					'padding'         => '10px 20px',
				),
				'secondary' => array(
					'background'      => 'transparent',
					'text'            => '#3BC2D0',
					'hoverBackground' => 'rgba(59, 194, 208, 0.1)',
					'borderRadius'    => '12px',
					'padding'         => '10px 20px',
				),
				'borderRadius' => '8px',
				'padding'      => '12px 24px',
				'shadow'       => 'none',
			),
			'formFields' => array(
				'background'       => '#FFFFFF',
				'text'             => '#1F2937',
				'border'           => '#E5E7EB',
				'focusBorder'      => '#3BC2D0',
				'borderRadius'     => '15px',
				'padding'          => '12px 16px',
				'placeholderColor' => '#9CA3AF',
				'labelColor'       => '#374151',
				'errorColor'       => '#EF4444',
				'borderWidth'      => '1px',
				'focusRing'        => true,
			),
			'effects' => array(
				'borderRadiusCircle' => '50%',
				'shadowAvatar'       => '0 2px 4px rgba(0,0,0,0.1)',
				'shadowSmall'        => '0 1px 2px rgba(0,0,0,0.05)',
				'shadowMedium'       => '0 4px 6px rgba(0,0,0,0.1)',
				'shadowLarge'        => '0 10px 40px rgba(0,0,0,0.15)',
				'transitionDuration' => '200ms',
				'transitionTiming'   => 'ease-out',
				'borderRadiusSmall'  => '4px',
				'borderRadiusMedium' => '8px',
				'borderRadiusLarge'  => '16px',
				'borderRadiusFull'   => '9999px',
				'borderRadius'       => '16px',
				'shadow'             => '0 10px 40px rgba(0,0,0,0.15)',
				'transition'         => '0.2s ease',
				'shadowNone'         => 'none',
				'shadowCard'         => '4px 10px 15px -3px rgba(0, 0, 0, 0.2)',
				'shadowButton'       => '0px 4px 6px 0px rgba(0, 0, 0, 0.2)',
				'borderRadiusPill'   => '41px',
				'shadowPlayButton'   => '0px 4px 6px -1px rgba(0, 0, 0, 0.15)',
			),
			'sizes' => array(
				'lg'                    => '24px',
				'avatarBorderWidth'     => '3px',
				'avatarIcon'            => '24px',
				'borderThin'            => '1px',
				'iconSmall'             => '14px',
				'iconMedium'            => '18px',
				'iconLarge'             => '28px',
				'headerHeight'          => '60px',
				'avatarMedium'          => '40px',
				'avatarLarge'           => '90px',
				'actionButton'          => '36px',
				'statusIndicator'       => '12px',
				'statusIndicatorBorder' => '2px',
				'bubbleSize'            => '45px',
				'starRating'            => '32px',
				'emojiSize'             => '40px',
				'productImageHeight'    => '180px',
				'textareaMin'           => '80px',
				'textareaMax'           => '200px',
				'windowWidth'           => '380px',
				'windowHeight'          => '600px',
				'messageMaxWidth'       => '80%',
				'iconXSmall'            => '10px',
				'sm'                    => '16px',
				'galleryImageHeight'    => '80px',
				'productImageMaxHeight' => '400px',
				'borderNone'            => '0px',
				'productPriceFontSize'  => '24px',
				'checkbox'              => '20px',
				'iconXLarge'            => '32px',
				'starStrokeWidth'       => '1.5px',
			),
			'spacing' => array(
				'buttonPadding'     => '10px 20px',
				'textareaPadding'   => '12px',
				'none'              => '0px',
				'containerTight'    => '8px',
				'containerNormal'   => '12px',
				'containerRelaxed'  => '16px',
				'containerSpacious' => '24px',
				'xsPlus'            => '6px',
				'xxs'               => '2px',
				'xs'                => '4px',
				'sm'                => '8px',
				'inputPadding'      => '8px 12px',
				'messagePadding'    => '8px 16px',
				'md'                => '12px',
				'checkboxGap'       => '10px',
			),
		);
	}

	/**
	 * Recursively resolve { type: 'global', ref: '...' } to { type: 'custom', value: '<resolved>' }
	 * for non-color/typography refs.
	 */
	private static function resolve_global_refs_to_custom( $data, $global_values, &$count ) {
		if ( ! is_array( $data ) ) {
			return $data;
		}

		// FieldValue leaf node
		if ( isset( $data['type'] ) && 'global' === $data['type'] && isset( $data['ref'] ) ) {
			$ref = $data['ref'];
			// Keep color and typography refs — they still use the global linking system.
			if ( self::is_color_or_typography_ref( $ref ) ) {
				return $data;
			}
			$resolved = self::resolve_ref( $ref, $global_values );
			if ( null !== $resolved ) {
				$count++;
				return array( 'type' => 'custom', 'value' => $resolved );
			}
			return $data;
		}

		foreach ( $data as $key => $value ) {
			if ( is_array( $value ) ) {
				$data[ $key ] = self::resolve_global_refs_to_custom( $value, $global_values, $count );
			}
		}

		return $data;
	}

	/**
	 * For a non-default preset: resolve global refs, then compare to the default preset
	 * value. If they match → { type: 'default' }. If different → { type: 'custom', value: ... }.
	 */
	private static function convert_non_default_preset( $data, $preset_gv, $default_data, &$count ) {
		if ( ! is_array( $data ) ) {
			return $data;
		}

		// FieldValue leaf node
		if ( isset( $data['type'] ) ) {
			// Already type: 'default' — leave as is (idempotent).
			if ( 'default' === $data['type'] ) {
				return $data;
			}

			if ( 'global' === $data['type'] && isset( $data['ref'] ) ) {
				$ref = $data['ref'];
				// Keep color and typography refs intact.
				if ( self::is_color_or_typography_ref( $ref ) ) {
					return $data;
				}
				$resolved = self::resolve_ref( $ref, $preset_gv );
				if ( null === $resolved ) {
					return $data;
				}

				// Compare resolved value to default preset.
				$default_value = self::extract_concrete_value( $default_data );
				if ( null !== $default_value && $resolved === $default_value ) {
					$count++;
					return array( 'type' => 'default' );
				}
				$count++;
				return array( 'type' => 'custom', 'value' => $resolved );
			}

			// Already type: 'custom' — compare to default.
			if ( 'custom' === $data['type'] && isset( $data['value'] ) ) {
				$default_value = self::extract_concrete_value( $default_data );
				if ( null !== $default_value && $data['value'] === $default_value ) {
					$count++;
					return array( 'type' => 'default' );
				}
				return $data;
			}

			return $data;
		}

		// Recurse into sub-objects.
		foreach ( $data as $key => $value ) {
			if ( is_array( $value ) ) {
				$child_default = isset( $default_data[ $key ] ) && is_array( $default_data[ $key ] ) ? $default_data[ $key ] : array();
				$data[ $key ] = self::convert_non_default_preset( $value, $preset_gv, $child_default, $count );
			}
		}

		return $data;
	}

	/**
	 * Extract the concrete string value from a default preset's FieldValue entry.
	 * Returns null if not a concrete { type: 'custom', value: '...' } leaf.
	 */
	private static function extract_concrete_value( $data ) {
		if ( ! is_array( $data ) ) {
			return null;
		}
		if ( isset( $data['type'] ) && 'custom' === $data['type'] && isset( $data['value'] ) ) {
			return $data['value'];
		}
		return null;
	}

	/**
	 * Check if a ref path is for colors or typography (which stay linked to globals).
	 */
	private static function is_color_or_typography_ref( string $ref ): bool {
		return 0 === strpos( $ref, 'colors.' ) || 0 === strpos( $ref, 'typography.' );
	}

	/**
	 * Resolve a dot-notation ref against a global_values array.
	 */
	private static function resolve_ref( string $ref, $global_values ) {
		if ( ! is_array( $global_values ) || '' === $ref ) {
			return null;
		}
		$parts   = explode( '.', $ref );
		$current = $global_values;
		foreach ( $parts as $part ) {
			if ( ! is_array( $current ) || ! isset( $current[ $part ] ) ) {
				return null;
			}
			$current = $current[ $part ];
		}
		return is_string( $current ) ? $current : null;
	}

	/**
	 * Remove the sections that are being moved to the default preset.
	 */
	private static function strip_sections( $gv ) {
		if ( ! is_array( $gv ) ) {
			return $gv;
		}
		foreach ( self::$strip_sections as $section ) {
			unset( $gv[ $section ] );
		}
		return $gv;
	}

	/**
	 * Clean the system-level global values (wp_options).
	 */
	private static function clean_system_global_values() {
		$sys = get_option( 'boostbot_appearance_global_values', array() );
		if ( ! is_array( $sys ) || empty( $sys ) ) {
			return;
		}
		$changed = false;
		foreach ( self::$strip_sections as $section ) {
			if ( isset( $sys[ $section ] ) ) {
				unset( $sys[ $section ] );
				$changed = true;
			}
		}
		if ( $changed ) {
			update_option( 'boostbot_appearance_global_values', $sys );
		}
	}

	/**
	 * Get languages for migration.
	 *
	 * Merges Language Manager enabled languages with languages actually present
	 * in presets/bots DB tables. This ensures presets in non-enabled languages
	 * (e.g. English presets on a Hebrew-only site) are still migrated.
	 */
	private static function get_active_languages(): array {
		$languages = array();

		if ( class_exists( 'BoostBot_Language_Manager' ) && method_exists( 'BoostBot_Language_Manager', 'get_instance' ) ) {
			try {
				$manager = BoostBot_Language_Manager::get_instance();
				if ( method_exists( $manager, 'get_enabled_languages' ) ) {
					$langs = $manager->get_enabled_languages();
					if ( ! empty( $langs ) && is_array( $langs ) ) {
						$languages = $langs;
					}
				}
			} catch ( \Throwable $e ) {
				BoostBot_Logger::warning( '[Migration] Could not get active languages via manager: ' . $e->getMessage() );
			}
		}

		// Always query distinct languages from presets and bots to catch
		// entities in languages not enabled in the Language Manager.
		global $wpdb;
		try {
			$bot_langs    = $wpdb->get_col( "SELECT DISTINCT language FROM {$wpdb->prefix}boostbot_bots WHERE language IS NOT NULL AND language != ''" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$preset_langs = $wpdb->get_col( "SELECT DISTINCT language FROM {$wpdb->prefix}boostbot_appearance_presets WHERE language IS NOT NULL AND language != ''" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$db_langs     = array_unique( array_merge( $bot_langs ?: array(), $preset_langs ?: array() ) );
			$languages    = array_unique( array_merge( $languages, $db_langs ) );
		} catch ( \Throwable $e ) {
			BoostBot_Logger::warning( '[Migration] Could not query languages from DB: ' . $e->getMessage() );
		}

		if ( empty( $languages ) ) {
			$primary = get_option( 'boostbot_primary_language', 'he' );
			return array( $primary );
		}

		if ( ! in_array( 'he', $languages, true ) ) {
			array_unshift( $languages, 'he' );
		}

		return $languages;
	}

	/**
	 * Fix bots whose appearance_preset_id is NULL or points to a preset in a
	 * different language. Re-links them to the default preset for the bot's
	 * own language.
	 *
	 * @return int Number of bots fixed.
	 */
	private static function fix_bot_preset_language_mismatch( $wpdb ): int {
		$bots_table    = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bots' );
		$presets_table  = self::sanitize_identifier( $wpdb->prefix . 'boostbot_appearance_presets' );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
		$mismatched = $wpdb->get_results(
			"SELECT b.id AS bot_id, b.bot_name, b.language AS bot_lang, b.appearance_preset_id, p.language AS preset_lang
			 FROM {$bots_table} b
			 LEFT JOIN {$presets_table} p ON b.appearance_preset_id = p.id
			 WHERE b.language IS NOT NULL AND b.language != ''
			   AND (b.appearance_preset_id IS NULL
			        OR p.id IS NULL
			        OR (p.language IS NOT NULL AND p.language != '' AND b.language != p.language))" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		if ( empty( $mismatched ) ) {
			return 0;
		}

		$fixed = 0;
		$default_cache = array();

		foreach ( $mismatched as $row ) {
			$lang = $row->bot_lang;

			if ( ! isset( $default_cache[ $lang ] ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
				$default_preset = $wpdb->get_row( $wpdb->prepare(
					"SELECT id FROM {$presets_table} WHERE is_default = 1 AND language = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$lang
				) );
				$default_cache[ $lang ] = $default_preset ? (int) $default_preset->id : null;
			}

			$correct_id = $default_cache[ $lang ];
			if ( null === $correct_id ) {
				BoostBot_Logger::warning( sprintf(
					'[Migration] Bot %d ("%s") lang=%s has preset=%s (lang=%s) but no default preset exists for %s — skipping.',
					$row->bot_id, $row->bot_name, $lang,
					$row->appearance_preset_id ?: 'NULL', $row->preset_lang ?: 'NULL', $lang
				) );
				continue;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$bots_table,
				array( 'appearance_preset_id' => $correct_id ),
				array( 'id' => $row->bot_id ),
				array( '%d' ),
				array( '%d' )
			);

			BoostBot_Logger::info( sprintf(
				'[Migration] Fixed bot %d ("%s"): preset %s (lang=%s) → %d (lang=%s).',
				$row->bot_id, $row->bot_name,
				$row->appearance_preset_id ?: 'NULL', $row->preset_lang ?: 'NULL',
				$correct_id, $lang
			) );
			$fixed++;
		}

		return $fixed;
	}

	/**
	 * Decode a JSON field (string/object → array).
	 */
	private static function decode_json( $value ): array {
		if ( is_string( $value ) ) {
			$decoded = json_decode( $value, true );
			return is_array( $decoded ) ? $decoded : array();
		}
		if ( is_object( $value ) ) {
			return json_decode( wp_json_encode( $value ), true ) ?: array();
		}
		if ( is_array( $value ) ) {
			return $value;
		}
		return array();
	}

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}
