<?php
/**
 * Data access for the `embp_aliases` table (SPEC §3.4).
 *
 * @package EmberPrice
 */

namespace EmberPrice\Matching;

defined( 'ABSPATH' ) || exit;

/**
 * Stage 0 of the match cascade reads this via find(); every human
 * confirmation (or validated LLM suggestion) writes to it via remember(),
 * so the same row resolves instantly on the supplier's next import.
 * Callers are responsible for normalizing the alias text first (see
 * Normalizer) -- this class only persists it.
 */
final class AliasStore implements AliasLookupInterface {

	/**
	 * Exact lookup for stage 0: (supplier, normalized alias) -> learned match.
	 *
	 * @param int    $supplier_id       Supplier id.
	 * @param string $alias_normalized  Already-normalized alias text.
	 */
	public function find( int $supplier_id, string $alias_normalized ): ?array {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_aliases';

		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE supplier_id = %d AND alias_normalized = %s',
				$table,
				$supplier_id,
				$alias_normalized
			),
			ARRAY_A
		);

		return null === $row ? null : $this->hydrate( $row );
	}

	/**
	 * Records a human confirmation. Creates the alias if it's new; if this
	 * (supplier, alias) pair was already learned, repoints it to the newly
	 * confirmed product/variation and bumps its hit count -- the latest
	 * confirmation always wins over whatever it was previously mapped to.
	 *
	 * @param int      $supplier_id      Supplier id.
	 * @param string   $alias_normalized Already-normalized alias text.
	 * @param int      $product_id       Confirmed product id.
	 * @param int|null $variation_id     Confirmed variation id, if any.
	 */
	public function remember( int $supplier_id, string $alias_normalized, int $product_id, ?int $variation_id = null ): void {
		global $wpdb;
		$table = $wpdb->prefix . 'embp_aliases';
		$now   = current_time( 'mysql' );

		if ( null === $variation_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table write.
			$wpdb->query(
				$wpdb->prepare(
					'INSERT INTO %i (supplier_id, alias_normalized, product_id, variation_id, hits, last_used_at) VALUES (%d, %s, %d, NULL, 1, %s) ON DUPLICATE KEY UPDATE product_id = VALUES(product_id), variation_id = VALUES(variation_id), hits = hits + 1, last_used_at = VALUES(last_used_at)',
					$table,
					$supplier_id,
					$alias_normalized,
					$product_id,
					$now
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table write.
			$wpdb->query(
				$wpdb->prepare(
					'INSERT INTO %i (supplier_id, alias_normalized, product_id, variation_id, hits, last_used_at) VALUES (%d, %s, %d, %d, 1, %s) ON DUPLICATE KEY UPDATE product_id = VALUES(product_id), variation_id = VALUES(variation_id), hits = hits + 1, last_used_at = VALUES(last_used_at)',
					$table,
					$supplier_id,
					$alias_normalized,
					$product_id,
					$variation_id,
					$now
				)
			);
		}
	}

	/**
	 * Casts DB column types.
	 *
	 * @param array $row Raw database row.
	 */
	private function hydrate( array $row ): array {
		return array(
			'id'               => (int) $row['id'],
			'supplier_id'      => (int) $row['supplier_id'],
			'alias_normalized' => $row['alias_normalized'],
			'product_id'       => (int) $row['product_id'],
			'variation_id'     => null !== $row['variation_id'] ? (int) $row['variation_id'] : null,
			'hits'             => (int) $row['hits'],
			'last_used_at'     => $row['last_used_at'],
		);
	}
}
