<?php

namespace WPPilot;

defined( 'ABSPATH' ) || exit;

/**
 * Every post of one type, a page at a time, as compact rows. Read-only.
 *
 * Live, 2026-10-15 (Phase 0): "Do my tour pages have good descriptions for
 * Google?" was answered from 5 of 46 tours ("not one has a meta description";
 * 5 do). "Write excerpts for all the tour pages" read 11 and never learned
 * there were 46. "How many products are out of stock?" (1,095 products) got
 * no count. "Which tours have no Kurdish version?" was answered by guessing
 * pairs from titles. Every read reachable without the Advanced module was a
 * page of 20, a search of 10 or one post, so a question about a whole set was
 * answered from a sample.
 *
 * This answers the set: one row per post of the type, in primary-key order,
 * with the facts set questions turn on and never a body —
 *
 *   id, title, slug, status, modified (date), excerpt and body length in
 *   characters, the stored meta description's length in the active SEO
 *   plugin's own field (null when that plugin's storage is not one this
 *   reads), whether a featured image is set, and, when Polylang or WPML
 *   translates the type, the post's language and its other language versions
 *   from the translation plugin's own records (never from titles); for
 *   WooCommerce products, SKU, stock status and price.
 *
 * Bounds: at most {@see self::MAX_PAGE} rows per request (keyset cursor
 * `after`, so a walk neither skips nor repeats a row), every statement
 * prepared, primary-key or index bounded and time-limited by the server
 * ({@see Site_Find::time_limited()}); meta values are read as their first
 * {@see self::META_CHARS} characters and their length only. The total for the
 * type and statuses is counted with the first page (later pages carry null),
 * so a caller knows whether its walk covered the set. Every statement's error
 * is checked; any failure answers 503, never a partial page.
 *
 * Live, 2026-10-17 (Phase 0 gs-out-of-stock, gs-duplicate-listings): on a shop
 * of 1,095 products answering ~6 s a request, the caller's walk was cut after
 * 1,000 rows, so every count it made from the rows covered 1,000 of 1,095 —
 * "91 of the 1,000 I could read" (about 170 in all) and "duplicates: can't
 * tell". A count over a set must not depend on walking every row: the first
 * page also carries `aggregates`, counted by the database over the WHOLE set
 * ({@see self::aggregates()}). A field whose statement failed, or that the
 * time for them did not reach, is absent — never zero.
 */
class Content_Set {

	/** Rows per page, at most. */
	public const MAX_PAGE = 500;
	/** Rows per page when not asked. */
	public const DEFAULT_PAGE = 200;
	/** Characters of a meta value read (SKU, stock status, price). */
	public const META_CHARS = 64;
	/** Seconds one statement may run, where the server enforces it. */
	public const QUERY_SECONDS = 5;
	/** Characters of a title returned. */
	public const TITLE_CHARS = 160;
	/** Seconds the first page spends on the set's aggregates in all; past it, the rest are left out. With one statement's own limit, the first page stays well inside the backend's request timeout (25 s). */
	public const AGGREGATE_SECONDS = 6;
	/** Duplicate groups given as examples, at most, and ids given for each. */
	public const DUPLICATE_EXAMPLES = 20;
	/** A meta description shorter than this many characters is counted as short. */
	public const SHORT_DESCRIPTION_CHARS = 70;
	/** Languages whose missing versions are counted, at most. */
	private const MAX_LANGUAGES = 30;
	/** A language code as the translation plugins store one. */
	private const LANGUAGE_CODE = '/^[A-Za-z]{2,3}(?:[-_][A-Za-z0-9]{2,8})?$/';
	/** Statuses an owner sees in wp-admin; `any` means all of them. */
	public const STATUSES = array( 'publish', 'draft', 'pending', 'private', 'future' );
	/** Types that are not content an owner manages as a set, or that hold private records. */
	private const SKIPPED_TYPES = array( 'revision', 'nav_menu_item', 'customize_changeset', 'oembed_cache', 'user_request', 'wp_global_styles', 'wp_font_family', 'wp_font_face', 'wp_navigation', 'attachment', 'shop_coupon', 'shop_order', 'shop_order_refund', 'shop_subscription', 'nf_sub', 'wpcf7_contact_form', 'wppilot_lead' );
	/** Words in a type's name that mark private records: orders, coupon codes, form entries, leads. */
	private const PRIVATE_WORDS = array( 'order', 'coupon', 'subscription', 'submission', 'entry', 'entries', 'lead', 'flamingo', 'payment', 'invoice', 'customer' );
	/** SEO plugins WPPilot does not write to, by their own description field. */
	private const OTHER_SEO_KEYS = array(
		'SEOPress'          => '_seopress_titles_desc',
		'The SEO Framework' => '_genesis_description',
		'SmartCrawl'        => '_wds_metadesc',
	);

	/** GET /content-set — register the route. */
	public static function register_routes( callable $permission ): void {
		register_rest_route(
			'wppilot/v1',
			'/content-set',
			array(
				'methods'             => 'GET',
				'callback'            => array( self::class, 'rest_content_set' ),
				'permission_callback' => $permission,
				'args'                => array(
					'type'   => array(
						'required'          => true,
						'validate_callback' => static fn( $value ): bool => is_string( $value ) && '' !== sanitize_key( $value ),
					),
					'status' => array(
						'validate_callback' => static fn( $value ): bool => is_string( $value ) && ( 'any' === $value || in_array( $value, self::STATUSES, true ) ),
					),
					'after'  => array(
						'validate_callback' => static fn( $value ): bool => is_numeric( $value ) && (int) $value >= 0,
					),
					'limit'  => array(
						'validate_callback' => static fn( $value ): bool => is_numeric( $value ) && (int) $value >= 1,
					),
				),
			)
		);
	}

