5DgWX723

1--table
2CREATE TABLE `match` (
3  `id` int unsigned NOT NULL AUTO_INCREMENT,
4  `homeId` int DEFAULT NULL,
5  `awayId` int DEFAULT NULL,
6  `date` date NOT NULL,
7  `time` time(3) NOT NULL,
8  `winner` int DEFAULT NULL,
9  `tournamentId` int DEFAULT NULL,
10  `created_by` int DEFAULT NULL,
11  `updated_by` int DEFAULT NULL,
12  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
13  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
14  PRIMARY KEY (`id`)
15) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
16
17--procedure
18CREATE DEFINER=`root`@`localhost` PROCEDURE `match_procedure`(IN winner INT, IN homeId INT, IN awayId INT)
19BEGIN
20	IF (homeId = awayId) THEN
21		SIGNAL SQLSTATE '45000'
22			SET MESSAGE_TEXT = 'Invalid team id\'s';
23    END IF;
24    IF (winner != null) THEN
25		IF ((winner != homeId) AND (winner != awayId)) THEN
26		SIGNAL SQLSTATE '45000'
27			SET MESSAGE_TEXT = 'Winner id\'s not valid';
28		END IF;
29    END IF;
30END
31
32--trigger before insert
33CREATE DEFINER=`root`@`localhost` TRIGGER `match_BEFORE_INSERT` BEFORE INSERT ON `match` FOR EACH ROW BEGIN
34	CALL match_procedure(NEW.winner, NEW.homeId, NEW.awayId);
35END
36
37--trigger after insert
38CREATE DEFINER=`root`@`localhost` TRIGGER `match_AFTER_INSERT` AFTER INSERT ON `match` FOR EACH ROW BEGIN
39	DECLARE _leaderboardId INT;
40    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);
41
42	IF NOT EXISTS (SELECT * FROM `strapi-league`.`teamleaderboard` t where (t.teamId = NEW.homeId) AND (t.leaderboardId = _leaderboardId)) THEN
43		CALL teamleaderboard_procedure(NEW.homeId, NEW.tournamentId);
44    END IF;
45	IF NOT EXISTS (SELECT * FROM `strapi-league`.`teamleaderboard` t where (t.teamId = NEW.awayId) AND (t.leaderboardId = _leaderboardId)) THEN
46		CALL teamleaderboard_procedure(NEW.awayId, NEW.tournamentId);
47    END IF;
48    
49    IF (NEW.winner IS NOT NULL AND NEW.winner = NEW.homeId) THEN
50		UPDATE `strapi-league`.`teamleaderboard` SET wins = wins + 1 WHERE (teamId = NEW.homeId AND leaderboardId = _leaderboardId);
51        UPDATE `strapi-league`.`teamleaderboard` SET losses = losses + 1 WHERE (teamId = NEW.awayId AND leaderboardId = _leaderboardId);
52	END IF;
53    
54    IF (NEW.winner IS NOT NULL AND NEW.winner = NEW.awayId) THEN
55		UPDATE `strapi-league`.`teamleaderboard` SET wins = wins + 1 WHERE (teamId = NEW.awayId AND leaderboardId = _leaderboardId);
56        UPDATE `strapi-league`.`teamleaderboard` SET losses = losses + 1 WHERE (teamId = NEW.homeId AND leaderboardId = _leaderboardId);
57	END IF;
58END
59
60
61