<?php
/**
 * Abandoned Cart storage helper.
 *
 * @package EDD\Pro\CartRecovery\Carts
 * @copyright   Copyright (c) 2026 Sandhills Development, LLC
 * @license     https://opensource.org/licenses/gpl-2.0.php GNU Public License
 * @since   3.6.5
 */

namespace EDD\Pro\CartRecovery\Carts;

use EDD\Pro\CartRecovery\Carts\Cart;
use EDD\Pro\CartRecovery\Config\Security;
use EDD\Pro\CartRecovery\Database\Queries\RecoveryCart as Query;
use EDD\Pro\CartRecovery\Traits\Debug;
use EDD\Pro\CartRecovery\Traits\Status;
use EDD\Reports;
use wpdb;

// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

/**
 * Persists cart snapshots into the abandoned cart table.
 *
 * @since 3.6.5
 */
final class Repository {
	use Debug;
	use Status;

	/**
	 * Table name including WordPress prefix.
	 *
	 * @since 3.6.5
	 * @var string
	 */
	private $table_name;

	/**
	 * Database handler.
	 *
	 * @since 3.6.5
	 * @var wpdb
	 */
	private $wpdb;

	/**
	 * Constructor.
	 *
	 * @since 3.6.5
	 */
	public function __construct() {
		global $wpdb;

		$this->wpdb       = $wpdb;
		$this->table_name = $wpdb->prefix . 'edd_acr_carts';
	}

	/**
	 * Executes a callback within a guarded database transaction.
	 *
	 * @since 3.6.5
	 * @param callable $callback Callback that receives the repository instance.
	 * @return mixed Callback return value.
	 * @throws \Throwable When the callback throws.
	 */
	public function with_transaction( callable $callback ) {
		if ( ! $this->wpdb ) {
			$this->log( 'CartRepository transaction skipped: database unavailable.', true );
			return null;
		}

		$started = $this->wpdb->query( 'START TRANSACTION' );

		if ( false === $started ) {
			$error = isset( $this->wpdb->last_error ) ? trim( (string) $this->wpdb->last_error ) : '';
			$this->log( 'CartRepository transaction start failed: ' . $error, true );
			return null;
		}

		try {
			$result = $callback( $this );
			$this->wpdb->query( 'COMMIT' );
			return $result;
		} catch ( \Throwable $e ) {
			$this->wpdb->query( 'ROLLBACK' );
			$this->log( 'CartRepository transaction failed: ' . $e->getMessage(), true );
			throw $e;
		}
	}

	/**
	 * Inserts or updates a cart snapshot with race condition protection.
	 *
	 * Uses database transactions and row-level locking to prevent race conditions
	 * during duplicate detection and cart merging operations.
	 *
	 * SECURITY NOTE: Security is implemented at the controller layer:
	 * - Loader::ingest() method validates nonce before calling upsert().
	 * - Recovery::handle_recovery_actions() validates recovery tokens and applies rate limiting.
	 * - All other calls come from WordPress hooks (server-side events).
	 *
	 * @since 3.6.5
	 * @param array $attributes           Cart snapshot attributes.
	 * @param bool  $manage_transaction Whether this method should manage its own transaction.
	 * @throws \Exception If the database operation fails.
	 * @throws \Throwable If the transaction fails.
	 * @return int|null Primary key of the cart row or null on failure.
	 */
	public function upsert( array $attributes, $manage_transaction = true ) {
		if ( empty( $attributes ) ) {
			return null;
		}

		if ( ! $this->wpdb ) {
			return null;
		}

		$normalised = $this->normalise_attributes( $attributes );

		// Require valid identifiers after security validation to prevent processing corrupted data.
		if ( empty( $normalised['session_id'] ) && empty( $normalised['customer_id'] ) && empty( $normalised['email'] ) ) {
			return null;
		}

		// Start database transaction to prevent race conditions in duplicate detection.
		$transaction_started = false;

		if ( $manage_transaction ) {
			$start_result = $this->wpdb->query( 'START TRANSACTION' );

			if ( false === $start_result ) {
				$error = isset( $this->wpdb->last_error ) ? trim( (string) $this->wpdb->last_error ) : '';
				$this->log( 'Cart upsert aborted: failed to start transaction. ' . $error, true );
				return null;
			}

			$transaction_started = true;
		}

		try {
			// If a specific cart ID is provided, retrieve it directly first.
			// This ensures we update the correct cart even if session ID changes.
			$existing         = null;
			$duplicates       = array();
			$incoming_version = isset( $normalised['cart_version'] ) ? (int) $normalised['cart_version'] : null;
			$now              = edd_get_utc_date_string();

			if ( isset( $attributes['id'] ) && ! empty( $attributes['id'] ) ) {
				$cart_id = (int) $attributes['id'];
				$query   = new Query();
				$cart    = $query->get_item( $cart_id );

				if ( $cart && ! empty( $cart->id ) ) {
					// Verify this cart can still be updated (not expired/recovered).
					if ( in_array( $cart->status, $this->get_updateable_statuses(), true ) ) {
						$existing = $cart;

						// Still check for duplicates based on other identifiers.
						$related    = $this->locate_existing_carts( $normalised );
						$duplicates = $related['duplicates'];

						// Remove the primary cart from duplicates if it matches our existing cart.
						if ( ! empty( $related['primary'] ) && (int) $related['primary']->id !== (int) $existing->id ) {
							$duplicates[] = $related['primary'];
						}
					}
				}
			}

			// If no cart ID was provided or the cart wasn't found, locate by identifiers.
			if ( ! $existing ) {
				$related    = $this->locate_existing_carts( $normalised );
				$existing   = $related['primary'];
				$duplicates = $related['duplicates'];

				if ( ! $existing && ! empty( $duplicates ) ) {
					$existing = array_shift( $duplicates );
				}
			}

			// A recovered cart should not be overwritten; start a fresh row instead.
			if ( $existing && 'recovered' === $existing->status ) {
				$existing   = null;
				$duplicates = array();
			}

			if ( $existing ) {
				$current_version = isset( $existing->cart_version ) ? (int) $existing->cart_version : 0;

				if ( null !== $incoming_version && $incoming_version < $current_version ) {
					if ( ! empty( $duplicates ) ) {
						$this->expire_duplicate_carts( (int) $existing->id, $duplicates );
					}
					if ( $manage_transaction ) {
						$this->wpdb->query( 'COMMIT' );
					}
					return (int) $existing->id;
				}

				if ( ! empty( $duplicates ) ) {
					$normalised = $this->merge_duplicate_carts( $normalised, $existing, $duplicates );
				}

				$data = $this->prepare_update_payload( $normalised, $existing, $now );

				$updated = $this->wpdb->update(
					$this->table_name,
					$data['values'],
					array( 'id' => (int) $existing->id ),
					$data['formats'],
					array( '%d' )
				);

				if ( false === $updated ) {
					throw new \Exception( 'Database update failed during cart upsert' );
				}

				if ( ! empty( $duplicates ) ) {
					$this->expire_duplicate_carts( (int) $existing->id, $duplicates );
				}

				if ( $manage_transaction ) {
					$this->wpdb->query( 'COMMIT' );
					$transaction_started = false;
				}

				return (int) $existing->id;
			}

			$data = $this->prepare_insert_payload( $normalised, $now );

			$inserted = $this->wpdb->insert(
				$this->table_name,
				$data['values'],
				$data['formats']
			);

			if ( false === $inserted ) {
				throw new \Exception( 'Database insert failed during cart upsert' );
			}

			if ( ! ( $this->wpdb instanceof wpdb ) ) {
				throw new \Exception( 'Database handler unavailable after cart insert operation' );
			}

			$insert_id = isset( $this->wpdb->insert_id ) ? (int) $this->wpdb->insert_id : 0;

			if ( $insert_id <= 0 ) {
				throw new \Exception( 'Cart insert succeeded without returning a valid identifier' );
			}

			if ( $manage_transaction ) {
				$this->wpdb->query( 'COMMIT' );
				$transaction_started = false;
			}

			return $insert_id;

		} catch ( \Throwable $e ) {
			// Rollback transaction on any failure to prevent partial updates.
			if ( $transaction_started ) {
				$this->wpdb->query( 'ROLLBACK' );
				$transaction_started = false;
			}

			$this->log( 'Cart upsert transaction failed: ' . $e->getMessage() . ' Stack: ' . $e->getTraceAsString(), true );

			if ( $manage_transaction ) {
				return null;
			}

			throw $e;
		} finally {
			if ( $transaction_started ) {
				$this->wpdb->query( 'ROLLBACK' );
			}
		}
	}

