/*
  MySQL Connector/Arduino Example : complex select

  This example demonstrates how to issue a SELECT query with parameters that
  we provide from code. Thus, it demonstrates how to build query parameters
  dynamically.

  Notice also the sketch demonstrates how to read columns and rows from
  the result set. Study this example until you are familiar with how to
  do this before writing your own sketch to read and consume query results.

  NOTICE: You must download and install the World sample database to run
          this sketch unaltered. See http://dev.mysql.com/doc/index-other.html.

  INSTRUCTIONS FOR USE

  1) Change the address of the server to the IP address of the MySQL server
  2) Change the user and password to a valid MySQL user and password
  3) Connect a USB cable to your Arduino
  4) Select the correct board and port
  5) Compile and upload the sketch to your Arduino
  6) Once uploaded, open Serial Monitor (use 115200 speed) and observe

  Note: The MAC address can be anything so long as it is unique on your network.

  Created by: Dr. Charles A. Bell
  Modificado por : Rodrigo M. Moraes
*/

#include <SPI.h>

#include <Dhcp.h>
#include <Dns.h>
#include <Ethernet.h>
#include <EthernetClient.h>
#include <EthernetServer.h>
#include <EthernetUdp.h>

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <MySQL_Encrypt_Sha1.h>
#include <MySQL_Packet.h>

byte mac[] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED};
IPAddress ip(10, 1, 1, 10);       //Define o endereco IP
//IPAddress gateway(10, 1, 1, 1);  //Define o gateway
//IPAddress subnet(255, 0, 0, 0); //Define a máscara de rede


IPAddress server_addr(10, 1, 1, 3); //Define o endereço IP do servidor
char user[] = "ARDUINO10"; //usuario
char password[] = "100nha"; //senha
char INSERT_DATA[] = "USE `test_arduino`; INSERT INTO `autorizacao` (`Nome`, `ID`, `acesso`) VALUES ('%s', '%s', %i)";
char SELECT_DATA[] = "SELECT acesso FROM test_arduino.autorizacao WHERE ID LIKE '%%s%'";
char query[2048];
//char query2[2048];
//String oq = " Id ";
EthernetClient client;
MySQL_Connection conn((Client *)&client);
MySQL_Cursor cur = MySQL_Cursor(&conn);
bool in = true;
String nome  = "";
char id[15] = "";
String ID = "";
String card = "";
char card_id[20] = "";
String acesso;
char nomee[100] = "";
bool inserir_dados = false;

void setup() {
  Ethernet.begin(mac, ip);
  Serial.begin(115200);
  pinMode (4, 1);
  pinMode (5, 1);
  while (!Serial);
  delay(1000);
  Serial.println("Connecting...");
  if ((conn.connect(server_addr, 3390, user, password))) {
    delay(1000);

    Serial.println("Success!");
  }
  else {
    Serial.println("Connection failed.");
    conn.close();
  }
  Serial.println("OK");

}


void loop() {
  card = "123456";
  //INSERT DATA
  if (digitalRead(4)) { // Entrada do Botao para inserir dados no banco de dados
    Serial.println("Insira o Nome");// Quando pressionado o botao ira pedir para digitar o nome na Serial
    while (Serial.available() <= 0) ;// Quando serial for igual a 0 nao acontece nada
    nome = Serial.readString();
    Serial.println(nome);
    Serial.flush();
    delay(100);
    Serial.println("Insira o ID");
    while (Serial.available() <= 0);
    ID = Serial.readString();
    Serial.println(ID);
    Serial.flush();
    delay(100);
    Serial.println("Insira o nivel de acesso");
    while (Serial.available() <= 0);
    acesso = Serial.readString();
    Serial.println(acesso);
    Serial.flush();
    delay(100);

    int acessoo = acesso.toInt(); // converte o nivel de acesso digitado no Serial para um valor inteiro
    nome.toCharArray(nomee, 100); //converte o nome digitado no Serial para um conjunto de caracteres
    ID.toCharArray(id, 15); //converte o ID digitado no Serial para um conjunto de caracteres
    delay(1000);
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    sprintf(query, INSERT_DATA, nomee, id, acessoo);
    cur_mem->execute(query);
    delete cur_mem;
    Serial.println("Dado enserido com sucesso.");
    inserir_dados = false;
    Serial.flush();
  }
  //READ DATA
  if (digitalRead(5)) { //vefiricar id do cartao
    card.toCharArray(card_id, 20);
    row_values *row = NULL;
    long cartao;
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    sprintf(query, SELECT_DATA, card_id);
    Serial.println(query);
    // Execute the query
    cur_mem->execute(query);
    // Fetch the columns (required) but we don't use them.
    column_names *columns = cur_mem->get_columns();

    // Read the row (we are only expecting the one)
    do {
      row = cur_mem->get_next_row();
      if (row != NULL) {
        cartao = atol(row->values[0]);
      }
    } while (row != NULL);
    // Deleting the cursor also frees up memory used
    delete cur_mem;

    // Show the result
    Serial.println(cartao);

    delay(500);
  }
}