	/**
	 * Whether a post type can be listed: content visitors can reach (public or
	 * publicly queryable; WooCommerce products), never WordPress's bookkeeping
	 * types and never a type that holds private records — orders, coupon
	 * codes, form submissions, leads — whose titles are themselves the secret
	 * (a coupon's title is its code). By name as well as by list, so a form
	 * plugin's `*_submission` or `*_entry` type is refused without being named.
	 */
	public static function listable( string $type ): bool {
		if ( in_array( $type, self::SKIPPED_TYPES, true ) ) {
			return false;
		}
		foreach ( self::PRIVATE_WORDS as $word ) {
			if ( false !== strpos( $type, $word ) ) {
				return false;
			}
		}
		$object = get_post_type_object( $type );
		if ( ! is_object( $object ) ) {
			return false;
		}
		return 'product' === $type || ! empty( $object->public ) || ! empty( $object->publicly_queryable );
	}

	/**
	 * GET /content-set?type=…&status=…&after=…&limit=…
	 *
	 * @param \WP_REST_Request $request The request.
	 */
	public static function rest_content_set( \WP_REST_Request $request ): \WP_REST_Response|\WP_Error {
		if ( ! current_user_can( 'manage_options' ) ) {
			return new \WP_Error( 'wppilot_content_set_forbidden', 'Listing a whole content type needs an administrator.', array( 'status' => 403 ) );
		}
		$type = sanitize_key( (string) $request->get_param( 'type' ) );
		if ( ! self::listable( $type ) ) {
			return new \WP_Error( 'wppilot_unknown_post_type', 'That content type does not exist on this site or is not listable.', array( 'status' => 404 ) );
		}
		$status   = (string) ( $request->get_param( 'status' ) ?? 'publish' );
		$statuses = 'any' === $status ? self::STATUSES : ( in_array( $status, self::STATUSES, true ) ? array( $status ) : array( 'publish' ) );
		$after    = max( 0, (int) ( $request->get_param( 'after' ) ?? 0 ) );
		$limit    = max( 1, min( self::MAX_PAGE, (int) ( $request->get_param( 'limit' ) ?? self::DEFAULT_PAGE ) ) );
		$result   = self::read( $type, $statuses, $after, $limit );
		if ( null === $result ) {
			return new \WP_Error( 'wppilot_content_set_unavailable', 'The database did not answer in time.', array( 'status' => 503 ) );
		}
		return new \WP_REST_Response( $result, 200 );
	}

	/**
	 * One page of the set, or null when the database did not answer.
	 *
	 * @param string   $type     A listable post type.
	 * @param string[] $statuses Statuses from {@see self::STATUSES}.
	 * @param int      $after    Rows with a larger id only.
	 * @param int      $limit    Rows wanted.
	 * @return array<string, mixed>|null
	 */
	public static function read( string $type, array $statuses, int $after, int $limit ): ?array {
		try {
			return self::read_page( $type, $statuses, $after, $limit );
		} catch ( \RuntimeException $e ) {
			// Any statement that did not answer: no page at all, never one whose
			// missing part would read as "none" (a translation, a description).
			return null;
		}
	}

	/**
	 * A statement's result, or an exception when the database did not answer.
	 * `$wpdb->get_results()` returns an empty array on an error or a timeout, so
	 * the error itself is what is checked, after every statement.
	 *
	 * @param mixed $result What the statement returned.
	 * @return mixed
	 * @throws \RuntimeException When the statement failed.
	 */
	private static function answered( $result ) {
		global $wpdb;
		if ( '' !== (string) $wpdb->last_error || null === $result ) {
			throw new \RuntimeException( 'statement failed' );
		}
		return $result;
	}

	/**
	 * One page; throws when any statement failed.
	 *
	 * @param string   $type     A listable post type.
	 * @param string[] $statuses Statuses.
	 * @param int      $after    Cursor.
	 * @param int      $limit    Rows wanted.
	 * @return array<string, mixed>
	 * @throws \RuntimeException When a statement failed.
	 */
	private static function read_page( string $type, array $statuses, int $after, int $limit ): array {
		global $wpdb;
		$in_status = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );

