
CREATE VIRTUAL TABLE boundingBox USING rtree_i32(
    ID INT PRIMARY KEY,
    minX INT,
    maxX INT,
    minY INT,
    maxY INT,
    minZ INT,
    maxZ INT
);

CREATE TABLE model(
    ID INT PRIMARY KEY,
    glId INT NOT NULL,
    x INT NOT NULL,
    y INT NOT NULL,
    z INT NOT NULL,
    w INT NOT NULL,
    h INT NOT NULL,
    d INT NOT NULL,
    boundingBoxID INT NOT NULL,
    FOREIGN KEY(boundingBoxID) REFERENCES boundingBox(ID)
);
CREATE UNIQUE INDEX modelIndex ON model(ID);
CREATE UNIQUE INDEX modelBoundIndex ON model(boundingBoxID);
CREATE TRIGGER modelBoundingTrigger 
AFTER UPDATE OF x,y,z,w,h,d ON model FOR EACH ROW
BEGIN
    UPDATE boundingBox SET minX=NEW.x-NEW.w/2,
                           minY=NEW.y-NEW.h/2,
                           minz=NEW.z-NEW.d/2,
                           maxX=NEW.x+NEW.w/2,
                           maxX=NEW.y+NEW.h/2,
                           maxX=NEW.z+NEW.d/2
    WHERE ID=NEW.boundingBoxID;
END;

CREATE TABLE blok(
    ID INT PRIMARY KEY,
    modelID INT NOT NULL,
    x INT NOT NULL,
    y INT NOT NULL,
    z INT NOT NULL,
    r INT NOT NULL,
    g INT NOT NULL,
    b INT NOT NULL,
    a INT NOT NULL,
    FOREIGN KEY(modelID) REFERENCES model(ID)
);
CREATE UNIQUE INDEX blokIndex ON blok(ID);
CREATE UNIQUE INDEX blokModelIndex ON blok(modelID);

CREATE VIEW blokModel AS
SELECT blok.ID AS blokID,
       blok.modelID AS modelID,
       blok.x+model.x AS x,
       blok.y+model.y AS y,
       blok.z+model.z AS z,
       model.boundingBoxID AS boundingBoxID,
       blok.r AS r,blok.g AS g,blok.b AS b,blok.a AS a
FROM blok
INNER JOIN model
ON model.ID=blok.modelID;

/*match all bloks together when they are not the same*/
CREATE VIEW blokModelCollide AS
SELECT center.blokID AS centerBlokID,
       center.modelID AS centerModelID,
       center.x AS centerX, 
       center.y AS centerY, 
       center.z AS centerZ,
       center.boundingBoxID AS centerBoundingBoxID,
       center.r AS centerR, 
       center.g AS centerG, 
       center.b AS centerB, 
       center.a AS centerA,
       adjacent.blokID AS adjacentBlokID,
       adjacent.modelID AS adjacentModelID,
       adjacent.x AS adjacentX, 
       adjacent.y AS adjacentY, 
       adjacent.z AS adjacentZ,
       adjacent.boundingBoxID AS adjacentBoundingBoxID,
       adjacent.r AS adjacentR, 
       adjacent.g AS adjacentG, 
       adjacent.b AS adjacentB, 
       adjacent.a AS adjacentA
FROM blokModel AS center
INNER JOIN blokModel AS adjacent ON center.blokID!=adjacent.blokID;

/*find all blok that can collide if it was powder*/
CREATE

CREATE VIEW powderNeighbor AS
SELECT centerBlokID,
       centerModelID,
       centerX, centerY, centerZ,
       centerBoundingBoxID,
       centerR, centerG, centerB, centerA,
       adjacentBlokID,
       adjacentModelID,
       adjacentX, adjacentY, adjacentZ,
       adjacentBoundingBoxID,
       adjacentR, adjacentG, adjacentB, adjacentA
FROM blokModelCollide
WHERE (adjacentX=centerX AND adjacentZ=centerZ AND adjacentY=centerY-1)
OR (adjacentX=centerX+1 AND adjacentZ=centerZ AND adjacentY=centerY-1)
OR (adjacentX=centerX-1 AND adjacentZ=centerZ AND adjacentY=centerY-1)
OR (adjacentX=centerX AND adjacentZ=centerZ+1 AND adjacentY=centerY-1)
OR (adjacentX=centerX AND adjacentZ=centerZ-1 AND adjacentY=centerY-1);

/*find all blok that can collide if it was liquid*/
CREATE VIEW liquidNeighbor AS
SELECT centerBlokID,
       centerModelID,
       centerX, centerY, centerZ,
       centerBoundingBoxID,
       centerR, centerG, centerB, centerA,
       adjacentBlokID,
       adjacentModelID,
       adjacentX, adjacentY, adjacentZ,
       adjacentBoundingBoxID,
       adjacentR, adjacentG, adjacentB, adjacentA
FROM blokModelCollide
WHERE (adjacentX=centerX AND adjacentZ=centerZ AND adjacentY=centerY-1)
OR (adjacentX=centerX+1 AND adjacentZ=centerZ AND adjacentY=centerY)
OR (adjacentX=centerX-1 AND adjacentZ=centerZ AND adjacentY=centerY)
OR (adjacentX=centerX AND adjacentZ=centerZ+1 AND adjacentY=centerY)
OR (adjacentX=centerX AND adjacentZ=centerZ-1 AND adjacentY=centerY);

/*when the model is a united form, it stop if ONE blok collide in the same model*/



