import numpy as np
import psycopg2
import pandas as pd

from sklearn.cluster import DBSCAN
from sklearn import metrics
from sklearn.datasets import make_blobs
from sklearn.preprocessing import StandardScaler


# #############################################################################
# Generate sample data
# centers = [[1, 1], [-1, -1], [1, -1]]
# X, labels_true = make_blobs(n_samples=750, centers=centers, cluster_std=0.4,
#                             random_state=0)

# X = StandardScaler().fit_transform(X)
# print(X)
####################################################
X = pd.read_csv('./test.csv')

# Dropping the Transaction_date column from the data
X = X.drop('Transaction_date', axis = 1)
X = X.drop('Product', axis = 1)
X = X.drop('Price', axis = 1)
X = X.drop('Payment_Type', axis = 1)
X = X.drop('Name', axis = 1)
X = X.drop('City', axis = 1)
X = X.drop('State', axis = 1)
X = X.drop('Country', axis = 1)
X = X.drop('Account_Created', axis = 1)
X = X.drop('Last_Login', axis = 1)
X = X.drop('US Zip', axis = 1)
# Handling the missing values
X.fillna(method ='ffill', inplace = True)

X=X.values
####################################################
##get values from database

# connection = psycopg2.connect(user="postgres",
#                                   password="postgres",
#                                   host="172.17.0.2",
#                                   port="5432",
#                                   database="roadmakerDB")
# cursor = connection.cursor()
# print("Table Before updating record ")
# sql_select_query = """SELECT latitude,longitude FROM hash_location Where latitude!='not found'"""
# cursor.execute(sql_select_query)
# record = cursor.fetchall()
# print(record)
# X=[[float(x[0]),float(x[1])] for x in record]
# print(X)
# # X=pd.DataFrame(X)
# print("WWWWW")
# print(X)
# print("WWWWW")
# exit()

# #############################################################################
# Compute DBSCAN
db = DBSCAN(eps=0.00000001, min_samples=2).fit(X)#0.001264,3
core_samples_mask = np.zeros_like(db.labels_, dtype=bool)
core_samples_mask[db.core_sample_indices_] = True
labels = db.labels_
labels_true = labels
# Number of clusters in labels, ignoring noise if present.
n_clusters_ = len(set(labels)) - (1 if -1 in labels else 0)
n_noise_ = list(labels).count(-1)
for x in range(len(labels)):
  print(labels[x],X[1])
print('Estimated number of clusters: %d' % n_clusters_)
print('Estimated number of noise points: %d' % n_noise_)
# print("Homogeneity: %0.3f" % metrics.homogeneity_score(labels_true, labels))
# print("Completeness: %0.3f" % metrics.completeness_score(labels_true, labels))
# print("V-measure: %0.3f" % metrics.v_measure_score(labels_true, labels))
# print("Adjusted Rand Index: %0.3f"
#       % metrics.adjusted_rand_score(labels_true, labels))
# print("Adjusted Mutual Information: %0.3f"
#       % metrics.adjusted_mutual_info_score(labels_true, labels))
print("Silhouette Coefficient: %0.3f"
      % metrics.silhouette_score(X, labels))

# # #############################################################################
# # Plot result
# import matplotlib.pyplot as plt

# # Black removed and is used for noise instead.
# unique_labels = set(labels)
# colors = [plt.cm.Spectral(each)
#           for each in np.linspace(0, 1, len(unique_labels))]
# for k, col in zip(unique_labels, colors):
#     if k == -1:
#         # Black used for noise.
#         col = [0, 0, 0, 1]

#     class_member_mask = (labels == k)

#     xy = X[class_member_mask & core_samples_mask]
#     plt.plot(xy[:, 0], xy[:, 1], 'o', markerfacecolor=tuple(col),
#              markeredgecolor='k', markersize=14)

#     xy = X[class_member_mask & ~core_samples_mask]
#     plt.plot(xy[:, 0], xy[:, 1], 'o', markerfacecolor=tuple(col),
#              markeredgecolor='k', markersize=6)

# plt.title('Estimated number of clusters: %d' % n_clusters_)
# plt.show()