CREATE DATABASE  progra252jpgrupo2_lilybd;
USE  progra252jpgrupo2_lilybd;

CREATE TABLE levels(
    level_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY(level_id)
);

CREATE TABLE ranks(
    rank_id INT NOT NULL AUTO_INCREMENT,
    letter VARCHAR(2) NOT NULL,
    PRIMARY KEY(rank_id)
);

CREATE TABLE enemies(
    enemy_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY(enemy_id)
);

CREATE TABLE games(
  game_id INT NOT NULL AUTO_INCREMENT,
  score INT NOT NULL,
  player_id INT NOT NULL,
  last_update TIMESTAMP NOT NULL,
  round INT NOT NULL,
  level_id INT NOT NULL,
  rank_id INT NOT NULL,
  final_round INT NOT NULL,
  PRIMARY KEY(game_id),
  CONSTRAINT fk_games_players
  FOREIGN KEY (player_id) 
  REFERENCES players(player_id),
  CONSTRAINT fk_games_levels
  FOREIGN KEY (level_id)
  REFERENCES levels(level_id),
  CONSTRAINT fk_games_ranks
  FOREIGN KEY(rank_id)
  REFERENCES ranks(rank_id)
);

CREATE TABLE enemiesstats(
    enemstat_id INT NOT NULL AUTO_INCREMENT,
    kills INT NOT NULL,
    game_id INT NOT NULL,
    enemy_id INT NOT NULL,
    PRIMARY KEY(enemstat_id),
    CONSTRAINT enemiesstats_games
    FOREIGN KEY (game_id) 
    REFERENCES games(game_id),
    CONSTRAINT enemiesstats_enemies
    FOREIGN KEY (enemy_id) 
    REFERENCES enemies(enemy_id)
);

INSERT INTO levels(level_id, name)
VALUES(NULL, "Círculo de la herejía");
INSERT INTO levels(level_id, name)
VALUES(NULL, "Círculo de la traición");

INSERT INTO ranks(rank_id, letter)
VALUES(NULL, "D");
INSERT INTO ranks(rank_id, letter)
VALUES(NULL, "C");
INSERT INTO ranks(rank_id, letter)
VALUES(NULL, "B");
INSERT INTO ranks(rank_id, letter)
VALUES(NULL, "A");
INSERT INTO ranks(rank_id, letter)
VALUES(NULL, "S");

INSERT INTO enemies(enemy_id, name)
VALUES(NULL, "Golem de fuego");
INSERT INTO enemies(enemy_id, name)
VALUES(NULL, "Fuego");
INSERT INTO enemies(enemy_id, name)
VALUES(NULL, "Mortero");
INSERT INTO enemies(enemy_id, name)
VALUES(NULL, "Golem de hielo");
INSERT INTO enemies(enemy_id, name)
VALUES(NULL, "Cubo");
INSERT INTO enemies(enemy_id, name)
VALUES(NULL, "Copo de nieve");

INSERT INTO games(game_id, score, player_id, last_update, round, level_id, rank_id, final_round)
VALUES(NULL, 100000, 1, NOW(), 5, 1, 5, 5);
INSERT INTO games(game_id, score, player_id, last_update, round, level_id, rank_id, final_round)
VALUES(NULL, 346,    2, NOW(), 2, 1, 1, 2);
INSERT INTO games(game_id, score, player_id, last_update, round, level_id, rank_id, final_round)
VALUES(NULL, 2579,   3, NOW(), 4, 2, 4, 4);

INSERT INTO enemiesstats(enemstat_id, kills, game_id, enemy_id)
VALUES(NULL, 3, 1, 3);
INSERT INTO enemiesstats(enemstat_id, kills, game_id, enemy_id)
VALUES(NULL, 6, 2, 2);
INSERT INTO enemiesstats(enemstat_id, kills, game_id, enemy_id)
VALUES(NULL, 7, 3, 4);
