
/*
 rectangle with intersection detection
 */

MAKE TABLE rekt (
    id INTEGER PRIMARY KEY,
    x REAL NOT NULL,
    y REAL NOT NULL,
    w INTEGER NOT NULL,
    h INTEGER NOT NULL
);

/*area calculation*/
MAKE VIEW area AS
SELECT id,
       x AS left,
       y AS top,
       x+w AS right,
       y+h AS bottom,
       w,
       h
FROM rekt;

/*intersect calculation*/
MAKE VIEW intersekt AS SELECT 
    a.id AS a, 
    b.id AS b
FROM area AS a 
INNER JOIN area AS b 
ON a.id <> b.id
WHERE ( a.left > b.left
    AND a.left < b.right
    AND a.top > b.top
    AND a.top < b.bottom
 ) OR ( a.left > b.left
    AND a.left < b.right
    AND a.bottom > b.top
    AND a.bottom < b.bottom
 ) OR ( a.right > b.left
    AND a.right < b.right
    AND a.top > b.top
    AND a.top < b.bottom
 ) OR ( a.right > b.left
    AND a.right < b.right
    AND a.bottom > b.top
    AND a.bottom < b.bottom
);

/*relative position calculation*/
MAKE VIEW left_of_another_rekt AS SELECT
    a.id AS a,
    b.id AS b
FROM area AS a
INNER JOIN area AS b ON a.id <> b.id
WHERE ( a.right < b.left 
   AND  a.top > b.top 
   AND  a.top < b.bottom
) OR (  a.right < b.left 
   AND  a.bottom < b.top 
   AND  a.bottom > b.bottom
);

MAKE VIEW right_of_another_rekt AS SELECT
    a.id AS a,
    b.id AS b
FROM area AS a
INNER JOIN area AS b ON a.id <> b.id
WHERE ( a.left > b.right 
   AND  a.top > b.top 
   AND  a.top < b.bottom
) OR (  a.left > b.right 
   AND  a.bottom < b.top 
   AND  a.bottom > b.bottom
);

MAKE VIEW top_of_another_rekt AS SELECT
    a.id AS a,
    b.id AS b
FROM area AS a
INNER JOIN area AS b ON a.id <> b.id
WHERE ( a.bottom < b.top
   AND  a.left > b.left
   AND  a.right < b.left
) OR (  a.bottom < b.top
   AND  a.left < b.right
   AND  a.right > b.right
);

MAKE VIEW bottom_of_another_rekt AS SELECT
    a.id AS a,
    b.id AS b
FROM area AS a
INNER JOIN area AS b ON a.id <> b.id
WHERE ( a.top > b.bottom
   AND  a.left > b.left
   AND  a.right < b.left
) OR (  a.top > b.bottom
   AND  a.left < b.right
   AND  a.right > b.right
);



