DROP TABLE IF EXISTS `containsTags`;
DROP TABLE IF EXISTS `tags`;
DROP TABLE IF EXISTS `containsBadges`;
DROP TABLE IF EXISTS `badges`;
DROP TABLE IF EXISTS `containsGroups`;
DROP TABLE IF EXISTS `nextPart`;
DROP TABLE IF EXISTS `groups`;
DROP TABLE IF EXISTS `completions`;
DROP TABLE IF EXISTS `players`;
DROP TABLE IF EXISTS `puzzles`;
DROP TABLE IF EXISTS `chapters`;

CREATE TABLE `players` (
  `id_player` int(11) NOT NULL AUTO_INCREMENT,
  `pseudo` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `passwd` varchar(150) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `avatar` blob DEFAULT NULL,
  PRIMARY KEY (`id_player`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE badges (
  id_badge int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  logo mediumblob DEFAULT NULL,
  level int(11) DEFAULT 1,
  PRIMARY KEY (id_badge)
);

CREATE TABLE `chapters` (
  `id_chapter` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id_chapter`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `puzzles` (
  `id_puzzle` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `content` text NOT NULL,
  `soluce` blob NOT NULL,
  `verify` text DEFAULT NULL,
  `score_max` int(11) NOT NULL,
  `fk_chapter` int(11) NOT NULL,
  PRIMARY KEY (`id_puzzle`),
  KEY `fk_chapter` (`fk_chapter`),
  CONSTRAINT `puzzles_ibfk_1` FOREIGN KEY (`fk_chapter`) REFERENCES `chapters` (`id_chapter`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `groups` (
  `id_group` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  `fk_chapter` int(11) DEFAULT NULL,
  `fk_puzzle` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_group`),
  KEY `fk_chapter` (`fk_chapter`),
  KEY `fk_puzzle` (`fk_puzzle`),
  CONSTRAINT `groups_ibfk_1` FOREIGN KEY (`fk_chapter`) REFERENCES `chapters` (`id_chapter`),
  CONSTRAINT `groups_ibfk_2` FOREIGN KEY (`fk_puzzle`) REFERENCES `puzzles` (`id_puzzle`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `nextPart` (
  `origin` int(11) NOT NULL,
  `next` int(11) NOT NULL,
  PRIMARY KEY (`origin`,`next`),
  KEY `next` (`next`),
  CONSTRAINT `nextPart_ibfk_1` FOREIGN KEY (`origin`) REFERENCES `puzzles` (`id_puzzle`),
  CONSTRAINT `nextPart_ibfk_2` FOREIGN KEY (`next`) REFERENCES `puzzles` (`id_puzzle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `completions` (
  `id_completion` int(11) NOT NULL AUTO_INCREMENT,
  `fk_puzzle` int(11) NOT NULL,
  `fk_player` int(11) NOT NULL,
  `tries` int(11) DEFAULT 0,
  `code` blob DEFAULT NULL,
  `score` int(11) DEFAULT 0,
  `fileName` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id_completion`),
  KEY `fk_puzzle` (`fk_puzzle`),
  KEY `fk_player` (`fk_player`),
  CONSTRAINT `completions_ibfk_1` FOREIGN KEY (`fk_puzzle`) REFERENCES `puzzles` (`id_puzzle`),
  CONSTRAINT `completions_ibfk_2` FOREIGN KEY (`fk_player`) REFERENCES `players` (`id_player`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `tags` (
  `id_tag` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id_tag`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `containsBadges` (
  `fk_player` int(11) NOT NULL,
  `fk_badge` int(11) NOT NULL,
  PRIMARY KEY (`fk_player`,`fk_badge`),
  KEY `fk_badge` (`fk_badge`),
  CONSTRAINT `containsBadges_ibfk_1` FOREIGN KEY (`fk_player`) REFERENCES `players` (`id_player`),
  CONSTRAINT `containsBadges_ibfk_2` FOREIGN KEY (`fk_badge`) REFERENCES `badges` (`id_badge`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `containsGroups` (
  `fk_player` int(11) NOT NULL,
  `fk_group` int(11) NOT NULL,
  PRIMARY KEY (`fk_player`,`fk_group`),
  KEY `fk_group` (`fk_group`),
  CONSTRAINT `containsGroups_ibfk_1` FOREIGN KEY (`fk_player`) REFERENCES `players` (`id_player`),
  CONSTRAINT `containsGroups_ibfk_2` FOREIGN KEY (`fk_group`) REFERENCES `groups` (`id_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `containsTags` (
  `fk_tag` int(11) NOT NULL,
  `fk_puzzle` int(11) NOT NULL,
  PRIMARY KEY (`fk_tag`,`fk_puzzle`),
  KEY `fk_puzzle` (`fk_puzzle`),
  CONSTRAINT `containsTags_ibfk_1` FOREIGN KEY (`fk_tag`) REFERENCES `tags` (`id_tag`),
  CONSTRAINT `containsTags_ibfk_2` FOREIGN KEY (`fk_puzzle`) REFERENCES `puzzles` (`id_puzzle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO chapters (id_chapter, name) VALUES (1, 'chapter 1');
INSERT INTO players (pseudo, email, passwd,firstname,lastname) VALUES ('userTest', 'test@peerat.dev', '$argon2id$v=19$m=15360,t=2,p=1$$cAQwfs30Bf2rQGj86bpz7i59TlsuOFPiXeNpLlVu4AY', 'a','b')