<?php

namespace AIEO\Claude\Abilities;

defined( 'ABSPATH' ) || exit;

class TopConvertingPairsAbility {

	const ABILITY_NAME = 'aieo/top-converting-pairs';

	public static function register() {
		wp_register_ability(
			self::ABILITY_NAME,
			array(
				'label'               => __( 'Top converting reco pairs', 'ai-eshop-optimizer' ),
				'description'         => __( "Return the (anchor product, recommended product) pairs that convert best on a given surface. Filters by `min_impressions` so we don't surface noisy single-event 100% rates.", 'ai-eshop-optimizer' ),
				'category'            => \AIEO\Claude\Bootstrap::CATEGORY,
				'input_schema'        => self::input_schema(),
				'output_schema'       => array( 'type' => 'object' ),
				'execute_callback'    => array( __CLASS__, 'execute' ),
				'permission_callback' => array( __CLASS__, 'permission_check' ),
				'meta'                => array(
					'annotations'  => array(
						'readonly'   => true,
						'idempotent' => true,
					),
					'show_in_rest' => true,
				),
			)
		);
	}

	public static function permission_check( $input = null ) {
		unset( $input );
		return current_user_can( 'manage_woocommerce' );
	}

	private static function input_schema() {
		return array(
			'default'    => array(),
			'type'       => 'object',
			'required'   => array( 'surface' ),
			'properties' => array(
				'surface'         => array(
					'type'        => 'string',
					'description' => 'Surface key (e.g. product_needs, product_upsells, product_related, sidecart_may_also_like).',
				),
				'min_impressions' => array(
					'type'        => 'integer',
					'minimum'     => 1,
					'maximum'     => 1000,
					'default'     => 10,
					'description' => 'Minimum number of impressions for a pair to be included.',
				),
				'period'          => array(
					'type'        => 'string',
					'default'     => 'last_30_days',
					'enum'        => PeriodHelper::ALLOWED,
				),
				'limit'           => array(
					'type'        => 'integer',
					'minimum'     => 1,
					'maximum'     => 100,
					'default'     => 25,
				),
			),
		);
	}

	public static function execute( $input ) {
		global $wpdb;
		$input   = is_array( $input ) ? $input : array();
		$surface = isset( $input['surface'] ) ? sanitize_key( (string) $input['surface'] ) : '';
		if ( $surface === '' ) {
			return new \WP_Error( 'aieo_invalid_input', __( 'surface is required.', 'ai-eshop-optimizer' ) );
		}
		$min_imp = isset( $input['min_impressions'] ) ? max( 1, (int) $input['min_impressions'] ) : 10;
		$limit   = isset( $input['limit'] ) ? max( 1, min( 100, (int) $input['limit'] ) ) : 25;
		$period  = PeriodHelper::resolve( $input['period'] ?? 'last_30_days' );

		$reco    = $wpdb->prefix . 'aieo_reco_daily';   
		$addcart = $wpdb->prefix . 'aieo_addcart_events';

		$exists = (int) $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN (%s, %s)",
			$reco, $addcart
		) );
		if ( $exists < 2 ) {
			return array( 'period' => $period, 'surface' => $surface, 'rows' => array(), 'note' => __( 'Attribution tables missing.', 'ai-eshop-optimizer' ) );
		}

		
		
		
		
		
		$sql = $wpdb->prepare(
			"SELECT r.anchor_pid,
			        r.target_pid,
			        r.impressions,
			        COALESCE(a.cart_adds, 0) AS cart_adds
			   FROM (
			       SELECT anchor_pid, target_pid, SUM(impressions) AS impressions
			         FROM {$reco}
			        WHERE date BETWEEN DATE(%s) AND DATE(%s)
			          AND surface = %s
			        GROUP BY anchor_pid, target_pid
			       HAVING SUM(impressions) >= %d
			   ) r
			   LEFT JOIN (
			       SELECT source_pid AS anchor_pid, product_id AS target_pid, COUNT(*) AS cart_adds
			         FROM {$addcart}
			        WHERE ts_added BETWEEN %s AND %s
			          AND source = %s
			        GROUP BY source_pid, product_id
			   ) a
			     ON a.anchor_pid = r.anchor_pid AND a.target_pid = r.target_pid
			  ORDER BY (COALESCE(a.cart_adds, 0) / GREATEST(r.impressions, 1)) DESC, r.impressions DESC
			  LIMIT %d",
			$period['since'], $period['until'], $surface, $min_imp,
			$period['since'], $period['until'], $surface,
			$limit
		);
		$rows = $wpdb->get_results( $sql, ARRAY_A );

		$out = array();
		foreach ( (array) $rows as $r ) {
			$imp = (int) $r['impressions'];
			$cad = (int) $r['cart_adds'];
			$out[] = array(
				'anchor_pid'          => (int) $r['anchor_pid'],
				'anchor_title'        => get_the_title( (int) $r['anchor_pid'] ),
				'target_pid'          => (int) $r['target_pid'],
				'target_title'        => get_the_title( (int) $r['target_pid'] ),
				'impressions'         => $imp,
				'cart_adds'           => $cad,
				'cart_add_rate'       => $imp > 0 ? round( $cad / $imp, 4 ) : 0.0,
			);
		}

		return array(
			'period'          => $period,
			'surface'         => $surface,
			'min_impressions' => $min_imp,
			'rows'            => $out,
		);
	}
}