/* collision detection part */

-- agg contain tile can be moved around agg, agg move with all its tile
CREATE TABLE IF NOT EXISTS agg (
    id PRIMARY KEY,
    name TEXT NOT NULL,
    col INTEGER NOT NULL,
    row  INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS tile (
    id PRIMARY KEY,
    symbol TEXT NOT NULL,
    aggid INTEGER REFERENCES agg(id) NOT NULL,
    col INTEGER NOT NULL,
    row INTEGER NOT NULL
);

CREATE VIEW IF NOT EXISTS sprite AS
    SELECT agg.id AS id,
           agg.name AS name,
           tile.symbol AS symbol,
           agg.col + tile.col AS col,
           agg.row + tile.row AS row
    FROM agg
    INNER JOIN tile 
    ON tile.aggid=agg.id;

-- test sprite
INSERT INTO agg (id,name,col,row) VALUES(0,'N0!D',0,0);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-1,-2);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,0,-2);

INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-3,-1);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-2,-1);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-1,-1);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,0,-1);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,1,-1);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,2,-1);

INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-3,0);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-1,0);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,0,0);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,2,0);

INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-1,1);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,0,1);

INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-2,2);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,1,2);

INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-3,3);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,-2,3);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,1,3);
INSERT INTO tile(symbol,aggid,col,row) VALUES('blok',0,2,3);




