<?php
/**
 * Wpdb implementation of BookingRepository.
 *
 * @package Bookline
 */

declare( strict_types=1 );

namespace Bookline\Infrastructure\Persistence;

use Bookline\Domain\Booking\Booking;
use Bookline\Domain\Booking\BookingRepository;
use Bookline\Domain\Booking\BookingStatus;
use Bookline\Domain\Booking\BusyInterval;
use Bookline\Domain\Booking\CancelledBy;
use Bookline\Domain\Booking\SlotCapacity;
use Bookline\Domain\Booking\SlotTakenException;
use Bookline\Domain\Pricing\Money;
use Bookline\Domain\Shared\TimeZoneId;
use Bookline\Domain\Shared\Uuid;
use DateTimeImmutable;
use RuntimeException;

/**
 * Includes docs/04 §3 query methods with prepared statements.
 *
 * @since 0.1.0
 */
final class WpdbBookingRepository extends AbstractWpdbRepository implements BookingRepository {

	/**
	 * Find by database id.
	 *
	 * {@inheritDoc}
	 *
	 * @param int $id Parameter.
	 * @since 0.1.0
	 */
	public function find_by_id( int $id ): ?Booking {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `id` = %d AND `deleted_at` IS NULL LIMIT 1',
			$this->table( 'bookings' ),
			$id
		);
		$row = $this->db->get_row( $sql, ARRAY_A );
		return is_array( $row ) ? $this->hydrate( $row ) : null;
	}

	/**
	 * Find by UUID.
	 *
	 * {@inheritDoc}
	 *
	 * @param Uuid $uuid Parameter.
	 * @since 0.1.0
	 */
	public function find_by_uuid( Uuid $uuid ): ?Booking {
		$sql = $this->db->prepare(
			'SELECT * FROM %i WHERE `uuid` = %s AND `deleted_at` IS NULL LIMIT 1',
			$this->table( 'bookings' ),
			$uuid->to_string()
		);
		$row = $this->db->get_row( $sql, ARRAY_A );
		return is_array( $row ) ? $this->hydrate( $row ) : null;
	}

	/**
	 * Persist the entity.
	 *
	 * {@inheritDoc}
	 *
	 * @param Booking $booking          Parameter.
	 * @param bool    $unique_slot_lock Whether to set slot_lock_key.
	 * @since 0.1.0
	 *
	 * @throws SlotTakenException When the unique slot lock collides.
	 */
	public function save( Booking $booking, bool $unique_slot_lock = false ): void {
		$data = $this->to_row( $booking, $unique_slot_lock );
		if ( null === $booking->id() ) {
			$result = $this->db->insert( $this->table( 'bookings' ), $data );
			if ( false === $result ) {
				$this->throw_on_duplicate_or_failure();
			}
			$booking->assign_id( $this->last_insert_id() );
			return;
		}
		$result = $this->db->update(
			$this->table( 'bookings' ),
			$data,
			[ 'id' => $booking->id() ]
		);
		if ( false === $result ) {
			$this->throw_on_duplicate_or_failure();
		}
	}

	/**
	 * Soft-delete the entity.
	 *
	 * {@inheritDoc}
	 *
	 * @param Booking           $booking Parameter.
	 * @param DateTimeImmutable $when    Parameter.
	 * @since 0.1.0
	 */
	public function soft_delete( Booking $booking, DateTimeImmutable $when ): void {
		$booking->soft_delete( $when );
		$this->save( $booking, false );
	}

	/**
	 * Find busy intervals for agent.
	 *
	 * {@inheritDoc}
	 *
	 * @param int               $agent_id Parameter.
	 * @param DateTimeImmutable $from_utc Parameter.
	 * @param DateTimeImmutable $to_utc   Parameter.
	 * @since 0.1.0
	 */
	public function find_busy_intervals_for_agent( int $agent_id, DateTimeImmutable $from_utc, DateTimeImmutable $to_utc ): array {
		$sql = $this->db->prepare(
			'SELECT `id`, `buffer_start_utc`, `buffer_end_utc`, `attendees` FROM %i
			WHERE `agent_id` = %d
				AND `deleted_at` IS NULL
				AND `status` IN (\'pending\',\'approved\',\'confirmed\',\'no_show\',\'completed\')
				AND `buffer_start_utc` < %s
				AND `buffer_end_utc` > %s
			ORDER BY `buffer_start_utc` ASC',
			$this->table( 'bookings' ),
			$agent_id,
			$this->format_datetime( $to_utc ),
			$this->format_datetime( $from_utc )
		);
		return $this->map_busy( $sql );
	}

	/**
	 * Find busy intervals for location.
	 *
	 * {@inheritDoc}
	 *
	 * @param int               $location_id Parameter.
	 * @param DateTimeImmutable $from_utc    Parameter.
	 * @param DateTimeImmutable $to_utc      Parameter.
	 * @since 0.1.0
	 */
	public function find_busy_intervals_for_location( int $location_id, DateTimeImmutable $from_utc, DateTimeImmutable $to_utc ): array {
		$sql = $this->db->prepare(
			'SELECT `id`, `buffer_start_utc`, `buffer_end_utc`, `attendees` FROM %i
			WHERE `location_id` = %d
				AND `deleted_at` IS NULL
				AND `status` IN (\'pending\',\'approved\',\'confirmed\',\'no_show\',\'completed\')
				AND `buffer_start_utc` < %s
				AND `buffer_end_utc` > %s
			ORDER BY `buffer_start_utc` ASC',
			$this->table( 'bookings' ),
			$location_id,
			$this->format_datetime( $to_utc ),
			$this->format_datetime( $from_utc )
		);
		return $this->map_busy( $sql );
	}

	/**
	 * Find capacity used per slot.
	 *
	 * {@inheritDoc}
	 *
	 * @param int               $service_id Parameter.
	 * @param DateTimeImmutable $from_utc   Parameter.
	 * @param DateTimeImmutable $to_utc     Parameter.
	 * @since 0.1.0
	 */
	public function find_capacity_used_per_slot( int $service_id, DateTimeImmutable $from_utc, DateTimeImmutable $to_utc ): array {
		$sql = $this->db->prepare(
			'SELECT `start_utc`, SUM(`attendees`) AS `attendees` FROM %i
			WHERE `service_id` = %d
				AND `deleted_at` IS NULL
				AND `status` IN (\'pending\',\'approved\',\'confirmed\',\'no_show\',\'completed\')
				AND `start_utc` >= %s
				AND `start_utc` < %s
			GROUP BY `start_utc`
			ORDER BY `start_utc` ASC',
			$this->table( 'bookings' ),
			$service_id,
			$this->format_datetime( $from_utc ),
			$this->format_datetime( $to_utc )
		);
		$rows = $this->db->get_results( $sql, ARRAY_A );
		if ( ! is_array( $rows ) ) {
			return [];
		}
		$out = [];
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				continue;
			}
			$out[] = new SlotCapacity(
				$this->parse_datetime( (string) $row['start_utc'] ),
				(int) $row['attendees']
			);
		}
		return $out;
	}

	/**
	 * Find upcoming for customer.
	 *
	 * {@inheritDoc}
	 *
	 * @param int               $customer_id Parameter.
	 * @param DateTimeImmutable $from_utc    Parameter.
	 * @param int               $limit       Parameter.
	 * @since 0.1.0
	 */
	public function find_upcoming_for_customer( int $customer_id, DateTimeImmutable $from_utc, int $limit = 50 ): array {
		$sql = $this->db->prepare(
			'SELECT * FROM %i
			WHERE `customer_id` = %d
				AND `deleted_at` IS NULL
				AND `start_utc` >= %s
				AND `status` NOT IN (\'cancelled\',\'rescheduled\')
			ORDER BY `start_utc` ASC
			LIMIT %d',
			$this->table( 'bookings' ),
			$customer_id,
			$this->format_datetime( $from_utc ),
			$limit
		);
		return $this->map_bookings( $sql );
	}

	/**
	 * Find history for customer.
	 *
	 * {@inheritDoc}
	 *
	 * @param int $customer_id Parameter.
	 * @param int $limit       Parameter.
	 * @since 0.1.0
	 */
	public function find_history_for_customer( int $customer_id, int $limit = 100 ): array {
		$limit = max( 1, min( 200, $limit ) );
		$sql   = $this->db->prepare(
			'SELECT * FROM %i
			WHERE `customer_id` = %d
				AND `deleted_at` IS NULL
				AND `status` != %s
			ORDER BY `start_utc` DESC
			LIMIT %d',
			$this->table( 'bookings' ),
			$customer_id,
			BookingStatus::Rescheduled->value,
			$limit
		);
		return $this->map_bookings( $sql );
	}

	/**
	 * Summarise bookings for customers.
	 *
	 * {@inheritDoc}
	 *
	 * @param list<int> $customer_ids Parameter.
	 * @return array<int, array{count: int, first_start: DateTimeImmutable}>
	 * @since 0.1.0
	 */
	public function summarise_for_customers( array $customer_ids ): array {
		$ids = array_values(
			array_unique(
				array_filter(
					array_map( 'intval', $customer_ids ),
					static fn( int $id ): bool => $id > 0
				)
			)
		);
		if ( [] === $ids ) {
			return [];
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$sql          = $this->db->prepare(
			"SELECT `customer_id`, COUNT(*) AS `cnt`, MIN(`start_utc`) AS `first_start`
			FROM %i
			WHERE `deleted_at` IS NULL
				AND `status` != %s
				AND `customer_id` IN ({$placeholders})
			GROUP BY `customer_id`",
			$this->table( 'bookings' ),
			BookingStatus::Rescheduled->value,
			...$ids
		);
		$rows = $this->db->get_results( $sql, ARRAY_A );
		if ( ! is_array( $rows ) ) {
			return [];
		}

		$out = [];
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				continue;
			}
			$customer_id = (int) ( $row['customer_id'] ?? 0 );
			if ( $customer_id <= 0 ) {
				continue;
			}
			$first = isset( $row['first_start'] ) && is_string( $row['first_start'] ) && '' !== $row['first_start']
				? $this->parse_datetime( $row['first_start'] )
				: null;
			if ( null === $first ) {
				continue;
			}
			$out[ $customer_id ] = [
				'count'       => (int) ( $row['cnt'] ?? 0 ),
				'first_start' => $first,
			];
		}
		return $out;
	}

	/**
	 * Find needing reminders.
	 *
	 * {@inheritDoc}
	 *
	 * @param DateTimeImmutable $from_utc Parameter.
	 * @param DateTimeImmutable $to_utc   Parameter.
	 * @since 0.1.0
	 */
	public function find_needing_reminders( DateTimeImmutable $from_utc, DateTimeImmutable $to_utc ): array {
		$sql = $this->db->prepare(
			'SELECT * FROM %i
			WHERE `deleted_at` IS NULL
				AND `status` IN (\'pending\',\'approved\',\'confirmed\')
				AND `start_utc` >= %s
				AND `start_utc` < %s
			ORDER BY `start_utc` ASC',
			$this->table( 'bookings' ),
			$this->format_datetime( $from_utc ),
			$this->format_datetime( $to_utc )
		);
		return $this->map_bookings( $sql );
	}

	/**
	 * Find for admin calendar.
	 *
	 * {@inheritDoc}
	 *
	 * @param DateTimeImmutable $from_utc  Parameter.
	 * @param DateTimeImmutable $to_utc    Parameter.
	 * @param list<int>|null    $agent_ids Parameter.
	 * @since 0.1.0
	 */
	public function find_for_admin_calendar( DateTimeImmutable $from_utc, DateTimeImmutable $to_utc, ?array $agent_ids = null ): array {
		if ( null === $agent_ids || [] === $agent_ids ) {
			$sql = $this->db->prepare(
				'SELECT * FROM %i
				WHERE `deleted_at` IS NULL
					AND `status` NOT IN (\'cancelled\',\'rescheduled\')
					AND `start_utc` >= %s
					AND `start_utc` < %s
				ORDER BY `start_utc` ASC',
				$this->table( 'bookings' ),
				$this->format_datetime( $from_utc ),
				$this->format_datetime( $to_utc )
			);
			return $this->map_bookings( $sql );
		}

		// Filter in PHP after the indexed status_start range scan — keeps prepare() a literal string.
		$all = $this->find_for_admin_calendar( $from_utc, $to_utc, null );
		$want = array_fill_keys( array_map( 'intval', $agent_ids ), true );
		$out  = [];
		foreach ( $all as $booking ) {
			$aid = $booking->agent_id();
			if ( null !== $aid && isset( $want[ $aid ] ) ) {
				$out[] = $booking;
			}
		}
		return $out;
	}

	/**
	 * Lock an agent row for update.
	 *
	 * {@inheritDoc}
	 *
	 * @param int $agent_id Parameter.
	 * @since 0.1.0
	 */
	public function lock_agent_for_update( int $agent_id ): void {
		$sql = $this->db->prepare(
			'SELECT `id` FROM %i WHERE `id` = %d FOR UPDATE',
			$this->table( 'agents' ),
			$agent_id
		);
		$this->db->get_var( $sql );
	}

	/**
	 * Lock overlapping occupying bookings for an agent.
	 *
	 * {@inheritDoc}
	 *
	 * @param int               $agent_id            Parameter.
	 * @param DateTimeImmutable $buffer_start_utc    Parameter.
	 * @param DateTimeImmutable $buffer_end_utc      Parameter.
	 * @param int|null          $exclude_booking_id Parameter.
	 * @since 0.1.0
	 */
	public function lock_overlapping_for_agent(
		int $agent_id,
		DateTimeImmutable $buffer_start_utc,
		DateTimeImmutable $buffer_end_utc,
		?int $exclude_booking_id = null,
	): array {
		if ( null === $exclude_booking_id ) {
			$sql = $this->db->prepare(
				'SELECT * FROM %i
				WHERE `agent_id` = %d
					AND `deleted_at` IS NULL
					AND `status` IN (\'pending\',\'approved\',\'confirmed\')
					AND `buffer_start_utc` < %s
					AND `buffer_end_utc` > %s
				FOR UPDATE',
				$this->table( 'bookings' ),
				$agent_id,
				$this->format_datetime( $buffer_end_utc ),
				$this->format_datetime( $buffer_start_utc )
			);
			return $this->map_bookings( $sql );
		}

		$sql = $this->db->prepare(
			'SELECT * FROM %i
			WHERE `agent_id` = %d
				AND `deleted_at` IS NULL
				AND `status` IN (\'pending\',\'approved\',\'confirmed\')
				AND `buffer_start_utc` < %s
				AND `buffer_end_utc` > %s
				AND `id` <> %d
			FOR UPDATE',
			$this->table( 'bookings' ),
			$agent_id,
			$this->format_datetime( $buffer_end_utc ),
			$this->format_datetime( $buffer_start_utc ),
			$exclude_booking_id
		);
		return $this->map_bookings( $sql );
	}

	/**
	 * Find expired soft holds.
	 *
	 * {@inheritDoc}
	 *
	 * @param DateTimeImmutable $now   Parameter.
	 * @param int               $limit Parameter.
	 * @since 0.1.0
	 */
	public function find_expired_holds( DateTimeImmutable $now, int $limit = 100 ): array {
		$out = [];
		foreach ( $this->find_pending_holds( $limit * 5 ) as $booking ) {
			if ( $booking->is_hold_expired( $now ) ) {
				$out[] = $booking;
				if ( count( $out ) >= $limit ) {
					break;
				}
			}
		}
		return $out;
	}

	/**
	 * Find pending soft holds (any expiry).
	 *
	 * {@inheritDoc}
	 *
	 * @param int $limit Parameter.
	 * @since 0.1.0
	 */
	public function find_pending_holds( int $limit = 100 ): array {
		$sql = $this->db->prepare(
			'SELECT * FROM %i
			WHERE `deleted_at` IS NULL
				AND `status` = \'pending\'
				AND `meta` IS NOT NULL
			ORDER BY `id` ASC
			LIMIT %d',
			$this->table( 'bookings' ),
			$limit * 5
		);
		$candidates = $this->map_bookings( $sql );
		$out        = [];
		foreach ( $candidates as $booking ) {
			if ( null !== $booking->hold_expires_at() ) {
				$out[] = $booking;
				if ( count( $out ) >= $limit ) {
					break;
				}
			}
		}
		return $out;
	}

	/**
	 * Map busy-interval query rows.
	 *
	 * @param string|null $sql Prepared SQL.
	 * @return list<BusyInterval>
	 */
	private function map_busy( ?string $sql ): array {
		$rows = $this->db->get_results( $sql, ARRAY_A );
		if ( ! is_array( $rows ) ) {
			return [];
		}
		$out = [];
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				continue;
			}
			$out[] = new BusyInterval(
				(int) $row['id'],
				$this->parse_datetime( (string) $row['buffer_start_utc'] ),
				$this->parse_datetime( (string) $row['buffer_end_utc'] ),
				(int) $row['attendees']
			);
		}
		return $out;
	}

	/**
	 * Map booking query rows.
	 *
	 * @param string|null $sql Prepared SQL.
	 * @return list<Booking>
	 */
	private function map_bookings( ?string $sql ): array {
		$rows = $this->db->get_results( $sql, ARRAY_A );
		if ( ! is_array( $rows ) ) {
			return [];
		}
		$out = [];
		foreach ( $rows as $row ) {
			if ( ! is_array( $row ) ) {
				continue;
			}
			$out[] = $this->hydrate( $row );
		}
		return $out;
	}

	/**
	 * Map a database row to an entity.
	 *
	 * @param array<string, mixed> $row Database row.
	 */
	private function hydrate( array $row ): Booking {
		return new Booking(
			(int) $row['id'],
			new Uuid( (string) $row['uuid'] ),
			null !== $row['order_id'] ? (int) $row['order_id'] : null,
			(int) $row['customer_id'],
			(int) $row['service_id'],
			null !== $row['duration_id'] ? (int) $row['duration_id'] : null,
			null !== $row['agent_id'] ? (int) $row['agent_id'] : null,
			null !== $row['location_id'] ? (int) $row['location_id'] : null,
			$this->parse_datetime( (string) $row['start_utc'] ),
			$this->parse_datetime( (string) $row['end_utc'] ),
			$this->parse_datetime( (string) $row['buffer_start_utc'] ),
			$this->parse_datetime( (string) $row['buffer_end_utc'] ),
			new TimeZoneId( (string) $row['timezone'] ),
			$this->parse_datetime( (string) $row['start_local'] ),
			BookingStatus::from( (string) $row['status'] ),
			(int) $row['attendees'],
			new Money( (int) $row['price_minor'], (string) $row['currency'] ),
			null !== $row['notes'] ? (string) $row['notes'] : null,
			null !== $row['internal_notes'] ? (string) $row['internal_notes'] : null,
			null !== $row['manage_token_hash'] ? (string) $row['manage_token_hash'] : null,
			null !== $row['cancelled_at'] ? $this->parse_datetime( (string) $row['cancelled_at'] ) : null,
			null !== $row['cancelled_by'] ? CancelledBy::from( (string) $row['cancelled_by'] ) : null,
			null !== $row['cancel_reason'] ? (string) $row['cancel_reason'] : null,
			null !== $row['rescheduled_from'] ? (int) $row['rescheduled_from'] : null,
			$this->decode_meta( isset( $row['meta'] ) ? (string) $row['meta'] : null ),
			$this->parse_datetime( (string) $row['created_at'] ),
			$this->parse_datetime( (string) $row['updated_at'] ),
			null !== $row['deleted_at'] ? $this->parse_datetime( (string) $row['deleted_at'] ) : null,
		);
	}

	/**
	 * Map an entity to a database row.
	 *
	 * @param Booking $booking          Entity.
	 * @param bool    $unique_slot_lock Whether to manage the capacity-1 unique slot lock key.
	 * @return array<string, mixed>
	 */
	private function to_row( Booking $booking, bool $unique_slot_lock = false ): array {
		$row = [
			'uuid'              => $booking->uuid()->to_string(),
			'order_id'          => $booking->order_id(),
			'customer_id'       => $booking->customer_id(),
			'service_id'        => $booking->service_id(),
			'duration_id'       => $booking->duration_id(),
			'agent_id'          => $booking->agent_id(),
			'location_id'       => $booking->location_id(),
			'start_utc'         => $this->format_datetime( $booking->start_utc() ),
			'end_utc'           => $this->format_datetime( $booking->end_utc() ),
			'buffer_start_utc'  => $this->format_datetime( $booking->buffer_start_utc() ),
			'buffer_end_utc'    => $this->format_datetime( $booking->buffer_end_utc() ),
			'timezone'          => $booking->timezone()->to_string(),
			'start_local'       => $this->format_datetime( $booking->start_local() ),
			'status'            => $booking->status()->value,
			'attendees'         => $booking->attendees(),
			'price_minor'       => $booking->price()->amount_minor(),
			'currency'          => $booking->price()->currency(),
			'notes'             => $booking->notes(),
			'internal_notes'    => $booking->internal_notes(),
			'manage_token_hash' => $booking->manage_token_hash(),
			'cancelled_at'      => null === $booking->cancelled_at() ? null : $this->format_datetime( $booking->cancelled_at() ),
			'cancelled_by'      => null === $booking->cancelled_by() ? null : $booking->cancelled_by()->value,
			'cancel_reason'     => $booking->cancel_reason(),
			'rescheduled_from'  => $booking->rescheduled_from(),
			'meta'              => $this->encode_meta( $booking->meta() ),
			'created_at'        => $this->format_datetime( $booking->created_at() ),
			'updated_at'        => $this->format_datetime( $booking->updated_at() ),
			'deleted_at'        => null === $booking->deleted_at() ? null : $this->format_datetime( $booking->deleted_at() ),
		];

		if ( $unique_slot_lock ) {
			$row['slot_lock_key'] = null;
			if ( $booking->occupies_slot() && null !== $booking->agent_id() ) {
				$row['slot_lock_key'] = sprintf(
					'%d:%s:%d',
					$booking->agent_id(),
					$this->format_datetime( $booking->start_utc() ),
					$booking->service_id()
				);
			}
		} elseif ( ! $booking->occupies_slot() ) {
			$row['slot_lock_key'] = null;
		}

		return $row;
	}

	/**
	 * Translate duplicate-key and deadlock errors into SlotTakenException.
	 *
	 * @throws SlotTakenException When MySQL reports a duplicate key or deadlock.
	 * @throws RuntimeException   On other write failures.
	 */
	private function throw_on_duplicate_or_failure(): void {
		$error = strtolower( (string) $this->db->last_error );
		if ( str_contains( $error, 'duplicate' ) || str_contains( $error, 'deadlock' ) ) {
			throw new SlotTakenException( 'Slot lock unique constraint rejected the write.' );
		}
		throw new RuntimeException( 'Failed to persist booking: ' . esc_html( (string) $this->db->last_error ) );
	}
}