bulkUpload.js

const CandidateModel = require('candidate_data');
const BundleService = require('bundle_service');
const BundlePlanMappingService = require('bundle_plan_mapping_service');
const candidateDataMappingService = require("candidate_data_mapping_services");
const _ = require('lodash');
const global_constants = require('global_constants');
const CustomerService = require('customer_service');
const CustomerContactModel = require('customer_contact');
const CommonService = require('common_service');
const PromoteService = require('promote_service');
const { Sequelize } = require('sequelize');
const DelegateModel = require('delegate');
const MemberModel = require('member');
const MemberProfileModel = require('member_profile');

class CandidateService {
    constructor(sequelize) {
        this.sequelize = sequelize;
        this.candidateModel = new CandidateModel(sequelize);
        this.candidateModel = new CandidateModel(sequelize);
        this.delegateModel = new DelegateModel(sequelize);
        this.customerContact = new CustomerContactModel(sequelize);
        this.promoteService = new PromoteService(sequelize);
        this.customerService = new CustomerService(sequelize);
        this.memberModel = new MemberModel(sequelize);
        this.memberProfileModel = new MemberProfileModel(sequelize);
    }

    createCandidateData(inputParams) {
        return new Promise(async (resolve, reject) => {
            try {
                let validationError = await this.validateParams(inputParams);
                if (!validationError) {
                    let createCandidateResponse = this.candidateModel.createCandidateData(candidateDataMappingService.paramCandidateDataMapping(inputParams));
                    resolve(createCandidateResponse);
                } else {
                    resolve({ error: validationError });
                }
            } catch (error) {
                reject(error);
            }
        })
    }

    async validateParams(inputParams) {
        let residentialZip = inputParams['residential_address_zip'];
        let mailingZip = inputParams['mailing_postal_code'];

        if (inputParams['birth_date']) {
            if (!this.dateIsValid(inputParams['birth_date']))
                return "Invalid birth date";
        }
        if (inputParams['plan_start_date']) {
            if (!this.dateIsValid(inputParams['plan_start_date']))
                return "Invalid plan start date";
        }
        if (inputParams['plan_end_date']) {
            if (!this.dateIsValid(inputParams['plan_end_date']))
                return "Invalid plan end date";
        }
        if (inputParams['work_phone'] && inputParams['work_phone'].length != 10) {
            return "Invalid work phone";
        }
        if (inputParams['mobile_phone'] && inputParams['mobile_phone'].length != 10) {
            return "Invalid mobile phone";
        }
        if (inputParams['email_id']) {
            if (this.validateEmail(inputParams['email_id']) == null) {
                return "Invalid email";
            }
        }
        if (residentialZip) {
            if (!(residentialZip.length == 5 || residentialZip.length == 6))
                return "Invalid residential zip";
        }
        if (mailingZip) {
            if (!(mailingZip.length == 5 || mailingZip.length == 6))
                return "Invalid mailing postal code";
        }
        let status = inputParams['membership_status'];
        if (status) {
            if (!(status.toLowerCase() == "active" || status.toLowerCase() == "inactive")) {
                return "Invalid membership status";
            }
        }

        let gender = inputParams['gender'];
        if (status) {
            if (!(gender.toLowerCase() == "male" || gender.toLowerCase() == "female" || gender.toLowerCase() == "other")) {
                return "Invalid gender";
            }
        }

        return null
    }

    dateIsValid(dateStr) {
        //yyyy/mm/dd
        const regex = /^\d{4}\-\d{2}\-\d{2}$/;
        const regex2 = /^\d{4}\/\d{2}\/\d{2}$/;

        if (typeof dateStr == 'string') {
            if (dateStr.match(regex2) !== null || dateStr.split('T')[0].match(regex) !== null) {
                return true;
            } else {
                return false;
            }
        } else {
            return false;
        }
    }

