var appsApi = {},
    common = require('./../../utils/common.js'),
    fs = require('fs'),
    logger = require('../../../logger'),
    {QueueClientEnum } = require('src_typescript_appice-core/dist/queue/index.js'),
    {EXPORTS,TOPIC } = require('src_typescript_appice-core/dist/constants/exports.js'),
    {LogProviderFactory, LogProviderType} = require('src_typescript_appice-core/dist/observability/index.js');
    const { Encryption } = require('src_typescript_appice-core/dist/utils/security/encryption.js')  
    mongojs = require('mongojs'),
    Minio = require('minio'),
    request = require('request'),
    cheerio = require('cheerio'),
    mail = require('./mail.js'),
    uuid = require('uuid'),
    semusiConfig = require('./../../config'),
    cacheApi = require('./../../utils/semusi.cache.js'),
    dataProducer = require('../../utils/common.data.producer.js')
    moment = require('moment'),
    validate = require('../../constants/constants'),
    psqlUtils = require('./../../utils/pgsql.utils'),
    regex = require('./../../utils/regex.js'),
    path = require('path');
    const pem = require('pem');
    var db;
    if(semusiConfig.IsProduction){
        db = mongojs(semusiConfig.mongodb.productionUrl, []);
    }
    else{
        db = mongojs(semusiConfig.mongodb.localUrl,  []);
    }

(function (appsApi) {


    /**
  * Asynchronously retrieves site settings from the database.
  * This function retrieves the site settings from db ,based on the CDN URL.
  * 
  * @returns {Promise<object>} A Promise that resolves with the site settings object if successful, or rejects with an error if unsuccessful.
  */

    async function retrieveSiteSettings() {
        return new Promise((resolve, reject) => {
            // Querying the 'site_settings' collection in the database to find the settings associated with the configured CDN URL.
            common.db.collection('site_settings').findOne({ "cdn": semusiConfig.serverDomain }, (err, sitesettings) => {
                if (err) {
                    reject(err);
                } else {
                    // If no error occurs and the settings are found, resolve the Promise with the retrieved site settings,
                    // or an empty object if no settings are found.
                    resolve(sitesettings || {});
                }
            });
        });
    }


    //get kill switch state
    appsApi.getKillSwitchState = function (params) {
        let argProps = {
            'app_id': {
                'required': true,
                'type': 'String',
            },
        };
        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
        //Check kill switch setting exist for app_id and get from 
        common.db.collection('apps').findOne({ "_id": common.db.ObjectID(params.qstring.args.app_id) }, { kill_switch: 1 }, (err, data) => {
            if (!err && data) {
                common.returnOutput(params, data);
            } else {
                common.returnOutput(params, err);
            }
        })
    }
       //save campaign setting in the campaigns
       appsApi.savefeaturesSettings = function( params ) {
        let argProps = {
           'app_id': {
               'required': true,
               'type': 'String',
           },
           'demographicsState': {
               'required': true,
               'type': 'Boolean'
           },
           // Audience Exports should be setting based
           'audience_exports':{
            'required': true,
            'type': 'Boolean'
            },
           'ab_testing_variant': {
            'required': true,
            'type': 'Boolean'
          },
          'language_variant': {
              'required': true,
              'type': 'Boolean'
           },
           'usageState': {
            'required': true,
            'type': 'Boolean'
           },
           'appInboxReports': {
              'required': true,
              'type': 'Boolean'
          },
          'maskedDids': {
              'required': true,
              'type': 'Boolean'
          },
           'reportsState': {
               'required': true,
               'type': 'Boolean'
           },
           'churnState': {
              'required': true,
              'type': 'Boolean'
          },
          'expImgState': {
              'required': true,
              'type': 'Boolean'
          },
          'advPushState': {
              'required': true,
              'type': 'Boolean'
          },
          'campExceptPushState': {
              'required': true,
              'type': 'Boolean'
          },
          'transState': {
              'required': true,
              'type': 'Boolean'
          },
          'userprofileImgState':{
              'required': true,
              'type': 'Boolean'
          },
          'optimisationState':{
              'required': true,
              'type': 'Boolean'
          },
          'appInboxDeliveryState':{
              'required': true,
              'type': 'Boolean'
           },
          'funnelPeriod':{
              'required': true,
              'type': 'Boolean'
          },
          'email_editor':{
              'required': true,
              'type': 'Boolean'
          },
          'documentationState':{
              'required': true,
              'type': 'Boolean'
           },
          'outsideTheAppState':{
              'required': true,
              'type': 'Boolean'
          },
          'campaignDetailsCsv':{
              'required': true,
              'type': 'Boolean'
          },
          'hidegranularMonetaryCACLTV':{
              'required': true,
              'type': 'Boolean'
          },
          'competingApps':{
            'required': true,
            'type': 'Boolean'
        },
          'embedCustomField':{
               'required': true,
               'type': 'Boolean'
          },
           'whowhatwherewhen':{
              'required': true,
              'type': 'Boolean'
          },
           'deepLinkUrl':{
              'required': true,
              'type': 'Boolean'
         },
           'category':{
             'required': true,
             'type': 'Boolean'
         },
             'utmBuilder':{
             'required': true,
             'type': 'Boolean'
         },
            'landingPageView':{
            'required': true,
            'type': 'Boolean'
         },
            'funnelLaunchbtn':{
              'required': true,
              'type': 'Boolean'
           },
           'cmpInsertInToPG': {
              'required': true,
              'type': 'Boolean',
           },
           'advancedFreq': {
             'required': true,
             'type': 'Boolean',
         },
         'launchCampFreq': {
            'required': true,
            'type': 'Boolean',
         },
         'actionbuttonSettings': {
            'required': true,
            'type': 'Boolean',
         },
         'specialattributes': {
            'required': true,
            'type': 'Boolean',
         },
         'conversionSettings': {
            'required': true,
            'type': 'Boolean',
         },
         'journeynavsettings': {
            'required': true,
            'type': 'Boolean',
         },
         'setupAppsSettings': {
            'required': true,
            'type': 'Boolean',
         },
         'delayLauch': {
            'required': true,
            'type': 'Boolean',
         },
         'removeWeb': {
            'required': true,
            'type': 'Boolean',
         },
         'liveEventSett': {
            'required': true,
            'type': 'Boolean',
         },
         'schedulerSett': {
            'required': true,
            'type': 'Boolean',
         },
         'cSegPhoneEmail': {
            'required': true,
            'type': 'Boolean',
         },
         'deviceAttributes': {
            'required': true,
            'type': 'Boolean',
         },
         'appUsageAttributes': {
            'required': true,
            'type': 'Boolean',
         },
         'allowList': {
            'required': true,
            'type': 'Boolean',
         }
     };
    
     params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
       if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
           common.returnMessage(params, 400, 'Not enough args'); 
           return false;
       }
  
       let updateItem = {
         features: {
           demographics: params.qstring.args.demographicsState,
           usage: params.qstring.args.usageState,
           reports: params.qstring.args.reportsState,
           appInboxReports: params.qstring.args.appInboxReports,
           maskedDids : !params.qstring.args.maskedDids,
           audience_exports : params.qstring.args.audience_exports,
           churn : params.qstring.args.churnState,
           expImg : params.qstring.args.expImgState,
           advPush : params.qstring.args.advPushState,
           campExceptPush : params.qstring.args.campExceptPushState,
           transactionalCamp : params.qstring.args.transState,
           userprofileImg : params.qstring.args.userprofileImgState,
           optimisation : params.qstring.args.optimisationState,
           appInboxAndDeliveryType : params.qstring.args.appInboxDeliveryState,
           funnelPeriod : params.qstring.args.funnelPeriod,
           documentation : params.qstring.args.documentationState,
           outsideTheApp : params.qstring.args.outsideTheAppState,
           campaignDetailsCsv : params.qstring.args.campaignDetailsCsv,
           hidegranularMonetaryCACLTV : params.qstring.args.hidegranularMonetaryCACLTV,
           competingApps : params.qstring.args.competingApps,
           embedCustomField : params.qstring.args.embedCustomField,
           whowhatwherewhen : params.qstring.args.whowhatwherewhen,
           deepLinkUrl : params.qstring.args.deepLinkUrl,
           category : params.qstring.args.category,
           email_editor:params.qstring.args.email_editor,
           utmBuilder : params.qstring.args.utmBuilder,
           landingPageView : params.qstring.args.landingPageView,
           ab_testing_variant: params.qstring.args.ab_testing_variant,
           language_variant:params.qstring.args.language_variant,
           funnelLaunchbtn : params.qstring.args.funnelLaunchbtn,
           cmpInsertInToPG: params.qstring.args.cmpInsertInToPG,
           advancedFreq: params.qstring.args.advancedFreq,
           launchCampFreq: params.qstring.args.launchCampFreq,  
           actionbuttonSettings: params.qstring.args.actionbuttonSettings,  
           specialattributes: params.qstring.args.specialattributes, 
           conversionSettings: params.qstring.args.conversionSettings,
           journeynavsettings: params.qstring.args.journeynavsettings,
           setupAppsSettings: params.qstring.args.setupAppsSettings,
           delayLauch: params.qstring.args.delayLauch,
           removeWeb: params.qstring.args.removeWeb,
           liveEventSett: params.qstring.args.liveEventSett,
           schedulerSett: params.qstring.args.schedulerSett,
           cSegPhoneEmail: params.qstring.args.cSegPhoneEmail,
           deviceAttributes: params.qstring.args.deviceAttributes,
           appUsageAttributes: params.qstring.args.appUsageAttributes,
           allowList: params.qstring.args.allowList

         },
       };
       common.db.collection('apps').update({"_id": common.db.ObjectID(params.qstring.args.app_id )}, {$set:updateItem }, (err, data) =>{
        
        if(err || !data) {
               common.returnMessage(params, 500, 'Something went wrong');
           }
           else{
              common.returnOutput(params, data); 
           }
       })
    }
    
  
     //save saveAppInboxSettings setting 
     appsApi.saveAppInboxSettings = function( params ) {
        let argProps = {
           'app_id': {
               'required': true,
               'type': 'String',
           },
      };
    
     params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
       if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
           common.returnMessage(params, 400, 'Not enough args'); 
           return false;
       }
    
       let updateItem = {
        AppInboxExpiry : params.qstring.args.days,
       };
       common.db.collection('apps').update({"_id": common.db.ObjectID(params.qstring.args.app_id )}, {$set:updateItem }, (err, data) =>{
        
        if(err || !data) {
               common.returnMessage(params, 500, 'Something went wrong');
           }
           else{
              common.returnOutput(params, data); 
           }
       })
    }
    
 //save saveEncryptionStatus setting
 appsApi.saveEncryptionStatus = function( params ) {
    let argProps = {
       'app_id': {
           'required': true,
           'type': 'String',
       },
  };

 params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
   if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
       common.returnMessage(params, 400, 'Not enough args'); 
       return false;
   }

   let updateItem = {
    encryptionstatus: params.qstring.args.encryptionstatus,
    encryptionkey:params.qstring.args.encryptionkey
   };
   common.db.collection('apps').update({"_id": common.db.ObjectID(params.qstring.args.app_id )}, {$set:updateItem }, async (err, data) =>{
    
    if(err || !data) {
           common.returnMessage(params, 500, 'Something went wrong');
       }
       else{
        //call the  ts-core class of encryption
        const encrypt = new Encryption(params.qstring.args.app_id);     // Instantiate the AuthManager
        await encrypt.setEncryptionKey()
          common.returnOutput(params, data); 
       }
   })
}


// Save push notifications settings
appsApi.savePushCharLimit = function(params) {
    let argProps = {
        'app_id': {
            'required': true,
            'type': 'String',
        },
    };

    params.qstring.args = (typeof(params.qstring.args)== 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
            
    if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
        common.returnMessage(params, 400, 'Not enough args'); 
        return false;
    }
    
    // Create update objects
    let updateCharLimit = {
        hl: params.qstring.args.hl,
        dl: params.qstring.args.dl,
        edl: params.qstring.args.edl
    };
    
    let updateDropdown = params.qstring.args.getAppDropdown;
    common.db.collection('apps').update(
        { "_id": common.db.ObjectID(params.qstring.args.app_id) },
        { 
            $set: {
                "pushNotificationCharLimit": updateCharLimit,
                "getAppEnabled": updateDropdown
            }
        },
        (err, data) => {
            if (err || !data) {
                common.returnMessage(params, 500, 'Something went wrong');
            } else {
                common.returnOutput(params, data); 
            }
        }
    );
}


//get char limit
appsApi.getPushCharLimit = function (params) {
    common.db.collection('apps').findOne({ "_id": common.db.ObjectID(params.qstring.app_id) }, {pushNotificationCharLimit : 1 }, (err, data) => {
        if (!err && data) {
            common.returnOutput(params, data);
        } else {
            common.returnOutput(params, err);
        }
    })
}

//save partner info
appsApi.savePartnerInfo = function (params) {
    let argProps = {
        'app_id': {
            'required': true,
            'type': 'String',
        },
    };

    params.qstring.args = (typeof (params.qstring.args) === 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
    if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }

    let partnerDataArray = params.qstring.args.partnerData;

    if (!Array.isArray(partnerDataArray)) {
        common.returnMessage(params, 400, 'Invalid partnerData format');
        return false;
    }

    // Fetch existing partnerInfo from the database
    common.db.collection('apps').findOne({ "_id": common.db.ObjectID(params.qstring.args.app_id) }, (err, appData) => {
        if (err || !appData) {
            common.returnMessage(params, 500, 'Something went wrong');
            return false;
        }

        let existingPartnerData = appData.partnerInfo && appData.partnerInfo.partnerData ? appData.partnerInfo.partnerData : [];

        // Create a map of existing partner names to partnerIds
        let partnerIdMap = existingPartnerData.reduce((map, partner) => {
            map[partner.partnerName] = partner.partnerId;
            return map;
        }, {});

        // Generate unique partnerId for each new partner or use existing partnerId if available
        partnerDataArray.forEach((partner) => {
            if (!partnerIdMap[partner.partnerName]) {
                partner.partnerId = uuid.v4();
            } else {
                partner.partnerId = partnerIdMap[partner.partnerName];
            }
        });

        let updateItem = {
            partnerInfo: {
                partnerData: partnerDataArray
            }
        };

        common.db.collection('apps').update({ "_id": common.db.ObjectID(params.qstring.args.app_id) }, { $set: updateItem }, (err, data) => {
            if (err || !data) {
                common.returnMessage(params, 500, 'Something went wrong');
            } else {
                common.returnOutput(params, data);
            }
        });
    });
};

//get partner info
appsApi.getPartnerInfo = function (params) {
    common.db.collection('apps').findOne({ "_id": common.db.ObjectID(params.qstring.app_id) }, (err, data) => {
        if (!err && data) {
            common.returnOutput(params, data);
        } else {
            common.returnOutput(params, err);
        }
    })
}
//save allow list settings
appsApi.saveallowListSettings = function(params) {
    let argProps = {
        'app_id': {
            'required': true,
            'type': 'String',
        },
    };

    params.qstring.args = (typeof(params.qstring.args) == 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
    if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }

    let allowList = params.qstring.args.allowList;
    if (typeof allowList === 'string') {
        allowList = allowList.split(',').map(item => item.trim());
    }

    let updateItem = {
        allowList :  allowList
    };

    const ObjectId = common.db.ObjectID;
    common.db.collection('apps').updateOne({ "_id": ObjectId(appId) }, { $set: updateItem }, (err, result) => {
        if (err) {
            logger.error("Error updating app in MongoDB:", err);
            return;
        }

        // Store object in Redis
        const redisKey = "allowlist_" + appId;
        cacheApi.setKey(redisKey, JSON.stringify(updateItem.allowList), (redisErr, redisRes) => {
            if (redisErr) {
                logger.error("Error storing obj in Redis:", redisErr);
            } else {
                logger.info("Obj stored in Redis:", redisRes);
            }
        });

        common.returnOutput(params, result);
    });
}

appsApi.saveSiteSetting = function (params) {
    let argProps = {
      sso_type: {
        required: true,
        type: "String",
      },
      is_sso: {
        required: true,
        type: "String",
      },
      samlConfig: {
        required: false,
        type: "JSON",
      },
      ldapConfig: {
        required: true,
        type: "JSON",
      },
      portal_name: {
        required: false,
        type: "String",
      },
      captcha: {
        required: false,
        type: "String",
      },
    };
    let gbl_settings = {};

    params.qstring.args =  typeof params.qstring.args == "string" ? JSON.parse(params.qstring.args) : params.qstring.args;
    if (!(gbl_settings = common.validateArgs(params.qstring.args, argProps))) {
      common.returnMessage(params, 422, "Validation error");
      return false;
    }



    // Define the query and update objects to update the 'siteS_settings' collection
    const query = { 'cdn': semusiConfig.serverDomain };
    const update = { $set: { sso_type: gbl_settings.sso_type ,is_sso :gbl_settings.is_sso, ldapConfig : gbl_settings.ldapConfig, captcha : gbl_settings.captcha} };
    // Add portal_name if is sent
    if (gbl_settings.portal_name && gbl_settings.portal_name.trim() !== '') {
        update.$set.portal_name = gbl_settings.portal_name.trim();
    }

    // Add samlConfig if it exists and is not an empty object
    if (gbl_settings.samlConfig && Object.keys(gbl_settings.samlConfig).length > 0) {
         update.$set.samlConfig = gbl_settings.samlConfig;
     }
    common.db.collection('site_settings').updateOne(query, update, { upsert: true }, (err, dataa) => {
        if (err || !dataa) {
              // If there is an error or the update fails, return an error message with a 500 status code
             common.returnMessage(params, 500, "Something went wrong");
        }else{
              // If the update is successful, return the output data
            common.returnOutput(params, dataa);
           }
    });
}



appsApi.getLogs = async function (params) {
    const page = params.qstring.args.page || 1;
    const pageSize = params.qstring.args.length;
    const searchedValue = params.qstring.args.value;
    const offset = (parseInt(page) - 1) * pageSize;
    const appid = params.qstring.args.app_id;
    const providerFactory = new LogProviderFactory();
    const provider = await providerFactory.createLogProvider(appid, LogProviderType.PG);


    try {
        let result;
        if (searchedValue) {
            result = await provider.getlog(searchedValue);
        } else {
            result = await provider.getLogs(pageSize, offset);
        }
        common.returnOutput(params, result);
    } catch (err) {
        logger.error(`Error occurred in getLogs: ${err.stack || err}`);
        common.returnOutput(params, {});
    }
};


