<?php
/**
 * Setting 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\Constants\Plugin_Filters;
use WP_Error;
use MOWCPS\Common\Utils\Model_Utils;

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

/**
 * Class Setting_Model
 *
 * Handles settings data operations for various setting types
 */
class Setting_Model implements Model_Interface {
	/**
	 * Sync operations table name
	 *
	 * @var string
	 */
	private static $operations_table = Model_Utils::TABLE_SYNC_OPERATIONS;

	/**
	 * Sync settings table name
	 *
	 * @var string
	 */
	private static $settings_table = Model_Utils::TABLE_SYNC_SETTINGS;

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

	/**
	 * Sync operation connections table name
	 *
	 * @var string
	 */
	private static $operation_connections_table = Model_Utils::TABLE_SYNC_OPERATION_CONNECTIONS;

	/**
	 * Sync queue table name
	 *
	 * @var string
	 */
	private static $sync_queue_table = Model_Utils::TABLE_SYNC_QUEUE;

	/**
	 * Sync schedule table name
	 *
	 * @var string
	 */
	private static $schedule_table = Model_Utils::TABLE_SYNC_SCHEDULE;

	/**
	 * Sync filters table name
	 *
	 * @var string
	 */
	private static $filters_table = Model_Utils::TABLE_SYNC_FILTERS;

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

	/**
	 * Valid operation types
	 *
	 * @var array
	 */
	private static $valid_operations = array(
		'outbound',
		'filter',
		'schedule',
		'product-operations',
		'file-sync',
		'page',
		'batch',
		'multi-threading',
	);

	/**
	 * Get all settings by type and supplier
	 *
	 * @param array $args Query parameters.
	 * @return array List of settings
	 */
	public static function get_all( $args = array() ) {
		global $wpdb;

		$operations_table = Model_Utils::get_table_name( self::$operations_table );
		$settings_table   = Model_Utils::get_table_name( self::$settings_table );
		$schedule_table   = Model_Utils::get_table_name( self::$schedule_table );
		$filters_table    = Model_Utils::get_table_name( self::$filters_table );

		$defaults = array(
			'supplier_id' => '',
			'type'        => '',
		);

		$args = wp_parse_args( $args, $defaults );

		if ( empty( $args['supplier_id'] ) || empty( $args['type'] ) ) {
			return array();
		}

		$where_conditions = array(
			'so.supplier_id = %d',
			'so.operation = %s',
		);
		$prepare_values   = array( $args['supplier_id'], $args['type'] );

		// Build query based on operation type.
		$query = 'SELECT so.*';

		if ( 'schedule' === $args['type'] ) {
			$query .= ', ssch.id as schedule_id, ssch.name as schedule_name, ssch.start_time, ssch.interval, ssch.frequency, ssch.timezone, ssch.override_add_update, ssch.allow_add, ssch.allow_update';
		} elseif ( 'filter' === $args['type'] ) {
			$query .= ', sf.name as filter_name, sf.filters';
		} else {
			$query .= ', ss.configuration';
		}

		$query .= " FROM `{$operations_table}` so";

		if ( 'schedule' === $args['type'] ) {
			$query .= " LEFT JOIN `{$schedule_table}` ssch ON so.id = ssch.operation_id";
		} elseif ( 'filter' === $args['type'] ) {
			$query .= " LEFT JOIN `{$filters_table}` sf ON so.id = sf.operation_id";
		} else {
			$query .= " LEFT JOIN `{$settings_table}` ss ON so.id = ss.operation_id";
		}

		$query .= ' WHERE ' . implode( ' AND ', $where_conditions ) . ' ORDER BY so.id ASC';

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

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

		// Convert supplier IDs to UUIDs in configuration for API response.
		$results = Model_Utils::convert_supplier_ids_to_uuids_in_config( $results );

		// Process results to add connection data.
		if ( $results ) {
			foreach ( $results as &$result ) {

				// Add simplified data based on operation type.
				if ( 'schedule' === $args['type'] ) {
					// For schedule: just attribute mapping UUIDs with wc_attribute.
					$result['attribute_mappings'] = self::get_simple_attribute_mappings( $result['id'] );
				} elseif ( 'filter' === $args['type'] ) {
					// For filter: just connection UUIDs with names.
					$result['connections'] = self::get_connections_with_names( $result['id'] );
				} else {
					// For other operations: full connections with names.
					$result['connections'] = self::get_connections_with_names( $result['id'] );
				}
			}
		}

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

	/**
	 * Get setting by UUID
	 *
	 * @param string $uuid The setting UUID.
	 * @return array|null Setting data or null if not found
	 */
	public static function get_by_uuid( $uuid ) {
		global $wpdb;

		$operations_table = Model_Utils::get_table_name( self::$operations_table );
		$settings_table   = Model_Utils::get_table_name( self::$settings_table );
		$schedule_table   = Model_Utils::get_table_name( self::$schedule_table );
		$filters_table    = Model_Utils::get_table_name( self::$filters_table );

		// First get the operation to determine its type.
		$operation = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT * FROM `{$operations_table}` WHERE uuid = %s",
				$uuid
			),
			ARRAY_A
		);

