var funnels = {},
    common = require('./../../utils/common.js'),
    logger = require('../../../logger'),
    async = require('./../../utils/async.min.js'),
    semusiConfig = require('./../../config.js'),
    _ = require('underscore'),
    //client = require('node-rest-client').Client,
    //proxy = new client(),
    json2csv = require('./../../utils/json-2-csv.js'),
    cacheApi = require('./../../utils/semusi.cache.js'),
    AWS = require('aws-sdk'),
    Promise = require('bluebird'),
    Minio = require('minio'),
    moment = require("moment"),
    crypto = require('crypto'),
    validate = require('../../constants/constants'),
    { exec } = require('child_process'),
    uuid = require('uuid'),
    fs = require('fs'),
    regex = require('./../../utils/regex.js');
    psqlUtils = require('./../../utils/pgsql.utils.js');
    Azure = require('azure-storage');
    path = require('path');

const {convertCsvToXlsx} = require('@aternus/csv-to-xlsx');
const zlib = require('zlib');
const XlsxPopulate = require('xlsx-populate');


(function (funnels) {
    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});
    let S3 = new AWS.S3();
    
    // create new funnels
    funnels.createFunnel = function (params) {
        let argProps = {
            'events': { 'required': true, 'type': 'JSON' },
            'name': { 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
            'description': { 'required': false, 'type': 'String', 'regex': validate.getRegex('name') },
            'duration': { 'required': true, 'type': 'String' },
            'unit': { 'required': true, 'type': 'String' },
            'customunit': { 'required': false, 'type': 'String' },
            'platform': {'required': true, 'type': 'Array'},
            'audience': {required: false, type: 'JSON' },
            'customDate':{'required': false, 'type':'JSON'}
        },
            funnel = {};


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

        funnel.createdOn = common.getCurrentEpochTime();
        funnel.modifiedOn = common.getCurrentEpochTime();
        funnel.deleted = false;
        common.db.collection('funnels_' + params.qstring.app_id).insert(funnel, function (err, res) {
            if (err) {
                logger.error("funnel error", err);
                common.returnOutput(params, 500, "Error");
            }
            else {
                logger.info("funnel created", res)
                common.returnOutput(params, res);
            }
        });

    }
    funnels.updateJourney = function (params) {

         
        let obj = {
            startEvent:params.qstring.args.startEvent,
            endEvent: params.qstring.args.endEvent,
            journeyName : params.qstring.args.journeyName,
            journeyDescription: params.qstring.args.journeyDescription,
            paths: params.qstring.args.paths,
            platform:params.qstring.args.platform,
            period: params.qstring.args.period
        }
        obj.modifiedOn = common.getCurrentEpochTime();
        common.db.collection('journeys_'+params.qstring.app_id).update({'_id':common.db.ObjectID(params.qstring.args.id)},{$set: obj},function(err,result){
            if(!err || result){
            common.returnOutput(params, result);
            }else{
                logger.error("error in updating journey",err);
                common.returnOutput(params, 'Failed to update journey');
                
            }  
        })
    }

// create new copy funnels by id
  funnels.copyFunnel = function(params) {

    if(params.qstring.id){
    common.db
      .collection("funnels_" + params.qstring.app_id)
      .findOne({ _id: common.db.ObjectID(params.qstring.id) }
      ,function(err, funnel) {
        if(err){
          common.returnOutput(params, 500, "Error");
        }
        let copy = {};
      
          funnel["name"] = funnel["name"] + " copy "+ crypto.randomBytes(2).toString('hex');
          funnel.createdOn = common.getCurrentEpochTime();
          funnel.modifiedOn = common.getCurrentEpochTime();
          funnel.deleted = false;
          funnel._id = common.db.ObjectID();
          copy = funnel;
          common.db
            .collection("funnels_" + params.qstring.app_id)
            .insert(copy, function(err, res) {
              if (err) {
                logger.error("error in funnel copy", err);
                common.returnOutput(params, 500, "Error");
              } else {
                logger.info("funnel copied successfully",res) 
                common.returnOutput(params, 200, "Success");
              }
            });
     

      });

    }
    else{
      common.returnMessage(params, 400, "Not enough args");
    }
  };

//get funnel by id
  funnels.getFunnelById = function(params){
  
    if(params.qstring.id){
      common.db
      .collection("funnels_" + params.qstring.app_id)
      .findOne({ _id: common.db.ObjectID(params.qstring.id) }
      ,function(err, funnel) {
        if(err){
          logger.error("error in fetching a single funnel", err);
          common.returnOutput(params, 500, "Error");
        }
        common.returnOutput(params, funnel);
      })

    }
    else{
      common.returnMessage(params, 400, "Not enough args");
    }
  }

//Update funnel by id
    funnels.updateFunnel = function (params) {
        let argProps = {
            'events': { 'required': true, 'type': 'JSON' },
            'name': { 'required': true, 'type': 'String', 'regex': validate.getRegex('name') },
            'description': { 'required': false, 'type': 'String', 'regex': validate.getRegex('name') },
            'duration': { 'required': true, 'type': 'String' },
            'unit': { 'required': true, 'type': 'String' },
            'customunit': { 'required': false, 'type': 'String' },
            'platform': {'required': true, 'type':'Array'},
            'audience': {required: false, type: 'JSON' },
            'customDate':{'required': false, 'type':'JSON'}
        },
            funnel = {};


        if (!(funnel = common.validateArgs(params.qstring.args, argProps))) {
            common.returnMessage(params, 422, 'Validation error');
            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) {
                logger.error("error in funnel update", err);
                common.returnOutput(params, 500, "Error");
            }
            else {
                logger.info("funnel updated successfully", res);
                common.returnOutput(params, 200, "Success");
            }

        });

    }

