# -*- coding: utf-8 -*-
import os
import json
import datetime
import time

import bottle 
from sqlalchemy import text

app = bottle.Bottle("ebus")

def maketime(dt):
    """Rechnet CET DateTime in CET timestamp"""
    return time.mktime(dt.timetuple())*1000 # - time.altzone)*1000

def parsetime(timestamp):
    """Machtaus CET timestamp Local DateTime"""
    return datetime.datetime.fromtimestamp((timestamp/1000)) # + time.altzone)

def now(conn):
    return conn.execute(text("SELECT CURRENT_TIMESTAMP t")).first().t

@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:
        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id

        conn = soup.connection()
        sql = 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(sql, 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)
        modulo = interval / 500 #500 values

        startdate = datetime.datetime.fromtimestamp(float(startdate))
        enddate = datetime.datetime.fromtimestamp(float(enddate))

        if interval <= 0: raise Exception("Invalid interval")
        if interval >= 14 * 24 * 60 * 60: raise Exception("interval too big")

        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id

        conn = soup.connection()
        sql = 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(sql,
                              sensor_id=sensor_id,
                              startdate=startdate, 
                              enddate=enddate, 
                              modulo=modulo).fetchall()

        values = map(lambda row: (maketime(row.round_timestamp), row.value_real),
                     values)

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

@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))
        sensor_id = soup.sensor.filter(soup.sensor.name == name).one().id

        # select data from cache-view
        conn = soup.connection()
        sql = 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(sql, 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)}

import select
import psycopg2

@app.route('/stream')
@app.route('/stream/:startdate')
def stream(soup, startdate=None):
    connection = soup.connection()
    conn = connection.connection

    time_start = startdate != None and parsetime(float(startdate)) \
                 or now(connection)

    
    conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

    cursor = conn.cursor()
    cursor.execute("LISTEN evt_ebus_value_insert;")

    values = []
    fails = 0
    while fails < 5:
        if select.select([conn],[],[],10) == ([],[],[]):
            time_stop = now(connection)
            fails += 1
        else:
            conn.poll()

            notify = conn.notifies.pop()
            if not notify:
                continue

            time_stop = now(connection)
            print "time_stop %s"%time_stop
            sql = text("""SELECT sensor.name,
                                 value.timestamp,
                                 COALESCE(value.value_int,value.value_float) "value_real",
                                 value_string
                            FROM value, sensor
                           WHERE value.sensor_id = sensor.id
                             AND timestamp >= :time_start
                             AND timestamp < :time_stop""")
            values = map(lambda row: {
                "name":row.name,
                "timestamp":maketime(row.timestamp),
                "value_real":row.value_real != None and row.value_real.__float__() or None,
                "value_string":row.value_string
                },
                connection.execute(sql, time_start=time_start, time_stop=time_stop))

            break

    cursor.close()
    return {'time_start' : maketime(time_start), 'time_stop':maketime(time_stop),
            'data':values}

@app.route("/all_values")
def all_values(soup):
    conn = soup.connection()

    sql = text("""
    SELECT sensor.name,
           value.timestamp,
           COALESCE(value.value_int,value.value_float) "value_real",
           value_string
      FROM value, sensor, (SELECT MAX(timestamp) as timestamp,
                                  sensor_id
                             FROM value
                            WHERE timestamp > CURRENT_TIMESTAMP - '15 minutes'::interval
                         GROUP BY sensor_id) last_value
     WHERE value.timestamp = last_value.timestamp
       AND value.sensor_id = last_value.sensor_id
       AND value.sensor_id = sensor.id""")

    time_stop = now(conn)
    values = map(lambda row: {
            "name":row.name,
            "timestamp":maketime(row.timestamp),
            "value_real":row.value_real != None and row.value_real.__float__() or None,
            "value_string":row.value_string},
                conn.execute(sql))

    return {'data':values, 'time_stop':maketime(time_stop)}
                               
# vim: autoindent tabstop=4 shiftwidth=4 expandtab softtabstop=4 filetype=python