#!/usr/bin/env python3

"""
Script to insert the country data in the database

IT HAS TO BE RUN INSIDE ./manage.py shell
"""
import csv 
import os
import time
from geopy.geocoders import Nominatim

tmp = os.path.join(os.path.realpath(__file__), '../../assets/destinations.csv')
destinations_path = os.path.abspath(tmp)

tmp = os.path.join(os.path.realpath(__file__), '../../assets/destinations_extracted.csv')
destinations_extracted_path = os.path.abspath(tmp)

if not os.path.isfile(destinations_path):
    print(destinations_path)
    raise Exception("Missing file containing country data")

with open(destinations_path, 'rt') as input:
    with open(destinations_extracted_path, 'w') as output:
        print("ini")
        reader = csv.reader(input)
        spamwriter = csv.writer(output, quoting=csv.QUOTE_NONNUMERIC)
        geolocator = Nominatim()

        failed = []
        i = 0
        for row in reader:
            # handle the header
            if i == 0:
                header = ['University', 'lat', 'lon']
                spamwriter.writerow(header)
                i += 1
            else:
                query = row[2]#+ ', ' + row[1] + ', ' + row[0]
                
                while True:
                    try:
                        location = geolocator.geocode(query)
                        break
                    except:
                        print("error during query, retrying")
                        time.sleep(1)

                print(location)
                if location is not None:
                    spamwriter.writerow([row[2], location.latitude, location.longitude])
                else:
                    failed.append(query)
        
        print(failed)