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

declare(strict_types=1);

// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQL.NotPrepared,WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Identifiers are prepared and sort expressions come from a closed map.

namespace Affilun\Infrastructure\Persistence;

use Affilun\Application\AmazonDiscount\AmazonDiscountBrand;
use Affilun\Application\AmazonDiscount\AmazonDiscountDisplayPolicy;
use Affilun\Application\AmazonDiscount\AmazonDiscountProduct;
use Affilun\Application\AmazonDiscount\AmazonDiscountRepository;
use Affilun\Domain\Clock;
use Affilun\Infrastructure\Clock\SystemClock;
use DateTimeImmutable;
use DateTimeZone;
use RuntimeException;
use Throwable;

/**
 * Stores shortcode-only discount snapshots outside affilun_products.
 */
final class WpdbAmazonDiscountRepository implements AmazonDiscountRepository {

	public const TABLE_LOGICAL_NAME = 'affilun_amazon_discount_products';

	/**
	 * @var array<string,string>
	 */
	private const ORDER_BY = array(
		'discount_desc' => '(savings_percentage IS NULL) ASC, savings_percentage DESC, position ASC',
		'discount_asc'  => '(savings_percentage IS NULL) ASC, savings_percentage ASC, position ASC',
		'brand_asc'     => '(brand IS NULL) ASC, brand ASC, title ASC',
		'brand_desc'    => '(brand IS NULL) ASC, brand DESC, title ASC',
		'title_asc'     => 'title ASC',
		'title_desc'    => 'title DESC',
	);

	/**
	 * Prefix-resolved table name.
	 */
	private string $tableName;

	/**
	 * Create the WordPress-backed repository.
	 *
	 * @param object            $wpdb       WordPress database object.
	 * @param TableNameResolver $tableNames Table-name resolver.
	 * @param Clock             $clock      Display-time clock.
	 */
	public function __construct(
		private object $wpdb,
		TableNameResolver $tableNames,
		private Clock $clock = new SystemClock()
	) {
		$this->tableName = $tableNames->resolve( self::TABLE_LOGICAL_NAME );
	}

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

	/**
	 * Replace one category snapshot transactionally.
	 *
	 * @param string                      $searchIndex Amazon SearchIndex.
	 * @param list<AmazonDiscountProduct> $products    Products in upstream order.
	 */
	public function replaceCategory( string $searchIndex, array $products ): int {
		return $this->replaceCollection( $searchIndex, $products );
	}

	/**
	 * Replace one brand snapshot transactionally.
	 *
	 * @param string                      $brand    Priority brand.
	 * @param list<AmazonDiscountProduct> $products Products in upstream order.
	 */
	#[\Override]
	public function replaceBrand( string $brand, array $products ): int {
		return $this->replaceCollection( AmazonDiscountBrand::storageKey( $brand ), $products );
	}

	/**
	 * Delete one brand snapshot.
	 *
	 * @param string $brand Priority brand.
	 */
	#[\Override]
	public function deleteBrand( string $brand ): void {
		$delete = $this->wpdb->prepare(
			'DELETE FROM %i WHERE search_index IN (%s, %s)',
			$this->tableName,
			AmazonDiscountDisplayPolicy::STORAGE_PREFIX . AmazonDiscountBrand::storageKey( $brand ),
			AmazonDiscountBrand::storageKey( $brand )
		);
		$this->queryOrFail( $delete, 'delete brand' );
	}

