import("system.logging");
import("system.question");
import("system.datetime");
import("system.util");
import("system.db");
import("system.text");
import("system.vars");







var importData = vars.get("$local.ImportData");
var importArray = text.parseCSV(importData, "\r\n", ";", "");
importArray.splice(0, 1);
var isMailbridge = vars.get("$local.isMailbridge");

var Mailname = vars.get("$local.Emailname");






var userNew;
var dateNew;







var associativePublisherArray = getPublisherArray();
var associativeLanguageArray = getLanguageArray();
var associativeFormatArray = getFormatArray();
var associativeAuthorArray = getAuthorArray();







//Configuration for Database Table
var table = "BOOK";
var columns = ["BOOKID", "CONTACT_ID", "BOOKTITLE", "RELEASEDATE", "ISBN", "LANGUAGE", "FORMAT",
"ORGANISATION_ID", "DATE_NEW", "USER_NEW"];
var columnTypes = db.getColumnTypes(table, columns);
var values;







//Array for Database Inserts
var sqlStatements = [];
var errMessages = "";







//Declare Variables used in for-loop
var bookTitle;
var releaseDate;
var isbn;







var authorFirstname;
var authorLastname;
var authorMiddlename;
var authorID;







var publisherID;
var publisherName;







var languageISO3;
var languageName;







var formatKeyID;
var formatTitle;







//declare Variables used in PublisherArray
var publisherSql;
var associativeArrayPublisher;







for(let i = 0; i < importArray.length; i++)
{
    authorFirstname = importArray[i][0];
    authorMiddlename = importArray[i][1];
    authorLastname = importArray[i][2];

    bookTitle = importArray[i][3];
    releaseDate = datetime.toLong(importArray[i][4], "yyyy", "UTC");
    isbn = importArray[i][6];

    //Content of .csv File
    publisherName = importArray[i][7];  //e.g.: "blanvalet"
    languageName = importArray[i][8];   //e.g.: "German"
    formatTitle = importArray[i][9];    //e.g.: "pocketbook"

    //Content of associative Array
    publisherID = associativePublisherArray[publisherName];
    languageISO3 = associativeLanguageArray[languageName];  //e.g.: "deu"
    formatKeyID = associativeFormatArray[formatTitle];      //e.g.: "abc123-523-bdfb-435"
    authorID = associativeAuthorArray[authorFirstname + authorMiddlename + authorLastname];

    dateNew = vars.get("$sys.date");
    userNew = Mailname;

    if(languageISO3 == "" || languageISO3 == undefined)
        languageISO3 = "deu";

    values = [util.getNewUUID(), authorID, bookTitle, releaseDate, isbn, languageISO3, formatKeyID, publisherID, dateNew, userNew];

    if(publisherID != null && authorID != null)
        sqlStatements.push([table, columns, columnTypes, values]);
    else
        errMessages += "ErrorMessage: the following Book couldn't be inserted: " + bookTitle + "\n";
}



if(isMailbridge == "false")
    question.showMessage(errMessages);
else
    logging.log(errMessages);




db.inserts(sqlStatements);







function getPublisherArray()
{
    var publisherSql = db.table("select ORGANISATIONID, NAME from ORGANISATION");
    var associativeArrayPublisher = [];

    for(let i = 0; i < publisherSql.length; i++)
    {
        publisherName = publisherSql[i][1];
        publisherID = publisherSql[i][0];
        associativeArrayPublisher[publisherName] = publisherID;
    }

    return associativeArrayPublisher;
}








function getLanguageArray()
{
    var languageSql = db.table("select ISO3, NAME_LATIN"
        + " from AB_LANGUAGE order by ISO3");
    var associativeLanguageArray = [];
    var ISO3;
    var Name;

    for(let i = 0; i < languageSql.length; i++)
    {
        ISO3 = languageSql[i][0];
        Name = languageSql[i][1];   //Bspw.: "German"
        associativeLanguageArray[Name] = ISO3;  //Assoziation an der Stelle "German" = "deu"
    }

    return associativeLanguageArray;
}







function getFormatArray()
{
    var formatSql = db.table("select KEYID, TITLE"
        + " from AB_KEYWORD_ENTRY"
        + " where CONTAINER = 'format'")
    var associativeArrayFormat = [];
    var KeyID;
    var Title;

    for(let i = 0; i < formatSql.length; i++)
    {
        KeyID = formatSql[i][0];
        Title = formatSql[i][1];
        associativeArrayFormat[Title] = KeyID;
    }

    return associativeArrayFormat;
}







function getAuthorArray()
{
    var AuthorSql = db.table("select FIRSTNAME, MIDDLENAME, LASTNAME, CONTACTID"
        + " from PERSON"
        + " join CONTACT on PERSONID = PERSON_ID");
    var associativeArrayAuthor = [];
    var AuthorFirstname;
    var AuthorMiddlename;
    var AuthorLastname;
    var AuthorID;

    for(let i = 0; i < AuthorSql.length; i++)
    {
        AuthorFirstname = AuthorSql[i][0];
        AuthorMiddlename = AuthorSql[i][1];
        AuthorLastname = AuthorSql[i][2];
        AuthorID = AuthorSql[i][3];

        associativeArrayAuthor[AuthorFirstname + AuthorMiddlename + AuthorLastname] = AuthorID;
    }

    return associativeArrayAuthor;
}