#!/usr/bin/python3
import time
from http.server import HTTPServer, BaseHTTPRequestHandler
#from picamera import PiCamera
import operator
import os
import datetime

import sqlite3 as lite

trainthreshold=15


#import asyncio
#import datetime
#import random
#import websockets


con = lite.connect('trains.db')

def db_purge():
    global con
    with con:        
        cur = con.cursor()    
        cur.execute("DELETE FROM trains")


def db_insert(ts,t):
    global con
    with con:
        #print "inserting "+mac+" into db"    
        cur = con.cursor()    
        
        length=22
        sql="INSERT INTO trains(ts,length) VALUES('"+str(ts)+"','"+str(t)+"')"
        cur.execute(sql)


def db_dump():
    global con
    with con:        
        cur = con.cursor()    
        cur.execute("SELECT * FROM trains")

        rows = cur.fetchall()
        train_time=0
        last_et_stamp=0
        last_st_stamp=0

        for row in rows:
            #print(row[1])
            st_stamp=float(row[1])-7200.0
            if st_stamp>last_et_stamp+45:
                st = datetime.datetime.fromtimestamp(last_st_stamp) #.strftime('%Y-%m-%d %H:%M:%S'))
                et = datetime.datetime.fromtimestamp(last_et_stamp)

                if train_time>2:
                    #print(st," to ", et," train_time: ",train_time)
                    print(str(last_st_stamp)+",%.2f" % train_time) 
                #print("----------------new train")
                last_st_stamp=st_stamp
                train_time=0
            train_time+=float(row[2])
            et_stamp=float(row[1])-7200.0+float(row[2])
            st = datetime.datetime.fromtimestamp(st_stamp) #.strftime('%Y-%m-%d %H:%M:%S'))
            et = datetime.datetime.fromtimestamp(et_stamp)
            #print(st,float(row[2]),et)

            last_et_stamp=et_stamp

def db_create():
    global con
    with con:
    
        cur = con.cursor()    
        cur.execute("DROP TABLE IF EXISTS trains")
        cur.execute("CREATE TABLE trains(id INTEGER PRIMARY KEY,ts varchar(20) NOT NULL, length INT DEFAULT 0)")




if __name__ == '__main__':
#    db_purge()
    db_dump()