<?php
/**
 * WordPress-backed product repository.
 *
 * Production {@see ProductRepository} implementation that persists
 * {@see ProductRecord} instances into the `affilun_products` table defined
 * in `specifications/database-schema.md` §4, 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 ProductRecord::toArray()} /
 *     {@see ProductRecord::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 InvalidArgumentException;
use RuntimeException;

/**
 * {@see ProductRepository} 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 WpdbProductRepository implements ProductRepository {

	/**
	 * 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_products';


	/**
	 * Column names in the canonical order declared by
	 * `database-schema.md` §4.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(
		'provider',
		'provider_item_id',
		'canonical_hash',
		'title',
		'brand',
		'description',
		'image_primary_url',
		'image_gallery_json',
		'price_amount',
		'price_currency',
		'price_label',
		'availability_status',
		'detail_url',
		'affiliate_url',
		'raw_payload_json',
		'search_keywords',
		'search_keywords_source',
		'snapshot_version',
		'sync_state',
		'lifecycle_state',
		'archived_at',
		'last_synced_at',
		'last_rendered_at',
		'last_sync_attempted_at',
		'last_sync_error_code',
		'last_sync_error_message',
		'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 integer columns (`snapshot_version`) and `%s`
	 * for everything else — including `price_amount`, which is kept as
	 * a string to preserve the `decimal(12,2)` precision that a
	 * `%f` placeholder would erode.
	 *
	 * @var array<string, string>
	 */
	private const COLUMN_FORMATS = array(
		'provider'                => '%s',
		'provider_item_id'        => '%s',
		'canonical_hash'          => '%s',
		'title'                   => '%s',
		'brand'                   => '%s',
		'description'             => '%s',
		'image_primary_url'       => '%s',
		'image_gallery_json'      => '%s',
		'price_amount'            => '%s',
		'price_currency'          => '%s',
		'price_label'             => '%s',
		'availability_status'     => '%s',
		'detail_url'              => '%s',
		'affiliate_url'           => '%s',
		'raw_payload_json'        => '%s',
		'search_keywords'         => '%s',
		'search_keywords_source'  => '%s',
		'snapshot_version'        => '%d',
		'sync_state'              => '%s',
		'lifecycle_state'         => '%s',
		'archived_at'             => '%s',
		'last_synced_at'          => '%s',
		'last_rendered_at'        => '%s',
		'last_sync_attempted_at'  => '%s',
		'last_sync_error_code'    => '%s',
		'last_sync_error_message' => '%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_products` 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 ProductRepository::save()} for the full contract. This
	 * implementation:
	 *
	 *   - Treats a null id as an insert. The column payload is built
	 *     from {@see ProductRecord::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 ProductRecord::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 ProductRecord $record Record to persist.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error, when an
	 *                          update targets a missing id, or when a
	 *                          constraint violation is reported by
	 *                          the driver.
	 */
	public function save( ProductRecord $record ): ProductRecord {
		if ( $record->id() === null ) {
			return $this->insert( $record );
		}

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

	/**
	 * Insert a fresh (unpersisted) record.
	 *
	 * @param ProductRecord $record Record to insert; must carry a null id.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or returns
	 *                          no inserted row.
	 */
	private function insert( ProductRecord $record ): ProductRecord {
		$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 = 'WpdbProductRepository::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 = 'WpdbProductRepository::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 ProductRecord $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( ProductRecord $record ): ProductRecord {
		$id = $record->id();
		if ( $id === null ) {
			$message = 'WpdbProductRepository::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(
				'WpdbProductRepository::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(
				'WpdbProductRepository::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 ): ?ProductRecord {
		if ( $id <= 0 ) {
			$message = sprintf( 'WpdbProductRepository::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
		);
	}

	/**
	 * Bulk lookup by primary key.
	 *
	 * Single round-trip implementation: deduplicate the input ids,
	 * build a sized `%d, %d, ...` placeholder list, bind every
	 * deduped id through `$wpdb->prepare()`, and run a single
	 * `SELECT ... WHERE id IN (...)` via `$wpdb->get_results()`.
	 * Result rows are decoded through {@see ProductRecord::fromArray()}
	 * and reordered to match the deduped input order via an
	 * `id => record` lookup map, so callers can zip the returned list
	 * against their requested-id sequence without an additional sort.
	 *
	 * Empty input returns an empty list without touching `$wpdb`.
	 * Missing ids are silently dropped from the result.
	 *
	 * @param list<int> $ids Positive ids to look up.
	 *
	 * @return list<ProductRecord> Found records in deduplicated input
	 *                             order; missing ids omitted.
	 *
	 * @throws InvalidArgumentException When `$ids` contains a
	 *                                  non-positive id.
	 * @throws RuntimeException         When `$wpdb` reports an error
	 *                                  or a returned row cannot be
	 *                                  decoded.
	 */
	public function findByIds( array $ids ): array {
		if ( $ids === array() ) {
			return array();
		}

		$seen    = array();
		$ordered = array();
		foreach ( $ids as $id ) {
			if ( $id <= 0 ) {
				$message = sprintf(
					'WpdbProductRepository::findByIds() requires positive ids, got %d.',
					$id
				);
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new InvalidArgumentException( $message );
			}
			if ( array_key_exists( $id, $seen ) ) {
				continue;
			}
			$seen[ $id ] = true;
			$ordered[]   = $id;
		}

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

		$sql = $this->wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table name resolved from a class-level constant; %d placeholder count is derived from the deduped id count and bound positionally below.
			'SELECT * FROM ' . $this->tableName . ' WHERE id IN (' . $placeholders . ')',
			...$ordered
		);

		$this->clearLastError();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading AFFILUN-owned rows; caching handled by caller where relevant.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Already prepared above.
		$rows = $this->wpdb->get_results( $sql, 'ARRAY_A' );
		$this->assertNoDriverError( 'select' );

		if ( ! is_array( $rows ) ) {
			$message = 'WpdbProductRepository::findByIds(): $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 );
		}

		$byId = array();
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				$message = 'WpdbProductRepository::findByIds(): $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 */
			$record = ProductRecord::fromArray( $row );
			$rid    = $record->id();
			if ( $rid === null ) {
				continue;
			}
			$byId[ $rid ] = $record;
		}

		$result = array();
		foreach ( $ordered as $id ) {
			if ( array_key_exists( $id, $byId ) ) {
				$result[] = $byId[ $id ];
			}
		}

		return $result;
	}

	/**
	 * Look up a record by the `(provider, provider_item_id)` unique
	 * constraint from §4.3.
	 *
	 * Returns null when no row matches either component; raises
	 * {@see InvalidArgumentException} when either argument is an empty
	 * string because the only way to reach that path is a programming
	 * error at the call site.
	 *
	 * @param string $provider       Provider identifier.
	 * @param string $providerItemId Provider-side item identifier.
	 *
	 * @throws InvalidArgumentException When either argument is empty.
	 *                                  Driver errors surface as RuntimeException
	 *                                  via {@see self::fetchOne()}.
	 */
	public function findByProviderItem( string $provider, string $providerItemId ): ?ProductRecord {
		if ( $provider === '' ) {
			$message = 'WpdbProductRepository::findByProviderItem() requires a non-empty provider.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		if ( $providerItemId === '' ) {
			$message = 'WpdbProductRepository::findByProviderItem() requires a non-empty providerItemId.';
			// 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 provider = %s AND provider_item_id = %s LIMIT 1',
			$provider,
			$providerItemId
		);
	}

	/**
	 * 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( 'WpdbProductRepository::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(
				'WpdbProductRepository::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_products`.
	 *
	 * Backed by a `SELECT COUNT(*)` query rather than materializing
	 * every row, matching the
	 * {@see ProductRepository::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, sorted slice of stored rows.
	 *
	 * Implements {@see ProductRepository::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 sort axis is
	 * mapped from the closed-set `sortKey` to a physical column name
	 * via {@see self::sortColumnFor()} and interpolated directly into
	 * SQL — both `sortKey` and `sortOrder` are pre-validated by
	 * {@see ProductListQuery}, so no escaping is needed at this seam.
	 *
	 * The `usedInPostId` filter is currently accepted but not honored
	 * at this layer. The lookup table that links products to posts is
	 * a Phase-9 concern (sync / usage tracking); both the in-memory
	 * and wpdb variants ignore the filter for now. The query VO
	 * carries the field through so the REST controller does not need
	 * to pre-strip it. The COUNT query mirrors the same WHERE clause,
	 * without ORDER BY / LIMIT / OFFSET, so `total_items` is reported
	 * AFTER filtering and BEFORE pagination per
	 * `specifications/rest-api-spec.md` §4.2.
	 *
	 * @param ProductListQuery $query Validated list-query inputs.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or when
	 *                          a returned row cannot be decoded into
	 *                          a {@see ProductRecord}.
	 */
	public function findPaginated( ProductListQuery $query ): ProductListResult {
		$whereSql  = '';
		$whereArgs = array();

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

		if ( $query->search() !== null ) {
			$whereSql   .= ' AND title LIKE %s';
			$whereArgs[] = '%' . $this->wpdb->esc_like( $query->search() ) . '%';
		}

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

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

		// The `usedInPostId` filter is intentionally not honored here;
		// see the method docblock — Phase-9 sync / usage tracking owns
		// the lookup table, and both repository variants currently
		// silently ignore the field.

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

		$sortColumn = $this->sortColumnFor( $query->sortKey() );
		$sortOrder  = $query->sortOrder() === ProductListQuery::SORT_ORDER_ASC ? 'ASC' : 'DESC';

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

		// 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 and WHERE skeleton resolved from class-level constants; 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' );
		$totalItems = (int) $totalRaw;

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

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and ORDER BY column resolved from validated whitelists; values bound via prepare().
		$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 and ORDER BY column resolved from validated whitelists; values bound immediately here.
			$this->wpdb->prepare( $selectSql, ...$selectArgs ),
			'ARRAY_A'
		);
		$this->assertNoDriverError( 'select' );

		if ( ! is_array( $rows ) ) {
			$message = 'WpdbProductRepository::findPaginated(): $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 = 'WpdbProductRepository::findPaginated(): $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[] = ProductRecord::fromArray( $row );
		}

		return new ProductListResult( $items, $totalItems, $page, $perPage );
	}

	/**
	 * Map a closed-set {@see ProductListQuery} sort key to its
	 * physical column name. The key is pre-validated by the query VO
	 * so the default branch is unreachable; it raises a
	 * {@see RuntimeException} purely as a defense against a future
	 * sort-key addition that forgets to update this map.
	 *
	 * @param string $sortKey Closed-set sort key.
	 *
	 * @throws RuntimeException When `$sortKey` has no registered column.
	 */
	private function sortColumnFor( string $sortKey ): string {
		switch ( $sortKey ) {
			case ProductListQuery::SORT_KEY_UPDATED_AT:
				return 'updated_at';
			case ProductListQuery::SORT_KEY_CREATED_AT:
				return 'created_at';
			case ProductListQuery::SORT_KEY_TITLE:
				return 'title';
			case ProductListQuery::SORT_KEY_LAST_SYNCED_AT:
				return 'last_synced_at';
		}

		$message = sprintf(
			'WpdbProductRepository::sortColumnFor(): no column registered for sort key "%s".',
			$sortKey
		);
		// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
		throw new RuntimeException( $message );
	}

	/**
	 * Prepare and execute a SELECT, then decode the first row into a
	 * {@see ProductRecord}, 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 ProductRecord}.
	 */
	private function fetchOne( string $sql, ...$args ): ?ProductRecord {
		$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 = 'WpdbProductRepository::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 ProductRecord::fromArray( $row );
	}

	/**
	 * Build the column payload handed to `$wpdb->insert()` /
	 * `$wpdb->update()`.
	 *
	 * Delegates to {@see ProductRecord::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 ProductRecord $record Record to encode.
	 *
	 * @return array<string, string|int|null>
	 */
	private function columnPayload( ProductRecord $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(
					'WpdbProductRepository::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(
			'WpdbProductRepository: $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 );
	}
}