	/**
	 * Finds all carts matching the given sets of identifiers in a single query.
	 *
	 * @since 3.6.5
	 * @param array $session_ids  Array of session IDs.
	 * @param array $customer_ids Array of customer IDs.
	 * @param array $emails       Array of email addresses.
	 * @return array Array of found cart objects.
	 */
	public function find_carts_by_identifiers_bulk( array $session_ids, array $customer_ids, array $emails ): array {
		if ( empty( $session_ids ) && empty( $customer_ids ) && empty( $emails ) ) {
			return array();
		}

		if ( ! $this->wpdb ) {
			return array();
		}

		$where_clauses = array();
		$params        = array();

		// Build WHERE clauses for each identifier type.
		if ( ! empty( $session_ids ) ) {
			$placeholders    = implode( ', ', array_fill( 0, count( $session_ids ), '%s' ) );
			$where_clauses[] = "session_id IN ({$placeholders})";
			$params          = array_merge( $params, $session_ids );
		}

		if ( ! empty( $customer_ids ) ) {
			$placeholders    = implode( ', ', array_fill( 0, count( $customer_ids ), '%d' ) );
			$where_clauses[] = "customer_id IN ({$placeholders})";
			$params          = array_merge( $params, $customer_ids );
		}

		if ( ! empty( $emails ) ) {
			$placeholders    = implode( ', ', array_fill( 0, count( $emails ), '%s' ) );
			$where_clauses[] = "email IN ({$placeholders})";
			$params          = array_merge( $params, $emails );
		}

		$where_sql = implode( ' OR ', $where_clauses );

		// We only want active, recoverable carts.
		$valid_statuses = array_values(
			array_filter(
				(array) array_keys( self::get_cart_statuses() ),
				static function ( $status ): bool {
					return '' !== (string) $status;
				}
			)
		);

		if ( empty( $valid_statuses ) ) {
			return array(); // No valid statuses means no carts can match.
		}

		$status_placeholders = implode( ', ', array_fill( 0, count( $valid_statuses ), '%s' ) );
		$params              = array_merge( $params, $valid_statuses );

		$sql = "SELECT * FROM {$this->table_name} WHERE ({$where_sql}) AND status IN ({$status_placeholders})";

		$prepared_sql = $this->wpdb->prepare( $sql, ...$params );

		if ( false === $prepared_sql ) {
			$error = isset( $this->wpdb->last_error ) ? $this->wpdb->last_error : '';
			$this->log( sprintf( 'Failed to prepare cart identifier bulk query (%d parameters): %s', count( $params ), $error ), true );
			return array();
		}

		return $this->wpdb->get_results( $prepared_sql );
	}

	/**
	 * Retrieves the primary cart matching provided identifiers.
	 *
	 * This is a read-only lookup that does not acquire row locks. For operations
	 * that need to modify the cart atomically, use upsert() which handles
	 * transactions and locking internally.
	 *
	 * @since 3.6.5
	 * @param array $attributes Cart attributes containing identifiers.
	 * @return object|null
	 */
	public function find_primary_cart( array $attributes ) {
		if ( empty( $attributes ) || ! ( $this->wpdb instanceof wpdb ) ) {
			return null;
		}

		$identity = array();

		if ( isset( $attributes['session_id'] ) ) {
			$identity['session_id'] = $attributes['session_id'];
		}

		if ( isset( $attributes['customer_id'] ) ) {
			$identity['customer_id'] = $attributes['customer_id'];
		}

		if ( isset( $attributes['email'] ) ) {
			$identity['email'] = $attributes['email'];
		}

		if ( empty( $identity ) ) {
			return null;
		}

		$normalised = $this->normalise_attributes( $identity );

		if ( empty( $normalised['session_id'] ) && empty( $normalised['customer_id'] ) && empty( $normalised['email'] ) ) {
			return null;
		}

		$related = $this->locate_existing_carts( $normalised, false );

		return $related['primary'];
	}

	/**
	 * Gets carts for admin listing with pagination and filtering.
	 *
	 * @since 3.6.5
	 * @param array $args {
	 *     Optional. Query arguments.
	 *
	 *     @type int    $page        Page number (1-based).
	 *     @type int    $per_page    Items per page.
	 *     @type string $status      Filter by status.
	 *     @type string $search      Search term for email/customer.
	 *     @type string $date_from   Start date filter.
	 *     @type string $date_to     End date filter.
	 *     @type string $orderby     Order by field.
	 *     @type string $order       Order direction (ASC/DESC).
	 *     @type float  $value_min   Minimum cart value.
	 *     @type float  $value_max   Maximum cart value.
	 * }
	 * @return array {
	 *     @type array $items    Cart items.
	 *     @type int   $total    Total items found.
	 *     @type int   $pages    Total pages.
	 * }
	 */
	public function get_carts_for_admin( array $args = array() ): array {

		// Build base query args with filters.
		$query_args            = $this->build_filter_query_args( $args );
		$per_page              = max( 1, (int) $args['per_page'] ?? 20 );
		$page                  = max( 1, (int) $args['page'] ?? 1 );
		$offset                = ( $page - 1 ) * $per_page;
		$query_args['number']  = $per_page;
		$query_args['offset']  = $offset;
		$query_args['orderby'] = $query_args['orderby'] ?? 'date_modified';
		$query_args['order']   = $query_args['order'] ?? 'DESC';

		$query = new Query();
		$items = $query->query( $query_args );

		$total_args = $query_args;
		unset( $total_args['number'], $total_args['offset'] );
		$total_args['count'] = true;

		$total_query = new Query( $total_args );
		$total_count = $total_query->found_items;

		return array(
			'items' => $items,
			'total' => $total_count,
			'pages' => (int) ceil( $total_count / $per_page ),
		);
	}

