#!/usr/bin/env python
# coding: UTF-8

import sys
import os
from lxml import etree
from urllib.parse import unquote
import pyrungps.pygpx
import pyrungps.pygeocode
import sqlite3
import datetime

def check_db_for_training(db,sport,timestamp):

  conn = sqlite3.connect(db)
  conn.text_factory = str
  cur = conn.cursor()

  cur.execute ("select count(*) from tracks where sport=? and start_time=?" , (sport,timestamp))
  return cur.fetchall()[0][0]

def write_parsed_to_db(db,gpx,filename):

  conn = sqlite3.connect(db)
  conn.text_factory = str
  cur = conn.cursor()

  cur.execute ("delete from tracks where filename=?" , (filename,))
  
  tracks = gpx.tracks
  
  for track in tracks:

      try:
        author = gpx.author
      except:
        author = None 
        
      try:
        name = gpx.name
      except:
        name = None
        
      if author:
        try:
          cur.execute("insert into authors(name,description) values(?,?)", (author,''))         
          print("created author %s" % (author))
        except:
          print("failed to create author %s" % (author))
          pass

      try:

        print("processing...")
        start = track.start()
        if start:
          printable = pygeocode.GeoName(start.lat,start.lon).printable
          start_time = track.start_time()
          full_duration = track.full_duration().total_seconds()
          distance = track.distance()
          filtered_distance = track.filtered_distance(max_speed=50)
          ascent = track.elevation_gain()
          descent = track.elevation_loss()
          ((minlat,minlon),(maxlat,maxlon)) = track.bound_box()
          params = ( 
            gpx.author,name,filename,
            track.sport,start_time,full_duration,
            distance,filtered_distance,ascent,descent,
            start.lat,start.lon,
            printable,
            minlat,minlon,maxlat,maxlon
             )
          print(params)
          cur.execute("""
            insert into tracks(
              author,name,filename,sport,
              start_time,duration,
              distance,distance_filtered,
              ascent,descent,
              lat,lon,printable_location,minlat,minlon,maxlat,maxlon) 
            values(
              ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
              """ 
              , params )
          conn.commit()
          print("created track %s" % (filename))

      except:
      
        raise

def write_tree_to_db(db,tree,filename):

  gpx = pygpx.GPX()
  gpx.ReadTree(tree)

  write_parsed_to_db(db,gpx,filename)

def print_parsed_file(filename):

  gpx = pygpx.GPX()
  gpx.ReadFile(filename)

  for track in gpx.tracks:

    try:
      author = gpx.author
    except:
      author = None 
       
    try:
      name = gpx.name
    except:
      name = None
        
    start = track.start()
    if start:
      printable = pygeocode.GeoName(start.lat,start.lon).printable
      start_time = track.start_time()
      full_duration = track.full_duration().total_seconds()
      distance = track.distance()
      filtered_distance = track.filtered_distance(max_speed=50)
      ascent = track.elevation_gain()
      descent = track.elevation_loss()
      ((minlat,minlon),(maxlat,maxlon)) = track.bound_box()
      params = ( 
        gpx.author,name,filename,
        track.sport,start_time,full_duration,
        distance,filtered_distance,ascent,descent,
        start.lat,start.lon,
        printable,
        minlat,minlon,maxlat,maxlon
      )
      print(params)