<?php
/**
 * AI job repository.
 *
 * @package Ugcify\AI
 */

namespace Ugcify\AI;

use WP_Error;

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

/**
 * Class AIJobRepository
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
 */
class AIJobRepository {
	/**
	 * Table name.
	 *
	 * @var string
	 */
	private $table;

	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;
		$this->table = $wpdb->prefix . 'ugcify_ai_jobs';
	}

	/**
	 * Create or reset a job.
	 *
	 * @param array<string,mixed> $data Job data.
	 * @return array<string,mixed>|WP_Error
	 */
	public function create_or_reset( array $data ) {
		global $wpdb;

		$existing = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE media_id = %d AND image_url_hash = %s LIMIT 1",
				(int) $data['media_id'],
				(string) $data['image_url_hash']
			),
			ARRAY_A
		);

		$now = current_time( 'mysql' );

		if ( is_array( $existing ) ) {
			$updated = $wpdb->update(
				$this->table,
				array(
					'review_id'              => (int) $data['review_id'],
					'media_index'            => (int) $data['media_index'],
					'attachment_id'          => ! empty( $data['attachment_id'] ) ? (int) $data['attachment_id'] : null,
					'status'                 => 'queued',
					'attempts'               => 0,
					'locked_until'           => null,
					'provider'               => sanitize_key( (string) ( $data['provider'] ?? 'anthropic' ) ),
					'model'                  => (string) $data['model'],
					'candidate_product_ids'  => null,
					'suggestions'            => null,
					'error_code'             => null,
					'updated_at'             => $now,
				),
				array( 'id' => (int) $existing['id'] ),
				array( '%d', '%d', '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ),
				array( '%d' )
			);

			if ( false === $updated ) {
				return new WP_Error( 'db_update_error', __( 'Failed to reset AI hotspot job.', 'ugcify' ) );
			}

			return $this->find( (int) $existing['id'] );
		}

		$inserted = $wpdb->insert(
			$this->table,
			array(
				'review_id'              => (int) $data['review_id'],
				'media_id'               => (int) $data['media_id'],
				'media_index'            => (int) $data['media_index'],
				'attachment_id'          => ! empty( $data['attachment_id'] ) ? (int) $data['attachment_id'] : null,
				'image_url_hash'         => (string) $data['image_url_hash'],
				'status'                 => 'queued',
				'attempts'               => 0,
				'locked_until'           => null,
				'provider'               => sanitize_key( (string) ( $data['provider'] ?? 'anthropic' ) ),
				'model'                  => (string) $data['model'],
				'candidate_product_ids'  => null,
				'suggestions'            => null,
				'error_code'             => null,
				'created_at'             => $now,
				'updated_at'             => $now,
			),
			array( '%d', '%d', '%d', '%d', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false === $inserted ) {
			return new WP_Error( 'db_insert_error', __( 'Failed to create AI hotspot job.', 'ugcify' ) );
		}

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

	/**
	 * Find job by ID.
	 *
	 * @param int $job_id Job ID.
	 * @return array<string,mixed>|null
	 */
	public function find( int $job_id ): ?array {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE id = %d",
				$job_id
			),
			ARRAY_A
		);

		return is_array( $row ) ? $this->normalize_row( $row ) : null;
	}

	/**
	 * Claim a queued job.
	 *
	 * @param int $job_id Job ID.
	 * @return bool
	 */
	public function claim( int $job_id ): bool {
		global $wpdb;

		$updated = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$this->table}
				SET status = 'running',
					attempts = attempts + 1,
					locked_until = DATE_ADD(UTC_TIMESTAMP(), INTERVAL 10 MINUTE),
					updated_at = %s
				WHERE id = %d
					AND status IN ('queued', 'failed')
					AND (locked_until IS NULL OR locked_until < UTC_TIMESTAMP())",
				current_time( 'mysql' ),
				$job_id
			)
		);

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

	/**
	 * Store suggestions.
	 *
	 * @param int                $job_id Job ID.
	 * @param array<int,int>     $candidate_product_ids Candidate IDs.
	 * @param array<int,array<string,mixed>> $suggestions Suggestions.
	 * @return bool
	 */
	public function mark_needs_review( int $job_id, array $candidate_product_ids, array $suggestions ): bool {
		return $this->update_payload(
			$job_id,
			'needs_review',
			$candidate_product_ids,
			$suggestions,
			null
		);
	}

	/**
	 * Mark job status.
	 *
	 * @param int         $job_id Job ID.
	 * @param string      $status Status.
	 * @param string|null $error_code Error code.
	 * @return bool
	 */
	public function mark_status( int $job_id, string $status, ?string $error_code = null ): bool {
		global $wpdb;

		$updated = $wpdb->update(
			$this->table,
			array(
				'status'       => sanitize_key( $status ),
				'locked_until' => null,
				'error_code'   => $error_code ? sanitize_key( $error_code ) : null,
				'updated_at'   => current_time( 'mysql' ),
			),
			array( 'id' => $job_id ),
			array( '%s', '%s', '%s', '%s' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Update suggestions and maybe status.
	 *
	 * @param int                             $job_id Job ID.
	 * @param array<int,array<string,mixed>>  $suggestions Suggestions.
	 * @param string|null                     $status Optional status.
	 * @return bool
	 */
	public function update_suggestions( int $job_id, array $suggestions, ?string $status = null ): bool {
		global $wpdb;

		$data = array(
			'suggestions'  => wp_json_encode( array_values( $suggestions ) ),
			'updated_at'   => current_time( 'mysql' ),
		);
		$format = array( '%s', '%s' );

		if ( null !== $status ) {
			$data['status'] = sanitize_key( $status );
			$format[]       = '%s';
		}

		$updated = $wpdb->update(
			$this->table,
			$data,
			array( 'id' => $job_id ),
			$format,
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Update job payload.
	 *
	 * @param int                $job_id Job ID.
	 * @param string             $status Status.
	 * @param array<int,int>     $candidate_product_ids Candidate IDs.
	 * @param array<int,array<string,mixed>> $suggestions Suggestions.
	 * @param string|null        $error_code Error code.
	 * @return bool
	 */
	private function update_payload( int $job_id, string $status, array $candidate_product_ids, array $suggestions, ?string $error_code ): bool {
		global $wpdb;

		$updated = $wpdb->update(
			$this->table,
			array(
				'status'                => sanitize_key( $status ),
				'candidate_product_ids' => wp_json_encode( array_values( array_map( 'absint', $candidate_product_ids ) ) ),
				'suggestions'           => wp_json_encode( array_values( $suggestions ) ),
				'locked_until'          => null,
				'error_code'            => $error_code ? sanitize_key( $error_code ) : null,
				'updated_at'            => current_time( 'mysql' ),
			),
			array( 'id' => $job_id ),
			array( '%s', '%s', '%s', '%s', '%s', '%s' ),
			array( '%d' )
		);

		return false !== $updated;
	}

	/**
	 * Normalize DB row.
	 *
	 * @param array<string,mixed> $row Row.
	 * @return array<string,mixed>
	 */
	private function normalize_row( array $row ): array {
		return array(
			'id'                    => isset( $row['id'] ) ? (int) $row['id'] : 0,
			'review_id'             => isset( $row['review_id'] ) ? (int) $row['review_id'] : 0,
			'media_id'              => isset( $row['media_id'] ) ? (int) $row['media_id'] : 0,
			'media_index'           => isset( $row['media_index'] ) ? (int) $row['media_index'] : 0,
			'attachment_id'         => isset( $row['attachment_id'] ) ? (int) $row['attachment_id'] : 0,
			'image_url_hash'        => isset( $row['image_url_hash'] ) ? (string) $row['image_url_hash'] : '',
			'status'                => isset( $row['status'] ) ? (string) $row['status'] : '',
			'attempts'              => isset( $row['attempts'] ) ? (int) $row['attempts'] : 0,
			'locked_until'          => isset( $row['locked_until'] ) ? (string) $row['locked_until'] : '',
			'provider'              => isset( $row['provider'] ) ? (string) $row['provider'] : '',
			'model'                 => isset( $row['model'] ) ? (string) $row['model'] : '',
			'candidate_product_ids' => ! empty( $row['candidate_product_ids'] ) ? json_decode( (string) $row['candidate_product_ids'], true ) : array(),
			'suggestions'           => ! empty( $row['suggestions'] ) ? json_decode( (string) $row['suggestions'], true ) : array(),
			'error_code'            => isset( $row['error_code'] ) ? (string) $row['error_code'] : '',
			'created_at'            => isset( $row['created_at'] ) ? (string) $row['created_at'] : '',
			'updated_at'            => isset( $row['updated_at'] ) ? (string) $row['updated_at'] : '',
		);
	}
}
