-- only normalising the type of tile
CREATE TABLE IF NOT EXISTS type(
  id INT PRIMARY KEY,
  name TEXT
);

-- the zone define a place where chunk interact together
CREATE TABLE IF NOT EXISTS zone(
  id INT PRIMARY KEY,
  name TEXT
);

-- the chunk is defined as a group of tile that move together in a zone
CREATE TABLE IF NOT EXISTS chunk(
  id INT PRIMARY KEY,
  x INT NOT NULL,
  y INT NOT NULL,
  nx INT NOT NULL,
  ny INT NOT NULL,
  name TEXT NOT NULL,
  idZone INT NOT NULL,
  FOREIGN KEY (idZone) REFERENCES zone(id)
);

-- the tile is tied to the chunk, it have relative position to the chunk position
CREATE TABLE IF NOT EXISTS tile(
  id INT PRIMARY KEY,
  rx INT NOT NULL,
  ry INT NOT NULL,
  idType TEXT NOT NULL,
  FOREIGN KEY (idType) REFERENCES type(id),
  idChunk INT NOT NULL,
  FOREIGN KEY (idChunk) REFERENCES chunk(id)
);

-- return the absolute coordinate of a tile
CREATE VIEW IF NOT EXISTS tileChunk AS 
SELECT chunk.id AS id, 
       tile.id AS idTile,
       x+rx AS x, 
       y+ry AS y,
       chunk.name AS name,
       type.name AS type,
       zone.name AS zone
FROM chunk 
INNER JOIN tile ON idChunk=chunk.id 
INNER JOIN type ON idType=type.id 
INNER JOIN zone ON idZone=zone.id;

-- return the next absolute coordinate of a tile
CREATE VIEW IF NOT EXISTS tileNextChunk AS 
SELECT chunk.id AS id, 
       tile.id AS idTile,
       nx+rx AS x, 
       ny+ry AS y,
       chunk.name AS name,
       type.name AS type,
       zone.name AS zone
FROM chunk 
INNER JOIN tile ON idChunk=chunk.id 
INNER JOIN type ON idType=type.id 
INNER JOIN zone ON idZone=zone.id;

-- for all tile where next position is not same as actual, return those that collide with another tile current position from not the same chunk
CREATE VIEW IF NOT EXISTS nextCollision AS
SELECT *
FROM tileChunk, tileNextChunk 
WHERE tileChunk.id != tileNextChunk.id 
AND tileNextChunk.nx != tileNextChunk.x 
AND tileNextChunk.ny != tileNextChunk.y 
AND tileChunk.x=tileNextChunk.x 
AND tileChunk.y=tileNextChunk.y 
GROUP BY tileNextChunk.idTile
