wpdb = $wpdb ?? $GLOBALS['wpdb']; } /** * Get table name. * * @return string */ public function getTableName(): string { return $this->wpdb->prefix . self::TABLE; } /** * Insert or update cart row for a session (cart_data is JSON text). * * @param string $token Cart token. * @param string $session_id WooCommerce customer session id. * @param int|null $user_id WP user id or null for guest. * @param string|null $phone Normalized phone or null. * @param string $email Billing / capture email. * @param string $cart_data JSON-encoded cart snapshot. * @param string|null $last_activity MySQL datetime or null to use now on insert only. * @return int|false Row id or false. */ public function upsert( string $token, string $session_id, ?int $user_id, ?string $phone, string $email, string $cart_data, ?string $last_activity = null ) { $table = $this->getTableName(); $wpdb = $this->wpdb; $now = current_time( 'mysql', true ); $activity = $last_activity ?? $now; $existing = $wpdb->get_row( $wpdb->prepare( "SELECT id, user_id, phone, email FROM %i WHERE (session_id = %s OR (token = %s and token != '')) AND recovered = 0 LIMIT 1", $table, $session_id, $token ) ); if ( $existing ) { $id = (int) $existing->id; $data = array( 'cart_data' => $cart_data, 'last_activity' => $activity, ); $format = array( '%s', '%s' ); // Only update these fields when we have new values; otherwise preserve existing DB values. if ( '' !== $email ) { $data['email'] = $email; $format[] = '%s'; } if ( null !== $user_id && $user_id > 0 ) { $data['user_id'] = $user_id; $format[] = '%d'; } if ( null !== $phone && '' !== $phone ) { $data['phone'] = $phone; $format[] = '%s'; } if ( '' !== $token ) { $data['token'] = $token; $format[] = '%s'; } $wpdb->update( $table, $data, array( 'id' => $id ), $format, array( '%d' ) ); return $id; } $insert = array( 'token' => $token, 'session_id' => $session_id, 'email' => $email, 'cart_data' => $cart_data, 'created_at' => $now, 'last_activity' => $activity, 'recovered' => 0, ); $format = array( '%s', '%s', '%s', '%s', '%s', '%s', '%d' ); if ( null !== $user_id && $user_id > 0 ) { $insert['user_id'] = $user_id; $format[] = '%d'; } if ( null !== $phone && '' !== $phone ) { $insert['phone'] = $phone; $format[] = '%s'; } $result = $wpdb->insert( $table, $insert, $format ); return $result ? (int) $wpdb->insert_id : false; } /** * Find non-recovered carts older than given minutes (by created_at). * * @param int $minutes Abandonment threshold in minutes. * @param int $limit Max records. * @return array */ public function findPendingOlderThan( int $minutes = 60, int $limit = 20 ): array { $table = $this->getTableName(); $wpdb = $this->wpdb; $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( "-{$minutes} minutes" ) ); $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE status = %s AND last_activity <= %s AND recovered = 0 ORDER BY created_at ASC LIMIT %d", $table, AbandonedCartService::STATUS_PENDING, $cutoff, $limit ) ); return is_array( $results ) ? $results : array(); } /** * Remove row after abandoned-cart workflow was dispatched (avoids duplicate events without extra columns). * * @param int $id Record ID. * @return bool */ public function delete( int $id ): bool { $table = $this->getTableName(); return (bool) $this->wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) ); } /** * Mark carts as recovered when an order completes (match user, phone, or email). * * @param int $user_id WordPress / WC customer user id (0 = guest). * @param string $phone Normalized billing phone (may be empty). * @param string $email Billing email (may be empty). * @param string $session_id WooCommerce customer session id. * @param string $token Cart token. * @return void */ public function markRecoveredForOrder( int $user_id, string $phone, string $email, string $session_id, string $token ): void { $table = $this->getTableName(); $wpdb = $this->wpdb; $email = sanitize_email( $email ); if ( $user_id > 0 ) { $wpdb->update( $table, array( 'recovered' => 1 ), array( 'user_id' => $user_id, 'recovered' => 0, ), array( '%d' ), array( '%d', '%d' ) ); } if ( '' !== $phone ) { $wpdb->update( $table, array( 'recovered' => 1 ), array( 'phone' => $phone, 'recovered' => 0, ), array( '%d' ), array( '%s', '%d' ) ); } if ( '' !== $email ) { $wpdb->update( $table, array( 'recovered' => 1 ), array( 'email' => $email, 'recovered' => 0, ), array( '%d' ), array( '%s', '%d' ) ); } if ( '' !== $session_id ) { $wpdb->update( $table, array( 'recovered' => 1 ), array( 'session_id' => $session_id, 'recovered' => 0, ), array( '%d' ), array( '%s', '%d' ) ); } if ( '' !== $token ) { $wpdb->update( $table, array( 'recovered' => 1 ), array( 'token' => $token, 'recovered' => 0, ), array( '%d' ), array( '%s', '%d' ) ); } } /** * Get an abandoned cart by ID. * * @param int $abandoned_cart_id Abandoned cart ID. * @return array|null */ public function get( int $abandoned_cart_id ): ?array { $table = $this->getTableName(); $wpdb = $this->wpdb; return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE id = %d AND recovered = 0 LIMIT 1", $table, $abandoned_cart_id ), ARRAY_A ); } public function updateStatus( int $abandoned_cart_id, string $status ): bool { $table = $this->getTableName(); return (bool) $this->wpdb->update( $table, array( 'status' => $status ), array( 'id' => $abandoned_cart_id ), array( '%s' ), array( '%d' ) ); } public function getByToken( string $token ) { $table = $this->getTableName(); $wpdb = $this->wpdb; return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM %i WHERE token = %s AND recovered = 0 LIMIT 1", $table, $token ), OBJECT ); } /** * Point a recovered token at the current WooCommerce session (cross-device restore). * * @param string $token Cart token. * @param string $session_id Current WooCommerce customer session id. * @return bool */ public function bindTokenToSession( string $token, string $session_id ): bool { $table = $this->getTableName(); return (bool) $this->wpdb->update( $table, array( 'session_id' => $session_id, 'last_activity' => current_time( 'mysql', true ), ), array( 'token' => $token, 'recovered' => 0, ), array( '%s', '%s' ), array( '%s', '%d' ) ); } }