<?php
/**
 * WordPress-backed sync-job repository.
 *
 * Production {@see SyncJobRepository} implementation that persists
 * {@see SyncJobRecord} instances into the `affilun_sync_jobs` table defined
 * in `specifications/database-schema.md` §8, routing every read and
 * write through the WordPress `$wpdb` global.
 *
 * Design notes:
 *
 *   - Writes use `$wpdb->insert()` / `$wpdb->update()` / `$wpdb->delete()`
 *     with explicit format strings rather than building raw SQL. This
 *     keeps every value correctly escaped by the driver and lets the
 *     repository stay terse enough to audit at a glance. Reads use
 *     `$wpdb->prepare()` for the variable components and
 *     `$wpdb->get_row()` / `$wpdb->get_var()` to materialize rows.
 *
 *   - The physical table name is resolved through the injected
 *     {@see TableNameResolver} (see {@see PrefixedTableNameResolver} for
 *     the production default). The resolved name is interpolated into
 *     the SQL string directly because `$wpdb->prepare()` cannot bind
 *     identifiers — the resolver is the trust boundary here, and its
 *     input comes from a class-level constant, not user data.
 *
 *   - Driver errors (`$wpdb->last_error`) are promoted to
 *     {@see RuntimeException} after every mutating call, so a write
 *     that MySQL rejected surfaces loudly at the repository boundary
 *     instead of silently returning a false-y result. Reads do the
 *     same promotion so a malformed SELECT raises rather than being
 *     silently treated as a "not found" outcome.
 *
 *   - Timestamps round-trip through {@see SyncJobRecord::toArray()} /
 *     {@see SyncJobRecord::fromArray()}, which encode UTC
 *     `DateTimeImmutable` values as the MySQL `Y-m-d H:i:s` form this
 *     table stores. Callers never see the string representation.
 *
 *   - `$wpdb` is typed as `object` rather than the concrete `\wpdb`
 *     class so this file stays trivially loadable in unit tests that
 *     run outside a WordPress environment. The repository still
 *     requires the real interface surface at call time; the abstract
 *     type hint only keeps the PSR-4 loader and the PHPCS /
 *     PHPStan analyzers happy in pure-unit mode.
 *
 * @package Affilun\Infrastructure\Persistence
 */

declare(strict_types=1);

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repository SQL uses table-name resolver output plus prepared values.

namespace Affilun\Infrastructure\Persistence;

use DateTimeImmutable;
use DateTimeZone;
use InvalidArgumentException;
use RuntimeException;

/**
 * {@see SyncJobRepository} implementation backed by WordPress's `$wpdb`.
 *
 * See the file-level docblock for the driver-call strategy, identifier-
 * interpolation boundary, and error-promotion policy.
 */
final class WpdbSyncJobRepository implements SyncJobRepository {

	/**
	 * Logical, unprefixed AFFILUN table name this repository targets.
	 *
	 * Named as a class-level constant so tests and diagnostics can
	 * reference the exact name without duplicating the string literal,
	 * and so a future rename has a single location to update.
	 */
	public const TABLE_LOGICAL_NAME = 'affilun_sync_jobs';

	/**
	 * Column names in the canonical order declared by
	 * `database-schema.md` §8.2. Used to bound the write payload and
	 * the read projection to the columns this repository owns,
	 * independent of any stray columns a host site might have added.
	 *
	 * The list excludes `id` because that column is driver-managed
	 * (auto-increment on insert, primary key on update / delete).
	 *
	 * @var list<string>
	 */
	private const COLUMNS = array(
		'job_type',
		'payload_json',
		'status',
		'priority',
		'attempts',
		'max_attempts',
		'available_at',
		'locked_at',
		'locked_by',
		'finished_at',
		'last_error_code',
		'last_error',
		'created_at',
		'updated_at',
	);

