<?php
namespace JVBase\importers;

use WP_Error;
use JVBase\managers\ReferralManager;

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

/**
 * JaneApp Sales Importer
 *
 * Imports sales/treatment data from JaneApp CSV exports and updates referral tracking
 */
class JaneAppSalesImporter
{
	protected $wpdb;
	protected string $jane_clients_table;
	protected string $referrals_table;
	protected string $treatments_table;
	protected string $rewards_table;
	protected ReferralManager $referral_manager;
	protected array $import_stats = [];
	protected array $skipped_details = [];

	// CSV column mapping
	protected array $column_map = [
		'patient_guid' => 'Patient Guid',
		'purchase_date' => 'Purchase Date',
		'item' => 'Item',
		'status' => 'Status',
		'invoice_number' => 'Invoice #',
		'total' => 'Total',
	];

	// Treatment types that qualify for rewards
	protected array $treatment_types = [
		'free_consult' => 'Free Consult',
		'tier_1' => 'Tier 1',
		'tier_2' => 'Tier 2',
		'tier_3' => 'Tier 3',
		'tier_4' => 'Tier 4',
		'tier_5' => 'Tier 5',
		'tier_6' => 'Tier 6',
		'brows' => 'Brows',
		'freckles' => 'Freckles',
		'test_snap' => 'Test Snap',
		'smp_half' => 'SMP - Half Head',
		'smp_full' => 'SMP - Full Head',
	];

	public function __construct()
	{
		global $wpdb;
		$this->wpdb = $wpdb;
		$this->jane_clients_table = $wpdb->prefix . BASE . 'jane_clients';
		$this->referrals_table = $wpdb->prefix . BASE . 'referrals';
		$this->treatments_table = $wpdb->prefix . BASE . 'referral_treatments';
		$this->rewards_table = $wpdb->prefix . BASE . 'referral_rewards';
		$this->referral_manager = new ReferralManager();
	}

	/**
	 * Import sales from CSV file
	 *
	 * @param string $file_path Path to the CSV file
	 * @param array $options Import options
	 * @return array|WP_Error Import results with stats and errors
	 */
	public function importFromCSV(string $file_path, array $options = []): array|WP_Error
	{
		// Initialize stats
		$this->import_stats = [
			'total_rows' => 0,
			'processed' => 0,
			'consultations' => 0,
			'treatments' => 0,
			'skipped' => 0,
			'errors' => [],
			'unmatched_guids' => [],
			'no_referral' => []
		];

		// Validate file exists
		if (!file_exists($file_path)) {
			return new WP_Error('file_not_found', 'CSV file not found');
		}

		// Parse options
		$skip_existing = $options['skip_existing'] ?? true;

		// Open and parse CSV
		$handle = fopen($file_path, 'r');
		if (!$handle) {
			return new WP_Error('file_open_error', 'Could not open CSV file');
		}

		// Get header row
		$headers = fgetcsv($handle);
		if (!$headers) {
			fclose($handle);
			return new WP_Error('invalid_csv', 'CSV file is empty or invalid');
		}

		// Trim whitespace from headers to handle inconsistent CSV formats
		$headers = array_map('trim', $headers);

		// Map column indices
		$column_indices = $this->mapColumnIndices($headers);
		if (is_wp_error($column_indices)) {
			fclose($handle);
			return $column_indices;
		}

		// Get Patient column index for error reporting
		$patient_name_index = array_search('Patient', $headers);

		// Start transaction for data integrity
		$this->wpdb->query('START TRANSACTION');

		try {
			// Process each row
			$row_number = 1; // Header is row 1, data starts at row 2
			while (($row = fgetcsv($handle)) !== false) {
				$row_number++;
				$this->import_stats['total_rows']++;

				$result = $this->processSalesRow($row, $column_indices, [
					'skip_existing' => $skip_existing
				]);

				if (is_wp_error($result)) {
					$error_data = [
						'row' => $row_number,
						'patient_guid' => trim($row[$column_indices['patient_guid']] ?? 'Unknown'),
						'patient_name' => trim($row[$patient_name_index] ?? 'Unknown'),
						'item' => trim($row[$column_indices['item']] ?? 'Unknown'),
						'reason' => $result->get_error_message()
					];

					$this->import_stats['errors'][] = $error_data;
					$this->import_stats['skipped']++;
				} else {
					$this->import_stats['processed']++;
					if ($result['type'] === 'consultation') {
						$this->import_stats['consultations']++;
					} elseif ($result['type'] === 'treatment') {
						$this->import_stats['treatments']++;
					}
				}
			}

			// Commit transaction
			$this->wpdb->query('COMMIT');

		} catch (\Exception $e) {
			// Rollback on error
			$this->wpdb->query('ROLLBACK');
			fclose($handle);
			return new WP_Error('import_error', 'Import failed: ' . $e->getMessage());
		}

		fclose($handle);

		return $this->import_stats;
	}

