<?php
/**
 * @package Hafenstudios\Kurato
 */

namespace Hafenstudios\Kurato\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Wochenraster und Ausnahmen/Urlaub je Host (hs_availability, K5a).
 *
 * Zwei Arten von Zeilen (Spalte `kind`):
 * - `weekly`: ein wiederkehrendes Zeitfenster an einem Wochentag (weekday 0-6,
 *   Konvention 0=Sonntag..6=Samstag, wie PHP `date('w')`), start_time/end_time
 *   als Site-Zeitzonen-Wanduhrzeit. booking_type_id NULL = gilt fuer alle
 *   Terminarten des Hosts, sonst nur fuer die genannte Terminart.
 * - `exception`: blockt einen Datumsbereich (date_from..date_to). Ohne Zeiten
 *   ganztaegig, mit start_time/end_time nur dieses Fenster an jedem Tag des
 *   Bereichs (Urlaub vs. wiederkehrende Mittagspause an bestimmten Tagen).
 *
 * Die Umrechnung dieser Site-Zeiten nach UTC macht ausschliesslich der
 * SlotCalculator; das Repository speichert und liest nur.
 *
 * Nicht final, damit Tests die DB-Methoden per Test-Double ueberschreiben koennen.
 */
class AvailabilityRepository {

	/**
	 * Alle Verfuegbarkeits-Zeilen eines Hosts.
	 *
	 * @param int $host_id Host-ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function for_host( int $host_id ): array {
		global $wpdb;
		$table = Schema::table( 'availability' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE host_id = %d ORDER BY weekday ASC, start_time ASC, id ASC", $host_id ), ARRAY_A );
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Wochenraster-Fenster fuer eine konkrete Terminart (fuer den SlotCalculator).
	 * Liefert alle `weekly`-Zeilen des Hosts, die entweder fuer alle Terminarten
	 * gelten (booking_type_id NULL) oder genau auf die gegebene Terminart zeigen.
	 *
	 * @param int $host_id Host-ID.
	 * @param int $type_id Terminart-ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function weekly_for_type( int $host_id, int $type_id ): array {
		global $wpdb;
		$table = Schema::table( 'availability' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE host_id = %d AND kind = 'weekly' AND ( booking_type_id IS NULL OR booking_type_id = %d ) ORDER BY weekday ASC, start_time ASC, id ASC",
				$host_id,
				$type_id
			),
			ARRAY_A
		);
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Ausnahmen (Urlaub/Blockaden) eines Hosts.
	 *
	 * @param int $host_id Host-ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function exceptions_for_host( int $host_id ): array {
		global $wpdb;
		$table = Schema::table( 'availability' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table} WHERE host_id = %d AND kind = 'exception' ORDER BY date_from ASC, id ASC", $host_id ), ARRAY_A );
		return array_map( array( $this, 'hydrate' ), $rows ?: array() );
	}

	/**
	 * Legt eine Wochenraster-Zeile an.
	 *
	 * @param int      $host_id    Host-ID.
	 * @param int      $weekday    Wochentag 0-6 (0=Sonntag).
	 * @param string   $start_time 'H:i' oder 'H:i:s' (Site-Zeit).
	 * @param string   $end_time   'H:i' oder 'H:i:s' (Site-Zeit).
	 * @param int|null $type_id    Terminart-ID oder null (= alle Arten).
	 * @return int Zeilen-ID (0 bei Fehler).
	 */
	public function create_weekly( int $host_id, int $weekday, string $start_time, string $end_time, ?int $type_id = null ): int {
		if ( $host_id <= 0 || $weekday < 0 || $weekday > 6 ) {
			return 0;
		}
		$start = $this->normalize_time( $start_time );
		$end   = $this->normalize_time( $end_time );
		if ( null === $start || null === $end || $end <= $start ) {
			return 0;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert(
			Schema::table( 'availability' ),
			array(
				'host_id'         => $host_id,
				'booking_type_id' => ( null !== $type_id && $type_id > 0 ) ? $type_id : null,
				'kind'            => 'weekly',
				'weekday'         => $weekday,
				'start_time'      => $start,
				'end_time'        => $end,
				'date_from'       => null,
				'date_to'         => null,
			)
		);
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Legt eine Ausnahme (Urlaub/Blockade) an. Ohne Zeiten ganztaegig.
	 *
	 * @param int      $host_id    Host-ID.
	 * @param string   $date_from  'Y-m-d' (Site-Datum).
	 * @param string   $date_to    'Y-m-d' (Site-Datum, inklusive).
	 * @param string   $start_time Optional 'H:i' fuer teiltaegige Blockade.
	 * @param string   $end_time   Optional 'H:i' fuer teiltaegige Blockade.
	 * @param int|null $type_id    Terminart-ID oder null (= alle Arten).
	 * @return int Zeilen-ID (0 bei Fehler).
	 */
	public function create_exception( int $host_id, string $date_from, string $date_to, string $start_time = '', string $end_time = '', ?int $type_id = null ): int {
		$from = $this->normalize_date( $date_from );
		$to   = $this->normalize_date( $date_to );
		if ( $host_id <= 0 || null === $from || null === $to || $to < $from ) {
			return 0;
		}
		$start = '' !== $start_time ? $this->normalize_time( $start_time ) : null;
		$end   = '' !== $end_time ? $this->normalize_time( $end_time ) : null;
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ok = $wpdb->insert(
			Schema::table( 'availability' ),
			array(
				'host_id'         => $host_id,
				'booking_type_id' => ( null !== $type_id && $type_id > 0 ) ? $type_id : null,
				'kind'            => 'exception',
				'weekday'         => null,
				'start_time'      => $start,
				'end_time'        => $end,
				'date_from'       => $from,
				'date_to'         => $to,
			)
		);
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Loescht eine Zeile.
	 *
	 * @param int $id Zeilen-ID.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (bool) $wpdb->delete( Schema::table( 'availability' ), array( 'id' => $id ) );
	}

	/**
	 * Normalisiert eine Zeitangabe auf 'H:i:s'. Ungueltig => null.
	 *
	 * @param string $time Zeit.
	 * @return string|null
	 */
	private function normalize_time( string $time ): ?string {
		$time = trim( $time );
		if ( ! preg_match( '/^([01]?\d|2[0-3]):([0-5]\d)(?::([0-5]\d))?$/', $time, $m ) ) {
			return null;
		}
		return sprintf( '%02d:%02d:%02d', (int) $m[1], (int) $m[2], (int) ( $m[3] ?? 0 ) );
	}

	/**
	 * Normalisiert ein Datum auf 'Y-m-d'. Ungueltig => null.
	 *
	 * @param string $date Datum.
	 * @return string|null
	 */
	private function normalize_date( string $date ): ?string {
		$date = trim( $date );
		if ( ! preg_match( '/^(\d{4})-(\d{2})-(\d{2})$/', $date, $m ) ) {
			return null;
		}
		if ( ! checkdate( (int) $m[2], (int) $m[3], (int) $m[1] ) ) {
			return null;
		}
		return $date;
	}

	/**
	 * Normalisiert eine Zeile.
	 *
	 * @param array<string, mixed> $row Rohzeile.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		return array(
			'id'              => (int) $row['id'],
			'host_id'         => (int) $row['host_id'],
			'booking_type_id' => null !== $row['booking_type_id'] ? (int) $row['booking_type_id'] : null,
			'kind'            => (string) $row['kind'],
			'weekday'         => null !== $row['weekday'] ? (int) $row['weekday'] : null,
			'start_time'      => null !== $row['start_time'] ? (string) $row['start_time'] : null,
			'end_time'        => null !== $row['end_time'] ? (string) $row['end_time'] : null,
			'date_from'       => null !== $row['date_from'] ? (string) $row['date_from'] : null,
			'date_to'         => null !== $row['date_to'] ? (string) $row['date_to'] : null,
		);
	}
}
