Tests

-- Used to get userGameId from the BALI game by @USERNAME
CREATE FUNCTION udf_GetUserGamesIdFromBali(@username varchar(50))
RETURNS INT
AS
BEGIN
    DECLARE @userGameId int = (SELECT ug.Id
         FROM Users AS u
         INNER JOIN UsersGames AS ug
         ON u.Id = ug.UserId AND
            u.Username = @username
         INNER JOIN Games AS g
         ON g.Name = 'Bali' AND
            ug.GameId = g.Id);

    RETURN @userGameId;
END
GO

-- Gets all userGames Ids from the BALI GAME
CREATE FUNCTION udf_UsersGamesIdsToUseFromBali()
RETURNS TABLE
AS
RETURN( SELECT ug.Id
        FROM Users AS u
        INNER JOIN UsersGames AS ug
        ON u.Id = ug.UserId AND
            u.Username IN ('baleremuda', 'loosenoise', 'inguinalself', 'buildingdeltoid', 'monoxidecos')
        INNER JOIN Games AS g
        ON g.Name = 'Bali' AND
            ug.GameId = g.Id)
GO

-- Gets all items in range of ids
CREATE FUNCTION udf_GetGroupOfItemsById(@minId int, @maxId int)
RETURNS TABLE
AS
 RETURN (SELECT i.Id, i.Name, i.Price, i.MinLevel
          FROM Items AS i
          WHERE Id >= @minId AND Id <= @maxId)
GO

-- When inserted into UserGameItems checks if the user level is enough and if it is it takes cash from him
ALTER TRIGGER T_UserGameItems_AFTER_INSERT
ON UserGameItems
INSTEAD OF INSERT
AS
BEGIN

-- INSERT THE DATA INTO THE TABLE
INSERT INTO UserGameItems (ItemId, UserGameId)
    SELECT ins.ItemId, ins.UserGameId
     FROM inserted AS ins
     INNER JOIN Items AS i
     ON ins.ItemId = i.Id
     INNER JOIN UsersGames AS ug
     ON i.MinLevel <= ug.Level AND
        ug.Id = ins.UserGameId

-- CURSOR FOR EVERY SINGLE ITEM
DECLARE inserted_Cursor CURSOR
FOR (SELECT ins.ItemId, ins.UserGameId
     FROM inserted AS ins
     INNER JOIN Items AS i
     ON ins.ItemId = i.Id
     INNER JOIN UsersGames AS ug
     ON i.MinLevel <= ug.Level AND
        ug.Id = ins.UserGameId)

OPEN inserted_Cursor

DECLARE @currentItemId int;
DECLARE @currentUsersGamesId int;

FETCH NEXT FROM inserted_Cursor
INTO @currentItemId, @currentUsersGamesId

WHILE @@FETCH_STATUS = 0
BEGIN

    UPDATE UsersGames
     SET Cash -= (SELECT i.Price
                   FROM Items AS i
                   WHERE i.Id = @currentItemId)
     WHERE Id = @currentUsersGamesId

    FETCH NEXT FROM inserted_Cursor
    INTO @currentItemId, @currentUsersGamesId
END

CLOSE inserted_Cursor;
DEALLOCATE inserted_Cursor;

END
GO

CREATE PROC usp_AddTheTwoSetOfItemsToPlayer (@username varchar(50))
AS
BEGIN
    INSERT INTO UserGameItems (ItemId, UserGameId)
    SELECT i.Id, dbo.udf_GetUserGamesIdFromBali(@username)
     FROM dbo.udf_GetGroupOfItemsById(251, 299) AS i

    INSERT INTO UserGameItems (ItemId, UserGameId)
    SELECT i.Id, dbo.udf_GetUserGamesIdFromBali(@username)
     FROM dbo.udf_GetGroupOfItemsById(501, 539) AS i
END
GO

UPDATE UsersGames
 SET Cash += 50000
 WHERE Id IN (SELECT * FROM dbo.udf_UsersGamesIdsToUseFromBali())

EXEC usp_AddTheTwoSetOfItemsToPlayer 'baleremuda';
EXEC usp_AddTheTwoSetOfItemsToPlayer 'loosenoise';
EXEC usp_AddTheTwoSetOfItemsToPlayer 'inguinalself';
EXEC usp_AddTheTwoSetOfItemsToPlayer 'buildingdeltoid';
EXEC usp_AddTheTwoSetOfItemsToPlayer 'monoxidecos';

SELECT ug.Id,
       u.Username,
       ug.Cash,
       g.Name,
       i.Name,
       i.Price,
       i.Id
 FROM Users AS u
 INNER JOIN UsersGames AS ug
 ON u.Id = ug.UserId AND
    u.Username IN ('baleremuda',
                   'loosenoise',
                   'inguinalself',
                   'buildingdeltoid',
                   'monoxidecos')
 INNER JOIN Games AS g
 ON ug.GameId = g.Id AND
    g.Name = 'Bali'
 INNER JOIN UserGameItems AS ugi
 ON ug.Id = ugi.UserGameId
 INNER JOIN Items AS i
 ON ugi.ItemId = i.Id