/*

*/

/*users

master is the game master, 
  he can create room and chest
  add padlock to it
  create weapon armor and tool
  create monster and add item to it

player is the model every player will be
  can

*/
GRANT USAGE ON *.* TO 'master'@'localhost';
DROP USER 'master'@'localhost';
GRANT USAGE ON *.* TO 'player'@'localhost';
DROP USER 'player'@'localhost';
GRANT USAGE ON *.* TO 'signin'@'localhost';
DROP USER 'signin'@'localhost';




/*database*/
DROP DATABASE IF EXISTS dungeon;
CREATE DATABASE dungeon;
USE dungeon;

/*=============>table*/

/*sequencer
only the auto increment
with the last inserted index facility
*/
CREATE TABLE seq (
  name VARCHAR(8) NOT NULL,
  PRIMARY KEY (name),
  id INTEGER UNSIGNED NOT NULL
);

DELIMITER ~~
CREATE TRIGGER newSeq 
BEFORE INSERT ON seq
FOR EACH ROW BEGIN
  SET NEW.id = rand() * 4294967295;
END ~~
DELIMITER ;

DELIMITER ~~
CREATE FUNCTION last(nameSeq VARCHAR(8))
RETURNS VARCHAR(32)
BEGIN
  RETURN (SELECT id FROM seq WHERE name=nameSeq);
END ~~
DELIMITER ;

/*room
does not contain anything beside descrition
all that the room contain is self linked to it*/
CREATE TABLE room (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  description TEXT NOT NULL
);

INSERT INTO seq (name) VALUES ('room');

DELIMITER ~~
CREATE TRIGGER newRoom
BEFORE INSERT ON room
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='room');
  UPDATE seq SET id=id+1 WHERE name='room';
END ~~
DELIMITER ;

GRANT INSERT (description) ON TABLE dungeon.room TO 'master';
GRANT SELECT ON TABLE dungeon.room TO 'player';

/*ITEM weapon*/
CREATE TABLE weapon (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  name VARCHAR(32) NOT NULL,
  damage TINYINT UNSIGNED NOT NULL,
  effect TINYINT UNSIGNED NOT NULL,
  speed TINYINT UNSIGNED NOT NULL
);

INSERT INTO seq (name) VALUES ('weapon');

DELIMITER ~~
CREATE TRIGGER newWeapon
BEFORE INSERT ON weapon
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='weapon');
  UPDATE seq SET id=id+1 WHERE name='weapon';
END ~~
DELIMITER ;

GRANT INSERT (name, damage, effect, speed) 
ON TABLE dungeon.weapon TO 'master';
GRANT SELECT ON TABLE dungeon.weapon to 'player';

/*ITEM armor*/
CREATE TABLE armor (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  name VARCHAR(32) NOT NULL,
  solidity TINYINT UNSIGNED NOT NULL,
  resistance TINYINT UNSIGNED NOT NULL,
  design TINYINT UNSIGNED NOT NULL
);

INSERT INTO seq (name) VALUES ('armor');

DELIMITER ~~
CREATE TRIGGER newArmor
BEFORE INSERT ON armor
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='armor');
  UPDATE seq SET id=id+1 WHERE name='armor';
END ~~
DELIMITER ;

GRANT INSERT (name, solidity, resistance, design) 
ON TABLE dungeon.armor TO 'master';
GRANT SELECT ON TABLE dungeon.armor to 'player';

/*ITEM tool*/
CREATE TABLE tool (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  name VARCHAR(32) NOT NULL,
  strength TINYINT UNSIGNED NOT NULL,
  versatility TINYINT UNSIGNED NOT NULL,
  quality TINYINT UNSIGNED NOT NULL
);

INSERT INTO seq (name) VALUES ('tool');

DELIMITER ~~
CREATE TRIGGER newTool
BEFORE INSERT ON tool
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='tool');
  UPDATE seq SET id=id+1 WHERE name='tool';
END ~~
DELIMITER ;

GRANT INSERT (name, strength, versatility, quality) 
ON TABLE dungeon.tool TO 'master';
GRANT SELECT ON TABLE dungeon.tool to 'player';

