#!/usr/bin/pyhton -u

import sqlite3 as sql3db
import paho.mqtt.client as mqtt
import time
import datetime
import requests

MQTT_Broker = "127.0.0.1" # usually on the same machine, if not, adjust accordingly
MQTT_Port = 9883 # mqtt port for internal use, can be whatever, determined by the broker config
Keep_Alive_Interval = 60 # keep alive interval for broker connection
MQTT_Topic = "topic" # mqtt topic, set to the same value as the monitoring device
MQTT_Auth = {'username':"", 'password':""} # mqtt authentication, determined by the broker config
DB = "./inenvmon_data.db" # path to database file
DB_TABLE = "envdata0" # name of the database table (mqtt topic is fitting here)
API_SECRET = "secret" # set to same value as in the api

def write_db(sql): # function for writing to the database
    dbconn = sql3db.connect(DB)
    cursor = dbconn.cursor()

    try:
        cursor.execute(sql)
    except sql3db.Error as e:
        print(e)
        return False

    dbconn.commit()
    dbconn.close()
    return True

def send_heartbeat(): # function for heartbeat reporting, adjust according to server setup
    return requests.post("http://localhost:80/api/heartbeat", data={'message':time.time(),'key': API_SECRET})

# on connection callback, used to subscirbe to the set up topic after connecting to the broker
def on_connect(client, userdata, flags, rc):
    print("connected: " + str(rc)) 
    mqttc.subscribe(MQTT_Topic, 0)
    print("subscribed!")

# on message callback, executes every time a message is published to the topic
def on_message(mosq, obj, msg):
    payload = msg.payload # save the message payload
    try:
        dec_msg = payload.decode("unicode_escape").split(",")  # decode the payload and split at commas
        # out of range value check, replaces erroneous values with no value
        # included as a precaution, the sensors could malfunction and the communication between the devices is not always perfect
        # the ranges are set to what can be reasonably expected in an interior environment, can be tweaked if desired
        if float(dec_msg[0]) > 45 or float(dec_msg[0]) < 0: # temperature
            dec_msg[0] = "NULL"
        if float(dec_msg[1]) < 0 or float(dec_msg[1]) > 100: # relative humidity
            dec_msg[1] = "NULL"
        if int(dec_msg[2]) < 900 or int(dec_msg[2]) > 1100: # pressure
            dec_msg[2] = "NULL"
        if dec_msg[3] == "-1":  # co2, value of -1 indicates a measurement error
            dec_msg[3] = "NULL"
        
    except Exception as ex:
        print("Excp: %s" % ex) # print exceptions

    f = '%Y-%m-%d %H:%M:%S'  # timestamp format string
    # create table
    sql0 = "CREATE TABLE IF NOT EXISTS %s \
            (\"timestamp\" datetime DEFAULT NULL, \
            \"temp\" decimal(5 , 2) DEFAULT NULL, \
            \"hum\" decimal(5 , 2) DEFAULT NULL, \
            \"pres\" int(4) DEFAULT NULL, \
            \"co2\" int(5) DEFAULT NULL)" % DB_TABLE
    write_db(sql0)
    # formulate sql string with values
    sql = "INSERT INTO %s (timestamp, temp, hum, pres, co2) VALUES (\"%s\", %s, %s, %s, %s)" % (DB_TABLE, datetime.datetime.now().strftime(f), dec_msg[0], dec_msg[1], dec_msg[2], dec_msg[3])
    write_db(sql)  # execute the sql string
    send_heartbeat() # send heartbeat

mqttc = mqtt.Client() # instantiate the client class
mqttc.on_message = on_message 
mqttc.on_connect = on_connect # set up callbacks
mqttc.username_pw_set(MQTT_Auth['username'], MQTT_Auth['password']) # set mqtt credentials
mqttc.connect(MQTT_Broker, int(MQTT_Port), int(Keep_Alive_Interval)) # connect to the broker using provided settings
mqttc.loop_forever() # keep the connection running as long as the script is running
