<?php
/**
 * GGM Commerce Custom Checkout Fields Management
 * Handles checkout field groups, field definitions, and order field values
 *
 * @package GGM_Commerce
 */

// Prevent direct access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

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

/**
 * Class GGM_Commerce_Checkout_Fields
 *
 * Manages custom checkout fields with group support.
 */
class GGM_Commerce_Checkout_Fields {

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

	/**
	 * Valid field types.
	 *
	 * @var array
	 */
	private $valid_field_types = array(
		'text',
		'textarea',
		'email',
		'tel',
		'number',
		'select',
		'radio',
		'checkbox',
		'date',
		'url',
	);

	/**
	 * Valid positions.
	 *
	 * @var array
	 */
	private $valid_positions = array(
		'after_customer_info',
		'after_billing',
		'after_shipping',
		'after_order_notes',
		'after_order_summary_totals',
	);

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

	/**
	 * Constructor.
	 */
	public function __construct() {
		// AJAX handlers for field groups.
		add_action( 'wp_ajax_ggmc_create_checkout_field_group', array( $this, 'ajax_create_field_group' ) );
		add_action( 'wp_ajax_ggmc_update_checkout_field_group', array( $this, 'ajax_update_field_group' ) );
		add_action( 'wp_ajax_ggmc_delete_checkout_field_group', array( $this, 'ajax_delete_field_group' ) );
		add_action( 'wp_ajax_ggmc_reorder_checkout_field_groups', array( $this, 'ajax_reorder_field_groups' ) );

		// AJAX handlers for fields.
		add_action( 'wp_ajax_ggmc_create_checkout_field', array( $this, 'ajax_create_field' ) );
		add_action( 'wp_ajax_ggmc_update_checkout_field', array( $this, 'ajax_update_field' ) );
		add_action( 'wp_ajax_ggmc_delete_checkout_field', array( $this, 'ajax_delete_field' ) );
		add_action( 'wp_ajax_ggmc_reorder_checkout_fields', array( $this, 'ajax_reorder_fields' ) );

		// AJAX handlers for group membership.
		add_action( 'wp_ajax_ggmc_add_field_to_group', array( $this, 'ajax_add_field_to_group' ) );
		add_action( 'wp_ajax_ggmc_remove_field_from_group', array( $this, 'ajax_remove_field_from_group' ) );

		// AJAX handler for admin order field edit.
		add_action( 'wp_ajax_ggmc_update_order_checkout_field', array( $this, 'ajax_update_order_field' ) );
	}

	/**
	 * Get available field types.
	 *
	 * @return array
	 */
	public function get_field_types() {
		return array(
			'text'     => __( 'Text', 'ggm-commerce' ),
			'textarea' => __( 'Textarea', 'ggm-commerce' ),
			'email'    => __( 'Email', 'ggm-commerce' ),
			'tel'      => __( 'Phone', 'ggm-commerce' ),
			'number'   => __( 'Number', 'ggm-commerce' ),
			'select'   => __( 'Dropdown Select', 'ggm-commerce' ),
			'radio'    => __( 'Radio Buttons', 'ggm-commerce' ),
			'checkbox'  => __( 'Checkbox', 'ggm-commerce' ),
			'date'     => __( 'Date', 'ggm-commerce' ),
			'url'      => __( 'URL', 'ggm-commerce' ),
		);
	}

	/**
	 * Get available positions.
	 *
	 * @return array
	 */
	public function get_positions() {
		return array(
			'after_customer_info'        => __( 'After Customer Info', 'ggm-commerce' ),
			'after_billing'              => __( 'After Billing Address', 'ggm-commerce' ),
			'after_shipping'             => __( 'After Shipping Address', 'ggm-commerce' ),
			'after_order_notes'          => __( 'After Order Notes', 'ggm-commerce' ),
			'after_order_summary_totals' => __( 'After Order Summary Totals', 'ggm-commerce' ),
		);
	}

	// =========================================================================
	// FIELD GROUPS CRUD
	// =========================================================================

