import("system.tools");
import("system.text");
import("system.datetime");
import("system.notification");
import("system.translate");
import("Contact_lib");
import("KeywordRegistry_basic");
import("system.vars");
import("system.util");
import("Sql_lib");
import("Util_lib");
import("system.db");
import("system.indexsearch");
import("JditoFilter_lib");
import("Util_lib");
import("system.process");
import("Employee_lib");

/**
 * Functions for district.
 */
function DistrictUtils () {}

/**
 * Gets the name of a district
 * 
 * @param {String} pDistrictId                  <p>
 *                                              Id of the district.<br>
 * @return {String}                             <p>
 *                                              Name of the district.<br>
 */
DistrictUtils.getDistrictName = function (pDistrictId)
{
    var districtName = newSelect("DISTRICT_NAME")
                            .from("DISTRICT")
                            .where("DISTRICT.DISTRICTID", pDistrictId)
                            .cell(true);    
                            
    return(districtName)
}

/**
 * Get data from district
 * 
 * @param {String} pDistrictId                  <p>
 *                                              ID of a district<br>
 * @return {Array}                              <p>
 *                                              array of data from a district <br>
 */
DistrictUtils.getDataFromDistrict = function (pDistrictId)
{    
    var districtData = newSelect("DISTRICT_FILTER, PARENTDISTRICT_DISTRICTID")
                            .from("DISTRICT")
                            .where("DISTRICT.DISTRICTID", pDistrictId)
                            .arrayRow();                   
    return(districtData);
}


/**
 * Get data from a special district
 * 
 * @param {String} pDistrictContactId           <p>
 *                                              Id of the districtcontact.<br>
 * @return {Array}                              <p>
 *                                              array of data from one districtcontact dataset <br>
 */
DistrictUtils.getDataFromDistrictContact = function (pDistrictContactId)
{
    var arrData = newSelect("DISTRICT_ID, ADVISER_CONTACT_ID, CONTACT_ID")
                            .from("DISTRICTCONTACT")
                            .where("DISTRICTCONTACT.DISTRICTCONTACTID", pDistrictContactId)
                            .arrayRow();    
                            
    return(arrData);
}


/**
 * Executes a process to assign districts on the server and creates a notification when finished.
 * 
 * @param {Array} pArrDistrictIds                   <p>
 *                                                  Array of district-IDs.<br>
 * @param {String} pUser=currentUser (optional)     <p>
 *                                                  User who will get the notification, <br>
 *                                                  User who will start the batch job. <br>
 *                                                  e.g: "_____USER_bcdfb521-c7d0-4ef1-8916-78e7d3232046" <br>
 * @param {String} pContactId (optional)            <p>   
 *                                                  Organisation which should be assigned to all  <br>
 *                                                  according districts with auto assignment is true  <br>                                            
 */
DistrictUtils.assignDistrictOnServer = function (pArrDistrictIds, pUser, pContactId)
{
    if (pUser === undefined)
        pUser = EmployeeUtils.getCurrentUserId(); 
    var processConfig = process.createStartAsyncConfig()
        .setName("assignDistrict_serverProcess")
        .setLocalVariables({
            arrDistrictIds : pArrDistrictIds.join(";"),
            user : pUser || "", 
            contactId : pContactId || ""
        })
        .setUser(pUser);
    process.startAsync(processConfig);
}

/**
 * Assignes a district to companies. You should only call this function on the server because it
 * can take some time to execute, use DistrictUtils.assignDistrictOnServer instead.
 * 
 * @param {String} pDistrictId                  <p>
 *                                              Id of the district.<br>
 * @param {String} pAppliedFilter               <p>
 *                                              Filter condition to get the assigned companies.<br>
 * @param {String} pContactId (opt)             <p>
 *                                              Only filled when a special contact-ID should assigned to a district.<br>*                                              
 * @return {Object}                             <p>
 *                                              Count of new assigned companies and no longer assigned companies.<br>
 */
