<?php
/**
 * WordPress-backed product URL repository.
 *
 * @package Affilun\Infrastructure\Persistence
 */

declare(strict_types=1);

namespace Affilun\Infrastructure\Persistence;

// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing exception messages are never rendered as HTML.

use InvalidArgumentException;
use RuntimeException;

/**
 * {@see ProductUrlRepository} implementation backed by `$wpdb`.
 */
final class WpdbProductUrlRepository implements ProductUrlRepository {

	public const TABLE_LOGICAL_NAME = 'affilun_product_urls';

	private const COLUMNS = array(
		'product_id',
		'provider_type',
		'provider_item_id',
		'url',
		'url_hash',
		'source',
		'created_at',
		'updated_at',
	);

	private const COLUMN_FORMATS = array(
		'product_id'       => '%d',
		'provider_type'    => '%s',
		'provider_item_id' => '%s',
		'url'              => '%s',
		'url_hash'         => '%s',
		'source'           => '%s',
		'created_at'       => '%s',
		'updated_at'       => '%s',
	);

	/**
	 * WordPress database driver.
	 */
	private object $wpdb;

	/**
	 * Physical product URL table name.
	 */
	private string $tableName;

	/**
	 * @param object            $wpdb       WordPress database driver.
	 * @param TableNameResolver $tableNames Table name resolver.
	 */
	public function __construct( object $wpdb, TableNameResolver $tableNames ) {
		$this->wpdb      = $wpdb;
		$this->tableName = $tableNames->resolve( self::TABLE_LOGICAL_NAME );
	}

	/**
	 * Return the physical product URL table name.
	 */
	public function tableName(): string {
		return $this->tableName;
	}

	/**
	 * Persist a product URL record.
	 *
	 * @param ProductUrlRecord $record URL record.
	 */
	public function save( ProductUrlRecord $record ): ProductUrlRecord {
		return $record->id() === null ? $this->insert( $record ) : $this->update( $record );
	}