//Save kill Switch Settings
appsApi.savekillSwitchSetting = function (params) {
    let argProps = {
        'app_id': {
            'required': true,
            'type': 'String',
        },
        'state': {
            'required': true,
            'type': 'String'
        },
        'android': {
            'required': false,
            'type': 'JSON'
        },
        'ios': {
            'required': false,
            'type': 'JSON'
        },
        'web': {
            'required': false,
            'type': 'JSON'
        },
        'baseurl': {
            'required': false,
            'type': 'String'
        }
        
    };
    params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
    if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }

    let kill_switch = {};

    if(params.qstring.args.state) {
        kill_switch.state = params.qstring.args.state;
    }
    if(params.qstring.args.android) {
        kill_switch.android = params.qstring.args.android;
    }
    if(params.qstring.args.ios) {
        kill_switch.ios = params.qstring.args.ios;
    }
    if(params.qstring.args.web) {
        kill_switch.web = params.qstring.args.web;
    }
    if(params.qstring.args.baseurl) {
        kill_switch.baseurl = params.qstring.args.baseurl;
    }

    //Check kill switch setting exist for app_id
    common.db.collection('apps').findOne({"_id": common.db.ObjectID(params.qstring.args.app_id)},{kill_switch: 1}, (err, data) => {
        if(!err && data) {
            //Update updatedAt
            kill_switch.updatedAt = common.getCurrentEpochTime();
            if(data.kill_switch != undefined){
                kill_switch.createdAt = data.kill_switch.createdAt;
            }else{
                kill_switch.createdAt = common.getCurrentEpochTime();
            }
            
        } else {
             //Update createAt & updatedAt
            kill_switch.updatedAt = common.getCurrentEpochTime();
            kill_switch.createdAt = common.getCurrentEpochTime();
        }
           //Insert data to collection
           common.db.collection('apps').update({"_id": common.db.ObjectID(params.qstring.args.app_id)},{$set:{'kill_switch': kill_switch}}, (err, data) => {
               if(!err && data) {
                   cacheApi.setKey('kill_switch_'+params.qstring.args.app_id,JSON.stringify(kill_switch));
                   common.returnOutput(params,data);
                }else{
                   common.returnOutput(params,err);
                }
           })

    })

    // kill_switch.timestamp = common.getCurrentEpochTime();
    // if(params.qstring.args.state){
    //     kill_switch.state = params.qstring.args.state;
    // }
    // if(params.qstring.args.android_ver){
    //     kill_switch.android_ver = params.qstring.args.android_ver;
    // }
    // if(params.qstring.args.ios_ver){
    //     kill_switch.ios_ver = params.qstring.args.ios_ver;
    // }
    // if(params.qstring.args.web_ver){
    //     kill_switch.web_ver = params.qstring.args.web_ver;
    // }
    //  common.db.collection('apps').update({"_id": common.db.ObjectID(params.qstring.args.app_id )}, {$set: {'kill_switch': kill_switch}}, (err,data) => {
    //      if(!err && data) {
    //         cacheApi.setKey('kill_switch_'+params.qstring.args.app_id,JSON.stringify(kill_switch));
    //         common.returnOutput(params,data);
    //      }else{
    //         common.returnOutput(params,err);
    //      }
    //  })


}


    appsApi.createApp = function (params) {
        let argProps = {
                'name':     { 'required': true, 'type': 'String','regex': validate.getRegex('name') },
                'country':  { 'required': false, 'type': 'String' },
                'category': { 'required': false, 'type': 'String' },
                'timezone': { 'required': false, 'type': 'String' },
                'gcmsenderid': { 'required': false, 'type': 'String' },
                'gcmapikey': { 'required': false, 'type': 'String' },
                'devemailid': { 'required': false, 'type': 'String' },
                'icon': { 'required': false, 'type': 'String' },
                'isAppDeleted':{'required':false,'type':'String'},
                'googleUrl':{'required':false,'type':'String','regex': validate.getRegex('name') ,'regex_1': validate.getRegex('url')},
                'iosUrl':{'required':false,'type':'String','regex': validate.getRegex('name'), 'regex_1': validate.getRegex('url')},
            },
            newApp = {};

        if (!(newApp = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 422, 'Validation error');
            return false;
        }

        processAppProps(newApp);

        newApp.createdOn=common.getCurrentEpochTime();
        newApp._id = common.db.ObjectID();
        newApp.key = common.shaHash(newApp._id, true);

        common.db.collection('apps').insert(newApp, function(err, app) {
            //brij - update the member collection to make this user and admin of his own app and
            let updatedAt = common.getCurrentEpochTime();
            common.db.collection('members').update({'api_key':params.member.api_key},{$push: {'user_role.admin': newApp._id.toString()}, '$set':{'updatedAt':updatedAt}}, function(err, member)
            {
                if(err){
                    logger.error(`there was an error" ${err}`);
                }

                // set table in PG
                if(common.config.IsProduction){
                     psqlUtils.setAppTables(newApp._id.toString());
                }
                //create app users collection collection
                common.db.createCollection('app_users'+newApp._id.toString(), function(err, collection){
                    if (err) {
                        logger.error(`Error created app_users" ${newApp._id.toString()}`);
                    }
                    else {
                        // add index on this collection
                        common.db.collection('app_users'+newApp._id.toString()).createIndex({active:true,p:true,d:true,did:true,av:true,pv:true,gcmid:true,fcmid:true},function(err, result){
                        });
                    }
                });

                appsApi.createAppAudSegment(newApp._id); // create app default aud segments

                //Clear this member from redis cache as its details are updated.
                cacheApi.deleteKey(params.member.api_key,function(err,res){
                    logger.info(`Redis key cleared. ${params.member.api_key}`);
                });
                common.returnOutput(params, newApp);
            });


        });
    };

    appsApi.createChatBot = function (params) {
        let argProps = {
            'name':     { 'required': true, 'type': 'String','regex': validate.getRegex('name') },
                'platforms': { 'required': true, 'type': 'Array' },
            },
            newApp = {};

        if (!(newApp = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 422, 'Validation error');
            return false;
        }

        newApp.createdOn=common.getCurrentEpochTime();
        newApp._id = common.db.ObjectID();
        newApp.key = common.shaHash(newApp._id, true);
        newApp.type = "chatbot";

        common.db.collection('apps').insert(newApp, function(err, app) {
            //brij - update the member collection to make this user and admin of his own app and
            let updatedAt = common.getCurrentEpochTime();
            common.db.collection('members').update({'api_key':params.member.api_key},{$push: {'user_role.admin': newApp._id.toString() }, '$set':{'updatedAt':updatedAt}}, function(err, member)
            {
                if(err){
                    logger.error(`there was an error" ${err}`);
                }

                //Clear this member from redis cache as its details are updated.
                cacheApi.deleteKey(params.member.api_key,function(err,res){
                    logger.info(`Redis key cleared. ${params.member.api_key}`);
                });
                common.returnOutput(params, newApp);
            });


        });
    };
    //save campaign column configuration
    appsApi.saveCampaignSettings = function (params) {

        let argProps = {
            'app_id': {
                'required': true,
                'type': 'String',
            },
            'allowedItems': { 
                'required': true, 
                'type': 'Array' 
            },
            'selectedColumns': {
                'required': false,
                'type': 'JSON'
            }
        };


        params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }


        common.db.collection('apps').update({ "_id": common.db.ObjectID(params.qstring.args.app_id) }, { $set: { 'campaign_columns': params.qstring.args.selectedColumns ,'allowedItems':params.qstring.args.allowedItems } }, (err, data) => {
            if (!err && data) {
                const allowedUrls = params.qstring.args.allowedItems
                if (allowedUrls.length > 0) {
                    let allowedList = '';           // set the default src for all apps
                    allowedUrls.forEach(function (url) {
                        allowedList += ' ' + url;
                    });
                    //Update csp policy 
                    semusiConfig.appListSrc = allowedList
                }
                common.returnOutput(params, data);
            } else {
                common.returnOutput(params, err);
            }
        })

    }

    appsApi.createAppAudSegment = function(appId, callback){
        let defaultAudSeg = [
                                {
                                    name: "All Audience",
                                    operand:"",
                                    operator:"",
                                    value:""
                                },
                                {
                                    name: "Dynamic Audience Segment",
                                    operand:"p",
                                    operator:"eq",
                                    value:"Data",
                                    category: "Device"
                                },
                                {
                                    name: "All IOS Users",
                                    operand:"p",
                                    operator:"eq",
                                    value:"IOS",
                                    category: "Device"
                                },
                                {
                                    name: "All Android Users",
                                    operand:"p",
                                    operator:"eq",
                                    value:"Android",
                                    category: "Device"
                                },
                                {
                                    name: "All Users with Competing Apps",
                                    operand:"competingapps.apps.0",
                                    operator:"exists",
                                    value:true,
                                    category: "Competing Apps"
                                }
                            ];

        let counter = 0;
        defaultAudSeg.forEach(function(segment){
            let segmentObj = {
                name : segment.name,
                description: segment.name,
                range:0,
                type :"system",
                segmentinfo:{
                    who: (segment.value)? [
                                            {
                                                operand: segment.operand,
                                                operator: segment.operator,
                                                value: segment.value,
                                                category: segment.category
                                            }
                                        ]: [],
                    what:[],
                    where:[],
                    when:[]
                },
                isDeleted:false,
                createdate: parseInt(moment().valueOf())
            }

            common.db.collection('audiencesegment_'+appId).insert(segmentObj, function(err, result) {
                if(err){
                    callback(false);
                }
                else{
                    counter++;
                    if(callback != undefined && defaultAudSeg.length == counter){ // return callback when array itrarate complete and record insert into db
                        callback(true);
                    }
                }
            });
        });
    }
    ///{ "competingapps.apps.0": {$exists: true}}
    //Update App Auth State, Reser Pwd Mode and concurrent login mode
    appsApi.saveAppAuthState = function( params ) {

        let argProps = {
           'app_id': {
               'required': true,
               'type': 'String',
           },
           'state': {
               'required': true,
               'type': 'String'
           },

           'concurrentLogin': {
            'required': true,
            'type': 'String'
            },
           'passwordResetMode': {
            'required': true,
            'type': 'String'
        }
    };
    let settings = {};
    params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
       if (!(settings = common.validateArgs(params.qstring.args, argProps))) {
           common.returnMessage(params, 422, 'Validation error');
           return false;
       }

       common.db.collection('apps').update({"_id": common.db.ObjectID(params.qstring.args.app_id )}, {$set: {'email_auth': settings.state,'concurrentLogin':settings.concurrentLogin, 'ResetPassswordMode': settings.passwordResetMode }}, (err, data) =>{
        
        if(err || !data) {
               common.returnMessage(params, 500, 'Something went wrong');
           }
           else{
              common.returnOutput(params, data); 
           }
       })
   }
   //Get Current App Auth State 
   appsApi.getAppAuthState = function(params) {
    let argProps = {
        'app_id': {
            'required': true,
            'type': 'String',
          }
       };

       if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }
       
       common.db.collection('apps').find({'_id':common.db.ObjectID(params.qstring.args.app_id)}).toArray( function(err, data){
        if(err ||!data) {
            common.returnMessage(params, 500, 'Something went wrong');
        }
        else{
            // Get the site setting
            common.db.collection('site_settings').findOne({"cdn": semusiConfig.serverDomain}, (err, sitesettings) => {
                if(err ||!sitesettings) {
                    //if it is not found send only the app data 
                    common.returnOutput(params, data);
                }else{
                    // Append Globalsetting data to the appdata to render the details in UI 
                    data[0].sitesettings = sitesettings;
                    common.returnOutput(params, data);
                }
            })
        }
    })
}

    appsApi.updateApp = function (params) {
        let argProps = {
                'app_id':   { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24, 'exclude-from-ret-obj': false },
                'name':     { 'required': false, 'type': 'String', 'regex': validate.getRegex('name') ,'regex_1': validate.getRegex('url') },
                'category': { 'required': false, 'type': 'String' },
                'timezone': { 'required': false, 'type': 'String' },
                'country':  { 'required': false, 'type': 'String' },
                'gcmsenderid': { 'required': false, 'type': 'String' },
                'gcmapikey': { 'required': false, 'type': 'String' }
            },
            updatedApp = {};
		params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(updatedApp = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 422, 'Validation error');
            return false;
        }

        if (Object.keys(updatedApp).length === 0) {
            common.returnMessage(params, 200, 'Nothing changed');
            return true;
        }

        processAppProps(updatedApp);

        if (params.member && params.member.global_admin) {
            updatedApp['modifiedOn'] = common.getCurrentEpochTime();
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: updatedApp}, function(err, app) {
                common.returnOutput(params, updatedApp);
            });
        } else {
            common.db.collection('members').findOne({'_id': params.member._id}, {user_role: 1}, function(err, member){
                if (
                    (member.user_role.admin && member.user_role.admin.indexOf(params.qstring.args.app_id) !== -1) ||
                    (member.user_role.manager && member.user_role.manager.indexOf(params.qstring.args.app_id) !== -1)
                 ) {
                    updatedApp['modifiedOn'] = common.getCurrentEpochTime();
                    common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: updatedApp}, function(err, app) {
                        //Clear this member from redis cache as its details are updated.
                        cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
                            logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
                        });
                        common.returnOutput(params, updatedApp);
                    });
                } else {
                    common.returnMessage(params, 401, 'User does not have admin rights for this app');
                }
            });
        }

        return true;
    };
    //save campaign setting in the campaigns
    appsApi.saveCmpSetting = function( params ) {

        let argProps = {
           'app_id': {
               'required': true,
               'type': 'String',
           },
           'appInboxreports': {
               'required': true,
               'type': 'String'
           }
    };
    params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
       if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
           common.returnMessage(params, 400, 'Not enough args');
           return false;
       }
       common.db.collection('apps').update({"_id": common.db.ObjectID(params.qstring.args.app_id )}, {$set: {'appInboxreports': params.qstring.args.appInboxreports }}, (err, data) =>{
        
        if(err || !data) {
               common.returnMessage(params, 500, 'Something went wrong');
           }
           else{
              common.returnOutput(params, data); 
           }
       })
    }

    appsApi.deleteApp = function (params) {
        let argProps = {
                'app_id':   { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24, 'exclude-from-ret-obj': false },
                'isAppDeleted': {'required':true ,'type':'String'}
            },
            updatedApp = {};

        if (!(updatedApp = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        if (Object.keys(updatedApp).length === 0) {
            common.returnMessage(params, 200, 'Nothing changed');
            return true;
        }

        processAppProps(updatedApp);

        updatedApp['modifiedOn'] = common.getCurrentEpochTime();
        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: updatedApp}, function(err, app) {
            //Clear this member from redis cache as its details are updated.
            cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
                logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
            });
            cacheApi.deleteKey("app_id"+params.qstring.args.app_id,function(err,res){
                logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
            });
            common.returnOutput(params, app);
        });

        return true;
    };


    appsApi.saveEmailConfiguration = function (params) {
        let argProps = {
            'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
            'active': { 'required': true, 'type': 'String'},
            'smtp_username': { 'required': false, 'type': 'String', 'regex': validate.getRegex('emailNameSettings')},
            'smtp_password': { 'required': false, 'type': 'String', 'regex': validate.getRegex('emailNameSettings')},
            'smtp_host': { 'required': false, 'type': 'String', 'regex': validate.getRegex('emailNameSettings')},
            'smtp_port': { 'required': false, 'type': 'Number', 'regex': validate.getRegex('number')},
            'smtp_fromAddress':{'required': false, 'type': 'String', 'regex': validate.getRegex('email')},
            'smtp_fromName':{'required': false, 'type': 'String'},
            'aws_accessKeyId': { 'required': false, 'type': 'String', 'regex': validate.getRegex('emailNameSettings')},
            'aws_secretAccessKey': { 'required': false, 'type': 'String', 'regex': validate.getRegex('emailNameSettings')},
            'aws_region': { 'required': false, 'type': 'String', 'regex': validate.getRegex('emailNameSettings')},
            'aws_sourceEmail': { 'required': false, 'type': 'String', 'regex': validate.getRegex('email')},
        },
        emailConfig,
        dataObj = { 'emailCfg': {} };
    
        params.qstring.args = (typeof (params.qstring.args) === 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
    
        if (!(emailConfig = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
    
        common.db.collection('apps').findOne({ '_id': common.db.ObjectID(params.qstring.app_id) }, { 'partnerInfo': 1 }, function (err, app) {
            if (err || app == null) {
                logger.error("Error finding app data:", err);
                common.returnMessage(params, 500, 'Internal server error');
            } else {
                if (emailConfig.active == "smtp") {
                    dataObj.emailCfg[emailConfig.active] = {
                        smtp_un: emailConfig.smtp_username,
                        smtp_ps: emailConfig.smtp_password,
                        smtp_h: emailConfig.smtp_host,
                        smtp_po: emailConfig.smtp_port,
                        smtp_fromAddress: emailConfig.smtp_fromAddress,
                        smtp_fromName: emailConfig.smtp_fromName,
                    };
                } else if (emailConfig.active == "aws_ses") {
                    dataObj.emailCfg[emailConfig.active] = {
                        aws_accessKeyId: emailConfig.aws_accessKeyId,
                        aws_secretAccessKey: emailConfig.aws_secretAccessKey,
                        aws_region: emailConfig.aws_region,
                        aws_sourceEmail: emailConfig.aws_sourceEmail,
                    };
                }
                dataObj.modifiedOn = common.getCurrentEpochTime();
                common.db.collection('apps').update({ '_id': common.db.ObjectID(params.qstring.args.app_id) }, { $set: { [`emailCfg.${emailConfig.active}`]: dataObj.emailCfg[emailConfig.active] } }, function (err, app) {
                    if (!err) {
                         // Clone and modify dataObj for Redis
                        let redisData = JSON.parse(JSON.stringify(dataObj));
                        let redisKey = "";

                        if(emailConfig.active === "smtp" ){
                            const smtpConfig = redisData.emailCfg.smtp;
                            redisKey = "smtp_";
                            redisData.smtp_host = smtpConfig.smtp_h;
                            redisData.smtp_port = smtpConfig.smtp_po;
                            redisData.smtp_username = smtpConfig.smtp_un;
                            redisData.smtp_password = smtpConfig.smtp_ps;
                            redisData.smtp_fromaddress = smtpConfig.smtp_fromAddress;


                                      delete smtpConfig.smtp_un;
                                      delete smtpConfig.smtp_ps;
                                      delete smtpConfig.smtp_h;
                                      delete smtpConfig.smtp_po;
                                      delete smtpConfig.smtp_fromAddress;
                                      delete redisData.emailCfg;
                                      delete redisData.modifiedOn;

                        }else{
                            redisKey = "ses_";
                            const smtpConfig = redisData.emailCfg.aws_ses;
                            redisData.aws_accesskeyid = smtpConfig.aws_accessKeyId;
                            redisData.aws_secretaccesskey  = smtpConfig.aws_secretAccessKey;
                            redisData.aws_region  = smtpConfig.aws_region;
                            redisData.aws_sourceemail  = smtpConfig.aws_sourceEmail;

                            delete smtpConfig.aws_accessKeyId;
                            delete smtpConfig.aws_secretAccessKey;
                            delete smtpConfig.aws_region;
                            delete smtpConfig.aws_sourceEmail;
                            delete redisData.emailCfg;
                            delete redisData.modifiedOn;

                        }
                        // Save the same object to Redis

                            cacheApi.setKey(`${redisKey}${params.qstring.args.app_id}`, JSON.stringify(redisData), (redisErr, redisRes) => {
                                if (redisErr) {
                                    logger.error('Error storing data in Redis:', redisErr);
                                } else {
                                    logger.info(`Data successfully stored in Redis==>>apns_${params.qstring.args.app_id}=============${redisRes}`);
                                }
                            });
                        common.returnOutput(params, dataObj);
                    }
                });
            }
        });
    };
    


    appsApi.saveSMSConfiguration = function (params) {
        let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'active': { 'required': true, 'type': 'String'},
                'username': { 'required': true, 'type': 'String'},
                'password': { 'required': true, 'type': 'String'},
            },
            smsConfig,
            dataObj = {'smsCfg':{}};
        params.qstring.args = (typeof(params.qstring.args) == 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(smsConfig = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
    
        common.db.collection('apps').findOne({ '_id': common.db.ObjectID(params.qstring.app_id) }, { 'partnerInfo': 1 }, function (err, app) {
            if (err || app == null) {
                logger.error("Error finding app data:", err);
                common.returnMessage(params, 500, 'Internal server error');
            } else {
                if (smsConfig.active === "gupshup") {
                    dataObj.smsCfg[smsConfig.active] = {
                        un: smsConfig.username,
                        ps: smsConfig.password
                    }
                }
                dataObj.modifiedOn = common.getCurrentEpochTime();
                common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, { $set: { [`smsCfg.${smsConfig.active}`]: dataObj.smsCfg[smsConfig.active] } }, function(err, app) {
                    common.returnOutput(params, dataObj);
                });
            }
        });
    };


    appsApi.saveCampConfigurator = function (params) {
        let argProps = {
            'app_id': {
                'required': true,
                'type': 'String',
            },
        };
    
        params.qstring.args = (typeof (params.qstring.args) === 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
    
        common.db.collection('apps').findOne({ '_id': common.db.ObjectID(params.qstring.app_id) }, { 'partnerInfo': 1, 'campaignConfigurator': 1 }, function (err, app) {
            if (err || app == null) {
                logger.error("Error finding app data:", err);
                common.returnMessage(params, 500, 'Internal server error');
            } else {
                let channel = params.qstring.args.channel;
                let partner_id = "";
                // Find partner_id based on the channel
                if (channel == "SMS") {
                    let smsPartner = app.partnerInfo.partnerData.find(partner => partner.channel === "SMS");
                    if (smsPartner) {
                        partner_id = smsPartner.partnerId; 
                    }
                } else if (channel == "EMAIL") {
                    let emailPartner = app.partnerInfo.partnerData.find(partner => partner.channel === "EMAIL");
                    if (emailPartner) {
                        partner_id = emailPartner.partnerId;
                    }
                } else if (channel == "WHATSAPP") {
                    let whatsappPartner = app.partnerInfo.partnerData.find(partner => partner.channel === "WHATSAPP");
                    if (whatsappPartner) {
                        partner_id = whatsappPartner.partnerId; 
                    }
                }
    
                // Construct the URL
                let generatedUrl = `${semusiConfig.apiServerUrl}/webhook?partnerId=${partner_id}&appid=${params.qstring.args.app_id}`;
    
                let updateItem = app.campaignConfigurator || {};
                updateItem[channel] = {
                    verb: params.qstring.args.httpRequest,
                    callBackUrl: generatedUrl,
                    url: params.qstring.args.url,
                    providerType: params.qstring.args.providerType,
                    channel: channel.toLowerCase(), 
                    validatorMappings : params.qstring.args.validatorMappings,
                    requestMappings: params.qstring.args.requestMappings,
                    responseMappings: params.qstring.args.responseMappings,
                    api_key: params.qstring.args.api_key,
                    app_id: params.qstring.args.app_id,
                    appKey: params.qstring.args.appKey,
                    partner_id: partner_id,
                    payload_key : params.qstring.args.payload_key,
                    partner : params.qstring.args.partner
                };
                
                let redisKey = partner_id;
                common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, { $set: { campaignConfigurator: updateItem, modifiedOn: common.getCurrentEpochTime() } }, function(err, app) {
                    if (err) {
                        logger.error("Error updating app data:", err);
                        common.returnMessage(params, 500, 'Internal server error');
                    } else {
                        cacheApi.setKey(`${redisKey}${params.qstring.args.app_id}`, JSON.stringify(updateItem), (redisErr, redisRes) => {
                            if (redisErr) {
                                logger.error('Error storing data in Redis:', redisErr);
                            } else {
                                logger.info(`Data successfully stored in Redis==>>apns_${params.qstring.args.app_id}=============${redisRes}`);
                            }
                        });
                        common.returnOutput(params, updateItem);
                    }
                });
            }
        });
    };
    
    appsApi.removeCampConfigurator = function (params) {
        let argProps = {
            'app_id': {
                'required': true,
                'type': 'String',
            },
        };
    
        params.qstring.args = (typeof (params.qstring.args) === 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
    
        common.db.collection('apps').findOne({ '_id': common.db.ObjectID(params.qstring.app_id) }, { 'partnerInfo': 1, 'campaignConfigurator': 1 }, function (err, app) {
            if (err || app == null) {
                logger.error("Error finding app data:", err);
                common.returnMessage(params, 500, 'Internal server error');
            } else {
                // Check if the campaign configuration exists
                if (!app.campaignConfigurator) {
                    common.returnMessage(params, 404, 'Campaign configuration not found');
                    return;
                }
    
                // Extract the partner IDs to remove from Redis
                let partnerIds = Object.values(app.campaignConfigurator).map(config => config.partner_id);
    
                // Remove the campaign configuration from the app using $unset
                common.db.collection('apps').update(
                    { '_id': common.db.ObjectID(params.qstring.args.app_id) },
                    { $unset: { campaignConfigurator: "" }, $set: { modifiedOn: common.getCurrentEpochTime() } },
                    function (err, result) {
                        if (err) {
                            logger.error("Error updating app data:", err);
                            common.returnMessage(params, 500, 'Internal server error');
                        } else {
                            // Remove the data from Redis for each partner ID
                            partnerIds.forEach(partner_id => {
                                let redisKey = partner_id + params.qstring.args.app_id;
                                cacheApi.deleteKey(redisKey, (redisErr, redisRes) => {
                                    if (redisErr) {
                                        logger.error('Error removing data from Redis:', redisErr);
                                    } else {
                                        logger.info(`Data successfully removed from Redis==>>apns_${redisKey}=============${redisRes}`);
                                    }
                                });
                            });
    
                            common.returnOutput(params, { message: 'Campaign configuration removed successfully' });
                        }
                    }
                );
            }
        });
    };
    
    
    
    

    appsApi.updateGCM = function (params) {
        let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'device_id': { 'required': true, 'type': 'String'},
                'gcm_id': { 'required': true, 'type': 'String'}
            },
            appId = '';

        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
        let app_user_id = common.crypto.createHash(semusiConfig.shaV1Hash).update(params.qstring.app_key + params.qstring.args.device_id + "").digest('hex');
        let updatedAt = common.getCurrentEpochTime();
        common.db.collection('app_users'+params.qstring.args.app_id).update({'_id':app_user_id}, {"$set":{"gcmid":params.qstring.args.gcm_id, 'updatedAt':updatedAt}},{safe:true},function(err, result) {
            if(!err && result && result.result && result.result.n>0){
                common.returnMessage(params, 200, 'Success');
                return true;
            }
            else{
                common.returnMessage(params, 500, 'App user does not exist.');
                return true;
            }
        });
    };

 

    appsApi.updateAppNew = function (params) {
        //brij - allow any one to create an app for himself, why restrict
        /*if (!(params.member.global_admin)) {
            common.returnMessage(params, 401, 'User is not a global administrator');
            return false;
        }*/

        let argProps = {
                'app_id':   { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24, 'exclude-from-ret-obj': false },
                'name':     { 'required': true, 'type': 'String' },
                //'category': { 'required': false, 'type': 'String' },
                'icon': { 'required': false, 'type': 'String' },
                'isAppDeleted':{'required':false,'type':'String'},
                'googleUrl':{'required':false,'type':'String'},
                'iosUrl':{'required':false,'type':'String'},
            },
            updatedApp = {};

        if (!(updatedApp = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        updatedApp['modifiedOn'] = common.getCurrentEpochTime();
        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: updatedApp}, function(err, app) {
            //Clear this member from redis cache as its details are updated.
            cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
                logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
            });
            common.returnOutput(params, updatedApp);
        });

    };

    appsApi.updateAppMetaData = function (params) {
            let argProps = {
                'pkg':{'required':false,'type':'String'},
                'tz' :{'required':false,'type':'String'}
            },
            updatedApp = {};

        if (!(updatedApp = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        let modifiedOn = common.getCurrentEpochTime();
        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.app_id)}, {$set: {"package":updatedApp.pkg,"time_zone":updatedApp.tz, 'modifiedOn':modifiedOn}}, {safe: true}, function(err, isOk) {
            if(!err){
                //Clear this member from redis cache as its details are updated.
               cacheApi.deleteKey(params.qstring.app_id,function(err,res){
                    logger.info(`Redis key cleared. ${params.qstring.app_id}`);
                });
               common.returnMessage(params, 200, 'Success');
            }
            else{
                common.returnMessage(params, 500, 'Error updating app.');
            }
        });
        return true;
    };

    appsApi.updateDeviceMetaData = function (params) {
            let argProps = {
                'tz' :{'required':false,'type':'Number'}
            },
            updatedDevice = {};

        if (!(updatedDevice = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        let updatedAt = common.getCurrentEpochTime();
        common.db.collection('app_users'+params.qstring.app_id).update({'did': params.qstring.did}, {$set: {"tz":updatedDevice.tz, 'updatedAt':updatedAt}}, {safe: true}, function(err, isOk) {
            if(!err){
               common.returnMessage(params, 200, 'Success');
            }
            else{
                common.returnMessage(params, 500, 'Error updating device.');
            }
        });
        return true;
    };

    appsApi.updateTimelyDashbaord = function(params){
        common.db.collection('app_users'+params.qstring.app_id).find({'did': params.qstring.did}, {'pv':1,'av':1,'p':1,'cc':1,'cty':1,'history':1,'tz':1}).toArray(function(err, result) {
            if(!err){
                let data = result[0];
                if(data.pv && data.av && data.history && data.p && data.cc && data.cty){
                    let history = data.history[data.history.length-1];

                    let platform = data.p.toLowerCase();
                    //history.dtEntry = (data.tz)? history.dtEntry+parseInt(data.tz): history.dtEntry;
                    let epoch = history.dtEntry*1000;
                    let id = common.timelyEventId(params.qstring.app_id, epoch);
                    data.pv = data.pv.replace(/\./g, "_");
                    data.av = data.av.replace(/\./g, "_");

                    let dashboardObj = {};

                    let cpi = (history.cpi)? history.cpi:0;
                    if(cpi){
                        common.fillUpdateMetaDataWithEpoch(epoch, dashboardObj, platform, data.pv+"."+data.av+"."+history.refname+"."+data.cc+"."+data.cty+".cpi", cpi*(-1));
                    }

                    if(history.isnew == true){ // unique install
                        common.fillUpdateMetaDataWithEpoch(epoch, dashboardObj, platform, data.pv+"."+data.av+"."+history.refname+"."+data.cc+"."+data.cty+".in", -1);
                    }else{ // repeat install
                        common.fillUpdateMetaDataWithEpoch(epoch, dashboardObj, platform, data.pv+"."+data.av+"."+history.refname+"."+data.cc+"."+data.cty+".ri", -1);
                    }

                    common.db.collection('timely_dashboard').update({'_id': id}, {'$inc': dashboardObj}, {'upsert': true},function(err, response){
                        if(!err){
                            data.av = params.qstring.args._app_version;
                            data.pv = (params.qstring.args._os_version)? params.qstring.args._os_version: data.pv;
                            common.addTimlyDashboard(data, params.qstring.app_id);
                        } else{
                            logger.error(`error update meta data ${err}`)
                        }
                    });
                }
            }
            else{
                logger.error(`error data not found while update meta data ${err}`)
            }
        });
    }

    appsApi.setAppMode = function (params) {
        let isappmodeproduction =  (params.qstring.mode === "true");
        let modifiedOn = common.getCurrentEpochTime();
        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.app_id)}, {$set: {"isappmodeproduction":isappmodeproduction, 'modifiedOn':modifiedOn}}, {safe: true}, function(err, isOk) {
            if(!err){
               //Clear this member from redis cache as its details are updated.
                cacheApi.deleteKey(params.qstring.app_id,function(err,res){
                    logger.info(`Redis key cleared. ${params.qstring.app_id}`);
                });
               common.returnMessage(params, 200, 'Success');
            }
            else{
                common.returnMessage(params, 500, 'Error updating referal');
            }
        });

    };

// This function, `setFcmMode`, is responsible for setting the FCM (Firebase Cloud Messaging) mode for an app.
// If App is using server key , Which is legacy method for sending Push , or Json File , Which is new approach 
// It also saves this informaation in both 'adev' and 'prod' mode
    appsApi.setFcmMode = function (params) {


    let updateObj = {};
        // Extracting whether JSON is enabled or not from the request parameters
    const isJsonEnabled = params.qstring.isJsonEnabled === "true";

      // Extracting whether the mode is development or production from the request parameters
    const mode = params.qstring.isdev === "true";

    // Determining the key to be updated based on the mode
    const targetKey = mode ? 'dev_isJsonEnabled' : 'prod_isJsonEnabled';

     // Creating the update object with the target key and its corresponding value,
    // and updating the 'modifiedOn' field with the current epoch time
    updateObj = {
        [targetKey]: isJsonEnabled,
        modifiedOn: common.getCurrentEpochTime()
    };


        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.app_id)}, {$set: updateObj }, {safe: true}, function(err, isOk) {
            if(!err){
              // If the update is successful, returning a success message with status code 200
              handleFcmKeyIncache(params)
            }
            else{
                    // If there's an error during the update, returning an error message with status code 500
                common.returnMessage(params, 500, 'Error updating referal');
            }
        });

    };



    // This function will update the redis key when modes are changed btm fcm key and JSON file

    function handleFcmKeyIncache(params) {
        // get the mode
        const mode = params.qstring.isdev === "true";


        // get file data s
        common.db.collection('apps').findOne({ '_id': common.db.ObjectID(params.qstring.app_id) }, function (err, app) {

            if (app) {

                // create redis data 
                let redisObj = {
                    signingKey: mode ? app.dev_android_cert3 : app.android_cert3,
                    fcmKey: mode ? app.fcmKey_dev : app.fcmKey_prod
                };


                // set in redis
                cacheApi.setKey(`fcm_${params.qstring.app_id}`, JSON.stringify(redisObj), (redisErr, redisRes) => {
                    if (redisErr) {
                        logger.error('Error Updating data in Redis:', err);
                    } else {
                        logger.info(`Redis Value Updated for FCM/JSON FILE`);
                        common.returnMessage(params, 200, 'Success');

                    }
                });
            }
        });
    }

