var events = {},
    common = require('./../../utils/common.js'),
    async = require('./../../utils/async.min.js'),
    semusiConfig = require('./../../config.js'),
    logger = require('../../../logger'),
    _ = require('underscore'),
    //client = require('node-rest-client').Client,
    //proxy = new client(),
    json2csv = require('./../../utils/json-2-csv.js'),
    cacheApi = require('./../../utils/semusi.cache.js'),
    mail = require('../mgmt/mail'),
    aws = require('aws-sdk'),
    Promise = require('bluebird'),
    moment = require("moment"),
    { execFun } = require('child_process'),
    converter = require('json-2-csv'),
    pgsqlUtils = require('./../../utils/pgsql.utils'),
    fs = require('fs'),
    Azure = require('azure-storage');

(function (events) {
    AWS.config.region = semusiConfig.AWS_REGION;
    // set aws access key and secret keys
    AWS.config.update({ accessKeyId: common.config.AWSLAMBDA_ACCESS_KEY_ID , secretAccessKey: common.config.AWSLAMBDA_SECRET_KEY});
    var S3 = new AWS.S3();

    //This function processes events recieved from client devices.
    events.processEvents = function (params) {
        if (params.qstring.events) {
            for (var i = 0; i < params.qstring.events.length; i++) {
                var currentEvent = {};
                var segmentList = [];
                currentEvent.did = params.qstring.device_id;
                currentEvent.key = params.qstring.events[i].key;
                currentEvent.eventTime = params.qstring.events[i].timestamp;
                currentEvent.segment = (params.qstring.events[i].segmentation !== undefined) ? params.qstring.events[i].segmentation : {};
                currentEvent.attributes = createSegmentCopy(currentEvent.segment);
                currentEvent.context = (params.qstring.events[i].context !== undefined) ? params.qstring.events[i].context : {};

                //Save event
                if (currentEvent.key && currentEvent.key != '' && currentEvent.did && currentEvent.did != '') {
                    currentEvent['createdAt'] = common.getCurrentEpochTime();
                    common.db.collection('events_' + params.app_id).insert(currentEvent, function (err, result) {
                        //Update last event to app users collection.
                        var updatedAt = common.getCurrentEpochTime();
                        common.db.collection('app_users' + params.app_id).update({ _id: params.app_user_id }, { "$set": { "le": { k: currentEvent.key, t: currentEvent.eventTime, s: currentEvent.segment, c: currentEvent.context } }, 'updatedAt': updatedAt }, { safe: true }, function (err, res) { });
                    });

                    //Save event segments.
                    processEventSegments(params, currentEvent.key, currentEvent.segment);
                }
            }
            common.returnMessage(params, 200, 'Success');

        }
        else {
            common.returnMessage(params, 500, 'Failure');
        }

    };

    events.getEventsStats = function (params) {
        var argProps = {
            'startDate': { 'required': true, 'type': 'Number' },
            'endDate': { 'required': true, 'type': 'Number' },
            'startDateNew': { 'required': true, 'type': 'String' },
            'endDateNew': { 'required': true, 'type': 'String' }
        },
            eventParams = {};

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

        var query = {};
        var array = [];
        if (params.qstring.isChat) {
            common.fillEventQueryObject(query, array, eventParams.startDateNew, eventParams.endDateNew, 'facebook', params.qstring.app_id);
        }
        else {
            common.fillEventQueryObject(query, array, eventParams.startDateNew, eventParams.endDateNew, 'android', params.qstring.app_id);
            common.fillEventQueryObject(query, array, eventParams.startDateNew, eventParams.endDateNew, 'ios', params.qstring.app_id);
            common.fillEventQueryObject(query, array, eventParams.startDateNew, eventParams.endDateNew, 'web', params.qstring.app_id);

        }

        common.db.collection('timely_events').find({ _id: { $in: array } }, query).toArray(function (error, result) {
            if (!error) {
                if (result) {
                    common.returnOutput(params, result);
                }
                else {
                    common.returnOutput(params, {});
                }
            }
            else {
                logger.error('error::' + JSON.stringify(error));
            }
        });
    }

    // Get event attr stats
    events.getEventAttributeStats = function (params) {
        var argProps = {
            'startDate': { 'required': true, 'type': 'Number' },
            'endDate': { 'required': true, 'type': 'Number' },
            'startDateNew': { 'required': true, 'type': 'String' },
            'endDateNew': { 'required': true, 'type': 'String' },
            'eventName': { 'required': true, 'type': 'String' },
            'attributeName': { 'required': true, 'type': 'String' },
            'startDateNew': { 'required': true, 'type': 'String' },
            'endDateNew': { 'required': true, 'type': 'String' }
        },
            eventParams = {};

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

        var query = {};
        var array = [];
        common.fillEventAttributeQueryObject(query, array, eventParams.startDateNew, eventParams.endDateNew, 'android', params.qstring.app_id, eventParams.eventName, eventParams.attributeName);
        common.fillEventAttributeQueryObject(query, array, eventParams.startDateNew, eventParams.endDateNew, 'ios', params.qstring.app_id, eventParams.eventName, eventParams.attributeName);
        common.fillEventAttributeQueryObject(query, array, eventParams.startDateNew, eventParams.endDateNew, 'web', params.qstring.app_id, eventParams.eventName, eventParams.attributeName);

        common.db.collection('timely_eventAttributes' + params.qstring.app_id).find({ _id: { $in: array } }, query).toArray(function (error, result) {
            if (!error) {

                if (result) {

                    common.returnOutput(params, result);
                }
                else {
                    common.returnOutput(params, {});
                }
            }
            else {
                logger.error('error::' + JSON.stringify(error));
            }
        });
    }

    // timely event
    events.timelyEventWithPlatformOld = function (query, data) {
        var result = [];
        var _tmp_events = [];

        var keys = Object.keys(query);
        if (keys.length > 0) {
            keys.forEach(function (value) {
                if (value) {
                    data.forEach(function (events) {
                        var args = value.split(".");
                        if (events[args[0]] != undefined) {
                            if (events[args[0]].hasOwnProperty(args[1])) {
                                var _e_list = events[args[0]][args[1]];
                                var _eventName = Object.keys(_e_list);
                                _eventName.forEach(function (_e_name) {
                                    if (_e_name != "total") {
                                        var newVal = args[0] + '.' + _e_name;
                                        if (_tmp_events.indexOf(newVal) === -1) {
                                            _tmp_events.push(newVal);
                                            var obj = {
                                                _id: {
                                                    key: _e_name,
                                                    p: args[0]
                                                },
                                                totalEvents: 0,
                                                key: _e_name,
                                                p: args[0]
                                            };

                                            result.push(obj);
                                        }
                                        var index = _tmp_events.indexOf(newVal);
                                        result[index].totalEvents += _e_list[_e_name];
                                    }
                                });
                            }
                        }
                    });
                }
            });
        }

        return result;
    }

    events.getEventList = function (params) {
        common.db.collection('eventsegments').aggregate([{$match:{"_id":common.db.ObjectID(params.qstring.app_id)}},{$unwind:'$events'},{$sort:{'events.event':1}},{$group:{_id:'$_id','events':{$push:'$events'}}}],{ allowDiskUse: true },function(err,result){
            if (err) {
                logger.error(err);
                common.returnOutput(params, []);
            }
            else {
                // result is comming as array for new Query which gives Elements in Sorted Order
                result= result[0];
                if (result && result.events) {
                    var eventsList = [];
                    var campaignEvents = [common.campaignStats.viewed, common.campaignStats.deleted];
                    //restrict campaign events
                    result.events.forEach(function (item) {
                        if(item.isDisplay == undefined){ item.isDisplay = true; }
                        if(campaignEvents.indexOf(item.event) == -1 && item.isDisplay === true) {
                            eventsList.push(item)
                        }
                    });

                    prepareEventSegmentsList(eventsList, function (data) {
                        common.returnOutput(params, { data: data, clist: result.clist });
                    });
                }
                else {
                    common.returnOutput(params, []);
                }

            }
        });
    };

    // prepare events segments list
    function prepareEventSegmentsList(data, callback) {
        data.forEach(function (item, index) {
            if (item.list) {
                item.list.forEach(function (eventAttribute, key) {
                    if (eventAttribute.type == "object") {
                        data[index].list.splice(key, 1);
                    }
                });
            }

        });
        callback(data);
    }

    events.createFunnel = function (params) {
        var argProps = {
            'events': { 'required': true, 'type': 'JSON' },
            'name': { 'required': true, 'type': 'String' },
            'description': { 'required': false, 'type': 'String' },
            'duration': { 'required': true, 'type': 'String' },
            'unit': { 'required': true, 'type': 'String' },
            'customunit': { 'required': false, 'type': 'String' }
        },
            funnel = {};


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

        funnel.createdOn = common.getCurrentEpochTime();
        funnel.modifiedOn = common.getCurrentEpochTime();
        funnel.deleted = false;
        /*funnel['createdAt'] = common.getCurrentEpochTime();*/
        common.db.collection('funnels_' + params.qstring.app_id).insert(funnel, function (err, res) {
            if (err) {
                common.returnOutput(params, 500, "Error");
            }
            else {
                common.returnOutput(params, res);
            }
        });

    }

    events.updateFunnel = function (params) {
        var argProps = {
            'events': { 'required': true, 'type': 'JSON' },
            'name': { 'required': true, 'type': 'String' },
            'description': { 'required': false, 'type': 'String' },
            'duration': { 'required': true, 'type': 'String' },
            'unit': { 'required': true, 'type': 'String' },
            'customunit': { 'required': false, 'type': 'String' }
        },
            funnel = {};


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

        funnel.modifiedOn = common.getCurrentEpochTime();
        common.db.collection('funnels_' + params.qstring.app_id).update({ _id: common.db.ObjectID(params.qstring.id) }, { $set: funnel }, function (err, res) {
            if (err) {
                common.returnOutput(params, 500, "Error");
            }
            else {
                common.returnOutput(params, 200, "Success");
            }

        });

    }

    events.deleteFunnel = function (params) {
        var funnel = {};
        funnel.modifiedOn = common.getCurrentEpochTime();
        funnel.deleted = true;
        common.db.collection('funnels_' + params.qstring.app_id).update({ _id: common.db.ObjectID(params.qstring.id) }, { $set: funnel }, function (err, res) {
            if (err) {
                common.returnOutput(params, 500, "Error");
            }
            else {
                common.returnOutput(params, 200, "Success");
            }
        });

    }

    events.getEventFunnels = function (params) {
        common.db.collection('funnels_' + params.qstring.app_id).find({ deleted: false }).sort({ createdOn: -1 }).toArray(function (err, res) {
            common.returnOutput(params, res);
        });
    }

    function epocToDate(epoc){
        var dateVal ="/Date("+epoc+")/";
        var date = new Date( parseFloat( dateVal.substr(6 )));
        return(date.getFullYear() + "-" +(date.getMonth()+1)   + "-" +  date.getDate());
    }

    // new get funnel with PG
    events.getEventFunnelStats = function (params) {
        var funnel = params.qstring.args;
        var epochtimeStamp = "";      
        //Put Filter on the Basis of UI Condition 
        switch (funnel.unit) {
            case "Today":
                epochtimeStamp = moment().subtract(1, 'days').unix()*1000;
                epochlastTimeStamp  = moment().add(2, 'days').unix()*1000;
            break;
            case "Yesterday":
                epochtimeStamp = moment().subtract(2, 'days').unix()*1000;
                epochlastTimeStamp  = moment().add(1, 'days').unix()*1000;
            break;
            case "This Week":
                epochtimeStamp = moment().startOf('week').unix()*1000;
                epochlastTimeStamp  = parseInt(params.qstring.offset)*1000;
            break;
            case "Last Week":   
                epochtimeStamp =moment().subtract(1, 'weeks').startOf('isoWeek').unix()*1000;
                epochlastTimeStamp  = moment().subtract(1, 'weeks').endOf('isoWeek').unix()*1000;
            break;
            case "This Month":
                epochtimeStamp = moment().startOf('month').unix()*1000;
                epochlastTimeStamp  = parseInt(params.qstring.offset)*1000;
            break;
            case "Last Month":
                epochtimeStamp =moment().subtract(1, 'months').startOf('isoMonth').unix()*1000;
                epochlastTimeStamp  = moment().subtract(1, 'months').endOf('isoMonth').unix()*1000;
            break;
        }
        var eventsData = {};
        var events = [];
        var timeStamp=epocToDate(epochtimeStamp);
        var lastTimeStamp=epocToDate(epochlastTimeStamp);
        //get Formatted Date
        if(funnel.unit=='Last Month'){
            if(funnel.events.length==5){
                // PG query
                var sqlData = "WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15'), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s2 ON s1.did = s2.did WHERE s1.eventtime < s2.eventtime), step3 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[2].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s2 ON ( s1.did = s2.did ) inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s3 ON ( s2.did = s3.did ) WHERE s1.eventtime < s2.eventtime AND s2.dt < date s3.eventtime), step4 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[3].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s2 ON ( s1.did = s2.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s3 ON ( s2.did = s3.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[3].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s4 ON ( s3.did = s4.did ) WHERE s1.eventtime < s2.eventtime AND s2.dt < date s3.eventtime AND s3.dt < date s4.eventtime), step5 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[4].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s2 ON ( s1.did = s2.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s3 ON ( s2.did = s3.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[3].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s4 ON ( s3.did = s4.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[4].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s5 ON ( s4.did = s5.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime AND s3.eventtime < s4.eventtime AND s4.eventtime < s5.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + ", step3." + funnel.events[2].eventName + ", step4." + funnel.events[3].eventName + ", step5." + funnel.events[4].eventName + " FROM step1, step2, step3, step4,step5" ;
            }else if(funnel.events.length==4){
                var sqlData = "WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s2 ON s1.did = s2.did WHERE s1.eventtime < s2.eventtime), step3 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[2].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s2 ON ( s1.did = s2.did ) inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s3 ON ( s2.did = s3.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime), step4 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[3].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s2 ON ( s1.did = s2.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s3 ON ( s2.did = s3.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[3].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s4 ON ( s3.did = s4.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime AND s3.eventtime < s4.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + ", step3." + funnel.events[2].eventName + ", step4." + funnel.events[3].eventName + " FROM step1, step2, step3, step4" ;
            }else if(funnel.events.length==3){
                var sqlData = "WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s2 ON s1.did = s2.did WHERE s1.eventtime < s2.eventtime), step3 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[2].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s2 ON ( s1.did = s2.did ) inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15' ) s3 ON ( s2.did = s3.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + ", step3." + funnel.events[2].eventName + " FROM step1, step2, step3" ;
            }else if(funnel.events.length==2){
                var sqlData ="WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15'), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15') s2 ON s1.did = s2.did WHERE  s1.eventtime < s2.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + " FROM step1, step2";
            }else if(funnel.events.length==1){
                var sqlData="SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '2020-06-01' and dt < date '2020-06-15'";
            }else{
            }
        }else{
            if(funnel.events.length==5){
                // PG query
                var sqlData = "WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "'), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s2 ON s1.did = s2.did WHERE s1.eventtime < s2.eventtime), step3 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[2].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s2 ON ( s1.did = s2.did ) inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s3 ON ( s2.did = s3.did ) WHERE s1.eventtime < s2.eventtime AND s2.dt < date s3.eventtime), step4 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[3].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s2 ON ( s1.did = s2.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s3 ON ( s2.did = s3.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[3].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s4 ON ( s3.did = s4.did ) WHERE s1.eventtime < s2.eventtime AND s2.dt < date s3.eventtime AND s3.dt < date s4.eventtime), step5 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[4].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s2 ON ( s1.did = s2.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s3 ON ( s2.did = s3.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[3].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s4 ON ( s3.did = s4.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[4].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s5 ON ( s4.did = s5.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime AND s3.eventtime < s4.eventtime AND s4.eventtime < s5.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + ", step3." + funnel.events[2].eventName + ", step4." + funnel.events[3].eventName + ", step5." + funnel.events[4].eventName + " FROM step1, step2, step3, step4,step5" ;
            }else if(funnel.events.length==4){
                var sqlData = "WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s2 ON s1.did = s2.did WHERE s1.eventtime < s2.eventtime), step3 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[2].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s2 ON ( s1.did = s2.did ) inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s3 ON ( s2.did = s3.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime), step4 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[3].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s2 ON ( s1.did = s2.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s3 ON ( s2.did = s3.did ) INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[3].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s4 ON ( s3.did = s4.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime AND s3.eventtime < s4.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + ", step3." + funnel.events[2].eventName + ", step4." + funnel.events[3].eventName + " FROM step1, step2, step3, step4" ;
            }else if(funnel.events.length==3){
                var sqlData = "WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s2 ON s1.did = s2.did WHERE s1.eventtime < s2.eventtime), step3 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[2].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s1 inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s2 ON ( s1.did = s2.did ) inner join (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[2].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "' ) s3 ON ( s2.did = s3.did ) WHERE  s1.eventtime < s2.eventtime AND s2.eventtime < s3.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + ", step3." + funnel.events[2].eventName + " FROM step1, step2, step3" ;
            }else if(funnel.events.length==2){
                var sqlData ="WITH step1 AS (SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "'), step2 AS (SELECT Count(DISTINCT s1.did) AS " + funnel.events[1].eventName + " FROM (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s1 INNER JOIN (SELECT did, eventtime FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[1].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "') s2 ON s1.did = s2.did WHERE  s1.eventtime < s2.eventtime) SELECT step1." + funnel.events[0].eventName + ", step2." + funnel.events[1].eventName + " FROM step1, step2";
            }else if(funnel.events.length==1){
                var sqlData="SELECT Count(DISTINCT did) AS " + funnel.events[0].eventName + " FROM events_" + params.qstring.app_id + " WHERE KEY = '" + funnel.events[0].eventName + "' AND dt > date '" + timeStamp + "' and dt < date '" + lastTimeStamp + "'";
            }else{
            }
        }
        logger.info(`sql => ${sqlData}`);
        // Run pg query
        pgsql.executeQuery(sqlData)
                .then((result) =>{
                    if(result){
                        logger.info("Query Result => ",JSON.stringify(result[0][0].count))
                        // eventsData.events = events;
                        // eventsData.data = '{"markets":"104353"}';
                        let eventsData ={"events":[],'data':'{"markets":50909}'};
                        return common.returnOutput(params, eventsData);
                    }
                    else{
                        logger.error(`event count reach error => ${error}`);
                        eventsData.events = events;
                        eventsData.data = 105400;
                        return common.returnOutput(params, eventsData);
                    }
        })
        .catch((error) =>{
            logger.error(`error : ${JSON.stringify(error)}`);
            common.returnMessage(params, 200, "Failed");
        })
    }



    events.downloadEventData = function(params){
         var argProps = {
                 'filter':{'required':true,'type':'JSON'},
             },filterObj={};

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

         var csvData = "";
         // keen variables
         var keenQueries = [];
         var filters = [];
         //construct match.
         var match = {key:{$ne:'_app_crash'}};
         if(filterObj.filter.dateRange){
             match["eventTime"] = {$gte:filterObj.filter.dateRange.sd,$lte:filterObj.filter.dateRange.ed};
             // keen timeframe Date
             start = moment(filterObj.filter.dateRange.sd*1000).toDate();
             end = moment(filterObj.filter.dateRange.ed*1000).toDate();

             filterObj.filter.events.forEach(function(event){
                  event_collection: "event_"+params.qstring.app_id+"_"+event
             });
         }

         if(filterObj.filter.platform =="android"){
             match["context.who.device.p"] = "Android";
             filters.push({
                 operator:'eq',
                 property_name:'context.who.device.p',
                 property_value:'Android'
             });
         }
         else if(filterObj.filter.platform =="ios"){
             match["context.who.device.p"] = "IOS";
             filters.push({
                 operator:'eq',
                 property_name:'context.who.device.p',
                 property_value:'IOS'
             });
         }

         if(!filterObj.filter.selectAll){
             match["key"] = {"$in":filterObj.filter.events};
             // prepare query for keen
             filterObj.filter.events.forEach(function(event){
                  var obj = {
                       filters: filters,
                       timeframe: {
                           start: start,
                           end: end
                        },
                        timezone: null,
                        event_collection: "event_"+params.qstring.app_id+"_"+event
                 }
                 // prepare query for extraction
                 keenQueries.push(keen.extractionQuery(obj));
             });
         }

         if(keenQueries.length > 0){
             keen.runQuery(keenQueries, function(error, response){
                 var eventData = [];
                 if(error){
                     logger.error(error);
                     common.returnOutput(params,{"result":"Download data exceeds data limit size, please try with smaller date range."});
                     return false;
                 }
                 else{
                     if(response){
                         // single event
                         if(response.result){
                           eventData.push(response)
                         }
                         else{ // multiple events
                           eventData = response;
                         }

                         var eventDataObj = [];
                         eventData.forEach(function(res){
                             res.result.forEach(function(item){
                                 var platform="";
                                 var appVersion = "";
                                 var sdkVersion = "";
                                 var device = "";
                                 var source = "";
                                 var pv = "";
                                 if(item.context && item.context.who && item.context.who.refname){
                                     source = item.context.who.refname;
                                 }
                                 if(item.context && item.context.who && item.context.who.device && item.context.who.device.p){
                                     platform = item.context.who.device.p;
                                 }
                                 if(item.context && item.context.who && item.context.who.device && item.context.who.device.d){
                                     device = item.context.who.device.d;
                                 }
                                 if(item.context && item.context.who && item.context.who.device && item.context.who.device.av){
                                     appVersion = item.context.who.device.av;
                                 }
                                 if(item.context && item.context.who && item.context.who.device && item.context.who.device.pv){
                                     pv = item.context.who.device.pv;
                                 }
                                 if(item.context && item.context.who && item.context.who.device &&item.context.who.device.sdkv){
                                     sdkVersion = item.context.who.device.sdkv;
                                 }

                                 if(filterObj.filter.dateRange.dates.indexOf(moment(item.eventTime*1000).format('YYYY-MM-DD')) != -1){
                                     var dateObj = common.getEpochToDateWithTime(item.eventTime);
                                     var eventObj = {
                                             EpochTime:item.mtimestamp,
                                             EventDate:dateObj.split(' ')[0],
                                             EventTime:dateObj.split(' ')[1],
                                             Event:item.key,
                                             DeviceId:item.did,
                                             Device:device,
                                             Platform:platform,
                                             Source:source,
                                             AppVersion:appVersion,
                                             SdkVersion:pv,
                                             EventData:formatEventData(item),
                                             Segment:item.segment
                                     }
                                     eventDataObj.push(eventObj);
                                  }
                             });
                         });

                         var CSVDATA = json2csv.convert2csv(eventDataObj, false).csv;
                         common.returnCSV(params,CSVDATA);
                     }
                     else{
                         common.returnOutput(params,{"result":"Sorry event data is not found!"});
                         return false;
                     }
                 }
             });
         }
         else{
             common.returnOutput(params,{"result":"Sorry event data is not found!"});
             return false;
         }
     };


     events.downloadEventData = function(params){
          var argProps = {
                  'filter':{'required':true,'type':'JSON'},
              },filterObj={};

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

          if(params.qstring.offset){
              if(params.qstring.offset.indexOf('+')){
                  params.qstring.offset = params.qstring.offset.replace('+','');
                  filterObj.filter.dateRange.sd = parseInt(filterObj.filter.dateRange.sd) + parseInt(params.qstring.offset)
              }
              else {
                  params.qstring.offset = params.qstring.offset.replace('-','');
                  filterObj.filter.dateRange.sd = parseInt(filterObj.filter.dateRange.sd) - parseInt(params.qstring.offset)
              }
          }

          // get registered email address of client
          cacheApi.getKey('csvemail_'+params.qstring.app_id, function(error, email){
              // if(!error && email != null){
              //     filterObj.emailTo = email;
              //     getEventDataFromPG(params, filterObj);
              // }
              // else{
                  common.db.collection('members').findOne({api_key:params.qstring.api_key}, function(err, members){
                      if(err){
                          logger.error(`members collection data not found!`);
                          getEventDataFromPG(params, filterObj);
                      }
                      else if(members.email){
                          cacheApi.setKey('csvemail_'+params.qstring.app_id, members.email);
                          filterObj.emailTo = members.email;
                          getEventDataFromPG(params, filterObj);
                      }
                  });
              // }
          });
     };

     events.downloadJourneyData = function(params){

        // get registered email address of client
        cacheApi.getKey('csvemail_'+params.qstring.app_id, function(error, email){
            if(!error && email != null){
                getJourneyData(params);
            }
            else{
                common.db.collection('members').findOne({api_key:params.qstring.api_key}, function(err, members){
                    if(err){
                        logger.error(`members collection data not found!`);
                        getJourneyData(params);
                    }
                    else if(members.email){
                        cacheApi.setKey('csvemail_'+params.qstring.app_id, members.email);
                        getJourneyData(params);
                    }
                });
            }
        });
   };


     function formatEventData(item){
         var data="";
         if(item.segment){
             var obj = item.segment;
             var keys = Object.keys(obj);

             keys.forEach(function(key){
                 if(obj[key] && typeof(obj[key])=='string'){
                     data+=key+":"+obj[key].replace(",","-")+";";
                 }
                 else if(obj[key] && typeof(obj[key]) === 'object'){
                     //data+=key+":"+JSON.stringify(obj[key])+";";
                     if(Array.isArray(obj[key])){
                         for(var i=0; i<obj[key].length; i++){
                             var keys = Object.keys(obj[key][i]);
                             for(var j=0; j<keys.length; j++){
                                 data += keys[j]+(i+1)+":"+obj[key][i][keys[j]]+";";
                             }
                         }
                     }
                 }
                 else if(obj[key] && typeof(obj[key])!='string'){
                     data+=key+":"+obj[key]+";";
                 }
                 else if( obj[key] == '' ){
                      data+=key+" ";
                 }
             });
         }
         return data;
     }

     //Get Journey Data
     function getJourneyData (params){
            var blobContainerCDN;
            blobContainerCDN = common.config.blobContainerCDN+common.config.CSVBUCKET

            const blobService = Azure.createBlobServiceWithSas(common.config.azureApiBlobSASURL, common.config.azureApiStorageSASToken);
            var key = "eventcsv/" + params.qstring.app_id +"_journey.csv";
            logger.info(`sending data to blob`);
            blobService.createBlockBlobFromLocalFile(common.config.CSVBUCKET,
                key,
                common.config.CSVDIR + params.qstring.app_id +"_journey.csv",
                (error, result) => {
                    if(error) {
                        logger.error("blob container csv error=> "+JSON.stringify(error));
                    } else {
                        logger.info('Upload is successful');
                        var message = {
                            to: filterObj.emailTo,
                            bcc: ['saurabh.singh@semusi.com','monika.kaushik@semusi.com','aman.aggarwal@semusi.com'],
                            subject:'Journey data CSV ',
                            html:'Hi,<br/><br/>' +
                                'click given hot link to download event data csv <b><br/><br/>' +
                                '<a href="'+blobContainerCDN+"/"+key+'"> '+blobContainerCDN+"/"+key+'</a> <br/><br/>' +
                                'Appice Team,<br/>' +
                                'Server: <a href="'+common.config.cdnUrl+'" target="_blank">'+common.config.cdnUrl+'</a>'
                        };
                        logger.info('sending mail..')
                        mail.sendMail(message);
                        //sendEventEmail(params, message);
                }
            });
            common.returnOutput(params,{"result":"You request for data extraction is under processing. It will be sent over to your reregistered email id"});
        }

        //Get Event Data
     function getEventDataFromPG (params, filterObj){
        if(filterObj.filter.events && filterObj.filter.events.length > 0){

            var queryFilter = "";
            if(filterObj.filter.platform =="android"){
                queryFilter = `context @> '{"who": {"device":{"p": "Android"}}}' and `;
            }
            else if(filterObj.filter.platform =="ios"){
                queryFilter = `context @> '{"who": {"device":{"p": "IOS"}}}' and `;
            }
            let events = filterObj.filter.events.join("','");
            let fetchEventDataQuery = `SELECT KEY as EventName, to_char(to_timestamp(eventtime), 'DD-MM-YYYY') as EventDate, to_char(to_timestamp(eventtime), 'HH24:MI:SS') as EventTime, eventtime as EpochTime, s1.did as DeviceID, context->'who'->'refname' as Source,context->'who'->'device'->'p' as Platform,context->'who'->'device'->'d' as Device,context->'who'->'device'->'av' as AppVersion,context->'who'->'device'->'pv' as PlatformVersion, context->'who'->'device'->'sdkv' as SDKVersion, segment as Segment, s2._custom_userid as userid from events_`+params.qstring.app_id+` as s1 inner join app_users_`+params.qstring.app_id+` as s2 ON  s1.did= s2.did  where ` +queryFilter+ ` KEY IN ('`+events+`') and dt >= date '`+moment(filterObj.filter.dateRange.sd*1000).format('YYYY-MM-DD')+`' and dt <= date '`+moment(filterObj.filter.dateRange.ed*1000).format('YYYY-MM-DD')+ `' order by eventtime desc`;
            logger.info(`query => ${fetchEventDataQuery}`);
            let eventdataDownloadQuery = "\\COPY ("+fetchEventDataQuery +")  TO PROGRAM 'gzip -c > "+common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename+ ".zip' WITH CSV HEADER";
            try{
                execFun(`psql '`+common.config.postgreSQL.productionCitusURI+`' -c "`+eventdataDownloadQuery+'"', (err, stdout, stderr) => {
                  if (err) {
                     logger.error("err=> "+err);
                  }
                  else {
                        var blobContainerCDN;
                        blobContainerCDN = common.config.blobContainerCDN+common.config.CSVBUCKET
                        const blobService = Azure.createBlobServiceWithSas(common.config.azureApiBlobSASURL, common.config.azureApiStorageSASToken);
                        var key = "eventcsv/" + params.qstring.app_id+"_"+params.qstring.filename+".zip";
                        blobService.createBlockBlobFromLocalFile(common.config.CSVBUCKET,
                            key,
                            common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename+".zip",
                            (error, result) => {
                                if(error) {
                                    logger.error("blob container csv error=> "+JSON.stringify(error));
                                } else {
                                    if (fs.existsSync(common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename)) {
                                        fs.unlinkSync(common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename);
                                    }
                                    var mailDate = (moment(filterObj.filter.dateRange.sd*1000).format('DD-MM-YYYY') != moment(filterObj.filter.dateRange.ed*1000).format('DD-MM-YYYY')) ? 'FROM '+moment(filterObj.filter.dateRange.sd*1000).format('DD-MM-YYYY')+' TO '+ moment(filterObj.filter.dateRange.ed*1000).format('DD-MM-YYYY') : ' Of '+ moment(filterObj.filter.dateRange.ed*1000).format('DD-MM-YYYY');
                                    var message = {
                                        to: filterObj.emailTo,
                                        bcc: ['monika.kaushik@semusi.com','saurabh.singh@semusi.com','tarun.anand@semusi.com'],
                                        subject:'Event data CSV '+mailDate,
                                        html:'Hi,<br/><br/>' +
                                            'Your data download is ready! Please click on the link below to download your data.<br/><br/>' +
                                            '<a href="'+blobContainerCDN+"/"+key+'"> '+blobContainerCDN+"/"+key+'</a> <br/><br/>' +
                                            'Appice Team,<br/>' +
                                            'Server: <a href="'+common.config.cdnUrl+'" target="_blank">'+common.config.cdnUrl+'</a>'
                                    };
                                    mail.sendMail(message);
                            }
                        });
                  }
              });
            }
            catch (e){
                logger.error(e.message);
            }

            if(filterObj.emailTo){
                common.returnOutput(params,{"result":"You request for data extraction is under processing. It will be sent over to your reregistered email id '"+filterObj.emailTo+"'"});
            }
            else {
                common.returnOutput(params,{"result":"Sorry, your email address is not registered with us"});
            }
            //common.returnOutput(params,{"result":"You request for data extraction is under processing. It will be sent over to your reregistered email id with AppICE."});
        }
        else{
            return common.returnOutput(params,{"result":"Please select events"});
        }
    }

    function getValueBasedOnType(type, value) {
        if (type == 'number') {
            return parseFloat(value);
        }
        else if (type = 'string' || type == "regex") {
            //return value.toLowerCase();
            return value;
        }
        else if (type == 'boolean') {
            return Boolean(value);
        }
    }

    /**
     * Validate funnel name
     * @return boolean value in response
     **/
    events.validateFunnelName = function (params) {
        if (params.qstring.funnelName) {
            var name = new RegExp(["^", params.qstring.funnelName, "$"].join(""), "i");
            common.db.collection('funnels_' + params.qstring.app_id).find({ "name": name, "$or": [{ 'deleted': { '$eq': false } }] }).toArray(function (err, result) {
                if (err) {
                    common.returnOutput(params, 500, "Error");
                }
                else {
                    common.returnOutput(params, (result.length == 0) ? false : true);
                }
            });
        }
        else {
            common.returnOutput(params, 500, "Error funnel name not found");
        }
    }

    /**
     *   Show source wise aquistion for all funnel bars
     * @return boolean value in response
     **/
    function sourceWiseAquistion(appId, did, cb) {
        if (did) {

            common.db.collection('events_' + appId).aggregate([
                { $match: { "did": { $in: did } } },
                { $group: { "_id": { "refname": "$context.who.refname" }, "total": { $sum: 1 } } },
                { $project: { "refname": "$_id.refname", "total": 1, "_id": 0 } }
            ]
                , function (err, refname) {
                    if (!err) {
                        var sourceList = [];
                        if (refname) {
                            sourceList = refname;
                        }
                        cb(null, sourceList);
                    }
                    else {
                        cb(err, null);
                    }
                });
        }
    }

    //Maintain a list of events and attributes for each app.
    function processEventSegments(params, key, segment) {
        var segmentList = Object.keys(segment);
        var list = [];

        //Create event attribute list.
        segmentList.forEach(function (item) {
            var obj = {};
            obj.name = item;
            obj.type = typeof (segment[item]);
            list.push(obj);
        });

        common.db.collection('eventsegments').findOne({ _id: params.app_id, "events.event": key }, function (err, result) {
            if (result) {
                var updated = common.getCurrentEpochTime();
                common.db.collection('eventsegments').update({ "_id": params.app_id, "events.event": key }, { $addToSet: { 'events.$.list': { $each: list } }, '$set': { 'updated': updated } }, function (err1, result1) { });
            }
            else if (!err && !result) {
                var createdAt = common.getCurrentEpochTime();
                common.db.collection('eventsegments').update({ "_id": params.app_id }, { $addToSet: { 'events': { "event": key, list: list } }, '$set': { 'createdAt': createdAt } }, { 'upsert': true }, function (err, result) { });
            }
        });

    }

    function createSegmentCopy(segment) {
        var keys = Object.keys(segment);
        var obj = {};
        keys.forEach(function (key) {
            if (typeof (segment[key]) == 'string') {
                obj[key] = segment[key].toLowerCase();
            }
            else {
                obj[key] = segment[key];
            }
        });

        return obj;
    }

    //explore data from keen
    events.getEventExploreData = function (params) {
        //init required property
        var argProps = {
            'analysis_type': { 'required': true, 'type': 'String' },
            'event': { 'required': true, 'type': 'String' },
            'startDate': { 'required': true, 'type': 'String' },
            'startTime': { 'required': true, 'type': 'String' },
            'endDate': { 'required': true, 'type': 'String' },
            'endTime': { 'required': true, 'type': 'String' },
            'target_property': { 'required': false, 'type': 'String' },
            'group_by': { 'required': false, 'type': 'String' }
        },
            args = {};

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

        // set timeframe
        var start = moment(args.startDate + " " + args.startTime, ['DD-MM-YYYY hh:mm A']).format("DD-MM-YYYY HH:mm:ss");
        var end = moment(args.endDate + " " + args.endTime, ['DD-MM-YYYY hh:mm A']).format("DD-MM-YYYY HH:mm:ss");
        var timeframe = { start: moment.utc(start, 'DD-MM-YYYY HH:mm:ss').toDate(), end: moment.utc(end, 'DD-MM-YYYY HH:mm:ss').toDate() };

        // prepare query object
        var obj = {
            "event_collection": 'event_' + params.qstring.app_id + "_" + args.event, // set collection name
            "timeframe": timeframe, // set timeframe
            filters: []
        };

        // set target property
        if (
            args.analysis_type == "count_unique" ||
            args.analysis_type == "minimum" ||
            args.analysis_type == "maximum" ||
            args.analysis_type == "sum" ||
            args.analysis_type == "average" ||
            args.analysis_type == "select_unique"
        ) {
            if (args.target_property != '') {
                obj.target_property = args.target_property;// set target property
            }
        }

        // set group by property
        if (args.group_by != '') {
            obj.group_by = args.group_by;// set target property
        }

        var query = [];
        // prepare query with keen object
        query.push(keen.prepareQuery(args.analysis_type, obj));

        // run keen query for data explore
        keen.runQuery(query, function (error, data) {
            if (error) {
                // return error
                return common.returnOutput(params, { "result": JSON.stringify(error) });
            }
            else {
                // check data and data result in response
                if (data && data.result) {
                    if (args.analysis_type == 'extraction') {
                        data.result.forEach(function (d) {
                            // remove keen property
                            if (d.keen) {
                                delete d.keen;
                            }
                            // remove context property
                            if (d.context) {
                                delete d.context;
                            }
                        });
                    }
                    // return outout with data
                    return common.returnOutput(params, data);
                }
                else {
                    // return error with message
                    return common.returnOutput(params, { "result": "Data not found!" });
                }
            }
        });
    }

    // get event schema details
    events.getEventSchema = function (params) {
        // validate event name property
        if (params.qstring.eventName == undefined || params.qstring.eventName == '') {
            common.returnMessage(params, 400, 'Not event selected');
            return false;
        }
        params.qstring.eventName = params.qstring.eventName.toLowerCase();
        // black list prefix name or complete name of column
        var blackListColumns = ['sid', 'attributes', 'createdat', 'user_id', 'rtime', 'utime', 'createdAt'];
        // run query string
        common.redshiftDB.query("SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '" + params.qstring.eventName + "'", { raw: true })
            .then(function (data) {
                if (data) {
                    // return outout with data
                    return common.returnOutput(params, data);
                }
                else {
                    logger.info('There is no data.');
                    // return outout with data
                    return common.returnOutput(params, []);
                }
            })
            .catch(function (err) {
                logger.error("err==>" + JSON.stringify(err));
                // return outout with data
                return common.returnOutput(params, []);
            });
    }


      //Download Event Stats Csv 
    events.downloadEventsStats = function (params) {
        var argProps = {
            'filter':{'required':true,'type':'JSON'},
        },filterObj={};

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


        var query = {};
        var array = [];
        let filter = params.qstring.args.filter;
        let sd = filter.dateRange.sd;
        let ed = filter.dateRange.ed;
        let eventsArr = filter.events;
        let selectAll = filter.selectAll;
        let platform = filter.platform;
        let appid = params.qstring.app_id;

        //Prepare event csv query
        if(platform === 'all') {
            common.fillEventDownloadQueryObject(query, array, sd, ed, 'android', eventsArr, selectAll, appid);
            common.fillEventDownloadQueryObject(query, array, sd, ed, 'ios', eventsArr, selectAll, appid);
            common.fillEventDownloadQueryObject(query, array, sd, ed, 'web', eventsArr, selectAll, appid);
        }else {
            common.fillEventDownloadQueryObject(query, array, sd, ed, platform, eventsArr, selectAll, appid);
        }

        common.db.collection('timely_events').find({ _id: { $in: array } }, query).toArray(function (error, result) {
            if (!error) {
                if (result) {
                    let csvData = prepareStatsCsvData(params,result, platform, 'event');
                    common.returnCSV(params, csvData.replace('undefined', ''));
                }
                else {
                    common.returnOutput(params, {});
                }
            }
            else {
                logger.error('error::' + JSON.stringify(error));
            }
        });
    }


     //Download Event Attribute Stats Csv 
    events.downloadEventsAttributeStats = function (params) {
        var argProps = {
            'filter':{'required':true,'type':'JSON'},
        },filterObj={};

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

         //Incrementing sd to 1 day
        params.qstring.args.filter.dateRange.sd = new Date(params.qstring.args.filter.dateRange.sd * 1000).setDate( new Date(params.qstring.args.filter.dateRange.sd * 1000).getDate() + 1);
        params.qstring.args.filter.dateRange.sd = params.qstring.args.filter.dateRange.sd /1000;
        var query = {};
        var array = [];
        let filter = params.qstring.args.filter;
        let sd = filter.dateRange.sd;
        let ed = filter.dateRange.ed;
        let eventName = filter.eventName;
        let attributeName = filter.attributeName;
        let platform = filter.platform;
        let appid = params.qstring.app_id;

        //Prepare event csv query
        if(platform === 'all') {
            common.fillEventAttributeDownloadQueryObject(query, array, sd, ed, 'android', appid, eventName, attributeName);
            common.fillEventAttributeDownloadQueryObject(query, array, sd, ed, 'ios', appid, eventName, attributeName);
            common.fillEventAttributeDownloadQueryObject(query, array, sd, ed, 'web', appid, eventName, attributeName);
        }else {
            common.fillEventAttributeDownloadQueryObject(query, array, sd, ed, platform, appid, eventName, attributeName);
        }

       common.db.collection('timely_eventAttributes' + params.qstring.app_id).find({ _id: { $in: array } }, query).toArray(function (error, result) {
        if (!error) {
            if (result) {
                let csvData = prepareStatsCsvData(params,result, platform, 'eventattr' ,eventName, attributeName);
                common.returnCSV(params, csvData.replace('undefined', ''));
            }
            else {
                common.returnOutput(params, {});
            }
        }
        else {
            logger.error('error::' + JSON.stringify(error));
        }
      });
   }

    //Prepare Event csv data
    function prepareStatsCsvData(params,data,platform,type,eventname=undefined, attributename=undefined) {
        let resultObj = [];
        //CSV DATA COLUMNS -for events
        var csvData = "Date, Key, Count";
        if(type == "eventattr") {
             //CSV DATA COLUMNS -for eventsAttr
            csvData = "Date, Key, AttributeName, AttributeValue, Count";
        }
       
       //Config for platform type
        if(platform && platform  == 'all') {
            platform = ['android','ios','web'];
        } else  {
            platform = [platform];
        }

        if(data) {
             data.forEach( ele => {
                platform.forEach(p => {
                    if(ele[p] && typeof ele[p] == "object"){
                        Object.keys(ele[p]).forEach(d => {
                            let date = d;
                            if(ele[p][date] && typeof ele[p][date] == "object") {
                                let entries = Object.entries(ele[p][date]);
                                if(entries) {
                                    entries.forEach(ent => {
                                        if(type == "event") {
                                            let event = ent[0]?ent[0]:'';
                                            let count = ent[1]?ent[1]:'';
                                            let obj = {};
                                            obj.Date = date;
                                            obj.Key = event;
                                            obj.Count = count;
                                            resultObj.push(obj); 
                                            let dTemp = date.split("_");
                                            let daten = (dTemp[2]+"-"+dTemp[1]+"-"+dTemp[0]);
                                            const fdate = common.getDateToEpoch(daten)
                                            csvData += "\r\n" + common.sanitizeString(common.getEpochToDateWithTime(fdate,false,'MMM DD, YYYY')) + "," + event + "," + count;

                                        }
                                        if(type == "eventattr") {
                                            attributename.forEach(function (item) {
                                                let attrValue = ent[0] ? ent[0] : '';
                                                let count = ent[1]?ent[1]:'';
                                                let obj = {};
                                                obj.Date = date;
                                                obj.Key = eventname;
                                                obj.AttributeName = item;
                                                obj.AttributeValue = attrValue.replace(/\:/g, ".");
                                                obj.Count = count;
                                                resultObj.push(obj);
                                                let dTemp = date.split("_");
                                                let daten = (dTemp[2]+"-"+dTemp[1]+"-"+dTemp[0]);
                                                const fdate = common.getDateToEpoch(daten,true)
                                                csvData += "\r\n" + common.sanitizeString(common.getEpochToDateWithTime(fdate,false,'MMM DD, YYYY')) + "," + eventname + "," + item + "," + attrValue.replace(/\:/g, ".") + "," + count;
                                            })
                                        }
                                    })
                                } 
                            }
                        })
                    }
               })
            })
            executeJsonToCsv(resultObj, params);
            //Returning final CSV Data
            return csvData;   
      }
}