DistrictUtils.assignDistrict = function (pDistrictId, pAppliedFilter, pContactId)
{
    if (pContactId == undefined)
        pContactId = null;
    
    var newAssigned = 0;
    var unchanged = 0;
    var invalid = 0;
    var updated = 0;
    
    //parse JSON-Formatted Filters to SQL-Condition
    var appliedFilterCondition = JSON.parse(pAppliedFilter).filter;
    appliedFilterCondition = JSON.stringify(appliedFilterCondition);   
    var sqlCondition = db.toFilterCondition(appliedFilterCondition, "Organisation_entity");

    //get all responsibleContacts, assigned to the superior district
    var arrResponsibleIds = newSelect(["DISTRICTRESPONSIBLE.EMPLOYEE_CONTACT_ID", "DISTRICTRESPONSIBLE.ADVISER_ROLE", 
        "DISTRICTRESPONSIBLE.ADVISER_STATUS", "DISTRICTRESPONSIBLE.VALID_FROM", "DISTRICTRESPONSIBLE.VALID_UNTIL", "DISTRICTRESPONSIBLE.VISITFREQUENCY"])
    .from("DISTRICTRESPONSIBLE")    
    .where("DISTRICTRESPONSIBLE.DISTRICT_ID", pDistrictId)
    .table();
            
    var arrOrgContactIdsAndStatus = new SqlBuilder()
    .selectDistinct("CONTACT.CONTACTID, CONTACT.STATUS")
    .from("ORGANISATION")
    .join("CONTACT", newWhere("ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID").and("CONTACT.PERSON_ID is null"))
    .leftJoin("ADDRESS", "ADDRESS.ADDRESSID = CONTACT.ADDRESS_ID")
    .leftJoin("CLASSIFICATIONSTORAGE", "CLASSIFICATIONSTORAGE.OBJECT_ROWID = CONTACT.CONTACTID")
    .whereIfSet(sqlCondition)
    .andIfSet("CONTACT.CONTACTID", pContactId)
    .table();
    
    var arrOrgContactIds = [];
    var orgContactStatusObj = {};
    for (let i = 0; i < arrOrgContactIdsAndStatus.length; i++)
    {
        arrOrgContactIds.push(arrOrgContactIdsAndStatus[i][0]);
        orgContactStatusObj[arrOrgContactIdsAndStatus[i][0]] = arrOrgContactIdsAndStatus[i][1];
    }

    var colsInsert = [
    "DISTRICTCONTACTID",
    "CONTACT_ID",
    "DISTRICT_ID",
    "ADVISER_CONTACT_ID",
    "ADVISER_ROLE",
    "STATUS",
    "VALID_FROM", 
    "VALID_UNTIL",
    "ORIGIN",
    "VISITFREQUENCY",
    "USER_NEW",
    "DATE_NEW"
    ];

    var colsUpdate = [
    "STATUS",
    "USER_EDIT",
    "DATE_EDIT"
    ];  
    
    //create districtContact datasets
    for (var j=0; j < arrResponsibleIds.length; j++)
    {   
        var insertArray = [];
        var updateArray = [];
        var updateStatements = [];
        var existingEnriesObject = {};
        var updatecond = "";
        
        var [resContactId, resRole, resStatus, resValidFrom, resValidUntil, resVisitFrequency] = arrResponsibleIds[j];
        
        var arrExistingEntries = [];
        if(!pContactId)
        {
            arrExistingEntries = new SqlBuilder()
            .select(["DISTRICTCONTACT.DISTRICT_ID", "DISTRICTCONTACT.CONTACT_ID", "DISTRICTCONTACT.ADVISER_CONTACT_ID", "DISTRICTCONTACT.DISTRICTCONTACTID"
                    , "DISTRICTCONTACT.STATUS", "DISTRICTCONTACT.ADVISER_ROLE", "DISTRICTCONTACT.VISITFREQUENCY", "DISTRICTCONTACT.VALID_FROM", "DISTRICTCONTACT.VALID_UNTIL"])
            .from("DISTRICTCONTACT")    
            .where("DISTRICTCONTACT.DISTRICT_ID", pDistrictId)
            .and("DISTRICTCONTACT.ADVISER_CONTACT_ID", resContactId)
            .and("DISTRICTCONTACT.ORIGIN", $KeywordRegistry.districtOrigin$auto())
            .table();
        }
        
        //create associative object so we don't have too loop trough an array to check wheter or not exisiting Entries already exist.
        for (var aee = 0; aee < arrExistingEntries.length; aee++) {
            //existingEnriesObject[DISTRICT_ID + CONTACT_ID + ADVISER_CONTACT_ID] = [ID, STATUS]
            existingEnriesObject[ arrExistingEntries[aee][0] + arrExistingEntries[aee][1] + arrExistingEntries[aee][2] ] = {"districtContactId": arrExistingEntries[aee][3]
                                                                                                                            , "status": arrExistingEntries[aee][4]
                                                                                                                            , "role": arrExistingEntries[aee][5]
                                                                                                                            , "visitFrequency" : arrExistingEntries[aee][6]
                                                                                                                            , "vlaidFrom" : arrExistingEntries[aee][7]
                                                                                                                            , "vlaidUntil" : arrExistingEntries[aee][8]};
        }
        
        for (var i = 0; i < arrOrgContactIds.length; i++)
        {
            resStatus = orgContactStatusObj[arrOrgContactIds[i]] == $KeywordRegistry.contactStatus$inactive() ? $KeywordRegistry.contactStatus$inReview() : resStatus;//set to in review if org is inactive
            
            //insert new assignments
            var valsInsert = [
            util.getNewUUID(),
            arrOrgContactIds[i],
            pDistrictId,
            resContactId,
            resRole,
            resStatus,
            resValidFrom,
            resValidUntil,
            $KeywordRegistry.districtOrigin$auto(), //origin
            resVisitFrequency,
            vars.get("$sys.user"),
            vars.get("$sys.date")
            ];  

            //association = DISTRICT_ID + CONTACT_ID + ADVISER_CONTACT_ID
            var existingEntryAssoz  = pDistrictId + arrOrgContactIds[i] + arrResponsibleIds[j][0];   
            var checkexistingEntry  = existingEnriesObject[existingEntryAssoz];     //existingEnriesObject[pDistrictId + arrOrgContactIds[i] + arrResponsibleIds[j][0]]
            
            //check associative Array
            if (checkexistingEntry == undefined) {  //insert missing entries
                newAssigned++
                insertArray.push(["DISTRICTCONTACT", colsInsert, null, valsInsert]);
            }
            else if (checkexistingEntry != undefined && checkexistingEntry["status"] != $KeywordRegistry.contactStatus$inReview() && 
                (checkexistingEntry["status"] != resStatus || checkexistingEntry["role"] != resRole 
                    || checkexistingEntry["validFrom"] != resValidFrom || checkexistingEntry["validUntil"] != resValidUntil
                    || checkexistingEntry["visitFrequency"] != resVisitFrequency))// value changed -> update this dataset
            {
                updated++;
                updateStatements.push(newWhere("DISTRICTCONTACT.DISTRICTCONTACTID", checkexistingEntry["districtContactId"]).buildUpdateStatement({
                            "ADVISER_ROLE": resRole,
                            "STATUS": resStatus,
                            "VALID_FROM": resValidFrom,
                            "VALID_UNTIL": resValidUntil,
                            "VISITFREQUENCY": resVisitFrequency,
                            "USER_EDIT": vars.get("$sys.user"),
                            "DATE_EDIT": vars.get("$sys.date")
                        })
                )
                //delete from Object (we need to use association here) to sieve for invalid entries
                delete existingEnriesObject[existingEntryAssoz];
            }
            else if (checkexistingEntry != undefined){  //don't touch existing entries with valid status
                unchanged++;
                //delete from Object (we need to use association here) to sieve for invalid entries
                delete existingEnriesObject[existingEntryAssoz];
            }                
        }        

        //invalid entries (Assignments, that where already exisitng, but don't fit into said district because of changes -> set status to "In review")
        for (index in existingEnriesObject) {
            invalid++;
            existingEntryId = existingEnriesObject[index]["districtContactId"];
            
            updateStatements.push(newWhere("DISTRICTCONTACT.DISTRICTCONTACTID", existingEntryId).buildUpdateStatement({
                        "STATUS": $KeywordRegistry.contactStatus$inReview(),
                        "USER_EDIT": vars.get("$sys.user"),
                        "DATE_EDIT": vars.get("$sys.date")
                    })
            )
        }
        
        var pageSize = 5000;//paging to prevent timeouts
    
        while(insertArray.length > pageSize)
        {  
            db.inserts(insertArray.splice(0, pageSize), "Data_alias", 6 * datetime.ONE_MINUTE);
        } 
        db.inserts(insertArray);
        
        while(updateStatements.length > pageSize)
        { 
            db.execute(updateStatements.splice(0, pageSize), "Data_alias", 6 * datetime.ONE_MINUTE);
        } 
        db.execute(updateStatements);
    }
        
    return {
        newAssigned : newAssigned,
        invalid : invalid,
        unchanged : unchanged, 
        updated: updated
    };    
}

