subscription / includes / Illuminate / Plans / PlanRepository.php

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

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