<?php

namespace includes\utils;


/**
 * Class WTV_Customer_Visma_DB
 *
 * This class provides methods for handling operations related to the 'customer_visma_ids' database table, such as creation, insertion, and retrieval of data.
 *
 * @since 2.3.5
 * @clickup https://app.clickup.com/t/86973ptw6
 */
class WTV_Customer_Visma_DB {
	private $table_name;
	private $wpdb;


	private static $instance = null;

	/**
	 * Retrieves the singleton instance of the class. If the instance does not exist, it initializes it.
	 *
	 * @return self The singleton instance of the class.
	 */
	public static function get_instance() {
		if ( self::$instance === null ) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	public static function init() {
		// Add filter for backward compatibility with visma_customer options
		add_filter( 'pre_option', array( __CLASS__, 'handle_visma_customer_option' ), 10, 3 );
		add_filter( 'pre_update_option', array( __CLASS__, 'handle_visma_customer_update' ), 10, 3 );
	}

	/**
	 * Handle backward compatibility for updating visma_customer_* options
	 *
	 * @param mixed $value The new, unserialized option value.
	 * @param string $option The name of the option to update.
	 * @param mixed $old_value The old option value from wp_options.
	 *
	 * @return bool False to prevent WordPress from updating the option in wp_options.
	 */
	public static function handle_visma_customer_update( $value, $option, $old_value ) {
		// Check if the requested option is a visma_customer key
		if ( strpos( $option, 'visma_customer_' ) === 0 ) {
			// Extract email from the option name
			$email = str_replace( 'visma_customer_', '', $option );

			// Validate the email
			if ( ! filter_var( $email, FILTER_VALIDATE_EMAIL ) ) {
				return false; // Invalid email, prevent update
			}
			_doing_it_wrong(
				__METHOD__,
				sprintf(
					'Direct access to visma_customer_* options via update_option is deprecated since version 2.3.5. Use WTV_Customer_Visma_DB::update_entry_by_email() instead for better performance.'
				),
				'2.3.5'
			);
			// Update the value in the custom database table
			$customer_db = WTV_Customer_Visma_DB::get_instance();
			$updated     = $customer_db->update_entry_by_email( $email, $value ); // Assumes this method updates data by email

			if ( $updated ) {
				return false; // Prevent WordPress from updating the wp_options table
			}
		}

		// If not handling visma_customer keys, let the default WordPress behavior run
		return $value;
	}

	/**
	 * Handles backward compatibility for visma_customer_* options
	 *
	 * @param mixed $pre Value to return instead of the option value. Default: false (bypass).
	 * @param string $option The name of the option.
	 * @param mixed $default_value Default value to return if the option does not exist.
	 *
	 * @return mixed The value of the custom database entry or the default option value.
	 */
	public static function handle_visma_customer_option( $pre, $option, $default_value ) {
		// Check if the requested option is a visma_customer key
		if ( strpos( $option, 'visma_customer_' ) === 0 ) {
			// Extract email from the option name
			$email = str_replace( 'visma_customer_', '', $option );

			// Validate the email
			if ( ! filter_var( $email, FILTER_VALIDATE_EMAIL ) ) {
				return false; // Invalid email, do not provide value
			}
			_doing_it_wrong(
				__METHOD__,
				sprintf(
					'Direct access to visma_customer_* options via get_option is deprecated since version 2.3.5. Use WTV_Customer_Visma_DB::read_customer_id_by_email() instead for better performance.'
				),
				'2.3.5'
			);
			// Fetch from the new database table
			$customer_db = WTV_Customer_Visma_DB::get_instance();
			$entry       = $customer_db->read_customer_id_by_email( $email ); // Assumes this method retrieves data by email

			if ( $entry ) {
				return $entry;
			}
		}

		// If not handling visma_customer keys, fall back to default behavior
		return $pre;
	}

	/**
	 * Initializes the class by setting up the database connection and table name.
	 *
	 * @return void
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb       = $wpdb;
		$this->table_name = $wpdb->prefix . 'customer_visma_ids'; // Use table prefix
	}

	/**
	 * Creates a database table with specified schema, including unique keys
	 * and an index for optimized queries.
	 *
	 * @return void
	 */
	public function create_table() {
		$charset_collate = $this->wpdb->get_charset_collate();

		$sql = "
			CREATE TABLE {$this->table_name} (
				id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
				email VARCHAR(255) NOT NULL,
				visma_customer_id VARCHAR(255) NOT NULL,
				UNIQUE KEY email (email),
				UNIQUE KEY visma_customer_id (visma_customer_id),
				INDEX email_visma_index (email, visma_customer_id)
			) $charset_collate;
		";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * Creates a new entry in the database with the provided email and Visma customer ID.
	 *
	 * @param string $email The email address to insert into the database.
	 * @param string $visma_customer_id The Visma customer ID to associate with the email.
	 *
	 * @return int|false The number of rows affected or false on failure.
	 */
	public function create_entry( $email, $visma_customer_id ) {
		return $this->wpdb->insert(
			$this->table_name,
			[
				'email'             => $email,
				'visma_customer_id' => $visma_customer_id,
			],
			[ '%s', '%s' ]
		);
	}

	/**
	 * Retrieves an entry from the database using the provided email.
	 *
	 * @param string $email The email address to search for in the database.
	 *
	 * @return string|false Returns the associated 'visma_customer_id' if found, or false if no entry exists.
	 */
	public function read_customer_id_by_email( $email ) {
		$query = $this->wpdb->prepare( "SELECT visma_customer_id FROM {$this->table_name} WHERE email = %s;", $email );
		return $this->wpdb->get_var($query) ?: false;
	}

	/**
	 * Updates an entry in the database by the given email with the specified value.
	 *
	 * @param array|string $email The email address or an array of email addresses to identify the entry to be updated.
	 * @param mixed $value The new value to set for the 'visma_customer_id' column.
	 *
	 * @return int|false The number of rows affected by the update query, or false on failure.
	 */
	private function update_entry_by_email( $email, mixed $value ) {
		$query = $this->wpdb->prepare( "UPDATE {$this->table_name} SET visma_customer_id = %s WHERE email = %s", $value, $email );

		return $this->wpdb->query( $query );
	}
}