/*
Provides the app store information of a given link if its a valid appstore url
returns name of app, category(optional), current downloads, company name, and platform information
*/
    appsApi.getAppStoreInfo = function (params){
            let argProps = {
                'storeurl': { 'required': true, 'type': 'String'}
            },storeurl = '';

        if (!(storeurl = common.validateArgs(params.qstring.args, argProps).storeurl)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        let appinfo={};

        if(params.qstring.args.storeurl.indexOf('play.google.com') > -1)
                {
                      request(params.qstring.args.storeurl, function (error, response, body) {
                      if (!error && response.statusCode == 200) {
                                let parsedHTML = cheerio.load(body);
                                // query for all elements with class 'foo' and loop over them
                                parsedHTML('.document-title').map(function(i, foo) {
                                      // the foo html element into a cheerio object (same pattern as jQuery)
                                    foo = cheerio(foo);
                                    let appname = foo.text();
                                    appinfo.appname = appname;
                                });

                                let divCounter = 1;
                                parsedHTML('.document-subtitle').each(function(i, element){
                                    if(divCounter == 3)
                                    {
                                        let a = cheerio(element);
                                        appinfo.appCategory = a.text();
                                    }
                                  divCounter++;
                                });

                                divCounter = 0;
                                //take the first image
                                parsedHTML('.cover-image').map(function(i,foo){
                                    if(divCounter == 0)
                                    {
                                        let a = cheerio(foo);
                                        appinfo.appImage = a.attr('src');
                                        common.returnMessage(params, 200, appinfo);
                                    }
                                    divCounter++;
                                });
                            }
                        });
                }
                else if(params.qstring.args.storeurl.indexOf('itunes.apple.com') > -1)
                {
                    //do a lookup at itunes for the appid
                    let s = params.qstring.args.storeurl;
                    let re = /(?:^|\W)id(\w+)(?!\w)/g, match, matches = [];
                    while (match = re.exec(s)) {
                        matches.push(match[1]);
                    }

                    //hit the lookup url from here
                    // http://itunes.apple.com/lookup?id=
                    let itunesUrl = 'http://itunes.apple.com/lookup?id=' + matches;
                     request(itunesUrl, function (error, response, body) {
                      if (!error && response.statusCode == 200) {
                        let obj = JSON.parse(body);
                        appinfo.appname = obj.results[0].trackName;
                        appinfo.appImage = obj.results[0].artworkUrl60;
                        appinfo.appCategory = obj.results[0].genres[0];
                        common.returnMessage(params, 200, appinfo);
                      }
                    });

                }
              else
              {
                common.returnMessage(params, 200, appinfo);
              }

        return true;
    };

/*
Goes to the db for apps_customfields, and returns all the names of custom fields
being sent by the dev so far
*/
    appsApi.getMetaCustomVariableFields = function (params)
    {

       common.db.collection('app_customfields').findOne({_id:common.db.ObjectID(params.qstring.app_id)}, function(err, customFields) {
                if (!err && customFields) {

                    common.returnMessage(params, 200, customFields.fields);
                } else {
                    common.returnMessage(params, 200, []);
                }
            });

        return true;
    };

/*
Finds out the custom event names sent thus far by the dev so far
*/
appsApi.getMetaCustomEventFields = function (params)
{
    common.db.collection('eventsegments_'+params.qstring.app_id).find({},{"event":1}).toArray(function(err,data){
        if(err){
            logger.error(err);
            common.returnOutput(params,{});
        }
        else{
            if(data.length>0){
                common.returnMessage(params,200,data);
            }
            else{
                common.db.collection('events').find({'_id': common.db.ObjectID(params.qstring.app_id)}).toArray(function(err, metaEvents) {

                    if (!err && metaEvents.length>0 && metaEvents[0].list) {
                        let result=[];
                        metaEvents[0].list.forEach(function(item){
                            result.push({"event":item});
                        });
                        common.returnMessage(params, 200, result);
                    } else {
                        common.returnMessage(params, 200, []);
                    }
                });
            }
        }
    });

    return true;
}

/*
Returns the count of current userbase against a given set of userdefined and system defined variables
*/
    appsApi.countUserBase = function(params)
    {
        let metavals = {};
        let eventvals = {};
        let _retCount = 0;

        if(params.qstring.metavals)
        {
            let metavals =  JSON.parse(params.qstring.metavals);
        }


        let staticVars = ['did','fs','ls','sd','tsd','sc','d','c','g','cty','cc','p','pv','av','lbst','lest','hos','alias','interests'];

        //[ { fs: { lt: 1435062358797 } }, { ls: { lt: 1435062372544 } } ]

        let match1= {$match:{}};

        common.constructMatchFromSegment(metavals,match1);

        common.db.collection('app_users'+params.qstring.app_id).aggregate([match1, {$unwind:"$history"},{$group:{"_id":{"did":"$did"},"lasthistory" : {$last: "$history"}}},{$match:{"lasthistory.type":"I"}},{$group:{"_id":null,count:{$sum:1}}}],function (err,countData)
        {
            if(!err)
            {
                if(countData)
                {
                    if(countData.length >= 1)
                    {
                        common.returnMessage(params, 200,countData[0].count);
                    }
                    else
                    {
                        common.returnMessage(params, 200,0);
                    }
                }
                else
                {
                    common.returnMessage(params, 200,0);
                }
            }
            else
            {
                common.returnMessage(params, 200,0);
            }
        });

        return true;
    };
    // generating n no. of steps
    appsApi.generateQuery = function(criteria, app_id, isAudienceExport = false) {
        let query = '';
        let k = 2;
    
        for (let i = 0; i < criteria.length; i++) {
            let category = criteria[i].category;
    
            query += `${(criteria.length > 1 && (criteria[1].category == 'Events' || (criteria[2] && criteria[2].category == 'Events'))) ? `step${i + 1} AS ` : ''} (SELECT DISTINCT s${i + 1}.did FROM ${(category == 'Device' || category == 'App Usage' || category == 'Custom Vars' || category == 'Competing Apps' || category == 'Geo') ? `app_users_${app_id}` : `events_${app_id}`} AS s${i + 1} WHERE ${(category == 'Events' ? `key = '${criteria[i].operand}' ${(criteria[i].attr ? "AND segment ->> '" + criteria[i].attr + "' " + common.convertToQuery(criteria[i].operator, criteria[i].value, "", criteria[i].operand) : '')}` : (category == 'Custom Vars' && criteria[i].operand.toLowerCase() === 'age' ? `(user_info->>'a')::numeric ${common.convertToQuery(criteria[i].operator, criteria[i].value, criteria[i].valueSecond , criteria[i].operand, criteria[i].timeUnit)}` : (category == 'Custom Vars' && criteria[i].operand.toLowerCase() === 'gender' ? `user_info->>'g' ${common.convertToQuery(criteria[i].operator, criteria[i].value, "", criteria[i].operand, criteria[i].timeUnit)}` : (category == 'Custom Vars' && criteria[i].operand.toLowerCase() === 'userid' ? `_custom_userid ${common.convertToQuery(criteria[i].operator, criteria[i].value, "", criteria[i].operand, criteria[i].timeUnit)}` : `${criteria[i].operand} ${common.convertToQuery(criteria[i].operator, criteria[i].value, "", criteria[i].operand, criteria[i].timeUnit)}`))))} ${(criteria[1] && criteria[1].category != 'Events' && (!criteria[2] || (criteria[i + 2] && criteria[i + 2].category === 'Events'))) || (!criteria[3] && criteria[2] && criteria[2].category !== 'Events') || (!criteria[4] && criteria[3] && criteria[3].category === 'Events') ? `AND ${(criteria[1].category === 'Custom Vars' && criteria[1].operand.toLowerCase() === 'age') ? `(user_info->>'a')::numeric ${common.convertToQuery(criteria[1].operator, criteria[1].value, criteria[1].valueSecond , criteria[1].operand, criteria[1].timeUnit)}` : (criteria[1].category === 'Custom Vars' && criteria[1].operand.toLowerCase() === 'gender') ? `user_info->>'g' ${common.convertToQuery(criteria[1].operator, criteria[1].value, "", criteria[1].operand, criteria[1].timeUnit)}` : (criteria[1].category === 'Custom Vars' && criteria[1].operand.toLowerCase() === 'userid') ? `_custom_userid ${common.convertToQuery(criteria[1].operator, criteria[1].value, "", criteria[1].operand, criteria[1].timeUnit)}` : `${criteria[1].operand} ${common.convertToQuery(criteria[1].operator, criteria[1].value, "", criteria[1].operand, criteria[1].timeUnit)}`} ${ (criteria[2] && criteria[2].category != 'Events' && (!criteria[3] || (criteria[i + 3] && criteria[i + 3].category == 'Events'))) ? `AND ${(criteria[2].category === 'Custom Vars' && criteria[2].operand.toLowerCase() === 'age') ? `(user_info->>'a')::numeric ${common.convertToQuery(criteria[2].operator, criteria[2].value, criteria[2].valueSecond , criteria[2].operand, criteria[2].timeUnit)}` : (criteria[2].category === 'Custom Vars' && criteria[2].operand.toLowerCase() === 'gender') ? `user_info->>'g' ${common.convertToQuery(criteria[2].operator, criteria[2].value, "", criteria[2].operand, criteria[2].timeUnit)}` : (criteria[2].category === 'Custom Vars' && criteria[2].operand.toLowerCase() === 'userid') ? `_custom_userid ${common.convertToQuery(criteria[2].operator, criteria[2].value, "", criteria[2].operand, criteria[2].timeUnit)}` : `${criteria[2].operand} ${common.convertToQuery(criteria[2].operator, criteria[2].value, "", criteria[2].operand, criteria[2].timeUnit)}`}` : ""})   ${(criteria.length > 2 && (criteria[2] && criteria[2].category === 'Events' ) ? ',' : '')}` : (criteria.length > 1 && i < (criteria.length == 2 ? criteria.length - 1 : criteria.length) ? '),' : (isAudienceExport && criteria.length > 1 && i < (criteria.length + 1)) ? '),' :')')}`;
    
            if(criteria[2] && criteria[2].category !== 'Events' && !criteria[3]) break;

            if(criteria[1] && criteria[1].category != 'Events' && (!criteria[2] || criteria[i + 2] && criteria[i + 2].category == 'Events')) i++;
        }
    
        if (criteria.length > 2 && criteria[2] && criteria[2].category === 'Events') {
            query = (criteria[k].category != 'Events') ? query.slice(0, -1) + `SELECT DISTINCT step${k + 1}.did from step${k - 1},step${k}, step${k + 1} WHERE step${k - 1}.did = step${k}.did AND step${k}.did=step${k + 1}.did;` : `${(criteria[k - 1].category != 'Events' && criteria[k].category == 'Events') ? (criteria[k - 1].e_operator == 'AND') ? query.slice(0, -1) + `${(isAudienceExport) ? `, step${k+2} As (` : ''}  SELECT DISTINCT step${k + 1}.did from step${k + 1} INNER JOIN step${k - 1} ON step${k + 1}.did = step${k - 1}.did ${(isAudienceExport) ? `)` : ''} ` : query.slice(0, -1) + `SELECT DISTINCT step${k - 1}.did AS did FROM step${k - 1} UNION SELECT DISTINCT step${k + 1}.did FROM step${k + 1} ${isAudienceExport ? ',' : ''}` : query}`
        }
    
        if (criteria.length > 1 && (criteria[1] && criteria[1].category == 'Events')) {
            for (let i = 1, j = criteria.length; i < criteria.length; i++, j++) {
                query += `${(criteria.length > i + 1) ? `step${j + 1} AS ` : (isAudienceExport) ? ` step${j + 1} AS `: ''}(${(criteria[i - 1].e_operator == 'AND') ? `SELECT DISTINCT step${(i == 1) ? i : j}.did from step${(i == 1) ? i : j} ${(criteria[i].since.have != 'hn') ? `INNER JOIN` : `LEFT JOIN`} step${i + 1} ON step${(i == 1) ? i : j}.did = step${i + 1}.did)` : `SELECT DISTINCT step${(i == 1) ? i : j}.did AS did from step${(i == 1) ? i : j} UNION SELECT DISTINCT step${i + 1}.did FROM step${i + 1})` }${(i < criteria.length - 2 ? ',' : (isAudienceExport && i < criteria.length - 1) ? ',' : '')}`
                k = j - 1;
            }
            
        }

        if(isAudienceExport) {
            if(criteria[1] !== 'Events') k++;
            const data = `${(criteria.length > 1 && ((criteria[1] && criteria[1].category == 'Events') || criteria[2] && criteria[2].category === "Events") ? 'WITH ' : '')}${query}`;
            if(criteria.length === 1 || (criteria.length === 2 && criteria[1].category !== 'Events') ||(criteria.length === 3 && criteria[2].category !== 'Events') ||(criteria.length === 4 && criteria[2].category !== 'Events')){
                return `WITH step1 as ${data} SELECT DISTINCT a.did AS deviceId, a._custom_userid AS "'"Customer ID"'" FROM app_users_${app_id} a INNER JOIN step1 b ON a.did = b.did;`
            } else{
                 return `${data} SELECT DISTINCT a.did AS deviceId, a._custom_userid AS "'"Customer ID"'" FROM app_users_${app_id} a INNER JOIN step${k + 1} b ON a.did = b.did;`
            }
        }
    
        return `${(criteria.length > 1 && ((criteria[1] && criteria[1].category == 'Events') || (criteria[2] && criteria[2].category == 'Events')) ? 'WITH ' : '')}${query}`;
    }
    appsApi.countAudienceReach = async function(params) {
        if(params.qstring.criteria)
        {
            let app_id = params.qstring.app_id;
            let criteria =  JSON.parse(params.qstring.criteria);
            let criteriaData =  JSON.parse(params.qstring.criteria);
            var selectors =(typeof params.qstring.selectors === 'string') ?  JSON.parse(params.qstring.selectors) : params.qstring.selectors;
            if(criteria.length==0){
                common.returnMessage(params,200, 0);
                return false;
            }
        //Run regex for digibox and azure appid
        if(semusiConfig.regexAPPIDS.includes(app_id)) {
            if(criteriaData[0].category != "Events") {

                let events = [];
                let tmpCriteria = [];
                let filters = [];

                // prepare filter and copy of criteria
                common.prepareFilter(criteria, filters, tmpCriteria);
                tmpCriteria.forEach(function(value, key) {
                    if(value.category == 'Events'){
                        events.push(value);
                        criteria.splice(key);
                    }
                });
        
                sql = '';
                let criteria = criteriaData;
                let criteriaLength = criteria.length;
                if (criteriaLength > 0) {
                    if (criteriaLength == 1) {
                        /* one level who query*/
                        if(criteria[0].category == 'Custom Segment'){
                            //sql = "select count(*) from app_users_" + params.qstring.app_id + " where  audsegment->> " + criteria[0].operand + " = '" +  criteria[0].value + "';";
                            sql = `select DISTINCT did from app_users_${params.qstring.app_id} where audsegment @> '{"audid${criteria[0].value}":"${criteria[0].value}"}'`;
                        }else{
                            sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                        }
                       
                    }
                    if (criteriaLength == 2) {
                        /* code for who + what condition query */
                        if (criteria[0].category == 'Device' || criteria[0].category == 'App Usage' || criteria[0].category == 'Custom Vars' || criteria[0].category == 'Competing Apps' || criteria[0].category == 'Geo') {
                            sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                        }
                    }
                    if (criteriaLength == 3) {
                        /* code for who + what condition query */
                        if (criteria[0].category == 'Device' || criteria[0].category == 'App Usage' || criteria[0].category == 'Custom Vars' || criteria[0].category == 'Competing Apps' || criteria[0].category == 'Geo') {
                          
                            sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);

                        }
                    }
                    if (criteriaLength == 4){

                        sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                    }
                    if (criteriaLength == 5){

                        sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                    }

                    // Run pg query
                    logger.info(`sql audience query ${sql}`);
                    
                    appsApi.executeAudienceCountQuery(sql, selectors,"length").then((reachCount) =>{
                        logger.info(`== executeAudienceCountQuery === ${reachCount}`);
                        return common.returnMessage(params, 200,  reachCount);
                    })
                    .catch(error =>{
                        logger.error(`catch error ::>>>${error}`);
                        return common.returnMessage(params, 200,  error);
                    });

                } else {
                    return common.returnMessage(params, 200, 0);
                }
            
        } else {
            //New Regex Code Integration
            let starttime = params.qstring.starttime;
            let endtime = params.qstring.endtime;
            if(starttime == undefined){
                let d = new Date();
                d.setDate(d.getDate() - parseInt(30));
                let epochFs = Math.floor(d.getTime() / 1000);
                starttime = epochFs
            }
            if(endtime == undefined){
                let d = new Date();
                let epochFs = Math.floor(d.getTime() / 1000);
                endtime = epochFs
            }
            logger.info("critera All",criteria);
            // Regex function call to get sql
                let sql = await regex.generateRegexForSteps(app_id, criteria, starttime, endtime, [ 'Android', 'IOS', 'Web' ], selectors.countOrData, selectors.column);
                logger.info(`sql result for audience ${sql}`);
                appsApi.executeAudienceCountQuery(sql, selectors,"n").then((reachCount) =>{
                    logger.info(`== executeAudienceCountQuery === ${reachCount}`);
                    return common.returnMessage(params, 200,  reachCount);
                })
                .catch(error =>{
                    logger.error(`catch error ::>>>${error}`);
                    return common.returnMessage(params, 200,  error);
                });

          }
        }
        else {
            //GETTING REACH COUNT WITH OLD FLOW
                let events = [];
                    let tmpCriteria = [];
                    let filters = [];

                    // prepare filter and copy of criteria
                    common.prepareFilter(criteria, filters, tmpCriteria);
                    tmpCriteria.forEach(function(value, key){
                        if(value.category == 'Events'){
                            events.push(value);
                            criteria.splice(key);
                        }
                    });
                    if(events.length > 0 && criteriaData[0].category == "Events"){
                        let steps = [];
                        let eventsData = {};
                        let edate = common.getCurrentEpochTime();
                        let start = moment(edate*1000).subtract(120,'days').toDate();
                        edate = moment(edate*1000).add(1,'days').toDate();


                        events.forEach(function(item){
                            let obj={
                                event_collection: 'event_'+params.qstring.app_id+"_"+item.operand,
                                with_actors: false,
                                actor_property: 'did',
                                timeframe: {
                                    start: start,
                                    end: edate
                                }
                            };
                            // validate segment information
                            if(item.attr && item.attr != ''){
                                let filter = {
                                        property_name:'segment.'+item.attr,
                                        operator:item.operator,
                                        property_value:item.value
                                }
                                obj.filters = [filter].concat(filters);
                            }
                            else{
                                obj.filters = filters;
                            }

                            if(!item.value){
                                obj.inverted = true;
                            }
                            steps.push(obj);
                        });
                        // PG query
                        let operator = (events[0].value)? "=" : "!=";
                        let eventsDataLength=(events.length-1);
                        let sql;
                        let userQuery;
                        let resDataType = [];
                        db.collection('eventsegments').findOne({_id:db.ObjectId(params.qstring.app_id)},async function(err,result){
                            if(result){
                                result.events.forEach(function(item){
                                    item.list.forEach(function(inneritem){
                                    resDataType.push(inneritem.name+":"+inneritem.type);
                                    }); 
                                });
                                if(selectors.export){   //for export. send export as boolean true in params
                                    let exportColumns = "" 
                                    let newArray1 = [];
                                    let newArray2 = [];
                                    let eventsList = [];
                                    let attributeList = [];

                                    const column_value = await psqlUtils.executeQuery(`SELECT displayname, propertyname, visible  FROM app_properties_${params.qstring.app_id}`);

                                    const userProperties = column_value[0];  
                                    
                                    const filteredArray = selectors.column.userProperties.filter(item => userProperties.some(obj => obj.propertyname === item.alias && obj.visible));

                                    if(userProperties && userProperties.length > 0){
                                        newArray2 = filteredArray.map(obj2 => {
                                            const matchingObject = userProperties.find(obj1 => obj2.alias === obj1.propertyname);
                                            if (matchingObject) {
                                                obj2.alias = matchingObject.displayname;
                                            }
                                            return obj2;
                                        });
                                    }

                                    if(selectors?.column?.attributeColumns && selectors?.column?.attributeColumns.length > 0){
                                        selectors.column.attributeColumns.map((data) => {
                                            if(data.type === 'event'){
                                                eventsList.push(data.alias)
                                            }
                                            if(data.type === "attribute"){
                                                attributeList.push({
                                                 name : data.alias,
                                                 event : data.event
                                                })
                                            }
                                        })
                                    
    
                                        let attrValue;
                                        if(attributeList && attributeList.length > 0){
                                           const attrColumn = await psqlUtils.executeQuery(`SELECT key, name, displayname FROM attributes_${params.qstring.app_id} WHERE ` + common.getAttributeCondition(attributeList));
                                           attrValue = attrColumn[0];
                                        }

                                    const eventColumns = await psqlUtils.executeQuery(`SELECT name, displayname FROM app_events_${params.qstring.app_id} WHERE ` + common.getEventCondition(eventsList));
                                    let eventValues = eventColumns[0];
                                    if(eventValues && eventValues.length > 0){
                                        newArray1 = selectors.column.attributeColumns.map(obj2 => {
                                            const matchingObject = eventValues.find(obj1 => obj2.alias === obj1.name);
                                            if (matchingObject) {
                                                obj2.alias = (matchingObject.displayname !== null) ? matchingObject.displayname : matchingObject.name;
                                            }
                                            if(attributeList && attributeList.length>0){
                                            const matchingAttributes = attrValue.find(obj1 => obj2.alias === obj1.name);
                                            if(matchingAttributes){
                                                obj2.alias = (matchingAttributes.displayname !== null) ? matchingAttributes.displayname : matchingAttributes.name;
                                            }
                                         }
                                            return obj2;
                                        });
                                    }
                                    }

                                    const newArray = [...newArray2, ...newArray1];
                                    
                                    for(let i=0; i<newArray?.length; i++){
                                        exportColumns+= (newArray[i].col == "" ? newArray[i].alias : newArray[i].col +" as  "+ `"'"${newArray[i].alias}"'"`)+","
                                    }
                                    exportColumns = exportColumns.slice(0, -1);
                                    if(events[0].since.have == "h" && eventsDataLength == 0){
                                        sql = `WITH ordered_events AS ( SELECT did, key, p, TO_TIMESTAMP(eventtime) as et, segment, dt, ROW_NUMBER() OVER (PARTITION BY did ORDER BY eventtime) AS rn FROM events_${params.qstring.app_id} WHERE key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)} ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`}  AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step1 AS ( SELECT did, TO_CHAR(et, 'YYYY-MM-DD HH24:MI:SS') AS step1date, key AS step1key, segment AS step1segment, rn AS rn1 FROM ordered_events WHERE key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)}) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN step1 b ON a.did = b.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                    else if(events[0].since.have == "hn" && eventsDataLength == 0){
                                        sql = `WITH ordered_events AS ( SELECT did, key, p, TO_TIMESTAMP(eventtime) as et, segment, dt, ROW_NUMBER() OVER (PARTITION BY did ORDER BY eventtime) AS rn FROM events_${params.qstring.app_id} WHERE key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)} ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`}  AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step1 AS ( SELECT did, TO_CHAR(et, 'YYYY-MM-DD HH24:MI:SS') AS step1date, key AS step1key, segment AS step1segment, rn AS rn1 FROM ordered_events WHERE key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)}) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN step1 b ON a.did = b.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                    // A AND B
                                    else if(events[0].since.have == "h" && events[1].since.have == "h" &&eventsDataLength == 1){
                                        sql = `WITH ordered_events AS ( SELECT did, key, TO_TIMESTAMP(eventtime) AS event_timestamp, segment, dt FROM events_${params.qstring.app_id} WHERE ((key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)} ) OR (key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events)} )) ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`} AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step_events AS ( SELECT did, key, event_timestamp, dt, segment, ROW_NUMBER() OVER (PARTITION BY did ORDER BY event_timestamp) AS rn FROM ordered_events), valid_sequences AS ( SELECT s1.did, s1.event_timestamp AS step1eventtime, s1.dt as step1date, s1.key AS step1key, s1.segment AS step1Segment, s2.event_timestamp AS step2eventtime, s2.dt as step2date, s2.key AS step2key, s2.segment AS step2Segment FROM step_events s1 JOIN step_events s2 ON s1.did = s2.did AND s1.rn < s2.rn AND s1.key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events, "s1")}  AND s2.key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events, "s2")} WHERE s1.key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events, "s1")} AND s2.key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events, "s2")}), ranked_sequences AS ( SELECT vs.did, vs.step1eventtime as step1date, vs.step1key, vs.step1Segment, vs.step2eventtime as step2date, vs.step2key, vs.step2Segment,  ROW_NUMBER() OVER (PARTITION BY vs.did, vs.step2eventtime ORDER BY vs.step1eventtime DESC) AS seq_num FROM valid_sequences vs), final_sequences AS ( SELECT did, TO_CHAR(step1date, 'YYYY-MM-DD HH24:MI:SS') as step1date, step1key, step1Segment, TO_CHAR(step2date, 'YYYY-MM-DD HH24:MI:SS') as step2date, step2key, step2Segment FROM ranked_sequences WHERE seq_num = 1) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN final_sequences ffs ON a.did = ffs.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                    // A AND NOT B
                                    else if(events[0].since.have == "h" && events[1].since.have == "hn" && eventsDataLength == 1){
                                        sql = `WITH ordered_events AS ( SELECT did, key, p, eventtime, segment, dt, ROW_NUMBER() OVER (PARTITION BY did ORDER BY eventtime) AS rn FROM events_${params.qstring.app_id} WHERE key IN ('${events[0].operand}','${events[1].operand}') ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`}  AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step1 AS (SELECT did, eventtime AS step1_eventtime, key AS step1key, eventtime AS step1date, segment AS step1segment, rn AS rn1 FROM ordered_events WHERE key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)}), step2 AS ( SELECT did, eventtime AS step2_eventtime, key AS step2key, eventtime AS step2date, segment AS step2segment, rn AS rn2 FROM ordered_events WHERE key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events)} ), filtered_final_sequences AS ( SELECT s1.did, s1.step1_eventtime, s1.step1key, TO_CHAR(TO_TIMESTAMP(s1.step1date), 'YYYY-MM-DD HH24:MI:SS') as step1date, s1.step1segment, s2.step2_eventtime, s2.step2key, TO_CHAR(TO_TIMESTAMP(s2.step2date), 'YYYY-MM-DD HH24:MI:SS') as step2date, s2.step2segment, CASE WHEN s2.step2_eventtime IS NULL THEN 'Dropoff' ELSE 'Complete' END AS sequence_status FROM step1 s1 LEFT JOIN step2 s2 ON s1.did = s2.did AND s2.rn2 = s1.rn1 + 1 WHERE s1.step1key = '${events[0].operand}' AND s2.step2_eventtime IS NULL) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN filtered_final_sequences ffs ON a.did = ffs.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                    // A AND B AND C
                                    else if(events[0].since.have == "h" && events[1].since.have == "h" && events[2].since.have == "h" && eventsDataLength == 2){
                                        sql = `WITH ordered_events AS ( SELECT did, key, TO_TIMESTAMP(eventtime) AS event_timestamp, segment, dt FROM events_${params.qstring.app_id} WHERE ((key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)} ) OR (key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events)} ) OR (key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events)})) ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`} AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step_events AS ( SELECT did, key, event_timestamp, dt, segment, ROW_NUMBER() OVER (PARTITION BY did ORDER BY event_timestamp) AS rn FROM ordered_events), valid_sequences AS ( SELECT s1.did, s1.event_timestamp AS step1eventtime, s1.dt as step1date, s1.key AS step1key, s1.segment AS step1Segment, s2.event_timestamp AS step2eventtime, s2.dt as step2date, s2.key AS step2key, s2.segment AS step2Segment, s3.event_timestamp AS step3eventtime, s3.dt as step3date, s3.key AS step3key, s3.segment AS step3Segment FROM step_events s1 JOIN step_events s2 ON s1.did = s2.did AND s1.rn < s2.rn AND s1.key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events, "s1")} AND s2.key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events, "s2")} JOIN step_events s3 ON s2.did = s3.did AND s2.rn < s3.rn AND s3.key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events, "s3")} WHERE s1.key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events, "s1")} AND s2.key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events, "s2")} AND s3.key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events, "s3")}), ranked_sequences AS ( SELECT vs.did, vs.step1eventtime as step1date, vs.step1key, vs.step1Segment, vs.step2eventtime as step2date, vs.step2key, vs.step2Segment, vs.step3eventtime as step3date, vs.step3key, vs.step3Segment, ROW_NUMBER() OVER (PARTITION BY vs.did, vs.step3eventtime ORDER BY vs.step1eventtime DESC) AS seq_num FROM valid_sequences vs), final_sequences AS ( SELECT did, TO_CHAR(step1date, 'YYYY-MM-DD HH24:MI:SS') as step1date, step1key, step1Segment, TO_CHAR(step2date, 'YYYY-MM-DD HH24:MI:SS') as step2date, step2key, step2Segment, TO_CHAR(step3date, 'YYYY-MM-DD HH24:MI:SS') as step3date, step3key, step3Segment FROM ranked_sequences WHERE seq_num = 1) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN final_sequences ffs ON a.did = ffs.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                    // A AND B AND NOT C
                                    else if(events[0].since.have == "h" && events[1].since.have == "h" && events[2].since.have == "hn" && eventsDataLength == 2){
                                    sql = `WITH ordered_events AS ( SELECT did, key, p, eventtime, segment, dt, ROW_NUMBER() OVER (PARTITION BY did ORDER BY eventtime) AS rn FROM events_${params.qstring.app_id} WHERE key IN ('${events[0].operand}', '${events[1].operand}', '${events[2].operand}') ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`} AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step1 AS ( SELECT did, eventtime AS step1_eventtime, key AS step1key, eventtime AS step1date, segment AS step1segment, rn AS rn1 FROM ordered_events WHERE  key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)}), step2 AS ( SELECT did, eventtime AS step2_eventtime, key AS step2key, eventtime AS step2date, segment AS step2segment, rn AS rn2 FROM ordered_events WHERE key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events)} ),  step3 AS ( SELECT did, eventtime AS step3_eventtime, key AS step3key, eventtime AS step3date, segment AS step3segment, rn AS rn3 FROM ordered_events WHERE  key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events)}), sequences AS ( SELECT s1.did, s1.step1_eventtime, s1.step1key, s1.step1date, s1.step1segment, s2.step2_eventtime, s2.step2key, s2.step2date, s2.step2segment, s3.step3_eventtime, s3.step3key,  s3.step3date, s3.step3segment, ROW_NUMBER() OVER (PARTITION BY s1.did ORDER BY s1.step1_eventtime) AS seq_rn FROM step1 s1 LEFT JOIN step2 s2 ON s1.did = s2.did AND s2.rn2 = s1.rn1 + 1 LEFT JOIN step3 s3 ON s2.did = s3.did AND s3.rn3 = s2.rn2 + 1), filtered_final_sequences AS (SELECT did, step1_eventtime, step1key, TO_CHAR(TO_TIMESTAMP(step1date), 'YYYY-MM-DD HH24:MI:SS') as step1date, step1segment, step2_eventtime, step2key, TO_CHAR(TO_TIMESTAMP(step2date), 'YYYY-MM-DD HH24:MI:SS') as step2date, step2segment, step3_eventtime, step3key, TO_CHAR(TO_TIMESTAMP(step3date), 'YYYY-MM-DD HH24:MI:SS') as step3date, step3segment, 'Dropoff' AS status FROM sequences WHERE step2key IS NOT NULL AND step3key IS NULL ORDER BY did, step1_eventtime) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN filtered_final_sequences ffs ON a.did = ffs.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                    // A AND B AND C AND D
                                    else if(events[0].since.have == "h" && events[1].since.have == "h" && events[2].since.have == "h" && events[3].since.have == "h" && eventsDataLength == 3){
                                        sql = `WITH ordered_events AS ( SELECT did, key, TO_TIMESTAMP(eventtime) AS event_timestamp, segment, dt FROM events_${params.qstring.app_id} WHERE ((key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)} ) OR (key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events)} ) OR (key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events)}) OR (key = '${events[3].operand}' ${common.mulipleAttributes(selectors.events[3].events)})) ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`} AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step_events AS ( SELECT did, dt, key, event_timestamp, segment, ROW_NUMBER() OVER (PARTITION BY did ORDER BY event_timestamp) AS rn FROM ordered_events), valid_sequences AS ( SELECT s1.did, s1.event_timestamp AS step1eventtime, s1.dt as step1date, s1.key AS step1key, s1.segment AS step1Segment, s2.event_timestamp AS step2eventtime, s2.dt as step2date, s2.key AS step2key, s2.segment AS step2Segment, s3.event_timestamp AS step3eventtime, s3.dt as step3date, s3.key AS step3key, s3.segment AS step3Segment, s4.event_timestamp AS step4eventtime, s4.dt as step4date, s4.key AS step4key, s4.segment AS step4Segment FROM step_events s1 JOIN step_events s2 ON s1.did = s2.did AND s1.rn < s2.rn AND s1.key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events, "s1")} AND s2.key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events, "s2")} JOIN step_events s3 ON s2.did = s3.did AND s2.rn < s3.rn AND s3.key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events, "s3")} JOIN step_events s4 ON s3.did = s4.did AND s3.rn < s4.rn AND s4.key = '${events[3].operand}' ${common.mulipleAttributes(selectors.events[3].events, "s4")} WHERE s1.key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events, "s1")} AND s2.key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events, "s2")} AND s3.key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events, "s3")} AND s4.key = '${events[3].operand}' ${common.mulipleAttributes(selectors.events[3].events, "s4")}), ranked_sequences AS ( SELECT vs.did, vs.step1eventtime as step1date, vs.step1key, vs.step1Segment, vs.step2eventtime as step2date, vs.step2key, vs.step2Segment, vs.step3eventtime as step3date, vs.step3key, vs.step3Segment, vs.step4eventtime as step4date, vs.step4key, vs.step4Segment, ROW_NUMBER() OVER (PARTITION BY vs.did, vs.step4eventtime ORDER BY vs.step1eventtime DESC) AS seq_num FROM valid_sequences vs), final_sequences AS ( SELECT did, TO_CHAR(step1date, 'YYYY-MM-DD HH24:MI:SS') as step1date , step1key, step1Segment, TO_CHAR(step2date, 'YYYY-MM-DD HH24:MI:SS') as step2date, step2key, step2Segment, TO_CHAR(step3date, 'YYYY-MM-DD HH24:MI:SS') as step3date, step3key, step3Segment, TO_CHAR(step4date, 'YYYY-MM-DD HH24:MI:SS') as step4date, step4key, step4Segment FROM ranked_sequences WHERE seq_num = 1) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN final_sequences ffs ON a.did = ffs.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                    else if(events[0].since.have == "h" && events[1].since.have == "h" && events[2].since.have == "h" && events[3].since.have == "hn" && eventsDataLength == 3){
                                       sql = `WITH ordered_events AS ( SELECT did, key, p, eventtime, segment, dt, ROW_NUMBER() OVER (PARTITION BY did ORDER BY eventtime) AS rn FROM events_${params.qstring.app_id} WHERE key IN ('${events[0].operand}', '${events[1].operand}', '${events[2].operand}', '${events[3].operand}') ${selectors?.p?.length == 3 ? '' : `AND p IN ('${selectors.p.join("','")}')`} AND ${common.queryConverter(selectors.customUnit, selectors.timeStamp, selectors.lastTimeStamp)}), step1 AS ( SELECT did, eventtime AS step1_eventtime, key AS step1key, eventtime AS step1date, segment AS step1segment, rn AS rn1 FROM ordered_events WHERE  key = '${events[0].operand}' ${common.mulipleAttributes(selectors.events[0].events)}), step2 AS ( SELECT did, eventtime AS step2_eventtime, key AS step2key, eventtime AS step2date, segment AS step2segment, rn AS rn2 FROM ordered_events WHERE  key = '${events[1].operand}' ${common.mulipleAttributes(selectors.events[1].events)}), step3 AS ( SELECT did, eventtime AS step3_eventtime, key AS step3key, eventtime AS step3date, segment AS step3segment, rn AS rn3 FROM ordered_events WHERE key = '${events[2].operand}' ${common.mulipleAttributes(selectors.events[2].events)}), step4 AS ( SELECT did, eventtime AS step4_eventtime, key AS step4key, eventtime AS step4date, segment AS step4segment, rn AS rn4 FROM ordered_events WHERE key = '${events[3].operand}' ${common.mulipleAttributes(selectors.events[3].events)}), sequences AS ( SELECT s1.did, s1.step1_eventtime, s1.step1key, s1.step1date, s1.step1segment, s2.step2_eventtime, s2.step2key, s2.step2date, s2.step2segment, s3.step3_eventtime, s3.step3key, s3.step3date, s3.step3segment, s4.step4_eventtime, s4.step4key, s4.step4date, s4.step4segment, ROW_NUMBER() OVER (PARTITION BY s1.did ORDER BY s1.step1_eventtime) AS seq_rn FROM step1 s1 LEFT JOIN step2 s2 ON s1.did = s2.did AND s2.rn2 = s1.rn1 + 1 LEFT JOIN step3 s3 ON s2.did = s3.did AND s3.rn3 = s2.rn2 + 1 LEFT JOIN step4 s4 ON s1.did = s4.did AND s4.rn4 = s3.rn3 + 1), filtered_final_sequences AS ( SELECT did, step1_eventtime, step1key, TO_CHAR(TO_TIMESTAMP(step1date), 'YYYY-MM-DD HH24:MI:SS') as step1date, step1segment, step2_eventtime, step2key, TO_CHAR(TO_TIMESTAMP(step2date), 'YYYY-MM-DD HH24:MI:SS') as step2date, step2segment, step3_eventtime, step3key, TO_CHAR(TO_TIMESTAMP(step3date), 'YYYY-MM-DD HH24:MI:SS') as step3date, step3segment, step4_eventtime, step4key, TO_CHAR(TO_TIMESTAMP(step4date), 'YYYY-MM-DD HH24:MI:SS') as step4date, step4segment, CASE WHEN step4_eventtime IS NULL THEN 'Dropoff' ELSE 'Complete' END AS status FROM sequences WHERE step1key = '${events[0].operand}' AND step2key = '${events[1].operand}' AND step3key = '${events[2].operand}' AND step4_eventtime IS NULL ORDER BY did, step1_eventtime) select ${exportColumns} from app_users_${params.qstring.app_id} a INNER JOIN filtered_final_sequences ffs ON a.did = ffs.did ${selectors?.column?.sortedAttribute && Object.keys(selectors?.column?.sortedAttribute).length !== 0 ? `ORDER BY ${selectors?.column?.sortedAttribute?.col == 'column' ? `'"${selectors?.column?.sortedAttribute?.value}"' ${selectors.sortBy}` : `(${selectors?.column?.sortedAttribute.step}) ${selectors.sortBy}` }` : ''}`;
                                    }
                                }else{   //for audience 
                                    let step1Count = 'select count(distinct did)';
                                    let step1User = `${selectors.isAudienceExport ? 'WITH step1 as (' : ''}select distinct did`;
                                    let step2Count = 'SELECT count(DISTINCT step1.did) FROM step1 LEFT JOIN step2 ON step1.did = step2.did WHERE step2.did IS NULL';
                                    let step2User = `${selectors.isAudienceExport ? ', step3 as (' : ''}SELECT DISTINCT step1.did FROM step1 LEFT JOIN step2 ON step1.did = step2.did WHERE step2.did IS NULL ${selectors.isAudienceExport ? ')' :'' }`;  //for audience 
                                    if(events[0].since.have == "h" && eventsDataLength == 0){
                                       let query = " from events_"+params.qstring.app_id+" where key = '"+events[0].operand+"' "+(events[0].attr ? "AND segment ->> '"+events[0].attr+"' "+common.convertToQuery(events[0].operator, events[0].value,"", events[0].operand)+""   :'' )+"";
                                        sql = step1Count + query;
                                        userQuery = step1User + query;
                                        if(selectors.isAudienceExport){
                                            userQuery+= `) SELECT DISTINCT a.did AS deviceId, a._custom_userid AS "'"Customer ID"'" FROM app_users_${app_id} a INNER JOIN step1 b ON a.did = b.did;`
                                        }
                                    }
                                    else if(events[0].since.have == "hn" && eventsDataLength == 0){
                                       let query = " from events_"+params.qstring.app_id+" where key = '"+events[0].operand+"' "+(events[0].attr ? "AND segment ->> '"+events[0].attr+"' "+common.convertToQuery(events[0].operator, events[0].value,"", events[0].operand)+""   :'' )+"";
                                       sql = step1Count + query;
                                       userQuery = step1User + query;
                                    }
                                    else if(events[0].since.have == "h" && events[1].since.have == "hn"){
                                        let query = "WITH step1 AS ( SELECT DISTINCT s1.did, s1.KEY FROM events_"+params.qstring.app_id+" AS s1 WHERE s1.KEY = '"+events[0].operand+"'"+(events[0].attr ? "AND s1.segment ->> '"+events[0].attr+"' "+common.convertToQuery(events[0].operator, events[0].value,"", events[0].operand)+""   :'' )+" AND s1.eventtime > CAST(extract(epoch FROM now() - INTERVAL '"+ (2 * events[1].since.time)+ " "+(events[1].since.type === "d" ? "day" : (events[1].since.type === "h" ? "hours": (events[1].since.type ==="m" ? "minutes" : "seconds")))+"') AS INTEGER) AND s1.eventtime < CAST(extract(epoch FROM now() - INTERVAL '"+ (events[1].since.time)+ " "+(events[1].since.type === "d" ? "day" : (events[1].since.type === "h" ? "hours": (events[1].since.type ==="m" ? "minutes" : "seconds")))+"') AS INTEGER) ), step2 AS ( SELECT DISTINCT s1.did, s1.KEY, s2.KEY FROM events_"+params.qstring.app_id+" AS s1 JOIN events_"+params.qstring.app_id+" AS s2 ON s1.did = s2.did WHERE s1.KEY = '"+events[0].operand+"' AND s2.KEY = '"+events[1].operand+"' "+(events[1].attr ? "AND s2.segment ->> '"+events[1].attr+"' "+common.convertToQuery(events[1].operator, events[1].value,"", events[1].operand)+""   :'' )+" AND (s2.eventtime - s1.eventtime) < CAST(extract(epoch FROM INTERVAL '"+events[1].since.time+ " "+(events[1].since.type === "d" ? "day" : (events[1].since.type === "h" ? "hours": (events[1].since.type ==="m" ? "minutes" : "seconds")))+"') AS INTEGER) AND s2.eventtime > s1.eventtime AND s2.eventtime > CAST(extract(epoch FROM now() - INTERVAL '"+ (2 * events[1].since.time)+ " "+(events[1].since.type === "d" ? "day" : (events[1].since.type === "h" ? "hours": (events[1].since.type ==="m" ? "minutes" : "seconds")))+"') AS INTEGER) AND s2.eventtime < CAST(extract(epoch FROM now()) AS INTEGER))";
                                          sql = query + step2Count;
                                          userQuery = query + step2User;
                                          if(selectors.isAudienceExport){
                                            userQuery+= `SELECT DISTINCT a.did AS deviceId, a._custom_userid AS "'"Customer ID"'" FROM app_users_${app_id} a INNER JOIN step3 b ON a.did = b.did;`
                                          }
                                    }
                                    else if(events[0].attr != ''){
                                            if(events[1].value == true || events[1].value == false){ 
                                               let query = "WITH step1 AS ( SELECT DISTINCT s1.did, s1.KEY FROM events_"+params.qstring.app_id+" AS s1 WHERE s1.KEY = '"+events[0].operand+"'"+(events[0].attr ? "AND s1.segment ->> '"+events[0].attr+"' "+common.convertToQuery(events[0].operator, events[0].value,"", events[0].operand)+""   :'' )+"), step2 AS ( SELECT DISTINCT s1.did, s1.KEY, s2.KEY FROM events_"+params.qstring.app_id+" AS s1 JOIN events_"+params.qstring.app_id+" AS s2 ON s1.did = s2.did WHERE s1.KEY = '"+events[0].operand+"' AND s2.KEY = '"+events[1].operand+"' "+(events[1].attr ? "AND s2.segment ->> '"+events[1].attr+"' "+common.convertToQuery(events[1].operator, events[1].value,"", events[1].operand)+""   :'' )+" AND s1.eventtime < s2.eventtime) ";
                                               sql = query + step2Count;
                                               userQuery = query + step1User
                                            }else{
                                                let query = "WITH step1 AS ( SELECT DISTINCT s1.did, s1.KEY FROM events_"+params.qstring.app_id+" AS s1 WHERE s1.KEY = '"+events[0].operand+"'"+(events[0].attr ? "AND s1.segment ->> '"+events[0].attr+"' "+common.convertToQuery(events[0].operator, events[0].value,"", events[0].operand)+""   :'' )+"), step2 AS ( SELECT DISTINCT s1.did, s1.KEY, s2.KEY FROM events_"+params.qstring.app_id+" AS s1 JOIN events_"+params.qstring.app_id+" AS s2 ON s1.did = s2.did WHERE s1.KEY = '"+events[0].operand+"' AND s2.KEY = '"+events[1].operand+"' "+(events[1].attr ? "AND s2.segment ->> '"+events[1].attr+"' "+common.convertToQuery(events[1].operator, events[1].value,"", events[1].operand)+""   :'' )+" AND s1.eventtime < s2.eventtime) ";
                                                sql = query + step2Count;
                                                userQuery = query + step2User;
                                            }
                                    }
                                    else if(events[1].attr == '' && events[0].attr == '' ){
                                            if(events[1].value == true || events[1].value == false){ 
                                                let query = "WITH step1 AS ( SELECT DISTINCT s1.did, s1.KEY FROM events_"+params.qstring.app_id+" AS s1 WHERE s1.KEY = '"+events[0].operand+"'), step2 AS ( SELECT DISTINCT s1.did, s1.KEY, s2.KEY FROM events_"+params.qstring.app_id+" AS s1 JOIN events_"+params.qstring.app_id+" AS s2 ON s1.did = s2.did WHERE s1.KEY = '"+events[0].operand+"' AND s2.KEY = '"+events[1].operand+"'  AND s2.dt > Now() - interval '"+events[1].since.time+ " "+(events[1].since.type === "d" ? "day" : (events[1].since.type === "h" ? "hours": (events[1].since.type ==="m" ? "minutes" : "seconds")))+"' AND s1.eventtime < s2.eventtime) ";
                                                sql = query + step2Count;
                                                userQuery = query + step1User;
                                            }else{
                                                let query = "WITH step1 AS ( SELECT DISTINCT s1.did, s1.KEY FROM events_"+params.qstring.app_id+" AS s1 WHERE s1.KEY = '"+events[0].operand+"'), step2 AS ( SELECT DISTINCT s1.did, s1.KEY, s2.KEY FROM events_"+params.qstring.app_id+" AS s1 JOIN events_"+params.qstring.app_id+" AS s2 ON s1.did = s2.did WHERE s1.KEY = '"+events[0].operand+"' AND s2.KEY = '"+events[1].operand+"'  AND s2.dt > Now() - interval '"+events[1].since.time+ " "+(events[1].since.type === "d" ? "day" : (events[1].since.type === "h" ? "hours": (events[1].since.type ==="m" ? "minutes" : "seconds")))+"' AND s1.eventtime < s2.eventtime) ";
                                                sql = query  + step2Count;
                                                userQuery = query + step2User;
                                            }
                                    }
                                    else{
                                        if(eventsDataLength == 0){
                                            if(events[0].value == true || events[0].value == false){ 
                                                let query = " from events_"+params.qstring.app_id+" where key "+operator+" '"+events[0].operand+"' AND  segment @> '{"+'"'+events[0].attr+'"'+" : "+events[0].value+" }'";
                                                sql = step1Count + query;
                                                userQuery = step1User + query;
                                            }else{
                                                let query = " from events_"+params.qstring.app_id+" where key "+operator+" '"+events[0].operand+"' AND  segment @> '{"+'"'+events[0].attr+'"'+" : "+'"'+events[0].value+'"'+" }'";
                                                sql = step1Count + query;
                                                userQuery = step1User + query;
                                            }
                                        }
                                        else if(eventsDataLength == 1){
                                            let query = " from events_"+params.qstring.app_id+" where key "+operator+" '"+events[1].operand+"' AND  segment @> '{"+'"'+events[1].attr+'"'+" : "+'"'+events[1].value+'"'+" }'";
                                            sql = step1Count + query;
                                            userQuery = step1User + query;
                                        }
                                        else if(eventsDataLength == 2){
                                            let query = " from events_"+params.qstring.app_id+" where key "+operator+" '"+events[0].operand+"' AND segment @> '{"+'"'+events[0].attr+'"'+" : "+'"'+events[0].value+'"'+" AND key "+operator+" '"+events[1].operand+"' AND segment @> '{"+'"'+events[1].attr+'"'+" : "+'"'+events[1].value+'"'+" AND key "+operator+" '"+events[2].operand+"' AND segment @> '{"+'"'+events[2].attr+'"'+" : "+'"'+events[2].value+'"'+" }'";
                                            sql = step1Count + query;
                                            userQuery = step1User + query;
                                        }
                                        else if(eventsDataLength == 3){
                                            let query = " from events_"+params.qstring.app_id+" where key "+operator+" '"+events[0].operand+"' AND segment @> '{"+'"'+events[0].attr+'"'+" : "+'"'+events[0].value+'"'+" AND key "+operator+" '"+events[1].operand+"' AND segment @> '{"+'"'+events[1].attr+'"'+" : "+'"'+events[1].value+'"'+" AND key "+operator+" '"+events[2].operand+"' AND segment @> '{"+'"'+events[2].attr+'"'+" : "+events[2].value+" AND key "+operator+" '"+events[3].operand+"' AND segment @> '{"+'"'+events[3].attr+'"'+" : "+events[3].value+" }'";
                                            sql = step1Count + query;
                                            userQuery = step1User + query;
                                        }else{
                                            let query = " from events_"+params.qstring.app_id+" where key "+operator+" '"+events[0].operand+"' AND segment @> '{"+'"'+events[0].attr+'"'+" : "+events[0].value+" AND key "+operator+" '"+events[1].operand+"' AND segment @> '{"+'"'+events[1].attr+'"'+" : "+events[1].value+" AND key "+operator+" '"+events[2].operand+"' AND segment @> '{"+'"'+events[2].attr+'"'+" : "+events[2].value+" AND key "+operator+" '"+events[3].operand+"' AND segment @> '{"+'"'+events[3].attr+'"'+" : "+events[3].value+" AND key "+operator+" '"+events[4].operand+"' AND segment @> '{"+'"'+events[4].attr+'"'+" : "+events[4].value+"}'";
                                            sql = step1Count + query;
                                            userQuery = step1User + query;
                                        }
                                    }
                                }
                                logger.info(`sql query apps file => ${sql}`)
                                // Run pg query
                                appsApi.executeAudienceCountQuery(sql, selectors,"n", userQuery).then((reachCount) =>{
                                    logger.info(`== executeAudienceCountQuery === ${reachCount}`);
                                    return common.returnMessage(params, 200,  reachCount);
                                })
                                .catch(error =>{
                                    logger.error(`catch error ::>>>${error}`);
                                    return common.returnMessage(params, 200,  error);
                                });
                            }
                            else{
                                logger.error(`event count reach error =>`);
                            }
                        });
                    }
                    else{
                        sql = '';
                        let criteria = criteriaData;
                        let criteriaLength = criteria.length;
                        if (criteriaLength > 0) {
                            if (criteriaLength == 1) {
                                /* one level who query*/
                                if(criteria[0].category == 'Custom Segment'){
                                    sql = `select DISTINCT did from app_users_${params.qstring.app_id} where audsegment @> '{"audid${criteria[0].value}":"${criteria[0].value}"}'`;
                                }else{
                                    sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                                }
                            }
                            if (criteriaLength == 2) {
                                /* code for who + what condition query */
                                if (criteria[0].category == 'Device' || criteria[0].category == 'App Usage' || criteria[0].category == 'Custom Vars' || criteria[0].category == 'Competing Apps' || criteria[0].category == 'Geo') {
                                    if(criteria[1].category == 'Custom Segment'){
                                        sql = `WITH step1 AS (SELECT DISTINCT s1.did FROM app_users_${params.qstring.app_id} AS s1 WHERE ${criteria[0].operand} ${common.convertToQuery(criteria[0].operator, criteria[0].value, "", criteria[0].operand, criteria[0].timeUnit)} ), step2 AS  (select DISTINCT did from app_users_${params.qstring.app_id} where audsegment @> '{"audid${criteria[1].value}":"${criteria[1].value}"}') SELECT DISTINCT step1.did from step1 INNER JOIN step2 ON step1.did = step2.did`;
                                     }else{
                                       sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                                    }
                                }
                            }
                            if (criteriaLength == 3) {
                                /* code for who + what condition query */
                                if(criteria[1] && criteria[1].since && criteria[1].since.have === "h" && criteria[2].since.have === "hn"){
                                   sql = `WITH step1 AS (SELECT DISTINCT s1.did FROM app_users_${params.qstring.app_id} AS s1 WHERE ${criteria[0].operand} ${common.convertToQuery(criteria[0].operator, criteria[0].value, "", criteria[0].operand, criteria[0].timeUnit)}), step2 AS (SELECT DISTINCT s2.did, s2.KEY FROM events_${params.qstring.app_id} AS s2 WHERE s2.KEY = '${criteria[1].operand}' ${criteria[1].attr ? `AND s2.segment ->> '${criteria[1].attr}' ${common.convertToQuery(criteria[1].operator, criteria[1].value, "", criteria[1].operand)}` : ""} AND s2.eventtime > CAST(extract(epoch FROM now() - INTERVAL '${2 * criteria[2].since.time} ${criteria[2].since.type === "d" ? "day" : (criteria[2].since.type === "h" ? "hours": (criteria[2].since.type ==="m" ? "minutes" : "seconds"))}') AS INTEGER) AND s2.eventtime < CAST(extract(epoch FROM now() - INTERVAL '${criteria[2].since.time} ${criteria[2].since.type === "d" ? "day" : (criteria[2].since.type === "h" ? "hours": (criteria[2].since.type ==="m" ? "minutes" : "seconds"))}') AS INTEGER)), step3 AS (SELECT DISTINCT s2.did, s2.KEY, s3.KEY FROM events_${params.qstring.app_id} AS s2 JOIN events_${params.qstring.app_id} AS s3 ON s2.did = s3.did WHERE s2.KEY = '${criteria[1].operand}' AND s3.KEY = '${criteria[2].operand}' ${criteria[2].attr ? `AND s2.segment ->> '${criteria[2].attr}' ${common.convertToQuery(criteria[2].operator, criteria[2].value, "", criteria[2].operand)}` : ""} AND (s3.eventtime - s2.eventtime) < CAST(extract(epoch FROM INTERVAL '${criteria[2].since.time} ${criteria[2].since.type === "d" ? "day" : (criteria[2].since.type === "h" ? "hours": (criteria[2].since.type ==="m" ? "minutes" : "seconds"))}') AS INTEGER) AND s3.eventtime > s2.eventtime AND s3.eventtime > CAST(extract(epoch FROM now() - INTERVAL '${2 * criteria[2].since.time} ${criteria[2].since.type === "d" ? "day" : (criteria[2].since.type === "h" ? "hours": (criteria[2].since.type ==="m" ? "minutes" : "seconds"))}') AS INTEGER)  AND s3.eventtime < CAST(extract(epoch FROM now()) AS INTEGER)), step4 AS (SELECT DISTINCT step1.did FROM step1 INNER JOIN step2 ON step1.did = step2.did) ${selectors.isAudienceExport ? ', step5 as (': ''} SELECT DISTINCT step4.did FROM step4 LEFT JOIN step3 ON step4.did = step3.did WHERE step3.did IS NULL ${selectors.isAudienceExport ? ')': ''}`;

                                   if(selectors.isAudienceExport){
                                    sql+= `SELECT DISTINCT a.did AS deviceId, a._custom_userid AS "'"Customer ID"'" FROM app_users_${app_id} a INNER JOIN step5 b ON a.did = b.did;`
                                   }
                                }else {
                                if (criteria[0].category == 'Device' || criteria[0].category == 'App Usage' || criteria[0].category == 'Custom Vars' || criteria[0].category == 'Competing Apps' || criteria[0].category == 'Geo') {
                                   
                                    sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);

                                }
                             }
                            }
                            if (criteriaLength == 4){

                                sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                            }
                            if (criteriaLength == 5){
        
                                sql = appsApi.generateQuery(criteria,app_id, selectors.isAudienceExport);
                            }

                            // Run pg query
                            logger.info(`sql audience query ${sql}`);
                            appsApi.executeAudienceCountQuery(sql, selectors,"length").then((reachCount) =>{
                                logger.info(`== executeAudienceCountQuery === ${reachCount}`);
                                return common.returnMessage(params, 200,  reachCount);
                            })
                            .catch(error =>{
                                logger.error(`catch error ::>>>${error}`);
                                return common.returnMessage(params, 200,  error);
                            });
                        } else {
                            return common.returnMessage(params, 200, 0);
                        }
                    }
                }      
            } else {
                common.returnMessage(params, 200, 'Criteria Not Found');
            }
    }

    //function for sql exucute and return count or result or query for reachcount and data download 
    appsApi.executeAudienceCountQuery = function(sql, selector, type, userQuery){
        if(userQuery == '' || userQuery == undefined){
            userQuery = sql;
        }
        return new Promise((resolve, reject) =>{
            if(selector.execute == true){
                psqlUtils.executeQuery(sql)
                .then((result) => {
                    logger.info(`result=> ${JSON.stringify(result[0].length)}`);
                    if (result) {
                        let count = 0;
                        if(type == 'length'){
                            count = result[0].length;
                        }else{
                            count = Number(result[0][0].count);
                        }
                        let random_id = db.ObjectId();
                        let randomUser_id = db.ObjectId();
                        cacheApi.setKeywithTTL(random_id, sql, 5000);
                        cacheApi.setKeywithTTL(randomUser_id, userQuery, 5000);
                        let res =  { "count" : count,"q": random_id, "userQ": randomUser_id};
                        resolve(res);
                    } else {
                        logger.error(`event count reach error =>`);
                        let random_id = db.ObjectId();
                        let randomUser_id = db.ObjectId();
                        cacheApi.setKeywithTTL(random_id, sql, 5000);
                        cacheApi.setKeywithTTL(randomUser_id, userQuery, 5000);
                        let res =  { "count" : 0,"q": random_id, "userQ": randomUser_id};
                        resolve(res);
                    }
                    })
                    .catch((error) => {
                        logger.error(`event count reach error =>`);
                        reject(error);
                    })
            }else{
                        let random_id = db.ObjectId();
                        let randomUser_id = db.ObjectId();
                        cacheApi.setKeywithTTL(random_id, sql, 5000);
                        cacheApi.setKeywithTTL(randomUser_id, userQuery, 5000);
                let res =  { "count" : 0,"q": random_id, "userQ": randomUser_id};
                resolve(res);
            }
        })
    }
    
    appsApi.countAudienceReachOld = function(params){
        if(params.qstring.criteria)
        {
            let criteria =  JSON.parse(params.qstring.criteria);
            if(criteria.length==0){
                common.returnMessage(params,200, 0);
                return false;
            }
            let events = [];
            let tmpCriteria = [];
            let filters = [];

            // prepare filter and copy of criteria
            common.prepareFilter(criteria, filters, tmpCriteria);
            tmpCriteria.forEach(function(value, key){
                if(value.category == 'Events'){
                    events.push(value);
                    criteria.splice(key);
                }
            });

            if(events.length > 0 && common.config.IsProduction){
                cacheApi.getKey('app_evnets'+params.qstring.app_id, function(error, eventsList){
                    if(eventsList != null && eventsList != ''){
                          eventsList = JSON.parse(eventsList);
                          // (0[1-9])(.*?)#3(?=#)(.*?)#9(?=#)) // ((#72(?=#))|(#81(?=#)))
                          let cDay = moment().format('DD');
                          let cYearMon = moment().format('YYYY-MM');

                          let query = "SELECT DISTINCT count(did) FROM app_users_"+params.qstring.app_id+" WHERE eventstring ~ '(#"+cYearMon+"-(0[1-"+cDay+"])";
                          events.forEach(function(item, index){
                              if(events[index+1] && events[index+1].value == false){
                                  query += '((#'+eventsList[item.operand]+'(?=#))|';
                              }
                              else{
                                  if(query.length-1 == query.lastIndexOf('|')){
                                      query += '(#'+eventsList[item.operand]+'(?=#)))';
                                  } else {
                                      query += '(.*?)#'+eventsList[item.operand]+'(?=#)';
                                  }
                              }
                          });

                          query += ")'";

                          // citus query
                          common.pgCitusDBClient.query(query, function(error, res){
                              if(error){
                                  logger.error(`error in citus`);
                              }
                              else {
                                  logger.error(`res => ${JSON.stringify(res)}`);
                                  common.returnMessage(params, 200,res.rows[0].count);
                              }
                          });
                    }
                    else{
                          common.returnMessage(params, 200,0);
                    }
                });
            }
            else{
                let match = common.constructMatchFromCriteria(criteria);
                common.db.collection('app_users'+params.qstring.app_id).aggregate([
                    {$match: {active:true}},
                    {$match: match},
                    {$group:{"_id":null,count:{$sum:1}}}
                ],function(err,result){
                    if(!err && result.length>0){
                        common.returnMessage(params, 200,result[0].count);
                    }
                    else{
                        common.returnMessage(params,200, 0);
                    }
                });
            }
        }
        else{
            common.returnMessage(params, 200, 'Criteria Not Found');
        }
    }

    /*Returns all teh audience segments for the given app*/
    appsApi.getAudienceSegments = function(params)
    {
        appsApi.checkDefaultAudSegments(params.qstring.app_id, function(err, result){
            if(!err && result){
                common.db.collection('audiencesegment_'+params.qstring.app_id).find({"$or": [{'isDeleted':false},{'isDeleted':null}]}).sort({createdate:-1}).toArray(function(err, audiencesegments) {
                    if (!err && audiencesegments) {
                        common.returnMessage(params, 200, audiencesegments);
                    } else {
                        common.returnMessage(params, 200, 'No Segments Found');
                    }
                });
            }
            else{
                common.returnMessage(params, 200, 'No default Segments Found');
            }
        });

        return true;
    };

    appsApi.checkDefaultAudSegments = function(appId, callback){
        let defaultAudSegment = common.defaultAudienceSegments();
        common.db.collection('audiencesegment_'+appId).find({"name": {"$in": defaultAudSegment}}).toArray(function(err, audiencesegments) {
            if (!err && audiencesegments) {
                if(audiencesegments.length > 0 ){ // return callback if default aud segments exists
                    callback(err, true);
                }
                else{ // insert default aud segments
                    appsApi.createAppAudSegment(appId, function(result){
                        callback(null, result);
                    });
                }
            } else {
                callback(err, null);
            }
        });
    }


    appsApi.getAudienceSegmentById = function(params){
         common.db.collection('audiencesegment_'+params.qstring.app_id).findOne({"_id":common.db.ObjectID(params.qstring.aud_id),"$or": [{'isDeleted':{'$eq':false}},{'isDeleted':null}]},function(err, audiencesegment) {
                if (!err && audiencesegment) {
                    common.returnMessage(params, 200, audiencesegment);
                } else {
                    common.returnMessage(params, 200, 'No Segments Found');
                }
            });

        return true;
    };

    appsApi.get_metavariables = async function(params)
    {
            let argProps = {'variablename': { 'required': true, 'type': 'String' }},
            apiVars = {};

        if (!(apiVars = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
        if(apiVars.variablename === 'device_lang'){
            const column_value = await psqlUtils.executeQuery(`SELECT * FROM masterlanguage;`);
            const mapedArray = column_value[0].map((item) => {
                return {
                    text : item.isolanguage,
                    label : item.id
                }
            })
           return common.returnMessage(params, 200, (mapedArray));
        }

         common.db.collection('app_users'+params.qstring.app_id).distinct(apiVars.variablename,function(err,metaVariables) {
                if (!err && metaVariables) {
                    common.returnMessage(params, 200, metaVariables);
                } else {
                    common.returnMessage(params, 200, 'No Cities Found');
                }
        });

        return true;
    };

    // This function will save the custom feilds and Script code from the frontend in the db 
    
    appsApi.save_custom = function (params) {

        let argProps = {
            'app_id': {
                'required': true,
                'type': 'String',
            },
            'customFields': {       // Custom Feilds 
                'required': false,
                'type': 'JSON'
            },
            'scriptCode': {         // Script code in text area
                'required': false,
                'type': 'String'
            }
        };


        params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        // default save button contain only customFeilds
        let updateObj = {
            'customFields': params.qstring.args.customFields ,
        }


        // if code is not undefined, then only set its value and flag in db 
        if(params.qstring.args.scriptCode !== undefined){
            updateObj.scriptCode = params.qstring.args.scriptCode
            updateObj.isScriptCode = true
        }
    
        common.db.collection('apps').update({ "_id": common.db.ObjectID(params.qstring.args.app_id) }, { $set: updateObj }, (err, data) => {
            if (!err && data) {
              
                common.returnOutput(params, data);
            } else {
                common.returnOutput(params, err);
            }
        })

    }

    appsApi.createAudSegment = async function(params) {
        //strip off $ signs

        let argProps = {
                'name': {
                    'required': true,
                    'type': 'String',
                    'regex': validate.getRegex('name')
                },
                'description': {
                    'required': false,
                    'type': 'String',
                    'regex': validate.getRegex('name')
                },
                'objectid': {
                    'required': true,
                    'type': 'String'
                },
                'createdate': {
                    'required': false,
                    'type': 'Number'
                },
                'range': {
                    'required': false,
                    'type': 'Number'
                },
                'segmentinfo': {
                    'required': false,
                    'type': 'JSON'
                }
            };
            params.qstring.segmentinfo = (params.qstring && params.qstring.segmentinfo  )? JSON.parse(params.qstring.segmentinfo) : ''
            params.qstring.range =  (params.qstring.range ) ? parseInt(params.qstring.range) : 0
            let apiVars = {}
            if (!(apiVars = common.validateArgs(params.qstring, argProps))) {
                common.returnMessage(params, 422, 'Validation error');
                return false;
            }

            //Creating queueObj
            let queueObj ={}

            //Incase of custom upload
        if (params.qstring.customUpload) {
            logger.info(`Case of Custom csv Upload`)

            try {

                // Upload csv to cloud and create QueueObj
                queueObj = await UploadCustomcsv(params.qstring);
                logger.info(`Queue Object Created For Custom CSV`)
            } catch (error) {

                logger.error(`Audience Failed in custom Csv Upload ::>>>${error}`);
                common.returnMessage(params, 403, 'Upload Has been Failed ');
                 return false;
            }
        }

        let newAudSegment = {...apiVars};
        newAudSegment.range = params.qstring.range;
        newAudSegment.type = 'user';
        newAudSegment.isDeleted = false;
        // Audid is taken from ObjectId
        newAudSegment.audid = common.db.ObjectID(apiVars.objectid);
        newAudSegment.createdate = moment().valueOf();
        //if it is custom
        if (params.qstring.customUpload) {
            //create _id manually
            newAudSegment._id = newAudSegment.audid;

        }
        appsApi.getQuery(apiVars.segmentinfo.q).then((sql) =>{
            apiVars.segmentinfo.q = Buffer.from(sql).toString('base64');
            appsApi.getQuery(apiVars.segmentinfo.userQ).then((sqlUser) =>{
                apiVars.segmentinfo.userQ = Buffer.from(sqlUser).toString('base64');
                common.db.collection('audiencesegment_' + params.qstring.app_id).insert(newAudSegment, function(err, app) {
                    if(err){
                        logger.info(`err in create auidnece ${JSON.stringify(err)}`);
                        common.returnOutput(params, "err while creating audience segment");
                    }else{
                        delete newAudSegment.segmentinfo.q;
                        delete newAudSegment.segmentinfo.userQ;
                        logger.info(`Audience Created Successfully`)

                        // Check if it is Custom Upload
                        if (params.qstring.customUpload) {
                            logger.info(`Case of Custom csv Upload`)

                            // Retrieve uploaded URL
                            const emailUrl = queueObj.metadata.dataObject.emailUrl;

                            // Remove uploaded URL from queue object
                            delete queueObj.metadata.dataObject.emailUrl;
                         

                            logger.info(`Queue-Obj populated during Saving as  ${JSON.stringify(queueObj)}`)
                            // Send data to queue directly
                            const qConfig = {
                                topic: TOPIC.FILE_SEGMENT,          
                                clientType: QueueClientEnum.PRODUCER
                            };
                    
                            psqlUtils.enqueuePayload(queueObj,params.qstring.app_id, qConfig )
                            // Prepare data object for sending email
                            const dataObj = {
                                app_id: params.qstring.app_id,
                                email: queueObj.metadata.dataObject.email,
                                audid: apiVars.objectid,        // Audid is taken from ObjectId in case of email also 
                                fileUrl: emailUrl
                            };
                            logger.info(`Data Object Created Successfully for Email`)

                            // Sending email for custom segment
                            appsApi.sendEmailCustomSegment(dataObj);
                        
                            // Return the flow
                            common.returnOutput(params, newAudSegment);
                        } else {
                            // If not custom upload, return the flow
                            common.returnOutput(params, newAudSegment);
                        }
                        
                    }      
                });
            })
            .catch(error =>{
                logger.error(`audence query catch error ::>>>${error}`);
                common.returnOutput(params, "audence query catch error ::>>>");
            });
        })
        .catch(error =>{
            common.returnOutput(params, "audence query catch error ::>>>");
        });
    };

    appsApi.getQuery = function(sqlno){
        return new Promise((resolve, reject) =>{
            cacheApi.getKey(sqlno, function(error, query){
                if(!error){
                    resolve(query !== null ? query : sqlno);
                }else{
                    reject(0)
                }
            })
        })
    }

    appsApi.updateAudSegment= async function(params)
    {
        //strip off $ signs
        let argProps = {
                'name': { 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
                'description': { 'required': false, 'type': 'String', 'regex': validate.getRegex('name') },
                'createdate': { 'required': false, 'type': 'Number' },
                'range': { 'required': false, 'type': 'Number' },
                'objectid': {'required': true,'type': 'String'},
                'segmentinfo': { 'required': false, 'type': 'JSON' }
            };
        let apiVars = {}
        params.qstring.segmentinfo = (params.qstring && params.qstring.segmentinfo  )? JSON.parse(params.qstring.segmentinfo) : ''
        params.qstring.range =  (params.qstring.range ) ? parseInt(params.qstring.range) : 0
        if (!(apiVars = common.validateArgs(params.qstring, argProps))) {
            common.returnMessage(params, 422, 'Validation error');
            return false;
        }

        //Creating queueObj
        let queueObj ={}

        //Incase of custom upload
        if (params.qstring.customUpload) {
            logger.info(`Case of Custom csv Upload`)
            try {
                // Upload csv to cloud and create QueueObj
                queueObj = await UploadCustomcsv(params.qstring);
                logger.info(`Queue Object Created For Custom CSV`)
            } catch (error) {
                logger.info(`Audience Failed in custom Csv Upload ::>>>${error}`);
                common.returnMessage(params, 403, 'Upload Has been Failed ');
                return false;
            }
        }
        let newAudSegment = {...apiVars};
        newAudSegment.range = params.qstring.range;
        newAudSegment.type = 'user';
        newAudSegment.isDeleted = false;
        newAudSegment.audid = common.db.ObjectID(apiVars.objectid);
        newAudSegment.modifiedOn = moment().valueOf();

        appsApi.getQuery(apiVars.segmentinfo.q).then((sql) =>{
            apiVars.segmentinfo.q = Buffer.from(sql).toString('base64');
            appsApi.getQuery(apiVars.segmentinfo.userQ).then((sqlUser) =>{
                apiVars.segmentinfo.userQ = Buffer.from(sqlUser).toString('base64');
                common.db.collection('audiencesegment_'+params.qstring.app_id).update({"_id":common.db.ObjectID(params.qstring.objectid)},{$set: newAudSegment},{'upsert':true}, function(err, app) {
                    if(err){
                        logger.info(`err in create auidnece ${JSON.stringify(err)}`);
                        common.returnOutput(params, "err while creating audience segment");
                    }else{
                        delete newAudSegment.segmentinfo.q;
                        delete newAudSegment.segmentinfo.userQ;
                        logger.info(`Audience Created Successfully`)

                        // Check if it is Custom Upload
                        if (params.qstring.customUpload) {
                            logger.info(`Case of Custom csv Upload`)

                            // Retrieve uploaded URL
                            const emailUrl = queueObj.metadata.dataObject.emailUrl;

                            // Remove uploaded URL from queue object
                            delete queueObj.metadata.dataObject.emailUrl;
                         
                            logger.info(`Queue-Obj populated during updated as  ${JSON.stringify(queueObj)}`)

                            // Send data to queue directly
                            const qConfig = {
                                topic: TOPIC.FILE_SEGMENT,          
                                clientType: QueueClientEnum.PRODUCER
                            };
                    
                            psqlUtils.enqueuePayload(queueObj,params.qstring.app_id, qConfig )
                            // Prepare data object for sending email
                            const dataObj = {
                                app_id: params.qstring.app_id,
                                email: queueObj.metadata.dataObject.email,
                                audid: apiVars.objectid,            // Audid is taken from ObjectId in case of email also 
                                fileUrl: emailUrl
                            };
                            logger.info(`Data Object Created Successfully for Email`)

                            // Sending email for custom segment
                            appsApi.sendEmailCustomSegment(dataObj);
                        
                            // Return the flow
                            common.returnOutput(params, newAudSegment);
                        } else {
                            // If not custom upload, return the flow
                            common.returnOutput(params, newAudSegment);
                        }
                        
                    }                            
                });
            })
            .catch(error =>{
                logger.error(`audence query catch error ::>>>${error}`);
                common.returnOutput(params, "audence query catch error ::>>>");
            });
        })
        .catch(error =>{
            common.returnOutput(params, "audence query catch error ::>>>");
        });

        
    };

    /**
     * validate segment Name
     * @Author Manish Yadav
     **/
    appsApi.validateSegmentName = function(params)
    {
       // let name = "/^"+params.qstring.segmentName+"$/i";
        let name = new RegExp(["^", params.qstring.segmentName, "$"].join(""), "i");
        if(params.qstring.segmentName){
            common.db.collection('audiencesegment_'+params.qstring.app_id).find({"name":name, "$or": [{'isDeleted':false},{'isDeleted':null}]}).toArray(function(err,result) {
                    if (!err && result) {
                        common.returnMessage(params, 200, (result.length == 0)? false : true);
                    } else {
                        common.returnMessage(params, 200, 'No Segment Name Found');
                    }
            });
        }
        else{
            common.returnMessage(params, 200, 'Segment Name Not Found');
        }
    };

    appsApi.deleteAudSegment = function (params)
    {
          let argProps = {
                '_id': { 'required': true, 'type': 'String' }
            },
            audSegment = {};


        if (!(audSegment = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        let modifiedOn = common.getCurrentEpochTime();
        common.db.collection('audiencesegment_'+params.qstring.app_id).update({"_id":common.db.ObjectID(audSegment._id)},{$set: {'isDeleted': true, 'modifiedOn':modifiedOn}},{'upsert':true}, function(err, app) {
            common.returnOutput(params, audSegment);
        });
    };

    appsApi.updateGCMKeysOld = function (params) {
        // update FCM database information
        if(params.qstring.args.fcmKey){
            let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'fcmKey': { 'required': true, 'type': 'String'},
                'mode': { 'required': true, 'type': 'String'}
            },
            newfcmdb = {};

            if (!(newfcmdb = common.validateArgs(params.qstring.args, argProps))) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }

            // let updateKeys = {
            //     ['fcmdb_'+params.qstring.args.mode]: params.qstring.args.fcm_db,
            //     'modifiedOn':common.getCurrentEpochTime()
            // }
            let updateKeys = {
                ['fcmKey_'+params.qstring.args.mode]: params.qstring.args.fcmKey,
                'modifiedOn':common.getCurrentEpochTime()
            }
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set:updateKeys}, function(err, app) {
                common.returnOutput(params, newgcmKey);
            });
        }
        else{ // update GCM key information
            let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'gcm_key': { 'required': true, 'type': 'String'}
            },
            newgcmKey = {};
            appId = '';

            if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }

            let modifiedOn = common.getCurrentEpochTime();

            if(params.qstring.isdev=='true')
            {
                common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'dev_gcm_key': params.qstring.args.gcm_key, 'modifiedOn':modifiedOn}}, function(err, app) {
                    common.returnOutput(params, newgcmKey);
                });
            }
            else
            {
                common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'gcm_key': params.qstring.args.gcm_key, 'modifiedOn':modifiedOn}}, function(err, app) {
                    common.returnOutput(params, newgcmKey);
                });
            }
        }

        //Clear this app from redis cache as its details are updated.
        cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
            logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
        });
        return true;
    };

    appsApi.updateGCMKeys = function (params) {
        params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;

        let updateKeys = {};
        let redisData = {};

        // update FCM database information
        if(params.qstring.args.webPush){
            let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'webPush': { 'required': true, 'type': 'JSON'},
                'mode': { 'required': true, 'type': 'String'}
            },
            webPush = {};

            if (!(webPush = common.validateArgs(params.qstring.args, argProps))) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }
            if(params.qstring.args.mode == 'dev') {
                updateKeys = {
                    ['webPush_' + params.qstring.args.mode]: webPush.webPush,
                    "push_notification.wpn.dev" : {
                        ['webPush_' + params.qstring.args.mode]: webPush.webPush
                    },
                    'modifiedOn': common.getCurrentEpochTime()
               }
               redisData = JSON.parse(JSON.stringify(updateKeys));
               delete redisData['push_notification.wpn.dev'];
               delete redisData.modifiedOn;
               
               redisData.privateKey = redisData.webPush_dev.privateKey;
               redisData.publicKey = redisData.webPush_dev.publicKey;  
               redisData.subject = redisData.webPush_dev.subject;

               delete redisData.webPush_dev.privateKey;
               delete redisData.webPush_dev.publicKey;
               delete redisData.webPush_dev.subject;
               delete redisData.webPush_dev;
            }
            else{
                updateKeys = {
                    ['webPush_' + params.qstring.args.mode]: webPush.webPush,
                    "push_notification.wpn.prod" : {
                        ['webPush_' + params.qstring.args.mode]: webPush.webPush
                    },
                    'modifiedOn': common.getCurrentEpochTime()
                }

                 redisData = JSON.parse(JSON.stringify(updateKeys));
                  delete redisData['push_notification.wpn.prod'];
                  delete redisData.modifiedOn;
                  
                  redisData.privateKey = redisData.webPush_prod.privateKey;
                  redisData.publicKey = redisData.webPush_prod.publicKey;  
                  redisData.subject = redisData.webPush_prod.subject;

                  delete redisData.webPush_prod.privateKey;
                  delete redisData.webPush_prod.publicKey;
                  delete redisData.webPush_prod.subject;
                  delete redisData.webPush_prod;


            }
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set:updateKeys}, function(err, app) {
                  // Store the same data in Redis

            cacheApi.setKey(`fcm_webpush_${params.qstring.args.app_id}`, JSON.stringify(redisData), (redisErr, redisRes) => {
                if (redisErr) {
                    logger.error('Error storing data in Redis:', err);
                } else {
                    logger.info(`Data successfully stored in Redis==>>${redisKey}=============${redisRes}`);
                }
            });
                common.returnOutput(params, updateKeys);
            });
        }
        //update EPNU key
        else if(params.qstring.args.epnu_key){
          if(params.qstring.args.mode == 'dev') {
               updateKeys = {
                ['epnu_key_' + params.qstring.args.mode]: params.qstring.args.epnu_key,
                "push_notification.epnu.dev" : {
                    ['epnu_key_' + params.qstring.args.mode]: params.qstring.args.epnu_key
                },
                'modifiedOn': common.getCurrentEpochTime()
            }
        }else{
                updateKeys = {
                ['epnu_key_' + params.qstring.args.mode]: params.qstring.args.epnu_key,
                "push_notification.epnu.prod" : {
                    ['epnu_key_' + params.qstring.args.mode]: params.qstring.args.epnu_key
                },
                'modifiedOn': common.getCurrentEpochTime()
            }
        }
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set:updateKeys}, function(err, app) {
                common.returnOutput(params, updateKeys);
            });
        }
        // update FCM database information
        else if(params.qstring.args.http_proxy){
            let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'http_proxy': { 'required': true, 'type': 'String'},
                'mode': { 'required': true, 'type': 'String'}
            },
            newfcmdb = {};

            if (!(newfcmdb = common.validateArgs(params.qstring.args, argProps))) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }
            if(params.qstring.args.mode == 'dev') {
                 updateKeys = {
                ['fcmKey_' + params.qstring.args.mode]: newfcmdb.fcmKey,
                "push_notification.fcm.dev" : {
                    ['fcmKey_' + params.qstring.args.mode]: newfcmdb.fcmKey
                },
                'modifiedOn': common.getCurrentEpochTime()
            }
            redisData = JSON.parse(JSON.stringify(updateKeys));
            delete redisData['push_notification.fcm.dev'];
            delete redisData.modifiedOn;
            
            redisData.fcmKey = redisData.fcmKey_dev;
            delete redisData.fcmKey_dev;
         }
         else{
               updateKeys = {
                ['fcmKey_' + params.qstring.args.mode]: newfcmdb.fcmKey,
                "push_notification.fcm.prod" : {
                    ['fcmKey_' + params.qstring.args.mode]: newfcmdb.fcmKey
                },
                'modifiedOn': common.getCurrentEpochTime()
            }
            redisData = JSON.parse(JSON.stringify(updateKeys));
                  delete redisData['push_notification.fcm.prod'];
                  delete redisData.modifiedOn;
                  
                  redisData.fcmKey = redisData.fcmKey_prod;
                  delete redisData.fcmKey_prod;
        }
             // Check if proxyUrl is not an empty string and then add it to updateData
             if (params.qstring.args.http_proxy !== "" || params.qstring.args.http_proxy  == "") {
                updateKeys.http_proxy = params.qstring.args.http_proxy;
        }
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set:updateKeys}, function(err, app) {
                           // Store the same data in Redis
                
            cacheApi.setKey(`fcm_${params.qstring.args.app_id}`, JSON.stringify(redisData), (redisErr, redisRes) => {
                if (redisErr) {
                    logger.error('Error storing data in Redis:', err);
                } else {
                    logger.info(`Data successfully stored in Redis==>>${redisKey}=============${redisRes}`);
                }
            });
                common.returnOutput(params, updateKeys);
            });
        }
        else{ // update GCM key information
            let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'gcm_key': { 'required': true, 'type': 'String'}
            },
            newgcmKey = {};
            appId = '';

            if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }

            let modifiedOn = common.getCurrentEpochTime();

             if(params.qstring.args.mode == 'dev') 
            {
                common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'dev_gcm_key': params.qstring.args.gcm_key, 'modifiedOn':modifiedOn}}, function(err, app) {
                    common.returnOutput(params, newgcmKey);
                });
            }
            else
            {
                common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'gcm_key': params.qstring.args.gcm_key, 'modifiedOn':modifiedOn}}, function(err, app) {
                    common.returnOutput(params, newgcmKey);
                });
            }
        }

        //Clear this app from redis cache as its details are updated.
        cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
            logger.error(`Redis key cleared. ${params.qstring.args.app_id}`);
        });
        return true;
    };
    // getting the existing data of frequeny capping if it exists
    appsApi.getAppFrequencyCapping = function( params ) {

        let argProps = {
           'app_id': {
               'required': true,
               'type': 'String',
                }     
       };

       if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
           common.returnMessage(params, 400, 'Not enough args');
           return false;
       }
        common.db.collection('apps').find({"_id": common.db.ObjectID(params.qstring.args.app_id ),frequency_capping:{$exists:true}}, {frequency_capping:1}).toArray(function(err,data) {        
        if(err || !data) {
            logger.error(error);
               common.returnMessage(params, 500, 'Something went wrong');
           }
           else{
              common.returnOutput(params, data[0]); 
           }
       });
   }
    // Saving the data of frequency capping from frontend     
    appsApi.updateFrequencyCapping = function (params) {

        let argProps = {
            'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
            'cappingObj': { 'required': true, 'type': 'JSON'},
            'mode': { 'required': true, 'type': 'String'}
        };
      let capData = {};
      params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(capData = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set:{['frequency_capping.capping_'+params.qstring.args.mode]:capData.cappingObj}}, function(err, app) {
            if(!err){
                logger.info(`app=> ${app}`);
                common.returnOutput(params, app);
            }else{
                logger.error(`ERROR in updating frequency ${err}`);
                common.returnMessage(params, 500, 'Something went wrong');
            }
        });
               
        //Clear this app from redis cache as its details are updated.
        cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
            logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
        });
        return true;
    };


      // getting the existing data of DND from DB if it exists
      appsApi.getAppDndState = function( params ) {

        let argProps = {
           'app_id': {
               'required': true,
               'type': 'String',
                }     
       };

       if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
           common.returnMessage(params, 400, 'Not enough args');
           return false;
       }
        common.db.collection('apps').find({"_id": common.db.ObjectID(params.qstring.args.app_id ),DND_Values:{$exists:true}}, {DND_Values:1}).toArray(function(err,data) {        
        if(err || !data) {
            logger.error(`error => ${err}`);
               common.returnMessage(params, 500, 'Something went wrong');
           }
           else{
              common.returnOutput(params, data[0]); 
           }
       });
   }

        // Saving the data of Do Not Distrub in DB   
        appsApi.updateDndValue = function (params) {

            let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'Dnd_Values': { 'required': true, 'type': 'JSON'},
                'mode': { 'required': true, 'type': 'String'}
            };
            // Dnd_Values
          let Dnd_Data = {};
          params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
            if (!(Dnd_Data = common.validateArgs(params.qstring.args, argProps))) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set:{['DND_Values.mode_'+params.qstring.args.mode]:Dnd_Data.Dnd_Values}}, function(err, app) {
                if(!err){
                    logger.info(`app=> ${app}`);
                    common.returnOutput(params, app);
                }else{
                    logger.error(`Error in Saving Do Not Disturb ${error}`);
                    common.returnMessage(params, 500, 'Something went wrong');
                }
            });
                   
            //Clear this app from redis cache as its details are updated.
            cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
                logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
            });
            return true;
        };

    appsApi.updateAppiceKeys = function (params) {
        let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'appiceKeys': { 'required': true, 'type': 'String'}

            },
            newgcmKey = {};
            appId = '';

        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        let modifiedOn = common.getCurrentEpochTime();
        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'appiceKeys': params.qstring.args.appiceKeys, 'modifiedOn':modifiedOn}}, function(err, app) {
            // set app information for set into redis cache
            common.db.collection('apps').findOne({'_id': common.db.ObjectID(params.qstring.args.app_id),"isAppDeleted":"false"}, function (err, app) {
                if(err || !app){
                   logger.error('App does not exist');
                }
                else{
                    //cacheApi.setKey("app_id"+params.qstring.args.app_id,JSON.stringify({key:app.key, name:app.name, appiceKeys:params.qstring.args.appiceKeys}));
                    let obj = {};
                    obj.key = app.key;
                    app.name = (app.name)? app.name.replace(/[`~%!@#$%^&*() |+\-=?;'",.<>\{\}\[\]\\\/]/gi, '_') : app.name;
                    obj.name = app.name;
                    obj.appiceKeys = params.qstring.args.appiceKeys;
                    if(app.callbackurl){
                        obj.callbackurl = app.callbackurl;
                    }
                    cacheApi.setKey("app_id"+params.qstring.args.app_id,JSON.stringify(obj));
                }
                common.returnOutput(params, newgcmKey);
                return true;
            });
        });
    };
    // get current Account ID's information
    appsApi.getChannelAccountData = function( params ) {

        let argProps = {
           'app_id': {
               'required': true,
               'type': 'String',
           }    
       };

       if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
           common.returnMessage(params, 400, 'Not enough args');
           return false;
       }
        common.db.collection('apps').find({"_id": common.db.ObjectID(params.qstring.args.app_id ),channel_obj:{$exists:true}}, {channel_obj:1}).toArray(function(err,data) {        
        if(err || !data) {
                logger.error(`err => ${err}`);
                common.returnMessage(params, 500, 'Something went wrong');
           }
           else{
              common.returnOutput(params, data[0]); 
           }
       });
   }

//    update ads AccountID
    appsApi.updateChannelAccount = function (params) {

            let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'channelObj': { 'required': true, 'type': 'JSON'},
              };

        let data = {};
        params.qstring.args = (typeof(params.qstring.args)== 'string' ) ? JSON.parse(params.qstring.args) : params.qstring.args;
            if (!(data = common.validateArgs(params.qstring.args, argProps))) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }

        logger.info(`type => ${params.qstring.args.channelObj.type}`);
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set:{['channel_obj.channel_'+params.qstring.args.channelObj.type]:data.channelObj.account_id}}, function(err, app) {
                if(!err){
                    common.returnOutput(params, app);
                }else{
                    logger.error(`ERROR in updating Channel account  ${err}`);
                    common.returnOutput(params, null);
                }
            });
       
        //Clear this app from redis cache as its details are updated.
        cacheApi.deleteKey(params.qstring.args.app_id,function(err,res){
            logger.info(`Redis key cleared. ${params.qstring.args.app_id}`);
        });
        return true;
    };
    appsApi.getCurrentAppKeys = function (params) {


       let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 }

            },
            appId = '';

        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }


             common.db.collection('apps').find({'_id': common.db.ObjectID(params.qstring.args.app_id)}).toArray(function(err, appDetails) {
                if (!err && appDetails) {
                    common.returnMessage(params, 200, appDetails);
                } else {
                    common.returnMessage(params, 200, 'No Meta found');
                }
            });

    };
 function setCertsInPathAndRedis(filePath,certName, cert,app_id, modeKey ){
        const target_path =  path.join(filePath, certName)
        fs.writeFile(target_path, cert, (err) => {
            if (err) {
              logger.error('Error writing file:', err);
            } else {
                    // Using fs.readFile with a callback
                fs.readFile(target_path, 'utf-8', (err, fileData) => {
                    if (err) {
                      console.error('Error reading file:', err);
                    } else {
                      cacheApi.setKey(modeKey + app_id, btoa(fileData));
                    }
                  });
              logger.info('File written successfully=================================>>');
            }
          });
    }
    
    appsApi.updateIOSCertificates = function (params) {
        let argPropsCommon = {
            'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
            'ios_cert3': { 'required': true, 'type': 'String' },
            'isdev': { 'required': true, 'type': 'String' },
            'ios_bundleid': { 'required': true, 'type': 'String' },
            'proxyPORT': { 'required': true, 'type': 'String' },
            'proxyUrl': { 'required': true, 'type': 'String' },
            'ios_certPath': { 'required': true, 'type': 'String' },
            'ios_key_type': { 'required': true, 'type': 'String' },
            'ios_topic': { 'required': true, 'type': 'String' },
            'ios_keyid': { 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
            'ios_teamid': { 'required': true, 'type': 'String', 'regex': validate.getRegex('name') }
        };
    
        let argPropsP12 = {
            'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
            'ios_cert3': { 'required': true, 'type': 'String' },
            'isdev': { 'required': true, 'type': 'String' },
            'proxyPORT': { 'required': true, 'type': 'String' },
            'proxyUrl': { 'required': true, 'type': 'String' },
            'ios_bundleid': { 'required': true, 'type': 'String' },
            'ios_certPath': { 'required': true, 'type': 'String' },
            'ios_key_type': { 'required': true, 'type': 'String' },
            'ios_passphrase': { 'required': true, 'type': 'String' }
        };
    
        params.qstring.args = (typeof (params.qstring.args) === 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
    
        let p12Path = path.join(__dirname, "../../../frontend/express/public/appcertificates/" + params.qstring.args.app_id + '/' + params.qstring.args.ios_cert3);
        let path_ = path.join(__dirname, "../../../frontend/express/public/appcertificates/" + params.qstring.args.app_id);
    
        let argProps = {};
        if (params.qstring.args.ios_key_type == "p8") {
            argProps = argPropsCommon;
        } else {
            argProps = argPropsP12;
            pem.readPkcs12(fs.readFileSync(p12Path), { p12Password: params.qstring.args.ios_passphrase }, (err, result) => {
                if (err) {
                    logger.error('Error reading PKCS#12 file:', err.message);
                    return;
                }
                let iosCert = result.cert;
                let iosCertKey = result.key;
                const app_id = params.qstring.args.app_id;
    
                // Assuming default mode prod
                let pemFileName = 'prod_iosCert.pem';
                let pemKeyFileName = 'prod_iosCertKey.pem';
                let pemFileNameMode = 'iosCertificatepem_prod_';
                let pemKeyFileNameMode = 'iosCertificatepemkey_prod_';
    
                if (params.qstring.args.isdev == 'true') {
                    pemFileName = 'dev_iosCert.pem';
                    pemKeyFileName = 'dev_iosCertKey.pem';
                    pemFileNameMode = 'iosCertificatepem_dev_';
                    pemKeyFileNameMode = 'iosCertificatepemkey_dev_';
                }
    
                // Setting both certs
                setCertsInPathAndRedis(path_, pemFileName, iosCert, app_id, pemFileNameMode);
                setCertsInPathAndRedis(path_, pemKeyFileName, iosCertKey, app_id, pemKeyFileNameMode);
            });
        }
    
        if (!(common.validateUploadFile(params.qstring.args.ios_cert3)) || !(validate.fileValidation('iosCert').includes(common.getFileExtension(params.qstring.args.ios_cert3)))) {
            logger.error("Failed to upload Certs");
            common.returnMessage(params, 400, "Invalid file format. Only .p8 or .p12 files are allowed.");
            return false;
        }
    
        if (!(certificates = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 422, 'Validation error');
            return false;
        }
        let modifiedOn = common.getCurrentEpochTime();
    
        let updateData = {};
        if (params.qstring.args.isdev == 'true') {
            updateData = {
                'dev_ios_cert1': params.qstring.args.ios_cert1,
                'dev_ios_pass1': params.qstring.args.ios_pass1,
                'dev_ios_cert2': params.qstring.args.ios_cert2,
                'dev_ios_cert3': certificates.ios_cert3,
                'dev_ios_keyid': params.qstring.args.ios_keyid,
                'dev_ios_teamid': params.qstring.args.ios_teamid,
                'dev_ios_pass2': params.qstring.args.ios_pass2,
                'dev_ios_bundleid': params.qstring.args.ios_bundleid,
                'dev_ios_certPath': params.qstring.args.ios_certPath,
                'dev_ios_topic': params.qstring.args.ios_topic,
                'dev_ios_key_type': params.qstring.args.ios_key_type,
                'dev_ios_passphrase': params.qstring.args.ios_passphrase,
                'dev_ios_cert_pem': 'dev_iosCert.pem',
                'dev_ios_cert_key': 'dev_iosCertKey.pem',
                'push_notification.apns.dev': {
                    'dev_ios_cert1': params.qstring.args.ios_cert1,
                    'dev_ios_pass1': params.qstring.args.ios_pass1,
                    'dev_ios_cert2': params.qstring.args.ios_cert2,
                    'dev_ios_cert3': certificates.ios_cert3,
                    'dev_ios_certPath': certificates.ios_certPath,
                    'dev_ios_keyid': certificates.ios_keyid,
                    'dev_ios_teamid': certificates.ios_teamid,
                    'dev_ios_bundleid': certificates.ios_bundleid,
                    'dev_ios_pass2': params.qstring.args.ios_pass2,
                    'dev_ios_topic': certificates.ios_topic,
                    'dev_ios_key_type': params.qstring.args.ios_key_type,
                    'dev_ios_passphrase': params.qstring.args.ios_passphrase,
                    'dev_ios_cert_pem': 'dev_iosCert.pem',
                    'dev_ios_cert_key': 'dev_iosCertKey.pem',
                },
                'modifiedOn': modifiedOn
            };
            // Check if proxyUrl is not an empty string and then add it to updateData
            if (params.qstring.args.proxyUrl !== "" || params.qstring.args.proxyUrl == "") {
                updateData.proxyUrl = params.qstring.args.proxyUrl;
            }
            // Check if proxyUrl is not an empty string and then add it to updateData
            if (params.qstring.args.proxyPORT !== "" || params.qstring.args.proxyPORT == "") {
                updateData.proxyPORT = params.qstring.args.proxyPORT;
            }
    
            common.db.collection('apps').update({ '_id': common.db.ObjectID(params.qstring.args.app_id) }, { $set: updateData }, function (err, app) {
                if (err) {
                    logger.error('Error updating database:', err.message);
                    common.returnMessage(params, 500, 'Database update failed');
                    return;
                }
    
                // Clone and modify updateData for Redis
                let redisData = JSON.parse(JSON.stringify(updateData));
                redisData.key = redisData.dev_ios_cert3;
                redisData.team = redisData.dev_ios_teamid;  
                redisData.keyId = redisData.dev_ios_keyid;
                redisData.defaultTopic = redisData.dev_ios_topic;
                redisData.passphrase = redisData.dev_ios_passphrase;
                redisData.bundleid = redisData.ios_bundleid;
                redisData.production = false;

                if(redisData.dev_ios_key_type === 'p12'){
                    redisData.cert = redisData.dev_ios_cert_pem;
                    redisData.key = redisData.dev_ios_cert_key;
                    redisData.defaultTopic = redisData.dev_ios_bundleid;
                    delete redisData.bundleid
                    delete redisData.passphrase
                }


                
                delete redisData.dev_ios_cert3;
                delete redisData.dev_ios_teamid;
                delete redisData.dev_ios_keyid;
                delete redisData.dev_ios_topic;
                delete redisData.dev_ios_key_type;
                delete redisData.dev_ios_certPath;   
                delete redisData.dev_ios_bundleid;
                delete redisData.dev_ios_passphrase;
                delete redisData.dev_ios_cert_pem;
                delete redisData.dev_ios_cert_key;
                delete redisData['push_notification.apns.dev'];
                delete redisData.modifiedOn


                cacheApi.setKey(`apns_${params.qstring.args.app_id}`, JSON.stringify(redisData), (redisErr, redisRes) => {
                    if (redisErr) {
                        logger.error('Error storing data in Redis:', redisErr);
                    } else {
                        logger.info(`Data successfully stored in Redis==>>apns_${params.qstring.args.app_id}=============${redisRes}`);
                    }
                });
    
                common.returnMessage(params, 200, 'Success');
            });
        } else {
            updateData = {
                'ios_cert1': params.qstring.args.ios_cert1,
                'ios_pass1': params.qstring.args.ios_pass1,
                'ios_cert2': params.qstring.args.ios_cert2,
                'ios_cert3': certificates.ios_cert3,
                'ios_certPath': certificates.ios_certPath,
                'ios_keyid': certificates.ios_keyid,
                'ios_teamid': certificates.ios_teamid,
                'ios_bundleid': certificates.ios_bundleid,
                'ios_pass2': params.qstring.args.ios_pass2,
                'ios_topic': certificates.ios_topic,
                'ios_key_type': certificates.ios_key_type,
                'ios_passphrase': params.qstring.args.ios_passphrase,
                'ios_cert_pem': 'iosCert.pem',
                'ios_cert_key': 'iosCertKey.pem',
                'push_notification.apns.prod': {
                    'ios_cert1': params.qstring.args.ios_cert1,
                    'ios_pass1': params.qstring.args.ios_pass1,
                    'ios_cert2': params.qstring.args.ios_cert2,
                    'ios_cert3': certificates.ios_cert3,
                    'ios_certPath': certificates.ios_certPath,
                    'ios_keyid': certificates.ios_keyid,
                    'ios_teamid': certificates.ios_teamid,
                    'ios_bundleid': certificates.ios_bundleid,
                    'ios_pass2': params.qstring.args.ios_pass2,
                    'ios_topic': certificates.ios_topic,
                    'ios_key_type': certificates.ios_key_type,
                    'ios_passphrase': params.qstring.args.ios_passphrase,
                    'ios_cert_pem': 'iosCert.pem',
                    'ios_cert_key': 'iosCertKey.pem',
                },
                'modifiedOn': modifiedOn
            };
    
            // Check if proxyUrl is not an empty string and then add it to updateData
            if (params.qstring.args.proxyUrl !== ""  || params.qstring.args.proxyUrl == "") {
                updateData.proxyUrl = params.qstring.args.proxyUrl;
            }
            // Check if proxyUrl is not an empty string and then add it to updateData
            if (params.qstring.args.proxyPORT !== "" || params.qstring.args.proxyPORT == "") {
                updateData.proxyPORT = params.qstring.args.proxyPORT;
            }
            common.db.collection('apps').update({ '_id': common.db.ObjectID(params.qstring.args.app_id) }, { $set: updateData }, function (err, app) {
                if (err) {
                    logger.error('Error updating database:', err.message);
                    common.returnMessage(params, 500, 'Database update failed');
                    return;
                }
    
                // Clone and modify updateData for Redis
                let redisData = JSON.parse(JSON.stringify(updateData));
                redisData.key = redisData.ios_cert3;
                redisData.team = redisData.ios_teamid;  
                redisData.keyId = redisData.ios_keyid;
                redisData.bundleid = redisData.ios_bundleid;
                redisData.defaultTopic = redisData.ios_topic;
                redisData.passphrase = redisData.ios_passphrase;
                redisData.production = true;

                if(redisData.ios_key_type === 'p12'){
                    redisData.cert = redisData.ios_cert_pem;
                    redisData.key = redisData.ios_cert_key;
                    redisData.defaultTopic = redisData.ios_bundleid;
                    delete redisData.bundleid 
                    delete redisData.passphrase;
                    
                }
                
                
                delete redisData.ios_cert3;
                delete redisData.ios_teamid;
                delete redisData.ios_keyid;
                delete redisData.ios_topic;
                delete redisData.ios_key_type;
                delete redisData.ios_certPath;   
                delete redisData.ios_bundleid;
                delete redisData.ios_cert_pem;
                delete redisData.ios_cert_key;
                delete redisData.ios_passphrase;
                delete redisData.modifiedOn
                delete redisData['push_notification.apns.prod'];

                cacheApi.setKey(`apns_${params.qstring.args.app_id}`, JSON.stringify(redisData), (redisErr, redisRes) => {
                    if (redisErr) {
                        logger.error('Error storing data in Redis:', redisErr);
                    } else {
                        logger.info(`Data successfully stored in Redis==>>apns_${params.qstring.args.app_id}=============${redisRes}`);
                    }
                });
    
                common.returnMessage(params, 200, 'Success');
            });
        }
    
        return true;
    };
    

    
    appsApi.updateIOSCertificates_old = function (params) {

        let argProps = {
                'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                'ios_cert1': { 'required': true, 'type': 'String'},
                'ios_pass1': { 'required': true, 'type': 'String'},
                'ios_cert2': { 'required': true, 'type': 'String'},
                'ios_pass2': { 'required': true, 'type': 'String'},
                'isdev':     { 'required': true, 'type': 'String'}
            },
            appId = '';

        let paths = path.join(__dirname,"../../../frontend/express/public/appcertificates/" + params.qstring.args.app_id);

        if (!(appId = common.validateArgs(params.qstring.args, argProps).app_id)) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }

        let modifiedOn = common.getCurrentEpochTime();
        if(params.qstring.args.isdev=='true')
        {
            common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'dev_ios_cert1': params.qstring.args.ios_cert1,'dev_ios_pass1': params.qstring.args.ios_pass1,'dev_ios_cert2': params.qstring.args.ios_cert2,'dev_ios_pass2': params.qstring.args.ios_pass2, 'modifiedOn':modifiedOn}}, function(err, app) {
            appsApi.deleteFolderRecursive(paths); // delete certificates from local machine
            common.returnMessage(params, 200, 'Success');
          });
        }
        else
        {
          common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'ios_cert1': params.qstring.args.ios_cert1,'ios_pass1': params.qstring.args.ios_pass1,'ios_cert2': params.qstring.args.ios_cert2,'ios_pass2': params.qstring.args.ios_pass2, 'modifiedOn':modifiedOn}}, function(err, app) {
            appsApi.deleteFolderRecursive(paths); // delete certificates from local machine
            common.returnMessage(params, 200, 'Success');
          });
        }
        return true;
    };

    // delete certificates from app directory
    appsApi.deleteFolderRecursive = function(paths) {
        if( fs.existsSync(paths) ) {
            fs.readdirSync(paths).forEach(function(file,index){
                let curPath = paths + "/" + file;
                if(fs.lstatSync(curPath).isDirectory()) { // recurse
                    appsApi.deleteFolderRecursive(curPath);
                }else { // delete file
                    fs.unlinkSync(curPath);
                }
            });

            fs.rmdirSync(paths);
        }
    };

    appsApi.SendDevData = function (params) {

        let argProps = {
                 'app_id': { 'required': true, 'type': 'String', 'min-length': 24, 'max-length': 24 },
                 'app_key': { 'required': true, 'type': 'String'},
                 'devemailid': { 'required': true, 'type': 'String'},
                 'a_appname': { 'required': false, 'type': 'String'},
                 'i_appname': { 'required': false, 'type': 'String'},
                 'sendSteps': { 'required': true, 'type': 'String'}
            },
            devData = {};

             if (!(devData = common.validateArgs(params.qstring.args, argProps))) {
                common.returnMessage(params, 400, 'Not enough args');
                return false;
            }
           let modifiedOn = common.getCurrentEpochTime();
           common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.args.app_id)}, {$set: {'devemailid': params.qstring.args.devemailid, 'modifiedOn':modifiedOn}}, function(err, app) {
             mail.sendStepsToDeveloper(params.qstring.args.devemailid,params.qstring.args.app_id,params.qstring.args.app_key,params.qstring.api_key,params.qstring.args.sendSteps,devData.a_appname,devData.i_appname);
            common.returnMessage(params, 200, 'Success');
          });

        return true;
    };


    appsApi.deleteCallBackUrl = function(params){
         let obj = {
            callbackurl: '',
            httpMethod: '',
            installs: '',
            events: ''
        };

        common.db.collection('apps').update({'_id': common.db.ObjectID(params.qstring.app_id)}, {$unset: obj}, function(err, app) {
            //Clear this app from redis cache as its details are updated.
            cacheApi.deleteKey("webhook_installs_"+params.qstring.app_id,function(err,res){
                logger.info(`Redis key cleared. webhook_installs_${params.qstring.app_id}`);
            });

            cacheApi.deleteKey("webhook_events_"+params.qstring.app_id,function(err,res){
                logger.info(`Redis key cleared. webhook_events_${params.qstring.app_id}`);
            });

            common.returnOutput(params, app);
        });

    };

    appsApi.processfile= function(params)
    {
        let argProps = {
                 'filename': { 'required': true, 'type': 'String'},
                'appkey': { 'required': true, 'type': 'String'}
        };

       let parser = csvparse.parse({delimiter: '\''}, function(err, data){

            if(err){
                logger.error(`there is an error: ${err}`);
            }
            let result = {};
            if(data.length<1){
                logger.error('No Data');
            }

            let ctr=1;
            data.forEach(function(item) {
                //Skip the first item as that is just headers.
                if(ctr>1){

                    let tmpIUObj = {};
                    let array = item[0].split(',');
                    //Populate fields.
                    let Client = require('node-rest-client').Client;
                    let URL="";
                    for (let i = 0; i < array.length; i++) {

                            //Example POST method invocation
                            switch(i){
                               case 0:
                                  URL= URL+array[i];
                               break;
                               case 1:
                                  //URL= URL+"app_key="+array[i];
                                 URL= URL+"app_key="+params.qstring.args.appkey
                               break;
                               case 2:
                                  URL= URL+"&device_id="+array[i];
                               break;
                               case 3:
                                  URL= URL+"&timestamp="+array[i];
                               break;
                               case 4:
                                  URL= URL+"&sdk_version="+array[i];
                               break;
                               case 5:
                                  URL= URL+"&begin_session="+array[i];
                               break;
                               case 6:
                                  URL= URL+"&metrics=%7B%22_locale%22%3A%22"+array[i];
                               break;
                               case 7:
                                  URL= URL+"%22%2C%22_ref%22%3A%22"+array[i];
                               break;
                               case 8:
                                  URL= URL+"%22%2C%22_app_version%22%3A%22"+array[i];
                               break;
                               case 9:
                                  URL= URL+"%22%2C%22_device%22%3A%22"+array[i];
                               break;
                               case 10:
                                  URL= URL+"%22%2C%22_resolution%22%3A%22"+array[i];
                               break;
                               case 11:
                                  URL= URL+"%22%2C%22_os_version%22%3A%22"+array[i];
                               break;
                               case 12:
                                  URL= URL+"%22%2C%22_os%22%3A%22"+array[i];
                               break;
                               case 13:
                                  URL= URL+"%22%2C%22_carrier%22%3A%22"+array[i]+"%22%7D";
                               break;
                            }
                          }
                    let client = new Client();

                   let headers = {
                      agent:false
                    }
                    let httpArgs = {
                      "headers": headers
                    }
                   client.post(URL, httpArgs, function(data,response) {
                    logger.info(`data => ${data}`);
                    logger.error(`response => ${response}`);
                   });

                }
                ctr=ctr+1;


            });
            result.status="OK";
            result.message="File processed successfully.";
            common.returnOutput(params, result);

    //Function end
    });
    fs.createReadStream(params.qstring.args.filename).pipe(parser);

};

appsApi.processUninstallData = function(params){
    let argProps = {
         'filename': { 'required': true, 'type': 'String'}
    };

    let parser = csvparse.parse({delimiter: '\''}, function(err, data){
        let result = {};
        if(data.length<1){
            logger.info('No Data');
        }

        let ctr=1;
        data.forEach(function(item) {
            if(ctr>1){
                let tmpIUObj = {};
                let array = item[0].split(',');
                //Populate fields.
                let Client = require('node-rest-client').Client;
                let URL = array[0]+'args={"app_id":"'+array[1]+'","did":"'+array[2]+'","campid":"'+array[3]+'","response":[{"value":"'+array[4]+'","extra":"","qid":1}]'+',"uninstallTime":'+array[5]+'}&api_key='+array[6];

                let client = new Client();
                let headers = {
                  agent:false
                }
                let httpArgs = {
                  "headers": headers
                }
                client.post(URL, httpArgs, function(data,response) {

                });
            }
            ctr++;
        });
        result.status="OK";
        result.message="File processed successfully.";
        common.returnOutput(params, result);

    });
    fs.createReadStream(params.qstring.args.filename).pipe(parser);

};

appsApi.checkemaildomain=function(params)
{
     let argProps = {

                 'devemailid': { 'required': true, 'type': 'String'}

            };
    let email = params.qstring.args.devemailid.toLowerCase();
    let emailParts = email.split('@');
    let domainInitial = emailParts[1].split('.')[0];
    let domainindex =  semusiConfig.Domains.indexOf(domainInitial);

    let result={};
    if(domainindex!=-1)
    {
        result.status="OK";
        result.message=domainInitial +' not allowed domain';

    }
    else
    {
        result.status="OK";
        result.message='allowed domain';
    }


    common.returnOutput(params, result);
}

appsApi.getGlobalAppModules = function(params){
    common.db.collection('appmodules').find({deleted:false}).toArray(function(err,result){
        if(!err && result){
            common.returnOutput(params,result);
        }
        else{
            common.returnOutput(params,[]);
        }
    });
};

appsApi.getAppModules = function(params){
    common.db.collection('apps').find({'isAppDeleted':'false'},{"name":1,"modules":1}).toArray(function(err,result){
        if(!err && result){
            common.returnOutput(params,result);
        }
        else{
            common.returnOutput(params,[]);
        }
    });
};


appsApi.saveAppModules = function(params){
    if(params.qstring.args){
        let modules = [];
        params.qstring.args.forEach(function(item){
            if(item.enabled==true){
                modules.push(common.db.ObjectID(item.id));
            }
        });

        cacheApi.deleteKey('appModules_data_'+params.qstring.app_id,function(err,res){
            logger.info(`Redis key cleared. "+'appModules_data_${params.qstring.app_id}`);
        });

        let modifiedOn = common.getCurrentEpochTime();
        common.db.collection('apps').update({_id:common.db.ObjectID(params.qstring.app_id)},{$set:{modules:modules, 'modifiedOn':modifiedOn}},function(err,result){
            common.returnOutput(params,result);
        });
    }

};
appsApi.saveModule = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String' },
        'version':{ 'required': true, 'type': 'String' },
        'filename':{ 'required': true, 'type': 'String' },
        'arch':{ 'required': true, 'type': 'String' },
        'platform':{ 'required': true, 'type': 'String' },
        'pv':{ 'required': true, 'type': 'String' },
        'url':{ 'required': true, 'type': 'String' },
        'editable':{ 'required': true, 'type': 'Boolean' },
        'enableall':{ 'required': true, 'type': 'Boolean' }
    },module = {};
    if (!(module = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }

    cacheApi.deleteKey('appModules_data_'+params.qstring.app_id,function(err,res){
        logger.info(`Redis key cleared. "+'appModules_data_${params.qstring.app_id}`);
    });

    module.deleted=false;
    module['createdAt'] = common.getCurrentEpochTime();
    common.db.collection('appmodules').insert(module, function (err,result) {
        if(!err){
            common.returnMessage(params,200,"Module added");
            if(module.enableall){
                if(result && result.ops && result.ops.length>0){
                    enableModuleToApp(result.ops[0]._id);
                }
            }
        }
        else{
            common.returnMessage(params,500,"Error while adding module");
        }

    });
};

appsApi.updateModule = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String' },
        'version':{ 'required': true, 'type': 'String' },
        'filename':{ 'required': true, 'type': 'String' },
        'arch':{ 'required': true, 'type': 'String' },
        'platform':{ 'required': true, 'type': 'String' },
        'pv':{ 'required': true, 'type': 'String' },
        'url':{ 'required': true, 'type': 'String' },
        'editable':{ 'required': true, 'type': 'Boolean' },
        'enableall':{ 'required': true, 'type': 'Boolean' }
    },module = {};


    if (!(module = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }

    cacheApi.deleteKey('appModules_data_'+params.qstring.app_id,function(err,res){
        logger.info(`Redis key cleared. "+'appModules_data_${params.qstring.app_id}`);
    });

    module['modifiedOn'] = common.getCurrentEpochTime();
    common.db.collection('appmodules').update({"_id":common.db.ObjectID(params.qstring.id)},{$set:module}, function (err,result) {
        if(!err){
            common.returnMessage(params,200,"Module updated");
            if(module.enableall){
                if(result && result.result && result.result.n>0){
                    enableModuleToApp(common.db.ObjectID(params.qstring.id));
                }
            }
        }
        else{
            common.returnMessage(params,500,"Error while updating module");
        }

    });
};

