<?php
/**
 * PostGate Orders Repository
 *
 * Provides data access layer for order management with CRUD operations for the
 * wp_postgate_orders table. Handles order lifecycle from creation through payment
 * completion, refunds, and subscription cancellations with object caching support.
 *
 * @package PostGate
 * @subpackage Database
 * @since 1.0.0
 * @author QodeSphere
 * @license GPL-2.0+
 */

defined( 'ABSPATH' ) || exit;

/**
 * PostGate Orders Repository
 *
 * This repository class manages all database operations for the orders table. It provides
 * a clean data access layer that abstracts direct database queries and implements caching
 * strategies for improved performance. Key responsibilities include:
 *
 * - Creating pending orders during checkout initiation
 * - Updating orders with Stripe identifiers (payment intent, session, subscription)
 * - Marking orders as paid, refunded, or canceled based on Stripe webhooks
 * - Retrieving orders by various identifiers (ID, session, intent, subscription)
 * - Object caching with 5-minute TTL for read operations
 * - Cache invalidation for write operations via dashboard cache flushing
 *
 * The repository follows a consistent pattern:
 * - Write operations (insert/update) invalidate related caches
 * - Read operations check cache before querying database
 * - All Stripe identifiers are sanitized before use in queries
 * - Prepared statements used throughout for SQL injection prevention
 *
 * Cache Strategy:
 * - Individual orders cached by ID, session_id, intent_id, and subscription_id
 * - 300-second (5-minute) TTL for object cache entries
 * - Dashboard statistics cache flushed on any order status change
 * - Cache keys use MD5 hashing for consistent identifier generation
 *
 * @since 1.0.0
 */
class PostGate_Orders_Repo {

	/**
	 * Object cache group.
	 *
	 * @since 1.0.0
	 */
	private const CACHE_GROUP = 'postgate_orders';

	/**
	 * Default cache TTL (seconds).
	 *
	 * @since 1.0.0
	 */
	private const CACHE_TTL = 300;

	/**
	 * Generate a cache key for order data.
	 *
	 * Creates a unique cache key by hashing the provided suffix to ensure
	 * consistent cache key generation across different order identifiers.
	 *
	 * @since 1.0.0
	 *
	 * @param string $suffix Unique identifier to append to the cache key.
	 *
	 * @return string The generated cache key.
	 */
	private static function cache_key( string $suffix ): string {
		return 'postgate_orders_' . md5( $suffix );
	}

	/**
	 * Conditionally flush dashboard caches when order data changes.
	 *
	 * Invalidates dashboard statistics caches to ensure metrics reflect the latest
	 * order data after updates. Only flushes if rows were actually affected.
	 *
	 * @since 1.0.0
	 *
	 * @param int $affected_rows Number of database rows affected by the operation.
	 *
	 * @return void
	 */
	private static function maybe_flush_dashboard_cache( int $affected_rows ): void {
		if ( $affected_rows > 0 ) {
			PostGate_Dashboard_Controller::flush_cache();
		}
	}

	/**
	 * Invalidate cache for a specific order identifier.
	 *
	 * Removes cached order data by a specific identifier type (session, intent, subscription).
	 * Helper method to ensure cache consistency after database updates.
	 *
	 * @since 1.0.0
	 *
	 * @param string $cache_prefix Cache key prefix (e.g., 'order_session_', 'order_intent_').
	 * @param string $identifier   The identifier value to invalidate.
	 *
	 * @return void
	 */
	private static function invalidate_cache_for_identifier( string $cache_prefix, string $identifier ): void {
		$cache_key = self::cache_key( $cache_prefix . $identifier );
		wp_cache_delete( $cache_key, self::CACHE_GROUP );
	}

