<?php

namespace AIEO\Claude\Abilities;

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

class VocSummaryAbility {
	const ABILITY_NAME = 'aieo/voc-summary';

	public static function register() {
		wp_register_ability( self::ABILITY_NAME, array(
			'label'               => __( 'Voice-of-customer survey summary', 'ai-eshop-optimizer' ),
			'description'         => __( 'Summarise the post-order survey Voice-of-customer: NPS split (promoters/passives/detractors) and net score, VoC band and mean score, the flagged shipping-price-concern rate, mean satisfaction, the VoC→repurchase link (do promoters actually reorder more than detractors?), a count + preview of HIGH-VALUE detractors worth a recovery flow, and a sample of recent detractor open feedback. Read-only. Requires the Rooster One plugin with survey intelligence built.', 'ai-eshop-optimizer' ),
			'category'            => \AIEO\Claude\Bootstrap::CATEGORY,
			'input_schema'        => array( 'default' => array(), 'type' => 'object', 'properties' => array(
				'min_ltv_for_recovery' => array( 'type' => 'number', 'description' => 'LTV floor (in the store currency) that makes a detractor a recovery target (default 100).' ),
			) ),
			'output_schema'       => array( 'type' => 'object' ),
			'execute_callback'    => array( __CLASS__, 'execute' ),
			'permission_callback' => array( 'AIEO\\Claude\\Abilities\\WorkflowAbilityHelpers', 'can' ),
			'meta'                => array( 'annotations' => array( 'readonly' => true, 'idempotent' => true ), 'show_in_rest' => true ),
		) );
	}

