<?php

namespace AIEO\Claude\Abilities;

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

class ProductRequestsAbility {

	const ABILITY_NAME = 'aieo/quote-requests';

	public static function register() {

		wp_register_ability(
			self::ABILITY_NAME,
			array(
				'label'               => __( 'Quote requests: demand a price list would have hidden', 'ai-eshop-optimizer' ),
				'description'         => __( 'READ-ONLY, no AI credits. Lists the products visitors ASKED to be quoted, newest first, with how many people asked, how many are still unanswered, and which campaign sent them. This is volunteered demand: a named person typed their email to ask about a product the shop does not sell at a listed price, which is the only conversion a catalogue site can produce and never appears in any order report. Two counts are returned per product and MUST NOT be added together: "requested" is stated demand (they asked to be quoted) and "viewed" is browsing context (they looked at it while a quote was open). Params: days (default 30), limit (default 25), status (all|open|handled). Returns nothing when the product-requests feature is not in use. It never emails anyone, never changes a request and never exposes a quote token.', 'ai-eshop-optimizer' ),
				'category'            => \AIEO\Claude\Bootstrap::CATEGORY,
				'input_schema'        => array(
					'default'    => array(),
					'type'       => 'object',
					'properties' => array(
						'days'   => array(
							'type'        => 'integer',
							'default'     => 30,
							'minimum'     => 1,
							'maximum'     => 730,
							'description' => 'How far back to look, in days. Defaults to 30.',
						),
						'limit'  => array(
							'type'        => 'integer',
							'default'     => 25,
							'minimum'     => 1,
							'maximum'     => 100,
							'description' => 'How many products to return, most-requested first. Defaults to 25.',
						),
						'status' => array(
							'type'        => 'string',
							'enum'        => array( 'all', 'open', 'handled' ),
							'default'     => 'all',
							'description' => '"open" is requests nobody has answered yet (status new or in_progress). Defaults to all.',
						),
					),
				),
				'output_schema'       => array( 'type' => 'object' ),
				'execute_callback'    => array( __CLASS__, 'execute' ),
				'permission_callback' => array( __CLASS__, 'can' ),
				'meta'                => array(
					'annotations'  => array(
						'readonly'   => true,
						'idempotent' => true,
					),
					'show_in_rest' => true,
				),
			)
		);
	}

	
	public static function can() {
		if ( class_exists( 'AIEO\\Claude\\Abilities\\BoostSlateAbilityHelpers' )
			&& method_exists( 'AIEO\\Claude\\Abilities\\BoostSlateAbilityHelpers', 'can' ) ) {
			return call_user_func( array( 'AIEO\\Claude\\Abilities\\BoostSlateAbilityHelpers', 'can' ) );
		}
		return current_user_can( 'manage_woocommerce' );
	}

	
	public static function execute( $input = null ) {
		global $wpdb;

		$input  = is_array( $input ) ? $input : array();
		$days   = max( 1, min( 730, isset( $input['days'] ) ? (int) $input['days'] : 30 ) );
		$limit  = max( 1, min( 100, isset( $input['limit'] ) ? (int) $input['limit'] : 25 ) );
		$status = isset( $input['status'] ) ? sanitize_key( (string) $input['status'] ) : 'all';

		$req   = $wpdb->prefix . 'aieo_product_requests';
		$items = $wpdb->prefix . 'aieo_product_request_items';
		$views = $wpdb->prefix . 'aieo_product_request_views';

		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $req ) ) !== $req ) {
			return array(
				'returned' => 0,
				'products' => array(),
				'note'     => __( 'The product-requests feature is not in use on this site, so nobody has asked for a quote.', 'ai-eshop-optimizer' ),
			);
		}

		$since = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );

		$status_sql = '';
		if ( 'open' === $status )    { $status_sql = " AND r.status IN ('new','in_progress')"; }
		if ( 'handled' === $status ) { $status_sql = " AND r.status IN ('quoted','closed')"; }

		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT i.product_id,
			        COUNT(DISTINCT r.id)    AS requests,
			        COUNT(DISTINCT r.email) AS people,
			        SUM(i.qty)              AS units_asked,
			        SUM(r.status = 'new')   AS unanswered,
			        MAX(r.created_at)       AS last_asked
			   FROM `{$items}` i
			   INNER JOIN `{$req}` r ON r.id = i.request_id
			  WHERE r.created_at >= %s AND r.status <> 'spam' {$status_sql}
			  GROUP BY i.product_id
			  ORDER BY requests DESC, people DESC
			  LIMIT %d",
			$since,
			$limit
		), ARRAY_A );

		if ( ! $rows ) {
			return array(
				'returned' => 0,
				'products' => array(),
				'note'     => sprintf(
					
					__( 'Nobody has asked for a quote in the last %d days.', 'ai-eshop-optimizer' ),
					$days
				),
			);
		}

		
		
		$viewed = array();
		if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $views ) ) === $views ) {
			foreach ( (array) $wpdb->get_results( $wpdb->prepare(
				"SELECT v.product_id, COUNT(*) AS n
				   FROM `{$views}` v
				   INNER JOIN `{$req}` r ON r.id = v.request_id
				  WHERE r.created_at >= %s
				  GROUP BY v.product_id",
				$since
			), ARRAY_A ) as $v ) {
				$viewed[ (int) $v['product_id'] ] = (int) $v['n'];
			}
		}

		$out = array();
		foreach ( $rows as $r ) {
			$pid  = (int) $r['product_id'];
			$prod = function_exists( 'wc_get_product' ) ? wc_get_product( $pid ) : null;
			$out[] = array(
				'product_id'  => $pid,
				'product'     => $prod ? $prod->get_name() : ( '#' . $pid ),
				'sku'         => $prod && method_exists( $prod, 'get_sku' ) ? (string) $prod->get_sku() : '',
				'purchasable' => $prod ? (bool) $prod->is_purchasable() : null,
				
				'requested'   => (int) $r['requests'],
				'people'      => (int) $r['people'],
				'units_asked' => (int) $r['units_asked'],
				'unanswered'  => (int) $r['unanswered'],
				'last_asked'  => (string) $r['last_asked'],
				
				'viewed'      => isset( $viewed[ $pid ] ) ? (int) $viewed[ $pid ] : 0,
			);
		}

		
		
		
		$campaigns = array();
		if ( class_exists( 'AIEO_DMM_Conversions_Registry' ) ) {
			$conv = \AIEO_DMM_Conversions_Registry::table();
			if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $conv ) ) === $conv ) {
				foreach ( (array) $wpdb->get_results( $wpdb->prepare(
					"SELECT canon_campaign, COUNT(*) AS leads
					   FROM `{$conv}`
					  WHERE conversion_type = 'goal' AND goal_key = 'product_request' AND occurred_at >= %s
					  GROUP BY canon_campaign
					  ORDER BY leads DESC
					  LIMIT 15",
					$since
				), ARRAY_A ) as $c ) {
					$campaigns[] = array(
						'campaign' => '' !== (string) $c['canon_campaign']
							? (string) $c['canon_campaign']
							: __( '(no campaign: direct or untagged)', 'ai-eshop-optimizer' ),
						'leads'    => (int) $c['leads'],
					);
				}
			}
		}

		$totals = $wpdb->get_row( $wpdb->prepare(
			"SELECT COUNT(*) AS requests,
			        SUM(status = 'new') AS unanswered,
			        COUNT(DISTINCT email) AS people
			   FROM `{$req}` WHERE created_at >= %s AND status <> 'spam'",
			$since
		), ARRAY_A );

		return array(
			'window_days' => $days,
			'returned'    => count( $out ),
			'totals'      => array(
				'requests'   => (int) ( $totals['requests'] ?? 0 ),
				'people'     => (int) ( $totals['people'] ?? 0 ),
				'unanswered' => (int) ( $totals['unanswered'] ?? 0 ),
			),
			'products'    => $out,
			'campaigns'   => $campaigns,
			'note'        => __( '"requested" is stated demand: somebody asked for a price. "viewed" is browsing context recorded while their quote was open. Never add the two: they answer different questions, and a product with ten views and one request is a weaker signal than one with three views and three requests, not a stronger one.', 'ai-eshop-optimizer' ),
		);
	}
}