#!E:\Pycharm Projects\Waytous
# -*- coding: utf-8 -*-
# @Time : 2022/9/7 9:56
# @Author : Opfer
# @Site :
# @File : topo_update.py    
# @Software: PyCharm

# 导入包

import os
from graph.graph_load import *
import networkx as nx
import matplotlib.pyplot as plt
import uuid
import sqlite3


def topo_graph_update():

    # 拓扑图生成与写入

    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    print("数据库打开成功")

    cursor = c.execute("DROP TABLE GRAPH_EDGE")
    conn.commit()

    cursor = c.execute("DROP TABLE GRAPH_NODE")
    conn.commit()

    cursor = c.execute("CREATE TABLE GRAPH_EDGE(id VARCHAR(30) PRIMARY KEY, startNodeId VARCHAR(30), endNodeId VARCHAR(30), distance FLOAT, type INT, lanes_sql VARCHAR(100))")
    conn.commit()

    cursor = c.execute("CREATE TABLE GRAPH_NODE(id VARCHAR(30) PRIMARY KEY, nodeId VARCHAR(30), name VARCHAR(30))")
    conn.commit()

    # 清空数据库数据

    cursor = c.execute("DELETE FROM GRAPH_EDGE")
    conn.commit()

    cursor = c.execute("DELETE FROM GRAPH_NODE")

    conn.commit()

    # 生成拓扑图

    topo = Topo()
    topo.generate_topo_graph()

    # 获取装载路径拓扑图

    load_G = topo.get_load_G()

    # 装载路径拓扑图数据写入数据库

    for item in load_G.edges.data():
        print("item load_G-edge")
        print(item)
        id = str(uuid.uuid4())
        startNodeId = item[0]
        endNodeId = item[1]
        distance = item[2]['real_distance']
        lanes = item[2]['lane']

        lanes_sql = str(" ".join(lanes))

        sql = "INSERT INTO GRAPH_EDGE VALUES ('%s', '%s', '%s', '%f', 1, '%s')" % (
        id, startNodeId, endNodeId, distance, lanes_sql)

        cursor = c.execute(sql)

        conn.commit()

    # 装载节点写入数据库

    for item in load_G.nodes.data():
        print("item load_G-nodes")
        print(item)
        id = str(uuid.uuid4())
        nodeId = item[0]
        if item[1] != {} and item[1]['name'] != None:
            name = item[1]['name']
        else:
            name = 'None'

        sql = "INSERT INTO GRAPH_NODE VALUES ('%s' ,'%s', '%s')" % (id, nodeId, name)

        cursor = c.execute(sql)

        conn.commit()

    # 获取卸载路径拓扑图

    unload_G = topo.get_unload_G()

    # 卸载路径拓扑图写入数据库

    for item in unload_G.edges.data():
        print("item unload_G-edge")
        print(item)
        id = str(uuid.uuid4())
        startNodeId = item[0]
        endNodeId = item[1]
        distance = item[2]['real_distance']
        lanes = item[2]['lane']

        lanes_sql = str(" ".join(lanes))

        sql = "INSERT INTO GRAPH_EDGE VALUES ('%s', '%s', '%s', '%f', 2, '%s')" % (
        id, startNodeId, endNodeId, distance, lanes_sql)

        cursor = c.execute(sql)

        conn.commit()

    # 卸载节点写入数据库

    for item in unload_G.nodes.data():
        print("item unload_G-nodes")
        print(item)
        id = str(uuid.uuid4())
        nodeId = item[0]
        if item[1] != {} and item[1]['name'] != None:
            name = item[1]['name']
        else:
            name = 'None'

        sql = "INSERT INTO GRAPH_NODE VALUES ('%s', '%s', '%s')" % (id, nodeId, name)

        cursor = c.execute(sql)

        conn.commit()