<?php
// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

/**
 * WC Appointment Data Store: Stored in CPT.
 *
 * @todo When 2.6 support is dropped, implement WC_Object_Data_Store_Interface
 */
class WC_Appointment_Data_Store extends WC_Data_Store_WP {

	public const CACHE_GROUP = 'wc-appointments';

	/**
     * Meta keys and how they transfer to CRUD props.
     */
    private array $appointment_meta_key_to_props = [
		'_appointment_all_day'                  => 'all_day',
		'_appointment_cost'                     => 'cost',
		'_appointment_customer_id'              => 'customer_id',
		'_appointment_order_item_id'            => 'order_item_id',
		'_appointment_parent_id'                => 'parent_id',
		'_appointment_product_id'               => 'product_id',
		'_appointment_staff_id'                 => 'staff_ids',
		'_appointment_start'                    => 'start',
		'_appointment_end'                      => 'end',
		'_wc_appointments_gcal_event_id'        => 'google_calendar_event_id',
		'_wc_appointments_gcal_staff_event_ids' => 'google_calendar_staff_event_ids',
		'_appointment_customer_status'          => 'customer_status',
		'_appointment_qty'                      => 'qty',
		'_appointment_timezone'                 => 'timezone',
		'_local_timezone'                       => 'local_timezone',
	];

	/*
	|--------------------------------------------------------------------------
	| CRUD Methods
	|--------------------------------------------------------------------------
	*/

	/**
	 * Create a new appointment in the database.
	 *
	 * Inserts a new appointment record and saves all associated metadata.
	 *
	 * @param WC_Appointment $appointment The appointment object to create.
	 */
	public function create( &$appointment ): void {
		if ( ! $appointment->get_date_created( 'edit' ) ) {
			$appointment->set_date_created( current_time( 'timestamp' ) );
		}

		// @codingStandardsIgnoreStart
		$id = wp_insert_post(
		    apply_filters(
		        'woocommerce_new_appointment_data',
		        [
					'post_date'     => date(
					    'Y-m-d H:i:s',
					    $appointment->get_date_created( 'edit' ),
					),
					'post_date_gmt' => get_gmt_from_date(
					    date(
					        'Y-m-d H:i:s',
					        $appointment->get_date_created( 'edit' ),
					    ),
					),
					'post_type'     => 'wc_appointment',
					'post_status'   => $appointment->get_status( 'edit' ),
					'post_author'   => $appointment->get_customer_id( 'edit' ),
					'post_title'    => sprintf(
						/* translators: %s: Appointment date */
						__( 'Appointment &ndash; %s', 'woocommerce-appointments' ),
					    date(
							/* translators: Appointment date format parsed by date() function */
							_x( 'M d, Y @ H:i a', 'Appointment date parsed by date()', 'woocommerce-appointments' ),
					        $appointment->get_date_created( 'edit' ),
					    ),
					),
					'post_parent'   => $appointment->get_order_id( 'edit' ),
					'ping_status'   => 'closed',
				],
		        $appointment,
		    ),
		    true,
		);
		// @codingStandardsIgnoreEnd

		if ( $id && ! is_wp_error( $id ) ) {
			$appointment->set_id( $id );
			$this->update_post_meta( $appointment );
			$appointment->save_meta_data();
			$appointment->apply_changes();
			WC_Cache_Helper::get_transient_version( 'appointments', true );

			do_action( 'woocommerce_new_appointment', $appointment->get_id() );
		}

		// Stop deleting the transient if the product is added to the cart.
		// Action Scheduler will be used to update new availability.
		// Add some meta to track that this product requires updating.
		$product_id = filter_input( INPUT_POST, 'add-to-cart', FILTER_SANITIZE_NUMBER_INT );
		$min_date   = isset( $_POST['min_date'] ) ? strtotime( wc_clean( wp_unslash( $_POST['min_date'] ) ) ) : '';
		$max_date   = isset( $_POST['max_date'] ) ? strtotime( wc_clean( wp_unslash( $_POST['max_date'] ) ) ) : '';

		// If $min_date or $max_date are somehow was not updated by the JS, stop and clear transient.
		if ( $product_id && $min_date && $max_date ) {
			$product_or_staff_id = $appointment->has_staff() ? $appointment->get_staff_ids() : $product_id;
			$timezone_offset     = isset( $_GET['timezone_offset'] ) ? wc_clean( wp_unslash( $_GET['timezone_offset'] ) ) : 0;

			/**
			 * Filter the maximum number of appointments before scheduling the transient delete.
			 *
			 * @since 4.18.1
			 *
			 * @param int Number of maximum booklings.
			 * @param int $product_or_staff_id Product or the Staff ID.
			 * @param int $min_date Start date timestamp.
			 * @param int $max_date End date timestamp.
			 */
			$max_appointment_count = apply_filters( 'woocommerce_appointments_max_appointments_to_delete_transient', 1000, (int) $product_or_staff_id, $min_date, $max_date );

			// If existing appointments' count is less than 1000, delete the transient now,
			// otherwise schedule it via an action scheduler.
			$existing_appointments = self::get_appointments_in_date_range( $min_date, $max_date, $product_or_staff_id, true );
			if ( count( $existing_appointments ) < $max_appointment_count ) {
				WC_Appointments_Cache::delete_appointment_slots_transient();
				WC_Appointments_Cache::invalidate_cache_group( self::CACHE_GROUP );
			} else {
				// Call the function with an extra argument to prepare data and schedule an event later.
				WC_Appointments_Controller::find_scheduled_day_slots( (int) $_POST['add-to-cart'], $min_date, $max_date, 'Y-n-j', $timezone_offset, [] );
			}
		} else {
			WC_Appointments_Cache::delete_appointment_slots_transient();
			WC_Appointments_Cache::invalidate_cache_group( self::CACHE_GROUP );
		}
	}

	/**
	 * Method to read an order from the database.
	 *
	 * @param WC_Appointment
	 */
	public function read( &$appointment ): void {
		$appointment->set_defaults();
		$appointment_id = $appointment->get_id();
		$post_object    = $appointment_id ? get_post( $appointment_id ) : false;

		if ( ! $appointment_id || ! $post_object || 'wc_appointment' !== $post_object->post_type ) {
			throw new Exception(
			    __( 'Invalid appointment.', 'woocommerce-appointments' ),
			);
		}

		$set_props = [];

		// Read post data.
		$set_props['date_created']  = $post_object->post_date;
		$set_props['date_modified'] = $post_object->post_modified;
		$set_props['status']        = $post_object->post_status;
		$set_props['order_id']      = $post_object->post_parent;

		// Read meta data.
		foreach ( $this->appointment_meta_key_to_props as $key => $prop ) {
			$value = get_post_meta( $appointment->get_id(), $key, true );

			switch ( $prop ) {
				case 'end':
				case 'start':
					#error_log( var_export( $value ? ( (bool) strtotime( $value ) ? strtotime( $value ) : $value ) : '', true ) );
					$set_props[ $prop ] = $value ? strtotime( $value ) : 0;
					break;
				case 'all_day':
					$set_props[ $prop ] = wc_appointments_string_to_bool( $value );
					break;
				case 'staff_ids':
					// Staff can be saved multiple times to same meta key.
					$value              = get_post_meta( $appointment->get_id(), $key, false );
					$set_props[ $prop ] = $value;
					break;
				default:
					$set_props[ $prop ] = $value;
					break;
			}
		}

		#error_log( var_export( $set_props, true ) );

		$appointment->set_props( $set_props );
		$appointment->set_object_read( true );
	}

