-- Apply the game rules for the insider card management at the end of the turn
-- @author Eric Pietrocupo
-- @date March 20th, 2026
-- @license GNU GPL 3.0
--
-- I order to avoid checking for a variant in every query, I decided to put all the card
-- play and management in a separate script that will be ran if the variant is active.






-- Verify if a player is holding one of the cards currently rolled
-- and assign it to the event triggered field.

UPDATE player_event
SET istriggered = 'Y'
WHERE fk_event IN (
    SELECT event.pk
    FROM event
    JOIN roll
        ON roll.fk_stock = event.fk_stock
       AND roll.move     = event.move
       AND roll.number   = event.number
);


-- modify the results of the dice roll if event is triggered.

WITH triggered_events AS (
    SELECT event.fk_stock, event.move, event.number
    FROM player_event
    JOIN event ON event.pk = player_event.fk_event
    WHERE player_event.istriggered = 'Y'
)
UPDATE roll
SET number = number + (SELECT insider_bonus FROM game)
WHERE EXISTS (
    SELECT 1
    FROM triggered_events te
    WHERE te.fk_stock = roll.fk_stock
      AND te.move     = roll.move
      AND te.number   = roll.number
);


-- All the players who do not have an event card will select a random card from the pool
-- Update all players, but designed for only 1 player will draw a new card. Else all players
-- will draw the same card
-- The first select find the player without cards. The second select find the event to assign

/*UPDATE event
SET fk_player = (
    SELECT pk
    FROM player
    WHERE pk NOT IN (
        SELECT fk_player FROM event WHERE fk_player IS NOT NULL
    )
    LIMIT 1
)
WHERE pk = (
    SELECT pk
    FROM event
    WHERE fk_player IS NULL
      AND cooldown = 0
    ORDER BY RANDOM()
    LIMIT 1
);*/

-- UPDATE player
--SET fk_event = (
--    SELECT pk
--    FROM event
--    WHERE cooldown = 0
--    ORDER BY RANDOM()
--    LIMIT 1
--)
--WHERE fk_event IS NULL;

-- Reduce the cool down of all events if one even was triggered

/*UPDATE event
SET cooldown = cooldown - 1
WHERE (SELECT fk_event_triggered FROM game) IS NOT NULL
AND cooldown > 0;*/



-- Make all player cards unavailable even if already unavailable .

-- UPDATE event
-- SET available = 'N'
-- WHERE pk IN (SELECT fk_event FROM player WHERE fk_event IS NOT NULL );

-- When there are no more cards reshuffle the cards that are not in the player's hand.
-- Makes them available again

-- UPDATE event
-- SET available = 'Y'
-- WHERE NOT EXISTS (
--     SELECT 1 FROM event WHERE available = 'Y'
-- )
-- AND pk NOT IN (
--     SELECT fk_event FROM player WHERE fk_event IS NOT NULL
-- );



