sureforms / inc / database / tables / payments.php

payments.php in SureForms – Contact Form Builder, AI Forms, Payment Form, Survey & Quiz 2.12.8, at inc/database/tables/payments.php

1,345 lines 34.1 KB

Raw Download Zip

1	<?php
2	/**
3	* SureForms Database Payment Table Class.
4	*
5	* @link https://sureforms.com
6	* @since 2.0.0
7	* @package SureForms
8	* @author SureForms <https://sureforms.com/>
9	*/
10
11	namespace SRFM\Inc\Database\Tables;
12
13	use SRFM\Inc\Database\Base;
14	use SRFM\Inc\Helper;
15	use SRFM\Inc\Traits\Get_Instance;
16
17	// Exit if accessed directly.
18	defined( 'ABSPATH' ) \|\| exit;
19
20	/**
21	* SureForms Database Payment Table Class.
22	*
23	* @since 2.0.0
24	*/
25	class Payments extends Base {
26	use Get_Instance;
27
28	/**
29	* Allowed SQL comparison operators for where conditions.
30	*
31	* @since 2.5.2
32	*/
33	private const ALLOWED_OPERATORS = [ '=', '!=', '>', '<', '>=', '<=', 'IN', 'NOT IN', 'LIKE', 'NOT LIKE' ];
34
35	/**
36	* Allowed column names for where conditions.
37	*
38	* @since 2.5.2
39	*/
40	private const ALLOWED_COLUMNS = [
41	'id',
42	'form_id',
43	'block_id',
44	'status',
45	'total_amount',
46	'refunded_amount',
47	'currency',
48	'entry_id',
49	'gateway',
50	'type',
51	'mode',
52	'transaction_id',
53	'customer_id',
54	'subscription_id',
55	'subscription_status',
56	'parent_subscription_id',
57	'payment_data',
58	'extra',
59	'log',
60	'created_at',
61	'updated_at',
62	'srfm_txn_id',
63	'customer_email',
64	'customer_name',
65	];
66
67	/**
68	* {@inheritDoc}
69	*
70	* @var string
71	*/
72	protected $table_suffix = 'payments';
73
74	/**
75	* {@inheritDoc}
76	*
77	* @var int
78	*/
79	protected $table_version = 1;
80
81	/**
82	* Valid payment statuses (Stripe-specific).
83	*
84	* @var array<string>
85	* @since 2.0.0
86	*/
87	private static $valid_statuses = [
88	'pending',
89	'succeeded',
90	'failed',
91	'canceled',
92	'requires_action',
93	'requires_payment_method',
94	'processing',
95	'refunded',
96	'partially_refunded',
97	];
98
99	/**
100	* Valid currencies (ISO 4217).
101	*
102	* @var array<string>
103	* @since 2.0.0
104	*/
105	private static $valid_currencies = [
106	'USD',
107	'EUR',
108	'GBP',
109	'JPY',
110	'CAD',
111	'AUD',
112	'CHF',
113	'CNY',
114	'SEK',
115	'NZD',
116	'MXN',
117	'SGD',
118	'HKD',
119	'NOK',
120	'PLN',
121	'TRY',
122	'RUB',
123	'INR',
124	'BRL',
125	'ZAR',
126	'KRW',
127	];
128
129	/**
130	* Valid payment gateways.
131	*
132	* @var array<string>
133	* @since 2.0.0
134	*/
135	private static $valid_gateways = [
136	'stripe',
137	];
138
139	/**
140	* Valid payment modes.
141	*
142	* @var array<string>
143	* @since 2.0.0
144	*/
145	private static $valid_modes = [
146	'test',
147	'live',
148	];
149
150	/**
151	* Valid subscription statuses (Stripe-specific).
152	*
153	* @var array<string>
154	* @since 2.0.0
155	*/
156	private static $valid_subscription_statuses = [
157	'active',
158	'canceled',
159	'past_due',
160	'unpaid',
161	'trialing',
162	'incomplete',
163	'incomplete_expired',
164	'paused',
165	];
166
167	/**
168	* {@inheritDoc}
169	*/
170	public function get_schema() {
171	return [
172	// Payment ID.
173	'id' => [
174	'type' => 'number',
175	],
176	// Form ID.
177	'form_id' => [
178	'type' => 'number',
179	],
180	'block_id' => [
181	'type' => 'string',
182	'default' => '',
183	],
184	// Payment status (Stripe).
185	'status' => [
186	'type' => 'string',
187	'default' => 'pending',
188	],
189	// Total amount after discount.
190	'total_amount' => [
191	'type' => 'string',
192	'default' => '0.00000000',
193	],
194	// Total refunded amount.
195	'refunded_amount' => [
196	'type' => 'string',
197	'default' => '0.00000000',
198	],
199	// Currency code.
200	'currency' => [
201	'type' => 'string',
202	'default' => '',
203	],
204	// Entry ID.
205	'entry_id' => [
206	'type' => 'number',
207	'default' => 0,
208	],
209	// Payment gateway.
210	'gateway' => [
211	'type' => 'string',
212	'default' => '',
213	],
214	// Payment type.
215	'type' => [
216	'type' => 'string',
217	'default' => '',
218	],
219	// Payment mode (test/live).
220	'mode' => [
221	'type' => 'string',
222	'default' => '',
223	],
224	// Transaction ID from gateway.
225	'transaction_id' => [
226	'type' => 'string',
227	'default' => '',
228	],
229	// Customer ID from gateway.
230	'customer_id' => [
231	'type' => 'string',
232	'default' => '',
233	],
234	// Subscription ID (if recurring).
235	'subscription_id' => [
236	'type' => 'string',
237	'default' => '',
238	],
239	// Subscription status.
240	'subscription_status' => [
241	'type' => 'string',
242	'default' => '',
243	],
244	// Parent subscription payment ID (for renewal payments).
245	'parent_subscription_id' => [
246	'type' => 'number',
247	'default' => 0,
248	],
249	// Payment data.
250	'payment_data' => [
251	'type' => 'array',
252	'default' => [],
253	],
254	// Extra data (JSON).
255	'extra' => [
256	'type' => 'array',
257	'default' => [],
258	],
259	// Payment log.
260	'log' => [
261	'type' => 'array',
262	'default' => [],
263	],
264	// Created date.
265	'created_at' => [
266	'type' => 'datetime',
267	],
268	// Updated date.
269	'updated_at' => [
270	'type' => 'datetime',
271	],
272	// Transaction ID (custom format).
273	'srfm_txn_id' => [
274	'type' => 'string',
275	'default' => '',
276	],
277	// Customer email.
278	'customer_email' => [
279	'type' => 'string',
280	'default' => '',
281	],
282	// Customer name.
283	'customer_name' => [
284	'type' => 'string',
285	'default' => '',
286	],
287	];
288	}
289
290	/**
291	* {@inheritDoc}
292	*/
293	public function get_columns_definition() {
294	return [
295	'id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY',
296	'form_id BIGINT(20) UNSIGNED',
297	'block_id VARCHAR(255) NOT NULL',
298	'status VARCHAR(50) NOT NULL',
299	'total_amount DECIMAL(26,8) NOT NULL',
300	'refunded_amount DECIMAL(26,8) NOT NULL',
301	'currency VARCHAR(10) NOT NULL',
302	'entry_id BIGINT(20) UNSIGNED NOT NULL',
303	'gateway VARCHAR(20) NOT NULL',
304	'type VARCHAR(30) NOT NULL',
305	'mode VARCHAR(20) NOT NULL',
306	'transaction_id VARCHAR(50) NOT NULL',
307	'customer_id VARCHAR(50) NOT NULL',
308	'subscription_id VARCHAR(50) NOT NULL',
309	'subscription_status VARCHAR(20) NOT NULL',
310	'parent_subscription_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0',
311	'payment_data LONGTEXT',
312	'extra LONGTEXT',
313	'log LONGTEXT',
314	'created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP',
315	'updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
316	'srfm_txn_id VARCHAR(100) NOT NULL',
317	'customer_email VARCHAR(255) NOT NULL',
318	'customer_name VARCHAR(255) NOT NULL',
319	];
320	}
321
322	/**
323	* Add a new payment record.
324	*
325	* @param array<string,mixed> $data Payment data to insert.
326	* @since 2.0.0
327	* @return int\|false The payment ID on success, false on error.
328	*/
329	public static function add( $data ) {
330
331	$instance = self::get_instance();
332
333	return $instance->use_insert( $data );
334	}
335
336	/**
337	* Update a payment record.
338	*
339	* @param int $payment_id Payment ID to update.
340	* @param array<string,mixed> $data Data to update.
341	* @since 2.0.0
342	* @return int\|false Number of rows updated or false on error.
343	*/
344	public static function update( $payment_id, $data = [] ) {
345	if ( empty( $payment_id ) ) {
346	return false;
347	}
348
349	return self::get_instance()->use_update( $data, [ 'id' => absint( $payment_id ) ] );
350	}
351
352	/**
353	* Get extra data for a payment.
354	*
355	* @param int $payment_id Payment ID.
356	* @since 2.0.0
357	* @return array<string,mixed> Extra data array.
358	*/
359	public static function get_extra_data( $payment_id ) {
360	if ( empty( $payment_id ) ) {
361	return [];
362	}
363
364	$result = self::get_instance()->get_results(
365	[ 'id' => absint( $payment_id ) ],
366	'extra'
367	);
368
369	return isset( $result[0] ) && is_array( $result[0] ) ? Helper::get_array_value( $result[0]['extra'] ) : [];
370	}
371
372	/**
373	* Update specific key in extra data.
374	*
375	* @param int $payment_id Payment ID.
376	* @param string $key Key to update.
377	* @param mixed $value Value to set.
378	* @since 2.0.0
379	* @return int\|false Number of rows updated or false on error.
380	*/
381	public static function update_extra_key( $payment_id, $key, $value ) {
382	if ( empty( $payment_id ) \|\| empty( $key ) ) {
383	return false;
384	}
385
386	// Get current extra data.
387	$extra_data = self::get_extra_data( $payment_id );
388
389	// Update specific key.
390	$extra_data[ sanitize_key( $key ) ] = $value;
391
392	// Update payment with new extra data.
393	return self::update( $payment_id, [ 'extra' => $extra_data ] );
394	}
395
396	/**
397	* Add multiple key-value pairs to extra data.
398	*
399	* @param int $payment_id Payment ID.
400	* @param array<string,mixed> $data Key-value pairs to add.
401	* @since 2.0.0
402	* @return int\|false Number of rows updated or false on error.
403	*/
404	public static function add_extra_data( $payment_id, $data ) {
405	if ( empty( $payment_id ) \|\| empty( $data ) \|\| ! is_array( $data ) ) {
406	return false;
407	}
408
409	// Get current extra data.
410	$extra_data = self::get_extra_data( $payment_id );
411
412	// Merge new data with existing data.
413	foreach ( $data as $key => $value ) {
414	$extra_data[ sanitize_key( $key ) ] = $value;
415	}
416
417	// Update payment with new extra data.
418	return self::update( $payment_id, [ 'extra' => $extra_data ] );
419	}
420
421	/**
422	* Remove specific key from extra data.
423	*
424	* @param int $payment_id Payment ID.
425	* @param string $key Key to remove.
426	* @since 2.0.0
427	* @return int\|false Number of rows updated or false on error.
428	*/
429	public static function remove_extra_key( $payment_id, $key ) {
430	if ( empty( $payment_id ) \|\| empty( $key ) ) {
431	return false;
432	}
433
434	// Get current extra data.
435	$extra_data = self::get_extra_data( $payment_id );
436
437	// Remove specific key.
438	$sanitized_key = sanitize_key( $key );
439	if ( isset( $extra_data[ $sanitized_key ] ) ) {
440	unset( $extra_data[ $sanitized_key ] );
441
442	// Update payment with modified extra data.
443	return self::update( $payment_id, [ 'extra' => $extra_data ] );
444	}
445
446	return false;
447	}
448
449	/**
450	* Get specific value from extra data.
451	*
452	* @param int $payment_id Payment ID.
453	* @param string $key Key to get.
454	* @param mixed $default Default value if key not found.
455	* @since 2.0.0
456	* @return mixed Value from extra data or default.
457	*/
458	public static function get_extra_value( $payment_id, $key, $default = null ) {
459	if ( empty( $payment_id ) \|\| empty( $key ) ) {
460	return $default;
461	}
462
463	$extra_data = self::get_extra_data( $payment_id );
464	$sanitized_key = sanitize_key( $key );
465
466	return $extra_data[ $sanitized_key ] ?? $default;
467	}
468
469	/**
470	* Get a single payment by ID.
471	*
472	* @param int $payment_id Payment ID.
473	* @since 2.0.0
474	* @return array\|null Payment data or null if not found.
475	*/
476	public static function get( $payment_id ) {
477	if ( empty( $payment_id ) ) {
478	return null;
479	}
480
481	$results = self::get_instance()->get_results( [ 'id' => absint( $payment_id ) ] );
482	return is_array( $results ) && isset( $results[0] ) && is_array( $results[0] ) ? $results[0] : null;
483	}
484
485	/**
486	* Get all payments with optional parameters.
487	*
488	* @param array<mixed> $args Query arguments.
489	* @param bool $set_limit Whether to apply limit to query.
490	* @since 2.0.0
491	* @return array Array of payments.
492	*/
493	public static function get_all( $args = [], $set_limit = true ) {
494	$_args = wp_parse_args(
495	$args,
496	[
497	'where' => [],
498	'columns' => '*',
499	'limit' => 20,
500	'offset' => 0,
501	'orderby' => 'created_at',
502	'order' => 'DESC',
503	]
504	);
505
506	$orderby = ! empty( $_args['orderby'] ) && is_string( $_args['orderby'] ) && in_array( $_args['orderby'], self::ALLOWED_COLUMNS, true ) ? $_args['orderby'] : 'created_at';
507	$order = 'ASC' === strtoupper( Helper::get_string_value( $_args['order'] ) ) ? 'ASC' : 'DESC';
508	$extra_queries = [
509	sprintf( 'ORDER BY `%1$s` %2$s', $orderby, $order ),
510	];
511
512	if ( $set_limit ) {
513	$extra_queries[] = sprintf( 'LIMIT %1$d, %2$d', absint( $_args['offset'] ), absint( $_args['limit'] ) );
514	}
515
516	return self::get_instance()->get_results(
517	$_args['where'],
518	$_args['columns'],
519	$extra_queries
520	);
521	}
522
523	/**
524	* Get total payments count by status.
525	*
526	* @param string $status Status to filter by ('all', 'pending', 'succeeded', etc.).
527	* @param int $form_id Optional form ID to filter by.
528	* @param array<mixed> $where_conditions Optional additional where conditions.
529	* @since 2.0.0
530	* @return int Total count.
531	*/
532	public static function get_total_payments_by_status( $status = 'all', $form_id = 0, $where_conditions = [] ) {
533	$instance = self::get_instance();
534	$where = [];
535
536	// Add status condition.
537	if ( 'all' !== $status ) {
538	$where[] = [
539	[
540	'key' => 'status',
541	'compare' => '=',
542	'value' => sanitize_text_field( $status ),
543	],
544	];
545	}
546
547	// Add form ID condition.
548	if ( $form_id > 0 ) {
549	$where[] = [
550	[
551	'key' => 'form_id',
552	'compare' => '=',
553	'value' => absint( $form_id ),
554	],
555	];
556	}
557
558	// Add additional where conditions.
559	if ( ! empty( $where_conditions ) ) {
560	$where = array_merge( $where, $where_conditions );
561	}
562
563	return $instance->get_total_count( $where );
564	}
565
566	/**
567	* Get payments count after specific timestamp.
568	*
569	* @param int $timestamp Unix timestamp.
570	* @since 2.0.0
571	* @return int Count of payments.
572	*/
573	public static function get_payments_count_after( $timestamp ) {
574	$instance = self::get_instance();
575	$where = [
576	[
577	[
578	'key' => 'created_at',
579	'compare' => '>=',
580	'value' => gmdate( 'Y-m-d H:i:s', $timestamp ),
581	],
582	],
583	];
584
585	return $instance->get_total_count( $where );
586	}
587
588	/**
589	* Get available months for payments.
590	*
591	* @param array<mixed> $where_conditions Optional where conditions.
592	* @since 2.0.0
593	* @return array Array of month values and labels.
594	*/
595	public static function get_available_months( $where_conditions = [] ) {
596	$results = self::get_instance()->get_results(
597	$where_conditions,
598	'DISTINCT DATE_FORMAT(created_at, "%Y%m") as month_value, DATE_FORMAT(created_at, "%M %Y") as month_label',
599	[
600	'ORDER BY month_value DESC',
601	],
602	false
603	);
604
605	$months = [];
606	foreach ( $results as $result ) {
607	if ( is_array( $result ) && isset( $result['month_value'], $result['month_label'] ) ) {
608	$months[ $result['month_value'] ] = $result['month_label'];
609	}
610	}
611
612	return $months;
613	}
614
615	/**
616	* Get all payment IDs for a specific form.
617	*
618	* @param int $form_id Form ID.
619	* @since 2.0.0
620	* @return array Array of payment IDs.
621	*/
622	public static function get_all_payment_ids_for_form( $form_id ) {
623	if ( empty( $form_id ) ) {
624	return [];
625	}
626
627	$instance = self::get_instance();
628	return $instance->get_results(
629	[
630	[
631	[
632	'key' => 'form_id',
633	'compare' => '=',
634	'value' => absint( $form_id ),
635	],
636	],
637	],
638	'id'
639	);
640	}
641
642	/**
643	* Get form IDs by payment IDs.
644	*
645	* @param array<mixed> $payment_ids Array of payment IDs.
646	* @since 2.0.0
647	* @return array Array of unique form IDs.
648	*/
649	public static function get_form_ids_by_payments( $payment_ids ) {
650	if ( empty( $payment_ids ) \|\| ! is_array( $payment_ids ) ) {
651	return [];
652	}
653
654	$instance = self::get_instance();
655	$results = $instance->get_results(
656	[
657	[
658	[
659	'key' => 'id',
660	'compare' => 'IN',
661	'value' => array_map( 'absint', $payment_ids ),
662	],
663	],
664	],
665	'DISTINCT form_id'
666	);
667
668	return array_unique( array_column( $results, 'form_id' ) );
669	}
670
671	/**
672	* Get all distinct form IDs that have payments.
673	*
674	* @since 2.0.0
675	* @return array Array of unique form IDs that have at least one payment.
676	*/
677	public static function get_all_forms_with_payments() {
678	$instance = self::get_instance();
679
680	// Get distinct form IDs from the payments table.
681	$results = $instance->get_results(
682	[], // Empty where clause to get all records.
683	'DISTINCT form_id'
684	);
685
686	$form_ids = array_unique( array_column( $results, 'form_id' ) );
687
688	// Filter out any null or 0 form IDs and return as integers.
689	return array_filter( array_map( 'absint', $form_ids ) );
690	}
691
692	/**
693	* Delete a payment.
694	*
695	* @param int $payment_id Payment ID.
696	* @since 2.0.0
697	* @return int\|false Number of rows deleted or false on error.
698	*/
699	public static function delete( $payment_id ) {
700	if ( empty( $payment_id ) ) {
701	return false;
702	}
703
704	return self::get_instance()->use_delete( [ 'id' => absint( $payment_id ) ] );
705	}
706
707	/**
708	* Get payments by entry ID.
709	*
710	* @param int $entry_id Entry ID.
711	* @since 2.0.0
712	* @return array Array of payments.
713	*/
714	public static function get_by_entry_id( $entry_id ) {
715	if ( empty( $entry_id ) ) {
716	return [];
717	}
718
719	return self::get_all(
720	[
721	'where' => [
722	[
723	[
724	'key' => 'entry_id',
725	'compare' => '=',
726	'value' => absint( $entry_id ),
727	],
728	],
729	],
730	]
731	);
732	}
733
734	/**
735	* Get payments by transaction ID.
736	*
737	* @param string $transaction_id Transaction ID.
738	* @since 2.0.0
739	* @return array\|null Payment data or null if not found.
740	*/
741	public static function get_by_transaction_id( $transaction_id ) {
742	if ( empty( $transaction_id ) ) {
743	return null;
744	}
745
746	$results = self::get_all(
747	[
748	'where' => [
749	[
750	[
751	'key' => 'transaction_id',
752	'compare' => '=',
753	'value' => sanitize_text_field( $transaction_id ),
754	],
755	],
756	],
757	'limit' => 1,
758	]
759	);
760
761	return $results[0] ?? null;
762	}
763
764	/**
765	* Validate payment status.
766	*
767	* @param string $status Status to validate.
768	* @since 2.0.0
769	* @return bool True if valid, false otherwise.
770	*/
771	public static function is_valid_status( $status ) {
772	return in_array( $status, self::$valid_statuses, true );
773	}
774
775	/**
776	* Validate currency.
777	*
778	* @param string $currency Currency to validate.
779	* @since 2.0.0
780	* @return bool True if valid, false otherwise.
781	*/
782	public static function is_valid_currency( $currency ) {
783	return in_array( strtoupper( $currency ), self::$valid_currencies, true );
784	}
785
786	/**
787	* Validate gateway.
788	*
789	* @param string $gateway Gateway to validate.
790	* @since 2.0.0
791	* @return bool True if valid, false otherwise.
792	*/
793	public static function is_valid_gateway( $gateway ) {
794	return in_array( $gateway, self::$valid_gateways, true );
795	}
796
797	/**
798	* Validate mode.
799	*
800	* @param string $mode Mode to validate.
801	* @since 2.0.0
802	* @return bool True if valid, false otherwise.
803	*/
804	public static function is_valid_mode( $mode ) {
805	return in_array( $mode, self::$valid_modes, true );
806	}
807
808	/**
809	* Validate subscription status.
810	*
811	* @param string $status Subscription status to validate.
812	* @since 2.0.0
813	* @return bool True if valid, false otherwise.
814	*/
815	public static function is_valid_subscription_status( $status ) {
816	return in_array( $status, self::$valid_subscription_statuses, true );
817	}
818
819	/**
820	* Get all valid subscription statuses.
821	*
822	* @since 2.0.0
823	* @return array<string> Array of valid subscription statuses.
824	*/
825	public static function get_valid_subscription_statuses() {
826	return self::$valid_subscription_statuses;
827	}
828
829	/**
830	* Get payment data for a payment.
831	*
832	* @param int $payment_id Payment ID.
833	* @since 2.0.0
834	* @return array<string,mixed> Payment data array.
835	*/
836	public static function get_payment_data( $payment_id ) {
837	if ( empty( $payment_id ) ) {
838	return [];
839	}
840
841	$result = self::get_instance()->get_results(
842	[ 'id' => absint( $payment_id ) ],
843	'payment_data'
844	);
845
846	return isset( $result[0] ) && is_array( $result[0] ) ? Helper::get_array_value( $result[0]['payment_data'] ) : [];
847	}
848
849	/**
850	* Add refund data to payment_data column.
851	*
852	* @param int $payment_id Payment ID.
853	* @param array<mixed> $refund_data Refund data to add.
854	* @since 2.0.0
855	* @return int\|false Number of rows updated or false on error.
856	*/
857	public static function add_refund_to_payment_data( $payment_id, $refund_data ) {
858	if ( empty( $payment_id ) \|\| empty( $refund_data ) \|\| ! is_array( $refund_data ) ) {
859	return false;
860	}
861
862	// Extract refund ID - required for using as array key.
863	$refund_id = $refund_data['refund_id'] ?? '';
864	if ( empty( $refund_id ) ) {
865	return false; // Must have a refund ID.
866	}
867
868	// Get current payment data.
869	$payment_data = self::get_payment_data( $payment_id );
870	$payment_data = is_array( $payment_data ) ? $payment_data : [];
871
872	// Initialize refunds array if it doesn't exist.
873	if ( ! isset( $payment_data['refunds'] ) \|\| ! is_array( $payment_data['refunds'] ) ) {
874	$payment_data['refunds'] = [];
875	}
876
877	// Use refund ID as array key - automatically prevents duplicates!
878	$payment_data['refunds'][ $refund_id ] = $refund_data;
879
880	// Update payment with new payment data.
881	return self::update( $payment_id, [ 'payment_data' => $payment_data ] );
882	}
883
884	/**
885	* Add refund amount to the refunded_amount column.
886	*
887	* @param int $payment_id Payment ID.
888	* @param float $refund_amount Refund amount to add (in dollars).
889	* @since 2.0.0
890	* @return int\|false Number of rows updated or false on error.
891	*/
892	public static function add_refund_amount( $payment_id, $refund_amount ) {
893	if ( empty( $payment_id ) \|\| $refund_amount <= 0 ) {
894	return false;
895	}
896
897	// Get current payment data.
898	$payment = self::get( $payment_id );
899	if ( ! $payment ) {
900	return false;
901	}
902
903	// Calculate new refunded amount.
904	$current_refunded = floatval( $payment['refunded_amount'] ?? 0 );
905	$new_total_refunded = $current_refunded + floatval( $refund_amount );
906
907	// Update refunded amount.
908	return self::update( $payment_id, [ 'refunded_amount' => $new_total_refunded ] );
909	}
910
911	/**
912	* Get refunded amount for a payment.
913	*
914	* @param int $payment_id Payment ID.
915	* @since 2.0.0
916	* @return float Refunded amount in dollars.
917	*/
918	public static function get_refunded_amount( $payment_id ) {
919	if ( empty( $payment_id ) ) {
920	return 0.0;
921	}
922
923	$payment = self::get( $payment_id );
924	if ( ! $payment ) {
925	return 0.0;
926	}
927
928	return floatval( $payment['refunded_amount'] ?? 0 );
929	}
930
931	/**
932	* Get refundable amount for a payment.
933	*
934	* @param int $payment_id Payment ID.
935	* @since 2.0.0
936	* @return float Remaining refundable amount in dollars.
937	*/
938	public static function get_refundable_amount( $payment_id ) {
939	if ( empty( $payment_id ) ) {
940	return 0.0;
941	}
942
943	$payment = self::get( $payment_id );
944	if ( ! $payment ) {
945	return 0.0;
946	}
947
948	$total_amount = floatval( $payment['total_amount'] ?? 0 );
949	$refunded_amount = floatval( $payment['refunded_amount'] ?? 0 );
950
951	return max( 0, $total_amount - $refunded_amount );
952	}
953
954	/**
955	* Check if payment is fully refunded.
956	*
957	* @param int $payment_id Payment ID.
958	* @since 2.0.0
959	* @return bool True if fully refunded, false otherwise.
960	*/
961	public static function is_fully_refunded( $payment_id ) {
962	if ( empty( $payment_id ) ) {
963	return false;
964	}
965
966	$payment = self::get( $payment_id );
967	if ( ! $payment ) {
968	return false;
969	}
970
971	$total_amount = floatval( $payment['total_amount'] ?? 0 );
972	$refunded_amount = floatval( $payment['refunded_amount'] ?? 0 );
973
974	return $refunded_amount >= $total_amount && $total_amount > 0;
975	}
976
977	/**
978	* Check if payment is partially refunded.
979	*
980	* @param int $payment_id Payment ID.
981	* @since 2.0.0
982	* @return bool True if partially refunded, false otherwise.
983	*/
984	public static function is_partially_refunded( $payment_id ) {
985	if ( empty( $payment_id ) ) {
986	return false;
987	}
988
989	$refunded_amount = self::get_refunded_amount( $payment_id );
990	return $refunded_amount > 0 && ! self::is_fully_refunded( $payment_id );
991	}
992
993	/**
994	* Get all individual payment transactions related to a subscription.
995	*
996	* @param string $subscription_id Stripe subscription ID.
997	* @since 2.0.0
998	* @return array Array of payment records linked to the subscription.
999	*/
1000	public static function get_subscription_related_payments( $subscription_id ) {
1001	if ( empty( $subscription_id ) ) {
1002	return [];
1003	}
1004
1005	// Get all payments with the subscription_id.
1006	// This automatically includes.
1007	// 1. The initial subscription payment (type='subscription').
1008	// 2. All renewal payments (type='renewal') created by webhooks.
1009	return self::get_all(
1010	[
1011	'where' => [
1012	[
1013	[
1014	'key' => 'subscription_id',
1015	'compare' => '=',
1016	'value' => sanitize_text_field( $subscription_id ),
1017	],
1018	],
1019	],
1020	'orderby' => 'created_at',
1021	'order' => 'DESC',
1022	],
1023	false
1024	);
1025	}
1026
1027	/**
1028	* Get the main subscription record by subscription ID.
1029	*
1030	* @param string $subscription_id Stripe subscription ID.
1031	* @since 2.0.0
1032	* @return array\|null Subscription payment record or null if not found.
1033	*/
1034	public static function get_main_subscription_record( $subscription_id ) {
1035	if ( empty( $subscription_id ) ) {
1036	return null;
1037	}
1038
1039	$results = self::get_all(
1040	[
1041	'where' => [
1042	[
1043	[
1044	'key' => 'subscription_id',
1045	'compare' => '=',
1046	'value' => sanitize_text_field( $subscription_id ),
1047	],
1048	[
1049	'key' => 'type',
1050	'compare' => '=',
1051	'value' => 'subscription',
1052	],
1053	],
1054	],
1055	'limit' => 1,
1056	]
1057	);
1058
1059	return $results[0] ?? null;
1060	}
1061
1062	/**
1063	* Get all payments for main payments table.
1064	* Shows: ALL payment records (subscription, renewal, payment)
1065	* No filtering applied by default - filters only come from frontend user selections
1066	*
1067	* @param array<mixed> $args Query arguments.
1068	* @param bool $set_limit Whether to apply limit to query.
1069	* @since 2.0.0
1070	* @return array Array of payments for main table display.
1071	*/
1072	public static function get_all_main_payments( $args = [], $set_limit = true ) {
1073	global $wpdb;
1074
1075	$_args = wp_parse_args(
1076	$args,
1077	[
1078	'where' => [],
1079	'columns' => '*',
1080	'limit' => 20,
1081	'offset' => 0,
1082	'orderby' => 'created_at',
1083	'order' => 'DESC',
1084	]
1085	);
1086
1087	$instance = self::get_instance();
1088	$table_name = $instance->get_tablename();
1089
1090	// No default filtering - show ALL transactions.
1091	// Filters are applied only from frontend user selections via 'where' conditions.
1092	$where_clause = 'WHERE 1=1';
1093	$params = [];
1094
1095	// Handle additional where conditions if provided.
1096	if ( ! empty( $_args['where'] ) ) {
1097	foreach ( $_args['where'] as $where_group ) {
1098	if ( ! is_array( $where_group ) ) {
1099	continue;
1100	}
1101	$where_clause .= self::build_clause_for_group( $where_group, $params );
1102	}
1103	}
1104
1105	// Order by.
1106	$order = 'ASC' === strtoupper( $_args['order'] ) ? 'ASC' : 'DESC';
1107	$orderby = ! empty( $_args['orderby'] ) && is_string( $_args['orderby'] ) && in_array( $_args['orderby'], self::ALLOWED_COLUMNS, true ) ? $_args['orderby'] : 'created_at';
1108	$order_clause = "ORDER BY {$orderby} {$order}";
1109
1110	// Limit clause.
1111	$limit_clause = '';
1112	if ( $set_limit ) {
1113	$limit_clause = $wpdb->prepare( 'LIMIT %d, %d', absint( $_args['offset'] ), absint( $_args['limit'] ) );
1114	}
1115
1116	// Build final query.
1117	$columns = '*';
1118	if ( ! empty( $_args['columns'] ) && is_string( $_args['columns'] ) ) {
1119	$columns = '' === $_args['columns'] ? '' : esc_sql( $_args['columns'] );
1120	}
1121	$query = "SELECT {$columns} FROM {$table_name} {$where_clause} {$order_clause} {$limit_clause}";
1122
1123	// Execute query with parameters.
1124	if ( ! empty( $params ) ) {
1125	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query string is built dynamically above based on conditions.
1126	$query = $wpdb->prepare( $query, $params );
1127	}
1128
1129	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table query with dynamic preparation; table name internal, not user input; caching not applicable for dynamic queries.
1130	$results = $wpdb->get_results( $query, ARRAY_A );
1131
1132	return is_array( $results ) ? $results : [];
1133	}
1134
1135	/**
1136	* Get total payments count by status for main payments table.
1137	* Counts: ALL payment records (no default filtering)
1138	* Filters are applied only from frontend user selections
1139	*
1140	* @param string $status Status to filter by ('all', 'pending', 'succeeded', etc.).
1141	* @param int $form_id Optional form ID to filter by.
1142	* @param array<mixed> $where_conditions Optional additional where conditions.
1143	* @since 2.0.0
1144	* @return int Total count.
1145	*/
1146	public static function get_total_main_payments_by_status( $status = 'all', $form_id = 0, $where_conditions = [] ) {
1147	global $wpdb;
1148
1149	$instance = self::get_instance();
1150	$table_name = $instance->get_tablename();
1151
1152	// No default filtering - count ALL transactions.
1153	$where_clause = '1=1';
1154	$params = [];
1155
1156	// Add status condition.
1157	if ( 'all' !== $status ) {
1158	$where_clause .= ' AND status = %s';
1159	$params[] = sanitize_text_field( $status );
1160	}
1161
1162	// Add form ID condition.
1163	if ( $form_id > 0 ) {
1164	$where_clause .= ' AND form_id = %d';
1165	$params[] = absint( $form_id );
1166	}
1167
1168	// Handle additional where conditions if provided.
1169	if ( ! empty( $where_conditions ) ) {
1170	foreach ( $where_conditions as $where_group ) {
1171	if ( ! is_array( $where_group ) ) {
1172	continue;
1173	}
1174	$where_clause .= self::build_clause_for_group( $where_group, $params );
1175	}
1176	}
1177
1178	// Build and execute query.
1179	$query = "SELECT COUNT(*) FROM {$table_name} WHERE {$where_clause}";
1180
1181	if ( ! empty( $params ) ) {
1182	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query string is built dynamically above based on conditions.
1183	$query = $wpdb->prepare( $query, $params );
1184	}
1185
1186	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom table query with dynamic preparation; table name internal, not user input; caching not applicable for count operations.
1187	$result = $wpdb->get_var( $query );
1188
1189	return absint( $result );
1190	}
1191
1192	/**
1193	* Check if payment is a subscription record.
1194	*
1195	* @param int $payment_id Payment ID.
1196	* @since 2.0.0
1197	* @return bool True if it's a subscription record, false otherwise.
1198	*/
1199	public static function is_subscription_record( $payment_id ) {
1200	if ( empty( $payment_id ) ) {
1201	return false;
1202	}
1203
1204	$payment = self::get( $payment_id );
1205	if ( ! $payment ) {
1206	return false;
1207	}
1208
1209	return 'subscription' === ( $payment['type'] ?? '' );
1210	}
1211
1212	/**
1213	* Check if payment is a subscription-related individual payment transaction.
1214	* These are payment records that have a subscription_id (part of a subscription billing cycle).
1215	*
1216	* @param int $payment_id Payment ID.
1217	* @since 2.0.0
1218	* @return bool True if it's a subscription-related payment transaction, false otherwise.
1219	*/
1220	public static function is_subscription_payment_transaction( $payment_id ) {
1221	if ( empty( $payment_id ) ) {
1222	return false;
1223	}
1224
1225	$payment = self::get( $payment_id );
1226	if ( ! $payment ) {
1227	return false;
1228	}
1229
1230	return 'payment' === ( $payment['type'] ?? '' ) && ! empty( $payment['subscription_id'] );
1231	}
1232
1233	/**
1234	* Build the SQL fragment for a single where group, handling both flat
1235	* AND-only groups and explicit RELATION (OR/AND) groups.
1236	*
1237	* Flat group example:
1238	* [ [ 'key' => 'status', 'compare' => '=', 'value' => 'paid' ] ]
1239	* → " AND status = %s"
1240	*
1241	* RELATION group example:
1242	* [
1243	* 'RELATION' => 'OR',
1244	* [ 'key' => 'status', 'compare' => '=', 'value' => 'canceled' ],
1245	* [ 'key' => 'subscription_status', 'compare' => '=', 'value' => 'canceled' ],
1246	* ]
1247	* → " AND (status = %s OR subscription_status = %s)"
1248	*
1249	* Conditions with disallowed columns or operators are silently skipped.
1250	* Always prefixes the returned fragment with " AND " so callers can append
1251	* directly to a `WHERE 1=1` clause.
1252	*
1253	* @param array<int\|string,mixed> $where_group Group of conditions, optionally with 'RELATION'.
1254	* @param array<mixed> $params Reference to running params array; appended in place.
1255	* @since 2.9.0
1256	* @return string SQL fragment to append, or empty string if nothing valid.
1257	*/
1258	private static function build_clause_for_group( array $where_group, array &$params ) {
1259	if ( empty( $where_group ) ) {
1260	return '';
1261	}
1262
1263	$is_relation_group = ! empty( $where_group['RELATION'] ) && is_string( $where_group['RELATION'] );
1264	$relation = $is_relation_group && 'OR' === strtoupper( $where_group['RELATION'] )
1265	? 'OR'
1266	: 'AND';
1267
1268	$sub_clauses = [];
1269
1270	foreach ( $where_group as $key => $condition ) {
1271	if ( 'RELATION' === $key \|\| ! is_array( $condition ) ) {
1272	continue;
1273	}
1274	if ( ! isset( $condition['key'], $condition['compare'], $condition['value'] ) ) {
1275	continue;
1276	}
1277	if ( ! in_array( $condition['key'], self::ALLOWED_COLUMNS, true ) ) {
1278	continue;
1279	}
1280
1281	$operator = strtoupper( trim( (string) $condition['compare'] ) );
1282	if ( ! in_array( $operator, self::ALLOWED_OPERATORS, true ) ) {
1283	continue;
1284	}
1285
1286	$column = $condition['key'];
1287
1288	if ( in_array( $operator, [ 'IN', 'NOT IN' ], true ) ) {
1289	if ( ! is_array( $condition['value'] ) ) {
1290	// A scalar used to fall through to the else branch and emit
1291	// `col IN %s`, which is a syntax error that fails the whole
1292	// query. Base::prepare_where_clauses() reports this and drops
1293	// the condition; do the same rather than leave the two builders
1294	// disagreeing on malformed input.
1295	_doing_it_wrong(
1296	__METHOD__,
1297	esc_html( "{$operator} requires an array value, received " . gettype( $condition['value'] ) . '.' ),
1298	'2.12.7'
1299	);
1300	continue;
1301	}
1302
1303	$ids = array_map( 'absint', $condition['value'] );
1304	if ( [] === $ids ) {
1305	// Same empty-list handling as Base::prepare_where_clauses(), so
1306	// the two builders cannot disagree. An empty IN matches nothing.
1307	// An empty NOT IN excludes nothing, and is dropped rather than
1308	// written as a literal, because a literal true would make an
1309	// enclosing OR group match every row.
1310	//
1311	// This builder serves the manage_options-gated admin payments
1312	// listing only, through get_all_main_payments() and
1313	// get_total_main_payments_by_status(). The payment-history
1314	// shortcode's customer filter is compiled by
1315	// Base::prepare_where_clauses() via Payments::get_all() -- that
1316	// group is where an OR fail-open would actually leak, and it is
1317	// covered by the change in base.php.
1318	if ( 'IN' === $operator ) {
1319	$sub_clauses[] = '1 = 0';
1320	}
1321	continue;
1322	}
1323	$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
1324	$sub_clauses[] = "{$column} {$operator} ({$placeholders})";
1325	foreach ( $ids as $id ) {
1326	$params[] = $id;
1327	}
1328	} else {
1329	$sub_clauses[] = "{$column} {$operator} %s";
1330	$params[] = $condition['value'];
1331	}
1332	}
1333
1334	if ( empty( $sub_clauses ) ) {
1335	return '';
1336	}
1337
1338	if ( $is_relation_group ) {
1339	return ' AND (' . implode( " {$relation} ", $sub_clauses ) . ')';
1340	}
1341
1342	return ' AND ' . implode( ' AND ', $sub_clauses );
1343	}
1344	}
1345