#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <Dns.h>
#include <DHT.h>

#define DHTPIN 24
#define dht2 34
#define DHTTYPE DHT22

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

char hostname[] = "bancodoservidor"; // change to your server's hostname/URL
char user[]     = "usuario";         // MySQL user login username
char password[] = "senha";           // MySQL user login password
int controle = 0;

//char INSERT_SQL[] = "INSERT INTO u277754222_arduino.tb_arduino (id_sensor,valor) VALUES (1,%s)";
//char INSERT_SQL[] = "UPDATE u277754222_arduino.tb_arduino SET valor=%s+'°C' WHERE id_sensor='1'";
char INSERT_Tinterna[] = "UPDATE u277754222_arduino.tb_arduino SET valor=%s+'°C' WHERE id_sensor='1'";
char INSERT_Hinterna[] = "UPDATE u277754222_arduino.tb_arduino SET valor=%s+'°C' WHERE id_sensor='2'";
char INSERT_Texterna[] = "UPDATE u277754222_arduino.tb_arduino SET valor=%s+'°C' WHERE id_sensor='3'";
char INSERT_Hexterna[] = "UPDATE u277754222_arduino.tb_arduino SET valor=%s+'°C' WHERE id_sensor='4'";
char query[128];
char valor1[10];

IPAddress server_ip;
EthernetClient client;
MySQL_Connection conn((Client *)&client);
DNSClient dns_client;   // DNS instance

DHT dht(DHTPIN, DHTTYPE);
DHT dht_ext(dht2, DHTTYPE);

void setup() {
  Serial.begin(115200);
  dht.begin();
  dht_ext.begin();
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);  
  dns_client.begin(Ethernet.dnsServerIP());
  dns_client.getHostByName(hostname, server_ip);  
}
  void loop() {    
    Serial.println("Connecting...");
    if (conn.connect(server_ip, 3306, user, password)) {
      delay(1000);
      controle = 1;
      Serial.println("Conectado com sucesso.");
      Serial.println(server_ip);
    }
    else{
      Serial.println("Connection failed.");
    controle = 0;
    //conn.close();
  }
   delay(10000);
  
  // Leitura da umidade
  float h = dht.readHumidity();
  //delay(150);
  float h_ext = dht_ext.readHumidity();
  
  // Leitura da temperatura (Celsius)
  float t = dht.readTemperature();
  //delay(150);
  float t_ext = dht_ext.readTemperature();
  
   // Mostra a temperatura no serial monitor e no display
  Serial.print("Temperatura interna : "); 
  Serial.print(t);
  Serial.print(" *C  ");
  // Mostra a umidade no serial monitor e no display
  Serial.print("Umidade interna : "); 
  Serial.print(h);
  Serial.println(" %");

  //===Temp Externa=============
  Serial.print("Temperatura externa : "); 
  Serial.print(t_ext);
  Serial.print(" *C  ");
  // Mostra a umidade no serial monitor e no display
  Serial.print("Umidade externa : "); 
  Serial.print(h_ext);
  Serial.println(" %");
  //============================
  Serial.print("Status: ");
  Serial.println(controle);

  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    
  //3casas para esquerda e 2 para direita
  //=========== Temperatura interna ====================================
  dtostrf(t, 2, 1, valor1);  
  sprintf(query, INSERT_Tinterna, valor1);
  cur_mem->execute(query);
  //====================================================================
  //=========== Umidade interna ========================================
  dtostrf(h, 2, 1, valor1); //nova
  sprintf(query, INSERT_Hinterna, valor1);
  cur_mem->execute(query);
  //====================================================================
  //=========== Temperatura externa ====================================
  dtostrf(t_ext, 2, 1, valor1); //nova
  sprintf(query, INSERT_Texterna, valor1);
  cur_mem->execute(query);
  //====================================================================
  //=========== Umidade externa ========================================
  dtostrf(h_ext, 2, 1, valor1); //nova
  sprintf(query, INSERT_Hexterna, valor1);
  cur_mem->execute(query);
  //====================================================================
  // Deleting the cursor also frees up memory used
  delete cur_mem;
  //=========================================================
  delay(30000);  
}