-- MySQL Script generated by MySQL Workbench
-- 02/07/20 15:03:39
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema game-reviews
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `videogames`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `videogames` ;

CREATE TABLE IF NOT EXISTS `videogames` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `editor` VARCHAR(64) NOT NULL,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `platforms`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `platforms` ;

CREATE TABLE IF NOT EXISTS `platforms` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `manufacturer` VARCHAR(64) NOT NULL,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `reviews`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `reviews` ;

CREATE TABLE IF NOT EXISTS `reviews` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(128) NOT NULL,
  `text` TEXT NULL,
  `author` VARCHAR(64) NULL,
  `publication_date` TIMESTAMP NOT NULL,
  `display_note` TINYINT NOT NULL DEFAULT 0,
  `gameplay_note` TINYINT NOT NULL DEFAULT 0,
  `scenario_note` TINYINT NOT NULL DEFAULT 0,
  `lifetime_note` TINYINT NOT NULL DEFAULT 0,
  `videogame_id` INT UNSIGNED NOT NULL,
  `platform_id` INT UNSIGNED NOT NULL,
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_review_videogame_idx` (`videogame_id` ASC),
  INDEX `fk_review_platform1_idx` (`platform_id` ASC))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `videogames`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `videogames` (`id`, `name`, `editor`, `status`, `created_at`, `updated_at`) VALUES (1, 'Theme Hospital', 'Bullfrog', 1, DEFAULT, NULL);
INSERT INTO `videogames` (`id`, `name`, `editor`, `status`, `created_at`, `updated_at`) VALUES (2, 'NBA Jam', 'Midway', 1, DEFAULT, NULL);
INSERT INTO `videogames` (`id`, `name`, `editor`, `status`, `created_at`, `updated_at`) VALUES (3, 'Day of Tentacle', 'LucasArts', 1, DEFAULT, NULL);
INSERT INTO `videogames` (`id`, `name`, `editor`, `status`, `created_at`, `updated_at`) VALUES (4, 'Castlevania', 'Konami', 1, DEFAULT, NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `platforms`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `platforms` (`id`, `name`, `manufacturer`, `status`, `created_at`, `updated_at`) VALUES (1, 'PC', '-', 1, DEFAULT, NULL);
INSERT INTO `platforms` (`id`, `name`, `manufacturer`, `status`, `created_at`, `updated_at`) VALUES (2, 'MegaDrive', 'Sega', 1, DEFAULT, NULL);
INSERT INTO `platforms` (`id`, `name`, `manufacturer`, `status`, `created_at`, `updated_at`) VALUES (3, 'SNES', 'Nintendo', 1, DEFAULT, NULL);
INSERT INTO `platforms` (`id`, `name`, `manufacturer`, `status`, `created_at`, `updated_at`) VALUES (4, 'Playstation', 'Sony', 1, DEFAULT, NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `reviews`
-- -----------------------------------------------------
START TRANSACTION;
INSERT INTO `reviews` (`id`, `title`, `text`, `author`, `publication_date`, `display_note`, `gameplay_note`, `scenario_note`, `lifetime_note`, `videogame_id`, `platform_id`, `status`, `created_at`, `updated_at`) VALUES (1, 'Attention aux tentacules !', 'Fun et fou, ce jeu d\'aventure et de science-fiction loufoque est vraiment immersif et bien scénarisé.', 'Pierre', '2020-02-06', 2, 7, 10, 8, 3, 1, 1, DEFAULT, NULL);
INSERT INTO `reviews` (`id`, `title`, `text`, `author`, `publication_date`, `display_note`, `gameplay_note`, `scenario_note`, `lifetime_note`, `videogame_id`, `platform_id`, `status`, `created_at`, `updated_at`) VALUES (2, 'Du basket spectaculaire', 'Qui n\'a jamais rêvé de dunker après avoir touché le plafond de la salle ? Ou de faire un 2880° avant de dunker ? Et bien NBA Jam l\'a fait ! Des matchs 2 contre 2 intenses et spectaculaires vous permettront de tenir une balle en feu et de cramer les filets du panier.', 'Maxime', '2019-12-14', 3, 7, 5, 7, 2, 2, 1, DEFAULT, NULL);
INSERT INTO `reviews` (`id`, `title`, `text`, `author`, `publication_date`, `display_note`, `gameplay_note`, `scenario_note`, `lifetime_note`, `videogame_id`, `platform_id`, `status`, `created_at`, `updated_at`) VALUES (3, 'Quand la NBA nous fait kiffer', 'Incarner des gloires du basket NBA des années et placer dunk sur dunk, c\'est le kiff intégral ! D\'ailleurs, le public dans la salle ne s\'y trompe pas, et à chaque action spectaculaire, c\'est une cinquantaine de flashs qui crépitent.', 'Claire', '2019-11-12', 4, 8, 5, 7, 2, 2, 1, DEFAULT, NULL);

COMMIT;

