<?php
/**
 * WordPress-backed top affiliate candidate repository.
 *
 * @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 RuntimeException;

/**
 * Persists affilun Cloud top-selling candidates in an AFFILUN-owned product-shaped table.
 */
final class WpdbTopAffiliateCandidateRepository implements TopAffiliateCandidateRepository {

	public const TABLE_LOGICAL_NAME = 'affilun_top_affiliate_candidates';

	private const COLUMNS = array(
		'provider',
		'provider_item_id',
		'canonical_hash',
		'title',
		'brand',
		'description',
		'image_primary_url',
		'image_gallery_json',
		'price_amount',
		'price_currency',
		'price_label',
		'availability_status',
		'detail_url',
		'affiliate_url',
		'raw_payload_json',
		'search_keywords',
		'search_keywords_source',
		'snapshot_version',
		'sync_state',
		'lifecycle_state',
		'archived_at',
		'last_synced_at',
		'last_rendered_at',
		'last_sync_attempted_at',
		'last_sync_error_code',
		'last_sync_error_message',
		'created_at',
		'updated_at',
	);

	private const COLUMN_FORMATS = array(
		'provider'                => '%s',
		'provider_item_id'        => '%s',
		'canonical_hash'          => '%s',
		'title'                   => '%s',
		'brand'                   => '%s',
		'description'             => '%s',
		'image_primary_url'       => '%s',
		'image_gallery_json'      => '%s',
		'price_amount'            => '%s',
		'price_currency'          => '%s',
		'price_label'             => '%s',
		'availability_status'     => '%s',
		'detail_url'              => '%s',
		'affiliate_url'           => '%s',
		'raw_payload_json'        => '%s',
		'search_keywords'         => '%s',
		'search_keywords_source'  => '%s',
		'snapshot_version'        => '%d',
		'sync_state'              => '%s',
		'lifecycle_state'         => '%s',
		'archived_at'             => '%s',
		'last_synced_at'          => '%s',
		'last_rendered_at'        => '%s',
		'last_sync_attempted_at'  => '%s',
		'last_sync_error_code'    => '%s',
		'last_sync_error_message' => '%s',
		'created_at'              => '%s',
		'updated_at'              => '%s',
	);

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

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

	/**
	 * @param object            $wpdb       WordPress database adapter.
	 * @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 table name.
	 */
	public function tableName(): string {
		return $this->tableName;
	}

	/**
	 * Replace cached candidate rows.
	 *
	 * @param list<array<string,mixed>> $rows Product-shaped rows.
	 */
	public function replaceAll( array $rows ): int {
		$this->clearLastError();
		$deleteSql = $this->wpdb->prepare( 'DELETE FROM %i', $this->tableName );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Prepared immediately above with an identifier placeholder.
		$deleted = $this->wpdb->query( $deleteSql );
		$this->assertNoDriverError( 'delete' );
		if ( $deleted === false ) {
			throw new RuntimeException( 'WpdbTopAffiliateCandidateRepository::replaceAll(): failed to clear candidates.' );
		}

		$inserted = 0;
		foreach ( $rows as $row ) {
			$payload = $this->payloadFromRow( $row );
			if ( $payload === null ) {
				continue;
			}
			$this->clearLastError();
			$result = $this->wpdb->insert(
				$this->tableName,
				$payload,
				$this->formatsFor( array_keys( $payload ) )
			);
			$this->assertNoDriverError( 'insert' );
			if ( $result === false ) {
				throw new RuntimeException( 'WpdbTopAffiliateCandidateRepository::replaceAll(): insert failed.' );
			}
			++$inserted;
		}

		return $inserted;
	}

