subscription / includes / Illuminate / Plans / PlanRepository.php

PlanRepository.php in Subscriptions for WooCommerce with Stripe Recurring Payments 2.4.0, at includes/Illuminate/Plans/PlanRepository.php

1,105 lines 29.9 KB

Raw Download Zip

1	<?php
2	/**
3	* Plan data-access layer and cached product → plans resolver.
4	*
5	* @package SpringDevs\Subscription\Illuminate\Plans
6	*/
7
8	namespace SpringDevs\Subscription\Illuminate\Plans;
9
10	use SpringDevs\Subscription\Illuminate\Multilingual;
11
12	/**
13	* Plan data-access layer.
14	*
15	* Read/write repository over the three plan tables (subscrpt_plan_group,
16	* subscrpt_plan, subscrpt_plan_relation). Provides the cached, batch-loaded
17	* product → plans resolver that runs on the shop / product hot path; a
18	* per-product N+1 here would degrade catalog pages, so reads are batched and
19	* cached in the object cache.
20	*
21	* This is the free base. Pro subclasses/decorates it to add Pro-only columns
22	* and semantics (variable/per-variation, taxonomy, live pricing); the base is
23	* complete and functional standalone on a free-only install.
24	*
25	* @package SpringDevs\Subscription\Illuminate\Plans
26	*/
27	class PlanRepository {
28
29	/**
30	* Object-cache group for resolved product → plans lookups.
31	*
32	* @var string
33	*/
34	const CACHE_GROUP = 'subscrpt_plans';
35
36	/**
37	* Relation type: row targets a specific product / variation.
38	*
39	* @var int
40	*/
41	const REL_PRODUCT = 1;
42
43	/**
44	* Relation type: row targets a taxonomy term (category / tag).
45	*
46	* @var int
47	*/
48	const REL_TAXONOMY = 2;
49
50	/**
51	* UI plan-type string ⇄ stored integer.
52	*
53	* Recurring = 2, subscribe_save = 1, installments = 3 (see Installer).
54	*
55	* @var array<string,int>
56	*/
57	const TYPE_MAP = array(
58	'subscribe_save' => 1,
59	'recurring' => 2,
60	'installments' => 3,
61	);
62
63	/**
64	* Convert a UI plan-type string to its stored integer.
65	*
66	* @param string $type Plan type key.
67	*
68	* @return int
69	*/
70	public static function type_to_int( $type ) {
71	return self::TYPE_MAP[ $type ] ?? 2;
72	}
73
74	/**
75	* Convert a stored plan-type integer back to its UI string.
76	*
77	* @param int $type Stored type integer.
78	*
79	* @return string
80	*/
81	public static function type_to_string( $type ) {
82	$flipped = array_flip( self::TYPE_MAP );
83	return $flipped[ (int) $type ] ?? 'recurring';
84	}
85
86	/**
87	* Billing-interval integer → free-engine timing_option string.
88	*
89	* @var array<int,string>
90	*/
91	const INTERVAL_OPTION = array(
92	1 => 'days',
93	2 => 'weeks',
94	3 => 'months',
95	4 => 'years',
96	);
97
98	/**
99	* Map a billing-interval integer to the free engine's timing_option string.
100	*
101	* @param int $interval 1=day, 2=week, 3=month, 4=year.
102	*
103	* @return string days\|weeks\|months\|years
104	*/
105	public static function interval_to_option( $interval ) {
106	return self::INTERVAL_OPTION[ (int) $interval ] ?? 'months';
107	}
108
109	/**
110	* Plan group table name.
111	*
112	* @return string
113	*/
114	public static function group_table() {
115	global $wpdb;
116	return $wpdb->prefix . 'subscrpt_plan_group';
117	}
118
119	/**
120	* Plan (term) table name.
121	*
122	* @return string
123	*/
124	public static function plan_table() {
125	global $wpdb;
126	return $wpdb->prefix . 'subscrpt_plan';
127	}
128
129	/**
130	* Plan relation (m2m) table name.
131	*
132	* @return string
133	*/
134	public static function relation_table() {
135	global $wpdb;
136	return $wpdb->prefix . 'subscrpt_plan_relation';
137	}
138
139	/**
140	* Resolve every active plan term that applies to a single product.
141	*
142	* Result is cached in the object cache keyed by product id. Variation
143	* filtering is applied in PHP on the cached product-level set so a product
144	* with many variations still costs one query per product.
145	*
146	* @param int $product_id Product (parent) id.
147	* @param int $variation_id Variation id, or 0 for simple products.
148	*
149	* @return array List of resolved plan rows (group + plan + relation merged).
150	*/
151	public static function resolve_for_product( $product_id, $variation_id = 0 ) {
152	$product_id = absint( $product_id );
153
154	if ( ! $product_id ) {
155	return array();
156	}
157
158	// A translation is a different post from the product its plans were
159	// attached to, so read across every language of it. One cache entry
160	// serves them all. Without WPML / Polylang this is just the product.
161	$product_ids = Multilingual::translation_ids( $product_id );
162	$cache_key = 'product_' . Multilingual::canonical_id( $product_id );
163	$resolved = wp_cache_get( $cache_key, self::CACHE_GROUP );
164
165	if ( false === $resolved ) {
166	$batch = self::resolve_for_products( $product_ids );
167	$resolved = count( $product_ids ) > 1 ? self::merge_translation_rows( $batch ) : ( $batch[ $product_id ] ?? array() );
168	wp_cache_set( $cache_key, $resolved, self::CACHE_GROUP );
169	}
170
171	if ( $variation_id ) {
172	$variation_ids = Multilingual::translation_ids( $variation_id );
173	$resolved = array_values(
174	array_filter(
175	$resolved,
176	static function ( $row ) use ( $variation_ids ) {
177	// vid 0 = applies to the parent / all variations.
178	return 0 === (int) $row['vid'] \|\| in_array( (int) $row['vid'], $variation_ids, true );
179	}
180	)
181	);
182	}
183
184	$resolved = Multilingual::translate_plan_rows( $resolved );
185
186	/**
187	* Filter the resolved plan rows for a product.
188	*
189	* Pro's extension point to inject taxonomy- and variation-resolved rows.
190	*
191	* @param array $resolved Resolved plan rows.
192	* @param int $product_id Product id.
193	* @param int $variation_id Variation id (0 for simple).
194	*/
195	return apply_filters( 'subscrpt_resolve_plans_for_product', $resolved, $product_id, $variation_id );
196	}
197
198	/**
199	* Batch-resolve active plan terms for many products in a single query.
200	*
201	* One JOIN across relation → plan → group, filtered to active rows and
202	* product-type relations. Used to prime the per-product cache and to avoid
203	* N+1 on shop loops.
204	*
205	* @param int[] $product_ids Product (parent) ids.
206	*
207	* @return array<int, array> Map of product id → list of resolved plan rows.
208	*/
209	public static function resolve_for_products( array $product_ids ) {
210	global $wpdb;
211
212	$product_ids = array_values( array_unique( array_filter( array_map( 'absint', $product_ids ) ) ) );
213
214	if ( empty( $product_ids ) ) {
215	return array();
216	}
217
218	$map = array_fill_keys( $product_ids, array() );
219
220	$relation_table = self::relation_table();
221	$plan_table = self::plan_table();
222	$group_table = self::group_table();
223
224	$placeholders = implode( ',', array_fill( 0, count( $product_ids ), '%d' ) );
225
226	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
227	$rows = $wpdb->get_results(
228	$wpdb->prepare(
229	"SELECT r.id AS relation_id, r.plan_id, r.oid, r.vid, r.data AS relation_data, r.exclude,
230	p.plan_group_id, p.title AS plan_title, p.type AS plan_type,
231	p.billing_frequency, p.billing_interval, p.billing_length,
232	p.signup_fee, p.free_trial, p.prepaid, p.offer, p.price_mode,
233	p.data AS plan_data,
234	g.title AS group_title, g.type AS group_type, g.product_type, g.data AS group_data
235	FROM {$relation_table} r
236	INNER JOIN {$plan_table} p ON p.id = r.plan_id
237	INNER JOIN {$group_table} g ON g.id = p.plan_group_id
238	WHERE r.type = %d
239	AND r.exclude = 0
240	AND r.status = 'active'
241	AND p.status = 'active'
242	AND g.status = 'active'
243	AND r.oid IN ({$placeholders})
244	ORDER BY g.id ASC, p.id ASC",
245	array_merge( array( self::REL_PRODUCT ), $product_ids )
246	),
247	ARRAY_A
248	);
249	// phpcs:enable
250
251	if ( empty( $rows ) ) {
252	return $map;
253	}
254
255	foreach ( $rows as $row ) {
256	$oid = (int) $row['oid'];
257
258	if ( ! isset( $map[ $oid ] ) ) {
259	continue;
260	}
261
262	$row['relation_data'] = self::maybe_json( $row['relation_data'] );
263	$row['signup_fee'] = self::maybe_json( $row['signup_fee'] );
264	$row['offer'] = self::maybe_json( $row['offer'] );
265	$row['plan_data'] = self::maybe_json( $row['plan_data'] );
266	$row['group_data'] = self::maybe_json( $row['group_data'] );
267
268	$map[ $oid ][] = $row;
269	}
270
271	return $map;
272	}
273
274	/**
275	* Merge the plan rows of every language of one product into one list.
276	*
277	* A plan attached in two languages would otherwise be offered twice, so a
278	* plan appears once per variation it applies to, in group → plan order.
279	*
280	* @param array<int, array> $batch Map of product id → rows, from {@see resolve_for_products()}.
281	*
282	* @return array
283	*/
284	private static function merge_translation_rows( array $batch ) {
285	$merged = array();
286
287	foreach ( $batch as $rows ) {
288	foreach ( $rows as $row ) {
289	$vid = (int) $row['vid'] ? Multilingual::canonical_id( $row['vid'] ) : 0;
290	$key = (int) $row['plan_id'] . ':' . $vid;
291
292	if ( ! isset( $merged[ $key ] ) ) {
293	$merged[ $key ] = $row;
294	}
295	}
296	}
297
298	usort(
299	$merged,
300	static function ( $a, $b ) {
301	return array( (int) $a['plan_group_id'], (int) $a['plan_id'] ) <=> array( (int) $b['plan_group_id'], (int) $b['plan_id'] );
302	}
303	);
304
305	return $merged;
306	}
307
308	/**
309	* Decode a JSON column to an array, tolerating null / plain strings.
310	*
311	* @param string\|null $value Raw column value.
312	*
313	* @return array
314	*/
315	protected static function maybe_json( $value ) {
316	if ( empty( $value ) ) {
317	return array();
318	}
319
320	$decoded = json_decode( $value, true );
321
322	return is_array( $decoded ) ? $decoded : array();
323	}
324
325	// ---- Read side (admin / REST) ----
326
327	/**
328	* Admin read: every plan term a product is attached to, any status.
329	*
330	* Unlike the storefront resolver this ignores status filters and the cache
331	* so the product editor reflects the live DB. Returns one row per relation
332	* with its group + term joined in.
333	*
334	* @param int $product_id Product (parent) id.
335	* @param int $variation_id Variation id, or 0 to include all.
336	*
337	* @return array<int,array>
338	*/
339	public static function get_product_connections( $product_id, $variation_id = 0 ) {
340	global $wpdb;
341
342	$product_id = absint( $product_id );
343
344	if ( ! $product_id ) {
345	return array();
346	}
347
348	$relation_table = self::relation_table();
349	$plan_table = self::plan_table();
350	$group_table = self::group_table();
351
352	// Every language of the product, as the storefront resolver reads them.
353	$product_ids = Multilingual::translation_ids( $product_id );
354	$where = 'r.type = %d AND r.oid IN (' . implode( ',', array_fill( 0, count( $product_ids ), '%d' ) ) . ')';
355	$params = array_merge( array( self::REL_PRODUCT ), $product_ids );
356
357	if ( $variation_id ) {
358	$variation_ids = Multilingual::translation_ids( $variation_id );
359	$where .= ' AND r.vid IN (' . implode( ',', array_fill( 0, count( $variation_ids ), '%d' ) ) . ')';
360	$params = array_merge( $params, $variation_ids );
361	}
362
363	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
364	$rows = $wpdb->get_results(
365	$wpdb->prepare(
366	"SELECT r.id AS relation_id, r.plan_id, r.oid, r.vid, r.data AS relation_data, r.exclude, r.status AS relation_status,
367	p.plan_group_id, p.title AS plan_title, p.type AS plan_type, p.billing_frequency, p.billing_interval, p.status AS plan_status,
368	g.title AS group_title, g.type AS group_type, g.product_type, g.data AS group_data, g.status AS group_status
369	FROM {$relation_table} r
370	INNER JOIN {$plan_table} p ON p.id = r.plan_id
371	INNER JOIN {$group_table} g ON g.id = p.plan_group_id
372	WHERE {$where}
373	ORDER BY g.id ASC, p.id ASC",
374	$params
375	),
376	ARRAY_A
377	);
378	// phpcs:enable
379
380	if ( empty( $rows ) ) {
381	return array();
382	}
383
384	foreach ( $rows as &$row ) {
385	$row['relation_data'] = self::maybe_json( $row['relation_data'] );
386	$row['group_data'] = self::maybe_json( $row['group_data'] );
387	}
388	unset( $row );
389
390	return $rows;
391	}
392
393	/**
394	* List plan groups, newest first.
395	*
396	* @return array<int,array> Raw group rows (data column decoded).
397	*/
398	public static function get_groups() {
399	global $wpdb;
400
401	$table = self::group_table();
402
403	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
404	$rows = $wpdb->get_results( "SELECT * FROM {$table} ORDER BY id DESC", ARRAY_A );
405	// phpcs:enable
406
407	return array_map( array( __CLASS__, 'decode_group_row' ), is_array( $rows ) ? $rows : array() );
408	}
409
410	/**
411	* Fetch a single plan group.
412	*
413	* @param int $group_id Group id.
414	*
415	* @return array\|null
416	*/
417	public static function get_group( $group_id ) {
418	global $wpdb;
419
420	$table = self::group_table();
421
422	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
423	$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $group_id ) ), ARRAY_A );
424	// phpcs:enable
425
426	return $row ? self::decode_group_row( $row ) : null;
427	}
428
429	/**
430	* Fetch the plan terms for a group.
431	*
432	* @param int $group_id Group id.
433	*
434	* @return array<int,array>
435	*/
436	public static function get_plans( $group_id ) {
437	global $wpdb;
438
439	$table = self::plan_table();
440
441	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
442	$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE plan_group_id = %d ORDER BY id ASC", absint( $group_id ) ), ARRAY_A );
443	// phpcs:enable
444
445	return array_map( array( __CLASS__, 'decode_plan_row' ), is_array( $rows ) ? $rows : array() );
446	}
447
448	/**
449	* Fetch a single plan term.
450	*
451	* @param int $plan_id Plan id.
452	*
453	* @return array\|null
454	*/
455	public static function get_plan( $plan_id ) {
456	global $wpdb;
457
458	$table = self::plan_table();
459
460	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
461	$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $plan_id ) ), ARRAY_A );
462	// phpcs:enable
463
464	return $row ? self::decode_plan_row( $row ) : null;
465	}
466
467	/**
468	* Every non-empty group and plan title, any status, for string translation.
469	*
470	* @return array{groups:array<int,string>,plans:array<int,string>} Titles by id.
471	*/
472	public static function get_titles() {
473	global $wpdb;
474
475	$group_table = self::group_table();
476	$plan_table = self::plan_table();
477
478	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
479	$groups = $wpdb->get_results( "SELECT id, title FROM {$group_table} WHERE title <> '' ORDER BY id ASC", ARRAY_A );
480	$plans = $wpdb->get_results( "SELECT id, title FROM {$plan_table} WHERE title <> '' ORDER BY id ASC", ARRAY_A );
481	// phpcs:enable
482
483	return array(
484	'groups' => array_column( (array) $groups, 'title', 'id' ),
485	'plans' => array_column( (array) $plans, 'title', 'id' ),
486	);
487	}
488
489	/**
490	* Fetch relations for a plan term.
491	*
492	* @param int $plan_id Plan id.
493	*
494	* @return array<int,array>
495	*/
496	public static function get_relations( $plan_id ) {
497	global $wpdb;
498
499	$table = self::relation_table();
500
501	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
502	$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE plan_id = %d ORDER BY id ASC", absint( $plan_id ) ), ARRAY_A );
503	// phpcs:enable
504
505	return array_map( array( __CLASS__, 'decode_relation_row' ), is_array( $rows ) ? $rows : array() );
506	}
507
508	/**
509	* Fetch a single relation row.
510	*
511	* @param int $relation_id Relation id.
512	*
513	* @return array\|null
514	*/
515	public static function get_relation( $relation_id ) {
516	global $wpdb;
517
518	$table = self::relation_table();
519
520	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
521	$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", absint( $relation_id ) ), ARRAY_A );
522	// phpcs:enable
523
524	return $row ? self::decode_relation_row( $row ) : null;
525	}
526
527	/**
528	* Assemble a group with its plans and relations as one nested structure.
529	*
530	* @param int $group_id Group id.
531	*
532	* @return array\|null
533	*/
534	public static function get_group_tree( $group_id ) {
535	$group = self::get_group( $group_id );
536
537	if ( ! $group ) {
538	return null;
539	}
540
541	$plans = self::get_plans( $group_id );
542
543	foreach ( $plans as &$plan ) {
544	$plan['relations'] = self::get_relations( $plan['id'] );
545	}
546	unset( $plan );
547
548	$group['plans'] = $plans;
549
550	return $group;
551	}
552
553	// ---- Write side (REST) ----
554
555	/**
556	* Insert a plan group.
557	*
558	* @param array $data Column values (data is JSON-encoded if array).
559	*
560	* @return int\|false New group id, or false on failure.
561	*/
562	public static function insert_group( array $data ) {
563	global $wpdb;
564
565	$now = current_time( 'mysql', true );
566	$row = self::prepare_group_columns( $data );
567
568	$row['created_at'] = $now;
569	$row['updated_at'] = $now;
570
571	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
572	$ok = $wpdb->insert( self::group_table(), $row );
573
574	return $ok ? (int) $wpdb->insert_id : false;
575	}
576
577	/**
578	* Update a plan group.
579	*
580	* @param int $group_id Group id.
581	* @param array $data Column values to update.
582	*
583	* @return bool
584	*/
585	public static function update_group( $group_id, array $data ) {
586	global $wpdb;
587
588	$row = self::prepare_group_columns( $data );
589	$row['updated_at'] = current_time( 'mysql', true );
590
591	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
592	$ok = $wpdb->update( self::group_table(), $row, array( 'id' => absint( $group_id ) ) );
593
594	self::flush_group_products( $group_id );
595
596	return false !== $ok;
597	}
598
599	/**
600	* Delete a plan group and cascade its plans + relations.
601	*
602	* @param int $group_id Group id.
603	*
604	* @return bool
605	*/
606	public static function delete_group( $group_id ) {
607	global $wpdb;
608
609	$group_id = absint( $group_id );
610
611	self::flush_group_products( $group_id );
612
613	$plans = self::get_plans( $group_id );
614	foreach ( $plans as $plan ) {
615	self::delete_plan( $plan['id'] );
616	}
617
618	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
619	$ok = $wpdb->delete( self::group_table(), array( 'id' => $group_id ) );
620
621	return false !== $ok;
622	}
623
624	/**
625	* Insert a plan term.
626	*
627	* @param array $data Column values.
628	*
629	* @return int\|false
630	*/
631	public static function insert_plan( array $data ) {
632	global $wpdb;
633
634	$now = current_time( 'mysql', true );
635	$row = self::prepare_plan_columns( $data );
636
637	$row['created_at'] = $now;
638	$row['updated_at'] = $now;
639
640	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
641	$ok = $wpdb->insert( self::plan_table(), $row );
642
643	return $ok ? (int) $wpdb->insert_id : false;
644	}
645
646	/**
647	* Update a plan term.
648	*
649	* @param int $plan_id Plan id.
650	* @param array $data Column values.
651	*
652	* @return bool
653	*/
654	public static function update_plan( $plan_id, array $data ) {
655	global $wpdb;
656
657	$row = self::prepare_plan_columns( $data );
658	$row['updated_at'] = current_time( 'mysql', true );
659
660	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
661	$ok = $wpdb->update( self::plan_table(), $row, array( 'id' => absint( $plan_id ) ) );
662
663	self::flush_plan_products( $plan_id );
664
665	return false !== $ok;
666	}
667
668	/**
669	* Delete a plan term and cascade its relations.
670	*
671	* @param int $plan_id Plan id.
672	*
673	* @return bool
674	*/
675	public static function delete_plan( $plan_id ) {
676	global $wpdb;
677
678	$plan_id = absint( $plan_id );
679
680	self::flush_plan_products( $plan_id );
681
682	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
683	$wpdb->delete( self::relation_table(), array( 'plan_id' => $plan_id ) );
684	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
685	$ok = $wpdb->delete( self::plan_table(), array( 'id' => $plan_id ) );
686
687	return false !== $ok;
688	}
689
690	/**
691	* Find an existing relation by its natural key (plan term × product/variation).
692	*
693	* @param int $plan_id Plan term id.
694	* @param int $oid Object (product) id.
695	* @param int $vid Variation id (0 for none / all).
696	* @param int $type Relation type (defaults to product).
697	*
698	* @return int Relation id, or 0 if none.
699	*/
700	public static function find_relation( $plan_id, $oid, $vid = 0, $type = self::REL_PRODUCT ) {
701	global $wpdb;
702
703	$table = self::relation_table();
704
705	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
706	$id = $wpdb->get_var(
707	$wpdb->prepare(
708	"SELECT id FROM {$table} WHERE plan_id = %d AND oid = %d AND vid = %d AND type = %d LIMIT 1",
709	absint( $plan_id ),
710	absint( $oid ),
711	absint( $vid ),
712	(int) $type
713	)
714	);
715	// phpcs:enable
716
717	return $id ? (int) $id : 0;
718	}
719
720	/**
721	* Insert a relation (attach a product / variation / term to a plan).
722	*
723	* Idempotent on the natural key (plan term × product × variation): a repeat
724	* connect updates the existing relation rather than creating a duplicate, so
725	* a product never shows the same duration twice.
726	*
727	* @param array $data Column values.
728	*
729	* @return int\|false
730	*/
731	public static function insert_relation( array $data ) {
732	global $wpdb;
733
734	$existing = self::find_relation(
735	isset( $data['plan_id'] ) ? (int) $data['plan_id'] : 0,
736	isset( $data['oid'] ) ? (int) $data['oid'] : 0,
737	isset( $data['vid'] ) ? (int) $data['vid'] : 0,
738	isset( $data['type'] ) ? (int) $data['type'] : self::REL_PRODUCT
739	);
740
741	if ( $existing ) {
742	self::update_relation( $existing, $data );
743	return $existing;
744	}
745
746	$row = self::prepare_relation_columns( $data );
747
748	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
749	$ok = $wpdb->insert( self::relation_table(), $row );
750
751	if ( ! $ok ) {
752	return false;
753	}
754
755	self::flush_cache( $row['oid'] ?? 0 );
756
757	return (int) $wpdb->insert_id;
758	}
759
760	/**
761	* Backfill relations for a newly added term so every product already in the
762	* group is linked to the new duration (inheriting the price it uses on a
763	* sibling term). Keeps the Products tab in sync when a term is added later.
764	*
765	* @param int $group_id Plan group id.
766	* @param int $new_plan_id Newly created term id.
767	*
768	* @return int Number of relations created.
769	*/
770	public static function backfill_term_relations( $group_id, $new_plan_id ) {
771	$group_id = absint( $group_id );
772	$new_plan_id = absint( $new_plan_id );
773
774	if ( ! $group_id \|\| ! $new_plan_id ) {
775	return 0;
776	}
777
778	// One representative relation per (oid, vid, type) across the other terms.
779	$seen = array();
780
781	foreach ( self::get_plans( $group_id ) as $term ) {
782	if ( (int) $term['id'] === $new_plan_id ) {
783	continue;
784	}
785
786	foreach ( self::get_relations( $term['id'] ) as $rel ) {
787	$key = $rel['oid'] . ':' . $rel['vid'] . ':' . $rel['type'];
788
789	if ( ! isset( $seen[ $key ] ) ) {
790	$seen[ $key ] = $rel;
791	}
792	}
793	}
794
795	$created = 0;
796
797	foreach ( $seen as $rel ) {
798	// Create-only: never touch an existing relation, so a re-run can't
799	// overwrite a per-product price the merchant already set.
800	if ( self::find_relation( $new_plan_id, (int) $rel['oid'], (int) $rel['vid'], (int) $rel['type'] ) ) {
801	continue;
802	}
803
804	$ok = self::insert_relation(
805	array(
806	'plan_id' => $new_plan_id,
807	'oid' => (int) $rel['oid'],
808	'vid' => (int) $rel['vid'],
809	'type' => (int) $rel['type'],
810	'status' => 'active',
811	'exclude' => 0,
812	'data' => is_array( $rel['data'] ) ? $rel['data'] : array(),
813	)
814	);
815
816	if ( $ok ) {
817	++$created;
818	}
819	}
820
821	return $created;
822	}
823
824	/**
825	* Update a relation.
826	*
827	* @param int $relation_id Relation id.
828	* @param array $data Column values.
829	*
830	* @return bool
831	*/
832	public static function update_relation( $relation_id, array $data ) {
833	global $wpdb;
834
835	$existing = self::get_relation( $relation_id );
836
837	// Merge a partial `data` payload into the existing JSON so callers can
838	// update a few fields (e.g. price, one_time) without resending all of it.
839	if ( array_key_exists( 'data', $data ) && $existing && is_array( $existing['data'] ) ) {
840	$data['data'] = array_merge( $existing['data'], (array) $data['data'] );
841	}
842
843	$row = self::prepare_relation_columns( $data );
844
845	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
846	$ok = $wpdb->update( self::relation_table(), $row, array( 'id' => absint( $relation_id ) ) );
847
848	if ( $existing ) {
849	self::flush_cache( $existing['oid'] );
850	}
851	if ( isset( $row['oid'] ) ) {
852	self::flush_cache( $row['oid'] );
853	}
854
855	return false !== $ok;
856	}
857
858	/**
859	* Delete a relation.
860	*
861	* @param int $relation_id Relation id.
862	*
863	* @return bool
864	*/
865	public static function delete_relation( $relation_id ) {
866	global $wpdb;
867
868	$existing = self::get_relation( $relation_id );
869
870	// phpcs:ignore WordPress.DB.DirectDatabaseQuery
871	$ok = $wpdb->delete( self::relation_table(), array( 'id' => absint( $relation_id ) ) );
872
873	if ( $existing ) {
874	self::flush_cache( $existing['oid'] );
875	}
876
877	return false !== $ok;
878	}
879
880	// ---- Column / row helpers ----
881
882	/**
883	* Whitelist + encode group columns for write.
884	*
885	* @param array $data Raw input.
886	*
887	* @return array
888	*/
889	protected static function prepare_group_columns( array $data ) {
890	$row = array();
891
892	if ( isset( $data['type'] ) ) {
893	$row['type'] = is_numeric( $data['type'] ) ? (int) $data['type'] : self::type_to_int( $data['type'] );
894	}
895	if ( isset( $data['product_type'] ) ) {
896	$row['product_type'] = (int) $data['product_type'];
897	}
898	if ( isset( $data['title'] ) ) {
899	$row['title'] = sanitize_text_field( $data['title'] );
900	}
901	if ( isset( $data['status'] ) ) {
902	$row['status'] = sanitize_key( $data['status'] );
903	}
904	if ( array_key_exists( 'data', $data ) ) {
905	$row['data'] = wp_json_encode( $data['data'] );
906	}
907
908	return $row;
909	}
910
911	/**
912	* Whitelist + encode plan columns for write.
913	*
914	* @param array $data Raw input.
915	*
916	* @return array
917	*/
918	protected static function prepare_plan_columns( array $data ) {
919	$row = array();
920
921	if ( isset( $data['plan_group_id'] ) ) {
922	$row['plan_group_id'] = absint( $data['plan_group_id'] );
923	}
924	if ( isset( $data['title'] ) ) {
925	$row['title'] = sanitize_text_field( $data['title'] );
926	}
927	if ( isset( $data['type'] ) ) {
928	$row['type'] = is_numeric( $data['type'] ) ? (int) $data['type'] : self::type_to_int( $data['type'] );
929	}
930	if ( isset( $data['billing_frequency'] ) ) {
931	$row['billing_frequency'] = (int) $data['billing_frequency'];
932	}
933	if ( isset( $data['billing_interval'] ) ) {
934	$row['billing_interval'] = (int) $data['billing_interval'];
935	}
936	if ( isset( $data['billing_length'] ) ) {
937	$row['billing_length'] = (int) $data['billing_length'];
938	}
939	if ( array_key_exists( 'signup_fee', $data ) ) {
940	$row['signup_fee'] = wp_json_encode( $data['signup_fee'] );
941	}
942	if ( isset( $data['free_trial'] ) ) {
943	$row['free_trial'] = sanitize_text_field( $data['free_trial'] );
944	}
945	if ( isset( $data['prepaid'] ) ) {
946	$row['prepaid'] = (int) (bool) $data['prepaid'];
947	}
948	if ( array_key_exists( 'offer', $data ) ) {
949	$row['offer'] = wp_json_encode( $data['offer'] );
950	}
951	if ( isset( $data['price_mode'] ) ) {
952	$row['price_mode'] = sanitize_key( $data['price_mode'] );
953	}
954	if ( isset( $data['status'] ) ) {
955	$row['status'] = sanitize_key( $data['status'] );
956	}
957	if ( array_key_exists( 'data', $data ) ) {
958	$row['data'] = wp_json_encode( $data['data'] );
959	}
960
961	return $row;
962	}
963
964	/**
965	* Whitelist + encode relation columns for write.
966	*
967	* @param array $data Raw input.
968	*
969	* @return array
970	*/
971	protected static function prepare_relation_columns( array $data ) {
972	$row = array();
973
974	if ( isset( $data['plan_id'] ) ) {
975	$row['plan_id'] = absint( $data['plan_id'] );
976	}
977	if ( isset( $data['oid'] ) ) {
978	$row['oid'] = absint( $data['oid'] );
979	}
980	if ( isset( $data['vid'] ) ) {
981	$row['vid'] = absint( $data['vid'] );
982	}
983	if ( isset( $data['type'] ) ) {
984	$row['type'] = (int) $data['type'];
985	}
986	if ( array_key_exists( 'data', $data ) ) {
987	$row['data'] = wp_json_encode( $data['data'] );
988	}
989	if ( isset( $data['exclude'] ) ) {
990	$row['exclude'] = (int) (bool) $data['exclude'];
991	}
992	if ( isset( $data['status'] ) ) {
993	$row['status'] = sanitize_key( $data['status'] );
994	}
995
996	return $row;
997	}
998
999	/**
1000	* Decode a raw group row's JSON column.
1001	*
1002	* @param array $row Raw row.
1003	*
1004	* @return array
1005	*/
1006	protected static function decode_group_row( $row ) {
1007	$row['id'] = (int) $row['id'];
1008	$row['type'] = (int) $row['type'];
1009	$row['product_type'] = (int) $row['product_type'];
1010	$row['data'] = self::maybe_json( $row['data'] );
1011	$row['type_key'] = self::type_to_string( $row['type'] );
1012
1013	return $row;
1014	}
1015
1016	/**
1017	* Decode a raw plan row's JSON columns.
1018	*
1019	* @param array $row Raw row.
1020	*
1021	* @return array
1022	*/
1023	protected static function decode_plan_row( $row ) {
1024	$row['id'] = (int) $row['id'];
1025	$row['plan_group_id'] = (int) $row['plan_group_id'];
1026	$row['type'] = (int) $row['type'];
1027	$row['signup_fee'] = self::maybe_json( $row['signup_fee'] );
1028	$row['offer'] = self::maybe_json( $row['offer'] );
1029	$row['data'] = self::maybe_json( $row['data'] );
1030
1031	return $row;
1032	}
1033
1034	/**
1035	* Decode a raw relation row's JSON column.
1036	*
1037	* @param array $row Raw row.
1038	*
1039	* @return array
1040	*/
1041	protected static function decode_relation_row( $row ) {
1042	$row['id'] = (int) $row['id'];
1043	$row['plan_id'] = (int) $row['plan_id'];
1044	$row['oid'] = (int) $row['oid'];
1045	$row['vid'] = (int) $row['vid'];
1046	$row['type'] = (int) $row['type'];
1047	$row['exclude'] = (int) $row['exclude'];
1048	$row['data'] = self::maybe_json( $row['data'] );
1049
1050	return $row;
1051	}
1052
1053	/**
1054	* Flush the resolver cache for every product attached to a plan.
1055	*
1056	* @param int $plan_id Plan id.
1057	*
1058	* @return void
1059	*/
1060	protected static function flush_plan_products( $plan_id ) {
1061	foreach ( self::get_relations( $plan_id ) as $relation ) {
1062	self::flush_cache( $relation['oid'] );
1063	}
1064	}
1065
1066	/**
1067	* Flush the resolver cache for every product attached to any plan in a group.
1068	*
1069	* @param int $group_id Group id.
1070	*
1071	* @return void
1072	*/
1073	protected static function flush_group_products( $group_id ) {
1074	foreach ( self::get_plans( $group_id ) as $plan ) {
1075	self::flush_plan_products( $plan['id'] );
1076	}
1077	}
1078
1079	/**
1080	* Flush the resolver cache for one product, or the whole group.
1081	*
1082	* Call after any write to a plan / relation so the hot-path cache cannot
1083	* serve stale terms. With no product id, bumps the whole cache group.
1084	*
1085	* @param int $product_id Product id, or 0 to flush everything.
1086	*
1087	* @return void
1088	*/
1089	public static function flush_cache( $product_id = 0 ) {
1090	$product_id = absint( $product_id );
1091
1092	if ( $product_id ) {
1093	wp_cache_delete( 'product_' . $product_id, self::CACHE_GROUP );
1094	// The key every translation of the product shares, when that is another id.
1095	wp_cache_delete( 'product_' . Multilingual::canonical_id( $product_id ), self::CACHE_GROUP );
1096	return;
1097	}
1098
1099	// No targeted product: drop the whole group when the backend supports it.
1100	if ( function_exists( 'wp_cache_flush_group' ) && wp_cache_supports( 'flush_group' ) ) {
1101	wp_cache_flush_group( self::CACHE_GROUP );
1102	}
1103	}
1104	}
1105