<?php

declare( strict_types=1 );

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

/**
 * Handles booking creation, retrieval, and status management.
 * Cash booking is implemented here (3.2).
 * Online (Stripe) booking is wired in Phase 7.
 * Full booking flow (slot locking, confirmation) is Phase 5.
 */
class Playard_Booking {

	// ── Cash booking ─────────────────────────────────────────────────────────────

	/**
	 * Creates a cash / pay-on-arrival booking.
	 *
	 * @param  array<string,mixed> $data  Keys: field_id, booking_date, slots (string[]),
	 *                                    sport, customer (array with name + phone).
	 * @return array<string,mixed>|WP_Error
	 */
	public function create_cash_booking( array $data ): array|WP_Error {
		global $wpdb;

		$field_id = absint( $data['field_id'] );
		$field    = playard_get_field( $field_id );

		if ( ! $field ) {
			return new WP_Error( 'field_not_found', __( 'Field not found.', 'playard' ) );
		}

		if ( $field->payment_mode === 'online' ) {
			return new WP_Error(
				'cash_not_allowed',
				__( 'This field requires online payment.', 'playard' )
			);
		}

		$date       = sanitize_text_field( (string) ( $data['booking_date'] ?? '' ) );
		$slots      = array_values( array_filter( (array) ( $data['slots'] ?? [] ) ) );
		$slot_count = count( $slots );

		if ( $slot_count === 0 ) {
			return new WP_Error( 'no_slots', __( 'No slots provided.', 'playard' ) );
		}

		// Conflict check — must run before customer creation or DB insert.
		foreach ( $slots as $start ) {
			$end = $this->get_end_time( $field, (string) $start );
			if ( $this->is_slot_taken( $field_id, $date, (string) $start, $end ) ) {
				return new WP_Error(
					'slot_unavailable',
					// translators: %s is the time slot e.g. "14:00"
					sprintf( __( 'Slot %s is no longer available.', 'playard' ), $start )
				);
			}
		}

		// If customer_id is supplied (logged-in portal user), use it directly.
		$provided_customer_id = absint( $data['customer_id'] ?? 0 );
		if ( $provided_customer_id > 0 ) {
			$customer = ( new Playard_Customer() )->get( $provided_customer_id );
			if ( ! $customer ) {
				return new WP_Error( 'customer_not_found', __( 'Customer account not found.', 'playard' ) );
			}
		} else {
			$customer = playard_get_or_create_customer( (array) ( $data['customer'] ?? [] ) );
		}

		$total       = $this->calculate_total( $field, $date, $slots );
		$coupon_code = strtoupper( sanitize_text_field( (string) ( $data['coupon_code'] ?? '' ) ) );
		$total       = (float) apply_filters( 'playard_cash_booking_amount', $total, $coupon_code );
		$ref         = playard_generate_booking_ref();

		sort( $slots );
		$result = $wpdb->insert(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_ref'     => $ref,
				'field_id'        => $field_id,
				'customer_id'     => absint( $customer->id ),
				'booking_date'    => $date,
				'start_time'      => sanitize_text_field( $slots[0] ),
				'end_time'        => $this->get_end_time( $field, end( $slots ) ),
				'slots_count'     => $slot_count,
				'booked_slots'    => wp_json_encode( array_values( $slots ) ),
				'sport'           => sanitize_text_field( (string) ( $data['sport'] ?? '' ) ),
				'amount_total'    => $total,
				'amount_paid'     => 0.00,
				'amount_due'      => $total,
				'payment_mode'    => 'cash',
				'payment_status'  => 'pending',
				'booking_status'  => 'pending',
				'payment_gateway' => 'cash',
			],
			[ '%s', '%d', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%f', '%f', '%f', '%s', '%s', '%s', '%s' ]
		);

		if ( false === $result ) {
			return new WP_Error( 'db_error', __( 'Could not create booking.', 'playard' ) );
		}

		$booking_id = (int) $wpdb->insert_id;

		do_action( 'playard_booking_created', $booking_id, 'cash' );
		do_action( 'playard_cash_booking_created', $booking_id );

		return [
			'booking_ref'    => $ref,
			'booking_status' => 'pending',
			'payment_status' => 'pending',
			'amount_total'   => $total,
			'customer_id'    => (int) $customer->id,
		];
	}

	// ── Pending booking (new lock-free flow) ─────────────────────────────────────

	/**
	 * Creates a pending booking row. This IS the slot lock — no separate table needed.
	 * Called when customer clicks "Pay". Returns booking details for the payment step.
	 *
	 * @param  array<string,mixed> $data  Keys: field_id, booking_date, slots[], sport,
	 *                                    customer (name, phone, email), customer_id (optional)
	 * @return array<string,mixed>|WP_Error
	 */
	public function create_pending_booking( array $data ): array|WP_Error {
		global $wpdb;

		$field_id = absint( $data['field_id'] ?? 0 );
		$field    = playard_get_field( $field_id );

		if ( ! $field ) {
			return new WP_Error( 'field_not_found', __( 'Field not found.', 'playard' ) );
		}

		$date       = sanitize_text_field( (string) ( $data['booking_date'] ?? '' ) );
		$slots      = array_values( array_filter( array_map( 'sanitize_text_field', (array) ( $data['slots'] ?? [] ) ) ) );
		$sport      = sanitize_text_field( (string) ( $data['sport'] ?? '' ) );
		$slot_count = count( $slots );

		if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ) {
			return new WP_Error( 'invalid_date', __( 'Invalid booking date.', 'playard' ) );
		}

		if ( $slot_count === 0 ) {
			return new WP_Error( 'no_slots', __( 'No slots provided.', 'playard' ) );
		}

		// Availability check — pending bookings block just like confirmed ones.
		$slot_obj = new Playard_Slot();
		sort( $slots );

		$available  = [];
		$taken_slot = null;

		foreach ( $slots as $start ) {
			$end = $this->get_end_time( $field, (string) $start );
			if ( $this->is_slot_taken( $field_id, $date, (string) $start, $end ) ) {
				$taken_slot = $start;
				break;
			}
		}

		if ( $taken_slot !== null ) {
			// Return available alternatives for the same date.
			$day_slots = $slot_obj->get_slots_for_date( $field_id, $date );
			foreach ( $day_slots as $s ) {
				if ( $s['status'] === 'available' ) {
					$available[] = $s['start'];
				}
			}

			return new WP_Error(
				'slot_taken',
				sprintf(
					/* translators: %s: slot start time */
					__( '%s was just booked. Please choose another slot.', 'playard' ),
					$taken_slot
				),
				[ 'available' => $available ]
			);
		}

		// Get or create customer.
		$provided_customer_id = absint( $data['customer_id'] ?? 0 );
		if ( $provided_customer_id > 0 ) {
			$customer = ( new Playard_Customer() )->get( $provided_customer_id );
			if ( ! $customer ) {
				return new WP_Error( 'customer_not_found', __( 'Customer account not found.', 'playard' ) );
			}
		} else {
			$customer = playard_get_or_create_customer( (array) ( $data['customer'] ?? [] ) );
		}

