<?php

namespace SugarCalendarBookings\Abilities;

use DateTimeImmutable;
use Exception;
use SugarCalendarBookings\Plugin;
use SugarCalendarBookings\Support\Helpers\Date;
use WP_Error;

/**
 * Default implementation that every concrete ability extends.
 *
 * Provides:
 * - manage_options permission check
 * - read-only annotation defaults
 * - REST + MCP exposure defaults
 * - Reusable input-schema fragments (pagination, id)
 * - Shared SQL helpers for stats abilities
 *
 * Response formatting is delegated to {@see AbilityResponseFormatter},
 * resolved from the container so Pro can rebind it.
 *
 * @since 1.5.0
 */
abstract class AbstractAbility implements AbilityInterface {

	/**
	 * Reusable pagination input-schema properties.
	 *
	 * @since 1.5.0
	 */
	protected const SCHEMA_PAGINATION = [
		'limit'  => [
			'type'    => 'integer',
			'minimum' => 1,
			'maximum' => 100,
			'default' => 20,
		],
		'offset' => [
			'type'    => 'integer',
			'minimum' => 0,
			'default' => 0,
		],
	];

	/**
	 * Reusable single-id input-schema property.
	 *
	 * @since 1.5.0
	 */
	protected const SCHEMA_ID = [
		'id' => [
			'type'    => 'integer',
			'minimum' => 1,
		],
	];

	/**
	 * Reusable date-range input-schema fragment.
	 *
	 * @since 1.5.0
	 */
	protected const SCHEMA_DATE_RANGE = [
		'date_from' => [
			'type'   => 'string',
			'format' => 'date',
		],
		'date_to'   => [
			'type'   => 'string',
			'format' => 'date',
		],
	];

	/**
	 * Read-only annotations applied to every v1 ability.
	 *
	 * @since 1.5.0
	 *
	 * @return array
	 */
	public function get_annotations(): array {

		return [
			'readonly'    => true,
			'destructive' => false,
			'idempotent'  => true,
		];
	}

	/**
	 * Expose abilities via the REST API by default.
	 *
	 * @since 1.5.0
	 *
	 * @return bool
	 */
	public function show_in_rest(): bool {

		return true;
	}

	/**
	 * Mark abilities as MCP-public by default.
	 *
	 * @since 1.5.0
	 *
	 * @return bool
	 */
	public function is_mcp_public(): bool {

		return true;
	}

	/**
	 * Restrict abilities to administrators.
	 *
	 * @since 1.5.0
	 *
	 * @return true|WP_Error
	 */
	public function check_permission() {

		if ( current_user_can( 'manage_options' ) ) {
			return true;
		}

		return new WP_Error(
			'sugar_calendar_bookings_forbidden',
			esc_html__( 'You do not have permission to access this resource.', 'sugar-calendar-bookings-scheduling-appointments-lite' ),
			[ 'status' => 403 ]
		);
	}

	/**
	 * Coerce input into a plain array.
	 *
	 * @since 1.5.0
	 *
	 * @param mixed $input Raw input passed to execute().
	 *
	 * @return array
	 */
	protected function normalize_input( $input ): array {

		if ( is_array( $input ) ) {
			return $input;
		}

		if ( is_object( $input ) ) {
			return get_object_vars( $input );
		}

		return [];
	}

	/**
	 * Build a standard "not found" error.
	 *
	 * Callers are expected to pass an already-escaped translated string
	 * (via `esc_html__()`), so this helper does not re-escape — doing so
	 * would double-encode apostrophes, ampersands, and quotes once a
	 * translator supplies a localized variant.
	 *
	 * @since 1.5.0
	 *
	 * @param string $message Human-readable, already-escaped message.
	 *
	 * @return WP_Error
	 */
	protected function not_found_error( string $message ): WP_Error {

		return new WP_Error(
			'sugar_calendar_bookings_not_found',
			$message,
			[ 'status' => 404 ]
		);
	}

