<?php
/**
 * TutorHelper Availability exceptions.
 *
 * @package TutorHelper_Bookings
 */

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

/**
 * Handles date-specific Availability exceptions.
 *
 * Unavailable exceptions remove generated occurrences.
 * Override exceptions apply date-specific Availability values.
 */
final class TutorHelper_Availability_Exceptions {

	/**
	 * Unavailable exception type.
	 *
	 * @var string
	 */
	const TYPE_UNAVAILABLE = 'unavailable';

	/**
	 * Override exception type.
	 *
	 * Reserved for a later milestone.
	 *
	 * @var string
	 */
	const TYPE_OVERRIDE = 'override';

	/**
	 * Return the Availability exceptions table name.
	 *
	 * @return string
	 */
	public static function get_table_name() {
		return TutorHelper_Bookings_Database::get_table_name(
			'availability_exceptions'
		);
	}

	/**
	 * Retrieve one exception.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $exception_date  Date in Y-m-d format.
	 * @param string $exception_type  Exception type.
	 *
	 * @return object|null
	 */
	public static function get_exception(
		$availability_id,
		$exception_date,
		$exception_type = self::TYPE_UNAVAILABLE
	) {
		global $wpdb;

		$availability_id = absint( $availability_id );
		$exception_date  = self::sanitize_date( $exception_date );
		$exception_type  = self::sanitize_exception_type(
			$exception_type
		);

		if (
			! $availability_id
			|| ! $exception_date
			|| ! $exception_type
		) {
			return null;
		}

		$table_name = self::get_table_name();

		if ( empty( $table_name ) ) {
			return null;
		}

		return $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Availability exceptions affect live slot generation and must reflect current exception and booking state.
			$wpdb->prepare(
				'SELECT *
				FROM %i
				WHERE availability_id = %d
				AND exception_date = %s
				AND exception_type = %s',
				$table_name,
				$availability_id,
				$exception_date,
				$exception_type
			)
		);
	}


	/**
	 * Retrieve the override for one Availability occurrence.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $exception_date  Occurrence date.
	 *
	 * @return object|null
	 */
	public static function get_override(
		$availability_id,
		$exception_date
	) {
		return self::get_exception(
			$availability_id,
			$exception_date,
			self::TYPE_OVERRIDE
		);
	}



	/**
	 * Retrieve exceptions for multiple Availability rules and a date range.
	 *
	 * This method performs one query for the complete visible calendar range.
	 *
	 * @param array  $availability_ids Availability IDs.
	 * @param string $range_start      Start date in Y-m-d format.
	 * @param string $range_end        End date in Y-m-d format.
	 * @param string $exception_type   Optional exception type.
	 *
	 * @return array|WP_Error
	 */
	public static function get_for_availability_range(
		$availability_ids,
		$range_start,
		$range_end,
		$exception_type = ''
	) {
		global $wpdb;

		$availability_ids = self::sanitize_id_list(
			$availability_ids
		);

		$range_start = self::sanitize_date( $range_start );
		$range_end   = self::sanitize_date( $range_end );

		if ( empty( $availability_ids ) ) {
			return array();
		}

		if (
			! $range_start
			|| ! $range_end
			|| $range_end < $range_start
		) {
			return new WP_Error(
				'tutorhelper_exception_invalid_range',
				__(
					'The Availability exception date range is invalid.',
					'tutorhelper-bookings'
				)
			);
		}

		$placeholders = implode(
			',',
			array_fill(
				0,
				count( $availability_ids ),
				'%d'
			)
		);

		$parameters = $availability_ids;

		$table_name = self::get_table_name();

		if ( empty( $table_name ) ) {
			return array();
		}

		if ( ! empty( $exception_type ) ) {
			$exception_type = self::sanitize_exception_type(
				$exception_type
			);

			if ( ! $exception_type ) {
				return new WP_Error(
					'tutorhelper_exception_invalid_type',
					__(
						'The Availability exception type is invalid.',
						'tutorhelper-bookings'
					)
				);
			}

			$parameters = array_merge(
				array( $table_name ),
				$availability_ids,
				array(
					$range_start,
					$range_end,
					$exception_type,
				)
			);

			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- IN() placeholders are generated solely from %d tokens; all IDs and values are passed to $wpdb->prepare().
			$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Availability exceptions affect live slot generation and must reflect current exception and booking state.
				$wpdb->prepare(
					"SELECT *
					FROM %i
					WHERE availability_id IN ({$placeholders})
					AND exception_date >= %s
					AND exception_date <= %s
					AND exception_type = %s
					ORDER BY exception_date ASC, availability_id ASC",
					...$parameters
				)
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		} else {
			$parameters = array_merge(
				array( $table_name ),
				$availability_ids,
				array(
					$range_start,
					$range_end,
				)
			);

			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- IN() placeholders are generated solely from %d tokens; all IDs and values are passed to $wpdb->prepare().
			$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Availability exceptions affect live slot generation and must reflect current exception and booking state.
				$wpdb->prepare(
					"SELECT *
					FROM %i
					WHERE availability_id IN ({$placeholders})
					AND exception_date >= %s
					AND exception_date <= %s
					ORDER BY exception_date ASC, availability_id ASC",
					...$parameters
				)
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		}

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

	/**
	 * Return unavailable exceptions indexed by Availability ID and date.
	 *
	 * Example:
	 *
	 * $index[12]['2026-08-03'] = true;
	 *
	 * @param array  $availability_ids Availability IDs.
	 * @param string $range_start      Start date.
	 * @param string $range_end        End date.
	 *
	 * @return array|WP_Error
	 */
	public static function get_unavailable_index(
		$availability_ids,
		$range_start,
		$range_end
	) {
		$rows = self::get_for_availability_range(
			$availability_ids,
			$range_start,
			$range_end,
			self::TYPE_UNAVAILABLE
		);

		if ( is_wp_error( $rows ) ) {
			return $rows;
		}

		$index = array();

		foreach ( $rows as $row ) {
			$availability_id = absint(
				$row->availability_id
			);

			$exception_date = (string) $row->exception_date;

			if ( ! isset( $index[ $availability_id ] ) ) {
				$index[ $availability_id ] = array();
			}

			$index[ $availability_id ][ $exception_date ] = true;
		}

		return $index;
	}


	/**
	 * Return override exceptions indexed by Availability ID and date.
	 *
	 * Example:
	 *
	 * $index[12]['2026-08-03'] = $override_row;
	 *
	 * @param array  $availability_ids Availability IDs.
	 * @param string $range_start      Start date in Y-m-d format.
	 * @param string $range_end        End date in Y-m-d format.
	 *
	 * @return array|WP_Error
	 */
	public static function get_override_index(
		$availability_ids,
		$range_start,
		$range_end
	) {
		$rows = self::get_for_availability_range(
			$availability_ids,
			$range_start,
			$range_end,
			self::TYPE_OVERRIDE
		);

		if ( is_wp_error( $rows ) ) {
			return $rows;
		}

		$index = array();

		foreach ( $rows as $row ) {
			$availability_id = absint(
				$row->availability_id
			);

			$exception_date = (string) $row->exception_date;

			if ( ! isset( $index[ $availability_id ] ) ) {
				$index[ $availability_id ] = array();
			}

			$index[ $availability_id ][ $exception_date ] = $row;
		}

		return $index;
	}



	/**
	 * Check whether a date has an unavailable exception.
	 *
	 * @param int    $availability_id Availability ID.
	 * @param string $exception_date  Date in Y-m-d format.
	 *
	 * @return bool
	 */
	public static function is_unavailable(
		$availability_id,
		$exception_date
	) {
		return null !== self::get_exception(
			$availability_id,
			$exception_date,
			self::TYPE_UNAVAILABLE
		);
	}

	/**
	 * Create or replace an unavailable exception.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $exception_date  Date being removed.
	 * @param string $notes           Optional non-personal technical note.
	 *
	 * @return true|WP_Error
	 */
	public static function create_unavailable(
		$availability_id,
		$exception_date,
		$notes = ''
	) {
		global $wpdb;

		$availability_id = absint( $availability_id );
		$exception_date  = self::sanitize_date( $exception_date );

		$rule = TutorHelper_Booking_Availability::get_by_id(
			$availability_id
		);

		if ( ! $rule ) {
			return new WP_Error(
				'tutorhelper_exception_availability_not_found',
				__(
					'The Availability rule could not be found.',
					'tutorhelper-bookings'
				)
			);
		}

		if ( ! $exception_date ) {
			return new WP_Error(
				'tutorhelper_exception_invalid_date',
				__(
					'The Availability exception date is invalid.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			! self::date_belongs_to_rule(
				$rule,
				$exception_date
			)
		) {
			return new WP_Error(
				'tutorhelper_exception_date_not_in_rule',
				__(
					'The selected date is not generated by this Availability rule.',
					'tutorhelper-bookings'
				)
			);
		}

		/*
		 * An occurrence cannot be both overridden and unavailable.
		 *
		 * Override support will be added later, but clearing a possible
		 * override now prevents contradictory records.
		 */
		self::delete_exception(
			$availability_id,
			$exception_date,
			self::TYPE_OVERRIDE
		);

		$now = current_time( 'mysql', true );

		$result = $wpdb->replace( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Availability exceptions are stored in a custom table; this mutation must be visible immediately to slot generation.
			self::get_table_name(),
			array(
				'availability_id' => $availability_id,
				'exception_date'  => $exception_date,
				'exception_type'  => self::TYPE_UNAVAILABLE,
				'notes'           => sanitize_textarea_field(
					wp_unslash( $notes )
				),
				'created_at'      => $now,
				'updated_at'      => $now,
			),
			array(
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
			)
		);

		if ( false === $result ) {
			TutorHelper_Logger::error(
				'Availability exception creation failed.',
				array(
					'availability_id' => $availability_id,
					'exception_date'  => $exception_date,
					'exception_type'  => self::TYPE_UNAVAILABLE,
					'database_error'  => $wpdb->last_error,
				)
			);

			return new WP_Error(
				'tutorhelper_exception_create_failed',
				__(
					'The Availability date could not be removed.',
					'tutorhelper-bookings'
				)
			);
		}

		return true;
	}

	/**
	 * Remove one occurrence unless an existing booking overlaps it.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $occurrence_date Date being removed.
	 *
	 * @return array|WP_Error
	 */
	public static function remove_occurrence(
		$availability_id,
		$occurrence_date
	) {
		$availability_id = absint( $availability_id );
		$occurrence_date = self::sanitize_date(
			$occurrence_date
		);

		$rule = TutorHelper_Booking_Availability::get_by_id(
			$availability_id
		);

		if ( ! $rule ) {
			return new WP_Error(
				'tutorhelper_exception_availability_not_found',
				__(
					'The Availability rule could not be found.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			! $occurrence_date
			|| ! self::date_belongs_to_rule(
				$rule,
				$occurrence_date
			)
		) {
			return new WP_Error(
				'tutorhelper_exception_date_not_in_rule',
				__(
					'The selected date is not generated by this Availability rule.',
					'tutorhelper-bookings'
				)
			);
		}

		$booked_dates = self::get_booked_occurrence_dates(
			$rule,
			array( $occurrence_date )
		);

		if ( in_array( $occurrence_date, $booked_dates, true ) ) {
			return array(
				'removed_dates'          => array(),
				'already_removed_dates'  => array(),
				'skipped_booked_dates'   => array(
					$occurrence_date,
				),
				'ignored_dates'          => array(),
			);
		}

		$already_removed = self::is_unavailable(
			$availability_id,
			$occurrence_date
		);

		$result = self::create_unavailable(
			$availability_id,
			$occurrence_date,
			'Removed from the Booking Hub.'
		);

		if ( is_wp_error( $result ) ) {
			return $result;
		}

		return array(
			'removed_dates' => $already_removed
				? array()
				: array( $occurrence_date ),

			'already_removed_dates' => $already_removed
				? array( $occurrence_date )
				: array(),

			'skipped_booked_dates' => array(),
			'ignored_dates'        => array(),
		);
	}

	/**
	 * Remove generated occurrences within a selected date range.
	 *
	 * Dates which contain overlapping bookings are left unchanged.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $range_start     Selected start date.
	 * @param string $range_end       Selected end date.
	 *
	 * @return array|WP_Error
	 */
	public static function remove_occurrences_in_range(
		$availability_id,
		$range_start,
		$range_end
	) {
		$availability_id = absint( $availability_id );
		$range_start     = self::sanitize_date( $range_start );
		$range_end       = self::sanitize_date( $range_end );

		$rule = TutorHelper_Booking_Availability::get_by_id(
			$availability_id
		);

		if ( ! $rule ) {
			return new WP_Error(
				'tutorhelper_exception_availability_not_found',
				__(
					'The Availability rule could not be found.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			! $range_start
			|| ! $range_end
			|| $range_end < $range_start
		) {
			return new WP_Error(
				'tutorhelper_exception_invalid_range',
				__(
					'The selected removal date range is invalid.',
					'tutorhelper-bookings'
				)
			);
		}

		$occurrence_dates = self::get_rule_occurrence_dates(
			$rule,
			$range_start,
			$range_end
		);

		$result = array(
			'removed_dates'         => array(),
			'already_removed_dates' => array(),
			'skipped_booked_dates'  => array(),
			'ignored_dates'         => array(),
		);

		if ( empty( $occurrence_dates ) ) {
			return $result;
		}

		$booked_dates = self::get_booked_occurrence_dates(
			$rule,
			$occurrence_dates
		);

		foreach ( $occurrence_dates as $occurrence_date ) {
			if (
				in_array(
					$occurrence_date,
					$booked_dates,
					true
				)
			) {
				$result['skipped_booked_dates'][] = $occurrence_date;
				continue;
			}

			if (
				self::is_unavailable(
					$availability_id,
					$occurrence_date
				)
			) {
				$result['already_removed_dates'][] = $occurrence_date;
				continue;
			}

			$created = self::create_unavailable(
				$availability_id,
				$occurrence_date,
				'Removed as part of a Booking Hub date range.'
			);

			if ( is_wp_error( $created ) ) {
				TutorHelper_Logger::warning(
					'One Availability range exception could not be created.',
					array(
						'availability_id' => $availability_id,
						'exception_date'  => $occurrence_date,
						'error_code'      => $created->get_error_code(),
					)
				);

				$result['ignored_dates'][] = $occurrence_date;
				continue;
			}

			$result['removed_dates'][] = $occurrence_date;
		}

		return $result;
	}

	/**
	 * Restore a previously removed individual occurrence.
	 *
	 * @param int    $availability_id Availability ID.
	 * @param string $exception_date  Date to restore.
	 *
	 * @return bool
	 */
	public static function restore_occurrence(
		$availability_id,
		$exception_date
	) {
		return self::delete_exception(
			$availability_id,
			$exception_date,
			self::TYPE_UNAVAILABLE
		);
	}

	/**
	 * Delete one exception.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $exception_date  Exception date.
	 * @param string $exception_type  Exception type.
	 *
	 * @return bool
	 */
	public static function delete_exception(
		$availability_id,
		$exception_date,
		$exception_type
	) {
		global $wpdb;

		$availability_id = absint( $availability_id );
		$exception_date  = self::sanitize_date( $exception_date );
		$exception_type  = self::sanitize_exception_type(
			$exception_type
		);

		if (
			! $availability_id
			|| ! $exception_date
			|| ! $exception_type
		) {
			return false;
		}

		$result = $wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Availability exceptions are stored in a custom table; this mutation must be visible immediately to slot generation.
			self::get_table_name(),
			array(
				'availability_id' => $availability_id,
				'exception_date'  => $exception_date,
				'exception_type'  => $exception_type,
			),
			array(
				'%d',
				'%s',
				'%s',
			)
		);

		return false !== $result;
	}


	/**
	 * Create a date-specific Availability override.
	 *
	 * Only values which differ from the parent rule are stored. If no submitted
	 * values differ, any existing override for the date is removed.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $exception_date  Occurrence date in Y-m-d format.
	 * @param array  $data            Submitted Availability values.
	 *
	 * @return true|WP_Error
	 */
	public static function create_override(
		$availability_id,
		$exception_date,
		$data
	) {
		global $wpdb;

		$availability_id = absint( $availability_id );
		$exception_date  = self::sanitize_date( $exception_date );

		$rule = TutorHelper_Booking_Availability::get_by_id(
			$availability_id
		);

		if ( ! $rule ) {
			return new WP_Error(
				'tutorhelper_exception_availability_not_found',
				__(
					'The Availability rule could not be found.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			! $exception_date
			|| ! self::date_belongs_to_rule(
				$rule,
				$exception_date
			)
		) {
			return new WP_Error(
				'tutorhelper_exception_date_not_in_rule',
				__(
					'The selected date is not generated by this Availability rule.',
					'tutorhelper-bookings'
				)
			);
		}

		/*
		* Use the main Availability sanitiser so base rules and overrides follow
		* the same rules for times, locations and Booking Type IDs.
		*/
		$clean = TutorHelper_Booking_Availability::sanitize_data(
			array_merge(
				get_object_vars( $rule ),
				is_array( $data ) ? $data : array()
			)
		);

		$validation = TutorHelper_Booking_Availability::validate_data(
			$clean
		);

		if ( is_wp_error( $validation ) ) {
			return $validation;
		}

		$override_title = (string) $clean['title']
			!== (string) $rule->title
				? $clean['title']
				: null;

		$override_start_time =
			substr( (string) $clean['start_time'], 0, 8 )
			!== substr( (string) $rule->start_time, 0, 8 )
				? $clean['start_time']
				: null;

		$override_end_time =
			substr( (string) $clean['end_time'], 0, 8 )
			!== substr( (string) $rule->end_time, 0, 8 )
				? $clean['end_time']
				: null;

		$override_timezone = (string) $clean['timezone']
			!== (string) $rule->timezone
				? $clean['timezone']
				: null;

		$override_location_type =
			(string) $clean['location_type']
			!== (string) $rule->location_type
				? $clean['location_type']
				: null;

		$override_location_types =
			self::normalise_csv( $clean['location_types'] )
			!== self::normalise_csv( $rule->location_types )
				? $clean['location_types']
				: null;

		$override_location_details =
			(string) $clean['location_details']
			!== (string) $rule->location_details
				? $clean['location_details']
				: null;

		$override_booking_type_ids = null;

		$clean_booking_type_ids =
			self::normalise_csv(
				$clean['allowed_booking_type_ids']
			);

		$rule_booking_type_ids =
			self::normalise_csv(
				$rule->allowed_booking_type_ids
			);

		if (
			$clean_booking_type_ids
			!== $rule_booking_type_ids
		) {
			/*
			* NULL means "inherit the parent rule" for an
			* Availability override.
			*
			* An empty string therefore represents an explicit
			* date-specific choice to allow all Booking Types.
			*/
			$override_booking_type_ids =
				empty( $clean_booking_type_ids )
					? ''
					: $clean_booking_type_ids;
		}

		/*
		* If every submitted value matches the parent rule, remove any stale
		* override so the occurrence inherits the parent again.
		*/
		if (
			null === $override_title
			&& null === $override_start_time
			&& null === $override_end_time
			&& null === $override_timezone
			&& null === $override_location_type
			&& null === $override_location_types
			&& null === $override_location_details
			&& null === $override_booking_type_ids
		) {
			self::delete_exception(
				$availability_id,
				$exception_date,
				self::TYPE_OVERRIDE
			);

			return true;
		}

		/*
		* An occurrence cannot be unavailable and overridden simultaneously.
		* Editing a removed date restores it as an overridden occurrence.
		*/
		self::delete_exception(
			$availability_id,
			$exception_date,
			self::TYPE_UNAVAILABLE
		);

		$now = current_time( 'mysql', true );

		$result = $wpdb->replace( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Availability exceptions are stored in a custom table; this mutation must be visible immediately to slot generation.
			self::get_table_name(),
			array(
				'availability_id' => $availability_id,
				'exception_date'  => $exception_date,
				'exception_type'  => self::TYPE_OVERRIDE,

				'override_title' => $override_title,

				'override_start_time' => $override_start_time,
				'override_end_time'   => $override_end_time,
				'override_timezone'   => $override_timezone,

				'override_location_type' => $override_location_type,

				'override_location_types' =>
					$override_location_types,

				'override_allowed_booking_type_ids' =>
					$override_booking_type_ids,

				'override_location_details' =>
					$override_location_details,

				'notes' => 'Edited from the Booking Hub.',

				'created_at' => $now,
				'updated_at' => $now,
			),
			array(
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
			)
		);

		if ( false === $result ) {
			TutorHelper_Logger::error(
				'Availability override creation failed.',
				array(
					'availability_id' => $availability_id,
					'exception_date'  => $exception_date,
					'database_error'  => $wpdb->last_error,
				)
			);

			return new WP_Error(
				'tutorhelper_exception_override_failed',
				__(
					'The Availability changes could not be saved.',
					'tutorhelper-bookings'
				)
			);
		}

		return true;
	}



	/**
	 * Create Availability overrides for matching occurrences in a date range.
	 *
	 * Dates containing an overlapping booking are left unchanged.
	 *
	 * @param int    $availability_id Availability rule ID.
	 * @param string $range_start     Range start in Y-m-d format.
	 * @param string $range_end       Range end in Y-m-d format.
	 * @param array  $data            Updated Availability values.
	 *
	 * @return array|WP_Error
	 */
	public static function create_overrides_in_range(
		$availability_id,
		$range_start,
		$range_end,
		$data
	) {
		$availability_id = absint( $availability_id );
		$range_start     = self::sanitize_date( $range_start );
		$range_end       = self::sanitize_date( $range_end );

		$rule = TutorHelper_Booking_Availability::get_by_id(
			$availability_id
		);

		if ( ! $rule ) {
			return new WP_Error(
				'tutorhelper_exception_availability_not_found',
				__(
					'The Availability rule could not be found.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			! $range_start
			|| ! $range_end
			|| $range_end < $range_start
		) {
			return new WP_Error(
				'tutorhelper_exception_invalid_range',
				__(
					'The selected edit date range is invalid.',
					'tutorhelper-bookings'
				)
			);
		}

		$occurrence_dates = self::get_rule_occurrence_dates(
			$rule,
			$range_start,
			$range_end
		);

		$result = array(
			'updated_dates'        => array(),
			'skipped_booked_dates' => array(),
			'ignored_dates'        => array(),
		);

		if ( empty( $occurrence_dates ) ) {
			return $result;
		}

		$booked_dates = self::get_booked_occurrence_dates(
			$rule,
			$occurrence_dates
		);

		foreach ( $occurrence_dates as $occurrence_date ) {
			if (
				in_array(
					$occurrence_date,
					$booked_dates,
					true
				)
			) {
				$result['skipped_booked_dates'][] =
					$occurrence_date;

				continue;
			}

			$updated = self::create_override(
				$availability_id,
				$occurrence_date,
				$data
			);

			if ( is_wp_error( $updated ) ) {
				TutorHelper_Logger::warning(
					'One Availability range override could not be saved.',
					array(
						'availability_id' => $availability_id,
						'exception_date'  => $occurrence_date,
						'error_code'      => $updated->get_error_code(),
					)
				);

				$result['ignored_dates'][] = $occurrence_date;
				continue;
			}

			$result['updated_dates'][] = $occurrence_date;
		}

		return $result;
	}





	/**
	 * Normalise comma-separated values for comparison.
	 *
	 * @param string|null $value CSV value.
	 *
	 * @return string
	 */
	private static function normalise_csv( $value ) {
		$parts = array_filter(
			array_map(
				'trim',
				explode( ',', (string) $value )
			)
		);

		sort( $parts );

		return implode( ',', array_unique( $parts ) );
	}


	/**
	 * Return the generated dates for one rule inside a requested range.
	 *
	 * This does not apply existing exceptions because already-removed dates
	 * still need to be recognised as part of the recurring rule.
	 *
	 * @param object $rule        Availability database row.
	 * @param string $range_start Range start.
	 * @param string $range_end   Range end.
	 *
	 * @return array
	 */
	private static function get_rule_occurrence_dates(
		$rule,
		$range_start,
		$range_end
	) {
		$timezone = wp_timezone();

		$requested_start = DateTimeImmutable::createFromFormat(
			'!Y-m-d',
			$range_start,
			$timezone
		);

		$requested_end = DateTimeImmutable::createFromFormat(
			'!Y-m-d',
			$range_end,
			$timezone
		);

		$rule_start = DateTimeImmutable::createFromFormat(
			'!Y-m-d',
			$rule->start_date,
			$timezone
		);

		$effective_start = $requested_start > $rule_start
			? $requested_start
			: $rule_start;

		$effective_end = $requested_end;

		if ( ! empty( $rule->end_date ) ) {
			$rule_end = DateTimeImmutable::createFromFormat(
				'!Y-m-d',
				$rule->end_date,
				$timezone
			);

			if ( $rule_end < $effective_end ) {
				$effective_end = $rule_end;
			}
		}

		if ( $effective_end < $effective_start ) {
			return array();
		}

		$weekdays = self::split_csv( $rule->weekdays );
		$dates    = array();
		$current  = $effective_start;

		while ( $current <= $effective_end ) {
			$weekday = strtolower( $current->format( 'D' ) );

			if ( in_array( $weekday, $weekdays, true ) ) {
				$dates[] = $current->format( 'Y-m-d' );
			}

			$current = $current->modify( '+1 day' );
		}

		return $dates;
	}

	/**
	 * Check whether a date belongs to a recurring Availability rule.
	 *
	 * @param object $rule Availability database row.
	 * @param string $date Date in Y-m-d format.
	 *
	 * @return bool
	 */
	private static function date_belongs_to_rule( $rule, $date ) {
		$dates = self::get_rule_occurrence_dates(
			$rule,
			$date,
			$date
		);

		return in_array( $date, $dates, true );
	}

	/**
	 * Return occurrence dates containing overlapping bookings.
	 *
	 * One bookings query is made for the complete requested date set.
	 *
	 * @param object $rule             Availability database row.
	 * @param array  $occurrence_dates Generated occurrence dates.
	 *
	 * @return array
	 */
	private static function get_booked_occurrence_dates(
		$rule,
		$occurrence_dates
	) {
		global $wpdb;

		$occurrence_dates = array_values(
			array_unique(
				array_filter(
					array_map(
						array( __CLASS__, 'sanitize_date' ),
						(array) $occurrence_dates
					)
				)
			)
		);

		if ( empty( $occurrence_dates ) ) {
			return array();
		}

		$first_date = reset( $occurrence_dates );
		$last_date  = end( $occurrence_dates );

		$blocking_statuses = apply_filters(
			'tutorhelper_availability_blocking_booking_statuses',
			array(
				'confirmed',
				'pending',
				'manual_pending',
			)
		);

		$blocking_statuses = array_values(
			array_unique(
				array_filter(
					array_map(
						'sanitize_key',
						(array) $blocking_statuses
					)
				)
			)
		);

		if ( empty( $blocking_statuses ) ) {
			return array();
		}

		$status_placeholders = implode(
			',',
			array_fill(
				0,
				count( $blocking_statuses ),
				'%s'
			)
		);

		$parameters = array(
			absint( $rule->tutor_user_id ),
			$first_date . ' 00:00:00',
			$last_date . ' 23:59:59',
		);

		$parameters = array_merge(
			$parameters,
			$blocking_statuses
		);

		$bookings_table = TutorHelper_Bookings_Database::get_table_name(
			'bookings'
		);

		$parameters = array_merge(
			array(
				$bookings_table,
				absint( $rule->tutor_user_id ),
				$last_date . ' 23:59:59',
				$first_date . ' 00:00:00',
			),
			$blocking_statuses
		);

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- IN() placeholders are generated solely from sanitized %s status tokens; all values are passed to $wpdb->prepare().
		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Availability exceptions affect live slot generation and must reflect current exception and booking state.
			$wpdb->prepare(
				"SELECT booking_start, booking_end, status
				FROM %i
				WHERE tutor_user_id = %d
				AND booking_start <= %s
				AND booking_end >= %s
				AND status IN ({$status_placeholders})",
				...$parameters
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber

		if ( ! is_array( $rows ) || empty( $rows ) ) {
			return array();
		}

		$timezone    = wp_timezone();
		$booked_dates = array();

		foreach ( $occurrence_dates as $occurrence_date ) {
			$occurrence_start = new DateTimeImmutable(
				$occurrence_date . ' ' . $rule->start_time,
				$timezone
			);

			$occurrence_end = new DateTimeImmutable(
				$occurrence_date . ' ' . $rule->end_time,
				$timezone
			);

			foreach ( $rows as $row ) {
				$booking_start = new DateTimeImmutable(
					$row->booking_start,
					$timezone
				);

				$booking_end = new DateTimeImmutable(
					$row->booking_end,
					$timezone
				);

				/*
				 * Intervals overlap when each starts before the other ends.
				 */
				if (
					$booking_start < $occurrence_end
					&& $booking_end > $occurrence_start
				) {
					$booked_dates[] = $occurrence_date;
					break;
				}
			}
		}

		/**
		 * Filters dates considered protected by existing bookings.
		 *
		 * Add-ons may add externally managed bookings or calendar events.
		 *
		 * @param array  $booked_dates     Protected dates.
		 * @param object $rule             Availability rule.
		 * @param array  $occurrence_dates Candidate occurrence dates.
		 */
		$booked_dates = apply_filters(
			'tutorhelper_availability_booked_occurrence_dates',
			array_values( array_unique( $booked_dates ) ),
			$rule,
			$occurrence_dates
		);

		return array_values(
			array_unique(
				array_filter(
					(array) $booked_dates
				)
			)
		);
	}

	/**
	 * Sanitise an exception type.
	 *
	 * @param string $exception_type Submitted type.
	 *
	 * @return string
	 */
	private static function sanitize_exception_type(
		$exception_type
	) {
		$exception_type = sanitize_key(
			wp_unslash( $exception_type )
		);

		$allowed_types = array(
			self::TYPE_UNAVAILABLE,
			self::TYPE_OVERRIDE,
		);

		return in_array(
			$exception_type,
			$allowed_types,
			true
		)
			? $exception_type
			: '';
	}

	/**
	 * Sanitise a Y-m-d date.
	 *
	 * @param string $date Submitted date.
	 *
	 * @return string
	 */
	private static function sanitize_date( $date ) {
		$date = sanitize_text_field(
			wp_unslash( $date )
		);

		$parsed = DateTimeImmutable::createFromFormat(
			'!Y-m-d',
			$date,
			wp_timezone()
		);

		if (
			false === $parsed
			|| $parsed->format( 'Y-m-d' ) !== $date
		) {
			return '';
		}

		return $date;
	}

	/**
	 * Sanitise a list of positive IDs.
	 *
	 * @param array $values Submitted IDs.
	 *
	 * @return array
	 */
	private static function sanitize_id_list( $values ) {
		$ids = array();

		foreach ( (array) $values as $value ) {
			$value = filter_var(
				$value,
				FILTER_VALIDATE_INT
			);

			if ( false !== $value && 0 < $value ) {
				$ids[] = $value;
			}
		}

		return array_values(
			array_unique( $ids )
		);
	}

	/**
	 * Split a comma-separated database value.
	 *
	 * @param string|null $value Stored value.
	 *
	 * @return array
	 */
	private static function split_csv( $value ) {
		if ( empty( $value ) ) {
			return array();
		}

		return array_values(
			array_filter(
				array_map(
					'trim',
					explode( ',', (string) $value )
				)
			)
		);
	}
}