		if ( ! $operation ) {
			return null;
		}

		// Build query based on operation type.
		$query = 'SELECT so.*';

		if ( 'schedule' === $operation['operation'] ) {
			$query .= ', ssch.id as schedule_id, ssch.name as schedule_name, ssch.start_time, ssch.interval, ssch.frequency, ssch.timezone, ssch.override_add_update, ssch.allow_add, ssch.allow_update';
		} elseif ( 'filter' === $operation['operation'] ) {
			$query .= ', sf.name as filter_name, sf.filters';
		} else {
			$query .= ', ss.configuration';
		}

		$query .= " FROM `{$operations_table}` so";

		if ( 'schedule' === $operation['operation'] ) {
			$query .= " LEFT JOIN `{$schedule_table}` ssch ON so.id = ssch.operation_id";
		} elseif ( 'filter' === $operation['operation'] ) {
			$query .= " LEFT JOIN `{$filters_table}` sf ON so.id = sf.operation_id";
		} else {
			$query .= " LEFT JOIN `{$settings_table}` ss ON so.id = ss.operation_id";
		}

		$query .= ' WHERE so.uuid = %s';

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

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

		// Convert supplier IDs to UUIDs in configuration for API response.
		$result = Model_Utils::convert_supplier_ids_to_uuids_in_config( $result );

		// Process result to add connection data.
		if ( $result ) {

			// Add simplified data based on operation type.
			if ( 'schedule' === $operation['operation'] ) {
				// For schedule: just attribute mapping UUIDs with wc_attribute.
				$result['attribute_mappings'] = self::get_simple_attribute_mappings( $result['id'] );
			} elseif ( 'filter' === $operation['operation'] ) {
				// For filter: just connection UUIDs with names.
				$result['connections'] = self::get_connections_with_names( $result['id'] );
			} else {
				// For other operations: full connections with names.
				$result['connections'] = self::get_connections_with_names( $result['id'] );
			}
		}

