var mqtt = require('mqtt');
var mysql = require('mysql');

var client  = mqtt.connect('mqtt://host',{
  username: 'mqttUsername',
  password: 'mqttPassword'
});

var connection = mysql.createConnection({
  host     : 'host',  //if port is not default, define!
  user     : 'user',
  password : 'password',
  database : 'database'
});

connection.connect(function(err) {
  if (err) throw err;
  console.log('Connected to MySQL');
    });

//data globals
var dataForColumn1 = 0;
var dataForColumn2 = 0;

client.on("connect", function () {
    client.subscribe('#'); //subscribe to all Topics
    console.log('Connected to MQTT');
});

client.on('message', function (topic, message) {
  switch(topic){ //switch is optional, you can obviously save all messages into one column, too.
    case 'topic1':
      dataForColumn1 = parseFloat(message.toString());
      break;
    case 'topic2':
      dataForColumn2 = parseFloat(message.toString());
      break;
    case 'triggerTopic':
      writeToDatabase();
    default:
      console.log('Unknown Topic');
  }
  //console.log(topic.toString() + ': ' + message.toString())
});


function writeToDatabase(){
    var sql = `INSERT INTO DatabaseName (topic1, topic2) VALUES (${dataForColumn1}, ${dataForColumn2})`;
    connection.query(sql, function (err, result) {
      if (err) throw err;
      console.log('New Data inserted');
    });
}