<?php
/**
 * Append-only, transaction-safe points ledger persistence.
 *
 * @package DevoynxRewards
 */

namespace Devoynx\Rewards\Database;

use WP_Error;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.DB.DirectDatabaseQuery -- This custom ledger requires fresh transaction/row-lock reads; cached balances would permit double spending.

/** Persists append-only movements and transactionally locked balance buckets. */
final class LedgerRepository {
	const BUCKETS = array( 'pending', 'available', 'reserved', 'redeemed', 'expired', 'reversed' );

	/**
	 * Atomically append one movement and update the materialized balance.
	 *
	 * Empty from/to buckets represent the external boundary.
	 *
	 * @param int       $user_id User ID.
	 * @param int       $points Positive integer point count.
	 * @param string    $from Source bucket or empty string.
	 * @param string    $to Destination bucket or empty string.
	 * @param array     $context Auditing context.
	 * @param bool|null $created Set true only when this call appended the row.
	 * @return int|WP_Error Ledger entry ID or error.
	 * @throws \RuntimeException When a database write fails and is rolled back.
	 */
	public function move( $user_id, $points, $from, $to, array $context, &$created = null ) {
		global $wpdb;
		$created = false;

		$user_id = absint( $user_id );
		$points  = absint( $points );
		$from    = sanitize_key( $from );
		$to      = sanitize_key( $to );

		if ( ! $user_id || ! $points || $from === $to || ( $from && ! in_array( $from, self::BUCKETS, true ) ) || ( $to && ! in_array( $to, self::BUCKETS, true ) ) ) {
			return new WP_Error( 'devoynx_rewards_invalid_movement', __( 'The requested points movement is invalid.', 'devoynx-rewards' ) );
		}

		$idempotency_key = isset( $context['idempotency_key'] ) ? sanitize_text_field( $context['idempotency_key'] ) : '';
		if ( '' === $idempotency_key || strlen( $idempotency_key ) > 191 ) {
			return new WP_Error( 'devoynx_rewards_invalid_idempotency_key', __( 'A valid idempotency key is required.', 'devoynx-rewards' ) );
		}

		$ledger_table   = $wpdb->prefix . 'devoynx_rewards_ledger';
		$balances_table = $wpdb->prefix . 'devoynx_rewards_balances';
		$now            = current_time( 'mysql', true );

		Transaction::begin();
		try {
			// Take an exclusive duplicate-key lock before reading the wallet row.
			$initialized = $wpdb->query(
				$wpdb->prepare(
					'INSERT INTO %i (user_id, updated_at) VALUES (%d, %s) ON DUPLICATE KEY UPDATE user_id = user_id',
					$balances_table,
					$user_id,
					$now
				)
			);
			if ( false === $initialized ) {
				throw new \RuntimeException( 'Could not initialize rewards wallet lock.' );
			}

			$balance = $wpdb->get_row(
				$wpdb->prepare( 'SELECT * FROM %i WHERE user_id = %d FOR UPDATE', $balances_table, $user_id ),
				ARRAY_A
			);
			if ( ! is_array( $balance ) ) {
				throw new \RuntimeException( 'Could not read the locked rewards wallet.' );
			}

			// A workflow may already have a repeatable-read snapshot from before it
			// waited for this wallet. Use a current read for the committed replay key.
			$existing = $wpdb->get_var(
				$wpdb->prepare( 'SELECT id FROM %i WHERE idempotency_key = %s FOR UPDATE', $ledger_table, $idempotency_key )
			);
			if ( $existing ) {
				Transaction::commit();
				return (int) $existing;
			}

			$validation = apply_filters( 'devoynx_rewards_validate_movement', true, $user_id, $points, $from, $to, $context );
			if ( is_wp_error( $validation ) || true !== $validation ) {
				Transaction::rollback();
				return is_wp_error( $validation ) ? $validation : new WP_Error( 'devoynx_rewards_movement_rejected', __( 'The points transaction was rejected by a configured limit.', 'devoynx-rewards' ) );
			}

			if ( $from && (int) $balance[ $from ] < $points ) {
				Transaction::rollback();
				return new WP_Error( 'devoynx_rewards_insufficient_points', __( 'The customer does not have enough points for this operation.', 'devoynx-rewards' ) );
			}

			if ( $from && $to ) {
				$updated = $wpdb->query( $wpdb->prepare( 'UPDATE %i SET %i = %i - %d, %i = %i + %d, version = version + 1, updated_at = %s WHERE user_id = %d', $balances_table, $from, $from, $points, $to, $to, $points, $now, $user_id ) );
			} elseif ( $from ) {
				$updated = $wpdb->query( $wpdb->prepare( 'UPDATE %i SET %i = %i - %d, version = version + 1, updated_at = %s WHERE user_id = %d', $balances_table, $from, $from, $points, $now, $user_id ) );
			} else {
				$updated = $wpdb->query( $wpdb->prepare( 'UPDATE %i SET %i = %i + %d, version = version + 1, updated_at = %s WHERE user_id = %d', $balances_table, $to, $to, $points, $now, $user_id ) );
			}
			if ( false === $updated ) {
				throw new \RuntimeException( 'Balance update failed.' );
			}

			$inserted = $wpdb->insert(
				$ledger_table,
				array(
					'user_id'           => $user_id,
					'points'            => $points,
					'from_bucket'       => $from,
					'to_bucket'         => $to,
					'event_type'        => sanitize_key( $context['event_type'] ?? 'adjustment' ),
					'source_type'       => sanitize_key( $context['source_type'] ?? 'system' ),
					'source_id'         => sanitize_text_field( (string) ( $context['source_id'] ?? '' ) ),
					'idempotency_key'   => $idempotency_key,
					'rule_id'           => sanitize_text_field( (string) ( $context['rule_id'] ?? '' ) ),
					'rule_version'      => absint( $context['rule_version'] ?? 0 ),
					'calculation_trace' => wp_json_encode( $context['trace'] ?? array() ),
					'expires_at'        => ! empty( $context['expires_at'] ) ? gmdate( 'Y-m-d H:i:s', strtotime( $context['expires_at'] ) ) : null,
					'created_at'        => $now,
				),
				array( '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s' )
			);

			if ( false === $inserted ) {
				throw new \RuntimeException( 'Ledger insert failed.' );
			}

			$entry_id = (int) $wpdb->insert_id;
			Transaction::commit();
			$created = true;
			return $entry_id;
		} catch ( \Throwable $error ) {
			Transaction::rollback();
			return new WP_Error( 'devoynx_rewards_ledger_write_failed', __( 'The points transaction could not be saved safely.', 'devoynx-rewards' ) );
		}
	}

