<?php

namespace AIEO\Claude\Abilities;

defined( 'ABSPATH' ) || exit;

class GetRatingLeaderboardAbility {

	const ABILITY_NAME = 'aieo/get-rating-leaderboard';

	public static function register() {
		wp_register_ability(
			self::ABILITY_NAME,
			array(
				'label'               => __( 'Get rating leaderboard', 'ai-eshop-optimizer' ),
				'description'         => __( 'Return the top-N highest-rated and bottom-N lowest-rated products (with vote counts) from `wp_aieo_rating_stats`. `min_votes` filters out single-vote noise.', '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(
				'n'         => array(
					'type'    => 'integer',
					'minimum' => 1,
					'maximum' => 50,
					'default' => 10,
				),
				'min_votes' => array(
					'type'    => 'integer',
					'minimum' => 1,
					'maximum' => 100,
					'default' => 3,
				),
			),
		);
	}

	public static function execute( $input ) {
		global $wpdb;
		$input     = is_array( $input ) ? $input : array();
		$n         = isset( $input['n'] ) ? max( 1, min( 50, (int) $input['n'] ) ) : 10;
		$min_votes = isset( $input['min_votes'] ) ? max( 1, (int) $input['min_votes'] ) : 3;
		$table     = $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 = %s",
			$table
		) );
		if ( ! $exists ) {
			return array( 'top' => array(), 'bottom' => array(), 'note' => __( 'wp_aieo_rating_stats not installed.', 'ai-eshop-optimizer' ) );
		}

		
		$top = $wpdb->get_results( $wpdb->prepare(
			"SELECT product_id, count_total AS votes,
			        ROUND(sum_total / GREATEST(count_total, 1), 2) AS avg_rating
			   FROM {$table}
			  WHERE object_type = 'product' AND count_total >= %d
			  ORDER BY (sum_total / GREATEST(count_total, 1)) DESC, count_total DESC
			  LIMIT %d",
			$min_votes, $n
		), ARRAY_A );

		
		$bottom = $wpdb->get_results( $wpdb->prepare(
			"SELECT product_id, count_total AS votes,
			        ROUND(sum_total / GREATEST(count_total, 1), 2) AS avg_rating
			   FROM {$table}
			  WHERE object_type = 'product' AND count_total >= %d
			  ORDER BY (sum_total / GREATEST(count_total, 1)) ASC, count_total DESC
			  LIMIT %d",
			$min_votes, $n
		), ARRAY_A );

		$enrich = static function ( $rows ) {
			$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 ),
					'votes'      => (int) $r['votes'],
					'avg_rating' => (float) $r['avg_rating'],
				);
			}
			return $out;
		};

		return array(
			'min_votes' => $min_votes,
			'n'         => $n,
			'top'       => $enrich( $top ),
			'bottom'    => $enrich( $bottom ),
		);
	}
}
