<?php
/**
 * S3 Media Migration Service.
 *
 * @package Ugcify\Storage
 */

namespace Ugcify\Storage;

use WP_Error;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class S3MigrationService
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
 */
class S3MigrationService {

	/**
	 * Job type identifier stored in automation_logs.type.
	 */
	const JOB_TYPE = 's3_media_migration';

	/**
	 * wp_options key for the deduplication registry.
	 */
	const MIGRATED_OPTION = 'ugcify_s3_migrated_urls';

	/**
	 * Estimated seconds per file for time estimation.
	 */
	const SECONDS_PER_FILE = 0.5;

	/**
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * @var string
	 */
	private $meta_table;

	/**
	 * @var string
	 */
	private $logs_table;

	/**
	 * @var string
	 */
	private $reviews_table;

	/**
	 * @var StorageManager
	 */
	private $storage;

	/**
	 * @var array<int, array{product_id:int, product_name:string, created_at:?string}>
	 */
	private $review_context_cache = array();

	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb          = $wpdb;
		$this->meta_table    = $wpdb->prefix . 'ugcify_review_meta';
		$this->logs_table    = $wpdb->prefix . 'ugcify_automation_logs';
		$this->reviews_table = $wpdb->prefix . 'ugcify_reviews';
		$this->storage       = new StorageManager();
	}

	// =========================================================================
	// Public API
	// =========================================================================

	/**
	 * Return pre-flight statistics for the migration modal.
	 *
	 * @return array|WP_Error
	 */
	public function get_stats() {
		if ( ! $this->storage->is_enabled() ) {
			return new WP_Error( 's3_not_configured', __( 'S3 storage is not enabled.', 'ugcify' ), array( 'status' => 400 ) );
		}

		$s3_host = $this->get_s3_host_pattern();
		$rows    = $this->wpdb->get_results(
			"SELECT meta_id, meta_value FROM {$this->meta_table}
			 WHERE meta_key IN ('images','videos')
			 AND meta_value != ''",
			ARRAY_A
		);

		$upload_dir = \wp_upload_dir();
		$base_url   = $upload_dir['baseurl'];
		$base_path  = $upload_dir['basedir'];
		$size_bytes = 0; // Estimated size for pending local files only.
		$total      = 0; // Pending file URLs to migrate.
		$total_rows = 0; // Meta rows containing at least one pending file URL.
		$already_migrated = 0;
		$registry = $this->load_registry();

		foreach ( $rows as $row ) {
			$urls = \maybe_unserialize( $row['meta_value'] );
			if ( ! is_array( $urls ) ) {
				$urls = array( $urls );
			}

			$row_has_pending = false;

			foreach ( $urls as $url ) {
				if ( ! is_string( $url ) || '' === trim( $url ) ) {
					continue;
				}

				// Already S3-hosted URL.
				if ( false !== strpos( $url, $s3_host ) ) {
					$already_migrated++;
					continue;
				}

				// Already present in migration registry.
				if ( isset( $registry[ $url ] ) ) {
					$already_migrated++;
					continue;
				}

				// Only local URLs are migratable.
				if ( ! $this->is_local_url( $url ) ) {
					continue;
				}

				$total++;
				$row_has_pending = true;

				if ( false === strpos( $url, $base_url ) ) {
					continue;
				}

				$relative = str_replace( $base_url, '', $url );
				$path     = $base_path . $relative;

				if ( file_exists( $path ) ) {
					$size_bytes += (int) filesize( $path );
				}
			}

			if ( $row_has_pending ) {
				$total_rows++;
			}
		}

		return array(
			'total_files'       => $total,
			'total_rows'        => $total_rows,
			'total_size_bytes'  => $size_bytes,
			'estimated_seconds' => (int) ceil( $total * self::SECONDS_PER_FILE ),
			'already_migrated'  => $already_migrated,
		);
	}

	/**
	 * Start a new migration job.
	 *
	 * @param array $options { remove_local, batch_size }.
	 * @return array|WP_Error
	 */
	public function start_job( array $options ) {
		if ( ! $this->storage->is_enabled() ) {
			return new WP_Error( 's3_not_configured', __( 'S3 storage is not enabled.', 'ugcify' ), array( 'status' => 400 ) );
		}

		$running = $this->get_running_job_id();
		if ( $running ) {
			return new WP_Error(
				'migration_already_running',
				__( 'A migration job is already running.', 'ugcify' ),
				array( 'status' => 409, 'job_id' => $running )
			);
		}

		$batch_size = max( 5, min( 100, (int) ( $options['batch_size'] ?? 20 ) ) );
		$stats      = $this->get_stats();
		$total      = \is_wp_error( $stats ) ? 0 : (int) ( $stats['total_rows'] ?? 0 );

		$payload = array(
			'job_type'      => self::JOB_TYPE,
			'batch_size'    => $batch_size,
			'total'         => $total,
			'processed'     => 0,
			'uploaded'      => 0,
			'skipped'       => 0,
			'failed'        => 0,
			'deleted_local' => 0,
			'url_updated'   => 0,
			'cursor'        => 0,
			'options'       => array(
				'remove_local'   => ! empty( $options['remove_local'] ),
				'update_db_urls' => true,
			),
			'errors'        => array(),
			'started_at'    => \current_time( 'mysql' ),
			'finished_at'   => null,
		);

		// Handle retry with specific meta_ids.
		if ( ! empty( $options['meta_ids'] ) && is_array( $options['meta_ids'] ) ) {
			$payload['meta_ids']     = array_values( array_map( 'intval', $options['meta_ids'] ) );
			$payload['retry_offset'] = 0;
			$payload['total']        = count( $payload['meta_ids'] );
		}

		$inserted = $this->wpdb->insert(
			$this->logs_table,
			array(
				'type'       => self::JOB_TYPE,
				'payload'    => \wp_json_encode( $payload ),
				'status'     => 'running',
				'created_at' => \current_time( 'mysql' ),
			),
			array( '%s', '%s', '%s', '%s' )
		);

		if ( false === $inserted ) {
			return new WP_Error( 'migration_start_failed', __( 'Failed to create migration job.', 'ugcify' ), array( 'status' => 500 ) );
		}

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

	/**
	 * Process one batch for a running job.
	 *
	 * @param int $job_id Job ID.
	 * @return array|WP_Error
	 */
	public function process_batch( int $job_id ) {
		$job = $this->get_job_row( $job_id );
		if ( ! $job ) {
			return new WP_Error( 'job_not_found', __( 'Migration job not found.', 'ugcify' ), array( 'status' => 404 ) );
		}

		if ( 'running' !== $job['status'] ) {
			return $this->get_snapshot( $job_id );
		}

		$payload    = json_decode( $job['payload'], true );
		$batch_size = (int) ( $payload['batch_size'] ?? 20 );
		$cursor     = (int) ( $payload['cursor'] ?? 0 );
		$opts       = $payload['options'] ?? array();

		// Retry mode: process specific meta_ids.
		if ( ! empty( $payload['meta_ids'] ) ) {
			$offset = (int) ( $payload['retry_offset'] ?? 0 );
			$batch  = array_slice( $payload['meta_ids'], $offset, $batch_size );
			$rows   = empty( $batch ) ? array() : $this->fetch_rows_by_ids( $batch );
		} else {
			$batch_result = $this->fetch_batch( $cursor, $batch_size );
			$rows         = $batch_result['rows'];
			// Advance cursor to avoid re-scanning already-migrated rows.
			$payload['cursor'] = max( $payload['cursor'], $batch_result['next_cursor'] );
		}

		if ( empty( $rows ) ) {
			$payload['finished_at'] = \current_time( 'mysql' );
			$this->update_job( $job_id, 'completed', $payload );
			return $this->get_snapshot( $job_id );
		}

		foreach ( $rows as $row ) {
			$meta_id = (int) $row['meta_id'];
			// In retry mode, cursor is not used. In normal mode, it's already set above.
			if ( empty( $payload['meta_ids'] ) ) {
				$payload['cursor'] = max( $payload['cursor'], $meta_id );
			}
			$payload['processed']++;

			$result = $this->migrate_single( $row, $opts );

			if ( 'uploaded' === $result['action'] ) {
				$payload['uploaded']++;
				if ( ! empty( $result['url_updated'] ) ) {
					$payload['url_updated']++;
				}
				if ( ! empty( $result['deleted_local'] ) ) {
					$payload['deleted_local']++;
				}
			} elseif ( 'skipped' === $result['action'] ) {
				$payload['skipped']++;
			} else {
				$payload['failed']++;
				$payload['errors'][] = array(
					'meta_id' => $meta_id,
					'url'     => $row['meta_value'] ?? '',
					'message' => $result['message'] ?? 'Unknown error',
				);
				if ( count( $payload['errors'] ) > 50 ) {
					$payload['errors'] = array_slice( $payload['errors'], -50 );
				}
			}
		}

		// Advance retry offset.
		if ( ! empty( $payload['meta_ids'] ) ) {
			$payload['retry_offset'] = (int) ( $payload['retry_offset'] ?? 0 ) + count( $rows );
			if ( $payload['retry_offset'] >= count( $payload['meta_ids'] ) ) {
				$payload['finished_at'] = \current_time( 'mysql' );
				$this->update_job( $job_id, 'completed', $payload );
				return $this->get_snapshot( $job_id );
			}
		}

		// For normal (non-retry) jobs, completion is signalled by fetch_batch returning empty.
		// The processed >= total check is a safety net for edge cases.
		if ( empty( $payload['meta_ids'] ) ) {
			$this->update_job( $job_id, 'running', $payload );
		} elseif ( (int) $payload['processed'] >= (int) $payload['total'] ) {
			$payload['finished_at'] = \current_time( 'mysql' );
			$this->update_job( $job_id, 'completed', $payload );
		} else {
			$this->update_job( $job_id, 'running', $payload );
		}

		return $this->get_snapshot( $job_id );
	}

	/**
	 * Get job snapshot.
	 *
	 * @param int $job_id Job ID.
	 * @return array|WP_Error
	 */
	public function get_snapshot( int $job_id ) {
		$job = $this->get_job_row( $job_id );
		if ( ! $job ) {
			return new WP_Error( 'job_not_found', __( 'Migration job not found.', 'ugcify' ), array( 'status' => 404 ) );
		}

		$payload   = json_decode( $job['payload'], true ) ?? array();
		$total     = max( 1, (int) ( $payload['total'] ?? 0 ) );
		$processed = (int) ( $payload['processed'] ?? 0 );

		return array(
			'job_id'        => (int) $job['id'],
			'status'        => $job['status'],
			'percent'       => min( 100, (int) round( ( $processed / $total ) * 100 ) ),
			'total'         => (int) ( $payload['total'] ?? 0 ),
			'processed'     => $processed,
			'uploaded'      => (int) ( $payload['uploaded'] ?? 0 ),
			'skipped'       => (int) ( $payload['skipped'] ?? 0 ),
			'failed'        => (int) ( $payload['failed'] ?? 0 ),
			'deleted_local' => (int) ( $payload['deleted_local'] ?? 0 ),
			'url_updated'   => (int) ( $payload['url_updated'] ?? 0 ),
			'errors'        => $payload['errors'] ?? array(),
			'started_at'    => $payload['started_at'] ?? null,
			'finished_at'   => $payload['finished_at'] ?? null,
			'created_at'    => $job['created_at'],
		);
	}

	/**
	 * Cancel a running job.
	 *
	 * @param int $job_id Job ID.
	 * @return array|WP_Error
	 */
	public function cancel_job( int $job_id ) {
		$job = $this->get_job_row( $job_id );
		if ( ! $job ) {
			return new WP_Error( 'job_not_found', __( 'Migration job not found.', 'ugcify' ), array( 'status' => 404 ) );
		}

		if ( 'running' !== $job['status'] ) {
			return $this->get_snapshot( $job_id );
		}

		$payload                = json_decode( $job['payload'], true ) ?? array();
		$payload['finished_at'] = \current_time( 'mysql' );
		$this->update_job( $job_id, 'cancelled', $payload );

		return $this->get_snapshot( $job_id );
	}

	/**
	 * Retry failed items from a previous job.
	 *
	 * @param int $job_id Source job ID.
	 * @return array|WP_Error
	 */
	public function retry_failed( int $job_id ) {
		$snapshot = $this->get_snapshot( $job_id );
		if ( \is_wp_error( $snapshot ) ) {
			return $snapshot;
		}

		$errors = $snapshot['errors'] ?? array();
		if ( empty( $errors ) ) {
			return new WP_Error( 'no_failed_items', __( 'No failed items to retry.', 'ugcify' ), array( 'status' => 400 ) );
		}

		$meta_ids = array_values( array_unique( array_filter( array_map(
			fn( $e ) => (int) ( $e['meta_id'] ?? 0 ),
			$errors
		) ) ) );

		if ( empty( $meta_ids ) ) {
			return new WP_Error( 'no_failed_items', __( 'No retryable items found.', 'ugcify' ), array( 'status' => 400 ) );
		}

		$job     = $this->get_job_row( $job_id );
		$payload = json_decode( $job['payload'], true ) ?? array();

		return $this->start_job( array(
			'remove_local'   => $payload['options']['remove_local'] ?? false,
			'batch_size'     => $payload['batch_size'] ?? 20,
			'meta_ids'       => $meta_ids,
		) );
	}

	/**
	 * Get migration history (last N jobs).
	 *
	 * @param int $limit Number of jobs to return.
	 * @return array
	 */
	public function get_history( int $limit = 10 ): array {
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->logs_table}
				 WHERE type = %s
				 ORDER BY id DESC
				 LIMIT %d",
				self::JOB_TYPE,
				$limit
			),
			ARRAY_A
		);

		if ( empty( $rows ) ) {
			return array();
		}

		return array_map( fn( $row ) => $this->get_snapshot( (int) $row['id'] ), $rows );
	}

	// =========================================================================
	// Private helpers
	// =========================================================================

	/**
	 * Migrate a single meta row to S3.
	 *
	 * meta_value is a serialized array of URLs e.g. a:1:{i:0;s:45:"https://...";}
	 * Each URL in the array is uploaded individually.
	 *
	 * @param array $row  Meta row with meta_id, meta_value.
	 * @param array $opts Options: remove_local.
	 * @return array { action: uploaded|skipped|failed, message?, url_updated?, deleted_local? }
	 */
	private function migrate_single( array $row, array $opts ): array {
		$meta_id = (int) $row['meta_id'];
		$urls    = \maybe_unserialize( $row['meta_value'] );

		// Normalise: could be a plain string or a serialized array.
		if ( ! is_array( $urls ) ) {
			$urls = is_string( $urls ) && '' !== trim( $urls ) ? array( $urls ) : array();
		}

		if ( empty( $urls ) ) {
			return array( 'action' => 'skipped' );
		}

		$s3_host        = $this->get_s3_host_pattern();
		$updated_urls   = $urls;
		$registry       = $this->load_registry();
		$any_uploaded   = false;
		$any_failed     = false;
		$fail_message   = '';
		$url_updated    = false;
		$deleted_local  = false;

		foreach ( $urls as $index => $url ) {
			if ( ! is_string( $url ) || '' === trim( $url ) ) {
				continue;
			}

			// Skip if already on S3.
			if ( false !== strpos( $url, $s3_host ) ) {
				continue;
			}

			// Skip if already in deduplication registry.
			if ( isset( $registry[ $url ] ) ) {
				$known_s3_url = (string) $registry[ $url ];

				// Keep skip behavior only when the mapped object still exists.
				if ( $this->is_registry_target_available( $known_s3_url ) ) {
					$updated_urls[ $index ] = $known_s3_url;
					continue;
				}

				// Stale registry mapping (object deleted from bucket): re-upload.
				$this->unmark_migrated( $url );
				unset( $registry[ $url ] );
			}

			// Validate URL is local.
			if ( ! $this->is_local_url( $url ) ) {
				continue;
			}

			// Download file to temp location.
			$file = $this->fetch_local_file( $url );
			if ( is_wp_error( $file ) ) {
				$any_failed   = true;
				$fail_message = $file->get_error_message();
				continue;
			}

			// Upload using the same foldering/naming convention as regular review uploads.
			$context     = $this->get_review_context( (int) ( $row['review_id'] ?? 0 ) );
			$date_folder = $this->resolve_migration_date( $url, $context['created_at'] );
			$upload      = $this->storage->upload(
				$file,
				$context['product_id'],
				$context['product_name'],
				$date_folder
			);

			// Clean up temp file — but only if it was created by us (not the original on-disk file).
			if ( empty( $file['_direct'] ) && ! empty( $file['tmp_name'] ) && file_exists( $file['tmp_name'] ) ) {
				wp_delete_file( $file['tmp_name'] );
			}

			if ( is_wp_error( $upload ) ) {
				$any_failed   = true;
				$fail_message = $upload->get_error_message();
				continue;
			}

			$s3_url                 = $upload['url'];
			$updated_urls[ $index ] = $s3_url;
			$any_uploaded           = true;

			$this->mark_migrated( $url, $s3_url );

			// Optionally delete local file.
			if ( ! empty( $opts['remove_local'] ) ) {
				if ( $this->delete_local_file( $url ) ) {
					$deleted_local = true;
				}
			}
		}

		if ( $any_failed && ! $any_uploaded ) {
			return array( 'action' => 'failed', 'message' => $fail_message );
		}

		if ( ! $any_uploaded ) {
			return array( 'action' => 'skipped' );
		}

		// Always update DB with the new S3 URL array.
		$url_updated = $this->update_meta_url( $meta_id, \maybe_serialize( array_values( $updated_urls ) ) );

		return array(
			'action'        => 'uploaded',
			'url_updated'   => $url_updated,
			'deleted_local' => $deleted_local,
		);
	}

	/**
	 * Fetch a batch of meta rows that still contain at least one non-S3 URL.
	 * Uses cursor pagination on meta_id.
	 *
	 * @param int $cursor     Last processed meta_id.
	 * @param int $batch_size Number of rows to fetch.
	 * @return array { rows: array, next_cursor: int }
	 */
	private function fetch_batch( int $cursor, int $batch_size ): array {
		// Fetch a larger window so we can filter in PHP without under-filling batches.
		$window   = $batch_size * 4;
		$s3_host  = $this->get_s3_host_pattern();
		$registry = $this->load_registry();

		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT meta_id, review_id, meta_key, meta_value
				 FROM {$this->meta_table}
				 WHERE meta_key IN ('images','videos')
				 AND meta_value != ''
				 AND meta_id > %d
				 ORDER BY meta_id ASC
				 LIMIT %d",
				$cursor,
				$window
			),
			ARRAY_A
		) ?? array();

		if ( empty( $rows ) ) {
			return array( 'rows' => array(), 'next_cursor' => $cursor );
		}

		// Track the highest meta_id seen in this window so the cursor always advances.
		$window_max = (int) end( $rows )['meta_id'];
		reset( $rows );

		$pending = array();
		foreach ( $rows as $row ) {
			$urls = \maybe_unserialize( $row['meta_value'] );
			if ( ! is_array( $urls ) ) {
				$urls = is_string( $urls ) && '' !== trim( $urls ) ? array( $urls ) : array();
			}

			$has_pending = false;
			foreach ( $urls as $url ) {
				if ( ! is_string( $url ) || '' === trim( $url ) ) {
					continue;
				}
				if ( false !== strpos( $url, $s3_host ) ) {
					continue;
				}
				if ( isset( $registry[ $url ] ) ) {
					continue;
				}
				if ( $this->is_local_url( $url ) ) {
					$has_pending = true;
					break;
				}
			}

			if ( $has_pending ) {
				$pending[] = $row;
				if ( count( $pending ) >= $batch_size ) {
					break;
				}
			}
		}

		// If we filled a full batch, next_cursor is the last pending row's meta_id.
		// If we exhausted the window with fewer rows, advance cursor to window_max so
		// the next call doesn't re-scan the same already-migrated rows.
		$next_cursor = count( $pending ) >= $batch_size
			? (int) end( $pending )['meta_id']
			: $window_max;

		return array( 'rows' => $pending, 'next_cursor' => $next_cursor );
	}

	/**
	 * Fetch meta rows by specific meta_ids (used for retry).
	 *
	 * @param array $meta_ids Array of meta_id integers.
	 * @return array
	 */
	private function fetch_rows_by_ids( array $meta_ids ): array {
		if ( empty( $meta_ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $meta_ids ), '%d' ) );

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT meta_id, review_id, meta_key, meta_value
				 FROM {$this->meta_table}
				 WHERE meta_id IN ($placeholders)",
				$meta_ids
			),
			ARRAY_A
		) ?? array();
	}

	/**
	 * Resolve a local file URL to its filesystem path.
	 *
	 * @param string $url Local file URL.
	 * @return string|false Absolute filesystem path, or false if not resolvable.
	 */
	private function resolve_local_path( string $url ) {
		$upload_dir = \wp_upload_dir();
		$base_url   = rtrim( $upload_dir['baseurl'], '/' );
		$base_path  = rtrim( $upload_dir['basedir'], '/' );

		// Normalise URL: strip query string / fragment.
		$clean_url = strtok( $url, '?' );
		$clean_url = strtok( $clean_url, '#' );

		if ( false === strpos( $clean_url, $base_url ) ) {
			return false;
		}

		$relative = str_replace( $base_url, '', $clean_url );
		$path     = $base_path . $relative;

		// Security: ensure path stays within uploads dir.
		$real = realpath( $path );
		if ( false === $real ) {
			// File may not exist yet — build the path without realpath.
			$normalised = str_replace( array( '/', '\\' ), DIRECTORY_SEPARATOR, $path );
			if ( 0 !== strpos( $normalised, $base_path ) ) {
				return false;
			}
			return $normalised;
		}

		if ( 0 !== strpos( $real, $base_path ) ) {
			return false;
		}

		return $real;
	}

	/**
	 * Read a local file and return a $_FILES-style array for upload.
	 * Reads directly from disk — avoids HTTP loopback issues on local environments.
	 *
	 * @param string $url Local file URL.
	 * @return array|WP_Error
	 */
	private function fetch_local_file( string $url ) {
		$path = $this->resolve_local_path( $url );

		if ( false !== $path && file_exists( $path ) ) {
			// Fast path: read directly from disk.
			$mime = mime_content_type( $path ) ?: 'application/octet-stream';
			return array(
				'name'     => basename( $path ),
				'tmp_name' => $path,
				'type'     => $mime,
				'size'     => (int) filesize( $path ),
				'error'    => 0,
				'_direct'  => true, // Flag: do NOT delete this path after upload (it's the original).
			);
		}

		// Fallback: HTTP fetch (handles CDN-rewritten or non-standard upload paths).
		$response = \wp_remote_get( $url, array( 'timeout' => 30, 'sslverify' => false ) );

		if ( \is_wp_error( $response ) ) {
			return $response;
		}

		$code = \wp_remote_retrieve_response_code( $response );
		if ( 200 !== (int) $code ) {
			return new WP_Error( 'fetch_failed', sprintf( __( 'HTTP %d fetching file.', 'ugcify' ), $code ) );
		}

		if ( ! function_exists( 'wp_tempnam' ) ) {
			require_once ABSPATH . 'wp-admin/includes/file.php';
		}

		$body     = \wp_remote_retrieve_body( $response );
		$tmp_path = \wp_tempnam( basename( $url ) );
		if ( ! $tmp_path ) {
			return new WP_Error( 'temp_file_failed', __( 'Failed to create temp file.', 'ugcify' ) );
		}

		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
		if ( false === file_put_contents( $tmp_path, $body ) ) {
			return new WP_Error( 'write_failed', __( 'Failed to write temp file.', 'ugcify' ) );
		}

		$mime = \wp_remote_retrieve_header( $response, 'content-type' ) ?: mime_content_type( $tmp_path ) ?: 'application/octet-stream';

		return array(
			'name'     => basename( $url ),
			'tmp_name' => $tmp_path,
			'type'     => $mime,
			'size'     => strlen( $body ),
			'error'    => 0,
		);
	}

	/**
	 * Update meta_value in DB to the new S3 URL.
	 *
	 * @param int    $meta_id Meta row ID.
	 * @param string $s3_url  New S3 public URL.
	 * @return bool
	 */
	private function update_meta_url( int $meta_id, string $s3_url ): bool {
		$result = $this->wpdb->update(
			$this->meta_table,
			array( 'meta_value' => $s3_url ),
			array( 'meta_id' => $meta_id ),
			array( '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Delete a local file from the server by resolving its URL to a filesystem path.
	 *
	 * @param string $url Local file URL.
	 * @return bool
	 */
	private function delete_local_file( string $url ): bool {
		$path = $this->resolve_local_path( $url );

		if ( false === $path ) {
			return false;
		}

		if ( ! file_exists( $path ) ) {
			return true; // Already deleted.
		}

		\wp_delete_file( $path );
		return ! file_exists( $path );
	}

	/**
	 * Check if a URL is local (within the WordPress uploads directory).
	 *
	 * @param string $url URL to check.
	 * @return bool
	 */
	private function is_local_url( string $url ): bool {
		$upload_dir = \wp_upload_dir();
		$site_url   = \home_url();

		return false !== strpos( $url, $upload_dir['baseurl'] )
			|| false !== strpos( $url, $site_url );
	}

	/**
	 * Load the migrated-URL registry from wp_options.
	 *
	 * @return array Map of original_url => s3_url.
	 */
	private function load_registry(): array {
		$raw = \get_option( self::MIGRATED_OPTION, '{}' );
		$map = json_decode( $raw, true );
		return is_array( $map ) ? $map : array();
	}

	/**
	 * Check if a URL has already been migrated.
	 *
	 * @param string $url Original local URL.
	 * @return bool
	 */
	private function is_migrated( string $url ): bool {
		$registry = $this->load_registry();
		return isset( $registry[ $url ] );
	}

	/**
	 * Mark a URL as migrated in the wp_options registry.
	 *
	 * @param string $original_url Original local URL.
	 * @param string $s3_url       New S3 URL.
	 */
	private function mark_migrated( string $original_url, string $s3_url ): void {
		$registry                  = $this->load_registry();
		$registry[ $original_url ] = $s3_url;
		\update_option( self::MIGRATED_OPTION, \wp_json_encode( $registry ), false );
	}

	/**
	 * Remove a URL mapping from the migration registry.
	 *
	 * @param string $original_url Original local URL.
	 */
	private function unmark_migrated( string $original_url ): void {
		$registry = $this->load_registry();
		if ( ! isset( $registry[ $original_url ] ) ) {
			return;
		}

		unset( $registry[ $original_url ] );
		\update_option( self::MIGRATED_OPTION, \wp_json_encode( $registry ), false );
	}

	/**
	 * Validate that a registry target URL still points to an existing S3 object.
	 *
	 * If existence cannot be reliably checked, returns true to preserve safe behavior.
	 *
	 * @param string $s3_url Registry target URL.
	 * @return bool
	 */
	private function is_registry_target_available( string $s3_url ): bool {
		$key = $this->extract_object_key_from_s3_url( $s3_url );
		if ( '' === $key ) {
			return true;
		}

		$exists = $this->storage->object_exists( $key );
		if ( is_wp_error( $exists ) ) {
			return true;
		}

		return (bool) $exists;
	}

	/**
	 * Extract S3 object key from a public object URL.
	 *
	 * Supports both virtual-hosted and path-style URLs.
	 *
	 * @param string $s3_url Public S3 URL.
	 * @return string
	 */
	private function extract_object_key_from_s3_url( string $s3_url ): string {
		$settings = $this->storage->get_settings();
		$bucket   = (string) ( $settings['bucket'] ?? '' );
		$host     = (string) \wp_parse_url( $s3_url, PHP_URL_HOST );
		$path     = trim( (string) \wp_parse_url( $s3_url, PHP_URL_PATH ), '/' );

		if ( '' === $bucket || '' === $host || '' === $path ) {
			return '';
		}

		// Virtual-hosted style: <bucket>.s3.<region>.amazonaws.com/<key>
		if ( 0 === strpos( $host, $bucket . '.' ) ) {
			return $path;
		}

		// Path-style: <endpoint>/<bucket>/<key>
		if ( 0 === strpos( $path, $bucket . '/' ) ) {
			return substr( $path, strlen( $bucket ) + 1 );
		}

		// Custom endpoint host fallback.
		if ( ! empty( $settings['custom_endpoint'] ) ) {
			$endpoint_host = (string) \wp_parse_url( (string) $settings['custom_endpoint'], PHP_URL_HOST );
			if ( '' !== $endpoint_host && $endpoint_host === $host ) {
				if ( 0 === strpos( $path, $bucket . '/' ) ) {
					return substr( $path, strlen( $bucket ) + 1 );
				}
				return $path;
			}
		}

		return '';
	}

	/**
	 * Get the S3 host pattern to detect already-migrated URLs.
	 *
	 * @return string
	 */
	private function get_s3_host_pattern(): string {
		$settings = $this->storage->get_settings();

		if ( ! empty( $settings['custom_endpoint'] ) ) {
			return rtrim( $settings['custom_endpoint'], '/' );
		}

		return 'amazonaws.com';
	}

	/**
	 * Resolve review context (product_id, product_name, created_at) with request-local cache.
	 *
	 * @param int $review_id Review ID.
	 * @return array{product_id:int, product_name:string, created_at:?string}
	 */
	private function get_review_context( int $review_id ): array {
		if ( isset( $this->review_context_cache[ $review_id ] ) ) {
			return $this->review_context_cache[ $review_id ];
		}

		$fallback = array(
			'product_id'  => 0,
			'product_name' => 'unknown',
			'created_at'  => null,
		);

		if ( $review_id <= 0 ) {
			$this->review_context_cache[ $review_id ] = $fallback;
			return $fallback;
		}

		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT product_id, created_at
				 FROM {$this->reviews_table}
				 WHERE id = %d
				 LIMIT 1",
				$review_id
			),
			ARRAY_A
		);

		if ( ! $row ) {
			$this->review_context_cache[ $review_id ] = $fallback;
			return $fallback;
		}

		$product_id = (int) ( $row['product_id'] ?? 0 );
		$context    = array(
			'product_id'  => $product_id,
			'product_name' => $this->resolve_product_name( $product_id ),
			'created_at'  => ! empty( $row['created_at'] ) ? (string) $row['created_at'] : null,
		);

		$this->review_context_cache[ $review_id ] = $context;
		return $context;
	}

	/**
	 * Resolve product name for filename variation.
	 *
	 * @param int $product_id Product ID.
	 * @return string
	 */
	private function resolve_product_name( int $product_id ): string {
		if ( $product_id > 0 && function_exists( 'wc_get_product' ) ) {
			$product = \wc_get_product( $product_id );
			if ( $product && method_exists( $product, 'get_name' ) ) {
				$name = (string) $product->get_name();
				if ( '' !== trim( $name ) ) {
					return $name;
				}
			}
		}

		return 'unknown';
	}

	/**
	 * Resolve the migration date folder from URL history or review created_at.
	 *
	 * @param string      $url               Original local media URL.
	 * @param string|null $review_created_at Review created_at timestamp.
	 * @return string Date in Y-m-d format.
	 */
	private function resolve_migration_date( string $url, ?string $review_created_at ): string {
		$path = (string) \wp_parse_url( $url, PHP_URL_PATH );

		if ( preg_match( '#/(\d{4})/(\d{2})/(\d{2})/#', $path, $parts ) ) {
			return "{$parts[1]}-{$parts[2]}-{$parts[3]}";
		}

		if ( ! empty( $review_created_at ) ) {
			$timestamp = strtotime( $review_created_at );
			if ( false !== $timestamp ) {
				return gmdate( 'Y-m-d', $timestamp );
			}
		}

		if ( preg_match( '#/(\d{4})/(\d{2})/#', $path, $parts ) ) {
			return "{$parts[1]}-{$parts[2]}-01";
		}

		return gmdate( 'Y-m-d' );
	}

	/**
	 * Get the ID of any currently running job.
	 *
	 * @return int|null
	 */
	private function get_running_job_id(): ?int {
		$id = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->logs_table} WHERE type = %s AND status = 'running' LIMIT 1",
				self::JOB_TYPE
			)
		);

		return $id ? (int) $id : null;
	}

	/**
	 * Get a raw job row from automation_logs.
	 *
	 * @param int $job_id Job ID.
	 * @return array|null
	 */
	private function get_job_row( int $job_id ): ?array {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->logs_table} WHERE id = %d AND type = %s LIMIT 1",
				$job_id,
				self::JOB_TYPE
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Update a job row in automation_logs.
	 *
	 * @param int    $job_id  Job ID.
	 * @param string $status  New status.
	 * @param array  $payload Updated payload.
	 */
	private function update_job( int $job_id, string $status, array $payload ): void {
		$this->wpdb->update(
			$this->logs_table,
			array(
				'status'  => $status,
				'payload' => \wp_json_encode( $payload ),
			),
			array( 'id' => $job_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}
}
