#!env/bin/python
import csv, os, urllib2
import xml.etree.ElementTree as ET
from app import db
from app.models import Route
from bs4 import BeautifulSoup
from config_local import ROUTES_FILE
from zipfile import ZipFile

# Define URLs and file name
url = 'https://data.cityofchicago.org/download/rytz-fq6y/application/zip'

def download_routes(url):
    # Download KMZ file from Data Portal
    print "Downloading %s" % ROUTES_FILE
    f = urllib2.urlopen(url)
    data = f.read()
    with open(ROUTES_FILE, 'wb') as route_file:
        route_file.write(data)

def parse_kml():
    # Open as KML and parse
    kmz = ZipFile(ROUTES_FILE, 'r')
    kml = kmz.open('doc.kml', 'r')

    # Define document structure for ElementTree
    # <Document>
    #   <Folder>
    #       <Placemark></Placemark>
    #   </Folder>
    # <Document>

    root = ET.fromstring(kml.read())
    namespace = '{http://www.opengis.net/kml/2.2}'
    document = root.find('%sDocument' % namespace)
    folder = document.find('%sFolder' % namespace)
    
    # Loop through placemarks
    for placemark in folder.findall('%sPlacemark' % namespace):
        route_name = placemark.find('%sname' % namespace).text
        description = placemark.find('%sdescription' % namespace).text
        soup = BeautifulSoup(description)
        route_num = soup.findAll('td')[3].text
        data = {'route_num': route_num, 'route_name': route_name}
        if update_database(data) is None:
            print 'Error updating database.'
            return None
        

def update_database(data):
    # Get all routes
    route_num = data['route_num']
    route_name = data['route_name']

    route = Route.query.filter_by(route_num = data['route_num']).first()
    if route is None:
        print 'Route %s not found. Creating a new database record.' % route_num
        r = Route(route_num,route_name)
        db.session.add(r)
        db.session.commit()
        return r.id
    else:
        print 'Route %s was found. Skipping.' % route_num
        return route.id
    

download_routes(url)
parse_kml()