	/**
	 * Map CSV column headers to internal field names
	 *
	 * @param array $headers CSV header row
	 * @return array|WP_Error Column index mapping or error
	 */
	protected function mapColumnIndices(array $headers): array|WP_Error
	{
		$indices = [];

		foreach ($this->column_map as $field => $csv_column) {
			$index = array_search($csv_column, $headers);
			if ($index === false) {
				return new WP_Error(
					'missing_column',
					sprintf('Required column "%s" not found in CSV', $csv_column)
				);
			}
			$indices[$field] = $index;
		}

		return $indices;
	}

	/**
	 * Process a single sales row from CSV
	 *
	 * @param array $row CSV row data
	 * @param array $column_indices Column mapping
	 * @param array $options Processing options
	 * @return array|WP_Error Result of processing
	 */
	protected function processSalesRow(array $row, array $column_indices, array $options): array|WP_Error
	{
		// Extract data from row
		$patient_guid = trim($row[$column_indices['patient_guid']] ?? '');
		$purchase_date = trim($row[$column_indices['purchase_date']] ?? '');
		$item = trim($row[$column_indices['item']] ?? '');
		$status = trim($row[$column_indices['status']] ?? '');
		$invoice_number = trim($row[$column_indices['invoice_number']] ?? '');
		$total = trim($row[$column_indices['total']] ?? 0);

		// Validate required fields
		if (empty($patient_guid) || empty($purchase_date) || empty($item)) {
			return new WP_Error('invalid_data', 'Missing required fields');
		}

		// Skip no-shows and cancellations
		if ($this->shouldSkipItem($item, $status)) {
			return new WP_Error('skipped_status', 'No show or cancelled appointment');
		}

		// Get user ID from patient GUID
		$user_id = $this->getUserIdByGuid($patient_guid);
		if (!$user_id) {
			$this->import_stats['unmatched_guids'][] = $patient_guid;
			return new WP_Error('user_not_found', 'User not found for patient GUID: ' . $patient_guid);
		}

		// Get referral record for this user
		$referral = $this->referral_manager->getReferralByReferee($user_id);
		if (!$referral) {
			$this->import_stats['no_referral'][] = [
				'patient_guid' => $patient_guid,
				'user_id' => $user_id
			];
			return new WP_Error('no_referral', 'User has no referral record');
		}

		// Parse date
		$treatment_date = date('Y-m-d H:i:s', strtotime($purchase_date));

		// Determine if this is a consultation or treatment
		$is_consultation = $this->isConsultation($item);

		if ($is_consultation) {
			return $this->processConsultation($referral, $user_id, $treatment_date, $invoice_number);
		} else {
			return $this->processTreatment($referral, $user_id, $item, $treatment_date, $invoice_number, $total, $options);
		}
	}

