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

/**
 * Visit Model Class
 *
 * Represents a visitor's interaction with affiliate links in the system.
 * Handles visit tracking, conversion monitoring, and analytics data.
 *
 * @since   1.0.0
 * @package WC_Affiliate\Models
 */
class Visit extends Model {

	use Date;
	use Validation;

	/**
	 * Table name
	 *
	 * @var string|null Database table name (without prefix)
	 */
	protected static ?string $table_name = 'wca_visits';

	/**
	 * Load visit 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['is_converted']   = $this->is_converted();

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

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

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

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

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

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

	/**
	 * Get referral ID if this visit resulted in a referral
	 *
	 * @return int Referral ID or 0 if none
	 */
	public function get_referral_id(): int {
		return (int) $this->get( 'referral' );
	}

	/**
	 * Get referral object
	 *
	 * @return Referral|null Referral object or null if none
	 */
	public function get_referral(): ?Referral {
		$referral_id = $this->get_referral_id();

		return $referral_id ? new Referral( $referral_id ) : null;
	}

	/**
	 * Check if visit resulted in a conversion
	 *
	 * @return bool True if converted, false otherwise
	 */
	public function is_converted(): bool {
		return $this->get_referral_id() > 0;
	}

	/**
	 * Get visit URL
	 *
	 * @return string page URL or empty string if none
	 */
	public function get_page_url(): string {
		return (string) $this->get( 'page_url' );
	}

	/**
	 * Get referrer URL
	 *
	 * @return string Referrer URL or empty string if none
	 */
	public function get_referrer_url(): string {
		return (string) $this->get( 'referrer_url' );
	}

	/**
	 * Get campaign
	 *
	 * @return string Campaign name or empty string if none
	 */
	public function get_campaign(): string {
		return (string) $this->get( 'campaign' );
	}

	/**
	 * Get visitor IP
	 *
	 * @return string IP address or empty string if none
	 */
	public function get_ip(): string {
		return $this->get( 'ip' );
	}

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

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

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

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

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

		$sanitized_data = array_map( '\sanitize_text_field', $data );
		$validation     = $this->validate_data( $sanitized_data );

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

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

		$table  = $this->get_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 visit update.
			do_action( 'wc_affiliate_visit_after_update', $data, $this->id, $sanitized_data );

