-- battle mecanic
CREATE TABLE element(
  id INTEGER,
  name TEXT,
  PRIMARY KEY(id)
);
INSERT INTO element(id, name) VALUES
(1,"physic"),
(2,"magic"),
(3,"mecanic"),
(4,"fire"),
(5,"ice"),
(6,"storm");

CREATE TABLE rockpaperscissor(
  weak INTEGER,
  strong INTEGER,
  PRIMARY KEY(weak,strong),
  FOREIGN KEY(weak) REFERENCES element(id),
  FOREIGN KEY(strong) REFERENCES element(id)
);
INSERT INTO rockpaperscissor(weak,strong) VALUES
(1,2),(2,3),(3,1),(4,5),(5,6),(6,4);

CREATE TABLE weapon(
  id INTEGER,
  name TEXT,
  damage INTEGER,
  distance INTEGER,
  element_id INTEGER,
  PRIMARY KEY(id),
  FOREIGN KEY(element_id) REFERENCES element(id)
);
INSERT INTO weapon (id,name,damage,distance,element_id) VALUES
(1,"punch",5,1,1),
(2,"blast",5,1,2),
(3,"spring",5,1,3),
(4,"fireball",3,3,4),
(5,"icestab",4,2,5),
(6,"thunderstruck",2,4,6);

-- navigation mecanic
CREATE TABLE place(
  id INTEGER,
  name TEXT,
  PRIMARY KEY(id)
);
INSERT INTO place (id,name) VALUES
(1,"quiet village"),
(2,"ice mountain"),
(3,"dark forest"),
(4,"evil castle");

CREATE TABLE path(
  id INTEGER,
  destination INTEGER,
  PRIMARY KEY(id,destination),
  FOREIGN KEY(destination) REFERENCES place(id)
);
INSERT INTO path(id,destination) VALUES
(1,2),(1,1),
(2,3),(2,1),
(3,4),(3,2),
(4,4),(4,3);

-- party
CREATE TABLE party(
  id INTEGER,
  name TEXT,
  place_id INTEGER,
  PRIMARY KEY(id),
  FOREIGN KEY(place_id) REFERENCES place(id)
);

CREATE TABLE character(
  id INTEGER,
  name TEXT,
  hp INTEGER,
  xp INTEGER,
  gold INTEGER,
  party_id INTEGER,
  party_position INTEGER,
  PRIMARY KEY(id),
  FOREIGN KEY(party_id) REFERENCES party(id)
);