# import the necessary packages
import flask
import json
import datetime, timedelta
#import mysql.connector as mariadb
#from flask.ext.mysqldb import MySQL
import mysql.connector
from datetime import datetime
from flask import request
from pymongo import MongoClient
app = flask.Flask(__name__)
app.config["DEBUG"] = True

#if __name__ == '__main__':
#    app.run(host='0.0.0.0', port=5000)
# Configuración de MongoDB
mongo_client = MongoClient("mongodb+srv://daniel:daniel89@cluster0.ktnrq.azure.mongodb.net/")
mongo_db = mongo_client["dataSensors"]


# route to return all information
@app.route('/api/data', methods=['GET'])
def getAllData():   
   json_data = []
   # execute a SQL statement
   try:
    #Database configuration
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='dataSensors1', port=3306)
    # create a connection cursor
    cursor = db.cursor()
    cursor.execute("SELECT id_dht22, temperature, humidity, datetime  FROM DHT22")
    result = cursor.fetchall()
    
    for row in result: 	
     json_data.append({'id' : row[0], 'temperature ': row[1], 'humidity' : row[2], 'datetime' : row[3].strftime('%H:%M:%S')})
     
    return json.dumps(json_data)
   
   
   except mysql.connector.Error as Error:
    print ("error: ", Error.msg)
    return ('<h1>Error:  %s </h1>' % Error.msg)
   

# route to return selected id
@app.route('/api/data/<id_dth22>', methods=['GET'])
def search_id(id_dth22):
 if int(id_dth22) >= 0:
   try:
    #Database configuration
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='dataSensors', port=3306)
    # create a connection cursor
    cursor = db.cursor()
    # execute a SQL statement
    cursor.execute("""SELECT id_dht22, temperature, humidity, datetime FROM DHT22 where id_dht22 = ('%s')""" % (id_dth22))
    tupla = ''
    result = cursor.fetchall()
    #print("valor: ",  result)
    for row in result:
   	 tupla = ({'id' : row[0], 'temperature ': row[1], 'humidity' : row[2], 'datetime' : row[3].strftime('%H:%M:%S')})
   	 return json.dumps(tupla)
   except mysql.connector.Error as Error:
    print ("error: ", Error.msg)
    return ('<h1>Error:  %s </h1>' % Error.msg)
 else:
   print('Errror')  
   return ('<h1>Search is ERROR</h1>')      
   
# route to calculate the average temperature for the last hour
@app.route('/api/data/hour', methods = ['GET'])
def average_last_hour():
 try:
    print('before')
    print(datetime.now().strftime('%H:%M:%S'))
    print('now')
    #print(datetime.now().hour -1) #.strftime('%H:%M:%S'))
    now1 = datetime.now().strftime('%H:%M:%S.2f')
    now = datetime.now()
    hour_1 = '{:02d}'.format(now.hour - 1)
    hour = '{:02d}'.format(now.hour)
    minute = '{:02d}'.format(now.minute)
    second = '{:02d}'.format(now.second)
    year = '{:02d}'.format(now.year)
    month = '{:02d}'.format(now.month)
    day = '{:02}'.format(now.day)
    #print('va todo')
    before_hour= '{}-{}-{} {}:{}:{}'.format(year,month, day,hour_1,minute,second)
    now_hour= '{}-{}-{} {}:{}:{}'.format(year,month, day,hour,minute,second)
    print(before_hour)
    asd = 10
    #Database configuration
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='dataSensors', port=3306)
    # create a connection cursor
    cursor = db.cursor()
    # execute a SQL statement
    cursor.execute("""SELECT id_dht22, avg(temperature), avg(humidity), datetime FROM DHT22 where datetime BETWEEN ('%s') AND  ('%s')""" % (before_hour, now_hour))
    tupla = ''
    result = cursor.fetchall()
    #print("valor: ",  result)
    for row in result:
   	 tupla = ({'id' : row[0], 'temperature ': row[1], 'humidity' : row[2], 'datetime' : row[3].strftime('%H:%M:%S')})
   	 return json.dumps(tupla)
 except mysql.connector.Error as Error:
    print ("error: ", Error.msg)
    return ('<h1>Error:  %s </h1>' % Error.msg)
  