	public static function execute( $input = null ) {
		global $wpdb;
		$si = $wpdb->prefix . 'aieo_survey_intelligence';
		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $si ) ) !== $si ) {
			return array( 'available' => false, 'note' => __( 'Survey intelligence has not been built yet.', 'ai-eshop-optimizer' ) );
		}
		$min_ltv = isset( $input['min_ltv_for_recovery'] ) ? max( 0, (float) $input['min_ltv_for_recovery'] ) : 100.0;

		
		$dist = array( 'promoter' => 0, 'passive' => 0, 'detractor' => 0 );
		
		foreach ( (array) $wpdb->get_results( "SELECT nps_class, COUNT(*) n FROM `{$si}` WHERE nps_class IN ('promoter','passive','detractor') GROUP BY nps_class", ARRAY_A ) as $r ) {
			$dist[ $r['nps_class'] ] = (int) $r['n'];
		}
		$classified = $dist['promoter'] + $dist['passive'] + $dist['detractor'];
		$nps_score  = $classified > 0 ? round( ( ( $dist['promoter'] - $dist['detractor'] ) / $classified ) * 100 ) : 0;

		
		
		
		
		
		
		
		
		
		
		
		
		$answered_cols = array();
		foreach ( array( 'nps_score', 'overall_satisfaction', 'site_ux_avg' ) as $vc ) {
			
			if ( $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM `{$si}` LIKE %s", $vc ) ) ) {
				$answered_cols[] = "`{$vc}` > 0";
			}
		}
		
		
		$answered = $answered_cols ? '( ' . implode( ' OR ', $answered_cols ) . ' )' : 'voc_score>0';
		
		$agg = (array) $wpdb->get_row(
			"SELECT
				SUM(CASE WHEN {$answered} AND voc_band='high' THEN 1 ELSE 0 END) high_band,
				SUM(CASE WHEN {$answered} AND voc_band='mid'  THEN 1 ELSE 0 END) mid_band,
				SUM(CASE WHEN {$answered} AND voc_band='low'  THEN 1 ELSE 0 END) low_band,
				AVG(CASE WHEN {$answered} THEN voc_score END) avg_voc,
				AVG(NULLIF(overall_satisfaction,0)) avg_sat,
				SUM(CASE WHEN f18_entry_id>0 AND shipping_price_concern=1 THEN 1 ELSE 0 END) ship_flag,
				SUM(CASE WHEN f18_entry_id>0 THEN 1 ELSE 0 END) ship_asked,
				COUNT(*) total
			 FROM `{$si}`", ARRAY_A
		);
		$total      = (int) ( $agg['total'] ?? 0 );
		$ship_asked = (int) ( $agg['ship_asked'] ?? 0 );

		
		
		$repeat = (array) $wpdb->get_results(
			"SELECT nps_class,
				ROUND(100 * SUM(orders_after_survey > 0) / NULLIF(COUNT(*),0), 1) repeat_pct,
				ROUND(AVG(spend_after_survey), 2) avg_spend_after
			 FROM `{$si}` WHERE nps_class IN ('promoter','detractor') GROUP BY nps_class", ARRAY_A
		);
		$repeat_map = array();
		foreach ( $repeat as $r ) {
			$repeat_map[ $r['nps_class'] ] = array( 'repeat_pct' => (float) $r['repeat_pct'], 'avg_spend_after' => (float) $r['avg_spend_after'] );
		}

		
		$ci = $wpdb->prefix . 'aieo_customer_intelligence';
		$recovery_count = 0;
		$recovery_preview = array();
		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $ci ) ) === $ci ) {
			
			
			$recovery_count = (int) $wpdb->get_var( $wpdb->prepare(
				"SELECT COUNT(DISTINCT ci.eponymous_id) FROM `{$si}` si JOIN `{$ci}` ci
				   ON ci.email = LOWER(TRIM(si.email))
				 WHERE si.nps_class = 'detractor' AND ci.ltv_total > %f AND si.email <> ''",
				$min_ltv
			) );
			
			
			
			$recovery_preview = (array) $wpdb->get_results( $wpdb->prepare(
				"SELECT ci.eponymous_id, ROUND(ci.ltv_total) ltv, MAX(si.voc_score) voc_score
				   FROM `{$si}` si JOIN `{$ci}` ci ON ci.email = LOWER(TRIM(si.email))
				 WHERE si.nps_class = 'detractor' AND ci.ltv_total > %f AND si.email <> ''
				 GROUP BY ci.eponymous_id, ci.ltv_total ORDER BY ci.ltv_total DESC LIMIT 8",
				$min_ltv
			), ARRAY_A );
		}

		
		
		
		
		$feedback_count = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM `{$si}` WHERE nps_class = 'detractor' AND TRIM(COALESCE(open_feedback_f18,'')) <> ''"
		);

		return array(
			'available'   => true,
			'surveyed'    => $total,
			'nps'         => array(
				'promoters'  => $dist['promoter'],
				'passives'   => $dist['passive'],
				'detractors' => $dist['detractor'],
				'classified' => $classified,
				'nps_score'  => $nps_score, 
			),
			'voc_band'    => array(
				'high' => (int) ( $agg['high_band'] ?? 0 ),
				'mid'  => (int) ( $agg['mid_band'] ?? 0 ),
				'low'  => (int) ( $agg['low_band'] ?? 0 ),
			),
			'avg_voc_score'    => round( (float) ( $agg['avg_voc'] ?? 0 ), 1 ),
			'avg_satisfaction' => round( (float) ( $agg['avg_sat'] ?? 0 ), 2 ),
			'shipping_price_concern' => array(
				'flagged' => (int) ( $agg['ship_flag'] ?? 0 ),
				'asked'   => $ship_asked, 
				'pct'     => $ship_asked > 0 ? round( 100 * (int) ( $agg['ship_flag'] ?? 0 ) / $ship_asked, 1 ) : 0.0,
			),
			'repurchase'  => array(
				'promoter_repeat_pct'  => $repeat_map['promoter']['repeat_pct'] ?? 0.0,
				'detractor_repeat_pct' => $repeat_map['detractor']['repeat_pct'] ?? 0.0,
				'promoter_avg_spend_after'  => $repeat_map['promoter']['avg_spend_after'] ?? 0.0,
				'detractor_avg_spend_after' => $repeat_map['detractor']['avg_spend_after'] ?? 0.0,
			),
			'recovery_targets' => array(
				'min_ltv' => $min_ltv,
				'count'   => $recovery_count,
				'preview' => array_map( function ( $r ) {
					return array( 'customer' => 'Customer #' . (int) $r['eponymous_id'], 'ltv' => (float) $r['ltv'], 'voc_score' => (int) $r['voc_score'] );
				}, $recovery_preview ),
			),
			'detractor_feedback_count' => $feedback_count, 
			'currency'    => WorkflowAbilityHelpers::currency(),
			'note'        => __( 'NPS classes come from the 1–5 post-order rating (4–5 promoter, 1–2 detractor). Recovery targets = detractors whose CDP lifetime value exceeds the floor — a "survey_detractor" workflow with a goodwill offer can auto-engage them. Compare promoter vs detractor repeat-purchase % to see whether satisfaction is converting to revenue.', 'ai-eshop-optimizer' ),
		);
	}
}