appsApi.deleteModule = function(params){
    cacheApi.deleteKey('appmodules_data_'+params.qstring.app_id,function(err,res){
        logger.info(`Redis key cleared. "+'appmodules_data_${params.qstring.app_id}`);
    });

    let modifiedOn = common.getCurrentEpochTime();
    common.db.collection('appmodules').update({"_id":common.db.ObjectID(params.qstring.id)},{$set:{deleted:true, 'modifiedOn':modifiedOn}}, function (err,result) {
        if(!err){
            common.returnMessage(params,200,"Module deleted");
            disableModuleFromApps(params.qstring.id);
        }
        else{
            common.returnMessage(params,500,"Error while deleting module");
        }

    });
};



appsApi.getActiveModules = function(params){
    if(params.qstring.sdk_version && params.qstring.sdk_i_version && params.qstring.sdk_i_version > 212 ){
        cacheApi.getKey('appModules_data_'+ params.qstring.app_id,function(err,result){
            if(err || result==null){
                common.db.collection('apps').findOne({_id:common.db.ObjectID(params.qstring.app_id)},function(err,result){
                    let data=[];
                    if(!err && result){
                        common.db.collection('appmodules').find({_id:{$in:result.modules}}).toArray(function(err,modules){
                            if(modules){
                                modules.forEach(function(item){
                                    if(item.arch==params.qstring.arch && item.platform==params.qstring.platform){
                                        data.push(item);
                                    }
                                });
                            }
                            cacheApi.setKey('appModules_data_'+ params.qstring.app_id,JSON.stringify(data));
                            common.returnOutput(params,data);
                        });
                    }
                    else{
                        common.returnOutput(params,[]);
                    }
                });
            }
            else{
                let data = [];
                data = JSON.parse(result);
                common.returnOutput(params,data);
            }
        });
    }else{
        common.returnOutput(params,[]);
    }
}

