<?php

namespace AIEO\Claude\Abilities;

defined( 'ABSPATH' ) || exit;

class RatingCoverageByCategoryAbility {

	const ABILITY_NAME = 'aieo/rating-coverage-by-category';

	public static function register() {
		wp_register_ability(
			self::ABILITY_NAME,
			array(
				'label'               => __( 'Rating coverage by category', 'ai-eshop-optimizer' ),
				'description'         => __( 'For each top-level `product_cat`, return the number of products, the count with any votes, the average rating, and the vote density (votes / product). Use this to spot categories that need rating seeding.', 'ai-eshop-optimizer' ),
				'category'            => \AIEO\Claude\Bootstrap::CATEGORY,
				'input_schema'        => array(
					
					
					
					'type'                 => 'object',
					'additionalProperties' => false,
					'default'              => array(),
				),
				'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' );
	}

	public static function execute( $input = null ) {
		unset( $input );
		global $wpdb;
		$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 = %s",
			$stats
		) );
		if ( ! $exists ) {
			return array( 'rows' => array(), 'note' => __( 'wp_aieo_rating_stats not installed.', 'ai-eshop-optimizer' ) );
		}

		$top_cats = get_terms(
			array(
				'taxonomy'   => 'product_cat',
				'hide_empty' => false,
				'parent'     => 0,
			)
		);
		if ( is_wp_error( $top_cats ) || empty( $top_cats ) ) {
			return array( 'rows' => array() );
		}

		$rows = array();
		foreach ( (array) $top_cats as $cat ) {
			
			$agg = $wpdb->get_row( $wpdb->prepare(
				"SELECT COUNT(DISTINCT rs.product_id)                                                          AS rated_products,
				        SUM(rs.count_total)                                                                    AS total_votes,
				        ROUND(SUM(rs.sum_total) / NULLIF(SUM(rs.count_total), 0), 2)                           AS avg_rating
				   FROM {$stats} rs
				   INNER JOIN {$wpdb->term_relationships} tr ON tr.object_id = rs.product_id
				   INNER JOIN {$wpdb->term_taxonomy} tt      ON tt.term_taxonomy_id = tr.term_taxonomy_id
				  WHERE rs.object_type = 'product'
				    AND tt.taxonomy = 'product_cat'
				    AND tt.term_id  = %d",
				(int) $cat->term_id
			), ARRAY_A );

			$rated   = (int) ( $agg['rated_products'] ?? 0 );
			$total   = (int) ( $agg['total_votes'] ?? 0 );
			$avg     = $agg['avg_rating'] !== null ? (float) $agg['avg_rating'] : null;
			$products = (int) $cat->count;

			$rows[] = array(
				'category_id'    => (int) $cat->term_id,
				'name'           => (string) $cat->name,
				'total_products' => $products,
				'rated_products' => $rated,
				'coverage_pct'   => $products > 0 ? round( 100.0 * $rated / $products, 1 ) : 0.0,
				'total_votes'    => $total,
				'avg_rating'     => $avg,
				'votes_per_product' => $rated > 0 ? round( $total / $rated, 2 ) : 0.0,
			);
		}

		usort( $rows, static function ( $a, $b ) {
			return $b['total_votes'] <=> $a['total_votes'];
		} );

		return array( 'rows' => $rows );
	}
}