"""SQLITE

pandora concept
business logic in data as relational script
using view and trigger
event store concept

message queue between independant db
"""
class Sql(object):
    def __init__(self,dbfile):
        self.dbfile=dbfile
        self.conn = False

    def _connect(self):
        if not self.conn:
            self.conn = sqlite3.connect(':memory:',
                                        check_same_thread=False,
                                        isolation_level=None)
            self.conn.cursor().execute("ATTACH DATABASE '%(dbfile)s' AS HSS;" % {'dbfile':self.dbfile})
            self.conn.commit()
        return self

    def _query(self,querytxt):
        self._connect()
        result=[]
        try:
            cur = self.conn.cursor()
            cur.execute(querytxt)
            if cur.description:
                col = [ attr[0] for attr in cur.description ]
                result = [ dict(zip(col,row)) for row in cur.fetchall()]
        except Exception as e:
            print(e)
        return result

    def _update(self,querytxt):
        self._connect()
        try:
            cur = self.conn.cursor()
            cur.execute(querytxt)
            self.conn.commit()
        except Exception as e:
            print(e)
        return self

    def _haveTable(self,name):
        sql = "SELECT 1 FROM TS WHERE TB="+name
        return bool(self._query(sql))

    @staticmethod
    def compile(dbname,querytxt):
        #try:
        #    os.remove(dbname)
        #except:
        #    pass
        conn = sqlite3.connect(dbname)
        try:
            cur = conn.cursor()
            cur.executescript("""
                CREATE TABLE EM (
                    ID INTEGER PRIMARY KEY,
                    KEY TEXT NOT NULL,
                    MSG TEXT NOT NULL,
                    TS DATETIME DEFAULT CURRENT_TIMESTAMP);
                CREATE VIEW KS AS
                    SELECT DISTINCT KEY
                    FROM EM;
                CREATE VIEW TS AS
                    SELECT name AS TB
                    FROM sqlite_master
                    INNER JOIN KS ON KS.KEY = name
                    WHERE type IN ('table','view');
		CREATE VIEW LG AS
		    SELECT KEY,MSG
		    FROM EM
		    ORDER BY TS DESC;
                              """)
            cur.executescript(querytxt)
            conn.commit()
            def printsql(query):
                cur.execute(query)
                for row in cur.fetchall():
                    print row
            print "compiled"
            print "list of table"
            printsql("""
                select name,type
                from sqlite_master 
                where type in ('table','view')
                     """)
            print "done"
        except Exception as e:
            print(e)
        return conn

    def chan(self):
        sql = "select * from HSS.KS"
        result = self._query(sql)
        return result

    def emit(self,key,msg):
        sql = "insert into HSS.EM(KEY,MSG) values('"+key+"','"+msg+"')"
        self._update(sql)

    def amend(self,key,msg):
        sql = "update HSS.EM set MSG='"+msg+"' where ID=(select max(id) from HSS.EM where KEY='"+key+"')"
	self._update(sql)

    def query(self,tbl):
        """
        many table and view accessible from here
        """
        if not self._haveTable(tbl):
            sql = "select KEY,MSG from HSS.EM where ID=(select max(id) from HSS.EM where KEY='"+tbl+"')"
	else:
	    sql = "select * from HSS."+tbl
        return self._query(sql)