appsApi.getOtherAppGroups = function(params){
    common.db.collection('otherapp_groups').find({appid:params.qstring.app_id,deleted:false}).toArray(function(err,result){
        if(result && result.length>0){
            common.returnOutput(params,result);
        }
        else{
            //Create the default app group for competitive Apps.
            let createdAt = common.getCurrentEpochTime();
            common.db.collection('otherapp_groups').insert({appid:params.qstring.app_id,name:"Competitive Apps",description:"This group will define a list of apps which are your competitive apps","type":"system",deleted:false,list:[], 'createdAt':createdAt},function(err,data){
                if(data && data.ops){
                    common.returnOutput(params,data.ops);
                }
                else{
                    common.returnOutput(params,[]);
                }
            });
        }
    });
}

appsApi.saveOtherAppGroup = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
        'description':{ 'required': false, 'type': 'String', 'regex': validate.getRegex('name') },
        'appid':{ 'required': true, 'type': 'String' }
    },group = {};
    if (!(group = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 422, 'Validation error');
        return false;
    }
    group.deleted=false;
    group.type = "user";
    group.list = [];
    group['createdAt'] = common.getCurrentEpochTime();

    common.db.collection('otherapp_groups').insert(group,function(err,data){
        if(data && data.ops){
            common.returnOutput(params,data.ops);
            cacheApi.deleteKey("AppGroups_"+group.appid,function(err,res){
                logger.info("App group cleared from cache.");
            });
            cacheApi.deleteKey("otherapp_"+params.qstring.app_id,function(err,res){
                logger.info("Otherapp group cleared from cache.");
            });
        }
        else{
            common.returnOutput(params,[]);
        }
    });
}

