--table
CREATE TABLE `match` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `homeId` int DEFAULT NULL,
  `awayId` int DEFAULT NULL,
  `date` date NOT NULL,
  `time` time(3) NOT NULL,
  `winner` int DEFAULT NULL,
  `tournamentId` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--procedure
CREATE DEFINER=`root`@`localhost` PROCEDURE `match_procedure`(IN winner INT, IN homeId INT, IN awayId INT)
BEGIN
    IF (homeId = awayId) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Invalid team id\'s';
    END IF;
    IF (winner != null) THEN
        IF ((winner != homeId) AND (winner != awayId)) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Winner id\'s not valid';
        END IF;
    END IF;
END

--trigger before insert
CREATE DEFINER=`root`@`localhost` TRIGGER `match_BEFORE_INSERT` BEFORE INSERT ON `match` FOR EACH ROW BEGIN
    CALL match_procedure(NEW.winner, NEW.homeId, NEW.awayId);
END

--trigger after insert
CREATE DEFINER=`root`@`localhost` TRIGGER `match_AFTER_INSERT` AFTER INSERT ON `match` FOR EACH ROW BEGIN
    DECLARE _leaderboardId INT;
    SET _leaderboardId = (SELECT lb.id from `strapi-league`.`leaderboard` lb join `strapi-league`.`tournament` t on lb.tournamentId = t.id WHERE t.id = new.tournamentId);

    IF NOT EXISTS (SELECT * FROM `strapi-league`.`teamleaderboard` t where (t.teamId = NEW.homeId) AND (t.leaderboardId = _leaderboardId)) THEN
        CALL teamleaderboard_procedure(NEW.homeId, NEW.tournamentId);
    END IF;
    IF NOT EXISTS (SELECT * FROM `strapi-league`.`teamleaderboard` t where (t.teamId = NEW.awayId) AND (t.leaderboardId = _leaderboardId)) THEN
        CALL teamleaderboard_procedure(NEW.awayId, NEW.tournamentId);
    END IF;

    IF (NEW.winner IS NOT NULL AND NEW.winner = NEW.homeId) THEN
        UPDATE `strapi-league`.`teamleaderboard` SET wins = wins + 1 WHERE (teamId = NEW.homeId AND leaderboardId = _leaderboardId);
        UPDATE `strapi-league`.`teamleaderboard` SET losses = losses + 1 WHERE (teamId = NEW.awayId AND leaderboardId = _leaderboardId);
    END IF;

    IF (NEW.winner IS NOT NULL AND NEW.winner = NEW.awayId) THEN
        UPDATE `strapi-league`.`teamleaderboard` SET wins = wins + 1 WHERE (teamId = NEW.awayId AND leaderboardId = _leaderboardId);
        UPDATE `strapi-league`.`teamleaderboard` SET losses = losses + 1 WHERE (teamId = NEW.homeId AND leaderboardId = _leaderboardId);
    END IF;
END