<?php
/**
 * GGM Commerce Variations Class
 *
 * Handles product variation functionality including variation attributes,
 * product variations, and stock management.
 *
 * @package GGM_Commerce
 */

defined( 'ABSPATH' ) || exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter

/**
 * Class GGM_Commerce_Variations
 */
class GGM_Commerce_Variations {

	/**
	 * Singleton instance.
	 *
	 * @var GGM_Commerce_Variations|null
	 */
	private static $instance = null;

	/** @var array Per-request cache for has_variations lookups */
	private static $has_variations_cache = [];

	/** @var array Per-request cache for parent product price */
	private static $parent_price_cache = [];

	/** @var array Per-request cache for variation mode */
	private static $variation_mode_cache = [];

	/** @var array Per-request cache for independent price ranges */
	private static $independent_range_cache = [];

	/**
	 * Get singleton instance.
	 *
	 * @return GGM_Commerce_Variations
	 */
	public static function instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Constructor.
	 */
	private function __construct() {
		// Register AJAX actions.
		add_action( 'wp_ajax_ggmc_get_variation_price', array( $this, 'ajax_get_variation_price' ) );
		add_action( 'wp_ajax_nopriv_ggmc_get_variation_price', array( $this, 'ajax_get_variation_price' ) );

		add_action( 'wp_ajax_ggmc_get_variation_by_attributes', array( $this, 'ajax_get_variation_by_attributes' ) );
		add_action( 'wp_ajax_nopriv_ggmc_get_variation_by_attributes', array( $this, 'ajax_get_variation_by_attributes' ) );
	}

	/**
	 * Check if Product Variations feature is activated.
	 *
	 * @return bool
	 */
	public static function is_activated() {
		if ( ! class_exists( 'GGM_Commerce_License' ) ) {
			return false;
		}
		return \GGM_Commerce_License::instance()->is_widget_activated( 'product-variations' );
	}

	// =========================================================================
	// VARIATION ATTRIBUTES CRUD
	// =========================================================================

	/**
	 * Get all variation attributes.
	 *
	 * @param array $args Query arguments.
	 * @return array Variation attributes.
	 */
	public function get_variation_types( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'   => 'active',
			'orderby'  => 'display_order',
			'order'    => 'ASC',
			'per_page' => -1,
			'page'     => 1,
		);

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

		$where = array( '1=1' );
		$values = array();

		if ( ! empty( $args['status'] ) && 'all' !== $args['status'] ) {
			$where[] = 'status = %s';
			$values[] = $args['status'];
		}

		$where_clause = implode( ' AND ', $where );
		$orderby = sanitize_sql_orderby( $args['orderby'] . ' ' . $args['order'] ) ?: 'display_order ASC';

		$limit_clause = '';
		if ( $args['per_page'] > 0 ) {
			$offset = ( $args['page'] - 1 ) * $args['per_page'];
			$limit_clause = $wpdb->prepare( ' LIMIT %d OFFSET %d', $args['per_page'], $offset );
		}

		if ( ! empty( $values ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}ggmc_variation_types WHERE {$where_clause} ORDER BY {$orderby}{$limit_clause}", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					...$values
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$results = $wpdb->get_results(
				"SELECT * FROM {$wpdb->prefix}ggmc_variation_types WHERE {$where_clause} ORDER BY {$orderby}{$limit_clause}",
				ARRAY_A
			);
		}

		// Decode options JSON for each attribute.
		if ( $results ) {
			foreach ( $results as &$attr ) {
				$attr['options'] = ! empty( $attr['options'] ) ? json_decode( $attr['options'], true ) : array();
			}
		}