	/**
	 * Gets count of carts matching the given criteria.
	 *
	 * @since 3.6.5
	 * @param array $args Query arguments.
	 * @return int Cart count.
	 */
	public function get_carts_count( array $args = array() ): int {

		// Build query args with filters.
		$query_args          = $this->build_filter_query_args( $args );
		$query_args['count'] = true;

		$query = new Query( $query_args );

		return $query->found_items;
	}

	/**
	 * Builds query args from filter parameters.
	 *
	 * Extracts common filtering logic for status, search, date range,
	 * and cart value filtering used by both admin listing and counting.
	 *
	 * @since 3.6.5
	 * @param array $args Filter arguments.
	 * @return array Query arguments for the Query class.
	 */
	private function build_filter_query_args( array $args ): array {
		$defaults   = array(
			'status'    => '',
			'search'    => '',
			'date_from' => '',
			'date_to'   => '',
			'value_min' => null,
			'value_max' => null,
		);
		$parsed     = wp_parse_args( $args, $defaults );
		$query_args = array();

		// Status filter.
		if ( ! empty( $parsed['status'] ) ) {
			$query_args['status'] = $parsed['status'];
		}

		// Search filter.
		if ( ! empty( $parsed['search'] ) ) {
			$search                       = sanitize_text_field( $parsed['search'] );
			$query_args['search']         = $search;
			$query_args['search_columns'] = array( 'email', 'session_id' );
		}

		if ( isset( $parsed['cart_id'] ) ) {
			$query_args['id'] = (int) $parsed['cart_id'];
		}

		// Date range filter.
		if ( ! empty( $parsed['date_from'] ) || ! empty( $parsed['date_to'] ) ) {
			$date_query = array(
				'after'     => '',
				'before'    => '',
				'inclusive' => true,
			);

			if ( ! empty( $parsed['date_from'] ) ) {
				$date_query['after'] = edd_get_utc_date_string( $parsed['date_from'] );
			}

			if ( ! empty( $parsed['date_to'] ) ) {
				$date_query['before'] = edd_get_utc_date_string( $parsed['date_to'] );
			}

			if ( isset( $date_query['after'] ) || isset( $date_query['before'] ) ) {
				$query_args['date_query'] = array( $date_query );
			}
		}

		// Cart value filter.
		$compare_conditions = array();

		if ( null !== $parsed['value_min'] && '' !== $parsed['value_min'] ) {
			$compare_conditions[] = array(
				'value'   => (float) $parsed['value_min'],
				'compare' => '>=',
			);
		}

		if ( null !== $parsed['value_max'] && '' !== $parsed['value_max'] ) {
			$compare_conditions[] = array(
				'value'   => (float) $parsed['value_max'],
				'compare' => '<=',
			);
		}

		if ( ! empty( $compare_conditions ) ) {
			if ( count( $compare_conditions ) > 1 ) {
				// Multiple conditions: add relation and use numeric keys.
				$query_args['cart_total__compare'] = array_merge(
					array( 'relation' => 'AND' ),
					$compare_conditions
				);
			} else {
				// Single condition: pass the condition directly without array wrapper.
				$query_args['cart_total__compare'] = $compare_conditions[0];
			}
		}

		return $query_args;
	}

	/**
	 * Updates status for a single cart.
	 *
	 * @since 3.6.5
	 * @param int    $cart_id Cart ID.
	 * @param string $status  New status.
	 * @return bool Whether update succeeded.
	 */
	public function update_status( int $cart_id, string $status ): bool {
		if ( $cart_id <= 0 ) {
			return false;
		}

		$allowed_statuses = array_keys( self::get_cart_statuses() );
		if ( ! in_array( $status, $allowed_statuses, true ) ) {
			return false;
		}

		$update_data = array(
			'status' => $status,
		);

		if ( 'recovered' === $status ) {
			$update_data['date_recovered'] = edd_get_utc_date_string();
		}

		$query = new Query();

		return (bool) $query->update_item( $cart_id, $update_data );
	}

	/**
	 * Updates status for multiple carts using a single bulk UPDATE query.
	 *
	 * @since 3.6.5
	 * @param array  $cart_ids Array of cart IDs to update.
	 * @param string $status   New status.
	 * @return bool Whether bulk update succeeded.
	 */
	public function update_status_bulk( array $cart_ids, string $status ): bool {
		if ( empty( $cart_ids ) || ! $this->wpdb ) {
			return false;
		}

		$allowed_statuses = array_keys( self::get_cart_statuses() );
		if ( ! in_array( $status, $allowed_statuses, true ) ) {
			return false;
		}

		// Ensure all IDs are integers.
		$cart_ids = array_map( 'absint', $cart_ids );
		$cart_ids = array_filter( $cart_ids ); // Remove zeros.

		if ( empty( $cart_ids ) ) {
			return false;
		}

		// Build update data based on status.
		$update_fields = array(
			'status'        => $status,
			'date_modified' => edd_get_utc_date_string(),
		);

		if ( 'recovered' === $status ) {
			$update_fields['date_recovered'] = edd_get_utc_date_string();
		}

		// Build SET clause dynamically.
		$set_clauses = array();
		foreach ( array_keys( $update_fields ) as $field ) {
			$set_clauses[] = "{$field} = %s";
		}
		$set_clause = implode( ', ', $set_clauses );

		// Build WHERE clause formats for cart IDs.
		$id_formats   = array_fill( 0, count( $cart_ids ), '%d' );
		$placeholders = implode( ',', $id_formats );

		$query = $this->wpdb->prepare(
			"UPDATE {$this->table_name}
				SET {$set_clause}
				WHERE id IN ({$placeholders})",
			...array_merge( array_values( $update_fields ), $cart_ids )
		);

		if ( false === $query ) {
			$error = isset( $this->wpdb->last_error ) ? $this->wpdb->last_error : '';
			$this->log( sprintf( 'Failed to prepare bulk status update for %d carts: %s', count( $cart_ids ), $error ), true );
			return false;
		}

		$result = $this->wpdb->query( $query );

		if ( false === $result ) {
			$this->log( sprintf( 'Bulk status update failed for %d carts: %s', count( $cart_ids ), $this->wpdb->last_error ), true );
		}

		return false !== $result;
	}

	/**
	 * Sets abandoned date for multiple carts, preserving existing timestamps.
	 *
	 * @since 3.6.5
	 * @param array  $cart_ids Cart identifiers.
	 * @param string $datetime MySQL datetime string.
	 * @return bool Whether any rows were updated.
	 */
	public function set_date_abandoned_bulk( array $cart_ids, string $datetime ): bool {
		if ( empty( $cart_ids ) || ! $this->wpdb ) {
			return false;
		}

		$cart_ids = array_map( 'absint', $cart_ids );
		$cart_ids = array_filter( $cart_ids );

		if ( empty( $cart_ids ) ) {
			return false;
		}

		$datetime = $this->to_mysql_datetime( $datetime );
		if ( empty( $datetime ) ) {
			$datetime = edd_get_utc_date_string();
		}

		$placeholders = $this->get_placeholders( $cart_ids, 'int' );

		$sql = "
			UPDATE {$this->table_name}
			SET date_abandoned = %s, date_modified = %s
			WHERE id IN ({$placeholders})
			AND ( date_abandoned IS NULL OR date_abandoned = '0000-00-00 00:00:00' )
		";

		$params = array_merge(
			array( $datetime, $datetime ),
			$cart_ids
		);

		$prepared = $this->wpdb->prepare( $sql, $params );
		if ( false === $prepared ) {
			return false;
		}

		$result = $this->wpdb->query( $prepared );

		if ( false === $result ) {
			$this->log( sprintf( 'Failed to set abandoned date for %d carts: %s', count( $cart_ids ), $this->wpdb->last_error ), true );
		}

		return false !== $result;
	}

