<?php

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

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

class AIEO_Proposal_Engine {

	const HOOK          = 'aieo_proposal_scan';
	const GROUP         = 'aieo';
	const RUN_AT        = '04:20';
	const MIN_AUDIENCE  = 20;    
	const MAX_NARRATE   = 8;     

	
	private static $booted = false;

	public static function bootstrap() {
		if ( self::$booted ) {
			return;
		}
		self::$booted = true;
		add_action( self::HOOK, array( __CLASS__, 'scan' ) );
		add_action( 'admin_init', array( __CLASS__, 'ensure_scheduled' ), 30 );
	}

	public static function ensure_scheduled() {
		if ( function_exists( 'as_next_scheduled_action' ) ) {
			if ( false !== as_next_scheduled_action( self::HOOK, array(), self::GROUP ) ) {
				return;
			}
			if ( function_exists( 'as_schedule_recurring_action' ) ) {
				as_schedule_recurring_action( self::next_run_ts(), DAY_IN_SECONDS, self::HOOK, array(), self::GROUP, true );
				return;
			}
		}
		if ( ! wp_next_scheduled( self::HOOK ) ) {
			wp_schedule_event( self::next_run_ts(), 'daily', self::HOOK );
		}
	}

	private static function next_run_ts() {
		try {
			$tz    = wp_timezone();
			$p     = explode( ':', self::RUN_AT );
			$next  = ( new DateTimeImmutable( 'now', $tz ) )->setTime( (int) $p[0], (int) ( $p[1] ?? 0 ), 0 );
			if ( $next->getTimestamp() <= time() ) {
				$next = $next->modify( '+1 day' );
			}
			return $next->getTimestamp();
		} catch ( \Throwable $e ) {
			return time() + HOUR_IN_SECONDS;
		}
	}

	
	
	

	
	const MOTION_FALLBACK = 'custom';

	
	const LEGACY_KINDS = array( 'replenishment' => 'reactivation_ready' );

	
	private static function canonical_kind( $kind ) {
		$kind = (string) $kind;
		return isset( self::LEGACY_KINDS[ $kind ] ) ? (string) self::LEGACY_KINDS[ $kind ] : $kind;
	}

	
	private static function kind_aliases( $kind ) {
		$kind = (string) $kind;
		$out  = array( $kind );
		foreach ( self::LEGACY_KINDS as $old => $new ) {
			if ( $new === $kind ) {
				$out[] = (string) $old;
			}
		}
		return array_values( array_unique( $out ) );
	}

	
	private static $miners = null;

	
	private static function motions() {
		return array(
			'instigator' => array(
				'offer_type' => 'percent', 'offer_value' => 10, 'timing' => 'daily', 'channel' => 'cascade', 'p_convert' => 0.08,
				'evidence' => __( 'People who carted / wishlisted / heavily viewed but did not buy — the highest-intent moment to nudge.', 'ai-eshop-optimizer' ),
			),
			'reactivation' => array(
				'offer_type' => 'percent', 'offer_value' => 15, 'timing' => 'weekly', 'channel' => 'cascade', 'p_convert' => 0.03,
				'evidence' => __( 'Proven big spenders who have gone quiet — worth a stronger incentive to win back.', 'ai-eshop-optimizer' ),
			),
			'winback' => array(
				'offer_type' => 'percent', 'offer_value' => 20, 'timing' => 'weekly', 'channel' => 'cascade', 'p_convert' => 0.025,
				'evidence' => __( 'Customers drifting toward lapse — catch them before the dormancy deepens.', 'ai-eshop-optimizer' ),
			),
			'reactivation_ready' => array(
				'offer_type' => 'none', 'offer_value' => 0, 'timing' => 'daily', 'channel' => 'cascade', 'p_convert' => 0.05,
				'evidence' => __( 'Scored the most recoverable of the quiet customers — spend value, frequency and a fresh-enough last order all point the right way, so a reminder often converts with no discount at all.', 'ai-eshop-optimizer' ),
			),
			
			
			
			
			
			
			
			
			
			'overdue' => array(
				'offer_type' => 'none', 'offer_value' => 0, 'timing' => 'daily', 'channel' => 'cascade', 'p_convert' => 0.05,
				'evidence' => __( 'Overdue against their OWN buying rhythm, not the calendar — a weekly buyer who has been quiet three weeks. A timely reminder often converts with no discount at all.', 'ai-eshop-optimizer' ),
			),
			'loyalist' => array(
				'offer_type' => 'none', 'offer_value' => 0, 'timing' => 'monthly', 'channel' => 'cascade', 'p_convert' => 0.10,
				'evidence' => __( 'Your most engaged repeat buyers — a recognition touch (recommendations, early access) sustains them.', 'ai-eshop-optimizer' ),
			),
			'deal_driven' => array(
				'offer_type' => 'percent', 'offer_value' => 10, 'timing' => 'weekly', 'channel' => 'cascade', 'p_convert' => 0.04,
				'evidence' => __( 'Buy mostly on discount — a modest, targeted offer moves them without training the full base to wait for sales.', 'ai-eshop-optimizer' ),
			),
			self::MOTION_FALLBACK => array(
				'offer_type' => 'none', 'offer_value' => 0, 'timing' => 'weekly', 'channel' => 'cascade', 'p_convert' => 0.02,
				'evidence' => '', 
			),
		);
	}

	
	private static function motion_for_rule( $rule ) {
		$fields   = array();
		$segments = array();
		self::walk_rule( $rule, $fields, $segments );
		if ( isset( $fields['instigator'] ) ) {
			return 'instigator';
		}
		if ( isset( $segments['loyalist'] ) || isset( $segments['vip'] ) ) {
			return 'loyalist';
		}
		if ( isset( $segments['overdue'] ) ) {
			
			
			
			
			return 'overdue';
		}
		if ( isset( $fields['discount_share'] ) || isset( $segments['discount_hunter'] ) ) {
			return 'deal_driven';
		}
		if ( isset( $fields['reactivation_score'] ) ) {
			return 'reactivation_ready';
		}
		if ( isset( $fields['dormancy_cohort'] ) ) {
			
			
			return isset( $fields['ltv_total'] ) ? 'reactivation' : 'winback';
		}
		return self::MOTION_FALLBACK;
	}

	
	private static function walk_rule( $rule, &$fields, &$segments ) {
		if ( ! is_array( $rule ) || empty( $rule['conditions'] ) || ! is_array( $rule['conditions'] ) ) {
			return;
		}
		foreach ( $rule['conditions'] as $c ) {
			if ( ! is_array( $c ) ) {
				continue;
			}
			if ( isset( $c['conditions'] ) ) {
				self::walk_rule( $c, $fields, $segments );
				continue;
			}
			$f = (string) ( $c['field'] ?? '' );
			if ( '' === $f ) {
				continue;
			}
			$fields[ $f ] = true;
			if ( 'segments' === $f ) {
				
				foreach ( (array) ( $c['value'] ?? array() ) as $v ) {
					$segments[ (string) $v ] = true;
				}
			}
		}
	}

	
	private static function miners() {
		if ( null !== self::$miners ) {
			return self::$miners;
		}
		if ( ! class_exists( 'AIEO_Audience_Rules' ) ) {
			
			
			
			return array();
		}
		$motions = self::motions();
		$out     = array();
		
		
		
		
		$seen = array();

		$sources = array(
			AIEO_Audience_Rules::suggestions(),
			AIEO_Audience_Rules::list_saved(),
		);
		foreach ( $sources as $rows ) {
			foreach ( (array) $rows as $a ) {
				$rule = isset( $a['rule'] ) && is_array( $a['rule'] ) ? $a['rule'] : array();
				if ( empty( $rule['conditions'] ) ) {
					continue; 
				}
				$fp = sha1( (string) wp_json_encode( $rule ) );
				if ( isset( $seen[ $fp ] ) ) {
					continue;
				}
				$seen[ $fp ] = true;
				
				$label  = trim( (string) ( $a['label'] ?? $a['name'] ?? '' ) );
				$motion = self::motion_for_rule( $rule );
				$m      = $motions[ $motion ];
				if ( '' === (string) $m['evidence'] ) {
					
					
					
					
					$m['evidence'] = isset( $a['summary'] ) && '' !== (string) $a['summary']
						? (string) $a['summary']
						: AIEO_Audience_Rules::describe_rule( $rule );
				}
				$out[] = array_merge( $m, array(
					'kind'  => $motion,
					'label' => '' !== $label ? $label : AIEO_Audience_Rules::describe_rule( $rule ),
					'rule'  => $rule,
				) );
			}
		}
		self::$miners = $out;
		return self::$miners;
	}

	
	
	

	
	public static function scan() {
		
		if ( class_exists( 'AIEO_DMM_Module_Base' ) && ! AIEO_DMM_Module_Base::is_aieo_pro_user() ) {
			return array( 'scanned' => 0, 'upserted' => 0, 'narrated' => 0, 'skipped' => 'not_pro' );
		}
		global $wpdb;
		if ( function_exists( 'aieo_subjects_maybe_install' ) ) {
			aieo_subjects_maybe_install();
		}
		$t = aieo_proposals_table();
		if ( ! self::table( $t ) || ! class_exists( 'AIEO_Audience_Rules' ) ) {
			return array( 'scanned' => 0, 'upserted' => 0, 'narrated' => 0 );
		}
		$lock = $wpdb->prefix . 'aieo_proposal_scan';
		if ( 1 !== (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 0)', $lock ) ) ) {
			return array( 'scanned' => 0, 'upserted' => 0, 'narrated' => 0 );
		}
		$margin     = (float) apply_filters( 'aieo_margin_ratio', (float) get_option( 'aieo_margin_ratio', 0.4 ) );
		$now        = gmdate( 'Y-m-d H:i:s' );
		$upserted   = 0;
		$to_narrate = array();
		$live       = array();
		try {
			foreach ( self::miners() as $m ) {
				$stats = AIEO_Audience_Rules::audience_stats( $m['rule'] );
				$match       = (int) $stats['count'];
				$contactable = (int) $stats['contactable']; 
				$aov = $stats['avg_aov'] > 0 ? $stats['avg_aov'] : (float) apply_filters( 'aieo_default_aov', 40.0 );
				
				
				
				$cal = self::realized_p_convert( $m['kind'], (float) $m['p_convert'] );
				$p_convert = $cal['rate'];
				$ev  = self::predicted_ev( $contactable, $aov, $p_convert, $m['offer_type'], (float) $m['offer_value'], $margin );
				$sig = sha1( $m['kind'] . '|' . wp_json_encode( $m['rule'] ) . '|' . $m['offer_type'] . '|' . $m['offer_value'] );
				$live[] = $sig;
				$evidence = wp_json_encode( array( 'evidence' => $m['evidence'], 'avg_aov' => round( $aov, 2 ), 'p_convert' => round( $p_convert, 4 ), 'p_convert_prior' => $m['p_convert'], 'calibrated_from_sends' => (int) $cal['sent'], 'match' => $match, 'contactable' => $contactable ) );

				$existing = $wpdb->get_row( $wpdb->prepare(
					
					"SELECT id, status FROM `{$t}` WHERE signature = %s",
					$sig
				), ARRAY_A );
				if ( $existing ) {
					
					
					
					if ( 'new' === $existing['status'] ) {
						$wpdb->update( $t, array(
							'audience_count' => $contactable, 'predicted_ev' => $ev, 'evidence_json' => $evidence,
							'timing' => $m['timing'], 'channel' => $m['channel'],
							'status' => ( $contactable < self::MIN_AUDIENCE ) ? 'expired' : 'new',
							'updated' => $now,
						), array( 'id' => (int) $existing['id'] ) );
					}
					continue;
				}
				if ( $contactable < self::MIN_AUDIENCE ) {
					continue; 
				}
				$wpdb->insert( $t, array(
					'signature'      => $sig,
					'kind'           => $m['kind'],
					'title'          => $m['label'], 
					'rule_json'      => wp_json_encode( $m['rule'] ),
					'offer_type'     => $m['offer_type'],
					'offer_value'    => (float) $m['offer_value'],
					'timing'         => $m['timing'],
					'channel'        => $m['channel'],
					'audience_count' => $contactable,
					'predicted_ev'   => $ev,
					'evidence_json'  => $evidence,
					'status'         => 'new',
					'created'        => $now,
					'updated'        => $now,
				) );
				$id = (int) $wpdb->insert_id;
				if ( $id > 0 ) {
					$upserted++;
					$to_narrate[] = array( 'id' => $id, 'm' => $m, 'count' => $contactable, 'ev' => $ev, 'aov' => $aov );
				}
			}
			
			
			
			
			
			self::retire_orphans( $live );
			
			$anomalies = self::scan_anomalies();
			update_option( 'aieo_proposals_last_scan', $now, false );
		} finally {
			$wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock ) );
		}
		
		$narrated = 0;
		foreach ( array_slice( $to_narrate, 0, self::MAX_NARRATE ) as $n ) {
			if ( self::narrate( $n['id'], $n['m'], $n['count'], $n['ev'], $n['aov'] ) ) {
				$narrated++;
			}
		}
		return array( 'scanned' => count( self::miners() ), 'upserted' => $upserted, 'narrated' => $narrated, 'anomalies' => isset( $anomalies ) ? $anomalies : 0 );
	}

	
	private static function retire_orphans( array $signatures ) {
		global $wpdb;
		if ( empty( $signatures ) ) {
			
			
			
			
			return 0;
		}
		$t  = aieo_proposals_table();
		$ph = implode( ',', array_fill( 0, count( $signatures ), '%s' ) );
		
		return (int) $wpdb->query( $wpdb->prepare(
			"UPDATE `{$t}` SET status = 'expired', updated = %s WHERE status = 'new' AND kind <> %s AND signature NOT IN ({$ph})",
			array_merge( array( gmdate( 'Y-m-d H:i:s' ), self::ANOMALY_KIND ), array_values( $signatures ) )
		) );
	}

	
	
	

	const ANOMALY_KIND = 'anomaly';

	
	public static function scan_anomalies() {
		$found = array();
		$o = self::anomaly_open_rate();
		if ( $o ) { $found['email_open_rate'] = $o; }
		$v = self::anomaly_voc();
		if ( $v ) { $found['voc_nps'] = $v; }
		$c = self::anomaly_checkout();
		if ( $c ) { $found['checkout_friction'] = $c; }
		foreach ( array( 'email_open_rate', 'voc_nps', 'checkout_friction' ) as $metric ) {
			if ( isset( $found[ $metric ] ) ) {
				self::upsert_anomaly( $metric, $found[ $metric ] );
			} else {
				self::retire_anomaly( $metric );
			}
		}
		return count( $found );
	}

	
	private static function upsert_anomaly( $metric, $a ) {
		global $wpdb;
		$t   = aieo_proposals_table();
		$now = gmdate( 'Y-m-d H:i:s' );
		$sig = sha1( 'anomaly|' . $metric );
		$evidence = wp_json_encode( array( 'anomaly' => true, 'metric' => $metric, 'evidence' => (string) $a['narrative'], 'detail' => isset( $a['detail'] ) ? $a['detail'] : array() ) );
		$existing = $wpdb->get_row( $wpdb->prepare( "SELECT id, status FROM `{$t}` WHERE signature = %s", $sig ), ARRAY_A );
		if ( $existing ) {
			
			
			
			
			if ( 'new' === $existing['status'] || 'expired' === $existing['status'] ) {
				$wpdb->update( $t, array( 'kind' => self::ANOMALY_KIND, 'title' => (string) $a['title'], 'narrative' => (string) $a['narrative'], 'predicted_ev' => (float) ( $a['ev'] ?? 0 ), 'evidence_json' => $evidence, 'status' => 'new', 'updated' => $now ), array( 'id' => (int) $existing['id'] ) );
			}
			return;
		}
		$wpdb->insert( $t, array(
			'signature' => $sig, 'kind' => self::ANOMALY_KIND, 'title' => (string) $a['title'], 'narrative' => (string) $a['narrative'],
			'rule_json' => '', 'offer_type' => 'none', 'offer_value' => 0, 'timing' => '', 'channel' => '',
			'audience_count' => 0, 'predicted_ev' => (float) ( $a['ev'] ?? 0 ), 'evidence_json' => $evidence,
			'status' => 'new', 'created' => $now, 'updated' => $now,
		) );
	}

	
	private static function retire_anomaly( $metric ) {
		global $wpdb;
		$t = aieo_proposals_table();
		
		
		$wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET status='expired', updated=%s WHERE signature=%s AND status IN ('new','dismissed')", gmdate( 'Y-m-d H:i:s' ), sha1( 'anomaly|' . $metric ) ) );
	}

	
	private static function anomaly_open_rate() {
		global $wpdb;
		$disp = $wpdb->prefix . 'aieo_push_dispatch';
		if ( ! self::table( $disp ) || ! (bool) $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$disp}` LIKE %s", 'opened_at' ) ) ) {
			return null;
		}
		$q = function ( $from, $to ) use ( $wpdb, $disp ) {
			
			return $wpdb->get_row( $wpdb->prepare(
				"SELECT COUNT(*) sent, SUM(opened_at IS NOT NULL) opens FROM `{$disp}`
				 WHERE channel='email' AND state IN ('sent','clicked','converted') AND sent_at >= %s AND sent_at < %s",
				$from, $to
			), ARRAY_A );
		};
		$now    = time();
		$recent = $q( gmdate( 'Y-m-d H:i:s', $now - 7 * DAY_IN_SECONDS ), gmdate( 'Y-m-d H:i:s', $now ) );
		$prior  = $q( gmdate( 'Y-m-d H:i:s', $now - 14 * DAY_IN_SECONDS ), gmdate( 'Y-m-d H:i:s', $now - 7 * DAY_IN_SECONDS ) );
		$sr = (int) $recent['sent'];
		$sp = (int) $prior['sent'];
		if ( $sr < 20 || $sp < 20 ) {
			return null;
		}
		$rr = $sr > 0 ? (int) $recent['opens'] / $sr : 0;
		$rp = $sp > 0 ? (int) $prior['opens'] / $sp : 0;
		if ( $rp <= 0 || $rr >= $rp * 0.7 ) {
			return null;
		}
		$drop = (int) round( ( 1 - $rr / $rp ) * 100 );
		return array(
			'title'     => sprintf( __( '⚠ Email open rate down %d%% this week', 'ai-eshop-optimizer' ), $drop ),
			'narrative' => sprintf( __( 'Open rate fell from %1$s%% to %2$s%% (%3$s vs %4$s emails sent). Check subject lines, deliverability / spam placement, and send frequency.', 'ai-eshop-optimizer' ), round( $rp * 100, 1 ), round( $rr * 100, 1 ), number_format_i18n( $sp ), number_format_i18n( $sr ) ),
			'ev'        => 0,
			'detail'    => array( 'rate_prior' => round( $rp, 4 ), 'rate_recent' => round( $rr, 4 ), 'sent_recent' => $sr ),
		);
	}

	
	private static function anomaly_voc() {
		global $wpdb;
		$si = $wpdb->prefix . 'aieo_survey_intelligence';
		if ( ! self::table( $si ) ) {
			return null;
		}
		$q = function ( $from, $to ) use ( $wpdb, $si ) {
			
			return $wpdb->get_row( $wpdb->prepare(
				"SELECT SUM(nps_class='promoter') p, SUM(nps_class='detractor') d, SUM(nps_class IN ('promoter','passive','detractor')) n
				 FROM `{$si}` WHERE f18_submitted_at >= %s AND f18_submitted_at < %s",
				$from, $to
			), ARRAY_A );
		};
		$now    = time();
		$recent = $q( gmdate( 'Y-m-d H:i:s', $now - 30 * DAY_IN_SECONDS ), gmdate( 'Y-m-d H:i:s', $now ) );
		$prior  = $q( gmdate( 'Y-m-d H:i:s', $now - 60 * DAY_IN_SECONDS ), gmdate( 'Y-m-d H:i:s', $now - 30 * DAY_IN_SECONDS ) );
		$nr = (int) $recent['n'];
		$np = (int) $prior['n'];
		if ( $nr < 10 || $np < 10 ) {
			return null;
		}
		$sr = 100 * ( (int) $recent['p'] - (int) $recent['d'] ) / $nr;
		$sp = 100 * ( (int) $prior['p'] - (int) $prior['d'] ) / $np;
		if ( $sr >= $sp - 15 ) {
			return null;
		}
		return array(
			'title'     => sprintf( __( '⚠ Customer NPS down %d points', 'ai-eshop-optimizer' ), (int) round( $sp - $sr ) ),
			'narrative' => sprintf( __( 'Net Promoter Score fell from %1$d to %2$d over the last 30 days (%3$s recent responses). Review recent detractor feedback and any service / shipping changes.', 'ai-eshop-optimizer' ), (int) round( $sp ), (int) round( $sr ), number_format_i18n( $nr ) ),
			'ev'        => 0,
			'detail'    => array( 'nps_prior' => round( $sp, 1 ), 'nps_recent' => round( $sr, 1 ) ),
		);
	}

	
	private static function anomaly_checkout() {
		global $wpdb;
		$ct = $wpdb->prefix . 'aieo_checkout_timing';
		if ( ! self::table( $ct ) ) {
			return null;
		}
		$q = function ( $from, $to ) use ( $wpdb, $ct ) {
			
			return $wpdb->get_row( $wpdb->prepare(
				"SELECT COUNT(*) n, AVG(active_seconds) a FROM `{$ct}` WHERE completed_at >= %s AND completed_at < %s AND has_coupon = 0",
				$from, $to
			), ARRAY_A );
		};
		$now    = time();
		$recent = $q( gmdate( 'Y-m-d H:i:s', $now - 7 * DAY_IN_SECONDS ), gmdate( 'Y-m-d H:i:s', $now ) );
		$prior  = $q( gmdate( 'Y-m-d H:i:s', $now - 14 * DAY_IN_SECONDS ), gmdate( 'Y-m-d H:i:s', $now - 7 * DAY_IN_SECONDS ) );
		$nr = (int) $recent['n'];
		$np = (int) $prior['n'];
		if ( $nr < 10 || $np < 10 ) {
			return null;
		}
		$ar = (float) $recent['a'];
		$ap = (float) $prior['a'];
		if ( $ap <= 0 || $ar <= $ap * 1.3 ) {
			return null;
		}
		$rise = (int) round( ( ( $ar / $ap ) - 1 ) * 100 );
		return array(
			'title'     => sprintf( __( '⚠ Checkout is %d%% slower this week', 'ai-eshop-optimizer' ), $rise ),
			'narrative' => sprintf( __( 'Average active checkout time rose from %1$ds to %2$ds. Check for a new required field, a slow third-party script, or a payment-step change.', 'ai-eshop-optimizer' ), (int) round( $ap ), (int) round( $ar ) ),
			'ev'        => 0,
			'detail'    => array( 'avg_prior' => (int) round( $ap ), 'avg_recent' => (int) round( $ar ) ),
		);
	}

	
	const PRIOR_PSEUDO = 200;

	
	private static function realized_p_convert( $kind, $prior ) {
		$prior = (float) $prior;
		
		
		$kind  = self::canonical_kind( $kind );
		
		
		
		
		if ( ! self::kind_has_offer( $kind ) ) {
			return array( 'rate' => $prior, 'sent' => 0, 'redeemed' => 0 );
		}
		$sent     = 0;
		$redeemed = 0;
		if ( class_exists( 'AIEO_Workflow_Stats' ) ) {
			
			
			
			foreach ( self::workflow_ids_for_kind( $kind ) as $wid ) {
				$s        = AIEO_Workflow_Stats::get( (int) $wid );
				$sent    += (int) $s['sent'];
				$redeemed += (int) $s['coupons_redeemed'];
			}
		}
		$k    = max( 1, (int) apply_filters( 'aieo_proposal_prior_pseudocount', self::PRIOR_PSEUDO ) ); 
		$rate = ( $redeemed + $prior * $k ) / max( 1, $sent + $k );
		$rate = min( 1.0, max( 0.0, $rate ) ); 
		return array( 'rate' => $rate, 'sent' => $sent, 'redeemed' => $redeemed );
	}

	
	private static function kind_has_offer( $kind ) {
		$motions = self::motions();
		
		
		
		$kind    = self::canonical_kind( $kind );
		$offer   = isset( $motions[ $kind ] ) ? (string) $motions[ $kind ]['offer_type'] : '';
		return in_array( $offer, array( 'percent', 'fixed' ), true );
	}

	
	private static function workflow_ids_for_kind( $kind ) {
		global $wpdb;
		if ( ! function_exists( 'aieo_workflows_table' ) ) {
			return array();
		}
		$t       = aieo_workflows_table();
		$aliases = self::kind_aliases( $kind );
		$needles = array();
		foreach ( $aliases as $slug ) {
			$needles[] = '%' . $wpdb->esc_like( '"proposal_kind":"' . $slug . '"' ) . '%';
		}
		$where = implode( ' OR ', array_fill( 0, count( $needles ), 'trigger_config LIKE %s' ) );
		
		return array_map( 'intval', (array) $wpdb->get_col( $wpdb->prepare(
			"SELECT id FROM `{$t}` WHERE {$where}",
			$needles
		) ) );
	}

	
	public static function calibration() {
		global $wpdb;
		$out = array();
		
		$accepted = array();
		$t = function_exists( 'aieo_proposals_table' ) ? aieo_proposals_table() : '';
		if ( $t && (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t ) {
			
			$rows = (array) $wpdb->get_results( "SELECT kind, predicted_ev, created_workflow_id FROM `{$t}` WHERE status = 'accepted' AND created_workflow_id > 0", ARRAY_A );
			foreach ( $rows as $r ) {
				$wid = (int) $r['created_workflow_id'];
				$rev = class_exists( 'AIEO_Workflow_Stats' ) ? (float) AIEO_Workflow_Stats::get( $wid )['revenue'] : 0.0;
				
				
				
				
				
				$k   = self::canonical_kind( (string) $r['kind'] );
				if ( ! isset( $accepted[ $k ] ) ) {
					$accepted[ $k ] = array( 'n' => 0, 'predicted' => 0.0, 'realized' => 0.0 );
				}
				$accepted[ $k ]['n']++;
				$accepted[ $k ]['predicted'] += (float) $r['predicted_ev'];
				$accepted[ $k ]['realized']  += $rev;
			}
		}
		
		
		
		
		$by_kind = array();
		foreach ( self::miners() as $m ) {
			if ( isset( $by_kind[ $m['kind'] ] ) ) {
				$by_kind[ $m['kind'] ]['audiences']++;
				continue;
			}
			$by_kind[ $m['kind'] ] = array( 'm' => $m, 'audiences' => 1 );
		}
		foreach ( $by_kind as $row ) {
			$m   = $row['m'];
			$cal = self::realized_p_convert( $m['kind'], (float) $m['p_convert'] );
			$a   = isset( $accepted[ $m['kind'] ] ) ? $accepted[ $m['kind'] ] : array( 'n' => 0, 'predicted' => 0.0, 'realized' => 0.0 );
			$out[] = array(
				'kind'               => $m['kind'],
				'label'              => $m['label'],
				'audiences'          => (int) $row['audiences'],
				'prior_rate'         => (float) $m['p_convert'],
				'realized_rate'      => round( $cal['rate'], 4 ),
				'based_on_sends'     => (int) $cal['sent'],
				'redeemed'           => (int) $cal['redeemed'],
				'accepted_proposals' => (int) $a['n'],
				'predicted_revenue'  => round( $a['predicted'], 2 ),
				'realized_revenue'   => round( $a['realized'], 2 ),
			);
		}
		return $out;
	}

	
	private static function predicted_ev( $count, $aov, $p_convert, $offer_type, $offer_value, $margin ) {
		$disc_on_convert = ( 'percent' === $offer_type ) ? ( $aov * $offer_value / 100.0 )
			: ( ( 'fixed' === $offer_type ) ? min( $offer_value, $aov ) : 0.0 );
		$value_per_convert = max( 0.0, $margin * $aov - $disc_on_convert );
		$send_cost = (float) apply_filters( 'aieo_send_cost_per_contact', 0.0003 ); 
		return round( $count * $p_convert * $value_per_convert - $count * $send_cost, 2 );
	}

	
	
	

	public static function narrate( $proposal_id, $m, $count, $ev, $aov ) {
		if ( 'yes' !== get_option( 'aieo_proposals_narrate', 'yes' ) || ! class_exists( 'AI_eShop_Chat_Anthropic_API' ) ) {
			return false;
		}
		
		
		$money = static function ( $n ) {
			return function_exists( 'wc_price' )
				? html_entity_decode( wp_strip_all_tags( wc_price( (float) $n, array( 'decimals' => 0 ) ) ), ENT_QUOTES, 'UTF-8' )
				: '€' . round( (float) $n );
		};
		$facts = sprintf(
			"Opportunity: %s. Audience: %d customers. Recommended offer: %s. Timing: %s. Channel: %s. Avg order value: %s. Predicted value: %s. Why it qualifies: %s.",
			$m['label'], $count,
			'none' === $m['offer_type'] ? 'no discount' : ( 'percent' === $m['offer_type'] ? $m['offer_value'] . '% off' : ( class_exists( 'AIEO_Coupon_Minter' ) ? AIEO_Coupon_Minter::label( (string) $m['offer_type'], (float) $m['offer_value'] ) : $money( $m['offer_value'] ) . ' off' ) ),
			$m['timing'], $m['channel'], $money( $aov ), $money( $ev ), $m['evidence']
		);
		$system = 'You name and explain marketing-campaign opportunities for a store operator. You are given ONLY deterministic facts — never invent numbers, audiences, or targeting. Reply with a compact JSON object {"title": "<=8 words punchy campaign name", "why": "1-2 plain sentences on why to run it and the trade-off"}. No demographic or protected-category language.';
		try {
			$src  = class_exists( 'AIEO_AI_Sources' ) && defined( 'AIEO_AI_Sources::CHAT_PROPOSAL' ) ? AIEO_AI_Sources::CHAT_PROPOSAL : 'aieo.chat.proposal';
			$resp = AI_eShop_Chat_Anthropic_API::get_instance()->chat_completion(
				array( array( 'role' => 'user', 'content' => $facts ) ),
				array( 'system' => $system, 'source' => $src, 'max_tokens' => 220 )
			);
			if ( is_wp_error( $resp ) ) {
				return false;
			}
			
			
			$text = '';
			if ( is_array( $resp ) && ! empty( $resp['content'][0]['text'] ) ) {
				$text = (string) $resp['content'][0]['text'];
			} elseif ( is_array( $resp ) && isset( $resp['content'] ) && is_string( $resp['content'] ) ) {
				$text = (string) $resp['content'];
			} elseif ( is_string( $resp ) ) {
				$text = $resp;
			}
			$json = self::extract_json( $text );
			if ( ! is_array( $json ) || ! is_string( $json['title'] ?? null ) || '' === trim( (string) $json['title'] ) ) {
				return false; 
			}
			global $wpdb;
			$t    = aieo_proposals_table();
			$done = $wpdb->update( $t, array(
				'title'     => mb_substr( sanitize_text_field( (string) $json['title'] ), 0, 80 ),
				'narrative' => is_string( $json['why'] ?? null ) ? sanitize_textarea_field( (string) $json['why'] ) : '',
			), array( 'id' => (int) $proposal_id ) );
			return false !== $done && $done > 0;
		} catch ( \Throwable $e ) {
			return false;
		}
	}

	private static function extract_json( $text ) {
		
		
		foreach ( array( '/\{.*\}/s', '/\{.*?\}/s' ) as $re ) {
			if ( preg_match( $re, (string) $text, $mm ) ) {
				$d = json_decode( $mm[0], true );
				if ( is_array( $d ) ) {
					return $d;
				}
			}
		}
		return null;
	}

	
	
	

	public static function list_new( $limit = 50 ) {
		global $wpdb;
		$t = aieo_proposals_table();
		if ( ! self::table( $t ) ) {
			return array();
		}
		$rows = $wpdb->get_results( $wpdb->prepare(
			
			"SELECT * FROM `{$t}` WHERE status = 'new' ORDER BY predicted_ev DESC LIMIT %d",
			(int) $limit
		), ARRAY_A );
		return array_map( array( __CLASS__, 'hydrate' ), (array) $rows );
	}

	private static function hydrate( $r ) {
		$r['rule']     = json_decode( (string) ( $r['rule_json'] ?? '' ), true ) ?: array();
		$r['evidence'] = json_decode( (string) ( $r['evidence_json'] ?? '' ), true ) ?: array();
		$r['id']       = (int) $r['id'];
		unset( $r['rule_json'], $r['evidence_json'] );
		return $r;
	}

	
	public static function accept( $proposal_id ) {
		global $wpdb;
		$t = aieo_proposals_table();
		if ( ! self::table( $t ) ) {
			return new WP_Error( 'no_table', 'proposals table missing' );
		}
		$now = gmdate( 'Y-m-d H:i:s' );
		
		
		$claimed = (int) $wpdb->query( $wpdb->prepare( "UPDATE `{$t}` SET status = 'accepted', updated = %s WHERE id = %d AND status = 'new'", $now, (int) $proposal_id ) );
		if ( $claimed < 1 ) {
			return new WP_Error( 'not_open', 'proposal not open' );
		}
		$p = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM `{$t}` WHERE id = %d", (int) $proposal_id ), ARRAY_A );
		
		
		if ( $p && self::ANOMALY_KIND === (string) $p['kind'] ) {
			$wpdb->update( $t, array( 'status' => 'dismissed', 'updated' => $now ), array( 'id' => (int) $proposal_id ) );
			return new WP_Error( 'anomaly_alert', __( 'This is an alert, not a campaign — acknowledged.', 'ai-eshop-optimizer' ) );
		}
		if ( ! $p || ! class_exists( 'AIEO_Audience_Rules' ) || ! class_exists( 'AIEO_Workflows' ) ) {
			$wpdb->update( $t, array( 'status' => 'new' ), array( 'id' => (int) $proposal_id ) ); 
			return new WP_Error( 'deps', 'audience/workflow modules unavailable' );
		}
		$rule  = json_decode( (string) $p['rule_json'], true ) ?: array();
		$title = trim( (string) $p['title'] ) !== '' ? trim( (string) $p['title'] ) : (string) $p['kind'];
		
		
		
		$aid = (int) AIEO_Audience_Rules::create( sprintf( '%s — #%d', $title, (int) $proposal_id ), $rule );
		if ( $aid <= 0 ) {
			$wpdb->update( $t, array( 'status' => 'new' ), array( 'id' => (int) $proposal_id ) );
			return new WP_Error( 'audience_save', 'could not save audience' );
		}
		$offer = (string) $p['offer_type'];
		
		
		
		$copy = self::starter_copy( (string) $p['kind'] );
		$wid  = (int) AIEO_Workflows::save( array(
			'name'           => $title, 
			'status'         => 'draft', 
			'trigger_type'   => 'scheduled',
			
			
			'trigger_config' => array( 'interval' => (string) $p['timing'], 'proposal_kind' => (string) $p['kind'], 'proposal_predicted_ev' => (float) ( $p['predicted_ev'] ?? 0 ) ),
			'audience_id'    => $aid,
			'steps'          => array( array(
				'type'           => 'send',
				'channel'        => (string) $p['channel'],
				'subject'        => $copy['subject'],
				'body'           => $copy['body'],
				'product_source' => 'recommendations',
				'offer'          => in_array( $offer, array( 'percent', 'fixed' ), true ) ? $offer : 'none',
				'offer_value'    => (float) $p['offer_value'],
				'offer_ttl_days' => 14,
			) ),
		) );
		if ( $wid <= 0 ) {
			
			
			
			if ( function_exists( 'aieo_audiences_table' ) ) {
				$wpdb->delete( aieo_audiences_table(), array( 'id' => $aid ) );
			}
			$wpdb->update( $t, array( 'status' => 'new', 'updated' => $now ), array( 'id' => (int) $proposal_id ) );
			return new WP_Error( 'workflow_save', 'could not create draft workflow' );
		}
		$wpdb->update( $t, array(
			'created_audience_id' => $aid,
			'created_workflow_id' => $wid,
			'updated'             => $now,
		), array( 'id' => (int) $proposal_id ) );

		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		if ( class_exists( 'AIEO_Campaign_Forecasts' ) ) {
			AIEO_Campaign_Forecasts::snapshot( array(
				'wf_id'             => $wid,
				'proposal_id'       => (int) $proposal_id,
				'vector'            => (string) $p['channel'],
				'object_type'       => 'workflow',
				'object_id'         => $wid,
				'predicted_revenue' => (float) ( $p['predicted_ev'] ?? 0 ),
				'confidence'        => (string) ( $p['confidence'] ?? '' ),
				'components_json'   => array(
					'kind'     => (string) $p['kind'],
					'timing'   => (string) $p['timing'],
					'audience' => $aid,
				),
			) );
		}

		return array( 'workflow_id' => $wid, 'audience_id' => $aid );
	}

	
	private static function starter_copy( $kind ) {
		$map = array(
			'instigator'         => array( 'subject' => __( 'Still thinking it over?', 'ai-eshop-optimizer' ), 'body' => __( 'You left something behind — here is a little nudge to help you decide.', 'ai-eshop-optimizer' ) ),
			'reactivation'       => array( 'subject' => __( 'We would love to see you back', 'ai-eshop-optimizer' ), 'body' => __( 'It has been a while — here are a few things we think you will like.', 'ai-eshop-optimizer' ) ),
			'winback'            => array( 'subject' => __( 'A little something to win you back', 'ai-eshop-optimizer' ), 'body' => __( 'We have missed you — enjoy this on your next order.', 'ai-eshop-optimizer' ) ),
			'reactivation_ready' => array( 'subject' => __( 'Look what is new since your last order', 'ai-eshop-optimizer' ), 'body' => __( 'It has been a little while — here are a few picks based on what you bought before.', 'ai-eshop-optimizer' ) ),
			'overdue'            => array( 'subject' => __( 'Time for a refill?', 'ai-eshop-optimizer' ), 'body' => __( 'You might be running low — reorder in a couple of taps.', 'ai-eshop-optimizer' ) ),
			'loyalist'           => array( 'subject' => __( 'Thank you for being a regular', 'ai-eshop-optimizer' ), 'body' => __( 'A few picks we think you will love, just for you.', 'ai-eshop-optimizer' ) ),
			'deal_driven'        => array( 'subject' => __( 'A pick just for you', 'ai-eshop-optimizer' ), 'body' => __( 'Here is an offer we thought you would appreciate.', 'ai-eshop-optimizer' ) ),
			
			
			self::MOTION_FALLBACK => array( 'subject' => __( 'A few picks for you', 'ai-eshop-optimizer' ), 'body' => __( 'Here are a few things we thought you would like.', 'ai-eshop-optimizer' ) ),
		);
		
		
		
		
		$kind = self::canonical_kind( $kind );
		
		
		
		
		
		
		return isset( $map[ $kind ] ) ? $map[ $kind ] : $map[ self::MOTION_FALLBACK ];
	}

	public static function dismiss( $proposal_id ) {
		global $wpdb;
		$t = aieo_proposals_table();
		if ( ! self::table( $t ) ) {
			return false;
		}
		return false !== $wpdb->update( $t, array( 'status' => 'dismissed', 'updated' => gmdate( 'Y-m-d H:i:s' ) ), array( 'id' => (int) $proposal_id, 'status' => 'new' ) );
	}

	

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