<?php

declare(strict_types=1);

namespace SiteFort\Modules\Scanner\Repository;

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

/**
 * Durable task queue backing the scanner execution graph.
 */
class TaskQueueRepository {

	public const TABLE_NAME = 'sitefort_task_queue';
	public const STATUS_PENDING = 'pending';
	public const STATUS_LEASED = 'leased';
	public const STATUS_COMPLETED = 'completed';
	public const STATUS_DEAD = 'dead';

	/**
	 * WordPress database object.
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Fully qualified table name.
	 *
	 * @var string
	 */
	private string $table_name;

	public function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
		$this->table_name = $wpdb->prefix . self::TABLE_NAME;
	}

	/**
	 * Create or upgrade the durable task queue table.
	 *
	 * @return void
	 */
	public static function ensure_table(): void {
		global $wpdb;

		$table = $wpdb->prefix . self::TABLE_NAME;
		$table_identifier = self::quote_table_identifier( $table );
		$charset_collate = \SiteFort\Core\Database\SchemaInspector::validated_charset_collate( $wpdb );

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			scan_id varchar(64) NOT NULL,
			task_type varchar(40) NOT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			payload longtext DEFAULT NULL,
			result longtext DEFAULT NULL,
			available_at int(11) NOT NULL DEFAULT 0,
			lease_until int(11) NOT NULL DEFAULT 0,
			attempts tinyint unsigned NOT NULL DEFAULT 0,
			max_attempts tinyint unsigned NOT NULL DEFAULT 5,
			priority tinyint unsigned NOT NULL DEFAULT 100,
			created_at int(11) NOT NULL,
			updated_at int(11) NOT NULL,
			dedupe_key varchar(64) DEFAULT NULL,
			PRIMARY KEY (id),
			KEY idx_claim (scan_id, status, available_at, priority),
			KEY idx_scan_type (scan_id, task_type, status),
			KEY idx_lease (status, lease_until),
			UNIQUE KEY idx_dedupe (scan_id, dedupe_key)
		) {$charset_collate};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
		\SiteFort\Core\Database\SchemaInspector::flush_cache( $table );
		\SiteFort\Core\Database\SchemaInspector::ensure_table_uses_innodb( $table );

		$row = self::get_results_static_prepared( "SHOW COLUMNS FROM {$table_identifier} LIKE %s", 'dedupe_key' );
		if ( empty( $row ) ) {
			self::query_static( "ALTER TABLE {$table_identifier} ADD COLUMN dedupe_key varchar(64) DEFAULT NULL AFTER updated_at" );
		}

		$row = self::get_results_static_prepared( "SHOW COLUMNS FROM {$table_identifier} LIKE %s", 'lease_until' );
		if ( empty( $row ) ) {
			self::query_static( "ALTER TABLE {$table_identifier} ADD COLUMN lease_until int(11) NOT NULL DEFAULT 0 AFTER available_at" );
		}

		$row = self::get_results_static_prepared( "SHOW COLUMNS FROM {$table_identifier} LIKE %s", 'max_attempts' );
		if ( empty( $row ) ) {
			self::query_static( "ALTER TABLE {$table_identifier} ADD COLUMN max_attempts tinyint unsigned NOT NULL DEFAULT 5 AFTER attempts" );
		}

		$index = self::get_var_static_prepared(
			"SELECT INDEX_NAME
			FROM information_schema.statistics
			WHERE table_schema = DATABASE()
			AND table_name = %s
			AND index_name = %s
			LIMIT 1",
			$table,
			'idx_scan_type'
		);
		if ( ! $index ) {
			self::query_static( "ALTER TABLE {$table_identifier} ADD KEY idx_scan_type (scan_id, task_type, status)" );
		}

		$index = self::get_var_static_prepared(
			"SELECT INDEX_NAME
			FROM information_schema.statistics
			WHERE table_schema = DATABASE()
			AND table_name = %s
			AND index_name = %s
			LIMIT 1",
			$table,
			'idx_lease'
		);
		if ( ! $index ) {
			self::query_static( "ALTER TABLE {$table_identifier} ADD KEY idx_lease (status, lease_until)" );
		}

		$index = self::get_var_static_prepared(
			"SELECT INDEX_NAME
			FROM information_schema.statistics
			WHERE table_schema = DATABASE()
			AND table_name = %s
			AND index_name = %s
			LIMIT 1",
			$table,
			'idx_dedupe'
		);
		if ( ! $index ) {
			self::query_static( "ALTER TABLE {$table_identifier} ADD UNIQUE KEY idx_dedupe (scan_id, dedupe_key)" );
		}
	}

	/**
	 * Insert a task row.
	 *
	 * @param string      $scan_id
	 * @param string      $task_type
	 * @param array|null  $payload
	 * @param int         $available_at
	 * @param int         $priority
	 * @param string|null $dedupe_key
	 * @param int         $max_attempts
	 * @return int|false Insert ID, affected rows when dedupe updates an earlier pending task, false on failure.
	 */
	public function enqueue(
		string $scan_id,
		string $task_type,
		?array $payload = null,
		int $available_at = 0,
		int $priority = 100,
		?string $dedupe_key = null,
		int $max_attempts = 5
	) {
		$now = time();
		$payload_json = null !== $payload ? wp_json_encode( $payload ) : null;
		if ( null !== $payload && false === $payload_json ) {
			return false;
		}

		$dedupe_key = $this->normalize_dedupe_key( $dedupe_key );
		$max_attempts = max( 1, min( 20, $max_attempts ) );
		$priority = max( 0, min( 255, $priority ) );
		$available_at = max( 0, $available_at );

		if ( $dedupe_key !== null ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = $this->wpdb->prepare(
				"INSERT INTO {$this->table_name}
				(scan_id, task_type, status, payload, result, available_at, lease_until, attempts, max_attempts, priority, created_at, updated_at, dedupe_key)
				VALUES (%s, %s, %s, %s, NULL, %d, 0, 0, %d, %d, %d, %d, %s)
				ON DUPLICATE KEY UPDATE
					payload = IF(status = %s AND VALUES(available_at) < available_at, VALUES(payload), payload),
					available_at = IF(status = %s, LEAST(available_at, VALUES(available_at)), available_at),
					priority = IF(status = %s, LEAST(priority, VALUES(priority)), priority),
					max_attempts = IF(status = %s, GREATEST(max_attempts, VALUES(max_attempts)), max_attempts),
					updated_at = IF(status = %s, VALUES(updated_at), updated_at)",
				$scan_id,
				$task_type,
				self::STATUS_PENDING,
				$payload_json,
				$available_at,
				$max_attempts,
				$priority,
				$now,
				$now,
				$dedupe_key,
				self::STATUS_PENDING,
				self::STATUS_PENDING,
				self::STATUS_PENDING,
				self::STATUS_PENDING,
				self::STATUS_PENDING
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		} else {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = $this->wpdb->prepare(
				"INSERT INTO {$this->table_name}
				(scan_id, task_type, status, payload, result, available_at, lease_until, attempts, max_attempts, priority, created_at, updated_at, dedupe_key)
				VALUES (%s, %s, %s, %s, NULL, %d, 0, 0, %d, %d, %d, %d, NULL)",
				$scan_id,
				$task_type,
				self::STATUS_PENDING,
				$payload_json,
				$available_at,
				$max_attempts,
				$priority,
				$now,
				$now
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		$result = $this->query_prepared( $sql );
		if ( false === $result ) {
			return false;
		}

		if ( 0 === (int) $result ) {
			return 0;
		}

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

	/**
	 * Insert many tasks.
	 *
	 * @param array<int,array<string,mixed>> $tasks
	 * @return int Number of rows inserted.
	 */
	public function enqueue_batch( array $tasks ): int {
		$inserted = 0;

		foreach ( $tasks as $task ) {
			$result = $this->enqueue(
				(string) ( $task['scan_id'] ?? '' ),
				(string) ( $task['task_type'] ?? '' ),
				is_array( $task['payload'] ?? null ) ? $task['payload'] : [],
				(int) ( $task['available_at'] ?? time() ),
				(int) ( $task['priority'] ?? 100 ),
				isset( $task['dedupe_key'] ) ? (string) $task['dedupe_key'] : null,
				(int) ( $task['max_attempts'] ?? 5 )
			);

			if ( false === $result ) {
				continue;
			}

			if ( $result > 0 ) {
				$inserted++;
			}
		}

		return $inserted;
	}

	/**
	 * Claim one ready task for a scan with its task-specific lease already applied.
	 *
	 * @param string $scan_id
	 * @param int    $default_lease_seconds
	 * @param int    $upload_lease_seconds
	 * @param int    $scan_worker_lease_seconds
	 * @param int    $system_check_lease_seconds
	 * @return array<string,mixed>|null
	 */
	public function claim(
		string $scan_id,
		int $default_lease_seconds = 30,
		int $upload_lease_seconds = 150,
		int $scan_worker_lease_seconds = 60,
		int $system_check_lease_seconds = 120
	): ?array {
		$now = time();
		$default_lease_until = $now + max( 1, $default_lease_seconds );
		$upload_lease_until = $now + max( 1, $upload_lease_seconds );
		$scan_worker_lease_until = $now + max( 1, $scan_worker_lease_seconds );
		$system_check_lease_until = $now + max( 1, $system_check_lease_seconds );

		$updated = $this->query_prepared(
			"UPDATE {$this->table_identifier()}
			SET status = %s,
				lease_until = CASE
					WHEN task_type = %s THEN %d
					WHEN task_type = %s THEN %d
					WHEN task_type = %s THEN %d
					ELSE %d
				END,
				attempts = attempts + 1,
				updated_at = %d,
				id = LAST_INSERT_ID(id)
			WHERE scan_id = %s
			AND status = %s
			AND available_at <= %d
			ORDER BY priority ASC, id ASC
			LIMIT 1",
			self::STATUS_LEASED,
			'upload_batch',
			$upload_lease_until,
			'scan_worker',
			$scan_worker_lease_until,
			'system_check',
			$system_check_lease_until,
			$default_lease_until,
			$now,
			$scan_id,
			self::STATUS_PENDING,
			$now
		);

		if ( false === $updated || 0 === (int) $updated ) {
			return null;
		}

		$task_id = (int) $this->wpdb->get_var( 'SELECT LAST_INSERT_ID()' );
		if ( $task_id <= 0 ) {
			return null;
		}

		return $this->get_task( $task_id );
	}

	/**
	 * Acquire per-scan CAS lease.
	 *
	 * @param string $scan_id
	 * @param string $token
	 * @param int    $lease_seconds
	 * @return bool
	 */
	public function acquire_scan_lease( string $scan_id, string $token, int $lease_seconds ): bool {
		global $wpdb;
		$scan_table = $wpdb->prefix . 'sitefort_scan_queue';
		$now = time();

		$scan_table_identifier = self::quote_table_identifier( $scan_table );
		$result = $this->query_prepared(
			"UPDATE {$scan_table_identifier}
			SET task_runner_lease_token = %s,
				task_runner_lease_until = %d,
				last_ping = %d,
				updated_at = %d
			WHERE job_id = %s
			AND status IN ('pending', 'running')
			AND task_runner_lease_until <= %d",
			$token,
			$now + max( 1, $lease_seconds ),
			$now,
			$now,
			$scan_id,
			$now
		);

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

	/**
	 * Extend scan coordinator lease.
	 *
	 * @param string $scan_id
	 * @param string $token
	 * @param int    $lease_seconds
	 * @return bool
	 */
	public function extend_scan_lease( string $scan_id, string $token, int $lease_seconds ): bool {
		global $wpdb;
		$scan_table = $wpdb->prefix . 'sitefort_scan_queue';
		$now = time();

		$scan_table_identifier = self::quote_table_identifier( $scan_table );
		$result = $this->query_prepared(
			"UPDATE {$scan_table_identifier}
			SET task_runner_lease_until = %d,
				last_ping = %d,
				updated_at = %d
			WHERE job_id = %s
			AND task_runner_lease_token = %s",
			$now + max( 1, $lease_seconds ),
			$now,
			$now,
			$scan_id,
			$token
		);

		return false !== $result && (int) $result >= 0;
	}

	/**
	 * Release scan coordinator lease if still owned by token.
	 *
	 * @param string $scan_id
	 * @param string $token
	 * @return bool
	 */
	public function release_scan_lease( string $scan_id, string $token ): bool {
		global $wpdb;
		$scan_table = $wpdb->prefix . 'sitefort_scan_queue';

		$scan_table_identifier = self::quote_table_identifier( $scan_table );
		$result = $this->query_prepared(
			"UPDATE {$scan_table_identifier}
			SET task_runner_lease_token = '',
				task_runner_lease_until = 0,
				updated_at = %d
			WHERE job_id = %s
			AND task_runner_lease_token = %s",
			time(),
			$scan_id,
			$token
		);

		return false !== $result;
	}

	/**
	 * Complete task and insert follow-up tasks atomically.
	 *
	 * @param int                    $task_id
	 * @param array<string,mixed>|null $result
	 * @param array<int,array<string,mixed>> $follow_ups
	 * @param int|null $claimed_attempts
	 * @return bool
	 */
	public function complete_and_enqueue( int $task_id, ?array $result, array $follow_ups, ?int $claimed_attempts = null ): bool {
		$task = $this->get_task( $task_id );
		if ( null === $task ) {
			return false;
		}
		if ( self::STATUS_LEASED !== (string) ( $task['status'] ?? '' ) ) {
			return false;
		}
		if ( null !== $claimed_attempts && (int) ( $task['attempts'] ?? 0 ) !== $claimed_attempts ) {
			return false;
		}

		$result_json = null !== $result ? wp_json_encode( $result ) : null;
		if ( null !== $result && false === $result_json ) {
			return false;
		}

		$now = time();

		try {
			$this->wpdb->query( 'START TRANSACTION' );

			$where = [
				'id'     => $task_id,
				'status' => self::STATUS_LEASED,
			];
			$where_formats = [ '%d', '%s' ];
			if ( null !== $claimed_attempts ) {
				$where['attempts'] = $claimed_attempts;
				$where_formats[] = '%d';
			}

			$updated = $this->wpdb->update(
				$this->table_name,
				[
					'status'      => self::STATUS_COMPLETED,
					'result'      => $result_json,
					'lease_until' => 0,
					'updated_at'  => $now,
					'dedupe_key'  => null,
				],
				$where,
				[ '%s', '%s', '%d', '%d', '%s' ],
				$where_formats
			);

			if ( false === $updated || 1 !== (int) $updated ) {
				throw new \RuntimeException( 'Failed to complete task.' );
			}

			foreach ( $follow_ups as $follow_up ) {
				$scan_id = (string) ( $follow_up['scan_id'] ?? $task['scan_id'] );
				$task_type = (string) ( $follow_up['task_type'] ?? '' );

				if ( '' === $scan_id || '' === $task_type ) {
					continue;
				}

				$insert_result = $this->insert_follow_up_task( $scan_id, $task_type, $follow_up, $now );
				if ( false === $insert_result ) {
					throw new \RuntimeException( 'Failed to enqueue follow-up task.' );
				}
			}

			$this->wpdb->query( 'COMMIT' );
			return true;
		} catch ( \Throwable $e ) {
			$this->wpdb->query( 'ROLLBACK' );
			return false;
		}
	}

	/**
	 * Requeue or dead-letter a failed task.
	 *
	 * @param int    $task_id
	 * @param string $error
	 * @param int|null $claimed_attempts
	 * @return bool
	 */
	public function fail( int $task_id, string $error, ?int $claimed_attempts = null ): bool {
		$task = $this->get_task( $task_id );
		if ( null === $task ) {
			return false;
		}
		if ( self::STATUS_LEASED !== (string) ( $task['status'] ?? '' ) ) {
			return false;
		}
		if ( null !== $claimed_attempts && (int) ( $task['attempts'] ?? 0 ) !== $claimed_attempts ) {
			return false;
		}

		$attempts = (int) ( $task['attempts'] ?? 0 );
		$max_attempts = max( 1, (int) ( $task['max_attempts'] ?? 5 ) );
		$now = time();
		$payload = is_array( $task['payload'] ?? null ) ? $task['payload'] : [];
		$task_type = sanitize_key( (string) ( $task['task_type'] ?? '' ) );
		$failure_type = $this->classify_failure_type( $task_type, $error );
		$error_result = [
			'error'          => sanitize_text_field( $error ),
			'attempts'       => $attempts,
			'failed_at'      => $now,
			'task_type'      => $task_type,
			'failure_origin' => $this->classify_failure_origin( $task_type, $error, $failure_type ),
			'failure_type'   => $failure_type,
		];
		$where = [
			'id'       => $task_id,
			'status'   => self::STATUS_LEASED,
			'attempts' => $attempts,
		];
		$where_formats = [ '%d', '%s', '%d' ];

		if ( $attempts >= $max_attempts ) {
			$update_data = [
				'status'      => self::STATUS_DEAD,
				'result'      => wp_json_encode( $error_result ),
				'lease_until' => 0,
				'updated_at'  => $now,
			];
			$update_formats = [ '%s', '%s', '%d', '%d' ];
			if ( in_array( (string) ( $task['task_type'] ?? '' ), [ 'poll_cloud', 'upload_batch' ], true ) ) {
				$update_data['dedupe_key'] = null;
				$update_formats[] = '%s';
			}

			$result = $this->wpdb->update(
				$this->table_name,
				$update_data,
				$where,
				$update_formats,
				$where_formats
			);

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

		$payload['last_error'] = sanitize_text_field( $error );
		$payload['last_failed_at'] = $now;

		$result = $this->wpdb->update(
			$this->table_name,
			[
				'status'       => self::STATUS_PENDING,
				'payload'      => wp_json_encode( $payload ),
				'result'       => wp_json_encode( $error_result ),
				'available_at' => $now + $this->get_backoff_delay( $attempts ),
				'lease_until'  => 0,
				'updated_at'   => $now,
			],
			$where,
			[ '%s', '%s', '%s', '%d', '%d', '%d' ],
			$where_formats
		);

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

	/**
	 * Requeue expired leased tasks and clear expired scan-row leases.
	 *
	 * @param string $scan_id Optional scan scope.
	 * @return int Number of task rows changed.
	 */
	public function release_expired_leases( string $scan_id = '' ): int {
		global $wpdb;
		$scan_table = $wpdb->prefix . 'sitefort_scan_queue';
		$now = time();
		$scope_sql = '';

		if ( '' !== $scan_id ) {
			$scope_sql = $this->prepare_sql( ' AND scan_id = %s', $scan_id );
		}

		$expired_sql = sprintf(
			"SELECT id, task_type, attempts, max_attempts
			FROM %s
			WHERE status = '%s'
			AND lease_until > 0
			AND lease_until <= %d%s",
			$this->table_identifier(),
			esc_sql( self::STATUS_LEASED ),
			$now,
			$scope_sql
		);
		$expired = $this->get_results_query( $expired_sql );

		$released = 0;

		foreach ( $expired as $task ) {
			$attempts = (int) ( $task['attempts'] ?? 0 );
			$max_attempts = max( 1, (int) ( $task['max_attempts'] ?? 5 ) );
			$status = $attempts >= $max_attempts ? self::STATUS_DEAD : self::STATUS_PENDING;
			$available_at = self::STATUS_PENDING === $status ? ( $now + $this->get_backoff_delay( $attempts ) ) : 0;
			$task_type = sanitize_key( (string) ( $task['task_type'] ?? '' ) );
			$failure_type = '' !== $task_type ? $task_type . '_lease_expired' : 'task_lease_expired';
			if ( 'scan_worker' === $task_type ) {
				$failure_type = 'worker_lease_expired';
			}
			$update_data = [
				'status'       => $status,
				'available_at' => $available_at,
				'lease_until'  => 0,
				'updated_at'   => $now,
			];
			$update_formats = [ '%s', '%d', '%d', '%d' ];

			if ( self::STATUS_DEAD === $status ) {
				$dead_result = wp_json_encode(
					[
						'error'          => sanitize_text_field( sprintf( '%s task lease expired before completion', $task_type ?: 'Background' ) ),
						'attempts'       => $attempts,
						'failed_at'      => $now,
						'task_type'      => $task_type,
						'failure_origin' => $this->classify_failure_origin( $task_type, '', $failure_type ),
						'failure_type'   => $failure_type,
					]
				);
				$update_data['result'] = false !== $dead_result ? $dead_result : '{}';
				$update_formats[] = '%s';
			}

			if ( self::STATUS_DEAD === $status && in_array( $task_type, [ 'poll_cloud', 'upload_batch' ], true ) ) {
				$update_data['dedupe_key'] = null;
				$update_formats[] = '%s';
			}

			$result = $this->wpdb->update(
				$this->table_name,
				$update_data,
				[
					'id'       => (int) $task['id'],
					'status'   => self::STATUS_LEASED,
					'attempts' => $attempts,
				],
				$update_formats,
				[ '%d', '%s', '%d' ]
			);

			if ( false !== $result ) {
				$released += (int) $result;
			}
		}

		if ( '' !== $scan_id ) {
			$scan_table_identifier = self::quote_table_identifier( $scan_table );
			$this->query_prepared(
				"UPDATE {$scan_table_identifier}
				SET task_runner_lease_token = '',
					task_runner_lease_until = 0
				WHERE job_id = %s
				AND task_runner_lease_until > 0
				AND task_runner_lease_until <= %d",
				$scan_id,
				$now
			);
		} else {
			$scan_table_identifier = self::quote_table_identifier( $scan_table );
			$this->query_prepared(
				"UPDATE {$scan_table_identifier}
				SET task_runner_lease_token = '',
					task_runner_lease_until = 0
				WHERE task_runner_lease_until > 0
				AND task_runner_lease_until <= %d",
				$now
			);
		}

		return $released;
	}

	/**
	 * Count tasks by status for a scan.
	 *
	 * @param string $scan_id
	 * @return array<string,int>
	 */
	public function count_by_status( string $scan_id ): array {
		$results = $this->get_results_prepared(
			"SELECT status, COUNT(*) AS count
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			GROUP BY status",
			$scan_id
		);

		$counts = [];
		foreach ( $results as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['count'];
		}

		return $counts;
	}

	/**
	 * Determine whether any work still exists for this scan.
	 *
	 * @param string $scan_id
	 * @return bool
	 */
	public function has_pending_work( string $scan_id ): bool {
		$count = (int) $this->get_var_prepared(
			"SELECT COUNT(*)
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status IN (%s, %s)",
			$scan_id,
			self::STATUS_PENDING,
			self::STATUS_LEASED
		);

		return $count > 0;
	}

	/**
	 * Determine whether immediately runnable work exists.
	 *
	 * @param string $scan_id
	 * @return bool
	 */
	public function has_available_work( string $scan_id ): bool {
		$now = time();
		$count = (int) $this->get_var_prepared(
			"SELECT COUNT(*)
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status = %s
			AND available_at <= %d",
			$scan_id,
			self::STATUS_PENDING,
			$now
		);

		return $count > 0;
	}

	public function get_next_ready_task_type( string $scan_id ): string {
		$now = time();
		$task_type = $this->get_var_prepared(
			"SELECT task_type
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status = %s
			AND available_at <= %d
			ORDER BY priority ASC, id ASC
			LIMIT 1",
			$scan_id,
			self::STATUS_PENDING,
			$now
		);

		return is_string( $task_type ) ? sanitize_key( $task_type ) : '';
	}

	/**
	 * Find earliest scheduled pending task.
	 *
	 * @param string $scan_id
	 * @return int
	 */
	public function get_next_available_at( string $scan_id ): int {
		$value = $this->get_var_prepared(
			"SELECT MIN(available_at)
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status = %s",
			$scan_id,
			self::STATUS_PENDING
		);

		return max( 0, (int) $value );
	}

	/**
	 * Check for an existing task.
	 *
	 * @param string      $scan_id
	 * @param string      $task_type
	 * @param string[]    $statuses
	 * @param string|null $dedupe_key
	 * @return bool
	 */
	public function has_task( string $scan_id, string $task_type, array $statuses, ?string $dedupe_key = null ): bool {
		if ( empty( $statuses ) ) {
			return false;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
		$params = [ $scan_id, $task_type ];
		$where = "scan_id = %s AND task_type = %s AND status IN ({$placeholders})";
		$params = array_merge( $params, array_values( $statuses ) );

		$dedupe_key = $this->normalize_dedupe_key( $dedupe_key );
		if ( null !== $dedupe_key ) {
			$where .= ' AND dedupe_key = %s';
			$params[] = $dedupe_key;
		}

		$count = (int) $this->get_var_prepared(
			"SELECT COUNT(*) FROM {$this->table_identifier()} WHERE {$where}",
			...$params
		);

		return $count > 0;
	}

	/**
	 * Distinct dedupe keys present for a task type in the given statuses.
	 *
	 * @param string   $scan_id
	 * @param string   $task_type
	 * @param string[] $statuses
	 * @return array<string,bool> Set keyed by dedupe_key.
	 */
	public function get_present_dedupe_keys( string $scan_id, string $task_type, array $statuses ): array {
		if ( empty( $statuses ) ) {
			return [];
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
		$params = array_merge( [ $scan_id, $task_type ], array_values( $statuses ) );

		$rows = $this->get_results_prepared(
			"SELECT DISTINCT dedupe_key FROM {$this->table_identifier()}
			WHERE scan_id = %s AND task_type = %s AND status IN ({$placeholders})
			AND dedupe_key IS NOT NULL",
			...$params
		);

		$keys = [];
		foreach ( $rows as $row ) {
			$key = (string) ( $row['dedupe_key'] ?? '' );
			if ( '' !== $key ) {
				$keys[ $key ] = true;
			}
		}

		return $keys;
	}

	/**
	 * Count tasks matching the given scan, type, statuses, and optional dedupe key.
	 *
	 * @param string      $scan_id
	 * @param string      $task_type
	 * @param string[]    $statuses
	 * @param string|null $dedupe_key
	 * @return int
	 */
	public function count_tasks( string $scan_id, string $task_type, array $statuses, ?string $dedupe_key = null ): int {
		if ( empty( $statuses ) ) {
			return 0;
		}

		$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
		$params = [ $scan_id, $task_type ];
		$where = "scan_id = %s AND task_type = %s AND status IN ({$placeholders})";
		$params = array_merge( $params, array_values( $statuses ) );

		$dedupe_key = $this->normalize_dedupe_key( $dedupe_key );
		if ( null !== $dedupe_key ) {
			$where .= ' AND dedupe_key = %s';
			$params[] = $dedupe_key;
		}

		return (int) $this->get_var_prepared(
			"SELECT COUNT(*) FROM {$this->table_identifier()} WHERE {$where}",
			...$params
		);
	}

	/**
	 * Delete all task rows for a scan.
	 *
	 * @param string $scan_id
	 * @return bool
	 */
	public function delete_by_scan_id( string $scan_id ): bool {
		$result = $this->wpdb->delete(
			$this->table_name,
			[ 'scan_id' => $scan_id ],
			[ '%s' ]
		);

		return false !== $result;
	}

	/**
	 * Get poison/dead tasks for reporting.
	 *
	 * @param string $scan_id
	 * @return array<int,array<string,mixed>>
	 */
	public function get_dead_tasks( string $scan_id ): array {
		$results = $this->get_results_prepared(
			"SELECT *
			FROM {$this->table_identifier()}
			WHERE scan_id = %s
			AND status = %s
			ORDER BY id ASC",
			$scan_id,
			self::STATUS_DEAD
		);

		return is_array( $results ) ? array_map( [ $this, 'hydrate_task_row' ], $results ) : [];
	}

	/**
	 * Load one task row by ID.
	 *
	 * @param int $task_id
	 * @return array<string,mixed>|null
	 */
	public function get_task( int $task_id ): ?array {
		$row = $this->get_row_prepared(
			"SELECT *
			FROM {$this->table_identifier()}
			WHERE id = %d",
			$task_id
		);

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

	/**
	 * Normalize task row types.
	 *
	 * @param array<string,mixed> $row
	 * @return array<string,mixed>
	 */
	private function hydrate_task_row( array $row ): array {
		$payload = [];
		if ( ! empty( $row['payload'] ) && is_string( $row['payload'] ) ) {
			$decoded = json_decode( $row['payload'], true );
			if ( is_array( $decoded ) ) {
				$payload = $decoded;
			}
		}

		$result = [];
		if ( ! empty( $row['result'] ) && is_string( $row['result'] ) ) {
			$decoded = json_decode( $row['result'], true );
			if ( is_array( $decoded ) ) {
				$result = $decoded;
			}
		}

		$row['id'] = (int) ( $row['id'] ?? 0 );
		$row['available_at'] = (int) ( $row['available_at'] ?? 0 );
		$row['lease_until'] = (int) ( $row['lease_until'] ?? 0 );
		$row['attempts'] = (int) ( $row['attempts'] ?? 0 );
		$row['max_attempts'] = (int) ( $row['max_attempts'] ?? 0 );
		$row['priority'] = (int) ( $row['priority'] ?? 100 );
		$row['created_at'] = (int) ( $row['created_at'] ?? 0 );
		$row['updated_at'] = (int) ( $row['updated_at'] ?? 0 );
		$row['payload'] = $payload;
		$row['result'] = $result;
		$row['dedupe_key'] = isset( $row['dedupe_key'] ) && null !== $row['dedupe_key']
			? (string) $row['dedupe_key']
			: null;

		return $row;
	}

	/**
	 * Insert a follow-up task inside the current DB transaction.
	 *
	 * @param string                $scan_id
	 * @param string                $task_type
	 * @param array<string,mixed>   $task
	 * @param int                   $now
	 * @return int|false
	 */
	private function insert_follow_up_task( string $scan_id, string $task_type, array $task, int $now ) {
		$payload = is_array( $task['payload'] ?? null ) ? $task['payload'] : [];
		$payload_json = wp_json_encode( $payload );
		if ( false === $payload_json ) {
			return false;
		}

		$dedupe_key = $this->normalize_dedupe_key(
			isset( $task['dedupe_key'] ) ? (string) $task['dedupe_key'] : null
		);
		$priority = max( 0, min( 255, (int) ( $task['priority'] ?? 100 ) ) );
		$available_at = max( 0, (int) ( $task['available_at'] ?? $now ) );
		$max_attempts = max( 1, min( 20, (int) ( $task['max_attempts'] ?? 5 ) ) );

		if ( null !== $dedupe_key ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = $this->wpdb->prepare(
				"INSERT INTO {$this->table_name}
				(scan_id, task_type, status, payload, result, available_at, lease_until, attempts, max_attempts, priority, created_at, updated_at, dedupe_key)
				VALUES (%s, %s, %s, %s, NULL, %d, 0, 0, %d, %d, %d, %d, %s)
				ON DUPLICATE KEY UPDATE
					payload = IF(status = %s AND VALUES(available_at) < available_at, VALUES(payload), payload),
					available_at = IF(status = %s, LEAST(available_at, VALUES(available_at)), available_at),
					priority = IF(status = %s, LEAST(priority, VALUES(priority)), priority),
					max_attempts = IF(status = %s, GREATEST(max_attempts, VALUES(max_attempts)), max_attempts),
					updated_at = IF(status = %s, VALUES(updated_at), updated_at)",
				$scan_id,
				$task_type,
				self::STATUS_PENDING,
				$payload_json,
				$available_at,
				$max_attempts,
				$priority,
				$now,
				$now,
				$dedupe_key,
				self::STATUS_PENDING,
				self::STATUS_PENDING,
				self::STATUS_PENDING,
				self::STATUS_PENDING,
				self::STATUS_PENDING
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		} else {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$sql = $this->wpdb->prepare(
				"INSERT INTO {$this->table_name}
				(scan_id, task_type, status, payload, result, available_at, lease_until, attempts, max_attempts, priority, created_at, updated_at, dedupe_key)
				VALUES (%s, %s, %s, %s, NULL, %d, 0, 0, %d, %d, %d, %d, NULL)",
				$scan_id,
				$task_type,
				self::STATUS_PENDING,
				$payload_json,
				$available_at,
				$max_attempts,
				$priority,
				$now,
				$now
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		$result = $this->query_prepared( $sql );
		return false === $result ? false : (int) $result;
	}

	/**
	 * Compute retry backoff.
	 *
	 * @param int $attempts
	 * @return int
	 */
	private function get_backoff_delay( int $attempts ): int {
		$attempts = max( 1, $attempts );
		return min( 300, 5 * ( 2 ** max( 0, $attempts - 1 ) ) );
	}

	private function classify_failure_origin( string $task_type, string $error = '', string $failure_type = '' ): string {
		if (
			false !== strpos( $failure_type, 'lease_expired' )
			|| false !== stripos( $error, 'lease' )
			|| false !== stripos( $error, 'task completion transaction failed' )
			|| false !== stripos( $error, 'unable to extend scan lease' )
		) {
			return 'local';
		}

		if ( 'poll_cloud' === $task_type ) {
			return 'cloud';
		}

		return 'local';
	}

	private function classify_failure_type( string $task_type, string $error ): string {
		if ( 'scan_worker' === $task_type && false !== stripos( $error, 'lease expired' ) ) {
			return 'worker_lease_expired';
		}

		if ( 'scan_worker' === $task_type ) {
			return 'worker_task_failed';
		}

		if ( 'upload_batch' === $task_type ) {
			return false !== stripos( $error, 'lease' ) ? 'upload_batch_lease_expired' : 'upload_batch_task_failed';
		}

		if ( 'poll_cloud' === $task_type ) {
			return false !== stripos( $error, 'lease' ) ? 'poll_cloud_lease_expired' : 'cloud_poll_failed';
		}

		return 'task_failed';
	}

	/**
	 * Normalize dedupe key to DB-safe length.
	 *
	 * @param string|null $dedupe_key
	 * @return string|null
	 */
	private function normalize_dedupe_key( ?string $dedupe_key ): ?string {
		if ( null === $dedupe_key ) {
			return null;
		}

		$dedupe_key = sanitize_text_field( $dedupe_key );
		if ( '' === $dedupe_key ) {
			return null;
		}

		if ( strlen( $dedupe_key ) <= 64 ) {
			return $dedupe_key;
		}

		return hash( 'sha256', $dedupe_key );
	}

	private function table_identifier(): string {
		return self::quote_table_identifier( $this->table_name );
	}

	private static function quote_table_identifier( string $table ): string {
		if ( $table === '' || strlen( $table ) > 64 || ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
			throw new \InvalidArgumentException( 'Invalid table identifier.' );
		}

		return '`' . str_replace( '`', '``', $table ) . '`';
	}

	private function get_results_query( string $sql ): array {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repository query.
			$results = $this->wpdb->get_results( $sql, ARRAY_A );
		return is_array( $results ) ? $results : [];
	}

	private function get_results_prepared( string $sql, ...$params ): array {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return [];
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			$results = $this->wpdb->get_results( $prepared, ARRAY_A );
		return is_array( $results ) ? $results : [];
	}

	private function get_row_prepared( string $sql, ...$params ): ?array {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			$result = $this->wpdb->get_row( $prepared, ARRAY_A );
		return is_array( $result ) ? $result : null;
	}

	private function get_var_prepared( string $sql, ...$params ) {
		$prepared = $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			return $this->wpdb->get_var( $prepared );
	}

	private function query_prepared( string $sql, ...$params ) {
		$prepared = empty( $params ) ? $sql : $this->prepare_sql( $sql, ...$params );
		if ( '' === $prepared ) {
			return false;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared repository query.
			return $this->wpdb->query( $prepared );
	}

	private function prepare_sql( string $sql, ...$params ): string {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
			$prepared = $this->wpdb->prepare( $sql, ...$params );
		return is_string( $prepared ) ? $prepared : '';
	}

	private static function get_results_static_prepared( string $sql, ...$params ): array {
		global $wpdb;
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
			$prepared = $wpdb->prepare( $sql, ...$params );
		if ( ! is_string( $prepared ) ) {
			return [];
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema query.
			$results = $wpdb->get_results( $prepared, ARRAY_A );
		return is_array( $results ) ? $results : [];
	}

	private static function get_var_static_prepared( string $sql, ...$params ) {
		global $wpdb;
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Bound with wpdb::prepare().
			$prepared = $wpdb->prepare( $sql, ...$params );
		if ( ! is_string( $prepared ) ) {
			return null;
		}

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema query.
			return $wpdb->get_var( $prepared );
	}

	private static function query_static( string $sql ) {
		global $wpdb;
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema query.
			return $wpdb->query( $sql );
	}
}
