import magnet as rip
import psycopg2
import googlemaps
from geomet import wkt
import json

gmaps = googlemaps.Client(key='AIzaSyAthjChcZHYkj8y7j5_drctNkvwBktzfj4')


n_clusters_civici = 4
n_clusters = 50 # 20
iterazioni = 300
init = 10



con = psycopg2.connect(database="carto", user="pcu_master", password="pcu_master", host="rdsppaldc.ci6trjgque6a.eu-central-1.rds.amazonaws.com", port="5432")
print("Database opened successfully")

cur = con.cursor()
cur.execute(''' select streetnum_id, prefix, street_name, address_num, comune, geometry, dx, dy
from enet.t_civici_dave
where cft = 'D61D159'
  ''')

rows = cur.fetchall()

dict = {}
dict["type"] = "FeatureCollection"
dict["features"] = []
lista = []

for row in rows:
    feature = {}

    x = wkt.loads(row[5])
    s = "(" + str(x["coordinates"][0]) + ", " + str(x["coordinates"][1]) + ")"

    feature["civico_id"] = int(row[0])
    feature["pref_via"] = row[1]
    feature["nome_via"] = row[2]
    feature["num_civico"] = row[3]
    feature["comune"] = row[4]
    feature["dx"] = row[6]
    feature["dy"] = row[7]
    lista.append([row[6], row[7]])
    feature["coord_wgs84"] = s


    dict["features"].append(feature)


print("Operation done successfully")
con.close()


city = row[4]
province = "RC"
aggiornati = 0 #se sono molti gli aggiornati, allora mando in postgres

for p in dict["features"]:
    print(p)
    if p["dx"] != 0:
        print("gia fatto")
        continue
    s = rip.pref_via(p['pref_via'])
    if s == "":
        continue
    else:
        aggiornati += 1
        via = s + p['nome_via'] + " " + str(p['num_civico']) + ", " + city + " " + province
        wgs84 = rip.parse_tuple(p['coord_wgs84'])
        lat = wgs84[1]
        lng = wgs84[0]
        geocode_result = gmaps.geocode(via)
        if geocode_result == []:
            continue
        delta_x = geocode_result[0]["geometry"]["location"]['lng'] - lng
        delta_y = geocode_result[0]["geometry"]["location"]['lat'] - lat

        print(delta_x, delta_y)

        p["dx"] = delta_x
        p["dy"] = delta_y



with open('civici.json', 'w') as fp:
    json.dump(dict, fp, indent=4)
print("Civici salvati")




if aggiornati > 300:
    lista = []
    print("Aggiornamento . . .", aggiornati)
    con = psycopg2.connect(database="carto", user="pcu_master", password="pcu_master",
                           host="rdsppaldc.ci6trjgque6a.eu-central-1.rds.amazonaws.com", port="5432")
    cur = con.cursor()
    sql_update_query = """ Update enet.t_civici_dave set dx = %s, dy = %s where streetnum_id = %s """

    for p in dict["features"]:
        cur.execute(sql_update_query, (p["dx"], p["dy"], p["civico_id"]))
        con.commit()
        count = cur.rowcount
        print(count, "Record Updated successfully")
        lista.append([p["dx"], p["dy"]])
    con.close()

else:
    print("inutile")



cluster = rip.find_delta_k_means(lista, n_clusters, iterazioni, init)



print("Aggiornamento rete. . .")
con = psycopg2.connect(database="carto", user="pcu_master", password="pcu_master",
                       host="rdsppaldc.ci6trjgque6a.eu-central-1.rds.amazonaws.com", port="5432")
cur = con.cursor()
sql_update_query = """ Update enet.t_ramo_bt_dave set newgeom = ST_Translate(geom, %s, %s) where cft = 'D61D159' """
cur.execute(sql_update_query, (cluster[0], cluster[1]))
con.commit()
count = cur.rowcount
print(count, "Record Updated successfully")
con.close()












