<?php

namespace WC_Affiliate\Models;

defined( 'ABSPATH' ) || exit;

use WC_Affiliate\Abstracts\Model;
use WC_Affiliate\Traits\Date;
use WC_Affiliate\Traits\Validation;
use WP_Error;

/**
 * Referral Model Class
 *
 * Handles all operations related to referral management in the affiliate system. This model provides functionality
 * for creating, reading, updating, and deleting referral records, as well as managing referral statuses,
 * commission calculations, and statistics tracking.
 */
class Referral extends Model {

	use Date;
	use Validation;

	/**
	 * Referral statuses
	 */
	const STATUS_PENDING   = 'pending';
	const STATUS_PAID      = 'paid';
	const STATUS_APPROVED  = 'approved';
	const STATUS_REJECTED  = 'rejected';
	const STATUS_CANCELLED = 'cancelled';

	/**
	 * Referral types
	 */
	const TYPE_SALE      = 'sale';
	const TYPE_RECURRING = 'recurring';

	/**
	 * Table name
	 *
	 * @var ?string
	 */
	protected static ?string $table_name = 'wca_referrals';

	/**
	 * Load referral data (override parent to handle JSON)
	 *
	 * @return void
	 */
	protected function load(): void {
		parent::load();

		if ( ! empty( $this->data ) ) {
			$this->data['time_formatted']        = $this->get_time_formatted();
			$this->data['commission_formatted']  = wca_format_price( $this->get_commission() );
			$this->data['order_total_formatted'] = wca_format_price( $this->get_order_total() );

			// Add affiliate info.
			$affiliate = $this->get_affiliate();
			if ( $affiliate instanceof Affiliate && $affiliate->exists() ) {
				$this->data['affiliate'] = $affiliate->to_array();
			} else {
				$this->data['affiliate'] = array();
			}

			// Add order info.
			$order = $this->get_order();
			if ( $order ) {
				$this->data['order_status'] = method_exists( $order, 'get_status' ) ? $order->get_status() : '';
				$this->data['order_date']   = method_exists( $order, 'get_date_created' ) ? $order->get_date_created() : '';
			}

			// Decode products JSON.
			if ( ! empty( $this->data['products'] ) && is_string( $this->data['products'] ) ) {
				$this->data['products'] = maybe_unserialize( $this->data['products'] );
			}
		}

		$this->data = apply_filters( 'wc_affiliate_referral_data', $this->data, $this->id );
	}

	/**
	 * Check if referral exists
	 *
	 * @return bool True if referral exists, false otherwise
	 */
	public function exists(): bool {
		return ! empty( $this->data );
	}

	/**
	 * Get affiliate ID
	 *
	 * @return int Affiliate ID or 0 if not set
	 */
	public function get_affiliate_id(): int {
		return (int) $this->get( 'affiliate' );
	}

	/**
	 * Get affiliate
	 *
	 * @return Affiliate|null Affiliate object or null if not found
	 */
	public function get_affiliate(): ?Affiliate {
		$affiliate_id = $this->get_affiliate_id();

		return $affiliate_id ? new Affiliate( $affiliate_id ) : null;
	}

	/**
	 * Get visit ID
	 *
	 * @return int Visit ID or 0 if not set
	 */
	public function get_visit_id(): int {
		return (int) $this->get( 'visit' );
	}

	/**
	 * Get visit object
	 *
	 * @return Visit|null Visit object or null if not found
	 */
	public function get_visit(): ?Visit {
		$visit_id = $this->get_visit_id();

		return $visit_id ? new Visit( $visit_id ) : null;
	}

	/**
	 * Get order ID
	 *
	 * @return int Order ID or 0 if not set
	 */
	public function get_order_id(): int {
		return (int) $this->get( 'order_id' );
	}

	/**
	 * Get order object
	 *
	 * @return mixed Order object (WooCommerce or EasyCommerce) or null if not found
	 */
	public function get_order() {
		$order_id = $this->get_order_id();

		if ( ! $order_id ) {
			return null;
		}

		// Try WooCommerce first.
		if ( function_exists( 'wc_get_order' ) ) {
			$order = wc_get_order( $order_id );
			if ( $order ) {
				return $order;
			}
		}

		// Try EasyCommerce.
		if ( class_exists( \EasyCommerce\Models\Order::class ) ) {
			return new \EasyCommerce\Models\Order( $order_id );
		}

		return null;
	}

	/**
	 * Get transaction ID
	 *
	 * @return int Transaction ID or 0 if not set
	 */
	public function get_transaction_id(): int {
		return (int) $this->get( 'transaction_id' );
	}

	/**
	 * Get transaction object
	 *
	 * @return Transaction|null Transaction object or null if not found
	 */
	public function get_transaction(): ?Transaction {
		$transaction_id = $this->get_transaction_id();

		return $transaction_id ? new Transaction( $transaction_id ) : null;
	}

	/**
	 * Get referral type
	 *
	 * @return string Referral type
	 */
	public function get_type(): string {
		return $this->get( 'type' );
	}

	/**
	 * Get commission amount
	 *
	 * @return float Commission amount
	 */
	public function get_commission(): float {
		return (float) $this->get( 'commission' );
	}

	/**
	 * Get order total
	 *
	 * @return float Order total amount
	 */
	public function get_order_total(): float {
		return (float) $this->get( 'order_total' );
	}

	/**
	 * Get payment status
	 *
	 * @return string Payment status
	 */
	public function get_status(): string {
		return $this->get( 'payment_status' );
	}

	/**
	 * Check if referral is paid
	 *
	 * @return bool True if paid, false otherwise
	 */
	public function is_paid(): bool {
		return $this->get_status() === self::STATUS_PAID;
	}

	/**
	 * Check if referral is pending
	 *
	 * @return bool True if pending, false otherwise
	 */
	public function is_pending(): bool {
		return $this->get_status() === self::STATUS_PENDING;
	}

	/**
	 * Check if referral is completed (paid)
	 *
	 * @return bool True if completed (paid), false otherwise
	 */
	public function is_completed(): bool {
		return $this->get_status() === self::STATUS_PAID;
	}

	/**
	 * Get products
	 *
	 * @return array Products array
	 */
	public function get_products(): array {
		return $this->get( 'products' ) ?? array();
	}

	/**
	 * Get referral time
	 *
	 * @return int Unix timestamp of referral time
	 */
	public function get_time(): int {
		return (int) $this->get( 'time' );
	}

