/*
 Navicat Premium Data Transfer

 Source Server         : test
 Source Server Type    : MySQL
 Source Server Version : 80040
 Source Host           : localhost:3306
 Source Schema         : games

 Target Server Type    : MySQL
 Target Server Version : 80040
 File Encoding         : 65001

 Date: 28/11/2024 09:55:54
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for communities
-- ----------------------------
DROP TABLE IF EXISTS `communities`;
CREATE TABLE `communities`  (
  `CommunityID` int(0) NOT NULL AUTO_INCREMENT,
  `CommunityName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL,
  `CreationDate` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`CommunityID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for gamedetails
-- ----------------------------
DROP TABLE IF EXISTS `gamedetails`;
CREATE TABLE `gamedetails`  (
  `GameDetailsID` int(0) NOT NULL AUTO_INCREMENT,
  `GameID` int(0) NOT NULL,
  `Developer` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Description` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`GameDetailsID`) USING BTREE,
  INDEX `fk_game_id`(`GameID`) USING BTREE,
  CONSTRAINT `fk_game_id` FOREIGN KEY (`GameID`) REFERENCES `games` (`GameID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `gamedetails_ibfk_1` FOREIGN KEY (`GameID`) REFERENCES `games` (`GameID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for games
-- ----------------------------
DROP TABLE IF EXISTS `games`;
CREATE TABLE `games`  (
  `GameID` int(0) NOT NULL AUTO_INCREMENT,
  `GameName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `Publisher` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Platform` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Genre` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Rating` float NULL DEFAULT NULL,
  PRIMARY KEY (`GameID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for player_community
-- ----------------------------
DROP TABLE IF EXISTS `player_community`;
CREATE TABLE `player_community`  (
  `PlayerCommunityID` int(0) NOT NULL AUTO_INCREMENT,
  `PlayerID` int(0) NOT NULL,
  `CommunityID` int(0) NOT NULL,
  PRIMARY KEY (`PlayerCommunityID`) USING BTREE,
  INDEX `PlayerID`(`PlayerID`) USING BTREE,
  INDEX `CommunityID`(`CommunityID`) USING BTREE,
  CONSTRAINT `player_community_ibfk_1` FOREIGN KEY (`PlayerID`) REFERENCES `users` (`UserID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `player_community_ibfk_2` FOREIGN KEY (`CommunityID`) REFERENCES `communities` (`CommunityID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for ratings
-- ----------------------------
DROP TABLE IF EXISTS `ratings`;
CREATE TABLE `ratings`  (
  `RatingID` int(0) NOT NULL AUTO_INCREMENT,
  `UserID` int(0) NOT NULL,
  `GameID` int(0) NOT NULL,
  `Score` float NULL DEFAULT NULL,
  `Comment` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`RatingID`) USING BTREE,
  INDEX `UserID`(`UserID`) USING BTREE,
  INDEX `GameID`(`GameID`) USING BTREE,
  CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `ratings_ibfk_2` FOREIGN KEY (`GameID`) REFERENCES `games` (`GameID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 80 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `UserID` int(0) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Password` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `Email` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`UserID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Triggers structure for table ratings
-- ----------------------------
DROP TRIGGER IF EXISTS `update_game_rating_after_insert`;
delimiter ;;
CREATE TRIGGER `update_game_rating_after_insert` AFTER INSERT ON `ratings` FOR EACH ROW BEGIN
    UPDATE Games
    SET Rating = (
        SELECT AVG(Score) FROM Ratings WHERE GameID = NEW.GameID
    )
    WHERE GameID = NEW.GameID;
END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table ratings
-- ----------------------------
DROP TRIGGER IF EXISTS `update_game_rating_after_update`;
delimiter ;;
CREATE TRIGGER `update_game_rating_after_update` AFTER UPDATE ON `ratings` FOR EACH ROW BEGIN
    UPDATE Games
    SET Rating = (
        SELECT AVG(Score) FROM Ratings WHERE GameID = NEW.GameID
    )
    WHERE GameID = NEW.GameID;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;