from flask import Flask, render_template, make_response, jsonify, request
import sqlite3 as sql3db
import datetime
import time
import io
import json
import pandas as pd
from collections import OrderedDict

DB = "../inenvmon_data.db" # path to the database file as set up in inenvmon_collector.py
DB_TABLE = "envdata0" # name of database table, as set up in inenvmon_collector.py
API_SECRET = "secret" # api secret for the heartbeat method, set the same in inenvmon_collector.py

class Source():
    def __init__(self):
        self.status = "unknown"  #used to persistently store status
        self.last_msg = 0.0

source = Source()
app = Flask(__name__)

def query_db(sql):  #function for executing database queries
    dbconn = sql3db.connect(DB)
    cursor = dbconn.cursor()
    
    try:
        cursor.execute(sql)
    except sql3db.Error as e:
        print(e)  # output errors

    data = cursor.fetchall()
    dbconn.close()

    return data

@app.route('/')
def index():
    # no actual template rendering is going on, this is used for convenience since the api and web app are on the same domain
    # the html is only a skeleton, all functionality is in the js
    return render_template('index.html')  

@app.route('/api/getdata', methods = ['GET','POST']) 
def process_data(): 
    if request.method == 'GET':
        if request.args.get('samples') is not None:
            try:
                samples = int(request.args.get('samples'))
                if samples > 10800:     # limit the maximum amount of data points, working with values above this threshold was observed unstable
                    samples = 120
            except Exception as excp:
                print(excp)  # output errors
                samples = 120
        else:
            samples = 120   # silently default to 120 in case of any problems
    # same as above but for POST requests this time
    elif request.method == 'POST':
        try:
            samples = int(request.form.get('samples'))
        except Exception as excp:
            print(excp)  # output errors
            samples = 120

        if samples > 10800:
            samples = 120

    sql = "SELECT * FROM %s ORDER BY timestamp DESC LIMIT %s" % (DB_TABLE, samples) # sql selection string
    dbdata = query_db(sql) # store data returned from the db

    f = '%Y-%m-%d %H:%M:%S'     # string format

    # initialize arrays
    labels = []
    temps = []
    hums = []
    press = []
    concs = []
    # split data returned from db into arrays
    for i in range(len(dbdata)):
       labels.append(datetime.datetime.strptime(dbdata[i][0], f))
       temps.append(dbdata[i][1])
       hums.append(dbdata[i][2])
       press.append(dbdata[i][3])
       concs.append(dbdata[i][4])

    # pandas dataset processing
    # detects gaps in the dataset timestamps and fills them with no values
    # gaps of one datapoint are bridged with an average value
    df = pd.DataFrame({'dt': labels, 'temp': temps, 'hum': hums, 'pres': press, 'conc': concs})
    r = pd.date_range(start=df['dt'].min(), end=df['dt'].max(), freq='T')
    df = df.set_index('dt').reindex(r, method='nearest', tolerance='90s').reset_index()
    # pandas returns weird stuff, converted here to a numpy array
    # numpy because that's what available without additional work
    darry = df.to_numpy()
    start = len(darry) # save the length for further slicing
    # array slicing and ordering 
    # because js graphing function expects the dataset with inverted timebase
    # different procedures for different scenarios
    # in case of missing values
    if len(darry) != samples:
        if samples > len(darry):
            labels=darry[samples-start:start:1,0]
            temps=darry[samples-start:start:1,1] 
            hums=darry[samples-start:start:1,2]
            press=darry[samples-start:start:1,3]
            concs=darry[samples-start:start:1,4]
        else:
            labels=darry[start-samples:start:1,0]
            temps=darry[start-samples:start:1,1] 
            hums=darry[start-samples:start:1,2]
            press=darry[start-samples:start:1,3]
            concs=darry[start-samples:start:1,4]
    # in case of a single value (when updating the plot)
    elif len(darry) == 1:
        labels=darry[0,0]
        temps=darry[0,1] 
        hums=darry[0,2]
        press=darry[0,3]
        concs=darry[0,4]
    # in case of uninterrupted dataset
    else:
        labels=darry[0:start:1,0]
        temps=darry[0:start:1,1] 
        hums=darry[0:start:1,2]
        press=darry[0:start:1,3]
        concs=darry[0:start:1,4]

    # order of the values is important therefore ordered dict
    tempdict = OrderedDict()
    humdict = OrderedDict()
    presdict = OrderedDict()
    co2dict = OrderedDict()   
    # fill the dict straight away with the single value
    if len(darry) == 1:
        tempdict[labels.strftime(f)] = str(temps)
        humdict[labels.strftime(f)] = str(hums)
        presdict[labels.strftime(f)] = str(press)
        co2dict[labels.strftime(f)] = str(concs)
    # iterate over the arrays and fill the dicts
    else:    
        for i in range(len(labels)):
            tempdict[labels[i].strftime(f)] = str(temps[i])
            humdict[labels[i].strftime(f)] = str(hums[i])
            presdict[labels[i].strftime(f)] = str(press[i])
            co2dict[labels[i].strftime(f)] = str(concs[i])

    # check status
    if (time.time() - source.last_msg) > 180:
        status = "dead"
    else:
        status = "alive"
    # final response dict
    respdict = OrderedDict()
    # ready data and corresponding key for json
    datas = ["status", "temp", "hum", "pres", "co2"]
    dicts = [status, tempdict, humdict, presdict, co2dict]    
    # iterate over the data and fill the response dict
    for i in range(len(datas)):
        respdict[datas[i]] = dicts[i]    
    # formulate json response from the prepared dict
    response = app.response_class(
        response=json.dumps(respdict),
        status=200,
        mimetype='application/json'
    )
    return response

@app.route('/api/heartbeat', methods = ['GET','POST'])
def heartbeat():  # device status reporting 
    global source
    if request.method == 'POST':  
        key = request.form.get('key')  # check the secret
        if key == API_SECRET:
            if request.form.get('message') is not None:
                source.last_msg = float(request.form.get('message'))  # save the status if timestamp present
            else:
                source.last_msg = "nodata"  
                source.status = "unknown" #unknown status if no timestamp 
            #respond with 200 ok
            post_response = app.response_class(
                status=200)
            return post_response
        else:
            # respond with 401 unauthorized if secret is incorrect
            err_response = app.response_class(
                status=401)
            return err_response

    if request.method == 'GET':
        # if get request calculate time delta 
        if (time.time() - source.last_msg) > 180:
            source.status = "dead"
        else:
            source.status = "alive"
        # formulate response json
        resp_data = {'status': source.status, 'last_msg': source.last_msg}
        get_response=app.response_class(
            response=json.dumps(resp_data),
            status=200,
            mimetype='application/json'
            )
        return get_response   

if __name__ == "__main__":
    app.run()
