<?php

namespace StoreSuit;

defined( 'ABSPATH' ) || exit;
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Messages are returned as JSON by Admin::ajax() and shown with textContent; HTML output escapes them where printed.
/** Complete account directory; Woo user records are authoritative, not an order-derived shortlist. */
final class CustomerDirectory {
    /** The account fields (user meta) a word of a search may match: the names, the billing e-mail and the company. */
    private const NAME_KEYS = array(
        'first_name',
        'last_name',
        'billing_first_name',
        'billing_last_name',
        'billing_email',
        'billing_company'
    );

    public static function listing( array $data ) : array {
        Store::require_cap( 'manage_woocommerce' );
        global $wpdb;
        $search = mb_substr( trim( Store::text( $data, 'search' ) ), 0, 80 );
        $scope = ( Store::text( $data, 'scope' ) ?: 'all' );
        if ( !in_array( $scope, array(
            'all',
            'customers',
            'team',
            'sample'
        ), true ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Choose a valid account filter.' ));
        }
        // A search stops counting at 10,000 matches; the list says "10,000+" (review I4).
        $count = (int) (( $search === '' ? self::count_all( $scope ) : self::count_found( $search, $scope ) ));
        self::checked();
        $capped = $search !== '' && $count > 10000;
        if ( $capped ) {
            $count = 10000;
        }
        // Owner decision OD-10 (a), then the owner on 29 Oct 2026: every column heading sorts the whole list, both ways: the name,
        // the orders, the total spent and what is owed (the store currency's; other currencies after it), and the account type.
        // Anything but the plain name order reads the accounts once (at most the first 10,000 by name) and ranks them here.
        $owed = false;
        $sort = ( Store::text( $data, 'sort' ) ?: 'name' );
        if ( !in_array( $sort, array(
            'name',
            'orders',
            'spent',
            'type',
            'owed'
        ), true ) || $sort === 'owed' && !$owed ) {
            throw new \InvalidArgumentException(I18n::t( 'Choose a valid sort order.' ));
        }
        $dir = ( Store::text( $data, 'dir' ) ?: (( in_array( $sort, array('orders', 'spent', 'owed'), true ) ? 'desc' : 'asc' )) );
        if ( !in_array( $dir, array('asc', 'desc'), true ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Choose a valid sort order.' ));
        }
        // Customers without an account (owner, 29 Oct 2026): a phone number's guest orders and till debt (CustomerGuests).
        $guests = ( in_array( $scope, array('all', 'customers'), true ) ? CustomerGuests::rows( $search ) : array() );
        $currency = get_woocommerce_currency();
        $sort_capped = false;
        if ( ($sort !== 'name' || $guests) && $count > 10000 ) {
            $count = 10000;
            $sort_capped = true;
        }
        $total = $count + count( $guests );
        $pages = max( 1, (int) ceil( $total / 25 ) );
        $page = min( $pages, max( 1, absint( $data['page'] ?? 1 ) ) );
        $offset = ($page - 1) * 25;
        if ( $sort === 'name' && !$guests ) {
            // The name order straight from the database; backwards, the same rows from the other end.
            $from = ( $dir === 'asc' ? $offset : max( 0, $count - $offset - 25 ) );
            $entries = ( $count ? self::ids(
                $search,
                $scope,
                ( $dir === 'asc' ? 25 : $count - $offset - $from ),
                $from
            ) : array() );
            if ( $dir === 'desc' ) {
                $entries = array_reverse( $entries );
            }
            self::checked();
            $bought = self::bought( $entries );
        } else {
            $all = ( $count ? self::ids(
                $search,
                $scope,
                $count,
                0
            ) : array() );
            self::checked();
            $bought = self::bought( null );
            $names = self::names( $all );
            $kinds = ( $sort === 'type' ? self::badges( $all ) : array() );
            $due = array();
            $list = array();
            foreach ( $all as $id ) {
                $b = $bought[$id] ?? array(
                    'orders' => 0,
                    'spent'  => array(),
                );
                $list[] = array(
                    'id'     => $id,
                    'name'   => $names[$id] ?? '',
                    'orders' => (int) $b['orders'],
                    'spent'  => (float) ($b['spent'][$currency] ?? 0),
                    'type'   => ( !empty( $kinds[$id]['sample'] ) ? 3 : (( !empty( $kinds[$id]['team'] ) ? 2 : 0 )) ),
                    'owed'   => $due[$id] ?? array(0.0, 0),
                );
            }
            foreach ( $guests as $g ) {
                $list[] = array(
                    'id'     => $g['id'],
                    'name'   => $g['name'],
                    'orders' => (int) $g['orders'],
                    'spent'  => (float) (( (array) $g['spent'] )[$currency] ?? 0),
                    'type'   => 1,
                    'owed'   => $due[$g['id']] ?? array(0.0, 0),
                    'guest'  => $g,
                );
            }
            $collator = ( class_exists( '\\Collator' ) ? new \Collator(get_locale()) : null );
            $by_name = static fn( $a, $b ) => ( (( $collator ? (int) $collator->compare( $a['name'], $b['name'] ) : strcasecmp( $a['name'], $b['name'] ) )) ?: strcmp( (string) $a['id'], (string) $b['id'] ) );
            // Customer accounts, then customers without one, then team and sample accounts; what is owed in the store currency, then
            // in another currency only. An equal figure keeps the name order, whichever way the column runs.
            $key = static fn( $r ) => match ($sort) {
                'orders' => array($r['orders'], $r['spent']),
                'spent' => array($r['spent'], $r['orders']),
                'type' => array($r['type']),
                'owed' => $r['owed'],
                default => array(),
            };
            usort( $list, static function ( $a, $b ) use(
                $sort,
                $dir,
                $key,
                $by_name
            ) {
                $c = ( $sort === 'name' ? $by_name( $a, $b ) : $key( $a ) <=> $key( $b ) );
                return ( (( $dir === 'desc' ? -$c : $c )) ?: $by_name( $a, $b ) );
            } );
            $entries = array_map( static fn( $r ) => $r['guest'] ?? $r['id'], array_slice( $list, $offset, 25 ) );
        }
        $ids = array_values( array_filter( $entries, 'is_int' ) );
        self::checked();
        $badges = self::badges( $ids );
        $out = array();
        foreach ( $entries as $entry ) {
            if ( is_array( $entry ) ) {
                $out[] = $entry;
                continue;
            }
            $out[] = self::row( $entry ) + array(
                'guest'  => false,
                'team'   => !empty( $badges[$entry]['team'] ),
                'sample' => !empty( $badges[$entry]['sample'] ),
                'orders' => (int) ($bought[$entry]['orders'] ?? 0),
                'spent'  => (object) ($bought[$entry]['spent'] ?? array()),
            );
        }
        return array(
            'rows'        => $out,
            'owed'        => $owed,
            'count'       => $total,
            'guests'      => count( $guests ),
            'capped'      => $capped,
            'page'        => $page,
            'pages'       => $pages,
            'scope'       => $scope,
            'search'      => $search,
            'sort'        => $sort,
            'dir'         => $dir,
            'sort_capped' => $sort_capped,
            'currency'    => $currency,
        );
    }

    /** Team and sample badges of these accounts, a thousand at a time. @return array<int,array{team:mixed,sample:mixed}> */
    private static function badges( array $ids ) : array {
        global $wpdb;
        $badges = array();
        $likes = self::team_likes();
        foreach ( array_chunk( $ids, 1000 ) as $chunk ) {
            foreach ( (array) $wpdb->get_results( $wpdb->prepare( "SELECT u.ID,EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND (" . implode( ' OR ', array_fill( 0, count( $likes ), 'm.meta_value LIKE %s' ) ) . ")) team,EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND m.meta_value<>'') sample FROM {$wpdb->users} u WHERE u.ID IN (" . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ')', array_merge(
                array($wpdb->get_blog_prefix() . 'capabilities'),
                $likes,
                array(DemoData::MARK),
                $chunk
            ) ), ARRAY_A ) as $row ) {
                $badges[(int) $row['ID']] = $row;
            }
            self::checked();
        }
        return $badges;
    }

    /** The accounts' names as the database orders them (display_name), a thousand at a time. @return array<int,string> */
    private static function names( array $ids ) : array {
        global $wpdb;
        $names = array();
        foreach ( array_chunk( $ids, 1000 ) as $chunk ) {
            foreach ( (array) $wpdb->get_results( $wpdb->prepare( 'SELECT ID,display_name FROM %i WHERE ID IN (' . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ')', array_merge( array($wpdb->users), $chunk ) ), ARRAY_A ) as $row ) {
                $names[(int) $row['ID']] = (string) $row['display_name'];
            }
            self::checked();
        }
        return $names;
    }

    /**
     * The accounts' purchases (OD-10 a), from WooCommerce's own order tables (HPOS or posts, whichever the store uses), in one read:
     * per account, its orders and what they came to after refunds, per currency. The same orders as the customer profile counts:
     * every order but a quote, a cancelled or failed one, and a shopper's own checkout left before paying (pending, made by the
     * checkout; Orders::abandoned_checkout()); an order's own refunds come off its total, never below zero. Gift card parts of a
     * paid build's order are not in the order total and are left out here. $ids null reads every account with orders.
     * @return array<int,array{orders:int,spent:array<string,float>}>
     */
    private static function bought( ?array $ids ) : array {
        global $wpdb;
        if ( is_array( $ids ) && !$ids ) {
            return array();
        }
        $only = ( is_array( $ids ) ? 1 : 0 );
        $ids = ( is_array( $ids ) ? array_values( array_map( 'intval', $ids ) ) : array(0) );
        $statuses = array_values( array_diff( SaleStatus::orders(), array('wc-cancelled', 'wc-failed', 'wc-checkout-draft') ) );
        $web = ( Orders::web_checkout_sources() ?: array('checkout') );
        if ( class_exists( '\\Automattic\\WooCommerce\\Utilities\\OrderUtil' ) && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ) {
            $orders = $wpdb->prefix . 'wc_orders';
            $operational = $wpdb->prefix . 'wc_order_operational_data';
            $rows = $wpdb->get_results( $wpdb->prepare( "SELECT o.customer_id AS customer_id, o.currency AS currency, COUNT(*) AS orders, SUM(GREATEST(0, o.total_amount + COALESCE((SELECT SUM(r.total_amount) FROM %i r WHERE r.parent_order_id=o.id AND r.type='shop_order_refund'),0))) AS spent FROM %i o LEFT JOIN %i d ON d.order_id=o.id WHERE o.type='shop_order' AND o.customer_id>0 AND (%d=0 OR o.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ")) AND o.status IN (" . implode( ',', array_fill( 0, count( $statuses ), '%s' ) ) . ") AND NOT (o.status='wc-pending' AND COALESCE(d.created_via,'') IN (" . implode( ',', array_fill( 0, count( $web ), '%s' ) ) . ")) GROUP BY o.customer_id, o.currency", array_merge(
                array(
                    $orders,
                    $orders,
                    $operational,
                    $only
                ),
                $ids,
                $statuses,
                $web
            ) ), ARRAY_A );
        } else {
            $rows = $wpdb->get_results( $wpdb->prepare( "SELECT CAST(c.meta_value AS UNSIGNED) AS customer_id, COALESCE(cur.meta_value,'') AS currency, COUNT(*) AS orders, SUM(GREATEST(0, CAST(COALESCE(t.meta_value,'0') AS DECIMAL(26,8)) + COALESCE((SELECT SUM(CAST(rm.meta_value AS DECIMAL(26,8))) FROM {$wpdb->posts} rp JOIN {$wpdb->postmeta} rm ON rm.post_id=rp.ID AND rm.meta_key='_order_total' WHERE rp.post_parent=p.ID AND rp.post_type='shop_order_refund'),0))) AS spent FROM {$wpdb->posts} p JOIN {$wpdb->postmeta} c ON c.post_id=p.ID AND c.meta_key='_customer_user' LEFT JOIN {$wpdb->postmeta} t ON t.post_id=p.ID AND t.meta_key='_order_total' LEFT JOIN {$wpdb->postmeta} cur ON cur.post_id=p.ID AND cur.meta_key='_order_currency' LEFT JOIN {$wpdb->postmeta} v ON v.post_id=p.ID AND v.meta_key='_created_via' WHERE p.post_type='shop_order' AND c.meta_value NOT IN ('','0') AND (%d=0 OR c.meta_value IN (" . implode( ',', array_fill( 0, count( $ids ), '%s' ) ) . ")) AND p.post_status IN (" . implode( ',', array_fill( 0, count( $statuses ), '%s' ) ) . ") AND NOT (p.post_status='wc-pending' AND COALESCE(v.meta_value,'') IN (" . implode( ',', array_fill( 0, count( $web ), '%s' ) ) . ")) GROUP BY customer_id, currency", array_merge(
                array($only),
                array_map( 'strval', $ids ),
                $statuses,
                $web
            ) ), ARRAY_A );
        }
        self::checked();
        $out = array();
        $places = wc_get_price_decimals();
        foreach ( (array) $rows as $row ) {
            $id = (int) $row['customer_id'];
            if ( !$id ) {
                continue;
            }
            $out[$id] = $out[$id] ?? array(
                'orders' => 0,
                'spent'  => array(),
            );
            $out[$id]['orders'] += (int) $row['orders'];
            $currency = ( (string) $row['currency'] ?: get_woocommerce_currency() );
            $out[$id]['spent'][$currency] = round( ($out[$id]['spent'][$currency] ?? 0) + (float) $row['spent'], $places );
        }
        return $out;
    }

    /** Preserve the existing array API, with one extra result so the picker can honestly say 20+. */
    public static function search( array $data ) : array {
        Store::require_cap( 'storesuit_orders' );
        $query = mb_substr( trim( Store::text( $data, 'search' ) ), 0, 80 );
        if ( mb_strlen( $query ) < 2 ) {
            return array();
        }
        $ids = self::ids(
            $query,
            'all',
            21,
            0
        );
        self::checked();
        $out = array();
        foreach ( $ids as $id ) {
            $out[] = self::row( $id, true );
        }
        return $out;
    }

    private static function checked() : void {
        global $wpdb;
        if ( $wpdb->last_error !== '' ) {
            throw new \RuntimeException(I18n::t( 'Customer accounts could not be read. Try again shortly.' ));
        }
    }

    /**
     * The accounts (WordPress users) a list shows are always read by one literal statement: the multisite check, the search and the scope
     * are constant switches such as (%d=0 OR ...) and (%s<>'team' OR ...), so the optimizer drops what is not asked for, and every value is a
     * placeholder. The three list statements below (count_all, count_found, ids) carry the same three parts; filter_args() holds their values.
     * Every word must match, but words may match different account/name/company fields. No multiplying meta joins.
     * A word (two characters or more, at most four) matches the name, e-mail or login, a name or company field, or, with three digits or
     * more, a phone; the whole query also matches as one phone number when it only holds phone characters. Multisite: only the accounts of
     * this shop's site (review I3). Scope: customers (no team account), team (a role that may manage the shop or edit posts) or sample.
     */
    private static function count_all( string $scope ) : int {
        global $wpdb;
        $likes = self::team_likes();
        $caps = $wpdb->get_blog_prefix() . 'capabilities';
        // The values of filter_args()'s multisite check and scope, written out one by one (the checker counts them against the placeholders).
        return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->users} u WHERE 1=1 AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} c WHERE c.user_id=u.ID AND c.meta_key=%s)) AND (%s<>'customers' OR NOT EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND (" . implode( ' OR ', array_fill( 0, count( $likes ), 'm.meta_value LIKE %s' ) ) . "))) AND (%s<>'team' OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND (" . implode( ' OR ', array_fill( 0, count( $likes ), 'm.meta_value LIKE %s' ) ) . "))) AND (%s<>'sample' OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND m.meta_value<>''))", array(
            ( is_multisite() ? 1 : 0 ),
            $caps,
            $scope,
            $caps,
            ...$likes,
            $scope,
            $caps,
            ...$likes,
            $scope,
            DemoData::MARK
        ) ) );
    }

    private static function count_found( string $search, string $scope ) : int {
        global $wpdb;
        $likes = self::team_likes();
        $a = self::filter_args( $search, $scope, $likes );
        return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM (SELECT 1 FROM {$wpdb->users} u WHERE 1=1 AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} c WHERE c.user_id=u.ID AND c.meta_key=%s)) AND (%d=1 OR (%d=1 AND ((%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))) AND (%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))) AND (%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))) AND (%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))))) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s))))) AND (%s<>'customers' OR NOT EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND (" . implode( ' OR ', array_fill( 0, count( $likes ), 'm.meta_value LIKE %s' ) ) . "))) AND (%s<>'team' OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND (" . implode( ' OR ', array_fill( 0, count( $likes ), 'm.meta_value LIKE %s' ) ) . "))) AND (%s<>'sample' OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND m.meta_value<>'')) LIMIT 10001) x", array_merge( $a['multi'], $a['search'], $a['scope'] ) ) );
    }

    /** One page of account IDs, by name. */
    private static function ids(
        string $search,
        string $scope,
        int $limit,
        int $offset
    ) : array {
        global $wpdb;
        $likes = self::team_likes();
        $a = self::filter_args( $search, $scope, $likes );
        return array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare( "SELECT u.ID FROM {$wpdb->users} u WHERE 1=1 AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} c WHERE c.user_id=u.ID AND c.meta_key=%s)) AND (%d=1 OR (%d=1 AND ((%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))) AND (%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))) AND (%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))) AND (%d=0 OR (u.display_name LIKE %s OR u.user_email LIKE %s OR u.user_login LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key IN (" . implode( ',', array_fill( 0, count( self::NAME_KEYS ), '%s' ) ) . ") AND m.meta_value LIKE %s) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s)))))))) OR (%d=1 AND EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key='billing_phone' AND (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s OR (%d=1 AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.meta_value,' ',''),'+',''),'-',''),'(',''),')',''),'.',''),'/',''),'\t',''),' ','') LIKE %s))))) AND (%s<>'customers' OR NOT EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND (" . implode( ' OR ', array_fill( 0, count( $likes ), 'm.meta_value LIKE %s' ) ) . "))) AND (%s<>'team' OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND (" . implode( ' OR ', array_fill( 0, count( $likes ), 'm.meta_value LIKE %s' ) ) . "))) AND (%s<>'sample' OR EXISTS(SELECT 1 FROM {$wpdb->usermeta} m WHERE m.user_id=u.ID AND m.meta_key=%s AND m.meta_value<>'')) ORDER BY u.display_name,u.ID LIMIT %d OFFSET %d", array_merge(
            $a['multi'],
            $a['search'],
            $a['scope'],
            array($limit, $offset)
        ) ) ) );
    }

    /** The values of the multisite check, the search and the scope, in the order of their placeholders. */
    private static function filter_args( string $search, string $scope, array $likes ) : array {
        global $wpdb;
        $caps = $wpdb->get_blog_prefix() . 'capabilities';
        // Only phone characters: one number however it is spaced, "0532 123 45 67" or "+90 (532) 123-45-67" (review I1).
        // It still matches names, e-mails and logins as words ("1985" in ahmet1985@…).
        $number = preg_replace( '/\\D+/', '', $search );
        $phone_like = strlen( $number ) >= 3 && preg_match( '/^[\\d\\s+\\-().\\/\\x{00A0}]+$/u', $search );
        // At most four words of two characters or more: a long query does not multiply the scans (review I4).
        $words = array_slice( array_values( array_filter( ( preg_split(
            '/\\s+/u',
            $search,
            -1,
            PREG_SPLIT_NO_EMPTY
        ) ?: array() ), static fn( $w ) => mb_strlen( $w ) >= 2 ) ), 0, 4 );
        // Whether the query is empty, whether it has words, the four word slots (unused ones switched off), then the whole-query phone.
        // A query with neither a word nor a phone matches nothing (one-letter words match not every account).
        $search_args = array(( trim( $search ) === '' ? 1 : 0 ), ( $words ? 1 : 0 ));
        for ($i = 0; $i < 4; $i++) {
            $search_args = array_merge( $search_args, self::word_args( $words[$i] ?? null ) );
        }
        $search_args = array_merge( $search_args, self::phone_args( $number, (bool) $phone_like ) );
        return array(
            'multi'  => array(( is_multisite() ? 1 : 0 ), $caps),
            'search' => $search_args,
            'scope'  => array_merge(
                array($scope, $caps),
                $likes,
                array($scope, $caps),
                $likes,
                array($scope, DemoData::MARK)
            ),
        );
    }

    /** The fifteen values of one word slot: whether it is used, its pattern for the name, e-mail and login, the name keys, its pattern for the name fields, and its phone match when it has three digits or more. */
    private static function word_args( ?string $word ) : array {
        global $wpdb;
        if ( $word === null ) {
            return array_merge(
                array(
                    0,
                    '',
                    '',
                    ''
                ),
                self::NAME_KEYS,
                array(''),
                array(
                    0,
                    '',
                    0,
                    ''
                )
            );
        }
        $like = '%' . $wpdb->esc_like( $word ) . '%';
        $digits = preg_replace( '/\\D+/', '', $word );
        return array_merge(
            array(
                1,
                $like,
                $like,
                $like
            ),
            self::NAME_KEYS,
            array($like),
            self::phone_args( $digits, strlen( $digits ) >= 3 )
        );
    }

    /** The billing phone without its usual formatting; a national number also matches without its leading zero (0532… in +90 532…). */
    private static function phone_args( string $digits, bool $on ) : array {
        global $wpdb;
        // Portable to older MySQL: common phone formatting (space + - ( ) . / tab, no-break space) stripped without REGEXP_REPLACE.
        $national = ltrim( $digits, '0' );
        $both = $national !== $digits && strlen( $national ) >= 7;
        return array(
            ( $on ? 1 : 0 ),
            '%' . $wpdb->esc_like( $digits ) . '%',
            ( $both ? 1 : 0 ),
            '%' . $wpdb->esc_like( $national ) . '%'
        );
    }

    /** The patterns that find the stored capabilities of a team account: every role that may manage the shop, take orders or edit posts. */
    private static function team_likes() : array {
        global $wpdb;
        $roles = array();
        foreach ( wp_roles()->roles as $role => $definition ) {
            foreach ( array(
                'manage_woocommerce',
                'storesuit_orders',
                'storesuit_fulfil',
                'edit_posts'
            ) as $cap ) {
                if ( !empty( $definition['capabilities'][$cap] ) ) {
                    $roles[] = $role;
                    break;
                }
            }
        }
        // storesuit_fulfil: a Warehouse & shipping account (OD-7) is a team account too.
        $keys = array_unique( array_merge( $roles, array(
            'manage_woocommerce',
            'storesuit_orders',
            'storesuit_fulfil',
            'edit_posts'
        ) ) );
        $likes = array();
        foreach ( $keys as $key ) {
            $likes[] = '%' . $wpdb->esc_like( '"' . $key . '";b:1;' ) . '%';
        }
        return $likes;
    }

    private static function row( int $id, bool $address = false ) : array {
        $user = get_user_by( 'id', $id );
        $meta = get_user_meta( $id );
        $get = static fn( $key ) => (string) ($meta[$key][0] ?? '');
        $name = ( trim( $get( 'billing_first_name' ) . ' ' . $get( 'billing_last_name' ) ) ?: (( $user ? $user->display_name : '' )) );
        $row = array(
            'id'      => $id,
            'name'    => $name,
            'email'   => ( $get( 'billing_email' ) ?: (( $user ? $user->user_email : '' )) ),
            'phone'   => $get( 'billing_phone' ),
            'company' => $get( 'billing_company' ),
        );
        if ( $address ) {
            foreach ( array(
                'address'   => 'billing_address_1',
                'address_2' => 'billing_address_2',
                'city'      => 'billing_city',
                'country'   => 'billing_country',
                'state'     => 'billing_state',
                'postcode'  => 'billing_postcode',
            ) as $key => $value ) {
                $row[$key] = $get( $value );
            }
        }
        return $row;
    }

}