	/**
	 * Deletes multiple carts using a single bulk DELETE query.
	 *
	 * @since 3.6.5
	 * @param array $cart_ids Array of cart IDs to delete.
	 * @return bool Whether bulk delete succeeded.
	 */
	public function delete_carts_bulk( array $cart_ids ): bool {
		if ( empty( $cart_ids ) || ! $this->wpdb ) {
			return false;
		}

		// Ensure all IDs are integers.
		$cart_ids = array_map( 'absint', $cart_ids );
		$cart_ids = array_filter( $cart_ids );

		if ( empty( $cart_ids ) ) {
			return false;
		}

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

		try {
			$placeholders = $this->get_placeholders( $cart_ids, 'int' );

			// Delete journey events first.
			$journey_query = $this->wpdb->prepare(
				"DELETE FROM {$this->wpdb->prefix}edd_acr_journey_events WHERE cart_id IN ({$placeholders})",
				...$cart_ids
			);

			$journey_result = $this->wpdb->query( $journey_query );

			if ( false === $journey_result ) {
				throw new \Exception( sprintf( 'Journey events deletion failed: %s', $this->wpdb->last_error ) );
			}

			// Delete recovery emails.
			$emails_query = $this->wpdb->prepare(
				"DELETE FROM {$this->wpdb->prefix}edd_acr_emails WHERE cart_id IN ({$placeholders})",
				...$cart_ids
			);

			$emails_result = $this->wpdb->query( $emails_query );

			if ( false === $emails_result ) {
				throw new \Exception( sprintf( 'Emails deletion failed: %s', $this->wpdb->last_error ) );
			}

			// Delete carts.
			$carts_query = $this->wpdb->prepare(
				"DELETE FROM {$this->table_name} WHERE id IN ({$placeholders})",
				...$cart_ids
			);

			$result = $this->wpdb->query( $carts_query );

			if ( false === $result ) {
				throw new \Exception( sprintf( 'Carts deletion failed: %s', $this->wpdb->last_error ) );
			}

			$this->wpdb->query( 'COMMIT' );

			return true;

		} catch ( \Exception $e ) {
			$this->wpdb->query( 'ROLLBACK' );
			$this->log( sprintf( 'Bulk delete failed for %d carts: %s', count( $cart_ids ), $e->getMessage() ), true );
			return false;
		}
	}

	/**
	 * Deletes a cart and its related recovery emails and journey events.
	 *
	 * @since 3.6.5
	 * @param int $cart_id Cart ID.
	 * @return bool Whether deletion succeeded.
	 */
	public function delete_cart( int $cart_id ): bool {
		if ( ! $this->wpdb ) {
			return false;
		}

		// Start transaction to ensure atomic deletion.
		$this->wpdb->query( 'START TRANSACTION' );

		try {
			// First, delete related journey events.
			$journey_deleted = $this->wpdb->delete(
				$this->wpdb->prefix . 'edd_acr_journey_events',
				array( 'cart_id' => $cart_id ),
				array( '%d' )
			);

			// Then delete related recovery emails.
			$emails_deleted = $this->wpdb->delete(
				$this->wpdb->prefix . 'edd_acr_emails',
				array( 'cart_id' => $cart_id ),
				array( '%d' )
			);

			// Finally delete the cart.
			$cart_deleted = $this->wpdb->delete(
				$this->table_name,
				array( 'id' => $cart_id ),
				array( '%d' )
			);

			if ( false === $journey_deleted || false === $emails_deleted || false === $cart_deleted ) {
				$this->wpdb->query( 'ROLLBACK' );
				return false;
			}

			if ( (int) EDD()->session->get( 'acr_cart' ) === $cart_id ) {
				EDD()->session->set( 'acr_cart', null );
			}

			$this->wpdb->query( 'COMMIT' );
			return true;

		} catch ( \Exception $e ) {
			$this->wpdb->query( 'ROLLBACK' );
			edd_debug_log( '[EDD ACR] Cart deletion transaction failed: ' . $e->getMessage() . ' Stack: ' . $e->getTraceAsString(), true );
			return false;
		}
	}

	/**
	 * Returns recovered carts count trend grouped by day using date_recovered.
	 * Database dates are UTC; start/end are in store timezone and converted to UTC for filtering.
	 * Grouping uses timezone conversion so returned dates are in store timezone.
	 *
	 * @since 3.6.5
	 * @param \DateTimeInterface $start Inclusive start date in site TZ.
	 * @param \DateTimeInterface $end   Inclusive end date in site TZ.
	 * @return array Array of [ [ 'date' => 'YYYY-MM-DD', 'count' => int ] ]
	 */
	public function get_recovered_trend( \DateTimeInterface $start, \DateTimeInterface $end ): array {
		if ( ! $this->wpdb ) {
			return array();
		}

		$date_range = Reports\Utilities\Database::parse_date_range_for_query( $start, $end );
		$clauses    = Reports\Utilities\Database::get_sql_clauses( 'day', $this->table_name . '.date_recovered' );

		$sql = "SELECT {$clauses['select']}, COUNT(*) AS count
				FROM {$this->table_name}
				WHERE status = 'recovered' AND date_recovered BETWEEN %s AND %s
				GROUP BY {$clauses['groupby']}
				ORDER BY {$clauses['orderby']} ASC";

		$prepared = $this->wpdb->prepare( $sql, $date_range['start'], $date_range['end'] );
		if ( false === $prepared ) {
			return array();
		}

		$rows = $this->wpdb->get_results( $prepared );
		$out  = array();
		foreach ( (array) $rows as $row ) {
			$out[] = array(
				'date'  => (string) $row->date,
				'count' => (int) $row->count,
			);
		}

		return $out;
	}