    validateEmail(email) {
        return String(email)
            .toLowerCase()
            .match(
                /^(([^<>()[\]\\.,;:\s@"]+(\.[^<>()[\]\\.,;:\s@"]+)*)|(".+"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/
            );
    }

    updateCandidateData(inputParams, candidateDataId) {
        return new Promise(async (resolve, reject) => {
            try {
                if (inputParams && candidateDataId) {
                    let validationError = await this.validateParams(inputParams);
                    if (!validationError) {
                        let fullName = await this.getFullName(inputParams, candidateDataId);
                        if (fullName) {
                            inputParams.full_name = fullName;
                        }

                        let createCandidateResponse = this.candidateModel.updateCandidateData(inputParams, candidateDataId);
                        resolve(createCandidateResponse);
                    } else {
                        resolve(validationError);
                    }
                } else {
                    console.log("updateCandidateData no_input_params");
                    resolve("no_input_params");
                }
            } catch (error) {
                console.log("updateCandidateData " + error);
                reject(error);
            }
        })
    }

    async getFullName(inputParams, candidateDataId) {
        if (inputParams['first_name'] != null || inputParams['last_name'] != null || inputParams['middle_name'] != null) {
            let canidateDataToUpdate = await this.getCandidateDataByCandidateId(candidateDataId);
            if (canidateDataToUpdate) {
                let firstName = canidateDataToUpdate.first_name;
                let middleName = canidateDataToUpdate.middle_name;
                let lastName = canidateDataToUpdate.last_name;

                if (inputParams.first_name != null) {
                    firstName = inputParams.first_name;
                }
                if (inputParams.middle_name != null) {
                    middleName = inputParams.middle_name;
                }
                if (inputParams.last_name != null) {
                    lastName = inputParams.last_name;
                }
                let fullName = firstName + " " + middleName + " " + lastName;
                return fullName;
            }
        }
        return null;
    }

    async getDependantDataByExternalSubId(external_subscriber_id) {
        try {
            const candidateDataFound = await this.candidateModel.getDependantDataByExternalSubId(external_subscriber_id);
            return candidateDataFound;
        } catch (error) {
            console.log(`Could not fetch  Dependant Data ${error}`);
        }

    }

    async getDelegateByExternalSubId(externalSubscriberId) {
        try {
            const candidateDataFound = await this.candidateModel.getDelegateByExternalSubId(externalSubscriberId);
            return candidateDataFound;
        } catch (error) {
            console.log(`Could not fetch  Delegate Data ${error}`);
        }
    }

    async getHepCandidatesByParents(partner_name, mobile_phone) {
        try {
            const candidateDataFound = await this.candidateModel.getDelegateByMobile(partner_name, mobile_phone);
            return candidateDataFound;
        } catch (error) {
            console.log(`Could not fetch  Delegate Member Data ${error}`);
        }
    }

    getAllCandidateData(inputParams) {
        return new Promise((resolve, reject) => {
            try {
                let getAllCandidateResponse = this.candidateModel.getAllCandidateData(inputParams);
                resolve(getAllCandidateResponse);
            } catch (error) {
                reject(error);
            }
        })
    }

    getCandidateByUcmMemberId(inputParams) {
        return new Promise((resolve, reject) => {
            try {
                let getCandidateResponse = this.candidateModel.getCandidateByUcmMemberId(inputParams);
                resolve(getCandidateResponse);
            } catch (error) {
                reject(error);
            }
        })
    }

    getCandidateDataByCandidateId(candidateDataId) {
        return new Promise(async (resolve, reject) => {
            try {
                if (candidateDataId) {
                    let getCandidateResponse = this.candidateModel.getCandidateDataByCandidateId(candidateDataId);
                    resolve(getCandidateResponse);
                } else {
                    reject("no_input_params");
                }
            } catch (error) {
                reject(error);
            }
        });
    }

    async getBundleIdFromPlan(customerId, planName) {
        let bundleServices = new BundleService(this.sequelize);
        let bundlePlanMappingService = new BundlePlanMappingService(this.sequelize);
        let bundleIdList = await bundleServices.getAllBundleIds(customerId);

        if (bundleIdList != null) {
            for (const bundle of bundleIdList) {
                var bundlePlanMapping = await bundlePlanMappingService.getBundlePlanMappingByBundleId(bundle.bundle_id)
                if (bundlePlanMapping != null) {
                    console.log("PlanName==> " + bundlePlanMapping.plan_name);
                    if (bundlePlanMapping.plan_name == planName) {
                        return bundle.bundle_id;
                    }
                }
            }
        }
        return null;
    }

    async getSubscriberFromExtsubId(external_subscriber_id) {
        try {
            const candidateDataFound = await this.candidateModel.getCandidateSubscriber(external_subscriber_id);
            console.log("candidate data found");
            return candidateDataFound;
        } catch (error) {
            console.log(`Could not fetch  Candidate Data ${error}`);
        }
    }

    async getSubscriberForDependent(external_subscriber_id) {
        try {
            const candidateDataFound = await this.candidateModel.getCandidateSubscriberForDependent(external_subscriber_id);
            console.log("candidate data found");
            return candidateDataFound;
        } catch (error) {
            console.log(`Could not fetch  Candidate Data ${error}`);
        }
    }

    async getCandidateDataByExternalId(external_id) {
        try {
            const candidateDataFound = await this.candidateModel.getCandidateDataByExternalId(external_id);
            return candidateDataFound;
        } catch (error) {
            console.log(`Could not fetch  Candidate Data ${error}`)
        }
    }

    async getStudentByExtsubId(external_subscriber_id) {
        try {
            const candidateDataFound = await this.candidateModel.getStudentByExtsubId(external_subscriber_id);
            return candidateDataFound;
        } catch (error) {
            console.log(`Could not fetch  Student Data ${error}`)
        }
    }

    getCandidateDataByExternalSubscriberId(inputParams) {
        return new Promise(async (resolve, reject) => {
            try {
                if (inputParams && inputParams.external_subscriber_id) {
                    let getCandidateResponse = this.candidateModel.getCandidateDataById(inputParams);
                    resolve(getCandidateResponse);
                } else {
                    reject("no_input_paramsss")
                }
            } catch (error) {
                reject(error);
            }
        });
    }

    searchCandidate(inputParams) {
        return new Promise(async (resolve, reject) => {
            try {
                let searchCanidateResponse = this.candidateModel.getAllCandidateData(inputParams);
                resolve(searchCanidateResponse);
            } catch (error) {
                reject(error);
            }
        });
    }

    async bulkCreateCandidateData(paramsList, partnerName, insertedResponse) {
        console.log("insertedResponse ", insertedResponse)
        try {
            let errorList = []
            let candidateCreateList = []
            let candidateUpdateList = []
            let candidateCreatePromoteList = []
            let memberUpdateList = [];
            let memberProfileUpdateList = [];
            let delegateUpdateList = [];
            let promotedList = []
            let source = partnerName.toLowerCase()

            if (source == global_constants.CDPHP_APP_NAME || source == global_constants.MVP_APP_NAME) {
                let customerId = await this.getCustomerId(source)
                if (customerId) {
                    let response = await this.iterateMvp(source, paramsList, customerId)
                    candidateCreateList.push(...response.candidateCreateList)
                    candidateUpdateList.push(...response.candidateUpdateList)
                    candidateCreatePromoteList.push(...response.candidateCreatePromoteList)
                    memberUpdateList.push(...response.memberUpdateList);
                    memberProfileUpdateList.push(...response.memberProfileUpdateList);
                    console.log("candidateCreatePromoteList", JSON.stringify(candidateCreatePromoteList))
                } else {
                    errorList.push(`Customer not found for ${source}`)
                    console.log("Customer not found")
                }
            } else if (source == global_constants.HEP) {
                let response = await this.iterateHep(paramsList, source)
                candidateCreateList.push(...response.candidateCreateList)
                candidateUpdateList.push(...response.candidateUpdateList)
                candidateCreatePromoteList.push(...response.candidateCreatePromoteList)
                memberUpdateList.push(...response.memberUpdateList);
                memberProfileUpdateList.push(...response.memberProfileUpdateList);
                delegateUpdateList.push(...response.delegateUpdateList)
                errorList.push(...response.errorList)
            } else if (source == global_constants.HINT) {
                let response = await this.iterateHint(paramsList)
                candidateCreateList.push(...response.candidateCreateList)
            } else {
                let response = await this.iterateBulkCandidate(paramsList)
                candidateCreateList.push(...response.candidateCreateList)
            }
            //for binding account owner name 
            if (source == global_constants.CDPHP_APP_NAME || source == global_constants.MVP_APP_NAME || source == global_constants.HINT) {
                for (let candidate of candidateCreateList) {
                    //find account owner by finding external subscriber id equal to externalid
                    //not for hep
                    let subscriber = candidateCreateList.find(item => item.external_id == candidate.external_subscriber_id)
                    candidate.account_owner_name = subscriber && subscriber.full_name ? subscriber.full_name : ""
                }
            }

            try {
                if (candidateCreateList.length != 0) {
                    await this.candidateModel.bulkCreateCandidateData(candidateCreateList);
                }
                if (candidateCreatePromoteList.length != 0) {
                    // promote the newly added eligibility data
                    try {
                        let createToPromoteRes = await this.candidateModel.bulkCreateCandidateData(candidateCreatePromoteList);
                        console.log("createToPromoteRes", createToPromoteRes)
                        for (let promoteCandidate of createToPromoteRes) {
                            let promote = await this.promoteService.promoteUser({ candidate_data_id: promoteCandidate.candidate_data_id });
                            if (promote && promote.data && promote.data.subscriber_member_id_array) {
                                let promoteArray = JSON.parse(promote.data.subscriber_member_id_array)
                                let extractedObj = promoteArray.find(o => o.candidate_data_id === promoteCandidate.candidate_data_id);
                                promotedList.push(extractedObj)
                            }
                        }
                    } catch (error) {
                        console.log("error", error)
                    }
                }

                let updateArray = []

                if (promotedList.length != 0) {
                    console.log("promotedList", JSON.stringify(promotedList))
                    updateArray.push(...promotedList)
                }
                if (candidateUpdateList.length != 0) {
                    let bupdateArray = await this.bulkUpdateCandidateData(candidateUpdateList, this.candidateModel)
                    updateArray.push(...bupdateArray)
                }
                if (memberUpdateList.length != 0) {
                    await this.bulkUpdateMemberData(memberUpdateList, this.memberModel)
                }
                if (memberProfileUpdateList.length != 0) {
                    await this.bulkUpdateMemberProfileData(memberProfileUpdateList, this.memberProfileModel)
                }
                if (delegateUpdateList.length != 0) {
                    await this.bulkUpdateDelegateData(delegateUpdateList, this.delegateModel)
                }
                if (updateArray.length != 0) {
                    // send updated array to queue to update in patient db
                    let filteredIdArray = []
                    for (let i of updateArray) {
                        if (i.ucm_member_id != null) {
                            filteredIdArray.push(i)
                        }
                    }
                    let execResp = await this.executePostTasksQueue(filteredIdArray)
                    console.log('execResp', execResp)
                }
            } catch (error) {
                console.log("error==>> ", error)
                errorList.push(error)
            }
            return { status: "success", error_list: errorList }
        } catch (error) {
            console.log(`Could not create candidate Data ${error}`);
            return { error_list: [error] };
        }
    }

    bulkUpdateCandidateData(candidateUpdateList, candidateModel) {
        console.log("bulkUpdateCandidateData==>> ", JSON.stringify(candidateUpdateList))
        return new Promise(async (resolve, reject) => {
            try {
                const promises = candidateUpdateList.map(function (candidateData) {
                    return candidateModel.updateCandidateData(candidateData, candidateData.candidate_data_id);
                });
                let response = await Promise.allSettled(promises);
                let filteredArray = response.filter(res => res.status === 'fulfilled').map(candidateData => candidateData.value);
                resolve(filteredArray)
            } catch (error) {
                reject(error)
            }
        })
    }

    bulkUpdateMemberData(memberUpdateList, memberModelModel) {
        return new Promise(async (resolve, reject) => {
            try {
                const promises = memberUpdateList.map(function (memberData) {
                    return memberModelModel.updateMemberDataById(memberData, memberData.member_id);
                });
                let response = await Promise.allSettled(promises);
                let filteredArray = response.filter(res => res.status === 'fulfilled').map(memberData => memberData.value);
                resolve(filteredArray)
            } catch (error) {
                reject(error)
            }
        })
    }

    bulkUpdateMemberProfileData(memberProfileUpdateList, memberProfileModelModel) {
        return new Promise(async (resolve, reject) => {
            try {
                const promises = memberProfileUpdateList.map(function (memberProfileData) {
                    return memberProfileModelModel.updateMemberProfileDataById(memberProfileData, memberProfileData.profile_id);
                });
                let response = await Promise.allSettled(promises);
                let filteredArray = response.filter(res => res.status === 'fulfilled').map(memberProfileData => memberProfileData.value);
                resolve(filteredArray)
            } catch (error) {
                reject(error)
            }
        })
    }

    bulkUpdateDelegateData(delegateUpdateList, delegateModelModel) {
        return new Promise(async (resolve, reject) => {
            try {
                const promises = delegateUpdateList.map(function (delegateData) {
                    return delegateModelModel.updateDelegateDataById(delegateData, delegateData.delegate_id);
                });
                let response = await Promise.allSettled(promises);
                let filteredArray = response.filter(res => res.status === 'fulfilled').map(delegateData => delegateData.value);
                resolve(filteredArray)
            } catch (error) {
                reject(error)
            }
        })
    }

    iterateBulkCandidate(paramsList) {
        let errorList = []
        let candidateCreateList = []
        let candidateUpdateList = []

        return new Promise(async (resolve, reject) => {
            try {
                for (const params of paramsList) {
                    let candidateDataObj = candidateDataMappingService.paramCandidateDataMapping(params);
                    candidateCreateList.push(candidateDataObj);

                }
                resolve({ errorList, candidateCreateList, candidateUpdateList });
            } catch (error) {
                reject(error);
            }
        });
    }

    iterateHint(paramsList) {
        let candidateCreateList = []

        return new Promise(async (resolve, reject) => {
            try {
                for (const params of paramsList) {
                    console.log("iterateHint", JSON.stringify(params))
                    let candidateDataObj = await candidateDataMappingService.paramCandidateDataMappingHint(params, this.customerService);
                    let candidateDataFound = await this.candidateModel.getCandidateDataByExternalId({ external_id: candidateDataObj.external_id });
                    if (!candidateDataFound) {
                        candidateCreateList.push(candidateDataObj);
                    }
                }
                resolve({ candidateCreateList });
            } catch (error) {
                reject(error);
            }
        });
    }

    iterateHep(paramsList, source) {
        let errorList = []
        let candidateCreateList = []
        let candidateUpdateList = []
        let candidateCreatePromoteList = []
        let memberUpdateList = []
        let memberProfileUpdateList = []
        let delegateUpdateList = []
        let studentIdArray = []
        let externalSubscriberIdFoundList = []
        let delegateIdList = []
        let delegateFoundList = []
        let customerNameIdMap = []


        return new Promise(async (resolve, reject) => {
            try {


                try {
                    for (let i of paramsList) {
                        let customerId = null

                        let institutionName = i['Institution Name']

                        let cacheCustID = customerNameIdMap.filter(x => x.hasOwnProperty(institutionName)).map(x => x[institutionName]);

                        if (cacheCustID.length == 0) {
                            customerId = await this.getCustomerId(institutionName)
                            if (customerId) {
                                customerNameIdMap.push({ [institutionName]: customerId })
                            }
                            console.log('customerId from query', customerId)

                        } else {

                            customerId = cacheCustID[0]
                            console.log('customerId from cache', customerId)

                        }
                        if (customerId) {
                            console.log('customerId', customerId)
                            let externalId = customerId + '_' + i['Student ID']
                            studentIdArray.push(externalId)
                            console.log('externalId', externalId)
                        }

                    }

                    //     console.log("CustStudentID", studentIdArray)

                    //     externalSubscriberIdFoundList = await this.candidateModel.getAllCandidatesByExternalId({
                    //         [Sequelize.Op.or]: [{ external_subscriber_id: studentIdArray }, { external_id: studentIdArray }]

                    //     });

                    //     console.log('externalSubscriberIdFoundList', externalSubscriberIdFoundList.splice(0, 2))
                } catch (error) {
                    console.log("Error", error)
                }


                for (let params of paramsList) {
                    let institutionName = params['Institution Name']

                    let customerIdArr = customerNameIdMap.filter(x => x.hasOwnProperty(institutionName)).map(x => x[institutionName]);

                    let customerId = null

                    if (customerIdArr && customerIdArr.length != 0) {
                        customerId = customerIdArr[0]
                    }
                    console.log("customerId", customerId)


                    // let customerId = await this.getCustomerId(institutionName)

                    if (customerId) {
                        let candidateDataArray = await candidateDataMappingService.paramCandidateDataMappingHep(params, customerId);
                        for (let candidateData of candidateDataArray) {
                            let inputHash = CommonService.createHashKey(candidateData)

                            if (!candidateData.is_subscriber) {
                                //parent
                                let extSubId = candidateData.external_subscriber_id ? candidateData.external_subscriber_id : null
                                if (extSubId != null) {
                                    let candidateDataFound = await this.candidateModel.getCandidateDataByExternalSubId({ external_subscriber_id: extSubId });
                                    // let candidateDataFound = externalSubscriberIdFoundList.find(o => o.external_subscriber_id == extSubId);
                                    console.log("candidateDataFound isNotSub", candidateDataFound)


                                    if (!candidateDataFound) {
                                        candidateData.hash_key = inputHash
                                        candidateCreateList.push(candidateData);
                                    } else {
                                        /// if hash not same then update
                                        if (candidateDataFound.hash_key != inputHash) {
                                            candidateData.hash_key = inputHash
                                            delete candidateData["customer_id"]
                                            delete candidateData["external_subscriber_id"]
                                            candidateData.candidate_data_id = candidateDataFound.candidate_data_id
                                            candidateUpdateList.push(candidateData)
                                            console.log('Hash not same ==> so update', candidateDataFound.first_name)
                                            let delegateObject = {};
                                            if (candidateDataFound.ucm_delegate_id != null) {
                                                let delegateDataFound = delegateFoundList.find(o => o.ucm_delegate_id == candidateDataFound.ucm_delegate_id);
                                                console.log('delegate found====>', delegateDataFound)
                                                // let delegateDataFound = await this.delegateModel.getDelegateDataByUcmDelegateId({ delegate_id: `${candidateDataFound.ucm_delegate_id}` });
                                                delegateObject = candidateDataMappingService.paramDelegateDataMapping(candidateData, source);
                                                delegateObject.delegate_id = delegateDataFound.delegate_id
                                                delegateUpdateList.push(delegateObject)
                                            }
                                        } else {
                                            console.log('Hash same ==> no update', candidateDataFound.first_name)
                                        }
                                    }
                                }
                            } else {
                                let extId = candidateData.external_id ? candidateData.external_id : null
                                if (extId != null) {
                                    let candidateDataFound = await this.candidateModel.getCandidateDataByExternalId({ external_id: extId });
                                    // let candidateDataFound = externalSubscriberIdFoundList.find(o => o.external_id == extId);
                                    console.log("candidateDataFound isSub", candidateDataFound)


                                    if (!candidateDataFound) {
                                        candidateData.hash_key = inputHash

                                        try {
                                            //if parent exists then promote
                                            let parentObj = candidateDataArray.find(o => o.external_subscriber_id === extId);
                                            console.log("Parent obkj==>>>>>>>", parentObj)
                                            let delegateObj = await this.delegateModel.getDelegateBy({ mobile_phone: "" + parentObj.mobile_phone });
                                            console.log("Delegate arr==>>>>>>>", delegateObj)

                                            if (parentObj && delegateObj) {
                                                console.log('Added to promote=====> ')
                                                candidateCreatePromoteList.push(candidateData);
                                            } else {
                                                candidateCreateList.push(candidateData);
                                            }
                                        } catch (error) {
                                            console.log("Error ", error)
                                        }

                                    } else {
                                        /// if hash not same then update
                                        if (candidateDataFound.hash_key != inputHash) {
                                            candidateData.hash_key = inputHash
                                            delete candidateData["customer_id"]
                                            delete candidateData["external_id"]
                                            candidateData.candidate_data_id = candidateDataFound.candidate_data_id
                                            candidateUpdateList.push(candidateData)
                                            let memberObj = {};
                                            let memberProfileObj = {};
                                            if (candidateDataFound.ucm_member_id != null) {
                                                let memberDataFound = await this.memberModel.getMemberDataByUcmMemberId({ ucmMemberId: `${candidateDataFound.ucm_member_id}` });
                                                let memberProfileDataFound = await this.memberProfileModel.getMemberProfileById({ member_id: `${candidateDataFound.ucm_member_id}` });

                                                memberObj = candidateDataMappingService.paramMemberDataMapping(candidateData, source);
                                                memberProfileObj = candidateDataMappingService.paramMemerProfileDataMapping(candidateData, source);

                                                memberObj.member_id = memberDataFound.member_id
                                                memberUpdateList.push(memberObj)

                                                memberProfileObj.profile_id = memberProfileDataFound.profile_id
                                                memberProfileUpdateList.push(memberProfileObj)
                                            }
                                            console.log('Hash not same ==> so update', candidateDataFound.first_name)
                                        } else {
                                            console.log('Hash same ==> no update', candidateDataFound.first_name)
                                        }
                                    }
                                }
                            }
                        }
                    } else {
                        errorList.push("Customer not found")
                        console.log("Customer not found")
                    }
                }
                resolve({ errorList, candidateCreateList, candidateUpdateList, candidateCreatePromoteList, memberUpdateList, memberProfileUpdateList, delegateUpdateList });
            } catch (error) {
                reject(error);
            }
        });
    }

    iterateMvp(source, paramsList, customerId) {
        let errorList = []
        let candidateCreateList = []
        let candidateCreatePromoteList = []
        let candidateUpdateList = []
        let memberUpdateList = []
        let memberProfileUpdateList = []

        let mpiList = []
        let mpiFoundList = []

        let externalSubscriberIdList = []
        let externalSubscriberIdFoundList = []

        return new Promise(async (resolve, reject) => {
            try {
                for (let i = 0; i < paramsList.length; i++) {
                    let mpi = paramsList[i]['MPI_ID']
                    if (mpi) {
                        mpiList.push(mpi + '')
                    }
                }

                for (let i = 0; i < paramsList.length; i++) {
                    let externalSubscriberId = paramsList[i]['Subscriber ID'] + '00'

                    if (externalSubscriberId && !externalSubscriberIdList.includes(externalSubscriberId)) {
                        externalSubscriberIdList.push(externalSubscriberId)
                    }
                }

                console.log("mpiList", mpiList)
                mpiFoundList = await this.candidateModel.getCandidatesByMpiList({ mpi: mpiList });
                console.log("mpiFoundList", mpiFoundList.slice(0, 2))

                console.log("externalSubscriberIdList", externalSubscriberIdList)
                externalSubscriberIdFoundList = await this.candidateModel.getAllCandidatesByExternalId({ external_subscriber_id: externalSubscriberIdList });
                console.log("externalSubscriberIdFoundList", JSON.stringify(externalSubscriberIdFoundList))


                for (let params of paramsList) {
                    let candidateDataObj = {};
                    if (!params['MPI_ID']) {
                        console.log("mpi not provided in sheet:", candidateDataObj);
                        continue
                    }
                    candidateDataObj = candidateDataMappingService.paramCandidateDataMappingMvp(params, source);
                    candidateDataObj.customer_id = customerId

                    let inputHash = CommonService.createHashKey(candidateDataObj)

                    // check if mpi already exists in db
                    let candidateDataFound = mpiFoundList.find(o => o.mpi == candidateDataObj.mpi);
                    console.log('candidateDataFound', candidateDataFound)
                    if (!candidateDataFound) {
                        //new entry
                        console.log("not found candidate mpi:", candidateDataObj.mpi);
                        console.log("not found candidate mpi:", candidateDataObj.external_subscriber_id);
                        candidateDataObj.hash_key = inputHash

                        // check if its a new dependent
                        let subscriberData = externalSubscriberIdFoundList.find(o => o.external_id == candidateDataObj.external_subscriber_id && o.is_subscriber == true);

                        if (subscriberData && subscriberData?.ucm_member_id) {
                            console.log("subscriberData", subscriberData)
                            //if subscriber already promoted
                            candidateCreatePromoteList.push(candidateDataObj)
                        } else {
                            candidateCreateList.push(candidateDataObj);
                        }

                    } else {
                        // if hash not same then update
                        if (candidateDataFound.hash_key != inputHash) {
                            candidateDataObj.hash_key = inputHash
                            delete candidateDataObj["mpi"]

                            candidateDataObj.candidate_data_id = candidateDataFound.candidate_data_id
                            candidateUpdateList.push(candidateDataObj)
                            console.log('Hash different ==> to update')
                            let memberObj = {};
                            let memberProfileObj = {};
                            if (candidateDataFound.ucm_member_id != null) {
                                let memberDataFound = await this.memberModel.getMemberDataByUcmMemberId({ ucmMemberId: `${candidateDataFound.ucm_member_id}` });
                                let memberProfileDataFound = await this.memberProfileModel.getMemberProfileById({ member_id: `${candidateDataFound.ucm_member_id}` });

                                memberObj = candidateDataMappingService.paramMemberDataMapping(candidateDataObj, source);
                                memberProfileObj = candidateDataMappingService.paramMemerProfileDataMapping(candidateDataObj, source);

                                memberObj.member_id = memberDataFound.member_id
                                memberUpdateList.push(memberObj)

                                memberProfileObj.profile_id = memberProfileDataFound.profile_id
                                memberProfileUpdateList.push(memberProfileObj)
                            }
                        } else {
                            console.log('Hash same ==> no update')
                        }
                    }
                }
                resolve({ errorList, candidateCreateList, candidateUpdateList, candidateCreatePromoteList, memberUpdateList, memberProfileUpdateList });
            } catch (error) {
                reject(error);
            }
        });
    }



    async updateCandidateDataFromHint(params) {
        //   service function to update a record in the candidateData table from hint
        //  * params - {firstName,lastName,birthDate}
        console.log("inside updateCandidateDataFromHint service");
        return new Promise(async (resolve, reject) => {
            // (async () => {
            try {
                // get candidate record from candidate_data table
                let candidateDataFound = await this.candidateModel.getCandidateDataByExternalId(params);
                console.log("candidate data found", candidateDataFound);

                //compare {candidateDataFound} and {params}
                if (candidateDataFound && _.isEqual(params, candidateDataFound)) {
                    // update not required if equal
                    console.log('hint data not updated in candidate_data table');
                    resolve(params);
                } else {
                    //update required if equal
                    let updatedCandidateData = await this.candidateModel.updateCandidateDataFromHint(params);
                    console.log('hint data updated in candidate_data table');
                    resolve(updatedCandidateData);
                }
            } catch (error) {
                console.log('Error in updating hint data in candidate_data table', error);
                reject(error);
            }
        });

    }


    async getCustomerId(source) {
        let getCustomerResponse = await this.customerService.getCustomerData({
            customer_name: {
                [Sequelize.Op.iLike]: source
            }
        });
        return getCustomerResponse ? getCustomerResponse.customer_id ? getCustomerResponse.customer_id : null : null
    }

    executePostTasksQueue(data) {
        console.log('executePostTasksQueue', JSON.stringify(data))
        const postTasksQueue = process.env.CSV_POST_TASKS_QUEUE;
        console.log('QUEUE_NAME ', postTasksQueue)
        return new Promise((resolve, reject) => {
            try {
                var params = {
                    MessageBody: JSON.stringify({ update_list: data }),
                    QueueUrl: postTasksQueue,
                    MessageGroupId: "" + new Date().getTime()

                };
                const AWS = require('aws-sdk');
                var sqs = new AWS.SQS();
                sqs.sendMessage(params, function (err, data) {
                    if (err) {
                        console.log(err, err.stack);
                        reject(err);
                    } else {
                        console.log("success queue", JSON.stringify(data))
                        resolve(data);
                    }
                });
            } catch (error) {
                console.log("errr=>>>> ", error)
                resolve(error);

            }
        });
    }

}

module.exports = CandidateService;