import sqlite3, os.path 

lonFrom=14.3 * 10000000
lonTo=14.6 * 10000000
latFrom=49.7 * 10000000
latTo=50.0 * 10000000

whereSql = "WHERE X > %.4f AND X < %.4f AND Y > %.4f AND Y < %.4f" % (lonFrom, lonTo, latFrom, latTo)
#whereSql = ""

fixedNodesInOutput = set()

def polyAreaWS(polygon):
    if len(polygon) < 3:
        return 0
    j = len(polygon) - 1;

    area = 0.0
    for i in xrange(len(polygon)):
        area += float(polygon[j][0]) * polygon[i][1] - float(polygon[i][0]) * polygon[j][1]
        j = i
    return area / 2.0;
 
def isClockwise(polygon):
    return polyAreaWS(polygon) < 0;

def isPolyClockwise(idPoly):
    cur = con.cursor() 
    poly = []
    for row in cur.execute("""SELECT X, Y FROM AreaNodes  
    INNER JOIN Nodes ON AreaNodes.ID_NODE=Nodes.ID_NODE  
    WHERE ID_AREA=?
    ORDER BY AreaNodes.SEQ""", (idPoly,)):
        poly.append((row['X'], row['Y']))
    return isClockwise(poly) 

def ex(cur, sql):
    print sql
    cur.execute(sql)

def createIndex(table, column):
    cur = con.cursor() 
    ex(cur, """CREATE INDEX IF NOT EXISTS i_%s%s ON %s (%s)""" % \
        (table, column, table, column))

def getWaysNodes(wayGroup, area):
    cur = con.cursor()
    typ = area and "AREA" or "LINE"
    waysNodes = {}
    lastWay = None
    for row in cur.execute("""SELECT ID_%s AS ID_WAY, Nodes.ID_NODE, X, Y FROM %sNodes
    INNER JOIN Nodes ON %sNodes.ID_NODE=Nodes.ID_NODE
    WHERE ID_%s IN (%s) ORDER BY ID_%s, SEQ""" % (typ, typ, typ, typ, 
    ",".join(map(lambda x: str(x), wayGroup)), typ)):
        if lastWay != row['ID_WAY']:
            nodes = []
            waysNodes[row['ID_WAY']] = nodes
            lastWay = row['ID_WAY']
        row['X'] /= 10000000.0
        row['Y'] /= 10000000.0
        nodes.append(row)
    return waysNodes

def splitBy(array, groupSize):
    temp = []
    for i in array:
        temp.append(i)
        if len(temp) >= groupSize:
            yield temp
            temp = []
    if len(temp) > 0:
        yield temp
    
def getWays(ways, area):
    cur = con.cursor()
    typ = area and "Area" or "Line"
    for wayGroup in splitBy(ways, 1000):
        resultWays = {} 
        for row in cur.execute("""SELECT %ss.ID_%s AS ID_WAY, NAME, OBJ_ID FROM %ss
        INNER JOIN Dibavod ON Dibavod.ID_DIBAVOD=%ss.ID_DIBAVOD
        WHERE ID_WAY IN (%s)""" % (typ, typ, typ, typ,
        ",".join(map(lambda x: str(x), wayGroup)))):
            resultWays[row['ID_WAY']] = row

        resultNodes = getWaysNodes(wayGroup, area)
        
        assert len(resultNodes) == len(wayGroup)
        assert len(resultNodes) == len(resultWays)
        
        for k in resultNodes.keys():
            yield (resultWays[k], resultNodes[k])
    
def dumpWays(prefix, idsLines, tags, area, idOffset):
    total = 0
    outIndex = 1
    if type(prefix) is file:
        split = False
        out = prefix
    else:
        split = True 
        out = None
    lastPercent = -1
    for (way, nodes) in getWays(idsLines, area):
        if out is None:
            out = open("%s_%03d.xml" % (prefix, outIndex), "wb")
            outIndex += 1

            print >> out, """<?xml version='1.0' encoding='UTF-8'?>
<osm version='0.5' generator='vody'>"""

        total += 1
        percent = 100 * total / len(idsLines)
        if percent != lastPercent:
            print '%d%%' % percent, total, '/', len(idsLines)
            lastPercent = percent  
        # Print nodes
        if len(nodes) <= 1:
            print "Warning, line has less than 2 points: ", way['ID_WAY']
            continue
        for node in nodes:
            if node['ID_NODE'] in fixedNodes:
                if node['ID_NODE'] in fixedNodesInOutput:
                    continue
                fixedNodesInOutput.add(node['ID_NODE'])
            print >> out, """<node id="-%d" lon="%.9f" lat="%.9f" />""" \
                % (node['ID_NODE'], node['X'], node['Y'])
        
        # Print way
        print >> out, """<way id="-%d">""" % (way['ID_WAY'] + idOffset)
        for node in nodes:
            print >> out, """<nd ref="-%d" />""" % node['ID_NODE']
        if area:
            print >> out, """<nd ref="-%d" />""" % nodes[0]['ID_NODE']
        
        # Print tags
        for k, v in tags.iteritems():
            print >> out, """<tag k="%s" v="%s" />""" % (k, v)
        if way['NAME'] is not None:
            print >> out, """<tag k="name" v="%s" />""" % way['NAME'].encode('utf8')
        if way['OBJ_ID'] is not None:
            print >> out, """<tag k="dibavod:id" v="%d" />""" % way['OBJ_ID']
        print >> out, """</way>"""
        
        if split and out.tell() > 10000000:
            print >> out, """</osm>"""
            out.close()
            out = None

    if split and out is not None:
        print >> out, """</osm>"""
        out.close()
        out = None
            

