import os
import csv
import click
import time
from geopy import geocoders
from dotenv import load_dotenv
from util import *
load_dotenv()

@click.command()
def s2_geocode_spreadsheet():
  geolocator = geocoders.GoogleV3(os.getenv('MAPS_API_KEY'))

  worksheet = fetch_worksheet()
  keys, rows = fetch_google_sheet()
  valid_count = 0
  invalid_count = 0

  print("got {} rows".format(len(rows)))

  cname_lookup = {}
  for i, row in enumerate(rows):
    if len(row) == 6:
      cname, name, address, lat, lng, org_type = row
    elif len(row) == 7:
      cname, name, address, lat, lng, org_type, extra_address = row
    else:
      print("Weirdly formatted row {}".format(i))
      continue
    if cname == name or cname not in cname_lookup:
      cname_lookup[cname] = i

  # 0 cname 1 name 2 address 3 lat 4 lng 5 org_type
  for i, row in enumerate(rows):
    if len(row) == 6:
      cname, name, address, lat, lng, org_type = row
    elif len(row) == 7:
      cname, name, address, lat, lng, org_type, extra_address = row
    else:
      print("Weirdly formatted row {}: {} entries".format(i, len(row)))
      continue
    if lat and lng:
      continue
    c_row = rows[cname_lookup[cname]]
    if c_row[3] and c_row[4]:
      print("name {}, found cname: {}".format(name, cname))
      worksheet.update_cell(i+2, 3, c_row[2])
      worksheet.update_cell(i+2, 4, c_row[3])
      worksheet.update_cell(i+2, 5, c_row[4])
      continue
    if address:
      address_to_geocode = address
    elif name:
      address_to_geocode = name
    elif cname:
      address_to_geocode = cname

    if not address_to_geocode:
      continue

    print(address_to_geocode)
    location = geolocator.geocode(address_to_geocode)
    if location:
      print("{} found: {}".format(i+1, name))
      print(location.raw)
      worksheet.update_cell(i+2, 3, location.address)
      worksheet.update_cell(i+2, 4, location.latitude)
      worksheet.update_cell(i+2, 5, location.longitude)
      if address and address != location.address:
        worksheet.update_cell(i+2, 7, address)
      valid_count += 1
      row[2] = location.address
      row[3] = location.latitude
      row[4] = location.longitude
    else:
      print("{} not found: {}".format(i+1, address_to_geocode))
      invalid_count += 1
    time.sleep(2)

  print("geocoded {} addresses, {} found, {} not found".format(len(rows), valid_count, invalid_count))

if __name__ == '__main__':
  s2_geocode_spreadsheet()