<?php
/**
 * Sync Queue Model
 *
 * @category   WordPress_Plugin
 * @package    Common
 * @subpackage Models
 * @author     miniOrange <info@miniorange.com>
 * @license    MIT/Expat
 * @link       https://plugins.miniorange.com/woocommerce-api-product-sync-with-woocommerce-rest-apis
 */

namespace MOWCPS\Common\Models;

use WP_Error;
use MOWCPS\Common\Utils\Model_Utils;

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

/**
 * Class Sync_Queue_Model
 *
 * Handles sync queue data operations
 */
class Sync_Queue_Model {
	/**
	 * Sync queue table name
	 *
	 * @var string
	 */
	private static $sync_queue_table = Model_Utils::TABLE_SYNC_QUEUE;

	/**
	 * Connections table name
	 *
	 * @var string
	 */
	private static $connections_table = Model_Utils::TABLE_CONNECTIONS;

	/**
	 * Attribute mapping table name
	 *
	 * @var string
	 */
	private static $attribute_mapping_table = Model_Utils::TABLE_ATTRIBUTE_MAPPING;

	/**
	 * Get priority queue for active connections by supplier ID and optional schedule ID
	 *
	 * @param int $supplier_id Supplier ID (required).
	 * @param int $schedule_id Schedule ID (optional - if null, gets manual sync connections).
	 * @return array Priority queue with connection IDs and priorities for active connections
	 */
	public static function get_priority_queue( $supplier_id, $schedule_id = null ) {
		global $wpdb;

		$sync_queue_table  = Model_Utils::get_table_name( self::$sync_queue_table );
		$connections_table = Model_Utils::get_table_name( self::$connections_table );

		if ( empty( $supplier_id ) ) {
			return array();
		}

		// Base query to get active connections with their priorities from sync_queue.
		$query = sprintf(
			'SELECT DISTINCT c.id as connection_id, c.priority 
			FROM `%s` c
			INNER JOIN `%s` sq ON c.id = sq.connection_id
			WHERE sq.supplier_id = %%d
			%s
			AND c.is_active = 1
			ORDER BY c.priority ASC',
			$connections_table,
			$sync_queue_table,
			! empty( $schedule_id ) ? 'AND sq.schedule_id = %d' : "AND sq.mode = 'manual'"
		);

		$results = $wpdb->get_results(
			$wpdb->prepare(
				$query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is a string and is escaped using the escape_prefix method.
				! empty( $schedule_id ) ? array( $supplier_id, $schedule_id ) : array( $supplier_id )
			),
			ARRAY_A
		);

		return $results ? $results : array();
	}

	/**
	 * Get attribute mapping IDs from sync queue by connection ID, supplier ID and optional schedule ID
	 *
	 * @param int $supplier_id Supplier ID (required).
	 * @param int $connection_id Connection ID (required).
	 * @param int $schedule_id Schedule ID (optional - if null, gets manual sync mappings).
	 * @return array Array of attribute mapping IDs from sync queue
	 */
	public static function get_attribute_mapping_ids_from_queue( $supplier_id, $connection_id, $schedule_id = null ) {
		global $wpdb;

		$sync_queue_table = Model_Utils::get_table_name( self::$sync_queue_table );

		if ( empty( $connection_id ) || empty( $supplier_id ) ) {
			return array();
		}

		$query = sprintf(
			'SELECT sq.attribute_mapping_id 
			FROM `%s` sq
			WHERE sq.connection_id = %%d
			AND sq.supplier_id = %%d
			%s
			ORDER BY sq.attribute_mapping_id ASC',
			$sync_queue_table,
			! empty( $schedule_id ) ? 'AND sq.schedule_id = %d' : "AND sq.mode = 'manual'"
		);

		$results = $wpdb->get_results(
			$wpdb->prepare(
				$query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is a string and is escaped using the escape_prefix method.
				! empty( $schedule_id ) ? array( $connection_id, $supplier_id, $schedule_id ) : array( $connection_id, $supplier_id )
			),
			ARRAY_A
		);

		// Extract attribute_mapping_id values and convert to integers.
		if ( $results ) {
			return array_map( 'intval', array_column( $results, 'attribute_mapping_id' ) );
		}

		return array();
	}

