<?php
/**
 * WPDB execution run repository.
 *
 * @package ScheduledChanges
 */

namespace ScheduledChanges\Repository;

// 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\TableNames;
use ScheduledChanges\Domain\Operation\Operation;
use ScheduledChanges\Domain\Run\FailureClassification;
use ScheduledChanges\Domain\Run\OperationRunStatus;
use ScheduledChanges\Domain\Run\Run;
use ScheduledChanges\Domain\Run\RunStatus;
use ScheduledChanges\Domain\Run\TriggerSource;
use ScheduledChanges\Domain\Shared\UtcTimestamp;
use ScheduledChanges\Domain\Shared\Uuid;
use ScheduledChanges\Support\EnumValue;
use ScheduledChanges\Support\Json;
use ScheduledChanges\Validation\ValidationReport;

/**
 * Persists runs and operation outcomes in scheduler-owned tables.
 */
final class WpdbRunRepository implements RunRepository {
	private \wpdb $wpdb;

	/**
	 * @var array<string,string>
	 */
	private array $tables;

	public function __construct( ?\wpdb $database = null, ?TableNames $tables = null ) {
		$database = \ScheduledChanges\Support\WpdbConnection::resolve( $database );

		$this->wpdb   = $database;
		$this->tables = ( $tables ?? new TableNames( $database ) )->all();
	}

	public function create( Run $run ): int {
		if ( null !== $run->id() ) {
			throw new \InvalidArgumentException( 'Cannot create an already-persisted run.' );
		}

		$scheduled_at = $run->scheduledAt();
		$started_at   = $run->startedAt();
		$completed_at = $run->completedAt();
		$inserted     = $this->wpdb->insert(
			$this->tables['runs'],
			array(
				'uuid'                    => $run->uuid()->value(),
				'launch_id'               => $run->launchId(),
				'launch_version_id'       => $run->launchVersionId(),
				'action_scheduler_id'     => $run->actionSchedulerId(),
				'trigger_source'          => $run->triggerSource(),
				'attempt_number'          => $run->attemptNumber(),
				'scheduled_at_utc'        => null === $scheduled_at ? null : $scheduled_at->toStorage(),
				'started_at_utc'          => null === $started_at ? null : $started_at->toStorage(),
				'completed_at_utc'        => null === $completed_at ? null : $completed_at->toStorage(),
				'status'                  => $run->status(),
				'lock_token'              => $run->lockToken(),
				'environment_fingerprint' => $run->environmentFingerprint(),
				'validation_json'         => Json::encode( $run->validation() ),
				'result_json'             => Json::encode( $run->result() ),
				'warning_count'           => $run->warningCount(),
				'error_count'             => $run->errorCount(),
				'rollback_run_id'         => $run->rollbackRunId(),
			),
			array( '%s', '%d', '%d', '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d' )
		);

		if ( false === $inserted ) {
			throw new RepositoryException( 'Unable to create execution run: ' . $this->wpdb->last_error );
		}

		return (int) $this->wpdb->insert_id;
	}

	public function findById( int $id ): ?Run {
		$sql = $this->wpdb->prepare(
			"SELECT * FROM {$this->tables['runs']} WHERE id = %d LIMIT 1",
			$id
		);
		$row = $this->wpdb->get_row( $sql, ARRAY_A );

		if ( null === $row ) {
			return null;
		}

		return $this->hydrate( $row );
	}

	public function findLatestSuccessfulForVersion( int $version_id ): ?Run {
		$sql = $this->wpdb->prepare(
			"SELECT * FROM {$this->tables['runs']}
				WHERE launch_version_id = %d
					AND status IN (%s, %s)
				ORDER BY completed_at_utc DESC, id DESC
				LIMIT 1",
			$version_id,
			RunStatus::SUCCESSFUL,
			RunStatus::SUCCESSFUL_WITH_WARNINGS
		);
		$row = $this->wpdb->get_row( $sql, ARRAY_A );

		return null === $row ? null : $this->hydrate( $row );
	}

	public function operationResults( int $run_id ): array {
		$sql  = $this->wpdb->prepare(
			"SELECT o.operation_uuid, ro.result_json
				FROM {$this->tables['run_operations']} ro
				INNER JOIN {$this->tables['operations']} o ON o.id = ro.operation_id
				WHERE ro.run_id = %d",
			$run_id
		);
		$rows = $this->wpdb->get_results( $sql, ARRAY_A );
		$out  = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$out[ (string) $row['operation_uuid'] ] = Json::decodeObject( (string) $row['result_json'] );
		}

