<?php
/**
 * Customer table columns on the Users screen.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\UserAddressColumns;

use SmartWP\AdminPowerups\Support\Cleanup;
use SmartWP\AdminPowerups\Support\Assets;
use Automattic\WooCommerce\Utilities\OrderUtil;
use SmartWP\AdminPowerups\Modules\AbstractModule;
use SmartWP\AdminPowerups\Support\CustomerAddress;
use SmartWP\AdminPowerups\Support\Orders;

defined( 'ABSPATH' ) || exit;

/**
 * Adds WooCommerce customer columns to Users → All Users.
 *
 * @since 1.0.0
 */
class Module extends AbstractModule {

	/**
	 * {@inheritDoc}
	 */
	public function id() {
		return 'user-address-columns';
	}

	/**
	 * {@inheritDoc}
	 */
	public function title() {
		return __( 'Customer table columns', 'admin-powerups-for-woocommerce' );
	}

	/**
	 * {@inheritDoc}
	 */
	public function description() {
		return __( 'Add customer details, orders and spend to the Users list. Pick the columns and drag them into order.', 'admin-powerups-for-woocommerce' );
	}

	/**
	 * {@inheritDoc}
	 */
	public function category() {
		return 'customers';
	}

	/**
	 * {@inheritDoc}
	 */
	public function autoload_settings() {
		return false; // Only read on wp-admin screens.
	}

	/**
	 * {@inheritDoc}
	 */
	public function icon() {
		return 'columns-3';
	}

	/**
	 * {@inheritDoc}
	 */
	public function location() {
		return __( 'Users list', 'admin-powerups-for-woocommerce' );
	}

	/**
	 * {@inheritDoc} Also forgets looked-up orders and stats.
	 */
	public function refresh() {
		parent::refresh();
		$this->last_order_ids = array();
		$this->last_orders    = array();
		$this->stats          = array();
	}

	/**
	 * Column labels, in their default order.
	 *
	 * @since 1.0.0
	 * @return array<string, string> Key => label.
	 */
	public function columns() {
		$columns = array(
			'billing'         => __( 'Billing address', 'admin-powerups-for-woocommerce' ),
			'shipping'        => __( 'Shipping address', 'admin-powerups-for-woocommerce' ),
			'phone'           => __( 'Billing phone', 'admin-powerups-for-woocommerce' ),
			'billing_email'   => __( 'Billing email', 'admin-powerups-for-woocommerce' ),
			'billing_city'    => __( 'Billing city', 'admin-powerups-for-woocommerce' ),
			'billing_country' => __( 'Billing country', 'admin-powerups-for-woocommerce' ),
			'shipping_city'   => __( 'Shipping city', 'admin-powerups-for-woocommerce' ),
			'since'           => __( 'Customer since', 'admin-powerups-for-woocommerce' ),
			'orders'          => __( 'Orders', 'admin-powerups-for-woocommerce' ),
			'spent'           => __( 'Lifetime spend', 'admin-powerups-for-woocommerce' ),
			'average'         => __( 'Average order', 'admin-powerups-for-woocommerce' ),
			'last_order'      => __( 'Last order', 'admin-powerups-for-woocommerce' ),
		);

		/**
		 * Filter the columns merchants can pick (key => label). Add-ons add their own here and
		 * print them with `apwc_customer_column_value`. Keys are stored in settings, so keep them stable.
		 *
		 * @since 1.0.0
		 * @param array<string, string> $columns Columns.
		 */
		return (array) apply_filters( 'apwc_customer_columns', $columns );
	}