	/**
	 * Method to update an order in the database.
	 *
	 * @param WC_Appointment $appointment
	 */
	public function update( &$appointment ): void {
		wp_update_post(
		    [
				'ID'            => $appointment->get_id(),
				'post_date'     => date(
				    'Y-m-d H:i:s',
				    $appointment->get_date_created( 'edit' ),
				),
				'post_date_gmt' => get_gmt_from_date(
				    date(
				        'Y-m-d H:i:s',
				        $appointment->get_date_created( 'edit' ),
				    ),
				),
				'post_status'   => $appointment->get_status( 'edit' ),
				'post_author'   => $appointment->get_customer_id( 'edit' ),
				'post_parent'   => $appointment->get_order_id( 'edit' ),
			],
		);
		$this->update_post_meta( $appointment );
		$appointment->save_meta_data();
		$appointment->apply_changes();
		WC_Cache_Helper::get_transient_version( 'appointments', true );
		WC_Appointments_Cache::flush_all_appointment_connected_transients( $appointment );
		WC_Appointments_Cache::invalidate_cache_group( self::CACHE_GROUP );
		// Emit an explicit update event so WooCommerce webhooks can deliver
		// `appointment.updated` without relying solely on status transitions.
		do_action( 'woocommerce_update_appointment', $appointment->get_id() );
	}

	/**
	 * Delete an appointment from the database.
	 *
	 * Removes the appointment record and associated metadata.
	 *
	 * @param WC_Appointment $appointment The appointment object to delete.
	 * @param array          $args        Array of args to pass to the delete method.
	 */
	public function delete( &$appointment, $args = [] ): void {
		$id   = $appointment->get_id();
		$args = wp_parse_args(
		    $args,
		    [
				'force_delete' => false,
			],
		);

		WC_Appointments_Cache::flush_all_appointment_connected_transients( $appointment );
		WC_Appointments_Cache::invalidate_cache_group( self::CACHE_GROUP );

		if ( $args['force_delete'] ) {
			wp_delete_post( $id );
			$appointment->set_id( 0 );
			do_action( 'woocommerce_delete_appointment', $id );
		} else {
			wp_trash_post( $id );
			$appointment->set_status( 'trash' );
			do_action( 'woocommerce_trash_appointment', $id );
		}
	}

	/**
	 * Helper method that updates all the post meta for an appointment based on it's settings in the WC_Appointment class.
	 *
	 * @param WC_Appointment
	 */
	protected function update_post_meta( &$appointment ) {
		foreach ( $this->appointment_meta_key_to_props as $key => $prop ) {
			if ( is_callable( [ $appointment, "get_$prop" ] ) ) {
				$value = $appointment->{ "get_$prop" }( 'edit' );

				switch ( $prop ) {
					case 'all_day':
						update_post_meta( $appointment->get_id(), $key, $value ? 1 : 0 );
						break;
					case 'end':
					case 'start':
						update_post_meta( $appointment->get_id(), $key, $value ? date( 'YmdHis', $value ) : '' );
						break;
					case 'staff_ids':
						delete_post_meta( $appointment->get_id(), $key );
						if ( is_array( $value ) ) {
							foreach ( $value as $staff_id ) {
								add_post_meta( $appointment->get_id(), '_appointment_staff_id', $staff_id );
							}
						} elseif ( is_numeric( $value ) ) {
							add_post_meta( $appointment->get_id(), '_appointment_staff_id', $value );
						}
						break;
					case 'google_calendar_staff_event_ids':
						if ( $value && is_array( $value ) ) {
							$new_value = [];
							foreach ( $value as $staff_id => $event_id ) {
								$new_value[ $staff_id ] = $event_id;
							}
							$current_value = get_post_meta( $appointment->get_id(), $key, true );
							if ( $current_value && is_array( $current_value ) ) {
								foreach ( $current_value as $c_staff_id => $c_event_id ) {
									$new_value[ $c_staff_id ] = $c_event_id;
								}
							}
							update_post_meta( $appointment->get_id(), $key, $new_value );
						}
						// Delete.
						if ( ! $value ) {
							delete_post_meta( $appointment->get_id(), $key );
						}
						break;
					default:
						update_post_meta( $appointment->get_id(), $key, $value );
						break;
				}
			}
		}
	}

