<?php
/**
 *  This file is responsible for handling database calls to the custom field mapping tables in the WordPress database.
 *
 * @package object-data-sync-for-salesforce\Services
 */

namespace MoSfSyncSalesforce\Services;

use MoSfSyncSalesforce\Helper\Instance;
use MoSfSyncSalesforce\Helper\Plugin_Constants;

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

/**
 * This class is responsible for handling database calls to the custom field mapping tables in the WordPress database.
 */
class DB_Utils {
	use Instance;
	/**
	 *
	 * Global object of `$wpdb`, the WordPress database.
	 *
	 * @var object
	 */
	public $db;

	/**
	 *
	 * Name of the field mapping custom table.
	 *
	 * @var string
	 */
	public $table_name;

	/**
	 *
	 * Name of the field mapping custom meta table.
	 *
	 * @var string
	 */
	public $meta_table_name;

	/**
	 *
	 * Creates instance of the class.
	 *
	 * @return __CLASS__
	 */
	public static function instance() {
		global $wpdb;
		self::$instance                  = new self();
		self::$instance->db              = $wpdb;
		self::$instance->table_name      = 'mo_sf_sync_object_mapping';
		self::$instance->meta_table_name = 'mo_sf_sync_object_mapping_meta';
		return self::$instance;
	}

	/**
	 *
	 * Creates field mapping custom table and custom meta in the database.
	 *
	 * @return void
	 */
	public function mo_sf_sync_create_mapping_tables() {
		$current_charset_collate = $this->db->get_charset_collate();
		
		$table_name = $this->table_name;
		$sql = "CREATE TABLE $table_name (
			id bigint(20) NOT NULL AUTO_INCREMENT,
			label varchar(64) NOT NULL,
			salesforce_object varchar(255) NOT NULL,
			wordpress_object varchar(128) NOT NULL,
			sync_sf_to_wp tinyint(1) NOT NULL,
			sync_wp_to_sf tinyint(1) NOT NULL,
			PRIMARY KEY (id)
		) $current_charset_collate;";
		
		dbDelta( $sql );
		
