
/**/

DROP DATABASE IF EXISTS space;
CREATE DATABASE space;
USE space;

/**/

CREATE TABLE planet (
  id Int UNSIGNED AUTO_INCREMENT,
  PRIMARY KEY(id),
  name VARCHAR(32) NOT NULL,
  population Int UNSIGNED,
  money Int UNSIGNED NOT NULL
);

CREATE TABLE ship (
  id Int UNSIGNED AUTO_INCREMENT,
  PRIMARY KEY(id),
  name VARCHAR(32) NOT NULL,
  money Int UNSIGNED NOT NULL,
  orbit Int UNSIGNED NOT NULL,
  FOREIGN KEY (orbit) REFERENCES planet(id),
  fuel Int UNSIGNED NOT NULL
);

CREATE TABLE warp (
  id Int UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY (id) REFERENCES planet(id),
  destination Int UNSIGNED NOT NULL,
  FOREIGN KEY (destination) REFERENCES planet(id),
  distance Int UNSIGNED NOT NULL
);

/**/

INSERT INTO planet (name,money) VALUES ('blublu',1000),
                                       ('pupu',100),
                                       ('marlu',500);

INSERT INTO warp (id,destination,distance) VALUES (1,2,100),
                                                  (2,3,50),
                                                  (3,1,10);

INSERT INTO ship (name, orbit) VALUES ('altus',1);


/**/

DELIMITER ~~
CREATE TRIGGER jumped BEFORE UPDATE ON ship
  FOR EACH ROW BEGIN
    IF NEW.orbit <=> OLD.orbit THEN
      IF (SELECT warp.id, ship.fuel, warp.distance FROM warp, ship
      WHERE warp.id=OLD.id and ship.fuel > warp.distance) IS NOT NULL THEN
        SET NEW.fuel = NEW.fuel-(SELECT warp.distance FROM warp WHERE warp.id=OLD.orbit);
      ELSE
        SET NEW.fuel = 0;
        SET NEW.orbit = OLD.orbit;
      END IF;      
    END IF;
  END ~~
DELIMITER ;

DELIMITER ~~
CREATE PROCEDURE jump (IN shipID Int UNSIGNED, IN planetID Int UNSIGNED)
BEGIN
  IF (SELECT warp.id,ship.fuel,warp.distance FROM warp,ship 
     WHERE warp.id=planetID and ship.fuel > warp.distance) IS NOT NULL THEN
    UPDATE ship 
    SET ship.fuel=ship.fuel-warp.distance, 
        ship.orbit=planetID 
    WHERE ship.id=shipID;
  ELSE
    UPDATE ship
    SET ship.fuel=0
    WHERE ship.id=shipID;
  END IF;
END ~~
DELIMITER ;



