-- Creates a new database when a new game is created
-- @author Eric Pietrocupo
-- @date March 29th, 2026
-- @license GNU GPL 3.0
--
-- Runs after the new game options are selected. prevent putting the queries inside the program
-- for thing easily ran in a script. Also insert constants in the database.

-- INSERT HOLDINGS using the cross product
INSERT INTO holding (fk_player, fk_stock, quantity)
SELECT player.pk, stock.pk, 0
FROM player
CROSS JOIN stock;

-- All players draw a card, but if the variant does not exists, they will simply not be used.
-- The query is very complex, it requires 2 random pool of elements to prevent the internal
-- select to return the same player or event all the time.

-- NOTE: I will try to update the players in C instead
/*
WITH free_players AS (
    SELECT pk,
           ROW_NUMBER() OVER (ORDER BY pk) AS rn
    FROM player
),
free_events AS (
    SELECT pk,
           ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM event
    WHERE fk_player IS NULL
      AND cooldown = 0
)
UPDATE event
SET fk_player = (
    SELECT fp.pk
    FROM free_players fp
    JOIN free_events fe ON fe.rn = fp.rn
    WHERE fe.pk = event.pk
)
WHERE pk IN (SELECT pk FROM free_events);
*/




