"""
#ШАБЛОН ПРОЕКТИРОВАНИЯ "НАБОР", (Алекс Мартелли - «Python Cookbook»)
class Bunch(dict):
	def __init__(self, *args, **kwds):
		super(Bunch, self).__init__(*args, **kwds)
		self.__dict__ = self
T = Bunch

x = Bunch(name="Jayne Cobb", position="PR")
print(x.name)

t = T(left=T(left="a", right="b"), right=T(left="c"))
print(t.left)
print(t.left.right)
print(t['left']['right'])
print("left" in t.right)
print("right" in t.right)
"""

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
import random
import matplotlib.pyplot as plt
import networkx as nx
from networkx.drawing.nx_agraph import write_dot, graphviz_layout
import warnings
warnings.filterwarnings("ignore", category=UserWarning)

dbconfig = read_db_config()

#Здесь 'a' выступает в роли количества вершин, которое будет в нашем дереве
def random_tree(a):
    cursor.execute("DELETE FROM tree")
    cursor.execute("INSERT INTO tree (ancestor, descendant) VALUES (1, 1)")
    for i in range(1, a):
        insert(random.randint(1, i))

def deletion_without_subtree(a):
    cursor.execute("DELETE FROM tree WHERE ancestor = " + str(a) + " OR descendant = " + str(a))

def deletion_with_subtree(a):
    cursor.execute("SELECT descendant FROM tree WHERE ancestor = " + str(a))
    des = cursor.fetchone()
    y = []
    while des is not None:
        y.append(des[0])
        des = cursor.fetchone()
    i = len(y)
    #while i > 0:
    #    deletion_without_subtree(y[i-1])
    #    conn.commit()
    #    i-=1
    for i in range(0, len(y)):
        deletion_without_subtree(y[i])

#У этой функции переменная 'а' обозначает предка нашего нового листа
def insert(a):
    cursor.execute("SELECT ancestor FROM tree WHERE descendant = " + str(a))
    anc = cursor.fetchone()
    x = []
    while anc is not None:
        x.append(anc[0])
        anc = cursor.fetchone()

    cursor.execute("SELECT MAX(ancestor) FROM tree")
    new_number = cursor.fetchone()[0] + 1

    x.append(new_number)

    i = 0
    insert_request = "INSERT INTO tree (ancestor, descendant) VALUES "
    while i < len(x):
        insert_request += "(" + str(x[i]) + ", " + str(new_number) + "),"
        i += 1
    cursor.execute(insert_request[:-1])

#'a' - какую вершину берем для переноса, 'b' - под какую вершину переносим

def transfering_without_subtree(a, b):
    cursor.execute("DELETE FROM tree WHERE ancestor != descendant AND (ancestor = " + str(a) + " OR descendant = " + str(a) + ")")

    cursor.execute("SELECT ancestor FROM tree WHERE descendant = " + str(b))
    anc = cursor.fetchone()
    x = []
    while anc is not None:
        x.append(anc[0])
        anc = cursor.fetchone()

    i = 0
    insert_request = "INSERT INTO tree (ancestor, descendant) VALUES "
    while i < len(x):
        insert_request += "(" + str(x[i]) + ", " + str(a) + "),"
        i += 1
    cursor.execute(insert_request[:-1])



#'a' - какую вершину берем для переноса, 'b' - под какую вершину переносим
def transfering_with_subrtee(a, b):
    s1 = "DELETE FROM tree WHERE ancestor IN (SELECT ancestor FROM tree WHERE descendant = " + str(a) +" AND ancestor != " + str(a) + ") AND descendant IN (SELECT descendant FROM tree WHERE ancestor = " + str(a) + ")"
    cursor.execute(s1)

    cursor.execute("SELECT ancestor FROM tree WHERE descendant = " + str(b))
    anc = cursor.fetchone()
    x = []
    while anc is not None:
        x.append(anc[0])
        anc = cursor.fetchone()

    cursor.execute("SELECT descendant FROM tree WHERE ancestor = " + str(a))
    des = cursor.fetchone()
    y = []
    while des is not None:
        y.append(des[0])
        des = cursor.fetchone()

    i = 0
    j = 0

    insert_request = "INSERT INTO tree (ancestor, descendant) VALUES "
    for i in range(len(y)):
        for j in range(len(x)):
            insert_request += "(" + str(x[i]) + ", " + str(y[j]) + "),"

    cursor.execute(insert_request[:-1])




def query_with_fetchone():
    cursor.execute("SELECT * FROM tree")
    row = cursor.fetchone()
    while row is not None:
        print(row)
        row = cursor.fetchone()

def query_with_fetchone_2():
    users = {}
    cursor.execute("SELECT * FROM tree")
    row = cursor.fetchone()
    while row is not None:
        row[0]
        row = cursor.fetchone()



if __name__ == '__main__':

    try:
        conn = MySQLConnection(**dbconfig)
        cursor = conn.cursor()
        #transfering_with_subrtee(5, 3)
        #create_my_tree_again()
        #transfering_without_subtree(5, 3)
        random_tree(10)
        G = nx.DiGraph()
        anc = []

        cursor.execute("SELECT DISTINCT ancestor FROM tree")
        row = cursor.fetchone()
        while row is not None:
            anc.append(row[0])
            G.add_node(str(row[0]))
            row = cursor.fetchone()

        data = []

        cursor.execute("SELECT * FROM tree")
        row = cursor.fetchone()
        while row is not None:
            data.append(row)
            row = cursor.fetchone()

        INF = -10000

        mtrx = {}

        for i in anc:
            for j in anc:
                if i in mtrx:
                    mtrx[i][j] = INF
                else:
                    q = {i: {j: INF}}
                    mtrx.update(q)

        for elem in data:
            if(elem[0] == elem[1]):
                if(elem[0] != anc[0]):
                    mtrx[elem[0]][elem[1]] = INF
                else:
                    mtrx[elem[0]][elem[1]] = 0
            else:
                mtrx[elem[0]][elem[1]] = 1

        for k in range(1, len(anc)+1):
            for i in anc:
                for j in anc:
                    if(mtrx[i][j] < (mtrx[i][k] + mtrx[k][j])):
                        mtrx[i][j] = mtrx[i][k] + mtrx[k][j]

        for elem in data:
            if(mtrx[elem[0]][elem[1]] == 1):
                G.add_edge(str(elem[0]), str(elem[1]))

        plt.title('my_tree')
        pos = graphviz_layout(G, prog='dot')
        nx.draw(G, pos, with_labels=True, arrows=True)
        plt.savefig('nx_test.png')
        plt.show()

        #query_with_fetchone()
        conn.commit()


    except Error as e:
        print(e)

    finally:
        cursor.close()
        conn.close()