appsApi.updateOtherAppGroup = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
        'description':{ 'required': false, 'type': 'String', 'regex': validate.getRegex('name') },
        'appid':{ 'required': true, 'type': 'String' }
    },group = {};
    if (!(group = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 422, 'Validation error');
        return false;
    }

    group['modifiedOn'] = common.getCurrentEpochTime();
    common.db.collection('otherapp_groups').update({_id:common.db.ObjectID(params.qstring.id)},{$set:group},function(err,data){
        if(err){
            common.returnMessage(params,400,"Failed to update.");
        }
        else{
            common.returnMessage(params,200,"Success.");
            cacheApi.deleteKey("AppGroups_"+group.appid,function(err,res){
                logger.info("App group cleared from cache.");
            });
        }
    });
}

appsApi.addAppToGroup = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
        'package':{ 'required': false, 'type': 'String' },
        'urlscheme':{ 'required': false, 'type': 'String' }
    },app = {};
    if (!(app = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 422, 'Validation error');
        return false;
    }

    app.id = common.db.ObjectID().toString();
    let addedOn = common.getCurrentEpochTime();
    app.addedOn = moment(addedOn*1000).format('DD-MM-YYYY');
    common.db.collection('otherapp_groups').findOne({_id:common.db.ObjectID(params.qstring.id)},function(err,group){
        if(group){
            //Validate app to be added.
            for (let i = 0; i < group.list.length; i++) {

                if(group.list[i].name == app.name || group.list[i].package == app.package){
                    let error={
                        msg:"This app already exists in this group."
                    }
                    common.returnOutput(params,error);
                    return;
                    break;
                }
            }

            let modifiedOn = common.getCurrentEpochTime();
            common.db.collection('otherapp_groups').update({_id:common.db.ObjectID(params.qstring.id)},{$addToSet:{'list':app}, '$set':{'modifiedOn':modifiedOn}},function(err,result){
                if(err){
                    common.returnMessage(params,400,"Failed to update app to group.");
                }
                else{
                    common.returnMessage(params,200,"App was updated successfully.");
                    cacheApi.deleteKey("AppGroups_"+params.qstring.app_id,function(err,res){
                        logger.info("App group cleared from cache.");
                    });
                    cacheApi.deleteKey("otherapp_"+params.qstring.app_id,function(err,res){
                        logger.info("Otherapp group cleared from cache.");
                    });
                }
            });
        }
        else{
           common.returnMessage(params,400,"Failed to update app to group.");
        }
    });
}

