<?php

namespace AffiAffiliate\Inc;

use WP_Query;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class QueryDB {

	protected static $instance = null;

	public $affi_affiliates;
	public $affi_track_referrals;
	public $affi_notifications;
	public $affi_user_notifications;
	public $affi_ranks;
	public $affi_payouts;
	public $affi_visits;
	public $wp_users;

	public $cart_meta;
	public $wpdb;

	protected $settings;

	private function __construct() {
		global $wpdb;
		$this->wpdb                    = $wpdb;
		$this->affi_affiliates         = $wpdb->prefix . "affi_affiliates";
		$this->affi_track_referrals    = $wpdb->prefix . "affi_track_referrals";
		$this->affi_notifications      = $wpdb->prefix . "affi_notifications";
		$this->affi_user_notifications = $wpdb->prefix . "affi_user_notifications";
		$this->affi_ranks              = $wpdb->prefix . "affi_ranks";
		$this->affi_payouts            = $wpdb->prefix . "affi_payouts";
		$this->affi_visits             = $wpdb->prefix . "affi_visits";
		$this->wp_users                = $wpdb->base_prefix . 'users';
		$this->settings                = Data::instance();
	}

	public static function instance() {
		if ( null == self::$instance ) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	public function get_referral( $id ) {
		if ( $id ) {
			global $wpdb;
			$rs   = $wpdb->prepare( "SELECT `id`,`refferal_wp_uid`,`campaign`,`affiliate_id`,`visit_id`,`description`,`source`,
										`reference`,`reference_details`,`parent_referral_id`,`child_referral_id`,`amount`,`currency`,
										`date`,`status`,`payment` FROM %i WHERE id=%d ", $this->affi_track_referrals, $id );
			$data = $wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			if ( $data ) {

				return (array) $data;
			}

			return array();
		} else {

			return array(
				'id'                 => 0,
				'refferal_wp_uid'    => '',
				'campaign'           => '',
				'affiliate_id'       => '',
				'visit_id'           => '',
				'description'        => '',
				'source'             => '',
				'reference'          => '',
				'reference_details'  => '',
				'parent_referral_id' => '',
				'child_referral_id'  => '',
				'amount'             => 0,
				'currency'           => 'USD',
				'date'               => '',
				'status'             => 1,//unverified
				'payment'            => 0,
			);
		}
	}

	public function referral_get_parent( $referral_id = 0 ) {
		if ( $referral_id ) {
			$rs   = $this->wpdb->prepare( "SELECT parent_referral_id FROM %i WHERE id=%d;", $this->affi_track_referrals, $referral_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data->parent_referral_id ) ) {

				return $data->parent_referral_id;
			} else {

				return 0;
			}
		}

		return 0;
	}

	public function referral_get_child( $referral_id = 0 ) {
		if ( $referral_id ) {
			$table = $this->wpdb->prefix . 'uap_referrals';
			$rs    = $this->wpdb->prepare( "SELECT child_referral_id FROM %i WHERE id=%d;", $this->affi_track_referrals, $referral_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data  = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data->child_referral_id ) ) {

				return $data->child_referral_id;
			} else {

				return 0;
			}
		}

		return 0;
	}

	public function change_referral_status( $id = 0, $new_status = 0, $search_parent = true, $search_child = true ) {
		$data = $this->get_referral( $id );
		if ( ! empty( $data ) && isset( $data['status'] ) ) {
			$data['status'] = $new_status;
			$this->save_referral( $data );
		}
		/// MLM
		if ( get_option( 'uap_mlm_enable' ) ) {
			if ( $search_parent ) {
				$parent = $this->referral_get_parent( $id );
				if ( $parent ) {
					$this->change_referral_status( $parent, $new_status, true, false );
				}
			}
			if ( $search_child ) {
				$child = $this->referral_get_child( $id );
				if ( $child ) {
					$this->change_referral_status( $child, $new_status, false, true );
				}
			}
		}
	}

	public function get_referral_id_for_reference( $reference = '', $source = '' ) {
		if ( $reference ) {
			$rs = $this->wpdb->prepare( "SELECT id FROM %i WHERE reference=%s ", $this->affi_track_referrals, $reference );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $source ) ) {
				$rs .= $this->wpdb->prepare( " AND source=%s ", $source );
			}
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data->id ) ) {
				return $data->id;
			}
		}

		return 0;
	}

	public function update_visit_referral_id( $visit_id = 0, $referral_id = 0 ) {
		if ( $visit_id && $referral_id ) {
			$rs     = $this->wpdb->prepare( "SELECT * FROM %i WHERE id=%d ", $this->affi_visits, $visit_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$exists = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( $exists ) {
				$rs = $this->wpdb->prepare( "UPDATE %i SET referral_id=%d WHERE id=%d ", $this->affi_visits, $referral_id, $visit_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$this->wpdb->query( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			}
		}
	}

	public function save_referral( $post_data = array() ) {
		global $wpdb;
		$stop = false;
		$stop = apply_filters( 'affi_filter_insert_referral', $stop, $post_data );
		if ( $stop ) {
			return 0;
		}
		if ( ! empty( $post_data ) ) {

			if ( empty( $post_data['date'] ) ) {
				$post_data['date'] = current_time( 'Y-m-d H:i:s' );//date('Y-m-d H:i:s', time());

			}

			////// MAX AMOUNT
			if ( ! empty( $post_data['id'] ) ) {
				$rs   = $wpdb->prepare( "SELECT `id`,`refferal_wp_uid`,`campaign`,`affiliate_id`,`visit_id`,`description`,
												`source`,`reference`,`reference_details`,`parent_referral_id`,`child_referral_id`,
												`amount`,`currency`,`date`,`status`,`payment` FROM %i WHERE id=%d ", $this->affi_track_referrals, $post_data['id'] );
				$data = $wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				if ( ! empty( $data ) ) {
					$post_data = apply_filters( 'affi_update_referral_filter', $post_data );

					/// UPDATE
					$rs = $wpdb->prepare( "UPDATE %i SET
													refferal_wp_uid=%d,
													campaign=%s,
													affiliate_id=%d,
													visit_id=%d,
													description=%s,
													source=%s,
													reference=%s,
													reference_details=%s,
													parent_referral_id=%d,
													child_referral_id=%d,
													amount=%s,
													currency=%s,
													date=%s,
													status=%s,
													payment=%s
												WHERE id=%d
								;", $this->affi_track_referrals, $post_data['refferal_wp_uid'], $post_data['campaign'], $post_data['affiliate_id'],
						$post_data['visit_id'], $post_data['description'], $post_data['source'], $post_data['reference'],
						$post_data['reference_details'], $post_data['parent_referral_id'], $post_data['child_referral_id'],
						$post_data['amount'], $post_data['currency'], $post_data['date'], $post_data['status'], $post_data['payment'],
						$post_data['id']
					);
					$wpdb->query( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

					return $post_data['id'];
				}
			}
			// ----------- already exists with another id ---------------
			$query = $wpdb->prepare( "SELECT `id` FROM %i WHERE affiliate_id=%d
											AND
											refferal_wp_uid=%d
											AND
											visit_id=%d
											AND
											source=%s
											AND
											reference=%s
											AND
											reference_details=%s
											AND
											amount=%s ", $this->affi_track_referrals,
				$post_data['affiliate_id'], $post_data['refferal_wp_uid'], $post_data['visit_id'],
				$post_data['source'], $post_data['reference'], $post_data['reference_details'], $post_data['amount']
			);
			$old   = $wpdb->get_var( $query );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			if ( $old !== null && $old !== false && $old > 0 ) {
				// do update
				$post_data = apply_filters( 'affi_update_referral_filter', $post_data );
				$rs        = $wpdb->prepare( "UPDATE %i SET
												campaign=%s,
												description=%s,
												parent_referral_id=%d,
												child_referral_id=%d,
												date=%s,
												status=%s,
												payment=%s
											WHERE id=%d
							;", $this->affi_track_referrals, $post_data['campaign'], $post_data['description'], $post_data['parent_referral_id'],
					$post_data['child_referral_id'], $post_data['date'], $post_data['status'], $post_data['payment'],
					$old
				);
				$wpdb->query( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

				return $old;
			}

			// ----------- end of already exists with another id ---------------

			$post_data = apply_filters( 'affi_save_referral_filter', $post_data );
			/// SAVE
			$rs = $wpdb->prepare( "INSERT INTO %i VALUES( NULL, 
												%d, 
												%s, 
												%d,
												%d,
												%s,
												%s,
												%s,
												%s,
												%d,
												%d,
												%s,
												%s,
												%s,
												%s,
												%s );
					", $this->affi_track_referrals, $post_data['refferal_wp_uid'], $post_data['campaign'], $post_data['affiliate_id'],
				$post_data['visit_id'], $post_data['description'], $post_data['source'], $post_data['reference'],
				$post_data['reference_details'], $post_data['parent_referral_id'], $post_data['child_referral_id'],
				$post_data['amount'], $post_data['currency'], $post_data['date'], $post_data['status'], $post_data['payment']
			);
			$wpdb->query( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$referralId = isset( $wpdb->insert_id ) ? $wpdb->insert_id : 0;

			do_action( 'affi_public_action_save_referral', $post_data );

//			/// AFFILIATE NOTIFICATION ON EVERY REFERRAL
//			if (!class_exists('AffiliateNotificationReports')){
//				require_once UAP_PATH . 'classes/AffiliateNotificationReports.class.php';
//			}
//			$object = new AffiliateNotificationReports();
//			$object->send_single_referral_notification($post_data['affiliate_id'], $referralId, $post_data['source']);
//
//			/// INCREMENT DASHBOARD NOTIFICATION COUNT
//			$this->increment_dashboard_notification('referrals');

			return $referralId;
		}

		return false;
	}

	public function is_affiliate_active( $id ) {
		if ( $id ) {
			$rs   = $this->wpdb->prepare( "SELECT status FROM %i WHERE id=%d ;", $this->affi_affiliates, $id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( $data->status == 'approved' ) {

				return true;
			}
		}

		return false;
	}

	public function get_all_affiliate_for_rank() {
		$rs   = $this->wpdb->prepare( 'SELECT id, user_id, rank, earning, balance FROM %i WHERE 1', $this->affi_affiliates );
		$data = $this->wpdb->get_results( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return $data;
	}

	public function get_affiliate_id_by_user_id( $user_id = 0 ) {
		if ( $user_id ) {
			$rs   = $this->wpdb->prepare( "SELECT id FROM %i WHERE user_id=%d;", $this->affi_affiliates, $user_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data->id ) ) {

				return (int) $data->id;
			}
		}

		return 0;
	}

	public function get_affiliate_by_user_id( $user_id = 0 ) {
		if ( $user_id ) {
			$rs   = $this->wpdb->prepare( "SELECT * FROM %i WHERE user_id=%d;", $this->affi_affiliates, $user_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data ) ) {

				return $data;
			}
		}

		return 0;
	}

	public function get_affiliate_by_id( $aff_id = '' ) {
		if ( $aff_id ) {
			$rs   = $this->wpdb->prepare( "SELECT * FROM %i WHERE id=%s ;", $this->affi_affiliates, $aff_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data ) ) {

				return $data;
			}
		}

		return '';
	}

	public function get_affiliate_id_by_username( $user_name = '' ) {
		if ( $user_name ) {
			$rs   = $this->wpdb->prepare( "SELECT aff.id FROM %i aff INNER JOIN %i us ON aff.user_id=us.ID WHERE us.user_login=%s ;", $this->affi_affiliates, $this->wp_users, $user_name );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data->id ) ) {

				return $data->id;
			}
		}

		return 0;
	}

	public function get_aff_user_data_by_affiliate_id( $aff_id ) {
		if ( $aff_id ) {
			$rs   = $this->wpdb->prepare( "SELECT aff.*, us.user_login, us.user_nicename, us.display_name FROM %i aff INNER JOIN %i us ON aff.user_id=us.ID WHERE aff.id=%s ;", $this->affi_affiliates, $this->wp_users, $aff_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data ) ) {

				return $data;
			}
		}

		return 0;
	}

	public function save_referral_from_admin( $post_data = array() ) {
		if ( ! empty( $post_data ) ) {
			$referral_id = $this->save_referral( $post_data );

			/// UPDATE VISITS TABLE
			if ( ! empty( $post_data['visit_id'] ) ) {
				$rs = $this->wpdb->prepare( 'UPDATE %i SET referral_id=%d WHERE id=%d', $this->affi_visits, $referral_id, $post_data['visit_id'] );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$this->wpdb->query( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			}

			return true;
		}

		return false;
	}

	public function get_ranks( $args = array() ) {
		$args = wp_parse_args( $args, array(
			'fields'  => '*',
			'where'   => '',
			'limit'   => 99,
			'offset'  => 0,
			'orderby' => 'data_created',
			'order'   => 'DESC',
		) );
		/*if ( isset( $args['fields'] ) && is_array( $args['fields'] ) ) {
			$args['fields'] = implode( ', ', $args['fields'] );
		}*/
		$query = "SELECT * FROM %i WHERE 1 LIMIT %d OFFSET %d ";

		return $this->wpdb->get_results( $this->wpdb->prepare( $query, $this->affi_ranks, $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	public function get_username_by_affiliate_id( $affiliate_id = 0 ) {
		if ( $affiliate_id ) {
			$rs   = $this->wpdb->prepare( "SELECT us.user_login FROM %i aff INNER JOIN %i us ON aff.user_id=us.ID WHERE aff.id=%d; ", $this->affi_affiliates, $this->wp_users, $affiliate_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data->user_login ) ) {

				return $data->user_login;
			}
		}

		return '';
	}

	public function get_user_data_by_id( $user_id = 0 ) {
		if ( $user_id ) {
			$rs   = $this->wpdb->prepare( "SELECT * FROM %i WHERE ID=%d; ", $this->wp_users, $user_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data ) ) {

				return $data;
			}
		}

		return '';
	}

	public function get_user_display_name_by_affiliate_id( $affiliate_id = 0 ) {
		if ( $affiliate_id ) {
			$rs   = $this->wpdb->prepare( "SELECT us.display_name FROM %i aff INNER JOIN %i us ON aff.user_id=us.ID WHERE aff.id=%d;", $this->affi_affiliates, $this->wp_users, $affiliate_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data->display_name ) ) {

				return $data->display_name;
			}
		}

		return '';
	}

	public function get_order_affiliate_data( $affiliate_id ) {
		if ( $affiliate_id ) {
			$rs     = $this->wpdb->prepare( "SELECT rk.rate, rk.rate_type FROM %i af INNER JOIN %i rk ON af.rank=rk.id WHERE af.id=%d ", $this->affi_affiliates, $this->affi_ranks, $affiliate_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$a_data = $this->wpdb->get_row( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

			return $a_data;
		}

		return [];
	}

	public function get_affiliates_for_checkout_select( $who = '', $type_of_name = '' ) {
		$array = array();
		if ( ! $type_of_name ) {
			return $array;
		}

		$allowed_columns = array( 'user_login', 'display_name', 'user_nicename', 'user_email' );
		if ( ! in_array( $type_of_name, $allowed_columns, true ) ) {
			$type_of_name = 'display_name';
		}

		$prepare_args = array( $this->wp_users, $this->affi_affiliates );
		$rs           = "SELECT a.id as id, u.{$type_of_name} as name FROM %i u INNER JOIN %i a ON u.ID=a.user_id WHERE 1";
		if ( $who !== '' && strpos( $who, '-1' ) === false ) {
			$affiliate_ids = array_filter( array_map( 'absint', explode( ',', $who ) ) );
			if ( ! empty( $affiliate_ids ) ) {
				$placeholders = implode( ', ', array_fill( 0, count( $affiliate_ids ), '%d' ) );
				$rs          .= " AND a.id IN ({$placeholders})";
				$prepare_args = array_merge( $prepare_args, $affiliate_ids );
			}
		}

		$data = $this->wpdb->get_results( $this->wpdb->prepare( $rs, $prepare_args ) );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		if ( $data ) {
			foreach ( $data as $object ) {
				$array[ $object->id ] = $object->name;
			}
		}

		return $array;
	}

	public function get_rank_by_id( $r_id = '' ) {
		if ( $r_id ) {
			$rs   = $this->wpdb->prepare( "SELECT * FROM %i WHERE id=%s ;", $this->affi_ranks, $r_id );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data ) ) {

				return $data;
			}
		}

		return '';
	}

	public function get_basic_rank() {
		//update to unset rank
		return 'empty';
		$rs   = "SELECT id FROM %i WHERE `order`=(SELECT MIN(`order`) from %i);";// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$data = $this->wpdb->get_row( $this->wpdb->prepare( $rs, $this->affi_ranks, $this->affi_ranks ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( ! empty( $data['id'] ) ) {

			return $data['id'];
		}

		return '';
	}

	public function get_clicks_in_period( $start, $end, $aff_id = '' ) {
		if ( $start || $end ) {
			if ( $aff_id ) {
				$rs = $this->wpdb->prepare( 'SELECT date FROM %i WHERE affiliate_id = %d AND date >= %s AND date <= %s', $this->affi_visits, $aff_id, $start, $end );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			} else {
				$rs = $this->wpdb->prepare( 'SELECT date FROM %i WHERE date >= %s AND date <= %s', $this->affi_visits, $start, $end );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			}
			$data = $this->wpdb->get_results( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data ) ) {

				return $data;
			}
		}

		return [];
	}

	public function get_clicks_total_in_period( $start, $end ) {
		if ( $start || $end ) {
			$rs   = $this->wpdb->prepare( "SELECT COUNT(*) as cl_count FROM %i WHERE date >= %s AND date <= %s", $this->affi_visits, $start, $end );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_row( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data['cl_count'] ) ) {

				return $data['cl_count'];
			}
		}

		return 0;
	}

	public function get_clicks_group_url_in_period( $start, $end ) {
		if ( $start || $end ) {
			$rs   = $this->wpdb->prepare( "SELECT COUNT(*), url FROM %i WHERE date >= %s AND date <= %s GROUP BY url", $this->affi_visits, $start, $end );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$data = $this->wpdb->get_results( $rs, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			if ( ! empty( $data ) ) {

				return $data;
			}
		}

		return [];
	}

	public function get_affiliates_count() {
		$query      = $this->wpdb->prepare( 'SELECT COUNT(*) as aff_count FROM %i WHERE 1', $this->affi_affiliates );
		$query_data = $this->wpdb->get_results( $query, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( is_array( $query_data ) && isset( $query_data[0] ) ) {
			if ( isset( $query_data[0]['aff_count'] ) ) {

				return intval( $query_data[0]['aff_count'] );
			}
		}

		return 0;
	}

	public function get_payouts_count() {
		$query      = $this->wpdb->prepare( 'SELECT COUNT(*) as aff_count FROM %i WHERE 1', $this->affi_payouts );
		$query_data = $this->wpdb->get_row( $query, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return $query_data['aff_count'];
	}

	public function get_affiliates_range_count( $from_time, $to_time ) {
//		$from_time = gmdate( 'Y-m-d H:i:s', $from_time );
//		$to_time   = gmdate( 'Y-m-d H:i:s', $to_time );

		$query = 'SELECT * FROM %i WHERE date_created >= %s AND date_created <= %s';

		return $this->wpdb->get_results( $this->wpdb->prepare( $query, $this->affi_affiliates, $from_time, $to_time ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	public function get_payout_range_count( $from_time, $to_time ) {
//		$from_time = gmdate( 'Y-m-d H:i:s', $from_time );
//		$to_time   = gmdate( 'Y-m-d H:i:s', $to_time );

		$query = "SELECT * FROM %i WHERE status='approved' AND date_created >= %s AND date_created <= %s";

		return $this->wpdb->get_results( $this->wpdb->prepare( $query, $this->affi_payouts, $from_time, $to_time ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	public function get_total_payout_by_user( $user_id ) {
		$query = 'SELECT COUNT(*) as p_count FROM %i WHERE user_id=%d';

		$data = $this->wpdb->get_row( $this->wpdb->prepare( $query, $this->affi_payouts, $user_id ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		if ( ! empty( $data['p_count'] ) ) {

			return $data['p_count'];
		}

		return 0;
	}

	public function get_total_payout_all() {
		$query = $this->wpdb->prepare( 'SELECT COUNT(id) as p_count FROM %i WHERE 1', $this->affi_payouts );

		$data = $this->wpdb->get_row( $query, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( ! empty( $data['p_count'] ) ) {

			return $data['p_count'];
		}

		return 0;
	}

	public function get_total_notify_all() {
		$query = $this->wpdb->prepare( 'SELECT COUNT(id) as u_count FROM %i WHERE 1', $this->affi_user_notifications );

		$data = $this->wpdb->get_row( $query, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( ! empty( $data['u_count'] ) ) {

			return $data['u_count'];
		}

		return 0;
	}

	public function get_total_rank_available() {
		$query = $this->wpdb->prepare( 'SELECT COUNT(id) as r_count FROM %i WHERE 1', $this->affi_ranks );

		$data = $this->wpdb->get_row( $query, ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		if ( ! empty( $data['r_count'] ) ) {

			return $data['r_count'];
		}

		return 0;
	}

	public function get_user_notification( $notify_id ) {
		$query = 'SELECT * FROM %i WHERE id=%d';

		$query_data = $this->wpdb->get_row( $this->wpdb->prepare( $query, $this->affi_user_notifications, $notify_id ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		if ( ! empty( $query_data ) ) {

			return $query_data;
		}

		return [];
	}

	public function update_affiliates_rank( $affiliate_id = '', $rank = '' ) {
		if ( $affiliate_id && $rank != '' ) {
			$success = $this->wpdb->update( $this->affi_affiliates, array( 'rank' => $rank ), array( 'id' => $affiliate_id ) );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}
	}

	public function update_affiliates_reward( $affiliate_id = '', $amount = '' ) {
		$affiliate_data = $this->get_affiliate_by_id( $affiliate_id );
		if ( ! is_object( $affiliate_data ) ) {
			return;
		}
		$base_amount = $affiliate_data->earning;
		if ( $affiliate_id && $amount != '' ) {
			$new_amount = floatval( $amount ) + floatval( $base_amount );
			$success    = $this->wpdb->update( $this->affi_affiliates, array( 'earning' => $new_amount ), array( 'id' => $affiliate_id ) );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}
	}

	public function update_affiliates_reward_refund( $affiliate_id = '', $amount = '' ) {
		$affiliate_data = $this->get_affiliate_by_id( $affiliate_id );
		if ( ! is_object( $affiliate_data ) ) {
			return;
		}
		$base_amount = $affiliate_data->earning;
		if ( $affiliate_id && $amount != '' ) {
			$new_amount = floatval( $base_amount ) - floatval( $amount );
			$success    = $this->wpdb->update( $this->affi_affiliates, array( 'earning' => $new_amount ), array( 'id' => $affiliate_id ) );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}
	}

	public function update_affiliates_payment_info( $affiliate_id = '', $info = '' ) {
		if ( $affiliate_id ) {
			$success = $this->wpdb->update( $this->affi_affiliates, array( 'payment_info' => $info ), array( 'id' => $affiliate_id ) );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

			return $success;
		}

		return false;
	}

	public function update_affiliates_payment_info_by_user_id( $user_id = '', $info = '' ) {
		if ( $user_id ) {
			$success = $this->wpdb->update( $this->affi_affiliates, array( 'payment_info' => $info ), array( 'user_id' => $user_id ) );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

			return $success;
		}

		return false;
	}

	public function update_affiliates_user_id( $affiliate_id, $user_id ) {
		if ( $affiliate_id && $user_id ) {
			$success = $this->wpdb->update( $this->affi_affiliates, array( 'user_id' => $user_id ), array( 'id' => $affiliate_id ) );
		}
	}

	public function insert_tracking_visitor( $data ) {
		$success = $this->wpdb->insert( $this->affi_visits, $data );
		if ( ! $success ) {
			return false;
		}
		$insertId = isset( $this->wpdb->insert_id ) ? $this->wpdb->insert_id : 0;

		return $insertId;
	}

	public function insert_affiliate_user( $data ) {
		$success = $this->wpdb->insert( $this->affi_affiliates, $data );
		if ( ! $success ) {
			return false;
		}
		$insertId = isset( $this->wpdb->insert_id ) ? $this->wpdb->insert_id : 0;

		return $insertId;
	}

	public function insert_user_notification( $data ) {
		$success = $this->wpdb->insert( $this->affi_user_notifications, $data );

		if ( ! $success ) {
			return false;
		}
		$insertId = isset( $this->wpdb->insert_id ) ? $this->wpdb->insert_id : 0;

		return $insertId;
	}

	/**
	 * Sum amounts of pending payouts for a WordPress user.
	 *
	 * @param int $user_id User ID.
	 * @return float
	 */
	public function get_pending_payout_amount_by_user( $user_id ) {
		$user_id = absint( $user_id );
		if ( ! $user_id ) {
			return 0.0;
		}

		$sum = $this->wpdb->get_var(
			$this->wpdb->prepare(
				'SELECT COALESCE( SUM( amount ), 0 ) FROM %i WHERE user_id = %s AND status = %s',
				$this->affi_payouts,
				(string) $user_id,
				'pending'
			)
		);// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		return (float) $sum;
	}

	public function insert_user_request_payout( $data ) {
		if ( empty( $data['transaction_id'] ) ) {
			$data['transaction_id'] = self::generate_payout_transaction_id( isset( $data['user_id'] ) ? $data['user_id'] : 0 );
		}

		$success = $this->wpdb->insert( $this->affi_payouts, $data );

		if ( ! $success ) {
			return false;
		}
		$insertId = isset( $this->wpdb->insert_id ) ? $this->wpdb->insert_id : 0;

		return $insertId;
	}

	/**
	 * Non-empty transaction id for payout inserts.
	 *
	 * @param int|string $user_id User ID.
	 * @return string
	 */
	public static function generate_payout_transaction_id( $user_id = 0 ) {
		return sprintf(
			'affi-%s-%s-%s',
			absint( $user_id ),
			gmdate( 'YmdHis' ),
			wp_generate_password( 8, false, false )
		);
	}

	public function get_notifications( $args = array() ) {
		global $wpdb;
		$args  = wp_parse_args( $args, array(
			'fields'  => '*',
			'where'   => '',
			'limit'   => 30,
			'offset'  => 0,
			'orderby' => 'data_created',
			'order'   => 'DESC',
		) );
//		$where = '';
//		if ( isset( $args['fields'] ) && is_array( $args['fields'] ) ) {
//			$args['fields'] = implode( ', ', $args['fields'] );
//		}
//		if ( isset( $args['ids_user'] ) ) {
//			$ids_user = $args['ids_user'];
//			if ( is_array( $args['ids_user'] ) ) {
//				$ids_user = implode( ', ', $args['ids_user'] );
//			}
//			$where = "WHERE user_id IN ({$ids_user}) ";
//		}
//		if ( isset( $args['fields'] ) && $args['fields'] === '*' ) {
//			$query = "SELECT * FROM {$wpdb->prefix}affi_user_notifications {$where} LIMIT %d OFFSET %d ";
//
//			return $wpdb->get_results( $wpdb->prepare( $query, $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
//		}
//		$query = "SELECT %i FROM {$wpdb->prefix}affi_user_notifications {$where} LIMIT %d OFFSET %d ";
//
//		return $wpdb->get_results( $wpdb->prepare( $query, $args['fields'], $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( isset( $args['fields'] ) && is_array( $args['fields'] ) ) {
			$args['fields'] = implode( ', ', $args['fields'] );
		}

		if ( isset( $args['ids_user'] ) ) {
			$ids_user = $args['ids_user'];
			if ( is_array( $args['ids_user'] ) ) {
				$ids_user = implode( ', ', array_fill( 0, count( $args['ids_user'] ), '%d' ) );

				if ( isset( $args['fields'] ) && $args['fields'] === '*' ) {
					$param_prepare = $args['ids_user'];
					array_push( $param_prepare, $args['limit'] );
					array_push( $param_prepare, $args['offset'] );

					array_unshift( $param_prepare, $this->affi_user_notifications );

					return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE user_id IN ( $ids_user ) LIMIT %d OFFSET %d", $param_prepare ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				} else {
					$param_prepare = array_merge(
						array( $args['fields'], $this->affi_user_notifications ),
						$args['ids_user'],
						array( $args['limit'], $args['offset'] )
					);

					return $wpdb->get_results( $wpdb->prepare( "SELECT %i FROM %i WHERE user_id IN ( $ids_user ) LIMIT %d OFFSET %d", $param_prepare ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, PluginCheck.Security.DirectDB.UnescapedDBParameter
				}
			}

			if ( isset( $args['fields'] ) && $args['fields'] === '*' ) {
				return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE user_id=%d LIMIT %d OFFSET %d', $this->affi_user_notifications, $args['ids_user'], $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			} else {
				return $wpdb->get_results( $wpdb->prepare( 'SELECT %i FROM %i WHERE user_id=%d LIMIT %d OFFSET %d', $args['fields'], $this->affi_user_notifications, $args['ids_user'], $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			}
		}

		if ( isset( $args['fields'] ) && $args['fields'] === '*' ) {
			return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE 1 LIMIT %d OFFSET %d', $this->affi_user_notifications, $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		} else {
			return $wpdb->get_results( $wpdb->prepare( 'SELECT %i FROM %i WHERE 1 LIMIT %d OFFSET %d', $args['fields'], $this->affi_user_notifications, $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		}
	}

	public function get_count_notifications_by_affiliate( $user_id ) {
		$query = "SELECT COUNT(*) as u_count FROM %i WHERE user_id=%d ";

		$rs = $this->wpdb->get_row( $this->wpdb->prepare( $query, $this->affi_user_notifications, $user_id ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		if ( ! empty( $rs['u_count'] ) ) {

			return $rs['u_count'];
		}

		return 0;
	}

	public function get_payouts( $args = array() ) {
		global $wpdb;
		$args  = wp_parse_args( $args, array(
			'fields'  => '*',
			'where'   => '',
			'limit'   => 30,
			'offset'  => 0,
			'orderby' => 'data_created',
			'order'   => 'DESC',
		) );
		if ( isset( $args['fields'] ) && is_array( $args['fields'] ) ) {
			$args['fields'] = implode( ', ', $args['fields'] );
		}
		if ( isset( $args['ids_user'] ) ) {
			$user_ids = is_array( $args['ids_user'] ) ? array_filter( array_map( 'absint', $args['ids_user'] ) ) : array( absint( $args['ids_user'] ) );
			if ( empty( $user_ids ) ) {
				return array();
			}
			$placeholders  = implode( ', ', array_fill( 0, count( $user_ids ), '%d' ) );
			$prepare_args  = array_merge( array( $this->affi_payouts ), $user_ids, array( $args['limit'], $args['offset'] ) );
			if ( isset( $args['fields'] ) && $args['fields'] === '*' ) {
				$sql = "SELECT * FROM %i WHERE user_id IN ({$placeholders}) LIMIT %d OFFSET %d"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

				return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
					$wpdb->prepare( $sql, $prepare_args ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
					ARRAY_A
				);
			}

			array_unshift( $prepare_args, $args['fields'] );
			$sql = "SELECT %i FROM %i WHERE user_id IN ({$placeholders}) LIMIT %d OFFSET %d"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

			return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prepare( $sql, $prepare_args ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
				ARRAY_A
			);
		}
		if ( isset( $args['fields'] ) && $args['fields'] === '*' ) {
			return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->prepare( 'SELECT * FROM %i WHERE 1 LIMIT %d OFFSET %d', $this->affi_payouts, $args['limit'], $args['offset'] ),
				ARRAY_A
			);
		}

		return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare( 'SELECT %i FROM %i WHERE 1 LIMIT %d OFFSET %d', $args['fields'], $this->affi_payouts, $args['limit'], $args['offset'] ),
			ARRAY_A
		);
	}

	public function delete_payout_after_time( $date ) {
		$rs   = $this->wpdb->prepare( "DELETE FROM %i WHERE `date_created`<%d;", $this->affi_payouts, $date );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$data = $this->wpdb->query( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared


		return $data;
	}

	public function delete_notify_after_time( $date ) {
		$rs   = $this->wpdb->prepare( "DELETE FROM %i WHERE `date_created`<%d;", $this->affi_user_notifications, $date );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$data = $this->wpdb->query( $rs );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared


		return $data;
	}

	public function get_payouts_by_affiliate_id( $args = array() ) {
		global $wpdb;
		$args  = wp_parse_args( $args, array(
			'fields'  => '*',
			'where'   => '',
			'limit'   => 30,
			'offset'  => 0,
			'orderby' => 'date_created',
			'order'   => 'DESC',
		) );
		$ids_user = '';
		if ( isset( $args['fields'] ) && is_array( $args['fields'] ) ) {
			$args['fields'] = implode( ', ', $args['fields'] );
		}
		if ( isset( $args['user_user'] ) ) {
			$ids_user = '%d';
			if ( is_array( $args['ids_user'] ) ) {
				$ids_user = implode( ', ', array_fill( 0, count( $args['ids_user'] ), '%d' ) );
			}
			$where = "WHERE id IN ({$ids_user}) ";
		}
		if ( isset( $args['fields'] ) && $args['fields'] === '*' ) {
			if ( $ids_user && $where ) {
				$param_prepare = is_array( $args['ids_user'] ) ? $args['ids_user'] : [ $args['ids_user'] ];
				array_unshift( $param_prepare, $this->affi_payouts );
				array_push( $param_prepare, $args['limit'] );
				array_push( $param_prepare, $args['offset'] );
				$query = "SELECT * FROM %i $where LIMIT %d OFFSET %d ";

				return $wpdb->get_results( $wpdb->prepare( $query, $param_prepare ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			} else {
				$query = "SELECT * FROM %i WHERE 1 LIMIT %d OFFSET %d ";

				return $wpdb->get_results( $wpdb->prepare( $query, $this->affi_payouts, $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			}
		}
		if ( $ids_user && $where ) {
			$param_prepare = is_array( $args['ids_user'] ) ? $args['ids_user'] : [ $args['ids_user'] ];
			array_unshift( $param_prepare, $this->affi_payouts );
			array_unshift( $param_prepare, $args['fields'] );
			array_push( $param_prepare, $args['limit'] );
			array_push( $param_prepare, $args['offset'] );
			$query = "SELECT %i FROM %i $where LIMIT %d OFFSET %d ";

			return $wpdb->get_results( $wpdb->prepare( $query, $param_prepare ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		} else {
			$query = "SELECT %i FROM %i WHERE 1 LIMIT %d OFFSET %d ";

			return $wpdb->get_results( $wpdb->prepare( $query, $args['fields'], $this->affi_payouts, $args['limit'], $args['offset'] ), ARRAY_A );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}
	}

	public function query_order_referral_by_affiliate( $i_args ) {
		$limit  = isset( $i_args['limit'] ) ? max( 1, (int) $i_args['limit'] ) : 20;
		$offset = isset( $i_args['offset'] ) ? max( 0, (int) $i_args['offset'] ) : 0;

		$hpos_custom_table = get_option( 'woocommerce_feature_custom_order_tables_enabled' ) === 'yes' ||
		                     get_option( 'woocommerce_custom_orders_table_enabled' ) === 'yes';
		if ( $hpos_custom_table ) {
			$args    = array(
				'limit'      => $limit,
				'offset'     => $offset,
				'paginate'   => true,
				'return'     => 'ids',
				'orderby'    => 'date',
				'order'      => 'DESC',
				'meta_query' => array(// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
					array(
						'key'     => 'affi_affiliate_id',
						'value'   => $i_args['aff_id'],
						'compare' => '=',
					),
				),
			);
			$results = wc_get_orders( $args );
			$orders  = is_object( $results ) && isset( $results->orders ) ? $results->orders : array();
			$count   = is_object( $results ) && isset( $results->total ) ? (int) $results->total : count( $orders );

			if ( count( $orders ) ) {
				$output = array();
				foreach ( $orders as $o_item ) {
					$order_data = wc_get_order( $o_item );
					if ( ! $order_data ) {
						continue;
					}
					$order_date  = $order_data->get_date_created();
					$order_items = [];
					foreach ( $order_data->get_items() as $item_id => $od_item ) {
						$order_items[] = $od_item->get_name();
					}
					$output[ $o_item ] = array(
						'id'           => $o_item,
						'products'     => $order_items,
						'amount'       => $order_data->get_total(),
						'status'       => $order_data->get_status(),
						'affiliate'    => $order_data->get_meta( 'affi_affiliate_rate', true ),
						'date_created' => $order_date ? $order_date->date( 'U' ) : 0,
					);
				}

				return [ 'count' => $count, 'data' => $output ];
			}
		} else {
			$new_args = array(
				'post_type'      => 'shop_order',
				'post_status'    => array_keys( wc_get_order_statuses() ),
				'posts_per_page' => $limit,
				'offset'         => $offset,
				'meta_key'       => 'affi_affiliate_id',// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
				'meta_value'     => $i_args['aff_id'],// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
				'orderby'        => 'date',
				'order'          => 'DESC',
			);

			$orders = new WP_Query( $new_args );
			$count  = (int) $orders->found_posts;
			if ( $orders->have_posts() ) {
				$output = array();
				while ( $orders->have_posts() ) {
					$orders->the_post();
					$wc_order_id = get_the_ID();
					$order_data  = wc_get_order( $wc_order_id );
					if ( ! $order_data ) {
						continue;
					}
					$order_date  = $order_data->get_date_created();
					$order_items = [];
					foreach ( $order_data->get_items() as $item_id => $od_item ) {
						$order_items[] = $od_item->get_name();
					}
					$output[ $wc_order_id ] = array(
						'id'           => $wc_order_id,
						'products'     => $order_items,
						'amount'       => $order_data->get_total(),
						'status'       => $order_data->get_status(),
						'affiliate'    => $order_data->get_meta( 'affi_affiliate_rate', true ),
						'date_created' => $order_date ? $order_date->date( 'U' ) : 0,
					);
				}
				wp_reset_postdata();

				return [ 'count' => $count, 'data' => $output ];
			}
			wp_reset_postdata();
		}

		return [];
	}

	function get_upgrade_affiliates_rank_info( $rank_id ) {
		$c_ranks_data = $this->get_ranks();
		if ( $c_ranks_data ) {
			$c_ranks_data = affi_reorder_ranks( $c_ranks_data );
			$rs_ranks     = [];
			$c_rank_order = '';
			$n_rank_order = '';
			$n_rank_data  = '';
			foreach ( $c_ranks_data as $key => $r_arr ) {
				if ( $r_arr['id'] == $rank_id ) {
					$rs_ranks['current'] = $r_arr;
					$c_rank_order        = $r_arr['order'];
				} else {
					if ( $n_rank_order == '' && $c_rank_order != '' && (int) $c_rank_order < (int) $r_arr['order'] ) {
						$n_rank_order = $r_arr['order'];
						$n_rank_data  = $r_arr;
					}
				}
			}
			if ( $n_rank_order !== '' && $n_rank_order !== '' ) {
				$rs_ranks['next'] = $n_rank_data;
			}

			return $rs_ranks;
		}

		return [];
	}
}