<?php
/**
 * WordPress-backed product-meta repository.
 *
 * Production {@see ProductMetaRepository} implementation that persists
 * {@see ProductMetaRecord} instances into the `clb_product_meta` table
 * defined in `specifications/database-schema.md` §5, 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 — including a violation of the
 *     `(product_id, meta_key)` unique constraint from §5.3 — 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 ProductMetaRecord::toArray()} /
 *     {@see ProductMetaRecord::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 CommerceLinkBuilder\Infrastructure\Persistence
 */

declare(strict_types=1);

// The `meta_key` / `meta_value` column names on `clb_product_meta` are
// part of CLB's own schema (§5.2), not WordPress's `postmeta` table.
// WPCS's SlowDBQuery sniff cannot tell the two apart, so suppress it
// at the file level rather than peppering every reference with a
// per-line `phpcs:ignore` comment.
//
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_value

namespace CommerceLinkBuilder\Infrastructure\Persistence;

use InvalidArgumentException;
use RuntimeException;

/**
 * {@see ProductMetaRepository} 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 WpdbProductMetaRepository implements ProductMetaRepository {

	/**
	 * Logical, unprefixed CLB 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 = 'clb_product_meta';

	/**
	 * Column names in the canonical order declared by
	 * `database-schema.md` §5.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(
		'product_id',
		'meta_key',
		'meta_value',
		'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 `bigint unsigned` foreign-key-style
	 * `product_id` column and `%s` for everything else, including the
	 * `longtext` `meta_value` blob and the timestamp columns.
	 *
	 * @var array<string, string>
	 */
	private const COLUMN_FORMATS = array(
		'product_id' => '%d',
		'meta_key'   => '%s',
		'meta_value' => '%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 `clb_product_meta`
	 *                                      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 ProductMetaRepository::save()} for the full contract.
	 * This implementation:
	 *
	 *   - Treats a null id as an insert. The column payload is built
	 *     from {@see ProductMetaRecord::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 ProductMetaRecord::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. The
	 * `(product_id, meta_key)` unique constraint from §5.3 is enforced
	 * by the database itself; a violation surfaces here as a driver
	 * error and is promoted accordingly.
	 *
	 * @param ProductMetaRecord $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( ProductMetaRecord $record ): ProductMetaRecord {
		if ( $record->id() === null ) {
			return $this->insert( $record );
		}

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

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

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

		return $this->fetchOne( $sql );
	}

	/**
	 * Look up a record by its `(product_id, meta_key)` composite
	 * unique key from §5.3.
	 *
	 * Returns null when no row matches; raises
	 * {@see InvalidArgumentException} when the product id is
	 * non-positive or the meta key is empty because the only way to
	 * reach those paths is a programming error at the call site.
	 *
	 * @param int    $productId Owning `clb_products.id` reference.
	 * @param string $metaKey   Meta key.
	 *
	 * @throws InvalidArgumentException When `$productId` is
	 *                                  non-positive or `$metaKey` is
	 *                                  the empty string. Driver errors
	 *                                  surface as RuntimeException
	 *                                  via {@see self::fetchOne()}.
	 */
	public function findByProductAndKey( int $productId, string $metaKey ): ?ProductMetaRecord {
		if ( $productId <= 0 ) {
			$message = sprintf(
				'WpdbProductMetaRepository::findByProductAndKey() requires a positive productId, got %d.',
				$productId
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		if ( $metaKey === '' ) {
			$message = 'WpdbProductMetaRepository::findByProductAndKey() requires a non-empty metaKey.';
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		$sql = $this->wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared -- Table name resolved from a class-level constant, not user input.
			'SELECT * FROM ' . $this->tableName . ' WHERE product_id = %d AND meta_key = %s LIMIT 1',
			$productId,
			$metaKey
		);

		return $this->fetchOne( $sql );
	}

	/**
	 * 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(
				'WpdbProductMetaRepository::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(
				'WpdbProductMetaRepository::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 `clb_product_meta`.
	 *
	 * Backed by a `SELECT COUNT(*)` query rather than materializing
	 * every row, matching the {@see ProductMetaRepository::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 CLB-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;
	}

	/**
	 * Execute a prepared SELECT and decode the first row into a
	 * {@see ProductMetaRecord}, 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 $preparedSql SQL produced by `$wpdb->prepare()`.
	 *
	 * @throws RuntimeException When `$wpdb` reports an error or when
	 *                          the returned row cannot be decoded
	 *                          into a {@see ProductMetaRecord}.
	 */
	private function fetchOne( string $preparedSql ): ?ProductMetaRecord {
		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Reading CLB-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.
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Already prepared upstream by wpdb->prepare().
		$row = $this->wpdb->get_row( $preparedSql, 'ARRAY_A' );
		$this->assertNoDriverError( 'select' );

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

		if ( ! is_array( $row ) ) {
			$message = 'WpdbProductMetaRepository::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 ProductMetaRecord::fromArray( $row );
	}

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