<?php

namespace StoreSuit;

defined( 'ABSPATH' ) || exit;
require_once __DIR__ . '/ProductExtras.php';
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Messages are returned as JSON by Admin::ajax() and shown with textContent; HTML output escapes them where printed.
final class Products {
    /** Pro-only methods live under premium-suffixed names; old callers keep their names (paid build only). */
    public static function __callStatic( string $name, array $args ) {
        throw new \BadMethodCallException(self::class . '::' . $name);
    }

    /** The product's barcode in the free build: its GTIN, barcode or EAN field, else its SKU (the paid build asks the purchase engine). */
    private static function barcode( \WC_Product $p ) : string {
        foreach ( array('_global_unique_id', '_barcode', '_ean') as $key ) {
            $value = get_post_meta( $p->get_id(), $key, true );
            if ( '' !== $value && null !== $value ) {
                return (string) $value;
            }
        }
        return (string) $p->get_sku();
    }

    /**
     * The barcode stored on the product, with no SKU in its place (persona fixes REP-6): the product window's editable field.
     * barcode() and the engine's barcode_of() keep the SKU fallback for search, scanning and labels; a form that showed the SKU
     * in the barcode field wrote it into _barcode on every save. The paid build reads the purchase engine's keys (its public,
     * read-only Catalog::barcode_keys()), the free build WooCommerce's GTIN and the usual barcode fields.
     */
    private static function stored_barcode( \WC_Product $p ) : string {
        $keys = array('_global_unique_id', '_barcode', '_ean');
        foreach ( $keys as $key ) {
            $value = get_post_meta( $p->get_id(), (string) $key, true );
            if ( '' !== $value && null !== $value && is_scalar( $value ) ) {
                return (string) $value;
            }
        }
        return '';
    }

    /** Page actual sellable rows, not the first N children of a variable parent. */
    public static function search( array $data ) : array {
        // A scanner's changed code (its EAN check digit left out, a 0 added in front) searches for the code stored on the one
        // product it stands for; 'scan' tells the screen which code that is, so its exact-match pick still adds the product.
        $typed = Store::text( $data, 'search' );
        $term = ScanCode::term( $typed );
        $data['search'] = $term;
        $scan = ( $term !== $typed ? array(
            'scan' => $term,
        ) : array() );
        return self::search_catalog( $term, max( 1, absint( $data['page'] ?? 1 ) ) ) + $scan;
    }

