fluent-cart / database / DataBackfills.php

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

501 lines 19.7 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	];
48	}
49
50	/**
51	* @return array pending backfill slugs (registered but not completed)
52	*/
53	public static function getPending()
54	{
55	$option = (array)fluent_cart_get_option('_db_migrations', []);
56	$doneSlugs = array_keys(array_filter((array)Arr::get($option, 'backfills', [])));
57
58
59	return array_values(array_diff(array_keys(self::getRegistry()), $doneSlugs));
60	}
61
62	public static function hasPending()
63	{
64	return (bool)self::getPending();
65	}
66
67	/**
68	* Run pending backfills within this request's budget. Called from the
69	* data-backfills/run REST endpoint — the admin app re-calls while the
70	* returned status is 'running'.
71	*
72	* @return array ['status' => completed\|running\|locked, 'completed' => [], 'pending' => []]
73	*/
74	public static function processPending()
75	{
76	$pending = self::getPending();
77
78	if (!$pending) {
79	return ['status' => 'completed', 'completed' => [], 'pending' => []];
80	}
81
82	if (!self::acquireBackfillLock()) {
83	// another request/tab is already on it — let that one finish
84	return ['status' => 'locked', 'completed' => [], 'pending' => $pending];
85	}
86
87	$completedNow = [];
88
89	try {
90	foreach ($pending as $slug) {
91	if (!self::runBackfill($slug)) {
92	break; // request budget spent — the next call resumes from the cursor
93	}
94
95	self::markCompleted($slug);
96	$completedNow[] = $slug;
97	}
98	} finally {
99	self::releaseBackfillLock();
100	}
101
102	$stillPending = self::getPending();
103
104	return [
105	'status' => $stillPending ? 'running' : 'completed',
106	'completed' => $completedNow,
107	'pending' => $stillPending,
108	];
109	}
110
111	/**
112	* @return bool true when the backfill finished; false = budget spent, more remains
113	*/
114	private static function runBackfill($slug)
115	{
116	if ($slug === 'installment_payments') {
117	return self::repairInstallmentBillTimes();
118	}
119
120	// registered slug without a runner — mark done so it can't wedge the
121	// queue, but leave a trace since this is a programming error
122	fluent_cart_add_log(
123	'Data backfill has no runner',
124	'Backfill "' . $slug . '" is registered but has no runner. Marked completed to unblock the queue.',
125	'warning',
126	[
127	'module_name' => 'activity',
128	'module_id' => 0
129	]
130	);
131
132	return true;
133	}
134
135	private static function markCompleted($slug)
136	{
137	$option = (array)fluent_cart_get_option('_db_migrations', []);
138	$backfills = (array)Arr::get($option, 'backfills', []);
139	$backfills[$slug] = 'yes';
140	$option['backfills'] = $backfills;
141
142	fluent_cart_update_option('_db_migrations', $option);
143
144	$title = Arr::get(self::getRegistry(), $slug . '.title', $slug);
145
146	fluent_cart_add_log(
147	$title . ' completed',
148	'Data backfill "' . $slug . '" finished and was marked completed.',
149	'info',
150	[
151	'module_name' => 'activity',
152	'module_id' => 0
153	]
154	);
155	}
156
157	/**
158	* Repair installment subscriptions whose bill_times was stored decremented
159	* by the old discount/simulated-trial checkout (completion then fired one
160	* installment early and canceled the remote subscription).
161	*
162	* Restores bill_times from the parent order item's other_info.times,
163	* backfills billed_cycles_offset for fully-free first cycles ($0 order),
164	* and recomputes bill_count with the same formula syncSubscriptionStates
165	* uses. Only rows matching the exact bug signature (bill_times == times - 1)
166	* are touched — which also makes re-runs idempotent. bill_times = 0 rows
167	* (unlimited) are excluded; a times=1 product sold with a discount landed
168	* there and stays unrepaired (accepted trade-off).
169	*
170	* @return bool true when the scan reached the end of the table
171	*/
172	private static function repairInstallmentBillTimes()
173	{
174	$chunkSize = 500;
175	$maxChunksPerRun = 5;
176	$maxRepairsPerRun = 500;
177	$chunksProcessed = 0;
178	$lastId = (int)fluent_cart_get_option('_fluent_cart_installment_repair_cursor', 0);
179	$offsetIds = [];
180	$underCollectedIds = [];
181	$anomalousIds = [];
182	$repairedRows = [];
183
184	do {
185	$subscriptions = Subscription::query()
186	->where('id', '>', $lastId)
187	->where('bill_times', '>', 0)
188	->where('config', 'LIKE', '%is_trial_days_simulated%')
189	->orderBy('id', 'ASC')
190	->limit($chunkSize)
191	->get();
192
193	if ($subscriptions->isEmpty()) {
194	break;
195	}
196
197	// batched lookups — two queries per chunk, not per subscription
198	$orderIds = [];
199	foreach ($subscriptions as $subscription) {
200	$orderIds[$subscription->parent_order_id] = $subscription->parent_order_id;
201	}
202
203	$itemsByOrderId = [];
204	$orderItems = OrderItem::query()
205	->whereIn('order_id', array_values($orderIds))
206	->where('payment_type', 'subscription')
207	->get();
208	foreach ($orderItems as $item) {
209	$itemsByOrderId[$item->order_id][] = $item;
210	}
211
212	$ordersById = [];
213	$parentOrders = Order::query()->whereIn('id', array_values($orderIds))->get();
214	foreach ($parentOrders as $order) {
215	$ordersById[$order->id] = $order;
216	}
217
218	$subscriptionIds = [];
219	foreach ($subscriptions as $subscription) {
220	$subscriptionIds[] = $subscription->id;
221	}
222
223	// batched per chunk, not per repaired row — grouped charge counts
224	$billsCountBySubscriptionId = [];
225	$transactionCounts = OrderTransaction::query()
226	->selectRaw('subscription_id, COUNT(*) as total')
227	->whereIn('subscription_id', $subscriptionIds)
228	->where('transaction_type', Status::TRANSACTION_TYPE_CHARGE)
229	->where('status', Status::TRANSACTION_SUCCEEDED)
230	->where('total', '>', 0)
231	->groupBy('subscription_id')
232	->get();
233	foreach ($transactionCounts as $row) {
234	$billsCountBySubscriptionId[(int)$row->subscription_id] = (int)$row->total;
235	}
236
237	$earlyPaymentHistoryBySubscriptionId = [];
238	$earlyPaymentMetaRows = SubscriptionMeta::query()
239	->whereIn('subscription_id', $subscriptionIds)
240	->where('meta_key', 'early_payment_history')
241	->get();
242	foreach ($earlyPaymentMetaRows as $metaRow) {
243	$earlyPaymentHistoryBySubscriptionId[(int)$metaRow->subscription_id] = $metaRow->meta_value;
244	}
245
246	foreach ($subscriptions as $subscription) {
247	$lastId = $subscription->id;
248
249	if (Arr::get($subscription->config, 'is_trial_days_simulated', 'no') !== 'yes') {
250	continue;
251	}
252
253	$orderSubscriptionItems = Arr::get($itemsByOrderId, $subscription->parent_order_id, []);
254
255	$orderItem = null;
256	foreach ($orderSubscriptionItems as $candidateItem) {
257	if ((int)$candidateItem->object_id === (int)$subscription->variation_id) {
258	$orderItem = $candidateItem;
259	break;
260	}
261	}
262
263	// fallback only when unambiguous — on a multi-subscription order the
264	// wrong item's times could overwrite this subscription's count
265	if (!$orderItem && count($orderSubscriptionItems) === 1) {
266	$orderItem = $orderSubscriptionItems[0];
267	}
268
269	if (!$orderItem) {
270	continue;
271	}
272
273	$originalTimes = (int)Arr::get((array)$orderItem->other_info, 'times', 0);
274
275	// more than one below the sold count = the old admin flow's double
276	// decrement OR a deliberate reduction — can't tell apart, surface only
277	if ($originalTimes > 1 && (int)$subscription->bill_times < $originalTimes - 1) {
278	$anomalousIds[] = $subscription->id;
279
280	fluent_cart_add_log(
281	'Installment subscription needs manual review',
282	'Subscription #' . $subscription->id . ' has bill_times ' . (int)$subscription->bill_times
283	. ' but its order item was sold with ' . $originalTimes . ' installments. This does not match '
284	. 'the known miscount signature (exactly one less), so it was not auto-repaired. '
285	. 'Verify the intended installment count and adjust manually if needed.',
286	'warning',
287	[
288	'module_name' => 'subscription',
289	'module_id' => $subscription->id
290	]
291	);
292
293	continue;
294	}
295
296	// exact bug signature only — everything else (already repaired,
297	// method-switch flag, deliberate adjustment) stays untouched
298	if ($originalTimes < 1 \|\| (int)$subscription->bill_times !== $originalTimes - 1) {
299	continue;
300	}
301
302	$parentOrder = Arr::get($ordersById, $subscription->parent_order_id);
303
304	if (!$parentOrder) {
305	// can't tell a free first cycle from a paid one without the order
306	continue;
307	}
308
309	$isFreeFirstCycle = !(int)$parentOrder->total_amount && !(int)$subscription->signup_fee;
310	if ($isFreeFirstCycle) {
311	$subscription->updateMeta('billed_cycles_offset', 1);
312	$offsetIds[] = $subscription->id;
313	}
314
315	$billsCount = Arr::get($billsCountBySubscriptionId, $subscription->id, 0);
316
317	$earlyPaymentHistory = Arr::get($earlyPaymentHistoryBySubscriptionId, $subscription->id, []);
318	foreach ((array)$earlyPaymentHistory as $earlyPayment) {
319	$paidCount = (int)Arr::get($earlyPayment, 'count', 1);
320	if ($paidCount > 1) {
321	$billsCount += ($paidCount - 1);
322	}
323	}
324
325	$billsCount += $isFreeFirstCycle ? 1 : 0;
326
327	// before/after audit trail — the overwrite is otherwise irreversible
328	$repairedRows[$subscription->id] = [
329	'status_before' => $subscription->status,
330	'bill_times_before' => (int)$subscription->bill_times,
331	'bill_count_before' => (int)$subscription->bill_count,
332	'bill_times_after' => $originalTimes,
333	'bill_count_after' => $billsCount,
334	];
335
336	$isUnderCollected = $subscription->status === Status::SUBSCRIPTION_COMPLETED
337	&& $billsCount < $originalTimes;
338
339	if ($isUnderCollected) {
340	// falsely completed (remote already canceled): back to active with a
341	// restored next_billing_date so the hourly expiry cron expires it
342	// through the production transition (events fire there, not here);
343	// the customer can then renew/reactivate to pay the remainder
344	$subscription->status = Status::SUBSCRIPTION_ACTIVE;
345	$subscription->next_billing_date = $subscription->guessNextBillingDate();
346	}
347
348	$subscription->bill_times = $originalTimes;
349	$subscription->bill_count = $billsCount;
350	$subscription->save();
351
352	if ($isUnderCollected) {
353	$underCollectedIds[] = $subscription->id;
354
355	fluent_cart_add_log(
356	'Installment subscription under-collected',
357	'Subscription #' . $subscription->id . ' was completed early due to a bill_times miscount ('
358	. $billsCount . ' of ' . $originalTimes . ' installments collected) when a discount was applied '
359	. 'at checkout, and its remote subscription was canceled. Status set back to active; the hourly '
360	. 'expiry check will mark it expired, after which the customer can renew/reactivate to pay the '
361	. 'remaining installment(s).',
362	'warning',
363	[
364	'module_name' => 'subscription',
365	'module_id' => $subscription->id
366	]
367	);
368	}
369	}
370
371	// cursor after every chunk — a timeout resumes here, never from id 0
372	fluent_cart_update_option('_fluent_cart_installment_repair_cursor', $lastId);
373	$chunksProcessed++;
374
375	// chunks bound the scan, repairs bound the heavy per-row work
376	$budgetSpent = $chunksProcessed >= $maxChunksPerRun
377	\|\| count($repairedRows) >= $maxRepairsPerRun;
378
379	if ($subscriptions->count() >= $chunkSize && $budgetSpent) {
380	self::mergeRepairReport($repairedRows, $offsetIds, $underCollectedIds, $anomalousIds);
381
382	return false;
383	}
384	} while ($subscriptions->count() >= $chunkSize);
385
386	$report = self::mergeRepairReport($repairedRows, $offsetIds, $underCollectedIds, $anomalousIds);
387
388	if ($report) {
389	fluent_cart_add_log(
390	'Installment bill_times repair completed',
391	'Restored bill_times on ' . (int)Arr::get($report, 'restored_count', 0)
392	. ' subscription(s), backfilled free-first-cycle offset on ' . (int)Arr::get($report, 'offset_count', 0)
393	. ', flagged ' . count((array)Arr::get($report, 'under_collected_ids', [])) . ' under-collected and '
394	. count((array)Arr::get($report, 'anomalous_ids', [])) . ' for manual review (see individual warning logs).',
395	'info',
396	[
397	'module_name' => 'subscription',
398	'module_id' => 0
399	]
400	);
401	}
402
403	// done — the cursor has no further use
404	Meta::query()
405	->where('object_type', 'option')
406	->where('meta_key', '_fluent_cart_installment_repair_cursor')
407	->delete();
408
409	return true;
410	}
411
412	/**
413	* Accumulate results into the report option across partial runs — rows are
414	* keyed by subscription id, so a replayed chunk can't duplicate entries.
415	*
416	* @return array\|null merged report, or null when nothing was ever repaired
417	*/
418	private static function mergeRepairReport($repairedRows, $offsetIds, $underCollectedIds, $anomalousIds)
419	{
420	$report = (array)fluent_cart_get_option('_fluent_cart_installment_repair_report', []);
421
422	if (!$repairedRows && !$anomalousIds && !$report) {
423	return null;
424	}
425
426	$report = [
427	'repaired_at' => gmdate('Y-m-d H:i:s'),
428	'rows' => $repairedRows + (array)Arr::get($report, 'rows', []),
429	// id-keyed + deduped, not a running sum — a re-scanned/replayed chunk
430	// (concurrent runs, advisory lock fail-open) can't double-count a subscription
431	'offset_ids' => array_values(array_unique(array_merge(
432	(array)Arr::get($report, 'offset_ids', []),
433	$offsetIds
434	))),
435	'under_collected_ids' => array_values(array_unique(array_merge(
436	(array)Arr::get($report, 'under_collected_ids', []),
437	$underCollectedIds
438	))),
439	'anomalous_ids' => array_values(array_unique(array_merge(
440	(array)Arr::get($report, 'anomalous_ids', []),
441	$anomalousIds
442	))),
443	];
444	$report['restored_count'] = count($report['rows']);
445	$report['offset_count'] = count($report['offset_ids']);
446
447	fluent_cart_update_option('_fluent_cart_installment_repair_report', $report);
448
449	return $report;
450	}
451
452	/**
453	* Advisory lock — own name, so backfills never contend with schema migrations.
454	*/
455	private static function acquireBackfillLock()
456	{
457	global $wpdb;
458
459	if (Schema::isSqlite()) {
460	// GET_LOCK is MySQL-only; SQLite has no concurrent writers.
461	return true;
462	}
463
464	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
465	$acquired = $wpdb->get_var($wpdb->prepare(
466	"SELECT GET_LOCK(%s, 0)",
467	self::getBackfillLockName()
468	));
469
470	// NULL means the server could not create the lock — fail open so a
471	// locking hiccup can never block backfills entirely.
472	return $acquired === null \|\| (string)$acquired === '1';
473	}
474
475	private static function releaseBackfillLock()
476	{
477	global $wpdb;
478
479	if (Schema::isSqlite()) {
480	return;
481	}
482
483	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
484	$wpdb->query($wpdb->prepare(
485	"SELECT RELEASE_LOCK(%s)",
486	self::getBackfillLockName()
487	));
488	}
489
490	private static function getBackfillLockName()
491	{
492	global $wpdb;
493
494	// GET_LOCK names are server-wide; scope to this site's DB and prefix
495	// so two WordPress installs on one MySQL server can't block each other.
496	$dbName = defined('DB_NAME') ? DB_NAME : '';
497
498	return 'fct_db_backfill_' . md5($dbName . '\|' . $wpdb->prefix);
499	}
500	}
501