<?php
namespace JVBase\managers;

use JVBase\JVB;
use WP_Error;
use WP_REST_Response;
use Exception;

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

/**
 * Referral Tracking System
 *
 * Manages user referral codes, tracking, and rewards
 * Uses existing infrastructure: MetaManager, CacheManager, NotificationManager
 */
class ReferralManager
{
	protected $wpdb;
	protected $cache;
	protected $table_codes;
	protected $table_usage;
	protected $table_rewards;

	// Default reward settings
	const DEFAULT_REFERRER_REWARD_TYPE = 'per_user'; // or 'flat_total'
	const DEFAULT_REFERRER_REWARD_AMOUNT = 25.00;
	const DEFAULT_REFERRED_REWARD_TYPE = 'percentage'; // or 'fixed'
	const DEFAULT_REFERRED_REWARD_AMOUNT = 20; // 20% or $20

	public function __construct()
	{
		global $wpdb;
		$this->wpdb = $wpdb;
		$this->cache = JVB()->cache();

		$this->table_codes = $wpdb->prefix . BASE . 'referral_codes';
		$this->table_usage = $wpdb->prefix . BASE . 'referral_usage';
		$this->table_rewards = $wpdb->prefix . BASE . 'referral_rewards';

		$this->registerHooks();
	}

	/**
	 * Register WordPress hooks
	 */
	protected function registerHooks(): void
	{
		// Track new user registrations with referral codes
		add_action('user_register', [$this, 'trackReferralRegistration'], 10, 2);

		// Monthly report cron
		add_action(BASE . 'referral_monthly_report', [$this, 'generateMonthlyReports']);

		// Cleanup expired codes
		add_action(BASE . 'cleanup_referrals', [$this, 'cleanupExpiredCodes']);

		// Handle bulk operations
		add_filter(BASE . 'handle_bulk_operation', [$this, 'processOperation'], 10, 3);
	}

	/************************************************************
	 * Referral Code Management
	 ************************************************************/

	/**
	 * Create or update a user's referral code
	 *
	 * @param int $user_id User ID
	 * @param string|null $custom_code Optional custom code (must be unique)
	 * @return array|WP_Error
	 */
	public function createReferralCode(int $user_id, ?string $custom_code = null): array|WP_Error
	{
		// Validate user
		if (!$this->validateUser($user_id)) {
			return new WP_Error('invalid_user', 'Invalid user ID');
		}

		// Check if user already has a code
		$existing = $this->getUserReferralCode($user_id);

		if ($existing && !$custom_code) {
			return $existing; // Return existing code if no custom code requested
		}

		// Generate or validate custom code
		$code = $custom_code ? $this->sanitizeCode($custom_code) : $this->generateUniqueCode($user_id);

		// Check if code is already taken
		if ($this->isCodeTaken($code, $user_id)) {
			return new WP_Error('code_taken', 'This referral code is already in use');
		}

		// Validate code format
		if (!$this->validateCodeFormat($code)) {
			return new WP_Error('invalid_format', 'Code must be 4-20 alphanumeric characters');
		}

		$data = [
			'user_id' => $user_id,
			'code' => $code,
			'is_active' => 1,
			'created_at' => current_time('mysql'),
			'updated_at' => current_time('mysql')
		];

		if ($existing) {
			// Update existing code
			$result = $this->wpdb->update(
				$this->table_codes,
				['code' => $code, 'updated_at' => current_time('mysql')],
				['user_id' => $user_id]
			);
		} else {
			// Insert new code
			$result = $this->wpdb->insert($this->table_codes, $data);
		}

		if ($result === false) {
			return new WP_Error('db_error', 'Failed to save referral code');
		}

		// Clear cache
		$this->cache->delete('referral_code_' . $user_id);
		$this->cache->delete('referral_user_' . $code);

		return [
			'success' => true,
			'code' => $code,
			'url' => $this->getReferralUrl($code)
		];
	}

