<?php
/**
 * Appointments repository.
 *
 * @package Abientot
 */

namespace Abientot\Repositories;

use Abientot\Support\Db;

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

final class AppointmentsRepository extends BaseRepository {
	protected string $table_suffix = 'appointments';

	/** Durable duplicate check. Caller holds BookingLock; no transient is authoritative. */
	public function has_public_submission( array $customer, int $service_id, string $start_local, ?string $start_utc = null ): bool {
		global $wpdb;
		$local = new \DateTimeImmutable( $start_utc ?? $start_local, new \DateTimeZone( 'UTC' ) );
		$lower = $local->modify( '-1 day' )->format( 'Y-m-d H:i:s' );
		$upper = $local->modify( '+1 day' )->format( 'Y-m-d H:i:s' );
		$after = 0;
		do {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.id, a.start_utc, a.timezone, a.customer_fields_json, c.email, c.phone, c.first_name, c.last_name
				FROM %i a LEFT JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.service_id = %d AND a.source = 'frontend' AND a.id > %d
				AND a.start_utc BETWEEN %s AND %s AND (st.slug IS NULL OR st.slug <> 'cancelled')
				ORDER BY a.id ASC LIMIT 250",
					$this->table(),
					$service_id,
					$after,
					$lower,
					$upper
				),
				ARRAY_A
			);
			if ( $wpdb->last_error ) {
				throw new \RuntimeException( 'Duplicate lookup failed.' );
			}
			foreach ( $rows as $row ) {
				$after = (int) $row['id'];
				try {
					$stored = ( new \DateTimeImmutable( $row['start_utc'], new \DateTimeZone( 'UTC' ) ) )->setTimezone( new \DateTimeZone( $row['timezone'] ) );
				} catch ( \Exception $exception ) {
					continue;
				}
				if ( $start_utc !== null ? $row['start_utc'] !== $start_utc : $stored->format( 'Y-m-d H:i:s' ) !== $local->format( 'Y-m-d H:i:s' ) ) {
					continue;
				}
				$snapshot = json_decode( (string) $row['customer_fields_json'], true );
				foreach ( is_array( $snapshot ) ? $snapshot : array() as $field ) {
					if ( is_array( $field ) && isset( $field['key'], $field['value'] ) && is_scalar( $field['value'] ) ) {
						$row[ $field['key'] ] = (string) $field['value'];
					}
				}
				$key      = ! empty( $customer['email'] ) ? 'email' : ( ! empty( $customer['phone'] ) ? 'phone' : 'name' );
				$incoming = 'name' === $key ? trim( $customer['first_name'] . ' ' . $customer['last_name'] ) : trim( $customer[ $key ] );
				$existing = 'name' === $key ? trim( $row['first_name'] . ' ' . $row['last_name'] ) : trim( (string) $row[ $key ] );
				if ( '' !== $incoming && 0 === strcasecmp( $incoming, $existing ) ) {
					return true;
				}
			}
		} while ( count( $rows ) === 250 );
		return false;
	}

	/**
	 * Counts appointments linked to a service.
	 */
	public function count_by_service( int $service_id ): int {
		global $wpdb;

		if ( $service_id <= 0 ) {
			return 0;
		}

		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE service_id = %d", $this->table(), $service_id )
		);
	}

	/**
	 * Returns appointment rows that would be affected by deleting a service.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function service_delete_impact( int $service_id, int $limit = 200 ): array {
		global $wpdb;

		if ( $service_id <= 0 ) {
			return array();
		}

		$limit = max( 1, min( 500, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.id, a.customer_id, a.status_id, a.start_utc, a.end_utc, a.timezone, a.payment_status,
					c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
					st.slug AS status_slug, st.label AS status_label
				FROM %i a
				LEFT JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.service_id = %d
				ORDER BY a.start_utc ASC
				LIMIT %d",
				$this->table(),
				$service_id,
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/** Retained relationships require the Team structural-edit contract, including bulk operations. */
	public function service_has_retained_relationships( int $service_id ): bool {
		global $wpdb;
		$staff     = $wpdb->prefix . 'abientot_staff';
		$locations = $wpdb->prefix . 'abientot_locations';
		$count     = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i a LEFT JOIN %i sf ON sf.id = a.staff_id LEFT JOIN %i l ON l.id = a.location_id WHERE a.service_id = %d AND (sf.id IS NULL OR l.id IS NULL OR sf.is_default <> 1 OR l.is_default <> 1)", $this->table(), $staff, $locations, $service_id ) );
		return null === $count || '' !== $wpdb->last_error || (int) $count > 0;
	}

	/** Reassigns every appointment from one service to another. */
	public function reassign_service( int $from_service_id, int $to_service_id ): int {
		global $wpdb;
		$retained = new ResourceBookingRepository();
		if ( $retained->internal_service( $from_service_id ) || $retained->internal_service( $to_service_id ) ) {
			return 0;
		}

		if ( $from_service_id <= 0 || $to_service_id <= 0 || $from_service_id === $to_service_id ) {
			return 0;
		}

		$result = $wpdb->update(
			$this->table(),
			array(
				'service_id' => $to_service_id,
				'updated_at' => gmdate( 'Y-m-d H:i:s' ),
			),
			array( 'service_id' => $from_service_id ),
			array( '%d', '%s' ),
			array( '%d' )
		);

		return false === $result ? 0 : (int) $result;
	}

	/**
	 * Returns currencies actually used by bookings, plus the configured currency.
	 *
	 * @return string[]
	 */
	public function reporting_currencies(): array {
		global $wpdb;
		return array_values( array_unique( array_merge( array( (string) \Abientot\Admin\Settings::values()['currency'] ), $wpdb->get_col( "SELECT DISTINCT currency FROM {$wpdb->prefix}abientot_appointments ORDER BY currency LIMIT 50" ) ) ) );
	}

	/** Returns KPI totals for a UTC period in one currency. */
	public function dashboard_summary( string $start_utc, string $end_utc, ?string $currency = null ): array {
		global $wpdb;
		$currency = $currency ?? (string) \Abientot\Admin\Settings::values()['currency'];

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					COUNT(*) AS booking_count,
					COALESCE(SUM(CASE WHEN st.counts_as_revenue = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents,
					COALESCE(SUM(CASE WHEN st.counts_as_revenue = 1 THEN 1 ELSE 0 END), 0) AS revenue_booking_count,
					COALESCE(AVG(a.duration_minutes), 0) AS average_duration_minutes,
					COALESCE(SUM(CASE WHEN st.slug = 'pending' THEN 1 ELSE 0 END), 0) AS pending_count,
					COALESCE(SUM(CASE WHEN st.slug = 'completed' THEN 1 ELSE 0 END), 0) AS completed_count,
					COALESCE(SUM(CASE WHEN st.slug = 'cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_count,
					COALESCE(SUM(CASE WHEN st.slug = 'no_show' THEN 1 ELSE 0 END), 0) AS no_show_count
				FROM %i a
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s",
				$this->table(),
				$start_utc,
				$end_utc,
				$currency
			),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			$row = array();
		}

		return array(
			'booking_count'            => (int) ( $row['booking_count'] ?? 0 ),
			'revenue_cents'            => (int) ( $row['revenue_cents'] ?? 0 ),
			'revenue_booking_count'    => (int) ( $row['revenue_booking_count'] ?? 0 ),
			'average_duration_minutes' => (int) round( (float) ( $row['average_duration_minutes'] ?? 0 ) ),
			'pending_count'            => (int) ( $row['pending_count'] ?? 0 ),
			'completed_count'          => (int) ( $row['completed_count'] ?? 0 ),
			'cancelled_count'          => (int) ( $row['cancelled_count'] ?? 0 ),
			'no_show_count'            => (int) ( $row['no_show_count'] ?? 0 ),
		);
	}

	/**
	 * Returns daily booking and revenue totals for a UTC period.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function dashboard_time_series( string $start_utc, string $end_utc, ?string $currency = null ): array {
		global $wpdb;
		$currency = $currency ?? (string) \Abientot\Admin\Settings::values()['currency'];

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(a.start_utc) AS period_date,
					COUNT(*) AS booking_count,
					COALESCE(SUM(CASE WHEN st.counts_as_revenue = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents
				FROM %i a
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s
				GROUP BY DATE(a.start_utc)
				ORDER BY period_date ASC",
				$this->table(),
				$start_utc,
				$end_utc,
				$currency
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Returns revenue grouped by service for a UTC period.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function dashboard_revenue_by_service( string $start_utc, string $end_utc, ?string $currency = null ): array {
		global $wpdb;
		$currency = $currency ?? (string) \Abientot\Admin\Settings::values()['currency'];

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT sv.id, sv.name, sv.color,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN st.counts_as_revenue = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents
				FROM %i a
				INNER JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s
				GROUP BY sv.id, sv.name, sv.color
				ORDER BY revenue_cents DESC, booking_count DESC, sv.name ASC
				LIMIT 10",
				$this->table(),
				$start_utc,
				$end_utc,
				$currency
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Returns revenue grouped by staff for a UTC period.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function dashboard_revenue_by_staff( string $start_utc, string $end_utc, ?string $currency = null ): array {
		global $wpdb;
		$currency = $currency ?? (string) \Abientot\Admin\Settings::values()['currency'];

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.staff_id AS id, COALESCE(sf.display_name, '') AS display_name,
					COUNT(a.id) AS booking_count,
					COALESCE(SUM(CASE WHEN st.counts_as_revenue = 1 THEN a.price_cents ELSE 0 END), 0) AS revenue_cents
				FROM %i a
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s
				GROUP BY a.staff_id, sf.display_name
				ORDER BY revenue_cents DESC, booking_count DESC, sf.display_name ASC
				LIMIT 10",
				$this->table(),
				$start_utc,
				$end_utc,
				$currency
			),
			ARRAY_A
		);

		if ( ! is_array( $rows ) ) {
			return array();
		}
		foreach ( $rows as&$row ) {
			if ( (int) $row['id'] === 0 ) {
				$row['display_name'] = __( 'No staff assigned', 'abientot' );
			}
		}
		unset( $row );
		return $rows;
	}

	/**
	 * Returns booking counts grouped by status for a UTC period.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function dashboard_status_breakdown( string $start_utc, string $end_utc, ?string $currency = null ): array {
		global $wpdb;
		$currency = $currency ?? (string) \Abientot\Admin\Settings::values()['currency'];

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT st.id, st.slug, st.label, st.color, COUNT(a.id) AS booking_count
				FROM %i a
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.start_utc >= %s AND a.start_utc < %s AND a.currency = %s
				GROUP BY st.id, st.slug, st.label, st.color, st.sort_order
				ORDER BY st.sort_order ASC, st.label ASC",
				$this->table(),
				$start_utc,
				$end_utc,
				$currency
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Returns joined booking rows for the admin list.
	 *
	 * @param array<string,mixed> $filters List filters.
	 * @return array<int,array<string,mixed>>
	 */
	public function list_for_admin( array $filters = array(), int $limit = 50, int $offset = 0 ): array {
		global $wpdb;

		$limit  = max( 1, min( 200, $limit ) );
		$offset = max( 0, $offset );
		$f      = $this->admin_filter_args( $filters );

		if ( ! empty( $filters['order'] ) && 'ASC' === strtoupper( (string) $filters['order'] ) ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
					COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
					st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
					FROM {$wpdb->prefix}abientot_appointments a
					INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
					LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
					LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
					INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
					INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
					WHERE ( %d = 0 OR a.status_id = %d ) AND ( %d = 0 OR a.service_id = %d ) AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) )
					AND ( %d = 0 OR a.payment_status = %s ) AND ( %d = 0 OR a.start_utc >= %s ) AND ( %d = 0 OR a.start_utc <= %s )
					AND ( %d = 0 OR c.display_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s OR sv.name LIKE %s OR sf.display_name LIKE %s OR l.name LIKE %s )
					ORDER BY a.start_utc ASC, a.id ASC LIMIT %d OFFSET %d",
					$f[0],
					$f[1],
					$f[2],
					$f[3],
					$f[4],
					$f[4],
					$f[5],
					$f[6],
					$f[7],
					$f[8],
					$f[9],
					$f[10],
					$f[11],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$limit,
					$offset
				),
				ARRAY_A
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
					COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
					st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
					FROM {$wpdb->prefix}abientot_appointments a
					INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
					LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
					LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
					INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
					INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
					WHERE ( %d = 0 OR a.status_id = %d ) AND ( %d = 0 OR a.service_id = %d ) AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) )
					AND ( %d = 0 OR a.payment_status = %s ) AND ( %d = 0 OR a.start_utc >= %s ) AND ( %d = 0 OR a.start_utc <= %s )
					AND ( %d = 0 OR c.display_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s OR sv.name LIKE %s OR sf.display_name LIKE %s OR l.name LIKE %s )
					ORDER BY a.start_utc DESC, a.id DESC LIMIT %d OFFSET %d",
					$f[0],
					$f[1],
					$f[2],
					$f[3],
					$f[4],
					$f[4],
					$f[5],
					$f[6],
					$f[7],
					$f[8],
					$f[9],
					$f[10],
					$f[11],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$limit,
					$offset
				),
				ARRAY_A
			);
		}

		return $this->admin_rows( $rows );
	}

	/**
	 * Counts bookings matching admin filters.
	 *
	 * @param array<string,mixed> $filters List filters.
	 */
	public function count_for_admin( array $filters = array() ): int {
		global $wpdb;

		$f = $this->admin_filter_args( $filters );

		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*)
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				LEFT JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				WHERE ( %d = 0 OR a.status_id = %d ) AND ( %d = 0 OR a.service_id = %d ) AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) )
				AND ( %d = 0 OR a.payment_status = %s ) AND ( %d = 0 OR a.start_utc >= %s ) AND ( %d = 0 OR a.start_utc <= %s )
				AND ( %d = 0 OR c.display_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s OR sv.name LIKE %s OR sf.display_name LIKE %s OR l.name LIKE %s )",
				$f[0],
				$f[1],
				$f[2],
				$f[3],
				$f[4],
				$f[4],
				$f[5],
				$f[6],
				$f[7],
				$f[8],
				$f[9],
				$f[10],
				$f[11],
				$f[12],
				$f[12],
				$f[12],
				$f[12],
				$f[12],
				$f[12]
			)
		);
	}

	/**
	 * Finds one joined booking row.
	 *
	 * @return array<string,mixed>|null
	 */
	public function find_for_admin( int $id ): ?array {
		global $wpdb;

		if ( $id <= 0 ) {
			return null;
		}

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
				COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
				st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.id = %d",
				$id
			),
			ARRAY_A
		);

		return is_array( $row ) ? $this->admin_rows( array( $row ) )[0] : null;
	}

	/**
	 * Exports joined booking rows.
	 *
	 * @param array<string,mixed> $filters List filters.
	 * @return array<int,array<string,mixed>>
	 */
	public function export_for_admin( array $filters = array(), int $limit = 5000 ): array {
		global $wpdb;

		$limit = max( 1, min( 10000, $limit ) );
		$f     = $this->admin_filter_args( $filters );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
				COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
				st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE ( %d = 0 OR a.status_id = %d ) AND ( %d = 0 OR a.service_id = %d ) AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) )
				AND ( %d = 0 OR a.payment_status = %s ) AND ( %d = 0 OR a.start_utc >= %s ) AND ( %d = 0 OR a.start_utc <= %s )
				AND ( %d = 0 OR c.display_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s OR sv.name LIKE %s OR sf.display_name LIKE %s OR l.name LIKE %s )
				ORDER BY a.start_utc DESC, a.id DESC LIMIT %d",
				$f[0],
				$f[1],
				$f[2],
				$f[3],
				$f[4],
				$f[4],
				$f[5],
				$f[6],
				$f[7],
				$f[8],
				$f[9],
				$f[10],
				$f[11],
				$f[12],
				$f[12],
				$f[12],
				$f[12],
				$f[12],
				$f[12],
				$limit
			),
			ARRAY_A
		);

		return $this->admin_rows( $rows );
	}

	/** Stream complete CSV data without retaining the entire export in memory. */
	public function iterate_for_export( array $filters = array() ): \Generator {
		global $wpdb;
		$after = 0;
		$f     = $this->admin_filter_args( $filters );
		do {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
					COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
					st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
					FROM {$wpdb->prefix}abientot_appointments a
					INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
					LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
					LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
					INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
					INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
					WHERE a.id > %d AND ( %d = 0 OR a.status_id = %d ) AND ( %d = 0 OR a.service_id = %d ) AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) )
					AND ( %d = 0 OR a.payment_status = %s ) AND ( %d = 0 OR a.start_utc >= %s ) AND ( %d = 0 OR a.start_utc <= %s )
					AND ( %d = 0 OR c.display_name LIKE %s OR c.email LIKE %s OR c.phone LIKE %s OR sv.name LIKE %s OR sf.display_name LIKE %s OR l.name LIKE %s )
					ORDER BY a.id ASC LIMIT 250",
					$after,
					$f[0],
					$f[1],
					$f[2],
					$f[3],
					$f[4],
					$f[4],
					$f[5],
					$f[6],
					$f[7],
					$f[8],
					$f[9],
					$f[10],
					$f[11],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$f[12],
					$f[12]
				),
				ARRAY_A
			);
			$rows = is_array( $rows ) ? $rows : array();
			foreach ( $this->admin_rows( $rows ) as $row ) {
				$after = (int) $row['id'];
				yield $row;
			}
		} while ( count( $rows ) === 250 );
	}

	/**
	 * Returns joined booking rows that overlap a UTC calendar window.
	 *
	 * @param array<string,mixed> $filters Calendar filters.
	 * @return array<int,array<string,mixed>>
	 */
	public function calendar_between( string $start_utc, string $end_utc, array $filters = array(), int $limit = 500, int $offset = 0 ): array {
		global $wpdb;

		if ( '' === $start_utc || '' === $end_utc ) {
			return array();
		}

		$limit = max( 1, min( 1000, $limit ) );
		$f     = $this->admin_filter_args( array_intersect_key( $filters, array_flip( array( 'service_id', 'status_id', 'staff_id' ) ) ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
				COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
				st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.start_utc < %s AND a.end_utc > %s
				AND ( %d = 0 OR a.service_id = %d ) AND ( %d = 0 OR a.status_id = %d ) AND ( %d = 0 OR a.staff_id = GREATEST( %d, 0 ) )
				ORDER BY a.start_utc ASC, a.id ASC LIMIT %d OFFSET %d",
				$end_utc,
				$start_utc,
				$f[2],
				$f[3],
				$f[0],
				$f[1],
				$f[4],
				$f[4],
				$limit,
				max( 0, $offset )
			),
			ARRAY_A
		);

		return $this->admin_rows( $rows );
	}

	/** Complete calendar fallback, fetched in bounded database pages. */
	public function calendar_all( string $start_utc, string $end_utc, array $filters = array() ): array {
		$rows   = array();
		$offset = 0;
		do {
			$page    = $this->calendar_between( $start_utc, $end_utc, $filters, 250, $offset );
			$rows    = array_merge( $rows, $page );
			$offset += count( $page );
		} while ( count( $page ) === 250 );
		return $rows;
	}

	/**
	 * Returns compact admin booking rows that overlap a cached admin window.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function admin_window( string $start_utc, string $end_utc, int $limit = 5000, int $after_id = 0 ): array {
		global $wpdb;

		if ( '' === $start_utc || '' === $end_utc ) {
			return array();
		}

		$limit = max( 1, min( 10000, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
				COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
				st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.start_utc < %s AND a.end_utc > %s AND a.id > %d ORDER BY a.id ASC LIMIT %d",
				$end_utc,
				$start_utc,
				max( 0, $after_id ),
				$limit
			),
			ARRAY_A
		);

		return $this->admin_rows( $rows );
	}

	/**
	 * Returns admin booking rows changed since a timestamp inside a cached window.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function admin_changed_since( string $since_utc, string $start_utc, string $end_utc, int $limit = 1000, int $after_id = 0 ): array {
		global $wpdb;

		if ( '' === $since_utc || '' === $start_utc || '' === $end_utc ) {
			return array();
		}

		$limit = max( 1, min( 5000, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
				COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
				st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.updated_at >= %s AND a.id > %d ORDER BY a.id ASC LIMIT %d",
				$since_utc,
				max( 0, $after_id ),
				$limit
			),
			ARRAY_A
		);

		return $this->admin_rows( $rows );
	}

	/**
	 * Finds appointments for a staff member in a UTC window.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function find_for_staff_between( int $staff_id, string $start_utc, string $end_utc ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE staff_id = %d AND start_utc < %s AND end_utc > %s ORDER BY start_utc ASC",
				$this->table(),
				$staff_id,
				$end_utc,
				$start_utc
			),
			ARRAY_A
		);

		return ( new ResourceBookingRepository() )->decorate_rows( is_array( $rows ) ? $rows : array() );
	}

	/**
	 * Finds appointments that block availability for a staff member in a UTC window.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function find_blocking_for_staff_between( int $staff_id, string $start_utc, string $end_utc, ?int $exclude_appointment_id = null ): array {
		global $wpdb;

		if ( $staff_id <= 0 || '' === $start_utc || '' === $end_utc ) {
			return array();
		}

		$groups = new GroupBookingRepository();
		// Appointment IDs start at 1, so 0 excludes nothing.
		$exclude   = null !== $exclude_appointment_id && $exclude_appointment_id > 0 ? $exclude_appointment_id : 0;
		$batch_ids = array_map( 'intval', \Abientot\Support\BookingConstraints::excluded_ids() ) ?: array( 0 );

		if ( $groups->present() ) {
			$rows = Db::rows(
				$wpdb->suppress_errors( true ),
				$wpdb->get_results(
					$wpdb->prepare(
						"SELECT a.*, s.slug AS status_slug, s.label AS status_label,
						DATE_SUB(a.start_utc, INTERVAL COALESCE(svc.buffer_before_minutes, 0) MINUTE) AS blocked_start_utc,
						DATE_ADD(a.end_utc, INTERVAL COALESCE(svc.buffer_after_minutes, 0) MINUTE) AS blocked_end_utc
						FROM {$wpdb->prefix}abientot_appointments a
						LEFT JOIN {$wpdb->prefix}abientot_booking_statuses s ON s.id = a.status_id
						LEFT JOIN {$wpdb->prefix}abientot_services svc ON svc.id = a.service_id
						WHERE a.staff_id = %d
						AND DATE_SUB(a.start_utc, INTERVAL COALESCE(svc.buffer_before_minutes, 0) MINUTE) < %s
						AND DATE_ADD(a.end_utc, INTERVAL COALESCE(svc.buffer_after_minutes, 0) MINUTE) > %s
						AND COALESCE(s.blocks_availability, 1) = 1
						AND NOT EXISTS (SELECT 1 FROM {$wpdb->prefix}abientot_group_memberships m INNER JOIN {$wpdb->prefix}abientot_group_sessions g ON g.id=m.session_id WHERE m.appointment_id=a.id AND g.status<>'cancelled')
						AND a.id <> %d AND a.id NOT IN (" . implode( ',', array_fill( 0, count( $batch_ids ), '%d' ) ) . ')
						ORDER BY a.start_utc ASC',
						array_merge( array( $staff_id, $end_utc, $start_utc, $exclude ), $batch_ids )
					),
					ARRAY_A
				),
				'availability_storage_unavailable'
			);
		} else {
			$rows = Db::rows(
				$wpdb->suppress_errors( true ),
				$wpdb->get_results(
					$wpdb->prepare(
						"SELECT a.*, s.slug AS status_slug, s.label AS status_label,
						DATE_SUB(a.start_utc, INTERVAL COALESCE(svc.buffer_before_minutes, 0) MINUTE) AS blocked_start_utc,
						DATE_ADD(a.end_utc, INTERVAL COALESCE(svc.buffer_after_minutes, 0) MINUTE) AS blocked_end_utc
						FROM {$wpdb->prefix}abientot_appointments a
						LEFT JOIN {$wpdb->prefix}abientot_booking_statuses s ON s.id = a.status_id
						LEFT JOIN {$wpdb->prefix}abientot_services svc ON svc.id = a.service_id
						WHERE a.staff_id = %d
						AND DATE_SUB(a.start_utc, INTERVAL COALESCE(svc.buffer_before_minutes, 0) MINUTE) < %s
						AND DATE_ADD(a.end_utc, INTERVAL COALESCE(svc.buffer_after_minutes, 0) MINUTE) > %s
						AND COALESCE(s.blocks_availability, 1) = 1
						AND a.id <> %d AND a.id NOT IN (" . implode( ',', array_fill( 0, count( $batch_ids ), '%d' ) ) . ')
						ORDER BY a.start_utc ASC',
						array_merge( array( $staff_id, $end_utc, $start_utc, $exclude ), $batch_ids )
					),
					ARRAY_A
				),
				'availability_storage_unavailable'
			);
		}

		return array_merge( $rows, $groups->blockers_for_staff( $staff_id, $start_utc, $end_utc ) );
	}

	/**
	 * Finds blocking appointments for a staff member in a UTC window with admin display fields.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function blocking_for_staff_between_admin( int $staff_id, string $start_utc, string $end_utc, int $limit = 100 ): array {
		global $wpdb;

		if ( $staff_id <= 0 || '' === $start_utc || '' === $end_utc ) {
			return array();
		}

		$limit = max( 1, min( 500, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
				COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
				st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.staff_id = %d AND a.start_utc < %s AND a.end_utc > %s AND st.blocks_availability = 1 ORDER BY a.start_utc ASC LIMIT %d",
				$staff_id,
				$end_utc,
				$start_utc,
				$limit
			),
			ARRAY_A
		);

		return $this->admin_rows( $rows );
	}

	/**
	 * Finds blocking appointments for a location in a UTC window with admin display fields.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function blocking_for_location_between_admin( int $location_id, string $start_utc, string $end_utc, int $limit = 100 ): array {
		global $wpdb;

		if ( $location_id <= 0 || '' === $start_utc || '' === $end_utc ) {
			return array();
		}

		$limit = max( 1, min( 500, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT a.*, c.first_name, c.last_name, c.display_name AS customer_name, c.email AS customer_email, c.phone AS customer_phone,
				COALESCE(sv.name, 'Deleted service') AS service_name, COALESCE(sv.color, '#6b7280') AS service_color, COALESCE(sf.display_name, '') AS staff_name, l.name AS location_name,
				st.slug AS status_slug, st.label AS status_label, st.color AS status_color, st.blocks_availability
				FROM {$wpdb->prefix}abientot_appointments a
				INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id = a.customer_id
				LEFT JOIN {$wpdb->prefix}abientot_services sv ON sv.id = a.service_id
				LEFT JOIN {$wpdb->prefix}abientot_staff sf ON sf.id = a.staff_id
				INNER JOIN {$wpdb->prefix}abientot_locations l ON l.id = a.location_id
				INNER JOIN {$wpdb->prefix}abientot_booking_statuses st ON st.id = a.status_id
				WHERE a.location_id = %d AND a.start_utc < %s AND a.end_utc > %s AND st.blocks_availability = 1 ORDER BY a.start_utc ASC LIMIT %d",
				$location_id,
				$end_utc,
				$start_utc,
				$limit
			),
			ARRAY_A
		);

		return $this->admin_rows( $rows );
	}

	/**
	 * Adds the payment-plan flag and the retained resource facts to admin rows.
	 *
	 * @param mixed $rows Result of an admin row query.
	 * @return array<int,array<string,mixed>>
	 */
	private function admin_rows( $rows ): array {
		global $wpdb;

		$rows  = is_array( $rows ) ? $rows : array();
		$plans = array();
		$ids   = array_values( array_unique( array_map( 'intval', array_column( $rows, 'id' ) ) ) );
		if ( $ids && ( new PaymentPlanRepository() )->present() ) {
			foreach ( array_chunk( $ids, 200 ) as $chunk ) {
				$found = $wpdb->get_col( $wpdb->prepare( "SELECT appointment_id FROM {$wpdb->prefix}abientot_payment_plans WHERE appointment_id IN (" . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ')', $chunk ) );
				foreach ( (array) $found as $appointment_id ) {
					$plans[ (int) $appointment_id ] = true;
				}
			}
		}
		foreach ( $rows as &$row ) {
			$row['has_payment_plan'] = isset( $plans[ (int) $row['id'] ] ) ? '1' : '0';
		}
		unset( $row );

		return ( new ResourceBookingRepository() )->decorate_rows( $rows );
	}

	/**
	 * Placeholder values for the admin filter clause, in clause order:
	 * status on/id, service on/id, staff (StaffFilter::arg, used twice), payment status on/value,
	 * from on/value, to on/value, search on/LIKE pattern (used six times).
	 *
	 * @param array<string,mixed> $filters List filters.
	 * @return array<int,int|string>
	 */
	private function admin_filter_args( array $filters ): array {
		global $wpdb;

		return array(
			empty( $filters['status_id'] ) ? 0 : 1,
			(int) ( $filters['status_id'] ?? 0 ),
			empty( $filters['service_id'] ) ? 0 : 1,
			(int) ( $filters['service_id'] ?? 0 ),
			\Abientot\Support\StaffFilter::arg( $filters['staff_id'] ?? 0 ),
			empty( $filters['payment_status'] ) ? 0 : 1,
			(string) ( $filters['payment_status'] ?? '' ),
			empty( $filters['date_from_utc'] ) ? 0 : 1,
			(string) ( $filters['date_from_utc'] ?? '' ),
			empty( $filters['date_to_utc'] ) ? 0 : 1,
			(string) ( $filters['date_to_utc'] ?? '' ),
			empty( $filters['search'] ) ? 0 : 1,
			'%' . $wpdb->esc_like( (string) ( $filters['search'] ?? '' ) ) . '%',
		);
	}
}