CREATE DATABASE IF NOT EXISTS bcnsgds_dev COMMENT = "bcns-gameDistributionSystem";

USE bcnsgds_dev;

CREATE TABLE IF NOT EXISTS game (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	uuid UUID UNIQUE, 
	title NVARCHAR(200) NOT NULL,
	originalTitle NVARCHAR(200) NULL,
	plot MEDIUMTEXT CHARACTER SET utf8 NULL,
	linuxInstruction MEDIUMTEXT CHARACTER SET utf8 NULL,
	systemRequirements MEDIUMTEXT NULL,
	recommendedSpecs MEDIUMTEXT NULL,
	year TINYINT UNSIGNED NULL) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS cdkey (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	cdkey NVARCHAR(50) NOT NULL,
	description NVARCHAR(150) NULL,
	CONSTRAINT `fk_game_cdkey`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS collaborator (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL,
	description NVARCHAR(150) NULL) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS gameCollaborator (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	collaboratorId TINYINT UNSIGNED NOT NULL,
	gameId SMALLINT UNSIGNED NOT NULL,
	datetime DATETIME NULL,
	CONSTRAINT `fk_game_gameCollaborator`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_collaborator_gameCollaborator`
		FOREIGN KEY (collaboratorId) REFERENCES collaborator (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS company (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(150) NOT NULL,
	country NVARCHAR(100) NULL) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS gameCompanyRelation (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS gameCompany (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	companyId SMALLINT UNSIGNED NOT NULL,
	relationId TINYINT UNSIGNED NOT NULL,
	CONSTRAINT `fk_game_gameCompany`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_company_gameCompany`
		FOREIGN KEY (companyId) REFERENCES company (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_gameCompanyRelation_gameCompany`
		FOREIGN KEY (relationId) REFERENCES gameCompanyRelation (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS ratingOrgan (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(100) NOT NULL,
	region NVARCHAR(100) NULL,
	description TEXT NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS rating (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	ratingOrganId TINYINT UNSIGNED NOT NULL,
	rating NVARCHAR(20) NOT NULL,
	CONSTRAINT `fk_game_rating`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_rating_ratingOrgan`
		FOREIGN KEY (ratingOrganId) REFERENCES ratingOrgan (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS mediumType (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(20) UNIQUE NOT NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS medium (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	name NVARCHAR(100) NULL,
	type TINYINT UNSIGNED NOT NULL,
	location NVARCHAR(250) NOT NULL,
	filename NVARCHAR(100) NOT NULL,
	CONSTRAINT `fk_game_medium`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS additionalFileType (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(100) UNIQUE NOT NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS additionalFile (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	name NVARCHAR(100) NULL,
	typeId TINYINT UNSIGNED NOT NULL,
	location NVARCHAR(250) NOT NULL,
	filename NVARCHAR(100) NOT NULL,
	language NVARCHAR(20) NULL,
	CONSTRAINT `fk_game_additionalFile`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_additionalFile_additionalFileType`
		FOREIGN KEY (typeId) REFERENCES additionalFileType (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;
    
CREATE TABLE IF NOT EXISTS artwork (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	name NVARCHAR(50) NULL,
	type NVARCHAR(50) NOT NULL,
	region NVARCHAR(100) NULL,
	location NVARCHAR(250) NOT NULL,
	filename NVARCHAR(100) NOT NULL,
	CONSTRAINT `fk_game_artwork`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS genre (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS gameGenre (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	genreId TINYINT UNSIGNED NOT NULL,
	CONSTRAINT `fk_game_gameGenre`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_gameGenre_genre`
		FOREIGN KEY (genreId) REFERENCES genre (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS playerMode (
	id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name NVARCHAR(50) UNIQUE NOT NULL,
	description NVARCHAR(250) NULL ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS gamePlayerMode (
	id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	gameId SMALLINT UNSIGNED NOT NULL,
	playerModeId TINYINT UNSIGNED NOT NULL,
	CONSTRAINT `fk_game_gamePlayerMode`
		FOREIGN KEY (gameId) REFERENCES game (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT,
	CONSTRAINT `fk_gamePlayerMode_playerMode`
		FOREIGN KEY (playerModeId) REFERENCES playerMode (id)
		ON DELETE CASCADE
		ON UPDATE RESTRICT
	) ENGINE = InnoDB;