<?php

namespace WC_Affiliate\Models;

defined( 'ABSPATH' ) || exit;

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

/**
 * Class Transaction
 *
 * Represents a financial transaction and provides methods for retrieving, creating,
 * and analyzing transactions with various filters and operations.
 */
class Transaction extends Model {

	use Validation;

	/**
	 * Transaction statuses
	 */
	const STATUS_PENDING    = 'pending';
	const STATUS_COMPLETED  = 'completed';
	const STATUS_FAILED     = 'failed';
	const STATUS_CANCELLED  = 'cancelled';
	const STATUS_PROCESSING = 'processing';

	/**
	 * Payment methods
	 */
	const METHOD_MANUAL = 'manual';

	/**
	 * Table name
	 *
	 * @var string|null
	 */
	protected static ?string $table_name = 'wca_transactions';

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

		if ( ! empty( $this->data ) ) {
			$this->data['formatted_amount']       = $this->get_formatted_amount();
			$this->data['formatted_request_time'] = $this->get_formatted_request_time();
			$this->data['formatted_process_time'] = $this->get_formatted_process_time();
			$this->data['processing_duration']    = $this->get_processing_duration();
			$this->data['referral_count']         = $this->get_referral_count();

			// 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 payment method label.
			$this->data['payment_method_label'] = $this->get_payment_method_label( $this->get_payment_method() );
		}

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

	/**
	 * Get transaction statistics
	 *
	 * @param array $args Optional filter arguments:
	 *
	 * 'affiliate_id'    => (int) Filter by affiliate ID.
	 * 'start_date'     => (string) Start date filter (YYYY-MM-DD).
	 * 'end_date'       => (string) End date filter (YYYY-MM-DD).
	 * 'status'         => (string) Filter by transaction status.
	 * 'payment_method' => (string) Filter by payment method.
	 *
	 * @return array Array of statistics including total transactions, total amount, average amount, status breakdowns, method breakdowns, and success rate.
	 */
	public function get_statistics( array $args = array() ): array {
		global $wpdb;

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

		$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[] = 'request_at >= %d';
			$query_params[]     = $start_time;
		}

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

		if ( $args['status'] ) {
			$where_conditions[] = 'status = %s';
			$query_params[]     = $args['status'];
		}

		if ( $args['payment_method'] ) {
			$where_conditions[] = 'payment_method = %s';
			$query_params[]     = $args['payment_method'];
		}

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

		$query = "SELECT 
					COUNT(*) as total_transactions,
					SUM(amount) as total_amount,
					AVG(amount) as avg_amount,
					COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_transactions,
					COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_transactions,
					SUM(IF(status = 'completed', amount, 0)) as completed_amount,
					SUM(IF(status = 'pending', amount, 0)) as pending_amount,
					COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_transactions,
					COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_transactions,
					COUNT(CASE WHEN payment_method = 'manual' THEN 1 END) as manual_transactions
				  FROM {$table} {$where_clause}";

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

		// Calculate success rate.
		$stats['success_rate'] = $stats['total_transactions'] > 0 ? ( $stats['completed_transactions'] / $stats['total_transactions'] ) * 100 : 0;

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

	/**
	 * Get transactions with filters
	 *
	 * @param array $args Optional filter arguments:
	 *                    'affiliate_id'   => (int) Filter by affiliate ID.
	 *                    'status'         => (string) Filter by transaction status.
	 *                    'payment_method' => (string) Filter by payment method.
	 *                    'limit'          => (int) Number of records to return. Default 50.
	 *                    'offset'         => (int) Offset for pagination. Default 0.
	 *                    'orderby'        => (string) Column to order by. Default 'request_at'.
	 *                    'order'          => (string) 'ASC' or 'DESC'. Default 'DESC'.
	 *                    'start_date'     => (string) Start date filter (YYYY-MM-DD).
	 *                    'end_date'       => (string) End date filter (YYYY-MM-DD).
	 *
	 * @return array Array of Transaction objects.
	 */
	public function get_transactions( array $args = array() ): array {
		global $wpdb;

		$defaults = array(
			'affiliate_id'   => 0,
			'status'         => '',
			'payment_method' => '',
			'limit'          => 50,
			'offset'         => 0,
			'orderby'        => 'request_at',
			'order'          => 'DESC',
			'start_date'     => '',
			'end_date'       => '',
			'search'         => '',
		);

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

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

		$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['status'] ) {
			$where_conditions[] = 'status = %s';
			$query_params[]     = $args['status'];
		}

