# -*- coding: utf-8 -*-
# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python
import os
import json
import datetime
import time

import bottle 
from sqlalchemy import text

app = bottle.Bottle("ebus")

@app.route('/')
def index_file():
    return static_files("index.html")

@app.route('/static/:filename#.+#')
def static_files(filename):
    return bottle.static_file(filename, root=os.path.join(os.path.dirname(__file__),"static"))

@app.route('/sensor/:name')
def sensor_data(soup,name):
    try:
        # get sensor_id for name
        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id

        # select data from cache-view
        conn = soup.connection()
        s = text("""SELECT timestamp, COALESCE(value_int, value_float) as "value_real"
                      FROM value
                     WHERE sensor_id = :sensor_id
                  ORDER BY timestamp DESC
                  LIMIT 1""")
        value = conn.execute(s, sensor_id=sensor_id).first()

        return {'sensor':name,'data':[maketime(value.timestamp), float(value.value_real)], 'error':None}
    except Exception,e:
        return {'sensor':name,'data':None, 'error':str(e)}

@app.route('/sensor/:name/:startdate/:enddate')
def sensor_data_fromto(soup,name,startdate,enddate):
    try:
        interval = float(enddate) - float(startdate)
        if interval <= 0: raise Exception("Invalid interval")
        if interval >= 14 * 24 * 60 * 60: raise Exception("interval too big")

        modulo = interval / 500 #500 values

        startdate = datetime.datetime.fromtimestamp(float(startdate))
        enddate = datetime.datetime.fromtimestamp(float(enddate))
        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id



        conn = soup.connection()
        s = text("""
        SELECT to_timestamp( extract(epoch from timestamp)::int - extract(epoch from timestamp)::int % :modulo ) "round_timestamp", 
               AVG(COALESCE(value_int,value_float)) "value_real" 
         FROM value
        WHERE timestamp > :startdate
          AND timestamp < :enddate
          AND sensor_id = :sensor_id
        GROUP BY "round_timestamp"
        ORDER BY "round_timestamp"
        """)

        values = conn.execute(s, sensor_id=sensor_id, startdate=startdate, enddate=enddate, modulo=modulo).fetchall()
        values = map(lambda row: (maketime(row.round_timestamp),
                                 row.value_real),
                     values)

        print "Sent", len(values)
        return {'sensor':name,'data':values, 'error':None}
    except Exception,e:
        return {'sensor':name, 'data':None, 'error':str(e)}

# rechnet zeit so um wie Javascript
# teil es braucht XXX
def maketime(datetime):
    return (time.mktime(datetime.timetuple())+time.altzone*-1)*1000

@app.route('/sensor_cached/:name/:timestamp_from')
def sensor_data_cached_fromto(soup, name, timestamp_from):
    try:
        timestamp_from = datetime.datetime.fromtimestamp(float(timestamp_from))

        # get sensor_id for name
        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id

        # select data from cache-view
        conn = soup.connection()
        s = text("""SELECT timestamp, value_real AS "value_real"
                      FROM vi_value_cache 
                     WHERE timestamp >= :timestamp_from
                           AND sensor_id = :sensor_id
                  ORDER BY timestamp""")
        values = conn.execute(s, timestamp_from=timestamp_from, sensor_id=sensor_id).fetchall()
        values = map(lambda row: (maketime(row.timestamp),
                                 row.value_real.__float__()),
                     values)

        return {'sensor':name,'data':values, 'error':None}
    except Exception,e:
        return {'sensor':name, 'data':None, 'error':str(e)}