<?php
/**
 * File: Ecre_Database.php
 *
 * Database operations for Echo Rewards - CRUD operations only.
 *
 * @package ECRE
 * @since   1.0.0
 */

namespace ECRE;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

if ( ! class_exists( 'ECRE\Ecre_Database' ) ) {

	/**
	 * Echo Rewards database operations (CRUD only).
	 *
	 * @since 1.0.0
	 */
	class Ecre_Database {

		/**
		 * Constructor intentionally empty.
		 *
		 * @since 1.0.0
		 */
		public function __construct() {}

		/**
		 * Store referral order data.
		 *
		 * @since 1.0.0
		 *
		 * @param \WC_Order $order            WooCommerce order object.
		 * @param string    $coupon_code      Coupon code used in the order.
		 * @param int       $coupon_id        Coupon ID.
		 * @param int       $referral_user_id Referrer user ID.
		 *
		 * @return int|false Inserted referral ID on success, false on failure.
		 */
		public function store_referral_order_data( $order, $coupon_code, $coupon_id, $referral_user_id ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_referrals_orders';

			// Bail if table does not exist.
			$table_exists = $wpdb->get_var(
				$wpdb->prepare(
					'SHOW TABLES LIKE %s',
					$wpdb->esc_like( $table_name )
				)
			);

			if ( $table_exists !== $table_name ) {
				return false;
			}

			// Customer info.
			$names            = ecre_get_customer_names_from_order( $order );
			$customer_email   = $order->get_billing_email();
			$referred_user_id = $order->get_user_id();

			// Coupon discount.
			$discount_amount = ecre_calculate_coupon_discount_amount( $order, $coupon_code );

			// Prevent duplicates.
			$existing_entry = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM `{$wpdb->prefix}ecre_referrals_orders` WHERE order_id = %d AND referrer_user_id = %d",
					$order->get_id(),
					$referral_user_id
				)
			);

			if ( $existing_entry ) {
				return false;
			}

			// Prepare data.
			$referral_data = array(
				'referrer_user_id' => (int) $referral_user_id,
				'referred_user_id' => $referred_user_id ? (int) $referred_user_id : null,
				'order_id'         => (int) $order->get_id(),
				'coupon_code'      => sanitize_text_field( $coupon_code ),
				'coupon_id'        => $coupon_id ? (int) $coupon_id : null,
				'order_value'      => (float) $order->get_total(),
				'discount_amount'  => (float) $discount_amount,
				'referral_status'  => $order->get_status(),
				'firstname'        => sanitize_text_field( $names['firstname'] ),
				'lastname'         => sanitize_text_field( $names['lastname'] ),
				'fullname'         => sanitize_text_field( $names['fullname'] ),
				'customer_email'   => sanitize_email( $customer_email ),
				'order_date'       => $order->get_date_created()->date( 'Y-m-d H:i:s' ),
			);

			$data_formats = array( '%d', '%d', '%d', '%s', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s' );

			// Remove null values safely.
			$clean_data    = array();
			$clean_formats = array();
			$i             = 0;
			foreach ( $referral_data as $key => $value ) {
				if ( null !== $value ) {
					$clean_data[ $key ]   = $value;
					$clean_formats[ $i ]  = $data_formats[ $i ];
				}
				$i++;
			}

			$result = $wpdb->insert( $table_name, $clean_data, $clean_formats );
			if ( false === $result ) {
				return false;
			}

			$referral_id = $wpdb->insert_id;

			// Store reference in order meta.
			update_post_meta( $order->get_id(), '_ecre_referral_id', $referral_id );

			return $referral_id;
		}

		/**
		 * Update referral status in the database for a given order.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $order_id        WooCommerce order ID.
		 * @param string $referral_status New referral status (e.g. 'completed', 'cancelled').
		 *
		 * @return bool True on success, false on failure.
		 */
		public function update_referral_status( $order_id, $referral_status ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_referrals_orders';

			// Bail if table does not exist.
			$table_exists = $wpdb->get_var(
				$wpdb->prepare(
					'SHOW TABLES LIKE %s',
					$wpdb->esc_like( $table_name )
				)
			);

			if ( $table_exists !== $table_name ) {
				return false;
			}

			// Update referral status and updated_at timestamp.
			$result = $wpdb->update(
				$table_name,
				array(
					'referral_status' => sanitize_text_field( $referral_status ),
					'updated_at'      => current_time( 'mysql' ),
				),
				array( 'order_id' => (int) $order_id ),
				array( '%s', '%s' ),
				array( '%d' )
			);

			if ( false === $result ) {
				return false;
			}

			return true;
		}

		/**
		 * Stores reward order data when a reward coupon is used in an order.
		 *
		 * This function saves the reward order details, including the reward coupon and its associated IDs,
		 * along with referrer and referral information. Optionally, it can store referral coupon details if they are provided.
		 *
		 * @since 1.0.0
		 *
		 * @param \WC_Order $order                   WooCommerce order object.
		 * @param string    $reward_coupon_code      Reward coupon code used in the order.
		 * @param int       $reward_coupon_id        Reward coupon ID used in the order.
		 * @param int       $referrer_user_id        The original referrer user's ID.
		 * @param string    $referral_coupon_code    Original referral coupon code used (optional).
		 * @param int       $referral_coupon_id      Original referral coupon ID (optional).
		 * @param int       $referral_user_id        The original referral user's ID (optional).
		 *
		 * @return int|false Returns the inserted reward order ID on success, or false on failure.
		 */
		public function store_reward_order_data( $order, $reward_coupon_code, $reward_coupon_id, $referrer_user_id, $referral_coupon_code = null, $referral_coupon_id = null, $referral_user_id = null ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_rewards_orders';

			// Bail if table does not exist.
			$table_exists = $wpdb->get_var(
				$wpdb->prepare(
					'SHOW TABLES LIKE %s',
					$wpdb->esc_like( $table_name )
				)
			);

			if ( $table_exists !== $table_name ) {
				return false;
			}

			// Customer info.
			$names          = ecre_get_customer_names_from_order( $order );
			$customer_email = $order->get_billing_email();
			$user_id        = $order->get_user_id();

			// Calculate reward discount amount.
			$reward_discount_amount = ecre_calculate_coupon_discount_amount( $order, $reward_coupon_code );

			// Prevent duplicates.
			$existing_entry = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM `{$wpdb->prefix}ecre_rewards_orders` WHERE reward_coupon_id = %d AND order_id = %d",
					$reward_coupon_id,
					$order->get_id()
				)
			);

			if ( $existing_entry ) {
				return false;
			}

			// Prepare data.
			$reward_order_data = array(
				'reward_coupon_id'       => (int) $reward_coupon_id,
				'reward_coupon_code'     => sanitize_text_field( $reward_coupon_code ),
				'order_id'               => (int) $order->get_id(),
				'user_id'                => $user_id ? (int) $user_id : null,
				'referral_user_id'       => (int) $referral_user_id,
				'referral_coupon_id'     => $referral_coupon_id ? (int) $referral_coupon_id : null,
				'referral_coupon_code'   => $referral_coupon_code ? sanitize_text_field( $referral_coupon_code ) : null,
				'reward_discount_amount' => (float) $reward_discount_amount,
				'order_total'            => (float) $order->get_total(),
				'order_subtotal'         => (float) $order->get_subtotal(),
				'customer_email'         => sanitize_email( $customer_email ),
				'firstname'              => sanitize_text_field( $names['firstname'] ),
				'lastname'               => sanitize_text_field( $names['lastname'] ),
				'fullname'               => sanitize_text_field( $names['fullname'] ),
				'usage_date'             => $order->get_date_created()->date( 'Y-m-d H:i:s' ),
				'reward_status'          => $order->get_status(),
				'reward_type'            => 'coupon',
			);

			$data_formats = array( '%d', '%s', '%d', '%d', '%d', '%d', '%s', '%f', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' );

			// Remove null values safely.
			$clean_data    = array();
			$clean_formats = array();
			$i             = 0;
			foreach ( $reward_order_data as $key => $value ) {
				if ( null !== $value ) {
					$clean_data[ $key ]   = $value;
					$clean_formats[ $i ]  = $data_formats[ $i ];
				}
				$i++;
			}

			$result = $wpdb->insert( $table_name, $clean_data, $clean_formats );
			if ( false === $result ) {
				return false;
			}

			$reward_order_id = $wpdb->insert_id;

			// Store reference in order meta.
			update_post_meta( $order->get_id(), '_ecre_reward_order_id', $reward_order_id );

			return $reward_order_id;
		}

		/**
		 * Update reward order status in the database for a given order.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $order_id       WooCommerce order ID.
		 * @param string $reward_status  New reward status (e.g. 'completed', 'cancelled').
		 *
		 * @return bool True on success, false on failure.
		 */
		public function update_reward_order_status( $order_id, $reward_status ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_rewards_orders';

			// Bail if table does not exist.
			$table_exists = $wpdb->get_var(
				$wpdb->prepare(
					'SHOW TABLES LIKE %s',
					$wpdb->esc_like( $table_name )
				)
			);

			if ( $table_exists !== $table_name ) {
				return false;
			}

			// Update reward order status and updated_at timestamp.
			$result = $wpdb->update(
				$table_name,
				array(
					'reward_status' => sanitize_text_field( $reward_status ),
					'updated_at'    => current_time( 'mysql' ),
				),
				array( 'order_id' => (int) $order_id ),
				array( '%s', '%s' ),
				array( '%d' )
			);

			if ( false === $result ) {
				return false;
			}

			return true;
		}

		/**
		 * Add reward coupon record when a coupon is generated
		 *
		 * @param array $data Coupon data.
		 * @return int|false Insert ID or false on failure
		 */
		public function store_reward_coupon_data( $data ) {
			global $wpdb;

			$table_reward_coupons = $wpdb->prefix . 'ecre_reward_coupons';

			$defaults = array(
				'referrer_user_id'           => 0,
				'order_id'                   => 0,
				'coupon_code'                => '',
				'coupon_id'                  => null,
				'discount_type'              => 'fixed_cart',
				'discount_amount'            => 0.00,
				'usage_limit'                => 1,
				'usage_count'                => 0,
				'status'                     => 'active',
				'expires_at'                 => null,
				'include_products'           => null,
				'exclude_products'           => null,
				'include_product_categories' => null,
				'exclude_product_categories' => null,
			);

			$data = wp_parse_args( $data, $defaults );

			// FAST CHECK: Already in our tracking table?
			$existing = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM `{$wpdb->prefix}ecre_reward_coupons` WHERE coupon_code = %s LIMIT 1",
					$data['coupon_code']
				)
			);

			if ( $existing ) {
				return false; // Already tracked, skip.
			}

			// Prepare insert data with new column names.
			$insert_data = array(
				'referrer_user_id' => $data['referrer_user_id'],
				'order_id'         => $data['order_id'],
				'coupon_code'      => $data['coupon_code'],
				'coupon_id'        => $data['coupon_id'],
				'discount_type'    => $data['discount_type'],
				'discount_amount'  => $data['discount_amount'],
				'usage_limit'      => $data['usage_limit'],
				'usage_count'      => $data['usage_count'],
				'status'           => $data['status'],
				'expires_at'       => $data['expires_at'],
			);

			// Handle old column names for backward compatibility.
			if ( isset( $data['product_restrictions'] ) && ! empty( $data['product_restrictions'] ) ) {
				$product_restrictions = is_array( $data['product_restrictions'] )
					? wp_json_encode( $data['product_restrictions'] )
					: $data['product_restrictions'];
				$insert_data['include_products'] = $product_restrictions;
			} elseif ( isset( $data['include_products'] ) && ! empty( $data['include_products'] ) ) {
				$include_products = is_array( $data['include_products'] )
					? wp_json_encode( $data['include_products'] )
					: $data['include_products'];
				$insert_data['include_products'] = $include_products;
			}

			if ( isset( $data['exclude_products'] ) && ! empty( $data['exclude_products'] ) ) {
				$exclude_products = is_array( $data['exclude_products'] )
					? wp_json_encode( $data['exclude_products'] )
					: $data['exclude_products'];
				$insert_data['exclude_products'] = $exclude_products;
			}

			if ( isset( $data['category_restrictions'] ) && ! empty( $data['category_restrictions'] ) ) {
				$category_restrictions = is_array( $data['category_restrictions'] )
					? wp_json_encode( $data['category_restrictions'] )
					: $data['category_restrictions'];
				$insert_data['include_product_categories'] = $category_restrictions;
			} elseif ( isset( $data['include_product_categories'] ) && ! empty( $data['include_product_categories'] ) ) {
				$include_product_categories = is_array( $data['include_product_categories'] )
					? wp_json_encode( $data['include_product_categories'] )
					: $data['include_product_categories'];
				$insert_data['include_product_categories'] = $include_product_categories;
			}

			if ( isset( $data['exclude_product_categories'] ) && ! empty( $data['exclude_product_categories'] ) ) {
				$exclude_product_categories = is_array( $data['exclude_product_categories'] )
					? wp_json_encode( $data['exclude_product_categories'] )
					: $data['exclude_product_categories'];
				$insert_data['exclude_product_categories'] = $exclude_product_categories;
			}

			$result = $wpdb->insert( $table_reward_coupons, $insert_data );

			return $result ? $wpdb->insert_id : false;
		}


		/**
		 * Sync reward coupon usage data from WooCommerce to the ecre_reward_coupons table.
		 *
		 * This function syncs the usage_count and usage_limit from WooCommerce coupon post meta
		 * to our custom table when a reward coupon is used in an order.
		 *
		 * @since 1.0.0
		 *
		 * @param int $coupon_id The WooCommerce coupon ID.
		 * @return bool True on success, false on failure.
		 */
		public function sync_reward_coupon_usage( $coupon_id ) {
			global $wpdb;

			$table_reward_coupons = $wpdb->prefix . 'ecre_reward_coupons';

			// Get actual usage count and limit from WooCommerce coupon post meta
			$usage_count = (int) get_post_meta( $coupon_id, 'usage_count', true );
			$usage_limit = (int) get_post_meta( $coupon_id, 'usage_limit', true );

			// Sync the values to our custom table
			$result = $wpdb->query(
				$wpdb->prepare(
					"UPDATE {$table_reward_coupons}
					SET usage_count = %d,
						usage_limit = %d
					WHERE coupon_id = %d",
					$usage_count,
					$usage_limit,
					$coupon_id
				)
			);

			return $result !== false;
		}




		/**
		 * Stores the referrer information for a user, including optional coupon details.
		 *
		 * This function checks if a referrer entry already exists for the given user. If it does, it updates the record.
		 * If no entry exists, a new referrer record is created. Optionally, the function can store a coupon code and coupon ID
		 * for the referral.
		 *
		 * @param int    $user_id     The ID of the user being referred.
		 * @param string $coupon_code The coupon code associated with the referral, if any. Default is an empty string.
		 * @param int    $coupon_id   The ID of the coupon, if provided. Default is null.
		 *
		 * @return bool Returns true if the referrer was successfully stored or updated, false otherwise.
		 */
		public function store_referrer( $user_id, $coupon_code = '', $coupon_id = null ) {
			global $wpdb;

			$table_referrers = $wpdb->prefix . 'ecre_referrers';

			// Check if referrer already exists.
			$exists = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM `{$wpdb->prefix}ecre_referrers` WHERE user_id = %d",
					$user_id
				)
			);

			if ( $exists ) {
				// Update existing referrer with coupon information if provided.
				if ( ! empty( $coupon_code ) && ! empty( $coupon_id ) ) {
					$wpdb->update(
						$table_referrers,
						array(
							'referral_coupon_code' => $coupon_code,
							'referral_coupon_id'   => $coupon_id,
							'updated_at'           => current_time( 'mysql' ),
						),
						array( 'user_id' => $user_id )
					);
				}
				return true;
			}

			// Insert new referrer with zero totals, coupon information, and new columns.
			$referrer_data = array(
				'user_id'              => $user_id,
				'referral_coupon_code' => ! empty( $coupon_code ) ? $coupon_code : null,
				'referral_coupon_id'   => ! empty( $coupon_id ) ? $coupon_id : null,
				'total_referrals'      => 0,
				'total_sales'          => 0.00,
				'total_rewards_issued' => 0,
				'total_discount'       => 0.00,
				'conversion_rate'      => 0.00,
				'custom_rewards'       => 0,
				'custom_referrals'     => 0,
				'first_referral_date'  => null,
				'last_referral_date'   => null,
			);

			$result = $wpdb->insert( $table_referrers, $referrer_data );

			// Store the referrer ID for potential use in referral records.
			if ( $result && ! empty( $coupon_code ) && ! empty( $coupon_id ) ) {
				$referrer_id = $wpdb->insert_id;
				// Store coupon information in user meta for easy access (for backward compatibility).
				update_user_meta( $user_id, 'ecre_referral_coupon_id', $coupon_id );
				update_user_meta( $user_id, 'ecre_referrer_id', $referrer_id );
			}

			return false !== $result;
		}

		/**
		 * Updates the referrer statistics when a referral order is placed.
		 *
		 * This function updates the referral statistics for the referrer, including the total number of referrals,
		 * total sales, total discounts, the first and last referral dates, and the updated timestamp. It also recalculates
		 * the referrer's conversion rate after the stats are updated.
		 *
		 * The referrer data is fetched from the `ecre_referrers` table using the provided referral user ID. If an existing
		 * record for the referrer is found, the statistics are updated accordingly. If no record is found, no changes are made.
		 *
		 * @since 1.0.0
		 *
		 * @param \WC_Order $order             WooCommerce order object for the referral order.
		 * @param string    $coupon_code       The coupon code used in the referral order (if any).
		 * @param int       $coupon_id         The ID of the coupon used in the referral order (if any).
		 * @param int       $referral_user_id  The ID of the user who referred the order.
		 *
		 * @return void
		 */
		public function update_referrer_stats_on_order( $order, $coupon_code, $coupon_id, $referral_user_id ) {
			global $wpdb;

			$table_referrers = $wpdb->prefix . 'ecre_referrers';

			// Calculate order totals.
			$order_total   = (float) $order->get_total();
			$discount_total = (float) $order->get_discount_total();

			// Current timestamp for last referral date.
			$current_date = current_time( 'mysql' );

			// Get existing record for this user.
			$referrer = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT id, total_referrals, total_sales, total_discount, first_referral_date 
					FROM `{$wpdb->prefix}ecre_referrers` WHERE user_id = %d",
					$referral_user_id
				)
			);

			if ( $referrer ) {
				// First referral date stays if already set, otherwise use now.
				$first_date = ! empty( $referrer->first_referral_date ) ? $referrer->first_referral_date : $current_date;

				// Update stats.
				$wpdb->query(
					$wpdb->prepare(
						"UPDATE `{$wpdb->prefix}ecre_referrers` 
						SET total_referrals = total_referrals + 1,
							total_sales     = total_sales + %f,
							total_discount  = total_discount + %f,
							first_referral_date = %s,
							last_referral_date  = %s,
							updated_at = %s
						WHERE user_id = %d",
						$order_total,
						$discount_total,
						$first_date,
						$current_date,
						$current_date,
						$referral_user_id
					)
				);
			}

			// Recalculate conversion rate using the new helper function.
			$this->recalculate_referrer_conversion_rate( $referral_user_id );
		}

		/**
		 * Updates the referrer's reward count upon the completion of a referral order.
		 *
		 * This function increments the total rewards issued for a given referral user by 1 and updates the
		 * timestamp of the last update in the `ecre_referrers` table. After updating the rewards, the function
		 * recalculates the referrer's conversion rate based on the new data.
		 *
		 * @since 1.0.0
		 *
		 * @param int $referral_user_id The ID of the user who referred the order and is receiving the reward.
		 *
		 * @return void
		 */
		public function update_referrer_rewards_on_completion( $referral_user_id ) {
			global $wpdb;

			$table_referrers = $wpdb->prefix . 'ecre_referrers';

			$wpdb->query(
				$wpdb->prepare(
					"UPDATE `{$wpdb->prefix}ecre_referrers` 
					SET total_rewards_issued = total_rewards_issued + 1,
						updated_at = %s
					WHERE user_id = %d",
					current_time( 'mysql' ),
					$referral_user_id
				)
			);

			$this->recalculate_referrer_conversion_rate( $referral_user_id );
		}

		/**
		 * Updates the dashboard summary statistics upon the completion of an order.
		 *
		 * This function updates the summary statistics for the referrer’s dashboard by incrementing the total
		 * number of referrals, updating the total referral sales, total discount given, and the timestamp for the
		 * last update. The statistics are updated in the `ecre_referrars_dashboard_summary` table under the 'all_time' period.
		 * After updating the stats, the function recalculates the dashboard’s average conversion rate.
		 *
		 * @since 1.0.0
		 *
		 * @param \WC_Order $order             WooCommerce order object for the referral order.
		 * @param string    $coupon_code       The coupon code used in the referral order (if any).
		 * @param int       $coupon_id         The ID of the coupon used in the referral order (if any).
		 * @param int       $referral_user_id  The ID of the user who referred the order.
		 *
		 * @return void
		 */
		public function update_dashboard_summary_stats_on_order( $order, $coupon_code, $coupon_id, $referral_user_id ) {
			global $wpdb;

			$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';

			// Calculate order totals.
			$order_total   = (float) $order->get_total();
			$discount_total = (float) $order->get_discount_total();

			// Current timestamp for last update.
			$current_date = current_time( 'mysql' );

			// Update dashboard summary stats.
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE `{$wpdb->prefix}ecre_referrars_dashboard_summary` 
					SET total_referrals = total_referrals + 1,
						total_referral_sales = total_referral_sales + %f,
						total_discount_given = total_discount_given + %f,
						last_updated = %s
					WHERE summary_period = 'all_time'",
					$order_total,
					$discount_total,
					$current_date
				)
			);

			$this->recalculate_dashboard_average_conversion_rate();
		}

		/**
		 * Updates the dashboard summary rewards when a referral reward is issued.
		 *
		 * This function increments the total rewards issued by 1 in the `ecre_referrars_dashboard_summary` table
		 * for the 'all_time' period. It also updates the timestamp for the last update and recalculates the average
		 * conversion rate for the dashboard.
		 *
		 * @since 1.0.0
		 *
		 * @param int $referral_user_id The ID of the user who referred the order and is receiving the reward.
		 *
		 * @return void
		 */
		public function update_dashboard_summary_rewards_on_completion( $referral_user_id ) {
			global $wpdb;

			$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';

			// Increase total_rewards_issued by 1.
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE `{$wpdb->prefix}ecre_referrars_dashboard_summary` 
					SET total_rewards_issued = total_rewards_issued + 1,
						last_updated = %s
					WHERE summary_period = 'all_time'",
					current_time( 'mysql' )
				)
			);

			// Recalculate average conversion rate.
			$this->recalculate_dashboard_average_conversion_rate();
		}

		/**
		 * Recalculates the average conversion rate for the dashboard.
		 *
		 * This function calculates the average conversion rate by dividing the total rewards issued by the total
		 * referrals, and then multiplying by 100 to get a percentage. The result is rounded to two decimal places.
		 * If there are no referrals, the conversion rate is set to 0 to avoid division by zero. After recalculating,
		 * the average conversion rate is updated in the `ecre_referrars_dashboard_summary` table.
		 *
		 * @since 1.0.0
		 *
		 * @return void
		 */
		public function recalculate_dashboard_average_conversion_rate() {
			global $wpdb;

			$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';

			$stats = $wpdb->get_row(
				"SELECT total_referrals, total_rewards_issued 
				FROM `{$wpdb->prefix}ecre_referrars_dashboard_summary`
				WHERE summary_period = 'all_time'"
			);

			if ( $stats && $stats->total_referrals > 0 ) {
				$average_conversion_rate = round( ( $stats->total_rewards_issued / $stats->total_referrals ) * 100, 2 );
			} else {
				$average_conversion_rate = 0.00; // Avoid division by zero.
			}

			$wpdb->update(
				$dashboard_summary_table,
				array(
					'average_conversion_rate' => $average_conversion_rate,
					'last_updated'            => current_time( 'mysql' ),
				),
				array( 'summary_period' => 'all_time' )
			);
		}

		/**
		 * Recalculates the conversion rate for a specific referrer.
		 *
		 * This function calculates the conversion rate for a given referrer by dividing the total rewards issued
		 * by the total referrals, and multiplying the result by 100 to get the percentage. If there are no referrals,
		 * the conversion rate is set to 0 to avoid division by zero. The calculated conversion rate is then updated
		 * in the `ecre_referrers` table for the given referrer.
		 *
		 * @since 1.0.0
		 *
		 * @param int $referral_user_id The ID of the referrer whose conversion rate is being recalculated.
		 *
		 * @return void
		 */
		public function recalculate_referrer_conversion_rate( $referral_user_id ) {
			global $wpdb;

			$table_referrers = $wpdb->prefix . 'ecre_referrers';

			$stats = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT total_referrals, total_rewards_issued 
					FROM `{$wpdb->prefix}ecre_referrers` 
					WHERE user_id = %d",
					$referral_user_id
				)
			);

			if ( $stats && $stats->total_referrals > 0 ) {
				$conversion_rate = round( ( $stats->total_rewards_issued / $stats->total_referrals ) * 100, 2 );
			} else {
				$conversion_rate = 0.00; // avoid division by zero.
			}

			$wpdb->update(
				$table_referrers,
				array(
					'conversion_rate' => $conversion_rate,
					'updated_at'      => current_time( 'mysql' ),
				),
				array( 'user_id' => $referral_user_id )
			);
		}

		/**
		 * Increments the total referrers count in the dashboard summary upon referrer creation.
		 *
		 * This function increases the `total_referrers` value by 1 in the `ecre_referrars_dashboard_summary`
		 * table for the 'all_time' summary period. It also updates the timestamp for the last update.
		 *
		 * @since 1.0.0
		 *
		 * @param int $user_id The ID of the newly created referrer user.
		 *
		 * @return void
		 */
		public function update_dashboard_summary_referrers_on_creation( $user_id ) {
			global $wpdb;

			$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';

			// Increase total_referrers by 1.
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE `{$wpdb->prefix}ecre_referrars_dashboard_summary` 
					SET total_referrers = total_referrers + 1,
						last_updated = %s
					WHERE summary_period = 'all_time'",
					current_time( 'mysql' )
				)
			);
		}

		/**
		 * Retrieves a paginated list of referrers with optional filters and sorting.
		 *
		 * This method fetches a list of referrers from the database, allowing for pagination, search, and filtering
		 * by time period and custom rewards or referrals. The results include details such as the referrer's total
		 * referrals, total sales, rewards issued, conversion rate, and more. Pagination details are also returned
		 * to help with displaying results in a paginated format.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $page                    The current page number for pagination (defaults to 1).
		 * @param int    $per_page                The number of results per page (defaults to 10).
		 * @param string $search                  Search term to filter referrers by their name or email (optional).
		 * @param string $time_filter             The time period to filter results by (e.g., 'All Time', 'Last 30 days').
		 * @param bool   $custom_rewards_filter   Whether to filter results by referrers with custom rewards (optional).
		 * @param bool   $custom_referrals_filter Whether to filter results by referrers with custom referrals (optional).
		 *
		 * @return array                         An array containing the filtered referrers' data and pagination information.
		 *                                        The array includes 'data' (the list of referrers) and 'pagination' (pagination details).
		 */
		public function get_referrers_with_pagination( $page = 1, $per_page = 10, $search = '', $time_filter = 'All Time', $custom_rewards_filter = false, $custom_referrals_filter = false ) {
			global $wpdb;

			$offset = ( $page - 1 ) * $per_page;

			// Build the base query with ALL columns from referrers table.
			$referrers_table = $wpdb->prefix . 'ecre_referrers';
			$users_table     = $wpdb->users;

			$base_query = "
				SELECT 
					r.id,
					r.user_id,
					r.referral_coupon_code,
					r.referral_coupon_id,
					r.total_referrals,
					r.total_sales,
					r.total_rewards_issued,
					r.total_discount,
					r.conversion_rate,
					r.custom_rewards,
					r.custom_referrals,
					r.first_referral_date,
					r.last_referral_date,
					r.created_at,
					r.updated_at,
					u.display_name as name,
					u.user_email as email
				FROM {$referrers_table} r
				LEFT JOIN {$users_table} u ON r.user_id = u.ID
			";

			// Build WHERE conditions.
			$where_conditions = array( '1=1' );
			$where_params     = array();

			// Search functionality.
			if ( ! empty( $search ) ) {
				$search_term        = '%' . $wpdb->esc_like( $search ) . '%';
				$where_conditions[] = '(u.display_name LIKE %s OR u.user_email LIKE %s)';
				$where_params[]     = $search_term;
				$where_params[]     = $search_term;
			}

			// Time filter.
			if ( 'All Time' !== $time_filter ) {
				$date_condition = $this->get_time_filter_date_condition( $time_filter );
				if ( $date_condition ) {
					$where_conditions[] = 'r.updated_at >= %s';
					$where_params[]     = $date_condition;
				}
			}

			// Custom rewards filter.
			if ( $custom_rewards_filter ) {
				$where_conditions[] = 'r.custom_rewards = 1';
			}

			// Custom referrals filter.
			if ( $custom_referrals_filter ) {
				$where_conditions[] = 'r.custom_referrals = 1';
			}

			// Combine WHERE conditions.
			$where_clause = implode( ' AND ', $where_conditions );

			// Count total items (for pagination).
			$count_query = "
				SELECT COUNT(*) 
				FROM {$referrers_table} r
				LEFT JOIN {$users_table} u ON r.user_id = u.ID
				WHERE {$where_clause}
			";

			if ( ! empty( $where_params ) ) {
				// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$total_items = $wpdb->get_var( $wpdb->prepare( $count_query, $where_params ) );
			} else {
				// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$total_items = $wpdb->get_var( $count_query );
			}

			// Build final query with pagination.
			$final_query = "
				{$base_query}
				WHERE {$where_clause}
				ORDER BY r.total_sales DESC, r.total_referrals DESC
				LIMIT %d OFFSET %d
			";

			// Add pagination parameters to params array.
			$final_params = array_merge( $where_params, array( $per_page, $offset ) );

			// Execute query.
			if ( ! empty( $where_params ) ) {
				// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$results = $wpdb->get_results( $wpdb->prepare( $final_query, $final_params ), ARRAY_A );
			} else {
				// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$results = $wpdb->get_results( $wpdb->prepare( $final_query, array( $per_page, $offset ) ), ARRAY_A );
			}

			// Process results - include ALL database columns.
			$processed_results = array();
			foreach ( $results as $row ) {
				$processed_results[] = array(
					'id'                   => $row['id'],
					'user_id'              => $row['user_id'],
					'name'                 => isset( $row['name'] ) ? $row['name'] : 'Unknown User',
					'email'                => $row['email'],
					'referrals'            => intval( $row['total_referrals'] ),
					'salesGenerated'       => number_format( (float) $row['total_sales'], 2 ),
					'avatar'               => get_avatar_url( $row['user_id'] ),
					'hasCustomRewards'     => (bool) $row['custom_rewards'],
					'hasCustomReferrals'   => (bool) $row['custom_referrals'],
					'joinDate'             => $row['created_at'],
					'lastActivity'         => $row['updated_at'],
					// Add all database columns.
					'referral_coupon_code' => $row['referral_coupon_code'],
					'referral_coupon_id'   => $row['referral_coupon_id'],
					'total_referrals'      => intval( $row['total_referrals'] ),
					'total_sales'          => floatval( $row['total_sales'] ),
					'total_rewards_issued' => intval( $row['total_rewards_issued'] ),
					'total_discount'       => floatval( $row['total_discount'] ),
					'conversion_rate'      => floatval( $row['conversion_rate'] ),
					'first_referral_date'  => $row['first_referral_date'],
					'last_referral_date'   => $row['last_referral_date'],
				);
			}

			// Calculate pagination info.
			$total_pages = ceil( $total_items / $per_page );
			$has_more    = $page < $total_pages;

			$pagination = array(
				'current_page' => $page,
				'per_page'     => $per_page,
				'total_items'  => intval( $total_items ),
				'total_pages'  => $total_pages,
				'has_more'     => $has_more,
			);

			return array(
				'data'       => $processed_results,
				'pagination' => $pagination,
			);
		}

		/**
		 * Retrieves the dashboard summary data for the 'all_time' period.
		 *
		 * This method fetches the summary data from the `ecre_referrars_dashboard_summary` table, including total
		 * referrers, total referrals, total referral sales, and total rewards issued. It retrieves the most recent
		 * summary based on the last update timestamp. If no data is available, it falls back to calculating the summary
		 * using a custom fallback method.
		 *
		 * @since 1.0.0
		 *
		 * @return array An associative array containing the dashboard summary data:
		 *               - `total_referrers`: Total number of referrers.
		 *               - `total_referrals`: Total number of referrals.
		 *               - `total_referral_sales`: Total sales generated from referrals, formatted to two decimal places.
		 *               - `total_rewards_issued`: Total rewards issued to referrers.
		 */
		public function get_dashboard_summary() {
			global $wpdb;

			$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';

			// Get summary from the dashboard summary table.
			$summary = $wpdb->get_row(
				"SELECT * FROM `{$wpdb->prefix}ecre_referrars_dashboard_summary` WHERE summary_period = 'all_time' ORDER BY last_updated DESC LIMIT 1",
				ARRAY_A
			);

			if ( $summary ) {
				return array(
					'total_referrers'      => intval( $summary['total_referrers'] ),
					'total_referrals'      => intval( $summary['total_referrals'] ),
					'total_referral_sales' => number_format( (float) $summary['total_referral_sales'], 2 ),
					'total_rewards_issued' => intval( $summary['total_rewards_issued'] ),
				);
			} else {
				// Fallback to calculate if not cached.
				return $this->calculate_dashboard_summary_fallback();
			}
		}

		/**
		 * Get dashboard summary for a specific referrer by user_id
		 *
		 * @param int $user_id The user ID of the referrer.
		 * @return array|null Referrer dashboard data or null if not found
		 */
		public function get_referrer_dashboard_summary( $user_id ) {
			global $wpdb;

			$referrer = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT 
						r.id,
						r.user_id,
						r.referral_coupon_code,
						r.referral_coupon_id,
						r.total_referrals,
						r.total_sales,
						r.total_rewards_issued,
						r.total_discount,
						r.conversion_rate,
						r.custom_rewards,
						r.custom_referrals,
						r.first_referral_date,
						r.last_referral_date,
						r.created_at,
						r.updated_at,
						u.display_name as name,
						u.user_email as email
					FROM {$wpdb->prefix}ecre_referrers r
					LEFT JOIN {$wpdb->users} u ON r.user_id = u.ID
					WHERE r.user_id = %d",
					$user_id
				),
				ARRAY_A
			);

			if ( ! $referrer ) {
				return null;
			}

			// Format the data to match expected types.
			$referrer['total_sales']          = floatval( $referrer['total_sales'] );
			$referrer['total_referrals']      = intval( $referrer['total_referrals'] );
			$referrer['total_rewards_issued'] = intval( $referrer['total_rewards_issued'] );
			$referrer['total_discount']       = floatval( $referrer['total_discount'] );
			$referrer['conversion_rate']      = floatval( $referrer['conversion_rate'] );
			$referrer['custom_rewards']       = intval( $referrer['custom_rewards'] );
			$referrer['custom_referrals']     = intval( $referrer['custom_referrals'] );

			// Add avatar (WordPress doesn't store it in DB, use get_avatar_url).
			$referrer['avatar'] = get_avatar_url( $user_id );
			return $referrer;
		}

		/**
		 * Helper function to convert a time filter into an SQL date condition.
		 *
		 * This function takes a time filter string (e.g., '1 Month', '3 Month', '1 Year') and converts it into a
		 * valid SQL date condition, which can be used in database queries. The function calculates the appropriate
		 * date based on the current time and returns the date in the format 'Y-m-d H:i:s'. If the time filter is not
		 * recognized, the function returns `null`.
		 *
		 * @param string $time_filter The time filter to be converted (e.g., '1 Month', '3 Month', '1 Year').
		 *
		 * @return string|null The SQL date condition or `null` if the time filter is not recognized.
		 */
		private function get_time_filter_date_condition( $time_filter ) {
			$current_date = current_time( 'mysql' );

			switch ( $time_filter ) {
				case '1 Month':
					return gmdate( 'Y-m-d H:i:s', strtotime( '-1 month', strtotime( $current_date ) ) );
				case '3 Month':
					return gmdate( 'Y-m-d H:i:s', strtotime( '-3 months', strtotime( $current_date ) ) );
				case '6 Month':
					return gmdate( 'Y-m-d H:i:s', strtotime( '-6 months', strtotime( $current_date ) ) );
				case '1 Year':
					return gmdate( 'Y-m-d H:i:s', strtotime( '-1 year', strtotime( $current_date ) ) );
				default:
					return null;
			}
		}

		/**
		 * Calculate the dashboard summary as a fallback if cached data is unavailable.
		 *
		 * This function calculates the total number of referrers, total referrals, total sales, and total rewards
		 * issued from the database. It uses the `ecre_referrers` and `ecre_rewards_orders` tables to gather the
		 * necessary data and returns it in a structured array. The function serves as a fallback when no cached
		 * dashboard summary is found.
		 *
		 * @return array An associative array containing the calculated dashboard summary:
		 *               - `total_referrers`: Total number of referrers.
		 *               - `total_referrals`: Total number of referrals.
		 *               - `total_referral_sales`: Total sales generated from referrals, formatted to two decimal places.
		 *               - `total_rewards_issued`: Total rewards issued, or 0 if no rewards are found.
		 */
		private function calculate_dashboard_summary_fallback() {
			global $wpdb;

			$referrers_table = $wpdb->prefix . 'ecre_referrers';

			$total_referrers = $wpdb->get_var( "SELECT COUNT(*) FROM `{$wpdb->prefix}ecre_referrers`" );
			$totals = $wpdb->get_row(
				"SELECT 
					COALESCE(SUM(total_referrals), 0) as total_referrals,
					COALESCE(SUM(total_sales), 0) as total_sales
				FROM `{$wpdb->prefix}ecre_referrers`",
				ARRAY_A
			);

			$rewards_table = $wpdb->prefix . 'ecre_rewards_orders';
			$total_rewards = $wpdb->get_var( "SELECT COUNT(*) FROM `{$wpdb->prefix}ecre_rewards_orders`" );

			return array(
				'total_referrers'      => intval( $total_referrers ),
				'total_referrals'      => intval( $totals['total_referrals'] ),
				'total_referral_sales' => number_format( (float) $totals['total_sales'], 2 ),
				'total_rewards_issued' => intval( $total_rewards ? $total_rewards : 0 ),
			);
		}

		/**
		 * Retrieves a paginated history of referrals for a given referrer with optional time-based filtering.
		 *
		 * This function retrieves a list of referral records associated with a specific referrer user ID, supporting
		 * pagination and optional time-based filters (e.g., "1 Month", "3 Months", "1 Year"). The data includes detailed
		 * The results are formatted for front-end display and include pagination details for easy navigation.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $referrer_user_id The ID of the referrer for whom the history is being retrieved.
		 * @param int    $page            The page number for pagination (defaults to 1).
		 * @param int    $per_page        The number of results per page (defaults to 10).
		 * @param string $time_filter     The time period filter (e.g., 'All Time', '1 Month', '3 Months', '1 Year').
		 *
		 * @return array An associative array containing:
		 *               - `referrals`: A list of referral data for the given referrer.
		 *               - `pagination`: Pagination information including the current page, items per page, total items,
		 *                 total pages, and whether there are more pages.
		 */
		public function get_referrer_history_with_pagination( $referrer_user_id, $page = 1, $per_page = 10, $time_filter = 'All Time' ) {
			global $wpdb;

			$referrals_table = $wpdb->prefix . 'ecre_referrals_orders';

			// Build time filter condition.
			$time_condition = '';
			$time_params    = array();

			if ( 'All Time' !== $time_filter ) {
				switch ( $time_filter ) {
					case '1 Month':
						$time_condition = ' AND order_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-1 month' ) );
						break;
					case '3 Months':
						$time_condition = ' AND order_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-3 months' ) );
						break;
					case '6 Months':
						$time_condition = ' AND order_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-6 months' ) );
						break;
					case '1 Year':
						$time_condition = ' AND order_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-1 year' ) );
						break;
				}
			}

			// Get total count.
			$count_sql    = "SELECT COUNT(*) FROM {$referrals_table} WHERE referrer_user_id = %d" . $time_condition;
			$count_params = array_merge( array( $referrer_user_id ), $time_params );
			// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$total_items  = $wpdb->get_var( $wpdb->prepare( $count_sql, $count_params ) );

			// Calculate pagination.
			$total_pages = ceil( $total_items / $per_page );
			$offset      = ( $page - 1 ) * $per_page;

			// Get referral history data with ALL columns.
			$sql = "SELECT 
						ro.id,
						ro.referrer_user_id,
						ro.referred_user_id,
						ro.order_id,
						ro.coupon_code,
						ro.coupon_id,
						ro.order_value,
						ro.discount_amount,
						ro.referral_status,
						ro.firstname,
						ro.lastname,
						ro.fullname,
						ro.customer_email,
						ro.order_date,
						ro.created_at,
						ro.updated_at
					FROM {$referrals_table} ro
					WHERE ro.referrer_user_id = %d" . $time_condition . "
					ORDER BY ro.order_date DESC
					LIMIT %d OFFSET %d";

			$sql_params = array_merge(
				array( $referrer_user_id ),
				$time_params,
				array( $per_page, $offset )
			);

			// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$referrals = $wpdb->get_results( $wpdb->prepare( $sql, $sql_params ), ARRAY_A );

			// Format the data with correct field names for frontend.
			$formatted_referrals = array();
			foreach ( $referrals as $referral ) {
				$formatted_referrals[] = array(
					'id'               => $referral['id'],
					'order_id'         => $referral['order_id'],
					'customer_email'   => $referral['customer_email'],
					'order_value'      => number_format( (float) $referral['order_value'], 2 ),
					'discount_amount'  => number_format( (float) $referral['discount_amount'], 2 ),
					'date'             => gmdate( 'j M, Y', strtotime( $referral['order_date'] ) ),
					'status'           => ucfirst( $referral['referral_status'] ),
					'coupon_code'      => $referral['coupon_code'],
					// Include all database fields for future use.
					'referrer_user_id' => $referral['referrer_user_id'],
					'referred_user_id' => $referral['referred_user_id'],
					'coupon_id'        => $referral['coupon_id'],
					'firstname'        => $referral['firstname'],
					'lastname'         => $referral['lastname'],
					'fullname'         => ! empty( $referral['fullname'] ) ? $referral['fullname'] : 'Guest',
					'order_date'       => $referral['order_date'],
					'created_at'       => $referral['created_at'],
					'updated_at'       => $referral['updated_at'],
				);
			}

			return array(
				'referrals'  => $formatted_referrals,
				'pagination' => array(
					'current_page' => $page,
					'per_page'     => $per_page,
					'total_items'  => (int) $total_items,
					'total_pages'  => $total_pages,
					'has_more'     => $page < $total_pages,
				),
			);
		}

		/**
		 * Retrieves a paginated list of reward coupons for a specific referrer with optional time-based filtering.
		 *
		 * This function fetches reward coupons associated with a given referrer user ID, allowing for pagination and
		 * time-based filters (e.g., '1 Month', '3 Months', '1 Year'). The results include coupon details such as
		 * coupon code, discount type, usage limit, and current status. It implements cursor-based pagination for better
		 * performance with large datasets, especially for deep pagination beyond page 100. The function also caches the
		 * total item count for faster retrieval on subsequent requests.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $referrer_user_id The ID of the referrer whose reward coupons are being retrieved.
		 * @param int    $page            The current page number for pagination (defaults to 1).
		 * @param int    $per_page        The number of results per page (defaults to 10).
		 * @param string $time_filter     The time filter for the coupons (e.g., 'All Time', '1 Month', '3 Months').
		 *
		 * @return array An associative array containing:
		 *               - `reward_coupons`: A list of reward coupon details.
		 *               - `pagination`: Pagination information, including the current page, items per page,
		 *                 total items, total pages, and whether there are more pages.
		 */
		public function get_referrer_reward_coupons_with_pagination( $referrer_user_id, $page = 1, $per_page = 10, $time_filter = 'All Time' ) {
			global $wpdb;

			$reward_coupons_table = $wpdb->prefix . 'ecre_reward_coupons';

			// Build time filter condition based on created_at.
			$time_condition = '';
			$time_params    = array();

			if ( 'All Time' !== $time_filter ) {
				switch ( $time_filter ) {
					case '1 Month':
						$time_condition = ' AND rc.created_at >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-1 month' ) );
						break;
					case '3 Months':
						$time_condition = ' AND rc.created_at >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-3 months' ) );
						break;
					case '6 Months':
						$time_condition = ' AND rc.created_at >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-6 months' ) );
						break;
					case '1 Year':
						$time_condition = ' AND rc.created_at >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-1 year' ) );
						break;
				}
			}

			// Cache key for count query (5 minutes cache).
			$cache_key   = 'ecre_coupon_count_' . $referrer_user_id . '_' . md5( $time_filter );
			$total_items = wp_cache_get( $cache_key, 'ecre_reward_coupons' );

			if ( false === $total_items ) {
				// Get total count with timeout protection.
				$count_sql    = "SELECT COUNT(*) FROM {$reward_coupons_table} rc WHERE rc.referrer_user_id = %d" . $time_condition;
				$count_params = array_merge( array( $referrer_user_id ), $time_params );
				// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$total_items = $wpdb->get_var( $wpdb->prepare( $count_sql, $count_params ) );

				// Cache for 5 minutes.
				wp_cache_set( $cache_key, $total_items, 'ecre_reward_coupons', 300 );
			}

			// Calculate pagination.
			$total_pages = ceil( $total_items / $per_page );
			$offset      = ( $page - 1 ) * $per_page;

			// For deep pagination, use cursor-based pagination instead of OFFSET.
			$order_condition = 'ORDER BY rc.created_at DESC, rc.id DESC';
			if ( $page > 100 ) { // Switch to cursor for deep pagination.
				// Get the last item from previous page as cursor.
				$cursor_offset = ( $page - 1 ) * $per_page - 1;
				$cursor_sql    = "SELECT rc.created_at, rc.id FROM {$reward_coupons_table} rc 
							WHERE rc.referrer_user_id = %d" . $time_condition . "
							ORDER BY rc.created_at DESC, rc.id DESC
							LIMIT 1 OFFSET %d";
				$cursor_params = array_merge( array( $referrer_user_id ), $time_params, array( $cursor_offset ) );
				// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$cursor = $wpdb->get_row( $wpdb->prepare( $cursor_sql, $cursor_params ) );

				if ( $cursor ) {
					$time_condition .= ' AND (rc.created_at < %s OR (rc.created_at = %s AND rc.id < %d))';
					$time_params[]   = $cursor->created_at;
					$time_params[]   = $cursor->created_at;
					$time_params[]   = $cursor->id;
					$offset          = 0; // No offset needed with cursor.
				}
			}

			// Get reward coupons data - only fetch essential fields first.
			$sql = "SELECT 
						rc.id,
						rc.referrer_user_id,
						rc.order_id,
						rc.coupon_code,
						rc.coupon_id,
						rc.discount_type,
						rc.discount_amount,
						rc.usage_limit,
						rc.usage_count,
						rc.status,
						rc.expires_at,
						rc.created_at
					FROM {$reward_coupons_table} rc
					WHERE rc.referrer_user_id = %d" . $time_condition . "
					{$order_condition}
					LIMIT %d OFFSET %d";

			$sql_params = array_merge(
				array( $referrer_user_id ),
				$time_params,
				array( $per_page, $offset )
			);

			// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$reward_coupons = $wpdb->get_results( $wpdb->prepare( $sql, $sql_params ), ARRAY_A );

			// Format the data for frontend consumption.
			$formatted_coupons = array();
			foreach ( $reward_coupons as $coupon ) {
				// Format discount amount with currency symbol or percentage.
				$discount_display = '';
				if ( 'percent' === $coupon['discount_type'] ) {
					$discount_display = number_format( (float) $coupon['discount_amount'], 0 ) . '%';
				} else {
					$discount_display = number_format( (float) $coupon['discount_amount'], 2 );
				}

				// Format usage limit display - synced from WooCommerce.
				$usage_display = '';
				if ( $coupon['usage_limit'] > 0 ) {
					$usage_display = $coupon['usage_count'] . '/' . $coupon['usage_limit'];
				} else {
					$usage_display = $coupon['usage_count'] . '/∞';
				}

				// Use the actual discount_type from database.
				$coupon_type = ucfirst( str_replace( '_', ' ', $coupon['discount_type'] ) );

				// Determine current status - check if expired.
				$current_status = $coupon['status'];
				if ( $coupon['expires_at'] && strtotime( $coupon['expires_at'] ) < time() && 'active' === $coupon['status'] ) {
					$current_status = 'expired';
				}

				// Map database status to display status.
				$status_display = ucfirst( $current_status );
				if ( 'active' === $current_status ) {
					$status_display = 'Available';
				} elseif ( 'used' === $current_status ) {
					$status_display = 'Used';
				} elseif ( 'expired' === $current_status ) {
					$status_display = 'Expired';
				} elseif ( 'disabled' === $current_status ) {
					$status_display = 'Removed';
				}

				$formatted_coupons[] = array(
					'id'         => $coupon['id'],
					'code'       => strtoupper( $coupon['coupon_code'] ),
					'issuedDate' => gmdate( 'j M, Y', strtotime( $coupon['created_at'] ) ),
					'expiryDate' => $coupon['expires_at'] ? gmdate( 'j M, Y', strtotime( $coupon['expires_at'] ) ) : '--',
					'amount'     => $discount_display,
					'usageLimit' => $usage_display,
					'type'       => $coupon_type,
					'status'     => $status_display,
					// Include essential fields only.
					'order_id'   => $coupon['order_id'],
					'coupon_id'  => $coupon['coupon_id'],
					'raw_status' => $coupon['status'],
				);
			}

			return array(
				'reward_coupons' => $formatted_coupons,
				'pagination'     => array(
					'current_page' => $page,
					'per_page'     => $per_page,
					'total_items'  => (int) $total_items,
					'total_pages'  => $total_pages,
					'has_more'     => $page < $total_pages,
				),
			);
		}

		/**
		 * Retrieves a paginated list of reward points for a specific referrer with optional time-based filtering.
		 *
		 * This function fetches reward points associated with a given referrer user ID, supporting pagination and
		 * optional time-based filters (e.g., '1 Month', '3 Months', '1 Year'). The results include details such as
		 * the total points earned, the date they were earned, and a unique identifier. It calculates the total number
		 * of reward points and applies pagination, returning a formatted list of the results.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $referrer_user_id The ID of the referrer whose reward points are being retrieved.
		 * @param int    $page            The current page number for pagination (defaults to 1).
		 * @param int    $per_page        The number of results per page (defaults to 10).
		 * @param string $time_filter     The time filter for the reward points (e.g., 'All Time', '1 Month', '3 Months').
		 *
		 * @return array An associative array containing:
		 *               - `reward_points`: A list of reward point details including user email, points, and earned date.
		 *               - `pagination`: Pagination information, including the current page, items per page,
		 *                 total items, total pages, and whether there are more pages.
		 */
		public function get_referrer_reward_points_with_pagination( $referrer_user_id, $page = 1, $per_page = 10, $time_filter = 'All Time' ) {
			global $wpdb;

			$reward_points_table = $wpdb->prefix . 'reward_points';
			$time_condition      = '';
			$time_params         = array();

			if ( 'All Time' !== $time_filter ) {
				switch ( $time_filter ) {
					case '1 Month':
						$time_condition = ' AND earned_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-1 month' ) );
						break;
					case '3 Months':
						$time_condition = ' AND earned_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-3 months' ) );
						break;
					case '6 Months':
						$time_condition = ' AND earned_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-6 months' ) );
						break;
					case '1 Year':
						$time_condition = ' AND earned_date >= %s';
						$time_params[]  = gmdate( 'Y-m-d H:i:s', strtotime( '-1 year' ) );
						break;
				}
			}

			$count_sql    = "SELECT COUNT(*) FROM {$reward_points_table} WHERE user_id = %d" . $time_condition;
			$count_params = array_merge( array( $referrer_user_id ), $time_params );
			// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$total_items = $wpdb->get_var( $wpdb->prepare( $count_sql, $count_params ) );

			// Calculate pagination.
			$total_pages = ceil( $total_items / $per_page );
			$offset      = ( $page - 1 ) * $per_page;

			// Get reward points data - same pattern as referral history.
			$sql = "SELECT 
						id,
						user_id,
						email,
						points,
						earned_date,
						unique_id,
						order_id
					FROM {$reward_points_table}
					WHERE user_id = %d" . $time_condition . "
					ORDER BY earned_date DESC
					LIMIT %d OFFSET %d";

			$sql_params = array_merge(
				array( $referrer_user_id ),
				$time_params,
				array( $per_page, $offset )
			);

			// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$reward_points = $wpdb->get_results( $wpdb->prepare( $sql, $sql_params ), ARRAY_A );

			// Format the data for frontend consumption - same pattern as others.
			$formatted_points = array();
			foreach ( $reward_points as $point ) {
				$formatted_points[] = array(
					'id'              => $point['id'],
					'user_id'         => $point['user_id'],
					'email'           => $point['email'],
					'points'          => intval( $point['points'] ),
					'earned_date'     => gmdate( 'j M, Y', strtotime( $point['earned_date'] ) ),
					'earned_date_raw' => $point['earned_date'],
					'unique_id'       => $point['unique_id'],
					'order_id'        => isset( $point['order_id'] ) ? intval( $point['order_id'] ) : null,
					'points_display'  => intval( $point['points'] ) . ' Points',
				);
			}

			return array(
				'reward_points' => $formatted_points,
				'pagination' => array(
					'current_page' => $page,
					'per_page'     => $per_page,
					'total_items'  => (int) $total_items,
					'total_pages'  => $total_pages,
					'has_more'     => $page < $total_pages,
				),
			);
		}

		/**
		 * Retrieves a user-specific reward setting or falls back to a global default value.
		 *
		 * This function checks if a specific reward setting exists for a given user in the `ecre_user_reward_settings`
		 * table. If the setting exists and is active, it converts the setting value according to its data type (e.g.,
		 * number, boolean, array). If no setting is found, it returns the provided global default value.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $user_id       The ID of the user whose reward setting is being retrieved.
		 * @param string $setting_key   The key of the reward setting to fetch.
		 * @param mixed  $global_default The default value to return if the user-specific setting is not found.
		 *
		 * @return mixed The user-specific reward setting value or the global default if the setting is not found.
		 */
		public function get_user_reward_setting( $user_id, $setting_key, $global_default = null ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_user_reward_settings';

			$setting = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT setting_value, data_type FROM `{$wpdb->prefix}ecre_user_reward_settings` 
					WHERE user_id = %d AND setting_key = %s AND is_active = 1",
					$user_id,
					$setting_key
				)
			);

			if ( $setting ) {
				// Convert based on data type.
				switch ( $setting->data_type ) {
					case 'number':
						return (float) $setting->setting_value;
					case 'boolean':
						return (bool) $setting->setting_value;
					case 'array':
						return json_decode( $setting->setting_value, true );
					default:
						return $setting->setting_value;
				}
			}

			return $global_default;
		}

		/**
		 * Saves a user-specific reward setting to the database.
		 *
		 * This function inserts or updates a specific reward setting for a user in the `ecre_user_reward_settings` table.
		 * The setting value is saved with a specified data type (e.g., string, number, array). If the data type is 'array',
		 * the value is encoded as JSON before being saved. The setting is marked as active upon saving.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $user_id       The ID of the user whose reward setting is being saved.
		 * @param string $setting_key   The key of the reward setting to save.
		 * @param mixed  $setting_value The value of the reward setting to save.
		 * @param string $data_type     The data type of the setting (defaults to 'string'). Can be 'string', 'number', 'boolean', or 'array'.
		 *
		 * @return bool Returns true if the setting was successfully saved, false otherwise.
		 */
		public function save_user_reward_setting( $user_id, $setting_key, $setting_value, $data_type = 'string' ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_user_reward_settings';

			// Convert value based on data type.
			if ( 'array' === $data_type ) {
				$setting_value = wp_json_encode( $setting_value );
			}

			$result = $wpdb->replace(
				$table_name,
				array(
					'user_id'       => $user_id,
					'setting_key'   => $setting_key,
					'setting_value' => $setting_value,
					'data_type'     => $data_type,
					'is_active'     => 1,
				),
				array( '%d', '%s', '%s', '%s', '%d' )
			);

			return false !== $result;
		}

		/**
		 * Deletes a user-specific reward setting or all settings for the user.
		 *
		 * This function removes a specific reward setting for a user if the `setting_key` is provided. If no `setting_key`
		 * is provided, it deletes all reward settings for the user, effectively resetting them to the global default.
		 * The deletion is performed in the `ecre_user_reward_settings` table.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $user_id     The ID of the user whose reward setting(s) are to be deleted.
		 * @param string $setting_key The key of the reward setting to delete (optional). If not provided, all settings for the user will be deleted.
		 *
		 * @return bool Returns true if the setting(s) were successfully deleted, false otherwise.
		 */
		public function delete_user_reward_setting( $user_id, $setting_key = null ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_user_reward_settings';

			if ( $setting_key ) {
				// Delete specific setting.
				$result = $wpdb->delete(
					$table_name,
					array(
						'user_id'     => $user_id,
						'setting_key' => $setting_key,
					),
					array( '%d', '%s' )
				);
			} else {
				// Delete all settings for user.
				$result = $wpdb->delete(
					$table_name,
					array( 'user_id' => $user_id ),
					array( '%d' )
				);
			}

			return false !== $result;
		}

		/**
		 * Retrieves all custom reward settings for a specific user.
		 *
		 * This function fetches all active custom reward settings for a given user from the `ecre_user_reward_settings`
		 * table. It processes each setting based on its data type (e.g., number, boolean, array) and returns the settings
		 * in an associative array with the setting keys as the array keys and the corresponding setting values.
		 * The values are converted according to their data type, such as converting numbers to floats or decoding arrays from JSON.
		 *
		 * @since 1.0.0
		 *
		 * @param int $user_id The ID of the user whose custom settings are being retrieved.
		 *
		 * @return array An associative array of custom settings where the key is the setting key,
		 *               and the value is the corresponding setting value, properly converted.
		 */
		public function get_user_custom_settings( $user_id ) {
			global $wpdb;

			$table_name = $wpdb->prefix . 'ecre_user_reward_settings';

			$settings = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT setting_key, setting_value, data_type FROM `{$wpdb->prefix}ecre_user_reward_settings` 
					WHERE user_id = %d AND is_active = 1",
					$user_id
				),
				ARRAY_A
			);

			$formatted_settings = array();
			foreach ( $settings as $setting ) {
				$value = $setting['setting_value'];

				// Convert based on data type.
				switch ( $setting['data_type'] ) {
					case 'number':
						$value = (float) $value;
						break;
					case 'boolean':
						$value = (bool) $value;
						break;
					case 'array':
						$value = json_decode( $value, true );
						break;
				}

				$formatted_settings[ $setting['setting_key'] ] = $value;
			}

			return $formatted_settings;
		}

		/**
		 * Retrieves user settings merged with global defaults.
		 *
		 * This function combines the global default settings with the user's custom settings by fetching the user's custom
		 * settings from the `ecre_user_reward_settings` table. It then merges these user-specific settings with the provided
		 * global settings, with user-specific settings taking precedence for certain keys. Only customizable settings are merged,
		 * ensuring that the final returned settings reflect both the global defaults and any user-specific customizations.
		 *
		 * @since 1.0.0
		 *
		 * @param int   $user_id        The ID of the user whose custom settings are being retrieved.
		 * @param array $global_settings An array of global default settings to be merged with the user’s custom settings.
		 *
		 * @return array An associative array of merged settings, where user-specific settings override the global defaults.
		 */
		public function get_merged_user_settings( $user_id, $global_settings ) {
			$user_custom_settings = $this->get_user_custom_settings( $user_id );

			// Merge user-specific settings with global defaults.
			$merged_settings = $global_settings;

			$customizable_settings = array(
				// Reward Points settings.
				'rewardPoint',
				'redeemPoint',
				'redeemLimit',
				'enableRedeemLimit',
				'redeemDiscount',

				// Reward Coupons settings  .
				'rewardType',
				'rewardDiscount',
				'rewardDiscountCapping',
				'enableRewardExpiry',
				'rewardCouponValidity',
				'enableRewardCouponUsageLimit',
				'rewardCouponUsageLimit',
				'referralDiscount',
				'referralDiscountCapping',
				'enableReferralLimit',
				'referralMonthlyLimit',
				'referralMinimumPurchaseAmount',
				'rewardMinimumPurchaseAmount',
			);

			foreach ( $customizable_settings as $setting_key ) {
				if ( isset( $user_custom_settings[ $setting_key ] ) ) {
					$merged_settings[ $setting_key ] = $user_custom_settings[ $setting_key ];
				}
			}

			return $merged_settings;
		}

		/**
		 * Issues reward points to a user manually with a table existence check.
		 *
		 * This function manually adds reward points to a user's account. It first checks if the necessary database table
		 * (`reward_points`) exists. If the table does not exist or the user is not found, the function returns `false`.
		 * If the table exists, it inserts a new record into the `reward_points` table with the provided points, user email,
		 * and other relevant details. It also updates the user's total and available points stored in their user meta data.
		 *
		 * @since 1.0.0
		 *
		 * @param int    $user_id  The ID of the user to whom the reward points will be issued.
		 * @param int    $points   The number of reward points to issue to the user.
		 * @param string $source   The source of the points (defaults to 'manual_issue').
		 *
		 * @return bool Returns `true` if the points were successfully issued and updated, `false` otherwise.
		 */
		public function issue_reward_points( $user_id, $points, $source = 'manual_issue' ) {
			global $wpdb;

			$table_name   = $wpdb->prefix . 'reward_points';
			$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );

			if ( $table_exists !== $table_name ) {
				return false;
			}

			$user_data = get_userdata( $user_id );
			if ( ! $user_data ) {
				return false;
			}

			$user_email  = $user_data->user_email;
			$earned_date = gmdate( 'Y-m-d' );
			$unique_id   = wp_generate_uuid4();

			// Insert with existing table structure.
			$result = $wpdb->insert(
				$table_name,
				array(
					'user_id'     => $user_id,
					'email'       => $user_email,
					'points'      => $points,
					'earned_date' => $earned_date,
					'unique_id'   => $unique_id,
				),
				array(
					'%d',
					'%s',
					'%d',
					'%s',
					'%s',
				)
			);

			if ( false === $result ) {
				return false;
			}

			// Update user meta totals.
			$available_points = intval( get_user_meta( $user_id, 'ecre_available_points', true ) );
			$total_points     = intval( get_user_meta( $user_id, 'ecre_reward_points', true ) );

			update_user_meta( $user_id, 'ecre_reward_points', $total_points + $points );
			update_user_meta( $user_id, 'ecre_available_points', $available_points + $points );

			return true;
		}
	}
}
