<?php
/**
 * Trust segment column on WooCommerce orders list.
 *
 * @package TrustLens
 * @since   1.2.0
 */

defined( 'ABSPATH' ) || exit;

/**
 * Order trust column class.
 *
 * Adds a color-coded trust segment badge column to the WooCommerce orders list,
 * with dropdown filtering and sorting by segment severity.
 *
 * Stores `_trustlens_email_hash` as order meta so that SQL JOINs can connect
 * orders to the TrustLens customers table (MySQL cannot replicate PHP-side
 * HMAC-SHA256 hashing). A one-time backfill runs via Action Scheduler for
 * orders created before this feature was added.
 *
 * @since 1.2.0
 */
class TrustLens_Order_Trust_Column {

	/**
	 * Single instance.
	 *
	 * @var TrustLens_Order_Trust_Column|null
	 */
	private static ?TrustLens_Order_Trust_Column $instance = null;

	/**
	 * Prefetched customer data keyed by email hash.
	 *
	 * @var array<string, object|null>
	 */
	private array $customer_cache = array();

	/**
	 * Valid segment values for filtering.
	 *
	 * @var array<string, int>
	 */
	private const SEGMENT_WEIGHTS = array(
		'critical' => 0,
		'risk'     => 1,
		'caution'  => 2,
		'normal'   => 3,
		'trusted'  => 4,
		'vip'      => 5,
	);

	/**
	 * Order meta key for the email hash.
	 *
	 * @var string
	 */
	private const META_KEY = '_trustlens_email_hash';

	/**
	 * Option key for tracking backfill completion.
	 *
	 * @var string
	 */
	private const BACKFILL_OPTION = 'trustlens_email_hash_backfill_done';

	/**
	 * Pending segment filter value for HPOS query modification.
	 *
	 * @var string
	 */
	private string $pending_segment_filter = '';

	/**
	 * Pending orderby value for HPOS query modification.
	 *
	 * @var string
	 */
	private string $pending_orderby = '';

	/**
	 * Pending segment filter value for legacy query modification.
	 *
	 * @var string
	 */
	private string $legacy_segment_filter = '';

	/**
	 * Pending orderby value for legacy query modification.
	 *
	 * @var string
	 */
	private string $legacy_orderby = '';

	/**
	 * Pending sort order for legacy query modification.
	 *
	 * @var string
	 */
	private string $legacy_order = 'ASC';

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

	/**
	 * Constructor.
	 *
	 * @since 1.2.0
	 */
	private function __construct() {
		$this->init_hooks();
	}

	/**
	 * Initialize hooks.
	 *
	 * @since 1.2.0
	 */
	private function init_hooks(): void {
		// Store email hash on new orders (runs on frontend checkout).
		add_action( 'woocommerce_checkout_order_created', array( $this, 'store_email_hash_meta' ), 5 );

		// Backfill action (runs via Action Scheduler). Scheduling happens once at
		// activation/upgrade in TrustLens_Install, never on admin_init, to avoid
		// the fan-out runaway fixed in 1.1.9 (see BUG-ACTIONSCHEDULER-RUNAWAY.md).
		add_action( 'trustlens/backfill_email_hash_meta', array( $this, 'process_backfill_batch' ) );

		// Admin-only UI hooks.
		if ( ! is_admin() ) {
			return;
		}

		// HPOS (High-Performance Order Storage) hooks.
		add_filter( 'manage_woocommerce_page_wc-orders_columns', array( $this, 'add_column' ) );
		add_action( 'manage_woocommerce_page_wc-orders_custom_column', array( $this, 'render_column' ), 10, 2 );
		add_filter( 'manage_woocommerce_page_wc-orders_sortable_columns', array( $this, 'add_sortable_column' ) );
		add_action( 'woocommerce_order_list_table_restrict_manage_orders', array( $this, 'render_filter_dropdown' ), 10, 2 );
		add_filter( 'woocommerce_order_list_table_prepare_items_query_args', array( $this, 'apply_hpos_filter_and_sort' ) );

		// Legacy CPT (Custom Post Type) hooks.
		add_filter( 'manage_edit-shop_order_columns', array( $this, 'add_column' ) );
		add_action( 'manage_shop_order_posts_custom_column', array( $this, 'render_legacy_column' ), 10, 2 );
		add_filter( 'manage_edit-shop_order_sortable_columns', array( $this, 'add_sortable_column' ) );
		add_action( 'restrict_manage_posts', array( $this, 'render_legacy_filter_dropdown' ) );
		add_action( 'pre_get_posts', array( $this, 'apply_legacy_filter_and_sort' ) );
	}

