thinkrank / includes / ai / class-brand-visibility-runner.php

class-brand-visibility-runner.php in ThinkRank AI SEO – AI SEO Plugin for WordPress: Schema, XML Sitemaps, Meta Tags, Search Console & Local SEO 1.29.0, at includes/ai/class-brand-visibility-runner.php

811 lines 28.7 KB

Raw Download Zip

1	<?php
2	/**
3	* Brand Visibility v2 — queued run engine.
4	*
5	* A full analysis is queries × platforms × samples, which is routinely 100+
6	* AI calls. v1 ran that batch synchronously inside a REST request: on any real
7	* configuration it would exhaust max_execution_time, and an interruption lost
8	* everything. This engine persists every probe as a task row and drains them
9	* off-request in cron ticks with a wall-clock budget, so a run makes forward
10	* progress, survives interruption, and reports honest progress while it works.
11	*
12	* The shape mirrors the standard batch/sweeper/finalizer trio:
13	* start() — snapshot config, fan out task rows, kick the first tick
14	* tick() — process what fits in the budget, then reschedule if unfinished
15	* sweep() — return tasks stuck in `running` (a killed worker) to `pending`
16	* finalize() — aggregate task rows into the run's results, once
17	*
18	* @package ThinkRank\AI
19	* @since 1.28.0
20	*/
21
22	declare(strict_types=1);
23
24	namespace ThinkRank\AI;
25
26	use ThinkRank\Core\Plan_Config;
27	use ThinkRank\Core\Settings;
28
29	if (!defined('ABSPATH')) {
30	exit;
31	}
32
33	/**
34	* Creates and drains Brand Visibility analysis runs.
35	*/
36	class Brand_Visibility_Runner {
37
38	/**
39	* Cron hook that drains pending tasks.
40	*/
41	public const TICK_HOOK = 'thinkrank_bv_tick';
42
43	/**
44	* Recurring safety net that restarts a stalled drain.
45	*/
46	public const WATCHDOG_HOOK = 'thinkrank_bv_watchdog';
47
48	/**
49	* Interval slug for the watchdog.
50	*/
51	public const WATCHDOG_INTERVAL = 'thinkrank_bv_five_minutes';
52
53	/**
54	* Wall-clock budget for one tick, seconds. Comfortably inside the 30s
55	* PHP default while still landing several probes per tick.
56	*/
57	private const TICK_BUDGET = 20;
58
59	/**
60	* Hard ceiling on probes per tick, so a fast provider can't run a tick
61	* long enough to collide with the next scheduled one.
62	*/
63	private const TICK_MAX_TASKS = 12;
64
65	/**
66	* Attempts before a task is abandoned.
67	*/
68	private const MAX_ATTEMPTS = 2;
69
70	/**
71	* Minutes after which a `running` task is presumed dead.
72	*/
73	private const STUCK_AFTER_MINUTES = 10;
74
75	/**
76	* Output-token budget per probe. Reasoning-safe: see 1942a44/6094d9d —
77	* GPT-5-family models spend output tokens on hidden reasoning first and
78	* return empty text if the budget is too small.
79	*/
80	private const ANSWER_TOKENS = 8000;
81
82	/**
83	* Settings accessor.
84	*
85	* @var Settings
86	*/
87	private Settings $settings;
88
89	/**
90	* Provider resolver.
91	*
92	* @var Brand_Visibility_Providers
93	*/
94	private Brand_Visibility_Providers $providers;
95
96	/**
97	* Constructor.
98	*
99	* @param Settings\|null $settings Settings.
100	* @param Brand_Visibility_Providers\|null $providers Provider resolver.
101	*/
102	public function __construct(?Settings $settings = null, ?Brand_Visibility_Providers $providers = null) {
103	$this->settings = $settings ?? Settings::instance();
104	$this->providers = $providers ?? new Brand_Visibility_Providers($this->settings);
105	}
106
107	/**
108	* Register the cron listener.
109	*
110	* @return void
111	*/
112	public function init(): void {
113	add_action(self::TICK_HOOK, [$this, 'tick']);
114	add_action(self::WATCHDOG_HOOK, [$this, 'watchdog']);
115	// A 5-minute interval is below the sniff's recommended floor on purpose:
116	// this is the watchdog that recovers a run whose tick was killed, and it
117	// unschedules itself as soon as nothing is outstanding.
118	// phpcs:ignore WordPress.WP.CronInterval.ChangeDetected, WordPress.WP.CronInterval.CronSchedulesInterval
119	add_filter('cron_schedules', [self::class, 'add_cron_interval']);
120	}
121
122	/**
123	* Register the watchdog's five-minute interval.
124	*
125	* @param array $schedules Registered cron schedules.
126	* @return array
127	*/
128	public static function add_cron_interval(array $schedules): array {
129	$schedules[self::WATCHDOG_INTERVAL] ??= [
130	'interval' => 5 * MINUTE_IN_SECONDS,
131	'display' => __('Every five minutes (ThinkRank Brand Visibility)', 'thinkrank'),
132	];
133
134	return $schedules;
135	}
136
137	/**
138	* Recover a run whose drain stopped.
139	*
140	* `tick()` reschedules itself, which is enough while ticks complete — but
141	* a tick killed by a fatal, an OOM or a worker timeout never reaches that
142	* line, and the run then sits at whatever percentage it reached forever.
143	* This recurring hook re-sweeps abandoned claims, closes runs whose tasks
144	* all resolved, and re-arms the drain. It unschedules itself once there is
145	* no outstanding work, so it costs nothing on an idle site.
146	*
147	* @return void
148	*/
149	public function watchdog(): void {
150	$this->sweep();
151	$this->finalize_completed_runs();
152
153	if ($this->has_outstanding_tasks()) {
154	$this->schedule_tick();
155	return;
156	}
157
158	self::unschedule_watchdog();
159	}
160
161	/**
162	* Arm the watchdog while a run is in flight.
163	*
164	* @return void
165	*/
166	private function schedule_watchdog(): void {
167	if (!wp_next_scheduled(self::WATCHDOG_HOOK)) {
168	wp_schedule_event(time() + (5 * MINUTE_IN_SECONDS), self::WATCHDOG_INTERVAL, self::WATCHDOG_HOOK);
169	}
170	}
171
172	/**
173	* Disarm the watchdog.
174	*
175	* @return void
176	*/
177	public static function unschedule_watchdog(): void {
178	wp_clear_scheduled_hook(self::WATCHDOG_HOOK);
179	}
180
181	/**
182	* Runs table name.
183	*
184	* @return string
185	*/
186	public static function runs_table(): string {
187	global $wpdb;
188	return $wpdb->prefix . 'thinkrank_bv_runs';
189	}
190
191	/**
192	* Tasks table name.
193	*
194	* @return string
195	*/
196	public static function tasks_table(): string {
197	global $wpdb;
198	return $wpdb->prefix . 'thinkrank_bv_tasks';
199	}
200
201	/**
202	* Expand a configuration into the flat list of probes it implies.
203	*
204	* Pure and public so the wizard's cost preview and the runner agree by
205	* construction — the number shown before spending is the number of calls
206	* actually made.
207	*
208	* @param array $config queries[{text,type}], platforms[], samples.
209	* @return array<int, array{query_text: string, query_type: string, platform: string, sample_index: int}>
210	*/
211	public static function plan_tasks(array $config): array {
212	$queries = $config['queries'] ?? [];
213	$platforms = $config['platforms'] ?? [];
214	$samples = max(1, (int) ($config['samples'] ?? 1));
215
216	$tasks = [];
217
218	foreach ($queries as $query) {
219	$text = trim((string) ($query['text'] ?? ''));
220	if ('' === $text) {
221	continue;
222	}
223	$type = (string) ($query['type'] ?? 'branded');
224	if (!in_array($type, Brand_Visibility_Scorer::QUERY_TYPES, true)) {
225	$type = 'branded';
226	}
227
228	foreach ($platforms as $platform) {
229	for ($i = 0; $i < $samples; $i++) {
230	$tasks[] = [
231	'query_text' => $text,
232	'query_type' => $type,
233	'platform' => (string) $platform,
234	'sample_index' => $i,
235	];
236	}
237	}
238	}
239
240	return $tasks;
241	}
242
243	/**
244	* Start a run: snapshot the config, fan out tasks, schedule the drain.
245	*
246	* @param array $config Full run configuration.
247	* @return int New run id.
248	* @throws \Exception When the config yields no work.
249	*/
250	public function start(array $config): int {
251	global $wpdb;
252
253	$planned = self::plan_tasks($config);
254	if (empty($planned)) {
255	throw new \Exception(esc_html__('Nothing to run: add at least one question and one AI platform.', 'thinkrank'));
256	}
257
258	// Pre-flight: repair a missing schema rather than failing with a bare
259	// "could not start" (#270). Self-healing beats an error message here —
260	// the user can't act on "a table is missing" anyway.
261	$this->ensure_schema();
262
263	$wpdb->insert(
264	self::runs_table(),
265	[
266	'status' => 'running',
267	'started_at' => current_time('mysql'),
268	'tasks_total' => count($planned),
269	'tasks_done' => 0,
270	'config' => wp_json_encode($config),
271	],
272	['%s', '%s', '%d', '%d', '%s']
273	);
274
275	$run_id = (int) $wpdb->insert_id;
276	if ($run_id <= 0) {
277	throw new \Exception(esc_html__('Could not start the analysis run.', 'thinkrank'));
278	}
279
280	foreach ($planned as $task) {
281	$wpdb->insert(
282	self::tasks_table(),
283	[
284	'run_id' => $run_id,
285	'query_text' => $task['query_text'],
286	'query_type' => $task['query_type'],
287	'platform' => $task['platform'],
288	'sample_index' => $task['sample_index'],
289	'status' => 'pending',
290	'updated_at' => current_time('mysql'),
291	],
292	['%d', '%s', '%s', '%s', '%d', '%s', '%s']
293	);
294	}
295
296	$this->schedule_tick();
297	$this->schedule_watchdog();
298
299	return $run_id;
300	}
301
302	/**
303	* Make sure this feature's tables exist before a run depends on them.
304	*
305	* Normally the schema upgrade path has already handled it; this is the
306	* backstop for an install whose stored db_version matched while a table
307	* was absent — the exact state that made Brand Visibility dead on arrival
308	* in #270. dbDelta creates only what's missing, so re-running is cheap and
309	* safe.
310	*
311	* @since 1.28.0
312	*
313	* @return void
314	* @throws \Exception When the tables still cannot be created.
315	*/
316	private function ensure_schema(): void {
317	global $wpdb;
318
319	$table = self::runs_table();
320
321	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-off existence probe before a run.
322	if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table))) {
323	return;
324	}
325
326	(new \ThinkRank\Database\Database_Schema())->create_tables();
327
328	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- verify the repair worked.
329	if (!$wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table))) {
330	throw new \Exception(esc_html__(
331	'Brand Visibility storage is missing and could not be created. Please deactivate and reactivate ThinkRank, then try again.',
332	'thinkrank'
333	));
334	}
335	}
336
337	/**
338	* Process a slice of pending work, then reschedule while work remains.
339	*
340	* @return void
341	*/
342	public function tick(): void {
343	$this->sweep();
344
345	$started = microtime(true);
346	$handled = 0;
347
348	while ($handled < self::TICK_MAX_TASKS && (microtime(true) - $started) < self::TICK_BUDGET) {
349	$task = $this->claim_next_task();
350	if (null === $task) {
351	break;
352	}
353
354	$this->process_task($task);
355	$handled++;
356	}
357
358	// Finalize every run whose tasks are all resolved, then keep ticking
359	// only while work is genuinely outstanding.
360	$this->finalize_completed_runs();
361
362	if ($this->has_outstanding_tasks()) {
363	$this->schedule_tick();
364	return;
365	}
366
367	// Nothing left to drain — stop the recurring safety net.
368	self::unschedule_watchdog();
369	}
370
371	/**
372	* Return long-`running` tasks to the pool.
373	*
374	* A worker killed mid-probe would otherwise leave its task claimed
375	* forever, stalling the run at 97%.
376	*
377	* @return void
378	*/
379	public function sweep(): void {
380	global $wpdb;
381
382	// `updated_at` is written with current_time('mysql') — site-local, not
383	// UTC — so the cutoff must be site-local too. Comparing a UTC cutoff
384	// against local timestamps skews the sweep by the gmt_offset: east of
385	// UTC nothing is ever swept (a killed worker stalls the run forever),
386	// west of it live tasks get yanked back to `pending` mid-probe.
387	$cutoff = $this->local_time_ago(self::STUCK_AFTER_MINUTES * MINUTE_IN_SECONDS);
388	$table = self::tasks_table();
389
390	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- maintenance sweep on our own table.
391	$wpdb->query($wpdb->prepare(
392	"UPDATE `{$table}` SET status = 'pending' WHERE status = 'running' AND updated_at < %s",
393	$cutoff
394	));
395	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
396	}
397
398	/**
399	* Claim the next pending task (marking it `running` so parallel ticks
400	* can't both take it).
401	*
402	* @return array\|null Task row, or null when the queue is drained.
403	*/
404	private function claim_next_task(): ?array {
405	global $wpdb;
406
407	$table = self::tasks_table();
408
409	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- queue read on our own table.
410	$task = $wpdb->get_row("SELECT * FROM `{$table}` WHERE status = 'pending' ORDER BY id ASC LIMIT 1", ARRAY_A);
411
412	if (!$task) {
413	return null;
414	}
415
416	// Conditional UPDATE is the claim: if another tick got there first the
417	// affected-row count is 0 and we simply skip this one.
418	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- claim on our own table.
419	$claimed = $wpdb->query($wpdb->prepare(
420	"UPDATE `{$table}` SET status = 'running', attempts = attempts + 1, updated_at = %s WHERE id = %d AND status = 'pending'",
421	current_time('mysql'),
422	(int) $task['id']
423	));
424	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
425
426	return $claimed ? $task : null;
427	}
428
429	/**
430	* Run one probe and record its verdict.
431	*
432	* @param array $task Task row.
433	* @return void
434	*
435	* @throws \Exception On failure.
436	*/
437	private function process_task(array $task): void {
438	global $wpdb;
439
440	$run = $this->get_run((int) $task['run_id']);
441	$config = is_array($run['config'] ?? null) ? $run['config'] : [];
442
443	$brand = (string) ($config['brand'] ?? get_bloginfo('name'));
444	$variants = (array) ($config['variants'] ?? []);
445	$competitors = (array) ($config['competitors'] ?? []);
446	$host = (string) wp_parse_url(home_url(), PHP_URL_HOST);
447
448	try {
449	$client = $this->providers->client_for((string) $task['platform']);
450	$answer = $this->ask($client, (string) $task['query_text'], (string) $task['platform']);
451
452	if ('' === trim($answer)) {
453	throw new \Exception(esc_html__('The AI returned an empty answer.', 'thinkrank'));
454	}
455
456	$mentioned = Brand_Visibility_Scorer::is_mentioned($answer, $brand, $variants);
457	$sentiment = '';
458
459	// Sentiment costs an extra call, so only ask when the brand was
460	// actually named — an answer that ignored you has no opinion.
461	if ($mentioned && Plan_Config::can('brand_sentiment', 'ai_visibility')) {
462	$sentiment = $this->classify_sentiment($client, $answer, $brand);
463	}
464
465	$this->update_task((int) $task['id'], [
466	'status' => 'done',
467	'mentioned' => $mentioned ? 1 : 0,
468	'cited' => Brand_Visibility_Scorer::is_cited($answer, $host) ? 1 : 0,
469	'sentiment' => $sentiment,
470	'competitors' => wp_json_encode(Brand_Visibility_Scorer::competitors_in($answer, $competitors)),
471	'excerpt' => Brand_Visibility_Scorer::excerpt($answer, $brand, $variants),
472	'answer' => substr($answer, 0, 20000),
473	'error' => '',
474	'updated_at' => current_time('mysql'),
475	]);
476
477	$this->bump_run((int) $task['run_id'], 'tasks_done');
478	} catch (\Throwable $e) {
479	$retryable = (int) $task['attempts'] < self::MAX_ATTEMPTS;
480
481	$this->update_task((int) $task['id'], [
482	'status' => $retryable ? 'pending' : 'failed',
483	'error' => substr($e->getMessage(), 0, 500),
484	'updated_at' => current_time('mysql'),
485	]);
486
487	if (!$retryable) {
488	$this->bump_run((int) $task['run_id'], 'tasks_failed');
489	}
490	}
491	}
492
493	/**
494	* Ask one platform one question.
495	*
496	* @param object $client Provider client.
497	* @param string $question User-style question.
498	* @param string $platform Platform slug (for provider-specific options).
499	* @return string Answer text.
500	*/
501	private function ask($client, string $question, string $platform): string {
502	// A user-style question, not an SEO prompt: the point is to observe an
503	// ordinary answer, exactly as a shopper would receive it.
504	$prompt = sprintf(
505	"Answer the following question the way you would answer any user. Be specific and name relevant products, companies or websites where appropriate.\n\nQuestion: %s",
506	$question
507	);
508
509	$options = [
510	'max_tokens' => self::ANSWER_TOKENS,
511	'temperature' => 0.4,
512	];
513
514	// GPT-5 reasoning models will otherwise spend the whole budget on
515	// hidden reasoning and return empty text (see 6094d9d).
516	if ('chatgpt' === $platform) {
517	$options['reasoning_effort'] = 'minimal';
518	}
519
520	$response = $client->generate_completion($prompt, $options);
521
522	return $this->extract_text($response);
523	}
524
525	/**
526	* One-word sentiment of an answer toward the brand.
527	*
528	* @param object $client Provider client.
529	* @param string $answer Answer text.
530	* @param string $brand Brand name.
531	* @return string positive\|neutral\|negative, or '' when unclear.
532	*/
533	private function classify_sentiment($client, string $answer, string $brand): string {
534	try {
535	$prompt = sprintf(
536	"Classify the sentiment toward \"%s\" in the text below. Reply with exactly one word: positive, neutral, or negative.\n\nText:\n%s",
537	$brand,
538	substr($answer, 0, 4000)
539	);
540
541	$verdict = strtolower(trim($this->extract_text(
542	$client->generate_completion($prompt, ['max_tokens' => 2000, 'temperature' => 0])
543	)));
544
545	foreach (['positive', 'negative', 'neutral'] as $mood) {
546	if (false !== strpos($verdict, $mood)) {
547	return $mood;
548	}
549	}
550	} catch (\Throwable $e) {
551	// Sentiment is a nice-to-have; never fail a probe over it.
552	return '';
553	}
554
555	return '';
556	}
557
558	/**
559	* Pull assistant text out of any supported provider response shape.
560	*
561	* @param array $response Decoded provider response.
562	* @return string
563	*/
564	private function extract_text(array $response): string {
565	if (isset($response['choices'][0]['message']['content'])) {
566	$content = $response['choices'][0]['message']['content'];
567	if (is_array($content)) {
568	return implode(' ', array_map(
569	static fn($part) => is_array($part) ? (string) ($part['text'] ?? '') : (string) $part,
570	$content
571	));
572	}
573	return (string) $content;
574	}
575
576	if (isset($response['content'][0]['text'])) {
577	return (string) $response['content'][0]['text'];
578	}
579
580	if (isset($response['candidates'][0]['content']['parts'][0]['text'])) {
581	return (string) $response['candidates'][0]['content']['parts'][0]['text'];
582	}
583
584	if (isset($response['content']) && is_string($response['content'])) {
585	return $response['content'];
586	}
587
588	return '';
589	}
590
591	/**
592	* Aggregate and close any run whose tasks are all resolved.
593	*
594	* @return void
595	*/
596	public function finalize_completed_runs(): void {
597	global $wpdb;
598
599	$runs = self::runs_table();
600	$tasks = self::tasks_table();
601
602	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own tables.
603	$ids = $wpdb->get_col(
604	"SELECT r.id FROM `{$runs}` r
605	WHERE r.status = 'running'
606	AND NOT EXISTS (
607	SELECT 1 FROM `{$tasks}` t
608	WHERE t.run_id = r.id AND t.status IN ('pending', 'running')
609	)"
610	);
611	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
612
613	foreach ((array) $ids as $id) {
614	$this->finalize((int) $id);
615	}
616	}
617
618	/**
619	* Compute and store a run's aggregates.
620	*
621	* @param int $run_id Run id.
622	* @return void
623	*/
624	public function finalize(int $run_id): void {
625	global $wpdb;
626
627	$run = $this->get_run($run_id);
628	$config = is_array($run['config'] ?? null) ? $run['config'] : [];
629	$tasks = $this->get_tasks($run_id);
630	$results = Brand_Visibility_Scorer::aggregate($tasks, $config);
631
632	$done = count(array_filter($tasks, static fn(array $t): bool => 'done' === $t['status']));
633	$failed = count(array_filter($tasks, static fn(array $t): bool => 'failed' === $t['status']));
634
635	// A run where every probe failed is a FAILED run, not a run reporting
636	// zero visibility — the distinction v1 got wrong.
637	$status = (0 === $done && $failed > 0) ? 'failed' : 'complete';
638
639	$wpdb->update(
640	self::runs_table(),
641	[
642	'status' => $status,
643	'finished_at' => current_time('mysql'),
644	'tasks_done' => $done,
645	'tasks_failed' => $failed,
646	'results' => wp_json_encode($results),
647	'error' => 'failed' === $status
648	? (string) ($tasks[0]['error'] ?? __('Every probe failed.', 'thinkrank'))
649	: '',
650	],
651	['id' => $run_id],
652	['%s', '%s', '%d', '%d', '%s', '%s'],
653	['%d']
654	);
655	}
656
657	/**
658	* A run with its config/results decoded.
659	*
660	* @param int $run_id Run id.
661	* @return array
662	*/
663	public function get_run(int $run_id): array {
664	global $wpdb;
665
666	$table = self::runs_table();
667
668	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
669	$row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$table}` WHERE id = %d", $run_id), ARRAY_A);
670
671	if (!$row) {
672	return [];
673	}
674
675	$row['config'] = json_decode((string) $row['config'], true) ?: [];
676	$row['results'] = json_decode((string) $row['results'], true) ?: [];
677
678	return $row;
679	}
680
681	/**
682	* Task rows for a run.
683	*
684	* @param int $run_id Run id.
685	* @return array
686	*/
687	public function get_tasks(int $run_id): array {
688	global $wpdb;
689
690	$table = self::tasks_table();
691
692	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
693	return (array) $wpdb->get_results(
694	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement.
695	$wpdb->prepare("SELECT * FROM `{$table}` WHERE run_id = %d ORDER BY id ASC", $run_id),
696	ARRAY_A
697	);
698	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
699	}
700
701	/**
702	* Recent runs, newest first (feeds the over-time chart).
703	*
704	* @param int $limit Max runs.
705	* @return array
706	*/
707	public function recent_runs(int $limit = 20): array {
708	global $wpdb;
709
710	$table = self::runs_table();
711	$limit = max(1, min(100, $limit));
712
713	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
714	$rows = (array) $wpdb->get_results(
715	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement.
716	$wpdb->prepare("SELECT id, status, started_at, finished_at, tasks_total, tasks_done, tasks_failed, results FROM `{$table}` ORDER BY id DESC LIMIT %d", $limit),
717	ARRAY_A
718	);
719	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
720
721	return array_map(static function (array $row): array {
722	$row['results'] = json_decode((string) $row['results'], true) ?: [];
723	return $row;
724	}, $rows);
725	}
726
727	/**
728	* Whether any run still has work outstanding.
729	*
730	* @return bool
731	*/
732	private function has_outstanding_tasks(): bool {
733	global $wpdb;
734
735	$table = self::tasks_table();
736
737	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
738	return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}` WHERE status IN ('pending','running')") > 0;
739	}
740
741	/**
742	* Schedule the next drain if one isn't already due.
743	*
744	* @return void
745	*/
746	private function schedule_tick(): void {
747	if (!wp_next_scheduled(self::TICK_HOOK)) {
748	wp_schedule_single_event(time() + 5, self::TICK_HOOK);
749	}
750
751	// Cron on a low-traffic site only fires on a visit; nudge it so a run
752	// started from the dashboard begins immediately.
753	spawn_cron();
754	}
755
756	/**
757	* Format a UTC timestamp as a site-local MySQL datetime.
758	*
759	* The datetime columns on this feature's tables are written with
760	* current_time('mysql'), so every comparison value has to be built in the
761	* same frame. Deriving it from current_time() rather than the gmt_offset
762	* option keeps it correct under DST — that option is only re-synced when
763	* the timezone is saved. WordPress runs PHP in UTC, so parsing the local
764	* string with strtotime() and re-formatting with gmdate() round-trips the
765	* same wall clock, minus the offset asked for.
766	*
767	* @param int $offset_seconds Seconds to subtract from "now".
768	* @return string Site-local `Y-m-d H:i:s`.
769	*/
770	private function local_time_ago(int $offset_seconds): string {
771	return gmdate('Y-m-d H:i:s', strtotime(current_time('mysql')) - $offset_seconds);
772	}
773
774	/**
775	* Update a task row.
776	*
777	* @param int $task_id Task id.
778	* @param array $data Column => value.
779	* @return void
780	*/
781	private function update_task(int $task_id, array $data): void {
782	global $wpdb;
783
784	$wpdb->update(self::tasks_table(), $data, ['id' => $task_id], null, ['%d']);
785	}
786
787	/**
788	* Increment a run counter.
789	*
790	* @param int $run_id Run id.
791	* @param string $column tasks_done\|tasks_failed.
792	* @return void
793	*/
794	private function bump_run(int $run_id, string $column): void {
795	global $wpdb;
796
797	if (!in_array($column, ['tasks_done', 'tasks_failed'], true)) {
798	return;
799	}
800
801	$table = self::runs_table();
802
803	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- counter bump on our own table; column whitelisted above.
804	$wpdb->query($wpdb->prepare(
805	"UPDATE `{$table}` SET {$column} = {$column} + 1 WHERE id = %d",
806	$run_id
807	));
808	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
809	}
810	}
811