import sqlite3

_init_sql = """
/*
*/
CREATE TABLE IF NOT EXISTS event(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS machine (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);


/*
multiple execution instance per machine
all machine/instance processed at the same time
event are bound to a scope, maybe using user/group
should use log based event execution system, so every thing is recoverable
execution should be called with only one event per cycle

 _order_
|       |1
|   [action]    [event]
|__n|      |1__n|  :) |
    |______|    |_____|
       |1
       |n
    [channel]
    |       |
    |_______|
       |n
       |1
   [instance]   [machine]
   |        |1_n|  :)   |
   |________|   |_______|
*/
CREATE TABLE IF NOT EXISTS channel(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS action(
    id INTEGER PRIMARY KEY,
    last_action_id INTEGER REFERENCES action(id) NULL,
    event_id INTEGER REFERENCES event(id) NOT NULL,
    channel_id INTEGER REFERENCES channel(id) NOT NULL,
    timestamp NUMERIC DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS instance(
    id INTEGER PRIMARY KEY,
    channel_id INTEGER REFERENCES channel(id) NULL,
    machine_id INTEGER REFERENCES machine(id) NOT NULL
);
/*
[machine]    [event]
|   :)  |    |  :) |
|_______|    |_____|
    |n          |n
    |           |
    |1          |1
 [ fsm ]   [transition]
 |     |n_1|          |
 |_____|   |__________|
    |1          |2
    |           |
    |           |n
    |        [state]
    |_______n|     |
             |_____|
*/

CREATE TABLE IF NOT EXISTS state(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS fsm(
    id INTEGER PRIMARY KEY,
    -- machine_id INTEGER REFERENCES machine(id) NOT NULL,
    name TEXT NOT NULL,
    first_state_id INTEGER REFERENCES state(id) NOT NULL
);

CREATE TABLE IF NOT EXISTS transition(
    id INTEGER PRIMARY KEY,
    fsm_id INTEGER REFERENCES fsm(id),
    event_id INTEGER REFERENCES event(id),
    from_state_id INTEGER REFERENCES state(id),
    next_state_id INTEGER REFERENCES state(id)
);

CREATE VIEW IF NOT EXISTS fsm_transition AS
    SELECT state.id AS state_id,
           state.name AS state_name,
           fsm.id AS fsm_id,
           fsm.machine_id AS machine_id,
           transition.id AS transition_id,
           COALESCE(event.id,NULL) AS event_id,
           COALESCE(event.name,NULL) AS event_name,
           next_state.id AS next_state_id,
           next_state.name AS next_state_name
    FROM transition
    INNER JOIN state ON transition.from_state_id = state.id
    INNER JOIN event ON transition.event_id = event.id
    INNER JOIN fsm ON fsm.id = transition.fsm_id
    INNER JOIN state AS next_state 
    ON next_state.id = transition.next_state_id;

CREATE VIEW IF NOT EXISTS mechine_first_state AS
    SELECT state.id AS state_id,
           state.name AS state_name,
           machine.id AS machine_id,
           machine.name AS machine_name
    FROM fsm
    INNER JOIN machine ON fsm.machine_id = machine.id
    INNER JOIN state ON first_state_id;

/*
key value store
[key]   [value]   [instance]
|   |___|     |___|    :)  |
|___|   |_____|   |________|

*/
"""

#example
#from https://upload.wikimedia.org/wikipedia/commons/thumb/9/9e/Turnstile_state_machine_colored.svg/330px-Turnstile_state_machine_colored.svg.png
_test_sql = """
INSERT INTO event(id,name) VALUES(1,'push');
INSERT INTO event(id,name) VALUES(2,'coin');
INSERT INTO machine(id,name) VALUES(1,'turnstile');
INSERT INTO state(id,name) VALUES(1,'locked');
INSERT INTO state(id,name) VALUES(2,'unlocked');
INSERT INTO fsm(id,machine_id,first_state_id) VALUES(1,1,1);
INSERT INTO transition VALUES(1,1,1,1,1);
INSERT INTO transition VALUES(2,1,2,1,2);
INSERT INTO transition VALUES(3,1,1,2,1);
INSERT INTO transition VALUES(4,1,2,2,2);
"""

_test_expect_view_state_transition = [('locked','push','locked'),('locked','coin','unlocked'),('unlocked','push','locked'),('unlocked','coin','unlocked')]
_GetMachineTransitionSql = "SELECT state_name, event_name,next_state_name FROM fsm_transition INNER JOIN machine ON machine_id = fsm_transition.machine_id WHERE machine.name = \'%s\'"
def GetMachineTransition(conn,MachineName):
    '''
    >>> conn = sqlite3.connect(':memory:')
    >>> c = conn.executescript(_init_sql)
    >>> c = conn.executescript(_test_sql)
    >>> conn.commit()
    >>> set(GetMachineTransition(conn,"turnstile")) == set(_test_expect_view_state_transition)
    True
    '''
    cur = conn.cursor()
    cur.execute(_GetMachineTransitionSql % MachineName)
    lstresult = cur.fetchall()
    return lstresult


if __name__ == '__main__':
    import doctest
    doctest.testmod()