-- Apply the game rules after the dice has been rolled at the end of the turn
-- @author Eric Pietrocupo
-- @date February 19th, 2026
-- @license GNU GPL 3.0
--
-- Since I am using more advance SQL feature, I asked chatGPT to give me some insight
-- of how to implement game mechanics using SQL queries. The randomness, the rolls, will
-- be determined in the C program and stored into the roll table. That allows reusing the
-- same rolls for multiple rules.




-- Replace previous price with current price before making any movements
UPDATE stock
SET previous_price = current_price;

-- Apply movement UP and DOWN to each stock
-- I am using SUM in case there is multiple rolls, can COALESCE to prevent nulls
-- So the negatives and positive are sum up together to apply the movement, the results
-- is the same than doing each roll one at a time.
-- The multiplication changes the sign of the answer to move up or down
UPDATE stock
SET current_price = current_price + COALESCE(
    (   SELECT SUM(move * number)
        FROM roll
        WHERE roll.fk_stock = stock.pk
    ), 0 );



-- (OPTIONAL) If dividend goes up variant is used, make DIV roll also move up
-- so I am adding 1 to the move value if rolls are 0 and make the stock modification again
-- The optional rule is applied with an EXIST clause because it returns true or false.
-- It is a safeguard for nulls and multirows, and it run slightly faster. Other variants should
-- be implemented this way.
UPDATE stock
SET current_price = current_price + COALESCE(
    (
        SELECT SUM((move + 1) * number)
        FROM roll
        WHERE roll.fk_stock = stock.pk
        AND roll.move = 0
    ), 0 )
WHERE EXISTS (
    SELECT 1
    FROM variant
    WHERE pk = 0
    AND isenabled = 'Y'
);



-- apply dice roll to dividend to pay to stock holder if >= 1$
-- Even if there is only 1 holding, sum is applied on holding too.
-- Makes every thing in 1 select even if only the roll table is multirow
-- It applies only to roll with move 0 and stock price >= 1$
UPDATE player
SET money = money + COALESCE(
    (
        SELECT SUM((holding.quantity * roll.number)/100)
        FROM holding
        JOIN roll ON roll.fk_stock = holding.fk_stock
        JOIN stock ON roll.fk_stock = stock.pk
        WHERE holding.fk_player = player.pk
          AND roll.move = 0 AND stock.current_price >= 100
    ), 0 );

-- Reset the stock status field to NO before assigning new values
UPDATE stock SET isdivpaid = 'N',
   crash = 'N',
   split = 'N';

-- Modify the isdivpaid field to identify which stock had dividend paid.
-- If at least 1 stock was
UPDATE stock SET isdivpaid = 'Y'
WHERE EXISTS( SELECT 1 FROM roll WHERE fk_stock = stock.pk
   AND move = 0 AND stock.current_price >= 100);



-- Double the holding of players where the stock value is >= 2$
UPDATE holding
SET quantity = quantity * 2
WHERE ( SELECT current_price FROM stock WHERE holding.fk_stock = stock.pk ) >= 200;

-- Remove the holdings (set to 0) where the stock value is <= 0$
UPDATE holding
SET quantity = 0
WHERE ( SELECT current_price FROM stock WHERE holding.fk_stock = stock.pk ) <=0;

-- reset value to 1$ and set split status
UPDATE stock
SET current_price = 100,
    split = 'Y'
WHERE current_price >=200;

-- reset value and set crash status
UPDATE stock
SET current_price = 100,
    crash = 'Y'
WHERE current_price <=0;


-- increment active player, or reset to the first player
-- The select case simulate a modulus operator
UPDATE game
SET fk_active_player = (
   SELECT CASE
        WHEN fk_active_player < (SELECT MAX(pk) FROM player)
        THEN fk_active_player + 1
        ELSE 1
   END
);

-- Increment game turn when it returns back to player 1 to play
UPDATE game
SET turn_number = turn_number + 1
WHERE fk_active_player = 1;


-- NOTE: The rolls are cleared from the table just before rolling






