<?php
/**
 * Price revision persistence (TSG-015).
 *
 * @package Kayvandra\MarginBeacon
 */

declare(strict_types=1);

namespace Kayvandra\MarginBeacon\Infrastructure\Persistence;

/**
 * Repository for marginbeacon_price_revisions.
 */
final class PriceRevisionRepository {

	/**
	 * Open (current) revision for an entity.
	 *
	 * @param int $product_id   Product ID.
	 * @param int $variation_id Variation ID (0 = simple/parent snapshot).
	 * @return array|null
	 */
	public function get_open( $product_id, $variation_id = 0 ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_price_revisions';
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$table} WHERE product_id = %d AND variation_id = %d AND valid_to_gmt IS NULL ORDER BY id DESC LIMIT 1",
				(int) $product_id,
				(int) $variation_id
			),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Revision covering a GMT timestamp (prefer variation row, then parent).
	 *
	 * @param int    $product_id   Product ID.
	 * @param int    $variation_id Variation ID.
	 * @param string $at_gmt       Y-m-d H:i:s GMT.
	 * @return array|null
	 */
	public function find_covering( $product_id, $variation_id, $at_gmt ) {
		$row = null;
		if ( $variation_id > 0 ) {
			$row = $this->find_covering_entity( (int) $product_id, (int) $variation_id, $at_gmt );
		}
		if ( ! $row ) {
			$row = $this->find_covering_entity( (int) $product_id, 0, $at_gmt );
		}
		return $row;
	}

	/**
	 * @param int    $product_id   Product ID.
	 * @param int    $variation_id Variation ID.
	 * @param string $at_gmt       Timestamp.
	 * @return array|null
	 */
	private function find_covering_entity( $product_id, $variation_id, $at_gmt ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_price_revisions';
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$table} WHERE product_id = %d AND variation_id = %d AND valid_from_gmt <= %s AND (valid_to_gmt IS NULL OR valid_to_gmt > %s) ORDER BY id DESC LIMIT 1",
				$product_id,
				$variation_id,
				$at_gmt,
				$at_gmt
			),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Close an open revision.
	 *
	 * @param int    $id      Row ID.
	 * @param string $to_gmt  Close timestamp GMT.
	 * @return void
	 */
	public function close( $id, $to_gmt ) {
		global $wpdb;
		$wpdb->update(
			$wpdb->prefix . 'marginbeacon_price_revisions',
			array( 'valid_to_gmt' => $to_gmt ),
			array( 'id' => (int) $id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Insert a revision row.
	 *
	 * @param array $row Fields.
	 * @return int|false
	 */
	public function insert( array $row ) {
		global $wpdb;
		$ok = $wpdb->insert( $wpdb->prefix . 'marginbeacon_price_revisions', $row );
		if ( false === $ok ) {
			return false;
		}
		return (int) $wpdb->insert_id;
	}

	/**
	 * Close the current open revision and insert the next atomically.
	 * Uses SELECT … FOR UPDATE + optional advisory lock so concurrent writers cannot create two opens.
	 *
	 * @param array    $row       New revision row.
	 * @param int|null $close_id  Open revision to close (optional; ignored — re-read under lock).
	 * @param string   $close_gmt Close timestamp GMT.
	 * @return int|false New row ID or false on failure.
	 */
	public function replace_open( array $row, $close_id = null, $close_gmt = null ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_price_revisions';

		$product_id   = isset( $row['product_id'] ) ? (int) $row['product_id'] : 0;
		$variation_id = isset( $row['variation_id'] ) ? (int) $row['variation_id'] : 0;
		$lock_name    = 'aksmb_price_rev_' . $product_id . '_' . $variation_id;
		$got_lock     = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 10)', $lock_name ) );
		if ( 1 !== $got_lock ) {
			return false;
		}

		$wpdb->query( 'START TRANSACTION' );

		$open = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$table} WHERE product_id = %d AND variation_id = %d AND valid_to_gmt IS NULL ORDER BY id DESC LIMIT 1 FOR UPDATE",
				$product_id,
				$variation_id
			),
			ARRAY_A
		);

		$close_gmt = $close_gmt ? $close_gmt : gmdate( 'Y-m-d H:i:s' );

		if ( $open ) {
			// Same material hash → no-op (idempotent concurrent observe).
			if ( isset( $row['price_revision_hash'] ) && (string) $open['price_revision_hash'] === (string) $row['price_revision_hash'] ) {
				$wpdb->query( 'COMMIT' );
				$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
				return (int) $open['id'];
			}
			$closed = $wpdb->update(
				$table,
				array( 'valid_to_gmt' => $close_gmt ),
				array(
					'id'           => (int) $open['id'],
					'valid_to_gmt' => null,
				),
				array( '%s' ),
				array( '%d', '%s' )
			);
			if ( false === $closed ) {
				$wpdb->query( 'ROLLBACK' );
				$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
				return false;
			}
		}

		$ok = $wpdb->insert( $table, $row );
		if ( false === $ok ) {
			$wpdb->query( 'ROLLBACK' );
			$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
			return false;
		}

		$new_id = (int) $wpdb->insert_id;
		$wpdb->query( 'COMMIT' );
		$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
		return $new_id;
	}

	/**
	 * List revisions for a product/variation (newest first). Used by Pro Price History UI.
	 *
	 * @param int   $product_id   Product ID.
	 * @param int   $variation_id Variation ID (0 = parent/simple).
	 * @param array $args {
	 *     @type int         $limit      Max rows (1–200).
	 *     @type string|null $continuity continuous|gap_detected|null.
	 *     @type string|null $from_gmt   Inclusive lower bound on valid_from_gmt.
	 *     @type string|null $to_gmt     Inclusive upper bound on valid_from_gmt.
	 * }
	 * @return array<int,array<string,mixed>>
	 */
	public function list_for_entity( $product_id, $variation_id = 0, array $args = array() ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_price_revisions';
		$limit = isset( $args['limit'] ) ? max( 1, min( 200, (int) $args['limit'] ) ) : 50;

		$sql    = "SELECT * FROM {$table} WHERE product_id = %d AND variation_id = %d";
		$params = array( (int) $product_id, (int) $variation_id );

		if ( ! empty( $args['continuity'] ) ) {
			$cont = sanitize_key( (string) $args['continuity'] );
			if ( in_array( $cont, array( 'continuous', 'gap_detected' ), true ) ) {
				$sql     .= ' AND continuity_state = %s';
				$params[] = $cont;
			}
		}
		if ( ! empty( $args['from_gmt'] ) ) {
			$sql     .= ' AND valid_from_gmt >= %s';
			$params[] = (string) $args['from_gmt'];
		}
		if ( ! empty( $args['to_gmt'] ) ) {
			$sql     .= ' AND valid_from_gmt <= %s';
			$params[] = (string) $args['to_gmt'];
		}

		$sql     .= ' ORDER BY valid_from_gmt DESC, id DESC LIMIT %d';
		$params[] = $limit;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Recent revisions across catalog (for empty-state browse).
	 *
	 * @param int   $limit Max rows.
	 * @param array $args  Optional continuity filter.
	 * @return array<int,array<string,mixed>>
	 */
	public function list_recent( $limit = 40, array $args = array() ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_price_revisions';
		$limit = max( 1, min( 100, (int) $limit ) );

		$sql    = "SELECT * FROM {$table} WHERE 1=1";
		$params = array();
		if ( ! empty( $args['continuity'] ) ) {
			$cont = sanitize_key( (string) $args['continuity'] );
			if ( in_array( $cont, array( 'continuous', 'gap_detected' ), true ) ) {
				$sql     .= ' AND continuity_state = %s';
				$params[] = $cont;
			}
		}
		$sql     .= ' ORDER BY captured_at_gmt DESC, id DESC LIMIT %d';
		$params[] = $limit;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Count revisions for entity (existence check / empty state).
	 *
	 * @param int $product_id   Product ID.
	 * @param int $variation_id Variation ID.
	 * @return int
	 */
	public function count_for_entity( $product_id, $variation_id = 0 ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_price_revisions';
		$n     = $wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT COUNT(1) FROM {$table} WHERE product_id = %d AND variation_id = %d",
				(int) $product_id,
				(int) $variation_id
			)
		);
		return (int) $n;
	}
}