<?php
namespace TeknoTok\AiTranslator\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use TeknoTok\AiTranslator\Utils\ErrorSanitizer;
use TeknoTok\AiTranslator\Utils\UrlGuard;

/**
 * Repository to interface with the background translation jobs table.
 */
class JobRepository {
	/**
	 * Flag to track if a translation job was added in the current request session.
	 *
	 * @var bool
	 */
	public static $job_added_this_request = false;

	/**
	 * Suppress automatic worker spawning while a larger queue is still being populated.
	 *
	 * @var bool
	 */
	private static $async_triggers_suspended = false;

	/**
	 * Get table name.
	 *
	 * @return string
	 */
	private static function get_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'teknotok_jobs';
	}

	/**
	 * Temporarily suspend automatic async queue triggers.
	 *
	 * @param bool $suspended Whether async triggers should be suspended.
	 * @return void
	 */
	public static function suspend_async_triggers( $suspended ) {
		self::$async_triggers_suspended = (bool) $suspended;
	}

	/**
	 * SQL LIKE patterns that identify quota/license failures.
	 *
	 * @return array
	 */
	private static function get_limit_error_patterns() {
		return array(
			'%quota%exceeded%',
			'%limit%exceeded%',
			'%word%limit%',
			'%license%',
			'%word quota%finished%',
			'%quota%finished%',
			'%quota%exhausted%',
			'%add more words%',
		);
	}

	/**
	 * Build a prepared SQL fragment for matching quota/license failures.
	 *
	 * @param string $operator LIKE or NOT LIKE.
	 * @param string $joiner   OR or AND.
	 * @return string SQL fragment with placeholders.
	 */
	private static function get_limit_error_condition_sql( $operator = 'LIKE', $joiner = 'OR' ) {
		$operator = 'NOT LIKE' === $operator ? 'NOT LIKE' : 'LIKE';
		$joiner   = 'AND' === $joiner ? 'AND' : 'OR';
		$parts    = array_fill( 0, count( self::get_limit_error_patterns() ), "error_message {$operator} %s" );

		return '(' . implode( " {$joiner} ", $parts ) . ')';
	}

	/**
	 * Add a translation job to the queue.
	 *
	 * @param string $object_type Object type ('post', 'taxonomy', 'string', etc.)
	 * @param int    $object_id   Database ID of the object.
	 * @param string $target_lang  Language code.
	 * @param string $source_hash  Optional source content hash.
	 * @return int|bool Job ID on success, false on failure.
	 */
	public static function add_job( $object_type, $object_id, $target_lang, $source_hash = '' ) {
		global $wpdb;

		$source_hash = sanitize_text_field( (string) $source_hash );
		if ( '' === $source_hash && 'post' === $object_type ) {
			$source_hash = self::get_post_source_hash( $object_id );
		}

		if ( self::has_job( $object_type, $object_id, $target_lang, $source_hash ) ) {
			return true;
		}

		$table = self::get_table_name();

		$data = array(
			'object_type' => $object_type,
			'object_id'   => $object_id,
			'source_hash' => $source_hash ?: null,
			'target_lang' => $target_lang,
			'status'      => 'pending',
			'created_at'  => current_time( 'mysql', true ),
			'updated_at'  => current_time( 'mysql', true ),
		);

		$format = array( '%s', '%d', '%s', '%s', '%s', '%s', '%s' );

		$result = $wpdb->insert( $table, $data, $format );

		if ( false !== $result ) {
			$job_id = $wpdb->insert_id;
			self::$job_added_this_request = true;
			if ( self::should_trigger_async_queue() ) {
				\TeknoTok\AiTranslator\Queue\QueueManager::trigger_async_queue();
			}
			return $job_id;
		}

		return false;
	}

	/**
	 * Build a stable hash of post fields that can affect translations.
	 *
	 * @param int $post_id Post ID.
	 * @return string Source hash, or empty string when unavailable.
	 */
	public static function get_post_source_hash( $post_id ) {
		$post = get_post( $post_id );
		if ( ! $post ) {
			return '';
		}

		$seo_keys = array(
			'_yoast_wpseo_title',
			'_yoast_wpseo_metadesc',
			'_metaseo_description',
			'rank_math_title',
			'rank_math_description',
			'rank_math_focus_keyword',
		);

		$source = array(
			'title'   => (string) $post->post_title,
			'excerpt' => (string) $post->post_excerpt,
			'content' => (string) $post->post_content,
			'meta'    => array(),
		);

		foreach ( $seo_keys as $meta_key ) {
			$value = get_post_meta( $post_id, $meta_key, true );
			if ( is_scalar( $value ) && '' !== trim( (string) $value ) ) {
				$source['meta'][ $meta_key ] = (string) $value;
			}
		}

		return md5( wp_json_encode( $source ) );
	}

	/**
	 * Add a frontend/theme string translation job without forcing synchronous shutdown processing.
	 *
	 * @param string $source_text Original string.
	 * @param string $target_lang Target language code.
	 * @return int|bool Job ID on success, true if already queued/cached, false on failure.
	 */
	public static function add_string_job( $source_text, $target_lang ) {
		global $wpdb;

		$source_text = trim( (string) $source_text );
		$target_lang = sanitize_key( $target_lang );

		if ( empty( $source_text ) || empty( $target_lang ) ) {
			return false;
		}

		if ( class_exists( '\TeknoTok\AiTranslator\Utils\HtmlParser' ) && ! \TeknoTok\AiTranslator\Utils\HtmlParser::should_translate( $source_text, $target_lang ) ) {
			return true;
		}

		if ( false !== \TeknoTok\AiTranslator\Database\MemoryRepository::get_translation( $source_text, $target_lang ) ) {
			return true;
		}

		$hash = md5( $source_text );
		if ( self::has_string_job( $hash, $target_lang ) ) {
			return true;
		}

		$table = self::get_table_name();
		$result = $wpdb->insert(
			$table,
			array(
				'object_type' => 'string',
				'object_id'   => 0,
				'source_hash' => $hash,
				'source_text' => $source_text,
				'target_lang' => $target_lang,
				'status'      => 'pending',
				'created_at'  => current_time( 'mysql', true ),
				'updated_at'  => current_time( 'mysql', true ),
			),
			array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false !== $result ) {
			self::$job_added_this_request = true;
			if ( self::should_trigger_async_queue() ) {
				\TeknoTok\AiTranslator\Queue\QueueManager::trigger_async_queue();
			}
			return $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Add a frontend URL warm-up job to discover theme/header/footer strings.
	 *
	 * @param string $source_url     Source-language URL to warm.
	 * @param string $target_lang    Target language code.
	 * @param string $content_hash   Optional content version hash for rewarming changed pages.
	 * @return int|bool Job ID on success, true if already queued/completed, false on failure.
	 */
	public static function add_frontend_warmup_job( $source_url, $target_lang, $content_hash = '' ) {
		global $wpdb;

		$source_url   = esc_url_raw( (string) $source_url );
		$target_lang  = sanitize_key( $target_lang );
		$content_hash = sanitize_text_field( (string) $content_hash );

		if ( empty( $source_url ) || empty( $target_lang ) ) {
			return false;
		}

		if ( ! UrlGuard::is_frontend_url( $source_url ) ) {
			return true;
		}

		$source_hash = md5( 'frontend_warmup|' . $source_url . '|' . $content_hash );
		if ( self::has_job( 'frontend_warmup', 0, $target_lang, $source_hash ) ) {
			return true;
		}

		$table = self::get_table_name();
		$result = $wpdb->insert(
			$table,
			array(
				'object_type' => 'frontend_warmup',
				'object_id'   => 0,
				'source_hash' => $source_hash,
				'source_text' => $source_url,
				'target_lang' => $target_lang,
				'status'      => 'pending',
				'created_at'  => current_time( 'mysql', true ),
				'updated_at'  => current_time( 'mysql', true ),
			),
			array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ( false !== $result ) {
			self::$job_added_this_request = true;
			if ( self::should_trigger_async_queue() ) {
				\TeknoTok\AiTranslator\Queue\QueueManager::trigger_async_queue();
			}
			return $wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Check whether adding a job should immediately spawn the async queue worker.
	 *
	 * @return bool True when the async worker should be triggered.
	 */
	private static function should_trigger_async_queue() {
		if ( self::$async_triggers_suspended ) {
			return false;
		}

		$warmup_param = isset( $_GET['teknotok_ait_warmup'] ) ? sanitize_key( wp_unslash( $_GET['teknotok_ait_warmup'] ) ) : '';
		if ( ! empty( $warmup_param ) ) {
			return false;
		}

		$warmup_header = isset( $_SERVER['HTTP_X_TEKNOTOK_WARMUP'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_X_TEKNOTOK_WARMUP'] ) ) : '';
		return empty( $warmup_header );
	}

	/**
	 * Check if a string translation job is already queued.
	 *
	 * @param string $source_hash Source text hash.
	 * @param string $target_lang Target language code.
	 * @return bool True if queued.
	 */
	public static function has_string_job( $source_hash, $target_lang ) {
		global $wpdb;
		$table = self::get_table_name();

		$query = $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table} WHERE object_type = 'string' AND source_hash = %s AND target_lang = %s AND status IN ('pending', 'processing')",
			$source_hash,
			$target_lang
		);

		return (int) $wpdb->get_var( $query ) > 0;
	}

	/**
	 * Check if a job is already queued.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param string $target_lang  Language.
	 * @param string $source_hash  Optional source content hash.
	 * @return bool True if queued (pending or processing), false otherwise.
	 */
	public static function has_job( $object_type, $object_id, $target_lang, $source_hash = '' ) {
		global $wpdb;
		$table = self::get_table_name();

		$source_hash = sanitize_text_field( (string) $source_hash );
		if ( '' !== $source_hash ) {
			$query = $wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE object_type = %s AND object_id = %d AND target_lang = %s AND source_hash = %s AND status IN ('pending', 'processing', 'completed')",
				$object_type,
				$object_id,
				$target_lang,
				$source_hash
			);
		} else {
			$query = $wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE object_type = %s AND object_id = %d AND target_lang = %s AND status IN ('pending', 'processing')",
				$object_type,
				$object_id,
				$target_lang
			);
		}

		return (int) $wpdb->get_var( $query ) > 0;
	}

	/**
	 * Get pending jobs.
	 *
	 * @param int $limit Max number of jobs to fetch.
	 * @return array List of pending job objects.
	 */
	public static function get_pending_jobs( $limit = 10 ) {
		global $wpdb;
		$table = self::get_table_name();
		$limit_patterns = self::get_limit_error_patterns();
		$non_limit_condition = self::get_limit_error_condition_sql( 'NOT LIKE', 'AND' );

		$now = time();
		$cutoff_1 = gmdate( 'Y-m-d H:i:s', $now - 900 ); // 15 minutes backoff
		$cutoff_2 = gmdate( 'Y-m-d H:i:s', $now - 7200 ); // 2 hours backoff

		$query = $wpdb->prepare(
			"SELECT * FROM {$table} 
			WHERE status = 'pending' 
			   OR (
			      status = 'failed' 
			      AND retry_count < 3 
			      AND (
			         error_message IS NULL
			         OR {$non_limit_condition}
			      )
			      AND (
			         (retry_count = 1 AND updated_at < %s) 
			         OR (retry_count = 2 AND updated_at < %s)
			      )
			   )
			ORDER BY CASE status WHEN 'pending' THEN 0 ELSE 1 END ASC, id ASC LIMIT %d",
			array_merge( $limit_patterns, array( $cutoff_1, $cutoff_2, $limit ) )
		);

		return $wpdb->get_results( $query );
	}

	/**
	 * Get pending jobs filtered by type (or excluding type).
	 *
	 * @param string $object_type Object type to filter.
	 * @param int    $limit       Max number of jobs to fetch.
	 * @param bool   $exclude     If true, exclude the specified object type instead of matching it.
	 * @return array List of pending job objects.
	 */
	public static function get_pending_jobs_by_type( $object_type, $limit = 10, $exclude = false ) {
		global $wpdb;
		$table = self::get_table_name();
		$limit_patterns = self::get_limit_error_patterns();
		$non_limit_condition = self::get_limit_error_condition_sql( 'NOT LIKE', 'AND' );

		$now = time();
		$cutoff_1 = gmdate( 'Y-m-d H:i:s', $now - 900 ); // 15 minutes backoff
		$cutoff_2 = gmdate( 'Y-m-d H:i:s', $now - 7200 ); // 2 hours backoff

		$type_operator = $exclude ? '!=' : '=';

		$query = $wpdb->prepare(
			"SELECT * FROM {$table} 
			WHERE (status = 'pending' 
			   OR (
			      status = 'failed' 
			      AND retry_count < 3 
			      AND (
			         error_message IS NULL
			         OR {$non_limit_condition}
			      )
			      AND (
			         (retry_count = 1 AND updated_at < %s) 
			         OR (retry_count = 2 AND updated_at < %s)
			      )
			   ))
			   AND object_type {$type_operator} %s
			ORDER BY CASE status WHEN 'pending' THEN 0 ELSE 1 END ASC, id ASC LIMIT %d",
			array_merge( $limit_patterns, array( $cutoff_1, $cutoff_2, $object_type, $limit ) )
		);

		return $wpdb->get_results( $query );
	}

	/**
	 * Count jobs of a type that have not finished, including retry-backoff failures.
	 *
	 * @param string $object_type Object type.
	 * @return int Count of unfinished jobs.
	 */
	public static function get_unfinished_job_count_by_type( $object_type ) {
		global $wpdb;
		$table = self::get_table_name();

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE object_type = %s
				AND (
					status IN ('pending', 'processing')
					OR (status = 'failed' AND retry_count < 3)
				)",
				$object_type
			)
		);
	}

	/**
	 * Count jobs of a type that are currently pending or processing.
	 *
	 * Retry-backoff failures should remain retryable, but they should not block
	 * unrelated queue work while waiting for their next retry window.
	 *
	 * @param string $object_type Object type.
	 * @return int Count of active jobs.
	 */
	public static function get_active_job_count_by_type( $object_type ) {
		global $wpdb;
		$table = self::get_table_name();

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE object_type = %s
				AND status IN ('pending', 'processing')",
				$object_type
			)
			);
	}

	/**
	 * Count active queue rows whose target language is no longer configured.
	 *
	 * @param array $active_targets Currently configured target language codes.
	 * @return int Number of active stale jobs.
	 */
	public static function get_stale_target_job_count( $active_targets ) {
		global $wpdb;
		$table = self::get_table_name();

		$active_targets = array_values( array_unique( array_filter( array_map( 'sanitize_key', (array) $active_targets ) ) ) );
		if ( empty( $active_targets ) ) {
			return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status IN ('pending', 'processing', 'failed')" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		$placeholders = implode( ',', array_fill( 0, count( $active_targets ), '%s' ) );

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE status IN ('pending', 'processing', 'failed')
				AND target_lang NOT IN ({$placeholders})",
				$active_targets
			)
		);
	}

	/**
	 * Cancel active jobs for target languages that are no longer configured.
	 *
	 * Completed jobs are preserved so existing translation memory remains useful
	 * if the language is enabled again later.
	 *
	 * @param array $active_targets Currently configured target language codes.
	 * @return int Number of canceled jobs.
	 */
	public static function cancel_jobs_for_unconfigured_targets( $active_targets ) {
		global $wpdb;
		$table = self::get_table_name();
		$now   = current_time( 'mysql', true );

		$active_targets = array_values( array_unique( array_filter( array_map( 'sanitize_key', (array) $active_targets ) ) ) );
		if ( empty( $active_targets ) ) {
			return (int) $wpdb->query(
				$wpdb->prepare(
					"UPDATE {$table}
					SET status = 'cancelled', error_message = %s, updated_at = %s
					WHERE status IN ('pending', 'processing', 'failed')",
					__( 'Cancelled because no target languages are configured.', 'teknotok-ai-translator' ),
					$now
				)
			);
		}

		$placeholders = implode( ',', array_fill( 0, count( $active_targets ), '%s' ) );

		return (int) $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'cancelled', error_message = %s, updated_at = %s
				WHERE status IN ('pending', 'processing', 'failed')
				AND target_lang NOT IN ({$placeholders})",
				array_merge(
					array(
						__( 'Cancelled because the target language was removed from settings.', 'teknotok-ai-translator' ),
						$now,
					),
					$active_targets
				)
			)
		);
	}

	/**
	 * Cancel active jobs for specific target languages.
	 *
	 * @param array $languages Target language codes.
	 * @return int Number of canceled jobs.
	 */
	public static function cancel_jobs_for_languages( $languages ) {
		global $wpdb;
		$table = self::get_table_name();

		$languages = array_values( array_unique( array_filter( array_map( 'sanitize_key', (array) $languages ) ) ) );
		if ( empty( $languages ) ) {
			return 0;
		}

		$placeholders = implode( ',', array_fill( 0, count( $languages ), '%s' ) );

		return (int) $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'cancelled', error_message = %s, updated_at = %s
				WHERE status IN ('pending', 'processing', 'failed')
				AND target_lang IN ({$placeholders})",
				array_merge(
					array(
						__( 'Cancelled because the target language was removed from settings.', 'teknotok-ai-translator' ),
						current_time( 'mysql', true ),
					),
					$languages
				)
			)
		);
	}

	/**
	 * Cancel all waiting, processing, and failed queue rows.
	 *
	 * @return int Number of canceled jobs.
	 */
	public static function cancel_waiting_jobs() {
		global $wpdb;
		$table = self::get_table_name();

		return (int) $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'cancelled', error_message = %s, updated_at = %s
				WHERE status IN ('pending', 'processing', 'failed')",
				__( 'Cancelled by administrator from Translation Status.', 'teknotok-ai-translator' ),
				current_time( 'mysql', true )
			)
		);
	}

	/**
	 * Reset jobs that have been stuck in 'processing' status for too long.
	 *
	 * @param int $timeout_seconds Timeout limit in seconds. Default is 90 (1.5 minutes).
	 * @return int Number of reset jobs.
	 */
	public static function reset_stuck_jobs( $timeout_seconds = 90 ) {
		global $wpdb;
		$table = self::get_table_name();

		$cutoff = time() - $timeout_seconds;
		$cutoff_mysql = gmdate( 'Y-m-d H:i:s', $cutoff );

		$query = $wpdb->prepare(
			"UPDATE {$table} SET status = 'failed', error_message = %s, retry_count = retry_count + 1, updated_at = %s WHERE status = 'processing' AND updated_at < %s",
			__( 'Job timed out or execution terminated unexpectedly.', 'teknotok-ai-translator' ),
			current_time( 'mysql', true ),
			$cutoff_mysql
		);

		return $wpdb->query( $query );
	}

	/**
	 * Reset jobs that failed because the gateway quota/license limit was reached.
	 *
	 * @return int Number of reset jobs.
	 */
	public static function reset_quota_failed_jobs() {
		global $wpdb;
		$table = self::get_table_name();

		$now = current_time( 'mysql', true );
		$limit_condition = self::get_limit_error_condition_sql( 'LIKE', 'OR' );

		return $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'pending', retry_count = 0, error_message = NULL, updated_at = %s
				WHERE status = 'failed'
				AND {$limit_condition}",
				array_merge( array( $now ), self::get_limit_error_patterns() )
			)
		);
	}

	/**
	 * Count jobs that failed because the gateway quota/license limit was reached.
	 *
	 * @return int Number of quota/license failed jobs.
	 */
	public static function get_quota_failed_count() {
		global $wpdb;
		$table = self::get_table_name();
		$limit_condition = self::get_limit_error_condition_sql( 'LIKE', 'OR' );

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE status = 'failed'
				AND {$limit_condition}",
				self::get_limit_error_patterns()
			)
		);
	}

	/**
	 * Reset failed frontend warmup jobs for another discovery attempt.
	 *
	 * @return int Number of reset jobs.
	 */
	public static function reset_warmup_failed_jobs() {
		global $wpdb;
		$table = self::get_table_name();

		return $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'pending', retry_count = 0, error_message = NULL, updated_at = %s
				WHERE status = 'failed'
				AND object_type = %s",
				current_time( 'mysql', true ),
				'frontend_warmup'
			)
		);
	}

	/**
	 * Reset non-quota failed jobs after server or timeout issues have been fixed.
	 *
	 * @return int Number of reset jobs.
	 */
	public static function reset_retryable_failed_jobs() {
		global $wpdb;
		$table = self::get_table_name();

		$now = current_time( 'mysql', true );
		$non_limit_condition = self::get_limit_error_condition_sql( 'NOT LIKE', 'AND' );

		return $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'pending', retry_count = 0, error_message = NULL, updated_at = %s
				WHERE status = 'failed'
				AND (
					error_message IS NULL
					OR {$non_limit_condition}
				)",
				array_merge( array( $now ), self::get_limit_error_patterns() )
			)
		);
	}

	/**
	 * Count failed jobs that are safe to retry manually.
	 *
	 * @return int Number of non-quota failed jobs.
	 */
	public static function get_retryable_failed_count() {
		global $wpdb;
		$table = self::get_table_name();
		$non_limit_condition = self::get_limit_error_condition_sql( 'NOT LIKE', 'AND' );

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table}
				WHERE status = 'failed'
				AND (
					error_message IS NULL
					OR {$non_limit_condition}
				)",
				self::get_limit_error_patterns()
			)
		);
	}

	/**
	 * Release processing jobs back to pending without counting a retry attempt.
	 *
	 * @param array $job_ids Job IDs.
	 * @return int|bool Number of rows updated, or false on failure.
	 */
	public static function release_jobs_to_pending( $job_ids ) {
		global $wpdb;
		$table = self::get_table_name();

		$job_ids = array_values( array_filter( array_map( 'absint', (array) $job_ids ) ) );
		if ( empty( $job_ids ) ) {
			return 0;
		}

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

		return $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table}
				SET status = 'pending', error_message = NULL, updated_at = %s
				WHERE id IN ({$placeholders})",
				array_merge( array( current_time( 'mysql', true ) ), $job_ids )
			)
		);
	}

	/**
	 * Update job status.
	 *
	 * @param int    $id            Job ID.
	 * @param string $status        New status ('pending', 'processing', 'completed', 'failed').
	 * @param string $error_message Optional error message for failures.
	 * @return bool True on success, false on failure.
	 */
	public static function update_status( $id, $status, $error_message = null ) {
		global $wpdb;
		$table = self::get_table_name();

		$data = array(
			'status'     => $status,
			'updated_at' => current_time( 'mysql', true ),
		);
		$format = array( '%s', '%s' );

		if ( 'failed' === $status ) {
			$data['error_message'] = ErrorSanitizer::sanitize_message( $error_message );
			$format[] = '%s';

			// Increment retry count
			$wpdb->query( $wpdb->prepare( "UPDATE {$table} SET retry_count = retry_count + 1 WHERE id = %d", $id ) );
		}

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

		return false !== $result;
	}

	/**
	 * Get total counts by status.
	 *
	 * @return array
	 */
	public static function get_counts() {
		global $wpdb;
		$table = self::get_table_name();

		$results = $wpdb->get_results(
			"SELECT status, COUNT(*) as count FROM {$table} GROUP BY status", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			OBJECT_K
		);

		$string_pending = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status IN ('pending', 'processing') AND object_type = %s", 'string' )
		);
		$heavy_pending = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status IN ('pending', 'processing') AND object_type != %s", 'string' )
		);
		$warmup_pending = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status IN ('pending', 'processing') AND object_type = %s", 'frontend_warmup' )
		);

		return array(
			'pending'    => isset( $results['pending'] ) ? (int) $results['pending']->count : 0,
			'processing' => isset( $results['processing'] ) ? (int) $results['processing']->count : 0,
			'completed'  => isset( $results['completed'] ) ? (int) $results['completed']->count : 0,
			'failed'     => isset( $results['failed'] ) ? (int) $results['failed']->count : 0,
			'string_pending' => $string_pending,
			'heavy_pending'  => $heavy_pending,
			'warmup_pending' => $warmup_pending,
		);
	}

	/**
	 * Clear all background jobs.
	 *
	 * @return void
	 */
	public static function clear_queue() {
		global $wpdb;
		$table = self::get_table_name();
		$wpdb->query( "TRUNCATE TABLE {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}
}
