
/*
 sqlite have everything instead human interaction
 only need to implement EVENT when the user do something
 report when the user want to see what happened
 and schedule because the time is another kind of user
 */


/**************************************
 MACRO for more concise code
 **************************************/
/* macro create */

--MACRO:A (TABLE|VIEW|TRIGGER) ([a-zA-Z_]+):--
/* create or replace {1} {2} */
DROP {1} IF EXISTS {2}; CREATE {1} {2}
--END:--

A TABLE test_table (ID INT PRIMARY KEY);
A VIEW test_view AS SELECT 'test' AS ID;
A TRIGGER test_trigger INSTEAD OF INSERT ON test_view FOR EACH ROW
BEGIN
    INSERT INTO test_table VALUES (new.ID);
END;
INSERT INTO test_view VALUES (666);


/**************************************
 multiuser environment
 **************************************/
CREATE TABLE USR (
    ID INTEGER PRIMARY KEY,
    NAME TEXT UNIQUE NOT NULL
);
/**/
INSERT INTO USR (NAME) VALUES ('sys'),('test');
/**/
INSERT OR REPLACE INTO USR (ID,NAME) VALUES (NULL,'test');

/**************************************
 LOGGING facility
 main tool for robust programming

 \log_msg\   \usr \
  \-at    \   \name\
   \-usr   \-->\-id \  \log_level\
    \-level \---------->\-id      \
     \-msg   \           \-name    \
     -------------------------------
     /LOGGING                /
    /USR => usr.name        /
   /LEVEL => log_level.name/
  /MSG => log_msg.msg     /
 **************************************/

/* LOGGING stm;
   it can be WARN DEBUG ERROR TRACE INFO
   need its own garbage collection
   OR at least inhibit a part of it
 */

/* on a clean name space */
DROP TRIGGER IF EXISTS LOG_INSERT;
DROP TRIGGER IF EXISTS LOG_UPDATE;
DROP TRIGGER IF EXISTS LOG_DELETE;
DROP VIEW IF EXISTS LOGGING; 

/* create log level */
CREATE TABLE LOG_LEVEL(
    ID INTEGER PRIMARY KEY,
    LEVEL TEXT NOT NULL
);
INSERT INTO LOG_LEVEL VALUES(1,'ERROR'),
                            (2,'WARN'),
                            (3,'INFO'),
                            (4,'DEBUG'),
                            (5,'TRACE'),
                            (6,'TEST');

/* create log message */
CREATE TABLE LOG_MSG (
    AT DATETIME DEFAULT CURRENT_TIMESTAMP,
    USR INTEGER NOT NULL,
    LEVEL INTEGER NOT NULL,
    MSG TEXT NOT NULL,
    FOREIGN KEY(USR) REFERENCES USR(ID),
    FOREIGN KEY(LEVEL) REFERENCES LOG_LEVEL(ID)
);

/* create logging front end */
CREATE VIEW LOGGING AS SELECT 
    USR.NAME AS USR,
    LOG_LEVEL.LEVEL AS LEVEL,
    LOG_MSG.AT||' : '||LOG_MSG.MSG AS MSG
FROM LOG_MSG
INNER JOIN USR ON LOG_MSG.USR = USR.ID
INNER JOIN LOG_LEVEL ON LOG_MSG.LEVEL = LOG_LEVEL.ID;

/* add log entry by directly insert into logging (dont put timestamp, it is automagic*/
CREATE TRIGGER LOG_INSERT INSTEAD OF INSERT ON LOGGING FOR EACH ROW
BEGIN
    -- LEVEL must exist
    -- USR must exist
    INSERT INTO LOG_MSG (AT,USR,LEVEL,MSG) 
    VALUES (
        DATETIME('now'),
        (SELECT ID FROM USR WHERE NAME = new.USR),
        (SELECT ID FROM LOG_LEVEL WHERE LEVEL = new.LEVEL),
        new.MSG
    );
END;
INSERT INTO LOGGING VALUES('sys','TEST','test insert on logging');