		return $out;
	}

	public function linkRollback( int $source_run_id, int $rollback_run_id ): void {
		$this->update(
			$source_run_id,
			array( 'rollback_run_id' => $rollback_run_id ),
			array( '%d' )
		);
	}

	/**
	 * @param array<string,mixed> $row Run row.
	 */
	private function hydrate( array $row ): Run {
		return new Run(
			(int) $row['id'],
			new Uuid( (string) $row['uuid'] ),
			(int) $row['launch_id'],
			(int) $row['launch_version_id'],
			null === $row['action_scheduler_id'] ? null : (int) $row['action_scheduler_id'],
			\ScheduledChanges\Support\EnumValue::from( \ScheduledChanges\Domain\Run\TriggerSource::class, (string) $row['trigger_source'] ),
			(int) $row['attempt_number'],
			$this->timestamp( $row['scheduled_at_utc'] ),
			$this->timestamp( $row['started_at_utc'] ),
			$this->timestamp( $row['completed_at_utc'] ),
			\ScheduledChanges\Support\EnumValue::from( \ScheduledChanges\Domain\Run\RunStatus::class, (string) $row['status'] ),
			null === $row['lock_token'] ? null : (string) $row['lock_token'],
			null === $row['environment_fingerprint'] ? null : (string) $row['environment_fingerprint'],
			Json::decodeObject( (string) $row['validation_json'] ),
			Json::decodeObject( (string) $row['result_json'] ),
			(int) $row['warning_count'],
			(int) $row['error_count'],
			null === $row['rollback_run_id'] ? null : (int) $row['rollback_run_id']
		);
	}

	public function nextAttemptNumber( int $version_id, UtcTimestamp $occurrence_at ): int {
		$sql = $this->wpdb->prepare(
			"SELECT COALESCE(MAX(attempt_number), 0)
				FROM {$this->tables['runs']}
				WHERE launch_version_id = %d
					AND scheduled_at_utc = %s",
			$version_id,
			$occurrence_at->toStorage()
		);

		return (int) $this->wpdb->get_var( $sql ) + 1;
	}

	public function transition( int $id, array $expected_statuses, string $status, array $values = array(), array $formats = array() ): bool {
		if ( array() === $expected_statuses ) {
			throw new \InvalidArgumentException( 'Run transitions require at least one expected prior status.' );
		}

		if ( count( $values ) !== count( $formats ) ) {
			throw new \InvalidArgumentException( 'Run transition values and formats must have the same length.' );
		}

		$status            = EnumValue::from( RunStatus::class, $status );
		$expected_statuses = array_map(
			static fn ( string $expected_status ): string => EnumValue::from( RunStatus::class, $expected_status ),
			array_values( $expected_statuses )
		);
		$values            = array_merge( array( 'status' => $status ), $values );
		$formats           = array_merge( array( '%s' ), $formats );
		$shape             = $this->transitionShape( array_keys( $values ), $formats, count( $expected_statuses ) );
		$params            = array_merge(
			array( $this->tables['runs'] ),
			array_values( $values ),
			array( $id ),
			$expected_statuses
		);

		switch ( $shape ) {
			case 'status|1':
				$updated = $this->wpdb->query(
					$this->wpdb->prepare(
						'UPDATE %i
						SET status = %s
						WHERE id = %d
							AND status IN (%s)',
						$params
					)
				);
				break;
			case 'status,started_at_utc|1':
				$updated = $this->wpdb->query(
					$this->wpdb->prepare(
						'UPDATE %i
						SET status = %s,
							started_at_utc = %s
						WHERE id = %d
							AND status IN (%s)',
						$params
					)
				);
				break;
			case 'status,validation_json|1':
				$updated = $this->wpdb->query(
					$this->wpdb->prepare(
						'UPDATE %i
						SET status = %s,
							validation_json = %s
						WHERE id = %d
							AND status IN (%s)',
						$params
					)
				);
				break;
			default:
				$updated = $this->wpdb->query(
					$this->wpdb->prepare(
						'UPDATE %i
						SET status = %s,
							validation_json = %s,
							result_json = %s,
							warning_count = %d,
							error_count = %d,
							completed_at_utc = %s
						WHERE id = %d
							AND status IN (%s, %s)',
						$params
					)
				);
		}

		if ( false === $updated ) {
			throw new RepositoryException( 'Unable to transition execution run: ' . $this->wpdb->last_error );
		}

		return 1 === (int) $updated;
	}

	/**
	 * Resolves one fixed compare-and-set transition shape.
	 *
	 * @param string[] $columns Ordered updated columns.
	 * @param string[] $formats Ordered value formats.
	 */
	private function transitionShape( array $columns, array $formats, int $expected_status_count ): string {
		$shape           = implode( ',', array_map( 'strval', $columns ) );
		$format_shape    = implode( ',', $formats );
		$expected_shapes = array(
			'status'                 => '%s',
			'status,started_at_utc'  => '%s,%s',
			'status,validation_json' => '%s,%s',
			'status,validation_json,result_json,warning_count,error_count,completed_at_utc' => '%s,%s,%s,%d,%d,%s',
		);

		if ( ! isset( $expected_shapes[ $shape ] ) || $expected_shapes[ $shape ] !== $format_shape ) {
			throw new \InvalidArgumentException( 'Unsupported run transition update shape.' );
		}

		if (
			( 'status,validation_json,result_json,warning_count,error_count,completed_at_utc' === $shape && 2 !== $expected_status_count )
			|| ( 'status,validation_json,result_json,warning_count,error_count,completed_at_utc' !== $shape && 1 !== $expected_status_count )
		) {
			throw new \InvalidArgumentException( 'Unsupported run transition status guard shape.' );
		}

		return $shape . '|' . (string) $expected_status_count;
	}

	public function markValidating( int $id, UtcTimestamp $started_at ): bool {
		return $this->transition(
			$id,
			array( RunStatus::PENDING ),
			RunStatus::VALIDATING,
			array(
				'started_at_utc' => $started_at->toStorage(),
			),
			array( '%s' )
		);
	}

	public function markRunning( int $id, ValidationReport $validation ): bool {
		return $this->transition(
			$id,
			array( RunStatus::VALIDATING ),
			RunStatus::RUNNING,
			array(
				'validation_json' => Json::encode( $validation->toArray() ),
			),
			array( '%s' )
		);
	}

	public function recordOperation(
		int $run_id,
		int $version_id,
		Operation $operation,
		string $status,
		int $attempt_number,
		UtcTimestamp $started_at,
		UtcTimestamp $completed_at,
		array $result,
		?string $classification,
		bool $retryable,
		bool $verified,
		int $duration_ms
	): void {
		$operation_id         = $this->operationId( $version_id, $operation->uuid()->value() );
		$payload              = array_merge(
			$result,
			array(
				'operation_uuid'         => $operation->uuid()->value(),
				'idempotency_key'        => $operation->idempotencyKey(),
				'failure_classification' => $classification,
				'retryable'              => $retryable,
				'verified'               => $verified,
				'duration_ms'            => $duration_ms,
			)
		);
		$result_json          = Json::encode( $payload );
		$warning_json         = Json::encode( array() );
		$error_json           = Json::encode( OperationRunStatus::FAILED === $status ? $payload : array() );
		$started_at_storage   = $started_at->toStorage();
		$completed_at_storage = $completed_at->toStorage();
		$sql                  = $this->wpdb->prepare(
			"INSERT INTO {$this->tables['run_operations']}
				(run_id, operation_id, status, attempt_number, started_at_utc, completed_at_utc, result_json, warning_json, error_json)
			VALUES (%d, %d, %s, %d, %s, %s, %s, %s, %s)
			ON DUPLICATE KEY UPDATE
				status = %s,
				attempt_number = %d,
				started_at_utc = %s,
				completed_at_utc = %s,
				result_json = %s,
				warning_json = %s,
				error_json = %s",
			$run_id,
			$operation_id,
			$status,
			$attempt_number,
			$started_at_storage,
			$completed_at_storage,
			$result_json,
			$warning_json,
			$error_json,
			$status,
			$attempt_number,
			$started_at_storage,
			$completed_at_storage,
			$result_json,
			$warning_json,
			$error_json
		);
		$inserted             = $this->wpdb->query( $sql );

		if ( false === $inserted ) {
			throw new RepositoryException( 'Unable to record operation result: ' . $this->wpdb->last_error );
		}
	}

	public function hasVerifiedSuccess(
		int $version_id,
		string $operation_uuid,
		UtcTimestamp $occurrence_at
	): bool {
		$sql = $this->wpdb->prepare(
			"SELECT ro.result_json
				FROM {$this->tables['run_operations']} ro
				INNER JOIN {$this->tables['operations']} o ON o.id = ro.operation_id
				INNER JOIN {$this->tables['runs']} r ON r.id = ro.run_id
				WHERE o.launch_version_id = %d
					AND o.operation_uuid = %s
					AND r.scheduled_at_utc = %s
					AND ro.status = %s",
			$version_id,
			$operation_uuid,
			$occurrence_at->toStorage(),
			OperationRunStatus::SUCCESSFUL
		);

		$rows = $this->wpdb->get_col( $sql );

		foreach ( $rows as $result_json ) {
			$result = Json::decodeObject( is_string( $result_json ) ? $result_json : null );

			if ( true === ( $result['verified'] ?? false ) ) {
				return true;
			}
		}

		return false;
	}

	public function finalize(
		int $id,
		string $status,
		ValidationReport $validation,
		array $result,
		int $warning_count,
		int $error_count,
		UtcTimestamp $completed_at
	): bool {
		if ( ! \ScheduledChanges\Domain\Run\RunStatus::terminal( $status ) ) {
			throw new \InvalidArgumentException( 'Runs can only be finalized with terminal statuses.' );
		}

		return $this->transition(
			$id,
			array( RunStatus::VALIDATING, RunStatus::RUNNING ),
			$status,
			array(
				'validation_json'  => Json::encode( $validation->toArray() ),
				'result_json'      => Json::encode( $result ),
				'warning_count'    => $warning_count,
				'error_count'      => $error_count,
				'completed_at_utc' => $completed_at->toStorage(),
			),
			array( '%s', '%s', '%d', '%d', '%s' )
		);
	}

	public function delayMetrics(): array {
		$row = $this->wpdb->get_row(
			"SELECT
				COUNT(*) AS sample_size,
				COALESCE(AVG(GREATEST(TIMESTAMPDIFF(SECOND, scheduled_at_utc, started_at_utc), 0)), 0) AS average_delay,
				COALESCE(MAX(GREATEST(TIMESTAMPDIFF(SECOND, scheduled_at_utc, started_at_utc), 0)), 0) AS maximum_delay
			FROM {$this->tables['runs']}
			WHERE scheduled_at_utc IS NOT NULL
				AND started_at_utc IS NOT NULL",
			ARRAY_A
		);

		return array(
			'average_delay_seconds' => (float) ( $row['average_delay'] ?? 0 ),
			'maximum_delay_seconds' => (int) ( $row['maximum_delay'] ?? 0 ),
			'sample_size'           => (int) ( $row['sample_size'] ?? 0 ),
		);
	}

	public function deleteById( int $id ): bool {
		$this->wpdb->delete( $this->tables['snapshots'], array( 'run_id' => $id ), array( '%d' ) );
		$this->wpdb->delete( $this->tables['run_operations'], array( 'run_id' => $id ), array( '%d' ) );
		$deleted = $this->wpdb->delete( $this->tables['runs'], array( 'id' => $id ), array( '%d' ) );

		if ( false === $deleted ) {
			throw new RepositoryException( 'Unable to delete execution run: ' . $this->wpdb->last_error );
		}

		return $deleted > 0;
	}

	/**
	 * Updates selected run columns.
	 *
	 * @param int                 $id Run ID.
	 * @param array<string,mixed> $values Updated columns.
	 * @param string[]            $formats Column formats.
	 */
	private function update( int $id, array $values, array $formats ): void {
		$updated = $this->wpdb->update(
			$this->tables['runs'],
			$values,
			array( 'id' => $id ),
			$formats,
			array( '%d' )
		);

		if ( false === $updated ) {
			throw new RepositoryException( 'Unable to update execution run: ' . $this->wpdb->last_error );
		}
	}

	private function operationId( int $version_id, string $operation_uuid ): int {
		$sql = $this->wpdb->prepare(
			"SELECT id FROM {$this->tables['operations']}
				WHERE launch_version_id = %d
					AND operation_uuid = %s
				LIMIT 1",
			$version_id,
			$operation_uuid
		);
		$id  = $this->wpdb->get_var( $sql );

		if ( null === $id ) {
			throw new RepositoryException( 'Normalized operation row was not found for execution.' );
		}

		return (int) $id;
	}

	/**
	 * @param mixed $value
	 */
	private function timestamp( $value ): ?UtcTimestamp {
		return null === $value || '' === $value
			? null
			: UtcTimestamp::fromStorage( (string) $value );
	}
}
