<?php
/**
 * Quick stock: which products and variations to list, and what each row shows.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\QuickStock;

use SmartWP\AdminPowerups\Support\Paging;
use SmartWP\AdminPowerups\Support\Str;
use WP_Post;

defined( 'ABSPATH' ) || exit;

/**
 * Lists products and variations with prepared SQL (WC_Product_Query can't sort by SKU or stock,
 * express low stock, or list products and variations together under their parent's category).
 *
 * The lookup table `wc_product_meta_lookup` is used only to filter and sort. What a row shows
 * comes from post meta, read for the whole page in one query, so it is always current even while
 * WooCommerce rebuilds the lookup table.
 *
 * @since 1.0.0
 */
class StockQuery {

	/**
	 * Sort columns.
	 */
	const SORTS = array( 'name', 'sku', 'stock' );

	/**
	 * Stock filters.
	 */
	const STOCK_FILTERS = array( 'instock', 'lowstock', 'outofstock', 'onbackorder', 'untracked' );

	/**
	 * Post meta a row reads.
	 */
	const META_KEYS = array( '_sku', '_stock', '_stock_status', '_manage_stock', '_backorders', '_low_stock_amount' );

	/**
	 * Most rows one page may ask for.
	 */
	const MAX_ROWS = 200;

	/**
	 * Product type slug => term_taxonomy_id (null = not read yet).
	 *
	 * @var array<string, int>|null
	 */
	private $type_tt = null;

	/**
	 * Product categories, by name (null = not read yet).
	 *
	 * @var array<int, array{id: int, name: string, parent: int, tt: int}>|null
	 */
	private $cats = null;

	/**
	 * Product types that at least one product has, by slug.
	 *
	 * @var array<string, bool>
	 */
	private $type_used = array();

	/**
	 * Whether stock amounts may be fractional (memoized per request).
	 *
	 * @var bool|null
	 */
	private static $fractional = null;

	/**
	 * Parent ID of each row the last ids() call returned (0 for products).
	 *
	 * @var array<int, int>
	 */
	private $parents = array();