'''


city = "Reggio Calabria"
province = "RC"

civici = "coords/civici/" + city + ".json"
file_in = "coords/AGUI/" + city + ".json"
file_out = "coords/NEW/" + city + ".json"
file_cluster = "coords/cluster/" + city + ".json"

html_agui = "build/" + city + "/old.html"
html_new = "build/" + city + "/new.html"
html_civici = "build/" + city + "/civici.html"
html_cluster = "build/" + city + "/civici_cluster.html"
html_linee = "build/" + city + "/linee_cluster.html"
html_new_cluster = "build/" + city + "/new_cluster.html"

delta = "delta/" + city

n_clusters_civici = 4
n_clusters = 50  # 20
iterazioni = 300
init = 10

colors = []
for i in range(n_clusters):
    colors.append(rip.random_color())

# clustering dei civici e della linea
print("Clustering civici")
lista_civici = rip.plot_civici(civici, html_civici, city, province)
clustered_list, centroids, cluster_assignments = rip.civici_k_means(lista_civici, n_clusters_civici, iterazioni, init)
rip.plot_cluster_civici(clustered_list, html_cluster, city, province, n_clusters_civici, colors)
clustered_lines, line_assignment = rip.cluster_assignment(file_in, centroids, n_clusters_civici)
rip.plot_cluster_linee(clustered_lines, html_linee, city, province, n_clusters_civici, colors)

rip.plot_on_map(file_in, html_agui, city, province)

if rip.exists(delta):
    print("Lista dei delta già presente")
    lista = rip.load_list(delta)


else:
    print("Lista dei delta da calcolare")
    # rip.plot_on_map(file_in, html_agui, city, province)
    lista = rip.create_delta_list(civici, city, province)
    print("Lista creata")
    rip.save_list(lista, delta)

print("elementi da clusterizzare: " + str(len(lista)))
cluster = rip.find_delta_k_means(lista, n_clusters, iterazioni, init)
rip.apply_delta(file_in, file_out, cluster[0], cluster[1])
rip.plot_on_map(file_out, html_new, city, province)

print("CLUSTER = " + str(cluster))

################# NUOVA PARTE
cluster_delta = []
for i in range(n_clusters_civici):
    cluster_delta.append([])

size_for_range = len(cluster_assignments)
if size_for_range > len(lista):
    size_for_range = len(lista)

if len(lista) != len(cluster_assignments):
    print("DIVERSI!")
else:
    print("UGUALI!")

# smistaggio da rivedere
for i in range(size_for_range):
    cluster_delta[cluster_assignments[i]].append(lista[i])

deltas = []
for i in range(len(cluster_delta)):
    c = rip.best_number(len(cluster_delta[i]))
    cluster = rip.find_delta_k_means(cluster_delta[i], c, iterazioni, init)
    deltas.append(cluster)

rip.apply_deltas(file_in, file_cluster, deltas, line_assignment)
rip.plot_on_map(file_cluster, html_new_cluster, city, province, "black")
# rip.plot_cluster_linee(clustered_lines,html_new_cluster,city, province, n_clusters_civici, colors, flag=False)



 line assignment mi serve per la lista dei delta da suddividere dai delta calcolati

creo un elenco di n_cluster delta, per ognuno di essi calcolo il delta ottimo con kmeans
 lo applico su un singolo cluster
nuova funzione apply delta per ritornare il json



'''


################



import googlemaps
import json
import ast
import numpy as np
from matplotlib import pyplot as plt
from sklearn.cluster import KMeans
from collections import Counter
import folium
import pickle


gmaps = googlemaps.Client(key='AIzaSyAthjChcZHYkj8y7j5_drctNkvwBktzfj4')


def getCoords(city, province):
    geocode_result = gmaps.geocode(city + ", " + province + " Italia")
    return (geocode_result[0]["geometry"]["location"]['lat'], geocode_result[0]["geometry"]["location"]['lng'])

def inserisciMarker(mappa, lat, lng, popup, icon, color):
    folium.Marker(location=[lat, lng], tooltip=popup, icon=folium.Icon(icon=icon, color=color)).add_to(mappa)


def inserisciLinea(mappa, points, color, w, opacity, tooltip):
    folium.PolyLine(points, tooltip=tooltip, color=color, weight=w, opacity=opacity).add_to(mappa)


def inserisciCerchio(mappa, lat, lng, radius):
    folium.CircleMarker([lat, lng], radius=radius * 10 / 2, fill=True, opacity=0.1).add_to(mappa)


def inserisciPunto(mappa, lat, lng, txt):
    folium.CircleMarker([lat, lng], radius=5, fill=True, color="black",opacity=0.2, popup=txt).add_to(mappa)

def inserisciPuntoCustom(mappa, lat, lng, txt, color):
    folium.CircleMarker([lat, lng], radius=5, fill=True, color=color,opacity=0.8, popup=txt).add_to(mappa)