# route to add data to database
@app.route('/api/data/<temperature>/<humidity>', methods = ['POST'])
def insert_data_dth22(temperature, humidity):
 try:
    # Database configuration
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='dataSensors1', port=3306)
    # create a connection cursor
    cursor = db.cursor()
    # execute query
    cursor.execute("""INSERT INTO DHT22(temperature, humidity) values('%s',' %s')""" % (temperature, humidity))
    db.commit()
    mongo_db.DHT22.insert_one({
            "temperature": float(temperature),
            "humidity": float(humidity),
            "datetime": datetime.now().strftime('%Y-%m-%d %H:%M:%S')
     })
    return ('<h1>ok</h1>')
 except mysql.connector.Error as Error:
 	print("error", Error.msg)
 	return('<h1>ERRor: %s </h1>' % Error.msg)
 	
# route to add data to database
@app.route('/api/db18/<temperature>', methods = ['POST'])
def insert_data_db18(temperature):
 try:
    # Database configuration
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='dataSensors1', port=3306)
    # create a connection cursor
    cursor = db.cursor()
    # execute query
    cursor.execute("""INSERT INTO DB18(temperature) values('%s')""" % (temperature))
    db.commit()
    return ('<h1>ok</h1>')
 except mysql.connector.Error as Error:
 	print("error", Error.msg)
 	return('<h1>ERRor: %s </h1>' % Error.msg)

@app.route('/api/tejado/<temperature>', methods  = ['POST'])
def insert_tejado(temperature):
 try:
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='Daniel89', db='tejado', port=3306)
    cursor = db.cursor() 
    cursor.execute("""INSERT INTO temperature(temperature) values ('%s') """ % (temperature))
    db.commit()
 except mysql.connector.Error as Error:
        print("Error", Error.msg)
        return('<h1> ERRorrr: %s </h1>' % Error.msg)

#nuevo
@app.route('/api/arduino/<temperature1>/<voltaje1>', methods  = ['POST'])
def insert_voltaje(temperature1, voltaje1):
 try:
   db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='esp', port=3306)
   cursor = db.cursor() 
   cursor.execute("""INSERT INTO arduino(temperature, voltaje) values ('%s','%s') """ % (temperature1, voltaje1))
   db.commit()
   mongo_db.arduino.insert_one({
            "temperature": float(temperature1),
            "voltaje": float(voltaje1),
            "datetime": datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        })
   return('<h1>ok</h1>')
 except mysql.connector.Error as Error:
   print("Error", Error.msg)
   return('<h1> ERRorrr: %s </h1>' % Error.msg)
 
@app.route('/api/esp/<temperature>', methods = ['POST'])
def insert_esp(temperature):
 try:
    # DataConexion
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='esp', port=3306)
    # create a conection cursor
    cursor = db.cursor()
   # excutar
    cursor.execute("""INSERT INTO esp(temperature) values ('%s')""" % (temperature))
    db.commit()
    return('<h1>ok</h1>')
 except mysql.connector.Error as Error:
       print("error ", Error.msg)
       return('<h1>Erorrr:  %s </h1>'  % Error.msg)

@app.route('/api/bme/<temperature>/<presion>/<altitud>/<altitud_mar>', methods = ['POST'])
def insert_data_bme(temperature,presion,altitud,altitud_mar):
 try:
    # Database configuration
    db = mysql.connector.connect(host='127.0.0.1', user='daniel', passwd='daniel89', db='dataSensors1', port=3306)
    # create a connection cursor
    cursor = db.cursor()
    # execute query
    cursor.execute("""INSERT INTO BME(temperature, presion, altitud, altitud_mar) values('%s', '%s', '%s', '%s')""" % (temperature,presion, altitud, altitud_mar))
    db.commit()
    return ('<h1>ok</h1>')
 except mysql.connector.Error as Error:
 	print("error", Error.msg)
 	return('<h1>ERRor: %s </h1>' % Error.msg)
    
	
	



app.run(host='192.168.1.28', port=5000)