<?php
/**
 * Wpdb-backed embed-item repository.
 *
 * Production {@see EmbedItemRepository} implementation that
 * persists `affilun_embed_items` junction rows through the WordPress
 * `$wpdb` driver. Phase 2 introduces this repository primarily to
 * back the KI-002 per-product render-cache invalidation sweep:
 * given a `product_id`, the
 * {@see \Affilun\Application\Cache\CacheInvalidator}
 * needs an indexed lookup of every embed that references the
 * product so the targeted `forgetByPrefix()` call on the render
 * cache can replace the Phase 9 MVP overshoot
 * (`renderCache->flush()`).
 *
 * Design notes:
 *
 *   - The physical table name is resolved through the injected
 *     {@see TableNameResolver} (see {@see PrefixedTableNameResolver}
 *     for the production default) and cached on the instance so
 *     every method emits 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}.
 *
 *   - `$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 just keeps PHPStan and the autoloader from
 *     pulling in `\wpdb` for pure-unit runs.
 *
 *   - {@see self::findEmbedIdsByProductId()} issues a single
 *     `SELECT DISTINCT embed_id ... ORDER BY embed_id ASC` query so
 *     the §7.3 `idx_product_id` index does the heavy lifting and
 *     the per-product invalidation path stays O(matches) rather
 *     than O(rows).
 *
 *   - {@see self::replaceEmbedItems()} owns the §7.1 "delete-then-
 *     insert" semantics. It validates the input list (positive ids,
 *     no duplicates) BEFORE touching the database so a malformed
 *     call cannot leave the embed half-updated, then issues the
 *     delete + insert pair inside a `START TRANSACTION` /
 *     `COMMIT` block when the driver supports it. A failure during
 *     the insert phase rolls back so the embed retains its prior
 *     row set.
 *
 *   - {@see self::deleteByEmbedId()} is idempotent: it returns the
 *     count of removed rows, with zero indicating "nothing
 *     matched". Neither outcome is an error.
 *
 * Failure semantics:
 *
 *   - Driver errors (connection loss, constraint violations,
 *     malformed rows) are promoted to {@see RuntimeException} via
 *     {@see self::assertNoDriverError()} so call sites can catch at
 *     a single boundary without importing driver classes.
 *
 *   - Validation failures on
 *     {@see self::findEmbedIdsByProductId()},
 *     {@see self::replaceEmbedItems()}, and
 *     {@see self::deleteByEmbedId()} raise
 *     {@see InvalidArgumentException} BEFORE any DB write so a
 *     programming error at the call site surfaces loudly.
 *
 * @package Affilun\Infrastructure\Persistence
 */

declare(strict_types=1);

// phpcs:disable Generic.Files.LineLength.TooLong -- Developer-facing exception strings include full class/method context.

// 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 EmbedItemRepository} implementation backed by WordPress's
 * `$wpdb`.
 *
 * See the file-level docblock for the table-name resolution
 * strategy, the §7.3 indexed reverse-lookup query, and the
 * transactional delete-then-insert semantics of
 * {@see self::replaceEmbedItems()}.
 */
final class WpdbEmbedItemRepository implements EmbedItemRepository {

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

	/**
	 * MySQL `DATETIME` format used for the `created_at` column.
	 */
	private const MYSQL_DATETIME_FORMAT = 'Y-m-d H:i:s';

	/**
	 * `$wpdb`-shaped collaborator. Typed as `object` so the file
	 * stays trivially loadable in unit tests; see 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.
	 */
	private string $tableName;

