# -*- coding: utf-8 -*-

import bottle 
import sqlalchemy
from sqlalchemy import create_engine, select
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text

from sqlalchemy.ext.sqlsoup import SqlSoup
from sqlalchemy import or_, and_, desc

from ebus.sql import model
import os
import json
import datetime
import time
app = bottle.Bottle("ebus")
bottle.debug()

engine = create_engine("postgresql://ebus:ebus@10.2.2.26/ebus", echo=True)
Session = sessionmaker()
Session.configure(bind=engine)

soup = SqlSoup(engine)

@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_single(name):
    #s = Session()
    #sensorid = s.query(model.Sensor).filter(model.Sensor.name == name).first()
    #values = Session().query(model.Value).add_columns("timestamp","value_float","value_string","value_int")
    #values = values.filter(model.Value.sensor == sensorid)
    #return json.dumps(values.first().value_int)
    return sensor_data_fromto(name,None,None)

@app.route('/sensor/:name/:startdate/:enddate')
def sensor_data_fromto(name,startdate,enddate):
    s = Session()
    sensorid = s.query(model.Sensor).filter(model.Sensor.name == name).first()
    values = Session().query(model.Value).add_columns("timestamp","value_float","value_string","value_int")
    values = values.filter(model.Value.sensor == sensorid)
    
    if startdate == None or enddate == None:
        queried_values = []
        queried_values.append(values.first())
    else:
        now = datetime.datetime.now()
        f = datetime.datetime.fromtimestamp(float(startdate))
        t = datetime.datetime.fromtimestamp(float(enddate))
        values = values.filter(model.Value.timestamp.between(f,t))
        queried_values = values.all()
    v = []
    for value in queried_values:
        if value.value_float != None:
            v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_float]) # time.altzone ist negativ
        elif value.value_int != None:
            v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_int])
        elif value.value_string != None:
            v.append([int((time.mktime(value.timestamp.timetuple())+time.altzone*-1)*1000),value.value_string])
    return {'data':v, 'error':None}


# 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(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 {'data':values, 'error':None}
    except Exception,e:
        return {'data':None, 'error':str(e)}