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 populateCardSet (@rows INT) AS
    WHILE @rows > 0
        BEGIN
            INSERT INTO CardSet(name, image, releaseDate)
            VALUES ('test', 'test', '2020-01-01')
            SET @rows = @rows - 1
        END

GO

CREATE OR ALTER PROCEDURE populateCards (@rows INT) AS
    DECLARE @faction INT
    SET @faction = (SELECT TOP 1 hmy FROM Factions)
    DECLARE @cardset INT
    SET @cardset = (SELECT TOP 1 hmy FROM CardSet)
    WHILE @rows > 0
        BEGIN
            INSERT INTO Cards(name, faction, description, action, image, cost, type, power, color, cardset)
            VALUES ('test', @faction, 'test', 'test', 'test', -1, 'unit', -1, 'gold', @cardset)
            SET @rows = @rows - 1
        END

GO

CREATE OR ALTER PROCEDURE populateCardsOwnership (@rows INT) AS
    DECLARE @card INT
    DECLARE @player INT
    WHILE @rows > 0
        BEGIN
            SET @card = (SELECT hmy FROM Cards ORDER BY hmy OFFSET @rows ROWS FETCH NEXT 1 ROWS ONLY)
            SET @player = (SELECT hmy FROM Players ORDER BY hmy OFFSET @rows ROWS FETCH NEXT 1 ROWS ONLY)
            INSERT INTO CardsOwnership(hCard, hPlayer, nrOfCards, nrOfPremiumCards)
            VALUES (@card, @player, 1, 1)
            SET @rows = @rows - 1
        END

GO

CREATE OR ALTER VIEW getNumberOfCardsOfEachTypeOwnByEachPlayer AS
    SELECT p.name as Player, cs.Name as CardSet, Count(c.hmy) as NumberOfCards
    FROM CardsOwnership co JOIN Cards c on co.hCard = c.hmy JOIN Players p on co.hPlayer = p.hmy JOIN CardSet cs on c.cardset = cs.hmy
    GROUP BY p.name, cs.Name


GO
EXEC addTests 'test2'

EXEC addToTables 'Factions'
EXEC addToTables 'Players'
EXEC addToTables 'Matches'
EXEC addToTables 'CardSet'
EXEC addToTables 'Cards'
EXEC addToTables 'CardsOwnership'

EXEC addToViews 'getNumberOfCardsOfEachTypeOwnByEachPlayer'

EXEC addTestTables 'test2', 'Factions', 100, 6
EXEC addTestTables 'test2', 'Players', 1000, 5
EXEC addTestTables 'test2', 'CardSet', 10, 4
EXEC addTestTables 'test2', 'Matches', 10, 3
EXEC addTestTables 'test2', 'Cards', 1000, 2
EXEC addTestTables 'test2', 'CardsOwnership', 500, 1

EXEC addTestViews 'test2', 'getNumberOfCardsOfEachTypeOwnByEachPlayer'

GO

EXEC runTest 'test2'