<?php
/**
 * Product Mapping Model
 *
 * @package    MOMCS_Sync
 * @subpackage MOMCS_Sync/includes/common/models
 * @author     miniOrange
 */

declare(strict_types=1);

namespace MOMCS_Sync\Includes\Common\Models;

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

/**
 * Class for handling product mapping data.
 */
class Product_Mapping_Model {

	/**
	 * DB table name.
	 *
	 * @var string
	 */
	private string $table_name;

	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;
		$this->table_name = $wpdb->prefix . 'momcs_product_mapping';
	}

	/**
	 * Create the product mapping table if it doesn't exist.
	 *
	 * @return void
	 */
	public static function create_table(): void {
		global $wpdb;

		$wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Custom table creation.
			"CREATE TABLE IF NOT EXISTS {$wpdb->prefix}momcs_product_mapping (
			id mediumint(9) NOT NULL AUTO_INCREMENT,
			uuid varchar(36) NOT NULL,
			channel_id varchar(36) NOT NULL,
			config_name varchar(50) NOT NULL,
			config longtext NOT NULL,
			mapping_type varchar(20) DEFAULT 'simple',
			created_at datetime DEFAULT NULL,
			updated_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY uuid (uuid),
			KEY channel_id (channel_id)
			);"
		);
	}

	/**
	 * Get all mappings for a channel.
	 *
	 * @param string $channel_id Channel UUID.
	 * @return array Array of mappings.
	 */
	public function get_mappings_by_channel_id( string $channel_id ): array {
		global $wpdb;

		return $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_product_mapping WHERE channel_id = %s ORDER BY id DESC",
				$channel_id
			),
			ARRAY_A
		);
	}

	/**
	 * Get a mapping by ID.
	 *
	 * @param int $id Mapping ID.
	 * @return array|null Mapping data or null if not found.
	 */
	public function get_mapping_by_id( int $id ): ?array {
		global $wpdb;

		return $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_product_mapping WHERE id = %d",
				$id
			),
			ARRAY_A
		);
	}

	/**
	 * Get a mapping by UUID.
	 *
	 * @param string $uuid Mapping UUID.
	 * @return array|null Mapping data or null if not found.
	 */
	public function get_mapping_by_uuid( string $uuid ): ?array {
		global $wpdb;

		return $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_product_mapping WHERE uuid = %s",
				$uuid
			),
			ARRAY_A
		);
	}

	/**
	 * Get a mapping by channel ID and config name.
	 *
	 * @param string $channel_id  Channel UUID.
	 * @param string $config_name Config name.
	 * @return array|null Mapping data or null if not found.
	 */
	public function get_mapping_by_channel_and_config( $channel_id, $config_name ) {
		global $wpdb;

		return $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_product_mapping WHERE channel_id = %s AND config_name = %s",
				$channel_id,
				$config_name
			),
			ARRAY_A
		);
	}

	/**
	 * Add a new mapping.
	 *
	 * @param array $data Mapping data.
	 * @return int|false The number of rows inserted, or false on error.
	 */
	public function add_mapping( $data ) {
		global $wpdb;

		return $wpdb->insert( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			$data
		);
	}

	/**
	 * Update a mapping.
	 *
	 * @param int   $id   Mapping ID.
	 * @param array $data Mapping data.
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function update_mapping( $id, $data ) {
		global $wpdb;

		return $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			$data,
			array( 'id' => $id )
		);
	}

	/**
	 * Update a mapping by UUID.
	 *
	 * @param string $uuid Mapping UUID.
	 * @param array  $data Mapping data.
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function update_mapping_by_uuid( $uuid, $data ) {
		global $wpdb;

		return $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			$data,
			array( 'uuid' => $uuid )
		);
	}

	/**
	 * Update or create a mapping by channel ID and config name.
	 *
	 * @param string $channel_id  Channel UUID.
	 * @param string $config_name Config name.
	 * @param array  $data        Mapping data.
	 * @return int|false The number of rows updated/inserted, or false on error.
	 */
	public function update_or_create_mapping( $channel_id, $config_name, $data ) {
		$existing = $this->get_mapping_by_channel_and_config( $channel_id, $config_name );

		if ( $existing ) {
			return $this->update_mapping( $existing['id'], $data );
		} else {
			return $this->add_mapping( $data );
		}
	}

	/**
	 * Delete a mapping.
	 *
	 * @param int $id Mapping ID.
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public function delete_mapping( $id ) {
		global $wpdb;

		return $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			array( 'id' => $id )
		);
	}

	/**
	 * Delete a mapping by UUID.
	 *
	 * @param string $uuid Mapping UUID.
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public function delete_mapping_by_uuid( $uuid ) {
		global $wpdb;

		return $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			array( 'uuid' => $uuid )
		);
	}

	/**
	 * Delete all mappings for a channel.
	 *
	 * @param string $channel_id Channel UUID.
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public function delete_mappings_by_channel_id( $channel_id ) {
		global $wpdb;

		return $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			array( 'channel_id' => $channel_id )
		);
	}

	/**
	 * Save (upsert) a mapping by channel ID and config name.
	 *
	 * This method will update an existing mapping or create a new one if it doesn't exist.
	 *
	 * @param string $channel_id  Channel UUID.
	 * @param string $config_name Config name.
	 * @param string $config      Config data (JSON string).
	 * @return int|false The number of rows updated/inserted, or false on error.
	 */
	public function save_mapping( string $channel_id, string $config_name, string $config ) {
		global $wpdb;

		$existing = $this->get_mapping_by_channel_and_config( $channel_id, $config_name );

		$data = array(
			'channel_id'  => $channel_id,
			'config_name' => $config_name,
			'config'      => $config,
			'updated_at'  => current_time( 'mysql', true ),
		);

		if ( $existing ) {
			// Update existing mapping.
			return $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
				$this->table_name,
				$data,
				array( 'id' => $existing['id'] ),
				array( '%s', '%s', '%s', '%s' ),
				array( '%d' )
			);
		} else {
			// Create new mapping.
			$data['uuid']       = wp_generate_uuid4();
			$data['created_at'] = current_time( 'mysql', true );

			$result = $wpdb->insert( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
				$this->table_name,
				$data,
				array( '%s', '%s', '%s', '%s', '%s', '%s' )
			);

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