import sqlite3

import inc
import defensive

class Tile80(object):
    def __init__(self):
        self.conn = sqlite3.connect(':memory:')
        c = self.conn.cursor()
        with open('tile80.sql') as file:
            c.executescript(file.read())
        self.conn.commit()

    def getPos(self,grpid):
        """
        return position of given grp
        """
        sql = """
        SELECT coll,rowe 
        FROM grp
        WHERE id=? 
        """
        c = self.conn.cursor()
        c.execute(sql,(grpid,))
        return c.fetchone()

    def getAt(self,col,row):
        """
        return all tag that match column and row
        """
        sql = """
        SELECT tag 
        FROM tile80 
        WHERE coll=? 
        AND rowe=? 
        GROUP BY tag
        """
        c = self.conn.cursor()
        c.execute(sql,(col,row))
        for row in c.fetchall():
            yield row

    def window(self,col,row,width,height):
        """
        return all symbol within given coordinate
        """
        sql = """
        SELECT symbol,coll,rowe 
        FROM tile80 
        WHERE coll>=?
        AND rowe>=?
        AND coll<?
        AND rowe<?
        """
        c = self.conn.cursor()
        c.execute(sql,(col,row,col+width,row+height))
        win = {}
        for symbol,col,row in c.fetchall():
            win[(col,row)]=symbol
        return win

    def willCollide(self,grpid,tag,rangeCollide='neighbor'):
        """
        return all tag that grpid will collide with 
        according to the given permutation
        """
        sql = """
        SELECT id_grpAdj
        FROM tileAdjacent 
        WHERE id_grp=? 
        AND range=?
        AND tagAdj=?
        GROUP BY tagAdj
        """
        c = self.conn.cursor()
        c.execute(sql,(grpid,rangeCollide,tag))
        for (grpid,) in c.fetchall():
            yield grpid

    def setPos(self,grpid,col,row):
        """
        set absolute position of a group
        """
        c = self.conn.cursor()
        sql = """
        UPDATE grp 
        SET coll=?, 
        rowe=? 
        WHERE id=?
        """
        c.execute(sql,(col,row,grpid))
        self.conn.commit()

    def permute(self,grpid,rangeCollide='neighbor'):
        """
        will permute one group using predefined permutation
        use only one permutation if more, and it is chosen randomly
        """
        c = self.conn.cursor()
        sql = """
        SELECT coll,rowe 
        FROM permutation 
        WHERE range=? 
        ORDER BY random() 
        LIMIT 1
        """
        c.execute(sql,(rangeCollide,))
        col,row = c.fetchone()
        sql = """
        UPDATE grp 
        SET coll=coll+?, 
        rowe=rowe+? 
        WHERE id=?
        """
        c.execute(sql,(col,row,grpid))
        self.conn.commit()

    def addTag(self,grpid,tag):
        """
        will add one tag to one group
        """
        sql = """
        INSERT INTO tag (name) VALUES(?)
        """
        c = self.conn.cursor()
        c.execute(sql,(tag,))
        sql = """
        INSERT INTO tagging(id_tag,id_grp) 
        SELECT tag.id, ?
        FROM tag WHERE tag.name = ?
        """
        c.execute(sql,(grpid,tag))
        self.conn.commit()

    def rmTag(self,grpid,tag):
        """
        will remove one tag from one group
        """
        c = self.conn.cursor()
        sql = """
        DELETE FROM tagging 
        WHERE id_grp=?
        AND id_tag IN (
            SELECT id 
            FROM tag 
            WHERE name=?
        )
        """
        c.execute(sql,(grpid,tag))
        self.conn.commit()

    def getGrpByTag(self,tag):
        """
        get all id who contain given tag
        """
        c = self.conn.cursor()
        sql = """
        SELECT id_grp FROM tile80 WHERE tag=?
        GROUP BY id_grp
        """
        c.execute(sql,(tag,))
        for (grpid,) in c.fetchall():
            yield grpid

    def getAllTag(grpid):
        """
        get all tag bound to id
        """
        c = self.conn.cursor()
        sql = """
        SELECT tag FROM tile80 WHERE id_grp=?
        GROUP BY tag
        """
        c.execute(sql,(grpid,))
        for (tag,) in c.fetchall():
            yield tag
    
    def addTile(self,grpid,symbol,col,row):
        """
        will add one tile to the group
        """
        c = self.conn.cursor()
        sql = """
        INSERT INTO tile (coll,rowe,symbol,id_grp)
        VALUES(?,?,?,?)
        """
        c.execute(sql,(col,row,symbol,grpid))
        self.conn.commit()
        
    def clrTile(self,grpid):
        """
        will remove all tile from one group
        """
        c = self.conn.cursor()
        sql = """
        DELETE FROM tile WHERE id_grp=?
        """
        c.execute(sql,(grpid,))
        self.conn.commit()

    def mkGrp(self,name):
        """
        insert a new group in the database
        return its ID
        """
        c = self.conn.cursor()
        sql = """
        INSERT INTO grp (coll,rowe,name)
        VALUES(0,0,?)
        """
        c.execute(sql,(name,))
        c.execute("SELECT last_insert_rowid()")
        (grpid,) = c.fetchone()
        self.conn.commit()
        return int(grpid)

    def rmGrp(self,grpid):
        """
        remove one group and all its tile
        """
        c = self.conn.cursor()
        sql = """
        DELETE FROM grp WHERE id=?
        """
        c.execute(sql,(grpid,))
        self.conn.commit()
        
    def dumpOne(self,grpid):
        """
        for given group id
        it dump entire data tree like this
        {
            name:'...',
            col:0,
            row:0,
            tile:[
                {
                    symbol:'..',
                    col:0,
                    row:0
                }
            ],
            tag:[
                '..'
            ]
        }
        """
        c = self.conn.cursor()
        c.execute("SELECT name,coll,rowe FROM grp WHERE id=?",(grpid,))
        name,col,row = c.fetchone()
        ret = {
            'name':name,
            'col':col,
            'row':row,
            'tile':[],
            'tag':[]
        }
        c.execute("SELECT symbol,coll,rowe FROM tile WHERE id_grp=?",(grpid,))
        for symbol,col,row in c.fetchall():
            ret['tile'].append({
                'symbol':symbol,
                'col':col,
                'row':row
            })
        c.execute("SELECT name FROM tag INNER JOIN tagging ON id_tag=tag.id AND id_grp=?",(grpid,))
        for (name,) in c.fetchall():
            ret['tag'].append(name)
        return ret

    def buildOne(self,model):
        """
        expect
        {
            name:'...',
            col:0,
            row:0,
            tile:[
                {
                    symbol:'..',
                    col:0,
                    row:0
                }
            ],
            tag:[
                '..'
            ]
        }
        build it in database
        """
        grpid = self.mkGrp(model[u'name'])
        self.setPos(grpid,model[u'col'],model[u'row'])
        for tile in model[u'tile']:
            self.addTile(grpid,tile[u'symbol'],tile[u'col'],tile[u'row'])
        for tag in model[u'tag']:
            self.addTag(grpid,tag)
        return grpid