class CheckoutFrictionAbility {
	const ABILITY_NAME = 'aieo/checkout-friction';
	const MAX_ROWS = 50000; 
	const MAX_STEPS = 25;   

	public static function register() {
		wp_register_ability( self::ABILITY_NAME, array(
			'label'               => __( 'Checkout friction (per-step timing)', 'ai-eshop-optimizer' ),
			'description'         => __( 'Report where customers slow down or stall in checkout: the SLOWEST checkout step (from the per-step durations captured on the order-received page), overall active / idle / total-time percentiles, and a mobile-vs-desktop split. Surfaces friction the timing dashboard measures but never broke down per step. Read-only. Requires the Rooster One plugin; needs captured checkout-timing data.', 'ai-eshop-optimizer' ),
			'category'            => \AIEO\Claude\Bootstrap::CATEGORY,
			'input_schema'        => array( 'default' => array(), 'type' => 'object', 'properties' => array(
				'device' => array( 'type' => 'string', 'enum' => array( 'all', 'mobile', 'desktop', 'tablet' ), 'description' => 'Restrict to a device type (default all).' ),
				'days'   => array( 'type' => 'integer', 'description' => 'Look-back window in days (default 90, max 730).' ),
			) ),
			'output_schema'       => array( 'type' => 'object' ),
			'execute_callback'    => array( __CLASS__, 'execute' ),
			'permission_callback' => array( 'AIEO\\Claude\\Abilities\\WorkflowAbilityHelpers', 'can' ),
			'meta'                => array( 'annotations' => array( 'readonly' => true, 'idempotent' => true ), 'show_in_rest' => true ),
		) );
	}