	/**
	 * Check if all appointments for a given order ID are confirmed.
	 *
	 * @param int $order_id
	 * @return bool
	 */
	 public static function all_appointments_in_order_confirmed( $order_id ) {
		$order_id = absint( $order_id );

		// Use existing helper to gather appointment IDs linked to this order.
		$appointment_ids = (array) self::get_appointment_ids_from_order_id( $order_id );
		if ( [] === $appointment_ids ) {
			return false; // No appointments for this order.
		}

		// Check each appointment object's status instead of querying a non-existent custom table.
		foreach ( $appointment_ids as $aid ) {
			$aid = (int) $aid;
			if ( 0 >= $aid ) {
				return false;
			}
			try {
				$appt = get_wc_appointment( $aid );
			} catch ( \Exception $e ) {
				return false;
			}
			if ( ! $appt || 'confirmed' !== $appt->get_status() ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * For a given order ID, get all appointments that belong to it.
	 * DO NOT CACHE
	 *
	 * @param  int|array $order_id
	 *
	 * @return int[] Array of appointment IDs.
	 */
	public static function get_appointment_ids_from_order_id( $order_id ) {
		global $wpdb;

		// Search multiple in multiple order IDs.
		if ( is_array( $order_id ) ) {
			global $wpdb;

			$order_ids = wp_parse_id_list( $order_id );

			return wp_parse_id_list(
			    $wpdb->get_col(
			        "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'wc_appointment' AND post_parent IN (" . implode( ',', array_map( 'esc_sql', $order_ids ) ) . ");",
			    ),
			);

		// Search in single order ID.
		} else {
			global $wpdb;

			$order_id = absint( $order_id );

			$data = wp_parse_id_list(
			    $wpdb->get_col(
			        $wpdb->prepare(
			            "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'wc_appointment' AND post_parent = %d;",
			            $order_id,
			        ),
			    ),
			);

			if ( empty( $data ) ) {
				$order = wc_get_order( $order_id );
				if ( is_a( $order, 'WC_Order' ) ) {
					$by_items = [];
					foreach ( $order->get_items() as $item_id => $item ) {
						if ( $item->is_type( 'line_item' ) ) {
							$ids = self::get_appointment_ids_from_order_item_id( $item_id, true );
							if ( ! empty( $ids ) ) {
								$by_items = array_merge( $by_items, (array) $ids );
							}
						}
					}
					$data = wp_parse_id_list( $by_items );
				}
			}

			return $data;
		}
	}

	/**
	 * For a given order item ID, get all appointments that belong to it.
	 * DO NOT CACHE
	 *
	 * @param  int $order_item_id
	 * @return array
	 */
    public static function get_appointment_ids_from_order_item_id( $order_item_id, $no_cache = false ) {
        $order_item_id = absint( $order_item_id );

        $collected = [];

        if ( ! $no_cache ) {
            $cached = wc_get_order_item_meta( $order_item_id, '_appointment_id', true );
            if ( ! empty( $cached ) ) {
                $collected = array_merge( $collected, (array) $cached );
            } else {
                $public_cached = wc_get_order_item_meta( $order_item_id, 'appointment_id', true );
                if ( ! empty( $public_cached ) ) {
                    $collected = array_merge( $collected, (array) $public_cached );
                }
            }
        }

        if ( $no_cache || [] === $collected ) {
            global $wpdb;
            $queried = $wpdb->get_col(
                $wpdb->prepare(
                    "SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = '_appointment_order_item_id' AND meta_value = %d;",
                    $order_item_id,
                ),
            );
            if ( ! empty( $queried ) ) {
                $collected = array_merge( $collected, (array) $queried );
            }
        }

        $appointment_ids = wp_parse_id_list( $collected );

        if ( ! empty( $appointment_ids ) ) {
            wc_update_order_item_meta( $order_item_id, '_appointment_id', $appointment_ids );
        }

        return $appointment_ids;
    }

	/**
	 * For a given order item ID and order ID, get all appointments that belong to both.
	 *
	 * @param  int $order_item_id
	 * @return array
	 */
	public static function get_appointment_ids_from_order_and_item_id( $order_id = 0, $order_item_id = 0 ) {
		$appointment_ids_i = self::get_appointment_ids_from_order_item_id( $order_item_id );
		$appointment_ids_o = self::get_appointment_ids_from_order_id( $order_id );
        #echo '<pre>' . var_export( $appointment_ids_i, true ) . '</pre>';
        // Remove appointments that are from different orders.
        if ($appointment_ids_i && $appointment_ids_o) {
            return array_intersect( $appointment_ids_i, $appointment_ids_o );
        }

		if ($appointment_ids_i) {
            return (array) $appointment_ids_i;
        }
        return [];
	}

	/**
	 * Check if a given order contains only Appointments items.
	 * If the order contains non-appointment items, it will return false.
	 * Otherwise, it will return an array of Appointments.
	 *
	 * @param  WC_Order $order
	 * @return bool|array
	 */
	public static function get_order_contains_only_appointments( $order ) {
		$all_appointment_ids = [];

		foreach ( array_keys( $order->get_items() ) as $order_item_id ) {
			$appointment_ids = self::get_appointment_ids_from_order_item_id( $order_item_id );

			if ( empty( $appointment_ids ) ) {
				return false;
			}

			$all_appointment_ids = array_merge( $all_appointment_ids, $appointment_ids );
		}

		return $all_appointment_ids;
	}

	/**
	 * Get appointment ids for an object  by ID. e.g. product.
	 * DO NOT CACHE
	 *
	 * @param  array
	 * @return array
	 */
	public static function get_appointment_ids_by( $filters = [] ) {
		global $wpdb;

		// Normalize defaults.
		$filters = wp_parse_args(
		    $filters,
		    [
				'object_id'     => 0,
				'product_id'    => 0,
				'staff_id'      => 0,
				'object_type'   => 'product',
				'strict'        => false,
				'status'        => false,
				'limit'         => -1,
				'offset'        => 0,
				'order_by'      => 'date_created',
				'order'         => 'DESC',
				'date_before'   => false,
				'date_after'    => false,
				'gcal_event_id' => false,
				'date_between'  => [
					'start' => false,
					'end'   => false,
				],
				// Back-compat (these were referenced in code).
				'post_date_before' => false,
				'post_date_after'  => false,
			],
		);

		// Product and staff fallbacks.
		$filters['product_id'] = $filters['product_id'] ?: $filters['object_id'];
		$filters['staff_id']   = $filters['staff_id'] ?: $filters['object_id'];

		// Normalize arrays of IDs.
		$filters['object_id']  = array_filter( wp_parse_id_list( is_array( $filters['object_id'] ) ? $filters['object_id'] : [ $filters['object_id'] ] ) );
		$filters['product_id'] = array_filter( wp_parse_id_list( is_array( $filters['product_id'] ) ? $filters['product_id'] : [ $filters['product_id'] ] ) );
		$filters['staff_id']   = array_filter( wp_parse_id_list( is_array( $filters['staff_id'] ) ? $filters['staff_id'] : [ $filters['staff_id'] ] ) );

		// Cache key.
		$cache_key = 'appt_ids_by:' . md5( wp_json_encode( $filters ) );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}

		// Whitelist order_by and order.
		$order_by = 'p.post_date';
		if ( isset( $filters['order_by'] ) ) {
			switch ( $filters['order_by'] ) {
				case 'date_created':
					$order_by = 'p.post_date';
					break;
				case 'start_date':
					// Will add meta join for _appointment_start later.
					$order_by = '_appointment_start.meta_value';
					break;
			}
		}
		$order_dir = ( isset( $filters['order'] ) && 'ASC' === strtoupper( $filters['order'] ) ) ? 'ASC' : 'DESC';

		$joins        = [];
		$where        = [];
		$params       = [];
		$meta_to_join = [];

		// Base constraints.
		$where[]  = "p.post_type = 'wc_appointment'";

		// Statuses.
		if ( ! empty( $filters['status'] ) ) {
			$statuses = array_filter( array_map( 'strval', (array) $filters['status'] ) );
			if ( [] !== $statuses ) {
				$placeholders = implode( ',', array_fill( 0, count( $statuses ), '%s' ) );
				$where[]      = "p.post_status IN ($placeholders)";
				$params       = array_merge( $params, $statuses );
			}
		}

		// Google Calendar event ID.
		if ( ! empty( $filters['gcal_event_id'] ) ) {
			$meta_to_join['_wc_appointments_gcal_event_id'] = true;
			$gids        = array_map( 'strval', (array) $filters['gcal_event_id'] );
			$placeholders = implode( ',', array_fill( 0, count( $gids ), '%s' ) );
			$where[]      = "_wc_appointments_gcal_event_id.meta_value IN ($placeholders)";
			$params       = array_merge( $params, $gids );
		}

		// Post date filters (posts table uses DATETIME).
		if ( ! empty( $filters['post_date_before'] ) ) {
			$where[] = 'p.post_date < %s';
			$params[] = gmdate( 'Y-m-d H:i:s', (int) $filters['post_date_before'] );
		}
		if ( ! empty( $filters['post_date_after'] ) ) {
			$where[] = 'p.post_date > %s';
			$params[] = gmdate( 'Y-m-d H:i:s', (int) $filters['post_date_after'] );
		}

		// Object type filters: product/staff/customer.
		switch ( $filters['object_type'] ) {
			case 'product':
			case 'product_and_staff':
				if ( ! empty( $filters['product_id'] ) && (isset($filters['staff_id']) && [] !== $filters['staff_id']) ) {
					$meta_to_join['_appointment_product_id'] = true;
					$meta_to_join['_appointment_staff_id']   = true;

					$pids          = $filters['product_id'];
					$sids          = $filters['staff_id'];
					$pid_place     = implode( ',', array_fill( 0, count( $pids ), '%d' ) );
					$sid_place     = implode( ',', array_fill( 0, count( $sids ), '%d' ) );

					if ( ! empty( $filters['strict'] ) ) {
						$where[]  = "( (_appointment_product_id.meta_value IN ($pid_place)) AND (_appointment_staff_id.meta_value IN ($sid_place)) )";
					} else {
						$where[]  = "( (_appointment_product_id.meta_value IN ($pid_place)) OR (_appointment_staff_id.meta_value IN ($sid_place)) )";
					}
					$params   = array_merge( $params, $pids, $sids );
				} elseif ( ! empty( $filters['product_id'] ) ) {
					$meta_to_join['_appointment_product_id'] = true;
					$pids      = $filters['product_id'];
					$pid_place = implode( ',', array_fill( 0, count( $pids ), '%d' ) );
					$where[]   = "_appointment_product_id.meta_value IN ($pid_place)";
					$params    = array_merge( $params, $pids );
				} elseif ( isset($filters['staff_id']) && [] !== $filters['staff_id'] ) {
					$meta_to_join['_appointment_staff_id'] = true;
					$sids      = $filters['staff_id'];
					$sid_place = implode( ',', array_fill( 0, count( $sids ), '%d' ) );
					$where[]   = "_appointment_staff_id.meta_value IN ($sid_place)";
					$params    = array_merge( $params, $sids );
				}
				break;

			case 'staff':
				if ( isset($filters['staff_id']) && [] !== $filters['staff_id'] ) {
					$meta_to_join['_appointment_staff_id'] = true;
					$sids      = $filters['staff_id'];
					$sid_place = implode( ',', array_fill( 0, count( $sids ), '%d' ) );
					$where[]   = "_appointment_staff_id.meta_value IN ($sid_place)";
					$params    = array_merge( $params, $sids );
				}
				break;

			case 'customer':
				if ( ! empty( $filters['object_id'] ) ) {
					$meta_to_join['_appointment_customer_id'] = true;
					$cids       = $filters['object_id'];
					$cid_place  = implode( ',', array_fill( 0, count( $cids ), '%d' ) );
					$where[]    = "_appointment_customer_id.meta_value IN ($cid_place)";
					$params     = array_merge( $params, $cids );
				}
				break;
		}

		// Date between in meta (string dates in YmdHis).
		if ( ! empty( $filters['date_between']['start'] ) && ! empty( $filters['date_between']['end'] ) ) {
			$meta_to_join['_appointment_start']    = true;
			$meta_to_join['_appointment_end']      = true;
			$meta_to_join['_appointment_all_day']  = true;

			$end_dt   = gmdate( 'YmdHis', (int) $filters['date_between']['end'] );
			$start_dt = gmdate( 'YmdHis', (int) $filters['date_between']['start'] );
			$end_day  = gmdate( 'Ymd000000', (int) $filters['date_between']['end'] );
			$start_day= gmdate( 'Ymd000000', (int) $filters['date_between']['start'] );

			$where[] = "( (
				_appointment_start.meta_value <= %s AND
				_appointment_end.meta_value > %s AND
				_appointment_all_day.meta_value = '0'
			) OR (
				_appointment_start.meta_value <= %s AND
				_appointment_end.meta_value >= %s AND
				_appointment_all_day.meta_value = '1'
			) )";
			$params = array_merge( $params, [ $end_dt, $start_dt, $end_day, $start_day ] );
		}

		// Date after/before in meta (string dates in YmdHis).
		if ( ! empty( $filters['date_after'] ) ) {
			$meta_to_join['_appointment_start'] = true;
			$where[]  = '_appointment_start.meta_value > %s';
			$params[] = gmdate( 'YmdHis', (int) $filters['date_after'] );
		}
		if ( ! empty( $filters['date_before'] ) ) {
			$meta_to_join['_appointment_end'] = true;
			$where[]  = '_appointment_end.meta_value < %s';
			$params[] = gmdate( 'YmdHis', (int) $filters['date_before'] );
		}

		// If ordering by start_date, ensure join.
		if ( '_appointment_start.meta_value' === $order_by ) {
			$meta_to_join['_appointment_start'] = true;
		}

		// Early path: no meta joins needed -> query posts only.
		if ( [] === $meta_to_join ) {
			$sql   = "SELECT p.ID FROM {$wpdb->posts} p WHERE " . implode( ' AND ', $where ) . " ORDER BY {$order_by} {$order_dir}";
			if ( 0 < $filters['limit'] ) {
				$sql .= $wpdb->prepare( ' LIMIT %d, %d', absint( $filters['offset'] ), absint( $filters['limit'] ) );
			}
			$query = $wpdb->prepare( $sql, $params );
			$ids   = array_filter( wp_parse_id_list( $wpdb->get_col( $query ) ) );
			wp_cache_set( $cache_key, $ids, self::CACHE_GROUP );
			return $ids;
		}

		// Simpler and reliable approach: JOIN necessary meta keys directly.
		$select = "SELECT p.ID FROM {$wpdb->posts} p";
		$joins  = [];
		// Build JOINs for every meta key we referenced.
		foreach ( array_keys( $meta_to_join ) as $key ) {
			$joins[] = "INNER JOIN {$wpdb->postmeta} {$key} ON ( {$key}.post_id = p.ID AND {$key}.meta_key = '{$key}' )";
		}
		$sql  = $select;
		if ( ! empty( $joins ) ) {
			$sql .= ' ' . implode( ' ', $joins );
		}
		$sql .= ' WHERE ' . implode( ' AND ', $where );
		$sql .= " ORDER BY {$order_by} {$order_dir}";
		if ( 0 < $filters['limit'] ) {
			$sql .= $wpdb->prepare( ' LIMIT %d, %d', absint( $filters['offset'] ), absint( $filters['limit'] ) );
		}
		$query = $wpdb->prepare( $sql, $params );
		$ids   = array_filter( wp_parse_id_list( $wpdb->get_col( $query ) ) );

		wp_cache_set( $cache_key, $ids, self::CACHE_GROUP );
		return $ids;
	}

