/*
kvs
*/

DROP VIEW IF EXISTS payload_view;

CREATE TABLE payload_ctx (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE payload_key (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE payload_val (
    key_id INT NOT NULL,
    ctx_id INT NOT NULL,
    value TEXT NOT NULL,
    FOREIGN KEY(key_id) REFERENCES payload_key(id),
    FOREIGN KEY(ctx_id) REFERENCES payload_ctx(id),
    UNIQUE(key_id,ctx_id)
);

CREATE VIEW payload_view AS
    SELECT payload_key.name AS key,
           payload_ctx.name AS ctx,
           payload_val.value as val
    FROM payload_val
    INNER JOIN payload_key ON payload_val.key_id = payload_key.id
    INNER JOIN payload_ctx ON payload_val.ctx_id = payload_ctx.id;

INSERT INTO payload_ctx VALUES(1,"test1");
INSERT INTO payload_ctx VALUES(2,"test2");
INSERT INTO payload_ctx VALUES(3,"test3");
INSERT INTO payload_key VALUES(1,"name");
INSERT INTO payload_key VALUES(2,"str");
INSERT INTO payload_key VALUES(3,"spd");
INSERT INTO payload_key VALUES(4,"int");
INSERT INTO payload_val VALUES(1,1,"NyKraidMorphy");
INSERT INTO payload_val SELECT payload_key.id,
                               2,
                               "Saladin"
                        FROM payload_key
                        WHERE payload_key.name = "name";
INSERT INTO payload_val SELECT payload_key.id,
                               3,
                               "ArmBorgar"
                        FROM payload_key
                        WHERE payload_key.name = "name";

DROP VIEW IF EXISTS payload_test;
CREATE TABLE payload_tuple (
    key TEXT,
    ctx TEXT,
    val TEXT,
    should INTEGER
);
INSERT INTO payload_tuple VALUES ("test1","name","ArmBorgar",1),
                                 ("test2","name","NyKraidMorphy",1),
                                 ("test3","name","Saladin",1),
                                 ("test4","name","Mario",0);
CREATE VIEW payload_test_kvs_shoud_not AS 
    SELECT payload_tuple.ctx || '::' || payload_tuple.key || ':' || payload_tuple.val AS test, 
           "should not exist" AS status
    FROM payload_view
    INNER JOIN payload_tuple
    ON payload_tuple.val = payload_view.val
    AND payload_tuple.key = payload_view.key
    AND payload_tuple.ctx = payload_view.ctx
    AND payload_tuple.should = 0;

CREATE VIEW payload_test AS
    SELECT test,status FROM payload_test_kvs_should_not;
