#!/usr/bin/env python
# -*- coding: utf-8 -*-
# based on a script found here : http://relet.net/frog/archives/68
# Licence GPL
# (c) Nicolas Laurance 2008

try:
    import elementtree.ElementTree as ET
except ImportError:
    import xml.etree.ElementTree as ET
import re, hashlib
from pysqlite2 import dbapi2 as sqlite
from optparse import OptionParser

import logging,sys

log = logging.getLogger('loc2tango')
hdlr = logging.StreamHandler(sys.stdout)
formatter = logging.Formatter('[%(levelname)s] %(message)s')
hdlr.setFormatter(formatter)
log.addHandler(hdlr)


class Geocache(object):
    def __init__(self, id, title, lat, lon, url):
        self.id = id
        self.title = title.replace('\'',' ')
        self.lat = lat
        self.lon = lon
        self.url = url
        self.idmd5 = hashlib.md5(str(lon)+str(lat)).hexdigest()[0:18]
        self.description = self.title
        
        
    def sql_update(self, cursor):
        record = cursor.execute('''select * from poi where keywords="%s" ''' % self.id).fetchone()
        if record:
            query = '''update poi set idmd5='%s',
                                      lat=%s,
                                      lon=%s,
                                      desc='%s'
                       where keywords='%s' ''' % (self.idmd5, self.lat, self.lon, self.description, self.id)
            log.debug('update query : %s' % query)
        else:
            query='''insert into poi (idmd5, lat, lon, keywords, visibility, cat, subcat, price_range, extended_open, desc)
                     values ('%s', %s, %s, '%s', 0.0, 0.0, 0.0, 3.0, 0.0, '%s')
                  ''' % (self.idmd5, self.lat, self.lon, self.id, self.description)
            log.debug('insert query : %s' % query)
        cursor.execute(query)
        log.info('cache %s imported' % self.id)


def inject_geocaches(from_file, to_databasefile):
    tree = ET.parse(from_file)
    root = tree.getroot()
    con = sqlite.connect(to_databasefile) 
    cur = con.cursor()

    cur.execute('''create table if not exists poi (idmd5 text, lat real, lon real, visibility real, cat real, subcat real, keywords text,
                                        desc text, price_range real, extended_open real, creator text, bookmarked real,
                                        user_rating real, rating real, user_comment text)''')
    for waypoint in root.findall('waypoint'):
        name=waypoint.find('name')
        id = name.attrib['id']
        title = name.text
        coord = waypoint.find('coord')
        lat = coord.attrib['lat']
        lon = coord.attrib['lon']
        url = waypoint.find('link').text
        geocache = Geocache(id, title, lat, lon, url)
        geocache.sql_update(cur)
    con.commit()

def main():
    parser = OptionParser()
    parser.add_option('-i', '--input',dest='input',metavar='INFILE',default='geocaching.loc',
                      help='read waypoints from geocaching.com XML loc format file INFILE, defaults to geocaching.loc')
    parser.add_option('-o', '--output',dest='output',metavar='OUTFILE',default='poi.db',
                      help='Write geocaches into poi table of file OUTFILE, defaults to poi.db')
    parser.add_option('-q','--quiet',action="store_true",dest='quiet',default=False,
                      help='Print less information.')
    parser.add_option('-v','--verbose',action="store_true",dest='verbose',default=False,
                      help='Print debug information. this is the default')
    (options,args)=parser.parse_args()

    log.setLevel(logging.INFO)
    if options.quiet:
        log.setLevel(logging.CRITICAL)
    if options.verbose:
        log.setLevel(logging.DEBUG)

    inject_geocaches(options.input, options.output)

if __name__ == "__main__":
    main()