	/**
	 * Resolve `limit` and `offset` from input, clamped to schema bounds.
	 *
	 * @since 1.5.0
	 *
	 * @param array $args Normalized input.
	 *
	 * @return array{limit: int, offset: int}
	 */
	protected function get_pagination( array $args ): array {

		$limit_default = (int) self::SCHEMA_PAGINATION['limit']['default'];
		$limit_max     = (int) self::SCHEMA_PAGINATION['limit']['maximum'];
		$limit_min     = (int) self::SCHEMA_PAGINATION['limit']['minimum'];

		$limit  = isset( $args['limit'] ) ? (int) $args['limit'] : $limit_default;
		$offset = isset( $args['offset'] ) ? (int) $args['offset'] : 0;

		return [
			'limit'  => max( $limit_min, min( $limit_max, $limit ) ),
			'offset' => max( 0, $offset ),
		];
	}

	/**
	 * Resolve the response formatter from the container.
	 *
	 * Container-resolved so Pro can rebind {@see AbilityResponseFormatter} in
	 * `Pro/Loader::provides()`.
	 *
	 * @since 1.5.0
	 *
	 * @return AbilityResponseFormatter
	 */
	protected function formatter(): AbilityResponseFormatter {

		return Plugin::get( AbilityResponseFormatter::class );
	}

	/**
	 * Build a wildcard-escaped LIKE pattern for use with `%s` placeholders.
	 *
	 * @since 1.5.0
	 *
	 * @param string $value User-supplied search term.
	 *
	 * @return string
	 */
	protected function build_like_pattern( string $value ): string {

		global $wpdb;

		return '%' . $wpdb->esc_like( $value ) . '%';
	}

	/**
	 * Run a status-grouped COUNT over the appointments table with a caller-supplied WHERE clause.
	 *
	 * @since 1.5.0
	 *
	 * @param string $where_sql Composed WHERE clause (without the leading `WHERE`).
	 * @param array  $params    Bindings matching the placeholders in `$where_sql`.
	 *
	 * @return array<string, int> Map of status => count.
	 */
	protected function load_status_counts( string $where_sql, array $params ): array {

		global $wpdb;

		$table = $wpdb->prefix . 'scbookings_appointments';
		$sql   = "SELECT appointments.status, COUNT(appointments.id) AS cnt FROM `{$table}` AS appointments WHERE {$where_sql} GROUP BY appointments.status";

		$rows = $this->run_internal_select( $sql, $params );

		$out = [];

		foreach ( (array) $rows as $row ) {
			$status = (string) ( $row['status'] ?? '' );

			if ( $status === '' ) {
				continue;
			}

			$out[ $status ] = (int) ( $row['cnt'] ?? 0 );
		}

		return $out;
	}

	/**
	 * Run a status+currency-grouped revenue SUM across the appointment → payment join.
	 *
	 * @since 1.5.0
	 *
	 * @param string $where_sql Composed WHERE clause (without the leading `WHERE`).
	 * @param array  $params    Bindings matching the placeholders in `$where_sql`.
	 *
	 * @return array<int, array{status: string, revenue: float, currency: string}>
	 */
	protected function load_revenue_buckets( string $where_sql, array $params ): array {

		global $wpdb;

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

		$sql = "
			SELECT p.status AS status, SUM(p.total_amount) AS revenue, p.currency AS currency
			FROM `{$appointments}` AS appointments
			INNER JOIN `{$payment_items}` AS pi ON appointments.id = pi.item_id AND pi.item_type = 'appointment'
			INNER JOIN `{$payments}` AS p ON pi.payment_id = p.id
			WHERE {$where_sql}
			GROUP BY p.status, p.currency
		";

		$rows = $this->run_internal_select( $sql, $params );

		$out = [];

		foreach ( (array) $rows as $row ) {
			$out[] = [
				'status'   => (string) ( $row['status'] ?? '' ),
				'revenue'  => (float) ( $row['revenue'] ?? 0.0 ),
				'currency' => (string) ( $row['currency'] ?? '' ),
			];
		}

		return $out;
	}

