<?php
/**
 * Appointment scheduling domain service.
 *
 * Owns scheduler settings, availability calculations, appointment persistence,
 * atomic booking, and contact-level CRM context access.
 *
 * @package Niwa_AI
 */

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

class Niwa_AI_Appointment_Service {

	const OPTION_KEY       = 'niwa_ai_scheduler_settings';
	const SCHEMA_OPTION    = 'niwa_ai_appointment_schema_stamp';
	const SCHEMA_STAMP     = 'appointments-v1';
	const TABLE_NAME       = 'niwa_ai_appointments';
	const SLOT_INTERVAL    = 5;
	const LOCK_WAIT_SECONDS = 5;

	const STATUS_PENDING   = 'pending';
	const STATUS_CONFIRMED = 'confirmed';
	const STATUS_CANCELLED = 'cancelled';
	const STATUS_COMPLETED = 'completed';
	const STATUS_NO_SHOW   = 'no_show';

	/**
	 * @var Niwa_AI_Contact_Service|null
	 */
	private $contact_service;

	/**
	 * @param Niwa_AI_Contact_Service|null $contact_service Contact persistence service.
	 */
	public function __construct( $contact_service = null ) {
		$this->contact_service = $contact_service;
	}

	/**
	 * Full appointments table name.
	 *
	 * @return string
	 */
	public function appointments_table() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_NAME;
	}

	/**
	 * Ensure appointment and contact CRM schemas exist.
	 *
	 * @param bool $force Re-run dbDelta even when the schema stamp matches.
	 * @return void
	 */
	public function ensure_schema( $force = false ) {
		$contacts = $this->get_contact_service();
		if ( $contacts && method_exists( $contacts, 'ensure_table_schema' ) ) {
			$contacts->ensure_table_schema( $force );
		}

		if ( ! $force && self::SCHEMA_STAMP === get_option( self::SCHEMA_OPTION, '' ) ) {
			return;
		}

		global $wpdb;
		$table   = $this->appointments_table();
		$charset = $wpdb->get_charset_collate();

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta(
			"CREATE TABLE {$table} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				booking_uid varchar(64) NOT NULL DEFAULT '',
				contact_id bigint(20) unsigned NOT NULL DEFAULT 0,
				session_id varchar(96) NOT NULL DEFAULT '',
				lead_form_session_id bigint(20) unsigned NOT NULL DEFAULT 0,
				service_id varchar(64) NOT NULL DEFAULT '',
				service_name varchar(191) NOT NULL DEFAULT '',
				team_member_id varchar(64) NOT NULL DEFAULT '',
				team_member_name varchar(191) NOT NULL DEFAULT '',
				team_member_email varchar(191) NOT NULL DEFAULT '',
				starts_at datetime NOT NULL,
				ends_at datetime NOT NULL,
				starts_at_utc datetime NOT NULL,
				ends_at_utc datetime NOT NULL,
				timezone varchar(64) NOT NULL DEFAULT 'UTC',
				duration_minutes smallint(5) unsigned NOT NULL DEFAULT 0,
				buffer_minutes smallint(5) unsigned NOT NULL DEFAULT 0,
				status varchar(20) NOT NULL DEFAULT 'confirmed',
				customer_name varchar(191) NOT NULL DEFAULT '',
				customer_email varchar(191) NOT NULL DEFAULT '',
				customer_phone varchar(64) NOT NULL DEFAULT '',
				details_snapshot longtext NULL,
				notes longtext NULL,
				source varchar(64) NOT NULL DEFAULT 'scheduler',
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				cancelled_at datetime NULL DEFAULT NULL,
				PRIMARY KEY (id),
				UNIQUE KEY booking_uid (booking_uid),
				KEY contact_id (contact_id),
				KEY session_id (session_id),
				KEY service_id (service_id),
				KEY member_range (team_member_id, starts_at_utc, ends_at_utc),
				KEY status_start (status, starts_at_utc),
				KEY local_start (starts_at)
			) {$charset};"
		);

		update_option( self::SCHEMA_OPTION, self::SCHEMA_STAMP, false );
	}

	/**
	 * Default scheduler settings.
	 *
	 * @return array
	 */
	public static function get_defaults() {
		return array(
			'enabled'               => 0,
			'buffer_minutes'        => 10,
			'contact_fields'        => array( 'name', 'phone', 'email' ),
			'confirmation_channels' => array( 'email', 'whatsapp' ),
			'team_members'          => array(),
			'services'              => array(),
		);
	}

	/**
	 * Read and normalize scheduler settings.
	 *
	 * @return array
	 */
	public static function get_settings() {
		$stored = get_option( self::OPTION_KEY, array() );
		$stored = is_array( $stored ) ? $stored : array();
		return self::sanitize_settings( array_merge( self::get_defaults(), $stored ) );
	}

	/**
	 * Sanitize scheduler settings into the canonical domain shape.
	 *
	 * @param mixed $input Raw settings.
	 * @return array
	 */
	public static function sanitize_settings( $input ) {
		$input    = is_array( $input ) ? $input : array();
		$defaults = self::get_defaults();
		$buffer   = isset( $input['buffer_minutes'] ) ? (int) $input['buffer_minutes'] : $defaults['buffer_minutes'];
		if ( ! in_array( $buffer, array( 5, 10, 15, 30 ), true ) ) {
			$buffer = $defaults['buffer_minutes'];
		}

		$contact_fields = self::sanitize_choice_list(
			array_key_exists( 'contact_fields', $input ) ? $input['contact_fields'] : $defaults['contact_fields'],
			array( 'name', 'phone', 'email' )
		);
		$confirmation_channels = self::sanitize_choice_list(
			array_key_exists( 'confirmation_channels', $input ) ? $input['confirmation_channels'] : $defaults['confirmation_channels'],
			array( 'email', 'whatsapp' )
		);

		$members     = array();
		$member_ids  = array();
		$raw_members = isset( $input['team_members'] ) && is_array( $input['team_members'] ) ? $input['team_members'] : array();
		foreach ( $raw_members as $index => $raw_member ) {
			if ( ! is_array( $raw_member ) ) {
				continue;
			}
			$name  = self::clean_text( isset( $raw_member['name'] ) ? $raw_member['name'] : '' );
			$email = sanitize_email( isset( $raw_member['email'] ) ? (string) $raw_member['email'] : '' );
			$id    = self::clean_id( isset( $raw_member['id'] ) ? $raw_member['id'] : '' );
			if ( '' === $id ) {
				$id = 'member-' . substr( md5( $name . '|' . $email . '|' . (string) $index ), 0, 12 );
			}
			if ( '' === $name || '' === $email || ! is_email( $email ) || isset( $member_ids[ $id ] ) ) {
				continue;
			}

			$working_input = isset( $raw_member['working_hours'] ) ? $raw_member['working_hours'] : array();
			if ( empty( $working_input ) && isset( $raw_member['workingHours'] ) ) {
				$working_input = $raw_member['workingHours'];
			}
			if ( empty( $working_input ) && isset( $raw_member['schedule'] ) ) {
				$working_input = $raw_member['schedule'];
			}
			$breaks_input = isset( $raw_member['breaks'] ) ? $raw_member['breaks'] : array();

			$working_hours = array();
			$breaks        = array();
			foreach ( self::weekdays() as $day ) {
				$day_work = is_array( $working_input ) && isset( $working_input[ $day ] ) ? $working_input[ $day ] : array();
				$day_breaks = is_array( $breaks_input ) && isset( $breaks_input[ $day ] ) ? $breaks_input[ $day ] : array();
				if ( is_array( $day_work ) && isset( $day_work['breaks'] ) ) {
					$day_breaks = $day_work['breaks'];
				}
				$working_hours[ $day ] = self::sanitize_intervals( $day_work );
				$breaks[ $day ]        = self::sanitize_intervals( $day_breaks );
			}

			$excluded_dates = array();
			$raw_dates      = isset( $raw_member['excluded_dates'] ) && is_array( $raw_member['excluded_dates'] ) ? $raw_member['excluded_dates'] : array();
			foreach ( $raw_dates as $raw_date ) {
				$date = self::sanitize_date( is_array( $raw_date ) && isset( $raw_date['date'] ) ? $raw_date['date'] : $raw_date );
				if ( '' !== $date ) {
					$excluded_dates[ $date ] = $date;
				}
			}

			$excluded_ranges = array();
			$raw_ranges      = isset( $raw_member['excluded_ranges'] ) && is_array( $raw_member['excluded_ranges'] ) ? $raw_member['excluded_ranges'] : array();
			foreach ( $raw_ranges as $raw_range ) {
				if ( ! is_array( $raw_range ) ) {
					continue;
				}
				$start = self::sanitize_date( isset( $raw_range['start'] ) ? $raw_range['start'] : '' );
				$end   = self::sanitize_date( isset( $raw_range['end'] ) ? $raw_range['end'] : $start );
				if ( '' === $start || '' === $end ) {
					continue;
				}
				if ( $end < $start ) {
					$temp  = $start;
					$start = $end;
					$end   = $temp;
				}
				$excluded_ranges[] = array(
					'start' => $start,
					'end'   => $end,
					'label' => self::clean_text( isset( $raw_range['label'] ) ? $raw_range['label'] : '' ),
				);
			}

			$members[] = array(
				'id'              => $id,
				'name'            => $name,
				'email'           => $email,
				'working_hours'   => $working_hours,
				'breaks'          => $breaks,
				'excluded_dates'  => array_values( $excluded_dates ),
				'excluded_ranges' => $excluded_ranges,
			);
			$member_ids[ $id ] = true;
		}

		$services     = array();
		$service_ids  = array();
		$raw_services = isset( $input['services'] ) && is_array( $input['services'] ) ? $input['services'] : array();
		foreach ( $raw_services as $index => $raw_service ) {
			if ( ! is_array( $raw_service ) ) {
				continue;
			}
			$name = self::clean_text( isset( $raw_service['name'] ) ? $raw_service['name'] : '' );
			$id   = self::clean_id( isset( $raw_service['id'] ) ? $raw_service['id'] : '' );
			if ( '' === $id ) {
				$id = 'service-' . substr( md5( $name . '|' . (string) $index ), 0, 12 );
			}
			if ( '' === $name || isset( $service_ids[ $id ] ) ) {
				continue;
			}

			$duration = isset( $raw_service['duration_minutes'] ) ? (int) $raw_service['duration_minutes'] : 0;
			if ( isset( $raw_service['durationMinutes'] ) ) {
				$duration = (int) $raw_service['durationMinutes'];
			}
			$duration = max( self::SLOT_INTERVAL, min( 1440, $duration ) );

			$assigned = isset( $raw_service['team_member_ids'] ) ? $raw_service['team_member_ids'] : array();
			if ( isset( $raw_service['assigned_member_ids'] ) ) {
				$assigned = $raw_service['assigned_member_ids'];
			}
			if ( isset( $raw_service['teamMemberIds'] ) ) {
				$assigned = $raw_service['teamMemberIds'];
			}
			$assigned = is_array( $assigned ) ? $assigned : array( $assigned );
			$assigned_ids = array();
			foreach ( $assigned as $assigned_id ) {
				$assigned_id = self::clean_id( $assigned_id );
				if ( '' !== $assigned_id && isset( $member_ids[ $assigned_id ] ) ) {
					$assigned_ids[ $assigned_id ] = $assigned_id;
				}
			}

			$services[] = array(
				'id'                => $id,
				'name'              => $name,
				'description'       => sanitize_textarea_field( isset( $raw_service['description'] ) ? (string) $raw_service['description'] : '' ),
				'duration_minutes'  => $duration,
				'team_member_ids'   => array_values( $assigned_ids ),
			);
			$service_ids[ $id ] = true;
		}

		return array(
			'enabled'               => ! empty( $input['enabled'] ) ? 1 : 0,
			'buffer_minutes'        => $buffer,
			'contact_fields'        => $contact_fields,
			'confirmation_channels' => $confirmation_channels,
			'team_members'          => $members,
			'services'              => $services,
		);
	}

	/**
	 * Persist canonical scheduler settings.
	 *
	 * @param mixed $input Raw settings.
	 * @return array Saved settings.
	 */
	public function update_settings( $input ) {
		$settings = self::sanitize_settings( $input );
		update_option( self::OPTION_KEY, $settings, false );
		return $settings;
	}

	/**
	 * Find one configured service.
	 *
	 * @param string $service_id Service ID.
	 * @return array|null
	 */
	public function get_service( $service_id ) {
		$service_id = self::clean_id( $service_id );
		foreach ( self::get_settings()['services'] as $service ) {
			if ( $service_id === $service['id'] ) {
				return $service;
			}
		}
		return null;
	}

	/**
	 * Find one configured team member.
	 *
	 * @param string $team_member_id Team member ID.
	 * @return array|null
	 */
	public function get_team_member( $team_member_id ) {
		$team_member_id = self::clean_id( $team_member_id );
		foreach ( self::get_settings()['team_members'] as $member ) {
			if ( $team_member_id === $member['id'] ) {
				return $member;
			}
		}
		return null;
	}

	/**
	 * Safe, public scheduler configuration for frontend localization and tools.
	 * Team member emails and private schedules are intentionally omitted.
	 *
	 * @return array
	 */
	public static function get_frontend_config() {
		$settings = self::get_settings();
		$services = array();
		foreach ( $settings['services'] as $service ) {
			$services[] = array(
				'id'              => $service['id'],
				'name'            => $service['name'],
				'description'     => $service['description'],
				'durationMinutes' => (int) $service['duration_minutes'],
				'teamMemberIds'   => array_values( $service['team_member_ids'] ),
			);
		}

		$members = array();
		foreach ( $settings['team_members'] as $member ) {
			$members[] = array(
				'id'   => $member['id'],
				'name' => $member['name'],
			);
		}

		return array(
			'enabled'       => ! empty( $settings['enabled'] ),
			'services'      => $services,
			'teamMembers'   => $members,
			'contactFields' => array_values( $settings['contact_fields'] ),
			'bufferMinutes' => (int) $settings['buffer_minutes'],
			'ajaxUrl'       => function_exists( 'admin_url' ) ? admin_url( 'admin-ajax.php' ) : '',
			'nonce'         => function_exists( 'wp_create_nonce' ) ? wp_create_nonce( 'niwa_ai_scheduler' ) : '',
		);
	}

	/**
	 * Return availability summary for every day in a site-local month.
	 *
	 * @param string $service_id    Service ID.
	 * @param string $month         YYYY-MM.
	 * @param string $team_member_id Optional member filter.
	 * @return array
	 */
	public function get_month_availability( $service_id, $month, $team_member_id = '' ) {
		$service = $this->get_service( $service_id );
		if ( ! $service ) {
			return $this->error( 'service_not_found', __( 'The selected service is not available.', 'niwa-ai' ) );
		}
		if ( ! preg_match( '/^\d{4}-\d{2}$/', (string) $month ) ) {
			return $this->error( 'invalid_month', __( 'Month must use YYYY-MM format.', 'niwa-ai' ) );
		}

		$timezone = $this->site_timezone();
		$first    = DateTimeImmutable::createFromFormat( '!Y-m-d', (string) $month . '-01', $timezone );
		if ( ! $first || $first->format( 'Y-m' ) !== (string) $month ) {
			return $this->error( 'invalid_month', __( 'The selected month is invalid.', 'niwa-ai' ) );
		}

		$last = $first->modify( 'last day of this month' );
		$days = array();
		for ( $date = $first; $date <= $last; $date = $date->modify( '+1 day' ) ) {
			$day_result = $this->get_day_availability( $service_id, $date->format( 'Y-m-d' ), $team_member_id );
			if ( isset( $day_result['error'] ) ) {
				return $day_result;
			}
			$slots       = $day_result['slots'];
			$first_slot  = ! empty( $slots ) ? $slots[0] : null;
			$member_ids  = array();
			foreach ( $slots as $slot ) {
				foreach ( $slot['team_member_ids'] as $member_id ) {
					$member_ids[ $member_id ] = $member_id;
				}
			}
			$days[] = array(
				'date'                => $date->format( 'Y-m-d' ),
				'available'           => ! empty( $slots ),
				'slot_count'          => count( $slots ),
				'first_slot'          => $first_slot ? $first_slot['time'] : '',
				'first_start_iso'     => $first_slot ? $first_slot['start_iso'] : '',
				'team_member_ids'     => array_values( $member_ids ),
			);
		}

		return array(
			'service'        => $this->public_service( $service ),
			'month'          => (string) $month,
			'timezone'       => $timezone->getName(),
			'available_days' => $days,
		);
	}

	/**
	 * Return site-local slots for one service/date.
	 *
	 * @param string $service_id     Service ID.
	 * @param string $date           YYYY-MM-DD.
	 * @param string $team_member_id Optional member filter.
	 * @return array
	 */
	public function get_day_availability( $service_id, $date, $team_member_id = '' ) {
		$this->ensure_schema();
		$service = $this->get_service( $service_id );
		if ( ! $service ) {
			return $this->error( 'service_not_found', __( 'The selected service is not available.', 'niwa-ai' ) );
		}
		$date = self::sanitize_date( $date );
		if ( '' === $date ) {
			return $this->error( 'invalid_date', __( 'Date must use YYYY-MM-DD format.', 'niwa-ai' ) );
		}

		$members = $this->members_for_service( $service, $team_member_id );
		if ( isset( $members['error'] ) ) {
			return $members;
		}

		$settings = self::get_settings();
		$by_start = array();
		foreach ( $members as $member ) {
			foreach ( $this->slots_for_member( $service, $member, $date, (int) $settings['buffer_minutes'] ) as $slot ) {
				$key = $slot['start_at'];
				if ( ! isset( $by_start[ $key ] ) ) {
					$slot['team_member_ids'] = array( $slot['team_member_id'] );
					$by_start[ $key ]         = $slot;
				} else {
					$by_start[ $key ]['team_member_ids'][] = $slot['team_member_id'];
				}
			}
		}
		ksort( $by_start, SORT_STRING );

		return array(
			'service'  => $this->public_service( $service ),
			'date'     => $date,
			'timezone' => $this->site_timezone()->getName(),
			'slots'    => array_values( $by_start ),
		);
	}

	/**
	 * Atomically book an available appointment.
	 *
	 * @param array $args Booking arguments.
	 * @return array {success,appointment}|{success:false,error:{code,message}}
	 */
	public function book_appointment( array $args ) {
		$this->ensure_schema();
		$settings = self::get_settings();
		if ( empty( $settings['enabled'] ) ) {
			return $this->failure( 'scheduler_disabled', __( 'Online scheduling is currently unavailable.', 'niwa-ai' ) );
		}

		$service = $this->get_service( isset( $args['service_id'] ) ? $args['service_id'] : '' );
		if ( ! $service ) {
			return $this->failure( 'service_not_found', __( 'The selected service is not available.', 'niwa-ai' ) );
		}

		$start_raw = isset( $args['start_at'] ) ? (string) $args['start_at'] : '';
		if ( '' === trim( $start_raw ) && isset( $args['date'], $args['time'] ) ) {
			$start_raw = (string) $args['date'] . ' ' . (string) $args['time'];
		}
		$starts_at = $this->parse_site_datetime( $start_raw );
		if ( ! $starts_at ) {
			return $this->failure( 'invalid_start', __( 'Please select a valid appointment date and time.', 'niwa-ai' ) );
		}

		$contact_fields = array_fill_keys( $settings['contact_fields'], true );
		$name  = isset( $contact_fields['name'] ) ? self::clean_text( isset( $args['name'] ) ? $args['name'] : '' ) : '';
		$email = isset( $contact_fields['email'] ) ? sanitize_email( isset( $args['email'] ) ? (string) $args['email'] : '' ) : '';
		$phone = isset( $contact_fields['phone'] ) ? self::clean_text( isset( $args['phone'] ) ? $args['phone'] : '' ) : '';
		if ( isset( $contact_fields['name'] ) && '' === $name ) {
			return $this->failure( 'name_required', __( 'Name is required to book an appointment.', 'niwa-ai' ) );
		}
		if ( isset( $contact_fields['email'] ) && ( '' === $email || ! is_email( $email ) ) ) {
			return $this->failure( 'email_required', __( 'A valid email address is required.', 'niwa-ai' ) );
		}
		if ( isset( $contact_fields['phone'] ) && '' === $phone ) {
			return $this->failure( 'phone_required', __( 'Phone number is required to book an appointment.', 'niwa-ai' ) );
		}
		$phone_digits = preg_replace( '/\D+/', '', $phone );
		if ( '' !== $phone && ( strlen( $phone_digits ) < 6 || strlen( $phone_digits ) > 20 ) ) {
			return $this->failure( 'phone_invalid', __( 'Please enter a valid phone number.', 'niwa-ai' ) );
		}

		$session_id = self::clean_session_id( isset( $args['session_id'] ) ? $args['session_id'] : '' );
		$contact_id = ( '' !== $email || '' !== $phone ) ? $this->resolve_contact( $args, $email, $name, $phone ) : 0;
		if ( ( '' !== $email || '' !== $phone ) && $contact_id <= 0 ) {
			return $this->failure( 'contact_save_failed', __( 'The contact could not be saved. Please try again.', 'niwa-ai' ) );
		}

		$requested_member = isset( $args['team_member_id'] ) ? $args['team_member_id'] : ( isset( $args['member_id'] ) ? $args['member_id'] : '' );
		$members          = $this->members_for_service( $service, $requested_member );
		if ( isset( $members['error'] ) ) {
			return array(
				'success' => false,
				'error'   => $members['error'],
			);
		}

		$last_unavailable = null;
		foreach ( $members as $member ) {
			$result = $this->book_for_member(
				$service,
				$member,
				$starts_at,
				array(
					'contact_id'          => $contact_id,
					'session_id'          => $session_id,
					'lead_form_session_id'=> isset( $args['lead_form_session_id'] ) ? max( 0, (int) $args['lead_form_session_id'] ) : 0,
					'name'                => $name,
					'email'               => $email,
					'phone'               => $phone,
					'notes'               => isset( $args['notes'] ) ? sanitize_textarea_field( (string) $args['notes'] ) : '',
					'source'              => self::clean_id( isset( $args['source'] ) ? $args['source'] : 'scheduler' ),
					'buffer_minutes'      => (int) $settings['buffer_minutes'],
				)
			);
			if ( ! empty( $result['success'] ) ) {
				$appointment = $result['appointment'];
				$summary     = '';
				if ( '' !== $session_id ) {
					$summary = (string) apply_filters(
						'niwa_ai_scheduler_verified_conversation_summary',
						'',
						$session_id,
						$appointment
					);
					$summary = sanitize_textarea_field( $summary );
				}
				if ( '' !== trim( $summary ) ) {
					if ( $contact_id > 0 ) {
						$this->update_contact_conversation_summary( $contact_id, $summary, $session_id );
					}
					$this->update_details_snapshot( (int) $appointment['id'], $summary );
					$refreshed = $this->get_appointment( (int) $appointment['id'] );
					if ( is_array( $refreshed ) ) {
						$appointment = $refreshed;
					}
				}

				do_action( 'niwa_ai_appointment_booked', $appointment, $session_id, $contact_id );
				return array(
					'success'     => true,
					'appointment' => $appointment,
				);
			}

			$last_unavailable = $result;
			if ( empty( $result['error']['code'] ) || ! in_array( $result['error']['code'], array( 'slot_unavailable', 'lock_timeout' ), true ) ) {
				return $result;
			}
		}

		return $last_unavailable ?: $this->failure( 'slot_unavailable', __( 'That time is no longer available. Please choose another slot.', 'niwa-ai' ) );
	}

	/**
	 * Fetch one appointment by numeric ID or public booking UID.
	 *
	 * @param int|string $id_or_uid Appointment ID or UID.
	 * @return array|null
	 */
	public function get_appointment( $id_or_uid ) {
		$this->ensure_schema();
		global $wpdb;
		$table    = $this->appointments_table();
		$contacts = $wpdb->prefix . 'niwa_ai_contacts';
		if ( is_numeric( $id_or_uid ) ) {
			$sql = $wpdb->prepare(
				"SELECT a.*, c.details AS contact_details, c.conversation_summary
				 FROM {$table} a LEFT JOIN {$contacts} c ON c.id = a.contact_id
				 WHERE a.id = %d LIMIT 1",
				(int) $id_or_uid
			);
		} else {
			$sql = $wpdb->prepare(
				"SELECT a.*, c.details AS contact_details, c.conversation_summary
				 FROM {$table} a LEFT JOIN {$contacts} c ON c.id = a.contact_id
				 WHERE a.booking_uid = %s LIMIT 1",
				(string) $id_or_uid
			);
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( $sql, ARRAY_A );
		return is_array( $row ) ? $row : null;
	}

	/**
	 * List appointments intersecting a site-local date range (inclusive).
	 *
	 * @param string $start_date YYYY-MM-DD.
	 * @param string $end_date   YYYY-MM-DD.
	 * @param array  $filters    status, service_id, team_member_id, contact_id, session_id.
	 * @param int    $limit      Result cap.
	 * @param int    $offset     Result offset.
	 * @return array
	 */
	public function get_appointments_for_range( $start_date, $end_date, array $filters = array(), $limit = 1000, $offset = 0 ) {
		$this->ensure_schema();
		$start_date = self::sanitize_date( $start_date );
		$end_date   = self::sanitize_date( $end_date );
		if ( '' === $start_date || '' === $end_date ) {
			return array();
		}
		if ( $end_date < $start_date ) {
			$temp       = $start_date;
			$start_date = $end_date;
			$end_date   = $temp;
		}

		$timezone = $this->site_timezone();
		$utc      = new DateTimeZone( 'UTC' );
		$start    = DateTimeImmutable::createFromFormat( '!Y-m-d', $start_date, $timezone );
		$end      = DateTimeImmutable::createFromFormat( '!Y-m-d', $end_date, $timezone )->modify( '+1 day' );

		global $wpdb;
		$table      = $this->appointments_table();
		$contacts   = $wpdb->prefix . 'niwa_ai_contacts';
		$where      = array( 'a.starts_at_utc < %s', 'a.ends_at_utc > %s' );
		$parameters = array( $end->setTimezone( $utc )->format( 'Y-m-d H:i:s' ), $start->setTimezone( $utc )->format( 'Y-m-d H:i:s' ) );

		$filter_specs = array(
			'status'         => 'a.status',
			'service_id'     => 'a.service_id',
			'team_member_id' => 'a.team_member_id',
			'session_id'     => 'a.session_id',
		);
		foreach ( $filter_specs as $key => $column ) {
			if ( isset( $filters[ $key ] ) && '' !== (string) $filters[ $key ] ) {
				$where[]      = $column . ' = %s';
				$parameters[] = (string) $filters[ $key ];
			}
		}
		if ( ! empty( $filters['contact_id'] ) ) {
			$where[]      = 'a.contact_id = %d';
			$parameters[] = (int) $filters['contact_id'];
		}
		if ( isset( $filters['search'] ) && '' !== trim( (string) $filters['search'] ) ) {
			$like = '%' . $wpdb->esc_like( trim( (string) $filters['search'] ) ) . '%';
			$where[] = '(a.customer_name LIKE %s OR a.customer_email LIKE %s OR a.customer_phone LIKE %s OR a.service_name LIKE %s OR a.team_member_name LIKE %s OR c.details LIKE %s OR c.conversation_summary LIKE %s)';
			for ( $index = 0; $index < 7; $index++ ) {
				$parameters[] = $like;
			}
		}

		$limit        = max( 1, min( 5000, (int) $limit ) );
		$offset       = max( 0, (int) $offset );
		$parameters[] = $limit;
		$parameters[] = $offset;
		$sql          = "SELECT a.*, c.details AS contact_details, c.conversation_summary
			FROM {$table} a LEFT JOIN {$contacts} c ON c.id = a.contact_id
			WHERE " . implode( ' AND ', $where ) . ' ORDER BY a.starts_at ASC, a.id ASC LIMIT %d OFFSET %d';
		$sql          = $wpdb->prepare( $sql, $parameters );
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * List recent appointments for one contact.
	 *
	 * @param int $contact_id Contact ID.
	 * @param int $limit      Result cap.
	 * @return array
	 */
	public function get_contact_appointments( $contact_id, $limit = 50 ) {
		$this->ensure_schema();
		global $wpdb;
		$table    = $this->appointments_table();
		$contacts = $wpdb->prefix . 'niwa_ai_contacts';
		$limit    = max( 1, min( 500, (int) $limit ) );
		$sql      = $wpdb->prepare(
			"SELECT a.*, c.details AS contact_details, c.conversation_summary
			 FROM {$table} a LEFT JOIN {$contacts} c ON c.id = a.contact_id
			 WHERE a.contact_id = %d ORDER BY a.starts_at DESC, a.id DESC LIMIT %d",
			(int) $contact_id,
			$limit
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Alias used by admin/agent consumers.
	 *
	 * @param int $contact_id Contact ID.
	 * @param int $limit      Result cap.
	 * @return array
	 */
	public function get_appointments_for_contact( $contact_id, $limit = 50 ) {
		return $this->get_contact_appointments( $contact_id, $limit );
	}

	/**
	 * Fetch latest appointment for a contact.
	 *
	 * @param int $contact_id Contact ID.
	 * @return array|null
	 */
	public function get_latest_appointment_for_contact( $contact_id ) {
		$this->ensure_schema();
		global $wpdb;
		$table    = $this->appointments_table();
		$contacts = $wpdb->prefix . 'niwa_ai_contacts';
		$now      = current_time( 'mysql' );
		$sql      = $wpdb->prepare(
			"SELECT a.*, c.details AS contact_details, c.conversation_summary
			 FROM {$table} a LEFT JOIN {$contacts} c ON c.id = a.contact_id
			 WHERE a.contact_id = %d AND a.status NOT IN (%s, %s)
			 ORDER BY
				CASE WHEN a.starts_at >= %s THEN 0 ELSE 1 END ASC,
				CASE WHEN a.starts_at >= %s THEN a.starts_at ELSE NULL END ASC,
				CASE WHEN a.starts_at < %s THEN a.starts_at ELSE NULL END DESC,
				a.id DESC
			 LIMIT 1",
			(int) $contact_id,
			self::STATUS_CANCELLED,
			self::STATUS_NO_SHOW,
			$now,
			$now,
			$now
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( $sql, ARRAY_A );
		return is_array( $row ) ? $row : null;
	}

	/**
	 * Fetch canonical contact-level CRM details and summary.
	 *
	 * @param int $contact_id Contact ID.
	 * @return array|null
	 */
	public function get_contact_profile( $contact_id ) {
		$contacts = $this->get_contact_service();
		if ( ! $contacts || ! method_exists( $contacts, 'get_contact' ) ) {
			return null;
		}
		return $contacts->get_contact( (int) $contact_id );
	}

	/**
	 * Update editable contact-level CRM details.
	 *
	 * @param int    $contact_id Contact ID.
	 * @param string $details    Manual CRM details.
	 * @param int    $updated_by Reserved for audit integration.
	 * @return bool
	 */
	public function update_contact_details( $contact_id, $details, $updated_by = 0 ) {
		unset( $updated_by );
		$contacts = $this->get_contact_service();
		return $contacts && method_exists( $contacts, 'update_contact_details' )
			? (bool) $contacts->update_contact_details( (int) $contact_id, $details )
			: false;
	}

	/**
	 * Apply a server-produced conversation summary without overwriting manual details.
	 *
	 * @param int    $contact_id Contact ID.
	 * @param string $summary    Verified session summary.
	 * @param string $session_id Associated session ID (stored on appointment rows).
	 * @return bool
	 */
	public function update_contact_conversation_summary( $contact_id, $summary, $session_id = '' ) {
		unset( $session_id );
		$contacts = $this->get_contact_service();
		return $contacts && method_exists( $contacts, 'apply_conversation_summary' )
			? (bool) $contacts->apply_conversation_summary( (int) $contact_id, $summary )
			: false;
	}

	/**
	 * Update booking-specific notes.
	 *
	 * @param int    $appointment_id Appointment ID.
	 * @param string $notes          Internal booking notes.
	 * @return bool
	 */
	public function update_appointment_details( $appointment_id, $notes ) {
		$this->ensure_schema();
		global $wpdb;
		$result = $wpdb->update(
			$this->appointments_table(),
			array(
				'notes'      => sanitize_textarea_field( (string) $notes ),
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => (int) $appointment_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
		if ( false === $result ) {
			return false;
		}
		$appointment = $this->get_appointment( (int) $appointment_id );
		if ( is_array( $appointment ) ) {
			do_action( 'niwa_ai_appointment_changed', $appointment );
		}
		return true;
	}

	/**
	 * Update appointment lifecycle status.
	 *
	 * @param int    $appointment_id Appointment ID.
	 * @param string $status         New status.
	 * @return bool
	 */
	public function update_appointment_status( $appointment_id, $status ) {
		$this->ensure_schema();
		$status = self::clean_id( $status );
		if ( ! in_array( $status, self::valid_statuses(), true ) ) {
			return false;
		}
		global $wpdb;
		$data = array(
			'status'     => $status,
			'updated_at' => current_time( 'mysql' ),
		);
		$formats = array( '%s', '%s' );
		if ( self::STATUS_CANCELLED === $status ) {
			$data['cancelled_at'] = current_time( 'mysql' );
			$formats[]            = '%s';
		} else {
			$data['cancelled_at'] = null;
			$formats[]            = null;
		}
		$result = $wpdb->update(
			$this->appointments_table(),
			$data,
			array( 'id' => (int) $appointment_id ),
			$formats,
			array( '%d' )
		);
		if ( false === $result ) {
			return false;
		}
		$appointment = $this->get_appointment( (int) $appointment_id );
		if ( is_array( $appointment ) ) {
			do_action( 'niwa_ai_appointment_changed', $appointment );
		}
		return true;
	}

	/**
	 * Generate all free slots for a member/date.
	 *
	 * @param array  $service Service definition.
	 * @param array  $member  Member definition.
	 * @param string $date    YYYY-MM-DD.
	 * @param int    $buffer  Buffer minutes.
	 * @return array
	 */
	private function slots_for_member( array $service, array $member, $date, $buffer ) {
		$timezone = $this->site_timezone();
		$day      = DateTimeImmutable::createFromFormat( '!Y-m-d', $date, $timezone );
		if ( ! $day || $this->is_date_excluded( $member, $date ) ) {
			return array();
		}
		$weekday = strtolower( $day->format( 'l' ) );
		$windows = isset( $member['working_hours'][ $weekday ] ) ? $member['working_hours'][ $weekday ] : array();
		if ( empty( $windows ) ) {
			return array();
		}

		$slots            = array();
		$existing_bookings = $this->get_member_bookings_for_local_day( $member['id'], $day, $buffer );
		$external_busy     = $this->get_external_calendar_busy_periods(
			$member['id'],
			$day,
			$day->modify( '+1 day +' . max( 0, (int) $buffer ) . ' minutes' ),
			false
		);
		foreach ( $windows as $window ) {
			$window_start = $this->local_date_time( $date, $window['start'] );
			$window_end   = $this->local_date_time( $date, $window['end'] );
			if ( ! $window_start || ! $window_end ) {
				continue;
			}
			// Admin time inputs can contain arbitrary minutes (for example 09:11),
			// while bookable starts use a five-minute grid. Start from the first
			// grid-aligned instant inside the working window instead of rejecting
			// every candidate produced from an off-grid window start.
			$first_candidate = $this->ceil_to_slot_interval( $window_start );
			for ( $candidate = $first_candidate; $candidate < $window_end; $candidate = $candidate->modify( '+' . self::SLOT_INTERVAL . ' minutes' ) ) {
				$check = $this->candidate_availability( $service, $member, $candidate, $buffer, false, $existing_bookings, $external_busy );
				if ( empty( $check['available'] ) ) {
					continue;
				}
				$end     = $candidate->modify( '+' . (int) $service['duration_minutes'] . ' minutes' );
				$slots[] = array(
					'start_at'         => $candidate->format( 'Y-m-d H:i:s' ),
					'end_at'           => $end->format( 'Y-m-d H:i:s' ),
					'start_iso'        => $candidate->format( DateTimeInterface::ATOM ),
					'end_iso'          => $end->format( DateTimeInterface::ATOM ),
					'time'             => $candidate->format( 'H:i' ),
					'label'            => $candidate->format( 'H:i' ),
					'team_member_id'   => $member['id'],
					'team_member_name' => $member['name'],
				);
			}
		}
		return $slots;
	}

	/**
	 * Check schedule, exclusions, breaks, past time, and current bookings.
	 *
	 * @param array             $service    Service definition.
	 * @param array             $member     Member definition.
	 * @param DateTimeImmutable $starts_at  Site-local start.
	 * @param int               $buffer     Buffer minutes.
	 * @param bool              $for_update Lock overlapping rows in the current transaction.
	 * @param array|null        $existing_bookings Optional preloaded rows for read-only slot generation.
	 * @param array             $external_busy     Preloaded external calendar busy periods.
	 * @return array
	 */
	private function candidate_availability( array $service, array $member, DateTimeImmutable $starts_at, $buffer, $for_update, $existing_bookings = null, array $external_busy = array() ) {
		$now = new DateTimeImmutable( 'now', $this->site_timezone() );
		if ( $starts_at <= $now ) {
			return array( 'available' => false, 'reason' => 'past' );
		}
		if ( 0 !== ( (int) $starts_at->format( 'i' ) % self::SLOT_INTERVAL ) || '00' !== $starts_at->format( 's' ) ) {
			return array( 'available' => false, 'reason' => 'interval' );
		}

		$date = $starts_at->format( 'Y-m-d' );
		if ( $this->is_date_excluded( $member, $date ) ) {
			return array( 'available' => false, 'reason' => 'excluded' );
		}

		$ends_at = $starts_at->modify( '+' . (int) $service['duration_minutes'] . ' minutes' );
		$weekday = strtolower( $starts_at->format( 'l' ) );
		$windows = isset( $member['working_hours'][ $weekday ] ) ? $member['working_hours'][ $weekday ] : array();
		$inside  = false;
		foreach ( $windows as $window ) {
			$window_start = $this->local_date_time( $date, $window['start'] );
			$window_end   = $this->local_date_time( $date, $window['end'] );
			if ( $window_start && $window_end && $starts_at >= $window_start && $ends_at <= $window_end ) {
				$inside = true;
				break;
			}
		}
		if ( ! $inside ) {
			return array( 'available' => false, 'reason' => 'outside_working_hours' );
		}

		$breaks = isset( $member['breaks'][ $weekday ] ) ? $member['breaks'][ $weekday ] : array();
		foreach ( $breaks as $break ) {
			$break_start = $this->local_date_time( $date, $break['start'] );
			$break_end   = $this->local_date_time( $date, $break['end'] );
			if ( $break_start && $break_end && $starts_at < $break_end && $ends_at > $break_start ) {
				return array( 'available' => false, 'reason' => 'break' );
			}
		}

		$conflicts = is_array( $existing_bookings )
			? $this->find_conflicts_in_rows( $existing_bookings, $starts_at, $ends_at, $buffer )
			: $this->find_conflicts( $member['id'], $starts_at, $ends_at, $buffer, $for_update );
		if ( ! empty( $conflicts ) ) {
			return array( 'available' => false, 'reason' => 'booked' );
		}
		if ( $this->has_external_calendar_conflict( $external_busy, $starts_at, $ends_at, $buffer ) ) {
			return array( 'available' => false, 'reason' => 'external_calendar_busy' );
		}

		return array( 'available' => true, 'ends_at' => $ends_at );
	}

	/**
	 * Attempt one transactionally guarded member booking.
	 *
	 * @param array             $service Service definition.
	 * @param array             $member  Member definition.
	 * @param DateTimeImmutable $start   Site-local start.
	 * @param array             $data    Sanitized booking data.
	 * @return array
	 */
	private function book_for_member( array $service, array $member, DateTimeImmutable $start, array $data ) {
		global $wpdb;
		// Serialize per member rather than per date: a late appointment's buffer
		// can cross midnight and conflict with the next day's first slot.
		$lock_name = 'niwa_app_' . sha1( $this->appointments_table() . '|' . $member['id'] );
		$locked    = $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, %d)', $lock_name, self::LOCK_WAIT_SECONDS ) );
		if ( '1' !== (string) $locked ) {
			return $this->failure( 'lock_timeout', __( 'Scheduling is busy. Please try again.', 'niwa-ai' ) );
		}

		$transaction_open = false;
		try {
			$external_busy = $this->get_external_calendar_busy_periods(
				$member['id'],
				$start,
				$start->modify( '+' . ( (int) $service['duration_minutes'] + max( 0, (int) $data['buffer_minutes'] ) ) . ' minutes' ),
				true
			);
			$wpdb->query( 'START TRANSACTION' );
			$transaction_open = true;
			$check            = $this->candidate_availability( $service, $member, $start, (int) $data['buffer_minutes'], true, null, $external_busy );
			if ( empty( $check['available'] ) ) {
				$wpdb->query( 'ROLLBACK' );
				$transaction_open = false;
				return $this->failure( 'slot_unavailable', __( 'That time is no longer available. Please choose another slot.', 'niwa-ai' ) );
			}

			$end         = $check['ends_at'];
			$utc         = new DateTimeZone( 'UTC' );
			$now         = current_time( 'mysql' );
			$booking_uid = $this->generate_booking_uid();
			$appointment_data = array(
				'booking_uid'          => $booking_uid,
				'contact_id'           => (int) $data['contact_id'],
				'session_id'           => $data['session_id'],
				'lead_form_session_id' => (int) $data['lead_form_session_id'],
				'service_id'           => $service['id'],
				'service_name'         => $service['name'],
				'team_member_id'       => $member['id'],
				'team_member_name'     => $member['name'],
				'team_member_email'    => $member['email'],
				'starts_at'            => $start->format( 'Y-m-d H:i:s' ),
				'ends_at'              => $end->format( 'Y-m-d H:i:s' ),
				'starts_at_utc'        => $start->setTimezone( $utc )->format( 'Y-m-d H:i:s' ),
				'ends_at_utc'          => $end->setTimezone( $utc )->format( 'Y-m-d H:i:s' ),
				'timezone'             => $this->site_timezone()->getName(),
				'duration_minutes'     => (int) $service['duration_minutes'],
				'buffer_minutes'       => (int) $data['buffer_minutes'],
				'status'               => self::STATUS_CONFIRMED,
				'customer_name'        => $data['name'],
				'customer_email'       => $data['email'],
				'customer_phone'       => $data['phone'],
				'notes'                => $data['notes'],
				'source'               => '' !== $data['source'] ? $data['source'] : 'scheduler',
				'created_at'           => $now,
				'updated_at'           => $now,
			);
			$inserted = $wpdb->insert(
				$this->appointments_table(),
				$appointment_data,
				array( '%s', '%d', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
			);
			if ( false === $inserted ) {
				$wpdb->query( 'ROLLBACK' );
				$transaction_open = false;
				return $this->failure( 'database_error', __( 'The appointment could not be saved. Please try again.', 'niwa-ai' ) );
			}

			$appointment_id = (int) $wpdb->insert_id;
			$wpdb->query( 'COMMIT' );
			$transaction_open = false;
			$appointment = $this->get_appointment( $appointment_id );
			if ( ! is_array( $appointment ) ) {
				// The booking is already committed. Return the canonical insert data
				// instead of throwing a TypeError that invites a duplicate retry.
				$appointment = array_merge(
					array( 'id' => $appointment_id ),
					$appointment_data,
					array( 'contact_details' => '', 'conversation_summary' => '' )
				);
			}
			return array(
				'success'     => true,
				'appointment' => $appointment,
			);
		} catch ( Throwable $error ) {
			if ( $transaction_open ) {
				$wpdb->query( 'ROLLBACK' );
			}
			return $this->failure( 'database_error', __( 'The appointment could not be saved. Please try again.', 'niwa-ai' ) );
		} finally {
			$wpdb->get_var( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) );
		}
	}

	/**
	 * Find non-cancelled bookings that overlap service duration plus buffer.
	 *
	 * @param string            $member_id Team member ID.
	 * @param DateTimeImmutable $start     Candidate start.
	 * @param DateTimeImmutable $end       Candidate service end.
	 * @param int               $buffer    New booking buffer.
	 * @param bool              $for_update Add FOR UPDATE.
	 * @return array
	 */
	private function find_conflicts( $member_id, DateTimeImmutable $start, DateTimeImmutable $end, $buffer, $for_update ) {
		global $wpdb;
		$utc        = new DateTimeZone( 'UTC' );
		$block_end  = $end->modify( '+' . max( 0, (int) $buffer ) . ' minutes' )->setTimezone( $utc )->format( 'Y-m-d H:i:s' );
		$start_utc  = $start->setTimezone( $utc )->format( 'Y-m-d H:i:s' );
		$table      = $this->appointments_table();
		$sql        = $wpdb->prepare(
			"SELECT id, starts_at_utc, ends_at_utc, buffer_minutes FROM {$table}
			 WHERE team_member_id = %s
			 AND status NOT IN (%s, %s)
			 AND starts_at_utc < %s
			 AND DATE_ADD(ends_at_utc, INTERVAL buffer_minutes MINUTE) > %s",
			$member_id,
			self::STATUS_CANCELLED,
			self::STATUS_NO_SHOW,
			$block_end,
			$start_utc
		);
		if ( $for_update ) {
			$sql .= ' FOR UPDATE';
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Load all bookings that could overlap any candidate on one local day.
	 * Slot generation then evaluates them in memory instead of issuing one SQL
	 * query for every five-minute candidate.
	 *
	 * @param string            $member_id Team member ID.
	 * @param DateTimeImmutable $day       Site-local day at midnight.
	 * @param int               $buffer    New booking buffer.
	 * @return array
	 */
	private function get_member_bookings_for_local_day( $member_id, DateTimeImmutable $day, $buffer ) {
		global $wpdb;
		$utc         = new DateTimeZone( 'UTC' );
		$range_start = $day->setTimezone( $utc )->format( 'Y-m-d H:i:s' );
		$range_end   = $day->modify( '+1 day +' . max( 0, (int) $buffer ) . ' minutes' )->setTimezone( $utc )->format( 'Y-m-d H:i:s' );
		$table       = $this->appointments_table();
		$sql         = $wpdb->prepare(
			"SELECT starts_at_utc, ends_at_utc, buffer_minutes FROM {$table}
			 WHERE team_member_id = %s
			 AND status NOT IN (%s, %s)
			 AND starts_at_utc < %s
			 AND DATE_ADD(ends_at_utc, INTERVAL buffer_minutes MINUTE) > %s",
			$member_id,
			self::STATUS_CANCELLED,
			self::STATUS_NO_SHOW,
			$range_end,
			$range_start
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $sql, ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Check one candidate against preloaded booking rows.
	 *
	 * @param array             $rows   Existing bookings.
	 * @param DateTimeImmutable $start  Candidate start.
	 * @param DateTimeImmutable $end    Candidate service end.
	 * @param int               $buffer New booking buffer.
	 * @return array Conflicting rows.
	 */
	private function find_conflicts_in_rows( array $rows, DateTimeImmutable $start, DateTimeImmutable $end, $buffer ) {
		$utc             = new DateTimeZone( 'UTC' );
		$candidate_start = $start->setTimezone( $utc )->getTimestamp();
		$candidate_end   = $end->modify( '+' . max( 0, (int) $buffer ) . ' minutes' )->setTimezone( $utc )->getTimestamp();
		$conflicts       = array();
		foreach ( $rows as $row ) {
			try {
				$row_start = ( new DateTimeImmutable( (string) $row['starts_at_utc'], $utc ) )->getTimestamp();
				$row_end   = ( new DateTimeImmutable( (string) $row['ends_at_utc'], $utc ) )->modify( '+' . max( 0, (int) $row['buffer_minutes'] ) . ' minutes' )->getTimestamp();
			} catch ( Exception $error ) {
				continue;
			}
			if ( $row_start < $candidate_end && $row_end > $candidate_start ) {
				$conflicts[] = $row;
			}
		}
		return $conflicts;
	}

	/** Load external busy periods once per availability range. */
	private function get_external_calendar_busy_periods( $member_id, DateTimeImmutable $start, DateTimeImmutable $end, $fresh ) {
		$utc = new DateTimeZone( 'UTC' );
		$periods = apply_filters(
			'niwa_ai_external_calendar_busy_periods',
			array(),
			(string) $member_id,
			$start->setTimezone( $utc ),
			$end->setTimezone( $utc ),
			(bool) $fresh
		);
		return is_array( $periods ) ? $periods : array();
	}

	/** Check a candidate service plus its buffer against external calendar rows. */
	private function has_external_calendar_conflict( array $periods, DateTimeImmutable $start, DateTimeImmutable $end, $buffer ) {
		$candidate_start = $start->getTimestamp();
		$candidate_end   = $end->modify( '+' . max( 0, (int) $buffer ) . ' minutes' )->getTimestamp();
		foreach ( $periods as $period ) {
			if ( ! is_array( $period ) ) {
				continue;
			}
			$period_start = $this->external_calendar_timestamp( $period['start'] ?? null );
			$period_end   = $this->external_calendar_timestamp( $period['end'] ?? null );
			if ( null !== $period_start && null !== $period_end && $period_start < $candidate_end && $period_end > $candidate_start ) {
				return true;
			}
		}
		return false;
	}

	/** Normalize a calendar timestamp without assuming one provider's wire format. */
	private function external_calendar_timestamp( $value ) {
		if ( is_int( $value ) || ( is_string( $value ) && '' !== $value && is_numeric( $value ) ) ) {
			return (int) $value;
		}
		if ( '' === trim( (string) $value ) ) {
			return null;
		}
		try {
			return ( new DateTimeImmutable( (string) $value ) )->getTimestamp();
		} catch ( Exception $error ) {
			return null;
		}
	}

	/**
	 * Resolve a contact using an explicitly provided valid ID or contact capture.
	 *
	 * @param array  $args  Booking args.
	 * @param string $email Email.
	 * @param string $name  Name.
	 * @param string $phone Phone.
	 * @return int
	 */
	private function resolve_contact( array $args, $email, $name, $phone ) {
		$contacts = $this->get_contact_service();
		if ( ! $contacts ) {
			return 0;
		}
		if ( ! empty( $args['contact_id'] ) && method_exists( $contacts, 'get_contact' ) ) {
			$contact = $contacts->get_contact( (int) $args['contact_id'] );
			if ( is_array( $contact ) ) {
				return (int) $contact['id'];
			}
		}
		if ( ! method_exists( $contacts, 'save_contact' ) ) {
			return 0;
		}
		$result = $contacts->save_contact( $email, $name, $phone, 'scheduler' );
		return ! empty( $result['success'] ) && ! empty( $result['id'] ) ? (int) $result['id'] : 0;
	}

	/**
	 * Members assigned to a service, optionally narrowed to one requested member.
	 *
	 * @param array  $service           Service definition.
	 * @param string $requested_member  Optional member ID.
	 * @return array
	 */
	private function members_for_service( array $service, $requested_member = '' ) {
		$settings          = self::get_settings();
		$requested_member  = self::clean_id( $requested_member );
		$assigned          = array_fill_keys( $service['team_member_ids'], true );
		if ( '' !== $requested_member && ! isset( $assigned[ $requested_member ] ) ) {
			return $this->error( 'member_not_assigned', __( 'The selected team member does not provide this service.', 'niwa-ai' ) );
		}
		$members = array();
		foreach ( $settings['team_members'] as $member ) {
			if ( ! isset( $assigned[ $member['id'] ] ) ) {
				continue;
			}
			if ( '' !== $requested_member && $requested_member !== $member['id'] ) {
				continue;
			}
			$members[] = $member;
		}
		if ( empty( $members ) ) {
			return $this->error( 'no_team_members', __( 'No team member is assigned to this service.', 'niwa-ai' ) );
		}
		return $members;
	}

	/**
	 * Whether a member is unavailable for the full local date.
	 *
	 * @param array  $member Member definition.
	 * @param string $date   YYYY-MM-DD.
	 * @return bool
	 */
	private function is_date_excluded( array $member, $date ) {
		if ( in_array( $date, $member['excluded_dates'], true ) ) {
			return true;
		}
		foreach ( $member['excluded_ranges'] as $range ) {
			if ( $date >= $range['start'] && $date <= $range['end'] ) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Update the booking's immutable-at-booking CRM snapshot.
	 *
	 * @param int    $appointment_id Appointment ID.
	 * @param string $summary        Verified summary.
	 * @return bool
	 */
	private function update_details_snapshot( $appointment_id, $summary ) {
		global $wpdb;
		$result = $wpdb->update(
			$this->appointments_table(),
			array(
				'details_snapshot' => sanitize_textarea_field( (string) $summary ),
				'updated_at'       => current_time( 'mysql' ),
			),
			array( 'id' => (int) $appointment_id ),
			array( '%s', '%s' ),
			array( '%d' )
		);
		return false !== $result;
	}

	/**
	 * Lazily build the contact service so this class remains easy to compose.
	 *
	 * @return Niwa_AI_Contact_Service|null
	 */
	private function get_contact_service() {
		if ( $this->contact_service ) {
			return $this->contact_service;
		}
		if ( class_exists( 'Niwa_AI_Contact_Service' ) ) {
			$this->contact_service = new Niwa_AI_Contact_Service();
		}
		return $this->contact_service;
	}

	/**
	 * Site timezone object, including DST rules where configured.
	 *
	 * @return DateTimeZone
	 */
	private function site_timezone() {
		if ( function_exists( 'wp_timezone' ) ) {
			return wp_timezone();
		}
		$timezone_string = (string) get_option( 'timezone_string', '' );
		if ( '' !== $timezone_string ) {
			try {
				return new DateTimeZone( $timezone_string );
			} catch ( Exception $error ) {
				// Fall through to UTC.
			}
		}
		return new DateTimeZone( 'UTC' );
	}

	/**
	 * Parse a browser/admin value as site-local time unless it has an offset.
	 *
	 * @param string $value Datetime input.
	 * @return DateTimeImmutable|null
	 */
	private function parse_site_datetime( $value ) {
		$value = trim( (string) $value );
		if ( '' === $value ) {
			return null;
		}
		$timezone = $this->site_timezone();
		$formats  = array( '!Y-m-d H:i:s', '!Y-m-d H:i', '!Y-m-d\TH:i:s', '!Y-m-d\TH:i' );
		foreach ( $formats as $format ) {
			$date   = DateTimeImmutable::createFromFormat( $format, $value, $timezone );
			$errors = DateTimeImmutable::getLastErrors();
			if ( $date && ( false === $errors || ( 0 === $errors['warning_count'] && 0 === $errors['error_count'] ) ) ) {
				return $date;
			}
		}
		try {
			$date = new DateTimeImmutable( $value, $timezone );
			return $date->setTimezone( $timezone );
		} catch ( Exception $error ) {
			return null;
		}
	}

	/**
	 * Build one local datetime; supports 24:00 as next-day midnight.
	 *
	 * @param string $date YYYY-MM-DD.
	 * @param string $time HH:MM.
	 * @return DateTimeImmutable|null
	 */
	private function local_date_time( $date, $time ) {
		if ( '24:00' === $time ) {
			$base = DateTimeImmutable::createFromFormat( '!Y-m-d', $date, $this->site_timezone() );
			return $base ? $base->modify( '+1 day' ) : null;
		}
		return $this->parse_site_datetime( $date . ' ' . $time );
	}

	/**
	 * Move a local datetime to the next scheduler-grid boundary.
	 *
	 * Already aligned values are returned unchanged. This keeps legacy and
	 * manually typed working-hour values usable without expanding availability
	 * outside the configured window.
	 *
	 * @param DateTimeImmutable $date_time Site-local datetime.
	 * @return DateTimeImmutable
	 */
	private function ceil_to_slot_interval( DateTimeImmutable $date_time ) {
		$interval_seconds = self::SLOT_INTERVAL * MINUTE_IN_SECONDS;
		$seconds_in_hour  = ( (int) $date_time->format( 'i' ) * MINUTE_IN_SECONDS ) + (int) $date_time->format( 's' );
		$remainder        = $seconds_in_hour % $interval_seconds;
		if ( 0 === $remainder ) {
			return $date_time;
		}

		return $date_time->modify( '+' . ( $interval_seconds - $remainder ) . ' seconds' );
	}

	/**
	 * Public service representation.
	 *
	 * @param array $service Service definition.
	 * @return array
	 */
	private function public_service( array $service ) {
		return array(
			'id'               => $service['id'],
			'name'             => $service['name'],
			'description'      => $service['description'],
			'duration_minutes' => (int) $service['duration_minutes'],
			'team_member_ids'  => array_values( $service['team_member_ids'] ),
		);
	}

	/**
	 * UUID suitable for a public booking reference.
	 *
	 * @return string
	 */
	private function generate_booking_uid() {
		if ( function_exists( 'wp_generate_uuid4' ) ) {
			return wp_generate_uuid4();
		}
		try {
			$bytes       = random_bytes( 16 );
			$bytes[6]    = chr( ( ord( $bytes[6] ) & 0x0f ) | 0x40 );
			$bytes[8]    = chr( ( ord( $bytes[8] ) & 0x3f ) | 0x80 );
			$hex         = bin2hex( $bytes );
			return substr( $hex, 0, 8 ) . '-' . substr( $hex, 8, 4 ) . '-' . substr( $hex, 12, 4 ) . '-' . substr( $hex, 16, 4 ) . '-' . substr( $hex, 20 );
		} catch ( Exception $error ) {
			return uniqid( 'niwa-', true );
		}
	}

	/** @return array */
	private static function weekdays() {
		return array( 'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday' );
	}

	/** @return array */
	private static function valid_statuses() {
		return array( self::STATUS_PENDING, self::STATUS_CONFIRMED, self::STATUS_CANCELLED, self::STATUS_COMPLETED, self::STATUS_NO_SHOW );
	}

	/**
	 * Sanitize and sort a list of time intervals.
	 *
	 * @param mixed $raw Raw intervals or one interval.
	 * @return array
	 */
	private static function sanitize_intervals( $raw ) {
		if ( ! is_array( $raw ) || empty( $raw ) ) {
			return array();
		}
		if ( isset( $raw['enabled'] ) && empty( $raw['enabled'] ) ) {
			return array();
		}
		if ( isset( $raw['start'] ) || isset( $raw['end'] ) ) {
			$raw = array( $raw );
		}
		$intervals = array();
		foreach ( $raw as $interval ) {
			if ( ! is_array( $interval ) || ( isset( $interval['enabled'] ) && empty( $interval['enabled'] ) ) ) {
				continue;
			}
			$start = self::sanitize_time( isset( $interval['start'] ) ? $interval['start'] : '', false );
			$end   = self::sanitize_time( isset( $interval['end'] ) ? $interval['end'] : '', true );
			if ( '' === $start || '' === $end || self::time_to_minutes( $end ) <= self::time_to_minutes( $start ) ) {
				continue;
			}
			$intervals[] = array( 'start' => $start, 'end' => $end );
		}
		usort(
			$intervals,
			static function ( $left, $right ) {
				return strcmp( $left['start'], $right['start'] );
			}
		);
		return $intervals;
	}

	/** @return string */
	private static function sanitize_time( $value, $allow_24 ) {
		$value = trim( (string) $value );
		if ( $allow_24 && '24:00' === $value ) {
			return $value;
		}
		if ( ! preg_match( '/^(?:[01]\d|2[0-3]):[0-5]\d$/', $value ) ) {
			return '';
		}
		return $value;
	}

	/** @return int */
	private static function time_to_minutes( $time ) {
		if ( '24:00' === $time ) {
			return 1440;
		}
		$parts = explode( ':', $time );
		return ( (int) $parts[0] * 60 ) + (int) $parts[1];
	}

	/** @return string */
	private static function sanitize_date( $value ) {
		$value = trim( (string) $value );
		if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
			return '';
		}
		$date = DateTimeImmutable::createFromFormat( '!Y-m-d', $value, new DateTimeZone( 'UTC' ) );
		return $date && $date->format( 'Y-m-d' ) === $value ? $value : '';
	}

	/** @return string */
	private static function clean_id( $value ) {
		return substr( sanitize_key( (string) $value ), 0, 64 );
	}

	/** @return array */
	private static function sanitize_choice_list( $values, array $allowed ) {
		$values = is_array( $values ) ? $values : array( $values );
		$valid  = array();
		foreach ( $values as $value ) {
			$value = sanitize_key( (string) $value );
			if ( in_array( $value, $allowed, true ) ) {
				$valid[ $value ] = $value;
			}
		}
		return array_values( $valid );
	}

	/** @return string */
	private static function clean_session_id( $value ) {
		$value = preg_replace( '/[^A-Za-z0-9_.:\-]/', '', (string) $value );
		return substr( (string) $value, 0, 96 );
	}

	/** @return string */
	private static function clean_text( $value ) {
		return substr( sanitize_text_field( (string) $value ), 0, 191 );
	}

	/** @return array */
	private function error( $code, $message ) {
		return array(
			'error' => array(
				'code'    => (string) $code,
				'message' => (string) $message,
			),
		);
	}

	/** @return array */
	private function failure( $code, $message ) {
		return array(
			'success' => false,
			'error'   => array(
				'code'    => (string) $code,
				'message' => (string) $message,
			),
		);
	}
}