	/**
	 * Get a customer's materialized balances.
	 *
	 * @param int $user_id User ID.
	 * @return array<string,int>
	 */
	public function get_balance( $user_id ) {
		global $wpdb;
		$table = $wpdb->prefix . 'devoynx_rewards_balances';
		$row   = $wpdb->get_row(
			$wpdb->prepare( 'SELECT pending, available, reserved, redeemed, expired, reversed FROM %i WHERE user_id = %d', $table, absint( $user_id ) ),
			ARRAY_A
		);

		$result = array_fill_keys( self::BUCKETS, 0 );
		foreach ( (array) $row as $bucket => $value ) {
			$result[ $bucket ] = (int) $value;
		}
		return $result;
	}

	/**
	 * Query customer or merchant-visible transactions.
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public function query( array $args = array() ) {
		global $wpdb;
		$table    = $wpdb->prefix . 'devoynx_rewards_ledger';
		$per_page = min( 100, max( 1, absint( $args['per_page'] ?? 20 ) ) );
		$page     = max( 1, absint( $args['page'] ?? 1 ) );
		$offset   = ( $page - 1 ) * $per_page;
		$user_id  = absint( $args['user_id'] ?? 0 );

		if ( $user_id ) {
			return $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i WHERE user_id = %d ORDER BY id DESC LIMIT %d OFFSET %d', $table, $user_id, $per_page, $offset ),
				ARRAY_A
			);
		}

		return $wpdb->get_results(
			$wpdb->prepare( 'SELECT * FROM %i ORDER BY id DESC LIMIT %d OFFSET %d', $table, $per_page, $offset ),
			ARRAY_A
		);
	}

	/**
	 * Verify the cached balance against the immutable ledger.
	 *
	 * @param int $user_id User ID.
	 * @return array{cached:array,calculated:array,matches:bool}
	 */
	public function reconcile( $user_id ) {
		global $wpdb;
		$table      = $wpdb->prefix . 'devoynx_rewards_ledger';
		$calculated = array_fill_keys( self::BUCKETS, 0 );

		foreach ( self::BUCKETS as $bucket ) {
			$calculated[ $bucket ] = (int) $wpdb->get_var(
				$wpdb->prepare(
					'SELECT COALESCE(SUM(CASE WHEN to_bucket = %s THEN points ELSE 0 END),0) - COALESCE(SUM(CASE WHEN from_bucket = %s THEN points ELSE 0 END),0) FROM %i WHERE user_id = %d',
					$bucket,
					$bucket,
					$table,
					absint( $user_id )
				)
			);
		}

		$cached = $this->get_balance( $user_id );
		return array(
			'cached'     => $cached,
			'calculated' => $calculated,
			'matches'    => $cached === $calculated,
		);
	}

	/**
	 * Return a bounded batch of FIFO expiry amounts.
	 *
	 * @param int $limit Maximum wallets in this batch.
	 */
	public function expiry_candidates( $limit = 100 ) {
		global $wpdb;
		$table = $wpdb->prefix . 'devoynx_rewards_ledger';
		$limit = min( 500, max( 1, absint( $limit ) ) );
		$rows  = $wpdb->get_results( $wpdb->prepare( 'SELECT user_id, SUM(CASE WHEN from_bucket=\'\' AND to_bucket=\'available\' AND expires_at IS NOT NULL AND expires_at<=%s THEN points ELSE 0 END)-SUM(CASE WHEN from_bucket=\'available\' AND to_bucket<>\'expired\' THEN points ELSE 0 END)+SUM(CASE WHEN to_bucket=\'available\' AND from_bucket<>\'\' THEN points ELSE 0 END)-SUM(CASE WHEN from_bucket=\'available\' AND to_bucket=\'expired\' THEN points ELSE 0 END) due_points FROM %i GROUP BY user_id HAVING due_points>0 ORDER BY user_id LIMIT %d', current_time( 'mysql', true ), $table, $limit ), ARRAY_A );
		return array_map(
			static function ( $row ) {
				return array(
					'user_id' => (int) $row['user_id'],
					'points'  => (int) $row['due_points'],
				);
			},
			$rows
		);
	}
}