<?php
/**
 * Connection 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 MOWCPS\Common\Interfaces\Model_Interface;
use MOWCPS\Common\Constants\Constants;
use MOWCPS\Common\Utils\Model_Utils;
use WP_Error;
use Exception;

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

/**
 * Class Connection_Model
 *
 * Handles connection data operations
 */
class Connection_Model implements Model_Interface {
	/**
	 * Database table name
	 *
	 * @var string
	 */
	private static $table_name = Model_Utils::TABLE_CONNECTIONS;

	/**
	 * Valid connection types
	 *
	 * @var array
	 */
	private static $valid_types = array(
		Constants::CONNECTION_TYPE_API,
		Constants::CONNECTION_TYPE_FTP,
		Constants::CONNECTION_TYPE_SFTP,
		Constants::CONNECTION_TYPE_FILE,
	);

	/**
	 * Valid resource types
	 *
	 * @var array
	 */
	private static $valid_resource_types = array(
		Constants::CONNECTION_RESOURCE_TYPE_PRODUCT,
		Constants::CONNECTION_RESOURCE_TYPE_ORDER,
	);

	/**
	 * Get all connections for a supplier by UUID
	 *
	 * @param string $supplier_uuid The supplier UUID.
	 * @param array  $args Query parameters.
	 * @return array|WP_Error List of connections or error
	 */
	public static function get_by_supplier_uuid( $supplier_uuid, $args = array() ) {
		global $wpdb;

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

		// Handle column selection.
		$columns = isset( $args['columns'] ) ? $args['columns'] : null;
		if ( null !== $columns ) {
			// Always include uuid and name.
			$columns        = array_unique( array_merge( $columns, array( 'uuid', 'name' ) ) );
			$select_columns = implode( ', ', array_map( 'esc_sql', $columns ) );
		} else {
			$select_columns = '*';
		}

		// Get supplier ID from UUID first.
		$supplier = Supplier_Model::get_by_uuid( $supplier_uuid );
		if ( ! $supplier ) {
			return new WP_Error(
				Constants::ERROR_SUPPLIER_NOT_FOUND,
				Constants::MSG_SUPPLIER_NOT_FOUND,
				array( 'status' => Constants::HTTP_NOT_FOUND )
			);
		}

		// Build query.
		$query = $wpdb->prepare(
			"SELECT {$select_columns} FROM {$table_name} WHERE supplier_id = %d", //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and columns are safe.
			$supplier['id']
		);

		// Add resource_type filter if provided.
		if ( isset( $args['resource_type'] ) ) {
			$query .= $wpdb->prepare( ' AND resource_type = %s', $args['resource_type'] );
		}

		// Add order by clause.
		$query .= ' ORDER BY priority ASC';

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

		if ( null === $results ) {
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				sprintf( Constants::MSG_DB_ERROR, $wpdb->last_error ),
				array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
			);
		}

		// Decode JSON fields.
		$results = Model_Utils::decode_json_fields( $results, array( 'configuration' ) );