	/**
	 * Check if item should be skipped based on status or suffix
	 *
	 * @param string $item Item name
	 * @param string $status Item status
	 * @return bool True if should skip
	 */
	protected function shouldSkipItem(string $item, string $status): bool
	{
		// Check for no-show or cancelled suffix
		if (stripos($item, ' - No Show') !== false || stripos($item, ' - Cancelled') !== false) {
			return true;
		}

		// Check status if available
		if (!empty($status) && in_array(strtolower($status), ['no_show', 'cancelled', 'no show'])) {
			return true;
		}

		return false;
	}

	/**
	 * Check if item is a consultation
	 *
	 * @param string $item Item name
	 * @return bool True if consultation
	 */
	protected function isConsultation(string $item): bool
	{
		return stripos($item, 'Free Consult') !== false || stripos($item, 'Consultation') !== false;
	}

	/**
	 * Process a consultation
	 *
	 * @param object $referral Referral record
	 * @param int $user_id User ID
	 * @param string $treatment_date Date of consultation
	 * @param string $invoice_number Invoice number
	 * @return array Result
	 */
	protected function processConsultation(object $referral, int $user_id, string $treatment_date, string $invoice_number): array
	{
		// Update referral status to 'consulted' if still pending
		if ($referral->status === 'pending') {
			$this->wpdb->update(
				$this->referrals_table,
				[
					'status' => 'consulted',
					'consulted_at' => $treatment_date
				],
				['id' => $referral->id],
				['%s', '%s'],
				['%d']
			);

			// Create 50% off reward for referee
			$this->createConsultationReward($referral->id, $user_id);
		}

		// Record consultation in treatments table
		$this->insertTreatment([
			'referral_id' => $referral->id,
			'user_id' => $user_id,
			'treatment_type' => 'Free Consultation',
			'treatment_date' => $treatment_date,
			'invoice_number' => $invoice_number,
			'amount' => 0,
			'status' => 'completed'
		]);

		return [
			'type' => 'consultation',
			'referral_id' => $referral->id,
			'user_id' => $user_id
		];
	}

	/**
	 * Process a treatment
	 *
	 * @param object $referral Referral record
	 * @param int $user_id User ID
	 * @param string $treatment_type Treatment type
	 * @param string $treatment_date Date of treatment
	 * @param string $invoice_number Invoice number
	 * @param float $total Treatment cost
	 * @param array $options Processing options
	 * @return array Result
	 */
	protected function processTreatment(
		object $referral,
		int    $user_id,
		string $treatment_type,
		string $treatment_date,
		string $invoice_number,
		float  $total,
		array  $options
	): array
	{
		// Check if this treatment already exists (prevent duplicates)
		if ($options['skip_existing'] && $this->treatmentExists($referral->id, $invoice_number)) {
			return new WP_Error('duplicate_treatment', 'Treatment already imported');
		}

		// Insert treatment record
		$treatment_id = $this->insertTreatment([
			'referral_id' => $referral->id,
			'user_id' => $user_id,
			'treatment_type' => $treatment_type,
			'treatment_date' => $treatment_date,
			'invoice_number' => $invoice_number,
			'amount' => $total,
			'status' => 'completed'
		]);

		// Update referral counts and status
		$treatment_count = (int)$referral->treatment_count + 1;
		$update_data = [
			'treatment_count' => $treatment_count
		];

		// If this is the first treatment, mark as treated and set treated_at
		if ($referral->status !== 'treated') {
			$update_data['status'] = 'treated';
			$update_data['treated_at'] = $treatment_date;

			// Create full rewards for both referrer and referee
			$this->createTreatmentRewards($referral->id);
		}

		$this->wpdb->update(
			$this->referrals_table,
			$update_data,
			['id' => $referral->id],
			array_fill(0, count($update_data), '%s'),
			['%d']
		);

		return [
			'type' => 'treatment',
			'referral_id' => $referral->id,
			'user_id' => $user_id,
			'treatment_id' => $treatment_id
		];
	}

