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 2.0.0, at includes/ai/class-brand-visibility-runner.php

912 lines 32.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	* Days a finished run keeps its individual task rows.
84	*
85	* Every task stores the full AI answer (up to 20,000 chars), and a run is
86	* queries × platforms × samples rows — 144 on a 12-query Pro setup. Nothing
87	* ever deleted them, so scheduled runs would grow the table without bound
88	* for the sake of transcripts nobody reads once a run is months old (#302).
89	* The run row itself is never pruned: `bv_runs.results` holds the
90	* aggregates, which is all the trend chart needs.
91	*
92	* @since 1.30.0
93	*/
94	public const TASK_RETENTION_DAYS = 90;
95
96	/**
97	* Settings accessor.
98	*
99	* @var Settings
100	*/
101	private Settings $settings;
102
103	/**
104	* Provider resolver.
105	*
106	* @var Brand_Visibility_Providers
107	*/
108	private Brand_Visibility_Providers $providers;
109
110	/**
111	* Constructor.
112	*
113	* @param Settings\|null $settings Settings.
114	* @param Brand_Visibility_Providers\|null $providers Provider resolver.
115	*/
116	public function __construct(?Settings $settings = null, ?Brand_Visibility_Providers $providers = null) {
117	$this->settings = $settings ?? Settings::instance();
118	$this->providers = $providers ?? new Brand_Visibility_Providers($this->settings);
119	}
120
121	/**
122	* Register the cron listener.
123	*
124	* @return void
125	*/
126	public function init(): void {
127	add_action(self::TICK_HOOK, [$this, 'tick']);
128	add_action(self::WATCHDOG_HOOK, [$this, 'watchdog']);
129	// A 5-minute interval is below the sniff's recommended floor on purpose:
130	// this is the watchdog that recovers a run whose tick was killed, and it
131	// unschedules itself as soon as nothing is outstanding.
132	// phpcs:ignore WordPress.WP.CronInterval.ChangeDetected, WordPress.WP.CronInterval.CronSchedulesInterval
133	add_filter('cron_schedules', [self::class, 'add_cron_interval']);
134	}
135
136	/**
137	* Register the watchdog's five-minute interval.
138	*
139	* @param array $schedules Registered cron schedules.
140	* @return array
141	*/
142	public static function add_cron_interval(array $schedules): array {
143	// `cron_schedules` fires from wp_get_schedules(), which any caller can
144	// reach before `init` — wp_schedule_event() at plugin boot does exactly
145	// that. Translating there loads the text domain too early and trips the
146	// _load_textdomain_just_in_time notice on WP 6.7+, so only translate
147	// once `init` has run. Schedules are rebuilt per call, so later reads
148	// still get the translated label.
149	$schedules[self::WATCHDOG_INTERVAL] ??= [
150	'interval' => 5 * MINUTE_IN_SECONDS,
151	'display' => did_action('init')
152	? __('Every five minutes (ThinkRank Brand Visibility)', 'thinkrank')
153	: 'Every five minutes (ThinkRank Brand Visibility)',
154	];
155
156	return $schedules;
157	}
158
159	/**
160	* Recover a run whose drain stopped.
161	*
162	* `tick()` reschedules itself, which is enough while ticks complete — but
163	* a tick killed by a fatal, an OOM or a worker timeout never reaches that
164	* line, and the run then sits at whatever percentage it reached forever.
165	* This recurring hook re-sweeps abandoned claims, closes runs whose tasks
166	* all resolved, and re-arms the drain. It unschedules itself once there is
167	* no outstanding work, so it costs nothing on an idle site.
168	*
169	* @return void
170	*/
171	public function watchdog(): void {
172	$this->sweep();
173	$this->finalize_completed_runs();
174
175	if ($this->has_outstanding_tasks()) {
176	$this->schedule_tick();
177	return;
178	}
179
180	self::unschedule_watchdog();
181	}
182
183	/**
184	* Arm the watchdog while a run is in flight.
185	*
186	* @return void
187	*/
188	private function schedule_watchdog(): void {
189	if (!wp_next_scheduled(self::WATCHDOG_HOOK)) {
190	wp_schedule_event(time() + (5 * MINUTE_IN_SECONDS), self::WATCHDOG_INTERVAL, self::WATCHDOG_HOOK);
191	}
192	}
193
194	/**
195	* Disarm the watchdog.
196	*
197	* @return void
198	*/
199	public static function unschedule_watchdog(): void {
200	wp_clear_scheduled_hook(self::WATCHDOG_HOOK);
201	}
202
203	/**
204	* Runs table name.
205	*
206	* @return string
207	*/
208	public static function runs_table(): string {
209	global $wpdb;
210	return $wpdb->prefix . 'thinkrank_bv_runs';
211	}
212
213	/**
214	* Tasks table name.
215	*
216	* @return string
217	*/
218	public static function tasks_table(): string {
219	global $wpdb;
220	return $wpdb->prefix . 'thinkrank_bv_tasks';
221	}
222
223	/**
224	* Expand a configuration into the flat list of probes it implies.
225	*
226	* Pure and public so the wizard's cost preview and the runner agree by
227	* construction — the number shown before spending is the number of calls
228	* actually made.
229	*
230	* @param array $config queries[{text,type}], platforms[], samples.
231	* @return array<int, array{query_text: string, query_type: string, platform: string, sample_index: int}>
232	*/
233	public static function plan_tasks(array $config): array {
234	$queries = $config['queries'] ?? [];
235	$platforms = $config['platforms'] ?? [];
236	$samples = max(1, (int) ($config['samples'] ?? 1));
237
238	$tasks = [];
239
240	foreach ($queries as $query) {
241	$text = trim((string) ($query['text'] ?? ''));
242	if ('' === $text) {
243	continue;
244	}
245	$type = (string) ($query['type'] ?? 'branded');
246	if (!in_array($type, Brand_Visibility_Scorer::QUERY_TYPES, true)) {
247	$type = 'branded';
248	}
249
250	foreach ($platforms as $platform) {
251	for ($i = 0; $i < $samples; $i++) {
252	$tasks[] = [
253	'query_text' => $text,
254	'query_type' => $type,
255	'platform' => (string) $platform,
256	'sample_index' => $i,
257	];
258	}
259	}
260	}
261
262	return $tasks;
263	}
264
265	/**
266	* Start a run: snapshot the config, fan out tasks, schedule the drain.
267	*
268	* @param array $config Full run configuration.
269	* @return int New run id.
270	* @throws \Exception When the config yields no work.
271	*/
272	public function start(array $config): int {
273	global $wpdb;
274
275	$planned = self::plan_tasks($config);
276	if (empty($planned)) {
277	throw new \Exception(esc_html__('Nothing to run: add at least one question and one AI platform.', 'thinkrank'));
278	}
279
280	// Pre-flight: repair a missing schema rather than failing with a bare
281	// "could not start" (#270). Self-healing beats an error message here —
282	// the user can't act on "a table is missing" anyway.
283	$this->ensure_schema();
284
285	$wpdb->insert(
286	self::runs_table(),
287	[
288	'status' => 'running',
289	'started_at' => current_time('mysql'),
290	'tasks_total' => count($planned),
291	'tasks_done' => 0,
292	'config' => wp_json_encode($config),
293	],
294	['%s', '%s', '%d', '%d', '%s']
295	);
296
297	$run_id = (int) $wpdb->insert_id;
298	if ($run_id <= 0) {
299	throw new \Exception(esc_html__('Could not start the analysis run.', 'thinkrank'));
300	}
301
302	foreach ($planned as $task) {
303	$wpdb->insert(
304	self::tasks_table(),
305	[
306	'run_id' => $run_id,
307	'query_text' => $task['query_text'],
308	'query_type' => $task['query_type'],
309	'platform' => $task['platform'],
310	'sample_index' => $task['sample_index'],
311	'status' => 'pending',
312	'updated_at' => current_time('mysql'),
313	],
314	['%d', '%s', '%s', '%s', '%d', '%s', '%s']
315	);
316	}
317
318	$this->schedule_tick();
319	$this->schedule_watchdog();
320
321	return $run_id;
322	}
323
324	/**
325	* Make sure this feature's tables exist before a run depends on them.
326	*
327	* Normally the schema upgrade path has already handled it; this is the
328	* backstop for an install whose stored db_version matched while a table
329	* was absent — the exact state that made Brand Visibility dead on arrival
330	* in #270. dbDelta creates only what's missing, so re-running is cheap and
331	* safe.
332	*
333	* @since 1.28.0
334	*
335	* @return void
336	* @throws \Exception When the tables still cannot be created.
337	*/
338	private function ensure_schema(): void {
339	global $wpdb;
340
341	$table = self::runs_table();
342
343	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- one-off existence probe before a run.
344	if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table))) {
345	return;
346	}
347
348	(new \ThinkRank\Database\Database_Schema())->create_tables();
349
350	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- verify the repair worked.
351	if (!$wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table))) {
352	throw new \Exception(esc_html__(
353	'Brand Visibility storage is missing and could not be created. Please deactivate and reactivate ThinkRank, then try again.',
354	'thinkrank'
355	));
356	}
357	}
358
359	/**
360	* Process a slice of pending work, then reschedule while work remains.
361	*
362	* @return void
363	*/
364	public function tick(): void {
365	$this->sweep();
366
367	$started = microtime(true);
368	$handled = 0;
369
370	while ($handled < self::TICK_MAX_TASKS && (microtime(true) - $started) < self::TICK_BUDGET) {
371	$task = $this->claim_next_task();
372	if (null === $task) {
373	break;
374	}
375
376	$this->process_task($task);
377	$handled++;
378	}
379
380	// Finalize every run whose tasks are all resolved, then keep ticking
381	// only while work is genuinely outstanding.
382	$this->finalize_completed_runs();
383
384	if ($this->has_outstanding_tasks()) {
385	$this->schedule_tick();
386	return;
387	}
388
389	// Nothing left to drain — stop the recurring safety net.
390	self::unschedule_watchdog();
391	}
392
393	/**
394	* Return long-`running` tasks to the pool.
395	*
396	* A worker killed mid-probe would otherwise leave its task claimed
397	* forever, stalling the run at 97%.
398	*
399	* @return void
400	*/
401	public function sweep(): void {
402	global $wpdb;
403
404	// `updated_at` is written with current_time('mysql') — site-local, not
405	// UTC — so the cutoff must be site-local too. Comparing a UTC cutoff
406	// against local timestamps skews the sweep by the gmt_offset: east of
407	// UTC nothing is ever swept (a killed worker stalls the run forever),
408	// west of it live tasks get yanked back to `pending` mid-probe.
409	$cutoff = $this->local_time_ago(self::STUCK_AFTER_MINUTES * MINUTE_IN_SECONDS);
410	$table = self::tasks_table();
411
412	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- maintenance sweep on our own table.
413	$wpdb->query($wpdb->prepare(
414	"UPDATE `{$table}` SET status = 'pending' WHERE status = 'running' AND updated_at < %s",
415	$cutoff
416	));
417	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
418	}
419
420	/**
421	* Claim the next pending task (marking it `running` so parallel ticks
422	* can't both take it).
423	*
424	* @return array\|null Task row, or null when the queue is drained.
425	*/
426	private function claim_next_task(): ?array {
427	global $wpdb;
428
429	$table = self::tasks_table();
430
431	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- queue read on our own table.
432	$task = $wpdb->get_row("SELECT * FROM `{$table}` WHERE status = 'pending' ORDER BY id ASC LIMIT 1", ARRAY_A);
433
434	if (!$task) {
435	return null;
436	}
437
438	// Conditional UPDATE is the claim: if another tick got there first the
439	// affected-row count is 0 and we simply skip this one.
440	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- claim on our own table.
441	$claimed = $wpdb->query($wpdb->prepare(
442	"UPDATE `{$table}` SET status = 'running', attempts = attempts + 1, updated_at = %s WHERE id = %d AND status = 'pending'",
443	current_time('mysql'),
444	(int) $task['id']
445	));
446	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
447
448	return $claimed ? $task : null;
449	}
450
451	/**
452	* Run one probe and record its verdict.
453	*
454	* @param array $task Task row.
455	* @return void
456	*
457	* @throws \Exception On failure.
458	*/
459	private function process_task(array $task): void {
460	global $wpdb;
461
462	$run = $this->get_run((int) $task['run_id']);
463	$config = is_array($run['config'] ?? null) ? $run['config'] : [];
464
465	$brand = (string) ($config['brand'] ?? get_bloginfo('name'));
466	$variants = (array) ($config['variants'] ?? []);
467	$competitors = (array) ($config['competitors'] ?? []);
468	$host = (string) wp_parse_url(home_url(), PHP_URL_HOST);
469
470	try {
471	$client = $this->providers->client_for((string) $task['platform']);
472	$answer = $this->ask($client, (string) $task['query_text'], (string) $task['platform']);
473
474	if ('' === trim($answer)) {
475	throw new \Exception(esc_html__('The AI returned an empty answer.', 'thinkrank'));
476	}
477
478	$mentioned = Brand_Visibility_Scorer::is_mentioned($answer, $brand, $variants);
479	$sentiment = '';
480
481	// Sentiment costs an extra call, so only ask when the brand was
482	// actually named — an answer that ignored you has no opinion.
483	if ($mentioned && Plan_Config::can('brand_sentiment', 'ai_visibility')) {
484	$sentiment = $this->classify_sentiment($client, $answer, $brand);
485	}
486
487	$this->update_task((int) $task['id'], [
488	'status' => 'done',
489	'mentioned' => $mentioned ? 1 : 0,
490	'cited' => Brand_Visibility_Scorer::is_cited($answer, $host) ? 1 : 0,
491	'sentiment' => $sentiment,
492	'competitors' => wp_json_encode(Brand_Visibility_Scorer::competitors_in($answer, $competitors)),
493	'excerpt' => Brand_Visibility_Scorer::excerpt($answer, $brand, $variants),
494	'answer' => substr($answer, 0, 20000),
495	'error' => '',
496	'updated_at' => current_time('mysql'),
497	]);
498
499	$this->bump_run((int) $task['run_id'], 'tasks_done');
500	} catch (\Throwable $e) {
501	$retryable = (int) $task['attempts'] < self::MAX_ATTEMPTS;
502
503	$this->update_task((int) $task['id'], [
504	'status' => $retryable ? 'pending' : 'failed',
505	'error' => substr($e->getMessage(), 0, 500),
506	'updated_at' => current_time('mysql'),
507	]);
508
509	if (!$retryable) {
510	$this->bump_run((int) $task['run_id'], 'tasks_failed');
511	}
512	}
513	}
514
515	/**
516	* Ask one platform one question.
517	*
518	* @param object $client Provider client.
519	* @param string $question User-style question.
520	* @param string $platform Platform slug (for provider-specific options).
521	* @return string Answer text.
522	*/
523	private function ask($client, string $question, string $platform): string {
524	// A user-style question, not an SEO prompt: the point is to observe an
525	// ordinary answer, exactly as a shopper would receive it.
526	$prompt = sprintf(
527	"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",
528	$question
529	);
530
531	$options = [
532	'max_tokens' => self::ANSWER_TOKENS,
533	'temperature' => 0.4,
534	];
535
536	// GPT-5 reasoning models will otherwise spend the whole budget on
537	// hidden reasoning and return empty text (see 6094d9d).
538	if ('chatgpt' === $platform) {
539	$options['reasoning_effort'] = 'minimal';
540	}
541
542	$response = $client->generate_completion($prompt, $options);
543
544	return $this->extract_text($response);
545	}
546
547	/**
548	* One-word sentiment of an answer toward the brand.
549	*
550	* @param object $client Provider client.
551	* @param string $answer Answer text.
552	* @param string $brand Brand name.
553	* @return string positive\|neutral\|negative, or '' when unclear.
554	*/
555	private function classify_sentiment($client, string $answer, string $brand): string {
556	try {
557	$prompt = sprintf(
558	"Classify the sentiment toward \"%s\" in the text below. Reply with exactly one word: positive, neutral, or negative.\n\nText:\n%s",
559	$brand,
560	substr($answer, 0, 4000)
561	);
562
563	$verdict = strtolower(trim($this->extract_text(
564	$client->generate_completion($prompt, ['max_tokens' => 2000, 'temperature' => 0])
565	)));
566
567	foreach (['positive', 'negative', 'neutral'] as $mood) {
568	if (false !== strpos($verdict, $mood)) {
569	return $mood;
570	}
571	}
572	} catch (\Throwable $e) {
573	// Sentiment is a nice-to-have; never fail a probe over it.
574	return '';
575	}
576
577	return '';
578	}
579
580	/**
581	* Pull assistant text out of any supported provider response shape.
582	*
583	* @param array $response Decoded provider response.
584	* @return string
585	*/
586	private function extract_text(array $response): string {
587	if (isset($response['choices'][0]['message']['content'])) {
588	$content = $response['choices'][0]['message']['content'];
589	if (is_array($content)) {
590	return implode(' ', array_map(
591	static fn($part) => is_array($part) ? (string) ($part['text'] ?? '') : (string) $part,
592	$content
593	));
594	}
595	return (string) $content;
596	}
597
598	if (isset($response['content'][0]['text'])) {
599	return (string) $response['content'][0]['text'];
600	}
601
602	if (isset($response['candidates'][0]['content']['parts'][0]['text'])) {
603	return (string) $response['candidates'][0]['content']['parts'][0]['text'];
604	}
605
606	if (isset($response['content']) && is_string($response['content'])) {
607	return $response['content'];
608	}
609
610	return '';
611	}
612
613	/**
614	* Aggregate and close any run whose tasks are all resolved.
615	*
616	* @return void
617	*/
618	public function finalize_completed_runs(): void {
619	global $wpdb;
620
621	$runs = self::runs_table();
622	$tasks = self::tasks_table();
623
624	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own tables.
625	$ids = $wpdb->get_col(
626	"SELECT r.id FROM `{$runs}` r
627	WHERE r.status = 'running'
628	AND NOT EXISTS (
629	SELECT 1 FROM `{$tasks}` t
630	WHERE t.run_id = r.id AND t.status IN ('pending', 'running')
631	)"
632	);
633	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
634
635	foreach ((array) $ids as $id) {
636	$this->finalize((int) $id);
637	}
638
639	// Runs have just been aggregated, so this is the natural moment to age
640	// out the transcripts of long-finished ones — no extra cron needed.
641	// Once per tick, not once per run: a tick closing N runs would
642	// otherwise issue the same multi-table DELETE N times.
643	if (!empty($ids)) {
644	$this->prune_finished_run_tasks();
645	}
646	}
647
648	/**
649	* Compute and store a run's aggregates.
650	*
651	* @param int $run_id Run id.
652	* @return void
653	*/
654	public function finalize(int $run_id): void {
655	global $wpdb;
656
657	$run = $this->get_run($run_id);
658	$config = is_array($run['config'] ?? null) ? $run['config'] : [];
659	$tasks = $this->get_tasks($run_id);
660	$results = Brand_Visibility_Scorer::aggregate($tasks, $config);
661
662	$done = count(array_filter($tasks, static fn(array $t): bool => 'done' === $t['status']));
663	$failed = count(array_filter($tasks, static fn(array $t): bool => 'failed' === $t['status']));
664
665	// A run where every probe failed is a FAILED run, not a run reporting
666	// zero visibility — the distinction v1 got wrong.
667	$status = (0 === $done && $failed > 0) ? 'failed' : 'complete';
668
669	$wpdb->update(
670	self::runs_table(),
671	[
672	'status' => $status,
673	'finished_at' => current_time('mysql'),
674	'tasks_done' => $done,
675	'tasks_failed' => $failed,
676	'results' => wp_json_encode($results),
677	'error' => 'failed' === $status
678	? (string) ($tasks[0]['error'] ?? __('Every probe failed.', 'thinkrank'))
679	: '',
680	],
681	['id' => $run_id],
682	['%s', '%s', '%d', '%d', '%s', '%s'],
683	['%d']
684	);
685	}
686
687	/**
688	* Days a finished run keeps its task rows.
689	*
690	* Filterable so a site that wants longer transcripts (or none at all) can
691	* say so; 0 or less disables pruning entirely.
692	*
693	* @since 1.30.0
694	*
695	* @return int Retention window in days.
696	*/
697	public static function task_retention_days(): int {
698	/**
699	* Filters how long Brand Visibility keeps per-probe task rows.
700	*
701	* @since 1.30.0
702	*
703	* @param int $days Retention window in days. 0 or less keeps everything.
704	*/
705	return (int) apply_filters('thinkrank_bv_task_retention_days', self::TASK_RETENTION_DAYS);
706	}
707
708	/**
709	* Drop task rows belonging to runs that finished outside the retention
710	* window, keeping every run's aggregates.
711	*
712	* The most recent finished run always keeps its tasks however old it is:
713	* on a site that ran Brand Visibility once and then stopped, the transcript
714	* view must not empty itself out just because time passed.
715	*
716	* @since 1.30.0
717	*
718	* @return int Rows deleted.
719	*/
720	public function prune_finished_run_tasks(): int {
721	global $wpdb;
722
723	$days = self::task_retention_days();
724	if ($days <= 0) {
725	return 0;
726	}
727
728	$runs = self::runs_table();
729	$tasks = self::tasks_table();
730
731	// finished_at is written with current_time('mysql'), i.e. site-local,
732	// so the cutoff is built from site-local time too.
733	$cutoff = $this->local_time_ago($days * DAY_IN_SECONDS);
734
735	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
736	$keep = (int) $wpdb->get_var(
737	"SELECT id FROM `{$runs}`
738	WHERE status IN ('complete', 'failed') AND finished_at IS NOT NULL
739	ORDER BY finished_at DESC, id DESC
740	LIMIT 1"
741	);
742
743	$deleted = $wpdb->query($wpdb->prepare(
744	"DELETE t FROM `{$tasks}` t
745	INNER JOIN `{$runs}` r ON r.id = t.run_id
746	WHERE r.status IN ('complete', 'failed')
747	AND r.finished_at IS NOT NULL
748	AND r.finished_at < %s
749	AND r.id <> %d",
750	$cutoff,
751	$keep
752	));
753	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
754
755	return max(0, (int) $deleted);
756	}
757
758	/**
759	* A run with its config/results decoded.
760	*
761	* @param int $run_id Run id.
762	* @return array
763	*/
764	public function get_run(int $run_id): array {
765	global $wpdb;
766
767	$table = self::runs_table();
768
769	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
770	$row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$table}` WHERE id = %d", $run_id), ARRAY_A);
771
772	if (!$row) {
773	return [];
774	}
775
776	$row['config'] = json_decode((string) $row['config'], true) ?: [];
777	$row['results'] = json_decode((string) $row['results'], true) ?: [];
778
779	return $row;
780	}
781
782	/**
783	* Task rows for a run.
784	*
785	* @param int $run_id Run id.
786	* @return array
787	*/
788	public function get_tasks(int $run_id): array {
789	global $wpdb;
790
791	$table = self::tasks_table();
792
793	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
794	return (array) $wpdb->get_results(
795	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement.
796	$wpdb->prepare("SELECT * FROM `{$table}` WHERE run_id = %d ORDER BY id ASC", $run_id),
797	ARRAY_A
798	);
799	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
800	}
801
802	/**
803	* Recent runs, newest first (feeds the over-time chart).
804	*
805	* @param int $limit Max runs.
806	* @return array
807	*/
808	public function recent_runs(int $limit = 20): array {
809	global $wpdb;
810
811	$table = self::runs_table();
812	$limit = max(1, min(100, $limit));
813
814	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
815	$rows = (array) $wpdb->get_results(
816	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement.
817	$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),
818	ARRAY_A
819	);
820	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
821
822	return array_map(static function (array $row): array {
823	$row['results'] = json_decode((string) $row['results'], true) ?: [];
824	return $row;
825	}, $rows);
826	}
827
828	/**
829	* Whether any run still has work outstanding.
830	*
831	* @return bool
832	*/
833	private function has_outstanding_tasks(): bool {
834	global $wpdb;
835
836	$table = self::tasks_table();
837
838	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- read on our own table.
839	return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}` WHERE status IN ('pending','running')") > 0;
840	}
841
842	/**
843	* Schedule the next drain if one isn't already due.
844	*
845	* @return void
846	*/
847	private function schedule_tick(): void {
848	if (!wp_next_scheduled(self::TICK_HOOK)) {
849	wp_schedule_single_event(time() + 5, self::TICK_HOOK);
850	}
851
852	// Cron on a low-traffic site only fires on a visit; nudge it so a run
853	// started from the dashboard begins immediately.
854	spawn_cron();
855	}
856
857	/**
858	* Format a UTC timestamp as a site-local MySQL datetime.
859	*
860	* The datetime columns on this feature's tables are written with
861	* current_time('mysql'), so every comparison value has to be built in the
862	* same frame. Deriving it from current_time() rather than the gmt_offset
863	* option keeps it correct under DST — that option is only re-synced when
864	* the timezone is saved. WordPress runs PHP in UTC, so parsing the local
865	* string with strtotime() and re-formatting with gmdate() round-trips the
866	* same wall clock, minus the offset asked for.
867	*
868	* @param int $offset_seconds Seconds to subtract from "now".
869	* @return string Site-local `Y-m-d H:i:s`.
870	*/
871	private function local_time_ago(int $offset_seconds): string {
872	return gmdate('Y-m-d H:i:s', strtotime(current_time('mysql')) - $offset_seconds);
873	}
874
875	/**
876	* Update a task row.
877	*
878	* @param int $task_id Task id.
879	* @param array $data Column => value.
880	* @return void
881	*/
882	private function update_task(int $task_id, array $data): void {
883	global $wpdb;
884
885	$wpdb->update(self::tasks_table(), $data, ['id' => $task_id], null, ['%d']);
886	}
887
888	/**
889	* Increment a run counter.
890	*
891	* @param int $run_id Run id.
892	* @param string $column tasks_done\|tasks_failed.
893	* @return void
894	*/
895	private function bump_run(int $run_id, string $column): void {
896	global $wpdb;
897
898	if (!in_array($column, ['tasks_done', 'tasks_failed'], true)) {
899	return;
900	}
901
902	$table = self::runs_table();
903
904	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- counter bump on our own table; column whitelisted above.
905	$wpdb->query($wpdb->prepare(
906	"UPDATE `{$table}` SET {$column} = {$column} + 1 WHERE id = %d",
907	$run_id
908	));
909	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
910	}
911	}
912