	/**
	 * Returns the field schema for cart attributes.
	 *
	 * Builds field definitions from the database schema, ensuring single source of truth.
	 *
	 * @since 3.6.5
	 * @return array Field schema definition.
	 */
	private function get_field_schema(): array {
		static $schema_cache = null;

		if ( null !== $schema_cache ) {
			return $schema_cache;
		}

		// Get the database schema columns.
		$schema_class = new \EDD\Pro\CartRecovery\Database\Schemas\RecoveryCarts();
		$columns      = $schema_class->columns;

		$schema = array();

		foreach ( $columns as $column ) {
			// Columns are Column objects with public properties, not arrays.
			if ( ! $column instanceof \EDD\Database\Column ) {
				continue;
			}

			$name = $column->name;

			if ( 'id' === $name ) {
				continue; // Skip auto-increment primary key.
			}

			$db_type  = strtolower( $column->type ?? 'varchar' );
			$length   = ! empty( $column->length ) ? (int) $column->length : null;
			$nullable = ! empty( $column->allow_null );

			// Map database types to normalization types.
			$field_definition = array(
				'nullable' => $nullable,
			);

			switch ( $db_type ) {
				case 'int':
				case 'bigint':
					$field_definition['type'] = 'int';
					// cart_version should have a minimum of 1.
					if ( 'cart_version' === $name ) {
						$field_definition['min'] = 1;
					}
					break;

				case 'decimal':
					$field_definition['type'] = 'float';
					break;

				case 'datetime':
					$field_definition['type'] = 'datetime';
					break;

				case 'varchar':
				case 'text':
				case 'longtext':
					// Determine if this field needs special handling.
					if ( 'email' === $name ) {
						$field_definition['type']     = 'string';
						$field_definition['sanitize'] = 'sanitize_email';
					} elseif ( 'referrer_url' === $name ) {
						$field_definition['type'] = 'url';
					} elseif ( 'user_agent' === $name ) {
						$field_definition['type']     = 'string';
						$field_definition['sanitize'] = 'sanitize_textarea_field';
					} elseif ( 'cart_contents' === $name ) {
						$field_definition['type']    = 'special';
						$field_definition['handler'] = 'encode_cart_contents';
					} else {
						$field_definition['type']     = 'string';
						$field_definition['sanitize'] = 'sanitize_text_field';
					}

					// Add max length for varchar types.
					if ( 'varchar' === $db_type && $length > 0 ) {
						$field_definition['max_length'] = $length;
					} elseif ( 'text' === $db_type ) {
						$field_definition['max_length'] = 65535;
					}
					break;

				default:
					$field_definition['type'] = 'string';
					break;
			}

			$schema[ $name ] = $field_definition;
		}

		$schema_cache = $schema;

		return $schema;
	}

	/**
	 * Normalises incoming data to match database schema.
	 *
	 * Only includes fields that are explicitly provided in the attributes array.
	 * This prevents unintended overwrites of existing database values.
	 *
	 * @since 3.6.5
	 * @param array $attributes Raw attributes.
	 * @return array
	 */
	private function normalise_attributes( array $attributes ) {
		$payload = array();
		$schema  = $this->get_field_schema();

		// Process each provided attribute according to its schema.
		foreach ( $attributes as $key => $value ) {
			if ( ! isset( $schema[ $key ] ) ) {
				continue; // Skip unknown fields.
			}

			$field_definition = $schema[ $key ];
			$normalised_value = $this->normalise_field_value( $key, $value, $field_definition, $attributes );

			// Only add to payload if we got a non-null value or if the field explicitly allows null.
			if ( null !== $normalised_value || ( isset( $field_definition['nullable'] ) && $field_definition['nullable'] ) ) {
				$payload[ $key ] = $normalised_value;
			}
		}

		// Always ensure date_modified is set for any update operation.
		if ( ! isset( $payload['date_modified'] ) ) {
			$payload['date_modified'] = edd_get_utc_date_string();
		}

		return $payload;
	}

	/**
	 * Normalises a single field value based on its schema definition.
	 *
	 * @since 3.6.5
	 * @param string $key              Field key.
	 * @param mixed  $value            Raw value.
	 * @param array  $field_definition Field definition from schema.
	 * @param array  $attributes       Full attributes array (for special handlers).
	 * @return mixed Normalised value.
	 */
	private function normalise_field_value( string $key, $value, array $field_definition, array $attributes ) {

		if ( empty( $value ) && ! empty( $field_definition['nullable'] ) ) {
			return null;
		}

		$type = $field_definition['type'] ?? 'string';

		switch ( $type ) {
			case 'int':
				$normalised = (int) $value;
				if ( isset( $field_definition['min'] ) ) {
					$normalised = max( $field_definition['min'], $normalised );
				}
				return $normalised;

			case 'float':
				return (float) $value;

			case 'string':
				$sanitize   = $field_definition['sanitize'] ?? 'sanitize_text_field';
				$normalised = $sanitize( (string) $value );

				if ( isset( $field_definition['max_length'] ) ) {
					$normalised = substr( $normalised, 0, $field_definition['max_length'] );
				}

				// For session_id, only return if non-empty.
				if ( 'session_id' === $key && empty( $normalised ) ) {
					return null;
				}

				return $normalised;

			case 'url':
				return esc_url_raw( (string) $value );

			case 'datetime':
				return $this->to_mysql_datetime( $value );

			case 'special':
				$handler = $field_definition['handler'] ?? null;
				if ( $handler && method_exists( $this, $handler ) ) {
					return $this->$handler( $attributes );
				}
				return $value;

			default:
				return $value;
		}
	}

