<?php

namespace StoreSuit;

defined( 'ABSPATH' ) || exit;
/** A rebuildable read model. WooCommerce remains the source of truth. */
final class OrderIndex {
    public static function table() : string {
        global $wpdb;
        return $wpdb->prefix . 'storesuit_order_index';
    }

    public static function phone_key( string $phone ) : string {
        $normalized = Store::normalize_phone( $phone );
        // The POS engine's own rule, also in the free build (0.63).
        return ( $normalized === '' ? '' : hash_hmac( 'sha256', $normalized, wp_salt( 'auth' ) ) );
    }

    private static function phone_key_marker() : string {
        return hash_hmac( 'sha256', 'storesuit-order-phone-key-v1', wp_salt( 'auth' ) );
    }

    public static function hooks() : void {
        self::install();
        DashboardFacts::install();
        IndexRecovery::hooks();
        add_action( 'woocommerce_after_order_object_save', array(__CLASS__, 'changed'), 100 );
        add_action( 'woocommerce_after_order_refund_object_save', array(__CLASS__, 'refund_saved'), 100 );
        // A refund deleted again (refused after WooCommerce saved it, or deleted in the order screen) re-reads its order (0.58 M5).
        add_action( 'woocommerce_delete_order_refund', array(__CLASS__, 'refund_deleted'), 100 );
        add_action(
            'woocommerce_refund_deleted',
            static function ( $refund_id, $order_id ) {
                \WC_Cache_Helper::invalidate_cache_group( 'orders' );
                self::changed( (int) $order_id );
            },
            100,
            2
        );
        add_action( 'woocommerce_order_refunded', array(__CLASS__, 'changed'), 100 );
        // WooCommerce's return form marks the order with save_meta_data(), which fires no order save; its note follows at once,
        // so the "Return requests" queue counts the request instead of waiting for the next save of that order.
        add_action(
            'woocommerce_order_note_added',
            static function ( $note_id, $order ) {
                if ( $order instanceof \WC_Order && $order->get_meta( '_order_withdrawal_requested' ) === 'yes' ) {
                    self::changed( $order );
                }
            },
            100,
            2
        );
        add_action( 'woocommerce_delete_order', array(__CLASS__, 'remove') );
        add_action( 'woocommerce_trash_order', array(__CLASS__, 'remove') );
        add_action( 'woocommerce_untrash_order', array(__CLASS__, 'changed') );
        add_action( 'trashed_post', static function ( $id ) {
            if ( get_post_type( $id ) === 'shop_order' ) {
                self::remove( $id );
            }
        } );
        add_action( 'untrashed_post', static function ( $id ) {
            if ( get_post_type( $id ) === 'shop_order' ) {
                self::changed( $id );
            }
        } );
        add_action(
            'deleted_post',
            static function ( $id, $post ) {
                if ( $post->post_type === 'shop_order' ) {
                    self::remove( $id );
                }
            },
            10,
            2
        );
        // created_at is the site's local time: a new time zone rewrites every row's (retime()).
        foreach ( array('timezone_string', 'gmt_offset') as $option ) {
            add_action( 'update_option_' . $option, static function () {
                self::retime_start();
            } );
        }
        // Its batches go on in cron requests too, not only when the orders screen is open (after Upgrade's start at init 30).
        if ( wp_doing_cron() ) {
            add_action( 'init', array(__CLASS__, 'retime_due'), 31 );
        }
    }

    /** One batch of the rewrite (and of the phone digits pass) while one is due and the index is ready. */
    public static function retime_due() : void {
        if ( get_option( 'storesuit_order_index_ready' ) === '1' ) {
            self::retime();
            self::phones();
        }
    }

    /** The phone number's digits alone, so a search finds it however it was typed (Almira audit SAT-6). */
    private static function phone_digits( string $phone ) : string {
        return (string) preg_replace( '/\\D+/', '', $phone );
    }

    /**
     * What the search looks for. A phone number, in any format ("0555 123 45 67", "+90 (555) 123-4567"), is searched by its
     * digits, with no leading zero and at most its last nine, so the national, international and spaced forms all find the
     * order (Almira audit SAT-6, 28 Oct 2026: only the exact stored format matched). Anything else is searched as typed.
     */
    public static function search_term( string $search ) : string {
        if ( !preg_match( '/^[\\d\\s+().\\/-]+$/', $search ) ) {
            return $search;
        }
        $digits = ltrim( self::phone_digits( $search ), '0' );
        if ( strlen( $digits ) < 7 ) {
            return $search;
        }
        return substr( $digits, -9 );
    }

    /** The cursor of the one pass that adds each earlier row's phone digits to its search text ('done' once through). */
    public const PHONES = 'storesuit_order_index_phones';