	// -------------------------------------------------------------------------
	// Email hash meta storage & backfill.
	// -------------------------------------------------------------------------

	/**
	 * Store email hash as order meta on new orders.
	 *
	 * Runs at priority 5 (before modules at 10-20) so the meta is available
	 * for any downstream processing.
	 *
	 * @since 1.2.0
	 * @param WC_Order $order Order object.
	 */
	public function store_email_hash_meta( WC_Order $order ): void {
		$email = $order->get_billing_email();
		if ( empty( $email ) ) {
			return;
		}

		$email_hash = wstl_get_email_hash( $email );
		if ( '' === $email_hash ) {
			return;
		}

		$order->update_meta_data( self::META_KEY, $email_hash );
		$order->save_meta_data();
	}

	/**
	 * Process a batch of orders for email hash backfill.
	 *
	 * Always fetches page 1 because the NOT EXISTS meta query produces a shrinking
	 * result set — processed orders gain the meta key and drop out of the query.
	 *
	 * Guarantees termination even under failure:
	 * - Per-order try/catch writes an empty-string sentinel on failure so the
	 *   bad order drops out of the NOT EXISTS query instead of blocking forever.
	 * - Outer try/catch marks the backfill done on catastrophic failure so the
	 *   scheduler loop cannot reissue forever.
	 * - If zero orders in the batch succeed, the backfill is marked done to
	 *   prevent a retry loop on a batch that's guaranteed to keep failing.
	 *
	 * Scheduling is owned by TrustLens_Install::schedule_one_time_backfills()
	 * on activation / upgrade. For a manual re-run, delete the backfill option
	 * and call that method again.
	 *
	 * @since 1.2.0
	 */
	public function process_backfill_batch(): void {
		$batch_size = 100;

		try {
			$orders = wc_get_orders( array(
				'limit'      => $batch_size,
				'page'       => 1,
				'return'     => 'objects',
				'type'       => 'shop_order',
				'status'     => array( 'completed', 'processing', 'on-hold', 'refunded', 'cancelled' ),
				'orderby'    => 'ID',
				'order'      => 'ASC',
				'meta_query' => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- One-time backfill only.
					array(
						'key'     => self::META_KEY,
						'compare' => 'NOT EXISTS',
					),
				),
			) );

			if ( empty( $orders ) ) {
				update_option( self::BACKFILL_OPTION, true );
				return;
			}

			$processed = 0;

			foreach ( $orders as $order ) {
				try {
					$email      = $order->get_billing_email();
					$email_hash = ! empty( $email ) ? wstl_get_email_hash( $email ) : '';

					$order->update_meta_data( self::META_KEY, $email_hash );
					$order->save_meta_data();
					++$processed;
				} catch ( \Throwable $order_error ) {
					// Try to tag the order with the empty sentinel so it drops out of
					// the NOT EXISTS query and doesn't block the backfill forever.
					try {
						$order->update_meta_data( self::META_KEY, '' );
						$order->save_meta_data();
					} catch ( \Throwable $inner_error ) {
						// Give up on this single order — the outer termination guard
						// (0 === $processed) will catch the case where every order fails.
						unset( $inner_error );
					}
					// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Operational log.
					error_log( sprintf(
						'[TrustLens] Email hash backfill failed on order %d: %s',
						(int) $order->get_id(),
						$order_error->getMessage()
					) );
				}
			}

			// Terminate if the batch wasn't full (no more orders remain) or if
			// every order in this batch failed (avoids infinite retry loops).
			if ( count( $orders ) < $batch_size || 0 === $processed ) {
				update_option( self::BACKFILL_OPTION, true );
				return;
			}

			// Self-continuation with replace-semantics (atomic, race-free).
			wstl_ensure_single_action( 'trustlens/backfill_email_hash_meta', array(), 'trustlens' );
		} catch ( \Throwable $batch_error ) {
			// Catastrophic failure — mark done so the scheduler stops retrying
			// forever. Merchants can delete the BACKFILL_OPTION and call
			// TrustLens_Install::schedule_one_time_backfills() to retry.
			update_option( self::BACKFILL_OPTION, true );
			// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- Operational log.
			error_log( '[TrustLens] Email hash backfill fatal error: ' . $batch_error->getMessage() );
		}
	}

	// -------------------------------------------------------------------------
	// Column registration.
	// -------------------------------------------------------------------------

	/**
	 * Add trust column to orders list.
	 *
	 * @since 1.2.0
	 * @param array $columns Existing columns.
	 * @return array Modified columns.
	 */
	public function add_column( array $columns ): array {
		$new_columns = array();

		foreach ( $columns as $key => $label ) {
			$new_columns[ $key ] = $label;

			// Insert after 'order_number' (HPOS) or 'order_title' (legacy).
			if ( 'order_number' === $key || 'order_title' === $key ) {
				$new_columns['trustlens_segment'] = __( 'Trust', 'trustlens' );
			}
		}

		// Fallback if neither key was found.
		if ( ! isset( $new_columns['trustlens_segment'] ) ) {
			$new_columns['trustlens_segment'] = __( 'Trust', 'trustlens' );
		}

		return $new_columns;
	}

	/**
	 * Make trust column sortable.
	 *
	 * @since 1.2.0
	 * @param array $columns Sortable columns.
	 * @return array Modified sortable columns.
	 */
	public function add_sortable_column( array $columns ): array {
		$columns['trustlens_segment'] = 'trustlens_segment';
		return $columns;
	}

	// -------------------------------------------------------------------------
	// Column rendering.
	// -------------------------------------------------------------------------

	/**
	 * Render trust column content (HPOS).
	 *
	 * @since 1.2.0
	 * @param string   $column_name Column identifier.
	 * @param WC_Order $order       Order object.
	 */
	public function render_column( string $column_name, $order ): void {
		if ( 'trustlens_segment' !== $column_name ) {
			return;
		}

		if ( ! $order instanceof WC_Order ) {
			return;
		}

		$this->render_badge( $order );
	}

	/**
	 * Render trust column content (legacy CPT).
	 *
	 * @since 1.2.0
	 * @param string $column_name Column identifier.
	 * @param int    $post_id     Post ID.
	 */
	public function render_legacy_column( string $column_name, $post_id ): void {
		if ( 'trustlens_segment' !== $column_name ) {
			return;
		}

		$order = wc_get_order( $post_id );
		if ( ! $order ) {
			return;
		}

		$this->render_badge( $order );
	}

	/**
	 * Render the trust badge for an order.
	 *
	 * @since 1.2.0
	 * @param WC_Order $order Order object.
	 */
	private function render_badge( WC_Order $order ): void {
		$email = $order->get_billing_email();

		if ( empty( $email ) ) {
			$this->render_order_flags( $order );
			return;
		}

		$email_hash = wstl_get_email_hash( $email );
		$customer   = $this->get_cached_customer( $email_hash );

		if ( ! $customer || empty( $customer->segment ) ) {
			$this->render_new_badge();
			$this->render_order_flags( $order );
			return;
		}

		$segment     = $customer->segment;
		$label       = wstl_get_segment_label( $segment );
		$color       = wstl_get_segment_color( $segment );
		$is_muted    = in_array( $segment, array( 'normal', 'trusted', 'vip' ), true );
		$opacity     = $is_muted ? '0.6' : '1';
		$profile_url = admin_url( 'admin.php?page=trustlens-customers&view=profile&hash=' . urlencode( $email_hash ) );

		printf(
			'<a href="%s" style="display:inline-block;padding:2px 8px;border-radius:12px;font-size:12px;font-weight:600;line-height:1.6;text-decoration:none;color:#fff;background-color:%s;opacity:%s;" title="%s">%s</a>',
			esc_url( $profile_url ),
			esc_attr( $color ),
			esc_attr( $opacity ),
			/* translators: %s: segment label */
			esc_attr( sprintf( __( 'Trust segment: %s — Click to view profile', 'trustlens' ), $label ) ),
			esc_html( $label )
		);

		$this->render_order_flags( $order );
	}

	/**
	 * Render the "Needs verification" / "Flagged" indicators when an automation
	 * rule has marked this order. Reads the order meta set by
	 * TrustLens_Automation::execute_order_action(), giving the require_verification
	 * and flag_order actions a visible effect on the orders list instead of
	 * writing meta that nothing surfaces.
	 *
	 * @since 1.5.1
	 * @param WC_Order $order Order being rendered.
	 * @return void
	 */
	private function render_order_flags( WC_Order $order ): void {
		$needs_verification = (bool) $order->get_meta( '_trustlens_requires_verification' );
		$flagged            = (bool) $order->get_meta( '_trustlens_flagged' );

		if ( ! $needs_verification && ! $flagged ) {
			return;
		}

		$reason = (string) $order->get_meta( '_trustlens_flag_reason' );

		if ( $needs_verification ) {
			$title = '' !== $reason
				/* translators: %s: automation rule name */
				? sprintf( __( 'Needs verification — automation rule "%s"', 'trustlens' ), $reason )
				: __( 'Needs verification (automation)', 'trustlens' );
			printf(
				'<span style="display:inline-block;margin-left:4px;padding:2px 6px;border-radius:10px;font-size:11px;font-weight:600;line-height:1.6;color:#fff;background-color:%s;" title="%s">%s</span>',
				esc_attr( '#b54708' ),
				esc_attr( $title ),
				esc_html__( '⚠ Verify', 'trustlens' )
			);
		}

		if ( $flagged ) {
			$title = '' !== $reason
				/* translators: %s: automation rule name */
				? sprintf( __( 'Flagged for review — automation rule "%s"', 'trustlens' ), $reason )
				: __( 'Flagged for review (automation)', 'trustlens' );
			printf(
				'<span style="display:inline-block;margin-left:4px;padding:2px 6px;border-radius:10px;font-size:11px;font-weight:600;line-height:1.6;color:#fff;background-color:%s;" title="%s">%s</span>',
				esc_attr( '#9a3412' ),
				esc_attr( $title ),
				esc_html__( '🚩 Flagged', 'trustlens' )
			);
		}
	}

	/**
	 * Render the "New" badge for unscored customers.
	 *
	 * @since 1.2.0
	 */
	private function render_new_badge(): void {
		printf(
			'<span style="display:inline-block;padding:2px 8px;border-radius:12px;font-size:12px;font-weight:600;line-height:1.6;color:#fff;background-color:%s;opacity:0.6;">%s</span>',
			esc_attr( '#adb5bd' ),
			esc_html__( 'New', 'trustlens' )
		);
	}

	/**
	 * Get customer from cache or database.
	 *
	 * @since 1.2.0
	 * @param string $email_hash Customer email hash.
	 * @return object|null Customer object or null.
	 */
	private function get_cached_customer( string $email_hash ): ?object {
		if ( array_key_exists( $email_hash, $this->customer_cache ) ) {
			return $this->customer_cache[ $email_hash ];
		}

		$customer = wstl_get_customer( $email_hash );
		$this->customer_cache[ $email_hash ] = $customer;
		return $customer;
	}

	// -------------------------------------------------------------------------
	// Filter dropdown.
	// -------------------------------------------------------------------------

	/**
	 * Render segment filter dropdown (HPOS).
	 *
	 * @since 1.2.0
	 * @param string $order_type Order type.
	 * @param string $which      Top or bottom.
	 */
	public function render_filter_dropdown( string $order_type = '', string $which = '' ): void {
		if ( '' !== $order_type && 'shop_order' !== $order_type ) {
			return;
		}

		$this->output_dropdown();
	}

	/**
	 * Render segment filter dropdown (legacy CPT).
	 *
	 * @since 1.2.0
	 */
	public function render_legacy_filter_dropdown(): void {
		global $typenow;

		if ( 'shop_order' !== $typenow ) {
			return;
		}

		$this->output_dropdown();
	}

	/**
	 * Output the segment filter dropdown HTML.
	 *
	 * @since 1.2.0
	 */
	private function output_dropdown(): void {
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only filter parameter.
		$current = isset( $_GET['trustlens_segment'] ) ? sanitize_text_field( wp_unslash( $_GET['trustlens_segment'] ) ) : '';

		$segments = array(
			'critical' => __( 'Critical', 'trustlens' ),
			'risk'     => __( 'Risk', 'trustlens' ),
			'caution'  => __( 'Caution', 'trustlens' ),
			'normal'   => __( 'Normal', 'trustlens' ),
			'trusted'  => __( 'Trusted', 'trustlens' ),
			'vip'      => __( 'VIP', 'trustlens' ),
		);

		echo '<select name="trustlens_segment">';
		echo '<option value="">' . esc_html__( 'All trust segments', 'trustlens' ) . '</option>';

		foreach ( $segments as $value => $label ) {
			printf(
				'<option value="%s"%s>%s</option>',
				esc_attr( $value ),
				selected( $current, $value, false ),
				esc_html( $label )
			);
		}

		echo '</select>';
	}

	// -------------------------------------------------------------------------
	// HPOS filtering & sorting.
	// -------------------------------------------------------------------------

	/**
	 * Apply segment filter and sort to HPOS query.
	 *
	 * @since 1.2.0
	 * @param array $query_args Query arguments.
	 * @return array Modified query arguments.
	 */
	public function apply_hpos_filter_and_sort( array $query_args ): array {
		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only filter parameter.
		$segment_filter = isset( $_GET['trustlens_segment'] ) ? sanitize_text_field( wp_unslash( $_GET['trustlens_segment'] ) ) : '';

		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only sort parameter.
		$orderby = isset( $_GET['orderby'] ) ? sanitize_text_field( wp_unslash( $_GET['orderby'] ) ) : '';

		if ( '' === $segment_filter && 'trustlens_segment' !== $orderby ) {
			return $query_args;
		}

		// Store state for the named handler method.
		$this->pending_segment_filter = $segment_filter;
		$this->pending_orderby        = $orderby;
		add_filter( 'woocommerce_orders_table_query_clauses', array( $this, 'handle_hpos_query_clauses' ) );

		return $query_args;
	}

	/**
	 * Named handler for HPOS query clauses — removes itself after first execution.
	 *
	 * @since 1.2.0
	 * @param array $clauses Query clauses.
	 * @return array Modified clauses.
	 */
	public function handle_hpos_query_clauses( array $clauses ): array {
		remove_filter( 'woocommerce_orders_table_query_clauses', array( $this, 'handle_hpos_query_clauses' ) );
		return $this->modify_hpos_query_clauses( $clauses, $this->pending_segment_filter, $this->pending_orderby );
	}

	/**
	 * Modify HPOS query clauses for filtering and sorting.
	 *
	 * Joins wc_orders_meta (email hash) → trustlens_customers (segment).
	 *
	 * @since 1.2.0
	 * @param array  $clauses        Query clauses.
	 * @param string $segment_filter Segment to filter by (empty = no filter).
	 * @param string $orderby        Current orderby value.
	 * @return array Modified clauses.
	 */
	private function modify_hpos_query_clauses( array $clauses, string $segment_filter, string $orderby ): array {
		global $wpdb;

		$orders_table    = $wpdb->prefix . 'wc_orders';
		$meta_table      = $wpdb->prefix . 'wc_orders_meta';
		$customers_table = $wpdb->prefix . 'trustlens_customers';

		// JOIN order meta (email hash) → trustlens_customers.
		$join  = " LEFT JOIN {$meta_table} AS tl_hash_meta ON ({$orders_table}.id = tl_hash_meta.order_id AND tl_hash_meta.meta_key = '" . esc_sql( self::META_KEY ) . "')";
		$join .= " LEFT JOIN {$customers_table} AS tl_customers ON tl_customers.email_hash = tl_hash_meta.meta_value";

		if ( ! empty( $clauses['join'] ) ) {
			$clauses['join'] .= $join;
		} else {
			$clauses['join'] = $join;
		}

		// Filter by segment.
		if ( '' !== $segment_filter && array_key_exists( $segment_filter, self::SEGMENT_WEIGHTS ) ) {
			$where = $wpdb->prepare( ' AND tl_customers.segment = %s', $segment_filter );
			if ( ! empty( $clauses['where'] ) ) {
				$clauses['where'] .= $where;
			} else {
				$clauses['where'] = $where;
			}
		}

		// Sort by segment severity (prepend to preserve secondary sort).
		if ( 'trustlens_segment' === $orderby ) {
			// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only sort parameter.
			$order = isset( $_GET['order'] ) && 'desc' === strtolower( sanitize_text_field( wp_unslash( $_GET['order'] ) ) ) ? 'DESC' : 'ASC';

			$case_sql        = $this->get_segment_case_sql( 'tl_customers.segment' );
			$existing_order  = ! empty( $clauses['orderby'] ) ? ', ' . $clauses['orderby'] : '';
			$clauses['orderby'] = "{$case_sql} {$order}{$existing_order}";
		}

		return $clauses;
	}

	// -------------------------------------------------------------------------
	// Legacy CPT filtering & sorting.
	// -------------------------------------------------------------------------

	/**
	 * Apply segment filter and sort to legacy CPT query.
	 *
	 * @since 1.2.0
	 * @param WP_Query $query WordPress query.
	 */
	public function apply_legacy_filter_and_sort( $query ): void {
		if ( ! is_admin() || ! $query->is_main_query() ) {
			return;
		}

		if ( 'shop_order' !== ( $query->get( 'post_type' ) ?: '' ) ) {
			return;
		}

		// phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only filter parameter.
		$segment_filter = isset( $_GET['trustlens_segment'] ) ? sanitize_text_field( wp_unslash( $_GET['trustlens_segment'] ) ) : '';
		$orderby        = $query->get( 'orderby' );

		if ( '' === $segment_filter && 'trustlens_segment' !== $orderby ) {
			return;
		}

		// Store state for named handler methods.
		$this->legacy_segment_filter = $segment_filter;
		$this->legacy_orderby        = $orderby;
		$this->legacy_order          = strtoupper( $query->get( 'order' ) ?: 'ASC' );
		if ( ! in_array( $this->legacy_order, array( 'ASC', 'DESC' ), true ) ) {
			$this->legacy_order = 'ASC';
		}

		add_filter( 'posts_join', array( $this, 'handle_legacy_join' ) );

		if ( '' !== $segment_filter && array_key_exists( $segment_filter, self::SEGMENT_WEIGHTS ) ) {
			add_filter( 'posts_where', array( $this, 'handle_legacy_where' ) );
		}

		if ( 'trustlens_segment' === $orderby ) {
			add_filter( 'posts_orderby', array( $this, 'handle_legacy_orderby' ) );
		}
	}

	/**
	 * Named handler for legacy JOIN — removes itself after execution.
	 *
	 * @since 1.2.0
	 * @param string $join Existing JOIN clause.
	 * @return string Modified JOIN clause.
	 */
	public function handle_legacy_join( string $join ): string {
		remove_filter( 'posts_join', array( $this, 'handle_legacy_join' ) );

		global $wpdb;

		$customers_table = $wpdb->prefix . 'trustlens_customers';

		$join .= " LEFT JOIN {$wpdb->postmeta} AS tl_hash_meta ON ({$wpdb->posts}.ID = tl_hash_meta.post_id AND tl_hash_meta.meta_key = '" . esc_sql( self::META_KEY ) . "')";
		$join .= " LEFT JOIN {$customers_table} AS tl_customers ON tl_customers.email_hash = tl_hash_meta.meta_value";

		return $join;
	}

	/**
	 * Named handler for legacy WHERE — removes itself after execution.
	 *
	 * @since 1.2.0
	 * @param string $where Existing WHERE clause.
	 * @return string Modified WHERE clause.
	 */
	public function handle_legacy_where( string $where ): string {
		remove_filter( 'posts_where', array( $this, 'handle_legacy_where' ) );

		global $wpdb;

		$where .= $wpdb->prepare( ' AND tl_customers.segment = %s', $this->legacy_segment_filter );
		return $where;
	}

	/**
	 * Named handler for legacy ORDER BY — removes itself after execution.
	 *
	 * @since 1.2.0
	 * @param string $orderby_clause Existing ORDER BY clause.
	 * @return string Modified ORDER BY clause.
	 */
	public function handle_legacy_orderby( string $orderby_clause ): string {
		remove_filter( 'posts_orderby', array( $this, 'handle_legacy_orderby' ) );

		$case_sql = $this->get_segment_case_sql( 'tl_customers.segment' );
		$existing = ! empty( $orderby_clause ) ? ', ' . $orderby_clause : '';
		return "{$case_sql} {$this->legacy_order}{$existing}";
	}

	// -------------------------------------------------------------------------
	// Helpers.
	// -------------------------------------------------------------------------

	/**
	 * Build SQL CASE expression for segment severity ordering.
	 *
	 * @since 1.2.0
	 * @param string $column Column reference (e.g., 'tl_customers.segment').
	 * @return string SQL CASE expression.
	 */
	private function get_segment_case_sql( string $column ): string {
		return "CASE {$column}
			WHEN 'critical' THEN 0
			WHEN 'risk' THEN 1
			WHEN 'caution' THEN 2
			WHEN 'normal' THEN 3
			WHEN 'trusted' THEN 4
			WHEN 'vip' THEN 5
			ELSE 6
		END";
	}
}
