from random import uniform
import datetime
import time
import configparser
import os
import sys
from collections import defaultdict
from flask import Flask, render_template, jsonify, request, abort, Response
import flask.json
from flask_sqlalchemy import SQLAlchemy, inspect


app_path = os.path.dirname(os.path.realpath(__file__))
lib_path = os.path.join(app_path, '..')
sys.path.append(lib_path)
from seeparklib.openweathermap import openweathermap_json, OpenWeatherMapError


# https://stackoverflow.com/a/37350445
def sqlalchemy_model_to_dict(model):
    return {c.key: getattr(model, c.key)
        for c in inspect(model).mapper.column_attrs}


class JSONEncoder(flask.json.JSONEncoder):
    def default(self, object):
        if isinstance(object, datetime.datetime):
            return object.isoformat()
        elif isinstance(object, db.Model):
            return sqlalchemy_model_to_dict(object)
        return super().default(object)


def parse_datetime(date_str):
    return datetime.datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S')


def get_sqlalchemy_database_uri(config):
    user = config.get('database', 'user')
    pwd = config.get('database', 'password')
    host = config.get('database', 'hostname')
    db = config.get('database', 'database')
    return 'mysql+mysqldb://{}:{}@{}/{}'.format(user, pwd, host, db)


config = configparser.ConfigParser()
config.read(os.environ['SEEPARKINI'])
apikey = config.get('openweathermap', 'apikey')
cityid = config.get('openweathermap', 'cityid')
mainsensor = config.get('temperature', 'mainsensor')

app = Flask(__name__)
app.json_encoder = JSONEncoder
app.config['SQLALCHEMY_DATABASE_URI'] = get_sqlalchemy_database_uri(config)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
db.reflect(app=app)


class Sensors(db.Model):
    __tablename__ = 'sensors'


class OpenWeatherMap(db.Model):
    __tablename__ = 'openweathermap'


def select_sensordata(sensor_id, sensor_type, begin, end, mode):
    query = Sensors.query
    if sensor_id is not None:
        query = query.filter(Sensors.sensor_id == sensor_id)
    if sensor_type is not None:
        query = query.filter(Sensors.value_type == sensor_type)
    if begin is not None:
        query = query.filter(Sensors.timestamp >= begin)
    if end is not None:
        query = query.filter(Sensors.timestamp <= end)
    if mode == 'consolidated' and begin is None and end is None:
        # copied from munin/master/_bin/munin-cgi-graph.in
        # interval in seconds for data points
        resolutions = dict(
            day   =   300,
            week  =  1800,
            month =  7200,
            year  = 86400,
        )
        duration = (end - begin).total_seconds()
        day = 60 * 60 * 24
        if duration < day:
            resolution = resolutions['day']
        elif duration < 7 * day:
            resolution = resolutions['week']
        elif duration < 31 * day:
            resolution = resolutions['month']
        else:
            resolution = resolutions['year']
        # TODO: filter out samples from 'result'
        # something like 
        # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
    return query.all()


def select_openweatherdata(cityid, begin, end, mode):
    query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
    if begin is not None:
        query = query.filter(OpenWeatherMap.datetime >= begin)
    if end is not None:
        query = query.filter(OpenWeatherMap.datetime <= end)
    if mode == 'consolidated' and begin is None and end is None:
        # copied from munin/master/_bin/munin-cgi-graph.in
        # interval in seconds for data points
        resolutions = dict(
            day   =   300,
            week  =  1800,
            month =  7200,
            year  = 86400,
        )
        duration = (end - begin).total_seconds()
        day = 60 * 60 * 24
        if duration < day:
            resolution = resolutions['day']
        elif duration < 7 * day:
            resolution = resolutions['week']
        elif duration < 31 * day:
            resolution = resolutions['month']
        else:
            resolution = resolutions['year']
        # TODO: filter out samples from 'result'
        # something like 
        # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
    return query.all()


def convert_to_c3(result):
    c3result = defaultdict(list)
    for row in result:
        c3result[row.sensor_id].append(row.value)
        dt = row.timestamp.strftime('%Y-%m-%d %H:%M:%S')
        c3result[row.sensor_id + '_x'].append(dt)
    return c3result