	/**
	 * For a given appointment ID, get it's linked order ID if set.
	 *
	 * @param  int $appointment_id
	 *
	 * @return int
	 */
	public static function get_appointment_order_id( $appointment_id ) {
		return absint( wp_get_post_parent_id( $appointment_id ) );
	}

	/**
	 * For a given appointment ID, get it's linked order item ID if set.
	 *
	 * @param  int $appointment_id
	 * @return int
	 */
	public static function get_appointment_order_item_id( $appointment_id ) {
		return absint( get_post_meta( $appointment_id, '_appointment_order_item_id', true ) );
	}

	/**
	 * For a given appointment ID, get it's linked order customer ID if set.
	 *
	 * @param  int $appointment_id
	 * @return int
	 */
	public static function get_appointment_customer_id( $appointment_id ) {
		return absint( get_post_meta( $appointment_id, '_appointment_customer_id', true ) );
	}

	/**
	 * Gets appointments for product ids and staff ids
	 * @param  array    $product_ids
	 * @param  array    $staff_ids
	 * @param  array    $status
	 * @param  integer  $date_from
	 * @param  integer  $date_to
	 *
	 * @return array of WC_Appointment objects
	 */
	public static function get_appointments_for_objects_query( $product_ids, $staff_ids, $status, $date_from = 0, $date_to = 0 ) {
		$status    = empty( $status ) ? get_wc_appointment_statuses( 'fully_scheduled' ) : $status;
		$date_from = empty( $date_from ) ? strtotime( 'midnight', current_time( 'timestamp' ) ) : $date_from;
		$date_to   = empty( $date_to ) ? strtotime( '+12 month', current_time( 'timestamp' ) ) : $date_to;

		// Filter the arguments
		$args = apply_filters(
		    'woocommerce_appointments_for_objects_query_args',
		    [
				'status'       => $status,
				'product_id'   => $product_ids,
				'staff_id'     => $staff_ids,
				'object_type'  => 'product_and_staff',
				'date_between' => [
					'start' => $date_from,
					'end'   => $date_to,
				],
			],
		);

		$appointment_ids = self::get_appointment_ids_by( $args );

		return apply_filters( 'woocommerce_appointments_for_objects_query', $appointment_ids );
	}