appsApi.updateAppToGroup = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
        'package':{ 'required': true, 'type': 'String' },
        'id':{ 'required': true, 'type': 'String' },
        'urlscheme':{ 'required': false, 'type': 'String' }
    },app = {};
    if (!(app = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 422, 'Validation error');
        return false;
    }

    common.db.collection('otherapp_groups').findOne({_id:common.db.ObjectID(params.qstring.id)},function(err,group){
        if(group){
            //Validate app to be added.
            for (let i = 0; i < group.list.length; i++) {
                if(group.list[i].id!=app.id && (group.list[i].name == app.name || group.list[i].package==app.package || (group.list[i].urlscheme!="" &&  group.list[i].urlscheme==app.urlscheme))){
                    let error={
                        msg:"This app already exists in this group."
                    }
                    common.returnOutput(params,error);
                    return;
                    break;
                }
                else if(group.list[i].id==app.id){
                   group.list[i].name=app.name;
                   group.list[i].package=app.package;
                   group.list[i].urlscheme=app.urlscheme;
                }
            };

            let modifiedOn = common.getCurrentEpochTime();
            common.db.collection('otherapp_groups').update({_id:common.db.ObjectID(params.qstring.id)},{$set:{'list':group.list, 'modifiedOn':modifiedOn}},function(err,result){
                if(err){
                    common.returnMessage(params,400,"Failed to add app to group.");
                }
                else{
                    common.returnMessage(params,200,"App was added successfully.");
                    cacheApi.deleteKey("AppGroups_"+params.qstring.app_id,function(err,res){
                        logger.info("App group cleared from cache.");
                    });
                    cacheApi.deleteKey("otherapp_"+params.qstring.app_id,function(err,res){
                        logger.info("Otherapp group cleared from cache.");
                    });
                }
            });
        }
        else{
           common.returnMessage(params,400,"Failed to add app to group.");
        }
    });
}

appsApi.deleteAppFromGroup = function(params){

    common.db.collection('otherapp_groups').findOne({_id:common.db.ObjectID(params.qstring.id)},function(err,group){
        if(group){
            //Validate app to be added.
            for (let i = 0; i < group.list.length; i++) {
                if(group.list[i].id==params.qstring._id){
                   group.list.splice(i,1);
                }
            };

            let modifiedOn = common.getCurrentEpochTime();
            common.db.collection('otherapp_groups').update({_id:common.db.ObjectID(params.qstring.id)},{$set:{'list':group.list, 'modifiedOn':modifiedOn}},function(err,result){
                if(err){
                    common.returnMessage(params,400,"Failed to deleted app from group.");
                }
                else{
                    common.returnMessage(params,200,"App was deleted successfully.");
                    cacheApi.deleteKey("AppGroups_"+params.qstring.app_id,function(err,res){
                        logger.info("App group cleared from cache.");
                    });
                    cacheApi.deleteKey("otherapp_"+params.qstring.app_id,function(err,res){
                        logger.info("Otherapp group cleared from cache.");
                    });
                }
            });
        }
        else{
           common.returnMessage(params,400,"Failed to delete app from group.");
        }
    });
}

appsApi.deleteOtherAppGroup = function(params){
    let modifiedOn = common.getCurrentEpochTime();
    common.db.collection('otherapp_groups').update({_id:common.db.ObjectID(params.qstring.id)},{$set:{deleted:true,'modifiedOn':modifiedOn}},function(err,data){
        if(err){
            common.returnMessage(params,400,"Failed to update.");
        }
        else{
            common.returnMessage(params,200,"Success.");
            cacheApi.deleteKey("AppGroups_"+params.qstring.app_id,function(err,res){
                logger.info("App group cleared from cache.");
            });
            cacheApi.deleteKey("otherapp_"+params.qstring.app_id,function(err,res){
                logger.info("Otherapp group cleared from cache.");
            });
        }
    });
}

///Competing Apps and Main App get data of average session starts///
///Competing Apps and Main App get data of average session starts///
appsApi.getAppsAverageSession  = function(params){
    let argProps = {
        'startDate_C':{'required':true,'type':'Number'},
        'endDate_C':{'required':true,'type':'Number'},
        'startDate_P':{'required':true,'type':'Number'},
        'endDate_P':{'required':true,'type':'Number'},

    },cAsession = {};
    if (!(cAsession = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }

    common.db.collection('otherapp_groups').findOne({appid:params.qstring.app_id},function(err,cData){
        if(!err){
            if(cData.list.length > 0){
                let array = [];
                let query = {};
                let current = [];
                let previous = [];
                cacheApi.getKey("app_id"+params.qstring.app_id,function(err,cacheResult){

                    cacheResult = JSON.parse(cacheResult)

                    if(cacheResult != undefined){
                        cData.list.push({name:cacheResult.name,id:params.qstring.app_id});
                        cData.list.forEach(function(item){
                            common.fillCompetingAppsQueryObject(query, item.id, item.name, array, cAsession.startDate_C, cAsession.endDate_C, 'android', params.qstring.app_id);
                            common.fillCompetingAppsQueryObject(query, item.id, item.name, array, cAsession.startDate_C, cAsession.endDate_C, 'ios', params.qstring.app_id);
                        });
                        common.db.collection('timely_users_session').find({_id:{$in:array}}, query).toArray(function(error, result){
                            if(!error  && result.length > 0){
                                if(result && result.length > 0){
                                    let data = common.prepareSessionData(result,true,current, previous)
                                    let result = {data:data}
                                    common.returnOutput(params,result);
                                }
                            }
                            else{
                                common.returnOutput(params,error);
                            }
                        })
                    }
                    else{
                        common.returnOutput(params,cacheResult);
                    }

                })
            }else{
                let array = [];
                let query = {};
                let current = [];
                let previous = [];
                cacheApi.getKey("app_id"+params.qstring.app_id,function(err,cacheResult){

                 cacheResult = JSON.parse(cacheResult)
                    if(cacheResult != undefined){
                        cData.list.push({name:cacheResult.name,id:params.qstring.app_id});
                        cData.list.forEach(function(item){
                            common.fillCompetingAppsQueryObject(query, item.id, item.name, array, cAsession.startDate_C, cAsession.endDate_C, 'android', params.qstring.app_id);
                            common.fillCompetingAppsQueryObject(query, item.id, item.name, array, cAsession.startDate_C, cAsession.endDate_C, 'ios', params.qstring.app_id);
                        });
                        common.db.collection('timely_users_session').find({_id:{$in:array}}, query).toArray(function(error, result){
                            if(!error){
                                if(result && result.length > 0){
                                    let data = common.prepareSessionData(result,true,current, previous)
                                    let result = {data:data}
                                    common.returnOutput(params,result);
                                }
                            }
                            else{
                                common.returnOutput(params,error);                                
                            }
                        })
                    }
                    else{
                        common.returnOutput(params,cacheResult);
                    }

                })
            }

        }else{
            common.returnOutput(params,err);                                
        }
    })


    /*let appsComps = [
        {
            "name" : "LimeRoad",
            "package" : "com.shopping.limeroad",
            "id" : "5710b0703ea9dc8806a261d0"
        },
        {
            "name" : "Voonik",
            "package" : "com.voonik.android",
            "id" : "57171cccc9023bab06619218",
        },
        {
            "name" : "craftsvilla",
            "package" : "com.craftsvilla.app",
            "id" : "571f3fea5782d9376b196e78"
        }
    ]

    appsComps.push({name:"session testing", id:"57c918de322e0bbf25a0bd0f"});

    let array = [];
    let query = {};
    let current = [];
    let previous = []

    appsComps.forEach(function(item){
        common.fillCompetingAppsQueryObject(query, item.id, item.name, array, cAsession.startDate_C, cAsession.endDate_C, 'android', params.qstring.app_id);
        common.fillCompetingAppsQueryObject(query, item.id, item.name, array, cAsession.startDate_C, cAsession.endDate_C, 'ios', params.qstring.app_id);
    });*/
    /*common.db.collection('timely_users_session').find({_id:{$in:array}}, query).toArray(function(error, result){
        if(!error){
            if(result && result.length > 0){
                let data = common.prepareSessionData(result,true,current, previous)
                let result = {data:data}
                common.returnOutput(params,result);
            }
        }
    })*/
}
///Competing Apps and Main App get data of average session end///

function fillCompetingAppsQueryObject(object, cId, cName, array ,epochStartDate_C, epochEndDate_C, platform, appId){
        let number = 1;
        let startDate_C = epochStartDate_C*1000;
        let endDate_C = epochEndDate_C*1000;
        endDate_C = moment(endDate_C).add(number,'days').format('YYYY-MM-DD');
        //let $query = {}
        while(moment(startDate_C).valueOf() < moment(endDate_C).valueOf()){
            let year = moment(startDate_C).format('YYYY');
            let month = parseInt(moment(startDate_C).format('MM'));
            let day = parseInt(moment(startDate_C).format('D'));
            let qDate = year+"_"+month+"_"+day;
            object[platform+'.'+cName+'.'+qDate] = 1;
            if(array.indexOf(common.timelyEventId(cId, startDate_C)) == -1){
                array.push(common.timelyEventId(cId, startDate_C));
            }
            startDate_C = moment(startDate_C).add(number,'days').format('YYYY-MM-DD');
        }
}




///Competing Apps and Main App get data of average session end///
appsApi.getChildApps = function(params){
    common.db.collection('childapps').find({appid:params.qstring.app_id,deleted:false}).toArray(function(err,data){
        common.returnOutput(params,data);
    });
}

appsApi.saveChildApp = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String' },
        'childappid':{ 'required': true, 'type': 'String' },
        'appid':{ 'required': true, 'type': 'String' }
    },childApp = {};
    if (!(childApp = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }
    childApp.deleted=false;
    childApp.type = "user";
    common.db.collection('childapps').find({appid:childApp.appid,deleted:false,$or:[{name:childApp.name},{childappid:childApp.childappid}]}).toArray(function(err,result){

        if(result && result.length>0){
            common.returnOutput(params,{msg:"This name/id already exists."});
        }
        else{
            childApp['createdAt'] = getCurrentEpochTime();
            common.db.collection('childapps').insert(childApp,function(err,data){
                if(data && data.ops){
                    common.returnOutput(params,data.ops);
                }
                else{
                    common.returnOutput(params,[]);
                }
            });
        }
    });

}

appsApi.updateChildApp = function(params){
    let argProps = {
        'name':{ 'required': true, 'type': 'String' },
        'childappid':{ 'required': true, 'type': 'String' },
        'appid':{ 'required': true, 'type': 'String' }
    },childApp = {};
    if (!(childApp = common.validateArgs(params.qstring.args, argProps))) {
        common.returnMessage(params, 400, 'Not enough args');
        return false;
    }

    common.db.collection('childapps').find({appid:childApp.appid,deleted:false,_id:{$ne:common.db.ObjectID(params.qstring.id)},$or:[{name:childApp.name},{childappid:childApp.childappid}]}).toArray(function(err,result){
        if(result && result.length>0){
            common.returnOutput(params,{msg:"This name/id already exists."});
        }
        else{
            childApp['updatedAt'] = common.getCurrentEpochTime();
            common.db.collection('childapps').update({_id:common.db.ObjectID(params.qstring.id)},{$set:childApp},function(err,data){
                if(!err){
                    common.returnMessage(params,200,"Updated.");
                }
                else{
                    common.returnMessage(params,400,"Failed to update.");
                }
            });
        }
    });
}

appsApi.deleteChildApp = function(params){
    let updatedAt = common.getCurrentEpochTime();
    common.db.collection('childapps').update({_id:common.db.ObjectID(params.qstring.id)},{$set:{deleted:true, 'updatedAt':updatedAt}},function(err,data){
        if(err){
            common.returnMessage(params,400,"Failed to update.");
        }
        else{
            common.returnMessage(params,200,"Success.");
        }
    });
}

appsApi.updateLinkingField = function(params){
    let modifiedOn = getCurrentEpochTime();
    common.db.collection('apps').update({_id:common.db.ObjectID(params.qstring.app_id)},{$set:{linkingField:params.qstring.linkingField, 'modifiedOn':modifiedOn}},function(err,data){
        if(err){
            common.returnMessage(params,400,"Failed to update.");
        }
        else{
            common.returnMessage(params,200,"Success.");
        }
    });
}

appsApi.getAppDetails = function(params){
    common.db.collection('apps').findOne({_id:common.db.ObjectID(params.qstring.app_id)},function(err,data){
        common.returnOutput(params,data);
    });
}

appsApi.getCompetingApps = function(params){
    common.db.collection('otherapp_groups').findOne({appid:params.qstring.app_id, deleted : false},function(err,data){
        logger.info(`data " ${JSON.stringify(data)}`);
        if(err){
            common.returnOutput(params,[]);
        }
        else{
            if(data && data.list){
                common.returnOutput(params,data.list);
            }
            else{
                common.returnOutput(params,[]);
            }
        }
    });
}