	/**
	 * Execute a SELECT composed of plugin-internal SQL with optional bound params.
	 *
	 * Centralizes the no-placeholder path so callers never branch on `empty( $params )` —
	 * the no-params query must contain only plugin-controlled strings (table names,
	 * literal column lists, fixed WHERE fragments). Any caller-supplied value must
	 * arrive through `$params` so it is bound via `$wpdb->prepare()`.
	 *
	 * @since 1.5.0
	 *
	 * @param string $sql    SQL composed only from plugin-controlled strings outside the placeholders.
	 * @param array  $params Placeholder bindings; may be empty.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	private function run_internal_select( string $sql, array $params ): array {

		global $wpdb;

		if ( empty( $params ) ) {
			// No bindings: $sql contains no placeholders and no user input — running prepare() here would `_doing_it_wrong`.
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return (array) $wpdb->get_results( $sql, ARRAY_A );
		}

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

	/**
	 * Reduce revenue buckets into total, per-status map, and currency.
	 *
	 * `total` only sums completed payments to align with the customer-aggregate
	 * semantics; refunded/failed buckets remain visible in `by_status`.
	 *
	 * When buckets contain more than one currency, the totals are not directly
	 * comparable. `currency` is reported as `MIXED` and `total` is returned as
	 * `null` so consumers (including AI agents) cannot mistake a cross-currency
	 * sum for a single-currency revenue figure. `by_status` still reflects the
	 * raw per-status sums for callers that want to inspect them.
	 *
	 * @since 1.5.0
	 *
	 * @param array<int, array{status: string, revenue: float, currency: string}> $rows Buckets from `load_revenue_buckets`.
	 *
	 * @return array{total: float|null, by_status: array<string, float>, currency: string}
	 */
	protected function aggregate_revenue( array $rows ): array {

		$total      = 0.0;
		$by_status  = [];
		$currencies = [];

		foreach ( $rows as $row ) {
			$status   = (string) ( $row['status'] ?? '' );
			$revenue  = (float) ( $row['revenue'] ?? 0.0 );
			$currency = (string) ( $row['currency'] ?? '' );

			if ( $currency !== '' ) {
				$currencies[ $currency ] = true;
			}

			$by_status[ $status ] = ( $by_status[ $status ] ?? 0.0 ) + $revenue;

			if ( $status === 'completed' ) {
				$total += $revenue;
			}
		}

		$unique_currencies = array_keys( $currencies );
		$has_mixed         = count( $unique_currencies ) > 1;

		return [
			'total'     => $has_mixed ? null : $total,
			'by_status' => $by_status,
			'currency'  => $has_mixed ? 'MIXED' : ( $unique_currencies[0] ?? '' ),
		];
	}

	/**
	 * Append `date_from` / `date_to` WHERE fragments to a raw-SQL filter pair.
	 *
	 * Boundaries are interpreted as midnight / end-of-day in the site's display
	 * timezone (matching {@see \SugarCalendarBookings\Appointments\Admin\AppointmentsListTable})
	 * and converted to UTC before binding, since `start_at` is stored in UTC.
	 *
	 * @since 1.5.0
	 *
	 * @param array  $args   Normalized input — reads `date_from` / `date_to`.
	 * @param string $column Fully-qualified column to compare against.
	 * @param array  $where  Existing WHERE fragments (passed by reference).
	 * @param array  $params Existing param bindings (passed by reference).
	 */
	protected function append_date_range_clauses( array $args, string $column, array &$where, array &$params ): void {

		if ( ! empty( $args['date_from'] ) ) {
			$from_utc = $this->local_day_boundary_to_utc( (string) $args['date_from'], '00:00:00' );

			if ( $from_utc !== null ) {
				$where[]  = "{$column} >= %s";
				$params[] = $from_utc;
			}
		}

		if ( ! empty( $args['date_to'] ) ) {
			$to_utc = $this->local_day_boundary_to_utc( (string) $args['date_to'], '23:59:59' );

			if ( $to_utc !== null ) {
				$where[]  = "{$column} <= %s";
				$params[] = $to_utc;
			}
		}
	}