    /** 300 rows a call: the billing phone read from the order tables, the row written only when its digits are missing. */
    private static function phones() : void {
        $cursor = get_option( self::PHONES, 0 );
        if ( $cursor === 'done' ) {
            return;
        }
        global $wpdb;
        $rows = ( \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ? $wpdb->get_results( $wpdb->prepare(
            "SELECT i.order_id, i.search_text, a.phone FROM %i i LEFT JOIN %i a ON a.order_id=i.order_id AND a.address_type='billing' WHERE i.order_id>%d ORDER BY i.order_id LIMIT 300",
            self::table(),
            $wpdb->prefix . 'wc_order_addresses',
            (int) $cursor
        ), ARRAY_A ) : $wpdb->get_results( $wpdb->prepare(
            "SELECT i.order_id, i.search_text, m.meta_value AS phone FROM %i i LEFT JOIN %i m ON m.post_id=i.order_id AND m.meta_key='_billing_phone' WHERE i.order_id>%d ORDER BY i.order_id LIMIT 300",
            self::table(),
            $wpdb->postmeta,
            (int) $cursor
        ), ARRAY_A ) );
        if ( $wpdb->last_error !== '' ) {
            return;
        }
        foreach ( (array) $rows as $row ) {
            $digits = self::phone_digits( (string) $row['phone'] );
            if ( $digits !== '' && !str_contains( (string) $row['search_text'], $digits ) ) {
                $wpdb->update( self::table(), array(
                    'search_text' => $row['search_text'] . ' ' . $digits,
                ), array(
                    'order_id' => (int) $row['order_id'],
                ) );
            }
        }
        update_option( self::PHONES, ( count( (array) $rows ) < 300 ? 'done' : (int) end( $rows )['order_id'] ), false );
    }

    /** While set, the next order id whose created_at is written again in the site's time zone (Almira audit SAT-7). */
    public const RETIME = 'storesuit_order_index_retime';

    /**
     * Rows keep the local time of the moment they were read, so after a time zone change the old rows and the ones read since
     * sorted apart and fell into the wrong day (Almira audit SAT-7, 28 Oct 2026: UTC to Istanbul, "newest first" out of order).
     */
    public static function retime_start() : void {
        update_option( self::RETIME, 0, false );
    }

    /** 300 rows a call, from the order's own GMT date (one read, one write a row; no order is loaded). */
    private static function retime() : void {
        $cursor = get_option( self::RETIME );
        if ( $cursor === false ) {
            return;
        }
        global $wpdb;
        $rows = ( \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ? $wpdb->get_results( $wpdb->prepare(
            'SELECT i.order_id, i.created_at, o.date_created_gmt AS gmt FROM %i i INNER JOIN %i o ON o.id=i.order_id WHERE i.order_id>%d ORDER BY i.order_id LIMIT 300',
            self::table(),
            $wpdb->prefix . 'wc_orders',
            (int) $cursor
        ), ARRAY_A ) : $wpdb->get_results( $wpdb->prepare(
            'SELECT i.order_id, i.created_at, p.post_date_gmt AS gmt FROM %i i INNER JOIN %i p ON p.ID=i.order_id WHERE i.order_id>%d ORDER BY i.order_id LIMIT 300',
            self::table(),
            $wpdb->posts,
            (int) $cursor
        ), ARRAY_A ) );
        if ( $wpdb->last_error !== '' ) {
            return;
        }
        foreach ( (array) $rows as $row ) {
            if ( empty( $row['gmt'] ) || str_starts_with( (string) $row['gmt'], '0000' ) ) {
                continue;
            }
            $local = get_date_from_gmt( (string) $row['gmt'], 'Y-m-d H:i:s' );
            if ( $local !== $row['created_at'] ) {
                $wpdb->update( self::table(), array(
                    'created_at' => $local,
                ), array(
                    'order_id' => (int) $row['order_id'],
                ) );
            }
        }
        if ( count( (array) $rows ) < 300 ) {
            delete_option( self::RETIME );
        } else {
            update_option( self::RETIME, (int) end( $rows )['order_id'], false );
        }
    }

    /** Refund id => its order, for the refunds saved in this request (WooCommerce's delete action passes only the id). */
    private static array $refund_orders = array();

    public static function refund_saved( $refund ) : bool {
        if ( $refund instanceof \WC_Order_Refund && $refund->get_id() ) {
            self::$refund_orders[$refund->get_id()] = $refund->get_parent_id();
        }
        return self::changed( $refund );
    }

    /**
     * WooCommerce saves a refund before its last check (woocommerce_create_refund), so a refund refused there (a card-paid
     * or marketplace order) was indexed into its order and is then deleted: the order is read again without it, with the
     * order caches cleared, so its row and dashboard facts do not keep a refund that never happened (review 0.58 M5).
     */
    public static function refund_deleted( $refund_id ) : void {
        $order_id = self::$refund_orders[(int) $refund_id] ?? 0;
        unset(self::$refund_orders[(int) $refund_id]);
        if ( $order_id ) {
            \WC_Cache_Helper::invalidate_cache_group( 'orders' );
            self::changed( $order_id );
        }
    }

    /**
     * '1' once the table has the columns of IDX-1: due (what a row of the payment queue still owes, 0 for every other row,
     * NULL in a row written before the column existed) and cod (paid by cash on delivery), and a key on channel. Added to
     * the table of schema 3 in place: the rows stay and the index stays ready, and Upgrade::index_repair() fills the new
     * columns of the earlier rows.
     */
    public const COLUMNS = 'storesuit_order_index_columns';

    /** Verify the additive migration before an upgrade pass uses its columns. A restored database can carry a stale marker. */
    public static function ensure_columns() : bool {
        self::install( true );
        return get_option( self::COLUMNS ) === '1';
    }

    /**
     * Object cache key: the table's columns were read and found whole. Kept ten minutes in a persistent object cache only, so
     * a page view no longer reads the table's columns each time (Almira profile, 1 Nov 2026: up to 31 ms under load); a
     * restored database is seen within that time, at once by an upgrade pass (ensure_columns()) or a failed row write. With no
     * persistent object cache, an admin screen, cron run or form post reads them each time and a shopper's page view never does.
     */
    public const VERIFIED = 'storesuit_index_columns';