			return true;
		}

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

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

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

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

		$table  = $this->get_table_name();
		$result = $wpdb->delete(
			$table,
			array( 'id' => $this->id ),
			array( '%d' )
		);

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

			// Fire action after visit deletion.
			do_action( 'wc_affiliate_visit_after_delete', $this->id );

			return true;
		}

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

	/**
	 * Create new visit
	 *
	 * @param array $data Visit data.
	 *
	 * @return Visit|WP_Error Visit object on success, WP_Error on failure
	 */
	public function create( array $data ) {
		global $wpdb;

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

		// Fire action before visit creation.
		do_action( 'wc_affiliate_visit_before_create', $data );

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

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

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

		// Set default values.
		$sanitized_data['time'] = $sanitized_data['time'] ?? time();

		$table  = $this->get_table_name();
		$result = $wpdb->insert(
			$table,
			$sanitized_data,
			$visit->get_format_array( $sanitized_data )
		);

		if ( $result ) {
			$visit_id = $wpdb->insert_id;
			$visit    = new self( $visit_id );

			// Fire action after visit creation.
			do_action( 'wc_affiliate_visit_after_create', $visit, $visit_id, $sanitized_data, $data );

			return $visit;
		}

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

	/**
	 * Track a new visit
	 *
	 * @param int    $affiliate_id Affiliate ID.
	 * @param string $page_url     page URL.
	 * @param string $referrer_url Referrer URL.
	 * @param string $campaign     Campaign name.
	 * @param string $ip           Visitor IP (optional, will auto-detect if not provided).
	 *
	 * @return Visit|WP_Error Visit object on success, WP_Error on failure
	 */
	public function track( int $affiliate_id, string $page_url, string $referrer_url = '', string $campaign = '', string $ip = '' ) {
		// Get IP if not provided.
		if ( empty( $ip ) ) {
			$ip = wca_get_client_ip();
		}

		// Allow filtering visit data before processing.
		$visit_data = apply_filters(
			'wc_affiliate_visit_data_before_track',
			array(
				'affiliate_id' => $affiliate_id,
				'page_url'     => $page_url,
				'referrer_url' => $referrer_url,
				'campaign'     => $campaign,
				'ip'           => $ip,
			)
		);

		$affiliate_id = $visit_data['affiliate_id'];
		$page_url     = $visit_data['page_url'];
		$referrer_url = $visit_data['referrer_url'];
		$campaign     = $visit_data['campaign'];
		$ip           = $visit_data['ip'];

		// Check if visit already exists (prevent duplicates).
		$existing = $this->get_recent_visit( $affiliate_id, $ip, $page_url );
		if ( $existing ) {
			return $existing;
		}

		$visit_data = array(
			'affiliate'    => $affiliate_id,
			'page_url'     => $page_url,
			'referrer_url' => $referrer_url,
			'campaign'     => $campaign,
			'ip'           => $ip,
			'time'         => time(),
		);

		return $this->create( $visit_data );
	}

	/**
	 * Get recent visit for affiliate
	 *
	 * @param int    $affiliate_id Affiliate ID.
	 * @param string $ip           IP address.
	 * @param string $page_url     page URL.
	 * @param int    $time_window  Time window in seconds (default: 300).
	 *
	 * @return Visit|null Visit object or null if not found.
	 */
	public function get_recent_visit( int $affiliate_id, string $ip, string $page_url, int $time_window = 300 ): ?Visit {
		global $wpdb;

		$start_time = time() - $time_window;
		$table      = $this->get_table_name();

		$query = $wpdb->prepare(
			"SELECT * FROM {$table} WHERE affiliate = %d AND ip = %s AND page_url = %s AND time > %d ORDER BY time DESC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$affiliate_id,
			$ip,
			$page_url,
			$start_time
		);

		$visit_data = $wpdb->get_row( $query, ARRAY_A );

		return $visit_data ? new self( $visit_data['id'] ) : null;
	}

	/**
	 * Get visit ID by time.
	 *
	 * @param int $time Unix timestamp.
	 *
	 * @return int|null Visit ID or null if not found.
	 */
	public function get_visit_id_by_time( int $time ): ?int {
		global $wpdb;

		$table = $this->get_table_name();
		$visit = $wpdb->get_row( $wpdb->prepare( "SELECT id FROM {$table} WHERE time = %d", $time ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return $visit->id;
	}

	/**
	 * Get visits with filters
	 *
	 * @param array $args           {
	 *                              Optional. Arguments to filter visits.
	 *
	 * @type int    $affiliate_id   Affiliate ID to filter by. Default 0 (all affiliates).
	 * @type int    $limit          Number of visits to retrieve. Default 50.
	 * @type int    $offset         Offset for pagination. Default 0.
	 * @type string $orderby        Column to order by. Default 'time'.
	 * @type string $order          Order direction ('ASC' or 'DESC'). Default 'DESC'.
	 * @type string $start_date     Start date (YYYY-MM-DD). Default empty (no start date).
	 * @type string $end_date       End date (YYYY-MM-DD). Default empty (no end date).
	 * @type bool   $converted_only Whether to retrieve only converted visits. Default false.
	 * @type string $campaign       Campaign name to filter by. Default empty (all campaigns).
	 *                              }
	 *
	 * @return Visit[] Array of visits matching criteria
	 */
	public function get_visits( array $args = array() ): array {
		global $wpdb;

		$defaults = array(
			'affiliate_id'   => 0,
			'limit'          => 50,
			'offset'         => 0,
			'orderby'        => 'time',
			'order'          => 'DESC',
			'start_date'     => '',
			'end_date'       => '',
			'converted_only' => false,
			'campaign'       => '',
			'search'         => '',
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = $this->get_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'];
		}

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

		// Converted only filter.
		if ( $args['converted_only'] ) {
			$query .= ' AND referral > 0';
		}

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

		// Search filter.
		if ( ! empty( $args['search'] ) ) {
			$query         .= ' AND (page_url LIKE %s OR referrer_url LIKE %s OR campaign LIKE %s OR ip LIKE %s)';
			$search_term    = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
		}

		// 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 )
		);

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

			$visit->load();

			$visits[] = $visit;
		}

		return apply_filters( 'wc_affiliate_get_visits', $visits, $args );
	}

	/**
	 * Get visit count
	 *
	 * @param array $args           {
	 *                              Optional. Arguments to filter count.
	 *
	 * @type int    $affiliate_id   Affiliate ID to filter by. Default 0 (all affiliates).
	 * @type string $start_date     Start date (YYYY-MM-DD). Default empty (no start date).
	 * @type string $end_date       End date (YYYY-MM-DD). Default empty (no end date).
	 * @type bool   $converted_only Whether to count only converted visits. Default false.
	 * @type string $campaign       Campaign name to filter by. Default empty (all campaigns).
	 *                              }
	 *
	 * @return int Count of visits matching criteria
	 */
	public function get_count( array $args = array() ): int {
		global $wpdb;

		$defaults = array(
			'affiliate_id'   => 0,
			'start_date'     => '',
			'end_date'       => '',
			'converted_only' => false,
			'campaign'       => '',
			'search'         => '',
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = $this->get_table_name();

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

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

		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['converted_only'] ) {
			$query .= ' AND referral > 0';
		}

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

		if ( ! empty( $args['search'] ) ) {
			$query         .= ' AND (page_url LIKE %s OR referrer_url LIKE %s OR campaign LIKE %s OR ip LIKE %s)';
			$search_term    = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
			$query_params[] = $search_term;
		}

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

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

	/**
	 * Get visit statistics
	 *
	 * @param array $args         {
	 *                            Optional. Arguments to filter statistics.
	 *
	 * @type int    $affiliate_id Affiliate ID to filter by. Default 0 (all affiliates).
	 * @type string $start_date   Start date (YYYY-MM-DD). Default empty (no start date).
	 * @type string $end_date     End date (YYYY-MM-DD). Default empty (no end date).
	 *                            }
	 *
	 * @return array Statistics including total visits, converted visits, conversion rate, unique affiliates, unique visitors, and campaigns.
	 */
	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 = $this->get_table_name();

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

		if ( $args['affiliate_id'] && 0 !== $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_visits, COUNT(CASE WHEN referral > 0 THEN 1 END) as converted_visits, COUNT(DISTINCT affiliate) as unique_affiliates, COUNT(DISTINCT ip) as unique_visitors, COUNT(DISTINCT campaign) as campaigns FROM {$table} {$where_clause}";

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

		// Calculate conversion rate.
		$stats['conversion_rate'] = $stats['total_visits'] > 0 ? ( $stats['converted_visits'] / $stats['total_visits'] ) * 100 : 0;

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

	/**
	 * Get top pages
	 *
	 * @param int $limit        Number of top pages to retrieve. Default is 10.
	 * @param int $affiliate_id Optional affiliate ID to filter by. Default is 0 (all affiliates).
	 * @param int $days         Number of days to look back. Default is 30 days.
	 *
	 * @return array List of top pages with visit counts and conversion rates.
	 */
	public function get_top_pages( int $limit = 10, int $affiliate_id = 0, int $days = 30 ): array {
		global $wpdb;

		$table      = $this->get_table_name();
		$start_time = time() - ( $days * DAY_IN_SECONDS );

		$query = "SELECT page_url, COUNT(*) as visit_count, COUNT(CASE WHEN referral > 0 THEN 1 END) as referral_count FROM {$table} WHERE time > %d";

		$params = array( $start_time );

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

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

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

		// Calculate referral rates.
		foreach ( $results as &$result ) {
			$result['referral_rate'] = $result['visit_count'] > 0 ? ( $result['referral_count'] / $result['visit_count'] ) * 100 : 0;
		}

		return apply_filters( 'wc_affiliate_visit_top_pages', $results, $limit, $affiliate_id, $days );
	}

	/**
	 * Get top referrers
	 *
	 * @param int $limit        Number of top referrers to retrieve. Default is 10.
	 * @param int $affiliate_id Optional affiliate ID to filter by. Default is 0 (all affiliates).
	 * @param int $days         Number of days to look back. Default is 30 days.
	 *
	 * @return array List of top referrers with visit counts and conversion rates.
	 */
	public function get_top_referrers( int $limit = 10, int $affiliate_id = 0, int $days = 30 ): array {
		global $wpdb;

		$table      = $this->get_table_name();
		$start_time = time() - ( $days * DAY_IN_SECONDS );

		$query = "SELECT referrer_url, COUNT(*) as visit_count, COUNT(CASE WHEN referral > 0 THEN 1 END) as referral_count FROM {$table} WHERE time > %d AND referrer_url != ''";

		$params = array( $start_time );

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

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

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

		// Calculate referral rates.
		foreach ( $results as &$result ) {
			$result['referral_rate'] = $result['visit_count'] > 0 ? ( $result['referral_count'] / $result['visit_count'] ) * 100 : 0;
		}

		return apply_filters( 'wc_affiliate_visit_top_referrers', $results, $limit, $affiliate_id, $days );
	}

	/**
	 * Clean up old visits
	 *
	 * @param int $days Number of days to retain visits. Default is 90 days.
	 *
	 * @return int Number of deleted visits
	 */
	public function cleanup_old_visits( int $days = 90 ): int {
		global $wpdb;

		$table       = $this->get_table_name();
		$cutoff_time = time() - ( $days * DAY_IN_SECONDS );

		// Delete visits older than specified days that don't have associated referrals.
		return $wpdb->query(
			$wpdb->prepare(
				'DELETE v FROM %s v LEFT JOIN %swca_referrals r ON v.id = r.visit WHERE v.time < %d AND r.id IS NULL',
				$table,
				$wpdb->prefix,
				$cutoff_time
			)
		);
	}

	/**
	 * Validate visit data
	 *
	 * @param array $data Visit 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['page_url'] ) ) {
			$this->validate_url( $data['page_url'], $errors, true );
		}

		if ( isset( $data['referrer_url'] ) ) {
			$this->validate_url( $data['referrer_url'], $errors, false );
		}

		if ( isset( $data['ip'] ) ) {
			$this->validate_ip( $data['ip'], $errors, false );
		}

		if ( isset( $data['time'] ) ) {
			$this->validate_timestamp( $data['time'], $errors, false );
		}

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

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

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

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

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

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

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

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

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

		return apply_filters( 'wc_affiliate_visit_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 'referral':
				case 'time':
					$formats[] = '%d';
					break;
				default:
					$formats[] = '%s';
					break;
			}
		}

		return $formats;
	}

	/**
	 * Get visit conversion counts
	 *
	 * @param array $args Optional filter arguments.
	 *
	 * @return array Array with converted and unconverted visit counts.
	 */
	public function get_conversion_counts( array $args = array() ): array {
		global $wpdb;

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

		$args  = wp_parse_args( $args, $defaults );
		$table = $this->get_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'];
		}

		// Campaign filter.
		if ( $args['campaign'] ) {
			$where_conditions[] = 'campaign = %s';
			$query_params[]     = $args['campaign'];
		}

		// 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 );

		$query = "SELECT COUNT(*) as total_visits,
					SUM(IF(referral > 0, 1, 0)) as converted_visits,
					SUM(IF(referral = 0 OR referral IS NULL, 1, 0))
    				as unconverted_visits FROM {$table} WHERE {$where_clause}";

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

		$total_visits       = (int) $results['total_visits'];
		$converted_visits   = (int) $results['converted_visits'];
		$unconverted_visits = (int) $results['unconverted_visits'];

		$conversion_rate = $total_visits > 0 ? ( $converted_visits / $total_visits ) * 100 : 0;

		$data = array(
			'counts' => array(
				array(
					'type'  => 'converted',
					'label' => __( 'Converted', 'wc-affiliate' ),
					'count' => $converted_visits,
				),
				array(
					'type'  => 'unconverted',
					'label' => __( 'Unconverted', 'wc-affiliate' ),
					'count' => $unconverted_visits,
				),
			),
			'totals' => array(
				'total_visits'     => $total_visits,
				'converted_visits' => $converted_visits,
				'conversion_rate'  => round( $conversion_rate, 2 ),
			),
		);

		return apply_filters( 'wc_affiliate_visit_conversion_counts', $data, $args );
	}

	/**
	 * Get chart data for visits and conversions
	 *
	 * @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, visits, conversions, conversion rate, and unique visitors
	 */
	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      = $this->get_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';

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

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

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

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

			++$chart_data[ $period_key ]['visits'];
			++$chart_data[ $period_key ]['conversions'];
		}

		// Calculate conversion rates and unique visitors.
		$result = array();
		foreach ( $chart_data as $period_key => $data ) {
			$conversion_rate = $data['visits'] > 0 ? ( $data['conversions'] / $data['visits'] ) * 100 : 0;

			// Count unique visitors for this period.
			$period_visits   = array_filter(
				$visits,
				function ( $visit ) use ( $period_key, $period ) {
					return wp_date( $this->get_date_format_for_period( $period ), $visit['time'] ) === $period_key;
				}
			);
			$unique_visitors = count( array_unique( array_column( $period_visits, 'ip' ) ) );

			$result[] = array(
				'period'          => $data['period'],
				'visits'          => $data['visits'],
				'conversions'     => $data['conversions'],
				'conversion_rate' => round( $conversion_rate, 2 ),
				'unique_visitors' => $unique_visitors,
			);
		}

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