	/**
	 * Get formatted referral time
	 *
	 * @param string $format Date format, defaults to WordPress settings if null.
	 *
	 * @return string Formatted date/time string.
	 */
	public function get_time_formatted( string $format = 'Y-m-d H:i:s' ): string {
		return wp_date( $format, $this->get_time() );
	}

	/**
	 * Get referrals with filters
	 *
	 * @param array $args Filter arguments.
	 *
	 * @return Referral[] Array of Referral objects matching criteria.
	 */
	public function get_referrals( array $args = array() ): array {
		global $wpdb;

		// Get all available referral statuses via filter.
		$available_statuses = $this->get_available_statuses();

		$defaults = array(
			'affiliate_id' => 0,
			'status'       => array_keys( $available_statuses ),
			'type'         => '',
			'limit'        => 50,
			'offset'       => 0,
			'orderby'      => 'time',
			'order'        => 'DESC',
			'start_date'   => '',
			'end_date'     => '',
			'order_id'     => 0,
			'product_id'   => 0,
			'search'       => '',
		);

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

		// Allow filtering query arguments.
		$args = apply_filters( 'wc_affiliate_get_referrals_args', $args );

		$table = $wpdb->prefix . self::$table_name;

		// Base query.
		$query        = "SELECT * FROM {$table} WHERE 1=1";
		$query_params = array();

		// Affiliate filter.
		if ( $args['affiliate_id'] ) {
			$query         .= ' AND affiliate = %d';
			$query_params[] = $args['affiliate_id'];
		}

		// Status filter.
		if ( $args['status'] ) {
			$statuses = $args['status'];
			if ( count( $statuses ) > 1 ) {
				$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
				$query       .= " AND payment_status IN ($placeholders)";
				$query_params = array_merge( $query_params, $statuses );
			} else {
				$query         .= ' AND payment_status = %s';
				$query_params[] = $statuses[0];
			}
		}

		// Type filter.
		if ( $args['type'] ) {
			$query         .= ' AND type = %s';
			$query_params[] = $args['type'];
		}

		// Order filter.
		if ( $args['order_id'] ) {
			$query         .= ' AND order_id = %d';
			$query_params[] = $args['order_id'];
		}

		// Product filter (search in products JSON).
		if ( $args['product_id'] ) {
			$query         .= ' AND products LIKE %s';
			$query_params[] = '%"id":' . $args['product_id'] . '%';
		}

		if ( ! empty( $args['search'] ) ) {
			$search_term = '%' . $wpdb->esc_like( $args['search'] ) . '%';

			// Search in affiliate name (join with users table), products JSON, or transaction_id.
			$query         .= ' AND (affiliate IN (SELECT ID FROM ' . $wpdb->users . ' WHERE display_name LIKE %s OR user_login LIKE %s OR user_email LIKE %s) OR products LIKE %s OR transaction_id LIKE %s)';
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
		}

		// Date range filter.
		if ( $args['start_date'] ) {
			$start_time     = strtotime( $args['start_date'] . ' 00:00:00' );
			$query         .= ' AND time >= %d';
			$query_params[] = $start_time;
		}

		if ( $args['end_date'] ) {
			$end_time       = strtotime( $args['end_date'] . ' 23:59:59' );
			$query         .= ' AND time <= %d';
			$query_params[] = $end_time;
		}

		// Order by.
		$query .= " ORDER BY {$args['orderby']} {$args['order']}";

		// Limit.
		$query .= " LIMIT {$args['offset']}, {$args['limit']}";

		// Execute query.
		$results = $wpdb->get_results(
			empty( $query_params ) ? $query : $wpdb->prepare( $query, ...$query_params )
		);

		$referrals = array();
		foreach ( $results as $result ) {
			$referral       = new self();
			$referral->id   = $result->id;
			$referral->data = (array) $result;

			$referral->load();

			// Decode products.
			if ( ! empty( $referral->data['products'] ) && is_string( $referral->data['products'] ) ) {
				$referral->data['products'] = maybe_unserialize( $referral->data['products'] );
			}

			$referrals[] = $referral;
		}

		return apply_filters( 'wc_affiliate_get_referrals', $referrals, $args );
	}

	/**
	 * @param int $id
	 *
	 * @return Referral|null
	 */
	public function get_by_id( int $id ): ?Referral {
		global $wpdb;

		$table  = $wpdb->prefix . self::$table_name;
		$result = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( $result ) {
			$referral       = new self();
			$referral->id   = $result['id'];
			$referral->data = (array) $result;
		}

		return $referral ?? null;
	}

	/**
	 * Create new referral
	 *
	 * @param array $data Associative array of referral data.
	 *
	 * @return Referral|WP_Error Newly created Referral object or WP_Error on failure.
	 */
	public function create( array $data ) {
		global $wpdb;

		// Allow plugins to prevent referral creation.
		$can_create = apply_filters( 'wc_affiliate_can_create_referral', true, $data );
		if ( ! $can_create ) {
			return new WP_Error( 'creation_prevented', __( 'Referral creation prevented by filter', 'wc-affiliate' ) );
		}

		// Fire action before referral creation.
		do_action( 'wc_affiliate_referral_before_create', $data );

		$referral       = new self();
		$sanitized_data = $referral->sanitize_data( $data );
		$validation     = $referral->validate_data( $sanitized_data );

		if ( true !== $validation ) {
			return new WP_Error( 'validation_failed', implode( ', ', $validation ) );
		}

		$sanitized_data = apply_filters( 'wc_affiliate_referral_create_data', $sanitized_data, $data );

		// Set default values.
		$sanitized_data['time']           = $sanitized_data['time'] ?? time();
		$sanitized_data['payment_status'] = $sanitized_data['payment_status'] ?? self::STATUS_PENDING;
		$sanitized_data['type']           = $sanitized_data['type'] ?? self::TYPE_SALE;

		// Encode products array.
		if ( isset( $sanitized_data['products'] ) && is_array( $sanitized_data['products'] ) ) {
			$sanitized_data['products'] = maybe_serialize( $sanitized_data['products'] );
		}

		$table  = $wpdb->prefix . self::$table_name;
		$result = $wpdb->insert(
			$table,
			$sanitized_data,
			$referral->get_format_array( $sanitized_data )
		);

		if ( $result ) {
			$referral_id = $wpdb->insert_id;
			$referral    = new self( $referral_id );

			// Fire action after referral creation.
			do_action( 'wc_affiliate_referral_after_create', $referral, $sanitized_data, $data );

			// Update visit with referral ID if visit provided.
			if ( ! empty( $sanitized_data['visit'] ) ) {
				$wpdb->update(
					$wpdb->prefix . 'wca_visits',
					array( 'referral' => $referral_id ),
					array( 'id' => $sanitized_data['visit'] ),
					array( '%d' ),
					array( '%d' )
				);
			}

			do_action( 'wc_affiliate_new_referral', $sanitized_data['affiliate'], $referral->to_array() );

			return $referral;
		}

		return new WP_Error( 'create_failed', __( 'Failed to create referral', 'wc-affiliate' ) );
	}

