<?php
/**
 * Data Mapper Class
 *
 * Handles ID mapping during import operations - mapping old IDs to new IDs
 * and resolving user associations across different WordPress installations.
 *
 * @package    Karate_Club_Manager
 * @subpackage Karate_Club_Manager/includes/classes
 * @since      1.0.271
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

/**
 * MACM Data Mapper Class
 *
 * Manages ID mapping for import operations, tracking old-to-new ID relationships
 * and handling user ID resolution based on selected mapping mode.
 *
 * @since 1.0.271
 */
class MACM_Data_Mapper {

	/**
	 * User mapping mode: Match users by email address.
	 *
	 * When importing data, finds existing WordPress users by matching
	 * the email address from the import file. If no match is found,
	 * assigns to the current logged-in administrator.
	 *
	 * @since 1.0.271
	 * @var string
	 */
	const MODE_MATCH_EMAIL = 'match_email';

	/**
	 * User mapping mode: Assign all to current user.
	 *
	 * Assigns all imported member records to the currently logged-in
	 * WordPress user, regardless of email addresses in the import file.
	 *
	 * @since 1.0.271
	 * @var string
	 */
	const MODE_ASSIGN_CURRENT = 'assign_current';

	/**
	 * User mapping mode: Skip unmatched users.
	 *
	 * Only imports member records where the email address matches an
	 * existing WordPress user. Records with unmatched emails are skipped.
	 *
	 * @since 1.0.271
	 * @var string
	 */
	const MODE_SKIP_UNMATCHED = 'skip_unmatched';

	/**
	 * Current user mapping mode.
	 *
	 * @since 1.0.271
	 * @var string
	 */
	private $user_mapping_mode;

	/**
	 * Cache of email to user ID mappings.
	 *
	 * @since 1.0.271
	 * @var array
	 */
	private $email_to_user_cache = array();

	/**
	 * Entity ID mapping storage.
	 * Structure: [ entity_type => [ old_id => new_id ] ]
	 *
	 * @since 1.0.271
	 * @var array
	 */
	private $entity_maps = array();

	/**
	 * User ID mapping storage.
	 * Structure: [ old_user_id => new_user_id ]
	 *
	 * @since 1.0.271
	 * @var array
	 */
	private $user_map = array();

	/**
	 * Skipped user IDs (unmatched in skip mode).
	 *
	 * @since 1.0.271
	 * @var array
	 */
	private $skipped_users = array();

	/**
	 * Constructor.
	 *
	 * @since 1.0.271
	 * @param string $user_mapping_mode User mapping mode (match_email, assign_current, skip_unmatched).
	 */
	public function __construct( $user_mapping_mode = self::MODE_MATCH_EMAIL ) {
		$valid_modes = array(
			self::MODE_MATCH_EMAIL,
			self::MODE_ASSIGN_CURRENT,
			self::MODE_SKIP_UNMATCHED,
		);

		$this->user_mapping_mode = in_array( $user_mapping_mode, $valid_modes, true )
			? $user_mapping_mode
			: self::MODE_MATCH_EMAIL;
	}

	/**
	 * Map a user ID from the import to a local user ID.
	 *
	 * Resolves user associations based on the selected mapping mode:
	 * - MATCH_EMAIL: Find existing user by email, return 0 if not found
	 * - ASSIGN_CURRENT: Always return current logged-in user ID
	 * - SKIP_UNMATCHED: Find by email, return null if not found (signal to skip)
	 *
	 * @since 1.0.271
	 * @param int    $old_user_id Original user ID from the export.
	 * @param string $email       User email address for matching.
	 * @return int|null New user ID, 0 if unassigned, or null if should skip.
	 */
	public function map_user_id( $old_user_id, $email ) {
		// Check if we already mapped this user.
		if ( isset( $this->user_map[ $old_user_id ] ) ) {
			return $this->user_map[ $old_user_id ];
		}

		// Check if this user was marked to skip.
		if ( in_array( $old_user_id, $this->skipped_users, true ) ) {
			return null;
		}

		$new_user_id = null;

		switch ( $this->user_mapping_mode ) {
			case self::MODE_ASSIGN_CURRENT:
				$new_user_id = get_current_user_id();
				break;

			case self::MODE_MATCH_EMAIL:
				$new_user_id = $this->find_user_by_email( $email );
				if ( 0 === $new_user_id ) {
					// User not found, assign to current admin.
					$new_user_id = get_current_user_id();
				}
				break;

			case self::MODE_SKIP_UNMATCHED:
				$new_user_id = $this->find_user_by_email( $email );
				if ( 0 === $new_user_id ) {
					// User not found, mark to skip.
					$this->skipped_users[] = $old_user_id;
					return null;
				}
				break;
		}

		// Cache the mapping.
		$this->user_map[ $old_user_id ] = $new_user_id;

		return $new_user_id;
	}