		$total   = $this->calculate_total( $field, $date, $slots );
		$coupon  = strtoupper( sanitize_text_field( (string) ( $data['coupon_code'] ?? '' ) ) );
		$total   = (float) apply_filters( 'playard_pending_booking_amount', $total, $coupon );
		$gateway = sanitize_text_field( (string) ( $data['payment_gateway'] ?? '' ) );
		$ref     = playard_generate_booking_ref();
		$start   = $slots[0];
		$end     = $this->get_end_time( $field, end( $slots ) );

		$inserted = $wpdb->insert(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_ref'     => $ref,
				'field_id'        => $field_id,
				'customer_id'     => absint( $customer->id ),
				'booking_date'    => $date,
				'start_time'      => sanitize_text_field( $start ),
				'end_time'        => sanitize_text_field( $end ),
				'slots_count'     => $slot_count,
				'booked_slots'    => wp_json_encode( array_values( $slots ) ),
				'sport'           => $sport,
				'amount_total'    => $total,
				'amount_paid'     => 0.00,
				'amount_due'      => $total,
				'payment_mode'    => 'pending',
				'payment_status'  => 'pending',
				'booking_status'  => 'pending',
				'payment_gateway' => $gateway,
			],
			[ '%s', '%d', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%f', '%f', '%f', '%s', '%s', '%s', '%s' ]
		);

		if ( false === $inserted ) {
			return new WP_Error( 'db_error', __( 'Could not create booking.', 'playard' ) );
		}

		$booking_id = (int) $wpdb->insert_id;

		do_action( 'playard_booking_created', $booking_id, 'pending' );

