<?php
/**
 * Read models for the WordPress administration application.
 *
 * @package ScheduledChanges
 */

namespace ScheduledChanges\Repository;

// phpcs:disable WordPress.DB -- Scheduler-owned custom tables require direct wpdb queries.
// phpcs:disable PluginCheck.Security.DirectDB -- Table names are resolved from scheduler-owned allowlists.

use ScheduledChanges\Database\TableNames;
use ScheduledChanges\Domain\Operation\TargetDefinition;
use ScheduledChanges\Domain\Shared\Uuid;
use ScheduledChanges\Domain\Snapshot\SnapshotKind;
use ScheduledChanges\Extension\PublicHooks;
use ScheduledChanges\Provider\ProviderAuthorization;
use ScheduledChanges\Provider\ProviderAuthorizationService;
use ScheduledChanges\Support\ValueRedactor;

/**
 * Builds bounded dashboard snapshots from scheduler-owned tables.
 */
final class AdminDashboardRepository {
	/**
	 * WordPress database connection.
	 *
	 * @var \wpdb
	 */
	private \wpdb $wpdb;

	/**
	 * Scheduler table names.
	 *
	 * @var array<string,string>
	 */
	private array $tables;
	private ProviderAuthorization $authorization;
	private AdminDashboardDisplayFormatter $formatter;
	/** @var int[]|null */
	private ?array $hidden_version_ids = null;
	private const DETAIL_ROW_LIMIT     = 100;