    /**
     * The free build's product search (the universal search and the sale lookup): published simple products and options, found
     * by ID, title, SKU or barcode, 30 to a page. One literal statement per read: the optional search is the constant switch
     * (%d=0 OR ...), so the optimizer drops it without a term, and every value is a placeholder.
     */
    private static function search_catalog( string $term, int $page ) : array {
        global $wpdb;
        $like = '%' . $wpdb->esc_like( Store::search_fold( $term ) ) . '%';
        $has = ( $term !== '' ? 1 : 0 );
        // Grouped/external/variable parents are not sale lines.
        $count = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->posts} p LEFT JOIN {$wpdb->posts} parent ON parent.ID=p.post_parent WHERE ((p.post_type='product' AND p.post_status IN ('publish')) OR (p.post_type='product_variation' AND p.post_status='publish' AND parent.post_status IN ('publish')))" . " AND NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=p.ID AND tt.taxonomy='product_type' AND t.slug<>'simple')" . " AND (%d=0 OR (p.ID=%d OR REPLACE(REPLACE(REPLACE(p.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(parent.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(m.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s)))",
            $has,
            absint( $term ),
            $like,
            $like,
            $like
        ) );
        $pages = max( 1, (int) ceil( $count / 30 ) );
        $page = min( $page, $pages );
        // Exact ID, SKU or barcode matches first.
        $ids = $wpdb->get_col( $wpdb->prepare(
            "SELECT p.ID FROM {$wpdb->posts} p LEFT JOIN {$wpdb->posts} parent ON parent.ID=p.post_parent WHERE ((p.post_type='product' AND p.post_status IN ('publish')) OR (p.post_type='product_variation' AND p.post_status='publish' AND parent.post_status IN ('publish')))" . " AND NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=p.ID AND tt.taxonomy='product_type' AND t.slug<>'simple')" . " AND (%d=0 OR (p.ID=%d OR REPLACE(REPLACE(REPLACE(p.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(parent.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(m.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s)))" . " ORDER BY (p.ID=%d OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} exactmeta WHERE exactmeta.post_id=p.ID AND exactmeta.meta_key IN ('_sku','_barcode','_global_unique_id') AND exactmeta.meta_value=%s)) DESC, p.ID DESC LIMIT 30 OFFSET %d",
            $has,
            absint( $term ),
            $like,
            $like,
            $like,
            absint( $term ),
            $term,
            ($page - 1) * 30
        ) );
        return self::search_result(
            $ids,
            $count,
            $page,
            $pages,
            false
        );
    }

    /** The rows of a product search: the product, its price and stock, its barcode, and for an option its product and its option. */
    private static function search_result(
        array $ids,
        int $count,
        int $page,
        int $pages,
        bool $purchase
    ) : array {
        $rows = array();
        foreach ( $ids as $id ) {
            $p = wc_get_product( $id );
            if ( $p ) {
                $row = array(
                    'id'         => $p->get_id(),
                    'name'       => $p->get_name(),
                    'sku'        => $p->get_sku(),
                    'price'      => $p->get_price( 'edit' ),
                    'stock'      => $p->get_stock_quantity(),
                    'in_stock'   => $p->is_in_stock(),
                    'backorders' => $p->backorders_allowed(),
                    'barcode'    => self::barcode( $p ),
                ) + (( $purchase ? array(
                    'status' => ( $p->get_parent_id() ? get_post_status( $p->get_parent_id() ) : $p->get_status() ),
                ) : array() ));
                if ( $p->get_parent_id() ) {
                    $row['parent_id'] = $p->get_parent_id();
                    $row['parent_name'] = get_the_title( $p->get_parent_id() );
                    $row['option'] = wc_get_formatted_variation(
                        $p,
                        true,
                        false,
                        false
                    );
                }
                $rows[] = $row;
            }
        }
        return array(
            'rows'  => $rows,
            'count' => $count,
            'page'  => $page,
            'pages' => $pages,
        );
    }

    public static function card( int $id ) : array {
        Store::require_cap( 'manage_woocommerce' );
        $p = wc_get_product( $id );
        if ( !$p ) {
            throw new \InvalidArgumentException(I18n::t( 'Product not found.' ));
        }
        global $wpdb;
        $stock_id = $p->get_stock_managed_by_id();
        $incoming = array();
        $receipts = array();
        $counts = array();
        $suppliers = array();
        $variable = $p->is_type( 'variable' );
        $catalog_id = ( $p->get_parent_id() ?: $id );
        $categories = wp_get_post_terms( $catalog_id, 'product_cat', array(
            'fields' => 'names',
        ) );
        $tags = wp_get_post_terms( $catalog_id, 'product_tag', array(
            'fields' => 'names',
        ) );
        $editable = self::editable( $p );
        $options = ( $variable ? self::option_rows( $p, $incoming ) : array() );
        $family = ( $variable ? self::family_totals( $id, $options ) : null );
        // The cost and supplier checks are the paid build's, in their old places.
        $checks = array(
            'price' => '' !== (string) $editable['regular_price'],
        );
        $checks += array(
            'sku'      => '' !== (string) $editable['sku'],
            'category' => array_diff( array_map( 'intval', (array) $editable['category_ids'] ), array((int) get_option( 'default_product_cat', 0 )) ) !== array(),
        );
        if ( $variable ) {
            // A family is ready when its options are (review products-modal-v2 I2): the parent itself never has a price or cost.
            // Enabled options are judged, like the list's health filters; a family with only disabled options is judged by all of them.
            $judged = ( array_values( array_filter( $options, static fn( $o ) => $o['option_status'] === 'publish' ) ) ?: $options );
            $every = static fn( callable $f ): bool => $judged !== array() && count( array_filter( $judged, $f ) ) === count( $judged );
            $checks['price'] = $every( static fn( $o ) => '' !== (string) $o['regular_price'] );
            $checks['sku'] = $every( static fn( $o ) => '' !== (string) $o['sku'] );
        }
        $card = array_merge( $editable, array(
            'stock_owner'        => $stock_id,
            'on_order'           => $incoming[$stock_id] ?? 0,
            'categories'         => ( is_wp_error( $categories ) ? array() : array_map( array(Store::class, 'plain'), $categories ) ),
            'tags'               => ( is_wp_error( $tags ) ? array() : array_map( array(Store::class, 'plain'), $tags ) ),
            'catalog_id'         => $catalog_id,
            'parent_name'        => ( $p->get_parent_id() ? Store::plain( get_post_field( 'post_title', $p->get_parent_id(), 'raw' ) ) : '' ),
            'children'           => $options,
            'family'             => $family,
            'family_version'     => ( $variable ? self::family_version( $p, $options ) : '' ),
            'shared_stock'       => ( $variable ? $family['shared_stock'] : (bool) $p->get_manage_stock() ),
            'product_type'       => $p->get_type(),
            'image'              => ( wp_get_attachment_image_url( ( $p->get_image_id() ?: (int) get_post_thumbnail_id( $catalog_id ) ), 'woocommerce_thumbnail' ) ?: '' ),
            'status'             => $p->get_status(),
            'checks'             => $checks,
            'suppliers'          => $suppliers,
            'receipts'           => $receipts,
            'counts'             => $counts,
            'edit_url'           => admin_url( 'post.php?post=' . $catalog_id . '&action=edit' ),
            'storefront_url'     => self::storefront_url( $catalog_id ),
            'storefront_preview' => in_array( get_post_status( $catalog_id ), array('draft', 'pending', 'future'), true ),
            'storefront_private' => get_post_status( $catalog_id ) === 'private',
        ), self::publication_fields( $p ) );
        return $card;
    }

    /** The storefront owns this URL; options open their family's page, never an admin editor. */
    private static function storefront_url( int $id ) : string {
        $status = get_post_status( $id );
        if ( in_array( $status, array('draft', 'pending', 'future'), true ) ) {
            return ( current_user_can( 'edit_post', $id ) ? (string) get_preview_post_link( $id ) : '' );
        }
        if ( $status === 'private' && !current_user_can( 'read_post', $id ) ) {
            return '';
        }
        return ( in_array( $status, array('publish', 'private'), true ) ? (string) get_permalink( $id ) : '' );
    }

    /** Status has its own revision, including WooCommerce/editor edits and repeated publish/draft cycles. */
    private static function publication_fields( \WC_Product $p, ?string $version = null ) : array {
        $post = get_post( $p->get_id() );
        $revision = array(
            $p->get_id(),
            $p->get_status(),
            $post->post_modified_gmt,
            $post->post_date_gmt,
            $p->get_meta( '_storesuit_publication_revision', true ),
            $version ?? self::editable_fields( $p )['version']
        );
        return array(
            'publication_version'    => hash_hmac( 'sha256', wp_json_encode( $revision ), wp_salt( 'nonce' ) ),
            'can_change_publication' => !$p->get_parent_id() && current_user_can( 'edit_post', $p->get_id() ) && current_user_can( 'publish_products' ),
        );
    }

    /** Read options without the published-only list query; attributes are computed only for this window. */
    private static function option_rows( \WC_Product $parent, array $incoming ) : array {
        $ids = self::family_children( array($parent->get_id()) )[$parent->get_id()] ?? array();
        self::prime_products( $ids );
        $out = array();
        $labels = array();
        $terms = array();
        foreach ( $ids as $id ) {
            $child = wc_get_product( $id );
            if ( !$child instanceof \WC_Product_Variation ) {
                continue;
            }
            $row = self::editable( $child );
            $row['option_status'] = $child->get_status();
            $row['attributes'] = array();
            // Two attributes with the same label keep separate columns; an empty value is WooCommerce's "any" (shown as such by the window).
            foreach ( $child->get_attributes() as $key => $value ) {
                if ( !isset( $labels[$key] ) ) {
                    $label = wc_attribute_label( $key, $parent );
                    $labels[$key] = ( in_array( $label, $labels, true ) ? $label . ' (' . $key . ')' : $label );
                }
                $pair = $key . ':' . $value;
                if ( !array_key_exists( $pair, $terms ) ) {
                    $attribute_term = ( taxonomy_exists( $key ) ? get_term_by( 'slug', $value, $key ) : false );
                    $terms[$pair] = ( $attribute_term && !is_wp_error( $attribute_term ) ? $attribute_term->name : $value );
                }
                $row['attributes'][$labels[$key]] = $terms[$pair];
            }
            $row['on_order'] = $incoming[$row['stock_owner']] ?? 0;
            $out[] = $row;
        }
        return $out;
    }

    /**
     * A family's figures come from its options (review products-modal-v2 I2): prices and costs as ranges, stock and incoming once per
     * stock holder. Options that use the parent's pool count it once, as Reorder::on_order() is keyed by the holder.
     */
    private static function family_totals( int $parent_id, array $options ) : array {
        $prices = array();
        $pos = array();
        $costs = array();
        $stock = 0.0;
        $tracked = false;
        $incoming = 0.0;
        $owners = array();
        foreach ( $options as $row ) {
            if ( (string) $row['price'] !== '' ) {
                $prices[] = (float) $row['price'];
            }
            // Till price and cost: paid build.
            $owner = (int) $row['stock_owner'];
            if ( isset( $owners[$owner] ) ) {
                continue;
            }
            $owners[$owner] = true;
            if ( $row['stock'] !== null ) {
                $stock += (float) $row['stock'];
                $tracked = true;
            }
            $incoming += (float) ($row['on_order'] ?? 0);
        }
        $range = static fn( array $values, string $name ): array => array(
            $name . '_min' => ( $values ? min( $values ) : null ),
            $name . '_max' => ( $values ? max( $values ) : null ),
        );
        return $range( $prices, 'price' ) + $range( $pos, 'pos' ) + $range( $costs, 'cost' ) + array(
            'stock'        => ( $tracked ? $stock : null ),
            'on_order'     => $incoming,
            'shared_stock' => isset( $owners[$parent_id] ),
            'options'      => count( $options ),
            'costed'       => count( $costs ),
        );
    }

    /** A product's small picture for the product list (owner, 0.63.3): its own image, or none. */
    private static function thumb( \WC_Product $p ) : string {
        $image = (int) $p->get_image_id();
        return ( $image ? (string) wp_get_attachment_image_url( $image, 'woocommerce_gallery_thumbnail' ) : '' );
    }

    public static function editable( \WC_Product $p ) : array {
        $fields = self::editable_fields( $p );
        $more = array(
            'price' => $p->get_price( 'edit' ),
        );
        $more += array(
            'stock'       => $p->get_stock_quantity(),
            'stock_owner' => $p->get_stock_managed_by_id(),
        );
        $more += array(
            'status' => ( $p->get_parent_id() ? get_post_status( $p->get_parent_id() ) : $p->get_status() ),
        );
        return array_merge( $fields, $more );
    }

    /** The fields a save checks (with their version), without the shown prices: a variable product's price works out every option's. */
    private static function editable_fields( \WC_Product $p ) : array {
        // Term ids through the object term cache (primed by prime_products() for the list): wp_get_post_terms() queries every call.
        $id = $p->get_id();
        $catalog_id = ( $p->get_parent_id() ?: $id );
        $term_ids = static function ( int $post_id, string $taxonomy ) : array {
            $terms = get_the_terms( $post_id, $taxonomy );
            return ( is_array( $terms ) ? array_map( static fn( $t ) => (int) $t->term_id, $terms ) : array() );
        };
        $category_ids = $term_ids( $catalog_id, 'product_cat' );
        $tag_ids = $term_ids( $catalog_id, 'product_tag' );
        sort( $category_ids );
        sort( $tag_ids );
        $fields = array(
            'id'            => $id,
            'name'          => $p->get_name(),
            'sku'           => $p->get_sku( 'edit' ),
            'barcode'       => self::stored_barcode( $p ),
            'regular_price' => $p->get_regular_price( 'edit' ),
            'sale_price'    => $p->get_sale_price( 'edit' ),
        );
        $fields += array(
            'category_ids' => $category_ids,
            'tag_ids'      => $tag_ids,
        );
        // Keyed (0.63.2 review): a plain hash of these fields let anyone work out the one the screen leaves out for them, the cost.
        $fields['version'] = hash_hmac( 'sha256', wp_json_encode( $fields ), wp_salt( 'nonce' ) );
        return $fields;
    }

    public static function listing( array $data ) : array {
        Store::require_cap( 'manage_woocommerce' );
        global $wpdb;
        $term = ScanCode::term( Store::text( $data, 'search' ) );
        $health = Store::text( $data, 'health' );
        $page = max( 1, absint( $data['page'] ?? 1 ) );
        $visibility = ( Store::text( $data, 'visibility' ) ?: 'publish' );
        if ( $visibility === 'active' ) {
            $visibility = 'publish';
        } elseif ( $visibility === 'inactive' ) {
            $visibility = 'unpublished';
        }
        if ( !in_array( $visibility, array(
            'publish',
            'draft',
            'pending',
            'private',
            'future',
            'all',
            'unpublished'
        ), true ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Choose a valid product visibility.' ));
        }
        $filters = self::catalog_filters( $data );
        $advanced = (bool) array_filter( $filters, static fn( $v ) => $v !== '' && $v !== 0 );
        // The Turkish 10-day discount rule's warnings (theme, inc/tr-price.php) are judged in PHP from each product's price log, not in SQL: a view of its own.
        $tr = $health === 'tr_price' && ProductExtras::tr_available();
        if ( $health === '' || $health === 'all' ) {
            $health = 'all';
        } elseif ( !$tr && !in_array( $health, self::health_filters(), true ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Invalid product health filter.' ));
        }
        $flagged = ( $tr ? self::family_tr_ids( $term, $visibility ) : array() );
        $filtered = ( $advanced ? self::catalog_filter_query(
            $term,
            ( $tr ? 'all' : $health ),
            $visibility,
            $filters,
            ( $tr ? $flagged : null )
        ) : null );
        $count = ( $advanced ? $filtered['count'] : (( $tr ? count( $flagged ) : (int) self::family_count_rows( $term, $health, $visibility ) )) );
        $pages = max( 1, (int) ceil( $count / 30 ) );
        $page = min( $page, $pages );
        // Exact ID, SKU or barcode matches (of the product or one of its options) first.
        $ids = ( $advanced ? self::catalog_filter_ids( $filtered, $term, $page ) : (( $tr ? array_slice( $flagged, ($page - 1) * 30, 30 ) : self::family_ids(
            $term,
            $health,
            $visibility,
            $page
        ) )) );
        $children = self::family_children( $ids );
        $all = $ids;
        foreach ( $children as $list ) {
            $all = array_merge( $all, $list );
        }
        self::prime_products( $all );
        $matched = ( $advanced && $filtered['has_line'] ? self::catalog_filter_matches( $ids, $filtered ) : self::family_matching_children( $ids, $term, ( $tr ? 'all' : $health ) ) );
        if ( $advanced && $filtered['has_line'] && $term !== '' ) {
            $matched = array_intersect_key( $matched, self::family_matching_children( $ids, $term, 'all' ) );
        }
        $incoming = array();
        $rows = array();
        // Stock on order: paid build.
        // Each product once: the publication fields below reuse it and a simple product's version (it read them again, and a
        // variable product's prices with every option's; admin speed, 28 Oct 2026).
        $products = array();
        self::prime_images( $ids );
        foreach ( $ids as $id ) {
            $p = wc_get_product( $id );
            if ( !$p ) {
                continue;
            }
            $products[$id] = $p;
            self::give_children( $p, $children[$id] ?? array() );
            if ( !$p->is_type( 'variable' ) ) {
                $rows[] = array_merge( self::editable( $p ), array(
                    'kind'     => 'simple',
                    'status'   => $p->get_status(),
                    'children' => array(),
                    'on_order' => $incoming[$id] ?? 0,
                    'image'    => self::thumb( $p ),
                    'tr_flags' => ProductExtras::tr_flags( $p ),
                ) );
                continue;
            }
            $options = array();
            foreach ( $children[$id] ?? array() as $child_id ) {
                $child = wc_get_product( $child_id );
                if ( !$child instanceof \WC_Product_Variation ) {
                    continue;
                }
                $row = self::editable( $child );
                $row['option_status'] = $child->get_status();
                $row['on_order'] = $incoming[$child->get_stock_managed_by_id()] ?? 0;
                $row['matched'] = isset( $matched[$child_id] );
                $options[] = $row;
            }
            $version = self::family_version( $p, $options );
            $totals = self::family_totals( $id, $options );
            $rows[] = array(
                'id'            => $id,
                'kind'          => 'variable',
                'status'        => $p->get_status(),
                'name'          => $p->get_name(),
                'sku'           => $p->get_sku( 'edit' ),
                'children'      => $options,
                'variant_count' => count( $options ),
                'shared_stock'  => $totals['shared_stock'],
                'price_min'     => $totals['price_min'],
                'price_max'     => $totals['price_max'],
                'stock'         => $totals['stock'],
                'on_order'      => $totals['on_order'],
                'version'       => $version,
                'image'         => self::thumb( $p ),
                'tr_flags'      => ProductExtras::tr_flags( $p ),
            );
        }
        foreach ( $rows as $i => $row ) {
            $rows[$i] += self::publication_fields( $products[$row['id']], ( $row['kind'] === 'simple' ? $row['version'] : null ) );
        }
        // Unpublished families the same search and health filter would find (merge-vis3 review, minor 3); only the published view hides them.
        $hidden = ( $visibility === 'publish' ? ( $advanced ? self::catalog_filter_query(
            $term,
            ( $tr ? 'all' : $health ),
            'unpublished',
            $filters,
            ( $tr ? self::family_tr_ids( $term, 'unpublished' ) : null )
        )['count'] : (( $tr ? count( self::family_tr_ids( $term, 'unpublished' ) ) : (int) self::family_count_rows( $term, $health, 'unpublished' ) )) ) : 0 );
        $out = array(
            'rows'              => $rows,
            'count'             => $count,
            'page'              => $page,
            'pages'             => $pages,
            'health'            => self::family_health( $visibility ),
            'unpublished_count' => $hidden,
            'visibility'        => $visibility,
            'filters'           => $filters,
            'filter_options'    => self::catalog_filter_options(),
            'suppliers'         => array(),
        );
        return $out;
    }

    /** Read-only catalogue filters. Empty bounds differ from zero; invalid input never silently broadens a query. */
    private static function catalog_filters( array $data ) : array {
        $out = array();
        foreach ( array('brand_id', 'category_id') as $key ) {
            $v = Store::text( $data, $key );
            if ( $v !== '' && (!ctype_digit( $v ) || (int) $v < 0) ) {
                throw new \InvalidArgumentException(I18n::t( 'Choose a valid product filter.' ));
            }
            $out[$key] = (int) $v;
        }
        foreach ( array(
            'price_min',
            'price_max',
            'quantity_min',
            'quantity_max'
        ) as $key ) {
            $v = Store::text( $data, $key );
            if ( $v !== '' && (!preg_match( '/^\\d+(?:[.,]\\d+)?$/D', $v ) || !is_finite( (float) str_replace( ',', '.', $v ) )) ) {
                throw new \InvalidArgumentException(I18n::t( 'Enter a valid non-negative filter amount.' ));
            }
            $out[$key] = ( $v === '' ? '' : str_replace( ',', '.', $v ) );
        }
        foreach ( array('price', 'quantity') as $key ) {
            if ( $out[$key . '_min'] !== '' && $out[$key . '_max'] !== '' && (float) $out[$key . '_min'] > (float) $out[$key . '_max'] ) {
                throw new \InvalidArgumentException(I18n::t( 'The minimum filter amount cannot exceed the maximum.' ));
            }
        }
        foreach ( array(
            'stock_status'       => array(
                '',
                'instock',
                'outofstock',
                'onbackorder'
            ),
            'product_type'       => array('', 'simple', 'variable'),
            'catalog_visibility' => array(
                '',
                'visible',
                'catalog',
                'search',
                'hidden'
            ),
            'on_sale'            => array('', 'yes', 'no'),
        ) as $key => $values ) {
            $out[$key] = Store::text( $data, $key );
            if ( !in_array( $out[$key], $values, true ) ) {
                throw new \InvalidArgumentException(I18n::t( 'Choose a valid product filter.' ));
            }
        }
        foreach ( array(
            'category_id' => 'product_cat',
            'brand_id'    => 'product_brand',
        ) as $key => $taxonomy ) {
            if ( $out[$key] && (!taxonomy_exists( $taxonomy ) || !term_exists( $out[$key], $taxonomy )) ) {
                throw new \InvalidArgumentException(I18n::t( 'Choose a valid product filter.' ));
            }
        }
        return $out;
    }

    private static function catalog_filter_options() : array {
        $out = array(
            'categories'      => array(),
            'brands'          => array(),
            'brand_available' => taxonomy_exists( 'product_brand' ),
        );
        foreach ( array(
            'categories' => 'product_cat',
            'brands'     => 'product_brand',
        ) as $key => $taxonomy ) {
            if ( !taxonomy_exists( $taxonomy ) ) {
                continue;
            }
            $terms = get_terms( array(
                'taxonomy'   => $taxonomy,
                'hide_empty' => false,
                'orderby'    => 'name',
            ) );
            if ( is_wp_error( $terms ) ) {
                continue;
            }
            foreach ( $terms as $term ) {
                $out[$key][] = array(
                    'id'     => (int) $term->term_id,
                    'name'   => Store::plain( $term->name ),
                    'parent' => (int) $term->parent,
                );
            }
        }
        return $out;
    }

    /**
     * One candidate line must satisfy price, stock, quantity, sale and health together. Shared stock is read from its owner.
     * One literal statement for every combination: each filter is a constant switch such as (%d=0 OR ...) and (%s='' OR ...),
     * so the optimizer drops what is not asked for, and every value is a placeholder (catalog_filter_values()).
     */
    private static function catalog_filter_query(
        string $term,
        string $health,
        string $visibility,
        array $filters,
        ?array $flagged = null
    ) : array {
        global $wpdb;
        $q = self::catalog_filter_values(
            $term,
            $health,
            $visibility,
            $filters,
            $flagged
        );
        $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} family WHERE family.post_type='product' AND family.post_status IN (" . implode( ',', array_fill( 0, count( $q['states'] ), '%s' ) ) . ')' . " AND NOT EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_type' AND t.slug NOT IN ('simple','variable'))" . " AND (%d=0 OR (family.ID=%d OR REPLACE(REPLACE(REPLACE(family.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} fm WHERE fm.post_id=family.ID AND fm.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(fm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s) OR EXISTS(SELECT 1 FROM {$wpdb->posts} child LEFT JOIN {$wpdb->postmeta} cm ON cm.post_id=child.ID AND cm.meta_key IN ('_sku','_barcode','_global_unique_id') WHERE child.post_parent=family.ID AND child.post_type='product_variation' AND child.post_status IN ('publish','private') AND (REPLACE(REPLACE(REPLACE(child.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(cm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s))))" . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_brand' AND tt.term_id IN (" . implode( ',', array_fill( 0, count( $q['brands'] ), '%d' ) ) . ')))' . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_cat' AND tt.term_id IN (" . implode( ',', array_fill( 0, count( $q['categories'] ), '%d' ) ) . ')))' . " AND (%s='' OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_type' AND t.slug=%s))" . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_visibility' AND t.slug='exclude-from-catalog')=%d)" . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_visibility' AND t.slug='exclude-from-search')=%d)" . " AND (%d=0 OR ((NOT EXISTS(SELECT 1 FROM {$wpdb->posts} child WHERE child.post_parent=family.ID AND child.post_type='product_variation') AND EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.ID=family.ID AND ((%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) >= %f)) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) <= %f)) AND (%s='' OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} status WHERE status.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND status.meta_key='_stock_status' AND status.meta_value=%s)) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) >= %f))) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) <= %f))) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->wc_product_meta_lookup} sale WHERE sale.product_id=p.ID AND sale.onsale=%d)) AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>'')))))" . " OR EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.post_parent=family.ID AND p.post_type='product_variation' AND p.post_status='publish' AND ((%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) >= %f)) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) <= %f)) AND (%s='' OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} status WHERE status.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND status.meta_key='_stock_status' AND status.meta_value=%s)) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) >= %f))) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) <= %f))) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->wc_product_meta_lookup} sale WHERE sale.product_id=p.ID AND sale.onsale=%d)) AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>''))))))" . " AND (%d=0 OR family.ID IN (" . implode( ',', array_fill( 0, count( $q['flagged'] ), '%d' ) ) . '))', $q['args'] ) );
        if ( $wpdb->last_error !== '' || $count === null ) {
            throw new \RuntimeException(I18n::t( 'This report section could not be loaded. Other figures remain available.' ));
        }
        return $q + array(
            'count' => (int) $count,
        );
    }

    /**
     * The values of the combined-filter statements, in the order of their placeholders. A switched-off list keeps one 0 (no
     * product or term has ID 0), so its IN () stays valid; an empty discount-rule list (tr_price) matches nothing, as before.
     */
    private static function catalog_filter_values(
        string $term,
        string $health,
        string $visibility,
        array $filters,
        ?array $flagged
    ) : array {
        global $wpdb;
        $states = self::family_states( $visibility );
        [$threshold, $default] = self::health_values();
        $like = '%' . $wpdb->esc_like( Store::search_fold( $term ) ) . '%';
        $has = ( $term !== '' ? 1 : 0 );
        $lists = array();
        foreach ( array(
            'brand_id'    => 'product_brand',
            'category_id' => 'product_cat',
        ) as $key => $taxonomy ) {
            $lists[$key] = array(0);
            if ( !$filters[$key] ) {
                continue;
            }
            $lists[$key] = array($filters[$key]);
            $desc = get_term_children( $filters[$key], $taxonomy );
            if ( !is_wp_error( $desc ) ) {
                $lists[$key] = array_merge( $lists[$key], array_map( 'intval', $desc ) );
            }
        }
        $shown = $filters['catalog_visibility'];
        $has_shown = ( $shown !== '' ? 1 : 0 );
        $set = static fn( string $key ): int => ( $filters[$key] !== '' ? 1 : 0 );
        $line = array_merge( array(
            $set( 'price_min' ),
            (float) $filters['price_min'],
            $set( 'price_max' ),
            (float) $filters['price_max'],
            $filters['stock_status'],
            $filters['stock_status'],
            $set( 'quantity_min' ),
            (float) $filters['quantity_min'],
            $set( 'quantity_max' ),
            (float) $filters['quantity_max'],
            $set( 'on_sale' ),
            ( $filters['on_sale'] === 'yes' ? 1 : 0 )
        ), self::health_args( $health, $threshold, $default ) );
        $has_line = $set( 'price_min' ) || $set( 'price_max' ) || $set( 'stock_status' ) || $set( 'quantity_min' ) || $set( 'quantity_max' ) || $set( 'on_sale' ) || $health !== 'all';
        $listed = ( $flagged ?: array(0) );
        $args = array_merge(
            $states,
            array(
                $has,
                absint( $term ),
                $like,
                $like,
                $like,
                $like,
                ( $filters['brand_id'] ? 1 : 0 )
            ),
            $lists['brand_id'],
            array(( $filters['category_id'] ? 1 : 0 )),
            $lists['category_id'],
            array(
                $filters['product_type'],
                $filters['product_type'],
                $has_shown,
                ( in_array( $shown, array('search', 'hidden'), true ) ? 1 : 0 ),
                $has_shown,
                ( in_array( $shown, array('catalog', 'hidden'), true ) ? 1 : 0 ),
                ( $has_line ? 1 : 0 )
            ),
            $line,
            $line,
            array(( $flagged !== null ? 1 : 0 )),
            $listed
        );
        return array(
            'has_line'   => $has_line,
            'states'     => $states,
            'brands'     => $lists['brand_id'],
            'categories' => $lists['category_id'],
            'flagged'    => $listed,
            'line'       => $line,
            'args'       => $args,
        );
    }

    private static function catalog_filter_ids( array $q, string $term, int $page ) : array {
        global $wpdb;
        // Exact ID, SKU or barcode matches (of the product or one of its options) first.
        return array_map( 'intval', $wpdb->get_col( $wpdb->prepare( "SELECT family.ID FROM {$wpdb->posts} family WHERE family.post_type='product' AND family.post_status IN (" . implode( ',', array_fill( 0, count( $q['states'] ), '%s' ) ) . ')' . " AND NOT EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_type' AND t.slug NOT IN ('simple','variable'))" . " AND (%d=0 OR (family.ID=%d OR REPLACE(REPLACE(REPLACE(family.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} fm WHERE fm.post_id=family.ID AND fm.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(fm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s) OR EXISTS(SELECT 1 FROM {$wpdb->posts} child LEFT JOIN {$wpdb->postmeta} cm ON cm.post_id=child.ID AND cm.meta_key IN ('_sku','_barcode','_global_unique_id') WHERE child.post_parent=family.ID AND child.post_type='product_variation' AND child.post_status IN ('publish','private') AND (REPLACE(REPLACE(REPLACE(child.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(cm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s))))" . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_brand' AND tt.term_id IN (" . implode( ',', array_fill( 0, count( $q['brands'] ), '%d' ) ) . ')))' . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_cat' AND tt.term_id IN (" . implode( ',', array_fill( 0, count( $q['categories'] ), '%d' ) ) . ')))' . " AND (%s='' OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_type' AND t.slug=%s))" . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_visibility' AND t.slug='exclude-from-catalog')=%d)" . " AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_visibility' AND t.slug='exclude-from-search')=%d)" . " AND (%d=0 OR ((NOT EXISTS(SELECT 1 FROM {$wpdb->posts} child WHERE child.post_parent=family.ID AND child.post_type='product_variation') AND EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.ID=family.ID AND ((%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) >= %f)) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) <= %f)) AND (%s='' OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} status WHERE status.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND status.meta_key='_stock_status' AND status.meta_value=%s)) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) >= %f))) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) <= %f))) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->wc_product_meta_lookup} sale WHERE sale.product_id=p.ID AND sale.onsale=%d)) AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>'')))))" . " OR EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.post_parent=family.ID AND p.post_type='product_variation' AND p.post_status='publish' AND ((%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) >= %f)) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) <= %f)) AND (%s='' OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} status WHERE status.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND status.meta_key='_stock_status' AND status.meta_value=%s)) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) >= %f))) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) <= %f))) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->wc_product_meta_lookup} sale WHERE sale.product_id=p.ID AND sale.onsale=%d)) AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>''))))))" . " AND (%d=0 OR family.ID IN (" . implode( ',', array_fill( 0, count( $q['flagged'] ), '%d' ) ) . '))' . " ORDER BY (family.ID=%d OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} exactmeta WHERE exactmeta.post_id=family.ID AND exactmeta.meta_key IN ('_sku','_barcode','_global_unique_id') AND exactmeta.meta_value=%s) OR EXISTS(SELECT 1 FROM {$wpdb->posts} exactchild JOIN {$wpdb->postmeta} exactmeta ON exactmeta.post_id=exactchild.ID WHERE exactchild.post_parent=family.ID AND exactmeta.meta_key IN ('_sku','_barcode','_global_unique_id') AND exactmeta.meta_value=%s)) DESC,family.ID DESC LIMIT 30 OFFSET %d", array_merge( $q['args'], array(
            absint( $term ),
            $term,
            $term,
            ($page - 1) * 30
        ) ) ) ) );
    }

    private static function catalog_filter_matches( array $ids, array $q ) : array {
        if ( !$ids || !$q['has_line'] ) {
            return array();
        }
        global $wpdb;
        return array_fill_keys( array_map( 'intval', $wpdb->get_col( $wpdb->prepare( "SELECT p.ID FROM {$wpdb->posts} p WHERE p.post_parent IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") AND p.post_type='product_variation' AND p.post_status='publish' AND ((%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) >= %f)) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} amount WHERE amount.post_id=p.ID AND amount.meta_key='_price' AND amount.meta_value<>'' AND CAST(amount.meta_value AS DECIMAL(26,8)) <= %f)) AND (%s='' OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} status WHERE status.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND status.meta_key='_stock_status' AND status.meta_value=%s)) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) >= %f))) AND (%d=0 OR (EXISTS(SELECT 1 FROM {$wpdb->postmeta} managed WHERE managed.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND managed.meta_key='_manage_stock' AND managed.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} qty WHERE qty.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND qty.meta_key='_stock' AND qty.meta_value<>'' AND CAST(qty.meta_value AS DECIMAL(26,8)) <= %f))) AND (%d=0 OR EXISTS(SELECT 1 FROM {$wpdb->wc_product_meta_lookup} sale WHERE sale.product_id=p.ID AND sale.onsale=%d)) AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=IF(p.post_type='product_variation' AND NOT EXISTS(SELECT 1 FROM {$wpdb->postmeta} own WHERE own.post_id=p.ID AND own.meta_key='_manage_stock' AND own.meta_value='yes') AND EXISTS(SELECT 1 FROM {$wpdb->postmeta} pool WHERE pool.post_id=p.post_parent AND pool.meta_key='_manage_stock' AND pool.meta_value='yes'),p.post_parent,p.ID) AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>'')))", array_merge( $ids, $q['line'] ) ) ) ), true );
    }

    /** The post statuses a product-list view shows: one status, every unpublished one, or all of them. */
    private static function family_states( string $visibility ) : array {
        if ( !in_array( $visibility, array(
            'all',
            'unpublished',
            'publish',
            'draft',
            'pending',
            'private',
            'future'
        ), true ) ) {
            $visibility = 'publish';
        }
        if ( $visibility === 'all' ) {
            return array(
                'publish',
                'draft',
                'pending',
                'private',
                'future'
            );
        }
        return ( $visibility === 'unpublished' ? array(
            'draft',
            'pending',
            'private',
            'future'
        ) : array($visibility) );
    }

    /** What the health conditions compare with: the store's low-stock threshold and its default product category. Low stock is
        judged as WooCommerce warns (wc_get_low_stock_amount(), CAT-03): the stock owner's own amount, an option's product's, then this one. */
    private static function health_values() : array {
        return array(max( 0, (int) get_option( 'woocommerce_notify_low_stock_amount', 2 ) ), (int) get_option( 'default_product_cat', 0 ));
    }

    /** The values of the health conditions, in the order of their placeholders: each condition's key switch, the low-stock threshold and the default product category. */
    private static function health_args( string $health, int $threshold, int $default ) : array {
        return array(
            $health,
            $health,
            $threshold,
            $health,
            $health,
            $health,
            $health,
            $default,
            $health
        );
    }

    /**
     * How many families (simple and variable products) a view of the product list holds: the count behind its pages and the
     * dashboard cards. One literal statement for every view: the optional search and health filter are constant switches such
     * as (%d=0 OR ...) and (%s<>'low_stock' OR ...), so the optimizer drops what is not asked for, and every value is a placeholder.
     * The raw read is returned: callers cast it or treat null as a failed read. The health conditions are judged on sellable
     * lines: a simple product itself, or an enabled option (the family's own row too for low stock).
     * The picture check looks at the line and its family one at a time: a single "post_id IN (p.ID, p.post_parent)" lookup cannot
     * use the postmeta index and took 14 s on a store with 5,000 lines (admin audit C1, 28 Oct 2026).
     */
    private static function family_count_rows( string $term, string $health, string $visibility ) {
        global $wpdb;
        $states = self::family_states( $visibility );
        [$threshold, $default] = self::health_values();
        $like = '%' . $wpdb->esc_like( Store::search_fold( $term ) ) . '%';
        $has = ( $term !== '' ? 1 : 0 );
        return $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} family WHERE family.post_type='product' AND family.post_status IN (" . implode( ',', array_fill( 0, count( $states ), '%s' ) ) . ')' . " AND NOT EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_type' AND t.slug NOT IN ('simple','variable'))" . " AND (%d=0 OR (family.ID=%d OR REPLACE(REPLACE(REPLACE(family.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} fm WHERE fm.post_id=family.ID AND fm.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(fm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s) OR EXISTS(SELECT 1 FROM {$wpdb->posts} child LEFT JOIN {$wpdb->postmeta} cm ON cm.post_id=child.ID AND cm.meta_key IN ('_sku','_barcode','_global_unique_id') WHERE child.post_parent=family.ID AND child.post_type='product_variation' AND child.post_status IN ('publish','private') AND (REPLACE(REPLACE(REPLACE(child.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(cm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s))))" . " AND (%s='all' OR (((%s='low_stock' OR NOT EXISTS(SELECT 1 FROM {$wpdb->posts} child WHERE child.post_parent=family.ID AND child.post_type='product_variation')) AND EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.ID=family.ID AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=p.ID AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=p.ID AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>'')))) OR EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.post_parent=family.ID AND p.post_type='product_variation' AND p.post_status='publish' AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=p.ID AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=p.ID AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>'')))))", array_merge(
            $states,
            array(
                $has,
                absint( $term ),
                $like,
                $like,
                $like,
                $like,
                $health,
                $health
            ),
            self::health_args( $health, $threshold, $default ),
            self::health_args( $health, $threshold, $default )
        ) ) );
    }

    /** The family IDs of one page (30) of a view of the product list; the same view as family_count_rows(), exact ID, SKU and barcode matches first. */
    private static function family_ids(
        string $term,
        string $health,
        string $visibility,
        int $page
    ) : array {
        global $wpdb;
        $states = self::family_states( $visibility );
        [$threshold, $default] = self::health_values();
        $like = '%' . $wpdb->esc_like( Store::search_fold( $term ) ) . '%';
        $has = ( $term !== '' ? 1 : 0 );
        // Exact ID, SKU or barcode matches (of the product or one of its options) first.
        return array_map( 'intval', $wpdb->get_col( $wpdb->prepare( "SELECT family.ID FROM {$wpdb->posts} family WHERE family.post_type='product' AND family.post_status IN (" . implode( ',', array_fill( 0, count( $states ), '%s' ) ) . ')' . " AND NOT EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_type' AND t.slug NOT IN ('simple','variable'))" . " AND (%d=0 OR (family.ID=%d OR REPLACE(REPLACE(REPLACE(family.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} fm WHERE fm.post_id=family.ID AND fm.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(fm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s) OR EXISTS(SELECT 1 FROM {$wpdb->posts} child LEFT JOIN {$wpdb->postmeta} cm ON cm.post_id=child.ID AND cm.meta_key IN ('_sku','_barcode','_global_unique_id') WHERE child.post_parent=family.ID AND child.post_type='product_variation' AND child.post_status IN ('publish','private') AND (REPLACE(REPLACE(REPLACE(child.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(cm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s))))" . " AND (%s='all' OR (((%s='low_stock' OR NOT EXISTS(SELECT 1 FROM {$wpdb->posts} child WHERE child.post_parent=family.ID AND child.post_type='product_variation')) AND EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.ID=family.ID AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=p.ID AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=p.ID AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>'')))) OR EXISTS(SELECT 1 FROM {$wpdb->posts} p WHERE p.post_parent=family.ID AND p.post_type='product_variation' AND p.post_status='publish' AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=p.ID AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=p.ID AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>'')))))" . " ORDER BY (family.ID=%d OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} exactmeta WHERE exactmeta.post_id=family.ID AND exactmeta.meta_key IN ('_sku','_barcode','_global_unique_id') AND exactmeta.meta_value=%s) OR EXISTS(SELECT 1 FROM {$wpdb->posts} exactchild JOIN {$wpdb->postmeta} exactmeta ON exactmeta.post_id=exactchild.ID WHERE exactchild.post_parent=family.ID AND exactmeta.meta_key IN ('_sku','_barcode','_global_unique_id') AND exactmeta.meta_value=%s)) DESC,family.ID DESC LIMIT 30 OFFSET %d", array_merge(
            $states,
            array(
                $has,
                absint( $term ),
                $like,
                $like,
                $like,
                $like,
                $health,
                $health
            ),
            self::health_args( $health, $threshold, $default ),
            self::health_args( $health, $threshold, $default ),
            array(
                absint( $term ),
                $term,
                $term,
                ($page - 1) * 30
            )
        ) ) ) );
    }

    /** A dashboard count must use the exact same family filter as its destination list. A failed read is an error, never 0. */
    public static function family_count( string $health ) : int {
        Store::require_cap( 'manage_woocommerce' );
        if ( !in_array( $health, self::health_filters(), true ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Invalid product health filter.' ));
        }
        global $wpdb;
        $count = self::family_count_rows( '', $health, 'publish' );
        if ( $wpdb->last_error !== '' || $count === null ) {
            throw new \RuntimeException(I18n::t( 'This report section could not be loaded. Other figures remain available.' ));
        }
        return (int) $count;
    }

    /**
     * The view counters are nine counts over the whole catalog (0.15-0.18 s on a live store, on every page and filter of the
     * list; admin speed, 28 Oct 2026), kept per view, discount-rule state and low-stock amount. A store synced with a
     * marketplace changes products every minute, so a change no longer drops them: it marks them stale (CHANGED_AT) and the
     * list shows the last counts at once while they are counted again in the background (HEALTH_RECOUNT, one waiting at
     * most, a minute apart at least), as the Reports stock card does. Counted now when nothing was counted before or this
     * very request changed a product (its own save is shown); ten minutes old, they are counted again in any case.
     */
    private const HEALTH_CACHE = 'storesuit_family_health';

    public const HEALTH_RECOUNT = 'storesuit_family_health_recount';

    private const CHANGED_AT = 'storesuit_products_changed_at';

    private const STALE_AFTER = 600;

    /** The exception code of a refused price of 0 (zero_price()): the router names it, the screens ask and send free=1 (admin.js api()). */
    public const ZERO_PRICE = 4020;

    /**
     * A web price of 0 sells the product for free online (Denetim R2-2: a typo left a web price of ₺0 beside a till price of
     * ₺179.90). It is refused until the person confirms that the product is free (free=1); a product that is free already stays so.
     */
    private static function zero_price( float $amount, array $data, string $before = '' ) : void {
        if ( $amount > 0 || !empty( $data['free'] ) || $before !== '' && (float) $before <= 0 ) {
            return;
        }
        throw new \InvalidArgumentException(I18n::t( 'A price of 0 sells this product for free online. Enter the price, or confirm that the product is free.' ), self::ZERO_PRICE);
    }

    private static bool $health_dropped = false;

    private static bool $changed_here = false;

    /** Whether a change since the counters were last kept still has to mark them stale. */
    public static function health_pending() : bool {
        return !self::$health_dropped;
    }

    /** Something counted after the last change was kept: the next change marks it stale again. */
    public static function health_kept() : void {
        self::$health_dropped = false;
    }

    /** A product, its meta or its stock changed (Reports::stock_changed()): one write per request, nothing is dropped. */
    public static function health_changed() : void {
        self::$changed_here = true;
        if ( self::$health_dropped ) {
            return;
        }
        self::$health_dropped = true;
        update_option( self::CHANGED_AT, microtime( true ), false );
    }

    /**
     * How a kept count (counted at $at) is used: 'fresh' as it is; 'now' to count while the page waits; 'later' shown as it
     * is while it is counted again in the background; 'kept' shown as it is with a background count already near.
     */
    public static function kept_state( ?float $at ) : string {
        if ( $at === null ) {
            return 'now';
        }
        $stale = $at < (float) get_option( self::CHANGED_AT, 0 ) || microtime( true ) - $at > self::STALE_AFTER;
        if ( !$stale ) {
            return 'fresh';
        }
        if ( self::$changed_here ) {
            return 'now';
        }
        return ( microtime( true ) - $at < MINUTE_IN_SECONDS ? 'kept' : 'later' );
    }

    private static function family_health( string $visibility = 'publish' ) : array {
        $kept = get_option( self::HEALTH_CACHE );
        $kept = ( is_array( $kept ) ? $kept : array() );
        $last = $kept[self::health_variant( $visibility )] ?? null;
        $state = ( is_array( $last ) && is_array( $last['counts'] ?? null ) ? self::kept_state( (float) ($last['at'] ?? 0) ) : 'now' );
        if ( $state === 'now' ) {
            return self::health_count( $visibility );
        }
        if ( $state === 'later' ) {
            Store::later(
                self::HEALTH_RECOUNT,
                array($visibility),
                'storesuit',
                0
            );
        }
        return $last['counts'];
    }

    /** The background count (Store::later() from family_health()). */
    public static function health_recount( $visibility = 'publish' ) : void {
        self::health_count( ( in_array( $visibility, array(
            'all',
            'unpublished',
            'publish',
            'draft',
            'pending',
            'private',
            'future'
        ), true ) ? $visibility : 'publish' ) );
    }

    private static function health_variant( string $visibility ) : string {
        return $visibility . '|' . (( ProductExtras::tr_available() ? 'tr' : '' )) . '|' . get_option( 'woocommerce_notify_low_stock_amount', '' );
    }

    private static function health_count( string $visibility ) : array {
        $at = microtime( true );
        $tr = ProductExtras::tr_available();
        $counts = array();
        foreach ( array_merge( array('all'), self::health_filters() ) as $key ) {
            $counts[$key] = (int) self::family_count_rows( '', (string) $key, $visibility );
        }
        // The discount rule's view is there only while the rule is on (a store in Turkey with the StoreRuler theme).
        if ( $tr ) {
            $counts['tr_price'] = count( self::family_tr_ids( '', $visibility ) );
        }
        $kept = get_option( self::HEALTH_CACHE );
        $kept = ( is_array( $kept ) ? $kept : array() );
        $kept[self::health_variant( $visibility )] = array(
            'counts' => $counts,
            'at'     => $at,
        );
        update_option( self::HEALTH_CACHE, $kept, false );
        self::health_kept();
        return $counts;
    }

    /**
     * The families of a product-list view that the Turkish 10-day discount rule warns about (newest first): the candidates are
     * the families with a sale price on the product or an enabled option (one literal statement, as the other views), then the
     * theme's own check (storeruler_tr_price_flags) judges each from its price log. Kept for the request.
     */
    private static function family_tr_ids( string $term, string $visibility ) : array {
        static $cache = array();
        $key = $term . '|' . $visibility;
        if ( isset( $cache[$key] ) ) {
            return $cache[$key];
        }
        global $wpdb;
        $states = self::family_states( $visibility );
        $like = '%' . $wpdb->esc_like( Store::search_fold( $term ) ) . '%';
        $has = ( $term !== '' ? 1 : 0 );
        $ids = array_map( 'intval', $wpdb->get_col( $wpdb->prepare( "SELECT family.ID FROM {$wpdb->posts} family WHERE family.post_type='product' AND family.post_status IN (" . implode( ',', array_fill( 0, count( $states ), '%s' ) ) . ')' . " AND NOT EXISTS(SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=family.ID AND tt.taxonomy='product_type' AND t.slug NOT IN ('simple','variable'))" . " AND (%d=0 OR (family.ID=%d OR REPLACE(REPLACE(REPLACE(family.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} fm WHERE fm.post_id=family.ID AND fm.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(fm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s) OR EXISTS(SELECT 1 FROM {$wpdb->posts} child LEFT JOIN {$wpdb->postmeta} cm ON cm.post_id=child.ID AND cm.meta_key IN ('_sku','_barcode','_global_unique_id') WHERE child.post_parent=family.ID AND child.post_type='product_variation' AND child.post_status IN ('publish','private') AND (REPLACE(REPLACE(REPLACE(child.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(cm.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s))))" . " AND (EXISTS(SELECT 1 FROM {$wpdb->postmeta} sm WHERE sm.post_id=family.ID AND sm.meta_key='_sale_price' AND sm.meta_value<>'') OR EXISTS(SELECT 1 FROM {$wpdb->posts} sc JOIN {$wpdb->postmeta} sm ON sm.post_id=sc.ID AND sm.meta_key='_sale_price' AND sm.meta_value<>'' WHERE sc.post_parent=family.ID AND sc.post_type='product_variation' AND sc.post_status='publish'))" . ' ORDER BY family.ID DESC LIMIT 2000', array_merge( $states, array(
            $has,
            absint( $term ),
            $like,
            $like,
            $like,
            $like
        ) ) ) ) );
        $children = self::family_children( $ids );
        $all = $ids;
        foreach ( $children as $list ) {
            $all = array_merge( $all, $list );
        }
        self::prime_products( $all );
        $out = array();
        foreach ( $ids as $id ) {
            $p = wc_get_product( $id );
            if ( !$p ) {
                continue;
            }
            self::give_children( $p, $children[$id] ?? array() );
            if ( ProductExtras::tr_flags( $p ) ) {
                $out[] = $id;
            }
        }
        return $cache[$key] = $out;
    }

    private static function family_children( array $ids, bool $locked = false ) : array {
        global $wpdb;
        if ( !$ids ) {
            return array();
        }
        $ids = array_values( array_map( 'intval', $ids ) );
        $rows = ( $locked ? $wpdb->get_results( $wpdb->prepare( "SELECT ID,post_parent FROM {$wpdb->posts} WHERE post_type='product_variation' AND post_status IN ('publish','private') AND post_parent IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') ORDER BY menu_order,ID FOR UPDATE', $ids ), ARRAY_A ) : $wpdb->get_results( $wpdb->prepare( "SELECT ID,post_parent FROM {$wpdb->posts} WHERE post_type='product_variation' AND post_status IN ('publish','private') AND post_parent IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') ORDER BY menu_order,ID', $ids ), ARRAY_A ) );
        if ( $wpdb->last_error !== '' ) {
            throw new \RuntimeException(I18n::t( 'The options could not be locked. Try again.' ));
        }
        $out = array();
        foreach ( $rows as $row ) {
            $out[(int) $row['post_parent']][] = (int) $row['ID'];
        }
        return $out;
    }

    private static function prime_products( array $ids ) : void {
        $ids = array_values( array_unique( array_map( 'intval', $ids ) ) );
        if ( !$ids ) {
            return;
        }
        if ( function_exists( '_prime_post_caches' ) ) {
            _prime_post_caches( $ids, false, true );
        }
        update_meta_cache( 'post', $ids );
        update_object_term_cache( $ids, 'product' );
        self::prime_wc_meta( $ids );
    }

    /**
     * WooCommerce keeps each product's meta rows (with their ids) apart from WordPress's meta cache and reads them one product
     * at a time when they are not kept: 147 statements on a cold first page of a live store's list (admin speed, 28 Oct
     * 2026). Those not kept are read here in one statement and kept where WooCommerce looks (WC_Data's own priming).
     */
    private static function prime_wc_meta( array $ids ) : void {
        if ( !method_exists( 'WC_Data', 'prime_raw_meta_data_cache' ) || !method_exists( 'WC_Data', 'generate_meta_cache_key' ) ) {
            return;
        }
        $keys = array();
        foreach ( $ids as $id ) {
            $keys[$id] = \WC_Data::generate_meta_cache_key( $id, 'products' );
        }
        $kept = wp_cache_get_multiple( array_values( $keys ), 'products' );
        $missing = array();
        foreach ( $keys as $id => $key ) {
            if ( !is_array( $kept[$key] ?? null ) ) {
                $missing[] = $id;
            }
        }
        global $wpdb;
        foreach ( array_chunk( $missing, 200 ) as $chunk ) {
            $rows = $wpdb->get_results( $wpdb->prepare( "SELECT post_id,meta_id,meta_key,meta_value FROM {$wpdb->postmeta} WHERE post_id IN (" . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ') ORDER BY meta_id', $chunk ) );
            if ( $wpdb->last_error !== '' || !is_array( $rows ) ) {
                return;
            }
            $by = array_fill_keys( $chunk, array() );
            foreach ( $rows as $row ) {
                $id = (int) $row->post_id;
                unset($row->post_id);
                $by[$id][] = $row;
            }
            \WC_Data::prime_raw_meta_data_cache( $by, 'products' );
        }
    }

    /**
     * A variable product reads its options with two statements of its own when they are not kept (a transient each product
     * change drops). The list has just read the same options in the same order (family_children(): enabled and disabled,
     * WooCommerce's "all"), so the product is given them; not while other code changes that read.
     */
    private static function give_children( \WC_Product $p, array $children ) : void {
        if ( !$p instanceof \WC_Product_Variable || has_filter( 'woocommerce_variable_children_args' ) ) {
            return;
        }
        $p->set_children( $children );
        if ( get_option( 'woocommerce_hide_out_of_stock_items' ) !== 'yes' ) {
            $p->set_visible_children( array_values( array_filter( $children, static fn( $id ) => get_post_status( $id ) === 'publish' ) ) );
        }
    }

    /** The first page's pictures (their posts and sizes) in two statements, not two per picture. */
    private static function prime_images( array $ids ) : void {
        $images = array();
        foreach ( $ids as $id ) {
            $image = (int) get_post_meta( $id, '_thumbnail_id', true );
            if ( $image ) {
                $images[] = $image;
            }
        }
        if ( $images && function_exists( '_prime_post_caches' ) ) {
            _prime_post_caches( array_values( array_unique( $images ) ), false, true );
        }
    }

    private static function family_matching_children( array $ids, string $term, string $health ) : array {
        // A search finds a family through a disabled (private) option too, so that option is a match; health filters judge enabled options only (merge-vis3 review, minor 2).
        if ( !$ids || $term === '' && $health === 'all' ) {
            return array();
        }
        global $wpdb;
        $ids = array_values( array_map( 'intval', $ids ) );
        [$threshold, $default] = self::health_values();
        $like = '%' . $wpdb->esc_like( Store::search_fold( $term ) ) . '%';
        $has = ( $term !== '' ? 1 : 0 );
        return array_fill_keys( array_map( 'intval', $wpdb->get_col( $wpdb->prepare( "SELECT p.ID FROM {$wpdb->posts} p LEFT JOIN {$wpdb->posts} parent ON parent.ID=p.post_parent WHERE p.post_parent IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") AND p.post_type='product_variation' AND (p.post_status='publish' OR (%s='all' AND p.post_status='private')) AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=p.ID AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=p.ID AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>''))" . " AND (%d=0 OR (p.ID=%d OR REPLACE(REPLACE(REPLACE(p.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR REPLACE(REPLACE(REPLACE(parent.post_title,'ı','i'),'İ','i'),'I','i') LIKE %s OR EXISTS(SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_sku','_barcode','_global_unique_id') AND REPLACE(REPLACE(REPLACE(m.meta_value,'ı','i'),'İ','i'),'I','i') LIKE %s)))", array_merge(
            $ids,
            array($health),
            self::health_args( $health, $threshold, $default ),
            array(
                $has,
                absint( $term ),
                $like,
                $like,
                $like
            )
        ) ) ) ), true );
    }

    private static function family_version( \WC_Product $parent, array $children ) : string {
        return hash( 'sha256', wp_json_encode( array(
            'id'       => $parent->get_id(),
            'name'     => $parent->get_name(),
            'sku'      => $parent->get_sku( 'edit' ),
            'children' => array_map( static fn( $row ) => array($row['id'], $row['version']), $children ),
        ) ) );
    }

    private static function family_price_plan( array $data, bool $locked = false ) : array {
        Store::require_cap( 'manage_woocommerce' );
        $id = absint( $data['id'] ?? 0 );
        $parent = wc_get_product( $id );
        if ( !$parent || !$parent->is_type( 'variable' ) || !current_user_can( 'edit_post', $id ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Choose a variable product you may edit.' ));
        }
        $children = self::family_children( array($id), $locked )[$id] ?? array();
        if ( !$children ) {
            throw new \InvalidArgumentException(I18n::t( 'This product has no options to update.' ));
        }
        self::prime_products( $children );
        $rows = array();
        foreach ( $children as $child_id ) {
            if ( !current_user_can( 'edit_post', $child_id ) ) {
                throw new \RuntimeException(I18n::t( 'One of the options can no longer be edited.' ));
            }
            $p = wc_get_product( $child_id );
            if ( !$p instanceof \WC_Product_Variation || $p->get_parent_id() !== $id ) {
                throw new \RuntimeException(I18n::t( 'The options changed. Refresh the list.' ));
            }
            $rows[] = self::editable( $p );
        }
        $version = self::family_version( $parent, $rows );
        if ( !hash_equals( $version, Store::text( $data, 'version' ) ) ) {
            throw new \RuntimeException(I18n::t( 'The options changed in another screen. Refresh the list and review them.' ));
        }
        $keys = array('regular_price', 'sale_price');
        // The till price: paid build.
        $values = array();
        foreach ( $keys as $key ) {
            if ( array_key_exists( $key, $data ) ) {
                $raw = Store::text( $data, $key );
                $values[$key] = ( $raw === '' ? '' : Store::amount( $raw ) );
            }
        }
        if ( !$values ) {
            throw new \InvalidArgumentException(I18n::t( 'Choose a price to update.' ));
        }
        if ( ($values['regular_price'] ?? null) === '' ) {
            throw new \InvalidArgumentException(I18n::t( 'Enter the new online price. It cannot be left blank.' ));
        }
        foreach ( $rows as $row ) {
            $regular = ( array_key_exists( 'regular_price', $values ) ? $values['regular_price'] : $row['regular_price'] );
            $sale = ( array_key_exists( 'sale_price', $values ) ? $values['sale_price'] : $row['sale_price'] );
            if ( $sale !== '' && ($regular === '' || (float) $sale >= (float) $regular) ) {
                throw new \InvalidArgumentException(I18n::t( 'The sale price must be lower than the regular price. Leave it blank when there is no sale.' ));
            }
        }
        $token = hash( 'sha256', wp_json_encode( array($version, $values) ) );
        return array(
            'id'      => $id,
            'version' => $version,
            'rows'    => $rows,
            'values'  => $values,
            'token'   => $token,
            'count'   => count( $rows ),
        );
    }

    public static function family_price_preview( array $data ) : array {
        $plan = self::family_price_plan( $data );
        $old = array();
        foreach ( $plan['rows'] as $row ) {
            if ( $row['regular_price'] !== '' ) {
                $old[] = (float) $row['regular_price'];
            }
        }
        return array(
            'token'   => $plan['token'],
            'count'   => $plan['count'],
            'old_min' => ( $old ? min( $old ) : '' ),
            'old_max' => ( $old ? max( $old ) : '' ),
            'values'  => $plan['values'],
        );
    }

    public static function family_price_save( array $data ) : array {
        Store::require_cap( 'manage_woocommerce' );
        if ( empty( $data['confirmed'] ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Review and confirm the option prices.' ));
        }
        return Store::once_request( 'product-family-price', $data, static function () use($data) {
            global $wpdb;
            $id = absint( $data['id'] ?? 0 );
            $children = self::family_children( array($id), true )[$id] ?? array();
            $ids = array_merge( array($id), $children );
            sort( $ids, SORT_NUMERIC );
            $in = implode( ',', array_map( 'intval', $ids ) );
            if ( !$in ) {
                throw new \RuntimeException(I18n::t( 'The options could not be locked. Try again.' ));
            }
            // Store::once has already read its idempotency row in this transaction. Plain post/meta reads could
            // therefore use its older REPEATABLE READ snapshot. Populate caches from fresh locking reads instead.
            $posts = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->posts} WHERE ID IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') ORDER BY ID FOR UPDATE', $ids ) );
            $meta = $wpdb->get_results( $wpdb->prepare( "SELECT post_id,meta_key,meta_value FROM {$wpdb->postmeta} WHERE post_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') ORDER BY meta_id FOR UPDATE', $ids ), ARRAY_A );
            if ( $wpdb->last_error !== '' || count( $posts ) !== count( $ids ) ) {
                throw new \RuntimeException(I18n::t( 'The options could not be locked. Try again.' ));
            }
            $cache = array_fill_keys( $ids, array() );
            foreach ( $meta as $m ) {
                $cache[(int) $m['post_id']][$m['meta_key']][] = $m['meta_value'];
            }
            foreach ( $ids as $item ) {
                clean_post_cache( $item );
                wp_cache_set( $item, $cache[$item], 'post_meta' );
            }
            foreach ( $posts as $post ) {
                wp_cache_set( (int) $post->ID, new \WP_Post($post), 'posts' );
            }
            $plan = self::family_price_plan( $data, true );
            if ( !hash_equals( $plan['token'], Store::text( $data, 'preview_token' ) ) || $children !== array_column( $plan['rows'], 'id' ) ) {
                throw new \RuntimeException(I18n::t( 'The options changed after the preview. Refresh the list and review them.' ));
            }
            // Every option at 0 is free only when confirmed, unless each of them is free already (R2-2).
            if ( array_key_exists( 'regular_price', $plan['values'] ) ) {
                $paid = array_filter( $plan['rows'], static fn( $row ) => $row['regular_price'] === '' || (float) $row['regular_price'] > 0 );
                if ( $paid ) {
                    self::zero_price( (float) $plan['values']['regular_price'], $data );
                }
            }
            foreach ( $plan['rows'] as $row ) {
                $p = wc_get_product( $row['id'] );
                $v = $plan['values'];
                if ( array_key_exists( 'regular_price', $v ) ) {
                    $p->set_regular_price( $v['regular_price'] );
                }
                if ( array_key_exists( 'sale_price', $v ) ) {
                    $p->set_sale_price( $v['sale_price'] );
                }
                $p->save();
            }
            // Every planned price must be on the options before success is reported (0.62.10 review): a write that failed quietly is not a saved price.
            // The till price is checked too (paid build).
            $keys = array(
                'regular_price' => '_regular_price',
                'sale_price'    => '_sale_price',
            );
            $price_keys = array_values( $keys );
            $option_ids = array_values( array_map( 'intval', $children ) );
            $stored = $wpdb->get_results( $wpdb->prepare( "SELECT post_id,meta_key,meta_value FROM {$wpdb->postmeta} WHERE post_id IN (" . implode( ',', array_fill( 0, count( $option_ids ), '%d' ) ) . ') AND meta_key IN (' . implode( ',', array_fill( 0, count( $price_keys ), '%s' ) ) . ') FOR UPDATE', array_merge( $option_ids, $price_keys ) ), ARRAY_A );
            if ( $wpdb->last_error !== '' ) {
                throw new \RuntimeException(I18n::t( 'The option prices could not be saved completely. Refresh the list and check the prices.' ));
            }
            $now = array();
            foreach ( $stored as $m ) {
                $now[(int) $m['post_id']][$m['meta_key']] = (string) $m['meta_value'];
            }
            foreach ( $children as $child ) {
                foreach ( $plan['values'] as $key => $value ) {
                    $have = $now[(int) $child][$keys[$key]] ?? '';
                    if ( ( $value === '' ? $have !== '' : $have === '' || abs( (float) $have - (float) $value ) > 1.0E-6 ) ) {
                        throw new \RuntimeException(I18n::t( 'The option prices could not be saved completely. Refresh the list and check the prices.' ));
                    }
                }
            }
            \WC_Product_Variable::sync( $id );
            wc_delete_product_transients( $id );
            clean_post_cache( $id );
            Store::audit(
                'product',
                $id,
                'family_prices_updated',
                array(
                    'variations' => $children,
                    'values'     => $plan['values'],
                )
            );
            return array(
                'updated' => $plan['count'],
                'id'      => $id,
            );
        } );
    }

    /** The health filter names the product list accepts (saved product views are checked against them). */
    public static function health_filters() : array {
        return array(
            'out_of_stock',
            'low_stock',
            'missing_price',
            'missing_sku',
            'missing_image',
            'missing_category',
            'missing_barcode'
        );
    }

    /**
     * Store-wide counts of the data-quality conditions for sellable lines (simple products and options). An option without its
     * own photo or category uses its parent's; the store's default "Uncategorized" category does not count as a category. The
     * conditions are the switch arms of one literal statement (the same ones the product-list filters use), chosen by the key.
     */
    public static function health() : array {
        Store::require_cap( 'manage_woocommerce' );
        global $wpdb;
        [$threshold, $default] = self::health_values();
        $count = static function ( string $key ) use($wpdb, $threshold, $default) : int {
            return (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->posts} p LEFT JOIN {$wpdb->posts} parent ON parent.ID=p.post_parent WHERE p.post_status='publish' AND (p.post_type='product' OR (p.post_type='product_variation' AND parent.post_status='publish')) AND NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} tr JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id=tr.term_taxonomy_id JOIN {$wpdb->terms} t ON t.term_id=tt.term_id WHERE tr.object_id=p.ID AND tt.taxonomy='product_type' AND t.slug<>'simple')" . " AND (%s<>'out_of_stock' OR EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_stock_status' AND m.meta_value='outofstock')) AND (%s<>'low_stock' OR (EXISTS (SELECT 1 FROM {$wpdb->postmeta} ms WHERE ms.post_id=p.ID AND ms.meta_key='_manage_stock' AND ms.meta_value='yes') AND EXISTS (SELECT 1 FROM {$wpdb->postmeta} st WHERE st.post_id=p.ID AND st.meta_key='_stock' AND CAST(st.meta_value AS DECIMAL(14,3))>0 AND CAST(st.meta_value AS DECIMAL(14,3))<=COALESCE((SELECT CAST(NULLIF(la.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->postmeta} la WHERE la.post_id=st.post_id AND la.meta_key='_low_stock_amount' LIMIT 1),(SELECT CAST(NULLIF(lp.meta_value,'') AS DECIMAL(14,3)) FROM {$wpdb->posts} lv JOIN {$wpdb->postmeta} lp ON lp.post_id=lv.post_parent AND lp.meta_key='_low_stock_amount' WHERE lv.ID=st.post_id AND lv.post_type='product_variation' LIMIT 1),%d)))) AND (%s<>'missing_price' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_regular_price' AND m.meta_value<>'')) AND (%s<>'missing_sku' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_sku' AND m.meta_value<>'')) AND (%s<>'missing_image' OR (NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0')) AND (p.post_parent=0 OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.post_parent AND m.meta_key='_thumbnail_id' AND m.meta_value NOT IN ('','0'))))) AND (%s<>'missing_category' OR NOT EXISTS (SELECT 1 FROM {$wpdb->term_relationships} cr JOIN {$wpdb->term_taxonomy} ct ON ct.term_taxonomy_id=cr.term_taxonomy_id WHERE cr.object_id=IF(p.post_type='product_variation', p.post_parent, p.ID) AND ct.taxonomy='product_cat' AND ct.term_id<>%d)) AND (%s<>'missing_barcode' OR NOT EXISTS (SELECT 1 FROM {$wpdb->postmeta} m WHERE m.post_id=p.ID AND m.meta_key IN ('_global_unique_id','_barcode') AND m.meta_value<>''))",
                $key,
                $key,
                $threshold,
                $key,
                $key,
                $key,
                $key,
                $default,
                $key
            ) );
        };
        $counts = array(
            'all' => $count( 'all' ),
        );
        foreach ( self::health_filters() as $key ) {
            $counts[$key] = $count( $key );
        }
        return $counts;
    }

    /** An explicit, idempotent publication action; prices, inventory and option enabled states stay independent. */
    public static function publication( array $data ) : array {
        Store::require_cap( 'manage_woocommerce' );
        $id = absint( $data['id'] ?? 0 );
        if ( !current_user_can( 'edit_post', $id ) || !current_user_can( 'publish_products' ) ) {
            throw new \RuntimeException(I18n::t( 'You do not have permission to change product publication.' ));
        }
        $status = Store::text( $data, 'status' );
        if ( !in_array( $status, array('publish', 'draft'), true ) ) {
            throw new \InvalidArgumentException(I18n::t( 'Choose a valid product publication status.' ));
        }
        if ( $status === 'publish' && Store::text( $data, 'confirmed' ) !== '1' ) {
            throw new \InvalidArgumentException(I18n::t( 'Review and confirm publishing this product now.' ));
        }
        return Store::once_request( 'product-publication-' . $id . '-' . $status, $data, static function () use($id, $status, $data) {
            global $wpdb;
            // once_request opened a transaction snapshot. Locking reads provide the current post and metadata.
            $post = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE ID=%d FOR UPDATE', $wpdb->posts, $id ) );
            $meta = $wpdb->get_results( $wpdb->prepare( 'SELECT meta_key,meta_value FROM %i WHERE post_id=%d ORDER BY meta_id FOR UPDATE', $wpdb->postmeta, $id ), ARRAY_A );
            if ( !$post || $post->post_type !== 'product' || $wpdb->last_error !== '' ) {
                throw new \InvalidArgumentException(I18n::t( 'Product not found.' ));
            }
            clean_post_cache( $id );
            $cache = array();
            foreach ( $meta as $row ) {
                $cache[$row['meta_key']][] = $row['meta_value'];
            }
            wp_cache_set( $id, new \WP_Post($post), 'posts' );
            wp_cache_set( $id, $cache, 'post_meta' );
            $p = wc_get_product( $id );
            if ( !$p || !in_array( $p->get_status(), array(
                'publish',
                'draft',
                'pending',
                'private',
                'future'
            ), true ) ) {
                throw new \InvalidArgumentException(I18n::t( 'Choose a valid product publication status.' ));
            }
            if ( !hash_equals( self::publication_fields( $p )['publication_version'], Store::text( $data, 'publication_version' ) ) ) {
                throw new \RuntimeException(I18n::t( 'The product changed in another screen. Refresh the list and review the current information.' ));
            }
            $before = $p->get_status();
            // A private, pending or scheduled product becomes a draft too (Denetim R2-4: private to draft was refused with "refresh
            // the list", which was not the reason); one already in the asked state says so.
            if ( $before === $status ) {
                throw new \InvalidArgumentException(( $status === 'draft' ? I18n::t( 'This product is already a draft. Refresh the list to see its current state.' ) : I18n::t( 'This product is already published. Refresh the list to see its current state.' ) ));
            }
            // Publishing a future product is an explicit "publish now": clear its scheduled future date.
            if ( $status === 'publish' && $before === 'future' ) {
                $p->set_date_created( current_time( 'timestamp', true ) );
            }
            $p->set_status( $status );
            $p->update_meta_data( '_storesuit_publication_revision', wp_generate_uuid4() );
            $p->save();
            $stored = $wpdb->get_var( $wpdb->prepare( 'SELECT post_status FROM %i WHERE ID=%d FOR UPDATE', $wpdb->posts, $id ) );
            if ( $stored !== $status ) {
                throw new \RuntimeException(I18n::t( 'Product publication could not be saved. Refresh and review its status.' ));
            }
            wc_delete_product_transients( $id );
            clean_post_cache( $id );
            Store::audit(
                'product',
                $id,
                'publication_changed',
                array(
                    'before' => $before,
                    'after'  => $status,
                )
            );
            return array(
                'id'     => $id,
                'status' => $status,
            );
        } );
    }

    public static function save( array $data ) : array {
        Store::require_cap( 'manage_woocommerce' );
        $id = absint( $data['id'] ?? 0 );
        if ( !current_user_can( 'edit_post', $id ) ) {
            throw new \RuntimeException(I18n::t( 'You do not have permission to edit this product.' ));
        }
        return Store::once_request( 'product-edit', $data, static function () use($id, $data) {
            Store::lock_products( array($id) );
            $p = wc_get_product( $id );
            if ( !$p ) {
                throw new \InvalidArgumentException(I18n::t( 'Product not found.' ));
            }
            $before = self::editable( $p );
            // A variable product's price is computed from its options, and its cost means nothing: these are edited per option.
            if ( $p->is_type( 'variable' ) && array_intersect( array(
                'regular_price',
                'sale_price',
                'pos_override',
                'cost'
            ), array_keys( $data ) ) ) {
                throw new \InvalidArgumentException(I18n::t( 'Edit option prices in the option matrix.' ));
            }
            if ( !hash_equals( $before['version'], Store::text( $data, 'version' ) ) ) {
                throw new \RuntimeException(I18n::t( 'The product changed in another screen. Refresh the list and review the current information.' ));
            }
            // A stock change is checked before anything is written (permission, own stock, the quantity shown), applied after the save.
            $stock_delta = null;
            if ( array_key_exists( 'regular_price', $data ) ) {
                $regular = Store::amount( $data['regular_price'] );
                self::zero_price( $regular, $data, (string) $before['regular_price'] );
                $p->set_regular_price( $regular );
            }
            if ( array_key_exists( 'sale_price', $data ) ) {
                $sale = Store::text( $data, 'sale_price' );
                $p->set_sale_price( ( $sale === '' ? '' : Store::amount( $sale ) ) );
            }
            if ( $p->get_sale_price( 'edit' ) !== '' && (float) $p->get_sale_price( 'edit' ) >= (float) $p->get_regular_price( 'edit' ) ) {
                throw new \InvalidArgumentException(I18n::t( 'The sale price must be lower than the regular price. Leave it blank when there is no sale.' ));
            }
            if ( array_key_exists( 'name', $data ) ) {
                $name = Store::text( $data, 'name' );
                if ( $name === '' ) {
                    throw new \InvalidArgumentException(I18n::t( 'The product name cannot be empty.' ));
                }
                $p->set_name( $name );
            }
            if ( array_key_exists( 'sku', $data ) ) {
                $p->set_sku( Store::text( $data, 'sku' ) );
            }
            // Only a barcode that changed is written (REP-6): an unrelated save never stores anything in _barcode.
            if ( array_key_exists( 'barcode', $data ) && Store::text( $data, 'barcode' ) !== (string) $before['barcode'] ) {
                $code = Store::text( $data, 'barcode' );
                $p->update_meta_data( '_barcode', $code );
                if ( method_exists( $p, 'set_global_unique_id' ) && $p->get_global_unique_id( 'edit' ) !== '' && $p->get_global_unique_id( 'edit' ) !== $code ) {
                    throw new \InvalidArgumentException(I18n::t( 'The product also has a GTIN. Change the GTIN from the advanced product screen to avoid conflicting barcodes.' ));
                }
                // A valid GTIN also becomes WooCommerce's GTIN when that is empty, as on product creation (0.62.9, Woo parity).
                $gtin = ProductEntry::as_gtin( $code );
                // Only a barcode that changed: the form sends the SKU as the barcode when there is none (0.62.9 review).
                if ( $gtin !== '' && $code !== (string) $before['barcode'] && method_exists( $p, 'set_global_unique_id' ) && $p->get_global_unique_id( 'edit' ) === '' ) {
                    if ( function_exists( 'wc_get_product_id_by_global_unique_id' ) ) {
                        $other = (int) wc_get_product_id_by_global_unique_id( $gtin );
                        if ( $other && $other !== $id ) {
                            throw new \InvalidArgumentException(I18n::t( 'This GTIN is already used by another product.' ));
                        }
                    }
                    $p->set_global_unique_id( $gtin );
                }
            }
            $p->save();
            wc_delete_product_transients( $id );
            if ( $p->get_parent_id() ) {
                \WC_Product_Variable::sync( $p->get_parent_id() );
                wc_delete_product_transients( $p->get_parent_id() );
            }
            clean_post_cache( $id );
            $after = self::editable( wc_get_product( $id ) );
            Store::audit(
                'product',
                $id,
                'quick_edited',
                array(
                    'before' => $before,
                    'after'  => $after,
                )
            );
            return $after;
        } );
    }

    public static function create( array $data ) : array {
        Store::require_cap( 'manage_woocommerce' );
        Store::require_cap( 'edit_products' );
        Store::require_cap( 'publish_products' );
        return Store::once_request( 'product', $data, static function () use($data) {
            $name = Store::text( $data, 'name' );
            if ( $name === '' ) {
                throw new \InvalidArgumentException(I18n::t( 'A product name is required.' ));
            }
            $purchase_id = 0;
            $count_id = 0;
            $supplier = 0;
            $stock_action = 'none';
            $qty = 0;
            $opening = ProductEntry::opening( $data );
            $bill = 0.0;
            $entry_bill = array();
            // Published, or a draft when the person saves it as one (Denetim R2-1: quick add always published, photo or not).
            $p = new \WC_Product_Simple();
            $p->set_name( $name );
            $p->set_status( ( Store::text( $data, 'status' ) === 'draft' ? 'draft' : 'publish' ) );
            $p->set_sku( Store::text( $data, 'sku' ) );
            $regular = Store::amount( $data['price'] ?? 0 );
            self::zero_price( $regular, $data );
            $p->set_regular_price( $regular );
            $p->set_manage_stock( true );
            $p->set_stock_quantity( $opening );
            $p->update_meta_data( '_barcode', Store::text( $data, 'barcode' ) );
            ProductEntry::fields( $p, $data );
            $p->save();
            ProductEntry::log( $p, $opening );
            $category = absint( $data['category'] ?? 0 );
            if ( $category ) {
                if ( !term_exists( $category, 'product_cat' ) ) {
                    throw new \InvalidArgumentException(I18n::t( 'Category not found.' ));
                }
                $p->set_category_ids( array($category) );
                $p->save();
            }
            clean_post_cache( $p->get_id() );
            Store::audit(
                'product',
                $p->get_id(),
                'created',
                array(
                    'stock'       => wc_get_product( $p->get_id() )->get_stock_quantity(),
                    'purchase_id' => $purchase_id,
                    'count_id'    => $count_id,
                )
            );
            return array_merge( self::card( $p->get_id() ), array(
                'purchase_id' => $purchase_id,
                'count_id'    => $count_id,
            ), $entry_bill );
        } );
    }

}