/**
 * Get the complete hierarchical filter
 * 
 * @param {JSON-String} pDistrictFilter         <p>
 *                                              The filter of the current level<br>
 * @param {String} pParentDistrict_DistrictId   <p>
 *                                              District-ID of the parent district<br>*                                              
 * @return {JSON-String}                        <p>
 *                                              JSON-String of the complete filter (all levels) <br>
 */
DistrictUtils.getAppliedFilter = function (pDistrictFilter, pParentDistrict_DistrictId)
{
    var parentFilter = DistrictUtils.getParentFilter(pParentDistrict_DistrictId);

    var appliedFilter = {
        entity: "Organisation_entity", 
        filter: {
            type: "group", 
            operator: "AND", 
            childs: []
        }
    };

    if (parentFilter)
    {
        appliedFilter.filter.childs.push(JSON.parse(parentFilter).filter);
    }
    if (pDistrictFilter)
    {
        appliedFilter.filter.childs.push(JSON.parse(pDistrictFilter).filter);
    }

    return JSON.stringify(appliedFilter);
}

/**
 * Get the filter starting with the parent level
 * @param {String} pParentDistrict_DistrictId   <p>
 *                                              District-ID of the parent district<br>                                              
 * @return {JSON-String}                        <p>
 *                                              JSON-String of the filter starting with the parent<br>
 */