	/**
	 * List cached candidate rows.
	 *
	 * @param ProductListQuery $query List query.
	 */
	public function findPaginated( ProductListQuery $query ): ProductListResult {
		$whereArgs = $this->candidateFilterArgs( $query );
		$countSql  = $this->wpdb->prepare(
			'SELECT COUNT(*) FROM %i WHERE (%s = \'0\' OR provider = %s) AND (%s = \'0\' OR title LIKE %s) AND (%s = \'0\' OR sync_state = %s) AND (%s = \'0\' OR lifecycle_state = %s)',
			$this->tableName,
			$whereArgs[0],
			$whereArgs[1],
			$whereArgs[2],
			$whereArgs[3],
			$whereArgs[4],
			$whereArgs[5],
			$whereArgs[6],
			$whereArgs[7]
		);
		$this->clearLastError();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Prepared immediately above.
		$totalItems = (int) $this->wpdb->get_var( $countSql );
		$this->assertNoDriverError( 'count' );

		$sortColumn = $this->sortColumnFor( $query->sortKey() );
		$sortOrder  = $query->sortOrder() === ProductListQuery::SORT_ORDER_ASC ? 'ASC' : 'DESC';
		$perPage    = $query->perPage();
		$offset     = ( $query->page() - 1 ) * $perPage;
		$selectSql  = $sortOrder === 'ASC' ? $this->wpdb->prepare(
			'SELECT * FROM %i WHERE (%s = \'0\' OR provider = %s) AND (%s = \'0\' OR title LIKE %s) AND (%s = \'0\' OR sync_state = %s) AND (%s = \'0\' OR lifecycle_state = %s) ORDER BY %i ASC LIMIT %d OFFSET %d',
			$this->tableName,
			$whereArgs[0],
			$whereArgs[1],
			$whereArgs[2],
			$whereArgs[3],
			$whereArgs[4],
			$whereArgs[5],
			$whereArgs[6],
			$whereArgs[7],
			$sortColumn,
			$perPage,
			$offset
		) : $this->wpdb->prepare(
			'SELECT * FROM %i WHERE (%s = \'0\' OR provider = %s) AND (%s = \'0\' OR title LIKE %s) AND (%s = \'0\' OR sync_state = %s) AND (%s = \'0\' OR lifecycle_state = %s) ORDER BY %i DESC LIMIT %d OFFSET %d',
			$this->tableName,
			$whereArgs[0],
			$whereArgs[1],
			$whereArgs[2],
			$whereArgs[3],
			$whereArgs[4],
			$whereArgs[5],
			$whereArgs[6],
			$whereArgs[7],
			$sortColumn,
			$perPage,
			$offset
		);

		$this->clearLastError();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Prepared immediately above.
		$rows = $this->wpdb->get_results( $selectSql, 'ARRAY_A' );
		$this->assertNoDriverError( 'select' );
		if ( ! is_array( $rows ) ) {
			throw new RuntimeException( 'WpdbTopAffiliateCandidateRepository::findPaginated(): non-array result.' );
		}

		$items = array();
		foreach ( $rows as $row ) {
			if ( is_array( $row ) ) {
				$items[] = ProductRecord::fromArray( $row );
			}
		}

		return new ProductListResult( $items, $totalItems, $query->page(), $perPage );
	}

	/**
	 * Build fixed-shape WHERE arguments for candidate list filters.
	 *
	 * @param ProductListQuery $query List query.
	 *
	 * @return list<string>
	 */
	private function candidateFilterArgs( ProductListQuery $query ): array {
		$provider       = $query->provider();
		$search         = $query->search();
		$syncState      = $query->syncState();
		$lifecycleState = $query->lifecycleState();

		return array(
			$provider === null ? '0' : '1',
			$provider ?? '',
			$search === null ? '0' : '1',
			$search === null ? '' : '%' . $this->wpdb->esc_like( $search ) . '%',
			$syncState === null ? '0' : '1',
			$syncState ?? '',
			$lifecycleState === null ? '0' : '1',
			$lifecycleState ?? '',
		);
	}

	/**
	 * Count cached candidate rows.
	 */
	public function count(): int {
		$this->clearLastError();
		$countSql = $this->wpdb->prepare( 'SELECT COUNT(*) FROM %i', $this->tableName );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- Prepared immediately above with an identifier placeholder.
		$value = $this->wpdb->get_var( $countSql );
		$this->assertNoDriverError( 'count' );
		return (int) $value;
	}