		return $result;
	}

	/**
	 * Create new setting
	 *
	 * @param array $data The setting data.
	 * @return array|WP_Error Created setting data or error
	 * @throws \Exception When database operation fails.
	 */
	public static function create( $data ) {
		global $wpdb;

		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['operation'] ) ) {
			return new WP_Error(
				Constants::ERROR_MISSING_REQUIRED_FIELD,
				sprintf( Constants::MSG_MISSING_REQUIRED_FIELD, 'operation' ),
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

		$operation = $data['operation'];
		if ( ! in_array( $operation, self::$valid_operations, true ) ) {
			return new WP_Error(
				Constants::ERROR_INVALID_OPERATION,
				Constants::MSG_INVALID_OPERATION,
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

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

		try {

			$operations_table            = Model_Utils::get_table_name( self::$operations_table );
			$settings_table              = Model_Utils::get_table_name( self::$settings_table );
			$operation_connections_table = Model_Utils::get_table_name( self::$operation_connections_table );
			$sync_queue_table            = Model_Utils::get_table_name( self::$sync_queue_table );
			$schedule_table              = Model_Utils::get_table_name( self::$schedule_table );
			$filters_table               = Model_Utils::get_table_name( self::$filters_table );
			$attribute_mapping_table     = Model_Utils::get_table_name( self::$attribute_mapping_table );

			// Create sync operation record.
			$operation_data = array(
				'uuid'           => wp_generate_uuid4(),
				'supplier_id'    => intval( $data['supplier_id'] ),
				'operation_type' => $data['operation_type'] ?? 'data-sync',
				'operation'      => $operation,
				'is_active'      => isset( $data['is_active'] ) ? intval( $data['is_active'] ) : 0,
			);

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

			if ( false === $result ) {
				throw new \Exception( sprintf( Constants::MSG_DB_ERROR, 'Failed to create sync operation.' ) );
			}

			$operation_id = $wpdb->insert_id;

			// Create connection associations if provided.
			if ( ! empty( $data['connection_ids'] ) && is_array( $data['connection_ids'] ) ) {
				// For schedule operations, connection_ids are not used - they are resolved from attribute_mapping_ids.
				// For non-schedule operations, connection_ids is a simple array.
				if ( 'schedule' !== $operation ) {
					foreach ( $data['connection_ids'] as $connection_id ) {
						$connection_data = array(
							'sync_operation_id' => $operation_id,
							'connection_id'     => intval( $connection_id ),
						);

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

						if ( false === $result ) {
							throw new \Exception( sprintf( Constants::MSG_DB_ERROR, 'Failed to create operation connection.' ) );
						}
					}
				}
			}

			// Create specific operation type records.
			if ( 'schedule' === $operation && isset( $data['schedule_data'] ) ) {
				// Handle premium schedule creation using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_SCHEDULE_CREATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
						)
					);
				}
			} elseif ( 'filter' === $operation && isset( $data['filter_data'] ) ) {
				// Handle premium filter creation using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_FILTER_CREATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
						)
					);
				}
			} elseif ( isset( $data['configuration'] ) ) {
				// For other operation types, create settings record.
				$settings_data = array(
					'operation_id'  => $operation_id,
					'configuration' => Model_Utils::encode_configuration_safely( $data['configuration'] ),
				);

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

				if ( false === $result ) {
					throw new \Exception( sprintf( Constants::MSG_DB_ERROR, 'Failed to create settings configuration.' ) );
				}
			}

			$wpdb->query( 'COMMIT' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			return self::get_by_uuid( $operation_data['uuid'] );

		} 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 )
			);
		}
	}

	/**
	 * Update setting
	 *
	 * @param string $uuid The setting UUID.
	 * @param array  $data The setting data.
	 * @return array|WP_Error Updated setting data or error
	 * @throws \Exception When database operation fails.
	 */
	public static function update( $uuid, $data ) {
		global $wpdb;

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

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

		try {
			$operations_table            = Model_Utils::get_table_name( self::$operations_table );
			$settings_table              = Model_Utils::get_table_name( self::$settings_table );
			$operation_connections_table = Model_Utils::get_table_name( self::$operation_connections_table );
			$sync_queue_table            = Model_Utils::get_table_name( self::$sync_queue_table );
			$schedule_table              = Model_Utils::get_table_name( self::$schedule_table );
			$filters_table               = Model_Utils::get_table_name( self::$filters_table );
			$attribute_mapping_table     = Model_Utils::get_table_name( self::$attribute_mapping_table );

			$operation_id   = $existing['id'];
			$operation_type = $existing['operation'];
			$update_data    = array();
			$formats        = array();

			// Update operation fields (only is_active is allowed to be updated).
			if ( isset( $data['is_active'] ) ) {
				$update_data['is_active'] = intval( $data['is_active'] );
				$formats[]                = '%d';
			}

			// Note: operation and operation_type cannot be updated as they define the setting structure.

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

				if ( false === $result ) {
					throw new \Exception( sprintf( Constants::MSG_DB_ERROR, 'Failed to update sync operation.' ) );
				}
			}

			// Update specific operation type data.
			if ( 'schedule' === $operation_type && isset( $data['schedule_data'] ) ) {
				// Handle premium schedule data update using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_SCHEDULE_DATA_UPDATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
						)
					);
				}
			}

			// Update sync queue entries for schedule operations if attribute_mapping_ids is provided.
			if ( 'schedule' === $operation_type && isset( $data['attribute_mapping_ids'] ) ) {
				// Handle premium schedule sync queue update using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_SCHEDULE_SYNC_QUEUE_UPDATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
							'existing'        => $existing,
						)
					);
				}
			}

			if ( 'filter' === $operation_type && isset( $data['filter_data'] ) ) {
				// Handle premium filter data update using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_FILTER_DATA_UPDATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
						)
					);
				}
			} elseif ( isset( $data['configuration'] ) ) {
				// For other operation types, update settings record.
				$configuration = Model_Utils::encode_configuration_safely( $data['configuration'] );

				// Check if settings record exists.
				$settings_exists = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
					$wpdb->prepare(
						"SELECT id FROM `{$settings_table}` WHERE operation_id = %d",
						$operation_id
					)
				);

				if ( $settings_exists ) {
					$result = $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
						$settings_table,
						array( 'configuration' => $configuration ),
						array( 'operation_id' => $operation_id ),
						array( '%s' ),
						array( '%d' )
					);
				} else {
					$result = $wpdb->insert( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
						$settings_table,
						array(
							'operation_id'  => $operation_id,
							'configuration' => $configuration,
						),
						array( '%d', '%s' )
					);
				}

				if ( false === $result ) {
					throw new \Exception( sprintf( Constants::MSG_DB_ERROR, 'Failed to update settings configuration.' ) );
				}
			}

			// Update connection associations if provided.
			if ( isset( $data['connection_ids'] ) ) {
				// Always delete existing connections.
				$wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
					$operation_connections_table,
					array( 'sync_operation_id' => $operation_id ),
					array( '%d' )
				);

				// Create new connections if connection_ids array is not empty.
				// Note: For schedule operations, connection_ids are not used - they are resolved from attribute_mapping_ids.
				if ( is_array( $data['connection_ids'] ) && ! empty( $data['connection_ids'] ) ) {
					foreach ( $data['connection_ids'] as $connection_id ) {

						$connection = Connection_Model::get_by_uuid( $connection_id );

						$connection_data = array(
							'sync_operation_id' => $operation_id,
							'connection_id'     => $connection['id'],
						);

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

						if ( false === $result ) {
							throw new \Exception( sprintf( Constants::MSG_DB_ERROR, 'Failed to update operation connection.' ) );
						}
					}
				}
				// If connection_ids is empty array, existing connections are already deleted above.

				// Handle premium filter connection update using filter.
				if ( 'filter' === $operation_type && has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_FILTER_CONNECTION_UPDATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
							'existing'        => $existing,
						)
					);
				}
			}

			// For schedule operations, also update connection associations based on attribute_mapping_ids.
			if ( 'schedule' === $operation_type && isset( $data['attribute_mapping_ids'] ) ) {
				// Handle premium schedule connection update using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_SCHEDULE_CONNECTION_UPDATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
							'existing'        => $existing,
						)
					);
				}
			}

			// Handle cron scheduling for schedule operations when is_active is updated (even without attribute_mapping_ids).
			if ( 'schedule' === $operation_type && isset( $data['is_active'] ) ) {
				// Handle premium schedule activation update using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_SCHEDULE_ACTIVATION_UPDATE,
							'data'            => $data,
							'operation_id'    => $operation_id,
							'existing'        => $existing,
						)
					);
				}
			}

			$wpdb->query( 'COMMIT' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			return self::get_by_uuid( $uuid );

		} catch ( \Exception $e ) {
			$wpdb->query( 'ROLLBACK' ); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			return new WP_Error(
				'db_error',
				$e->getMessage()
			);
		}
	}

	/**
	 * Delete setting
	 *
	 * @param string $uuid The setting UUID.
	 * @return bool|WP_Error True on success, WP_Error on failure
	 * @throws \Exception When database operation fails.
	 */
	public static function delete( $uuid ) {
		global $wpdb;

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

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

		try {
			$operations_table            = Model_Utils::get_table_name( self::$operations_table );
			$settings_table              = Model_Utils::get_table_name( self::$settings_table );
			$operation_connections_table = Model_Utils::get_table_name( self::$operation_connections_table );
			$sync_queue_table            = Model_Utils::get_table_name( self::$sync_queue_table );
			$schedule_table              = Model_Utils::get_table_name( self::$schedule_table );

			$operation_id   = $existing['id'];
			$operation_type = $existing['operation'];

			// Delete specific operation type data first.
			if ( 'schedule' === $operation_type ) {
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_SCHEDULE_DELETION,
							'data'            => array(),
							'operation_id'    => $operation_id,
							'existing'        => $existing,
						)
					);
				}
			} elseif ( 'filter' === $operation_type ) {
				// Handle premium filter deletion using filter.
				if ( has_filter( Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS ) ) {
					apply_filters(
						Plugin_Filters::FILTER_SETTING_MODEL_OPERATIONS,
						array(
							'event_specifier' => Plugin_Filters::EVENT_HANDLE_FILTER_DELETION,
							'data'            => array(),
							'operation_id'    => $operation_id,
							'existing'        => $existing,
						)
					);
				}
			} else {
				// Delete settings for other operation types.
				$wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.	
					$settings_table,
					array( 'operation_id' => $operation_id ),
					array( '%d' )
				);
			}

			// Delete operation connections.
			$wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$wpdb->prepare(
					"DELETE FROM `{$operation_connections_table}` WHERE sync_operation_id = %d",
					$operation_id
				)
			);

			// Delete operation.
			$result = $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
				$operations_table,
				array( 'uuid' => $uuid ),
				array( '%s' )
			);

			if ( false === $result ) {
				throw new \Exception( sprintf( Constants::MSG_DB_ERROR, 'Failed to delete setting.' ) );
			}

			$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(
				'db_error',
				$e->getMessage()
			);
		}
	}


	/**
	 * Get connections with names for a sync operation
	 *
	 * @param int $operation_id The operation ID.
	 * @param int $operation_id The sync operation ID.
	 * @return array List of connection objects with uuid and name
	 */
	private static function get_connections_with_names( $operation_id ) {
		global $wpdb;

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

		$connections = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT c.uuid, 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
		);

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

	/**
	 * Get simple attribute mappings for schedule operations
	 *
	 * @param int $operation_id The sync operation ID.
	 * @return array Simple array of attribute mappings with uuid and wc_attribute
	 */
	private static function get_simple_attribute_mappings( $operation_id ) {
		global $wpdb;

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

		// Get the schedule ID first.
		$schedule_id = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT id FROM `{$schedule_table}` WHERE operation_id = %d",
				$operation_id
			)
		);

		if ( ! $schedule_id ) {
			return array();
		}

		// Get attribute mappings with just UUID and wc_attribute.
		$results = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT am.uuid, am.wc_attribute
				 FROM `{$sync_queue_table}` sq
				 INNER JOIN `{$attribute_mapping_table}` am ON sq.attribute_mapping_id = am.id
				 WHERE sq.schedule_id = %d
				 ORDER BY am.wc_attribute",
				$schedule_id
			),
			ARRAY_A
		);

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

	/**
	 * Get settings for a specific connection ID and operation type
	 *
	 * @param int    $connection_id The connection ID.
	 * @param string $operation     The operation type (outbound, filter, schedule, product-operations, etc.).
	 * @param array  $args          Optional arguments to control returned data.
	 *                              - configuration (bool): Whether to include configuration data. Default false.
	 * @return array Single setting array for the connection and operation. Returns empty array if no result found.
	 */
	public static function get_by_connection_and_operation( $connection_id, $operation, $args = array() ) {
		global $wpdb;

		$operations_table            = Model_Utils::get_table_name( self::$operations_table );
		$operation_connections_table = Model_Utils::get_table_name( self::$operation_connections_table );
		$schedule_table              = Model_Utils::get_table_name( self::$schedule_table );
		$filters_table               = Model_Utils::get_table_name( self::$filters_table );
		$settings_table              = Model_Utils::get_table_name( self::$settings_table );

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

		// Parse arguments with defaults.
		$defaults = array(
			'configuration' => false,
		);
		$args     = wp_parse_args( $args, $defaults );

		// Validate operation type.
		if ( ! in_array( $operation, self::$valid_operations, true ) ) {
			return array();
		}

		// Build query based on operation type.
		$query = 'SELECT so.id, so.supplier_id, so.operation_type, so.operation, so.is_active';

		if ( 'schedule' === $operation ) {
			$query .= ', ssch.id as schedule_id, ssch.name as schedule_name, ssch.start_time, ssch.interval, ssch.frequency, ssch.timezone, ssch.override_add_update, ssch.allow_add, ssch.allow_update';
		} elseif ( 'filter' === $operation ) {
			$query .= ', sf.name as filter_name, sf.filters';
		} elseif ( $args['configuration'] ) {
			// Only include configuration if explicitly requested.
			$query .= ', ss.configuration';
		}

		$query .= " FROM `{$operations_table}` so";
		$query .= " INNER JOIN `{$operation_connections_table}` soc ON so.id = soc.sync_operation_id";

		if ( 'schedule' === $operation ) {
			$query .= " LEFT JOIN `{$schedule_table}` ssch ON so.id = ssch.operation_id";
		} elseif ( 'filter' === $operation ) {
			$query .= " LEFT JOIN `{$filters_table}` sf ON so.id = sf.operation_id";
		} elseif ( $args['configuration'] ) {
			// Only join with settings table if configuration is requested.
			$query .= " LEFT JOIN `{$settings_table}` ss ON so.id = ss.operation_id";
		}

		$query .= ' WHERE soc.connection_id = %d AND so.operation = %s ORDER BY so.id ASC';

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

		// Decode JSON fields for all results.
		$json_fields = array( 'filters' );
		if ( $args['configuration'] ) {
			$json_fields[] = 'configuration';
		}
		$results = Model_Utils::decode_json_fields( $results, $json_fields );

		// Return single array since there's only one setting per connection+operation.
		return $results ? $results[0] : array();
	}
}