	/**
	 * Find a WordPress user by email address.
	 *
	 * Uses internal cache to avoid repeated database queries.
	 *
	 * @since 1.0.271
	 * @param string $email Email address to search.
	 * @return int User ID if found, 0 if not found.
	 */
	private function find_user_by_email( $email ) {
		$email = sanitize_email( $email );

		if ( empty( $email ) ) {
			return 0;
		}

		// Check cache first.
		if ( isset( $this->email_to_user_cache[ $email ] ) ) {
			return $this->email_to_user_cache[ $email ];
		}

		// Query for user.
		$user = get_user_by( 'email', $email );
		$user_id = $user ? $user->ID : 0;

		// Cache result.
		$this->email_to_user_cache[ $email ] = $user_id;

		return $user_id;
	}

	/**
	 * Pre-populate the email cache with multiple emails.
	 *
	 * Useful for batch processing to minimize database queries.
	 *
	 * @since 1.0.271
	 * @since 1.0.297 Fixed SQL to use literal placeholders instead of interpolated variable.
	 * @param array $emails Array of email addresses.
	 */
	public function preload_user_emails( $emails ) {
		global $wpdb;

		// Filter out already cached emails.
		$uncached_emails = array_diff( $emails, array_keys( $this->email_to_user_cache ) );

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

		// Sanitize emails.
		$uncached_emails = array_map( 'sanitize_email', $uncached_emails );
		$uncached_emails = array_filter( $uncached_emails );

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

		// Re-index array after filtering to ensure consecutive keys for spread operator.
		$uncached_emails = array_values( $uncached_emails );

		// Query all users at once using helper method with literal SQL.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $this->execute_user_email_query( $wpdb, $uncached_emails );

		// Populate cache with found users.
		$found_emails = array();
		if ( $results ) {
			foreach ( $results as $row ) {
				$this->email_to_user_cache[ $row->user_email ] = (int) $row->ID;
				$found_emails[] = $row->user_email;
			}
		}

		// Mark unfound emails as 0.
		$unfound_emails = array_diff( $uncached_emails, $found_emails );
		foreach ( $unfound_emails as $email ) {
			$this->email_to_user_cache[ $email ] = 0;
		}
	}

	/**
	 * Record an entity ID mapping (old ID to new ID).
	 *
	 * Used during import to track relationships between original IDs
	 * and newly created IDs in the local database.
	 *
	 * @since 1.0.271
	 * @param string $entity Entity type (e.g., 'members', 'classes').
	 * @param int    $old_id Original ID from the export.
	 * @param int    $new_id New ID in the local database.
	 */
	public function map_entity_id( $entity, $old_id, $new_id ) {
		if ( ! isset( $this->entity_maps[ $entity ] ) ) {
			$this->entity_maps[ $entity ] = array();
		}

		$this->entity_maps[ $entity ][ $old_id ] = $new_id;
	}

	/**
	 * Get a mapped entity ID.
	 *
	 * Retrieves the new local ID for a given entity type and old ID.
	 *
	 * @since 1.0.271
	 * @param string $entity Entity type (e.g., 'members', 'classes').
	 * @param int    $old_id Original ID from the export.
	 * @return int|null New ID if mapped, null if not found.
	 */
	public function get_mapped_id( $entity, $old_id ) {
		if ( ! isset( $this->entity_maps[ $entity ][ $old_id ] ) ) {
			return null;
		}

		return $this->entity_maps[ $entity ][ $old_id ];
	}

	/**
	 * Check if an entity ID has been mapped.
	 *
	 * @since 1.0.271
	 * @param string $entity Entity type.
	 * @param int    $old_id Original ID.
	 * @return bool True if mapped, false otherwise.
	 */
	public function has_mapped_id( $entity, $old_id ) {
		return isset( $this->entity_maps[ $entity ][ $old_id ] );
	}

	/**
	 * Get all mappings for an entity type.
	 *
	 * @since 1.0.271
	 * @param string $entity Entity type.
	 * @return array Array of old_id => new_id mappings.
	 */
	public function get_entity_mappings( $entity ) {
		return $this->entity_maps[ $entity ] ?? array();
	}

	/**
	 * Get all user mappings.
	 *
	 * @since 1.0.271
	 * @return array Array of old_user_id => new_user_id mappings.
	 */
	public function get_user_mappings() {
		return $this->user_map;
	}