		// The set's size is counted once, with the first page; later pages carry null.
		$total = null;
		if ( 0 === $after ) {
			$wpdb->last_error = '';
			$total            = (int) self::answered( $wpdb->get_var( Site_Find::time_limited( $wpdb->prepare( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s AND post_status IN ({$in_status})", array_merge( array( $type ), $statuses ) ), self::QUERY_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- the IN list is placeholders only; every value is prepared.
		}
		$wpdb->last_error = '';
		$rows             = self::answered( $wpdb->get_results( Site_Find::time_limited( $wpdb->prepare( "SELECT ID, post_title, post_name, post_status, post_modified_gmt, CHAR_LENGTH(post_excerpt) AS excerpt_chars, CHAR_LENGTH(post_content) AS content_chars FROM {$wpdb->posts} WHERE post_type = %s AND post_status IN ({$in_status}) AND ID > %d ORDER BY ID ASC LIMIT %d", array_merge( array( $type ), $statuses, array( $after, $limit + 1 ) ) ), self::QUERY_SECONDS ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- the IN list is placeholders only; every value is prepared.
		$has_more = count( $rows ) > $limit;
		$rows     = array_slice( $rows, 0, $limit );
		$ids      = array_map( static fn( $row ) => (int) $row['ID'], $rows );

		$seo         = self::seo_source();
		$woocommerce = 'product' === $type && ( class_exists( '\WooCommerce' ) || defined( 'WC_VERSION' ) );
		$keys        = array( '_thumbnail_id' );
		if ( null !== $seo['key'] ) {
			$keys[] = $seo['key'];
		}
		if ( $woocommerce ) {
			array_push( $keys, '_sku', '_stock_status', '_price' );
		}
		$meta      = self::meta_of( $ids, $keys );
		$aioseo    = 'aioseo' === $seo['plugin'] ? self::aioseo_descriptions( $ids ) : null;
		$languages = self::languages_of( $type, $ids );

		$items = array();
		foreach ( $rows as $row ) {
			$id   = (int) $row['ID'];
			$item = array(
				'id'            => $id,
				'title'         => self::bounded( wp_strip_all_tags( (string) $row['post_title'] ), self::TITLE_CHARS ),
				'slug'          => (string) $row['post_name'],
				'status'        => (string) $row['post_status'],
				'modified'      => substr( (string) $row['post_modified_gmt'], 0, 10 ),
				'excerpt_chars' => (int) $row['excerpt_chars'],
				'content_chars' => (int) $row['content_chars'],
				'image'         => (int) ( $meta[ $id ]['_thumbnail_id']['v'] ?? 0 ) > 0,
			);
			if ( null !== $aioseo ) {
				$item['meta_description_chars'] = $aioseo[ $id ] ?? 0;
			} elseif ( null !== $seo['key'] ) {
				$item['meta_description_chars'] = (int) ( $meta[ $id ][ $seo['key'] ]['n'] ?? 0 );
			} else {
				$item['meta_description_chars'] = null;
			}
			if ( $woocommerce ) {
				$item['sku']          = (string) ( $meta[ $id ]['_sku']['v'] ?? '' );
				$item['stock_status'] = (string) ( $meta[ $id ]['_stock_status']['v'] ?? '' );
				$item['price']        = (string) ( $meta[ $id ]['_price']['v'] ?? '' );
			}
			if ( null !== $languages ) {
				$item['lang']         = $languages['of'][ $id ]['lang'] ?? null;
				$item['translations'] = $languages['of'][ $id ]['translations'] ?? array();
			}
			$items[] = $item;
		}

		$object = get_post_type_object( $type );
		$page   = array(
			'post_type'       => $type,
			'post_type_label' => is_object( $object ) && isset( $object->label ) ? wp_strip_all_tags( (string) $object->label ) : $type,
			'statuses'        => array_values( $statuses ),
			'total'           => $total,
			'after'           => $after,
			'page_size'       => $limit,
			'has_more'        => $has_more,
			'next_after'      => $has_more && ! empty( $ids ) ? end( $ids ) : null,
			'seo'             => array(
				'plugin' => $seo['plugin'],
				'field'  => null !== $aioseo ? 'aioseo_posts.description' : $seo['key'],
			),
			'translation'     => null === $languages ? null : array(
				'plugin'    => $languages['plugin'],
				'languages' => $languages['languages'],
				'default'   => $languages['default'],
			),
			'woocommerce'     => $woocommerce,
			'items'           => $items,
		);
		// Counted over the whole set, with the first page only (as the total is).
		if ( 0 === $after ) {
			$page['aggregates'] = self::aggregates( $type, $statuses );
		}
		return $page;
	}

	/**
	 * Counts over the WHOLE set, by the database: a few bounded aggregate
	 * statements over the same type and statuses as the walk, each with the
	 * meaning the rows have —
	 *
	 *   by_status, excerpt {with, without}, featured_image {with, without};
	 *   meta_description {with, without, shorter_than_70, field} from the
	 *   detected SEO source (All in One SEO's own table when it is there);
	 *   for WooCommerce products stock_status (by value; '' when not set),
	 *   missing_sku, missing_price (no non-empty price) and zero_price;
	 *   duplicate_titles and duplicate_skus {groups, items, examples} — exact
	 *   value (case and outer spaces folded), within one language when a
	 *   translation plugin translates the type, examples the largest groups;
	 *   posts_by_language ('' when none) and translation_groups {groups,
	 *   missing_a_version_in} from Polylang's or WPML's own records, a group
	 *   counted once and a version that is trashed or an auto-draft no version.
	 *
	 * Never throws. A statement that failed leaves its fields out — absent,
	 * never zero — and so do the ones the time for them did not reach.
	 *
	 * @param string        $type     A listable post type.
	 * @param string[]      $statuses Statuses from {@see self::STATUSES}.
	 * @param callable|null $clock    Seconds now (tests).
	 * @return array<string, mixed>
	 */
	public static function aggregates( string $type, array $statuses, ?callable $clock = null ): array {
		global $wpdb;
		$clock     = $clock ?? static fn(): float => microtime( true );
		$started   = $clock();
		$p         = $wpdb->posts;
		$m         = $wpdb->postmeta;
		$in_status = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
		$set       = "p.post_type = %s AND p.post_status IN ({$in_status})";
		$set_args  = array_merge( array( $type ), $statuses );
		$product   = 'product' === $type && ( class_exists( '\WooCommerce' ) || defined( 'WC_VERSION' ) );
		$steps     = array();

		// Statuses, excerpts and featured images: one pass over the set.
		$steps[] = static function () use ( $p, $m, $set, $set_args ): array {
			$rows      = self::aggregate_rows( "SELECT t.s AS s, COUNT(*) AS n, SUM(t.e) AS excerpt, SUM(t.i) AS image FROM (SELECT p.post_status AS s, CHAR_LENGTH(p.post_excerpt) > 0 AS e, EXISTS(SELECT 1 FROM {$m} m WHERE m.post_id = p.ID AND m.meta_key = '_thumbnail_id' AND CAST(m.meta_value AS UNSIGNED) > 0) AS i FROM {$p} p WHERE {$set}) t GROUP BY t.s", $set_args );
			$by_status = array();
			$n         = 0;
			$excerpt   = 0;
			$image     = 0;
			foreach ( $rows as $row ) {
				$by_status[ (string) $row['s'] ] = (int) $row['n'];
				$n       += (int) $row['n'];
				$excerpt += (int) $row['excerpt'];
				$image   += (int) $row['image'];
			}
			return array(
				'by_status'      => $by_status,
				'excerpt'        => array( 'with' => $excerpt, 'without' => $n - $excerpt ),
				'featured_image' => array( 'with' => $image, 'without' => $n - $image ),
			);
		};
		// Meta descriptions, from the field the rows read them from.
		$steps[] = static function () use ( $p, $m, $set, $set_args ): array {
			global $wpdb;
			$seo    = self::seo_source();
			$aioseo = $wpdb->prefix . 'aioseo_posts';
			if ( 'aioseo' === $seo['plugin'] && self::table_exists( $aioseo ) ) {
				$length = "COALESCE((SELECT MAX(CHAR_LENGTH(a.description)) FROM {$aioseo} a WHERE a.post_id = p.ID), 0)";
				$args   = array();
				$field  = 'aioseo_posts.description';
			} elseif ( null !== $seo['key'] ) {
				$length = "COALESCE((SELECT CHAR_LENGTH(m.meta_value) FROM {$m} m WHERE m.post_id = p.ID AND m.meta_key = %s AND TRIM(m.meta_value) <> '' ORDER BY m.meta_id ASC LIMIT 1), 0)";
				$args   = array( $seo['key'] );
				$field  = $seo['key'];
			} else {
				// Storage this does not read: unknown, as on the rows.
				return array();
			}
			$rows = self::aggregate_rows( "SELECT COUNT(*) AS n, SUM(t.d > 0) AS with_d, SUM(t.d > 0 AND t.d < %d) AS short_d FROM (SELECT {$length} AS d FROM {$p} p WHERE {$set}) t", array_merge( array( self::SHORT_DESCRIPTION_CHARS ), $args, $set_args ) );
			$row  = $rows[0] ?? array();
			$n    = (int) ( $row['n'] ?? 0 );
			$with = (int) ( $row['with_d'] ?? 0 );
			return array(
				'meta_description' => array(
					'with'            => $with,
					'without'         => $n - $with,
					'shorter_than_70' => (int) ( $row['short_d'] ?? 0 ),
					'field'           => $field,
				),
			);
		};
		if ( $product ) {
			// Stock status: each product's first non-empty value, as on the rows.
			$steps[] = static function () use ( $p, $m, $set, $set_args ): array {
				$rows  = self::aggregate_rows( "SELECT t.s AS s, COUNT(*) AS n FROM (SELECT COALESCE((SELECT LEFT(TRIM(m.meta_value), %d) FROM {$m} m WHERE m.post_id = p.ID AND m.meta_key = '_stock_status' AND TRIM(m.meta_value) <> '' ORDER BY m.meta_id ASC LIMIT 1), '') AS s FROM {$p} p WHERE {$set}) t GROUP BY t.s", array_merge( array( self::META_CHARS ), $set_args ) );
				$stock = array();
				foreach ( $rows as $row ) {
					$value           = self::bounded( wp_strip_all_tags( (string) $row['s'] ), self::META_CHARS );
					$stock[ $value ] = ( $stock[ $value ] ?? 0 ) + (int) $row['n'];
				}
				return array( 'stock_status' => $stock );
			};
			// SKU and price present, and a price of zero.
			$steps[] = static function () use ( $p, $m, $set, $set_args ): array {
				$rows = self::aggregate_rows( "SELECT COUNT(*) AS n, SUM(t.sku = 0) AS no_sku, SUM(t.price IS NULL) AS no_price, SUM(t.price IS NOT NULL AND CAST(t.price AS DECIMAL(20,4)) = 0) AS zero_price FROM (SELECT EXISTS(SELECT 1 FROM {$m} m WHERE m.post_id = p.ID AND m.meta_key = '_sku' AND TRIM(m.meta_value) <> '') AS sku, (SELECT TRIM(m2.meta_value) FROM {$m} m2 WHERE m2.post_id = p.ID AND m2.meta_key = '_price' AND TRIM(m2.meta_value) <> '' ORDER BY m2.meta_id ASC LIMIT 1) AS price FROM {$p} p WHERE {$set}) t", $set_args );
				$row  = $rows[0] ?? array();
				return array(
					'missing_sku'   => (int) ( $row['no_sku'] ?? 0 ),
					'missing_price' => (int) ( $row['no_price'] ?? 0 ),
					'zero_price'    => (int) ( $row['zero_price'] ?? 0 ),
				);
			};
		}

		// Duplicates are within one language, so the translation source comes
		// first; when it cannot be read, neither duplicates nor languages are counted.
		try {
			$languages = self::translation_source( $type );
			$known     = true;
		} catch ( \RuntimeException $e ) {
			$languages = null;
			$known     = false;
		}
		if ( $known ) {
			$join      = null === $languages ? '' : "LEFT JOIN ({$languages['members']}) l ON l.post_id = p.ID";
			$join_args = null === $languages ? array() : $languages['args'];
			$lang      = null === $languages ? "''" : "COALESCE(l.lang, '')";
			$by_lang   = null === $languages ? '' : "COALESCE(l.lang, ''), ";
			$steps[]   = static function () use ( $p, $set, $set_args, $join, $join_args, $by_lang ): array {
				$from = "FROM {$p} p {$join} WHERE {$set} AND TRIM(p.post_title) <> '' GROUP BY {$by_lang}LOWER(TRIM(p.post_title)) HAVING COUNT(*) > 1";
				return array( 'duplicate_titles' => self::duplicate_groups( "SELECT COUNT(*) AS c {$from}", "SELECT MIN(p.post_title) AS v, COUNT(*) AS c, SUBSTRING_INDEX(GROUP_CONCAT(p.ID ORDER BY p.ID ASC SEPARATOR ','), ',', %d) AS ids {$from} ORDER BY c DESC, MIN(p.ID) ASC LIMIT %d", array_merge( $join_args, $set_args ) ) );
			};
			if ( $product ) {
				$steps[] = static function () use ( $p, $m, $set, $set_args, $join, $join_args, $lang ): array {
					$rows = "(SELECT p.ID AS id, {$lang} AS lang, (SELECT TRIM(m.meta_value) FROM {$m} m WHERE m.post_id = p.ID AND m.meta_key = '_sku' AND TRIM(m.meta_value) <> '' ORDER BY m.meta_id ASC LIMIT 1) AS v FROM {$p} p {$join} WHERE {$set}) d";
					$from = "FROM {$rows} WHERE d.v IS NOT NULL GROUP BY d.lang, LOWER(d.v) HAVING COUNT(*) > 1";
					return array( 'duplicate_skus' => self::duplicate_groups( "SELECT COUNT(*) AS c {$from}", "SELECT MIN(d.v) AS v, COUNT(*) AS c, SUBSTRING_INDEX(GROUP_CONCAT(d.id ORDER BY d.id ASC SEPARATOR ','), ',', %d) AS ids {$from} ORDER BY c DESC, MIN(d.id) ASC LIMIT %d", array_merge( $join_args, $set_args ) ) );
				};
			}
			if ( null !== $languages ) {
				$steps[] = static function () use ( $p, $set, $set_args, $languages ): array {
					$rows = self::aggregate_rows( "SELECT COALESCE(l.lang, '') AS lang, COUNT(*) AS n FROM {$p} p LEFT JOIN ({$languages['members']}) l ON l.post_id = p.ID WHERE {$set} GROUP BY COALESCE(l.lang, '')", array_merge( $languages['args'], $set_args ) );
					$by   = array();
					foreach ( $rows as $row ) {
						$code        = self::language_code( $row['lang'] ) ?? '';
						$by[ $code ] = ( $by[ $code ] ?? 0 ) + (int) $row['n'];
					}
					return array( 'posts_by_language' => $by );
				};
				$steps[] = static function () use ( $p, $set, $set_args, $languages ): array {
					// A group is the set's posts sharing a translation group (a post in
					// none is its own); it has a language when a live member is in it.
					$codes = array_slice( $languages['languages'], 0, self::MAX_LANGUAGES );
					$flags = '';
					$sums  = '';
					$args  = array();
					foreach ( $codes as $i => $code ) {
						$flags .= ", MAX(IF(s.lang = %s OR v.lang = %s, 1, 0)) AS l{$i}";
						$sums  .= ", SUM(t.l{$i} = 0) AS m{$i}";
						array_push( $args, $code, $code );
					}
					$members = $languages['members'];
					$rows    = self::aggregate_rows( "SELECT COUNT(*) AS grps{$sums} FROM (SELECT s.g{$flags} FROM (SELECT l.grp AS grp, l.lang AS lang, COALESCE(CONCAT('g', l.grp), CONCAT('p', p.ID)) AS g FROM {$p} p LEFT JOIN ({$members}) l ON l.post_id = p.ID WHERE {$set}) s LEFT JOIN (SELECT l2.grp AS grp, l2.lang AS lang FROM ({$members}) l2 JOIN {$p} p2 ON p2.ID = l2.post_id WHERE p2.post_status NOT IN ('trash', 'auto-draft', 'inherit')) v ON v.grp = s.grp GROUP BY s.g) t", array_merge( $args, $languages['args'], $set_args, $languages['args'] ) );
					$row     = $rows[0] ?? array();
					$missing = array();
					foreach ( $codes as $i => $code ) {
						$missing[ $code ] = (int) ( $row[ "m{$i}" ] ?? 0 );
					}
					return array(
						'translation_groups' => array(
							'groups'               => (int) ( $row['grps'] ?? 0 ),
							'missing_a_version_in' => $missing,
						),
					);
				};
			}
		}

		$out = array();
		foreach ( $steps as $step ) {
			if ( $clock() - $started >= self::AGGREGATE_SECONDS ) {
				break;
			}
			try {
				$out = array_merge( $out, $step() );
			} catch ( \RuntimeException $e ) {
				// This statement did not answer: its fields are left out, never zero.
				continue;
			}
		}
		return $out;
	}

	/**
	 * Duplicate groups: how many, how many items are in them, and the largest
	 * as examples (value and ids), from two statements over the same groups.
	 *
	 * @param string $count    A statement giving one row per group.
	 * @param string $examples The groups' values and ids, largest first.
	 * @param array  $args     The groups' arguments (examples add their bounds).
	 * @return array{groups: int, items: int, examples: array<int, array{value: string, count: int, ids: int[]}>}
	 * @throws \RuntimeException When a statement failed.
	 */
	private static function duplicate_groups( string $count, string $examples, array $args ): array {
		$groups = self::aggregate_rows( "SELECT COUNT(*) AS grps, SUM(g.c) AS items FROM ({$count}) g", $args );
		$rows   = self::aggregate_rows( $examples, array_merge( array( self::DUPLICATE_EXAMPLES ), $args, array( self::DUPLICATE_EXAMPLES ) ) );
		$list   = array();
		foreach ( $rows as $row ) {
			$list[] = array(
				'value' => self::bounded( wp_strip_all_tags( (string) $row['v'] ), self::TITLE_CHARS ),
				'count' => (int) $row['c'],
				'ids'   => array_values( array_filter( array_map( 'intval', explode( ',', (string) $row['ids'] ) ) ) ),
			);
		}
		return array(
			'groups'   => (int) ( $groups[0]['grps'] ?? 0 ),
			'items'    => (int) ( $groups[0]['items'] ?? 0 ),
			'examples' => $list,
		);
	}

	/**
	 * One aggregate statement's rows, prepared and time-limited.
	 *
	 * @param string $sql  A SELECT with placeholders.
	 * @param array  $args Its values, in order.
	 * @return array<int, array<string, mixed>>
	 * @throws \RuntimeException When the statement failed.
	 */
	private static function aggregate_rows( string $sql, array $args ): array {
		global $wpdb;
		$wpdb->last_error = '';
		return (array) self::answered( $wpdb->get_results( Site_Find::time_limited( $wpdb->prepare( $sql, $args ), self::QUERY_SECONDS ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- fixed statements of this class; table names from $wpdb, every value a placeholder.
	}

	/**
	 * Where the translation plugin keeps each post's language and group, as a
	 * statement giving (post_id, grp, lang) for posts of the type, or null when
	 * no plugin translates it — the same plugins and conditions as
	 * {@see self::languages_of()}, from the same records: Polylang's `language`
	 * and `post_translations` terms, WPML's own table.
	 *
	 * @param string $type Post type.
	 * @return array{members: string, args: array, languages: string[]}|null
	 * @throws \RuntimeException When WPML's table could not be checked.
	 */
	private static function translation_source( string $type ): ?array {
		global $wpdb;
		$valid = static fn( array $codes ): array => array_values( array_filter( array_map( array( self::class, 'language_code' ), $codes ) ) );
		if ( function_exists( 'pll_get_post_language' ) && function_exists( 'pll_get_post_translations' ) ) {
			if ( function_exists( 'pll_is_translated_post_type' ) && ! pll_is_translated_post_type( $type ) ) {
				return null;
			}
			return array(
				'members'   => "SELECT tr.object_id AS post_id, MAX(IF(tt.taxonomy = 'post_translations', tt.term_taxonomy_id, NULL)) AS grp, MAX(IF(tt.taxonomy = 'language', t.slug, NULL)) AS lang 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 JOIN {$wpdb->posts} pp ON pp.ID = tr.object_id AND pp.post_type = %s WHERE tt.taxonomy IN ('language', 'post_translations') GROUP BY tr.object_id",
				'args'      => array( $type ),
				'languages' => $valid( function_exists( 'pll_languages_list' ) ? (array) pll_languages_list( array( 'fields' => 'slug' ) ) : array() ),
			);
		}
		if ( defined( 'ICL_SITEPRESS_VERSION' ) ) {
			$table = $wpdb->prefix . 'icl_translations';
			if ( ! self::table_exists( $table ) ) {
				return null;
			}
			$active = apply_filters( 'wpml_active_languages', null, array( 'skip_missing' => 0 ) );
			return array(
				'members'   => "SELECT element_id AS post_id, trid AS grp, language_code AS lang FROM {$table} WHERE element_type = %s",
				'args'      => array( 'post_' . $type ),
				'languages' => $valid( is_array( $active ) ? array_keys( $active ) : array() ),
			);
		}
		return null;
	}

	/**
	 * A language code as the translation plugins store one, or null.
	 *
	 * @param mixed $code A stored code.
	 */
	public static function language_code( $code ): ?string {
		return is_string( $code ) && 1 === preg_match( self::LANGUAGE_CODE, $code ) ? $code : null;
	}

	/**
	 * Where this site keeps a post's meta description: the SEO plugin that
	 * prints it and its own field, or no field when its storage is not one
	 * this reads (then the length is reported as unknown, never as none).
	 *
	 * @return array{plugin: string, key: string|null}
	 */
	public static function seo_source(): array {
		$detector = new Seo_Detector();
		$plugin   = $detector->detect();
		if ( 'fallback' !== $plugin ) {
			return array(
				'plugin' => $plugin,
				'key'    => $detector->meta_key_for( $plugin ),
			);
		}
		$other = $detector->other_seo_plugin();
		if ( null === $other ) {
			return array(
				'plugin' => 'none',
				'key'    => $detector->meta_key_for( 'fallback' ),
			);
		}
		return array(
			'plugin' => $other,
			'key'    => self::OTHER_SEO_KEYS[ $other ] ?? null,
		);
	}

	/**
	 * The listed meta keys of these posts: each key's first value, as its first
	 * {@see self::META_CHARS} characters and its length.
	 *
	 * @param int[]    $ids  Post ids.
	 * @param string[] $keys Meta keys (constants of this class and the SEO detector's).
	 * @return array<int, array<string, array{v: string, n: int}>>
	 * @throws \RuntimeException When the statement failed.
	 */
	private static function meta_of( array $ids, array $keys ): array {
		global $wpdb;
		if ( empty( $ids ) ) {
			return array();
		}
		$in_ids  = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
		$in_keys = implode( ', ', array_fill( 0, count( $keys ), '%s' ) );
		$wpdb->last_error = '';
		$rows             = self::answered( $wpdb->get_results( Site_Find::time_limited( $wpdb->prepare( "SELECT post_id, meta_key, LEFT(meta_value, %d) AS v, CHAR_LENGTH(meta_value) AS n FROM {$wpdb->postmeta} WHERE post_id IN ({$in_ids}) AND meta_key IN ({$in_keys}) ORDER BY meta_id ASC", array_merge( array( self::META_CHARS ), $ids, $keys ) ), self::QUERY_SECONDS ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- the IN lists are placeholders only; every value is prepared.
		$out = array();
		foreach ( $rows as $row ) {
			$id  = (int) $row['post_id'];
			$key = (string) $row['meta_key'];
			$v   = (string) $row['v'];
			// The first non-empty value wins: a variable product can store several `_price` rows.
			if ( isset( $out[ $id ][ $key ] ) && '' !== trim( $out[ $id ][ $key ]['v'] ) ) {
				continue;
			}
			$out[ $id ][ $key ] = array(
				'v' => self::bounded( wp_strip_all_tags( $v ), self::META_CHARS ),
				'n' => '' === trim( $v ) ? 0 : (int) $row['n'],
			);
		}
		return $out;
	}

	/**
	 * All in One SEO 4 keeps descriptions in its own table; the postmeta key is
	 * its legacy copy. Lengths by post id, or null when the table is not there
	 * (then the postmeta field is read instead).
	 *
	 * @param int[] $ids Post ids.
	 * @return array<int, int>|null
	 * @throws \RuntimeException When a statement failed.
	 */
	private static function aioseo_descriptions( array $ids ): ?array {
		global $wpdb;
		$table = $wpdb->prefix . 'aioseo_posts';
		if ( empty( $ids ) || ! self::table_exists( $table ) ) {
			return null;
		}
		$in_ids = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
		$wpdb->last_error = '';
		$rows             = self::answered( $wpdb->get_results( Site_Find::time_limited( $wpdb->prepare( "SELECT post_id, CHAR_LENGTH(description) AS n FROM {$table} WHERE post_id IN ({$in_ids})", $ids ), self::QUERY_SECONDS ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- All in One SEO's table, confirmed above; the IN list is placeholders only.
		$out = array();
		foreach ( $rows as $row ) {
			$out[ (int) $row['post_id'] ] = (int) $row['n'];
		}
		return $out;
	}

	/**
	 * Each post's language and its other language versions, from the
	 * translation plugin's own records, or null when no plugin translates this
	 * type. A version in the trash, or one that is an auto-draft, is no version.
	 *
	 * @param string $type Post type.
	 * @param int[]  $ids  Post ids.
	 * @return array{plugin: string, languages: string[], default: string|null, of: array<int, array{lang: string|null, translations: array<string, array{0: int, 1: string}>}>}|null
	 * @throws \RuntimeException When the versions' statuses could not be read.
	 */
	public static function languages_of( string $type, array $ids ): ?array {
		if ( function_exists( 'pll_get_post_language' ) && function_exists( 'pll_get_post_translations' ) ) {
			if ( function_exists( 'pll_is_translated_post_type' ) && ! pll_is_translated_post_type( $type ) ) {
				return null;
			}
			if ( function_exists( 'update_object_term_cache' ) && ! empty( $ids ) ) {
				update_object_term_cache( $ids, $type );
			}
			$maps = array();
			foreach ( $ids as $id ) {
				$maps[ $id ] = array(
					'lang' => pll_get_post_language( $id, 'slug' ),
					'map'  => (array) pll_get_post_translations( $id ),
				);
			}
			return self::shape_languages(
				'polylang',
				function_exists( 'pll_languages_list' ) ? (array) pll_languages_list( array( 'fields' => 'slug' ) ) : array(),
				function_exists( 'pll_default_language' ) ? pll_default_language( 'slug' ) : null,
				$maps
			);
		}
		if ( defined( 'ICL_SITEPRESS_VERSION' ) ) {
			$maps = self::wpml_maps( $type, $ids );
			if ( null === $maps ) {
				return null;
			}
			$active = apply_filters( 'wpml_active_languages', null, array( 'skip_missing' => 0 ) );
			return self::shape_languages(
				'wpml',
				is_array( $active ) ? array_keys( $active ) : array(),
				apply_filters( 'wpml_default_language', null ),
				$maps
			);
		}
		return null;
	}

	/**
	 * WPML's translation groups for these posts, from its own table: two
	 * prepared statements (the posts' groups, then every member of them).
	 *
	 * @param string $type Post type.
	 * @param int[]  $ids  Post ids.
	 * @return array<int, array{lang: string|null, map: array<string, int>}>|null
	 */
	private static function wpml_maps( string $type, array $ids ): ?array {
		global $wpdb;
		$table = $wpdb->prefix . 'icl_translations';
		if ( empty( $ids ) ) {
			return array();
		}
		if ( ! self::table_exists( $table ) ) {
			return null;
		}
		$element = 'post_' . $type;
		$in_ids  = implode( ', ', array_fill( 0, count( $ids ), '%d' ) );
		$wpdb->last_error = '';
		$own              = self::answered( $wpdb->get_results( Site_Find::time_limited( $wpdb->prepare( "SELECT element_id, trid, language_code FROM {$table} WHERE element_type = %s AND element_id IN ({$in_ids})", array_merge( array( $element ), $ids ) ), self::QUERY_SECONDS ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- WPML's table, confirmed above; the IN list is placeholders only.
		$trids = array_values( array_unique( array_map( static fn( $row ) => (int) $row['trid'], $own ) ) );
		$group = array();
		if ( ! empty( $trids ) ) {
			$in_trids = implode( ', ', array_fill( 0, count( $trids ), '%d' ) );
			$wpdb->last_error = '';
			$members          = self::answered( $wpdb->get_results( Site_Find::time_limited( $wpdb->prepare( "SELECT element_id, trid, language_code FROM {$table} WHERE element_type = %s AND trid IN ({$in_trids})", array_merge( array( $element ), $trids ) ), self::QUERY_SECONDS ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- WPML's table, confirmed above; the IN list is placeholders only.
			foreach ( $members as $row ) {
				if ( null !== $row['element_id'] ) {
					$group[ (int) $row['trid'] ][ (string) $row['language_code'] ] = (int) $row['element_id'];
				}
			}
		}
		$maps = array();
		foreach ( $own as $row ) {
			$maps[ (int) $row['element_id'] ] = array(
				'lang' => (string) $row['language_code'],
				'map'  => $group[ (int) $row['trid'] ] ?? array(),
			);
		}
		return $maps;
	}

	/**
	 * The language facts, shaped: each post's other language versions with
	 * their status, never itself, never one that is trashed or does not exist.
	 *
	 * @param string                                                  $plugin    polylang|wpml.
	 * @param array                                                   $languages The site's language codes.
	 * @param mixed                                                   $default   The default language code.
	 * @param array<int, array{lang: mixed, map: array<string, int>}> $maps      Post id => its language and group.
	 * @return array<string, mixed>
	 */
	public static function shape_languages( string $plugin, array $languages, $default, array $maps ): array {
		$valid   = static fn( $code ): bool => null !== self::language_code( $code );
		$other   = array();
		foreach ( $maps as $id => $entry ) {
			foreach ( $entry['map'] as $other_id ) {
				if ( (int) $other_id !== (int) $id ) {
					$other[] = (int) $other_id;
				}
			}
		}
		$status = self::statuses_of( array_values( array_unique( $other ) ) );
		$of     = array();
		foreach ( $maps as $id => $entry ) {
			$versions = array();
			foreach ( $entry['map'] as $code => $other_id ) {
				$other_id = (int) $other_id;
				if ( ! $valid( $code ) || $other_id === (int) $id || ! isset( $status[ $other_id ] ) ) {
					continue;
				}
				$versions[ (string) $code ] = array( $other_id, $status[ $other_id ] );
			}
			$of[ (int) $id ] = array(
				'lang'         => $valid( $entry['lang'] ) ? (string) $entry['lang'] : null,
				'translations' => $versions,
			);
		}
		return array(
			'plugin'    => $plugin,
			'languages' => array_values( array_filter( array_map( 'strval', $languages ), $valid ) ),
			'default'   => $valid( $default ) ? (string) $default : null,
			'of'        => $of,
		);
	}

	/**
	 * Post statuses by id, leaving out what is trashed, an auto-draft or gone.
	 *
	 * @param int[] $ids Post ids.
	 * @return array<int, string>
	 * @throws \RuntimeException When the database did not answer.
	 */
	private static function statuses_of( array $ids ): array {
		global $wpdb;
		if ( empty( $ids ) ) {
			return array();
		}
		$out = array();
		foreach ( array_chunk( $ids, self::MAX_PAGE ) as $chunk ) {
			$in_ids = implode( ', ', array_fill( 0, count( $chunk ), '%d' ) );
			$wpdb->last_error = '';
			$rows             = self::answered( $wpdb->get_results( Site_Find::time_limited( $wpdb->prepare( "SELECT ID, post_status FROM {$wpdb->posts} WHERE ID IN ({$in_ids})", $chunk ), self::QUERY_SECONDS ), ARRAY_A ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- the IN list is placeholders only.
			foreach ( $rows as $row ) {
				$state = (string) $row['post_status'];
				if ( 'trash' !== $state && 'auto-draft' !== $state && 'inherit' !== $state ) {
					$out[ (int) $row['ID'] ] = $state;
				}
			}
		}
		return $out;
	}

	/**
	 * Whether a plugin's own table exists. A failed check is a failed read,
	 * never "not there".
	 *
	 * @param string $table Full table name ($wpdb->prefix plus a fixed suffix).
	 * @throws \RuntimeException When the check failed.
	 */
	private static function table_exists( string $table ): bool {
		global $wpdb;
		$wpdb->last_error = '';
		$found            = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- whether a plugin's own table exists.
		if ( '' !== (string) $wpdb->last_error ) {
			throw new \RuntimeException( 'table check failed' );
		}
		return $table === $found;
	}

	/** A string cut to a length in characters. */
	private static function bounded( string $text, int $chars ): string {
		$text = trim( $text );
		return function_exists( 'mb_substr' ) ? mb_substr( $text, 0, $chars ) : substr( $text, 0, $chars );
	}
}