CREATE DATABASE BUENOFIGHT;

USE BUENOFIGHT;

#		NAME TABLE

CREATE TABLE NAME (
	id_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(16)
);

#		PLAYER TABLE

CREATE TABLE PLAYER (
	id_player INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fighter_id INT REFERENCES FIGHTER(id_fighter) ON DELETE CASCADE,
    name_id VARCHAR(16) REFERENCES NAME(id_name) ON DELETE CASCADE,
    kills INT,
    game_id INT REFERENCES GAME(id_game) ON DELETE CASCADE,
    haswon BOOLEAN
);

#		CHARACTER TABLE

CREATE TABLE FIGHTER (
	id_fighter INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(22)
);

#		GAME TABLE

CREATE TABLE GAME (
    id_game INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    map_id VARCHAR(30) REFERENCES MAP(id_map) ON DELETE CASCADE,
    time_start DATETIME,
    time_end DATETIME
);

#		MAP TABLE

CREATE TABLE MAP (
	id_map INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
);

#		USER

CREATE USER gamer IDENTIFIED BY '123456';
GRANT ALL ON * to gamer@localhost IDENTIFIED BY '123456';

INSERT INTO MAP (name) VALUES ('Skyline');

INSERT INTO GAME (map_id) VALUES (1);

INSERT INTO GAME (map_id,time_start,time_end) VALUES (1,'2018:05:24-15:10:30','2018:05:24-16:12:00');
DELETE FROM Game WHERE 1 = 1;
SELECT * FROM GAME;
INSERT INTO PLAYER (fighter_id,name_id,kills,game_id,haswon) VALUES (2,1,2,1,true);
SELECT COUNT(*) FROM PLAYER WHERE fighter_id = (SELECT id_fighter FROM FIGHTER WHERE name = 'Applikationsentwickler');
SET SQL_SAFE_UPDATES = 0;
INSERT INTO PLAYER (fighter_id,name_id,kills,game_id,haswon) VALUES (1,1,1,1,false);
INSERT INTO PLAYER (fighter_id,name_id,kills,game_id,haswon) VALUES (3,1,1,1,false);

SELECT * FROM Map;
SELECT * FROM PLAYER;
SELECT COUNT(*) FROM PLAYER WHERE fighter_id = (SELECT id_fighter FROM FIGHTER WHERE name = 'Applikationsentwickler');

SELECT * FROM FIGHTER;

SELECT time_end FROM GAME;