


DROP DATABASE IF EXISTS dungeon;
CREATE DATABASE dungeon;
USE dungeon;

/*==============================================================================
basic function
==============================================================================*/
DELIMITER ~~
CREATE FUNCTION randInt ()
RETURNS INTEGER
BEGIN
  RETURN FLOOR(1 + (RAND()*4294967295));
END~~
DELIMITER ;

/*smaller number*/
DELIMITER ~~
CREATE FUNCTION randID ()
RETURNS INTEGER
BEGIN
  RETURN FLOOR(1 + (RAND()*10000));
END~~
DELIMITER ;

/*smaller number*/
DELIMITER ~~
CREATE FUNCTION randTiny ()
RETURNS TINYINT
BEGIN
  RETURN FLOOR(1 + (RAND()*256));
END~~
DELIMITER ;

/*smaller number*/
DELIMITER ~~
CREATE FUNCTION randStat ()
RETURNS TINYINT UNSIGNED
BEGIN
  RETURN FLOOR(1 + (RAND()*10));
END~~
DELIMITER ;
/*==============================================================================
unique id management
==============================================================================*/

/*seqencer hold auto increment function*/
CREATE TABLE seq(
  id INTEGER UNSIGNED NOT NULL,
  name VARCHAR(8) NOT NULL,
  PRIMARY KEY(name)
);

/*return last incremented value*/
DELIMITER ~~
CREATE FUNCTION lastSeq (nameSeq VARCHAR(8))
RETURNS INTEGER
BEGIN
  RETURN (SELECT id
    FROM seq
    WHERE name=nameSeq);
END~~
DELIMITER ;

/*increment value*/
DELIMITER ~~
CREATE PROCEDURE incSeq (IN nameSeq VARCHAR(32))
BEGIN
  UPDATE seq
  SET id=id+1
  WHERE name=nameSeq;
END~~
DELIMITER ;

/*create new sequence*/
DELIMITER ~~
CREATE TRIGGER newSeq BEFORE INSERT ON seq
  FOR EACH ROW BEGIN
    SET NEW.id = 1;/*randInt();*/
  END ~~
DELIMITER ;

/*==============================================================================
the core of the game mecanic
everything can be beaten up
everything give xp
in this world, door, chest, furnace, chauldron are all monster
not only that but also your sword, your book, your key

==============================================================================*/

/*this table use sequencer*/
INSERT INTO seq (name) VALUES ("monster");

/*monster is the core of everything
everything can be fighted
they all have its strength*/
CREATE TABLE monster(
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  str TINYINT UNSIGNED NOT NULL,
  mag TINYINT UNSIGNED NOT NULL,
  mec TINYINT UNSIGNED NOT NULL,
  hp SMALLINT NOT NULL,
  xp SMALLINT NOT NULL
);

/*normalizer, make sure no one is really stronger than the other*/

DELIMITER ~~
CREATE PROCEDURE normalize (INOUT str TINYINT UNSIGNED, INOUT mag TINYINT UNSIGNED, INOUT mec TINYINT UNSIGNED)
BEGIN
  SET mag=(10*mag)/(str+mag+mec);
  SET str=(10*str)/(str+mag+mec);
  SET mec=(10*mec)/(str+mag+mec);
END~~
DELIMITER ;

/*use sequencer to determinate unique id
normalize whatever the user entered in stat*/
DELIMITER ~~
CREATE TRIGGER newmonster BEFORE INSERT ON monster
  FOR EACH ROW BEGIN
    CALL incSeq("monster");
    SET NEW.id = lastSeq("monster");
    SET NEW.hp = 100;
    SET NEW.xp = 0;
    /*SET NEW.str = IF(NEW.str=null,randStat(),NEW.str);
    SET NEW.mag = IF(NEW.mag=null,randStat(),NEW.mag);
    SET NEW.mec = IF(NEW.mec=null,randStat(),NEW.mec);*/
    CALL normalize(NEW.str, NEW.mag, NEW.mec);
  END ~~
DELIMITER ;

/*every change done to monster call the normalizer
will ensure that sum of 3 stat dont exceed 10
will ensure you cant have hp below 0
will ensure you cant have lesser XP than 0 and will hurt you for doing so
normally only fight will change your XP and your HP
but in the future, some item like furnace and chauldron will be able too
*/
DELIMITER ~~
CREATE TRIGGER changemonster BEFORE UPDATE ON monster
  FOR EACH ROW BEGIN
    CALL normalize(NEW.str, NEW.mag, NEW.mec);
    IF NEW.xp<0 THEN
      SET NEW.hp=NEW.hp+NEW.xp;
      SET NEW.xp=0;
    ELSEIF NEW.xp > 10000 THEN
      SET NEW.xp=10000;
    END IF;
    IF NEW.hp<0 THEN
      SET NEW.hp=0;
    ELSEIF NEW.hp > 10000 THEN
      SET NEW.hp=10000;
    END IF;
  END ~~
