<?php

namespace AIEO\Claude\Abilities;

defined( 'ABSPATH' ) || exit;

class FindProductsNeedingRatingsAbility {

	const ABILITY_NAME = 'aieo/find-products-needing-ratings';

	public static function register() {
		wp_register_ability(
			self::ABILITY_NAME,
			array(
				'label'               => __( 'Find products needing ratings', 'ai-eshop-optimizer' ),
				'description'         => __( 'Return products with many cart-adds in the period but zero or near-zero ratings — the "high commercial signal, no social proof" shortlist for rating-seeding campaigns.', '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(
			'type'       => 'object',
			'properties' => array(
				'period'           => array(
					'type'    => 'string',
					'default' => 'last_30_days',
					'enum'    => PeriodHelper::ALLOWED,
				),
				'min_cart_adds'    => array(
					'type'    => 'integer',
					'minimum' => 1,
					'maximum' => 1000,
					'default' => 5,
				),
				'max_votes'        => array(
					'type'        => 'integer',
					'minimum'     => 0,
					'maximum'     => 10,
					'default'     => 1,
					'description' => 'Maximum existing votes for a product to count as "needs ratings".',
				),
				'limit'            => array(
					'type'    => 'integer',
					'minimum' => 1,
					'maximum' => 100,
					'default' => 25,
				),
			),
		);
	}

	public static function execute( $input ) {
		global $wpdb;
		$input         = is_array( $input ) ? $input : array();
		$period        = PeriodHelper::resolve( $input['period'] ?? 'last_30_days' );
		$min_cart_adds = isset( $input['min_cart_adds'] ) ? max( 1, (int) $input['min_cart_adds'] ) : 5;
		$max_votes     = isset( $input['max_votes'] ) ? max( 0, (int) $input['max_votes'] ) : 1;
		$limit         = isset( $input['limit'] ) ? max( 1, min( 100, (int) $input['limit'] ) ) : 25;

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

		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT a.product_id,
			        a.cart_adds,
			        COALESCE(rs.count_total, 0) AS votes,
			        ROUND(COALESCE(rs.sum_total, 0) / GREATEST(COALESCE(rs.count_total, 0), 1), 2) AS avg_rating
			   FROM (
			       SELECT product_id, COUNT(*) AS cart_adds
			         FROM {$addcart}
			        WHERE ts_added BETWEEN %s AND %s
			        GROUP BY product_id
			       HAVING cart_adds >= %d
			   ) a
			   LEFT JOIN {$stats} rs
			          ON rs.product_id = a.product_id AND rs.object_type = 'product'
			  WHERE COALESCE(rs.count_total, 0) <= %d
			  ORDER BY a.cart_adds DESC
			  LIMIT %d",
			$period['since'], $period['until'], $min_cart_adds, $max_votes, $limit
		), ARRAY_A );

		$out = array();
		foreach ( (array) $rows as $r ) {
			$pid = (int) $r['product_id'];
			$out[] = array(
				'product_id' => $pid,
				'title'      => get_the_title( $pid ),
				'permalink'  => get_permalink( $pid ),
				'cart_adds'  => (int) $r['cart_adds'],
				'votes'      => (int) $r['votes'],
				'avg_rating' => (float) $r['avg_rating'],
			);
		}

		return array(
			'period'        => $period,
			'min_cart_adds' => $min_cart_adds,
			'max_votes'     => $max_votes,
			'rows'          => $out,
		);
	}
}
