

/**/
CREATE TABLE bfapp(
    id INTEGER PRIMARY KEY,
    name TEXT,
    src TEXT
);
/**/
CREATE TABLE bfinstance(
    id INTEGER PRIMARY KEY,
    app INTEGER,
    instptr INTEGER, -- SUBSTR(src,instptr,1)
    cellptr INTEGER,
    lastbracket INTEGER DEFAULT 1, -- cant process ] without, set instptr when [ encountered
    output TEXT,
    FOREIGN KEY(app) REFERENCES bfapp(app)
);
/**/
CREATE TABLE bfcell(
    id INTEGER PRIMARY KEY,
    instance INTEGER,
    cellptr INTEGER,
    value INTEGER,
    UNIQUE(instance,cellptr),
    FOREIGN KEY(instance) REFERENCES bfinstance(id)
);
/**/
CREATE VIEW bfinstruction AS
SELECT bfinstance.id AS instance,
       SUBSTR(bfapp.src,bfinstance.instptr+1,1) AS instruction,
       SUBSTR(bfapp.src,bfinstance.instptr+2) AS rest,
       bfinstance.instptr+INSTR(SUBSTR(bfapp.src,bfinstance.instptr+2),']') AS nextbracket
FROM bfinstance
INNER JOIN bfapp ON bfinstance.app == bfapp.id;

/**/

/**/

CREATE TRIGGER AFTER UPDATE ON HEARTBEAT
BEGIN

    -- create cell if not exist
    INSERT or ignore INTO bfcell (instance,cellptr,value) 
    SELECT id,cellptr,0
    FROM bfinstance;

    -- [.] output current cell
    UPDATE bfinstance 
    SET output=output||(SELECT CHAR(value) FROM bfcell WHERE bfcell.instance=bfinstance.id)
    WHERE id IN (SELECT instance FROM bfinstruction WHERE instruction=='.');

    -- [>] increment cell pointer
    UPDATE bfinstance 
    SET cellptr=cellptr+1
    WHERE id IN (SELECT instance FROM bfinstruction WHERE instruction=='>');

    -- [<] decrement cell pointer
    UPDATE bfinstance 
    SET cellptr=cellptr-1
    WHERE id IN (SELECT instance FROM bfinstruction WHERE instruction=='<');

    -- [+] increment cell value
    UPDATE bfcell SET value=value+1 WHERE id IN (
        SELECT bfinstance.cellptr
        FROM bfinstruction
        INNER JOIN bfinstance ON bfinstruction.instance == bfinstance.id
        WHERE instruction=='+'
    );

    -- [-] decrement cell value
    UPDATE bfcell SET value=value-1 WHERE id IN (
        SELECT bfinstance.cellptr
        FROM bfinstruction
        INNER JOIN bfinstance ON bfinstruction.instance == bfinstance.id
        WHERE instruction=='-'
    );

    -- ([) if current cell pointer value is zero, move forward till next (])
    UPDATE bfinstance 
    SET lastbracket = instptr,
        instptr = (SELECT nextbracket FROM bfinstruction WHERE bfinstruction.instance==bfinstance.id)
    WHERE id IN (
        SELECT bfinstruction.instance 
        FROM bfinstruction 
        INNER JOIN bfcell ON bfcell.instance == bfinstruction.instance
        WHERE instruction=='['
        AND bfcell.value == 0
    );

  
    -- (]) if current cell pointer value is zero, move backward till next ([)
    UPDATE bfinstance 
    SET instptr = lastbracket
    WHERE id IN (
        SELECT bfinstruction.instance 
        FROM bfinstruction 
        INNER JOIN bfcell ON bfcell.instance == bfinstruction.instance
        WHERE instruction==']'
        AND bfcell.value <> 0
    );

    -- increment instruction pointer at the end of each instruction
    UPDATE bfinstance SET instptr=instptr+1;

    -- show output to report
    UPDATE REPORT 
    SET msg=(
        SELECT output FROM bfinstance WHERE app=1
    )
    WHERE ID=1;
END;
/**/

insert into bfapp(name,src) VALUES('helloworld',
'++++++++[>++++[>++>+++>+++>+<<<<-]>+>+>->>+[<]<-]>>.>---.+++++++..+++.>>.<-.<.+++.------.--------.>>+.>++.'
);
insert into bfcell values(1,1,-1,0);
insert into bfinstance values(1,1,0,0,0,'fuck');
insert into report values(1,'');