	/**
	 * Insert a new URL record.
	 *
	 * @param ProductUrlRecord $record Unsaved URL record.
	 */
	private function insert( ProductUrlRecord $record ): ProductUrlRecord {
		$row     = $this->columnPayload( $record );
		$formats = $this->formatsFor( array_keys( $row ) );

		$this->clearLastError();
		$result = $this->wpdb->insert( $this->tableName, $row, $formats );
		$this->assertNoDriverError( 'insert' );
		if ( $result === false ) {
			throw new RuntimeException( 'WpdbProductUrlRepository::save(): $wpdb->insert() returned false.' );
		}

		$insertId = property_exists( $this->wpdb, 'insert_id' ) ? (int) $this->wpdb->insert_id : 0;
		if ( $insertId <= 0 ) {
			throw new RuntimeException( 'WpdbProductUrlRepository::save(): $wpdb->insert() did not populate insert_id.' );
		}

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

	/**
	 * Update an existing URL record.
	 *
	 * @param ProductUrlRecord $record URL record.
	 */
	private function update( ProductUrlRecord $record ): ProductUrlRecord {
		$id = $record->id();
		if ( $id === null ) {
			throw new RuntimeException( 'WpdbProductUrlRepository::update(): record must carry an id.' );
		}
		if ( $this->findById( $id ) === null ) {
			throw new RuntimeException( sprintf( 'WpdbProductUrlRepository::save(): cannot update missing id %d.', $id ) );
		}

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

		$this->clearLastError();
		$result = $this->wpdb->update( $this->tableName, $row, array( 'id' => $id ), $formats, array( '%d' ) );
		$this->assertNoDriverError( 'update' );
		if ( $result === false ) {
			throw new RuntimeException( sprintf( 'WpdbProductUrlRepository::save(): $wpdb->update() returned false for id %d.', $id ) );
		}

		return $record;
	}

	/**
	 * Find a URL record by database id.
	 *
	 * @param int $id Database row id.
	 */
	public function findById( int $id ): ?ProductUrlRecord {
		if ( $id <= 0 ) {
			throw new InvalidArgumentException( sprintf( 'WpdbProductUrlRepository::findById() requires a positive id, got %d.', $id ) );
		}

		$this->clearLastError();
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared inline with value and identifier placeholders.
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare( 'SELECT * FROM %i WHERE id = %d LIMIT 1', $this->tableName, $id ),
			'ARRAY_A'
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
		$this->assertNoDriverError( 'select' );
		return $this->recordFromRow( $row, 'findById' );
	}

	/**
	 * Find URL records attached to a product.
	 *
	 * @param int $productId Product id.
	 *
	 * @return list<ProductUrlRecord>
	 */
	public function findByProductId( int $productId ): array {
		if ( $productId <= 0 ) {
			throw new InvalidArgumentException( sprintf( 'WpdbProductUrlRepository::findByProductId() requires a positive productId, got %d.', $productId ) );
		}
		$this->clearLastError();
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared inline with value and identifier placeholders.
		$rows = $this->wpdb->get_results(
			$this->wpdb->prepare( 'SELECT * FROM %i WHERE product_id = %d ORDER BY id ASC', $this->tableName, $productId ),
			'ARRAY_A'
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
		$this->assertNoDriverError( 'select' );
		return $this->recordsFromRows( $rows, 'findByProductId' );
	}

	/**
	 * Find a product URL row by product, provider, and URL hash.
	 *
	 * @param int    $productId    Product id.
	 * @param string $providerType Provider namespace.
	 * @param string $urlHash      Normalized URL hash.
	 */
	public function findByProductProviderUrl( int $productId, string $providerType, string $urlHash ): ?ProductUrlRecord {
		if ( $productId <= 0 ) {
			throw new InvalidArgumentException( sprintf( 'WpdbProductUrlRepository::findByProductProviderUrl() requires a positive productId, got %d.', $productId ) );
		}
		if ( trim( $providerType ) === '' || trim( $urlHash ) === '' ) {
			throw new InvalidArgumentException( 'WpdbProductUrlRepository::findByProductProviderUrl() requires non-empty providerType and urlHash.' );
		}
		$this->clearLastError();
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared inline with value and identifier placeholders.
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				'SELECT * FROM %i WHERE product_id = %d AND provider_type = %s AND url_hash = %s LIMIT 1',
				$this->tableName,
				$productId,
				$providerType,
				$urlHash
			),
			'ARRAY_A'
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
		$this->assertNoDriverError( 'select' );
		return $this->recordFromRow( $row, 'findByProductProviderUrl' );
	}

	/**
	 * Find a URL row by provider-side item id.
	 *
	 * @param string $providerType   Provider namespace.
	 * @param string $providerItemId Provider-side item identifier.
	 */
	public function findByProviderItem( string $providerType, string $providerItemId ): ?ProductUrlRecord {
		if ( trim( $providerType ) === '' || trim( $providerItemId ) === '' ) {
			throw new InvalidArgumentException( 'WpdbProductUrlRepository::findByProviderItem() requires non-empty providerType and providerItemId.' );
		}
		$this->clearLastError();
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared inline with value and identifier placeholders.
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				'SELECT * FROM %i WHERE provider_type = %s AND provider_item_id = %s ORDER BY id ASC LIMIT 1',
				$this->tableName,
				$providerType,
				$providerItemId
			),
			'ARRAY_A'
		);
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
		$this->assertNoDriverError( 'select' );
		return $this->recordFromRow( $row, 'findByProviderItem' );
	}

	/**
	 * Delete a URL row by database id.
	 *
	 * @param int $id Database row id.
	 */
	public function delete( int $id ): bool {
		if ( $id <= 0 ) {
			throw new InvalidArgumentException( sprintf( 'WpdbProductUrlRepository::delete() requires a positive id, got %d.', $id ) );
		}
		$this->clearLastError();
		$result = $this->wpdb->delete( $this->tableName, array( 'id' => $id ), array( '%d' ) );
		$this->assertNoDriverError( 'delete' );
		if ( $result === false ) {
			throw new RuntimeException( sprintf( 'WpdbProductUrlRepository::delete(): $wpdb->delete() returned false for id %d.', $id ) );
		}
		return (int) $result > 0;
	}

	/**
	 * Delete URL rows for a product/provider pair.
	 *
	 * @param int    $productId    Product id.
	 * @param string $providerType Provider namespace.
	 */
	public function deleteByProductAndProvider( int $productId, string $providerType ): int {
		if ( $productId <= 0 ) {
			throw new InvalidArgumentException( sprintf( 'WpdbProductUrlRepository::deleteByProductAndProvider() requires a positive productId, got %d.', $productId ) );
		}
		if ( trim( $providerType ) === '' ) {
			throw new InvalidArgumentException( 'WpdbProductUrlRepository::deleteByProductAndProvider() requires a non-empty providerType.' );
		}

		$this->clearLastError();
		$result = $this->wpdb->delete(
			$this->tableName,
			array(
				'product_id'    => $productId,
				'provider_type' => $providerType,
			),
			array( '%d', '%s' )
		);
		$this->assertNoDriverError( 'delete' );
		if ( $result === false ) {
			throw new RuntimeException( 'WpdbProductUrlRepository::deleteByProductAndProvider(): $wpdb->delete() returned false.' );
		}
		return (int) $result;
	}

	/**
	 * Count stored URL rows.
	 */
	public function count(): int {
		$this->clearLastError();
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Query is prepared inline with an identifier placeholder.
		$value = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT COUNT(*) FROM %i', $this->tableName ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared
		$this->assertNoDriverError( 'count' );
		return (int) $value;
	}

	/**
	 * Convert a database row into a record.
	 *
	 * @param mixed  $row       Raw database row.
	 * @param string $operation Operation name for diagnostics.
	 */
	private function recordFromRow( mixed $row, string $operation ): ?ProductUrlRecord {
		if ( $row === null ) {
			return null;
		}
		if ( ! is_array( $row ) ) {
			throw new RuntimeException( sprintf( 'WpdbProductUrlRepository::%s(): $wpdb->get_row() returned a non-array row.', $operation ) );
		}
		return ProductUrlRecord::fromArray( $row );
	}

	/**
	 * Convert database rows into records.
	 *
	 * @param mixed  $rows      Raw database rows.
	 * @param string $operation Operation name for diagnostics.
	 *
	 * @return list<ProductUrlRecord>
	 */
	private function recordsFromRows( mixed $rows, string $operation ): array {
		if ( ! is_array( $rows ) ) {
			throw new RuntimeException( sprintf( 'WpdbProductUrlRepository::%s(): $wpdb->get_results() returned a non-array result set.', $operation ) );
		}
		$result = array();
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				throw new RuntimeException( sprintf( 'WpdbProductUrlRepository::%s(): $wpdb->get_results() returned a non-array row.', $operation ) );
			}
			$result[] = ProductUrlRecord::fromArray( $row );
		}
		return $result;
	}

	/**
	 * Build the writable column payload for a record.
	 *
	 * @param ProductUrlRecord $record URL record.
	 *
	 * @return array<string,string|int|null>
	 */
	private function columnPayload( ProductUrlRecord $record ): array {
		$row = $record->toArray();
		unset( $row['id'] );
		$filtered = array();
		foreach ( self::COLUMNS as $column ) {
			$filtered[ $column ] = $row[ $column ] ?? null;
		}
		return $filtered;
	}

	/**
	 * @param list<string> $columns Columns.
	 * @return list<string>
	 */
	private function formatsFor( array $columns ): array {
		$formats = array();
		foreach ( $columns as $column ) {
			if ( ! array_key_exists( $column, self::COLUMN_FORMATS ) ) {
				throw new RuntimeException( sprintf( 'WpdbProductUrlRepository::formatsFor(): no format for column "%s".', $column ) );
			}
			$formats[] = self::COLUMN_FORMATS[ $column ];
		}
		return $formats;
	}

	/**
	 * Reset the WordPress database driver's last error field.
	 */
	private function clearLastError(): void {
		if ( property_exists( $this->wpdb, 'last_error' ) ) {
			$this->wpdb->last_error = '';
		}
	}

	/**
	 * Throw when the WordPress database driver reported an error.
	 *
	 * @param string $operation Operation name for diagnostics.
	 */
	private function assertNoDriverError( string $operation ): void {
		$error = property_exists( $this->wpdb, 'last_error' ) ? (string) $this->wpdb->last_error : '';
		if ( $error !== '' ) {
			throw new RuntimeException( sprintf( 'WpdbProductUrlRepository: %s failed: %s', $operation, $error ) );
		}
	}
}