<?php
/**
 * Requests stored in the plugin's own table.
 *
 * @package Fitzaro
 */

namespace Fitzaro\Job;

use Fitzaro\Database\Schema;
use Fitzaro\Database\Status;
use Fitzaro\Limits\Money;
use Fitzaro\Storage\Uuid;

defined( 'ABSPATH' ) || exit;

// Our own table: there is no WordPress API for it, and request state must never be cached.
// phpcs:disable WordPress.DB.DirectDatabaseQuery

/**
 * Each state change is one UPDATE whose WHERE clause carries the expected state (and claim token),
 * so the database itself decides which worker wins.
 */
final class WpdbRequests implements Requests {

	/**
	 * Integer columns, cast after reading because wpdb returns strings.
	 */
	private const INT_COLUMNS = array( 'user_id', 'product_id', 'variation_id', 'attempts' );

	/**
	 * Columns create() accepts.
	 */
	private const CREATE_COLUMNS = array( 'visitor_hash', 'ip_hash', 'user_id', 'product_id', 'variation_id', 'upload_path', 'provider', 'model', 'prompt_version', 'params' );

	/**
	 * Database connection.
	 *
	 * @var \wpdb
	 */
	private $db;

	/**
	 * Full table name.
	 *
	 * @var string
	 */
	private $table;

	/**
	 * Returns the current Unix time.
	 *
	 * @var callable
	 */
	private $clock;

	/**
	 * Creates the repository.
	 *
	 * @param \wpdb         $db    Database connection.
	 * @param callable|null $clock Returns the current Unix time.
	 */
	public function __construct( \wpdb $db, ?callable $clock = null ) {
		$this->db    = $db;
		$this->table = Schema::table_name( $db->prefix );
		$this->clock = $clock ?? 'time';
	}

	/**
	 * Inserts a queued request and returns its UUID.
	 *
	 * @param array<string, mixed> $fields visitor_hash, user_id, product_id, variation_id, upload_path, provider, model, prompt_version, params.
	 * @throws \RuntimeException When the row cannot be inserted.
	 */
	public function create( array $fields ): string {
		$id  = Uuid::v4();
		$row = array(
			'id'         => $id,
			'status'     => Status::QUEUED,
			'created_at' => gmdate( 'Y-m-d H:i:s', (int) ( $this->clock )() ),
		);

		foreach ( self::CREATE_COLUMNS as $column ) {
			if ( array_key_exists( $column, $fields ) ) {
				$value          = $fields[ $column ];
				$row[ $column ] = is_array( $value ) ? wp_json_encode( $value ) : $value;
			}
		}

		if ( false === $this->db->insert( $this->table, $row ) ) {
			throw new \RuntimeException( 'Could not save the try-on request.' );
		}

		return $id;
	}

	/**
	 * A request row, or null.
	 *
	 * @param string $id Request UUID.
	 * @return array<string, mixed>|null
	 */
	public function find( string $id ): ?array {
		$row = $this->db->get_row(
			$this->db->prepare( 'SELECT * FROM %i WHERE id = %s', $this->table, $id ),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return null;
		}

		foreach ( self::INT_COLUMNS as $column ) {
			$row[ $column ] = (int) $row[ $column ];
		}

		return $row;
	}

	/**
	 * Queued → running under $token. False when someone else claimed it or it is not queued.
	 *
	 * @param string $id          Request UUID.
	 * @param string $token       Claim token for this worker.
	 * @param string $lease_until Lease expiry.
	 * @param string $now         Current time.
	 */
	public function claim( string $id, string $token, string $lease_until, string $now ): bool {
		return $this->changed(
			$this->db->prepare(
				'UPDATE %i SET status = %s, claim_token = %s, lease_until = %s, started_at = COALESCE(started_at, %s) WHERE id = %s AND status = %s',
				$this->table,
				Status::RUNNING,
				$token,
				$lease_until,
				$now,
				$id,
				Status::QUEUED
			)
		);
	}

	/**
	 * Increments attempts right before the provider is called: the point after which money may be spent.
	 *
	 * @param string $id    Request UUID.
	 * @param string $token Claim token.
	 */
	public function mark_sent( string $id, string $token ): bool {
		return $this->changed(
			$this->db->prepare(
				'UPDATE %i SET attempts = attempts + 1 WHERE id = %s AND status = %s AND claim_token = %s',
				$this->table,
				$id,
				Status::RUNNING,
				$token
			)
		);
	}

	/**
	 * Records the provider's job ID for an asynchronous generation and renews the lease.
	 *
	 * @param string $id          Request UUID.
	 * @param string $token       Claim token.
	 * @param string $remote_id   Provider job ID.
	 * @param string $lease_until New lease expiry.
	 */
	public function set_pending( string $id, string $token, string $remote_id, string $lease_until ): bool {
		$updated = $this->changed(
			$this->db->prepare(
				'UPDATE %i SET remote_id = %s, lease_until = %s WHERE id = %s AND status = %s AND claim_token = %s',
				$this->table,
				$remote_id,
				$lease_until,
				$id,
				Status::RUNNING,
				$token
			)
		);

		// MySQL counts changed rows, not matched ones: writing the same values reports zero.
		return $updated || $this->owned( $id, $token );
	}