	/**
	 * Replace one stored collection transactionally.
	 *
	 * @param string                      $collectionKey Category or brand key.
	 * @param list<AmazonDiscountProduct> $products      Products in upstream order.
	 */
	private function replaceCollection( string $collectionKey, array $products ): int {
		$this->queryOrFail( 'START TRANSACTION', 'start transaction' );
		try {
			$delete = $this->wpdb->prepare(
				'DELETE FROM %i WHERE search_index IN (%s, %s)',
				$this->tableName,
				AmazonDiscountDisplayPolicy::STORAGE_PREFIX . $collectionKey,
				$collectionKey
			);
			$this->queryOrFail( $delete, 'delete collection' );

			foreach ( $products as $product ) {
				if ( ! $product instanceof AmazonDiscountProduct ) {
					throw new RuntimeException( 'Amazon discount repository received an invalid product.' );
				}
				$result = $this->wpdb->insert(
					$this->tableName,
					array(
						'search_index'       => AmazonDiscountDisplayPolicy::STORAGE_PREFIX . $product->searchIndex(),
						'position'           => $product->position(),
						'asin'               => $product->asin(),
						'title'              => $product->title(),
						'brand'              => $product->brand(),
						'detail_url'         => $product->detailUrl(),
						'affiliate_url'      => $product->affiliateUrl(),
						'image_url'          => $product->imageUrl(),
						'price_amount'       => $product->priceAmount(),
						'price_currency'     => $product->priceCurrency(),
						'price_label'        => $product->priceLabel(),
						'savings_percentage' => $product->savingsPercentage(),
						'min_saving_percent' => $product->minSavingPercent(),
						'fetched_at'         => $product->fetchedAt()->setTimezone( new DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' ),
						'updated_at'         => $this->clock->now()->setTimezone( new DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' ),
					),
					array( '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s' )
				);
				$this->assertNoDriverError( 'insert' );
				if ( $result === false ) {
					throw new RuntimeException( 'Amazon discount product insert failed.' );
				}
			}

			$this->queryOrFail( 'COMMIT', 'commit' );
			return count( $products );
		} catch ( Throwable $error ) {
			$this->wpdb->query( 'ROLLBACK' );
			throw $error;
		}
	}

	/**
	 * Load sorted rows by category.
	 *
	 * @param list<string> $categories  Category order.
	 * @param string       $sort        Closed sort token.
	 * @param int          $perCategory Maximum rows per category.
	 *
	 * @return array<string,list<AmazonDiscountProduct>>
	 */
	public function findGrouped( array $categories, string $sort, int $perCategory ): array {
		return $this->findCollections( $categories, $sort, $perCategory );
	}

	/**
	 * Load sorted rows by brand.
	 *
	 * @param list<string> $brands   Brand order.
	 * @param string       $sort     Closed sort token.
	 * @param int          $perBrand Maximum rows per brand.
	 *
	 * @return array<string,list<AmazonDiscountProduct>>
	 */
	#[\Override]
	public function findBrandsGrouped( array $brands, string $sort, int $perBrand ): array {
		$keys       = array();
		$keyToBrand = array();
		foreach ( AmazonDiscountBrand::normalize( $brands ) as $brand ) {
			$key                = AmazonDiscountBrand::storageKey( $brand );
			$keys[]             = $key;
			$keyToBrand[ $key ] = $brand;
		}
		$stored  = $this->findCollections( $keys, $sort, $perBrand );
		$grouped = array();
		foreach ( $keyToBrand as $key => $brand ) {
			$grouped[ $brand ] = $stored[ $key ] ?? array();
		}
		return $grouped;
	}

	/**
	 * Load sorted rows by stored collection key.
	 *
	 * @param list<string> $keys  Collection keys.
	 * @param string       $sort  Closed sort token.
	 * @param int          $limit Maximum rows per collection.
	 *
	 * @return array<string,list<AmazonDiscountProduct>>
	 */
	private function findCollections( array $keys, string $sort, int $limit ): array {
		$orderBy = self::ORDER_BY[ $sort ] ?? self::ORDER_BY['discount_desc'];
		$limit   = max( 1, min( 20, $limit ) );
		$grouped = array();
		$now     = $this->clock->now();
		$utc     = new DateTimeZone( 'UTC' );

		foreach ( $keys as $key ) {
			$brand = AmazonDiscountBrand::fromStorageKey( $key );
			$sql   = $this->wpdb->prepare(
				'SELECT * FROM %i WHERE search_index = %s'
				. ' AND fetched_at > %s AND fetched_at <= %s'
				. ' AND savings_percentage >= %d AND savings_percentage >= min_saving_percent'
				. ' AND savings_percentage <= 100 ORDER BY ' . $orderBy,
				$this->tableName,
				AmazonDiscountDisplayPolicy::STORAGE_PREFIX . $key,
				AmazonDiscountDisplayPolicy::cutoff( $now )->setTimezone( $utc )->format( 'Y-m-d H:i:s' ),
				$now->setTimezone( $utc )->format( 'Y-m-d H:i:s' ),
				\Affilun\Application\AmazonDiscount\AmazonDiscountSyncService::DEFAULT_MIN_SAVING_PERCENT
			);
			// Brand snapshots contain at most 100 rows; filter legacy mismatches before limiting.
			if ( $brand === null ) {
				$sql .= $this->wpdb->prepare( ' LIMIT %d', $limit );
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Values and identifier are prepared; ORDER BY is selected from a closed map.
			$rows = $this->wpdb->get_results( $sql, 'ARRAY_A' );
			$this->assertNoDriverError( 'select' );
			$grouped[ $key ] = array();
			if ( ! is_array( $rows ) ) {
				continue;
			}
			foreach ( $rows as $row ) {
				if ( is_array( $row ) ) {
					if ( ( $row['search_index'] ?? null ) !== AmazonDiscountDisplayPolicy::STORAGE_PREFIX . $key ) {
						continue;
					}
					$product = self::fromRow( $row );
					if ( ! AmazonDiscountDisplayPolicy::allows( $product, $now ) ) {
						continue;
					}
					if ( $brand !== null && ! AmazonDiscountBrand::matches( $brand, $product->brand() ) ) {
						continue;
					}
					$grouped[ $key ][] = $product;
					if ( count( $grouped[ $key ] ) >= $limit ) {
						break;
					}
				}
			}
		}

		return $grouped;
	}

	/**
	 * Count all stored discount rows.
	 */
	public function count(): int {
		$sql = $this->wpdb->prepare( 'SELECT COUNT(*) FROM %i', $this->tableName );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Identifier prepared above.
		$count = $this->wpdb->get_var( $sql );
		$this->assertNoDriverError( 'count' );
		return (int) $count;
	}

	/**
	 * Return the newest fetch timestamp.
	 */
	public function latestFetchedAt(): ?DateTimeImmutable {
		$sql = $this->wpdb->prepare( 'SELECT MAX(fetched_at) FROM %i', $this->tableName );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Identifier prepared above.
		$value = $this->wpdb->get_var( $sql );
		$this->assertNoDriverError( 'latest fetch' );
		if ( ! is_string( $value ) || trim( $value ) === '' ) {
			return null;
		}
		return new DateTimeImmutable( $value, new DateTimeZone( 'UTC' ) );
	}

	/**
	 * @param array<string,mixed> $row Database row.
	 */
	private static function fromRow( array $row ): AmazonDiscountProduct {
		return new AmazonDiscountProduct(
			substr( (string) $row['search_index'], strlen( AmazonDiscountDisplayPolicy::STORAGE_PREFIX ) ),
			(int) $row['position'],
			(string) $row['asin'],
			(string) $row['title'],
			isset( $row['brand'] ) && $row['brand'] !== '' ? (string) $row['brand'] : null,
			(string) $row['detail_url'],
			(string) $row['affiliate_url'],
			isset( $row['image_url'] ) && $row['image_url'] !== '' ? (string) $row['image_url'] : null,
			isset( $row['price_amount'] ) && $row['price_amount'] !== '' ? (string) $row['price_amount'] : null,
			isset( $row['price_currency'] ) && $row['price_currency'] !== '' ? (string) $row['price_currency'] : null,
			isset( $row['price_label'] ) && $row['price_label'] !== '' ? (string) $row['price_label'] : null,
			isset( $row['savings_percentage'] ) ? (int) $row['savings_percentage'] : null,
			(int) $row['min_saving_percent'],
			new DateTimeImmutable( (string) $row['fetched_at'], new DateTimeZone( 'UTC' ) )
		);
	}

	/**
	 * Execute a query and surface database failures.
	 *
	 * @param string $sql       Prepared SQL or transaction command.
	 * @param string $operation Internal operation label.
	 */
	private function queryOrFail( string $sql, string $operation ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Transaction controls or already-prepared SQL.
		$result = $this->wpdb->query( $sql );
		$this->assertNoDriverError( $operation );
		if ( $result === false ) {
			throw new RuntimeException( 'Amazon discount repository failed to ' . $operation . '.' );
		}
	}

	/**
	 * Surface the current database driver error.
	 *
	 * @param string $operation Internal operation label.
	 */
	private function assertNoDriverError( string $operation ): void {
		$error = property_exists( $this->wpdb, 'last_error' ) ? $this->wpdb->last_error : '';
		if ( is_string( $error ) && trim( $error ) !== '' ) {
			throw new RuntimeException( 'Amazon discount repository ' . $operation . ' failed: ' . $error );
		}
	}
}