CREATE PROCEDURE usp_SwitchRoom(@tripId INT, @targetRoomId INT)
AS
BEGIN
    DECLARE @hotelId INT = (SELECT TOP(1)
        r.HotelId
    FROM Trips AS t
    JOIN Rooms AS r ON r.Id = t.RoomId
    WHERE t.Id = @tripId)

    DECLARE @targetHotel INT = (SELECT TOP(1)
        HotelId
    FROM Rooms
    WHERE Id = @targetRoomId)

    IF(@HotelId != @targetHotel)
        BEGIN
            RAISERROR('Target room is in another hotel!', 16, 1)
            RETURN
        END

    DECLARE @accountOnTrip INT = (SELECT
        COUNT(*)
    FROM AccountsTrips
    WHERE TripId = @tripId)

    DECLARE @roomBeds INT = (SELECT TOP(1)
        *
    FROM Rooms
    WHERE Id = @targetRoomId)

    IF (@accountOnTrip > @roomBeds)
        BEGIN
            RAISERROR('Not enough beds in target room!', 16, 1)
            RETURN
        END

        UPDATE Trips
        SET RoomId = @targetRoomId
        WHERE Id = @tripId

END