<?php
/**
 * Availability exceptions repository.
 *
 * @package Abientot
 */

namespace Abientot\Repositories;

use Abientot\Support\Db;

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

final class AvailabilityExceptionsRepository extends BaseRepository {
	protected string $table_suffix = 'availability_exceptions';

	/**
	 * Returns upcoming exceptions for a staff member.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function for_staff( int $staff_id, int $limit = 50 ): array {
		global $wpdb;

		if ( $staff_id <= 0 ) {
			return array();
		}

		$limit = max( 1, min( 100, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id = %d ORDER BY exception_date ASC, start_time ASC LIMIT %d",
				$staff_id,
				$limit
			),
			ARRAY_A
		);

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

	/**
	 * Returns exceptions for a staff member in a local date range.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function for_staff_between( int $staff_id, string $start_date, string $end_date ): array {
		global $wpdb;

		if ( $staff_id <= 0 || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $start_date ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $end_date ) ) {
			return array();
		}

		return Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id = %d AND exception_date >= %s AND exception_date <= %s ORDER BY exception_date ASC, exception_type ASC, start_time ASC",
					$staff_id,
					$start_date,
					$end_date
				),
				ARRAY_A
			),
			'availability_storage_unavailable'
		);
	}

	/**
	 * Returns full-day closed exceptions for a staff member in a local date range.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function closed_days_for_staff_between( int $staff_id, string $start_date, string $end_date, ?int $location_id = null ): array {
		global $wpdb;

		if ( $staff_id <= 0 || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $start_date ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $end_date ) ) {
			return array();
		}

		$location = null !== $location_id && $location_id > 0 ? $location_id : 0;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id = %d AND exception_date >= %s AND exception_date <= %s AND exception_type = %s AND start_time IS NULL AND end_time IS NULL AND ( ( %d > 0 AND location_id = %d ) OR ( %d <= 0 AND location_id IS NULL ) ) ORDER BY exception_date ASC",
				$staff_id,
				$start_date,
				$end_date,
				'closed',
				$location,
				$location,
				$location
			),
			ARRAY_A
		);

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

	/**
	 * Returns full-day closed exceptions for a staff member.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function closed_days_for_staff( int $staff_id, ?int $location_id = null, int $limit = 500 ): array {
		global $wpdb;

		if ( $staff_id <= 0 ) {
			return array();
		}

		$limit    = max( 1, min( 1000, $limit ) );
		$location = null !== $location_id && $location_id > 0 ? $location_id : 0;
		$rows     = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id = %d AND exception_type = %s AND start_time IS NULL AND end_time IS NULL AND ( ( %d > 0 AND location_id = %d ) OR ( %d <= 0 AND location_id IS NULL ) ) ORDER BY exception_date ASC LIMIT %d",
				$staff_id,
				'closed',
				$location,
				$location,
				$location,
				$limit
			),
			ARRAY_A
		);

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

	/**
	 * Replaces full-day closed exceptions for a staff member inside a local date window.
	 *
	 * @param string[] $dates Closed dates to persist.
	 */
	public function replace_closed_days_for_staff_between( int $staff_id, ?int $location_id, string $start_date, string $end_date, array $dates, string $reason ): void {
		global $wpdb;

		if ( $staff_id <= 0 || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $start_date ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $end_date ) ) {
			return;
		}

		$location = null !== $location_id && $location_id > 0 ? $location_id : 0;
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id = %d AND exception_date >= %s AND exception_date <= %s AND exception_type = %s AND start_time IS NULL AND end_time IS NULL AND ( ( %d > 0 AND location_id = %d ) OR ( %d <= 0 AND location_id IS NULL ) )",
				$staff_id,
				$start_date,
				$end_date,
				'closed',
				$location,
				$location,
				$location
			)
		);

		$now = gmdate( 'Y-m-d H:i:s' );
		foreach ( $dates as $date ) {
			if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) || $date < $start_date || $date > $end_date ) {
				continue;
			}

			$wpdb->insert(
				$this->table(),
				array(
					'staff_id'       => $staff_id,
					'location_id'    => $location_id,
					'service_id'     => null,
					'exception_date' => $date,
					'start_time'     => null,
					'end_time'       => null,
					'exception_type' => 'closed',
					'reason'         => $reason,
					'created_at'     => $now,
					'updated_at'     => $now,
				),
				array( '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
			);
		}
	}

	/**
	 * Deletes full-day closed exceptions for a staff member inside a local date window.
	 */
	public function delete_closed_days_for_staff_between( int $staff_id, ?int $location_id, string $start_date, string $end_date ): void {
		global $wpdb;

		if ( $staff_id <= 0 || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $start_date ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $end_date ) ) {
			return;
		}

		$location = null !== $location_id && $location_id > 0 ? $location_id : 0;
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id = %d AND exception_date >= %s AND exception_date <= %s AND exception_type = %s AND start_time IS NULL AND end_time IS NULL AND ( ( %d > 0 AND location_id = %d ) OR ( %d <= 0 AND location_id IS NULL ) )",
				$staff_id,
				$start_date,
				$end_date,
				'closed',
				$location,
				$location,
				$location
			)
		);
	}

	/**
	 * Returns exceptions for a staff member on a local date.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function for_staff_date( int $staff_id, string $date, ?int $location_id = null, ?int $service_id = null ): array {
		global $wpdb;

		if ( $staff_id <= 0 || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ) {
			return array();
		}

		$location = null !== $location_id && $location_id > 0 ? $location_id : 0;
		$service  = null !== $service_id && $service_id > 0 ? $service_id : 0;

		return Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}abientot_availability_exceptions WHERE staff_id = %d AND exception_date = %s AND ( %d = 0 OR location_id IS NULL OR location_id = %d ) AND ( %d = 0 OR service_id IS NULL OR service_id = %d ) ORDER BY exception_type ASC, start_time ASC",
					$staff_id,
					$date,
					$location,
					$location,
					$service,
					$service
				),
				ARRAY_A
			),
			'availability_storage_unavailable'
		);
	}
}