var eventString = {},
    _ = require('underscore'),
    logger = require('../../../logger');
    moment = require('moment'),
    LRU = require("lru-cache"),
    options = {length: function (n, key) { return n * 2 + key.length }},
    pgsql = require('./../../utils/pgsql.utils.js'),
    eventstringCache = new LRU(options),
    sessionCache = new LRU(options),
    attributeValueCache = new LRU(options),
    Promise = require('bluebird');
    
(function (eventString) {
    //insert element between array
    Array.prototype.insert = function ( index, item ) {
        this.splice( index, 0, item );
    };

    var finalUpdateString = '' //final string for batch processing of eventstrings
    var finalArray = [] //local object to store eventstring objects
    var AttributeArray = [] //array to store attribute data
    var AttributeValueArray = [];//array to store attribute value data
    var finalUpdateSessionLength = ''; //final string for batch processing of session length
    var finalSessionArray = []; //local object to store all sessions
    var finalSessionString = '';//final string for batch processing of insertion of sessions
    var appEventsData = []; //array to store appEventsData

    //update existing attribute details
    let updateAttributeValueDetails = async function(attributeValueTable,  attribute, value) {
        let UpdateAttributeValueDetailQuery ='update ' + attributeValueTable + ' set frequency = frequency + 1 where attributeid = '+ attribute +" and value = '"+ value + "'";  
        let result = await pgsql.executeQuery(UpdateAttributeValueDetailQuery)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in fetching attribute detail => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }


    //insert a row in attribute value table
    let insertInAttributeValueTable = async function(attributeValueTable, attribute, value, frequency) {
        let insertAttributeValueQuery = "with s as (select id from "+ attributeValueTable  +" where attributeid = "+ attribute +" and value = '"+ value +"' ), i as ( insert into "+ attributeValueTable  +"  (attributeid, value) select " + attribute + " , '" + value + "' where not exists (select 1 from s) returning id) select id from i union all select id from s"
        insertAttributeValueQuery = insertAttributeValueQuery.replace(/\\/g, '');
        let result = await pgsql.executeQuery(insertAttributeValueQuery)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in inserting attribute value => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //update existing attribute details
    let updateAttributeDetails = async function(UpdateAttributeDetailQuery) {
        let result = await pgsql.executeQuery(UpdateAttributeDetailQuery)
            .then((result) => { 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in fetching attribute detail => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //insert a row in attribute table
    let insertInAttributeTable = async function(insertAttributeQuery) {
        let result = await pgsql.executeQuery(insertAttributeQuery)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in inserting attribute => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }



    //insert a batch in session table
    let insertInSessionTable = async function(finalString) {
        return new Promise((resolve, reject) => {
            pgsql.executeQuery(finalString)
            .then((result) => {
                 
                resolve(result);   
            })
            .catch((error)=>{
                logger.error(`error in inserting sessions => ${error}`);
                process.exit()
                let result = [];
                reject(result)
            })
        });    
    }
    //updating session length on the basis of event occurence
    let updateSessionLength = async function(finalString) {
        // let UpdateUserSessionLength = "Update "+ sessionTable + " set sessionlength = "+ eventtime +"-"+ startEventtime+" where id = "+ id +";"
        let result = await pgsql.executeQuery(finalString)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in updating sessions length  => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //fetch current eventstring for the user
    let fetchAppUsersData = async function( deviceId, eventStringTable, eventStringColumn, eventStringCurrentColumn) {
        let FetchUserDetailQuery = "select "+ eventStringColumn +","+ eventStringCurrentColumn +",day_map from "+ eventStringTable +" where did = '"+ deviceId +"';"
        let result = await pgsql.executeQuery(FetchUserDetailQuery)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in fetching eventstring => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

     //update eventstring after inserting the events 
     let upsertAppUsersDataBatch = async function( UpdateString ) {
        let result = await pgsql.executeQuery(UpdateString)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in updating eventstring => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //fetch app events data
    let insertAppEventsData = async function(appEventTable, event) {
        let currentDate = new Date();
        let currentTimestamp = currentDate.getTime()
        currentTimestamp = Math.round(currentTimestamp /1000);
        let AppEventsQuery = "insert into "+ appEventTable +" values(default,'"+ event +"', '{}', '[]', "+ currentTimestamp +" , "+ currentTimestamp +") returning id;";
        let result = await pgsql.executeQuery(AppEventsQuery)
            .then((result) => {
                    return result;   
            })
            .catch((error)=>{
                logger.error(`error in inserting in app events table => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }


    //prepare event string for the current sessions coming in the batch
    let prepareEventString = async function(iter, arraylength, user, appEventsData, appUsersTable, sessionTable, attributeTable, attributeValueTable, eventStringTable, eventStringColumn, eventStringCurrentColumn, appEventTable){
         
        //if iter is zero, meaning it is the starting of the batch, initialize all the strings used for batch processing
        if(iter == 0){
            finalUpdateString = ''  //string use to batch upsert eventstrings
            finalUpdateSessionLength = '';  //string used to batch update session length
            finalSessionString = '';    //String used to batch insert the sessions
        }

        let eventStringArray = [] //event string array
        let finaleventstring = null//final string after all the events has occured
        
        if(user.length>0){
            if(finalArray[user[0].did]){
                eventStringArray = finalArray[user[0].did]
            }
            //compute date string as per required format
            let currentDate = new Date(user[0].dt);
            let day = currentDate.getDate();
            let month = currentDate.getMonth() + 1;
            let year = currentDate.getFullYear() % 100;
            var dateString = null
            if(day<10){
                dateString = "0" + day.toString()
            }
            else{
                dateString = day.toString()
            }
            if(month<10){
                dateString = dateString+"0"+month.toString()
            }
            else{
                dateString = dateString + month.toString()
            }
            dateString = dateString + year.toString()  
            
        }        
        let pdid = 1; //initialize pdid with 1 for new session
        let starttime = 0; //for session length
        let endtime = 0;    //for session length
        let updateSession = 0;  //flag to check whether update session length is to be called or not
        let sessionArrayLength = 0; // store the index for details of current session to update session length
        if(user.length>0){
            //initialising session id as fetching from session table
            starttime = user[0].eventtime
            endtime = user[0].eventtime
            //filter all the sessions with this did and date and key
            let sessionArray = sessionCache.get(user[0].did);
            var sessionid = []
            if(sessionArray == undefined){
                sessionArray = []
                pdid = 1;
            }
            else{
                //update pdid, increment by 1 for current session
                pdid = sessionArray.length + 1
                //try to search if current session already exists
                sessionid = sessionArray.filter(function(v, j) {
                  return (v.sid == user[0].sid);
                })

            }
            

            //if current session do not exists
            if(!sessionid.length>0){
                //session id should not be null
                if(user[0].sid!=null&&user[0].sid!='null'){
                    //compute user array length
                    let userLen = user.length
                    //compute session length
                    let sessLength = user[userLen-1].eventtime - user[0].eventtime;
                    //make session object
                    let sessionObject = {
                        "did":user[0].did,
                        "sid":user[0].sid,
                        "eventtime": user[0].eventtime,
                        "key": "Session_Start",
                        "status":1,
                        "sessionlength": sessLength,
                        "dt":user[0].dt,
                        "platform": user[0].p,
                        "pdid":pdid
                    }
                    //replace double quotes from platform
                    let platform = sessionObject.platform.replace(/["]+/g, '')
                    //make the insert query
                    let insertSessionQuery = "insert into "+ sessionTable + " values (DEFAULT, '"+ sessionObject.did + "', '"+ sessionObject.sid +"'," + sessionObject.eventtime + ",'" + sessionObject.key + "', "+ sessionObject.status + ", "+ sessionObject.sessionlength + ", '" + sessionObject.dt + "', '" + platform + "', " + sessionObject.pdid + ") on conflict(did,sid,key) do nothing;"
                    //append this string for batch processing
                    finalSessionString = finalSessionString + insertSessionQuery
                    //push object to store in local cache
                    sessionArray.push(sessionObject)
                    sessionCache.set(user[0].did,sessionArray);
                }
            }
            //if current session exists
            else{
                //mark this flag to 1 to update session length
                updateSession = 1;
                //set the pdid of existing sessions
                pdid = sessionid[0].pdid;
            }
        }
        //for each event occuring in this batch, make an event string
        // const now5 = new Date()
        // var time5 = Math.round(now5.getTime())  
        for(var i = 0; i<user.length;i++){
            //getting event id for the current event
            let event = _.filter(appEventsData,{name:user[i].key})  
            if(!event.length>0){
                let eventid = await insertAppEventsData(appEventTable, user[i].key);
                if(eventid.length>0){
                   event = eventid[0]; 
                   appEventsData.push({name:user[i].key,id:event[0].id});
                }
            }
            //first check if session id is null, if yes ignore event
            if(user[i].sid!=null&&user[i].sid!='null'&&user[i].did!=null&&user[i].did!='null'&&user[i].did!=undefined&&user[i].did!='undefined'){
                //App_foreground and background are rejected and not to be stored in eventstring
                if(user[i].key!="App_Foreground"&&user[i].key!="App_Background"){                    
                    //check if this date exists
                    if(eventStringArray.indexOf("D"+dateString)==-1){
                        eventStringArray.push("D"+dateString)
                    }
                    //if session id does not exists in the current eventstring
                    if(eventStringArray.indexOf("S"+pdid)==-1){
                        eventStringArray.push("S"+pdid)
                        eventStringArray.push("T"+user[i].eventtime)
                        //if current event is not session start, then insert that event as well
                        if(user[i].key!='Session_Start'&& user[i].key!='Session_End'){
                            eventStringArray.push("B"+event[0].id)
                            eventStringArray.push("T" + user[i].eventtime)
                            //adding logic for attribute here
                            if(user[i].segment!='{}'&&Object.keys(user[i].segment).length != 0 && user[i].segment.constructor === Object&&user[i].key!='_app_crash'&&user[i].key!='Inbox_Read'&&user[i].key!='Inbox_Viewed'&&user[i].key!='Inbox_Deleted'&&user[i].key!='Inbox_Unread'){
                                if(user[i].key=='Campaign_Viewed'|| user[i].key=='Campaign_Deleted'||user[i].key=='Campaign_Clicked'|| user[i].key=='Campaign_Received'){
                                    validKeys = ['campid']
                                    Object.keys(user[i].segment).forEach((key) => validKeys.includes(key) || delete user[i].segment[key]);
                                }
                                //loop over segment array
                                // const now1 = new Date()
                                // var time1 = Math.round(now1.getTime())
                                for(let key in user[i].segment){
                                    if(user[i].segment[key]!=''&&user[i].segment[key]!=null){ 
                                        let valueDateTime = null //check for datatype date time
                                        let datatype = typeof(user[i].segment[key])
                                        //check if valus is int
                                        if(/^\d+$/.test(user[i].segment[key])){
                                            datatype = 'int'
                                        }
                                        //check if value is float
                                        else if(/^[0-9]+\.[0-9]+$/.test(user[i].segment[key])){
                                            datatype = 'float'
                                        }
                                        let datetime = null
                                        let datetimeValue = null
                                        //check if value can be converted to date time 
                                        if(datatype=='string'){
                                            valueDateTime =  Date.parse(user[i].segment[key])
                                            if((!isNaN(valueDateTime))&&valueDateTime>0){
                                                valueDateTime = valueDateTime/1000
                                                datetime = moment.unix(valueDateTime).format("DD-MM-YYYY");
                                                datetimeValue = datetime.replace(/-/g,'');
                                                datatype = "datetime" 
                                                
                                            }
                                        }
                                        //check for existing attribute
                                        let attributeKey = AttributeArray.filter(function(v, j) {
                                          return (v.key == user[i].key && v.name == key);
                                        })
                                        //if attribute does not exists
                                        if(!attributeKey.length>0){  
                                            //make attribute object
                                            let attributeObject = {
                                                "key": user[i].key,
                                                "name": key,
                                                "datatype": datatype,
                                                "datetimemax": null,
                                                "datetimemin": null,
                                                "intmax": 0,
                                                "intmin":0,
                                                "floatmax" :0,
                                                "floatmin":0
                                            }
                                            //if datatype is string, no operation is required
                                            if(datatype=='boolean'||datatype=='string'){
                                                //no change 
                                            } 
                                            //if datatype is date time, assign value to datemin, datemax
                                            else if(datatype=='datetime'){
                                                if(datetime<0){
                                                    datatype == 'string'    
                                                }
                                                else{
                                                    attributeObject.datatype = 'datetime' 
                                                    attributeObject.datetimemin = datetime
                                                    attributeObject.datetimemax = datetime
                                                } 
                                            }
                                            //check if type is int, assign value to intmin and intmax
                                            else if(datatype == 'int'){
                                                attributeObject.datatype = 'int'
                                                if( user[i].segment[key] > 1000000000)
                                                {
                                                    user[i].segment[key] = 1000000000
                                                }
                                                attributeObject.intmin = user[i].segment[key]
                                                attributeObject.intmax = user[i].segment[key]
                                            }
                                            //check if type is float, assign value to floatmax and floatmin
                                            else if(datatype=='float'){
                                                attributeObject.datatype = 'float'
                                                attributeObject.floatmax = user[i].segment[key]
                                                attributeObject.floatmin = user[i].segment[key]
                                            }
                                            //check for datetimemin and datetimemax and make insert query
                                            let insertAttributeQuery = ''
                                            if(attributeObject.datetimemax==null||attributeObject.datetimemin==null){
                                                //make insert query
                                                insertAttributeQuery = "insert into "+ attributeTable + " values (DEFAULT, '"+ attributeObject.key + "', '"+ attributeObject.name +"','" + attributeObject.datatype + "', "+ attributeObject.datetimemax +", "+ attributeObject.datetimemin +", "+ attributeObject.intmax + ", "+ attributeObject.intmin + ", "+ attributeObject.floatmax +", "+ attributeObject.floatmin +") on conflict(key,name) do nothing returning id;"
                                            }
                                            else{
                                                //make insert query
                                                insertAttributeQuery = "insert into "+ attributeTable + " values (DEFAULT, '"+ attributeObject.key + "', '"+ attributeObject.name +"','" + attributeObject.datatype + "', '"+ attributeObject.datetimemax +"', '"+ attributeObject.datetimemin +"', "+ attributeObject.intmax + ", "+ attributeObject.intmin + ", "+ attributeObject.floatmax +", "+ attributeObject.floatmin +") on conflict(key,name) do nothing returning id;"
                                            }
                                            
                                            //insert in attribute table
                                            let attributeInsertResponse = await insertInAttributeTable(insertAttributeQuery)
                                            //get the id in response
                                            attributeKey = attributeInsertResponse[0]
                                            //add the id to eventstring
                                            eventStringArray.push("A"+ attributeKey[0].id)
                                            //update the id of current object
                                            attributeObject.id = attributeKey[0].id
                                            //store the object in local
                                            AttributeArray.push(attributeObject);
                                        }
                                        else{
                                            //define column name which is to be updated
                                            let column = ''
                                            if(datatype=='boolean'||datatype=='string'){
                                                //no change 
                                            } 
                                            //if datatype is datetime then first convert it into epoch and do the comparison and update the value in the table
                                            else if(datatype=='DateTime'){
                                                flag = 0;
                                                let num1 = attributeKey[0].datetimemin
                                                let num2 = attributeKey[0].datetimemax
                                                let str = num1.toString();
                                                let str2 = num2.toString();
                                                let str3 = str.substring(4, 8) +'-' + str.substring(2, 4) + '-' + str.substring(0, 2)
                                                let str4 = str2.substring(4, 8) +'-' + str2.substring(2, 4) + '-' + str2.substring(0, 2)
                                                let date1 = new Date(str3);
                                                let date2 = new Date(str4);
                                                let time1 = date1.getTime()/1000;
                                                let time2 = date2.getTime()/1000;

                                                if(valueDateTime>time1&&valueDateTime>time2){
                                                    column = 'datetimemax'
                                                    flag =1;
                                                }
                                                else if(valueDateTime<time1){
                                                    column = 'datetimemin'
                                                    flag=1;
                                                }
                                                //flag ==1 meaning, if value is to be update call the update attribute query
                                                if(flag==1){
                                                    //make query
                                                    let UpdateAttributeDetailQuery ="update " + attributeTable + " set " + column + " = '" + datetime + "' where id = " + attributeKey[0].id;
                                                    let updateAttribute = await updateAttributeDetails(UpdateAttributeDetailQuery)   
                                                }

                                                 
                                            }
                                            //if datatype is int, compare with min and max and update accordingly
                                            else if(datatype=='int'){
                                                flag = 0
                                                if(user[i].segment[key]>attributeKey[0].intmin&&user[i].segment[key]>attributeKey[0].intmax)
                                                {
                                                    column = 'intmax'
                                                    flag =1;
                                                }    
                                                else if(user[i].segment[key]<attributeKey[0].intmin)
                                                {
                                                    column = 'intmin'
                                                    flag =1;
                                                }
                                                if( user[i].segment[key] > 1000000000)
                                                {
                                                    user[i].segment[key] = 1000000000 
                                                }
                                                //flag ==1 meaning, if value is to be update call the update attribute query
                                                if(flag==1&&user[i].segment[key] <= 1000000000){
                                                    //make query
                                                    let UpdateAttributeDetailQuery ="update " + attributeTable + " set " + column + " = " + user[i].segment[key]  + " where id = " + attributeKey[0].id;
                                                    let updateAttribute = await updateAttributeDetails(UpdateAttributeDetailQuery)   
                                                }
                                            } 
                                            //if datatype is float, compare with min and max and update accordingly
                                            else if(datatype=='float'){
                                                flag =0;
                                                if(user[i].segment[key]>attributeKey[0].floatmin&&user[i].segment[key]>attributeKey[0].floatmax)
                                                {
                                                    column = 'floatmax'
                                                    flag = 1;
                                                }    
                                                else if(user[i].segment[key]<attributeKey[0].floatmin)
                                                {
                                                    column = 'floatmin'
                                                    flag = 1;
                                                }
                                                 if( user[i].segment[key] > 1000000000)
                                                {
                                                    user[i].segment[key] = 1000000000
                                                }
                                                //flag ==1 meaning, if value is to be update call the update attribute query
                                                if(flag==1&&user[i].segment[key] <= 1000000000){
                                                    //make query
                                                    let UpdateAttributeDetailQuery ="update " + attributeTable + " set " + column + " = " + user[i].segment[key] + " where id = " + attributeKey[0].id;
                                                    let updateAttribute = await updateAttributeDetails(UpdateAttributeDetailQuery)   
                                                }
                                                 
                                            }
                                            eventStringArray.push("A"+ attributeKey[0].id)
                                        }
                                        //value cannot be empty or null
                                        if(user[i].segment[key]!=''&&user[i].segment[key]!=null){    
                                            
                                            //if datatype is string, insert value in attribute value table, else append as it is
                                            if(datatype == 'string'||datatype == 'boolean'){ 
                                                //check if value already exists
                                                // const now11 = new Date()
                                                // var time11 = Math.round(now11.getTime())
                                                // let attributeValue = AttributeValueArray.filter(function(v, j) {
                                                //   return (v.value == user[i].segment[key] && v.attributeid == attributeKey[0].id);
                                                // })
                                                let attributeValue = attributeValueCache.get(user[i].segment[key]+'_'+ attributeKey[0].id);
                                                // const now12 = new Date()
                                                // var time12 = Math.round(now12.getTime())
                                                 
                                                //make value object
                                                var valueObject = {
                                                    "id" : 1,
                                                    "attributeid" : attributeKey[0].id,
                                                    "value" : user[i].segment[key],
                                                    "frequency" : 1
                                                }
                                                //if attribute Value doesnt exists
                                                if(attributeValue == undefined){
                                                    //insert in attribute value table
                                                    let attributeValueInsertResponse = await insertInAttributeValueTable(attributeValueTable, attributeKey[0].id , user[i].segment[key], 1)
                                                    //get id in response
                                                    attributeValues = attributeValueInsertResponse[0]  
                                                    if(attributeValues!=undefined){
                                                        //update value object
                                                        valueObject.id = attributeValues[0].id
                                                        attributeValue = attributeValues[0].id
                                                        //push this object to local
                                                        // AttributeValueArray.push(valueObject) 
                                                        attributeValueCache.set(user[i].segment[key]+'_'+ attributeKey[0].id,attributeValues[0].id);
                                                         
                                                    }
                                                          
                                                }
                                                // else{
                                                //     //if entry already exists, update frequency
                                                //     let updatefrequency = await updateAttributeValueDetails(attributeValueTable, attributeKey[0].id , user[i].segment[key]) 
                                                //     valueObject.frequency = valueObject.frequency + 1
                                                // }
                                                if(attributeValue!=undefined){
                                                    if(attributeValue != null&&attributeValue!='null'){ 
                                                        eventStringArray.push("V"+ attributeValue)
                                                    }
                                                }
                                                
                                            }
                                            //if datatype is int or float, add as it is to eventstring
                                            else if(datatype=='number'||datatype=='int'||datatype=='float'){
                                                if(user[i].segment[key]!=null&&user[i].segment[key]!='null'){
                                                    if(datatype=='float'){
                                                        if(parseFloat(user[i].segment[key])){
                                                            let temp = parseFloat(user[i].segment[key]).toString();
                                                            eventStringArray.push("V" + temp);    
                                                        }                                                    
                                                    }
                                                    else{
                                                        eventStringArray.push("V"+ user[i].segment[key])
                                                    }
                                                }
                                            }
                                            //if datatype is datetime, add that to eventstring
                                            else if(!isNaN(valueDateTime)){
                                                if(datetime!=null&&datetime!='null'){    
                                                    eventStringArray.push("V"+ datetimeValue)
                                                }
                                            }
                                        }
                                    }   
                                }
                                // const now2 = new Date()
                                // var time2 = Math.round(now2.getTime())
                                 
                            }
                        }
                        //add keyword for session end as well, irrespective of its occurence
                        eventStringArray.push("E"+pdid)
                           
                    }
                    //if session already exists
                    else {
                        endtime = user[i].eventtime;                        
                        //make session end object if key is Session End
                        if(user[i].key == "Session_End"){
                            let sessionObject = {
                                "did":user[i].did,
                                "sid":user[i].sid,
                                "eventtime": user[i].eventtime,
                                "key": "Session_End",
                                "status":0,
                                "sessionlength":0,
                                "dt":user[i].dt,
                                "platform": user[i].p,
                                "pdid":pdid
                            }

                            //replace double quotes from platform
                            let platform = sessionObject.platform.replace(/["]+/g, '')
                            //generate insert query
                            let insertSessionQuery = "insert into "+ sessionTable + " values (DEFAULT, '"+ sessionObject.did + "', '"+ sessionObject.sid +"'," + sessionObject.eventtime + ",'" + sessionObject.key + "', "+ sessionObject.status + ", "+ sessionObject.sessionlength + ", '" + sessionObject.dt + "', '" + platform + "', " + sessionObject.pdid + ") on conflict(did,sid,key) do nothing;"
                            //update string for batch insert
                            finalSessionString = finalSessionString + insertSessionQuery
                        }
                        //if session already exists in current eventstring, just add the incoming event
                        else if(user[i].key!="Session_Start"){
                            //fetch index to store session before session end
                            let index = eventStringArray.indexOf("E"+pdid)
                            //add event to eventstring
                            eventStringArray.insert(index,"B"+event[0].id)
                            index = eventStringArray.indexOf("E"+pdid)
                            eventStringArray.insert(index,"T" + user[i].eventtime)
                            //adding logic for attribute here
                            if(user[i].segment!='{}'&& user[i].key!='_app_crash'&&Object.keys(user[i].segment).length != 0 && user[i].segment.constructor === Object&&user[i].key!='_app_crash'&&user[i].key!='Inbox_Read'&&user[i].key!='Inbox_Viewed'&&user[i].key!='Inbox_Deleted'&&user[i].key!='Inbox_Unread'){
                                if(user[i].key=='Campaign_Viewed'|| user[i].key=='Campaign_Deleted'||user[i].key=='Campaign_Clicked'|| user[i].key=='Campaign_Received'){
                                    validKeys = ['campid']
                                    Object.keys(user[i].segment).forEach((key) => validKeys.includes(key) || delete user[i].segment[key]);
                                }
                                // const now3 = new Date()
                                // var time3 = Math.round(now3.getTime())
                                //loop over segment array
                                for(let key in user[i].segment){
                                    if(user[i].segment[key]!=''&&user[i].segment[key]!=null){ 
                                        let valueDateTime = null //check for datatype date time
                                        let datatype = typeof(user[i].segment[key])
                                        //check if valus is int
                                        if(/^\d+$/.test(user[i].segment[key])){
                                            datatype = 'int'
                                        }
                                        //check if value is float
                                        else if(/^[0-9]+\.[0-9]+$/.test(user[i].segment[key])){
                                            datatype = 'float'
                                        }
                                        let datetime = null
                                        let datetimeValue = null
                                        //check if value can be converted to date time 
                                        if(datatype=='string'){
                                            valueDateTime =  Date.parse(user[i].segment[key])
                                            if((!isNaN(valueDateTime))&&valueDateTime>0){
                                                valueDateTime = valueDateTime/1000
                                                datetime = moment.unix(valueDateTime).format("DD-MM-YYYY");
                                                datetimeValue = datetime.replace(/-/g,'');
                                                datatype = "datetime" 
                                                
                                            }
                                        }
                                        //check for existing attribute
                                        let attributeKey = AttributeArray.filter(function(v, j) {
                                          return (v.key == user[i].key && v.name == key);
                                        })
                                        //if attribute does not exists
                                        if(!attributeKey.length>0){  
                                            //make attribute object
                                            let attributeObject = {
                                                "key": user[i].key,
                                                "name": key,
                                                "datatype": datatype,
                                                "datetimemax": null,
                                                "datetimemin": null,
                                                "intmax": 0,
                                                "intmin":0,
                                                "floatmax" :0,
                                                "floatmin":0
                                            }
                                            //if datatype is string, no operation is required
                                            if(datatype=='boolean'||datatype=='string'){
                                                //no change 
                                            } 
                                            //if datatype is date time, assign value to datemin, datemax
                                            else if(datatype=='datetime'){
                                                if(datetime<0){
                                                    datatype == 'string'    
                                                }
                                                else{
                                                    attributeObject.datatype = 'datetime' 
                                                    attributeObject.datetimemin = datetime
                                                    attributeObject.datetimemax = datetime
                                                } 
                                            }
                                            //check if type is int, assign value to intmin and intmax
                                            else if(datatype == 'int'){
                                                attributeObject.datatype = 'int'
                                                if( user[i].segment[key] > 1000000000)
                                                {
                                                    user[i].segment[key] = 1000000000
                                                }
                                                attributeObject.intmin = user[i].segment[key]
                                                attributeObject.intmax = user[i].segment[key]
                                            }
                                            //check if type is float, assign value to floatmax and floatmin
                                            else if(datatype=='float'){
                                                attributeObject.datatype = 'float'
                                                attributeObject.floatmax = user[i].segment[key]
                                                attributeObject.floatmin = user[i].segment[key]
                                            }
                                            //check for datetimemin and datetimemax and make insert query
                                            let insertAttributeQuery = ''
                                            if(attributeObject.datetimemax==null||attributeObject.datetimemin==null){
                                                //make insert query
                                                insertAttributeQuery = "insert into "+ attributeTable + " values (DEFAULT, '"+ attributeObject.key + "', '"+ attributeObject.name +"','" + attributeObject.datatype + "', "+ attributeObject.datetimemax +", "+ attributeObject.datetimemin +", "+ attributeObject.intmax + ", "+ attributeObject.intmin + ", "+ attributeObject.floatmax +", "+ attributeObject.floatmin +") on conflict(key,name) do nothing returning id;"
                                            }
                                            else{
                                                //make insert query
                                                insertAttributeQuery = "insert into "+ attributeTable + " values (DEFAULT, '"+ attributeObject.key + "', '"+ attributeObject.name +"','" + attributeObject.datatype + "', '"+ attributeObject.datetimemax +"', '"+ attributeObject.datetimemin +"', "+ attributeObject.intmax + ", "+ attributeObject.intmin + ", "+ attributeObject.floatmax +", "+ attributeObject.floatmin +") on conflict(key,name) do nothing returning id;"
                                            }
                                            
                                            //insert in attribute table
                                            let attributeInsertResponse = await insertInAttributeTable(insertAttributeQuery)
                                            //get the id in response
                                            attributeKey = attributeInsertResponse[0]
                                            //add the id to eventstring
                                            let index = eventStringArray.indexOf("E"+pdid)
                                            eventStringArray.insert(index, "A"+ attributeKey[0].id)
                                            //update the id of current object
                                            attributeObject.id = attributeKey[0].id
                                            //store the object in local
                                            AttributeArray.push(attributeObject);
                                        }
                                        else{
                                            //define column name which is to be updated
                                            let column = ''
                                            if(datatype=='boolean'||datatype=='string'){
                                                //no change 
                                            } 
                                            //if datatype is datetime then first convert it into epoch and do the comparison and update the value in the table
                                            else if(datatype=='DateTime'){
                                                flag = 0;
                                                let num1 = attributeKey[0].datetimemin
                                                let num2 = attributeKey[0].datetimemax
                                                let str = num1.toString();
                                                let str2 = num2.toString();
                                                let str3 = str.substring(4, 8) +'-' + str.substring(2, 4) + '-' + str.substring(0, 2)
                                                let str4 = str2.substring(4, 8) +'-' + str2.substring(2, 4) + '-' + str2.substring(0, 2)
                                                let date1 = new Date(str3);
                                                let date2 = new Date(str4);
                                                let time1 = date1.getTime()/1000;
                                                let time2 = date2.getTime()/1000;

                                                if(valueDateTime>time1&&valueDateTime>time2){
                                                    column = 'datetimemax'
                                                    flag =1;
                                                }
                                                else if(valueDateTime<time1){
                                                    column = 'datetimemin'
                                                    flag=1;
                                                }
                                                //flag ==1 meaning, if value is to be update call the update attribute query
                                                if(flag==1){
                                                    //make query
                                                    let UpdateAttributeDetailQuery ="update " + attributeTable + " set " + column + " = '" + datetime + "' where id = " + attributeKey[0].id;
                                                    let updateAttribute = await updateAttributeDetails(UpdateAttributeDetailQuery)   
                                                }

                                                 
                                            }
                                            //if datatype is int, compare with min and max and update accordingly
                                            else if(datatype=='int'){
                                                flag = 0
                                                if(user[i].segment[key]>attributeKey[0].intmin&&user[i].segment[key]>attributeKey[0].intmax)
                                                {
                                                    column = 'intmax'
                                                    flag =1;
                                                }    
                                                else if(user[i].segment[key]<attributeKey[0].intmin)
                                                {
                                                    column = 'intmin'
                                                    flag =1;
                                                }
                                                if( user[i].segment[key] > 1000000000)
                                                {
                                                    user[i].segment[key] = 1000000000
                                                }
                                                //flag ==1 meaning, if value is to be update call the update attribute query
                                                if(flag==1&&user[i].segment[key] <= 1000000000){
                                                    //make query
                                                    let UpdateAttributeDetailQuery ="update " + attributeTable + " set " + column + " = " + user[i].segment[key] + " where id = " + attributeKey[0].id;
                                                    let updateAttribute = await updateAttributeDetails(UpdateAttributeDetailQuery)   
                                                }
                                            } 
                                            //if datatype is float, compare with min and max and update accordingly
                                            else if(datatype=='float'){
                                                flag =0;
                                                if(user[i].segment[key]>attributeKey[0].floatmin&&user[i].segment[key]>attributeKey[0].floatmax)
                                                {
                                                    column = 'floatmax'
                                                    flag = 1;
                                                }    
                                                else if(user[i].segment[key]<attributeKey[0].floatmin)
                                                {
                                                    column = 'floatmin'
                                                    flag = 1;
                                                }                          
                                                if( user[i].segment[key] > 1000000000)
                                                {
                                                    user[i].segment[key] = 1000000000
                                                }
                                                //flag ==1 meaning, if value is to be update call the update attribute query
                                                if(flag==1&&user[i].segment[key] <= 1000000000){
                                                    //make query
                                                    let UpdateAttributeDetailQuery ="update " + attributeTable + " set " + column + " = " + user[i].segment[key] + " where id = " + attributeKey[0].id;
                                                    let updateAttribute = await updateAttributeDetails(UpdateAttributeDetailQuery)   
                                                }
                                                 
                                            }
                                            let index = eventStringArray.indexOf("E"+pdid)
                                            eventStringArray.insert(index, "A"+ attributeKey[0].id)
                                        }
                                        //value cannot be empty or null
                                        if(user[i].segment[key]!=''&&user[i].segment[key]!=null){
                                            //if datatype is string, insert value in attribute value table, else append as it is
                                            if(datatype == 'string'|| datatype=='boolean'){ 
                                                //check if value already exists
                                                // let attributeValue = AttributeValueArray.filter(function(v, j) {
                                                //   return (v.value == user[i].segment[key] && v.attributeid == attributeKey[0].id);
                                                // // })
                                                // const now11 = new Date()
                                                // var time11 = Math.round(now11.getTime())
                                                // let attributeValue = AttributeValueArray.filter(function(v, j) {
                                                //   return (v.value == user[i].segment[key] && v.attributeid == attributeKey[0].id);
                                                // })
                                                let attributeValue = attributeValueCache.get(user[i].segment[key]+'_'+ attributeKey[0].id);
                                                // const now12 = new Date()
                                                // var time12 = Math.round(now12.getTime())
                                                 
                                                //make value object
                                                var valueObject = {
                                                    "id" : 1,
                                                    "attributeid" : attributeKey[0].id,
                                                    "value" : user[i].segment[key],
                                                    "frequency" : 1
                                                }
                                                //if attribute Value doesnt exists
                                                if(attributeValue == undefined){
                                                    //insert in attribute value table
                                                    let attributeValueInsertResponse = await insertInAttributeValueTable(attributeValueTable, attributeKey[0].id , user[i].segment[key], 1)
                                                    //get id in response
                                                    attributeValues= attributeValueInsertResponse[0]  
                                                    if(attributeValues!=undefined){
                                                        //update value object
                                                        valueObject.id = attributeValues[0].id
                                                        attributeValueCache.set(user[i].segment[key]+'_'+ attributeKey[0].id,attributeValues[0].id);

                                                        attributeValue = attributeValues[0].id
                                                        //push this object to local
                                                        // AttributeValueArray.push(valueObject)    
                                                    }  
                                                }
                                                // else{
                                                //     //if entry already exists, update frequency
                                                //     let updatefrequency = await updateAttributeValueDetails(attributeValueTable, attributeKey[0].id , user[i].segment[key]) 
                                                //     valueObject.frequency = valueObject.frequency + 1
                                                // } 
                                                if(attributeValue!=undefined){
                                                    if(attributeValue!= null&&attributeValue!='null'){
                                                        let index = eventStringArray.indexOf("E"+pdid)
                                                        eventStringArray.insert(index, "V"+ attributeValue)  
                                                    }
                                                }
                                                
                                                
                                            }
                                            //if datatype is int or float, add as it is to eventstring
                                            else if(datatype=='number'||datatype=='int'||datatype=='float'){
                                                if(user[i].segment[key]!=null&&user[i].segment[key]!='null'){
                                                    if(datatype=='float'){
                                                        if(parseFloat(user[i].segment[key])){
                                                            let temp = parseFloat(user[i].segment[key]).toString();
                                                            let index = eventStringArray.indexOf("E"+pdid)
                                                            eventStringArray.insert(index, "V"+ temp)    
                                                        }
                                                    }
                                                    else{
                                                        let index = eventStringArray.indexOf("E"+pdid)
                                                        eventStringArray.insert(index, "V"+ user[i].segment[key]) 
                                                    }
                                                        
                                                }
                                            }
                                            //if datatype is datetime, add that to eventstring
                                            else if(!isNaN(valueDateTime)){
                                                if(datetime!=null&&datetime!='null'){
                                                    let index = eventStringArray.indexOf("E"+pdid)
                                                    eventStringArray.insert(index, "V"+ datetimeValue)    
                                                }
                                                
                                            }
                                        }
                                    }
                                }
                            }        
                        }
                    }
                }
            }
        }
        // const now6 = new Date()
        // var time6 = Math.round(now6.getTime())
         
        //if session already existed, then session length is to be updated
        if(updateSession == 1&&sessionid!=undefined){
            let UpdateUserSessionLength = "Update "+ sessionTable + " set sessionlength = "+ endtime +"-"+ starttime+" where did = '"+ sessionid[0].did +"' and sid = '"+ sessionid[0].sid +"';"
            UpdateUserSessionLength = UpdateUserSessionLength.replace(/\\/g, '');
            //update the string for batch update
            finalUpdateSessionLength = finalUpdateSessionLength +UpdateUserSessionLength;   
        }
            

        //if eventstring Array is not null
        if(eventStringArray.length>0){
            //store the current array in local
            finalArray[user[0].did] = eventStringArray
            //final eventstring which is to be inserted in db
            finaleventstring =  eventStringArray.join('');
            finaleventstring = finaleventstring.replace(/null/, "");
            //update call for updating event string
            if(user.length>0&&user[0].did!=undefined&&user[0].did!='undefined'){
                //make upsert query for eventstring
                let UpsertUserDetailQuery = "insert into "+ eventStringTable + "(did, "+ eventStringCurrentColumn +") values('"+ user[0].did +"', '"+ finaleventstring +"') on conflict(did) do update set "+ eventStringCurrentColumn +" = '"+finaleventstring+"' where "+ eventStringTable +".did = '"+ user[0].did +"';"
                UpsertUserDetailQuery = UpsertUserDetailQuery.replace(/\\/g, '');
                finalUpdateString = finalUpdateString + UpsertUserDetailQuery;                        
            }
        }
        // if batch is completed and last user's eventstring is computed, then do batch processing
        if(iter == arraylength - 1){
            await insertInSessionTable(finalSessionString);
            if(finalUpdateSessionLength!=''){
                updateSessionLength(finalUpdateSessionLength);
            }
            upsertAppUsersDataBatch(finalUpdateString); 
        }

        return iter;
    }


    //procces the cron 
    let processEventStringCron = async function(events, uniqueUser, appEventsData, appUsersTable, sessionTable, attributeTable, attributeValueTable, eventStringTable, eventStringColumn, eventStringCurrentColumn, appEventTable){
        let uniqueUserArray = Array.from(uniqueUser[0]);
        logger.info(`unique User ARray Length => ${uniqueUserArray.length}`);
        logger.info(`event Length => ${events.length}`);
        var arrayLength = uniqueUserArray.length 
        for(let i =0;i < uniqueUserArray.length; i++){
            let usersEvents = _.filter(events,{did_sid:uniqueUserArray[i]})
            //sort them on the basis of event time
            usersEvents.sort(function(a, b){return a.eventtime - b.eventtime});  
            //prepare the string once we have the events sorted accordingly
            var result = await prepareEventString(i, arrayLength, usersEvents, appEventsData, appUsersTable, sessionTable, attributeTable, attributeValueTable, eventStringTable, eventStringColumn, eventStringCurrentColumn, appEventTable);
            if(result==uniqueUserArray.length-1){
                 
                return "success";
            }
        }

    }

    //fetch data from events table
    let fetchDataForEventStringCron = async function(offset, eventTable) {
        let EventCronquery = "select did,sid,key,platform as p,dt,segment,eventtime from "+ eventTable +"  offset "+ offset +" limit 20000";
        let result = await pgsql.executeQuery(EventCronquery)
            .then((result) => {
                     
                    return result;
                       
            })
            .catch((error)=>{
                logger.error(`error in fetching events => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //fetch existing attribute details
    let fetchAttributeValueDetails = async function(attributeValueTable) {
        let FetchAttributeValueDetailQuery = "select * from "+ attributeValueTable ;        
        let result = await pgsql.executeQuery(FetchAttributeValueDetailQuery)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in fetching attribute detail => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //fetch existing attribute details
    let fetchAttributeDetails = async function(attributeTable) {
        let FetchAttributeDetailQuery = "select * from "+ attributeTable;
        let result = await pgsql.executeQuery(FetchAttributeDetailQuery)
            .then((result) => {
                 
                return result;   
            })
            .catch((error)=>{
                logger.error(`error in fetching attribute detail => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //fetch app events data
    let fetchAppEventsData = async function(appEventTable) {
        let AppEventsQuery = "select DISTINCT name,id from "+ appEventTable ;
        let result = await pgsql.executeQuery(AppEventsQuery)
            .then((result) => {
                    return result;   
            })
            .catch((error)=>{
                logger.error(`error => ${error}`);
                process.exit()
                let result = [];
                return result;
            })
        return result;    
    }

    //create event table for specific date
    var createEventTable = async function(currentdate, tablename, EventTable){
        var createEventTableQuery = "create table if not exists "+ tablename +" as select key,eventTime,did,sid,utime, p as platform,dt,segment from "+ EventTable +" where dt = date '"+ currentdate +"' and sid!='null' and key NOT IN ('App_Background', 'App_Foreground');"
        createEventTableQuery = createEventTableQuery.replace(/\\/g, '');
         var result = await pgsql.executeQuery(createEventTableQuery)
            .then((result) => {
                    return result;   
            })
            .catch((error)=>{
                logger.error(`error => ${error}`);
                result = [];
            })
        return result; 
    }

    eventString.eventStringCron = async function(params){
        logger.info(`params => ${params}`);
        let eventTable = params.eventTable //event table name
        let appEventTable = params.appEventTable //app events table name
        let appUsersTable = params.appUsersTable  // app users table name
        let sessionTable = params.sessionTable  //sessions table name
        let attributeTable = params.attributeTable //attribute name table
        let attributeValueTable = params.attributeValueTable //attribute value table 
        let appEventDataFethched = false // check whether app events data is fetched or not
        let attributeDataFetched = false // check whether attribute data is fetched or not
        let attributeValueDataFetched = false // check whether attribute value data is fetched or not
        let eventStringTable = params.eventStringTable //event string table
        let eventStringColumn = params.eventStringColumn // event string column for day map
        let eventStringCurrentColumn = params.eventStringCurrentColumn //current column in which eventstring is to be stored
        let tableLength = params.tablelength //length of the event table
        let count = 0 //offset 
        let eventPartition = params.eventPartition
        let date = params.date

        let createTable = await createEventTable(date, eventTable, eventPartition);

        const now4 = new Date()
        var time4= Math.round(now4.getTime())



        //infinite loop till the terminating condition
        while(1){
            //if app events data is not fetched
            if(appEventDataFethched == false){
                //wait for results from appevents table
                let appEvents = await fetchAppEventsData(appEventTable);
                if(appEvents.length>0){
                    appEventsData = []
                    appEventsData = appEvents[0]
                    //so that this data is not fetched again and again
                    appEventDataFethched = true
                }
            }
            if(attributeDataFetched == false){
                //wait for results from appevents table
                let attributeData = await fetchAttributeDetails(attributeTable);
                if(attributeData.length>0){
                    AttributeArray = []
                    AttributeArray = attributeData[0]; 
                    //so that this data is not fetched again and again
                    attributeDataFetched = true       
                }

            }

            // //fetch attribute value data if not exists
            if(attributeValueDataFetched == false){
                //wait for results from attribute table
                let attributeValueData = await fetchAttributeValueDetails(attributeValueTable);
                if(attributeValueData.length>0){
                    let attributevaluesarray = attributeValueData[0]
                    for(let i = 0; i < attributevaluesarray.length; i++){
                        attributeValueCache.set(attributevaluesarray[i].value+'_'+ attributevaluesarray[i].attributeid ,attributevaluesarray[i].id);    
                    }

                    //so that this data is not fetched again and again   
                    attributeValueDataFetched = true   
                }          
            }
            //wait for result from events table so that events can be fetched in batches
            let result = await fetchDataForEventStringCron(count, eventTable)
            let finalResult = null
            //add a column merging did and sid
            finalResult = result[0].map(function(el) {
                var o = Object.assign({}, el);
                o.did_sid = el.did+'_'+el.sid;
                return o;
            })

            if(finalResult.length==0){
                 
                break;
            }

            // get all unique users with respective sessions
            let uniqueUser = [new Set(finalResult.map(item => item.did_sid))];
            //wait for this batch to process
            const now4 = new Date()
            var time4= Math.round(now4.getTime())
            var result1 = await processEventStringCron(finalResult, uniqueUser, appEventsData, appUsersTable, sessionTable, attributeTable, attributeValueTable, eventStringTable, eventStringColumn, eventStringCurrentColumn, appEventTable);
            
            //if result returns success, meaning the batch is fully operated, then we can move to next batch
            if(result1 == "success"){
                //if the eventtime is not more than midnight of same day, continue with the batches 
                count = count + 20000            
            }
            else{                
                logger.info(`BREAK`)
                break;
                // procces.exit();
            }

            result = null;
        }   
        const now5 = new Date()
        var time5 = Math.round(now5.getTime())
        logger.info(`one batch time  => ${time5-time4}`);
        process.exit();
        // await insertInSessionTable(finalSessionString);
        // upsertAppUsersDataBatch(finalUpdateString); 
    }
}(eventString))

// eventString.eventStringCron();

module.exports = eventString;