/*character*/
CREATE TABLE monster (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  location INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (location) REFERENCES room(id),
  name VARCHAR(32) NOT NULL,
  hp INTEGER UNSIGNED NOT NULL,
  xp INTEGER UNSIGNED NOT NULL,
  gold INTEGER UNSIGNED NOT NULL,
  melee TINYINT UNSIGNED NOT NULL,
  magic TINYINT UNSIGNED NOT NULL,
  thievery TINYINT UNSIGNED NOT NULL,
  hand INTEGER UNSIGNED,
  FOREIGN KEY (hand) REFERENCES weapon(id),
  body INTEGER UNSIGNED,
  FOREIGN KEY (body) REFERENCES armor(id),
  pocket INTEGER UNSIGNED,
  FOREIGN KEY (pocket) REFERENCES tool(id)
);

INSERT INTO seq (name) VALUES ('monster');

DELIMITER ~~
CREATE TRIGGER newMonster
BEFORE INSERT ON monster
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='monster');
  UPDATE seq SET id=id+1 WHERE name='monster';
END ~~
DELIMITER ;

GRANT INSERT (name) ON TABLE monster TO 'player';
GRANT INSERT (name, hp, xp, gold, melee, magic, thievery)
ON TABLE dungeon.monster TO 'master';
GRANT SELECT ON TABLE dungeon.monster to 'player';

/*lock*/
CREATE TABLE padlock (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  solidity TINYINT UNSIGNED NOT NULL,
  curse TINYINT UNSIGNED NOT NULL,
  complexity TINYINT UNSIGNED NOT NULL
);

INSERT INTO seq (name) VALUES ('padlock');

DELIMITER ~~
CREATE TRIGGER newPadlock
BEFORE INSERT ON padlock
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='padlock');
  UPDATE seq SET id=id+1 WHERE name='padlock';
END ~~
DELIMITER ;

GRANT INSERT (solidity, curse, complexity) ON TABLE dungeon.padlock TO 'master';
GRANT SELECT ON TABLE dungeon.padlock to 'player';

/*door
simply link two room and maybe locked*/

CREATE TABLE door (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  frontRoom INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (frontRoom) REFERENCES room(id),
  backRoom INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (backRoom) REFERENCES room(id),
  locked INTEGER UNSIGNED,
  FOREIGN KEY (locked) REFERENCES padlock(id)
);

INSERT INTO seq (name) VALUES ('door');

DELIMITER ~~
CREATE TRIGGER newDoor
BEFORE INSERT ON door
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='door');
  UPDATE seq SET id=id+1 WHERE name='door';
END ~~
DELIMITER ;

GRANT INSERT (frontRoom, backRoom, locked) 
ON TABLE dungeon.door TO 'master';

/*chest*/

CREATE TABLE chest (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  locked INTEGER UNSIGNED,
  FOREIGN KEY (locked) REFERENCES padlock(id),
  location INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY (location) REFERENCES room(id),
  weapon INTEGER UNSIGNED,
  FOREIGN KEY (weapon) REFERENCES weapon(id),
  armor INTEGER UNSIGNED,
  FOREIGN KEY (armor) REFERENCES armor(id),
  tool INTEGER UNSIGNED,
  FOREIGN KEY (tool) REFERENCES tool(id)
);

INSERT INTO seq (name) VALUES ('chest');

DELIMITER ~~
CREATE TRIGGER newChest
BEFORE INSERT ON chest
FOR EACH ROW BEGIN
  SET NEW.id = (SELECT id FROM seq WHERE name='chest');
  UPDATE seq SET id=id+1 WHERE name='chest';
END ~~
DELIMITER ;

GRANT INSERT (location, locked) 
ON TABLE dungeon.chest TO 'master';

/*normalisation of the stats
the sum of all stat must not go beyond 10
*/

DELIMITER ~~
CREATE PROCEDURE normalize (INOUT a TINYINT UNSIGNED, 
                            INOUT b TINYINT UNSIGNED, 
                            INOUT c TINYINT UNSIGNED)
BEGIN
  SET @tmp = a+b+c;
  SET a = 10*a / @tmp;
  SET b = 10*b / @tmp;
  SET c = 10*c / @tmp;
END ~~
DELIMITER ;

/*unlock
unlock simply mean changing the locked property of [chest] or [door] to null
a trigger will check if your skill allow you to do that
*/

/*hurt
simply change the hp from another dude

*/


