<?php
/**
 * WordPress-backed embed repository.
 *
 * Production {@see EmbedRepository} implementation that persists
 * {@see EmbedRecord} instances into the `affilun_embeds` table defined in
 * `specifications/database-schema.md` §6, 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 EmbedRecord::toArray()} /
 *     {@see EmbedRecord::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 EmbedRepository} 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 WpdbEmbedRepository implements EmbedRepository {

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

	/**
	 * Column names in the canonical order declared by
	 * `database-schema.md` §6.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(
		'uuid',
		'name',
		'type',
		'config_json',
		'created_by',
		'updated_by',
		'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 actor-id columns (`created_by` /
	 * `updated_by`) and `%s` for everything else, including the JSON
	 * configuration blob.
	 *
	 * @var array<string, string>
	 */
	private const COLUMN_FORMATS = array(
		'uuid'        => '%s',
		'name'        => '%s',
		'type'        => '%s',
		'config_json' => '%s',
		'created_by'  => '%d',
		'updated_by'  => '%d',
		'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_embeds` 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 EmbedRepository::save()} for the full contract. This
	 * implementation:
	 *
	 *   - Treats a null id as an insert. The column payload is built
	 *     from {@see EmbedRecord::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 EmbedRecord::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 EmbedRecord $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( EmbedRecord $record ): EmbedRecord {
		if ( $record->id() === null ) {
			return $this->insert( $record );
		}

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

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

	/**
	 * Look up a record by its `uuid` unique key from §6.3.
	 *
	 * Returns null when no row matches; raises
	 * {@see InvalidArgumentException} when the argument is an empty
	 * string because the only way to reach that path is a programming
	 * error at the call site.
	 *
	 * @param string $uuid Prefixed-ULID external identifier.
	 *
	 * @throws InvalidArgumentException When `$uuid` is empty.
	 *                                  Driver errors surface as RuntimeException
	 *                                  via {@see self::fetchOne()}.
	 */
	public function findByUuid( string $uuid ): ?EmbedRecord {
		if ( $uuid === '' ) {
			$message = 'WpdbEmbedRepository::findByUuid() requires a non-empty uuid.';
			// 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 uuid = %s LIMIT 1',
			$uuid
		);
	}

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

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

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