		return [
			'booking_id'     => $booking_id,
			'booking_ref'    => $ref,
			'booking_status' => 'pending',
			'payment_status' => 'pending',
			'amount_total'   => $total,
			'field_name'     => (string) $field->name,
			'booking_date'   => $date,
			'start_time'     => $start,
			'end_time'       => $end,
			'sport'          => $sport,
			'slot_duration'  => (int) $field->slot_duration,
			'customer_id'    => (int) $customer->id,
		];
	}

	/**
	 * Confirms a pending booking after successful Stripe payment.
	 * Verifies the intent server-side, updates the booking row, logs the payment.
	 *
	 * @param  string $booking_ref
	 * @param  string $payment_intent_id
	 * @param  int    $customer_id  Optional — from JWT (verifies ownership)
	 * @return array<string,mixed>|WP_Error
	 */
	public function confirm_payment( string $booking_ref, string $payment_intent_id, int $customer_id = 0 ): array|WP_Error {
		global $wpdb;

		$where = [ 'booking_ref' => $booking_ref, 'booking_status' => 'pending' ];
		if ( $customer_id > 0 ) {
			$where['customer_id'] = $customer_id;
		}

		$booking = $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$wpdb->prefix}playard_bookings
			 WHERE booking_ref = %s AND booking_status = 'pending' LIMIT 1",
			$booking_ref
		) );

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Pending booking not found.', 'playard' ) );
		}

		if ( $customer_id > 0 && (int) $booking->customer_id !== $customer_id ) {
			return new WP_Error( 'unauthorized', __( 'Booking does not belong to this customer.', 'playard' ) );
		}

		$payment = new Playard_Payment();
		if ( ! $payment->verify_stripe_intent( $payment_intent_id ) ) {
			return new WP_Error( 'payment_failed', __( 'Payment verification failed.', 'playard' ) );
		}

		$total = (float) $booking->amount_total;

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_status'    => 'confirmed',
				'payment_status'    => 'paid',
				'payment_gateway'   => 'stripe',
				'payment_mode'      => 'full',
				'payment_intent_id' => sanitize_text_field( $payment_intent_id ),
				'amount_paid'       => $total,
				'amount_due'        => 0.00,
			],
			[ 'id' => (int) $booking->id ],
			[ '%s', '%s', '%s', '%s', '%s', '%f', '%f' ],
			[ '%d' ]
		);

		$payment->record_transaction( [
			'booking_id'  => (int) $booking->id,
			'amount'      => $total,
			'gateway'     => 'stripe',
			'gateway_ref' => $payment_intent_id,
			'type'        => 'payment',
			'status'      => 'success',
		] );

		do_action( 'playard_booking_confirmed', (int) $booking->id );
		do_action( 'playard_payment_received',  (int) $booking->id, $total );

		$field = playard_get_field( (int) $booking->field_id );

		return [
			'booking_ref'    => $booking->booking_ref,
			'booking_status' => 'confirmed',
			'payment_status' => 'paid',
			'amount_paid'    => $total,
			'amount_due'     => 0.00,
			'field_name'     => $field ? (string) $field->name : '',
			'booking_date'   => $booking->booking_date,
			'start_time'     => $booking->start_time,
			'end_time'       => $booking->end_time,
			'sport'          => $booking->sport,
			'customer_id'    => (int) $booking->customer_id,
		];
	}

	/**
	 * Abandons a pending booking (customer clicked Back from payment step).
	 * Verifies ownership via phone, only deletes status='pending' rows.
	 *
	 * @param  string $booking_ref
	 * @param  string $phone  Customer phone for lightweight ownership check
	 * @return true|WP_Error
	 */
	public function abandon_booking( string $booking_ref, string $phone ): bool|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row( $wpdb->prepare(
			"SELECT b.*, c.phone AS customer_phone
			 FROM {$wpdb->prefix}playard_bookings b
			 LEFT JOIN {$wpdb->prefix}playard_customers c ON c.id = b.customer_id
			 WHERE b.booking_ref = %s LIMIT 1",
			sanitize_text_field( $booking_ref )
		) );

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		if ( (string) $booking->booking_status !== 'pending' ) {
			return new WP_Error( 'not_pending', __( 'Only pending bookings can be abandoned.', 'playard' ) );
		}

		// Lightweight ownership check: phone must match or be omitted for logged-in flow.
		if ( ! empty( $phone ) && (string) $booking->customer_phone !== preg_replace( '/\D/', '', $phone ) ) {
			return new WP_Error( 'unauthorized', __( 'Phone number does not match.', 'playard' ) );
		}

		$wpdb->delete( $wpdb->prefix . 'playard_bookings', [ 'id' => (int) $booking->id ], [ '%d' ] );

		return true;
	}

	// ── Customer-facing queries ───────────────────────────────────────────────────

	/**
	 * Returns paginated bookings for a customer, with field name joined.
	 *
	 * @param string $status  'upcoming' | 'past' | 'all'
	 * @return array<string,mixed>
	 */
	public function get_customer_bookings(
		int $customer_id,
		string $status = 'all',
		int $page = 1,
		int $per_page = 10
	): array {
		global $wpdb;

		$where = 'b.customer_id = %d';
		$args  = [ $customer_id ];

		if ( $status === 'upcoming' ) {
			$where .= " AND b.booking_date >= %s AND b.booking_status IN ('confirmed','pending')";
			$args[] = playard_now( 'Y-m-d' );
		} elseif ( $status === 'past' ) {
			$where .= " AND (b.booking_date < %s OR b.booking_status IN ('completed','cancelled','no_show'))";
			$args[] = playard_now( 'Y-m-d' );
		}

		$offset = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where built from whitelisted conditions; safe
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->prefix}playard_bookings b WHERE {$where}",
				...$args
			)
		);

		$bookings = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT b.booking_ref, b.booking_date, b.start_time, b.end_time,
				        b.slots_count, b.booked_slots,
				        b.sport, b.amount_total, b.amount_paid, b.amount_due,
				        b.booking_status, b.payment_status, b.payment_mode, b.payment_gateway, b.created_at,
				        f.name AS field_name, f.slot_duration
				 FROM {$wpdb->prefix}playard_bookings b
				 LEFT JOIN {$wpdb->prefix}playard_fields f ON b.field_id = f.id
				 WHERE {$where}
				 ORDER BY b.booking_date DESC, b.start_time DESC
				 LIMIT %d OFFSET %d",
				...array_merge( $args, [ $per_page, $offset ] )
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		return [
			'bookings' => $bookings ?? [],
			'total'    => $total,
			'page'     => $page,
			'per_page' => $per_page,
		];
	}

	/**
	 * Cancels a booking via the customer portal (JWT auth).
	 * Enforces the cancellation policy and issues a Stripe refund when eligible.
	 *
	 * @return array<string,mixed>|WP_Error
	 */
	public function cancel_booking( string $booking_ref, int $customer_id ): array|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_ref = %s AND customer_id = %d LIMIT 1",
				$booking_ref,
				$customer_id
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		if ( in_array( $booking->booking_status, [ 'cancelled', 'completed' ], true ) ) {
			return new WP_Error(
				'already_closed',
				__( 'This booking cannot be cancelled.', 'playard' )
			);
		}

		if ( $booking->booking_date < playard_now( 'Y-m-d' ) ) {
			return new WP_Error(
				'past_booking',
				__( 'Cannot cancel a past booking.', 'playard' )
			);
		}

		// Cancellation policy check
		$settings = get_option( 'playard_settings', [] );
		$policy   = $settings['cancellation'] ?? [];

		if ( isset( $policy['allowed'] ) && ! $policy['allowed'] ) {
			return new WP_Error( 'not_allowed', __( 'Cancellation is not allowed for this booking.', 'playard' ) );
		}

		$refund_info = $this->is_refundable( $booking );

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_status' => 'cancelled',
				'cancelled_at'   => current_time( 'mysql' ),
			],
			[ 'booking_ref' => $booking_ref ],
			[ '%s', '%s' ],
			[ '%s' ]
		);

		// Decrement customer stats
		if ( (float) $booking->amount_paid > 0 ) {
			( new Playard_Customer() )->decrement_stats( (int) $booking->customer_id, (float) $booking->amount_paid );
		}

		// Process Stripe refund if eligible
		$refund_method = 'none';

		if ( $refund_info['refundable'] && (float) $booking->amount_paid > 0 ) {
			if ( 'stripe' === $booking->payment_gateway && ! empty( $booking->payment_intent_id ) ) {
				$stripe_result = ( new Playard_Payment() )->create_stripe_refund(
					(string) $booking->payment_intent_id,
					$refund_info['amount'],
					(int) $booking->id
				);

				if ( ! is_wp_error( $stripe_result ) ) {
					$wpdb->update(
						$wpdb->prefix . 'playard_bookings',
						[ 'payment_status' => 'refunded' ],
						[ 'id' => (int) $booking->id ],
						[ '%s' ], [ '%d' ]
					);
					$refund_method = 'stripe';
				}
			} else {
				// Pro gateways (PayPal, bKash, etc.) — delegate via filter
				$pro_result = apply_filters( 'playard_process_refund', [
					'amount' => $refund_info['amount'],
					'status' => 'pending',
				], $booking );

				if ( ( $pro_result['status'] ?? '' ) === 'refunded' ) {
					$wpdb->update(
						$wpdb->prefix . 'playard_bookings',
						[ 'payment_status' => 'refunded' ],
						[ 'id' => (int) $booking->id ],
						[ '%s' ], [ '%d' ]
					);
					$refund_method = 'refunded';
				}
			}
		}

		do_action( 'playard_booking_cancelled', (int) $booking->id, 'customer' );

		return [
			'booking_ref'    => $booking->booking_ref,
			'booking_status' => 'cancelled',
			'refund'         => [
				'eligible' => $refund_info['refundable'],
				'amount'   => $refund_info['amount'],
				'percent'  => $refund_info['percent'],
				'method'   => $refund_method,
			],
		];
	}

	// ── Mark as paid (cash bookings) ─────────────────────────────────────────────

	/**
	 * Marks a pending cash booking as paid.
	 * Called by admin when customer pays at the facility.
	 *
	 * @param  string     $booking_ref  Booking reference (PY-YYYY-XXXXX).
	 * @param  float|null $amount       Amount paid; null defaults to full amount_due.
	 * @return bool|WP_Error
	 */
	public function mark_as_paid( string $booking_ref, ?float $amount = null ): bool|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_bookings WHERE booking_ref = %s LIMIT 1",
				$booking_ref
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		if ( $booking->payment_gateway !== 'cash' ) {
			return new WP_Error( 'not_cash', __( 'Only cash bookings can be marked as paid here.', 'playard' ) );
		}

		if ( $booking->payment_status === 'paid' ) {
			return new WP_Error( 'already_paid', __( 'Booking is already marked as paid.', 'playard' ) );
		}

		$pay_amount = $amount ?? (float) $booking->amount_due;
		$new_paid   = (float) $booking->amount_paid + $pay_amount;
		$new_due    = max( 0.0, (float) $booking->amount_due - $pay_amount );

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[
				'amount_paid'    => $new_paid,
				'amount_due'     => $new_due,
				'payment_status' => 'paid',
				'booking_status' => 'confirmed',
			],
			[ 'booking_ref' => $booking_ref ],
			[ '%f', '%f', '%s', '%s' ],
			[ '%s' ]
		);

		$wpdb->insert(
			$wpdb->prefix . 'playard_payments',
			[
				'booking_id'  => (int) $booking->id,
				'amount'      => $pay_amount,
				'gateway'     => 'cash',
				'gateway_ref' => '',
				'type'        => 'payment',
				'status'      => 'success',
			],
			[ '%d', '%f', '%s', '%s', '%s', '%s' ]
		);

		do_action( 'playard_payment_received', (int) $booking->id, $pay_amount );

		return true;
	}

	/**
	 * Confirms a booking from Pro gateway payment data.
	 * Called by Pro gateways (PayPal, bKash, Razorpay) after successful charge.
	 * Finds the pending booking by ref, updates it to confirmed.
	 *
	 * @param  string             $booking_ref
	 * @param  array<string,mixed> $payment_data { gateway, gateway_ref, amount_paid, payment_intent_id }
	 * @return array{booking_ref:string,booking_id:int,booking_status:string,amount_paid:float}|WP_Error
	 */
	public function confirm_from_pending( string $booking_ref, array $payment_data ): array|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row( $wpdb->prepare(
			"SELECT * FROM {$wpdb->prefix}playard_bookings
			 WHERE booking_ref = %s AND booking_status = 'pending' LIMIT 1",
			sanitize_text_field( $booking_ref )
		) );

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Pending booking not found.', 'playard' ) );
		}

		$gateway           = sanitize_text_field( (string) ( $payment_data['gateway']           ?? '' ) );
		$gateway_ref       = sanitize_text_field( (string) ( $payment_data['gateway_ref']       ?? '' ) );
		$amount_paid       = (float)              ( $payment_data['amount_paid']       ?? (float) $booking->amount_total );
		$payment_intent_id = sanitize_text_field( (string) ( $payment_data['payment_intent_id'] ?? '' ) );
		$amount_due        = max( 0.0, round( (float) $booking->amount_total - $amount_paid, 2 ) );

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_status'    => 'confirmed',
				'payment_status'    => $amount_paid >= (float) $booking->amount_total ? 'paid' : 'partial',
				'payment_gateway'   => $gateway,
				'payment_intent_id' => $payment_intent_id,
				'amount_paid'       => $amount_paid,
				'amount_due'        => $amount_due,
			],
			[ 'id' => (int) $booking->id ],
			[ '%s', '%s', '%s', '%s', '%f', '%f' ],
			[ '%d' ]
		);

		$wpdb->insert(
			$wpdb->prefix . 'playard_payments',
			[
				'booking_id'  => (int) $booking->id,
				'amount'      => $amount_paid,
				'gateway'     => $gateway,
				'gateway_ref' => $gateway_ref,
				'type'        => 'payment',
				'status'      => 'success',
			],
			[ '%d', '%f', '%s', '%s', '%s', '%s' ]
		);

		do_action( 'playard_booking_confirmed', (int) $booking->id );

		return [
			'booking_ref'    => $booking->booking_ref,
			'booking_id'     => (int) $booking->id,
			'booking_status' => 'confirmed',
			'amount_paid'    => $amount_paid,
		];
	}

	// ── Helpers ───────────────────────────────────────────────────────────────────

	/**
	 * Calculates total price for a set of slot start times on a given date.
	 * Uses get_slot_price() so Pro peak/weekend pricing applies automatically.
	 *
	 * @param object   $field
	 * @param string   $date   Y-m-d
	 * @param string[] $slots  Array of HH:MM start times
	 */
	private function calculate_total( object $field, string $date, array $slots ): float {
		$slot_obj = new Playard_Slot();
		$total    = 0.0;

		foreach ( $slots as $start ) {
			$total += $slot_obj->get_slot_price( (int) $field->id, $date, (string) $start );
		}

		return round( $total, 2 );
	}

	/**
	 * Returns the end time (HH:MM:SS) for the last slot in a booking.
	 * End = last slot start + slot_duration minutes.
	 *
	 * @param object $field
	 * @param string $last_start  HH:MM
	 */
	// ── Admin list ───────────────────────────────────────────────────────────────

	/**
	 * Returns a paginated, filtered list of bookings joined with field and customer data.
	 *
	 * @param array<string,mixed> $args Supported: field_id, date_from, date_to,
	 *                                  status, payment_status, sport, search,
	 *                                  sort, order, page, per_page.
	 * @return array{ bookings: list<object>, total: int, page: int, per_page: int }
	 */
	public function get_bookings( array $args ): array {
		global $wpdb;

		$where  = [ '1=1' ];
		$params = [];

		if ( ! empty( $args['field_id'] ) ) {
			$where[]  = 'b.field_id = %d';
			$params[] = absint( $args['field_id'] );
		}
		if ( ! empty( $args['date_from'] ) ) {
			$where[]  = 'b.booking_date >= %s';
			$params[] = sanitize_text_field( (string) $args['date_from'] );
		}
		if ( ! empty( $args['date_to'] ) ) {
			$where[]  = 'b.booking_date <= %s';
			$params[] = sanitize_text_field( (string) $args['date_to'] );
		}
		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'b.booking_status = %s';
			$params[] = sanitize_text_field( (string) $args['status'] );
		}
		if ( ! empty( $args['payment_status'] ) ) {
			$where[]  = 'b.payment_status = %s';
			$params[] = sanitize_text_field( (string) $args['payment_status'] );
		}
		if ( ! empty( $args['sport'] ) ) {
			$where[]  = 'b.sport = %s';
			$params[] = sanitize_text_field( (string) $args['sport'] );
		}
		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( sanitize_text_field( (string) $args['search'] ) ) . '%';
			$where[]  = '(b.booking_ref LIKE %s OR c.name LIKE %s OR c.phone LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		$where_sql = implode( ' AND ', $where );

		$sort_col_map = [
			'booking_date'   => 'b.booking_date',
			'created_at'     => 'b.created_at',
			'amount_total'   => 'b.amount_total',
			'start_time'     => 'b.start_time',
			'booking_status' => 'b.booking_status',
			'payment_status' => 'b.payment_status',
			'sport'          => 'b.sport',
			'field_name'     => 'f.name',
			'customer_name'  => 'c.name',
		];
		$sort_key = (string) ( $args['sort'] ?? 'booking_date' );
		$sort_col = $sort_col_map[ $sort_key ] ?? 'b.booking_date';
		$order    = strtoupper( (string) ( $args['order'] ?? 'DESC' ) ) === 'ASC' ? 'ASC' : 'DESC';
		$page     = max( 1, absint( $args['page'] ?? 1 ) );
		$per_page = min( 100, max( 1, absint( $args['per_page'] ?? 20 ) ) );
		$offset   = ( $page - 1 ) * $per_page;

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $where_sql/$sort_col built from whitelisted maps; safe
		$count_base = "SELECT COUNT(*) FROM {$wpdb->prefix}playard_bookings b
		               LEFT JOIN {$wpdb->prefix}playard_customers c ON b.customer_id = c.id
		               WHERE $where_sql";

		$total = (int) $wpdb->get_var(
			empty( $params )
				? $count_base
				: $wpdb->prepare( $count_base, ...$params )
		);

		$data_base = "SELECT b.*, f.name AS field_name,
		                     c.name  AS customer_name,
		                     c.phone AS customer_phone
		              FROM {$wpdb->prefix}playard_bookings b
		              LEFT JOIN {$wpdb->prefix}playard_fields f    ON b.field_id    = f.id
		              LEFT JOIN {$wpdb->prefix}playard_customers c ON b.customer_id = c.id
		              WHERE $where_sql
		              ORDER BY {$sort_col} {$order}
		              LIMIT %d OFFSET %d";

		$data_params = array_merge( $params, [ $per_page, $offset ] );

		$rows = $wpdb->get_results( $wpdb->prepare( $data_base, ...$data_params ) );
		// phpcs:enable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		return [
			'bookings' => $rows ?? [],
			'total'    => $total,
			'page'     => $page,
			'per_page' => $per_page,
		];
	}

	// ── Admin cancel booking ─────────────────────────────────────────────────

	/**
	 * Cancels a booking from the admin dashboard.
	 * Slot becomes available immediately. Fires hooks for email notifications.
	 *
	 * @param  string $ref    Booking reference (PY-YYYY-XXXXX).
	 * @param  string $reason Optional cancellation reason.
	 * @return array<string,mixed>|WP_Error
	 */
	public function admin_cancel_booking( string $ref, string $reason = '' ): array|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_bookings WHERE booking_ref = %s LIMIT 1",
				sanitize_text_field( $ref )
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		if ( $booking->booking_status === 'cancelled' ) {
			return new WP_Error( 'already_cancelled', __( 'This booking is already cancelled.', 'playard' ) );
		}

		if ( $booking->booking_status === 'completed' ) {
			return new WP_Error( 'completed', __( 'Cannot cancel a completed booking.', 'playard' ) );
		}

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_status'      => 'cancelled',
				'cancelled_at'        => current_time( 'mysql' ),
				'cancellation_reason' => sanitize_textarea_field( $reason ),
			],
			[ 'id' => (int) $booking->id ],
			[ '%s', '%s', '%s' ],
			[ '%d' ]
		);

		// Process Stripe refund automatically if payment was made via Stripe.
		$refund_amount = 0.0;
		$refund_status = 'none';

		if ( (float) $booking->amount_paid > 0 && 'stripe' === $booking->payment_gateway && ! empty( $booking->payment_intent_id ) ) {
			$refund_info = $this->is_refundable( $booking );

			if ( $refund_info['refundable'] && $refund_info['amount'] > 0 ) {
				$stripe_result = ( new Playard_Payment() )->create_stripe_refund(
					(string) $booking->payment_intent_id,
					$refund_info['amount'],
					(int) $booking->id
				);

				if ( ! is_wp_error( $stripe_result ) ) {
					$wpdb->update(
						$wpdb->prefix . 'playard_bookings',
						[ 'payment_status' => 'refunded' ],
						[ 'id' => (int) $booking->id ],
						[ '%s' ], [ '%d' ]
					);
					$refund_amount = $refund_info['amount'];
					$refund_status = 'stripe';
				} else {
					$refund_status = 'failed';
				}
			}
		} else {
			// Non-Stripe gateways — delegate via filter (Pro extensibility).
			$pro_result    = apply_filters( 'playard_process_refund', [ 'amount' => 0, 'status' => 'none' ], $booking );
			$refund_amount = (float) ( $pro_result['amount'] ?? 0 );
			$refund_status = (string) ( $pro_result['status'] ?? 'none' );
		}

		// Decrement customer stats only after refund outcome is known.
		// This prevents a stat mismatch when the Stripe refund call fails.
		if ( (float) $booking->amount_paid > 0 ) {
			( new Playard_Customer() )->decrement_stats(
				(int) $booking->customer_id,
				(float) $booking->amount_paid
			);
		}

		do_action( 'playard_booking_cancelled', (int) $booking->id, 'admin' );

		return [
			'booking_ref'    => $booking->booking_ref,
			'booking_status' => 'cancelled',
			'refund_amount'  => $refund_amount,
			'refund_status'  => $refund_status,
		];
	}

	// ── Admin walk-in booking ─────────────────────────────────────────────────

	/**
	 * Creates a walk-in booking from the admin dashboard.
	 * Slot availability is checked server-side. Customer is auto-created by phone.
	 * Walk-in bookings are always status=confirmed immediately.
	 *
	 * @param  array<string,mixed> $data
	 * @return array<string,mixed>|WP_Error
	 */
	public function admin_create_booking( array $data ): array|WP_Error {
		global $wpdb;

		$field_id = absint( $data['field_id'] );
		$field    = playard_get_field( $field_id );

		if ( ! $field || $field->status !== 'active' ) {
			return new WP_Error( 'invalid_field', __( 'Field not found or inactive.', 'playard' ) );
		}

		$date  = sanitize_text_field( (string) ( $data['booking_date'] ?? '' ) );
		$slots = array_values( array_filter( (array) ( $data['slots'] ?? [] ) ) );
		$sport = sanitize_text_field( (string) ( $data['sport'] ?? '' ) );

		if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ) {
			return new WP_Error( 'invalid_date', __( 'Invalid booking date.', 'playard' ) );
		}

		if ( $date < playard_now( 'Y-m-d' ) ) {
			return new WP_Error( 'past_date', __( 'Cannot create bookings for past dates.', 'playard' ) );
		}

		if ( empty( $slots ) ) {
			return new WP_Error( 'no_slots', __( 'Please select at least one time slot.', 'playard' ) );
		}

		foreach ( $slots as $start ) {
			$end = $this->get_end_time( $field, (string) $start );
			if ( $this->is_slot_taken( $field_id, $date, (string) $start, $end ) ) {
				return new WP_Error(
					'slot_unavailable',
					// translators: %s is the time slot (e.g. "09:00")
					sprintf( __( 'Slot %s is no longer available.', 'playard' ), $start )
				);
			}
		}

		$customer       = playard_get_or_create_customer( (array) ( $data['customer'] ?? [] ) );
		$total          = $this->calculate_total( $field, $date, $slots );
		$pay_mode       = sanitize_text_field( (string) ( $data['payment_mode'] ?? 'cash' ) );
		$already_paid   = $pay_mode === 'paid';
		$is_stripe      = $pay_mode === 'stripe';
		$payment_status = $already_paid ? 'paid' : 'pending';
		$gateway        = $is_stripe ? 'stripe' : 'cash';
		$ref            = playard_generate_booking_ref();

		sort( $slots );

		$insert_ok = $wpdb->insert(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_ref'     => $ref,
				'field_id'        => $field_id,
				'customer_id'     => (int) $customer->id,
				'booking_date'    => $date,
				'start_time'      => sanitize_text_field( $slots[0] ),
				'end_time'        => $this->get_end_time( $field, end( $slots ) ),
				'slots_count'     => count( $slots ),
				'booked_slots'    => wp_json_encode( array_values( $slots ) ),
				'sport'           => $sport,
				'amount_total'    => $total,
				'amount_paid'     => $already_paid ? $total : 0.00,
				'amount_due'      => $already_paid ? 0.00 : $total,
				'payment_mode'    => $is_stripe ? 'full' : 'cash',
				'payment_status'  => $payment_status,
				'booking_status'  => 'confirmed',
				'payment_gateway' => $gateway,
				'admin_notes'     => sanitize_textarea_field( (string) ( $data['admin_notes'] ?? '' ) ),
			],
			[ '%s', '%d', '%d', '%s', '%s', '%s', '%d', '%s', '%s', '%f', '%f', '%f', '%s', '%s', '%s', '%s', '%s' ]
		);

		if ( false === $insert_ok ) {
			return new WP_Error( 'db_error', __( 'Could not create booking.', 'playard' ) );
		}

		$booking_id = (int) $wpdb->insert_id;

		if ( $already_paid ) {
			$wpdb->insert(
				$wpdb->prefix . 'playard_payments',
				[
					'booking_id'  => $booking_id,
					'amount'      => $total,
					'gateway'     => 'cash',
					'gateway_ref' => '',
					'type'        => 'payment',
					'status'      => 'success',
				],
				[ '%d', '%f', '%s', '%s', '%s', '%s' ]
			);
		}

		// For Stripe walk-ins: skip confirmation email (payment link email is sent instead).
		// For cash/paid: fire playard_booking_confirmed to trigger confirmation email.
		if ( ! $is_stripe ) {
			do_action( 'playard_booking_confirmed', $booking_id );
		}
		do_action( 'playard_walkin_booking_created', $booking_id );

		// If Stripe selected, create a hosted Checkout Session link to share with the customer.
		$checkout_url = '';
		if ( $is_stripe ) {
			$booking_row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}playard_bookings WHERE id = %d", $booking_id ) );
			if ( $booking_row ) {
				$checkout = ( new Playard_Payment() )->create_stripe_checkout_session(
					$booking_row,
					esc_url_raw( playard_get_portal_url() )
				);
				if ( ! is_wp_error( $checkout ) ) {
					$checkout_url = $checkout['checkout_url'];
					if ( ! empty( $checkout['payment_intent_id'] ) ) {
						$wpdb->update(
							$wpdb->prefix . 'playard_bookings',
							[ 'payment_intent_id' => $checkout['payment_intent_id'] ],
							[ 'id' => $booking_id ],
							[ '%s' ],
							[ '%d' ]
						);
					}
				}
				// Send the payment link to the customer's email (if they have one).
				$customer_email = isset( $customer->email ) ? (string) $customer->email : '';
				do_action( 'playard_walkin_payment_link_created', $booking_id, $checkout_url, $customer_email );
			}
		}

		return [
			'booking_ref'    => $ref,
			'booking_status' => 'confirmed',
			'checkout_url'   => $checkout_url,
		];
	}

	/**
	 * Returns true if a confirmed or pending booking already occupies the given slot.
	 * Uses individual booked_slots when available to avoid false-positives with non-consecutive bookings.
	 */
	protected function is_slot_taken( int $field_id, string $date, string $start, string $end ): bool {
		global $wpdb;

		$bookings = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT start_time, end_time, booked_slots FROM {$wpdb->prefix}playard_bookings
				 WHERE field_id = %d AND booking_date = %s
				   AND booking_status IN ('confirmed','pending','completed')",
				$field_id, $date
			)
		) ?: [];

		$req_start = substr( $start, 0, 5 );

		foreach ( $bookings as $booking ) {
			$individual = ! empty( $booking->booked_slots )
				? json_decode( (string) $booking->booked_slots, true )
				: null;

			if ( is_array( $individual ) && count( $individual ) > 0 ) {
				foreach ( $individual as $b_slot_start ) {
					if ( substr( (string) $b_slot_start, 0, 5 ) === $req_start ) {
						return true;
					}
				}
			} else {
				$b_start = substr( (string) $booking->start_time, 0, 5 );
				$b_end   = substr( (string) $booking->end_time,   0, 5 );
				if ( $start < $b_end && $end > $b_start ) {
					return true;
				}
			}
		}

		return false;
	}

	/**
	 * Reschedules a booking to a new date and/or time.
	 * Excludes the current booking from the availability check so the same slot can be re-selected.
	 *
	 * @param  string              $ref  Booking reference.
	 * @param  array<string,mixed> $data Keys: booking_date (Y-m-d), slots (string[] of HH:MM starts).
	 * @return array<string,mixed>|WP_Error
	 */
	public function reschedule_booking( string $ref, array $data ): array|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_bookings WHERE booking_ref = %s LIMIT 1",
				sanitize_text_field( $ref )
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		if ( in_array( $booking->booking_status, [ 'cancelled', 'completed' ], true ) ) {
			return new WP_Error( 'invalid_status', __( 'Cannot reschedule this booking.', 'playard' ) );
		}

		$new_date  = sanitize_text_field( (string) ( $data['booking_date'] ?? '' ) );
		$new_slots = array_map( 'sanitize_text_field', (array) ( $data['slots'] ?? [] ) );

		if ( empty( $new_date ) || empty( $new_slots ) ) {
			return new WP_Error( 'missing_data', __( 'Date and slots are required.', 'playard' ) );
		}

		$field = playard_get_field( (int) $booking->field_id );
		if ( ! $field ) {
			return new WP_Error( 'field_not_found', __( 'Field not found.', 'playard' ) );
		}

		$slot_obj      = new Playard_Slot();
		$slot_duration = absint( $field->slot_duration );

		// Check each requested slot against all OTHER bookings (exclude self by id).
		$existing = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT start_time, end_time, booked_slots FROM {$wpdb->prefix}playard_bookings
				 WHERE field_id = %d AND booking_date = %s
				   AND booking_status IN ('confirmed','pending','completed')
				   AND id != %d",
				(int) $field->id, $new_date, (int) $booking->id
			)
		) ?: [];

		foreach ( $new_slots as $start ) {
			$req_start = substr( (string) $start, 0, 5 );
			$end       = $slot_obj->calculate_end_time( (string) $start, $slot_duration );

			foreach ( $existing as $ex ) {
				$individual = ! empty( $ex->booked_slots )
					? json_decode( (string) $ex->booked_slots, true )
					: null;

				if ( is_array( $individual ) && count( $individual ) > 0 ) {
					foreach ( $individual as $bs ) {
						if ( substr( (string) $bs, 0, 5 ) === $req_start ) {
							return new WP_Error(
								'slot_unavailable',
								/* translators: %s: slot start time */
								sprintf( __( 'Slot %s is not available on the new date.', 'playard' ), $start )
							);
						}
					}
				} else {
					$b_start = substr( (string) $ex->start_time, 0, 5 );
					$b_end   = substr( (string) $ex->end_time,   0, 5 );
					if ( $start < $b_end && $end > $b_start ) {
						return new WP_Error(
							'slot_unavailable',
							/* translators: %s: slot start time */
							sprintf( __( 'Slot %s is not available on the new date.', 'playard' ), $start )
						);
					}
				}
			}
		}

		sort( $new_slots );
		$last_slot_end = $slot_obj->calculate_end_time( end( $new_slots ), $slot_duration );
		$old_date      = (string) $booking->booking_date;
		$old_time      = $booking->start_time . '–' . $booking->end_time;

		$old_total = (float) $booking->amount_total;
		$new_total = $this->calculate_total( $field, $new_date, $new_slots );
		$new_due   = max( 0.0, $new_total - (float) $booking->amount_paid );

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_date' => $new_date,
				'start_time'   => $new_slots[0],
				'end_time'     => $last_slot_end,
				'slots_count'  => count( $new_slots ),
				'booked_slots' => wp_json_encode( array_values( $new_slots ) ),
				'amount_total' => $new_total,
				'amount_due'   => $new_due,
				'updated_at'   => current_time( 'mysql' ),
			],
			[ 'id' => (int) $booking->id ],
			[ '%s', '%s', '%s', '%d', '%s', '%f', '%f', '%s' ],
			[ '%d' ]
		);

		// Adjust customer total_spent when the rescheduled price differs from the original.
		$diff = round( $new_total - $old_total, 2 );
		if ( $diff > 0 ) {
			( new Playard_Customer() )->increment_stats( (int) $booking->customer_id, $diff );
		} elseif ( $diff < 0 ) {
			( new Playard_Customer() )->decrement_stats( (int) $booking->customer_id, abs( $diff ) );
		}

		$new_time = $new_slots[0] . '–' . $last_slot_end;

		do_action( 'playard_booking_rescheduled', (int) $booking->id, [
			'old_date' => $old_date,
			'old_time' => $old_time,
			'new_date' => $new_date,
			'new_time' => $new_time,
		] );

		return [
			'booking_ref' => (string) $booking->booking_ref,
			'old_date'    => $old_date,
			'old_time'    => $old_time,
			'new_date'    => $new_date,
			'new_time'    => $new_time,
		];
	}

	/**
	 * Saves admin-only internal notes on a booking.
	 *
	 * @param  string $ref   Booking reference.
	 * @param  string $notes Notes content (max 1000 chars, already sanitized by caller).
	 * @return true|WP_Error
	 */
	public function update_admin_notes( string $ref, string $notes ): bool|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}playard_bookings WHERE booking_ref = %s LIMIT 1",
				sanitize_text_field( $ref )
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		$result = $wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[ 'admin_notes' => sanitize_textarea_field( mb_substr( $notes, 0, 1000 ) ) ],
			[ 'id' => (int) $booking->id ],
			[ '%s' ],
			[ '%d' ]
		);

		return $result !== false;
	}

	/**
	 * Marks a booking as completed.
	 *
	 * @param  string $ref Booking reference.
	 * @return true|WP_Error
	 */
	public function mark_completed( string $ref ): bool|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_bookings WHERE booking_ref = %s LIMIT 1",
				sanitize_text_field( $ref )
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		if ( $booking->booking_status === 'completed' ) {
			return new WP_Error( 'already_completed', __( 'Already completed.', 'playard' ) );
		}

		if ( $booking->booking_status === 'cancelled' ) {
			return new WP_Error( 'cancelled', __( 'Cannot complete a cancelled booking.', 'playard' ) );
		}

		if ( $booking->booking_date > playard_now( 'Y-m-d' ) ) {
			return new WP_Error(
				'future_booking',
				__( 'Cannot mark a future booking as completed. The slot has not been played yet.', 'playard' )
			);
		}

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[ 'booking_status' => 'completed' ],
			[ 'id' => (int) $booking->id ],
			[ '%s' ],
			[ '%d' ]
		);

		do_action( 'playard_booking_completed', (int) $booking->id );
		do_action( 'playard_booking_status_changed', (int) $booking->id, (string) $booking->booking_status, 'completed' );

		return true;
	}

	/**
	 * Marks a booking as no-show (customer booked but did not arrive).
	 *
	 * @param  string $ref Booking reference.
	 * @return true|WP_Error
	 */
	public function mark_no_show( string $ref ): bool|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_bookings WHERE booking_ref = %s LIMIT 1",
				sanitize_text_field( $ref )
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		if ( $booking->booking_status === 'no_show' ) {
			return new WP_Error( 'already_no_show', __( 'Already marked as no-show.', 'playard' ) );
		}

		if ( $booking->booking_status === 'cancelled' ) {
			return new WP_Error( 'cancelled', __( 'Cannot mark a cancelled booking as no-show.', 'playard' ) );
		}

		if ( $booking->booking_date > playard_now( 'Y-m-d' ) ) {
			return new WP_Error( 'future_booking', __( 'Cannot mark a future booking as no-show.', 'playard' ) );
		}

		// For today's bookings, the slot must have already ended.
		if ( $booking->booking_date === playard_now( 'Y-m-d' ) ) {
			$now_time = playard_now( 'H:i' );
			if ( substr( (string) $booking->end_time, 0, 5 ) > $now_time ) {
				return new WP_Error( 'slot_not_ended', __( 'Cannot mark as no-show before the slot has ended.', 'playard' ) );
			}
		}

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[ 'booking_status' => 'no_show' ],
			[ 'id' => (int) $booking->id ],
			[ '%s' ],
			[ '%d' ]
		);

		do_action( 'playard_booking_no_show', (int) $booking->id );
		do_action( 'playard_booking_status_changed', (int) $booking->id, (string) $booking->booking_status, 'no_show' );

		return true;
	}

	/**
	 * Auto-completes all confirmed/pending bookings whose slot time has passed.
	 * Runs via WP-Cron every 5 minutes.
	 */
	public static function auto_complete_past_bookings(): void {
		global $wpdb;

		$today = playard_now( 'Y-m-d' );
		$now   = playard_now( 'H:i:s' );

		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}playard_bookings
				 WHERE booking_status IN ('confirmed','pending')
				 AND (
				     booking_date < %s
				     OR (booking_date = %s AND end_time <= %s)
				 )",
				$today, $today, $now
			)
		);

		foreach ( $ids as $id ) {
			$wpdb->update(
				$wpdb->prefix . 'playard_bookings',
				[ 'booking_status' => 'completed' ],
				[ 'id' => (int) $id ],
				[ '%s' ],
				[ '%d' ]
			);
			do_action( 'playard_booking_completed', (int) $id );
		}
	}

	private function get_end_time( object $field, string $last_start ): string {
		$duration = absint( $field->slot_duration ?? 60 );
		$ts       = strtotime( $last_start . ':00' );

		if ( $ts === false ) {
			return '00:00:00';
		}

		return gmdate( 'H:i:s', $ts + $duration * 60 );
	}

	// ── Cancellation policy ───────────────────────────────────────────────────────

	/**
	 * Checks whether a booking is eligible for a refund based on the facility
	 * cancellation policy stored in playard_settings.
	 *
	 * @param  object $booking  Full booking DB row.
	 * @return array{ refundable: bool, amount: float, percent: int, reason: string }
	 */
	public function is_refundable( object $booking ): array {
		$settings = get_option( 'playard_settings', [] );
		$policy   = $settings['cancellation'] ?? [];

		if ( empty( $policy['allowed'] ) ) {
			return [ 'refundable' => false, 'amount' => 0.0, 'percent' => 0, 'reason' => 'Cancellation not allowed' ];
		}

		$refund_type = $policy['refund_type'] ?? 'time_based';

		if ( $refund_type === 'none' ) {
			return [ 'refundable' => false, 'amount' => 0.0, 'percent' => 0, 'reason' => 'No refund policy' ];
		}

		if ( $refund_type === 'full' ) {
			return [
				'refundable' => true,
				'amount'     => (float) $booking->amount_paid,
				'percent'    => 100,
				'reason'     => 'Full refund policy',
			];
		}

		// Time-based — parse booking start in facility timezone so the window is calculated correctly.
		$window_hours = (int) ( $policy['refund_window'] ?? 24 );
		$percent      = (int) ( $policy['refund_percent'] ?? 100 );
		$ftz          = playard_facility_tz();
		$booking_dt   = DateTime::createFromFormat(
			'Y-m-d H:i:s',
			$booking->booking_date . ' ' . substr( (string) $booking->start_time, 0, 8 ),
			$ftz
		);
		$now_dt      = new DateTime( 'now', $ftz );
		$hours_until = $booking_dt !== false
			? ( $booking_dt->getTimestamp() - $now_dt->getTimestamp() ) / 3600
			: -1;

		if ( $hours_until >= $window_hours ) {
			return [
				'refundable' => true,
				'amount'     => round( (float) $booking->amount_paid * $percent / 100, 2 ),
				'percent'    => $percent,
				'reason'     => sprintf( 'Cancelled within %d-hour window', $window_hours ),
			];
		}

		return [
			'refundable' => false,
			'amount'     => 0.0,
			'percent'    => 0,
			'reason'     => sprintf( 'Must cancel at least %d hours before', $window_hours ),
		];
	}

	// ── Customer cancel (token-based, from email link) ────────────────────────────

	/**
	 * Cancels a booking using the secure token sent in the confirmation email.
	 *
	 * @param  string $ref    Booking reference.
	 * @param  string $token  wp_hash( booking_ref . customer_id ).
	 * @param  string $reason Optional customer reason.
	 * @return array|WP_Error
	 */
	public function customer_cancel_booking( string $ref, string $token, string $reason = '' ): array|WP_Error {
		global $wpdb;

		$booking = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}playard_bookings WHERE booking_ref = %s LIMIT 1",
				sanitize_text_field( $ref )
			)
		);

		if ( ! $booking ) {
			return new WP_Error( 'not_found', __( 'Booking not found.', 'playard' ) );
		}

		// Timing-safe token check — token = wp_hash( ref . customer_id )
		$expected = wp_hash( $booking->booking_ref . $booking->customer_id );
		if ( ! hash_equals( $expected, $token ) ) {
			return new WP_Error( 'invalid_token', __( 'Invalid cancellation link.', 'playard' ) );
		}

		if ( $booking->booking_status === 'cancelled' ) {
			return new WP_Error( 'already_cancelled', __( 'This booking is already cancelled.', 'playard' ) );
		}

		if ( $booking->booking_status === 'completed' ) {
			return new WP_Error( 'completed', __( 'Cannot cancel a completed booking.', 'playard' ) );
		}

		$settings = get_option( 'playard_settings', [] );
		$policy   = $settings['cancellation'] ?? [];
		if ( isset( $policy['allowed'] ) && ! $policy['allowed'] ) {
			return new WP_Error( 'not_allowed', __( 'Cancellation is not allowed for this booking.', 'playard' ) );
		}

		$refund_info = $this->is_refundable( $booking );

		$wpdb->update(
			$wpdb->prefix . 'playard_bookings',
			[
				'booking_status'      => 'cancelled',
				'amount_due'          => 0.00,
				'cancelled_at'        => current_time( 'mysql' ),
				'cancellation_reason' => sanitize_textarea_field( $reason ),
			],
			[ 'id' => (int) $booking->id ],
			[ '%s', '%f', '%s', '%s' ],
			[ '%d' ]
		);

		if ( (float) $booking->amount_paid > 0 ) {
			( new Playard_Customer() )->decrement_stats( (int) $booking->customer_id, (float) $booking->amount_paid );
		}

		$refund_method = 'none';

		if ( $refund_info['refundable'] && (float) $booking->amount_paid > 0 ) {
			if ( 'stripe' === $booking->payment_gateway && ! empty( $booking->payment_intent_id ) ) {
				// Automatic Stripe refund
				$stripe_result = ( new Playard_Payment() )->create_stripe_refund(
					(string) $booking->payment_intent_id,
					$refund_info['amount'],
					(int) $booking->id
				);

				if ( ! is_wp_error( $stripe_result ) ) {
					$wpdb->update(
						$wpdb->prefix . 'playard_bookings',
						[ 'payment_status' => 'refunded' ],
						[ 'id' => (int) $booking->id ],
						[ '%s' ], [ '%d' ]
					);
					$refund_method = 'stripe';
				}
			} elseif ( 'cash' !== $booking->payment_gateway ) {
				// Pro gateways (PayPal, bKash, etc.) — delegate via filter
				$pro_result = apply_filters( 'playard_process_refund', [
					'amount' => $refund_info['amount'],
					'status' => 'pending',
				], $booking );

				if ( ( $pro_result['status'] ?? '' ) === 'refunded' ) {
					$wpdb->update(
						$wpdb->prefix . 'playard_bookings',
						[ 'payment_status' => 'refunded' ],
						[ 'id' => (int) $booking->id ],
						[ '%s' ], [ '%d' ]
					);
					$refund_method = 'refunded';
				}
			}
			// Cash: owner refunds manually — no automatic processing
		}

		do_action( 'playard_booking_cancelled', (int) $booking->id, 'customer' );

		return [
			'booking_ref'    => $booking->booking_ref,
			'booking_status' => 'cancelled',
			'refund'         => [
				'eligible' => $refund_info['refundable'],
				'amount'   => $refund_info['amount'],
				'percent'  => $refund_info['percent'],
				'method'   => $refund_method,
			],
		];
	}

	/**
	 * Finds all bookings that overlap another booking on the same field+date
	 * (duplicate / double-booked slots) and cancels all but the earliest one.
	 *
	 * Returns the number of bookings cancelled.
	 */
	public function cancel_duplicate_bookings(): int {
		global $wpdb;

		// Find every pair (a, b) where a.id < b.id and they overlap on the same field+date.
		// We collect the IDs of the LATER duplicates (b.id) to cancel.
		$duplicate_ids = $wpdb->get_col(
			"SELECT b.id
			 FROM {$wpdb->prefix}playard_bookings a
			 JOIN {$wpdb->prefix}playard_bookings b
			   ON  b.field_id      = a.field_id
			   AND b.booking_date  = a.booking_date
			   AND b.id            > a.id
			   AND b.start_time    < a.end_time
			   AND b.end_time      > a.start_time
			   AND b.booking_status NOT IN ('cancelled')
			   AND a.booking_status NOT IN ('cancelled')"
		);

		if ( empty( $duplicate_ids ) ) {
			return 0;
		}

		$now      = current_time( 'mysql' );
		$ids_in   = implode( ',', array_map( 'absint', $duplicate_ids ) );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $now from current_time(); $ids_in is array_map('absint',...); both safe
		$cancelled = $wpdb->query(
			"UPDATE {$wpdb->prefix}playard_bookings
			 SET booking_status = 'cancelled',
			     cancellation_reason = 'Duplicate booking — cancelled automatically',
			     cancelled_at = '$now'
			 WHERE id IN ($ids_in)
			   AND booking_status NOT IN ('cancelled')"
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

		return (int) $cancelled;
	}
}