	/**
	 * Get list of skipped user IDs.
	 *
	 * @since 1.0.271
	 * @return array Array of old user IDs that were skipped.
	 */
	public function get_skipped_users() {
		return $this->skipped_users;
	}

	/**
	 * Get the current user mapping mode.
	 *
	 * @since 1.0.271
	 * @return string The mapping mode constant.
	 */
	public function get_mapping_mode() {
		return $this->user_mapping_mode;
	}

	/**
	 * Get mapping statistics.
	 *
	 * Returns a summary of all mappings performed.
	 *
	 * @since 1.0.271
	 * @return array Statistics array.
	 */
	public function get_statistics() {
		$stats = array(
			'user_mappings'    => count( $this->user_map ),
			'skipped_users'    => count( $this->skipped_users ),
			'entity_mappings'  => array(),
			'total_entities'   => 0,
		);

		foreach ( $this->entity_maps as $entity => $mappings ) {
			$count = count( $mappings );
			$stats['entity_mappings'][ $entity ] = $count;
			$stats['total_entities'] += $count;
		}

		return $stats;
	}

	/**
	 * Clear all mappings.
	 *
	 * Resets the mapper to initial state. Useful for testing or batch imports.
	 *
	 * @since 1.0.271
	 */
	public function clear() {
		$this->entity_maps        = array();
		$this->user_map           = array();
		$this->skipped_users      = array();
		$this->email_to_user_cache = array();
	}

	/**
	 * Find entity by unique key.
	 *
	 * Checks if an entity already exists in the database by its unique identifier.
	 * Used for conflict resolution during import.
	 *
	 * @since 1.0.271
	 * @since 1.0.272 Added whitelist validation for key_field to prevent SQL injection.
	 * @since 1.0.290 Refactored to use helper method with literal SQL for Plugin Check compliance.
	 * @param string $entity    Entity type.
	 * @param string $key_field Field name that acts as unique key.
	 * @param mixed  $key_value Value to search for.
	 * @return int|null Existing entity ID or null if not found.
	 */
	public function find_existing_entity( $entity, $key_field, $key_value ) {
		global $wpdb;

		// Map entity to table name and allowed key fields.
		$entity_config = array(
			'belt_colors'      => array(
				'table'  => 'macm_belt_colors',
				'fields' => array( 'color_key', 'color_name' ),
			),
			'membership_types' => array(
				'table'  => 'macm_membership_types',
				'fields' => array( 'type_name' ),
			),
			'members'          => array(
				'table'  => 'macm_members',
				'fields' => array( 'full_name', 'license_number' ),
			),
			'locations'        => array(
				'table'  => 'macm_locations',
				'fields' => array( 'location_name' ),
			),
			'groups'           => array(
				'table'  => 'macm_groups',
				'fields' => array( 'group_name' ),
			),
			'clubs'            => array(
				'table'  => 'macm_clubs',
				'fields' => array( 'club_name' ),
			),
			'instructors'      => array(
				'table'  => 'macm_instructors',
				'fields' => array( 'full_name', 'email' ),
			),
			'classes'          => array(
				'table'  => 'macm_classes',
				'fields' => array( 'class_name' ),
			),
			'events'           => array(
				'table'  => 'macm_events',
				'fields' => array( 'title' ),
			),
		);

		// Validate entity exists in config.
		if ( ! isset( $entity_config[ $entity ] ) ) {
			return null;
		}

		// Validate key_field is in the whitelist for this entity.
		$config = $entity_config[ $entity ];
		if ( ! in_array( $key_field, $config['fields'], true ) ) {
			return null;
		}

		// Use helper method with literal SQL strings to avoid interpolation warnings.
		return $this->execute_entity_lookup_query( $wpdb, $entity, $key_field, $key_value );
	}

	/**
	 * Execute entity lookup query with literal SQL strings.
	 *
	 * Uses a switch statement with literal SQL strings to avoid WordPress Plugin Check
	 * warnings about interpolated variables in prepared statements. Each case uses
	 * hardcoded table and column names with %i placeholder for table prefix.
	 *
	 * @since 1.0.290
	 * @param wpdb   $wpdb      WordPress database object.
	 * @param string $entity    Validated entity type.
	 * @param string $key_field Validated field name.
	 * @param mixed  $key_value Value to search for.
	 * @return int|null Entity ID if found, null otherwise.
	 */
	private function execute_entity_lookup_query( $wpdb, $entity, $key_field, $key_value ) {
		$lookup_key = $entity . '_' . $key_field;
		$id         = null;

		switch ( $lookup_key ) {
			case 'belt_colors_color_key':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE color_key = %s LIMIT 1',
						$wpdb->prefix . 'macm_belt_colors',
						$key_value
					)
				);
				break;