def request_arg(key, type, default=None):
    """Returns the key from the request if available, otherwise the default value.
    In case type is provided and the key is present, the value is converted by calling type.
    In other words: Reimplement request.args.get but don't return default value if
    type raises a ValueError."""
    if key in request.args:
        try:
            return type(request.args[key])
        except ValueError as e:
            abort(Response(str(e), 400))
    else:
        return default


def sensordata(sensor_id=None, sensor_type=None):
    begin = request_arg('begin', parse_datetime)
    end = request_arg('end', parse_datetime)
    mode = request.args.get('mode', 'full')
    format = request.args.get('format', 'default')

    result = select_sensordata(sensor_id, sensor_type, begin, end, mode)

    if format == 'c3':
        return convert_to_c3(result)
    return result


def openweathermapdata(cityid):
    begin = request_arg('begin', parse_datetime)
    end = request_arg('end', parse_datetime)
    mode = request.args.get('mode', 'full')
    format = request.args.get('format', 'default')

    result = select_openweatherdata(cityid, begin, end, mode)

    if format == 'c3':
        return convert_to_c3(result)
    return result


def currentairtemperature(cityid):
    result = OpenWeatherMap.query.filter_by(cityid=cityid).order_by(OpenWeatherMap.datetime.desc()).first()
    return result.temp, result.datetime


def currentwatertemperature(sensorid):
    result = Sensors.query.filter_by(sensor_id=sensorid).order_by(Sensors.timestamp.desc()).first()
    return result.value, result.timestamp


@app.route('/api/<version>/sensors/')
def sensors(version):
    """List all sensors found in the database"""
    result = db.session.query(Sensors.sensor_id, Sensors.sensor_name, Sensors.value_type).distinct().all()
    return jsonify(result)


@app.route('/api/<version>/sensor/id/<sensor_id>')
def sensorid(version, sensor_id):
    """Return all data for a specific sensor

    URL parameters:
    begin=<datetime>, optional, format like "2018-05-19T21:07:53"
    end=<datetime>, optional, format like "2018-05-19T21:07:53"
    mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
    format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
    """
    result = sensordata(sensor_id=sensor_id)
    return jsonify(result)


@app.route('/api/<version>/sensor/type/<sensor_type>')
def sensortype(version, sensor_type):
    """Return all data for a specific sensor type

    URL parameters:
    begin=<datetime>, optional, format like "2018-05-19T21:07:53"
    end=<datetime>, optional, format like "2018-05-19T21:07:53"
    mode=<full|consolidated>, optional. return all rows (default) or with lower resolution (for charts)
    format=<default|c3>, optional. return result as returned by sqlalchemy (default) or formatted for c3.js
    """
    result = sensordata(sensor_type=sensor_type)
    return jsonify(result)


@app.route('/api/<version>/openweathermap/cities')
def openweathermap_cities(version):
    """List all city IDs found in the database"""
    result = db.session.query(OpenWeatherMap.cityid).distinct().all()
    return jsonify(result)


@app.route('/api/<version>/openweathermap/city/<cityid>')
def openweathermap_city(version, cityid):
    """List all data found for a city"""
    result = openweathermapdata(cityid=cityid)
    return jsonify(result)


@app.route('/data/', defaults={'timespan': 1})
@app.route("/data/<int:timespan>", methods=['GET'])
def data(timespan):
    granularity = 5 * timespan               # (every) minute(s) per day
    samples = 60/granularity * 24 * timespan # per hour over whole timespan
    s4m   = []
    s4m_x = []
    s5m   = []
    s5m_x = []
    end   = time.time()
    start = end - samples * granularity * 60

    for i in range(int(samples)):
        s4m.append(uniform(-10,30))
        s5m.append(uniform(-10,30))
        s4mt = uniform(start, end)
        s5mt = uniform(start, end)
        s4m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s4mt)))
        s5m_x.append(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(s5mt)))

    data = {
        '0316a2193bff':   s4m,
        '0316a2193bff_x': s4m_x,
        '0316a21383ff':   s5m,
        '0316a21383ff_x': s5m_x,
        }

    return jsonify(data)


@app.route("/")
def index():
    airvalue, airtime     = currentairtemperature(cityid)
    watervalue, watertime = currentwatertemperature(mainsensor)

    return render_template(
        'seepark_web.html',
        apikey=apikey,
        watervalue=watervalue,
        watertime=watertime,
        airvalue=airvalue,
        airtime=airtime,
    )