DROP DATABASE dungeon;
DROP USER 'normal'@'localhost';

CREATE DATABASE dungeon;
USE dungeon;


/*seq*/
CREATE TABLE seq (
  name VARCHAR(32),
  id INTEGER,
  PRIMARY KEY (name)
);

/*stat*/
INSERT INTO seq VALUES ('stat',0);
CREATE TABLE stat (
  id INTEGER,
  PRIMARY KEY(id),
  melee smallint,
  magic smallint,
  thievery smallint
);

/*item can be a weapon, an armor or a tool*/
CREATE TABLE item (
  id INTEGER,
  name VARCHAR(32),
  PRIMARY KEY(id),
  FOREIGN KEY(id) REFERENCES stat(id)
);

CREATE TABLE weaponItem (
  id INTEGER,
  FOREIGN KEY(id) REFERENCES item(id),
  PRIMARY KEY(id)
);

CREATE TABLE armorItem (
  id INTEGER,
  FOREIGN KEY(id) REFERENCES item(id),
  PRIMARY KEY(id)
);

CREATE TABLE toolItem (
  id INTEGER,
  FOREIGN KEY(id) REFERENCES item(id),
  PRIMARY KEY(id)
);

CREATE VIEW weapon AS (
  SELECT item.id AS id, 
         item.name AS name, 
         stat.melee AS melee, 
         stat.magic AS magic, 
         stat.thievery AS thievery 
  FROM item, stat, weaponItem 
  WHERE item.id=weaponItem.id AND item.id=stat.id);

CREATE VIEW armor AS (
  SELECT item.id AS id, 
         item.name AS name, 
         stat.melee AS melee, 
         stat.magic AS magic, 
         stat.thievery AS thievery 
  FROM item, stat, armorItem
  WHERE item.id=armorItem.id AND item.id=stat.id);

CREATE VIEW tool AS (
  SELECT item.id AS id, 
         item.name AS name, 
         stat.melee AS melee, 
         stat.magic AS magic, 
         stat.thievery AS thievery  
  FROM item, stat, toolItem
  WHERE item.id=toolItem.id AND item.id=stat.id);

DELIMITER //
CREATE PROCEDURE newWeapon (IN nam VARCHAR(32), IN mel INTEGER, IN mag INTEGER, IN thi INTEGER)
BEGIN
    INSERT INTO stat (id, melee, magic, thievery) VALUES ((SELECT id FROM seq WHERE name='stat'), mel, mag, thi);
    INSERT INTO item (id, name) VALUES ((SELECT id FROM seq WHERE name='stat'), nam);
    INSERT INTO weaponItem (id) VALUES ((SELECT id FROM seq WHERE name='stat'));
    UPDATE seq SET id=id+1 WHERE name='stat';
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE newArmor (IN nam VARCHAR(32), IN mel INTEGER, IN mag INTEGER, IN thi INTEGER)
BEGIN
    INSERT INTO stat (id, melee, magic, thievery) VALUES ((SELECT id FROM seq WHERE name='stat'), mel, mag, thi);
    INSERT INTO item (id, name) VALUES ((SELECT id FROM seq WHERE name='stat'), nam);
    INSERT INTO armorItem (id) VALUES ((SELECT id FROM seq WHERE name='stat'));
    UPDATE seq SET id=id+1 WHERE name='stat';
END//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE newTool (IN nam VARCHAR(32), IN mel INTEGER, IN mag INTEGER, IN thi INTEGER)
BEGIN
    INSERT INTO stat (id, melee, magic, thievery) VALUES ((SELECT id FROM seq WHERE name='stat'), mel, mag, thi);
    INSERT INTO item (id, name) VALUES ((SELECT id FROM seq WHERE name='item'), nam);
    INSERT INTO toolItem (id) VALUES ((SELECT id FROM seq WHERE name='stat'));
    UPDATE seq SET id=id+1 WHERE name='stat';
END//
DELIMITER ;

GRANT SELECT ON dungeon.weapon TO 'normal';
GRANT SELECT ON dungeon.armor TO 'normal';
GRANT SELECT ON dungeon.tool TO 'normal';
GRANT EXECUTE ON PROCEDURE dungeon.newWeapon TO 'normal';
GRANT EXECUTE ON PROCEDURE dungeon.newArmor TO 'normal';
GRANT EXECUTE ON PROCEDURE dungeon.newTool TO 'normal';

/*player*/
CREATE TABLE playerTbl (
  id INTEGER NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (id) REFERENCES stat(id),
  name VARCHAR(32),
  hp INTEGER,
  xp INTEGER,
  gold INTEGER
);

CREATE VIEW player AS (
  SELECT playerTbl.id AS id, 
         playerTbl.name AS name, 
         stat.melee AS melee, 
         stat.magic AS magic, 
         stat.thievery AS thievery, 
         playerTbl.hp AS hp, 
         playerTbl.xp AS xp, 
         playerTbl.gold AS gold
  FROM playerTbl, stat
  WHERE playerTbl.id=stat.id);

DELIMITER //
CREATE PROCEDURE newPlayer (IN nam VARCHAR(32), IN mel INTEGER, IN mag INTEGER, IN thi INTEGER)
BEGIN
    INSERT INTO stat (id, melee, magic, thievery) VALUES ((SELECT id FROM seq WHERE name='stat'), mel, mag, thi);
    INSERT INTO playerTbl (id, name, hp, xp, gold, roomID) VALUES ((SELECT id FROM seq WHERE name='stat'), nam, 100, 0, 0);
    UPDATE seq SET id=id+1 WHERE name='stat';
END//
DELIMITER ;

GRANT EXECUTE ON PROCEDURE dungeon.newPlayer TO 'normal';
GRANT SELECT ON dungeon.player TO 'normal';


