shopbuilder / app / Modules / AbandonedCartRecovery / CartRecoveryDB.php

CartRecoveryDB.php in ShopBuilder – WooCommerce Builder For Elementor 3.2.6, at app/Modules/AbandonedCartRecovery/CartRecoveryDB.php

987 lines 31.7 KB

Raw Download Zip

1	<?php
2	/**
3	* Sticky Abandoned Cart Recovery Module Class.
4	*
5	* @package RadiusTheme\SB
6	*/
7
8	namespace RadiusTheme\SB\Modules\AbandonedCartRecovery;
9
10	use DateTime;
11	use Exception;
12	use RadiusTheme\SB\Helpers\Fns;
13
14	defined( 'ABSPATH' ) \|\| exit();
15
16	/**
17	* Sticky add-to-cart Module Class.
18	*/
19	class CartRecoveryDB {
20	/**
21	* Parse GROUP_CONCAT meta_data into associative array.
22	*
23	* @param array $abandonments Single or multiple abandonment records.
24	* @return array Abandonments with 'meta' array.
25	* @since 1.0.0
26	*/
27	private static function parseMetaData( array $abandonments ): array {
28	return array_map(
29	function ( $abandonment ) {
30	if ( ! empty( $abandonment['meta_data'] ) ) {
31	$abandonment['meta'] = [];
32	foreach ( explode( ',', $abandonment['meta_data'] ) as $pair ) {
33	[ $key, $value ] = explode( ':', $pair, 2 );
34	$abandonment['meta'][ $key ] = $value;
35	}
36	}
37	unset( $abandonment['meta_data'] );
38	if ( isset( $abandonment['other_fields'] ) ) {
39	$abandonment['other_fields'] = maybe_unserialize( $abandonment['other_fields'] );
40	}
41	return $abandonment;
42	},
43	$abandonments
44	);
45	}
46	/**
47	* Insert meta for a given table and ID.
48	*
49	* @param string $table Table name.
50	* @param string $id_field ID field name (e.g., 'abandonment_id' or 'email_template_id').
51	* @param int $id The ID value.
52	* @param string $key Meta key.
53	* @param mixed $value Meta value.
54	* @return void
55	*/
56	public static function insert_meta( $table, $id_field, $id, $key, $value ) {
57	$data = [
58	$id_field => absint( $id ),
59	'meta_key' => $key, // phpcs:ignore WordPress.DB.SlowDBQuery
60	'meta_value' => maybe_serialize( $value ), // phpcs:ignore WordPress.DB.SlowDBQuery
61	];
62	Fns::DB()::insert( $table, [ $data ] )->execute();
63	}
64
65	/**
66	* Update meta for a given table and ID.
67	*
68	* @param string $table Table name.
69	* @param string $id_field ID field name (e.g., 'abandonment_id' or 'email_template_id').
70	* @param int $id The ID value.
71	* @param string $key Meta key.
72	* @param mixed $value Meta value.
73	* @return void
74	*/
75	public static function update_meta( $table, $id_field, $id, $key, $value ) {
76	$data = [
77	'meta_value' => maybe_serialize( $value ), // phpcs:ignore WordPress.DB.SlowDBQuery
78	];
79	Fns::DB()::update( $table, $data )
80	->where( $id_field, '=', absint( $id ) )
81	->andWhere( 'meta_key', '=', $key )
82	->execute();
83	}
84	/**
85	* Insert or update meta for a given table and ID.
86	*
87	* @param string $table Table name.
88	* @param string $id_field ID field name (e.g., 'abandonment_id' or 'email_template_id').
89	* @param int $id The ID value.
90	* @param string $key Meta key.
91	* @param mixed $value Meta value.
92	* @return void
93	*/
94	public static function upsert_meta( $table, $id_field, $id, $key, $value ) {
95	$exists = Fns::DB()::select( 'id' )
96	->from( $table )
97	->where( $id_field, '=', absint( $id ) )
98	->andWhere( 'meta_key', '=', $key )
99	->get();
100	if ( ! empty( $exists[0] ) ) {
101	// Update existing.
102	self::update_meta( $table, $id_field, $id, $key, $value );
103	} else {
104	// Insert new.
105	self::insert_meta( $table, $id_field, $id, $key, $value );
106	}
107	}
108	/**
109	* @param array $templates Array of templates.
110	* @return void
111	*/
112	public static function createTemplates( $templates = [] ) {
113	if ( empty( $templates ) ) {
114	return;
115	}
116	foreach ( $templates as $template ) {
117	$id = $template['id'] ?? 0;
118	unset( $template['id'] );
119	// Check if the ID already exists.
120	$exists = Fns::DB()::select( 'id' )
121	->from( CartRecoveryFns::$ca_email )
122	->where( 'id', '=', $id )
123	->get();
124	if ( $exists ) {
125	// Update existing row.
126	Fns::DB()::update( CartRecoveryFns::$ca_email, $template )->where( 'id', '=', $id )->execute();
127	} else {
128	Fns::DB()::insert( CartRecoveryFns::$ca_email, [ $template ] )->execute();
129	}
130	}
131	}
132
133	/**
134	* Apply coupon meta fields to a template
135	*
136	* @param array $template Template data with parsed meta.
137	* @return array Template with coupon-related fields.
138	*/
139	private static function templatesOtherFields( $template ) {
140	if ( ! empty( $template['other_fields'] ) ) {
141	$template = array_merge( $template, $template['other_fields'] );
142	unset( $template['other_fields'] );
143	}
144	return $template;
145	}
146	/**
147	* Get all email templates from database
148	*
149	* @return array Array of template objects
150	*/
151	public static function getAllTemplates() {
152	$order_by = 'menu_order';
153	$order = 'ASC';
154	// Validate order direction to prevent SQL injection.
155	$order = strtoupper( $order ) === 'DESC' ? 'DESC' : 'ASC';
156	// Validate order_by column to prevent SQL injection.
157	$allowed_columns = [ 'id', 'title', 'email_subject', 'is_activated', 'frequency', 'menu_order', 'created_at' ];
158	$order_by = in_array( $order_by, $allowed_columns, true ) ? $order_by : 'menu_order';
159	try {
160	$templates = Fns::DB()::select()
161	->raw( 't.*, GROUP_CONCAT(CONCAT(m.meta_key, ":", m.meta_value) SEPARATOR ",") AS meta_data' )
162	->from( CartRecoveryFns::$ca_email . ' t' )
163	->leftJoin( CartRecoveryFns::$ca_email_meta . ' m', 't.id', 'm.email_template_id' )
164	->groupBy( 't.id' )
165	->orderBy( $order_by, $order )
166	->get();
167	$templates = ! empty( $templates ) ? self::parseMetaData( $templates ) : [];
168	$data = [];
169	if ( ! empty( $templates ) ) {
170	foreach ( $templates as $template ) {
171	$data[] = self::templatesOtherFields( $template );
172	}
173	}
174	return $data;
175	} catch ( Exception $e ) {
176	return [];
177	}
178	}
179
180	/**
181	* Get template by ID
182	*
183	* @param int $template_id Template ID.
184	* @return object\|null Template object or null if not found
185	*/
186	/**
187	* Get template by ID with meta data
188	*
189	* @param int $template_id Template ID.
190	* @return array\|null Template data with meta or null if not found
191	*/
192	public static function getTemplateById( $template_id ) {
193	try {
194	$template = Fns::DB()::select()
195	->raw( 't.*, GROUP_CONCAT(CONCAT(m.meta_key, ":", m.meta_value) SEPARATOR ",") AS meta_data' )
196	->from( CartRecoveryFns::$ca_email . ' t' )
197	->leftJoin( CartRecoveryFns::$ca_email_meta . ' m', 't.id', 'm.email_template_id' )
198	->where( 't.id', '=', absint( $template_id ) )
199	->groupBy( 't.id' )
200	->get();
201
202	if ( empty( $template ) ) {
203	return null;
204	}
205	// Parse meta data.
206	$templates = self::parseMetaData( $template );
207	$template = $templates[0] ?? null;
208	return $template ? self::templatesOtherFields( $template ) : null;
209	} catch ( Exception $e ) {
210	return null;
211	}
212	}
213
214	/**
215	* Delete template by ID
216	*
217	* @param int $template_id Template ID.
218	* @return bool True if deleted successfully, false otherwise
219	*/
220	public static function deleteTemplate( $template_id ) {
221	try {
222	// Delete email history records.
223	Fns::DB()::delete( CartRecoveryFns::$ca_email_history )
224	->where( 'template_id', '=', $template_id )
225	->execute();
226	Fns::DB()::delete( CartRecoveryFns::$ca_email_meta )
227	->where( 'email_template_id', '=', $template_id )
228	->execute();
229	return Fns::DB()::delete( CartRecoveryFns::$ca_email )
230	->where( 'id', '=', $template_id )
231	->execute();
232	} catch ( Exception $e ) {
233	return false;
234	}
235	}
236
237	/**
238	* Get total number of abandoned carts.
239	*
240	* @since 1.0.0
241	*/
242	public static function getCartAbandonmentCount() {
243	try {
244	$count = Fns::DB()::select()
245	->raw( 'COUNT(*) as total' )
246	->from( CartRecoveryFns::$ca_abandonment )
247	->get();
248
249	return ! empty( $count[0]['total'] ) ? intval( $count[0]['total'] ) : 0;
250	} catch ( Exception $e ) {
251	return 0;
252	}
253	}
254
255	/**
256	* Get all cart abandonment records with meta.
257	*
258	* @param int $limit Number of records per page.
259	* @param int $page Page number.
260	* @return array List of abandonment records with meta.
261	* @since 1.0.0
262	*/
263	public static function getAllCartAbandonment( int $limit = 50, int $page = 1 ): array {
264	try {
265	$offset = ( $page - 1 ) * $limit;
266	$abandonments = Fns::DB()::select()
267	->raw( 't.*, GROUP_CONCAT(CONCAT(m.meta_key, ":", m.meta_value) SEPARATOR ",") AS meta_data' )
268	->from( CartRecoveryFns::$ca_abandonment . ' t' )
269	->leftJoin( CartRecoveryFns::$ca_abandonment_meta . ' m', 't.id', 'm.abandonment_id' )
270	->groupBy( 't.id' )
271	->orderBy( 't.id', 'DESC' )
272	->limit( $limit )
273	->offset( $offset )
274	->get();
275
276	return ! empty( $abandonments ) ? self::parseMetaData( $abandonments ) : [];
277	} catch ( Exception $e ) {
278	return [];
279	}
280	}
281
282	/**
283	* Get all cart abandonment records with meta.
284	*
285	* @param string $startDate Number of records per page.
286	* @param string $endDate Page number.
287	* @return array number.
288	*/
289	public static function getRecoverableOrderData( $startDate, $endDate ) {
290	$default = [
291	'revenue' => '',
292	'items' => [],
293	'count' => 0,
294	];
295	if ( empty( $startDate ) \|\| empty( $endDate ) ) {
296	return $default;
297	}
298	try {
299	$results = Fns::DB()::select()
300	->raw( "t.*, SUM(CAST(REGEXP_REPLACE(t.cart_total, '[^0-9.]', '') AS DECIMAL(10,2))) AS total, REGEXP_REPLACE(t.cart_total, '[0-9.]+', '') AS currency, JSON_ARRAYAGG(JSON_OBJECT('scheduled_time', m.scheduled_time, 'template_id', m.template_id)) AS scheduled_times" )
301	->from( CartRecoveryFns::$ca_abandonment . ' t' )
302	->innerJoin( CartRecoveryFns::$ca_email_history . ' m', 't.id', 'm.abandonment_id' )
303	->where( 'm.email_sent', '=', 1 )
304	->andWhere( 'm.scheduled_time', '>=', $startDate )
305	->andWhere( 'm.scheduled_time', '<=', $endDate )
306	->groupBy( 't.id' )
307	->get();
308
309	$totals = [];
310	$items = [];
311	foreach ( $results as $row ) {
312	$currency = trim( $row['currency'] ?: '$' );
313	if ( isset( $totals[ $currency ] ) ) {
314	$totals[ $currency ] += floatval( $row['total'] );
315	} else {
316	$totals[ $currency ] = floatval( $row['total'] );
317	}
318	$scheduled_times = json_decode( $row['scheduled_times'], true );
319	$times = [];
320	foreach ( $scheduled_times as &$st ) {
321	$time = ( new DateTime( $st['scheduled_time'] ) )->format( 'Y-m-d H:i:s' );
322	$times[ $time ] = strtotime( $time );
323	}
324	$row['scheduled_times'] = $times;
325	$items[] = $row;
326	}
327	// Prepare final string with currency symbols.
328	$revenue_parts = [];
329	foreach ( $totals as $currency => $amount ) {
330	$revenue_parts[] = $amount . $currency;
331	}
332	$revenue = implode( ' / ', $revenue_parts );
333	return [
334	'items' => $items,
335	'count' => count( $items ),
336	'revenue' => $revenue,
337	];
338	} catch ( Exception $e ) {
339	return $default;
340	}
341	}
342	/**
343	* Get recovered revenue grouped by currency and return as a single concatenated string.
344	*
345	* @param array $recoveredOrderMeta Abandonment Meta.
346	* @return string e.g. "36.50-$, 30.50-€"
347	*/
348	public static function getRecoveredRevenueByRecoveredOrderMeta( $recoveredOrderMeta = [] ) {
349	if ( empty( $recoveredOrderMeta ) ) {
350	return '';
351	}
352	$ids = array_column( $recoveredOrderMeta, 'abandonment_id' );
353	$ids = implode( ',', $ids );
354	try {
355	$query = Fns::DB()::select()
356	->raw( "SUM( CAST( REGEXP_REPLACE(cart_total, '[^0-9.]', '') AS DECIMAL(10,2)) ) as total, REGEXP_REPLACE(cart_total, '[0-9.]+', '') as currency" )
357	->from( CartRecoveryFns::$ca_abandonment )
358	->whereIn( 'id', $ids )
359	->groupBy( 'currency' );
360	$results = $query->get();
361	$totals = [];
362	foreach ( $results as $row ) {
363	$currency = trim( $row['currency'] ?: '$' );
364	$totals[] = floatval( $row['total'] ) . $currency;
365	}
366	return implode( ' / ', $totals );
367	} catch ( Exception $e ) {
368	return '';
369	}
370	}
371
372	/**
373	* Get a single cart abandonment record with meta by a specific field.
374	*
375	* @param string $field Database column name (e.g., 'id', 'email', 'ca_session_id').
376	* @param mixed $value Value of the field to match.
377	* @return array Single abandonment record with meta, or empty array if not found.
378	* @since 1.0.0
379	*/
380	public static function getSingleAbandonment( string $field, $value ): array {
381	try {
382	$abandonments = Fns::DB()::select()
383	->raw( 't.*, GROUP_CONCAT(CONCAT(m.meta_key, ":", m.meta_value) SEPARATOR ",") AS meta_data' )
384	->from( CartRecoveryFns::$ca_abandonment . ' t' )
385	->leftJoin( CartRecoveryFns::$ca_abandonment_meta . ' m', 't.id', 'm.abandonment_id' )
386	->where( 't.' . $field, '=', $value )
387	->groupBy( 't.id' )
388	->orderBy( 't.id', 'DESC' )
389	->get();
390	if ( empty( $abandonments ) ) {
391	return [];
392	}
393	$abandonments = self::parseMetaData( $abandonments );
394	return $abandonments[0] ?? [];
395	} catch ( Exception $e ) {
396	return [];
397	}
398	}
399
400	/**
401	* Get a cart abandonment record by ID.
402	*
403	* Retrieves a single abandonment record by its unique ID and includes meta data.
404	*
405	* @param int $id Abandonment ID.
406	* @return array Abandonment record with meta data. Empty array if not found.
407	* @since 1.0.0
408	*/
409	public static function getCaAbandonmentByID( int $id ): array {
410	return self::getSingleAbandonment( 'id', absint( $id ) );
411	}
412
413	/**
414	* Get a cart abandonment record by user email.
415	*
416	* Retrieves a single abandonment record based on the email and includes meta data.
417	*
418	* @param string $email User email address.
419	* @return array Abandonment record with meta data. Empty array if not found or invalid email.
420	* @since 1.0.0
421	*/
422	public static function getCaAbandonmentByEmail( string $email ): array {
423	$email = sanitize_email( $email );
424	return ! empty( $email ) ? self::getSingleAbandonment( 'email', $email ) : [];
425	}
426
427	/**
428	* Get a cart abandonment record by session ID.
429	*
430	* Retrieves a single abandonment record based on the session ID and includes meta data.
431	*
432	* @param string $session_id User session ID.
433	* @return array Abandonment record with meta data. Empty array if not found or invalid session ID.
434	* @since 1.0.0
435	*/
436	public static function getCaAbandonmentBySessionId( string $session_id ): array {
437	$session_id = sanitize_text_field( $session_id );
438	return ! empty( $session_id ) ? self::getSingleAbandonment( 'ca_session_id', $session_id ) : [];
439	}
440
441	/**
442	* Delete checkout details and meta for a user session.
443	*
444	* @param string $session_id User session ID.
445	* @since 1.0.0
446	*/
447	public static function deleteCaAbandonmentBySessionId( $session_id ) {
448	try {
449	$session_id = sanitize_text_field( $session_id );
450	// Get abandonment ID first.
451	$abandonment = self::getCaAbandonmentBySessionId( $session_id );
452	$abandonment_id = $abandonment['id'] ?? 0;
453	// Delete meta records.
454	Fns::DB()::delete( CartRecoveryFns::$ca_abandonment_meta )
455	->where( 'abandonment_id', '=', absint( $abandonment_id ) )
456	->execute();
457	// Delete email history records.
458	Fns::DB()::delete( CartRecoveryFns::$ca_email_history )
459	->where( 'abandonment_id', '=', $abandonment_id )
460	->execute();
461	if ( ! $abandonment_id ) {
462	return false; // Nothing to delete.
463	}
464	// Delete main record.
465	return Fns::DB()::delete( CartRecoveryFns::$ca_abandonment )
466	->where( 'ca_session_id', '=', $session_id )
467	->execute();
468	} catch ( Exception $e ) {
469	return false;
470	}
471	}
472	/**
473	* Delete abandoned cart records for a given email with "normal" order status.
474	*
475	* This function looks up the abandoned cart sessions for the provided email
476	* where the order status is "normal" and deletes the corresponding record.
477	*
478	* @param string $email The customer's email address.
479	*
480	* @return bool\|mixed Returns the result of the deletion if a session is found, or false if no session exists.
481	*/
482	public static function deleteNormalAbandonedByEmail( $email ) {
483	$normal = Fns::DB()::select( 'ca_session_id' )
484	->from( CartRecoveryFns::$ca_abandonment )
485	->where( 'order_status', '=', 'normal' )
486	->andWhere( 'email', '=', $email )
487	->get();
488	$session_id = ! empty( $normal[0] ) ? $normal[0]['ca_session_id'] : '';
489	if ( ! empty( $session_id ) ) {
490	return self::deleteCaAbandonmentBySessionId( $session_id );
491	}
492	return false;
493	}
494	/**
495	* Get a cart abandonment record by ID.
496	*
497	* Retrieves a single abandonment record by its unique ID and includes meta data.
498	*
499	* @param int $id Abandonment ID.
500	* @since 1.0.0
501	*/
502	public static function deleteAbandonmentByID( $id ) {
503	$abandonment = self::getSingleAbandonment( 'id', absint( $id ) );
504	if ( ! empty( $abandonment['ca_session_id'] ) ) {
505	self::deleteCaAbandonmentBySessionId( $abandonment['ca_session_id'] );
506	}
507	$abandonment = self::getSingleAbandonment( 'id', absint( $id ) );
508	return empty( $abandonment );
509	}
510	/**
511	* Update an abandonment record in the database.
512	*
513	* Updates the specified record by applying the given key/value condition.
514	* Typically used with session ID or abandonment ID, but can work with
515	* any column name/value pair.
516	*
517	* @param string $whereKey The database column name for the condition.
518	* @param mixed $whereValue The value to match against the column.
519	* @param array $data Key-value pairs of data to update.
520	*
521	* @return array Number of rows updated on success, or false on failure.
522	*
523	* @since 1.0.0
524	*/
525	public static function updateCaAbandonment( $whereKey, $whereValue, $data = [] ) {
526	try {
527	if ( empty( $whereKey ) \|\| empty( $data ) ) {
528	return [];
529	}
530	$sanitized_data = CartRecoveryFns::sanitize_abandonment_data( $data );
531	unset( $sanitized_data['time'] );
532	$query = Fns::DB()::update( CartRecoveryFns::$ca_abandonment, $sanitized_data );
533	$query->where( $whereKey, '=', $whereValue )->execute();
534	return self::getSingleAbandonment( $whereKey, $whereValue );
535	} catch ( Exception $e ) {
536	return [];
537	}
538	}
539	/**
540	* Insert or update checkout details for a user session.
541	*
542	* @param array $data Data to insert/update.
543	* @since 1.0.0
544	*/
545	public static function insertCaAbandonment( $data ) {
546	try {
547	if ( empty( $data ) \|\| ! is_array( $data ) ) {
548	return false;
549	}
550	$meta_data = $data['meta_data'] ?? [];
551	$sanitized_data = CartRecoveryFns::sanitize_abandonment_data( $data );
552	$result = Fns::DB()::insert( CartRecoveryFns::$ca_abandonment, [ $sanitized_data ] )->execute();
553	// Get abandonment ID for meta update.
554	$abandonment_id = 0;
555	$session_lookup = $sanitized_data['ca_session_id'] ?? '';
556	if ( $session_lookup ) {
557	$abandonment_id = self::getCaAbandonmentBySessionId( $session_lookup )['id'] ?? 0;
558	}
559	if ( $abandonment_id && ! empty( $meta_data ) ) {
560	foreach ( $meta_data as $k => $val ) {
561	self::update_ca_abandonment_meta( $abandonment_id, $k, $val );
562	}
563	}
564	return $result;
565	} catch ( Exception $e ) {
566	return false;
567	}
568	}
569	/**
570	* Update checkout details for a user session.
571	*
572	* @param string $session_id User session ID.
573	* @param array $sanitized_data Data to update.
574	*/
575	public static function updateCaAbandonmentBySessionId( $session_id, $sanitized_data ) {
576	return self::updateCaAbandonment( 'ca_session_id', sanitize_text_field( $session_id ), $sanitized_data );
577	}
578
579	/**
580	* Update checkout details for a user session.
581	*
582	* @param int $id ID.
583	* @param array $sanitized_data Data to update.
584	*/
585	public static function updateCaAbandonmentById( $id, $sanitized_data ) {
586	return self::updateCaAbandonment( 'id', absint( $id ), $sanitized_data );
587	}
588
589	/**
590	* Update checkout details for a user session.
591	*
592	* @param int $id ID.
593	*/
594	public static function get_abandonment_details( $id ) {
595	$abandonment = self::getSingleAbandonment( 'id', absint( $id ) );
596	if ( empty( $abandonment ) ) {
597	return [
598	'abandonment' => [],
599	'email' => [],
600	];
601	}
602	$email = Fns::DB()::select()
603	->raw( 't., m.' )
604	->from( CartRecoveryFns::$ca_email . ' t' )
605	->leftJoin( CartRecoveryFns::$ca_email_history . ' m', 't.id', 'm.template_id' )
606	->where( 'm.abandonment_id', '=', $id )
607	->orderBy( 'm.scheduled_time', 'ASC' );
608	$result = $email->get();
609	$abandonment['cart_contents'] = maybe_unserialize( $abandonment['cart_contents'] );
610	return [
611	'abandonment' => $abandonment,
612	'email' => $result,
613	];
614	}
615
616	/**
617	* Insert or update email abandonment meta.
618	*
619	* @param int $abandonment_id abandonment ID.
620	* @param string $key Meta key.
621	* @param mixed $value Meta value.
622	*
623	* @since 1.0.0
624	* @return void
625	*/
626	public static function update_ca_abandonment_meta( $abandonment_id, $key, $value ) {
627	self::upsert_meta( CartRecoveryFns::$ca_abandonment_meta, 'abandonment_id', $abandonment_id, $key, $value );
628	}
629	/**
630	* Insert or update email abandonment meta.
631	*
632	* @param int $abandonment_id abandonment ID.
633	* @param string $key Meta key.
634	* @param mixed $value Meta value.
635	*
636	* @since 1.0.0
637	*/
638	public static function add_ca_abandonment_meta( $abandonment_id, $key, $value ) {
639	self::insert_meta( CartRecoveryFns::$ca_abandonment_meta, 'abandonment_id', $abandonment_id, $key, $value );
640	}
641
642	/**
643	* Delete abandonment meta by key (and optional value).
644	*
645	* @param int $abandonment_id Abandonment ID.
646	* @param string $key Meta key.
647	* @param string\|null $value Optional meta value to match.
648	* @return void
649	*/
650	public static function delete_abandonment_meta( $abandonment_id, $key, $value = null ) {
651	$query = Fns::DB()::delete( CartRecoveryFns::$ca_abandonment_meta )
652	->where( 'abandonment_id', '=', absint( $abandonment_id ) )
653	->andWhere( 'meta_key', '=', $key );
654	if ( $value ) {
655	$query->andWhere( 'meta_value', '=', $value );
656	}
657	$query->execute();
658	}
659	/**
660	* Delete unnecessery email history
661	*
662	* @param int $abandonment_id Abandonment ID.
663	*
664	* @return bool
665	*/
666	public static function delete_unnecessery_email_history_for_abandonment( $abandonment_id ) {
667	try {
668	return Fns::DB()::delete( CartRecoveryFns::$ca_email_history )
669	->where( 'abandonment_id', '=', $abandonment_id )
670	->andWhere( 'email_sent', '=', 0 )
671	->execute();
672	} catch ( Exception $e ) {
673	return false;
674	}
675	}
676	/**
677	* Get abandoned cart data.
678	*/
679	public static function get_normal_abandoned() {
680	$wp_current_datetime = gmdate( 'Y-m-d H:i:s', Fns::currentTimestampUTC() );
681	$minutes = absint( CartRecoveryFns::get_options( 'abandoned_time', 20 ) );
682	$normal = Fns::DB()::select( '*' )
683	->from( CartRecoveryFns::$ca_abandonment )
684	->where( 'order_status', '=', 'normal' )
685	->raw( "AND ADDDATE(time, INTERVAL '{$minutes}' MINUTE) <= '{$wp_current_datetime}' " )
686	->orderBy( 'id', 'DESC' )
687	->get();
688	return $normal;
689	}
690
691	/**
692	* Get active templates only
693	*
694	* @return array Array of active template objects.
695	*/
696	public static function getActiveTemplatesSecuence() {
697	try {
698	$templates = Fns::DB()::select( '*' )
699	->from( CartRecoveryFns::$ca_email )
700	->where( 'is_activated', '=', 'on' )
701	->raw(
702	"ORDER BY CASE UPPER(frequency_unit)
703	WHEN 'MINUTE' THEN 1
704	WHEN 'HOUR' THEN 2
705	WHEN 'DAY' THEN 3
706	ELSE 4
707	END ASC"
708	)
709	->get();
710	return $templates ?: [];
711
712	} catch ( Exception $e ) {
713	return [];
714	}
715	}
716	/**
717	* Scheduled Email
718	*
719	* @return mixed
720	*/
721	public static function get_scheduled_email_history() {
722	$wp_current_datetime = gmdate( 'Y-m-d H:i:s', Fns::currentTimestampUTC() );
723	return Fns::DB()::select( '*' )
724	->from( CartRecoveryFns::$ca_email_history )
725	->where( 'email_sent', '=', 0 )
726	->andWhere( 'scheduled_time', '<=', $wp_current_datetime )
727	->groupBy( 'abandonment_id' )
728	->orderBy( 'scheduled_time', 'ASC' )
729	->get();
730	}
731
732	/**
733	* Get an email history record by ID.
734	*
735	* @param int $id Record ID.
736	* @return array Record object or null.
737	*/
738	public static function get_history_by_id( $id ) {
739	$result = Fns::DB()::select( '*' )
740	->from( CartRecoveryFns::$ca_email_history )
741	->where( 'id', '=', $id )
742	->get();
743	return $result[0] ?? [];
744	}
745	/**
746	* @param int $history_id Email History ID.
747	* @param array $data update column.
748	* @return void
749	*/
750	public static function update_scheduled_email_history( $history_id, $data ) {
751	Fns::DB()::update( CartRecoveryFns::$ca_email_history, $data )->where( 'id', '=', absint( $history_id ) )->execute();
752	}
753
754	/**
755	* @param int $template_id Email History ID.
756	* @param string $meta_key Meta Key.
757	* @param mixed $default default value.
758	* @return mixed
759	*/
760	public static function get_email_template_meta( $template_id, $meta_key, $default = null ) {
761	$getMetaData = Fns::DB()::select( 'meta_value' )
762	->from( CartRecoveryFns::$ca_email_meta )
763	->where( 'email_template_id', '=', $template_id )
764	->andWhere( 'meta_key', '=', $meta_key )
765	->get();
766	return $getMetaData[0]['meta_value'] ?? $default;
767	}
768
769	/**
770	* @param string $startDate Number of records per page.
771	* @param string $endDate Page number.
772	* @return mixed
773	*/
774	public static function getRecoveredOrderMeta( $startDate, $endDate ) {
775	$getMetaData = Fns::DB()::select( 'abandonment_id', 'meta_value' )
776	->from( CartRecoveryFns::$ca_abandonment_meta )
777	->where( 'meta_key', '=', 'completed_time' )
778	->andWhere( 'meta_value', '>=', $startDate )
779	->andWhere( 'meta_value', '<=', $endDate )
780	->groupBy( 'abandonment_id' )
781	->get();
782	if ( is_array( $getMetaData ) && count( $getMetaData ) ) {
783	return $getMetaData;
784	}
785	return [];
786	}
787	/**
788	* @param string $startDate Number of records per page.
789	* @param string $endDate Page number.
790	* @return mixed
791	*/
792	public static function getLostOrderMeta( $startDate, $endDate ) {
793	$getMetaData = Fns::DB()::select( 'abandonment_id', 'meta_value' )
794	->from( CartRecoveryFns::$ca_abandonment_meta )
795	->where( 'meta_key', '=', 'lost_time' )
796	->andWhere( 'meta_value', '>=', $startDate )
797	->andWhere( 'meta_value', '<=', $endDate )
798	->groupBy( 'abandonment_id' )
799	->get();
800	if ( is_array( $getMetaData ) && count( $getMetaData ) ) {
801	return $getMetaData;
802	}
803	return [];
804	}
805	/**
806	* @param int $template_id Email History ID.
807	* @param string $meta_key Meta Key.
808	* @param mixed $andValue Meta Value.
809	* @return mixed
810	*/
811	public static function count_email_template_by_meta_value( $template_id, $meta_key, $andValue = '' ) {
812	$query = Fns::DB()::select( 'id' )
813	->from( CartRecoveryFns::$ca_email_meta )
814	->where( 'email_template_id', '=', $template_id )
815	->andWhere( 'meta_key', '=', $meta_key )
816	->andWhere( 'meta_value', '=', $andValue );
817	$getMetaData = $query->get();
818	return is_array( $getMetaData ) ? count( $getMetaData ) : 0;
819	}
820	/**
821	* Insert or update email template meta.
822	*
823	* @param int $template_id Template ID.
824	* @param string $key Meta key.
825	* @param mixed $value Meta value.
826	*
827	* @since 1.0.0
828	*/
829	public static function add_email_template_meta( $template_id, $key, $value ) {
830	self::insert_meta( CartRecoveryFns::$ca_email_meta, 'email_template_id', $template_id, $key, $value );
831	}
832
833	/**
834	* Insert or update email template meta.
835	*
836	* @param int $template_id Template ID.
837	* @param string $key Meta key.
838	* @param mixed $value Meta value.
839	*
840	* @since 1.0.0
841	*/
842	public static function update_email_template_meta( $template_id, $key, $value ) {
843	self::upsert_meta( CartRecoveryFns::$ca_email_meta, 'email_template_id', $template_id, $key, $value );
844	}
845	/**
846	* Get the most recent scheduled email for an abandonment ID.
847	*/
848	public static function lost_abandonment_detection() {
849	$wp_current_datetime = gmdate( 'Y-m-d H:i:s', Fns::currentTimestampUTC() );
850	$getLostItems = Fns::DB()->select( 't.id' )
851	->from( CartRecoveryFns::$ca_abandonment . ' t' )
852	->leftJoin( CartRecoveryFns::$ca_abandonment_meta . ' m', 't.id', 'm.abandonment_id' )
853	->where( 't.order_status', '!=', 'lost' )
854	->andWhere( 'm.meta_key', '=', 'lost_time' )
855	->andWhere( 'm.meta_value', '<=', $wp_current_datetime )
856	->groupBy( 'abandonment_id' )
857	->get();
858	if ( empty( $getLostItems ) ) {
859	return;
860	}
861	foreach ( $getLostItems as $item ) {
862	if ( empty( $item['id'] ) ) {
863	continue;
864	}
865	self::updateCaAbandonmentById( $item['id'], [ 'order_status' => 'lost' ] );
866	self::delete_unnecessery_email_history_for_abandonment( $item['id'] );
867	}
868	}
869	/**
870	* Retrieve abandonment meta records by meta key and meta value.
871	*
872	* This function fetches records from the abandonment meta table
873	* where the meta key and meta value match the given parameters.
874	*
875	* @param int $abandonment_id abandonment id to match.
876	* @param string $meta_key meta key to match.
877	* @param mixed $default default value.
878	*
879	* @return false\|array List of results with abandonment_id and meta_value.
880	*/
881	public static function get_abandonment_meta( $abandonment_id, $meta_key, $default = null ) {
882	if ( empty( $abandonment_id ) \|\| empty( $meta_key ) ) {
883	return false;
884	}
885	$query = Fns::DB()
886	->select( 'meta_value' )
887	->from( CartRecoveryFns::$ca_abandonment_meta )
888	->where( 'abandonment_id', '=', $abandonment_id )
889	->andWhere( 'meta_key', '=', $meta_key );
890	$result = $query->get();
891	return $result[0]['meta_value'] ?? $default;
892	}
893	/**
894	* @param int $abandonment_id abandonment ID.
895	* @param string $meta_key Meta Key.
896	* @param mixed $andValue Meta Value.
897	* @return mixed
898	*/
899	public static function count_abandonment_by_meta_value( $abandonment_id, $meta_key, $andValue = '' ) {
900	$query = Fns::DB()::select( 'id' )
901	->from( CartRecoveryFns::$ca_abandonment_meta )
902	->where( 'abandonment_id', '=', $abandonment_id )
903	->andWhere( 'meta_key', '=', $meta_key )
904	->andWhere( 'meta_value', '=', $andValue );
905	$getMetaData = $query->get();
906	return is_array( $getMetaData ) ? count( $getMetaData ) : 0;
907	}
908
909	/**
910	* Reschedule emails for cart abandonment.
911	*
912	* @param int $abandonment_id Abandonment ID.
913	*
914	* @return void
915	*/
916	public static function reshedule_email( $abandonment_id ) {
917	$templates = self::getActiveTemplatesSecuence();
918	if ( empty( $templates ) ) {
919	return;
920	}
921	$result = CartRecoveryFns::prepare_email_history_data( $templates, $abandonment_id );
922	if ( ! empty( $result['history'] ) ) {
923	Fns::DB()::insert( CartRecoveryFns::$ca_email_history, $result['history'] )->execute();
924	$expireTimestamp = $result['max_scheduled'] + ( 7 * DAY_IN_SECONDS );
925	$lost_time = gmdate( 'Y-m-d H:i:s', $expireTimestamp );
926	self::update_ca_abandonment_meta( $abandonment_id, 'lost_time', $lost_time );
927	}
928	}
929
930	/**
931	* Update normal carts to abandoned status and schedule emails.
932	*
933	* @return array Updated abandonment IDs.
934	*/
935	public static function update_to_abandoned() {
936	$abandoned = self::get_normal_abandoned();
937	if ( empty( $abandoned ) ) {
938	return [];
939	}
940	$ids = [];
941	$allHistory = [];
942	$templates = self::getActiveTemplatesSecuence();
943	$currentTime = Fns::currentTimestampUTC();
944	foreach ( $abandoned as $item ) {
945	$ids[] = $item['id'];
946	Fns::DB()::update(
947	CartRecoveryFns::$ca_abandonment,
948	[ 'order_status' => 'abandoned' ]
949	)
950	->where( 'order_status', '=', 'normal' )
951	->andWhere( 'id', '=', absint( $item['id'] ) )
952	->execute();
953	if ( ! empty( $templates ) ) {
954	$result = CartRecoveryFns::prepare_email_history_data( $templates, $item['id'], $currentTime );
955	if ( ! empty( $result['history'] ) ) {
956	$allHistory = array_merge( $allHistory, $result['history'] );
957	}
958	if ( $result['max_scheduled'] ) {
959	$expireTimestamp = $result['max_scheduled'] + ( 7 * DAY_IN_SECONDS );
960	$lost_time = gmdate( 'Y-m-d H:i:s', $expireTimestamp );
961	self::update_ca_abandonment_meta( $item['id'], 'lost_time', $lost_time );
962	}
963	}
964	}
965	if ( ! empty( $allHistory ) ) {
966	Fns::DB()::insert( CartRecoveryFns::$ca_email_history, $allHistory )->execute();
967	}
968	return $ids;
969	}
970	/**
971	* Delete a scheduled email record by its ID.
972	*
973	* This method removes the specified scheduled email entry from
974	* the email history table in the database.
975	*
976	* @param int $sheduled_id The ID of the scheduled email record to delete.
977	*
978	* @return void
979	*/
980	public static function delete_sheduled( $sheduled_id ) {
981	// Delete email history records.
982	Fns::DB()::delete( CartRecoveryFns::$ca_email_history )
983	->where( 'id', '=', $sheduled_id )
984	->execute();
985	}
986	}
987