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

namespace Abientot\Repositories;

use Abientot\Support\Db;

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

final class AvailabilityRepository extends BaseRepository {
	protected string $table_suffix = 'availability_rules';

	/**
	 * Finds active recurring rules for a staff member and weekday.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function rules_for_staff_weekday( int $staff_id, int $weekday ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}abientot_availability_rules WHERE staff_id = %d AND weekday = %d AND is_active = 1 ORDER BY start_time ASC",
				$staff_id,
				$weekday
			),
			ARRAY_A
		);

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

	/**
	 * Finds active recurring rules for a staff member, local weekday, and date.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function rules_for_staff_weekday_date( int $staff_id, int $weekday, string $date, ?int $location_id = null, ?int $service_id = null ): array {
		global $wpdb;

		if ( $staff_id <= 0 || $weekday < 0 || $weekday > 6 || ! 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;

		$rows = Db::rows(
			$wpdb->suppress_errors( true ),
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}abientot_availability_rules WHERE staff_id = %d AND weekday = %d AND is_active = 1 AND (effective_from IS NULL OR effective_from <= %s) AND (effective_to IS NULL OR effective_to >= %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 rule_type ASC, start_time ASC",
					$staff_id,
					$weekday,
					$date,
					$date,
					$location,
					$location,
					$service,
					$service
				),
				ARRAY_A
			),
			'availability_storage_unavailable'
		);

		$override_rows = array_values(
			array_filter(
				$rows,
				static function ( array $row ): bool {
					return ! empty( $row['effective_from'] ) || ! empty( $row['effective_to'] );
				}
			)
		);

		if ( $override_rows ) {
			$rows = $override_rows;
		}

		/**
		 * Filters recurring availability rules before slot generation.
		 *
		 * @param array<int,array<string,mixed>> $rows    Availability rules.
		 * @param array<string,mixed>            $context Lookup context.
		 */
		$rows = apply_filters(
			'abientot_availability_rules',
			$rows,
			array(
				'staff_id'    => $staff_id,
				'weekday'     => $weekday,
				'date'        => $date,
				'location_id' => $location_id,
				'service_id'  => $service_id,
			)
		);

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

	/**
	 * Returns all active weekly rules for a staff member.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function weekly_rules_for_staff( int $staff_id ): array {
		global $wpdb;

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

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}abientot_availability_rules WHERE staff_id = %d AND is_active = 1 AND effective_from IS NULL AND effective_to IS NULL ORDER BY weekday ASC, rule_type ASC, start_time ASC",
				$staff_id
			),
			ARRAY_A
		);

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

	/**
	 * Returns date-scoped schedule overrides for a staff member.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function overrides_for_staff( int $staff_id, int $limit = 100 ): array {
		global $wpdb;

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

		$limit = max( 1, min( 500, $limit ) );
		$rows  = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}abientot_availability_rules WHERE staff_id = %d AND is_active = 1 AND (effective_from IS NOT NULL OR effective_to IS NOT NULL) ORDER BY effective_from DESC, weekday ASC, rule_type ASC, start_time ASC LIMIT %d",
				$staff_id,
				$limit
			),
			ARRAY_A
		);

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

	/**
	 * Replaces the free weekly working/break rules for the default staff member.
	 *
	 * @param array<int,array<string,string|int|null>> $rules Rules to insert.
	 */
	public function replace_weekly_rules( int $staff_id, array $rules ): void {
		global $wpdb;

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

		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->prefix}abientot_availability_rules WHERE staff_id = %d AND rule_type IN ('working', 'break') AND effective_from IS NULL AND effective_to IS NULL",
				$staff_id
			)
		);

		$now = gmdate( 'Y-m-d H:i:s' );

		foreach ( $rules as $rule ) {
			$wpdb->insert(
				$this->table(),
				array(
					'staff_id'    => $staff_id,
					'location_id' => isset( $rule['location_id'] ) ? (int) $rule['location_id'] : null,
					'service_id'  => null,
					'weekday'     => (int) $rule['weekday'],
					'start_time'  => (string) $rule['start_time'],
					'end_time'    => (string) $rule['end_time'],
					'rule_type'   => (string) $rule['rule_type'],
					'is_active'   => 1,
					'created_at'  => $now,
					'updated_at'  => $now,
				),
				array( '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%d', '%s', '%s' )
			);
		}
	}

	/**
	 * Adds one date-scoped working or break rule.
	 *
	 * @param array<string,string|int|null> $rule Rule data.
	 */
	public function add_override_rule( int $staff_id, array $rule ): int {
		global $wpdb;

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

		$now = gmdate( 'Y-m-d H:i:s' );
		$wpdb->insert(
			$this->table(),
			array(
				'staff_id'       => $staff_id,
				'location_id'    => isset( $rule['location_id'] ) ? (int) $rule['location_id'] : null,
				'service_id'     => null,
				'weekday'        => (int) $rule['weekday'],
				'start_time'     => (string) $rule['start_time'],
				'end_time'       => (string) $rule['end_time'],
				'rule_type'      => (string) $rule['rule_type'],
				'effective_from' => (string) $rule['effective_from'],
				'effective_to'   => (string) $rule['effective_to'],
				'is_active'      => 1,
				'created_at'     => $now,
				'updated_at'     => $now,
			),
			array( '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s' )
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Deletes date-scoped override rules that overlap a local date range.
	 */
	public function delete_overrides_for_staff_range( int $staff_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;
		}

		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->prefix}abientot_availability_rules WHERE staff_id = %d AND is_active = 1 AND (effective_from IS NOT NULL OR effective_to IS NOT NULL) AND effective_from <= %s AND effective_to >= %s",
				$staff_id,
				$end_date,
				$start_date
			)
		);
	}
}