thinkrank / includes / seo / class-word-count-index.php

class-word-count-index.php in ThinkRank AI SEO – AI SEO Plugin for WordPress: Schema, XML Sitemaps, Meta Tags, Search Console & Local SEO trunk, at includes/seo/class-word-count-index.php

894 lines 35.3 KB

Raw Download Zip

1	<?php
2	/**
3	* Persisted word count per post.
4	*
5	* @package ThinkRank\SEO
6	* @since 2.10.0
7	*/
8
9	declare(strict_types=1);
10
11	namespace ThinkRank\SEO;
12
13	use ThinkRank\Core\Seo_Text;
14
15	// Prevent direct access
16	if (!defined('ABSPATH')) {
17	exit;
18	}
19
20	/**
21	* Word Count Index
22	*
23	* "Which of my pages are thin?" is a question about every published page, and
24	* answering it honestly means counting the words a visitor actually reads —
25	* which on an Elementor, Divi, Oxygen, Beaver or Bricks page are not in
26	* `post_content` at all (#565).
27	*
28	* That makes the count expensive in a way the snippet index's is not.
29	* {@see Builder_Content::resolve()} walks a builder's stored tree, so it cannot
30	* run over a whole site inside one request. The answer is the same shape as
31	* {@see Snippet_Index}: count once, store the number in post meta, and answer
32	* every report by SQL over the stored numbers. Counting happens a bounded batch
33	* at a time and the caller is told how many are left.
34	*
35	* Deliberately a separate index rather than a fourth field on the snippet
36	* index, because the two are invalidated by different things. A word count
37	* changes when the body changes; a snippet verdict changes when a title
38	* template, the site name or the separator changes. Sharing one entry would
39	* mean every edit to an SEO template re-resolved every builder page on the
40	* site, which is the one cost this class exists to avoid.
41	*
42	* The stored value is `{version}:{unit}:{count}`. There is no generation:
43	* almost nothing global changes a post's word count, so a post's own edits are
44	* what make its entry stale. The version exists so that changing how counting
45	* works retires every entry with one constant.
46	*
47	* The two global things that do change a count are handled explicitly. The
48	* unit is written into the entry, because a locale switch between words
49	* and characters changes every number at once, and an entry is only current
50	* while its unit is the one the site counts in now. **Content a post pulls in
51	* by reference** (a synced pattern, a navigation menu) is expanded by
52	* `do_blocks()` before counting, so editing it changes the count of every post
53	* that references it; {@see on_referenced_change()} retires those entries.
54	*
55	* @since 2.10.0
56	*/
57	class Word_Count_Index {
58
59	/**
60	* Post meta holding the entry.
61	*/
62	public const META_KEY = '_thinkrank_word_count';
63
64	/**
65	* Counting rules version. Bump to retire every stored entry.
66	*/
67	public const VERSION = 2;
68
69	/**
70	* Short codes for the counting unit, as stored in an entry.
71	*
72	* Version 1 entries carried no unit, so a site that switched between a
73	* words locale and a characters one kept its old numbers as "current" and
74	* the report printed word counts as character counts. Version 2 is the
75	* first to carry it; every version 1 entry is recounted once.
76	*
77	* @var array<string,string>
78	*/
79	private const UNIT_CODES = [
80	'words' => 'w',
81	'characters_excluding_spaces' => 'c',
82	'characters_including_spaces' => 'cs',
83	];
84
85	/**
86	* Post types whose content other posts pull in by `"ref":ID` and that
87	* `do_blocks()` expands in place: synced patterns (`core/block`) and
88	* navigation menus (`core/navigation`).
89	*
90	* Builder templates are not here. Elementor global widgets and templates,
91	* and Bricks templates, are expanded by the builder at render time and are
92	* referenced in shapes that differ per builder; a page using one is
93	* recounted on its own next edit, or by Rescan.
94	*
95	* @var string[]
96	*/
97	private const REFERENCED_POST_TYPES = ['wp_block', 'wp_navigation'];
98
99	/**
100	* How deep {@see posts_referencing()} follows a pattern nested inside a
101	* pattern. Core refuses to render a pattern inside itself; this bound is
102	* for the same reason, so a cycle in stored content cannot loop here.
103	*/
104	private const MAX_REFERENCE_DEPTH = 5;
105
106	/**
107	* Posts per `IN (...)` list when retiring entries in bulk.
108	*/
109	private const BULK_CHUNK = 500;
110
111	/**
112	* Most entries one refresh call will build, and the time it may spend.
113	*
114	* Much smaller than the snippet index's 500, and for a real reason:
115	* resolving a builder tree is orders of magnitude dearer than reading two
116	* meta values, so a batch sized for the cheap case would time out on a
117	* site built entirely in Elementor.
118	*/
119	private const REFRESH_MAX_POSTS = 50;
120	private const REFRESH_MAX_SECONDS = 3.0;
121
122	/**
123	* Memo for {@see watched_meta()}.
124	*
125	* @var string[]\|null
126	*/
127	private static $watched_meta = null;
128
129	/**
130	* Memo for {@see unit()} when it has to switch locale to answer: site
131	* locale => unit.
132	*
133	* @var array<string,string>
134	*/
135	private static $site_units = [];
136
137	/**
138	* Register invalidation hooks. Runs on every request, because posts are
139	* edited everywhere and not only on the report's screen.
140	*
141	* @return void
142	*/
143	public function init(): void {
144	add_action('save_post', [self::class, 'mark_post_stale'], 99, 1);
145
146	add_action('added_post_meta', [self::class, 'on_meta_change'], 10, 3);
147	add_action('updated_post_meta', [self::class, 'on_meta_change'], 10, 3);
148	add_action('deleted_post_meta', [self::class, 'on_meta_change'], 10, 3);
149
150	// A post leaving the report is the case no other hook here covers.
151	// Trashing, unpublishing or deleting one removes it from every query
152	// this class answers, but it changes no entry, so nothing would tell a
153	// report derived from the index that its answer had moved.
154	add_action('transition_post_status', [self::class, 'on_status_change'], 10, 3);
155	add_action('before_delete_post', [self::class, 'on_post_deleted'], 10, 1);
156
157	// Content other posts pull in by reference. Saving covers trashing and
158	// restoring too (both go through wp_update_post()), and a trashed
159	// pattern renders nothing, so the posts that use it lose those words.
160	foreach (self::REFERENCED_POST_TYPES as $post_type) {
161	add_action('save_post_' . $post_type, [self::class, 'on_referenced_change'], 99, 1);
162	}
163	}
164
165	/**
166	* Post meta whose change changes a post's word count.
167	*
168	* Read from {@see Builder_Content::builder_meta_keys()} rather than listed
169	* here, so a builder added to the resolver is invalidated by the same
170	* commit that teaches the resolver to read it. Bricks stores its tree
171	* outside that list, so it is named explicitly.
172	*
173	* Memoised because {@see on_meta_change()} is the callback on three hooks
174	* that fire for every post meta write anywhere in WordPress — an import
175	* writing a dozen fields across a thousand posts rebuilt this list tens of
176	* thousands of times to answer the same question. The source is a class
177	* constant, so there is nothing for the memo to go stale against.
178	*
179	* @return string[]
180	*/
181	public static function watched_meta(): array {
182	if (null === self::$watched_meta) {
183	self::$watched_meta = array_values(array_unique(array_merge(
184	Builder_Content::builder_meta_keys(),
185	['_bricks_page_content_2', '_bricks_editor_mode']
186	)));
187	}
188
189	return self::$watched_meta;
190	}
191
192	/**
193	* Post meta changed.
194	*
195	* @param int\|array $meta_id Meta ID(s).
196	* @param int $object_id Post ID.
197	* @param string $meta_key Meta key.
198	* @return void
199	*/
200	public static function on_meta_change($meta_id, $object_id, $meta_key): void {
201	if (in_array($meta_key, self::watched_meta(), true)) {
202	self::mark_post_stale($object_id);
203	}
204	}
205
206	/**
207	* A post moved between statuses.
208	*
209	* Only one direction needs announcing: a post leaving `publish`. It takes
210	* its row out of every query here while its entry sits untouched, so nothing
211	* else can tell a report derived from the index that its answer moved.
212	*
213	* A post arriving at `publish` deliberately does not bump. It has no entry
214	* yet, so it is pending, and a report is never served from cache while
215	* anything is pending — the batch that counts it bumps the revision itself.
216	* Bumping here as well would write one option row per post through a bulk
217	* import of a thousand published posts, to say something already known.
218	*
219	* @param string $new_status Status now.
220	* @param string $old_status Status before.
221	* @param \WP_Post\|null $post Post.
222	* @return void
223	*/
224	public static function on_status_change($new_status, $old_status, $post = null): void {
225	if ($new_status === $old_status \|\| !$post instanceof \WP_Post) {
226	return;
227	}
228
229	if ('publish' !== $old_status) {
230	return;
231	}
232
233	if (wp_is_post_revision($post->ID) \|\| wp_is_post_autosave($post->ID)) {
234	return;
235	}
236
237	self::bump_revision();
238	}
239
240	/**
241	* A post is about to be deleted for good.
242	*
243	* Hooked before the delete rather than after it, so the entry is still
244	* readable: only a post that had been counted can change an answer, and
245	* revisions never have an entry, which keeps revision cleanup out of this.
246	*
247	* @param int\|mixed $post_id Post ID.
248	* @return void
249	*/
250	public static function on_post_deleted($post_id): void {
251	$post_id = (int) $post_id;
252	if ($post_id <= 0) {
253	return;
254	}
255
256	// A synced pattern or menu being deleted for good takes its words out
257	// of every post that referenced it. It never has an entry of its own.
258	if (in_array(get_post_type($post_id), self::REFERENCED_POST_TYPES, true)) {
259	self::on_referenced_change($post_id);
260	return;
261	}
262
263	if (null !== self::decode((string) get_post_meta($post_id, self::META_KEY, true))) {
264	self::bump_revision();
265	}
266	}
267
268	/**
269	* A synced pattern or navigation menu changed.
270	*
271	* Counting renders a post through `do_blocks()`, which expands
272	* `<!-- wp:block {"ref":12} /-->` into pattern 12's content. Nothing about
273	* the referencing post changes when pattern 12 is edited, so without this
274	* every page using a 400-word pattern kept its 400-word count after the
275	* pattern was cut to one line, and was never reported as thin.
276	*
277	* Found by searching `post_content`, which is a scan of the posts table.
278	* That is acceptable here and nowhere hotter: it runs when someone saves a
279	* pattern or a menu, which is rare, and it replaces recounting the site.
280	*
281	* @param int\|mixed $post_id Pattern or menu ID.
282	* @return void
283	*/
284	public static function on_referenced_change($post_id): void {
285	$post_id = (int) $post_id;
286	if ($post_id <= 0 \|\| wp_is_post_revision($post_id) \|\| wp_is_post_autosave($post_id)) {
287	return;
288	}
289
290	self::forget(self::posts_referencing($post_id));
291	}
292
293	/**
294	* Counted posts whose content references a post by `"ref":ID`, directly or
295	* through patterns nested inside patterns.
296	*
297	* @param int $post_id Referenced post.
298	* @return int[] IDs of posts that hold an entry.
299	*/
300	private static function posts_referencing(int $post_id): array {
301	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the interpolated fragment is built by reference_sql() through prepare(); every value is a placeholder. Runs on a pattern save, never on a front-end request.
302	global $wpdb;
303
304	$seen = [$post_id => true];
305	$frontier = [$post_id];
306	$counted = [];
307
308	for ($depth = 0; !empty($frontier) && $depth < self::MAX_REFERENCE_DEPTH; $depth++) {
309	$match = self::reference_sql($frontier);
310
311	// Posts with an entry that use anything in the frontier.
312	$ids = $wpdb->get_col($wpdb->prepare(
313	"SELECT DISTINCT p.ID FROM {$wpdb->posts} p
314	INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s
315	WHERE " . $match,
316	self::META_KEY
317	));
318	foreach ((array) $ids as $id) {
319	$counted[(int) $id] = true;
320	}
321
322	// Patterns that nest anything in the frontier: every post using
323	// them renders the edited content too.
324	$nested = $wpdb->get_col($wpdb->prepare(
325	"SELECT p.ID FROM {$wpdb->posts} p
326	WHERE p.post_type = %s AND " . $match,
327	'wp_block'
328	));
329
330	$frontier = [];
331	foreach ((array) $nested as $id) {
332	$id = (int) $id;
333	if ($id > 0 && !isset($seen[$id])) {
334	$seen[$id] = true;
335	$frontier[] = $id;
336	}
337	}
338	}
339
340	return array_keys($counted);
341	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
342	}
343
344	/**
345	* WHERE fragment matching `post_content` that references any of these IDs.
346	*
347	* Core serialises block attributes as compact JSON, so a reference to 12
348	* reads `"ref":12` followed by `}` or `,`. Matching the terminator is what
349	* keeps an edit to pattern 12 from recounting every page that uses 120.
350	*
351	* @param int[] $ids Referenced post IDs.
352	* @return string Trusted SQL.
353	*/
354	private static function reference_sql(array $ids): string {
355	global $wpdb;
356
357	$likes = [];
358	foreach ($ids as $id) {
359	foreach (['}', ','] as $terminator) {
360	$likes[] = $wpdb->prepare(
361	'p.post_content LIKE %s',
362	'%' . $wpdb->esc_like('"ref":' . (int) $id . $terminator) . '%'
363	);
364	}
365	}
366
367	return '(' . implode(' OR ', $likes) . ')';
368	}
369
370	/**
371	* Retire the entries of many posts in one statement per chunk.
372	*
373	* `delete_post_meta()` per post would be one query and one set of hooks
374	* each, for a pattern that can sit on every page of the site. The meta
375	* cache is cleared per post so a persistent object cache does not keep
376	* serving the entry that was just removed.
377	*
378	* @param int[] $post_ids Post IDs.
379	* @return void
380	*/
381	private static function forget(array $post_ids): void {
382	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the IN list is a run of %d built from the chunk size, so the sniff cannot see the placeholders it looks for; every value is still passed to prepare(). The per-post meta cache is cleared below.
383	global $wpdb;
384
385	$post_ids = array_values(array_unique(array_filter(array_map('intval', $post_ids))));
386	if (empty($post_ids)) {
387	return;
388	}
389
390	foreach (array_chunk($post_ids, self::BULK_CHUNK) as $chunk) {
391	$in = implode(',', array_fill(0, count($chunk), '%d'));
392	$wpdb->query($wpdb->prepare(
393	"DELETE FROM {$wpdb->postmeta} WHERE meta_key = %s AND post_id IN ({$in})",
394	array_merge([self::META_KEY], $chunk)
395	));
396
397	foreach ($chunk as $id) {
398	wp_cache_delete($id, 'post_meta');
399	}
400	}
401
402	self::bump_revision();
403	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
404	}
405
406	/**
407	* Make one post's entry stale.
408	*
409	* The revision is bumped only when an entry was really removed. A post that
410	* had none cannot have contributed to any answer, which keeps a bulk import
411	* of fresh posts from writing one option row per post — and it means a
412	* change that takes a counted post out of scope without changing its status
413	* (adding a password, most of all) still retires the cached report.
414	*
415	* @param int\|mixed $post_id Post ID.
416	* @return void
417	*/
418	public static function mark_post_stale($post_id): void {
419	$post_id = (int) $post_id;
420	if ($post_id <= 0 \|\| wp_is_post_revision($post_id) \|\| wp_is_post_autosave($post_id)) {
421	return;
422	}
423
424	if (delete_post_meta($post_id, self::META_KEY)) {
425	self::bump_revision();
426	}
427	}
428
429	/**
430	* How many times a batch of entries has been rebuilt.
431	*
432	* A report derived from the whole index needs to know that some entry
433	* changed, which no single post's meta can tell it. Bumped once per batch
434	* rather than once per entry.
435	*
436	* @return int
437	*/
438	public static function revision(): int {
439	return (int) get_option('thinkrank_word_count_index_revision', 0);
440	}
441
442	/**
443	* Record that entries changed. Not autoloaded: only the report reads it,
444	* and never on a front-end request.
445	*
446	* @return void
447	*/
448	private static function bump_revision(): void {
449	update_option('thinkrank_word_count_index_revision', self::revision() + 1, false);
450	}
451
452	/**
453	* Encode an entry.
454	*
455	* @param int $count Words (or characters, per the unit).
456	* @param string\|null $unit Unit the count is in. Defaults to the site's.
457	* @return string
458	*/
459	public static function encode(int $count, ?string $unit = null): string {
460	return self::prefix($unit ?? self::unit()) . max(0, $count);
461	}
462
463	/**
464	* Decode an entry.
465	*
466	* An entry counted in another unit is as stale as one from another
467	* version: 40 words and 40 characters are not the same page.
468	*
469	* @param string $value Stored value.
470	* @return int\|null Count, or null when malformed, from an older version or
471	* in a unit the site no longer counts in.
472	*/
473	public static function decode(string $value): ?int {
474	$prefix = self::prefix(self::unit());
475	if (0 !== strpos($value, $prefix)) {
476	return null;
477	}
478
479	$count = substr($value, strlen($prefix));
480
481	return '' !== $count && ctype_digit($count) ? (int) $count : null;
482	}
483
484	/**
485	* The part of an entry before the count: version and unit.
486	*
487	* @param string $unit Counting unit.
488	* @return string
489	*/
490	private static function prefix(string $unit): string {
491	$code = self::UNIT_CODES[$unit] ?? self::UNIT_CODES['characters_excluding_spaces'];
492
493	return self::VERSION . ':' . $code . ':';
494	}
495
496	/**
497	* LIKE pattern matching an entry written by the current rules, in the
498	* unit the site counts in now.
499	*
500	* @return string
501	*/
502	private static function current_like(): string {
503	global $wpdb;
504
505	return $wpdb->esc_like(self::prefix(self::unit())) . '%';
506	}
507
508	/**
509	* Count the content of a post the way the locale counts it.
510	*
511	* Two things here are easy to get wrong and invisible in English.
512	*
513	* What to count. `post_content` is empty on a builder page, so counting
514	* it reports a 900-word Elementor page as 0 and calls it thin. Every count
515	* goes through {@see Builder_Content::resolve()}, which is the same
516	* resolution the editor score and the meta description already use.
517	*
518	* What a "word" is. WordPress reads the unit from a per-locale gettext
519	* string, and `th`, `ja` and `zh_*` set it to characters rather than words
520	* (#687). Splitting those on whitespace returns 1 for an entire article, so
521	* a Japanese site would report every page as thin. This mirrors core's own
522	* counter: words where the locale counts words, characters otherwise.
523	*
524	* @param \WP_Post $post Post to count.
525	* @return int Count in {@see self::unit()}.
526	*/
527	public static function count_post(\WP_Post $post): int {
528	return self::count_text(Builder_Content::resolve($post));
529	}
530
531	/**
532	* Count a string the way the locale counts it.
533	*
534	* @param string $content HTML or text.
535	* @return int
536	*/
537	public static function count_text(string $content): int {
538	// Scripts and styles carry no reading matter, and a page builder's
539	// output can hold a great deal of both. Counting them would make an
540	// empty page look substantial, which is the failure that matters here.
541	$text = preg_replace('#<(script\|style)\b[^>]>.?</\1>#is', ' ', $content);
542	$text = wp_strip_all_tags((string) $text);
543	$text = html_entity_decode($text, ENT_QUOTES \| ENT_HTML5, 'UTF-8');
544	// Non-breaking spaces are spaces to a reader.
545	$text = str_replace(["\xc2\xa0", "\xe2\x80\x8b"], ' ', $text);
546	$text = trim((string) preg_replace('/\s+/u', ' ', $text));
547
548	if ('' === $text) {
549	return 0;
550	}
551
552	switch (self::unit()) {
553	case 'characters_including_spaces':
554	return mb_strlen($text);
555
556	case 'characters_excluding_spaces':
557	return mb_strlen(str_replace(' ', '', $text));
558
559	default:
560	return count(preg_split('/\s+/u', $text, -1, PREG_SPLIT_NO_EMPTY) ?: []);
561	}
562	}
563
564	/**
565	* The unit the site counts in.
566	*
567	* The site locale, not the viewer's. The report is one answer shared by
568	* every user, but a REST request from wp-admin loads the translations of
569	* the requesting user's profile language (`_locale=user`), which is where
570	* core reads the unit from. Asking the loaded locale meant an administrator
571	* whose profile is Japanese counted a batch in characters, a colleague in
572	* English counted the next batch in words, and the index held both.
573	*
574	* Switching locale loads core's translations, so the answer is memoised
575	* per site locale for the rest of the request. When the loaded locale is
576	* already the site's, nothing is switched and nothing is memoised.
577	*
578	* @return string 'words', 'characters_excluding_spaces' or 'characters_including_spaces'.
579	*/
580	public static function unit(): string {
581	$site = (string) get_locale();
582
583	// The switcher is created during setup_theme; a count asked for before
584	// then (a save during plugins_loaded) cannot switch, and uses what is
585	// loaded rather than fataling.
586	if (!function_exists('determine_locale')
587	\|\| !function_exists('switch_to_locale')
588	\|\| empty($GLOBALS['wp_locale_switcher'])
589	\|\| determine_locale() === $site
590	) {
591	return self::loaded_locale_unit();
592	}
593
594	if (!isset(self::$site_units[$site])) {
595	$switched = switch_to_locale($site);
596	try {
597	self::$site_units[$site] = self::loaded_locale_unit();
598	} finally {
599	if ($switched) {
600	restore_previous_locale();
601	}
602	}
603	}
604
605	return self::$site_units[$site];
606	}
607
608	/**
609	* The unit of whichever locale is loaded right now.
610	*
611	* @return string
612	*/
613	private static function loaded_locale_unit(): string {
614	if (Seo_Text::locale_counts_words()) {
615	return 'words';
616	}
617
618	if (function_exists('wp_get_word_count_type')) {
619	$type = (string) wp_get_word_count_type();
620
621	return 'characters_including_spaces' === $type
622	? 'characters_including_spaces'
623	: 'characters_excluding_spaces';
624	}
625
626	return 'characters_excluding_spaces';
627	}
628
629	/**
630	* Build a bounded batch of missing entries.
631	*
632	* @param string[] $post_types Post types in scope.
633	* @param string[] $statuses Post statuses.
634	* @return int How many entries remain to build after this batch.
635	*/
636	public static function refresh(array $post_types, array $statuses): int {
637	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the interpolated fragment is built by scope_sql() through prepare(); every value is a placeholder. The index is itself the cache.
638	global $wpdb;
639
640	if (empty($post_types)) {
641	return 0;
642	}
643
644	$scope = self::scope_sql($post_types, $statuses);
645	$started = microtime(true);
646
647	$ids = $wpdb->get_col($wpdb->prepare(
648	"SELECT p.ID FROM {$wpdb->posts} p
649	LEFT JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s
650	WHERE " . $scope . "
651	AND (m.meta_value IS NULL OR m.meta_value NOT LIKE %s)
652	ORDER BY p.ID DESC
653	LIMIT %d",
654	self::META_KEY,
655	self::current_like(),
656	self::REFRESH_MAX_POSTS
657	));
658
659	$ids = array_map('intval', (array) $ids);
660
661	if (!empty($ids)) {
662	_prime_post_caches($ids, false, true);
663
664	foreach ($ids as $post_id) {
665	self::build($post_id);
666
667	// Checked per post, not per chunk: one Bricks page can take
668	// longer than the whole budget, and a batch that only checks
669	// between chunks would sail past it.
670	if (microtime(true) - $started > self::REFRESH_MAX_SECONDS) {
671	break;
672	}
673	}
674
675	self::bump_revision();
676	}
677
678	return max(0, self::pending($post_types, $statuses));
679	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
680	}
681
682	/**
683	* Compute and store one entry.
684	*
685	* @param int $post_id Post ID.
686	* @return void
687	*/
688	private static function build(int $post_id): void {
689	$post = get_post($post_id);
690	if (!$post instanceof \WP_Post) {
691	return;
692	}
693
694	update_post_meta($post_id, self::META_KEY, self::encode(self::count_post($post)));
695	}
696
697	/**
698	* How many posts in scope still have no current entry.
699	*
700	* @param string[] $post_types Post types.
701	* @param string[] $statuses Post statuses.
702	* @return int
703	*/
704	public static function pending(array $post_types, array $statuses): int {
705	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the interpolated fragment is built by scope_sql() through prepare(); every value is a placeholder. The index is itself the cache.
706	global $wpdb;
707
708	if (empty($post_types)) {
709	return 0;
710	}
711
712	return (int) $wpdb->get_var($wpdb->prepare(
713	"SELECT COUNT(*) FROM {$wpdb->posts} p
714	LEFT JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s
715	WHERE " . self::scope_sql($post_types, $statuses) . "
716	AND (m.meta_value IS NULL OR m.meta_value NOT LIKE %s)",
717	self::META_KEY,
718	self::current_like()
719	));
720	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
721	}
722
723	/**
724	* Counted posts per post type, and how many of them fall under that post
725	* type's threshold.
726	*
727	* One query for every post type rather than one each, and the comparison
728	* happens in SQL so a site with 20,000 products never loads them.
729	*
730	* @param array<string,int> $thresholds Post type => threshold.
731	* @param string[] $statuses Post statuses.
732	* @return array<string,array{counted:int, thin:int}>
733	*/
734	public static function totals(array $thresholds, array $statuses): array {
735	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the interpolated fragment is built by scope_sql() through prepare(); the CASE compares a column against integers cast from the threshold map. The index is itself the cache.
736	global $wpdb;
737
738	if (empty($thresholds)) {
739	return [];
740	}
741
742	$count_sql = self::count_sql('m');
743
744	// One CASE arm per post type, each prepared on its own and
745	// concatenated rather than left as placeholders in the outer query.
746	//
747	// The order matters and is easy to get wrong: these placeholders sit
748	// in the SELECT clause, before the ones in the JOIN and WHERE, so a
749	// single prepare() over the whole statement binds them in that order.
750	// Getting it wrong does not error — it silently hands `meta_key` a
751	// post type name, the INNER JOIN matches nothing and the report reads
752	// "0 posts counted" on a site full of content. Preparing each fragment
753	// where it is built removes the ordering question entirely.
754	$arms = [];
755	foreach ($thresholds as $post_type => $threshold) {
756	$arms[] = $wpdb->prepare(
757	'WHEN p.post_type = %s THEN %d',
758	(string) $post_type,
759	max(0, (int) $threshold)
760	);
761	}
762	$threshold_sql = 'CASE ' . implode(' ', $arms) . ' ELSE 0 END';
763
764	$scope = self::scope_sql(array_keys($thresholds), $statuses);
765	$meta_key_sql = $wpdb->prepare('m.meta_key = %s', self::META_KEY);
766	$current_sql = $wpdb->prepare('m.meta_value LIKE %s', self::current_like());
767
768	$rows = $wpdb->get_results(
769	"SELECT p.post_type,
770	COUNT(*) AS counted,
771	SUM({$count_sql} < {$threshold_sql}) AS thin
772	FROM {$wpdb->posts} p
773	INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND {$meta_key_sql}
774	WHERE {$scope} AND {$current_sql}
775	GROUP BY p.post_type",
776	ARRAY_A
777	);
778
779	$totals = [];
780	foreach ((array) $rows as $row) {
781	$totals[(string) $row['post_type']] = [
782	'counted' => (int) $row['counted'],
783	'thin' => (int) $row['thin'],
784	];
785	}
786
787	return $totals;
788	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
789	}
790
791	/**
792	* The thinnest posts of one post type, under its threshold.
793	*
794	* Thinnest first: the emptiest page is the one worth opening, and on a site
795	* with hundreds of thin pages the tail is noise.
796	*
797	* @param string $post_type Post type.
798	* @param int $threshold Count below which a post is thin.
799	* @param string[] $statuses Post statuses.
800	* @param int $limit Most posts to return.
801	* @return array<int,array{post_id:int, count:int}>
802	*/
803	public static function thinnest(string $post_type, int $threshold, array $statuses, int $limit): array {
804	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the interpolated fragment is built by scope_sql() through prepare(); every value is a placeholder. The index is itself the cache.
805	global $wpdb;
806
807	$count_sql = self::count_sql('m');
808
809	$rows = $wpdb->get_results($wpdb->prepare(
810	"SELECT p.ID, {$count_sql} AS word_count
811	FROM {$wpdb->posts} p
812	INNER JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = %s
813	WHERE " . self::scope_sql([$post_type], $statuses) . "
814	AND m.meta_value LIKE %s
815	AND {$count_sql} < %d
816	ORDER BY word_count ASC, p.ID DESC
817	LIMIT %d",
818	self::META_KEY,
819	self::current_like(),
820	max(0, $threshold),
821	max(1, $limit)
822	), ARRAY_A);
823
824	$posts = [];
825	foreach ((array) $rows as $row) {
826	$posts[] = [
827	'post_id' => (int) $row['ID'],
828	'count' => (int) $row['word_count'],
829	];
830	}
831
832	return $posts;
833	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
834	}
835
836	/**
837	* SQL reading the count out of a stored entry.
838	*
839	* @param string $alias Postmeta table alias.
840	* @return string Trusted SQL.
841	*/
842	private static function count_sql(string $alias): string {
843	$alias = self::alias($alias);
844
845	return "CAST(SUBSTRING_INDEX({$alias}.meta_value, ':', -1) AS UNSIGNED)";
846	}
847
848	/**
849	* WHERE fragment for post types and statuses.
850	*
851	* @param string[] $post_types Post types.
852	* @param string[] $statuses Post statuses.
853	* @return string Trusted SQL.
854	*/
855	private static function scope_sql(array $post_types, array $statuses): string {
856	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- both IN lists are runs of %s built from the argument counts, so the sniff cannot see the placeholders it looks for; every value is still passed to prepare().
857	global $wpdb;
858
859	$post_types = array_values(array_unique(array_filter($post_types, 'is_string')));
860	if (empty($post_types)) {
861	// No post type matches nothing. Falling back to every post type
862	// would silently widen a scope the caller meant to narrow.
863	return '1 = 0';
864	}
865
866	$statuses = array_values(array_intersect($statuses, ['publish', 'future', 'draft', 'pending', 'private']));
867	if (empty($statuses)) {
868	$statuses = ['publish'];
869	}
870
871	$types_in = implode(',', array_fill(0, count($post_types), '%s'));
872	$statuses_in = implode(',', array_fill(0, count($statuses), '%s'));
873
874	return $wpdb->prepare(
875	"p.post_type IN ({$types_in}) AND p.post_status IN ({$statuses_in}) AND p.post_password = ''",
876	array_merge($post_types, $statuses)
877	);
878	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
879	}
880
881	/**
882	* A table alias this class uses, and nothing else.
883	*
884	* Aliases are interpolated into SQL (identifiers cannot be placeholders),
885	* so only the fixed set this class writes is accepted.
886	*
887	* @param string $alias Requested alias.
888	* @return string
889	*/
890	private static function alias(string $alias): string {
891	return in_array($alias, ['p', 'm'], true) ? $alias : 'm';
892	}
893	}
894