DistrictUtils.getParentFilter = function (pParentDistrict_DistrictId)
{
    var res = "";

    if (pParentDistrict_DistrictId)
    {
        var completeParentFilter = DistrictUtils._getParentFilter(pParentDistrict_DistrictId, null);
        completeParentFilter.entity = "Organisation_entity";
        res = completeParentFilter;
    }
    else
    {
        var entity = "Organisation_entity";
        var emptyFilter = {
            entity: entity, 
            filter: {
                type: "group", 
                operator: "AND", 
                childs: []
            }
        };

        res = emptyFilter;
    }

    return JSON.stringify(res);    
}

/**
 * This function will recursively go through each filter level up to the root.
 * @param {String} pParentDistrict_DistrictId   <p>
 *                                              District-ID of the parent district<br> 
 * @param {JSON} pPreviousFilter                <p>
 *                                              Collected filters so far<br>                                                                                    
 * @return {JSON}                               <p>
 *                                              JSON-Object of the collected filters<br>
 */
DistrictUtils._getParentFilter = function (pParentDistrict_DistrictId, pPreviousFilter)
{
    var resFilter;
    
    if (pPreviousFilter != null)
    {
        resFilter = pPreviousFilter;
    } 

    if (pParentDistrict_DistrictId)
    {
        var parentDistrictFilter, grandParentDistrict;
        [parentDistrictFilter, grandParentDistrict] = newSelect(["DISTRICT.DISTRICT_FILTER", "DISTRICT.PARENTDISTRICT_DISTRICTID"])
                                                            .from("DISTRICT")
                                                            .where("DISTRICT.DISTRICTID", pParentDistrict_DistrictId)
                                                            .arrayRow();

        parentDistrictFilter = JSON.parse(parentDistrictFilter);

        if (pPreviousFilter)
            parentDistrictFilter.filter.childs.push(resFilter.filter);
        
        resFilter = parentDistrictFilter;

        if (grandParentDistrict.length > 0)
        {   
            resFilter = DistrictUtils._getParentFilter(grandParentDistrict, resFilter);
        }
    }

    return resFilter;
}


/**
 * This function will set the status of districtcontact datasets
 * @param {Array} pArrIds                       <p>
 *                                              IDs of districtcontact<br> 
 * @param {String} pStatus                      <p>
 *                                              Status, for example $KeywordRegistry.contactStatus$active<br>                                                                                    
 * @return {none}                               <p>
 */
DistrictUtils.setDistrictContactStatus = function (pArrIds, pStatus)
{
    var updateArray = [];
    var colsUpdate = [
            "STATUS",
            "USER_EDIT",
            "DATE_EDIT"
        ]; 
    var valsUpdate = [
        pStatus,
        vars.get("$sys.user"),
        vars.get("$sys.date")
        ];          
        
    for (var i=0; i<pArrIds.length; i++)
    {          
        var condition = newWhere("DISTRICTCONTACT.DISTRICTCONTACTID", pArrIds[i]);
        updateArray.push(["DISTRICTCONTACT", colsUpdate, null, valsUpdate, condition.build()]);        
    }
    db.updates(updateArray);
}


/**
 * This function will notificate the district contact that he/she was assigned
 * 
 * @param {String} pDistrictContactId           <p>
 *                                              DistrictContact-ID of the new Dataset<br>                                                                                   
 * @return <none>                               <p>
 */
DistrictUtils.notificateNewDistrictContact = function(pDistrictContactId){
    message = translate.text("Assignment to a new district/company!");

    //[0]: DISTRICT_ID, [1]: ADVISER_CONTACT_ID, [2]: CONTACT_ID
    var arrayDistrictContact = DistrictUtils.getDataFromDistrictContact(pDistrictContactId);
    //get corresponding profile information of the systemalias
    var user = tools.getUserByAttribute(tools.CONTACTID, arrayDistrictContact[1], tools.PROFILE_DEFAULT);
    
    //You can only send a notification, if the user is an employee
    if (user)
    {
        var userName = user[tools.NAME];
        var districtName = DistrictUtils.getDistrictName(arrayDistrictContact[0]);
        var organisationName = ContactUtils.getFullTitleByContactId(arrayDistrictContact[2]);
        // Benachrichtigung am Ende der Zuordnungen     
        description = translate.withArguments("You were assigned as new district contact to district %0 and organisation %1.",
            [districtName, organisationName]);
        notification.addNotification(util.getNewUUID(), text.encodeMS(["Organisation", arrayDistrictContact[2]]), null, null, "DistrictAssigned", notification.PRIO_NORMAL, 2, notification.STATE_UNSEEN, [userName], message, description);    
    }
}