	/**
	 * Construct the repository.
	 *
	 * @param object            $wpdb       WordPress `$wpdb`-like
	 *                                      object exposing `insert()`,
	 *                                      `delete()`, `prepare()`,
	 *                                      `get_col()` /
	 *                                      `get_results()`,
	 *                                      `get_var()`, `query()`,
	 *                                      and the `last_error`
	 *                                      property.
	 * @param TableNameResolver $tableNames Resolver used to derive
	 *                                      the physical
	 *                                      `affilun_embed_items` 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;
	}

	/**
	 * Find every embed id that references the supplied product.
	 *
	 * Issues a single `SELECT DISTINCT embed_id` query under the
	 * §7.3 `idx_product_id` index, ordered ascending so the
	 * cache-invalidator loop and its tests are deterministic.
	 *
	 * @param int $productId Local `affilun_products.id` to look up.
	 *
	 * @return list<int>
	 *
	 * Errors: raises {@see RuntimeException} (via
	 * {@see self::assertNoDriverError()}) when `$wpdb` reports an
	 * error during the SELECT.
	 *
	 * @throws InvalidArgumentException When `$productId` is non-positive.
	 */
	public function findEmbedIdsByProductId( int $productId ): array {
		if ( $productId <= 0 ) {
			$message = sprintf(
				'WpdbEmbedItemRepository::findEmbedIdsByProductId() requires a positive productId, got %d.',
				$productId
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		$selectSql = 'SELECT DISTINCT embed_id FROM ' . $this->tableName
			. ' WHERE product_id = %d ORDER BY embed_id ASC';

		$this->clearLastError();

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- KI-002 reverse-index lookup.
		$rows = $this->wpdb->get_col(
			// 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, $productId )
		);

		$this->assertNoDriverError( 'select' );

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

		$ids = array();
		foreach ( $rows as $value ) {
			$id = (int) $value;
			if ( $id > 0 ) {
				$ids[] = $id;
			}
		}

		return $ids;
	}

	/**
	 * Replace the items associated with `$embedId` with the
	 * supplied ordered product-id list.
	 *
	 * Validates the input list BEFORE touching the database, then
	 * issues the delete + insert pair inside a `START TRANSACTION`
	 * / `COMMIT` block when the driver supports it. A failure
	 * during the insert phase rolls back so the embed retains its
	 * prior row set.
	 *
	 * @param int       $embedId    Positive `affilun_embeds.id`.
	 * @param list<int> $productIds Ordered product ids; positions
	 *                              become `sort_order` values.
	 *
	 * @return int Number of inserted rows.
	 *
	 * @throws InvalidArgumentException When validation fails.
	 * @throws RuntimeException         When `$wpdb->insert()` returns false
	 *                                  without raising a driver error.
	 * @throws \Throwable               Re-raised verbatim when the rollback
	 *                                  branch fires after a failed insert
	 *                                  inside the transaction.
	 */
	public function replaceEmbedItems( int $embedId, array $productIds ): int {
		if ( $embedId <= 0 ) {
			$message = sprintf(
				'WpdbEmbedItemRepository::replaceEmbedItems() requires a positive embedId, got %d.',
				$embedId
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		$normalized = array();
		$seen       = array();
		foreach ( $productIds as $position => $productId ) {
			if ( ! is_int( $productId ) || $productId <= 0 ) {
				$message = sprintf(
					'WpdbEmbedItemRepository::replaceEmbedItems() requires positive product ids; productIds[%d] invalid.',
					(int) $position
				);
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new InvalidArgumentException( $message );
			}

			if ( array_key_exists( $productId, $seen ) ) {
				$message = sprintf(
					'WpdbEmbedItemRepository::replaceEmbedItems() rejects duplicate productId %d.',
					$productId
				);
				// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
				throw new InvalidArgumentException( $message );
			}
			$seen[ $productId ] = true;

			$normalized[] = $productId;
		}

		$useTransaction = $this->beginTransactionIfSupported();

		try {
			$this->deleteByEmbedIdInternal( $embedId );

			$now = ( new DateTimeImmutable( 'now', new DateTimeZone( 'UTC' ) ) )
				->format( self::MYSQL_DATETIME_FORMAT );

			foreach ( $normalized as $sortOrder => $productId ) {
				$this->clearLastError();

				$row     = array(
					'embed_id'   => $embedId,
					'product_id' => $productId,
					'sort_order' => $sortOrder,
					'created_at' => $now,
				);
				$formats = array( '%d', '%d', '%d', '%s' );

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

				$this->assertNoDriverError( 'insert' );

				if ( $result === false ) {
					$message = sprintf(
						'WpdbEmbedItemRepository::replaceEmbedItems(): $wpdb->insert() returned false for embed_id=%d product_id=%d.',
						$embedId,
						$productId
					);
					// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
					throw new RuntimeException( $message );
				}
			}
		} catch ( \Throwable $e ) {
			if ( $useTransaction ) {
				$this->rollbackQuietly();
			}
			throw $e;
		}

		if ( $useTransaction ) {
			$this->commitTransaction();
		}

		return count( $normalized );
	}

	/**
	 * Remove every junction row for the supplied embed id.
	 *
	 * Idempotent: returns the count of removed rows.
	 *
	 * @param int $embedId Positive `affilun_embeds.id`.
	 *
	 * Errors: raises {@see RuntimeException} (via
	 * {@see self::deleteByEmbedIdInternal()}) when `$wpdb` reports
	 * an error during the DELETE.
	 *
	 * @throws InvalidArgumentException When `$embedId` is non-positive.
	 */
	public function deleteByEmbedId( int $embedId ): int {
		if ( $embedId <= 0 ) {
			$message = sprintf(
				'WpdbEmbedItemRepository::deleteByEmbedId() requires a positive embedId, got %d.',
				$embedId
			);
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing message, never rendered to HTML.
			throw new InvalidArgumentException( $message );
		}

		return $this->deleteByEmbedIdInternal( $embedId );
	}

	/**
	 * Total number of rows currently stored in `affilun_embed_items`.
	 *
	 * Backed by a `SELECT COUNT(*)` query rather than
	 * materializing every row.
	 *
	 * @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;
	}

	/**
	 * Internal `deleteByEmbedId()` body shared by the public
	 * surface and the {@see self::replaceEmbedItems()} flow so the
	 * delete branch does not duplicate the driver-error promotion
	 * boilerplate.
	 *
	 * @param int $embedId Positive `affilun_embeds.id`. Caller has
	 *                     already validated.
	 *
	 * @throws RuntimeException When `$wpdb->delete()` returns false
	 *                          without raising a driver error.
	 */
	private function deleteByEmbedIdInternal( int $embedId ): int {
		$this->clearLastError();

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

		$this->assertNoDriverError( 'delete' );

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

		return (int) $result;
	}

	/**
	 * Issue `START TRANSACTION` when the driver supports it.
	 *
	 * Returns true when the transaction was started (caller MUST
	 * subsequently call {@see self::commitTransaction()} or
	 * {@see self::rollbackQuietly()}), false when the driver does
	 * not expose `query()` so the caller falls back on
	 * non-transactional writes.
	 */
	private function beginTransactionIfSupported(): bool {
		if ( ! method_exists( $this->wpdb, 'query' ) ) {
			return false;
		}

		$this->clearLastError();
		$this->wpdb->query( 'START TRANSACTION' );
		$this->assertNoDriverError( 'begin' );

		return true;
	}

	/**
	 * Issue `COMMIT` and promote any driver error.
	 */
	private function commitTransaction(): void {
		$this->clearLastError();
		$this->wpdb->query( 'COMMIT' );
		$this->assertNoDriverError( 'commit' );
	}

	/**
	 * Issue `ROLLBACK` swallowing any driver error.
	 *
	 * Used on the failure path of
	 * {@see self::replaceEmbedItems()} so the surrounding
	 * exception (the original cause) reaches the caller verbatim
	 * rather than being shadowed by a follow-up rollback failure.
	 */
	private function rollbackQuietly(): void {
		try {
			$this->clearLastError();
			$this->wpdb->query( 'ROLLBACK' );
		} catch ( \Throwable $e ) {
			// Intentionally swallowed: see method docblock.
			unset( $e );
		}
	}

	/**
	 * 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`, `delete`, `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(
			'WpdbEmbedItemRepository: $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 );
	}
}