import sys
sys.path.insert(0, "..")
import time
import datetime
import subprocess
import logging

from opcua import Server
import BMP085
import mysql.connector as mysql

##### Sensor Data of BMP180 ######################
bmp = BMP085.BMP085()
temperature =bmp.read_temperature()
altitude = bmp.read_altitude()
pressure = bmp.read_pressure()
################################################

if __name__ == "__main__":
    logging.basicConfig(level = logging.WARNING)

# get Objects node, this is where we should put our nodes
# setup our server
server = Server()
server.set_endpoint("opc.tcp://192.168.1.10:4840/freeopcua/server/")

# setup our own namespace, not really necessary but should as spec
uri = "http://192.168.1.10:4840/freeopcua/server/"
idx = server.register_namespace(uri)

# get Objects node, this is where we should put our nodes
objects = server.get_objects_node()

# populating our address space
################################### Object ####################################################
sensordata = objects.add_object(idx, "Sensor One BMP180")
############################# Variables of Object #############################################
temp_var_node= sensordata.add_variable(idx, "Temperature Sensor One", temperature)
print("the temp_var_node is:", temp_var_node)
altit_var_node = sensordata.add_variable(idx, "Altitude Sensor One", altitude)
print("the altit_var_node is:", altit_var_node)
press_var_node = sensordata.add_variable(idx, "Pressure Sensor One", pressure)
print("the pressure_var_node is:", press_var_node)
#################### Set MyVariable to be writable by clients #################################

temp_var_node.set_writable()# Set MyVariable to be writable by clients
altit_var_node.set_writable()
press_var_node.set_writable()

######################### Sending data values of variables to the database ####################################

db = mysql.connect(host = '192.168.1.15',port = 3306,user = 'root',password = 'admin',database = 'OPCUA')
cursor = db.cursor()
delete = "DROP TABLE sensorBMP180_raspberrypi2"
cursor.execute(delete)

sql = """CREATE TABLE sensorBMP180_raspberrypi2 (
         temperature DOUBLE,
         altitude INT,
         pressure INT)"""
cursor.execute(sql)

# starting de server OPCUA!
server.start()
try:

    while True:
 ############## Values on UaExpert ###########################################
        temp_var_node.set_value(bmp.read_temperature())
        time.sleep(2)
        altit_var_node.set_value(bmp.read_altitude())
        time.sleep(2)
        press_var_node.set_value(bmp.read_pressure())
        time.sleep(2)
################## Values to DataBase #########################################
        temperature = bmp.read_temperature()
        pressure = bmp.read_pressure()
        altitude = bmp.read_altitude()
        time.sleep(2)
        cursor.execute("""INSERT INTO sensorBMP180_raspberrypi2 VALUES (%s, %s, %s)""",(temperature,pressure,altitude))
        db.commit()

finally:

    #close connection, remove subcsriptions, etc
    server.stop()
    db.close()
#################################################################################

cursor.execute("""INSERT INTO sensorBMP180_raspberrypi2 VALUES (%s, %s, %s)""",(temperature,pressure,altitude))