		if ( $args['payment_method'] ) {
			$where_conditions[] = 'payment_method = %s';
			$query_params[]     = $args['payment_method'];
		}

		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;
		}

		if ( $args['search'] ) {
			$search_term        = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where_conditions[] = '(t.txn_id LIKE %s OR u.display_name LIKE %s OR t.payment_method LIKE %s)';
			$query_params[]     = $search_term;
			$query_params[]     = $search_term;
			$query_params[]     = $search_term;
		}

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

		$join_clause = '';
		if ( $args['search'] ) {
			$users_table = $wpdb->users;
			$join_clause = "LEFT JOIN {$users_table} u ON t.affiliate = u.ID";
		}

		$query = "SELECT t.* FROM {$table} t {$join_clause} WHERE {$where_clause} ORDER BY t.{$args['orderby']} {$args['order']} LIMIT {$args['offset']}, {$args['limit']}";

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

		$transactions = array();
		foreach ( $results as $result ) {
			$transaction         = new self();
			$transaction->id     = $result->id;
			$transaction->data   = (array) $result;
			$transaction->exists = true;
			$transaction->load();
			$transactions[] = $transaction;
		}

		return apply_filters( 'wc_affiliate_get_transactions', $transactions, $args );
	}

	/**
	 * Create new transaction
	 *
	 * @param array $data           {
	 *                              Transaction data.
	 *
	 * @type int    $affiliate      Required. Affiliate ID.
	 * @type float  $amount         Required. Transaction amount.
	 * @type string $payment_method Required. Payment method.
	 * @type string $txn_id         Optional. External transaction ID.
	 * @type string $status         Optional. Transaction status. Default 'pending'.
	 * @type int    $request_at     Optional. Request timestamp. Default current time.
	 *                              }
	 *
	 * @return Transaction|\WP_Error Transaction object on success, WP_Error on failure
	 */
	public function create( array $data ) {
		global $wpdb;

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

		// Fire action before transaction creation.
		do_action( 'wc_affiliate_transaction_before_create', $data );

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

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

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

		// Set default values.
		$sanitized_data['request_at'] = $sanitized_data['request_at'] ?? time();
		$sanitized_data['status']     = $sanitized_data['status'] ?? static::STATUS_PENDING;

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

		if ( $result ) {
			$transaction_id = $wpdb->insert_id;
			$transaction    = new self( $transaction_id );

			// Fire action after transaction creation.
			do_action( 'wc_affiliate_transaction_after_create', $transaction_id, $transaction, $sanitized_data, $data );

			return $transaction;
		}

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

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

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

	/**
	 * Get affiliate object
	 *
	 * @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 transaction amount
	 *
	 * @return float Transaction amount.
	 */
	public function get_amount(): float {
		return (float) $this->get( 'amount' );
	}

	/**
	 * Get formatted amount
	 *
	 * @return string Formatted amount with currency symbol.
	 */
	public function get_formatted_amount(): string {
		return wca_format_price( $this->get_amount() );
	}

	/**
	 * Get payment method
	 *
	 * @return string Payment method.
	 */
	public function get_payment_method(): string {
		return $this->get( 'payment_method' );
	}

	/**
	 * Get transaction ID (external)
	 *
	 * @return string Transaction ID.
	 */
	public function get_txn_id(): string {
		return $this->get( 'txn_id' );
	}

	/**
	 * Get transaction status
	 *
	 * @return string Transaction status.
	 */
	public function get_status(): string {
		return $this->get( 'status' );
	}

	/**
	 * Check if transaction is completed
	 *
	 * @return bool True if transaction status is 'completed', false otherwise.
	 */
	public function is_completed(): bool {
		return $this->get_status() === self::STATUS_COMPLETED;
	}

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

	/**
	 * Check if transaction failed
	 *
	 * @return bool True if transaction status is 'failed', false otherwise.
	 */
	public function is_failed(): bool {
		return $this->get_status() === self::STATUS_FAILED;
	}

	/**
	 * Get request time
	 *
	 * @return int Request time as Unix timestamp.
	 */
	public function get_request_time(): int {
		return (int) $this->get( 'request_at' );
	}

	/**
	 * Get process time
	 *
	 * @return int Process time as Unix timestamp. Returns 0 if not processed.
	 */
	public function get_process_time(): int {
		return (int) $this->get( 'process_at' );
	}

	/**
	 * Get formatted request time
	 *
	 * @param string $format Optional date format. Defaults to WordPress date and time format.
	 *
	 * @return string Formatted request time.
	 */
	public function get_formatted_request_time( string $format = 'Y-m-d H:i:s' ): string {
		if ( empty( $this->get_request_time() ) ) {
			return '-';
		}

		return wp_date( $format, $this->get_request_time() );
	}

	/**
	 * Get formatted process time
	 *
	 * @param string $format Optional date format. Defaults to WordPress date and time format.
	 *
	 * @return string Formatted process time or empty string if not processed.
	 */
	public function get_formatted_process_time( string $format = 'Y-m-d H:i:s' ): string {
		if ( empty( $this->get_process_time() ) ) {
			return '-';
		}

		return wp_date( $format, $this->get_process_time() );
	}

	/**
	 * Get processing time duration
	 *
	 * @return int Duration in seconds between request and process time. Returns 0 if not processed.
	 */
	public function get_processing_duration(): int {
		$request_time = $this->get_request_time();
		$process_time = $this->get_process_time();

		if ( ! $process_time || ! $request_time ) {
			return 0;
		}

		return $process_time - $request_time;
	}

	/**
	 * Get related referrals
	 *
	 * @return array Array of Referral objects associated with this transaction.
	 */
	public function get_referrals(): array {
		$referral_model = new Referral();

		return $referral_model->get_referrals(
			array(
				'transaction_id' => $this->id,
				'limit'          => -1,
			)
		);
	}

	/**
	 * Get referral count
	 *
	 * @return int Count of referrals associated with this transaction.
	 */
	public function get_referral_count(): int {
		$referral_model = new Referral();

		return $referral_model->get_count(
			array(
				'transaction_id' => $this->id,
			)
		);
	}

	/**
	 * Update transaction status
	 *
	 * @param string $status New status.
	 * @param string $txn_id Optional transaction ID.
	 *
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public function update_status( string $status, string $txn_id = '' ) {
		$valid_statuses = array(
			self::STATUS_PENDING,
			self::STATUS_COMPLETED,
			self::STATUS_FAILED,
			self::STATUS_CANCELLED,
			self::STATUS_PROCESSING,
		);

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

		$old_status = $this->get_status();

		$can_update = apply_filters( 'wc_affiliate_transaction_can_update_status', true, $this->id, $status, $old_status );
		if ( ! $can_update ) {
			return new WP_Error( 'cannot_update', __( 'Transaction status update prevented by filter', 'wc-affiliate' ) );
		}

		// Fire action before status update.
		do_action( 'wc_affiliate_transaction_before_status_update', $this->id, $old_status, $status, $txn_id );

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

		$update_data = array( 'status' => $status );

		// Set process time when completing.
		if ( self::STATUS_COMPLETED === $status && self::STATUS_COMPLETED !== $old_status ) {
			$update_data['process_at'] = time();
		}

		// Update transaction ID if provided.
		if ( ! empty( $txn_id ) ) {
			$update_data['txn_id'] = $txn_id;
		}

		$result = $wpdb->update(
			$table,
			$update_data,
			array( 'id' => $this->id ),
			array_fill( 0, count( $update_data ), '%s' ),
			array( '%d' )
		);

		if ( false !== $result ) {
			// Update local data.
			foreach ( $update_data as $key => $value ) {
				$this->data[ $key ] = $value;
			}

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

				if ( self::STATUS_COMPLETED === $status ) {
					do_action( 'wc_affiliate_payout_processed', $this->get_affiliate_id(), $this->get_amount(), $this->to_array() );
				}
			}

			return true;
		}

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

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

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

		// Fire action before transaction update.
		do_action( 'wc_affiliate_transaction_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_transaction_update_data', $sanitized_data, $data, $this->id );

		$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 ) {
			$this->data = array_merge( $this->data, $sanitized_data );

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

			return true;
		}

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

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

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

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

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

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

		if ( $result ) {
			// Reset referral transaction IDs.
			$wpdb->update(
				$wpdb->prefix . 'wca_referrals',
				array( 'transaction_id' => 0 ),
				array( 'transaction_id' => $this->id ),
				array( '%d' ),
				array( '%d' )
			);

			// Fire action after transaction deletion.
			do_action( 'wc_affiliate_transaction_after_delete', $this->id, $this->data );

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

			return true;
		}

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

	/**
	 * Get payment method label
	 *
	 * @param string $method Payment method key.
	 *
	 * @return string Payment method label.
	 */
	public function get_payment_method_label( string $method ): string {
		$labels = array(
			self::METHOD_MANUAL => __( 'Manual', 'wc-affiliate' ),
		);

		return $labels[ $method ] ?? $method;
	}

	/**
	 * Get transaction count
	 *
	 * @param array $args Optional filter arguments.
	 *
	 * @return int Count of transactions matching the criteria.
	 */
	public function get_count( array $args = array() ): int {
		global $wpdb;

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

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

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

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

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

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

		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;
		}

		if ( $args['search'] ) {
			$search_term    = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$query         .= ' AND (txn_id LIKE %s OR affiliate IN (SELECT ID FROM ' . $wpdb->users . ' WHERE display_name LIKE %s) OR payment_method LIKE %s)';
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
		}

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

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

		// Use ValidationTrait methods.
		if ( isset( $data['affiliate'] ) ) {
			$this->validate_affiliate_id( $data['affiliate'], $errors );
		}

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

		if ( isset( $data['payment_method'] ) && empty( $data['payment_method'] ) ) {
			$errors[] = __( 'Payment method is required', 'wc-affiliate' );
		}

		if ( isset( $data['status'] ) ) {
			$valid_statuses = array(
				self::STATUS_PENDING,
				self::STATUS_COMPLETED,
				self::STATUS_FAILED,
				self::STATUS_CANCELLED,
				self::STATUS_PROCESSING,
			);
			$this->validate_status( $data['status'], $valid_statuses, $errors );
		}

		return apply_filters( 'wc_affiliate_transaction_validate', empty( $errors ) ? true : $errors, $data );
	}

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

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

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

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

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

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

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

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

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

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

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

		foreach ( $data as $key => $value ) {
			switch ( $key ) {
				case 'affiliate':
				case 'request_at':
				case 'process_at':
					$formats[] = '%d';
					break;
				case 'amount':
					$formats[] = '%f';
					break;
				default:
					$formats[] = '%s';
					break;
			}
		}

		return $formats;
	}

	/**
	 * Get available transaction statuses
	 *
	 * @return array Array of status keys and labels.
	 */
	public function get_available_statuses(): array {
		return apply_filters(
			'wc_affiliate_transaction_available_statuses',
			array(
				self::STATUS_PENDING    => __( 'Pending', 'wc-affiliate' ),
				self::STATUS_PROCESSING => __( 'Processing', 'wc-affiliate' ),
				self::STATUS_COMPLETED  => __( 'Completed', 'wc-affiliate' ),
				self::STATUS_FAILED     => __( 'Failed', 'wc-affiliate' ),
				self::STATUS_CANCELLED  => __( 'Cancelled', 'wc-affiliate' ),
			)
		);
	}

	/**
	 * Get transaction 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'       => '',
			'payment_method' => '',
		);

		$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'];
		}

		// Payment method filter.
		if ( $args['payment_method'] ) {
			$where_conditions[] = 'method = %s';
			$query_params[]     = $args['payment_method'];
		}

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

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

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

		// Get all available transaction 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 status IN ( ' . implode( ', ', $status_placeholders ) . ' )';
		$query_params        = array_merge( $query_params, array_keys( $available_statuses ) );

		$query = "SELECT status, COUNT(*) as count, SUM(amount) as total_amount FROM {$table} WHERE {$where_clause} GROUP BY 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_amount' => 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_amount'] = (float) $result['total_amount'];
			}
		}

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

		// Calculate totals.
		$total_count  = 0;
		$total_amount = 0;

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

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

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

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

		// Get total unpaid transactions (pending status).
		$unpaid = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT SUM(amount) FROM %s WHERE affiliate = %d AND status = %s',
				$table,
				$affiliate_id,
				self::STATUS_PENDING
			)
		);

		$unpaid = (float) $unpaid;

		return apply_filters( 'wc_affiliate_transaction_unpaid_amount', $unpaid, $affiliate_id );
	}
}