	public static function execute( $input = null ) {
		global $wpdb;
		$t = $wpdb->prefix . 'aieo_checkout_timing';
		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) !== $t ) {
			return array( 'available' => false, 'note' => __( 'No checkout-timing table yet.', 'ai-eshop-optimizer' ) );
		}
		$device = isset( $input['device'] ) ? sanitize_key( (string) $input['device'] ) : 'all';
		$days   = isset( $input['days'] ) ? max( 1, min( 730, (int) $input['days'] ) ) : 90;
		
		
		$conds  = array( $wpdb->prepare( 'completed_at >= %s', gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ) ) );
		if ( in_array( $device, array( 'mobile', 'desktop', 'tablet' ), true ) ) {
			$conds[] = $wpdb->prepare( 'device_type = %s', $device );
		}
		$where = ' WHERE ' . implode( ' AND ', $conds );
		
		$rows = (array) $wpdb->get_results( "SELECT total_seconds, active_seconds, idle_seconds, step_durations, device_type FROM `{$t}`{$where} ORDER BY completed_at DESC LIMIT " . self::MAX_ROWS, ARRAY_A );
		if ( empty( $rows ) ) {
			return array( 'available' => true, 'rows' => 0, 'window_days' => $days, 'note' => __( 'No checkout-timing rows in this window/filter — the tracker records one row per completed order on the order-received page.', 'ai-eshop-optimizer' ) );
		}

		$tot = array();
		$act = array();
		$idle = array();
		$per_step = array();      
		$per_device = array();    
		foreach ( $rows as $r ) {
			$tot[]  = (int) $r['total_seconds'];
			$act[]  = (int) $r['active_seconds'];
			$idle[] = (int) $r['idle_seconds'];
			$dev = (string) ( $r['device_type'] ?: 'unknown' );
			$per_device[ $dev ][] = (int) $r['total_seconds'];
			
			$steps = json_decode( (string) $r['step_durations'], true );
			if ( is_array( $steps ) ) {
				foreach ( $steps as $name => $secs ) {
					if ( is_numeric( $secs ) ) {
						$per_step[ (string) $name ][] = (float) $secs;
					}
				}
			}
		}

		
		$steps_out = array();
		foreach ( $per_step as $name => $list ) {
			$steps_out[] = array(
				'step'          => sanitize_text_field( $name ),
				'median_seconds' => self::median( $list ),
				'n'             => count( $list ),
			);
		}
		usort( $steps_out, function ( $a, $b ) {
			return $b['median_seconds'] <=> $a['median_seconds'];
		} );
		
		$steps_out = array_slice( $steps_out, 0, self::MAX_STEPS );

		$device_out = array();
		foreach ( $per_device as $dev => $list ) {
			$device_out[] = array( 'device' => $dev, 'median_total_seconds' => self::median( $list ), 'n' => count( $list ) );
		}
		usort( $device_out, function ( $a, $b ) {
			return $b['n'] <=> $a['n'];
		} );

		return array(
			'available' => true,
			'rows'      => count( $rows ),
			'window_days' => $days,
			'device'    => $device,
			'overall'   => array(
				'median_total_seconds'  => self::median( $tot ),
				'median_active_seconds' => self::median( $act ),
				'median_idle_seconds'   => self::median( $idle ),
				'p75_total_seconds'     => self::percentile( $tot, 75 ),
			),
			'slowest_step' => ! empty( $steps_out ) ? $steps_out[0] : null,
			'steps'        => $steps_out,
			'by_device'    => $device_out,
			'note'         => __( 'Times are per completed order captured on the order-received page. "Idle" is elapsed minus active (tab-blurred / distracted) time. A step with a high median relative to the others is where customers hesitate — a candidate for UX simplification or a reassurance nudge. step_durations was collected but never surfaced before this.', 'ai-eshop-optimizer' ),
		);
	}

	private static function median( $a ) {
		return self::percentile( $a, 50 );
	}

	private static function percentile( $a, $p ) {
		$a = array_values( array_filter( $a, 'is_numeric' ) );
		if ( empty( $a ) ) {
			return 0;
		}
		sort( $a );
		$idx = (int) floor( ( $p / 100 ) * ( count( $a ) - 1 ) );
		$v = $a[ $idx ];
		return ( $v == (int) $v ) ? (int) $v : round( (float) $v, 1 );
	}
}

class GoalValueAbility {
	const ABILITY_NAME = 'aieo/goal-value';
	const MAX_ROWS = 100000; 

	public static function register() {
		wp_register_ability( self::ABILITY_NAME, array(
			'label'               => __( 'Conversion-goal economic value', 'ai-eshop-optimizer' ),
			'description'         => __( 'Value non-purchase conversion goals (leads, newsletter signups, course enrolments, form submissions) at their configured monetary worth over a window, so lead-generation campaigns are not scored as producing zero revenue. Returns per-goal completions × unit value, the grand total goal value, how many completions are unvalued (need a value set), and — optionally — the total goal value split by campaign (utm_campaign). Read-only. Requires the Rooster One plugin.', 'ai-eshop-optimizer' ),
			'category'            => \AIEO\Claude\Bootstrap::CATEGORY,
			'input_schema'        => array( 'default' => array(), 'type' => 'object', 'properties' => array(
				'days'        => array( 'type' => 'integer', 'description' => 'Look-back window in days (default 90, max 730).' ),
				'by_campaign' => array( 'type' => 'boolean', 'description' => 'Also split total goal value by campaign (default false).' ),
			) ),
			'output_schema'       => array( 'type' => 'object' ),
			'execute_callback'    => array( __CLASS__, 'execute' ),
			'permission_callback' => array( 'AIEO\\Claude\\Abilities\\WorkflowAbilityHelpers', 'can' ),
			'meta'                => array( 'annotations' => array( 'readonly' => true, 'idempotent' => true ), 'show_in_rest' => true ),
		) );
	}