	/**
	 * Gets appointments for product ids and staff ids
	 * @param  array    $product_ids
	 * @param  array    $staff_ids
	 * @param  array    $status
	 * @param  integer  $date_from
	 * @param  integer  $date_to
	 *
	 * @return array of WC_Appointment objects
	 */
	public static function get_appointments_for_objects( $product_ids = [], $staff_ids = [], $status = [], $date_from = 0, $date_to = 0 ) {
		// TODO: We need to round date_from/date_to to something specific.
		// Otherwise, one might abuse the DB transient cache by calling various combinations from the front-end with min-date/max-date.
		$transient_name  = 'schedule_fo_' . md5( http_build_query( [ $product_ids, $staff_ids, $date_from, $date_to, WC_Cache_Helper::get_transient_version( 'appointments' ) ] ) );
		$status          = ( empty( $status ) ) ? get_wc_appointment_statuses( 'fully_scheduled' ) : $status;
		$date_from       = empty( $date_from ) ? strtotime( 'midnight', current_time( 'timestamp' ) ) : $date_from;
		$date_to         = empty( $date_to ) ? strtotime( '+12 month', current_time( 'timestamp' ) ) : $date_to;
		$appointment_ids = WC_Appointments_Cache::get( $transient_name );

		if ( false === $appointment_ids ) {
			$appointment_ids = self::get_appointments_for_objects_query( $product_ids, $staff_ids, $status, $date_from, $date_to );
			WC_Appointments_Cache::set( $transient_name, $appointment_ids, DAY_IN_SECONDS * 30 );
		}

		#echo '<pre>' . var_export( $appointment_ids, true ) . '</pre>';

		// Get objects.
		if ( ! empty( $appointment_ids ) ) {
			return array_map( 'get_wc_appointment', wp_parse_id_list( $appointment_ids ) );
		}

		return [];
	}

	/**
	 * Finds existing appointments for a product and its tied staff.
	 *
	 * @param  int|WC_Product_Appointment $appointable_product  Product ID or object
	 * @param  int                        $min_date
	 * @param  int                        $max_date
	 * @param  array                      $staff_ids
	 *
	 * @return array
	 */
	public static function get_all_existing_appointments( $appointable_product, $min_date = 0, $max_date = 0, $staff_ids = [] ) {
		if ( is_int( $appointable_product ) ) {
			$appointable_product = wc_get_product( $appointable_product );
		}
		$find_appointments_for_product = [ $appointable_product->get_id() ];
		$find_appointments_for_staff   = [];

		// Account for staff?
		if ( $appointable_product->has_staff() ) {
			$staff_ids   = 0 === absint( $staff_ids ) ? $appointable_product->get_staff_ids() : $staff_ids; #no preference
			$staff_ids   = wc_appointments_normalize_staff_ids( $staff_ids );
			$staff_array = [] === $staff_ids ? $appointable_product->get_staff_ids() : $staff_ids;
			// Loop through staff.
			foreach ( $staff_array as $staff_member_id ) {
				$find_appointments_for_staff[] = $staff_member_id;
			}
		}

		// Account for padding?
		$padding_duration_in_minutes = $appointable_product->get_padding_duration_in_minutes();
		if ( $padding_duration_in_minutes && in_array( $appointable_product->get_duration_unit(), [ 'hour', 'minute', 'day' ] ) ) {
			if ( ! empty( $min_date ) ) {
				$min_date = strtotime( "-{$padding_duration_in_minutes} minutes", $min_date );
			}
			if ( ! empty( $max_date ) ) {
				$max_date = strtotime( "+{$padding_duration_in_minutes} minutes", $max_date );
			}
		}

		if ( empty( $min_date ) ) {
			// Determine a min and max date
			$min_date = $appointable_product->get_min_date_a();
			$min_date = empty( $min_date ) ? [
				'unit'  => 'minute',
				'value' => 1,
			] : $min_date;
			$min_date = strtotime( "midnight +{$min_date['value']} {$min_date['unit']}", current_time( 'timestamp' ) );
		}

		if ( empty( $max_date ) ) {
			$max_date = $appointable_product->get_max_date_a();
			$max_date = empty( $max_date ) ? [
				'unit'  => 'month',
				'value' => 12,
			] : $max_date;
			$max_date = strtotime( "+{$max_date['value']} {$max_date['unit']}", current_time( 'timestamp' ) );
		}

		#error_log( var_export( 'get_all_existing_appointments', true ) );

		return self::get_appointments_for_objects(
		    $find_appointments_for_product,
		    $find_appointments_for_staff,
		    get_wc_appointment_statuses( 'fully_scheduled' ),
		    $min_date,
		    $max_date,
		);
	}

	/**
	 * Return all appointments for a product and/or staff in a given range  - the query part (no cache)
	 * @param integer $start_date
	 * @param integer $end_date
	 * @param integer $product_id
	 * @param integer $staff_id
	 * @param bool    $check_in_cart
	 * @param array   $filters
	 * @param bool    $strict
	 *
	 * @return array of appointment ids
	 */
	private static function get_appointments_in_date_range_query( $start_date, $end_date, $product_id, $staff_id, $check_in_cart, $filters, $strict ) {
		$args = wp_parse_args(
		    $filters,
		    [
				'status'       => get_wc_appointment_statuses(),
				'object_id'    => 0,
				'product_id'   => 0,
				'staff_id'     => 0,
				'object_type'  => 'product',
				'strict'       => $strict,
				'date_between' => [
					'start' => $start_date,
					'end'   => $end_date,
				],
			],
		);

		if ( ! $check_in_cart ) {
			$args['status'] = array_diff( $args['status'], [ 'in-cart' ] );
		}

		if ( $product_id ) {
			$args['product_id'] = $product_id;
		}

		if ( $staff_id ) {
			$args['staff_id'] = $staff_id;
		}

		if ( ! $product_id && $staff_id ) {
			$args['object_type'] = 'staff';
		}

		if ( $product_id && $staff_id ) {
			$args['object_type'] = 'product_and_staff';
		}

		// Filter the arguments
		$args = apply_filters( 'woocommerce_appointments_in_date_range_query_args', $args );

		// Filter the appointment IDs.
		return apply_filters( 'woocommerce_appointments_in_date_range_query', self::get_appointment_ids_by( $args ) );
	}

	/**
	 * Return all appointments for a product and/or staff in a given range
	 * @param integer $start_date
	 * @param integer $end_date
	 * @param integer $product_id
	 * @param integer $staff_id
	 * @param bool    $check_in_cart
	 * @param array   $filters
	 * @param bool    $strict
	 *
	 * @return array of appointment ids
	 */
	public static function get_appointments_in_date_range( $start_date, $end_date, $product_id = 0, $staff_id = 0, $check_in_cart = true, $filters = [], $strict = false ) {
		$transient_name  = 'schedule_dr_' . md5( http_build_query( [ $start_date, $end_date, $product_id, $staff_id, $check_in_cart, WC_Cache_Helper::get_transient_version( 'appointments' ) ] ) );
		$appointment_ids = WC_Appointments_Cache::get( $transient_name );

		if ( false === $appointment_ids ) {
			$appointment_ids = self::get_appointments_in_date_range_query( $start_date, $end_date, $product_id, $staff_id, $check_in_cart, $filters, $strict );
			WC_Appointments_Cache::set( $transient_name, $appointment_ids, DAY_IN_SECONDS * 30 );
		}

		#print '<pre>'; print_r( $appointment_ids ); print '</pre>';
		#error_log( var_export( $start_date, true ) );
		#error_log( var_export( $end_date, true ) );
		#error_log( var_export( $filters, true ) );


		// Get objects
		return array_map( 'get_wc_appointment', wp_parse_id_list( $appointment_ids ) );
	}

