<?php
/**
 * Review Migration Service.
 *
 * @package Ugcify\Migration
 */

namespace Ugcify\Migration;

use Ugcify\Review\ReviewMeta;
use WP_Error;

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

/**
 * Handles bidirectional review migration between WooCommerce comments and UGC review tables.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Migration service; all queries use $wpdb->prepare(); table names are trusted internal constants.
 */
class ReviewMigrationService {
	/**
	 * Job type identifier.
	 */
	const JOB_TYPE = 'review_migration';

	/**
	 * Database instance.
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * UGC reviews table.
	 *
	 * @var string
	 */
	private $ugcify_reviews_table;

	/**
	 * UGC review meta table.
	 *
	 * @var string
	 */
	private $ugcify_review_meta_table;

	/**
	 * Review sync map table.
	 *
	 * @var string
	 */
	private $sync_map_table;

	/**
	 * Automation logs table.
	 *
	 * @var string
	 */
	private $automation_logs_table;

	/**
	 * Review meta utility.
	 *
	 * @var ReviewMeta
	 */
	private $review_meta;

	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;

		$this->wpdb                  = $wpdb;
		$this->ugcify_reviews_table     = $wpdb->prefix . 'ugcify_reviews';
		$this->ugcify_review_meta_table = $wpdb->prefix . 'ugcify_review_meta';
		$this->sync_map_table        = $wpdb->prefix . 'ugcify_review_sync_map';
		$this->automation_logs_table = $wpdb->prefix . 'ugcify_automation_logs';
		$this->review_meta           = new ReviewMeta();
	}

	/**
	 * Start a migration job.
	 *
	 * @param string $direction Migration direction.
	 * @param array  $options Job options.
	 * @return array|WP_Error
	 */
	public function start_job( $direction, array $options = array() ) {
		$direction = $this->sanitize_direction( $direction );
		if ( is_wp_error( $direction ) ) {
			return $direction;
		}

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

		$batch_size = isset( $options['batch_size'] ) ? (int) $options['batch_size'] : 100;
		$batch_size = max( 10, min( 500, $batch_size ) );
		$total      = $this->count_source_reviews( $direction );

		$source_ids = array();
		if ( ! empty( $options['source_ids'] ) && is_array( $options['source_ids'] ) ) {
			$source_ids = array_values(
				array_unique(
					array_filter(
						array_map( 'intval', $options['source_ids'] ),
						function ( $id ) {
							return $id > 0;
						}
					)
				)
			);
		}

		$total = ! empty( $source_ids ) ? count( $source_ids ) : $total;

		$payload = array(
			'direction'   => $direction,
			'batch_size'  => $batch_size,
			'total'       => (int) $total,
			'processed'   => 0,
			'inserted'    => 0,
			'updated'     => 0,
			'skipped'     => 0,
			'failed'      => 0,
			'cursor'      => 0,
			'errors'      => array(),
			'started_at'  => current_time( 'mysql' ),
			'finished_at' => null,
		);

		if ( ! empty( $source_ids ) ) {
			$payload['source_ids']   = $source_ids;
			$payload['retry_offset'] = 0;
		}

		$result = $this->wpdb->insert(
			$this->automation_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 === $result ) {
			return new WP_Error(
				'migration_start_failed',
				__( 'Failed to start migration job.', 'ugcify' ),
				array( 'status' => 500 )
			);
		}

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

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

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

		$payload = $this->decode_payload( $job['payload'] );
		if ( empty( $payload['direction'] ) || empty( $payload['batch_size'] ) ) {
			return new WP_Error(
				'invalid_migration_payload',
				__( 'Invalid migration payload.', 'ugcify' ),
				array( 'status' => 500 )
			);
		}

		$direction  = $payload['direction'];
		$batch_size = (int) $payload['batch_size'];
		$cursor     = isset( $payload['cursor'] ) ? (int) $payload['cursor'] : 0;
		$rows       = array();
		$batch_ids  = array();

		if ( ! empty( $payload['source_ids'] ) && is_array( $payload['source_ids'] ) ) {
			$retry_offset = isset( $payload['retry_offset'] ) ? (int) $payload['retry_offset'] : 0;
			$batch_ids    = array_slice( $payload['source_ids'], $retry_offset, $batch_size );
			$rows         = $this->fetch_source_rows_by_ids( $direction, $batch_ids );
		} else {
			$rows = $this->fetch_source_rows( $direction, $cursor, $batch_size );
		}

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

		foreach ( $rows as $row ) {
			$source_id          = $this->extract_source_id( $direction, $row );
			$payload['cursor']  = max( (int) $payload['cursor'], $source_id );
			$payload['processed']++;

			try {
				$result = $this->process_single_review( $direction, $row );
				$action = isset( $result['action'] ) ? $result['action'] : 'skipped';

				if ( isset( $payload[ $action ] ) ) {
					$payload[ $action ]++;
				} else {
					$payload['skipped']++;
				}
			} catch ( \Throwable $e ) {
				$payload['failed']++;
				$payload['errors'][] = array(
					'source_id' => $source_id,
					'message'   => $e->getMessage(),
				);

				// Keep payload compact.
				if ( count( $payload['errors'] ) > 50 ) {
					$payload['errors'] = array_slice( $payload['errors'], -50 );
				}
			}
		}

		if ( ! empty( $payload['source_ids'] ) && is_array( $payload['source_ids'] ) ) {
			$payload['retry_offset'] = (int) ( $payload['retry_offset'] ?? 0 ) + count( $batch_ids );
			if ( (int) $payload['retry_offset'] >= count( $payload['source_ids'] ) ) {
				$payload['finished_at'] = current_time( 'mysql' );
				$this->update_job( $job_id, 'completed', $payload );
				return $this->get_job_snapshot( $job_id );
			}
		}

		if ( (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_job_snapshot( $job_id );
	}

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

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

		$payload                = $this->decode_payload( $job['payload'] );
		$payload['finished_at'] = current_time( 'mysql' );
		$this->update_job( $job_id, 'cancelled', $payload );

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

	/**
	 * Start a retry job from failed items of an old job.
	 *
	 * @param int $job_id Source job id.
	 * @return array|WP_Error
	 */
	public function retry_failed( $job_id ) {
		$snapshot = $this->get_job_snapshot( $job_id );
		if ( is_wp_error( $snapshot ) ) {
			return $snapshot;
		}

		$payload = $snapshot['payload'];
		$errors  = isset( $payload['errors'] ) && is_array( $payload['errors'] ) ? $payload['errors'] : array();
		if ( empty( $errors ) ) {
			return new WP_Error(
				'no_failed_items',
				__( 'There are no failed items to retry.', 'ugcify' ),
				array( 'status' => 400 )
			);
		}

		$source_ids = array_values(
			array_unique(
				array_filter(
					array_map(
						function ( $error ) {
							return isset( $error['source_id'] ) ? (int) $error['source_id'] : 0;
						},
						$errors
					),
					function ( $id ) {
						return $id > 0;
					}
				)
			)
		);

		if ( empty( $source_ids ) ) {
			return new WP_Error(
				'no_failed_items',
				__( 'There are no retryable failed items.', 'ugcify' ),
				array( 'status' => 400 )
			);
		}

		return $this->start_job(
			$payload['direction'],
			array(
				'batch_size' => $payload['batch_size'] ?? 100,
				'source_ids' => $source_ids,
			)
		);
	}

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

		$payload = $this->decode_payload( $job['payload'] );
		$total   = max( 0, (int) ( $payload['total'] ?? 0 ) );
		$done    = max( 0, (int) ( $payload['processed'] ?? 0 ) );

		return array(
			'id'         => (int) $job['id'],
			'status'     => $job['status'],
			'type'       => $job['type'],
			'created_at' => $job['created_at'],
			'progress'   => $total > 0 ? min( 100, (int) round( ( $done / $total ) * 100 ) ) : 0,
			'payload'    => $payload,
		);
	}

	/**
	 * Process one source review based on direction.
	 *
	 * @param string $direction Direction.
	 * @param array  $row Source row.
	 * @return array
	 */
	private function process_single_review( $direction, array $row ) {
		if ( 'wc_to_ugc' === $direction ) {
			return $this->migrate_wc_to_ugc( $row );
		}

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

	/**
	 * Migrate one WooCommerce review comment into UGC review table.
	 *
	 * @param array $wc_row WooCommerce comment row.
	 * @return array
	 */
	private function migrate_wc_to_ugc( array $wc_row ) {
		$wc_comment_id = (int) $wc_row['comment_ID'];
		$rating        = $this->get_wc_rating( $wc_comment_id );
		$wc_hash       = $this->hash_wc_review( $wc_row, $rating );

		$map = $this->find_map_by_wc_comment_id( $wc_comment_id );

		if ( $map && ! empty( $map['ugcify_review_id'] ) ) {
			$ugcify_review_id = (int) $map['ugcify_review_id'];
			$ugcify_row       = $this->get_ugcify_review_row( $ugcify_review_id );

			if ( $ugcify_row ) {
				$current_ugcify_hash = $this->hash_ugcify_review( $ugcify_row );
				$mapped_ugcify_hash  = ! empty( $map['ugcify_hash'] ) ? $map['ugcify_hash'] : '';
				$mapped_wc_hash   = ! empty( $map['wc_hash'] ) ? $map['wc_hash'] : '';

				// Source data unchanged since last sync.
				if ( $mapped_wc_hash && $mapped_wc_hash === $wc_hash ) {
					return array( 'action' => 'skipped' );
				}

				// Protect local UGC edits when WooCommerce source has not changed since last sync.
				if ( $mapped_ugcify_hash && $mapped_ugcify_hash !== $current_ugcify_hash && $mapped_wc_hash === $wc_hash ) {
					$this->upsert_map(
						array(
							'id'                   => (int) $map['id'],
							'last_error'           => __( 'Skipped import to avoid overriding local UGC edits.', 'ugcify' ),
							'last_synced_at'       => current_time( 'mysql' ),
							'last_synced_direction'=> 'wc_to_ugc',
						)
					);

					return array( 'action' => 'skipped' );
				}

				$update_data = $this->map_wc_row_to_ugcify_data( $wc_row, $rating, false );
				$updated     = $this->wpdb->update(
					$this->ugcify_reviews_table,
					$update_data,
					array( 'id' => $ugcify_review_id ),
					array( '%d', '%s', '%s', '%d', '%d', '%s', '%s', '%s' ),
					array( '%d' )
				);

				if ( false === $updated ) {
					throw new \RuntimeException( __( 'Failed to update mapped UGC review.', 'ugcify' ) ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
				}

				$this->review_meta->update_meta( $ugcify_review_id, 'source_comment_id', (string) $wc_comment_id );
				$this->review_meta->update_meta( $ugcify_review_id, 'wc_comment_id', (string) $wc_comment_id );

				$ugcify_row      = $this->get_ugcify_review_row( $ugcify_review_id );
				$new_ugcify_hash = $this->hash_ugcify_review( $ugcify_row );

				$this->upsert_map(
					array(
						'id'                    => (int) $map['id'],
						'ugcify_review_id'         => $ugcify_review_id,
						'wc_comment_id'         => $wc_comment_id,
						'source_of_truth'       => 'wc',
						'last_synced_direction' => 'wc_to_ugc',
						'ugcify_hash'              => $new_ugcify_hash,
						'wc_hash'               => $wc_hash,
						'last_synced_at'        => current_time( 'mysql' ),
						'last_error'            => '',
					)
				);

				return array( 'action' => 'updated' );
			}
		}

		$insert_data = $this->map_wc_row_to_ugcify_data( $wc_row, $rating, true );
		$inserted    = $this->wpdb->insert(
			$this->ugcify_reviews_table,
			$insert_data,
			// Keep format order aligned with $insert_data key insertion order.
			array( '%d', '%s', '%s', '%d', '%d', '%s', '%s', '%s', '%d', '%d', '%s', '%d', '%s' )
		);

		if ( false === $inserted ) {
			throw new \RuntimeException( __( 'Failed to insert migrated UGC review.', 'ugcify' ) ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
		}

		$ugcify_review_id = (int) $this->wpdb->insert_id;
		$this->review_meta->update_meta( $ugcify_review_id, 'source_comment_id', (string) $wc_comment_id );
		$this->review_meta->update_meta( $ugcify_review_id, 'wc_comment_id', (string) $wc_comment_id );

		$ugcify_row  = $this->get_ugcify_review_row( $ugcify_review_id );
		$ugcify_hash = $this->hash_ugcify_review( $ugcify_row );

		$this->upsert_map(
			array(
				'id'                    => $map['id'] ?? null,
				'ugcify_review_id'         => $ugcify_review_id,
				'wc_comment_id'         => $wc_comment_id,
				'source_of_truth'       => 'wc',
				'last_synced_direction' => 'wc_to_ugc',
				'ugcify_hash'              => $ugcify_hash,
				'wc_hash'               => $wc_hash,
				'last_synced_at'        => current_time( 'mysql' ),
				'last_error'            => '',
			)
		);

		return array( 'action' => 'inserted' );
	}

	/**
	 * Migrate one UGC review into WooCommerce review comments.
	 *
	 * @param array $ugcify_row UGC review row.
	 * @return array
	 */
	private function migrate_ugcify_to_wc( array $ugcify_row ) {
		$ugcify_review_id = (int) $ugcify_row['id'];
		$ugcify_hash      = $this->hash_ugcify_review( $ugcify_row );

		$map = $this->find_map_by_ugcify_review_id( $ugcify_review_id );

		$wc_comment_id = $map && ! empty( $map['wc_comment_id'] ) ? (int) $map['wc_comment_id'] : 0;
		$wc_comment    = $wc_comment_id ? $this->get_wc_comment_row( $wc_comment_id ) : null;

		if ( $wc_comment ) {
			$mapped_ugcify_hash = ! empty( $map['ugcify_hash'] ) ? $map['ugcify_hash'] : '';
			if ( $mapped_ugcify_hash && $mapped_ugcify_hash === $ugcify_hash ) {
				return array( 'action' => 'skipped' );
			}

			$comment_data = $this->map_ugcify_row_to_wc_comment_data( $ugcify_row, $wc_comment_id );
			$updated_id   = wp_update_comment( $comment_data, true );

			if ( is_wp_error( $updated_id ) ) {
				throw new \RuntimeException( $updated_id->get_error_message() ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
			}

			update_comment_meta( $wc_comment_id, 'rating', (int) $ugcify_row['rating'] );
			update_comment_meta( $wc_comment_id, '_ugcify_migrated', '1' );
			update_comment_meta( $wc_comment_id, '_ugcify_review_id', (string) $ugcify_review_id );

			$wc_comment = $this->get_wc_comment_row( $wc_comment_id );
			$wc_hash    = $this->hash_wc_review( $wc_comment, $this->get_wc_rating( $wc_comment_id ) );

			$this->review_meta->update_meta( $ugcify_review_id, 'wc_comment_id', (string) $wc_comment_id );

			$this->upsert_map(
				array(
					'id'                    => (int) $map['id'],
					'ugcify_review_id'         => $ugcify_review_id,
					'wc_comment_id'         => $wc_comment_id,
					'source_of_truth'       => 'ugcify',
					'last_synced_direction' => 'ugcify_to_wc',
					'ugcify_hash'              => $ugcify_hash,
					'wc_hash'               => $wc_hash,
					'last_synced_at'        => current_time( 'mysql' ),
					'last_error'            => '',
				)
			);

			return array( 'action' => 'updated' );
		}

		$comment_data   = $this->map_ugcify_row_to_wc_comment_data( $ugcify_row );
		$new_comment_id = wp_insert_comment( $comment_data, true );
		if ( is_wp_error( $new_comment_id ) ) {
			throw new \RuntimeException( $new_comment_id->get_error_message() ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
		}

		$new_comment_id = (int) $new_comment_id;
		update_comment_meta( $new_comment_id, 'rating', (int) $ugcify_row['rating'] );
		update_comment_meta( $new_comment_id, '_ugcify_migrated', '1' );
		update_comment_meta( $new_comment_id, '_ugcify_review_id', (string) $ugcify_review_id );

		$wc_comment = $this->get_wc_comment_row( $new_comment_id );
		$wc_hash    = $this->hash_wc_review( $wc_comment, $this->get_wc_rating( $new_comment_id ) );

		$this->review_meta->update_meta( $ugcify_review_id, 'wc_comment_id', (string) $new_comment_id );

		$this->upsert_map(
			array(
				'id'                    => $map['id'] ?? null,
				'ugcify_review_id'         => $ugcify_review_id,
				'wc_comment_id'         => $new_comment_id,
				'source_of_truth'       => 'ugcify',
				'last_synced_direction' => 'ugcify_to_wc',
				'ugcify_hash'              => $ugcify_hash,
				'wc_hash'               => $wc_hash,
				'last_synced_at'        => current_time( 'mysql' ),
				'last_error'            => '',
			)
		);

		return array( 'action' => 'inserted' );
	}

	/**
	 * Fetch source rows by direction.
	 *
	 * @param string $direction Direction.
	 * @param int    $cursor Last processed source ID.
	 * @param int    $batch_size Batch size.
	 * @return array
	 */
	private function fetch_source_rows( $direction, $cursor, $batch_size ) {
		if ( 'wc_to_ugc' === $direction ) {
			return $this->wpdb->get_results(
				$this->wpdb->prepare(
					"SELECT c.comment_ID, c.comment_post_ID, c.user_id, c.comment_author, c.comment_author_email, c.comment_content, c.comment_approved, c.comment_date, c.comment_date_gmt
					FROM {$this->wpdb->comments} AS c
					INNER JOIN {$this->wpdb->posts} AS p ON p.ID = c.comment_post_ID AND p.post_type = 'product'
					WHERE c.comment_type = 'review'
						AND c.comment_approved NOT IN ('spam', 'trash', 'post-trashed')
						AND c.comment_ID > %d
					ORDER BY c.comment_ID ASC
					LIMIT %d",
					$cursor,
					$batch_size
				),
				ARRAY_A
			);
		}

		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT id, user_id, guest_name, guest_email, product_id, rating, content, status, type, created_at
				FROM {$this->ugcify_reviews_table}
				WHERE id > %d
				ORDER BY id ASC
				LIMIT %d",
				$cursor,
				$batch_size
			),
			ARRAY_A
		);
	}

	/**
	 * Fetch source rows by fixed ids (used for failed-item retries).
	 *
	 * @param string $direction Direction.
	 * @param array  $source_ids Source IDs.
	 * @return array
	 */
	private function fetch_source_rows_by_ids( $direction, array $source_ids ) {
		if ( empty( $source_ids ) ) {
			return array();
		}

		$source_ids   = array_map( 'intval', $source_ids );
		$placeholders = implode( ',', array_fill( 0, count( $source_ids ), '%d' ) );

		if ( 'wc_to_ugc' === $direction ) {
			$sql = "SELECT c.comment_ID, c.comment_post_ID, c.user_id, c.comment_author, c.comment_author_email, c.comment_content, c.comment_approved, c.comment_date, c.comment_date_gmt
				FROM {$this->wpdb->comments} AS c
				INNER JOIN {$this->wpdb->posts} AS p ON p.ID = c.comment_post_ID AND p.post_type = 'product'
				WHERE c.comment_type = 'review'
					AND c.comment_approved NOT IN ('spam', 'trash', 'post-trashed')
					AND c.comment_ID IN ($placeholders)
				ORDER BY c.comment_ID ASC";

			return $this->wpdb->get_results( $this->wpdb->prepare( $sql, $source_ids ), ARRAY_A );
		}

		$sql = "SELECT id, user_id, guest_name, guest_email, product_id, rating, content, status, type, created_at
			FROM {$this->ugcify_reviews_table}
			WHERE id IN ($placeholders)
			ORDER BY id ASC";

		return $this->wpdb->get_results( $this->wpdb->prepare( $sql, $source_ids ), ARRAY_A );
	}

	/**
	 * Count source records by direction.
	 *
	 * @param string $direction Direction.
	 * @return int
	 */
	private function count_source_reviews( $direction ) {
		if ( 'wc_to_ugc' === $direction ) {
			return (int) $this->wpdb->get_var(
				"SELECT COUNT(1)
				FROM {$this->wpdb->comments} AS c
				INNER JOIN {$this->wpdb->posts} AS p ON p.ID = c.comment_post_ID AND p.post_type = 'product'
				WHERE c.comment_type = 'review'
					AND c.comment_approved NOT IN ('spam', 'trash', 'post-trashed')"
			);
		}

		return (int) $this->wpdb->get_var( "SELECT COUNT(1) FROM {$this->ugcify_reviews_table}" );
	}

	/**
	 * Map WooCommerce comment row to UGC review data.
	 *
	 * @param array $row WooCommerce row.
	 * @param int   $rating Rating.
	 * @param bool  $for_insert Whether insert format is required.
	 * @return array
	 */
	private function map_wc_row_to_ugcify_data( array $row, $rating, $for_insert = false ) {
		$user_id = ! empty( $row['user_id'] ) ? (int) $row['user_id'] : null;
		$date    = $this->resolve_comment_date( $row );
		$data    = array(
			'user_id'     => $user_id,
			'guest_name'  => $user_id ? null : sanitize_text_field( (string) $row['comment_author'] ),
			'guest_email' => $user_id ? null : sanitize_email( (string) $row['comment_author_email'] ),
			'product_id'  => (int) $row['comment_post_ID'],
			'rating'      => max( 1, min( 5, (int) $rating ) ),
			'content'     => (string) $row['comment_content'],
			'status'      => $this->map_wc_status_to_ugcify_status( $row['comment_approved'] ),
			'updated_at'  => current_time( 'mysql' ),
		);

		if ( $for_insert ) {
			$data['is_featured'] = 0;
			$data['is_pinned']   = 0;
			$data['type']        = 'text';
			$data['points']      = 0;
			$data['created_at']  = $date;
			$data['updated_at']  = $date;
		}

		return $data;
	}

	/**
	 * Build wc comment payload from a UGC row.
	 *
	 * @param array   $ugcify_row UGC row.
	 * @param int|nil $comment_id Optional existing comment ID.
	 * @return array
	 */
	private function map_ugcify_row_to_wc_comment_data( array $ugcify_row, $comment_id = null ) {
		$user_id       = ! empty( $ugcify_row['user_id'] ) ? (int) $ugcify_row['user_id'] : 0;
		$author_name   = $user_id ? $this->resolve_user_display_name( $user_id ) : (string) $ugcify_row['guest_name'];
		$author_email  = $user_id ? $this->resolve_user_email( $user_id ) : (string) $ugcify_row['guest_email'];
		$created_local = ! empty( $ugcify_row['created_at'] ) ? $ugcify_row['created_at'] : current_time( 'mysql' );
		$created_gmt   = get_gmt_from_date( $created_local );

		$data = array(
			'comment_post_ID'      => (int) $ugcify_row['product_id'],
			'comment_content'      => (string) $ugcify_row['content'],
			'user_id'              => $user_id,
			'comment_author'       => $author_name,
			'comment_author_email' => $author_email,
			'comment_type'         => 'review',
			'comment_approved'     => $this->map_ugcify_status_to_wc_status( $ugcify_row['status'] ),
			'comment_date'         => $created_local,
			'comment_date_gmt'     => $created_gmt,
		);

		if ( $comment_id ) {
			$data['comment_ID'] = (int) $comment_id;
		}

		return $data;
	}

	/**
	 * Find map row by WooCommerce comment ID.
	 *
	 * @param int $wc_comment_id WooCommerce comment ID.
	 * @return array|null
	 */
	private function find_map_by_wc_comment_id( $wc_comment_id ) {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->sync_map_table} WHERE wc_comment_id = %d LIMIT 1",
				$wc_comment_id
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Find map row by UGC review ID.
	 *
	 * @param int $ugcify_review_id UGC review ID.
	 * @return array|null
	 */
	private function find_map_by_ugcify_review_id( $ugcify_review_id ) {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->sync_map_table} WHERE ugcify_review_id = %d LIMIT 1",
				$ugcify_review_id
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Insert or update a map row.
	 *
	 * @param array $data Map data.
	 * @return array|null
	 */
	private function upsert_map( array $data ) {
		$now = current_time( 'mysql' );

		if ( ! empty( $data['id'] ) ) {
			$map_id = (int) $data['id'];
			unset( $data['id'] );
			$data['updated_at'] = $now;

			$this->wpdb->update(
				$this->sync_map_table,
				$data,
				array( 'id' => $map_id )
			);

			return $this->wpdb->get_row(
				$this->wpdb->prepare( "SELECT * FROM {$this->sync_map_table} WHERE id = %d", $map_id ),
				ARRAY_A
			);
		}

		$data['created_at'] = $data['created_at'] ?? $now;
		$data['updated_at'] = $now;

		$this->wpdb->insert( $this->sync_map_table, $data );
		if ( ! $this->wpdb->insert_id ) {
			// Best effort fallback, useful when unique key prevented insert.
			if ( ! empty( $data['ugcify_review_id'] ) ) {
				return $this->find_map_by_ugcify_review_id( (int) $data['ugcify_review_id'] );
			}
			if ( ! empty( $data['wc_comment_id'] ) ) {
				return $this->find_map_by_wc_comment_id( (int) $data['wc_comment_id'] );
			}
			return null;
		}

		return $this->wpdb->get_row(
			$this->wpdb->prepare( "SELECT * FROM {$this->sync_map_table} WHERE id = %d", (int) $this->wpdb->insert_id ),
			ARRAY_A
		);
	}

	/**
	 * Extract source ID from source row based on direction.
	 *
	 * @param string $direction Direction.
	 * @param array  $row Source row.
	 * @return int
	 */
	private function extract_source_id( $direction, array $row ) {
		return 'wc_to_ugc' === $direction ? (int) $row['comment_ID'] : (int) $row['id'];
	}

	/**
	 * Get UGC review row by ID.
	 *
	 * @param int $ugcify_review_id UGC review ID.
	 * @return array|null
	 */
	private function get_ugcify_review_row( $ugcify_review_id ) {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT id, user_id, guest_name, guest_email, product_id, rating, content, status, type, created_at, updated_at
				FROM {$this->ugcify_reviews_table}
				WHERE id = %d",
				$ugcify_review_id
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Get WooCommerce comment row by ID.
	 *
	 * @param int $wc_comment_id Comment ID.
	 * @return array|null
	 */
	private function get_wc_comment_row( $wc_comment_id ) {
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT comment_ID, comment_post_ID, user_id, comment_author, comment_author_email, comment_content, comment_approved, comment_date, comment_date_gmt
				FROM {$this->wpdb->comments}
				WHERE comment_ID = %d",
				$wc_comment_id
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Resolve rating for a WooCommerce comment.
	 *
	 * @param int $wc_comment_id Comment ID.
	 * @return int
	 */
	private function get_wc_rating( $wc_comment_id ) {
		$rating = (int) get_comment_meta( $wc_comment_id, 'rating', true );
		if ( $rating < 1 || $rating > 5 ) {
			$rating = 5;
		}
		return $rating;
	}

	/**
	 * Build hash for UGC review row.
	 *
	 * @param array $row UGC row.
	 * @return string
	 */
	private function hash_ugcify_review( array $row ) {
		$data = array(
			'user_id'     => (int) ( $row['user_id'] ?? 0 ),
			'guest_name'  => (string) ( $row['guest_name'] ?? '' ),
			'guest_email' => (string) ( $row['guest_email'] ?? '' ),
			'product_id'  => (int) ( $row['product_id'] ?? 0 ),
			'rating'      => (int) ( $row['rating'] ?? 0 ),
			'content'     => (string) ( $row['content'] ?? '' ),
			'status'      => (string) ( $row['status'] ?? '' ),
			'type'        => (string) ( $row['type'] ?? 'text' ),
		);
		return md5( wp_json_encode( $data ) );
	}

	/**
	 * Build hash for WooCommerce review row.
	 *
	 * @param array $row WooCommerce row.
	 * @param int   $rating Rating value.
	 * @return string
	 */
	private function hash_wc_review( array $row, $rating ) {
		$data = array(
			'comment_ID'           => (int) ( $row['comment_ID'] ?? 0 ),
			'comment_post_ID'      => (int) ( $row['comment_post_ID'] ?? 0 ),
			'user_id'              => (int) ( $row['user_id'] ?? 0 ),
			'comment_author'       => (string) ( $row['comment_author'] ?? '' ),
			'comment_author_email' => (string) ( $row['comment_author_email'] ?? '' ),
			'comment_content'      => (string) ( $row['comment_content'] ?? '' ),
			'comment_approved'     => (string) ( $row['comment_approved'] ?? '' ),
			'comment_date_gmt'     => (string) ( $row['comment_date_gmt'] ?? '' ),
			'rating'               => (int) $rating,
		);
		return md5( wp_json_encode( $data ) );
	}

	/**
	 * Map WooCommerce comment approval status to UGC status.
	 *
	 * @param string|int $status WooCommerce comment approval status.
	 * @return string
	 */
	private function map_wc_status_to_ugcify_status( $status ) {
		$status = (string) $status;
		if ( '1' === $status || 'approve' === $status ) {
			return 'approved';
		}
		if ( '0' === $status || 'hold' === $status ) {
			return 'pending';
		}
		return 'rejected';
	}

	/**
	 * Map UGC status to WooCommerce comment approval status.
	 *
	 * @param string $status UGC status.
	 * @return string
	 */
	private function map_ugcify_status_to_wc_status( $status ) {
		if ( 'approved' === $status ) {
			return '1';
		}
		return '0';
	}

	/**
	 * Resolve comment date for migrated import.
	 *
	 * @param array $row WooCommerce row.
	 * @return string
	 */
	private function resolve_comment_date( array $row ) {
		$gmt = isset( $row['comment_date_gmt'] ) ? trim( (string) $row['comment_date_gmt'] ) : '';
		if ( $gmt && '0000-00-00 00:00:00' !== $gmt ) {
			return get_date_from_gmt( $gmt );
		}

		$local = isset( $row['comment_date'] ) ? trim( (string) $row['comment_date'] ) : '';
		return $local ?: current_time( 'mysql' );
	}

	/**
	 * Resolve display name from user id.
	 *
	 * @param int $user_id User ID.
	 * @return string
	 */
	private function resolve_user_display_name( $user_id ) {
		$user = get_user_by( 'id', $user_id );
		return $user ? $user->display_name : __( 'Customer', 'ugcify' );
	}

	/**
	 * Resolve email from user id.
	 *
	 * @param int $user_id User ID.
	 * @return string
	 */
	private function resolve_user_email( $user_id ) {
		$user = get_user_by( 'id', $user_id );
		return $user ? $user->user_email : '';
	}

	/**
	 * Get currently running migration job id.
	 *
	 * @return int
	 */
	private function get_running_job_id() {
		return (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->automation_logs_table} WHERE type = %s AND status = %s ORDER BY id DESC LIMIT 1",
				self::JOB_TYPE,
				'running'
			)
		);
	}

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

		return $row ?: null;
	}

	/**
	 * Update job row.
	 *
	 * @param int    $job_id Job ID.
	 * @param string $status Status.
	 * @param array  $payload Payload.
	 * @return void
	 */
	private function update_job( $job_id, $status, array $payload ) {
		$this->wpdb->update(
			$this->automation_logs_table,
			array(
				'status'  => $status,
				'payload' => wp_json_encode( $payload ),
			),
			array( 'id' => $job_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Decode payload.
	 *
	 * @param string|null $payload Raw payload.
	 * @return array
	 */
	private function decode_payload( $payload ) {
		if ( empty( $payload ) ) {
			return array();
		}

		$decoded = json_decode( $payload, true );
		return is_array( $decoded ) ? $decoded : array();
	}

	/**
	 * Validate and normalize direction.
	 *
	 * @param string $direction Raw direction.
	 * @return string|WP_Error
	 */
	private function sanitize_direction( $direction ) {
		$direction = sanitize_text_field( (string) $direction );
		$allowed   = array( 'wc_to_ugc', 'ugcify_to_wc' );
		if ( ! in_array( $direction, $allowed, true ) ) {
			return new WP_Error(
				'invalid_migration_direction',
				__( 'Invalid migration direction.', 'ugcify' ),
				array( 'status' => 400 )
			);
		}
		return $direction;
	}
}