appsApi.getCompetingAppsAppWiseOld = function(params){
    /*
    let query = [
                    {"$match":{"active":true}},
                    {$project: { _id: 0, "competingapps.apps": 1 } },
                    {$unwind:"$competingapps.apps"},
                    {"$match":{"competingapps.apps.id":{"$in":params.qstring.apps_arr}}},
                    {$group: { _id: {package:"$competingapps.apps.name"}, total: { $sum: 1 }}},
                    {$project: { _id: 0,competingapps: "$_id.package", total: 1 }},
                ];
    common.db.collection('app_users'+params.qstring.app_id).aggregate(query,function(err,data){
        if(err){
            common.returnOutput(params,[]);
        }
        else{
            if(data && data.length > 0){
                common.db.collection('app_users'+params.qstring.app_id).count({active:true},function(err,totalUsers){
                    if(err){
                        common.returnOutput(params,[]);
                    }else{
                        if(totalUsers){
                            common.db.collection('apps').findOne({_id:common.db.ObjectID(params.qstring.app_id)},function(err,appsData){
                                if(err){
                                    common.returnOutput(params,[]);
                                }else{
                                    if(appsData){
                                        if(data.length > 0){
                                            data.unshift({competingapps:appsData.name,total:totalUsers})
                                            let compAppsInfo = {data:data};
                                            common.returnOutput(params,compAppsInfo);
                                        }
                                    }
                                }
                            })
                        }
                    }
                })
            }
            else{
                common.returnOutput(params,[]);
            }
        }
    });
    */

    cacheApi.deleteKey("otherapp_"+params.qstring.app_id,function(err,res){
        logger.info("Otherapp group cleared from cache.");
    });

    let competingApps = [];

    common.db.collection('app_users'+params.qstring.app_id).count({active:true},function(err,appData){
        if(err){
            common.returnOutput(params,{data:competingApps});
        }
        else{
           competingApps.push({
                competingapps:(params.qstring.name)? params.qstring.name : 'Self' ,
                total:appData
            });

            // get competing apps name and count
            common.db.collection('competing_aggregates').findOne({_id:common.db.ObjectID(params.qstring.app_id)},function(err,data){
              if(err){
                    common.returnOutput(params,{data:competingApps});
                }
                else{
                    if(data && data.android && data.android.comp){
                        Object.keys(data.android.comp).forEach(function(key){
                            competingApps.push({
                                competingapps:key,
                                total:data.android.comp[key]
                            })
                        })
                    }

                    common.returnOutput(params,{data:competingApps});
                }
            });
        }
    });

}

     /* insert geo-fencing data */
    appsApi.saveGeoFencing = function (params) {
        let argProps = {
                'name': {
                    'required': true,
                    'type': 'String'
                },
                'desc': {
                    'required': true,
                    'type': 'String'
                }
            },
            geo = {};
        if (!(geo = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 400, 'Not enough args');
            return false;
        }
        geo = params.qstring.args;
        let app_id = params.qstring.app_id;
        geo.deleted = false;
        common.db.collection('geo_fence_'+app_id).find({
            deleted: false,
            $or: [{
                name: geo.name
            }]
        }).toArray(function (err, result) {

            if (result && result.length > 0) {
                common.returnOutput(params, {
                    msg: "This name already exists."
                });
            } else {
                geo['createdAt'] = common.getCurrentEpochTime();
                common.db.collection('geo_fence_'+app_id).insert(geo, function (err, data) {
                    logger.error(`err => ${err}`);
                    if (data) {
                        common.returnOutput(params, data);
                    } else {
                        common.returnOutput(params, []);
                    }
                });
            }
        });

    }


    appsApi.getGeoFencing = function (params) {
        let app_id = params.qstring.app_id;
        common.db.collection('geo_fence_'+app_id).find({
            deleted: false},{
            name: 1,
            details:1
        }).toArray(function (err, result) {
            if (result && result.length > 0) {
                logger.info(`result => ${result}`);
                common.returnOutput(params, result);
            } else {
                common.returnOutput(params, []);
            }
        });
    }

    appsApi.delGeoFencing = function (params) {
        let app_id = params.qstring.app_id;
        let geo_id = params.qstring.geo_id;
        if(geo_id != ''){
            common.db.collection('geo_fence_'+app_id).update({"_id":geo_id},{$set: {"deleted":"true"}}, function(err, result) {
                if (!err) {
                    common.returnOutput(params, result);
                } else {
                    common.returnOutput(params, []);
                }
            });
        }else{
            common.returnOutput(params, [{'message':"geo is null"}]);
        }
        
    }


    appsApi.getCompetingAppsAppWise = function(params) {
        if (common.config.intetestAppsList.indexOf(params.qstring.app_id) >= 0) {
            let competingApps = [];
            //total Variable
            let grandTotal = 0;
            common.db.collection('competing_aggregates').findOne({
                _id: common.db.ObjectID(params.qstring.app_id)
            }, function(error, result) {
                if (error || result == null) {
                    logger.error(`error => ${JSON.stringify(error)}`);
                    common.returnOutput(params, competingApps);
                } else {

                    if (result.android && result.android.comp) {
                        Reflect.ownKeys(result.android.comp).forEach(comp => {
                            grandTotal += Number(result.android.comp[comp])
                            logger.info(`${comp}, ' =>', ${result.android.comp[comp]}`)
                            competingApps.push({
                                competingapps: comp,
                                total: result.android.comp[comp]
                            });
                        })
                    }
                    let competing = {};

                    competingApps.map((v) => {
                        dataPer = v.total * 100 / grandTotal;
                        v.per = dataPer.toFixed(0);
                        return v; //Make integer
                    })
                    competing.data = competingApps
                    // competing.data.reduce((competingapps,total))
                    common.returnOutput(params, competing);
                }
            });
        } else {
            common.db.collection('app_users' + params.qstring.app_id).aggregate([{
                        $match: {
                            "active": true
                        }
                    },
                    {
                        $project: {
                            "capps": "$competingapps.apps",
                            "did": 1
                        }
                    },
                    {
                        $unwind: "$capps"
                    },
                    {
                        $match: {
                            "capps.state": "I"
                        }
                    },
                    {
                        $group: {
                            "_id": "$did",
                            "capps": {
                                "$addToSet": "$capps"
                            }
                        }
                    },
                    {
                        $project: {
                            did: 1,
                            "capps": {
                                "$size": {
                                    "$ifNull": ["$capps", []]
                                }
                            }
                        }
                    },
                    {
                        $group: {
                            _id: {
                                "comp": "$capps"
                            },
                            total: {
                                $sum: 1
                            }
                        }
                    },
                    {
                        $project: {
                            comp: "$_id.comp",
                            total: 1
                        }
                    }
                ], {
                    maxTimeMS: semusiConfig.mongodb.maxTimeMS
                },
                function(err, result) {
                    if (result) {
                        common.returnOutput(params, result);
                    } else {
                        common.returnOutput(params, []);
                    }
                });
        }
    }
    appsApi.saveEmailBudget = function (params) {

        var argProps = {
            'cost_per_email': {
                'required': true,
                'type': 'String',
            },
            'total_bdgt': {
                'required': true,
                'type': 'String'
            },
            'app_id': {
                'required': true,
                'type': 'String'
            },
            'email_currency': {
                'required': true,
                'type': 'String'
            }
        };
        let settings = {};
        
        params.qstring.args = (typeof (params.qstring.args) == 'string') ? JSON.parse(params.qstring.args) : params.qstring.args;
        if (!(settings = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 422, 'Validation error');
            return false;
        }

        const email_Config = {
            cost_per_email: settings.cost_per_email,
            total_bdgt: settings.total_bdgt,
            email_currency: settings.email_currency,
        }


        common.db.collection('apps').update({ "_id": common.db.ObjectID(params.qstring.args.app_id) }, { $set: { 'email_Config': email_Config } }, (err, data) => {

            if (err || !data) {
                common.returnMessage(params, 500, 'Something went wrong');
            }
            else {
                common.returnOutput(params, data);
            }
        })
    }
     //  getfeatures
     appsApi.getFeaturesSettings = function (params) {
        common.db.collection('apps').find({'_id': common.db.ObjectID(params.qstring.args.app_id),},{'features':1}).toArray(async function(err, app){
            if(err || app == null){
                common.returnOutput(params, []);
            }else{
                if (app[0].features.allowList) {
                    const update = { $unset: { allowList: "" } };
        
                    common.db.collection('apps').updateOne(
                        { '_id': common.db.ObjectID(params.qstring.args.app_id) },
                        update,
                        function (err, result) {
                            if (err) {
                                logger.info('Error during unsetting allowlist:', err);
                            } else {
                                logger.info('Allowlist unset successfully');
                                const redisKey = "allowlist_" + params.qstring.args.app_id;
                                cacheApi.deleteKey(redisKey,function(err,res){
                                    if (err) {
                                        logger.info("Error deleting allowlist key from Redis:", err);
                                    } else {
                                        logger.info("Allowlist Key deleted from Redis :", res);
                                    }
                                });
                            }
                        }
                    );
                }
                    // get siteSettings from the common function
                app[0].siteSettings = await retrieveSiteSettings();
               common.returnOutput(params, app);
            }
        })
    }

/* Email send after custom segment csv file upload */
appsApi.sendEmailCustomSegment = function({ app_id, email, fileUrl ,audid}) {
    //validate Inputs
    if (!app_id || !email || !fileUrl) {
        throw new Error('Missing or incomplete data');
    }

    // Constants for email recipients
    const DEFAULT_BCC = [];
    const SPECIAL_BCC = ['backend@appice.io'];


    // Common message content
    const messageContent = `Hello Team,<br/><br/> Audience ID : ${audid}<br/>The CSV file upload is IN progress.<br/>File Name: ${fileUrl}. We will notify you once the file is uploaded.`;

    // Construct message
    const message = {
        to: email,
        bcc: app_id === '5fa41afe6fc1e678a8344311' ? SPECIAL_BCC : DEFAULT_BCC,
        subject: `Csv upload has been started for app id (${app_id})`,
        html: messageContent
    };

    // Log and send mail
    logger.info(`Sending Mail As Now => ${JSON.stringify(message)}`);
    mail.sendMail(message);
}


function enableModuleToApp(id){
    common.db.collection('apps').find({'isAppDeleted':'false'}).toArray(function(err,apps){
        apps.forEach(function(app){
            let modifiedOn = getCurrentEpochTime();
            common.db.collection('apps').update({_id:app._id},{$addToSet:{'modules':id},'$set':{'modifiedOn':modifiedOn}},function(err,result){});
        });
    });
}

function disableModuleFromApps(id){
    common.db.collection('apps').find({'isAppDeleted':'false'}).toArray(function(err,apps){
        apps.forEach(function(app){
            if(app){
                let index = app.modules.indexOf(common.db.ObjectID(id));
                app.modules.splice(index,1);
                let modifiedOn = getCurrentEpochTime();
                common.db.collection('apps').update({_id:app._id},{$set:{'modules':app.modules, 'modifiedOn':modifiedOn}},function(err,result){});
            }
        });
    });
}

function registerDevice(devicetoken)
{
    ua = new ua("AB7xUl6dQkK0RpHUze-8cA", "YN0yQJYEQJSzLyFWSGmWfg", "xX2t3KSGSCClNEC2vZMYvg");
    ua.registerDevice(devicetoken, function(error)
        {
            return true;
        });
    return true;
}

    function deleteAppData(appId) {
        common.db.collection('sessions').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('users').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('carriers').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('locations').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('cities').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('app_users' + appId).drop();
        common.db.collection('devices').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('device_details').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('app_versions').remove({'_id': common.db.ObjectID(appId)});
        common.db.collection('rules'+appId).drop();

        common.db.collection('events').findOne({'_id': common.db.ObjectID(appId)}, function(err, events) {
            if (!err && events && events.list) {
                for (let i = 0; i < events.list.length; i++) {
                    common.db.collection(events.list[i] + appId).drop();
                }

                common.db.collection('events').remove({'_id': common.db.ObjectID(appId)});
            }
        });
    }


    function packApps(apps) {
        let appsObj = {};

        for (let i = 0; i < apps.length ;i++) {
            appsObj[apps[i]._id] = {
                '_id': apps[i]._id,
                'category' : apps[i].category,
                'country' : apps[i].country,
                'key' : apps[i].key,
                'name' : apps[i].name,
                'timezone' : apps[i].timezone,
                'gcmsenderid' : apps[i].gcmsenderid,
                'gcmapikey' : apps[i].gcmapikey,
            };
        }

        return appsObj;
    }

    function processAppProps(app) {
        let semusiConfig = require('./../../config.js');

        if (!app.country || !isValidCountry(app.country)) {
            //app.country = semusiConfig.apps.country;
        }

        if (!app.timezone || !isValidTimezone(app.timezone)) {
            //app.timezone = semusiConfig.apps.timezone;
        }

        if (!app.category || !isValidCategory(app.category)) {
            //app.category = semusiConfig.apps.category;
        }
    }

    function isValidTimezone(timezone) {
        let timezones = ["Africa/Abidjan","Africa/Accra","Africa/Addis_Ababa","Africa/Algiers","Africa/Asmera","Africa/Bamako","Africa/Bangui","Africa/Banjul","Africa/Bissau","Africa/Blantyre","Africa/Brazzaville","Africa/Bujumbura","Africa/Cairo","Africa/Casablanca","Africa/Ceuta","Africa/Conakry","Africa/Dakar","Africa/Dar_es_Salaam","Africa/Djibouti","Africa/Douala","Africa/El_Aaiun","Africa/Freetown","Africa/Gaborone","Africa/Harare","Africa/Johannesburg","Africa/Kampala","Africa/Khartoum","Africa/Kigali","Africa/Kinshasa","Africa/Lagos","Africa/Libreville","Africa/Lome","Africa/Luanda","Africa/Lubumbashi","Africa/Lusaka","Africa/Malabo","Africa/Maputo","Africa/Maseru","Africa/Mbabane","Africa/Mogadishu","Africa/Monrovia","Africa/Nairobi","Africa/Ndjamena","Africa/Niamey","Africa/Nouakchott","Africa/Ouagadougou","Africa/Porto-Novo","Africa/Sao_Tome","Africa/Tripoli","Africa/Tunis","Africa/Windhoek","America/Anchorage","America/Anguilla","America/Antigua","America/Araguaina","America/Aruba","America/Asuncion","America/Bahia","America/Barbados","America/Belem","America/Belize","America/Boa_Vista","America/Bogota","America/Buenos_Aires","America/Campo_Grande","America/Caracas","America/Cayenne","America/Cayman","America/Chicago","America/Costa_Rica","America/Cuiaba","America/Curacao","America/Danmarkshavn","America/Dawson_Creek","America/Denver","America/Dominica","America/Edmonton","America/El_Salvador","America/Fortaleza","America/Godthab","America/Grand_Turk","America/Grenada","America/Guadeloupe","America/Guatemala","America/Guayaquil","America/Guyana","America/Halifax","America/Havana","America/Hermosillo","America/Iqaluit","America/Jamaica","America/La_Paz","America/Lima","America/Los_Angeles","America/Maceio","America/Managua","America/Manaus","America/Martinique","America/Mazatlan","America/Mexico_City","America/Miquelon","America/Montevideo","America/Montreal","America/Montserrat","America/Nassau","America/New_York","America/Noronha","America/Panama","America/Paramaribo","America/Phoenix","America/Port-au-Prince","America/Port_of_Spain","America/Porto_Velho","America/Puerto_Rico","America/Recife","America/Regina","America/Rio_Branco","America/Santiago","America/Santo_Domingo","America/Sao_Paulo","America/Scoresbysund","America/St_Johns","America/St_Kitts","America/St_Lucia","America/St_Thomas","America/St_Vincent","America/Tegucigalpa","America/Thule","America/Tijuana","America/Toronto","America/Tortola","America/Vancouver","America/Whitehorse","America/Winnipeg","America/Yellowknife","Antarctica/Casey","Antarctica/Davis","Antarctica/DumontDUrville","Antarctica/Mawson","Antarctica/Palmer","Antarctica/Rothera","Antarctica/Syowa","Antarctica/Vostok","Arctic/Longyearbyen","Asia/Aden","Asia/Almaty","Asia/Amman","Asia/Aqtau","Asia/Aqtobe","Asia/Ashgabat","Asia/Baghdad","Asia/Bahrain","Asia/Baku","Asia/Bangkok","Asia/Beirut","Asia/Bishkek","Asia/Brunei","Asia/Calcutta","Asia/Choibalsan","Asia/Colombo","Asia/Damascus","Asia/Dhaka","Asia/Dili","Asia/Dubai","Asia/Dushanbe","Asia/Gaza","Asia/Hong_Kong","Asia/Hovd","Asia/Irkutsk","Asia/Jakarta","Asia/Jayapura","Asia/Jerusalem","Asia/Kabul","Asia/Kamchatka","Asia/Karachi","Asia/Katmandu","Asia/Krasnoyarsk","Asia/Kuala_Lumpur","Asia/Kuwait","Asia/Macau","Asia/Magadan","Asia/Makassar","Asia/Manila","Asia/Muscat","Asia/Nicosia","Asia/Omsk","Asia/Phnom_Penh","Asia/Pyongyang","Asia/Qatar","Asia/Rangoon","Asia/Riyadh","Asia/Saigon","Asia/Seoul","Asia/Shanghai","Asia/Singapore","Asia/Taipei","Asia/Tashkent","Asia/Tbilisi","Asia/Tehran","Asia/Thimphu","Asia/Tokyo","Asia/Ulaanbaatar","Asia/Vientiane","Asia/Vladivostok","Asia/Yakutsk","Asia/Yekaterinburg","Asia/Yerevan","Atlantic/Azores","Atlantic/Bermuda","Atlantic/Canary","Atlantic/Cape_Verde","Atlantic/Faeroe","Atlantic/Reykjavik","Atlantic/South_Georgia","Atlantic/St_Helena","Atlantic/Stanley","Australia/Adelaide","Australia/Brisbane","Australia/Darwin","Australia/Hobart","Australia/Perth","Australia/Sydney","Etc/GMT","Europe/Amsterdam","Europe/Andorra","Europe/Athens","Europe/Belgrade","Europe/Berlin","Europe/Bratislava","Europe/Brussels","Europe/Bucharest","Europe/Budapest","Europe/Chisinau","Europe/Copenhagen","Europe/Dublin","Europe/Gibraltar","Europe/Helsinki","Europe/Istanbul","Europe/Kaliningrad","Europe/Kiev","Europe/Lisbon","Europe/Ljubljana","Europe/London","Europe/Luxembourg","Europe/Madrid","Europe/Malta","Europe/Minsk","Europe/Monaco","Europe/Moscow","Europe/Oslo","Europe/Paris","Europe/Prague","Europe/Riga","Europe/Rome","Europe/Samara","Europe/San_Marino","Europe/Sarajevo","Europe/Skopje","Europe/Sofia","Europe/Stockholm","Europe/Tallinn","Europe/Tirane","Europe/Vaduz","Europe/Vatican","Europe/Vienna","Europe/Vilnius","Europe/Warsaw","Europe/Zagreb","Europe/Zurich","Indian/Antananarivo","Indian/Chagos","Indian/Christmas","Indian/Cocos","Indian/Comoro","Indian/Kerguelen","Indian/Mahe","Indian/Maldives","Indian/Mauritius","Indian/Mayotte","Indian/Reunion","Pacific/Apia","Pacific/Auckland","Pacific/Easter","Pacific/Efate","Pacific/Enderbury","Pacific/Fakaofo","Pacific/Fiji","Pacific/Funafuti","Pacific/Galapagos","Pacific/Gambier","Pacific/Guadalcanal","Pacific/Guam","Pacific/Honolulu","Pacific/Johnston","Pacific/Kiritimati","Pacific/Kosrae","Pacific/Kwajalein","Pacific/Majuro","Pacific/Marquesas","Pacific/Midway","Pacific/Nauru","Pacific/Niue","Pacific/Norfolk","Pacific/Noumea","Pacific/Pago_Pago","Pacific/Palau","Pacific/Pitcairn","Pacific/Ponape","Pacific/Port_Moresby","Pacific/Rarotonga","Pacific/Saipan","Pacific/Tahiti","Pacific/Tarawa","Pacific/Tongatapu","Pacific/Truk","Pacific/Wake","Pacific/Wallis"];

        return timezones.indexOf(timezone) !== -1;
    }

    function isValidCategory(category) {
        let categories = ["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20"];

        return categories.indexOf(category) !== -1;
    }

    function isValidCountry(country) {
        let countries = ["AF","AX","AL","DZ","AS","AD","AO","AI","AQ","AG","AR","AM","AW","AU","AT","AZ","BS","BH","BD","BB","BY","BE","BZ","BJ","BM","BT","BO","BQ","BA","BW","BV","BR","IO","BN","BG","BF","BI","KH","CM","CA","CV","KY","CF","TD","CL","CN","CX","CC","CO","KM","CG","CD","CK","CR","CI","HR","CU","CW","CY","CZ","DK","DJ","DM","DO","EC","EG","SV","GQ","ER","EE","ET","FK","FO","FJ","FI","FR","GF","PF","TF","GA","GM","GE","DE","GH","GI","GR","GL","GD","GP","GU","GT","GG","GN","GW","GY","HT","HM","VA","HN","HK","HU","IS","IN","ID","IR","IQ","IE","IM","IL","IT","JM","JP","JE","JO","KZ","KE","KI","KP","KR","KW","KG","LA","LV","LB","LS","LR","LY","LI","LT","LU","MO","MK","MG","MW","MY","MV","ML","MT","MH","MQ","MR","MU","YT","MX","FM","MD","MC","MN","ME","MS","MA","MZ","MM","NA","NR","NP","NL","NC","NZ","NI","NE","NG","NU","NF","MP","NO","OM","PK","PW","PS","PA","PG","PY","PE","PH","PN","PL","PT","PR","QA","RE","RO","RU","RW","BL","SH","KN","LC","MF","PM","VC","WS","SM","ST","SA","SN","RS","SC","SL","SG","SX","SK","SI","SB","SO","ZA","GS","SS","ES","LK","SD","SR","SJ","SZ","SE","CH","SY","TW","TJ","TZ","TH","TL","TG","TK","TO","TT","TN","TR","TM","TC","TV","UG","UA","AE","GB","US","UM","UY","UZ","VU","VE","VN","VG","VI","WF","EH","YE","ZM","ZW"];

        return countries.indexOf(country) !== -1;
    }


/**
 * This function retrieves the file extension from a given file name.
 * It returns the extension in lowercase format. If no extension is found, it returns null.
 *
 * @param {string} file - The name of the file from which to extract the extension.
 * @returns {string|null} - The file extension in lowercase, or null if no extension is found.
 *
 * How the function works:
 * 1. It checks if the input file name is provided. If not, it logs an error and returns null.
 * 2. It uses a regular expression /\.([^.]+)$/ to find the file extension:
 *    - \. matches the last dot in the file name.
 *    - ([^.]+) captures one or more characters that are not dots after the last dot.
 *    - $ ensures the match is at the end of the string.
 * 3. If no match is found, it logs a warning and returns null.
 * 4. If a match is found, it extracts the extension, converts it to lowercase, and returns it.
 */

    function getFileExtension(file) {
        // Check if the file name is provided
        if (!file) {
            logger.error('Error: File name is empty.');
            return null;
        }

        // Using regular expression to match the extension
        const match = file.match(/\.([^.]+)$/);

        // If no match found
        if (!match) {
            logger.error('Warning: No extension found.');
            return null;
        }

        // Extract and return the extension
        return match[1].toLowerCase();
    }

    function validateUploadFile(str) {
        if (str.indexOf('.') !== str.lastIndexOf('.')) {
            return false;
        }
        return true;
    }

     /**
     * UploadCustomcsv method returns a promise  
     * @param {*} uploadObj a object which contains the csv Inputs and the file 
     * @returns the promise of object to be send to the queue and also the url to which it is uploaded
     */
    async function UploadCustomcsv(uploadObj) {
        return new Promise((resolve, reject) => {
            try {

                logger.info(`UploadCustomcsv Starts `)

                const csvFile_id = uploadObj.csvFile_id;
                // Validate the inputs
                if (validateUploadFile(csvFile_id) && validateUploadFile(uploadObj.file.name) && getFileExtension(uploadObj.file.path) === "csv" && getFileExtension(csvFile_id) === "csv") {
                    
                    logger.info(`Validation Done in custom upload Csv`)

                    let tmp_path = uploadObj.file.path;
                    let fname = uploadObj.file.name;
                    const app_id = uploadObj.app_id;



                    // Handle undefined filename and type
                    if (!fname) {
                        const ags = tmp_path.split("/");
                        fname = ags.pop().split(".").shift();
                    }

                    // Generate new filename using moment
                    let filename = moment.utc() + '_' + fname ;

                    //creating object for the Queue
                    let obj = {
                        fileUrl: '',         // this is the file uploaded csv for custom csv 
                        metadata: {
                            dataObject: {
                                q: '',   // this is the query generated in case of traits 
                                did: uploadObj.did || '', 
                                appid: app_id,
                                audid: uploadObj.objectid || '',
                                email: uploadObj.email || '',
                                header: uploadObj.fileheader || '', 
                                clientid: uploadObj.clientid || '', 
                                datatype: uploadObj.datatype || '',
                                fileName: filename || '', 
                                createdate: new Date().toISOString(),
                                serviceType: EXPORTS.FILE_SEGMENT //TODO
                            },
                            csvFileName: filename || '',  
                        }
                    };

                    if (semusiConfig.uploadFilesStorage === 'E2E') {
                        logger.info(` Custom Csv Uploading To e2e `)

                        var minioClient = new Minio.Client({
                            endPoint: semusiConfig.endPoint,
                            useSSL: semusiConfig.useSSL,
                            accessKey: semusiConfig.accessKey,
                            secretKey: semusiConfig.secretKey
                        });


                        // Using fPutObject API upload file to the bucket
                        minioClient.fPutObject(semusiConfig.minioBuckets.exportsCsv, filename, tmp_path, function (err, etag) {
                            if (err) {

                                logger.error("Error occurred in Custom Csv upload  To E2E  Now Rejecting => " + err);
                                reject(err)

                            } else {
                                logger.info(` Custom Csv Uploaded Succesfully To E2E  `)


                                obj.fileUrl = semusiConfig.blobContainerCDN + filename;
                                obj.metadata.dataObject.fileName = filename;
                                obj.metadata.dataObject.emailUrl = semusiConfig.blobContainerCDN + filename;


                                logger.info(` Now Resolving`)
                                resolve(obj);
                            }
                        });
                    } else if (semusiConfig.uploadFilesStorage === 'azure') {
                        logger.info(` Custom Csv Uploading To Azure `)

                        var bucket = semusiConfig.segmentCsvFile;
                        var blobContainerCDN = semusiConfig.blobContainerCDN + bucket;

                        var blobService = Azure.createBlobServiceWithSas(semusiConfig.azureApiBlobSASURL, semusiConfig.azureApiStorageSASToken);
                        var keyVar = "uploads/audiencesegment/" + app_id + "/" + filename;
                        blobService.createBlockBlobFromLocalFile(bucket, keyVar, tmp_path, (error, result) => {
                            if (error) {
                                // Handle blob error
                                logger.error(` Custom Csv Uploading FailedTo Azure Now Rejecting ${JSON.stringify(error)} `)
                                reject(error);
                            } else {
                                logger.info(` Custom Csv Uploaded Succesfully To Azure `)

                                obj.fileUrl = blobContainerCDN + '/' + result.name;
                                obj.metadata.dataObject.fileName = filename;
                                obj.metadata.dataObject.emailUrl = blobContainerCDN + '/' + keyVar;

                                logger.info(` Now Resolving`)
                                resolve(obj);
                            }
                        });
                    }

                } else {
                    //In Case Upload validation fails
                    logger.error("Validation Fails for CustomCsv Failed to upload. Only .csv files are allowed.  Now Rejecting");
                    reject(new Error("Validation failed for CustomCsv. Only .csv files are allowed."));
                }
            } catch (error) {
                logger.error(`Error in csv upload: Now Rejecting ${error}`);
                reject(error);
            }
        });
    }
  //start of utm post base url settings
  appsApi.postbaseUrl = function( params ) {
    let baseUrl = params.qstring.baseUrl
     common.db.collection('apps').update({'_id':common.db.ObjectID(params.qstring.args.app_id)},{$set:{'appsettingsBaseUrl': baseUrl}}, function (err, result) {
         if (err || result == null) {
             logger.error("err occured in saving url in mongo", err)
             common.returnOutput(params, 500, "Error");
         }else{
             common.returnOutput(params,result)
         }
     })
     
 }
 //end of utm post base url settings

}(appsApi));

module.exports = appsApi;