	/**
	 * Gets appointments for a user by ID
	 *
	 * @param  int   $user_id    The id of the user that we want appointments for
	 * @param  array $query_args The query arguments used to get appointment IDs
	 *
	 * @return array             Array of WC_Appointment objects
	 */
	public static function get_appointments_for_user( $user_id, $query_args = null ) {
		$appointment_ids = self::get_appointment_ids_by(
		    array_merge(
		        [
					'status'      => get_wc_appointment_statuses( 'user' ),
					'object_id'   => $user_id,
					'object_type' => 'customer',
				],
		        $query_args,
		    ),
		);

		return array_map( 'get_wc_appointment', $appointment_ids );
	}

	/**
	 * Gets appointments for a product by ID
	 *
	 * @param int $product_id The id of the product that we want appointments for
	 * @param array $status
	 *
	 * @return array of WC_Appointment objects
	 */
	public static function get_appointments_for_product( $product_id, $status = [ WC_Appointments_Constants::STATUS_CONFIRMED, WC_Appointments_Constants::STATUS_PAID ] ) {
		$appointment_ids = self::get_appointment_ids_by(
		    [
				'object_id'   => $product_id,
				'object_type' => 'product',
				'status'      => $status,
			],
		);

		return array_map( 'get_wc_appointment', $appointment_ids );
	}

	/**
	 * Search appointment data for a term and return ids.
	 *
	 * @since 4.10.2
	 *
	 * @param  string $term Searched term.
	 * @return array of ids
	 */
	public function search_appointments( $term ) {
		global $wpdb;

		$search_fields   = array_map(
		    'wc_clean',
		    apply_filters( 'woocommerce_appointment_search_fields', [] ),
		);
		$appointment_ids = [];

		if ( is_numeric( $term ) ) {
			$appointment_ids[] = absint( $term );
		}

		if ( [] !== $search_fields ) {
			$appointment_ids = array_unique(
			    array_merge(
			        $appointment_ids,
			        $wpdb->get_col(
			            $wpdb->prepare(
			                "SELECT DISTINCT p1.post_id
							FROM {$wpdb->postmeta} p1
							WHERE p1.meta_value LIKE %s
							AND p1.meta_key IN ('" . implode( "','", array_map( 'esc_sql', $search_fields ) ) . "')", // @codingStandardsIgnoreLine
							'%' . $wpdb->esc_like( wc_clean( $term ) ) . '%',
			            ),
			        ),
			    ),
			);
		}

		$appointment_ids = array_unique(
		    array_merge(
		        $appointment_ids,
		        $wpdb->get_col(
		            $wpdb->prepare(
		                "SELECT p.id
						FROM {$wpdb->prefix}posts p
						INNER JOIN $wpdb->users u ON p.post_author = u.id
						WHERE display_name LIKE %s OR user_nicename LIKE %s",
		                '%' . $wpdb->esc_like( wc_clean( $term ) ) . '%',
		                '%' . $wpdb->esc_like( wc_clean( $term ) ) . '%',
		            ),
		        ),
		        $wpdb->get_col(
		            $wpdb->prepare(
		                "SELECT pm.post_id
						FROM {$wpdb->prefix}postmeta pm
						INNER JOIN {$wpdb->prefix}posts p ON p.id = pm.meta_value
						WHERE meta_key = '_appointment_product_id' AND p.post_title LIKE %s",
		                '%' . $wpdb->esc_like( wc_clean( $term ) ) . '%',
		            ),
		        ),
		    ),
		);

		return apply_filters( 'woocommerce_appointment_search_results', $appointment_ids, $term, $search_fields );
	}

	/**
	 * Get appointments optimized for calendar display.
	 *
	 * Uses direct SQL with JOINs to avoid N+1 queries and heavy object hydration.
	 * Returns lightweight arrays instead of WC_Appointment objects.
	 *
	 * @since 5.0.0
	 *
	 * @param array $args {
	 *     Query arguments.
	 *
	 *     @type int   $start_ts   Start timestamp (required).
	 *     @type int   $end_ts     End timestamp (required).
	 *     @type int   $product_id Optional product filter.
	 *     @type int   $staff_id   Optional staff filter.
	 *     @type array $status     Optional status filter.
	 *     @type array $include    Fields to include (empty = all default fields).
	 * }
	 * @return array Array of appointment data arrays (not WC_Appointment objects).
	 */
	public static function get_calendar_appointments( array $args ): array {
		global $wpdb;

		$start_ts   = absint( $args['start_ts'] ?? 0 );
		$end_ts     = absint( $args['end_ts'] ?? 0 );
		$product_id = absint( $args['product_id'] ?? 0 );
		$staff_id   = absint( $args['staff_id'] ?? 0 );
		$status     = $args['status'] ?? [];
		$include    = $args['include'] ?? [];

		if ( 0 >= $start_ts || 0 >= $end_ts ) {
			return [];
		}

		// Build SELECT fields - always include essential fields
		$select_fields = [
			'p.ID as id',
			'p.post_status as status',
			'p.post_parent as order_id',
			'pm_start.meta_value as start_raw',
			'pm_end.meta_value as end_raw',
		];

		// Conditional fields based on include parameter
		$include_all = empty( $include );

		if ( $include_all || in_array( 'product_id', $include, true ) ) {
			$select_fields[] = 'pm_product.meta_value as product_id';
		}
		if ( $include_all || in_array( 'staff_ids', $include, true ) || in_array( 'staff_id', $include, true ) ) {
			$select_fields[] = 'pm_staff.meta_value as staff_id';
		}
		if ( $include_all || in_array( 'customer_id', $include, true ) ) {
			$select_fields[] = 'pm_customer.meta_value as customer_id';
		}
		if ( $include_all || in_array( 'customer_status', $include, true ) ) {
			$select_fields[] = 'pm_cust_status.meta_value as customer_status';
		}
		if ( $include_all || in_array( 'cost', $include, true ) ) {
			$select_fields[] = 'pm_cost.meta_value as cost';
		}
		if ( $include_all || in_array( 'all_day', $include, true ) ) {
			$select_fields[] = 'pm_allday.meta_value as all_day';
		}
		if ( $include_all || in_array( 'order_item_id', $include, true ) ) {
			$select_fields[] = 'pm_order_item.meta_value as order_item_id';
		}
		if ( $include_all || in_array( 'qty', $include, true ) ) {
			$select_fields[] = 'pm_qty.meta_value as qty';
		}

		// Customer name from user table (avoid loading full WP_User)
		$include_customer_name = $include_all || in_array( 'customer_name', $include, true );
		if ( $include_customer_name ) {
			$select_fields[] = 'um_first.meta_value as customer_first_name';
			$select_fields[] = 'um_last.meta_value as customer_last_name';
			$select_fields[] = 'u.display_name as customer_display_name';
			$select_fields[] = 'u.user_email as customer_email';
			$select_fields[] = 'um_phone.meta_value as customer_phone';
		}

		// Staff name and email for avatar
		$include_staff_info = $include_all || in_array( 'staff_name', $include, true ) || in_array( 'staff_avatar', $include, true );
		if ( $include_staff_info ) {
			$select_fields[] = 'staff_user.display_name as staff_name';
			$select_fields[] = 'staff_user.user_email as staff_email';
		}

		// Calendar color from product meta
		$include_cal_color = $include_all || in_array( 'cal_color', $include, true );
		if ( $include_cal_color ) {
			$select_fields[] = 'prod_color.meta_value as cal_color';
		}

		// Product title
		$include_product_title = $include_all || in_array( 'product_title', $include, true );
		if ( $include_product_title ) {
			$select_fields[] = 'prod.post_title as product_title';
		}

		$select_sql = implode( ', ', $select_fields );

		// Build JOINs - always need start/end for date filtering
		$joins = [
			"LEFT JOIN {$wpdb->postmeta} pm_start ON p.ID = pm_start.post_id AND pm_start.meta_key = '_appointment_start'",
			"LEFT JOIN {$wpdb->postmeta} pm_end ON p.ID = pm_end.post_id AND pm_end.meta_key = '_appointment_end'",
		];

		// Optimized: Use conditional JOIN building to minimize unnecessary joins
		$needs_product_join = $include_all || in_array( 'product_id', $include, true ) || 0 < $product_id || $include_cal_color || $include_product_title;
		$needs_staff_join = $include_all || in_array( 'staff_ids', $include, true ) || in_array( 'staff_id', $include, true ) || 0 < $staff_id || $include_staff_info;
		$needs_customer_join = $include_all || in_array( 'customer_id', $include, true ) || $include_customer_name;

		// Product join (needed for product_id filter and cal_color/product_title)
		if ( $needs_product_join ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_product ON p.ID = pm_product.post_id AND pm_product.meta_key = '_appointment_product_id'";
		}

		// Staff join - optimized to avoid duplicate joins
		if ( $needs_staff_join ) {
			$select_fields[] = 'pm_staff_ids.meta_value as staff_ids_raw';
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_staff ON p.ID = pm_staff.post_id AND pm_staff.meta_key = '_appointment_staff_id'";
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_staff_ids ON p.ID = pm_staff_ids.post_id AND pm_staff_ids.meta_key = '_appointment_staff_ids'";
		}

		// Customer join
		if ( $needs_customer_join ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_customer ON p.ID = pm_customer.post_id AND pm_customer.meta_key = '_appointment_customer_id'";
		}

		// Customer status join
		if ( $include_all || in_array( 'customer_status', $include, true ) ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_cust_status ON p.ID = pm_cust_status.post_id AND pm_cust_status.meta_key = '_appointment_customer_status'";
		}

		// Cost join
		if ( $include_all || in_array( 'cost', $include, true ) ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_cost ON p.ID = pm_cost.post_id AND pm_cost.meta_key = '_appointment_cost'";
		}

		// All day join
		if ( $include_all || in_array( 'all_day', $include, true ) ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_allday ON p.ID = pm_allday.post_id AND pm_allday.meta_key = '_appointment_all_day'";
		}

		// Order item ID join
		if ( $include_all || in_array( 'order_item_id', $include, true ) ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_order_item ON p.ID = pm_order_item.post_id AND pm_order_item.meta_key = '_appointment_order_item_id'";
		}

		// Qty join
		if ( $include_all || in_array( 'qty', $include, true ) ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} pm_qty ON p.ID = pm_qty.post_id AND pm_qty.meta_key = '_appointment_qty'";
		}

