import csv
import logging
import psycopg2 as postgres
from psycopg2.extras import RealDictCursor
import geocoder

weed_cols = ['case_no', 'case_type', 'status', 'opened_date', 'addr_no', 'addr_dir', 'addr_street_name', 'addr_street_type', 'application_name', 'desc', 'assigned_to_staff', 'business_name', 'active_task']

currentService = 0

logging.basicConfig(filename='weed.log',level=logging.DEBUG)

con = postgres.connect('dbname=civic user=postgres password=calvin')
cur = con.cursor(cursor_factory=RealDictCursor)

with open("data/weed.csv", "r") as weed_file:
    weed_reader = csv.DictReader(weed_file, weed_cols)

    for entry in weed_reader:
        address = "{} {} {} {} Indianapolis, IN, United States".format(entry['addr_no'], entry['addr_dir'], entry['addr_street_name'], entry['addr_street_type'])
        logging.info("Adding violation for address {}.".format(address))

        try:
            if currentService == 0:
                code = geocoder.google(address)
            elif currentService == 1:
                code = geocoder.bing(address)
            elif currentService == 2:
                code = geocoder.osm(address)
            elif currentService == 3:
                code = geocoder.here(address)
            elif currentService == 4:
                code = geocoder.tomtom(address)
            elif currentService == 5:
                code = geocoder.mapquest(address)

            lat, lng = code.latlng
        
        except ValueError:  # Request failed
            logging.error("Geocoding failed for address {}.".format(address))
            currentService += 1
            currentService %= 6
            continue
        
        cur.execute("INSERT INTO violations (lattitude, longitude, street_address, date, violation_type_id) VALUES (%s, %s, %s, %s, 1)", (lat, lng, address, entry['opened_date']))
        con.commit()
        logging.info("Adding violation for address {}.".format(address))