	/**
	 * {@inheritDoc}
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function data() {
		return array_merge(
			parent::data(),
			array( Cleanup::option( 'found-columns', self::SEEN, __( 'Found columns', 'admin-powerups-for-woocommerce' ), __( 'WordPress and plugin columns found on the Users list, and which ones you hid. They are found again next time; hidden columns show again until you untick them.', 'admin-powerups-for-woocommerce' ) ) )
		);
	}

	/**
	 * {@inheritDoc}
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function fields() {
		$others  = $this->other_columns();
		$options = $this->columns();
		$badges  = array();
		foreach ( $others as $key => $label ) {
			$options[ self::OTHER . $key ] = $label;
			$badges[ self::OTHER . $key ]  = in_array( $key, self::CORE, true ) ? __( 'WordPress', 'admin-powerups-for-woocommerce' ) : __( 'Plugin', 'admin-powerups-for-woocommerce' );
		}

		return array(
			array(
				'id'          => 'columns',
				'type'        => 'sortable',
				'label'       => __( 'Columns', 'admin-powerups-for-woocommerce' ),
				'description' => $others
					? __( 'Tick the columns to show and drag them into order. Unticking a WordPress or plugin column hides it for all staff.', 'admin-powerups-for-woocommerce' )
					: ( $this->is_enabled()
						? __( 'Tick the columns to show and drag them into order. WordPress and plugin columns are added here once the Users list has been checked.', 'admin-powerups-for-woocommerce' )
						: __( 'Tick the columns to show and drag them into order. WordPress and plugin columns show up here once this power-up is on.', 'admin-powerups-for-woocommerce' ) ),
				'default'     => array( 'billing', 'phone', 'orders' ),
				'options'     => $options,
				'badges'      => $badges,
				// Only our own columns are judged; other plugins' columns are never guessed at.
				'slow'        => $this->slow_columns(),
				'warning'     => $this->slow_columns()
					? __( 'Without High-performance order storage, the order columns search all order data for each page of users, which can slow the Users list on stores with many orders. Turning on High-performance order storage in WooCommerce → Settings → Advanced → Features makes them fast.', 'admin-powerups-for-woocommerce' )
					: '',
			),
			array(
				'id'          => 'compact',
				'type'        => 'toggle',
				'label'       => __( 'Short addresses', 'admin-powerups-for-woocommerce' ),
				'description' => __( 'Address columns show city, state and country only.', 'admin-powerups-for-woocommerce' ),
				'default'     => false,
			),
		);
	}

	/**
	 * {@inheritDoc} Columns not offered right now (an add-on's, like Customer tags while it's off)
	 * aren't in the form, so saving keeps them where they were instead of dropping them.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $field Field schema.
	 * @param mixed                $value Submitted value.
	 * @return mixed
	 */
	protected function sanitize_field( array $field, $value ) {
		$clean = parent::sanitize_field( $field, $value );
		if ( 'columns' !== $field['id'] || ! is_array( $clean ) ) {
			return $clean;
		}
		$before  = array_values( array_map( 'strval', (array) ( parent::settings()['columns'] ?? array() ) ) ); // As saved, before arranging.
		$offered = array_map( 'strval', array_keys( (array) ( $field['options'] ?? array() ) ) );
		$out     = array_values( $clean );
		foreach ( $before as $pos => $key ) {
			if ( in_array( $key, $offered, true ) || in_array( $key, $out, true ) ) {
				continue;
			}
			// Put it back after the nearest column that was before it.
			$at = 0;
			for ( $i = $pos - 1; $i >= 0; $i-- ) {
				$found = array_search( $before[ $i ], $out, true );
				if ( false !== $found ) {
					$at = $found + 1;
					break;
				}
			}
			array_splice( $out, $at, 0, array( $key ) );
		}
		return $out;
	}

	/**
	 * Our columns that can slow the Users list.
	 *
	 * Details come from user meta, which the Users list loads for the whole page in one query, so
	 * they cost nothing extra. Order columns share one grouped query per page: fast on the HPOS
	 * orders table (indexed by customer and status), but on legacy storage it has to match
	 * customer IDs in `wp_postmeta.meta_value`, which has no index.
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public function slow_columns() {
		if ( Orders::hpos() ) {
			return array();
		}
		return self::order_columns();
	}

	/**
	 * {@inheritDoc}
	 *
	 * @since 1.0.0
	 * @param string $tab Tab on screen.
	 * @return void
	 */
	public function admin_screen( $tab ) {
		$this->enqueue_detect( $this );
	}

	/**
	 * {@inheritDoc}
	 */
	public function boot() {
		// Last, so columns every other plugin added are there to find and arrange.
		add_filter( 'manage_users_columns', array( $this, 'add_columns' ), PHP_INT_MAX );
		add_action( 'load-users.php', array( $this, 'maybe_detect' ), PHP_INT_MAX );
		add_filter( 'manage_users_custom_column', array( $this, 'render_column' ), 10, 3 );
		add_action( 'admin_enqueue_scripts', array( $this, 'enqueue' ) );
	}

