<?php
/**
 * WPDB recording session repository.
 *
 * @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.
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Exception messages are escaped at UI boundaries.

use ScheduledChanges\Database\TableNames;
use ScheduledChanges\Domain\Recording\RecordingCleanupStatus;
use ScheduledChanges\Domain\Recording\RecordingSession;
use ScheduledChanges\Domain\Recording\RecordingSessionStatus;
use ScheduledChanges\Domain\Shared\UtcTimestamp;
use ScheduledChanges\Domain\Shared\Uuid;
use ScheduledChanges\Recording\RecordingWorkspaceMetadata;
use ScheduledChanges\Support\Json;

/**
 * Stores Record Changes sessions in the scheduler-owned table.
 */
final class WpdbRecordingSessionRepository implements RecordingSessionRepository {
	private \wpdb $wpdb;

	private string $table;

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

		$this->wpdb  = $database;
		$this->table = ( $tables ?? new TableNames( $database ) )->get( 'recording_sessions' );
	}

	public function create( RecordingSession $session ): int {
		if ( null !== $session->id() ) {
			throw new \InvalidArgumentException( 'Cannot create a recording session that already has an internal ID.' );
		}

		$inserted = $this->wpdb->insert(
			$this->table,
			array(
				'uuid'                   => $session->uuid()->value(),
				'source_id'              => $session->sourceId(),
				'workspace_id'           => $session->workspaceId(),
				'source_provider_id'     => $session->sourceProviderId(),
				'source_type'            => $session->sourceType(),
				'source_reference'       => $session->sourceReference(),
				'workspace_provider_id'  => $session->workspaceProviderId(),
				'workspace_type'         => $session->workspaceType(),
				'workspace_reference'    => $session->workspaceReference(),
				'provider_map_json'      => Json::encode( array_merge( array( 'relation_map' => $session->relationMap() ), $session->providerMap() ) ),
				'baseline_snapshot_json' => Json::encode( $session->baselineSnapshot() ),
				'current_snapshot_json'  => Json::encode( $session->currentSnapshot() ),
				'diff_json'              => Json::encode( $session->diff() ),
				'selected_changes_json'  => Json::encode( $session->selectedChanges() ),
				'owner_id'               => $session->ownerId(),
				'status'                 => $session->status(),
				'expires_at_utc'         => $session->expiresAt()->toStorage(),
				'created_at_utc'         => $session->createdAt()->toStorage(),
				'updated_at_utc'         => $session->updatedAt()->toStorage(),
				'cleanup_status'         => $session->cleanupStatus(),
			),
			array(
				'%s',
				'%d',
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
			)
		);

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

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

	public function findByUuid( Uuid $uuid ): ?RecordingSession {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE uuid = %s LIMIT 1",
				$uuid->value()
			),
			ARRAY_A
		);

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

	public function findActiveBySourceIdentity( string $provider_id, string $source_type, string $source_reference ): ?RecordingSession {
		if ( '' === trim( $provider_id ) || '' === trim( $source_type ) || '' === trim( $source_reference ) ) {
			throw new \InvalidArgumentException( 'Canonical recording source identity is required.' );
		}

		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table}
				WHERE source_provider_id = %s
					AND source_type = %s
					AND source_reference = %s
					AND status = %s
					AND expires_at_utc >= %s
				ORDER BY id DESC
				LIMIT 1",
				$provider_id,
				$source_type,
				$source_reference,
				RecordingSessionStatus::ACTIVE,
				UtcTimestamp::now()->toStorage()
			),
			ARRAY_A
		);

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

	public function findActiveBySourceId( int $source_id ): ?RecordingSession {
		if ( $source_id <= 0 ) {
			throw new \InvalidArgumentException( 'Source ID must be positive.' );
		}

		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table}
				WHERE source_id = %d
					AND status = %s
					AND expires_at_utc >= %s
				ORDER BY id DESC
				LIMIT 1",
				$source_id,
				RecordingSessionStatus::ACTIVE,
				UtcTimestamp::now()->toStorage()
			),
			ARRAY_A
		);

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

	/**
	 * {@inheritDoc}
	 */
	public function stale( int $limit ): array {
		$limit = max( 1, min( 100, $limit ) );
		$rows  = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table}
				WHERE status = %s
					AND expires_at_utc < %s
				ORDER BY expires_at_utc ASC, id ASC
				LIMIT %d",
				RecordingSessionStatus::ACTIVE,
				UtcTimestamp::now()->toStorage(),
				$limit
			),
			ARRAY_A
		);

		return array_map(
			fn ( array $row ): RecordingSession => $this->hydrate( $row ),
			is_array( $rows ) ? $rows : array()
		);
	}

	public function updateCapture( Uuid $uuid, array $current_snapshot, array $diff ): void {
		$updated = $this->wpdb->update(
			$this->table,
			array(
				'current_snapshot_json' => Json::encode( $current_snapshot ),
				'diff_json'             => Json::encode( $diff ),
				'updated_at_utc'        => UtcTimestamp::now()->toStorage(),
			),
			array( 'uuid' => $uuid->value() ),
			array( '%s', '%s', '%s' ),
			array( '%s' )
		);

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

	public function updateSelectedChanges( Uuid $uuid, array $selected_changes ): void {
		$updated = $this->wpdb->update(
			$this->table,
			array(
				'selected_changes_json' => Json::encode( $selected_changes ),
				'updated_at_utc'        => UtcTimestamp::now()->toStorage(),
			),
			array( 'uuid' => $uuid->value() ),
			array( '%s', '%s' ),
			array( '%s' )
		);

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

	public function updateStatus(
		Uuid $uuid,
		string $status,
		string $cleanup_status
	): void {
		$updated = $this->wpdb->update(
			$this->table,
			array(
				'status'         => $status,
				'cleanup_status' => $cleanup_status,
				'updated_at_utc' => UtcTimestamp::now()->toStorage(),
			),
			array( 'uuid' => $uuid->value() ),
			array( '%s', '%s', '%s' ),
			array( '%s' )
		);

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

	public function deleteByUuid( Uuid $uuid ): bool {
		$deleted = $this->wpdb->delete(
			$this->table,
			array( 'uuid' => $uuid->value() ),
			array( '%s' )
		);

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

		return $deleted > 0;
	}

	public function activeCount(): int {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table} WHERE status = %s",
				RecordingSessionStatus::ACTIVE
			)
		);
	}

	public function staleCount(): int {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->table}
				WHERE status = %s
					AND expires_at_utc < %s",
				RecordingSessionStatus::ACTIVE,
				UtcTimestamp::now()->toStorage()
			)
		);
	}

	public function orphanedWorkspaceCount(): int {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(DISTINCT posts.ID)
				FROM {$this->wpdb->posts} posts
				INNER JOIN {$this->wpdb->postmeta} workspace_meta
					ON workspace_meta.post_id = posts.ID
					AND workspace_meta.meta_key = %s
					AND workspace_meta.meta_value = '1'
				LEFT JOIN {$this->wpdb->postmeta} session_meta
					ON session_meta.post_id = posts.ID
					AND session_meta.meta_key = %s
				LEFT JOIN {$this->table} sessions
					ON sessions.uuid = session_meta.meta_value
				WHERE posts.post_type IN ('post', 'page')
					AND sessions.id IS NULL",
				RecordingWorkspaceMetadata::WORKSPACE_MARKER,
				RecordingWorkspaceMetadata::SESSION_UUID
			)
		);
	}

	/**
	 * @param array<string,mixed> $row Database row.
	 */
	private function hydrate( array $row ): RecordingSession {
		$source_id           = (int) ( $row['source_id'] ?? 0 );
		$workspace_id        = null === ( $row['workspace_id'] ?? null ) ? null : (int) $row['workspace_id'];
		$source_reference    = (string) ( $row['source_reference'] ?? '' );
		$workspace_reference = null === ( $row['workspace_reference'] ?? null ) ? null : (string) $row['workspace_reference'];
		$source_provider_id  = (string) ( $row['source_provider_id'] ?? '' );
		$source_type         = (string) ( $row['source_type'] ?? '' );
		$workspace_provider  = (string) ( $row['workspace_provider_id'] ?? '' );
		$workspace_type      = (string) ( $row['workspace_type'] ?? '' );

		if ( ( null === $workspace_reference || '' === $workspace_reference ) && null !== $workspace_id ) {
			$workspace_provider  = '' === $workspace_provider ? 'wordpress-native' : $workspace_provider;
			$workspace_type      = '' === $workspace_type ? $source_type : $workspace_type;
			$workspace_reference = $workspace_type . ':' . $workspace_id;
		}

		return new RecordingSession(
			(int) $row['id'],
			new Uuid( (string) $row['uuid'] ),
			$source_id,
			$workspace_id,
			$this->relationMap( $row ),
			Json::decodeObject( (string) $row['provider_map_json'] ),
			Json::decodeObject( (string) $row['baseline_snapshot_json'] ),
			Json::decodeObject( (string) $row['current_snapshot_json'] ),
			Json::decodeObject( (string) $row['diff_json'] ),
			Json::decodeObject( (string) $row['selected_changes_json'] ),
			(int) $row['owner_id'],
			\ScheduledChanges\Support\EnumValue::from( \ScheduledChanges\Domain\Recording\RecordingSessionStatus::class, (string) $row['status'] ),
			UtcTimestamp::fromStorage( (string) $row['expires_at_utc'] ),
			UtcTimestamp::fromStorage( (string) $row['created_at_utc'] ),
			UtcTimestamp::fromStorage( (string) $row['updated_at_utc'] ),
			\ScheduledChanges\Support\EnumValue::from( \ScheduledChanges\Domain\Recording\RecordingCleanupStatus::class, (string) $row['cleanup_status'] ),
			$source_provider_id,
			$source_type,
			$source_reference,
			$workspace_provider,
			$workspace_type,
			$workspace_reference
		);
	}

	/**
	 * @param array<string,mixed> $row Database row.
	 * @return array<string,mixed>
	 */
	private function relationMap( array $row ): array {
		$provider_map = Json::decodeObject( (string) ( $row['provider_map_json'] ?? '{}' ) );

		return is_array( $provider_map['relation_map'] ?? null ) ? $provider_map['relation_map'] : array();
	}
}