	/**
	 * Creates the read-model repository.
	 *
	 * @param \wpdb|null                 $database WordPress database connection.
	 * @param TableNames|null            $tables Table resolver.
	 * @param ProviderAuthorization|null $authorization Provider-owned target permissions.
	 */
	public function __construct( ?\wpdb $database = null, ?TableNames $tables = null, ?ProviderAuthorization $authorization = null ) {
		$database = \ScheduledChanges\Support\WpdbConnection::resolve( $database );

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

	/**
	 * Returns recent launch rows for the launch list and editor summary.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function launches( int $limit = 25, int $offset = 0 ): array {
		$limit      = $this->limit( $limit );
		$offset     = max( 0, $offset );
		$visibility = $this->versionVisibilitySql( 'l.current_version_id' );
		$sql        = $this->wpdb->prepare(
			"SELECT l.*, u.display_name AS owner_name,
				v.status AS version_status,
				v.validation_summary_json,
				(
					SELECT COUNT(DISTINCT lt.target_reference)
					FROM {$this->tables['launch_targets']} lt
					WHERE lt.launch_version_id = l.current_version_id
				) AS target_count,
				(
					SELECT r.status
					FROM {$this->tables['runs']} r
					WHERE r.launch_id = l.id
					ORDER BY r.completed_at_utc DESC, r.id DESC
					LIMIT 1
				) AS last_run_status
			FROM {$this->tables['launches']} l
			LEFT JOIN {$this->wpdb->users} u ON u.ID = l.owner_id
			LEFT JOIN {$this->tables['launch_versions']} v ON v.id = l.current_version_id
			WHERE {$visibility}
			ORDER BY l.updated_at_utc DESC, l.id DESC
			LIMIT %d OFFSET %d",
			$limit,
			$offset
		);
		$rows       = $this->wpdb->get_results( $sql, ARRAY_A );

		return $this->formatLaunchRows( is_array( $rows ) ? $rows : array() );
	}

	/**
	 * Adds small display-safe target summaries to visible calendar rows.
	 *
	 * @param array<int,array<string,mixed>> $launches Calendar launch rows.
	 * @return array<int,array<string,mixed>>
	 */
	private function withCalendarTargets( array $launches ): array {
		$version_ids = array_values(
			array_filter(
				array_unique(
					array_map(
						static fn ( array $launch ): int => (int) ( $launch['versionId'] ?? 0 ),
						$launches
					)
				),
				static fn ( int $version_id ): bool => $version_id > 0
			)
		);

		$targets_by_version = $this->calendarTargetsForVersions( $version_ids );

		foreach ( $launches as &$launch ) {
			$version_id                 = (int) ( $launch['versionId'] ?? 0 );
			$targets                    = $targets_by_version[ $version_id ] ?? array();
			$launch['calendarTargets']  = $targets;
			$launch['primaryItemName']  = (string) ( $targets[0]['name'] ?? '' );
			$launch['primaryItemType']  = (string) ( $targets[0]['typeLabel'] ?? '' );
			$launch['primaryTargetKey'] = (string) ( $targets[0]['key'] ?? '' );
		}
		unset( $launch );

		return $launches;
	}

	/**
	 * Returns calendar-safe target summaries keyed by launch version ID.
	 *
	 * @param int[] $version_ids Launch version IDs.
	 * @return array<int,array<int,array<string,string>>>
	 */
	private function calendarTargetsForVersions( array $version_ids ): array {
		$version_ids = array_values(
			array_filter(
				array_unique( array_map( 'intval', $version_ids ) ),
				static fn ( int $version_id ): bool => $version_id > 0
			)
		);

		if ( array() === $version_ids ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $version_ids ), '%d' ) );
		$rows         = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT launch_version_id, target_uuid, target_reference,
					target_id, target_type, subtype,
					parent_reference, provider_id,
					baseline_fingerprint, execution_order
				FROM {$this->tables['launch_targets']}
				WHERE launch_version_id IN ({$placeholders})
				ORDER BY launch_version_id ASC, execution_order ASC, id ASC",
				...$version_ids
			),
			ARRAY_A
		);
		$targets      = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$version_id = (int) ( $row['launch_version_id'] ?? 0 );

			if ( $version_id <= 0 ) {
				continue;
			}

			$formatted = $this->formatter->targetRow( $row, 'calendar_target', false );
			$key       = hash(
				'sha256',
				implode(
					'|',
					array(
						(string) ( $row['provider_id'] ?? '' ),
						(string) ( $row['target_type'] ?? '' ),
						(string) ( $row['target_reference'] ?? '' ),
						(string) ( $row['target_id'] ?? '' ),
					)
				)
			);

			$targets[ $version_id ][] = array(
				'key'       => substr( $key, 0, 16 ),
				'name'      => (string) ( $formatted['name'] ?? __( 'Item', 'jjs-post-scheduler' ) ),
				'typeLabel' => (string) ( $formatted['typeLabel'] ?? __( 'Item', 'jjs-post-scheduler' ) ),
			);
		}

		return $targets;
	}

	/**
	 * Counts launch rows for REST pagination.
	 */
	public function launchCount(): int {
		$visibility = $this->versionVisibilitySql( 'current_version_id' );

		return (int) $this->wpdb->get_var( "SELECT COUNT(*) FROM {$this->tables['launches']} WHERE {$visibility}" );
	}

	/**
	 * Returns one launch row by public UUID.
	 *
	 * @param string $uuid Public launch UUID.
	 * @return array<string,mixed>|null
	 */
	public function launchByUuid( string $uuid ): ?array {
		$visibility = $this->versionVisibilitySql( 'l.current_version_id' );
		$sql        = $this->wpdb->prepare(
			"SELECT l.*, u.display_name AS owner_name,
				v.status AS version_status,
				v.validation_summary_json,
				(
					SELECT COUNT(DISTINCT lt.target_reference)
					FROM {$this->tables['launch_targets']} lt
					WHERE lt.launch_version_id = l.current_version_id
				) AS target_count,
				(
					SELECT r.status
					FROM {$this->tables['runs']} r
					WHERE r.launch_id = l.id
					ORDER BY r.completed_at_utc DESC, r.id DESC
					LIMIT 1
				) AS last_run_status
			FROM {$this->tables['launches']} l
			LEFT JOIN {$this->wpdb->users} u ON u.ID = l.owner_id
			LEFT JOIN {$this->tables['launch_versions']} v ON v.id = l.current_version_id
			WHERE l.uuid = %s AND {$visibility}
			LIMIT 1",
			$uuid
		);
		$row        = $this->wpdb->get_row( $sql, ARRAY_A );

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

	/**
	 * Returns launch rows that have a start or end timestamp for the calendar.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function calendarItems( int $limit = 50, int $offset = 0 ): array {
		$limit      = $this->limit( $limit );
		$offset     = max( 0, $offset );
		$visibility = $this->versionVisibilitySql( 'l.current_version_id' );
		$sql        = $this->wpdb->prepare(
			"SELECT l.*, u.display_name AS owner_name,
				v.status AS version_status,
				v.validation_summary_json,
				(
					SELECT COUNT(DISTINCT lt.target_reference)
					FROM {$this->tables['launch_targets']} lt
					WHERE lt.launch_version_id = l.current_version_id
				) AS target_count,
				(
					SELECT r.status
					FROM {$this->tables['runs']} r
					WHERE r.launch_id = l.id
					ORDER BY r.completed_at_utc DESC, r.id DESC
					LIMIT 1
				) AS last_run_status
			FROM {$this->tables['launches']} l
			LEFT JOIN {$this->wpdb->users} u ON u.ID = l.owner_id
			LEFT JOIN {$this->tables['launch_versions']} v ON v.id = l.current_version_id
			WHERE (l.start_at_utc IS NOT NULL OR l.end_at_utc IS NOT NULL)
				AND {$visibility}
			ORDER BY
				CASE
					WHEN l.start_at_utc IS NULL THEN l.end_at_utc
					ELSE l.start_at_utc
				END ASC,
				l.id ASC
			LIMIT %d OFFSET %d",
			$limit,
			$offset
		);
		$rows       = $this->wpdb->get_results( $sql, ARRAY_A );
		$items      = $this->formatLaunchRows( is_array( $rows ) ? $rows : array() );

		return $this->withCalendarTargets( $items );
	}

	/**
	 * Returns dashboard summary counters for the custom admin shell.
	 *
	 * @return array<string,mixed>
	 */
	public function summary(): array {
		$now          = gmdate( 'Y-m-d H:i:s' );
		$week_start   = gmdate( 'Y-m-d H:i:s', strtotime( '-7 days' ) );
		$visibility   = $this->versionVisibilitySql( 'l.current_version_id' );
		$calendar_sql = '(l.start_at_utc IS NOT NULL OR l.end_at_utc IS NOT NULL)';
		$summary      = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT
					COALESCE(SUM(CASE WHEN l.status = %s THEN 1 ELSE 0 END), 0) AS scheduled_count,
					COALESCE(SUM(CASE WHEN lr.status IN (%s, %s, %s) AND lr.completed_at_utc >= %s THEN 1 ELSE 0 END), 0) AS successful_count,
					COALESCE(SUM(CASE WHEN l.status IN (%s, %s, %s, %s) OR lr.status IN (%s, %s, %s, %s) THEN 1 ELSE 0 END), 0) AS failed_count,
					COALESCE(SUM(COALESCE(tc.target_count, 0)), 0) AS affected_targets
				FROM {$this->tables['launches']} l
				LEFT JOIN {$this->tables['runs']} lr ON lr.id = (
					SELECT latest_run.id
					FROM {$this->tables['runs']} latest_run
					WHERE latest_run.launch_id = l.id
					ORDER BY COALESCE(latest_run.completed_at_utc, latest_run.started_at_utc, latest_run.scheduled_at_utc, '1970-01-01 00:00:00') DESC, latest_run.id DESC
					LIMIT 1
				)
				LEFT JOIN (
					SELECT launch_version_id, COUNT(DISTINCT target_reference) AS target_count
					FROM {$this->tables['launch_targets']}
					GROUP BY launch_version_id
				) tc ON tc.launch_version_id = l.current_version_id
				WHERE {$calendar_sql} AND {$visibility}",
				'scheduled',
				'successful',
				'successful_with_warnings',
				'success',
				$week_start,
				'failed',
				'partially_failed',
				'stale',
				'dead_letter',
				'failed',
				'partially_failed',
				'stale',
				'dead_letter'
			),
			ARRAY_A
		);
		$next_row     = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT l.*, u.display_name AS owner_name,
					v.status AS version_status,
					v.validation_summary_json,
					COALESCE(tc.target_count, 0) AS target_count,
					lr.status AS last_run_status
				FROM {$this->tables['launches']} l
				LEFT JOIN {$this->wpdb->users} u ON u.ID = l.owner_id
				LEFT JOIN {$this->tables['launch_versions']} v ON v.id = l.current_version_id
				LEFT JOIN {$this->tables['runs']} lr ON lr.id = (
					SELECT latest_run.id
					FROM {$this->tables['runs']} latest_run
					WHERE latest_run.launch_id = l.id
					ORDER BY COALESCE(latest_run.completed_at_utc, latest_run.started_at_utc, latest_run.scheduled_at_utc, '1970-01-01 00:00:00') DESC, latest_run.id DESC
					LIMIT 1
				)
				LEFT JOIN (
					SELECT launch_version_id, COUNT(DISTINCT target_reference) AS target_count
					FROM {$this->tables['launch_targets']}
					GROUP BY launch_version_id
				) tc ON tc.launch_version_id = l.current_version_id
				WHERE {$calendar_sql}
					AND {$visibility}
					AND l.status = %s
					AND l.start_at_utc IS NOT NULL
					AND l.start_at_utc >= %s
				ORDER BY l.start_at_utc ASC, l.id ASC
				LIMIT 1",
				'scheduled',
				$now
			),
			ARRAY_A
		);
		$summary      = is_array( $summary ) ? $summary : array();
		$next_row     = is_array( $next_row ) ? $next_row : null;
		$next         = null === $next_row ? null : $this->formatLaunchRow( $next_row );

		return array(
			'nextChange'      => $next,
			'scheduledCount'  => (int) ( $summary['scheduled_count'] ?? 0 ),
			'successfulCount' => (int) ( $summary['successful_count'] ?? 0 ),
			'failedCount'     => (int) ( $summary['failed_count'] ?? 0 ),
			'affectedTargets' => (int) ( $summary['affected_targets'] ?? 0 ),
		);
	}

	/**
	 * Returns expanded launch detail rows keyed by launch UUID.
	 *
	 * @return array<string,array<string,mixed>>
	 */
	public function launchDetails( int $limit = 50 ): array {
		$details = array();

		foreach ( $this->calendarItems( $limit ) as $launch ) {
			$uuid = (string) ( $launch['uuid'] ?? '' );

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

			$detail = $this->detailFromLaunchRow( $launch );

			if ( null !== $detail ) {
				$details[ $uuid ] = $detail;
			}
		}

		return $details;
	}

	/**
	 * Returns one expanded launch detail by canonical UUID.
	 *
	 * @return array<string,mixed>|null
	 */
	public function launchDetailByUuid( string $uuid, bool $include_diagnostics = false ): ?array {
		$launch = $this->launchByUuid( $uuid );

		return null === $launch ? null : $this->detailFromLaunchRow( $launch, $include_diagnostics );
	}

	/**
	 * Returns target-centric schedule summaries.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function targetSummaries( int $limit = 50 ): array {
		$limit      = $this->limit( $limit );
		$visibility = $this->versionVisibilitySql( 'lt.launch_version_id' );
		$rows       = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT
					MIN(lt.id) AS id,
					lt.target_reference,
					lt.target_id,
					lt.target_type,
					lt.subtype,
					lt.parent_reference,
					lt.provider_id,
					SUM(CASE WHEN l.status = %s THEN 1 ELSE 0 END) AS upcoming_count,
					SUM(CASE WHEN l.status IN (%s, %s, %s, %s) THEN 1 ELSE 0 END) AS failed_count,
					MIN(CASE WHEN l.status = %s THEN l.start_at_utc ELSE NULL END) AS next_change
				FROM {$this->tables['launch_targets']} lt
				INNER JOIN {$this->tables['launches']} l ON l.current_version_id = lt.launch_version_id
				WHERE {$visibility}
					AND (COALESCE(lt.target_id, 0) > 0 OR COALESCE(lt.target_reference, '') <> '')
				GROUP BY
					lt.provider_id,
					lt.target_type,
					lt.target_reference,
					lt.target_id,
					lt.subtype,
					lt.parent_reference
				ORDER BY
					CASE WHEN MIN(CASE WHEN l.status = %s THEN l.start_at_utc ELSE NULL END) IS NULL THEN 1 ELSE 0 END ASC,
					MIN(CASE WHEN l.status = %s THEN l.start_at_utc ELSE NULL END) ASC,
					MIN(lt.id) ASC
				LIMIT %d",
				'scheduled',
				'failed',
				'partially_failed',
				'stale',
				'dead_letter',
				'scheduled',
				'scheduled',
				'scheduled',
				$limit
			),
			ARRAY_A
		);

		return array_map(
			function ( array $row ): array {
				return $this->formatter->targetRow( $row, 'target_summary', false ) + array(
					'upcomingCount' => (int) ( $row['upcoming_count'] ?? 0 ),
					'failedCount'   => (int) ( $row['failed_count'] ?? 0 ),
					'nextChange'    => (string) ( $row['next_change'] ?? '' ),
					'history'       => $this->targetHistoryRows( $row, 6 ),
				);
			},
			is_array( $rows ) ? $rows : array()
		);
	}

	/**
	 * Returns targets that can be selected when starting a recording from the dashboard.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function recordableTargets( int $limit = 100 ): array {
		return PublicHooks::recordableTargets( array(), $this->limit( $limit ) );
	}

	/**
	 * Formats launch rows with object-level action decisions.
	 *
	 * @param array<int,array<string,mixed>> $rows Launch database rows.
	 * @return array<int,array<string,mixed>>
	 */
	private function formatLaunchRows( array $rows ): array {
		$permissions = $this->launchActionPermissions(
			array_map(
				static fn ( array $row ): int => (int) ( $row['current_version_id'] ?? 0 ),
				$rows
			)
		);

		return array_map(
			function ( array $row ) use ( $permissions ): array {
				$version_id = (int) ( $row['current_version_id'] ?? 0 );

				return $this->formatter->launchRow(
					array_merge( $row, $permissions[ $version_id ] ?? array() )
				);
			},
			$rows
		);
	}

	/**
	 * Formats one launch row with object-level action decisions.
	 *
	 * @param array<string,mixed> $row Launch database row.
	 * @return array<string,mixed>
	 */
	private function formatLaunchRow( array $row ): array {
		return $this->formatLaunchRows( array( $row ) )[0];
	}

	/**
	 * Returns whether each current version can be changed by the current actor.
	 *
	 * @param int[] $version_ids Launch version IDs.
	 * @return array<int,array{can_schedule_targets:bool,can_execute_targets:bool,can_rollback_targets:bool}>
	 */
	private function launchActionPermissions( array $version_ids ): array {
		$version_ids = array_values(
			array_filter(
				array_unique( array_map( 'intval', $version_ids ) ),
				static fn ( int $version_id ): bool => $version_id > 0
			)
		);

		if ( array() === $version_ids ) {
			return array();
		}

		$permissions  = array_fill_keys(
			$version_ids,
			array(
				'can_schedule_targets' => false,
				'can_execute_targets'  => false,
				'can_rollback_targets' => false,
			)
		);
		$seen         = array();
		$placeholders = implode( ',', array_fill( 0, count( $version_ids ), '%d' ) );
		$rows         = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT launch_version_id, target_uuid, target_reference,
				target_id,
				target_type,
				subtype,
				parent_reference, provider_id, baseline_fingerprint, execution_order
				FROM {$this->tables['launch_targets']}
				WHERE launch_version_id IN ({$placeholders})",
				...$version_ids
			),
			ARRAY_A
		);
		$actor_id     = get_current_user_id();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$version_id = (int) ( $row['launch_version_id'] ?? 0 );

			if ( ! isset( $permissions[ $version_id ] ) ) {
				continue;
			}

			if ( empty( $seen[ $version_id ] ) ) {
				$seen[ $version_id ]        = true;
				$permissions[ $version_id ] = array(
					'can_schedule_targets' => true,
					'can_execute_targets'  => true,
					'can_rollback_targets' => true,
				);
			}

			try {
				$target = $this->storedTarget( $row );
				$permissions[ $version_id ]['can_schedule_targets'] = $permissions[ $version_id ]['can_schedule_targets']
					&& $this->authorization->canSchedule( $actor_id, $target );
				$permissions[ $version_id ]['can_execute_targets']  = $permissions[ $version_id ]['can_execute_targets']
					&& $this->authorization->canExecute( $actor_id, $target );
				$permissions[ $version_id ]['can_rollback_targets'] = $permissions[ $version_id ]['can_rollback_targets']
					&& $this->authorization->canRollback( $actor_id, $target );
			} catch ( \Throwable $throwable ) {
				unset( $throwable );
				$permissions[ $version_id ] = array(
					'can_schedule_targets' => false,
					'can_execute_targets'  => false,
					'can_rollback_targets' => false,
				);
			}
		}

		return $permissions;
	}

	/**
	 * Returns recent execution run rows for the logs screen.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function runs( int $limit = 25, bool $include_diagnostics = false ): array {
		$limit      = $this->limit( $limit );
		$visibility = $this->versionVisibilitySql( 'r.launch_version_id' );
		$sql        = $this->wpdb->prepare(
			"SELECT r.*, l.uuid AS launch_uuid, l.name AS launch_name,
				(SELECT COUNT(*) FROM {$this->tables['snapshots']} s WHERE s.run_id = r.id) AS snapshot_count
			FROM {$this->tables['runs']} r
			LEFT JOIN {$this->tables['launches']} l ON l.id = r.launch_id
			WHERE {$visibility}
			ORDER BY r.started_at_utc DESC, r.id DESC
			LIMIT %d",
			$limit
		);
		$rows       = $this->wpdb->get_results( $sql, ARRAY_A );

		return $this->formatRunRows( is_array( $rows ) ? $rows : array(), $include_diagnostics );
	}

	/**
	 * Returns recent Record Changes sessions for the recording dashboard.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function recordings( int $limit = 25, bool $include_diagnostics = false ): array {
		$limit = $this->limit( $limit );
		$sql   = $this->wpdb->prepare(
			"SELECT rs.*
			FROM {$this->tables['recording_sessions']} rs
			ORDER BY rs.updated_at_utc DESC, rs.id DESC
			LIMIT %d",
			$limit
		);
		$rows  = $this->wpdb->get_results( $sql, ARRAY_A );

		$actor_id = get_current_user_id();
		$rows     = array_values(
			array_filter(
				is_array( $rows ) ? $rows : array(),
				fn ( array $row ): bool => $this->canInspectRecordingRow( $actor_id, $row )
			)
		);
		$rows     = $this->withRecordingPostTitles( $rows );

		return array_map(
			function ( array $row ) use ( $include_diagnostics ): array {
				$formatted = $this->formatter->recordingRow(
					$row,
					fn ( array $target, string $context ): array => $this->formatter->targetRow( $target, $context ),
					fn ( string $reference, int $fallback = 0 ): int => $this->formatter->referenceId( $reference, $fallback ),
					fn ( int $user_id ): string => $this->formatter->ownerName( $user_id ),
					fn ( $value ): string => $this->formatter->displayText( $value )
				);

				if ( ! $include_diagnostics ) {
					unset( $formatted['diagnostics'] );
				}

				return $formatted;
			},
			$rows
		);
	}

	/**
	 * Builds a SQL condition excluding native versions with unreadable targets.
	 */
	private function versionVisibilitySql( string $column ): string {
		$hidden = $this->hiddenVersionIds();

		if ( array() === $hidden ) {
			return '1 = 1';
		}

		return sprintf(
			'(%1$s IS NULL OR %1$s NOT IN (%2$s))',
			$column,
			implode( ',', array_map( 'intval', $hidden ) )
		);
	}

	/**
	 * Returns immutable version IDs containing a target the viewer cannot read.
	 *
	 * @return int[]
	 */
	private function hiddenVersionIds(): array {
		if ( null !== $this->hidden_version_ids ) {
			return $this->hidden_version_ids;
		}

		$version_ids = $this->visibilityCandidateVersionIds();

		if ( array() === $version_ids ) {
			$this->hidden_version_ids = array();

			return $this->hidden_version_ids;
		}

		$placeholders = implode( ',', array_fill( 0, count( $version_ids ), '%d' ) );
		$rows         = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT launch_version_id, target_uuid, target_reference,
				target_id,
				target_type,
				subtype,
				parent_reference, provider_id, baseline_fingerprint, execution_order
				FROM {$this->tables['launch_targets']}
				WHERE launch_version_id IN ({$placeholders})",
				...$version_ids
			),
			ARRAY_A
		);
		$hidden       = array();
		$actor        = get_current_user_id();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$version_id = (int) ( $row['launch_version_id'] ?? 0 );

			if ( $version_id > 0 && ! $this->canInspectTargetRow( $actor, $row ) ) {
				$hidden[ $version_id ] = $version_id;
			}
		}

		$this->hidden_version_ids = array_values( $hidden );

		return $this->hidden_version_ids;
	}

	/**
	 * Returns every version candidate referenced by a dashboard surface.
	 *
	 * The query count is fixed (one per surface) and the result is de-duplicated
	 * before target authorization is evaluated. Do not cap this set: doing so can
	 * expose older launch references when a user paginates beyond the cap.
	 *
	 * @return int[]
	 */
	private function visibilityCandidateVersionIds(): array {
		$ids  = array();
		$sqls = array(
			"SELECT current_version_id
				FROM {$this->tables['launches']}
				WHERE current_version_id IS NOT NULL
				ORDER BY updated_at_utc DESC, id DESC",
			"SELECT launch_version_id
				FROM {$this->tables['runs']}
				WHERE launch_version_id IS NOT NULL
				ORDER BY COALESCE(completed_at_utc, started_at_utc, scheduled_at_utc, '1970-01-01 00:00:00') DESC, id DESC",
		);

		foreach ( $sqls as $sql ) {
			foreach ( (array) $this->wpdb->get_col( $sql ) as $version_id ) {
				$version_id = (int) $version_id;

				if ( $version_id > 0 ) {
					$ids[ $version_id ] = $version_id;
				}
			}
		}

		return array_values( $ids );
	}

	/**
	 * Checks one stored launch target through the provider authorization contract.
	 *
	 * @param array<string,mixed> $row Stored target row.
	 */
	private function canInspectTargetRow( int $actor_id, array $row ): bool {
		try {
			$target = $this->storedTarget( $row );

			return $this->authorization->canInspect( $actor_id, $target );
		} catch ( \Throwable $throwable ) {
			unset( $throwable );

			return false;
		}
	}

	/**
	 * Normalizes legacy display rows enough to run provider authorization.
	 *
	 * Authorization failures still deny visibility; legacy formatting defects do
	 * not bypass the authorization contract or silently hide the launch.
	 *
	 * @param array<string,mixed> $row Stored target row.
	 */
	private function storedTarget( array $row ): TargetDefinition {
		$raw_uuid = (string) ( $row['target_uuid'] ?? '' );
		try {
			$uuid = new Uuid( $raw_uuid );
		} catch ( \InvalidArgumentException $exception ) {
			unset( $exception );
			$hash = md5( (string) wp_json_encode( $row ) );
			$uuid = new Uuid( substr( $hash, 0, 8 ) . '-' . substr( $hash, 8, 4 ) . '-4' . substr( $hash, 13, 3 ) . '-8' . substr( $hash, 17, 3 ) . '-' . substr( $hash, 20, 12 ) );
		}

		$target_id   = (int) ( $row['target_id'] ?? 0 );
		$provider_id = strtolower( trim( (string) ( $row['provider_id'] ?? '' ) ) );
		$provider_id = trim( (string) preg_replace( '/[^a-z0-9-]+/', '-', $provider_id ), '-' );
		$target_type = strtolower( trim( (string) ( $row['target_type'] ?? '' ) ) );
		$target_type = trim( (string) preg_replace( '/[^a-z0-9_-]+/', '-', $target_type ), '-_' );
		$reference   = trim( (string) ( $row['target_reference'] ?? '' ) );
		$fingerprint = (string) ( $row['baseline_fingerprint'] ?? '' );

		if ( '' === $provider_id ) {
			throw new \InvalidArgumentException( 'Stored target provider is unavailable.' );
		}
		if ( '' === $target_type ) {
			$target_type = 'legacy-target';
		}
		if ( '' === $reference ) {
			$reference = $provider_id . ':' . ( $target_id > 0 ? (string) $target_id : $uuid->value() );
		}
		$reference = substr( $reference, 0, 191 );
		if ( 1 !== preg_match( '/^[0-9a-f]{64}$/', $fingerprint ) ) {
			$fingerprint = '';
		}

		return new TargetDefinition(
			$uuid,
			$reference,
			$target_id > 0 ? $target_id : null,
			$target_type,
			(string) ( $row['subtype'] ?? '' ),
			null === ( $row['parent_reference'] ?? null ) ? null : (string) $row['parent_reference'],
			$provider_id,
			'' === $fingerprint ? null : $fingerprint,
			(int) ( $row['execution_order'] ?? 0 ),
			$target_id > 0 ? null : $uuid
		);
	}

	/**
	 * Checks one recording source through the provider authorization contract.
	 *
	 * @param array<string,mixed> $row Stored recording row.
	 */
	private function canInspectRecordingRow( int $actor_id, array $row ): bool {
		$target_id = $this->recordingSourceId( $row );

		if ( $target_id <= 0 ) {
			return false;
		}

		return $this->canInspectTargetRow(
			$actor_id,
			array(
				'target_uuid'          => (string) ( $row['uuid'] ?? '' ),
				'target_reference'     => (string) ( $row['source_reference'] ?? '' ),
				'target_id'            => $target_id,
				'target_type'          => (string) ( $row['source_type'] ?? '' ),
				'subtype'              => (string) ( $row['source_type'] ?? '' ),
				'parent_reference'     => null,
				'provider_id'          => (string) ( $row['source_provider_id'] ?? '' ),
				'baseline_fingerprint' => null,
				'execution_order'      => 0,
			)
		);
	}

	/**
	 * Adds source and workspace titles after recording rows are bounded.
	 *
	 * @param array<int,array<string,mixed>> $rows Recording rows.
	 * @return array<int,array<string,mixed>>
	 */
	private function withRecordingPostTitles( array $rows ): array {
		$post_ids = array();

		foreach ( $rows as $row ) {
			$source_id    = $this->recordingSourceId( $row );
			$workspace_id = $this->recordingWorkspaceId( $row );

			if ( $source_id > 0 ) {
				$post_ids[ $source_id ] = $source_id;
			}

			if ( $workspace_id > 0 ) {
				$post_ids[ $workspace_id ] = $workspace_id;
			}
		}

		$titles = $this->postTitlesById( array_values( $post_ids ) );

		foreach ( $rows as &$row ) {
			$source_id              = $this->recordingSourceId( $row );
			$workspace_id           = $this->recordingWorkspaceId( $row );
			$row['source_title']    = $source_id > 0 ? (string) ( $titles[ $source_id ] ?? '' ) : '';
			$row['workspace_title'] = $workspace_id > 0 ? (string) ( $titles[ $workspace_id ] ?? '' ) : '';
		}
		unset( $row );

		return $rows;
	}

	/**
	 * Resolves the concrete source post ID for a recording row.
	 *
	 * @param array<string,mixed> $row Recording row.
	 */
	private function recordingSourceId( array $row ): int {
		return $this->formatter->referenceId(
			(string) ( $row['source_reference'] ?? '' ),
			(int) ( $row['source_id'] ?? 0 )
		);
	}

	/**
	 * Resolves the concrete workspace post ID for a recording row.
	 *
	 * @param array<string,mixed> $row Recording row.
	 */
	private function recordingWorkspaceId( array $row ): int {
		return $this->formatter->referenceId(
			(string) ( $row['workspace_reference'] ?? '' ),
			null === ( $row['workspace_id'] ?? null ) ? 0 : (int) $row['workspace_id']
		);
	}

	/**
	 * Fetches post titles for already bounded dashboard rows.
	 *
	 * @param int[] $post_ids Post IDs.
	 * @return array<int,string>
	 */
	private function postTitlesById( array $post_ids ): array {
		$post_ids = array_values(
			array_filter(
				array_unique( array_map( 'intval', $post_ids ) ),
				static fn ( int $post_id ): bool => $post_id > 0
			)
		);

		if ( array() === $post_ids ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $post_ids ), '%d' ) );
		$rows         = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT ID, post_title
			FROM {$this->wpdb->posts}
			WHERE ID IN ({$placeholders})",
				...$post_ids
			),
			ARRAY_A
		);
		$titles       = array();

		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$titles[ (int) ( $row['ID'] ?? 0 ) ] = (string) ( $row['post_title'] ?? '' );
		}

		return $titles;
	}

	/**
	 * Clamps dashboard query limits.
	 */
	private function limit( int $limit ): int {
		return max( 1, min( 100, $limit ) );
	}





	/**
	 * Resolves the current version ID for a launch UUID.
	 */
	private function currentVersionIdForLaunch( string $uuid ): ?int {
		$value = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT current_version_id FROM {$this->tables['launches']} WHERE uuid = %s LIMIT 1",
				$uuid
			)
		);

		if ( null !== $value && (int) $value > 0 ) {
			return (int) $value;
		}

		$fallback = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT lv.id
				FROM {$this->tables['launch_versions']} lv
				INNER JOIN {$this->tables['launches']} l ON l.id = lv.launch_id
				WHERE l.uuid = %s
				ORDER BY lv.version_number DESC, lv.id DESC
				LIMIT 1",
				$uuid
			)
		);

		return null === $fallback ? null : (int) $fallback;
	}

	/**
	 * Builds one expanded detail from a permission-filtered launch row.
	 *
	 * @param array<string,mixed> $launch Launch row.
	 * @return array<string,mixed>|null
	 */
	private function detailFromLaunchRow( array $launch, bool $include_diagnostics = false ): ?array {
		$uuid = (string) ( $launch['uuid'] ?? '' );

		if ( '' === $uuid ) {
			return null;
		}

		$version_id = $this->currentVersionIdForLaunch( $uuid );
		$targets    = null === $version_id ? array() : $this->targetsForVersion( $version_id );
		$operations = null === $version_id ? array() : $this->operationsForVersion( $version_id );
		$runs       = $this->runsForLaunch( (int) ( $launch['id'] ?? 0 ), $include_diagnostics );

		return array(
			'launch'         => $launch,
			'targets'        => $targets,
			'operations'     => $operations,
			'runs'           => $runs,
			'timeline'       => $this->timelineForLaunch( $launch ),
			'restorePreview' => $this->restorePreviewForLaunch( $launch, $version_id, $operations, $targets ),
		);
	}

	/**
	 * Returns target rows for a launch version.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	private function targetsForVersion( int $version_id ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT *
				FROM {$this->tables['launch_targets']}
				WHERE launch_version_id = %d
				ORDER BY execution_order ASC, id ASC
				LIMIT %d",
				$version_id,
				self::DETAIL_ROW_LIMIT
			),
			ARRAY_A
		);

		return array_map(
			fn ( array $row ): array => $this->formatter->targetRow( $row, 'launch_target', false ) + array(
				'history' => $this->targetHistoryRows( $row, 6 ),
			),
			is_array( $rows ) ? $rows : array()
		);
	}

	/**
	 * Returns visible scheduled-change history for one stored target row.
	 *
	 * @param array<string,mixed> $target Target row.
	 * @return array<int,array<string,mixed>>
	 */
	private function targetHistoryRows( array $target, int $limit = 6 ): array {
		$limit            = $this->limit( $limit );
		$target_reference = trim( (string) ( $target['target_reference'] ?? $target['reference'] ?? '' ) );
		$target_id        = (int) ( $target['target_id'] ?? 0 );
		$target_type      = trim( (string) ( $target['target_type'] ?? '' ) );
		$conditions       = array();
		$params           = array();

		if ( '' !== $target_reference ) {
			$conditions[] = 'lt.target_reference = %s';
			$params[]     = $target_reference;
		}

		if ( $target_id > 0 && '' !== $target_type ) {
			$conditions[] = '(lt.target_id = %d AND lt.target_type = %s)';
			$params[]     = $target_id;
			$params[]     = $target_type;
		}

		if ( array() === $conditions ) {
			return array();
		}

		$visibility = $this->versionVisibilitySql( 'lt.launch_version_id' );
		$params[]   = $limit;
		$sql        = $this->wpdb->prepare(
			"SELECT l.*, u.display_name AS owner_name,
				v.status AS version_status,
				v.validation_summary_json,
				(
					SELECT COUNT(DISTINCT all_targets.target_reference)
					FROM {$this->tables['launch_targets']} all_targets
					WHERE all_targets.launch_version_id = l.current_version_id
				) AS target_count,
				(
					SELECT r.status
					FROM {$this->tables['runs']} r
					WHERE r.launch_id = l.id
					ORDER BY COALESCE(r.completed_at_utc, r.started_at_utc, r.scheduled_at_utc, '1970-01-01 00:00:00') DESC, r.id DESC
					LIMIT 1
				) AS last_run_status
			FROM {$this->tables['launch_targets']} lt
			INNER JOIN {$this->tables['launches']} l ON l.current_version_id = lt.launch_version_id
			LEFT JOIN {$this->wpdb->users} u ON u.ID = l.owner_id
			LEFT JOIN {$this->tables['launch_versions']} v ON v.id = l.current_version_id
			WHERE {$visibility}
				AND (" . implode( ' OR ', $conditions ) . ')
			GROUP BY l.id, v.id
			ORDER BY COALESCE(l.completed_at_utc, l.scheduled_at_utc, l.start_at_utc, l.updated_at_utc, l.created_at_utc) DESC, l.id DESC
			LIMIT %d',
			...$params
		);
		$rows       = $this->wpdb->get_results( $sql, ARRAY_A );

		return $this->formatLaunchRows( is_array( $rows ) ? $rows : array() );
	}



	/**
	 * Returns display-ready operation rows for a launch version.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	private function operationsForVersion( int $version_id ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT o.*, lt.target_reference, ro.status AS last_operation_status
				FROM {$this->tables['operations']} o
				LEFT JOIN {$this->tables['launch_targets']} lt
					ON lt.launch_version_id = o.launch_version_id
					AND lt.target_uuid = o.target_uuid
				LEFT JOIN {$this->tables['run_operations']} ro ON ro.id = (
					SELECT latest_ro.id
					FROM {$this->tables['run_operations']} latest_ro
					INNER JOIN {$this->tables['runs']} latest_r ON latest_r.id = latest_ro.run_id
					WHERE latest_ro.operation_id = o.id
					ORDER BY
						COALESCE(latest_r.completed_at_utc, latest_r.started_at_utc, latest_r.scheduled_at_utc, '1970-01-01 00:00:00') DESC,
						COALESCE(latest_ro.completed_at_utc, latest_ro.started_at_utc, '1970-01-01 00:00:00') DESC,
						latest_r.id DESC,
						latest_ro.id DESC
					LIMIT 1
				)
				WHERE o.launch_version_id = %d
				ORDER BY o.execution_order ASC, o.id ASC
				LIMIT %d",
				$version_id,
				self::DETAIL_ROW_LIMIT
			),
			ARRAY_A
		);
		$rows = array_values(
			array_filter(
				is_array( $rows ) ? $rows : array(),
				static fn ( array $row ): bool => 'capture_snapshot' !== (string) ( $row['operation_type'] ?? '' )
			)
		);
		return $this->formatter->operationRows( $rows );
	}

	/**
	 * Returns recent run rows for one launch.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	private function runsForLaunch( int $launch_id, bool $include_diagnostics = false ): array {
		if ( $launch_id <= 0 ) {
			return array();
		}

		$visibility = $this->versionVisibilitySql( 'r.launch_version_id' );
		$rows       = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT r.*, l.uuid AS launch_uuid, l.name AS launch_name,
					(SELECT COUNT(*) FROM {$this->tables['snapshots']} s WHERE s.run_id = r.id) AS snapshot_count
				FROM {$this->tables['runs']} r
				LEFT JOIN {$this->tables['launches']} l ON l.id = r.launch_id
				WHERE r.launch_id = %d
					AND {$visibility}
				ORDER BY r.started_at_utc DESC, r.id DESC
				LIMIT 20",
				$launch_id
			),
			ARRAY_A
		);

		return $this->formatRunRows( is_array( $rows ) ? $rows : array(), $include_diagnostics );
	}

	/**
	 * Formats run rows and attaches sanitized support details only when allowed.
	 *
	 * @param array<int,array<string,mixed>> $rows Run database rows.
	 * @return array<int,array<string,mixed>>
	 */
	private function formatRunRows( array $rows, bool $include_diagnostics ): array {
		$operation_results = $include_diagnostics ? $this->operationResultCodesForRuns( $rows ) : array();
		$restore_points    = $include_diagnostics ? $this->restorePointIdsForRuns( $rows ) : array();

		return array_map(
			function ( array $row ) use ( $include_diagnostics, $operation_results, $restore_points ): array {
				$run = $this->formatter->runRow( $row );

				if ( $include_diagnostics ) {
					$run['supportDetails'] = $this->runSupportDetails(
						$row,
						$operation_results[ (int) ( $row['id'] ?? 0 ) ] ?? array(),
						$restore_points[ (int) ( $row['id'] ?? 0 ) ] ?? array()
					);
				}

				return $run;
			},
			$rows
		);
	}

	/**
	 * @param array<int,array<string,mixed>> $rows Run database rows.
	 * @return array<int,array<int,array<string,mixed>>>
	 */
	private function operationResultCodesForRuns( array $rows ): array {
		$run_ids = $this->runIds( $rows );

		if ( array() === $run_ids ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $run_ids ), '%d' ) );
		$limit        = min( count( $run_ids ) * self::DETAIL_ROW_LIMIT, 500 );
		$query_rows   = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT ro.run_id, o.operation_uuid, ro.status, 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 IN ({$placeholders})
				ORDER BY ro.run_id ASC, ro.completed_at_utc ASC, ro.id ASC
				LIMIT %d",
				...array_merge( $run_ids, array( $limit ) )
			),
			ARRAY_A
		);
		$results      = array();

		foreach ( is_array( $query_rows ) ? $query_rows : array() as $row ) {
			$run_id = (int) ( $row['run_id'] ?? 0 );

			if ( $run_id <= 0 ) {
				continue;
			}

			$payload = $this->decodedSnapshotObject( $row['result_json'] ?? null );
			$code    = $this->supportCode( $payload['code'] ?? '', (string) ( $row['status'] ?? 'unknown' ) );
			$status  = $this->supportCode( $row['status'] ?? '', 'unknown' );

			$results[ $run_id ][] = array(
				'operationUuid'      => $this->supportCode( $row['operation_uuid'] ?? '', 'unavailable' ),
				'status'             => $status,
				'code'               => $code,
				'retryable'          => true === ( $payload['retryable'] ?? false ),
				'failureClass'       => $this->supportCode( $payload['failure_classification'] ?? '', '' ),
				'verificationStatus' => true === ( $payload['verified'] ?? null ) ? 'verified' : 'not_verified',
			);
		}

		return $results;
	}

	/**
	 * @param array<int,array<string,mixed>> $rows Run database rows.
	 * @return array<int,string[]>
	 */
	private function restorePointIdsForRuns( array $rows ): array {
		$run_ids = $this->runIds( $rows );

		if ( array() === $run_ids ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $run_ids ), '%d' ) );
		$limit        = min( count( $run_ids ) * self::DETAIL_ROW_LIMIT, 500 );
		$query_rows   = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT run_id, uuid
				FROM {$this->tables['snapshots']}
				WHERE run_id IN ({$placeholders})
				ORDER BY run_id ASC, id ASC
				LIMIT %d",
				...array_merge( $run_ids, array( $limit ) )
			),
			ARRAY_A
		);
		$results      = array();

		foreach ( is_array( $query_rows ) ? $query_rows : array() as $row ) {
			$run_id = (int) ( $row['run_id'] ?? 0 );
			$uuid   = $this->supportCode( $row['uuid'] ?? '', '' );

			if ( $run_id <= 0 || '' === $uuid ) {
				continue;
			}

			$results[ $run_id ][] = $uuid;
		}

		return $results;
	}

	/**
	 * @param array<int,array<string,mixed>> $rows Run database rows.
	 * @return int[]
	 */
	private function runIds( array $rows ): array {
		return array_values(
			array_filter(
				array_unique(
					array_map(
						static fn ( array $row ): int => (int) ( $row['id'] ?? 0 ),
						$rows
					)
				),
				static fn ( int $run_id ): bool => $run_id > 0
			)
		);
	}

	/**
	 * @param array<string,mixed>            $row Run database row.
	 * @param array<int,array<string,mixed>> $operation_results Sanitized operation rows.
	 * @param string[]                       $restore_points Snapshot UUIDs.
	 * @return array<string,mixed>
	 */
	private function runSupportDetails( array $row, array $operation_results, array $restore_points ): array {
		$task_id = null === ( $row['action_scheduler_id'] ?? null )
			? ''
			: (string) (int) $row['action_scheduler_id'];

		return array(
			'runUuid'              => $this->supportCode( $row['uuid'] ?? '', 'unavailable' ),
			'attemptNumber'        => (int) ( $row['attempt_number'] ?? 0 ),
			'triggerSource'        => $this->supportCode( $row['trigger_source'] ?? '', 'unknown' ),
			'sanitizedCode'        => $this->runSupportCode( (string) ( $row['status'] ?? '' ), $operation_results ),
			'retryClassification'  => $this->runRetryClassification( $operation_results ),
			'scheduledTaskId'      => $task_id,
			'restorePointIds'      => array_values( array_unique( $restore_points ) ),
			'operationResultCodes' => $operation_results,
		);
	}

	/**
	 * @param array<int,array<string,mixed>> $operation_results Sanitized operation rows.
	 */
	private function runSupportCode( string $status, array $operation_results ): string {
		foreach ( $operation_results as $result ) {
			if ( 'failed' === (string) ( $result['status'] ?? '' ) ) {
				return (string) ( $result['code'] ?? 'failed' );
			}
		}

		foreach ( $operation_results as $result ) {
			$code = (string) ( $result['code'] ?? '' );

			if ( '' !== $code ) {
				return $code;
			}
		}

		return $this->supportCode( $status, 'unknown' );
	}

	/**
	 * @param array<int,array<string,mixed>> $operation_results Sanitized operation rows.
	 */
	private function runRetryClassification( array $operation_results ): string {
		$classifications = array();

		foreach ( $operation_results as $result ) {
			$classification = (string) ( $result['failureClass'] ?? '' );

			if ( '' !== $classification ) {
				$classifications[] = $classification;
			}
		}

		$classifications = array_values( array_unique( $classifications ) );

		if ( array() !== $classifications ) {
			return implode( ', ', $classifications );
		}

		foreach ( $operation_results as $result ) {
			if ( true === ( $result['retryable'] ?? false ) ) {
				return 'retryable';
			}
		}

		return 'none';
	}

	/**
	 * Returns a bounded stable support code, not raw messages or details.
	 *
	 * @param mixed $value Raw value.
	 */
	private function supportCode( $value, string $fallback ): string {
		$code = trim( (string) $value );

		if ( '' === $code ) {
			$code = $fallback;
		}

		$code = preg_replace( '/[^A-Za-z0-9._:-]+/', '_', $code );

		return substr( is_string( $code ) ? trim( $code, '_' ) : $fallback, 0, 96 );
	}

	/**
	 * Builds a friendly restore preview from saved restore-point rows.
	 *
	 * @param array<string,mixed>            $launch Launch row.
	 * @param array<int,array<string,mixed>> $operations Display operation rows.
	 * @param array<int,array<string,mixed>> $targets Display target rows.
	 * @return array<string,mixed>
	 */
	private function restorePreviewForLaunch( array $launch, ?int $version_id, array $operations, array $targets ): array {
		$last_status = strtolower( (string) ( $launch['lastRunStatus'] ?? '' ) );
		$base        = $this->restorePreviewBase();

		if ( in_array( $last_status, array( 'rolled_back', 'restored' ), true ) ) {
			return array_merge(
				$base,
				array(
					'status'     => 'restored',
					'summary'    => __( 'Previous values were already restored for this scheduled change.', 'jjs-post-scheduler' ),
					'nextAction' => __( 'Review history if you need to confirm what changed.', 'jjs-post-scheduler' ),
					'support'    => array( 'code' => 'restore_already_completed' ),
				)
			);
		}

		if ( ! in_array( $last_status, array( 'successful', 'success', 'successful_with_warnings', 'partially_failed', 'failed', 'rollback_failed' ), true ) ) {
			return array_merge(
				$base,
				array(
					'status'     => 'planned',
					'summary'    => __( 'Previous values will be saved before this change goes live, where supported.', 'jjs-post-scheduler' ),
					'nextAction' => __( 'Come back here after the change goes live to preview previous values.', 'jjs-post-scheduler' ),
					'support'    => array( 'code' => 'restore_point_not_created_yet' ),
				)
			);
		}

		if ( null === $version_id || $version_id <= 0 ) {
			return array_merge(
				$base,
				array(
					'status'     => 'unavailable',
					'summary'    => __( 'Saved previous values are unavailable because this change cannot be matched to a saved change set.', 'jjs-post-scheduler' ),
					'nextAction' => __( 'Review the affected item manually or open a support report.', 'jjs-post-scheduler' ),
					'support'    => array( 'code' => 'restore_preview_missing_version' ),
				)
			);
		}

		if ( array() === $operations ) {
			return array_merge(
				$base,
				array(
					'status'     => 'unavailable',
					'summary'    => __( 'Saved previous values are unavailable because no saved changes were found.', 'jjs-post-scheduler' ),
					'nextAction' => __( 'Review history and the live item before making manual edits.', 'jjs-post-scheduler' ),
					'support'    => array( 'code' => 'restore_preview_missing_operations' ),
				)
			);
		}

		$run_id = $this->latestRestorePreviewRunId( (int) ( $launch['id'] ?? 0 ) );

		if ( $run_id <= 0 ) {
			return array_merge(
				$base,
				array(
					'status'     => 'unavailable',
					'summary'    => __( 'Saved previous values were not found for the latest run.', 'jjs-post-scheduler' ),
					'nextAction' => __( 'Review the live item manually or open a support report.', 'jjs-post-scheduler' ),
					'support'    => array( 'code' => 'restore_point_missing' ),
				)
			);
		}

		$snapshot_rows = $this->restorePreviewSnapshotRows( $run_id, $version_id );

		if ( null === $snapshot_rows ) {
			return array_merge(
				$base,
				array(
					'status'     => 'failed',
					'summary'    => __( 'Restore preview could not be prepared.', 'jjs-post-scheduler' ),
					'nextAction' => __( 'Refresh WordPress and try again. If this keeps happening, download a support report.', 'jjs-post-scheduler' ),
					'support'    => array(
						'code'        => 'restore_preview_query_failed',
						'sourceRunId' => $run_id,
					),
				)
			);
		}

		if ( array() === $snapshot_rows ) {
			return array_merge(
				$base,
				array(
					'status'     => 'unavailable',
					'summary'    => __( 'Saved previous values were not found for this run.', 'jjs-post-scheduler' ),
					'nextAction' => __( 'Review the live item manually or open a support report.', 'jjs-post-scheduler' ),
					'support'    => array(
						'code'        => 'restore_point_empty',
						'sourceRunId' => $run_id,
					),
				)
			);
		}

		$snapshots      = $this->restorePreviewSnapshotMap( $snapshot_rows );
		$target_names   = $this->targetNameMap( $targets );
		$restorable     = array();
		$non_restorable = array();

		foreach ( $operations as $operation ) {
			$preview_row = $this->restorePreviewOperationRow( $operation, $snapshots, $target_names );

			if ( (bool) ( $preview_row['restorable'] ?? false ) ) {
				unset( $preview_row['restorable'] );
				$restorable[] = $preview_row;
				continue;
			}

			unset( $preview_row['restorable'] );
			$non_restorable[] = $preview_row;
		}

		$status  = array() === $restorable
			? 'unavailable'
			: ( array() === $non_restorable ? 'available' : 'partial' );
		$summary = $this->restorePreviewSummary( $status, count( $restorable ), count( $non_restorable ) );

		return array_merge(
			$base,
			array(
				'status'        => $status,
				'summary'       => $summary,
				'nextAction'    => $this->restorePreviewNextAction( $status ),
				'restorable'    => $restorable,
				'nonRestorable' => $non_restorable,
				'support'       => array(
					'code'               => 'restore_preview_ready',
					'sourceRunId'        => $run_id,
					'restorePointCount'  => count( $snapshot_rows ),
					'restorableCount'    => count( $restorable ),
					'needsReviewCount'   => count( $non_restorable ),
					'firstCapturedAtUtc' => (string) ( $snapshot_rows[0]['captured_at_utc'] ?? '' ),
				),
			)
		);
	}

	/**
	 * Returns shared restore preview copy.
	 *
	 * @return array<string,mixed>
	 */
	private function restorePreviewBase(): array {
		return array(
			'status'            => 'unavailable',
			'summary'           => __( 'Saved previous values are unavailable.', 'jjs-post-scheduler' ),
			'nextAction'        => __( 'Review the live item manually or open a support report.', 'jjs-post-scheduler' ),
			'conflictPolicy'    => __( 'Edits made after this ran are preserved by default. If WordPress detects a newer edit, that value is skipped.', 'jjs-post-scheduler' ),
			'createdItemPolicy' => __( 'New items created by this change are left in place and disabled when supported.', 'jjs-post-scheduler' ),
			'restorable'        => array(),
			'nonRestorable'     => array(),
			'support'           => array( 'code' => 'restore_preview_unavailable' ),
		);
	}

	/**
	 * Finds the latest source run that has a pre-execution restore point.
	 */
	private function latestRestorePreviewRunId( int $launch_id ): int {
		if ( $launch_id <= 0 ) {
			return 0;
		}

		$statuses = array( 'success', 'successful', 'successful_with_warnings', 'partially_failed', 'failed', 'rollback_failed' );
		$args     = array_merge(
			array( $launch_id ),
			$statuses,
			array( 'manual_rollback', SnapshotKind::PRE_EXECUTION, 'before' )
		);
		$sql      = $this->wpdb->prepare(
			"SELECT r.id
			FROM {$this->tables['runs']} r
			WHERE r.launch_id = %d
				AND r.status IN (" . implode( ',', array_fill( 0, count( $statuses ), '%s' ) ) . ")
				AND r.trigger_source <> %s
				AND r.rollback_run_id IS NULL
				AND EXISTS (
					SELECT 1
					FROM {$this->tables['snapshots']} s
					WHERE s.run_id = r.id
						AND s.snapshot_kind IN (%s, %s)
				)
			ORDER BY COALESCE(r.completed_at_utc, r.started_at_utc, r.scheduled_at_utc, '1970-01-01 00:00:00') DESC, r.id DESC
			LIMIT 1",
			...$args
		);

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

	/**
	 * Returns raw snapshot rows for a restore preview, or null when the query fails.
	 *
	 * @return array<int,array<string,mixed>>|null
	 */
	private function restorePreviewSnapshotRows( int $run_id, int $version_id ): ?array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT id, uuid, run_id, launch_version_id, target_reference, provider_id,
					provider_version, snapshot_kind, data_json, redaction_json, captured_at_utc
				FROM {$this->tables['snapshots']}
				WHERE run_id = %d
					AND launch_version_id = %d
					AND snapshot_kind IN (%s, %s)
				ORDER BY id ASC
				LIMIT %d",
				$run_id,
				$version_id,
				SnapshotKind::PRE_EXECUTION,
				'before',
				self::DETAIL_ROW_LIMIT
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : null;
	}

	/**
	 * @param array<int,array<string,mixed>> $rows Snapshot rows.
	 * @return array<string,array{values:array<string,mixed>,redaction:array<string,mixed>}>
	 */
	private function restorePreviewSnapshotMap( array $rows ): array {
		$map = array();

		foreach ( $rows as $row ) {
			$key = $this->restorePreviewSnapshotKey(
				(string) ( $row['target_reference'] ?? '' ),
				(string) ( $row['provider_id'] ?? '' )
			);

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

			$map[ $key ] = array(
				'values'    => $this->decodedSnapshotObject( $row['data_json'] ?? null ),
				'redaction' => $this->decodedSnapshotObject( $row['redaction_json'] ?? null ),
			);
		}

		return $map;
	}

	/**
	 * Builds one restore preview row from one display operation.
	 *
	 * @param array<string,mixed>                                                           $operation Display operation row.
	 * @param array<string,array{values:array<string,mixed>,redaction:array<string,mixed>}> $snapshots Snapshot map.
	 * @param array<string,string>                                                          $target_names Target labels keyed by reference.
	 * @return array<string,mixed>
	 */
	private function restorePreviewOperationRow( array $operation, array $snapshots, array $target_names ): array {
		$field            = (string) ( $operation['field'] ?? '' );
		$type             = (string) ( $operation['type'] ?? '' );
		$target_reference = (string) ( $operation['targetReference'] ?? '' );
		$provider_id      = (string) ( $operation['providerId'] ?? '' );
		$key              = $this->restorePreviewSnapshotKey( $target_reference, $provider_id );
		$snapshot         = '' === $key ? array() : ( $snapshots[ $key ] ?? array() );
		$values           = is_array( $snapshot['values'] ?? null ) ? $snapshot['values'] : array();
		$redaction        = is_array( $snapshot['redaction'] ?? null ) ? $snapshot['redaction'] : array();
		$sensitive        = (bool) ( $operation['sensitive'] ?? false ) || $this->restorePreviewFieldSensitive( $field, $redaction );
		$item             = (string) ( $target_names[ $target_reference ] ?? __( 'Affected item', 'jjs-post-scheduler' ) );
		$row              = array(
			'id'           => (string) ( $operation['id'] ?? md5( $target_reference . '|' . $field ) ),
			'group'        => (string) ( $operation['group'] ?? __( 'Changes', 'jjs-post-scheduler' ) ),
			'label'        => (string) ( $operation['label'] ?? __( 'Saved value', 'jjs-post-scheduler' ) ),
			'item'         => $item,
			'currentValue' => $this->restorePreviewCurrentValue( $operation ),
		);

		if ( 'reversible' !== (string) ( $operation['reversibility'] ?? '' ) && 'conditionally_reversible' !== (string) ( $operation['reversibility'] ?? '' ) ) {
			return $row + array(
				'restorable' => false,
				'reason'     => __( 'This kind of change is not reversible automatically.', 'jjs-post-scheduler' ),
			);
		}

		if ( '' === $field ) {
			return $row + array(
				'restorable' => false,
				'reason'     => __( 'This change did not save a field value that can be restored automatically.', 'jjs-post-scheduler' ),
			);
		}

		if ( array() === $snapshot ) {
			return $row + array(
				'restorable' => false,
				'reason'     => __( 'No saved previous values were found for this item.', 'jjs-post-scheduler' ),
			);
		}

		if ( ! array_key_exists( $field, $values ) ) {
			return $row + array(
				'restorable' => false,
				'reason'     => $sensitive
					? __( 'This sensitive value stays hidden, so it was not saved for automatic restore.', 'jjs-post-scheduler' )
					: __( 'Saved previous values do not include this value.', 'jjs-post-scheduler' ),
			);
		}

		return $row + array(
			'restorable'   => true,
			'restoreValue' => $this->formatter->restorePreviewValue( $values[ $field ], $field, $type, $sensitive ),
			'reason'       => __( 'This saved previous value can be restored.', 'jjs-post-scheduler' ),
		);
	}

	/**
	 * Returns the current value text for restore previews.
	 *
	 * @param array<string,mixed> $operation Display operation row.
	 */
	private function restorePreviewCurrentValue( array $operation ): string {
		$value = (string) ( $operation['after'] ?? __( 'Current value', 'jjs-post-scheduler' ) );

		return ValueRedactor::REDACTED === $value ? __( 'Hidden for security.', 'jjs-post-scheduler' ) : $value;
	}

	/**
	 * Returns a stable snapshot lookup key, or an empty string when incomplete.
	 */
	private function restorePreviewSnapshotKey( string $target_reference, string $provider_id ): string {
		return '' === $target_reference || '' === $provider_id
			? ''
			: $target_reference . "\n" . $provider_id;
	}

	/**
	 * Whether a field was omitted from the restore point for privacy.
	 *
	 * @param array<string,mixed> $redaction Snapshot redaction metadata.
	 */
	private function restorePreviewFieldSensitive( string $field, array $redaction ): bool {
		$omitted = is_array( $redaction['omitted_sensitive_fields'] ?? null )
			? $redaction['omitted_sensitive_fields']
			: array();

		return in_array( $field, array_map( 'strval', $omitted ), true );
	}

	/**
	 * @param mixed $json Snapshot JSON.
	 * @return array<string,mixed>
	 */
	private function decodedSnapshotObject( $json ): array {
		if ( null === $json || '' === (string) $json ) {
			return array();
		}

		$decoded = json_decode( (string) $json, true );

		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * @param array<int,array<string,mixed>> $targets Target display rows.
	 * @return array<string,string>
	 */
	private function targetNameMap( array $targets ): array {
		$map = array();

		foreach ( $targets as $target ) {
			$reference = (string) ( $target['targetReference'] ?? $target['reference'] ?? '' );

			if ( '' !== $reference ) {
				$map[ $reference ] = (string) ( $target['name'] ?? __( 'Affected item', 'jjs-post-scheduler' ) );
			}
		}

		return $map;
	}

	/**
	 * Returns the main restore preview summary.
	 */
	private function restorePreviewSummary( string $status, int $restorable_count, int $needs_review_count ): string {
		if ( 'available' === $status ) {
			return sprintf(
				/* translators: %d: restorable value count. */
				_n( '%d previous value can be restored.', '%d previous values can be restored.', $restorable_count, 'jjs-post-scheduler' ),
				$restorable_count
			);
		}

		if ( 'partial' === $status ) {
			return sprintf(
				/* translators: 1: restorable value summary, 2: needs-review value summary. */
				__( '%1$s. %2$s before changing anything manually.', 'jjs-post-scheduler' ),
				sprintf(
					/* translators: %d: restorable value count. */
					_n( '%d value can be restored', '%d values can be restored', $restorable_count, 'jjs-post-scheduler' ),
					$restorable_count
				),
				sprintf(
					/* translators: %d: value count needing review. */
					_n( '%d value needs review', '%d values need review', $needs_review_count, 'jjs-post-scheduler' ),
					$needs_review_count
				)
			);
		}

		return __( 'Saved previous values are unavailable because this run did not include restorable values.', 'jjs-post-scheduler' );
	}

	/**
	 * Returns the next safe action for one restore preview state.
	 */
	private function restorePreviewNextAction( string $status ): string {
		if ( 'available' === $status ) {
			return __( 'Preview these values, then confirm before restoring previous values.', 'jjs-post-scheduler' );
		}

		if ( 'partial' === $status ) {
			return __( 'Review the values that cannot be restored before confirming.', 'jjs-post-scheduler' );
		}

		return __( 'Review the live item manually or open a support report.', 'jjs-post-scheduler' );
	}

	/**
	 * Builds a simple launch timeline.
	 *
	 * @param array<string,mixed> $launch Launch row.
	 * @return array<int,array<string,string>>
	 */
	private function timelineForLaunch( array $launch ): array {
		return array(
			array(
				'label' => __( 'Recorded', 'jjs-post-scheduler' ),
				'time'  => (string) ( $launch['updatedAt'] ?? '' ),
				'state' => 'complete',
			),
			array(
				'label' => __( 'Validated', 'jjs-post-scheduler' ),
				'time'  => $this->validationTimelineText( (string) ( $launch['validationState'] ?? '' ) ),
				'state' => 'valid' === (string) ( $launch['validationState'] ?? '' ) ? 'complete' : 'warning',
			),
			array(
				'label' => __( 'Scheduled', 'jjs-post-scheduler' ),
				'time'  => (string) ( $launch['startAt'] ?? '' ),
				'state' => 'scheduled' === (string) ( $launch['status'] ?? '' ) ? 'active' : 'complete',
			),
			array(
				'label' => __( 'Last run', 'jjs-post-scheduler' ),
				'time'  => (string) ( $launch['lastRunStatus'] ?? __( 'None recorded', 'jjs-post-scheduler' ) ),
				'state' => '' === (string) ( $launch['lastRunStatus'] ?? '' ) ? 'pending' : (string) ( $launch['lastRunStatus'] ?? '' ),
			),
		);
	}

	/**
	 * Returns friendly validation state text for timelines.
	 */
	private function validationTimelineText( string $state ): string {
		if ( 'valid' === $state ) {
			return __( 'Safety check passed', 'jjs-post-scheduler' );
		}

		if ( 'not_validated' === $state ) {
			return __( 'Needs review', 'jjs-post-scheduler' );
		}

		return __( 'Not checked yet', 'jjs-post-scheduler' );
	}
}