	/**
	 * Enqueue column styles on the Users screen.
	 *
	 * @since 1.0.0
	 * @param string $hook Admin page hook.
	 * @return void
	 */
	public function enqueue( $hook ) {
		if ( 'users.php' === $hook ) {
			wp_enqueue_style( 'apwc-modules', Assets::url( 'assets/css/modules.css' ), array(), Assets::ver( 'assets/css/modules.css' ) );
		}
	}

	/**
	 * Option holding columns seen on the Users list (other than ours) and the ones hidden.
	 * Not autoloaded: only read on the Users list and our settings.
	 */
	const SEEN = 'apwc_user_columns';

	/**
	 * Settings key prefix for columns we don't add (WordPress's and other plugins').
	 */
	const OTHER = 'col:';

	/**
	 * Query arg for the background column check.
	 */
	const DETECT = 'apwc_detect_columns';

	/**
	 * WordPress's own Users list columns.
	 */
	const CORE = array( 'name', 'email', 'role', 'posts' );

	/**
	 * Columns that always stay first (row checkbox, and Username, which carries the row actions).
	 */
	const FIXED = array( 'cb', 'username' );

	/**
	 * Columns seen on the Users list: key => label, in table order.
	 *
	 * @since 1.0.0
	 * @return array<string, string>
	 */
	public function other_columns() {
		return (array) ( $this->seen()['seen'] ?? array() );
	}

	/**
	 * Stored discovery data.
	 *
	 * @since 1.0.0
	 * @return array{seen: array<string, string>, hidden: string[]}
	 */
	private function seen() {
		$data = get_option( self::SEEN, array() );
		return array(
			'seen'   => (array) ( $data['seen'] ?? array() ),
			'hidden' => array_map( 'strval', (array) ( $data['hidden'] ?? array() ) ),
		);
	}

	/**
	 * {@inheritDoc} The column order is resolved against what's on the Users list: columns seen since
	 * the last save (and not hidden) are added at the end, shown.
	 */
	public function settings() {
		$settings            = parent::settings();
		$settings['columns'] = $this->arrange( (array) $settings['columns'] );
		return $settings;
	}

	/**
	 * Full column order: saved order, plus WordPress and plugin columns that aren't hidden.
	 *
	 * Before the merchant has arranged WordPress/plugin columns, keep the classic layout:
	 * our columns right after Email.
	 *
	 * @since 1.0.0
	 * @param string[] $saved Saved (ticked) keys in order.
	 * @return string[]
	 */
	private function arrange( array $saved ) {
		$data   = $this->seen();
		$ours   = $this->columns();
		$others = array();
		foreach ( array_keys( $data['seen'] ) as $key ) {
			$others[] = self::OTHER . $key;
		}

		$saved = array_values(
			array_filter(
				array_map( 'strval', $saved ),
				static fn( $key ) => isset( $ours[ $key ] ) || in_array( $key, $others, true )
			)
		);
		$shown = array_values( array_diff( $others, $data['hidden'], $saved ) );

		if ( array_filter( $saved, static fn( $key ) => 0 === strpos( $key, self::OTHER ) ) ) {
			return array_merge( $saved, $shown );
		}

		// Not arranged yet: ours go after Email, like before.
		$email = array_search( self::OTHER . 'email', $shown, true );
		if ( false === $email ) {
			return array_merge( $saved, $shown );
		}
		return array_merge( array_slice( $shown, 0, $email + 1 ), $saved, array_slice( $shown, $email + 1 ) );
	}

	/**
	 * {@inheritDoc} Also remembers which WordPress/plugin columns were unticked, so they stay hidden
	 * while columns that show up later are shown until arranged.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $input Submitted settings.
	 * @return array<string, mixed> Saved settings.
	 */
	public function save_settings( array $input ) {
		parent::save_settings( $input );
		$data   = $this->seen();
		$ticked = (array) parent::settings()['columns']; // As saved, before arranging.
		$hidden = array();
		foreach ( array_keys( $data['seen'] ) as $key ) {
			if ( ! in_array( self::OTHER . $key, $ticked, true ) ) {
				$hidden[] = self::OTHER . $key;
			}
		}
		$data['hidden'] = $hidden;
		update_option( self::SEEN, $data, false );
		return $this->settings();
	}

