var mysql = require('mysql');
init();

function init() {

    var args = process.argv.slice(2);

    if (args.length != 2) {
        console.log('Please provide start date and end date');
        return;
    }

    var startDate = new Date(args[0]), endDate = new Date(args[1]);
    var dates = [];


    dates.push(startDate);

    var nearestEnd = new Date();
    nearestEnd.setDate(startDate.getDate()+6-startDate.getDay());

    if(nearestEnd < endDate) {
        dates.push(nearestEnd);
        var i = new Date();
        i.setDate(nearestEnd.getDate()+1);

        for(;i<= endDate; i = new Date(i.setDate(i.getDate()+1))) {
            dates.push(new Date(i));
            dates.push(new Date(i.setDate(i.getDate()+6)));
        }

        if(dates[dates.length-1] != endDate)
            dates[dates.length-1] = endDate;
    }
    else
        dates.push(endDate);


    var queryString = "SELECT * FROM (SELECT count(*) as count, date(created_at + INTERVAL 6 - weekday(created_at) DAY) as week, workflow_state FROM applicants WHERE created_at > '"+(dates[0]).toISOString().substring(0, 19).replace('T', ' ')+"' AND created_at < '"+(dates[1]).toISOString().substring(0, 19).replace('T', ' ')+"' GROUP BY workflow_state";
    for(i=2;i<4;i++) {
        queryString += " UNION SELECT count(*) as count, date(created_at + INTERVAL 6 - weekday(created_at) DAY) as week, workflow_state FROM applicants WHERE created_at > '"+(dates[i]).toISOString().substring(0, 19).replace('T', ' ')+"' AND created_at < '"+(dates[i+1]).toISOString().substring(0, 19).replace('T', ' ')+"' GROUP BY workflow_state";
        i++;
    }
    queryString += ") as t";
    console.log(queryString);


    var con = mysql.createConnection({
        host: "localhost",
        user: "root",
        password: "root",
        database: "shiv"
    });

    con.connect(function(err) {
        if (err) throw err;
        console.log("Connected!");
        con.query(queryString, function(error, results) {
            if(err)
                throw err;
            console.log(results);
        });
    });
}