		return $results ?: array();
	}

	/**
	 * Get a single variation attribute by ID.
	 *
	 * @param int $id Attribute ID.
	 * @return array|null Attribute data or null.
	 */
	public function get_variation_type( $id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$attr = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_variation_types WHERE id = %d",
				$id
			),
			ARRAY_A
		);

		if ( $attr ) {
			$attr['options'] = ! empty( $attr['options'] ) ? json_decode( $attr['options'], true ) : array();
		}

		return $attr;
	}

	/**
	 * Get a variation attribute by slug.
	 *
	 * @param string $slug Attribute slug.
	 * @return array|null Attribute data or null.
	 */
	public function get_variation_type_by_slug( $slug ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$attr = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_variation_types WHERE slug = %s",
				$slug
			),
			ARRAY_A
		);

		if ( $attr ) {
			$attr['options'] = ! empty( $attr['options'] ) ? json_decode( $attr['options'], true ) : array();
		}

		return $attr;
	}

	/**
	 * Create a variation attribute.
	 *
	 * @param array $data Attribute data.
	 * @return int|false New attribute ID or false on failure.
	 */
	public function create_variation_type( $data ) {
		global $wpdb;

		$defaults = array(
			'name'           => '',
			'slug'           => '',
			'input_type' => 'select',
			'options'        => array(),
			'display_order'  => 0,
			'status'         => 'active',
		);

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

		// Generate slug if not provided.
		if ( empty( $data['slug'] ) && ! empty( $data['name'] ) ) {
			$data['slug'] = sanitize_title( $data['name'] );
		}

		// Ensure unique slug.
		$data['slug'] = $this->ensure_unique_type_slug( $data['slug'] );

		// Encode options.
		$options_json = is_array( $data['options'] ) ? wp_json_encode( $data['options'] ) : '[]';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert(
			"{$wpdb->prefix}ggmc_variation_types",
			array(
				'name'           => sanitize_text_field( $data['name'] ),
				'slug'           => sanitize_title( $data['slug'] ),
				'input_type' => sanitize_key( $data['input_type'] ),
				'options'        => $options_json,
				'display_order'  => intval( $data['display_order'] ),
				'status'         => sanitize_key( $data['status'] ),
			),
			array( '%s', '%s', '%s', '%s', '%d', '%s' )
		);

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

	/**
	 * Update a variation attribute.
	 *
	 * @param int   $id   Attribute ID.
	 * @param array $data Updated data.
	 * @return bool Success or failure.
	 */
	public function update_variation_type( $id, $data ) {
		global $wpdb;

		$update_data = array();
		$format = array();

		if ( isset( $data['name'] ) ) {
			$update_data['name'] = sanitize_text_field( $data['name'] );
			$format[] = '%s';
		}

		if ( isset( $data['slug'] ) ) {
			$update_data['slug'] = sanitize_title( $data['slug'] );
			$format[] = '%s';
		}

		if ( isset( $data['input_type'] ) ) {
			$update_data['input_type'] = sanitize_key( $data['input_type'] );
			$format[] = '%s';
		}

		if ( isset( $data['options'] ) ) {
			$update_data['options'] = is_array( $data['options'] ) ? wp_json_encode( $data['options'] ) : '[]';
			$format[] = '%s';
		}

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

		if ( isset( $data['status'] ) ) {
			$update_data['status'] = sanitize_key( $data['status'] );
			$format[] = '%s';
		}

		if ( empty( $update_data ) ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			"{$wpdb->prefix}ggmc_variation_types",
			$update_data,
			array( 'id' => $id ),
			$format,
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Delete a variation attribute.
	 *
	 * @param int $id Attribute ID.
	 * @return bool Success or failure.
	 */
	public function delete_variation_type( $id ) {
		global $wpdb;

		// First, remove this variation type from all product configurations.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_product_variation_types",
			array( 'variation_type_id' => $id ),
			array( '%d' )
		);

		// Then delete the variation type.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->delete(
			"{$wpdb->prefix}ggmc_variation_types",
			array( 'id' => $id ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Ensure unique attribute slug.
	 *
	 * @param string $slug    Base slug.
	 * @param int    $exclude ID to exclude from check.
	 * @return string Unique slug.
	 */
	private function ensure_unique_type_slug( $slug, $exclude = 0 ) {
		global $wpdb;

		$original_slug = $slug;
		$counter = 1;

		while ( true ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$existing = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT id FROM {$wpdb->prefix}ggmc_variation_types WHERE slug = %s AND id != %d",
					$slug,
					$exclude
				)
			);

			if ( ! $existing ) {
				break;
			}

			$slug = $original_slug . '-' . $counter;
			++$counter;
		}

		return $slug;
	}

	// =========================================================================
	// PRODUCT VARIATION CONFIGURATION
	// =========================================================================

	/**
	 * Get variation types configured for a product.
	 *
	 * @param int $product_id Product ID.
	 * @return array Configured variation types with their details.
	 */
	public function get_product_variation_config( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$configs = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT pvt.*, vt.name, vt.slug, vt.input_type, vt.options
				FROM {$wpdb->prefix}ggmc_product_variation_types pvt
				INNER JOIN {$wpdb->prefix}ggmc_variation_types vt ON pvt.variation_type_id = vt.id
				WHERE pvt.product_id = %d
				ORDER BY pvt.display_order ASC",
				$product_id
			),
			ARRAY_A
		);

		if ( $configs ) {
			foreach ( $configs as &$config ) {
				$config['options'] = ! empty( $config['options'] ) ? json_decode( $config['options'], true ) : array();
			}
		}

		return $configs ?: array();
	}

	/**
	 * Set variation types for a product.
	 *
	 * @param int   $product_id        Product ID.
	 * @param array $variation_type_ids Array of variation type IDs.
	 * @return bool Success or failure.
	 */
	public function set_product_variation_config( $product_id, $variation_type_ids ) {
		global $wpdb;

		// Remove existing configuration.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_product_variation_types",
			array( 'product_id' => $product_id ),
			array( '%d' )
		);

		// Insert new configuration.
		$order = 0;
		foreach ( $variation_type_ids as $type_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->insert(
				"{$wpdb->prefix}ggmc_product_variation_types",
				array(
					'product_id'        => $product_id,
					'variation_type_id' => intval( $type_id ),
					'display_order'     => $order,
				),
				array( '%d', '%d', '%d' )
			);
			++$order;
		}

		return true;
	}

	/**
	 * Check if a product has variations enabled.
	 *
	 * @param int $product_id Product ID.
	 * @return bool Whether product has variations.
	 */
	public function product_has_variations( $product_id ) {
		if ( isset( self::$has_variations_cache[ $product_id ] ) ) {
			return self::$has_variations_cache[ $product_id ];
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$has_variations = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT has_variations FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
				$product_id
			)
		);

		$result = (bool) $has_variations;
		self::$has_variations_cache[ $product_id ] = $result;
		return $result;
	}

	/**
	 * Set whether a product has variations.
	 *
	 * @param int  $product_id     Product ID.
	 * @param bool $has_variations Whether product has variations.
	 * @param string $stock_mode   Stock mode ('parent' or 'variation').
	 * @return bool Success or failure.
	 */
	public function set_product_has_variations( $product_id, $has_variations, $stock_mode = 'parent' ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			"{$wpdb->prefix}ggmc_products",
			array(
				'has_variations'       => $has_variations ? 1 : 0,
				'variation_stock_mode' => sanitize_key( $stock_mode ),
			),
			array( 'id' => $product_id ),
			array( '%d', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	// =========================================================================
	// PRODUCT VARIATIONS CRUD
	// =========================================================================

	/**
	 * Get all variations for a product.
	 *
	 * @param int   $product_id Product ID.
	 * @param array $args       Query arguments.
	 * @return array Variations.
	 */
	public function get_variations( $product_id, $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status' => 'active',
		);

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

		$where = array( 'product_id = %d' );
		$values = array( $product_id );

		if ( ! empty( $args['status'] ) && 'all' !== $args['status'] ) {
			$where[] = 'status = %s';
			$values[] = $args['status'];
		}

		$where_clause = implode( ' AND ', $where );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$variations = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_product_variations WHERE {$where_clause} ORDER BY is_default DESC, id ASC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				...$values
			),
			ARRAY_A
		);

		// Get attribute values for each variation.
		if ( $variations ) {
			foreach ( $variations as &$variation ) {
				$variation['attributes'] = $this->get_variation_type_values( $variation['id'] );
				$variation['gallery_ids'] = ! empty( $variation['gallery_ids'] )
					? array_map( 'intval', explode( ',', $variation['gallery_ids'] ) )
					: array();
			}
		}

		return $variations ?: array();
	}

	/**
	 * Get a single variation by ID.
	 *
	 * @param int $variation_id Variation ID.
	 * @return array|null Variation data or null.
	 */
	public function get_variation( $variation_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$variation = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_product_variations WHERE id = %d",
				$variation_id
			),
			ARRAY_A
		);

		if ( $variation ) {
			$variation['attributes'] = $this->get_variation_type_values( $variation_id );
			$variation['gallery_ids'] = ! empty( $variation['gallery_ids'] )
				? array_map( 'intval', explode( ',', $variation['gallery_ids'] ) )
				: array();
		}

		return $variation;
	}

	/**
	 * Get variation by type combination.
	 *
	 * @param int   $product_id    Product ID.
	 * @param array $type_values   Associative array of variation_type_id => value.
	 * @return array|null Matching variation or null.
	 */
	public function get_variation_by_attributes( $product_id, $type_values ) {
		global $wpdb;

		// Get all variations for this product.
		$variations = $this->get_variations( $product_id );

		foreach ( $variations as $variation ) {
			$variation_types = array();
			foreach ( $variation['attributes'] as $attr ) {
				$variation_types[ $attr['variation_type_id'] ] = $attr['option_value'];
			}

			// Check if this variation matches the requested type values.
			$match = true;
			foreach ( $type_values as $type_id => $value ) {
				if ( ! isset( $variation_types[ $type_id ] ) || $variation_types[ $type_id ] !== $value ) {
					$match = false;
					break;
				}
			}

			if ( $match && count( $type_values ) === count( $variation_types ) ) {
				return $variation;
			}
		}

		return null;
	}

	/**
	 * Create a product variation.
	 *
	 * @param int   $product_id Product ID.
	 * @param array $data       Variation data.
	 * @return int|false New variation ID or false on failure.
	 */
	public function create_variation( $product_id, $data ) {
		global $wpdb;

		$defaults = array(
			'sku'          => '',
			'price'        => null,
			'sale_price'   => null,
			'stock'        => null,
			'stock_status' => 'instock',
			'manage_stock' => 0,
			'image_id'     => null,
			'gallery_ids'  => array(),
			'weight'       => null,
			'is_default'   => 0,
			'status'       => 'active',
			'attributes'   => array(), // Array of variation_type_id => value.
		);

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

		// Convert gallery_ids array to comma-separated string.
		$gallery_ids = is_array( $data['gallery_ids'] )
			? implode( ',', array_map( 'intval', $data['gallery_ids'] ) )
			: '';

		// If this is default, unset default on other variations.
		if ( $data['is_default'] ) {
			$this->clear_default_variation( $product_id );
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
		$result = $wpdb->insert(
			"{$wpdb->prefix}ggmc_product_variations",
			array(
				'product_id'   => $product_id,
				'sku'          => sanitize_text_field( $data['sku'] ),
				'price'        => $data['price'] !== null ? floatval( $data['price'] ) : null,
				'sale_price'   => $data['sale_price'] !== null ? floatval( $data['sale_price'] ) : null,
				'stock'        => $data['stock'] !== null ? intval( $data['stock'] ) : null,
				'stock_status' => sanitize_key( $data['stock_status'] ),
				'manage_stock' => intval( $data['manage_stock'] ),
				'image_id'     => $data['image_id'] !== null ? intval( $data['image_id'] ) : null,
				'gallery_ids'  => $gallery_ids,
				'weight'       => $data['weight'] !== null ? floatval( $data['weight'] ) : null,
				'is_default'   => intval( $data['is_default'] ),
				'status'       => sanitize_key( $data['status'] ),
			),
			array( '%d', '%s', '%f', '%f', '%d', '%s', '%d', '%d', '%s', '%f', '%d', '%s' )
		);

		if ( ! $result ) {
			return false;
		}

		$variation_id = $wpdb->insert_id;

		// Save attribute values.
		if ( ! empty( $data['attributes'] ) ) {
			$this->set_variation_type_values( $variation_id, $data['attributes'] );
		}

		return $variation_id;
	}

	/**
	 * Update a product variation.
	 *
	 * @param int   $variation_id Variation ID.
	 * @param array $data         Updated data.
	 * @return bool Success or failure.
	 */
	public function update_variation( $variation_id, $data ) {
		global $wpdb;

		// Get current variation for product_id.
		$current = $this->get_variation( $variation_id );
		if ( ! $current ) {
			return false;
		}

		$update_data = array();
		$format = array();

		if ( isset( $data['sku'] ) ) {
			$update_data['sku'] = sanitize_text_field( $data['sku'] );
			$format[] = '%s';
		}

		if ( array_key_exists( 'price', $data ) ) {
			$update_data['price'] = $data['price'] !== null ? floatval( $data['price'] ) : null;
			$format[] = '%f';
		}

		if ( array_key_exists( 'sale_price', $data ) ) {
			$update_data['sale_price'] = $data['sale_price'] !== null ? floatval( $data['sale_price'] ) : null;
			$format[] = '%f';
		}

		if ( array_key_exists( 'stock', $data ) ) {
			$update_data['stock'] = $data['stock'] !== null ? intval( $data['stock'] ) : null;
			$format[] = '%d';
		}

		if ( isset( $data['stock_status'] ) ) {
			$update_data['stock_status'] = sanitize_key( $data['stock_status'] );
			$format[] = '%s';
		}

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

		if ( array_key_exists( 'image_id', $data ) ) {
			$update_data['image_id'] = $data['image_id'] !== null ? intval( $data['image_id'] ) : null;
			$format[] = '%d';
		}

		if ( isset( $data['gallery_ids'] ) ) {
			$update_data['gallery_ids'] = is_array( $data['gallery_ids'] )
				? implode( ',', array_map( 'intval', $data['gallery_ids'] ) )
				: '';
			$format[] = '%s';
		}

		if ( array_key_exists( 'weight', $data ) ) {
			$update_data['weight'] = $data['weight'] !== null ? floatval( $data['weight'] ) : null;
			$format[] = '%f';
		}

		if ( isset( $data['is_default'] ) ) {
			if ( $data['is_default'] ) {
				$this->clear_default_variation( $current['product_id'] );
			}
			$update_data['is_default'] = intval( $data['is_default'] );
			$format[] = '%d';
		}

		if ( isset( $data['status'] ) ) {
			$update_data['status'] = sanitize_key( $data['status'] );
			$format[] = '%s';
		}

		if ( isset( $data['price_type'] ) ) {
			$price_type = sanitize_key( $data['price_type'] );
			$update_data['price_type'] = in_array( $price_type, array( 'absolute', 'modifier' ), true ) ? $price_type : 'absolute';
			$format[] = '%s';
		}

		if ( ! empty( $update_data ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->update(
				"{$wpdb->prefix}ggmc_product_variations",
				$update_data,
				array( 'id' => $variation_id ),
				$format,
				array( '%d' )
			);

			if ( false === $result ) {
				return false;
			}
		}

		// Update attribute values if provided.
		if ( isset( $data['attributes'] ) ) {
			$this->set_variation_type_values( $variation_id, $data['attributes'] );
		}

		return true;
	}

	/**
	 * Delete a product variation.
	 *
	 * @param int $variation_id Variation ID.
	 * @return bool Success or failure.
	 */
	public function delete_variation( $variation_id ) {
		global $wpdb;

		// Delete attribute values first.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_variation_type_values",
			array( 'variation_id' => $variation_id ),
			array( '%d' )
		);

		// Delete the variation.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->delete(
			"{$wpdb->prefix}ggmc_product_variations",
			array( 'id' => $variation_id ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Delete all variations for a product.
	 *
	 * @param int $product_id Product ID.
	 * @return bool Success or failure.
	 */
	public function delete_product_variations( $product_id ) {
		global $wpdb;

		// Get all variation IDs.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$variation_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}ggmc_product_variations WHERE product_id = %d",
				$product_id
			)
		);

		if ( empty( $variation_ids ) ) {
			return true;
		}

		// Delete attribute values for all variations.
		$placeholders = implode( ',', array_fill( 0, count( $variation_ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->prefix}ggmc_variation_type_values WHERE variation_id IN ({$placeholders})",
				...$variation_ids
			)
		);

		// Delete all variations.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->delete(
			"{$wpdb->prefix}ggmc_product_variations",
			array( 'product_id' => $product_id ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Bulk generate variations from all attribute combinations.
	 * Only applies to Combined mode - Independent mode does not create variation records.
	 *
	 * @param int $product_id Product ID.
	 * @return array Array of created variation IDs.
	 */
	public function bulk_generate_variations( $product_id ) {
		// Independent mode does NOT create variation records.
		// Options are stored per-attribute with price modifiers, not as combinations.
		$mode = $this->get_product_variation_mode( $product_id );
		if ( 'independent' === $mode ) {
			return array();
		}

		$config = $this->get_product_variation_config( $product_id );

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

		// Get all possible combinations.
		$combinations = $this->get_type_combinations( $config );

		$created_ids = array();

		foreach ( $combinations as $combination ) {
			// Check if this combination already exists.
			$existing = $this->get_variation_by_attributes( $product_id, $combination );
			if ( $existing ) {
				continue;
			}

			// Create new variation (no default set - customer must choose).
			$variation_id = $this->create_variation(
				$product_id,
				array(
					'attributes' => $combination,
					'is_default' => 0,
				)
			);

			if ( $variation_id ) {
				$created_ids[] = $variation_id;
			}
		}

		return $created_ids;
	}

	/**
	 * Get all possible type combinations.
	 *
	 * @param array $config Product variation config.
	 * @return array Array of combinations (each is variation_type_id => value).
	 */
	private function get_type_combinations( $config ) {
		$attributes = array();
		foreach ( $config as $attr ) {
			if ( ! empty( $attr['options'] ) ) {
				$values = array();
				foreach ( $attr['options'] as $option ) {
					$values[] = $option['value'];
				}
				$attributes[ $attr['variation_type_id'] ] = $values;
			}
		}

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

		return $this->cartesian_product( $attributes );
	}

	/**
	 * Calculate cartesian product of arrays.
	 *
	 * @param array $arrays Associative array where keys are attribute IDs and values are arrays of values.
	 * @return array Array of combinations.
	 */
	private function cartesian_product( $arrays ) {
		$result = array( array() );

		foreach ( $arrays as $key => $values ) {
			$new_result = array();
			foreach ( $result as $combination ) {
				foreach ( $values as $value ) {
					$new_combination = $combination;
					$new_combination[ $key ] = $value;
					$new_result[] = $new_combination;
				}
			}
			$result = $new_result;
		}

		return $result;
	}

	/**
	 * Clear default flag on all variations for a product.
	 *
	 * @param int $product_id Product ID.
	 */
	public function clear_default_variation( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			"{$wpdb->prefix}ggmc_product_variations",
			array( 'is_default' => 0 ),
			array( 'product_id' => $product_id ),
			array( '%d' ),
			array( '%d' )
		);
	}

	// =========================================================================
	// VARIATION ATTRIBUTE VALUES
	// =========================================================================

	/**
	 * Get attribute values for a variation.
	 *
	 * @param int $variation_id Variation ID.
	 * @return array Attribute values.
	 */
	private function get_variation_type_values( $variation_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT vtv.*, vt.name as type_name, vt.slug as type_slug, vt.input_type
				FROM {$wpdb->prefix}ggmc_variation_type_values vtv
				INNER JOIN {$wpdb->prefix}ggmc_variation_types vt ON vtv.variation_type_id = vt.id
				WHERE vtv.variation_id = %d",
				$variation_id
			),
			ARRAY_A
		) ?: array();
	}

	/**
	 * Set type values for a variation.
	 *
	 * @param int   $variation_id Variation ID.
	 * @param array $type_values  Associative array of variation_type_id => value.
	 */
	private function set_variation_type_values( $variation_id, $type_values ) {
		global $wpdb;

		// Delete existing values.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_variation_type_values",
			array( 'variation_id' => $variation_id ),
			array( '%d' )
		);

		// Insert new values.
		foreach ( $type_values as $type_id => $value ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->insert(
				"{$wpdb->prefix}ggmc_variation_type_values",
				array(
					'variation_id'      => $variation_id,
					'variation_type_id' => intval( $type_id ),
					'option_value'      => sanitize_text_field( $value ),
				),
				array( '%d', '%d', '%s' )
			);
		}
	}

	// =========================================================================
	// PRICING
	// =========================================================================

	/**
	 * Get effective price for a variation.
	 *
	 * Handles both absolute prices (replaces base) and modifier prices (adds to base).
	 *
	 * @param int $variation_id Variation ID.
	 * @return float|null Price or null if using parent.
	 */
	public function get_variation_price( $variation_id ) {
		$variation = $this->get_variation( $variation_id );

		if ( ! $variation ) {
			return null;
		}

		$base_price    = $this->get_parent_product_price( $variation['product_id'] );
		$price_type    = isset( $variation['price_type'] ) ? $variation['price_type'] : 'absolute';
		$has_own_price = $variation['price'] !== null;

		// Determine the variation price (sale price takes precedence).
		$variation_price = null;
		if ( $variation['sale_price'] !== null ) {
			$variation_price = floatval( $variation['sale_price'] );
		} elseif ( $has_own_price ) {
			$variation_price = floatval( $variation['price'] );
		}

		// If no variation price set, use base price.
		if ( $variation_price === null ) {
			return $base_price;
		}

		// Handle price type.
		if ( 'modifier' === $price_type ) {
			// Modifier: add/subtract from base price.
			return ( $base_price !== null ? $base_price : 0 ) + $variation_price;
		}

		// Absolute: replace base price entirely.
		return $variation_price;
	}

	/**
	 * Get price range for a product with variations.
	 *
	 * @param int $product_id Product ID.
	 * @return array Array with 'min', 'max', and 'has_range' keys.
	 */
	public function get_price_range( $product_id ) {
		$variations = $this->get_variations( $product_id );

		if ( empty( $variations ) ) {
			return array(
				'min'       => null,
				'max'       => null,
				'has_range' => false,
			);
		}

		$prices = array();
		foreach ( $variations as $variation ) {
			$price = $this->get_variation_price( $variation['id'] );
			if ( $price !== null ) {
				$prices[] = $price;
			}
		}

		if ( empty( $prices ) ) {
			return array(
				'min'       => null,
				'max'       => null,
				'has_range' => false,
			);
		}

		$min = min( $prices );
		$max = max( $prices );

		return array(
			'min'       => $min,
			'max'       => $max,
			'has_range' => $min !== $max,
		);
	}

	/**
	 * Get parent product price.
	 *
	 * @param int $product_id Product ID.
	 * @return float|null Price or null.
	 */
	private function get_parent_product_price( $product_id ) {
		if ( isset( self::$parent_price_cache[ $product_id ] ) ) {
			return self::$parent_price_cache[ $product_id ];
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$product = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT price, sale_price FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
				$product_id
			),
			ARRAY_A
		);

		if ( ! $product ) {
			self::$parent_price_cache[ $product_id ] = null;
			return null;
		}

		if ( $product['sale_price'] !== null && $product['sale_price'] > 0 ) {
			$result = floatval( $product['sale_price'] );
		} else {
			$result = $product['price'] !== null ? floatval( $product['price'] ) : null;
		}

		self::$parent_price_cache[ $product_id ] = $result;
		return $result;
	}

	// =========================================================================
	// VARIATION MODE
	// =========================================================================

	/**
	 * Get product variation mode.
	 *
	 * @param int $product_id Product ID.
	 * @return string 'combined' or 'independent'.
	 */
	public function get_product_variation_mode( $product_id ) {
		if ( isset( self::$variation_mode_cache[ $product_id ] ) ) {
			return self::$variation_mode_cache[ $product_id ];
		}

		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$mode = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT variation_mode FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
				$product_id
			)
		);

		$result = $mode ? $mode : 'combined';
		self::$variation_mode_cache[ $product_id ] = $result;
		return $result;
	}

	/**
	 * Set product variation mode.
	 *
	 * @param int    $product_id Product ID.
	 * @param string $mode       'combined' or 'independent'.
	 * @return bool Success.
	 */
	public function set_product_variation_mode( $product_id, $mode ) {
		global $wpdb;

		// Validate mode.
		if ( ! in_array( $mode, array( 'combined', 'independent' ), true ) ) {
			$mode = 'combined';
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			$wpdb->prefix . 'ggmc_products',
			array( 'variation_mode' => $mode ),
			array( 'id' => $product_id ),
			array( '%s' ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Calculate price for independent mode based on selected options.
	 *
	 * In independent mode, each selected option adds to or replaces the base price,
	 * depending on its price_type setting.
	 *
	 * @param int   $product_id        Product ID.
	 * @param array $selected_options  Array of variation_type_id => value pairs.
	 * @return float Calculated price.
	 */
	public function calculate_independent_price( $product_id, $selected_options ) {
		$base_price = $this->get_parent_product_price( $product_id );
		$total      = $base_price !== null ? $base_price : 0;

		if ( empty( $selected_attrs ) ) {
			return $total;
		}

		$variations = $this->get_variations( $product_id );

		// For each selected attribute, find matching variation and apply price.
		foreach ( $selected_attrs as $attr_id => $value ) {
			foreach ( $variations as $variation ) {
				// Check if this variation matches the attribute/value.
				$attributes = $variation['attributes'];
				if ( ! empty( $attributes ) ) {
					foreach ( $attributes as $attr ) {
						if ( intval( $attr['variation_type_id'] ) === intval( $attr_id ) && $attr['value'] === $value ) {
							// Found matching variation for this attribute.
							$price_type = isset( $variation['price_type'] ) ? $variation['price_type'] : 'absolute';
							$var_price  = null;

							if ( $variation['sale_price'] !== null ) {
								$var_price = floatval( $variation['sale_price'] );
							} elseif ( $variation['price'] !== null ) {
								$var_price = floatval( $variation['price'] );
							}

							if ( $var_price !== null ) {
								if ( 'modifier' === $price_type ) {
									$total += $var_price;
								} else {
									// Absolute replaces base.
									$total = $var_price;
								}
							}
							break 2; // Found match, move to next attribute.
						}
					}
				}
			}
		}

		return $total;
	}

	/**
	 * Get display price range for a product.
	 *
	 * Returns price range data suitable for frontend display,
	 * supporting both "From $X" and "$X - $Y" formats.
	 *
	 * Handles both Combined mode (variations table) and Independent mode
	 * (attribute options with price modifiers).
	 *
	 * @param int $product_id Product ID.
	 * @return array Array with 'min', 'max', 'has_range', 'base_price' keys.
	 */
	public function get_display_price_range( $product_id ) {
		$base_price = $this->get_parent_product_price( $product_id );
		$mode       = $this->get_product_variation_mode( $product_id );

		if ( 'independent' === $mode ) {
			// Independent mode: calculate price range from attribute option modifiers.
			$range = $this->get_independent_price_range( $product_id, $base_price );
		} else {
			// Combined mode: get price range from variations table.
			$range = $this->get_price_range( $product_id );
		}

		return array(
			'min'        => $range['min'],
			'max'        => $range['max'],
			'has_range'  => $range['has_range'],
			'base_price' => $base_price,
		);
	}

	/**
	 * Calculate price range for Independent mode products.
	 *
	 * In Independent mode, each attribute has options with price modifiers.
	 * The customer selects ONE option from each attribute.
	 * Min price = base + sum of minimum modifier from each attribute.
	 * Max price = base + sum of maximum modifier from each attribute.
	 *
	 * @param int        $product_id Product ID.
	 * @param float|null $base_price Base product price.
	 * @return array Array with 'min', 'max', 'has_range' keys.
	 */
	private function get_independent_price_range( $product_id, $base_price ) {
		if ( isset( self::$independent_range_cache[ $product_id ] ) ) {
			return self::$independent_range_cache[ $product_id ];
		}

		global $wpdb;

		if ( $base_price === null ) {
			$result = array(
				'min'       => null,
				'max'       => null,
				'has_range' => false,
			);
			self::$independent_range_cache[ $product_id ] = $result;
			return $result;
		}

		// Get all enabled variation options for this product, grouped by type.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$options = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT variation_type_id, price_modifier
				FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND is_enabled = 1
				ORDER BY variation_type_id",
				$product_id
			),
			ARRAY_A
		);

		if ( empty( $options ) ) {
			// No variation options configured - return base price only.
			$result = array(
				'min'       => $base_price,
				'max'       => $base_price,
				'has_range' => false,
			);
			self::$independent_range_cache[ $product_id ] = $result;
			return $result;
		}

		// Group modifiers by variation type.
		$modifiers_by_type = array();
		foreach ( $options as $option ) {
			$type_id  = $option['variation_type_id'];
			$modifier = (float) $option['price_modifier'];
			if ( ! isset( $modifiers_by_type[ $type_id ] ) ) {
				$modifiers_by_type[ $type_id ] = array();
			}
			$modifiers_by_type[ $type_id ][] = $modifier;
		}

		// Calculate min and max by summing min/max modifier from each type.
		$min_total_modifier = 0;
		$max_total_modifier = 0;
		foreach ( $modifiers_by_type as $type_modifiers ) {
			$min_total_modifier += min( $type_modifiers );
			$max_total_modifier += max( $type_modifiers );
		}

		$min_price = $base_price + $min_total_modifier;
		$max_price = $base_price + $max_total_modifier;

		$result = array(
			'min'       => $min_price,
			'max'       => $max_price,
			'has_range' => $min_price !== $max_price,
		);
		self::$independent_range_cache[ $product_id ] = $result;
		return $result;
	}

	// =========================================================================
	// STOCK MANAGEMENT
	// =========================================================================

	/**
	 * Get stock for a variation.
	 *
	 * @param int $variation_id Variation ID.
	 * @return int|null Stock quantity or null if not tracked.
	 */
	public function get_variation_stock( $variation_id ) {
		$variation = $this->get_variation( $variation_id );

		if ( ! $variation ) {
			return null;
		}

		// If variation manages its own stock, return it.
		if ( $variation['manage_stock'] ) {
			return $variation['stock'];
		}

		// Fall back to parent product stock.
		return $this->get_parent_product_stock( $variation['product_id'] );
	}

	/**
	 * Update variation stock.
	 *
	 * @param int $variation_id Variation ID.
	 * @param int $change       Change amount (negative for decrease).
	 * @return bool Success or failure.
	 */
	public function update_variation_stock( $variation_id, $change ) {
		global $wpdb;

		$variation = $this->get_variation( $variation_id );

		if ( ! $variation || ! $variation['manage_stock'] ) {
			return false;
		}

		$new_stock = max( 0, intval( $variation['stock'] ) + $change );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			"{$wpdb->prefix}ggmc_product_variations",
			array(
				'stock'        => $new_stock,
				'stock_status' => $new_stock > 0 ? 'instock' : 'outofstock',
			),
			array( 'id' => $variation_id ),
			array( '%d', '%s' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Check if variation is available in requested quantity.
	 *
	 * @param int $variation_id Variation ID.
	 * @param int $quantity     Requested quantity.
	 * @return bool Whether available.
	 */
	public function check_availability( $variation_id, $quantity = 1 ) {
		$variation = $this->get_variation( $variation_id );

		if ( ! $variation || 'active' !== $variation['status'] ) {
			return false;
		}

		// If out of stock status, not available.
		if ( 'outofstock' === $variation['stock_status'] ) {
			return false;
		}

		// If managing stock, check quantity.
		if ( $variation['manage_stock'] ) {
			return $variation['stock'] >= $quantity;
		}

		return true;
	}

	/**
	 * Get parent product stock.
	 *
	 * @param int $product_id Product ID.
	 * @return int|null Stock quantity or null.
	 */
	private function get_parent_product_stock( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_var(
			$wpdb->prepare(
				"SELECT stock FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
				$product_id
			)
		);
	}

	// =========================================================================
	// IMAGES
	// =========================================================================

	/**
	 * Get images for a variation.
	 *
	 * @param int $variation_id Variation ID.
	 * @return array Array of image IDs (image_id first, then gallery_ids).
	 */
	public function get_variation_images( $variation_id ) {
		$variation = $this->get_variation( $variation_id );

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

		$images = array();

		// Add main image.
		if ( ! empty( $variation['image_id'] ) ) {
			$images[] = intval( $variation['image_id'] );
		}

		// Add gallery images.
		if ( ! empty( $variation['gallery_ids'] ) ) {
			$images = array_merge( $images, $variation['gallery_ids'] );
		}

		return array_unique( $images );
	}

	/**
	 * Get all variation images for a product.
	 *
	 * @param int $product_id Product ID.
	 * @return array Associative array of variation_id => image IDs.
	 */
	public function get_all_variation_images( $product_id ) {
		$variations = $this->get_variations( $product_id );

		$images = array();
		foreach ( $variations as $variation ) {
			$images[ $variation['id'] ] = $this->get_variation_images( $variation['id'] );
		}

		return $images;
	}

	// =========================================================================
	// AJAX HANDLERS
	// =========================================================================

	/**
	 * AJAX handler for getting variation price.
	 */
	public function ajax_get_variation_price() {
		check_ajax_referer( 'ggmc_nonce', 'nonce' );

		$variation_id = isset( $_POST['variation_id'] ) ? intval( $_POST['variation_id'] ) : 0;

		if ( ! $variation_id ) {
			wp_send_json_error( 'Invalid variation ID' );
		}

		$price = $this->get_variation_price( $variation_id );

		wp_send_json_success(
			array(
				'price'           => $price,
				'formatted_price' => $price !== null ? ggm_commerce_format_price( $price ) : '',
			)
		);
	}

	/**
	 * AJAX handler for getting variation by attributes.
	 */
	public function ajax_get_variation_by_attributes() {
		check_ajax_referer( 'ggmc_nonce', 'nonce' );

		$product_id = isset( $_POST['product_id'] ) ? intval( $_POST['product_id'] ) : 0;
		$attributes = isset( $_POST['attributes'] ) ? array_map( 'sanitize_text_field', wp_unslash( $_POST['attributes'] ) ) : array();

		if ( ! $product_id || empty( $attributes ) ) {
			wp_send_json_error( 'Invalid parameters' );
		}

		$variation = $this->get_variation_by_attributes( $product_id, $attributes );

		if ( ! $variation ) {
			wp_send_json_error( 'Variation not found' );
		}

		// Get effective price.
		$price = $this->get_variation_price( $variation['id'] );

		wp_send_json_success(
			array(
				'variation'       => $variation,
				'price'           => $price,
				'formatted_price' => $price !== null ? ggm_commerce_format_price( $price ) : '',
				'available'       => $this->check_availability( $variation['id'] ),
				'images'          => $this->get_variation_images( $variation['id'] ),
			)
		);
	}

	// =========================================================================
	// INDEPENDENT MODE METHODS
	// =========================================================================

	/**
	 * Calculate price for independent mode based on selected options.
	 * Price = base_price + sum(selected_option_modifiers).
	 *
	 * @param int   $product_id       Product ID.
	 * @param array $selected_options Array of [variation_type_id => option_value].
	 * @return float Calculated price.
	 */
	public function get_independent_price( $product_id, $selected_options ) {
		global $wpdb;

		// Get base product price.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$base_price = (float) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COALESCE(sale_price, price) FROM {$wpdb->prefix}ggmc_products WHERE id = %d",
				$product_id
			)
		);

		if ( empty( $selected_options ) ) {
			return $base_price;
		}

		// Get per-product option configurations.
		$product_options = $this->get_product_variation_options( $product_id );

		$total = $base_price;

		foreach ( $selected_options as $attr_id => $selected_value ) {
			// Look up the price modifier for this option.
			$key = $attr_id . '_' . $selected_value;
			if ( isset( $product_options[ $key ] ) ) {
				$modifier = (float) $product_options[ $key ]['price_modifier'];
				$total += $modifier;
			}
		}

		return $total;
	}

	/**
	 * Get per-product variation options (for Independent mode).
	 * Returns options with enabled/disabled status, defaults, and price modifiers.
	 *
	 * @param int $product_id Product ID.
	 * @return array Keyed by "variation_type_id_option_value".
	 */
	public function get_product_variation_options( $product_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$options = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d
				ORDER BY variation_type_id, display_order",
				$product_id
			),
			ARRAY_A
		);

		$result = array();
		if ( $options ) {
			foreach ( $options as $option ) {
				$key = $option['variation_type_id'] . '_' . $option['option_value'];
				$result[ $key ] = $option;
			}
		}

		return $result;
	}

	/**
	 * Get enabled options for a specific variation type on a product (Independent mode).
	 *
	 * @param int $product_id       Product ID.
	 * @param int $variation_type_id Variation type ID.
	 * @return array Array of enabled options with their config.
	 */
	public function get_enabled_variation_options( $product_id, $variation_type_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$options = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d AND is_enabled = 1
				ORDER BY display_order",
				$product_id,
				$variation_type_id
			),
			ARRAY_A
		);

		return $options ?: array();
	}

	/**
	 * Get the default option for a variation type on a product (Independent mode).
	 *
	 * @param int $product_id       Product ID.
	 * @param int $variation_type_id Variation type ID.
	 * @return string|null Default option value or null.
	 */
	public function get_default_variation_option( $product_id, $variation_type_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$default_value = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT option_value FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d AND is_default = 1
				LIMIT 1",
				$product_id,
				$variation_type_id
			)
		);

		return $default_value;
	}

	/**
	 * Save per-product variation options (for Independent mode).
	 * This creates/updates options with enabled status, defaults, and price modifiers.
	 *
	 * @param int   $product_id       Product ID.
	 * @param int   $variation_type_id Variation type ID.
	 * @param array $options          Array of options: [ ['value' => 'X', 'enabled' => true, 'modifier' => 0, 'is_default' => false], ... ].
	 * @return bool Success.
	 */
	public function save_product_variation_options( $product_id, $variation_type_id, $options ) {
		global $wpdb;

		// Delete existing options for this product/variation type.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_product_variation_options",
			array(
				'product_id'        => $product_id,
				'variation_type_id' => $variation_type_id,
			),
			array( '%d', '%d' )
		);

		// Insert new options.
		$order = 0;
		foreach ( $options as $option ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->insert(
				"{$wpdb->prefix}ggmc_product_variation_options",
				array(
					'product_id'        => $product_id,
					'variation_type_id' => $variation_type_id,
					'option_value'      => $option['value'],
					'price_modifier'    => isset( $option['modifier'] ) ? (float) $option['modifier'] : 0.00,
					'is_enabled'        => ! empty( $option['enabled'] ) ? 1 : 0,
					'is_default'        => ! empty( $option['is_default'] ) ? 1 : 0,
					'display_order'     => $order++,
				),
				array( '%d', '%d', '%s', '%f', '%d', '%d', '%d' )
			);
		}

		return true;
	}

	/**
	 * Initialize per-product variation options from the global variation type options.
	 * Called when enabling Independent mode or adding a variation type to a product.
	 *
	 * @param int $product_id       Product ID.
	 * @param int $variation_type_id Variation type ID.
	 * @return bool Success.
	 */
	public function initialize_product_variation_options( $product_id, $variation_type_id ) {
		global $wpdb;

		// Check if options already exist.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d",
				$product_id,
				$variation_type_id
			)
		);

		if ( $existing > 0 ) {
			return true; // Already initialized.
		}

		// Get global variation type options.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$variation_type = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT options FROM {$wpdb->prefix}ggmc_variation_types WHERE id = %d",
				$variation_type_id
			)
		);

		if ( ! $variation_type || empty( $variation_type->options ) ) {
			return false;
		}

		$global_options = json_decode( $variation_type->options, true );
		if ( empty( $global_options ) ) {
			return false;
		}

		// Create per-product options (all enabled by default, first one is default).
		$options = array();
		$is_first = true;
		foreach ( $global_options as $opt ) {
			$options[] = array(
				'value'      => $opt['value'],
				'enabled'    => true,
				'modifier'   => isset( $opt['price_modifier'] ) ? (float) $opt['price_modifier'] : 0.00,
				'is_default' => $is_first,
			);
			$is_first = false;
		}

		return $this->save_product_variation_options( $product_id, $variation_type_id, $options );
	}

	/**
	 * Get variation config for Independent mode with per-product overrides.
	 * Returns variation options filtered to ONLY include options that exist
	 * in the per-product options table (explicit add/remove model).
	 *
	 * @param int $product_id Product ID.
	 * @return array Configuration with filtered options.
	 */
	public function get_independent_mode_config( $product_id ) {
		$config = $this->get_product_variation_config( $product_id );

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

		// Get per-product option records - ONLY these should be shown.
		$product_options = $this->get_product_variation_options( $product_id );

		foreach ( $config as &$attr ) {
			$attr_id = $attr['variation_type_id'];
			$filtered_options = array();
			$default_value = null;

			if ( ! empty( $attr['options'] ) ) {
				foreach ( $attr['options'] as $option ) {
					$key = $attr_id . '_' . $option['value'];

					// With explicit add/remove model: ONLY include options that have
					// a per-product record. Options not in the table = removed/disabled.
					if ( ! isset( $product_options[ $key ] ) ) {
						continue; // Skip - option doesn't exist for this product.
					}

					$prod_opt = $product_options[ $key ];

					// Use per-product price modifier.
					$option['price_modifier'] = (float) $prod_opt['price_modifier'];

					// Track default.
					if ( ! empty( $prod_opt['is_default'] ) ) {
						$default_value = $option['value'];
					}

					$filtered_options[] = $option;
				}
			}

			$attr['options'] = $filtered_options;
			$attr['default_value'] = $default_value;
		}

		return $config;
	}

	/**
	 * Format selected options for display (used in orders/cart).
	 *
	 * @param int   $product_id       Product ID.
	 * @param array $selected_options Array of [variation_type_id => option_value].
	 * @return string Formatted string like "Storage: 1TB (+$200), RAM: 32GB (+$300)".
	 */
	public function format_selected_options( $product_id, $selected_options ) {
		$config = $this->get_product_variation_config( $product_id );
		$product_options = $this->get_product_variation_options( $product_id );

		$parts = array();

		foreach ( $selected_options as $type_id => $value ) {
			// Find variation type name.
			$type_name = '';
			foreach ( $config as $type ) {
				if ( (int) $type['variation_type_id'] === (int) $type_id ) {
					$type_name = $type['name'];
					break;
				}
			}

			// Get price modifier.
			$key = $type_id . '_' . $value;
			$modifier = 0;
			if ( isset( $product_options[ $key ] ) ) {
				$modifier = (float) $product_options[ $key ]['price_modifier'];
			}

			// Format.
			$part = $type_name . ': ' . $value;
			if ( $modifier != 0 ) {
				$sign = $modifier >= 0 ? '+' : '';
				$part .= ' (' . $sign . ggm_commerce_format_price( $modifier ) . ')';
			}
			$parts[] = $part;
		}

		return implode( ', ', $parts );
	}

	// =========================================================================
	// INDEPENDENT MODE: EXPLICIT ADD/REMOVE OPTION METHODS
	// =========================================================================

	/**
	 * Delete a specific per-product variation option.
	 * Used for explicit removal of options in Independent mode.
	 *
	 * @param int    $product_id       Product ID.
	 * @param int    $variation_type_id Variation type ID.
	 * @param string $option_value     Option value to remove.
	 * @return bool Success.
	 */
	public function delete_product_variation_option( $product_id, $variation_type_id, $option_value ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->delete(
			"{$wpdb->prefix}ggmc_product_variation_options",
			array(
				'product_id'        => $product_id,
				'variation_type_id' => $variation_type_id,
				'option_value'      => $option_value,
			),
			array( '%d', '%d', '%s' )
		);

		return $result !== false;
	}

	/**
	 * Add a single option to a product variation type.
	 * Used when restoring removed options in Independent mode.
	 *
	 * @param int    $product_id       Product ID.
	 * @param int    $variation_type_id Variation type ID.
	 * @param string $option_value     Option value.
	 * @param float  $modifier         Price modifier (default 0).
	 * @param bool   $is_default       Whether this is the default option.
	 * @return int|false New option ID or false on failure.
	 */
	public function add_product_variation_option( $product_id, $variation_type_id, $option_value, $modifier = 0.00, $is_default = false ) {
		global $wpdb;

		// Get current max display order for this product/variation type.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$max_order = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT MAX(display_order) FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d",
				$product_id,
				$variation_type_id
			)
		);
		$display_order = ( $max_order !== null ) ? intval( $max_order ) + 1 : 0;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->insert(
			"{$wpdb->prefix}ggmc_product_variation_options",
			array(
				'product_id'        => $product_id,
				'variation_type_id' => $variation_type_id,
				'option_value'      => $option_value,
				'price_modifier'    => $modifier,
				'is_enabled'     => 1, // Always enabled when added
				'is_default'     => $is_default ? 1 : 0,
				'display_order'  => $display_order,
			),
			array( '%d', '%d', '%s', '%f', '%d', '%d', '%d' )
		);

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

	/**
	 * Get options from global variation type that are NOT on this product.
	 * Used to show "Add Missing Options" dropdown.
	 *
	 * @param int $product_id       Product ID.
	 * @param int $variation_type_id Variation type ID.
	 * @return array Missing options with value and label.
	 */
	public function get_missing_variation_options( $product_id, $variation_type_id ) {
		global $wpdb;

		// Get global variation type options.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$variation_type = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT options FROM {$wpdb->prefix}ggmc_variation_types WHERE id = %d",
				$variation_type_id
			)
		);

		if ( ! $variation_type || empty( $variation_type->options ) ) {
			return array();
		}

		$global_options = json_decode( $variation_type->options, true );
		if ( empty( $global_options ) ) {
			return array();
		}

		// Get product's current options for this variation type.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$product_option_values = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT option_value FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d",
				$product_id,
				$variation_type_id
			)
		);

		// Find options that are in global but not in product.
		$missing = array();
		foreach ( $global_options as $opt ) {
			if ( ! in_array( $opt['value'], $product_option_values, true ) ) {
				$missing[] = array(
					'value' => $opt['value'],
					'label' => isset( $opt['label'] ) ? $opt['label'] : $opt['value'],
					'price_modifier' => isset( $opt['price_modifier'] ) ? (float) $opt['price_modifier'] : 0,
				);
			}
		}

		return $missing;
	}

	/**
	 * Ensure a valid default exists after option removal.
	 * If the removed option was the default, sets first remaining option as default.
	 *
	 * @param int $product_id       Product ID.
	 * @param int $variation_type_id Variation type ID.
	 * @return array Result with 'changed' bool and 'new_default' value if changed.
	 */
	public function ensure_valid_default( $product_id, $variation_type_id ) {
		global $wpdb;

		// Check if there's still a default set.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$current_default = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT option_value FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d AND is_default = 1",
				$product_id,
				$variation_type_id
			)
		);

		if ( $current_default ) {
			// Default still exists, no change needed.
			return array(
				'changed' => false,
				'new_default' => $current_default,
			);
		}

		// No default set, get the first remaining option and make it default.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$first_option = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT option_value FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d
				ORDER BY display_order ASC, id ASC
				LIMIT 1",
				$product_id,
				$variation_type_id
			)
		);

		if ( ! $first_option ) {
			// No options remaining.
			return array(
				'changed' => false,
				'new_default' => null,
			);
		}

		// Set the first option as default.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			"{$wpdb->prefix}ggmc_product_variation_options",
			array( 'is_default' => 1 ),
			array(
				'product_id'        => $product_id,
				'variation_type_id' => $variation_type_id,
				'option_value'      => $first_option,
			),
			array( '%d' ),
			array( '%d', '%d', '%s' )
		);

		return array(
			'changed' => true,
			'new_default' => $first_option,
		);
	}

	/**
	 * Add all missing options from global variation type to product.
	 * Bulk restore for removed options.
	 *
	 * @param int $product_id       Product ID.
	 * @param int $variation_type_id Variation type ID.
	 * @return int Number of options added.
	 */
	public function add_all_missing_options( $product_id, $variation_type_id ) {
		$missing = $this->get_missing_variation_options( $product_id, $variation_type_id );

		$added = 0;
		foreach ( $missing as $opt ) {
			$result = $this->add_product_variation_option(
				$product_id,
				$variation_type_id,
				$opt['value'],
				$opt['price_modifier'],
				false // Not default
			);
			if ( $result ) {
				$added++;
			}
		}

		return $added;
	}

	/**
	 * Update a single product variation option.
	 * Used to update price modifier without affecting other options.
	 *
	 * @param int    $product_id       Product ID.
	 * @param int    $variation_type_id Variation type ID.
	 * @param string $option_value     Option value to update.
	 * @param array  $data             Data to update (price_modifier, is_default).
	 * @return bool Success.
	 */
	public function update_product_variation_option( $product_id, $variation_type_id, $option_value, $data ) {
		global $wpdb;

		$update_data = array();
		$update_format = array();

		if ( isset( $data['price_modifier'] ) ) {
			$update_data['price_modifier'] = (float) $data['price_modifier'];
			$update_format[] = '%f';
		}

		if ( isset( $data['is_default'] ) ) {
			$update_data['is_default'] = $data['is_default'] ? 1 : 0;
			$update_format[] = '%d';
		}

		if ( empty( $update_data ) ) {
			return true; // Nothing to update.
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			"{$wpdb->prefix}ggmc_product_variation_options",
			$update_data,
			array(
				'product_id'        => $product_id,
				'variation_type_id' => $variation_type_id,
				'option_value'      => $option_value,
			),
			$update_format,
			array( '%d', '%d', '%s' )
		);

		return $result !== false;
	}

	/**
	 * Set the default option for a variation type.
	 * Clears any existing default and sets the new one.
	 *
	 * @param int    $product_id       Product ID.
	 * @param int    $variation_type_id Variation type ID.
	 * @param string $option_value     Option value to set as default.
	 * @return bool Success.
	 */
	public function set_default_option( $product_id, $variation_type_id, $option_value ) {
		global $wpdb;

		// Clear existing default for this variation type.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			"{$wpdb->prefix}ggmc_product_variation_options",
			array( 'is_default' => 0 ),
			array(
				'product_id'        => $product_id,
				'variation_type_id' => $variation_type_id,
			),
			array( '%d' ),
			array( '%d', '%d' )
		);

		// Set new default.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $wpdb->update(
			"{$wpdb->prefix}ggmc_product_variation_options",
			array( 'is_default' => 1 ),
			array(
				'product_id'        => $product_id,
				'variation_type_id' => $variation_type_id,
				'option_value'      => $option_value,
			),
			array( '%d' ),
			array( '%d', '%d', '%s' )
		);

		return $result !== false;
	}

	/**
	 * Get all options for a specific variation type on a product.
	 * Returns options in display order with all their data.
	 *
	 * @param int $product_id       Product ID.
	 * @param int $variation_type_id Variation type ID.
	 * @return array Options for this variation type.
	 */
	public function get_variation_options_for_product( $product_id, $variation_type_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$options = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_product_variation_options
				WHERE product_id = %d AND variation_type_id = %d
				ORDER BY display_order ASC, id ASC",
				$product_id,
				$variation_type_id
			),
			ARRAY_A
		);

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