	/**
	 * Renews the lease of a running request.
	 *
	 * @param string $id          Request UUID.
	 * @param string $token       Claim token.
	 * @param string $lease_until New lease expiry.
	 */
	public function extend_lease( string $id, string $token, string $lease_until ): bool {
		$updated = $this->changed(
			$this->db->prepare(
				'UPDATE %i SET lease_until = %s WHERE id = %s AND status = %s AND claim_token = %s',
				$this->table,
				$lease_until,
				$id,
				Status::RUNNING,
				$token
			)
		);

		return $updated || $this->owned( $id, $token );
	}

	/**
	 * Running → done. False when the request was cancelled meanwhile.
	 *
	 * @param string      $id          Request UUID.
	 * @param string      $token       Claim token.
	 * @param string      $result_path Stored result file.
	 * @param string      $model       Model that produced it.
	 * @param string|null $cost        USD, or null when unknown.
	 * @param string      $now         Current time.
	 */
	public function complete( string $id, string $token, string $result_path, string $model, ?string $cost, string $now ): bool {
		return $this->changed(
			$this->db->prepare(
				"UPDATE %i SET status = %s, result_path = %s, model = %s, cost = NULLIF(%s, ''), lease_until = NULL, finished_at = %s WHERE id = %s AND status = %s AND claim_token = %s",
				$this->table,
				Status::DONE,
				$result_path,
				$model,
				(string) $cost,
				$now,
				$id,
				Status::RUNNING,
				$token
			)
		);
	}

	/**
	 * Stores what a generation cost even when its result was thrown away (the provider still charged).
	 *
	 * @param string      $id    Request UUID.
	 * @param string      $model Model.
	 * @param string|null $cost  USD, or null when unknown.
	 */
	public function record_cost( string $id, string $model, ?string $cost ): void {
		$this->db->update(
			$this->table,
			array(
				'model' => $model,
				'cost'  => $cost,
			),
			array( 'id' => $id )
		);
	}

	/**
	 * Queued or running → failed with an error code. Never overwrites a finished request.
	 *
	 * @param string $id    Request UUID.
	 * @param string $error Error code.
	 * @param string $now   Current time.
	 */
	public function fail( string $id, string $error, string $now ): bool {
		return $this->changed(
			$this->db->prepare(
				'UPDATE %i SET status = %s, error = %s, lease_until = NULL, finished_at = %s WHERE id = %s AND status IN (%s, %s)',
				$this->table,
				Status::FAILED,
				substr( $error, 0, 500 ),
				$now,
				$id,
				Status::QUEUED,
				Status::RUNNING
			)
		);
	}

	/**
	 * Running → queued, only when the provider was never called (attempts = 0).
	 *
	 * @param string $id Request UUID.
	 */
	public function requeue( string $id ): bool {
		return $this->changed(
			$this->db->prepare(
				'UPDATE %i SET status = %s, claim_token = NULL, lease_until = NULL WHERE id = %s AND status = %s AND attempts = 0',
				$this->table,
				Status::QUEUED,
				$id,
				Status::RUNNING
			)
		);
	}

	/**
	 * Number of queued and running requests of a visitor.
	 *
	 * @param string $visitor_hash Visitor.
	 */
	public function active_for_visitor( string $visitor_hash ): int {
		return (int) $this->db->get_var(
			$this->db->prepare(
				'SELECT COUNT(*) FROM %i WHERE visitor_hash = %s AND status IN (%s, %s)',
				$this->table,
				$visitor_hash,
				Status::QUEUED,
				Status::RUNNING
			)
		);
	}

	/**
	 * The shopper threw a request away: an unfinished one becomes cancelled, a finished one expired,
	 * and the result path is cleared. The caller deletes the file.
	 *
	 * @param string $id  Request UUID.
	 * @param string $now Current time.
	 */
	public function discard( string $id, string $now ): void {
		$this->db->query(
			$this->db->prepare(
				'UPDATE %i SET status = CASE WHEN status IN (%s, %s) THEN %s WHEN status = %s THEN %s ELSE status END, result_path = NULL, lease_until = NULL, finished_at = COALESCE(finished_at, %s) WHERE id = %s',
				$this->table,
				Status::QUEUED,
				Status::RUNNING,
				Status::CANCELLED,
				Status::DONE,
				Status::EXPIRED,
				$now,
				$id
			)
		);
	}