	/**
	 * Get user's referral code
	 *
	 * @param int $user_id User ID
	 * @return array|null
	 */
	public function getUserReferralCode(int $user_id): ?array
	{
		$cache_key = 'referral_code_' . $user_id;
		$cached = $this->cache->get($cache_key);

		if ($cached !== false) {
			return $cached;
		}

		$result = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT * FROM {$this->table_codes} WHERE user_id = %d",
			$user_id
		), ARRAY_A);

		if ($result) {
			$result['url'] = $this->getReferralUrl($result['code']);
			$result['stats'] = $this->getCodeStats($result['code']);
			$this->cache->set($cache_key, $result, 3600);
		}

		return $result;
	}

	/**
	 * Get referral code statistics
	 *
	 * @param string $code Referral code
	 * @return array
	 */
	public function getCodeStats(string $code): array
	{
		$cache_key = 'referral_stats_' . $code;
		$cached = $this->cache->get($cache_key);

		if ($cached !== false) {
			return $cached;
		}

		$stats = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT
                COUNT(*) as total_uses,
                COUNT(CASE WHEN registered_at IS NOT NULL THEN 1 END) as completed_registrations,
                COUNT(CASE WHEN first_order_at IS NOT NULL THEN 1 END) as converted_orders
             FROM {$this->table_usage}
             WHERE referral_code = %s",
			$code
		), ARRAY_A);

		$this->cache->set($cache_key, $stats, 1800);
		return $stats ?: ['total_uses' => 0, 'completed_registrations' => 0, 'converted_orders' => 0];
	}

	/**
	 * Get user ID from referral code
	 *
	 * @param string $code Referral code
	 * @return int|null User ID or null
	 */
	public function getUserFromCode(string $code): ?int
	{
		$cache_key = 'referral_user_' . $code;
		$cached = $this->cache->get($cache_key);

		if ($cached !== false) {
			return $cached;
		}

		$user_id = $this->wpdb->get_var($this->wpdb->prepare(
			"SELECT user_id FROM {$this->table_codes}
             WHERE code = %s AND is_active = 1",
			$code
		));

		if ($user_id) {
			$this->cache->set($cache_key, (int)$user_id, 3600);
			return (int)$user_id;
		}

		return null;
	}

	/************************************************************
	 * Referral Tracking
	 ************************************************************/

	/**
	 * Track when someone clicks a referral link
	 *
	 * @param string $code Referral code
	 * @param string|null $email Optional email if user provides it
	 * @return array|WP_Error
	 */
	public function trackReferralClick(string $code, ?string $email = null): array|WP_Error
	{
		$user_id = $this->getUserFromCode($code);

		if (!$user_id) {
			return new WP_Error('invalid_code', 'Invalid referral code');
		}

		// Check if this email/IP already used this code recently (prevent duplicate tracking)
		$ip_address = $this->getClientIp();

		$existing = $this->wpdb->get_var($this->wpdb->prepare(
			"SELECT id FROM {$this->table_usage}
             WHERE referral_code = %s
             AND (email = %s OR ip_address = %s)
             AND clicked_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)",
			$code,
			$email ?: '',
			$ip_address
		));

		if ($existing) {
			return ['success' => true, 'message' => 'Already tracked'];
		}

		// Track the click
		$data = [
			'referral_code' => $code,
			'referrer_user_id' => $user_id,
			'email' => $email,
			'ip_address' => $ip_address,
			'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '',
			'clicked_at' => current_time('mysql')
		];

		$result = $this->wpdb->insert($this->table_usage, $data);

		if ($result === false) {
			return new WP_Error('db_error', 'Failed to track referral');
		}

		// Store in cookie for 30 days
		setcookie('jvb_referral', $code, time() + (86400 * 30), '/');

		return [
			'success' => true,
			'tracking_id' => $this->wpdb->insert_id
		];
	}

	/**
	 * Track referral when user registers
	 *
	 * @param int $new_user_id Newly registered user ID
	 * @param array $userdata User data
	 * @return void
	 */
	public function trackReferralRegistration(int $new_user_id, array $userdata = []): void
	{
		// Check for referral code in cookie or GET parameter
		$code = $_COOKIE['jvb_referral'] ?? $_GET['ref'] ?? null;

		if (!$code) {
			return;
		}

		$user = get_userdata($new_user_id);
		if (!$user) {
			return;
		}

		// Update or create usage record
		$usage = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT * FROM {$this->table_usage}
             WHERE referral_code = %s
             AND (email = %s OR ip_address = %s)
             ORDER BY clicked_at DESC LIMIT 1",
			$code,
			$user->user_email,
			$this->getClientIp()
		), ARRAY_A);

		if ($usage) {
			// Update existing record
			$this->wpdb->update(
				$this->table_usage,
				[
					'referred_user_id' => $new_user_id,
					'email' => $user->user_email,
					'registered_at' => current_time('mysql')
				],
				['id' => $usage['id']]
			);
		} else {
			// Create new record (direct registration with code)
			$referrer_id = $this->getUserFromCode($code);

			if ($referrer_id) {
				$this->wpdb->insert($this->table_usage, [
					'referral_code' => $code,
					'referrer_user_id' => $referrer_id,
					'referred_user_id' => $new_user_id,
					'email' => $user->user_email,
					'ip_address' => $this->getClientIp(),
					'clicked_at' => current_time('mysql'),
					'registered_at' => current_time('mysql')
				]);
			}
		}

		// Clear cache
		$this->cache->delete('referral_stats_' . $code);

		// Notify referrer
		if (isset($referrer_id) && $referrer_id) {
			$this->notifyReferrer($referrer_id, $new_user_id);
		}
	}

	/**
	 * Track when referred user makes first order
	 *
	 * @param int $user_id User who made order
	 * @param float $order_amount Order amount
	 * @return void
	 */
	public function trackFirstOrder(int $user_id, float $order_amount): void
	{
		$usage = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT * FROM {$this->table_usage}
             WHERE referred_user_id = %d
             AND first_order_at IS NULL",
			$user_id
		), ARRAY_A);

		if (!$usage) {
			return;
		}

		// Update usage record
		$this->wpdb->update(
			$this->table_usage,
			[
				'first_order_at' => current_time('mysql'),
				'first_order_amount' => $order_amount
			],
			['id' => $usage['id']]
		);

		// Process rewards
		$this->processRewards($usage['referrer_user_id'], $user_id, $order_amount);

		// Clear cache
		$this->cache->delete('referral_stats_' . $usage['referral_code']);
	}

	/************************************************************
	 * Reward Management
	 ************************************************************/

	/**
	 * Process referral rewards
	 *
	 * @param int $referrer_id User who referred
	 * @param int $referred_id User who was referred
	 * @param float $order_amount First order amount
	 * @return void
	 */
	protected function processRewards(int $referrer_id, int $referred_id, float $order_amount): void
	{
		// Get reward settings
		$settings = $this->getRewardSettings();

		// Calculate referrer reward
		$referrer_amount = $this->calculateReferrerReward($referrer_id, $settings);

		if ($referrer_amount > 0) {
			$this->addReward($referrer_id, 'referrer', $referrer_amount, $referred_id);
		}

		// Calculate referred user reward (already applied at checkout)
		$referred_amount = $this->calculateReferredReward($order_amount, $settings);

		if ($referred_amount > 0) {
			$this->addReward($referred_id, 'referred', $referred_amount, $referrer_id);
		}
	}

	/**
	 * Calculate referrer reward amount
	 *
	 * @param int $referrer_id Referrer user ID
	 * @param array $settings Reward settings
	 * @return float Reward amount
	 */
	protected function calculateReferrerReward(int $referrer_id, array $settings): float
	{
		$type = $settings['referrer_reward_type'];
		$amount = floatval($settings['referrer_reward_amount']);

		if ($type === 'per_user') {
			return $amount;
		}

		// For 'flat_total', check if total reward cap reached
		$total_earned = $this->getTotalRewardsEarned($referrer_id, 'referrer');

		if ($total_earned >= $amount) {
			return 0; // Cap reached
		}

		return min($settings['referrer_reward_per_user'] ?? 25.00, $amount - $total_earned);
	}

	/**
	 * Calculate referred user reward
	 *
	 * @param float $order_amount Order amount
	 * @param array $settings Reward settings
	 * @return float Discount amount
	 */
	protected function calculateReferredReward(float $order_amount, array $settings): float
	{
		$type = $settings['referred_reward_type'];
		$amount = floatval($settings['referred_reward_amount']);

		if ($type === 'percentage') {
			return $order_amount * ($amount / 100);
		}

		return min($amount, $order_amount); // Fixed amount, but not more than order
	}

	/**
	 * Add reward to user's account
	 *
	 * @param int $user_id User receiving reward
	 * @param string $type 'referrer' or 'referred'
	 * @param float $amount Reward amount
	 * @param int $related_user_id Related user ID
	 * @return bool
	 */
	protected function addReward(int $user_id, string $type, float $amount, int $related_user_id): bool
	{
		$data = [
			'user_id' => $user_id,
			'reward_type' => $type,
			'amount' => $amount,
			'related_user_id' => $related_user_id,
			'status' => 'pending',
			'created_at' => current_time('mysql')
		];

		$result = $this->wpdb->insert($this->table_rewards, $data);

		if ($result) {
			// Notify user
			$notification_type = $type === 'referrer' ? 'referral_reward_earned' : 'referral_reward_received';
			JVB()->notification()->addNotification(
				$user_id,
				$notification_type,
				null,
				sprintf('You earned $%.2f in referral rewards!', $amount)
			);

			return true;
		}

		return false;
	}

	/**
	 * Get total rewards earned by user
	 *
	 * @param int $user_id User ID
	 * @param string|null $type Optional reward type filter
	 * @return float Total amount
	 */
	public function getTotalRewardsEarned(int $user_id, ?string $type = null): float
	{
		$sql = "SELECT SUM(amount) FROM {$this->table_rewards} WHERE user_id = %d";
		$params = [$user_id];

		if ($type) {
			$sql .= " AND reward_type = %s";
			$params[] = $type;
		}

		$total = $this->wpdb->get_var($this->wpdb->prepare($sql, $params));
		return floatval($total);
	}

	/**
	 * Get user's available reward balance
	 *
	 * @param int $user_id User ID
	 * @return float Available balance
	 */
	public function getAvailableBalance(int $user_id): float
	{
		$total = $this->wpdb->get_var($this->wpdb->prepare(
			"SELECT SUM(amount) FROM {$this->table_rewards}
             WHERE user_id = %d AND status IN ('pending', 'available')",
			$user_id
		));

		return floatval($total);
	}

	/************************************************************
	 * Monthly Reports
	 ************************************************************/

	/**
	 * Generate monthly reports for all users with referrals
	 *
	 * @return void
	 */
	public function generateMonthlyReports(): void
	{
		$first_day = date('Y-m-01', strtotime('last month'));
		$last_day = date('Y-m-t', strtotime('last month'));

		// Get all users who had referral activity last month
		$users = $this->wpdb->get_col($this->wpdb->prepare(
			"SELECT DISTINCT referrer_user_id
             FROM {$this->table_usage}
             WHERE registered_at BETWEEN %s AND %s
             OR first_order_at BETWEEN %s AND %s",
			$first_day, $last_day, $first_day, $last_day
		));

		if (empty($users)) {
			return;
		}

		// Queue report generation
		$queue = JVB()->queue();
		$queue->queueOperation(
			'generate_referral_report',
			0,
			[
				'users' => $users,
				'period_start' => $first_day,
				'period_end' => $last_day
			],
			[
				'count' => count($users),
				'chunk_key' => 'users',
				'chunk_size' => 10,
				'priority' => 'low'
			]
		);
	}

	/**
	 * Generate report for a single user
	 *
	 * @param int $user_id User ID
	 * @param string $period_start Start date
	 * @param string $period_end End date
	 * @return array|WP_Error
	 */
	public function generateUserReport(int $user_id, string $period_start, string $period_end): array|WP_Error
	{
		$user = get_userdata($user_id);
		if (!$user) {
			return new WP_Error('invalid_user', 'Invalid user');
		}

		$code = $this->getUserReferralCode($user_id);
		if (!$code) {
			return new WP_Error('no_code', 'User has no referral code');
		}

		// Get activity for period
		$activity = $this->wpdb->get_results($this->wpdb->prepare(
			"SELECT * FROM {$this->table_usage}
             WHERE referrer_user_id = %d
             AND (
                (registered_at BETWEEN %s AND %s)
                OR (first_order_at BETWEEN %s AND %s)
             )
             ORDER BY registered_at DESC",
			$user_id, $period_start, $period_end, $period_start, $period_end
		), ARRAY_A);

		// Generate CSV
		$csv_path = $this->generateActivityCSV($user_id, $activity, $period_start, $period_end);

		// Send email with CSV attachment
		$this->sendMonthlyReportEmail($user, $activity, $csv_path, $period_start, $period_end);

		return [
			'success' => true,
			'user_id' => $user_id,
			'activity_count' => count($activity)
		];
	}

	/**
	 * Generate CSV file for activity
	 *
	 * @param int $user_id User ID
	 * @param array $activity Activity records
	 * @param string $period_start Start date
	 * @param string $period_end End date
	 * @return string File path
	 */
	protected function generateActivityCSV(int $user_id, array $activity, string $period_start, string $period_end): string
	{
		$upload_dir = wp_upload_dir();
		$filename = sprintf(
			'referral-report-%d-%s-to-%s.csv',
			$user_id,
			$period_start,
			$period_end
		);
		$filepath = $upload_dir['basedir'] . '/referral-reports/' . $filename;

		// Create directory if needed
		wp_mkdir_p(dirname($filepath));

		$fp = fopen($filepath, 'w');

		// Headers
		fputcsv($fp, [
			'Date',
			'Type',
			'Email',
			'User ID',
			'Status',
			'Order Amount',
			'Reward Earned'
		]);

		// Data rows
		foreach ($activity as $record) {
			$type = $record['registered_at'] ? 'Registration' : 'Click';
			if ($record['first_order_at']) {
				$type = 'First Order';
			}

			fputcsv($fp, [
				$record['registered_at'] ?? $record['clicked_at'],
				$type,
				$record['email'],
				$record['referred_user_id'] ?? 'N/A',
				$record['first_order_at'] ? 'Converted' : ($record['registered_at'] ? 'Registered' : 'Pending'),
				$record['first_order_amount'] ?? 'N/A',
				$this->getRewardForUsage($record['id'])
			]);
		}

		fclose($fp);
		return $filepath;
	}

	/**
	 * Get reward amount for usage record
	 *
	 * @param int $usage_id Usage ID
	 * @return string Formatted amount or N/A
	 */
	protected function getRewardForUsage(int $usage_id): string
	{
		$usage = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT * FROM {$this->table_usage} WHERE id = %d",
			$usage_id
		), ARRAY_A);

		if (!$usage || !$usage['referred_user_id']) {
			return 'N/A';
		}

		$reward = $this->wpdb->get_var($this->wpdb->prepare(
			"SELECT amount FROM {$this->table_rewards}
             WHERE user_id = %d AND related_user_id = %d
             AND reward_type = 'referrer'",
			$usage['referrer_user_id'],
			$usage['referred_user_id']
		));

		return $reward ? '$' . number_format($reward, 2) : 'N/A';
	}

	/**
	 * Send monthly report email
	 *
	 * @param object $user User object
	 * @param array $activity Activity records
	 * @param string $csv_path Path to CSV file
	 * @param string $period_start Start date
	 * @param string $period_end End date
	 * @return bool
	 */
	protected function sendMonthlyReportEmail($user, array $activity, string $csv_path, string $period_start, string $period_end): bool
	{
		$total_clicks = count(array_filter($activity, fn($a) => !empty($a['clicked_at'])));
		$total_registrations = count(array_filter($activity, fn($a) => !empty($a['registered_at'])));
		$total_orders = count(array_filter($activity, fn($a) => !empty($a['first_order_at'])));

		$total_earned = $this->wpdb->get_var($this->wpdb->prepare(
			"SELECT SUM(r.amount) FROM {$this->table_rewards} r
             INNER JOIN {$this->table_usage} u ON r.related_user_id = u.referred_user_id
             WHERE r.user_id = %d
             AND r.reward_type = 'referrer'
             AND r.created_at BETWEEN %s AND %s",
			$user->ID, $period_start, $period_end
		));

		$subject = sprintf(
			'Your Referral Report for %s',
			date('F Y', strtotime($period_start))
		);

		$message = sprintf(
			"Hi %s,\n\n" .
			"Here's your referral activity summary for %s:\n\n" .
			"📊 Activity Overview:\n" .
			"- Clicks: %d\n" .
			"- New Registrations: %d\n" .
			"- First Orders: %d\n" .
			"- Total Earned: $%.2f\n\n" .
			"Your current reward balance: $%.2f\n\n" .
			"Detailed activity is attached as a CSV file.\n\n" .
			"Keep sharing your referral link to earn more rewards!\n" .
			"Your link: %s\n\n" .
			"Thanks,\n%s",
			$user->display_name,
			date('F Y', strtotime($period_start)),
			$total_clicks,
			$total_registrations,
			$total_orders,
			floatval($total_earned),
			$this->getAvailableBalance($user->ID),
			$this->getReferralUrl($this->getUserReferralCode($user->ID)['code']),
			get_bloginfo('name')
		);

		return wp_mail(
			$user->user_email,
			$subject,
			$message,
			['Content-Type: text/plain; charset=UTF-8'],
			[$csv_path]
		);
	}

	/************************************************************
	 * Settings & Configuration
	 ************************************************************/

	/**
	 * Get referral reward settings
	 *
	 * @return array Settings
	 */
	public function getRewardSettings(): array
	{
		$defaults = [
			'referrer_reward_type' => self::DEFAULT_REFERRER_REWARD_TYPE,
			'referrer_reward_amount' => self::DEFAULT_REFERRER_REWARD_AMOUNT,
			'referrer_reward_per_user' => self::DEFAULT_REFERRER_REWARD_AMOUNT,
			'referred_reward_type' => self::DEFAULT_REFERRED_REWARD_TYPE,
			'referred_reward_amount' => self::DEFAULT_REFERRED_REWARD_AMOUNT
		];

		// Get from options (can be customized in admin settings)
		$saved = get_option(BASE . 'referral_settings', []);

		return array_merge($defaults, $saved);
	}

	/**
	 * Update referral reward settings
	 *
	 * @param array $settings New settings
	 * @return bool
	 */
	public function updateRewardSettings(array $settings): bool
	{
		$valid_settings = [];

		if (isset($settings['referrer_reward_type'])) {
			$valid_settings['referrer_reward_type'] = in_array($settings['referrer_reward_type'], ['per_user', 'flat_total'])
				? $settings['referrer_reward_type']
				: self::DEFAULT_REFERRER_REWARD_TYPE;
		}

		if (isset($settings['referrer_reward_amount'])) {
			$valid_settings['referrer_reward_amount'] = max(0, floatval($settings['referrer_reward_amount']));
		}

		if (isset($settings['referrer_reward_per_user'])) {
			$valid_settings['referrer_reward_per_user'] = max(0, floatval($settings['referrer_reward_per_user']));
		}

		if (isset($settings['referred_reward_type'])) {
			$valid_settings['referred_reward_type'] = in_array($settings['referred_reward_type'], ['percentage', 'fixed'])
				? $settings['referred_reward_type']
				: self::DEFAULT_REFERRED_REWARD_TYPE;
		}

		if (isset($settings['referred_reward_amount'])) {
			$valid_settings['referred_reward_amount'] = max(0, floatval($settings['referred_reward_amount']));
		}

		return update_option(BASE . 'referral_settings', $valid_settings);
	}

	/************************************************************
	 * Helper Methods
	 ************************************************************/

	/**
	 * Generate unique referral code
	 *
	 * @param int $user_id User ID
	 * @return string Unique code
	 */
	protected function generateUniqueCode(int $user_id): string
	{
		$user = get_userdata($user_id);
		$base = strtoupper(substr($user->user_login, 0, 6));
		$code = $base . rand(1000, 9999);

		// Ensure uniqueness
		while ($this->isCodeTaken($code)) {
			$code = $base . rand(1000, 9999);
		}

		return $code;
	}

	/**
	 * Sanitize referral code
	 *
	 * @param string $code Raw code
	 * @return string Sanitized code
	 */
	protected function sanitizeCode(string $code): string
	{
		return strtoupper(preg_replace('/[^A-Z0-9]/', '', strtoupper($code)));
	}

	/**
	 * Check if code is already taken
	 *
	 * @param string $code Code to check
	 * @param int|null $exclude_user_id User ID to exclude
	 * @return bool
	 */
	protected function isCodeTaken(string $code, ?int $exclude_user_id = null): bool
	{
		$sql = "SELECT COUNT(*) FROM {$this->table_codes} WHERE code = %s";
		$params = [$code];

		if ($exclude_user_id) {
			$sql .= " AND user_id != %d";
			$params[] = $exclude_user_id;
		}

		$count = $this->wpdb->get_var($this->wpdb->prepare($sql, $params));
		return $count > 0;
	}

	/**
	 * Validate code format
	 *
	 * @param string $code Code to validate
	 * @return bool
	 */
	protected function validateCodeFormat(string $code): bool
	{
		return preg_match('/^[A-Z0-9]{4,20}$/', $code);
	}

	/**
	 * Validate user ID
	 *
	 * @param int $user_id User ID
	 * @return bool
	 */
	protected function validateUser(int $user_id): bool
	{
		return get_userdata($user_id) !== false;
	}

	/**
	 * Get referral URL for code
	 *
	 * @param string $code Referral code
	 * @return string Full URL
	 */
	protected function getReferralUrl(string $code): string
	{
		return add_query_arg('ref', $code, home_url('/register'));
	}

	/**
	 * Get client IP address
	 *
	 * @return string IP address
	 */
	protected function getClientIp(): string
	{
		$ip = $_SERVER['HTTP_CF_CONNECTING_IP'] ??
			$_SERVER['HTTP_X_FORWARDED_FOR'] ??
			$_SERVER['REMOTE_ADDR'] ??
			'0.0.0.0';

		return filter_var($ip, FILTER_VALIDATE_IP) ? $ip : '0.0.0.0';
	}

	/**
	 * Notify referrer of new registration
	 *
	 * @param int $referrer_id Referrer user ID
	 * @param int $referred_id Referred user ID
	 * @return void
	 */
	protected function notifyReferrer(int $referrer_id, int $referred_id): void
	{
		JVB()->notification()->addNotification(
			$referrer_id,
			'referral_signup',
			$referred_id,
			'Someone signed up using your referral code!'
		);
	}

	/**
	 * Cleanup expired/old records
	 *
	 * @return void
	 */
	public function cleanupExpiredCodes(): void
	{
		// Delete clicks older than 90 days with no registration
		$this->wpdb->query(
			"DELETE FROM {$this->table_usage}
             WHERE clicked_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
             AND registered_at IS NULL"
		);
	}

	/**
	 * Handle bulk operations
	 *
	 * @param mixed $result Default result
	 * @param object $operation Operation object
	 * @param array $data Operation data
	 * @return mixed
	 */
	public function processOperation($result, object $operation, array $data)
	{
		if ($operation->type === 'generate_referral_report') {
			$user_id = $data['users'][$operation->progress_count] ?? null;

			if ($user_id) {
				return $this->generateUserReport(
					$user_id,
					$data['period_start'],
					$data['period_end']
				);
			}
		}

		return $result;
	}
}