def dumpRels(out, idsRels, tags):
    cur = con.cursor() 
    for rel in idsRels:
        # Print relation
        print >> out, """<relation id="-%d">""" % rel
        for row in cur.execute("""SELECT ID_RELATION, ID_AREA FROM AreaRelations
        WHERE ID_RELATION=?
        ORDER BY SEQ""", (rel,)):
            # Print members
            if isPolyClockwise(row['ID_AREA']):
                role = "outer"
            else:
                role = "inner"
            print >> out, """<member type="way" ref="-%d" role="%s" />""" % (1000000 + row['ID_AREA'], role)
            
        # Print tags
        for k, v in tags.iteritems():
            print >> out, """<tag k="%s" v="%s" />""" % (k, v)
        print >> out, """</relation>"""

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def findWaysAndRelsInRect(typ):
    cur = con.cursor()
    col = ("ID_%s" % typ).upper()
    idsLines = set() 
    for i in cur.execute("""SELECT DISTINCT ID_%s FROM %sNodes 
    INNER JOIN Nodes on %sNodes.ID_NODE=Nodes.ID_NODE 
    %s
    """ % (typ, typ, typ, whereSql)):
        idsLines.add(i[col])

    idsRels = set() 
    for i in cur.execute("""SELECT DISTINCT ID_RELATION FROM %sNodes 
    INNER JOIN Nodes on %sNodes.ID_NODE=Nodes.ID_NODE 
    INNER JOIN %sRelations on %sNodes.ID_%s=%sRelations.ID_%s 
    %s
    """ % (typ, typ, typ, typ, typ, typ, typ, whereSql)):
        idsRels.add(i['ID_RELATION'])

    idsInRels = set()
    if len(idsRels) > 0:
        for i in cur.execute("""SELECT DISTINCT %s FROM %sRelations 
        WHERE ID_RELATION IN(%s)""" % (col, typ, ",".join(map(lambda x: str(x), idsRels)))):
            idsInRels.add(i[col])
    
    idsLines = idsLines.difference(idsInRels)
    return (idsLines, idsInRels, idsRels)

def findFixedNodes():
    global fixedNodes
    fixedNodes = set()
    
    cur = con.cursor()
    cur.execute("""CREATE TABLE IF NOT EXISTS Fixed AS 
    SELECT * FROM (SELECT SUM(p) as pocet, ID_NODE FROM 
    (SELECT ID_NODE, count(*) AS p, 1 FROM AreaNodes GROUP BY ID_NODE
    UNION
    SELECT ID_NODE, count(*) AS p, 2 FROM LineNodes GROUP BY ID_NODE)
    GROUP BY ID_NODE
    ) WHERE pocet > 1""")
    
    for r in cur.execute("SELECT * FROM Fixed"):
        fixedNodes.add(r['ID_NODE'])
    print 'fixedNodes: ', len(fixedNodes)

def main():
    global con
    con = sqlite3.connect("vody.sqlite")
    con.row_factory = dict_factory

    con.isolation_level = None
    cur = con.cursor() 
    cur.execute("BEGIN")

    createIndex("LineNodes", "ID_LINE") 
    createIndex("AreaNodes", "ID_AREA")

    createIndex("LineNodes", "ID_NODE") 
    createIndex("AreaNodes", "ID_NODE")

    createIndex("LineRelations", "ID_LINE")
    createIndex("AreaRelations", "ID_AREA")

    createIndex("Lines", "ID_DIBAVOD") 
    createIndex("Areas", "ID_DIBAVOD") 

    findFixedNodes()
    
    dumpSeparated = False
    if dumpSeparated:
        (idsLines, idsInRels, idsRels) = findWaysAndRelsInRect("line")
        # We do not want lines with relations, so union...
        idsLines = idsLines.union(idsInRels)
        dumpWays("lines", idsLines, {'waterway' : 'river', 'source' : 'dibavod'}, False, 0)
    
        # At first we dump areas that do not have relation
        (idsAreas, idsInRels, idsRels) = findWaysAndRelsInRect("area")
        dumpWays("areas", idsAreas, {'natural' : 'water', 'source' : 'dibavod'}, True, 1000000)
    
        # At last we dump areas with relation into one file
        out = open("areasWithRelation.xml", "wb")
        print >> out, """<?xml version='1.0' encoding='UTF-8'?>
    <osm version='0.5' generator='vody'>"""
        dumpWays(out, idsInRels, {'natural' : 'water', 'source' : 'dibavod'}, True, 1000000)
        dumpRels(out, idsRels, {'source' : 'dibavod', 'type' : 'multipolygon'})
        print >> out, """</osm>"""
        out.close()
    else:
        out = open("vody.xml", "wb")
        print >> out, """<?xml version='1.0' encoding='UTF-8'?>
    <osm version='0.5' generator='vody'>"""
        (idsLines, idsInRels, idsRels) = findWaysAndRelsInRect("line")
        idsLines = idsLines.union(idsInRels)
        dumpWays(out, idsLines, {'waterway' : 'river', 'source' : 'dibavod'}, False, 0)
    
        (idsAreas, idsInRels, idsRels) = findWaysAndRelsInRect("area")
        dumpWays(out, idsAreas, {'natural' : 'water', 'source' : 'dibavod'}, True, 1000000)
    
        dumpWays(out, idsInRels, {'natural' : 'water', 'source' : 'dibavod'}, True, 1000000)
        dumpRels(out, idsRels, {'source' : 'dibavod', 'type' : 'multipolygon'})
        print >> out, """</osm>"""
        out.close()
        
    cur.execute("COMMIT")
    con.close()

if __name__ == '__main__':
    main()
    
    