	/**
	 * Remember the columns WordPress and other plugins put on the Users list, for our settings.
	 * Written only when they change, and only for staff who can change our settings.
	 *
	 * @since 1.0.0
	 * @param array<string, string> $columns Columns before we arrange them.
	 * @return void
	 */
	private function discover( array $columns ) {
		if ( ! current_user_can( 'manage_woocommerce' ) || ! $this->is_plain_view() ) {
			return;
		}
		$found = array();
		foreach ( $columns as $key => $label ) {
			$key = (string) $key;
			if ( in_array( $key, self::FIXED, true ) || 0 === strpos( $key, 'apwc_' ) ) {
				continue;
			}
			$text          = trim( wp_strip_all_tags( (string) $label ) );
			$found[ $key ] = '' === $text ? $key : $text;
		}

		$data = $this->seen();
		if ( $found === $data['seen'] ) {
			return;
		}
		$data['seen'] = $found;
		update_option( self::SEEN, $data, false );
	}

	/**
	 * Whether the Users list is shown unfiltered. Searched or role-filtered views can carry fewer
	 * columns, so only the plain view (any page or sort) is used to learn columns.
	 *
	 * @since 1.0.0
	 * @return bool
	 */
	private function is_plain_view() {
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- read-only check of which args are present.
		$extra = array_diff( array_keys( $_GET ), array( 'paged', 'orderby', 'order', self::DETECT, '_apwc' ) );
		return ! $extra;
	}

	/**
	 * Background column check (like Admin Columns' "default headings" request): our settings screen
	 * loads `users.php?apwc_detect_columns=1` quietly, we build the Users table's columns the way the
	 * screen does, record them, and answer with JSON instead of drawing the page.
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public function maybe_detect() {
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- verified below.
		if ( empty( $_GET[ self::DETECT ] ) ) {
			return;
		}
		$nonce = isset( $_GET['_apwc'] ) ? sanitize_text_field( wp_unslash( $_GET['_apwc'] ) ) : '';
		if ( ! current_user_can( 'manage_woocommerce' ) || ! wp_verify_nonce( $nonce, self::DETECT ) ) {
			wp_send_json_error( null, 403 );
		}

		$before = $this->other_columns();
		ob_start(); // Other plugins may print while the table is set up.
		$screen = get_current_screen();
		$table  = _get_list_table( 'WP_Users_List_Table', array( 'screen' => $screen ? $screen->id : 'users' ) );
		if ( $table && $screen ) {
			get_column_headers( $screen ); // Runs manage_users_columns, including our discover().
		}
		ob_end_clean();

		$after = $this->other_columns();
		wp_send_json_success(
			array(
				'changed' => $after !== $before,
				'columns' => count( $after ),
			)
		);
	}

	/**
	 * Background column check URL.
	 *
	 * @since 1.0.0
	 * @return string
	 */
	public function detect_url() {
		return add_query_arg(
			array(
				self::DETECT => 1,
				'_apwc'      => wp_create_nonce( self::DETECT ),
			),
			admin_url( 'users.php' )
		);
	}

	/**
	 * Start the background check when our settings open.
	 *
	 * @since 1.0.0
	 * @param \SmartWP\AdminPowerups\Modules\AbstractModule|null $module Module on screen.
	 * @return void
	 */
	public function enqueue_detect( $module ) {
		// The check needs this power-up's hook on the Users list, which only runs while it's on.
		if ( $module !== $this || ! $this->is_enabled() || ! did_action( 'admin_enqueue_scripts' ) ) {
			return;
		}
		wp_enqueue_script( 'apwc-user-columns', Assets::url( 'assets/js/user-columns.js' ), array( 'apwc-admin' ), Assets::ver( 'assets/js/user-columns.js' ), true );
		wp_localize_script( 'apwc-user-columns', 'apwcUserColumns', array( 'detectUrl' => $this->detect_url() ) );
	}

	/**
	 * Arrange the Users list: checkbox and Username first, then every ticked column in the saved order.
	 * Unticked WordPress/plugin columns are removed; ones not seen before go at the end.
	 *
	 * @since 1.0.0
	 * @param array<string, string> $columns Columns.
	 * @return array<string, string>
	 */
	public function add_columns( $columns ) {
		$columns = (array) $columns;
		$this->discover( $columns );

		$ours   = $this->columns();
		$hidden = $this->seen()['hidden'];
		$out    = array();
		foreach ( self::FIXED as $key ) {
			if ( isset( $columns[ $key ] ) ) {
				$out[ $key ] = $columns[ $key ];
			}
		}

		foreach ( $this->arrange( (array) parent::settings()['columns'] ) as $key ) {
			if ( isset( $ours[ $key ] ) ) {
				if ( $this->can_view( $key ) ) {
					$out[ 'apwc_' . $key ] = $ours[ $key ];
				}
				continue;
			}
			$other = substr( $key, strlen( self::OTHER ) );
			if ( isset( $columns[ $other ] ) ) {
				$out[ $other ] = $columns[ $other ];
			}
		}

		foreach ( $columns as $key => $label ) {
			if ( ! isset( $out[ $key ] ) && ! in_array( self::OTHER . $key, $hidden, true ) ) {
				$out[ $key ] = $label;
			}
		}
		return $out;
	}

