<?php
/**
 * Acknowledgements persistence (FR-041).
 *
 * @package Kayvandra\MarginBeacon
 */

declare(strict_types=1);

namespace Kayvandra\MarginBeacon\Infrastructure\Persistence;

/**
 * Repository for marginbeacon_acknowledgements.
 */
final class AcknowledgementsRepository {

	/**
	 * Active uniqueness key (NULL when revoked — multiple NULLs allowed).
	 *
	 * @param string $scope_type Scope type.
	 * @param string $scope_ref  Scope ref.
	 * @return string
	 */
	public static function active_key_for( $scope_type, $scope_ref ) {
		return sanitize_key( (string) $scope_type ) . ':' . (string) $scope_ref;
	}

	/**
	 * @param array $row Row.
	 * @return int Insert ID, or 0 on failure.
	 */
	public function insert( array $row ) {
		global $wpdb;
		if ( isset( $row['status'] ) && 'active' === $row['status']
			&& ! empty( $row['scope_type'] ) && ! empty( $row['scope_ref'] ) ) {
			$row['active_key'] = self::active_key_for( (string) $row['scope_type'], (string) $row['scope_ref'] );
		}
		$ok = $wpdb->insert( $wpdb->prefix . 'marginbeacon_acknowledgements', $row );
		if ( false === $ok ) {
			return 0;
		}
		return (int) $wpdb->insert_id;
	}

	/**
	 * @param int   $id   ID.
	 * @param array $data Fields.
	 * @return bool
	 */
	public function update( $id, array $data ) {
		global $wpdb;
		if ( isset( $data['status'] ) && 'revoked' === $data['status'] ) {
			$data['active_key'] = null;
		}
		$result = $wpdb->update(
			$wpdb->prefix . 'marginbeacon_acknowledgements',
			$data,
			array( 'id' => (int) $id )
		);
		return false !== $result;
	}

	/**
	 * Atomically supersede any active row for scope and insert the new acknowledgement.
	 *
	 * Uses MySQL GET_LOCK + transaction so concurrent acknowledges cannot leave two actives.
	 *
	 * @param string $scope_type Scope type.
	 * @param string $scope_ref  Scope ref.
	 * @param array  $new_row    New active row (without id).
	 * @return array{success:bool,id?:int,error?:string}
	 */
	public function supersede_and_insert( $scope_type, $scope_ref, array $new_row ) {
		global $wpdb;

		$lock_name = 'aksmb_ack_' . md5( (string) $scope_type . '|' . (string) $scope_ref );
		$got       = $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 5)', $lock_name ) );
		if ( '1' !== (string) $got ) {
			return array(
				'success' => false,
				'error'   => 'lock_timeout',
			);
		}

		$started = false;
		try {
			$begin = $wpdb->query( 'START TRANSACTION' );
			if ( false === $begin ) {
				return array(
					'success' => false,
					'error'   => 'transaction_failed',
				);
			}
			$started = true;

			$old = $this->find_active_any_revision( $scope_type, $scope_ref );
			if ( $old ) {
				$revoked = $this->update(
					(int) $old['id'],
					array(
						'status'         => 'revoked',
						'revoke_reason'  => 'superseded_by_new_acknowledgement',
						'revoked_at_gmt' => gmdate( 'Y-m-d H:i:s' ),
						'active_key'     => null,
					)
				);
				if ( ! $revoked ) {
					$wpdb->query( 'ROLLBACK' );
					$started = false;
					return array(
						'success' => false,
						'error'   => 'supersede_failed',
					);
				}
			}

			$new_row['status']     = 'active';
			$new_row['scope_type'] = $scope_type;
			$new_row['scope_ref']  = $scope_ref;
			$new_row['active_key'] = self::active_key_for( $scope_type, $scope_ref );

			$id = $this->insert( $new_row );
			if ( $id <= 0 ) {
				$wpdb->query( 'ROLLBACK' );
				$started = false;
				return array(
					'success' => false,
					'error'   => 'insert_failed',
				);
			}

			$committed = $wpdb->query( 'COMMIT' );
			$started   = false;
			if ( false === $committed ) {
				$wpdb->query( 'ROLLBACK' );
				return array(
					'success' => false,
					'error'   => 'commit_failed',
				);
			}
			return array(
				'success' => true,
				'id'      => $id,
			);
		} catch ( \Throwable $e ) {
			if ( $started ) {
				$wpdb->query( 'ROLLBACK' );
			}
			return array(
				'success' => false,
				'error'   => 'exception',
			);
		} finally {
			$wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
		}
	}

	/**
	 * Active acknowledgement for scope + revision.
	 *
	 * @param string $scope_type Scope type.
	 * @param string $scope_ref  Scope ref.
	 * @param string $revision   Revision hash (exact match).
	 * @return array|null
	 */
	public function find_active( $scope_type, $scope_ref, $revision ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_acknowledgements';
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$table} WHERE scope_type = %s AND scope_ref = %s AND revision_hash = %s AND status = 'active' ORDER BY id DESC LIMIT 1",
				$scope_type,
				$scope_ref,
				$revision
			),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * Any active acknowledgement for scope (revision may differ — caller validates).
	 *
	 * @param string $scope_type Scope type.
	 * @param string $scope_ref  Scope ref.
	 * @return array|null
	 */
	public function find_active_any_revision( $scope_type, $scope_ref ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_acknowledgements';
		$row   = $wpdb->get_row(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$table} WHERE scope_type = %s AND scope_ref = %s AND status = 'active' ORDER BY id DESC LIMIT 1",
				$scope_type,
				$scope_ref
			),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * @param int $id ID.
	 * @return array|null
	 */
	public function get( $id ) {
		global $wpdb;
		$row = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM ' . $wpdb->prefix . 'marginbeacon_acknowledgements WHERE id = %d',
				(int) $id
			),
			ARRAY_A
		);
		return $row ? $row : null;
	}

	/**
	 * @return int
	 */
	public function count_active() {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_acknowledgements';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table} WHERE status = 'active'" );
	}

	/**
	 * @param int $limit Limit.
	 * @return array
	 */
	public function list_active( $limit = 50 ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_acknowledgements';
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$table} WHERE status = 'active' ORDER BY created_at_gmt DESC LIMIT %d",
				max( 1, (int) $limit )
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Paginated active acknowledgements after a keyset id (avoids OFFSET skip after revoke).
	 *
	 * @param int $limit    Page size.
	 * @param int $after_id Exclusive lower bound (id > after_id).
	 * @return array
	 */
	public function list_active_after_id( $limit = 100, $after_id = 0 ) {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_acknowledgements';
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
				"SELECT * FROM {$table} WHERE status = 'active' AND id > %d ORDER BY id ASC LIMIT %d",
				max( 0, (int) $after_id ),
				max( 1, (int) $limit )
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : array();
	}
}