//delete funnel by id
    funnels.deleteFunnel = function (params) {
        let 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) {
                logger.error("error in funnel deletion", err);
                common.returnOutput(params, 500, "Error");
            }
            else {
                logger.info("funnel deleted successfully", res);
                common.returnOutput(params, 200, "Success");
            }
        });

    }
    
    
    /**
     * Get eventsFunnel
     * @return funnel in response
     **/
    funnels.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){
        let dateVal ="/Date("+epoc+")/";
        let date = new Date( parseFloat( dateVal.substr(6)));
        return(date.getFullYear() + "-" +(date.getMonth()+1)   + "-" +  date.getDate());
    }

    /**
     * updateEventNames methods updates eventname with _number if any duplicate eventname there
     * @param {*} funnel 
     * @returns 
     */
    funnels.updateEventNames = function (funnel) {
        const eventNamesCount = {};
    
        funnel.events.forEach((event, index) => {
            const baseEventName = event.eventName;
    
            if (!eventNamesCount[baseEventName]) {
                eventNamesCount[baseEventName] = 1;
            } else {
                eventNamesCount[baseEventName]++;
                event.eventName = `${baseEventName}_${eventNamesCount[baseEventName]}`;
            }
        });
        return funnel;
    };

// new get funnel with PG
    funnels.getEventFunnelStats = function (params) {
        let funnel = funnels.updateEventNames(params.qstring.args);
        let epochtimeStamp = "";      
        let epochlastTimeStamp = "";
        let duration = funnel.duration;      
        //Put Filter on the Basis of UI Condition 
        switch (funnel.customunit || funnel.unit) {
            case "Today":
                epochtimeStamp = moment().unix()*1000;
                epochlastTimeStamp  = moment().unix()*1000;
            break;
            case "Yesterday":
                epochtimeStamp = moment().subtract(1, 'days').startOf('day').unix()*1000;
                epochlastTimeStamp  = moment().subtract(1, 'days').endOf('day').unix()*1000;
            break;
            case "This Week":
                epochtimeStamp = moment().startOf('week').unix()*1000;
                epochlastTimeStamp  = moment().unix()*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  = moment().unix()*1000;
            break;
            case "Last Month":
                epochtimeStamp = moment().subtract(30, 'days').format('YYYY-MM-DD');
                epochlastTimeStamp = moment().subtract(30, 'days').format('YYYY-MM-DD');
                epochtimeStamp = common.adjustStartDate(epochtimeStamp);
                epochlastTimeStamp = common.adjustEndDate(epochlastTimeStamp);
            break;
            case "Days":
                if(funnel.customDate && Object.keys(funnel.customDate).length > 0 ){
                    epochtimeStamp = funnel.customDate.sd;
                    epochlastTimeStamp = funnel.customDate.ed;
                }else{
                    epochtimeStamp = moment().subtract(duration, 'days').unix()*1000;
                    epochlastTimeStamp  = moment().unix()*1000;
                }
            break;
            case "Weeks":
                epochtimeStamp =moment().subtract(duration, 'weeks').startOf('isoWeek').unix()*1000;
                epochlastTimeStamp  = moment().startOf('week').unix()*1000; 
            break;
            case "Months":
                epochtimeStamp = moment().subtract(duration, 'month').unix()*1000;
                epochlastTimeStamp  = moment().unix()*1000;
            break;
            case "Hours":
                epochtimeStamp = moment().subtract(duration, 'hours').unix();
                epochlastTimeStamp  = moment().unix();
                break;
            case "Minutes":
                epochtimeStamp = moment().subtract(duration, 'minutes').unix();
                epochlastTimeStamp  = moment().unix();  
                break;
        }
        /* to check app configure for pg or regex data */
        if(semusiConfig.regexAPPIDS.includes(params.qstring.app_id)){
            let eventsData = {}
            let timeStamp=epocToDate(epochtimeStamp);
            let lastTimeStamp=epocToDate(epochlastTimeStamp);
            if(new Date(timeStamp).getMonth() < 9){
            let smonth = ("0" + (new Date(timeStamp).getMonth()+1)).slice(-2);
            let sdate  = new Date(timeStamp).getDate();
            let syear = new Date(timeStamp).getFullYear();
            timeStamp = syear+ "-" + smonth + "-" + sdate;   

            }
            if(new Date(lastTimeStamp).getMonth() < 9){
                let emonth = ("0" + (new Date(lastTimeStamp).getMonth()+1)).slice(-2);
                let edate  = new Date(lastTimeStamp).getDate();
                let eyear = new Date(lastTimeStamp).getFullYear();
                lastTimeStamp = eyear+ "-" + emonth + "-" + edate;  
            }
            //get Formatted Date
            //fetch data from app Events table
            let fetchAppEventsData = async function(appEventTable) {
                let AppEventsQuery = "select DISTINCT name,id from "+ appEventTable ;
                //wait for response to get stored in result
                let result = await pgsql.executeQuery(AppEventsQuery)
                .then((result) => {
                        return result;   
                })
                .catch((error)=>{
                     
                    result = [];
                })
                return result;    
            }

            //get length of event steps
            let query = [];
            let steps = params.qstring.args.events.length;
            let requestEvent = [];
            //get data from events table
    
            fetchAppEventsData("app_events_"+params.qstring.app_id)
            .then(async events=>{
                let stepId = funnel.events.map(ele =>{
                    let obj = events[0].find(e => e.name === ele.eventName);
                    if(obj){
                    requestEvent.push(obj);    
                    return {"operand": obj.name, "attr":'',"value":'', "operator":'', "since":{"have":'h'}};
                    }
                });
                timeStamp = (new Date(timeStamp).getTime()) / 1000;
                lastTimeStamp = (new Date(lastTimeStamp).getTime()) /1000;
                if(steps == 5){
                    query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0]], timeStamp,lastTimeStamp, funnel.platform));
                    query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1]],timeStamp,lastTimeStamp, funnel.platform));
                    query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1],stepId[2]],timeStamp,lastTimeStamp, funnel.platform));
                    query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1],stepId[2],stepId[3]],timeStamp,lastTimeStamp, funnel.platform));
                    query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1],stepId[2],stepId[3],stepId[4]],timeStamp,lastTimeStamp, funnel.platform));
                    }
                    else if(steps == 4){
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0]],timeStamp,lastTimeStamp, funnel.platform));
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1]],timeStamp,lastTimeStamp, funnel.platform));
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1],stepId[2]],timeStamp,lastTimeStamp, funnel.platform));
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1],stepId[2],stepId[3]],timeStamp,lastTimeStamp, funnel.platform));
                    }
                    else if(steps == 3){
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0]],timeStamp,lastTimeStamp, funnel.platform));
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1]],timeStamp,lastTimeStamp, funnel.platform));
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1],stepId[2]],timeStamp,lastTimeStamp, funnel.platform));
                    }
                    else if(steps == 2){
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0]],timeStamp,lastTimeStamp, funnel.platform));
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0],stepId[1]],timeStamp,lastTimeStamp, funnel.platform));
                    }
                    else if(steps == 1){
                        query.push(await regex.generateRegexForSteps(params.qstring.app_id,[stepId[0]],timeStamp,lastTimeStamp, funnel.platform));
                    }
                    else{}
            
                    let funnelStats=[];  
                    let len = query.length;
                    for(let i=0;i<len;i++){
                        funnelStats.push(pgsql.executeQuery(query[i]));
                    }
                    Promise.all(funnelStats).then(data=>{
                        let countArray = [];
                        for(let c = 0; c<data.length;c++){
                            for(let d=0;d<data[c].length;d++){ 
                                if(d==1) break;
                                countArray.push(data[c][d][0]); 
                                }
                        }
                        let resArray = requestEvent.map((e, i) => {
                            return  {
                                name: e.name,
                                count: countArray[i].count
                            }     
                        });  
                        common.returnOutput(params, resArray);
                        })
                        .catch(error=>{
                            let op = {};
                            op.data = error;
                            common.returnOutput(params, op);
                        })
                })
            .catch(error=>{ 
                let op = {};
                op.status = "Error from first catch"
                op.error = error;
                common.returnOutput(params, op);

            });
        }else{
            let eventsData = {}
            let events = [];
            let timeStamp;
            let lastTimeStamp;
            let p = (funnel.platform && funnel.platform.length==3) ? "" : " AND p in ('"+funnel.platform.join("','")+"')";
            if((funnel.customunit === 'Minutes') || (funnel.customunit === 'Hours')){
                 timeStamp=epochtimeStamp;
                 lastTimeStamp=epochlastTimeStamp;
            }else if(funnel.customDate && Object.keys(funnel.customDate).length > 0){
                 timeStamp=epochtimeStamp;
                 lastTimeStamp=epochlastTimeStamp;
            }else{
                 timeStamp=epocToDate(epochtimeStamp);
                 lastTimeStamp=epocToDate(epochlastTimeStamp);
            }
            let sqlData;
            //get Formatted Date
            if(funnel.events.length==5){
                // PG query
             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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s2 ON (s1.did = s2.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[2].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[2])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s2 ON (s1.did = s2.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[2].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[2])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s3 ON (s2.did = s3.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[3].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[3])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s4 ON (s3.did = s4.did) WHERE s1.eventtime <= s2.eventtime AND s2.eventtime <= s3.eventtime AND s3.eventtime <= 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s2 ON (s1.did = s2.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[2].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[2])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s3 ON (s2.did = s3.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[3].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[3])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s4 ON (s3.did = s4.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[4].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[4])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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){
             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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s2 ON (s1.did = s2.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[2].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[2])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s2 ON (s1.did = s2.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[2].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[2])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s3 ON (s2.did = s3.did) INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[3].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[3])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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){
                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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s2 ON s1.did = s2.did INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[2].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[2])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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){
                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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}) s1 INNER JOIN (SELECT did, eventtime FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[1].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[1])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, 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){
                sqlData = `SELECT Count(DISTINCT did) AS "${funnel.events[0].eventName}" FROM events_${params.qstring.app_id} WHERE KEY = '${funnel.events[0].eventName.replace(/_\d+$/, '')}' ${common.mulipleAttributes(funnel.events[0])} ${p} AND ${common.queryConverter(funnel.customunit, timeStamp, lastTimeStamp)}`;
            }else{
            }
            logger.info(`sql => ${sqlData}`);
            // Run pg query
            pgsql.executeQuery(sqlData)
                    .then((result) =>{
                        if(result){
                            logger.info("Funnel Query Result => ",JSON.stringify(result[0][0].count))
                            eventsData.events = events;
                            eventsData.data = JSON.stringify(result[0][0]);
                            logger.info(eventsData)
                            let temArry = []
                            funnel.events.forEach(function(value, i){
                                temArry.push({"name":value.eventName,"count": Number(result[0][0][value.eventName]), timeStamp, lastTimeStamp, customUnit : funnel.customunit, events : funnel.events[i], p : funnel.platform});
                                logger.info(`Data Rendered Will be `)
                                logger.info(`name : ${value.eventName}, count : ${Number(result[0][0][value.eventName])}, timeStamp : ${timeStamp}, lastTimeStamp : ${lastTimeStamp}, customUnit : ${funnel.customunit}, events : ${funnel.events} , platform : ${funnel.platform}`)
                            });
                            return common.returnOutput(params, temArry);
                        }
                        else{
                            logger.error(`event count reach error => ${error}`);
                            eventsData.events = events;
                            eventsData.data = 0;
                            return common.returnOutput(params, eventsData);
                        }
            })
            .catch((error) =>{
                logger.error(`error : ${JSON.stringify(error)}`);
                common.returnMessage(params, 200, "Failed");
            })
        }
    }
 
     /**
     * Validate funnel name
     * @return boolean value in response
     **/
    funnels.validateFunnelName = function (params) {
        if (params.qstring.funnelName) {
            let 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) {
                    logger.error("error in validating funnel", 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");
        }
    }
    /**
     * Validate funnel export
     * @return csv file in response
     **/
    funnels.startExports = function (params) {
        try {

        cacheApi.getKey(params.qstring.args.q, function(err, result) {
            if(!err){
                logger.info('query result =>', result)
        if(params.qstring.app_id){
            let app_id  = params.qstring.app_id;
            let quer = result;
            let currentTime = common.getCurrentEpochTime();
            // construct name based on the incoming req. from audience or Funnels ,
            const exportType = params.qstring.args.isAud ? 'AudienceExports_' : 'FunnelExports_';
            const fileName = `${exportType}${moment().format('MMMM Do YYYY,h:mm:ss a').replace(/ /g, '_')}`;
            let jobs = {};
            jobs._id = common.db.ObjectID();
            jobs.deleted = false;
            jobs.app_id = app_id;
            jobs.filename = fileName;
            jobs.email= params.member.email;
            jobs.status ='initiated';
            
            jobs.exportjobbaseurl = '';
            jobs.exportjobentityurl = '';
            jobs.processeddate = '';
            jobs.createdOn = currentTime;
            jobs.modifiedOn = currentTime;
            jobs.query = quer;
    
            //function call for save jobs data
            common.saveJobsData(jobs).then((jobsData) =>{
                let payloadStatusTableName = "exportdata_status_"+app_id;
                let payloadTableName = "exportdata_"+app_id;
                let jobId = uuid.v4();

                jobsData.query.mid = jobsData._id;
                jobsData.query.email = jobsData.email;

                let mainQuery = {};

                mainQuery.q = quer.toString().replace(/'/ig, "''");
                mainQuery.email = jobsData.email;
                mainQuery.serviceType = semusiConfig.ServiceName.Funnel;
                mainQuery.mid = jobsData._id;

                let query = "INSERT INTO " + payloadStatusTableName + " (jobid,status,created_at) VALUES ('" + jobId + "','unprocessed',current_timestamp AT TIME ZONE 'IST');INSERT INTO exportdata_"+app_id+" (jobid,payload,created_at) VALUES ('"+jobId+"','"+JSON.stringify(mainQuery)+"',current_timestamp AT TIME ZONE 'IST');";
                logger.info(`_____________________Query --------------------`);
                logger.info(`${query}`);
                // EXECUTE MAIN USER QUERY
                psqlUtils.runQuery(query)
                .then(s => {
                    logger.info(`s==> ${s}`);
                    common.returnOutput(params,s);
                })
                .catch((err) => {
                    logger.error(`err${err}`);

                    //Incase of Query failure update the status in the Jobs data
                    const job_status = 'Failed Due to Metadata'
                    common.updateJobsData(jobsData._id,job_status,app_id).then(()=>{
                        common.returnOutput(params,err);
                    }).catch(error =>{
                        logger.error(`ERROR IN THIS===${error}`)
                        common.returnOutput(params,error);
                    });
                });
            })
            .catch(error =>{
                logger.error(`catch error ::>>>${error}`);
            });
        }else{
            common.returnOutput(params, {});
        } 
    }else{
        common.returnOutput(params, {});
    }
   })
    } catch (error) {
          logger.info('error in downlodFunnelUsers =>', error)      
    }
    }


    /**
     * Get CSV downloaded List with status
     * @return csv list in response
     **/
     funnels.getCsvList = function (params) {

        let retrieveItems={'_id':1,app_id:1,filename:1,status:1,processeddate:1,createdOn:1,downloaded:1}
        // create object for response
        const retVal = {}
        common.db.collection('exportjobs_' + params.qstring.app_id).find({ deleted: false },retrieveItems).sort({ createdOn: -1 }).toArray(function (err, res) {
        if(semusiConfig.panelExport){
            // if panel export is enabled then add flag to response for UI
            retVal.panelExport = true
            retVal.list = res       // add csv List to response
            logger.info("CSV List get successfully");
            common.returnOutput(params, retVal);
        }else{
            //incase of streaming downloading set exports downloaded by default as true 
            res.forEach(element => {
                element.downloaded = true
            });
            retVal.list = res
            logger.info("CSV List get successfully");
            common.returnOutput(params, retVal);
        }
        });
    }

    function exportDownloads(params, exportDetails) {
      const rString = atob(params.qstring.args.num);

      if (semusiConfig.panelExport) {
        const currentFilePath = path.join(__dirname + "../../../exports/" + exportDetails.exportjobentityurl);
        const outputFilePath = path.join(__dirname + "../../../exports/" + exportDetails.filename + ".csv");
        const gunzip = zlib.createGunzip();
        const inputStream = fs.createReadStream(currentFilePath);
        const outputStream = fs.createWriteStream(outputFilePath);
        const compressOutputFilePath = path.join(__dirname + "../../../exports/" + exportDetails.filename + ".xlsx.gz");
        const destination = path.join(__dirname + "../../../exports/" + exportDetails.filename + ".xlsx");
        const fileExtenison = exportDetails.exportjobentityurl.slice(exportDetails.exportjobentityurl.indexOf('.') + 1);   

        //if compressed file is already Present
        //then delete the previous lockedfile so we can create new one
          fs.unlink(destination, function (err) {
            logger.info(`File deleted!`);

            if(fileExtenison == 'csv'){
                source = currentFilePath
                initiateConversion(source , destination)
            }else{
            // Extracting .gz File
            inputStream.pipe(gunzip).pipe(outputStream);
            outputStream.on("finish", () => {
              logger.info(`File extracted successfully.`);
    
              // Converting csv to xlsx
              let source = outputFilePath;
              try {
                initiateConversion(source , destination)
    
              } catch (e) {
                logger.error(`Exports Error = Failing converting Csv into xlsx===>${e.toString()}`);
                params.res.redirect("/dashboard?action=insideapp#/jobs/export/" +exportDetails.export_id);
              }
            });
    
            outputStream.on("error", (err) => {
              logger.error(`Exports Error ==> Extraction error===>${err.toString()}`);
              params.res.redirect("/dashboard?action=insideapp#/jobs/export/" + exportDetails.export_id);
            });
            }
          });

        function initiateConversion(source , destination){
            
            convertCsvToXlsx(source, destination);
            // Load an xlsx
            XlsxPopulate.fromFileAsync(destination)
            .then((workbook) => {
                workbook
                .toFileAsync(destination, { password: rString })
                .then(() => {

                    const inputStream = fs.createReadStream(destination); // Read the file
                    const gzip = zlib.createGzip(); // Create the Gzip compression stream
                    const outputStream = fs.createWriteStream(compressOutputFilePath); // Write the compressed data to the output file

                    inputStream.pipe(gzip).pipe(outputStream);

                    outputStream.on("finish", () => {
                      logger.info(`File compressed successfully.`);
                      params.res.download(compressOutputFilePath); //Downloading the file
                    });

                    outputStream.on("error", (err) => {
                      logger.error(`Exports Error ==> Compression error ===>${err.toString()}`);
                      params.res.redirect("/dashboard?action=insideapp#/jobs/export/" +exportDetails.export_id);
                    });

                })
                .catch((error) => {
                  logger.error(`Exports Error occurred while Executing workbook.toFileAsync ${error}`);
                  params.res.redirect("/dashboard?action=insideapp#/jobs/export/" + exportDetails.export_id);
                });
            })
            .catch((error) => {
              logger.error(`Exports Error occurred while Executing workbook.fromFileAsync ${error}`);
              params.res.redirect("/dashboard?action=insideapp#/jobs/export/" + exportDetails.export_id);
            });
        }
      } else {
        // TODO LATER
      }
    }
    /**
     * Download CSV from Minio 
     * @params App_id to find its exportCsv collection and  export_id of csv to get the filename from db
     * @return download csv File in  Browser
     **/
    funnels.downloadExportcsv = function (params) {
      logger.info(`Export Csv Download Started  `);

      let argProps = {
          export_id: { required: true, type: "String" },
          app_id: { required: true, type: "String" },
        },
        filterObj = {};

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

    //   validate minio endpoint
      if (semusiConfig.endPoint == undefined || semusiConfig.endPoint == "") {
        logger.info(`Error ==> Minio EndPoint Not Found `);
        params.res.redirect("/dashboard?action=insideapp#/jobs/export/" + filterObj.export_id);
        return;
      }

      //configure minio Details
      let minioClient = new Minio.Client({
        endPoint: semusiConfig.endPoint,
        port: semusiConfig.port,
        useSSL: semusiConfig.useSSL,
        accessKey: semusiConfig.accessKey,
        secretKey: semusiConfig.secretKey,
      });


      common.db.collection("exportjobs_" + filterObj.app_id).find({ _id: common.db.ObjectID(filterObj.export_id) }).toArray(function (err, exportResult) {
          if (err || exportResult.length == 0) {
            // error from db / no export found for this ID 
            logger.error(`Error while getting Export Csv Details from db==${JSON.stringify(err)}`);
            params.res.redirect("/dashboard?action=insideapp#/jobs/export/" + filterObj.export_id);
          } else {
            logger.info(`Export Details Found Now  initiating Process`);
            let exportDetails = exportResult[0];

            const fileExtenison = exportDetails.exportjobentityurl.slice(exportDetails.exportjobentityurl.indexOf('.') + 1);

            if(semusiConfig.panelExport){

                // will pick the funnel csv ffrom local 
                logger.info(`Export Csv File will be downloaded from Panel `)

               if(fs.existsSync(path.join(__dirname+'../../../exports/'+exportDetails.exportjobentityurl))){
                   logger.info(`File Found in Panel : -  Will Download it Now`)
                    exportDownloads(params ,exportDetails)

               }else{
                 // if it is already renamed and  stored in local 
                 if(fs.existsSync(path.join(__dirname+'../../../exports/'+exportDetails.filename+"."+ fileExtenison))){
                     params.res.download(__dirname+'../../../exports/'+exportDetails.filename+"."+ fileExtenison)
                 }else{
                     // incase file not present in the local path
                     logger.error(` Error :- File Not Found In The Panel Folder `)
                     params.res.redirect("/dashboard?action=insideapp#/jobs/export/" +filterObj.export_id);
                 }
               } 
            }else{
                logger.info(`Export Csv File will be downloaded from Minio`)
            //stream data from Minio 
            minioClient.getObject(
                semusiConfig.minioBuckets.exportsCsv,
              exportDetails.exportjobentityurl,
              function (err, dataStream) {
                if (err) {
                       //streaming File  Failed 
                  logger.error(`Error While Streaming Data in Minio ${JSON.stringify(err)}`);
                  params.res.redirect("/dashboard?action=insideapp#/jobs/export/" +filterObj.export_id);
                } else {
                    //streaming File successfully 
                  logger.info(` Streaming Data Successfully Now Downloading the Export Csv`);
                  params.res.attachment(exportDetails.filename +"."+ fileExtenison);
                  dataStream.pipe(params.res);
                }
              }
            );
          }
          }
        });
    };
   /**
     * delete CSV by id
     * @return csv delete status in response
     **/
    funnels.deleteCsv = function (params) {
        let funnel = {};
        funnel.modifiedOn = common.getCurrentEpochTime();
        funnel.deleted = true;
        common.db.collection('exportjobs_' + params.qstring.app_id).update({ _id: common.db.ObjectID(params.qstring.id) }, { $set: funnel }, function (err, res) {
            if (err) {
                logger.error("error in csv deletion", err);
                common.returnOutput(params, 500, "Error");
            }
            else {
                logger.info("csv deleted successfully", res);
                common.returnOutput(params, 200, "Success");
            }
        });

    }


}(funnels));

module.exports = funnels;
