# ------------------------------------------
# --- Author: Mladen Simeunovic
# --- Company: Telos d.o.o.
# --- Date: October 2017
# --- Version: 1.0
# --- Python Ver: 2.7
#
# --- Name: mosquitto_sub2MySQL
#
# ------------------------------------------

import paho.mqtt.client as mqtt
import json
import mysql.connector
from datetime import datetime

# Ura in datum
timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")


# Prijava na Topic
MQTT_Topic = "+/+/+/+/+"

# DB podatki
db = mysql.connector.connect(user='<user>', password='<password>', host='<ip-address>', database='<database>', use_pure=False)
cursor = db.cursor()

# Izpis ob prijavi na brokerja
def on_connect(client, userdata, flags, rc):
    print("Connected to Telos Lora Network server with result code " + str(rc) + ", subscribed on topic " + str(
        MQTT_Topic))
    print("*" * 200)
    # Prijava na tpic
    client.subscribe(MQTT_Topic)

# Live izpis prijeve na novice (Topic)
def on_message(client, userdata, msg):
    podatki = json.loads(msg.payload)
    rxinfo = podatki["rxInfo"]
    dumprxinfo = json.dumps(rxinfo[0])
    endnode = json.loads(dumprxinfo)


    # Shrani v variable GW podatke
    gNAME = str(endnode["name"])
    gDEVEUI = str(endnode["mac"])
    gRSSI = str(endnode["rssi"])
    gSNR = str(endnode["loRaSNR"])
    gLAT = str(endnode["latitude"])
    gLON = str(endnode["longitude"])
    gALT = str(endnode["altitude"])
    gCDR = str(podatki["txInfo"]["codeRate"])
    gFRE = str(podatki["txInfo"]["frequency"])
    gMOD = str(podatki["txInfo"]["dataRate"]["modulation"])
    gBW = str(podatki["txInfo"]["dataRate"]["bandwidth"])
    gSF = str(podatki["txInfo"]["dataRate"]["spreadFactor"])
    gADR = str(podatki["txInfo"]["codeRate"])

    # Shrani v variable Node podatke
    nNAME = str(podatki["nodeName"])
    nAPPNAME = str(podatki["applicationName"])
    nDEVEUI = str(podatki["devEUI"])
    nFCNT = str(podatki["fCnt"])
    nFPORT = str(podatki["fPort"])
    nDATA = str(podatki["data"])

    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(timestamp)

    # Vpis v DB
    try:
        cursor.execute(
            "INSERT INTO gwdata (Name, ura, DevEUI, RSSI, SNR, Lat, Lon, Alt, CodeRate, Freq, Modulation, Bandwidth, SpreadFactor, Adr ) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
            (gNAME, timestamp, gDEVEUI, gRSSI, gSNR, gLAT, gLON, gALT, gCDR, gFRE, gMOD, gBW, gSF, gADR,))
        cursor.execute(
        	"INSERT INTO nodedata (Time, Name, AppName, DevEUI, fCnt, fPort, Data) VALUES (%s,%s,%s,%s,%s,%s,%s)",
        	(timestamp, nNAME, nAPPNAME, nDEVEUI, nFCNT, nFPORT, nDATA))

        db.commit()
    except:
        db.rollback()


client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message

# Podatki mosquitto brokerja
client.connect("<broker ip-address>", <broker port>, 60)

client.loop_forever()
db.close()