	/**
	 * Convert a YYYY-MM-DD string + time-of-day to a UTC datetime suitable for SQL.
	 *
	 * The input date is interpreted in the site's display timezone, so a filter for
	 * `2026-04-28` on an `America/New_York` site covers `2026-04-28 04:00:00` to
	 * `2026-04-29 03:59:59` in UTC.
	 *
	 * @since 1.5.0
	 *
	 * @param string $date Raw date input (anything {@see Date::create()} accepts).
	 * @param string $time `H:i:s` to anchor the boundary at.
	 *
	 * @return string|null UTC `Y-m-d H:i:s` string, or null if the date is unparseable.
	 */
	protected function local_day_boundary_to_utc( string $date, string $time ): ?string {

		$parsed = Date::create( $date );

		if ( $parsed === null ) {
			return null;
		}

		try {
			$local = new DateTimeImmutable( $parsed->format( 'Y-m-d' ) . ' ' . $time, Date::get_default_timezone() );
		} catch ( Exception $e ) {
			return null;
		}

		return Date::to_utc( $local )->format( Date::DB_DATETIME_FORMAT );
	}

	/**
	 * Reject malformed dates and inverted ranges before they reach the query layer.
	 *
	 * @since 1.5.0
	 *
	 * @param array $args Normalized input — reads `date_from` and `date_to`.
	 *
	 * @return true|WP_Error
	 */
	protected function validate_date_range( array $args ) {

		$date_from_raw = ! empty( $args['date_from'] ) ? (string) $args['date_from'] : '';
		$date_to_raw   = ! empty( $args['date_to'] ) ? (string) $args['date_to'] : '';

		if ( $date_from_raw !== '' && ! $this->is_valid_iso_date( $date_from_raw ) ) {
			return new WP_Error(
				'sugar_calendar_bookings_invalid_date',
				esc_html__( 'Invalid date_from value. Use YYYY-MM-DD.', 'sugar-calendar-bookings-scheduling-appointments-lite' ),
				[ 'status' => 400 ]
			);
		}

		if ( $date_to_raw !== '' && ! $this->is_valid_iso_date( $date_to_raw ) ) {
			return new WP_Error(
				'sugar_calendar_bookings_invalid_date',
				esc_html__( 'Invalid date_to value. Use YYYY-MM-DD.', 'sugar-calendar-bookings-scheduling-appointments-lite' ),
				[ 'status' => 400 ]
			);
		}

		if ( $date_from_raw !== '' && $date_to_raw !== '' && strcmp( $date_from_raw, $date_to_raw ) > 0 ) {
			return new WP_Error(
				'sugar_calendar_bookings_invalid_date_range',
				esc_html__( 'date_from must be on or before date_to.', 'sugar-calendar-bookings-scheduling-appointments-lite' ),
				[ 'status' => 400 ]
			);
		}

		return true;
	}

	/**
	 * Check that a string is a real calendar date in strict `YYYY-MM-DD` format.
	 *
	 * Rejects inputs with time/zone components so that `local_day_boundary_to_utc()`
	 * cannot silently shift the date across midnight when re-anchoring to the site
	 * timezone.
	 *
	 * @since 1.5.0
	 *
	 * @param string $value Raw user-supplied date.
	 *
	 * @return bool
	 */
	private function is_valid_iso_date( string $value ): bool {

		if ( preg_match( '/^(\d{4})-(\d{2})-(\d{2})$/', $value, $matches ) !== 1 ) {
			return false;
		}

		return checkdate( (int) $matches[2], (int) $matches[3], (int) $matches[1] );
	}
}