		return $results;
	}

	/**
	 * Get all connections
	 *
	 * @param array $args Query parameters (not used).
	 * @return array List of connections
	 */
	public static function get_all( $args = array() ) {
		global $wpdb;
		$table_name = esc_sql( Model_Utils::get_table_name( self::$table_name ) );
		$results    = $wpdb->get_results( "SELECT * FROM {$table_name}", ARRAY_A ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.

		// Decode JSON fields.
		$results = Model_Utils::decode_json_fields( $results, array( 'configuration' ) );

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

	/**
	 * Get connection by UUID
	 *
	 * @param string $uuid The connection UUID.
	 * @return array|null Connection data or null if not found
	 */
	public static function get_by_uuid( $uuid ) {
		global $wpdb;
		$table_name = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		$connection = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT * FROM {$table_name} WHERE uuid = %s",
				$uuid
			),
			ARRAY_A
		);

		// Decode JSON fields.
		$connection = Model_Utils::decode_json_fields( $connection, array( 'configuration' ) );

		return $connection;
	}

	/**
	 * Get connection by ID
	 *
	 * @param int $id The connection ID.
	 * @return array|null Connection data or null if not found
	 */
	public static function get_by_id( $id ) {
		global $wpdb;
		$table_name = esc_sql( Model_Utils::get_table_name( self::$table_name ) );
		$connection = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT * FROM {$table_name} WHERE id = %d",
				$id
			),
			ARRAY_A
		);

		// Decode JSON fields.
		$connection = Model_Utils::decode_json_fields( $connection, array( 'configuration' ) );

		return $connection;
	}

	/**
	 * Get connections by supplier ID
	 *
	 * @param int $supplier_id The supplier ID.
	 * @return array List of connections for the supplier
	 */
	public static function get_by_supplier_id( $supplier_id ) {
		global $wpdb;
		$table_name = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		$results = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT * FROM {$table_name} WHERE supplier_id = %d",
				$supplier_id
			),
			ARRAY_A
		);

		// Decode JSON fields.
		$results = Model_Utils::decode_json_fields( $results, array( 'configuration' ) );

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

	/**
	 * Create new connection
	 *
	 * @param array $data The connection data.
	 * @return array|WP_Error Created connection data or error
	 */
	public static function create( $data ) {
		global $wpdb;

		// Validate required fields.
		if ( empty( $data['supplier_id'] ) ) {
			return new WP_Error(
				Constants::ERROR_MISSING_REQUIRED_FIELD,
				sprintf( Constants::MSG_MISSING_REQUIRED_FIELD, 'supplier_id' ),
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

		if ( empty( $data['name'] ) ) {
			return new WP_Error(
				Constants::ERROR_MISSING_REQUIRED_FIELD,
				sprintf( Constants::MSG_MISSING_REQUIRED_FIELD, 'name' ),
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

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

		// Get existing connections count for this supplier.
		$existing_count = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT COUNT(*) FROM `{$table_name}` WHERE supplier_id = %d",
				intval( $data['supplier_id'] )
			)
		);

		// Set priority automatically.
		// If no priority provided or if provided priority already exists, use next available.
		$priority = isset( $data['priority'] ) ? intval( $data['priority'] ) : 0;
		if ( $priority <= 0 || self::priority_exists( intval( $data['supplier_id'] ), $priority ) ) {
			// Get highest existing priority and add 1, or start at 2 if no connections exist.
			$highest_priority = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$wpdb->prepare(
					"SELECT COALESCE(MAX(priority), 1) FROM `{$table_name}` WHERE supplier_id = %d",
					intval( $data['supplier_id'] )
				)
			);
			$priority         = intval( $highest_priority ) + 1;
		}

		// Validate and set defaults.
		$type = $data['type'] ?? Constants::CONNECTION_TYPE_API;
		if ( ! in_array( $type, self::$valid_types, true ) ) {
			return new WP_Error(
				Constants::ERROR_INVALID_CONNECTION_TYPE,
				Constants::MSG_INVALID_CONNECTION_TYPE,
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

		$resource_type = $data['resource_type'] ?? Constants::CONNECTION_RESOURCE_TYPE_PRODUCT;
		if ( ! in_array( $resource_type, self::$valid_resource_types, true ) ) {
			return new WP_Error(
				Constants::ERROR_INVALID_RESOURCE_TYPE,
				Constants::MSG_INVALID_RESOURCE_TYPE,
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

		// Check if connection name already exists for this supplier.
		$existing = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT id FROM `{$table_name}` WHERE name = %s AND supplier_id = %d",
				$data['name'],
				intval( $data['supplier_id'] )
			)
		);

		if ( $existing ) {
			return new WP_Error(
				Constants::ERROR_DUPLICATE_ENTRY,
				Constants::MSG_DUPLICATE_ENTRY,
				array( 'status' => Constants::HTTP_CONFLICT )
			);
		}

		// Set is_primary automatically for first connection.
		$is_primary = isset( $data['is_primary'] ) ? intval( $data['is_primary'] ) : ( 0 === $existing_count ? 1 : 0 );

		// If setting this connection as primary, unset any existing primary connections for this supplier.
		if ( $is_primary ) {
			$wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$table_name,
				array( 'is_primary' => 0 ),
				array( 'supplier_id' => intval( $data['supplier_id'] ) ),
				array( '%d' ),
				array( '%d' )
			);
		}

		// Prepare configuration as JSON while preserving XML and other non-JSON body types.
		$configuration = Model_Utils::encode_configuration_safely( $data['configuration'] ?? array() );

		$insert_data = array(
			'uuid'          => wp_generate_uuid4(),
			'supplier_id'   => intval( $data['supplier_id'] ),
			'name'          => $data['name'],
			'type'          => $type,
			'is_active'     => isset( $data['is_active'] ) ? intval( $data['is_active'] ) : 0,
			'is_primary'    => $is_primary,
			'resource_type' => $resource_type,
			'priority'      => $priority,
			'configuration' => $configuration,
		);

		$result = $wpdb->insert( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom query.
			$table_name,
			$insert_data,
			array( '%s', '%d', '%s', '%s', '%d', '%d', '%s', '%d', '%s' )
		);

		if ( false === $result ) {
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				sprintf( Constants::MSG_DB_ERROR, $wpdb->last_error ),
				array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
			);
		}

		return self::get_by_uuid( $insert_data['uuid'] );
	}

	/**
	 * Update connection
	 *
	 * @param string $uuid The connection UUID.
	 * @param array  $data The connection data.
	 * @return array|WP_Error Updated connection data or error
	 * @throws Exception When a database error occurs during priority update transaction.
	 */
	public static function update( $uuid, $data ) {
		global $wpdb;

		$existing = self::get_by_uuid( $uuid );
		if ( ! $existing ) {
			return new WP_Error(
				Constants::ERROR_CONNECTION_NOT_FOUND,
				Constants::MSG_CONNECTION_NOT_FOUND,
				array( 'status' => Constants::HTTP_NOT_FOUND )
			);
		}

		$update_data = array();
		$formats     = array();

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

		// Handle priority update first as it requires special handling.
		if ( isset( $data['priority'] ) ) {
			$new_priority = intval( $data['priority'] );
			$old_priority = intval( $existing['priority'] );

			if ( $new_priority <= 0 ) {
				return new WP_Error(
					Constants::ERROR_INVALID_PRIORITY,
					Constants::MSG_INVALID_PRIORITY,
					array( 'status' => Constants::HTTP_BAD_REQUEST )
				);
			}

			// Only proceed if priority is actually changing.
			if ( $old_priority !== $new_priority ) {
				// Start transaction.
				$wpdb->query( 'START TRANSACTION' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.

				try {
					// First, temporarily set the current connection's priority to 0.
					$result = $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
						$table_name,
						array( 'priority' => 0 ),
						array( 'uuid' => $uuid ),
						array( '%d' ),
						array( '%s' )
					);

					if ( false === $result ) {
						throw new Exception( $wpdb->last_error );
					}

					// If moving to a higher priority.
					if ( $new_priority > $old_priority ) {
						$result = $wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
							$wpdb->prepare(
								"UPDATE `{$table_name}`
                                SET priority = priority - 1
                                WHERE supplier_id = %d
                                AND priority > %d
                                AND priority <= %d",
								$existing['supplier_id'],
								$old_priority,
								$new_priority
							)
						);
					} else { // Moving to a lower priority.
						$result = $wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
							$wpdb->prepare(
								"UPDATE `{$table_name}`
                                SET priority = priority + 1
                                WHERE supplier_id = %d
                                AND priority >= %d
                                AND priority < %d",
								$existing['supplier_id'],
								$new_priority,
								$old_priority
							)
						);
					}

					if ( false === $result ) {
						throw new Exception( $wpdb->last_error );
					}

					// Finally, set the new priority.
					$result = $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
						$table_name,
						array( 'priority' => $new_priority ),
						array( 'uuid' => $uuid ),
						array( '%d' ),
						array( '%s' )
					);

					if ( false === $result ) {
						throw new Exception( $wpdb->last_error );
					}

					$wpdb->query( 'COMMIT' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				} catch ( Exception $e ) {
					$wpdb->query( 'ROLLBACK' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
					return new WP_Error(
						Constants::ERROR_DB_ERROR,
						sprintf( Constants::MSG_DB_ERROR, $e->getMessage() ),
						array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
					);
				}

				// Return early as we've already updated the priority.
				return self::get_by_uuid( $uuid );
			}
		}

		// Handle other updates.
		if ( isset( $data['name'] ) ) {
			$name = sanitize_text_field( $data['name'] );
			if ( empty( $name ) ) {
				return new WP_Error(
					Constants::ERROR_MISSING_REQUIRED_FIELD,
					sprintf( Constants::MSG_MISSING_REQUIRED_FIELD, 'name' ),
					array( 'status' => Constants::HTTP_BAD_REQUEST )
				);
			}

			// Check if name already exists for different connection of same supplier.
			$name_exists = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$wpdb->prepare(
					"SELECT id FROM `{$table_name}` WHERE name = %s AND uuid != %s AND supplier_id = %d",
					$name,
					$uuid,
					$existing['supplier_id']
				)
			);

			if ( $name_exists ) {
				return new WP_Error(
					Constants::ERROR_DUPLICATE_ENTRY,
					Constants::MSG_DUPLICATE_ENTRY,
					array( 'status' => Constants::HTTP_CONFLICT )
				);
			}

			$update_data['name'] = $name;
			$formats[]           = '%s';
		}

		// Handle other fields...
		if ( isset( $data['type'] ) ) {
			$type = sanitize_text_field( $data['type'] );
			if ( ! in_array( $type, self::$valid_types, true ) ) {
				return new WP_Error(
					Constants::ERROR_INVALID_CONNECTION_TYPE,
					Constants::MSG_INVALID_CONNECTION_TYPE,
					array( 'status' => Constants::HTTP_BAD_REQUEST )
				);
			}
			$update_data['type'] = $type;
			$formats[]           = '%s';
		}

		if ( isset( $data['resource_type'] ) ) {
			$resource_type = sanitize_text_field( $data['resource_type'] );
			if ( ! in_array( $resource_type, self::$valid_resource_types, true ) ) {
				return new WP_Error(
					Constants::ERROR_INVALID_RESOURCE_TYPE,
					Constants::MSG_INVALID_RESOURCE_TYPE,
					array( 'status' => Constants::HTTP_BAD_REQUEST )
				);
			}
			$update_data['resource_type'] = $resource_type;
			$formats[]                    = '%s';
		}

		if ( isset( $data['is_active'] ) ) {
			$update_data['is_active'] = intval( $data['is_active'] );
			$formats[]                = '%d';
		}

		if ( isset( $data['is_primary'] ) ) {
			$is_primary                = intval( $data['is_primary'] );
			$update_data['is_primary'] = $is_primary;
			$formats[]                 = '%d';

			// If setting as primary, unset any other primary connections for this supplier.
			if ( $is_primary ) {
				$wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
					$wpdb->prepare(
						"UPDATE `{$table_name}` SET is_primary = 0 WHERE supplier_id = %d AND uuid != %s",
						$existing['supplier_id'],
						$uuid
					)
				);
			}
		}

		if ( isset( $data['configuration'] ) ) {
			$update_data['configuration'] = Model_Utils::encode_configuration_safely( $data['configuration'] );
			$formats[]                    = '%s';
		}

		if ( ! empty( $update_data ) ) {
			$result = $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$table_name,
				$update_data,
				array( 'uuid' => $uuid ),
				$formats,
				array( '%s' )
			);

			if ( false === $result ) {
				return new WP_Error(
					Constants::ERROR_DB_ERROR,
					sprintf( Constants::MSG_DB_ERROR, $wpdb->last_error ),
					array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
				);
			}
		}

		return self::get_by_uuid( $uuid );
	}

	/**
	 * Delete connection
	 *
	 * @param string $uuid The connection UUID.
	 * @return bool|WP_Error True on success, WP_Error on failure
	 * @throws Exception When a database error occurs during deletion transaction.
	 */
	public static function delete( $uuid ) {
		global $wpdb;

		$existing = self::get_by_uuid( $uuid );
		if ( ! $existing ) {
			return new WP_Error(
				Constants::ERROR_CONNECTION_NOT_FOUND,
				Constants::MSG_CONNECTION_NOT_FOUND,
				array( 'status' => Constants::HTTP_NOT_FOUND )
			);
		}

		// Start transaction.
		$wpdb->query( 'START TRANSACTION' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.

		try {
			$table_name = Model_Utils::get_table_name( self::$table_name );
			// Delete the connection.
			$result = $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$table_name,
				array( 'uuid' => $uuid ),
				array( '%s' )
			);

			if ( false === $result ) {
				throw new Exception( $wpdb->last_error );
			}

			// Decrement priorities of all connections with higher priority.
			$result = $wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$wpdb->prepare(
					"UPDATE {$table_name}
                    SET priority = priority - 1
                    WHERE supplier_id = %d
                    AND priority > %d",
					$existing['supplier_id'],
					intval( $existing['priority'] )
				)
			);

			if ( false === $result && null !== $result ) { // Null means no rows needed updating, which is fine.
				throw new Exception( $wpdb->last_error );
			}

			$wpdb->query( 'COMMIT' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			return true;
		} catch ( Exception $e ) {
			$wpdb->query( 'ROLLBACK' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				sprintf( Constants::MSG_DB_ERROR, $e->getMessage() ),
				array(
					'status'   => Constants::HTTP_INTERNAL_SERVER_ERROR,
					'db_error' => $e->getMessage(),
					'uuid'     => $uuid,
				)
			);
		}
	}

	/**
	 * Get connection id by name
	 *
	 * @param int    $supplier_id The supplier ID.
	 * @param string $connection_name The connection name.
	 * @return int|WP_Error Connection ID or error
	 */
	public static function get_connection_id_by_name( $supplier_id, $connection_name ) {
		global $wpdb;
		$table_name    = Model_Utils::get_table_name( self::$table_name );
		$connection_id = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT id FROM {$table_name} WHERE name = %s AND supplier_id = %d",
				$connection_name,
				$supplier_id,
			)
		);
		return $connection_id;
	}

	/**
	 * Check if a priority already exists for a supplier
	 *
	 * @param int    $supplier_id The supplier ID.
	 * @param int    $priority The priority to check.
	 * @param string $exclude_uuid Optional UUID to exclude from check.
	 * @return bool True if priority exists, false otherwise
	 */
	private static function priority_exists( $supplier_id, $priority, $exclude_uuid = null ) {
		global $wpdb;

		$query  = 'SELECT COUNT(*) FROM `' . Model_Utils::get_table_name( self::$table_name ) . '` WHERE supplier_id = %d AND priority = %d';
		$params = array( $supplier_id, $priority );

		if ( ! is_null( $exclude_uuid ) ) {
			$query   .= ' AND uuid != %s';
			$params[] = $exclude_uuid;
		}

		$count = $wpdb->get_var( $wpdb->prepare( $query, $params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		return $count > 0;
	}

	/**
	 * Get connection names from operation ID
	 *
	 * @param string $operation_connections_table The name of the operation connections table.
	 * @param string $connections_table The name of the connections table.
	 * @param int    $operation_id The operation ID.
	 * @return array The connection names.
	 */
	public static function get_connection_name_from_operation_id( $operation_connections_table, $connections_table, $operation_id ) {
		global $wpdb;

		$connections = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT c.name FROM `{$operation_connections_table}` soc
				 INNER JOIN `{$connections_table}` c ON soc.connection_id = c.id
				 WHERE soc.sync_operation_id = %d",
				$operation_id
			),
			ARRAY_A //phpcs:ignore WordPress.DB.SlowDBQuery.SlowDBQuery, WordPress.DB.SlowDBQuery.NotUsingInDirect, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- ARRAY_A is safe.
		);

		return $connections ?: array();
	}

	/**
	 * Get connection name by ID
	 *
	 * @param int $connection_id Connection ID.
	 * @return string Connection name or empty string.
	 */
	public static function get_connection_name_by_id( $connection_id ) {
		global $wpdb;

		$connections_table = Model_Utils::get_table_name( Model_Utils::TABLE_CONNECTIONS );
		$connection_name   = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT name FROM `{$connections_table}` WHERE id = %d",
				$connection_id
			)
		);

		return $connection_name;
	}

	/**
	 * Get the primary product connection for a supplier.
	 *
	 * @param int $supplier_id Supplier ID.
	 * @return array|null Connection record or null if not found.
	 */
	public static function get_primary_product_connection( int $supplier_id ): ?array {
		global $wpdb;

		$result = $wpdb->get_row(
			$wpdb->prepare(
				'SELECT id,uuid FROM `' . Model_Utils::get_table_name( self::$table_name ) . '`
				 WHERE supplier_id = %d
				   AND is_primary = 1
				   AND resource_type = %s
				 LIMIT 1',
				$supplier_id,
				'product'
			),
			ARRAY_A
		);

		return $result ?: null;
	}
}