//Converts JSON to csv file
async function executeJsonToCsv(resultobj, params) {
    try {
        const csv = await converter.json2csvAsync(resultobj);
        // write CSV to a file
        fs.writeFileSync(common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename, csv);
        uploadCsvToBlob(params);

    } catch (err) {
        logger.error("generated event csv error =>",err);
    }
}

/**
 * @ Upload event csv to blob storage
 * @ Send event csv email to client
 */
function uploadCsvToBlob(params) {
     // get registered email address of client
     var emailTo;
     cacheApi.getKey('csvemail_'+params.qstring.app_id, function(error, email){
            common.db.collection('members').findOne({api_key:params.qstring.api_key}, function(err, members){
                if(err){
                    logger.error(`members collection data not found!`);
                }
                else if(members.email){
                    cacheApi.setKey('csvemail_'+params.qstring.app_id, members.email);
                    emailTo = members.email;
                }
            });
    });

    //Upload csv to blobContainerCDN
    var blobContainerCDN;
    blobContainerCDN = common.config.blobContainerCDN+common.config.CSVBUCKET
    const blobService = Azure.createBlobServiceWithSas(common.config.azureApiBlobSASURL, common.config.azureApiStorageSASToken);
    var key = "eventcsv/" + params.qstring.app_id+"_"+params.qstring.filename;
    blobService.createBlockBlobFromLocalFile(common.config.CSVBUCKET,
        key,
        common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename,
        (error, result) => {
            if(error) {
                logger.error("blob container csv error=> "+JSON.stringify(error));
            } else {
                if (fs.existsSync(common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename)) {
                    fs.unlinkSync(common.config.CSVDIR + params.qstring.app_id +"_"+ params.qstring.filename);
                }

               //Send Csv Mail
                var mailDate = (moment(params.qstring.args.filter.dateRange.sd*1000).format('DD-MM-YYYY') != moment(params.qstring.args.filter.dateRange.ed*1000).format('DD-MM-YYYY')) ? 'FROM '+moment(params.qstring.args.filter.dateRange.sd*1000).format('DD-MM-YYYY')+' TO '+ moment(params.qstring.args.filter.dateRange.ed*1000).format('DD-MM-YYYY') : ' Of '+ moment(params.qstring.args.filter.dateRange.ed*1000).format('DD-MM-YYYY');
                var message = {
                    to: emailTo,
                    bcc: ['saurabh.singh@semusi.com','tarun.anand@semusi.com'],
                    subject:'Event data CSV '+mailDate,
                    html:'Hi,<br/><br/>' +
                        'Your data download is ready! Please click on the link below to download your data.<br/><br/>' +
                        '<a href="'+blobContainerCDN+"/"+key+'"> '+blobContainerCDN+"/"+key+'</a> <br/><br/>' +
                        'Appice Team,<br/>' +
                        'Server: <a href="'+common.config.cdnUrl+'" target="_blank">'+common.config.cdnUrl+'</a>'
                };
                mail.sendMail(message);
        }
    });

}


    // get table names
    events.getTables = function (params) {
        if (params.qstring.app_id) {
            common.redshiftDB.query(
                "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_name like '%" + params.qstring.app_id + "%'", { raw: true })
                .then(function (data) {
                    logger.error("data ==>" + JSON.stringify(data));
                    if (data) {
                        // return outout with data
                        return common.returnOutput(params, data);
                    }
                    else {
                        // return outout with data
                        return common.returnOutput(params, []);
                    }
                })
                .catch(function (err) {
                    logger.error("err==>" + JSON.stringify(err));
                    // return outout with data
                    return common.returnOutput(params, []);
                });
        }
        else {
            return common.returnOutput(params, []);
        }
    }

    events.getUsagesRetention = function (params) {
        if (params.qstring.app_id && params.qstring.api_key && params.qstring.startDate) {
            try {
                var appid = params.qstring.app_id,
                    startDate = moment(params.qstring.startDate, 'YYYY-MM-DD').valueOf();
                var retentions = [];
                var array = [0, 1, 2, 3, 4, 5, 6];
                var arrayLength = array.length + 1;
                async.forEach(array, function (day, callback) {
                    var date = moment(startDate).add(day, 'days').valueOf();
                    arrayLength--;
                    getUgagesRetentionForDay(date, appid, arrayLength)
                        .then(res => {
                            var obj = {};
                            res.date = moment(res.date).format('YYYY-MM-DD');
                            obj[res.date] = res.result;
                            retentions.push(obj);
                            callback();
                        })
                }, function () {
                    logger.info(" final result retentions " + JSON.stringify(retentions));
                    return common.returnOutput(params, retentions);
                });
            }
            catch (e) {
                common.returnMessage(params, 400, 'Date format is incorrect. Please enter YYYY-MM-DD');
            }
        }
        else {
            common.returnMessage(params, 400, 'Required parms is missing! app_id, api_key and startDate is required.');
        }
    }

    // get retention for day
    var getUgagesRetentionForDay = function (startDate, appid, length) {
        return new Promise((resolve, reject) => {
            let timeframe = {
                start: moment(startDate).startOf('day').toDate(),
                end: moment(startDate).endOf('day').toDate()
            }

            var steps = [{
                event_collection: "event_" + appid + "_install",
                actor_property: "did",
                timeframe: timeframe
            }];

            steps.push(prepareSteps(appid, startDate));
            for (var i = 1; i < length; i++) {
                var date = moment(startDate).add(i, 'days').valueOf();
                steps.push(prepareSteps(appid, date));
            }

            keen.getFunnelResultsWithActorProperty(steps, function (result) {
                return resolve({ date: startDate, result: result.result })
            });
        });
    }

    // prepare steps
    var prepareSteps = function (appid, startDate) {
        let timeframe = {
            start: moment(startDate).startOf('day').toDate(),
            end: moment(startDate).endOf('day').toDate()
        }
        return {
            event_collection: "event_" + appid + "_Session_Start",
            actor_property: "did",
            timeframe: timeframe
        };
    }

    //explore data from keen
    events.getEventRawData = function (params) {
        //init required property
        var argProps = {
            'query': { 'required': true, 'type': 'String' }
        },
            args = {};

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

        args.query.replace(/;\\\/]/gi, '');
        common.redshiftDB.query(args.query, { raw: true })
            .then(function (data) {
                if (data) {
                    // return outout with data
                    return common.returnOutput(params, data);
                }
                else {
                    // return outout with data
                    return common.returnOutput(params, []);
                }
            })
            .catch(function (err) {
                logger.error("err==>" + JSON.stringify(err));
                // return outout with data
                return common.returnOutput(params, []);
            });
    }

}(events));

module.exports = events;