	/**
	 * @param array<string,mixed> $row Candidate row.
	 *
	 * @return array<string,mixed>|null
	 */
	private function payloadFromRow( array $row ): ?array {
		$provider       = self::stringField( $row, 'provider' );
		$providerItemId = self::stringField( $row, 'provider_item_id' );
		$title          = self::stringField( $row, 'title' );
		$detailUrl      = self::stringField( $row, 'detail_url' );
		if ( $provider === '' || $providerItemId === '' || $title === '' || $detailUrl === '' ) {
			return null;
		}
		$now                     = gmdate( ProductRecord::MYSQL_DATETIME_FORMAT );
		$row                     = array_merge(
			array(
				'canonical_hash'          => hash( 'sha256', $provider . ':' . $providerItemId ),
				'brand'                   => null,
				'description'             => null,
				'image_primary_url'       => null,
				'image_gallery_json'      => null,
				'price_amount'            => null,
				'price_currency'          => null,
				'price_label'             => null,
				'availability_status'     => null,
				'affiliate_url'           => null,
				'raw_payload_json'        => null,
				'search_keywords'         => null,
				'search_keywords_source'  => null,
				'snapshot_version'        => 1,
				'sync_state'              => ProductRecord::DEFAULT_SYNC_STATE,
				'lifecycle_state'         => ProductRecord::DEFAULT_LIFECYCLE_STATE,
				'archived_at'             => null,
				'last_synced_at'          => $now,
				'last_rendered_at'        => null,
				'last_sync_attempted_at'  => $now,
				'last_sync_error_code'    => null,
				'last_sync_error_message' => null,
				'created_at'              => $now,
				'updated_at'              => $now,
			),
			$row
		);
		$row['provider']         = $provider;
		$row['provider_item_id'] = $providerItemId;
		$row['title']            = $title;
		$row['detail_url']       = $detailUrl;
		$row['canonical_hash']   = self::stringField( $row, 'canonical_hash' ) !== '' ? self::stringField( $row, 'canonical_hash' ) : hash( 'sha256', $provider . ':' . $providerItemId );

		return array_intersect_key( $row, array_flip( self::COLUMNS ) );
	}

	/**
	 * @param array<string,mixed> $row Row.
	 * @param string              $key Field key.
	 */
	private static function stringField( array $row, string $key ): string {
		$value = $row[ $key ] ?? '';
		return is_scalar( $value ) ? trim( (string) $value ) : '';
	}

	/**
	 * @param list<string> $columns Columns.
	 *
	 * @return list<string>
	 */
	private function formatsFor( array $columns ): array {
		return array_map(
			static fn ( string $column ): string => self::COLUMN_FORMATS[ $column ] ?? '%s',
			$columns
		);
	}

	/**
	 * Map a product list sort key to a safe SQL column.
	 *
	 * @param string $sortKey Sort key.
	 */
	private function sortColumnFor( string $sortKey ): string {
		return match ( $sortKey ) {
			ProductListQuery::SORT_KEY_CREATED_AT => 'created_at',
			ProductListQuery::SORT_KEY_TITLE => 'title',
			ProductListQuery::SORT_KEY_LAST_SYNCED_AT => 'last_synced_at',
			default => 'updated_at',
		};
	}

	/**
	 * Clear the last driver error before a query.
	 */
	private function clearLastError(): void {
		if ( property_exists( $this->wpdb, 'last_error' ) ) {
			$this->wpdb->last_error = '';
		}
	}

	/**
	 * Raise a repository exception when wpdb reports an error.
	 *
	 * @param string $operation Operation name.
	 */
	private function assertNoDriverError( string $operation ): void {
		$error = property_exists( $this->wpdb, 'last_error' ) ? (string) $this->wpdb->last_error : '';
		if ( $error !== '' ) {
			// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Developer-facing exception, never rendered to HTML.
			throw new RuntimeException( sprintf( 'WpdbTopAffiliateCandidateRepository::%s(): %s', $operation, $error ) );
		}
	}
}