/**
 * Class responsible for splitting MQTT messages and inserting raw data into database
 * <p>
 * Version 1.0
 * Brynjar Blomvik
 * <p>
 * v1.0 functionality
 * Receive MQTT message from MQTT subscriber client
 * Message must consist of max one line of sensor data
 * Split single line message into raw data and construct predefined SQL statement
 * Pass statement to SQL database
 * <p>
 * TODO: Convert insertSensorData to preparedStatement.
 */

package com.FleetControl;

import com.mysql.jdbc.Connection;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.List;


public class DatabaseHandler {

    static String url = "jdbc:mysql://localhost:3306/DatabaseWebserver";
    static String username = "java";
    static String password = "password";


    public static void main(String[] args) {
        // Test calls for development
        /*
     DatabaseHandler dbHandler = new DatabaseHandler();
     String splitTest = "vehicle/001/sensor!time/11!temp/23.09!hum/50.96!Z/z";
     dbHandler.insertTestData("hello test");
     dbHandler.insertSensorDataIntoDatabase(splitTest);
     */
    }

    /**
     * Connects and passes data to the SQL database
     *
     * @param s MQTT message to be added to database
     */
    public void insertStatementIntoDatabase(String s) throws SQLException {
        // Connect to the database with username and password
        System.out.println("Statement to be inserted: " + s);
        Statement st = null;

        System.out.println("Connecting to database...");
        try (Connection connection = (Connection) DriverManager.getConnection(url, username, password)) {
            System.out.println("[ OK ] Database connected");
            st = connection.createStatement();
            st.executeUpdate(s);
            //  System.out.println("Data for insertion::\n" + s);
            System.out.println("[ OK ] Data inserted to database");
        } catch (SQLException e) {
            throw new IllegalStateException("Cannot connect the database!", e);
        } finally {
            // Disconnect from database
            if (st != null) {
                st.close();
                System.out.println("[ OK ] Disconnected from database.");
            }
        }
    }

    /**
     * Function to add new vehicle to the database
     * <p>
     * v1.0 Adds static vehicle. Improvement take input from user
     *
     * @return
     */







    /**
     * If MQTT message does not match predefined conditions for message content insert into pure string table
     *
     * @param s message from MQTT client
     */
    public void insertGenericMessageIntoDatabase(String s) throws SQLException {
        String sql = "INSERT INTO Misc (Message) VALUES (\"" + s + "\")";
        // System.out.println("COMPLETE SQL STATEMENT:\n" + sql);
        insertStatementIntoDatabase(sql);
    }

    /**
     * Split sensordata message and create SQL INSERT statement
     *
     * @param message The MQTT message to be splitted
     * @throws SQLException
     */
    public void splitMessage(String message) throws SQLException {
        // Defining variables for incoming data
        String vehicleId = null;
        //String timeStamp = null;
        String gpsLat = null;
        String gpsLon = null;
       /* String temp = null;
        String humid = null;*/

        // Defining SQL statement for database insertion
        String sqlDataEntry = "INSERT INTO Sensordata (vehicleId, Timestamp, GpsLat, GpsLon, TempSens, HumidSens) " + "VALUES(";
        String sqlCurLocUpdate = null;
        System.out.println();
        // Splitting MQTT message on different data types
        String[] sensorType = message.split("!");
        //System.out.println("souting \"sensorType\"");
        //System.out.println(sensorType + "\n");
        List<String> dataList = Arrays.asList(sensorType);
        //System.out.println("souting \"dataList\"");
        //System.out.println(dataList);

        int j = 0;
        while (j < 5) {
            for (String tmp : dataList) {
                // check for end of line trigger
                if (tmp.contains("y")) {
                    // prepare for next line of sensordata
                    sqlDataEntry += "), (" + vehicleId + ", ";
                    j = 1;
                }
                // check for end of message trigger
                if (tmp.contains("z")) {
                    // Close SQL statement
                    sqlDataEntry += ")";
                    // Returning SQL statement for database insertion
                    System.out.println("[ OK ] message dissection");
                    // forward sql statement to database insertion
                    insertStatementIntoDatabase(sqlDataEntry);
                    sqlCurLocUpdate = "UPDATE DatabaseWebserver.vehicle SET CurrentLocLat = " + gpsLat + ", CurrentLocLon = " + gpsLon + " WHERE vehicleId = " + vehicleId + ";";
                    System.out.println(sqlCurLocUpdate);
                    insertStatementIntoDatabase(sqlCurLocUpdate);
                    vehicleId = "IDparReset";
                    break;

                }
                //  System.out.println(j);
                //  System.out.println("tmp: " + tmp);
                String[] rawData = tmp.split("/");
                //  System.out.println("split on data type " + rawData[0].toString());
                //System.out.println("Rawdata[0]: " + rawData[0]);
                //System.out.println("Rawdata[1]: " + rawData[1]);

                if (rawData[0].contains("vehicle")) {
                    vehicleId = "\"" + rawData[1] + "\"";
                }
                if (rawData[0].contains("Lat")) {
                    gpsLat = "\'" + rawData[1] + "\'";
                }
                if (rawData[0].contains("Lon")) {
                    gpsLon = "\'" + rawData[1] + "\'";
                }
                if (j < 6) {
                    sqlDataEntry += "\"" + rawData[1] + "\"";
                }
                if (j < 5) {
                    sqlDataEntry += ", ";
                }
                j++;
            }
        }
    }
}