	/**
	 * Locates carts related to the provided identifiers using a single optimized query.
	 *
	 * When $for_update is true, this method uses SELECT ... FOR UPDATE to acquire
	 * row-level locks. This MUST be called from within an active database transaction
	 * for the locks to be held beyond the query execution.
	 *
	 * When $for_update is false, no locking is performed and the method can be safely
	 * called outside of a transaction for read-only operations.
	 *
	 * @since 3.6.5
	 * @param array $attributes Prepared attributes.
	 * @param bool  $for_update Whether to use FOR UPDATE locking. Default true.
	 * @return array{
	 *     primary: object|null,
	 *     duplicates: array<int,object>
	 * }
	 */
	private function locate_existing_carts( array $attributes, bool $for_update = true ) {
		if ( ! $this->wpdb ) {
			return array(
				'primary'    => null,
				'duplicates' => array(),
			);
		}

		// Build each clause with its own prepare() call so placeholders stay next to their values.
		$where_clauses = array();

		if ( ! empty( $attributes['customer_id'] ) ) {
			$where_clauses[] = $this->wpdb->prepare( '(customer_id = %d AND customer_id IS NOT NULL)', (int) $attributes['customer_id'] );
		}

		if ( ! empty( $attributes['session_id'] ) ) {
			$where_clauses[] = $this->wpdb->prepare( "(session_id = %s AND session_id != '')", $attributes['session_id'] );
		}

		if ( ! empty( $attributes['email'] ) ) {
			$where_clauses[] = $this->wpdb->prepare( "(email = %s AND email != '')", $attributes['email'] );
		}

		if ( empty( $where_clauses ) ) {
			return array(
				'primary'    => null,
				'duplicates' => array(),
			);
		}

		// Status filter.
		$valid_statuses = $this->get_updateable_statuses();
		if ( empty( $valid_statuses ) ) {
			return array(
				'primary'    => null,
				'duplicates' => array(),
			);
		}

		$status_placeholders = $this->get_placeholders( $valid_statuses );
		$status_sql          = $this->wpdb->prepare( "status IN ({$status_placeholders})", ...$valid_statuses );

		// ORDER BY prioritization: customer_id > session_id > email.
		$order_clauses = array();

		if ( ! empty( $attributes['customer_id'] ) ) {
			$order_clauses[] = $this->wpdb->prepare( 'WHEN customer_id = %d THEN 1', (int) $attributes['customer_id'] );
		}

		if ( ! empty( $attributes['session_id'] ) ) {
			$order_clauses[] = $this->wpdb->prepare( 'WHEN session_id = %s THEN 2', $attributes['session_id'] );
		}

		if ( ! empty( $attributes['email'] ) ) {
			$order_clauses[] = $this->wpdb->prepare( 'WHEN email = %s THEN 3', $attributes['email'] );
		}

		$where_sql  = implode( ' OR ', $where_clauses );
		$order_case = 'CASE ' . implode( ' ', $order_clauses ) . ' ELSE 4 END';

		// All placeholders are already resolved — no prepare() needed on the final string.
		$prepared_sql = "SELECT * FROM {$this->table_name}
			WHERE ({$where_sql}) AND {$status_sql}
			ORDER BY {$order_case}
			LIMIT 3";

		if ( $for_update ) {
			$prepared_sql .= ' FOR UPDATE';
		}

		if ( false === $prepared_sql ) {
			return array(
				'primary'    => null,
				'duplicates' => array(),
			);
		}

		$carts = $this->wpdb->get_results( $prepared_sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- This is already prepared.

		$error = isset( $this->wpdb->last_error ) ? trim( (string) $this->wpdb->last_error ) : '';
		if ( '' !== $error ) {
			$this->log( 'Database error while locating carts: ' . $error, true );
			return array(
				'primary'    => null,
				'duplicates' => array(),
			);
		}

		if ( ! is_array( $carts ) ) {
			$this->log( 'Database returned unexpected result type while locating carts.', true );
			return array(
				'primary'    => null,
				'duplicates' => array(),
			);
		}

		if ( empty( $carts ) ) {
			return array(
				'primary'    => null,
				'duplicates' => array(),
			);
		}

		// Process results to determine primary and duplicates.
		$primary    = null;
		$duplicates = array();
		$seen_ids   = array();

		foreach ( $carts as $cart ) {
			$cart_id = (int) $cart->id;

			if ( in_array( $cart_id, $seen_ids, true ) ) {
				continue; // Skip duplicates.
			}

			$seen_ids[] = $cart_id;

			if ( null === $primary ) {
				$primary = $cart;
			} else {
				$duplicates[] = $cart;
			}
		}

		return array(
			'primary'    => $primary,
			'duplicates' => $duplicates,
		);
	}

	/**
	 * Merges duplicate cart rows into the payload being persisted.
	 *
	 * @since 3.6.5
	 * @param array             $payload    Incoming payload.
	 * @param object            $primary    Primary cart object.
	 * @param array<int,object> $duplicates Duplicate carts to merge.
	 * @return array
	 */
	private function merge_duplicate_carts( array $payload, $primary, array $duplicates ) {
		$items_map = array();

		if ( $primary ) {
			// Seed the map with the persisted cart so we retain its state before merging others.
			$primary_items = Cart::decode_items( $primary->cart_contents );
			foreach ( $primary_items as $item ) {
				$items_map[ $this->build_cart_item_key( $item ) ] = $item;
			}
		}

		foreach ( $duplicates as $duplicate ) {
			// Fold in any additional carts tied to the same shopper, adding only items not already present.
			// Duplicates represent the same cart captured at different times or from different identifiers,
			// so existing items should be kept as-is rather than accumulating quantities.
			$duplicate_items = Cart::decode_items( $duplicate->cart_contents );
			foreach ( $duplicate_items as $item ) {
				$key = $this->build_cart_item_key( $item );
				if ( ! isset( $items_map[ $key ] ) ) {
					$items_map[ $key ] = $item;
				}
			}
		}

		// Finally merge the incoming payload so the freshest visitor data wins.
		$incoming_contents = isset( $payload['cart_contents'] ) ? $payload['cart_contents'] : '';
		if ( is_array( $incoming_contents ) ) {
			$incoming_contents = wp_json_encode( $incoming_contents );
		}
		$incoming_items = Cart::decode_items( (string) $incoming_contents );
		foreach ( $incoming_items as $item ) {
			$key = $this->build_cart_item_key( $item );
			// Freshest visitor data wins - overwrite existing quantity with incoming data.
			$items_map[ $key ] = $item;
		}

		$merged_items = array_values(
			array_map(
				array( $this, 'sanitize_cart_item_for_storage' ),
				$items_map
			)
		);

		// Build merged cart contents, preserving customer data from incoming payload.
		$merged_cart_contents = array(
			'items' => $merged_items,
		);

		// Preserve customer data from incoming payload (email, first_name, last_name).
		if ( is_array( $payload['cart_contents'] ) && ! empty( $payload['cart_contents']['customer'] ) ) {
			$merged_cart_contents['customer'] = $payload['cart_contents']['customer'];
		}

		$encoded_contents = wp_json_encode( $merged_cart_contents );

		if ( false === $encoded_contents ) {
			$encoded_contents = wp_json_encode(
				$merged_cart_contents,
				JSON_PARTIAL_OUTPUT_ON_ERROR
			);
		}

		if ( false === $encoded_contents ) {
			$cart_id = $primary && isset( $primary->id ) ? (int) $primary->id : 'new';
			$this->log( 'Failed to encode merged cart contents for cart ID: ' . $cart_id, true );

			$fallback_contents = null;

			if ( isset( $payload['cart_contents'] ) && is_string( $payload['cart_contents'] ) && '' !== $payload['cart_contents'] ) {
				$fallback_contents = $payload['cart_contents'];
			} elseif ( $primary && isset( $primary->cart_contents ) && is_string( $primary->cart_contents ) && '' !== $primary->cart_contents ) {
				$fallback_contents = $primary->cart_contents;
			}

			if ( null === $fallback_contents ) {
				$fallback_contents = wp_json_encode( array( 'items' => array() ) );
			}

			$payload['cart_contents'] = is_string( $fallback_contents ) ? $fallback_contents : '{}';
		} else {
			$payload['cart_contents'] = $encoded_contents;
		}

		$payload['cart_total'] = $this->resolve_cart_total( $payload, $primary, $duplicates, $merged_items );

		if ( empty( $payload['currency'] ) ) {
			if ( $primary && ! empty( $primary->currency ) ) {
				$payload['currency'] = $primary->currency;
			} else {
				foreach ( $duplicates as $duplicate ) {
					if ( ! empty( $duplicate->currency ) ) {
						$payload['currency'] = $duplicate->currency;
						break;
					}
				}
			}
		}

		if ( empty( $payload['discount_applied'] ) ) {
			if ( $primary && ! empty( $primary->discount_applied ) ) {
				$payload['discount_applied'] = $primary->discount_applied;
			} else {
				foreach ( $duplicates as $duplicate ) {
					if ( ! empty( $duplicate->discount_applied ) ) {
						$payload['discount_applied'] = $duplicate->discount_applied;
						break;
					}
				}
			}
		}

		$versions = array();
		if ( isset( $payload['cart_version'] ) ) {
			$versions[] = (int) $payload['cart_version'];
		}
		if ( $primary ) {
			$versions[] = (int) $primary->cart_version;
		}
		foreach ( $duplicates as $duplicate ) {
			$versions[] = (int) $duplicate->cart_version;
		}
		if ( ! empty( $versions ) ) {
			$payload['cart_version'] = max( $versions );
		}

		if ( ! isset( $payload['date_abandoned'] ) ) {
			$abandoned_statuses = array( 'abandoned', 'abandoned_ir', 'abandoned_vr', 'abandoned_fa' );
			$candidates         = array();

			if ( $primary && in_array( $primary->status, $abandoned_statuses, true ) ) {
				if ( ! empty( $primary->date_abandoned ) && '0000-00-00 00:00:00' !== $primary->date_abandoned ) {
					$candidates[] = $primary->date_abandoned;
				}
			}

			foreach ( $duplicates as $duplicate ) {
				if ( ! $duplicate || ! in_array( $duplicate->status, $abandoned_statuses, true ) ) {
					continue;
				}

				if ( ! empty( $duplicate->date_abandoned ) && '0000-00-00 00:00:00' !== $duplicate->date_abandoned ) {
					$candidates[] = $duplicate->date_abandoned;
				}
			}

			if ( ! empty( $candidates ) ) {
				$payload['date_abandoned'] = min( $candidates );
			}
		}

		if ( ! isset( $payload['status'] ) ) {
			if ( $primary && ! empty( $primary->status ) ) {
				$payload['status'] = $primary->status;
			} else {
				foreach ( $duplicates as $duplicate ) {
					if ( ! empty( $duplicate->status ) ) {
						$payload['status'] = $duplicate->status;
						break;
					}
				}
			}
		}

		return $payload;
	}

	/**
	 * Marks duplicate cart rows as expired to avoid duplicate tracking.
	 *
	 * @since 3.6.5
	 * @param int               $primary_id Primary cart id.
	 * @param array<int,object> $duplicates Duplicate carts.
	 * @return void
	 */
	private function expire_duplicate_carts( $primary_id, array $duplicates ) {
		if ( empty( $duplicates ) || ! ( $this->wpdb instanceof wpdb ) ) {
			$this->log( 'Security: Invalid parameters detected in expire_duplicate_carts - empty duplicates or invalid wpdb instance.', true );
			return;
		}

		$now = edd_get_utc_date_string();

		// Filter out the primary cart and prepare IDs for bulk update.
		$duplicate_ids = array();
		foreach ( $duplicates as $duplicate ) {
			if ( (int) $duplicate->id !== (int) $primary_id ) {
				$duplicate_ids[] = (int) $duplicate->id;
			}
		}

		if ( empty( $duplicate_ids ) ) {
			return;
		}

		// Perform bulk UPDATE instead of N+1 individual updates.
		$placeholders = $this->get_placeholders( $duplicate_ids, 'int' );

		$query = $this->wpdb->prepare(
			"UPDATE {$this->table_name}
            SET session_id = '',
                customer_id = NULL,
                email = NULL,
                status = 'expired',
                date_expired = %s,
                date_modified = %s
            WHERE id IN ({$placeholders})",
			$now,
			$now,
			...$duplicate_ids
		);

		if ( false === $query ) {
			$error = isset( $this->wpdb->last_error ) ? $this->wpdb->last_error : '';
			$this->log( sprintf( 'Failed to prepare duplicate cart expiration query for %d carts: %s', count( $duplicate_ids ), $error ), true );
			return;
		}

		$result = $this->wpdb->query( $query );

		if ( false === $result ) {
			$this->log( sprintf( '[EDD ACR] Failed to expire %d duplicate carts.', count( $duplicate_ids ) ) );
		}
	}


	/**
	 * Builds a unique key for cart items using product and variation identifiers.
	 *
	 * @since 3.6.5
	 * @param array{id:int,name:string,price_id:?string,quantity:int,price:float} $item Cart item.
	 * @return string
	 */
	private function build_cart_item_key( array $item ) {
		$id       = isset( $item['id'] ) ? (string) $item['id'] : '';
		$price_id = isset( $item['price_id'] ) ? (string) $item['price_id'] : '';

		return $id . '|' . $price_id;
	}

	/**
	 * Sanitises a cart item prior to storage.
	 *
	 * @since 3.6.5
	 * @param array{id:int,name:string,price_id:?string,quantity:int,price:float} $item Cart item.
	 * @return array{id:?string,name:?string,price_id:?string,quantity:int,price:float}
	 */
	private function sanitize_cart_item_for_storage( array $item ) {
		// Preserve numeric price_id including 0.
		$price_id = isset( $item['price_id'] ) && is_numeric( $item['price_id'] )
			? substr( sanitize_text_field( (string) $item['price_id'] ), 0, 255 )
			: null;

		$name = isset( $item['name'] ) && ! empty( $item['name'] )
			? substr( sanitize_text_field( (string) $item['name'] ), 0, 255 )
			: null;

		return array(
			'id'       => isset( $item['id'] ) ? substr( sanitize_text_field( (string) $item['id'] ), 0, 255 ) : null,
			'name'     => $name,
			'price_id' => $price_id,
			'quantity' => max( 1, min( 100, (int) isset( $item['quantity'] ) ? $item['quantity'] : 1 ) ),
			'price'    => isset( $item['price'] ) ? (float) $item['price'] : 0.0,
		);
	}

	/**
	 * Resolves cart total using payload and duplicate metadata.
	 *
	 * @since 3.6.5
	 * @param array                                                                    $payload      Incoming payload.
	 * @param object|null                                                              $primary      Primary cart row.
	 * @param array<int,object>                                                        $duplicates   Duplicate cart rows.
	 * @param array{id:?string,name:?string,price_id:?string,quantity:int,price:float} $merged_items Merged cart items.
	 * @return float
	 */
	private function resolve_cart_total( array $payload, $primary, array $duplicates, array $merged_items ) {
		if ( isset( $payload['cart_total'] ) ) {
			return (float) $payload['cart_total'];
		}

		// Derive total from merged line items before falling back to historical metadata.
		$totals = array_sum(
			array_map(
				static function ( $item ) {
					if ( ! is_array( $item ) ) {
						return 0.0;
					}

					$price    = isset( $item['price'] ) ? $item['price'] : null;
					$quantity = isset( $item['quantity'] ) ? $item['quantity'] : null;

					if ( ! is_numeric( $price ) || ! is_numeric( $quantity ) ) {
						return 0.0;
					}

					return (float) $price * (float) $quantity;
				},
				$merged_items
			)
		);

		if ( ! empty( $merged_items ) ) {
			return $totals;
		}

		$fallback = array();
		if ( $primary && isset( $primary->cart_total ) ) {
			$fallback[] = (float) $primary->cart_total;
		}
		foreach ( $duplicates as $duplicate ) {
			if ( isset( $duplicate->cart_total ) ) {
				$fallback[] = (float) $duplicate->cart_total;
			}
		}

		if ( ! empty( $fallback ) ) {
			return max( $fallback );
		}

		return 0.0;
	}

	/**
	 * Encodes cart contents for database storage with JSON depth validation.
	 *
	 * Validates JSON depth to prevent stack overflow attacks and applies
	 * quantity limits before encoding.
	 *
	 * @since 3.6.5
	 * @param array $attributes Raw attributes.
	 * @return string
	 */
	private function encode_cart_contents( array $attributes ) {
		$contents = array();

		if ( isset( $attributes['cart_contents'] ) ) {
			if ( is_string( $attributes['cart_contents'] ) ) {
				// Add depth limit to prevent stack overflow attacks via nested malicious JSON.
				$decoded = json_decode( $attributes['cart_contents'], true, Security::get_max_json_depth() );
				if ( is_array( $decoded ) && JSON_ERROR_NONE === json_last_error() ) {
					$contents = $decoded;
				} elseif ( JSON_ERROR_DEPTH === json_last_error() ) {
					// Log potential JSON depth attack attempts for security monitoring.
					$this->log( '[EDD ACR] Security: JSON depth limit exceeded in cart contents - possible stack overflow attack.' );
				}
			} else {
				$contents = (array) $attributes['cart_contents'];
			}
		}

		if ( isset( $contents['items'] ) && is_array( $contents['items'] ) ) {
			$contents['items'] = array_slice( $contents['items'], 0, Security::get_max_items_per_cart() );
			foreach ( $contents['items'] as &$item ) {
				if ( ! is_array( $item ) ) {
					$item = array();
				}

				// Enforce canonical quantity bounds on top-level quantity if present.
				if ( isset( $item['quantity'] ) ) {
					$item['quantity'] = (int) $item['quantity'];
					if ( $item['quantity'] < 1 ) {
						$item['quantity'] = 1;
					} elseif ( $item['quantity'] > 100 ) {
						$item['quantity'] = 100;
					}
				}

				// Also enforce quantity bounds in item_number if present.
				if ( isset( $item['item_number']['quantity'] ) ) {
					$item['item_number']['quantity'] = (int) $item['item_number']['quantity'];
					if ( $item['item_number']['quantity'] < 1 ) {
						$item['item_number']['quantity'] = 1;
					} elseif ( $item['item_number']['quantity'] > 100 ) {
						$item['item_number']['quantity'] = 100;
					}
				}
			}
			unset( $item );
		}

		return wp_json_encode( $contents );
	}

	/**
	 * Converts a value into a MySQL datetime string when possible.
	 *
	 * @since 3.6.5
	 * @param mixed $value Value to convert.
	 * @return string|null
	 */
	private function to_mysql_datetime( $value ) {
		if ( empty( $value ) ) {
			return null;
		}

		$timestamp = is_numeric( $value ) ? (int) $value : strtotime( (string) $value );

		if ( false === $timestamp ) {
			return null;
		}

		return gmdate( 'Y-m-d H:i:s', $timestamp );
	}

	/**
	 * Builds insert payload with formats.
	 *
	 * @since 3.6.5
	 * @param array  $payload Normalised attributes.
	 * @param string $now     Current timestamp.
	 * @return array
	 */
	private function prepare_insert_payload( array $payload, $now ) {
		$values                 = $payload;
		$values['cart_version'] = isset( $payload['cart_version'] ) ? (int) $payload['cart_version'] : 1;

		if ( array_key_exists( 'date_abandoned', $payload ) ) {
			$values['date_abandoned'] = $payload['date_abandoned'];
		} else {
			$values['date_abandoned'] = null;
		}

		$values['status']   = isset( $payload['status'] ) ? $payload['status'] : 'tracking';
		$values['currency'] = isset( $payload['currency'] ) ? $payload['currency'] : substr( sanitize_text_field( (string) edd_get_currency() ), 0, 3 );

		$values['date_created']  = $now;
		$values['date_modified'] = isset( $payload['date_modified'] ) ? $payload['date_modified'] : $now;

		$formats = $this->get_formats( $values );

		return array(
			'values'  => $values,
			'formats' => $formats,
		);
	}

	/**
	 * Builds update payload with formats.
	 *
	 * @since 3.6.5
	 * @param array  $payload  Normalised attributes.
	 * @param object $existing Existing database row.
	 * @param string $now      Current timestamp.
	 * @return array
	 */
	private function prepare_update_payload( array $payload, $existing, $now ) {
		// Start with the payload values - only fields that are explicitly provided will be updated.
		// This prevents heartbeat events from overwriting cart data with empty values.
		$values = $payload;

		$current_version = isset( $existing->cart_version ) ? (int) $existing->cart_version : 0;
		if ( isset( $payload['cart_version'] ) ) {
			$values['cart_version'] = (int) $payload['cart_version'];
		} else {
			// No incoming version means we are rolling the record forward optimistically.
			$values['cart_version'] = $current_version + 1;
		}

		if ( isset( $payload['status'] ) ) {
			$values['status'] = $payload['status'];
		} elseif ( isset( $existing->status ) ) {
			$values['status'] = $existing->status;
		} else {
			$values['status'] = 'tracking';
		}

		if ( array_key_exists( 'date_abandoned', $payload ) ) {
			$values['date_abandoned'] = $payload['date_abandoned'];
		} elseif ( isset( $existing->date_abandoned ) ) {
			$values['date_abandoned'] = $existing->date_abandoned;
		}

		$values['date_modified'] = isset( $payload['date_modified'] ) ? $payload['date_modified'] : $now;

		$formats = $this->get_formats( $values );

		return array(
			'values'  => $values,
			'formats' => $formats,
		);
	}

	/**
	 * Returns format map for wpdb operations.
	 *
	 * @since 3.6.5
	 * @param array $values Values being persisted.
	 * @return array
	 */
	private function get_formats( array $values ) {
		$map = array(
			'session_id'           => '%s',
			'customer_id'          => '%d',
			'email'                => '%s',
			'cart_contents'        => '%s',
			'cart_total'           => '%f',
			'currency'             => '%s',
			'discount_applied'     => '%s',
			'browser_fingerprint'  => '%s',
			'timezone'             => '%s',
			'date_expired'         => '%s',
			'cart_version'         => '%d',
			'status'               => '%s',
			'date_abandoned'       => '%s',
			'date_recovered'       => '%s',
			'order_id'             => '%d',
			'recovery_email_count' => '%d',
			'last_recovery_email'  => '%s',
			'user_agent'           => '%s',
			'ip_address'           => '%s',
			'referrer_url'         => '%s',
			'fail_count'           => '%d',
			'date_created'         => '%s',
			'date_modified'        => '%s',
		);

		$formats = array();

		foreach ( $values as $key => $value ) {
			if ( isset( $map[ $key ] ) ) {
				$formats[ $key ] = $map[ $key ];
			}
		}

		return $formats;
	}

	/**
	 * Returns placeholders for wpdb operations.
	 *
	 * @since 3.6.5
	 * @param array  $values Values being persisted.
	 * @param string $type   Type of placeholder to return.
	 * @return string
	 */
	private function get_placeholders( array $values, $type = 'string' ): string {
		if ( 'int' === $type ) {
			return implode( ',', array_fill( 0, count( $values ), '%d' ) );
		}

		return implode( ',', array_fill( 0, count( $values ), '%s' ) );
	}

	/**
	 * Returns an array of statuses that can be updated.
	 *
	 * @since 3.6.5
	 * @return array
	 */
	private function get_updateable_statuses(): array {
		$statuses = self::get_cart_statuses();
		unset( $statuses['recovered'] );
		unset( $statuses['expired'] );

		return array_keys( $statuses );
	}
}