	/**
	 * Columns built from orders.
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public static function order_columns() {
		return array( 'orders', 'spent', 'average', 'last_order' );
	}

	/**
	 * Whether the current user may see a column. Anyone who can open the Users list (list_users)
	 * may be looking, so customer details need manage_woocommerce, and order columns
	 * manage_woocommerce or edit_shop_orders. Add-on columns default to manage_woocommerce.
	 *
	 * @since 1.0.0
	 * @param string $key Column key (without the apwc_ prefix).
	 * @return bool
	 */
	public function can_view( $key ) {
		$key = (string) $key;
		if ( 'since' === $key ) {
			$caps = array(); // The account's registration date: WordPress data the Users list already offers.
		} elseif ( in_array( $key, self::order_columns(), true ) ) {
			$caps = array( 'manage_woocommerce', 'edit_shop_orders' );
		} else {
			$caps = array( 'manage_woocommerce' );
		}

		/**
		 * Filter the capabilities that let a user see a Customer table column on the Users list.
		 * Any one of them is enough; an empty list shows the column to everyone who can list users.
		 *
		 * @since 1.0.0
		 * @param string[] $caps Capabilities (any of).
		 * @param string   $key  Column key (as in `apwc_customer_columns`).
		 */
		$caps = (array) apply_filters( 'apwc_customer_column_capabilities', $caps, $key );
		if ( ! $caps ) {
			return true;
		}
		foreach ( $caps as $cap ) {
			if ( is_string( $cap ) && '' !== $cap && current_user_can( $cap ) ) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Render a column.
	 *
	 * @since 1.0.0
	 * @param string $output  Current output.
	 * @param string $column  Column key.
	 * @param int    $user_id User ID.
	 * @return string
	 */
	public function render_column( $output, $column, $user_id ) {
		if ( 0 !== strpos( $column, 'apwc_' ) ) {
			return $output;
		}
		$key     = substr( $column, 5 );
		$user_id = (int) $user_id;
		if ( ! $this->can_view( $key ) ) {
			return $output;
		}

		switch ( $key ) {
			case 'billing':
			case 'shipping':
				return $this->address( $user_id, $key );

			case 'phone':
				$phone = (string) get_user_meta( $user_id, 'billing_phone', true );
				return '' === $phone
					? $this->dash()
					: sprintf( '<a href="%s">%s</a>', esc_url( 'tel:' . preg_replace( '/[^0-9+]/', '', $phone ) ), esc_html( $phone ) );

			case 'billing_email':
				$email = (string) get_user_meta( $user_id, 'billing_email', true );
				return is_email( $email ) ? sprintf( '<a href="%s">%s</a>', esc_url( 'mailto:' . $email ), esc_html( $email ) ) : $this->dash();

			case 'billing_city':
			case 'shipping_city':
				return $this->text( (string) get_user_meta( $user_id, $key, true ) );

			case 'billing_country':
				$code      = (string) get_user_meta( $user_id, 'billing_country', true );
				$countries = function_exists( 'WC' ) && WC()->countries ? WC()->countries->get_countries() : array();
				return $this->text( $countries[ $code ] ?? $code );

			case 'since':
				$user = get_userdata( $user_id );
				$time = $user ? strtotime( $user->user_registered . ' UTC' ) : false;
				return $time ? esc_html( wp_date( get_option( 'date_format' ), $time ) ) : $this->dash();

			case 'orders':
				$count = $this->stats( $user_id )['orders'];
				if ( ! $count ) {
					return $this->dash();
				}
				return sprintf( '<a href="%s">%s</a>', esc_url( Orders::customer_orders_url( $user_id ) ), esc_html( number_format_i18n( $count ) ) );

			case 'spent':
				$spent = $this->stats( $user_id )['spent'];
				return $spent > 0 ? wp_kses_post( wc_price( $spent ) ) : $this->dash();

			case 'average':
				$average = $this->stats( $user_id )['average'];
				return $average > 0 ? wp_kses_post( wc_price( $average ) ) : $this->dash();

			case 'last_order':
				return $this->last_order_cell( $user_id );
		}

		/**
		 * Print an add-on column's cell (see `apwc_customer_columns`). Return escaped HTML.
		 *
		 * @since 1.0.0
		 * @param string $output  Cell HTML.
		 * @param string $key     Column key.
		 * @param int    $user_id User ID.
		 */
		return (string) apply_filters( 'apwc_customer_column_value', $output, $key, $user_id );
	}

	/**
	 * Order stats for a user: order count, lifetime spend (paid orders, as WooCommerce counts it),
	 * paid order count and average paid order. Fetched for the whole page in one query.
	 *
	 * @since 1.0.0
	 * @param int $user_id User ID.
	 * @return array{orders: int, spent: float, paid: int, average: float}
	 */
	public function stats( $user_id ) {
		$this->prime_rows( array_merge( $this->listed_user_ids(), array( $user_id ) ) );
		return $this->stats[ $user_id ];
	}

	/**
	 * Last order ID per user, filled in batches.
	 *
	 * @var array<int, int> User ID => order ID (0 = none).
	 */
	private $last_order_ids = array();

	/**
	 * Loaded last orders: order ID => [ number, date (timestamp) ].
	 *
	 * @var array<int, array{number: string, date: int}>
	 */
	private $last_orders = array();

	/**
	 * Order stats per user, filled with the last orders.
	 *
	 * @var array<int, array{orders: int, spent: float, paid: int, average: float}>
	 */
	private $stats = array();

	/**
	 * "Last order" cell: the order number, linked to the edit order screen, with its date below.
	 *
	 * @since 1.0.0
	 * @param int $user_id User ID.
	 * @return string
	 */
	private function last_order_cell( $user_id ) {
		$this->prime_rows( array_merge( $this->listed_user_ids(), array( $user_id ) ) );

		$order_id = $this->last_order_ids[ $user_id ] ?? 0;
		$order    = $this->last_orders[ $order_id ] ?? null;
		if ( ! $order ) {
			return $this->dash();
		}

		/* translators: %s: order number. */
		$label  = sprintf( __( '#%s', 'admin-powerups-for-woocommerce' ), $order['number'] );
		$number = current_user_can( 'edit_shop_orders' )
			? sprintf( '<a href="%1$s">%2$s</a>', esc_url( Orders::edit_url( $order_id ) ), esc_html( $label ) )
			: esc_html( $label );

		if ( ! $order['date'] ) {
			return $number;
		}
		return sprintf(
			'%1$s<span class="apwc-user-sub"><time datetime="%2$s">%3$s</time></span>',
			$number,
			esc_attr( gmdate( 'c', $order['date'] ) ),
			esc_html( wp_date( wc_date_format(), $order['date'] ) )
		);
	}

	/**
	 * Users shown on the current Users screen page.
	 *
	 * @since 1.0.0
	 * @return int[]
	 */
	private function listed_user_ids() {
		global $wp_list_table;
		if ( ! $wp_list_table instanceof \WP_Users_List_Table || empty( $wp_list_table->items ) ) {
			return array();
		}
		return array_map( 'intval', wp_list_pluck( $wp_list_table->items, 'ID' ) );
	}

	/**
	 * Find the order count, lifetime spend and last order of many users in one query, then load
	 * those orders' numbers and dates in one more.
	 *
	 * Avoids one lookup per row on the Users screen. Checkout drafts, trashed and deleted
	 * orders don't count, matching WooCommerce's own statuses. Spend sums paid orders
	 * (`wc_get_is_paid_statuses()`), the same way WooCommerce's "total spent" does.
	 * Users already looked up are skipped.
	 *
	 * @since 1.0.0
	 * @param int[] $user_ids User IDs.
	 * @return void
	 */
	public function prime_rows( array $user_ids ) {
		$user_ids = array_values( array_diff( array_unique( array_filter( array_map( 'intval', $user_ids ) ) ), array_keys( $this->last_order_ids ) ) );
		if ( ! $user_ids ) {
			return;
		}
		foreach ( $user_ids as $id ) {
			$this->last_order_ids[ $id ] = 0;
		}
		$order_ids = $this->apply_stat_filters( $user_ids, $this->query_stats( $user_ids ) );
		$this->load_last_order_dates( $order_ids );
	}

	/**
	 * One grouped query for the order count, paid spend, paid count and newest order of each user.
	 *
	 * @since 1.0.0
	 * @param int[] $user_ids User IDs (non-empty).
	 * @return array<int, object> User ID => row (order_id, order_count, spent, paid_count). Users
	 *                            without orders are missing.
	 */
	private function query_stats( array $user_ids ) {
		global $wpdb;

		$statuses = array_values( array_diff( array_keys( wc_get_order_statuses() ), array( 'wc-checkout-draft' ) ) );
		$paid     = array_map( static fn( $status ) => 'wc-' . $status, wc_get_is_paid_statuses() );
		$users_in = implode( ',', array_fill( 0, count( $user_ids ), '%d' ) );
		$stat_in  = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
		$paid_in  = implode( ',', array_fill( 0, count( $paid ), '%s' ) );

		// The IN () lists are only %d/%s placeholders built above; every value goes through prepare().
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery
		if ( Orders::hpos() ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT customer_id AS user_id, MAX(id) AS order_id, COUNT(*) AS order_count,
						SUM( CASE WHEN status IN ( {$paid_in} ) THEN total_amount ELSE 0 END ) AS spent,
						SUM( CASE WHEN status IN ( {$paid_in} ) THEN 1 ELSE 0 END ) AS paid_count
					FROM %i
					WHERE type = 'shop_order' AND customer_id IN ( {$users_in} ) AND status IN ( {$stat_in} )
					GROUP BY customer_id",
					array_merge( $paid, $paid, array( OrderUtil::get_table_for_orders() ), $user_ids, $statuses )
				)
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT CAST( pm.meta_value AS UNSIGNED ) AS user_id, MAX( p.ID ) AS order_id, COUNT(*) AS order_count,
						SUM( CASE WHEN p.post_status IN ( {$paid_in} ) THEN CAST( pt.meta_value AS DECIMAL( 26, 8 ) ) ELSE 0 END ) AS spent,
						SUM( CASE WHEN p.post_status IN ( {$paid_in} ) THEN 1 ELSE 0 END ) AS paid_count
					FROM {$wpdb->posts} p
					INNER JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID AND pm.meta_key = '_customer_user'
					LEFT JOIN {$wpdb->postmeta} pt ON pt.post_id = p.ID AND pt.meta_key = '_order_total'
					WHERE p.post_type = 'shop_order' AND pm.meta_value IN ( {$users_in} ) AND p.post_status IN ( {$stat_in} )
					GROUP BY pm.meta_value",
					array_merge( $paid, $paid, $user_ids, $statuses )
				)
			);
		}
		// phpcs:enable