	/**
	 * Generic method to retrieve order by a specific field.
	 *
	 * Provides DRY implementation for all get_by_*() methods. Queries database
	 * with caching support for any field-value pair.
	 *
	 * @since 1.0.0
	 *
	 * @param string $field        Database column name to query.
	 * @param string $value        Value to match in the specified field.
	 * @param string $cache_prefix Cache key prefix for this identifier type.
	 *
	 * @return array|null Order data as associative array, or null if not found.
	 */
	private static function get_by_field( string $field, string $value, string $cache_prefix ): ?array {
		global $wpdb;

		$table = $wpdb->prefix . 'postgate_orders';
		$value = sanitize_text_field( $value );

		$cache_key = self::cache_key( $cache_prefix . $value );
		$row = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false === $row ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$row = $wpdb->get_row(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE %i = %s',
					$table,
					$field,
					$value
				),
				ARRAY_A
			);
			wp_cache_set( $cache_key, $row, self::CACHE_GROUP, self::CACHE_TTL );
		}

		return $row ?: null;
	}

	/**
	 * Insert a new pending order record.
	 *
	 * Creates a new order in the database with 'pending' status during checkout initiation.
	 * This is typically called when a Stripe Checkout session is created but before payment
	 * completion. The order will be updated to 'paid' status via webhook after successful payment.
	 *
	 * Required data keys:
	 * - user_id: WordPress user ID (required, must be > 0)
	 *
	 * Optional data keys with defaults:
	 * - stripe_account_id, type, status, currency, amount_cents, fee_cents, fee_percent,
	 *   recurring_interval, stripe_payment_intent_id, stripe_checkout_session_id,
	 *   stripe_subscription_id, stripe_customer_id, purchased_price_id, purchased_product_id,
	 *   product_name, unlocked_content, origin_post_id
	 *
	 * @since 1.0.0
	 *
	 * @param array $data Associative array of order data to insert.
	 *
	 * @return int|false The new order ID on success, false on failure.
	 */
	public static function insert_pending_order( array $data ) {
		global $wpdb;

		$table = $wpdb->prefix . 'postgate_orders';

		$defaults = [
			'user_id' => 0,
			'stripe_account_id' => '',
			'type' => 'one_time',
			'status' => 'pending',
			'currency' => 'usd',
			'amount_cents' => 0,
			'fee_cents' => 0,
			'fee_percent' => null,
			'recurring_interval' => null,
			'stripe_payment_intent_id' => null,
			'stripe_checkout_session_id' => null,
			'stripe_subscription_id' => null,
			'stripe_customer_id' => null,
			'purchased_price_id' => null,
			'purchased_product_id' => null,
			'product_name' => null,
			'unlocked_content' => null,
			'origin_post_id' => null,
		];

		$data = wp_parse_args( $data, $defaults );

		if ( empty( $data['user_id'] ) ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert( $table, $data );

		if ( false === $result ) {
			return false;
		}

		$order_id = (int) $wpdb->insert_id;

		// Cache the newly created order by session_id to prevent NULL cache pollution
		if ( ! empty( $data['stripe_checkout_session_id'] ) ) {
			$cache_key = self::cache_key( 'order_session_' . $data['stripe_checkout_session_id'] );
			// Fetch the complete order data to cache it properly
			$fresh_order = self::get_by_id( $order_id );
			if ( $fresh_order ) {
				wp_cache_set( $cache_key, $fresh_order, self::CACHE_GROUP, self::CACHE_TTL );
			}
		}

		return $order_id;
	}

	/**
	 * Update order with payment intent ID by checkout session ID.
	 *
	 * Associates a Stripe payment intent with an existing order identified by its
	 * checkout session ID. Only updates if the payment intent field is currently empty.
	 * This is typically called during the checkout flow when the intent is finalized.
	 *
	 * @since 1.0.0
	 *
	 * @param string $session_id Stripe checkout session ID.
	 * @param string $intent_id  Stripe payment intent ID.
	 *
	 * @return int Number of rows updated (0 or 1).
	 */
	public static function insert_intent_by_session( string $session_id, string $intent_id ): int {
		global $wpdb;

		$table = $wpdb->prefix . 'postgate_orders';
		$session_id = sanitize_text_field( $session_id );
		$intent_id = sanitize_text_field( $intent_id );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->query(
			$wpdb->prepare(
				'UPDATE %i
				 SET stripe_payment_intent_id = %s
				 WHERE stripe_checkout_session_id = %s
				   AND ( stripe_payment_intent_id IS NULL OR stripe_payment_intent_id = "" )',
				$table,
				$intent_id,
				$session_id
			)
		);

		$affected = false !== $result ? (int) $result : 0;

		// Invalidate cache for updated order
		if ( $affected > 0 ) {
			self::invalidate_cache_for_identifier( 'order_session_', $session_id );
			self::invalidate_cache_for_identifier( 'order_intent_', $intent_id );
		}

		return $affected;
	}

	/**
	 * Mark order as paid by session, intent, or subscription ID.
	 *
	 * Updates order status to 'paid' when payment is confirmed via Stripe webhook.
	 * Matches orders by checkout session, payment intent, or subscription ID and
	 * updates the customer ID and (optionally) payment intent if not already set.
	 *
	 * This method supports three identification strategies (priority order):
	 * 1. Checkout session ID (for session-based checkout)
	 * 2. Payment intent ID (for direct payment intent flows)
	 * 3. Subscription ID (for recurring subscription payments)
	 *
	 * At least one identifier must be provided. Flushes dashboard cache on success.
	 *
	 * @since 1.0.0
	 *
	 * @param string|null $session_id      Stripe checkout session ID or null.
	 * @param string|null $intent_id       Stripe payment intent ID or null.
	 * @param string|null $subscription_id Stripe subscription ID or null.
	 * @param string|null $customer_id     Stripe customer ID to associate with order.
	 *
	 * @return int Number of rows updated.
	 */
	public static function mark_paid_by_session_or_intent(
		?string $session_id,
		?string $intent_id,
		?string $subscription_id,
		?string $customer_id
	): int {
		global $wpdb;

		$table = $wpdb->prefix . 'postgate_orders';

		$has_session = ! empty( $session_id );
		$has_intent = ! empty( $intent_id );
		$has_sub = ! empty( $subscription_id );

		if ( ! $has_session && ! $has_intent && ! $has_sub ) {
			return 0;
		}

		$session_id = $has_session ? (string) $session_id : '';
		$intent_id = $has_intent ? (string) $intent_id : '';
		$subscription_id = $has_sub ? (string) $subscription_id : '';
		$customer_id = (string) $customer_id;

		// Write query; cache invalidation happens via dashboard flush and related deletes.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->query(
			$wpdb->prepare(
				'UPDATE %i
				 SET status = %s,
				     stripe_customer_id = %s,
				     stripe_payment_intent_id = CASE
				         WHEN %d = 1 THEN COALESCE( NULLIF( stripe_payment_intent_id, "" ), %s )
				         ELSE stripe_payment_intent_id
				     END
				 WHERE ( ( %d = 1 AND stripe_checkout_session_id = %s )
				      OR ( %d = 1 AND stripe_payment_intent_id = %s )
				      OR ( %d = 1 AND stripe_subscription_id = %s ) )',
				$table,
				'paid',
				$customer_id,
				$has_intent ? 1 : 0,
				$intent_id,
				$has_session ? 1 : 0,
				$session_id,
				$has_intent ? 1 : 0,
				$intent_id,
				$has_sub ? 1 : 0,
				$subscription_id
			)
		);

		$affected = false !== $result ? (int) $result : 0;

		// Invalidate caches for all provided identifiers to ensure fresh data on next read
		if ( $affected > 0 ) {
			if ( $has_session ) {
				self::invalidate_cache_for_identifier( 'order_session_', $session_id );
			}
			if ( $has_intent ) {
				self::invalidate_cache_for_identifier( 'order_intent_', $intent_id );
			}
			if ( $has_sub ) {
				self::invalidate_cache_for_identifier( 'order_subscription_', $subscription_id );
			}
		}

		self::maybe_flush_dashboard_cache( $affected );

		return $affected;
	}

	/**
	 * Mark order as refunded by payment intent ID.
	 *
	 * Updates order status to 'refunded' when a refund is processed via Stripe.
	 * Typically called from webhook handler when refund.created event is received.
	 * Flushes dashboard cache to update revenue statistics.
	 *
	 * @since 1.0.0
	 *
	 * @param string $intent_id Stripe payment intent ID of the refunded payment.
	 *
	 * @return int Number of rows updated.
	 */
	public static function mark_refunded_by_intent( string $intent_id ): int {
		global $wpdb;

		$table = $wpdb->prefix . 'postgate_orders';
		$intent_id = sanitize_text_field( $intent_id );

		// Write query; cache invalidation happens via dashboard flush and related deletes.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			$table,
			[ 'status' => 'refunded' ],
			[ 'stripe_payment_intent_id' => $intent_id ],
			[ '%s' ],
			[ '%s' ]
		);

		$affected = false !== $result ? (int) $result : 0;

		// Invalidate cache for this payment intent after status update
		if ( $affected > 0 ) {
			self::invalidate_cache_for_identifier( 'order_intent_', $intent_id );
		}

		self::maybe_flush_dashboard_cache( $affected );

		return $affected;
	}

	/**
	 * Mark subscription order as canceled.
	 *
	 * Updates subscription order status to 'canceled' when a subscription is terminated.
	 * Only affects orders with type='subscription' and matching subscription ID.
	 * Typically called from webhook handler or manual cancellation endpoint.
	 * Flushes dashboard cache to update subscription metrics.
	 *
	 * @since 1.0.0
	 *
	 * @param string $subscription_id Stripe subscription ID of the canceled subscription.
	 *
	 * @return int Number of rows updated.
	 */
	public static function mark_canceled_subscription( string $subscription_id ): int {
		global $wpdb;

		$table = $wpdb->prefix . 'postgate_orders';
		$subscription_id = sanitize_text_field( $subscription_id );

		// Write query; cache invalidation happens via dashboard flush and related deletes.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			$table,
			[ 'status' => 'canceled' ],
			[
				'stripe_subscription_id' => $subscription_id,
				'type' => 'subscription',
			],
			[ '%s' ],
			[ '%s', '%s' ]
		);

		$affected = false !== $result ? (int) $result : 0;

		// Invalidate cache for this subscription after status update
		if ( $affected > 0 ) {
			self::invalidate_cache_for_identifier( 'order_subscription_', $subscription_id );
		}

		self::maybe_flush_dashboard_cache( $affected );

		return $affected;
	}

	/**
	 * Retrieve order by order ID.
	 *
	 * Fetches a single order record by its database primary key. Results are cached
	 * for 5 minutes to reduce database queries on repeated lookups.
	 *
	 * @since 1.0.0
	 *
	 * @param int $order_id The order's database ID.
	 *
	 * @return array|null Order data as associative array, or null if not found.
	 */
	public static function get_by_id( int $order_id ): ?array {
		global $wpdb;

		$table = $wpdb->prefix . 'postgate_orders';

		$cache_key = self::cache_key( 'order_id_' . $order_id );
		$row = wp_cache_get( $cache_key, self::CACHE_GROUP );

		if ( false === $row ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$row = $wpdb->get_row(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE id = %d',
					$table,
					$order_id
				),
				ARRAY_A
			);
			wp_cache_set( $cache_key, $row, self::CACHE_GROUP, self::CACHE_TTL );
		}

		return $row ?: null;
	}

	/**
	 * Retrieve order by Stripe checkout session ID.
	 *
	 * Fetches order associated with a specific Stripe checkout session. Used during
	 * checkout completion and webhook processing. Results are cached for 5 minutes.
	 *
	 * @since 1.0.0
	 *
	 * @param string $session_id Stripe checkout session ID.
	 *
	 * @return array|null Order data as associative array, or null if not found.
	 */
	public static function get_by_session_id( string $session_id ): ?array {
		return self::get_by_field( 'stripe_checkout_session_id', $session_id, 'order_session_' );
	}

	/**
	 * Retrieve order by Stripe payment intent ID.
	 *
	 * Fetches order associated with a specific Stripe payment intent. Used for
	 * payment confirmation and refund processing. Results are cached for 5 minutes.
	 *
	 * @since 1.0.0
	 *
	 * @param string $intent_id Stripe payment intent ID.
	 *
	 * @return array|null Order data as associative array, or null if not found.
	 */
	public static function get_by_intent_id( string $intent_id ): ?array {
		return self::get_by_field( 'stripe_payment_intent_id', $intent_id, 'order_intent_' );
	}

	/**
	 * Retrieve order by Stripe subscription ID.
	 *
	 * Fetches subscription order associated with a specific Stripe subscription.
	 * Used for recurring payment processing and subscription management.
	 * Results are cached for 5 minutes.
	 *
	 * @since 1.0.0
	 *
	 * @param string $subscription_id Stripe subscription ID.
	 *
	 * @return array|null Order data as associative array, or null if not found.
	 */
	public static function get_by_subscription_id( string $subscription_id ): ?array {
		return self::get_by_field( 'stripe_subscription_id', $subscription_id, 'order_subscription_' );
	}
}
