<?php
namespace JVBase\rest\routes;

use JVBase\importers\JaneAppClientImporter;
use JVBase\managers\JaneSalesImporter;
use JVBase\rest\RestRouteManager;
use WP_REST_Request;
use WP_REST_Response;
use WP_Error;

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

/**
 * REST API routes for referral system
 */
class ReferralRoutes extends RestRouteManager
{
	protected string $referrals_table;
	protected string $rewards_table;
	protected string $treatments_table;
	protected string $jane_clients_table;
	protected $wpdb;

	public function __construct()
	{
		$this->route = 'referrals';
		$this->cache_name = 'referrals';
		parent::__construct();

		global $wpdb;
		$this->wpdb = $wpdb;
		$this->referrals_table = $wpdb->prefix . BASE . 'referrals';
		$this->rewards_table = $wpdb->prefix . BASE . 'referral_rewards';
		$this->treatments_table = $wpdb->prefix . BASE . 'referral_treatments';
		$this->jane_clients_table = $wpdb->prefix . BASE . 'jane_clients';
	}

	public function registerRoutes(): void
	{
		// Get user's referrals
		register_rest_route($this->namespace, "/{$this->route}", [
			'methods' => 'GET',
			'callback' => [$this, 'getUserReferrals'],
			'permission_callback' => [$this, 'checkPermission']
		]);

		register_rest_route($this->namespace, "/{$this->route}/register", [
			'methods' => 'POST',
			'callback' => [$this, 'registerWithReferral'],
			'permission_callback' => [$this, 'checkRateLimit'],
			'args' => [
				'name' => [
					'required' => true,
					'type' => 'string',
					'sanitize_callback' => 'sanitize_text_field'
				],
				'email' => [
					'required' => true,
					'type' => 'string',
					'format' => 'email',
					'validate_callback' => function($param) {
						return is_email($param);
					}
				],
				'code' => [
					'required' => true,
					'type' => 'string',
					'sanitize_callback' => function($code) {
						return strtoupper(sanitize_text_field($code));
					}
				]
			]
		]);

		register_rest_route($this->namespace, '/referrals/check-code', [
			'methods' => 'POST',
			'callback' => [$this, 'checkReferralCode'],
			'permission_callback' => [$this, 'checkRateLimit'],
			'args' => [
				'code' => [
					'required' => true,
					'type' => 'string',
					'sanitize_callback' => function($code) {
						return strtoupper(sanitize_text_field($code));
					}
				]
			]
		]);

		// Get or create referral code
		register_rest_route($this->namespace, "/{$this->route}/code", [
			'methods' => 'GET',
			'callback' => [$this, 'getReferralCode'],
			'permission_callback' => [$this, 'checkPermission']
		]);

		// Track referral click (public endpoint)
		register_rest_route($this->namespace, "/{$this->route}/track", [
			'methods' => 'POST',
			'callback' => [$this, 'trackReferralClick'],
			'permission_callback' => [$this, 'checkRateLimit'],
			'args' => [
				'code' => [
					'required' => true,
					'type' => 'string'
				]
			]
		]);

		// Mark referral as treated
		register_rest_route($this->namespace, "/{$this->route}/(?P<id>\d+)/treat", [
			'methods' => 'POST',
			'callback' => [$this, 'markAsTreated'],
			'permission_callback' => function() {
				return current_user_can('manage_options');
			},
			'args' => [
				'id' => [
					'required' => true,
					'validate_callback' => function($param) {
						return is_numeric($param);
					}
				]
			]
		]);

		// Send referral invitation
		register_rest_route($this->namespace, '/'.$this->route.'/invite', [
			'methods' => 'POST',
			'callback' => [$this, 'sendInvitation'],
			'permission_callback' => [$this, 'checkPermission'],
			'args' => [
				'email' => [
					'required' => true,
					'type' => 'string',
					'format' => 'email',
					'validate_callback' => function($param) {
						return is_email($param);
					}
				],
				'name' => [
					'required' => true,
					'type' => 'string',
					'sanitize_callback' => 'sanitize_text_field'
				]
			]
		]);

		// Send batch invitations
		register_rest_route($this->namespace, '/'.$this->route.'/invite/batch', [
			'methods' => 'POST',
			'callback' => [$this, 'sendBatchInvitations'],
			'permission_callback' => [$this, 'checkPermission'],
			'args' => [
				'invitations' => [
					'required' => true,
					'type' => 'array',
					'validate_callback' => function($param) {
						return is_array($param) && !empty($param);
					}
				]
			]
		]);

		// Get invitation stats for current user
		register_rest_route($this->namespace, '/'.$this->route.'/invite/stats', [
			'methods' => 'GET',
			'callback' => [$this, 'getInvitationStats'],
			'permission_callback' => [$this, 'checkPermission']
		]);

		// Export referrals for Jane App
		register_rest_route($this->namespace, '/'.$this->route.'/export', [
			'methods' => 'POST',
			'callback' => [$this, 'exportReferrals'],
			'permission_callback' => function() {
				return current_user_can('manage_options');
			},
			'args' => [
				'start_date' => [
					'required' => true,
					'type' => 'string',
					'validate_callback' => function($param) {
						return (bool) strtotime($param);
					}
				],
				'end_date' => [
					'required' => true,
					'type' => 'string',
					'validate_callback' => function($param) {
						return (bool) strtotime($param);
					}
				]
			]
		]);


		// Get top referrers (admin only)
		register_rest_route($this->namespace, "/{$this->route}/leaderboard", [
			'methods' => 'GET',
			'callback' => [$this, 'getTopReferrers'],
			'permission_callback' => function() {
				return current_user_can('manage_options');
			},
			'args' => [
				'period' => [
					'default' => 'week',
					'enum' => ['day', 'week', 'month', 'all']
				],
				'limit' => [
					'default' => 10,
					'type' => 'integer'
				]
			]
		]);

		// Get/Update referral settings (admin only)
		register_rest_route($this->namespace, "/{$this->route}/settings", [
			[
				'methods' => 'GET',
				'callback' => [$this, 'getSettings'],
				'permission_callback' => function() {
					return current_user_can('manage_options');
				}
			],
			[
				'methods' => 'POST',
				'callback' => [$this, 'updateSettings'],
				'permission_callback' => function() {
					return current_user_can('manage_options');
				}
			]
		]);

		register_rest_route($this->namespace, "/{$this->route}/add-code", [
			'methods' 	=> 'POST',
			'callback'	=> [$this, 'addReferralCodeAfterRegistration'],
			'permission_callback'	=> [$this, 'checkRateLimit'],
			'args'	=> [
				'code'	=> [
					'required'	=> true,
					'type'		=> 'string',
					'sanitize_callback'	=> function ($code) {
						return strtoupper(sanitize_text_field($code));
					}
				]
			]
		]);


		/***************************
		 * ADDITIONAL
		 */
// CSV Uploads
		register_rest_route($this->namespace, '/referrals/upload-clients', [
			'methods' => 'POST',
			'callback' => [$this, 'handleClientUpload'],
			'permission_callback' => [$this, 'checkAdminPermission']
		]);

		register_rest_route($this->namespace, '/referrals/upload-sales', [
			'methods' => 'POST',
			'callback' => [$this, 'handleSalesUpload'],
			'permission_callback' => [$this, 'checkAdminPermission']
		]);

		// Referral List & Details
		register_rest_route($this->namespace, '/referrals/list', [
			'methods' => 'GET',
			'callback' => [$this, 'getReferralsList'],
			'permission_callback' => [$this, 'checkAdminPermission']
		]);

		register_rest_route($this->namespace, '/referrals/(?P<id>\d+)', [
			'methods' => 'GET',
			'callback' => [$this, 'getReferralDetails'],
			'permission_callback' => [$this, 'checkAdminPermission']
		]);

		// Manual Status Updates
		register_rest_route($this->namespace, '/referrals/mark-consulted', [
			'methods' => 'POST',
			'callback' => [$this, 'handleMarkConsulted'],
			'permission_callback' => [$this, 'checkAdminPermission']
		]);

		register_rest_route($this->namespace, '/referrals/mark-treated', [
			'methods' => 'POST',
			'callback' => [$this, 'handleMarkTreated'],
			'permission_callback' => [$this, 'checkAdminPermission']
		]);

		// User-facing endpoints
		register_rest_route($this->namespace, '/referrals/my-stats', [
			'methods' => 'GET',
			'callback' => [$this, 'getMyStats'],
			'permission_callback' => [$this, 'checkPermission']
		]);

		register_rest_route($this->namespace, '/referrals/my-referrals', [
			'methods' => 'GET',
			'callback' => [$this, 'getMyReferrals'],
			'permission_callback' => [$this, 'checkPermission']
		]);
	}