	/**
	 * Create consultation reward (50% off)
	 *
	 * @param int $referral_id Referral ID
	 * @param int $user_id User ID
	 */
	protected function createConsultationReward(int $referral_id, int $user_id): void
	{
		$this->wpdb->insert(
			$this->rewards_table,
			[
				'referral_id' => $referral_id,
				'user_id' => $user_id,
				'reward_type' => 'referee',
				'amount' => 50,
				'reward_calculation' => 'percentage',
				'status' => 'available',
				'created_at' => current_time('mysql'),
				'notes' => 'Consultation reward - 50% off first treatment'
			],
			['%d', '%d', '%s', '%f', '%s', '%s', '%s', '%s']
		);
	}

	/**
	 * Create treatment rewards for both referrer and referee
	 *
	 * @param int $referral_id Referral ID
	 */
	protected function createTreatmentRewards(int $referral_id): void
	{
		$referral = $this->wpdb->get_row($this->wpdb->prepare(
			"SELECT * FROM {$this->referrals_table} WHERE id = %d",
			$referral_id
		));

		if (!$referral) {
			return;
		}

		$settings = $this->referral_manager->getRewardSettings();

		// Create referrer reward (fixed amount)
		$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']
		);

		// Create referee reward (percentage or fixed)
		$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']
		);
	}

	/**
	 * Check if treatment already exists
	 *
	 * @param int $referral_id Referral ID
	 * @param string $invoice_number Invoice number
	 * @return bool True if exists
	 */
	protected function treatmentExists(int $referral_id, string $invoice_number): bool
	{
		$count = $this->wpdb->get_var($this->wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->treatments_table}
			WHERE referral_id = %d AND invoice_number = %s",
			$referral_id,
			$invoice_number
		));

		return (int)$count > 0;
	}

	/**
	 * Insert treatment record
	 *
	 * @param array $data Treatment data
	 * @return int|false Insert ID or false on failure
	 */
	protected function insertTreatment(array $data): int|false
	{
		$result = $this->wpdb->insert(
			$this->treatments_table,
			$data,
			['%d', '%d', '%s', '%s', '%s', '%f', '%s']
		);

		return $result ? $this->wpdb->insert_id : false;
	}

	/**
	 * Get user ID by patient GUID
	 *
	 * @param string $patient_guid Patient GUID
	 * @return int|null User ID or null if not found
	 */
	protected function getUserIdByGuid(string $patient_guid): ?int
	{
		$result = $this->wpdb->get_var($this->wpdb->prepare(
			"SELECT user_id FROM {$this->jane_clients_table} WHERE patient_guid = %s",
			$patient_guid
		));

		return $result ? (int)$result : null;
	}

	/**
	 * Get import statistics
	 *
	 * @return array Import statistics
	 */
	public function getImportStats(): array
	{
		return $this->import_stats;
	}

	/**
	 * Get treatment history for a referral
	 *
	 * @param int $referral_id Referral ID
	 * @return array Treatment records
	 */
	public function getTreatmentHistory(int $referral_id): array
	{
		return $this->wpdb->get_results($this->wpdb->prepare(
			"SELECT * FROM {$this->treatments_table}
			WHERE referral_id = %d
			ORDER BY treatment_date DESC",
			$referral_id
		));
	}

	/**
	 * Get treatment statistics for a user
	 *
	 * @param int $user_id User ID
	 * @return array Statistics
	 */
	public function getUserTreatmentStats(int $user_id): array
	{
		$referral = $this->referral_manager->getReferralByReferee($user_id);

		if (!$referral) {
			return [
				'total_treatments' => 0,
				'last_treatment' => null,
				'treatment_types' => []
			];
		}

		$treatments = $this->getTreatmentHistory($referral->id);

		$stats = [
			'total_treatments' => count($treatments),
			'last_treatment' => $treatments[0]->treatment_date ?? null,
			'treatment_types' => []
		];

		foreach ($treatments as $treatment) {
			$type = $treatment->treatment_type;
			if (!isset($stats['treatment_types'][$type])) {
				$stats['treatment_types'][$type] = 0;
			}
			$stats['treatment_types'][$type]++;
		}

		return $stats;
	}
}