		// Customer name joins (user table)
		if ( $include_customer_name ) {
			$joins[] = "LEFT JOIN {$wpdb->usermeta} um_first ON pm_customer.meta_value = um_first.user_id AND um_first.meta_key = 'first_name'";
			$joins[] = "LEFT JOIN {$wpdb->usermeta} um_last ON pm_customer.meta_value = um_last.user_id AND um_last.meta_key = 'last_name'";
			$joins[] = "LEFT JOIN {$wpdb->usermeta} um_phone ON pm_customer.meta_value = um_phone.user_id AND um_phone.meta_key = 'billing_phone'";
			$joins[] = "LEFT JOIN {$wpdb->users} u ON pm_customer.meta_value = u.ID";
		}

		// Staff user joins
		if ( $include_staff_info ) {
			$joins[] = "LEFT JOIN {$wpdb->users} staff_user ON pm_staff.meta_value = staff_user.ID";
		}

		// Calendar color join (product meta)
		if ( $include_cal_color ) {
			$joins[] = "LEFT JOIN {$wpdb->postmeta} prod_color ON pm_product.meta_value = prod_color.post_id AND prod_color.meta_key = '_wc_appointment_cal_color'";
		}

		// Product title join
		if ( $include_product_title ) {
			$joins[] = "LEFT JOIN {$wpdb->posts} prod ON pm_product.meta_value = prod.ID";
		}

		$joins_sql = implode( "\n", $joins );

		// Build WHERE clause
		$where  = [];
		$params = [];

		$where[] = "p.post_type = 'wc_appointment'";

		// Status filter
		if ( ! empty( $status ) ) {
			$statuses     = array_map( 'sanitize_text_field', (array) $status );
			$placeholders = implode( ', ', array_fill( 0, count( $statuses ), '%s' ) );
			$where[]      = "p.post_status IN ($placeholders)";
			$params       = array_merge( $params, $statuses );
		} else {
			// Default: exclude trash, auto-draft, in-cart, was-in-cart
			$where[] = "p.post_status NOT IN ('trash', 'auto-draft', 'in-cart', 'was-in-cart')";
		}

		// Date range filter using overlap logic: start < end_ts AND end > start_ts
		$start_str = gmdate( 'YmdHis', $start_ts );
		$end_str   = gmdate( 'YmdHis', $end_ts );
		$where[]   = 'pm_start.meta_value < %s';
		$params[]  = $end_str;
		$where[]   = 'pm_end.meta_value > %s';
		$params[]  = $start_str;

		// Product filter
		if ( 0 < $product_id ) {
			$where[]  = 'pm_product.meta_value = %d';
			$params[] = $product_id;
		}

		// Staff filter
		if ( 0 < $staff_id ) {
			$where[]  = 'pm_staff.meta_value = %d';
			$params[] = $staff_id;
		}

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

		// Build full query
		$sql = "
			SELECT {$select_sql}
			FROM {$wpdb->posts} p
			{$joins_sql}
			{$where_sql}
			GROUP BY p.ID
			ORDER BY pm_start.meta_value ASC
		";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is built with proper escaping
		$prepared_sql = $wpdb->prepare( $sql, $params );
		$results      = $wpdb->get_results( $prepared_sql, ARRAY_A );

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

		// Pre-process results to handle missing staff names and staff_ids
		$missing_staff_ids = [];
		foreach ( $results as &$row ) {
			// Unserialize staff_ids_raw to populate/fix staff_id if needed
			if ( isset( $row['staff_ids_raw'] ) && ! empty( $row['staff_ids_raw'] ) ) {
				$ids = maybe_unserialize( $row['staff_ids_raw'] );
				if ( is_array( $ids ) && ! empty( $ids ) ) {
					// If staff_id is missing/zero but we have IDs, use the first one
					if ( empty( $row['staff_id'] ) ) {
						$row['staff_id'] = reset( $ids );
					}
				}
			}

			// If we have a staff_id but missing name/email (e.g. join failed), mark for fetching
			if ( ! empty( $row['staff_id'] ) && ( empty( $row['staff_name'] ) || empty( $row['staff_email'] ) ) ) {
				$missing_staff_ids[] = absint( $row['staff_id'] );
			}
		}
		unset( $row );

