#!/usr/bin/python
# -*- coding: utf-8 -*-
# Schreibt mqtt-meldungen des Wettersensors in eine Datenbank

from time import gmtime, strftime
import paho.mqtt.client as mqtt
import sqlite3

temperature_topic = "wetter/temperatur"
humidity_topic = "wetter/luftfeuchtigkeit"
pressure_topic = "wetter/luftdruck"
battery_topic = "wetter/batterie"
#dbFile = "/home/pi/umweltsensor.sqlite3"
dbFile = "/home/pi/db/umweltsensor.sqlite3"

dataTuple = [-1,-1,-1,-1]

# The callback for when the client receives a CONNACK response from the server.
def on_connect(client, userdata, flags, rc):
    print("Connected with result code "+str(rc))

    # Subscribing in on_connect() means that if we lose the connection and
    # reconnect then subscriptions will be renewed.
    client.subscribe(temperature_topic)
    client.subscribe(humidity_topic)
    client.subscribe(pressure_topic)
    client.subscribe(battery_topic)
    
# The callback for when a PUBLISH message is received from the server.
def on_message(client, userdata, msg):
    theTime = strftime("%Y-%m-%d %H:%M:%S", gmtime())

    result = (theTime + "\t" + str(msg.payload))
    print(msg.topic + ":\t" + result)
    if (msg.topic == temperature_topic):
        dataTuple[0] = str(msg.payload)
    if (msg.topic == humidity_topic):
        dataTuple[1] = str(msg.payload)
    if (msg.topic == pressure_topic):
        dataTuple[2] = str(msg.payload)
    if (msg.topic == battery_topic):
        dataTuple[3] = str(msg.payload)
        #return
    if (dataTuple[0] != -1 and dataTuple[1] != -1 and dataTuple[2] != -1 and dataTuple[3] != -1):
        writeToDb(theTime, dataTuple[0], dataTuple[1], dataTuple[2], dataTuple[3])
    return

def writeToDb(theTime, temperature, humidity, pressure, battery):
    conn = sqlite3.connect(dbFile)
    c = conn.cursor()
    print "Writing to db..."
    #c.execute("INSERT INTO climate VALUES (?,?,?)", (theTime, temperature, humidity))
    c.execute("insert into 'messwerte' ('datum','temperatur','luftfeuchtigkeit','luftdruck','batterie') VALUES (?,?,?,?,?)", (theTime, temperature, humidity, pressure, battery))
    conn.commit()
    #conn.close()
    global dataTuple
    dataTuple = [-1,-1,-1,-1]

BROKER_ADDRESS = "localhost"
client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message
client.connect(BROKER_ADDRESS)
client.loop_forever()