part4_procedures_triggers.sql

-- ---------------------- SUMMARY ---------------------------------------------------
-- 2 procedures 
--  GetTeamSummary(2);               -- returns summary for team India
--  GetTopScoringInnings('T20', 3);  -- returns top 3 T20 innings
--
-- 2 triggers  
--  after_wins_insert           -> creates and stores a win summary
--  before_participates_insert  -> makes sure no more than 2 teams for each match
-- ----------------------------------------------------------------------------------


-- STORED PROCEDURE 1: GetTeamSummary
-- Takes a team_id, returns matches played, wins, losses, draws

-- Example usage: CALL GetTeamSummary(2);  -- returns summary for India
DELIMITER $$

CREATE PROCEDURE GetTeamSummary(IN p_team_id INT)
BEGIN
    -- variables to hold count 
    DECLARE v_team_name     VARCHAR(100);
    DECLARE v_played        INT DEFAULT 0;
    DECLARE v_wins          INT DEFAULT 0;
    DECLARE v_losses        INT DEFAULT 0;
    DECLARE v_draws         INT DEFAULT 0;

    -- display team 
    SELECT team_name INTO v_team_name
    FROM Team
    WHERE team_id = p_team_id;

    -- if team doesnt exist, then dont do anything else
    IF v_team_name IS NULL THEN
        SELECT 'Error: Team not found' AS message;
    ELSE
        -- total matches the team participated in
        SELECT COUNT(*) INTO v_played
        FROM Participates_In
        WHERE team_id = p_team_id;

        -- matches the team won
        SELECT COUNT(*) INTO v_wins
        FROM Wins
        WHERE team_id = p_team_id;

        -- draws -> matches team played in but no result in Wins table
        -- A draw means no team won, so match_id not in Wins at all
        SELECT COUNT(*) INTO v_draws
        FROM Participates_In pi
        WHERE pi.team_id = p_team_id
          AND pi.match_id NOT IN (
              SELECT match_id FROM Wins
          );

        -- losses -> totalMatches - total_wins - total_draws 
        SET v_losses = v_played - v_wins - v_draws;

        -- display summary 
        SELECT
            v_team_name                 AS team_name,
            v_played                    AS matches_played,
            v_wins                      AS wins,
            v_losses                    AS losses,
            v_draws                     AS draws,
            ROUND((v_wins / v_played) * 100, 2) AS win_percentage;
    END IF;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------------------------------------------------------------------

-- STORED PROCEDURE 2: GetTopScoringInnings
-- Takes a format (T20/ODI/Test) and a number N,
-- returns the top N highest scoring innings for that format

-- Example usage:
-- CALL GetTopScoringInnings('ODI', 5);  -- top 5 ODI innings
-- CALL GetTopScoringInnings('T20', 3);  -- top 3 T20 innings

DELIMITER $$

CREATE PROCEDURE GetTopScoringInnings(
    IN  p_format    VARCHAR(10),
    IN  p_n         INT
)
BEGIN
    -- Validate format input using IF-ELSE
    IF p_format NOT IN ('T20', 'ODI', 'Test') THEN
        SELECT 'Error: format must be T20, ODI or Test' AS message;

    ELSEIF p_n <= 0 THEN
        SELECT 'Error: N must be a positive integer' AS message;


    ELSE
        SELECT
            tr.name            AS tournament,
            m.match_date       AS match_date,
            t.team_name        AS team_name,
            i.total_runs       AS total_runs,
            i.total_wickets    AS total_wickets,
            i.total_overs      AS total_overs,
            i.total_sixes      AS total_sixes
        FROM Innings i
        JOIN `Match`    m  ON i.match_id      = m.match_id
        JOIN Tournament tr ON m.tournament_id = tr.tournament_id
        JOIN Team       t  ON i.team_id       = t.team_id
        WHERE tr.format = p_format
        ORDER BY i.total_runs DESC   -- enables ranking
        LIMIT p_n;                   -- prints only the top specified rank

    END IF;
END$$

DELIMITER ;


-- --------------------------------------------------------------------------------------------------------------------------------------

-- TRIGGER 1: AFTER INSERT on Wins
-- Automatically updates result_summary in Match to reflect winner

-- Example: inserting a new win will auto-update Match
-- INSERT INTO Wins VALUES (11, 3, 4, 'Wickets');
-- Then check: SELECT result_summary FROM `Match` WHERE match_id = 11;
-- Should now read: 'England won by 4 Wickets'

DELIMITER $$

CREATE TRIGGER after_wins_insert
AFTER INSERT ON Wins
FOR EACH ROW
BEGIN
    -- Declare a variable to hold the winning team name
    DECLARE v_winner_name   VARCHAR(100);
    DECLARE v_summary       VARCHAR(255);

    -- Look up the winning team name using the inserted team_id
    SELECT team_name INTO v_winner_name
    FROM Team
    WHERE team_id = NEW.team_id;

    -- Build the result summary string
    SET v_summary = CONCAT(
        v_winner_name, ' won by ',
        NEW.win_margin, ' ',
        NEW.win_type
    );

    -- Update the result_summary in the Match table
    UPDATE `Match`
    SET result_summary = v_summary
    WHERE match_id = NEW.match_id;

END$$

DELIMITER ;

-- --------------------------------------------------------------------------------------------------------------------------------------

-- TRIGGER 2: BEFORE INSERT on Participates_In
-- Prevents more than 2 teams being added to the same match
-- Example: this should fail since match 1 already has 2 teams
-- INSERT INTO Participates_In VALUES (3, 1, 'Neutral');
-- Should return: Error: A match cannot have more than 2 teams

DELIMITER $$

CREATE TRIGGER before_participates_insert
BEFORE INSERT ON Participates_In
FOR EACH ROW
BEGIN
    -- Variable to hold current team count for this match
    DECLARE v_team_count INT DEFAULT 0;

    -- Count how many teams are already registered for this match
    SELECT COUNT(*) INTO v_team_count
    FROM Participates_In
    WHERE match_id = NEW.match_id;

    -- If 2 teams already exist, block the insert with an error
    IF v_team_count >= 2 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Error: A match cannot have more than 2 teams';
    END IF;

END$$

DELIMITER ;