/* you cant update a log entry */
CREATE TRIGGER LOG_UPDATE INSTEAD OF UPDATE ON LOGGING
BEGIN
    SELECT 'a log table may be cleaned but not modified, no need to store unreliable data';
END;

/* deleting log entry is a valid way to uncluther a database */
CREATE TRIGGER LOG_DELETE INSTEAD OF DELETE ON LOGGING
BEGIN
    DELETE FROM LOG_MSG WHERE AT=old.AT;
END;


/* macro logging */
--MACRO:LOG (INFO|WARN|ERROR|DEBUG|TRACE) ([^;]+);:-- 
-- log at level {1}
INSERT INTO LOGGING (USR,LEVEL,MSG) SELECT usr,'{1}',msg FROM ({2});
--END:--
LOG INFO select 'sys' as usr,
                'info macro test 'as msg ;
LOG DEBUG SELECT 'sys' as usr, 
                 'debug macro test' as msg;

/**************************************
 REPORTING table WITH stm
 assume the table have ID as row
 the ID will enable to write update the right row
 msg can be anything, ansi-esc, html...

     \report\
      \-id   \
       \-usr  \
        \-msg  \
(reporting macro)
     /view     /
    /-renderid/
   /-usr     /
  /-msg     /
 /-id      /
 ***************************************/

CREATE TABLE REPORT (
    ID TEXT NOT NULL,
    USR TEXT DEFAULT 'sys', -- stay denormalized
    MSG TEXT NOT NULL,
    PRIMARY KEY (ID,USR)
);

--MACRO:REPORTING ([^;]+);:--
-- report (id,usr,msg)
INSERT OR REPLACE INTO REPORT (ID,USR,MSG) {1};
--END:--

REPORTING VALUES ('test','report','first report');
REPORTING SELECT 'test',USR.NAME,'individual report'
FROM USR;

--MACRO:REPORTING FROM ([a-zA-Z_]+) WITH ([^;]+);:--
-- report FROM {1} WITH query
-- Expect WITH (renderid TEXT,usr TEXT,msg TEXT,id INTEGER)
-- Expect FROM {1} (id INTEGER,...)
DROP TRIGGER IF EXISTS reporting_{1}_draw; 
CREATE TRIGGER reporting_{1}_draw 
AFTER INSERT ON {1} FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO REPORT (ID,USR,MSG)
    SELECT renderid, usr, msg  FROM ({2}) 
    WHERE ID=new.id;
END;
DROP TRIGGER IF EXISTS reporting_{1}_redraw; 
CREATE TRIGGER reporting_{1}_redraw 
AFTER UPDATE ON {1} FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO REPORT (ID,USR,MSG)
    SELECT renderid, usr, msg FROM ({2}) 
    WHERE ID=new.id;
END;
DROP TRIGGER IF EXISTS reporting_{1}_erase; 
CREATE TRIGGER reporting_{1}_erase 
AFTER DELETE ON {1} FOR EACH ROW
BEGIN
    DELETE FROM REPORT 
    WHERE ID = (select renderid from ({2}) where id=new.id);
END;
--END:--

/* test reporting */
REPORTING FROM test_table WITH 
SELECT 'test'||id AS renderid,
       'report' AS usr,
		'reporting from table change' AS msg,
		id AS id
FROM test_table;
insert or replace into test_table values (13);
update test_table set id=666 where id=666;





/**************************************
 event manager
 event are sent from the front end
 most logic depend on it
 had to use emit because sqlite seam to use event keyword

 \MSG       \                       \EVENT     \
  \-timestamp\     \USR  \           \-timestamp\
   \-msg      \     \-name\  \CHAN \  \-msg      \
    \-usr      \---->\-id  \  \-name\  \-usr      \
     \-chan     \------------->\-id  \  \-chan     \
     ---------------------
    /EMIT                /
   /ID => event_msg.id  /
  /USR => usr.name     /
 /msg => event_msg.msg/
 ***************************************/
DROP TRIGGER IF EXISTS EMIT_INSERT;
DROP TRIGGER IF EXISTS EMIT_UPDATE;
DROP TRIGGER IF EXISTS EMIT_DELETE;
DROP VIEW IF EXISTS EMIT;