	/**
	 * Update referral data
	 *
	 * @param array $data Associative array of data to update.
	 *
	 * @return bool|WP_Error True on success, WP_Error on failure
	 */
	public function update( array $data ) {
		global $wpdb;

		// Allow plugins to prevent referral update.
		$can_update = apply_filters( 'wc_affiliate_can_update_referral', true, $data, $this->id );
		if ( ! $can_update ) {
			return new WP_Error( 'update_prevented', __( 'Referral update prevented by filter', 'wc-affiliate' ) );
		}

		// Fire action before referral update.
		do_action( 'wc_affiliate_referral_before_update', $data, $this->id );

		$sanitized_data = $this->sanitize_data( $data );
		$validation     = $this->validate_data( $sanitized_data );

		if ( true !== $validation ) {
			return new WP_Error( 'validation_failed', implode( ', ', $validation ) );
		}

		$sanitized_data = apply_filters( 'wc_affiliate_referral_update_data', $sanitized_data, $data, $this->id );

		// Encode products array if present.
		if ( isset( $sanitized_data['products'] ) && is_array( $sanitized_data['products'] ) ) {
			$sanitized_data['products'] = maybe_serialize( $sanitized_data['products'] );
		}

		$table  = $wpdb->prefix . self::$table_name;
		$result = $wpdb->update(
			$table,
			$sanitized_data,
			array( 'id' => $this->id ),
			$this->get_format_array( $sanitized_data ),
			array( '%d' )
		);

		if ( false !== $result ) {
			// Update local data.
			foreach ( $sanitized_data as $key => $value ) {
				if ( 'products' === $key && is_string( $value ) ) {
					$this->data[ $key ] = maybe_unserialize( $value );
				} else {
					$this->data[ $key ] = $value;
				}
			}

			// Fire action after referral update.
			do_action( 'wc_affiliate_referral_after_update', $data, $this->id, $sanitized_data );

			return true;
		}

		return new WP_Error( 'update_failed', __( 'Failed to update referral', 'wc-affiliate' ) );
	}

	/**
	 * Update referral status
	 *
	 * @param string $status New status to set.
	 *
	 * @return bool|WP_Error True on success, WP_Error on failure
	 */
	public function update_status( string $status ) {
		$valid_statuses = array(
			self::STATUS_PENDING,
			self::STATUS_PAID,
			self::STATUS_APPROVED,
			self::STATUS_REJECTED,
			self::STATUS_CANCELLED,
		);

		if ( ! in_array( $status, $valid_statuses, true ) ) {
			return new WP_Error( 'invalid_status', __( 'Invalid referral status', 'wc-affiliate' ) );
		}

		$old_status = $this->get_status();

		global $wpdb;
		$table = $wpdb->prefix . self::$table_name;

		$result = $wpdb->update(
			$table,
			array( 'payment_status' => $status ),
			array( 'id' => $this->id ),
			array( '%s' ),
			array( '%d' )
		);

		if ( false !== $result ) {
			$this->data['payment_status'] = $status;

			// Fire hooks based on status change.
			if ( $old_status !== $status ) {
				do_action( 'wc_affiliate_referral_status_changed', $this->id, $old_status, $status );

				if ( self::STATUS_PAID === $status ) {
					do_action( 'wc_affiliate_commission_earned', $this->get_affiliate_id(), $this->get_commission(), $this->to_array() );
				}
			}

			return true;
		}

		return new WP_Error( 'update_failed', __( 'Failed to update referral status', 'wc-affiliate' ) );
	}

	/**
	 * Delete referral
	 *
	 * @return bool|WP_Error True on success, WP_Error on failure
	 */
	public function delete() {
		global $wpdb;

		// Check if referral has completed referrals.
		if ( $this->is_completed() ) {
			return new WP_Error( 'cannot_delete', __( 'Cannot delete completed referral', 'wc-affiliate' ) );
		}

		$can_delete = apply_filters( 'wc_affiliate_referral_can_delete', true, $this->id, $this->data );
		if ( ! $can_delete ) {
			return new WP_Error( 'cannot_delete', __( 'Referral deletion prevented by filter', 'wc-affiliate' ) );
		}

		// Fire action before referral deletion.
		do_action( 'wc_affiliate_referral_before_delete', $this->id, $this->data );

		$table  = $wpdb->prefix . self::$table_name;
		$result = $wpdb->delete(
			$table,
			array( 'id' => $this->id ),
			array( '%d' )
		);

		if ( $result ) {
			// Fire action after referral deletion.
			do_action( 'wc_affiliate_referral_after_delete', $this->id, $this->data );

			$this->data = array();
			$this->id   = null;
		}

		return new WP_Error( 'delete_failed', __( 'Failed to delete referral', 'wc-affiliate' ) );
	}