def inserisciPuntoR(mappa, lat, lng, txt):
    folium.CircleMarker([lat, lng], radius=5, fill=True, color="red", popup=txt).add_to(mappa)



def random_color():
    r = np.random.randint(255)
    g = np.random.randint(255)
    b = np.random.randint(0,100)
    rgb = (r,g,b)
    return '#%02x%02x%02x' % rgb


def disegnaLinee(lista,m,color):
    for i in range(len(lista) - 1):
        l = []
        l.append((lista[i][1], lista[i][0]))
        l.append((lista[i + 1][1], lista[i + 1][0]))
        inserisciLinea(m, l, color, 2, 0.8, "")

def parse_tuple(string):
    try:
        s = ast.literal_eval(str(string))
        if type(s) == tuple:
            return s
        return
    except:
        return

def pref_via(s):
    if "STR" in s:
        return ""
    if "PZA" in s:
        return "Piazza"
    if "CON" in s:
        return "Contrada"
    return "Via"



def create_delta_list(file, city, province):

    with open(file) as json_file:
        json_data = json.load(json_file)

        lista = []

        for p in json_data['features']:
            s = pref_via(p['pref_via'])
            if s == "":
                continue
            else:
                via = s + p['nome_via'] + " " + str(p['num_civico']) + ", " + city + " " + province
                wgs84 = parse_tuple(p['coord_wgs84'])
                lat = wgs84[1]
                lng = wgs84[0]
                geocode_result = gmaps.geocode(via)
                if geocode_result == []:
                    continue
                delta_x = geocode_result[0]["geometry"]["location"]['lng'] - lng
                delta_y = geocode_result[0]["geometry"]["location"]['lat'] - lat
                lista.append([delta_x, delta_y])
                #print(delta_x, delta_y)

    return lista

def save_list(lista, outfile):
    with open(outfile, 'wb') as fp:
        pickle.dump(lista, fp)

def load_list(file):
    with open(file, 'rb') as fp:
        itemlist = pickle.load(fp)
    return itemlist

def exists(file):
    try:
        f = open(file)
        flag = True
    except IOError:
        flag = False
    finally:
        return flag

def find_delta_k_means(lista, n_clusters, iterazioni, init):
    X = np.array(lista)
    #print(X)

    kmeans = KMeans(n_clusters=n_clusters, init='k-means++', max_iter=iterazioni, n_init=init, random_state=0)
    pred_y = kmeans.fit_predict(X)

    # plt.scatter(X[:, 0], X[:, 1])
    # plt.scatter(kmeans.cluster_centers_[:, 0], kmeans.cluster_centers_[:, 1], s=300, c='red')
    # plt.show()

    #print(pred_y)
    l = list(pred_y)

    data = Counter(l)
    #print(data.most_common(1))
    most_common = data.most_common(1)[0]
    cluster = most_common[0]
    lunghezza = most_common[1]
    #print(cluster)
    print(lunghezza)

    print(kmeans.cluster_centers_[cluster])

    return kmeans.cluster_centers_[cluster]

def civici_k_means(lista, n_clusters, iterazioni, init):
    X = np.array(lista)
    kmeans = KMeans(n_clusters=n_clusters, init='k-means++', max_iter=iterazioni, n_init=init, random_state=0)
    pred_y = kmeans.fit_predict(X)

    # plt.scatter(X[:, 0], X[:, 1])
    # plt.scatter(kmeans.cluster_centers_[:, 0], kmeans.cluster_centers_[:, 1], s=300, c='red')
    # plt.show()

    centroids = kmeans.cluster_centers_
    print(centroids)

    size = len(lista)
    clustered_list = []

    for e in range(n_clusters):
        clustered_list.append([])

    cluster_assignment = []
    for e in range(size):
        clustered_list[pred_y[e]].append(lista[e])
        cluster_assignment.append(pred_y[e])

    return clustered_list, centroids, cluster_assignment

def best_number(n):
    print("Cluster = " + str(n))
    if n < 20:
        return 6
    if n < 50:
        return 8
    if n < 180:
        return 10
    if n < 300:
        return 15
    if n < 500:
        return 25
    return 50

def kmeans_assignment(centroids, points):
    points = np.array(points)

    num_centroids, dim = centroids.shape
    num_points, _ = points.shape

    # Tile and reshape both arrays into `[num_points, num_centroids, dim]`.
    centroids = np.tile(centroids, [num_points, 1]).reshape([num_points, num_centroids, dim])
    points = np.tile(points, [1, num_centroids]).reshape([num_points, num_centroids, dim])

    # Compute all distances (for all points and all centroids) at once and
    # select the min centroid for each point.
    distances = np.sum(np.square(centroids - points), axis=2)
    return np.argmin(distances, axis=1)

