

import os
import glob
import numpy as np
import pandas as pd
import geopandas as gpd
import db_connect
import sqlalchemy as sal
import csv
import psycopg2
os.chdir('D:\\ENEA_CAS_WORK\\Catania_RAFAEL\\viasat_data\\vulnerability_by_days')
cwd = os.getcwd()

# connect to new DB to be populated with Viasat data after route-check
conn_HAIG = db_connect.connect_HAIG_Viasat_CT()
cur_HAIG = conn_HAIG.cursor()


#### check how many CELL (by ID) we have ######################

## get all ID terminal of Viasat data
# all_VULNERABILTY_IDs = pd.read_sql_query(
#     ''' SELECT "CELL"
#         FROM "vulnerability_all_OD_AUGUST_2019" ''', conn_HAIG)

# # get all ID terminal of Viasat data
# all_VULNERABILTY_IDs = pd.read_sql_query(
#     ''' SELECT "CELL"
#         FROM "vulnerability_all_OD_FEBRUARY_2019_NEW" ''', conn_HAIG)

# get all ID terminal of Viasat data
# all_VULNERABILTY_IDs = pd.read_sql_query(
#    ''' SELECT "CELL"
#        FROM "vulnerability_4seasons_2019_NEW" ''', conn_HAIG)

all_VULNERABILTY_IDs = pd.read_sql_query(
    ''' SELECT "CELL"
        FROM "vulnerability_EVENING_PEAK_AUGUST_2019" ''', conn_HAIG)

vulnerabilty_2019 = pd.read_sql_query('''
                       SELECT *
                           FROM "vulnerability_EVENING_PEAK_AUGUST_2019" ''', conn_HAIG)


# make a list of all unique trips
CELL_IDs = list(all_VULNERABILTY_IDs.CELL.unique())

print(len(CELL_IDs))
print("CELL number:", len(CELL_IDs))


## reload 'all_CELL_IDs' as list

# elements = np.load('elements_400m_AUGUST.npy',allow_pickle='TRUE').item()
# elements = np.load('elements_400m_FEBRUARY.npy',allow_pickle='TRUE').item()
# elements = np.load('elements_400m_Feb_May_Aug_Nov.npy',allow_pickle='TRUE').item()
os.chdir('D:\\ENEA_CAS_WORK\\Catania_RAFAEL\\viasat_data\\vulnerability_by_days')
cwd = os.getcwd()
elements = np.load('EVENING_PEAK_AUGUST_elements_400m.npy',allow_pickle='TRUE').item()

all_CELL_ID = list(elements.keys())
print(len(all_CELL_ID))

## make difference between all idterm and matched idterms
all_CELL_ID_DIFF = list(set(all_CELL_ID) - set(CELL_IDs))
print(len(all_CELL_ID_DIFF))

# ## save 'all_ID_TRACKS' as list
with open("D:/ENEA_CAS_WORK/Catania_RAFAEL/viasat_data/element_list_new.txt", "w") as file:
    file.write(str(all_CELL_ID_DIFF))



month = 'AUGUST'
day = 'EVENING_PEAK'
MONTH = str(month)
DAY = str(day)
path = 'D:/ENEA_CAS_WORK/Catania_RAFAEL/viasat_data/vulnerability_by_days/'
TIME_EDGES = gpd.read_file(path + DAY.strip() + "_" + MONTH.strip() + "_2019_sottorete_speed_counts_all_vehicles.geojson")
TIME_EDGES.plot()