	/**
	 * Get all field groups.
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public function get_field_groups( $args = array() ) {
		global $wpdb;

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

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

		$sql = "SELECT * FROM {$wpdb->prefix}ggmc_checkout_field_groups WHERE 1=1";

		if ( $args['status'] ) {
			$sql .= $wpdb->prepare( ' AND status = %s', $args['status'] );
		}

		$sql .= " ORDER BY {$args['orderby']} {$args['order']}";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $sql is built safely with wpdb->prepare.
		return $wpdb->get_results( $sql );
	}

	/**
	 * Get field group by ID.
	 *
	 * @param int $group_id Group ID.
	 * @return object|null
	 */
	public function get_field_group( $group_id ) {
		global $wpdb;

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

	/**
	 * Create field group.
	 *
	 * @param array $data Group data.
	 * @return int|WP_Error
	 */
	public function create_field_group( $data ) {
		global $wpdb;

		if ( empty( $data['name'] ) ) {
			return new WP_Error( 'missing_name', __( 'Group name is required', 'ggm-commerce' ) );
		}

		$position = sanitize_text_field( $data['position'] ?? 'after_billing' );
		if ( ! in_array( $position, $this->valid_positions, true ) ) {
			$position = 'after_billing';
		}

		$insert_data = array(
			'name'          => sanitize_text_field( $data['name'] ),
			'description'   => sanitize_textarea_field( $data['description'] ?? '' ),
			'position'      => $position,
			'display_order' => intval( $data['display_order'] ?? 0 ),
			'status'        => sanitize_text_field( $data['status'] ?? 'active' ),
			'created_at'    => current_time( 'mysql' ),
			'updated_at'    => current_time( 'mysql' ),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$inserted = $wpdb->insert( "{$wpdb->prefix}ggmc_checkout_field_groups", $insert_data );

		if ( $inserted ) {
			return $wpdb->insert_id;
		}

		return new WP_Error( 'insert_failed', __( 'Failed to create field group', 'ggm-commerce' ) );
	}

	/**
	 * Update field group.
	 *
	 * @param int   $group_id Group ID.
	 * @param array $data     Group data.
	 * @return bool|WP_Error
	 */
	public function update_field_group( $group_id, $data ) {
		global $wpdb;

		$group = $this->get_field_group( $group_id );
		if ( ! $group ) {
			return new WP_Error( 'not_found', __( 'Field group not found', 'ggm-commerce' ) );
		}

		$update_data = array();

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

		if ( isset( $data['description'] ) ) {
			$update_data['description'] = sanitize_textarea_field( $data['description'] );
		}

		if ( isset( $data['position'] ) ) {
			$position = sanitize_text_field( $data['position'] );
			if ( in_array( $position, $this->valid_positions, true ) ) {
				$update_data['position'] = $position;
			}
		}

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

		if ( isset( $data['status'] ) ) {
			$update_data['status'] = sanitize_text_field( $data['status'] );
		}

		if ( ! empty( $update_data ) ) {
			$update_data['updated_at'] = current_time( 'mysql' );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$updated = $wpdb->update(
				"{$wpdb->prefix}ggmc_checkout_field_groups",
				$update_data,
				array( 'id' => $group_id )
			);

			if ( $updated !== false ) {
				return true;
			}

			return new WP_Error( 'update_failed', __( 'Failed to update field group', 'ggm-commerce' ) );
		}

		return true;
	}

	/**
	 * Delete field group.
	 * Note: Removes group membership but does NOT delete the fields themselves.
	 * Fields become ungrouped.
	 *
	 * @param int $group_id Group ID.
	 * @return bool|WP_Error
	 */
	public function delete_field_group( $group_id ) {
		global $wpdb;

		$group = $this->get_field_group( $group_id );
		if ( ! $group ) {
			return new WP_Error( 'not_found', __( 'Field group not found', 'ggm-commerce' ) );
		}

		// Remove all group memberships (fields become ungrouped).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_checkout_field_group_members",
			array( 'group_id' => $group_id ),
			array( '%d' )
		);

		// Delete the group.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->delete(
			"{$wpdb->prefix}ggmc_checkout_field_groups",
			array( 'id' => $group_id )
		);

		if ( $deleted ) {
			return true;
		}

		return new WP_Error( 'delete_failed', __( 'Failed to delete field group', 'ggm-commerce' ) );
	}

	// =========================================================================
	// FIELDS CRUD
	// =========================================================================

