import json
import psycopg2

def addPolygonToPostGIS(cursor, conn, geotagging_date, uid, fieldid, polygonid, name, phone_number, crop, season, area, added_by_phone_number, added_by_uid, points_arr):
   #print([geotagging_date, uid, fieldid, polygonid, name, phone_number, crop, season, area, added_by_phone_number, added_by_uid, points_arr])
   geotagging_date = add_comma(geotagging_date)
   uid = add_comma(uid)
   fieldid = add_comma(fieldid)
   polygonid = add_comma(polygonid)
   name = add_comma(name)
   phone_number = add_comma(phone_number)
   crop =add_comma(crop)
   season = add_comma(season)
   area = add_comma(area)
   added_by_phone_number = add_comma(added_by_phone_number)
   added_by_uid = add_comma(added_by_uid)
   #points_arr = add_comma(points_arr)
   #points_str = '\'POLYGON(' + points_arr + ')\''

   sql2 = '''CREATE TABLE POLYGONS(
   GEOTAGGING_DATE CHAR(100), UID CHAR(100), FIELD_ID CHAR(100), POLYGON_ID CHAR(100),
   NAME CHAR(100), PHONE_NUMBER CHAR(50), CROP CHAR(50), SEASON CHAR(50), AREA INT, ADDED_BY_PHONE_NUMBER CHAR(50),
   ADDED_BY_UID CHAR(100), BOUNDARY GEOMETRY(POLYGON, 4326));
   '''

   sql = '''CREATE EXTENSION postgis
   '''

   sql3 = '''INSERT INTO POLYGONS(GEOTAGGING_DATE, UID, FIELD_ID, POLYGON_ID,
   NAME, PHONE_NUMBER, CROP, SEASON, AREA, ADDED_BY_PHONE_NUMBER,
   ADDED_BY_UID, BOUNDARY) VALUES (''' + geotagging_date + ''',''' + uid +''',''' + fieldid+ ''','''+ polygonid + ''',''' + name + ''',''' + phone_number + ''',''' + crop + ''',''' + season +''',''' + area +  ''',''' + added_by_phone_number +  ''','''+ added_by_uid + ''',''' + make_sql_points_arr(points_arr) + ''')
   '''
   sql4 = '''INSERT INTO POLYGONS(GEOTAGGING_DATE, UID, FIELD_ID, POLYGON_ID,
   NAME, PHONE_NUMBER, CROP, SEASON, AREA, ADDED_BY_PHONE_NUMBER,
   ADDED_BY_UID, BOUNDARY) VALUES (2021-01-28,TCXcp5VIsfhHZrh0nm2VsgBtcGy2,1611730411677,1611807451303,1132041010,46210,apple,None,8519,None,None,POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))'''
   sql4 = '''INSERT INTO POLYGONS VALUES ('2021-01-28','TCXcp5VIsfhHZrh0nm2VsgBtcGy2','1611730411677','1611807451303','1132041010',46210,'apple','None',8519,'None','None','POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')'''
   #print(sql2)
   cursor.execute(sql3)
   # conn.commit()
   # #Closing the connection
   # conn.close()

def convert_fn_coords_to_array(coords, isPolygon=False):
   # outputs list of [lon,lat]
   result = []
   for i in range(len(coords)):
      coord = coords['a' if isPolygon and i==0 else f'P_{i}']
      result.append([coord['Longitude'], coord['Latitude']])
   return result

def get_db_cursor():
   conn = psycopg2.connect(
      database="gavl-postgis", user='postgres', password='P7^6fL9Q}h2v$UvA', host='34.68.167.51', port= '5432'
   )
   #Setting auto commit false
   conn.autocommit = True

   #Creating a cursor object using the cursor() method
   cursor = conn.cursor()

   return cursor

def get_json_from_file(path):
   with open(path, "r", encoding="utf-8") as f:
      return json.loads(f.read())
   
def get_coords_from_polyid(polyId, fixedData):
   # fixed data is supposed in the format features -> properties -> PolygonID,geometry -> coordinates
   for feature in fixedData["features"]:
      if feature["properties"]["PolygonID"] == polyId:
         return feature["geometry"]["coordinates"][0]
   return None

def update_invalid_polygons(fixedData):
   cursor = get_db_cursor()

   # get all invalid polygon_ids
   get_invalid_sql = "SELECT polygon_id FROM polygons WHERE is_invalid = true limit 2"
   cursor.execute(get_invalid_sql)
   invalidResult = cursor.fetchall()
   print(invalidResult)

   # get polygon fixedData and update in db
   for polyIdTuple in invalidResult:
      polyId = polyIdTuple[0]
      fixedCoordsArray = get_coords_from_polyid(polyId, fixedData)
      update_polygon_coords(cursor, polyId, fixedCoordsArray)

def update_polygon_coords(cursor, polygonId, coordsArray):
   sqlStr = f'''UPDATE polygons 
   SET boundary = {make_sql_points_arr(coordsArray)}, is_invalid = false
   WHERE polygon_id = {polygonId}'''
   cursor.execute(sqlStr)

def add_comma(variable):
   variable = '\'' + variable + '\''
   return variable

def make_sql_points_arr(points_arr):
   new_str = '('
   point_num = 0
   first_point = []
   for single_point in points_arr:
      if point_num == 0:
         first_point = single_point
     # print(['single_point', single_point])
      #if point_num < (len(points_arr)-1):
      new_str = new_str + str(single_point[0]) + " " + str(single_point[1]) + ", "
      #else:
      point_num = point_num + 1
   new_str = new_str + str(first_point[0]) + " " + str(first_point[1])
   new_str = new_str + ')'
   new_str = 'POLYGON(' + new_str + ')'
   new_str = add_comma(new_str)
   #print(new_str)
   return new_str


fixedDataPath = "temp_files/proj4_2.json"
fixedData = get_json_from_file(fixedDataPath)
update_invalid_polygons(fixedData)
print("all Done")