		$meta_table_name = $this->meta_table_name;
		$sql = "CREATE TABLE $meta_table_name (
			id tinyint(4) NOT NULL AUTO_INCREMENT,
			mapping_id bigint(20) NOT NULL,
			meta_key longtext NOT NULL,
			meta_value longtext NOT NULL,
			PRIMARY KEY (id)
		) $current_charset_collate;";
		
		dbDelta( $sql );
		
	}

	/**
	 *
	 * Saves field mapping in the created custom tables.
	 *
	 * @param string $sf_object Salesforce object name.
	 * @param string $wp_object WordPress object name.
	 * @param string $direction Direction of the sync, either from salesforce to WordPress or from WordPress to Salesforce.
	 * @param string $field_map Field mapping between the salesforce and WordPress fields.
	 * @return boolean
	 */
	public function mo_sf_sync_save_mapping_in_object_table( $sf_object, $wp_object, $direction, $field_map ) {
		if ( 'sync_sf_to_wp' === $direction ) {
			$sync_sf_to_wp = 1;
			$sync_wp_to_sf = 0;
		} else {
			$sync_sf_to_wp = 0;
			$sync_wp_to_sf = 1;
		}
		$data             = array(
			'label'             => $sf_object . '_' . $wp_object . '_Map',
			'salesforce_object' => $sf_object,
			'wordpress_object'  => $wp_object,
			'sync_sf_to_wp'     => $sync_sf_to_wp,
			'sync_wp_to_sf'     => $sync_wp_to_sf,
		);
		$format           = array( '%s', '%s', '%s', '%d', '%d' );
		$existing_mapping = $this->mo_sf_sync_if_mapping_already_exist();
		$mapping_id       = '';

		if ( false !== $existing_mapping && ! empty( $existing_mapping ) ) {
			if ( $sync_sf_to_wp !== (int) $existing_mapping->sync_sf_to_wp || $sync_wp_to_sf !== (int) $existing_mapping->sync_wp_to_sf ) {
				return false;
			}

			$mapping_id = $existing_mapping->id;
		}

		if ( ! empty( $mapping_id ) ) {
			$this->db->update( $this->table_name, $data, array( 'id' => $mapping_id ), $format, array( '%d' ) );
		} else {
			$this->db->insert( $this->table_name, $data, $format );
			$mapping_id = $this->db->insert_id;

			if ( $sync_sf_to_wp ) {
				$mapping_access_key    = $this->mo_sf_sync_create_key();
				$outbound_redirect_uri = home_url() . '/?method=soap&action=store&mappinglabel=' . rawurlencode( $data['label'] ) . '&accesskey=' . $mapping_access_key;
				$this->mo_sf_sync_save_data_in_meta_table( $mapping_id, 'outbound_redirect_uri', $outbound_redirect_uri );
				$this->mo_sf_sync_save_data_in_meta_table( $mapping_id, 'mapping_access_key', $mapping_access_key );
			}
		}
		$this->mo_sf_sync_save_data_in_meta_table( $mapping_id, 'field_mapping', $field_map );
		return true;
	}

	/**
	 *
	 * Creates an access key.
	 *
	 * @return string
	 */
	private function mo_sf_sync_create_key() {
		$token = bin2hex( random_bytes( 8 ) );
		return $token;
	}

	/**
	 *
	 * Checks if a field mapping already exists.
	 *
	 * @return mixed
	 */
	private function mo_sf_sync_if_mapping_already_exist() {
		$select_query = $this->db->prepare( "SELECT id, sync_wp_to_sf, sync_sf_to_wp FROM %i LIMIT 1", $this->table_name );
		$result       = $this->db->get_results( $select_query );

		if ( ! empty( $result ) ) {
			return $result[0];
		}
		
		return false;
	}

	/**
	 * Saves data in field mapping custom meta table.
	 *
	 * @param int    $mapping_id  Identifier for a field mapping.
	 * @param string $meta_key    label for a value saved in the custom field mapping table.
	 * @param string $meta_value  value to be store in the the custom field mapping table.
	 * @return void
	 */
	public function mo_sf_sync_save_data_in_meta_table( $mapping_id, $meta_key, $meta_value ) {
		$data        = array(
			'mapping_id' => $mapping_id,
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- we use meta_key as label to store values in custom meta table, therefore this can't be changed.
			'meta_key'   => $meta_key,
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- we use meta_value to store values in custom meta table, therefore this can't be changed.
			'meta_value' => maybe_serialize( $meta_value ),
		);
		$format      = array( '%d', '%s', '%s' );
		$meta_exists = $this->mo_sf_sync_if_meta_exists( $mapping_id, $meta_key );
		if ( false !== $meta_exists ) {
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- we use meta_key as label to store values in custom meta table, therefore this can't be changed.
			$this->db->update( $this->meta_table_name, $data, array( 'meta_key' => $meta_key ), $format, array( '%s' ) );
		} else {
			$this->db->insert( $this->meta_table_name, $data, $format );
		}
	}

	/**
	 *
	 * Checks if a meta exists for a mapping or not.
	 *
	 * @param int    $mapping_id  Identifier for a field mapping.
	 * @param string $meta_key    label for a value saved in the custom field mapping table.
	 * @return bool
	 */
	private function mo_sf_sync_if_meta_exists( $mapping_id, $meta_key ) {
		$select_query = "SELECT COUNT(*) from $this->meta_table_name where mapping_id=%d AND meta_key=%s";
		$select_query = $this->db->prepare( $select_query, array( $mapping_id, $meta_key ) );
		$result       = $this->db->get_results( $select_query, ARRAY_A );
		if ( isset( $result[0]['COUNT(*)'] ) && $result[0]['COUNT(*)'] > 0 ) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 *
	 * Fetches all the field mappings.
	 *
	 * @return null|array
	 */
	public function mo_sf_sync_get_all_mapping_data() {
		$select_query = $this->db->prepare( "SELECT * FROM %i", $this->table_name );
		$result       = $this->db->get_results( $select_query, ARRAY_A );
		if ( isset( $result[0]['id'] ) ) {
			$id = $result[0]['id'];
		} else {
			return;
		}
		$meta_data    = $this->mo_sf_sync_get_data_from_metatable( $id );
		$mapping_data = wp_parse_args( $result[0], maybe_unserialize( $meta_data ) );
		return $mapping_data;
	}

	/**
	 * Checks whether at least one mapping row exists.
	 *
	 * @return bool
	 */
	public function mo_sf_sync_has_mapping_data() {
		$select_query = $this->db->prepare( 'SELECT id FROM %i LIMIT 1', $this->table_name );
		$mapping_id   = (int) $this->db->get_var( $select_query );
		return $mapping_id > 0;
	}

	/**
	 *
	 * Get the Object mappings based on mapping label for Salesforce to WordPress Sync.
	 *
	 * @param string $mapping_label    Object mapping Label.
	 * @return null|array
	 */
	public function mo_sf_sync_get_sf_to_wp_sync_object_mapping_by_label_and_access_key( $mapping_label, $access_key ) {
		if ( empty( $mapping_label ) || ! isset( $mapping_label ) || empty( $access_key ) || ! isset( $access_key ) ) {
			return;
		}

		$where_clause = 'AND label = %s';

		$query  = "SELECT * FROM {$this->table_name} WHERE sync_sf_to_wp = 1 {$where_clause}";
		$query  = $this->db->prepare( $query, $mapping_label );
		$result = $this->db->get_results( $query, ARRAY_A );

		if ( empty( $result ) || ! isset( $result[0]['id'] ) ) {
			return;
		}

		$id = $result[0]['id'];

		$access_key_validation = $this->mo_sf_sync_validate_access_key( $id, $access_key );

		if ( $access_key_validation ) {
			$meta_data    = $this->mo_sf_sync_get_data_from_metatable( $id );
			$mapping_data = wp_parse_args( $result[0], maybe_unserialize( $meta_data ) );
			return $mapping_data;
		}

		return;
	}

	/**
	 *
	 * Get the Object mappings based on WordPress Object.
	 *
	 * @param string $wp_object    WordPress Object.
	 * @return null|array
	 */
	public function mo_sf_sync_get_all_mapping_data_by_wp_object( $wp_object ) {
		if ( ! isset( $wp_object ) ) {
			return;
		}

		$query = $this->db->prepare(
			'SELECT * FROM %i WHERE wordpress_object=%s',
			$this->table_name,
			$wp_object
		);
		$result = $this->db->get_results( $query, ARRAY_A );

		if ( empty( $result ) || ! isset( $result[0]['id'] ) ) {
			return;
		}

		$id           = $result[0]['id'];
		$meta_data    = $this->mo_sf_sync_get_data_from_metatable( $id );
		$mapping_data = wp_parse_args( $result[0], maybe_unserialize( $meta_data ) );
		return $mapping_data;
	}

	/**
	 * Validates the provided access key against stored access key for a given mapping ID.
	 *
	 * @param int    $mapping_id The ID of the field mapping.
	 * @param string $access_key The access key to validate.
	 *
	 * @return bool True if the access key is valid, false otherwise.
	 */
	public function mo_sf_sync_validate_access_key( $mapping_id, $access_key ) {
		$meta_response = $this->mo_sf_sync_get_data_from_metatable( $mapping_id, 'mapping_access_key' );

		if ( ! empty( $meta_response ) && ! empty( $meta_response['mapping_access_key'] ) ) {
			$stored_access_key = $meta_response['mapping_access_key'];

			if ( $stored_access_key === $access_key ) {
				return true;
			} else {
				return false;
			}
		}

		$meta_response = $this->mo_sf_sync_get_data_from_metatable( $mapping_id, 'outbound_redirect_uri' );

		if ( ! empty( $meta_response ) && ! empty( $meta_response['outbound_redirect_uri'] ) ) {
			$outbound_url        = $meta_response['outbound_redirect_uri'];
			$parsed_outbound_url = wp_parse_url( $outbound_url );

			if ( isset( $parsed_outbound_url['query'] ) ) {
				parse_str( $parsed_outbound_url['query'], $query_params );

				$parsed_access_key = $query_params['accesskey'] ?? '';

				if ( $parsed_access_key === $access_key ) {
					return true;
				} else {
					return false;
				}
			}
		}

		return false;
	}

	/**
	 *
	 * Fetches data from field mapping meta table.
	 *
	 * @param int    $mapping_id  Identifier for a field mapping.
	 * @param string $meta_key    label for a value saved in the custom field mapping table.
	 * @return array
	 */
	public function mo_sf_sync_get_data_from_metatable( $mapping_id, $meta_key = '' ) {
		$select_query_base = "SELECT * from %i where mapping_id = %d";
		if ( ! empty( $meta_key ) ) {
			$select_query = $this->db->prepare(
				$select_query_base . " AND meta_key = %s",
				$this->meta_table_name,
				$mapping_id,
				$meta_key
			);
		} else {
			$select_query = $this->db->prepare(
				$select_query_base,
				$this->meta_table_name,
				$mapping_id
			);
		}

		$result = $this->db->get_results( $select_query, ARRAY_A );
		$ret    = array();
		foreach ( $result as $key => $value ) {
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- we use meta_key as label to store values in custom meta table, therefore this can't be changed.
			$ret[ $value['meta_key'] ] = $value['meta_value'];
		}

		return $ret;
	}

	/**
	 *
	 * Fetches mapping id of a mapping using mapping label.
	 *
	 * @param  string $mapping_label Label for a field mapping.
	 * @return int
	 */
	private function mo_sf_sync_get_mapping_id_from_mapping_label( $mapping_label ) {
		$select_query = $this->db->prepare(
			"SELECT id from %i where label = %s",
			$this->table_name,
			$mapping_label
		);

		$result = $this->db->get_results( $select_query, ARRAY_A );

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

		return $result[0]['id'];
	}

	/**
	 *
	 * Deletes data from field mapping meta table.
	 *
	 * @param int    $mapping_id  Identifier for a field mapping.
	 * @param string $meta_key    label for a value saved in the custom field mapping table.
	 * @return void
	 */
	public function mo_sf_sync_delete_data_from_metatable( $mapping_id, $meta_key = '' ) {
		$where        = array(
			'mapping_id' => $mapping_id,
		);
		$where_format = array( '%d' );

		if ( ! empty( $meta_key ) ) {
			// phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- meta_key refers to a custom table column, not wp_postmeta
			$where['meta_key'] = $meta_key;
			$where_format[]    = '%s';
		}

		$this->db->delete( $this->meta_table_name, $where, $where_format );
	}

	/**
	 *
	 * Deletes mapping from the custom tables.
	 *
	 * @param string $mapping_label Label for a field mapping.
	 * @return void
	 */
	public function mo_sf_sync_delete_mapping_from_db( $mapping_label ) {
		$id = $this->mo_sf_sync_get_mapping_id_from_mapping_label( $mapping_label );

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

		$this->mo_sf_sync_delete_data_from_metatable( $id );
		$this->db->delete(
			$this->table_name,
			array( 'label' => $mapping_label ),
			array( '%s' )
		);
	}

	/**
	 * Validate the imported mapping data
	 *
	 * @param array $mapping_data imported mapping data array.
	 * @return bool
	 */
	public function mo_sf_sync_validate_imported_mapping( $mapping_data ) {
		if ( ! is_array( $mapping_data ) ) {
			return false;
		}

		foreach ( Plugin_Constants::REQUIRED_FIELDS_FOR_FIELD_MAP_IMPORT as $required_value ) {
			if ( ! isset( $mapping_data[ $required_value ] ) ) {
				return false;
			}
		}

		$wordpress_object = sanitize_text_field( $mapping_data['wordpress_object'] );

		if ( ! in_array( $wordpress_object, Plugin_Constants::SUPPORTED_WORDPRESS_OBJECTS_FOR_MAPPING, true ) ) {
			return false;
		}

		$field_mapping = isset( $mapping_data['field_mapping'] ) ? maybe_unserialize( $mapping_data['field_mapping'] ) : array();
		if ( ! empty( $field_mapping ) ) {
			if ( ! isset( $field_mapping['field_map'] ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 *
	 * Inserts imported data into the database.
	 *
	 * @param array $mapping_data data to be inserted into database.
	 * @return bool
	 */
	public function mo_sf_sync_insert_imported_mapping_data( $mapping_data ) {
		$mapping_validation = $this->mo_sf_sync_validate_imported_mapping( $mapping_data );

		if ( ! $mapping_validation ) {
			return false;
		}

		$label_components = array(
			sanitize_text_field( $mapping_data['salesforce_object'] ),
			sanitize_text_field( $mapping_data['wordpress_object'] ),
			'Map',
		);

		$prepared_map_label = implode( '_', $label_components );

		$data   = array(
			'label'             => $prepared_map_label,
			'salesforce_object' => sanitize_text_field( $mapping_data['salesforce_object'] ),
			'wordpress_object'  => sanitize_text_field( $mapping_data['wordpress_object'] ),
			'sync_sf_to_wp'     => absint( $mapping_data['sync_sf_to_wp'] ),
			'sync_wp_to_sf'     => absint( $mapping_data['sync_wp_to_sf'] ),
		);
		$format = array( '%s', '%s', '%s', '%d', '%d' );

		foreach ( Plugin_Constants::REQUIRED_FIELDS_FOR_FIELD_MAP_IMPORT as $key_name ) {
			unset( $mapping_data[ $key_name ] );
		}

		if ( isset( $mapping_data['id'] ) ) {
			unset( $mapping_data['id'] );
		}

		$get_query = $this->db->prepare( "SELECT * FROM %i", $this->table_name );
		$result    = $this->db->get_results( $get_query, ARRAY_A );

		if ( isset( $result[0] ) && is_array( $result[0] ) && ! empty( $result[0] ) ) {
			$this->db->update(
				$this->table_name,
				$data,
				array(
					'salesforce_object' => $result[0]['salesforce_object'],
					'wordpress_object'  => $result[0]['wordpress_object'],
				),
				$format,
				array( '%s', '%s' )
			);
			$id = $result[0]['id'];
		} else {
			$this->db->insert( $this->table_name, $data, $format );
			$id = $this->db->insert_id;
		}

		if ( ! empty( $mapping_data ) ) {
			foreach ( $mapping_data as $key => $value ) {
				$key = sanitize_key( $key );
			
				if ( 'outbound_redirect_uri' === $key && ! empty( $value ) ) {
					$url_parts = wp_parse_url( $value );
					if ( $url_parts ) {
						$path  = isset( $url_parts['path'] ) ? $url_parts['path'] : '';
						$query = isset( $url_parts['query'] ) ? '?' . $url_parts['query'] : '';
						$value = esc_url_raw( rtrim( site_url(), '/' ) . '/' . ltrim( $path, '/' ) . $query );
					}
				}
				$this->mo_sf_sync_save_data_in_meta_table( $id, $key, maybe_unserialize( $value ) );
			}
		}

		return true;
	}

	/**
	 *
	 * Fetches salesforce record id for WordPress users.
	 *
	 * @return mixed
	 */
	public function mo_sf_sync_get_salesforce_record_id_for_wp_users() {
		$select_query = $this->db->prepare(
			"SELECT salesforce_object FROM %i where wordpress_object = %s",
			$this->table_name,
			'user'
		);
		$data         = $this->db->get_results( $select_query );
		if ( ! empty( $data ) ) {
			return 'salesforce_' . $data[0]->salesforce_object . '_ID';
		}
		return false;
	}

	/**
	 *
	 * Fetches salesforce record id for WordPress posts.
	 *
	 * @return mixed
	 */
	public function mo_sf_sync_get_salesforce_record_id_for_wp_posts() {
		$select_query = $this->db->prepare(
			"SELECT salesforce_object FROM %i where wordpress_object = %s",
			$this->table_name,
			'post'
		);
		$data         = $this->db->get_results( $select_query );
		if ( ! empty( $data ) ) {
			return 'salesforce_' . $data[0]->salesforce_object . '_ID';
		}
		return array();
	}

}
