
CREATE TABLE IF NOT EXISTS type(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sector(
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  w INTEGER NOT NULL,
  h INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS entity(
  id INTEGER PRIMARY KEY,
  x INTEGER NOT NULL,
  y INTEGER NOT NULL,
  last_x INTEGER NOT NULL,
  last_y INTEGER NOT NULL,
  grp TEXT,
  sector_id INTEGER,
  type_id INTEGER NOT NULL,
  FOREIGN KEY (sector_id) REFERENCES sector(id),
  FOREIGN KEY (type_id) REFERENCES type(id)
);

CREATE TABLE IF NOT EXISTS entity_part(
  id INTEGER PRIMARY KEY,
  tag TEXT NOT NULL,
  rx INTEGER NOT NULL,
  ry INTEGER NOT NULL,
  last_rx INTEGER NOT NULL,
  last_ry INTEGER NOT NULL,
  entity_id INTEGER NOT NULL,
  type_id INTEGER NOT NULL,
  FOREIGN KEY (entity_id) REFERENCES entity(id),
  FOREIGN KEY (type_id) REFERENCES type(id)
);

CREATE VIEW IF NOT EXISTS completeEntityView AS
SELECT entity.id AS id, 
       x+rx AS x,
       y+ry AS y,
       tag ,
       type.name AS type_name 
FROM entity 
INNER JOIN entity_part ON entity_id=entity.id 
INNER JOIN type ON entity_part.type_id=type.id;

CREATE VIEW IF NOT EXISTS completeEntityInSectorView AS 
SELECT entity.id AS id, 
       x+rx AS nx,
       y+ry AS ny,
       w,
       h,
       tag,
       grp,
       type.name AS type_name 
FROM entity 
INNER JOIN entity_part ON entity_id=entity.id 
INNER JOIN sector ON sector_id=sector.id 
INNER JOIN type ON entity_part.type_id=type.id;

CREATE VIEW IF NOT EXISTS willCollideWithEntity AS 
SELECT entity.id AS id ,
entity.x+entity_part.rx AS entity_part_rx,
entity.y+entity_part.ry AS entity_part_ry,
mover.x+mover_part.rx AS mover_part_rx,
mover.y+mover_part.ry AS mover_part_ry 
FROM entity, entity AS mover 
INNER JOIN entity_part ON entity_part.entity_id=entity.id 
INNER JOIN entity_part AS mover_part ON mover_part.entity_id=mover.id
WHERE entity.sector_id=mover.sector_id 
AND entity.id!=mover.id 
GROUP BY entity.id;

