<?php
/**
 * Adds canonical recording source/workspace identity columns.
 *
 * @package ScheduledChanges
 */

namespace ScheduledChanges\Database\Migrations;

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

use ScheduledChanges\Database\Migration;
use ScheduledChanges\Database\TableNames;

/** Backfills recording identity columns added after early schema-3 installs. */
final class RecordingSessionIdentityColumnsMigration implements Migration {
	/** Returns the schema version introduced by this migration. */
	public function version(): int {
		return 4;
	}

	/**
	 * Adds missing canonical columns and backfills them from legacy product fields.
	 *
	 * @param \wpdb $wpdb WordPress database connection.
	 */
	public function up( \wpdb $wpdb ): void {
		$table = ( new TableNames() )->get( 'recording_sessions' );

		if ( ! $this->columnExists( $wpdb, $table, 'source_id' ) ) {
			$this->query( $wpdb, $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN source_id bigint(20) unsigned NOT NULL DEFAULT 0 AFTER uuid', $table ) );
		}

		if ( ! $this->columnExists( $wpdb, $table, 'workspace_id' ) ) {
			$this->query( $wpdb, $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN workspace_id bigint(20) unsigned NULL AFTER source_id', $table ) );
		}

		$has_legacy_source    = $this->columnExists( $wpdb, $table, 'source_product_id' );
		$has_legacy_workspace = $this->columnExists( $wpdb, $table, 'shadow_product_id' );

		$source_expression = $has_legacy_source
			? "CASE
				WHEN source_id > 0 THEN source_id
				WHEN source_product_id > 0 THEN source_product_id
				WHEN source_reference REGEXP ':[0-9]+$' THEN CAST(SUBSTRING_INDEX(source_reference, ':', -1) AS UNSIGNED)
				ELSE source_id
			END"
			: "CASE
				WHEN source_id > 0 THEN source_id
				WHEN source_reference REGEXP ':[0-9]+$' THEN CAST(SUBSTRING_INDEX(source_reference, ':', -1) AS UNSIGNED)
				ELSE source_id
			END";

		$workspace_expression = $has_legacy_workspace
			? "CASE
				WHEN workspace_id > 0 THEN workspace_id
				WHEN shadow_product_id > 0 THEN shadow_product_id
				WHEN workspace_reference REGEXP ':[0-9]+$' THEN CAST(SUBSTRING_INDEX(workspace_reference, ':', -1) AS UNSIGNED)
				ELSE workspace_id
			END"
			: "CASE
				WHEN workspace_id > 0 THEN workspace_id
				WHEN workspace_reference REGEXP ':[0-9]+$' THEN CAST(SUBSTRING_INDEX(workspace_reference, ':', -1) AS UNSIGNED)
				ELSE workspace_id
			END";

		$this->query(
			$wpdb,
			$wpdb->prepare(
				"UPDATE %i
			 SET source_id = {$source_expression},
			     workspace_id = {$workspace_expression}",
				$table
			)
		);

		$this->replaceIndex( $wpdb, $table, 'source_status', 'KEY source_status (source_id, status)' );
		$this->replaceIndex( $wpdb, $table, 'workspace_id', 'KEY workspace_id (workspace_id)' );
	}

	/**
	 * Checks whether a column exists.
	 */
	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 recording session columns: ' . $wpdb->last_error );
		}

		return null !== $found;
	}

	/**
	 * Replaces an index after verifying its current definition.
	 */
	private function replaceIndex( \wpdb $wpdb, string $table, string $index, string $definition ): void {
		if ( $this->indexExists( $wpdb, $table, $index ) ) {
			$this->query( $wpdb, $wpdb->prepare( 'ALTER TABLE %i DROP INDEX %i', $table, $index ) );
		}

		$this->query( $wpdb, $wpdb->prepare( "ALTER TABLE %i ADD {$definition}", $table ) );
	}

	/**
	 * Checks whether an index exists.
	 */
	private function indexExists( \wpdb $wpdb, string $table, string $index ): bool {
		$found = $wpdb->get_var(
			$wpdb->prepare(
				'SHOW INDEX FROM %i WHERE Key_name = %s',
				$table,
				$index
			)
		);

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

		return null !== $found;
	}

	/**
	 * Runs a schema query and reports wpdb errors.
	 */
	private function query( \wpdb $wpdb, string $sql ): void {
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema SQL is prepared at call sites because identifiers cannot be escaped with value placeholders.
		$result = $wpdb->query( $sql );

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