<?php

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

require_once __DIR__ . '/dmm/sql/subjects-schema.php';

class AIEO_Audience_Rules {

	const CI_ALIAS = 'c';

	
	
	

	
	const SEGMENT_SLUGS_FALLBACK = array( 'brand_devoted', 'competition_participant', 'discount_hunter', 'loyalist', 'newbie', 'overdue', 'replenisher', 'vip' );

	
	const SEGMENT_SLUGS_RETIRED = array( 'dormant' );

	
	const SEGMENT_SLUG_ALIASES = array( 'lapsed' => 'overdue' );

	const SEGMENT_SLUGS_TRANSIENT = 'aieo_audience_segment_slugs';

	
	public static function segment_slugs() {
		static $memo = null;
		if ( null !== $memo ) {
			return $memo; 
		}
		$cached = get_transient( self::SEGMENT_SLUGS_TRANSIENT );
		if ( is_array( $cached ) && ! empty( $cached ) ) {
			$memo = $cached;
			return $memo;
		}
		global $wpdb;
		$ci   = $wpdb->prefix . 'aieo_customer_intelligence';
		$seen = array();
		if ( self::table( $ci ) ) {
			
			
			
			
			
			$rows = $wpdb->get_col( "SELECT segments_json FROM `{$ci}` WHERE segments_json IS NOT NULL AND segments_json <> '' AND segments_json <> '[]'" );
			if ( ! empty( $rows ) ) {
				preg_match_all( '/"([a-z0-9_\-]+)"/i', implode( ',', $rows ), $m );
				foreach ( (array) $m[1] as $slug ) {
					$slug = self::SEGMENT_SLUG_ALIASES[ $slug ] ?? $slug;
					if ( in_array( $slug, self::SEGMENT_SLUGS_RETIRED, true ) ) {
						continue;
					}
					$seen[ $slug ] = true;
				}
			}
		}
		
		
		
		
		
		if ( empty( $seen ) ) {
			$memo = self::SEGMENT_SLUGS_FALLBACK;
			return $memo;
		}
		$memo = array_keys( $seen );
		sort( $memo );
		set_transient( self::SEGMENT_SLUGS_TRANSIENT, $memo, 6 * HOUR_IN_SECONDS );
		return $memo;
	}

	
	public static function flush_segment_slugs() {
		delete_transient( self::SEGMENT_SLUGS_TRANSIENT );
	}

	
	
	

	
	const DORMANCY_COHORTS = array( 'active', 'drifting', 'lapsed', 'long_dormant', 'lost' );

	
	const SYNTHETIC_POPULATIONS = array(
		'pure_intent' => array( 'col' => 'sole_order_share', 'op' => '>=', 'value' => 0.50 ),
	);

	
	public static function population_names() {
		$out = array();
		foreach ( array_keys( self::SYNTHETIC_POPULATIONS ) as $n ) {
			$out[ $n ] = 'synthetic';
		}
		foreach ( self::DORMANCY_COHORTS as $n ) {
			$out[ $n ] = 'dormancy';
		}
		foreach ( self::segment_slugs() as $n ) {
			if ( ! isset( $out[ $n ] ) ) { 
				$out[ $n ] = 'segment';
			}
		}
		return $out;
	}

	
	public static function is_population( $name ) {
		$names = self::population_names();
		return isset( $names[ (string) $name ] );
	}

	
	public static function population_where( $name, $alias = 'ci' ) {
		$name  = (string) $name;
		$alias = preg_replace( '/[^A-Za-z0-9_]/', '', (string) $alias );
		if ( '' === $alias ) {
			$alias = 'ci';
		}
		$kind = self::population_names()[ $name ] ?? '';

		if ( 'synthetic' === $kind ) {
			$def = self::SYNTHETIC_POPULATIONS[ $name ];
			$op  = in_array( $def['op'], array( '>=', '>', '<=', '<', '=' ), true ) ? $def['op'] : '>=';
			return array(
				'sql'  => "{$alias}.`" . $def['col'] . "` {$op} %f",
				'args' => array( (float) $def['value'] ),
				'kind' => 'synthetic',
			);
		}

		if ( 'dormancy' === $kind ) {
			return array(
				'sql'  => "{$alias}.`dormancy_cohort` = %s",
				'args' => array( 'active' === $name ? '' : $name ),
				'kind' => 'dormancy',
			);
		}

		if ( 'segment' === $kind ) {
			
			
			
			$names = array( $name );
			$key   = strtolower( $name );
			if ( isset( self::SEGMENT_SLUG_ALIASES[ $key ] ) ) {
				$names[] = self::SEGMENT_SLUG_ALIASES[ $key ];
			}
			foreach ( self::SEGMENT_SLUG_ALIASES as $legacy => $current ) {
				if ( $current === $key ) {
					$names[] = $legacy;
				}
			}
			$names = array_values( array_unique( $names ) );

			$parts = array();
			$args  = array();
			foreach ( $names as $n ) {
				$parts[] = "JSON_SEARCH(IFNULL({$alias}.`segments_json`, '[]'), 'one', %s) IS NOT NULL";
				$args[]  = $n;
			}
			
			return array(
				'sql'  => '(' . implode( ' OR ', $parts ) . ')',
				'args' => $args,
				'kind' => 'segment',
			);
		}

		throw new InvalidArgumentException( sprintf(
			'Unknown population "%s". Expected a synthetic (%s), a dormancy cohort (%s) or a segment (%s).',
			$name,
			implode( ', ', array_keys( self::SYNTHETIC_POPULATIONS ) ),
			implode( ', ', self::DORMANCY_COHORTS ),
			implode( ', ', self::segment_slugs() )
		) );
	}

	
	
	

	
	public static function field_catalog() {
		$num = array( '=', '!=', '>', '>=', '<', '<=' );
		
		
		$sym = function_exists( 'get_woocommerce_currency_symbol' ) ? html_entity_decode( get_woocommerce_currency_symbol(), ENT_QUOTES, 'UTF-8' ) : '€';
		return array(
			'ltv_total'                 => array( 'col' => 'ltv_total', 'type' => 'number', 'label' => 'Lifetime value (' . $sym . ')', 'ops' => $num ),
			'aov'                       => array( 'col' => 'aov', 'type' => 'number', 'label' => 'Average order value (' . $sym . ')', 'ops' => $num ),
			'orders_count'              => array( 'col' => 'orders_count', 'type' => 'number', 'label' => 'Orders count', 'ops' => $num ),
			'recency_days'              => array( 'col' => 'recency_days', 'type' => 'number', 'label' => 'Days since last order', 'ops' => $num ),
			'reactivation_score'        => array( 'col' => 'reactivation_score', 'type' => 'number', 'label' => 'Reactivation score (0-100)', 'ops' => $num ),
			'discount_share'            => array( 'col' => 'discount_share', 'type' => 'number', 'label' => 'Discount share (0-1)', 'ops' => $num ),
			'avg_item_price'            => array( 'col' => 'avg_item_price', 'type' => 'number', 'label' => 'Avg item price (' . $sym . ')', 'ops' => $num ),
			'days_since_last_engagement' => array( 'col' => 'days_since_last_engagement', 'type' => 'number', 'label' => 'Days since last email engagement', 'ops' => $num ),
			'open_rate_lifetime'        => array( 'col' => 'open_rate_lifetime', 'type' => 'number', 'label' => 'Email open rate (0-1)', 'ops' => $num ),
			'sale_purchase_cnt'         => array( 'col' => 'sale_purchase_cnt', 'type' => 'number', 'label' => 'On-sale purchases', 'ops' => $num ),
			
			
			
			
			
			
			
			'sole_order_share'          => array( 'col' => 'sole_order_share', 'type' => 'number', 'label' => 'Single-item order share (0-1)', 'ops' => $num ),
			
			
			
			'segments'                  => array( 'col' => 'segments_json', 'type' => 'json', 'label' => 'Segment (behavioural — not dormancy)', 'ops' => array( 'contains' ), 'enum' => self::segment_slugs() ),
			'dormancy_cohort'           => array( 'col' => 'dormancy_cohort', 'type' => 'string', 'label' => 'Dormancy cohort (days since last order)', 'ops' => array( '=','!=','in' ), 'enum' => array( 'active','drifting','lapsed','long_dormant','lost' ) ),
			'rfm_cell'                  => array( 'col' => 'rfm_cell', 'type' => 'string', 'label' => 'RFM cell', 'ops' => array( '=','!=','in' ) ),
			
			
			
			
			
			'price_band'                => array( 'col' => 'price_band', 'type' => 'number', 'label' => 'Price band (1-5 quintile of avg item price)', 'ops' => array_merge( $num, array( 'in' ) ), 'enum' => array( 1, 2, 3, 4, 5 ), 'enum_labels' => array( 1 => 'lowest fifth', 2 => 'second fifth', 3 => 'middle fifth', 4 => 'fourth fifth', 5 => 'highest fifth' ), 'exclude_zero' => true ),
			'tnp_status'                => array( 'col' => 'tnp_status', 'type' => 'string', 'label' => 'Newsletter status', 'ops' => array( '=','!=' ), 'enum' => array( 'C','U','B','' ) ),
			'inferred_top_priority'     => array( 'col' => 'inferred_top_priority', 'type' => 'string', 'label' => 'Inferred top priority', 'ops' => array( '=','!=' ) ),
			'top_category_id'           => array( 'col' => 'top_category_id', 'type' => 'term', 'label' => 'Top category (term id)', 'ops' => array( '=','in' ) ),
			'top_brand_id'              => array( 'col' => 'top_brand_id', 'type' => 'term', 'label' => 'Top brand (term id)', 'ops' => array( '=','in' ) ),
			'instigator'                => array( 'col' => '', 'type' => 'behaviour', 'label' => 'In unfulfilled-intent state (carted/wished/heavy-viewed, not bought)', 'ops' => array( 'is' ) ),
			'lookalike'                 => array( 'col' => '', 'type' => 'behaviour', 'label' => 'Behaviourally similar to a seed subject', 'ops' => array( 'is' ) ),
			
			
			
			
			
			'image_engaged'             => array( 'col' => '', 'type' => 'behaviour', 'label' => 'Has opened product/content images (deliberate look, no purchase needed)', 'ops' => array( 'is' ) ),
			
			
			
			
			'ever_purchased_product'    => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased product (product id)', 'ops' => array( 'in', 'not_in' ) ),
			'ever_purchased_category'   => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased in category (term id)', 'ops' => array( 'in', 'not_in' ) ),
			'ever_purchased_brand'      => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased brand (term id)', 'ops' => array( 'in', 'not_in' ) ),
			'ever_purchased_tag'        => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased tagged (tag term id)', 'ops' => array( 'in', 'not_in' ) ),
			'ever_purchased_need'       => array( 'col' => '', 'type' => 'purchase', 'label' => 'Ever purchased for need (need term id)', 'ops' => array( 'in', 'not_in' ) ),
			
			
			
			
			'nps_class'                 => array( 'col' => 'nps_class', 'type' => 'voc', 'voc_kind' => 'string', 'label' => 'Survey NPS class', 'ops' => array( '=', '!=', 'in' ), 'enum' => array( 'promoter', 'passive', 'detractor' ) ),
			'voc_band'                  => array( 'col' => 'voc_band', 'type' => 'voc', 'voc_kind' => 'string', 'label' => 'Voice-of-customer band', 'ops' => array( '=', '!=', 'in' ), 'enum' => array( 'high', 'mid', 'low' ) ),
			'voc_score'                 => array( 'col' => 'voc_score', 'type' => 'voc', 'voc_kind' => 'number', 'label' => 'Voice-of-customer score (0-100)', 'ops' => $num ),
			'shipping_price_concern'    => array( 'col' => 'shipping_price_concern', 'type' => 'voc', 'voc_kind' => 'number', 'label' => 'Flagged shipping-price concern in survey (0/1)', 'ops' => array( '=' ), 'enum' => array( '0', '1' ) ),
		) + self::survey_fields();
	}

	
	private static function survey_fields() {
		if ( ! class_exists( 'AIEO_Survey_Blueprint' ) ) {
			return array();
		}
		$num  = array( '=', '!=', '>', '>=', '<', '<=' );
		$out  = array();
		$seen = array();
		foreach ( (array) AIEO_Survey_Blueprint::all()['blueprints'] as $bid => $bp ) {
			foreach ( AIEO_Survey_Blueprint::question_codes( $bid ) as $code ) {
				if ( isset( $seen[ $code ] ) ) {
					continue;   
				}
				$seen[ $code ] = true;
				$q = AIEO_Survey_Blueprint::question( $code );
				if ( ! $q || 'text' === $q['answers'] ) {
					continue;   
				}
				$scale = ( 'scale' === $q['scored'] );
				$out[ 'survey_' . $code ] = array(
					'col'      => $code,
					'type'     => 'survey',
					'survey_kind' => $scale ? 'number' : 'string',
					
					'label'    => sprintf( __( 'User Survey: %s', 'ai-eshop-optimizer' ), $q['label'] ),
					'ops'      => $scale ? $num : array( '=', '!=', 'in' ),
					'enum'     => $scale ? array() : array_keys( $q['choices'] ),
				);
			}
			unset( $bp );
		}
		return $out;
	}

	
	
	

	
	public static function compile( $rule ) {
		if ( ! is_array( $rule ) ) {
			return '1=0';
		}
		$op   = ( isset( $rule['op'] ) && 'or' === strtolower( (string) $rule['op'] ) ) ? 'OR' : 'AND';
		$conds = isset( $rule['conditions'] ) && is_array( $rule['conditions'] ) ? $rule['conditions'] : array();
		if ( empty( $conds ) ) {
			return '1=0';
		}
		$parts = array();
		foreach ( $conds as $cnd ) {
			if ( ! is_array( $cnd ) ) {
				continue;
			}
			if ( isset( $cnd['conditions'] ) ) {
				$sub = self::compile( $cnd );
				
				
				
				
				if ( '' !== $sub ) {
					$parts[] = '(' . $sub . ')';
				}
				continue;
			}
			$leaf = self::compile_leaf( $cnd );
			if ( '' !== $leaf ) {
				$parts[] = $leaf;
			}
		}
		if ( empty( $parts ) ) {
			return '1=0';
		}
		return implode( ' ' . $op . ' ', $parts );
	}