	/**
	 * Get all fields.
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public function get_fields( $args = array() ) {
		global $wpdb;

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

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

		$sql = "SELECT * FROM {$wpdb->prefix}ggmc_checkout_fields WHERE 1=1";

		if ( $args['status'] ) {
			$sql .= $wpdb->prepare( ' AND status = %s', $args['status'] );
		}

		$sql .= " ORDER BY {$args['orderby']} {$args['order']}";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- $sql is built safely with wpdb->prepare.
		return $wpdb->get_results( $sql );
	}

	/**
	 * Get field by ID.
	 *
	 * @param int $field_id Field ID.
	 * @return object|null
	 */
	public function get_field( $field_id ) {
		global $wpdb;

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

	/**
	 * Create field.
	 *
	 * @param array $data Field data.
	 * @return int|WP_Error
	 */
	public function create_field( $data ) {
		global $wpdb;

		if ( empty( $data['name'] ) ) {
			return new WP_Error( 'missing_name', __( 'Field name is required', 'ggm-commerce' ) );
		}

		$field_type = sanitize_text_field( $data['field_type'] ?? 'text' );
		if ( ! in_array( $field_type, $this->valid_field_types, true ) ) {
			$field_type = 'text';
		}

		$position = sanitize_text_field( $data['position'] ?? 'after_billing' );
		if ( ! in_array( $position, $this->valid_positions, true ) ) {
			$position = 'after_billing';
		}

		$validation = sanitize_text_field( $data['validation'] ?? '' );
		$valid_validations = array( '', 'none', 'email', 'url', 'number', 'phone' );
		if ( ! in_array( $validation, $valid_validations, true ) ) {
			$validation = '';
		}

		$width = sanitize_text_field( $data['width'] ?? 'full' );
		if ( ! in_array( $width, array( 'full', 'half' ), true ) ) {
			$width = 'full';
		}

		// Handle options for select/radio/checkbox types.
		$options = '';
		if ( in_array( $field_type, array( 'select', 'radio', 'checkbox' ), true ) && ! empty( $data['options'] ) ) {
			if ( is_array( $data['options'] ) ) {
				$options = wp_json_encode( array_map( 'sanitize_text_field', $data['options'] ) );
			} else {
				// Assume newline-separated string.
				$options_array = array_map( 'trim', explode( "\n", $data['options'] ) );
				$options_array = array_map( 'sanitize_text_field', $options_array );
				$options_array = array_filter( $options_array );
				$options       = wp_json_encode( array_values( $options_array ) );
			}
		}

		$insert_data = array(
			'group_id'       => ! empty( $data['group_id'] ) ? intval( $data['group_id'] ) : null,
			'name'           => sanitize_text_field( $data['name'] ),
			'label'          => sanitize_text_field( $data['label'] ?? $data['name'] ),
			'field_type'     => $field_type,
			'placeholder'    => sanitize_text_field( $data['placeholder'] ?? '' ),
			'help_text'      => sanitize_textarea_field( $data['help_text'] ?? '' ),
			'options'        => $options,
			'default_value'  => sanitize_text_field( $data['default_value'] ?? '' ),
			'required'       => ! empty( $data['required'] ) ? 1 : 0,
			'validation'     => $validation,
			'position'       => $position,
			'width'          => $width,
			'admin_editable' => ! empty( $data['admin_editable'] ) ? 1 : 0,
			'show_in_email'  => isset( $data['show_in_email'] ) ? ( ! empty( $data['show_in_email'] ) ? 1 : 0 ) : 1,
			'display_order'  => intval( $data['display_order'] ?? 0 ),
			'status'         => sanitize_text_field( $data['status'] ?? 'active' ),
			'created_at'     => current_time( 'mysql' ),
			'updated_at'     => current_time( 'mysql' ),
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$inserted = $wpdb->insert( "{$wpdb->prefix}ggmc_checkout_fields", $insert_data );

		if ( $inserted ) {
			return $wpdb->insert_id;
		}

		return new WP_Error( 'insert_failed', __( 'Failed to create checkout field', 'ggm-commerce' ) );
	}

	/**
	 * Update field.
	 *
	 * @param int   $field_id Field ID.
	 * @param array $data     Field data.
	 * @return bool|WP_Error
	 */
	public function update_field( $field_id, $data ) {
		global $wpdb;

		$field = $this->get_field( $field_id );
		if ( ! $field ) {
			return new WP_Error( 'not_found', __( 'Checkout field not found', 'ggm-commerce' ) );
		}

		$update_data = array();

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

		if ( isset( $data['label'] ) ) {
			$update_data['label'] = sanitize_text_field( $data['label'] );
		}

		if ( isset( $data['field_type'] ) ) {
			$field_type = sanitize_text_field( $data['field_type'] );
			if ( in_array( $field_type, $this->valid_field_types, true ) ) {
				$update_data['field_type'] = $field_type;
			}
		}

		if ( isset( $data['placeholder'] ) ) {
			$update_data['placeholder'] = sanitize_text_field( $data['placeholder'] );
		}

		if ( isset( $data['help_text'] ) ) {
			$update_data['help_text'] = sanitize_textarea_field( $data['help_text'] );
		}

		if ( isset( $data['options'] ) ) {
			if ( is_array( $data['options'] ) ) {
				$update_data['options'] = wp_json_encode( array_map( 'sanitize_text_field', $data['options'] ) );
			} else {
				$options_array          = array_map( 'trim', explode( "\n", $data['options'] ) );
				$options_array          = array_map( 'sanitize_text_field', $options_array );
				$options_array          = array_filter( $options_array );
				$update_data['options'] = wp_json_encode( array_values( $options_array ) );
			}
		}

		if ( isset( $data['default_value'] ) ) {
			$update_data['default_value'] = sanitize_text_field( $data['default_value'] );
		}

		if ( array_key_exists( 'required', $data ) ) {
			$update_data['required'] = ! empty( $data['required'] ) ? 1 : 0;
		}

		if ( isset( $data['validation'] ) ) {
			$validation = sanitize_text_field( $data['validation'] );
			$valid_validations = array( '', 'none', 'email', 'url', 'number', 'phone' );
			if ( in_array( $validation, $valid_validations, true ) ) {
				$update_data['validation'] = $validation;
			}
		}

		if ( isset( $data['position'] ) ) {
			$position = sanitize_text_field( $data['position'] );
			if ( in_array( $position, $this->valid_positions, true ) ) {
				$update_data['position'] = $position;
			}
		}

		if ( isset( $data['width'] ) ) {
			$width = sanitize_text_field( $data['width'] );
			if ( in_array( $width, array( 'full', 'half' ), true ) ) {
				$update_data['width'] = $width;
			}
		}

		if ( array_key_exists( 'admin_editable', $data ) ) {
			$update_data['admin_editable'] = ! empty( $data['admin_editable'] ) ? 1 : 0;
		}

		if ( array_key_exists( 'show_in_email', $data ) ) {
			$update_data['show_in_email'] = ! empty( $data['show_in_email'] ) ? 1 : 0;
		}

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

		if ( isset( $data['status'] ) ) {
			$update_data['status'] = sanitize_text_field( $data['status'] );
		}

		if ( isset( $data['group_id'] ) ) {
			$update_data['group_id'] = ! empty( $data['group_id'] ) ? intval( $data['group_id'] ) : null;
		}

		if ( ! empty( $update_data ) ) {
			$update_data['updated_at'] = current_time( 'mysql' );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$updated = $wpdb->update(
				"{$wpdb->prefix}ggmc_checkout_fields",
				$update_data,
				array( 'id' => $field_id )
			);

			if ( $updated !== false ) {
				return true;
			}

			return new WP_Error( 'update_failed', __( 'Failed to update checkout field', 'ggm-commerce' ) );
		}

		return true;
	}

	/**
	 * Delete field.
	 * Note: Also removes group memberships. Order field values are preserved (denormalized).
	 *
	 * @param int $field_id Field ID.
	 * @return bool|WP_Error
	 */
	public function delete_field( $field_id ) {
		global $wpdb;

		$field = $this->get_field( $field_id );
		if ( ! $field ) {
			return new WP_Error( 'not_found', __( 'Checkout field not found', 'ggm-commerce' ) );
		}

		// Remove from all groups.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_checkout_field_group_members",
			array( 'field_id' => $field_id ),
			array( '%d' )
		);

		// Delete the field.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->delete(
			"{$wpdb->prefix}ggmc_checkout_fields",
			array( 'id' => $field_id )
		);

		if ( $deleted ) {
			return true;
		}

		return new WP_Error( 'delete_failed', __( 'Failed to delete checkout field', 'ggm-commerce' ) );
	}

