-- Add triggers to log modifications for easier debugging
-- @author Eric Pietrocupo
-- @date March 22nd, 2026
-- @license GNU GPL 3.0

-- UPDATE player money: Log when the player gains or pays money
CREATE TRIGGER log_player_money
AFTER UPDATE ON player
WHEN OLD.money != NEW.money
BEGIN
    -- Pay case
    INSERT INTO log (turn, fk_player, message)
    SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        (NEW.name || ' pays ' || (OLD.money - NEW.money) || '$'),
            '[ERROR] log_player_money pay case = NULL')
    FROM game g
    WHERE NEW.money < OLD.money;

    -- Gain case
    INSERT INTO log (turn, fk_player, message)
    SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        (NEW.name || ' gains ' || (NEW.money - OLD.money) || '$'),
        '[ERROR] log_player_money pay case = NULL')
    FROM game g
    WHERE NEW.money > OLD.money;
END;

-- INSERT player_event: Display the new drawn card.
CREATE TRIGGER log_player_event_draw
AFTER INSERT ON player_event
BEGIN
   INSERT INTO log (turn, fk_player, message)
   SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        ( (SELECT player.name FROM player WHERE NEW.fk_player = player.pk)
        || ' draws [' || NEW.fk_event || '] ' ||
        (SELECT event.name FROM event WHERE NEW.fk_event = event.pk) ),
        '[ERROR] log_player_event_draw = NULL')
   FROM game g;
END;


-- UPDATE player_event istriggered: Display information of the triggered event

CREATE TRIGGER log_player_event_triggered
AFTER UPDATE ON player_event
WHEN NEW.istriggered = 'Y'
AND OLD.istriggered = 'N'
BEGIN
   INSERT INTO log (turn, fk_player, message)
   SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        ( 'Event: ' ||
        (SELECT event.name FROM event WHERE NEW.fk_event = event.pk) ),
        '[ERROR] log_player_event_triggered = NULL')
   FROM game g;
END;

-- UPDATE stock current_price: Display price changes

CREATE TRIGGER log_stock_price
AFTER UPDATE ON stock
WHEN OLD.current_price != NEW.current_price
BEGIN
    -- Price reduce case
    INSERT INTO log (turn, fk_player, message)
    SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        (NEW.name || ' drops by 0.' || (OLD.current_price - NEW.current_price) || '$'
        || ' ( ' || (OLD.current_price /100.0) || '$ -> ' || (NEW.current_price /100.0) || '$ )' ),
        '[ERROR] log_stock_price reduce case = NULL')
    FROM game g
    WHERE NEW.current_price < OLD.current_price;

    -- Price increase case
    INSERT INTO log (turn, fk_player, message)
    SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        (NEW.name || ' raise by 0.' || (NEW.current_price - OLD.current_price) || '$'
        || ' ( ' || (OLD.current_price /100.0) || '$ -> ' || (NEW.current_price /100.0) || '$ )' ),
        '[ERROR] log_stock_price increase case = NULL')
    FROM game g
    WHERE NEW.current_price > OLD.current_price;
END;

-- UPDATE stock isdivpaid: Indicates a dividend in paid on a stock

CREATE TRIGGER log_stock_divpaid
AFTER UPDATE ON stock
WHEN NEW.isdivpaid = 'Y'
   AND OLD.isdivpaid = 'N'
BEGIN
   INSERT INTO log (turn, fk_player, message)
   SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        ( NEW.name || ' pays a dividend of ' ||
        (SELECT SUM(roll.number) FROM roll
        WHERE roll.fk_stock = NEW.pk AND roll.move=0) || '%'),
        '[ERROR] log_stock_divpaid = NULL')
   FROM game g;
END;

-- UPDATE stock split: Notify a stock split

CREATE TRIGGER log_stock_split
AFTER UPDATE ON stock
WHEN NEW.split = 'Y'
   AND OLD.split = 'N'
BEGIN
   INSERT INTO log (turn, fk_player, message)
   SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        ( NEW.name || ' splits' ),
        '[ERROR] log_stock_split = NULL')
   FROM game g;
END;

-- UPDATE stock crash: Notify a stock crash
CREATE TRIGGER log_stock_crash
AFTER UPDATE ON stock
WHEN NEW.crash = 'Y'
   AND OLD.crash = 'N'
BEGIN
   INSERT INTO log (turn, fk_player, message)
   SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        ( NEW.name || ' crashes' ),
        '[ERROR] log_stock_crash = NULL')
   FROM game g;
END;

-- UPDATE holding quantity: Old and new quantity

CREATE TRIGGER log_player_buysell_stock
AFTER UPDATE ON holding
WHEN OLD.quantity != NEW.quantity
BEGIN
    -- sell case
    INSERT INTO log (turn, fk_player, message)
    SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        (player.name || ' sells ' || (OLD.quantity - NEW.quantity ) || ' ' || stock.name ),
        '[ERROR] log_player_buysell_stock sell case = NULL')
    FROM game g
    JOIN player ON player.pk = NEW.fk_player
    JOIN stock ON stock.pk = NEW.fk_stock
    WHERE NEW.quantity < OLD.quantity;

    -- buy case
    INSERT INTO log (turn, fk_player, message)
    SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        (player.name || ' buys ' || (NEW.quantity - OLD.quantity ) || ' ' || stock.name ),
        '[ERROR] log_player_buysell_stock buy case = NULL')
    FROM game g
    JOIN player ON player.pk = NEW.fk_player
    JOIN stock ON stock.pk = NEW.fk_stock
    WHERE NEW.quantity > OLD.quantity;

END;


-- INSERT roll: display all rolled values

CREATE TRIGGER log_roll_dice
AFTER INSERT ON roll
BEGIN
   INSERT INTO log (turn, fk_player, message)
   SELECT
        g.turn_number,
        g.fk_active_player,
        COALESCE(
        ( 'Roll: ' || stock.code || ' ' ||
        CASE NEW.move WHEN -1 THEN 'DOWN' WHEN 0 THEN 'DIV' WHEN 1 THEN 'UP' END
        || ' ' || NEW.number ),
        '[ERROR] log_roll_dice = NULL')
   FROM game g
   JOIN stock ON NEW.fk_stock = stock.pk;
END;
