<?php
/**
 * Migrates persisted launch plans and targets to canonical target names.
 *
 * @package ScheduledChanges
 */

namespace ScheduledChanges\Database\Migrations;

// phpcs:disable WordPress.DB -- Scheduler-owned custom tables require direct wpdb queries.
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Exception messages are escaped at UI boundaries.

use ScheduledChanges\Database\Migration;
use ScheduledChanges\Database\TableNames;
use ScheduledChanges\Domain\Launch\OperationPlan;
use ScheduledChanges\Support\Json;

/**
 * Rewrites legacy object/product target aliases to operation-plan schema v3.
 */
final class CanonicalTargetSchemaMigration implements Migration {
	/** Returns the schema version introduced by this migration. */
	public function version(): int {
		return 3;
	}

	/**
	 * Migrates legacy persisted target aliases to canonical schema fields.
	 *
	 * @param \wpdb $wpdb WordPress database connection.
	 */
	public function up( \wpdb $wpdb ): void {
		$tables   = ( new TableNames() )->all();
		$versions = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, plan_json FROM %i WHERE schema_version < %d ORDER BY id ASC',
				$tables['launch_versions'],
				3
			),
			ARRAY_A
		);

		if ( '' !== (string) $wpdb->last_error ) {
			throw new \RuntimeException( 'Unable to read legacy launch plans: ' . $wpdb->last_error );
		}

		foreach ( is_array( $versions ) ? $versions : array() as $row ) {
			$legacy    = OperationPlan::fromArray( Json::decodeObject( (string) ( $row['plan_json'] ?? '' ) ) );
			$canonical = OperationPlan::create(
				$legacy->graph(),
				$legacy->validation(),
				$legacy->compatibility(),
				$legacy->requiredProviders(),
				$legacy->humanSummary(),
				$legacy->fingerprints()
			);
			$updated   = $wpdb->update(
				$tables['launch_versions'],
				array(
					'plan_json'      => $canonical->json(),
					'schema_version' => OperationPlan::SCHEMA_VERSION,
					'checksum'       => $canonical->checksum(),
				),
				array( 'id' => (int) ( $row['id'] ?? 0 ) ),
				array( '%s', '%d', '%s' ),
				array( '%d' )
			);

			if ( false === $updated ) {
				throw new \RuntimeException( 'Unable to migrate a legacy launch plan: ' . $wpdb->last_error );
			}
		}

		if (
			$this->columnExists( $wpdb, $tables['launch_targets'], 'object_id' )
			&& $this->columnExists( $wpdb, $tables['launch_targets'], 'object_type' )
			&& $this->columnExists( $wpdb, $tables['launch_targets'], 'product_type' )
		) {
			$result = $wpdb->query(
				$wpdb->prepare(
					"UPDATE %i
				 SET target_id = COALESCE(target_id, object_id),
				     target_type = CASE WHEN target_type = '' THEN object_type ELSE target_type END,
				     subtype = COALESCE(subtype, product_type),
				     object_id = NULL,
				     object_type = '',
				     product_type = NULL
				 WHERE object_id IS NOT NULL OR object_type <> '' OR product_type IS NOT NULL",
					$tables['launch_targets']
				)
			);

			if ( false === $result ) {
				throw new \RuntimeException( 'Unable to migrate legacy launch targets: ' . $wpdb->last_error );
			}
		}

		if (
			$this->columnExists( $wpdb, $tables['recording_sessions'], 'source_product_id' )
			&& $this->columnExists( $wpdb, $tables['recording_sessions'], 'shadow_product_id' )
		) {
			$result = $wpdb->query(
				$wpdb->prepare(
					"UPDATE %i
				 SET source_provider_id = CASE WHEN source_provider_id = '' THEN 'woocommerce-core' ELSE source_provider_id END,
				     source_type = CASE WHEN source_type = '' THEN 'product' ELSE source_type END,
				     source_reference = CASE WHEN source_reference IS NULL OR source_reference = '' THEN CONCAT('product:', source_product_id) ELSE source_reference END,
				     workspace_provider_id = CASE WHEN shadow_product_id IS NOT NULL AND (workspace_provider_id IS NULL OR workspace_provider_id = '') THEN 'woocommerce-core' ELSE workspace_provider_id END,
				     workspace_type = CASE WHEN shadow_product_id IS NOT NULL AND (workspace_type IS NULL OR workspace_type = '') THEN 'shadow_product' ELSE workspace_type END,
				     workspace_reference = CASE WHEN shadow_product_id IS NOT NULL AND (workspace_reference IS NULL OR workspace_reference = '') THEN CONCAT('shadow_product:', shadow_product_id) ELSE workspace_reference END,
				     source_product_id = 0,
				     shadow_product_id = NULL
				 WHERE source_product_id > 0 OR shadow_product_id IS NOT NULL",
					$tables['recording_sessions']
				)
			);

			if ( false === $result ) {
				throw new \RuntimeException( 'Unable to migrate legacy recording identities: ' . $wpdb->last_error );
			}
		}
	}

	/**
	 * Checks whether a legacy column exists before running compatibility SQL.
	 *
	 * @param \wpdb  $wpdb WordPress database connection.
	 * @param string $table Table name.
	 * @param string $column Column name.
	 */
	private function columnExists( \wpdb $wpdb, string $table, string $column ): bool {
		$found = $wpdb->get_var(
			$wpdb->prepare(
				'SHOW COLUMNS FROM %i LIKE %s',
				$table,
				$column
			)
		);

		if ( '' !== (string) $wpdb->last_error ) {
			throw new \RuntimeException( 'Unable to inspect legacy schema columns: ' . $wpdb->last_error );
		}

		return null !== $found;
	}
}