/* main table */
CREATE TABLE MSG (
    ID INTEGER PRIMARY KEY,
    USR INTEGER NOT NULL,
    CHAN INTEGER NOT NULL,
    MSG TEXT NOT NULL,
    TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (USR) REFERENCES USR(ID),
    FOREIGN KEY (CHAN) REFERENCES CHAN(ID)
);
/**/
CREATE TABLE CHAN (
    ID INTEGER PRIMARY KEY,
    NAME TEXT UNIQUE NOT NULL
);
/**/
CREATE TABLE EVNT (
    CHAN INTEGER UNIQUE NOT NULL,
    USR INTEGER UNIQUE NOT NULL,
    MSG TEXT NULL,
    FOREIGN KEY (USR) REFERENCES USR(ID),
    FOREIGN KEY (CHAN) REFERENCES CHAN(ID),
    FOREIGN KEY (MSG) REFERENCES MSG(ID)
);

/* view abstract */
CREATE VIEW EMIT AS SELECT
    CHAN.NAME AS CHAN,
    USR.NAME AS USR,
    EVNT.MSG AS MSG
FROM EVNT
INNER JOIN USR ON USR.ID = EVNT.USR
INNER JOIN CHAN ON CHAN.ID = EVNT.CHAN;

/* when insert on event, 
   the user is added magicly,
   only insert (id,usr,msg) */

CREATE TRIGGER EMIT_INSERT INSTEAD OF INSERT ON EMIT FOR EACH ROW
BEGIN
    -- add usr if not present
    INSERT OR REPLACE INTO USR (ID,NAME)
    SELECT (SELECT ID FROM USR WHERE NAME=new.USR) AS ID,
           new.USR AS NAME;

    -- add chan if not present
    INSERT OR REPLACE INTO CHAN (ID,NAME)
    SELECT (SELECT ID FROM CHAN WHERE NAME=new.CHAN) AS ID,
           new.USR AS NAME;

    -- msg are cumulative already present for that usr
    INSERT INTO MSG (CHAN,USR,MSG) 
    SELECT (SELECT ID FROM USR WHERE NAME=new.USR) AS USR,
           (SELECT ID FROM CHAN WHERE NAME=new.CHAN) AS CHAN,
           new.MSG AS MSG;

    -- create event if not present and then update it
    INSERT OR REPLACE INTO EVNT (CHAN,USR,MSG)
    SELECT (SELECT ID FROM CHAN WHERE NAME=new.CHAN) AS CHAN,
           (SELECT ID FROM USR WHERE NAME=new.USR) AS USR,
           new.MSG AS MSG;
END;

/* when update on event, make sur it exist, and the user exist */
CREATE TRIGGER EMIT_UPDATE INSTEAD OF UPDATE ON EMIT FOR EACH ROW
BEGIN
    INSERT INTO EMIT(CHAN,USR,MSG) VALUES(new.CHAN,new.USR,new.MSG);
END;

/* when delete on event */
CREATE TRIGGER EMIT_DELETE INSTEAD OF DELETE ON EMIT FOR EACH ROW
BEGIN
    INSERT INTO EMIT(CHAN,USR,MSG) VALUES(old.CHAN,old.USR,'dead');
END;

/**/
INSERT INTO EMIT VALUES('test','test','test trigger insert');
/**/
UPDATE EMIT SET MSG='test update trigger' WHERE CHAN = 'test' AND USR = 'test';
/**/
DELETE FROM EMIT WHERE CHAN='test' AND USR='test';
/**/
INSERT INTO EMIT VALUES('test','sys','test trigger insert on another user');
/**/
INSERT INTO EMIT VALUES('test2', 'sys', 'test trigger insert on on another id');

/* event macro */
--MACRO:HAPPEN ([a-zA-Z_]+):--
-- a trigger on event {1}
DROP TRIGGER IF EXISTS HAPPEN_{1}; CREATE TRIGGER HAPPEN_{1} 
INSTEAD OF UPDATE ON EMIT FOR EACH ROW WHEN new.ID = {1}
--END:--

