<?php
/**
 * Durable, transactional workflow checkpoints; never an alternative points balance.
 *
 * @package DevoynxRewards
 */

namespace Devoynx\Rewards\Database;

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery -- Uncached reads and row locks are required for durable workflow idempotency.

/** Transactional records for resumable reward programs. */
final class ProgramStore {

	/**
	 * Read a bounded queue using the indexed kind/status/id key.
	 *
	 * @param string $kind Record kind.
	 * @param string $status Queue state.
	 * @param int    $limit Maximum page size.
	 * @return array
	 */
	public function pending( $kind, $status = 'queued', $limit = 10 ) {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE kind = %s AND status = %s ORDER BY id LIMIT %d', $wpdb->prefix . 'devoynx_rewards_program_records', $kind, $status, min( 100, max( 1, absint( $limit ) ) ) ), ARRAY_A );
	}

	/** Create the indexed workflow store without changing RC3 ledger schema or rows. */
	public static function maybe_upgrade() {
		if ( '1' === get_option( 'devoynx_rewards_program_schema' ) ) {
			return;
		}
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$table   = $wpdb->prefix . 'devoynx_rewards_program_records';
		$charset = $wpdb->get_charset_collate();
		dbDelta(
			"CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			event_key varchar(191) NOT NULL,
			kind varchar(40) NOT NULL,
			group_key varchar(100) NOT NULL DEFAULT '',
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			order_id bigint(20) unsigned NOT NULL DEFAULT 0,
			status varchar(30) NOT NULL DEFAULT 'new',
			payload longtext NOT NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY event_key (event_key),
			KEY workflow (kind,group_key,id),
			KEY customer (user_id,kind,id),
			KEY order_lookup (order_id,kind),
			KEY pending (kind,status,id)
		) ENGINE=InnoDB {$charset};"
		);
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) === $table ) {
			update_option( 'devoynx_rewards_program_schema', '1', false );
		}
	}

	/**
	 * Find a workflow by its immutable key.
	 *
	 * @param string $key Workflow key.
	 * @return array|null
	 */
	public function find( $key ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE event_key = %s', $wpdb->prefix . 'devoynx_rewards_program_records', $key ), ARRAY_A );
	}

	/**
	 * Serialize a workflow with any nested ledger writes in one transaction.
	 *
	 * @param string   $key Immutable workflow key.
	 * @param string   $kind Record kind.
	 * @param callable $callback Transactional operation.
	 * @return mixed
	 * @throws \InvalidArgumentException When the key is invalid.
	 * @throws \RuntimeException When a checkpoint cannot be created or locked.
	 * @throws \Throwable When the operation fails; all changes roll back.
	 */
	public function locked( $key, $kind, callable $callback ) {
		global $wpdb;
		if ( ! $key || strlen( $key ) > 191 ) {
			throw new \InvalidArgumentException( 'Invalid workflow key.' );
		}
		$table = $wpdb->prefix . 'devoynx_rewards_program_records';
		Transaction::begin();
		try {
			$now = current_time( 'mysql', true );
			// Acquire an exclusive duplicate-key lock immediately. INSERT IGNORE would
			// first take shared locks, letting simultaneous readers deadlock on upgrade.
			if ( false === $wpdb->query( $wpdb->prepare( "INSERT INTO %i (event_key,kind,payload,created_at,updated_at) VALUES (%s,%s,'{}',%s,%s) ON DUPLICATE KEY UPDATE id = id", $table, $key, $kind, $now, $now ) ) ) {
				throw new \RuntimeException( 'Could not create workflow checkpoint.' );
			}
			$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE event_key = %s FOR UPDATE', $table, $key ), ARRAY_A );
			if ( ! $row || $kind !== $row['kind'] ) {
				throw new \RuntimeException( 'Workflow lock unavailable.' );
			}
			$result = $callback( $row );
			if ( is_wp_error( $result ) ) {
				Transaction::rollback();
				return $result;
			}
			Transaction::commit();
			return $result;
		} catch ( \Throwable $error ) {
			Transaction::rollback();
			throw $error;
		}
	}

	/**
	 * Update a locked record using a fixed field allowlist.
	 *
	 * @param array  $row Locked record.
	 * @param array  $data JSON payload.
	 * @param string $status Workflow status.
	 * @param array  $identity Optional indexed identities.
	 * @return array
	 * @throws \RuntimeException When persistence fails.
	 */
	public function save( $row, array $data, $status, array $identity = array() ) {
		global $wpdb;
		$values = array(
			'payload'    => wp_json_encode( $data ),
			'status'     => sanitize_key( $status ),
			'updated_at' => current_time( 'mysql', true ),
		);
		foreach ( array( 'user_id', 'order_id', 'group_key' ) as $field ) {
			if ( isset( $identity[ $field ] ) ) {
				$values[ $field ] = 'group_key' === $field ? sanitize_text_field( $identity[ $field ] ) : absint( $identity[ $field ] );
			}
		}
		if ( false === $wpdb->update( $wpdb->prefix . 'devoynx_rewards_program_records', $values, array( 'id' => $row['id'] ) ) ) {
			throw new \RuntimeException( 'Could not save workflow checkpoint.' );
		}
		return $this->find( $row['event_key'] );
	}

	/**
	 * Read a bounded page without acquiring a mutation lock.
	 *
	 * @param string $kind Record kind.
	 * @param string $group Workflow group.
	 * @param int    $after Exclusive record cursor.
	 * @param int    $limit Maximum page size.
	 * @param int    $user_id Optional customer filter.
	 * @return array
	 */
	public function page( $kind, $group = '', $after = 0, $limit = 25, $user_id = 0 ) {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE kind = %s AND group_key = %s AND id > %d AND (%d = 0 OR user_id = %d) ORDER BY id ASC LIMIT %d', $wpdb->prefix . 'devoynx_rewards_program_records', $kind, $group, absint( $after ), absint( $user_id ), absint( $user_id ), min( 100, max( 1, absint( $limit ) ) ) ), ARRAY_A );
	}
}