//Converter Class
var Converter = require("csvtojson").Converter;
var converter = new Converter({});
var async = require('async');
var u = require('./u');
var redis = require('redis').createClient();
var sql = {
    tagnet:require('./csv/tagnet_sql')
};
var lpad = require('./utils/left-pad');
var SPN = {};
var CSVDATA = [];


function parseCsv(cb){
    u.log('Start parseCsv');
    //end_parsed will be emitted once parsing finished
    converter.on("end_parsed", function (jsonArray) {
        u.log('csv pareser end');
        //u.log(jsonArray); //here is your result jsonarray
        handleJson(jsonArray);
    });

    //read from file
    require("fs").createReadStream("./csv/TN010.import.csv").pipe(converter);
    function handleJson(data){
        //u.log('csv handleJson',data);
        CSVDATA = data;




                u.log('END parseCsv ', CSVDATA.length);
                cb(false, null);




    }
}
function getOrigin(cb){
    u.log('Start getOrigin');
    var len = CSVDATA.length - 1;
    CSVDATA.forEach(function(e, i, a){
        // e.Origin = lpad(e.Origin, 5, '0');
        e.Zipcode = lpad(e.Zipcode, 5, '0');


        if(i == len){
            u.log('End getOrigin');
            cb(false, null);
        }
    });

}

function handleRecord(cb){
    var tasks = [];
    var len = CSVDATA.length - 1;
    CSVDATA.forEach(function(e, i, a){
        tasks.push(function(callback){

                u.log('[Inserting record]',i, len);

                var query = "INSERT INTO " +
                    " [dbo].[tagnet_Geo_Zipcode2RouteSPN] " +
                    "(Zipcode,ServiceProviderID,Region,Route,IsAirport,IsMilitary,DriverNote,CityName,IsActiveForNewWork,IsActiveForCalendar)" +
                    " VALUES " +
                    "(" +
                    " '" + e.Zipcode + "' " +
                    " ,'" + e.ServiceProviderID + "' " +
                    " ,'" + e.Region + "' " +
                    " ,'" + e.Route + "' " +
                    " ," + e.IsAirport + " " +
                    " ," + e.IsMilitary + " " +
                    " ,'" + e.DriverNote + "' " +
                    " ,(select city from tagnet_Geo_ZipcodeInfo WHERE Zipcode = '" + e.Zipcode + "') " +
                    " ," + e.IsActiveForNewWork + " " +
                    " ," + e.IsActiveForCalendar + " " +

                    " ) ";
                // setTimeout(function(){
                //     callback(false, null);
                // }, 10);

                u.log('Would Insert ', query)
                sql.tagnet(query, function (err, reply) {
                    callback(false, null);
                });

        });
        if(i == len){
            async.series(tasks, function(err, reply){
               cb(false, null);
            });
        }
    })

}
async.series([ parseCsv, getOrigin, handleRecord], function(){
    u.log('DONE Updating Records');
})