DELIMITER ;

/*vs stat calculation
it use rock paper scissor paradigm
STR as strength
MAG as magic
MEC as thievery or mecanic
while strength kick thievery, magic kick strenght.....
then the rule is
STR attack MEC at 2x value
*/
/*sub function*/
DELIMITER ~~
CREATE FUNCTION statVS (firstSTAT TINYINT UNSIGNED,
                        secondSTAT TINYINT UNSIGNED)
RETURNS SMALLINT UNSIGNED
BEGIN
  RETURN IF(firstSTAT*2 > secondSTAT, firstSTAT*2-secondSTAT,0);
END~~
DELIMITER ;
/*the real function*/
DELIMITER ~~
CREATE FUNCTION bestStat (firstSTR TINYINT UNSIGNED, 
                        firstMAG TINYINT UNSIGNED,
                        firstMEC TINYINT UNSIGNED,
                        secondSTR TINYINT UNSIGNED,
                        secondMAG TINYINT UNSIGNED,
                        secondMEC TINYINT UNSIGNED)
RETURNS SMALLINT
BEGIN
  RETURN GREATEST(statVS(firstSTR,secondMEC),
                  statVS(firstMAG,secondSTR),
                  statVS(firstMEC,secondMAG));
END~~
DELIMITER ;


/*++++++++++public function+++++++++++++++++++++++++++++++++++++++++++++++++++++
this function should not accept if one of the monster is at 0 hp
the clever trick was to place de condition in the WHERE clause

fight occur between two monster
increase both their xp
decrease both their hp
make sure not to overkill it (trigger handle)
this will call the monsterChange trigger

rule, choose your best stat against the other
randomize between 0 and this number and that is the amount of hp you take from it
the other do the same to you
the amount of xp is not determined by now so i let it to 1 per attack
*/
DELIMITER ~~
CREATE PROCEDURE fight (IN firstID INTEGER UNSIGNED, IN secondID INTEGER UNSIGNED)
BEGIN
  UPDATE monster first
  JOIN monster second ON second.id=secondID
  SET first.xp=first.xp+1, 
      second.xp=second.xp+1,
      first.hp=first.hp-bestStat(first.str,first.mag,first.mec,second.str,second.mag,second.mec),
      second.hp=second.hp-bestStat(second.str,second.mag,second.mec,first.str,first.mag,first.mec)
  WHERE first.id=firstID AND first.hp>0 AND second.hp>0;
END~~
DELIMITER ;

DELIMITER ~~
CREATE FUNCTION createMonster ()
RETURNS INTEGER UNSIGNED
BEGIN
  INSERT INTO monster (str,mag,mec) VALUES (4,3,3);
  RETURN lastSeq("monster");
END ~~
DELIMITER ;

/*==============================================================================
now there is a need for adventurer... and room...then door
==============================================================================*/

/*room maybe have a door...should have a door*/
CREATE TABLE room (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(id) REFERENCES monster(id),
  door INTEGER UNSIGNED,
  FOREIGN KEY(door) REFERENCES room(id),
  name VARCHAR(32) NOT NULL
);

DELIMITER ~~
CREATE TRIGGER newroom BEFORE INSERT ON room
  FOR EACH ROW BEGIN
    SET NEW.id=createMonster();
  END ~~
DELIMITER ;

/*the door is the incarnation of the room itself
  unless dead*/
CREATE VIEW door AS
  SELECT room.id as roomID,
         monster.id as monsterID,
         hp as health, 
         xp as Xperience, 
         str as Strength, 
         mag as Magic, 
         mec as Mecanic
  FROM monster, room 
  WHERE room.id=monster.id
  UNION
  SELECT room.door as roomID,
         monster.id as monsterID,
         hp as health, 
         xp as Xperience, 
         str as Strength, 
         mag as Magic, 
         mec as Mecanic
  FROM monster, room
  WHERE room.id=monster.id;
  
/*the player travel from room to room*/
CREATE TABLE adventurer (
  id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(id) REFERENCES monster(id),
  foundIn INTEGER UNSIGNED NOT NULL,
  FOREIGN KEY(foundIn) REFERENCES room(id)
);

DELIMITER ~~
CREATE TRIGGER newadventurer BEFORE INSERT ON adventurer
  FOR EACH ROW BEGIN
    SET NEW.id=createMonster();
  END ~~
DELIMITER ;

/*todo , 
add name to the player
think about security system for multiplayer
add description to the room or metatag system with view
view for player
view for room
/*
test values
*/



INSERT INTO monster (str,mag,mec) VALUES (30,30,30),
                                        (40,20,20);

INSERT INTO room (name) VALUES ("entrance");
INSERT INTO room (name,door) 
SELECT "tunnel",id 
FROM room WHERE name="entrance";

SELECT * from door;