"""ADMIN
"""
def Admin(zfile="app.zip", 
          sfile="save.db"):
    #db=None
    #def _th():
    #    return _th
    #pay['th'] = Thread(_th,5)
    Dic = multiprocessing.Manager().dict()
    Que = multiprocessing.Queue()

    Dic['api'] = { "hello" : "world" }


    #db = Sql(sfile)
    http = Http(zfile,
		lambda path: "http get "+ path,
		[
		    #( 'chan' , lambda : db.chan() ),
		    #( 'emit' , lambda iD, msg : db.emit(iD,msg) ),
		    #( 'amend' , lambda iD, msg : db.amend(iD,msg) ),
                    #( 'query', lambda iD : db.query(iD) )
		])

    cli = []
    def _help():
        for name,_,how in cli:
            if how:
                print name,how
    cli.append(('help',_help," => show this"))
    def _play():
        import webbrowser
        webbrowser.open('localhost:57005/www/main.html',2,True)
        print "webbrowser triggered"
    cli.append(('play',_play," => start game"))
    def _restart():
        print("""restart""")
        os.execl(sys.executable, sys.executable, * sys.argv)
    cli.append(('restart', _restart," => restart the whole server"))
    #def _reinit():
    #    print "reinit the whole thing in ",sfile
    #os.remove(sfile)
    #    appz = zipfile.ZipFile(zfile, 'r')
    #   for name in appz.namelist():
    #       if name.endswith('.sql'):
    #          Sql.compile(sfile,appz.open(name).read())
    #       db = Sql(sfile)
    #cli.append(('reinit', _reinit, " => reinit the services"))
    def _repak():
        print "repaking the whole shit to",zfile
	os.remove(zfile);
	ziph = zipfile.ZipFile(zfile,'w',zipfile.ZIP_DEFLATED)
	for root, dirs, files in os.walk('app'):
	    for file in files:
                path = os.path.join(root,file)
	        ziph.write(path)
        ziph.close()
    cli.append(('repak',_repak, " => re package app/ dir to app.zip"))
    #threading
    #def _start():
    #    print "start thread"
    #    pay['th'].start()
    #cli.append(('start',_start," => initiate heartbeat if not already"))
    #def _stop():
    #    print "stop thread"
    #    pay['th'].stop()
    #cli.append(('stop',_stop," => stop heartbeat"))
    #database sqlite
    #def _emit():
    #    default_id = 'testID'
    #    default_msg = 'anything'
    #    _id = raw_input("ID ("+default_id+"?): ")
    #    if not _id:
    #        _id = default_id
    #    _msg = raw_input("MSG ("+default_msg+"?): ")
    #    if not _msg:
    #        _msg = default_msg
    #    pay['db'].emit(_id,_msg)
    #    print "ok"
    #cli.append(('emit',_emit," => emit message to database"))
    #def _get():
    #    default_id = 'testID'
    #    _id = raw_input("ID ("+default_id+"?): ")
    #    if not _id:
    #        _id = default_id
    #    print pay['db'].get(_id)
    #cli.append(('get',_get," => get data from database with id"))
    #def _pulse():
    #    pay['db'].pulse()
    #    print "triggered one cycle"
    #cli.append(('pulse',_pulse," => trigger one heartbeat into the database"))
    #def _load(default_dbfilename='hss.db'):
    #    filename = raw_input("load db file ("+default_dbfilename+"?) : ")
    #    if not filename:
    #        filename = default_dbfilename
    #    pay['db'] = Sql(filename)
    #    print "loaded"
    #cli.append(('load',_load," => load sqlite database"))
    #def _compile(default_filename = 'hss.sql'):
    #    filename = raw_input("compile src file ("+default_filename+"?) : ")
    #    if not filename:
    #        filename = default_filename
    #    file = open(filename,'r')
    #    src = file.read()
    #    dbfilename = filename.replace('.sql','.db')
    #    db = Sql.compile(dbfilename,src)
    #    _load(dbfilename)
    #cli.append(('compile',_compile," => compile sqlite code"))
    #default menu item
    def _tryhelp():
        print('try help')
    cli.append((True,_tryhelp,""))
    cli.append(('','','exit => to shutdown'))
    #the main loop
    command = raw_input("]]] ")
    while command != 'exit':
        for condition, functor,doc in cli:
            if isinstance(condition,str):
                predicate = lambda a : re.compile(condition).match(command)
            if isinstance(condition,bool):
                predicate = lambda a : condition
            if callable(condition):
                predicate = condition
            if predicate(command):
                functor()
                break
        command = raw_input("]]] ")
    print 'bye'