			case 'belt_colors_color_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE color_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_belt_colors',
						$key_value
					)
				);
				break;

			case 'membership_types_type_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE type_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_membership_types',
						$key_value
					)
				);
				break;

			case 'members_full_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE full_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_members',
						$key_value
					)
				);
				break;

			case 'members_license_number':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE license_number = %s LIMIT 1',
						$wpdb->prefix . 'macm_members',
						$key_value
					)
				);
				break;

			case 'locations_location_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE location_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_locations',
						$key_value
					)
				);
				break;

			case 'groups_group_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE group_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_groups',
						$key_value
					)
				);
				break;

			case 'clubs_club_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE club_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_clubs',
						$key_value
					)
				);
				break;

			case 'instructors_full_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE full_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_instructors',
						$key_value
					)
				);
				break;

			case 'instructors_email':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE email = %s LIMIT 1',
						$wpdb->prefix . 'macm_instructors',
						$key_value
					)
				);
				break;

			case 'classes_class_name':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE class_name = %s LIMIT 1',
						$wpdb->prefix . 'macm_classes',
						$key_value
					)
				);
				break;

			case 'events_title':
				$id = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT id FROM %i WHERE title = %s LIMIT 1',
						$wpdb->prefix . 'macm_events',
						$key_value
					)
				);
				break;

			default:
				// Invalid combination (should never reach here due to validation above).
				return null;
		}

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

	/**
	 * Execute user email query with literal SQL.
	 *
	 * Uses a switch statement with literal SQL strings based on the number of emails
	 * to avoid WordPress Plugin Check warnings about variable interpolation.
	 * For larger sets, batches queries into groups of 10 to maintain compliance.
	 *
	 * @since 1.0.297
	 * @since 1.0.303 Extended switch to 10 cases, batch larger sets for WP Plugin Check compliance.
	 * @param wpdb  $wpdb   WordPress database object.
	 * @param array $emails Array of sanitized email addresses.
	 * @return array|null Query results.
	 */
	private function execute_user_email_query( $wpdb, $emails ) {
		$count = count( $emails );

		// Handle counts 1-10 with literal SQL for static analysis compliance.
		switch ( $count ) {
			case 1:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s)',
						$emails[0]
					)
				);

			case 2:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s)',
						$emails[0],
						$emails[1]
					)
				);

			case 3:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2]
					)
				);

			case 4:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2],
						$emails[3]
					)
				);

			case 5:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2],
						$emails[3],
						$emails[4]
					)
				);

			case 6:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s, %s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2],
						$emails[3],
						$emails[4],
						$emails[5]
					)
				);

			case 7:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s, %s, %s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2],
						$emails[3],
						$emails[4],
						$emails[5],
						$emails[6]
					)
				);

			case 8:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s, %s, %s, %s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2],
						$emails[3],
						$emails[4],
						$emails[5],
						$emails[6],
						$emails[7]
					)
				);

			case 9:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s, %s, %s, %s, %s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2],
						$emails[3],
						$emails[4],
						$emails[5],
						$emails[6],
						$emails[7],
						$emails[8]
					)
				);

			case 10:
				return $wpdb->get_results(
					$wpdb->prepare(
						'SELECT ID, user_email FROM ' . $wpdb->users . ' WHERE user_email IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
						$emails[0],
						$emails[1],
						$emails[2],
						$emails[3],
						$emails[4],
						$emails[5],
						$emails[6],
						$emails[7],
						$emails[8],
						$emails[9]
					)
				);

			default:
				// For larger sets, batch into groups of 10 and merge results.
				return $this->batch_user_email_query( $wpdb, $emails );
		}
	}

	/**
	 * Batch user email query for large sets.
	 *
	 * Splits emails into batches of 10 to maintain WordPress Plugin Check compliance.
	 *
	 * @since 1.0.303
	 * @param wpdb  $wpdb   WordPress database object.
	 * @param array $emails Array of sanitized email addresses.
	 * @return array Query results merged from all batches.
	 */
	private function batch_user_email_query( $wpdb, $emails ) {
		$results = array();
		$batches = array_chunk( $emails, 10 );

		foreach ( $batches as $batch ) {
			$batch_results = $this->execute_user_email_query( $wpdb, $batch );
			if ( ! empty( $batch_results ) ) {
				array_push( $results, ...$batch_results );
			}
		}

		return $results;
	}
}
