#!/usr/bin/env python

#NOTE: look if your track files order coordinates lat,lon or lon,lat.
#      Then select the appropriate order in the places marked XXX in this
#      source code.

import sys, re, hashlib
from pysqlite2 import dbapi2 as sqlite

if len(sys.argv) < 3:
  print "usage:"
  print "./loc2tango locfile poidbfile"
  print "  locfile: a file in the geocaching.com loc file format, containing the route"
  print "  poidbfile: usually poi.db, or any other wellformed tangogps poi sqlite database file"
  sys.exit(1)

#XXX
RE_ID    = '<name id="(GC[0-9A-Z]+)"'
RE_TRKPT = '<coord lat="([0-9\.]+)" lon="([0-9\.]+)"'
#RE_TRKPT = '<trkpt lon="([0-9\.]+)" lat="([0-9\.]+)"'
#/XXX

re_trkpt = re.compile(RE_TRKPT)
re_id = re.compile(RE_ID)

con = sqlite.connect(sys.argv[2]) 
cur = con.cursor()

loc = open(sys.argv[1])
gcid  = "unknown"

for line in loc:
  results = re_id.findall(line)
  for match in results:
    gcid = match
  results = re_trkpt.findall(line)
  for match in results: 
    lat, lon = match 
    idmd5 = hashlib.md5(str(lon)+str(lat)).hexdigest()[0:18] #not the format tangogps uses, but what's an idmd5?s
    query = "insert into poi (idmd5, lon, lat, keywords, visibility, cat, subcat, price_range, extended_open) values ('"+idmd5+"', '"+str(lon)+"', '"+str(lat)+"', '"+gcid+"', 0.0, 0.0, 0.0, 3.0, 0.0)" #tangogps crashes if some of these remain NULL. whatever.
    cur.execute(query)

#if everything went fine, commit the changes.
con.commit()