<?php

namespace SugarCalendarBookings\Abilities\Customers;

use SugarCalendarBookings\Abilities\AbstractAbility;
use SugarCalendarBookings\Appointments\AppointmentModel;
use SugarCalendarBookings\Customers\CustomerModel;
use WP_Error;

/**
 * Ability: list customers with name/email search.
 *
 * @since 1.5.0
 */
class ListCustomersAbility extends AbstractAbility {

	/**
	 * Ability slug, without the namespace prefix.
	 *
	 * @since 1.5.0
	 *
	 * @return string
	 */
	public function get_name(): string {

		return 'list-customers';
	}

	/**
	 * Human-readable label.
	 *
	 * @since 1.5.0
	 *
	 * @return string
	 */
	public function get_label(): string {

		return esc_html__( 'List Customers', 'sugar-calendar-bookings-scheduling-appointments-lite' );
	}

	/**
	 * Human-readable description.
	 *
	 * @since 1.5.0
	 *
	 * @return string
	 */
	public function get_description(): string {

		return esc_html__( 'List customers with name/email search and pagination.', 'sugar-calendar-bookings-scheduling-appointments-lite' );
	}

	/**
	 * Input schema.
	 *
	 * @since 1.5.0
	 *
	 * @return array
	 */
	public function get_input_schema(): array {

		return [
			'type'       => 'object',
			'properties' => array_merge(
				self::SCHEMA_PAGINATION,
				[
					'search' => [
						'type'      => 'string',
						'maxLength' => 100,
					],
				]
			),
		];
	}

	/**
	 * Output schema.
	 *
	 * @since 1.5.0
	 *
	 * @return array
	 */
	public function get_output_schema(): array {

		return [
			'type'       => 'object',
			'properties' => [
				'customers' => [ 'type' => 'array' ],
				'total'     => [ 'type' => 'integer' ],
			],
		];
	}

	/**
	 * Query customers and return summary bodies enriched with aggregate stats.
	 *
	 * @since 1.5.0
	 *
	 * @param mixed $input Input data.
	 *
	 * @return array|WP_Error
	 */
	public function execute( $input ) {

		$args       = $this->normalize_input( $input );
		$pagination = $this->get_pagination( $args );
		$limit      = $pagination['limit'];
		$offset     = $pagination['offset'];
		$search     = isset( $args['search'] ) ? trim( (string) $args['search'] ) : '';

		$query = CustomerModel::query()->with(
			[
				'tags' => static fn( $q ) => $q->select( [ 'id', 'name' ] ),
				'meta' => static fn( $q ) => $q->where( 'meta_key', 'IN', [ 'notes' ], '%s' ),
			]
		);

		$count_query = CustomerModel::query();

		$this->apply_search( $query, $search );
		$this->apply_search( $count_query, $search );

		$total = (int) $count_query->count();

		$query->order_by( 'customers.id', 'DESC' )
			->offset( $offset )
			->limit( $limit );

		$rows = $query->get();

		$customer_ids = array_map(
			static function ( CustomerModel $c ): int {

				return (int) $c->id;
			},
			$rows
		);
		$aggregates   = $this->load_aggregates( $customer_ids );
		$formatter    = $this->formatter();

		$out = [];

		foreach ( $rows as $customer ) {
			$out[] = $formatter->customer( $customer, $aggregates[ $customer->id ] ?? [] );
		}

		return [
			'customers' => $out,
			'total'     => $total,
		];
	}

	/**
	 * Apply name+email search to a query builder.
	 *
	 * @since 1.5.0
	 *
	 * @param mixed  $query  Query builder.
	 * @param string $search Search term.
	 */
	private function apply_search( $query, string $search ): void {

		if ( $search === '' ) {
			return;
		}

		$like = $this->build_like_pattern( $search );

		$query->where(
			static function ( $q ) use ( $like ) {
				$q->where( 'customers.name', 'LIKE', $like, '%s' )
					->or_where( 'customers.email', 'LIKE', $like, '%s' );
			}
		);
	}

	/**
	 * Load per-customer aggregates in one grouped query (no N+1).
	 *
	 * @since 1.5.0
	 *
	 * @param int[] $customer_ids
	 *
	 * @return array<int, array{appointments_count: int, last_appointment_at: ?string, total_spent_amount: float, total_spent_currency: string}>
	 */
	private function load_aggregates( array $customer_ids ): array {

		if ( empty( $customer_ids ) ) {
			return [];
		}

		global $wpdb;

		$customers     = $wpdb->prefix . 'scbookings_customers';
		$appointments  = $wpdb->prefix . 'scbookings_appointments';
		$payment_items = $wpdb->prefix . 'scbookings_payment_items';
		$payments      = $wpdb->prefix . 'scbookings_payments';

		$placeholders = implode( ',', array_fill( 0, count( $customer_ids ), '%d' ) );

		// Real booking history only — drafts are short-lived placeholders auto-cleaned by DraftAppointmentsCleanupTask,
		// and cancelled appointments must not inflate appointments_count or skew last_appointment_at.
		$exclude_appointment_statuses = [
			AppointmentModel::STATUS_DRAFT,
			AppointmentModel::STATUS_CANCELLED,
		];

		$status_placeholders = implode( ',', array_fill( 0, count( $exclude_appointment_statuses ), '%s' ) );

		// MIN/MAX(currency) bracket lets us detect multi-currency customers in
		// the same row — when MIN != MAX, summing total_amount mixes currencies
		// into a meaningless figure, so we surface currency = "MIXED" with a
		// null amount (matching AbstractAbility::aggregate_revenue() convention).
		$sql = "
			SELECT
				customers.id AS customer_id,
				COUNT(DISTINCT appointments.id) AS appointments_count,
				MAX(appointments.start_at) AS last_appointment_at,
				COALESCE(SUM(CASE WHEN payments.status = 'completed' THEN payments.total_amount ELSE 0 END), 0) AS total_spent,
				MIN(CASE WHEN payments.status = 'completed' THEN payments.currency END) AS currency_min,
				MAX(CASE WHEN payments.status = 'completed' THEN payments.currency END) AS currency_max
			FROM `{$customers}` AS customers
			LEFT JOIN `{$appointments}` AS appointments
				ON appointments.customer_id = customers.id
				AND appointments.status NOT IN ({$status_placeholders})
			LEFT JOIN `{$payment_items}` AS pi ON pi.item_id = appointments.id AND pi.item_type = 'appointment'
			LEFT JOIN `{$payments}` AS payments ON payments.id = pi.payment_id
			WHERE customers.id IN ({$placeholders})
			GROUP BY customers.id
		";

		$bindings = array_merge( $exclude_appointment_statuses, $customer_ids );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $bindings ), ARRAY_A );

		$out = [];

		foreach ( (array) $rows as $row ) {

			$cid          = (int) ( $row['customer_id'] ?? 0 );
			$currency_min = (string) ( $row['currency_min'] ?? '' );
			$currency_max = (string) ( $row['currency_max'] ?? '' );
			$is_mixed     = ( $currency_min !== '' ) && ( $currency_min !== $currency_max );

			$out[ $cid ] = [
				'appointments_count'   => (int) ( $row['appointments_count'] ?? 0 ),
				'last_appointment_at'  => $row['last_appointment_at'] ?: null,
				'total_spent_amount'   => $is_mixed ? null : (float) ( $row['total_spent'] ?? 0.0 ),
				'total_spent_currency' => $is_mixed ? 'MIXED' : $currency_max,
			];
		}

		return $out;
	}
}
