fluent-cart / database / DataBackfills.php

DataBackfills.php in FluentCart A New Era of eCommerce – Faster, Lighter, and Simpler 1.6.4, at database/DataBackfills.php

746 lines 30.4 KB

Raw Download Zip

1	<?php
2
3	namespace FluentCart\Database;
4
5	use FluentCart\App\Helpers\Status;
6	use FluentCart\App\Models\Meta;
7	use FluentCart\App\Models\Order;
8	use FluentCart\App\Models\OrderItem;
9	use FluentCart\App\Models\OrderTransaction;
10	use FluentCart\App\Models\Subscription;
11	use FluentCart\App\Models\SubscriptionMeta;
12	use FluentCart\Framework\Database\Schema;
13	use FluentCart\Framework\Support\Arr;
14
15	/**
16	* One-shot data backfills / repairs. Migrators stay schema-only.
17	*
18	* Delivery: nothing runs on normal requests. While a registered backfill is
19	* pending, the admin app gets has_data_migrations = true and silently POSTs
20	* data-backfills/run (manage_options via AdminPolicy) until the server says
21	* completed. Completion is tracked per slug under the _db_migrations option
22	* (fct_meta), marked only after a backfill's FINAL chunk.
23	*
24	* Every backfill MUST have (repairInstallmentBillTimes is the reference):
25	* - a registry entry (slug => title) with ship date and removal target
26	* - bounded chunks per request with a persisted keyset cursor (resume,
27	* never restart from id 0)
28	* - batched per-chunk lookups (whereIn) — per-row queries only for rows
29	* actually being repaired
30	* - idempotent row logic; the advisory lock below; id-keyed report merge
31	*
32	* Removal: delete the runner, its registry entry, and its cursor/report options.
33	*/
34	class DataBackfills
35	{
36	/**
37	* Registered backfills: slug => ['title' => ...] (title is for logging).
38	*/
39	public static function getRegistry()
40	{
41	return [
42	// 2026-07-03 — shipped with the installment bill_times fix (PR #2194).
43	// Remove after one or two releases once affected installs have upgraded.
44	'installment_payments' => [
45	'title' => 'Installment Payments Backfill',
46	],
47	// 2026-07-22 — shipped with the completed-subscription guard in
48	// Subscription::cancelRemoteSubscription: the EOT flow used to stamp
49	// next_billing_date with the completion time on completed rows.
50	// Remove after one or two releases once affected installs have upgraded.
51	'completed_next_billing_date' => [
52	'title' => 'Completed Subscription Billing Date Cleanup',
53	],
54	// 2026-07-25 — idx_order_addresses_order_id_type has been declared in
55	// OrderAddressesMigrator::migrated() since 2026-06-06, but migrated()
56	// only runs on ACTIVATION and a WordPress in-place update never fires
57	// the activation hook, so stores that updated rather than
58	// deactivated/reactivated still have fct_order_addresses with nothing
59	// but its PRIMARY key. Delivered here instead of behind a DB-version
60	// bump: an index is not a correctness change, so it does not warrant
61	// forcing the whole version-gated block to re-run on every install.
62	// Remove after one or two releases once affected installs have upgraded.
63	'order_address_index' => [
64	'title' => 'Order Address Index',
65	],
66	];
67	}
68
69	/**
70	* @return array pending backfill slugs (registered but not completed)
71	*/
72	public static function getPending()
73	{
74	$option = (array)fluent_cart_get_option('_db_migrations', [], false);
75	$doneSlugs = array_keys(array_filter((array)Arr::get($option, 'backfills', [])));
76
77
78	return array_values(array_diff(array_keys(self::getRegistry()), $doneSlugs));
79	}
80
81	public static function hasPending()
82	{
83	return (bool)self::getPending();
84	}
85
86	/**
87	* Run pending backfills within this request's budget. Called from the
88	* data-backfills/run REST endpoint — the admin app re-calls while the
89	* returned status is 'running'.
90	*
91	* @return array ['status' => completed\|running\|locked, 'completed' => [], 'pending' => []]
92	*/
93	/**
94	* How many times the order-address index DDL may be retried before the slug
95	* retires. Small on purpose: the only failures worth retrying are transient locks,
96	* and each retry is an immediate re-post from the browser driver, not a page load.
97	*/
98	const ORDER_ADDRESS_INDEX_MAX_ATTEMPTS = 3;
99
100	public static function processPending()
101	{
102	$pending = self::getPending();
103
104	if (!$pending) {
105	return ['status' => 'completed', 'completed' => [], 'pending' => []];
106	}
107
108	if (!self::acquireBackfillLock()) {
109	// another request/tab is already on it — let that one finish
110	return ['status' => 'locked', 'completed' => [], 'pending' => $pending];
111	}
112
113	$completedNow = [];
114
115	try {
116	foreach ($pending as $slug) {
117	if (!self::runBackfill($slug)) {
118	break; // request budget spent — the next call resumes from the cursor
119	}
120
121	self::markCompleted($slug);
122	$completedNow[] = $slug;
123	}
124	} finally {
125	self::releaseBackfillLock();
126	}
127
128	$stillPending = self::getPending();
129
130	return [
131	'status' => $stillPending ? 'running' : 'completed',
132	'completed' => $completedNow,
133	'pending' => $stillPending,
134	];
135	}
136
137	/**
138	* @return bool true when the backfill finished; false = budget spent, more remains
139	*/
140	private static function runBackfill($slug)
141	{
142	if ($slug === 'installment_payments') {
143	return self::repairInstallmentBillTimes();
144	}
145
146	if ($slug === 'completed_next_billing_date') {
147	return self::clearCompletedNextBillingDates();
148	}
149
150	if ($slug === 'order_address_index') {
151	return self::ensureOrderAddressIndex();
152	}
153
154	// registered slug without a runner — mark done so it can't wedge the
155	// queue, but leave a trace since this is a programming error
156	fluent_cart_add_log(
157	'Data backfill has no runner',
158	'Backfill "' . $slug . '" is registered but has no runner. Marked completed to unblock the queue.',
159	'warning',
160	[
161	'module_name' => 'activity',
162	'module_id' => 0
163	]
164	);
165
166	return true;
167	}
168
169	private static function markCompleted($slug)
170	{
171	$option = (array)fluent_cart_get_option('_db_migrations', [], false);
172	$backfills = (array)Arr::get($option, 'backfills', []);
173	$backfills[$slug] = 'yes';
174	$option['backfills'] = $backfills;
175
176	fluent_cart_update_option('_db_migrations', $option);
177
178	$title = Arr::get(self::getRegistry(), $slug . '.title', $slug);
179
180	fluent_cart_add_log(
181	$title . ' completed',
182	'Data backfill "' . $slug . '" finished and was marked completed.',
183	'info',
184	[
185	'module_name' => 'activity',
186	'module_id' => 0
187	]
188	);
189	}
190
191	/**
192	* Apply fct_order_addresses' declared indexes on installs that never ran the
193	* activation hook.
194	*
195	* The DDL itself is NOT written here — Migrators stay the single home for
196	* schema, so this delegates to OrderAddressesMigrator::migrated(), which owns
197	* idx_order_addresses_order_id_type and whose addIndexIfNotExists is a no-op
198	* where activation already applied it. This runner only supplies the delivery
199	* the activation hook missed.
200	*
201	* No cursor: this is one DDL statement, not a row scan. MySQL builds a
202	* secondary index online (5.6+), so it does not lock the table for writes.
203	*
204	* The outcome is CHECKED, not assumed. addIndexIfNotExists() returns void and
205	* routes through $wpdb->query(), which returns false on a failed DDL rather than
206	* throwing — so a lock timeout, or a denied ALTER on a restricted grant, would
207	* otherwise let this report success and retire the slug permanently with no index
208	* and no trace.
209	*
210	* Failure is retried a BOUNDED number of times rather than by returning false
211	* indefinitely. In this queue false means "budget spent, resume me", and the
212	* browser driver in resources/admin/bootstrap/app.js re-posts immediately while the
213	* status stays 'running' — up to 100 times per page load. An unfixable failure
214	* (no ALTER grant) returned as false would therefore fire 100 doomed ALTERs and
215	* write 100 log rows on every admin page load. A few attempts are enough to ride
216	* out a transient lock; past that the slug retires with one clear warning, and the
217	* index is still declared in the migrator so a later activation re-applies it.
218	*
219	* @return bool true when the index exists, the table does not, or the attempt
220	* budget is spent; false only to earn one more retry
221	*/
222	private static function ensureOrderAddressIndex()
223	{
224	$table = Migrations\OrderAddressesMigrator::$tableName;
225
226	// Nothing to index and nothing to retry — a fresh install creates the table
227	// with the index already in getSqlSchema().
228	if (!Schema::hasTable($table)) {
229	return true;
230	}
231
232	Migrations\OrderAddressesMigrator::migrated();
233
234	if (Migrations\OrderAddressesMigrator::hasOrderIdTypeIndex()) {
235	return true;
236	}
237
238	$cursorKey = '_fluent_cart_order_address_index_attempts';
239	$attempts = (int) fluent_cart_get_option($cursorKey, 0, false) + 1;
240	fluent_cart_update_option($cursorKey, $attempts);
241
242	if ($attempts < self::ORDER_ADDRESS_INDEX_MAX_ATTEMPTS) {
243	return false;
244	}
245
246	fluent_cart_add_log(
247	'Order address index backfill gave up',
248	'Could not create ' . Migrations\OrderAddressesMigrator::ORDER_ID_TYPE_INDEX
249	. ' on ' . $table . ' after ' . $attempts . ' attempts. Check that the database'
250	. ' user has ALTER permission. Order address lookups will still work, only'
251	. ' slower; the index is re-applied on the next plugin activation. NOTE: the'
252	. ' "' . Arr::get(self::getRegistry(), 'order_address_index.title', 'Order Address Index')
253	. ' completed" entry logged straight after this one means this backfill'
254	. ' STOPPED RETRYING, not that the index was created — the shared queue logs'
255	. ' that line for every slug it retires. This warning is the real outcome.',
256	'warning',
257	[
258	'module_name' => 'activity',
259	'module_id' => 0,
260	]
261	);
262
263	return true;
264	}
265
266	/**
267	* Repair installment subscriptions whose bill_times was stored decremented
268	* by the old discount/simulated-trial checkout (completion then fired one
269	* installment early and canceled the remote subscription).
270	*
271	* Restores bill_times from the parent order item's other_info.times,
272	* backfills billed_cycles_offset for fully-free first cycles ($0 order),
273	* and recomputes bill_count with the same formula syncSubscriptionStates
274	* uses. Only rows matching the exact bug signature (bill_times == times - 1)
275	* are touched — which also makes re-runs idempotent. bill_times = 0 rows
276	* (unlimited) are excluded; a times=1 product sold with a discount landed
277	* there and stays unrepaired (accepted trade-off).
278	*
279	* @return bool true when the scan reached the end of the table
280	*/
281	private static function repairInstallmentBillTimes()
282	{
283	$chunkSize = 500;
284	$maxChunksPerRun = 5;
285	$maxRepairsPerRun = 500;
286	$chunksProcessed = 0;
287	$lastId = (int)fluent_cart_get_option('_fluent_cart_installment_repair_cursor', 0, false);
288	$offsetIds = [];
289	$underCollectedIds = [];
290	$anomalousIds = [];
291	$repairedRows = [];
292
293	do {
294	$subscriptions = Subscription::query()
295	->where('id', '>', $lastId)
296	->where('bill_times', '>', 0)
297	->where('config', 'LIKE', '%is_trial_days_simulated%')
298	->orderBy('id', 'ASC')
299	->limit($chunkSize)
300	->get();
301
302	if ($subscriptions->isEmpty()) {
303	break;
304	}
305
306	// batched lookups — two queries per chunk, not per subscription
307	$orderIds = [];
308	foreach ($subscriptions as $subscription) {
309	$orderIds[$subscription->parent_order_id] = $subscription->parent_order_id;
310	}
311
312	$itemsByOrderId = [];
313	$orderItems = OrderItem::query()
314	->whereIn('order_id', array_values($orderIds))
315	->where('payment_type', 'subscription')
316	->get();
317	foreach ($orderItems as $item) {
318	$itemsByOrderId[$item->order_id][] = $item;
319	}
320
321	$ordersById = [];
322	$parentOrders = Order::query()->whereIn('id', array_values($orderIds))->get();
323	foreach ($parentOrders as $order) {
324	$ordersById[$order->id] = $order;
325	}
326
327	$subscriptionIds = [];
328	foreach ($subscriptions as $subscription) {
329	$subscriptionIds[] = $subscription->id;
330	}
331
332	// batched per chunk, not per repaired row — grouped charge counts
333	$billsCountBySubscriptionId = [];
334	$transactionCounts = OrderTransaction::query()
335	->selectRaw('subscription_id, COUNT(*) as total')
336	->whereIn('subscription_id', $subscriptionIds)
337	->where('transaction_type', Status::TRANSACTION_TYPE_CHARGE)
338	->where('status', Status::TRANSACTION_SUCCEEDED)
339	->where('total', '>', 0)
340	->groupBy('subscription_id')
341	->get();
342	foreach ($transactionCounts as $row) {
343	$billsCountBySubscriptionId[(int)$row->subscription_id] = (int)$row->total;
344	}
345
346	$earlyPaymentHistoryBySubscriptionId = [];
347	$earlyPaymentMetaRows = SubscriptionMeta::query()
348	->whereIn('subscription_id', $subscriptionIds)
349	->where('meta_key', 'early_payment_history')
350	->get();
351	foreach ($earlyPaymentMetaRows as $metaRow) {
352	$earlyPaymentHistoryBySubscriptionId[(int)$metaRow->subscription_id] = $metaRow->meta_value;
353	}
354
355	foreach ($subscriptions as $subscription) {
356	$lastId = $subscription->id;
357
358	if (Arr::get($subscription->config, 'is_trial_days_simulated', 'no') !== 'yes') {
359	continue;
360	}
361
362	$orderSubscriptionItems = Arr::get($itemsByOrderId, $subscription->parent_order_id, []);
363
364	$orderItem = null;
365	foreach ($orderSubscriptionItems as $candidateItem) {
366	if ((int)$candidateItem->object_id === (int)$subscription->variation_id) {
367	$orderItem = $candidateItem;
368	break;
369	}
370	}
371
372	// fallback only when unambiguous — on a multi-subscription order the
373	// wrong item's times could overwrite this subscription's count
374	if (!$orderItem && count($orderSubscriptionItems) === 1) {
375	$orderItem = $orderSubscriptionItems[0];
376	}
377
378	if (!$orderItem) {
379	continue;
380	}
381
382	$originalTimes = (int)Arr::get((array)$orderItem->other_info, 'times', 0);
383
384	// more than one below the sold count = the old admin flow's double
385	// decrement OR a deliberate reduction — can't tell apart, surface only
386	if ($originalTimes > 1 && (int)$subscription->bill_times < $originalTimes - 1) {
387	$anomalousIds[] = $subscription->id;
388
389	fluent_cart_add_log(
390	'Installment subscription needs manual review',
391	'Subscription #' . $subscription->id . ' has bill_times ' . (int)$subscription->bill_times
392	. ' but its order item was sold with ' . $originalTimes . ' installments. This does not match '
393	. 'the known miscount signature (exactly one less), so it was not auto-repaired. '
394	. 'Verify the intended installment count and adjust manually if needed.',
395	'warning',
396	[
397	'module_name' => 'subscription',
398	'module_id' => $subscription->id
399	]
400	);
401
402	continue;
403	}
404
405	// exact bug signature only — everything else (already repaired,
406	// method-switch flag, deliberate adjustment) stays untouched
407	if ($originalTimes < 1 \|\| (int)$subscription->bill_times !== $originalTimes - 1) {
408	continue;
409	}
410
411	$parentOrder = Arr::get($ordersById, $subscription->parent_order_id);
412
413	if (!$parentOrder) {
414	// can't tell a free first cycle from a paid one without the order
415	continue;
416	}
417
418	$isFreeFirstCycle = !(int)$parentOrder->total_amount && !(int)$subscription->signup_fee;
419	if ($isFreeFirstCycle) {
420	$subscription->updateMeta('billed_cycles_offset', 1);
421	$offsetIds[] = $subscription->id;
422	}
423
424	$billsCount = Arr::get($billsCountBySubscriptionId, $subscription->id, 0);
425
426	$earlyPaymentHistory = Arr::get($earlyPaymentHistoryBySubscriptionId, $subscription->id, []);
427	foreach ((array)$earlyPaymentHistory as $earlyPayment) {
428	$paidCount = (int)Arr::get($earlyPayment, 'count', 1);
429	if ($paidCount > 1) {
430	$billsCount += ($paidCount - 1);
431	}
432	}
433
434	$billsCount += $isFreeFirstCycle ? 1 : 0;
435
436	// before/after audit trail — the overwrite is otherwise irreversible
437	$repairedRows[$subscription->id] = [
438	'status_before' => $subscription->status,
439	'bill_times_before' => (int)$subscription->bill_times,
440	'bill_count_before' => (int)$subscription->bill_count,
441	'bill_times_after' => $originalTimes,
442	'bill_count_after' => $billsCount,
443	];
444
445	$isUnderCollected = $subscription->status === Status::SUBSCRIPTION_COMPLETED
446	&& $billsCount < $originalTimes;
447
448	if ($isUnderCollected) {
449	// falsely completed (remote already canceled): back to active with a
450	// restored next_billing_date so the hourly expiry cron expires it
451	// through the production transition (events fire there, not here);
452	// the customer can then renew/reactivate to pay the remainder
453	$subscription->status = Status::SUBSCRIPTION_ACTIVE;
454	$subscription->next_billing_date = $subscription->guessNextBillingDate();
455	}
456
457	$subscription->bill_times = $originalTimes;
458	$subscription->bill_count = $billsCount;
459	$subscription->save();
460
461	if ($isUnderCollected) {
462	$underCollectedIds[] = $subscription->id;
463
464	fluent_cart_add_log(
465	'Installment subscription under-collected',
466	'Subscription #' . $subscription->id . ' was completed early due to a bill_times miscount ('
467	. $billsCount . ' of ' . $originalTimes . ' installments collected) when a discount was applied '
468	. 'at checkout, and its remote subscription was canceled. Status set back to active; the hourly '
469	. 'expiry check will mark it expired, after which the customer can renew/reactivate to pay the '
470	. 'remaining installment(s).',
471	'warning',
472	[
473	'module_name' => 'subscription',
474	'module_id' => $subscription->id
475	]
476	);
477	}
478	}
479
480	// cursor after every chunk — a timeout resumes here, never from id 0
481	fluent_cart_update_option('_fluent_cart_installment_repair_cursor', $lastId);
482	$chunksProcessed++;
483
484	// chunks bound the scan, repairs bound the heavy per-row work
485	$budgetSpent = $chunksProcessed >= $maxChunksPerRun
486	\|\| count($repairedRows) >= $maxRepairsPerRun;
487
488	if ($subscriptions->count() >= $chunkSize && $budgetSpent) {
489	self::mergeRepairReport($repairedRows, $offsetIds, $underCollectedIds, $anomalousIds);
490
491	return false;
492	}
493	} while ($subscriptions->count() >= $chunkSize);
494
495	$report = self::mergeRepairReport($repairedRows, $offsetIds, $underCollectedIds, $anomalousIds);
496
497	if ($report) {
498	fluent_cart_add_log(
499	'Installment bill_times repair completed',
500	'Restored bill_times on ' . (int)Arr::get($report, 'restored_count', 0)
501	. ' subscription(s), backfilled free-first-cycle offset on ' . (int)Arr::get($report, 'offset_count', 0)
502	. ', flagged ' . count((array)Arr::get($report, 'under_collected_ids', [])) . ' under-collected and '
503	. count((array)Arr::get($report, 'anomalous_ids', [])) . ' for manual review (see individual warning logs).',
504	'info',
505	[
506	'module_name' => 'subscription',
507	'module_id' => 0
508	]
509	);
510	}
511
512	// done — the cursor has no further use
513	Meta::query()
514	->where('object_type', 'option')
515	->where('meta_key', '_fluent_cart_installment_repair_cursor')
516	->delete();
517
518	return true;
519	}
520
521	/**
522	* Clear the stale next_billing_date the pre-guard EOT flow stamped onto
523	* completed subscriptions (cancelRemoteSubscription used to run its
524	* effective_from=immediately assignment on completed rows too). Completed
525	* subscriptions never bill again, so any non-null value here is the bug
526	* signature — which also makes re-runs idempotent: cleared rows no longer
527	* match the scan.
528	*
529	* @return bool true when the scan reached the end of the table
530	*/
531	private static function clearCompletedNextBillingDates()
532	{
533	// Filterable so the chunk/budget boundary is testable with small
534	// tables; production keeps the defaults.
535	$budget = apply_filters('fluent_cart/data_backfills/chunk_budget', [
536	'chunk_size' => 500,
537	'max_chunks_per_run' => 10,
538	], ['slug' => 'completed_next_billing_date']);
539
540	$chunkSize = max(1, (int)Arr::get($budget, 'chunk_size', 500));
541	$maxChunksPerRun = max(1, (int)Arr::get($budget, 'max_chunks_per_run', 10));
542	$chunksProcessed = 0;
543	$lastId = (int)fluent_cart_get_option('_fluent_cart_completed_billing_date_cursor', 0, false);
544	$clearedIds = [];
545
546	do {
547	$rows = Subscription::query()
548	->select(['id'])
549	->where('id', '>', $lastId)
550	->where('status', Status::SUBSCRIPTION_COMPLETED)
551	->whereNotNull('next_billing_date')
552	->orderBy('id', 'ASC')
553	->limit($chunkSize)
554	->get();
555
556	if ($rows->isEmpty()) {
557	break;
558	}
559
560	$ids = [];
561	foreach ($rows as $row) {
562	$lastId = $row->id;
563	$ids[] = $row->id;
564	}
565
566	// Fires between selection and write — a selected row CAN legitimately
567	// change state here (reactivation, gateway resync); the UPDATE below
568	// must re-check status so it never clears a live schedule.
569	do_action('fluent_cart/data_backfills/chunk_selected', [
570	'slug' => 'completed_next_billing_date',
571	'ids' => $ids,
572	]);
573
574	// status re-checked in the UPDATE so a row that changed between the
575	// scan and the write can't lose a legitimate billing date
576	Subscription::query()
577	->whereIn('id', $ids)
578	->where('status', Status::SUBSCRIPTION_COMPLETED)
579	->update(['next_billing_date' => null]);
580
581	// Report only rows the guarded UPDATE actually cleared — every
582	// selected id had a non-null date, so post-update null + completed
583	// is the cleared signature; a row the guard skipped keeps its date.
584	$clearedRows = Subscription::query()
585	->select(['id'])
586	->whereIn('id', $ids)
587	->where('status', Status::SUBSCRIPTION_COMPLETED)
588	->whereNull('next_billing_date')
589	->get();
590	foreach ($clearedRows as $clearedRow) {
591	$clearedIds[] = $clearedRow->id;
592	}
593
594	// cursor after every chunk — a timeout resumes here, never from id 0
595	fluent_cart_update_option('_fluent_cart_completed_billing_date_cursor', $lastId);
596	$chunksProcessed++;
597
598	if ($rows->count() >= $chunkSize && $chunksProcessed >= $maxChunksPerRun) {
599	self::mergeClearedBillingDateReport($clearedIds);
600
601	return false;
602	}
603	} while ($rows->count() >= $chunkSize);
604
605	$report = self::mergeClearedBillingDateReport($clearedIds);
606
607	if ($report) {
608	fluent_cart_add_log(
609	'Completed subscription billing date cleanup completed',
610	'Cleared the stale next_billing_date on ' . (int)Arr::get($report, 'cleared_count', 0)
611	. ' completed subscription(s).',
612	'info',
613	[
614	'module_name' => 'subscription',
615	'module_id' => 0
616	]
617	);
618	}
619
620	// done — the cursor has no further use
621	Meta::query()
622	->where('object_type', 'option')
623	->where('meta_key', '_fluent_cart_completed_billing_date_cursor')
624	->delete();
625
626	return true;
627	}
628
629	/**
630	* Accumulate cleared ids into the report option across partial runs —
631	* id-keyed + deduped, so a replayed chunk can't double-count a subscription.
632	*
633	* @return array\|null merged report, or null when nothing was ever cleared
634	*/
635	private static function mergeClearedBillingDateReport($clearedIds)
636	{
637	$report = (array)fluent_cart_get_option('_fluent_cart_completed_billing_date_report', [], false);
638
639	if (!$clearedIds && !$report) {
640	return null;
641	}
642
643	$report = [
644	'repaired_at' => gmdate('Y-m-d H:i:s'),
645	'cleared_ids' => array_values(array_unique(array_merge(
646	(array)Arr::get($report, 'cleared_ids', []),
647	$clearedIds
648	))),
649	];
650	$report['cleared_count'] = count($report['cleared_ids']);
651
652	fluent_cart_update_option('_fluent_cart_completed_billing_date_report', $report);
653
654	return $report;
655	}
656
657	/**
658	* Accumulate results into the report option across partial runs — rows are
659	* keyed by subscription id, so a replayed chunk can't duplicate entries.
660	*
661	* @return array\|null merged report, or null when nothing was ever repaired
662	*/
663	private static function mergeRepairReport($repairedRows, $offsetIds, $underCollectedIds, $anomalousIds)
664	{
665	$report = (array)fluent_cart_get_option('_fluent_cart_installment_repair_report', [], false);
666
667	if (!$repairedRows && !$anomalousIds && !$report) {
668	return null;
669	}
670
671	$report = [
672	'repaired_at' => gmdate('Y-m-d H:i:s'),
673	'rows' => $repairedRows + (array)Arr::get($report, 'rows', []),
674	// id-keyed + deduped, not a running sum — a re-scanned/replayed chunk
675	// (concurrent runs, advisory lock fail-open) can't double-count a subscription
676	'offset_ids' => array_values(array_unique(array_merge(
677	(array)Arr::get($report, 'offset_ids', []),
678	$offsetIds
679	))),
680	'under_collected_ids' => array_values(array_unique(array_merge(
681	(array)Arr::get($report, 'under_collected_ids', []),
682	$underCollectedIds
683	))),
684	'anomalous_ids' => array_values(array_unique(array_merge(
685	(array)Arr::get($report, 'anomalous_ids', []),
686	$anomalousIds
687	))),
688	];
689	$report['restored_count'] = count($report['rows']);
690	$report['offset_count'] = count($report['offset_ids']);
691
692	fluent_cart_update_option('_fluent_cart_installment_repair_report', $report);
693
694	return $report;
695	}
696
697	/**
698	* Advisory lock — own name, so backfills never contend with schema migrations.
699	*/
700	private static function acquireBackfillLock()
701	{
702	global $wpdb;
703
704	if (Schema::isSqlite()) {
705	// GET_LOCK is MySQL-only; SQLite has no concurrent writers.
706	return true;
707	}
708
709	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
710	$acquired = $wpdb->get_var($wpdb->prepare(
711	"SELECT GET_LOCK(%s, 0)",
712	self::getBackfillLockName()
713	));
714
715	// NULL means the server could not create the lock — fail open so a
716	// locking hiccup can never block backfills entirely.
717	return $acquired === null \|\| (string)$acquired === '1';
718	}
719
720	private static function releaseBackfillLock()
721	{
722	global $wpdb;
723
724	if (Schema::isSqlite()) {
725	return;
726	}
727
728	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
729	$wpdb->query($wpdb->prepare(
730	"SELECT RELEASE_LOCK(%s)",
731	self::getBackfillLockName()
732	));
733	}
734
735	private static function getBackfillLockName()
736	{
737	global $wpdb;
738
739	// GET_LOCK names are server-wide; scope to this site's DB and prefix
740	// so two WordPress installs on one MySQL server can't block each other.
741	$dbName = defined('DB_NAME') ? DB_NAME : '';
742
743	return 'fct_db_backfill_' . md5($dbName . '\|' . $wpdb->prefix);
744	}
745	}
746