	/**
	 * Check admin-only permission
	 */
	public function checkAdminPermission(WP_REST_Request $request): bool
	{
		return current_user_can('manage_options') && parent::checkPermission($request);
	}

	public function checkPermission(WP_REST_Request $request): bool
	{
		return is_user_logged_in();
	}

	/**
	 * Get user's referrals
	 */
	public function getUserReferrals(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();

		$args = [
			'status' => $request->get_param('status') ?? 'all',
			'limit' => $request->get_param('limit') ?? 50,
			'offset' => $request->get_param('offset') ?? 0
		];

		$referrals = JVB()->referrals()->getUserReferrals($user_id, $args);

		return new WP_REST_Response([
			'success' => true,
			'referrals' => $referrals
		]);
	}

	/**
	 * Get user's referral code
	 */
	public function getReferralCode(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$code = JVB()->referrals()->getUserReferralCode($user_id);

		if (is_wp_error($code)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => $code->get_error_message()
			], 400);
		}

		return new WP_REST_Response([
			'success' => true,
			'code' => $code,
			'share_url' => home_url('/?ref=' . $code)
		]);
	}

	/**
	 * Update user's referral code
	 */
	public function updateReferralCode(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$new_code = strtoupper(sanitize_text_field($request->get_param('code')));

		$result = JVB()->referrals()->getUserReferralCode($user_id, $new_code);

		if (is_wp_error($result)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => $result->get_error_message()
			], 400);
		}

		return new WP_REST_Response([
			'success' => true,
			'code' => $result,
			'message' => 'Referral code updated successfully'
		]);
	}

	/**
	 * Track referral click and store in session
	 */
	public function trackReferralClick(WP_REST_Request $request): WP_REST_Response
	{
		$code = strtoupper(sanitize_text_field($request->get_param('code')));

		// Start session if not already started
		if (session_status() === PHP_SESSION_NONE) {
			session_start();
		}

		// Store referral code in both session and cookie (30 day expiry)
		$_SESSION[BASE . 'referral_code'] = $code;
		setcookie(BASE . 'referral_code', $code, time() + (30 * DAY_IN_SECONDS), '/');

		return new WP_REST_Response([
			'success' => true,
			'message' => 'Referral tracked'
		]);
	}

	/**
	 * Mark referral as treated
	 */
	public function markAsTreated(WP_REST_Request $request): WP_REST_Response
	{
		$referral_id = intval($request->get_param('id'));

		$result = JVB()->referrals()->markAsTreated($referral_id, true);

		if (!$result) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Failed to update referral'
			], 400);
		}

		return new WP_REST_Response([
			'success' => true,
			'message' => 'Referral marked as treated and rewards created'
		]);
	}

	/**
	 * Get user stats
	 */
	public function getUserStats(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$stats = JVB()->referrals()->getUserStats($user_id);

		return new WP_REST_Response([
			'success' => true,
			'stats' => $stats
		]);
	}

	/**
	 * Get top referrers
	 */
	public function getTopReferrers(WP_REST_Request $request): WP_REST_Response
	{
		$period = $request->get_param('period') ?? 'week';
		$limit = $request->get_param('limit') ?? 10;

		$top_referrers = JVB()->referrals()->getTopReferrers($limit, $period);

		return new WP_REST_Response([
			'success' => true,
			'period' => $period,
			'referrers' => $top_referrers
		]);
	}

	/**
	 * Get referral settings
	 */
	public function getSettings(WP_REST_Request $request): WP_REST_Response
	{
		$settings = get_option(BASE . 'referral_settings', []);

		return new WP_REST_Response([
			'success' => true,
			'settings' => $settings
		]);
	}

	/**
	 * Update referral settings
	 */
	public function updateSettings(WP_REST_Request $request): WP_REST_Response
	{
		$settings = [
			'referrer_reward_type' => $request->get_param('referrer_reward_type') ?? 'per_user',
			'referrer_reward_amount' => floatval($request->get_param('referrer_reward_amount') ?? 25),
			'referee_reward_type' => $request->get_param('referee_reward_type') ?? 'percentage',
			'referee_reward_amount' => floatval($request->get_param('referee_reward_amount') ?? 20),
			'referee_reward_applies_to' => $request->get_param('referee_reward_applies_to') ?? 'first_order'
		];

		update_option(BASE . 'referral_settings', $settings);

		return new WP_REST_Response([
			'success' => true,
			'message' => 'Settings updated successfully',
			'settings' => $settings
		]);
	}

	/**
	 * Send a single referral invitation
	 *
	 * @param WP_REST_Request $request
	 * @return WP_REST_Response
	 */
	public function sendInvitation(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$email = sanitize_email($request->get_param('email'));
		$name = sanitize_text_field($request->get_param('name'));

		// Send invitation via ReferralManager
		$referral_manager = JVB()->referrals();
		$result = $referral_manager->sendReferralInvitation($user_id, $email, $name);

		if (is_wp_error($result)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => $result->get_error_message(),
				'code' => $result->get_error_code()
			], 400);
		}

		return new WP_REST_Response($result, 200);
	}

	/**
	 * Send batch referral invitations
	 *
	 * @param WP_REST_Request $request
	 * @return WP_REST_Response
	 */
	public function sendBatchInvitations(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$invitations = $request->get_param('invitations');

		// Validate invitation format
		foreach ($invitations as $invite) {
			if (empty($invite['email']) || empty($invite['name'])) {
				return new WP_REST_Response([
					'success' => false,
					'message' => 'Each invitation must have email and name'
				], 400);
			}
		}

		// Send batch via ReferralManager
		$referral_manager = JVB()->referrals();
		$result = $referral_manager->sendBatchReferralInvitations($user_id, $invitations);

		return new WP_REST_Response($result, 200);
	}

	/**
	 * Get invitation stats for current user
	 *
	 * @param WP_REST_Request $request
	 * @return WP_REST_Response
	 */
	public function getInvitationStats(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();

		$referral_manager = JVB()->referrals();
		$stats = $referral_manager->getUserInvitationStats($user_id);

		return new WP_REST_Response([
			'success' => true,
			'stats' => $stats
		], 200);
	}

	/**
	 * Export referrals for Jane App cross-reference
	 * Admin only
	 *
	 * @param WP_REST_Request $request
	 * @return WP_REST_Response
	 */
	public function exportReferrals(WP_REST_Request $request): WP_REST_Response
	{
		$start_date = sanitize_text_field($request->get_param('start_date'));
		$end_date = sanitize_text_field($request->get_param('end_date'));

		$referral_manager = JVB()->referrals();
		$csv_content = $referral_manager->exportReferrals($start_date, $end_date);

		// Return CSV for download
		return new WP_REST_Response([
			'success' => true,
			'csv' => $csv_content,
			'filename' => sprintf('referrals_%s_to_%s.csv', $start_date, $end_date)
		], 200);
	}

	public function registerWithReferral(WP_REST_Request $request): WP_REST_Response
	{
		$name = sanitize_text_field($request->get_param('name'));
		$email = sanitize_email($request->get_param('email'));
		$code = strtoupper(sanitize_text_field($request->get_param('code')));

		// Validate email
		if (!is_email($email)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Invalid email address'
			], 400);
		}

		// Check if user exists
		if (email_exists($email)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'An account with this email already exists'
			], 400);
		}

		// Validate referral code
		$referral_manager = JVB()->referrals();
		$referrer = $referral_manager->getUserByReferralCode($code);

		if (!$referrer) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Invalid referral code'
			], 404);
		}

		// Get reward text
		$settings = $referral_manager->getRewardSettings();
		$reward_amount = $settings['referee_reward_amount'] ?? 20;
		$reward_type = $settings['referee_reward_type'] ?? 'percentage';
		$reward_text = $reward_type === 'percentage'
			? "{$reward_amount}% off your first treatment!"
			: "\${$reward_amount} off your first treatment!";

		// Send magic link with referral context via MagicLinkManager
		$magic_link_manager = new \JVBase\managers\MagicLinkManager();

		$result = $magic_link_manager->sendMagicLink(
			$email,
			\JVBase\managers\MagicLinkManager::TYPE_REFERRAL,
			[
				'name' => $name,
				'referral_code' => $code,
				'referrer_id' => $referrer->ID,
				'referrer_name' => $referrer->display_name,
				'reward_text' => $reward_text
			]
		);

		if (is_wp_error($result)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Failed to send registration link. Please try again.'
			], 500);
		}

		return new WP_REST_Response([
			'success' => true,
			'message' => 'Check your email! We sent you a link to complete your registration.',
			'email' => $email
		], 200);
	}

	public function checkReferralCode(WP_REST_Request $request): WP_REST_Response
	{
		$code = strtoupper(sanitize_text_field($request->get_param('code')));

		if (empty($code)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Code is required'
			], 400);
		}

		$referral_manager = JVB()->referrals();
		$referrer = $referral_manager->getUserByReferralCode($code);

		if (!$referrer) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Invalid referral code'
			], 404);
		}
		if (is_user_logged_in() && get_current_user_id() === $referrer->ID) {
			return $this->error('You cannot use your own referral code', 'self_referral', 400);
		}

		// Return basic referrer info (no sensitive data)
		return new WP_REST_Response([
			'success' => true,
			'code' => $code,
			'referrer_name' => $referrer->display_name,
		], 200);
	}

	public function addReferralCodePostRegistration(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$code = $request->get_param('code');

		// Check if user already has a referral (can't change)
		$existing = JVB()->referrals()->getReferralByReferee($user_id);
		if ($existing) {
			return $this->error('You already have a referral code applied', 'already_referred', 400);
		}

		// Validate the code exists
		$referrer = JVB()->referrals()->getUserByReferralCode($code);
		if (!$referrer) {
			return $this->error('Invalid referral code', 'invalid_code', 400);
		}

		// Create the referral
		$user = wp_get_current_user();
		$result = JVB()->referrals()->createReferral($referrer->ID, $user_id, $code);

		if ($result) {
			return $this->success([
				'message' => 'Referral code applied successfully!',
				'referrer_name' => $referrer->display_name
			]);
		}

		return $this->error('Failed to apply referral code', 'creation_failed', 500);
	}

	/**********************************
	 * ADDITIONAL
	 */
	/**
	 * Handle client CSV upload
	 */
	public function handleClientUpload(WP_REST_Request $request): WP_REST_Response
	{
		$files = $request->get_file_params();

		if (!isset($files['file'])) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'No file uploaded'
			], 400);
		}

		$file = $files['file'];

		// Validate file type
		$allowed_types = ['text/csv', 'application/vnd.ms-excel', 'text/plain'];
		if (!in_array($file['type'], $allowed_types)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'File must be a CSV'
			], 400);
		}

		// Validate file size (10MB max)
		if ($file['size'] > 10 * 1024 * 1024) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'File size exceeds 10MB limit'
			], 400);
		}

		// Import using JaneAppClientImporter
		$importer = new JaneAppClientImporter();
		$default_role = get_option(BASE . 'client_import_role', JVB_USER);

		$options = [
			'update_existing' => true,
			'send_welcome_email' => false,
			'create_users' => true,
			'default_role' => $default_role
		];

		$result = $importer->importFromCSV($file['tmp_name'], $options);

		if (is_wp_error($result)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => $result->get_error_message()
			], 500);
		}

		// Build detailed message
		$message = sprintf(
			'Import complete: %d created, %d updated, %d skipped',
			$result['created'],
			$result['updated'],
			$result['skipped']
		);

		$details = [];
		if (!empty($result['skipped_details'])) {
			$details = $result['skipped_details'];
		}

		// Clear cache
		$this->cache->clear();

		return new WP_REST_Response([
			'success' => true,
			'message' => $message,
			'stats' => $result,
			'skipped_details' => $details
		]);
	}

	/**
	 * Handle sales CSV upload
	 */
	public function handleSalesUpload(WP_REST_Request $request): WP_REST_Response
	{
		$files = $request->get_file_params();

		if (!isset($files['file'])) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'No file uploaded'
			], 400);
		}

		$file = $files['file'];

		// Validate file type
		$allowed_types = ['text/csv', 'application/vnd.ms-excel', 'text/plain'];
		if (!in_array($file['type'], $allowed_types)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'File must be a CSV'
			], 400);
		}

		// Validate file size (10MB max)
		if ($file['size'] > 10 * 1024 * 1024) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'File size exceeds 10MB limit'
			], 400);
		}

		// Import using JaneSalesImporter
		$importer = new JaneSalesImporter();
		$options = [
			'skip_existing' => true
		];

		$result = $importer->importFromCSV($file['tmp_name'], $options);

		if (is_wp_error($result)) {
			return new WP_REST_Response([
				'success' => false,
				'message' => $result->get_error_message()
			], 500);
		}

		// Clear cache
		$this->cache->clear();

		return new WP_REST_Response([
			'success' => true,
			'message' => 'Sales imported successfully',
			'stats' => $result
		]);
	}

	/**
	 * Get referrals list for table display
	 */
	public function getReferralsList(WP_REST_Request $request): WP_REST_Response
	{
		$page = $request->get_param('page') ?: 1;
		$per_page = $request->get_param('per_page') ?: 20;
		$orderby = $request->get_param('orderby') ?: 'referred_at';
		$order = strtoupper($request->get_param('order')) === 'ASC' ? 'ASC' : 'DESC';
		$status = $request->get_param('status') ?: '';
		$search = $request->get_param('search') ?: '';

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

		// Build WHERE clause
		$where_clauses = [];
		$where_params = [];

		if (!empty($status)) {
			$where_clauses[] = "r.status = %s";
			$where_params[] = $status;
		}

		if (!empty($search)) {
			$where_clauses[] = "(r.referee_name LIKE %s OR r.referee_email LIKE %s OR referrer.display_name LIKE %s)";
			$search_term = '%' . $this->wpdb->esc_like($search) . '%';
			$where_params[] = $search_term;
			$where_params[] = $search_term;
			$where_params[] = $search_term;
		}

		$where = !empty($where_clauses) ? ' WHERE ' . implode(' AND ', $where_clauses) : '';

		// Sanitize orderby to prevent SQL injection
		$allowed_orderby = ['referred_at', 'consulted_at', 'treated_at', 'status', 'referee_name', 'referrer_name'];
		if (!in_array($orderby, $allowed_orderby)) {
			$orderby = 'referred_at';
		}

		// Get referrals with user info
		$query = "SELECT
			r.*,
			referrer.display_name as referrer_name,
			referrer.user_email as referrer_email,
			referee.display_name as referee_display_name,
			referee.user_email as referee_display_email,
			(SELECT COUNT(*) FROM {$this->referrals_table} WHERE referrer_id = r.referrer_id) as referrer_total_referrals,
			(SELECT SUM(amount) FROM {$this->rewards_table} WHERE user_id = r.referrer_id AND status = 'available') as referrer_available_rewards
		FROM {$this->referrals_table} r
		LEFT JOIN {$this->wpdb->users} referrer ON r.referrer_id = referrer.ID
		LEFT JOIN {$this->wpdb->users} referee ON r.referee_id = referee.ID
		{$where}
		ORDER BY {$orderby} {$order}
		LIMIT %d OFFSET %d";

		$where_params[] = $per_page;
		$where_params[] = $offset;

		$prepared_query = $this->wpdb->prepare($query, $where_params);
		$referrals = $this->wpdb->get_results($prepared_query);

		// Get total count
		$count_query = "SELECT COUNT(*) FROM {$this->referrals_table} r
			LEFT JOIN {$this->wpdb->users} referrer ON r.referrer_id = referrer.ID
			{$where}";

		$total = $this->wpdb->get_var(
			!empty($where_params) && count($where_params) > 2 ?
				$this->wpdb->prepare($count_query, array_slice($where_params, 0, -2)) :
				$count_query
		);

		return new WP_REST_Response([
			'success' => true,
			'referrals' => $referrals,
			'total' => (int)$total,
			'page' => (int)$page,
			'per_page' => (int)$per_page,
			'total_pages' => ceil($total / $per_page)
		]);
	}

	/**
	 * Get details for a specific referral
	 */
	public function getReferralDetails(WP_REST_Request $request): WP_REST_Response
	{
		$referral_id = $request->get_param('id');

		$referral = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT r.*,
				referrer.display_name as referrer_name,
				referee.display_name as referee_display_name
			FROM {$this->referrals_table} r
			LEFT JOIN {$this->wpdb->users} referrer ON r.referrer_id = referrer.ID
			LEFT JOIN {$this->wpdb->users} referee ON r.referee_id = referee.ID
			WHERE r.id = %d",
			$referral_id
		));

		if (!$referral) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Referral not found'
			], 404);
		}

		// Get associated treatments
		$treatments = $this->wpdb->get_results($this->wpdb->prepare(
			"SELECT * FROM {$this->treatments_table} WHERE referral_id = %d ORDER BY treatment_date DESC",
			$referral_id
		));

		// Get associated rewards
		$rewards = $this->wpdb->get_results($this->wpdb->prepare(
			"SELECT * FROM {$this->rewards_table} WHERE referral_id = %d",
			$referral_id
		));

		return new WP_REST_Response([
			'success' => true,
			'referral' => $referral,
			'treatments' => $treatments,
			'rewards' => $rewards
		]);
	}

	/**
	 * Handle manual mark as consulted
	 */
	public function handleMarkConsulted(WP_REST_Request $request): WP_REST_Response
	{
		$referral_id = $request->get_param('referral_id');

		if (!$referral_id) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Referral ID required'
			], 400);
		}

		$referral = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT * FROM {$this->referrals_table} WHERE id = %d",
			$referral_id
		));

		if (!$referral) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Referral not found'
			], 404);
		}

		if ($referral->status !== 'pending') {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Referral is not pending'
			], 400);
		}

		// Update to consulted
		$this->wpdb->update(
			$this->referrals_table,
			[
				'status' => 'consulted',
				'consulted_at' => current_time('mysql')
			],
			['id' => $referral_id],
			['%s', '%s'],
			['%d']
		);

		// Create consultation reward (20% off)
		$this->wpdb->insert(
			$this->rewards_table,
			[
				'referral_id' => $referral_id,
				'user_id' => $referral->referee_id,
				'reward_type' => 'referee',
				'amount' => 20,
				'reward_calculation' => 'percentage',
				'status' => 'available',
				'created_at' => current_time('mysql'),
				'notes' => 'Consultation reward - 20% off first treatment'
			],
			['%d', '%d', '%s', '%f', '%s', '%s', '%s', '%s']
		);

		// Clear cache
		$this->cache->clear();

		return new WP_REST_Response([
			'success' => true,
			'message' => 'Marked as consulted and reward created'
		]);
	}

	/**
	 * Handle manual mark as treated
	 */
	public function handleMarkTreated(WP_REST_Request $request): WP_REST_Response
	{
		$referral_id = $request->get_param('referral_id');

		if (!$referral_id) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Referral ID required'
			], 400);
		}

		$referral = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT * FROM {$this->referrals_table} WHERE id = %d",
			$referral_id
		));

		if (!$referral) {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Referral not found'
			], 404);
		}

		if ($referral->status === 'treated') {
			return new WP_REST_Response([
				'success' => false,
				'message' => 'Referral already marked as treated'
			], 400);
		}

		// Update to treated
		$this->wpdb->update(
			$this->referrals_table,
			[
				'status' => 'treated',
				'treated_at' => current_time('mysql'),
				'treatment_count' => ($referral->treatment_count ?? 0) + 1
			],
			['id' => $referral_id],
			['%s', '%s', '%d'],
			['%d']
		);

		// Create full rewards for both parties
		$settings = JVB()->referrals()->getRewardSettings();

		// Referrer reward
		$this->wpdb->insert(
			$this->rewards_table,
			[
				'referral_id' => $referral_id,
				'user_id' => $referral->referrer_id,
				'reward_type' => 'referrer',
				'amount' => $settings['referrer_reward_amount'],
				'reward_calculation' => $settings['referrer_reward_type'],
				'status' => 'available',
				'created_at' => current_time('mysql'),
				'notes' => 'Referral reward for completed treatment'
			],
			['%d', '%d', '%s', '%f', '%s', '%s', '%s', '%s']
		);

		// Referee reward
		$this->wpdb->insert(
			$this->rewards_table,
			[
				'referral_id' => $referral_id,
				'user_id' => $referral->referee_id,
				'reward_type' => 'referee',
				'amount' => $settings['referee_reward_amount'],
				'reward_calculation' => $settings['referee_reward_type'],
				'status' => 'available',
				'created_at' => current_time('mysql'),
				'notes' => 'Treatment completion reward'
			],
			['%d', '%d', '%s', '%f', '%s', '%s', '%s', '%s']
		);

		// Clear cache
		$this->cache->clear();

		return new WP_REST_Response([
			'success' => true,
			'message' => 'Marked as treated and rewards created'
		]);
	}

	/**
	 * Get current user's referral stats
	 */
	public function getMyStats(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$stats = JVB()->referrals()->getUserStats($user_id);

		return new WP_REST_Response([
			'success' => true,
			'stats' => $stats
		]);
	}

	/**
	 * Get current user's referrals
	 */
	public function getMyReferrals(WP_REST_Request $request): WP_REST_Response
	{
		$user_id = get_current_user_id();
		$limit = $request->get_param('limit') ?: 20;

		$referrals = JVB()->referrals()->getUserReferrals($user_id, ['limit' => $limit]);

		return new WP_REST_Response([
			'success' => true,
			'referrals' => $referrals
		]);
	}
}