	private static function compile_leaf( $cnd ) {
		global $wpdb;
		$cat   = self::field_catalog();
		$field = (string) ( $cnd['field'] ?? '' );
		if ( ! isset( $cat[ $field ] ) ) {
			return '';
		}
		$def = $cat[ $field ];
		$op  = strtolower( (string) ( $cnd['op'] ?? '' ) );
		if ( ! in_array( $op, array_map( 'strtolower', $def['ops'] ), true ) ) {
			return '';
		}
		$a   = self::CI_ALIAS;
		$col = $a . '.' . $def['col']; 

		switch ( $def['type'] ) {
			case 'number':
				if ( 'in' === $op ) {
					
					
					
					
					$vals = array();
					foreach ( self::int_list( $cnd['value'] ?? array() ) as $iv ) {
						if ( empty( $def['exclude_zero'] ) || 0 !== $iv ) {
							$vals[] = $iv;
						}
					}
					return $vals ? "{$col} IN (" . implode( ',', $vals ) . ')' : '1=0';
				}
				$v  = (float) ( $cnd['value'] ?? 0 );
				$sym = self::num_op( $op );
				if ( ! $sym ) {
					return '';
				}
				if ( ! empty( $def['exclude_zero'] ) ) {
					
					
					
					
					
					
					return "({$col} {$sym} {$v} AND {$col} <> 0)";
				}
				return "{$col} {$sym} {$v}";

			case 'term':
				if ( 'in' === $op ) {
					$ids = self::int_list( $cnd['value'] ?? array() );
					
					
					return $ids ? "{$col} IN (" . implode( ',', $ids ) . ')' : '1=0';
				}
				return "{$col} = " . (int) ( $cnd['value'] ?? 0 );

			case 'string':
				
				
				
				
				$map = ( 'dormancy_cohort' === $field )
					? function ( $x ) { return 'active' === (string) $x ? '' : (string) $x; }
					: null;
				if ( 'in' === $op ) {
					$raw  = is_array( $cnd['value'] ?? null ) ? $cnd['value'] : array( $cnd['value'] ?? '' );
					if ( $map ) {
						$raw = array_map( $map, (array) $raw );
					}
					$vals = self::str_list( $raw );
					
					
					return $vals ? "{$col} IN (" . implode( ',', $vals ) . ')' : '1=0';
				}
				$sym = ( '!=' === $op ) ? '<>' : '=';
				$val = (string) ( $cnd['value'] ?? '' );
				if ( $map ) {
					$val = $map( $val );
				}
				return "{$col} {$sym} '" . esc_sql( $val ) . "'";

			case 'json': 
				$val = preg_replace( '/[^a-z0-9_\-]/i', '', (string) ( $cnd['value'] ?? '' ) );
				if ( '' === $val ) {
					
					
					return '1=0';
				}
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				
				$names = array( $val );
				$key   = strtolower( $val );
				if ( isset( self::SEGMENT_SLUG_ALIASES[ $key ] ) ) {
					$names[] = self::SEGMENT_SLUG_ALIASES[ $key ]; 
				}
				foreach ( self::SEGMENT_SLUG_ALIASES as $legacy => $current ) {
					if ( $current === $key ) {
						$names[] = $legacy; 
					}
				}
				$names = array_unique( $names );
				$likes = array();
				foreach ( $names as $n ) {
					$likes[] = "{$col} LIKE '%\"" . esc_sql( $n ) . "\"%'";
				}
				return ( 1 === count( $likes ) ) ? $likes[0] : '(' . implode( ' OR ', $likes ) . ')';

			case 'behaviour':
				return self::compile_behaviour( $field, $cnd );

			case 'purchase':
				return self::compile_purchase( $field, $op, $cnd );

			case 'voc':
				return self::compile_voc( $field, $op, $cnd );

			case 'survey':
				return self::compile_survey( $field, $op, $cnd );
		}
		return '';
	}

	
	private static function compile_survey( $field, $op, $cnd ) {
		global $wpdb;
		$cat = self::field_catalog();
		$def = $cat[ $field ] ?? null;
		if ( ! $def || empty( $def['col'] ) || ! class_exists( 'AIEO_Form_CPT' ) ) {
			return '';
		}
		$entries = AIEO_Form_CPT::entries_table();
		$values  = AIEO_Form_CPT::entry_values_table();
		if ( ! self::table( $entries ) || ! self::table( $values ) ) {
			return '1=0';   
		}
		$code = esc_sql( (string) $def['col'] );

		if ( 'number' === ( $def['survey_kind'] ?? '' ) ) {
			$sym = self::num_op( $op );
			if ( ! $sym ) {
				return '';
			}
			$inner = "sav.value_num {$sym} " . (float) ( $cnd['value'] ?? 0 );
		} elseif ( 'in' === $op ) {
			$raw  = is_array( $cnd['value'] ?? null ) ? $cnd['value'] : array( $cnd['value'] ?? '' );
			$vals = self::str_list( $raw );
			if ( ! $vals ) {
				
				
				
				return '1=0';
			}
			$inner = 'sav.choice_key IN (' . implode( ',', $vals ) . ')';
		} else {
			$sym   = ( '!=' === $op ) ? '<>' : '=';
			$inner = "sav.choice_key {$sym} '" . esc_sql( (string) ( $cnd['value'] ?? '' ) ) . "'";
		}

		$a = self::CI_ALIAS;
		return "EXISTS (SELECT 1 FROM `{$values}` sav
			JOIN `{$entries}` sae ON sae.id = sav.entry_id
			WHERE sae.eponymous_id = {$a}.eponymous_id
			  AND sae.eponymous_id > 0
			  AND sav.field_key = '{$code}'
			  AND sav.disposition = 'answered'
			  AND {$inner})";
	}

	
	private static function compile_voc( $field, $op, $cnd ) {
		global $wpdb;
		$cat = self::field_catalog();
		$def = $cat[ $field ] ?? null;
		if ( ! $def || empty( $def['col'] ) ) {
			return '';
		}
		$sv = $wpdb->prefix . 'aieo_survey_intelligence';
		if ( ! self::table( $sv ) ) {
			return '1=0'; 
		}
		$col = 'sv.' . $def['col']; 
		if ( 'number' === ( $def['voc_kind'] ?? '' ) ) {
			$sym = self::num_op( $op );
			if ( ! $sym ) {
				return '';
			}
			$inner = "{$col} {$sym} " . (float) ( $cnd['value'] ?? 0 );
		} else { 
			if ( 'in' === $op ) {
				$raw  = is_array( $cnd['value'] ?? null ) ? $cnd['value'] : array( $cnd['value'] ?? '' );
				$vals = self::str_list( $raw );
				if ( ! $vals ) {
					
					
					return '1=0';
				}
				$inner = "{$col} IN (" . implode( ',', $vals ) . ')';
			} else {
				$sym   = ( '!=' === $op ) ? '<>' : '=';
				$inner = "{$col} {$sym} '" . esc_sql( (string) ( $cnd['value'] ?? '' ) ) . "'";
			}
		}
		$a = self::CI_ALIAS;
		
		
		
		
		$guard = '';
		if ( 'nps_class' === $field ) {
			$guard = " AND sv.nps_class <> ''";
		} elseif ( 'shipping_price_concern' === $field ) {
			$guard = ' AND sv.f18_entry_id > 0';
		}
		
		
		
		return "EXISTS (SELECT 1 FROM `{$sv}` sv WHERE {$a}.email <> '' AND sv.email = LOWER(TRIM({$a}.email)){$guard} AND {$inner})";
	}

	
	private static function compile_purchase( $field, $op, $cnd ) {
		global $wpdb;
		$kind_map = array(
			'ever_purchased_product'  => 'product',
			'ever_purchased_category' => 'category',
			'ever_purchased_brand'    => 'brand',
			'ever_purchased_tag'      => 'tag',
			'ever_purchased_need'     => 'need',
		);
		$kind = $kind_map[ $field ] ?? '';
		if ( '' === $kind ) {
			return '';
		}
		$t = function_exists( 'aieo_customer_purchases_table' ) ? aieo_customer_purchases_table() : ( $wpdb->prefix . 'aieo_customer_purchases' );
		if ( ! self::table( $t ) ) {
			return '1=0'; 
		}
		
		
		
		
		
		if ( 'not_in' === $op && class_exists( 'AIEO_Customer_Purchases' )
			&& ! in_array( $kind, AIEO_Customer_Purchases::ready_kinds(), true ) ) {
			return '1=0';
		}
		$ids = self::int_list( $cnd['value'] ?? array() );
		if ( empty( $ids ) ) {
			
			
			
			
			return ( 'not_in' === $op ) ? '1=1' : '1=0';
		}
		$in = implode( ',', $ids );
		$a  = self::CI_ALIAS;
		
		
		
		$exists = "EXISTS (SELECT 1 FROM `{$t}` cp WHERE cp.email_hash = MD5(LOWER(TRIM({$a}.email))) AND cp.kind = '{$kind}' AND cp.ref_id IN ({$in}))";
		return ( 'not_in' === $op ) ? "NOT {$exists}" : $exists;
	}

	
	private static function compile_behaviour( $field, $cnd ) {
		global $wpdb;
		$a    = self::CI_ALIAS;
		$subj = $wpdb->prefix . 'aieo_subjects';
		$isum = aieo_interaction_summary_table();

		if ( 'instigator' === $field ) {
			if ( ! self::table( $subj ) || ! self::table( $isum ) ) {
				return '1=0';
			}
			$days = 14;
			
			
			return "{$a}.wp_user_id IN (
				SELECT DISTINCT s.wp_user_id FROM `{$subj}` s
				JOIN `{$isum}` i ON i.subject_id = s.subject_id
				WHERE s.wp_user_id > 0 AND s.merged_into = 0 AND i.item_type='product'
				  AND i.conversions = 0 AND (i.carts > 0 OR i.wishlists > 0 OR i.weight >= 4)
				  AND i.last_at >= (UTC_TIMESTAMP() - INTERVAL {$days} DAY) )";
		}

		if ( 'image_engaged' === $field ) {
			if ( ! self::table( $subj ) || ! self::table( $isum ) ) {
				return '1=0';
			}
			
			
			
			
			$min  = max( 1, min( 50, (int) ( $cnd['min'] ?? 1 ) ) );
			$days = max( 1, min( 365, (int) ( $cnd['days'] ?? 90 ) ) );
			
			return "{$a}.wp_user_id IN (
				SELECT DISTINCT s.wp_user_id FROM `{$subj}` s
				JOIN `{$isum}` i ON i.subject_id = s.subject_id
				WHERE s.wp_user_id > 0 AND s.merged_into = 0
				  AND i.image_clicks >= {$min}
				  AND i.last_at >= (UTC_TIMESTAMP() - INTERVAL {$days} DAY) )";
		}

		if ( 'lookalike' === $field ) {
			$seed = (int) ( $cnd['seed_subject_id'] ?? 0 );
			if ( $seed <= 0 || ! class_exists( 'AIEO_Behavioural_Reco' ) ) {
				return '1=0';
			}
			$n     = (int) ( $cnd['limit'] ?? 500 );
			$looks = AIEO_Behavioural_Reco::lookalikes( $seed, $n );
			$sids  = array();
			foreach ( (array) $looks as $l ) {
				$sids[] = (int) $l['subject_id'];
			}
			if ( empty( $sids ) ) {
				return '1=0';
			}
			$in  = implode( ',', array_map( 'intval', $sids ) );
			
			
			return "{$a}.wp_user_id IN ( SELECT wp_user_id FROM `{$subj}` WHERE subject_id IN ({$in}) AND wp_user_id > 0 )";
		}
		return '1=0';
	}

	
	
	

	public static function count( $rule ) {
		global $wpdb;
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		if ( ! self::table( $ci ) ) {
			return 0;
		}
		$where = self::compile( $rule );
		
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$ci}` " . self::CI_ALIAS . " WHERE ({$where})" );
	}

	
	public static function audience_stats( $rule ) {
		global $wpdb;
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		if ( ! self::table( $ci ) ) {
			return array( 'count' => 0, 'contactable' => 0, 'avg_aov' => 0.0 );
		}
		$where = self::compile( $rule );
		$a     = self::CI_ALIAS;
		
		
		
		
		$row = $wpdb->get_row(
			"SELECT COUNT(*) c,
				SUM(CASE WHEN {$a}.tnp_status = 'C' THEN 1 ELSE 0 END) contactable,
				AVG(NULLIF(CASE WHEN {$a}.tnp_status = 'C' THEN {$a}.aov END, 0)) aov
			 FROM `{$ci}` {$a} WHERE ({$where})",
			ARRAY_A
		);
		return array(
			'count'       => (int) ( $row['c'] ?? 0 ),
			'contactable' => (int) ( $row['contactable'] ?? 0 ),
			'avg_aov'     => (float) ( $row['aov'] ?? 0 ),
		);
	}

	public static function preview( $rule, $limit = 8 ) {
		global $wpdb;
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		if ( ! self::table( $ci ) ) {
			return array();
		}
		$where = self::compile( $rule );
		$limit = max( 1, min( 25, (int) $limit ) );
		$a     = self::CI_ALIAS;
		
		$rows = $wpdb->get_results( "SELECT {$a}.eponymous_id, {$a}.wp_user_id, {$a}.email FROM `{$ci}` {$a} WHERE ({$where}) ORDER BY {$a}.ltv_total DESC LIMIT {$limit}", ARRAY_A );
		$out = array();
		foreach ( (array) $rows as $r ) {
			$name = '';
			if ( (int) $r['wp_user_id'] > 0 ) {
				$u    = get_userdata( (int) $r['wp_user_id'] );
				$name = $u ? (string) $u->display_name : '';
			}
			$out[] = array(
				'eponymous_id' => (int) $r['eponymous_id'],
				'email'        => (string) $r['email'],
				'name'         => $name,
			);
		}
		return $out;
	}

	
	
	public static function members( $rule, $after = 0, $limit = 200 ) {
		global $wpdb;
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		if ( ! self::table( $ci ) ) {
			return array();
		}
		$where = self::compile( $rule );
		
		
		$where = str_replace( '%', '%%', $where );
		$a     = self::CI_ALIAS;
		$limit = max( 1, min( 1000, (int) $limit ) );
		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT {$a}.eponymous_id, {$a}.wp_user_id, {$a}.email
			   FROM `{$ci}` {$a}
			  WHERE ({$where}) AND {$a}.eponymous_id > %d
			  ORDER BY {$a}.eponymous_id ASC
			  LIMIT {$limit}",
			(int) $after
		), ARRAY_A );
		$out = array();
		foreach ( (array) $rows as $r ) {
			$out[] = array(
				'eponymous_id' => (int) $r['eponymous_id'],
				'wp_user_id'   => (int) $r['wp_user_id'],
				'email'        => (string) $r['email'],
			);
		}
		return $out;
	}

	
	public static function reach( $rule ) {
		global $wpdb;
		$ci  = $wpdb->prefix . 'aieo_customer_intelligence';
		$out = array( 'total' => 0, 'emailable' => 0, 'with_account' => 0, 'consented' => 0, 'push' => 0 );
		if ( ! self::table( $ci ) ) {
			return $out;
		}
		$where = self::compile( $rule );
		$a     = self::CI_ALIAS;

		
		
		
		
		
		
		$push_tbl = $wpdb->prefix . 'aieo_push_subscriptions';
		$push_sel = '0';
		if ( self::table( $push_tbl ) ) {
			$by_email = self::column( $push_tbl, 'identified_email' )
				? " OR ({$a}.email <> '' AND ps.identified_email = {$a}.email)"
				: '';
			$push_sel = "SUM(CASE WHEN EXISTS (
					SELECT 1 FROM `{$push_tbl}` ps
					 WHERE ps.status = 'active' AND ps.endpoint <> ''
					   AND ( ({$a}.wp_user_id > 0 AND ps.user_id = {$a}.wp_user_id){$by_email} )
				) THEN 1 ELSE 0 END)";
		}

		
		$row = $wpdb->get_row(
			"SELECT COUNT(*) total,
				SUM(CASE WHEN {$a}.email <> '' THEN 1 ELSE 0 END) emailable,
				SUM(CASE WHEN {$a}.wp_user_id > 0 THEN 1 ELSE 0 END) with_account,
				SUM(CASE WHEN {$a}.tnp_status = 'C' THEN 1 ELSE 0 END) consented,
				{$push_sel} push
			   FROM `{$ci}` {$a} WHERE ({$where})",
			ARRAY_A
		);
		foreach ( array_keys( $out ) as $k ) {
			$out[ $k ] = (int) ( $row[ $k ] ?? 0 );
		}
		return $out;
	}

	
	public static function member_user_ids( $rule, $after_id = 0, $limit = 200 ) {
		global $wpdb;
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		if ( ! self::table( $ci ) ) {
			return array();
		}
		$where = self::compile( $rule );
		
		
		
		$where = str_replace( '%', '%%', $where );
		$a     = self::CI_ALIAS;
		$limit = max( 1, min( 1000, (int) $limit ) );
		
		$ids = $wpdb->get_col( $wpdb->prepare(
			"SELECT {$a}.wp_user_id FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.wp_user_id > %d ORDER BY {$a}.wp_user_id ASC LIMIT {$limit}",
			(int) $after_id
		) );
		return array_map( 'intval', (array) $ids );
	}

	
	public static function matches_user( $rule, $wp_user_id ) {
		global $wpdb;
		$wp_user_id = (int) $wp_user_id;
		if ( $wp_user_id <= 0 ) {
			return false;
		}
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		if ( ! self::table( $ci ) ) {
			return false;
		}
		$where = self::compile( $rule );
		
		$where = str_replace( '%', '%%', $where );
		$a     = self::CI_ALIAS;
		
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$ci}` {$a} WHERE ({$where}) AND {$a}.wp_user_id = %d", $wp_user_id ) ) > 0;
	}

	
	public static function matches_email( $rule, $email ) {
		global $wpdb;
		$email = strtolower( trim( (string) $email ) );
		if ( '' === $email || ! is_email( $email ) ) {
			return false;
		}
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		if ( ! self::table( $ci ) ) {
			return false;
		}
		$where = self::compile( $rule );
		
		$where = str_replace( '%', '%%', $where );
		$a     = self::CI_ALIAS;
		
		return (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM `{$ci}` {$a} WHERE ({$where}) AND LOWER(TRIM({$a}.email)) = %s",
			$email
		) ) > 0;
	}

	
	
	

	public static function describe_rule( $rule ) {
		if ( ! is_array( $rule ) || empty( $rule['conditions'] ) ) {
			return __( '(empty)', 'ai-eshop-optimizer' );
		}
		$cat  = self::field_catalog();
		$op   = ( isset( $rule['op'] ) && 'or' === strtolower( (string) $rule['op'] ) ) ? ' OR ' : ' AND ';
		$bits = array();
		foreach ( $rule['conditions'] as $c ) {
			if ( ! is_array( $c ) ) {
				continue;
			}
			if ( isset( $c['conditions'] ) ) {
				$bits[] = '(' . self::describe_rule( $c ) . ')';
				continue;
			}
			$f = (string) ( $c['field'] ?? '' );
			if ( ! isset( $cat[ $f ] ) ) {
				continue;
			}
			$label = $cat[ $f ]['label'];
			if ( 'behaviour' === $cat[ $f ]['type'] ) {
				$bits[] = $label . ( 'lookalike' === $f && ! empty( $c['seed_subject_id'] ) ? ' #' . (int) $c['seed_subject_id'] : '' );
			} else {
				$val = is_array( $c['value'] ?? null ) ? implode( ',', $c['value'] ) : (string) ( $c['value'] ?? '' );
				
				
				if ( isset( $cat[ $f ]['enum_labels'][ $val ] ) ) {
					$val .= ' (' . $cat[ $f ]['enum_labels'][ $val ] . ')';
				}
				$bits[] = $label . ' ' . (string) ( $c['op'] ?? '' ) . ' ' . $val;
			}
		}
		return implode( $op, $bits );
	}

	
	
	

	
	public static function from_nl( $text ) {
		if ( ! class_exists( 'AI_eShop_Chat_Anthropic_API' ) ) {
			return array( 'ok' => false, 'rule' => array(), 'error' => 'chat_api_unavailable' );
		}
		$cat   = self::field_catalog();
		$lines = array();
		foreach ( $cat as $f => $d ) {
			$enum = '';
			if ( isset( $d['enum'] ) ) {
				
				
				$vals = array();
				foreach ( (array) $d['enum'] as $ev ) {
					$vals[] = isset( $d['enum_labels'][ $ev ] ) ? $ev . ' = ' . $d['enum_labels'][ $ev ] : $ev;
				}
				$enum = ' — values: ' . implode( '|', $vals );
			}
			$lines[] = "- \"{$f}\" ({$d['type']}, ops: " . implode( '/', $d['ops'] ) . "): {$d['label']}{$enum}";
		}
		$system = "You convert a plain-English customer-audience description into a JSON rule tree. Output ONLY valid JSON, no prose.\n"
			. "Schema: {\"op\":\"and\"|\"or\",\"conditions\":[ <leaf> | <nested group> ]}. "
			. "A leaf is {\"field\":\"<field>\",\"op\":\"<op>\",\"value\":<value>}. "
			. "For behaviour fields use {\"field\":\"instigator\",\"op\":\"is\"} or {\"field\":\"lookalike\",\"op\":\"is\",\"seed_subject_id\":<int>}. "
			. "For a 'segments' membership use op 'contains' with a single value. Use ONLY the fields below; if the request needs something unavailable, omit it.\n"
			. "FIELDS:\n" . implode( "\n", $lines )
			
			
			
			
			. "\nDORMANCY: express it ONLY with \"dormancy_cohort\" (calendar staleness since the last order: "
			. "active = under 60 days, drifting = 60-90, lapsed = 90-180, long_dormant = 180-365, lost = 365+). "
			. "There is no 'dormant' segment — never put \"dormant\" in a segments leaf. "
			. "The \"overdue\" SEGMENT is a different idea: overdue against that customer's OWN reorder cadence "
			. "(a weekly buyer silent 3 weeks), which can fire long before any dormancy cohort does.\n"
			. "Example — 'lapsed VIPs with LTV over 100 who aren't discount hunters': "
			. "{\"op\":\"and\",\"conditions\":[{\"field\":\"dormancy_cohort\",\"op\":\"=\",\"value\":\"lapsed\"},{\"field\":\"ltv_total\",\"op\":\">\",\"value\":100},{\"field\":\"discount_share\",\"op\":\"<\",\"value\":0.4}]}";

		$res = AI_eShop_Chat_Anthropic_API::get_instance()->chat_completion(
			array( array( 'role' => 'user', 'content' => (string) $text ) ),
			array(
				'system'      => $system,
				'model'       => (string) get_option( 'aieo_chat_model', 'claude-sonnet-4-5' ),
				'max_tokens'  => 800,
				'temperature' => 0.1,
				'source'      => 'aieo.chat.audience',
			)
		);
		if ( is_wp_error( $res ) ) {
			return array( 'ok' => false, 'rule' => array(), 'error' => $res->get_error_message() );
		}
		$txt = ( is_array( $res ) && ! empty( $res['content'][0]['text'] ) ) ? (string) $res['content'][0]['text'] : '';
		$rule = self::extract_json( $txt );
		if ( ! is_array( $rule ) || empty( $rule['conditions'] ) ) {
			return array( 'ok' => false, 'rule' => array(), 'error' => 'could_not_parse_rule' );
		}
		return array( 'ok' => true, 'rule' => $rule, 'error' => '' );
	}

	private static function extract_json( $txt ) {
		$txt = trim( $txt );
		if ( '' === $txt ) {
			return null;
		}
		
		$txt = preg_replace( '/^```(?:json)?\s*|\s*```$/m', '', $txt );
		$start = strpos( $txt, '{' );
		$end   = strrpos( $txt, '}' );
		if ( false === $start || false === $end || $end <= $start ) {
			return null;
		}
		$d = json_decode( substr( $txt, $start, $end - $start + 1 ), true );
		return is_array( $d ) ? $d : null;
	}

	
	
	

	public static function suggestions() {
		$defs = array(
			array( 'label' => __( 'In-market now (unfulfilled intent)', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'instigator', 'op' => 'is' ) ) ) ),
			
			
			
			array( 'label' => __( 'High-value dormant (180+ days)', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'dormancy_cohort', 'op' => 'in', 'value' => array( 'long_dormant', 'lost' ) ), array( 'field' => 'ltv_total', 'op' => '>=', 'value' => 100 ) ) ) ),
			
			
			
			
			
			
			array( 'label' => __( 'Reactivation-ready', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'reactivation_score', 'op' => '>=', 'value' => AIEO_DMM::REACTIVATION_SCORE_MIN ), array( 'field' => 'dormancy_cohort', 'op' => '!=', 'value' => 'active' ) ) ) ),
			array( 'label' => __( 'Loyalists', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'segments', 'op' => 'contains', 'value' => 'loyalist' ) ) ) ),
			array( 'label' => __( 'Deal-driven', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'discount_share', 'op' => '>=', 'value' => 0.5 ) ) ) ),
			array( 'label' => __( 'Lapsing (drifting/lapsed)', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'dormancy_cohort', 'op' => 'in', 'value' => array( 'drifting', 'lapsed' ) ) ) ) ),
			
			
			
			array( 'label' => __( 'Overdue vs own cadence', 'ai-eshop-optimizer' ), 'rule' => array( 'op' => 'and', 'conditions' => array( array( 'field' => 'segments', 'op' => 'contains', 'value' => 'overdue' ) ) ) ),
		);
		$out = array();
		foreach ( $defs as $d ) {
			$out[] = array(
				'label'   => $d['label'],
				'rule'    => $d['rule'],
				'count'   => self::count( $d['rule'] ),
				'summary' => self::describe_rule( $d['rule'] ),
			);
		}
		return $out;
	}

	
	
	

	
	public static function create( $name, $rule ) {
		global $wpdb;
		$t = aieo_audiences_table();
		if ( ! self::table( $t ) ) {
			return 0;
		}
		$now = gmdate( 'Y-m-d H:i:s' );
		$wpdb->insert( $t, array(
			'name'         => sanitize_text_field( (string) $name ),
			'rule_json'    => wp_json_encode( $rule ),
			'count_cached' => self::count( $rule ),
			'count_at'     => $now,
			'created'      => $now,
			'updated'      => $now,
		) );
		return (int) $wpdb->insert_id;
	}

	public static function save( $name, $rule ) {
		global $wpdb;
		$t = aieo_audiences_table();
		if ( ! self::table( $t ) ) {
			return 0;
		}
		$now  = gmdate( 'Y-m-d H:i:s' );
		$cnt  = self::count( $rule );
		$name = sanitize_text_field( (string) $name );
		$data = array(
			'name'         => $name,
			'rule_json'    => wp_json_encode( $rule ),
			'count_cached' => $cnt,
			'count_at'     => $now,
			'updated'      => $now,
		);
		$existing = (int) $wpdb->get_var( $wpdb->prepare( "SELECT id FROM `{$t}` WHERE name = %s LIMIT 1", $name ) );
		if ( $existing > 0 ) {
			$wpdb->update( $t, $data, array( 'id' => $existing ) );
			return $existing;
		}
		$data['created'] = $now;
		$wpdb->insert( $t, $data );
		return (int) $wpdb->insert_id;
	}

	public static function list_saved() {
		global $wpdb;
		$t = aieo_audiences_table();
		if ( ! self::table( $t ) ) {
			return array();
		}
		$rows = $wpdb->get_results( "SELECT id, name, rule_json, count_cached FROM `{$t}` ORDER BY updated DESC LIMIT 100", ARRAY_A );
		$out = array();
		foreach ( (array) $rows as $r ) {
			$out[] = array(
				'id'           => (int) $r['id'],
				'name'         => (string) $r['name'],
				'rule'         => json_decode( (string) $r['rule_json'], true ),
				'count_cached' => (int) $r['count_cached'],
			);
		}
		return $out;
	}

	public static function delete( $id ) {
		global $wpdb;
		$t = aieo_audiences_table();
		if ( ! self::table( $t ) || (int) $id <= 0 ) {
			return false;
		}
		return (bool) $wpdb->delete( $t, array( 'id' => (int) $id ), array( '%d' ) );
	}

	
	
	

	private static function num_op( $op ) {
		$m = array( '=' => '=', '!=' => '<>', '>' => '>', '>=' => '>=', '<' => '<', '<=' => '<=' );
		return $m[ $op ] ?? '';
	}

	private static function int_list( $v ) {
		$v = is_array( $v ) ? $v : array( $v );
		$out = array();
		foreach ( $v as $x ) {
			if ( is_numeric( $x ) ) {
				$out[] = (int) $x;
			}
		}
		return $out;
	}

	private static function str_list( $v ) {
		$v = is_array( $v ) ? $v : array( $v );
		$out = array();
		foreach ( $v as $x ) {
			$out[] = "'" . esc_sql( (string) $x ) . "'";
		}
		return $out;
	}

	private static function table( $t ) {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
	}

	
	private static function column( $t, $c ) {
		global $wpdb;
		
		return (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$t}` LIKE %s", $c ) );
	}
}