	/**
	 * Format string handed to `$wpdb->insert()` / `$wpdb->update()` so
	 * every column is bound with the correct placeholder. Keyed by the
	 * snake_case column name and restricted to the columns listed in
	 * {@see self::COLUMNS}.
	 *
	 * `%d` is used for the three integer counters from §8.2
	 * (`priority`, `attempts`, `max_attempts`) and `%s` for everything
	 * else, including the JSON payload, the `locked_by` worker
	 * identifier (which is `varchar(64)` on the schema side, not an
	 * integer), and the timestamp columns.
	 *
	 * @var array<string, string>
	 */
	private const COLUMN_FORMATS = array(
		'job_type'        => '%s',
		'payload_json'    => '%s',
		'status'          => '%s',
		'priority'        => '%d',
		'attempts'        => '%d',
		'max_attempts'    => '%d',
		'available_at'    => '%s',
		'locked_at'       => '%s',
		'locked_by'       => '%s',
		'finished_at'     => '%s',
		'last_error_code' => '%s',
		'last_error'      => '%s',
		'created_at'      => '%s',
		'updated_at'      => '%s',
	);

	/**
	 * WordPress `$wpdb`-like object used for every database call.
	 *
	 * Typed as `object` rather than `\wpdb` so this file stays loadable
	 * in unit tests that run outside a WordPress environment; see the
	 * file-level docblock for the rationale.
	 */
	private object $wpdb;

	/**
	 * Cached physical table name.
	 *
	 * Resolved once at construction time so repeated calls emit the
	 * same identifier even when the host site mutates `$wpdb->prefix`
	 * mid-request (for example via `switch_to_blog()`). Call sites
	 * that need per-request prefix lookup should construct a fresh
	 * repository after each switch, matching the stability contract
	 * on {@see PrefixedTableNameResolver}.
	 */
	private string $tableName;

	/**
	 * Construct the repository.
	 *
	 * @param object            $wpdb       WordPress `$wpdb`-like object
	 *                                      exposing `insert()`, `update()`,
	 *                                      `delete()`, `prepare()`,
	 *                                      `get_row()`, `get_var()`, the
	 *                                      `last_error` property, and the
	 *                                      `insert_id` property.
	 * @param TableNameResolver $tableNames Resolver used to derive the
	 *                                      physical `affilun_sync_jobs` name.
	 */
	public function __construct( object $wpdb, TableNameResolver $tableNames ) {
		$this->wpdb      = $wpdb;
		$this->tableName = $tableNames->resolve( self::TABLE_LOGICAL_NAME );
	}

	/**
	 * Physical table name this repository targets, resolved once at
	 * construction time through the injected {@see TableNameResolver}.
	 *
	 * Exposed primarily for tests and diagnostics; production code
	 * should not need to interpolate the name by hand.
	 */
	public function tableName(): string {
		return $this->tableName;
	}

	/**
	 * Persist the record and return its post-save form.
	 *
	 * See {@see SyncJobRepository::save()} for the full contract. This
	 * implementation:
	 *
	 *   - Treats a null id as an insert. The column payload is built
	 *     from {@see SyncJobRecord::toArray()} (minus `id`) and handed
	 *     to `$wpdb->insert()` with the format list from
	 *     {@see self::COLUMN_FORMATS}. The driver's `insert_id` is
	 *     reflected into the returned record via
	 *     {@see SyncJobRecord::withId()}.
	 *
	 *   - Treats a non-null id as an update. The implementation first
	 *     probes the id via {@see self::findById()} so the "update-
	 *     after-delete is a visible error" rule surfaces as a
	 *     descriptive {@see RuntimeException} rather than a silent
	 *     `$wpdb->update()` returning 0.
	 *
	 * Driver errors raise {@see RuntimeException} carrying
	 * `$wpdb->last_error` verbatim.
	 *
	 * @param SyncJobRecord $record Record to persist.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or when
	 *                          an update targets a missing id.
	 */
	public function save( SyncJobRecord $record ): SyncJobRecord {
		if ( $record->id() === null ) {
			return $this->insert( $record );
		}

		return $this->update( $record );
	}

