<?php
/**
 * TutorHelper Booking Availability data layer.
 *
 * @package TutorHelper_Bookings
 */

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

/**
 * Handles Availability storage and recurring occurrence generation.
 */
final class TutorHelper_Booking_Availability {

	/**
	 * Allowed weekday identifiers.
	 *
	 * @var array
	 */
	const ALLOWED_WEEKDAYS = array(
		'mon',
		'tue',
		'wed',
		'thu',
		'fri',
		'sat',
		'sun',
	);

	/**
	 * Allowed location identifiers.
	 *
	 * @var array
	 */
	const ALLOWED_LOCATIONS = array(
		'online',
		'phone',
		'in_person',
		'other',
	);

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

	/**
	 * Retrieve Availability rules.
	 *
	 * @param array $args Query arguments.
	 *
	 * @return array
	 */
	public static function get_all( $args = array() ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			array(
				'tutor_user_id' => 0,
				'active_only'   => false,
			)
		);

		$table_name = self::get_table_name();


		if (
			! empty( $args['tutor_user_id'] )
			&& ! empty( $args['active_only'] )
		) {
			$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- TutorHelper uses a custom table and this query must reflect current scheduling data; stale cached results could affect booking availability.
				$wpdb->prepare(
					'SELECT *
					FROM %i
					WHERE tutor_user_id = %d
					AND is_active = %d
					ORDER BY start_date ASC, start_time ASC, title ASC',
					$table_name,
					absint( $args['tutor_user_id'] ),
					1
				)
			);
		} elseif ( ! empty( $args['tutor_user_id'] ) ) {
			$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- TutorHelper uses a custom table and this query must reflect current scheduling data; stale cached results could affect booking availability.
				$wpdb->prepare(
					'SELECT *
					FROM %i
					WHERE tutor_user_id = %d
					ORDER BY start_date ASC, start_time ASC, title ASC',
					$table_name,
					absint( $args['tutor_user_id'] )
				)
			);
		} elseif ( ! empty( $args['active_only'] ) ) {
			$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- TutorHelper uses a custom table and this query must reflect current scheduling data; stale cached results could affect booking availability.
				$wpdb->prepare(
					'SELECT *
					FROM %i
					WHERE is_active = %d
					ORDER BY start_date ASC, start_time ASC, title ASC',
					$table_name,
					1
				)
			);
		} else {
			$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- TutorHelper uses a custom table and this query must reflect current scheduling data; stale cached results could affect booking availability.
				$wpdb->prepare(
					'SELECT *
					FROM %i
					ORDER BY start_date ASC, start_time ASC, title ASC',
					$table_name
				)
			);
		}

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

	/**
	 * Retrieve one Availability rule.
	 *
	 * @param int $availability_id Availability ID.
	 *
	 * @return object|null
	 */
	public static function get_by_id( $availability_id ) {
		global $wpdb;

		$availability_id = absint( $availability_id );

		if ( ! $availability_id ) {
			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 -- TutorHelper uses a custom table and this query must reflect current scheduling data; stale cached results could affect booking availability.
			$wpdb->prepare(
				'SELECT *
				FROM %i
				WHERE id = %d',
				$table_name,
				$availability_id
			)
		);
	}

	/**
	 * Create an Availability rule.
	 *
	 * @param array $data Availability data.
	 *
	 * @return int|WP_Error
	 */
	public static function create( $data ) {
		global $wpdb;

		$clean = self::sanitize_data( $data );
		$valid = self::validate_data( $clean );

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

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

		$clean['created_at'] = $now;
		$clean['updated_at'] = $now;

		$result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- TutorHelper stores this data in a custom table; caching does not apply to the database write.
			self::get_table_name(),
			$clean,
			self::get_formats()
		);

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

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

		return absint( $wpdb->insert_id );
	}

	/**
	 * Update an Availability rule.
	 *
	 * Partial updates preserve existing values.
	 *
	 * @param int   $availability_id Availability ID.
	 * @param array $data            Updated values.
	 *
	 * @return true|WP_Error
	 */
	public static function update( $availability_id, $data ) {
		global $wpdb;

		$availability_id = absint( $availability_id );
		$existing        = self::get_by_id( $availability_id );

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

		$merged = array_merge(
			get_object_vars( $existing ),
			is_array( $data ) ? $data : array()
		);

		$clean = self::sanitize_data( $merged );
		$valid = self::validate_data( $clean );

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

		$clean['updated_at'] = current_time( 'mysql', true );

		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- TutorHelper stores this data in a custom table; this is a write operation and must take effect immediately.
			self::get_table_name(),
			$clean,
			array(
				'id' => $availability_id,
			),
			self::get_update_formats(),
			array(
				'%d',
			)
		);

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

			return new WP_Error(
				'tutorhelper_availability_update_failed',
				__(
					'The Availability rule could not be updated.',
					'tutorhelper-bookings'
				)
			);
		}

		return true;
	}

	/**
	 * Archive an Availability rule.
	 *
	 * @param int $availability_id Availability ID.
	 *
	 * @return true|WP_Error
	 */
	public static function archive( $availability_id ) {
		return self::set_active_status(
			$availability_id,
			0
		);
	}

	/**
	 * Restore an Availability rule.
	 *
	 * @param int $availability_id Availability ID.
	 *
	 * @return true|WP_Error
	 */
	public static function restore( $availability_id ) {
		return self::set_active_status(
			$availability_id,
			1
		);
	}

	/**
	 * Retrieve active rules overlapping a date range.
	 *
	 * @param int    $tutor_user_id Tutor user ID.
	 * @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_for_date_range(
		$tutor_user_id,
		$range_start,
		$range_end
	) {
		global $wpdb;

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

		$table_name = self::get_table_name();

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

		$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- TutorHelper uses a custom table and this query must reflect current scheduling data; stale cached results could affect booking availability.
			$wpdb->prepare(
				'SELECT *
				FROM %i
				WHERE tutor_user_id = %d
				AND is_active = %d
				AND start_date <= %s
				AND (
					end_date IS NULL
					OR end_date = %s
					OR end_date >= %s
				)
				ORDER BY start_time ASC, title ASC',
				$table_name,
				absint( $tutor_user_id ),
				1,
				$range_end,
				'',
				$range_start
			)
		);

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

	/**
	 * Generate date-specific occurrences for a range.
	 *
	 * This deliberately returns occurrences rather than raw recurring rules.
	 * Future exception handling can modify these occurrences without changing
	 * the Booking Hub templates.
	 *
	 * @param int    $tutor_user_id Tutor user ID.
	 * @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_occurrences(
		$tutor_user_id,
		$range_start,
		$range_end
	) {
		$rules = self::get_for_date_range(
			$tutor_user_id,
			$range_start,
			$range_end
		);

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


        $availability_ids = array_map(
                'absint',
                wp_list_pluck(
                    $rules,
                    'id'
                )
            );

        $unavailable_index =
            TutorHelper_Availability_Exceptions::get_unavailable_index(
                $availability_ids,
                $range_start,
                $range_end
            );

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

		$override_index =
			TutorHelper_Availability_Exceptions::get_override_index(
				$availability_ids,
				$range_start,
				$range_end
			);

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


		$timezone = wp_timezone();

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

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

		$occurrences = array();

		while ( $current_date <= $final_date ) {
			$date_string = $current_date->format( 'Y-m-d' );
			$weekday     = strtolower(
				$current_date->format( 'D' )
			);

			foreach ( $rules as $rule ) {
				$weekdays = self::split_csv(
					$rule->weekdays
				);

				if ( ! in_array( $weekday, $weekdays, true ) ) {
					continue;
				}

				if ( $date_string < $rule->start_date ) {
					continue;
				}

				if (
					! empty( $rule->end_date )
					&& $date_string > $rule->end_date
				) {
					continue;
				}

                $availability_id = absint( $rule->id );

                if (
                    isset(
                        $unavailable_index[ $availability_id ][ $date_string ]
                    )
                ) {
                    continue;
                }

				$override = isset(
					$override_index[ $availability_id ][ $date_string ]
				)
					? $override_index[ $availability_id ][ $date_string ]
					: null;


				$occurrences[] = array(
					'availability_id' => $availability_id,

					'tutor_user_id' => absint(
						$rule->tutor_user_id
					),

					'date' => $date_string,

					'title' => $override
						&& null !== $override->override_title
							? $override->override_title
							: $rule->title,

					'start_time' => $override
						&& null !== $override->override_start_time
							? $override->override_start_time
							: $rule->start_time,

					'end_time' => $override
						&& null !== $override->override_end_time
							? $override->override_end_time
							: $rule->end_time,

					'timezone' => $override
						&& null !== $override->override_timezone
							? $override->override_timezone
							: $rule->timezone,

					'location_type' => $override
						&& null !== $override->override_location_type
							? $override->override_location_type
							: $rule->location_type,

					'location_types' => self::split_csv(
						$override
						&& null !== $override->override_location_types
							? $override->override_location_types
							: $rule->location_types
					),

					'location_details' => $override
						&& null !== $override->override_location_details
							? $override->override_location_details
							: $rule->location_details,

					'allowed_booking_type_ids' => array_map(
						'absint',
						self::split_csv(
							$override
							&& null !==
								$override->override_allowed_booking_type_ids
								? $override->override_allowed_booking_type_ids
								: $rule->allowed_booking_type_ids
						)
					),

					'is_override' => null !== $override,
				);

			}

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

		usort(
			$occurrences,
			function ( $first, $second ) {
				$first_key = $first['date']
					. ' '
					. $first['start_time'];

				$second_key = $second['date']
					. ' '
					. $second['start_time'];

				return strcmp( $first_key, $second_key );
			}
		);

		return $occurrences;
	}

	/**
	 * Sanitise Availability data.
	 *
	 * @param array $data Raw Availability data.
	 *
	 * @return array
	 */
	public static function sanitize_data( $data ) {
		$data = is_array( $data ) ? $data : array();

		$weekdays = self::sanitize_list(
			isset( $data['weekdays'] )
				? $data['weekdays']
				: array(),
			self::ALLOWED_WEEKDAYS
		);

		$location_types = self::sanitize_list(
			isset( $data['location_types'] )
				? $data['location_types']
				: array(),
			self::ALLOWED_LOCATIONS
		);

		if (
			empty( $location_types )
			&& isset( $data['location_type'] )
		) {
			$primary_location = sanitize_key(
				wp_unslash( $data['location_type'] )
			);

			if (
				in_array(
					$primary_location,
					self::ALLOWED_LOCATIONS,
					true
				)
			) {
				$location_types[] = $primary_location;
			}
		}

		if ( empty( $location_types ) ) {
			$location_types = array( 'online' );
		}

		$primary_location = isset( $data['location_type'] )
			? sanitize_key( wp_unslash( $data['location_type'] ) )
			: $location_types[0];

		if (
			! in_array(
				$primary_location,
				$location_types,
				true
			)
		) {
			$primary_location = $location_types[0];
		}

		$booking_type_ids = self::sanitize_id_list(
			isset( $data['allowed_booking_type_ids'] )
				? $data['allowed_booking_type_ids']
				: array()
		);

		$title = isset( $data['title'] )
			? sanitize_text_field( wp_unslash( $data['title'] ) )
			: '';

		if ( empty( $title ) ) {
			$title = __(
				'Availability',
				'tutorhelper-bookings'
			);
		}

		return array(
			'tutor_user_id' => isset( $data['tutor_user_id'] )
				? absint( $data['tutor_user_id'] )
				: get_current_user_id(),

			'title' => $title,

			'start_date' => isset( $data['start_date'] )
				? sanitize_text_field(
					wp_unslash( $data['start_date'] )
				)
				: '',

			'end_date' => ! empty( $data['end_date'] )
				? sanitize_text_field(
					wp_unslash( $data['end_date'] )
				)
				: null,

			'weekdays' => implode( ',', $weekdays ),

			'start_time' => isset( $data['start_time'] )
				? self::normalise_time( $data['start_time'] )
				: '',

			'end_time' => isset( $data['end_time'] )
				? self::normalise_time( $data['end_time'] )
				: '',

			'timezone' => isset( $data['timezone'] )
				? sanitize_text_field(
					wp_unslash( $data['timezone'] )
				)
				: wp_timezone_string(),

			'location_type' => $primary_location,

			'location_types' => implode(
				',',
				$location_types
			),

			'allowed_booking_type_ids' => empty(
				$booking_type_ids
			)
				? null
				: implode( ',', $booking_type_ids ),

			'location_details' => isset(
				$data['location_details']
			)
				? sanitize_textarea_field(
					wp_unslash( $data['location_details'] )
				)
				: '',

			'is_active' => isset( $data['is_active'] )
				? min( 1, absint( $data['is_active'] ) )
				: 1,
		);
	}

	/**
	 * Validate sanitised Availability data.
	 *
	 * @param array $data Sanitised Availability data.
	 *
	 * @return true|WP_Error
	 */
	public static function validate_data( $data ) {
		if ( empty( $data['start_date'] ) ) {
			return new WP_Error(
				'tutorhelper_availability_missing_start_date',
				__(
					'Please choose a start date.',
					'tutorhelper-bookings'
				)
			);
		}

		if ( ! self::is_valid_date( $data['start_date'] ) ) {
			return new WP_Error(
				'tutorhelper_availability_invalid_start_date',
				__(
					'The start date is invalid.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			! empty( $data['end_date'] )
			&& ! self::is_valid_date( $data['end_date'] )
		) {
			return new WP_Error(
				'tutorhelper_availability_invalid_end_date',
				__(
					'The end date is invalid.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			! empty( $data['end_date'] )
			&& $data['end_date'] < $data['start_date']
		) {
			return new WP_Error(
				'tutorhelper_availability_end_before_start',
				__(
					'The end date cannot be earlier than the start date.',
					'tutorhelper-bookings'
				)
			);
		}

		if ( empty( $data['weekdays'] ) ) {
			return new WP_Error(
				'tutorhelper_availability_missing_weekdays',
				__(
					'Please choose at least one weekday.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			empty( $data['start_time'] )
			|| empty( $data['end_time'] )
		) {
			return new WP_Error(
				'tutorhelper_availability_missing_times',
				__(
					'Please enter both a start and end time.',
					'tutorhelper-bookings'
				)
			);
		}

		if ( $data['end_time'] <= $data['start_time'] ) {
			return new WP_Error(
				'tutorhelper_availability_invalid_time_range',
				__(
					'The end time must be later than the start time.',
					'tutorhelper-bookings'
				)
			);
		}

		if (
			empty( $data['timezone'] )
			|| ! in_array(
				$data['timezone'],
				timezone_identifiers_list(),
				true
			)
		) {
			return new WP_Error(
				'tutorhelper_availability_invalid_timezone',
				__(
					'The selected timezone is invalid.',
					'tutorhelper-bookings'
				)
			);
		}

		return true;
	}

	/**
	 * Update the active status.
	 *
	 * @param int $availability_id Availability ID.
	 * @param int $is_active       New active status.
	 *
	 * @return true|WP_Error
	 */
	private static function set_active_status(
		$availability_id,
		$is_active
	) {
		global $wpdb;

		$availability_id = absint( $availability_id );

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

		$result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- TutorHelper stores this data in a custom table; this is a write operation and must take effect immediately.
			self::get_table_name(),
			array(
				'is_active' => min( 1, absint( $is_active ) ),
				'updated_at' => current_time( 'mysql', true ),
			),
			array(
				'id' => $availability_id,
			),
			array(
				'%d',
				'%s',
			),
			array(
				'%d',
			)
		);

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

			return new WP_Error(
				'tutorhelper_availability_status_failed',
				__(
					'The Availability status could not be changed.',
					'tutorhelper-bookings'
				)
			);
		}

		return true;
	}

	/**
	 * Sanitise a list against allowed values.
	 *
	 * @param array|string $values  Submitted values.
	 * @param array        $allowed Allowed values.
	 *
	 * @return array
	 */
	private static function sanitize_list( $values, $allowed ) {
		if ( ! is_array( $values ) ) {
			$values = explode( ',', (string) $values );
		}

		$values = array_map(
			'sanitize_key',
			wp_unslash( $values )
		);

		return array_values(
			array_unique(
				array_intersect(
					$allowed,
					$values
				)
			)
		);
	}

	/**
	 * Sanitise a list of positive IDs.
	 *
	 * @param array|string $values Submitted values.
	 *
	 * @return array
	 */
	private static function sanitize_id_list( $values ) {
		if ( ! is_array( $values ) ) {
			$values = explode( ',', (string) $values );
		}

		$values = array_filter(
			array_map(
				'absint',
				wp_unslash( $values )
			)
		);

		return array_values(
			array_unique( $values )
		);
	}

	/**
	 * Split a comma-separated database value.
	 *
	 * @param string|null $value Stored CSV 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 )
				)
			)
		);
	}

	/**
	 * Normalise an HTML time input.
	 *
	 * @param string $time Submitted time.
	 *
	 * @return string
	 */
	private static function normalise_time( $time ) {
		$time = sanitize_text_field(
			wp_unslash( $time )
		);

		if ( preg_match( '/^\d{2}:\d{2}$/', $time ) ) {
			return $time . ':00';
		}

		if ( preg_match( '/^\d{2}:\d{2}:\d{2}$/', $time ) ) {
			return $time;
		}

		return '';
	}

	/**
	 * Validate a Y-m-d date.
	 *
	 * @param string $date Date value.
	 *
	 * @return bool
	 */
	private static function is_valid_date( $date ) {
		$parsed = DateTimeImmutable::createFromFormat(
			'!Y-m-d',
			(string) $date,
			wp_timezone()
		);

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

	/**
	 * Database insert formats.
	 *
	 * @return array
	 */
	private static function get_formats() {
		return array(
			'%d',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%d',
			'%s',
			'%s',
		);
	}

	/**
	 * Database update formats.
	 *
	 * @return array
	 */
	private static function get_update_formats() {
		return array(
			'%d',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%s',
			'%d',
			'%s',
		);
	}
}