	public static function execute( $input = null ) {
		global $wpdb;
		if ( ! class_exists( 'AIEO_Goal_Tracker' ) ) {
			$f = defined( 'AIEO_DIR' ) ? AIEO_DIR . 'includes/class-aieo-goal-tracker.php' : '';
			if ( $f && is_readable( $f ) ) { require_once $f; }
		}
		if ( ! class_exists( 'AIEO_Goal_Tracker' ) ) {
			return new \WP_Error( 'aieo_unavailable', __( 'The goal tracker is not loaded.', 'ai-eshop-optimizer' ) );
		}
		$t = $wpdb->prefix . 'aieo_sessions';
		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) !== $t ) {
			return array( 'available' => false, 'note' => __( 'No session/goal event stream yet.', 'ai-eshop-optimizer' ) );
		}
		$days        = isset( $input['days'] ) ? max( 1, min( 730, (int) $input['days'] ) ) : 90;
		$by_campaign = ! empty( $input['by_campaign'] );
		$since_ms    = ( (int) round( microtime( true ) * 1000 ) ) - ( $days * 86400000 );

		
		
		$rows = (array) $wpdb->get_results( $wpdb->prepare(
			"SELECT event_data FROM `{$t}` WHERE event_type = 'goal' AND timestamp >= %d LIMIT %d",
			$since_ms, self::MAX_ROWS
		), ARRAY_A );
		$capped = ( count( $rows ) >= self::MAX_ROWS );

		$goals    = array(); 
		$campaign = array(); 
		$total_value = 0.0;
		$total_completions = 0;
		$unvalued = 0;
		foreach ( $rows as $r ) {
			$d = json_decode( (string) $r['event_data'], true );
			if ( ! is_array( $d ) ) {
				continue;
			}
			$gk   = (string) ( $d['goal_key'] ?? '' );
			if ( '' === $gk ) {
				continue;
			}
			$form = isset( $d['form'] ) ? (int) $d['form'] : 0;
			$val  = (float) \AIEO_Goal_Tracker::goal_value( $gk, $d );
			$bucket = ( 'form_submit_gravity' === $gk && $form > 0 ) ? ( $gk . ':' . $form ) : $gk;
			if ( ! isset( $goals[ $bucket ] ) ) {
				$goals[ $bucket ] = array(
					'goal'       => $gk,
					'form_id'    => $form,
					'label'      => self::goal_label( $gk, $form ),
					'count'      => 0,
					'unit_value' => $val,
					'total_value' => 0.0,
				);
			}
			$goals[ $bucket ]['count']++;
			$goals[ $bucket ]['total_value'] += $val;
			$total_value += $val;
			$total_completions++;
			if ( $val <= 0 ) {
				$unvalued++;
			}
			if ( $by_campaign ) {
				$c = isset( $d['utm_campaign'] ) && '' !== $d['utm_campaign'] ? (string) $d['utm_campaign'] : '(none)';
				$campaign[ $c ] = ( $campaign[ $c ] ?? 0.0 ) + $val;
			}
		}
		$goals_out = array_values( $goals );
		usort( $goals_out, function ( $a, $b ) {
			return $b['total_value'] <=> $a['total_value'];
		} );
		foreach ( $goals_out as &$g ) {
			$g['total_value'] = round( $g['total_value'], 2 );
			$g['unit_value']  = round( $g['unit_value'], 2 );
		}
		unset( $g );
		$campaign_out = array();
		if ( $by_campaign ) {
			arsort( $campaign );
			foreach ( $campaign as $c => $v ) {
				$campaign_out[] = array( 'campaign' => $c, 'goal_value' => round( $v, 2 ) );
			}
		}

		return array(
			'available'         => true,
			'window_days'       => $days,
			'total_completions' => $total_completions,
			'total_goal_value'  => round( $total_value, 2 ),
			'unvalued_completions' => $unvalued, 
			'goals'             => $goals_out,
			'by_campaign'       => $by_campaign ? $campaign_out : null,
			'capped'            => $capped,
			'currency'          => WorkflowAbilityHelpers::currency(),
			'note'              => __( 'Goal value = completions × the monetary worth configured per goal on the Conversion Goals tab (0 until set). Use it to credit lead-gen / signup campaigns that produce no direct order revenue, and combine with order revenue for a fuller ROI picture. "unvalued_completions" are goals firing with no configured value.', 'ai-eshop-optimizer' ),
		);
	}

	
	private static function goal_label( $goal_key, $form_id ) {
		return \AIEO_Goal_Tracker::goal_label( $goal_key, (int) $form_id );
	}
}