/*
 * ioTechProjects.com 2022
 * 
 * ESP8266 to MySQL tutorial
 * 
 * u are free to edit this code
 */
 
#include <Wire.h>
#include <SPI.h>
#include <MySQL_Generic.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>

// Defining BME280 sensor
#define SEALEVELPRESSURE_HPA (1013.25)
Adafruit_BME280 bme; // I2C

// MySQL Debug Level from 0 to 4
#define _MYSQL_LOGLEVEL_      1
#define MYSQL_DEBUG_PORT      Serial

// Network Info
char ssid[] = "WIFI_SSID";
char pass[] = "WIFI_PASSWORD";

// Database Adress
char server[] = "HOSTNAME OR IP";
uint16_t server_port = PORT;

// Database Account
char user[]         = "DATABASE_NAME";
char password[]     = "DATABASE_PASSWORD";

//Database Name
char database[] = "DATABASE_NAME";
char table[]    = "DATABASE_TABLE_NAME";

// Default insert data values
double temperature = 0;
double humidity = 0;
double pressure = 0;

// MySQL something
MySQL_Connection conn((Client *)&client);
MySQL_Query *query_mem;

void setup()
{
  // Initialize status LED
  pinMode(LED_BUILTIN, OUTPUT);
  
  // Setup Serial
  Serial.begin(115200);

  // Begin WiFi section
  Serial.println("================================================");
  Serial.println(String("Connecting to ") + ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED)
  {
    delay(500);
    Serial.print(".");
  }

  // Print out info about the WiFi connection
  Serial.print("Connected to network. My IP address is: ");
  Serial.println(WiFi.localIP());

  // Test MySQL connection
  Serial.print("Connecting to SQL Server @ ");
  Serial.print(server);
  Serial.println(String(", Port = ") + server_port);
  Serial.println(String("User = ") + user + String(", PW = ") + password + String(", DB = ") + database);

  // Testing BME280 Sensor
  unsigned status;
  status = bme.begin(0x76);
  if (!status) {
    Serial.println("Could not find a valid BME280 sensor, check wiring, address, sensor ID!");
    Serial.print("SensorID was: 0x"); Serial.println(bme.sensorID(), 16);
    Serial.print("        ID of 0xFF probably means a bad address, a BMP 180 or BMP 085\n");
    Serial.print("   ID of 0x56-0x58 represents a BMP 280,\n");
    Serial.print("        ID of 0x60 represents a BME 280.\n");
    Serial.print("        ID of 0x61 represents a BME 680.\n");
    while (1) delay(10);
  }
}


void loop()
{
  // Turn on status LED
  digitalWrite(LED_BUILTIN, LOW);

  // Measuring data
  Serial.println("================================================");
  Serial.println("Measuring data...");
  delay(500);
  Serial.println();
  
  temperature = bme.readTemperature();
  pressure = bme.readPressure() / 100.0F;
  humidity = bme.readHumidity();
  
  Serial.print("Temperature = ");
  Serial.print(temperature);
  Serial.println(" °C");

  Serial.print("Pressure = ");
  Serial.print(pressure / 100.0F);
  Serial.println(" hPa");

  Serial.print("Humidity = ");
  Serial.print(humidity);
  Serial.println(" %");

  Serial.println("================================================");

  // change Insert Query with new data
  String INSERT_SQL = String("INSERT INTO ") + database + "." + table + " (temperature, humidity, pressure) VALUES (" + temperature + "," + humidity + "," + pressure + ")";

  // Insert data in to database
  Serial.println("Connecting to MySQL...");
  Serial.println();
  if (conn.connectNonBlocking(server, server_port, user, password) != RESULT_FAIL)
  {
    delay(500);
    // Initiate the query class instance
    MySQL_Query query_mem = MySQL_Query(&conn);

    if (conn.connected())
    {
      Serial.println(INSERT_SQL);

      // Execute the query
      // KH, check if valid before fetching
      if ( !query_mem.execute(INSERT_SQL.c_str()) )
        Serial.println("Insert error");
      else
        Serial.println("Data Inserted.");
    }
    else
    {
      Serial.println("Disconnected from Server. Can't insert.");
    }
    conn.close();                     // close the connection
  }
  else
  {
    Serial.println("\nConnect failed. Trying again on next iteration.");
  }

  // Turn off status LED
  digitalWrite(LED_BUILTIN, HIGH);

  // Sleep till next data measure and insert
  Serial.println("================================================");
  Serial.println("\nSleeping...");
  delay(5000);
}