	/**
	 * Get attribute mapping UUIDs from sync queue by supplier ID with null schedule ID
	 *
	 * @param int $supplier_id Supplier ID.
	 * @return array Array of attribute mapping UUIDs from sync queue
	 */
	public static function get_attribute_mappings_from_queue( $supplier_id ) {
		global $wpdb;
		$sync_queue_table        = Model_Utils::get_table_name( self::$sync_queue_table );
		$attribute_mapping_table = Model_Utils::get_table_name( self::$attribute_mapping_table );

		$results = $wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Static value coming from DB.
			$wpdb->prepare(
				"SELECT am.uuid
				 FROM `{$sync_queue_table}` sq
				 INNER JOIN `{$attribute_mapping_table}` am ON sq.attribute_mapping_id = am.id
				 WHERE sq.supplier_id = %d AND sq.schedule_id IS NULL",
				$supplier_id
			),
			ARRAY_A
		);

		if ( $results ) {
			return array_column( $results, 'uuid' );
		}

		return array();
	}

	/**
	 * Create manual sync queue entries from attribute mapping UUIDs
	 *
	 * @param int   $supplier_id Supplier ID.
	 * @param array $attribute_mapping_uuids Array of attribute mapping UUIDs.
	 * @return bool|WP_Error True if successful, WP_Error on failure.
	 */
	public static function create_manual_sync_queue( $supplier_id, $attribute_mapping_uuids ) {
		global $wpdb;

		$sync_queue_table = Model_Utils::get_table_name( self::$sync_queue_table );
		$mapping_table    = Model_Utils::get_table_name( self::$attribute_mapping_table );

		// Get connection IDs for the attribute mapping UUIDs.
		$query = sprintf(
			'SELECT id, connection_id FROM `%s` WHERE uuid IN (%s)',
			$mapping_table,
			implode( ',', array_fill( 0, count( $attribute_mapping_uuids ), '%s' ) )
		);

		$mappings = $wpdb->get_results(
			$wpdb->prepare( $query, $attribute_mapping_uuids ), // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query is a string and is escaped using the escape_prefix method.
			ARRAY_A
		);

		if ( empty( $mappings ) ) {
			return new WP_Error(
				'mappings_not_found',
				'No attribute mappings found for the provided UUIDs.',
				array( 'status' => 404 )
			);
		}

		// Build bulk insert query.
		$values = array();
		$args   = array();

		foreach ( $mappings as $mapping ) {
			$values[] = '(%d, %d, %d, %s)';
			array_push(
				$args,
				$supplier_id,
				$mapping['connection_id'],
				$mapping['id'],
				'manual'
			);
		}

		// Prepare and execute bulk insert.
		$query = $wpdb->prepare(
			sprintf(
				'INSERT INTO `%s` (supplier_id, connection_id, attribute_mapping_id, mode) VALUES %s',
				$sync_queue_table, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sync_queue_table is a string and is escaped using the escape_prefix method.
				implode( ',', $values ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $values is an array of strings.
			),
			...$args
		);

		$result = $wpdb->query( $query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom dynamic query.

		if ( false === $result ) {
			return new WP_Error(
				'insert_failed',
				'Failed to create sync queue entries.',
				array( 'status' => 500 )
			);
		}

		return true;
	}

	/**
	 * Delete all manual sync queue entries for a supplier
	 *
	 * @param int $supplier_id Supplier ID.
	 * @return bool|WP_Error True if successful, WP_Error on failure.
	 */
	public static function delete_manual_sync_queue( $supplier_id ) {
		global $wpdb;

		$sync_queue_table = Model_Utils::get_table_name( self::$sync_queue_table );
		$result           = $wpdb->query(
			$wpdb->prepare( 'DELETE FROM `' . $sync_queue_table . '` WHERE supplier_id = %d AND mode = %s', $supplier_id, 'manual' ) // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $sync_queue_table is a string and is escaped using the escape_prefix method.
		);

		if ( false === $result ) {
			return new WP_Error(
				'delete_failed',
				'Failed to delete sync queue entries.',
				array( 'status' => 500 )
			);
		}

		return true;
	}
}