    private static function install( bool $verify = false ) : void {
        $schema = get_option( 'storesuit_order_index_schema' );
        $marker = self::phone_key_marker();
        $base = $schema === '3' && get_option( 'storesuit_order_index_phone_marker' ) === $marker;
        $columns = get_option( self::COLUMNS ) === '1';
        global $wpdb;
        $table = self::table();
        if ( $base && $columns ) {
            $kept = wp_using_ext_object_cache();
            // With nothing to keep the result between requests, a shopper's page view (it writes no row) does not read the columns
            // at all: about 9 ms of each uncached page on a clean store, 4 Nov 2026. The next admin screen, cron run or form post does.
            if ( !$verify && (( $kept ? wp_cache_get( self::VERIFIED, 'storesuit' ) === $table : !Store::upkeep_request() )) ) {
                return;
            }
            $present = array_map( 'strval', (array) $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ) ) );
            if ( in_array( 'due', $present, true ) && in_array( 'cod', $present, true ) ) {
                if ( $kept ) {
                    wp_cache_set(
                        self::VERIFIED,
                        $table,
                        'storesuit',
                        10 * MINUTE_IN_SECONDS
                    );
                }
                return;
            }
            wp_cache_delete( self::VERIFIED, 'storesuit' );
            // The marker describes a completed migration, not permission to query a column that is actually absent.
            delete_option( self::COLUMNS );
            $columns = false;
            if ( !$present ) {
                $base = false;
            }
        }
        // Only a missing or changed table needs dbDelta: its file brings in the whole wp-admin include chain, which every
        // storefront request paid for when it was loaded above the check (TTFB review, 19 Oct 2026).
        if ( $schema !== '3' || !$columns ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            $charset = $wpdb->get_charset_collate();
            dbDelta( "CREATE TABLE {$table} (\n            order_id bigint(20) unsigned NOT NULL,\n            customer_id bigint(20) unsigned NOT NULL DEFAULT 0,\n            phone_key char(64) NOT NULL DEFAULT '',\n            created_at datetime NOT NULL,\n            channel varchar(32) NOT NULL,\n            currency varchar(8) NOT NULL,\n            status varchar(32) NOT NULL,\n            search_text text NOT NULL,\n            needs_payment tinyint(1) NOT NULL DEFAULT 0,\n            needs_fulfil tinyint(1) NOT NULL DEFAULT 0,\n            needs_return tinyint(1) NOT NULL DEFAULT 0,\n            flow_stage varchar(24) NOT NULL DEFAULT 'new',\n            due decimal(26,8) DEFAULT NULL,\n            cod tinyint(1) NOT NULL DEFAULT 0,\n            PRIMARY KEY  (order_id),\n            KEY created_at (created_at),\n            KEY customer_id (customer_id),\n            KEY guest_phone (customer_id,phone_key),\n            KEY payment (needs_payment,created_at),\n            KEY fulfil (needs_fulfil,created_at),\n            KEY returns_queue (needs_return,created_at),\n            KEY flow_queue (flow_stage,created_at),\n            KEY channel (channel)\n        ) {$charset};" );
        }
        $fields = array_map( 'strval', (array) $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ) ) );
        if ( !$base && in_array( 'phone_key', $fields, true ) ) {
            update_option( 'storesuit_order_index_schema', '3', false );
            update_option( 'storesuit_order_index_phone_marker', $marker, false );
            // A new schema or salt changes every row (each phone key): the whole pass starts again.
            IndexRecovery::restart();
        }
        // Read on every request, as the schema options above are: kept with the autoloaded options.
        if ( !$columns && in_array( 'due', $fields, true ) && in_array( 'cod', $fields, true ) ) {
            update_option( self::COLUMNS, '1', true );
            // A restored completed marker can outlive the old physical table. Re-read its added defaults from WooCommerce.
            foreach ( array(
                Upgrade::INDEX_REPAIR,
                'storesuit_order_index_repair_cursor',
                Upgrade::COD_RULE_READ,
                'storesuit_order_index_cod_rule_cursor'
            ) as $key ) {
                delete_option( $key );
            }
        }
    }

    public static function remove( $id ) : void {
        global $wpdb;
        // A row that could not be removed is a stale row: the index is rebuilt (8.30 review I1).
        if ( false === $wpdb->delete( self::table(), array(
            'order_id' => (int) $id,
        ), array('%d') ) && !IndexRecovery::reading() ) {
            IndexRecovery::restart();
        }
        DashboardFacts::remove( (int) $id );
    }

    /**
     * Writes the order's row (and its dashboard facts) from the order as stored, never from the copy a hook or a caller passes
     * (IDX-1). WooCommerce saves copies it loaded earlier: its place-order step logger saves the checkout's own copy, still
     * 'pending', after the payment gateway moved another copy to on-hold or processing. That save writes only the meta it
     * changed, so the stored order keeps the gateway's status, paid date and totals, while the copy still says 'pending': a
     * row written from it put an unpaid bank transfer or cash-on-delivery order out of the payment queue as an abandoned
     * checkout, and out of the sales facts. The status, totals, paid date, payment method and meta are therefore read again
     * here, with this request's copies of the order dropped (Orders::fresh()).
     */
    public static function changed( $value ) : bool {
        if ( $value instanceof \WC_Order_Refund ) {
            $id = $value->get_parent_id();
        } elseif ( $value instanceof \WC_Abstract_Order ) {
            if ( $value->get_type() !== 'shop_order' ) {
                return true;
            }
            $id = $value->get_id();
        } else {
            $id = absint( $value );
        }
        $o = ( $id ? self::stored( $id ) : null );
        if ( $o instanceof \WC_Order_Refund ) {
            $o = ( $o->get_parent_id() ? self::stored( $o->get_parent_id() ) : null );
        }
        if ( !$o instanceof \WC_Order || $o->get_type() !== 'shop_order' ) {
            return true;
        }
        // An abandoned "Add order" screen (an auto-draft) is no order: never a row in the New queue (8.30 review M3).
        $id = $o->get_id();
        if ( in_array( $o->get_status(), array('trash', 'auto-draft'), true ) ) {
            self::remove( $id );
            return true;
        }
        // A quote owes nothing and waits for no preparation (0.62.9).
        $active = !$o->has_status( array(
            'cancelled',
            'failed',
            'refunded',
            'checkout-draft'
        ) ) && !SaleStatus::quote( $o );
        $fulfil = false;
        $returns = false;
        $progress = ( $o->get_meta( '_storesuit_fulfilment' ) ?: array() );
        foreach ( $o->get_items() as $iid => $item ) {
            if ( (float) ($progress[$iid]['shipped'] ?? 0) < (float) $item->get_quantity() - abs( $o->get_qty_refunded_for_item( $iid ) ) ) {
                $fulfil = true;
            }
        }
        foreach ( ( $o->get_meta( '_storesuit_returns' ) ?: array() ) as $r ) {
            if ( in_array( $r['status'], array('requested', 'approved'), true ) ) {
                $returns = true;
            }
        }
        // A customer's request from the return form waits in the same queue until someone reviews it (the row already says so).
        if ( Operations::pending_withdrawal( $o ) ) {
            $returns = true;
        }
        $channel = Orders::channel( $o );
        $label = Orders::MARKETS[$channel] ?? I18n::t( Orders::CHANNELS[$channel] ?? (( $channel === 'pos' ? 'POS store' : 'Web' )) );
        global $wpdb;
        // Payment work: what the order still owes, unless nothing can be owed (closed, a quote) or it is a checkout left before
        // payment (0.62.12).
        $owed = ( $active && !Orders::abandoned_checkout( $o ) ? Orders::outstanding( $o ) : 0.0 );
        $row = array(
            'order_id'      => $id,
            'customer_id'   => $o->get_customer_id(),
            'phone_key'     => self::phone_key( (string) $o->get_billing_phone() ),
            'created_at'    => ( $o->get_date_created() ? $o->get_date_created()->date( 'Y-m-d H:i:s' ) : gmdate( 'Y-m-d H:i:s' ) ),
            'channel'       => $channel,
            'currency'      => $o->get_currency(),
            'status'        => $o->get_status(),
            'flow_stage'    => OrderFlow::read( $o )['stage'],
            'search_text'   => implode( ' ', array(
                $o->get_order_number(),
                $id,
                $o->get_formatted_billing_full_name(),
                $o->get_billing_email(),
                $o->get_billing_phone(),
                self::phone_digits( (string) $o->get_billing_phone() ),
                $channel,
                $label
            ) ),
            'needs_payment' => ( $owed > 0 ? 1 : 0 ),
            'needs_fulfil'  => ( $active && !$o->has_status( 'completed' ) && !Orders::shipped_status( $o ) && $fulfil && !OrderFlow::counter( $o ) ? 1 : 0 ),
            'needs_return'  => ( $returns ? 1 : 0 ),
        );
        // The amount the payment queue adds up (0 for every other row) and whether the order is paid by cash on delivery, so
        // the queue's total and its cash-on-delivery part come from the index; written once the table has the columns.
        if ( get_option( self::COLUMNS ) === '1' ) {
            $row['due'] = sprintf( '%.8F', ( $owed > 0 ? $owed : 0 ) );
            $row['cod'] = ( self::cod( $o ) ? 1 : 0 );
        }
        $result = $wpdb->replace( self::table(), $row );
        // A search cache failure must never roll back a successful sale. The index starts again from the beginning: a pass
        // under way would otherwise declare it ready with this stale row (8.30 review I1). A recovery step reports its own.
        if ( false === $result ) {
            wp_cache_delete( self::VERIFIED, 'storesuit' );
            if ( !IndexRecovery::reading() ) {
                IndexRecovery::restart();
            }
        } else {
            DashboardFacts::changed( $o );
        }
        return false !== $result;
    }

    /** The order (or refund) as the database holds it now, or null; this request's copies of it are dropped first. */
    private static function stored( int $id ) {
        // Orders::fresh() drops the caches and refuses what is not an order; a refund or a missing id is then read as it is.
        try {
            return Orders::fresh( $id );
        } catch ( \InvalidArgumentException $e ) {
            $o = wc_get_order( $id );
            return ( $o ?: null );
        }
    }

    /**
     * Paid by cash on delivery: WooCommerce's cod method, or a manual sale of the cod payment type (Orders::create sets both;
     * an older one may carry only the type). The orders Upgrade::cod_orders() lists; Orders::cod_unreceived() applies its rule
     * to them (leaving till records out), so a change of that rule needs no change here (Upgrade::cod_rule_reread()).
     */
    public static function cod( \WC_Order $o ) : bool {
        return $o->get_payment_method() === 'cod' || $o->get_meta( '_storesuit_payment_mode' ) === 'cod';
    }

    /** Rows to read again after a rule change while the index stays ready (0.62.12); sync() takes 150 per call. */
    public static function read_again( array $ids ) : void {
        $queued = get_option( 'storesuit_order_index_reread' );
        $ids = array_values( array_unique( array_merge( ( is_array( $queued ) ? array_map( 'intval', $queued ) : array() ), array_map( 'intval', $ids ) ) ) );
        if ( $ids ) {
            update_option( 'storesuit_order_index_reread', $ids, false );
        }
    }

    private static function reread() : void {
        $ids = get_option( 'storesuit_order_index_reread' );
        if ( !is_array( $ids ) || !$ids ) {
            return;
        }
        $batch = array_splice( $ids, 0, 150 );
        // The rest is written back before reading, so a request that stops halfway loses nothing; reading a row twice is harmless.
        if ( $ids ) {
            update_option( 'storesuit_order_index_reread', array_values( $ids ), false );
        } else {
            delete_option( 'storesuit_order_index_reread' );
        }
        foreach ( $batch as $id ) {
            self::changed( (int) $id );
        }
    }

    public static function sync() : array {
        // A ready index serves the screens; a background refresh of it (IndexRecovery::refresh()) gets its next job booked again
        // should one have been lost.
        if ( get_option( 'storesuit_order_index_ready' ) === '1' ) {
            self::reread();
            self::retime();
            self::phones();
            IndexRecovery::nudge();
            return array(
                'ready' => true,
            );
        }
        return IndexRecovery::sync();
    }

    /** Order desk filters and their defaults; saved order views store exactly these keys. */
    public const FILTERS = array(
        'queue'   => 'all',
        'channel' => '',
        'payment' => '',
        'period'  => '',
        'from'    => '',
        'to'      => '',
        'search'  => '',
        'sort'    => 'newest',
    );

    // Marketplace channels only while marketplaces are on (v2; 0.62.12): old marketplace orders stay under "All".
    public static function channels() : array {
        $channels = array_merge( array('web'), array_keys( Orders::CHANNELS ) );
        return array_values( array_unique( $channels ) );
    }

    /** The one validator for Order desk filters: a value that is not valid falls back to its default and its key is reported in $dropped. */
    public static function filters( array $data, ?array &$dropped = null ) : array {
        $dropped = array();
        $f = self::FILTERS;
        $pick = static function ( string $key, array $allowed ) use($data, &$f, &$dropped) : void {
            $value = Store::text( $data, $key );
            if ( $value === '' || $value === self::FILTERS[$key] ) {
                return;
            }
            if ( in_array( $value, $allowed, true ) ) {
                $f[$key] = $value;
            } else {
                $dropped[] = $key;
            }
        };
        $pick( 'queue', array_merge( array(
            'payment',
            'fulfil',
            'returns',
            SaleStatus::QUOTE_STAGE
        ), array_keys( OrderFlow::STAGES ) ) );
        $pick( 'channel', self::channels() );
        $pick( 'payment', array('due', 'paid') );
        $pick( 'period', array(
            'today',
            '7d',
            '30d',
            'month',
            'custom'
        ) );
        $pick( 'sort', array('oldest') );
        if ( $f['period'] === 'custom' ) {
            foreach ( array('from', 'to') as $key ) {
                $day = Store::text( $data, $key );
                if ( $day === '' ) {
                    continue;
                }
                if ( preg_match( '/^(\\d{4})-(\\d{2})-(\\d{2})$/', $day, $m ) && checkdate( (int) $m[2], (int) $m[3], (int) $m[1] ) ) {
                    $f[$key] = $day;
                } else {
                    $dropped[] = $key;
                }
            }
            if ( $f['from'] !== '' && $f['to'] !== '' && $f['from'] > $f['to'] ) {
                $f['to'] = '';
                $dropped[] = 'to';
            }
            if ( $f['from'] === '' && $f['to'] === '' ) {
                $f['period'] = '';
            }
        }
        $search = trim( Store::text( $data, 'search' ) );
        if ( mb_strlen( $search ) > 80 ) {
            $search = mb_substr( $search, 0, 80 );
            $dropped[] = 'search';
        }
        $f['search'] = $search;
        return $f;
    }

    /** Local-time bounds: created_at is stored in the site's timezone. */
    private static function range( array $f ) : array {
        $today = current_time( 'Y-m-d' );
        switch ( $f['period'] ) {
            case 'today':
                return array($today . ' 00:00:00', '');
            case '7d':
                return array(gmdate( 'Y-m-d', strtotime( $today . ' -6 days' ) ) . ' 00:00:00', '');
            case '30d':
                return array(gmdate( 'Y-m-d', strtotime( $today . ' -29 days' ) ) . ' 00:00:00', '');
            case 'month':
                return array(substr( $today, 0, 8 ) . '01 00:00:00', '');
            case 'custom':
                return array(( $f['from'] !== '' ? $f['from'] . ' 00:00:00' : '' ), ( $f['to'] !== '' ? $f['to'] . ' 23:59:59' : '' ));
        }
        return array('', '');
    }

    /**
     * One page of the Order desk and the counts of its queues. Every read is one literal statement: each optional filter is a constant
     * switch such as (%d=0 OR ...) that the optimizer drops when it is not asked for, and every value is a placeholder. The filters part
     * is the same in the counts and in the page; filter_args() holds its values in the order of its placeholders.
     *
     * Three optional parts (IDX-1), each only when asked for, so a caller that asks for none gets the answer it always had:
     * - 'rows' => 0: no page of orders ('orders' stays an empty list; the counts, the page numbers and the filters are the
     *   same). For a screen that shows only counts or totals (Home, Today), which then loads no order at all.
     * - 'totals' => 1: 'totals', per queue its count and what its orders still owe per currency, plus 'cod', the orders of the
     *   payment queue paid by cash on delivery; and 'totals_complete', false while a row written before the amounts were
     *   indexed still waits to be read again (Upgrade::index_repair()).
     * - 'channels' => 1: 'channels', the sales channels that have orders, among those the channel filter accepts.
     */
    public static function query( array $data ) : array {
        $f = self::filters( $data );
        $queues = self::queues( $f, $data );
        $filter = $f['queue'];
        $total = $queues[$filter] ?? $queues['all'];
        $pages = max( 1, (int) ceil( $total / 30 ) );
        $page = min( $pages, max( 1, absint( $data['page'] ?? 1 ) ) );
        $orders = array();
        if ( (string) ($data['rows'] ?? '') !== '0' ) {
            $ids = self::page_ids( $f, $data, ($page - 1) * 30 );
            self::prime( $ids );
            foreach ( $ids as $id ) {
                $o = wc_get_order( $id );
                if ( $o instanceof \WC_Order && !$o->has_status( 'trash' ) ) {
                    $orders[] = Orders::summary( $o );
                }
            }
        }
        $result = array(
            'orders'  => $orders,
            'total'   => $total,
            'page'    => $page,
            'pages'   => $pages,
            'queues'  => $queues,
            'filters' => $f,
        );
        if ( !empty( $data['totals'] ) ) {
            [$result['totals'], $result['totals_complete']] = self::totals( $f, $data, $queues );
        }
        if ( !empty( $data['channels'] ) ) {
            $result['channels'] = self::used_channels();
        }
        return $result;
    }

    /**
     * The sales channels that have orders (quotes included: the channel filter reads them too), in the order of channels():
     * only those the filter accepts, so an old marketplace order while marketplaces are off adds none. DISTINCT reads the
     * channel key alone.
     */
    public static function used_channels() : array {
        global $wpdb;
        $used = array_map( 'strval', (array) $wpdb->get_col( $wpdb->prepare( 'SELECT DISTINCT channel FROM %i', self::table() ) ) );
        return array_values( array_intersect( self::channels(), $used ) );
    }

    /**
     * Per queue (the keys of queues(), plus 'cod'): its count, as queues() counted it, and what its orders still owe, per
     * currency, rounded to the store's decimals, only the currencies with something owed. The amounts come from one read
     * over the same filters as the counts, grouped by currency, stage and quote status; a quote owes nothing. Only the
     * payment queue's rows carry an amount (OrderIndex::changed()), so 'all', 'payment' and the stages add up the same money,
     * each over its own orders. 'cod' counts and adds up the payment queue's cash-on-delivery orders. The second value says
     * whether every amount is known: a row of the payment queue written before the amounts were indexed has none yet.
     */
    private static function totals( array $f, array $data, array $queues ) : array {
        global $wpdb;
        $out = array();
        foreach ( $queues as $key => $count ) {
            $out[$key] = array(
                'count' => (int) $count,
                'due'   => array(),
            );
        }
        $out['cod'] = array(
            'count' => 0,
            'due'   => array(),
        );
        if ( get_option( self::COLUMNS ) !== '1' ) {
            return array($out, false);
        }
        [$from, $to] = self::range( $f );
        if ( $f['search'] === '' && $f['channel'] === '' && empty( $data['customer_id'] ) && $f['payment'] === '' && $from === '' && $to === '' ) {
            $rows = $wpdb->get_results( $wpdb->prepare( "SELECT currency, flow_stage, status IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ") AS is_quote, COALESCE(SUM(CASE WHEN needs_payment=1 THEN due ELSE 0 END),0) AS payment, COALESCE(SUM(CASE WHEN needs_fulfil=1 AND needs_payment=1 THEN due ELSE 0 END),0) AS fulfil, COALESCE(SUM(CASE WHEN needs_return=1 AND needs_payment=1 THEN due ELSE 0 END),0) AS returns_due, COALESCE(SUM(CASE WHEN cod=1 AND needs_payment=1 THEN 1 ELSE 0 END),0) AS cod_count, COALESCE(SUM(CASE WHEN cod=1 AND needs_payment=1 THEN due ELSE 0 END),0) AS cod_due, COALESCE(SUM(CASE WHEN needs_payment=1 AND due IS NULL THEN 1 ELSE 0 END),0) AS unknown FROM %i GROUP BY currency, flow_stage, is_quote", array_merge( SaleStatus::QUOTES, array(self::table()) ) ), ARRAY_A );
        } else {
            $rows = $wpdb->get_results( $wpdb->prepare( "SELECT currency, flow_stage, status IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ") AS is_quote, COALESCE(SUM(CASE WHEN needs_payment=1 THEN due ELSE 0 END),0) AS payment, COALESCE(SUM(CASE WHEN needs_fulfil=1 AND needs_payment=1 THEN due ELSE 0 END),0) AS fulfil, COALESCE(SUM(CASE WHEN needs_return=1 AND needs_payment=1 THEN due ELSE 0 END),0) AS returns_due, COALESCE(SUM(CASE WHEN cod=1 AND needs_payment=1 THEN 1 ELSE 0 END),0) AS cod_count, COALESCE(SUM(CASE WHEN cod=1 AND needs_payment=1 THEN due ELSE 0 END),0) AS cod_due, COALESCE(SUM(CASE WHEN needs_payment=1 AND due IS NULL THEN 1 ELSE 0 END),0) AS unknown FROM %i WHERE 1=1 AND (%d=0 OR search_text LIKE %s) AND (%d=0 OR channel = %s) AND (%d=0 OR customer_id = %d) AND (%s<>'due' OR (needs_payment=1 AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%s<>'paid' OR (needs_payment=0 AND flow_stage<>'closed' AND status NOT IN ('pending','checkout-draft') AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%d=0 OR created_at >= %s) AND (%d=0 OR created_at <= %s) GROUP BY currency, flow_stage, is_quote", array_merge( SaleStatus::QUOTES, array(self::table()), self::filter_args( $f, $data ) ) ), ARRAY_A );
        }
        if ( !is_array( $rows ) || $wpdb->last_error !== '' ) {
            return array($out, false);
        }
        $sums = array();
        $unknown = 0;
        $add = static function ( string $queue, string $currency, $amount ) use(&$sums) : void {
            $sums[$queue][$currency] = ($sums[$queue][$currency] ?? 0.0) + (float) $amount;
        };
        foreach ( $rows as $row ) {
            $currency = (string) $row['currency'];
            if ( (string) $row['is_quote'] !== '0' ) {
                continue;
            }
            // A quote owes nothing (and an unknown status is no order).
            $unknown += (int) $row['unknown'];
            foreach ( array('all', 'payment') as $queue ) {
                $add( $queue, $currency, $row['payment'] );
            }
            $add( 'fulfil', $currency, $row['fulfil'] );
            $add( 'returns', $currency, $row['returns_due'] );
            if ( isset( OrderFlow::STAGES[$row['flow_stage']] ) ) {
                $add( (string) $row['flow_stage'], $currency, $row['payment'] );
            }
            $out['cod']['count'] += (int) $row['cod_count'];
            $add( 'cod', $currency, $row['cod_due'] );
        }
        $decimals = wc_get_price_decimals();
        foreach ( $sums as $queue => $by_currency ) {
            ksort( $by_currency );
            foreach ( $by_currency as $currency => $amount ) {
                $amount = round( $amount, $decimals );
                if ( $amount > 0 ) {
                    $out[$queue]['due'][$currency] = $amount;
                }
            }
        }
        return array($out, $unknown === 0);
    }

    /**
     * The page's orders read together before their summaries, as WooCommerce's own order lists do: their posts and meta in
     * classic order storage, and through WooCommerce's order data store their meta, items, refunds and refunded totals. Each
     * summary then reads these records from this request's cache instead of a few queries per order; the values are the same.
     * OrderPaymentBadges::rows() reads its orders the same way.
     */
    public static function prime( array $ids ) : void {
        if ( !$ids ) {
            return;
        }
        if ( !(class_exists( '\\Automattic\\WooCommerce\\Utilities\\OrderUtil' ) && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled()) ) {
            _prime_post_caches( $ids, false, true );
        }
        $store = \WC_Data_Store::load( 'order' );
        if ( $store->has_callable( 'prime_caches_for_orders' ) ) {
            $store->prime_caches_for_orders( $ids, array(
                'type' => 'shop_order',
            ) );
        }
    }

    /** The values of the filters part, in the order of its placeholders. */
    private static function filter_args( array $f, array $data ) : array {
        global $wpdb;
        [$from, $to] = self::range( $f );
        $customer = !empty( $data['customer_id'] );
        return array_merge(
            array(
                ( $f['search'] !== '' ? 1 : 0 ),
                '%' . $wpdb->esc_like( self::search_term( $f['search'] ) ) . '%',
                ( $f['channel'] !== '' ? 1 : 0 ),
                $f['channel'],
                ( $customer ? 1 : 0 ),
                ( $customer ? absint( $data['customer_id'] ) : 0 )
            ),
            // "Paid" never includes cancelled, failed or refunded orders, which owe nothing but were not paid, nor a checkout
            // left before payment (0.62.12: it is no debt, so its flag is 0 too).
            // A quote is neither (0.65): it owes nothing until it is accepted, and nothing was paid for it.
            array($f['payment']),
            SaleStatus::QUOTES,
            array($f['payment']),
            SaleStatus::QUOTES,
            array(
                ( $from !== '' ? 1 : 0 ),
                $from,
                ( $to !== '' ? 1 : 0 ),
                $to
            )
        );
    }

    /**
     * The counts of the queues for the filters (the queue itself is not a filter here): the orders, the ones that owe money, wait to be
     * fulfilled or have a return, per flow stage, and the quotes. Quotes are no orders (0.62.9), left out by their status. One read,
     * grouped by stage and by whether the status is a quote.
     */
    private static function queues( array $f, array $data ) : array {
        global $wpdb;
        $queues = array(
            'all'     => 0,
            'payment' => 0,
            'fulfil'  => 0,
            'returns' => 0,
        );
        foreach ( OrderFlow::STAGES as $stage => $label ) {
            $queues[$stage] = 0;
        }
        $queues[SaleStatus::QUOTE_STAGE] = 0;
        [$from, $to] = self::range( $f );
        if ( $f['search'] === '' && $f['channel'] === '' && empty( $data['customer_id'] ) && $f['payment'] === '' && $from === '' && $to === '' ) {
            // No filter: the plain statement (the filters part would only make the read carry columns it never uses).
            $rows = $wpdb->get_results( $wpdb->prepare( "SELECT flow_stage, status IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ") AS is_quote, COUNT(*) AS n, COALESCE(SUM(needs_payment),0) AS payment, COALESCE(SUM(needs_fulfil),0) AS fulfil, COALESCE(SUM(needs_return),0) AS returns_count FROM %i GROUP BY flow_stage, is_quote", array_merge( SaleStatus::QUOTES, array(self::table()) ) ), ARRAY_A );
        } else {
            $rows = $wpdb->get_results( $wpdb->prepare( "SELECT flow_stage, status IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ") AS is_quote, COUNT(*) AS n, COALESCE(SUM(needs_payment),0) AS payment, COALESCE(SUM(needs_fulfil),0) AS fulfil, COALESCE(SUM(needs_return),0) AS returns_count FROM %i WHERE 1=1 AND (%d=0 OR search_text LIKE %s) AND (%d=0 OR channel = %s) AND (%d=0 OR customer_id = %d) AND (%s<>'due' OR (needs_payment=1 AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%s<>'paid' OR (needs_payment=0 AND flow_stage<>'closed' AND status NOT IN ('pending','checkout-draft') AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%d=0 OR created_at >= %s) AND (%d=0 OR created_at <= %s) GROUP BY flow_stage, is_quote", array_merge( SaleStatus::QUOTES, array(self::table()), self::filter_args( $f, $data ) ) ), ARRAY_A );
        }
        foreach ( (array) $rows as $row ) {
            if ( (string) $row['is_quote'] === '1' ) {
                $queues[SaleStatus::QUOTE_STAGE] += (int) $row['n'];
                continue;
            }
            if ( (string) $row['is_quote'] !== '0' ) {
                continue;
            }
            $queues['all'] += (int) $row['n'];
            $queues['payment'] += (int) $row['payment'];
            $queues['fulfil'] += (int) $row['fulfil'];
            $queues['returns'] += (int) $row['returns_count'];
            if ( isset( OrderFlow::STAGES[$row['flow_stage']] ) ) {
                $queues[$row['flow_stage']] = (int) $row['n'];
            }
        }
        return $queues;
    }

    /** One page (30) of order IDs for the filters and the queue, newest first or oldest first. */
    private static function page_ids( array $f, array $data, int $offset ) : array {
        global $wpdb;
        $filter = $f['queue'];
        $columns = array('payment', 'fulfil', 'returns');
        // A quote queue reads the quotes, every other queue the orders; a queue is one of the three flags or one flow stage.
        $stage = ( $filter !== SaleStatus::QUOTE_STAGE && !in_array( $filter, $columns, true ) && isset( OrderFlow::STAGES[$filter] ) ? 1 : 0 );
        $args = array_merge(
            array(self::table()),
            self::filter_args( $f, $data ),
            array($filter),
            SaleStatus::QUOTES,
            array($filter),
            SaleStatus::QUOTES,
            array(
                $filter,
                $filter,
                $filter,
                $stage,
                $filter,
                $offset
            )
        );
        if ( $f['sort'] === 'oldest' ) {
            return $wpdb->get_col( $wpdb->prepare( "SELECT order_id FROM %i WHERE 1=1 AND (%d=0 OR search_text LIKE %s) AND (%d=0 OR channel = %s) AND (%d=0 OR customer_id = %d) AND (%s<>'due' OR (needs_payment=1 AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%s<>'paid' OR (needs_payment=0 AND flow_stage<>'closed' AND status NOT IN ('pending','checkout-draft') AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%d=0 OR created_at >= %s) AND (%d=0 OR created_at <= %s) AND (%s<>'quote' OR status IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ")) AND (%s='quote' OR status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ")) AND (%s<>'payment' OR needs_payment=1) AND (%s<>'fulfil' OR needs_fulfil=1) AND (%s<>'returns' OR needs_return=1) AND (%d=0 OR flow_stage=%s) ORDER BY created_at ASC, order_id ASC LIMIT 30 OFFSET %d", $args ) );
        }
        return $wpdb->get_col( $wpdb->prepare( "SELECT order_id FROM %i WHERE 1=1 AND (%d=0 OR search_text LIKE %s) AND (%d=0 OR channel = %s) AND (%d=0 OR customer_id = %d) AND (%s<>'due' OR (needs_payment=1 AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%s<>'paid' OR (needs_payment=0 AND flow_stage<>'closed' AND status NOT IN ('pending','checkout-draft') AND status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . "))) AND (%d=0 OR created_at >= %s) AND (%d=0 OR created_at <= %s) AND (%s<>'quote' OR status IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ")) AND (%s='quote' OR status NOT IN (" . implode( ',', array_fill( 0, count( SaleStatus::QUOTES ), '%s' ) ) . ")) AND (%s<>'payment' OR needs_payment=1) AND (%s<>'fulfil' OR needs_fulfil=1) AND (%s<>'returns' OR needs_return=1) AND (%d=0 OR flow_stage=%s) ORDER BY created_at DESC, order_id DESC LIMIT 30 OFFSET %d", $args ) );
    }

}