USE "Gwent - The Witcher Card Game"

GO

CREATE OR ALTER PROCEDURE populateFactions (@rows INT) AS
    WHILE @rows > 0
        BEGIN
            INSERT INTO Factions(name, totalMatches, totalWins, totalLosses, totalCards, totalDraws)
            VALUES ('test', -1, -1, -1, -1, -1)
            SET @rows = @rows - 1
        END

GO

CREATE OR ALTER PROCEDURE populatePlayers (@rows INT) AS
    WHILE @rows > 0
        BEGIN
            INSERT INTO Players(name, totalMatches, totalWins, totalLosses, totalDraws, reward_points, scraps, ores, meteorite_powder, rank)
            VALUES ('test', -1, -1, -1, -1, -1, -1, -1, -1, -1)
            SET @rows = @rows - 1
        END

GO

CREATE OR ALTER PROCEDURE populateMatches (@rows INT) AS
    DECLARE @player1 INT
    SET @player1 = (SELECT TOP 1 hmy FROM Players)
    DECLARE @player2 INT
    SET @player2 = (SELECT TOP 1 hmy FROM Players WHERE hmy <> @player1)
    DECLARE @faction INT
    SET @faction = (SELECT TOP 1 hmy FROM Factions)
    WHILE @rows > 0
        BEGIN
            INSERT INTO Matches(hPlayer1, hPlayer2, hPlayer1Faction, hPlayer2Faction, result)
            VALUES (@player1, @player2, @faction, @faction, 1)
            SET @rows = @rows - 1
        END

GO

CREATE OR ALTER VIEW getMatches AS
    SELECT p1.name as player1, p2.name as player2, f1.name as player1Faction, f2.name as player2Faction
    FROM Matches m
    JOIN Players p1 on m.hPlayer1 = p1.hmy
    JOIN Players p2 on m.hPlayer2 = p2.hmy
    JOIN Factions f1 on m.hPlayer1Faction = f1.hmy
    JOIN Factions f2 on m.hPlayer2Faction = f2.hmy

GO

EXEC addTests 'test3'
EXEC addToTables 'Factions'
EXEC addToTables 'Players'
EXEC addToTables 'Matches'
EXEC addToViews 'getMatches'
EXEC addTestTables 'test3', 'Factions', 1000, 3
EXEC addTestTables 'test3', 'Players', 1000, 2
EXEC addTestTables 'test3', 'Matches', 1000, 1
EXEC addTestViews 'test3', 'getMatches'

GO

EXEC runTest 'test3'