<?php
/**
 * Data Source for WooCommerce customers.
 *
 * Adapter between WP_User_Query/WC_Customer and the export engine.
 * Extracts registered customer data and optionally aggregates guest
 * customers from order billing emails.
 */

namespace RawTypeOE\Free;

// Prevent direct access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Provides customer data extraction and normalization for export.
 */
class Data_Source_Customers {

	use Data_Source_Trait {
		get_meta_value as private trait_get_meta_value;
	}

	/** Maximum number of unique guest emails to process. */
	const GUEST_LIMIT = 1000;

	/**
	 * Get all available columns for customers, grouped by category.
	 *
	 * @return array Associative array of field_id => { label, group }.
	 */
	public function get_available_columns() {
		$columns = array(
			// --- Basic customer data ---
			'customer_id'         => array(
				'label' => __( 'Customer ID', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'username'            => array(
				'label' => __( 'Username', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'email'               => array(
				'label' => __( 'Email', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'first_name'          => array(
				'label' => __( 'First Name', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'last_name'           => array(
				'label' => __( 'Last Name', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'display_name'        => array(
				'label' => __( 'Display Name', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'date_registered'     => array(
				'label' => __( 'Date Registered', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'is_guest'            => array(
				'label' => __( 'Is Guest', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),
			'role'                => array(
				'label' => __( 'Role', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'basic',
			),

			// --- Billing ---
			'billing_first_name'  => array(
				'label' => __( 'Billing First Name', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_last_name'   => array(
				'label' => __( 'Billing Last Name', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_company'     => array(
				'label' => __( 'Billing Company', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_email'       => array(
				'label' => __( 'Billing Email', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_phone'       => array(
				'label' => __( 'Billing Phone', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_address_1'   => array(
				'label' => __( 'Billing Address 1', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_address_2'   => array(
				'label' => __( 'Billing Address 2', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_city'        => array(
				'label' => __( 'Billing City', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_state'       => array(
				'label' => __( 'Billing State', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_postcode'    => array(
				'label' => __( 'Billing Postcode', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),
			'billing_country'     => array(
				'label' => __( 'Billing Country', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'billing',
			),

			// --- Shipping ---
			'shipping_first_name' => array(
				'label' => __( 'Shipping First Name', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_last_name'  => array(
				'label' => __( 'Shipping Last Name', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_company'    => array(
				'label' => __( 'Shipping Company', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_address_1'  => array(
				'label' => __( 'Shipping Address 1', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_address_2'  => array(
				'label' => __( 'Shipping Address 2', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_city'       => array(
				'label' => __( 'Shipping City', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_state'      => array(
				'label' => __( 'Shipping State', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_postcode'   => array(
				'label' => __( 'Shipping Postcode', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),
			'shipping_country'    => array(
				'label' => __( 'Shipping Country', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'shipping',
			),

			// --- Purchase metrics ---
			'orders_count'        => array(
				'label' => __( 'Orders Count', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'metrics',
			),
			'total_spent'         => array(
				'label' => __( 'Total Spent', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'metrics',
			),
			'last_order_date'     => array(
				'label' => __( 'Last Order Date', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'metrics',
			),
			'last_order_id'       => array(
				'label' => __( 'Last Order ID', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'metrics',
			),
			'avg_order_value'     => array(
				'label' => __( 'Avg Order Value', 'rawtype-order-exporter-woocommerce' ),
				'group' => 'metrics',
			),
		);

		/**
		 * Filter available customer columns.
		 *
		 * @param array $columns Column definitions.
		 */
		return apply_filters( 'rawtype_oe_columns_customers_data_source', $columns );
	}

	/**
	 * Fetch customers (registered + optionally guests).
	 *
	 * @param array $args {
	 *     Query arguments.
	 *     @type int  $limit          Number of results.
	 *     @type int  $paged          Page number.
	 *     @type bool $include_guests Whether to include guest customers.
	 *     @type string $return       Return type ('objects' for mixed array).
	 * }
	 * @return array Mixed array of WC_Customer objects and guest arrays.
	 */
	public function fetch( $args ) {
		$include_guests   = ! empty( $args['include_guests'] );
		$limit            = isset( $args['limit'] ) ? (int) $args['limit'] : 500;
		$paged            = isset( $args['paged'] ) ? absint( $args['paged'] ) : 1;
		$offset           = isset( $args['offset'] ) ? max( 0, absint( $args['offset'] ) ) : max( 0, ( $paged - 1 ) * max( 0, $limit ) );
		$customers        = array();
		$include          = Query_Builder::get_customer_include_shortcut_ids(
			$args,
			array( 'include_guests', 'limit', 'paged', 'offset', 'return' )
		);
		$registered_total = $this->count_registered_customers( $args, $include );

		if ( -1 === $limit ) {
			$customers = $this->fetch_registered_customers( $args, $registered_total, 0, $include );

			if ( $include_guests ) {
				$customers = array_merge( $customers, $this->fetch_guest_customers( -1, 0, $args ) );
			}

			return $customers;
		}

		if ( $limit < 1 ) {
			return array();
		}

		if ( $offset < $registered_total ) {
			$registered_limit = min( $limit, max( 0, $registered_total - $offset ) );
			$customers        = $this->fetch_registered_customers( $args, $registered_limit, $offset, $include );
		}

		if ( $include_guests && count( $customers ) < $limit ) {
			$guest_offset = max( 0, $offset - $registered_total );
			$guest_limit  = $limit - count( $customers );

			if ( $guest_limit > 0 ) {
				$customers = array_merge( $customers, $this->fetch_guest_customers( $guest_limit, $guest_offset, $args ) );
			}
		}

		return $customers;
	}

	/**
	 * Count customers matching args.
	 *
	 * @param array $args Query arguments.
	 * @return int Total count.
	 */
	public function count( $args ) {
		$include_guests = ! empty( $args['include_guests'] );
		$include        = Query_Builder::get_customer_include_shortcut_ids(
			$args,
			array( 'include_guests', 'limit', 'paged', 'offset', 'return' )
		);
		$count          = $this->count_registered_customers( $args, $include );

		if ( $include_guests ) {
			$count += $this->count_guest_customers( $args );
		}

		return $count;
	}

	/**
	 * Build WP_User_Query args from export/query-builder args.
	 *
	 * @param array $args       Export/query args.
	 * @param bool  $count_only Whether this is a count query.
	 * @return array
	 */
	private function build_registered_customer_query_args( $args, $count_only = false ) {
		$query_args = is_array( $args ) ? $args : array();
		$limit      = isset( $query_args['number'] ) ? (int) $query_args['number'] : ( isset( $query_args['limit'] ) ? (int) $query_args['limit'] : 500 );
		$paged      = isset( $query_args['paged'] ) ? max( 1, absint( $query_args['paged'] ) ) : 1;
		$offset     = isset( $query_args['offset'] ) ? max( 0, absint( $query_args['offset'] ) ) : max( 0, ( $paged - 1 ) * max( 0, $limit ) );

		unset(
			$query_args['include_guests'],
			$query_args['limit'],
			$query_args['paged'],
			$query_args['return'],
			$query_args['offset'],
			$query_args['number'],
			$query_args['count_total'],
			$query_args['fields'],
			$query_args['rawtype_oe_customer_filters'],
			$query_args['rawtype_oe_customer_logic']
		);

		if (
			! isset( $query_args['include'] )
			&& ! isset( $query_args['role'] )
			&& ! isset( $query_args['role__in'] )
			&& ! isset( $query_args['role__not_in'] )
		) {
			$query_args['role'] = 'customer';
		}

		if ( $count_only ) {
			return array_merge(
				$query_args,
				array(
					'count_total' => true,
					'number'      => 0,
					'fields'      => 'ID',
				)
			);
		}

		if ( empty( $query_args['orderby'] ) ) {
			$query_args['orderby'] = 'registered';
		}

		if ( empty( $query_args['order'] ) ) {
			$query_args['order'] = 'DESC';
		}

		if ( $limit < 1 ) {
			$limit = 500;
		}

		$query_args['number'] = $limit;
		$query_args['offset'] = $offset;

		return $query_args;
	}

	/**
	 * Count registered customers for the current query.
	 *
	 * @param array      $args    Query args.
	 * @param array|null $include Include shortcut IDs.
	 * @return int
	 */
	private function count_registered_customers( $args, $include = null ) {
		if ( null !== $include ) {
			return count( $include );
		}

		if ( ! class_exists( '\\WP_User_Query' ) ) {
			return 0;
		}

		$query = new \WP_User_Query( $this->build_registered_customer_query_args( $args, true ) );

		return (int) $query->get_total();
	}

	/**
	 * Fetch registered customers with a deterministic offset/limit.
	 *
	 * @param array      $args    Query args.
	 * @param int        $limit   Max rows to fetch.
	 * @param int        $offset  Row offset.
	 * @param array|null $include Include shortcut IDs.
	 * @return array
	 */
	private function fetch_registered_customers( $args, $limit, $offset, $include = null ) {
		if ( $limit < 1 ) {
			return array();
		}

		$customers = array();

		if ( null !== $include ) {
			if ( ! class_exists( '\\WC_Customer' ) ) {
				return array();
			}

			$slice = array_slice( $include, $offset, $limit );
			foreach ( $slice as $user_id ) {
				$customers[] = new \WC_Customer( $user_id );
			}

			return $customers;
		}

		if ( ! class_exists( '\\WP_User_Query' ) || ! class_exists( '\\WC_Customer' ) ) {
			return array();
		}

		$query_args           = is_array( $args ) ? $args : array();
		$query_args['number'] = $limit;
		$query_args['offset'] = $offset;
		$user_query           = new \WP_User_Query( $this->build_registered_customer_query_args( $query_args ) );
		$users                = $user_query->get_results();

		if ( ! is_array( $users ) ) {
			return array();
		}

		foreach ( $users as $user ) {
			$user_id     = is_object( $user ) ? $user->ID : (int) $user;
			$customers[] = new \WC_Customer( $user_id );
		}

		return $customers;
	}

	/**
	 * Normalize a mixed array of customers into flat rows.
	 *
	 * Handles both WC_Customer objects (registered) and arrays (guests).
	 *
	 * @param array  $customers Mixed array of WC_Customer and guest arrays.
	 * @param array  $columns   Column IDs to include.
	 * @param string $mode     Unused — kept for interface consistency.
	 * @return array Array of associative arrays (rows).
	 */
	public function normalize( $customers, $columns, $mode = '' ) { // phpcs:ignore Generic.CodeAnalysis.UnusedFunctionParameter.FoundAfterLastUsed
		$rows           = array();
		$last_order_map = $this->build_last_order_map_for_customers( $customers, $columns );

		foreach ( $customers as $customer ) {
			if ( is_object( $customer ) && method_exists( $customer, 'get_id' ) ) {
				$row    = $this->extract_registered_customer( $customer, $columns, $last_order_map );
				$row    = $this->filter_columns( $row, $columns );
				$rows[] = $row;
			} elseif ( is_array( $customer ) ) {
				$row    = $this->filter_columns( $customer, $columns );
				$rows[] = $row;
			}
		}

		return $rows;
	}

	/**
	 * Batch-resolve the most recent order for each registered customer in this
	 * chunk so we avoid one `wc_get_orders()` call per customer during export.
	 *
	 * On stores where customers have hundreds of orders each, the previous
	 * `wc_get_orders('customer_id' => [...], 'limit' => -1)` would load every
	 * order across every customer — easily hundreds of thousands of objects.
	 * We now resolve only the latest order ID per customer via a direct SQL
	 * aggregate, then load just those orders.
	 *
	 * @param array $customers Mixed array of WC_Customer objects and guest arrays.
	 * @param array $columns   Column IDs requested in this export.
	 * @return array Map of customer_id => WC_Order, or empty when not needed.
	 */
	private function build_last_order_map_for_customers( $customers, $columns ) {
		if ( ! $this->columns_require_last_order( $columns ) ) {
			return array();
		}

		$customer_ids = array();
		foreach ( (array) $customers as $customer ) {
			if ( is_object( $customer ) && method_exists( $customer, 'get_id' ) ) {
				$id = absint( $customer->get_id() );
				if ( $id > 0 ) {
					$customer_ids[ $id ] = true;
				}
			}
		}

		if ( empty( $customer_ids ) ) {
			return array();
		}

		$customer_ids = array_keys( $customer_ids );
		$last_ids     = $this->resolve_latest_order_ids_for_customers( $customer_ids );

		if ( empty( $last_ids ) ) {
			return array();
		}

		$orders = wc_get_orders(
			array(
				'include' => array_values( $last_ids ),
				'limit'   => count( $last_ids ),
				'return'  => 'objects',
				'type'    => 'shop_order',
			)
		);

		if ( ! is_array( $orders ) ) {
			return array();
		}

		$map = array();
		foreach ( $orders as $order ) {
			$customer_id = absint( $order->get_customer_id() );
			if ( $customer_id < 1 || isset( $map[ $customer_id ] ) ) {
				continue;
			}
			$map[ $customer_id ] = $order;
		}

		return $map;
	}

	/**
	 * Resolve the latest order ID per customer using a single SQL aggregate.
	 *
	 * @param int[] $customer_ids Customer IDs to resolve.
	 * @return array<int, int>    Map customer_id => order_id.
	 */
	private function resolve_latest_order_ids_for_customers( array $customer_ids ) {
		global $wpdb;

		if ( empty( $customer_ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $customer_ids ), '%d' ) );

		$is_hpos = Query_Builder::is_hpos_orders_table_enabled();

		if ( $is_hpos ) {
			$table = $wpdb->prefix . 'wc_orders';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT customer_id, MAX(id) AS order_id FROM {$table}
					WHERE type = %s AND customer_id IN ({$placeholders})
					GROUP BY customer_id",
					array_merge( array( 'shop_order' ), $customer_ids )
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT CAST(pm.meta_value AS UNSIGNED) AS customer_id, MAX(p.ID) AS order_id
					FROM {$wpdb->posts} p
					INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_customer_user'
					WHERE p.post_type = %s AND CAST(pm.meta_value AS UNSIGNED) IN ({$placeholders})
					GROUP BY CAST(pm.meta_value AS UNSIGNED)",
					array_merge( array( 'shop_order' ), $customer_ids )
				)
			);
		}

		$map = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				$customer_id = absint( $row->customer_id );
				$order_id    = absint( $row->order_id );
				if ( $customer_id > 0 && $order_id > 0 ) {
					$map[ $customer_id ] = $order_id;
				}
			}
		}

		return $map;
	}

	/**
	 * Decide whether the requested columns need last-order metadata resolved.
	 *
	 * @param array $columns Requested column IDs.
	 * @return bool
	 */
	private function columns_require_last_order( $columns ) {
		if ( empty( $columns ) ) {
			return true;
		}

		foreach ( (array) $columns as $column ) {
			if ( 'last_order_id' === $column || 'last_order_date' === $column ) {
				return true;
			}
		}

		return false;
	}

	/**
	 * Extract data from a registered WC_Customer.
	 *
	 * @param \WC_Customer $customer       WC_Customer object.
	 * @param array        $columns        Column IDs to include.
	 * @param array        $last_order_map Customer_id => WC_Order map produced by normalize() to avoid N+1 queries.
	 * @return array Associative array of field => value.
	 */
	private function extract_registered_customer( $customer, $columns, $last_order_map = array() ) { // phpcs:ignore Generic.CodeAnalysis.UnusedFunctionParameter.FoundAfterLastUsed
		$data = array();

		// Basic fields.
		$data['customer_id']     = $customer->get_id();
		$data['username']        = method_exists( $customer, 'get_username' ) ? $customer->get_username() : '';
		$data['email']           = $customer->get_email();
		$data['first_name']      = method_exists( $customer, 'get_first_name' ) ? $customer->get_first_name() : '';
		$data['last_name']       = method_exists( $customer, 'get_last_name' ) ? $customer->get_last_name() : '';
		$data['display_name']    = method_exists( $customer, 'get_display_name' ) ? $customer->get_display_name() : '';
		$date_created            = method_exists( $customer, 'get_date_created' ) ? $customer->get_date_created() : null;
		$data['date_registered'] = $this->format_date( $date_created );
		$data['is_guest']        = __( 'No', 'rawtype-order-exporter-woocommerce' );
		$data['role']            = method_exists( $customer, 'get_role' ) ? $customer->get_role() : '';

		// Billing fields.
		$data['billing_first_name'] = $customer->get_billing_first_name();
		$data['billing_last_name']  = $customer->get_billing_last_name();
		$data['billing_company']    = method_exists( $customer, 'get_billing_company' ) ? $customer->get_billing_company() : '';
		$data['billing_email']      = method_exists( $customer, 'get_billing_email' ) ? $customer->get_billing_email() : '';
		$data['billing_phone']      = $customer->get_billing_phone();
		$data['billing_address_1']  = $customer->get_billing_address_1();
		$data['billing_address_2']  = method_exists( $customer, 'get_billing_address_2' ) ? $customer->get_billing_address_2() : '';
		$data['billing_city']       = $customer->get_billing_city();
		$data['billing_state']      = $customer->get_billing_state();
		$data['billing_postcode']   = $customer->get_billing_postcode();
		$data['billing_country']    = $customer->get_billing_country();

		// Shipping fields.
		$data['shipping_first_name'] = method_exists( $customer, 'get_shipping_first_name' ) ? $customer->get_shipping_first_name() : '';
		$data['shipping_last_name']  = method_exists( $customer, 'get_shipping_last_name' ) ? $customer->get_shipping_last_name() : '';
		$data['shipping_company']    = method_exists( $customer, 'get_shipping_company' ) ? $customer->get_shipping_company() : '';
		$data['shipping_address_1']  = method_exists( $customer, 'get_shipping_address_1' ) ? $customer->get_shipping_address_1() : '';
		$data['shipping_address_2']  = method_exists( $customer, 'get_shipping_address_2' ) ? $customer->get_shipping_address_2() : '';
		$data['shipping_city']       = method_exists( $customer, 'get_shipping_city' ) ? $customer->get_shipping_city() : '';
		$data['shipping_state']      = method_exists( $customer, 'get_shipping_state' ) ? $customer->get_shipping_state() : '';
		$data['shipping_postcode']   = method_exists( $customer, 'get_shipping_postcode' ) ? $customer->get_shipping_postcode() : '';
		$data['shipping_country']    = method_exists( $customer, 'get_shipping_country' ) ? $customer->get_shipping_country() : '';

		// Purchase metrics.
		$order_count             = method_exists( $customer, 'get_order_count' ) ? $customer->get_order_count() : 0;
		$total_spent             = method_exists( $customer, 'get_total_spent' ) ? (float) $customer->get_total_spent() : 0.0;
		$data['orders_count']    = $order_count;
		$data['total_spent']     = $total_spent;
		$data['avg_order_value'] = $order_count > 0 ? round( $total_spent / $order_count, 2 ) : 0.00;

		// Last order data — resolved from the batch map built upstream in normalize().
		$data['last_order_date'] = '';
		$data['last_order_id']   = '';

		$customer_id = absint( $customer->get_id() );
		$last_order  = isset( $last_order_map[ $customer_id ] ) ? $last_order_map[ $customer_id ] : null;

		if ( $last_order ) {
			$data['last_order_id'] = $last_order->get_id();
			$date_created          = $last_order->get_date_created();
			if ( null !== $date_created && is_object( $date_created ) ) {
				$data['last_order_date'] = $date_created->date( 'Y-m-d H:i:s' );
			}
		}

		$data = $this->append_custom_meta_data( $data, $customer, $columns );

		return $data;
	}

	/**
	 * Retrieve meta value using get_user_meta() instead of WC_Data::get_meta().
	 *
	 * @param object $item     The WC_Customer object.
	 * @param string $meta_key The meta key to retrieve.
	 * @return mixed
	 */
	private function get_meta_value( $item, $meta_key ) {
		if ( ! method_exists( $item, 'get_id' ) ) {
			return '';
		}

		$user_id = absint( $item->get_id() );
		if ( $user_id < 1 ) {
			return '';
		}

		return get_user_meta( $user_id, $meta_key, true );
	}

	/**
	 * Fetch guest customers aggregated by billing_email.
	 *
	 * @param int $limit  Max results.
	 * @param int $offset Offset for pagination.
	 * @return array Array of guest record arrays.
	 */
	private function fetch_guest_customers( $limit, $offset, $args = array() ) {
		$guests = $this->get_guest_customer_dataset( $args );

		if ( empty( $guests ) ) {
			return array();
		}

		if ( -1 === $limit ) {
			return array_slice( $guests, $offset );
		}

		return array_slice( $guests, $offset, max( 0, $limit ) );
	}

	/**
	 * Count unique guest customers.
	 *
	 * @return int Number of unique guest email addresses.
	 */
	private function count_guest_customers( $args = array() ) {
		return count( $this->get_guest_customer_dataset( $args ) );
	}

	/**
	 * Build and cache guest customer records for the current request.
	 *
	 * Previously this iterated every guest order via `wc_get_orders` in pages
	 * of 250 until exhausted. On stores with hundreds of thousands of guest
	 * orders that loop alone took minutes. We now aggregate guests directly
	 * from the order tables (HPOS or legacy CPT) and only hydrate full
	 * WC_Order objects for the per-email "last order" — capped at GUEST_LIMIT.
	 *
	 * @return array
	 */
	private function get_guest_customer_dataset( $args = array() ) {
		static $base_dataset = null;

		if ( null !== $base_dataset ) {
			return $this->filter_guest_customer_dataset( $base_dataset, $args );
		}

		$rows = $this->aggregate_guest_orders();

		$base_dataset = array();

		if ( ! empty( $rows ) ) {
			$last_order_ids = array();
			foreach ( $rows as $row ) {
				if ( ! empty( $row['last_order_id'] ) ) {
					$last_order_ids[] = (int) $row['last_order_id'];
				}
			}

			$last_order_map = array();
			if ( ! empty( $last_order_ids ) ) {
				$last_orders = wc_get_orders(
					array(
						'include' => $last_order_ids,
						'limit'   => count( $last_order_ids ),
						'return'  => 'objects',
						'type'    => 'shop_order',
					)
				);

				if ( is_array( $last_orders ) ) {
					foreach ( $last_orders as $order ) {
						$last_order_map[ $order->get_id() ] = $order;
					}
				}
			}

			foreach ( $rows as $row ) {
				$email = sanitize_email( (string) $row['email'] );
				if ( '' === $email ) {
					continue;
				}

				$last_order = isset( $row['last_order_id'], $last_order_map[ $row['last_order_id'] ] )
					? $last_order_map[ $row['last_order_id'] ]
					: null;

				$base_dataset[] = $this->build_guest_record(
					$email,
					array(
						'orders_count' => (int) $row['orders_count'],
						'total_spent'  => (float) $row['total_spent'],
						'last_order'   => $last_order,
					),
					$last_order
				);
			}
		}

		return $this->filter_guest_customer_dataset( $base_dataset, $args );
	}

	/**
	 * Aggregate guest orders directly from the order storage layer.
	 *
	 * Returns one row per unique billing_email with order_count, total_spent
	 * and the most recent order ID, capped to GUEST_LIMIT entries.
	 *
	 * @return array<int, array{email:string, orders_count:int, total_spent:float, last_order_id:int}>
	 */
	private function aggregate_guest_orders() {
		global $wpdb;

		$limit = max( 1, (int) self::GUEST_LIMIT );

		$is_hpos = Query_Builder::is_hpos_orders_table_enabled();

		if ( $is_hpos ) {
			$orders_table    = $wpdb->prefix . 'wc_orders';
			$addresses_table = $wpdb->prefix . 'wc_order_addresses';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.email AS email,
							COUNT(o.id) AS orders_count,
							SUM(o.total_amount) AS total_spent,
							MAX(o.id) AS last_order_id
					FROM {$orders_table} o
					INNER JOIN {$addresses_table} a ON a.order_id = o.id AND a.address_type = 'billing'
					WHERE o.type = %s AND o.customer_id = 0 AND a.email <> ''
					GROUP BY a.email
					ORDER BY last_order_id DESC
					LIMIT %d",
					'shop_order',
					$limit
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT email_meta.meta_value AS email,
							COUNT(p.ID) AS orders_count,
							SUM(CAST(total_meta.meta_value AS DECIMAL(20,6))) AS total_spent,
							MAX(p.ID) AS last_order_id
					FROM {$wpdb->posts} p
					INNER JOIN {$wpdb->postmeta} customer_meta
						ON p.ID = customer_meta.post_id AND customer_meta.meta_key = '_customer_user'
					INNER JOIN {$wpdb->postmeta} email_meta
						ON p.ID = email_meta.post_id AND email_meta.meta_key = '_billing_email'
					LEFT JOIN {$wpdb->postmeta} total_meta
						ON p.ID = total_meta.post_id AND total_meta.meta_key = '_order_total'
					WHERE p.post_type = %s
						AND CAST(customer_meta.meta_value AS UNSIGNED) = 0
						AND email_meta.meta_value <> ''
					GROUP BY email_meta.meta_value
					ORDER BY last_order_id DESC
					LIMIT %d",
					'shop_order',
					$limit
				),
				ARRAY_A
			);
		}

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Apply supported customer filters to the guest dataset.
	 *
	 * @param array $dataset Guest customer records.
	 * @param array $args    Query arguments.
	 * @return array
	 */
	private function filter_guest_customer_dataset( $dataset, $args ) {
		$filters = isset( $args['rawtype_oe_customer_filters'] ) && is_array( $args['rawtype_oe_customer_filters'] )
			? $args['rawtype_oe_customer_filters']
			: array();
		$logic   = isset( $args['rawtype_oe_customer_logic'] ) ? sanitize_key( $args['rawtype_oe_customer_logic'] ) : 'and';

		if ( empty( $filters ) ) {
			return $dataset;
		}

		$filtered = array();
		foreach ( (array) $dataset as $record ) {
			if ( ! is_array( $record ) ) {
				continue;
			}

			if ( $this->guest_record_matches_filters( $record, $filters, $logic ) ) {
				$filtered[] = $record;
			}
		}

		return $filtered;
	}

	/**
	 * Evaluate the guest record against supported customer filters.
	 *
	 * @param array  $record  Guest customer record.
	 * @param array  $filters Raw customer filters.
	 * @param string $logic   Filter logic.
	 * @return bool
	 */
	private function guest_record_matches_filters( $record, $filters, $logic ) {
		$logic         = 'or' === $logic ? 'or' : 'and';
		$effective     = 0;
		$matched_count = 0;

		foreach ( (array) $filters as $filter ) {
			if ( ! is_array( $filter ) || empty( $filter['type'] ) ) {
				continue;
			}

			$type    = sanitize_key( $filter['type'] );
			$matched = null;

			switch ( $type ) {
				case 'customer_total_spend':
					$matched = $this->matches_numeric_filter(
						isset( $record['total_spent'] ) ? (float) $record['total_spent'] : 0.0,
						$filter
					);
					break;

				case 'customer_orders_count':
					$matched = $this->matches_numeric_filter(
						isset( $record['orders_count'] ) ? (float) $record['orders_count'] : 0.0,
						$filter
					);
					break;

				case 'date':
					$matched = $this->matches_guest_date_filter( $record, $filter );
					break;
			}

			if ( null === $matched ) {
				continue;
			}

			++$effective;
			if ( $matched ) {
				++$matched_count;
				if ( 'or' === $logic ) {
					return true;
				}
			} elseif ( 'and' === $logic ) {
				return false;
			}
		}

		if ( 0 === $effective ) {
			return true;
		}

		return 'and' === $logic ? $matched_count === $effective : false;
	}

	/**
	 * Evaluate a numeric customer filter against a guest metric.
	 *
	 * @param float $actual Metric value.
	 * @param array $filter Filter definition.
	 * @return bool|null
	 */
	private function matches_numeric_filter( $actual, $filter ) {
		$operator = isset( $filter['operator'] ) ? sanitize_text_field( $filter['operator'] ) : '=';
		$value    = isset( $filter['value'] ) ? $filter['value'] : '';

		switch ( $operator ) {
			case 'between':
				if ( ! is_array( $value ) || 2 !== count( $value ) ) {
					return null;
				}

				return $actual >= (float) $value[0] && $actual <= (float) $value[1];

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

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

			case '>=':
				return $actual >= (float) $value;

			case '<=':
				return $actual <= (float) $value;

			case '=':
				return $actual === (float) $value;
		}

		return null;
	}

	/**
	 * Evaluate guest date filters that depend on order history.
	 *
	 * @param array $record Guest customer record.
	 * @param array $filter Filter definition.
	 * @return bool|null
	 */
	private function matches_guest_date_filter( $record, $filter ) {
		$field = isset( $filter['field'] ) ? sanitize_key( $filter['field'] ) : 'date_registered';

		if ( 'date_registered' === $field ) {
			return false;
		}

		if ( 'last_order_date' !== $field ) {
			return null;
		}

		$actual = isset( $record['last_order_date'] ) ? (string) $record['last_order_date'] : '';
		if ( '' === $actual ) {
			return false;
		}

		$actual_raw_ts = strtotime( $actual );
		if ( false === $actual_raw_ts ) {
			return false;
		}

		$actual_ts = strtotime( gmdate( 'Y-m-d', $actual_raw_ts ) );
		if ( false === $actual_ts ) {
			return false;
		}

		$operator = isset( $filter['operator'] ) ? sanitize_key( $filter['operator'] ) : 'between';
		$value    = isset( $filter['value'] ) ? $filter['value'] : '';

		switch ( $operator ) {
			case 'is':
				$target = is_string( $value ) ? strtotime( $value ) : false;
				return false !== $target && $actual_ts === strtotime( gmdate( 'Y-m-d', $target ) );

			case 'after':
				$target = is_string( $value ) ? strtotime( $value ) : false;
				return false !== $target && $actual_ts > strtotime( gmdate( 'Y-m-d', $target ) );

			case 'before':
				$target = is_string( $value ) ? strtotime( $value ) : false;
				return false !== $target && $actual_ts < strtotime( gmdate( 'Y-m-d', $target ) );

			case 'between':
				if ( ! is_array( $value ) || 2 !== count( $value ) ) {
					return null;
				}

				$start = is_string( $value[0] ) ? strtotime( $value[0] ) : false;
				$end   = is_string( $value[1] ) ? strtotime( $value[1] ) : false;

				if ( false === $start || false === $end ) {
					return null;
				}

				$start = strtotime( gmdate( 'Y-m-d', $start ) );
				$end   = strtotime( gmdate( 'Y-m-d', $end ) );

				return $actual_ts >= $start && $actual_ts <= $end;
		}

		return null;
	}

	/**
	 * Build a virtual guest customer record from order data.
	 *
	 * @param string    $email       Guest email address.
	 * @param array     $data        Aggregated data (orders, total_spent, last_order).
	 * @param \WC_Order $last_order  Most recent order.
	 * @return array Normalized customer record.
	 */
	private function build_guest_record( $email, $data, $last_order ) {
		$orders_count = isset( $data['orders_count'] )
			? absint( $data['orders_count'] )
			: count( isset( $data['orders'] ) && is_array( $data['orders'] ) ? $data['orders'] : array() );
		$total_spent  = isset( $data['total_spent'] ) ? (float) $data['total_spent'] : 0.0;
		$avg_value    = $orders_count > 0 ? round( $total_spent / $orders_count, 2 ) : 0.00;

		$record = array(
			'customer_id'         => 'guest-' . md5( $email ),
			'username'            => '',
			'email'               => $email,
			'first_name'          => $last_order ? $last_order->get_billing_first_name() : '',
			'last_name'           => $last_order ? $last_order->get_billing_last_name() : '',
			'display_name'        => '',
			'date_registered'     => '',
			'is_guest'            => __( 'Yes', 'rawtype-order-exporter-woocommerce' ),
			'role'                => 'guest',
			'billing_first_name'  => $last_order ? $last_order->get_billing_first_name() : '',
			'billing_last_name'   => $last_order ? $last_order->get_billing_last_name() : '',
			'billing_company'     => $last_order ? $last_order->get_billing_company() : '',
			'billing_email'       => $last_order ? $last_order->get_billing_email() : $email,
			'billing_phone'       => $last_order ? $last_order->get_billing_phone() : '',
			'billing_address_1'   => $last_order ? $last_order->get_billing_address_1() : '',
			'billing_address_2'   => $last_order ? $last_order->get_billing_address_2() : '',
			'billing_city'        => $last_order ? $last_order->get_billing_city() : '',
			'billing_state'       => $last_order ? $last_order->get_billing_state() : '',
			'billing_postcode'    => $last_order ? $last_order->get_billing_postcode() : '',
			'billing_country'     => $last_order ? $last_order->get_billing_country() : '',
			'shipping_first_name' => $last_order ? $last_order->get_shipping_first_name() : '',
			'shipping_last_name'  => $last_order ? $last_order->get_shipping_last_name() : '',
			'shipping_company'    => method_exists( $last_order, 'get_shipping_company' ) ? $last_order->get_shipping_company() : '',
			'shipping_address_1'  => $last_order ? $last_order->get_shipping_address_1() : '',
			'shipping_address_2'  => $last_order ? $last_order->get_shipping_address_2() : '',
			'shipping_city'       => $last_order ? $last_order->get_shipping_city() : '',
			'shipping_state'      => $last_order ? $last_order->get_shipping_state() : '',
			'shipping_postcode'   => $last_order ? $last_order->get_shipping_postcode() : '',
			'shipping_country'    => $last_order ? $last_order->get_shipping_country() : '',
			'orders_count'        => $orders_count,
			'total_spent'         => $total_spent,
			'last_order_date'     => '',
			'last_order_id'       => '',
			'avg_order_value'     => $avg_value,
		);

		if ( $last_order ) {
			$record['last_order_id'] = $last_order->get_id();
			$last_date               = $last_order->get_date_created();

			if ( null !== $last_date && is_object( $last_date ) ) {
				$record['last_order_date'] = $last_date->date( 'Y-m-d H:i:s' );
			}
		}

		return $record;
	}
}