def fromTxt(name,aa):
    data = {'name':name,'col':0,'row':0,'tile':[],'tag':['txt']}
    col,row=0,0
    for c in aa:
        if c=='\n':
            row+=1
            col=-1
        elif c!=' ':
            data['tile'].append({'symbol':c,'col':col,'row':row})
        col+=1
    return data

if __name__ == '__main__':

    def show(world):
        win = world.window(0,0,20,20)
        for row in range(20):
            for col in range(20):
                if (col,row) in win.keys():
                    print win[(col,row)],
                #elif len(tuple(world.getAt(col,row)))>0:
                #    print '#',
                else:
                    print ' ',
            print

    import sys,json
    function = sys.argv[1] if len(sys.argv)>1 else None
    filename = sys.argv[2] if len(sys.argv)>2 else None
    
    if function == 'convert':    
        if filename:
            with open(filename) as file:
                with open(filename+'.json','w') as out:
                    out.write(json.dumps(fromTxt(filename,file.read())))
        else:
            print """
                convert what, expect state of the art ascii drawn model like
                  ##
                ######
                # ## #
                  ##
                 #  #
                ##  ##
            """
    elif function == 'show':    
        if filename:
            with open(filename) as file:
                world = Tile80()
                data = json.loads(file.read())
                modelid = world.buildOne(data)
                show(world)
        else:
            print """
            expect json model object like
            {"name":"arobas","col":0,"row":0,"tile":[{"symbol":"@","col":0,"row":0}],"tag":["arobas"]}
            """

    else:
        print """
        function lst
        convert
        show
        test
        """

        import unittest
        class TestSequenceFunctions(unittest.TestCase):
            def setUp(self):
                self.world = Tile80()
                self.square =self.world.buildOne({'tile': [{'symbol': u'#', 'col': 7, 'row': 7}, {'symbol': u'#', 'col': 7, 'row': 8}, {'symbol': u'#', 'col': 7, 'row': 9}, {'symbol': u'#', 'col': 8, 'row': 7}, {'symbol': u'#', 'col': 8, 'row': 8}, {'symbol': u'#', 'col': 8, 'row': 9}, {'symbol': u'#', 'col': 9, 'row': 7}, {'symbol': u'#', 'col': 9, 'row': 8}, {'symbol': u'#', 'col': 9, 'row': 9}], 'row': 0, 'tag': [u'square'], 'name': u'square', 'col': 0})
                self.nazi = self.world.buildOne({'tile': [{'symbol': u'$', 'col': 10, 'row': 5}, {'symbol': u'$', 'col': 15, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 15}, {'symbol': u'$', 'col': 5, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 10}, {'symbol': u'$', 'col': 11, 'row': 5}, {'symbol': u'$', 'col': 15, 'row': 11}, {'symbol': u'$', 'col': 9, 'row': 15}, {'symbol': u'$', 'col': 5, 'row': 9}, {'symbol': u'$', 'col': 9, 'row': 10}, {'symbol': u'$', 'col': 11, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 9}, {'symbol': u'$', 'col': 10, 'row': 11}, {'symbol': u'$', 'col': 12, 'row': 5}, {'symbol': u'$', 'col': 15, 'row': 12}, {'symbol': u'$', 'col': 8, 'row': 15}, {'symbol': u'$', 'col': 5, 'row': 8}, {'symbol': u'$', 'col': 8, 'row': 10}, {'symbol': u'$', 'col': 12, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 8}, {'symbol': u'$', 'col': 10, 'row': 12}, {'symbol': u'$', 'col': 13, 'row': 5}, {'symbol': u'$', 'col': 15, 'row': 13}, {'symbol': u'$', 'col': 7, 'row': 15}, {'symbol': u'$', 'col': 5, 'row': 7}, {'symbol': u'$', 'col': 7, 'row': 10}, {'symbol': u'$', 'col': 13, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 7}, {'symbol': u'$', 'col': 10, 'row': 13}, {'symbol': u'$', 'col': 14, 'row': 5}, {'symbol': u'$', 'col': 15, 'row': 14}, {'symbol': u'$', 'col': 6, 'row': 15}, {'symbol': u'$', 'col': 5, 'row': 6}, {'symbol': u'$', 'col': 6, 'row': 10}, {'symbol': u'$', 'col': 14, 'row': 10}, {'symbol': u'$', 'col': 10, 'row': 6}, {'symbol': u'$', 'col': 10, 'row': 14}], 'row': 0, 'tag': [u'nazi'], 'name': u'nazi', 'col': 0})

            def test_cango(self):
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','right')))>0)
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','left')))==0)
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','down')))>0)
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','up')))==0)
        
            def test_afterMoveLeft(self):
                self.world.permute(self.square,'left')
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','right')))==0)
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','left')))>0)
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','down')))>0)
                self.assertTrue(len(tuple(self.world.willCollide(self.square,'nazi','up')))==0)
        
            def test_loadReload(self):
                before = tuple(self.world.window(0,0,20,20))
                sqrDump = self.world.dumpOne(self.square)
                self.world.rmGrp(self.square)
                after = tuple(self.world.window(0,0,20,20))
                self.world.buildOne(sqrDump)
                then = tuple(self.world.window(0,0,20,20))
                self.assertTrue(before != after)
                #self.assertTrue(before == then)
        unittest.main()