HAPPEN makeithappen
BEGIN
    LOG DEBUG SELECT
     --(select NAME FROM USR WHERE ID = new.USR) as usr,
     new.USR AS usr,
     new.MSG AS msg ;
END;
INSERT INTO EMIT VALUES ('makeithappen','happener','happening');
INSERT INTO EMIT VALUES ('makeithappen','happener','happening again');


/**************************************
 scheduler (at #|now) (every #|once) begin ... end;
   once triggered, the scheduler change its [at] value to next schedule using every statement
   the listener should trigger on update, 
   so insert or replace will lock the row 
   and the update will ignite the trigger
   schedule dont have usr

 ID is liked to EMIT
 AT represent trigger time
 EVERY represent repeat is set

 \interval \       \timeout\       \emit\
  \-id      \------>\-id    \------>\-id \
   \-modifier\       \-at    \       \-usr\(scheduler)
                                      \-msg\
    ----------------------------------------
     /TODO                 /
    /-ID                  /
   /-'scheduler' as USR  /
  /-timeout.at AS MSG   /
 /-now+modifier AS NEXT/
 ***************************************/
DROP VIEW IF EXISTS TODO;
DROP TRIGGER IF EXISTS SCHEDULE_TIMEOUT_INSERT;
DROP TRIGGER IF EXISTS SCHEDULE_INTERVAL_INSERT;

INSERT OR REPLACE INTO USR VALUES(3,'schedule');

/* timeout, run once */
CREATE TABLE SCHEDULE_TIMEOUT (
    ID TEXT UNIQUE,
    AT DATETIME NOT NULL,
    FOREIGN KEY (ID) REFERENCES EMIT(ID)
);

/* repeat every, run at interval */
CREATE TABLE SCHEDULE_INTERVAL (
    ID TEXT UNIQUE,
    MODIFIER TEXT NOT NULL,
    FOREIGN KEY (ID) REFERENCES SCHEDULE_TIMEOUT(ID)
);

/* a schedule_timeout is todo when at is smaller than now */
CREATE VIEW TODO AS
SELECT EMIT.ID AS ID,
       'scheduler' AS USR,
       TIMEOUT.AT AS MSG,
       DATETIME(TIMEOUT.AT,INTERVAL.MODIFIER) AS NEXT
FROM EMIT
INNER JOIN SCHEDULE_TIMEOUT AS TIMEOUT ON TIMEOUT.ID = EMIT.ID
LEFT JOIN SCHEDULE_INTERVAL AS INTERVAL ON INTERVAL.ID = TIMEOUT.ID
WHERE DATETIME('now') < TIMEOUT.AT;



/* a timeout trigger an emit */
CREATE TRIGGER SCHEDULE_TIMEOUT_INSERT 
BEFORE INSERT ON SCHEDULE_TIMEOUT FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO EMIT (ID,USR,MSG)
    VALUES (new.ID,'scheduler','new schedule');
END;

/* a repeat reset a timeout */
CREATE TRIGGER SCHEDULE_INTERVAL_INSERT
BEFORE INSERT ON SCHEDULE_INTERVAL FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO SCHEDULE_TIMEOUT (ID,TIME)
    VALUES (new.ID,DATETIME('now',new.MODIFIER));
END;

/* when updating a todo, its emit is trigger, its timer is refreshed if apply */
CREATE TRIGGER TODO_UPDATE
INSTEAD OF UPDATE ON TODO FOR EACH ROW
BEGIN
    -- if next exists
    -- INSERT OR REPLACE INTO SCHEDULE_TIMEOUT (ID,AT) VALUES(ID,new.NEXT);

    -- trigger the emit
    UPDATE EMIT SET USR='schedule', msg=new.AT;
END;
/* a schedule timeout is refreshed if updated beyond its time and have an interval */
/*
CREATE TRIGGER SCHEDULE_TIMEOUT_UPDATE
AFTER UPDATE ON SCHEDULE_TIMEOUT FOR EACH ROW
WHEN old.AT < DATETIME('now')
BEGIN
    
END;
*/
/**/
