/*************************************************************************************************************************
 * This code is just an example on how to build the query to send the historical data to the database. Feel free to
 * modify it or replace it completely to adapt it to your specific needs. The ultimate goal is to place in $.output the
 * query to INSERT the data set containing the historical data.
 * 
 * The table where the data is inserted contains the following columns:
 *    - id: INT PRIMARY KEY, autoincrements by 1 on each new row.
 *    - tag: VARCHAR(255) NOT NULL, contains the tag name of each event.
 *    - number_value: DOUBLE, contains the numeric value of each event or NULL if the event is not a number.
 *    - string_value: VARCHAR(255), contains the text value of each event or NULL if the event is not a string.
 *    - bool_value: SMALLINT, contains the boolean value of each event or NULL if the event is not a boolean.
 *    - quality: SMALLINT NOT NULL, contains the quality value of each event.
 *    - ts: DATETIME, contains the timestamp of the event.
 * 
 * ************************************************************************************************************************/

// SYNTAX => target database to create the queries with the correct syntax. Valid values: "mysql", "mariaDb", "mssql", "pg" and "oracleDb".
const SYNTAX = "mssql";

// TABLE_NAME => target table of the database where the data will be inserted. For tables within a schema the name would
// be "schema.table_name".
// If the table does not exist, it is created automatically.
const TABLE_NAME = "PruebaSQL";

// PARTITION_FORMAT => enables data partitioning when different to null. Tables are named with TABLE_NAME+PARTITION_FORMAT
// Examples of PARTITION_FORMAT:
//  - "YYYYMMDD" => creates a new table every day.
//  - "YYYYMMDDHH" => creates a new table every hour.
const PARTITION_FORMAT = null;

//If the input datasets has no rows, return an empty string to abort the query.
if($.input.getRowCount() === 0){
  $.output = ""
} else {
  $.logger.debug("Preparing CREATE TABLE query for %s", SYNTAX);
  
  //Add the CREATE TABLE query to the output, depending on the target database (MySQL/MariaDB, SQL Server or PostgreSQL).
  switch (SYNTAX){
    case "mysql":
    case "mariaDb":
      $.output = _createTableMySQL();
      break;
    case "mssql":
      $.output = _createTableSqlServer();
      break;
    case "pg":
      $.output = _createTablePostgreSql();
      break;
    case "oracleDb":
      $.output = _createTableOracleDb();
      break;
      
  }
  $.logger.debug("CREATE TABLE query created OK, creating INSERT query...", SYNTAX);
  
  //Add the INSERT query to the final query, along with all the values in the dataset
  switch(SYNTAX){
    case "mysql":
    case "mariaDb":
      $.output += _insertDataMySQL();
      break;
    case "mssql":
      $.output += _insertDataSqlServer();
      break;
    case "pg":
      $.output += _insertDataPostgreSql();
      break;
    case "oracleDb":
      const insert = _insertDataOracleDb();
      $.output += sprintf(` 
      %s 
      exception when others then
      if SQLCODE = -955 then
      %s
      else raise;
      END IF;
      END;`,insert,insert);
      break;
  }
  $.logger.debug("Insert query created OK", $.output);
}


function _createTableSqlServer(){
  const query = sprintf("IF OBJECT_ID(N'%1$s', N'U') IS NULL BEGIN\
	CREATE TABLE %1$s (\
		\"id\" INT PRIMARY KEY IDENTITY(0, 1),\
		id_value FLOAT,\
		tag NVARCHAR(255) NOT NULL,\
		number_value FLOAT,\
		quality SMALLINT NOT NULL,\
		ts DATETIME2 NOT NULL\
	)\
  END;\r\n", _getTableName())
  return query;
}



function _insertDataSqlServer(){
  const values = [];
  for(const row of $.input){
    let rowValues = "(";
    
    
    //********************************************************************* comprobacion de nombre *********************************************************************//
    var nombre = row.getValue("tag");
    let valor;
    switch(true){
    case  nombre.includes("BM10"): 
      $.logger.debug("hello!! 1 ");
      valor = "250";
      break;
      
    case nombre.includes("BM24"):
      valor = "500";
      $.logger.debug("hello!! 2 ");
      break;
    }
    //********************************************************************* comprobacion de nombre *********************************************************************//
   
    //******************************************************************** funcion corregir nombre *********************************************************************//
    
    
    var nombreCorregido = nombre.replace("/PLC_MA/","");
    $.logger.debug("El valor es: " + valor);
    $.logger.debug("El nombre corregido es: " + nombreCorregido);
    
     //******************************************************************** funcion corregir nombre *********************************************************************//
    
    rowValues += valor + ",";
    rowValues += sprintf("'%s',", nombreCorregido);
    rowValues += row.getValue("number_value")+",";
    rowValues += row.getValue("quality")+",";
    //Timestamp is created using the ISO String representation of the date in the ts column of the dataset
    rowValues+= sprintf("'%s'", row.getValue("ts").toISOString());
    rowValues += ")";
    values.push(rowValues);
  }
  const query = sprintf("INSERT INTO %s (id_value, tag, number_value, quality, ts) VALUES\r\n%s;\r\n", _getTableName(), values.join(",\r\n"));
  return query;
}


/*
*
*/

function _getTableName(){
  // If the partition format is defined, the table name is created concatenating the 
  // table name and the current date formatted using the partition format.
  // Otherwise, the table name is returned directly.
  if(PARTITION_FORMAT){
    return sprintf("%s_%s",TABLE_NAME, moment().format(PARTITION_FORMAT));
  } else {
    return TABLE_NAME;
  }
}