		$by_user = array();
		foreach ( (array) $rows as $row ) {
			$by_user[ (int) $row->user_id ] = $row;
		}
		return $by_user;
	}

	/**
	 * Store each user's stats and last order ID, through `apwc_customer_stats` and
	 * `apwc_user_last_order_id`.
	 *
	 * @since 1.0.0
	 * @param int[]              $user_ids User IDs.
	 * @param array<int, object> $rows     query_stats() rows by user ID.
	 * @return int[] Last order IDs to load.
	 */
	private function apply_stat_filters( array $user_ids, array $rows ) {
		$order_ids = array();
		foreach ( $user_ids as $user_id ) {
			$row   = $rows[ $user_id ] ?? null;
			$spent = $row ? (float) $row->spent : 0.0;
			$count = $row ? (int) $row->paid_count : 0;

			/**
			 * Filter a customer's order stats in the Users list (after the batched lookup).
			 *
			 * @since 1.0.0
			 * @param array<string, int|float> $stats   `orders` (count), `spent` (paid total), `paid` (paid order count), `average` (spent / paid).
			 * @param int                      $user_id User ID.
			 */
			$stats = (array) apply_filters(
				'apwc_customer_stats',
				array(
					'orders'  => $row ? (int) $row->order_count : 0,
					'spent'   => $spent,
					'paid'    => $count,
					'average' => $count ? $spent / $count : 0.0,
				),
				$user_id
			);

			$this->stats[ $user_id ] = array(
				'orders'  => (int) ( $stats['orders'] ?? 0 ),
				'spent'   => (float) ( $stats['spent'] ?? 0 ),
				'paid'    => (int) ( $stats['paid'] ?? 0 ),
				'average' => (float) ( $stats['average'] ?? 0 ),
			);

			/**
			 * Filter the order shown as a user's last order in the Users list.
			 *
			 * @since 1.0.0
			 * @param int $order_id Order ID (0 for none).
			 * @param int $user_id  User ID.
			 */
			$order_id = (int) apply_filters( 'apwc_user_last_order_id', $row ? (int) $row->order_id : 0, $user_id );

			$this->last_order_ids[ $user_id ] = $order_id;
			if ( $order_id ) {
				$order_ids[] = $order_id;
			}
		}
		return $order_ids;
	}

	/**
	 * Load the number and date of the last orders.
	 *
	 * A plugin that changes order numbers (sequential numbering, prefixes) needs the order object.
	 * Otherwise the number is the ID, so one light query for the dates is enough; loading full
	 * orders costs WooCommerce about 15 queries per page.
	 *
	 * @since 1.0.0
	 * @param int[] $order_ids Order IDs.
	 * @return void
	 */
	private function load_last_order_dates( array $order_ids ) {
		global $wpdb;
		if ( ! $order_ids ) {
			return;
		}
		$hpos = Orders::hpos();

		if ( has_filter( 'woocommerce_order_number' ) ) {
			$orders = wc_get_orders(
				array(
					$hpos ? 'id' : 'post__in' => $order_ids,
					'limit'                   => -1,
					'status'                  => 'any',
					'type'                    => 'shop_order',
				)
			);
			foreach ( (array) $orders as $order ) {
				if ( ! $order instanceof \WC_Order ) {
					continue;
				}
				$date                                  = $order->get_date_created();
				$this->last_orders[ $order->get_id() ] = array(
					'number' => (string) $order->get_order_number(),
					'date'   => $date ? $date->getTimestamp() : 0,
				);
			}
			return;
		}

		$in = implode( ',', array_fill( 0, count( $order_ids ), '%d' ) );
		// The IN () list is only %d placeholders built above; the IDs go through prepare().
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery
		$dates = $hpos
			? $wpdb->get_results( $wpdb->prepare( "SELECT id, date_created_gmt AS gmt FROM %i WHERE id IN ( {$in} )", array_merge( array( OrderUtil::get_table_for_orders() ), $order_ids ) ) )
			: $wpdb->get_results( $wpdb->prepare( "SELECT ID AS id, post_date_gmt AS gmt FROM {$wpdb->posts} WHERE ID IN ( {$in} )", $order_ids ) );
		// phpcs:enable
		foreach ( (array) $dates as $row ) {
			$this->last_orders[ (int) $row->id ] = array(
				'number' => (string) $row->id,
				'date'   => $row->gmt && '0000-00-00 00:00:00' !== $row->gmt ? (int) strtotime( $row->gmt . ' UTC' ) : 0,
			);
		}
	}

	/**
	 * Formatted address cell.
	 *
	 * @since 1.0.0
	 * @param int    $user_id User ID.
	 * @param string $type    billing|shipping.
	 * @return string
	 */
	private function address( $user_id, $type ) {
		if ( $this->setting( 'compact' ) ) {
			$parts = array_filter(
				array(
					(string) get_user_meta( $user_id, "{$type}_city", true ),
					(string) get_user_meta( $user_id, "{$type}_state", true ),
					(string) get_user_meta( $user_id, "{$type}_country", true ),
				)
			);
			$text  = implode( ', ', $parts );
		} else {
			$text = CustomerAddress::format_for_user( $user_id, $type );
		}
		return '' === $text ? $this->dash() : '<span class="apwc-user-address">' . esc_html( $text ) . '</span>';
	}

	/**
	 * Plain text cell.
	 *
	 * @since 1.0.0
	 * @param string $text Text.
	 * @return string
	 */
	private function text( $text ) {
		return '' === $text ? $this->dash() : esc_html( $text );
	}

	/**
	 * Empty cell marker.
	 *
	 * @since 1.0.0
	 * @return string
	 */
	private function dash() {
		return '<span aria-hidden="true">—</span><span class="screen-reader-text">' . esc_html__( 'None', 'admin-powerups-for-woocommerce' ) . '</span>';
	}
}