from socket import *
import sys
import os
import MySQLdb

# NI POMEMBNO
# IP address and port of Arduino W5100
# address = ('192.168.2.100', 5000)
# client_socket = socket(AF_INET, SOCK_DGRAM)
# client_socket.settimeout(0.5)






# database connection
# TIM
# tle se probaš connectat na podatkovno bazo
try:
    db = MySQLdb.connect(host="127.0.0.1", user="root", passwd="", db="arduino")
except Exception as e:
    sys.exit("Connection was not successfull!")






# NI POMEMBNO
# counter = 0
# dataBefore = []
# dataAfter = []

while(1):




    # v pythonu rabiš za database vse delat s cursorjem, notr shranš trenutno delo, k ga opravljaš
    cur = db.cursor()

    # translation (SLO - ENG): prostor - room
    # izvedeš stavek
    cur.execute("SELECT i.item, i.state, p.id_arduino from items as i, prostor as p where i.id_prostor = p.id_prostor")

    # rezultate shraniš v spremenljivko result
    result = cur.fetchall()

    # vsako stvar, k jo izvedeš, morš commitat, da zaključiš
    # mene je tle neki jebalo, k se je prejšnja koda izvajala al neki takga
    # preber si, kaj nardi commit
    db.commit();






    try:
        # NI POMEMBNO
        # reading data from Arduino, sent over UDP on 192.168.2.100:5000
        # data, server = client_socket.recvfrom(4096);
        # string received over UDP is in format b'string', that's why I decode it and get first 3 letters (type of element)
        # element = data[0:3].decode('UTF-8')
        # Arduino sends temperature data in format TMP=22.50Mastr
        # (TMP=    - type of element)
        # (22.50   - temperature in celsius)
        # (Mastr   - 5 digit room ID) (in this project also called node)
        # if (element == "TMP"):

            # value = data[4:9].decode('UTF-8')
            # node = data[9:14].decode('UTF-8')

            # updates temperatura based on a room ID (node)
            # translation (SLO - ENG) - temperatura - temperature, prostor - room, id_prostor - id_room




            # tle sm naredu querry za update, """ je v pythonu multiline string
            # concatenatov sm parametre, jih shranu v spremenljivko query, in potem izvedu s cur.execute(query)
            query = """UPDATE temperatura set temperatura = """ + value + """
            WHERE id_prostor = (SELECT id_prostor from prostor where id_arduino = \'""" + str(node) + "\')"
            print(query)
            cur.execute(query)




    except Exception as e:
        pass