		// Batch fetch missing staff details to avoid N+1 queries
		if ( ! empty( $missing_staff_ids ) ) {
			$missing_staff_ids = array_unique( $missing_staff_ids );
			$users = get_users( [
				'include' => $missing_staff_ids,
				'fields'  => [ 'ID', 'display_name', 'user_email' ],
			] );
			
			$user_map = [];
			foreach ( $users as $u ) {
				$user_map[ $u->ID ] = $u;
			}

			// Patch the rows with fetched user data
			foreach ( $results as &$row ) {
				if ( ! empty( $row['staff_id'] ) && ( empty( $row['staff_name'] ) || empty( $row['staff_email'] ) ) ) {
					$sid = absint( $row['staff_id'] );
					if ( isset( $user_map[ $sid ] ) ) {
						if ( empty( $row['staff_name'] ) ) {
							$row['staff_name'] = $user_map[ $sid ]->display_name;
						}
						if ( empty( $row['staff_email'] ) ) {
							$row['staff_email'] = $user_map[ $sid ]->user_email;
						}
					}
				}
			}
			unset( $row );
		}

		// Post-process results
		return array_map( [ __CLASS__, 'process_calendar_row' ], $results );
	}

	/**
	 * Process a single row from get_calendar_appointments query.
	 *
	 * @since 5.0.0
	 *
	 * @param array $row Raw database row.
	 * @return array Processed appointment data.
	 */
	private static function process_calendar_row( array $row ): array {
		// Convert YmdHis to Unix timestamp
		if ( isset( $row['start_raw'] ) ) {
			$row['start'] = strtotime( $row['start_raw'] );
			unset( $row['start_raw'] );
		}
		if ( isset( $row['end_raw'] ) ) {
			$row['end'] = strtotime( $row['end_raw'] );
			unset( $row['end_raw'] );
		}

		// Normalize status (remove wc- prefix for consistency)
		if ( isset( $row['status'] ) ) {
			$row['status'] = str_replace( 'wc-', '', $row['status'] );
		}

		// Ensure numeric types
		$row['id']         = absint( $row['id'] ?? 0 );
		$row['product_id'] = absint( $row['product_id'] ?? 0 );
		$row['order_id']   = absint( $row['order_id'] ?? 0 );

		if ( isset( $row['customer_id'] ) ) {
			$row['customer_id'] = absint( $row['customer_id'] );
		}
		if ( isset( $row['order_item_id'] ) ) {
			$row['order_item_id'] = absint( $row['order_item_id'] );
		}
		if ( isset( $row['cost'] ) ) {
			$row['cost'] = (float) $row['cost'];
		}
		if ( isset( $row['qty'] ) ) {
			$row['qty'] = absint( $row['qty'] ) ?: 1;
		}
		if ( isset( $row['all_day'] ) ) {
			$row['all_day'] = wc_appointments_string_to_bool( $row['all_day'] );
		}

		// Handle staff_ids from raw data if available
		if ( isset( $row['staff_ids_raw'] ) ) {
			$ids = maybe_unserialize( $row['staff_ids_raw'] );
			if ( is_array( $ids ) ) {
				$row['staff_ids'] = array_map( 'absint', $ids );
			}
			unset( $row['staff_ids_raw'] );
		}

		// Fallback or ensure staff_ids array exists based on staff_id
		if ( ! isset( $row['staff_ids'] ) && isset( $row['staff_id'] ) ) {
			$staff_val         = $row['staff_id'];
			$row['staff_ids']  = 0 < absint( $staff_val ) ? [ absint( $staff_val ) ] : [];
		}
		
		// Ensure staff_id is integer
		if ( isset( $row['staff_id'] ) ) {
			$row['staff_id']   = absint( $row['staff_id'] );
		}

		// Customer name construction
		$first_name    = trim( $row['customer_first_name'] ?? '' );
		$last_name     = trim( $row['customer_last_name'] ?? '' );
		$display_name  = trim( $row['customer_display_name'] ?? '' );
		$full_name     = trim( $first_name . ' ' . $last_name );
		$customer_name = '' !== $full_name ? $full_name : $display_name;

		if ( '' !== $customer_name ) {
			$row['customer_name']       = $customer_name;
			$row['customer_first_name'] = $first_name;
			$row['customer_last_name']  = $last_name;
			$row['customer_full_name']  = $full_name ?: $display_name;
		} else {
			$row['customer_name']       = __( 'Guest', 'woocommerce-appointments' );
			$row['customer_first_name'] = '';
			$row['customer_last_name']  = '';
			$row['customer_full_name']  = __( 'Guest', 'woocommerce-appointments' );
		}
		unset( $row['customer_display_name'] );

		// Default cal_color
		if ( empty( $row['cal_color'] ) ) {
			$row['cal_color'] = '';
		}

		// Default customer_status
		if ( empty( $row['customer_status'] ) ) {
			$row['customer_status'] = 'expected';
		}

		// Generate customer avatar URL from email (or default if empty)
		$customer_email = isset( $row['customer_email'] ) ? $row['customer_email'] : '';
		$row['customer_avatar'] = get_avatar_url( $customer_email, [ 'size' => 48 ] );
		if ( ! isset( $row['customer_phone'] ) ) {
			$row['customer_phone'] = '';
		}

		// Generate staff avatar URL (or default if empty) and ensure staff_name is set
		$staff_email = isset( $row['staff_email'] ) ? $row['staff_email'] : '';
		$row['staff_avatar'] = get_avatar_url( $staff_email, [ 'size' => 48 ] );
		unset( $row['staff_email'] ); // Don't expose email in API response

		// Default staff_name
		if ( ! isset( $row['staff_name'] ) || '' === $row['staff_name'] ) {
			$row['staff_name'] = '';
		}

		return $row;
	}

	/**
	 * Get order details for calendar appointments.
	 *
	 * Fetches order data for multiple order IDs efficiently to avoid N+1 queries.
	 * Called after get_calendar_appointments to enrich results with order data.
	 *
	 * @since 5.0.0
	 *
	 * @param array $order_ids Array of order IDs to fetch.
	 * @return array Associative array of order_id => order_data.
	 */
	public static function get_calendar_order_details( array $order_ids ): array {
		if ( empty( $order_ids ) ) {
			return [];
		}

		$order_ids = array_unique( array_filter( array_map( 'absint', $order_ids ) ) );
		if ( empty( $order_ids ) ) {
			return [];
		}

		$orders_data = [];

		foreach ( $order_ids as $order_id ) {
			$order = wc_get_order( $order_id );
			if ( ! $order ) {
				continue;
			}

			$billing = $order->get_address( 'billing' );

			// Format line items for addons display
			$line_items = [];
			foreach ( $order->get_items() as $item_id => $item ) {
				$meta_data = [];
				foreach ( $item->get_formatted_meta_data() as $meta_key => $meta ) {
					$meta_data[] = [
						'key'           => $meta->key,
						'value'         => $meta->value,
						'display_key'   => $meta->display_key,
						'display_value' => strip_tags( $meta->display_value ), // Strip tags for clean display
					];
				}
				$line_items[] = [
					'id'        => $item_id,
					'name'      => $item->get_name(),
					'sku'       => $item->get_product() ? $item->get_product()->get_sku() : '',
					'product_id'=> $item->get_product_id(),
					'quantity'  => $item->get_quantity(),
					'subtotal'  => $item->get_subtotal(),
					'total'     => $item->get_total(),
					'meta_data' => $meta_data,
				];
			}

			$orders_data[ $order_id ] = [
				'id'             => $order_id,
				'currency'       => $order->get_currency(),
				'total'          => $order->get_total(),
				'discount_total' => $order->get_discount_total(),
				'billing'        => [
					'first_name' => $billing['first_name'] ?? '',
					'last_name'  => $billing['last_name'] ?? '',
					'email'      => $billing['email'] ?? '',
					'phone'      => $billing['phone'] ?? '',
				],
				'line_items'     => $line_items,
			];
		}

		return $orders_data;
	}
}