	/**
	 * Get referral status counts
	 *
	 * @param array $args Optional filter arguments.
	 *
	 * @return array Array of statuses with counts.
	 */
	public function get_status_counts( array $args = array() ): array {
		global $wpdb;

		$defaults = array(
			'affiliate_id' => 0,
			'start_date'   => '',
			'end_date'     => '',
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = $wpdb->prefix . self::$table_name;

		$where_conditions = array( '1=1' );
		$query_params     = array();

		// Affiliate filter.
		if ( $args['affiliate_id'] ) {
			$where_conditions[] = 'affiliate = %d';
			$query_params[]     = $args['affiliate_id'];
		}

		// Date range filters.
		if ( $args['start_date'] ) {
			$start_time         = strtotime( $args['start_date'] . ' 00:00:00' );
			$where_conditions[] = 'time >= %d';
			$query_params[]     = $start_time;
		}

		if ( $args['end_date'] ) {
			$end_time           = strtotime( $args['end_date'] . ' 23:59:59' );
			$where_conditions[] = 'time <= %d';
			$query_params[]     = $end_time;
		}

		$where_clause = implode( ' AND ', $where_conditions );

		// Get all available referral statuses via filter.
		$available_statuses = $this->get_available_statuses();

		// Build WHERE condition for all available statuses.
		$status_placeholders = array_fill( 0, count( $available_statuses ), '%s' );
		$where_clause       .= ' AND payment_status IN ( ' . implode( ', ', $status_placeholders ) . ' )';
		$query_params        = array_merge( $query_params, array_keys( $available_statuses ) );

		$query = "SELECT
					payment_status as status,
					COUNT(*) as count,
					SUM(commission) as total_commission,
					SUM(order_total) as total_sales
				  FROM {$table}
				  WHERE {$where_clause}
				  GROUP BY payment_status";

		$results = $wpdb->get_results(
			$wpdb->prepare( $query, ...$query_params ),
			ARRAY_A
		);

		// Build status array with all available statuses, even if count is 0.
		$statuses = array();
		foreach ( $available_statuses as $status_key => $status_label ) {
			$statuses[ $status_key ] = array(
				'status'           => $status_key,
				'label'            => $status_label,
				'count'            => 0,
				'total_commission' => 0,
				'total_sales'      => 0,
			);
		}

		// Populate with actual data from database.
		foreach ( $results as $result ) {
			if ( isset( $statuses[ $result['status'] ] ) ) {
				$statuses[ $result['status'] ]['count']            = (int) $result['count'];
				$statuses[ $result['status'] ]['total_commission'] = (float) $result['total_commission'];
				$statuses[ $result['status'] ]['total_sales']      = (float) $result['total_sales'];
			}
		}

		// Allow filtering of the final status counts.
		$statuses = apply_filters( 'wc_affiliate_referral_status_counts', $statuses, $args );

		// Calculate totals.
		$total_count      = 0;
		$total_commission = 0;
		$total_sales      = 0;

		foreach ( $statuses as $status_data ) {
			$total_count      += $status_data['count'];
			$total_commission += $status_data['total_commission'];
			$total_sales      += $status_data['total_sales'];
		}

		return array(
			'statuses' => array_values( $statuses ),
			'totals'   => array(
				'count'            => $total_count,
				'total_commission' => $total_commission,
				'total_sales'      => $total_sales,
			),
		);
	}

	/**
	 * Get referral count
	 *
	 * @param array $args Filter arguments.
	 *
	 * @return int Count of referrals matching criteria.
	 */
	public function get_count( array $args = array() ): int {
		global $wpdb;

		// Get all available referral statuses via filter.
		$available_statuses = $this->get_available_statuses();

		$defaults = array(
			'affiliate_id' => 0,
			'status'       => array_keys( $available_statuses ),
			'type'         => '',
			'start_date'   => '',
			'end_date'     => '',
			'order_id'     => 0,
			'product_id'   => 0,
			'search'       => '',
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = $wpdb->prefix . self::$table_name;

		$query        = "SELECT COUNT(*) FROM {$table} WHERE 1=1";
		$query_params = array();

		// Apply same filters as get_referrals.
		if ( $args['affiliate_id'] ) {
			$query         .= ' AND affiliate = %d';
			$query_params[] = $args['affiliate_id'];
		}

		if ( $args['status'] ) {
			$statuses = is_array( $args['status'] ) ? $args['status'] : explode( ',', $args['status'] );
			if ( count( $statuses ) > 1 ) {
				$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
				$query       .= " AND payment_status IN ($placeholders)";
				$query_params = array_merge( $query_params, $statuses );
			} else {
				$query         .= ' AND payment_status = %s';
				$query_params[] = $statuses[0];
			}
		}

		if ( $args['type'] ) {
			$query         .= ' AND type = %s';
			$query_params[] = $args['type'];
		}

		if ( $args['order_id'] ) {
			$query         .= ' AND order_id = %d';
			$query_params[] = $args['order_id'];
		}

		if ( $args['product_id'] ) {
			$query         .= ' AND products LIKE %s';
			$query_params[] = '%"' . $args['product_id'] . '"%';
		}

		// Search filter.
		if ( ! empty( $args['search'] ) ) {
			$search_term = '%' . $wpdb->esc_like( $args['search'] ) . '%';

			// Search in affiliate name (join with users table) or products JSON.
			$query         .= ' AND (affiliate IN (SELECT ID FROM ' . $wpdb->users . ' WHERE display_name LIKE %s OR user_login LIKE %s OR user_email LIKE %s) OR products LIKE %s)';
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
		}

		if ( $args['start_date'] ) {
			$start_time     = strtotime( $args['start_date'] . ' 00:00:00' );
			$query         .= ' AND time >= %d';
			$query_params[] = $start_time;
		}

		if ( $args['end_date'] ) {
			$end_time       = strtotime( $args['end_date'] . ' 23:59:59' );
			$query         .= ' AND time <= %d';
			$query_params[] = $end_time;
		}

		$count = (int) $wpdb->get_var(
			empty( $query_params ) ? $query : $wpdb->prepare( $query, ...$query_params )
		);

		return apply_filters( 'wc_affiliate_referral_get_count', $count, $args );
	}

	/**
	 * Get referral statistics
	 *
	 * @param array $args Filter arguments.
	 *
	 * @return array Associative array of statistics
	 */
	public function get_statistics( array $args = array() ): array {
		global $wpdb;

		$defaults = array(
			'affiliate_id' => 0,
			'start_date'   => '',
			'end_date'     => '',
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = $wpdb->prefix . self::$table_name;

		$where_conditions = array();
		$query_params     = array();

		if ( $args['affiliate_id'] ) {
			$where_conditions[] = 'affiliate = %d';
			$query_params[]     = $args['affiliate_id'];
		}

		if ( $args['start_date'] ) {
			$start_time         = strtotime( $args['start_date'] . ' 00:00:00' );
			$where_conditions[] = 'time >= %d';
			$query_params[]     = $start_time;
		}

		if ( $args['end_date'] ) {
			$end_time           = strtotime( $args['end_date'] . ' 23:59:59' );
			$where_conditions[] = 'time <= %d';
			$query_params[]     = $end_time;
		}

		$where_clause = empty( $where_conditions ) ? '' : 'WHERE ' . implode( ' AND ', $where_conditions );

		$query = "SELECT
					COUNT(*) as total_referrals,
					COUNT(CASE WHEN payment_status = 'paid' THEN 1 END) as paid_referrals,
					COUNT(CASE WHEN payment_status = 'pending' THEN 1 END) as pending_referrals,
					COUNT(CASE WHEN payment_status = 'approved' THEN 1 END) as approved_referrals,
					SUM(IF(payment_status = 'paid', commission, 0)) as paid_commissions,
					SUM(IF(payment_status IN ('approved'), commission, 0)) as unpaid_commissions,
					SUM(IF(payment_status IN ('paid', 'approved'), commission, 0)) as total_commissions,
					SUM(order_total) as total_sales,
					AVG(CASE WHEN payment_status IN ('paid', 'approved') THEN commission END) as avg_commission,
					COUNT(DISTINCT affiliate) as unique_affiliates
				  FROM {$table} {$where_clause}";

		$stats = $wpdb->get_row(
			empty( $query_params ) ? $query : $wpdb->prepare( $query, ...$query_params ),
			ARRAY_A
		);

		// Calculate percentages.
		$stats['paid_percentage']               = $stats['total_referrals'] > 0 ? ( $stats['paid_referrals'] / $stats['total_referrals'] ) * 100 : 0;
		$stats['total_commissions_formatted']   = wca_format_price( $stats['total_commissions'] ?? 0 );
		$stats['paid_commissions_formatted']    = wca_format_price( $stats['paid_commissions'] ?? 0 );
		$stats['pending_commissions_formatted'] = wca_format_price( $stats['unpaid_commissions'] ?? 0 );

		return apply_filters( 'wc_affiliate_referral_statistics', $stats, $args );
	}

	/**
	 * Get top products by referrals
	 *
	 * @param int $limit        Number of top products to retrieve.
	 * @param int $affiliate_id Optional affiliate ID to filter by.
	 * @param int $days         Number of days to look back.
	 *
	 * @return array Array of top products with stats.
	 */
	public function get_top_products( int $limit = 10, int $affiliate_id = 0, int $days = 30 ): array {
		global $wpdb;

		$table      = $wpdb->prefix . self::$table_name;
		$start_time = time() - ( $days * DAY_IN_SECONDS );

		// This is a simplified version - in reality, you'd need to parse the JSON products.
		$query = "SELECT products, COUNT(*) as referral_count, SUM(commission) as total_commission, SUM(order_total) as total_sales FROM {$table} WHERE time > %d";

		$params = array( $start_time );

		if ( 0 !== $affiliate_id ) {
			$query   .= ' AND affiliate = %d';
			$params[] = $affiliate_id;
		}

		$query   .= ' GROUP BY products ORDER BY referral_count DESC LIMIT %d';
		$params[] = $limit;

		$results = $wpdb->get_results( $wpdb->prepare( $query, ...$params ) );

		$products = array();
		foreach ( $results as $result ) {
			$product_data = maybe_unserialize( $result->products );

			if ( ! is_array( $product_data ) || empty( $product_data ) ) {
				continue;
			}

			foreach ( $product_data as $key => $product_details ) {
				$product_id       = is_numeric( $key ) ? (int) $key : 0;
				$product_name     = is_string( $product_details ) ? $product_details : '';
				$product_quantity = $product_details['quantity'] ?? 1;
				$product_price    = $product_details['price'] ?? 0;

				if ( ! $product_id ) {
					continue;
				}

				// Aggregate stats per product.
				$products[ $product_id ] = array(
					'product_id'       => $product_id,
					'product_name'     => $product_name,
					'product_quantity' => $product_quantity,
					'product_price'    => $product_price,
					'product_url'      => get_permalink( $product_id ),
					'referral_count'   => 0,
					'total_commission' => 0,
					'total_sales'      => 0,
				);
			}

			$products[ $product_id ]['referral_count']   += (int) $result->referral_count;
			$products[ $product_id ]['total_commission'] += (float) $result->total_commission;
			$products[ $product_id ]['total_sales']      += (float) $result->total_sales;
		}

		// Sort by referral count and limit.
		uasort( $products, fn( $a, $b ) => $b['referral_count'] - $a['referral_count'] );

		return $products;
	}

	/**
	 * Update referral statuses in bulk
	 *
	 * @param array  $referral_ids Array of referral IDs to update.
	 * @param string $status       New status to set.
	 *
	 * @return int|WP_Error Number of updated rows or WP_Error on failure
	 */
	public function bulk_update_status( array $referral_ids, string $status ) {
		if ( empty( $referral_ids ) ) {
			return new WP_Error( 'empty_ids', __( 'No referral IDs provided', 'wc-affiliate' ) );
		}

		global $wpdb;
		$table = $wpdb->prefix . self::$table_name;

		$ids          = array_map( 'intval', $referral_ids );
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		$query  = "UPDATE {$table} SET payment_status = %s WHERE id IN ({$placeholders})";
		$params = array( $status, ...$ids );

		$updated = $wpdb->query( $wpdb->prepare( $query, ...$params ) );

		if ( $updated ) {
			// Fire hooks for each updated referral.
			foreach ( $ids as $referral_id ) {
				do_action( 'wc_affiliate_referral_status_changed', $referral_id, '', $status );
			}
		}

		return $updated;
	}

	/**
	 * Sanitize referral data
	 *
	 * @param array $data Referral data to sanitize.
	 *
	 * @return array Sanitized data
	 */
	public function sanitize_data( array $data ): array {
		$sanitized = array();

		if ( isset( $data['affiliate'] ) ) {
			$sanitized['affiliate'] = (int) $data['affiliate'];
		} elseif ( isset( $data['affiliate_id'] ) ) {
			$sanitized['affiliate'] = (int) $data['affiliate_id'];
		}

		if ( isset( $data['type'] ) ) {
			$sanitized['type'] = sanitize_text_field( $data['type'] );
		}

		if ( isset( $data['visit'] ) ) {
			$sanitized['visit'] = (int) $data['visit'];
		}

		if ( isset( $data['order_id'] ) ) {
			$sanitized['order_id'] = (int) $data['order_id'];
		}

		if ( isset( $data['commission'] ) ) {
			$sanitized['commission'] = (float) $data['commission'];
		}

		if ( isset( $data['order_total'] ) ) {
			$sanitized['order_total'] = (float) $data['order_total'];
		}

		if ( isset( $data['payment_status'] ) ) {
			$sanitized['payment_status'] = sanitize_text_field( $data['payment_status'] );
		}

		if ( isset( $data['transaction_id'] ) ) {
			$sanitized['transaction_id'] = (int) $data['transaction_id'];
		}

		if ( isset( $data['time'] ) ) {
			$sanitized['time'] = (int) $data['time'];
		}

		if ( isset( $data['products'] ) ) {
			$sanitized['products'] = $data['products'];
		}

		return apply_filters( 'wc_affiliate_referral_sanitize_data', $sanitized, $data );
	}

	/**
	 * Validate referral data
	 *
	 * @param array $data Referral data to validate.
	 *
	 * @return true|array True if valid, array of error messages if invalid
	 */
	public function validate_data( array $data ) {
		$errors = array();

		if ( isset( $data['affiliate'] ) ) {
			$this->validate_affiliate_id( $data['affiliate'], $errors );
		}

		if ( isset( $data['type'] ) ) {
			$valid_types = apply_filters(
				'wc_affiliate_referral_types',
				array( self::TYPE_SALE, self::TYPE_RECURRING ),
				$data['type']
			);

			$this->validate_status( $data['type'], $valid_types, $errors );
		}

		if ( isset( $data['commission'] ) ) {
			$this->validate_amount( $data['commission'], $errors, true );
		}

		if ( isset( $data['order_id'] ) && apply_filters( 'wc_affiliate_referral_validate_order_id', true, $data['order_id'], $data ) ) {
			$this->validate_order_id( $data['order_id'], $errors );
		}

		if ( isset( $data['order_total'] ) && apply_filters( 'wc_affiliate_referral_validate_order_total', true, $data['order_id'], $data ) ) {
			$this->validate_amount( $data['order_total'], $errors, true );
		}

		if ( isset( $data['payment_status'] ) && apply_filters( 'wc_affiliate_referral_validate_payment_status', true, $data['payment_status'], $data ) ) {
			$valid_statuses = array(
				self::STATUS_PENDING,
				self::STATUS_PAID,
				self::STATUS_APPROVED,
				self::STATUS_REJECTED,
				self::STATUS_CANCELLED,
			);

			$valid_statuses = apply_filters( 'wc_affiliate_referral_valid_payment_statuses', $valid_statuses );

			$this->validate_status( $data['payment_status'], $valid_statuses, $errors );
		}

		return empty( $errors ) ? true : $errors;
	}

	/**
	 * Get available referral statuses
	 *
	 * @return array Array of status keys and labels.
	 */
	public function get_available_statuses(): array {
		return apply_filters(
			'wc_affiliate_referral_available_statuses',
			array(
				self::STATUS_PAID      => __( 'Paid', 'wc-affiliate' ),
				self::STATUS_APPROVED  => __( 'Approved', 'wc-affiliate' ),
				self::STATUS_PENDING   => __( 'Pending', 'wc-affiliate' ),
				self::STATUS_REJECTED  => __( 'Rejected', 'wc-affiliate' ),
				self::STATUS_CANCELLED => __( 'Cancelled', 'wc-affiliate' ),
			)
		);
	}

	/**
	 * Get format array for wpdb operations
	 *
	 * @param array $data Data array to determine formats.
	 *
	 * @return array Array of formats corresponding to data keys.
	 */
	protected function get_format_array( array $data ): array {
		$formats = array();

		foreach ( $data as $key => $value ) {
			switch ( $key ) {
				case 'affiliate':
				case 'visit':
				case 'order_id':
				case 'transaction_id':
				case 'time':
					$formats[] = '%d';
					break;
				case 'commission':
				case 'order_total':
					$formats[] = '%f';
					break;
				default:
					$formats[] = '%s';
					break;
			}
		}

		return $formats;
	}

	/**
	 * Get chart data for referrals, commissions, and sales
	 *
	 * @param array  $date_range   Date range with start_date and end_date.
	 * @param string $period       Period type (daily, weekly, monthly).
	 * @param int    $affiliate_id Optional affiliate ID filter.
	 *
	 * @return array Chart data with period, referrals, commissions, and sales
	 */
	public function get_chart_data( array $date_range, string $period, int $affiliate_id = 0 ): array {
		global $wpdb;

		$start_time = strtotime( $date_range['start_date'] . ' 00:00:00' );
		$end_time   = strtotime( $date_range['end_date'] . ' 23:59:59' );
		$table      = $wpdb->prefix . self::$table_name;

		$query        = "SELECT * FROM {$table} WHERE time >= %d AND time <= %d";
		$query_params = array( $start_time, $end_time );

		if ( $affiliate_id > 0 ) {
			$query         .= ' AND affiliate = %d';
			$query_params[] = $affiliate_id;
		}

		$query .= ' ORDER BY time ASC';

		$referrals = $wpdb->get_results( $wpdb->prepare( $query, ...$query_params ), ARRAY_A );
		if ( empty( $referrals ) ) {
			return array();
		}

		// Group by period and calculate totals.
		$chart_data = array();
		foreach ( $referrals as $referral ) {
			$period_key = wp_date( $this->get_date_format_for_period( $period ), $referral['time'] );

			if ( ! isset( $chart_data[ $period_key ] ) ) {
				$chart_data[ $period_key ] = array(
					'period'      => $period_key,
					'referrals'   => 0,
					'commissions' => 0,
					'sales'       => 0,
				);
			}

			++$chart_data[ $period_key ]['referrals'];
			$chart_data[ $period_key ]['commissions'] += $referral['commission'];
			$chart_data[ $period_key ]['sales']       += $referral['order_total'];
		}

		// Format output.
		$result = array();
		foreach ( $chart_data as $data ) {
			$result[] = array(
				'period'      => $data['period'],
				'referrals'   => $data['referrals'],
				'commissions' => round( $data['commissions'], 2 ),
				'sales'       => round( $data['sales'], 2 ),
			);
		}

		return apply_filters( 'wc_affiliate_referral_chart_data', $result, $date_range, $period, $affiliate_id );
	}

	/**
	 * Get commission status chart data
	 *
	 * @param array  $date_range   Date range with start_date and end_date.
	 * @param string $period       Period type (daily, weekly, monthly).
	 * @param int    $affiliate_id Optional affiliate ID filter.
	 *
	 * @return array Chart data with period and commission status breakdowns
	 */
	public function get_commission_status_chart_data( array $date_range, string $period, int $affiliate_id = 0 ): array {
		global $wpdb;

		$start_time = strtotime( $date_range['start_date'] . ' 00:00:00' );
		$end_time   = strtotime( $date_range['end_date'] . ' 23:59:59' );
		$table      = $wpdb->prefix . self::$table_name;

		$query        = "SELECT * FROM {$table} WHERE time >= %d AND time <= %d";
		$query_params = array( $start_time, $end_time );

		if ( $affiliate_id > 0 ) {
			$query         .= ' AND affiliate = %d';
			$query_params[] = $affiliate_id;
		}

		$query .= ' ORDER BY time ASC';

		$referrals = $wpdb->get_results( $wpdb->prepare( $query, ...$query_params ), ARRAY_A );

		if ( empty( $referrals ) ) {
			return array();
		}

		// Group by period and payment status.
		$chart_data = array();
		foreach ( $referrals as $referral ) {
			$period_key = wp_date( $this->get_date_format_for_period( $period ), $referral['time'] );

			if ( ! isset( $chart_data[ $period_key ] ) ) {
				$chart_data[ $period_key ] = array(
					'period'               => $period_key,
					'pending_commissions'  => 0,
					'paid_commissions'     => 0,
					'approved_commissions' => 0,
				);
			}

			$status = $referral['payment_status'];
			if ( isset( $chart_data[ $period_key ][ $status . '_commissions' ] ) ) {
				$chart_data[ $period_key ][ $status . '_commissions' ] += $referral['commission'];
			}
		}

		// Format output.
		$result = array();
		foreach ( $chart_data as $data ) {
			$result[] = array(
				'period'               => $data['period'],
				'pending_commissions'  => round( $data['pending_commissions'], 2 ),
				'paid_commissions'     => round( $data['paid_commissions'], 2 ),
				'approved_commissions' => round( $data['approved_commissions'], 2 ),
			);
		}

		return apply_filters( 'wc_affiliate_referral_commission_status_chart_data', $result, $date_range, $period, $affiliate_id );
	}

	/**
	 * Get payable referrals (paid but not in transaction)
	 *
	 * @param int   $affiliate_id Optional affiliate ID filter.
	 * @param float $min_amount   Minimum commission amount filter.
	 * @param int   $limit        Number of records to return.
	 * @param int   $offset       Number of records to skip.
	 *
	 * @return array Array of payable referral data with affiliate info
	 */
	public function get_payable_referrals( int $affiliate_id = 0, float $min_amount = 0, int $limit = 0, int $offset = 0 ): array {
		global $wpdb;

		$table = $wpdb->prefix . self::$table_name;

		$query        = "SELECT * FROM {$table} WHERE payment_status = 'paid' AND (transaction_id = 0 OR transaction_id IS NULL)";
		$query_params = array();

		if ( $affiliate_id > 0 ) {
			$query         .= ' AND affiliate = %d';
			$query_params[] = $affiliate_id;
		}

		if ( $min_amount > 0 ) {
			$query         .= ' AND commission >= %f';
			$query_params[] = $min_amount;
		}

		$query .= ' ORDER BY affiliate ASC, time ASC';

		if ( $limit > 0 ) {
			$query         .= ' LIMIT %d';
			$query_params[] = $limit;

			if ( $offset > 0 ) {
				$query         .= ' OFFSET %d';
				$query_params[] = $offset;
			}
		}

		$referrals = $wpdb->get_results( $wpdb->prepare( $query, ...$query_params ), ARRAY_A );

		// Add affiliate information.
		foreach ( $referrals as &$referral ) {
			$user = get_userdata( $referral['affiliate'] );
			if ( $user ) {
				$referral['affiliate_name']  = $user->display_name;
				$referral['affiliate_email'] = $user->user_email;
			}
		}

		return apply_filters( 'wc_affiliate_referral_payable_referrals', $referrals, $affiliate_id, $min_amount, $limit, $offset );
	}

	/**
	 * Get approved referrals grouped by affiliates
	 *
	 * Retrieves approved and paid referrals grouped by each affiliate, with summary statistics.
	 *
	 * @param int $page     Current page number (for pagination).
	 * @param int $per_page Number of affiliates per page.
	 *
	 * @return array Array of affiliate data with referral statistics and pagination info.
	 */
	public function get_approved_referrals_by_affiliate( int $page = 1, int $per_page = 50 ): array {
		global $wpdb;

		$table = $wpdb->prefix . self::$table_name;

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

		// Build the query to get affiliate data with unpaid amount and total earnings.
		$query = "SELECT
				r.affiliate,
				u.display_name as affiliate_name,
				u.user_email as affiliate_email,
				COALESCE(um.meta_value, '') as payment_method,
				COUNT(CASE WHEN r.payment_status = 'approved' THEN 1 END) as unpaid_referral_count,
				COUNT(r.id) as total_referral_count,
				SUM(IF(r.payment_status = 'approved', r.commission, 0)) as unpaid_commission,
				SUM(r.commission) as total_commissions,
				SUM(IF(r.payment_status = 'approved', r.order_total, 0)) as unpaid_sales,
				SUM(r.order_total) as total_sales,
				AVG(r.commission) as avg_commission,
				MIN(r.time) as first_referral_date,
				MAX(r.time) as last_referral_date
			FROM {$table} r
			INNER JOIN {$wpdb->users} u ON r.affiliate = u.ID
			LEFT JOIN {$wpdb->usermeta} um ON u.ID = um.user_id AND um.meta_key = '_wc_affiliate_payout_method'
			WHERE r.payment_status IN ('approved', 'paid')";

		$query_params = array();

		$query .= ' GROUP BY r.affiliate, u.display_name, u.user_email, um.meta_value ORDER BY commission DESC';

		// Get total count for pagination (without LIMIT).
		$count_query = $query;
		$total_count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM ({$count_query}) as subquery", ...$query_params ) ); // phpcs:ignore WordPress.DB

		// Add pagination to main query.
		$query         .= ' LIMIT %d OFFSET %d';
		$query_params[] = $per_page;
		$query_params[] = $offset;

		$results = $wpdb->get_results( $wpdb->prepare( $query, ...$query_params ), ARRAY_A );

		if ( null === $results ) {
			return array();
		}

		// Format the affiliate data.
		$affiliates_data = array();
		foreach ( $results as $result ) {
			$payment_method       = $result['payment_method'];
			$payment_method_label = $this->get_payment_method_label( $payment_method );

			$affiliate_data = array(
				'affiliate_id'                 => (int) $result['affiliate'],
				'affiliate_name'               => sanitize_text_field( $result['affiliate_name'] ),
				'affiliate_email'              => sanitize_email( $result['affiliate_email'] ),
				'payment_method'               => $payment_method,
				'payment_method_label'         => $payment_method_label,
				'referral_count'               => (int) $result['total_referral_count'],
				'unpaid_referral_count'        => (int) $result['unpaid_referral_count'],
				'total_commissions'            => (float) $result['total_commissions'],
				'unpaid_commissions'           => (float) $result['unpaid_commission'],
				'total_sales'                  => (float) $result['total_sales'],
				'unpaid_sales'                 => (float) $result['unpaid_sales'],
				'avg_commission'               => (float) $result['avg_commission'],
				'first_referral_date'          => wp_date( 'Y-m-d H:i:s', (int) $result['first_referral_date'] ),
				'last_referral_date'           => wp_date( 'Y-m-d H:i:s', (int) $result['last_referral_date'] ),
				'total_commissions_formatted'  => wca_format_price( (float) $result['total_commissions'] ),
				'unpaid_commissions_formatted' => wca_format_price( (float) $result['unpaid_commission'] ),
				'total_sales_formatted'        => wca_format_price( (float) $result['total_sales'] ),
				'unpaid_sales_formatted'       => wca_format_price( (float) $result['unpaid_sales'] ),
				'avg_commission_formatted'     => wca_format_price( (float) $result['avg_commission'] ),
				'payout_requested'             => get_user_meta( $result['affiliate'], '_wc-affiliate-applied_payout', true ) ?? false,
			);

			if ( 'manual' === $payment_method ) {
				$affiliate_data['details'] = get_user_meta( $result['affiliate'], '_wc_affiliate_mannual_payment', true ) ?? '';
			}

			$affiliates_data[] = $affiliate_data;
		}

		return apply_filters(
			'wc_affiliate_referral_approved_by_affiliate_data',
			array(
				'affiliates' => $affiliates_data,
				'pagination' => array(
					'total_count' => (int) $total_count,
					'page'        => $page,
					'per_page'    => $per_page,
					'total_pages' => ceil( $total_count / $per_page ),
				),
			),
			$page,
			$per_page
		);
	}

	/**
	 * Get all approved referrals for a specific affiliate
	 *
	 * @param int $affiliate_id Affiliate ID.
	 *
	 * @return array Array of referral data for the affiliate.
	 */
	public function get_approved_referrals_for_affiliate( int $affiliate_id ): array {
		global $wpdb;

		$table = $wpdb->prefix . self::$table_name;

		// Get all approved referrals for this affiliate.
		$query = $wpdb->prepare(
			"SELECT * FROM {$table} WHERE affiliate = %d AND payment_status = 'approved' AND transaction_id = 0 ORDER BY time ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$affiliate_id
		);

		$results = $wpdb->get_results( $query, ARRAY_A );

		if ( null === $results ) {
			return array();
		}

		// Format the referral data.
		$referrals_data = array();
		foreach ( $results as $result ) {
			$referrals_data[] = array(
				'id'             => (int) $result['id'],
				'affiliate'      => (int) $result['affiliate'],
				'order_id'       => (int) $result['order_id'],
				'order_total'    => (float) $result['order_total'],
				'commission'     => (float) $result['commission'],
				'payment_status' => $result['payment_status'],
				'transaction_id' => (int) $result['transaction_id'],
				'time'           => (int) $result['time'],
				'referrer'       => $result['referrer'] ?? '',
				'description'    => $result['description'] ?? '',
			);
		}

		return $referrals_data;
	}

	/**
	 * Get total unpaid commission for a specific affiliate
	 *
	 * @param int $affiliate_id Affiliate ID.
	 *
	 * @return float Total unpaid commission amount.
	 */
	public function get_total_unpaid_commission_for_affiliate( int $affiliate_id ): float {
		global $wpdb;

		$table = $wpdb->prefix . self::$table_name;

		// Get total unpaid commission for this affiliate.
		$query = $wpdb->prepare(
			"SELECT SUM(commission) as total_commission FROM {$table} WHERE affiliate = %d AND payment_status = 'approved' AND transaction_id = 0", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$affiliate_id
		);

		$result = $wpdb->get_var( $query );

		return (float) ( $result ?? 0 );
	}

	/**
	 * Get payable affiliates grouped by affiliate with total commission amounts
	 *
	 * @param string $affiliate  Specific affiliate ID filter (optional).
	 * @param string $from       Start date filter (optional).
	 * @param string $to         End date filter (optional).
	 * @param float  $min_amount Minimum payout amount filter.
	 *
	 * @return array Array of affiliates with their total approved commission amounts.
	 */
	public function get_payable_affiliates_grouped( string $affiliate = '', string $from = '', string $to = '', float $min_amount = 0 ): array {
		global $wpdb;

		$table = $wpdb->prefix . self::$table_name;

		$query        = "SELECT affiliate, SUM(commission) as total_commission FROM {$table} WHERE payment_status = 'approved' AND transaction_id = 0";
		$query_params = array();

		if ( '' !== $affiliate ) {
			$query         .= ' AND affiliate = %d';
			$query_params[] = $affiliate;
		}

		if ( '' !== $from && '' !== $to ) {
			$from_date      = strtotime( $from );
			$to_date        = strtotime( $to ) + DAY_IN_SECONDS - 1; // Include entire day
			$query         .= ' AND time >= %d AND time <= %d';
			$query_params[] = $from_date;
			$query_params[] = $to_date;
		}

		$query .= ' GROUP BY affiliate';

		if ( ! empty( $query_params ) ) {
			$query = $wpdb->prepare( $query, ...$query_params );
		}

		$results = $wpdb->get_results( $query, ARRAY_A );

		if ( null === $results ) {
			return array();
		}

		$payable_affiliates = array();
		foreach ( $results as $result ) {
			$total_commission = (float) $result['total_commission'];
			if ( $total_commission >= $min_amount ) {
				$payable_affiliates[ $result['affiliate'] ] = $total_commission;
			}
		}

		return $payable_affiliates;
	}

	/**
	 * Get payment method label
	 *
	 * @param string $method Payment method.
	 *
	 * @return string Payment method label.
	 */
	protected function get_payment_method_label( string $method ): string {
		$payment_methods = wca_get_payment_methods_options();

		return $payment_methods[ $method ] ?? ucfirst( str_replace( '_', ' ', $method ) );
	}
}
