PRAGMA foreign_keys = ON;
/*______________________________________________________________________________
tile engine
______________________________________________________________________________*/

/*
 ___      _______      ___      ____
|tag|____|tagging|____|grp|____|tile|
|___|n  1|_______|1  n|___|n  1|____|

 _____      ________
|range|____|neighbor|
|_____|n  1|________|
*/

/*

*/
CREATE TABLE space(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

CREATE TABLE grp(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    coll INTEGER NOT NULL,
    rowe INTEGER NOT NULL,
    name TEXT NOT NULL
);
/*

*/
CREATE TABLE tile(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    coll INTEGER NOT NULL,
    rowe INTEGER NOT NULL,
    symbol TEXT NOT NULL,
    id_grp INTEGER NOT NULL,
    FOREIGN KEY(id_grp) REFERENCES grp(id) ON DELETE CASCADE
);

CREATE TABLE tag(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    UNIQUE(name) ON CONFLICT IGNORE
);

CREATE TABLE tagging(
    id_tag INTEGER NOT NULL,
    id_grp INTEGER NOT NULL,
    FOREIGN KEY (id_tag) REFERENCES tag(id),
    FOREIGN KEY (id_grp) REFERENCES grp(id) ON DELETE CASCADE,
    UNIQUE(id_tag, id_grp) ON CONFLICT IGNORE
);

CREATE VIEW tile80 AS
SELECT tile.id AS id,
       grp.id AS id_grp,
       grp.name AS grp,
       tile.coll+grp.coll AS coll, 
       tile.rowe+grp.rowe AS rowe,
       tile.symbol AS symbol,
       tag.name AS tag
FROM tagging
INNER JOIN grp ON tagging.id_grp = grp.id
INNER JOIN tag ON tagging.id_tag = tag.id
INNER JOIN tile ON tile.id_grp = grp.id;

/*


CREATE VIEW permutation AS
SELECT 0 AS coll,1 AS rowe
UNION ALL
SELECT 0,-1
UNION ALL
SELECT 1,0
UNION ALL
SELECT -1,0
*/
/*
awesome fucking idea!!!!
*/
CREATE TABLE range(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

CREATE TABLE neighbor(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    coll INTEGER NOT NULL,
    rowe INTEGER NOT NULL,
    id_range INTEGER NOT NULL,
    FOREIGN KEY(id_range) REFERENCES range(id),
    UNIQUE(coll,rowe,id_range)
);

CREATE VIEW permutation AS
SELECT coll,rowe,range.name AS range FROM neighbor
INNER JOIN range ON range.id=neighbor.id_range;

INSERT INTO range(name) VALUES('neighbor');
INSERT INTO neighbor(coll,rowe,id_range) SELECT 0,1,range.id FROM range WHERE range.name='neighbor';
INSERT INTO neighbor(coll,rowe,id_range) SELECT 0,-1,range.id FROM range WHERE range.name='neighbor';
INSERT INTO neighbor(coll,rowe,id_range) SELECT 1,0,range.id FROM range WHERE range.name='neighbor';
INSERT INTO neighbor(coll,rowe,id_range) SELECT -1,0,range.id FROM range WHERE range.name='neighbor';
INSERT INTO range(name) VALUES('right');
INSERT INTO neighbor(coll,rowe,id_range) SELECT 1,0,range.id FROM range WHERE range.name='right';
INSERT INTO range(name) VALUES('left');
INSERT INTO neighbor(coll,rowe,id_range) SELECT -1,0,range.id FROM range WHERE range.name='left';
INSERT INTO range(name) VALUES('up');
INSERT INTO neighbor(coll,rowe,id_range) SELECT 0,-1,range.id FROM range WHERE range.name='up';
INSERT INTO range(name) VALUES('down');
INSERT INTO neighbor(coll,rowe,id_range) SELECT 0,1,range.id FROM range WHERE range.name='down';

/*

*/
CREATE VIEW tilePermutation AS
SELECT tile80.id AS id,
       tile80.id_grp AS id_grp,
       tile80.grp AS grp,
       tile80.coll + permutation.coll AS collAdj,
       tile80.rowe + permutation.rowe AS roweAdj,
       tile80.symbol AS symbol,
       permutation.coll AS collDiff,
       permutation.rowe AS roweDiff,
       permutation.range AS range
FROM tile80
JOIN permutation;

/*

*/
CREATE VIEW tileAdjacent AS
SELECT tilePermutation.id,
       tilePermutation.id_grp,
       tilePermutation.grp,
       tilePermutation.collAdj,
       tilePermutation.roweAdj,
       tilePermutation.symbol,
       tilePermutation.collDiff,
       tilePermutation.roweDiff,
       tilePermutation.range,
       tile80.id AS idAdj,
       tile80.id_grp AS id_grpAdj,
       tile80.grp AS grpAdj,
       tile80.coll AS collAdj, 
       tile80.rowe AS roweAdj,
       tile80.symbol AS symbolAdj,
       tile80.tag AS tagAdj
FROM tilePermutation
INNER JOIN tile80
ON tile80.coll = tilePermutation.collAdj 
AND tile80.rowe = tilePermutation.roweAdj
AND tile80.id != tilePermutation.id 
AND tile80.id_grp != tilePermutation.id_grp;




