18. Available Room

create function udf_GetAvailableRoom
    (@HotelID int,
    @Date date,
    @People int)
returns varchar(100)
begin
    declare @FreeRoomID int =
        (select top 1
            [R].Id
        from
            Hotels as [H]
        join
            Rooms as [R]
        on
            [H].Id = [R].HotelId
        join
            Trips as [T]
        on
            [R].Id = [T].RoomId
        join
            AccountsTrips as [AT]
        on
            [T].Id = [AT].TripId
        where
            [H].Id = @HotelID
            and ([T].CancelDate is not null or @Date not between [T].ArrivalDate and [T].ReturnDate)
            and [R].Beds >= @People
        order by
            [R].Price desc)

    if @FreeRoomID is null
        return 'No rooms available'

    declare @RoomType varchar(50) = (select [Type] from Rooms where Id = @FreeRoomID)
    declare @Beds int = (select Beds from Rooms where Id = @FreeRoomID)
    declare @Price decimal(15, 2) = (select Price from Rooms where Id = @FreeRoomID)
    declare @BaseRate decimal(15, 2) = (select BaseRate from Hotels where Id = @HotelID)

    return concat('Room ', @FreeRoomID, ': ', @RoomType, ' (', @Beds, ' beds) - $', (@BaseRate + @Price) * @People)
end