	/**
	 * Insert a fresh (unpersisted) record.
	 *
	 * @param SyncJobRecord $record Record to insert; must carry a null id.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or returns
	 *                          no inserted row.
	 */
	private function insert( SyncJobRecord $record ): SyncJobRecord {
		$row     = $this->columnPayload( $record );
		$formats = $this->formatsFor( array_keys( $row ) );

		$this->clearLastError();

		/** @var false|int $result */
		$result = $this->wpdb->insert( $this->tableName, $row, $formats );

		$this->assertNoDriverError( 'insert' );

		if ( $result === false ) {
			$message = 'WpdbSyncJobRepository::save(): $wpdb->insert() returned false without raising a driver error.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$insertId = property_exists( $this->wpdb, 'insert_id' ) ? (int) $this->wpdb->insert_id : 0;
		if ( $insertId <= 0 ) {
			$message = 'WpdbSyncJobRepository::save(): $wpdb->insert() did not populate insert_id.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		return $record->withId( $insertId );
	}

	/**
	 * Overwrite an existing row with the supplied record.
	 *
	 * @param SyncJobRecord $record Record carrying the new column values;
	 *                              must carry a non-null id.
	 *
	 * @throws RuntimeException When no row currently matches the id or
	 *                          when `$wpdb` reports an error.
	 */
	private function update( SyncJobRecord $record ): SyncJobRecord {
		$id = $record->id();
		if ( $id === null ) {
			$message = 'WpdbSyncJobRepository::update(): record must carry a non-null id.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		if ( $this->findById( $id ) === null ) {
			$message = sprintf(
				'WpdbSyncJobRepository::save(): cannot update missing id %d; row may have been deleted.',
				$id
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$row     = $this->columnPayload( $record );
		$formats = $this->formatsFor( array_keys( $row ) );

		$this->clearLastError();

		/** @var false|int $result */
		$result = $this->wpdb->update(
			$this->tableName,
			$row,
			array( 'id' => $id ),
			$formats,
			array( '%d' )
		);

		$this->assertNoDriverError( 'update' );

		if ( $result === false ) {
			$message = sprintf(
				'WpdbSyncJobRepository::save(): $wpdb->update() returned false for id %d '
				. 'without raising a driver error.',
				$id
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		return $record;
	}

	/**
	 * Look up a record by its primary key.
	 *
	 * Returns null when no row matches; raises
	 * {@see InvalidArgumentException} for non-positive ids so a
	 * programming error at the call site surfaces loudly instead of
	 * silently missing.
	 *
	 * @param int $id Primary key to look up.
	 *
	 * @throws InvalidArgumentException When `$id` is non-positive.
	 *                                  Driver errors surface as RuntimeException
	 *                                  via {@see self::fetchOne()}.
	 */
	public function findById( int $id ): ?SyncJobRecord {
		if ( $id <= 0 ) {
			$message = sprintf( 'WpdbSyncJobRepository::findById() requires a positive id, got %d.', $id );
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		return $this->fetchOne(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant, not user input.
			'SELECT * FROM ' . $this->tableName . ' WHERE id = %d LIMIT 1',
			$id
		);
	}

	/**
	 * Remove the row identified by the given id.
	 *
	 * Idempotent: returns true when a row was removed, false when no
	 * row matched. Non-positive ids raise
	 * {@see InvalidArgumentException} so a programming error at the
	 * call site surfaces loudly instead of silently missing.
	 *
	 * @param int $id Primary key of the row to delete.
	 *
	 * @throws InvalidArgumentException When `$id` is non-positive.
	 * @throws RuntimeException         When `$wpdb` reports an error.
	 */
	public function delete( int $id ): bool {
		if ( $id <= 0 ) {
			$message = sprintf( 'WpdbSyncJobRepository::delete() requires a positive id, got %d.', $id );
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		$this->clearLastError();

		/** @var false|int $result */
		$result = $this->wpdb->delete(
			$this->tableName,
			array( 'id' => $id ),
			array( '%d' )
		);

		$this->assertNoDriverError( 'delete' );

		if ( $result === false ) {
			$message = sprintf(
				'WpdbSyncJobRepository::delete(): $wpdb->delete() returned false for id %d '
				. 'without raising a driver error.',
				$id
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		return (int) $result > 0;
	}

	/**
	 * Total number of rows currently stored in `affilun_sync_jobs`.
	 *
	 * Backed by a `SELECT COUNT(*)` query rather than materializing
	 * every row, matching the {@see SyncJobRepository::count()} guidance.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error.
	 */
	public function count(): int {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counting AFFILUN-owned rows; caching handled by caller where relevant.
		$value = $this->wpdb->get_var(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared -- Table name resolved from a class-level constant, not user input.
			'SELECT COUNT(*) FROM ' . $this->tableName
		);

		$this->assertNoDriverError( 'count' );

		return (int) $value;
	}

	/**
	 * Look up a paginated, filtered slice of stored rows.
	 *
	 * Implements {@see SyncJobRepository::findPaginated()} against
	 * `$wpdb`. The WHERE clause is built dynamically by appending one
	 * fragment per non-null filter on `$query`; every parameterized
	 * value is bound through `$wpdb->prepare()`. The ordering is fixed
	 * at `created_at DESC, id DESC` because §11.3 does not expose a
	 * sort parameter.
	 *
	 * Two queries are issued: a `SELECT COUNT(*)` under the same
	 * WHERE clause for the pre-pagination total, and a `SELECT *`
	 * with `LIMIT $perPage OFFSET $offset` for the page slice. Both
	 * pin the same WHERE arguments through `$wpdb->prepare()` so the
	 * total reported on the result envelope cannot disagree with the
	 * row set.
	 *
	 * @param SyncJobListQuery $query Validated list-query inputs.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or when
	 *                          a returned row cannot be decoded into
	 *                          a {@see SyncJobRecord}.
	 */
	public function findPaginated( SyncJobListQuery $query ): SyncJobListResult {
		$whereSql  = '';
		$whereArgs = array();

		if ( $query->status() !== null ) {
			$whereSql   .= ' AND status = %s';
			$whereArgs[] = $query->status();
		}

		if ( $query->jobType() !== null ) {
			$whereSql   .= ' AND job_type = %s';
			$whereArgs[] = $query->jobType();
		}

		$whereClause = $whereSql === '' ? '' : ' WHERE 1=1' . $whereSql;

		$page    = $query->page();
		$perPage = $query->perPage();
		$offset  = $query->offset();

		// Total count under the same WHERE clause, before pagination.
		$countSql = 'SELECT COUNT(*) FROM ' . $this->tableName . $whereClause;
		$this->clearLastError();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Counting AFFILUN-owned rows; caching handled by caller where relevant.
		$totalRaw = $whereArgs !== array()
			? $this->wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
				$this->wpdb->prepare( $countSql, ...$whereArgs )
			)
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Constant count query contains no user-supplied values when there are no WHERE args.
			: $this->wpdb->get_var( $countSql );
		$this->assertNoDriverError( 'count' );
		$total = (int) $totalRaw;

		// Page slice under the same WHERE clause, sorted newest-first.
		$selectSql  = 'SELECT * FROM ' . $this->tableName
			. $whereClause
			. ' ORDER BY created_at DESC, id DESC'
			. ' LIMIT %d OFFSET %d';
		$selectArgs = array_merge( $whereArgs, array( $perPage, $offset ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound via prepare().
		$items = $this->fetchMany( $selectSql, ...$selectArgs );

		return new SyncJobListResult( $items, $total, $page, $perPage );
	}

	/**
	 * Group every stored row by DB-vocabulary status and return the
	 * per-status counts.
	 *
	 * See {@see SyncJobRepository::countByStatus()} for the contract;
	 * in particular, statuses with zero occurrences are omitted so
	 * callers default-fill against
	 * {@see SyncJobListQuery::VALID_STATUSES}.
	 *
	 * Backed by a single `SELECT status, COUNT(*) ... GROUP BY status`
	 * query so the database does the grouping rather than this layer
	 * materializing every row.
	 *
	 * @return array<string, int>
	 *
	 * @throws RuntimeException When `$wpdb` reports an error.
	 */
	public function countByStatus(): array {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared -- Table name resolved from a class-level constant, not user input.
		$sql = 'SELECT status, COUNT(*) AS c FROM ' . $this->tableName . ' GROUP BY status';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Aggregating AFFILUN-owned rows; caching handled by caller where relevant.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is a constant statement built from class-level identifiers; no user input bound.
		$rows = $this->wpdb->get_results( $sql, 'ARRAY_A' );

		$this->assertNoDriverError( 'select' );

		if ( ! is_array( $rows ) ) {
			$message = 'WpdbSyncJobRepository::countByStatus(): $wpdb->get_results() returned a non-array result set.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$counts = array();
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				$message = 'WpdbSyncJobRepository::countByStatus(): $wpdb->get_results() returned a non-array row.';
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			if ( ! array_key_exists( 'status', $row ) || ! array_key_exists( 'c', $row ) ) {
				$message = 'WpdbSyncJobRepository::countByStatus(): grouped row missing "status" or "c" key.';
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			$counts[ (string) $row['status'] ] = (int) $row['c'];
		}

		return $counts;
	}

	/**
	 * Find the oldest queued/running sync_product row for a product id.
	 *
	 * @param int $productId Local product id carried in payload_json.
	 */
	public function findOpenProductSync( int $productId ): ?SyncJobRecord {
		if ( $productId <= 0 ) {
			$message = sprintf( 'WpdbSyncJobRepository::findOpenProductSync() requires a positive productId, got %d.', $productId );
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		return $this->fetchOne(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound via prepare().
			'SELECT * FROM ' . $this->tableName
			. ' WHERE job_type = %s'
			. ' AND status IN (%s, %s)'
			. ' AND JSON_UNQUOTE(JSON_EXTRACT(payload_json, %s)) = %s'
			. ' ORDER BY created_at ASC, id ASC'
			. ' LIMIT 1',
			'sync_product',
			SyncJobRecord::DEFAULT_STATUS,
			SyncJobRecord::STATUS_RUNNING,
			'$.product_id',
			(string) $productId
		);
	}

	/**
	 * Maximum number of candidate rows the §8.5 claim scan will
	 * consider before returning null.
	 *
	 * Bounds the inner conditional-UPDATE loop so a contended hot
	 * row cannot wedge the worker. Five candidates is enough to
	 * tolerate ordinary contention from the small worker pool ADR-0021
	 * §8.1 envisions (single-site WP-Cron) without scanning the whole
	 * eligible queue on every tick.
	 */
	private const CLAIM_CANDIDATE_LIMIT = 5;

	/**
	 * Atomically claim the next runnable job per
	 * {@see SyncJobRepository::claimNext()}.
	 *
	 * Strategy:
	 *
	 *   1. Select up to {@see self::CLAIM_CANDIDATE_LIMIT} candidate
	 *      ids whose row still matches the §8.5 eligibility predicate
	 *      (`pending` + `available_at <= $now`, or `running` +
	 *      `locked_at < $lockTimeoutCutoff`), ordered `priority ASC,
	 *      available_at ASC, id ASC`.
	 *
	 *   2. For each candidate, issue a conditional UPDATE that
	 *      transitions the row to `running` ONLY when the row still
	 *      satisfies the same eligibility predicate. The
	 *      `$wpdb->update()` API does not accept a multi-clause WHERE,
	 *      so the conditional UPDATE is built via `$wpdb->query()` /
	 *      `$wpdb->prepare()` directly.
	 *
	 *   3. The first candidate whose conditional UPDATE reports
	 *      `affected_rows > 0` is the winner; re-read the row through
	 *      {@see self::findById()} and return the post-claim record.
	 *
	 *   4. When every candidate is stolen by a competing worker (zero
	 *      rows affected) or no candidate exists, return null.
	 *
	 * @param string            $workerId          Lock-holder identifier.
	 * @param DateTimeImmutable $now               Current UTC instant.
	 * @param DateTimeImmutable $lockTimeoutCutoff Reclaim threshold.
	 *
	 * @throws InvalidArgumentException When `$workerId` is empty or > 64 bytes.
	 * @throws RuntimeException         When `$wpdb` reports an error.
	 */
	public function claimNext(
		string $workerId,
		DateTimeImmutable $now,
		DateTimeImmutable $lockTimeoutCutoff
	): ?SyncJobRecord {
		$length = strlen( $workerId );
		if ( $length === 0 || $length > 64 ) {
			$message = sprintf(
				'WpdbSyncJobRepository::claimNext() requires workerId of 1-64 bytes, got %d.',
				$length
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		$utc       = new DateTimeZone( 'UTC' );
		$nowSql    = $now->setTimezone( $utc )->format( SyncJobRecord::MYSQL_DATETIME_FORMAT );
		$cutoffSql = $lockTimeoutCutoff->setTimezone( $utc )->format( SyncJobRecord::MYSQL_DATETIME_FORMAT );

		$eligibility = '('
			. "(status = 'pending' AND available_at <= %s)"
			. " OR (status = 'running' AND locked_at IS NOT NULL AND locked_at < %s)"
			. ')';

		$selectSql = 'SELECT id FROM ' . $this->tableName
			. ' WHERE ' . $eligibility
			. ' ORDER BY priority ASC, available_at ASC, id ASC'
			. ' LIMIT %d';

		$this->clearLastError();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Worker primitive; results consumed immediately and never cached.
		$rows = $this->wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
			$this->wpdb->prepare( $selectSql, $nowSql, $cutoffSql, self::CLAIM_CANDIDATE_LIMIT ),
			'ARRAY_A'
		);
		$this->assertNoDriverError( 'select' );

		if ( ! is_array( $rows ) || $rows === array() ) {
			return null;
		}

		$updateSql = 'UPDATE ' . $this->tableName
			. " SET status = 'running', locked_at = %s, locked_by = %s, updated_at = %s"
			. ' WHERE id = %d AND ' . $eligibility;

		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) || ! array_key_exists( 'id', $row ) ) {
				continue;
			}
			$id = (int) $row['id'];
			if ( $id <= 0 ) {
				continue;
			}

			$this->clearLastError();
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Worker primitive; conditional UPDATE owns the §8.5 lock-acquisition transition.
			$affected = $this->wpdb->query(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
				$this->wpdb->prepare( $updateSql, $nowSql, $workerId, $nowSql, $id, $nowSql, $cutoffSql )
			);
			$this->assertNoDriverError( 'update' );

			if ( $affected === false ) {
				$message = sprintf(
					'WpdbSyncJobRepository::claimNext(): $wpdb->query() returned false for id %d without driver error.',
					$id
				);
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}

			if ( (int) $affected > 0 ) {
				$claimed = $this->findById( $id );
				if ( $claimed === null ) {
					$message = sprintf(
						'WpdbSyncJobRepository::claimNext(): claimed id %d disappeared before re-read.',
						$id
					);
					// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
					throw new RuntimeException( $message );
				}
				return $claimed;
			}
			// Otherwise a competing worker stole this candidate; advance to the next id.
		}

		return null;
	}

	/**
	 * Prepare and execute a SELECT, then decode every row into a
	 * {@see SyncJobRecord}.
	 *
	 * Centralizes the multi-row decoding boilerplate used by
	 * {@see self::findPaginated()} so the per-method body stays
	 * focused on query construction.
	 *
	 * @param string $sql  SQL containing placeholders.
	 * @param mixed  ...$args Placeholder values.
	 *
	 * @return list<SyncJobRecord>
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or when
	 *                          a returned row cannot be decoded into
	 *                          a {@see SyncJobRecord}.
	 */
	private function fetchMany( string $sql, ...$args ): array {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading AFFILUN-owned rows; caching handled by caller where relevant.
		$rows = $this->wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
			$this->wpdb->prepare( $sql, ...$args ),
			'ARRAY_A'
		);
		$this->assertNoDriverError( 'select' );

		if ( ! is_array( $rows ) ) {
			$message = 'WpdbSyncJobRepository::fetchMany(): $wpdb->get_results() returned a non-array result set.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		$items = array();
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				$message = 'WpdbSyncJobRepository::fetchMany(): $wpdb->get_results() returned a non-array row.';
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			/** @var array<string, mixed> $row */
			$items[] = SyncJobRecord::fromArray( $row );
		}

		return $items;
	}

	/**
	 * Prepare and execute a SELECT, then decode the first row into a
	 * {@see SyncJobRecord}, or return null when the query matched no row.
	 *
	 * Centralizes the error-promotion and decoding paths shared by
	 * every read method on this class so the per-method bodies stay
	 * focused on query construction.
	 *
	 * @param string $sql  SQL containing placeholders.
	 * @param mixed  ...$args Placeholder values.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or when
	 *                          the returned row cannot be decoded
	 *                          into a {@see SyncJobRecord}.
	 */
	private function fetchOne( string $sql, ...$args ): ?SyncJobRecord {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading AFFILUN-owned rows; caching handled by caller where relevant.
		// The ARRAY_A constant is defined by WordPress core; use its string
		// value directly so this repository file stays loadable in pure-unit
		// tests that do not boot WordPress.
		$row = $this->wpdb->get_row(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name resolved from a class-level constant; values bound immediately here.
			$this->wpdb->prepare( $sql, ...$args ),
			'ARRAY_A'
		);
		$this->assertNoDriverError( 'select' );

		if ( $row === null ) {
			return null;
		}

		if ( ! is_array( $row ) ) {
			$message = 'WpdbSyncJobRepository::fetchOne(): $wpdb->get_row() returned a non-array row.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new RuntimeException( $message );
		}

		/** @var array<string, mixed> $row */
		return SyncJobRecord::fromArray( $row );
	}

	/**
	 * Build the column payload handed to `$wpdb->insert()` /
	 * `$wpdb->update()`.
	 *
	 * Delegates to {@see SyncJobRecord::toArray()} for the encoded row
	 * shape (timestamps formatted as MySQL `DATETIME` strings, nullable
	 * columns preserved as `null`) and strips the `id` entry because
	 * that column is driver-managed.
	 *
	 * @param SyncJobRecord $record Record to encode.
	 *
	 * @return array<string, string|int|null>
	 */
	private function columnPayload( SyncJobRecord $record ): array {
		$row = $record->toArray();
		unset( $row['id'] );

		/** @var array<string, string|int|null> $filtered */
		$filtered = array();
		foreach ( self::COLUMNS as $column ) {
			$filtered[ $column ] = $row[ $column ] ?? null;
		}

		return $filtered;
	}

	/**
	 * Produce the `$wpdb` format list for the given column names, in
	 * the order supplied.
	 *
	 * The WordPress `$wpdb->insert()` / `$wpdb->update()` API expects
	 * the format list to line up positionally with the column payload,
	 * so this helper preserves the caller's ordering.
	 *
	 * @param list<string> $columns Column names in payload order.
	 *
	 * @return list<string> Matching format placeholders.
	 *
	 * @throws RuntimeException When a column has no registered format.
	 */
	private function formatsFor( array $columns ): array {
		$formats = array();
		foreach ( $columns as $column ) {
			if ( ! array_key_exists( $column, self::COLUMN_FORMATS ) ) {
				$message = sprintf(
					'WpdbSyncJobRepository::formatsFor(): no format registered for column "%s".',
					$column
				);
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new RuntimeException( $message );
			}
			$formats[] = self::COLUMN_FORMATS[ $column ];
		}

		return $formats;
	}

	/**
	 * Clear `$wpdb->last_error` before a call so the post-call probe
	 * only sees errors produced by the current statement.
	 */
	private function clearLastError(): void {
		if ( property_exists( $this->wpdb, 'last_error' ) ) {
			$this->wpdb->last_error = '';
		}
	}

	/**
	 * Raise a {@see RuntimeException} when `$wpdb->last_error` is
	 * populated, carrying the driver message verbatim for diagnosis.
	 *
	 * @param string $operation Short operation label used in the error
	 *                          message (for example `insert`,
	 *                          `update`, `select`).
	 *
	 * @throws RuntimeException When a driver error is detected.
	 */
	private function assertNoDriverError( string $operation ): void {
		if ( ! property_exists( $this->wpdb, 'last_error' ) ) {
			return;
		}

		$lastError = (string) $this->wpdb->last_error;
		if ( $lastError === '' ) {
			return;
		}

		$message = sprintf(
			'WpdbSyncJobRepository: $wpdb reported an error during %s: %s',
			$operation,
			$lastError
		);
		// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
		throw new RuntimeException( $message );
	}
}