import Adafruit_DHT
import time
import mysql.connector
from mysql.connector import Error
from datetime import datetime

DHT_SENSOR = Adafruit_DHT.DHT22
DHT_PIN = 4

HUMIDITY_SENSOR_ID = 1
TEMPERATURE_SENSOR_ID = 2

try:
    connection = mysql.connector.connect(host='sql3.hostcreators.sk',
                                         database='d4552_martinsenzor',
                                         user='u4552_martinsenzor',
                                         password='5dC-1uXX.0s_',
                                         port = 3308)


    while True:
        humidity, temperature = Adafruit_DHT.read_retry(DHT_SENSOR, DHT_PIN)

        if humidity is not None and temperature is not None:
            print("Temp={0:0.1f}*C  Humidity={1:0.1f}%".format(temperature, humidity))

            sensor_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

            humidity_val = '{"value":'+("{0:0.2f}".format(humidity))+',"unit":"%"}'
            temperature_val = '{"value":'+("{0:0.2f}".format(temperature))+',"unit":"C"}'

            cursor = connection.cursor()
            cursor.execute("INSERT INTO sensor_history (id_sensor, data, sensor_date) VALUES (%s, %s, %s);", (HUMIDITY_SENSOR_ID, humidity_val, sensor_date))
            cursor.execute("INSERT INTO sensor_history (id_sensor, data, sensor_date) VALUES (%s, %s, %s);", (TEMPERATURE_SENSOR_ID, temperature_val, sensor_date))
            connection.commit()

            cursor.execute("UPDATE sersors SET data = %s, sensor_date = %s WHERE id_sensor = %s;", (humidity_val, sensor_date, HUMIDITY_SENSOR_ID))
            connection.commit()

            cursor.execute("UPDATE sersors SET data = %s, sensor_date = %s WHERE id_sensor = %s;", (temperature_val, sensor_date, TEMPERATURE_SENSOR_ID))
            connection.commit()


            print("Date Record inserted successfully")

            time.sleep(100)
        else:
            print("Failed to retrieve data from humidity sensor")

except mysql.connector.Error as error:
    connection.rollback()
    print("Failed to insert into MySQL table {}".format(error))

finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")