	/**
	 * Requests created at or after $since, optionally only those of one visitor or IP.
	 *
	 * @param string $since  Start of the window.
	 * @param string $column '' for all, 'visitor_hash' or 'ip_hash'.
	 * @param string $value  Hash to match.
	 */
	public function count_created_since( string $since, string $column = '', string $value = '' ): int {
		if ( 'visitor_hash' === $column || 'ip_hash' === $column ) {
			$sql = $this->db->prepare( 'SELECT COUNT(*) FROM %i WHERE %i = %s AND created_at >= %s', $this->table, $column, $value, $since );
		} else {
			$sql = $this->db->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at >= %s', $this->table, $since );
		}

		return (int) $this->db->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared above.
	}

	/**
	 * Spending of requests created at or after $since.
	 *
	 * @param string $since Start of the window.
	 * @return array{micros: int, unpriced: int}
	 */
	public function spend_since( string $since ): array {
		$row = $this->db->get_row(
			$this->db->prepare(
				'SELECT COALESCE(SUM(cost), 0) AS spent, SUM(CASE WHEN cost IS NULL AND (attempts > 0 OR status IN (%s, %s)) THEN 1 ELSE 0 END) AS unpriced FROM %i WHERE created_at >= %s',
				Status::QUEUED,
				Status::RUNNING,
				$this->table,
				$since
			),
			ARRAY_A
		);

		return array(
			'micros'   => Money::to_micros( is_array( $row ) ? (string) $row['spent'] : '0' ),
			'unpriced' => is_array( $row ) ? (int) $row['unpriced'] : 0,
		);
	}

	/**
	 * Marks done requests finished before $before as expired and clears their result path.
	 *
	 * @param string $before Cut-off.
	 * @return int Number of requests expired.
	 */
	public function expire_results( string $before ): int {
		return (int) $this->db->query(
			$this->db->prepare(
				'UPDATE %i SET status = %s, result_path = NULL WHERE status = %s AND finished_at < %s',
				$this->table,
				Status::EXPIRED,
				Status::DONE,
				$before
			)
		);
	}

	/**
	 * Deletes requests created before $before.
	 *
	 * @param string $before Cut-off.
	 * @return int Number of rows deleted.
	 */
	public function delete_created_before( string $before ): int {
		return (int) $this->db->query(
			$this->db->prepare( 'DELETE FROM %i WHERE created_at < %s', $this->table, $before )
		);
	}

	/**
	 * Requests of a logged-in user, newest first (privacy export and erasure).
	 *
	 * @param int $user_id User ID.
	 * @param int $limit   Maximum rows.
	 * @return list<array<string, mixed>>
	 */
	public function for_user( int $user_id, int $limit ): array {
		if ( $user_id <= 0 ) {
			return array();
		}

		$rows = $this->db->get_results(
			$this->db->prepare( 'SELECT * FROM %i WHERE user_id = %d ORDER BY created_at DESC LIMIT %d', $this->table, $user_id, max( 1, $limit ) ),
			ARRAY_A
		);

		$result = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			foreach ( self::INT_COLUMNS as $column ) {
				$row[ $column ] = (int) $row[ $column ];
			}
			$result[] = $row;
		}

		return $result;
	}

	/**
	 * Deletes one request row. The caller deletes its files.
	 *
	 * @param string $id Request UUID.
	 */
	public function delete( string $id ): bool {
		return 1 === (int) $this->db->delete( $this->table, array( 'id' => $id ) );
	}

	/**
	 * Queued and running requests, oldest first, for the reaper.
	 *
	 * @param int $limit Maximum rows.
	 * @return list<array<string, mixed>>
	 */
	public function unfinished( int $limit ): array {
		$rows = $this->db->get_results(
			$this->db->prepare(
				'SELECT * FROM %i WHERE status IN (%s, %s) ORDER BY created_at ASC LIMIT %d',
				$this->table,
				Status::QUEUED,
				Status::RUNNING,
				max( 1, $limit )
			),
			ARRAY_A
		);

		$result = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			foreach ( self::INT_COLUMNS as $column ) {
				$row[ $column ] = (int) $row[ $column ];
			}
			$result[] = $row;
		}

		return $result;
	}

	/**
	 * Runs an UPDATE and reports whether it changed exactly one row.
	 *
	 * @param string|null $sql Prepared statement.
	 */
	private function changed( ?string $sql ): bool {
		if ( null === $sql || '' === $sql ) {
			return false;
		}

		return 1 === $this->db->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Prepared by the caller.
	}

	/**
	 * Whether the request is running under this claim token.
	 *
	 * @param string $id    Request UUID.
	 * @param string $token Claim token.
	 */
	private function owned( string $id, string $token ): bool {
		$count = $this->db->get_var(
			$this->db->prepare(
				'SELECT COUNT(*) FROM %i WHERE id = %s AND status = %s AND claim_token = %s',
				$this->table,
				$id,
				Status::RUNNING,
				$token
			)
		);

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