	/**
	 * Post statuses a product (or a variation's parent) may have to be listed and edited.
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public static function post_statuses() {
		/**
		 * Filter the post statuses of products Quick stock lists and edits. Variations are listed
		 * when their own status and their parent's are in the list (a disabled variation is
		 * "private").
		 *
		 * @since 1.0.0
		 * @param string[] $statuses Default: publish, private, draft, pending, future.
		 */
		$statuses = (array) apply_filters( 'apwc_quick_stock_post_statuses', array( 'publish', 'private', 'draft', 'pending', 'future' ) );
		$statuses = array_values( array_unique( array_filter( array_map( 'sanitize_key', array_filter( $statuses, 'is_string' ) ) ) ) );
		// Never trash or auto-drafts, whatever a filter says.
		$statuses = array_values( array_diff( $statuses, array( 'trash', 'auto-draft', 'inherit' ) ) );
		return $statuses ? $statuses : array( 'publish' );
	}

	/**
	 * Product types left out (their stock isn't theirs to manage).
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public static function excluded_types() {
		/**
		 * Filter the product types Quick stock leaves out.
		 *
		 * @since 1.0.0
		 * @param string[] $types Default: grouped, external.
		 */
		$types = (array) apply_filters( 'apwc_quick_stock_excluded_types', array( 'grouped', 'external' ) );
		return array_values( array_unique( array_filter( array_map( 'sanitize_key', array_filter( $types, 'is_string' ) ) ) ) );
	}

	/**
	 * Stock at or below which WooCommerce calls a product out of stock.
	 *
	 * @since 1.0.0
	 * @return int
	 */
	public static function no_stock_amount() {
		return (int) get_option( 'woocommerce_notify_no_stock_amount', 0 );
	}

	/**
	 * Store-wide low stock threshold.
	 *
	 * @since 1.0.0
	 * @return int
	 */
	public static function low_stock_amount() {
		return (int) get_option( 'woocommerce_notify_low_stock_amount', 2 );
	}

	/**
	 * Filters with defaults; unknown values become their default.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $filters s, stock, type, cat, parent, orderby, order, paged, per_page.
	 * @return array{s: string, stock: string, type: string, cat: int, parent: int, orderby: string, order: string, paged: int, per_page: int}
	 */
	public static function normalize( array $filters ) {
		$int = static fn( $key ) => isset( $filters[ $key ] ) && is_scalar( $filters[ $key ] ) ? max( 0, (int) $filters[ $key ] ) : 0;
		$str = static fn( $key ) => Str::scalar( $filters[ $key ] ?? '' );
		$per = $int( 'per_page' );
		$per = $per >= 1 ? min( self::MAX_ROWS, $per ) : 50;
		return array(
			's'        => trim( $str( 's' ) ),
			'stock'    => in_array( $str( 'stock' ), self::STOCK_FILTERS, true ) ? $str( 'stock' ) : '',
			'type'     => sanitize_key( $str( 'type' ) ),
			'cat'      => $int( 'cat' ),
			'parent'   => $int( 'parent' ),
			'orderby'  => in_array( $str( 'orderby' ), self::SORTS, true ) ? $str( 'orderby' ) : 'name',
			'order'    => 'desc' === strtolower( $str( 'order' ) ) ? 'desc' : 'asc',
			'paged'    => Paging::page( $filters['paged'] ?? 1, $per ),
			'per_page' => $per,
		);
	}

	/**
	 * Whether this store allows fractional stock (a `woocommerce_stock_amount` filter keeps 0.5).
	 *
	 * @since 1.0.0
	 * @return bool
	 */
	public static function fractional_stock() {
		if ( null === self::$fractional ) {
			self::$fractional = 0.5 === wc_stock_amount( 0.5 );
		}
		return self::$fractional;
	}

	/**
	 * Forget memoized store settings (tests change filters between requests).
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function reset() {
		self::$fractional = null;
	}

	/**
	 * Product type term_taxonomy_ids by slug.
	 *
	 * @since 1.0.0
	 * @return array<string, int>
	 */
	public function type_terms() {
		$this->read_terms();
		return (array) $this->type_tt;
	}

	/**
	 * Every product category (ID, name, parent, term_taxonomy_id), sorted by name.
	 *
	 * @since 1.0.0
	 * @return array<int, array{id: int, name: string, parent: int, tt: int}>
	 */
	public function categories() {
		$this->read_terms();
		return (array) $this->cats;
	}

	/**
	 * Product types and categories in one narrow query per instance (get_terms() would take two
	 * queries per taxonomy and build every term object).
	 *
	 * @since 1.0.0
	 * @return void
	 */
	private function read_terms() {
		global $wpdb;
		if ( null !== $this->type_tt ) {
			return;
		}
		$this->type_tt = array();
		$this->cats    = array();
		// `used`: EXISTS on the relationships (tt.count can be stale), only for the few type terms.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- once per instance, memoized.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT t.term_id, t.name, t.slug, tt.taxonomy, tt.parent, tt.term_taxonomy_id, IF( tt.taxonomy = 'product_type', EXISTS ( SELECT 1 FROM %i r WHERE r.term_taxonomy_id = tt.term_taxonomy_id ), 1 ) AS used FROM %i t INNER JOIN %i tt ON tt.term_id = t.term_id WHERE tt.taxonomy IN ( 'product_type', 'product_cat' ) ORDER BY t.name, t.term_id", $wpdb->term_relationships, $wpdb->terms, $wpdb->term_taxonomy ) );
		foreach ( $rows as $row ) {
			if ( 'product_type' === $row->taxonomy ) {
				$this->type_tt[ (string) $row->slug ]   = (int) $row->term_taxonomy_id;
				$this->type_used[ (string) $row->slug ] = (bool) (int) $row->used;
			} else {
				$this->cats[] = array(
					'id'     => (int) $row->term_id,
					'name'   => (string) $row->name,
					'parent' => (int) $row->parent,
					'tt'     => (int) $row->term_taxonomy_id,
				);
			}
		}
	}

	/**
	 * Number of rows the filters match.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $filters Filters.
	 * @return int
	 */
	public function count( array $filters ) {
		global $wpdb;
		$c   = $this->clauses( self::normalize( $filters ) );
		$sql = $this->prep( 'SELECT COUNT(*) FROM %i p ', array( $wpdb->posts ) ) . $c['join'] . ' WHERE ' . $c['where'];
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- every fragment was prepared by prep() (the apwc_quick_stock_query filter must return prepared SQL); one query built from fragments, so no single prepare().
		return (int) $wpdb->get_var( $sql ); // nosemgrep: apwc-wpdb-unprepared-variable -- fragments prepared in prep().
	}

	/**
	 * Row IDs for one page, in order.
	 *
	 * @since 1.0.0
	 * @param array<string, mixed> $filters Filters.
	 * @return int[]
	 */
	public function ids( array $filters ) {
		global $wpdb;
		$f     = self::normalize( $filters );
		$c     = $this->clauses( $f );
		$dir   = 'desc' === $f['order'] ? 'DESC' : 'ASC';
		$order = array(
			'name'  => "COALESCE( par.post_title, p.post_title ) {$dir}, COALESCE( par.ID, p.ID ) {$dir}, p.post_type = 'product_variation', p.menu_order, p.ID",
			'sku'   => "( l.sku = '' OR l.sku IS NULL ), l.sku {$dir}, p.ID",
			'stock' => "l.stock_quantity IS NULL, l.stock_quantity {$dir}, p.ID",
		);
		$sql   = $this->prep( 'SELECT p.ID, p.post_parent FROM %i p ', array( $wpdb->posts ) ) . $c['join']
			. ' WHERE ' . $c['where']
			. ' ORDER BY ' . $order[ $f['orderby'] ]
			. $this->prep( ' LIMIT %d OFFSET %d', array( $f['per_page'], ( $f['paged'] - 1 ) * $f['per_page'] ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- every fragment was prepared by prep() (the apwc_quick_stock_query filter must return prepared SQL); ORDER BY comes from a whitelist, LIMIT/OFFSET via %d.
		$rows = (array) $wpdb->get_results( $sql ); // nosemgrep: apwc-wpdb-unprepared-variable -- fragments prepared in prep().
		$ids  = array();
		foreach ( $rows as $row ) {
			$ids[]                           = (int) $row->ID;
			$this->parents[ (int) $row->ID ] = (int) $row->post_parent;
		}
		return $ids;
	}

	/**
	 * Prepared JOIN and WHERE for the filters.
	 *
	 * @since 1.0.0
	 * @param array{s: string, stock: string, type: string, cat: int, parent: int, orderby: string, order: string, paged: int, per_page: int} $f Normalized filters.
	 * @return array{join: string, where: string}
	 */
	private function clauses( array $f ) {
		global $wpdb;
		$lookup   = $wpdb->prefix . 'wc_product_meta_lookup';
		$statuses = self::post_statuses();
		$in_s     = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
		$types    = $this->type_terms();

		$join  = array(
			$this->prep( "LEFT JOIN %i par ON par.ID = p.post_parent AND p.post_type = 'product_variation'", array( $wpdb->posts ) ),
			$this->prep( 'LEFT JOIN %i l ON l.product_id = p.ID', array( $lookup ) ),
		);
		$where = array(
			"p.post_type IN ( 'product', 'product_variation' )",
			$this->prep( "p.post_status IN ( {$in_s} )", $statuses ),
			$this->prep( "( p.post_type = 'product' OR ( par.post_type = 'product' AND par.post_status IN ( {$in_s} ) ) )", $statuses ),
		);

		// One NOT IN per excluded type (each uses the term_taxonomy_id index); a type no product has is skipped.
		foreach ( self::excluded_types() as $excluded ) {
			if ( isset( $types[ $excluded ] ) && ! empty( $this->type_used[ $excluded ] ) ) {
				$where[] = $this->prep( "IF( p.post_type = 'product_variation', p.post_parent, p.ID ) NOT IN ( SELECT object_id FROM %i WHERE term_taxonomy_id = %d )", array( $wpdb->term_relationships, $types[ $excluded ] ) );
			}
		}

		// Others' private products need read_private_products; others' drafts, pending and scheduled
		// products need edit_others_products. A variation follows its parent (a private variation is
		// only "disabled").
		$owner_status = "IF( p.post_type = 'product_variation', par.post_status, p.post_status )";
		$owner_author = "IF( p.post_type = 'product_variation', par.post_author, p.post_author )";
		$user         = get_current_user_id();
		if ( ! current_user_can( 'read_private_products' ) ) {
			$where[] = $this->prep( "( {$owner_status} <> 'private' OR {$owner_author} = %d )", array( $user ) );
		}
		if ( ! current_user_can( 'edit_others_products' ) ) {
			$where[] = $this->prep( "( {$owner_status} IN ( 'publish', 'private' ) OR {$owner_author} = %d )", array( $user ) );
		}

		if ( '' !== $f['s'] ) {
			$like = '%' . $wpdb->esc_like( $f['s'] ) . '%';
			$cond = 'p.post_title LIKE %s OR par.post_title LIKE %s OR l.sku LIKE %s';
			$args = array( $like, $like, $like );
			if ( ctype_digit( $f['s'] ) ) {
				$cond  .= ' OR p.ID = %d';
				$args[] = (int) $f['s'];
			}
			$where[] = $this->prep( "( {$cond} )", $args );
		}

		switch ( $f['stock'] ) {
			case 'instock':
			case 'outofstock':
			case 'onbackorder':
				$where[] = $this->prep( 'l.stock_status = %s', array( $f['stock'] ) );
				break;

			case 'untracked':
				// Not tracked here: no stock of its own, not a variable parent (its variations are
				// listed), and not a variation whose parent tracks stock for it.
				$where[] = 'l.stock_quantity IS NULL';
				if ( isset( $types['variable'] ) ) {
					$where[] = $this->prep( 'p.ID NOT IN ( SELECT object_id FROM %i WHERE term_taxonomy_id = %d )', array( $wpdb->term_relationships, $types['variable'] ) );
				}
				$where[] = $this->prep( "( p.post_type = 'product' OR NOT EXISTS ( SELECT 1 FROM %i lp WHERE lp.product_id = p.post_parent AND lp.stock_quantity IS NOT NULL ) )", array( $lookup ) );
				break;

			case 'lowstock':
				// wc_get_low_stock_amount(): the product's own threshold, then its parent's, then the store's.
				// The first bound (the largest threshold anywhere, computed once) only cuts rows early;
				// the per-row COALESCE decides.
				$where[] = $this->prep(
					"l.stock_quantity IS NOT NULL AND l.stock_status = 'instock' AND l.stock_quantity > %d
					AND l.stock_quantity <= GREATEST( %d, ( SELECT COALESCE( MAX( CAST( mx.meta_value AS SIGNED ) ), 0 ) FROM %i mx WHERE mx.meta_key = '_low_stock_amount' ) )
					AND l.stock_quantity <= COALESCE(
						( SELECT CAST( NULLIF( ls.meta_value, '' ) AS SIGNED ) FROM %i ls WHERE ls.post_id = p.ID AND ls.meta_key = '_low_stock_amount' LIMIT 1 ),
						( SELECT CAST( NULLIF( pls.meta_value, '' ) AS SIGNED ) FROM %i pls WHERE p.post_parent > 0 AND pls.post_id = p.post_parent AND pls.meta_key = '_low_stock_amount' LIMIT 1 ),
						%d
					)",
					array( self::no_stock_amount(), self::low_stock_amount(), $wpdb->postmeta, $wpdb->postmeta, $wpdb->postmeta, self::low_stock_amount() )
				);
				break;
		}

		if ( '' !== $f['type'] ) {
			$tt      = $types[ $f['type'] ] ?? 0;
			$where[] = $tt ? $this->prep( "IF( p.post_type = 'product_variation', p.post_parent, p.ID ) IN ( SELECT object_id FROM %i WHERE term_taxonomy_id = %d )", array( $wpdb->term_relationships, $tt ) ) : '1=0';
		}

		if ( $f['cat'] ) {
			$tts = $this->category_tt_ids( $f['cat'] );
			if ( $tts ) {
				$in_d    = implode( ', ', array_fill( 0, count( $tts ), '%d' ) );
				$where[] = $this->prep( "IF( p.post_type = 'product_variation', p.post_parent, p.ID ) IN ( SELECT object_id FROM %i WHERE term_taxonomy_id IN ( {$in_d} ) )", array_merge( array( $wpdb->term_relationships ), $tts ) );
			} else {
				$where[] = '1=0'; // Not a product category (deleted, or a made-up ID).
			}
		}

		if ( $f['parent'] ) {
			$where[] = $this->prep( '( p.ID = %d OR p.post_parent = %d )', array( $f['parent'], $f['parent'] ) );
		}

		/**
		 * Filter the Quick stock list query. Every string must be prepared SQL (values through
		 * $wpdb->prepare()); they are joined as they are. The tables are aliased `p` (the product
		 * or variation), `par` (a variation's parent) and `l` (wc_product_meta_lookup).
		 *
		 * @since 1.0.0
		 * @param array{join: string[], where: string[]} $clauses Clauses.
		 * @param array<string, mixed>                   $filters Normalized filters.
		 */
		$clauses = (array) apply_filters(
			'apwc_quick_stock_query',
			array(
				'join'  => $join,
				'where' => $where,
			),
			$f
		);
		$join    = array_filter( (array) ( $clauses['join'] ?? array() ), 'is_string' );
		$where   = array_filter( (array) ( $clauses['where'] ?? array() ), 'is_string' );
		return array(
			'join'  => implode( ' ', $join ),
			'where' => $where ? implode( ' AND ', $where ) : '1=1',
		);
	}

	/**
	 * The term_taxonomy_ids of a product category and every category under it.
	 *
	 * @since 1.0.0
	 * @param int $term_id Category term ID.
	 * @return int[] Empty when it isn't a product category.
	 */
	private function category_tt_ids( $term_id ) {
		$tt       = array();
		$children = array();
		foreach ( $this->categories() as $cat ) {
			$tt[ $cat['id'] ]             = $cat['tt'];
			$children[ $cat['parent'] ][] = $cat['id'];
		}
		if ( ! isset( $tt[ $term_id ] ) ) {
			return array();
		}
		$out  = array();
		$todo = array( (int) $term_id );
		while ( $todo ) {
			$id = (int) array_shift( $todo );
			if ( isset( $out[ $id ] ) ) {
				continue; // A loop in a broken hierarchy.
			}
			$out[ $id ] = $tt[ $id ];
			foreach ( $children[ $id ] ?? array() as $child ) {
				$todo[] = $child;
			}
		}
		return array_values( $out );
	}

	/**
	 * Prepare a fragment (placeholders only in literal SQL; values in $args).
	 *
	 * @since 1.0.0
	 * @param string            $sql  SQL with %i/%s/%d placeholders.
	 * @param array<int, mixed> $args Values.
	 * @return string
	 */
	private function prep( $sql, array $args ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sql is a literal from this class with placeholders only.
		return $args ? (string) $wpdb->prepare( $sql, $args ) : $sql;
	}

	/**
	 * Display rows for these IDs: posts, parents, the six stock meta keys and product types are
	 * read once for the whole page, so the query count doesn't grow with the rows.
	 *
	 * @since 1.0.0
	 * @param int[] $ids Product or variation IDs.
	 * @return array<int, array<string, mixed>> Rows in the order given (missing posts skipped).
	 */
	public function rows( array $ids ) {
		global $wpdb;
		$ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) );
		if ( ! $ids ) {
			return array();
		}

		// Posts and parents in one query when ids() already knows the parents.
		$known = array_filter( array_intersect_key( $this->parents, array_flip( $ids ) ) );
		_prime_post_caches( array_values( array_unique( array_merge( $ids, $known ) ) ), false, false );
		$parents = array();
		foreach ( $ids as $id ) {
			$post = get_post( $id );
			if ( $post instanceof WP_Post && 'product_variation' === $post->post_type && $post->post_parent ) {
				$parents[ $id ] = (int) $post->post_parent;
			}
		}
		$parent_ids = array_values( array_unique( $parents ) );
		if ( $parent_ids ) {
			_prime_post_caches( $parent_ids, false, false ); // No query when already primed above.
		}

		$all    = array_values( array_unique( array_merge( $ids, $parent_ids ) ) );
		$meta   = $this->meta( $all );
		$owners = array_values( array_unique( array_map( static fn( $id ) => $parents[ $id ] ?? $id, $ids ) ) );
		$types  = $this->product_types( $owners );
		$counts = $this->variation_counts( array_keys( array_filter( $types, static fn( $t ) => 'variable' === $t ) ) );

		/**
		 * Runs once per page after Quick stock has read its rows, so add-on columns can load
		 * their data for all rows in one go.
		 *
		 * @since 1.0.0
		 * @param int[] $ids Product and variation IDs on the page.
		 */
		do_action( 'apwc_quick_stock_prime_rows', $ids );

		$no_stock = self::no_stock_amount();
		$global   = self::low_stock_amount();
		$options  = function_exists( 'wc_get_product_stock_status_options' ) ? (array) wc_get_product_stock_status_options() : array();
		$rows     = array();
		foreach ( $ids as $id ) {
			$post = get_post( $id );
			if ( ! $post instanceof WP_Post ) {
				continue;
			}
			$is_var    = isset( $parents[ $id ] );
			$parent_id = $is_var ? $parents[ $id ] : 0;
			$owner     = $is_var ? $parent_id : $id;
			$type      = $types[ $owner ] ?? 'simple';
			$m         = $meta[ $id ] ?? array();
			$pm        = $parent_id ? ( $meta[ $parent_id ] ?? array() ) : array();
			$manage    = 'yes' === ( $m['_manage_stock'] ?? '' );
			$by_parent = $is_var && ! $manage && 'yes' === ( $pm['_manage_stock'] ?? '' );

			if ( $is_var ) {
				$kind = $by_parent ? 'variation_parent_stock' : 'variation';
			} elseif ( 'variable' === $type ) {
				$kind = 'variable';
			} elseif ( 'simple' === $type ) {
				$kind = 'simple';
			} else {
				$kind = 'other';
			}

			$own_low = (string) ( $m['_low_stock_amount'] ?? '' );
			$par_low = (string) ( $pm['_low_stock_amount'] ?? '' );
			$low     = '' !== $own_low ? (int) $own_low : ( '' !== $par_low ? (int) $par_low : $global );
			$stock   = $manage ? wc_stock_amount( $m['_stock'] ?? 0 ) : null;
			$status  = (string) ( $m['_stock_status'] ?? 'instock' );
			$status  = '' !== $status ? $status : 'instock';
			$key     = $status;
			if ( null !== $stock && 'instock' === $status && $stock > $no_stock && $stock <= $low ) {
				$key = 'lowstock';
			}
			$parent = $is_var ? get_post( $parent_id ) : null;
			$name   = $parent instanceof WP_Post ? $parent->post_title : $post->post_title;
			$attrs  = '';
			if ( $is_var ) {
				$attrs = '' !== trim( (string) $post->post_excerpt ) ? (string) $post->post_excerpt : (string) $post->post_title;
				$attrs = $attrs !== $name ? $attrs : '';
			}

			$row = array(
				'id'            => $id,
				'parent_id'     => $parent_id,
				'kind'          => $kind,
				'type'          => $type,
				'name'          => $name,
				'attributes'    => $attrs,
				'sku'           => (string) ( $m['_sku'] ?? '' ),
				'manage'        => $manage,
				'stock'         => $stock,
				'parent_stock'  => $by_parent ? wc_stock_amount( $pm['_stock'] ?? 0 ) : null,
				'backorders'    => (string) ( $m['_backorders'] ?? 'no' ),
				'status'        => $status,
				'status_key'    => $key,
				'status_label'  => 'lowstock' === $key ? __( 'Low stock', 'admin-powerups-for-woocommerce' ) : (string) ( $options[ $status ] ?? $status ),
				'low_threshold' => $low,
				'post_status'   => $post->post_status,
				'parent_status' => $parent instanceof WP_Post ? $parent->post_status : '',
				'edit_url'      => admin_url( 'post.php?post=' . $owner . '&action=edit' ),
				'variations'    => 'variable' === $kind ? (int) ( $counts[ $id ] ?? 0 ) : 0,
			);

			/**
			 * Filter one Quick stock row before it's drawn.
			 *
			 * @since 1.0.0
			 * @param array<string, mixed> $row  Row (id, parent_id, kind, type, name, attributes, sku, manage,
			 *                                   stock, parent_stock, backorders, status, status_key, status_label,
			 *                                   low_threshold, post_status, parent_status, edit_url, variations).
			 * @param WP_Post              $post Product or variation post.
			 */
			$row = apply_filters( 'apwc_quick_stock_row', $row, $post );
			if ( is_array( $row ) ) {
				$rows[] = $row;
			}
		}
		return $rows;
	}

	/**
	 * Product type slug of each product, in one query on the product_type terms only.
	 *
	 * @since 1.0.0
	 * @param int[] $ids Product IDs.
	 * @return array<int, string> ID => type ('simple' when none is set).
	 */
	private function product_types( array $ids ) {
		global $wpdb;
		$by_tt = array_flip( $this->type_terms() );
		$out   = array_fill_keys( $ids, 'simple' );
		if ( ! $ids || ! $by_tt ) {
			return $out;
		}
		$in_d = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
		$in_t = implode( ', ', array_fill( 0, count( $by_tt ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $in_d / $in_t are one %d per value, all passed.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT object_id, term_taxonomy_id FROM %i WHERE object_id IN ( {$in_d} ) AND term_taxonomy_id IN ( {$in_t} )", array_merge( array( $wpdb->term_relationships ), $ids, array_keys( $by_tt ) ) ) );
		foreach ( $rows as $row ) {
			$out[ (int) $row->object_id ] = sanitize_key( (string) $by_tt[ (int) $row->term_taxonomy_id ] );
		}
		return $out;
	}

	/**
	 * The stock meta of these posts, in one query.
	 *
	 * @since 1.0.0
	 * @param int[] $ids Post IDs.
	 * @return array<int, array<string, string>> Post ID => meta key => value.
	 */
	private function meta( array $ids ) {
		global $wpdb;
		$in_d = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
		$in_s = implode( ', ', array_fill( 0, count( self::META_KEYS ), '%s' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $in_d / $in_s are one placeholder per value, all passed; one narrow read instead of every meta key of every row.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT post_id, meta_key, meta_value FROM %i WHERE post_id IN ( {$in_d} ) AND meta_key IN ( {$in_s} ) ORDER BY meta_id", array_merge( array( $wpdb->postmeta ), $ids, self::META_KEYS ) ) );
		$out  = array();
		foreach ( $rows as $row ) {
			$id = (int) $row->post_id;
			if ( ! isset( $out[ $id ][ $row->meta_key ] ) ) {
				$out[ $id ][ $row->meta_key ] = (string) $row->meta_value;
			}
		}
		return $out;
	}

	/**
	 * Listed variations per variable product, in one query.
	 *
	 * @since 1.0.0
	 * @param int[] $parent_ids Variable product IDs.
	 * @return array<int, int>
	 */
	private function variation_counts( array $parent_ids ) {
		global $wpdb;
		if ( ! $parent_ids ) {
			return array();
		}
		$statuses = self::post_statuses();
		$in_d     = implode( ', ', array_fill( 0, count( $parent_ids ), '%d' ) );
		$in_s     = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $in_d / $in_s are one placeholder per value, all passed.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT post_parent, COUNT(*) AS n FROM %i WHERE post_type = 'product_variation' AND post_parent IN ( {$in_d} ) AND post_status IN ( {$in_s} ) GROUP BY post_parent", array_merge( array( $wpdb->posts ), $parent_ids, $statuses ) ) );
		$out  = array();
		foreach ( $rows as $row ) {
			$out[ (int) $row->post_parent ] = (int) $row->n;
		}
		return $out;
	}
}