	// =========================================================================
	// GROUP MEMBERSHIP (Many-to-Many)
	// =========================================================================

	/**
	 * Add a field to a group.
	 *
	 * @param int $group_id Group ID.
	 * @param int $field_id Field ID.
	 * @return bool|WP_Error
	 */
	public function add_field_to_group( $group_id, $field_id ) {
		global $wpdb;

		// Check if already exists.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}ggmc_checkout_field_group_members WHERE group_id = %d AND field_id = %d",
				$group_id,
				$field_id
			)
		);

		if ( $existing ) {
			return true;
		}

		// Get max display order.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$max_order = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT MAX(display_order) FROM {$wpdb->prefix}ggmc_checkout_field_group_members WHERE group_id = %d",
				$group_id
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$inserted = $wpdb->insert(
			"{$wpdb->prefix}ggmc_checkout_field_group_members",
			array(
				'group_id'      => $group_id,
				'field_id'      => $field_id,
				'display_order' => $max_order + 1,
				'created_at'    => current_time( 'mysql' ),
			)
		);

		return $inserted ? true : new WP_Error( 'insert_failed', __( 'Failed to add field to group', 'ggm-commerce' ) );
	}

	/**
	 * Remove a field from a group.
	 *
	 * @param int $group_id Group ID.
	 * @param int $field_id Field ID.
	 * @return bool
	 */
	public function remove_field_from_group( $group_id, $field_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete(
			"{$wpdb->prefix}ggmc_checkout_field_group_members",
			array(
				'group_id' => $group_id,
				'field_id' => $field_id,
			),
			array( '%d', '%d' )
		);

		return true;
	}

	/**
	 * Get fields in a group (using junction table).
	 *
	 * @param int   $group_id Group ID.
	 * @param array $args     Query arguments.
	 * @return array
	 */
	public function get_group_fields( $group_id, $args = array() ) {
		global $wpdb;

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

		$sql = "SELECT f.*, gm.display_order as group_display_order
				FROM {$wpdb->prefix}ggmc_checkout_fields f
				INNER JOIN {$wpdb->prefix}ggmc_checkout_field_group_members gm ON f.id = gm.field_id
				WHERE gm.group_id = %d";

		if ( $args['status'] ) {
			$sql .= $wpdb->prepare( ' AND f.status = %s', $args['status'] );
		}

		$sql .= ' ORDER BY gm.display_order ASC';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Built with prepare calls.
		return $wpdb->get_results( $wpdb->prepare( $sql, $group_id ) );
	}

	/**
	 * Get ungrouped fields (not in any group).
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public function get_ungrouped_fields( $args = array() ) {
		global $wpdb;

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

		$sql = "SELECT f.* FROM {$wpdb->prefix}ggmc_checkout_fields f
				WHERE f.id NOT IN (
					SELECT field_id FROM {$wpdb->prefix}ggmc_checkout_field_group_members
				)";

		if ( $args['status'] ) {
			$sql .= $wpdb->prepare( ' AND f.status = %s', $args['status'] );
		}

		$sql .= ' ORDER BY f.display_order ASC';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Built safely.
		return $wpdb->get_results( $sql );
	}

	// =========================================================================
	// POSITION-BASED QUERIES (for checkout rendering)
	// =========================================================================

	/**
	 * Get all active fields for a given position, both grouped and ungrouped.
	 * Returns structured data: groups with their fields, and ungrouped fields.
	 *
	 * @param string $position Position identifier.
	 * @return array Array with 'groups' and 'ungrouped' keys.
	 */
	public function get_fields_by_position( $position ) {
		global $wpdb;

		$result = array(
			'groups'    => array(),
			'ungrouped' => array(),
		);

		// Get groups at this position with their fields.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$groups = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}ggmc_checkout_field_groups
				WHERE position = %s AND status = 'active'
				ORDER BY display_order ASC",
				$position
			)
		);

		foreach ( $groups as $group ) {
			$fields = $this->get_group_fields( $group->id );
			if ( ! empty( $fields ) ) {
				$result['groups'][] = array(
					'group'  => $group,
					'fields' => $fields,
				);
			}
		}

		// Get ungrouped fields at this position.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result['ungrouped'] = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT f.* FROM {$wpdb->prefix}ggmc_checkout_fields f
				WHERE f.position = %s
				AND f.status = 'active'
				AND f.id NOT IN (
					SELECT field_id FROM {$wpdb->prefix}ggmc_checkout_field_group_members
				)
				ORDER BY f.display_order ASC",
				$position
			)
		);

		return $result;
	}

	/**
	 * Check if there are any active fields at a given position.
	 *
	 * @param string $position Position identifier.
	 * @return bool
	 */
	public function has_fields_at_position( $position ) {
		global $wpdb;

		// Check ungrouped fields.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ungrouped_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}ggmc_checkout_fields f
				WHERE f.position = %s AND f.status = 'active'
				AND f.id NOT IN (SELECT field_id FROM {$wpdb->prefix}ggmc_checkout_field_group_members)",
				$position
			)
		);

		if ( $ungrouped_count > 0 ) {
			return true;
		}

		// Check grouped fields.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$grouped_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}ggmc_checkout_field_groups g
				INNER JOIN {$wpdb->prefix}ggmc_checkout_field_group_members gm ON g.id = gm.group_id
				INNER JOIN {$wpdb->prefix}ggmc_checkout_fields f ON gm.field_id = f.id
				WHERE g.position = %s AND g.status = 'active' AND f.status = 'active'",
				$position
			)
		);

		return $grouped_count > 0;
	}

	// =========================================================================
	// ORDER FIELD VALUES
	// =========================================================================

	/**
	 * Save order field values.
	 *
	 * @param int   $order_id Order ID.
	 * @param array $values   Array of field_id => value pairs.
	 * @return bool
	 */
	public function save_order_field_values( $order_id, $values ) {
		global $wpdb;

		if ( ! is_array( $values ) || empty( $values ) ) {
			return true;
		}

		foreach ( $values as $field_id => $value ) {
			$field_id = intval( $field_id );
			$field    = $this->get_field( $field_id );

			if ( ! $field ) {
				continue;
			}

			// Sanitize value based on field type.
			$sanitized_value = $this->sanitize_field_value( $field, $value );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->replace(
				"{$wpdb->prefix}ggmc_order_checkout_field_values",
				array(
					'order_id'    => $order_id,
					'field_id'    => $field_id,
					'field_name'  => $field->name,
					'field_value' => $sanitized_value,
					'created_at'  => current_time( 'mysql' ),
				)
			);
		}

		return true;
	}

	/**
	 * Get order field values.
	 *
	 * @param int $order_id Order ID.
	 * @return array
	 */
	public function get_order_field_values( $order_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT v.*, f.label, f.field_type, f.admin_editable, f.show_in_email
				FROM {$wpdb->prefix}ggmc_order_checkout_field_values v
				LEFT JOIN {$wpdb->prefix}ggmc_checkout_fields f ON v.field_id = f.id
				WHERE v.order_id = %d
				ORDER BY v.id ASC",
				$order_id
			)
		);
	}

	/**
	 * Update a single order field value (admin edit).
	 *
	 * @param int    $order_id Order ID.
	 * @param int    $field_id Field ID.
	 * @param string $value    New value.
	 * @return bool
	 */
	public function update_order_field_value( $order_id, $field_id, $value ) {
		global $wpdb;

		$field = $this->get_field( $field_id );

		$sanitized_value = $field ? $this->sanitize_field_value( $field, $value ) : sanitize_text_field( $value );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$updated = $wpdb->update(
			"{$wpdb->prefix}ggmc_order_checkout_field_values",
			array( 'field_value' => $sanitized_value ),
			array(
				'order_id' => $order_id,
				'field_id' => $field_id,
			)
		);

		return $updated !== false;
	}

	// =========================================================================
	// VALIDATION & SANITIZATION
	// =========================================================================

	/**
	 * Validate submitted checkout field values.
	 *
	 * @param array $values Array of field_id => value pairs.
	 * @return array Array with 'valid' (bool) and 'errors' (array of field_id => message).
	 */
	public function validate_field_values( $values ) {
		$errors = array();
		$fields = $this->get_fields();

		foreach ( $fields as $field ) {
			$value = isset( $values[ $field->id ] ) ? $values[ $field->id ] : '';

			// Check required.
			if ( $field->required && '' === trim( $value ) ) {
				/* translators: %s: Field label */
				$errors[ $field->id ] = sprintf( __( '%s is required', 'ggm-commerce' ), $field->label );
				continue;
			}

			// Skip further validation if empty and not required.
			if ( '' === trim( $value ) ) {
				continue;
			}

			// Type-specific validation.
			$error = $this->validate_field_value( $field, $value );
			if ( $error ) {
				$errors[ $field->id ] = $error;
			}
		}

		return array(
			'valid'  => empty( $errors ),
			'errors' => $errors,
		);
	}

	/**
	 * Validate a single field value by type.
	 *
	 * @param object $field Field object.
	 * @param mixed  $value Submitted value.
	 * @return string|null Error message or null if valid.
	 */
	public function validate_field_value( $field, $value ) {
		// Validation rule takes precedence.
		if ( ! empty( $field->validation ) && 'none' !== $field->validation ) {
			switch ( $field->validation ) {
				case 'email':
					if ( ! is_email( $value ) ) {
						/* translators: %s: Field label */
						return sprintf( __( '%s must be a valid email address', 'ggm-commerce' ), $field->label );
					}
					break;
				case 'url':
					if ( ! filter_var( $value, FILTER_VALIDATE_URL ) ) {
						/* translators: %s: Field label */
						return sprintf( __( '%s must be a valid URL', 'ggm-commerce' ), $field->label );
					}
					break;
				case 'number':
					if ( ! is_numeric( $value ) ) {
						/* translators: %s: Field label */
						return sprintf( __( '%s must be a number', 'ggm-commerce' ), $field->label );
					}
					break;
				case 'phone':
					if ( ! preg_match( '/^[+\d\s\-().]+$/', $value ) ) {
						/* translators: %s: Field label */
						return sprintf( __( '%s must be a valid phone number', 'ggm-commerce' ), $field->label );
					}
					break;
			}
		}

		// Field type validation.
		switch ( $field->field_type ) {
			case 'email':
				if ( ! is_email( $value ) ) {
					/* translators: %s: Field label */
					return sprintf( __( '%s must be a valid email address', 'ggm-commerce' ), $field->label );
				}
				break;
			case 'url':
				if ( ! filter_var( $value, FILTER_VALIDATE_URL ) ) {
					/* translators: %s: Field label */
					return sprintf( __( '%s must be a valid URL', 'ggm-commerce' ), $field->label );
				}
				break;
			case 'number':
				if ( ! is_numeric( $value ) ) {
					/* translators: %s: Field label */
					return sprintf( __( '%s must be a number', 'ggm-commerce' ), $field->label );
				}
				break;
			case 'select':
			case 'radio':
				// Validate against available options.
				$options = json_decode( $field->options, true );
				if ( is_array( $options ) && ! in_array( $value, $options, true ) ) {
					/* translators: %s: Field label */
					return sprintf( __( '%s has an invalid selection', 'ggm-commerce' ), $field->label );
				}
				break;
			case 'date':
				if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
					/* translators: %s: Field label */
					return sprintf( __( '%s must be a valid date', 'ggm-commerce' ), $field->label );
				}
				break;
		}

		return null;
	}

	/**
	 * Sanitize a field value based on field type.
	 *
	 * @param object $field Field object.
	 * @param mixed  $value Raw value.
	 * @return string Sanitized value.
	 */
	public function sanitize_field_value( $field, $value ) {
		if ( is_array( $value ) ) {
			// Checkbox with multiple values.
			return wp_json_encode( array_map( 'sanitize_text_field', $value ) );
		}

		switch ( $field->field_type ) {
			case 'email':
				return sanitize_email( $value );
			case 'url':
				return esc_url_raw( $value );
			case 'textarea':
				return sanitize_textarea_field( $value );
			case 'number':
				return is_numeric( $value ) ? $value : '';
			default:
				return sanitize_text_field( $value );
		}
	}

	// =========================================================================
	// EMAIL HELPER
	// =========================================================================

	/**
	 * Get formatted checkout fields HTML for email.
	 *
	 * @param int $order_id Order ID.
	 * @return string HTML table of checkout field values.
	 */
	public function get_email_fields_html( $order_id ) {
		$values = $this->get_order_field_values( $order_id );

		if ( empty( $values ) ) {
			return '';
		}

		// Filter to only fields with show_in_email.
		$email_values = array_filter(
			$values,
			function ( $v ) {
				return ! isset( $v->show_in_email ) || $v->show_in_email;
			}
		);

		if ( empty( $email_values ) ) {
			return '';
		}

		$html  = '<table class="order-table" style="width:100%; margin:24px 0; border-collapse:collapse;">';
		$html .= '<thead><tr><th colspan="2" style="background:#f9fafb; padding:12px; text-align:left; font-size:12px; font-weight:600; color:#6b7280; text-transform:uppercase; letter-spacing:0.5px; border-bottom:2px solid #e5e7eb;">' . esc_html__( 'Additional Information', 'ggm-commerce' ) . '</th></tr></thead>';
		$html .= '<tbody>';

		foreach ( $email_values as $v ) {
			$label = ! empty( $v->label ) ? $v->label : $v->field_name;
			$value = $v->field_value;

			// Decode JSON arrays (checkboxes).
			$decoded = json_decode( $value, true );
			if ( is_array( $decoded ) ) {
				$value = implode( ', ', $decoded );
			}

			$html .= '<tr>';
			$html .= '<td style="padding:12px; border-bottom:1px solid #e5e7eb; font-size:14px; color:#1f2937; font-weight:600; width:35%;">' . esc_html( $label ) . '</td>';
			$html .= '<td style="padding:12px; border-bottom:1px solid #e5e7eb; font-size:14px; color:#1f2937;">' . esc_html( $value ) . '</td>';
			$html .= '</tr>';
		}

		$html .= '</tbody></table>';

		return $html;
	}

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

	/**
	 * AJAX: Create field group.
	 */
	public function ajax_create_field_group() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$result = $this->create_field_group( wp_unslash( $_POST ) );

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		} else {
			wp_send_json_success(
				array(
					'message'  => __( 'Field group created successfully', 'ggm-commerce' ),
					'group_id' => $result,
				)
			);
		}
	}

	/**
	 * AJAX: Update field group.
	 */
	public function ajax_update_field_group() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$group_id = intval( $_POST['group_id'] ?? 0 );
		$result   = $this->update_field_group( $group_id, wp_unslash( $_POST ) );

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		} else {
			wp_send_json_success( array( 'message' => __( 'Field group updated successfully', 'ggm-commerce' ) ) );
		}
	}

	/**
	 * AJAX: Delete field group.
	 */
	public function ajax_delete_field_group() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$group_id = intval( $_POST['group_id'] ?? 0 );
		$result   = $this->delete_field_group( $group_id );

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		} else {
			wp_send_json_success( array( 'message' => __( 'Field group deleted successfully', 'ggm-commerce' ) ) );
		}
	}

	/**
	 * AJAX: Reorder field groups.
	 */
	public function ajax_reorder_field_groups() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$order = isset( $_POST['order'] ) ? array_map( 'intval', wp_unslash( $_POST['order'] ) ) : array();

		global $wpdb;
		$position = 0;
		foreach ( $order as $group_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				"{$wpdb->prefix}ggmc_checkout_field_groups",
				array( 'display_order' => $position ),
				array( 'id' => $group_id )
			);
			++$position;
		}

		wp_send_json_success( array( 'message' => __( 'Order updated', 'ggm-commerce' ) ) );
	}

	/**
	 * AJAX: Create field.
	 */
	public function ajax_create_field() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$result = $this->create_field( wp_unslash( $_POST ) );

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		} else {
			wp_send_json_success(
				array(
					'message'  => __( 'Checkout field created successfully', 'ggm-commerce' ),
					'field_id' => $result,
				)
			);
		}
	}

	/**
	 * AJAX: Update field.
	 */
	public function ajax_update_field() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$field_id = intval( $_POST['field_id'] ?? 0 );
		$result   = $this->update_field( $field_id, wp_unslash( $_POST ) );

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		} else {
			wp_send_json_success( array( 'message' => __( 'Checkout field updated successfully', 'ggm-commerce' ) ) );
		}
	}

	/**
	 * AJAX: Delete field.
	 */
	public function ajax_delete_field() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$field_id = intval( $_POST['field_id'] ?? 0 );
		$result   = $this->delete_field( $field_id );

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		} else {
			wp_send_json_success( array( 'message' => __( 'Checkout field deleted successfully', 'ggm-commerce' ) ) );
		}
	}

	/**
	 * AJAX: Reorder fields.
	 */
	public function ajax_reorder_fields() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$order    = isset( $_POST['order'] ) ? array_map( 'intval', wp_unslash( $_POST['order'] ) ) : array();
		$group_id = isset( $_POST['group_id'] ) ? intval( $_POST['group_id'] ) : 0;

		global $wpdb;

		if ( $group_id > 0 ) {
			// Reorder within group (update junction table display_order).
			$position = 0;
			foreach ( $order as $field_id ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->update(
					"{$wpdb->prefix}ggmc_checkout_field_group_members",
					array( 'display_order' => $position ),
					array(
						'group_id' => $group_id,
						'field_id' => $field_id,
					)
				);
				++$position;
			}
		} else {
			// Reorder ungrouped fields (update field display_order).
			$position = 0;
			foreach ( $order as $field_id ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$wpdb->update(
					"{$wpdb->prefix}ggmc_checkout_fields",
					array( 'display_order' => $position ),
					array( 'id' => $field_id )
				);
				++$position;
			}
		}

		wp_send_json_success( array( 'message' => __( 'Order updated', 'ggm-commerce' ) ) );
	}

	/**
	 * AJAX: Add field to group.
	 */
	public function ajax_add_field_to_group() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$group_id = intval( $_POST['group_id'] ?? 0 );
		$field_id = intval( $_POST['field_id'] ?? 0 );

		$result = $this->add_field_to_group( $group_id, $field_id );

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( array( 'message' => $result->get_error_message() ) );
		} else {
			wp_send_json_success( array( 'message' => __( 'Field added to group', 'ggm-commerce' ) ) );
		}
	}

	/**
	 * AJAX: Remove field from group.
	 */
	public function ajax_remove_field_from_group() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$group_id = intval( $_POST['group_id'] ?? 0 );
		$field_id = intval( $_POST['field_id'] ?? 0 );

		$this->remove_field_from_group( $group_id, $field_id );

		wp_send_json_success( array( 'message' => __( 'Field removed from group', 'ggm-commerce' ) ) );
	}

	/**
	 * AJAX: Update order field value (admin edit).
	 */
	public function ajax_update_order_field() {
		check_ajax_referer( 'ggm_commerce_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => __( 'Insufficient permissions', 'ggm-commerce' ) ) );
		}

		$order_id = intval( $_POST['order_id'] ?? 0 );
		$field_id = intval( $_POST['field_id'] ?? 0 );
		$value    = isset( $_POST['field_value'] ) ? wp_unslash( $_POST['field_value'] ) : '';

		$result = $this->update_order_field_value( $order_id, $field_id, $value );

		if ( $result ) {
			wp_send_json_success( array( 'message' => __( 'Field value updated', 'ggm-commerce' ) ) );
		} else {
			wp_send_json_error( array( 'message' => __( 'Failed to update field value', 'ggm-commerce' ) ) );
		}
	}
}