def cluster_assignment(file, centroids, n_clusters):

    with open(file) as json_file:
        json_data = json.load(json_file)
        #print(json_data)

    clustered_list = []
    line_assignment = []
    for e in range(n_clusters):
        clustered_list.append([])

    for p in json_data['features']:
        if p["geometry"]["type"] == "LineString":
            x = kmeans_assignment(centroids,p["geometry"]["coordinates"])
            clustered_list[np.bincount(x).argmax()].append(p["geometry"]["coordinates"])
            line_assignment.append(np.bincount(x).argmax())

    return clustered_list,line_assignment



def apply_delta(file_in, file_out, delta_x, delta_y):
    with open(file_in) as json_file:
        json_data = json.load(json_file)
        #print(json_data)
        for p in json_data['features']:
            if p["geometry"]["type"] == "Point":
                p["geometry"]["coordinates"][0] += delta_x
                p["geometry"]["coordinates"][1] += delta_y
            elif p["geometry"]["type"] == "LineString":
                for c in p["geometry"]["coordinates"]:
                    c[0] += delta_x
                    c[1] += delta_y

        with open(file_out, 'w') as outfile:
            json.dump(json_data, outfile, sort_keys=True, indent=4,
                      ensure_ascii=False)

def apply_deltas(file_in, file_out, deltas, line_assignment):
    with open(file_in) as json_file:
        json_data = json.load(json_file)
        i = 0
        for p in json_data['features']:

            delta_x = deltas[line_assignment[i]][0]
            delta_y = deltas[line_assignment[i]][1]

            if p["geometry"]["type"] == "Point":
                p["geometry"]["coordinates"][0] += delta_x
                p["geometry"]["coordinates"][1] += delta_y
            elif p["geometry"]["type"] == "LineString":
                for c in p["geometry"]["coordinates"]:
                    c[0] += delta_x
                    c[1] += delta_y
            i += 1

        with open(file_out, 'w') as outfile:
            json.dump(json_data, outfile, sort_keys=True, indent=4,
                      ensure_ascii=False)

def plot_on_map(file,file_out,city, province,color="blue"):

    coords = getCoords(city, province)
    m = folium.Map(location=[coords[0], coords[1]], tiles='OpenStreetMap', zoom_start=16)

    with open(file) as json_file:
        json_data = json.load(json_file)
        #print(json_data)

        for p in json_data['features']:
            if p["geometry"]["type"] == "Point":
                inserisciPunto(m, p["geometry"]["coordinates"][1], p["geometry"]["coordinates"][0], "test")
            elif p["geometry"]["type"] == "LineString":
                disegnaLinee(p["geometry"]["coordinates"], m,color)

        m.save(file_out)


def plot_civici(file,file_out,city, province):

    coords = getCoords(city, province)
    m = folium.Map(location=[coords[0], coords[1]], tiles='OpenStreetMap', zoom_start=16)
    l = []
    with open(file) as json_file:
        json_data = json.load(json_file)

        for p in json_data['features']:
            punto = parse_tuple(p["coord_wgs84"])
            inserisciPunto(m, punto[1], punto[0], "test")
            l.append(punto)

        m.save(file_out)


    return l


def plot_cluster_civici(lista,file_out,city, province, n, colors):

    coords = getCoords(city, province)
    m = folium.Map(location=[coords[0], coords[1]], tiles='OpenStreetMap', zoom_start=16)


    for i in range(n):
        for e in lista[i]:
            inserisciPuntoCustom(m,e[1],e[0], "cluster " + str(i), colors[i])

    m.save(file_out)

def swap(l):
    for e in l:
        temp = e[0]
        e[0] = e[1]
        e[1] = temp
    return l

def plot_cluster_linee(lista,file_out,city, province, n, colors, flag=True):

    coords = getCoords(city, province)
    m = folium.Map(location=[coords[0], coords[1]], tiles='OpenStreetMap', zoom_start=16)

    for i in range(n):
        for e in lista[i]:
            if flag:
                e = swap(e)
            inserisciLinea(m, e, colors[i], 2, 0.7, "cluster " + str(i))

    m.save(file_out)